Ранее мы уже писали о полезных приемах при работе в MS Excel:
Сегодня хочется рассказать об одном из самых мощных инструментов программы Microsoft Excel – сводных таблицах.
Что такое сводные таблицы Excel и для чего они нужны?
Сводные таблицы Excel являются инструментом анализа и визуализации данных. С их помощью можно суммировать, группировать и проводить множество других манипуляций с большими объемами данных, представленных в электронной таблице.
Как создать сводную таблицу?
- Выбрать необходимый диапазон исходной таблицы либо кликнуть на любую ячейку исходной таблицы (в этом случае автоматически выделится вся таблица);
- “Вставка” (1) → “Сводная таблица” (2). Появится окно для задания параметров вставки:
Сводную таблицу можно вставить как на новый лист, так и на существующий. Для удобства и наглядности выбираем существующий (3). Далее указываем желаемый диапазон вставки (4):
и нажимаем “Ок”:
Появился макет сводной таблицы, в котором потребуется задать необходимые параметры. Как задавать параметры зависит от поставленной задачи. Например, чтобы узнать, какое количество товаров в сумме продал каждый продавец за отчетный период, перетащим с помощью мыши поле “Продавец” в область “Название строк” (5), а поле “Количество проданного” в область “Значения” (6) и получим такую таблицу (7):
Либо, расположив данные таким образом (8), получим более наглядную информацию по проданным товарам (9):
Таблицы называют сводными, потому что они “сводят” значения по заданным критериям. У сводных таблиц очень много возможностей, они способны значительно облегчить работу с данными. В этой статье мы рассмотрим секретные приемы, которые известны не каждому.
Важно! Чтобы при создании сводной таблицы не возникало ошибок, необходимо обратить внимание на несколько моментов:
- шапка таблицы должна быть заполнена: каждый столбец таблицы должен иметь название;
- в таблице не должно быть пустых строк (при наличии пустой строки сводная таблица будет формироваться только по тем данным, которые находятся над этой пустой строкой) и пустых колонок (в том числе скрытых);
- рядом с исходной таблицей не должно быть никаких дополнительных данных (они могут быть взяты в учет при анализе и Excel выдаст ошибку).
Секретные приемы при работе со сводными таблицами
Применение формулы к нескольким ячейкам
Если вам нужно применить какую-либо формулу ко всем столбцам таблицы, необходимо знать пару хитростей. Дело в том, что в сводных таблицах не сработает привычный способ. Допустим, вам необходимо получить 50% от итогового результата каждого столбца. Если вы последуете привычному алгоритму: “= → клик по ячейке → *50% → Enter”, то результат вы получите:
Но, при протягивании до конца таблицы, эта формула не применится ко всем столбцам, а протянется исходное значение:
И, чтобы формула все-таки применилась и не пришлось вбивать ее вручную каждый раз, есть 2 способа решения:
– написать вручную ссылку на нужную нам ячейку: “=H33*50% → Enter” и потом потянуть вправо, то формула применяется и к остальным столбцам:
– поменять настройки Excel: “Файл” → “Параметры” → “Формулы” → убрать галочку с позиции “Использовать функции GetPivovData для ссылок в сводной таблице” → “Ок”:
Теперь формула будет работать, даже если вы ее написали не вручную, а используя ссылку на ячейку.
Срезы
В сводных таблицах Excel срез – это функция, которая позволяет фильтровать и просматривать подмножество данных из сводной таблицы, то есть делает отчеты интерактивными. Срезы предоставляют способ исследовать различные сегменты ваших данных и сравнивать результаты.
Когда вы создаете срез, появляется отдельное окно или диалоговое окно, в котором отображается список полей или категорий из сводной таблицы. Вы можете выбрать один или несколько элементов из этих полей, чтобы создать фильтр. После применения срез будет отображать только те данные, которые соответствуют выбранным критериям, скрывая остальную часть данных.
Чтобы вставить срез, нужно выполнить следующее: “Вставка” → “Срез” → в появившемся окошке выбираем нужный критерий → “Ок”.
Появится небольшая табличка с перечнем продавцов (заданный нами критерий). Кликая по конкретному продавцу, мы увидим какое количество товаров каждого наименования он реализовал:
Пара дополнительных фишек:
- зажатая клавиша “Ctri” позволяет выбрать сразу несколько позиций на срезе и отобразить данные по ним в таблице;
- если выбрать верхнюю позицию, нажать и удерживать кнопку “Shift” и кликнуть по нижней позиции, то выберутся все позиции между ними.
Можно вставлять сразу несколько срезов и к каждому применить свой дизайн:
Добавление одного и того же поля несколько раз
В данном случае мы добавили трижды поле “Количество проданного” в область “Значения” и получили 3 одинаковых столбца в сводной таблице:
Теперь мы можем каждый из столбцов отформатировать нужным нам образом в разделе “Параметры поля” (10) и поменять их названия:
Так мы можем оценить одни и те же показатели с разных сторон.
Создание вычисляемых полей
“Параметры” (11) → “Поля, элементы и наборы” (12) → “Вычисляемое поле”:
В появившемся окне нужно дать название новому полю (13) и вписать требуемую формулу (14), нажать “Ок”. Справа появится новое поле с готовыми расчетами.
Таким образом можно рассчитывать сумму без НДС, сумму в другой валюте по курсу и производить любые другие расчеты.
Что полезного узнали из статьи? Какие у вас любимые фишки при работе в Excel?
Ставьте 👍🏼 статье, если было полезно.
Подписывайтесь на канал Swiss International Institute Lausanne — SIIL, чтобы не пропустить новые статьи.
Аккаунты SIIL в соцсетях: RuTube Telegram YouTube ВКонтакте