Pivotar datos filtrados sin celdas vacías: PIVOTARPOR vs REDUCE

Reto de LinkedIn que un miembro trae al grupo evitando ver las soluciones originales: tiene una tabla con categorías, meses y valores, y necesita pivotarla mostrando solo las celdas con dato (sin huecos). La dificultad está en que PIVOTARPOR rellena con ceros las combinaciones inexistentes.

Con PIVOTARPOR básico (Leo): el punto de partida. Genera la tabla pivotada pero con ceros en las celdas vacías:

``
=PIVOTARPOR(A2:A10; B2:B10; C2:C10; SINGLE;; 0;; 0)
`

Con PIVOTARPOR + orden cronológico (Leo): para que los meses aparezcan en orden, añade COINCIDIRX como columna auxiliar con APILARH:

`
=PIVOTARPOR(
A2:A10;
APILARH(COINCIDIRX(B2:B10; meses_ordenados); B2:B10);
C2:C10;
SINGLE;; 0;; 0
)
`

Con REDUCE + SECUENCIA + ENCOL (Leo): la solución avanzada que elimina los ceros. Recorre columna a columna extrayendo solo los valores no vacíos con ENCOL(; 2):

`
=LET(
m; HALLAR(B2:B10; G8#) * C2:C10;
EXCLUIR(
REDUCE(0; SECUENCIA(COLUMNAS(m)); LAMBDA(a; b;
APILARH(a; ENCOL(INDICE(m;; b); 2))
));; 1
)
)
`

Con PIVOTARPOR + LAMBDA personalizada (Gerson): usa una función de agregación personalizada dentro de PIVOTARPOR para controlar exactamente qué se devuelve:

`
=PIVOTARPOR(
A2:A10; B2:B10; C2:C10;
LAMBDA(x; SI.ND(FILTRAR(x; x <> 0); ""));; 0;; 0
)
`

Un caso que muestra la versatilidad de PIVOTARPOR y cómo REDUCE` puede ser la alternativa cuando se necesita un control más fino sobre el resultado.

El problema: pivotar mostrando solo las celdas con dato

Un miembro trajo al grupo un reto de LinkedIn (sin mirar las soluciones originales): una tabla con categorías, meses y valores que hay que pivotar mostrando únicamente las celdas con dato, sin huecos. El obstáculo: PIVOTARPOR rellena con ceros todas las combinaciones categoría-mes que no existen, y eso ensucia la tabla.

Punto de partida: PIVOTARPOR básico

Leo arrancó con el pivote directo, que resuelve la estructura pero deja los ceros:

`` =PIVOTARPOR(A2:A10; B2:B10; C2:C10; SINGLE;; 0;; 0) ``

Genera la matriz de categorías por meses, pero cada combinación inexistente aparece como 0 en lugar de vacía.

Ordenar los meses cronológicamente

Antes de limpiar los ceros, un problema clásico: los meses salen en orden alfabético, no cronológico. Leo lo arregló añadiendo COINCIDIRX contra una lista de meses ordenados como columna auxiliar dentro de APILARH:

`` =PIVOTARPOR( A2:A10; APILARH(COINCIDIRX(B2:B10; meses_ordenados); B2:B10); C2:C10; SINGLE;; 0;; 0 ) ``

El COINCIDIRX aporta la posición de cada mes en el calendario, y PIVOTARPOR la usa para ordenar las columnas.

La solución que elimina los ceros: REDUCE + ENCOL

Para quitar de verdad los huecos, Leo cambió de herramienta y recorrió la matriz columna a columna con REDUCE, extrayendo en cada una solo los valores no vacíos con ENCOL(...;2):

`` =LET( m; HALLAR(B2:B10; G8#) * C2:C10; EXCLUIR( REDUCE(0; SECUENCIA(COLUMNAS(m)); LAMBDA(a; b; APILARH(a; ENCOL(INDICE(m;; b); 2)) ));; 1 ) ) ``

ENCOL(...;2) es la clave: el segundo argumento a 2 le dice que ignore las celdas vacías al apilar, así que cada columna queda compactada sin ceros. REDUCE va pegando columna tras columna y EXCLUIR retira la fila semilla inicial.

Un cuarto camino: función de agregación a medida

Gerson demostró que PIVOTARPOR admite una LAMBDA de agregación personalizada en lugar de la típica SUMA. Con ella se puede controlar exactamente qué devuelve cada celda: filtrar los valores iguales a cero y, si no queda nada, dejar la celda vacía con SI.ND. Es la vía más integrada, porque resuelve el problema dentro del propio PIVOTARPOR sin post-procesar la matriz.

Funciones clave

  • PIVOTARPOR: monta la tabla dinámica por fórmula; admite función de agregación personalizada.
  • COINCIDIRX + APILARH: ordenan los meses cronológicamente.
  • REDUCE + INDICE: recorren la matriz columna a columna.
  • ENCOL(...;2): apila una columna descartando las celdas vacías.
  • EXCLUIR / SI.ND: retiran filas semilla o vacían celdas sin dato.

Conclusión

El caso enseña dos cosas: que PIVOTARPOR es mucho más flexible de lo que parece —acepta hasta tu propia función de agregación— y que, cuando necesitas un control fino sobre el resultado, REDUCE con ENCOL es la alternativa que compacta la matriz a voluntad. Cuatro enfoques para un mismo pivote, tal como se debaten cada semana en la comunidad de InflueXcel.

Más casos con estas funciones

Más contenido de Excel en InflueXcel