Базы данных Excel. Список. Сортировка записей Понятие о списке (базе данных Excel) Электронные таблицы Excel можно использовать для организации работы с небольшими реляционными базами данных. В этом случае электронную таблицу называют списком или базой данных Excel (рисунок 1) и используют соответствующую терминологию: - строка списка – запись базы данных; - столбец списка – поле базы данных.Название столбца может занимать только одну ячейку и при работе с таблицей как с базой данных называется именем поля. Все ячейки строки с именами полей образуют область имен полей, которая занимает только одну строку.
Данные всегда располагаются, начиная со следующей строки после области имен полей. Весь блок ячеек с данными называют областью данных.Для размещения имени поля списка (рисунок 1) необходимо : - выделить ячейку или всю строку, где будут располагаться имена полей; Рисунок 1 – Структурные элементы списка (базы данных Excel) - выполнить команду Формат ячеек и выбрать вкладку Выравнивание; - на вкладке установить следующие параметры: по горизонтали: по значению или по центру, по вертикали: по верхнему краю или по центру.
Отображение: установить флажок переносить по словам. Над записями списка можно выполнять различные операции обработки, команды вызова которых сгруппированы в меню Данные.Для того чтобы электронная таблица воспринималась системой как список, необходимо соблюдать описанные выше правила и перед выполнением операций обработки установить курсор внутри этой таблицы.
В этом случае при вводе команды обработки из меню Данные весь список (имена полей и записи, см. рисунок 1) будет выделен темным цветом. Когда список сформирован неверно или нужно работать с частью области списка, область списка надо выделить вручную с помощью мыши. Excel предоставляет возможности для работы с базами данных различных форматов, которые при открытии в среде Excel автоматически преобразуются в список. Такое преобразование называют импортом.Данные в электронную таблицу можно включить не только путем импорта из «чужой» базы данных, но и посредством запросов данных, адресованных тому или иному серверу баз данных.
Такие запросы формируются специальной программой MS Query, вызываемой по команде Данные  Внешние данные  Создать запрос (в Excel 2003 меню Внешние данные называется Импорт внешних данных, в Excel 2007 – Получить внешние данные). Результат запроса возвращается в электронную таблицу в виде списка.Сортировка данных в списке Сортировка данных является базовой операцией любой таблицы и выполняется командой Данные  Сортировка с установкой необходимых параметров.
Целью сортировки является упорядочивание данных. Сортировка осуществляется на том же листе. Особенно важно осуществлять сортировку в списке, так как многие операции группировки данных, которые доступны из меню Данные, можно использовать только после проведения операции сортировки.В среде Excel 2003 предусмотрены три уровня сортировки, которые определяются в диалоговом окне «Сортировка диапазона» (рисунок 2, а) параметром Сортировать по. Рисунок 2 – Диалоговые окна Excel 2003 для операции сортировки: а – сортировка диапазона; б – параметры сортировки В Excel 2007 количество уровней сортировки неограниченно (рисунок 3). Первоначально предлагается 1 уровень, но с помощью кнопки <Добавить уровень> можно увеличить их число до необходимого (в нашем примере – до 3). Сначала осуществляется сортировка в столбце 1-го уровня, затем сортируются одинаковые записи 1-го столбца по столбцу 2-го уровня, затем сортируются одинаковые записи 2-го столбца по столбцу 3-го уровня.
В том же окне устанавливается порядок сортировки в столбцах – по возрастанию или убыванию.
При сортировке по возрастанию упорядочение осуществляется от меньшего к большему, по алфавиту или в хронологическом порядке дат, но при этом имеет место приоритет: числа, текст, логические значения, значения ошибок, пустые ячейки.Рисунок 3 – Диалоговое окно Excel 2007 для операции сортировки Сортировка по убыванию использует обратный порядок (исключение – пустые ячейки, которые и в этом случае располагаются в конце списка). В Excel 2007 наименование опции возрастания или убывания порядка упрощено и зависит от типа данных в столбце (например, для текста – «От А до Я»). При наличии заголовков столбцов (имен полей) их следует исключить из области, подлежащей сортировке, установкой флажка переключателя Идентифицировать поля по подписям (в Excel 2007 – Мои данные содержат заголовки). Кнопка <Параметры> выводит диалоговое окно «Параметры сортировки», в котором задаются дополнительные установки сортировки (рисунок 2, б): с учетом регистра или без учета; по столбцам или по строкам; порядок сортировки (отсутствует в Excel 2007) – обычный или специальный, выбранный из предлагаемого списка. В Excel 2003 этот список можно сформировать самостоятельно с помощью Сервис  Параметры, вкладка Списки.
Основные технологические операции по сортировке данных Действие Содержание действия Сортировка списка 1. Установить курсор в области списка 2. Выполнить команду Данные  Сортировка 3. Указать порядок и направление сортировки для каждого сортировки ключа 4. Нажать кнопку <Параметры> и выбрать параметры (порядок по первому ключу, учет регистра, направление сортировки – по строкам или по столбцам) сортировки Создать новый список для сортировки 1. Выполнить команду Сервис  Параметры, вкладка Списки 2. Нажать кнопку <Добавить> 3. Сформировать элементы списка Изменить список для сортировки 1. Выполнить команду Сервис  Параметры, вкладка Списки 2. Выделить в окне Списки начало редактируемого списка 3. Перейти к элементам списка и отредактировать их (добавить, удалить, отредактировать) 4. Нажать кнопку <ОК> Удалить список для сортировки 1. Выполнить команду Сервис  Параметры, вкладка Списки 2. Выделить в окне Списки начало редактируемого списка 3. Нажать кнопку <Удалить> Использование автофильтра Автофильтр является простым и удобным средством Excel для избирательного отображения записей.
С его помощью можно задать определенные условия, для того чтобы на рабочем листе отображались не все записи базы данных Excel, а только те, которые удовлетворяют этим условиям. Создать/отменить автофильтр можно командой Данные  Фильтр  Автофильтр (в Excel 2007 – Данные  <Фильтр>). ЗАДАНИЕ 1. Проделайте подготовительную работу: создайте книгу и сохраните ее под именем Spisok, переименуйте Лист1 на Список, Лист2 – на Сортировка, Лист3 – на Автофильтр. 2. В новой рабочей книге на листе Список создайте таблицу, приведенную на рисунке 4. Рисунок 4 – Пример списка (базы данных) 3. Скопируйте табличную базу данных с листа Список на лист Сортировка. 4. Проведите трехуровневую сортировку по возрастанию: по преподавателям, по номеру группы, по коду предмета. 5. Создайте копию таблицы и выполните сортировку по другим полям. 6. Скопируйте табличную базу данных с листа Список на лист Автофильтр. 7. Изучите самостоятельно возможности средства Автофильтр.
Методика выполнения работы 1. Проведите подготовительную работу: - создайте новую рабочую книгу командой Файл  Создать.
Укажите шаблон – Книга; - сохраните созданную рабочую книгу под именем Spisok командой Файл  Сохранить как; - переименуйте Лucm1 на Список, - переименуйте Лист2 на Сортировка, - переименуйте Лист2 на Автофильтр. 2. Сформируйте на листе Список шапку таблицы (см. рисунок 4). Имена столбцов шапки будут в дальнейшем играть роль имен полей списка (базы данных). Следует помнить, что имя каждого поля должно занимать одну ячейку.
Для этого: - выделите первую строку; - вызовите контекстное меню и выберите команду Формат ячеек; - произведите форматирование ячеек первой строки, установив параметры на вкладке Выравнивание: По горизонтали: по значению.
По вертикали: по верхнему краю. Переносить по словам: установить флажок. - введите названия столбцов (имен полей) в соответствии с отображенной на рисунке 4 таблицей; - заполните таблицу данными.
Скопируйте базу на лист Сортировка.
Для этого: - выделите список, начиная от имен полей и вниз до конца записей таблицы; - выполните команду Правка  Копировать (в Excel 2007 – Главная  кнопка <Копировать>); - откройте лист Сортировка и выделите левую верхнюю ячейку; - выполните команду Правка  Вставить (в Excel 2007 – Главная  кнопка <Вставить>). Выполните сортировку по столбцу Таб. № препод. Для этого: - установите курсор в поле списка и введите команду Данные  Сортировка.
При этом должна выделиться вся область списка.
Если этого не произошло, то предварительно выделите весь список, а затем введите указанную команду; - в диалоговом окне «Сортировка диапазона» установите: Сортировать по: поле «Таб. № препод», по возрастанию. Затем по: поле «Номер группы», по возрастанию.
В последнюю очередь по: поле «Код предмета», по возрастанию. - установите флажок Идентифицировать поля по подписям. 3. Создайте копию таблицы на том же листе.Для этого: - выделите таблицу; - выполните команду Правка  Копировать (в Excel 2007 – Главная  кнопка <Копировать>); - выделите любую ячейку ниже таблицы; - выполните команду Правка  Вставить (в Excel 2007 – Главная  кнопка <Вставить>). 4. Выполните сортировку по каким-либо другим полям. 5. Скопируйте базу с листа Список на лист Автофильтр (аналогично копированию на лист Сортировка). 6. Выделите заголовочную часть таблицы (имена полей базы данных). Выполните команду Данные  Фильтр  Автофильтр (в Excel 2007 – Данные  кнопка <Фильтр>). Изучите самостоятельно возможности данного средства. 7. Продемонстрируйте созданную книгу преподавателю.