Multiplicar por coeficientes de escenario con INDICE, COINCIDIRX y SI.CONJUNTO

Un miembro de la comunidad tiene una tabla de conceptos con cantidades, precios y un campo "Escenario" (1, 2 o 3). Aparte, tiene una tabla de coeficientes donde cada fila es un concepto y cada columna un escenario. Necesita multiplicar cantidad × precio × coeficiente correspondiente al escenario indicado, buscando el concepto correcto en la tabla de coeficientes. Llevaba días dándole vueltas sin conseguir cerrar la fórmula.

Miki propone una solución con LET, SI.CONJUNTO y ELEGIRFILAS:

``
=LET(
a;F6:H6; b;F10:H10; c;F14:H14; d;F18:H18;
i;C8:C15; concepto;A8:A15; total;D8:D15; escenario;E8:E15;
APILARH(A8:D15;escenario;
itotalSI.CONJUNTO(
escenario="Escenario 1";
ELEGIRCOLS(ELEGIRFILAS(F6:H18;COINCIDIRX(A8:A15;F4:F16));1);
escenario="Escenario 2";
ELEGIRCOLS(ELEGIRFILAS(F6:H18;COINCIDIRX(A8:A15;F4:F16));2);
escenario="Escenario 3";
ELEGIRCOLS(ELEGIRFILAS(F6:H18;COINCIDIRX(A8:A15;F4:F16));3)
)
)
)
`

Usa ELEGIRFILAS para seleccionar las filas correctas de la tabla de coeficientes según el concepto, ELEGIRCOLS para elegir la columna del escenario, y SI.CONJUNTO para ramificar según el escenario.

John aporta la solución más elegante con INDICE + COINCIDIRX + DERECHA:

`
=APILARH(A8:E15;
C8:C15 D8:D15
INDICE(F6:H18; COINCIDIRX(A8:A15;F4:F16); DERECHA(E8:E15))
)
`

Brillante en una sola línea: COINCIDIRX encuentra la fila del concepto, DERECHA(E8:E15) extrae el número del escenario (el "1" de "Escenario 1") y lo usa directamente como índice de columna en INDICE. Elimina completamente la necesidad de SI.CONJUNTO.

En un hilo posterior, el usuario intentó usar ELEGIRFILAS para otro paso y descubrió que es una función "todo o nada": si un solo elemento del índice es inválido (vacío, 0 o error), la función entera devuelve error. Nacho propuso envolver en MAP + SI.ERROR:

`
=MAP(G3#;LAMBDA(x;SI.ERROR(ELEGIRFILAS(D12:D13;x);"")))
`

John también señaló que BUSCARX` es más adecuada en estos casos porque no tiene esa limitación.

El problema: multiplicar por el coeficiente del escenario correcto

Un miembro tenía una tabla de conceptos con cantidad, precio y un campo Escenario (1, 2 o 3). Por otro lado, una tabla de coeficientes: una fila por concepto, una columna por escenario. El objetivo: para cada línea, calcular cantidad x precio x coeficiente, donde el coeficiente es el que cruza el concepto de esa fila con el escenario indicado. Llevaba días sin cerrar la fórmula, y no es de extrañar: hay dos búsquedas encadenadas (fila por concepto, columna por escenario).

Primer enfoque: LET + SI.CONJUNTO + ELEGIRFILAS

Miki lo resolvió ramificando por escenario con SI.CONJUNTO, seleccionando la fila del concepto con ELEGIRFILAS (vía COINCIDIRX) y la columna con ELEGIRCOLS:

=LET(
    i;C8:C15; total;D8:D15; escenario;E8:E15;
    APILARH(A8:D15;escenario;
        i*total*SI.CONJUNTO(
            escenario="Escenario 1";
                ELEGIRCOLS(ELEGIRFILAS(F6:H18;COINCIDIRX(A8:A15;F4:F16));1);
            escenario="Escenario 2";
                ELEGIRCOLS(ELEGIRFILAS(F6:H18;COINCIDIRX(A8:A15;F4:F16));2);
            escenario="Escenario 3";
                ELEGIRCOLS(ELEGIRFILAS(F6:H18;COINCIDIRX(A8:A15;F4:F16));3)
        )
    )
)

Funciona, pero repite tres veces casi la misma expresión, una por escenario. Si mañana aparece un cuarto escenario, hay que tocar la fórmula.

El enfoque elegante: INDICE + COINCIDIRX + DERECHA

John dio con la solución que elimina el SI.CONJUNTO por completo:

=APILARH(A8:E15;
    C8:C15 * D8:D15 *
    INDICE(F6:H18; COINCIDIRX(A8:A15;F4:F16); DERECHA(E8:E15))
)

La idea es preciosa. COINCIDIRX(A8:A15;F4:F16) encuentra la fila del concepto dentro de la tabla de coeficientes. Y DERECHA(E8:E15) extrae el número del texto "Escenario 1" —el "1"— y lo usa directamente como índice de columna en INDICE. Como el escenario ya lleva su número dentro, no hace falta ningún SI que traduzca: el propio dato es el índice. Una sola línea, sin ramas y sin mantenimiento.

La trampa de ELEGIRFILAS: todo o nada

En un hilo posterior el usuario intentó usar ELEGIRFILAS en otro paso y se topó con una limitación importante: es una función "todo o nada". Si un solo elemento del índice es inválido —vacío, cero o error—, la función entera devuelve error, no solo esa fila.

Nacho propuso envolver cada búsqueda en MAP + SI.ERROR para aislar los fallos fila a fila:

=MAP(G3#;LAMBDA(x;SI.ERROR(ELEGIRFILAS(D12:D13;x);"")))

Así, un índice problemático devuelve una celda vacía en lugar de tumbar todo el resultado. John apuntó además que BUSCARX suele ser mejor opción en estos casos, precisamente porque no arrastra esa fragilidad.

Funciones clave

  • INDICE + COINCIDIRX: la pareja para localizar un valor por fila y columna en una matriz.
  • DERECHA: extrae el número del texto del escenario para usarlo como índice.
  • SI.CONJUNTO: ramifica por condiciones; potente, pero verboso frente a la solución con INDICE.
  • ELEGIRFILAS: selecciona filas por índice, con la pega de fallar en bloque ante un índice inválido.
  • MAP + SI.ERROR: patrón para tolerar errores elemento a elemento.

Conclusión

El caso deja una moraleja muy de Excel moderno: cuando un dato ya contiene la información que necesitas (el número dentro de "Escenario 1"), aprovéchalo como índice en vez de montar una cadena de SI. La fórmula queda más corta, más rápida y sin mantenimiento. Y ojo con ELEGIRFILAS: su comportamiento "todo o nada" es una fuente de errores silenciosos que MAP + SI.ERROR domestica. Otro problema real resuelto entre varios en la comunidad de InflueXcel.

Más casos con estas funciones

Más contenido de Excel en InflueXcel