#google-sheets #google-sheets-formula
Вопрос:
Я пытаюсь подсчитать уникальные значения столбца, основываясь на их статусе в другом столбце, пример:
| Клиенты | Лицензия Активна |
|---|---|
| Адам | ДА |
| Барри | НЕТ |
| Адам | НЕТ |
| Клэр | НЕТ |
В этой ситуации я хочу знать, у скольких клиентов есть хотя бы 1 активная лицензия, а у скольких клиентов нет хотя бы одной активной лицензии.
Формулу я пробовал это: =COUNTUNIQUEIFS(A2:A,B2:B,"Yes") это возвращается 1 в данной ситуации, что является правильным, так как есть 1 клиент, который имеет Да в столбце B. моя проблема, когда я пытаюсь сделать обратное, считать «нет», используя эту формулу: =COUNTUNIQUEIFS(A2:A,B2:B,"No") она возвращает 3 что это не желаемый результат, так как он рассчитывает второй Adam в качестве уникального значения, потому что у них «нет» в столбце B.
Результат , который мне здесь нужен, таков 2 , потому что у Адама где-то в столбце B есть «да», поэтому я не хочу, чтобы его снова учитывали при следующем подсчете его поля.
Ответ №1:
Мне кажется, что самый простой способ получить счет «Нет» — это так:
=COUNTUNIQUE(A2:A)-COUNTUNIQUEIFS(A2:A,B2:B,"Yes")
Это еще проще, если вы уже перенесли количество «Да» в ячейку (скажем, C2), и в этом случае количество «Нет» можно было бы получить довольно просто с помощью этого:
=COUNTUNIQUE(A2:A)-C2
Комментарии:
1. Спасибо, Эрик, это сработало и является самым простым решением. Очень признателен.
2. Пожалуйста. И вы также видите здесь, что существует множество способов достижения одной и той же цели в простынях.
Ответ №2:
Я не думаю, что вы сможете сделать это за один шаг — попробуйте отфильтровать тех, у кого хотя бы одно «Да», как это:
=countunique(filter(A2:A,countifs(B2:B,"Yes",A2:A,A2:A)=0))
Объяснение
Когда у графов есть диапазон вместо одного значения в графах части критериев(B2:B,»Да»,A2:A,A2:A) , графы повторно оцениваются для каждой ячейки в диапазоне. Таким образом, вы получаете массив с результатами
countifs(B2:B,"Yes",A2:A,A2)
countifs(B2:B,"Yes",A2:A,A3)
countifs(B2:B,"Yes",A2:A,A4)
countifs(B2:B,"Yes",A2:A,A5)
и так далее по всей колонне.
Первые приведенные выше графы проверяют прямо через a2:a и b2:b, чтобы увидеть, есть ли какие-либо случаи, когда имя Adam и условие лицензии верно, и получает количество 1, чтобы строка была отфильтрована. То же самое происходит в следующей строке, содержащей Адама (строка 4) — графы проверяются прямо в обоих столбцах, исключая заголовки, и количество по-прежнему равно 1, так что строка также отфильтровывается, оставляя только Барри и Клэр.
Если вы хотите исключить все записи, содержащие «Тест» в столбце «Клиент», вы можете добавить условие в фильтр, используя оператор умножения на «И», с существующим условием:
=countunique(filter(A2:A,(countifs(B2:B,"Yes",A2:A,A2:A)=0)*(A2:A<>"Test")))
Если бы у вас было несколько имен для исключения, вы, вероятно, захотели бы составить их список и использовать поиск, чтобы формула не становилась слишком длинной и громоздкой, но это была бы та же идея.
Комментарии:
1. Большое спасибо, Том, это сработало, и так как это немного надежнее, чем ответ Эрика выше (который также работал нормально) Я принял это, поскольку это позволяет мне немного легче перейти к следующему шагу моей формулы, потому что она более четко выражает то, что я пытаюсь сделать. Если у вас есть минутка, не могли бы вы рассказать мне, как в этом случае работает «фильтр» с «графами»; как он отфильтровывает ВСЕ экземпляры точного соответствия в A:A, если у любого из них есть «Да» в B:B? Я не совсем понимаю эту часть 🙂
2. Том, еще один вопрос, если можно: есть ли какой-либо способ фильтрации внутри фильтра, чтобы исключить конкретную запись из подсчета? Например, если в A:A была запись «Тест» , которой было присвоено несколько строк. Есть ли какой-либо способ добавить это в фильтр, чтобы он только «подсчитывал», исключая любые ячейки в A:A, которые являются «Тестовыми»
3. Да, конечно, я просто выхожу, но посмотрю позже.
4. Объяснение добавлено к ответу.