У меня есть электронная таблица, в которой перечислены ученики, посещающие один из наших центров домашнего задания. В электронной таблице перечислены языки, на которых говорят ученики, и я хочу подсчитать количество случаев, когда ученики говорят на определенных языках, как в следующем примере, где столбец Язык ученика имеет именованный диапазон, установленный на registerListLanguage:
| Имя ученика | Язык ученика |
|---|---|
| Поддельное имя | албанский |
| Другой | урду |
| Ученик 1 | урду, пенджаби, итальянский |
| Ученик 2 | пенджаби |
| Ученик 3 | Урду и пенджаби |
Это легко сделать, если ученики говорят только на одном языке (например, на албанском), с помощью простого =SUM(COUNTIFS(registerListLanguage, "албанский")
Однако во многих случаях наши ученики говорят на двух или более языках, а это значит, что нам необходимо перечислить языки, на которых они говорят (как ученик 1 в примере выше).
Мои начальники хотят, чтобы я мог сообщать об учениках, говорящих на урду ИЛИ пенджаби, и мне действительно трудно получить точный подсчет... На данный момент я использую следующую формулу:
=SUM(COUNTIFS(registerListLanguage, {"*Urdu*","*Punjabi*"}))
Но это касается учеников, которые говорят И на пенджаби, И на урду дважды, поэтому ученик 1 и ученик 3 в приведенном выше примере учитываются дважды...
Я весь день мучился с тем, как заставить его учитывать ячейку только ОДИН РАЗ, если она содержит ЛИБО урду, либо пенджаби...
Кто-нибудь может указать, где я ошибся?
Спасибо!
решение1
Вы можете вычесть функцию СЧЁТЕСЛИМН, которая учитывает, где они оба присутствуют:
=SUM(COUNTIFS(B2:B6, {"*Urdu*","*Punjabi*"}))-COUNTIFS(B2:B6,"*Urdu*",B2:B6,"*Punjabi*")
Или используйте функцию СУММПРОИЗВ, которая проверяет, есть ли в ячейке хотя бы один из элементов, и учитывает его только как 1, а не как 2:
=SUMPRODUCT(--((ISNUMBER(SEARCH("Urdu",B2:B6)))+(ISNUMBER(SEARCH("Punjabi",B2:B6)))>0))
Это создает два массива из 1 и 0, а затем складывает их. Если любая из строк в добавленном массиве больше 0, возвращается 1.
