Навигация
Главная
 
Главная arrow Банковское дело arrow Информационные технологии в банках - Страхарчук АЯ
Предыдущая   СОДЕРЖАНИЕ   Следующая

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

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

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

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

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

Зразок заповнення бази даних

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

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

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

Діалогове вікно форми з даними першого запису

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

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

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

Заповнена база даних

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

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

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

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

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

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

Діалогове вікно форми з маскою пошуку

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

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

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

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

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

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

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

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

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

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

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

Пост_Пок

1001

1590

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

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

2 11 125 1000

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

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

Зона електронної таблиці зі сформованою областю критеріїв та цільовою областю

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

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

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

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

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

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

Електронна таблиця з результатами пошуку даних

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

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

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

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

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

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

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

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

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

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

База даних із вікнами полів фільтрації

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

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

База даних після фільтрації

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Макет зведеної таблиці

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

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

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

Форма зведеної таблиці

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

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

 
Предыдущая   СОДЕРЖАНИЕ   Следующая
 
Дисциплины
Банковское дело
БЖД
Бухучет и Аудит
География
Документоведение
Экология
Экономика
Этика и Эстетика
Журналистика
Инвестирование
Информатика
История
Культурология
Литература
Логика
Логистика
Маркетинг
Медицина
Менеджмент
Педагогика
Политология
Политэкономия
Право
Естествознание
Психология
Религиоведение
Риторика
РПС
Социология
Статистика
Страховое дело
Товароведение
Туризм
Философия
Финансы