rss
  •  
Обучение Microsoft Excel: от основ до PowerBI

Фильтр отчета сводной таблицы в несколько столбцов. Возможно ли?

| Категория: Приемы и советы, Работа с табличными массивами |

12

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

Конечно, можно использовать альтернативный фильтр – срез [slicer]. Но и это не всегда позволяет добиться компактности расположения. Многие не задумываются, что и это можно легко настроить.

Например, изначально в фильтре отчета размещено 6 полей:

ptf1.png

Чтобы добиться более компактного расположения, следует:

  • Щелкнуть правой кнопкой мыши по любой ячейке сводной таблицы и выбрать Параметры сводной таблицы [Pivot Table Option], перейти на вкладку Макет и формат [Layout & Format]
  • Установить нужное значение в поле Число полей фильтра отчета в столбце [Report filter fields per column]
    при необходимости задать порядок в поле Отображать поля в области фильтра отчета [Display fields in report filter area]: вниз, затем поперек [Down, Then Over] или поперек, затем вниз [Over, Then Down]

  ptf2.png

И результат не заставил себя долго ждать! 

ptf3.png

Создание зависимых списков с изменяемым источником

| Категория: Приемы и советы, Работа с данными ячеек, Формулы и функции |

15

С помощью Проверки данных [Data Validation] можно организовать ввод данных путем выбора из предлагаемого списка, значения которого зависят от другого списка. Причем, используя функцию СМЕЩ [OFFSET] можно создать вариант, когда добавленные исходные значения будут отображаться в списках для выбора нужных значений. Если значения списка зависят от выбранного значения из другого списка, то можно создать связанный (зависимый) список нужных значений. Это позволит в значительной степени избежать не корректных комбинаций вводимых значений.

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

Последовательность создания:

  • Выделить ячейку F2, где будет выбираться Европа.
    На вкладке Данные [Data], в группе Работа с данными [Data Tools], выбрать Проверка данных [Data Validation] и на вкладке Параметры [Option], задать Условие проверки [Validation criteria] – Список [List], в качестве источника выделить ячейки B2 и C2

dv2.png

  • Ячейкам значений стран (данные в столбцах B и C) необходимо присвоить имена – Западная и Восточная, с возможностью автоматического определения диапазона ячеек по мере изменения количества значений в соответствующих столбцах:
    На вкладке Формулы [Formulas] выбрать Диспетчер имен [Name Manager] или нажать клавиши Ctrl+F3.
    Создать имена с использованием функции СМЕЩ:

dv3.png

  • Выделить ячейку F3, где будет выбираться Страна.
    На вкладке Данные [Data], в группе Работа с данными [Data Tools], выбрать Проверка данных [Data Validation] и на вкладке Параметры [Option], задать Условие проверки [Validation criteria] – Список [List], в качестве источника ввести формулу:
    =ЕСЛИ($F$2=”Западная”;Западная;Восточная)
    [=IF ($F$2=”Западная”;Западная;Восточная)], где F2 – ячейка, которая содержит значение, выбираемого из первого списка.

dv4.png

При добавлении новых данных, они будут сразу показаны в выпадающем списке:

dv5.png

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

Удаление из фильтров сводной таблицы “старых” данных

| Категория: Приемы и советы, Работа с табличными массивами |

24

Ситуация довольна простая. По источнику данных происходит построение отчета сводной таблицы. Затем в источнике происходит изменение данных, но при обновлении отчета в фильтрах видны как прежние, так и новые значения. Как избавиться от “старых” (прежних) данных?

Например, строим отчет по источнику, в котором есть данные 4-х городов:

pvd1.png

и получаем:

pvd2.png

Теперь произведем замену в исходных данных, например, город Казань заменим на Ульяновск:

pvd3.png

Однако, выполнив команду Обновить [Refresh] в сводной таблице (клавиши Alt+F5), данные отображаются в ячейках корректно в соответствии с данными источника:

pvd4.png

но в фильтрах осталось и прежнее значение – Казань:

pvd5.png

Чтобы убрать из фильтра уже не существующие данные, следуют в контекстном меню отчета выбрать команду Параметры сводной таблицы [PivotTable Options] и на вкладке Данные [Data] в списке Число элементов, сохраняемых для каждого поля [Number of items to retain per field] выбрать Нет [None].

solution.png

выполнить обновление (Alt+F5), после чего элементы в фильтре содержатся актуальные:

pvd6.png