Для сдачи тестов, рубежного контроля, а также закрепления материала используйте браузеры MS Internet Explorer, Mozilla Firefox, Chromium
    Главная страница электронного учебника
    Содержание дисциплины

    Графические возможности MS Excel
    Содержание дисциплины

    Сортировка и фильтрация данных в Microsoft Excel

    Учебная тема
    Сортировка и фильтрация данных в Microsoft Excel

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


     

    Цели работы:

    • познакомиться с использованием электронной таблицы как базы данных;

    • научиться осуществлять поиск информации в базе данных по различным критериям;

    • научиться производить сортировку информации.


     

    Средства Ecxel для работы с данными списка

    Электронная таблица, все строки которой содержат однородную информацию, рассматривается как список или база данных.

    Термины база данных и список в Excel используются как синонимы. При этом нужно иметь в виду следующее:

    • каждая строка списка рассматривается как запись базы данных;

    • столбцы списков считаются полями базы данных;

    • заголовки столбцов считаются именами полей базы данных. В Excel имеется набор функций, облегчающих обработку и анализ данных в списке или базе данных. Для того чтобы электронную таблицу можно было обрабатывать с помощью подобных функций, данные в ней должны быть организованы следующим образом:

    • все строки таблицы должны содержать однородную информацию - во всех строках в одинаковых столбцах должны находиться однотипные данные;

    • заголовки столбцов должны находиться в первой строке списка;

    • названия строк должны находиться в левом столбце списка;

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

    К данным, организованным в виде списка (базы данных), можно применять специальные функции, относящиеся к категории Работа с базой данных, и выполнять следующие операции:


     

    • добавлять, изменять и удалять записи;

    • находить записи;

    • сортировать записи;

    • осуществлять фильтрацию (и выборку) данных с помощью Автофильтра и Расширенного фильтра;

    • подводить общие и промежуточные итоги и т. д.


     

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

    Сортировка осуществляется "на месте" - непосредственно в таблице.

    В электронной таблице можно сортировать как строки, так и столбцы. Строки можно отсортировать по значениям ячеек одного столбца или нескольких.

    Строки, столбцы или отдельные ячейки в процессе сортировки переупорядочиваются в соответствии с заданным пользователем порядком сортировки. Списки можно сортировать в возрастающем порядке (от 1 до 9, от А до Я) или в убывающем порядке (от 9 до 1, от Я до А).

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

    Сортировка данных в электронных таблицах производится с помощью команд меню Данные → Сортировка или с помощью кнопок на Стандартной панели инструментов. (рисунок 1)


     

    Фильтрация (выборка) данных из списка. Фильтрация (выборка) данных в таблице позволяет отображать только те строки, содержимое ячеек которых отвечает заданному условию или нескольким условиям. С помощью фильтров пользователь может в удобной для себя форме выводить или удалять (скрывать) записи списка.

    В отличие от сортировки данные при фильтрации не переупорядочиваются, а лишь скрываются те записи, которые не отвечают заданным критериям выборки.

    Отобранные записи можно форматировать или удалять, копировать в отдельную область таблицы, распечатывать, а также использовать для последующих вычислений или построения диаграмм.

    Фильтрация данных в электронных таблицах может выполняться двумя способами: с помощью автофильтра или расширенного фильтра. (рисунок 2)

    Фильтрация данных с использованием автофильтра. Для выполнения этой операции нужно:


     

    1) установить курсор внутри таблицы;

    2) ввести команду меню Данные → Фильтр → Автофильтр;

    3) щелчком мыши по кнопке со стрелкой img3 раскрыть список столбца, по которому будет производиться выборка;

    4) указать требуемые значения или выбрать строку "условие" и задать критерии выборки в диалоговом окне Пользовательский автофильтр. (рисунок 3)

    Условия для отбора записей в определенном столбце могут состоять из двух самостоятельных частей, соединенных логической связкой И / ИЛИ.

    Каждая часть условия может включать:


     

    • значение, которое может выбираться из списка или содержать шаблонные символы подстановки. В качестве символов подстановки используются звездочка * - для указания произвольного количества символов или вопросительный знак ? - для замены одного символа;

    • оператор отношения (сравнения). При задании критериев выборки могут использоваться следующие операторы сравнения:

    = Равно ≠ He равно

    < Меньше < = Меньше или равно

    > Больше > = Больше или равно

    Для восстановления всех строк исходной таблицы нужно щелкнуть мышью по кнопке со стрелкой img5 и в раскрывшемся списке выбрать строку "все" или выполнить команду Данные → Фильтр → Отобразить все.

    Для отмены режима фильтрации нужно установить курсор внутри таблицы и снова ввести команду меню ДАННЫЕ → Фильтр → Автофильтр (убрать переключатель).


     

    Фильтрация данных с использованием расширенного фильтра. Расширенный фильтр позволяет формировать множественные критерии выборки и осуществлять более сложную фильтрацию данных электронной таблицы с заданием набора условий отбора по нескольким столбцам.

    Для фильтрации записей списка расширенный фильтр обеспечивает использование двух типов критериев:


     

    • критериев сравнения;

    • вычисляемых критериев.


     

    Фильтрация записей с использованием расширенного фильтра выполняется с помощью команды меню Данные → Фильтр → Расширенный фильтр.

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

    Диапазон условий должен содержать строку с заголовками столбцов и несколько строк для задания условий отбора. Обычно для создания диапазона условий вначале копируют в отдельное место (на другом или на том же самом рабочем листе - как правило, выше исходной таблицы) строку с заголовками столбцов, затем в расположенные ниже строки вводят критерии выборки по отдельным столбцам. Между значениями условий отбора и таблицей должна находиться, как минимум, одна пустая строка.

    Если критерии отбора (условия фильтрации) вводятся в одной строке для разных столбцов, то они считаются связанными условием "И". Если критерии отбора записываются в разных строках, то они считаются связанными условием "ИЛИ".

    После формирования диапазона условий с критериями выборки записей устанавливают курсор внутри таблицы, вводят команду Данные → Фильтр → Расширенный фильтр и в диалоговом окнеРасширенный фильтр указывают диапазон ячеек таблицы и адрес или имя сформированного заранее диапазона условий. (рисунок 4)

    Записи можно фильтровать на месте или одновременно с выполнением фильтрации копировать в указанную область на текущем рабочем листе.

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

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

    - Содержание дисциплины

    Графические возможности MS Excel

    Закрепление материала
    Тестирование материала
    Содержание дисциплины

    Использование программы Microsoft Excel в качестве базы данных