Основные технологические операции по сортировке данных
ФЕДЕРАЛЬНОЕ АГЕНТСТВО ПО ОБРАЗОВАНИЮ
ГОУВПО
«Воронежская государственная технологическая академия»
КАФЕДРА МАТЕМАТИЧЕСКОГО МОДЕЛИРОВАНИЯ
ИНФОРМАЦИОННЫХ И ТЕХНОЛОГИЧЕСКИХ СИСТЕМ
ОБРАБОТКА ДАННЫХ В EXCEL
Методические указания и задания к практическим и
лабораторным занятиям по курсу “Информатика”
Для студентов всех направлений,
Дневной формы обучения
ВОРОНЕЖ
УДК 681.3.06
Обработка данных в Excel [Текст] : метод. указания и задания к практическим и лабораторным занятиям по курсу “Информатика” / Воронеж. гос. технол. акад.; сост. С. В. Глущенко, Н. Д. Писаренко, С. В. Чикунов, Б. Е. Никитин, И. И. Каширская. – Воронеж: ВГТА, 2007. - 31 с.
Методические указания разработаны в соответствии с требованиями ГОС ВПО подготовки выпускников по всем направлениям. Они предназначены для закрепления теоретических знаний дисциплин цикла ЕН и содержат сведения о способах и методах работы с данными в Excel, с созданием консолидированных групп данных.
Библиогр.: 5 назв.
Составители: доценты С. В. ГЛУЩЕНКО, Н. Д. ПИСАРЕНКО,
С. В. ЧИКУНОВ, Б. Е. НИКИТИН,
ассистент И. И. КАШИРСКАЯ.
Научный редактор профессор Г. В. АБРАМОВ
Рецензент профессор Центрального филиала Российской
академии правосудия В. К. ГОЛИКОВ
Печатается по решению
редакционно-издательского совета
Воронежской государственной технологической академии
Ó Глущенко С. В., Писаренко Н. Д,
Чикунов С. В., Никитин Б. Е.,
Каширская И. И., 2007
Ó ГОУВПО «Воронеж. гос.
технол. акад.», 2007
Оригинал-макет данного издания является собственностью Воронежской государственной технологической академии, его репродуцирование (воспроизведение) любым способом без согласия академии запрещается.
МЕТОДИЧЕСКИЕ УКАЗАНИЯ
Список. Сортировка Данных
Понятие о списке (базе данных Excel)
Для обработки данных с использованием возможностей СУБД, можно рекомендовать те средства, которыми располагает табличный процессор Microsoft Excel.
Электронные таблицы Excel можно использовать для организации работы с небольшими реляционными базами данных. В этом случае электронную таблицу называют списком или базой данных Excel. Предоставляются различные средства по сортировке данных, их фильтрации по определенным критериям, а также автофильтрации. Методы консолидации данных позволяют различными способами агрегировать данные.
Списки (рис. 1) используют терминологию:
· строка списка - запись базы данных;
· столбец списка - поле базы данных.
Название столбца может занимать только одну ячейку и при работе с таблицей как с базой данных называется именем поля. Все ячейки строки с именами полей образуют область имен полей, которая занимает только одну строку. Данные всегда располагаются, начиная со следующей строки после области имен полей. Весь блок ячеек с данными называют областью данных. Список содержит фиксированное количество полей (столбцов), определяющих структуру записи базы данных (строки). Верхняя строка списка содержит имена полей (названия столбцов). Имя поля может состоять из нескольких слов любого алфавита. Обязательное требование - размещение в одной ячейке.
Список (база данных Excel) - электронная таблица, в которой строки (записи) имеют фиксированную структуру, а имена столбцов (полей) занимают одну строку.
Для размещения имени поля списка в одной ячейке
(рис. 1) необходимо:
· выделить ячейку или всю строку, где будут располагаться имена полей;
· ввести командуФормат, ячеек и выбрать вкладку Выравнивание;
· на вкладке установить следующие параметры:
По горизонтали: по значению;
По вертикали: по верхнему краю или по центру;
Отображение: установить флажок переносить по словам.
Рис. 1.Список в Excel
Над записями списка можно выполнять различные операции обработки, команды вызова которых сгруппированы в менюДанные. Для того чтобы электронная таблица воспринималась системой как список, необходимо соблюдать описанные выше правила и перед выполнением операций обработки установить курсор внутри этой таблицы. В этом случае пру вводе команды обработки из менюДанные весь список (имена полей и записи, см.. рис. 1) будет выделены темным цветом. Когда список сформирован неверно или нужно работать с частью области списка, область списка надо выделить вручную с помощью мыши.
Excel предоставляет возможности для работы с базами данных различных форматов, которые при открытии в среде Excel автоматически преобразуются в список. Такое преобразование называют импортом. Данные в электронную таблицу можно включить не только путем импорта из "чужой" базы данных, но и посредством запросов данных, адресованных тому или иному серверу баз данных. Такие запросы формируются специальной программой MS Query, вызываемой по командеДанные, Внешние данные. Создать запрос. Результат запроса возвращается в электронную таблицу в виде списка.
Сортировка данных в списке
Сортировка данных является базовой операцией любой таблицы и выполняется командой Данные, Сортировка с установкой необходимых параметров (таблица). Целью сортировки является упорядочивание данных. Сортировка осуществляется на том же листе.
В среде Excel предусмотрены три уровня сортировки, которые определяются в диалоговом окне <Сортировка диапазона> (рис. 2, а) параметром Сортировать по.
Сначала осуществляется сортировка в столбце 1-го уровня, затем сортируются одинаковые записи 1-го столбца по столбцу 2-го уровня, затем сортируются одинаковые записи 2-го столбца по столбцу 3-го уровня.
В том же окне устанавливается порядок сортировки в столбцах - по возрастанию или убыванию. При сортировке по возрастанию упорядочение идет следующим образом - от меньшего к большему, по алфавиту или в хронологическом порядке дат, но при этом имеет место приоритет: числа, текст, логические значения, значения ошибок, пустые ячейки. Сортировка по убыванию использует обратный порядок (исключение - пустые ячейки, которые располагаются в конце списка),
При наличии заголовков столбцов (имен полей) их следует исключить из области, подлежащей сортировке, установкой флажка переключателя Идентифицировать поля по подписям.. Кнопка <Параметры> выводит диалоговое окно <Параметры сортировки>, в котором задаются дополнительные установки сортировки (рис. 2, б): с учетом регистра или без учета; по столбцам или по строкам; порядок сортировки - обычный или специальный, выбранный из предлагаемого списка. Этот список можно сформировать самостоятельно с помощьюСервис, Параметры, вкладка Списки.
а
б 
Рис. 2. Диалоговые окна для операции сортировки:
а - сортировка диапазона; б - параметры сортировки
Таблица
Основные технологические операции по сортировке данных
| Действие | Содержание действия |
| Сортировка списка | 1. Установить курсор в области списка 2. Выполнить команду Данные, Сортировка 3. Указать порядок и направление сортировки для каждого ключа сортировки 4. Нажать кнопку <Параметры> и выбрать параметры сортировки (порядок по первому ключу, учет регистра, направление сортировки — по строкам или по столбцам) |
| Создать новый список для сортировки | 1. Выполнить команду Сервис, Параметры, вкладка Списки 2. Нажать кнопку <Добавить> 3. Сформировать элементы списка |
| Изменить список для сортировки | 1. Выполнить команду Сервис, Параметры, вкладка Списки 2. Выделить в окне Списки начало редактируемого списка 3. Перейти к элементам списка и отредактировать их (добавить, удалить, отредактировать) 4. Нажать кнопку <ОК> |
| Удалить список для сортировки | 1. Выполнить команду Сервис, Параметры, вкладка Списки 2. Выделить в окне Списки начало редактируемого списка 3. Нажать кнопку <Удалить> |
ЗАДАНИЕ 1
1. Проделайте подготовительную работу: создайте книгу и сохраните ее под именем Spisok, переименуйте Лист1 на Список, а Лист2 - на Сортировка.
2. В новой рабочей книге на листе Список создайте таблицу, приведенную на рис. 3.
3. Произведите копирование списка (базы данных) с листа Список на лист Сортировка.
4. Сделайте сортировку на трех уровнях по возрастанию: по преподавателям, по номеру группы, по коду предмета.
5. Выполните сортировку по другим полям.
Выполнение работы
1. Проведите подготовительную работу:
· создайте новую рабочую книгу командой Файл, Создать. Укажите шаблон Книга;
· сохраните созданную рабочую книгу под именем Spisok командой Файл; Сохранить как;
· переименуйте Лист 1 на Список;
· переименуйте Лист1 на Сортировка.
2. Сформируйте на листе Список шапку таблицы (рис. 3). Имена столбцов шапки будут в дальнейшем играть роль имен полей базы данных (списка). Следует помнить, что имя каждого поля должно занимать одну ячейку. Для этого:
· выделите первую строку;
· вызовите контекстное меню и выберите команду Формат ячеек;
· произведите форматирование ячеек первой строки, установив параметры вкладке Выравнивание:
По горизонтали: по значению;
По вертикали: по верхнему краю;
Переносить по словам: установить флажок;
· введите названия столбцов (имен полей) в соответствии с отображенной на рис. 3 таблицей;
· заполните таблицу данными.
3. Выделите список, начиная от имен полей и вниз до конца записей таблицы, и скопируйте их на лист Сортировка.
4. Выполните сортировку по столбцу Таб. № препод. Для этого:
· установите курсор в поле списка и введите командуДанные ® Сортировка. При этом должна выделиться вся область списка. Если этого не произошло, то предварительно выделите весь список, а затем введите указанную команду;
· в диалоговом окне <Сортировка диапазона> установите:
5. Сортировать по: поле <Таб. № препод.>, по возрастанию. Затем по: поле <Номер группы>, по возрастанию.
В последнюю очередь по: поле <Код предмета>, по возрастанию;
· установите флажок Идентифицировать поля по подписям.
6. Выполните сортировку по другим полям.
Рис. 3. Пример списка (базы данных)
Фильтрация ()выборка) данных
Фильтрация данных в списке ( выбор данных по заданному критерию - условию) осуществляется с помощью командыДанные, Фильтр. Имеются две разновидности этой команды, задаваемые параметрами:Автофильтр и Расширенный фильтр.Фильтрация данных может осуществляться с помощью специальной формы, которая вызывается командойДанные, Форма.
Автофильтрация
КомандаДанные, Фильтр, Автофильтр для каждого столбца строит список значений, и который используется для задания условий фильтрации (рис. 4). В каждом столбце появляется кнопка списка, нажав которую можно ознакомиться со списком возможных критериев выбора.
По отдельному столбцу в списке критериев отбора предусматриваются следующие варианты:
· все — выбираются все записи без ограничений;
· первые 10 — данный пункт позволяет во вновь появляющемся диалоговом окне «Наложение условия по списку» (рис. 5) выбрать определенное количество наибольших или наименьших элементов списка, которые необходимо отобразить;
· значения — будут выбраны только те записи, которые в данном столбце содержат указанное значение;
· условие — выбираются записи по формируемому пользователем условию в диалоговом окне «Пользовательский фильтр» (рис. 6).
Каждая часть условия включает:
· оператор отношения: = (равно), <> (не равно), > (больше), >= (больше или равно), < (меньше), <= (меньше или равно), начинается с, содержит и т.п.;
· значение, которое может выбираться из списка или содержать шаблонные символы *,?.
Рис. 4. Список с автофильтром
Рис. 5. Диалоговое окно «Наложение условия по списку»
Рис. 6. Диалоговое окно «Пользовательский фильтр»
Пример. Для Кода предмета можно сформировать условия:
>=п* — отобрать все записи, которые содержат код предмета, начинающийся с буквып;
>= п1 И <=п2 — отобрать все записи, которые содержат коды предметов п1 и п2;