☰

Obtenha um valor do registro anterior mais recente onde algum texto aparece em qualquer uma das duas colunas

tag-icon • tag-icon microsoft-excel
Obtenha um valor do registro anterior mais recente onde algum texto aparece em qualquer uma das duas colunas

Estou tentando automatizar de forma mais completa o cálculo de valores e dados resumidos em uma planilha que mantenho sobre os resultados das partidas de uma liga de pool.

Tenho uma tabela com muitas informações sobre cada partida, sendo os campos relevantes: Data da partida, Vencedor, Handicap inicial do vencedor, Handicap final do vencedor, Perdedor, Handicap inicial do perdedor, Handicap final do perdedor, Hora de início da partida.

Os handicaps são ajustados no final de cada partida e antes da próxima. É difícil encontrar o recorde passado mais recente de um jogador (poderia ter sido um Vencedor ou Perdedor) e copiar seu handicap final desse registro para o Handicap Inicial (vencedor ou perdedor) daquele em que estou entrando agora.

Eu gostaria de uma fórmula que encontrasse o recorde mais recente (data e hora de início mais altas, caso ele tenha jogado duas vezes em um dia) onde ele foi vencedor ou perdedor e, em seguida, obtivesse o handicap final (do respectivo vencedor ou perdedor). ).

Por sugestão de Teylyn, aqui está um link do Dropbox para o arquivo. A guia relevante é Resultados da partida:https://www.dropbox.com/s/1j9c6zsxjd3q4dt/Sample%20for%20Excel%20Question%20on%20Superuser.xlsx?dl=0

Adicionei uma coluna L em branco para testar as coisas, comparando os resultados com o que está em K para ver se estavam funcionando, por isso está lá. Esqueci de removê-lo quando coloquei no Dropbox.

Responder1

Declaração do problema

Uma planilha tem nomes em Colunas Ee X. Para cada linhan, ≠ . Existem números em Column correspondentes aos nomes em Column , e números em Column correspondentes aos nomes em Column . Para qualquer linha após a primeira (digamos a linha 42), queremos obter valores para e das linhas anteriores, se possível.EnXnMEAGXK42AF42

  • Se E42for “John”, encontre a linha mais recente que contém “John” (na coluna Eou X). Chame essa linhan. Se = “João”, defina igual a . Se = “João”, defina igual a .EnK42MnXnK42AGn

  • Se X42for “Scott”, encontre a linha mais recente que contém “Scott” (na coluna Eou X). Chame essa linhan. If = “Scott”, iguale a . If = “Scott”, iguale a .EnAF42MnXnAF42AGn

                   

Solução

Na esperança de preservar alguma sanidade, vamos usar colunas auxiliares; digamos ARe AS. Suponha que (como no arquivo de exemplo), os dados comecem na Linha 2. Insira

=MAX(($E$2:$E2=$E3)*(100*ROW($E$2:$E2)+COLUMN($M:$M)), ($X$2:$X2=$E3)*(100*ROW($X$2:$X2)+COLUMN($AG:$AG)))

em AR3(pulando AR2). Termine com Ctrl++ Shift, Enterpara torná-la uma fórmula de matriz. Da mesma forma, defina AS3para

=MAX(($E$2:$E2=$X3)*(100*ROW($E$2:$E2)+COLUMN($M:$M)), ($X$2:$X2=$X3)*(100*ROW($X$2:$X2)+COLUMN($AG:$AG)))

como uma fórmula de matriz. (Isso é o mesmo, AR3 exceto que as duas ocorrências de $E3foram substituídas por $X3.)

Definido K3como

=IF($AR3=0, "?", INDEX($A$1:$BG$999, INT($AR3/100), MOD($AR3,100)))

e AF3para

=IF($AS3=0, "?", INDEX($A$1:$BG$999, INT($AS3/100), MOD($AS3,100)))

(não como fórmulas de matriz). Eles são iguais, exceto que as três ocorrências de $AR3foram substituídas por $AS3.

E, claro, arraste/preencha.

As colunas auxiliares encontram as ocorrências anteriores mais recentes dos nomes - encontram a ocorrência anterior mais recente de e encontram a ocorrência anterior mais recente de - basicamente encontrando o máximo, ao longo das linhas anteriores, deARnEnASnXn

(valor_anterior=este_valor) * LINHA())

ou seja, o número de linha mais alto onde o nome corresponde. Em seguida, codifica o local onde o nome foi encontrado como

100*LINHA() + COLUNA(dados_queremos_copiar)

Ambas as fórmulas aparecem em Colunas Ee Xe retornam as coordenadas codificadas das Colunas Mou AGcélulas correspondentes. Em seguida, as fórmulas Ke AFsimplesmente decodificam o endereço da célula e recuperam o valor.

               

O mesmo AR6acontece com 213 porque “John” ( E6) foi visto mais recentemente na Linha 2 e, como ele foi visto em E2(em vez de X2), queremos copiar o valor da Coluna 13 (Coluna M).

informação relacionada