Excel - считать, если в ячейке несколько слов

у меня есть ценности

 AA,BB,CC
 AA,CC
 AA
 AA,BB
 BB
 BB,CC
 CC
 CC,AA
 CC,BB
 BB,CC,DD

найти все клетки, которые имеют. как мне искать каждое значение.

 AA,BB = 2
 BB,CC = 4

Пример

COUNTIFS($A:$A,"*AA*",$A:$A,"*BB*") doesn't seem to work.
=COUNTIFS($A:$A,"*AA*",$A:$A,"*BB*") ?
Spectral Instance 19.02.2023 00:23

@Spectral Instance, у меня была Астрид, но она не отображалась, спасибо

Lacer 19.02.2023 00:57
Преобразование HTML-таблицы в профессиональный документ Excel
Преобразование HTML-таблицы в профессиональный документ Excel
Это самый простой способ создания Excel из HTML-таблицы.
Импорт excel в laravel в базу данных
Импорт excel в laravel в базу данных
Здравствуйте, дорогой читатель, в этой статье я расскажу практическим и быстрым способом, как импортировать файл Excel в вашу базу данных с помощью...
1
2
109
5
Перейти к ответу Данный вопрос помечен как решенный

Ответы 5

Вы можете попробовать это, используя wildcards* с TEXTBEFORE() и TEXTAFTER()


• Формула, используемая в ячейке D2

=COUNTIFS(A1:A10,"*"&TEXTBEFORE(C2,",")&"*",A1:A10,"*"&TEXTAFTER(C2,",")&"*")

РЕДАКТИРОВАТЬ

Как предложил Том Шарп Сэр,


• Формула, используемая в ячейке D2

=LET(x,TEXTSPLIT(C2,","),y,COUNTA(x),
SUM(--(MMULT(N(ISNUMBER(SEARCH(x,$A$1:$A$10))),SEQUENCE(y,,1,0))=y)))

Спасибо за это, но BB, dd, cc это результат должен быть 1, но это 0. Значения следует искать в ячейках индивидуально.

Lacer 19.02.2023 01:04

Как вы получаете BB,dd,cc как один, потому что это дает мне 4 со второй формулой

Mayukh Bhattacharya 19.02.2023 01:07

Спасибо за ответ. Это должно дать вам 1, так как только одна ячейка имеет все 3 из этих значений. Возможно, это плохой пример того, что я ищу, это то, что значение в столбце C следует искать в столбце A. Если у него есть все эти значения в столбце c, он добавляет количество вхождений этого.

Lacer 19.02.2023 01:16

Я согласен с вами, но если это дает вам один, то примеры, отмеченные для AA,BB и BB,CC, также должны давать вам 1. Это потому, что если вы считаете ячейки по отдельности, этого не происходит для данных двух.

Mayukh Bhattacharya 19.02.2023 01:18

На самом деле не нужно разбивать значения столбца C. Я могу вручную ввести значения. Я просто хочу иметь возможность искать несколько значений в столбце A.

Lacer 19.02.2023 01:29

Можете ли вы показать мне, каким должен быть счет CC,BB,AA?

Mayukh Bhattacharya 19.02.2023 01:32

Возможно, формула должна быть =LET(x,TEXTSPLIT(C2,",")), SUM(--(MMULT(N(ISNUMBER(SEARCH(x,$A$1:$A$10))),SEQUENCE(COUNT‌​TA( x),,1,0))=COUNTA(‌​x)))) ?

Tom Sharpe 19.02.2023 18:43

@TomSharpe Сэр, абсолютно верно. Я же не редактировал, т.к. у ОП поисковые слова были в разных ячейках, а в запросе они размещены с разделителем. Если позволите, обновлю так же.

Mayukh Bhattacharya 19.02.2023 19:02

Пожалуйста, сделайте - зависит от вас, хотите ли вы поместить counta (x) в качестве именованного значения в let, чтобы избежать повторения и т. д.

Tom Sharpe 19.02.2023 19:06

@TomSharpe Сэр, спасибо, я обновлю так же, как вы предложили.

Mayukh Bhattacharya 19.02.2023 19:07

Будьте осторожны с ложными срабатываниями. Чтобы понять, что я имею в виду, замените A1 на AAA,BB,CC.

JvdV 20.02.2023 09:13
Ответ принят как подходящий

Ответ был

COUNTIFS($A:$A,"*"&$C$2&"*",$A:$A,"*"&$C$3&"*")

С2 = АА С3 = ББ

Это применимо только в случае двух слов, если вы ищете три слова или только одно слово, вам нужно все время изменять формулу. Вопрос гласит: несколько. Это очень хорошее решение, но оно действительно только в этом сценарии.

David Leal 19.02.2023 16:45

Как насчет:

=LET(searchvalues,  C2,
     data,          $A$1:$A$10,
     split,         TEXTSPLIT(searchvalues,","),
     count,         COLUMNS(split),
SUM(
    --(MMULT(
             --ISNUMBER(SEARCH(split,data)),
             SEQUENCE(count,,1,0))
       =count)))

Он разбивает значения, которые вы хотите найти (хранящиеся в C2 в моем примере), на отдельные значения: split

Чем split ищется в каждой строке вашего data. Это возвращает TRUE или FALSE, завернутые в --, изменяет TRUE на 1 и FALSE на 0. Это используется в MMULT и возвращает сумму вхождений каждого отдельного значения split. Наконец, результат MMULT должен быть равен количеству разделенных значений поиска. Сумма этого факта является конечным результатом.

Следующий подход рассматривает переменное количество слов для поиска, а не только 2 слов, как во входном образце:

=LET(lk, B1, A, $A$1:$A$11, lks, TEXTSPLIT(lk,, ","),
  byr, BYROW(A, LAMBDA(x,SUM(COUNTIF(x,"*"&lks&"*")))), SUM(N(byr=ROWS(lks))))

У него есть оговорка: если слово повторяется более одного раза в строке в первом столбце, оно считается как 1, как вы можете видеть в следующем выводе. Это связано с тем, как COUNTIF (или COUNTIFS) работает с подстановочными знаками (другие ответы, предоставленные с использованием SEARCH, дают тот же результат). Если с этим предположением все в порядке, то оно работает. Осталось просто перетащить формулу вниз (formula1).

ОБНОВЛЕНИЕ: Учитывая комментарий @JvdV, чтобы избежать ложных срабатываний, когда искомое слово может быть подстрокой столбца A в данной строке. Как, например, поиск слова AA в строке: AAA приведет к ложному срабатыванию. Следующая версия избегает этого:

=LET(lk, B1, A, $A$1:$A$11, lks, TEXTSPLIT(lk,, ","),
 byr, BYROW(A, LAMBDA(x, SUM(N(TEXTSPLIT(x,",") = lks)))), SUM(N(byr=ROWS(lks))))

Вот результат:

Я намеренно добавил выделенные случаи, чтобы проверить дополнительные ситуации. Строка 11 повторяет слово: AA и BB, но в конечном результате считается как 1.

Следующий подход пытается определить общее количество отсчетов с учетом повторений слова в столбце A (формула 2):

=LET(lk, B1, A, $A$1:$A$11, lks, TEXTSPLIT(lk,, ","),
 byr, BYROW(A, LAMBDA(x, LET(match, IFERROR(TOCOL(XMATCH(TEXTSPLIT(x, ","),
 lks),2),0), ux, UNIQUE(match), IF(ROWS(ux) < ROWS(lks), 0,
   MIN(MMULT(TRANSPOSE(N(match = TOROW(ux))), SEQUENCE(ROWS(match),,1,0))))))),
 SUM(byr))

Теперь мы получаем следующий результат:

Теперь мы получаем дополнительный счет для случая AA, BB.

В формуле2 match имеет позицию индекса в результате вызова XMATCH, мы используем TOCOL для удаления ненайденных значений #N/A. Если слова не найдены, мы используем IFERROR, чтобы присвоить нулевое значение. Условие IF:

IF(ROWS(ux) < ROWS(lks)

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

Второе предостережение с COUNTIF() — это возможность ложных срабатываний. Скажем, измените A1 на «AAA, BB, CC», чтобы понять, что я имею в виду.

JvdV 20.02.2023 09:15

Хороший улов @JvdV, правда. Поскольку он находит первую подстроку, аналогичную SEARCH("Apple","Apples"), возвращает 1, поэтому другие предыдущие ответы имеют ту же проблему или предположение.

David Leal 20.02.2023 15:08

Да, все предыдущие ответы имеют одинаковую проблему в этом отношении. Мне кажется, что все эти параметры являются заполнителями, а не рабочими данными. Как бы то ни было, ваш второй вариант работает!

JvdV 20.02.2023 15:26

да, действительно, я добавил обновление к своему ответу, дав вам кредит на то, как решить ложное срабатывание для формулы 1, которая в конце является вашей версией без массива.

David Leal 20.02.2023 15:32

Вы можете попробовать:

Формула в D2:

=MAP(C2:C5,LAMBDA(z,SUM(MAP(A1:A11,LAMBDA(x,LET(y,TEXTSPLIT(z,,","),N(SUM(N(TEXTSPLIT(x,",")=y))=ROWS(y))))))))

Примечание. Мой ответ предполагает отсутствие дубликатов значений в столбце A:A. например: AA,AA.

Другие вопросы по теме