Сводные таблицы. Часть 2
Настройки сводных таблиц





Перестройка макета отчёта. Традиционная фильтрация данные в сводных таблиц. Использование специальных срезов (для новых версий).

Конспекты

Курсы

Практикум


Скачать файл
Теги: Бесплатный курс Excel, самоучитель Excel, конспекты тренинга Excel, без регистрации.

Настройки сводных таблиц

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

Настройки сводных таблиц

Для занятия Вам потребуется книга Excel с заготовками таблиц, по которым составлен конспект - этот файл прикреплен в самом низу страницы (без проблем откроется в Excel версии 2007 и новее)

 

Оформление сводных таблиц

Продолжая работу с получившейся на прошлом занятии сводной таблицей выполним еще ряд преобразований, которые доступны на контекстной вкладке Конструктор [Design] – напомню, она становится доступна, если выбрана любая ячейка сводной таблицы. Правая часть этой вкладки содержит возможности оформления, очень похожие на «умные» таблицы: можно применить любой стиль из предложенных готовых вариантов оформления, при помощи флажков Заголовки строк/столбцов [Row/Column_Headers] и Чередующиеся столбцы/строки [Banded_Rows/Columns] выделить элементы отчета. А вот в первой группе инструментов Макет [Layout] можно найти кое-что интересное

ПРОМЕЖУТОЧНЫЕ ИТОГИ: Чтобы интереснее было разбираться, стоит изменить иерархию в названии строк – разместить поле Сорт над Номенклатурой. А теперь попробуйте выбирать поочередно команды вкладки Конструктор – Макет – Промежуточные итоги [DesignLayoutSubtotal]</strog> </span> и увидите все возможные варианты расположения строк с итогами внутри отчета сводной таблицы. В случае отображения итогов в нижней части группы можно использовать команду кнопки Пустые строки [Blank_Rows]  – она в самом деле добавляет пустую строку после каждого раздела  [Insert_Blank_Line_After…] и таблица становится легче для восприятия

АВТОФИЛЬТР И ОБЩИЕ ИТОГИ: Теперь выделите любое номенклатурное название и воспользуйтесь кнопкой автофильтра в ячейке A4, чтобы оставить данные только по первому сорту. Сначала данные по сортам не будут обнаружены в списке элементов, но добраться до них можно – в верхней части открывшегося окна настроек автофильтра есть команда Выберите поле [Select_field] с выпадающим списком – найдите в нем Сорт и далее действуйте привычным образом – сбросьте все ненужные флажки и нажмите ОК

После фильтрации присмотритесь к отчету сводной таблицы – необходимость в строке Общего итога [Grand_Total] пропала, так как она полностью дублирует строку с итогом по разделу. В таких ситуациях удобно будет отключить общий итог и сделать это можно при помощи команды на ленте Конструктор – Макет – Общие итоги – Включить только для строк [DesignLayoutGrand_TotalsOn_for_Rows_Only] </strong> </span

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

Чтобы разделить по разным столбцам номенклатурные названия и названия сортов, выполните команду на контекстной вкладке ленты Конструктор – Макет – Макет отчета – Показать в табличной форме [DesignLayoutShow_in_Tabular_Form]. Для создания объединенных ячеек в заголовках и строках таблицы измените ее настройку через контекстное меню, вызванное в любой ячейке отчета сводной таблицы, в нем необходима вторая снизу команда – Параметры сводной таблицы [Pivot_Table_Options…]. В открывшемся диалоговом окне остаемся на первой вкладке Макет и формат [Layout_&_Format] и первый же флажок устанавливаем Объединить и выровнять по центру ячейки с подписями [Merge_and_center_cells_with_labels]. Готово

 

Фильтры и срезы

После всех преобразований я свернула расшифровку по столбцам, иначе таблица получалась необъятная. Обратите внимание на четыре кнопки фильтра – отдельная кнопка предусмотрена для каждого поля, в том числе расположенного в столбцах. Но можно сделать эту таблицу удобнее для просмотра, если поле Склад забросить в область Фильтры [Filters]

СРЕЗЫ: Теперь можно настраивать любые возможные условия по фильтрам и получать данные в соответствии с ними. Но удобно ли пользоваться кнопками фильтров? Мы к ним привыкли, конечно, но есть инструмент поинтереснее – Срезы. Находясь в любой ячейке отчета сводной таблицы выполните команду на контекстной вкладке ленты Анализ (Параметры [Options] в предыдущих версиях) – Фильтр – Вставить срез [Analyze Filter Insert_Slicer] и на экран будет выведено окно, содержащее список заголовков исходной таблицы. При помощи флажков отметьте те, по которым будете фильтровать. Можно отмечать те поля, которые на участвуют в отчете в настоящий момент. Я, например, выберу Менеджер (в любой момент можно добавлять срезы по другим полям) – на листе отобразится срез со списком элементов поля Менеджер – щелкните по любому из них и отчет сводной таблицы тут же обновит содержимое. Для очистки условия предусмотрена кнопка удаления фильтра в верхней правой части окна

Удалить срез как таковой можно следующим образом – выделите его и нажмите клавишу Delete. Я удалять срез не буду и для удобства расположу его справа от отчета сводной таблицы. Для настроек среза можно использовать контекстную вкладку Параметры [Options] – она доступна в том случае, если выделен срез. Из наиболее любопытных команд обратите внимание на вторую справа группу инструментов Кнопки [Buttons] – она позволяет отрегулировать высоту кнопок с названиями элементов и расположить их в несколько столбцов, как на следующем рисунке (цветовая схема была изменена при помощи группы Стили срезов [Slicer_Styles], расположенной в левой части этой же вкладки). Еще для работы со срезами имеет смысл посмотреть возможности команды Настройка среза [Slicer_Settings] из левой части вкладки Параметры [Options]

ФИЛЬТРАЦИЯ ПО ДАТАМ: В 2013 версии программы предусмотрена замечательная возможность для фильтрации по датам – для этого щелкните по названию поля Дата документа в верхней части списка полей и в появившемся меню выберите команду Добавить как временную шкалу [Add_as_Timeline] – на листе появится шакала дат, в верней правой части которой можно выбрать шаг – год или месяц (по умолчанию обычно выбран Месяцы [Months]), например; и далее уже выбирать интересующий элемент в сформированном в основной части окна списке  

Что делать тем, у кого 2013-ая версия офиса еще не установлена? Решать вопрос при помощи группировки. Для этого перетащите поле Дата документа в область столбцов, например, ниже поля Покупатель, разверните таблицу по столбцам. Выделите ячейку с любой датой, нажмите правую кнопку мыши и в контекстном меню выберите команду Группировать [Group]. Далее в появившемся окошке укажите один или несколько шагов группировки (обычно по умолчанию выбран Месяц [Months]) – я добавлю Годы [Years] и нажму ОК.

Далее можно свернуть поля и смотреть расшифровку в случае необходимости, но я бы поступила иначе, а именно: сняла флажок напротив поля Дата документа в верхней части списка полей; из области столбцов нижней части списка полей убираю оставшееся поле Годы [Years] – его можно перетащить левой кнопкой мыши в область ячеек листа и отпустить. Далее добавлю срез при помощи команды Анализ (Параметры [Options] в предыдущих версиях) – Фильтр – Вставить срез [AnalyzeFilterInsert_Slicer] – флажками отмечаю поля Дата документа и появившееся в нижней части поле Годы [Years] и жму ОК. Далее выполняем настройки срезов уже рассмотренным выше способом – при помощи контекстной вкладки Параметры [Options]. В частности, расположила кнопки в несколько столбцов, а в окошке Настройка среза [Slicer_Settings] (его также можно открыть через контекстное меню среза) установила флажок Скрыть элементы без данных [Hide_items_with_no_data], чтобы в срезе не отображались интервалы дат, находящиеся за пределами группировки (на рисунке отмечены стрелкой)

ВАЖНО (!) При работе с конспектами помним, что все названия программных продуктов являются зарегистрированными торговыми марками. Никакая часть настоящего издания ни в каких целях не может быть воспроизведена в какой бы то ни было форме и какими бы то ни было средствами, будь то электронные и механические, включая фотокопирование и запись на магнитный носитель, если на это нет письменного разрешения автора

Похожие материалы

Интерфейс и основные понятия
Основные понятия
Вводный урок Excel, чтобы разобраться, как правильно называются элементы интерфейса программы. Это займёт немного времени, но дальнейшие материалы будут намного понятнее.

Примеры использования функции ЕСЛИ
ЕСЛИ логическая функция
Как выполнить обработку ошибок с помощью ЕСЛИОШИБКА. Как ЕСЛИ выполнить проверку данных по нескольким условиям - И, ИЛИ, вложенные ЕСЛИ

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

Правила и закономерности, по которым работает Excel
Общие принципы, лежащие в основе любого инструмента программы Excel
Как понять программу Excel? Это несложно, список правил и настроек приведён в этом уроке.

Заказчики

Я сотрудничаю с многими известными компаниями. Вот некоторые из них:

ВТБ МегаФон Глобус Абсолют

Связаться с автором

Оставьте ваше имя и адрес электронной почты, в ближайшее время я свяжусь
с вами для консультации и отвечу на вопросы!



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