☰

Função para retornar referências de células (endereços) de N valores mais altos

tag-icon • tag-icon microsoft-excel worksheet-function
Função para retornar referências de células (endereços) de N valores mais altos

Estou fazendo uma planilha para acompanhar os resultados eleitorais e retornar os eleitos. Estamos usando um sistema de representação proporcional, aplicando ométodo Sainte-Laguë modificado. Os resultados das eleições são inseridos em uma planilha e depois divididos por um determinado número (1,4, 3, 5, etc., até 15 para um total de oito quocientes, conforme exigido pela lei aplicável) para obter o quociente do qual para distribuir os assentos. Usando formatação condicional, a planilha agora destaca os oito números mais altos, mostrando quem fica em qual posição. Tudo isso funciona como pretendido.

Precisamos a) retornar uma lista dos oito candidatos aprovados eb) fazer com que essa lista retorne a referência da célula (por exemplo, D7), em vez do número. Eu brinquei com o uso da LARGEfunção e estou retornando com sucesso os oito quocientes principais em C24:C31 usando uma fórmula que é, essencialmente =LARGE($C$2:$J$21, ROW()-23),. No entanto, não consigo fazer com que ele retorne as coordenadas da grade para esses quocientes.

A captura de tela abaixo mostra um modelo do que desejo alcançar. As células destacadas em verde são destacadas como resultado da formatação condicional; eles são os oito maiores valores em C2:J21. As células destacadas em amarelo são o que desejo alcançar. Para ser claro; os dados na referência da célula C2:J5 são gerados por uma fórmula, da mesma forma os valores em C24:C31 são os mesmos oito valores destacados em verde acima; eles são gerados por fórmulas. As linhas ocultas de 6 a 21 são essencialmente duplicatas das linhas de 1 a 5. Carreguei a planilha paraGoogle Drive

Captura de tela da planilha em questão

Responder1

Isso é complicado. Podemos usar

=MIN(SE($C$2:$J$21=valor, LINHA($C$2:$J$21)))
para encontrar o número da primeira linha em quevalueparece. Da mesma maneira,
=MIN(SE($C$2:$J$21=valor, COLUNA($C$2:$J$21)))
encontra o número da primeira coluna em quevalueparece. Os itens acima são fórmulas de matriz.

Se os oito principais valores C2:J21forem únicos, podemos usar o acima para encontrar um valor nessa grade. Então

ÍNDICE($A$1:$J$21,número_linha,número_coluna)
indexará essa célula, e podemos usar
CÉLULA("endereço", ÍNDICE(o de cima))
para obter o endereço da linha e coluna dessa célula.

Então, entre

=CÉLULA("endereço", ÍNDICE($A$1:$J$21, MIN(IF($C$2:$J$21=C24,LINHA($C$2:$J$21))), MIN(SE($C $2:$J$21=C24,COLUNA($C$2:$J$21)))))
na célula B24, pressione Ctrl+ Shift+ Entere arraste/preencha até B31.

Notas:

  • CELL("address", …)retorna um endereço absoluto (com cifrões). Se você não os quiser, você pode usar 
    =SUBSTITUIR(CÉLULA(blá blá blá), "$", "")
  • Isso não lida bem com valores duplicados. Por exemplo, se E3também contiver 8 (além de D4), informará que ambos estão em D3(primeira linha e primeira coluna). Isto pode ser muito difícil de resolver, mas seria possívelteste se tal coincidência ocorreu, para permitir a correção manual. 
  • Embora tudo isso possa ser feito em uma célula, pode simplificar sua vida no longo prazo se você usar colunas auxiliares para os valores de linha e coluna.

Esta resposta é baseada parcialmente em um truque deesta respostapor Barry Houdini.

informação relacionada