Обзор сводных таблиц и сводных диаграмм
С помощью сводной таблицы можно создавать сводки, анализировать, изучать и представлять сводные данные. Сводные диаграммы дополняют сводные таблицы, позволяя визуализировать содержащиеся в них данные. Кроме того, с помощью сводных диаграмм можно легко отображать сравнения, шаблоны и тенденции. И сводные таблицы, и сводные диаграммы помогают принимать взвешенные решения на основании критически важных данных вашей организации. Вы также можете подключаться к внешним источникам данных, например к таблицам SQL Server, кубам служб SQL Server Analysis Services, Azure Marketplace, файлам подключения к данным Office (ODC-файлам), XML-файлам, базам данных Access и текстовым файлам, чтобы создавать сводные таблицы, или создавать на основе сводных таблиц другие таблицы.
Примечание: Представленные в этой статье снимки экрана созданы в Excel 2016. Если вы используете другую версию, то в ней может быть немного другой интерфейс, но если не указано иное, функции будут такими же.
Сведения о сводных таблицах
Сводная таблица — это интерактивный способ быстрого обобщения больших объемов данных. Вы можете использовать ее для подробного анализа числовых данных и для получения ответов на разнообразные вопросы, связанные с данными. Сводные таблицы предназначены для выполнения следующих задач:
запрос больших объемов данных различными понятными способами;
подведение промежуточных итогов и вычисление числовых данных, обобщение данных по категориям и подкатегориям, создание пользовательских вычислений и формул;
развертывание и свертывание уровней представления данных для получения точных сведений о результатах, детализация итоговых данных по интересующим вопросам;
перемещение строк в столбцы или столбцов в строки («сведение») для просмотра различных сводок на основе исходных данных;
фильтрация, сортировка, группировка и условное форматирование наиболее важных и часто используемых подмножеств данных для привлечения внимания к нужным сведениям;
представление кратких, наглядных отчетов с примечаниями на веб-страницах или в напечатанном виде.
Например, слева показан простой список расходов семьи, а справа — созданная на его основе сводная таблица.
Данные о расходах семьи
Соответствующая сводная таблица
После того как вы создадите сводную таблицу, выбрав для нее источник данных, упорядочив поля в списке полей сводной таблицы и выбрав исходный макет, вы сможете при работе с ней выполнять перечисленные ниже задачи.
Изучать данные. Вот какие действия доступны.
Развертывание и свертывание данных, а также отображение базовых сведений, которые относятся к значениям.
Сортировка, фильтрация и группировка полей и элементов.
Изменение функций сведения и добавление настраиваемых вычислений и формул.
Изменять макет формы и расположения полей. Вот какие действия доступны.
Изменение формы сводной таблицы: сжатая форма, структура или табличная форма.
Добавление, изменение порядка и удаление полей.
Изменение порядка полей или элементов.
Изменение макета столбцов, строк и промежуточных итогов Вот какие действия доступны.
Включение и отключение заголовков полей строк и столбцов, а также отображение или скрытие пустых строк.
Отображение промежуточных итогов выше или ниже их строк.
Настройка ширины колонок при обновлении.
Перемещение поля столбца в область строк или поля строки в область столбцов.
Объединение или отмена объединения ячеек для внешних элементов строки и столбца.
Изменять способы отображения пустых значений и ошибок. Вот какие действия доступны.
Изменение способа отображения ошибок и пустых ячеек.
Изменение способа отображения элементов и подписей без данных.
Отображение и скрытие пустых строк.
Изменять формат. Вот какие действия доступны.
Ручное и условное форматирование ячеек и диапазонов.
Изменение стиля общего формата сводной таблицы.
Изменение числового формата для полей.
Включение форматирования сервера OLAP.
Сводные диаграммы служат для графического представления данных, содержащихся в сопоставленных с ними сводных таблицах. Как и сводные таблицы, сводные диаграммы — интерактивные объекты. При создании сводной диаграммы на экране отображается область фильтра сводной диаграммы. С помощью этой области вы можете сортировать и фильтровать исходные данные, используемые в сводной диаграмме. Изменения макета и данных в связанной сводной таблице немедленно отражаются в макете и данных в сводной диаграмме и наоборот.
Как и в обычных диаграммах, в сводных диаграммах отображаются ряды данных, категории, маркеры данных и оси. Кроме того, вы можете изменить тип диаграммы и другие параметры, например названия, расположение условных обозначений, метки данных, расположение диаграммы и т. д.
Вот сводная диаграмма, основанная на приведенном выше примере сводной таблицы.
Дополнительные сведения см. в статье Создание сводной диаграммы.
Если вы знакомы со стандартными диаграммами, то работа со сводными диаграммами не будет для вас проблемой, так как большинство действий для этих двух типов диаграмм полностью совпадают. Тем не менее существуют и некоторые отличия.
Ориентация строк и столбцов В отличие от обычной диаграммы в сводной диаграмме вам не удастся изменить ориентацию строк и столбцов с помощью диалогового окна Выбор источника данных. Вместо этого вы можете изменить ориентацию меток строк и столбцов в связанной сводной таблице.
Типы диаграмм Для сводной диаграммы вы можете выбрать любой тип диаграммы, кроме точечной, биржевой и пузырьковой.
Исходные данные Обычные диаграммы связаны непосредственно с ячейками листа. Сводные диаграммы основаны на источнике данных сопоставленных с ними сводных таблиц. В отличие от обычной диаграммы в сводной диаграмме вам не удастся изменить диапазон данных диаграммы с помощью диалогового окна Выбор источника данных.
Форматирование Большинство параметров форматирования, включая добавленные вами элементы диаграммы, макет и стиль, сохраняются при обновлении сводной диаграммы. Тем не менее линии трендов, метки данных, пределы погрешностей и другие изменения, вносимые в наборы данных, не сохраняются. В обычных диаграммах после применения такого форматирования оно не исчезает.
Несмотря на то что вам не удастся напрямую изменить размер меток данных в сводной диаграмме, вы можете увеличить размер шрифта и таким образом изменить размер меток.
В качестве основы для сводной таблицы или сводной диаграммы вы можете использовать данные листа Excel. Данные должны представлять собой список, в первой строке которого содержатся метки столбцов. Приложение Excel использует эти метки в качестве имен полей. Каждая ячейка в последующих строках должна содержать данные, соответствующие заголовку столбца. Не следует использовать данные различных типов в одном и том же столбце. Например, в одном и том же столбце не следует использовать денежные значения и даты. Кроме того, в выбранном вами диапазоне данных не должно быть пустых строк или столбцов.
Таблицы Excel Таблиц Excel изначально представлены в виде списка и отлично подходят в качестве исходных данных для сводной таблицы. При обновлении сводной таблицы новые и обновленные данные из таблицы Excel автоматически включаются в операцию обновления.
Использование динамического именованного диапазона Чтобы было проще обновлять сводную таблицу, вы можете создать динамический именованный диапазон и использовать его имя в качестве источника данных для сводной таблицы. Если вы расширите именованный диапазон, чтобы включить в него дополнительные данные, просто обновите сводную таблицу, и в нее будут включены эти новые данные.
Включение итогов Excel автоматически создает промежуточные и общие итоги в сводной таблице. Если источник данных содержит автоматически вычисляемые промежуточные и общие итоги, которые вы создали с помощью команды Промежуточный итог в группе Структура на вкладке Данные, то с помощью этой же команды удалите промежуточные и общие итоги, прежде чем создавать сводную таблицу.
Вы можете получить данные из внешнего источника данных, например базы данных, куба OLAP или текстового файла. Например, у вас может быть база данных со сведениями о продажах, для которых вы хотите создать сводку и которые вам необходимо проанализировать.
Файлы подключения к данным Office Если вы используете файл подключения к данным Office (ODC-файл) для получения внешних данных для сводной таблицы, вы можете вводить данные непосредственно в нее. Рекомендуется получать внешние данные для отчетов с помощью ODC-файлов.
Источник данных OLAP Когда вы получаете исходные данные из базы данных OLAP или файла куба, данные возвращаются в Excel только в виде сводной таблицы либо сводной таблицы, преобразованной в функции листа. Дополнительные сведения см. в статье Преобразование ячеек сводной таблицы в формулы листа.
Источник данных, не являющийся источником данных OLAP Это базовые данные для сводной таблицы или сводной диаграммы, поступающие из источника, отличного от базы данных OLAP. Например, это могут быть данные из реляционных баз данных или текстовых файлов.
Кэш сводной таблицы Каждый раз при создании новой сводной таблицы или сводной диаграммы Excel сохраняет копию данных для отчета в памяти, а также сохраняет эту область хранения данных в файле книги — это называется кэш сводной таблицы. Для каждой новой сводной таблицы требуется дополнительный объем памяти и место на диске. Однако при использовании существующей сводной таблицы в качестве источника данных для новой таблицы в той же книге обе таблицы будут использовать один и тот же кэш. Благодаря повторному использованию кэша снижается размер книги и сокращается объем данных, хранящихся в памяти.
Требования к расположению Чтобы использовать одну сводную таблицу в качестве источника данных для другой сводной таблицы, обе они должны находиться в одной и той же рабочей книге. Если исходная сводная таблица находится в другой рабочей книге, скопируйте источник в рабочую книгу, в которой вы хотите создать другую сводную таблицу. Сводные таблицы и сводные диаграммы, находящиеся в разных рабочих книгах, — это отдельные объекты, для каждого из которых имеется собственная копия данных в памяти и в рабочих книгах.
Изменения влияют на обе сводные таблицы Когда вы обновляете данные в новой сводной таблице, Excel также обновляет данные в исходной сводной таблице и наоборот. Когда вы группируете элементы или отменяете их группировку либо создаете вычисляемые поля или вычисляемые элементы в одной таблице, изменения будут внесены в обе таблицы. Если вам необходимо, чтобы одна сводная таблица не зависела от другой, создайте сводную таблицу на основе исходного источника данных, а не копируйте исходную сводную таблицу. Просто имейте в виду, что если выполнять такие операции слишком часто, это может сказаться на объеме свободной памяти.
Сводные диаграммы Вы можете создать сводную таблицу или сводную диаграмму на основе другой сводной таблицы, но вам не удастся создать сводную диаграмму непосредственно на основе другой сводной диаграммы. Изменения, вносимые в сводную диаграмму, влияют на связанную сводную таблицу и наоборот.
Изменение исходных данных может привести к тому, что для анализа будут доступны другие данные. Например, вам может быть необходимо удобно переключаться из тестовой базы данных в рабочую базу данных. Переопределив исходные данные, вы можете обновить сводную таблицу или сводную диаграмму, используя данные, структура которых похожа на структуру информации, получаемой из исходного подключения к данным. Если эти данные значительно отличаются, и в них имеется большое количество новых или дополнительных полей, возможно, будет проще создать другую сводную таблицу или сводную диаграмму.
Отображение данных, полученных при обновлении При обновлении сводной таблицы могут быть изменены данные, доступные для отображения. Для сводных таблиц, основанных на данных листа, Excel получает новые поля, имеющиеся в указанном вами исходном диапазоне или именованном диапазоне. Для отчетов, основанных на внешних данных, Excel получает новые данные, которые соответствуют критериям для базового запроса или данных, которые становятся доступными в кубе OLAP. Вы можете просматривать все новые поля в списке полей и добавлять необходимые поля в отчет.
Изменение создаваемых кубов OLAP У отчетов, основанных на данных OLAP, всегда есть доступ ко всем данным в кубе. Если вы создали автономный куб, который содержит подмножество данных, имеющихся в кубе сервера, с помощью команды Автономный режим OLAP вы можете изменить файл куба так, чтобы он содержал другие данные с сервера.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Источник
Как сделать сводную таблицу в Excel: пошаговая инструкция
Сводные таблицы – один из самых эффективных инструментов в MS Excel. С их помощью можно в считанные секунды преобразовать миллион строк данных в краткий отчет. Помимо быстрого подведения итогов, сводные таблицы позволяют буквально «на лету» изменять способ анализа путем перетаскивания полей из одной области отчета в другую.
Cводная таблица в Эксель – это также один из самых недооцененных инструментов. Большинство пользователей не подозревает, какие возможности находятся в их руках. Представим, что сводные таблицы еще не придумали. Вы работаете в компании, которая продает свою продукцию различным клиентам. Для простоты в ассортименте только 4 позиции. Продукцию регулярно покупает пара десятков клиентов, которые находятся в разных регионах. Каждая сделка заносится в базу данных и представляет отдельную строку.
Ваш директор дает указание сделать краткий отчет о продажах всех товаров по регионам (областям). Решить задачу можно следующим образом.
Вначале создадим макет таблицы, то есть шапку, состоящую из уникальных значений товаров и регионов. Сделаем копию столбца с товарами и удалим дубликаты. Затем с помощью специальной вставки транспонируем столбец в строку. Аналогично поступаем с областями, только без транспонирования. Получим шапку отчета.
Данную табличку нужно заполнить, т.е. просуммировать выручку по соответствующим товарам и регионам. Это нетрудно сделать с помощью функции СУММЕСЛИМН. Также добавим итоги. Получится сводный отчет о продажах в разрезе область-продукция.
Вы справились с заданием и показываете отчет директору. Посмотрев на таблицу, он генерирует сразу несколько замечательных идей.
— Можно ли отчет сделать не по выручке, а по прибыли?
— Можно ли товары показать по строкам, а регионы по столбцам?
— Можно ли такие таблицы делать для каждого менеджера в отдельности?
Даже если вы опытный пользователь Excel, на создание новых отчетов потребуется немало времени. Это уже не говоря о возможных ошибках. Однако если вы знаете, как сделать сводную таблицу в Эксель, то ответите: да, мне нужно 5 минут, возможно, меньше.
Рассмотрим, как создать сводную таблицу в Excel.
Создание сводной таблицы в Excel
Открываем исходные данные. Сводную таблицу можно строить по обычному диапазону, но правильнее будет преобразовать его в таблицу Excel. Это сразу решит вопрос с автоматическим захватом новых данных. Выделяем любую ячейку и переходим во вкладку Вставить. Слева на ленте находятся две кнопки: Сводная таблица и Рекомендуемые сводные таблицы.
Если Вы не знаете, каким образом организовать имеющиеся данные, то можно воспользоваться командой Рекомендуемые сводные таблицы. Эксель на основании ваших данных покажет миниатюры возможных макетов.
Кликаете на подходящий вариант и сводная таблица готова. Остается ее только довести до ума, так как вряд ли стандартная заготовка полностью совпадет с вашими желаниями. Если же нужно построить сводную таблицу с нуля, или у вас старая версия программы, то нажимаете кнопку Сводная таблица. Появится окно, где нужно указать исходный диапазон (если активировать любую ячейку Таблицы Excel, то он определится сам) и место расположения будущей сводной таблицы (по умолчанию будет выбран новый лист).
Обычно ничего менять здесь не нужно. После нажатия Ок будет создан новый лист Excel с пустым макетом сводной таблицы.
Макет таблицы настраивается в панели Поля сводной таблицы, которая находится в правой части листа.
В верхней части панели находится перечень всех доступных полей, то есть столбцов в исходных данных. Если в макет нужно добавить новое поле, то можно поставить галку напротив – эксель сам определит, где должно быть размещено это поле. Однако угадывает далеко не всегда, поэтому лучше перетащить мышью в нужное место макета. Удаляют поля также: снимают флажок или перетаскивают назад.
Сводная таблица состоит из 4-х областей, которые находятся в нижней части панели: значения, строки, столбцы, фильтры. Рассмотрим подробней их назначение.
Область значений – это центральная часть сводной таблицы со значениями, которые получаются путем агрегирования выбранным способом исходных данных.
В большинстве случае агрегация происходит путем Суммирования. Если все данные в выбранном поле имеют числовой формат, то Excel назначит суммирование по умолчанию. Если в исходных данных есть хотя бы одна текстовая или пустая ячейка, то вместо суммы будет подсчитываться Количество ячеек. В нашем примере каждая ячейка – это сумма всех соответствующих товаров в соответствующем регионе.
В ячейках сводной таблицы можно использовать и другие способы вычисления. Их около 20 видов (среднее, минимальное значение, доля и т.д.). Изменить способ расчета можно несколькими способами. Самый простой, это нажать правой кнопкой мыши по любой ячейке нужного поля в самой сводной таблице и выбрать другой способ агрегирования.
Область строк – названия строк, которые расположены в крайнем левом столбце. Это все уникальные значения выбранного поля (столбца). В области строк может быть несколько полей, тогда таблица получается многоуровневой. Здесь обычно размещают качественные переменные типа названий продуктов, месяцев, регионов и т.д.
Область столбцов – аналогично строкам показывает уникальные значения выбранного поля, только по столбцам. Названия столбцов – это также обычно качественный признак. Например, годы и месяцы, группы товаров.
Область фильтра – используется, как ясно из названия, для фильтрации. Например, в самом отчете показаны продукты по регионам. Нужно ограничить сводную таблицу какой-то отраслью, определенным периодом или менеджером. Тогда в область фильтров помещают поле фильтрации и там уже в раскрывающемся списке выбирают нужное значение.
С помощью добавления и удаления полей в указанные области вы за считанные секунды сможете настроить любой срез ваших данных, какой пожелаете.
Посмотрим, как это работает в действии. Создадим пока такую же таблицу, как уже была создана с помощью функции СУММЕСЛИМН. Для этого перетащим в область Значения поле «Выручка», в область Строки перетащим поле «Область» (регион продаж), в Столбцы – «Товар».
В результате мы получаем настоящую сводную таблицу.
На ее построение потребовалось буквально 5-10 секунд.
Работа со сводными таблицами в Excel
Изменить существующую сводную таблицу также легко. Посмотрим, как пожелания директора легко воплощаются в реальность.
Заменим выручку на прибыль.
Товары и области меняются местами также перетягиванием мыши.
Для фильтрации сводных таблиц есть несколько инструментов. В данном случае просто поместим поле «Менеджер» в область фильтров.
На все про все ушло несколько секунд. Вот, как работать со сводными таблицами. Конечно, не все задачи столь тривиальные. Бывают и такие, что необходимо использовать более замысловатый способ агрегации, добавлять вычисляемые поля, условное форматирование и т.д. Но об этом в другой раз.
Источник данных сводной таблицы Excel
Для успешной работы со сводными таблицами исходные данные должны отвечать ряду требований. Обязательным условием является наличие названий над каждым полем (столбцом), по которым эти поля будут идентифицироваться. Теперь полезные советы.
1. Лучший формат для данных – это Таблица Excel. Она хороша тем, что у каждого поля есть наименование и при добавлении новых строк они автоматически включаются в сводную таблицу.
2. Избегайте повторения групп в виде столбцов. Например, все даты должны находиться в одном поле, а не разбиты по месяцам в отдельных столбцах.
3. Уберите пропуски и пустые ячейки иначе данная строка может выпасть из анализа.
4. Применяйте правильное форматирование к полям. Числа должны быть в числовом формате, даты должны быть датой. Иначе возникнут проблемы при группировке и математической обработке. Но здесь эксель вам поможет, т.к. сам неплохо определяет формат данных.
В целом требований немного, но их следует знать.
Обновление данных в сводной таблице Excel
Если внести изменения в источник (например, добавить новые строки), сводная таблица не изменится, пока вы ее не обновите через правую кнопку мыши
или
через команду во вкладке Данные – Обновить все.
Так сделано специально из-за того, что сводная таблица занимает много места в оперативной памяти. Чтобы расходовать ресурсы компьютера более экономно, работа идет не напрямую с источником, а с кэшем, где находится моментальный снимок исходных данных.
Зная, как делать сводные таблицы в Excel даже на таком базовом уровне, вы сможете в разы увеличить скорость и качество обработки больших массивов данных.
Ниже находится видеоурок о том, как в Excel создать простую сводную таблицу.
Источник
