Я хочу использовать GROUPBY в Excel.

На чтение 3 мин Опубликовано 30.12.2024

Содержание

Вопрос или проблема
Ответ или решение
Шаги по группировке и вычислению среднего значения в Excel:
Принцип работы формулы:

Вопрос или проблема

У меня есть два столбца Code и Score. Вот так

| Code | Score |
|:----:|:-----:|
| M01  | 4     |
| M05  | 3     |
| M02  | 4     |
| M01  | 5     |
| M04  | 4     |
| M01  | 3     |

Я хочу получить СРЕДНЕЕ каждого score с их соответствующим Code.

Что-то вроде этого ВЫВОДА:

| Code | Average |
|:----:|:-------:|
| M01  | 4       |
| M05  | 3       |
| M02  | 4       |
| M04  | 4       |

Предполагая, что входные данные находятся в столбцах A2:B7 на Sheet1, а вывод на Sheet2. В B2 на Sheet2 (рядом с M01) введите:

=SUMPRODUCT((Sheet1!$B$2:$B$7)*(Sheet1!$A$2:$A$7=Sheet2!A2))/COUNTIF(Sheet1!$A$2:$A$7,Sheet2!A2)

идея: использование (Sheet1!$A$2:$A$7=Sheet2!A2) для ‘генерации’ списка из 0 и 1, затем умножение на список оценок, после деление на количество вхождений M01 в определенный список.

Пожалуйста, поделитесь, если это работает/не работает. ( :

Ответ или решение

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

Шаги по группировке и вычислению среднего значения в Excel:

Подготовка данных:
- Убедитесь, что ваши данные корректно введены в диапазон, например, с A2 до B7 на "Листе1".
Создание уникального списка кодов:
- Перейдите на "Лист2" и создайте уникальный список кодов. Вы можете вручную ввести уникальные коды в столбец A или использовать инструмент "Удалить дубликаты" в Excel.
Использование формулы для вычисления среднего значения:
- В ячейке B2 на "Листе2" введите следующую формулу:
```
=SUMPRODUCT((Sheet1!$B$2:$B$7)*(Sheet1!$A$2:$A$7=Sheet2!A2))/COUNTIF(Sheet1!$A$2:$A$7,Sheet2!A2)
```
- В этой формуле SUMPRODUCT выполняет умножение списка оценок на список логических значений (1 или 0), в зависимости от совпадения кодов. COUNTIF подсчитывает количество вхождений конкретного кода для вычисления среднего значения.
Копирование формулы:
- Скопируйте формулу из B2 в нижние ячейки столбца B на "Листе2", чтобы она рассчитала среднее значение для других кодов.

Принцип работы формулы:

(Sheet1!$A$2:$A$7=Sheet2!A2) генерирует массив логических значений (TRUE/FALSE), где TRUE соответствует совпадению кодов "Код" в "Листе1" и "Листе2".
SUMPRODUCT(...) суммирует произведение оценок на логические значения, таким образом фактически суммируя только те оценки, которые соответствуют нужному коду.
COUNTIF(...) делит на количество вхождений кода, обеспечивая расчет среднего значения.

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