Вопрос или проблема
Найдите разницу в днях в сводной таблице
Я пытаюсь найти разницу в днях в сводной таблице или просто в самой таблице. У меня следующая ситуация (я добавлю таблицу для копирования внизу своего вопроса)
У меня есть несколько столбцов, и я хочу рассчитать разницу во времени (дни) внутри одной группы AAA со значениями в столбце DDD (красные цифры в примере ниже). И, если возможно, также разницу во времени (дни) внутри одной группы AAA и одной группы BBB со значениями в столбце DDD (зеленые цифры в примере ниже).
Это таблица с данными:
(У меня недостаточно баллов репутации, чтобы вставить изображение)
А это сводная таблица, рядом с которой находятся значения, которые я хочу найти автоматически. Красные значения являются для меня наиболее важными, но зеленые значения также будут полезны.
(ссылка на изображение)
(У меня недостаточно баллов репутации, чтобы вставить изображение)
Резюме того, что я хочу:
Разница во времени в днях между первым и последним днем в каждом слое сводной таблицы. Так что в примере сводной таблицы, дни в красном, зеленом и синем. Элемент 44 (столбец AAA) дает лучший пример значений.
Таблица для копирования:
| AAA | BBB | CCC | DDD |
|---|---|---|---|
| 11 | 0 | A | 10-1-2020 |
| 11 | 0 | B | 11-2-2020 |
| 11 | 0 | C | 14-3-2020 |
| 11 | 0 | D | 15-4-2020 |
| 22 | 0 | A | 15-3-2024 |
| 22 | 0 | B | 20-3-2024 |
| 22 | 0 | B | 23-3-2024 |
| 22 | 0 | C | 24-3-2024 |
| 22 | 0 | D | 24-3-2024 |
| 33 | 0 | A | 2-3-2022 |
| 33 | 0 | B | 3-3-2022 |
| 33 | 0 | C | 4-3-2022 |
| 33 | 0 | D | 5-3-2022 |
| 33 | 1 | A | 6-3-2022 |
| 33 | 1 | B | 7-3-2022 |
| 33 | 1 | B | 8-3-2022 |
| 33 | 1 | B | 9-3-2022 |
| 33 | 1 | B | 10-3-2022 |
| 33 | 1 | C | 11-3-2022 |
| 33 | 1 | C | 12-3-2022 |
| 33 | 1 | C | 13-3-2022 |
| 33 | 1 | D | 14-3-2022 |
| 44 | 0 | A | 6-8-2022 |
| 44 | 0 | A | 3-11-2022 |
| 44 | 0 | A | 31-1-2023 |
| 44 | 0 | A | 30-4-2023 |
| 44 | 0 | B | 28-7-2023 |
| 44 | 0 | B | 25-10-2023 |
| 44 | 0 | B | 22-1-2024 |
| 44 | 0 | C | 20-4-2024 |
| 44 | 0 | D | 18-7-2024 |
| 44 | 0 | D | 15-10-2024 |
| 44 | 1 | A | 12-1-2025 |
| 44 | 1 | B | 11-4-2025 |
| 44 | 1 | C | 9-7-2025 |
| 44 | 1 | D | 6-10-2025 |
| 44 | 2 | A | 3-1-2026 |
| 44 | 2 | B | 2-4-2026 |
| 44 | 2 | C | 30-6-2026 |
| 44 | 2 | D | 27-9-2026 |
Вы не против использовать формулы Excel?
Если нет, вы можете посмотреть мой обходной путь:
Сначала используйте функцию “Группировать” в Excel, чтобы получить группы. Я показал шаги в вашей другой теме:
Excel, как узнать, какие группы в одном столбце имеют различные значения в другом столбце
Вы получите таблицу, похожую на следующую:
Затем вы можете использовать функции Maxifs и Minifs.
- Разницы для CCC:
=MAXIFS(Table2[DDD],Table2[AAA],G2,Table2[BBB],H2,Table2[CCC],I2)-MINIFS(Table2[DDD],Table2[AAA],G2,Table2[BBB],H2,Table2[CCC],I2)
- Разницы для BBB:
=MAXIFS(Table2[DDD],Table2[AAA],G2,Table2[BBB],H2)-MINIFS(Table2[DDD],Table2[AAA],G2,Table2[BBB],H2)
- Разницы для AAA:
=MAXIFS(Table2[DDD],Table2[AAA],G2)-MINIFS(Table2[DDD],Table2[AAA],G2)
Ответ или решение
Конечно! Я помогу вам разобраться с этой задачей, используя формулы Excel для расчёта разницы в днях между датами в сводной таблице.
Шаги по решению задачи
-
Подготовка данных: Убедитесь, что ваш столбец с датами (DDD) расположен в формате даты, чтобы Excel мог корректно выполнять математические операции с ними.
-
Создание промежуточной таблицы: Для наиболее удобного подсчёта разницы в днях между минимальными и максимальными значениями по заданным группам (AAA, BBB, CCC) вы можете создать новую таблицу, в которую будете переносить результаты. Сначала сделаем группировку.
-
Использование функции MINIFS и MAXIFS:
-
Для расчёта разницы между первым и последним значением по колонке CCC:
=MAXIFS(Table2[DDD], Table2[AAA], G2, Table2[BBB], H2, Table2[CCC], I2) - MINIFS(Table2[DDD], Table2[AAA], G2, Table2[BBB], H2, Table2[CCC], I2) -
Для расчёта разницы между первым и последним значением по колонке BBB:
=MAXIFS(Table2[DDD], Table2[AAA], G2, Table2[BBB], H2) - MINIFS(Table2[DDD], Table2[AAA], G2, Table2[BBB], H2) - Для расчёта разницы между первым и последним значением по колонке AAA:
=MAXIFS(Table2[DDD], Table2[AAA], G2) - MINIFS(Table2[DDD], Table2[AAA], G2)
-
-
Форматирование обычной таблицы: После того как вы создали формулы, просто скопируйте их для всех групп в вашей промежуточной таблице. Убедитесь, что ссылки на ячейки корректны.
-
Форматирование результата: Убедитесь, что результат формул формата даты (если вы хотите рассмотреть разницу в днях, вы можете оставить их просто в числовом формате).
- Проверка результатов: Проверьте, что разница по каждой группировке соответствует вашим ожиданиям, сравнив результаты с оригинальными данными.
Пример работы с вашими данными
Допустим, в вашей промежуточной таблице используются:
- G2 для группы AAA,
- H2 для группы BBB,
- I2 для группы CCC.
Вы просто подставите эти ссылки в предложенные выше формулы.
Заключение
Таким образом, используя функции MINIFS и MAXIFS, мы можем легко находить разницу в днях между первыми и последними значениями в разных группировках в вашей сводной таблице. Если у вас есть дополнительные вопросы или вам требуется ещё помощь по настройке, не стесняйтесь спрашивать!