Как сделать фильтр в сводной таблице excel?

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

Видеоурок: Как создать сводную таблицу в Excel

Что такое сводные таблицы в Excel? Пошаговая инструкция

• Сводные таблицы это инструмент Excel для суммирования и анализа больших объемов данных.
• Представим, что у нас есть таблица с данными продаж по клиентам за год размером в 1000 строчек:
• 
• Она содержит данные:

• Даты заказов;
• Регион в котором расположен клиент;
• Тип клиента;
• Клиент;
• Количество продаж;
• Выручка;
• Прибыль.

Теперь, представим, что наш руководитель поставил задачу вычислить:

• Какой объем выручки у региона Север за 2017 год?;
• ТОП пять клиентов по выручке;
• Какое место по выручке занимает клиент Лудников ИП в регионе Восток?

Для поиска ответа на эти вопросы вы можете использовать различные функции и формулы. Но что, если задач по этим данным будет не три, а тридцать? Каждый раз вам придется менять формулы и функции и подстраивать под каждый тип расчета.

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

Как сделать сводную таблицу в Excel

Для создания таблицы выполните следующие действия:

• Выделите любую ячейку в таблице с данными;
• Нажмите на вкладку “Вставка” => “Сводная таблица”:

• Во всплывающем диалоговом окне система автоматически определит границы данных, на основе которых вы сможете создать сводную таблицу. Рекомендую при каждом создании убеждаться в том, что система правильно определила границы диапазона данных:
  • Таблица или диапазон: Система автоматически определяет границы данных. Они будут корректными при том условии, что в таблице нет пробелов в заголовках и строках. При необходимости вы можете скорректировать диапазон данных.
• Система по умолчанию создает таблицу в новой вкладке файла Excel. Если вы хотите создать её в конкретном месте на определенном листе, то вы можете указать границы для создания в графе “На существующий лист”.

После нажатия кнопки “ОК” таблица будет создана.

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

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

Области сводной таблицы в Excel

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

Ниже вы узнаете подробней об областях:

• Кэш
• Область “Значения”
• Область “Строки”
• Область “Столбцы”
• Область “Фильтры”

Что такое кэш сводной таблицы

При создании сводной таблицы, Excel создает кэш данных, на основе которых будет построена таблица.

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

Кэш данных увеличивает размер Excel-файла.

Область “Значения”

Область “Значения” включает в себя числовые элементы таблицы. Представим, что мы хотим отразить объем продаж регионов по месяцам (из примера в начале статьи). Область закрашенная желтым цветом, на изображении ниже, отражает значения размещенные в области “Значения”.

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

Область “Строки”

Заголовки таблицы, размещенные слева от значений, называются строками. В нашем примере это названия регионов. На скриншоте ниже, строки выделены красным цветом:

Область”Столбцы”

Заголовки вверху значений таблицы называются “Столбцы”.

На примере ниже красным выделены поля “Столбцы”, в нашем случае это значения месяцев.

Область “Фильтры”

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

Сводные таблицы в Excel. Примеры

На примерах ниже мы рассмотрим, как с помощью сводных таблиц ответить на три вопроса:

• Какой объем выручки у региона Север за 2017 год?;
• ТОП пять клиентов по выручке;
• Какое место по выручке занимает клиент Лудников ИП в регионе Восток?

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

Например, если нам нужно отобразить данные продаж клиентов по регионам, то следует поместить названия регионов в строки, месяцы в колонки, значения продаж в поле “Значения”.

В окне “Поля сводной таблицы” размещены области и поля со значениями для размещения:

Поля создаются на основе значений исходного диапазона данных. Раздел «Области» – это место, где вы размещаете элементы таблицы.

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

Пример 1. Какой объем выручки у региона Север?

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

• создать сводную таблицу и поле “Регион” перенести в область “Строки”;
• поле “Выручка” разместить в области “Значения”
• задать финансовый числовой формат ячейкам со значениями.

Получим ответ: продажи региона Север составляют 1 233 006 966 ₽:

Пример 2. ТОП пять клиентов по продажам

Для того чтобы вычислить рейтинг ТОП пяти клиентов, нам нужно:

• переместить поле “Клиент” в область “Строки”;
• поле “Выручка” разместить в области “Значения”;
• задать финансовый числовой формат ячейкам со значениями.

• У нас получится следующая таблица:
• По-умолчанию, система Excel сортирует данные в таблице в алфавитном порядке. Для сортировки данных по объему продаж выполните следующие действия:

• кликните правой кнопкой на любой из строчек с данными выручки;
• перейдите в меню “Сортировка” => “Сортировка по убыванию”:

Как результат мы получим отсортированный список клиентов по объему выручки.

Пример 3. Какое место по выручке занимает клиент Лудников ИП в регионе Восток?

Для расчета места по объему выручки клиента Лудников ИП в регионе Восток рекомендую сформировать сводную таблицу, в которой будут отображены данные выручки по регионам и клиентам внутри этого региона.

Для этого:

• поместим поле “Регион” в область “Строки”;
• поместим поле “Клиент” в область “Строки” под поле “Регион”;
• зададим финансовый числовой формат ячейкам со значениями.

После перемещения элемента “Регион” и “Клиент” в области “Строки” друг под другом , система поймет каким образом вы хотите отобразить данные и предложит подходящий вариант.

• поле “Выручка” разместим в область “Значения”.

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

• кликните правой кнопкой на любой из строчек с данными выручки;
• перейдите в меню “Сортировка” => “Сортировка по убыванию”:

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

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

Источник: https://excelhack.ru/svodnaya-tablica-v-excel-kak-sdelat/

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

Сводные таблицы необходимы для суммирования, анализа и представления данных, находящихся в «больших» исходных таблицах, в различных разрезах. Рассмотрим процесс создания несложных Сводных таблиц.

Сводные таблицы () могут пригодиться, если одновременно выполняются следующие условия:

• имеется исходная таблица с множеством строк (записей), речь идет о нескольких десятках и сотнях строк;
• необходимо провести анализ данных, который требует выборки (фильтрации) данных, их группировки (суммирования, подсчета) и представления данных в различных разрезах (подготовки отчетов);
• этот анализ затруднительно провести на основе исходной таблицы с использованием других стредств: фильтра (CTRL+SHIFT+L), формул, Расширенного фильтра;
• исходная таблица удовлетворяет определенным требованиям (см. ниже).

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

Подготовка исходной таблицы

Начнем с требований к исходной таблице.

• каждый столбец должен иметь заголовок;
• в каждый столбец должны вводиться значения только в одном формате (например, столбец «Дата поставки» должен содержать все значения только в формате Дата; столбец «Поставщик» — названия компаний только в текстовом формате или можно вводить Код поставщика в числовом формате);
• в таблице должны отсутствовать полностью незаполненные строки и столбцы;
• в ячейки должны вводиться «атомарные» значения, т.е. только те, которые нельзя разнести в разные столбцы. Например, нельзя в одну ячейку вводить адрес в формате: «Город, Название улицы, дом №». Нужно создать 3 одноименных столбца, иначе Сводная таблица будет работать неэффективно (в случае, если Вам нужна информация, например, в разрезе города);
• избегайте таблиц с «неправильной» структурой (см. рисунок ниже).

Вместо того, чтобы плодить повторяющиеся столбцы (регион 1, регион 2, …), в которых будут в изобилии незаполненные ячейки, переосмыслите структуру таблицы, как показано на рисунке выше (Все значения объемов продаж должны быть в одном столбце, а не размазаны по нескольким столбцам. Для того, чтобы это реализовать, возможно, потребуется вести более подробные записи (см. рисунок выше), а не указывать для каждого региона суммарные продажи).

Более детальные советы по построению таблиц изложены в одноименной статье Советы по построению таблиц.

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

Создание таблицы в формате EXCEL 2007 добавляет новые возможности:

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

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

В таблице имеются столбцы:

• Товар – наименование партии товара, например, «Апельсины»;
• Группа – группа товара, например, «Апельсины» входят в группу «Фрукты»;
• Поставщик – компания-поставщик Товаров, Поставщик может поставлять несколько Групп Товаров;
• Дата поставки – Дата поставки Товара Поставщиком;
• Регион продажи – Регион, в котором была реализована партия Товара;
• Продажи – Стоимость, по которой удалось реализовать партию Товара;
• Сбыт – срок фактической реализации Товара в Регионе (в днях);
• Прибыль – отметка о том, была ли получена прибыль от реализованной партии Товара.

Через Диспетчер имен () откорректируем Имя таблицы на «Исходная_таблица».

Создание Сводной таблицы

Сводную таблицу будем создавать для решения следующей задачи: «Подсчитать суммарные объемы продаж по каждому Товару». 

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

В появившемся окне нажмем ОК, согласившись с тем, что Сводная таблица будет размещена на отдельном листе.

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

Структура Сводной таблицы в общем виде может быть представлена так:

 Заполним сначала раздел Названия строк. Т.к. требуется определить объемы продаж по каждому Товару, то в строках Сводной таблицы должны быть размещены названия Товаров. Для этого поставим галочку в Списке полей у поля Товар (поле и столбец — синонимы).

Т.к. ячейки столбца Товар имеют текстовый формат, то они автоматически попадут в область Названия строк Списка полей. Разумеется, поле Товар можно при необходимости переместить в другую область Списка полей. Заметьте, что названия Товаров будут автоматически отсортированы от А до Я (об изменении порядка сортировки читайте ниже).

Теперь поставим галочку в Списке полей у поля Продажи.

Т.к. ячейки столбца Продажи имеют числовой формат, то они автоматически попадут в раздел Списка полей Значения.

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

Детализация данных Сводной таблицы

Если возникли вопросы о том, какие же данные из исходной таблицы были использованы для подсчета тех или иных значений Сводной таблицы, то достаточно двойного клика мышкой на конкретном значении в Сводной таблице, чтобы был создан отдельный лист с отобранными из исходной таблицей строками. Например, посмотрим какие записи были использованы для суммирования продаж Товара «Апельсины». Для этого дважды кликнем на значении 646720. Будет создан отдельный лист только со строками исходной таблицы относящихся к Товару «Апельсины».

Обновление Сводной таблицы

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

Чтобы обновить Сводную таблицу выделите любую ее ячейку и выберите пункт меню: меню .

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

Удаление Сводной таблицы

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

Второй способ — удалить только саму Сводную таблицу: выделите любую ячейку Сводной таблицы, нажмите CTRL+A (будет выделена вся Сводная таблица), нажмите клавишу Delete.

Изменение функции итогов

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

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

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

Изменение порядка сортировки

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

Теперь предположим, что Товар Баранки – наиболее важный товар, поэтому его нужно выводить в первой строке. Для этого выделите ячейку со значением Баранки и установите курсор на границу ячейки (курсор должен принять вид креста со стрелками).

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

Изменение формата числовых значений

1. Теперь добавим разделитель групп разрядов у числовых значений (поле Продажи). Для этого выделите любое значение в поле Продажи, вызовите правой клавишей мыши контекстное меню и выберите пункт меню Числовой формат…
2. В появившемся окне выберите числовой формат и поставьте галочку флажка Разделитель групп разрядов.

Добавление новых полей

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

Поле Регион продажи будет добавлено в область Названия строк Списка полей (к полю Товар).

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

• Выделив любое название Товара и нажав пункт меню , можно свернуть Сводную таблицу, чтобы отобразить только продажи по Регионам.

Добавление столбцов

Добавление поля Регион продажи в область строк привело к тому, что Сводная таблица развернулась на 144 строки. Это не всегда удобно. Т.к. продажи осуществлялись только в 6 регионах, то поле Регион продажи имеет смысл разместить в области столбцов.

1. Сводная таблица примет следующий вид.
2. Меняем столбцы местами
3. Чтобы изменить порядок следования столбцов нужно взявшись за заголовок столбца в Сводной таблице перетащить его в нужное место.

Удаление полей

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

Другой способ – снять галочку напротив удаляемого поля в верхней части Списка полей. Но, в этом случае поле будет удалено сразу из всех областей Списка полей (если оно использовалось в нескольких областях).

Добавление фильтра

Предположим, что необходимо подготовить отчет о продажах Групп Товаров, причем его нужно сделать в 2-х вариантах: один для партий Товаров принесших прибыль, другой – для убыточных. Для этого:

• Очистим ранее созданный отчет: выделите любое значение Сводной таблицы, нажмите пункт меню ;
• Ставим галочки в Списке полей у полей Группа, Продажи и Прибыль;
• Переносим поле Прибыль из области Названия строк Списка полей в область Фильтр отчета;

• Вид получившейся Сводной таблицы должен быть таким:
• Теперь воспользовавшись Выпадающим (раскрывающимся) списком в ячейке B1 (поле Прибыль) можно, например, построить отчет о продажах Групп Товаров, принесших прибыль.
• После нажатия кнопки ОК будут выведены значения Продаж только прибыльных Партий.

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

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

Несколько итогов для одного поля

Предположим, что требуется подсчитать количество проданных партий и суммарные продажи каждого Товара. Для этого:

• Очистим ранее созданный отчет: выделите любое значение Сводной таблицы, нажмите пункт меню ;
• Поставьте галочки напротив полей Товар и Продажи в верхней части Списка полей. Поле Продажи будет автоматически помещено в область Значения;
• Перетащите мышкой еще одну копию поля Продажи в ту же область Значения. В Сводной таблице появится 2 столбца подсчитывающими суммы продаж;

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

Отключаем строки итогов

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

Группируем числа и Даты

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

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

Теперь Сводная таблица показывает сколько партий Товара сбывалось за 5, 6, 7, … дней. Всего 66 строк. Сгруппируем значения с шагом 10. Для этого:

• Выделите одно значение Сводной таблицы в столбце Названия строк;
• В меню выберите пункт Группировка по полю;
• Появившееся окно заполните, как показано на рисунке ниже;

Теперь Сводная таблица показывает сколько партий Товара сбывалось в период от 1 до 10 дней, в период 11-20 дней и т.д.

• Чтобы разгруппировать значения выберите пункт Разгруппировать в меню .
• Аналогичную группировку можно провести по полю Дата поставки. В этом случае окно Группировка по полю будет выглядеть так:
• Теперь Сводная таблица показывает, сколько партий Товара поставлялось каждый месяц.

Условное форматирование ячеек Сводной таблицы

К ячейкам Сводной таблицы можно применить правила Условного форматирования как и к ячейкам обычного диапазона. Выделим, например, ячейки с 10 наибольшими объемами продаж. Для этого:

• Выделите все ячейки содержащие значения продаж;
• Выберите пункт меню ;
• Нажмите ОК.

Источник: https://excel2.ru/articles/svodnye-tablicy-v-ms-excel

Фильтрация по месяцам и годам в сводной таблице Calc

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

то есть просто доступное поле дата перетащить в раздел «Поля страниц», то в результате мы сможем фильтровать только так, как это указано в изначальных данных, на которых основана сводная таблица (у меня это были конкретные даты — дни):

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

Одним из вариантов является создание вспомогательных столбцов в исходных данных, в которых бы формулами типа МЕСЯЦ(А1) и ГОД(А1) высчитывались бы номер месяца и год.

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

Это настройка фильтрации по дате по периодам в самой сводной таблице.

В настройках сводной таблицы поле «Дата» нужно перетащить в раздел «Поля строк» и нажать «ОК».

Получится вот такой вид сводной таблицы:

Установите курсор в любую ячейку сводной таблицы с датой и выбирете пункт меню Данные > Группа и структура > Группировать (или просто нажмите клавишу F12 на клавиатуре). Откроется вот такой диалог «Группировка»:

в котором, внезапно, можно настроить интервалы времени, по которым возможна будет дополнительная фильтрация. Выбрать можно несколько значений одновременно. Я выбрал «Месяцы» и «Годы». В итоге мы получим в свое распоряжение дополнительные поля, которые нужно перенести в настройках сводной таблицы обратно в раздел «Поля страниц», нажать «ОК»

и увидеть вот такой вид сводной таблицы:

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

Отмечу, что такой не очень очевидный путь для настройки общего фильтра в LibreOffice Calc полностью копирует решение из MS Excel.

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

ps: в Excel название поля «Дата» можно заменить на свое, в Либре я что-то такого не нахожу, плохо ищу?

Источник: http://antilibreoffice.blogspot.com/2017/09/calc.html

Динамические каскадные списки на основе сводных таблиц

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

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

Диспозиция

Итак, имеем таблицу в 50 000 строк, состоящую из столбцов: Город, Должность, Сотрудник и Акций.

Предполагается, что таблица описывает гигантское предприятие, раскинувшееся на территории 300 крупных городов РФ (столбец Город), в каждом городе на некоторых должностях (столбец Должность) работают конкретные люди (столбец Сотрудник), которые владеют определенным количеством акций (столбец Акции) данного предприятия.

Цель

Цель — получить вот этот, не побоюсь этого слова, совершенный «берилл» из моей коллекции рецептов Excel, который вы наблюдаете на анимированной иллюстрации.

Файл примера

Скачать

Устройство

1. Исходные данные удачно расположены на листе Data и привычно оформлены в виде умной таблицы tblData

   

2. На листе Pivots на основе tblData вручную созданы 3 вспомогательных сводных таблицы, называющиеся слева-направо: ptCities, ptPositions и ptPersons.

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

   

   Таблица ptPositions имеет 1 фильтр — Город, таблица ptPersons имеет 2 фильтра — Город и Должность.

   Эти сводные таблицы управляются полностью автоматически с листа Lists посредством VBA кода, но об этом ниже.

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

   

   • ИД selCity фотографирует список городов в сводной таблице ptCities
   • ИД selPosition — список должностей в ptPositions
   • ИД selPerson — список сотрудников в ptPersons
   • ИД rngShares — тоже самое, что и selPerson, плюс колонка, где указано количество акций у сотрудника

4. На листе Lists мы нарисовали симпатичную панельку для трёх выпадающих списков и поля для вывода количества акций.

   

   Выпадающие списки реализованы стандартно и ссылаются соответственно на ИД selCity (D3), selPosition (D5) и selPerson (D7).

   

   Поле Имеется акций (D9) вычисляем формулой =ЕСЛИОШИБКА(ВПР($D$7;rngShares;2;0);»»)

5. На события Activate и Change листа Lists прикручены обработчики событий, которые я для вас тщательно прокомментировал в коде.

6. Для красоты на листах отключены показ заголовков и сетки, но вы их можете включить в меню ВИД, группа Показ, параметры Сетка и Заголовки

   

Взаимодействие

Опишем, как перечисленные компоненты работают совместно. Когда вы активируете лист Lists, то процедура Worksheet_Activate рефрешит кэш сводных таблиц листа Pivots, поэтому, если на лист Data были добавлены новые данные, то они включаются в кэш наших сводных таблиц.

Когда на листе Lists вы встаёте на ячейку D3, то вы видите, что она содержит выпадающий список на основе ИД selCity. Указанный именованный диапазон фотографирует список городов на листе Pivots в сводной таблице ptCities.

Выбрав какой-то город, вы меняете содержимое ячейки D3, поэтому Excel вызывает процедуру Worksheet_Change текущего листа, которая реагирует на изменение D3 тем, что очищает ячейки D5 и D7 и устанавливает выбранный город в качестве фильтра сводных таблиц ptPositions и ptPersons.

Когда вы уточняете Должность, изменяя D5, процедура Worksheet_Change отрабатывает опять, но уже очищает лишь D7 и обновляет фильтры таблицы ptPersons. После этого ИД selPerson и rngShares обновляются самыми актуальными данными и далее, когда вы выбираете конкретного сотрудника, происходит лишь расчёт формулы в D9 и вывод на экран количества акций из диапазона rngShares.

Вам понравилось? 🙂

Источник: http://perfect-excel.ru/publ/excel/razlichnye_instrumenty_excel/dinamicheskie_kaskadnye_spiski_na_osnove_svodnykh_tablic/10-1-0-86

Как фильтровать данные сводной таблицы в Excel 2016 — манекены 2019

Когда вы создаете новую сводную таблицу, вы заметите, что Excel 2016 автоматически добавляет раскрывающиеся кнопки для поле «Фильтр отчетов». Эти кнопки позволяют вам фильтровать все, кроме определенных записей, в любом из этих полей.

Фильтрация отчета

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

В этой сводной таблице поле «Пол» используется как поле «Фильтр отчетов».

Например, в примерной сводной таблице (показано здесь), которая использует поле «Пол» из списка данных сотрудников в качестве поля «Фильтр отчетов», вы можете отображать сумму только зарплаты мужчин или женщин по отделам и местонахождение в теле сводной таблицы выполняет одно из следующих действий:

• Нажмите кнопку фильтра поля «Пол», а затем нажмите «М» в раскрывающемся списке, прежде чем нажимать «ОК», чтобы увидеть только общие суммы заработной платы мужчин по отделам.
• Нажмите кнопку фильтра поля «Пол», а затем нажмите «F» в раскрывающемся списке, прежде чем нажимать «ОК», чтобы увидеть только общие суммы заработной платы женщин по отделам.

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

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

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

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

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

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

Как и при фильтрации поля фильтра отчетов, Excel заменяет стандартную раскрывающуюся кнопку для этого поля столбца или строки с символом фильтра в виде конуса, указывая, что поле фильтруется и отображает только некоторые из его итоговых значений в сводной Таблица.Чтобы повторно отобразить все значения для поля фильтрованного столбца или строки, вам нужно щелкнуть по его кнопке фильтра, а затем щелкнуть (Выбрать все) в верхней части раскрывающегося списка. Затем нажмите «ОК».

На этом рисунке показана сводная таблица выборки после фильтрации поля «Фильтр гендерного отчета» для женщин и поле «Столбец» в «Учет, администрирование и человеческие ресурсы».

Сводная таблица после фильтрации поля «Фильтр гендерного отчета» и поля «Столбец».

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

Фильтрация с помощью slicers

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

Чтобы добавить слаймеры в сводную таблицу, вы выполните всего два шага:

1. Нажмите одну из ячеек в вашей чтобы выбрать его, а затем нажмите кнопку «Вставить слайсер», расположенную в группе «Фильтр» на вкладке «Анализ», в контекстной вкладке «Сводная таблица инструментов».

   Excel открывает диалоговое окно Insert Slicers со списком всех полей в активной сводной таблице.

2. Установите флажки для всех полей, которые вы хотите использовать для фильтрации сводной таблицы и для которых вы хотите создать слайзеры, а затем нажмите кнопку «ОК».

   Затем Excel добавляет слайзеры для каждого поля сводной таблицы, которые вы выбираете, и автоматически закрывает панель задач «Сводные поля», если она открыта в то время.

На этом рисунке показана сводная таблица выборки после использования слайсеров, созданных для полей «Пол», «Департамент» и «Место», для фильтрации данных, чтобы только заработная плата для мужчин в отделах персонала и администрации в Бостоне, Чикаго и Сан Франциско.

Пример сводной таблицы, отфильтрованной с помощью слайсеров, созданных для полей Gender, Dept и Location.

Поскольку slicers — это графические объекты Excel (хотя и довольно красивые), вы можете перемещать, изменять размер и удалять их так же, как и любой другой графический файл Excel. Чтобы удалить слайсер из сводной таблицы, щелкните его, чтобы выбрать его, а затем нажмите клавишу «Удалить».

Фильтрация с временными графиками

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

Чтобы создать временную шкалу для сводной таблицы, выберите ячейку в сводной таблице и нажмите кнопку «Вставить временную шкалу» в группе «Фильтр» на вкладке «Анализ контекста» на вкладке «Инструменты сводной таблицы» на ленте.

Затем Excel отображает диалоговое окно «Вставить временные рамки», отображающее список полей сводной таблицы, которые можно использовать при создании новой временной шкалы.

После выбора флажка для поля даты, которое вы хотите использовать в этом диалоговом окне, нажмите «ОК».

На этом рисунке показана временная шкала, созданная для списка данных Employee Data, выбрав поле Date Hired в диалоговом окне «Вставить временные рамки».

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

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

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

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

Сделайте это, перетащив строку временной шкалы на графике временной графики даты, чтобы она начиналась в январе 2000 года и расширялась включая Dec, 2000.

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

Источник: https://ru.howtodou.com/how-to-filter-pivot-table-data-in-excel-2016

Финансы в Excel

Подробности Создано 18 Февраль 2015

Вложения:

multilevelgroups.xls

[Многоуровневые группы]

44 kB

Еще один пример, расширяющий грани возможного в Excel.

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

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

Этот же алгоритм можно попытаться автоматизировать, используя программную проверку элементов фильтра. Решение вполне реализуемо, но по факту смотрится не очень красиво, так как в Excel не реализован вызов события на изменение автофильтра.

Можно также попытаться совсем не использовать встроенные возможности фильтрации Excel, вместо этого полностью реализовать проверку, скрытие и показ нужных строк с данными средствами VBA. Но это не наш метод — мы не используем программирование, там где без него можно обойтись.

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

Файл

В приложении к статье файл multilevelgroups.xls с решением задачи. Работает во всех версиях Excel, начиная с 2000 (10.0) (по идее Excel 2011 for Mac тоже должно работать, пока нет возможности проверить). Для автоматического обновления таблицы данных по выбору из списка необходимо подключить макросы Excel.

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

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

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

Для показа скрытых столбцов проще всего нажать на кнопку [+] структуры документа.

Важным полем справочника групп является FilterID — по нему можно проводить сортировку стандартными средствами Excel после добавления элемента в середину списка.

Сортировка для корректности функционирования примера, в принципе, не обязательна.

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

Взят за основу контрол из коллекции Forms, а не ActiveX Combobox — для возможности использования в Excel for Mac. Переделать пример на ActiveX Combobox не представляет особого труда.

В свойствах контрола указан источник значений списка ($D$2:$D$19) и служебная ячейка записи номера выбранного элемента ($H$1)

Как это работает

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

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

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

Префикс «=» в итоговых значениях поля Group определяет точное соответствие в условии, без него отбираются наименования, начинающиеся на значение в ячейке.

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

Для удобства использования диапазонам фильтрации и критериев были присвоены имена — listData и listCriteria (в примере выделены жирными рамками).

Sub Macro1()
Range(«listData»).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range(«listCriteria»), Unique:=False
End Sub

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

Ограничения

Из-за особенностей реализации имеются ограничения в справочнике групп:

• Общее количество элементов в каждой подгруппе — не более 99Формулы поддерживают два знака на каждую группу.
• Количество уровней вложенности — не более 4Задано в ячейке G1, можно увеличить
• Группы должны иметь уникальные наименования
• Заголовки полей фильтрации групп в таблице и в служебном столбце справочника должны быть одинаковые.В примере — Group.

Основная причина ограничений в размере поля FilterID, там должно храниться целое число. В каждом конкретном случае практической реализации можно настроить пример на расширение ограничений, например, увеличить максимальное количество групп, уменьшив лимит уровней вложенности.

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

Источник: https://www.excelfin.ru/index.php/articles/interface/225-interface-multilevelgroups

Как сделать фильтр в Excel

Работа с большими объемами данных в табличных формах Excel зачастую приводит к необходимости сортировки информации по тому или иному критерию. Как организовать данный способ фильтрации информации в таблицах Excel?

1

Организация работы фильтра в Excel – установка сортировки

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

2

Организация работы фильтра в Excel – параметры фильтрации

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

1. По нарастанию или убыванию значений (по алфавиту или с конца буквенного перечня). Для этого следует выбрать первую либо вторую строку выпавшего перечня.
2. По цвету. Данным фильтром можно корректно воспользоваться лишь в случаях, когда присутствуют закрашенные ячейки.
3. Следующий параметр – фильтр по тексту или числам – проводит выборку согласно установленного параметра. Им может выступать числовое значение или текстовая информация.

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

3

Организация работы фильтра в Excel – расширенная фильтрация

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

• перейти во вкладку данных;
• выбрать блок сортировки и фильтров;
• кликнуть меню “Дополнительно”.

Перед вами появится окно расширенных фильтров. Необходимо указать обрабатываемый диапазон, условия отбора, а также место расположения результатов выборки. В завершении следует нажать”Ок”.

Источник: https://sovetclub.ru/kak-sdelat-filtr-v-excel