Как вы все прекрасно знаете, в Excel 2010 появилась такая штука, как срез или slicer. Срез представляет из себя, по сути, кнопочный фильтр. Выглядит это вот так:

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

Так вот, в Excel 2010 можно было ассоциировать со сводными таблицами и со сводными диаграммами. В Excel 2013 стало возможно их применять и с умными таблицами. В этой статье я расскажу, как можно задействовать срезы для работы с обычными таблицами. Вы спросите зачем? Я вам отвечу, что на практике часто бывает так, что сводная таблица не может быть тем объектом, который вы показываете пользователю в качестве результата, так как, например, то, что вы в итоге должны показать, опирается на 2 и более сводных таблицы, поэтому результаты приходится собирать из кусков данных промежуточных сводных таблиц. Тут не может быть ничего гибче обычной таблицы, которую вы можете контролировать на 100%.

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

Файл примера

Скачать

Исходные данные

Исходные данные, располагаются на листе Data в умной таблице tblSales.

Классика

Для начала покажем, как срезы используются для свобной таблицы. Смотрим лист примера Метод 1 - сводная таблица. Тут сделано следующее:

1. Добавлена сводная таблица на основе tblSales. Она элементарная - я хочу видеть продажи в разрезе регионов и модельного ряда.

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

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

4. По мелочи: сводная таблица и срезы получили стили форматирования, которые более-менее органично смотрятся друг с другом, срезы были настроены для расположения над таблицей, в частности, количество столбцов у них сейчас 6, а не 1 (так по умолчанию).

В результате я, не написав ни единой формулы и уж тем более ни строчки VBA кода, получил добротный пользовательский отчёт, который выглядит достаточно эстетично и его не стыдно показать боссу или клиенту.

Я могу выбирать не обязательно один какой-то год / месяц - можно выбрать набор лет / месяцев и сводная таблица без проблем отобразит правильные результаты. Единственный недостаток - это то, что довольно затруднительно получить текущие параметры фильтрации, чтобы, к примеру, автоматически формировать заголовок нашей таблицы. Параметры фильтрации можно получить при помощи VBA, но в данной статье я не буду об этом говорить.

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

Теперь давайте обсудим, как нам приспособить такой полезный инструмент, как срез, к обычной таблице Excel. Смотрим на лист Метод 2 - обычная таблица. Как видите, я полностью воспроизвёл дизайн сводной таблицы, однако сердцевина таблицы состоит из формул СУММЕСЛИМН, которые через один полезный приём, о котором я собираюсь вам рассказать, получают параметры фильтрации из срезов.

Это устроено следующим образом:

1. На листе Ref я создал 2 вспомогательных умных таблицы tblYears и tblMonths. Они ни с чем не связаны - просто значения, которые мы хотим видеть в наших срезах для обычной таблицы.

   

2. На том же листе созданы 2 крошечные сводные таблицы ptYears и ptMonths на основе соответствующих вышеперечисленных вспомогательных таблиц.

   

   Особенностью этих сводных таблиц является то, что они состоят только из фильтра.

   

3. Для каждой сводной таблицы был добавлен срез, который оказывается связанным с фильтром по полям Годы таблицы ptYears и Месяцы таблицы ptMonths. Выбор какого-либо года в срезе автоматически приводит к фильтрации по таблице ptYears.

   

4. Если в срезе по годам не выбрано ничего, то ячейка Ref!F1 будет содержать значение "(Все)", если выбран конкретный год, то - значение этого года, если - несколько лет, то - значение "(Несколько элементов)". Вот этим мы и пользуемся. В Ref!F3 я размещаю формулу, которая принимает значение 0, если в срезе не выбрано ничего, значение года, если выбран год, и значение -1 в остальных случаях (то есть случай выбора нескольких лет). Для удобства на базе F3 создаю именованный диапазон SelectedYear. Та же история и со вторым срезом.

5. Теперь у нас в SelectedYear и SelectedMonth содержатся либо год/месяц, либо 0 (ничего не выбрано), либо -1 (безобразие со множественным выбором). Эти именованные диапазоны мы используем в нашей таблицы, где при помощи формул ЕСЛИ (IF), И (AND) и, конечно же, СУММЕСЛИМН (SUMIFS) выдираем данные из tblSales. Формула выглядит страшновато, но на самом деле она шаблонна и просто уточняет состояние вышеуказанных ИД, чтобы применить формулу суммирования с нужными параметрами. Например, если SelectedYear = 0, а SelectedMonth >0, то никакой год не выбран, но выбран какой-то месяц, поэтому надо из формулы СУММЕСЛИМН убрать критерий для года, но оставить критерий для месяца. Вот и всё - остальное по аналогии.

   

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

   

Конечный результат:

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

Читайте также:

• Формула СУММЕСЛИМН (SUMIFS) и другие этого семейства

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

• Ценные советы начинающим аналитикам

• Dashboards: как это делают профи. Часть 1.

• Dashboards: как это делают профи. Часть 2.