Limitación de BYCOL: cómo obtener múltiples valores por columna

Quieres los cinco valores más altos de cada columna de un rango. Escribes un BYCOL con K.ESIMO.MAYOR, parece razonable, y Excel te devuelve un error. La explicación de Leo en la comunidad aclara una limitación de BYCOL y BYROW que conviene tener grabada.

El problema

La fórmula de partida era esta:

=APILARH(BYCOL(A1:X100; LAMBDA(col; K.ESIMO.MAYOR(col; {1;2;3;4;5}))))

La idea se entiende perfectamente: recorrer cada columna, y para cada una pedir el primero, segundo, tercero, cuarto y quinto mayor mediante una constante matricial. El resultado esperado sería una tabla de 5 filas por tantas columnas tenga el rango.

La causa: BYCOL devuelve un valor por columna

Leo identifica el problema al instante: BYCOL no puede devolver más de un elemento por columna. La LAMBDA que le pasas tiene que producir un único valor escalar en cada iteración.

Y K.ESIMO.MAYOR con una constante matricial de cinco posiciones devuelve cinco valores. BYCOL no tiene forma de encajar eso en su matriz de salida.

Lo mismo vale para BYROW: una LAMBDA por fila, un valor por fila. Ambas están pensadas para reducir cada línea a un dato, no para expandirla.

Merece la pena recordar el reparto de papeles entre las funciones de matriz:

• BYROW y BYCOL reducen: muchos valores dentro, uno fuera
• MAP transforma elemento a elemento, también con un valor de salida por cada entrada
• REDUCE acumula y es la única que admite resultados de tamaño variable

Solución 1: consolidar los valores en un texto

Gerson propone una salida elegante cuando lo que quieres es ver los valores, no operar con ellos: unirlos en una sola cadena separada por comas, de forma que la LAMBDA sí devuelva un escalar.

=APILARH(BYCOL(A1:X100; LAMBDA(col; UNIRCADENAS(","; ; K.ESIMO.MAYOR(col; {1;2;3})))))

UNIRCADENAS recibe los tres mayores de la columna y los colapsa en un único texto. BYCOL ya está contento, porque cada iteración produce un valor.

Es una técnica muy práctica para informes de un vistazo. La contrapartida es evidente: el resultado es texto, y si luego necesitas calcular con esos números tendrás que volver a trocearlos.

Solución 2: si solo necesitas un valor, simplifica

Gerson señala además algo que se pasa por alto con facilidad. Si lo que quieres es un solo dato por columna (el máximo, por ejemplo), la fórmula se reduce a su mínima expresión:

=BYCOL(A1:X100; MAX)

Sin LAMBDA y sin APILARH. Fíjate en dos detalles:

• Puedes pasar el nombre de la función directamente como segundo argumento, sin envolverlo en LAMBDA, siempre que solo reciba un argumento
• El APILARH de la fórmula original era innecesario: BYCOL ya devuelve una fila de resultados con la forma correcta

Ese APILARH sobrante es un síntoma habitual cuando una fórmula ha ido creciendo a base de parches.

Solución 3: REDUCE para múltiples valores reales

Cuando de verdad necesitas los cinco mayores como números y no como texto, Leo apunta a REDUCE. Al arrastrar un acumulador, permite ir apilando bloques de cualquier tamaño con APILARH o APILARV, columna a columna, hasta construir la tabla completa.

Es más verboso que BYCOL, pero es la herramienta correcta para el trabajo: la única de la familia que no impone un valor por iteración.

Funciones clave

• BYCOL — recorre columnas; exige un valor escalar por columna
• BYROW — misma limitación, aplicada a filas
• K.ESIMO.MAYOR — devuelve el n-ésimo mayor; admite una constante matricial para pedir varios de golpe
• UNIRCADENAS — colapsa varios valores en un texto para satisfacer a BYCOL
• REDUCE — la alternativa cuando el resultado por iteración tiene tamaño variable
• MAX — recuerda que puedes pasarla directamente como función, sin LAMBDA

Conclusión

Ante un error con BYROW o BYCOL, la pregunta que resuelve el 90% de los casos es: cuántos valores devuelve mi LAMBDA. Si son más de uno, o lo colapsas en un texto o cambias a REDUCE.

Casos como este surgen a diario en la comunidad de InflueXcel, donde una duda de tres líneas acaba explicando cómo está diseñada toda una familia de funciones.

Más casos con estas funciones

• LAMBDA
• APILARH
• REDUCE
• BYROW
• UNIRCADENAS
• BYCOL

Más contenido de Excel en InflueXcel

• Descomposición de Cholesky con matrices dinámicas: de VBA a LAMBDA+REDUCE Caso
  Juan Pablo lanza un reto al grupo: tiene una descomposición de Cholesky resuelta con VBA y quiere saber si se puede hacer con matrices dinám
• Saldo acumulado por mes: tres enfoques (REDUCE+BYROW, PIVOTARPOR+acumulado, MMULT) Caso
  Juan plantea una pregunta que parece sencilla y se acaba convirtiendo en tres clases magistrales sobre cómo recorrer una matriz mes a mes. T
• Optimización de REDUCE+APILARV con LAMBDA recursiva en bisección Caso
  Alejandro plantea un reto de rendimiento interesante: tiene una fórmula LET enorme que calcula la permanencia de carga en puerto por matrícu
• Error #CALC con MAP y AGRUPARPOR: solución con REDUCE+APILARV Caso
  Nuevo reto de Excel resuelto por la comunidad: un usuario necesita aplicar AGRUPARPOR de forma iterativa sobre un rango de códigos de cuenta
• Serie de Fibonacci con LAMBDA y REDUCE en una sola fórmula Caso
  Surge un reto interesante en la comunidad: generar la serie de Fibonacci con una sola fórmula de Excel, sin celdas auxiliares ni macros. Un
• Generar la serie de Fibonacci con REDUCE, APILARV y LAMBDA Caso
  Interesante ejercicio compartido en la comunidad: generar los primeros N números de la serie de Fibonacci usando exclusivamente fórmulas de
• Funciones window en Excel: el total del grupo en cada fila con LAMBDA y BYROW Tutorial
  En SQL se llaman funciones window: columnas que, para cada fila, traen un agregado calculado sobre un grupo mayor. El total de ese cliente a
• Filtrar una tabla por una lista de valores: una LAMBDA propia y su inversa Tutorial
  Te pasan una lista de 30 números de albarán y hay que sacar esas filas de una tabla de miles. Con el autofiltro es marcar casillas una a una
• Crear una función LAMBDA reutilizable para agrupar ventas por punto de venta Caso
  Un miembro de la comunidad comparte un fichero con una función personalizada llamada xMGP_SUG registrada en el Administrador de nombres. El
• Consolidar múltiples hojas con APILARV, referencias 3D y FILTRAR Caso
  Fito necesita consolidar varias hojas de Excel que comparten la misma cabecera pero tienen distinto número de filas con datos de gastos de v