Сводная таблица эксель для чайников: примеры, как сделать – WindowsTips.Ru. Новости и советы

Работа со сводными таблицами в Excel на примерах

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

Исходный материал – таблица с несколькими десятками и сотнями строк, несколько таблиц в одной книге, несколько файлов. Напомним порядок создания: «Вставка» – «Таблицы» – «Сводная таблица».

А в данной статье мы рассмотрим, как работать со сводными таблицами в Excel.

Как сделать сводную таблицу из нескольких файлов

Первый этап – выгрузить информацию в программу Excel и привести ее в соответствие с таблицами Excel. Если наши данные находятся в Worde, мы переносим их в Excel и делаем таблицу по всем правилам Excel (даем заголовки столбцам, убираем пустые строки и т.п.).

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

Мы просто создаем сводный отчет на основе данных в нескольких диапазонах консолидации.

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

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

Мастер сводных таблиц при таких исходных параметрах выдаст ошибку. Так как нарушено одно из главных условий консолидации – одинаковые названия столбцов.

Но два заголовка в этих таблицах идентичны. Поэтому мы можем объединить данные, а потом создать сводный отчет.

1. В ячейке-мишени (там, куда будет переноситься таблица) ставим курсор. Пишем = — переходим на лист с переносимыми данными – выделяем первую ячейку столбца, который копируем. Ввод. «Размножаем» формулу, протягивая вниз за правый нижний угол ячейки.
2. По такому же принципу переносим другие данные. В результате из двух таблиц получаем одну общую.
3. Теперь создадим сводный отчет. Вставка – сводная таблица – указываем диапазон и место – ОК.

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

Покажем, к примеру, количество проданного товара.

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



Детализация информации в сводных таблицах

Из отчета (см.выше) мы видим, что продано ВСЕГО 30 видеокарт. Чтобы узнать, какие данные были использованы для получения этого значения, щелкаем два раза мышкой по цифре «30». Получаем детальный отчет:

Как обновить данные в сводной таблице Excel?

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

Обновление данных:

Курсор должен стоять в любой ячейке сводного отчета.

Либо:

Правая кнопка мыши – обновить.

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

1. Курсор стоит в любом месте отчета. Работа со сводными таблицами – Параметры – Сводная таблица.
2. Параметры.
3. В открывшемся диалоге – Данные – Обновить при открытии файла – ОК.

Изменение структуры отчета

Добавим в сводную таблицу новые поля:

1. На листе с исходными данными вставляем столбец «Продажи». Здесь мы отразим, какую выручку получит магазин от реализации товара. Воспользуемся формулой – цена за 1 * количество проданных единиц.
2. Переходим на лист с отчетом. Работа со сводными таблицами – параметры – изменить источник данных. Расширяем диапазон информации, которая должна войти в сводную таблицу.

Если бы мы добавили столбцы внутри исходной таблицы, достаточно было обновить сводную таблицу.

После изменения диапазона в сводке появилось поле «Продажи».

Как добавить в сводную таблицу вычисляемое поле?

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

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

Инструкция по добавлению пользовательского поля:

1. Определяемся, какие функции будет выполнять виртуальный столбец. На какие данные сводной таблицы вычисляемое поле должно ссылаться. Допустим, нам нужны остатки по группам товаров.
2. Работа со сводными таблицами – Параметры – Формулы – Вычисляемое поле.
3. В открывшемся меню вводим название поля. Ставим курсор в строку «Формула». Инструмент «Вычисляемое поле» не реагирует на диапазоны. Поэтому выделять ячейки в сводной таблице не имеет смысла. Из предполагаемого списка выбираем категории, которые нужны в расчете. Выбрали – «Добавить поле». Дописываем формулу нужными арифметическими действиями.
4. Жмем ОК. Появились Остатки.

Группировка данных в сводном отчете

Для примера посчитаем расходы на товар в разные годы. Сколько было затрачено средств в 2012, 2013, 2014 и 2015. Группировка по дате в сводной таблице Excel выполняется следующим образом. Для примера сделаем простую сводную по дате поставки и сумме.

Щелкаем правой кнопкой мыши по любой дате. Выбираем команду «Группировать».

В открывшемся диалоге задаем параметры группировки. Начальная и конечная дата диапазона выводятся автоматически. Выбираем шаг – «Годы».

Получаем суммы заказов по годам.

Скачать пример работы

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

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

pro

Перейти к содержанию

Вы здесь:

Что такое сводная таблица

 (Pivot Table – англ.)? Pivot Table дословно переводится как «таблица, которую можно крутить, показывать в разных разворотах». Это инструмент, который позволяет представлять данные в виде, удобном для анализа. Вид сводной таблицы можно быстро менять с помощью одной только мышки, помещая данные в строки или столбцы, выбирать уровни группировки, фильтровать и «перетаскивать» мышкой столбцы с одного места на другое.

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

• Отчет о динамике и структуре продаж
• Анализ исполнения бюджета (БДР)

 

Исходные данные для сводной таблицы

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

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

Как быстро выделить таблицу:

• Выбрать ее любую ячейку и нажать Crtl + * или Ctrl + A, или
• Выбрать самую первую ячейку в таблице, зажать кнопки Ctrl и Shift, а затем нажать на кнопки вправо, затем вниз (→↓).

Если выделить больше одной ячейки, но не всю таблицу, в качестве источника данных будет захвачена только выделенная область.

Создайте сводную таблицу: перейдите на вкладку Вставка и выберите «Сводная таблица».

 

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

 

Когда сводная таблица добавлена, на листе появляется

область сводной таблицы. Если эта область не активна (вы не выделили ее мышкой), на ней будет подсказка: «Чтобы начать работу с отчетом сводной таблицей, щелкните в этой области». Щелкаем по ней мышкой и происходят две вещи:

1. Справа появится список полей сводной таблицы.
2. В меню — две дополнительные вкладки, связанные с управлением сводной таблицей (Анализ и Конструктор).

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

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

После заполнения областей сводной таблицы её вид изменится. В нашем примере в строках появились ФИО менеджеров и товары, а напротив них – суммы продаж. Далее данные можно детализировать и создать визуализации.

Поля можно «перетаскивать» мышкой из одной области в другую:

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

Когда исходные данные поменяются, обновите сводную таблицу. Для этого перейдите в меню Данные и нажмите «Обновить». Такая же кнопка есть и на вкладке Анализ. Или еще можно щелкнуть правой кнопкой мышки по сводной таблице и выбрать «Обновить» в появившемся меню. После нажатия этой кнопки сводная таблица «пересчитается».

Если изменился сам источник данных (добавлены новые строки или столбцы), выберите любую ячейку сводной таблицы и перейдите в меню Анализ → Источник данных.

В появившемся окне выберите источник данных.

Один из оптимальных способов задать источник данных – это указать в качестве него «умную» smart-таблицу Excel. О преимуществах этого способа и вообще о плюсах использования «умных» таблиц читайте в следующей статье.

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

То, что написано о сводных таблицах в статье — это еще не все. Для «продвинутой» аналитики в Excel есть надстройки и инструменты, которые расширяют функционал сводных таблиц до действительно серьезного уровня и переносят его в область Business Intelligence. Сейчас мы говорим о надстройках Power Query и Power Pivot, которые также задействованы в продукте Microsoft Power BI.

Теги: Excel

Автор: Станислав Салостей

Вверх

Как создать сводную таблицу

Если вы понимаете базовую структуру сводной таблицы, пришло время попробовать свои силы в создании своей первой сводной таблицы. Вы можете найти образец файла для работы на сайте Dummies.com в книгах Chapter 3 Samples.xlsx и Chapter 3 Slicers.xlsx.

Щелкните любую ячейку в источнике данных ; — это таблица, которую вы используете для заполнения сводной таблицы. Если вы следуете инструкциям, источником данных будет таблица на вкладке Sample Data.

Выберите вкладку «Вставка» на ленте.

Здесь найдите значок сводной таблицы. Выберите «Сводная таблица» в раскрывающемся списке под значком. Этот шаг открывает диалоговое окно «Создать сводную таблицу».

Excel пытается заполнить диапазон ваших данных за вас. Всегда следите за тем, чтобы выбран правильный диапазон.

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

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

Нажмите OK.

На данный момент у вас есть пустой отчет сводной таблицы на новом листе. Рядом с пустой сводной таблицей вы видите диалоговое окно «Поля сводной таблицы».

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

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

Теперь, прежде чем вы сойдете с ума и начнете сбрасывать поля в различные зоны сброса, вы должны задать себе два вопроса: «Что я измеряю?» и «Как я хочу это видеть?» Ответы на эти вопросы дадут вам некоторое руководство при определении того, какие поля и где должны располагаться.

Для вашего первого отчета сводной таблицы измерьте продажи в долларах по рынку. Это автоматически говорит вам, что вам нужно работать с полями «Сумма продаж» и «Рынок».

Как ты хочешь это видеть? Вы хотите, чтобы рынки были перечислены в левой части отчета, а сумма продаж рассчитывалась рядом с каждым рынком. Помня о четырех областях сводной таблицы, вам нужно добавить поле «Рынок» в зону сброса «Строки» и добавить поле «Сумма продаж» в зону сброса «Значения».

Установите флажок Market в списке.

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

Установите флажок Сумма продаж в списке.

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

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

Еще одно: при добавлении полей в зоны перетаскивания вам может быть трудно увидеть все поля в каждой зоне перетаскивания. Вы можете развернуть диалоговое окно «Поля сводной таблицы», щелкнув и перетащив границы диалогового окна.

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

Об этой статье

Эта статья взята из книги:

• Excel Power Pivot и Power Query для чайников,

Об авторе книги:

Майкл Александр — старший консультант Slalom Consulting с более чем 15 многолетний опыт управления данными и отчетности. Он является автором более дюжины книг по бизнес-анализу с использованием Microsoft Excel и был назван Microsoft Excel MVP за вклад в сообщество Excel.

Эту статью можно найти в категории:

• Excel ,

Сводная таблица Excel для чайников Шаг за шагом

Сводная таблица Excel!

Самая важная функция, когда-либо добавленная в набор инструментов Microsoft Excel!

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

Почему?

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

Дайте ему миллионы строк данных и попросите отчет в течение 10 минут. Он придет к вам через 5 минут и покажет отчет.

Часовой курс по сводным таблицам Excel 2016 ( Скидка 100% )

Сводные таблицы Excel 2016: создание основных сводных таблиц в Excel

История сводной таблицы

Давайте начнем

Почему важно изучать сводную таблицу?

Жизнь без сводной таблицы

Жизнь после сводной таблицы

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

Знакомство со сводными таблицами

Анализ данных с помощью сводных таблиц

Скачать PDF

Подведение итогов

Статьи по Теме

История сводной таблицы

Функция

Pivot Table как программа была впервые представлена ​​​​компаниям Lotus в 1986 году. В 1987 году Стив Джобс увидел программу и сразу же приказал разработать ее для своей тогда еще новой компьютерной платформы NeXT. Наконец, эта программа была добавлена ​​на платформу NeXT в 19 году.91. Версия для Windows была представлена ​​в 1993 году.

После этого сводная таблица стала самым мощным оружием воина данных!

Начнем

Итак, вы новичок в Excel и впервые слышите о функции сводной таблицы?

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

Изучение основ работы со сводными таблицами — это действительно легко и весело! Начните изучать его сегодня, я могу гарантировать, что, если вы похожи на меня, вы закончите это сегодня!

Итак, начните изучать сводные таблицы Excel уже сегодня!

Почему важно изучать сводную таблицу?

Знаете ли вы о Cortana от Microsoft? Cortana, механизм обработки данных Bing, сделал безупречный результат, правильно предсказав каждый матч на чемпионате мира по футболу 2014 года. Это потрясающе! Cortana анализировала, обрабатывала и обобщала каждый бит данных, которые можно было собрать об игроках, местах проведения игр, тренерах, окружающей среде и многом другом. И результат? 100% правильный прогноз в каждом матче. Если бы Cortana использовала свои возможности в ставках на спорт, она могла бы заработать миллиарды долларов всего за один месяц! Ого!

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

Я часто пользуюсь электронными таблицами Google. Потому что для работы очень удобно, а сейчас глаза болят от столов и для отдыха играю только тут https://casinowis.com/uptown-pokies-casino.

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

Жизнь без сводной таблицы

Посмотрите это видео и ощутите те дни, когда сводной таблицы не было!

Срок службы после сводной таблицы

Вот наша жизнь, жизнь с функцией Excel Pivot Table.

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

Этот пост представляет собой полное руководство, которое поможет вам стать мастером сводных таблиц Excel! Затяните ремень безопасности, чтобы стать специалистом по данным!

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

Знакомство со сводными таблицами

Это руководство включает 10 руководств.

1. Что такое сводная таблица в Excel – сделать сводную таблицу вручную!?
2. 8 Примеры сводных таблиц Excel – Как сделать сводную таблицу!
3. Данные для сводной таблицы
4. Автоматическое создание сводной таблицы
5. Создание сводной таблицы Excel вручную
6. Терминология сводной таблицы Excel
7. Вычисления в сводной таблице Excel [Сумма, количество, среднее, максимальное и т. д.]
8. Форматирование сводных таблиц Excel 7 способами!
9. Как изменить сводную таблицу Excel
10. Копирование сводной таблицы Excel!

Анализ данных с помощью сводных таблиц

Это руководство включает 13 учебных пособий. Вот они:

1. Создание сводной таблицы из нечисловых данных
2. Автоматическая группировка сводной таблицы Excel по дате, времени, месяцу и диапазону!
3. Создание таблицы частотного распределения в Excel 7 способами [Способ 2 — использование сводной таблицы Excel]
4. Несколько групп из одного источника данных
5. Как создать среднее вычисляемое поле в сводной таблице Excel
6. Как вставить вычисляемый элемент в сводную таблицу Excel!
7. Как фильтровать сводные таблицы Excel с помощью слайсеров!
8. Как создать временную шкалу в Excel для фильтрации сводных таблиц!
9. Как сослаться на ячейку в сводной таблице
10. Создание сводных диаграмм в Excel
11. Пример сводной таблицы в Excel
12. Как создать отчет сводной таблицы в Excel
13. Как создать модель данных сводной таблицы в Excel 2013

Скачать PDF

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