Distribuir importes por meses según periodicidad: de fórmula ingeniosa a solución flexible con tabla maestra

Juan vuelve al grupo con un caso que ya tenía resuelto gracias a una fórmula de John, pero que necesitaba evolucionar. La fórmula original distribuye importes a lo largo de 12 meses según su periodicidad (mensual, bimensual, trimestral, semestral) usando un truco ingenioso: detecta la primera letra del tipo (M, B, T, S) y con RESIDUO + HALLAR calcula en qué meses cae cada importe:

``
=LET(
f; B3:B5;
m; C2:N2;
x; SI.ERROR(MES(1&f););
A3:A5 SI(x; m=x; RESIDUO(m-1; HALLAR(IZQUIERDA(f); "MBT__S"))=0)
)
`

La fórmula funcionaba perfectamente para ciclos regulares, pero Juan necesitaba dos cosas nuevas: poder asignar importes solo a meses concretos a elegir (por ejemplo, solo enero y febrero) y hacer que la periodicidad "Anual" funcionase (la "A" no encajaba en el truco original con HALLAR).

John responde con una versión evolucionada de su propia fórmula, extendiendo la cadena de búsqueda a "MBT__S_____A" para colocar la "A" en la posición 12 (= anual, cada 12 meses):

`
=E15:E18 SI(
SI.ERROR(MES(1&F15:F18););
G14:R14 = SI.ERROR(MES(1&F15:F18););
RESIDUO(G14:R14-1; HALLAR(IZQUIERDA(F15:F18); "MBT__S_____A"))=0
)
`

Para el caso de "meses a elegir", John lo resuelve fuera de la fórmula principal, asignando manualmente los importes a los meses deseados.

Nacho propone un enfoque diferente: en vez de exprimir la fórmula para cubrir todos los casos, crea una tabla maestra de "faseado" donde defines libremente qué meses aplican a cada tipo de reparto (con 1 y 0). Luego una sola fórmula con REDUCE, LAMBDA, BUSCARX y APILARV recorre cada línea, busca su tipo de reparto en la tabla maestra y multiplica por el importe:

`
=LET(
_importes; E15:E20;
_periodos; F15:F20;
EXCLUIR(
REDUCE(
0;
_periodos;
LAMBDA(_A; _I;
APILARV(
_A;
BUSCARX(_I; Tabla1[Faseado]; Tabla1[[Ene]:[Dic]])
)
)
) * _importes;
1
)
)
``

La tabla maestra tiene esta pinta:

| Faseado | Ene | Feb | Mar | Abr | May | Jun | Jul | Ago | Sep | Oct | Nov | Dic |
|---------|-----|-----|-----|-----|-----|-----|-----|-----|-----|-----|-----|-----|
| Mensual | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
| Bimensual | | 1 | | 1 | | 1 | | 1 | | 1 | | 1 |
| Trimestral | | | 1 | | | 1 | | | 1 | | | 1 |
| Anual | | | | | | | | | | 1 | | |
| Enero y Febrero | 1 | 1 | | | | | | | | | | |
| Semestral | | | | | | 1 | | | | | | 1 |

La ventaja es total flexibilidad: puedes crear cualquier combinación de meses simplemente añadiendo una fila a la tabla maestra, sin tocar la fórmula. Dos enfoques complementarios para un mismo problema: John optimiza la fórmula original, Nacho externaliza la lógica a los datos.

De una fórmula ingeniosa a una tabla maestra flexible

Juan traía un caso ya resuelto por John, pero que necesitaba crecer. La fórmula original reparte importes a lo largo de doce meses según su periodicidad (mensual, bimensual, trimestral, semestral) con un truco muy elegante: mira la primera letra del tipo y, con RESIDUO y HALLAR, decide en qué meses cae cada importe.

=LET(
    f; B3:B5;
    m; C2:N2;
    x; SI.ERROR(MES(1&f););
    A3:A5 * SI(x; m=x; RESIDUO(m-1; HALLAR(IZQUIERDA(f); "MBT__S"))=0)
)

El truco está en la cadena "MBT__S": la posición de cada letra (M en 1, B en 2, T en 3, S en 6) coincide con cada cuántos meses se repite el importe. RESIDUO(mes-1; posición)=0 marca los meses correctos.

El problema: casos que el truco no cubría

Juan necesitaba dos cosas nuevas: asignar importes solo a meses sueltos elegidos a mano (por ejemplo, enero y febrero) y que la periodicidad Anual funcionara, porque la letra "A" no encajaba en la cadena original.

John evolucionó su propia fórmula extendiendo la cadena a "MBT__S_____A", colocando la "A" en la posición 12 (una vez cada doce meses):

=E15:E18 * SI(
    SI.ERROR(MES(1&F15:F18););
    G14:R14 = SI.ERROR(MES(1&F15:F18););
    RESIDUO(G14:R14-1; HALLAR(IZQUIERDA(F15:F18); "MBT__S_____A"))=0
)

Los meses sueltos los resolvía fuera de la fórmula, asignando los importes a mano.

La alternativa de Nacho: sacar la lógica a los datos

En vez de seguir exprimiendo la fórmula, Nacho propuso una tabla maestra de faseado donde defines con unos y ceros qué meses aplican a cada tipo de reparto. Una sola fórmula recorre cada línea, busca su tipo en la tabla maestra y multiplica por el importe:

=LET(
    _importes; E15:E20;
    _periodos; F15:F20;
    EXCLUIR(
        REDUCE(
            0;
            _periodos;
            LAMBDA(_A; _I;
                APILARV(_A; BUSCARX(_I; Tabla1[Faseado]; Tabla1[[Ene]:[Dic]]))
            )
        ) * _importes;
        1
    )
)

La tabla maestra tiene esta forma:

FaseadoEneFebMarAbrMayJunJulAgoSepOctNovDic
Mensual111111111111
Bimensual111111
Trimestral1111
Anual1
Enero y Febrero11
Semestral11

Añades una fila con cualquier combinación de meses y la fórmula la respeta sin tocar una coma.

Funciones clave

  • RESIDUO y HALLAR: el corazón del truco original, traducen la letra del tipo a una cadencia de meses.
  • REDUCE y LAMBDA: recorren fila a fila para construir el resultado acumulado.
  • BUSCARX: trae de la tabla maestra el patrón de meses de cada tipo de reparto.
  • APILARV y EXCLUIR: montan el bloque final y descartan la semilla inicial del REDUCE.

Conclusión

Dos filosofías para el mismo problema: John optimiza la fórmula hasta cubrir cada caso; Nacho externaliza la lógica a una tabla que cualquiera puede editar. Cuando las reglas cambian a menudo, mover la lógica a los datos suele ganar en mantenimiento. Ambos enfoques nacieron en la comunidad de InflueXcel en cuestión de minutos.

Más casos con estas funciones

Más contenido de Excel en InflueXcel