Calcula o número de elementos compartilhados nas células

Question

Estou com dificuldades para obter uma fórmula de cálculo para fazer os seguintes cálculos:

        A B D   G H A
A B C    2     1
A D E    2     1
A F G    1     2
B H I    1     1

Como no exemplo, eu preciso da fórmula para comparar duas células, e me diga quantos elementos comuns elas compartilham - sem me dizer quais elementos estão lá em qualquer uma das duas células, embora sejam sempre letras do alfabeto e qualquer carta dada nunca é repetida na mesma célula. Apenas para esclarecimento, o exemplo é comparar o que seria B1 com A2, A3, A4 e A5, e C1 para A2, A3, A4 e A5, respectivamente.

Alguma idéia de como conseguir isso com o LibreOffice? Obrigado!

    

Answer 1

Se você tiver sempre três letras nas células da linha 1, poderá usar:

=IF(ISNUMBER(SEARCH(MID(B$1;1;1);$A2));1;0) + IF(ISNUMBER(SEARCH(MID(B$1;2;1);$A2));1;0) + IF(ISNUMBER(SEARCH(MID(B$1;3;1);$A2));1;0)

na célula B2 e copie / cole nas outras células (o $ para corrigir as colunas / linhas já estão na fórmula)

Existem 3 IF, um para cada carta a testar. Se a letra for encontrada, o resultado é um número e o IF substitui por 1, se não por 0 e o resultado do 3 IF é adicionado.

Se você tiver mais letras em uma coluna, adicione apenas um IF às fórmulas dessa coluna.

A quantidade de letras na coluna 1 pode mudar sem qualquer alteração na fórmula.

Se você tiver espaço entre as letras na linha 1, como A B D' instead of ABD ', altere o 1, 2 e 3 no segundo parâmetro das funções MID para 1, 3 e 5.

    

Answer 2

Se você sempre tiver três letras nas células da Linha 1, poderá usar:

=SUM(--ISNUMBER(SEARCH(MID(B$1,{1,2,3},1),$A2)))

Esta é uma evolução incremental da resposta de Laurent. Eu fiz algumas otimizações. Primeiro de tudo,

IF (Boolean_value, 1 , 0)

é um pouco redundante, já que TRUE tem o valor 1 e FALSE tem o valor 0. Os booleanos podem ser prontamente convertidos em seus inteiros equivalentes com o prefixo -- ; abreviação de - ( - ( value ) ) , isso preserva o valor numérico mas coage o tipo para inteiro. E então o {1,2,3} é uma matriz que (com a função SUM ) elimina a necessidade de adicionar três termos quase idênticos.

Para manipular cadeias na Linha 1 com comprimentos variados, use

=SUM(--ISNUMBER(SEARCH(MID(B$1,ROW(INDIRECT("1:"&LEN(B$1))),1),$A2)))

Esta é como a primeira resposta exceto que obtemos o comprimento da string na parte superior da coluna, LEN(B$1) , e concatená-lo com a string " 1: ", formando algo como " 1:3 ". Isso parece um intervalo de linhas, e a função INDIRECT trata exatamente desse jeito, retornando o intervalo de endereços $1:$3 . Em seguida, ROW() desse valor retorna a matriz {1,2,3} . Este é um truque para criar uma matriz de números consecutivos cujo começo e fim não são predeterminados.

A fórmula acima deve ser digitada com Ctrl + Deslocar + Enter . Eu não tenho certeza do porque e o primeiro não.

Seu exemplo / ilustração faz com que pareça que suas strings têm espaços incorporados. Se isso é verdade (mas você não quer que os espaços contem), a fórmula se torna

=SUMPRODUCT(--(MID(B$1,ROW(INDIRECT("1:"&LEN(B$1))),1)<>" "),--ISNUMBER(SEARCH(MID(B$1,ROW(INDIRECT("1:"&LEN(B$1))),1),$A2)))

que também deve ser digitado com Ctrl + Deslocamento + Enter .