Сводные таблицы. Часть 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
Горячие клавиши
Горячие клавиши ускоряют доступ ко многим командам, необходимым в ежедневной работе. Если учить хотя бы одно сочетание в 1-2 недели, результат будет заметен и ощутим!

Настройка искомого значения в числовом и текстовом форматах, с помощью символа звёздочки. Вычисление номера столбца.
Функция ВПР - профессиональные хитрости
Функция ВПР может научиться игнорировать формат ячеек и даже высчитывать номер столбца в таблице с помощью ПОИСКПОЗ.

Создание сводной таблицы и настройки отчёта
Сводные таблицы. Часть 1
Команда Сводная таблица позволяет получить компактный упорядоченный отчёт по выгрузке из базы данных. Создать и настроить отчёт сводной таблицы очень просто. Главное - начать.

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

Заказчики

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

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

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

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



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