Добавление среднего в сводную таблицу

В этом уроке вы узнаете, как добавить среднее значение в сводную таблицу в Excel. Действительно полезный и простой трюк.

1. Откройте лист Excel, на котором создана ваша сводная таблица.

2. Щелкните в любом месте сводной таблицы. Появится поле Поля сводной таблицы .

3. В разделе поля значений щелкните правой кнопкой мыши сумму количества и настройку поля значения. Появится диалоговое окно. Выберите Среднее на вкладке Суммировать значение по и нажмите ОК.

4. Перетащите количество в поле значения.

5. Окончательный результат будет таким:

Среднее значение только что добавлен в сводную таблицу.



Сводная таблица Excel — среднее вычисленных сумм

Я уверен, что это просто, но как мне получить сводную таблицу для отображения среднего для вычисленной суммы полей? В упрощенном примере я отфильтровал фонд x1, а сводная таблица показывает суммы оставшихся средств на человека. Теперь, как мне получить среднее по человеку (то есть, вычисленное вручную, 3300/3)?

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

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

TIA.

Для каждого запроса вот список полей — если я попытаюсь добавить «среднее значение» в поле значения, оно усреднит суммы фонда, а не сумму фонда на человека. :


1

Вот рабочее решение:

Во-первых, вы должны установить или включить Power Pivot. Цитата из Microsoft:

Power Pivot — это надстройка Excel, которую вы можете использовать для выполнения мощного анализа данных и создания сложных модели данных.

https://support.office. com/en-us/article/Power-Pivot-Powerful-data-analysis-and-data-models-in-Excel-a9c2c6e2-cc49-4976-a7d7-40896795d045? ui = en-US & rs = en-US & ad = US

В более новых версиях Excel Power Pivot уже установлен, и вы можете включить его, перейдя в:

Файл> Параметры> Дополнительно> Данные> Включить надстройки анализа данных: Power Pivot, Power View и Power Map

Хорошо, теперь у вас есть Power Pivot и вы видите вкладку Power Pivot. Выполните следующие действия:

  1. Выберите данные и щелкните значок «Добавить в модель данных» на вкладке Power Pivot.
  2. В окне Power Pivot добавить столбец, который будет подсчитывать различное количество людей в данных. = DISTINCTCOUNT ([человек]) назовите его, например, «DistPersNo». Это важный шаг — Power Pivot позволяет подсчитать уникальные значения в выбранном столбце.
  3. Добавьте еще один столбец с формулой = [amount]/[DistPersNo], назовите его « PersonAverages ».
  4. В окне Power Pivot щелкните« Сводная таблица »и добавьте новую сводную таблицу на свой рабочий лист.
  5. В сводной таблице добавьте« людей »в строки и« количество »к значениям. . Теперь, если вы добавите «PersonAverages» к значениям (их сумму) и отфильтруете фонд «x1», вы получите желаемый результат, т.е. значение 1100.

Надеюсь, что это поможет.

Улучшите этот ответ
ответил Ян 18 ’18 в 13:46
комментировать |

Вот рабочее решение:

Сначала вы должны установить или включить Power Pivot. Цитата из Microsoft:

Power Pivot — это надстройка Excel, которую вы можете использовать для выполнения мощного анализа данных и создания сложных модели данных.

https://support.office.com/en-us/article/Power-Pivot-Powerful-data-analysis-and-data- моделирование-in-Excel-a9c2c6e2-cc49-4976-a7d7-40896795d045? ui = en-US & rs = en-US & ad = US

В более новых версиях Excel Power Pivot уже установлен, и вы можете его включить выбрав:

Файл> Параметры> Дополнительно> Данные> Включить надстройки анализа данных: Power Pivot, Power View и Power Map

Хорошо, так что вы установите Power Pivot сейчас, и вы увидите вкладку Power Pivot. Выполните следующие действия:

  1. Выберите данные и щелкните значок «Добавить в модель данных» на вкладке Power Pivot.
  2. В окне Power Pivot добавить столбец, который будет подсчитывать различное количество людей в данных. = DISTINCTCOUNT ([человек]) назовите его, например, «DistPersNo». Это важный шаг — Power Pivot позволяет подсчитать уникальные значения в выбранном столбце.
  3. Добавьте еще один столбец с формулой = [amount]/[DistPersNo], назовите его « PersonAverages ».
  4. В окне Power Pivot щелкните «Сводная таблица» и добавьте новую сводную таблицу на свой рабочий лист.
  5. В сводной таблице добавьте «людей» к строкам и «количество» к значениям. Теперь, если вы добавите «PersonAverages» к значениям (их сумму) и отфильтруете фонд «x1», вы получите желаемый результат, то есть значение 1100.

Надеюсь, что это поможет.


0

Я бы добавил вспомогательный столбец в столбец D для подсчета уникальных клиентов.

  1. Сортировка данных по людям
  2. В столбце D2 поместите = IF (A2 = A1, 1, 0) и вызовите столбец UniqueCust.
  3. Скопируйте формулу в набор данных для всех строк
  4. Добавьте столбец в сводную таблицу
  5. Создайте формулу в своем сводная таблица, которая называется Avg per Cust = Value/UniqueCust

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

Улучшите этот ответ
ответил 20 марта 2018, 16:53
добавить комментарий |

Я бы добавил вспомогательный столбец в столбец D для подсчета уникальных клиентов.

  1. Отсортируйте данные по лицам.
  2. В столбце D2 поместите = IF (A2 = A1, 1, 0) и вызовите столбец UniqueCust.
  3. Скопируйте формулу в свой набор данных для всех строки
  4. Добавьте столбец в сводную таблицу.
  5. Создайте формулу в сводной таблице под названием Avg per Cust = Value/UniqueCust

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


0

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

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

сводные таблицы

Вторая сводная таблица имеет источник данных как- E3: F5 или до любой строки вам нужно.

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

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

Улучшите этот ответ
ответил 13 июня 2018, 15:06
добавить комментарий |

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

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

сводные таблицы

Вторая сводная таблица имеет источник данных как — E3: F5 или до нужной вам строки.

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

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


-1
  1. Щелкните сводную таблицу, чтобы открыть список полей.
  2. В разделе «Значения», где написано «Сумма суммы», щелкните раскрывающийся список.
  3. Щелкните «Параметры поля значений» и выберите среднее значение из списка во всплывающем окне.

Улучшите этот ответ
отредактировано 10 мая ’18 в 0:52
Stephen Rauch
39.1k1515 золотых знаков7373 серебряных знака9393 бронзовых знака
ответил 10 мая ’18 в 0:33
  • Я набрал ответ и понял, что вы уже опубликовали решение. — Легион Хаоса 01 июня ’18, в 12:01
  • 1
    Это не помогает, просто используйте среднее значение вместо суммы. Плакат хочет первую сумму, чем в среднем по сумме. — rankthefirst 6 июн 2018, в 9:24
добавить комментарий |

  1. Щелкните сводную таблицу, чтобы открыть список полей.
  2. В разделе «Значения», где написано «Сумма суммы», щелкните раскрывающийся список.
  3. Щелкните «Параметры поля значений» и выберите среднее значение из списка во всплывающем окне.


-2

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

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

Сортировать по фонду (обратите внимание на фонд и лицо в разделе меток строк)

и Сортировать по лицам (обратите внимание на обратное расположение средств и лиц в разделе меток строк)

Для упрощения данных вы всегда можете свернуть основные поля .

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

Улучшите этот ответ
ответил 21 февраля 2014, в 23:56
  • 5
    Э … может я что-то упускаю, но как этот ответ связан с вопросом? Где в вашем примере отображается среднее значение рассчитанных сумм? — Ajedi32 13 ноя. 2015, в 14:21
  • 1
    Кто-то оспорил мой флаг; по какой-то непонятной причине кто-то считает этот ответ актуальным. Нет. Это не ответ на заданный вопрос. Он не показывает, как сделать так, чтобы промежуточные итоги группы отображали среднее значение сумм, содержащихся в их дочерних строках. Вместо этого он просто сообщает OP, как изменить порядок групп строк, о чем они не спрашивали. — underscore_d 07 июл. ’17 в 11:34
добавить комментарий |

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

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

Сортировать по фонду (Обратите внимание на «Фонд» и «Человек в метках строк»)

и Сортировать по лицам (обратите внимание на обратное расположение средств и лиц в разделе меток строк)

Чтобы упростить данные, вы всегда можете свернуть основные поля.

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

Оцените статью
logicle.ru
Добавить комментарий