Distribuir gastos por categoría con SI.CONJUNTO matricial y reparto proporcional

Un miembro de la comunidad tiene una tabla de gastos donde cada línea tiene un concepto (categoría como "SGA", "Overhead", "COGS") y un importe. Necesita distribuir cada importe en la columna correcta según su categoría. Además, los gastos marcados como "Reparto" deben distribuirse proporcionalmente entre todas las columnas según unos pesos definidos en otra fila. El usuario intentaba adaptar una solución anterior y no le funcionaba porque la lógica del reparto proporcional no cuadraba.

Hugo propone un SI anidado con reparto:

``
=SI.ND(SI(D25:D31="Reparto";
E25:E31F22:H22/SUMA(F22:H22);
SI(D25:D31=F10:I10;E25:E31;0));0)
`

Si el concepto es "Reparto", multiplica el importe por el peso de cada columna dividido entre el total de pesos. Si no, compara el concepto con las cabeceras y coloca el importe donde corresponda.

Leo presenta una versión más limpia con LET y SI.CONJUNTO:

`
=LET(
p;F22:H22;
d;D25:D31;
i;E25:E31;
h;F24:I24;
SI.ND(SI.CONJUNTO(
d="Reparto"; ip/SUMA(p);
d=h; i
);0)
)
`

Más legible con LET para nombrar variables y SI.CONJUNTO para manejar los dos escenarios de forma clara. El SI.ND(...;0)` convierte los #N/D (celdas sin coincidencia) en ceros.

Repartir una tabla de gastos en columnas por categoría es un clásico del control de costes: cada línea trae un concepto (SGA, Overhead, COGS…) y un importe, y hay que colocar ese importe en la columna que le toca. La vuelta de tuerca que planteó un miembro de la comunidad es la que complica el ejercicio: algunas líneas no pertenecen a una categoría concreta, vienen marcadas como Reparto y deben distribuirse proporcionalmente entre todas las columnas según unos pesos definidos en otra fila.

Por qué no basta con adaptar una fórmula anterior

El usuario partía de una solución que ya usaba para el caso simple —colocar cada importe bajo su cabecera— y no conseguía encajar el reparto proporcional. Es un tropiezo muy habitual: en realidad no hay un problema, hay dos que conviven en la misma tabla.

  1. Las líneas normales necesitan una comparación: ¿coincide el concepto de esta fila con la cabecera de esta columna? Si sí, el importe; si no, cero.
  2. Las líneas de reparto necesitan un cálculo: el importe multiplicado por el peso de la columna dividido entre la suma de todos los pesos.

Mientras intentes resolverlo con una sola regla, la fórmula no cuadra. La solución pasa por decidir primero de qué tipo es la fila y aplicar después la lógica que corresponda.

La versión de Hugo: un SI anidado con el reparto dentro

=SI.ND(SI(D25:D31 = "Reparto";
    E25:E31 * F22:H22 / SUMA(F22:H22);
    SI(D25:D31 = F10:I10; E25:E31; 0)); 0)

Se lee de fuera hacia dentro:

  • El SI exterior separa los dos mundos: si el concepto es Reparto, se calcula la proporción; si no, se compara contra las cabeceras.
  • E25:E31 * F22:H22 / SUMA(F22:H22) es el reparto propiamente dicho: importe por peso de la columna, dividido entre el total de pesos. Como el importe es un vector vertical y los pesos son un vector horizontal, la multiplicación se expande sola y genera la matriz completa de importes repartidos. Ese comportamiento es el que hace que no necesites arrastrar nada.
  • El SI interior hace la asignación directa: donde el concepto coincide con la cabecera, va el importe; en el resto, cero.
  • SI.ND(...; 0) envuelve todo para convertir en ceros los casos sin coincidencia, que de otro modo aparecerían como error.

La versión de Leo: LET más SI.CONJUNTO

La misma lógica, escrita para que se pueda leer:

=LET(
    p; F22:H22;
    d; D25:D31;
    i; E25:E31;
    h; F24:I24;
    SI.ND(SI.CONJUNTO(
        d = "Reparto"; i * p / SUMA(p);
        d = h; i
    ); 0)
)

Dos mejoras concretas sobre la anterior:

  • LET nombra las piezas. p son los pesos, d los conceptos, i los importes y h las cabeceras. La fórmula deja de ser una sopa de referencias y pasa a describir el problema. Además, cada rango se evalúa una sola vez.
  • SI.CONJUNTO sustituye a los SI anidados. En lugar de meter una condición dentro de otra, escribes la lista de pares condición-resultado en el mismo nivel. Se lee como una tabla de decisiones, y añadir un tercer tipo de línea en el futuro es cuestión de una línea más.

Una precaución que conviene recordar con SI.CONJUNTO: evalúa las condiciones en orden y se queda con la primera que se cumple. Aquí eso es exactamente lo que queremos, porque una fila de reparto no debe pasar nunca por la comparación contra cabeceras. Si algún día cambias el orden de los pares, cambias el resultado.

El detalle del SI.ND

SI.CONJUNTO devuelve un error cuando ninguna condición se cumple, y en esta tabla eso ocurre continuamente: en cada fila normal, todas las columnas menos una se quedan sin coincidencia. Envolver el conjunto en SI.ND(...; 0) convierte esos huecos en ceros y deja la matriz lista para sumar por columnas.

Funciones clave

  • LET: nombra rangos y cálculos intermedios; hace legible una fórmula matricial larga y evita recalcular lo mismo varias veces.
  • SI.CONJUNTO: encadena pares de condición y resultado sin anidar; devuelve el primero que se cumple.
  • SI.ND: sustituye los errores por el valor que le indiques, aquí un cero.
  • SUMA: totaliza los pesos para calcular la proporción.

Conclusión

La lección de fondo no es qué función usar, sino reconocer que hay dos reglas distintas conviviendo en la misma tabla. En cuanto lo separas —una condición para las líneas normales y otra para las de reparto— la fórmula sale sola, y con LET y SI.CONJUNTO queda además en un formato que cualquiera puede revisar meses después.

Como todos los casos que publicamos, este salió de un problema real planteado en la comunidad de InflueXcel y de dos miembros afinando la misma solución desde ángulos distintos.

Más casos con estas funciones

Más contenido de Excel en InflueXcel