Категории:

Астрономия
Биология
География
Другие языки
Интернет
Информатика
История
Культура
Литература
Логика
Математика
Медицина
Механика
Охрана труда
Педагогика
Политика
Право
Психология
Религия
Риторика
Социология
Спорт
Строительство
Технология
Транспорт
Физика
Философия
Финансы
Химия
Экология
Экономика
Электроника

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

ПРАКТИЧЕСКОЕ ЗАНЯТИЕ №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 Пример заполнения таблицы «Маршруты»

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

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

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