Организация базы данных средствами электронной таблицы Excel.

Технология создания базы данных в Excel такая. После запуска Excel на экране появляется чистая рабочая книга с новым окном документа с временным названием Воок 1 (Книга). Эта книга содержит несколько чистых рабочих листов (количество листов определяется командой Сервис / Параметры / Общие).

Создание базы данных

Создание базы данных начинается с описания полей, то есть по вводу имен полей (заголовков столбцов). Каждый элемент поля можно отформатировать, а дальше согласно заданным форматом автоматически будут форматироваться все последующие элементы в этом поле базы данных. Например, если элемент поля отформатировать полужирным шрифтом, тогда все последующие элементы этого поля будут выделены также полужирным шрифтом.

На рис. 2.36 приведен образец заполнения базы данных "Движение товаров".

Образец заполнения базы данных

Рис. 2.36. Образец заполнения базы данных

Если база предполагает наличие полей, вычисляемых тогда для таких полей следует задать формулы их вычисления. В базе данных "Движение товаров" поле Сумма в ячейке 12 вычисляется по формуле = G2 х НЕТ. После введения заголовков столбцов и образца заполнения базы следует выделить эти две строки, а затем выбрать из меню Data (Данные) команду Form (Форма). С помощью этой команды Excel анализирует строку с именами полей и элементами в первой записи и создает форму, в которой расположены сверху вниз все имена полей и их значение в первой записи. На рис. 2.37 показано форму для ввода данных в базу данных - она выглядит как диалоговое окно пользователя (вычисляемые поля, не имеют текстовых окон). После создания формы данных можно начинать введение записей в базу данных активизацией кнопки Добавить.

Переход к следующему полю формы после ввода значения предыдущего осуществляется клавишей Tab. Клавиша Enter в этом случае обеспечивает полной записи в базу. Если надо отредактировать значение поля, которое уже пройдено, следует нажать Shift + Tab для возврата к ошибочному поля. При вводе данных в определенное поле можно скопировать значение с того же поля предварительной записи нажатием клавиш Ctrl + "(кавычки). Если надо ввести число, начинается нулями (005102), которые не должны исчезнуть после введения, набор числа надо начать с апострофа ('005102).

Диалоговое окно формы данным первой записи

Рис. 2.37. Диалоговое окно формы данным первой записи

Введение всех полей текущей записи заканчивается активацией кнопки Добавить или нажатием одной из двух клавиш, Enter или 4. В результате Excel запишет текущую запись в базу данных и на экран выведет пустую форму, в которую можно вводить значения полей новой записи.

Для просмотра созданной базы данных следует закрыть диалоговое окно формы нажатием кнопки Закрыть. Наполненную базу данных приведены на рис. 2.38.

Заполненная база данных

Рис. 2.38. Заполненная база данных

Форма также содержит ряд кнопок в правой части, используемые для поиска и редактирования любой записи. Над верхней кнопкой Добавить Excel показывает номер текущей записи, за ним следует число, показывающее общее количество записей в базе.

Если допущена ошибка при редактировании данных, активированием кнопки Вернуть можно восстановить неправильно или случайно изменен запись данных и таким образом не допустить введения ошибочной записи в таблицу. Если же ошибочная запись уже попал в таблицу, его можно отредактировать традиционными методами редактирования таблицы в Excel после закрытия диалогового окна формы. Если форма данных еще не закрыта, можно активировать кнопки Назад или Далее к появлению ошибочной записи в форме и изъять его кнопкой Удалить.

Закончив ввод в таблицу всех записей, надо закрыть форму данных кнопкой Закрыть, а затем выполнить сохранение данных таблицы командой Файл / Сохранить.

Поиск данных с помощью маски

Наряду с удобным вводом Форма данных предоставляет возможность осуществлять поиск информации. Для ввода критерия поиска соответствующей записи (записей) предназначена кнопка Критерии. Критерий поиска может включать шаблоны поиска (*,?), А также операторы сравнения (=,>,> =, <<=, в). Например, чтобы отразить в форме данных только тех поставщиков, которые поставляли товар на склад № и, нужно в поле Состав ввести значение критерия поиска - и, а в поле Операция - значение <1И (кодами операций от 1 до 10 обозначены поступления товаров на склад , кодами, превышающих 10 - выбытие товаров со склада). Далее следует активировать кнопку Далее или Назад для осуществления поиска от текущей записи, который активирован табличным курсором, соответственно вперед или назад. Введение критериев поиска в форму отражено на рис. 2.39. Результаты поиска данных в базе отражаются на экране таким образом, как это показано на рис. 2.40, то есть в форме отображается только одна запись. Для просмотра других найденных записей надо воспользоваться кнопками Далее или Назад.

Диалоговое окно формы с маской поиска

Рис. 2.39. Диалоговое окно формы с маской поиска

Диалоговое окно формы с результатами поиска данных

Рис. 2.40. Диалоговое окно формы с результатами поиска данных

Критерии поиска и целевые функции

В предыдущем материале с помощью Формы данных был сформирован запрос на поиск записей в базе. Разыскивались поставщики, поставляли товар на склад № И.

Пользователю могут потребоваться результаты в дальнейшей работе, например для того, чтобы продолжить работу с ними, используя формулы, функции или просто распечатать их. В этом случае поиск с помощью формы данных не дает желаемого результата, поскольку результаты поиска в Форме отображаются только в разрезе одной записи. В случае если такой вариант поиска не удовлетворяет пользователя, есть возможность осуществить поиск по-другому - с помощью области критериев и целевой области. В этом случае Excel скопирует записи из базы, соответствующие заданным критериям, в целевую область. Там их можно будет обрабатывать традиционными способами, как в таблице. Рассмотрим подробнее эту процедуру.

Область критериев организует пользователь, и желательно ее определять таким образом, чтобы она не мешала пополнению и расширению базы данных. Как правило, область критериев создают перед или рядом с областью базы данных. В нашем примере область критериев сформирована правее таблицы с базой данных.

Область критериев должна иметь размер по крайней мере в две строки. Первая строка области критериев формирующейся должна содержать имя критерия (критериев). Сюда, как правило, копируются все имена полей базы данных (то есть ее первая строка) или часть из них. Однако в области критериев могут также быть имена полей, которых нет в базе данных. Так бывает тогда, когда надо выполнить, например, поиск по полям, исчисляемые.

В строках под именами полей формируется область критериев поиска. В области критериев каждая строка - это критерий поиска. Полный критерий поиска, заданный всей областью критериев, состоит из объединенных с помощью логической операции Вот (или) -условие.

Таким образом, каждая Or-условие задается отдельной строкой. Внутри строки критерия значения отдельных полей рассматриваются как условия, объединенных с помощью логической операции And (И).

Если нужно разыскать в базе данных все поставки поставщиков с кодом 1001 или с кодом 1590, тогда в области критериев в колонке Пост_Пок под именем поля должен стоять 1001, а в следующей строке - 1590:

Пост_Пок

1 001

1 590

Если надо разыскать в базе данных поставки на склад № 2 на сумму свыше 1000 грн только поставщика с кодом 1 125, то такой комбинированный запрос на поиск образуется с помощью трех критериев, связанных условной операцией And (И). Вводится этот запрос в одну строку области критериев:

Состав Опер Пост_Пок Сумма

2 <11125> 1000

Прежде чем начать поиск в соответствии с критериями поиска в таблице следует предусмотреть еще одну область - целевую. В эту область Excel сможет копировать найдены данные. ее следует расположить так, чтобы избежать конфликтов с частями таблицы, выделенные под базу и критерии. В первой строке целевой области следует записать имена полей, содержание которых нужно увидеть в найденных записях. Целевая область должна быть точно определена. Если пользователь во время объявления целевой области определит, кроме строки заголовка, кроме того, например, три строки, то тем самым он сообщает Excel, при поиске в базе его интересуют первые три найдены записи, которые соответствуют критериям поиска. В случае если Excel найдет более трех записи - в зону будут выведены первые три записи, а затем сообщение "Extract Range is Рой" (Целевая область заполнена). Это свидетельствует о том, что существуют и другие записи, которые соответствуют критериям поиска.

Реорганизуя электронную таблицу таким образом, чтобы в соответствии с предыдущими требованиями сформировать область критериев, целевую область и область данных (рис. 2.41).

Зона электронной таблицы со сложившейся областью критериев и целевой областью

Рис. 2.41. Зона электронной таблицы со сложившейся областью критериев и целевой областью

Полный критерий поиска данных в базе - поставки на склад № 2 на сумму свыше 1000 грн поставщика с кодом +1125.

Таким образом, база данных объявлена, определенные область критериев и сами критерии, выбранная целевая область. Можно начинать поиск данных. Поиск осуществляется с помощью команды поиска и выборки данных из базы Данные / Фильтр / Расширенный фильтр ... После запуска команды на выполнение на экране появляется диалоговое окно установки расширенного фильтра. В окне диалога в зоне Обработка следует установить опцию скопировать результат в другое место, а дальше следует определить область базы данных - Исходный диапазон, Диапазон условий и зону ячеек для фиксации результата поиска - Поместить результат в диапазон. Оформленный фильтр в данном случае будет таким, как на рис. 2.42.

Диалоговое окно установки расширенного фильтра

Рис. 2.42. Диалоговое окно установки расширенного фильтра

После активации кнопки OK Excel начинает поиск данных в базе. Результат поиска оформляется как на рис. 2.43.

Электронная таблица с результатами поиска данных

Рис. 2.43. Электронная таблица с результатами поиска данных

В диалоговом окне фильтра есть опция Unique Records Only / Только уникальные записи, которая позволяет ликвидировать повторную выборку из базы одинаковых записей, если таковые имеются. В случае если определение некоторых установок (база данных, критерии, целевая область) содержали ошибки, появляется диалоговое окно с предупреждением о том, например, что целевая область недействительна, - Extract Range not Valid / Неправильно указано диапазон. В этом случае следует выявить ошибку и задать правильные установки.

Сортировка данных

База данных в Excel обладает способностью обеспечить определенный порядок записей (например, в порядке возрастания номеров или в алфавитном порядке). Однако при заполнении базы новыми записями сложно сохранять установленный порядок. При введении новой записи Excel подсоединяет его в конец базы данных. Если все записи упорядочены по определенному признаку, он может не быть удобным для пользователя в большинстве случаев. Но иногда возникает необходимость расположить записи в другом порядке, причем для каждого конкретного случая порядок может быть свой, особый. Гибкость упорядочения записей в Excel обеспечивается командой Sort (Сортировать).

Чтобы обеспечить с помощью Excel правильную сортировку записей в базе данных, нужно указать ключи сортировки и тип упорядочения. Под ключом сортировки понимаем поле, которое определяет новый порядок записей. Существуют два типа упорядочения: порядок роста, когда текст располагается в алфавитном порядке (от А до Z), а числа - по убыванию (от мала до велика) порядок убывания, что противоположный алфавитном порядке и порядке нумерации (то есть от Z до А и от самого маленького до самого).

Сортировать можно не только базы данных, простые таблицы также могут быть перегруппированы. Для сортировки записей можно указывать до трех ключей сортировки, для каждого ключа можно указывать свой тип упорядочения.

Для того, чтобы отсортировать записи в базе данных Excel, выполняют такие действия установят табличный курсор на любое имя поля базы данных; выбирают команду Данные / Сортировка. При этом Excel автоматически отмечает все записи базы и открывает диалоговое окно Сортировка диапазона, в которое следует внести ключи сортировки и типы упорядочения.

После активации кнопки OK Excel отсортирует записи. Если по ошибке выбрано не те ключи сортировки или использован не тот порядок сортировки, следует воспользоваться командой Правка / Отменить Сортировка.

Фильтрация базы данных

Иногда нужно вывести на экран только записи базы данных определенного содержания. Результат достигается фильтрацией данных, то есть способностью Excel скрыть не нужны в определенный момент записи базы данных. Для выполнения фильтрации надо: табличный курсор предварительно установить в одно из полей базы данных и выбрать команду Данные / Фильтр / Автофильтр, при этом рядом с полями базы создаются окна открываются; открыть окно поля для фильтрации; из списка открывшемся выбрать значение фильтра (на рис. 2.44) приведены налаживания фильтрации данных по коду товара 15120).

База данных с окнами полей фильтрации

Рис. 2.44. База данных с окнами полей фильтрации

В результате фильтрации Excel выведет на экран только записи, соответствующие поставленным условиям. На рис. 2.45 приведены отфильтрованную базу за фильтром 15120 поля Код_тов.

База данных после фильтрации

Рис. 2.45. База данных после фильтрации

Отфильтрованы и выведены на экран записи можно копировать в другую таблицу, а затем использовать для обработки, печати и т. Для отмены фильтрации, то есть вывода всех записей базы, следует воспользоваться командой Данные / Фильтр / Отобразить все.

Excel дает пользователю возможность осуществлять фильтрацию базы не только по определенному значению поля, но и по более общим критериям, например - по фамилии, начинающейся с определенной буквы, или цифровым значением полей, соответствующих определенным условиям. Для такого рода фильтрации нужно создать собственный автофильтр.

Для создания собственного фильтра нужно:

• выбрать команду Данные / Фильтр / Автофильтр;

• открыть окно того поля, на базе которого будет создан собственный автофильтр;

• из списка открывшемся выбрать опцию Сustom .. (условиях), на экране появляется диалоговое окно Пользовательский автофильтр;

• создать фильтр - из открытого списка выбрать нужный оператор сравнения (=,>, <,> =, <=, в) в текстовое окно, дело, ввести значение (текст или число), в отношении которого будет осуществляться сравнения значений поля; наложить создан фильтр на базу данных активацией кнопки ОК. В режиме просмотра Пользовательский автофильтр можно разработать собственный фильтр для выборки записей, значения полей которых лежат в заданных пределах или соответствуют хотя 6 одном из двух введенных критериев. Для этого нужно: ввесгы оператор сравнения для нижней границы; ввести наименьшее значение интервала; активировать переключатель And, если образуется И-условие, или переключатель Вот, если образуется Или-условие; выбрать оператор сравнения для определения верхнего интервала; ввести наибольшее значение интервала.

На рис. 2.46 иллюстрируется фильтр, образованный для фильтрации только тех записей, где значение поля Цена находится в пределах - больше (isgreater then) чем 2,87 меньше (is less than) чем 34,78 грн.

Рис. 2.46. Диалоговое окно Автофильтр пользователя с установленными параметрами фильтрации

Результат фильтрации по данным фильтром приведены на рис. 2.47.

Рис. 2.47. Результат фильтрации базы за фильтром 2,87 <Цена <34,78

Отфильтрованные данные можно копировать в другую таблицу или выводить на печать.

Анализ данных с помощью сводных таблиц

Сводные таблицы (Pivot Table) позволяют различными способами видеть информацию, хранящуюся в

базах данных Excel. Сводная таблица Excel позволяет легко и быстро суммировать и сравнивать данные. Если нужно суммировать данные, то следует только перенести поля из одного места таблицы в другое, создав таким образом новый отчет, не меняя структуры данных.

Для автоматизации создания сводной таблицы Excel использует программу - мастер сводных таблиц. Для формирования сводной таблицы нужно выполнить следующие действия:

• активировать базу данных (установить курсор на любую ячейку таблицы, содержащей базу данных); выбрать команду Данные / Сводная таблица. На экране появляется диалоговое окно Мастер сводных таблиц и диаграмм - шаг 1 из 4, в котором надо задать тип данных, на основе которых будет формироваться сводная таблица (рис. 2.48);

Диалоговое окно Мастер сводных таблиц и диаграмм

Рис. 2.48. Диалоговое окно Мастер сводных таблиц и диаграмм

• нажать кнопку Далее, появляется новое диалоговое окно Мастер сводных таблиц и диаграмм для определения местоположения входных данных (рис. 2.49);

Диалоговое окно Мастер сводных таблиц и диаграмм

Рис. 2.49. Диалоговое окно Мастер сводных таблиц и диаграмм

"После определения местоположения входных данных снова активировать кнопку Далее, появляется третий диалоговое окно Мастер

сводных таблиц и диаграмм, в котором надо определить место формирования таблицы - новый лист или текущий (рис. 2.50);

Диалоговое окно Мастер сводных таблиц и диаграмм

Рис. 2.50. Диалоговое окно Мастер сводных таблиц и диаграмм

• указав место формирования сводной таблицы, следует активировать кнопку Готово - на экране появляется макет сводной таб7иици (рис. 2.51);

Макет сводной таблицы

Рис. 2.51. Макет сводной таблицы

• заполнить макет сводной таблицы - переместить с помощью мыши имена полей из правой части диалогового окна в области Page (Страница), Row (строка), Column (Колонка), Data (Данные). Поля, размещаются в области Page (Страница), является условием фильтрации данных для сводной таблицы. Поля, размещаются в области Row (строка), отражают их конкретные значения в каждой строке сводной таблицы. Поля, размещаются в области Column (Столбец), фиксируют названия колонок таблицы и конкретные значения полей, подсчитываются. Поле, размещается в зоне данных, подсчитывается. Если элементы колонок содержат числа, тогда для вычисления программа Excel автоматически использует функцию Sum (Сумма).

В сводной таблице можно менять порядок полей и полностью изменить ее вид. На рис. 2.52 приведены сложившуюся Сводную таблицу, в которой движение товаров в разрезе складов фильтровые по дате (06.03.2004).

Форма сводной таблицы

Рис. 2.52. Форма сводной таблицы

После обновления данных в базе нужно обновить и сводную таблицу. Для обновления сводной таблицы следует выделить любую ее ячейку, после чего выполнить команду Данные / Обновить данные.

 
< Пред   СОДЕРЖАНИЕ   След >