Как построить сводную таблицу в эксель. Как работать с данными

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

Требования к исходным данным.

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

Давайте разберем в чем, собственно говоря, "правильность" этой таблицы? Правильность заключается в том, что:

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

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

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

Вот еще один пример "Неправильного массива":

Тут целый букет "неправильностей":

• Во-первых, столбцы "Поставщик" и "Категория" имеют пустые ячейки, соответственно мы не можем воспользоваться фильтром;
• Во-вторых, практически в каждом столбце есть строки "Итого...", они совершенно не нужны для построения Сводной таблицы, более того они будут только мешать;
• В-третьих, столбец "Общий итог" так же не нужен;
• В-четвертых, однотипные данные, а именно "Деньги", стоят аж в трех столбцах: "янв", "фев" и "мар", что значительно затруднит построение Сводной таблицы, а значит, нам придется с ними, что-то делать...

Но это все для отдельного разговора, если вы хотите научиться быстро, преобразовывать такие "кривые таблицы" в "правильные массивы" читайте статьи: "Как быстро построить сводную таблицу из отчета 1C или SAP? " и "Как быстро преобразовать таблицу в массив для сводной таблицы? "

Собственно, построение Сводной таблицы:

Берем "правильный массив", курсором встаем в любую ячейку массива, в главном меню выбираем вкладку "Вставка", в левом углу, в разделе "Таблицы" нажимаем кнопку "Сводная таблица":

В открывшемся диалоговом окне "Создание сводной таблицы" нажимаем "ОК":

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

Начинаем настройку Сводной таблицы. Какой разрез мы хотим получить? Товарные категории в разрезе Менеджеров - пожалуйста. Наводим мышку на поле "Категория", нажимаем на нем левой кнопкой мышки и тащим его в поле "СТРОКИ". Поле "Менеджер" тащим в "КОЛОННЫ", "Сумму" тащим в "ЗНАЧЕНИЯ":

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

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

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

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

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

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

— Можно ли отчет сделать не по выручке, а по прибыли?

— Можно ли товары показать по строкам, а регионы по столбцам?

— Можно ли такие таблицы делать для каждого менеджера в отдельности?

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

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

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

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

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

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

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

Сводная таблица состоит из 4-х областей, которые находятся в нижней части панели: значения, строки, столбцы, фильтры. Рассмотрим подробней их назначение.

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

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

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

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

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

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

Посмотрим, как это работает в действии. Создадим пока такую же таблицу, как уже была создана с помощью функции СУММЕСЛИМН. Для этого перетащим в область Значения поле «Выручка», в область Строки перетащим поле «Область» (регион продаж), в Столбцы – «Товар».

В результате мы получаем настоящую сводную таблицу.

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

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

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

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

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

Исходные данные

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

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

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

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

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

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

Если внести изменения в источник (например, добавить новые строки), сводная таблица не изменится, пока вы ее не обновите через правую кнопку мыши

или
через команду во вкладке Данные – Обновить все .

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

Используя сводные таблицы даже на таком базовом уровне, вы сможете в разы увеличить скорость и качество обработки больших массивов данных. Если что-то осталось непонятным, напишите в комментариях.

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

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

Немного истории

На заре развития программ для создания электронных таблиц балом правил Lotus 1-2-3. Его превосходство было настолько полным, что усилия Microsoft, направленные на разработку собственного программного обеспечения (Excel), как альтернативы Lotus, казались пустой тратой времени. А теперь перенесёмся в 2010 год! Excel доминирует среди электронных таблиц более, чем Lotus кода-либо за всю свою историю, а число людей, которые до сих пор используют Lotus, стремится к нулю. Как это могло произойти? Что послужило причиной для такого драматического разворота событий?

Аналитики выделяют два основных фактора:

• Во-первых, компания Lotus решила, что эта новомодная GUI-платформа с названием Windows – это всего лишь мимолётное увлечение, которое долго не протянет. Они отказались создавать версию Lotus 1-2-3 для Windows (впрочем, только несколько лет), предсказывая, что DOS-версия их программного обеспечения – это всё, что когда-либо будет нужно потребителям. Microsoft, естественно, разработала Excel специально под Windows.
• Во-вторых, Microsoft разработала в Excel такой инструмент, как сводные таблицы, которого не было в Lotus 1-2-3. Сводные таблицы, эксклюзивная для Excel вещь, оказалась так ошеломительно полезна, что люди были склонны осваивать новый программный пакет Excel, а не продолжать работать в Lotus 1-2-3, в котором их не было.

Сводные таблицы вместе с недооценкой успеха Windows в целом, сыграли похоронный марш для Lotus 1-2-3 и положили начало успеху Microsoft Excel.

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

Итак, как же лучше охарактеризовать, что собой представляют сводные таблицы?

Говоря простым языком, сводные таблицы – это итоги каких-то данных, созданные для облегчения анализа этих данных. В отличие от созданных вручную итогов, сводные таблицы Excel интерактивны. После создания, Вы можете легко изменять их, если они не дали той картины, которую Вы надеялись получить. Всего лишь парой щелчков мышью итоги могут быть перевёрнуты таким образом, что заголовки столбцов становятся заголовками строк и наоборот. Со сводными таблицами можно проделать множество различных действий. Вместо того, чтобы пытаться описать на словах все возможности сводных таблиц, проще продемонстрировать это на практике…

Данные, которые Вы анализируете с помощью сводных таблиц, не могут быть какими попало. Это должны быть необработанные исходные данные, вроде какого-то списка. Например, это может быть список совершённых продаж в компании за последние шесть месяцев.

Посмотрите на данные, показанные на рисунке ниже:

Обратите внимание, что это не сырые исходные данные, поскольку для них уже подведены итоги. В ячейке B3 мы видим $30000, что, вероятно, является суммарным результатом, который сделал James Cook в январе. Где же тогда исходные данные? Откуда взялась цифра $30000? Где исходный список продаж, из которого этот итог за месяц был получен? Ясно, что кто-то совершил огромный труд по упорядочиванию и сортировке всех данных о продажах за последние шесть месяцев и превратил их в таблицу итогов, которую мы видим. Сколько, по-вашему, это заняло времени? Час? Десять часов?

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

Если вернуться к исходному списку продаж, то он выглядел бы примерно так:

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

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

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

Итак, запускаем Excel… и загружаем такой список…

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

Выделите любую ячейку из этого списка:

Затем на вкладке Insert (Вставка) выберите команду PivotTable (Сводная таблица):

Появится диалоговое окно Create PivotTable (Создание сводной таблицы) с двумя вопросами для Вас:

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

Так как на предыдущем шаге мы уже выбрали одну из ячеек списка, то для создания сводной таблицы будет выделен весь список автоматически. Заметьте, что мы можем выбрать другой диапазон, другую таблицу и даже какой-нибудь внешний источник данных, например, таблицу базы данных Access или MS-SQL. К тому же нам необходимо выбрать, где разместить новую сводную таблицу: на новом листе или на одном из существующих. В данном примере мы выберем вариант – New Worksheet (На новый лист):

Excel создаст новый лист и разместит на нем пустую сводную таблицу:

Как только мы кликнем по любой ячейке в сводной таблице, появится ещё одно диалоговое окно: PivotTable Field List (Поля сводной таблицы).

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

Область Values (Значения), вероятно, самая важная из четырёх. То, какой заголовок помещён в эту область, определяет, по каким данным будут подводиться итоги (сумма, среднее, максимум, минимум и т.д.) Это, почти всегда, численные значения. Отличный кандидат на место в этой области – данные под заголовком Amount (Стоимость) нашей исходной таблицы. Перетащим этот заголовок в область Values (Значения):

Обратите внимание, что заголовок Amount теперь отмечен галочкой, а в области Values (Значения) появилась запись Sum of Amount (Сумма по полю Amount), указывающая на то, что столбец Amount просуммирован.

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

Итак, наша первая сводная таблица создана! Удобно, но не особо впечатляет. Вероятно, мы хотим получить больше информации о наших данных, чем есть сейчас.

Обратимся к исходным данным и попробуем определить один или несколько столбцов, которые можно использовать, чтобы раздробить эту сумму. Например, мы можем сформировать нашу сводную таблицу таким образом, чтобы итоговая сумма продаж подсчитывалась для каждого продавца по отдельности. Т.е. в нашу сводную таблицу добавятся строки с именем каждого продавца компании и его итоговой суммой продаж. Чтобы достичь такого результата, достаточно перетащить заголовок Salesperson Row Labels (Строки):

Становится интересней! Наша сводная таблица начинает обретать форму…

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

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

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

Во-первых, мы можем создать двумерную сводную таблицу. Сделаем это, используя заголовок столбца Payment Method (Способ оплаты). Просто перетащите заголовок Payment Method в область Column Labels (Колонны):

Получим результат:

Выглядит очень круто!

Теперь сделаем трёхмерную таблицу. Как может выглядеть такая таблица? Давайте посмотрим…

Перетащите заголовок Package (Комплекс) в область Report Filter (Фильтры):

Заметьте, где он оказался…

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

Итак, если Вы правильно это понимаете, то нашу сводную таблицу можно назвать трёхмерной. Продолжим настраивать…

Если вдруг выясняется, что в сводной таблице должны выводится только оплата чеком и кредитной картой (то есть безналичный расчёт), то мы можем отключить вывод заголовка Cash (Наличными). Для этого рядом с Column Labels нажмите стрелку вниз и в выпадающем меню снимите галочку с пункта Cash :

Давайте посмотрим, на что теперь похожа наша сводная таблица. Как видите, столбец Cash исчез из нее.

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

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

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

Во-первых, найдём запись Sum of Amount в области Values (Значения) и кликнем по ней. В появившемся меню выберем пункт Value Field Settings (Параметры полей значений):

Появится диалоговое окно Value Field Settings (Параметры поля значений).

Нажмите кнопку Number Format (Числовой формат), откроется диалоговое окно Format Cells (Формат ячеек):

Из списка Category (Числовые форматы) выберите Accounting (Финансовый) и число десятичных знаков установите равным нулю. Теперь несколько раз нажмите ОК , чтобы вернуться назад к нашей сводной таблице.

Как видите, числа оказались отформатированы как суммы в долларах.

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

Откройте вкладку PivotTable Tools: Design (Работа со сводными таблицами: Конструктор):

Выберите любой подходящий стиль и посмотрите на результат в своей сводной таблице:

Прочие настройки сводных таблиц в Excel

Иногда приходится фильтровать данные по датам. Например, в нашем списке торговых операций присутствует много-много дат. Excel предоставляет инструмент для группировки данных по дням, месяцам, годам и т.д. Давайте посмотрим, как это делается.

Для начала уберите запись Payment Method из области Column Labels (Колонны). Для этого перетащите его обратно к списку заголовков, а на его место переместите заголовок Date Booked (Дата бронирования):

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

Чтобы исправить это, кликните правой кнопкой мыши по любой дате и выберите из контекстного меню пункт Group (Группировать):

Появится диалоговое окно группировки. Мы выбираем Months (Месяцы) и жмём ОК :

Вуаля! От такой таблицы намного больше пользы:

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

Есть еще один очень важный момент, который необходимо знать! Вы можете создать не один, а несколько уровней заголовков строк (или столбцов):

… а выглядеть это будет так…

То же самое можно проделать с заголовками столбцов (или даже с фильтрами).

Вернёмся к исходному виду таблицы и посмотрим, как вывести средние значения вместо сумм.

Для начала кликните на Sum of Amount и из появившегося меню выберите Value Field Settings (Параметры полей значений):

В списке Summarize value field by (Операция) в диалоговом окне Value Field Settings (Параметры поля значений) выберите Average (Среднее):

Заодно, пока мы здесь, давайте изменим Custom Name (Пользовательское имя) с Average of Amount (Количество по полю Amount) на что-нибудь покороче. Введите в этом поле что-нибудь вроде Avg :

Нажмите ОК и посмотрите, что получилось. Обратите внимание, все значения изменились с итоговых сумм на средние значения, а заголовок таблицы (в левой верхней ячейке) поменялся на Avg :

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

Вот пошаговая инструкция, как сделать это, начиная с пустой сводной таблицы:

1. Перетащите заголовок Salesperson (Торговый представитель) в область Column Labels (Колонны).
2. Трижды перетащите заголовок Amount (Стоимость) в область Values (Значения).
3. Для первого поля Amount измените название на Total (Сумма), а формат чисел в этом поле на Accounting (Финансовый). Количество десятичных знаков равно нулю.
4. Второе поле Amount назовите Averag e, операцию для него установите Average (Среднее) и формат чисел в этом поле тоже измените на Accounting (Финансовый) с числом десятичных знаков равным нулю.
5. Для третьего поля Amount установите название Count и операцию для него – Count (Количество)
6. В области Column Labels (Колонны) автоматически создано поле Σ Values (Σ Значения) – перетащите его в область Row Labels (Строки)

Вот что мы получим в итоге:

Общая сумма, среднее значение и количество продаж – всё в одной сводной таблице!

Заключение

Сводные таблицы Microsoft Excel содержат очень-очень много функций и настроек. В такой небольшой статье их все не охватить даже близко. Чтобы полностью описать все возможности сводных таблиц, потребовалась бы небольшая книга или большой веб-сайт. Смелые и любознательные читатели могут продолжить исследование сводных таблиц. Для этого достаточно щелкать правой кнопкой мыши практически на любом элементе сводной таблицы и смотреть, какие открываются функции и настройки. На Ленте Вы найдёте две вкладки: PivotTable Tools: Options (Анализ) и Design (Конструктор). Не бойтесь допустить ошибку, всегда можно удалить сводную таблицу и начать все заново. У Вас есть возможность, которой никогда не было у давних пользователей DOS и Lotus 1-2-3.

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

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

Теперь о требованиях, которых необходимо придерживаться при ее создании в Эксель. Исходные данные должны быть представлены в виде таблицы, у которой должно быть название для столбцов, то есть шапка. Для этих целей отлично подойдет создание умной таблицы в Excel . Также она не должна содержать пустые строки, столбцы и ячейки. Не должно быть скрытых строк/столбцов и объединенных ячеек.

Как создать

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

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

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

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

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

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

Как расставить поля

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

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

В качестве строк выберем «Товары» . Аналогичным образом перетягиваем нужное поле в область «Названия строк» .

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

Если бы у нас в исходной таблице был столбец «Единицы» . То данная сводная таблица показывала бы, какой продавец, в каком месяце, сколько продал единиц товара по определенной цене.

В область «Названия столбцов» перетянем поле «Дата» . Чтобы отобразить продажи не за каждый день, а, к примеру, по месяцам, кликните по любой дате правой кнопкой мыши и выберите из меню «Группировать» .

Таблица примет следующий вид.

Теперь в область «Значения» перетянем поле «Сумма» .

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

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

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

Как работать с данными

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

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

В области «Значения» можно настроить параметры для поля. В примере выводится сумма значений: Рома в феврале продал рубашек на сумму 1 800.00. давайте посмотрим, сколько это штук. Кликаем левой кнопкой мышки по строке «Сумма по полю…» и выбираем из меню «Параметры полей значений» .

В следующем окне из списка выбираем «Количество» , выберите то, что подходит конкретно в вашем случае, и нажимаем «ОК» .

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

Теперь сделаем, чтобы общий фильтр для таблицы был по месяцам. Меняем области: в «Фильтр отчета» перетаскиваем поле «Дата» , в «Названия столбцов» – «Продавец» .

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

Обратите также внимание на ленту. При выделении любой ячеек из сводной таблицы, на ней появляется вкладка «Работа со сводными таблицами» с двумя подвкладками «Параметры» и «Конструктор» .

Проблема, с которой мы все сталкиваемся - это не отсутствие данных; наоборот - это огромные объемы данных! Именно поэтому я выступаю за использование замечательной функции Excel - Cводные таблицы , чтобы обобщить и проанализировать ваши данные.

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

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

Этот урок, мы построим по нашему , для лучшей работы с данными. Я покажу вам пять моих любимых передовых приёмов сводной таблицы.

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

Как применять продвинутые приёмы сводных таблицы в Excel (короткое видео)

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

5 продвинутых приёмов сводных таблиц в Excel

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

1. Срезы

Срезы - инструмент указания и клика, чтобы уточнить данные, включенные в сводную таблицу Excel. Вставьте срез и вы сможете легко изменять данные, включенные в сводную таблицу.

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

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

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

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

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

2. Временная шкала

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

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

Совет: если эта функция у вас не работает, убедитесь, что в исходных данных есть дата, отформатированная в электронной таблице, как дата.

Чтобы добавить временную шкалу, убедитесь, что вы выбрали сводную таблицу (щелкните внутри неё), а затем нажмите кнопку Анализ > Вставить временную шкалу на ленте Excel. Во всплывающем окне отметьте столбец даты (или несколько столбцов) и нажмите OK , чтобы создать временную шкалу.

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

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

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

3. Табличный вид

Стандартный вид сводных таблиц в Excel выглядит как водопад; при перетаскивании большего количества полей в строки, Excel создает больше «слоев» в данных.

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

Выберите Конструктор > Макет отчета > Показать в табличной форме , чтобы работать с вашей сводной таблицей в виде обычной таблицы.

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

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

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

4. Вычисляемые поля

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

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

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

Используйте Анализ > Поля, элементы и наборы > Вычисляемое поле , чтобы вставить вычисленное поле в вашу сводную таблицу.

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

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

После добавления названия этого поля, я добавлю знак умножения * , а затем дважды кликаю по общему количеству (quantity). Давайте продолжим и нажмите ОК .

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

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

Я умножил общую стоимость заказа на 1.05 для расчета стоимости, включая налог с продаж; вы можете использовать числовые значения наряду с существующими полями.

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

Перейдите в Вставка > , чтобы попробовать эту функцию.

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

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

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

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

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

Повторяйте и продолжайте обучение (с ещё бо́льшими уроками по Excel)

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

Эти уроки вам продвинуть ваши навыки с Excel и сводным таблицам на следующий уровень. Проверь их:

• ExcelZoo имеет большой обзор приёмов сводной таблицы в их статье, 10 уроков для освоения сводных таблиц (на английском).
• Мы в Envato Tuts+ рассмотрели сводные таблицы с помощью урока для новичков .
• Для более простого введения в Microsoft Excel ознакомьтесь с нашей учебной серией .

Что вы всё ещё хотите узнать о сводных таблицах? Сообщите мне ваши идеи или вопросы в комментариях ниже этого урока.