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

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

Создание в Excel для Windows

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

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

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

Сvotttables from other sources

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

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

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

Из модели данных

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

Из Power BI

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

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

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

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

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

Операция

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

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

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

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

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

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

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

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

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

Вставка в Excel для Интернета

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

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

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

Изменение исходных данных

При необходимости перед созданием можно изменить источник данных в pivottable.

В области Вставка pivotTable выберите текстовое поле в поле Источник. При изменении источника карточки в области будут недоступны.
Вы можете выбрать данные на сетке или ввести диапазон в текстовое поле.
Нажмите клавишу ВВОД или кнопку, чтобы подтвердить выбор. В области по-новому источнику данных будут обновляться рекомендуемые с помощью новых стеблей.

Работа с областью "Поля" в области "Поля"

В верхней части области Поля pivotTable (Поля) в верхней части выберите любое поле, добавляемое в нее.

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

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

Соответствующие поля в pivotTable:

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

Операция

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Либо:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Как сделать сводную таблицу в Excel – пошаговая инструкция для чайников

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

Это инструмент для изучения и обобщения больших объемов данных, анализа связанных итогов и представления отчетов. Они помогут вам:

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

Например, у вас множество записей в электронной таблице с цифрами продаж шоколада:

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

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

Вот посмотрите сами.

Этот скриншот демонстрирует лишь несколько из множества возможных вариантов анализа продаж. И далее мы рассмотрим примеры построения сводных таблиц в Excel 2020, 2013, 2010 и 2007.

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

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

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

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

Плюсы использования такого вида группировки данных:

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

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

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

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

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

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

Закрытие и открытие панели редактирования.

Закрыть список полей в сводной таблице так же просто, как нажать кнопку «Закрыть» (X) в верхнем правом углу панели. А вот как заставить его появиться снова – уже не так очевидно

Чтобы снова отобразить его, щелкните правой кнопкой мыши в любом месте таблицы и выберите «Показать …» в контекстном меню.

Также можно нажать кнопку «Список полей» на ленте, которая находится на вкладке меню «Анализ».

Заполнение

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

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

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

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

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

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

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

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

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

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

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

Что такое формулы в Excel и для чего они нужны?

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

Для формул в Microsoft Excel применяются стандартные математические операторы:

Рисунок 1. Что такое формулы в Excel и для чего они нужны?

Во время умножения всегда необходимо использовать знак «*». Его упущение, как в письменном варианте вычислений, недопустимо. Иными словами, если Вы запишите (4+7)8, то данную запись программа не сможет распознать, так как после круглой скобки не стоит знак умножения «*».
Программа Excel многими используется вместо калькулятора. Например, если Вы введёте в строку формул 2+3, то в выбранной ячейке отобразится результат 5.

Рисунок 2. Что такое формулы в Excel и для чего они нужны?

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

Рисунок 3. Что такое формулы в Excel и для чего они нужны?

Давайте улучшим результат.

Теперь, когда вы знакомы с основами, вы можете перейти к вкладкам «Анализ» и «Конструктор» инструментов в Excel 2020 и 2013 ( вкладки « Параметры» и « Конструктор» в 2010 и 2007). Они появляются, как только вы щелкаете в любом месте таблицы.

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

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

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

Чтобы настроить макет определенного поля, щелкните на нем, затем нажмите кнопку «Параметры» на вкладке «Анализ» в Excel 2020 и 2013 (вкладка « Параметры» в 2010 и 2007). Также вы можете щелкнуть правой кнопкой мыши поле и выбрать «Параметры … » в контекстном меню.

На снимке экрана ниже показан новый дизайн и макет.

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

Думаю, стало даже лучше.

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

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

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

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

Как создать

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Оценить статью: Загрузка... Поделиться новостью в соцсетях

Об авторе: Олег Каминский

Вебмастер. Высшее образование по специальности "Защита информации". Создатель портала comp-profi.com. Автор большинства статей и уроков компьютерной грамотности

Что такое сводные таблицы Excel 2010 и как правильно создавать сводные таблицы

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

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

Рис. П1.1. Пример таблицы в виде списка

Если у вас в таблице есть какие-нибудь промежуточные заголовки или промежуточные итоги, то их нужно удалить. Чтобы не объяснять словами всю пользу сводной таблицы, я покажу это на примере. Жмем кнопку Сводная таблица в группе Таблицы меню Вставка (рис. П1.2).

Рис. П1.2. Кнопка для создания сводной таблицы

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

Рис. П1.3. Вставка сводной таблицы

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

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

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

Рис. П1.4. Сводная таблица. Сумма

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

Рис. П1.5. Сводная таблица. Среднее значение

Я выставила итоги по среднему значению, и, как видите на рис. П1.5, теперь сводная таблица считает не сумму по месяцам и фамилиям, а среднее значение: среднюю зарплату по месяцам и среднее значение по работнику. Кроме того, вы можете по значениям сводной таблицы составить сводную диаграмму (рис. П1.6).

Рис. П1.6. Сводная диаграмма

При этом появляется группа вкладок Работа со сводными диаграммами, в которой для вас нет ничего нового. Мы все это рассматривали, когда разбирали работу обычных диаграмм. Кстати, обратите внимание: я поменяла местами строки и столбцы, поэтому итоги считаются теперь по значению столбца Остаток (см. рис. П1.6). Я сделала это просто так, чтобы вы знали, что значения столбцов, строк и поле значений можно тасовать так, как вам удобно.

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

Рис. П1.7. Вставка среза

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

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

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

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

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

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

Задание.

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

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

Открыть новую книгу.
Переименовать лист в Картотека.
Подготовить исходные данные (см. табл. 1)

Таблица 1.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

На ленте Параметры выполнить команду Активное поле → Параметры поля (рис. 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 для Windows

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

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

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

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

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

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

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

Из модели данных

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

С Power BI

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

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

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

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

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

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

Сводка значений согласно

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

90 115

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

Рекомендация: Изменение типа расчета в разделе Суммировать значения на приведет к переименованию поля сводной таблицы, поэтому переименование полей сводной таблицы не рекомендуется до завершения настройки сводной таблицы.Хороший способ — использовать функцию «Найти и заменить» ( Ctrl + H )> Найти > « Сумма » и оставить поле Заменить на пустым, чтобы заменить все сразу, а не исправлять его вручную. .

90 142

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

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

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

Отображение значений в виде расчета и процента.

Просто дважды перетащите элемент в раздел Values , затем установите Summary Values на и Show Value на для каждого.

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

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

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

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

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

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

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

Изменить исходные данные

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

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

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

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

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

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

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

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

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

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

Сводка значений согласно

По умолчанию поля сводной таблицы, расположенные под Значения , отображаются как ВСЕГО .Если Excel интерпретирует данные как текст, они будут отображаться как ЧИСЛО . Поэтому важно не смешивать типы данных в полях значений. Вы можете изменить расчет по умолчанию, щелкнув правой кнопкой мыши любое значение в строке и выбрав Summarize Values By.

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

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

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

Отображение значений в виде расчета и процента.

Просто дважды перетащите элемент в раздел Values , затем установите Summary Values на и Show Value на для каждого.

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

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

90 333

Удалить сводную таблицу

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

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

Вы когда-нибудь создавали такую сводную таблицу? Вы хотели создать выписку о покупке по регионам, поэтому поле «Сумма покупки» было бы перенесено в столбцы «Значения», а поле «Область продаж» — в столбцы «Ярлыки ». Но результаты неверны.

Как решить эту проблему?

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

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

Excel 2013 включает в себя технологию, разработанную Microsoft Research (MSR, Microsoft Research), для автоматического обнаружения и решения проблем взаимоотношений, подобных этой.

Начало страницы

Использование автообнаружения

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

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

Процесс состоит из двух фаз:

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

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

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

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

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

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

Начало страницы

Пустые строки в сводных таблицах (неизвестный элемент)

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

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

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

Начало страницы
.90 000 5 проблем со сводными таблицами и способы их решения - SkuteczneRaporty.pl
При работе со сводными таблицами в будние или праздничные дни вы сталкиваетесь с такими проблемами, как значения, видимые в фильтре, которых больше нет в исходных данных. Функция ПОЛУЧЕНИЕ ТАБЛИЦЫ ДАННЫХ и ряд других, затрудняющих использование значений в табличном отчете. Я постараюсь найти решение некоторых из этих проблем.

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

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

Который тоже виден на фильтре.

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

Тем не менее, он все еще доступен в фильтрах.

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

Данные> Количество элементов для сохранения в каждом поле> изменение с Автоматически на Нет> ОК.

При обновлении сводной таблицы я больше не вижу март среди значений фильтра сводной таблицы.

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

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

Чтобы отключить эту опцию, мне нужно изменить настройки сводной таблицы.Я устанавливаю мышь в области сводной таблицы>выбираю на ленте>Инструменты сводной таблицы>Анализ>Сводная таблица>Параметры>Отменить выбор функции Генерировать функцию сводной таблицы.

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

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

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

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

Для создания таблицы из обычных данных я настроил себя в Область данных > Вставка > Таблица.

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

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

Наведите указатель мыши на область таблицы, чтобы отобразить меню Работа с таблицами > Дизайн > Свойства > Имя таблицы.

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

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

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

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

Я навожу мышь на название месяца> щелкните правой кнопкой мыши> Настройки поля> Макет и печать> Показать элементы без данных.

После этой операции таблица выглядит так.

Загрузите файл сводной таблицы с 5 задачами и вставьте свои данные.
.
Сводные таблицы iExcel — платформа онлайн-обучения для Excel

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

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

Расчетные поля и элементы

Слайсеры и временная шкала

Обновление таблицы 3 способами

Функции в вычисляемых полях

Функции в сводных таблицах

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

Автоматическая группировка текста

.
сводных таблиц Excel | Репетиторство к экзамену на аттестат зрелости по информатике

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

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

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

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

Для чего используются сводные таблицы?

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

Перемещение данных из строк в столбцы или наоборот из столбцов в строки,

подсчет и отображение конкретных данных,

Индивидуальный поиск данных,

сортировка данных, по интересующему нас ключу,

заказ и сравнение данных,

анализирует меньший диапазон данных (т.н.переход от общего к частному).

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

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

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

Наша таблица:

Затем следует выделить таблицу и в верхнем меню выбрать:

- вставка,
- сводная таблица (первое положение).

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

- Таблица устанавливается в новый,
- или уже в существующий лист.

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

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

Нажмите на картинку для увеличения:

Важный!

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

Что можно получить с помощью сводной таблицы?

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

Как мне его получить?

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

Нажмите на картинку для увеличения:

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

В правом нижнем углу есть окно с четырьмя полями:

- Фильтры,

- колонны,

- строки,

- значения.

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

Формат столбцов в сводной таблице

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

После нажатия появится такое окно:

Нажмите на картинку для увеличения:

Затем нажмите на числовой формат, чтобы открыть другое окно:

Нажмите на картинку для увеличения:

Здесь мы можем выбрать интересующий нас формат, например.:

даты,

часов,

Процент,

обозначение валюты,

запись дробей и т. д.

После выбора интересующего вас формата нажмите OK. После закрытия первого окна снова нажмите OK. Потом будут изменения.

Средний общий объем продаж — Магические команды сводной таблицы

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

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

всего,

счетчик,

товар,

максимум.

Достаточно выбрать то, что нас интересует и правильно отформатировать числа.

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

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

Вы можете выполнить действие двумя способами:

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

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

Визуализация сводной таблицы

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

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

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

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

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

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

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

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

можно также традиционно выделить интересующий вас элемент, и щелкнуть по нему правой кнопкой мыши и выбрать из окна - . Удалить поле. Здесь вы должны помнить, что если вы нажмете на одну строку, Excel удалит весь столбец (который виден в команде).

.
Скидка -40% на онлайн курс Сводные таблицы

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

Сводные таблицы — применение и преимущества

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

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

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

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

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

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

Помимо участия в онлайн-обучении вы получаете:

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

доступ к преподавателю курса, который поддержит вас и проверит вашу домашнюю работу,

доступ к архиву онлайн встреч с участниками курса,

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

Сводные таблицы - МЕГА АКЦИЯ!

Почему я об этом пишу?

Потому что я подготовил интересную акцию на курс Сводные таблицы, представляющий собой простой макет с большой скидкой ДЛЯ САМЫХ БЫСТРЫХ .

Скидки, которые могут получить первые 40 человек, оооочень большие, даже -40%!

Ниже приведена информация о том, как работают коды. Пожалуйста, загружайте осторожно, и прежде всего БЕГИТЕ БЫСТРО ! 🙂

Акция работает так:

для первых 10 человек купивших курс Сводные таблицы и введших код BLACK40 будет работать скидка -40% ,

для следующих 10 человек купивших курс Сводные таблицы и введших код BLACK30 будет работать скидка -30% ,

для следующих 10 человек купивших курс Pivot Tables и введших код BLACK20 будет работать скидка -20% ,

для следующих 10 человек купивших курс Сводные таблицы и введших код BLACK10 будет действовать скидка -10% .

Воспользуйтесь акцией и купите прямо сейчас: онлайн-курс Pivot Tables

Сводные таблицы — помните об этом! Не теряйте возможности!

Напоминаю, что это последняя возможность присоединиться к этому курсу в 2019 году и с таким количеством бонусов.

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

Поэтому, если вы хотите получить проверенные знания в области сводных таблиц, принимайте решение очень быстро. Распродажа продлится до пятницы 20:00!

Воспользуйтесь акцией и купите прямо сейчас: онлайн-курс Pivot Tables

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

Урок 28. Дополнительные возможности сводных таблиц

/ en / tr_pl-excel / введение в сводные таблицы / содержание /

Введение

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

Необязательно: Загрузите нашу рабочую книгу.

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

Фильтры

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

Чтобы добавить фильтр:

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

Перетащите поле из списка полей в область Фильтры . В этом примере мы будем использовать поле Поставщик .

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

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

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

Слайсер

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

Чтобы добавить слайсер:

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

На вкладке Анализ нажмите Вставить Слайсер .

Появится диалоговое окно. Установите флажок рядом с нужным полем . В нашем примере мы выбираем Vendor , затем нажимаем OK .

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

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

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

Сводные диаграммы

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

Чтобы создать сводную диаграмму:

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

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

На вкладке Вставка щелкните Сводная диаграмма .

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

Появится сводная диаграмма.

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

Вызов!

Откройте нашу рабочую тетрадь.

В Rows удалите Region и замените его на Salesperson.

Вставьте PivotChart и выберите тип Linear с маркерами .

Используйте слайсер для регионов.

Используйте слайсер , , чтобы показать только регионы Юг и Восток .

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

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

По завершении ваша книга должна выглядеть примерно так:

/ en / tr_pl-excel / условный анализ / содержание /
.
Смотрите также

Где на клавиатуре символы

Телефон быстро разряжается и греется

Администрирование как зайти

Как поменять возраст в аккаунте майкрософт

Выбранный файл не является допустимым образом диска

Информация о конфигурации компьютера

Как включить историю файлов в windows 10

Блокируется ноутбук сам по себе

On the go в смартфоне это что

Отправка сообщений в whatsapp

Что такое ptt в биосе