Repartir costes directos e indirectos con una sola fórmula matricial

Un miembro de la comunidad tiene una tabla de costes con distintos tipos de partida (SGA, Overhead, Reparto...) y necesita distribuirlos automáticamente en columnas según su categoría. El reto está en que los costes directos van a la columna que coincide con su encabezado, pero los costes de tipo "Reparto" deben distribuirse proporcionalmente entre todas las columnas según unos pesos definidos.

Leo propone una primera solución con SI anidado y SI.ND para manejar los errores:

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

La lógica es directa: si el tipo es "Reparto", multiplica el importe por el peso relativo de cada columna. Si no, compara con los encabezados y asigna el importe donde coincida. El SI.ND convierte los #N/D en ceros.

Después Leo simplifica con LET y SI.CONJUNTO:

`
=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
)
)
`

SI.CONJUNTO evalúa las condiciones en orden: primero comprueba si es "Reparto" y reparte proporcionalmente, luego si coincide con el encabezado asigna el importe directo. Mucho más legible que anidar SI.

Hugo aporta un enfoque alternativo: poner directamente los valores en porcentaje en la tabla de pesos, evitando la división por la suma en la fórmula.

John propone la versión más compacta:

`
=LET(
d; D25:D31;
w; F22:I22;
E25:E31 * SI(d="Reparto"; w / SUMA(w); d=F24:I24)
)
`

Aquí el SI devuelve el porcentaje si es reparto, o un array de VERDADERO/FALSO si es coste directo (que al multiplicar actúa como 1/0). Elegante y sin necesidad de SI.ND`.

El problema: costes directos y de reparto en la misma tabla

Un miembro de la comunidad tiene una tabla de costes con distintos tipos de partida (SGA, Overhead, Reparto...) y necesita distribuirlos automáticamente en columnas según su categoría. La dificultad tiene dos caras:

  • Los costes directos van a la columna cuyo encabezado coincide con su tipo.
  • Los costes de tipo "Reparto" deben repartirse proporcionalmente entre todas las columnas, según unos pesos definidos.

Todo en un mismo bloque, sin columnas auxiliares y derramando desde una sola celda.

Primer intento: SI anidado con SI.ND

Leo arranca con la versión directa, anidando condiciones y limpiando errores con SI.ND:

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

La lógica es transparente: si el tipo es "Reparto", multiplica el importe por el peso relativo de cada columna; si no, lo compara con los encabezados y coloca el importe donde coincida. El SI.ND convierte los errores de no coincidencia en ceros.

Segundo intento: LET + SI.CONJUNTO

Después Leo lo hace legible nombrando los rangos con LET y sustituyendo los SI anidados por SI.CONJUNTO:

=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
    )
)

SI.CONJUNTO evalúa en orden: primero mira si la fila es de reparto y aplica el prorrateo; si no, comprueba si coincide con el encabezado y asigna el importe directo. Mucho más limpio que anidar.

La versión más compacta: John

John aporta la solución más elegante, aprovechando que en Excel un array de booleanos multiplica como 1 y 0:

=LET(
    d; D25:D31;
    w; F22:I22;
    E25:E31 * SI(d="Reparto"; w / SUMA(w); d=F24:I24)
)

El SI interior devuelve el vector de porcentajes cuando la fila es de reparto, o un array de VERDADERO/FALSO cuando es coste directo. Al multiplicarlo por el importe, ese array actúa como una máscara de unos y ceros y coloca el valor en la columna correcta. Sin SI.ND y sin condiciones extra.

Un matiz de Hugo

Hugo propone algo práctico: poner directamente los pesos ya en porcentaje en la tabla de referencia, evitando la división por la suma dentro de la fórmula. Menos cálculo repetido y una fórmula todavía más corta. Es el clásico truco de mover parte de la lógica al diseño de los datos.

Funciones clave

  • SI.CONJUNTO: encadena condiciones en orden, ideal para "si es esto, aquello; si no, lo otro".
  • SI.ND: neutraliza los errores de no coincidencia convirtiéndolos en ceros.
  • SUMA: normaliza los pesos para el reparto proporcional.
  • Multiplicación por máscara booleana: la técnica de multiplicar un importe por un array de VERDADERO/FALSO para colocarlo donde corresponde.

Conclusión

Es un ejemplo perfecto de cómo un mismo cálculo pasa de "funciona pero cuesta leerlo" a "una línea que se entiende de un vistazo". La progresión —de SI anidado con SI.ND, a SI.CONJUNTO con LET, a la máscara booleana de John— enseña más que la fórmula final: enseña a simplificar. Y ese refinamiento colectivo, con tres o cuatro personas puliendo la misma idea, es lo que hace especial a la comunidad de Influexcel.

Más casos con estas funciones

Más contenido de Excel en InflueXcel