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

Обновлено: 07.07.2024

Несмотря на то, что первая возможность создания сводных таблица появилась еще в Excel 5.0(аж в 1993 году), даже сейчас лишь немногие из пользователей Excel используют сводные таблицы для решения задач. Кто-то считает, что сводные таблицы это нечто слишком сложное, кто-то про них даже не слышал. В данном разделе (Сводные таблицы) я постараюсь убедить читателей в том, что это не так. На самом деле ничего сложного в сводных таблицах нет. А вот польза при анализе информации просто неоценима.

Для чего же нужны сводные? В Excel работу можно разделить на две категории: анализ(вычисление) и форматирование данных. Под вычислениями и анализом я понимаю получение неких показателей на основании имеющихся данных. А форматирование - не закраска ячеек цветом, а вид представления таблиц данных. Все это можно сделать и формулами. Предположим, есть исходные ежедневные данные по продажам всех филиалов за полгода. Из этих данных необходимо построить отчет в разрезе каждого филиала и для филиала в разрезе месяца. Плюс сводные отчеты по каждому филиалу за все полгода. А теперь представим как это будет выглядеть формулами:
-сначала надо получить список месяцев;
-затем список уникальных наименований филиалов;
-далее создать листы с рыбой таблиц, в которые надо будет собрать данные из исходной таблицы при помощи СУММЕСЛИ, СУММПРОИЗВ и им подобным.
При должном опыте можно уложиться минут в 15-20. С использованием сводных это можно сделать за две минуты.

Сводная таблица (Pivot Table) – инструмент Excel, используемый для создания уникального представления данных и последующего анализа. Сводная таблица может быть построена на основе правильно сформированной исходной таблицы данных:

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

Данные сводных таблиц можно группировать, сортировать, фильтровать и менять местами с целью получения различных аналитических отчетов. Что немаловажно - сводные таблицы никоим образом не меняет исходные данные, на основе которых построена сводная. Можно как угодно менять и данные местами в сводной и исключать из отчета - исходные данные останутся такими, какими и были.
Но если изменить исходные данные, то изменение данных не будет автоматически отражено внутри сводных таблиц - для этого надо будет принудительно обновить отчет сводной таблицы:

Выделить любую ячейку сводной таблицы→Правая кнопка мыши→Обновить (Refresh) или вкладка Данные (Data) →Обновить все (Refresh all) →Обновить (Refresh) .

СОЗДАНИЕ СВОДНОЙ ТАБЛИЦЫ

Выделить любую ячейку исходной таблицы
Вкладка Вставка (Insert) →группа Таблица (Table) →Сводная таблица (PivotTable)
В диалоговом окне Создание сводной таблицы (Create PivotTable) проверить правильность выделения диапазона данных (или установить новый источник данных), определить место размещения Сводной таблицы:
- На новый лист (New Worksheet)
- На существующий лист (Existing Worksheet)
нажать OK

СВОДНАЯ ТАБЛИЦА СОСТОИТ ИЗ ЧЕТЫРЕХ ОБЛАСТЕЙ:
Область данных – основная область сводной таблицы, в которой производятся расчеты. Содержит основные итоговые данные по числовым полям. В область данных можно поместить одно и тоже поле, но с разными вычислениями (например одно Сумма по полю, другое Количество по полю).
Основные вычислительные функции области данных:

 Сумма (Sum)
 Количество (Count)
 Среднее (Average)
 Максимум (Max)
 Минимум (Min)
 Произведение (Product)

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

При использовании сводной таблицы следует учитывать, что она не обновляет свои значения автоматически при изменении значений в исходных данных. Для выполнения обновления необходимо выделить любую ячейку сводной таблицы→Правая кнопка мыши→Обновить (Refresh) или вкладка Данные (Data) →Обновить (Refresh) . Это связано с тем, что сводная не содержит прямой ссылки на исходные данные, а хранит их в кэше, что позволяет сводной достаточно быстро обрабатывать данные.

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

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

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

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

Рис. 1.2. Устаревшая система возвращает данные за день, а не за месяц

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

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

Рис. 1.3. Ваша цель состоит в получении итоговых сведений за месяц и транспонировании их в горизонтальный вид

Получение необходимого фрагмента данных с помощью сводной таблицы требует всего лишь 9 щелчков мышью:

создание отчета сводной таблицы — пять щелчков мышью;
группирование дат в месяцы — три щелчка;
изменение ориентации с вертикальной на горизонтальную — один щелчок.

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

Рис. 1.4. Добавив сведения о кредитах в отчет, можно подсчитать чистую прибыль

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

На позапрошлой неделе снимал получасовое видео о сводных таблицах в Excel. Тогда не решился выкладывать его на Пикабу, так как подумал, что плохо зайдёт (переводить все 30 минут видео в формат поста точно не охота, а закинуть лишь видео не решился, так как думал, что такой пост не будет особо хорошо принят). Тем не менее, видео-продолжение о построении дашбордов зашло на ура, так что сейчас также делюсь видеоуроком о всех особенностях работы со сводными таблицами с нуля:

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

MS, Libreoffice & Google docs

466 постов 12.7K подписчиков

Правила сообщества

2. Публиковать посты соответствующие тематике сообщества

3. Проявлять уважение к пользователям

4. Не допускается публикация постов с вопросами, ответы на которые легко найти с помощью любого поискового сайта.

По интересующим вопросам можно обратиться к автору поста схожей тематики, либо к пользователям в комментариях

Важно - сообщество призвано помочь, а не постебаться над постами авторов! Помните, не все обладают 100 процентными знаниями и навыками работы с Office. Хотя вы и можете написать, что вы знали об описываемом приёме раньше, пост неинтересный и т.п. и т.д., просьба воздержаться от подобных комментариев, вместо этого предложите способ лучше, либо дополните его своей полезной информацией и вам будут благодарны пользователи.

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

Есть три стадии принятия эксель:

1. Тупой эксель, не понимает что мне нужно

2. Удобный эксель, легко растягивать столбики

3. Тупой я, на экселе можно запустить майнкрафт

Позавчера делал сводную таблицу из массива 45000 строк и 25 столбцов. Да, я понимаю, что это задача СУБД, но бухгалтерам хотелось в Экселе. 10 минут на любое изменение - это жесть, конечно :))
power pivot только не пробовал, мб быстрее было бы

Ну наконец-то! А то я вчера смотрела видео про дашборд и там было упоминание, что надо посмотреть предыдущий пост про сводные. Не поленилась пролистать в аккаунте у Вас несколько страниц с постами и не нашла. Спасибо, в общем!

Спасибо за Ваши ролики, давно подписан, пару интересных способов взял в работу!

Как в Access сделать макрос на сохранение текущего файла?

Здравствуйте, извините за оффтоп. Подскажите, в эксель можно создать правило автозамены текста с условием: если в тексте есть частичные совпадения -> выполнить замену?

поиск по слову детский, при обнаружении - автозамена на Сад детский

Детский сад (Ромашка) -> Сад детский

Детский сад (Звездочка) -> Сад детский

Детский сад (Пионер) -> Сад детский

А можете подсказать, почему при определённой компановке значения встают в шахматном порядке? Как это убрать?

А чего не в PBI? Программа халявная, а выходит симпатичнее

Извините, мы ещё лаптем кашу едим, а где можно скачать файл из видео для упражнения?

Когда ты не умеешь работать в Excel

Фото сделано в маршрутке

Excell - как закрыть для пользователей доступ к другим листам

Не единожды видел, как помогали по таким вопросам. Надеюсь и мне кто-то сможет :)

Есть простая гугл-ексель таблица. В ней 5 вкладок. Необходимо, что бы другие люди имели возможность смотреть-редактировать только свою вкладку. А я, соответственно, имел доступ ко всем.

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

Визуализация гистограммы фигурами и рисунками в Excel

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

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

00:00 - О вариантах добавления фигур и рисунков на графики

01:06 - Построение диаграммы с добавлением фигур на график

06:40 - Построение диаграммы с добавлением рисунков на график

Как перевести данные в тысячи/миллионы/… в Excel?

Сегодня обсудим способы перевода числовых данных в тысячи, миллионы, миллиарды и другие порядки в Excel для сокращения записей (например, как число вида 123 456 перевести в 123,4 тыс. или 987 654 321 в 988 млн.).

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

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

Построение радиальной гистограммы в Excel | Радиальная диаграмма

Чем же особенна радиальная гистограмма?

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

00:00 - Начало

00:28 - Особенности радиальной гистограммы (отличие от линейной)

00:44 - Недостатки графика

01:12 - Построение радиальной диаграммы

01:50 - Меняем строки и столбцы местами для корректного отображения рядов

02:16 - Формируем дополнительный ряд для диаграммы

03:31 - Особенности настройки параметров (процент заполнения кольца)

03:47 - Убираем заливку на ненужных столбцах

04:12 - Расширяем столбцы на графике (оптимизируем полезное пространство)

04:43 - Добавляем подписи данных на ряды

06:21 - Меняем заливку столбцов на подходящие цвета

06:51 - Настраиваем угол поворота диаграммы

07:36 - Примеры радиальных гистограмм

Умная таблица в Excel | Как сделать умную таблицу?

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

00:00 - Начало

00:39 - Создание умной таблицы в Excel

01:45 - Преимущества при работе с умными таблицами

02:22 - Автоматическое добавление фильтра

02:43 - Имя умной таблицы

03:28 - Автоматическое изменение размера

03:50 - Копирование формулы на весь столбец

04:17 - Копирование формулы для новых строк

04:57 - Закрепление заголовков при прокрутке

05:56 - Имена для отдельных элементов таблицы и работа с ними

06:33 - Дополнительные настройки (вкладка Конструктор)

06:59 - Как убрать умную таблицу и сделать из нее обычную

08:00 - Недостатки умной таблицы

Количество и сумма ячеек по цвету в Excel

Сегодня поговорим про простой способ как посчитать количество, и как суммировать ячейки по цвету в Excel.

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

Тайм-коды для удобства навигации

00:44 - Суммирование ячеек по цвету

02:48 - Подсчет количества ячеек по цвету

03:34 - Изменение функций для расчетов по цвету текста

03:55 - Особенности применения функций

Лайфхаки с Excel

1. Как восстановить файл, который вы не сохранили:

Как же часто случается, что по забывчивости на вопрос: "Сохранить изменения?" - нажимаешь "Нет"! И вот вы уже думаете, что последняя пара часов ушла вникуда. Однако есть отличный шанс восстановить утраченное. В Excel 2010 нажмите на Файл, выберите Последние. В правом нижнем углу экрана появится опция Восстановить несохраненные книги. В версии 2013 года путь такой: Файл — Сведения — Управление версиями.

Чтобы выделить все ячейки, не нужно долго прокручивать мышкой. Достаточно будет нажать Ctrl + A или специальную кнопку в углу листа.

Бывает, что в самом начале работы с таблицей вы еще не можете представить, как лучше подать информацию - в ячейках или в столбцах. Функция транспонирования облегчит эту задачу. Выделите нужное количество ячеек и скопируйте их, зажав Ctrl + C. Теперь нужно выбрать нужный столбец и вставить данные с опцией Транспонировать.

Фишки Excel

1. Как быстро объединить текст из нескольких ячеек:

Нажмите на ячейку, в которой вы хотите увидеть результат, и вбейте туда знак " story-block story-block_type_image">

Вы легко можете поменять регистр всей текстовой информации, выбрав необходимую функцию. ПРОПИСН - сделать все буквы прописными, ПРОПНАЧ - сделать прописной первую букву в каждом слове. Функция СТРОЧН, соответственно, делает все буквы строчными.

Как перейти к нужному листу

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

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

Диаграмма в Эксель

Пять первых шагов к освоению программы Excel.

Базовый видеокурс по работе в программе Эксель.

- как на основе таблицы создать диаграмму

- как вывести книгу Эксель на печать

Создание сводной таблицы в Excel. Реальная задача

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

10 Трюков в Excel

В этом видео, я рассмотрел 10 трюков в Excel:

00:00 - Спонсорство

00:39 - Быстрый подсчёт суммы

03:28 - Функция ТРАНСП()

05:21 - Фильтрация СРЕЗАМИ

06:58 - Анализ "Что если"

10:13 Функция ПРЕОБР()

11:01 - Курсы валют

12:37 - Скрытый формат ячейки

13:52 - Удалить пустые ячейки

14:31 - Социальный граф

15:50 - Расширенный фильтр

Формулы Excel – все особенности, тонкости и трюки в одном видео!

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

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

Исходная таблица.

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

Как построить сводную таблицу в Excel.

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

Появляется диалоговое окно Создание сводной таблицы.

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

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

Он содержит в себе следующие элементы:

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

После того, как выбраны (поставлены галочки) поля для добавления в отчет, они отобразятся в блоках (полях) Фильтры, Столбцы, Строки и Значения. Далее, их необходимо перетащить в соответствующие поля исходя их того, какую Сводную таблицу необходимо получить в результате.

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

Настройки сводной таблицы в Excel.

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

После того, как галочки поставлены, элементы появились в блоках (областях) Фильтры, Столбцы, Строки и Значения.

И уже будет сформирована Сводная таблица.

Как это сделать.

Перетаскиваем элемент Магазин в поле Фильтры.

Наша таблица готова.

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

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

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

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