☰

Función para devolver referencias de celda (direcciones) de N valores más altos

tag-icon • tag-icon microsoft-excel worksheet-function
Función para devolver referencias de celda (direcciones) de N valores más altos

Estoy creando una hoja de cálculo para realizar un seguimiento de los resultados electorales y devolver a los representantes electos. Estamos utilizando un sistema de representación proporcional, aplicando lamétodo Sainte-Laguë modificado. Los resultados de las elecciones se introducen en una hoja de cálculo y luego se dividen por un número determinado (1,4, 3, 5, etc., hasta 15 para un total de ocho cocientes, según lo exige la ley aplicable) para obtener el cociente del cual para distribuir los asientos. Usando formato condicional, la hoja de cálculo ahora resalta los ocho números más altos, mostrando quién obtiene qué posición. Todo esto funciona según lo previsto.

Necesitamos a) devolver una lista de los ocho candidatos seleccionados, yb) hacer que esa lista devuelva la referencia de celda (por ejemplo, D7), en lugar del número. He jugado con el uso de la LARGEfunción y estoy devolviendo con éxito los ocho cocientes principales en C24:C31 usando una fórmula que es, esencialmente, =LARGE($C$2:$J$21, ROW()-23). Sin embargo, no puedo lograr que devuelva las coordenadas de la cuadrícula para estos cocientes.

La siguiente captura de pantalla muestra una maqueta de lo que quiero lograr. Las celdas resaltadas en verde se resaltan como resultado del formato condicional; son los ocho valores más grandes en C2:J21. Las celdas resaltadas en amarillo son lo que quiero lograr. Para ser claro; los datos en la referencia de celda C2:J5 se generan mediante una fórmula, de igual manera los valores en C24:C31 son los mismos ocho valores que están resaltados en verde, arriba; son fórmulas generadas. Las filas ocultas del 6 al 21 son esencialmente duplicados de las filas del 1 al 5. He subido la hoja de cálculo aGoogle Drive

Captura de pantalla de la hoja de cálculo en cuestión.

Respuesta1

Esto es complicado. Nosotros podemos usar

=MÍN(SI($C$2:$J$21=valor, FILA($C$2:$J$21)))
para encontrar el número de la primera fila en la quevalueaparece. Asimismo,
=MÍN(SI($C$2:$J$21=valor, COLUMNA($C$2:$J$21)))
encuentra el número de la primera columna en la quevalueaparece. Las anteriores son fórmulas matriciales.

Si los ocho valores principales C2:J21son únicos, Podemos usar lo anterior para encontrar un valor en esa cuadrícula. Entonces

ÍNDICE ($A$1:$J$21,numero de fila,número_columna)
indexaremos esa celda, y podemos usar
CELDA("dirección", ÍNDICE(lo anterior))
para obtener la dirección de fila y columna de esa celda.

Entonces, entra

=CELDA("dirección", ÍNDICE ($A$1:$J$21, MIN(SI($C$2:$J$21=C24,FILA($C$2:$J$21))), MIN(SI($C $2:$J$21=C24,COLUMNA($C$2:$J$21)))))
en la celda B24, presione Ctrl+ Shift+ Entery arrastre/rellene hacia abajo hasta B31.

Notas:

  • CELL("address", …)devuelve una dirección absoluta (con signos de dólar). Si no los quieres, puedes usarlos. 
    =SUSTITUIR(CELDA(bla, bla, bla), "$", "")
  • Esto no maneja bien los valores duplicados. Por ejemplo, si E3también contiene 8 (además de D4), informará que ambos están en D3(primera fila y primera columna). Esto puede ser muy difícil de solucionar, pero sería posibleprueba si tal coincidencia había ocurrido, para permitir la corrección manual. 
  • Si bien esto se puede hacer todo en una celda, podría simplificar su vida a largo plazo si usa columnas auxiliares para los valores de filas y columnas.

Esta respuesta se basa en parte en un truco deesta respuestapor barry houdini.

información relacionada