Excel + Лайфхак

Вы создали небольшую сводную таблицу (Вставка – Сводная таблица):

Но вашему начальнику не нравится внешний вид отчёта, и он хочет видеть что-то похожее на это:

То есть мы имеем несколько ощутимых трудностей:

1. Исходный внешний вид сводной таблицы не подходит - дизайн отчёта должен соответствовать корпоративным стандартам (цвета, логотипы, спарклайны, стрелки и т.д.). "Дорабатывать напильником" дизайн сводной - долгий и мучительный процесс. И не факт, что красота не слетит после пересчёта и обновления.

2. Из всей сводной для отчёта вам нужны не все данные, а только конкретные модели Ford по Питеру - придётся руками фильтровать.

3. Стандартные итоги в сводной нам не подходят, т.к. нужны суммы по выручке в зелёных ячейках, но среднее по месяцу в итогах - сводная так не умеет.

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

5. Нужно построить по результатам хитрую диаграмму (обычные сводные диаграммы имеют много ограничений).

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

1 способ. Прямая ссылка на ячейку в сводной

Это, что называется, решение проблемы «в лоб». Сделаем на отдельном листе заготовку отчёта:

Теперь в ячейку D7 можно вручную прописать ссылку:

=Лист1!B8

Где Лист1 – имя листа со сводной таблицей, а B8 – нужная нам ячейка в сводной с данными по продажам Ford Fiesta за январь.

При внешней простоте и очевидности у этого способа есть две проблемы:

1. Если сортировка моделей и дат в нашем красивом «отчёте для шефа» отличается от сводной, то скопировать созданную ссылку не получится и придётся делать их для каждой модели автомобиля персонально. А если нужно извлечь много данных, то придётся делать много ссылок вручную.

2. Завтра, после обновления, структура сводной таблицы может измениться - например, Fiesta может оказаться уже не третьей, а седьмой строкой, Focus переехать во вторую и т.д. И тогда все ссылки придётся переделывать.

2 способ. Функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ

Изящным решением всех этих проблем может стать функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GETPIVOTDATA), которая умеет извлекать нужные нам данные из сводной, чтобы использовать их в других таблицах или расчетах.

Чтобы её использовать, убедитесь, что при выделении любой ячейки сводной таблицы на вкладке Анализ (Analysis) или Параметры (Options) в выпадающем списке Параметры (Options) включена галочка Создать GetPivotData:

Теперь выделите первую ячейку зелёного диапазона, введите знак "равно" и щёлкните по ячейке в сводной, которая содержит нужные данные, т.е. по B8, где лежит выручка Fiesta за январь. Вместо привычной ссылки Excel вставит функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ:

=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ

("Выручка";'Сводная для отчета'!$A$4;

"Наименование";$C7;"Дата";1)

Давайте разберём её подробно:

1. Первый её аргумент ("Выручка") – это имя извлекаемого поля.

2. Второй (Лист1!$A$4) - это адрес первой ячейки сводной таблицы, откуда мы берём данные. Этот параметр нужен, т.к. на листе может быть несколько сводных и Excel должен понимать, из какой именно нужно вытащить число.

3. Все остальные аргументы начиная с третьего – это попарно название поля и его значение, т.е. в нашем случае это имя модели (Наименование="Fiesta") и временной период (Дата=1). Поскольку в сводной была применена группировка дат по месяцам, то в функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ мы получили не имя месяца, а его номер. Если бы в исходной базе данных был столбец не с датой, а с названием месяца, то группировка была бы не нужна и вместо единички был бы просто "январь".

У этой функции есть несколько серьёзных преимуществ перед обычной ссылкой на B8, которая приводила бы, на первый взгляд, к тому же результату. Главный плюс в том, что если завтра после обновления в сводной таблице изменится количество строк/столбцов или Ford Fiesta станет не третьей, а пятой строкой, то нам об этом волноваться уже не придётся – функция корректно извлечёт нужное нам значение. Достаточно только обновить сводную правой кнопкой мыши – и наша красивая форма отчёта «для шефа» пересчитается автоматически.

Далее, замените в формуле "Fiesta" на $С7 (т.е. на ячейку с названием модели), а единичку на D$5 (т.е. ячейку с номером месяца) и допишите в конце формулы деление на 1000, т.к. нам нужно отобразить данные в тысячах. Затем нажмите на Enter и протяните формулу на оставшиеся зелёные ячейки.

=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ

("Выручка";'Сводная для отчета'!$A$4;"Наименование";

$C7;"Дата";D$5)/1000

Функция извлечёт из сводной нужные нам данные, заполнив нашу корпоративную форму отчёта.

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

Формула для прогноза: =ПРЕДСКАЗ($G$5;D7:F7;$D$5:$F$5)

=FORECAST($G$5;D7:F7;$D$5:$F$5)

Формула для среднего значения: =СРЗНАЧ(D7:D10)

AVERAGE(D7:D10)

Формула для динамики: =D13/D14-1

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

Пример принципа работы группировки таблицы в гифке:

Чтобы создать группировку таблицы выделяем нужное количество строк или столбцов, а затем выбираем на вкладке Данные – Группировать (Data – Group), либо нажатием сочетания клавиш Alt+Shift+стрелка вправо, а для разгруппировки Alt+Shift+стрелка влево, соответственно. В результате они будут сгруппированы.

В итоге у нас получится вот такая таблица

Нажатием на «+/-» или цифры можно сворачивать и разворачивать ячейки и столбцы. Группы можно делать вложенными одна в другую (до 8 уровней вложенности).

Также, если в таблице имеются итоговые строки или столбцы с функцией суммирования соседних ячеек, то есть шанс (не 100%-ный), что Excel сам создаст все нужные группировки в таблице одним движением – с помощью команды Данные – Группировать – Создать структуру (Data – Group – Create Outline). Однако, данная функция работает весьма непредсказуемо на сложных таблицах и порой выдаёт полный бред, но проверить можно.

Для этого, уже имеющуюся таблицу с данными, необходимо отформатировать как таблицу «Главная» - «Форматировать как таблицу» (Home – Format as Table), где выбираем любой дизайн. В моей таблице 3000 строк, ссылку на файл оставлю в комментариях.

Теперь, поставив курсор на ячейку в шапке, переходим на появившуюся динамическую вкладку Работа с таблицами: Конструктор (Table Tools: Design) и нажимаем кнопку Вставить срез (Insert Slicer). В открывшемся окне отмечаем флажками названия столбцов, по которым будем фильтровать.

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

Если срез содержит очень много элементов (кнопок), то их можно расположить в несколько столбцов, как на срезах «Месяц» и «Бригадир». Для этого выделите срез и увеличьте для него количество столбцов на вкладке Инструменты для среза: Параметры (Slicer Tools: Options). Здесь же можно выбрать Стили срезов.

Дополнительные параметры выделенного среза можно настроить с помощью кнопки Настройка среза (Slicer Settings) на той же вкладке:

Удерживая клавиши Ctrl или Shift можно выделять сразу несколько элементов среза. Для сброса фильтра нажимаем кнопку Удалить фильтр (Clear Filter) в правом верхнем углу среза.

Также, чтобы скрыть срезы при печати документа нажимаем правой клавишей мыши по срезу и в разделе Размер и свойства - «Свойства» снимаем галочку с «Выводить объект на печать».

Либо, выделив область таблицы, в параметрах печати можно выбрать «Напечатать таблицу» или только «Выделенный фрагмент».

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

Дальше используем функцию ИНДЕКС, чтобы извлечь данные из набора нескольких таблиц

=ИНДЕКС((C4:E8;H4:J8;M4:O8;R4:T8);X3;X4;W5)

=INDEX((C4:E8;H4:J8;M4:O8;R4:T8);X3;X4;W5)

В результате в ячейке X4 получаем данные из таблиц:

Можно же обойтись без дополнительных формул и сразу ввести следующую формулу в удобную для вас ячейку:

=ИНДЕКС((C4:E8;H4:J8;M4:O8;R4:T8);

ПОИСКПОЗ(W3;Q4:Q8;0);

ПОИСКПОЗ(W4;R3:T3;0);W5)

=INDEX((C4:E8;H4:J8;M4:O8;R4:T8);

MATCH(W3;Q4:Q8;0);

MATCH(W4;R3:T3;0);W5)

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

Сперва на вкладке «Формулы» - «Диспетчер имен» создадим именованные диапазоны, которые на них указывают:

Далее используем формулу для поиска номера строки товара:

=ПОИСКПОЗ(W4;ИНДЕКС(ДВССЫЛ(W3);0;1);0)

=MATCH(W4;INDEX(INDIRECT(W3);0;1);0)

Для тех, кто предпочитает знать как всё устроено, разберём её подробно))

Во-первых, функция ДВССЫЛ(W3) в данном случае представляет собой ссылку на именованный диапазон 4-го квартала. Прямую ссылку на ячейку с именем W3 использовать нельзя, т.к. Excel будет воспринимать ее как текст. Чтобы превратить текст «Квартал4» в живую ссылку на именованный диапазон «Квартал4», и нужна функция ДВССЫЛ (INDIRECT).

Во-вторых, фрагмент: ИНДЕКС(ДВССЫЛ(W3);0;1)

… представляет собой ссылку на первый столбец именованного диапазона «Квартал4», т.е. на Q3:Q10.

Как это получилось?

Классический вариант использования функции ИНДЕКС на одной двумерной таблице, напомним, предполагает три аргумента: =ИНДЕКС(диапазон; номер_строки; номер_столбца)

В этом случае мы получим содержимое ячейки в указанном диапазоне с пересечения строки и столбца с заданными номерами.

Хитрость в том, что если номер строки равен 0, то ИНДЕКС выдает уже не содержимое ячейки, а ссылку на весь столбец с указанным номером, т.е. на первый столбец именованного диапазона заданного ДВССЫЛ(W3), т.е. на ячейки Q3:Q10.

Ну а затем функция ПОИСКПОЗ (MATCH) ищет в этом диапазоне требуемый товар (Пиво) и возвращает его позицию (4 строка, т.к. пустая Q3 тоже считается).

Аналогично можно найти номер столбца с нужной страной:

=ПОИСКПОЗ(W5;ИНДЕКС(ДВССЫЛ(W3);1;0);0)

=MATCH(W5;INDEX(INDIRECT(W3);1;0);0)

Только в этом случае нулю равен не номер строки, а номер столбца, чтобы получить ссылку на первую строку именованного диапазона «Квартал4», где затем функция ПОИСКПОЗ будет искать «KZ».

И, последним останется вытащить количество заказов функцией ИНДЕКС:

=ИНДЕКС(ДВССЫЛ(W3);X4;X5)

=INDEX(INDIRECT(W3);X4;X5)

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

P.S. Ссылка на файл примера в комментариях.

P.P.S. Уважаемые подписчики, Вас уже больше 7000 человек, это приятно радует и вдохновляет на создание новых постов)) В комментариях Вы писали, что я один из немногих, на кого Вы подписаны, кто-то впервые подписался на меня, а кто-то специально зарегистрировался, чтобы подписаться)) Это очень приятно и я не хочу Вас разочаровывать.

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