Promediar los "k" mayores valores de un listado - Aclaración sobre la expresión empleada

Question

Buenas tardes.

Hace unos días me enfrenté a la necesidad de calcular el promedio de los "k" mayores valores de un listado, con la dificultad añadida de que podían existir valores duplicados en dicho listado.

Comento lo de dificultad añadida porque la primera idea que tuve para solucionarlo, aplicando algo tan sencillo como:

=SUMAR.SI(B3:B12;">="&K.ESIMO.MAYOR(B3:B12;$B$14))/$B$14,

donde "$B$14" hace referencia al número de valores a considerar para el promedio, resultó un fracaso. Al realizar pruebas, pude comprobar que cuando el k-ésimo valor era idéntico al k-ésimo +1 (pudiendo ser igual, a su vez, al k-ésimo +2, etc.) el promedio consideraba, respectivamente, k+1, k+2, etc. valores, proporcionando así resultados distintos de los deseados.

Pese a esto, tuve la suerte de encontrar una publicación -que agradezco enormemente- en donde figuraba una expresión que, debidamente modificada, funciona a la perfección:

=SUMAPRODUCTO(K.ESIMO.MAYOR(B3:B12;FILA(INDIRECTO(CONCAT("1:";$B$14)))))/$B$14

Con el problema resuelto, sin embargo, mi curiosidad me ha llevado a intentar comprender en profundidad el funcionamiento de la expresión, algo que no he conseguido y por lo que solicito la ayuda de la comunidad.

Si bien es cierto que las funciones empleadas en la expresión no me resultan ajenas, ni mucho menos, también lo es el que en este caso se emplean de formas que desconocía y no termino de comprender. Al intentar desgranarla, de esta forma, me he encontrado con las siguientes cuestiones:

Nota: En lo que sigue, obviaré conscientemente tanto la referencia absoluta al número de valores sobre los que la expresión calcula el promedio como el empleo de la función CONCATenar, ya que corresponden a modificaciones que realicé sobre la expresión original para adaptarla a mis necesidades y, además, considero que carecen de relevancia.

1. La función CONCATenar, de forma aislada, devuelve explícitamente "1:k", donde "k" corresponde al número de valores sobre los que realizar el promedio. Es decir, el tipo de resultado que arrojaría sería, por poner un ejemplo numérico, "1:5".

2) La función INDIRECTO, aplicada -de nuevo de forma aislada- sobre la anterior, proporciona directamente un error, algo que no me extraña puesto que la referencia que recibe se encuentra, hasta donde yo sé, incompleta. Aquí es donde comienzan mis dudas, ya que la función INDIRECTO devuelve una referencia especificada por un valor de texto y no entiendo qué referencia está devolviendo en realidad (al margen del error que para mí es natural debido a los datos que recibe de entrada).

3) Pese a este error mencionado -y para mi sorpresa-, la función FILA, la cual devuelve el número de fila de una referencia, aplicada sobre lo anterior proporciona el resultado "1"; siempre el mismo y, aparentemente, independiente del valor "k".

4) Lo anterior, una vez más, me descoloca, ya que, partiendo de un supuesto error, la función K.ESIMO.MAYOR, que devuelve el valor k-ésimo mayor de un conjunto de datos, aplicada sobre esta función FILA, arroja un único resultado, que para mí no tiene sentido -si se me perdona la ignorancia-.

5 ) Finalmente, la aplicación de la función SUMAPRODUCTO, la cual devuelve la suma de los productos de rangos o matrices correspondientes, sobre un único argumento, tal y como se emplea en este caso, me resulta totalmente opaca -no he encontrado nada sobre su aplicación de este modo en la ayuda de Excel-.

La cuestión, no obstante, más allá de mi comprensión, es que la expresión funciona a la perfección para su propósito... Así que, con todo lo anterior, agradeceré cualquier aclaración al respecto que me pueda proporcionar la comunidad.

Un saludo.

Answer 1

Tanquam ex ungue leonem

=FILA(INDIRECTO(CONCAT("1:";$B$14))) devuelve un array con el número de máximos que buscas (1,2,3,4 si buscas cuatro máximos)

=K.ESIMO.MAYOR(B3:B12;FILA(INDIRECTO(CONCAT("1:";$B$14)))) devuelve un array con los máximos solicitados.

=sumaproducto....  suma los elementos (la operación producto no la hace porque solo se suministra un array)

/B14  no tengo que explicarlo (espero)

Answer 2

En primer lugar, muchas gracias por la respuesta y su prontitud.

Cuando veo cosas como ésta pienso y yo que creía que sabía algo... Intuía que por ahí iban los tiros -arrays, vectores o matrices de n x 1, como prefiera decirse-, pero sólo lo intuía. Hasta la fecha no había trabajado sobre elementos de este tipo con estas funciones; de hecho, ni sabía que se podía hacer; y la ayuda de Excel, en este caso, tampoco ayuda -no se hace referencia a este tipo de elementos en ninguna de las ayudas correspondientes a estas funciones-.

Aunque no voy a mentir diciendo que ya comprendo con profundidad la expresión -todavía sigo viendo como coja o incompleta la función INDIRECTO en la expresión, por ejemplo-, sea como fuere, le agradezco enormemente la aclaración.

Un saludo.

Answer 3

CONCAT("1:";$B$14)    genera una cadena de texto ("1:4" en nuestro caso)

INDIRECTO Devuelve la referencia especificada por una cadena de texto (la que ha generado CONCAT. 

FILA es la función que genera los números 1,2,3,...  tantos como filas tenga el rango (1,2,3,4). En realidad es un truco para generar una sucesión de números.

Espero que ahora te quede  claro.

Supongo que la fórmula será de Hector Miguel, de ahí, Tanquam ex ungue leonem.

Answer 4

Buenas tardes.

Una vez más, agradezco su interés.

En cuanto a lo que comenta en esta última respuesta, sobre la función CONCAT no tengo duda alguna, entre otras cosas porque es una modificación que introduje yo mismo en la expresión para poder referenciar el número deseado de valores sobre los que hacer el promedio, en vez de indicar un valor fijo, implícitamente contenido en la expresión.

No sé si con esto que voy a comentar lograré explicar el por qué se me hace tan extraña esta construcción -más allá del ya aclarado empleo de arrays-, pero aún así lo voy a intentar.

En el caso, por ejemplo, de la función INDIRECTO, hasta donde creía saber, necesita un argumento que le indique una referencia a una celda que, a su vez, contenga una referencia a otra celda, un nombre definido como una referencia o una referencia a una celda como cadena de texto. Sin embargo, en este caso, lo que encuentro que recibe la función como argumento es, por ejemplo, como ha mencionado, "1:4"; una referencia que -recalco una vez más, hasta donde yo conozco- no me parece completa -de 1 a 4 qué, A, B, C...-. No sé si termino de hacerme entender...

Al contrario de lo habitual para mí, parece que en este caso las funciones sólo tienen sentido agrupadas y, además, que sólo cobran un sentido completo al llegar hasta la función K.ESIMO.MAYOR, donde realmente se le proporciona a la expresión el rango de valores afectados.

Pese a sus más que satisfactorias aclaraciones, no puedo evitar sentir extrañeza ante esta expresión, puesto que nunca había trabajado ni me había encontrado con algo semejante.

En cuanto a la procedencia de la expresión original, puedo remitirle la página de donde la he extraído (trucosycursos.es), aunque, desgraciadamente, no puedo confirmarle su autoría.

Un saludo. Y, una vez más, muchas gracias por su ayuda.

Answer 5

Lee esto:https://docs.microsoft.com/es-es/office/troubleshoot/excel/indirect-function-to-create-reference