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

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

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

Рисунок 2-14

Задание 2.3 Вставка вычисляемого поля или вычисляемого элемента

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

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

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

На основе данных, содержащихся в файле Задание 2 - Анализ данных с помощью сводных таблиц.xls (рис. 2-15), создайте список.

Рисунок 2-15

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

Создайте сводную таблицу, которая содержит объемы продаж каждого продавца, упорядоченные по месяцам (рис. 2-16).

Рисунок 2-16

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

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

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

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

Чтобы создать такое поле, выполните ряд действий.

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

2. На панели инструментов

в Формулы → Вычисляемое поле . Появится диалоговое окноВставка вычисляемого поля .

3. Введите имя поля и определите формулу, как показано на рисунке 2-17. В формуле можно использовать другие поля. В данном примере имя поля –Средняя стоимость , а формула имеет такой вид:продажи/"Продано единиц" .

4. Щелкните на кнопке

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

Рисунок 2-17

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

Рисунок 2-18

Вставка в сводную таблицу вычисляемого элемента

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

Сводная таблица, показанная на рисунке 2-16, содержит текстовое поле с именем

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

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

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

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

2. На панели инструментов Сводные таблицы щелкните на кнопкеСводная таблица и

в открывшемся меню выберите команду Формулы → Вычисляемый объект . Появится диалоговое окноВставка вычисляемого элемента .

3. Введите имя нового элемента в поле Имя , а также определите формулу в полеФормула (рис. 2-19). В формуле можно использовать элементы других полей, но нельзя вводить функции рабочего листа. В этом примере новый элемент названКвартал 1 , а вычисляется он по следующей формуле: =Январь+Февраль+Март.

4. Щелкните на кнопке Добавить .

5. Повторите пункты 3 и 4, чтобы создать вычисляемые элементы для второго и третьего кварталов.

6. Щелкните на кнопке ОК , чтобы закрыть диалоговое окно.

Рисунок 2-19

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

вычисляемых элементов на соответствующие им места. Эти строки были также выделены

полужирным шрифтом.

Для изменения групповых операций, выполняемых над данными, выполним следующие операции:

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

2. В появившемся окне нажмите кнопку «Дополнительно». На появившейся дополнительной области (рис. 10) в списке «Дополнительные вычисления» выберите пункт «С нарастающим итогом в поле», а в списке поле - элемент «Дата оплаты» и нажмите кнопку ОК .

Эти же действия следует повторить и для остальных двух полей - поля «Сумма оплат» и поля «Задолженность».

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

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

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

Таблица 1. Дополнительные вычисления

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

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

1. Перетащите поле «Специализация» в область Строка в крайнюю левую позицию.

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

3. Перетащите поле «Данные» из области Строка в область Столбец так, чтобы оно оказалось справа от поля даты оплаты счета.

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

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

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

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

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

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

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

Выбираем Параметры — раздел Сервис — Формулы

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

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

Если есть вопросы пишите нам в комментарии!