El secreto para informes profesionales en Excel

El secreto para informes profesionales en Excel

Te mostramos paso a paso cómo aplicar la multiplicación de matrices junto con las funciones SUMA, HALLAR y ESNUMERO para crear un reporting financiero eficiente y claro

¿Crees que necesitas funciones complicadas o modelos de datos avanzados para montar informes financieros con aspecto profesional en Excel? La realidad es justo la contraria. En este tutorial verás cómo construir una cuenta de resultados completa, flexible y estable usando solo tres funciones muy sencillas: SUMA, HALLAR y ESNUMERO. El mismo método sirve para reportes de recursos humanos o cualquier otra cosa que tengas que reportar a partir de una tabla de datos planos.

Dos conceptos previos: operar con rangos

Antes de tocar ninguna función, hay dos ideas fundamentales sobre cómo trabajan los rangos en Excel.

  • Multiplicación de rangos línea a línea. Si tienes dos rangos con el mismo número de filas, puedes multiplicarlos directamente (=rango1*rango2) y obtienes un nuevo rango con el producto de cada par de valores fila a fila.
  • Condiciones que devuelven verdadero o falso. Si aplicas una condición a un rango (por ejemplo, rango>150), Excel te devuelve una columna de VERDADERO/FALSO para cada valor. Lo interesante es que ese resultado se comporta como ceros y unos: FALSO equivale a 0 y VERDADERO a 1.

Combinando ambas ideas, si multiplicas un rango de valores por una matriz de verdaderos y falsos, donde la condición es falsa el valor se convierte en cero y donde es verdadera se conserva. Esta es la base de todo el método.

La función SUMA

La función SUMA la conoce todo el mundo: suma todos los valores de un rango. La clave aquí es que puede sumar el resultado de una operación entre rangos. Si multiplicas un rango de importes por una columna de verdaderos y falsos y lo envuelves todo en SUMA, obtienes el total únicamente de los valores que cumplen la condición.

HALLAR y ESNUMERO: filtrar por coincidencias

La función HALLAR busca un texto dentro de otro y te devuelve la posición en la que lo encuentra; si no lo encuentra, devuelve un error. El truco que da potencia al método es invertir el planteamiento habitual: en lugar de buscar una palabra dentro de un listado, buscas cada valor del listado dentro de un texto de referencia.

De esta forma puedes concatenar varios criterios separados por comas (por ejemplo, omega,alfa,delta) y HALLAR te indica la posición de cada coincidencia. Esto permite filtrar por más de un valor a la vez.

El problema es que HALLAR devuelve números cuando encuentra y errores cuando no. Para convertir eso en la columna de verdaderos y falsos que necesitamos entra ESNUMERO: envolviendo el HALLAR con ESNUMERO obtienes VERDADERO donde hay coincidencia y FALSO donde había error.

Montando la cuenta de resultados

Con estos tres ingredientes se construye el informe. Partimos de una tabla de datos planos con columnas como periodo, centro, cuenta contable, cliente y saldo. El objetivo es una cuenta de resultados con epígrafes (ingresos, costes directos, costes fijos, overheads), subtotales y totales, y con columnas por periodo.

El cálculo de cada celda combina las tres funciones multiplicando rangos:

  1. Tomas el rango de saldos de la contabilidad.
  2. Lo multiplicas por ESNUMERO(HALLAR(...)) aplicado a la columna de cuenta (para quedarte solo con la cuenta deseada, por ejemplo la 1000).
  3. Lo multiplicas además por ESNUMERO(HALLAR(...)) aplicado a la columna de periodo (para quedarte con el periodo correcto, por ejemplo AP0123).
  4. Envuelves todo en SUMA y obtienes el importe exacto de esa cuenta para ese periodo.

Fijando referencias con $ (columna del criterio de fila, fila de la cabecera de columna) puedes arrastrar la fórmula a toda la matriz del informe y rellenar cada mes y cada cuenta automáticamente.

Subtotales que no dependen de otras celdas

Aquí está una de las grandes ventajas del método. Los subtotales no suman celdas de la propia hoja (algo frágil si añades o quitas filas), sino que vuelven a calcularse sobre la tabla original. Basta con indicar en el criterio los valores que quieres agregar, por ejemplo varias cuentas concatenadas, y la fórmula recalcula directamente desde el origen.

Para no tener que escribir esos criterios a mano, puedes usar UNIRCADENAS:

  • Indicas un delimitador (una coma).
  • Pones VERDADERO para ignorar celdas vacías.
  • Seleccionas las celdas con las definiciones que quieres agregar.

Así, un subtotal une las definiciones de las líneas que abarca y se mantiene correcto aunque cambies cuentas, insertes filas o reorganices el informe. El mismo enfoque sirve para totales por trimestre: con UNIRCADENAS agregas varias columnas de meses y obtienes la comparativa, pudiendo después conservar solo el dato del trimestre y eliminar el detalle mensual sin romper nada.

Conclusión

Con SUMA, HALLAR y ESNUMERO, más la idea de operar con rangos y matrices de verdaderos y falsos, se consigue un sistema de reporting estable, fácil de hacer y totalmente adaptable, sin recurrir a modelos de datos. A partir de aquí puedes enriquecerlo con filtros globales del informe o llevarlo más lejos con LAMBDA y otras funciones avanzadas, pero el núcleo es tan sencillo que puedes empezar a aplicarlo hoy mismo.

Más contenido de Excel en InflueXcel