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

Сортировка списка

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

Возрастающий порядок сортировки:

- Числа будут отсортированы в порядке от наименьшего отрицательного до наибольшего положительного числа.

- Значения даты и времени будут отсортированы в порядке от наиболее раннего до наиболее позднего значения.

- Текст будет отсортирован по алфавиту. При этом сначала будут расположены заданные в качестве текста числовые значения.

- При сортировке в возрастающем порядке логических значений сначала будет отображено значение ЛОЖЬ, а затем – значение ИСТИНА.

- Значения ошибки будут отсортированы в том порядке, в котором они были обнаружены (с точки зрения сортировки все они равны).

- Пустые ячейки будут отображены в конце отсортированного списка.

 

Убывающий порядок сортировки:
В случае убывающего порядка сортировки Excel сортирует записи в обратном описанному выше порядке. Только пустые ячейки по-прежнему будут отображены в конце списка.

 

Пользовательский порядок сортировки:

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

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

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

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

 

 

29. Понятие о сводных таблицах

Для всестороннего и эффективного анализа данных больших таблиц в Excel используются т.н. сводные таблицы (СТ). Функции работы с СТ относятся к одному из самых мощных инструментов Excel.

Главные достоинства СТ - это представление больших объемов информации в концентрированном и удобном для анализа виде, широкие возможности для группировки данных, а так же возможность получения промежуточных и общих итогов, которые помещаются в таблицу автоматически.

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

СТ являются “трехмерными”, т.к. к привычным атрибутам таблиц: строка и столбец, добавляется атрибут - страница (на основе исходной таблицы с данными по 12 месяцам, можно создать СТ, содержащую, например, 13 страниц; “открывая”, специальным образом, любую из 12 страниц, можно просмотреть данные какого-то одного месяца, а на 13 странице - суммарные данные за все месяцы сразу).

Для построения и модификации СТ используется Мастер сводных таблиц, вызываемый с помощью пункта меню Данные -Сводная таблица. Мастер предоставляет ряд окон диалога, в которых пользователь, выбирая те или иные предложенные варианты, шаг за шагом создает СТ. Построение СТ выполняется в четыре этапа (шага). Переход от этапа к этапу выполняется кнопкой Шаг> в диалоговых окнах Мастера сводных таблиц.

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

В списке или базе данных какого-либо листа MicrosoftExcel.

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

Во внешнем источнике данных, которым может быть база данных, текстовый файл или любой другой источник, кроме книги MicrosoftExcel.

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

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

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

 

На втором шаге в диалоговом окне указывается интервал клеток списка или базы данных, которые должны быть сведены.

В общем случае полное имя интервала задается в виде:

[имя_ книги]имя_листа!интервал;

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

Для указания интервала из другой закрытой рабочей книги нажимается кнопка ПРОЛИСТАТЬ, далее, в открывшемся диалоговом окне, выбирается диск, каталог и файл закрытой книги, вводятся имя рабочего листа и интервал клеток.

На третьем шаге в макете таблицы в т.н. режиме создания (рис.1) создается структура СТ и определяются ее функции. Макет представлен в центре окна и состоит из областей: строка, столбец, страница и данные. Справа от макета отображаются все имена полей (заголовки столбцов) в заданном интервале исходной таблицы.

Размещение полей в определенную область макета выполняется путем их “перетаскивания” при нажатой левой кнопки мыши. Чтобы удалить поле из области макета, его перетаскивают за пределы макета. Удаление поля приведет к скрытию в СТ всех зависимых от него величин, но не повлияет на исходные данные.

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

Каждая область макета, куда размещаются поля исходной таблицы, имеет свое назначение, определяющее внешний вид СТ и ее функции:

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

Столбец - поля в этой области формируют заголовки столбцов СТ; если таких полей несколько, то они в макете размещаются слева направо, обеспечивая группирование данных СТ по иерархии полей;

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

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