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

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: было 7 завершений 7/5 (самое последнее), поэтому 3-е самое последнее - 7/1.
Задача B: было 7 завершений 7/3 (самое последнее), поэтому 3-е самое последнее - 7/2.
Задача C: все были в одну и ту же дату, так что 3-е место - 7/9.

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

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

Answer 1

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

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

Answer 2

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

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

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

3. Создайте кумулятивные суммы: в F3 введите формулу =SUM(B$3:B3) . И продлить до H8.
4. Создайте логическое значение, чтобы указать, когда сумма не меньше 3: В I3 введите формулу =F3>=3 . И продлить до К8.
5. Повторите даты, потому что VLOOKUP требует, чтобы поиск происходил справа: в L3 введите формулу =$A3 . И продлить до N8.
6. Создайте VLOOKUP. В 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 = В11) * ($ D $ 2: $ D $ 9) $ A $ 2: $ A $ 9))

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

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