СЧЁТЕСЛИ с положительным и отрицательным критериями

Мне нужно подсчитать количество ячеек, в которых ячейки в «СПИСОК A» появляются в наборе значений, содержащихся в столбце D И ячейки в «СПИСОК B» не отображаются в столбце E.

Я пытаюсь использовать что-то вроде следующей формулы массива, но пока не добился успеха:

={SUM(COUNTIFS(A2:A21,D2:D5,B2:B21,"<>"&E2:E3))}

Обратите внимание, что данные, содержащиеся в этом примере, отличаются от данных в моей реальной таблице. Настоящая таблица значительно длиннее и сложнее этой таблицы.

Какие-либо предложения?

Пример


excel countif excel-formula
person Gavin121    schedule 09.02.2016    source источник
comment
В таких ситуациях лучше всего показать свою работу и использовать пару дополнительных столбцов для промежуточных столбцов. Я не люблю тщательно продумывать СЧЕТЧИК.   -  person Bathsheba    schedule 09.02.2016
comment
Countif использует OR, что означает: подсчитайте каждое значение в B2: B21, которое хотя бы один раз не является ЛЮБЫМ из E2: E4 (это считается для всех чисел ... но вы можете использовать эту формулу без массива: =SUMPRODUCT(COUNTIF(D2:D5,A2:A21)*(COUNTIF(E2:E5,B2:B21)-1))*-1   -  person Dirk Reichel    schedule 09.02.2016
comment
Дирк Райхель, эта формула работает хорошо, но я не совсем понимаю, почему. Во-вторых, если, например, в столбце C был третий список со значениями ИСТИНА или ЛОЖЬ, а ячейка должна была иметь значение ИСТИНА, может ли это соответствовать вашему предложению?   -  person Gavin121    schedule 09.02.2016
comment
конечно ... хотя эта формула может быть для вас более понятной: _1 _... можно добавить третий столбец, поскольку правила должны быть установлены ... я напишу ответ, чтобы показать, как   -  person Dirk Reichel    schedule 09.02.2016
comment
Извините, это моя ошибка - я исправил ее сейчас. Спасибо за внимание   -  person Gavin121    schedule 09.02.2016
comment
Ах! Вы изменили второй диапазон критериев только на E2: E3, не так ли? Разве это не было раньше с E2: E4?   -  person XOR LX    schedule 09.02.2016
comment
Да, раньше было - сейчас поправил.   -  person Gavin121    schedule 09.02.2016

Ответы (2)


arrow_upward
1
arrow_downward

просто для вашего примера будет достаточно формулы, как в моем комментарии

=SUMPRODUCT(COUNTIF(D2:D5,A2:A21)*NOT(COUNTIF(E2:E5,B2:B21)))

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

=SUMPRODUCT(1*NOT(COUNTIF(E2:E5,B2:B21)))

при этом будут подсчитаны все строки, содержащие что-либо из списка исключений. Но NOT внутри sumproduct переключит его на противоположное (необходим 1 *, потому что он будет содержать только bool, который не может быть подсчитан)

если у вас есть столбец C с bool (true / false или 1/0), вы можете просто добавить это:

=SUMPRODUCT(COUNTIF(D2:D5,A2:A21)*NOT(COUNTIF(E2:E5,B2:B21))*C2:C21)

или также (C2:C21>12), если это то, что вам нужно ... но вы также можете включить его в список включения (если A - A / B / E / F, а C - (2/4/6)

=SUMPRODUCT(COUNTIFS(D2:D5,A2:A21,F2:F5,C2:C21)*NOT(COUNTIF(E2:E5,B2:B21)))

Но каждому исключению нужен свой счетчик (B не X / Y, а C не 8/9/11)

=SUMPRODUCT(COUNTIFS(D2:D5,A2:A21)*NOT(COUNTIF(E2:E5,B2:B21))*NOT(COUNTIF(F2:F5,C2:C21))

Как уже было сказано: наличие формул, возвращающих "", может возвращать ложные подсчеты (имейте это в виду)

person Dirk Reichel    schedule 09.02.2016
comment
Имейте в виду, что ваша формула может завершиться ошибкой, если выполняются оба следующих условия: 1) Один или несколько диапазонов, переданных как параметр диапазона COUNTIF, включают пустые ячейки и 2) Пустые ячейки возможны в пределах диапазонов, переданных в качестве параметра критерия, тогда как MATCH На установку, которую я опубликовал, такие случаи не влияют. 1) кажется верным, поскольку OP включает пустые ячейки в диапазоне E2: E4 (E4 - в настоящее время по крайней мере - пусто), поэтому, хотя на данный момент нет пустых ячеек в диапазоне B2: B21 (так что 2) в настоящее время неверно), если это изменится, ваша формула не сработает. - person XOR LX; 09.02.2016
comment
я провел несколько тестов и не получил ошибок ... вы можете показать мне пример, чтобы я получил ошибку? если я включаю пустые ячейки, они не будут учитываться, если в столбце A и пустые ячейки в столбце B считаются, чтобы не исключать ... отсутствие обоих не будет считаться ... я действительно не понимаю :( - person Dirk Reichel; 09.02.2016
comment
Извинения. Я должен был уточнить, что он не работает с нулевыми строками, а не с действительно пустыми ячейками. Так, например, с пустыми E3 и E4, как в исходном примере OP, сделайте так, чтобы некоторые из ячеек в диапазоне B2: B21 возвращали пустую строку. - person XOR LX; 09.02.2016
comment
да, это правда ="" вызовет ошибки ... (как и много раз), я включу это в свой ответ - person Dirk Reichel; 09.02.2016
comment
Верно. Надоедливые мелочи! На самом деле моя установка не удастся, если такие строки возможны в диапазоне D2: E5 (но не в A2: B21). У меня не было оснований предполагать, что строки в D2: E5 были настоящими пробелами, а строки в A2: B21 потенциально нулевыми. - person XOR LX; 09.02.2016
comment
но ваша проблема может быть решена простым IF(LEN(E2:E5),..., хотя я не знаю, будет ли ISNUMBER(MATCH(B2:B21,IF(LEN(E2:E5),E2:E5),0)) работать таким образом ... (все же список не должен включать никаких FALSE, тогда ... хм ... бегает по кругу: P - person Dirk Reichel; 09.02.2016
comment
@DirkReichel, последний пример мне не подходит. В настоящее время я пробую =SUMPRODUCT(COUNTIFS(D2:D5,A2:A21)*COUNTIF(F2,C2:C21)*NOT(COUNTIF(E2:E5,B2:B21))), где мой F2 проверяет слово (например, ИСТИНА), а столбец C содержит список слов. - person Gavin121; 10.02.2016
comment
Вам нужен первый список и третий список, но не второй ... или вы хотите A или C? Итак, если совпадают только A или C (а B не исключает этого), вы хотите это подсчитать? - person Dirk Reichel; 10.02.2016
comment
A и C оба совпадают (D и F соответственно), а B не исключает его (т.е. не исключается в E). Если проще, с удовольствием задам отдельный вопрос или обновлю этот вопрос. - person Gavin121; 10.02.2016
comment
=SUMPRODUCT(COUNTIF(D2:D5,A2:A21)*NOT(COUNTIF(E2:E5,B2:B21))*(C2:C21=F2)) должен это сделать ... - person Dirk Reichel; 10.02.2016

arrow_upward
1
arrow_downward

Лучше всего переходить на SUMPRODUCT в таких случаях:

=SUMPRODUCT(0+ISNUMBER(MATCH(A2:A21,D2:D5,0)),1-ISNUMBER(MATCH(B2:B21,E2:E5,0)))

С Уважением

person XOR LX    schedule 09.02.2016