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

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

pivot_calc1.gif

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

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

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

pivot_calc2.gif

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

pivot_calc3.gif

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

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

pivot_calc4.gif

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

pivot_calc5.gif

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

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

pivot_calc6.gif

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

pivot_calc7.gif

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

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

pivot_calc8.gif

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

pivot_calc10.gif

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

pivot_calc9.gif

P.S.

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

pivot_calc11.png

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

pivot_calc12.png

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

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

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

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

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

Ссылки по теме

 


Galina
22.11.2012 23:29:49
Огромное спасибо за статью! После 2х лет проб и ошибок, протряски нашего офисного Excel спеца пришла к выводу что показ % различий в сводной таблице не возможен! Оказывается возможен да еще и со столькими вариациями!!!
Марина
22.11.2012 23:31:51
Подскажите, пожалуйста, как в сводной таблице настроить процент от промежуточного итога (не от основного в конце), те чтобы промежуточный итог был 100% и данные по группе в % считались от промежуточного 100%
22.11.2012 23:34:29
Такая функция есть только в Excel 2010. Правой кнопкой мыши по полю - Дополнительные вычисления - % от родительской строки.
14.04.2018 18:38:12
Николай, не могу изменить дополнительные вычисления для нескольких полей.
пример: последние 2 столбца.  Выделяю группу ячеек и меняется вычисления только в одном поле.
Катя
22.11.2012 23:32:56
Все это чудо интересно, но просто. А вот если бы Вам пришлось посчитать в сводной таблице что-то сложное, но достаточно простое в обычном листе Excel? К примеру, Вам надо сослаться на результат по прошлому периоду, умножив его на разницу между продажами за февраль-январь. Какую бы Вы функцию применили? Собсбвенно, анализ данных из сводных таблиц и не дает покоя уже несколько дней.
22.11.2012 23:36:44
Копайте в направлении вставки Вычисляемых полей (Calculated Field) и Вычисляемых объектов (Calculated Items). Либо использовать сводную как промежуточный этап и вытаскивать из нее данные с помощью функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ и считать дальше самостоятельно.
08.10.2014 09:12:55
Доброго времени суток.

Николай, спасибо за доступное объяснение, вопрос по вычисляемым полям в сводной таблице, создаю поле в котором округляю вверх значения другого поля, как сделать так чтобы в строке итоги суммировались уже округленные значения, а не округлялось вверх итоговое значение исходного столбца?
Леночка
22.11.2012 23:33:37
супер статья!!! очень помогла!!!! пасибки!!!
23.12.2012 02:16:53
Ни как не могу понять, что вычисляет "Индекс"?
17.02.2013 09:12:20
Индекс вычисляет, по словам Microsoft, "относительную важность" элемента в таблице. Вычисляется по формуле:
=(Значение*Общая сумма) / (Сумма по столбцу*Сумма по строке)
Имхо, неочевидная и бесполезная штука - не применял в реальных проектах ни разу.
15.02.2013 12:17:06
Николай, спасибо Вам за такой замечательный сайт!!!
Помогите разобраться в следующем: имеем сводную таблицу, состоящую из 5-ти столбцов: 1 –номенклатура; 2 –сумма по полю План; 3 – сумма по полю Факт; 4 - % выполнения плана; 5 – прогноз выполнения (два последних – это вычисляемые поля). Поле «прогноз выполнения» вычисляется по формуле: Сумма по полю Факт/9*20/Сумма по полю План, - где цифра 9 – это количество отработанных дней в месяце, 20 – количество рабочих дней в месяце. Так как таблица обновляется каждый день, то и формулу соответственно приходится корректировать в ручную на кол-во этих отработанных дней. К сожалению в формуле вычисляемого поля невозможно использовать функции или ссылки на ячейки. Есть ли другой способ, что б в этой формуле само изменялось кол-во отработанных дней в месяце?
17.02.2013 09:15:14
Как вариант, можно добавить в исходную таблицу служебный столбец со значением количества отработанных дней и использовать его в вычисляемом поле.
17.02.2013 14:15:30
Такой вариант уже пробовала, но результат у меня вышел не правильный, тогда я подумала, что идея себя не оправдала, но скорее всего, я ее не довела до конца. Спасибо, буду пробовать по Вашему совету!
18.02.2013 12:30:47
Анна, если своими силами не получится - напишите мне на info@planetaexcel.ru или создайте тему с вопросом на форуме + приложите ваш файл-пример. Чем сможем - поможем!
19.02.2013 13:19:02
19.02.2013 09:04:56
Николай, подскажите, можно ли вычислить в сводной таблице с помощью вычисляемых полей следующее: по строкам - ФИО, по столбцам - дата, в значении - сумма, необходимо вычислить дату первой операции и дату последней операции по каждому ФИО. Я это делал рядом со сводно таблицей с помощью формул =МИН(ЕСЛИ($B6927:$AE6927>0;$B$4:$AE$4;999999)) и =МАКС(ЕСЛИ($B6927:$AE6927>0;$B$4:$AE$4;)) соответственно. Можно ли тоже самое сделать внутри сводной таблицы?
20.02.2013 20:11:30
Закиньте дату не в область столбцов, а в область данных два раза (получится оригинал и копия поля), а потом через правую кнопку мыши по полю - Параметры поля установите для оригинала функцию минимум, а для копии - максимум. Получите самую раннюю и самую позднюю даты по каждой ФИО.
21.02.2013 03:11:44
все гениальное просто!!! сам не догадался... еще не все возможности сводных раскрыл... а как сделать, чтобы минимум и максимум по дате отображался только в Итогах, а в остновном поле нет?
21.02.2013 22:48:14
А вот это никак, к сожалению. Промежуточные итоги еще можно настроить (правой кнопкой по ним - Параметры поля), а вот общий итог всегда имеет ту же методику расчета, что данные в области значений.
22.02.2013 04:29:42
думаю для разработчиков Сводные таблицы хорошее направление деятельности для усовершенствования... ведь если здесь реализовать возможность делать расчеты с помощью сложных формул, то можно творить "такие вещи"...
хотя может быть и ошибаюсь и знаю не все возможности сводных...
применительно к моему примеру мне на основе данных сводной надо было вычислить: общую сумму операций, количество операций, среднее значение одной операции, дату первой и дату последне операции, количество дней между первой и последней операцией... все это (кроме последнего параметра) можно вычислить с помощью сводной за исключением одного минуса - сводная по столбцам увеличивается до неимоверного размера, т.к. при перетаскивании в поле значение нужных данных увеличивается количество столбцов... и количество дней между первой и последней операцией в сводной не вычислить...
28.02.2013 09:57:54
Тут еще большой вопрос - какого вида у вас таблица с исходными данными. Если столбец с датой один, то все проще.
06.04.2013 17:21:03
Мне кажется, что Вам для подобного рода вычислений есть смысл посмотреть на бесплатную надстройку для Excel 2010 от Microsoft которая называется PowerPivot. Подобного рода вычисления делаются там довольно безболезненно (если не считать некоторого времени на освоение нового).
26.02.2013 14:05:16
Подскажите, пожалуйста, как сделать в сводной таблице (2007) такое вычисление.
Есть регионы, в них офисы и в каждом офисе - встречи. Сводную по количеству встреч в каждом офисе и каждом регионе - сделать я могу. Но это не показательно, т.к. в большом регионе (много офисов) и встреч много. Мне нужно посмотреть среднее количество встреч в офисах по регионам (т.е в каждом регионе - количество встреч в этом регионе, деленное на количество офисов в этом регионе).
Спасибо
28.02.2013 09:55:10
Строите сводную, где в области значений будет кол-во офисов и кол-во встреч, а в области строк - регионы. Потом идем на вкладку Параметры - кнопка Поля, Элементы, Наборы (или Формулы) - Вычисляемый объект. Придумываете ему имя и вводите формулу вида =Встречи/Офисы
06.03.2013 02:04:52
В сводной таблице, созданной с помощью PowerPivot в Excel 2010 погашен и не нажимается Вычисляемый объект на вкладке Параметры - кнопка Поля, Элементы, Наборы. А мне необходимо создать вычисляемое поле в данной сводной таблице с процентом выполнения плана (факт/план). Подскажите, пожалуйста, с чем это может быть связано и как мне можно осуществить данный расчет? Заранее благодарна.
09.03.2013 08:12:27
Это лучше на форум - тут вопросы по теме статьи, а не про Power Pivot.
18.03.2013 17:30:52
Подскажите пожалуйста как сделать в сводной таблице вычисление ДЗ. Есть "Контрагенты", есть "наименование продукции" в поле Строка, в поле Столбец "Статус". В поле Данные "Кол-во" и сумма отгрузки и оплаты. Нужно вычислять разницу между суммой отгрузки и суммой поступлений. Никак не могу придумать как это сделать в самой сводной таблице, а не рядом.
11.04.2013 07:50:42
Ставите активную ячейку в область строк, открываете вкладку Параметры - Поля,элементы, наборы - Вычисляемый объект. Вводите имя и формулу вида =Отгрузка-Поступления (с вашими названиями полей, естественно). Жмете ОК.
Если не получится - поэкспериментируйте там же с вычисляемыми полями. Не видя вашей таблицы, точно сказать тяжело.
11.04.2013 09:18:13
Спасибо! Все получилось:)
День добрый. Подскажите, пожалуйста, а как в одной таблице вывести и абсолютные значения и долю от строки.
11.04.2013 07:47:42
Татьяна, кидаете в область значений сводной таблицы ваше поле ДВА РАЗА. Потом правой кнопкой мыши отдельно настраиваете каждое - одно на сумму, второе - на долю (3-я картинка в статье и текст над ней).
25.04.2013 12:00:01
Здравствуйте! Подскажите пожалуйста, как рассчитать среднее значение последних 10 значений в столбце, если информация обновляется каждый день. Заранее спасибо.
09.05.2013 15:18:57
Я бы добавил в исходную таблицу дополнительный вспомогательный столбец, где формулой бы (или руками) напротив 10 последних значений ставил 1, иначе 0. И потом в сводной закинул бы этот столбец в фильтр и вывел среднее по получившимся значениям. Точнее сказать не могу, не видя вашей таблицы.
27.06.2013 11:43:03
Добрый день!
Благодарю Вас за проделанную вами работу!
Создал в сводной таблице "Вычисляемое поле", можно ли применить именно к этому полю фильтр (допустим по названию получателя)?
В идеале хотелось бы получить что-то в виде
Вычисляемое поле = (Вычисл. поле 1 [фильтр 1] +  Вычисл. поле 2 [фильтр 2] + ... +  Вычисл. поле n [фильтр n] ) Возможен ли такой вариант?? Может макросом.
И еще по фильтрам, можно ли как-то фильтровать по принципу содержит ("Моск*", "*тербур*" и др.)?
Спасибо!
01.07.2013 21:08:32
Полноценные фильтры есть только в области строк и столбцов. Причем отдельно по подписям и по значениям.
Подскажите, пожалуйста, чем отличается Доля от суммы по столбцу  (% of column) или
Доля от общей суммы  (% of total) -
значения одинаковые, и от какой именно общей суммы? Спасибо
01.07.2013 21:07:15
Если у вас в сводной таблице один столбец - ничем не отличаются.
Если несколько, то Доля от суммы по столбцу берет каждый столбец за 100% и считает долю внутри них, а Доля от общей суммы берет за 100% общий итого по всей сводной (правая нижняя ячейка сводной).
22.08.2013 23:44:27
Николай, спасибо за добавление к каждому значению объяснение на английском, у меня EXCEL 2010 на английском
если можно подскажите, почему у меня получаются промежуточные суммы, а я хочу итоговую сумму по каждому городу (на вашем примере) Спасибо, Жанна
08.10.2013 11:07:48
Не понял вопрос. Пришлите мне на почту файл с примером или создайте тему на форуме - поможем.
05.10.2013 15:28:37
как сделать ранг от выручки в последней таблице, какие формулы используются в вычисляемом поле и в  сумме значения?
08.10.2013 11:07:07
В последней таблице нет вычисляемых полей - там только сумма и ранг. Сумма будет по умолчанию, а ранг делаем правой кнопкой мыши - Дополнительные вычисления - Сортировка от минимального к максимальному.
16.12.2015 19:55:52
А как сделать это в 2007? Можно как-то исхитриться? Голову уже сломал на подсчете ранга.
27.11.2013 20:57:08
Добрый вечер, Николай!

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

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

Как записать такую формулу?

Работаю в Excel 2013 + интересно, есть ли решение для 2003-го.
04.01.2014 12:58:07
Дария, не думаю, что в сводной такое можно реализовать встроенными средствами.
Я бы делал отдельный столбец с формулами рядом со сводной или использовал вместо сводной функции выборочного суммирования СУММЕСЛИМН (SUMIFS).
16.12.2013 22:12:10
Можно ли добавить дополнительные "Промежуточные итоги" для сводной таблицы, содержащей большую структуру - столбцов.
Так, чтобы эти дополнительные итоги - показывали итоги по каждой структуре.
Например, есть сводная таблица по месяцам продаж (строки) по Магазинам, Маркам, Цветам товара (столбцы).

Хотелось бы увидеть в столбцах: Общие итоги (+), Итоги по Магазину (+), Итоги по Марке (- не дает, только внутри каждого магазина), Итоги по Цвету (+), Итоги по Магазину-Марке(+), Итоги по Марке-Цвету(- не дает), Итоги по Магазину -Цвету (не дает).....

Итого 7 итогов: 4 могу сделать, а 3 не получается ( в одной таблице)...... Приходится делать надстройку поверх Сводной....
04.01.2014 12:59:06
Иван, тут все зависит от конкретной конструкции вашей сводной. В общем случае: правой кнопкой мыши по полю - Промежуточный итог не помогает?
05.01.2014 13:39:15
Спасибо, уже разобрался!!!

Сводная таблица и Промежуточные итоги - нужны были для того, чтобы с помощью кнопок (чекбокс) и формул "СуммЕсли" из большой БД показать в маленькой таблице определенные динамические данные.
Так вот Промежуточных итогов не хватало (они показывают не ВСЕ итоги), и в конце концов, пришлось вообще отказаться от Сводной таблицы.
Теперь, с помощью кнопок и формулы Массива "Сумм" напрямую из БД без участия Сводной и Итогов берутся динамические данные.
Формула массива позволила сократить расчеты в 10 раз, отказаться от ненужных Сводных таблиц и неполных Промежуточных итогов.
За формулами Массива будущее!!!! )))
08.01.2014 10:24:16
Формулы массива - штука классная, но медленная. Вот вырастет у вас исходная таблица до нескольких тысяч строк - начнутся "тормоза", поверьте :)
05.03.2014 00:23:57
Согласен!
Сейчас работаю с 15.000 строками, и при нажатии кнопки выбора на обработку таблицы уходит 3-4 секунды.
Планирую увеличить таблицу до 50.000 строк - наверное, тогда и начнутся настоящие тормоза )
24.01.2014 17:12:25
Спасибо громадное за ваш сайт, за ваши уроки, приемы и хитрости. Постоянно обращаюсь за вашей мудростью и опытом!
Возник вопрос.
Чаще всего сводные таблицы используются для подготовки всевозможных отчетов. И в этих отчетах нужно сравнивать разные периоды. Я знаю про дополнительные вычисления отличие и приведенное отличие, где мы сравниваем все итоги с каким либо значением или с предыдущим/следующим значением.
А возможно настроить в сводной таблице сравнение месяц одного года с таким же месяцем другого года (например январь 2013 с январем 2012, и т.д.)???
05.03.2014 00:22:26
Напрямую нет. Такая возможность есть при построении сводных на основе OLAP-кубов или в Power Pivot.
В обычных сводных таблицах для этого можно попробовать использовать вычисляемые объекты (не поля), но, скорее всего, придется дорабатывать таблицу исходных данных.

Вообще говоря, Мария, спасибо за идею - надо будет отдельную статью про это написать. Интересная задачка - поставлю себе в план :)
07.03.2014 16:18:45
Наверное, вопрос из той же серии.
В исходной таблице данных содержится и план, и факт.
План/факт     ПодразделениеСумма
планА 100
план Б 150
фактА80
фактБ120

С помощью сводной таблицы легко можно сделать отчет с планом и фактом:
Подразделениеплан факт
А10080
Б150120

А вот как бы сделать еще и расчет отклонений справа от факта?

Через "дополнительные вычисления" это сделать можно, но получается коряво, т.к. помимо столбца собственно с отклонением факта появляется еще и столбец с отклонением плана от плана (пустой).
XL 2010
28.02.2014 10:16:46
Добрый день, Николай

А можно в сводной таблице сделать подсчет именно уникальных значений?. Например, есть большая таблица с продажами, у каждого товара есть номер заказа. Поскольку в одном заказе может быть чем один товар, то если мы просто выбираем "кол-во по полю заказ", то в сводной отображается не кол-во заказов, а кол-во товаров в заказах за конкретный период, а хотелось бы именно кол-во заказов
В принципе можно попробовать через дополнительный столбец, поставить ключик напротив уникальных номеров заказов, но может есть более простой способ сделать это в самой сводной?
Z
28.02.2014 18:41:08
А можно в сводной таблице сделать подсчет именно уникальных значений?
Да, если XL - 2013. Или сводную по сводной.
04.03.2014 11:54:33
А если 2010?
05.03.2014 00:18:12
Вам же Z ответил: "Сводную по сводной".
То есть делаете сводную, в поле строк кидаете нужное поле - получите там список уникальных элементов без повторений, т.к. их в строках или столбцах быть не может. И строите затем еще одну сводную, используя предыдущую как исходные данные, где и подсчитываете количество уникальных.
03.04.2014 15:16:40
Подскажите, а можно ли с помощью сводной таблицы вывести приведенное отличие (% изменения) не просто от предыдущего месяца (периода), а от последнего ненулевого? Например, есть динамика по стоимости какого-либо продукта с января по декабрь. В марте и июне продажи=ноль. Нужно, чтобы % изменения в июле и апреле считался не от марта и июня, а от февраля и мая соответственно. Уже голову сломала, не знаю как сделать.
15.04.2014 10:12:01
Стандартными средствами - точно никак. Тут даже вычисляемые поля не помогут :(
Я бы делал сводную и вытаскивал из нее данные с помощью функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ в другую таблицу, где уже можно использовать ЕСЛИ для проверки и сравнения.
Примерно так.
Точнее, не видя файла, сказать сложно.
15.04.2014 11:12:20
Спасибо за ответ. Я выкладывала пример здесь на форуме, но так никто и не откликнулся :(  Наверное, муторная задача.
http://www.planetaexcel.ru/forum/index.php?PAGE_NAME=message&FID=1&TID=56428&MID=470774#message470774
15.04.2014 13:01:57
Однозначно муторная. Если есть острая необходимость и хотя бы минимальный бюджет, то можно попробовать продублировать в ветке Работа. С большой вероятностью кто-нибудь возьмется, думаю.
14.04.2014 17:37:03
Николай, если возможно, примите пожелание, в одном из будущих "приемов" расскажите про про подсчет количество уникальных значений в сводной EXCEL 2013, и приемом Сводной по Сводной в предыдущих версиях.
15.04.2014 10:15:57
Принято :) Думаю, добавлю это сюдакак один из способов - будет логично.
Николай, подскажите такую вещь. Отличие и приведенное отличие действует классно, если период времени один - только месяцы или только годы. А если нужно сравнить декабрь 2013 с январем 2014 года? Как вывести эту динамику? Благодарю за помощь.
вот файл примера: https://onedrive.live.com/redir?resid=B37BE04A6CC44BCB!7426&authkey=!AMskIQNihfFKKp4&ithint=file%2c.xlsx
07.05.2014 12:02:56
Я бы, наверное, с ходу просто добавил в исходные данные еще один столбец с порядковым номером месяца (декабрь 2013 будет, допустим 12-м, а январь 2014 уже 13-м и т.д.) И строил потом сводную и считал динамику по этому номеру, а не по фактической дате.
Либо вытаскивать данные из сводной в отдельную таблицу с помощью функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ и сравнивать уже вручную формулами.
J C
31.05.2014 21:46:48
Николай, а как бы вместо среднего арифметического в поле Итогов отобразить среднее геометрическое ??.. (хоть бы это было возможно в сводной)... или как-нибудь через промежуточные итоги или вычисления в сводной? я вижу что Произведение есть, Количество есть - т е надо по строкам из этих произведений элементов извлечь корень степени равной их количеству (ну или возвести в степень 1/n, где n-количество элементов)...это только через доп поле? но внизу Общий от Итогов тоже по такому принципу должен быть (ср геом) - возможно ли как-нибудь простыми методами? Заранее спасибо...
09.06.2014 12:19:40
Стандартными средствами - точно никак. Я бы вытаскивал данные из сводной в отдельную таблицу с помощью функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ и считал потом уже формулой СРГЕОМ.  
Добрый день, а подскажите пожалуйста можно ли в поле значения отобразить текст? Мне нужно чтобы там отображались значения такого формата "ТО6", "Т48" а получаются только нули да единички))
14.08.2014 13:36:02
Скажите, почему не активна "Сортировка...." в дополнительных вычислениях? Спасибо ))
19.10.2014 12:30:28
Добрый день, Николай
Подскажите пожалуйста, как построить несколько сводных таблиц с отдельным кэшем для каждой в одной книге при использовании одного и того же исходного файла. в 2010 excele
Спасибо
18.11.2014 15:59:12
Здравствуйте, Николай. Как отключить подсчет любых вычислений (кроме итоговой строки) в выборочных столбцах сводной таблицы и есть ли возможность использовать в них формулу? Поясню на примере:в исходной таблице имеется база данных по месяцам и годам (ввела отдельный столбец "месяц" и отдельный "год") следующих значений (числовых): данные на начало периода, обороты и данные на конец периода-это столбцы исходной таблицы, в строках таблицы-название покупателя. В сводной таблице у меня появилась возможность видеть сводную информацию по каждому покупателю по заданному периоду, НО данные на начало и конец периода тоже выводятся как сумма этих значений по периодам, а мне нужно чтобы данные на начало периода в сводной таблице равнялись данным на начало соответствующего периода  исходной таблицы, а данные на конец периода сводной таблицы вычислялись бы формулой по данным сводной таблицы. Можно ли реализовать такую возможность в сводных таблицах или надо использовать другие возможности Excel? Спасибо.
22.03.2015 03:08:17
Помогите срочным советом, как среднее применить к итогу только, и делать таблицу как я прилагаю на картинке ( фото из лабораторной в стиле "как надо чтоб было), пересмотрела все Ваши видео, но у меня даже среднее работает не так, как у Вас - применяется к конкретной строке и все:(

[img]file:///C:/Users/8ADB~1/AppData/Local/Temp/msohtmlclip1/01/clip_image001.jpg[/img]
22.11.2015 20:09:47
Добрый вечер! подскажите пжл как можно посчитать, например, % выполнения плана, исп-я сводную таблицу по 2 столбцам одновременно, т.к. план - это 1 столбец, факт - второй, а в третьем мне нужно посчитать %, но не просто, забив формулу2/1, а с помощью "доп.вычислений". спасибо
06.12.2015 13:02:05
Алексей, имеется ли возможность прописать формулу ВПР в вычисляемом поле для сводной таблицы?
Подскажите как посчитать скритие ячейки , сумировать ?
04.02.2016 16:50:34
Николай, здравствуйте! Ликбез - полезная штука, на все времена. Но вот странное дело, уже давно приходиться считать вручную сумму средних значений в сводной таблице. На слух - задачка тривиальная. Но в Эксель тупо не предусмотрен такой функционал. А может быть это я просто не знаю какого-то секрета? (На вопрос: "А зачем вам это?", отвечаю: Есть такие массивы данных, где для сравнительного анализа численных характеристик разных объектов дублирование повторяющихся характеристик в разных строках данных неизбежно. В итоге, приходиться вычислять сумму всех значений столбца, которые состоят из пар, троек, четверок, и т.д., одинаковых значений.).
Добрый день! Подскажите, пожалуйста, добавила в таблицу % от суммы по родительской строке, считает все правильно, после добавления новых данных в базу и обновления сводника пересчитывает тоже верно Но вот незадача, после закрытия документа, когда открываю снова и обновляю сводник, % от суммы по родительской строке "слетает", нажимаю правой кнопкой на этот столбец, выбираю доп.вычисления , а там галка "без вычислений". Каждый раз приходится заново выставлять % от суммы по родительской строке. Почему так происходит?
20.07.2017 15:49:18
Добрый день!
Как можно создать сводную таблицу, так чтобы новые введенные данные отображались автоматом в этой таблице. Я новичек в экселе. По вашей статье все классно, но новые данные или измененные не отображаются. Надо как бы заново настраивать конструктор. Может как то по другому делается? У меня шеф может в любой момент может спросить итоги продаж, а данные накладных я ввожу каждый день. И чтобы эти введеные данные вводились автоматом. Подскажите заранее спасибо.
k m
04.08.2017 09:53:05
Добрый день! Подскажите, пожалуйста, как в сводной таблице в качестве результатов вывести данные из исходной таблицы, чтобы к ним не применялись вычисления, и они не группировались, не применялась операция для сведения данных, при условии, что в исходной таблице при заданных параметрах эти значения уникальны.
Заранее спасибо!

Вопрос решен!! Спасибо!  
17.01.2018 15:05:20
Всем привет! Кто знает как в сводных таблицах показать долю значения, если переменная бинарная? Есть три марки, которые отмечались опрошенными. Т.е. в таблице есть значения 1, показывающие, что марка была выбрана и 0 (марка не выбрана).

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

Если указывать долю по столбцам - выдает 100%, что логично. Есть еще вкладка Доля, если там выбрать переменную и указать что нужна доля именно кода 1 - выдает Н/Д.

Всем спасибо!

Пример тут
https://drive.google.com/file/d/1kA3MR0FjY_xAt7FyL6UQ-c3JV8c4hn9l/view?usp=sharing
26.01.2018 13:41:40
Николай, добрый день!

Спасибо за статью! Есть вопрос:

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

Т.е. в столбцах идёт сумма нарастающим итогом и нужно в общем итоге видеть максимальное значение, а по строкам общий итог нужен именно в сумме значений.  
14.04.2018 18:35:47
03.10.2018 10:45:04
Николай, здравствуйте!
Скажите, пожалуйста, а можно ли создать связь между сводными таблицами? Допустим, есть 2 показателя - продажи в сумме и продажи в количестве. Этот показатель в сводной таблице выносится в поле Значения. Есть 3 сводных таблицы на одном листе: таблица с исходными данными, с абсолютным отклонением  и приведенным отклонением (как описано в данной статье). Можно ли создать какую-либо связь, чтобы автоматически происходила смена значений в этих 3 таблицах при выборе в поле Значения продаж в сумме или в количестве?
15.11.2019 15:37:03
Николай, здравствуйте!
Подскажите пожалуйста, не могу никак показывать разницу минимальных значений в сводной. В исходнике добавил обычную формулу, которая выдает разницу (например, текущего сезона и прошлого). Так как данных много, создал сводную.
Допустим есть несколько групп, в каждой группе разное количество человек и данные по ним за два разных периода. Необходимо найти минимальное значение (не самого человека) по каждой группе за два периода и их разницу. Проблема заключается в том, что при минимальных значениях по каждому периоду разница выходит другая (Пример: в Группе В есть сотрудники Вакуленко Д. (данные за 1 период - 346, второй - 362), Рыбкин М. (1 период - 373, второй - 396) и Соловьев Е. (1 период - 400, второй - 400). При группировке в Группе В минимальные значения за первый период = 346, второй = 362, а разница должна быть -16 (минус 16), а выходит разница -23 (минус 23), то есть минимальная по Рыбкину М., а не Вакуленко Д..
Не знаю. смог ли обьяснить или нет, есть пример - Пример
mo8
26.03.2020 13:48:58
Здравствуйте!
Добавить кварталы в строки к месяцам
можно только через добавление доп.столбца в исходные данные?
Никакие промежуточные итоги не умеют этого делать?
В моей задаче есть месяца разных лет, а мне нужно сравнить по годам
есть такая возможность без доп.вычислений в исходных данных?
Спасибо!
upd: нашёл возможность ручной группировки:
активировать таблицу\Параметры\Группа по выделенному
хотя бы так
Николай, присоединяюсь ко всем благодарностям в Ваш адрес! Сама пользуюсь вашей книгой готовые решения. Но вот нигде не смогла найти ответ на простой как мне кажется вопрос: При протягивании формулы в сводной таблице, копируются значения из первой ячейки, как решить данную задачу. Спасибо!
19.01.2021 09:05:54
Добрый день, не могу разобраться и найти решение в интернете. Ситуация такая, необходимо, чтобы среднее значение в сводной показывалось не как 1 500 000, а как 1,5 млн в сводной. Когда пишу формулу в вычисляемом поле, то почему-то выходит в млн, но не среднее значение строк, а сумма. Даже вроде как пишет, что это "Среднее по полю В МЛН", но по факту все равно это сумма строк деленная на 1 000 000...помогите, пожалуйста
19.05.2021 10:30:05
Добрый день. а возможно ли сделать суммирование в сводной по модулю?
24.09.2021 14:23:59
Здравствуйте. Похожий вопрос: нужно в сводной таблице суммировать только положительные значения столбца исходной таблицы. Но кажется суммесли не работает в режиме "вычисляемое поле"
21.07.2023 01:30:42
Здравствуйте, Николай! Подскажите в какую сторону "копать"? В сводной таблице необходимо отражение значений показателя заболеваемости (на 100 тысяч населения). Умная таблица содержит большой список пациентов, зарегистрированных с болезнью в разные годы, в разных населенных пунктах, где численность населения, естественно, разница по годам. Пытаюсь создать сводную, где по строкам - населенный пункт, по столбцам - годы, значения - этот самый показатель.
Наверх