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

ПРАКТИЧЕСКОЕ ЗАНЯТИЕ №2

СПИСКИ И БАЗЫ ДАННЫХ В EXCEL.

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

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

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

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

В качестве примера я предлагаю Вам создать небольшую и несложную базу под названием «Буро путешествий». База будет состоять из трех основных таблиц: Клиенты агентства, маршруты и Поездки.

Рассмотрим первую таблицу – «Клиенты». На рисунке 1 приведён фрагмент этой таблицы.

Рис.1 Окно Excel со списком клиентов нашего бюро путешествий.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ТЕХНОЛОГИЯ СОЗДАНИЯ СПИСКОВ

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

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

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

Установить курсор в пределах списка → Данные → Форма. Если не нашли на закладке Данные пункт «Форма», то можно поместить его на панель инструментов самостоятельно. Для этого используем КЗМ по панели инструментов àпанели быстрого доступа (рис.2).

РИС. 2 Настройка состава кнопок на панели быстрого доступа

Там выбираем «Все команды», по алфавиту находим «Форма» и перетаскиваем значок на панель быстрого доступа (показано на рисунке2 черной стрелкой).

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

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

В левой части формы располагаются заголовки столбцов, т.е. имена полей списка, и поля ввода и хранения соответствующих значений. Одновременно в форме может выводиться до 32 полей списка. В правом верхнем углу формы находится индикатор номера текущей записи (строки таблицы) и количество записей в списке, без учета строки заголовка. Справа также представлены кнопки управления списком. Значения всех кнопок понятны. Единственная кнопка, значение которой способно вызвать затруднение – это кнопка «Критерии». Она означает «Переход в режим задания критерия поиска данных в списке». Нажмите на нее. Все поля станут пустыми, в нужное поле можно ввести какое-то условие отбора. Например, в поле ФИО введем «Потап». У нас в списке должно получиться лишь 4 человека – это семья Потаповых. Можете сами поэкспериментировать с заданием условий отбора записей.

Теперь введем некоторые подсказки для того, кто будет данную таблицу заполнять. Например, в поле телефон будем вводить только сотовые телефоны без 8 и дефисов, а даты будем вводить без точек. При этом нам необходимо, чтобы отображались эти данные в правильном формате. Телефон в виде +7(924)3727890, а даты рождения – 12.04.2011.

Чтобы этого добиться для каждого из соответствующих столбцов откроем окно «Формат ячеек» и введем нужную маску (рис.4).

РИС.4 Задание маски для ввода данных в ячейки

А чтобы пользователю было понятнее, в каком виде данные следует вводить, мы сделаем ему подсказку. Вкладка «Данные» àПроверка данных. Для столбца «Телефоны» - первую, для столбца «Дата рождения» - вторую (рис.5).

РИС. 5 Задание подсказок пользователю при вводе данных

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

Выделим один из листов Excel в этом же документе под список документов. Введем туда нужные нам документы. Выделим наш список и выберем пункт «Присвоить имя» вкладки «Формулы». Присвоим этому списку имя «Документы» (рис.6).

РИС. 6 Присвоение диапазону данных определенного имени

 

А далее на листе «Клиенты» выделаем столбец «Документ» и открываем окно «Проверка данных» на вкладке «Данные» (рис.7).

РИС. 7 Создание в ячейке открывающегося списка из элементов определенного диапазона

На вкладке «Параметры» появившегося окна выбираем Тип данных – список, а источник – «=Документы» (рис.8).

РИС. 8 Задание имени диапазона, из элементов которого будет состоять список

Теперь осталось только снять эти параметры для заголовка нашей таблицы. В остальных ячейках столбца «Документ» теперь можно пользоваться раскрывающимся списком.

Теперь заполним таблицу «Маршруты» на одноименном листе Excel. Здесь главная трудность будет заключаться в том, чтобы при выборе страны с помощью аналогичного раскрывающегося списка, в поле «Города» был список городов именно этой страны (рис.12).

РИС. 12 Фрагмент таблицы «Маршруты», показывающий список городов выбранной в первом столбце Страны

Выполнение этой задачи предлагается следующее: создаем отдельный лист «Страны». Заполняем его следующим образом (рис.13):

РИС. 13 На отдельном листе «Страны» задается список стран по столбцам, а под каждым столбцом города или курорты соответствующей страны

Теперь выделяем диапазон стран, присваиваем ему имя «Страны». Выделяем диапазон городов каждой страны, присваиваем каждому из них имя – имя страны (оно должно быть точно так же, как название страны).

Теперь выделяем столбец «Страна», выбираем «Проверку данных» на вкладке «Данные», выбираем тип данных – список и источник – «=Страны». А для столбца «Город» источник – «=двссыл($A3)». Эта функция ссылается на данные с именем, записанным в ячейке А3 ()рис.14.

РИС. 14 Слева приведены настройки списка для столбца «Страна», а справа – для столбца «Город»

Теперь можно заполнить нашу таблицу «Маршруты» определенным количеством записей. Например, такими (рис.15):

РИС. 15 Пример заполнения таблицы «Маршруты»

Сделайте текущим лист «Клиенты». После заполнения данных о клиентах нашего бюро можно попробовать использование автофильтра. Для этого на вкладке «Данные» выберем пункт «Фильтр». У заголовков нашей таблицы справа появились знаки , при нажатии на которые можно выбрать только тех клиентов, данные о которых удовлетворяют нужным критериям. Выберите клиентов, фамилия которых начинается с символов «Пот». Результат фильтрования будет следующим:

Чтобы отменить фильтр, надо снова нажать на и выбрать пункт «Снять фильтр».

Пока все. Продолжение создания этой базы ждет Вас в следующих лабораторных работах.