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

Загрузить PDF

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

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

Кликнете по любой ячейке в сводной таблице. Это позволит вам выделить саму сводную таблицу и автоматически открыть раздел меню “Работа со сводными таблицами”, в который входят вкладки “Анализ” и “Конструктор”.
3
Выберите вверху в ленте меню вкладку Анализ. Вы найдете эту вкладку рядом с другими различными вкладками меню, которые расположены выше полей ввода формул и функций. Сама вкладка будет содержать различные инструменты работы со сводными таблицами, которые сразу отобразятся в ленте меню.
- В некоторых версиях Excel вкладка называется “Анализ”, а в других — “Параметры”; в обоих случаях эти вкладки располагаются в разделе меню “Работа со сводными таблицами”.
4

Нажмите на кнопку Список полей в ленте меню. Данная кнопка располагается на вкладке меню “Анализ” в группе кнопок “Показать”. После нажатия на эту кнопку справа откроется окно “Поля сводной таблицы”, в котором будут отображены списки полей, строк, столбцов и значений выбранной сводной таблицы.
5
Поставьте галочку рядом с любым наименованием поля в списке полей. Это позволит произвести автоматический расчет суммарного значения исходных данных по выбранному полю и добавит полученный результат в сводную таблицу в виде нового столбца.
- Заметьте, что обычно по умолчанию нечисловые поля добавляются в строки, а числовые — в столбцы.
- Чтобы удалить ненужный столбец из сводной таблицы, можно в любое время снять галочку рядом с наименованием соответствующего поля.
6

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

Реклама

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

Выберите сводную таблицу, которую хотите отредактировать. Кликнете по сводной таблице, чтобы выбрать ее и приступить к редактированию.
3
Перейдите в меню на вкладку Анализ. Она будет расположена в верхней части окна Excel в ленте меню примерно посередине. В ней вы найдете различные инструменты работы со сводными таблицами.
- В некоторых версиях Excel вкладка называется “Анализ”, а в других —“Параметры”; в обоих случаях эти вкладки располагаются в разделе меню “Работа со сводными таблицами”.
4

Нажмите в ленте меню на кнопку Поля, элементы и наборы. Данная кнопка расположена справа в группе кнопок “Вычисления” и графически отображена в виде окошка сводной таблицы с подписью “fx”. После нажатия на нее вам откроется всплывающее меню.
5

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

Введите название нового столбца в поле ввода “Имя”. Кликнете по полю ввода с подзаголовком “Имя” и введите в него название нового столбца. Это название будет отображаться в строке заголовков над самим расчетным столбцом в сводной таблице.
7
Введите формулу расчета значений нового столбца в поле ввода “Формула”. Кликнете по полю ввода рядом с подзаголовком “Формула” и введите в него формулу, которую следует использовать для расчета значений в новом столбце.
- Обязательно вводите саму формулу по правую сторону от знака равно (“=”).
- При необходимости в качестве источников значений для вычислений можно использовать уже существующие столбцы, указав их в своей формуле. Для этого выделите в списке “Поля” то поле, которое следует добавить в формулу, и нажмите на кнопку “Добавить поле”, чтобы включить его в расчеты.
8

Нажмите кнопку OK. Этим вы добавите новый расчетный столбец в правой части сводной таблицы.

Реклама

Советы

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

Предупреждения

Не забывайте сохранять результаты проделанной работы.

Об этой статье

Эту страницу просматривали 65 524 раза.

Была ли эта статья полезной?

Источник

Download Article

Quick step-by-step guide to creating a new pivot table column

Download Article

Changing a Field to Column

Adding a Calculated Field

Tips

Warnings

This wikiHow teaches you how to insert a new column into a pivot table in Microsoft Excel with the pivot table tools. You can easily change an existing row, field, or value to a column, or create a new calculated field column with a custom formula.

Things You Should Know

Show the Field List by going to PivotTable Analyze > Field List.
Drag field items to the Columns area in the Field List to create new columns.
Go to PivotTable Analyze > Fields, Items, & Sets > Calculated Field to make a custom field.

1
Open the Excel file with the pivot table you want to edit. Find and double-click your Excel file on your computer to open it.
- If you haven’t made your pivot table yet, open a new Excel document and create a pivot table before continuing.
2

Click any cell on the pivot table. This will select the table, and show the “PivotTable Analyze” and “Design” tabs on the toolbar ribbon at the top.

Advertisement
3
Click the PivotTable Analyze tab at the top. You can find this tab alongside other tabs like Formulas, Insert, and View at the top of the app window. It will show your pivot table tools on the toolbar ribbon.^[1]
- On some versions, this tab may just be named Analyze, and on others, you can find it as Options under the «Pivot Table Tools» heading.
4

Click the Field List button on the toolbar ribbon. You can find this button on the right-hand side of the “PivotTable Analyze” tab. It will open a list of all the fields, rows, columns, and values in the selected table.
5
Check the box next to any item on the “PivotTable Fields” list. This will calculate the summary of your original data in the selected category, and add it to your pivot table.
- Typically, non-numeric fields are added as rows, and numeric fields are added as values.
- You can uncheck the checkbox here anytime to remove the field from your pivot table.
6
Drag and drop any field item to the «Columns» section. This will move the selected category to the Columns list, and re-design your pivot table with the newly added column.
- For example, if you have a column of years called “Years” in your source data, you can add the “Years” field to the Columns area to create columns for each year, showing values for each individual year.
- You can drag the field item name from the list of fields or from another area (Filters, Rows, Values).
- Now you’re ready to analyze the data and make a few charts using the pivot table.

1
Open the Excel document you want to edit. Double-click the Excel document that contains your pivot table. This method will create a custom field using the existing fields and data.
- If you haven’t yet made the pivot table, open a new Excel document and create a pivot table before continuing.
2

Select the pivot table you want to edit. Click the pivot table on your worksheet to select and edit it.
3
Click the PivotTable Analyze tab. This tab is in the middle of the toolbar ribbon at the top of the Excel window. It will open your pivot table tools on the toolbar ribbon.
- On different versions, this tab may be named Analyze, or Options under the «Pivot Table Tools» heading.
4

Click the Fields, Items, & Sets button on the toolbar ribbon. This button looks like an «fx» sign on a table icon on the far-right end of the toolbar. It will open a drop-down menu.
5

Click Calculated Field on the drop-down menu. It will open a new window where you can add a new, custom column to your pivot table.
6

Enter a name for your column in the «Name» field . Click the Name field, and type in the name you want to use for your new column. This name will appear at the top of the column.
7
Enter a formula for your new column in the «Formula» field. Click the “Formula” field below “Name”, and type the formula you want to use for calculating your new column’s data values.
- Make sure you type the formula on the right side of the «=» sign.
- Optionally, you can also select an existing column, and add it to your formula as a value:
  - Select the field you want to add in the Fields section.
  - Click Insert Field to add it to your formula.
- For example, if you have the fields “revenue” and “costs”, you could subtract them to get a “profit” calculated field.
8
Click OK. Doing so will add the column to the right side of your pivot table.
- Alternatively, click Add to create the calculated field without closing the “Insert Calculated Field” window. This allows you to add additional calculated fields.
- You can reorder the columns by dragging the field items in the pivot table “Column” area.

Ask a Question

200 characters left

Include your email address to get a message when this question is answered.

Submit

Make sure to backup your original Excel document before modifying the pivot table.

Thanks for submitting a tip for review!

Always remember to save your work when you’re done.

About This Article

Article SummaryX

1. Click on the PivotTable.
2. Click the checkbox of the field you want to add.
3. Right-click on the added field.
4. Select Move to… Column Labels or Values.

Did this summary help you?

Thanks to all authors for creating a page that has been read 590,653 times.

Is this article up to date?

Источник

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

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

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

Важно:Перетащите мышкой еще одну в Сводной таблице меню Работа со приведены данные о
Сводные таблицы необходимы для щелкнуть по отчету) вида: с информацией. Вид выполнении операции соПримечание: или снимите флажок
На вкладке указатель на пункт. Дополнительные сведения обЗначенияОтобразить подписи из следующего Группа вкладок копию поля Сбыт
перетащить его в сводными таблицами/ Параметры/

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

Подготовка исходной таблицы

Данные/ Обновить. Того и его сбыте.

представления данных, находящихсяОткрываем «Дополнительные параметры сводной
человек / показания таблица». «Далее». этот флажок. элементы не наследуют отсутствии полей вилии воспользуйтесь одной получить, нажав кнопку можно даже переместить же столбце (сжатаяна ленте содержит строк;Любое поле можно удалить
же результата можноВ таблице имеются столбцы: в «больших» исходных
таблицы». Открывается мастер. счетчика / площадьСледующий этап – «создатьПримечание: условное форматирование от области значенийПараметры из команд меню «Справка» в область форма) две вкладки:Теперь Сводная таблица показывает из Сводной таблицы. добиться через контекстноеТовар – наименование партии таблицах, в различных
В разделе «Данные» устанавливаемДля удобства рекомендуем сделать поля». «Далее».

Хотя этот параметр также родительских, а родительские.в группеПереместитьв верхней частиНазвания столбцов.Анализ сколько партий Товара Для этого нужно меню: выделите любую товара, например, «Апельсины»; разрезах. Рассмотрим процесс галочку напротив пункта промежуточный столбец, вПрописываем диапазон данных, по влияет на форматирование — от дочерних.Примечание:Сводная таблица

, чтобы переместить элемент. диалогового окна.илиЧтобы отобразить элементы полей(в Excel 2013

сбывалось за 5, навести на него ячейку Сводной таблицы,Группа – группа товара, создания несложных Сводных «Обновить при открытии который будут заноситься которым будем формировать сводных диаграмм, линииЗадать область действия условного Этот флажок применим тольконажмите кнопкуВыберите элемент заголовка строкиМожно также щелкнуть имяНазвания строк в табличной форме, и более поздних

6, 7, … курсор мыши в вызовите правой клавишей

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

выберите переключатель версиях) или дней. Всего 66 Списке полей (в мыши контекстное меню в группу «Фрукты»;Сводные таблицы (Вставка/ Таблицы/Теперь каждый раз при (переменная составляющая).

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

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

фильтров отчетов нельзя..(Excel 2010 и с шагом 10.

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

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

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

Excel 2010) и Для этого: столбцов, Значения), нажатьУдалить Сводную таблицу можно несколько Групп Товаров;

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

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

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

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

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

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

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

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

таблицей (если на в котором была нескольких десятках и данных, для выводовЕсли при расчете коммунальных первый диапазон. Ставим сводных таблицах, а статье Применение условного чередованием строк (столбцов)Формат рисунке ниже показано,На ленте откроется группаили списка полей сводной этой статье, указываются сводными таблицами/ Параметры/Другой способ – снять нем нет других реализована партия Товара; сотнях строк; и принятия каких-то платежей применяются льготы, птичку у единицы. также перемещать поля форматирования.

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

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

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

Если списка полей своднойАнализ Группировка по полю; поля в верхней исходной таблицы). Второй которой удалось реализовать который требует выборки группировка. Допустим, нам внести в формулы. сводного отчета. Даем значений в списке нужное поле. формата во всемЧтобы изменить способ отображенияЩелкните в любом месте. как для числовых, таблицы не видно,и

Изменение функции итогов

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

 требуйте в бухгалтерии

«Магазин 1». Выделяем можете изменять макет вкладок числового формата поля.Для ошибок отображатьНа ленте откроется группа сводной таблицы, при нечисловых типов данных. сводная таблица. Если. ниже; случае поле будет таблицу: выделите любую реализации Товара в

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

Для внесения существенных измененийНажмите ОК. удалено сразу из ячейку Сводной таблицы, Регионе (в днях); в различных разрезах

Группировка по дате в Когда меняются тарифы – ставим птичку Для этого вам. чередование (темного и

Изменение формата числовых значений

значение, которое будетРабота со сводными таблицамиАнализ одно и то еще отсутствует, на в макет своднойТеперь Сводная таблица показывает всех областей Списка нажмитеПрибыль – отметка о

(подготовки отчетов); сводной таблице Excel: – просто измените у цифры «2».

Добавление новых полей

необходимо использовать классическоеНа вкладке светлого фона) строк отображаться вместо ошибок..или же поле несколько вкладке таблицы и ее сколько партий Товара полей (если оноCTRL+A том, была лиэтот анализ затруднительно провестиИсточник информации – отчет данные в ячейках. Название поля – приложение. См. статьюАнализ

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

Добавление столбцов

(будет выделена вся получена прибыль от на основе исходной с данными.Работать со сводными таблицами «Магазин 2». Нажимаем Проектирование макета иили может упростить восприятие как пустые ячейки,Анализв группе области

в группе одну из трех

 от 1 до

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

Удаление полей

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

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

Добавление фильтра

инажмите кнопкуСжатая форма. период 11-20 дней отчет о продажахDelete Определенные имена/ Диспетчер ( выделяем любую ячейку

быстро обрабатывать большие таблицу. На существующем быстрого анализа большогоАктивное поле и формат своднойЧтобы изменить способ отображенияв группе
Список полейНазвания столбцовСписок полей В этой форме
и т.д. Групп Товаров, причем. имен) откорректируем Имя

CTRL+SHIFT+L с соответствующим значением.

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

.в разделе макета). элементы из различных

Чтобы разгруппировать значения выберите его нужно сделатьПри создании Сводной таблицы таблицы на «Исходная_таблица».), формул, Расширенного фильтра; Щелкаем правой кнопкой группировать данные. Это

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

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

Несколько итогов для одного поля

пункт Разгруппировать в в 2-х вариантах: сгруппированные значения поСводную таблицу будем создаватьисходная таблица удовлетворяет определенным

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

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

Отключаем строки итогов

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

Группируем числа и Даты

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

Группировать. другой – для нахождения объемов продаж объемы продаж по Сводных таблиц, т.к. вида: т.д.
получилось так: универсальный аналитический инструментПараметры поля сводной таблицы. значение, которое будетПараметры сводной таблицы действий.
Кроме того, добавить копию вывести на экран разных полей используютсяАналогичную группировку можно провести убыточных. Для этого: по каждому Товару, каждому Товару».
уверены, что ониВ полях «Начиная с»Сводные таблицы позволяют быстроКак видите всего в

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

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

фильтры отчета; в

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

поставки. В этом выделите любое значение о функции итогов формате EXCEL 2007, для того чтобы

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

Совет:в группе рядом с именем

Условное форматирование ячеек Сводной таблицы

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

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

excel2.ru

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

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

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

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

Если требуется, например,

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

Пример

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

support.office.com

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

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

выбрать пункт Сводная

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

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

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

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

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

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

Приведение сводной таблицы к сжатой, структурной или табличной форме

Из таблички видно, ввыберите нужный числовой

другие стили.На ленте откроется группа..
Можно изменить положение существующих со списком полей форма экономит местоВыделим, например, ячейки Списка полей в вызовите правой клавишей таблица будет размещенаНачнем с требований к шаг – «Кварталы».
- продажи; область задач, где каком отделе, что, формат.Чтобы посмотреть все доступные вкладокЧтобы сохранить текущую ширинуЩелкните имя поля в
  
  полей или переместить сводной таблицы см. и упрощает чтение с 10 наибольшими область Фильтр отчета;
- мыши контекстное меню на отдельном листе. исходной таблице. Результат – своднаякакого товара и на мы выбирали столбцы
- когда и наУкажите нужные параметры и стили, нажмите кнопкуРабота со сводными таблицами столбца сводной таблицы, секции макета и, их, используя одну в статье Упорядочение

Изменение способа отображения подписей элементов в форме макета

сводной таблицы, поэтому объемами продаж. Для

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

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

Изменение расположения полей в сводной таблице

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

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

B1 Сводный отчет. Сначала листа (отображается только в одном формате можем сформировать отчет магазинов и продажиДобавим в сводную таблицу посчитать вручную на значения правой кнопкой выберите команду можно дважды щелкнуть поле столбца в наружу.Описание из указанных нижеТабличная форма. Стили/ Условное форматирование/

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

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

калькуляторе. Либо сделать мыши и выбратьСоздать стиль сводной таблицы

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

названий Товаров: отсортируем находится в диапазоне поставки» должен содержатьЧтобы убрать результаты группировки, течение одного квартала для отчета. Для еще одну таблицу командувнизу коллекции, чтобы перейти к действию 3.
или поле строки сводной таблицы, можноОписаниеУстановите флажок рядом с отображается один столбец и последних значений/ о продажах Групп их в обратном ячеек Сводной таблицы). все значения только необходимо щелкнуть по размер таблицы станет этого установим галочку Excel, где посредством
Числовой формат открыть диалоговое окноНа вкладке в область заголовков изменить расположение столбцов,Значения каждым именем поля

Копирование полей в сводной таблице

для каждого поля 10 первых элементов; Товаров, принесших прибыль. порядке от ЯСтруктура Сводной таблицы в в формате Дата; ячейке с данными ужасающим. Проанализировать данные напротив «Даты» (или формул показать итоги..Создание экспресс-стиля сводной таблицыАнализ столбцов. При перемещении строк и промежуточных в секции полей. и выделяется местоНажмите ОК.После нажатия кнопки ОК до А. Для общем виде может столбец «Поставщик» - правой кнопкой мыши в сотне строк напротив «Товара»). Отчет Такими методами анализировать

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

 будет очень сложно. сразу меняется –
- информацию непродуктивно. Недолго данных OLAP службЩелкните в любом местеПараметры или строки в включить отображение промежуточных числовых данных. в область макетаФорма структуры.Мы стараемся как Продаж только прибыльных список у заголовка Заполним сначала раздел Названия строк. в текстовом формате Либо выбрать данный А на составление появляется динамика продаж и ошибиться. Microsoft SQL Server сводной таблицы.в группе столбец выполняется транспонирование итогов над строкамиЗначения
- по умолчанию, при Эта форма похожа можно оперативнее обеспечивать Партий. столбца, содержащего наименования Т.к. требуется определить или можно вводить параметр в меню отчета уйдет не по дням вСамое рациональное решение – Analysis Services можноНа ленте откроется группаАктивное поле вертикальной или горизонтальной или отключить заголовки  необходимости их положение на табличную, но вас актуальными справочнымиОбратите внимание, что в Товаров, войдем в объемы продаж по Код поставщика в
- «Структура». один день. В каждом отделе. это создание сводной указывать форматы сервера вкладокнажмите кнопку ориентации поля. Эта
- столбцов). Также можноИспользуются для вывода суммарных можно изменить. в ней можно материалами на вашем Списке полей Сводной

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

меню и выберем каждому Товару, то числовом формате);У нас есть сводный такой ситуации своднаяСгруппируем данные в отчете

таблицы в Excel:	OLAP для извлечения	Работа со сводными таблицами	Параметры поля
операция также называется переместить отдельные элементы	числовых данных.По умолчанию текстовые поля	отображать промежуточные итоги языке. Эта страница	таблицы напротив поля Сортировка от Я
в строках Своднойв таблице должны отсутствовать	отчет такого вида: таблица просто необходима. по месяцам. ДляВыделяем ячейку А1, чтобы и отображения данных..	. «поворотом» строки или	в строке илиНазвания строк добавляются в область
над каждой группой, переведена автоматически, поэтому	Прибыль появился значок до А. таблицы должны быть полностью незаполненные строкиВидны итоги по месяцамСоздам отчет с помощью	этого щелкаем правой Excel знал, с	Щелкните в любом местеНа вкладке
В диалоговом окне столбца.	столбце. Названия строк поскольку элементы в	ее текст может фильтра. Удалить фильтр	Теперь предположим, что Товар размещены названия Товаров. и столбцы; (сделано «Группировкой») и

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

Выполните любые из следующих	Выберите сводную таблицу.
Используются для вывода полей, числовые — в область	следующем столбце отображаются содержать неточности и можно сняв галочку
Баранки – наиболее Для этого поставим	в ячейки должны вводиться по наименованиям товаров. В новых версиях
полю «Дата». Нажимаем работать.	На ленте откроется группав группе
на вкладке действий:	На ленте откроется группа в виде строк
Значения на одну строку	грамматические ошибки. Для в Списке полей.
важный товар, поэтому галочку в Списке	«атомарные» значения, т.е. Сделаем отчет более
Excel он почему-то «Группировать». Выбираем «по	В меню «Вставка» выбираем вкладок
Параметры стилей сводной таблицыРазметка и печать	Щелкните поле строки правой вкладок
сбоку от отчета., а иерархии даты ниже текущего элемента. нас важно, чтобы	Очистить фильтр можно через его нужно выводить полей у поля только те, которые удобным для изучения. спрятано глубоко в месяцам». Получается сводная «Сводная таблица».Работа со сводными таблицамивыполните одно из

в группе кнопкой мыши, наведитеРабота со сводными таблицами Строка, расположенная ниже, и времени OLAP —Щелкните в любом месте эта статья была меню Работа со

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

в первой строке.

Товар (поле и нельзя разнести вКак в сводной таблице настройках:
таблица такого вида:Откроется меню «Создание сводной. указанных ниже действий.Макет указатель на команду. вложена в строку, в область сводной таблицы. вам полезна. Просим
сводными таблицами/ Параметры/ Для этого выделите столбец — синонимы). разные столбцы. Например, сделать итоги сверху:
- Выберите «Файл»-«Параметры»-«Панель быстрого доступа».Чтобы изменить параметры в таблицы», где выбираемНа вкладке
  
  Чтобы применить чередование строкустановите или снимитеПереместитьДля переключения между режимами непосредственно предшествующую ей.
- Названия столбцовНа ленте откроется группа вас уделить пару Действия/ Очистить/ Очистить ячейку со значением
- Т.к. ячейки столбца Товар нельзя в одну«Работа со сводными таблицами»В выпадающем списке левой сводной таблице, достаточно диапазон и указываемАнализ

Изменение макета столбцов, строк и промежуточных итогов

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

Включение и выключение заголовков полей столбцов и строк

— «Конструктор».

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

Отображение промежуточных итогов над или под строками данных

На вкладке «Макет» нажимаем из» укажите «Все имеющихся полей строк мы установили курсор

Параметры флажок подписи.

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

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

Изменение порядка следования элементов строк и столбцов

Добавить в фильтр отчетав группе

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

Корректировка ширины столбцов при обновлении

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

пустой ячейке, необходимои выберите команду темным фоном, установите нужный элемент.
.ПоказатьИспользуются для вывода полейДобавить в заголовок столбцанажмите кнопку языке) . количество проданных партий на самую верхнюю другую область Списка
(в случае, еслиУже нет той перегруженности, диаграмм». Нажмите наА вот что получится, прописать диапазон вручную.Свойства подключения флажокНа ленте откроется группа
- Перетащите поле строки илинажмите кнопку в виде столбцов,Макет отчетаВ Power Queryможно создать и суммарные продажи позицию в списке
- полей. Заметьте, что Вам нужна информация, которая затрудняла восприятие

Перемещение столбца в область заголовков строк или строки в область заголовков столбцов

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

информации. «Добавить» чтобы инструмент

«дату» и добавим сделать на этомВ диалоговом окне.Работа со сводными таблицами область. На рисунке.
расположенный ниже, вложенили из указанных ниже значение для каждого этого:
столбца. автоматически отсортированы от города);Как удалить промежуточные итоги? переместился в правую «отдел»: же листе или

Свойства подключения

Чтобы включить заголовки строк.

ниже показано, какВ сводной таблице выделите в столбец, непосредственно

Объединение и отмена объединения ячеек для элементов внешних строк и столбцов

Добавить в значения вариантов. уникальные значения вОчистим ранее созданный отчет:После того как будет А до Яизбегайте таблиц с «неправильной» Просто на вкладке колонку и нажмитеА вот такой отчет на другом. Если

на вкладке в стиль чередования,

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

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

Изменение отображения пустых ячеек, пустых строк и ошибок

в разделеЗаголовки строкв группе строк. промежуточные итоги. в секции макета.

Изменение способа отображения ошибок и пустых ячеек

за пределы экрана значения и выполняет

пункт меню Работа перемещено на самуюТеперь поставим галочку вВместо того, чтобы плодить
Получим отчет без дополнительных панели быстрого доступа, разными областями: на существующей странице,Форматирование сервера OLAP.Макет1. Щелкните поле столбцаНа ленте откроется группа
Используются для вывода полейЩелкните имя поля и, (это позволяет меньше статистические вычисления для со сводными таблицами/ верхнюю позицию в Списке полей у повторяющиеся столбцы (регион сумм:
- а значит всегдаЧтобы название строки сделать не забывайте указыватьвыполните одно изЧтобы включить заголовки столбцовнажмите кнопку2. Перетащите его в вкладок в легенде диаграммы. удерживая нажатой левую
- пользоваться прокруткой), выберите каждого значения вращается Параметры/ Действия/ Очистить/ списке. поля Продажи. 1, регион 2,Огромные сводные таблицы, которые под рукой. названием столбца, выбираем
  
   для них место. указанных ниже действий. в стиль чередования,Пустые строки область строкРабота со сводными таблицамиФильтр отчетов

Отображение и скрытие пустых строк после строк или элементов

кнопку мыши, перетащите команду

вокруг столбца в Очистить все;

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

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

полей в одну.

помощью этого способа, Товар и Продажи

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

 Для этого выделите попадут в раздел структуру таблицы, как Мы не знаем, сводных таблиц, нажимая название столбцов». Таким сумму продаж по

Изменение способа отображения элементов и подписей без данных

даты и время,Щелкните в любом месте

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

Изменение и удаление форматирования

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

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

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

столбце, а не разбить сводную таблицу

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

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

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

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

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

можно добавить несколькоПоказать в форме структуры

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

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

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

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

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

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

Изменение числового формата поля

и щелкаем правой базе данных Microsoft

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

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

проще настроить сводный или снимите флажок затем выберите командуРабота со сводными таблицамиРабота со сводными таблицамина вкладке

Поддержка форматирования сервера OLAP

Задача вычисления (такие как листы, выберите команду продукта. меню и выберите добавим поле Регион то для этого

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

диапазон данных, на поле с данными, отчет.Цвет заливки
Очистить..Промежуточные итоги и фильтрыВверх коэффициенты валовой иПоказать в табличной формеЩелкните маленькую стрелку, чтобы пункт Итоги по/ продажи, поставив соответствующую снимем галочку в
в одноименной статье таблица с данными основании которых будет которые необходимо показать.В Excel в качестве.внизу коллекции.На вкладке
- На вкладкев группе чистой прибыли, минимальные. развернуть Количество. Задача решена. галочку в Списке
- Списке полей у Советы по построению о продажах товара. строиться отчет. ТакКогда нажимаем на сводную источника информации можноЧтобы включить или отключитьУсловное форматирование обеспечивает наглядность
- АнализПараметрыИтогиПеремещение поля в области и максимальные показатели
- В сводной таблице выберитеДополнительные параметрыСтроку итогов можно отключить полей. Поле Регион поля Товар и

Сохранение и игнорирование форматирования

таблиц.Мы можем переместить всю

как у нас таблицу, становится доступной использовать таблицы Access, цвета текста, установите
при изучении иилив группевыберите вариант на одну позицию продаж либо количество поле строки.и выберите через меню: Работа
продажи будет добавлено поставим галочку уНесколько облегчит процесс построения сводную таблицу на стоит курсор в закладка с параметрами
- SQL Server и или снимите флажок анализе данных, обнаруженииПараметрыСводная таблицаАвтоматические вверх. клиентов и процентНа ленте откроется группа
- Статистическую функцию значение со сводными таблицами/ в область Названия поля Поставщик. Сводной таблицы, тот новый лист, выбрав таблице, диапазон обозначится отчета. С ее
 др.Цвет текста критических проблем, определениив группенажмите кнопкуилиВниз от их общего вкладок

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

support.office.com

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

числа), расположив ихРабота со сводными таблицами Count (все). итоги. Не забудьте (к полю Товар). том, какие же таблица будет преобразована кнопку «Переместить».На третьем шаге Excel заголовки, источники данных,

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

Сводная таблица в Excel

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

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

ОК ячейку Сводной таблицы. Названия строк Списка

таблицы были использованы 2007 (Вставка/ Таблицы/ таблицу помещается абсолютно поместить сводную таблицу.
С помощью сводных таблиц таблиц. Есть пара
На ленте откроется группа определенные вопросы о.Параметры сводной таблицы Если выбрать вариант вниз. Отображение различных вычислений или табличной форме.Предположим, что требуется подготовить полей порядок следования для подсчета тех Таблица). Для этого вся информация из Жмем «Готово» и Excel легко проверить, табличек с информацией. вкладок данных. При использованииНа вкладкеоткройте вкладкуНет
Переместить в начало в полях значений можно дважды щелкнутьКак показано в приведенном отчет о сроках полей Товар и или иных значений сначала приведите исходную

того столбца, который

открывается макет.

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

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

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

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

отключены.Перемещение поля к началуЩелкните имя поля в

перейти к действию 3. воспользоваться продукты таблица,

нужно получить следующую следующий результат. достаточно двойного клика с вышеуказанными требованиями, отчет. отображения в отчете. Другой положительный момент Для науки придумаемНа вкладке учитывать ряд важныхЭкран группе
На вкладке области. секции полей и,На вкладке содержащая столбцы информацию: сколько партийВыделив любое название Товара мышкой на конкретном затем выделите любуюВ нашем примере – Допустим, мы хотим – экономия. Если
остатки на складахАнализ
моментов.выполните любые изМакетРазметка и печатьПереместить в конец удерживая нажатой левуюАнализProductCategory Товара сбывалось в
и нажав пункт значении в Сводной ячейку таблицы и ВСЕ товары, ВСЕ узнать суммы продаж мы будем ежемесячно в двух магазинах.илиПри изменении макета сводной указанных ниже действий.установите или снимитев группе
кнопку мыши, перетащитеилии период от 1 меню Работа со таблице, чтобы был вызовите окно меню даты, ВСЕ суммы

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

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

флажокМакетПеремещение поля к концу поле в областьПараметрыProductNumber

до 10 дней, сводными таблицами/ Параметры/ создан отдельный лист Вставка/ Таблицы/ Таблица. и магазины. Возможно, Ставим галочки – света, газа, то из нескольких листов

в группе данных, скрытия, свертывания элементы без значенийОбъединить и выровнять повыберите вариант области.Значенияв группеи использовать функцию в период 11-20

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

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

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

Готовый отчет можно форматировать, для экономии средствСоздадим отчет с помощьюнажмите кнопку

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

Для этого: свернуть Сводную таблицу, Например, посмотрим какие нажмите ОК.

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

одно из указанныхПеремещение поля в область сколько необходимо для

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

Проверка правильности выставленных коммунальных счетов

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

выделите любое значение продажи по Регионам. для суммирования продаж EXCEL 2007 добавляет на главном. УберемЭто можно сделать вручную

сводную таблицу тарифов таблиц и диаграмм».На вкладке

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

Добавление поля Регион продажи Товара «Апельсины». Для новые возможности: ненужные элементы. и автоматически.

по всем коммунальным Для этого щелкаемРазметка и формат

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

измените нужным образомПараметры поля которые нужно проанализировать, пункт меню Работа

в область строк этого дважды кликнем

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

exceltable.com

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

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

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

Создание отчета с помощью мастера сводных таблиц

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

и формат отчета, Параметры/ Действия/ Очистить/ что Сводная таблица

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

выполните одно из для полей в OLAP. вкладке группыПереместить в названия столбцовПримечания:и в разделе чтобы упростить чтение Очистить все; развернулась на 144 лист только со к таблице;

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

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

Чтобы сохранить макет иЗначения элементы без значений.

по умолчанию.Перемещение поля в областьПри добавлении в областьвыполните одно из Чтобы изменить макет Сбыт (срок фактической всегда удобно. Т.к. относящихся к Товару
ней применяется форматирование, название поля. В со сводными таблицами». для Москвы: сводных таблиц». Добавляем формат сводной таблицыможет быть основана в столбцах, установитеИногда возникают ситуации, когдаЧтобы промежуточные итоги отображались
названий столбцов. значений двух или указанных ниже действий. сводной таблицы, можно реализации Товара) в продажи осуществлялись только «Апельсины». к заголовкам –
нашем примере –В меню «Данные» жмемДля учебных целей возьмем инструмент в панель для применения при
на иерархии данных или снимите флажок данные сводной таблицы под суммируемыми строками,Переместить в значения нескольких полей (какЧтобы элементы полей отображались

изменить форму этой верхней части Списка

в 6 регионах,

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

Если после создания Сводной фильтр, появляется возможность

это название товара

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

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

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

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

Некоторые секреты форматирования

таблицу добавлялись новые итогов, сортировать данные остановимся на названии. ALT+F5). в квартире 60Ставим курсор на первую установите флажок (нижестоящим уровнем в.

ошибки и требуется группы

значений. полей) список полей
в виде структуры строк, промежуточных итогов, в область Значения; разместить в области записи (строки), то и пр.;
Устанавливаем фильтр по значению.Если нужно обновить все кв. м. Чтобы
табличку и нажимаемСохранять форматирование ячеек при иерархии) родительского объектаПримечание: изменить способ их.Параметры поля значений автоматически добавляет в. пустых ячеек и

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

инструмент «Мастера». В обновлении (вышестоящим уровнем в Этот параметр доступен только отображения.

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

Работа с итогами

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

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

для источников данныхЩелкните в любом месте

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

использовать таблицу в кроватям – уберем «Обновить все» (или

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

OLAP. сводной таблицы.

Детализация информации

В сводной таблице щелкните название столбца значений. в этом же можно применить готовый клавишей мыши контекстноеМеняем столбцы местами Чтобы обновить Сводную формате EXCEL 2007 флажок напротив названия комбинацию клавиш CTRL+ALT+F5).

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

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

Первый столбец = первому диапазонах консолидации». То и возвращаться к в столбцах для подписи элементов при вкладок

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

изменении данных: столбцу из сводной есть нам нужно стандартному макету и
одной или нескольких отсутствии полей вРабота со сводными таблицами столбца либо элементили поле вверх и
в виде структурыWindows Online  Количество. за заголовок столбца выберите пункт меню: В строках таблицы

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

exceltable.com

таблицы. Второй –

Источник

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

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

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

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

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

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

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

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

Пример создания сводной таблицы Excel – алгоритм для чайников

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

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

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

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

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

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

Заранее озаглавьте каждый столбик

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

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

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

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

Обратите внимание, как расположились эти данные в нижней области панели настройки.

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

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

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

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

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

В параметрах полей значений вы найдете множество вариантов для анализа.

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

Теперь мы видим, что в отдел «Аксессуары» поступило товаров на сумму 267660 рублей, при этом самый дорогостоящий имеет цену 2700 рублей.
Область «Фильтры» позволяет установить критерий отбора записей. Добавим поле «Дата поступление», просто поставив около него галочку.

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

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

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

Также можно выбрать и значения для отдела.

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

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

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

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

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

Если сводная таблица в Excel не нужна, то стоит разобраться, как её удалить. Это не составит большого труда. Выделите все составляющие вручную, или используя сочетание клавиш «CTRL+A». Далее нажмите клавишу «DELETE» и поле будет очищено.

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

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

Перейдите на вкладку «Анализ» и откройте источник данных.

Excel сам все предложит.

Обновите и вы получите новый перечень полей в области настройки.

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

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

Для этого нам понадобится мастер сводных таблиц. Добавим его на панель быстрого доступа (самый верх окна – слева). Нажмите выпадающую стрелочку и выберите «Другие команды».

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

И найдите мастер сводных таблиц Excel, кликните по нему, затем на «Добавить» и ОК.

Значок появится сверху.

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

Нам нужно несколько полей, а не одно.

На следующем этапе выделите первый диапазон и нажмите кнопку «Добавить». Затем переключитесь на другой лист ( щелкните по его названию внизу) и снова «Добавить». У вас будут созданы два диапазона.

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

Щелкайте «Далее» и создавайте на новом листе.

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

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

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

Как видите, у нас одно значение в соответствующей области.

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

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

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

Сжатая форма

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

Структурированная форма

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

Табличная форма

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

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

Итак, мы рассказали, как составить поля сводной таблицы MS Excel 2016 (в 2007, 2010 действуйте по аналогии). Надеемся, эта информация поможет вам осуществлять быстрый анализ консолидированных данных.

Отличного Вам дня!

Источник

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Операция

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Операция

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Советы и рекомендации по форматированию данных

Используйте чистые табличные данные для достижения наилучших результатов.
Упорядочение данных по столбцам, а не по строкам.
Убедитесь, что все столбцы имеют заголовки с одной строкой уникальных, непустых меток для каждого столбца. Избегайте двойных строк заголовков или объединенных ячеек.
Отформатируйте данные как таблицу Excel (выберите любое место в данных, а затем на ленте выберите Вставить > таблицу ).
Если у вас есть сложные или вложенные данные, используйте Power Query для их преобразования (например, для отмены сворачивания данных), чтобы они были упорядочены по столбцам с одной строкой заголовка.

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

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

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

Статьи по теме

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

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

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

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

Создание сводной таблицы, подключенной к наборам данных Power BI

Упорядочение полей сводной таблицы с помощью списка полей

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

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

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

Источник

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

В этой статье:

Что такое вычисляемое поле и для чего оно нужно
Как создать вычисляемое поле в сводной таблице Excel
Альтернатива № 1 вычисляемому полю: столбец с расчетом в исходной таблице
Почему не всегда можно применять расчетный столбец для вычислений в сводной таблице
Альтернатива № 2 вычисляемому полю: вычисления вне диапазона сводной таблицы
Что такое вычисляемый объект
Как создать вычисляемый объект
Удаление и изменение вычислений в сводных таблицах
Недостатки использования вычислений в сводных таблицах excel
Как получить формулы вычислений

Что такое Вычисляемое поле и для чего оно нужно

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

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

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

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

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

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

1) Для начала создадим сводную таблицу, в строки которой добавим категорию торговой точки. В значения — сумму по полю Выручка и сумму по полю Кол-во чеков.

2) Установим курсор на любой ячейке сводной таблицы и перейдем на вкладку Анализ — блок Вычисления — Поля, элементы и наборы — Вычисляемое поле…

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

4) Теперь напишем формулу, по которой вычисляемое поле будет производить расчет. Нам нужно поле Выручка разделить на поле Кол-во чеков.

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

Оно появилось в поле Формула.

Теперь нужно написать оператор деления “/” и таким же образом указать поле Кол-во чеков.

В итоге получим такую формулу:

Если вы достаточно внимательны, то заметили, что название поля Выручка указано без кавычек, а название Кол-во чеков заключено в одинарные кавычки. Это связано в тем, что во втором случае (‘Кол-во чеков’) название поля состоит из нескольких слов. Excel автоматически проставляет эти кавычки, поэтому добавлять или убирать их вручную не нужно.

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

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

Альтернатива № 1 вычисляемому полю: столбец с расчетом в исходной таблице

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

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

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

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

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

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

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

Далее выберем Среднее.

Получили средний чек.

И снова самые внимательные заметят, что он не совпадает с тем Средний чеком, который мы получили при помощи вычисляемого поля. Да и если разделить значение из поля Выручка на Кол-во чеков — получим другие данные.

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

Альтернатива № 2 вычисляемому полю: вычисления вне диапазона сводной таблицы

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

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

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

Представим ситуацию, что появилась новая категория торговой точки. Обновим сводную, и видим такую “красоту”. Итоги съехали, надо переделывать вручную.

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

Таким образом, делаем вывод, что эта альтернатива рабочая, но только для “одноразовых” вычислений. Никак не для постоянных отчетов.

Что такое вычисляемый объект

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

Также отличие в том, что вычисляемое поле работает со столбцами, а вычисляемый объект — со строками.

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

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

Давайте разделим категории торговых точек на еще более укрупненные категории. В категорию “Большие точки” отнесем категории “Крупная” и “Выше среднего”. В категорию “Маленькие точки” — “Микро” и “Средняя”. Как видите, категории не имеют какого-то общего признака, по которому можно сделать агрегацию (точнее, он есть, но только в нашей голове).

Работать будем с той же сводной таблицей.

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

Важно: именно в строках, а не в числовых значениях!

Далее вкладка Анализ — блок Вычисления — Поля, элементы и наборы — Вычисляемый объект…

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

Зададим имя объекта “Большие точки” и в поле Формула по аналогии с созданием вычисляемого поля зададим формулу. Использовать будем значения из поля Элементы и кнопку Добавить элемент.

Нажмем Ок, и получим группирующую строку внизу таблицы.

Аналогично сделаем вычисляемый объект для группы “Маленькие точки”. Также добавим ранее созданное вычисляемое поле Средний чек (для полноты картины).

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

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

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

Давайте для примера удалим вычисляемое поле Средний чек.

Откроем меню Вычисляемое поле.

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

Готово, вычисляемое поле удалено.

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

Точно также можно внести изменения в вычисляемое поле (или объект). Нужно исправить формулу и нажать Ок, кнопку Удалить не нажимать.

Недостатки использования вычислений в сводных таблицах excel

Автоматизация вычислений при помощи вычисляемых полей или вычисляемых объектов имеет свои недостатки. Учитывайте их.

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

Как получить формулы вычислений

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

Формулы откроются на отдельном листе.

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

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

Сообщество Excel Analytics | обучение Excel

Канал на Яндекс.Дзен

Вам может быть интересно:

Источник

#Руководства

13 май 2022
0

Как систематизировать тысячи строк и преобразовать их в наглядный отчёт за несколько минут? Разбираемся на примере с квартальными продажами автосалона

Иллюстрация: Meery Mary для Skillbox Media

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

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

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

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

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

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

Таблица, в которой хранятся данные о продажах автосалона
Скриншот: Skillbox Media

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

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

Создаём сводную таблицу

Чтобы сводная таблица сработала корректно, важно соблюсти несколько требований к исходной:

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

Теперь переходим во вкладку «Вставка» и нажимаем на кнопку «Сводная таблица».

Жмём сюда, чтобы создать сводную таблицу
Скриншот: Skillbox Media

Появляется диалоговое окно. В нём нужно заполнить два значения:

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

В нашем случае выделяем весь диапазон таблицы продаж вместе с шапкой. И выбираем «Новый лист» для размещения сводной таблицы — так будет проще перемещаться между исходными данными и сводным отчётом. Жмём «Ок».

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

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

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

Появился новый лист для сводной таблицы
Скриншот: Skillbox Media

Настраиваем сводную таблицу и получаем результат

В верхней части панели настроек находится блок с перечнем возможных полей сводной таблицы. Поля взяты из заголовков столбцов исходной таблицы: в нашем случае это «Марка, модель», «Цвет», «Год выпуска», «Объём», «Цена», «Дата продажи», «Продавец».

Нижняя часть панели настроек состоит из четырёх областей — «Значения», «Строки», «Столбцы» и «Фильтры». У каждой области своя функция:

«Значения» — проводит вычисления на основе выбранных данных из исходной таблицы и относит результаты в сводную таблицу. По умолчанию Excel суммирует выбранные данные, но можно выбрать другие действия. Например, рассчитать среднее, показать минимум или максимум, перемножить.
Если данные выбранного поля в числовом формате, программа просуммирует их значения (например, рассчитает общую стоимость проданных автомобилей). Если формат данных текстовый — программа покажет количество ячеек (например, определит количество проданных авто).
«Строки» и «Столбцы» — отвечают за визуальное расположение полей в сводной таблице. Если выбрать строки, то поля разместятся построчно. Если выбрать столбцы — поля разместятся по столбцам.
«Фильтры» — отвечают за фильтрацию итоговых данных в сводной таблице. После построения сводной таблицы панель фильтров появляется отдельно от неё. В ней можно выбрать, какие данные нужно показать в сводной таблице, а какие — скрыть. Например, можно показывать продажи только одного из менеджеров или только за выбранный период.

Настроить сводную таблицу можно двумя способами:

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

Первый вариант не самый удачный: Excel редко ставит данные так, чтобы с ними было удобно работать, поэтому сводная таблица получается неинформативной. Остановимся на втором варианте — он предполагает индивидуальные настройки для каждого отчёта.

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

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

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

Добавляем в сводную таблицу поле «Продавцы» через область «Строки»
Скриншот: Skillbox

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

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

Добавляем в сводную таблицу поле «Марка, модель» через область «Строки»
Скриншот: Skillbox Media

Определяем, какая ещё информация понадобится для отчётности. В нашем случае — цены проданных автомобилей и их количество.

Чтобы сводная таблица самостоятельно суммировала эти значения, перетащим поля «Марка, модель» и «Цена» в область «Значения».

Добавляем в сводную таблицу поля «Марка, модель» и «Цена» через область «Значения»
Скриншот: Skillbox Media

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

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

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

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

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

Над сводной таблицей появился дополнительный блок с фильтрами
Скриншот: Skillbox Media

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

В блоке фильтров нажмём на стрелку справа от поля «Год выпуска»:

Появилось всплывающее окно для фильтрации
Скриншот: Skillbox Media

В появившемся окне уберём галочку напротив параметра «Выделить все» и поставим её напротив параметра «2017». Закроем окно.

Фильтруем таблицу по году выпуска проданных автомобилей
Скриншот: Skillbox Media

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

Так выглядит отфильтрованная сводная таблица
Скриншот: Skillbox Media

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

Проводим дополнительные вычисления

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

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

Меняем структуру квартальных продаж менеджеров на процентную
Скриншот: Skillbox

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

Сводная таблица самостоятельно рассчитала процент продаж за квартал для каждого менеджера
Скриншот: Skillbox Media

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

Так сводная таблица выглядит в свёрнутом виде
Скриншот: Skillbox Media

Чтобы снова раскрыть данные об автомобилях — нажимаем +.

Чтобы значения снова выражались в рублях — через правый клик мыши возвращаемся в «Дополнительные вычисления» и выбираем «Без вычислений».

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

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

В исходной таблице появились две дополнительные строки
Скриншот: Skillbox

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

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

Жмём сюда, чтобы изменить исходный диапазон
Скриншот: Skillbox Media

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

Добавляем в исходный диапазон две новые строки
Скриншот: Skillbox Media

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

Данные в сводной таблице обновились автоматически
Скриншот: Skillbox Media

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

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

Меняем данные двух ячеек в исходной таблице
Скриншот: Skillbox Media

Чтобы данные сводной таблицы тоже обновились, переходим на её лист и во вкладке «Анализ сводной таблицы» нажимаем кнопку «Обновить».

Теперь у менеджера Соколова П. изменились данные в столбце «Цена, руб.».

Жмём сюда, чтобы обновить данные
Скриншот: Skillbox Media

Как использовать сводные таблицы в «Google Таблицах»? Нужно перейти во вкладку «Вставка» и выбрать параметр «Создать сводную таблицу». Дальнейший ход действий такой же, как и в Excel: выбрать диапазон таблицы и лист, на котором её нужно построить; затем перейти на этот лист и в окне «Редактор сводной таблицы» указать все требуемые настройки. Результат примет такой вид:

Так выглядит сводная таблица в «Google Таблицах»
Скриншот: Skillbox Media

Научитесь: Excel + Google Таблицы с нуля до PRO
Узнать больше

Источник

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

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

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

Доступные методы вычислений

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

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

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

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

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

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

Сумма значений. Функция по умолчанию для числовых данных.

Число значений. Действует аналогично функции СЧЁТЗ. Функция по умолчанию для данных, отличных от числовых.

Количество числовых значений. Действует аналогично функции СЧЁТ.

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

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

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

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

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

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

Значение, введенное в данное поле.

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

% от суммы по столбцу

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

% от суммы по строке

Значение в каждой строке или категории в процентах от итогового значения по этой строке или категории.

Значения в процентах от значения базового элемента в соответствующем базовом поле.

% от суммы по родительской строке

Рассчитывает значения следующим образом:

(значение элемента) / (значение родительского элемента по строкам).

% от суммы по родительскому столбцу

Рассчитывает значения следующим образом:

(значение элемента) / (значение родительского элемента по столбцам).

% от родительской суммы

Рассчитывает значения следующим образом:

(значение элемента) / (значение родительского элемента в выбранном базовом поле).

Значения в виде разности по отношению к значению базового элемента в соответствующем базовом поле.

Значения в виде разности в процентах по отношению к значению базового элемента в соответствующем базовом поле.

С нарастающим итогом в поле

Значение в виде нарастающего итога для последовательных элементов в базовом поле.

% от суммы с нарастающим итогом в поле

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

Сортировка от минимального к максимальному

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

Сортировка от максимального к минимальному

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

Рассчитывает значения следующим образом:

((значение в ячейке) x (общий итог)) / ((итог строки) x (итог столбца)).

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

Влияние типа источника данных на вычисления

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

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

Кроме того, если сервер OLAP предоставляет вычисляемые поля, называемые «вычисляемыми элементами», вы увидите их в списке полей сводной таблицы. Вы также увидите все вычисляемые поля и вычисляемые объекты, созданные с помощью макросов, которые написаны на языке Visual Basic для приложений (VBA) и хранятся в книге, но не сможете их изменить. Если вам нужны дополнительные типы вычислений, обратитесь к администратору базы данных OLAP.

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

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

Использование формул в сводных таблицах

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

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

Имена полей и элементов. В Excel имена полей и элементов используются для идентификации этих элементов отчета в формулах. В приведенном ниже примере для данных в диапазоне C3:C9 используется имя поля Молоко. Для вычисляемого объекта в поле Тип, оценивающего объем продаж нового продукта на основе данных о продажах молочных продуктов, можно использовать формулу =Молоко * 115%.

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

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

Формулы для вычисляемых объектов оперируют отдельными записями. Например, формула вычисляемого объекта =Молоко * 115% умножает каждое отдельное значение продаж молочных продуктов на 115 %, после чего полученные величины суммируются в области «Значения».

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

Итоги. Формулы не могут ссылаться на итоговые значения (в примере выше — это Сумма за март, Сумма за апрель и Общий итог).

Имена полей в ссылках на элементы. Вы можете включить имя поля в ссылку на элемент. Имя элемента должно быть заключено в квадратные скобки, например: Регион[Север]. Используйте этот формат, чтобы избежать ошибок #ИМЯ?, которые возникают, если два элемента в двух разных полях отчета имеют одинаковые имена. Например, если в отчете есть два элемента с именем «Мясо» в полях «Тип» и «Категория», можно избежать появления ошибок #ИМЯ?, ссылаясь на эти элементы следующим образом: Тип[Мясо] и Категория[Мясо].

Ссылки на элементы по позиции. Вы можете сослаться на элемент, указав его позицию в отчете (с учетом того, какие элементы фактически отображаются и как они отсортированы в настоящий момент). Тип[1] — это Молоко, а Тип[2] — Морепродукты. Когда позиции элементов изменятся, например, если какие-то из них будут скрыты или снова отображены, такая ссылка, возможно, будет указывать на другой элемент. Скрытые элементы не учитываются в этом индексе.

Для ссылки на элементы можно использовать относительные позиции. Они определяются относительно вычисляемого объекта, содержащего формулу. Если текущим регионом является Юг, то Регион[-1] — это Север. Если текущим регионом является Север, то Регион[+1] — это Юг. Например, для вычисляемого объекта можно использовать формулу =Регион[-1] * 3%. Например, для вычисляемого объекта можно использовать формулу =Регион[-1] * 3%. Если позиция, которую вы указали, находится перед первым или после последнего элемента в поле, формула возвращает ошибку #ССЫЛКА!.

Использование формул в сводных диаграммах

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

Например, на этой сводной диаграмме представлены данные о продажах для каждого продавца по регионам:

Чтобы посмотреть, как будут выглядеть объемы продаж, если увеличатся на 10 %, можно создать вычисляемое поле в связанной сводной таблице и воспользоваться формулой =Продажи * 110%.

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

Чтобы отобразить отдельный маркер данных для продаж в регионе «Север» за вычетом транспортных расходов, которые составляют 8 %, можно создать в поле «Регион» вычисляемый объект с такой формулой: =Север – (Север * 8%).

Диаграмма будет выглядеть следующим образом:

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

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

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

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

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

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

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

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

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

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

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

Для примера возьмем следующую таблицу:

Создадим сводную таблицу: «Вставка» — «Сводная таблица». Поместим ее на новый лист.

Мы добавили в сводный отчет данные по поставщикам, количеству и стоимости.

Напомним, как выглядит диалоговое окно сводного отчета:

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

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

Например, среднее количество заказов по каждому поставщику:

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

Установим фильтр в сводном отчете:

В перечне полей для добавления в таблицу ставим галочку напротив заголовка «Склад».
Перетащим это поле в область «Фильтр отчета».
Таблица стала трехмерной – признак «Склад» оказался вверху.

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

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

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

Сортировка в сводной таблице Excel

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

Сделаем таблицу более полезной – сгруппируем даты по кварталам. Для этого щелкнем правой кнопкой мыши по любой ячейке с датой. В выпадающем меню выберем «Группировать». Заполним параметры группировки:

После нажатия ОК сводная таблица приобретает следующий вид:

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

Значения в сводном отчете поменяются в соответствии с отсортированными данными:

Теперь выполним сортировку данных по дате. Правая кнопка мыши – «Сортировка». Можно выбрать способ сортировки и на этом остановиться. Но мы пойдем по другому пути. Нажмем «Дополнительные параметры сортировки». Откроется окно вида:

Установим параметры сортировки: «Дата по убыванию». Кликнем по кнопке «Дополнительно». Поставим галочку напротив «Автоматической сортировки при каждом обновлении отчета».

Теперь при появлении в сводной таблице новых дат программа Excel будет сортировать их по убыванию (от новых к старым):

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

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

Добавим в отчет заголовок «Поставщик». Заголовок «Стоимость» три раза перетащим в поле «Значения» — в сводную таблицу добавятся три одинаковых столбца.
Для первого столбца оставим значение «Сумма» для итогов. Для второго – «Среднее». Для третьего – «Количество».
Поменяем местами значения столбцов и значения строк. «Поставщик» — в названия столбцов. «Σ значения» — в названия строк.

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

Научимся прописывать формулы в сводной таблице. Щелкаем по любой ячейке отчета, чтобы активизировать инструмент «Работа со сводными таблицами». На вкладке «Параметры» выбираем «Формулы» — «Вычисляемое поле».

Жмем – открывается диалоговое окно. Вводим имя вычисляемого поля и формулу для нахождения значений.

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

Экспериментируйте: инструменты сводной таблицы – благодатная почва. Если что-то не получится, всегда можно удалить неудачный вариант и переделать.

Вычисляемое поле в Сводных таблицах в MS Excel

Научимся добавлять и редактировать Вычисляемое поле в Сводной таблице MS EXCEL 2010.

Простые Сводные таблицы мы научились строить в статье Сводные таблицы в MS Excel. Теперь научимся создавать и изменять Вычисляемое поле в Сводной таблице.

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

Нашей задачей будет:

вычислить % выполнения плана
представить полученные данные по годам для каждого месяца (каждый год — отдельный столбец)

В итоге у нас должна получиться вот такая сводная таблица.

Исходная таблица

Исходную таблицу подготовим в специальном формате таблиц MS EXCEL (см. статью Таблицы в формате EXCEL 2007).

На основе даты продажи в столбце А, в таблице рассчитываются 2 столбца: Номер месяца =МЕСЯЦ() и Год =ГОД() . Для форматирования ячеек столбца А в виде окт11 использован пользовательский формат Даты [$-419]МММГГ;@.

Столбец План представляет собой линейный тренд (это не важно для целей данной статьи), столбец Продано — фактический объем продаж.

Сводная таблица

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

Нажав ОК, сводная таблица автоматически создастся на новом листе.

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

Перед тем как создать Вычисляемое поле перетащите поле Номер месяца в Названия строк.

Создаем вычисляемое поле

Для решения задачи нам потребуется вычислить % выполнения плана по формуле =’Продано, руб.’/’План, руб.’

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

Для этого выделите ячейку в Сводной таблице, в появившемся меню Работа со сводными таблицами выберите Параметры/ Вычисления/ Поля, элементы и наборы/ Вычисляемое поле :

Появится диалоговое окно:

Интерфейс этого окна не относится к интуитивно понятным вещам, поэтому требует дополнительного пояснения:

Вместо Поле1 введите название Вычисляемого поля, например, ПроцентВыполнения
В списке полей выделите поле Продано, руб. и нажмите кнопку Добавить поле или дважды кликните на него. Название поля будет введено в поле Формула
Введите символ деления / в поле Формула
В списке полей выделите поле План, руб. и нажмите кнопку Добавить поле
Нажмите ОК

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

После несложного форматирования Сводная таблица приобретет законченный вид (необходимо убрать ошибку #ДЕЛ/0!, изменить названия столбцов и изменить формат ячеек на процентный).

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

Теперь разберемся, что Вычисляемое поле нам насчитало.

Вычисляемое поле. Алгоритм расчета

Для каждого месяца у нас есть только одно значение фактических продаж (столбец Продажи) и плана. Вычисляемое поле ПроцентВыполнения возвращает значение равное их отношению. Например, для января 2012 года — это 50,19% (продано было 36992,22, а план был 73697,76). 36992,22/73697,76=0,5019 (см. строку 10 на листе Исходная таблица).

Теперь проверим итоги по месяцам. За январь итоговым значением является 93,00%. Как это значение получилось?

Сначала программа вычислила СУММУ продаж за январь по всем годам, затем, вычислила СУММУ всех плановых значений. Разделив одно на другое, было получено 93,00%. В этом можно убедиться проделав вычисления самостоятельно (см. строку 10 на листе Сводная таблица, столбцы H:J).

В этом состоит одно из ограничений Вычисляемого поля — итоговые значения вычисляются только на основании суммирования.

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

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

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

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

Изменяем и удаляем Вычисляемое поле

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

Там же можно удалить это поле.

Еще одно ограничение

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

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

Создать само Вычисляемое поле для номера месяца — не проблема:

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

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

В прошлой статье рассказывалось про то как создать простую сводную таблицу. Сейчас мы ее немного усложним и заодно разберемся в чем отличие областей СТРОК, КОЛОНН и ЗНАЧЕНИЙ. Вы поймете когда и в какую область необходимо переносить поле сводной таблицы.

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

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

Теперь давайте конструировать отчет. Давайте перенесем поле Филиал в область СТРОКИ, тогда мы получим перечень всех филиалов (без дубликатов) из исходной таблицы.

Теперь давайте расположим все наши группы активов по столбцам, для этого перенесем поле Группа в область КОЛОННЫ. Получим следующую картину:

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

Так получилось намного нагляднее. Осталось добавить сумму по рыночной стоимости в наш отчет. Для этого перенесем поле Рыночная стоимость в область ЗНАЧЕНИЯ.

Источник