PIVOTARPOR: cadenas vacías y orden cronológico mensual

Juan trae dos problemas de PIVOTARPOR el mismo día, ambos muy prácticos y con soluciones elegantes de la comunidad.

Problema 1: cadenas vacías que impiden operar

Juan tiene una tabla de proyectos con columnas Proyecto, Tipo (Budget/Actual) e Importe. Al pivotar con PIVOTARPOR, cuando un proyecto no tiene valor de Budget, la celda resultante no queda vacía sino con una cadena de texto vacía "". Esto hace que cualquier operación aritmética posterior (resta Actual - Budget) devuelva #VALOR!.

El fichero adjunto muestra el problema: Proyecto1 tiene Actual (8) y Budget (4), pero Proyecto2 solo tiene Actual (3). Al intentar =LAMBDA(x;y;x-y)(I7:I8;J7:J8), falla porque J8 es "", no 0.

La comunidad propone varias soluciones:

John resuelve el problema directamente en el PIVOTARPOR, multiplicando por -1 elevado a una condición para invertir el signo del Budget:

``
=PIVOTARPOR(C6:C194; D6:D194; -1^(D6:D194="Budget")*E6:E194; SUMA)
`

Así el total ya sale como Actual - Budget sin necesidad de operar después. Para la resta por separado, propone usar N(+x) que convierte cadenas vacías a cero:

`
=LAMBDA(x;y;N(+x)-N(+y))(I7:I8;J7:J8)
`

Nacho usa el doble negativo -- dentro de la LAMBDA para forzar la conversión a número: LAMBDA(x;y;(--x)-(--y)).

Alejandro envuelve todo el PIVOTARPOR en un LET con SI al final para limpiar los vacíos:

`
=LET(
_m; PIVOTARPOR(APILARH(C6:C194); D6:D194;
SI(D6:D194="Budget"; -E6:E194; E6:E194); SUMA;; 1;; 1);
SI(_m=""; 0; _m)
)
`

Problema 2: PIVOTARPOR mensual con orden cronológico

Por la noche, Juan vuelve con otro reto: quiere un resumen mensual con PIVOTARPOR donde los meses aparezcan ordenados cronológicamente (Ene, Feb, Mar...) en vez de alfabéticamente.

Leo lo clava con una sola fórmula usando TEXTO con formato dual para crear una clave de ordenación invisible:

`
=EXCLUIR(
PIVOTARPOR(
A5:A193;
APILARH(TEXTO(C5:C193; {"mm"\"e-mmm"}); B5:B193);
D5:D193;
SUMA;; 1;; 0
);
1
)
`

El truco está en TEXTO(fecha;{"mm"\"e-mmm"}): genera dos columnas, una con el número de mes ("mm" → "01", "02"...) para que PIVOTARPOR ordene correctamente, y otra con el nombre abreviado ("e-mmm" → "e-ene", "e-feb"...) para mostrar. Después EXCLUIR(...;1)` elimina la columna numérica auxiliar.

Juan reacciona entusiasmado: "perfecta!!! Al último paso no llego ni en 100 vidas" 😄

Dos problemas de PIVOTARPOR en el mismo día

Juan trajo a la comunidad dos dudas sobre PIVOTARPOR con pocas horas de diferencia. Las dos son de las que aparecen en cuanto empiezas a usar la función en serio, y las dos tienen soluciones elegantes.

Problema 1: las cadenas vacías que rompen la aritmética

Juan tiene una tabla de proyectos con tres columnas: Proyecto, Tipo (Budget o Actual) e Importe. Al pivotar, cuando un proyecto no tiene valor de Budget, la celda resultante no queda vacía: queda con una cadena de texto vacía.

Y ahí está la trampa. Una celda con texto vacío parece vacía, pero no lo es. Cualquier operación aritmética posterior devuelve #VALOR!.

En su fichero, Proyecto1 tiene Actual (8) y Budget (4), pero Proyecto2 solo tiene Actual (3). Al intentar restar los dos bloques con una LAMBDA, falla, porque la celda del Budget de Proyecto2 contiene texto vacío en lugar de un cero.

Solución A: resolver la resta dentro del propio PIVOTARPOR

John da la vuelta al problema. En lugar de pivotar y restar después, invierte el signo de los importes de Budget antes de agregar, de modo que la suma ya sea la diferencia:

=PIVOTARPOR(
    C6:C194;
    D6:D194;
    SI(D6:D194="Budget"; -E6:E194; E6:E194);
    SUMA
)

El tercer argumento ya no es la columna de importes tal cual, sino una matriz donde los importes de Budget llegan en negativo. El total sale directamente como Actual menos Budget, y no hay ninguna resta posterior que pueda fallar. John planteó la misma idea de forma más compacta usando una potencia de menos uno elevada a la condición, que da el mismo cambio de signo.

Solución B: convertir el texto vacío en cero

Si necesitas la resta por separado, el truco está en forzar la conversión a número antes de operar. La función N combinada con un más unario delante de la referencia hace justo eso:

=LAMBDA(x; y; N(+x) - N(+y))(I7:I8; J7:J8)

N convierte el texto vacío en cero y deja los números intactos. Nacho apuntó una variante equivalente con doble negación, que es el atajo clásico para lo mismo.

Solución C: limpiar la matriz al final con LET

Alejandro envuelve todo el pivotado en un LET y limpia los vacíos en el último paso:

=LET(
    _m; PIVOTARPOR(APILARH(C6:C194); D6:D194;
        SI(D6:D194="Budget"; -E6:E194; E6:E194); SUMA;; 1;; 1);
    SI(_m=""; 0; _m)
)

La matriz resultante se guarda en una variable y un SI final sustituye cada texto vacío por un cero. Es la más verbosa de las tres, pero también la más explícita: deja el resultado limpio para cualquier cosa que venga después.

Problema 2: meses ordenados cronológicamente, no alfabéticamente

Por la noche, Juan volvió con otro reto. Quería un resumen mensual con PIVOTARPOR donde los meses salieran en orden cronológico (Ene, Feb, Mar...) y no alfabético (Abr, Ago, Dic...), que es lo que hace la función por defecto al agrupar por nombre de mes.

Leo lo clavó con una sola fórmula:

=EXCLUIR(
    PIVOTARPOR(
        A5:A193;
        APILARH(TEXTO(C5:C193; formatos); B5:B193);
        D5:D193;
        SUMA;; 1;; 0
    );
    1
)

Donde formatos es una constante matricial horizontal con dos formatos de fecha: el primero devuelve solo el número de mes en dos dígitos ("01", "02"...) y el segundo devuelve el nombre abreviado precedido de un separador ("e-ene", "e-feb"...).

El truco es precioso. TEXTO aplicado a una matriz de dos formatos genera dos columnas a la vez:

  • La columna del número de mes sirve para que PIVOTARPOR ordene correctamente, porque ordenar "01, 02, 03" alfabéticamente coincide con el orden cronológico.
  • La columna del nombre abreviado es la que se quiere mostrar.

Como PIVOTARPOR ordena por la primera columna de agrupación, los meses salen en su sitio. Y después EXCLUIR con el argumento 1 elimina esa primera columna numérica, que solo estaba ahí para ordenar.

Juan reaccionó como es debido: "perfecta!!! Al último paso no llego ni en 100 vidas".

Funciones clave

  • PIVOTARPOR — pivota filas, columnas y valores en una sola fórmula derramada. Ordena por la primera columna de agrupación.
  • TEXTO — convierte una fecha a texto con el formato indicado. Si el formato es una matriz, devuelve tantas columnas como formatos.
  • APILARH — pega matrices en horizontal. Aquí une la clave de orden con la etiqueta visible.
  • EXCLUIR — descarta las primeras filas o columnas de una matriz. Perfecta para deshacerse de las columnas auxiliares.
  • N — convierte a número. Con un más unario delante, transforma el texto vacío en cero.

Conclusión

Los dos problemas comparten la misma moraleja: cuando PIVOTARPOR no te da lo que quieres, casi nunca hay que retocar el resultado, sino preparar mejor la entrada. El texto vacío se arregla decidiendo el signo antes de agregar; el orden de los meses se arregla añadiendo una clave de ordenación que luego se descarta.

Es una forma de pensar muy propia de las matrices dinámicas, y se repite en muchos casos de la comunidad de InflueXcel: en lugar de parchear la salida, construye la entrada que produce la salida correcta.

Más casos con estas funciones

Más contenido de Excel en InflueXcel