☰

Encontre a enésima instância de uma string de texto dentro de um intervalo?

tag-icon • tag-icon openoffice openoffice-calc
Encontre a enésima instância de uma string de texto dentro de um intervalo?

Tenho uma lista de nomes ao lado de uma lista de turnos. Quero agrupar uma lista de pessoas em cada turno por dia, que será atualizada de forma automática e dinâmica. Não consigo descobrir a melhor maneira de fazer isso.

Na lista principal, haverá uma coluna de nomes de funcionários, seguida de 14 colunas de turnos. Quero então criar 14 listas de nomes com base em quem está em qual turno na primeira tabela.

Ex: A faixa principal possui:

        A      B   C   D   E
   ---------- --- --- --- ---
1  Joe Blogs   E   E   L   O
2  Jill Bleg   L   L   E   E
3  Geoff Ted   O   L   L   L

Gostaria de pesquisar a coluna D, por exemplo, e procurar a primeira instância de L para listar Joe Blogs, depois procurar a segunda instância de L para listar Geoff Ted e assim por diante.

Existe uma maneira de procurar a enésima instância de uma string dentro de um intervalo de células?

Responder1

Isto não é tão fácil quanto parece. Primeiro, para encontrar os números das linhas de todas as ocorrências de "L" na coluna D, você usaria:

=IF(D1:D3="L"; ROW(D1:D3))

Salve isso comouma fórmula de matriz: depois de digitar, pressione Ctrl+Shift+Return (ou Command+Shift+Return em um Mac). Será entãomostrarentre chaves. E não terá uma única célula como resultado, mas tantas células quanto o intervalo em que você está trabalhando. No exemplo acima, você obteria 3 células, com valores 1, em branco e 3.

Próximo,usarSMALLpara encontrar o enésimo valor:

SMALL(numberlist; n)

retorna o nmenor número dentro do intervalo (não ordenado) ou matriz de números numberlist.

Aplicando SMALLao acima, você volta a ter uma única célula como resultado:

=SMALL(IF(D1:D3="L"; ROW(D1:D3)); 1)
=SMALL(IF(D1:D3="L"; ROW(D1:D3)); 2)

Ainda assim, apesar do resultado ser uma única célula, isso precisa ser salvo usando Ctrl+Shift+Return.

Agora, conhecendo o número da linha, INDEXpodemos encontrar o nome na primeira coluna de A1:A3:

=INDEX(A1:A3; SMALL(IF(D1:D3="L"; ROW(D1:D3)); 1); 1)
=INDEX(A1:A3; SMALL(IF(D1:D3="L"; ROW(D1:D3)); 2); 1)

Novamente, todos eles precisam ser salvos usando Ctrl+Shift+Return.

No entanto, tal fórmula não pode ser arrastada para ser estendida para outras células, pois as classificações "1" e "2" não se tornariam automaticamente "3" e assim por diante. Em vez disso, para calcular a classificação necessária com base na linha em que a fórmula está:

=INDEX(A1:A3; SMALL(IF(D1:D3="L"; ROW(D1:D3)); ROW()); 1)

Depois de salvá-la como uma fórmula de matriz em algum lugar da linha 1, pode-se arrastá-la para baixo para adicioná-la às linhas 2, 3 e assim por diante.

Alternativamente, em vez de estender o resultado, você pode copiar/colar a fórmula de matriz para que as referências de coluna e linha sejam ajustadas instantaneamente. Na captura de tela abaixo, copiei o seguinte de B7, B12 e B17 para as outras células:

=INDEX($A$1:$A$3; SMALL(IF(B$1:B$3=B$6; ROW(B$1:B$3)); ROW()-ROW(B$6)); 1)
=INDEX($A$1:$A$3; SMALL(IF(B$1:B$3=B$11; ROW(B$1:B$3)); ROW()-ROW(B$11)); 1)
=INDEX($A$1:$A$3; SMALL(IF(B$1:B$3=B$16; ROW(B$1:B$3)); ROW()-ROW(B$16)); 1)

Esteja ciente de que o atalho de teclado para fórmulas de matriz só funcionará depois de realmente fazer alterações na fórmula; quando você simplesmente pressiona Return, entrar na fórmula novamente e pressionar Ctrl + Shift + Return não terá efeito.

Além disso, depois que uma fórmula de matriz for estendida, você precisará selecionar todas as células de resultado para alterar essa fórmula. Caso contrário você conseguirá"Você não pode alterar apenas parte de um array".

informação relacionada