Сводные таблицы в excel

Работа со сводными таблицами в Excel

Изменить существующую сводную таблицу также легко. Посмотрим, как пожелания директора легко воплощаются в реальность.

Заменим выручку на прибыль.

Товары и области меняются местами также перетягиванием мыши.

Для фильтрации сводных таблиц есть несколько инструментов. В данном случае просто поместим поле «Менеджер» в область фильтров.

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

Как создать дашборд в Excel

Как только человек осваивает методику создания сводных таблиц, он может приступать к еще одному способу отображения итоговых данных, который строится на них. Это дашборды – очень удобный способ визуального представления информации на основе определенного диапазона данных.

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

Как правило, в компаниях созданием умных таблиц и ограничиваются, в то время как дашборды имеют огромное количество преимуществ:

1. Дает возможность гибко управлять элементами отчета, сделав акцент на наиболее актуальных показателях или заменять их в случае необходимости.
2. Дает возможность компактно уместить всю необходимую информацию буквально на одном листе, что позволяет экономить бумагу, если начальство требует печатать отчеты. 
3. С помощью дашбордов легко сравнивать ключевые показатели за разные периоды. 

Кроме всего прочего, умение работать с дашбордами говорит о профессионализме сотрудника. Такой навык сразу поднимает его на голову выше в глазах руководства.

Существует множество способов создания дашбордов Excel, но всегда нужно начинать с эскизов прямо на листе бумаги. Нужно отрисовать, какие блоки на каких местах будут находиться. Далее будет значительно проще создавать дашборд. В частности, создать дашборд в Excel можно с помощью надстройки PowerView. Также визуализация может осуществляться такими методами:

1. Фигуры и объекты Word Art. Они позволяют рисовать все, что угодно, вплоть до инженерных чертежей. Кроме этого, есть множество текстовых меток, которые позволяют описать любую составную часть дашборда.
2. Использование сводных таблиц.
3. Графики, которые могут в качестве данных также использовать исходный диапазон.

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

Сводные таблицы Excel

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

Теперь, когда в нашем распоряжении имеется сводная таблица, наступило время для создания, вычисляемого поля. Чтобы создать вычисляемое поле, активизируйте диалоговое окно Вставка вычисляемого поля (Insert Calculated Field).

Рис. 5.6. Создайте сводную таблицу, в которую будет добавляться вычисляемое поле

Чтобы открыть его, на контекстной вкладке ленты Параметры (Options), выбранной в группе контекстных вкладок Работа со сводными таблицами (PivotTable Tools), перейдите в группу Вычисления (Calculations), щелкните на кнопке Поля, элементы и наборы (Fields, Items & Sets) и выберите в раскрывающемся меню команду Вычисляемое поле (Calculated Field), как показано на рис. 5.7.

Рис. 5.7. Создание вычисляемого поля начинается с этого раскрывающегося меню

На экране появится диалоговое окно Вставка вычисляемого поля (Insert Calculation Field), как показано на рис. 5.8.

Рис. 5.8. Диалоговое окно Вставка вычисляемого поля поможет в создании вычисляемого поля для сводной таблицы

В верхней части диалогового окна имеются два текстовых поля: Имя (Name) и Формула (Formula). В этих полях следует задать имя вычисляемого поля и создать формулу, указав необходимые поля данных и математические операторы. На рис. 5.9 показано, что вычисляемому полю присвоено описательное имя Средняя выручка за час. Это имя должно точно характеризовать тип выполняемой математической операции.

Рис. 5.9. Выбор для вычисляемого поля описательного имени

Далее перейдите к списку Поля (Fields) и дважды щелкните на поле Объем продаж. Введите символ косой черты /, чтобы указать программе Excel, что вы будете делить значение поля Объем продаж на другой элемент.

По умолчанию текстовое поле Формула диалогового окна Вставка вычисляемого поля содержит выражение = 0. Перед вводом собственной формулы следует удалить нуль.

С этого момента диалоговое окно должно выглядеть так, как показано на рис. 5.10.

Рис. 5.10. Начните ввод формулы со значения = ‘Объем продаж’/

Закончите ввод формулы, дважды щелкнув на поле Период продаж (в часах). Готовая формула показана на рис. 5.11.

Рис 5.11. Полная формула = ‘Объем продаж’/’Период продаж (в часах)’ отображает требуемое вычисляемое поле

Щелкните на кнопке Добавить (Add), а затем — на кнопке ОК для активизации нового вычисляемого поля. Как видно на рис. 5.12, в результате вы получите вычисляемое поле внутри сводной таблицы.

Рис. 5.12. Вы успешно добавили вычисляемое поле в сводную таблицу. Теперь можно изменять настройки этого нового поля точно так же, как и параметры любого другого поля (например, имя поля, числовой формат или цвет)

Обратите внимание на то, что после создания нового поля Средняя выручка за час оно добавляется в список полей. Результат, полученный с помощью вычисляемого поля, не форматируется

При желании его можно отформатировать с помощью методики, описанной в разделе Создание базовой сводной таблицы

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

Означает ли это, что вы добавили столбец в источник данных? Нет. Вычисляемые поля похожи на элементы Промежуточная сумма (Subtotal) и Общая сумма (Grand Total) сводной таблицы, задаваемые по умолчанию, так как все они являются математическими функциями, которые выполняют перерасчет данных при изменении или обновлении сводной таблицы. Вычисляемые поля просто имитируют строго заданные поля в источнике данных. Можете перетаскивать их, изменять настройки полей, а также использовать вместе с другими вычисляемыми полями.

Взгляните внимательно на рис. 5.11

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

Если вы уже работали с формулами в Excel, то быстро усвоите концепции создания вычисляемых полей.

Плюсы сводных таблиц

Отчеты в формате сводных таблиц обладают серьезными преимуществами перед другими типами отчетов в Excel. Рассмотрим каждое из них:

1. Таблица составляется по практически любому объему данных.
2. Редактировать вид отчета можно через меню форматов – встроенная библиотека содержит множество цветовых тем таблиц.
3. Возможно объединение данных в более широкие группы, например несколько дат объединяются в кварталы.
4. По результатам отчета можно провести расчеты с помощью инструментов Excel, это не повлияет на источники данных.
5. Информация в сводной таблице может стать основой для составления графика или другого визуального отчета.

Полезные советы по подбору источника данных

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

Очень важно, чтобы над каждой колонкой было название, позволяющее определять, что за данные. Кроме этого, следуйте таким полезным рекомендациям:

Лучше всего в качестве источника данных использовать умную таблицу. Ее главное преимущество заключается в том, что каждая колонка имеет свое название, и если добавлять колонки или строки, диапазон данных автоматически расширяется на соответствующее их количество.
Не рекомендуется повторения групп в колонках. Так, желательно, чтобы даты располагались в одном столбце, а не разбивались по месяцам в отдельных колонках.
Правильно форматируйте поля

Важно, чтобы все числа были в числовом формате, а даты были действительно в формате даты. Иначе Excel будет трудно правильно сгруппировать и обработать данные

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

Таблица и именованный диапазон

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

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

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

= СУММ(E2:E8)+СРЗНАЧ(E2:E8)/5+10/СУММ(E2:E8)

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

Если же этому диапазону присвоить имя или превратить его в таблицу, достаточно просто указать его название один раз, а потом в случае чего просто поменять привязку к определенному диапазону также один раз. 

Добавление новых полей

Предположим, что необходимо подготовить отчет о продажах Товаров, но с разбивкой по Регионам продажи. Для этого добавим поле Регион продажи, поставив соответствующую галочку в Списке полей. Поле Регион продажи будет добавлено в область Названия строк Списка полей (к полю Товар). Поменяв в области Названия строк Списка полей порядок следования полей Товар и Регион продажи, получим следующий результат.

Выделив любое название Товара и нажав пункт меню Работа со сводными таблицами/ Параметры/ Активное поле/ Свернуть все поле , можно свернуть Сводную таблицу , чтобы отобразить только продажи по Регионам.

Где применять

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

Расчет KPI

Современные CRM-системы позволяют выгрузить все необходимые отчеты в готовом виде. Но что делать тем, кто специализированный софт не использует? Остается возможность как в Экселе сделать сводную таблицу, так и посчитать необходимые показатели в ручном режиме. Второй способ кажется проще, но он не всегда удобен. Если исходные данные представлены в виде списка подобного вида, использовать объединенные реестры вполне уместно, так как это значительно облегчает последующую работу.

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

Теперь эти данные используем для дальнейшего расчета. Сравним плановый показатель с фактическим и вычислим отклонение.

Тем менеджерам, которые превысили плановое значение (отклонение положительно), положена премия, рассчитываемая в процентах от выручки, превышающей плановое значение. Рассчитаем премию.

Отчет по персоналу

Практически все данные о персонале получаем из 1С. Но если такой софт в организации не используется или необходим отчет в другой форме, не остается ничего, кроме как делать сводные таблицы в Еxcel. Даже если массив данных составляется в ручном режиме, базы помогут представить их в более «красивом» виде. Имея сведения об образовании, стаже, окладе сотрудников в виде подобного списка, есть возможность, допустим, выяснить, сколько сотрудников каждого из отделов имеют образование определенного уровня.

С помощью подобной базы данных решают и задачи посложнее. Отобразим минимальный оклад сотрудников различных отделов по каждому уровню образования.

На основе таких отчетов удобно строить диаграммы для графического отображения информации.

Как удалить СТ

Самый простой случай – когда вы создали сводную таблицу, отослали результаты шефу, и она вам больше не нужна. Если вы в этом уверены, просто выбираем таблицу и жмём клавишу Delete. Просто и эффективно.

Но вдруг структура таблицы может вам понадобиться в будущем? В Excel имеется возможность удалить только результаты, или данные ячеек. Рассмотрим, как это делается.

Для удаления результатов вычислений выполняем следующие шаги:

• помечаем мышкой любую ячейку СТ;
• выбираем на верхней панели вкладку «Анализ»;
• из перечня доступных действий выбираем «Очистить»;
• кликаем на пункте «Очистить всё».

Но как поступить, если вы хотите сохранить результаты, но сами данные вам не нужны, то есть вы хотите освободить стол? Такая ситуация часто возникает, если руководству нужны только итоги. Алгоритм действий:

• снова выбираем любую ячейку, кликаем на вкладке «Анализ»;
• выбираем пункт меню «Действия», кликаем на «Выбрать», отмечаем мышкой всю сводную таблицу;
• щёлкаем ПКМ внутри выделенной области;
• из контекстного меню выбираем пункт «Скопировать»;
• переходим к вкладке «Главная», снова щёлкаем ПКМ и выбираем «Вставить»;
• выбираем вкладку «Вставить значение», в ней отмечаем параметр «Вставить как значение».

В итоге сводная таблица будет стёрта с сохранением результатов.

СОВЕТ. Ускорить процедуру можно посредством использования комбинации клавиш. Для выделения таблицы применяйте Ctrl + A, для копирования – Ctrl + C. Затем жмём ALT + E, ALT + S, ALT + V и завершаем процедуру нажатием Enter.

Для удаления сводных таблиц в Excel 2007/2010 нужно использовать другой алгоритм:

• выбираем СТ, предназначенную для удаления;
• жмём вкладку «Параметры»;
• в «Группе действий» выбираем пункт «Очистить», а затем – «Очистить всё».

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

В старых версиях программы для этого нужно выделить диаграмму, щёлкнуть на вкладке «Анализ», выбрать группу данных и нажать последовательно «Очистить» и «Очистить всё».

При этом, если диаграмма связана с самой сводной таблицей, после её удаления вы потеряете все настройки таблицы, её поля и форматирование.

Для версий старше Excel 2010 нужно выбрать диаграмму, на вкладке «Анализ» выбрать пункт «Действия» и нажать «Очистить» и «Очистить всё». Результат будет аналогичным.

Подготовка исходной таблицы

Начнем с требований к исходной таблице.

• каждый столбец должен иметь заголовок;
• в каждый столбец должны вводиться значения только в одном формате (например, столбец «Дата поставки» должен содержать все значения только в формате Дата >;>
• в таблице должны отсутствовать полностью незаполненные строки и столбцы;
• в ячейки должны вводиться «атомарные» значения, т.е. только те, которые нельзя разнести в разные столбцы. Например, нельзя в одну ячейку вводить адрес в формате: «Город, Название улицы, дом №». Нужно создать 3 одноименных столбца, иначе Сводная таблица будет работать неэффективно (в случае, если Вам нужна информация, например, в разрезе города);
• избегайте таблиц с «неправильной» структурой (см. рисунок ниже).

Вместо того, чтобы плодить повторяющиеся столбцы ( регион 1, регион 2, … ), в которых будут в изобилии незаполненные ячейки, переосмыслите структуру таблицы, как показано на рисунке выше (Все значения объемов продаж должны быть в одном столбце, а не размазаны по нескольким столбцам. Для того, чтобы это реализовать, возможно, потребуется вести более подробные записи (см. рисунок выше), а не указывать для каждого региона суммарные продажи).

Более детальные советы по построению таблиц изложены в одноименной статье Советы по построению таблиц .

Несколько облегчит процесс построения Сводной таблицы , тот факт, если исходная таблица будет преобразована в формат EXCEL 2007 ( Вставка/ Таблицы/ Таблица ). Для этого сначала приведите исходную таблицу в соответствие с вышеуказанными требованиями, затем выделите любую ячейку таблицы и вызовите окно меню Вставка/ Таблицы/ Таблица . Все поля окна будут автоматически заполнены, нажмите ОК.

Создание таблицы в формате EXCEL 2007 добавляет новые возможности:

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

В качестве исходной будем использовать таблицу в формате EXCEL 2007 содержащую информацию о продажах партий продуктов. В строках таблицы приведены данные о поставке партии продукта и его сбыте.

В таблице имеются столбцы:

• Товар – наименование партии товара, например, « Апельсины >»;
• Группа – группа товара, например, « Апельсины » входят в группу « Фрукты >»;
• Поставщик – компания-поставщик Товаров, Поставщик может поставлять несколько Групп Товаров;
• Дата поставки – Дата поставки Товара Поставщиком;
• Регион продажи – Регион, в котором была реализована партия Товара;
• Продажи – Стоимость, по которой удалось реализовать партию Товара;
• Сбыт – срок фактической реализации Товара в Регионе (в днях);
• Прибыль – отметка о том, была ли получена прибыль от реализованной партии Товара.

Через Диспетчер имен ( Формулы/ Определенные имена/ Диспетчер имен ) откорректируем Имя таблицы на « Исходная_таблица ».

Сводные таблицы в Excel. Примеры

На примерах ниже мы рассмотрим, как с помощью сводных таблиц ответить на три вопроса:

• Какой объем выручки у региона Север за 2017 год?;
• ТОП пять клиентов по выручке;
• Какое место по выручке занимает клиент Лудников ИП в регионе Восток?

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

Как только вы представили каким образом вы видите итоговую таблицу – начинайте её создание.

В окне “Поля сводной таблицы” размещены области и поля со значениями для размещения:

Поля создаются на основе значений исходного диапазона данных. Раздел «Области» – это место, где вы размещаете элементы таблицы.

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

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

Пример 1. Какой объем выручки у региона Север?

Для вычисления объема продаж региона Север, рекомендую разместить в таблице данные продаж по всем регионам. Для этого нам потребуется:

• создать сводную таблицу и поле “Регион” перенести в область “Строки”;
• поле “Выручка” разместить в области “Значения”
• задать финансовый числовой формат ячейкам со значениями.

Получим ответ: продажи региона Север составляют 1 233 006 966 ₽:

Пример 2. ТОП пять клиентов по продажам

Для того чтобы вычислить рейтинг ТОП пяти клиентов, нам нужно:

• переместить поле “Клиент” в область “Строки”;
• поле “Выручка” разместить в области “Значения”;
• задать финансовый числовой формат ячейкам со значениями.

У нас получится следующая таблица:

По-умолчанию, система Excel сортирует данные в таблице в алфавитном порядке. Для сортировки данных по объему продаж выполните следующие действия:

• кликните правой кнопкой на любой из строчек с данными выручки;
• перейдите в меню “Сортировка” => “Сортировка по убыванию”:

Как результат мы получим отсортированный список клиентов по объему выручки.

Пример 3. Какое место по выручке занимает клиент Лудников ИП в регионе Восток?

Для расчета места по объему выручки клиента Лудников ИП в регионе Восток рекомендую сформировать сводную таблицу, в которой будут отображены данные выручки по регионам и клиентам внутри этого региона.

• поместим поле “Регион” в область “Строки”;
• поместим поле “Клиент” в область “Строки” под поле “Регион”;
• зададим финансовый числовой формат ячейкам со значениями.

После перемещения элемента “Регион” и “Клиент” в области “Строки” друг под другом , система поймет каким образом вы хотите отобразить данные и предложит подходящий вариант.

поле “Выручка” разместим в область “Значения”.

В итоге мы получили таблицу, в которой отражены данные выручки клиентов в рамках каждого региона.

Для сортировки данных выполните следующие шаги:

• кликните правой кнопкой на любой из строчек с данными выручки;
• перейдите в меню “Сортировка” => “Сортировка по убыванию”:

В полученной таблице мы можем определить какое место занимает клиент Лудников ИП среди всех клиентов региона Восток.

Существует несколько вариантов для решения этой задачи. Вы можете перенести поле “Регион” в область “Фильтры” и в строчках разместить данные продаж клиентов, таким образом отразив данные по выручке только клиентов региона Восток.

Проверка правильности выставленных коммунальных счетов

С помощью сводных таблиц Excel легко проверить, насколько правильно обслуживающие организации начисляют квартплату. Другой положительный момент – экономия. Если мы будем ежемесячно контролировать, сколько расходуется света, газа, то сможем найти резерв для экономии средств на оплату квартиры.

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

Для примера мы сделали сводную табличку тарифов для Москвы:

Для учебных целей возьмем семью из 4 человек, которые проживают в квартире 60 кв. м. Чтобы контролировать коммунальные платежи, необходимо создать таблицы для расчета на каждый месяц.

Первый столбец = первому столбцу из сводной таблицы. Второй – формула для расчета вида:

= тариф * количество человек / показания счетчика / площадь

Для удобства рекомендуем сделать промежуточный столбец, в который будут заноситься показания по счетчикам (переменная составляющая).

Наши формулы ссылаются на лист, где расположена сводная таблица с тарифами.

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

Для чего в Excel нужны сводные таблицы

В оригинале этот термин звучит как Pivot Table, а перевод фразы несколько туманен: «таблица, которую можно менять, крутить, демонстрировать в разных проекциях». Что это означает? Интерпретация табличных данных – задача непростая. Обычно это делается с помощью формул, получая итоговые результаты в конце таблицы.

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

Большинство регулярных пользователей Excel даже не догадываются о наличии такого мощного инструмента. Для наглядности приведём небольшой и показательный пример использования сводных таблиц Excel «для чайников».

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

Как мы поступаем? Создаём макет новой таблицы, в шапке которой указываем весь ассортимент реализуемых товаров, а в строках – наименования регионов. Для этого копируем из исходной таблицы столбец с товарами, удалив дубликаты. Используя режим специальной вставки, транспонируем этот столбец в шапку сводной страницы. Аналогичным образом поступаем с регионами, но, поскольку они располагаются в сводной таблице по вертикали, просто копируем без транспонирования.

В каждую ячейку суммируем выручку, используя функцию СУММЕСЛИМН. Наконец, внизу добавляем итоговые результаты и отправляем отчёт руководителю. Руководитель просит регионы расположить вверху, а товары – по строкам. Вы делаете новый отчёт за 5 минут, и получаете новое задание: предоставить данные по прибыли, а не выручке, то есть с учётом затрат. Что ж, немного изменяем формулу, и опять результат готов за считанные минуты. Наконец, поступает просьба подготовить такие отчёты по каждому продавцу, чтобы оценить их эффективность. И это для сводной таблицы не проблема, если знать, где располагаются нужные исходные данные!

Между тем любой пользователь Excel знает, насколько сложно выполнять подобные манипуляции в рамках исходной таблицы.

Пример создания сводной таблицы Excel – алгоритм для чайников

Ознакомившись с базовыми теоретическими нюансами про сводные таблицы в Excel, давайте перейдем к применению их на деле. Для старта создания сводной таблицы в Excel 2016, 2010 или 2007 необходимо установить программное обеспечение. Как правило, если вы пользуетесь программами системы Microsoft Office, то Excel уже есть на вашем компьютере. Запустив его, перед вами откроется обширное поле, разделенное на большое количество ячеек. Более детально о том, как делать сводные таблицы в Excel, вам подскажет видеоурок выше.

С помощью следующего алгоритма мы детально рассмотрим пример, как построить сводную таблицу в Excel. На панели вверху окна переходим на вкладку «Вставка», где слева в углу выбираем «Сводная таблица». Далее на экране открывается диалоговое окошко, где требуется указать соответствующие параметры. На этом этапе создания сводных таблиц в Excel есть несколько важных моментов. Если перед тем, как начать формирование реестра, вы установили значок курсора на листе, то заполнение пустых строчек окна произойдёт автоматически. Иначе адрес диапазона данных нужно обозначить самостоятельно. Рассмотрим детальней самостоятельное заполнение пунктов диалогового окна.

Первую строку не оставляем пустой, иначе программа выдаст ошибку. Если есть источник, с которого планируете переноситься данные, то выберите его в пункте «Использовать внешний источник данных». Под внешним источником подразумевается другая книга Excel или набор моделей данных из СУБД.

Выберите место, где будет располагаться будущая рамка с ячейками. Это может быть новое окно или же этот лист, рекомендуем использовать другой лист. Закрепив все настройки, получаем готовую основу. По левую сторону располагается область, где размещена основа будущей рамки. В правой части есть окно с настройками, которое помогает управлять реестром. Теперь необходимо разобраться, как строится вся эта конструкция. В окне настроек «Поля свободной таблицы», вы обозначаете данные, которые будут присутствовать. Вся структура строится таким образом, чтобы текстовые данные играли роль объединяющих элементов, а числовые показывали консолидированные значения. Например, объединим все поступления по отделам и узнаем на какую сумму получил товары каждый. Для этого поставим галочку у двух заголовков: отдел и стоимость товара в партии

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

Если попробовать щелкнуть по любому столбцу с числами, они будут появляться в этой области. А в самой таблице добавится новый столбец. При этом происходит суммирование. Сейчас мы можем узнать из нашего отчета, сколько товаров поступило в каждый отдел и их общую стоимость.

Вы можете свободно корректировать ширину столбиков для оптимального расположения данных. Просто раздвигайте ширину столбцов или высоту строк так, как вы привыкли в Excel. Если вам не нравится такая ориентация, вы можете перетащить мышкой названия строк в область столбцов — просто наведите мышь, нажмите левую кнопку и тяните. Что касается подсчета результатов, то сумма – далеко не единственная функция. Чтобы посмотреть, какие Excel предлагает еще, нажмите на название в области значений и выберите последнюю команду. В параметрах полей значений вы найдете множество вариантов для анализа. Для каждого значения можно выбрать свою функцию. Например, добавим поле «Цена» и найдем максимальную цену товара в каждом отделе. Фактически, узнаем сколько стоит самый дорогой. Теперь мы видим, что в отдел «Аксессуары» поступило товаров на сумму 267660 рублей, при этом самый дорогостоящий имеет цену 2700 рублей. Область «Фильтры» позволяет установить критерий отбора записей. Добавим поле «Дата поступление», просто поставив около него галочку. Сейчас сводная таблица в Excel выглядит неудобно, если нам необходимо провести анализ по дате. Поэтому переместим дату из строк в фильтры — просто перетяните, как было указано выше. Итогом этих действий стало появление еще одного поля сверху. Чтобы выбрать дату, нажмем на стрелочку около слова «Все». Теперь нам доступен выбор конкретного дня, чтобы открыть список, щелкайте по треугольнику в правом углу. Также можно выбрать и значения для отдела. Снимите галочки с тех, которые вас не интересуют, и вы получите только нужную информацию.

Во время работы вы можете столкнуться с подобным сообщением «недопустимое имя сводной таблицы Excel». Это означает, что первая строка диапазона, откуда пытаются извлечь информацию, осталась с незаполненными ячейками. Чтобы решить эту проблему, вы должны заполнить пустоты колонки.

Изменение функции итогов

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

Изменение порядка сортировки

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

Теперь предположим, что Товар Баранки – наиболее важный товар, поэтому его нужно выводить в первой строке. Для этого выделите ячейку со значением Баранки и установите курсор на границу ячейки (курсор должен принять вид креста со стрелками).

Затем, нажав левую клавишу мыши, перетащите ячейку на самую верхнюю позицию в списке прямо под заголовок столбца.

После того как будет отпущена клавиша мыши, значение Баранки будет перемещено на самую верхнюю позицию в списке.