Создайте сводную таблицу в зависимости от двух дат.

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

У меня есть таблица Excel со статистикой по вопросам, обработанным моей командой:

|Дата открытия|Дата закрытия|Описание|…|
|————-|————-|——–|—|
|2020-03-05 |2020-04-23 |foo |…|
|2020-04-30 | |bar |…|

Я хотел бы иметь дашборд, который бы отображал в конце каждого месяца средний “возраст” открытых вопросов.

Сводная таблица, которую я пытаюсь создать, будет выглядеть, по состоянию на сегодня (27 мая):

31 марта – 26 дней
30 апреля – 0 дней
27 мая – 28 дней

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

Я преобразовал исходные данные в таблицу (Ctrl+T), затем переименовал таблицу в case_data.

Затем я добавил столбец под названием “Дни в открытом состоянии” с этой формулой:

=DAYS(IF([@[Дата закрытия]]<>“”,[@[Дата закрытия]],TODAY()),[@[Дата открытия]])

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

После этого я осознал, что это было не обязательно, но оставил так, потому что почему бы и нет?

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

Если это интересно, вы можете получить список дат между двумя датами, используя функцию SEQUENCE. Это полезно для корректировки високосных лет. Вот формула в ячейке G2:

=DATE($F$2,1,SEQUENCE(DAYS(DATE($F$2,12,31),DATE($F$2,1,1))+1))

Год указан в ячейке F2.

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

Проще говоря, если дата и после даты открытия, и меньше или равна дате закрытия (или сегодня, если все еще открыта), то вернуть 1. Взять сумму по всем случаям, чтобы посчитать, сколько случаев было открыто в этот день.

=SUMPRODUCT(–(G2>case_data[Дата открытия]),–(G2<=IF(case_data[Дата закрытия]="",TODAY(),case_data[Дата закрытия]))) Наконец, чтобы рассчитать количество дней, в течение которых случай был открыт, используйте это: =IF(H2=0,0,I1+H2) Это означает, если сегодняшнее значение для открытых случаев равно нулю, то и дни в открытом состоянии должны быть равны нулю. Если не ноль, то дни в открытом состоянии должны равняться сумме сегодняшних открытых случаев плюс значение дней в открытом состоянии с предыдущего дня. Таким образом, в первый день после первого открытия (то есть 6 марта) открытые случаи составляют 1, а дни в открытом состоянии - 1. На все дни между датой открытия и датой закрытия количество открытых случаев равно 1, и дни в открытом состоянии итеративно увеличиваются, пока количество открытых случаев снова не станет нулем. То есть: Затем: Чтобы вернуться к вашему вопросу, если мы отфильтруем таблицу по указанным вами датам: Это должно быть 27 дней в открытом состоянии между 30 апреля и 27 мая, а не 28 (предполагая, что вы не считаете дату открытия первым днем). Чтобы получить среднее количество дней в открытом состоянии на любую заданную дату, нам нужно учитывать ситуации, когда несколько случаев открыты в один и тот же день. Поэтому я добавил немного фиктивных данных: Вы видите, что это изменило столбец "открытых случаев" в марте: Я добавил "среднее количество дней в открытом состоянии" с этой формулой: =IFERROR(I2/H2,0) 6 марта был открыт один случай. Таким образом, дни в открытом состоянии были 1. 8 марта были открыты 2 случая. Один из этих случаев был открыт 3 дня, а один был открыт 1 день. Таким образом, общее количество дней в открытом состоянии на 8 марта составило 4, а среднее количество дней в открытом состоянии составило 4 / 2 = 2. Вы можете добиться этого, следуя следующим шагам: Создайте новый столбец в исходных данных с названием "Возраст открытых вопросов (дни)". Я создал источник данных как таблицу с некоторыми случайными значениями дат и рассчитал "Возраст открытых вопросов (дни)" следующим образом: Вы видите, что для расчета возраста я использовал сегодняшнюю дату как ссылочную точку. Затем добавьте сводную таблицу (в моем примере я добавил её на новый лист). В окне списка полей пожалуйста добавьте поля источника данных (путем выбора элемента или перетаскивания) в нижеуказанные "области" сводной таблицы: Элемент списка полей > ОБЛАСТЬ

i. Открытые > СТРОКИ

ii. (Перетаскивание) Открытые > ЗНАЧЕНИЯ (мы будем использовать это для подсчета количества открытых вопросов)

iii. Закрытые > ФИЛЬТРЫ

iv. Возраст открытых вопросов (дни) > ЗНАЧЕНИЯ

Изначально сводная таблица выглядела бы примерно как указано ниже:

В поле фильтра Закрытые (Строка 1) установите галочку “Выбрать несколько элементов”, снимите галочку “Все”, наконец, установите галочку (пустые)

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

Если у вас отображаются отдельные даты, то:

Щелкните правой кнопкой мыши на одной из ячеек в столбце “Метки строк” > Группировка > Выберите “Месяцы”

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

Вы также можете выбрать группировку по “Годам”, когда ваш источник данных включает даты из нескольких лет. Далее, щелкните правой кнопкой мыши на одной из ячеек в столбце “Сумма по возрасту открытых вопросов (дни)” > Параметры полей значений > В разделе “Сводка по полю значений” выберите Среднее.

Чуть ниже вы можете настроить количество десятичных знаков в полученных средних. Нажмите кнопку “Формат чисел” > Категория: Число > Настройте количество десятичных знаков как вам угодно.

После применения шагов с 4 по 7 вы должны увидеть сводную таблицу, как указано ниже:

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

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

Создание сводной таблицы в Excel для анализа статистики по открытым вопросам может показаться сложной задачей, особенно если вы стремитесь получить усредненные данные по "возрасту" проблем по состоянию на конец каждого месяца. Ваша цель — получить отчет, который будет показывать средний возраст открытых проблем на конец каждого месяца, подобно тому, как это выглядит на 31 марта, 30 апреля и 27 мая. Для достижения таких результатов можно использовать различные функции Excel, такие как Сводные таблицы, которые помогут вам эффективно обрабатывать и визуализировать данные.

Теория

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

Пример

Вы предоставили таблицу со следующими данными:

|Дата открытия|Дата закрытия|Описание|...|
|-------------|-------------|--------|---|
|2020-03-05   |2020-04-23   |foo     |...|
|2020-04-30   |             |bar     |...|

Вы также добавили столбец "Дни в открытом состоянии" с формулой:

=DAYS(IF([@[Date closed]]<>"",[@[Date closed]],TODAY()),[@[Date opened]])

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

Применение

Шаги для построения сводной таблицы и расчета среднего возраста открытых вопросов:

1. Подготовка данных:
   Сделайте копию исходной таблицы и используйте ее как источник данных для сводной таблицы. Это обеспечит сохранность оригинальных данных.

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

3. Создание сводной таблицы:

   • Вкладка "Вставка" > "Сводная таблица".
   • Выберите ваш диапазон данных (case_data) в качестве источника.
   • Укажите новое место на листе или создайте новый лист для размещения сводной таблицы.

4. Настройка полей сводной таблицы:

   • Переместите поле "Дата открытия" в область "Строки".
   • Переместите поле "Дни в открытом состоянии" в область "Значения". Убедитесь, что выбрано "Среднее" для получения среднего значения.
   • В разделе фильтров добавьте "Дата закрытия" и установите фильтр на значение "(пусто)" для отображения только открытых вопросов.

5. Группировка данных:
   Если в колонке сводной таблицы строками идут отдельные даты вместо месяцев, выполните:

   • Правая кнопка мыши на колонке "Дата открытия" > "Группировать" > Выберите "Месяцы" и, если нужно, "Годы".
   • Это автоматически соберет все данные по месяцам и облегчит понимание.

6. Настройка вывода:

   • Убедитесь, что отображается дата окончания месяца с соответствующим числом дней. Например, 31 марта — 26 дней.
   • Параметры отображения, такие как формат чисел (например, без десятичных знаков), могут быть изменены.

7. Дополнительные расчеты:
   Если необходимо, вы можете добавить дополнительные вычисления, такие как "чисто рабочих дней" с помощью NETWORKDAYS или учет праздников.

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

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