У меня есть таблица, в которой я сначала применяю фильтр к определенному столбцу, чтобы показать "Только уникальные записи". Теперь у меня есть столбец, который содержит, например, такие значения:
Applepie
Applesauce
Crabapple
Banana
Mango
Мне нужно подсчитать все видимые ячейки, содержащие слово "Apple". Обратите внимание, что это должно исключить скрытые (отфильтрованные) ячейки.
Теперь я нашел формулу:
`=SUMPRODUCT(SUBTOTAL(3,OFFSET(Sheet1!A:A,ROW(Sheet1!A:A)-MIN(ROW(Sheet1!A:A)),,1))*(Sheet1!A:A="<Text to search>"))`
Однако проблема в том, что эта формула ищет весь текст. А НЕ только его часть. Как мне изменить эту формулу, чтобы она возвращала количество ячеексодержащий "Яблоко"т.е. для этого примера он должен вернуть 3 в качестве результата.
решение1
COUNTIFс подстановочными знаками будет работать самостоятельно для подсчета всех строк, содержащих слово «Apple» где-либо в тексте... но он не ограничивается видимыми строками, и вы не можете использовать его COUNTIFвместе с вашей SUBTOTALформулой.
Скорректируйте формулу следующим образом, используя SEARCHфункцию
=SUMPRODUCT(SUBTOTAL(3,OFFSET(Sheet1!A:A,ROW(Sheet1!A:A)-MIN(ROW(Sheet1!A:A)),,1)),ISNUMBER(SEARCH("Apple",Sheet1!A:A))+0)
Примечание: эта версия может быть медленной, ссылаясь на весь столбец — лучше ограничить диапазоны, если это возможно.
Альтернативный подход
Если вы хотите избежать этой длинной формулы, то вы можете использовать вспомогательный столбец, чтобы указать, отфильтрована ли каждая строка или нет, например, на Листе 1 Z2 используйте эту формулу, скопированную ниже.
=SUBTOTAL(3,A2)
Теперь вы можете использовать COUNTIFSфункцию для подсчета видимых строк, содержащих «Apple»
=COUNTIFS(A:A,"*Apple*",Z:Z,1)
решение2
Тест: =COUNTIF(A2:A10;"Apple*")изменениеrange