Тема 5. Работа со списками (базами данных)

Цель работы: овладеть навыками работы со списками (базами данных) в Excel, освоить средства сортировки, поиска и фильтрами данных, а также формирования промежуточных итогов.

ПРЕДСТАВЛЕНИЕ ЭТ В ВИДЕ СПИСКА

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

Строки списка должны содержать данные одного типа. Рекомендуется отделять список от других данных на рабочем листе (в том числе от итоговой строки) хотя бы одной пустой строкой. Это поможет Excel автоматически выделить список при сортировке энных или выполнении фильтрации.

СРЕДСТВА ОБРАБОТКИ СПИСКОВ В EXCEL

Excel предоставляет различные возможности и функции обработки списков:

• ввод, просмотр и редактирование списка с использованием формы данных;

• сортировка данных списка по одному или нескольким ключам сортировки (не более трех);

• поиск данных с использованием формы данных;

• отбор записей из списка с использованием автофильтра и расширенного фильтра;

• получение промежуточных итогов по группам записей.

ВВОД, ПРОСМОТР И РЕДАКТИРОВАНИЕ СПИСКА С ИСПОЛЬЗОВАНИЕМ ФОРМЫ ДАННЫХ

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

• Добавить - ввод новой строки в список;

• Удалить - удаление текущей строки из списка;

• Вернуть - восстановление данных;

• Назад - перемещение на одну строку назад по списку;

• Далее - перемещение на одну строку вперед по списку;

• Критерии - задание критериев поиска данных;

• Закрыть - завершение диалога.

СОРТИРОВКА ДАННЫХ

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

Чтобы отсортировать список, следует:

• выделить область списка (Достаточно указать одну ячейку списка. После подачи команды сортировки Excel автоматически выделит весь список. Однако в этом случае итоговая строка списка (если она не отделена пустой строкой) также будет включена в сортировку.);

• задать Данные- Сортировка;

• в диалоговом окне Сортировка диапазона в группе Сортировать по определить ключ сортировки (ключом сортировки является столбец, по которому Excel должен отсортировать данные )и указать направление сортировки: по возрастанию или по убыванию;

• нажать кнопку ОК.

Для выполнения быстрой сортировки (по одному ключу) используются кнопки на панели инструментов Стандартная: по возрастанию и - по убыванию. Ключом сортировки в этом случае является столбец с текущей ячейкой.

Excel дает возможность проводить сортировку по нескольким ключам. Многоуровневая сортировка выполняется в том случае если в списке содержатся данные с одинаковым значением первого ключа.

Для выполнения многоуровневой сортировки следует в диалоговом окне Сортировка диапазона в группе Затем по указать второй, а в группе В последнюю очередь, по - третий ключ сортировки.

ПОИСК ДАННЫХ С ИСПОЛЬЗОВАНИЕМ ФОРМЫ ДАННЫХ

Для осуществления поиска данных следует:

• выделить область списка;

• задать Данные - Форма;

• в появившемся диалоговом окне нажать кнопку Критерии;

• задать данные для поиска;

• просмотреть строки списка, содержащие искомые данные, нажимая кнопки Далееи Назад;

• нажать кнопку Закрыть.

ОТБОР ЗАПИСЕЙ ИЗ СПИСКА

С помощью фильтров можно выводить на экран и просматривать данные списка, которые удовлетворяют определенным условиям. Быстро и удобно можно просматривать нужные записи с помощью автофильтра. Более сложные запросы к базе данных можно реализовать с помощью расширенного фильтра.

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

Чтобы установить автофильтр, следует:

• выделить область БД;

• задать Данные- Фильтр-Автофильтр.

Excel помещает раскрывающиеся списки непосредственно в

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

Чтобы отключить фильтрацию для отдельного поля, надо выбрать в раскрывающемся списке параметр (Все). Чтобы показать на экране все скрытые записи, следует задать Данные-Фильтр-Отобразить все.

С помощью автофильтра можно для каждого поля задать пользовательские критерии для отбора записей БД. Например, вывести на экран только те записи, значения которых находятся в пределах заданного интервала соответствующего поля. Чтобы указать пользовательские критерии, надо в раскрывающемся списке выбрать параметр (Условие...), а затем в диалоговом окне Пользователъский автофильтр ввести нужные критерии.

Можно задать соединение нескольких критериев фильтрации, объединив их по И (запись появится в выходном списке тогда, когда будут выполнены все заданные условия) или по ИЛИ (в выходном списке запись появится, если будет выполнено хотя бы одно условие). В критериях можно задавать шаблоны, содержащие символы * и ?.

Например,

1. если задать по полю Фамилия =Иванов ИЛИ =Петров, то будут оставлены только записи с этими фамилиями;

2. если по полю Сумма задать >=2000 И <=3000, то будут отобраны записи в которых сумма лежит в диапазоне от 2000 до 3000;

3. если по полю Фамилия задать =А*, то будут найдены все записи в которых фамилия начинается на букву А (символ * заменяет собою любое количество любых символов);

4. если по полю Фамилия задать =А?????, то будут найдены все записи в которых фамилия начинается на букву А и содержит не более шести букв (символ ? заменяет собою один любой символ).

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

Предпосылкой использования расширенного фильтра является наличие на рабочем листе области БД и области критериев.

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

Число строк в области критериев определяется количеством критериев. Если условия поиска заданы в области критериев в одной строке, то эти условия связаны оператором И. Если же условия поиска заданы в разных строках, то они связаны оператором ИЛИ. Включение пустых строк в область критериев недопустимо, так как в этом случае будут отобраны все записи БД.

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

• выделить область БД;

• задать Данные-Фильтр-Расширенный фильтр;

• в диалоговом окне Расширенный фильтр в поле Исходный диапазон: указать интервал ячеек, содержащий область БД;

• поле Диапазон условий: указать интервал ячеек, содержащий область критериев;

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

• нажать кнопку ОК.

Если в диалоговом окне Расширенный фильтр установить переключатель Только уникальные записи, то повторяющиеся строки списка в области выходного документа показаны не будут.

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

Примеры расширенных фильтров.

Пусть имеется база данных содержащая поля: Марка автомобиля, Год выпуска, Пробег (тыс. км), Объем двигателя, Состояние, Цвет, Цена (долл), Цена (руб)

Если сформировать таблицу критериев следующим образом:

Марка автомобиля	Год выпуска	Пробег (тыс. км)	Объем двигателя	Состояние	Цвет	Цена (долл)	Цена (долл)	Цена (руб
Москвич-2140	>=2000			хор		>=3000	<=3500
Москвич-2140	>=2000			идеал		>=3000	<=3500

То будут отобраны все записи, в которых речь идет об автомобиле Москвич-2140 с годом выпуска не ранее 2000, состояние хорошее или идеальное, цена(долл) от 3000 до 3500. Т.е. по полю Состояние связь при помощи ИЛИ, а по полю Цена (долл) свяь при помощи И.

ФОРМИРОВАНИЕ ПРОМЕЖУТОЧНЫХ ИТОГОВ

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

• выделить область БД;

• задать Данные-Итоги;

• в диалоговом окне Промежуточные итоги указать имя поля, при каждом изменении значений в котором нужно подсчитать итоги;

• в списке Операция: выбрать нужную функцию для вычисления итогов (например, Сумма);

• в списке Добавить итоги по: определить, по каким полям предполагается подвести итоги;

• нажать кнопку ОК.

КОНТРОЛЬНЫЕ ВОПРОСЫ

1. Что представляет собой БД в Excel?

2. Какими средствами обработки списков обладает Excel?

3. Как преобразовать таблицу Excel в список?

4. Для каких целей используются формы данных?

5. Как выполнить многоуровневую сортировку?

6. Какие существуют способы фильтрации данных?

7. Чем автофильтр отличается от расширенного фильтра?

8. Как выполнить фильтрацию данных с помощью автофильтра?

9. Какова последовательность работы с расширенным фильтром?

10. В каких случаях и каким образом подсчитываются промежуточные итоги?

ЗАДАНИЯ

1. Вызвать Excel для работы с ЭТ, созданной при выполнении предыдущих практических заданий.

2. Создать новый лист Лист 7. Присвоить ему имя База данных.

3. На листе База данных создать БД, аналогичную приведенной ниже (табл. 3.7).

3.7. База данных

Номер цеха	Шифр продукции	Цена 1 т, руб.	1 кв., т	2 кв., т	3 кв., т	4 кв., т	Всего за год, т
1
...
2
...
3
...

При заполнении полей: "Шифр продукции", "Цена 1 т, руб.", "1 кв., т", "2 кв.. т", "3 кв., т", "4 кв., т)", "Всего за год, т" использовать ссылки на данные листов. Цех1, Цех2, ЦехЗ.

Выполнить сортировку БД по полю "Цена 1 т, руб." в порядке убывания.

5. Выполнить многоуровневую сортировку, где в качестве первого ключа указать номер цеха, второго -цену 1 т продукции.

6. Осуществить поиск записей с ценой продукции 650руб., используя форму данных.

7. Используя автофильтр, отобрать записи по цеху 2.

8. С помощью автофильтра задать пользовательские критерии для отбора всех продуктов, шифр которых начинается с символов 03 по цеху 2.

9. Отобразить на экране все скрытые записи БД. Снять автофильтр.

10. Используя расширенный фильтр, отобрать продукты, производство которых всего за год превышает 2000т. Выходные данные разместить на свободном месте рабочего листа под областью БД и областью критериев. В выходной документ включить все поля БД.

11. Используя расширенный фильтр, получить список продуктов, цена которых находится в диапазоне от 300 до 600 руб. Организовать связь по И.

12. Используя расширенный фильтр, получить список продуктов, цена которых либо менее 300 либо более 600 руб. Организовать связь по ИЛИ.

13. Используя расширенный фильтр, найти записи с максимальным производством продукции всего за год по цехам 1, 2 и 3. В выходной документ включит) поля: "Номер цеха", "Шифр продукции", "Всего за год, т".

14. Используя расширенный фильтр, найти запись по цеху 2, где производстве продукции всего за год является минимальным.

15. Подсчитать промежуточные итоги по выпуску продукции (по кварталам и всего за год) в цехах 1, 2 и 3.