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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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 показано, как будет выглядеть сводная таблица после переноса

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

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