* Данная работа не является научным трудом, не является выпускной квалификационной работой и представляет собой результат обработки, структурирования и форматирования собранной информации, предназначенной для использования в качестве источника материала при самостоятельной подготовки учебных работ.
ПРАКТИЧЕСКОЕ ЗАНЯТИЕ №1
Базы данных Excel. Список. Сортировка записей
Понятие о списке (базе данных Excel )
Электронные таблицы Excel можно использовать для организации работы с небольшими реляц и онными базами данных. В этом случае электронную таблицу называют списком или базой данных E x cel (рисунок 1) и используют соответствующую терминол о гию:
- строка списка – запись базы данных;
- столбец списка – поле базы данных .
Название столбца может занимать только одну ячейку и при работе с табл и цей как с базой данных называется именем поля. Все ячейки строки с именами полей образуют о б ласть имен полей, которая занимает только одну строку.
Данные всегда располагаются, начиная со следующей строки после области имен по лей. Весь блок ячеек с данными называют областью данных. Для разм е щения имени поля списка (рисунок 1) необходимо В Excel 2007 такое форматирование необязательно и носит скорее рекомендательный характер. :
- выделить ячейку или всю строку, где будут располагаться имена полей;
Рисунок 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 командой Файл а Сохр а нить как;
- переименуйте Л ucm 1 на Список ,
- переименуйте Лист2 на Сортировка ,
- переименуйте Лист2 на Автофильтр .
2. Сформируйте на листе Список шапку таблицы (см. рисунок 4). Имена столбцов шапки будут в дальнейшем играть роль имен полей списка (базы данных). Следует по м нить, что имя каждого поля должно занимать одну ячейку. Для этого:
- выделите первую строку;
- вызовите контекстное меню и выберите команду Формат ячеек;
- произведите форматирование ячеек первой строки, установив параметры на вкладке В ы равнивание:
По горизонтали: по значению.
По вертикали: по верхнему краю.
Переносить по словам: установить флажок.
- введите названия столбцов (имен полей) в соответствии с отображенной на рисунке 4 та б лицей;
- заполните таблицу данными.
Скопи руйте базу на лист Сортировка. Для этого:
- в ыделите список, начиная от имен полей и вниз до конца записей таблицы;
- выполните команду Правка а Копировать
(в Excel 2007 – Главная а кнопка <Копировать>) ;
- откройте лист Сортировка и выделите левую верхнюю ячейку;
- выполните команду Правка а Вставить
(в Excel 2007 – Главная а кнопка <Вставить>) .
Выполните сортировку по столбцу Таб. № препод. Для этого:
- установите курсор в поле списка и введите команду Данные а Сортировка . При этом должна выделиться вся область списка. Если этого не произошло, то пред вар и тельно выделите весь список, а затем введите указанную команду;
- в диалоговом окне «Сортировка диапазона» установите:
Сортировать по: поле «Таб. № препод», по возрастанию.
Затем по : поле «Номер группы», по возрастанию.
В последнюю очередь по: поле «Код предмета», по возрастанию.
- установите флажок Идентифицировать поля по подписям.
3. Создайте копию таблицы на том же листе. Для этого:
- выделите таблицу;
- выполните команду Правка а Копировать
(в Excel 2007 – Главная а кнопка <Копировать>) ;
- выделите любую ячейку ниже таблицы;
- выполните команду Правка а Вставить
(в Excel 2007 – Главная а кнопка <Вставить>) .
4. Выполните сортировку по каким-либо другим полям.
5. Скопи руйте базу с листа Список на лист Автофильтр ( аналогично копированию на лист Со р тировка ) .
6. Выделите заголовочную часть таблицы (имена полей базы данных). Выполните команду Да н ные а Фильтр а Автофильтр (в Excel 2007 – Данные а кнопка <Фильтр>). Изучите самосто я тельно возможности данного средства.
7. Продемонстрируйте созданную книгу преподавателю.