Usos poco comunes de la función ELEGIR

Si las funciones tuvieran sentimientos ELEGIR seguramente se sentiría subestimada; y con bastante razón.
La descripción de la función en la ayuda en línea de Excel reza literalmente:

Utiliza el argumento núm_índice para devolver un valor de una lista de argumentos de valores.

La cuestión se vuelve más interesante cuando seguimos leyendo y llegamos a las observaciones:

• Si núm_índice es una matriz, cada valor se evaluará cuando se evalúe ELEGIR. 

• Los argumentos de valor para ELEGIR pueden ser referencias de rango, así como valores individuales. 

Interesante! ELEGIR puede usar matrices como número de índice y las referencias pueden ser valores individuales y rangos.

Estas carcterísticas nos abren muchas más posibilidades de lo que podemos deducir de la escueta descripción en la ayuda.

Veamos algunos ejemplos:

Operaciones dinámicas con rangos.

Consideremos esta tabla de datos

Hemos definido los nombres "Plan", "Ventas" y "Diferencia" que se refieren a los respectivos rangos en la tabla. La fórmula

=SUMA(ELEGIR(1,Plan,Ventas,Diferencia))

da el total del plan de ventas de todos los productos. De la misma manera

=SUMA(ELEGIR(2,Plan,Ventas,Diferencia))

Para que la fórmula sea más dinámica podemos combinarla con la función COINCIDIR y usar vallidación de datos para crear una lista desplegable con las distintas opciones

=SUMA(ELEGIR(COINCIDIR(G2,B2:D2,0),Plan,Ventas,Diferencia))

Si bien podemos crear el mismo modelo usando INDICE, la ventaja de ELEGIR es que podemos usar rangos que este en distintas hojas.

BUSCARV de derecha a izquierda.

Uno de los usos de ELEGIR con matrices es hacer que BUSCARV (VLOOKUP) pueda trabajar de derecha a izquierda. Por ejemplo, en la tabla anterior si queremos encontrar a qué producto pertenece el importe 13,048 usamos

=BUSCARV(13048,ELEGIR({2,1},A3:A7,B3:B7),2,0)

Para entender como funciona esta fórmula podemos usar el evaluador de fórmulas (Fórmulas-Auditoría-Evaluar Fórmulas)

Podemos ver que la expresión ELEGIR({2,1},A3:A7,B3:B7) crea una matriz de dos columnas donde la primera es el importe de las ventas y la segunda los nombres de los productos.

En mi opinión el uso más importante de ELEGIR es reemplazar el sobrestimado uso de SI anidado. Como ya hemos señalado en una nota de este blog anidar funciones SI es una de las mejores maneras de cometer errores en una hoja de cálculo. Después de tres o cuatro niveles, la fórmula se vuelve practicamente incontrolable (y me disculparán todos esos cursos que insisten en enseñar a construir fórmulas SI anidadas como si fuera una gran proeza).

Para ejemplificarlo supongamos que queremos determinar el responsable de producción según el día de la semana. La fórmula  para seis días de la semana, donde en la celda B3 tenemos la fecha a considerarcon sería

con Si anidado

=SI(DIA(B3)=1,"Roberto",SI(DIA(B3)=2,"Juan",SI(DIA(B3)=3,"Carlos",SI(DIA(B3)=4,"Esteban",SI(DIA(B3)=5,"Raul","Jose")))))

Con ELEGIR

=ELEGIR(DIA(B3),"Roberto","Juan","Carlos","Esteban","Raul","Jose")

Como diría Gracián, "lo bueno si breve, dos veces bueno".

Que tengan un buen fin de semana

Fechas de vencimiento en un día determinado

En la edad de piedra de este blog, hace varios años atrás, tratamos el tema del cálculo de fecha de pago.
En esa nota mostramos dos casos:

# - la fecha de vencimiento sucede 30 días a partir de la fecha de la factura, cuando la intención es el mismo día un mes más tarde. En este caso, suponiendo que la fecha de la factura está en la celda B3, =B3+30 sino =FECHA.MES(B3,1)

Si usáramos =B3+30 el resultado sería 24/11/2014

# - la fecha de vencimiento debe caer en un día determinado del mes, por ejemplo el 15. Para este caso usamos la fórmula 

=SI(DIA(B3)<=15,FIN.MES(B3,0)+15,FIN.MES(B3,1)+15)

(en la nota original usé una fórmula innecesariamente más complicada)

Un lector me consulta cómo calcular la fecha de pago de manera que caiga en un día determinado de la semana. Por ejemplo, una empresa que decide pagar 30 días después de la fecha de la factura pero sólo los días lunes. Consideremos esta tabla

La columna C muestra la fecha de vencimiento 30 días a partir de la fecha de la factura (el mismo día, un mes más tarde). En la celda C3 vemos que el vencimiento cae el 2/11/2014 que es un domingo; para corregir la fecha al lunes más cercano en la celda D3 ponemos la fórmula

=FECHA.MES(B3,1)+(2-DIASEM(FECHA.MES(B3,1)))

Al copiar la fórmula al resto de las celdas vemos que los vencimiento ocurren siempre los lunes (3/11/14, 10/11/14, etc.)

Esta fórmula funciona así:

=FECHA.MES(B3,1) da la fecha que aparece en la columna C (30 días de la fecha de la factura);

(2-DIASEM(FECHA.MES(B3,1))) es el "factor de correción" donde DIASEM(FECHA.MES(B3,1)) da el número de orden del día del vencimiento antes de la correción (el que aparece en la columna C), en nuestro caso 1 (domingo) y el resultado de la expresión será 2-1=1.

Algunos de mis agudos lectores ya habrán descubierto un problema potencial en este cálculo. Si observamos la factura en la fila 9 de la tabla, vemos que los 30 días caen el 8/11/2014 pero al corregir la fecha con la fórmula el pago ocurrirá 5 días antes, el 3/11/2014.

Si queremos que el pago ocurra siempre el mismo día o después de los 30 días debemos modificar nuestra fórmula a 

=FECHA.MES(B3,1)+SI(DIASEM(FECHA.MES(B3,1))<=2,(2-DIASEM(FECHA.MES(B3,1))),7+(2-DIASEM(FECHA.MES(B3,1))))

Una aclaración importante: la función DIASEM usa el argumento Tipo para determinar cuál es el primer día de la semana.

En mi ejemplo el argumento Tipo está en blanco significando que el día 1 es el domingo. Si queremos el número de orden 1 sea el lunes, el valor de Tipo debe ser 2.

Calcular días por períodos

Supongamos que queremos calcular el interés a cobrar por una deuda (por ejemplo, un pago atrasado). Durante el lapso transcurrido hay períodos con distintas tasas de interés.
El problema consiste en calcular cuantos días del lapso de la deuda caen en cada período de interés.
Consideremos esta ejemplo (el cuaderno es interactivo y puede descargarse)





El lapso de la deuda corre del 15/02/2014 al 27/06/2014, 133 días. Durante este lapso hay tres períodos de interes, tal como aparece en el rango B6:E8.

Nuestra tarea es asignar los 133 días a los distintos períodos de interés, tal como aparece en el rango F6:F8. La celda F6 contiene la fórmula

=SUMAPRODUCTO((((C6-1)+FILA(INDIRECTO("1:"&(D6-C6+1))))>=$C$3)*(((C6-1)+FILA(INDIRECTO("1:"&(D6-C6+1))))<=$D$3))

En esta fórmula usamos la técnica que ya mostré en la nota "Calcular días por años entre dos fechas". El funcionamiento es el siguiente:

• la expresión (C6-1)+FILA(INDIRECTO("1:"&(D6-C6+1)) al estar dentro de la función SUMAPRODUCTO crea un vector de fechas , en nuestro caso {01/01/2014,02/01/2014,03/01/2014...,31/03/2014}

• al comparar los miembros de este vector con la expresión >=$C$3 creamos un vector con los valores VERDADERO o FALSO (según se cumpla la condición o no)

• la segunda expresión ((C6-1)+FILA(INDIRECTO("1:"&(D6-C6+1))))<=$D$3) funciona de la misma manera para la fecha del fin del lapso

• SUMAPRODUCTO multipllica ambos vectores resultando 1 cuando se multiplican dos valores VERDADERO y 0 para los restantes casos. La suma interna del vector da como resultado el número de días comprendido dentro del período de interés.