Excel- Como encontro valores de texto comuns de várias colunas (2)?

Question

Eu tenho 9 colunas de dados de texto e preciso conhecer os termos comuns encontrados em todas as nove colunas (ou possivelmente diferentes combinações de colunas). Eu posso fazer duas colunas =IF(ISERROR(MATCH(A1,$C$1:$C$133,0)),"",A1) e posso encontrar os valores de texto que são duplicados em toda a planilha, mas não consigo descobrir como observar os valores de texto comuns em várias colunas diferentes.

Existe uma maneira de modificar a fórmula =IF(ISERROR(MATCH(A1,$C$1:$C$133,0)),"",A1) para que eu possa comparar 3, 4, ... 9 colunas em vez de apenas duas? Estou usando o Microsoft 2013 se isso ajudar.

    

Answer 1

Eu tenho uma solução que funcionaria, mas é meio feia. Eu vou assumir que você está olhando para uma célula A1 e ver se ela aparece em todas as 3 colunas (D, E, F).

=MIN(MAX(($D$1:$D$3=$A1)*1),MAX(($E$1:$E$3=$A1)*1),MAX(($F$1:$F$3=$A1)*1))

Esta é uma fórmula de matriz, por isso, é necessário inserir ctrl + shift + enter

Agora, como funciona a partir de dentro para fora

($ D $ 1: $ D $ 3 = $ A1) * 1 Compare D1 a D3 com A1, o que retornaria verdadeiro ou falso, o * 1 converte isso para 1s e 0s

Max Se a primeira fórmula encontrar uma correspondência na coluna D, ela retornará 1, que o Max retornará. Não é encontrada correspondência, o valor máximo (apenas) é zero.

Min Se todas as colunas contiverem uma correspondência, todas as fórmulas máximas conterão um 1, de modo que o mínimo será 1. Se não houver uma correspondência em todas as colunas, o valor mínimo será zero .

Se você quiser contar o número de colunas que correspondem à soma de uso em vez de min e rememeber ctrl + shift + digite .

note: Será uma fórmula máxima para cada coluna que você deseja pesquisar.

    

Answer 2

Esta configuração funcionará para qualquer número de colunas.

Primeiro, vá para o Gerenciador de nomes (guia Fórmulas ) e defina o seguinte:

Name: Range1
Refers to: =$A$1:$I$8

(Ou qualquer que seja o intervalo em questão.)

Name: Arry1
Refers to: =COLUMN(Range1)-MIN(COLUMN(Range1))

Name: Arry2
Refers to: =ROW(INDEX(Range1,,1))-MIN(ROW(INDEX(Range1,,1)))+1

Name: Arry3
Refers to: =MMULT(0+(COUNTIF(OFFSET(INDEX(Range1,,1),,Arry1,,),INDEX(Range1,,1))>0),ROW(INDIRECT("1:"&COLUMNS(Range1)))^0)

Sair do Gerenciador de nomes.

A matriz de obrigatória é:

=IFERROR(INDEX(INDEX(Range1,,1),SMALL(IF(FREQUENCY(IF(INDEX(Range1,,1)<>"",IF(Arry3=COLUMNS(Range1),MATCH(INDEX(Range1,,1),INDEX(Range1,,1),0))),Arry2),Arry2),ROWS($1:1))),"")

Copie até começar a obter espaços em branco para os resultados.

Atenciosamente

As fórmulas de matriz não são inseridas da mesma forma que as fórmulas 'padrão'. Ao invés de pressionar apenas Enter , você primeiro pressiona Ctrl e Shift , e somente então pressiona Enter . Se você fez isso corretamente, você notará que o Excel coloca chaves entre {} ao redor da fórmula (embora não tente manualmente inseri-las você mesmo).

    

Answer 3

Acho que você pode ir muito longe com o countif: