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

ТЕХНОЛОГИЯ ОБРАБОТКИ И АНАЛИЗА ЭКОНОМИЧЕ-СКОЙ ИНФОРМАЦИИ

 

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

 

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

 

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

 

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

 

 

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

 

 

Строки таблицы называются записями базы данных, а столбцы – полями. Таким образом, запись представляет собой набор полей одного объекта, а поле

 

– это определенная категория информации. Первая строка таблицы в этом слу-чае является названием полей списка.

 

Например, на рис. 2.7 приведен фрагмент списка о сотрудниках некоторого предприятия.

 

 

 

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

 

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

 

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

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

 

· размер списка не может превышать размеры листа Excel, т.е. число столбцов не может быть более 256, а число строк – 65536;

 

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

 

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

 

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

 

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

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

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

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

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

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

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

 

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

 

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

формы данных.

 

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

 

 

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

 

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

 

 

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

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

Выделить ячейку с начальным значением последовательности →

 

Команда Правка → Заполнить → Прогрессия...¹→

 

Задать расположение последовательности: по строкам или по столбцам → Задать значение шага → Выбрать тип последовательности → Для последовательности Даты выбрать единицы измерения:

 

день, рабочий день, месяц, год →

 

Задать предельное значение последовательности → Кнопка ОК

 

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

 

 

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

 

Для запрета автозаполнения и реализации только операции копирования следует при перетаскивании маркера заполнения удерживать клавишу [Ctrl].

 

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

Команда Сервис → Параметры… → Вкладка Правка).

 

Как только ниже в списке будут введены символы, позволяющие однознач-но идентифицировать значение элемента списка, оно появляется в ячейке. Ав-томатически дополненные символы выделены. Нажатие клавиши [Enter] пред-писывает поместить сформированное значение в ячейку. Продолжение набора символов предполагает ввод в ячейку нового элемента списка.

 

 

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

 

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

 

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

 

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

 

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

Вкладка Сообщение об ошибке⁴→

 

Выбрать вид действия при попытке ввода неверных данных → Ввести текст сообщения об ошибке → ОК

 

 

¹ Выделять следует как заполненные, так и пустые ячейки столбца или строки, в кото-рые предполагается ввод однотипных данных.

² Окно вкладки Параметры показано на рис. 2.11.

 

³ Окно вкладки Сообщение для ввода показано на рис. 2.12. ⁴ Окно вкладки Сообщение об ошибке показано на рис. 2.13.

 

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

 

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

 

 

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

 

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

 

Примечания.

 

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

 

 

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

 

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

 

 

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

 

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

 

Просмотр следующей записи также выполняется по кнопке Далее. Для возврата к предыдущей записи используется кнопка Назад.

 

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

 

Кнопка Критерии → Кнопка Очистить → Ввести новый критерий

 

→ Кнопка Далее

 

 

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

 

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

 

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

 

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