Друзья, всем привет. Сегодня хочу рассказать вам про несколько полезных и интересных (с моей скромной и субъективной точки зрения) трюков при работе со сводными таблицами. Здесь не будет подробного разбора про работу в сводных таблицах: что это, для чего и почему. Материал предназначен для тех, кто уже хоть как-то знаком с этим прекрасным инструментом и знает, что это за зверь такой. Если же вы ещё не работали со сводными таблицами, при этом проводите много времени в Excel и строите разного рода отчёты, аналитику, то я вам настоятельно рекомендую как можно скорее освоить этот поистине чудесный инструмент. Поверьте, вы откроете для себя абсолютно новый мир :)
1 - Фильтры для полей сводной, которые по умолчанию фильтровать нельзя.
Поля, которые находятся в области значений, нельзя фильтровать привычным для нас образом. Там просто нет фильтров, и установить их обычным способом нельзя, команда не активна:
Но если нельзя, но очень хочется, то можно.
Решение: нужно поставить курсор в соседнюю ячейку справа от заголовка поля и установить фильтр. Всё, готово. Теперь можно фильтровать значения, как в обычной таблице.
2 - Изменение порядка элементов в списке.
Вообще, есть несколько способов менять порядок элементов в сводной. Я хочу рассказать вам про наиболее удобный. Предположим, мы хотим, чтобы список менеджеров начинался с Чайниковой:
Решение: выбираем ячейку с Баранкиным и вероломно начинаем печатать фамилию Чайниковой. Целиком печатать не нужно, программа сама предложит готовый вариант. Нажимаем Enter. Вуаля! Теперь Чайникова на вершине нашего списка:
GIF●60 Кб
Ещё один способ: это обычное копирование и вставка. Выбираем ячейку с Чайниковой, копируем её. Далее выбираем ячейку с Баранкиным (место, куда хотим переместить наше значение), и вставляем.
3 - Использование собственных списков для сортировки.
Прошлый способ хорош, если нужно сделать это однократно. Но если у нас уже есть какой-то устоявшийся порядок, он вот совсем не по алфавиту, а бездушный Excel всегда всё сортирует именно по алфавиту?
Решение: многие знают, что если мы напишем в ячейке "январь" ("февраль", "март" и т.д.), потом протащим ячейку за правый нижний угол вниз, то всемогущий Excel автоматически заполнит следующие ячейки месяцами по порядку. Но не только лишь все знают, что такую штуку можно сделать и по своему списку.
Шаг 1. Создаём свой список (с блэкджеком и порядком). Где-нибудь на листе Excel создаём список в том порядке, в котором нам нужно. Далее заходим в Файл - Параметры - Дополнительно - Изменить списки:
В поле "Импорт списка из ячеек:" указываем диапазон, в котором находится наш список, далее нажимаем Импорт. Всё, подготовительная работа завершена:
Шаг 2. Для сортировки данных используем наш список. Теперь можно применить сортировку от А до Я в столбце с менеджерами. И вот ведь неожиданность, сортировка будет не по алфавиту, а согласно нашему списку:
GIF●77 Кб
К сожалению, данный приём имеет ограничения в части обновления списка. Если добавится новый сотрудник, то при обновлении сводной сотрудник будет в самом конце списка независимо от того, какая у него там первая буква фамилии. Дальше либо добавлять его в список, либо вручную перетаскивать в нужное место.
Если нужно будет вернуть стандартную православную сортировку по алфавиту для данного списка, необходимо раскрыть значок фильтра, выбрать Дополнительные параметры сортировки - по возрастанию - Дополнительно - снять галочку с "Автоматическая сортировка..." - в поле ниже выбрать "Без вычислений":
Кстати, созданные нами списки будут работать так же, как встроенные. То есть если вписать в ячейку Баранкина, протащить вниз, в следующих ячейках будут фамилии по списку.
4 - Отображение пустых строк.
При разбивке какого-то показателя на несколько групп/категорий в сводных таблицах не отображаются данные, которых нет в источнике. В целом, это логично. Но согласитесь, что если мы, к примеру, хотим проанализировать показатель по сотрудникам по месяцам, то вот такая картина будет только нас запутывать:
Месяцы идут по порядку, да, но у кого-то пропущен июль, у кого-то февраль и октябрь.
Решение - щёлкаем правой кнопкой мыши по любой ячейке того поля, в котором хотим отображать ВСЕ элементы (в том числе и пустые) - далее Параметры поля... - вкладка Разметка и печать - ставим галочку Отображать пустые элементы.
У этого приёма есть один побочный эффект - если речь идёт про работу с датами, то отображаются даты до самого раннего числа и после него (видно на гифке). Избавиться от этого довольно просто: нужно убрать их через фильтр, сняв галочки.
5 - Топ-N сотрудников/месяцев/товаров и так далее.
Вообще, отображение топ-3/5/10 сотрудников или любого другого - это не привилегия фильтра в сводной таблице. Работая с фильтрами в обычных таблицах, у нас есть точно такая же возможность. Но про эту возможность, исходя из моего хоть и небогатого, но всё же опыта, мало кто знает. То ли нафиг никому не нужно, то ли просто пользователи не знают. Как по мне, вещь полезная. Особенно с учётом того, что в сводной таблице фильтр будет обновляться вместе с внесением изменений в источник (и обновления самой сводной, конечно же), чего в обычных таблицах, увы, нет.
Решение - всё просто. Раскрываем фильтр по тому полю сводной, по которому хотим выделить топ-N чего-нибудь - далее Фильтр по значению - Первые 10 - указываем нужные нам настройки.
Правда, без побочных эффектов тут, к сожалению, тоже не обошлось. Если у какого-то сотрудника не будет хватать данных для того, чтобы отобразить топ-3, то по этому сотруднику (или по тому, по чему вы ищете топ-N) будут вообще все данные. Поведение более чем странное, но как есть :(
6 - Повтор подписей и промежуточные итоги для отдельных полей, а не для всей таблицы.
Когда у нас с вами в сводной таблице в области строк больше двух полей, то сразу возникает вопрос про промежуточные итоги. И если идти стандартным путём, то есть через вкладку Конструктор - группа Макет - Промежуточные итоги, то там мы можем играться с промежуточными итогами для всей таблицы, а не для конкретного поля. Порой, это неудобно. Как в примере ниже:
Я бы хотел оставить промежуточный итог по сотруднику, а вот по месяцу убрать.
И сразу второй момент. Когда меняем макет сводной на табличный или форму структуры, то возникает вопрос с повторением подписей элементов, чтобы не было пустых ячеек. Если перейти на вкладку Конструктор - группа Макет - раскрыть Макет отчёта и выбрать Повторять все подписи элементов, то это сработает для всей таблицы. А я, например, хочу только по месяцам подписи.
Решение - великая и могучая правая кнопка мыши. Для того, чтобы добавить/убрать промежуточные итоги у определённого поля сводной таблицы, щёлкаем правой кнопкой мыши по любой ячейке этого поля и в контекстном меню находим Промежуточный итог...
Что касается подписей элементов, то щёлкаем правой кнопкой мыши по тому полю, где хотим/не хотим повторять подписи - далее Параметры поля... - на вкладке Разметка и печать находим галочку Повторять все подписи элементов.
7 - Объединение ячеек и выравнивание подписи по центру.
А если вам вообще не нужны все эти повторения, вы хотите объединённые ячейки с надписью по центру без всех этих плюсов/минусов, то и тут сводная сдюжит.
Решение - для объединения ячеек и выравнивания подписи по центру щёлкаем по любой ячейке сводной таблицы правой кнопкой мыши (данная настройка будет работать для всех полей сводной, для какого-то конкретного поля настроить возможности нет) - в контекстном меню находим Параметры сводной таблицы - вкладка Макет и формат - находим галочку Объединить и выровнять по центру ячейки с подписями.
А чтобы убрать плюсы/минусы, нужно выбрать любую ячейку сводной - далее вкладка Анализ сводной таблицы (в предыдущих версиях просто Анализ) - группа Показать - иконка Кнопки.
Заключение. На этом, пожалуй, всё. Вообще, всяких разных интересных штук при работе со сводной таблицей огромное множество. Про все и не напишешь. Делитесь в комментариях своими интересными приёмами. Думаю, многим будет интересно и полезно узнать что-то новое (мне так точно). Спасибо всем огромное, кто потратил своё драгоценное время и внимание на прочтение данного поста. Надеюсь, что-то из описанного было для вас полезным и вы сможете использовать это при работе со сводными таблицами.
