Como recuperar todas as ocorrências de um valor particular dentro de uma string?

Question

Estou a olhar para um livro de exercícios do Excel com possíveis definições para uma coluna (até 135) referenciada a partir de uma folha adjacente.

Por exemplo Folha 1: Col C (valores 0-134 definidos na Folha 2)

Por exemplo 0,1,8 Cada linha na Col C Sheet 1 pode ter qualquer combinação dos valores separados por vírgulas.

Imagine uma pasta de trabalho com duas folhas chamada 'Aluno' e 'Nomes da equipe' Cada linha na planilha 'Nomes da equipe' lista o ID de uma equipe. Um aluno pode jogar em mais de um time.

Digamos que há três equipes que apareceriam na planilha ' nomes de equipe ' como

A1 = 10

A2 = 20

A3 = 30.

Diga agora que há 4 alunos cujo roll.no aparece na planilha ' Estudante ' como A1 = 13, A2 = 27, A3 = 43, A4 = 53.

Roll.No 13 Col B 10, 20, 30

Roll.No 27 Col B 30, 10

Roll.No 53 Col B 20

Roll.No 43 Col B 30, 20

A intenção aqui é configurar uma fórmula tal que na planilha 'Team names'

10 Col B 2

20 Col B 3

30 Col B 3

A folha 2 deve manter estatísticas da ocorrência de cada valor. Isso é feito usando o COUNTIF. A questão aqui é que o COUNTIF não manipula muito bem um valor incorporado. Quando terminar, a referência a '1' no exemplo acima não será exibida.

Como isso pode ser feito sem recorrer a uma sub-rotina?

    

Answer 1

Se você tiver a lista de valores em sheet2 em A2, tente esta fórmula para uma contagem em B2

=SUMPRODUCT(ISNUMBER(FIND(","&A2&",",","&Sheet1!C$2:C$1000&","))+0)

Copiar a coluna de fórmula abaixo

Pressupõe até 1000 linhas de dados na planilha1, ajuste conforme necessário

Atualizado

O acima assumiu que os dados foram separados por vírgulas apenas (sem espaços), mas dado o seu exemplo, assumindo que os separadores são [comma] [espaço] juntos, então tente esta versão revisada, considerando seus comentários

=SUMPRODUCT(ISNUMBER(FIND(" "&A1&","," "&students!B$1:B$4&","))+0)