La función SI en Excel es sin duda una de las más (e incluso podríamos decir: la más) usada en Excel, por lo que ahora vamos a ver 10 trucos avanzados para poder sacarle mayor provecho.
Entre las cosas que vamos a aprender hoy tenemos:
- Condicion excluyente
- Revisar dos de tres condiciones
- Verificación de entre condiciones con MEDIANA
- Reemplazar funciones SI anidadas con funciones más cortas
- Usar lógica booleana para reemplazar funciones SI
- Revisar si un valor esta en una lista
- Arrays con la función SI
- Comprobaciones con comodines
- Cómo usar la función SI en lugares como formato condicional
- Crear gráficos dinámicos con la función si
Todos los ejemplos que vamos a ver utilizan datos de muestra en la tabla que puedes ver y descargar aquí.
1. Condición Excluyente
Se nos pide identificar empleados que cumplen ‘solo una’ de las siguientes condiciones: género = masculino o tengan un salario por debajo de $25,000. La fórmula a emplear sería la siguiente:
=IF(XOR([@Genero]="Masculino",[@Salario]<25000),"Si es","No es")
En castellano:
=SI(XO([@Genero]="Masculino",[@Salario]<25000),"Si es","No es")
Lo que sucede es que la función XO regresará VERDADERO si un número par de entrada es VERDADERO, de no serlo nos regresará FALSO. Por lo tanto, nuestro XO([@Genero]=»Masculino»,[@Salario]<25000),»Si es»,»No es») será útil para revisar solo uno de condición. Hay que tener en cuenta que XO no funciona cuando queremos revisar ‘solo uno de’ cuando tenemos más de dos condiciones, para lo cual tenemos el siguiente truco.
2. REVISAR DOS DE TRES CONDICIONES
Se nos pide marcar a los empleados que hayan cumplido con dos de las siguientes tres condiciones: se encuentran en el departamento de Produccion, su año de ingreso fue en 2014, su salario es mayor a $40,000. La fórmula a usar será:
=IF(([@Departamento]="Produccion")+(YEAR([@[Fecha_ingreso]])=2014)+([@Salario]>40000)>=2,"Si es","No es")
O lo que es lo mismo:
=SI(([@Departamento]="Produccion")+(AÑO([@[Fecha_ingreso]])=2014)+([@Salario]>40000)>=2,"Si es","No es")
El truco para entender la fórmula se encuentra en entender que Excel trata el valor VERDAERO como 1 y FALSO como 0. Por lo tanto, la expresión ([@Departamento]=»Produccion»)+(AÑO([@[Fecha_ingreso]])=2014)+([@Salario]>40000) será convertida a un montón de 1s y 0s que se sumarán de acuerdo a los detalles del empleado. Podemos entonces hacer una revisión rápida para saber si ese número es igual o mayor a 2 para poder comprobar que dos de las tres condiciones se han cumplido.
3. Usando MEDIANA para valores entre
Se nos pide identificar a los empleados que comenzaron a trabajar entre 1-Enero-2014 y 30-Junio-2015. Nuestra fórmula será la siguiente:
=IF(MEDIAN([@[Fecha_ingreso]],DATE(2014,1,1),DATE(2015,6,30))=[@[Fecha_ingreso]],"Si es","no es")
Para decirlo en nuestra lengua
=SI(MEDIANA([@[Fecha_ingreso]],FECHA(2014,1,1),FECHA(2015,6,30))=[@[Fecha_ingreso]],"Si es","no es")
Por lo general usamos la función Y() para revisar por entre condición, pero también podemos usar MEDIANA. El patrón que se sigue es algo así:
=MEDIANA(tu valor, mayor, menor) = tu valor
Este valor será VERDADERO si nuestro valor es entre los valores mayor y menor. Por ejemplo:
=MEDIANA(7,3,9)=7
nos arrojará VERDADERO.
4. REEMPLAZANDO FUNCIONES SI ANIDADAS
Se nos pide calcular el bono del personal en base a las siguientes reglas:
1. 1% por personal de Ventas
2. 3% por personal de Almacen que se unió en 2011
3. 2% por otros
La fórmula a emplear será la siguiente:
=IFS([@Departamento]="Ventas",1%,AND([@Departamento]="Almacen",YEAR([@[Fecha_ingreso]])=2011),3%,TRUE,2%)
Que traducida se lee:
=SI.CONJUNTO([@Departamento]="Ventas",1%,Y([@Departamento]="Almacen",AÑO([@[Fecha_ingreso]])=2011),3%,VERDADERO,2%)
Las funciones SI anidadas pueden ser difíciles de escribir y aun más complicadas de mantener, por lo cual se recomienda optar por la nueva función SI.CONJUNTO() en su lugar. La síntaxis para SI.CONJUNTO sigue el siguiente patrón:
=SI.CONJUNTO(condición1, valor1, condición2, valor2...)
Pero entonces, «¿eso significa que SI.CONJUNTO no tiene opción SI_FALSO?» Lo que podemos hacer es usar TRUE como la última condición para arreglar esto. En la fórmula TRUE de arriba, la parte con el 2% maneja el caso SI_FALSO de maravilla.
5. LÓGICA BOOLEANA PARA EVITAR FUNCIONES SI
Se nos pide calcular el bono del personal en base a las mismas reglas, pero ahora no podemos usar ninguna función SI:
1. 1% por personal de Ventas
2. 3% por personal de Almacen que se unió en 2011
3. 2% por otros
La fórmula que vamos a utilizar será la siguiente:
=2%-([@Departamento]="Ventas")*1%+AND([@Departamento]="Almacen",YEAR([@[Fecha_ingreso]])=2011)*1%
Podemos usar comprobaciones de lógica booleana para evitar usar funciones SI por completo. Esto nos funciona cuando los valores de salida son números. La fórmula de arriba calcula el bono de personal usando la noción VERDADERO=1 y FALSO=0. Haciendo la prueba con el siguiente personal:
Para Maria:
2% – (FALSO)*1% + (VERDADERO)* 1% = 3%
Para Pilar:
2% – (FALSO)*1% + (FALSO)*1% = 2%
6. REVISAR SI UN VALOR SE ENCUENTRA EN OTRA LISTA
Se nos pide revisar si un empleado es parte de un equipo de brigada en la tabla «Table2». La fórmula a utilizar es la siguiente:
=IF(COUNTIFS(Table2[Brigada],[@Nombre])>0,"si es","no es")
=SI(CONTAR.SI.CONJUNTO(Table2[Brigada],[@Nombre])>0,"si es","no es")
Podemos usar las funciones CONTAR.SI.CONJUNTO o COINCIDIR para cumplir la misma función, pero en este caso prefiero usar CONTAR.SI.CONJUNTO ya que solo contamos si cierto punto en los datos se encuentra en otra lista. «¿Y por qué no revisamos >0?» Recordemos que Excel trata cualquier número que no sea 0 como VERDADERO, por lo cual no hace falta escribir CONTAR.SI.CONJUNTO($C$32:$C$36,C8)>0.
7. ARRAYS CON LA FUNCIÓN SI
Se nos pide calcular la mediana de salario del personal de Producción. Nuestra fórmula será:
=MEDIAN(IF(Table1[Departamento]="Produccion",Table1[Salario]))
Cuando usamos arrays en la funciones SI se nos regresará también un array de salidas. Por ejemplo:
=SI({VERDADERO,VERDADERO,FALSO},{1, 2, 3},{“A”,”B”,”C”})nos regresará {1, 2, “C”}.
Podemos aprovechar esta misma idea para calcular la mediana de salario del personal. «¿Y qué hay de la parte ELSE? ¿No hace falta?» Si no mencionamos la parte SI_FALSO de la función SI simplemente nos regresará FALSO para esos valores.
Por lo que, en nuestro caso, tenemos:
45000,FALSO,FALSO,FALSO,45000,45000...8000
Cuando MEDIANA lee estos valores, ignorará los valores FALSO y calculará MEDIANA para el resto.
Ahora veamos otra situación. Se nos pide mostrar todos los nombres del personal de «Administracion» en una celda, separados por coma. La fórmula a emplear será:
=TEXTJOIN(",",TRUE,IF(Table1[Departamento]="Administracion",Table1[Nombre],""))Esto funciona igual que la estructura anterior.
8. CONDICIONES BASADAS EN COMODINES
Se nos pide identificar el nombre de un empleado que contenga las letras «DI». Esta es nuestra fórmula:
=IF(COUNTIFS([@Nombre],"*DI*"),"entra","no entra")
=SI(CONTAR.SI.CONJUNTO([@Nombre],"*DI*"),"entra","no entra")
La función SI no reconoce los comodines, pero podemos hacer que uno de los comodines reconozca las funciones dentro de SI para resolver este problema. Para ello podemos usar BUSCARX, COINCIDIRX, COINCIDIR, BUSCARV o CONTAR.SI.CONJUNTO. En este caso vamos a usar CONTAR.SI.CONJUNTO.
La función «CONTAR.SI.CONJUNTO([@Nombre], “*DI*”)» será 1 si el nombre en la columna Nombre contiene «DI», de otro modo será 0.
9. FUNCIÓN SI CON FORMATO CONDICIONAL
Se nos pide resaltar a los empleados que cumplan las condiciones especificadas en las siguientes celdas:
Nuestra fórmula a usar será:
=AND($C2=PARAMETROS!$D$2,$B2=PARAMETROS!$E$2)
Cuando revisamos reglas en formato condicional no necesitamos la función SI. En su lugar, usamos solo la parte de condición de la fórmula. Este sería el resultado de nuestra regla:
10. USANDO SI CON GRÁFICOS
Se nos pide crear un gráfico con los salarios de los empleados, pero también que resaltemos al personal que esté ganando por encima del salario promedio con un color diferente. Antes de pasar a la fórmula debemos saber los pasos que hay que realizar.
Primero vamos a añadir una columna extra en nuestros datos y usaremos la función SI para revisar si el salario de una persona está por encima del promedio. Luego vamos a crear un gráfico que contenga el salario original y la nueva columna. Seguido de eso vamos a sobreponer las barras (o columnas) en 100% y finalmente las vamos a colorear adecuadamente.
Ahora sí, la fórmula a usar será la siguiente:
=IF([@Salario]>AVERAGE([Salario]),[@Salario],NA())
Y así es como se verá mi gráfico:
Mil y un trucos para usar la función SI en Excel y los valores booleanos.
Cualquier duda nos leemos abajo.
