Simplificando la función SI (IF) combinando expresiones lógicas.

Una de las funciones más usadas en Excel es la función SI. La ayuda on-line de Excel la define así: "devuelve un valor si la condición especificada es VERDADERO y otro valor si dicho argumento es FALSO."
La sintaxis de esta función es:

=SI(prueba_lógica;valor_si_verdadero;valor_si_falso)

Para crear pruebas más elaboradas Excel nos permite anidar hasta siete funciones SI como argumentos valor_si_verdadero y valor_si_falso.
En entradas anteriores ya había comentado que crear formulas con varias funciones SI anidadas, suele resultar en fórmulas "chorizo" difíciles de entender y conllevan una nada despreciable inversión de tiempo.

Algunas de las alternativas, dependiendo del caso, pueden ser usar la función ELEGIR (CHOOSE) en lugar de SI o utilizar la función BUSCARV en lugar de anidar varias funciones SI.

Otra alternativa, en especial cuando tenemos varias condiciones que deben cumplirse, es crear fórmulas con expresiones lógicas combinadas con operadores "+".

Qué es una expresión lógica? Funciones (expresiones) en Excel pueden dar resultados numéricos o ser resueltas como expresión lógica con el valor VERDADERO o el valor FALSO. Si escribimos la fórmula "=A1=B1", Excel dará como resultado o FALSO o VERDADERO.
Esta característica puede ser aprovechada para crear una alternativa a la función SI. Veremos esto con un ejemplo. Supongamos un club de compras, donde los clientes reciben descuentos sobre las compras mensuales basados en las siguientes reglas

Nuestra tarea es determinar el descuento que le corresponde a cada cliente dados estos datos:



Para determinar el descuento de cada cliente no utilizaremos funciones SI, sino expresiones lógicas. Dado que tenemos que tener en cuenta dos condiciones (las compras del mes corriente y las del anterior) usaremos la función Y (AND en su versión inglesa) para armar la expresión lógica.
La fórmulas que expresan las condiciones de los descuentos son:



Por ejemplo, si la expresión =Y(B5<5000,c5<5000) es verdadera, la fórmula

=Y(B5<5000,c5<5000)*0.05

da como resultado 5%. Si la expresión no se cumple, el resultado de la fórmula será 0.

Combinamos las fórmulas para cada uno de los casos en una única formula con el operador "+"

=Y(B5<5000,C5<5000)*0.05+Y(B5<5000,c5>=5000)*0.07+Y(B5>=5000,C5<5000)*0.10+Y(B5>=5000,C5>=5000)*0.12

y la copiamos en el rango D5:D14

Esta fórmula es más explicita y manejable que la equivalente usando funciones SI anidadas.


Categorías: Funciones&Formulas_

Technorati Tags: Funciones Excel

Calculando las semanas del año en Excel

Últimamente he recibido varias preguntas sobre temas relacionados con cálculos de fechas. Una de ellas era cómo calcular el número de semana de una determinada fecha.

A primera vista parece una pregunta sencilla. Si tenemos instalado el Análisis Toolpak, podemos utilizar la función NUM.DE.SEMANA(núm_de_serie;tipo), donde "num_de_serie" es el número de serie de la fecha y "tipo" es una variable que indica si la semana comienza el domingo (tipo = 1) o el lunes(tipo = 2).

Si NUM.DE.SEMANA da resultado #¿NOMBRE?, podemos usar su equivalente en inglés WEEKNUM.

Sencillo, no? Pues bien, no!. Sucede que hay que tener en cuenta varias cuestiones antes de lanzarnos a calcular el número de semana.

El resto de esta entrada está basado en los excelentes artículos de Chip Pearson y Ron de Bruin sobre el tema.

La primer semana comienza el 1 de enero; pero, cuando comienza la segunda, al 8 de enero o el primer lunes después del 1 de enero? Que día marca el comienzo de la semana, el lunes o el domingo?

Veamos las distintas formas de calcular el número de semana. Un archivo con las distintas fórmulas se puede bajar aquí.

Número de semana absoluto.

De acuerdo este concepto, la primer semana empieza siempre el 1 de enero y concluye el 7 de enero, sin tener en cuenta el día de la semana. De esta manera tendremos 53 semanas al año. La semana 53 tendrá un día o dos, si se trata de un año bisiesto.
Para calcular el número de semana absoluto usamos la fórmula
=TRUNCAR(((A1-FECHA(AÑO(A1),1,0))+6)/7)
Si en la celda A1 tenemos la fecha de hoy (28/06/2006), esta fórmula da el resultado 26.

Número de semana de Excel.

Excel ofrece la función WEEKNUM (o NUM.DE.SEMANA). Para usar esta fórmula debemos instalar previamente el Analysis Toolpak.
Esta función calcula el número de semana contando desde el domingo o el lunes. Por lo tanto, la primer semana puede tener entre 1 y 7 días.
La sintaxis de esta fórmula es
NUM.DE.SEMANA(núm_de_serie;tipo)
Núm_de_serie es una fecha dentro de la semana. Las fechas deben introducirse mediante la función FECHA o como resultados de otras fórmulas o funciones.
Tipo es un número que determina en qué día comienza la semana. El valor puede ser 1 (domingo) o 2 (lunes).
La función =WEEKNUM(A1,2), donde A1 contiene la fecha 28/06/2006 da el resulta 27. En cambio la función =WEEKNUM(A1,1) da 26.

Número de semana ISO (International Organization for Standardisation )

De acuerdo a la norma ISO, la semana empieza siempre un lunes y termina un domingo. La primer semana del año es la que contiene el primer jueves. Es decir, la primer semana tendrá siempre 4 días por lo menos.
La fórmula para calcular el número de semana de acuerdo al estándar ISO es

=ENTERO((A1-FECHA(AÑO(A1-DIASEM(A1-1)+4),1,3)+DIASEM(FECHA(AÑO(A1-DIASEM(A1-1)+4),1,3))+5)/7)

Si la celda A1 contiene la fecha 28/06/2006, el resultado será 26.

Finalmente, si queremos utilizar fórmulas en lugar de la función WEEKNUM,

Si la semana empieza en domingo:

=1+ENTERO((A1-(FECHA(AÑO(A1),1,2)-DIASEM(FECHA(AÑO(A1),1,1))))/7)

Si la semana empieza en lunes:

=1+ENTERO((A1-(FECHA(AÑO(A1),1,2)-DIASEM(FECHA(AÑO(A1),1,0))))/7)


Categorías: Funciones&Formulas

Technorati Tags: WEEKNUM

La función INDIRECTO con cuadernos Excel cerrados.

Con la función INDIRECTO de Excel, podemos establecer referencias dinámicas a un rango o una celda en un cuaderno remoto (referencia externa). En esta entrada sobre el uso de la función INDIRECTO, daba un ejemplo de cómo establecer referencias a celdas que se encuentran en distintas hojas de un mismo cuaderno Excel.
De la misma manera podemos establecer referencias a celdas en hojas de otros cuadernos (lo que he llamado cuadernos remotos). Como ya he explicado, la función INDIRECTO devuelve la referencia especificada por una cadena de texto. El problema con INDIRECTO es que si se hace referencia a un libro remoto (una referencia externa), ese libro debe estar abierto. Si el libro de origen no está abierto, INDIRECTO devolverá el valor de error #¡REF!

En esta nota sobre el tema en Daily Dose of Excel se señalan varias soluciones, de las que mencionaré sólo dos:

1 – El complemento desarrollado por Laurent Longre, MOREFUNC.XLL, que incluye la función INDIRECT.EXT que funciona como INDIRECTO, pero también con cuadernos cerrados. Esta función tiene varias limitaciones:
- permite establecer referencias sólo a una única celda, por lo que no sirve como segundo argumento en la función BUSCARV (VLOOKUP)
- no funciona con si se refiere a nombres en cuadernos cerrados


2 – La función PULL de Harlan Grove. Esta es una UDF (user defined function) cuyo código se puede obtener aquí. Este función permite crear referencias a rangos y por lo tanto, puede usarse con BUSCARV (VLOOKUP), por ejemplo =VLOOKUP("search_text",PULL("'C:temp[book1.xls]sheet!'A1:B20"),2,0)

Ambas funciones tienen en común el ser lentas.


Categorías: Funciones&Formulas
Technorati Tags: INDIRECTO