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

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

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

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

Задание 8

Создайте вычисляемое поле Итого . Для этого:

1. перейдите на Лист 1

Вклад ;

3. установите курсор на название одного из вкладов;

4. на вкладке Параметры найдите инструмент Поля, элементы и наборы и в раскрывшемся списке выберите команду Вычисляемое поле ;

5. в диалоговом окне укажите имя поля – Итоги ;

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

7. после нажатия кнопки ОК вы должны получить результат представленный на рисунке ниже;

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

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

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

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

Задание 9

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

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

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

Задание 10

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

1. перейдите на Лист 1 и по имеющимся данным создайте новую сводную таблицу на новом листе;

2. в область строк перетащите поле Отделение банка, в область столбцов – поле Вклад , а в область значений – поле Сумма вклада ;

3. в область Фильтр отчета перетащите поле Дата вклада ;

4. в сводной таблице в области фильтра вы можете теперь выбрать необходимое условие отбора – 1 квартал. (Ранее вы уже задавали способ группировки по датам, поэтому в фильтре отображается уже заданный ранее параметр – по кварталам);

5. после нажатия кнопки ОК вы должны получить следующий результат:

Самостоятельное задание

Откройте таблицу Сотрудники редакции.xlsx . Выполните следующие задания (Каждое задание сохраняется в виде отдельной сводной таблицы на отдельном листе):

· Создайте сводную таблицу с вычислением суммы окладов по отделам.

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

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

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

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

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

· Построить диаграмму средний оклад по отделам.

2-й способ: щелкнуть правой кнопкой мыши по значениям нужного поля в области ΣЗначения [Σ Values] и выбрать:

 Итоги по , далее выбратьСумма ,Количество ,Среднее .Максимум ,Минимум ,Произведение или

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

 Дополнительные вычисления , далее выбрать нужный вариант расчета.

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

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

Values] несколько раз, затем настроить параметры поля. Это позволит одновременно видеть как само вычисление, так и вычисление, выполненное на его основе.

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

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

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

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

Microsoft Excel 2010. Уровень 2. Расширенные возможности

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

 Исключение потенциальных ошибок в формулах и ссылках на ячейки.

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

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

 Обеспечение гибкости изменения вычислений при изменении определений элементов.

 Возможность эффективного управления вычислениями.

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

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

2. На вкладке Параметры , в группеВычисления , раскрыть списокПоля,

элементы и наборы и выбратьВычисляемое поле .

3. В окне Вставка вычисляемого поля :

 В поле Имя ввести имя нового поля.

 В поле Формула составить формулу, начиная со знака= и выбирая

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

 Нажать Добавить .

4. ОК .

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

Параметры полей значений .

Редактирование вычисляемого поля

1. На вкладке Параметры , в группеВычисления , раскрыть списокПоля,

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

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

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

Освежить свои знания об основах сводных таблиц можно .

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

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

Третий способ. Встроить вычисляемые поля в саму сводную таблицу. А вот это хороший способ. Делается это следующим образом. Когда сводная таблица уже построена, ставим курсор на сводную таблицу (при этом на ленте появляются две закладки, относящиеся к данной сводной таблице) и нажимаем на появившуюся закладку Options , затем Fields , Items , & Sets из блока Calculations , в выпавшем меню выбираем Calculated field . В 2007 офисе эта кнопка была на той же вкладке Options , только кнопка была отдельная.

В появившемся окне, в поле Name введите желаемое название поля. В поле Formula введите необходимую вам формулу, вставляя поля из списка внизу и нажимая кнопку Insert field .

Получится таблица с новым расчетным полем. Таблица готова!

Экселевский файл можно скачать отсюда.

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

Последние новости

• Продвинутый Excel: Почему я перестал пользоваться функцией V…

  Я уже писал про то, что функция VLOOKUP (ВПР), наверное, самая полезная функция после простых арифметических операций …

• Как Excel пересчитывает книгу и почему надо избегать волатил…

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

• Как быстро построить график Waterfall (водопад)

  В Excel отсутствует стандартные диаграммы типа Waterfall. Поэтому для создания диаграмм этого типа обычно используют гис…

• Окно контрольного значения для отслеживания результатов

  Часто бывает, что мы работаем с большими таблицами, которые рассчитывают в конечном итоге 1-2 показателя, но зависят от …

• Bullet chart для сравнения планируемых показателей и фактиче…

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

• Тацуо Хориучи - 73-летний художник, рисующий в Excel

  "Я никогда не пользовался Excel на работе, но я видел, как другие люди делают в нем довольно красивые диаграммы и график…

• Как возвести число в степень и извлечь корень

  Знаете ли вы, что для того, что для возведения в степень числа в Excel есть специальный символ ^ (на шестерке в английск…