Программа работы
1. Изучение понятия базы данных в Excel.
2. Изучение способов сортировки данных.
3. Использование фильтров.
4. Использование экранной формы.
5. Подведение промежуточных итогов.
Краткие сведения
Понятие базы данных в Excel
Под базой данных понимается совокупность взаимосвязанных данных, хранящихся на машинных носителях информации и обрабатываемых с помощью системы управления. Таким образом, база данных кроме взаимосвязанных данных включает в себя совокупность программных средств для управления ими.
Данные, организованные в Excel в виде таблицы или списка, также часто называют базой данных. Конечно, такая база данных не сравнима с профессиональными СУБД, но вполне может называться базой данных, так как содержит взаимосвязанные данные, для управления которыми в Excel содержатся специальные команды и функции. Кроме того, в Excel содержится специальная программа MS-Query, позволяющая работать и с более мощными СУБД.
Каждую строку таблицы Excel можно рассматривать как запись. Запись состоит из взаимосвязанных полей, которым соответствуют клетки таблицы. Под полем понимается так же столбец таблицы. Каждое поле имеет имя.
Наиболее простой операцией по обработке данных является их сортировка по одному или нескольким ключам, то есть условиям.
Сортировка данных
Пусть у нас имеется неотсортированная таблица «Ведомость», представленная на рис. 7.1.
Самый простой способ сортировки можно выполнить с использованием пиктограмм 
на панели инструментов Стандартная.
Пусть нам необходимо отсортировать нашу ведомость в алфавитном порядке. Для этого необходимо выделить блок В4:Е8 и щелкнуть на пиктограмме 
|
|
Рис. 7.1 Исходная таблица для сортировки
В результате получим ведомость, отсортированную в алфавитном порядке (рис. 7.2).
Рис. 7.2 Таблица «Ведомость», отсортированная в алфавитном порядке
Аналогичную сортировку и более сложные её виды можно производить с помощью меню Данные-Сортировка (Data-Sort...). В результате использования этих команд появится диалоговое окно Сортировка диаnaзoнa(Sort...) (рис. 7.3).
Рис. 7.3 Диалоговое окно Сортировка диапазона
В самом низу окна содержатся два индикатора, в которых необходимо отметить, как идентифицировать поля. Если при выделении таблицы выделена строка с именами полей, то необходимо сообщить системе об этом, то есть щелкнуть по индикатору Подписям [первая строка диапазона] (Header Row), в противном случае нужно активизировать индикатор Обозначениям столбцов листа (No Header Row).
В первом случае сортировка будет проводиться по именам полей, которые можно выбрать в поле Сортировать по (Sort By). Во втором случае сортировка будет проводиться по именам столбцов (Столбец А, Столбец В и так далее). Напротив поля сортировки указаны индикаторы сортировки по в озрастанию (Ascending) и по убыванию (Descending).
Отсортируем нашу таблицу по полю «Отработано дней» по убыванию. Для этого проделаем следующие операции:
· выделим диапазон АЗ:Е8;
· вызовем команды Данные-Сортировка (Data-Sort.);
· с помощью индикатора Подписям (Header Row) укажем, что выделенный блок таблицы содержит строку с именами полей;
· в поле Сортировать по (Sort By) щелкнем на стрелке и из списка полей выберем поле Отработано дней;
· укажем, что сортировка будет производиться по убыванию;
|
|
· щелкнем на ОК.
В результате получим таблицу, отсортированную по полю Отработано дней (рис. 7.4).
Рис. 7.4 Ведомость, отсортированная по полю Отработано дней
Таблицу можно отсортировать по нескольким ключам. Для этого в поле Сортировать по нужно указать первый ключ сортировки, а в поле Затем по второй (см. рис. 7.3). Пример отсортированной таблицы по двум ключам представлен на рис. 7.5.
Рис. 7.5 Ведомость, отсортированная по убыванию
двух полей Отработано дней и Начислено, тыс.руб.
Кроме того, диалог Параметры (Sort options) (см. рис. 7.3) позволяет установить порядок сортировки по первому ключу - обычный или устанавливаемый пользователем, учет регистра символов и направление сортировки - сверху вниз (по строкам) или слева направо (по столбцам).
Фильтры
Под фильтром в Excel понимаются определенные условия, позволяющие просматривать только те данные, которые удовлетворяют этим условиям. В Excel различают обычный автофильтр и расширенный фильтр. Рассмотрим сначала автофильтр.
Автофильтр. Для вызова автофильтра необходимо выполнить команды Данные-Филътр-Автофильтр (Data-Filter-AutoFilter), выделив перед этим всю или 1 часть таблицы с именами полей. В результате рядом с именами полей появятся стрелочки (рис. 7.6).
Рис. 7.6 Автофильтр
Если щелкнуть по одной из этих стрелочек, то откроется список. Если активной является строка Все (All), то фильтрация отключена. Если выбрать какое- либо поле, то будет отображена только запись, содержащая это поле. Если же указать Условие, то откроется диалоговое окно Пользовательский автофильтр (Custom AutoFilter) (рис. 7.7).
|
|
Рис. 7.7 Диалоговое окно Пользовательский автофильтр
В данном окне можно задать условия для фильтра с использованием логических функций И (And) и Или (Or).
Чтобы выключить автофильтр, достаточно набрать еще раз команды Данные-Фильтр-Автофильтр (Data- Filter-AutoFilter).
Расширенный автофильтр. Для использования расширенного автофильтра назовем исходную таблицу Блоком ввода (рис. 7.8). Для создания Блока критериев скопируем имена полей(столбцов) в свободную часть таблицы и зададим критерии поиска. Пусть нам необходимо извлечь записи с окладом > 1000 и количеством отработанных дней, равным 22. Извлекать данные можно в Блок вывода, для которого также необходимо скопировать имена полей (столбцов), или в исходную таблицу.
Рис. 7.8 База данных, созданная с использованием
расширенного фильтра
Для вызова расширенного автофильтра необходимо выполнить команды Данные-Фильтр-Расширенный фильтр (Data-Filter-Advanced Filter). В результате откроется диалоговое окно Расширенный фильтр (Advanced Filter) (рис. 7.9).
Рис. 7.9 Диалоговое окно Расширенный фильтр
В полях, расположенных в центре окна, следует указать диапазоны блока ввода, блока критериев и, если блок вывода будет не на месте исходной таблицы, а за ее пределами - диапазон блока вывода (перед этим в группе Обработка (Acuion) необходимо указать, что копирование будет производиться в другое место) Если установлен индикатор Только уникальные записи (Unique Record Only), то повторяющиеся записи не будут показаны в блоке вывода.
Если несколько критериев заданы в смежных ячейках на одной строке, то они работают как логическая функция И (And). Если несколько критериев заданы в смежных ячейках в столбце, то они работают как логическая функция Или (Or). Если условие достаточно сложное, то лучше взять его в кавычки, а перед ним поставить знак равно, например =">1400".
Экранные формы для работы с базой данных
Экранные формы делают работу с базами данных более простой и наглядной. Для использования экранной формы нужно выделить таблицу с именами полей и использовать команды Данные-Форма (Data-Form...).
В результате появится диалоговое окно для работы с экранной формой базы данных (рис. 7.10).
Рис. 7.10 Диалоговое окно с экранной формой
для работы с базой данных
В левой части окна видны имена полей и их содержимое для первой записи. Справа находятся кнопки для работы с экранной формой:
· Добавить (New) - вводит новую строку (запись) в базу;
· Удалить (Delete) - удаляет текущую запись базы;
· Вернуть (Restore) восстанавливает удаленную строку;
· Назад (Find Prev) на одну строку назад;
· Далее (Find Next) на одну строку вперед;
· Критерии (Criteria) задает критерии поиска записи в базе;
· Закрыть (Close) закрывает диалог.
В качестве критерия можно задать любое имеющееся значение поля. Например, если в поле «Оклад» задать "800" и нажать клавишу ввода, то мы мгновенно перейдем к записи за номером 3 (Петров).
К сожалению, количество полей в экранной форме ограничено, что несколько снижает ее ценность.
Промежуточные итоги
Часто бывает необходимо подсчитать промежуточные итоги, например, при расчетах с подотчетными лицами, в ведомостях отпуска материалов со склада и в других таблицах. Для этого необходимо:
q сначала отсортировать таблицу по столбцу, промежуточные итоги для которого мы хотим определить;
q далее поместить курсор в область таблицы и Excel попытается автоматически определить ее диапазон. Можно выделить таблицу явным образом, то есть вручную;
q выполнить команды Данные-Итоги (Data- Subtotal). После этого откроется диалоговое окно Промежуточные итоги (рис. 7.11).
Рис. 7.11 Диалоговое окно Промежуточные итоги
В первом поле этого окна нужно указать имя столбца исходной таблицы (рис. 7.12), для которой мы хотим получить промежуточные итоги, например "Ф.И.О.".
Во втором поле следует указать операцию, например «Сумма».
В третьем поле указывается столбец, по которому считаются промежуточные итоги, например «Стоимость»
Три индикатора внизу окна соответственно означают:
· заменить текущие итоги;
· конец страницы между группами;
· итоги под данными.
Рис. 7.12 Исходная таблица для определения промежуточных итогов
Предположим, что мы хотим подсчитать промежуточные итоги для лиц, получающих материалы с центрального склада. Отсортируем исходную таблицу по столбцу "Ф.И.О.", а затем активизируем команды Данные-Итоги. В результате получим общие промежуточные итоги по Иванову, Петрову, Сидорову и общий итог (Grand Total) (рис. 7.13).
Для получения промежуточных итогов по товарам, нужно отсортировать таблицу по полю «Товар», а затем выполнить команды Данные-Итоги.
При определении промежуточных итогов Excel создает новые строки промежуточных результатов и структуру таблицы. Цифры 
в левом верхнем углу таблицы позволяют показать различные элементы ее структуры.
Если нас интересует только общий итог, то следует щелкнуть по цифре [1].
Итоги по группам и общий итог раскрываются при помощи цифры [2].
Все промежуточные итоги (по каждой записи, группе и общий итог) выводятся щелчком мыши по цифре [3]. Этим же целям служат находящиеся слева от таблицы плюсы [+] и минусы [–].
Рис. 7.13 Таблица с промежуточными итогами по фамилиям