Технология работы с базами данных в приложении Microsoft Excel
Базы данных как способ хранения и обработки различной информации играют в настоящее время огромную роль. В базах данных хранят сведения о клиентах, заказах, справочники адресов и телефонов, различного рода информацию о туристических агентствах и предлагаемых услугах и т. д.
Для учета данных о сотрудниках на предприятиях используют самые разнообразные методы. В одних организациях существуют журналы учета, куда информация вносится вручную, в других применяются классические базы данных для учета кадров, в третьих используются СУБД Access. Но в большинстве случаев на небольших предприятиях учет данных о сотрудниках ведется в электронных таблицах Microsoft Excel.
В предложенном пособии рассматриваются основные возможности, предоставляемые Excel, для работы с базами данных на примере списка "Сотрудники", который содержит информацию о работниках некоторого условного предприятия. Такая база данных удобна по следующим причинам:
- она обеспечивает удобный метод поиска информации о работнике (фамилия, имя, отчество, место проживания, телефон, должность, дата рождения и т. д.);
- с ее помощью можно выполнять различного рода анализ, например, определять структуру кадров;
- пользуясь хранящейся в ней информацией, можно быстро сформировать отчет.
Списки Excel как база данных
Приложение Microsoft Excel обладает богатыми встроенными средствами для обработки и анализа данных. Аналогом простой базы данных в Excel служить список.
Список - это группа строк таблицы, содержащая связанные данные.
Отличительной особенностью списка является то, что каждый его столбец содержит однотипные данные, например, перечень фамилий, дату рождения и т. д. (рис. 1).
Если провести аналогию между списком и табличной базой данных, то столбцы списка являются полями базы данных, а его строки - записями. Считается, что первая строка списка является его заголовком и содержит названия столбцов списка. Заголовок должен иметь на листе электронных таблиц горизонтальную ориентацию. Заголовки применяются Excel при составлении отчетов, а также при поиске и организации данных. Шрифт, размер шрифта, выравнивание и другие параметры форматирования, присвоенные заголовкам столбцов списка, должны отличаться от параметров, назначенных для строк данных. В списке не должно быть пустых строк и столбцов.
Рис. 1. Фрагмент списка «Сотрудники»
Проверка данных при вводе
Если с файлом работает сразу несколько пользователей, желательно контролировать тип вводимой ими информации и свести к минимуму ошибки ввода. В Excel выполнение подобных условий проверяется при помощи средства, которое называется проверкой ввода. Для этого надо:
- Выделить ячейки столбца, для которого устанавливается проверка ввода.
- На ленте Данные в группе Работа с данными выбрать команду Проверка данных.
- На вкладке Параметры в области Условие проверки выбрать Тип данных: Любое значение (используется для отмены проверки ввода), Целое число, Действительное, Список, Дата, Время, Длина текста и Другой (формат, для которого можно задать собственную формулу, например, "м" or "ж"). При выборе значения внизу окна появляются дополнительные поля для ввода условий или ограничений – например, минимального и максимального допустимого значения.
- На вкладке Сообщение для ввода можно установить флажок Отображать подсказку, если ячейка является текущей и ввести сообщение, чтобы оно появлялось на экране при выделении ячеек.
- На вкладке Сообщение об ошибке можно установить флажок Выводить сообщение об ошибке, чтобы задать тип сообщения об ошибке, которое появится при вводе в ячейку недопустимого значения.
Сортировка данных
Команда Сортировка позволяет переставить записи в другом порядке на основании значений одного или нескольких столбцов. Записи сортируются возрастанию/убыванию или по выбранному пользователю порядке (например, по дням недели).
Чтобы отсортировать список надо:
- Установить курсор в ячейку списка.
- Выполнить команду Сортировкана ленте Данные в группе Сортировка и Фильтр.
- В диалоговом окне Сортировка выбрать поле, по которому будет происходить сортировка; тип сортировки (по значению, цвет ячейки, цвет шрифта, значок ячейки) и порядок (по возрастанию, убыванию, настраиваемый).
Рис. 2. Создание пользовательского порядка сортировки
Примечание.Выбор настраиваемогопорядка позволяет задать нестандартный порядок сортировки. Для этого надо в диалоговом окне Списки (рис. 2) выбрать НОВЫЙ СПИСОК, в поле Элементы списка ввести значения, образующие пользовательский порядок сортировки (например, АОП, ФЭО, ИВЦ, ИТО, МПО), после чего последовательно выбрать кнопки Добавить и ОК.
Промежуточные итоги в БД
Для организации списков используют команду Промежуточные итоги на ленте Данные в группе Структура, которая позволяет:
- упорядочить список посредством группировки записей с выводом промежуточных итогов, средних значений или другой вспомогательной информации;
- выводить итоговую сумму;
- отображать список в виде структуры, что позволяет разворачивать и сворачивать разделы с помощью щелчка мыши.
Перед вызовом команды Итоги список обязательно надо отсортировать по полю, которое будет использоваться для группировки.
Режим структуры, в котором оказывается список после выполнения команды Итоги, позволяет просматривать различные части списка с помощью кнопок, расположенных на левом поле (рис. 3).
Рис. 3. Просмотр списка в режиме структуры
Кнопки, расположенные в верхнем левом углу, определяют количество выводимых уровней данных. Кнопки со значками "+" и "-" предназначены для свертывания \ развертывания отельных групп.
Чтобы удалить промежуточный и окончательные итоги, надо повторно выполнить команду Промежуточные итоги, а затем щелкнуть по кнопке Убрать все.
Автофильтр
Отфильтровать список - значит показать только те записи, которые удовлетворяют заданному критерию.
Чтобы установить или убрать автофильтр надо на ленте Данные в группе Сортировка и фильтр выбрать команду Фильтр. После этого нажать кнопку со стрелкой возле названия какого-либо поля, чтобы раскрыть список его элементов и выбрать отображаемые значения или задать условие отбора. На экране появятся только те записи, которые отвечают заданному условию. В случае необходимости можно продолжить фильтрацию, нажимая кнопки со стрелками на других полях.
Показать все записи по отфильтрованному полю, не убирая фильтр, можно выбрав в списке фильтра критерий Снять фильтр с….
Показать все записи по всем полям, не убирая фильтр, команда Очистить.
Для данных разного типа существуют дополнительные автофильтры, которые находятся в списке критериев Текстовые фильтры, Числовые фильтры, Фильтры по дате и т.д.
Если выделить какое-то числовое поле (например, Возраст), а в списке критериев выбрать Числовые фильтры, то появится список дополнительных фильтров (рис. 4), которые позволяют:
- задать критерий в виде неравенства – критерии равно, не равно, больше, больше или равно, меньше, меньше или равно, между;
- вывести первые N значений – критерий Первые 10: после выбора в списке Числовых фильтровкоманду Первые 10…, необходимо в появившемся окне указать число значений (N), а также способ вычисления: количество элементов списка, % от количества элементов;
- определить условие по среднему значению в указанном столбце – критерии Выше среднего, Ниже среднего;
- самостоятельно задаваемый фильтр – критерий Настраиваемый фильтр.
Рис. 4. Дополнительные числовые фильтры
Настраиваемый фильтр позволяет задать критерии из одного или двух условий.
Простое условие состоит: из имени поля (атрибута); варианта условия (равно, не равно, больше, меньше, больше или равно, меньше или равно; начинается с, не начинается с, заканчивается на или не заканчивается на; содержит, не содержит); слова или числа для сравнения.
Сложное условие состоит из двух простых, соединенных союзами И или ИЛИ.
При написании значений в условиях сравнения в фильтрах можно использовать подстановочные знаки (Таблица 1).
Таблица 1
Подстановочные знаки
Знак | Значение |
? | один любой знак |
* | любое количество символов |
~ | используют, когда в тексте надо найти подстановочные знаки (символы «?», «*» или «~») |
Расширенный фильтр
Расширенный фильтр позволяет сформировать более сложные условия, в том числе состоящие из более, чем двух условий.
Перед вызовом команды Расширенный фильтр, необходимо сформировать критерии. Для удобства лучше формировать критерии на отдельном листе (можно дать ему имя, например, Критерии) и давать критериям имена Кр1, Кр2 и т.д.
Основное правило: если критерии связаны между собой операцией И, то они должны располагаться в одной строке, а если ИЛИ, то в разных.
После формирования критерия, вызывают расширенный фильтр: на ленте Данные в группе Сортировка и фильтр команда Дополнительно.
Восстановить исходный список можно выбрав на ленте Данные в группе Сортировка и фильтр команду Очистить.