Найти дату третьего предыдущего события

Question

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

Вот пример, если мое объяснение непонятно:

Date    | Task   | Count of Task
--------------------------
6/30/18 | Task A | 2
6/30/18 | Task B | 3
7/01/18 | Task A | 2
7/02/18 | Task B | 1
7/03/18 | Task B | 1
7/03/18 | Task B | 1
7/05/18 | Task A | 2
7/09/18 | Task C | 7

Формула должна возвращать следующее:

Task A: 7/1/18
Task B: 7/2/18
Task C: 7/9/18

Задача A: 2 завершения были выполнены 5 июля (самое последнее), поэтому 3-е самое позднее — 1 июля.
Задача B: 2 завершения были выполнены 3 июля (самое последнее), поэтому 3-е самое позднее — 2 июля.
Задача C: все были выполнены в один и тот же день, поэтому 3-е самое позднее — 9 июля.

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

Я думаю, что решение будет включать в себя некоторую комбинацию ВПР и сум., но это выходит за рамки моих текущих навыков.

Answer 1

В ячейке C10напишите эту формулу массива, закончите Ctrl+Shift+Enterи заполните ее.

{=MIN(IF($B$3:$B$8=A10,$A$3:$A$8))}

Answer 2

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

   • Выделите данные.
   • Перейдите в меню Вставка > Таблицы > Сводная таблица.
   • Выберите место для размещения стола.
   • Проверьте дату, задачу и количество.
   • Перетащите «Дата» в «Строки», «Задача» в «Столбцы», а «Количество» в «Значения». И выберите «Сумма количества» (если это еще не сделано).

2. Сортируйте даты (метки строк) по убыванию.

3. Создайте кумулятивные суммы: в F3 введите формулу =SUM(B$3:B3). И расширьте до H8.
4. Создайте логические значения для указания, когда сумма равна не менее 3: в I3 введите формулу =F3>=3. И расширьте ее до K8.
5. Повторите даты, так как VLOOKUP требует, чтобы поиск выполнялся справа: В L3 введите формулу =$A3. И расширьте до N8.
6. Создайте VLOOKUPs. В I9 введите формулу =VLOOKUP(TRUE, I3:L8,4,FALSE). Расширьте до K9

Ответ находится в I9-K9.

Это решение занимает много ячеек, но это решение, которое легко настроить. Обратите внимание, что вместо того, чтобы расширять формулу в шаге 5 до N8, можно просто изменить VLOOKUP в J9, чтобы он ссылался на 3-й столбец, а VLOOKUP в K9 — на 2-й столбец.

Электронная таблица со значениями:

Электронная таблица с формулами:

Answer 3

Объединение моего старого ответа и ответа Раджеша С.

1. Создайте накопительную сумму по каждой задаче: в ячейке D2напишите =SUMIF($B2:B$9, B2, $C2:C$9) >= 3и заполните все ячейки C9.

2. Найдите максимальную дату для каждой задачи, отмеченной как TRUEна шаге 1: в ячейке D11введите следующую формулу массива (и нажмите Ctrl+Shift+Enter):

   =МАКС(ЕСЛИ(($B$2:$B$9=B11)*($D$2:$D$9),$A$2:$A$9))

3. Заполните его до D13.

Примечание: Чтобы решение работало, задачи должны быть расположены в порядке возрастания даты.