Списки в MS Excel. Сортировка и фильтрация данных

Цель работы: научиться создавать базы данных, выполнять

сортировку и фильтрацию данных

Содержание работы:

1 Создание списка – базы данных

2 Сортировка данных в списке

3 Фильтрация данных в списке

Общие сведения

Список (база данных MS Excel) – это электронная таблица, в которой имена столбцов занимают одну строку, а строки имеют фиксированную структуру.

База данных (БД) – это поименованная совокупность данных, имеющая одно или несколько приложений. Например, список группы является БД студентов. Её приложение для деканата – это список студентов, обучающихся в академической группы № …, приложение для военкомата – список призывников, для телефонной компании – список абонентов и т.д.

В терминах БД таблица MS Excel– это отношение, строка в таблице – запись или кортеж, столбец – поле или атрибут, а шапка таблицы с именами полей называется схемой отношения. Весь блок ячеек с данными называется областью данных.

Строка заголовков (схема таблицы) состоит из ячеек с именами полей, причём имя поля в БД должно располагаться в одной ячейке таблицы MS Excel.

Операции над данными

Над данными в БД можно выполнять различные операции обработки, которые сгруппированы во вкладке Данные: сортировка, фильтрация, группировка, проверка вводимых данных, анализ и др. Чтобы процессор MS Excel воспринимал таблицу БД как список, перед выполнением какой-либо команды таблицу или нужный диапазон нужно выделить.

Для работы с БД используются формы вывода данных (команда Форма). Кнопки Форма нет на ленте, однако эту функцию можно использовать в MS Excel, добавив её кнопку на панель быстрого доступа – рисунок 9.1. Для этого во вкладке Файл (Office) нужно нажать Параметры Excel, выбрать слева - Настройка, справа - Все команды или Команды не на ленте, найти Форма, нажать Добавить и ОК.

Рисунок 9.1 – Кнопка Форма на панели быстрого доступа

В MS Excel можно создавать запросы на данные из созданного списка и из внешних БД (команда Данные\Получить внешних данные и выбрать нужный источник), обновлять данные через Internet и др.

Создание списка – базы данных

После запуска приложения MS Excel нужно ввести таблицу БД, но перед этим нужно настроить строку её схемы (заголовка). Поскольку имя каждого поля должно занимать одну ячейку, нужно выделить строку для заголовка таблицы, затем во вкладке Главная \ раздел Ячейки \ Формат \ Формат ячеек (рисунок 9.2) в окне Формат ячеек \вкладкаВыравнивание (или нажать Ctrl + 1) установить параметры:

Рисунок 9.2 – Настройка строки заголовка таблицы БД

▪ по горизонтали: по значению

▪ по вертикали: по верхнему краю

▪ отображение: переносить по словам (если Имя поля – длинное)

Если имя поля ненамного превышает ширину ячейки, то можно ограничиться командой Главная \ Ячейки \ Формат \ Автоподбор ширины столбца.

Теперь можно вводить таблицу (список) БД - рисунок 9.3.

Рисунок 9.3 Список Экзаменационная ведомость

Сортировка данных в списке

Сортировка данных в таблице БД MS Excel является основной операцией, т.к. многие операции группировки, которые выполняются из меню Данные, можно использовать только после сортировки.

Цель сортировки – упорядочивание данных в порядке возрастания или убывания, она производится в том же списке. В среде MS Excel сортировка выполняется по трём полям, причём сортировка по второму полю производится для одинаковых значений отсортированного первого поля, а третьего – для одинаковых значений второго поля.

Для сортировки данных нужно выполнить команду Данные\ Сортировка, откроется окно Сортировка диапазона (рис. 9.4), в котором выбираются столбцы и вид упорядочивания – по возрастанию или по убыванию.

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

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

Проведём сортировку списка " Экзаменационная ведомость" по полям Код преподавателя, Оценка и Фамилия И.О.

Рисунок 9.4 Окна Сортировка диапазона и Параметры сортировки

Результат сортировки показан на рис. 9.4, где столбецЕ отсортирован по возрастанию, в столбце D по возрастанию отсортированы одинаковые записи столбца Е, а в столбце В – по алфавиту одинаковые значения в столбце D.

Рисунок 9.4 Результат сортировки данных

Дополнительные установки сортировки задаются в окне Параметры сортировки, вызываемой кнопкой Параметры (рис. 9.4). Здесь можно задать сортировку с учётом или без учёта регистра, по столбцам или построкам, обычный или выбранный из списка. Этот список можно создать самостоятельно с помощью команды Сортировка \ Настраиваемая сортировка (выделив таблицу, нажать правой клавишей мыши и выбрать Сортировка\Настраиваемая сортировка).

Фильтрация данных в списке

Фильтрация данных – это выбор данных, соответствующих какому-либо условию, критерию. Фильтрация в MS Excel выполняется из вкладки Данные \ раздел Сортировка и фильтр двумя способами:

▪ с помощью команды Фильтр и

▪ с помощью команды Дополнительно (Расширенный фильтр).

Фильтр

Фильтрация позволяет выбрать критерий отбора из предлагаемых в приложении для каждого столбца отдельно. Для этого нужно раскрыть список [▼ ] в названия каждого столбца и поставить галочку для критерия. В результате в таблице остаются только строки, удовлетворяющие критериям. Например, фильтрация поля " Оценка" по условию " 4" оставляет от исходного списка только записи с оценкой " 4" (рис. 9.5)

Рисунок 9.5 Список студентов, получивших отметку 4

Дальнейшая фильтрация по другому полю выполняется для уже отфильтрованных данных, например, фильтр по коду преподавателя 1 приведёт к следующему результату (рис. 9.6):

Рисунок 9.6 Список студентов, получивших отметку 4

у преподавателя с кодом 1

Список критериев для каждого столбца содержит следующие варианты критериев:

▪ все – выбираются все записи поля;

▪ первые 10 – появляется окно Наложение условия по списку, в котором выбираются количество выводимых записей (наибольших или наименьших) в элементах списка или процентах от количества элементов;

▪ значения – отфильтровываются только записи, содержащие в данном столбце указанное значение;

▪ условие – нажать кнопку списка, выбрать Текстовые фильтры \ Настраиваемый фильтр - пользователь формирует условие отбора в окне Пользовательский автофильтр (равно, больше, …И, ИЛИ…) и записи фильтруются по этому условию.

Отмена результата фильтрации (для поля) выполняется либо повторной фильтрацией по критерию Все, либо – для всего списка – повторным вводом команды Данные \ Фильтр.

Расширенный фильтр

Расширенный фильтр задаётся командой Данные \ Дополнительно в окне Расширенный фильтр. При этом используется два типа критериев для фильтрации записей:

▪ критерий сравнения;

▪ вычисляемый критерий.

Если критерий формируется в нескольких полях (столбцах), то его называют множественным критерием (поэтому фильтр называется расширенным).

Особенностью расширенного фильтра является то, что критерии отбора данных формируются вне таблицы и производится в 2 этапа:

1 Сначала формируется диапазон условия (область критерия) в отдельном блоке ячеек листа MS Excel – вне таблицы БД, на свободном месте Листа.

2 Затем командой Данные \ Дополнительно в окнеРасширенный фильтрвыполняется фильтрация записей списка по сформированному вне таблицы множественному критерию.

⇐ Предыдущая 7 8 9 10 111213 14 15 16 Следующая ⇒