Сводные таблицы Excel 2007

Сортировка и фильтрация

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

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

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

Сделайте небольшой список для тренировки.

Выделите его.

Нажмите кнопку "Сортировка и фильтр" на панели "Редактирование" ленты "Главная".

Выберите "Сортировка от А до Я". Наш список будет отсортирован по первому столбцу, т.е. по полю ФИО.

Если надо отсортировать список по нескольким полям, то для этого предназначен пункт "Настраиваемая сортировка..".

Сложная сортировка подразумевает упорядочение данных по нескольким полям. Добавлять поля можно при помощи кнопки "Добавить уровень".

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

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

Перемещать уровни сортировки можно при помощи кнопок "Вверх" и "Вниз".

Не следует забывать и о контекстном меню. Из него, также, можно настроить сортировку списка. К тому же есть такие интересные варианты сортировки, связанные с выделением того или иного элемента таблицы.

Фильтрация списков

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

Фильтры бывают двух типов: обычный фильтр (его еще называют автофильтр) и расширенный фильтр.

Для применения автофильтра нажмите ту же кнопку, что и при сортировке - "Сортировка и фильтр" и выберите пункт "Фильтр" (конечно же, перед этим должен быть выделен диапазон ячеек).

В столбцах списка появятся кнопки со стрелочками, нажав на которые можно настроить параметры фильтра.

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

Для формирования более сложных условий отбора предназначен пункт "Текстовые фильтры" или "Числовые фильтры". В окне "Пользовательский автофильтр" необходимо настроить окончательные условия фильтрации.

При использовании расширенного фильтра критерии отбора задаются на рабочем листе.

Для этого надо сделать следующее.

Скопируйте и вставьте на свободное место шапку списка.

В соответствующем поле (полях) задайте критерии фильтрации.

Выделите основной список.

Нажмите кнопку "Фильтр" на панели "Сортировка и фильтр" ленты "Данные".

На той же панели нажмите кнопку "Дополнительно".

В появившемся окне "Расширенный фильтр" задайте необходимые диапазоны ячеек.

В результате отфильтрованные данные появятся в новом списке.

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

Связанные таблицы

Связанная таблица - это набор данных, которыми можно управлять как единым целым. Для создания связанной таблицы предназначена кнопка "Форматировать как таблицу" на панели "Стили" ленты "Главная".

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

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

Каждой связанной таблице дается уникальное имя. По умолчанию - "Таблица_номер". Изменить название таблицы можно на панели "Свойства".

На панели "Стили таблиц" можно, при необходимости, изменить или настроить стиль связанной таблицы.

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

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

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

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

В связанную таблицу можно добавлять/удалять строки и столбцы.

Это можно делать несколькими способами.

1. Воспользоваться кнопкой "Изменить размер таблицы" на панели "Свойства".

2. Установите курсор в ячейке связанной таблицы, рядом с которой надо добавить новый столбец (строку) и на панели "Ячейки" ленты "Главная" воспользуйтесь кнопкой "Вставить".

3. Не забывайте также о контекстном меню.

Сводные таблицы Excel 2007

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

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

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

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

Все эти данные мы свели в одну таблицу, которую Вы можете увидеть ниже.

За 17 дней продаж у нас получилась большая таблица на 350 записей. Но эта таблица не решает наших проблем. Нам необходимо узнать объемы продаж в денежном и количественном выражении по датам и по отдельным магазинам, но как это сделать? Сортировать таблицу и суммировать отдельные её части? Это требует времени, а завтра поступят новые данные, и всю работу нужно будет снова повторить.

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

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

Таблица сразу же отобразит нужные нам результаты:

Этот пример наглядно демонстрирует преимущества сводных таблиц, к которым относятся:

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

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

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

Далее ⇒