Как добавить формулу в сводную таблицу excel

Вычисляемое поле сводной таблицы «Excel». ( формулы в сводной таблице )

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

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

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

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

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

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

Таблица на листе в Excel

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

Сводная таблица в Эксель

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

Рассмотрим, как это можно сделать.

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

Перейти во вкладку «Параметры»

Кликнуть по ярлыку «Поля, элементы, наборы»

Вставить вычесляемое поле

Выбрать пункт «Вычисляемое поле»

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

Имя поля

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

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

Источник: ruexcel.ru

Как добавить формулу в сводную таблицу excel

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

Файлы для скачивания:

Файл Описание Размер файла: Скачивания
Пример 109 Кб 2498

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

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

Как включить видео?

Чтобы увидеть видео включите в браузере GIF-Анимацию.

Затем наводим курсор на это поле в окне “Значения” и нажимаем на него левой клавишей мышки, в выпавшем меню выбираем пункт “Параметры полей значений. “:

Как включить видео?

Чтобы увидеть видео включите в браузере GIF-Анимацию.

В открывшемся диалоговом окне “Параметры поля значений”, меняем название поля, например, на “Доля”:

Переходим во вкладку “Дополнительные вычисления”, в поле со списком (там, где написано “Без вычислений”) выбираем пункт “% от суммы по столбцу” и нажимаем кнопку “ОК”:

После чего ваша Сводная таблица должна будет приобрести следующий вид:

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

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

В одном столбце у нас “Сумма продаж”, в другой “Прибыль”, нам необходимо получить рентабельность каждой категории товаров. Для этого устанавливаем курсор на сводную таблицу, в главном меню в разделе “Работа со сводными таблицами”, во вкладке “Анализ” (в 2007 и 2010 “Параметры”), в разделе “Вычисления”, нажимаем кнопку “Поля, элементы и наборы”, в выпавшем списке выбираем пункт “Вычисляемое поле. ” (в 2007 “Сервис” -> “Формулы”):

В открывшемся диалоговом окне меняем название поля, например, на “Рентабельность”:

В окне “Поля:” встаем на поле “Прибыль” и нажимаем кнопку “Добавить поле” так чтобы оно появилось в поле “Формула:”, затем нажимаем на клавиатуре “/” – Знак деления, встаем на поле “Сумма” и снова нажимаем кнопку “Добавить поле”. В итоге всех манипуляций в поле “Формула:” у вас должно получиться “=Прибыль/Сумма”:

Источник: e-xcel.ru

Excel works!

Excel работает за вас

Excel works!

Thanks for Visiting

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

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

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

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

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

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

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

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

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

Ссылка на сводную таблицу

Если вам все-таки нужно сослаться на сводную таблицу, то ссылка в большинстве случае будет иметь вид = ПОЛУЧИТЬ.ДАННЫЕ(…

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

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

Источник: excelworks.ru

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

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

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

Другие функции расчета вместо банальной суммы

Если щелкнуть правой кнопкой мыши по расчетному полю в области данных и выбрать из контекстного меню команду Параметры поля (Field Settings) или в версии Excel 2007 – Параметры полей значений (Value Field Settings) , то откроется очень полезное окно, используя которое можно задать кучу интересных настроек:

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

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

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

…а потом задавайте разные функции для каждого из полей, щелкая по очереди по ним мышью и выбирая команду Параметры поля (Field settings) , чтобы в итоге получить желаемое:

Долевые проценты

Если в этом же окне Параметры поля нажать кнопку Дополнительно (Options) или перейти на вкладку Дополнительные вычисления (в Excel 2007-2010), то станет доступен выпадающий список Дополнительные вычисления (Show data as) :


В этом списке, например, можно выбрать варианты Доля от суммы по строке (% of row) , Доля от суммы по столбцу (% of column) или Доля от общей суммы (% of total) , чтобы автоматически подсчитать проценты для каждого товара или города. Вот так, например, будет выглядеть наша сводная таблица с включенной функцией Доля от суммы по столбцу:

Динамика продаж

Если в выпадающем списке Дополнительные вычисления (Show data as) выбрать вариант Отличие (Difference) , а в нижних окнах Поле (Base field) и Элемент (Base item) выбрать Месяц и Назад (в родной англоязычной версии вместо этого странного слова было более понятное Previous, т.е. предыдущий):

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

А если заменить Отличие (Difference) на Приведенное отличие (% of difference) и добавить условное форматирование для выделения отрицательных значений красным цветом – то получим то же самое, но не в рублях, а в процентах:

В Microsoft Excel 2010 все вышеперечисленные настройки вычислений можно проделать еще проще – щелкнув правой кнопкой мыши по любому полю и выбрав в контекстном меню команды Итоги по (Summarize Values By) :

. и Дополнительные вычисления (Show Data as) :

Также в версии Excel 2010 к этому набору добавились несколько новых функций:

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

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

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

  • Сортировка от минимального к максимальному и наоборот – немного странное название для функции ранжирования (РАНГ), вычисляющей порядковый номер (позицию) элемента в общем списке значений. Например, с ее помощью удобно ранжировать менеджеров по их суммарной выручке, определяя кто на каком месте в общем зачете:
  • Источник: www.planetaexcel.ru

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Источник: exceltable.com

    Microsoft Excel

    трюки • приёмы • решения

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

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

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

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

    Вставка промежуточных итогов

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

    1. Выберите любую ячейку в столбце С.
    2. Щелкните правой кнопкой мыши и в контекстном меню выберите Сортировка ► Сортировка от А до Я.
    3. Выберите Данные ► Структура ► Промежуточный итог для открытия диалогового окна Промежуточные итоги.
    4. В окне Промежуточные итоги выберите в списке При каждом изменении в пункт Пол, в списке ОперацияСреднее, а в поле Добавить итоги по установите флажок Оценка.

    Результат добавления промежуточных итогов показан на рис. 103.2. Обратите внимание, что Excel также создает контур, так что вы можете скрыть подробности и просматривать только итоги. Формулы, добавленные Excel, используют функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ со значением 1 в качестве первого аргумента (1 обозначает среднее). Вот эти формулы:
    =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(1;B2:B13)
    =ПРОМЕЖУТОЧНЫЕ.ИТ0ГИ(1;B15:B28)
    =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(1;B2:B28)
    Формула в ячейке В30 вычисляет общее среднее и использует диапазон, который включают в себя другие две формулы ПРОМЕЖУТОЧНЫЕ.ИТОГИ в ячейках В14 и В29 . Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ игнорирует ячейки, которые содержат другие формулы ПРОМЕЖУТОЧНЫЕ.ИТОГИ.

    Рис. 103.2. Excel добавляет промежуточные итоги автоматически

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

    Другой способ вычисления средних значений заключается в использовании формул. Формула для расчета среднего для всех студентов простая: =СРЗНАЧ(B2:B27) .

    Чтобы узнать среднее значение в зависимости от пола, можете воспользоваться функцией СРЗНАЧЕСЛИ для создания следующих формул:
    =СРЗНАЧЕСЛИ(C2:C27;”Женский”;B2:B27)
    =СРЗНАЧЕСЛИ(C2:C27;”Мужской”;B2:B27)

    Функция СРЗНАЧЕСЛИ была введена в Excel 2007. Если вам нужно, чтобы ваша книга была совместима с более ранними версиями, используйте эти формулы:
    =СУММЕСЛИ(C2:C27;”Женский”;B2:B27)/СЧЕТЕСЛИ(C2:C27;”Женский”)
    =СУММЕСЛИ(C2:C27;”Мужской”;B2:B27)/СЧЕТЕСЛИ(C2:C27;”Мужской”)

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

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

    1. Выберите любую ячейку из диапазона данных и выполните команду Вставка ► Таблицы ► Сводная таблица для открытия диалогового окна Создание сводной таблицы.
    2. В диалоговом окне убедитесь, что Excel выбрал правильный диапазон данных, и укажите ячейку в текущем листе в качестве местоположения. Ячейка Е1 будет неплохим выбором.
    3. Нажмите кнопку ОК, и Excel отобразит список полей сводной таблицы.
    4. В списке полей перетащите пункт Пол в раздел Названия строк, в самый низ.
    5. Перетащите пункт Оценка в раздел Значения. Excel создаст сводную таблицу, но отобразит функцию СУММ, а не СРЗНАЧ.
    6. Чтобы изменить используемую итоговую функцию, щелкните правой кнопкой мыши на любом значении в сводной таблице и выберите Итоги по ► Среднее в контекстном меню (рис. 103.3).

    Рис. 103.3. Эта сводная таблица вычисляет средние значения без использования формул

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

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

    Источник: excelexpert.ru