Вопрос или проблема
Пример данных:
| Год | Деятельность | Л | В |
|---|---|---|---|
| 2019 | x | 5 | 9 |
| 2015 | z | 1 | 0 |
| 2015 | x | 5 | 4 |
| 2020 | z | 4 | 5 |
| 2020 | y | 7 | 3 |
Используя сводную таблицу, я могу усреднить Л по “Деятельности”, и усреднение по “Деятельности” выглядит следующим образом:
| Деятельность | Л | В |
|---|---|---|
| X | 5 | 6.5 |
| Y | 7 | 3 |
| Z | 2.5 | 2.5 |
Что мне нужно, так это усреднить по Деятельности за весь год (где Л + В). Я пробовал “вычисляемое поле”, но оно дает только “1” для всех видов деятельности, используя эту формулу:
= (SUM('Л $')+SUM('В $'))/COUNT(Деятельность)
И я пробовал
= ('Л $'+'В $')/COUNT(Деятельность)
Я могу суммировать Л + В по деятельности в сводной таблице, и я могу посчитать количество видов деятельности в сводной таблице. Тем не менее, когда я пытаюсь создать вычисляемое уравнение, количество видов деятельности не является параметром для использования в формуле (среднее =сумма / количество).
Как я могу рассчитать в сводной таблице среднее значение за весь год (Л+В) по деятельности?
Пожалуйста, ознакомьтесь с моими дальнейшими шагами, если есть недопонимание, дайте нам знать.
Шаг 1
Выберите диапазон всех данных > щелкните правой кнопкой мыши > Получить данные из таблицы/диапазона, затем вы импортируете данные в Power Query в качестве таблицы.
Шаг 2
Нажмите Добавить столбец > Пользовательский столбец > Вставьте В, введите “+” и вставьте Л. Вы также можете ввести имя для нового столбца, затем нажмите ОК.
Шаг 3
Нажмите Файл > Закрыть и загрузить в > Отчет сводной таблицы.
Шаг 4
Переместите поля в разные области по мере необходимости. Следующее изображение – мой пример.
Ответ или решение
Чтобы вычислить среднее значение по двум столбцам (L и H) с использованием сводной таблицы в Excel, следуйте пошаговой инструкции, описанной ниже. Мы будем использовать Power Query для подготовки данных и создания сводной таблицы, которая будет отображать нужные вам результаты.
Шаг 1: Импорт данных в Power Query
- Выберите диапазон ваших данных. Левый клик по любой ячейке вашей таблицы данных.
- Правый клик и выберите "Получить данные из таблицы/диапазона". Это откроет Power Query.
- Убедитесь, что ваш диапазон данных правильно распознан Power Query.
Шаг 2: Создание нового столбца
- Добавьте новый столбец. В Power Query перейдите на вкладку "Добавить столбец" и выберите "Пользовательский столбец".
- Введите формулу. В окне, которое откроется, введите название нового столбца (например, "Total") и формулу:
= [L] + [H]Это создаст новый столбец, который будет суммировать значения столбцов L и H для каждой строки.
Шаг 3: Завершение работы с Power Query
- Закройте и загрузите данные. Нажмите "Файл" -> "Закрыть и загрузить" и выберите "Сводная таблица".
- Excel создаст новую сводную таблицу на основе ваших данных с добавленным столбцом "Total".
Шаг 4: Создание сводной таблицы
- Перетащите поле "Activity" в область "Строки" вашей сводной таблицы.
- Перетащите поле "Total" в область "Значения". Убедитесь, что оно отображает среднее значение. Для этого, щелкните правой кнопкой мыши на значении в области "Значения", выберите "Настройка поля значения" и выберите "Среднее".
Шаг 5: Форматирование и визуализация
- Отформатируйте сводную таблицу по своему усмотрению, добавляя фильтры или настраивая внешний вид для улучшения читаемости.
- Проверьте результаты. Ваша сводная таблица теперь должна показывать средние значения по активности с учетом суммированных столбцов L и H.
Важные замечания
- Если вместо среднего значения в сводной таблице получаются другие агрегированные значения, перепроверьте формулы и убедитесь, что данные корректно сгруппированы.
- Power Query автоматически обновляет данные при изменении источника, что удобно для работы с большими и изменяющимися наборами данных.
Следуя приведённым выше шагам, вы сможете эффективно рассчитывать среднее значение по двум столбцам в сводной таблице. Это не только ускорит процесс анализа, но также поможет в принятии более обоснованных бизнес-решений.