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

Question

Estou tentando automatizar mais completamente o cálculo de valores e dados de resumo em uma planilha que mantenho sobre os resultados de correspondências em uma liga de pool.

Eu tenho uma tabela com muitas informações sobre cada partida, com os campos relevantes sendo: Data do Jogo, Vencedor, Handicap Inicial do Vencedor, Vencedor do Final do Vencedor, Perdedor, Perdedor de Partida Perdedor, Perdedor de Final de Perdedor, Horário de Início da Prova.

Os handicaps são ajustados no final de cada partida e antes do próximo. É doloroso encontrar o mais recente recorde passado de um jogador (poderia ser um Vencedor ou Perdedor) e copiar seu handicap final daquele registro para o Handicap Inicial (vencedor ou perdedor) para o que estou entrando agora.

Gostaria que uma fórmula que encontrasse o registro mais recente (maior data e hora de início, caso ele jogasse duas vezes em um dia), onde ele foi um vencedor ou um perdedor, e obtenha o handicap final (do respectivo vencedor ou perdedor).

Por sugestão do teylyn, aqui está um link do Dropbox para o arquivo. A guia relevante é Resultados da partida: link

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

    

Answer 1

Declaração do problema

Uma planilha tem nomes em Colunas E e X . Para cada linha n , En ≠ Xn . Há números na coluna M correspondentes aos nomes na coluna E , e números na coluna AG correspondente aos nomes na coluna X . Para qualquer linha após a primeira (digamos, Linha 42), Queremos obter valores para K42 e AF42 das linhas anteriores, se possível.

• Se E42 for "João", encontre a linha mais recente que contém "John" (na coluna E ou X ). Chame essa linha n . Se En ="John", defina K42 igual a Mn . Se Xn ="João", defina K42 igual a AGn .

• Se X42 for "Scott", encontre a linha mais recente que contém "Scott" (na coluna E ou X ). Chame essa linha n . Se En ="Scott", defina AF42 igual a Mn . Se Xn ="Scott", defina AF42 igual a AGn .

  

Solução

Naesperançadepreservaralgumasanidade,vamosusarcolunasauxiliares;digamosAReAS.Suponhaque(comonoarquivodeexemplo),osdadosiniciemnaFila2.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)))

emAR3(ignorandoAR2).TerminarcomCtrl+Deslocar+Introduzir,paratorná-loumafórmuladematriz.Damesmaforma,definaAS3para

=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)))

comoumafórmuladematriz.(IssoéomesmoqueAR3excetoqueasduasocorrênciasde$E3foramsubstituídaspor$X3.)

DefinaK3para

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

e AF3 para

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

(não como fórmulas de array). Estes são os mesmos exceto que as três ocorrências de $AR3 foram substituídas por $AS3 .

E, claro, arraste / preencha.

As colunas auxiliares localizam as ocorrências anteriores mais recentes dos nomes - ARn encontra a ocorrência anterior mais recente de En e ASn encontra a ocorrência anterior mais recente de Xn - basicamente encontrando o máximo, ao longo das linhas anteriores, de

(previous_value=this_value) * ROW())

, ou seja, o número da linha mais alta em que o nome é uma correspondência. Em seguida, ele codifica o local onde o nome foi encontrado como

100*ROW() + COLUMN(data_we_want_to_copy)

Ambas as fórmulas aparecem em Colunas E e X , e retornar as coordenadas codificadas das colunas correspondentes M ou AG cell. Em seguida, as fórmulas K e AF simplesmente decodificam o endereço da célula e recuperar o valor.

EntãoAR6é213porque"John" ( E6 ) foi visto mais recentemente na Linha 2, e, desde que ele foi visto em E2 (em vez de X2 ), queremos copiar o valor da coluna 13 (coluna M ).