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

Question

Здравствуйте, у меня есть данные в таблице в виде:

Order#           Origin        Status          Date
90028             BG00           30          1/2/2018
90021             BG01           50          2/2/2018
90021             BG01           40          3/2/2018
90028             BG01           20          2/2/2018

И я хочу посчитать, сколько существует уникального номера заказа (считайте без дубликатов).

Происхождение от BG00 Имеет статус 30 ИЛИ 40 имеет Дату сегодня

Помощь ценится как можно скорее.

Answer 1

Запишите эту формулу массива в F76, завершите с помощью Ctrl+Shift+Enter.

{=SUM(IF(("BG00"=$B$76:$B$79)*($C$76:$C$79>=30)*($C$76:$C$79<=40)*(E74=$D$76:$D$79), 1/COUNTIFS($B$76:$B79, "BG00", $A$76:$A$79, $A$76:$A$79, $C$76:$C$79, ">="&30, $C$76:$C$79, "<="&40,$D$76:$D$79,E74)),0)}

Отредактировано:

Как это устроено:

Первая часть формулы возвращает 90028 из A76:A79 поскольку идентификатор заказа в строке 1 соответствует только всем трем условиям.

{=SUM(IF(("BG00"=$B$76:$B$79)*($C$76:$C$79>=30)*($C$76:$C$79<=40)*(E74=$D$76:$D$79),A76:A79,0))}

NB. С помощью вышеприведенной формулы я специально A76:A79 чтобы получить значение из Range, в противном случае вторая часть вводит его с помощью Countifs качестве истинных условий IF и возвращает 1 (только одно совпадение).

{=COUNTIFS($B$76:$B79, "BG00", $A$76:$A$79, $A$76:$A$79, $C$76:$C$79, ">="&30, $C$76:$C$79, "<="&40,$D$76:$D$79,E74)),0)}

Если идентификатор заказа 90028 в row 4 будет соответствовать всем трем условиям Orgin, Status & Date то Countifs вернет 2 .

SUM & 1/Countifs , был добавлен в формулу как " отказ от мер предосторожности", иначе эта формула будет работать и без них.

{=IF(("BG00"=$B$76:$B$79)*($C$76:$C$79>=30)*($C$76:$C$79<=40)*(E74=$D$76:$D$79), COUNTIFS($B$76:$B79, "BG00", $A$76:$A$79, $A$76:$A$79, $C$76:$C$79, ">="&30, $C$76:$C$79, "<="&40,$D$76:$D$79,E74),0)}

SUM весьма полезна, когда к критерию необходимо добавить значения из диапазона A76:A79 .

Заметка,

• При необходимости измените ссылки на ячейки в формуле.
• Если вы поместите сегодняшнюю дату в E74, то вы должны заполнить столбец Date несколькими похожими значениями даты, чтобы соответствовать.