Excel - Verificando se o conteúdo de várias células existe dentro de uma célula principal

Question

Eu tenho uma planilha do Excel. Na coluna A há algumas palavras. Em alguns casos, há várias palavras em uma célula, separadas por pontos decimais (pontos); por exemplo, university.of.california ou school.house . O que vier depois do primeiro ponto, incluindo o próprio ponto, deve ser ignorado; por exemplo, university.of.california deve ser tratado como se fosse apenas university .

Nas colunas B a R existem trigramas - grupos de três letras cada. Mas também há células em branco nessas colunas.

Eu quero verificar se os trigramas nas colunas B a R aparecem dentro da (primeira) palavra na coluna A da mesma linha. Por exemplo, se as colunas A-F em alguma linha contiverem university.of.california , cal , rev , sit , uni e uny , essa linha deve contar como 2, porque uni e sit aparecem em university . cal não conta porque california vem depois de um período rev não conta porque é ver na ordem errada e uny não conta, porque as letras u , n e y não ocorrem juntos em university .

Eu quero a coluna U em cada linha para indicar o número de trigramas nas colunas B a R nessa linha que correspondem à primeira palavra na coluna A. Como posso fazer isso?

E qual fórmula usar na coluna T, então é VERDADEIRO (em verde) se U for igual ou maior que 1 correspondência encontrada nessa linha e FALSE (não colorido) se U for 0 nessa linha?

Veja um exemplo de conjunto de dados. Como descrito acima, As colunas de A a R contêm dados de entrada que serão fornecidos. As colunas T e U contêm os resultados que eu quero que o Excel crie a partir dessa entrada. Neste exemplo, a célula T6 é verdadeira porque "ice" existe antes do primeiro ponto e U6 é 1 porque é a única correspondência antes do primeiro ponto, uma vez que "hou" e "col" existem somente após o primeiro ponto, então foram ignorados (em vermelho). Na foto, o amarelo são as correspondências corretas a serem consideradas.

+---+--------------------+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+---+-------+---+
|   |         A          |  B  |  C  |  D  |  E  |  F  |  G  |  H  |  I  |  J  |  K  |  L  |  M  |  N  |  O  |  P  |  Q  |  R  | S |   T   | U |
+---+--------------------+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+---+-------+---+
| 1 | university         | abd | ity | sfd | fgh | tyu | kjg | sdf | jhg | bnm | hjk |     |     |     |     |     |     |     |   | TRUE  | 1 |
| 2 | school             | bnm | sdf | hoo | tyu | kjg | sdf | jhg | ool | hjk | sdf | fgh | tyu | kjg | sch | jhg | bnm | hjk |   | TRUE  | 3 |
| 3 | college            | sdf | fgh | tyu | kjg | sdf | jhg | bnm | sdf | fgh | tyu | kjg | sdf | jhg | bnm | hjk |     |     |   | FALSE | 0 |
| 4 | home               | ome | fgh | tyu | kjg | sdf | jhg |     |     |     |     |     |     |     |     |     |     |     |   | TRUE  | 1 |
| 5 | nice.colored.house | hou | col | ice |     |     |     |     |     |     |     |     |     |     |     |     |     |     |   | TRUE  | 1 |
| 6 | super.market       | etr | etr | sdf | fsd | sdf | iuj | mar | ket |     |     |     |     |     |     |     |     |     |   | FALSE | 0 |
+---+--------------------+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+   +-------+---+

Aqui estão os mesmos dados (possivelmente incluindo erros de transcrição) com codificação de cores para iluminação, conforme descrito acima:

Se possível, a fórmula deve ser insensível a maiúsculas e minúsculas. Por exemplo, ooL e OOL devem contar como correspondências para school .

    

Answer 1

Isso é muito difícil - talvez até impossível - sem o auxílio de células auxiliares. Um lote de células auxiliares. Felizmente, é bastante fácil fazer com muitas células auxiliares.

Minha solução requer uma célula auxiliar para cada célula real, até a coluna R . Você pode colocá-las em Colunas AA a AR nas mesmas linhas. Ou você pode colocá-los em Colunas A a R nas linhas 11 a 16, ou 101 a 106. Eu escolhi colocá-los nas células paralelas em uma folha diferente; isso facilita a expansão posterior.

Observação: se você quiser classificar os dados mais tarde, colocar as células auxiliares na mesma folha que os dados principais, nas mesmas linhas mas (obviamente) colunas diferentes (por exemplo, AA a AR ).

Em Sheet2!A1 , insira

=IFERROR(LEFT(Sheet1!A1,SEARCH(".",Sheet1!A1)-1), Sheet1!A1)

Isso extrai o valor de Sheet1!A1 até o primeiro período (ponto decimal), caso existam. Especificamente, ele pesquisa o primeiro . em Sheet1!A1 . Se encontrar um, usa LEFT() para extrair o texto antes dele; caso contrário, leva apenas o valor total.

Em Sheet2!B1 , insira

=IF(AND(Sheet1!B1<>"",NOT(ISERROR(SEARCH(Sheet1!B1, $A1)))), 1, 0)

Isso verifica se Sheet1!B1 não está em branco e se aparece em Sheet2!A1 (a parte de Sheet1!A1 até o primeiro ponto decimal). Se sim e sim, ele é avaliado como 1; caso contrário, ele será avaliado como 0.

Selecione Sheet2!B1 e arraste / preencha para a direita, para Coluna R . Em seguida, selecione as células A1:R1 e arraste / preencha para a linha 6. Aqui está o resultado:

Agoraorestoéfácil.EmSheet1!U1,insira

=SUM(Sheet2!B1:R1)

quecontaascorrespondênciasnalinha1.EemSheet1!T1,insira

=U1>0

SelecioneascélulasT1:U1earraste/preenchaparaalinha6.Evocêestáfeito:

Se você quiser colorir as células, você pode fazer isso facilmente com a formatação condicional. Se você quiser classificar os dados, e você colocou as células auxiliares nas mesmas linhas que os dados reais, em seguida, selecione os dados reais e as células auxiliares juntas (por exemplo, A1:AR6 ) e ordenar o bloco inteiro.