Технология создания списков

Лабораторная работа №13

Списки и базы данных

1. Цель работы – научиться следующему:

1) Изучить основные понятия списков и базы данных в Excel.

2) Освоить технологию создания списков в Excel, поиска записей, сортировки и фильтрации.

3) Научиться анализировать списки, использую многокритериальные условия.

Ход работы

Понятие списка и базы данных в Excel

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

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

В терминологии Excel понятия «список» и «база данных» являются синонимами.

Строки таблицы называются записями базы данных, а столбцы — полями.

ü Начните создавать список, аналогичный представленному на рис. 13.1, заполнив только первые 3 записи, № личного дела не заполняйте.

Рис. 13.1 Окно Excel со списком - сведениями о сотрудниках предприятия

Список содержит информацию о каждом сотруднике: номер личного дела, ФИО, должность, пол, год рождения и т.д. - это поля списка. Так, в строке 5 показана запись на сотрудника с личным номером 1265, а в строке 8 - на сотрудника с личным номером 1269.

При создании списков следует придерживаться следующих правил:

- на одном рабочем листе следует размещать только один список;

- список отделяется от других данных по крайней мере одной пустой строкой и одним пустым столбцом;

- каждый столбец списка должен содержать однородную информацию;

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

- необходимо избегать пустых строк и столбцов внутри списка.

Над созданными списками Excel можно выполнять следующие операции:

- осуществлять поиск данных по заданным критериям;

- выполнять сортировку записей;

- фильтровать список;

- подводить промежуточные итоги;

- создавать итоговые таблицы данных.

Технология создания списков

Создание списка начинается с ввода наименований полей списка (см. строку 3 рис. 13.1). Заполнение записей списка — ввод данных в их поля (см. строки А —23 рис. 13.1) осуществляется двумя способами: вручную, как и при подготовке небольших таблиц, и с использованием специальной встроенной формы данных.

Форма данных - это наиболее удобный способ для просмотра, изменения, добавления, удаления записей списка, а также для поиска записей, удовлетворяющих заданным условиям.

Форма данных, используемая при создании списков, а также при их обработке, вызывается следующими командами:

Установить курсор в пределах списка»¹ → Данные → Форма

¹ Если выполнен только ввод наименований полей списка, для вызова формы следует выделить эти поля.

В результате появляется диалоговое окно формы с пользовательскими текстовыми полями для ввода значений полей списка (рис. 13.2). Имя окна формы соответствует имени листа, на котором создается список.

ü Введите оставшиеся записи списка с помощью формы данных.

Рис. 13.2 Диалоговое окно формы для работы со списками Excel

Он меняется в зависимости от ситуации обработки записей. Краткая информация о кнопках представлена в табл. 13.1.

Таблица 13.1 Назначение кнопок формы работы со списками

Кнопка	Назначение
Добавить	Открытие пустой формы для добавления новой записи
Удалить	Удаление текущей записи из списка
Вернуть	Восстановление измененных значений поля записи
Назад	Переход к предыдущей записи; возврат из режима задания критерия
Далее	Переход к следующей записи
Критерии	Переход в режим задания критерия поиска данных в списке
Закрыть	Закрытие окна формы
Очистить	Удаление данных поля (в режиме задания критерия)
Вернуть	Восстановление данных поля (в режиме критерия)
Правка	Переход к редактированию содержимого полей записи

Для быстрого заполнения базы можно использовать команду меню Правка → Заполнить.

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

Выделить ячейку с начальным значением последовательности →Команда Правка → Заполнить → Прогрессия...¹ → Задать расположение последовательности: по строкам или по столбцам → Задать значение шага → Выбрать тип последовательности → Для последовательности Даты выбрать единицы измерения: день, рабочий день, месяц, год → Задать предельное значение последовательности →ОК

Последующие операции выполняются в окне Прогрессия, изображенном на рис. 13.3.

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

Рис. 13.3 Диалоговое окно задания параметров последовательности

ü Заполните с помощью вышеописанной команды поле «№ личного дела».

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

Возможность достигается, если в окне Параметры (рис. 13.4) установлен флажок Автозавершение значений ячеек (см.: Команда Сервис → Параметры... → Вкладка Правка).

 

Рис. 13.4 Диалоговое окно задания параметров правки данных

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

Условия для определенного диапазона ячеек, или критерий правильности ввода, реализуются следующей схемой:

Выделить ячейки → Команда Данные → Проверка → Вкладка Параметры → Выбрать из списка тип данных → Задать интервал значений → Вкладка Сообщение для ввода → Задать параметры подсказки → Вкладка Сообщение об ошибке → Выбрать вид действия при попытке ввода неверных данных → Ввести текст сообщения об ошибке → ОК.

Рис. 13.5 Диалоговое окно задания параметров проверки вводимых значений

в ячейки таблицы

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

ü ЗАДАНИЕ. Задайте проверку вводимых значений для оклада сотрудников предприятия от 10000 до 50000.

Примечание. Следует отметить, что если поле списка содержит формулу, то в форме выводится ее результат. Изменять это поле в форме нельзя. При изменении записи, содержащей формулу, результат формулы не будет вычислен до нажатия клавиши [Enter] или кнопки Закрыть.

Рис. 13.6 Диалоговое окно формирования подсказки, предваряющей ввод данных в ячейку

Рис. 13.7 Диалоговое окно формирования сообщения об ошибке при введении данных в ячейку таблицы

Критерии Добавить

Очистить

Вернуть

Назад

Далее

Правка

Закрыть

Поиск записей

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

Критерий поиска вводится в одно или несколько полей, по которым нужно найти совпадение. Например, из списка требуется найти сотрудника, фамилия которого начинается на букву «М» и оклад более 15 000 руб. (рис. 13.8).

Просмотр результатов поиска осуществляется по кнопке Далее. При этом Excel отобразит форму данных с первой найденной записью (рис. 13.9).

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

Сортировка записей

Сортировка — упорядочивание информации в списке в соответствии со значением или с типом информации.

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

Следует различать сортировку, производимую по одному столбцу, и сортировку по нескольким столбцам.

В первом случае записи сортируются на основании выбранного поля по возрастанию (или убыванию) значений в этом поле независимо от места расположения данного поля.

Рис. 13.8 Окно формы в ходе задания критерия выборки записей из списка

Рис. 13.9 Окно формы с записью списка, удовлетворяющей критерию

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

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

Рис. 13.10 Окно задания параметров сортировки диапазона

 

Существует два типа упорядочения:

- по возрастанию - числа располагаются в порядке увеличения от наименьшего к наибольшему; текст сортируется в алфавитном порядке; даты - от наиболее ранней даты к самой поздней дате;

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

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

Пример 1

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

Рис. 13.11 Фрагмент базы данных торгового предприятия

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

«Ф.И.О.» - в алфавитном порядке.

«Дата посещения» - в возрастанию;

«Прибыль» - по убыванию;

Технология выполнения сортировки записей базы данных (списка Excel)

заключается в следующем.

1) Выделите любую ячейку списка (например, ВЗ).

2) Выберите команды: Данные → Сортировка.

3) В диалоговом окне Сортировка диапазона (рис. 2.23) выберите из списка Сортировать по имя поля, по которому необходимо отсортировать записи в первую очередь, в нашем случае таким полем будет «Ф.И.О.». Установите для данного поля порядок сортировки По возрастанию.

Рис. 13.12 Диалоговое окно Сортировка диапазонов

4) В поле Затем по выберите поле для вторичной сортировки («Дата посещения»), установите переключатель По убыванию.

5) Укажите третье поле («Прибыль») для сортировки в списке В последнюю очередь, по и выберите порядок сортировки По возрастанию.

6) Для закрытия диалогового окна щелкните по кнопке ОК.

В результате база данных будет отсортирована в соответствии с заданными параметрами.

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

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

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

Рис. 13.13 Окно Параметры сортировки

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

Кроме того, можно определить направления сортировки по строкам или столбцам.

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

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

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

Возможности фильтрации

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

Microsoft Excel предоставляет две команды фильтрации: Автофильтр, предназначенный для простых критериев, и Расширенный фильтр, использующийся для более сложных критериев.

ü Создайте базу данных товаров (см. табл. 13.2)

Таблица 13.2

Код товара	Наименование товара	Модель	Цена
	Видеокамера цифровая	Panasonic NV-GS76GCS	16 990
	Видеокамера цифровая	Samsung VР-D455 i	12 990
	Видеокамера цифровая	Samsung VР-D 462 Bi	11 990
	Видеокамера цифровая	Panasonic NV-GS6EES	8 990
	Микроволновая печь	Panasonic NN-C2003GZPE	16 490
	Микроволновая печь	Samsung С-100/R	4 490
	Мобильный телефон	Nokia 8800	39 999
	Мобильный телефон	Voxtel 3ID	13 999
	Ноутбук	Toshiba Satellite L10-194	44 990
	Ноутбук	ASUS A6Q	43 990
	Ноутбук	ASUS A3L	28 990
	Ноутбук	Compaq Р0840Е5 пэс6110	23 990
	Принтер лазерный	НР LJ1020	5 290
	Принтер струйный	ЕРSON SТ РНOТО R200	4 490
	Телевизор 21-40 см	Toshiba 15СS72R	4 590
	Телевизор 21-40 см	Panasonic 15РМ50	3 990
	Телевизор 21-40 см	Philips 14РТ-1347/01	3 690
	Телевизор 21-40 см	Toshiba 14CJ1RS	2 790
	Телевизор 21-40 см	Rolsen C 1470	2 390
	Телевизор 54-55 см	Rolsen С 21S880	5 490
	Телевизор 54-55 см	Panasonic ТС-21Z8ORQ	4 990
	Телевизор 54-55 см	Toshiba 21 СS1R	3 990
	Телевизор 54-55 см	Витязь 54 CTV
	Телевизор 64-82 см	Panasonic ТX-29Р580	12 990
	Телевизор 64-82 см	Philips 29РТ-307	11 990
	Телевизор 64-82 см	Panasonic ТX-25FJ20T	10 690
	Телевизор 64-82 см	Rolsen C26R21	6 990
	Телевизор LCD (16:9)	Toshiba 32WL55	74 990
	Телевизор LCD (16:9)	Philips 20PF4111	54 990
	Телевизор LCD (4:3)	Philips 32PF3320	21 980

Автофильтр

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

Установить курсор на любой ячейке списка → Данные →Фильтр → Автофильтр

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

Все — отображает все содержащиеся в столбце элементы; Первые 10... — отображает первые 10 строк с максимальными или минимальными значениями ячеек текущего поля.; Условие... — отображает элементы, удовлетворяющие одному или двум условиям.

Последний критерий (Условие) используется при задании пользовательского автофильтра (рис. 13.16). При этом задается критерий отбора, состоящий из двух условий, связанных между собой логическими операторами И или ИЛИ. Каждое из условий включает две части. В первой части задаются операторы сравнения (равно, не равно, больше, больше или равно, меньше, меньше или равно) или принадлежности (начинается с, не начинается с, заканчивается на, не заканчивается на, содержит, не содержит).

Рис. 13.14 Фрагмент окна списка при задании автофильтра с раскрытым меню отбора значений поля

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

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

Рис. 13.16 Диалоговое окно Пользовательский автофильтр

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

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

Расширенный фильтр

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

Диапазон критериев должен соответствовать следующим требованиям:

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

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

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

- Значения условий, заданных в разных строках, связаны опера
тором ИЛИ.

- Пустая строка критерия означает, что условия отбора нет.

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

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

После того как создан диапазон условий, можно использовать средства расширенного фильтра. Для этого следует последовательно выполнить операции:

Установить курсор в любую ячейку списка → Команда Данные → фильтр → Расширенный фильтр

В итоге на экране появится одноименное диалоговое окно (рис. 13.17), в котором следует указать необходимые данные.

Рис. 13.17 Диалоговое окно Расширенный фильтр

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

Затем в текстовом поле Исходный диапазон следует задать адрес обрабатываемой базы данных (путем выделения всех ее ячеек, включая заголовки полей). Этот адрес, как правило, устанавливается по умолчанию, и пользователь может его подтвердить либо изменить по своему усмотрению.

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

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

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

Примечания

1) При использовании расширенного фильтра целесообразнее помещать диапазоны условий выше и правее исходной базы данных, если она содержит не более 10-20 столбцов и не более 200-250 записей. В противном случае диапазон условий целесообразнее размещать на новом листе.

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

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