Конструктор сводных таблиц в excel

Обновлено: 25.04.2024

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

Если, не найдёте ответ на свой вопрос, пишите в комментариях.

Плюсы Сводных таблиц:

Лёгкость создания отчетов по большому объему данных;
Простота редактирования и изменения вида;
Возможность группировать данные в диапазоны (например, даты объединить в кварталы или месяца, числа в интервалы);
Можно добавить поля с расчетами, которых в исходной таблице нет;
Данные из Сводных можно быстро визуализировать, построив график или диаграмму и даже создать не сложный интерактивный Дашборд (Dashboard).

Всё это делается в пару кликов мыши.

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

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

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

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

Чтобы быстро создать Сводную таблицу, встаньте на любую ячейку таблицы с источником данных, выберите на вкладке Вставка ► Рекомендуемые Сводные таблицы и выберите подходящий вариант из предложенных и нажмите Ok :

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

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

Если, не нашли подходящий вариант, создайте свой. Выберите на вкладке Вставка ► Сводная таблица :

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

После настройки, нажмите Ok .

На месте вставки таблицы появится такое поле:

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

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

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

Теперь поговорим о полях.

Черный треугольник в правом верхнем углу:

Нажав на него вы сможете переместить поле, изменить его размер или закрыть. Крест закрывает окно.

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

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

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

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

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

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

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

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

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

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

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

Полезное по теме:

Чек-лист ► обратить внимание при подготовке данных:

Спасибо, что дочитали до конца!

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

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

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

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

На вкладке Вставка нажмите кнопку Сводная таблица.

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

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

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

Примечание: В зависимости от параметров ИТ-среды
организации в кнопку может быть включено имя вашей организации. Например, "Из Power BI (Майкрософт)"

Получение из внешнего источника данных

Получение из модели данных

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

Получение из Power BI

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

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

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

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

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

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

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

Совет: Так как при изменении способа вычисления в разделе Операция обновляется имя поля сводной таблицы, не рекомендуется переименовывать поля сводной таблицы до завершения ее настройки. Вместо того чтобы вручную изменять имена, можно нажать кнопку Найти и заменить (CTRL+H), в поле Найти ввести Сумма по полю, а поле Заменить оставить пустым.

Дополнительные вычисления

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

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

Отображение значения как результата вычисления и как процента

Просто перетащите элемент в раздел Значения дважды, а затем настройте параметры Операция и Дополнительные вычисления для каждой из копий.

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

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

На странице "Создание собственного сводная таблица" выберите новый лист или существующий лист, чтобы выбрать назначение сводная таблица.

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

Примечание: Рекомендуемые сводные таблицы доступны только Microsoft 365 подписчикам.

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

В области сводная таблица "Вставка" выберите текстовое поле в разделе "Источник". При изменении источника карточки на панели будут недоступны.

Выберите данные в сетке или введите диапазон в текстовом поле.

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

Получение из Power BI

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

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

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

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

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

Дополнительные вычисления

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

Сделайте выбор из списка.

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

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

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

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

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

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

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

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

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

На вкладке Вставка нажмите кнопку Рекомендуемые сводные таблицы.

Excel проанализирует данные и предоставит несколько вариантов, как в этом примере:

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

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

На вкладке Вставка нажмите кнопку Сводная таблица.

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

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

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

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

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

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

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

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

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

Дополнительные вычисления

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

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

Отображение значения как результата вычисления и как процента

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

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

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

С помощью сводной таблицы можно создавать сводки, анализировать, изучать и представлять сводные данные. Сводные диаграммы дополняют сводные таблицы, позволяя визуализировать содержащиеся в них данные. Кроме того, с помощью сводных диаграмм можно легко отображать сравнения, шаблоны и тенденции. И сводные таблицы, и сводные диаграммы помогают принимать взвешенные решения на основании критически важных данных вашей организации. Вы также можете подключаться к внешним источникам данных, например к таблицам 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.

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

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

✨ А прежде, чем мы начнем 📣 напомню, теперь все видео 📽 предыдущих уроков доступны в 👉 одном месте .

📚 По уже сложившейся традиции. немного теории

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

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

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

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

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

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

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

📝 Изучение рассмотрим на примере.

Пусть мы имеем некоторую таблицу содержащую следующие данные:

Для того, чтобы создать сводную таблицу нужно:

📍 ШАГ 1. Выделить любую ячейку таблицу и на вкладке Вставка [Insert], в группе Таблицы [Table], выбрать Сводная таблица [PivotTable]

📍 ШАГ 2. В диалоговом окне Создание сводной таблицы [Create PivotTable] проверить правильность выделения диапазона данных или выделить новый источник данных в поле Таблица или диапазон [Table/Range]:

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

📍 ШАГ 3. Определить место размещения сводной таблицы: На новый лист [New Worksheet] или На существующий лист [Existing Worksheet]

📍 ШАГ 4. Жмем 🆗

И получаем результат. На листе появилась графическая область с указанием имени сводной таблицы - Сводная таблица 1 [PivotTable 1].

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

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

📍 ШАГ 5. Сформировать отчет сводной таблицы

Чтобы начать работу с отчетом сводной таблицы, щелкните в этой области [Click in this area to work with the PivotTable report]

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

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

Фильтры [Filters] – фильтр отчета сводной таблицы. Если установлен фильтр, то построение и расчет данных сводной таблицы ведется для заданного значения.

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

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

Значения [ Values] – обязательная область макета для размещения полей, по которым подводятся итоги, согласно выбранной функции. Размещаемые здесь поля могут быть произвольных типов. Если в расчетной области расположено несколько полей, то в области макета Столбцы [Columns] автоматически появляется поле  Значения [ Values], которое можно при необходимости переместить в область Строки [Rows].

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

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

Добавить в фильтр отчета [Add to Report Filter],
Добавить в названия строк [Add to Row Labels],
Добавить в названия столбцов [Add to Column Labels],
Добавить в значения [Add to Values]

В условиях рассматриваемого примера поместим поля в следующие области раздела макета :

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

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

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

Вариант 1: Обычная сводная таблица

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

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

Вариант 2: Мастер сводных таблиц

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

Переходим в пункт меню «Файл» и кликаем на «Параметры».

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

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

«Дата»

«Выделить несколько элементов»

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

Мы рады, что смогли помочь Вам в решении проблемы.

Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

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