Подсчитайте, сколько раз проект изменял дату завершения

Question

Project             Est Comp    Need Date  Revision Date
ABC123BLDP01.001    1/1/2017    2/14/2017   12/31/2017
ABC123BLDP01.001    1/12/2017   2/14/2017   1/5/2017
ABC123BLDP01.001    2/25/2017   2/14/2017   2/14/2017
ABC123BLDP01.001    4/30/2017   2/14/2017   3/31/2017
ABC123BLDP01.001    5/26/2017   2/14/2017   4/27/2017

У меня есть таблица, которая похожа на выше. В нем сотни проектов, и я пытаюсь придумать самый простой способ подсчитать, сколько раз в каждом проекте менялся второй столбец (Est Comp). Данные в Excel. Мне нужен VBA или я могу сделать здесь заявление COUNTIFS? Я не могу придумать, как заставить его проверять разницу в датах строка за строкой для одного и того же проекта с COUNTIFS.

Answer 1

Сводная таблица может работать. В приведенном ниже примере я добавил данные, и сводная таблица посчитала "дату ревизии". Вы заметите, что в задании 123 есть 7 записей, но поскольку для 3 из них не существует ревизии, сводная таблица насчитывает только 4.

Вы можете адаптировать это, чтобы считать только столбец, который вы хотите использовать. Чтобы создать сводную таблицу, выберите ячейку в своей таблице и перейдите на вкладку "Вставка" и выберите "Сводная таблица". Выберите, куда вы хотите поместить таблицу, затем добавьте свои данные. Возможно, вам придется изменить формат поля "значение" с СУММЫ на СЧЕТ.

Answer 2

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

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

Подход, показанный на рисунке ниже, требует столбца "помощник", как показано. Формула для вспомогательного столбца:=1/COUNTIFS(A$2:A$16,A2,B$2:B$16,B2) . Это подсчитывает количество раз, когда данная комбинация даты Project и Est Comp встречается в списке, а затем принимает обратную величину от этого числа.

Например, Project ABC ... происходит только один раз с датой Est Comp 1/1/17, но происходит дважды с датой Est Comp 1/12/17. Таким образом, взаимные 1 и 0,5. Причиной для этого является то, что суммирование этих чисел для данного проекта дает число различных комбинаций столбцов Project и Est Comp, то есть сколько различных дат Est Comp имеется в каждом проекте.

Ссылка выше объясняет это более подробно, если вам интересно.

Теперь давайте составим список проектов. Ячейка F2 имеет следующую формулу:

 =IFERROR(INDEX($A$2:$A$16,MATCH(0,INDEX(COUNTIF($F$1:F1,$A$2:$A$16),0,0),0)),"")

Обратите внимание, что ДОЛЖНА быть строка над первой строкой данных, чтобы эта формула работала правильно. У вас есть имена столбцов, так что вы будете в порядке.

Теперь осталось только подсчитать количество различных дат Est Comp для каждого проекта. Формула в G2:

 =SUMIF(A$2:A$16,F2,E$2:E$16)

Эта формула суммирует столбец Helper для каждого проекта и выдает количество различных дат Est Comp для каждого проекта. Количество раз, когда измененная дата может быть на 1 меньше, чем это число, но вы можете принять это решение и вычесть 1, если вам нужно.

Надеюсь, это поможет и удачи.