Данные для сводной таблицы в excel: Создание сводных таблиц в Excel

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

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

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

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

Сводная таблица состоит из четырех областей: Фильтры, Колонны, Строки и Значения. В зависимости от того, куда вы разместите данные, внешний вид сводной таблицы будет меняться.

Давайте рассмотрим функцию каждой из областей более подробно.

Область значений

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

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

Область строк

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

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

Область столбцов

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

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

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

Область фильтров

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

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

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

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

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

• Щелкните на любой ячейке, находящейся внутри таблицы с исходными данными (те, которые вы будете использовать для создания сводной таблицы)
• Перейдите к вкладке Вставка –> Таблица -> Сводная таблица, как показано на рисунке.

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

На данном этапе вы создали пустой отчет сводной таблицы на ново листе.

Макет сводной таблицы

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

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

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

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

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

В списке с полями для добавления в отчет, ставим галочку напротив поля

Федеральный округ. Теперь в области Строки и в сводной таблице появились данные поля.

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

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

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

Изменение сводной таблицы

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

Поля сводной таблицы и переместите поле Регион в область Строки. Посмотрите как изменилась ваша таблица.

Использование фильтров в сводной таблице

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

Обновление сводной таблицы

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

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

Щелкаем левой кнопкой мыши в любом месте сводной таблицы. Идем во вкладку Работа со сводными таблицами -> Анализ –> Источник данных.

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

Итог

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

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

 

Вам также могут быть интересны следующие статьи

Сводная таблица — Википедия

Сводная таблица (англ. Pivot table) — инструмент обработки данных, служащий для их обобщения.

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

Термин «сводная таблица» используется различными производителями. В США корпорация Microsoft имеет торговую марку конкретного вида сводной таблицы^[1](отменено 3го апреля 2020).

Сводные таблицы можно рассматривать также как некое упрощение концепции OLAP.

Первое упоминание о сводных таблицах было в книге «Анализ данных в сводных таблицах» авторов Билла Елена и Майка Александера^[2]. Основателем термина «сводная таблица» считается Пито Салас. Он разработал первую программу (Lotus Improv), которая помогала пользователям увидеть закономерности в данных электронных таблиц для быстрого построения моделей данных. В этой программе пользователь мог определять и сохранять наборы категорий, затем изменять представление с помощью перетаскивания категории мышкой. Эта особенность, в дальнейшем, превратилась в базовую для сводных таблиц. Lotus Development выпустила программу в 1991 году на платформе NeXT. Несколько месяцев спустя, технология появилась на компьютерах Mac и называлась DataPivot^[3]. Компания Borland купила технологию DataPivot в 1992 году и внедрила её в свой проект электронных таблиц (Quattro Pro). В 1993 году, во время того, как появилась версия Improv.

Эта функциональность была значительно улучшена в последующих версиях Microsoft Excel:

Excel 97 включал в себя PivotTable Wizard, который позволял разработчикам писать макросы на языке Visual Basic для создания или модификации сводных таблиц.

Excel 2000 представил технологию «Pivot Charts» для графического представления табличных данных.

Пример сводной таблицыПравить

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

Region	Gender	Style	Ship date	Units	Price	Cost
East	Boy	Tee	1/31/2005	12	11.04	10.42
East	Boy	Golf	1/31/2005	12	13	12.6
East	Boy	Fancy	1/31/2005	12	11. 96	11.74
East	Girl	Tee	1/31/2005	10	11.27	10.56
East	Girl	Golf	1/31/2005	10	12.12	11.95
East	Girl	Fancy	1/31/2005	10	13.74	13.33
West	Boy	Tee	1/31/2005	11	11.44	10.94
West	Boy	Golf	1/31/2005	11	12.63	11.73
West	Boy	Fancy	1/31/2005	11	12.06	11.51
West	Girl	Tee	1/31/2005	15	13.42	13.29
West	Girl	Golf	1/31/2005	15	11.48	10.67

В то время, как такие таблицы могут содержать большое количество данных, довольно сложно воспринимать какую-то резюмированную информацию по ним. Сводная таблица может быстро объединять данные и выделять нужную информацию. Использование сводных таблиц очень распространено и зависит от ситуации. Первый вопрос, который надо задать перед составлением сводной таблицы: «Что мне нужно получить?» В нижеприведенном примере, вопрос может быть сформулирован следующим образом: «Сколько единиц продукции мы продали в каждом регионе для каждой даты поставки?».
 
Сводная таблица обычно содержит строки, колонки и поля данных (или фактов). В этом случае, столбец — Ship Date, строка — Region. Нужные нам данные — это сумма единиц продукции (sum of Units). Эти поля допускают несколько видов функций агрегации, включая суммирование, нахождение среднего, стандартное отклонение, количество элементов и другие. В нашем случае, суммарное количество поставленных единиц продукции отражено в виде операции суммирования.

Как работает сводная таблицаПравить

Используя вышеприведенный пример, программное обеспечение будет искать все различные значения для Региона. В этом случае: North, South, East, West. Кроме того, оно найдет все различные значения для Ship Date. В соответствии с типом агрегации, будет найдена сумма фактов и отражена на многомерном графике. В примере, первое значение искомой информации — 66. Это число было получено в результате поиска всех записей, где значением региона был East и Ship Date был равен 1/31/2005. Затем все единицы продукции из полученной коллекции (ячейки с E2 до E7), были просуммированы в финальный результат.
Сводные таблицы не создаются автоматически. Для начала нужно выбрать все данные в исходной таблице (например, в MS Excel), затем найти функцию вставки сводной таблицы. Это создаст список полей сводной таблицы. Например, если у нас есть таблица, отражающая данные по продажам компании, которые включают дату продажи, имя продавца, название проданной единицы, её цвет, количество проданных единиц, цену для каждой и суммарную цену.

Новые поля появятся на правой стороне листа. По умолчанию, под этим списком будет размещен макет дизайна сводной таблицы. Каждое из полей списка можно будет перетаскивать на этот макет, который будет содержать четыре опции:
— Фильтр отчетов;
— Название столбцов;
— Название строк;
— Суммирование значений.

«Фильтр Отчета» используется в Excel для применения определенного фильтра ко всей таблице. Например, если перетащить поле «Цвет элемента» в данную область, то над созданной таблицы появятся фильтры. Этот фильтр имеет выпадающие списки («Черный», «Красный» и «Белый» как в приведенном выше примере). При выборе определенной опции из списка (например «Черный»), видимая область таблицы будет содержать данные только тех строк, в которых «Цвет Элемента = Черный».

Названия столбцовПравить

«Названия Столбцов» используется в Excel для применения фильтра к одному или нескольким столбцам, которые должны быть представлены в сводной таблице. Например, если мы перетащим поле «Sales Person» в данную область, то построенная таблица будет иметь значения из столбца «Sales Person», то есть число столбцов будет равно числу «Sales Person». Также будет добавлен столбец «Total». Касательно вышеприведенного примера, оператор создаст в таблице 3 столбца — «Harry», «Larry» и «Grand Total». Над данными появится фильтр «Названия Столбцов», где можно выбрать или отменить выбор конкретного менеджера по продажам для сводной таблицы. Таблица не будет содержать ни численных величин, ни выбранных числовых полей, но при выборе таковых, происходит автоматическое обновление столбца » Grand Total «.

«Названия Строк» используется в Excel для применения фильтра к одной или нескольким строкам, которые должны быть представлены в сводной таблице. Например, если мы перетащим поле «Sales Person» в данную область, то построенная таблица будет иметь значения из столбца «Sales Person», то есть число строк будет равно числу «Sales Person». Также будет добавлена строка «Total». Касательно вышеприведенного примера, данный оператор создаст в таблице 3 строки — «Harry», «Larry» и «Grand Total». Над данными появится фильтр «Названия Строк», где можно выбрать или отменить выбор конкретного менеджера по продажам из сводной таблицы. Таблица не будет содержать ни численных величин, ни выбранных числовых полей, но при выборе таковых, происходит автоматическое обновление строки » Grand Total «.

Суммирование значенийПравить

Как правило, это имеет отношение к полям, имеющим числовые значения и которые могут быть использованы для различных типов вычислений. Однако использование текстовых значений также не было бы неправильным, но вместо суммы оператор выдаст их количество. Таким образом в вышеприведенном примере, если мы перетащим «Units Sold» в данную область вместе с со строкой «Sales Person», то тогда оператором будет добавлен новый столбец «Sum of Units Sold», содержащий значения напротив каждого продавца.

Row Labels	Sum of Units Sold
Harry	14
Larry	18
Grand Total	32

Поддержка в приложенияхПравить

Сводные таблицы в настоящее время рассматриваются как неотъемлемая часть электронных таблиц. Конкурирующие с Microsoft Excel программы, такие как Apache OpenOffice Calc, обеспечивают схожий функционал; в OpenOffice и LibreOffice вплоть до версии 3.3 сводная таблица называлась DataPilot, а начиная с версии 3.4 DataPilot был переименован в «Сводную Таблицу». Другие компании, такие как Quantrix и numberGo, также обеспечивают схожую функциональность.

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

Google Docs позволяет создавать простые сводные таблицы посредством специальных приложений-гаджетов Panorama Analytics, но к 2011 году их функциональность все еще оставалась ограниченной. В мае 2011 года компания Google объявила о развертывании изначально размещенной функции сводных таблиц в электронных таблицах редактора Google^[4].

Ajax платформа ZK также позволяет встраивание сводных таблиц в Веб-приложениях.
PostgreSQL, свободная объектно-реляционная система управления базами данных (СУБД), позволяет создавать сводные таблицы, используя модуль tablefunc^[5].

Сводная таблица как клиент к OLAPПравить

Сводные таблицы в Excel включают в себя функцию прямых запросов к OLAP-серверу для получения данных вместо их агрегирования из таблиц Excel. В такой конфигурации сводная таблица является простым клиентом OLAP-сервера. Сводная таблица в Excel позволяет подключаться не только к решениям от Microsoft (Analysis Service), но и к любому XML совместимому для аналитики (XMLA (англ.)русск., стандарта OLAP) серверу.
Существуют и другие клиенты OLAP-серверов: JPivot, Dundas, IcCube (Клиентская библиотека).

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

2.2.6. Анализ деловых данных в Excel 2007

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

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

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

Рис. 1.

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

Рис. 2.

Для построения отчета по этой таблице целесообразно применить мощное средство «Сводная таблица». Для применения этого средства к спискам данных или к таблицам данных необходимо активизировать одну из ячеек таблицы данных, например ячейку таблицы «Остатки товаров на складе». Затем щелкнуть кнопку «Сводная таблица», которая находится на вкладке «Вставка» в группе «Таблица» (рисунок 3).

Рис. 3.

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

Рис. 4.

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

Например, если выбрать поля: № склада, Наименование, Цена (грн.) и перетащить их в соответствующие области: «Название столбцов», «Название строк» и «Значения», то в правой части они будут отображаться в этих областях. При этом в левой части рабочего листа будет построена сводная таблица или отчет (рис. 5).

Рис. 5.

Следует отметить, что в области «Значения» выполняются какие-либо математические вычисления, например, суммирование (Сумма по полю Цена). Чтобы изменить тип вычислений, надо в области «Значения» щелкнуть левой кнопкой мыши по полю «Сумма по полю Цена» и в открывшемся меню выбрать команду «Параметры полей значений», затем в окне диалога «Параметры поля значений» выбрать требуемую функцию и щелкнуть на кнопке ОК.

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

Средства Excel для анализа данных и решения задач оптимизации

Мощными средствами анализа данных Excel 2007 являются:

• анализ «что – если», к которым относятся: подбор параметров и диспетчер сценариев;
• надстройка «Поиск решения» (надстройка Solver).

Средства анализ «что – если» помещены на вкладке «Данные» в группе «Работа с данными», а «Поиск решений» на вкладке «Данные» в группе «Analysis».

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

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

Средства анализа данных и решения задач оптимизации Excel 2007 аналогичны средствам Excel 2003. Работа со средствами Excel 2003 для анализа данных и решения задач оптимизации: подбор параметров, диспетчер сценариев и надстройка Solver подробно изложены на страничке: Решение задач оптимизации в Excel.

Далее …>>> Тема: 2.2.7. Проверочные средства и настройка Microsoft Excel 2007

Excel: как делать ссылки на сводную таблицу

Уважаемые сотрудники «Б & К»! Я часто пользуюсь сводными таблицами, и в связи с этим у меня вопрос: как правильно делать ссылки на ячейки сводного отчета? Дело в том, что при обычном способе создания ссылок Excel вместо адреса вставляет специальную функцию, а это иногда очень неудобно. Подскажите, есть ли простой способ решения этой проблемы? Я работаю с программой MS Excel 2010. Среди параметров программы подходящих настроек я не нашел. Надеюсь на вашу помощь.

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

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

1. Становимся на свободную ячейку, пусть это будет «D3».

2. Набираем символ «=» (начало формулы).

3. Щелкаем левой кнопкой мыши на ячейке «B3» (я хочу сделать ссылку на сумму реализации по контрагенту «ТОВ «Топаз»»). В ячейке «D3» вместо ссылки мы увидим такой результат: «=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(«Сумма»; $A$1;»Покупатель»;»ТОВ «»Топаз»»»)». При этом значение в ячейке «D3» будет равно «119,80», что соответствует объемам продаж по «ТОВ «Топаз»».

4. Копируем эту формулу вниз до ячейки «D8» (на всю высоту сводной таблицы). Результат во всех ячейках будет одинаковым — «119,80». То есть функция получения данных из сводного отчета сослалась на одну и ту же ячейку сводной таблицы.

Причина такого поведения лежит в параметрах функции «=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ()». Таких параметров у нее четыре. Первым идет название поля, по которому нужно выбрать итог. В нашем случае это поле «Сумма». Так это поле называлось в исходной базе, с этим именем оно и попало в сводный отчет. Вторым параметром стоит ссылка на ячейку с заголовком поля. В формуле эта ссылка выглядит как «$A$1». Кстати, абсолютная адресация в данном случае обязательна! Третий параметр — название поля, по которому Excel будет выбирать данные из сводного отчета. В формуле указано, что поиск конкретного числа в сводной таблице нужно делать по полю «Покупатель». Последний параметр — это строка для поиска конкретного значения среди покупателей. В нашей функции указано значение «ТОВ «Топаз»». Поэтому Excel выберет итог именно по этому контрагенту. Сразу бросается в глаза, что большинство параметров в функции «=ПОЛУЧИТЬ. ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ()» указаны в виде текстовых строк. Именно поэтому не сработала корректировка адресов при копировании формулы в ячейки «D3:D8», и все функции вернули один и тот же результат.

Кстати, исправить такую ситуацию несложно: нужно вместо фиксированного элемента «»ТОВ «»Топаз»»»» поставить ссылку на ячейку «A3». То есть формула в ячейке «D3» должна выглядеть так: «=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ («Сумма»;$A$1;»Покупатель» ;A3)» (изменения выделены полужирным начертанием). В этом варианте после копирования формулы вниз до ячейки «D8» мы получим правильные объемы реализации по каждому контрагенту.

Однако речь сейчас о другом. Использование функции «=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ()» имеет свои преимущества и недостатки. Среди преимуществ я бы указал, что независимо от порядка сортировки записей в сводной таблице ссылка через функцию обеспечит правильный результат. И это понятно — извлечение данных из сводного отчета функция делает по ключевому полю, а не по адресу рабочего листа! Если посмотреть на формулу в ячейке «D3», то ключевым полем для обращения к сводной таблице является название фирмы «ТОВ «Топаз»». И при этом не имеет никакого значения, где конкретно находится запись по этой фирме — на первой позиции отчета или в самом конце. Данные Excel подставит правильно.

Недостаток работы с функцией «=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ()» состоит в том, что нужно корректировать значение ключевого поля или заменять его ссылкой. Поэтому в некоторых случаях удобнее вместо встроенной функции использовать ссылки на ячейки сводной таблицы. Чтобы вставить такие ссылки автоматически (отказаться от использования функции «=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ()»), нужно знать одну тонкость.

Секрет Встроенную функцию «=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ()» Excel использует только при ссылках на поля в области данных сводного отчета. При организации ссылок на заголовки строк или колонок он вставляет обычные ссылки на ячейки рабочего листа.

Зная это правило, мы легко получим «нормальные» ссылки на ячейки сводной таблицы. Для этого делаем так:

1. Открываем документ, как на рис. 1.

2. Становимся на ячейку «D3».

3. Вводим символ «=» (начинаем запись формулы).

4. Щелкаем левой кнопкой мыши на ячейке «A3». Excel добавит в текущую ячейку ссылку «=A3», где записано название фирмы. В данном конкретном случае — это «ТОВ «Топаз»».

5. Нажимаем «Enter» (завершаем ввод формулы).

6. Копируем формулу в ячейку «E3».

Смотрим на содержимое ячеек «D3» и «E3». Как и следовало ожидать, там находятся обычные ссылки: «=A3» и «=B3». Одна указывает на ячейку с названием фирмы, вторая — на объем реализации. Теперь с этими ссылками можно делать все что угодно — переносить на другой лист, использовать в расчетах и т. д.

И последнее. Работа с обычными ссылками незаменима, когда нужно построить график по данным сводного отчета (!) в программе Excel 2003. При создании такого графика Excel 2003 формирует его на отдельном листе, а это не всегда удобно. Чтобы отказаться от такой возможности и построить диаграмму на текущем листе, нужно создать рабочую область со ссылками на данные сводной таблицы. А затем по этим ссылкам сформировать диаграмму. Для таблицы на рис. 1 процедура выглядит так:

1. Открываем документ, переходим на ячейку «D3».

2. Вводим в нее формулу «=A3».

3. Копируем формулу в ячейки «D3:E8». В результате мы получим копию данных из сводной таблицы в виде формул.

4. Строим график по данным «D3:E8».

5. Чтобы скрыть «рабочую область», форматируем значения в блоке «D3:D8» белым цветом или ставим график поверх ячеек «D3:D8», чтобы закрыть им вспомогательную информацию (рис. 2).

 

На сегодня все. Удачной работы! Жду ваши вопросы, замечания и предложения на bk@id. factor.ua, [email protected] или на форуме редакции www.bk.factor.ua/forum.

Помітили помилку? Виділіть її та натисніть Ctrl+Enter, щоб повідомити нас про це

Преобразуйте сводную кросс-таблицу в плоский список быстро и точно

(Внимание: видео может не отражать последние обновления. Используйте инструкцию ниже.)

Кросс-таблицы также называют сводными, двумерными (2D) или таблицами в «пользовательском» представлении. Они преподносят информацию в сжатой и наглядной матрице с заголовками столбцов и строк. Но такое представление данных не подходят для построения сводных таблиц PivotTable, графиков, фильтрации, экспорта данных в сторонние системы, т.д. Поэтому перед анализом данных так важно аккуратно преобразовать сводные таблицы в «плоский» список.

Инструмент «Редизайн таблицы» точно преобразует сводные таблицы в плоский список без написания макросов:

• Редизайн сводной таблицы в список в секунды

• Преобразование сложных таблиц с многоуровневыми заголовками

• Корректный редизайн таблиц с объединёнными или пустыми ячейками

• Сохранение заголовков столбцов

• Сохранение форматирования ячеек

Перед началом работы добавьте «Редизайн таблицы» в Excel

«Редизайн таблицы» – это один из 20+ инструментов в составе надстройки XLTools для Excel. Работает в Excel 2019, 2016, 2013, 2010, десктоп Office 365.

Скачать XLTools для Excel

– пробный период дает 14 дней полного доступа ко всем инструментам.

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

1. Нажмите кнопкуРедизайн таблицына вкладке XLTools Откроется диалоговое окно.
2. Выделите сводную таблицу, включая заголовки.

   Совет:нажмите на любую ячейку таблицы, и вся таблица будет выделена автоматически.

3. Укажите размер заголовков:

   В простой таблице: Заглавных строк = 1, Заглавных столбцов = 1

4. Укажите, следует ли поместить результат на новый или на существующий лист.

   Чтобы вставить плоский список на существующий лист, укажите начальную ячейку (верхняя левая).

5. Нажмите ОК Готово. Надстройка автоматически подберёт ширину столбцов для плоского списка.

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

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

1. Нажмите кнопкуРедизайн таблицына вкладке XLTools Откроется диалоговое окно.
2. Выделите сводную таблицу, включая заголовки.

   Совет:нажмите на любую ячейку таблицы, и вся таблица будет выделена автоматически.

3. Укажите размер заголовков:

   • Заглавных строк: число строк, которые составляют заголовок таблицы сверху.

   • Заглавных столбцов: число столбцов, которые составляют заголовок таблицы слева.

4. Укажите, следует ли поместить результат на новый или на существующий лист.

5. Нажмите ОК Готово. Надстройка автоматически подберёт ширину столбцов для плоского списка.

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

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

• Если пустые ячейки находятся в заголовке: перед редизайном заполните ячейки заголовков.

• Если пустые ячейки находятся в теле таблицы: вы можете пропустить соответствующие строки в плоском списке:

1. Нажмите кнопкуРедизайн таблицына вкладке XLTools.

2. Выделите сводную таблицу, включая заголовки.

3. Укажите размер заголовков.

4. Отметьте флажкомПропустить пустые значения.

5. Укажите, куда поместить результат.

6. Нажмите ОК Готово.

Как выполнить редизайн таблицы с объединёнными ячейками

1. Нажмите кнопкуРедизайн таблицына вкладке XLTools.

2. Выделите сводную таблицу, включая заголовки.

3. Укажите размер заголовков.

4. Отметьте флажкомДублировать значение в объединённых ячейках:

   • Если объединённые ячейки находятся в заголовке: содержимое объединённых ячеек заголовка будет продублировано в каждой соответствующей строке плоского списка.

   • Если в объединённые ячейки находятся в теле таблицы: значения в объединённых ячейках будут продублированы в каждой соответсвующей ячейке плоского списка.

5. Укажите, куда поместить результат.

6. Нажмите ОК Готово.

Как выполнить редизайн таблицы с сохранением заголовков

1. Нажмите кнопкуРедизайн таблицына вкладке XLTools.

2. Выделите сводную таблицу, включая заголовки.

3. Укажите размер заголовков.

4. Отметьте флажкомСохранить заголовки:

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

   • Категориям таблицы будет автоматически присвоен заголовок «Категория».

   • Переменным значениям таблицы будет автоматически присвоен заголовок «Значение».

5. Укажите, куда поместить результат.

6. Нажмите ОК Готово.

Как выполнить редизайн таблицы с сохранением формата ячеек

1. Нажмите кнопкуРедизайн таблицына вкладке XLTools.

2. Выделите сводную таблицу, включая заголовки.

3. Укажите размер заголовков.

4. Отметьте флажкомСохранить формат ячеек:

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

5. Укажите, куда поместить результат.

6. Нажмите ОК Готово.

Внимание:обработка больших таблиц с множеством форматов займёт больше времени.

Какие таблицы обрабатывает надстройка «Редизайн таблицы»

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

Термином «Таблица» в Excel часто обозначают разные понятия:

• «Настоящая» таблица — именованный диапазон с применением стиля таблицы (операция «Форматировать как таблицу»). Может быть преобразована в простой диапазон.

• Диапазон — простой диапазон внешне похожий на таблицу, с применением (или без) форматирования цвета фона, границ, т.д. Может быть преобразован в «настоящую» таблицу.

• Сводная таблица (PivotTable) — динамическая таблица, сгенерированная с помощью операции Excel «Сводная таблица» (PivotTable). Ячейки не могут быть отредактированы.

Надстройка XLTools «Редизайн таблицы» позволяет преобразовать в плоский список «настоящие» таблицы и диапазоны. Чтобы произвести редизайн PivotTable, сначала скопируйте диапазон такой таблицы и вставьте значения — это создаст простой диапазон, который далее можно преобразовать.

Урок Создание сводных таблиц в MS Excel

Практическая работа

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

Цель работы: освоить навыки создания, редактирования и анализа данных на основе сводных таблиц.

Задание.

Построить сводную таблицу для расчета месячной заработной платы рабочих при повременной форме оплаты труда, начисления премии и учета удержаний. Премия дифференцирована по разрядам: 2 разряд 20%, 3 разряд 30%, 4 разряд 40% к тарифу, 5 разряд – 50%. Удержания берутся со всех видов начислений (зарплата, премия) и составляют 13% от суммы начислений.

Методика выполнения работы

1. Открыть новую книгу.

2. Переименовать лист в Картотека.

3. Подготовить исходные данные (см. табл. 1)

Таблица 1.

4. Установить курсор в список, выполнить команду меню Вставка→ Сводная таблица для вызова Мастера сводных таблиц и диаграмм.

4. Указать тип источника – Создать таблицу на основе данных, находящихся в списке или базе данных Microsoft Excel. Выбрать вид создаваемого отчета → Сводная таблица.

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

4. Разместить поля в макете сводной таблицы:

Фильтр отчета – Профессия, Названия строк – ФИО, Названия столбцов –

Разряд работающего, Значения – Тариф, Операция – Сумма.

Макет сводной таблицы представлен на рисунке 1.

Рис 1. Макет сводной таблицы

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

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

Для преобразования сводной таблицы следует:

1. Установить курсор в область сводной таблицы.

2. Выполнить команду Параметры (Анализ – MS Excel 2013) → Формулы → Вычисляемое поле

для создания вычисляемого поля.

Рис. 2. Сводная таблица.

3. На рис. 3 представлено диалоговое окно для формирования вычисляемого поля. Имя поля – Зарплата, Формула вычисления: =Тариф*168.

(Коэффициент 168 зависит от количества рабочих часов в текущем учетном периоде.). Для добавления поля в формулу можно воспользоваться кнопкой Добавить.

Рис. 3. Создание вычисляемого поля

4. Установить курсор в область сводной таблицы.

5. С помощью кнопки Список полей на ленте Параметры откройте макет

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

6. Удалить поле Сумма по полю Тариф (простым перетаскиванием мышкой за поле окна).

6. Установить курсор в области сводной таблицы на поле Сумма по полю Зарплата.

6. На ленте Параметры выполнить команду Активное поле → Параметры поля (рис. 4):

Рис. 4. Задание параметров вычисляемого поля

9. Установить курсор в область сводной таблицы на поле Разряд работающего.

10. Создать вычисляемый объект Премия. Премия выплачивается как процент к начисленной заработной плате, дифференцируется по разрядам: 2 разряд – 20%, 3 разряд – 30%, 4 разряд – 40%, 5 разряд – 50%.

• Формула: = ‘2’*0,20+’3’*0,30+’4’*0,4+’5’*0,50

Рис. 5. Создание вычисляемого объекта

11. Установить курсор в область сводной таблицы на поле Разряд работающего. Создать вычисляемый объект Вычеты, сумма вычетов это 13% от суммы заработка и премии.

 Выполнить команду Формулы → Вычисляемый объект. Указать имя объекта – Вычеты (рис. 6).

Рис. 6. Создание вычисляемого объекта

 В окне Поля выбрать поле Разряд работающего, в окне Элементы выбрать элементы для построения формулы вида:

12. Выполнить команду Параметры→Формулы→Вывести формулы для просмотра выражений вычисляемых полей и объектов (рис. 7).

Если потребуется изменить нормативы (количество отработанных часов, % премии, % вычетов), следует отредактировать вычисляемые поля и объекты – команда меню Формулы→Вывести формулы, вызывать поле/объект, внести изменения

Рис. 7. Вывод формул

13. Переименовать лист, содержащий сводную таблицу, присвоив имя, Сводная таблица 1.

14. Поставить курсор внутрь сводной таблицы и на ленте Конструктор выполнить команду Макет отчета. Выбрать тип отчета.

15. Поставить курсор внутрь сводной таблицы и щелкнуть на ленте Параметры кнопку Сводная диаграмма.

16. В готовой диаграмме перетащить Разряд работающего в область Поле ряда. Выбирая вид профессии просмотреть данные по различным профессиям.

17. Сохранить рабочую книгу.

Сводная диаграмма

в Excel — Easy Excel Tutorial

Вставить сводную диаграмму | Сводная диаграмма фильтров | Изменить тип сводной диаграммы

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

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

Вставить сводную диаграмму

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

1. Щелкните любую ячейку в сводной таблице.

2. На вкладке «Анализ» в группе «Инструменты» щелкните «Сводная диаграмма».

Появится диалоговое окно «Вставить диаграмму».

3. Щелкните OK.

Ниже представлена ​​сводная диаграмма. Эта сводная диаграмма поразит и впечатлит вашего начальника.

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

Сводная диаграмма фильтров

Чтобы отфильтровать эту сводную диаграмму, выполните следующие шаги.

1. Используйте стандартные фильтры (треугольники рядом с продуктом и страной). Например, используйте фильтр «Страна», чтобы показать только общую сумму каждого продукта, экспортированного в США.

2. Снимите фильтр страны.

3. Поскольку мы добавили поле «Категория» в область «Фильтры», мы можем фильтровать эту сводную диаграмму (и сводную таблицу) по категориям.Например, используйте фильтр «Категория», чтобы отображать только овощи, экспортируемые в каждую страну.

Изменить тип сводной диаграммы

Вы можете в любой момент перейти на другой тип сводной диаграммы.

1. Выберите диаграмму.

2. На вкладке «Дизайн» в группе «Тип» щелкните «Изменить тип диаграммы».

3. Выберите «Пирог».

4.Щелкните ОК.

Результат:

Примечание: круговые диаграммы всегда используют одну серию данных (в данном случае Beans). Чтобы получить сводную диаграмму страны, поменяйте местами данные по оси. Сначала выберите диаграмму. Затем на вкладке «Дизайн» в группе «Данные» нажмите «Переключить строку / столбец».

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

Excel | Exceljet

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

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

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

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

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

Содержание

Пошаговое руководство

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

Пример данных

Образец данных содержит 452 записи с 5 полями информации: Дата, Цвет, Единицы, Продажи и Регион. Эти данные идеально подходят для сводной таблицы.

Данные в соответствующей таблице Excel с именем «Таблица1». Таблицы Excel — отличный способ создания сводных таблиц, поскольку они автоматически корректируются при добавлении или удалении данных.

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

Вставить поворотный стол

1. Для начала выберите любую ячейку в данных и щелкните Сводная таблица на вкладке Вставка ленты:

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

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

3. Нажмите «ОК», и Excel построит пустую сводную таблицу, начиная с ячейки h5.

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

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

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

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

Добавить поля

1. Перетащите поле «Продажи» в область «Значения».

Excel вычисляет общую сумму 26356. Это сумма всех значений продаж во всем наборе данных:

2. Перетащите поле «Цвет» в область «Строки».

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

.

Обратите внимание, что общая сумма остается 26356. Это имеет смысл, потому что мы все еще предоставляем полный набор данных.

Давайте теперь посмотрим на панель полей.Вы можете видеть, что Цвет — это поле строки, а Продажи — это поле значения:

Форматирование чисел

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

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

1. Щелкните правой кнопкой мыши любой номер продажи и выберите Формат номера:

.

2. Примените форматирование валюты с нулевым десятичным знаком, нажмите OK:

В итоговой сводной таблице ко всем значениям продаж применен денежный формат:

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

Сортировка по значению

1. Щелкните правой кнопкой мыши любое значение «Продажи» и выберите «Сортировка»> «От наибольшего к наименьшему».

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

Обновить данные

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

1. Выберите ячейку F5 и измените 11,00 долларов на 2000 долларов.

2. Щелкните правой кнопкой мыши в любом месте сводной таблицы и выберите «Обновить».

Уведомление «Красный» теперь является самым продаваемым цветом и автоматически перемещается наверх:

3. Измените F5 обратно на 11,00 долларов и снова обновите опорную точку.

Примечание: изменить F5 на 2000 долларов нереально, но это хороший способ добиться изменения, которое вы можете легко увидеть в сводной таблице. Попробуйте изменить существующий цвет на что-нибудь новое, например «Золотой» или «Черный».Когда вы обновитесь, вы увидите, что появился новый цвет. Вы можете использовать отмену для возврата к исходным данным и поворота.

Поле второго значения

В качестве поля значения можно добавить несколько полей.

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

Процент от общего числа

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

1. Удалите единицы измерения из области значений

2. Добавьте (снова) поле «Продажи» в область «Значения».

3. Щелкните второй экземпляр правой кнопкой мыши и выберите «% от общей суммы»:

Результат — разбивка по цвету вместе с процентом от общего числа:

Примечание: числовой формат для процентов также был скорректирован для отображения 1 десятичного знака.

Вот панель Поля на данный момент:

Сгруппировать по дате

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

1. Удалите второе поле Продажи (Продажи2).

2. Перетащите поле «Дата» в область «Столбцы».

3. Щелкните правой кнопкой мыши дату в области заголовка и выберите «Группа»:

4. Когда появится окно «Группа», сгруппируйте только по годам (снимите флажок «Месяцы и кварталы»):

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

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

Вот панель Поля на данный момент:

Двусторонний шарнир

Сводные таблицы могут отображать данные в различных двухмерных схемах.

1. Перетащите поле «Дата» из области столбцов

2. Перетащите область в область столбцов.

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

3.Поменяйте местами регион и цвет (т. Е. Перетащите регион в область строк, а цвет — в область столбцов).

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

Снова обратите внимание, что общий объем продаж (26 356 долларов США) одинаков для всех сводных таблиц выше . Каждая таблица представляет собой разное представление одинаковых данных , поэтому все они в сумме составляют одинаковое общее количество .

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

Ключевые преимущества сводной таблицы

Простота . Базовые сводные таблицы очень просто настроить и настроить. Нет необходимости изучать сложные формулы.

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

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

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

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

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

Фильтрация . Сводные таблицы содержат несколько инструментов для фильтрации данных. Нужно посмотреть на Северную Америку и Азию, но исключить Европу? Сводная таблица упрощает задачу.

Графики . Если у вас есть сводная таблица, вы можете легко создать сводную диаграмму.

Дополнительные ресурсы сводной таблицы

Советы по сводным таблицам | Exceljet

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

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

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

Пример исходных данных

Затем выполните следующие четыре шага:

1. На вкладке «Вставка» ленты нажмите кнопку «Сводная таблица»
2. В диалоговом окне «Создание сводной таблицы» проверьте данные и нажмите «ОК».
3. Перетащите поле «метка» в область «Метки строк» ​​(например,г. покупатель)
4. Перетащите числовое поле в область значений (например, продажи)

Базовая сводная таблица примерно за 30 секунд

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

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

2. Очистите исходные данные

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

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

Иногда может потребоваться добавить недостающие данные. Советы смотрите в этом видео:

Видео: Как быстро заполнить недостающие данные

3. Сначала посчитайте данные

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

300 имен означает, что у нас 300 сотрудников. Проверьте.

4.Планируйте перед сборкой

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

5. Используйте таблицу для ваших данных, чтобы создать «динамический диапазон».

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

Чтобы использовать таблицу для сводной таблицы:

1. Выберите любую ячейку в данных, используя сочетание клавиш Ctrl-T, чтобы создать таблицу
2. Нажмите кнопку Суммировать со сводной таблицей (TableTools> Дизайн)
3. Обычное построение сводной таблицы
4. Прибыль: данные, которые вы добавляете в таблицу, автоматически появятся в сводной таблице при обновлении.

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

Создание простой таблицы из данных с помощью (Ctrl-T)

Теперь, когда у нас есть таблица, мы можем использовать Summarize with a Pivot Table

6.Используйте сводную таблицу для подсчета вещей

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

1. Обычное создание сводной таблицы
2. Добавить отдел в виде метки строки
3. Добавить поле имени сотрудника как значение
4. В сводной таблице будет отображаться количество сотрудников по отделам

Распределение сотрудников по отделам

7.Показать итоги в процентах

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

1. Добавить продукт в сводную таблицу в виде метки строки
2. Добавить продажи в сводную таблицу как значение
3. Щелкните правой кнопкой мыши поле «Продажи» и установите для параметра «Показать значения как» значение «% от общей суммы»

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

Изменение отображения значения на% от общего

Сумма сотрудников отображается в% от общего количества

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

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

1. Обычное создание сводной таблицы
2. Добавить товар в виде ярлыка строки
3. Добавьте любое другое текстовое поле (категорию, клиента и т. Д.) В качестве значения
4. Сводная таблица покажет список всех продуктов, которые появляются в данных о продажах

Все продукты, которые появляются в данных, перечислены (включая опечатку)

9. Создайте автономную сводную таблицу

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

1. Обновите сводную таблицу, чтобы обеспечить актуальность кеша (Инструменты сводной таблицы> Обновить)
2. Удалить рабочий лист, содержащий данные
3. Обычное использование сводной таблицы

Видео: Как сделать автономную сводную таблицу

10. Сгруппировать сводную таблицу вручную

Хотя сводные таблицы автоматически группируют данные разными способами, вы также можете группировать элементы вручную в свои собственные настраиваемые группы.Например, предположим, что у вас есть сводная таблица, в которой показано распределение сотрудников по отделам. Предположим, вы хотите дополнительно сгруппировать отделы проектирования, исполнения и поддержки в группу 1, а отделы продаж и маркетинга — в группу 2. Группа 1 и группа 2 не отображаются в данных, это ваши собственные настраиваемые группы. Чтобы сгруппировать сводную таблицу в специальные группы, Группа 1 и Группа 2:

1. Удерживая нажатой клавишу Control, выберите каждый элемент в первой группе
2. Щелкните правой кнопкой мыши один из элементов и выберите «Группа» в меню.
3. Excel создает новую группу «Группа1»
4. Выберите Marketing and Sales в столбце B и сгруппируйте как указано выше
5. Excel создает другую группу, «Группа2»

Запуск группировки вручную

Половина ручной группировки — Группа 1 завершена

Готовая группировка вручную

11.Группируйте числовые данные в диапазоны

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

1. Обычное создание сводной таблицы
2. Добавить возраст как метку строки, голосование как метку столбца и имя как значение
3. Щелкните правой кнопкой мыши любое значение в поле Возраст и выберите Группа
4. Введите 10 в качестве интервала в области ввода «По:»
5. Когда вы нажмете ОК, вы увидите данные голосования, сгруппированные по возрасту в 10-летние периоды

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

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

Группирование поля возраста в 10-летние периоды

Выполнена группировка результатов голосования по возрастному диапазону

12.Переименовать поля для лучшей читаемости

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

Переименовать поле, введя его вместо исходного имени

13.Добавьте пробел в имена полей, когда Excel жалуется

При попытке переименовать поля вы можете столкнуться с проблемой, если попытаетесь использовать точно такое же имя поля, которое отображается в данных. Например, предположим, что у вас есть поле с названием «Продажи» в исходных данных. В качестве поля значения оно отображается как Сумма продаж , но (разумно) вы хотите, чтобы оно отображало Продажи . Однако при попытке использовать «Продажи» Excel жалуется, что поле уже существует, и выдает сообщение об ошибке «Имя поля сводной таблицы уже существует».

Excel не нравится ваше новое имя поля

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

Добавление пробела к имени позволяет избежать проблемы

14. Добавить поле в сводную таблицу более одного раза

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

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

1. Добавьте текстовое поле в область значений (например, имя, имя и т. Д.)
2. По умолчанию вы получите счетчик текстовых полей
3. Добавьте то же поле еще раз в область значений
4. Щелкните второй экземпляр правой кнопкой мыши и измените «Показать значения как» на «% от общей суммы»
5. Переименуйте оба поля как хотите

Настройка поля для отображения процентов от общего количества

Поле имени добавлялось дважды

15.Автоматически форматировать все поля значений

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

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

1. Щелкните правой кнопкой мыши поле «Продажи» и выберите в меню «Параметры поля значения».
2. Нажмите кнопку «Числовой формат» в появившемся диалоговом окне «Параметры поля значения».
3. Установите формат «Учет» и нажмите «ОК» для выхода.

Настройка формата непосредственно в поле значения

16.Разверните вниз, чтобы увидеть (или извлечь) данные, стоящие за любым общим значением

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

Дважды щелкните сумму, чтобы «развернуть»

50 инженеров, автоматически извлеченных в новый лист

17. Клонируйте сводные таблицы, когда вам нужно другое представление

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

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

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

Видео: как клонировать сводную таблицу

18. Отклонить сводную таблицу для независимого обновления

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

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

1. Вырезать всю сводную таблицу до буфера обмена
2. Вставьте сводную таблицу в новую книгу
3. Обновить сводную таблицу
4. Снова скопируйте в буфер обмена
5. Вставить обратно в исходную книгу
6. Отменить временную книгу

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

19. Избавьтесь от ненужных заголовков

Макетом по умолчанию для новых сводных таблиц является компактный макет. Этот макет будет отображать «Ярлыки строк» ​​и «Названия столбцов» как заголовки в сводной таблице. Это не самые интуитивно понятные заголовки, особенно для людей, которые не часто используют сводные таблицы. Простой способ избавиться от этих странных заголовков — переключить компоновку сводной таблицы с компактной на структуру или табличную. Это заставит сводную таблицу отображать фактические имена полей в виде заголовков в сводной таблице, что гораздо более разумно.Чтобы полностью избавиться от этих меток, найдите кнопку «Заголовки полей» на вкладке «Анализ» ленты «Инструменты сводной таблицы». Нажатие на эту кнопку полностью отключит заголовки.

Обратите внимание на бесполезные и сбивающие с толку заголовки полей

Переключение макета с компактного на контурный

Заголовки полей в схеме Outline намного более понятны

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

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

Небольшое белое пространство делает ваши сводные таблицы более изысканными

21. Избавьтесь от общих итогов строк и столбцов

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

Вы можете удалить общие итоги как для строк, так и для столбцов

22.Форматировать пустые ячейки

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

Для пустых ячеек установлено отображение 0 (ноль), а в числовом формате учета отображаются дефисы

23. При необходимости выключите AutoFit.

По умолчанию, когда вы обновляете сводную таблицу, столбцы, содержащие данные, автоматически корректируются для наилучшего соответствия данным. Обычно это хорошо, но это может свести вас с ума, если у вас есть другие вещи на листе вместе со сводной таблицей или если вы тщательно отрегулировали ширину столбцов вручную и не хотите, чтобы они менялись.Чтобы отключить эту функцию, щелкните правой кнопкой мыши внутри сводной таблицы и выберите «Параметры сводной таблицы». На первой вкладке параметров (или вкладке макета на Mac) снимите флажок «Автоподгонка ширины столбца при обновлении».

Параметр автоподбора столбца сводной таблицы для Windows

Параметр автоподбора столбца сводной таблицы для Mac

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

1. Щелкните отчет сводной таблицы.

2. На вкладке Анализировать в группе Данные щелкните Изменить источник данных , а затем нажмите Изменить источник данных .

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

3. Выполните одно из следующих действий:

   • Чтобы изменить источник данных сводной таблицы на другую таблицу Excel или диапазон ячеек, нажмите Выберите таблицу или диапазон , а затем введите первую ячейку в текстовое поле Таблица / диапазон и нажмите ОК

   • Чтобы использовать другое соединение, сделайте следующее:

     1. Щелкните выберите Использовать внешний источник данных , а затем щелкните Выберите соединение .

        Отображается диалоговое окно « Существующие подключения ».

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

     3. Выберите соединение в списке Выберите соединение , а затем нажмите Открыть .Что делать, если вашего подключения нет в списке?

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

        Дополнительные сведения см. В разделе Управление подключениями к данным в книге.

     4. Нажмите ОК .

Что делать, если вашего соединения нет в списке?

Если ваше соединение не указано в диалоговом окне « Существующие подключения », нажмите «Обзор дополнительных », а затем найдите источник данных, к которому вы хотите подключиться, в диалоговом окне « Выбор источника данных ».Нажмите Новый источник , если необходимо, и следуйте инструкциям мастера Data Connection Wizard , а затем вернитесь в диалоговое окно Select Data Source .

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

1. Выберите нужное соединение и нажмите Открыть .

2. Нажмите Только создать соединение .

3. Щелкните Свойства и щелкните вкладку Определение .

4. Если ваш файл подключения .odc был перемещен, перейдите в его новое расположение в поле Файл подключения .

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

1. Щелкните отчет сводной таблицы.

2. На вкладке Параметры в группе Данные щелкните Изменить источник данных , а затем щелкните Изменить источник данных .

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

3. Выполните одно из следующих действий:

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

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

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

   Отображается диалоговое окно « Существующие подключения ».

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

6. Выберите соединение в списке Выберите соединение , а затем нажмите Открыть .

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

   Дополнительные сведения см. В разделе Управление подключениями к данным в книге.

7. Нажмите ОК .

Что делать, если вашего соединения нет в списке?

Если ваше соединение не указано в диалоговом окне « Существующие подключения », нажмите «Обзор дополнительных », а затем найдите источник данных, к которому вы хотите подключиться, в диалоговом окне « Выбор источника данных ». Нажмите Новый источник , если необходимо, и следуйте инструкциям мастера Data Connection Wizard , а затем вернитесь в диалоговое окно Select Data Source .

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

1. Выберите нужное соединение и нажмите Открыть .

2. Нажмите Только создать соединение .

3. Щелкните Свойства и щелкните вкладку Определение .

4. Если ваш файл подключения .odc был перемещен, перейдите в его новое расположение в поле Файл подключения .

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

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

Создание сводной таблицы для анализа данных рабочего листа

Прежде чем начать:

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

• Таблицы

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

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

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

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

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

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

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

Рекомендуемая сводная таблица

Создание сводной таблицы вручную

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

Щелкните ячейку в диапазоне исходных данных или таблицы. Перейти к Вставить > Сводная таблица . Если вы используете Excel для Mac 2011 и более ранних версий, кнопка сводной таблицы находится на вкладке Data в группе Analysis . Excel отобразит диалоговое окно Создать сводную таблицу с выбранным диапазоном или именем таблицы. В этом случае мы используем таблицу под названием «tbl_HouseholdExpenses». В Выберите, где вы хотите разместить отчет сводной таблицы , выберите Новый рабочий лист или Существующий рабочий лист . Для существующего листа выберите ячейку, в которую вы хотите поместить сводную таблицу. Нажмите OK , и Excel создаст пустую сводную таблицу и отобразит список полей сводной таблицы .

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

Список полей сводной таблицы	Соответствующие поля в сводной таблице

• Суммировать по

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

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

  Совет: Поскольку изменение вычисления в разделе Суммировать на изменит имя поля сводной таблицы, лучше не переименовывать поля сводной таблицы, пока вы не закончите настройку сводной таблицы. Один из приемов — нажать Заменить ( в меню «Правка» )> Найти, что > « Сумма », затем Заменить на > оставить пустым, чтобы заменить все сразу, а не вводить вручную.

• Показать данные как

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

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

• Отображение значения как в виде вычисления, так и в процентах.

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

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

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

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

1. Выберите таблицу или диапазон в электронной таблице, а затем выберите Вставка> Сводная таблица .

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

2. Выполните одно из следующих действий:

• Чтобы создать сводную таблицу вручную, выберите Создать собственную сводную таблицу .

• Чтобы использовать сводную таблицу рекомендаций, прокрутите до нужного варианта и выберите + Вставить .

  Сводная таблица появится в месте назначения, и появится панель Поля сводной таблицы .

Изменение исходных данных или ячейки назначения

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

1. На панели «Вставить сводную таблицу» выберите ссылку рядом с источником или ссылку рядом с пунктом назначения . Изменения панели «Вставить сводную таблицу»:

2. Выполните одно из следующих действий:

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

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

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

.

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

Список полей сводной таблицы	Соответствующие поля в сводной таблице

• Суммировать значения по

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

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

  Совет: Поскольку изменение вычисления в разделе Суммировать значения по изменит имя поля сводной таблицы, лучше не переименовывать поля сводной таблицы, пока вы не закончите настройку сводной таблицы. Один из приемов — использовать Find & Replace ( Ctrl + H )> Find what > « Sum of », затем Replace with > оставьте пустым, чтобы заменить все сразу, а не вводить вручную.

• Показать значения как

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

  Открыв диалоговое окно « Value Field Setting» , вы можете выбрать нужный вариант на вкладке « Показать значения как ».

• Отображение значения как в виде вычисления, так и в процентах.

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

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

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

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

Существуют различные способы фильтрации данных в сводной таблице в Excel.

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

Типы фильтров в сводной таблице

Вот демонстрация типов фильтров, доступных в сводной таблице.

Давайте посмотрим на эти фильтры один за другим:

• Фильтр отчета: Этот фильтр позволяет вам детализировать подмножество всего набора данных.Например, если у вас есть данные о розничных продажах, вы можете анализировать данные для каждого региона, выбрав один или несколько регионов (да, это также позволяет выбрать несколько). Вы создаете этот фильтр, перетаскивая поле сводной таблицы в область фильтров.
• Фильтр меток строки / столбца: Эти фильтры позволяют фильтровать релевантные данные на основе элементов поля (например, фильтровать определенный элемент или элемент, который содержит определенный текст) или значения (например, фильтровать 10 первых элементов по значению или элементы со значением больше / меньше указанного значения).
  • Поле поиска: Вы можете получить доступ к этому фильтру в фильтре меток строки / столбца, и это позволяет быстро фильтровать на основе введенного текста. Например, если вам нужны данные только для Costco, просто введите здесь Costco, и он отфильтрует их за вас.
  • Флажки: Они позволяют выбирать определенные элементы из списка. Например, если вы хотите вручную выбрать розничных продавцов для анализа, вы можете сделать это здесь. Кроме того, вы также можете выборочно исключить некоторых розничных продавцов, сняв этот флажок.

Обратите внимание, что пользователю доступны еще два инструмента фильтрации: срезы и временные шкалы (которые не рассматриваются в этом руководстве).

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

Примеры использования фильтров в сводной таблице

В этом разделе рассматриваются следующие примеры:

• Отфильтровать 10 первых элементов по значению / проценту / сумме.
• Фильтровать элементы на основе значения.
• Фильтр с использованием фильтра этикеток.
• Фильтр с помощью окна поиска.

Отфильтровать 10 верхних элементов сводной таблицы

Вы можете использовать параметр фильтра 10 верхних в сводной таблице, чтобы:

• Отфильтровать верхние / нижние элементы по значению.
• Отфильтруйте верхние / нижние элементы, составляющие указанный процент значений.
• Фильтр сверху / снизу Элементы, составляющие указанное значение.

Предположим, у вас есть сводная таблица, как показано ниже:

Давайте посмотрим, как использовать фильтр Top 10 с этим набором данных.

Фильтровать верхние / нижние товары по значению

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

Вот шаги, чтобы сделать это:

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

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

Фильтр верхних / нижних элементов, составляющих указанный процент от значения

Вы можете использовать фильтр первых 10, чтобы получить список первых 10 процентов (или любое другое число, например, 20 процентов, 50 процентов и т. Д.)) товаров по стоимости.

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

Вот шаги, чтобы сделать это:

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

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

Фильтр верхних / нижних элементов, составляющих указанное значение

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

Это можно сделать с помощью фильтра «Топ 10» в сводной таблице.

Для этого:

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

Фильтровать элементы на основе значения

Элементы можно фильтровать на основе значений в столбцах в области значений.

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

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

Вот шаги, чтобы сделать это:

• Перейдите к фильтру метки строки -> Фильтры значений -> Больше чем.
• В диалоговом окне «Фильтр значений»:
  • Выберите значения, которые вы хотите использовать для фильтрации. В данном случае это сумма продаж (если у вас есть больше элементов в области значений, раскрывающийся список покажет все).
  • Выберите условие. Поскольку мы хотим, чтобы объем продаж всех розничных продавцов превышал 3 миллиона, выберите «больше чем».
  • Введите 3000000 в последнее поле.
• Нажмите ОК.

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

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

Фильтровать данные с помощью фильтров ярлыков

Фильтры ярлыков пригодятся, когда вы у вас огромный список, и вы хотите отфильтровать определенные элементы на основе его имени / текста.

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

Вот шаги, чтобы сделать это:

• Перейдите в Фильтр меток строки -> Фильтры меток -> Содержит.
• В диалоговом окне фильтра меток:
  • «Содержит» выбрано по умолчанию (поскольку мы выбрали «Содержит» на предыдущем шаге). Вы можете изменить это здесь, если хотите.
  • Введите текстовую строку, по которой вы хотите отфильтровать список.В данном случае это «доллар».
• Нажмите ОК.

Вы также можете использовать подстановочные знаки вместе с текстом.

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

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

Фильтрация данных с помощью окна поиска

Фильтрация списка с помощью поля поиска во многом похожа на параметр «содержит» в фильтре меток.

Например, если вам нужно отфильтровать всех розничных продавцов, в которых есть название «доллар», просто введите доллар в поле поиска, и он отфильтрует результаты.

Вот шаги:

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

Это позволит мгновенно отфильтровать всех розничных продавцов, содержащих термин «доллар».

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

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

Есть несколько важных вещей, которые нужно знать о строке поиска:

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

Вам также могут понравиться следующие руководства по сводным таблицам:

.