Referencias Relativas, absolutas, mixtas y Función Si anidada en Excel
REFERENCIAS RELATIVAS
Una referencia relativa a una celda o rango es aquella que, al copiar la celda donde está escrita y pegarla en otra ubicación o al utilizar "Autorrellenar", se ajusta automáticamente para hacer referencia a otras celdas.
Son las referencias más utilizadas y las que, en la mayoría de las veces, se obtienen por defecto en Excel al hacer click en una celda para hacer referencia a ella. Son del tipo A1.
Ejemplo 1:
Si tenemos la expresión
= A1*2
En la celda B1 y utilizamos "Autorrellenar" hacia abajo, las fórmulas que se obtienen van ajustando el número de forma que en la celda B2 tendremos la fórmula
=A2*2
En la celda B3 tendremos la fórmula
=A3*2 y así sucesivamente.
Podemos ver en la siguiente imagen como se ajustan las fórmulas a medida que "arrastramos" con "Autorrellenar":
El resultado sería:
Ejemplo 2:
Tengo una fórmula que suma el total de las ventas para el mes de Enero:
Nuestra fórmula tiene una referencia relativa al rango B2:B6 y al momento de copiarla hacia la derecha estaremos aumentando la columna por lo que la formula copiada deberá aumentar también su columna. La siguiente imagen muestra el resultado de copiar la fórmula de la celda B7 a la derecha:
REFERENCIA ABSOLUTA
Una referencia absoluta a una celda o rango es aquella que, al copiar la celda donde está escrita y pegarla en otra ubicación o al utilizar "Autorrellenar", no se ajusta y queda bloqueada haciendo referencia siempre a la misma celda.
Para hacer una referencia absoluta a una celda, se deben introducir los símbolos del dólar ($ ) antes de la letra y antes del número de una referencia normal del tipo A1.
Es decir, una referencia absoluta a la celda A1, sería: $A$1
Con ello, conseguimos que al copiar y pegar o bien al utilizar "Autorrellenar" siempre mantenemos la referncia a la misma celda:
Ejemplo 1:
Nuestro objetivo es obtener el precio en pesos basados en la columna de precios en dólares y el tipo de cambio que está indicado en la celda E2. El primer intento que haremos por resolver este problema es utilizando la siguiente fórmula:
=B2*E2
Ingresaré esta fórmula en la celda C2 y al copiarla hacia abajo tendremos el siguiente resultado:
La fórmula de la celda C2 devuelve el resultado correcto, pero las fórmulas de las filas inferiores devuelven cero. Al observar la fórmula de la celda C6 nos damos cuenta que, al copiar la fórmula hacia abajo, Excel modificó ambas referencias, inclusive la que hacía referencia a la celda E2 que contiene el tipo de cambio y por esta razón obtenemos el valor cero.
Si queremos que todas las fórmulas hagan referencia a la celda E2 sin importar que la copiemos a otra ubicación, entonces es necesario hacer que dicha referencia sea absoluta. Nuestra fórmula quedará de la siguiente manera:
=B2*$E$2
En esta fórmula, la primera referencia es relativa y la segunda es absoluta. En el momento en que ingresamos esta fórmula en la celda C2 y la copiamos hacia abajo, obtenemos el resultado correcto para cada uno de los productos.
Ejemplo 2:
Ejemplo 3:
Paso 1:
Selecciona la Celda donde deseas escribir la Fórmula (en este ejemplo, H2) y e scribe el signo de igual (=) para indicar a Excel que una Fórmula será definida.
Paso 2:
Haz clic en la primera Celda que deseas incluir en la Fórmula (F2, por ejemplo).
Paso 3:
Escribe un signo matemático (use el símbolo de multiplicación por ejemplo).
Paso 4:
Haz clic en la segunda Celda en la Fórmula (C2, por ejemplo) y añade el signo $ antes de C y un signo $ antes de 2 para crear una Referencia Absoluta.
Paso 5:
Copia la Fórmula en H3. La nueva Fórmula se deberá leer así =F3*
C
C2. La referencia del F2 cambió a F3 ya que esta es una Referencia Relativa, pero C2 permanece constante ya que has creado una Referencia Absoluta al insertar el signo de dinero.
Referencia absoluta.
Lo mejor de este tipo de fórmulas es que, podrás cambiar el valor del tipo de cambio y obtendrás los nuevos precios automáticamente sin la necesidad de modificar las fórmulas.
REFERENCIAS MIXTAS
Están formadas por una mezcla entre refencia absoluta y referencia relativa. Las referencias relativas se identifican con el título de la celda (letra de la columna y número de fila) mientras que en las referencias absolutas colocamos un signo de dólar ($) antes de la columna o fila cuyo valor se mantendrá invariable.
La referencia de columna absoluta identifica una columna absoluta y una fila relativa. Por ejemplo $A1, $B5, $D4
La referencia de fila absoluta es una combinación de columna relativa y fila absoluta. Por ejemplo A$2, B$7, C$3.
Asi, si se cambia la posición de la celda que contiene la fórmula, la referencia relativa se actualiza dependiendo de la nueva celda donde es copiada, pero la referencia absoluta (columna o fila que tiene el signo $) se mantiene igual.
Ejemplo 1:
En el cual creamos una operación para copiar el mismo valor de la celda A1.
Si creamos una fórmula con una columna absoluta y una fila relativa y la copiamos, la fórmula se actualiza con el valor de la nueva fila ($A1, $A2, $A3)
Si creamos una fórmula con una columna relativa y una fila absoluta y la copiamos, la fórmula se actualiza con el valor de la nueva columna (A$1, B$1, C$1)
Ejemplo 2:
En este primero tenemos el coste de comprar tornillos, tuercas y pinzas. Nuestro proveedor de material nos hace unos descuentos según la fecha de pago: un 8% si pagamos al contado, un 5% pagando a 30 días, etc.
Queremos saber el importe que tendríamos que pagar por tornillos, tuercas y pinzas según la forma de pago que realizásemos.
Primero, vamos a introducir la fórmula que nos va a servir de base para las posteriores. Para saber cuánto nos costarán los tornillos pagando al contado, la fórmula sería: el precio de los tornillos menos el descuento, que en este caso es un 8%. Pulsamos “intro” y vemos el coste. Vamos a trabajar con esta fórmula.
Lo que le vamos a decir a Excel es que cada vez que arrastre la fórmula hacia la derecha queremos que siga tomando el valor de los tornillos PERO que la política de descuentos sí se arrastre hacia la derecha. Por tanto, queremos que la columna de la celda que hace referencia a los tornillos quede bloqueada pero no la fila. Pinchamos en la fórmula sobre la celda que hace referencia al valor de los tornillos y pulsamos “f4” hasta que aparezca el signo de “$” delante de la columna.
Además, queremos que las celdas donde se encuentran los descuentos, si se desplacen hacia la derecha por las columnas pero que la fila no se desplace hacia abajo. Pinchamos en la fórmula sobre la celda que hace referencia al valor del descuento y pulsamos “f4” hasta que aparezca el signo de “$” delante de la fila.
Si lo hemos hecho correctamente al arrastrar la fórmula hacia la derecha y luego hacia abajo tendremos los precios finales con sus descuentos. Si seleccionamos cualquier celda, comprobamos que toma los datos correctamente.
Ejemplo 3:
vamos a realizar una tabla de multiplicar en la que estableciendo la fórmula en una celda nos va a servir para el resto. Nos situamos en la celda donde se realiza la operación 1 X 1 y escribimos la fórmula.
Si ya arrastro hacia la derecha, el problema que tengo es que, al ser una referencia relativa también se arrastra hacia la derecha la columna de donde toma los datos.
Por lo tanto, le tengo que decir a Excel que no queremos que la columna se arrastre.
Para conseguirlo, nos situamos en la primera fórmula y bloqueamos la columna pulsando “f4”.
Una vez hayamos bloqueado la columna, si arrastramos la fórmula hacia la derecha vemos que el dato de origen es correcto
Veamos qué pasa si arrastro ahora las fórmulas hacia abajo. Como podemos comprobar las fórmulas no son correctas ya que los datos de origen de la fila se han desplazado hacia abajo según arrastrábamos las fórmulas. Por tanto, lo que tenemos que decirle a Excel es que queremos bloquear la fila de origen. Para hacerlo volvemos a nuestra primera fórmula y en la barra de fórmulas pulsaremos f4 hasta bloquear la fila.
Una vez hecho eso ya podemos arrastrar la fórmula hacia la derecha y hacia abajo y comprobamos que las fórmulas han quedado perfectamente definidas
FUNCION SI ANIDADA EN EXCEL
La función SI es una de las funciones más utilizadas en Excel ya que nos ayuda a probar si una condición es verdadera o falsa. Si la condición es verdadera, la función realizará una acción determinada, pero si la condición es falsa entonces la función ejecutará una acción diferente.
Es así como la función SI nos permite evaluar el resultado de una prueba lógica y tomar una acción en base al resultado. La siguiente imagen ilustra el funcionamiento de la función SI.
El diagrama anterior nos deja en claro que la función SI evaluará como máximo una prueba lógica y podremos ejecutar hasta dos posibles acciones.
Ejemplo 1:
Supongamos que tenemos un listado de edades y en una nueva columna debemos colocar la leyenda “Menor de edad” en caso de que la edad de la persona sea menor de 18 años, pero si la edad es igual o mayor a 18, entonces debemos colocar la leyenda “Mayor de edad”.
La solución a nuestro problema será desplegar dos leyendas diferentes es decir, necesitamos ejecutar dos acciones diferentes en base a la prueba lógica de mayoría de edad. Este problema se resuelve fácilmente utilizando la función SI de la siguiente manera:
=SI(A2 < 18, "Menor de edad", "Mayor de edad")
La función SI mostrará el primer mensaje solamente si la prueba lógica (A2<18) es verdadera. Si dicha prueba lógica es falsa, entonces se mostrará el segundo mensaje. Observa el resultado de aplicar esta función en nuestros datos de ejemplo:
Ejemplo 2:
Desplegar la leyenda “Tercera edad” cuando la persona tenga 65 años o más. Esto nos deja el problema con las siguientes reglas:
Menor a 18 años: “Menor de edad”
Mayor a 18 años y menor de 65 años: “Mayor de edad”
Mayor o igual a 65 años: “Tercera edad”
Ahora tenemos tres posibles acciones, y la función SI no podrá resolver por sí sola este problema, por lo que necesitamos recurrir a la función SI anidada la cual nos permitirá resolver cualquier situación en las que necesitemos evaluar más de una prueba lógica y ejecutar más de dos acciones.
La siguiente imagen muestra el funcionamiento de la función SI anidada. Observa que la clave es que, en lugar de ejecutar una segunda acción, la primera función SI incluye una segunda función SI de manera que entre ambas funciones puedan ejecutar un máximo de tres acciones.
La función SI anidada en Excel aumenta la flexibilidad de la función al ampliar el número de posibles resultados a probar así como las acciones que podemos ejecutar. Para nuestro ejemplo, la función SI anidada que resolverá adecuadamente el problema será la siguiente:
=SI(A2 < 18, "Menor de edad", SI(A2 < 65, "Mayor de edad", "Tercera edad"))
Pon especial atención al tercer argumento de la primera función SI, que en lugar de ser una acción, se ha convertido en otra función SI que a su vez tendrá la posibilidad de ejecutar otras dos acciones.
Ejemplo 3:
En este caso la idea es obtener el porcentaje de comisión y el bono correspondiente para los vendedores que alcancen cierto nivel de ventas, esto es, tenemos una tabla donde se indican los rangos de ventas con sus respectivos porcentajes de comisión y bono.
En la imagen observamos los rangos de ventas con su respectivo porcentaje de comisión y bono, la función SI anidada deberá comparar el importe de ventas de cada vendedor con esa tabla y en su caso devolver el porcentaje y bono que le corresponda.
La formula para obtener la comisión.
=SI($C10>=$B$6,$D$6,SI($C10>=$B$5,$D$5,SI($C10>=$B$4,$D$4,$D$3)))*C10
Esa formula simplemente va comparando la cantidad mínima en la tabla de comisiones con el importe de ventas, si es mayor o igual devolverá el porcentaje que esta en esa fila donde coincide y lo multiplica por el importe de ventas.
Es importante tomar en cuenta que se debe comparar a partir del valor mas grande en la tabla de criterios de comisión, esto porque el operador de comparación es >= , si iniciáramos comparando con el importe de 0.00 de ventas en la tabla, la formula siempre daría 0.00 porque todos los importes de venta serán 0.00 o mas, así que siempre se cumplirá esa condición, lo cual no seria correcto para lo que estamos buscando, ademas eso coincide con el hecho de que las formulas se evalúan de izquierda a derecha y devuelve el valor cuando la primera condición se cumple, al iniciar a comprara el mayor valor se elimina el problema de evaluar el cero en la tabla y se deja para el final.
La formula Para obtener el bono.
=SI($C10>=$B$6,$E$6,SI($C10>=$B$5,$E$5,SI($C10>=$B$4,$E$4,$E$3)))
Esta formula es prácticamente igual a la anterior, con la única diferencia que aquí no se multiplica por el importe de ventas.
El resultado de la función si anidada.
En la imagen se puede ver el resultado al aplicar la formula, la misma se aplica para el bono, solo que no se multiplica por el importe de ventas, solo devuelve el bono que le corresponde según la tabla.
Una referencia relativa a una celda o rango es aquella que, al copiar la celda donde está escrita y pegarla en otra ubicación o al utilizar "Autorrellenar", se ajusta automáticamente para hacer referencia a otras celdas.
Son las referencias más utilizadas y las que, en la mayoría de las veces, se obtienen por defecto en Excel al hacer click en una celda para hacer referencia a ella. Son del tipo A1.
Ejemplo 1:
Si tenemos la expresión
= A1*2
En la celda B1 y utilizamos "Autorrellenar" hacia abajo, las fórmulas que se obtienen van ajustando el número de forma que en la celda B2 tendremos la fórmula
=A2*2
En la celda B3 tendremos la fórmula
=A3*2 y así sucesivamente.
Podemos ver en la siguiente imagen como se ajustan las fórmulas a medida que "arrastramos" con "Autorrellenar":
El resultado sería:
Ejemplo 2:
Tengo una fórmula que suma el total de las ventas para el mes de Enero:
Nuestra fórmula tiene una referencia relativa al rango B2:B6 y al momento de copiarla hacia la derecha estaremos aumentando la columna por lo que la formula copiada deberá aumentar también su columna. La siguiente imagen muestra el resultado de copiar la fórmula de la celda B7 a la derecha:
Al copiar la fórmula a una columna diferente, la referencia relativa modifica su columna. Por esta razón, cada celda de la fila 7 sumará el rango superior de su misma columna.
Ejemplo 3:
En la celda G5 se ha incluido la fórmula
=E5*F5+E3
y luego se ha rellenado hasta la celda G9. Observa las fórmulas pegadas en las celdas G6 a G9 y comprenderás la razón por la que hay un error en la celda G6.
Las referencias relativas permiten a Excel modificar la columna y fila al momento de copiar la fórmula a otras celdas. El cambio será relativo a la cantidad de columnas o filas que se ha desplazado la fórmula.
Una referencia absoluta a una celda o rango es aquella que, al copiar la celda donde está escrita y pegarla en otra ubicación o al utilizar "Autorrellenar", no se ajusta y queda bloqueada haciendo referencia siempre a la misma celda.
Para hacer una referencia absoluta a una celda, se deben introducir los símbolos del dólar ($ ) antes de la letra y antes del número de una referencia normal del tipo A1.
Es decir, una referencia absoluta a la celda A1, sería: $A$1
Con ello, conseguimos que al copiar y pegar o bien al utilizar "Autorrellenar" siempre mantenemos la referncia a la misma celda:
Ejemplo 1:
Nuestro objetivo es obtener el precio en pesos basados en la columna de precios en dólares y el tipo de cambio que está indicado en la celda E2. El primer intento que haremos por resolver este problema es utilizando la siguiente fórmula:
=B2*E2
Ingresaré esta fórmula en la celda C2 y al copiarla hacia abajo tendremos el siguiente resultado:
La fórmula de la celda C2 devuelve el resultado correcto, pero las fórmulas de las filas inferiores devuelven cero. Al observar la fórmula de la celda C6 nos damos cuenta que, al copiar la fórmula hacia abajo, Excel modificó ambas referencias, inclusive la que hacía referencia a la celda E2 que contiene el tipo de cambio y por esta razón obtenemos el valor cero.
Si queremos que todas las fórmulas hagan referencia a la celda E2 sin importar que la copiemos a otra ubicación, entonces es necesario hacer que dicha referencia sea absoluta. Nuestra fórmula quedará de la siguiente manera:
=B2*$E$2
En esta fórmula, la primera referencia es relativa y la segunda es absoluta. En el momento en que ingresamos esta fórmula en la celda C2 y la copiamos hacia abajo, obtenemos el resultado correcto para cada uno de los productos.
Ejemplo 2:
En la celda G5 se ha incluido la fórmula
=E5*F5+$E$3
y luego se ha rellenado hasta la celda G9. Observa las fórmulas pegadas en las celdas G6 a G9.
Ejemplo 3:
Paso 1:
Selecciona la Celda donde deseas escribir la Fórmula (en este ejemplo, H2) y e scribe el signo de igual (=) para indicar a Excel que una Fórmula será definida.
Paso 2:
Haz clic en la primera Celda que deseas incluir en la Fórmula (F2, por ejemplo).
Paso 3:
Escribe un signo matemático (use el símbolo de multiplicación por ejemplo).
Paso 4:
Haz clic en la segunda Celda en la Fórmula (C2, por ejemplo) y añade el signo $ antes de C y un signo $ antes de 2 para crear una Referencia Absoluta.
Paso 5:
Copia la Fórmula en H3. La nueva Fórmula se deberá leer así =F3*
C
C2. La referencia del F2 cambió a F3 ya que esta es una Referencia Relativa, pero C2 permanece constante ya que has creado una Referencia Absoluta al insertar el signo de dinero.
Referencia absoluta.
Lo mejor de este tipo de fórmulas es que, podrás cambiar el valor del tipo de cambio y obtendrás los nuevos precios automáticamente sin la necesidad de modificar las fórmulas.
REFERENCIAS MIXTAS
Están formadas por una mezcla entre refencia absoluta y referencia relativa. Las referencias relativas se identifican con el título de la celda (letra de la columna y número de fila) mientras que en las referencias absolutas colocamos un signo de dólar ($) antes de la columna o fila cuyo valor se mantendrá invariable.
La referencia de columna absoluta identifica una columna absoluta y una fila relativa. Por ejemplo $A1, $B5, $D4
La referencia de fila absoluta es una combinación de columna relativa y fila absoluta. Por ejemplo A$2, B$7, C$3.
Asi, si se cambia la posición de la celda que contiene la fórmula, la referencia relativa se actualiza dependiendo de la nueva celda donde es copiada, pero la referencia absoluta (columna o fila que tiene el signo $) se mantiene igual.
Ejemplo 1:
En el cual creamos una operación para copiar el mismo valor de la celda A1.
Si creamos una fórmula con una columna absoluta y una fila relativa y la copiamos, la fórmula se actualiza con el valor de la nueva fila ($A1, $A2, $A3)
Si creamos una fórmula con una columna relativa y una fila absoluta y la copiamos, la fórmula se actualiza con el valor de la nueva columna (A$1, B$1, C$1)
Ejemplo 2:
En este primero tenemos el coste de comprar tornillos, tuercas y pinzas. Nuestro proveedor de material nos hace unos descuentos según la fecha de pago: un 8% si pagamos al contado, un 5% pagando a 30 días, etc.
Queremos saber el importe que tendríamos que pagar por tornillos, tuercas y pinzas según la forma de pago que realizásemos.
Primero, vamos a introducir la fórmula que nos va a servir de base para las posteriores. Para saber cuánto nos costarán los tornillos pagando al contado, la fórmula sería: el precio de los tornillos menos el descuento, que en este caso es un 8%. Pulsamos “intro” y vemos el coste. Vamos a trabajar con esta fórmula.
Lo que le vamos a decir a Excel es que cada vez que arrastre la fórmula hacia la derecha queremos que siga tomando el valor de los tornillos PERO que la política de descuentos sí se arrastre hacia la derecha. Por tanto, queremos que la columna de la celda que hace referencia a los tornillos quede bloqueada pero no la fila. Pinchamos en la fórmula sobre la celda que hace referencia al valor de los tornillos y pulsamos “f4” hasta que aparezca el signo de “$” delante de la columna.
Además, queremos que las celdas donde se encuentran los descuentos, si se desplacen hacia la derecha por las columnas pero que la fila no se desplace hacia abajo. Pinchamos en la fórmula sobre la celda que hace referencia al valor del descuento y pulsamos “f4” hasta que aparezca el signo de “$” delante de la fila.
Si lo hemos hecho correctamente al arrastrar la fórmula hacia la derecha y luego hacia abajo tendremos los precios finales con sus descuentos. Si seleccionamos cualquier celda, comprobamos que toma los datos correctamente.
Ejemplo 3:
vamos a realizar una tabla de multiplicar en la que estableciendo la fórmula en una celda nos va a servir para el resto. Nos situamos en la celda donde se realiza la operación 1 X 1 y escribimos la fórmula.
Si ya arrastro hacia la derecha, el problema que tengo es que, al ser una referencia relativa también se arrastra hacia la derecha la columna de donde toma los datos.
Por lo tanto, le tengo que decir a Excel que no queremos que la columna se arrastre.
Para conseguirlo, nos situamos en la primera fórmula y bloqueamos la columna pulsando “f4”.
Una vez hayamos bloqueado la columna, si arrastramos la fórmula hacia la derecha vemos que el dato de origen es correcto
Veamos qué pasa si arrastro ahora las fórmulas hacia abajo. Como podemos comprobar las fórmulas no son correctas ya que los datos de origen de la fila se han desplazado hacia abajo según arrastrábamos las fórmulas. Por tanto, lo que tenemos que decirle a Excel es que queremos bloquear la fila de origen. Para hacerlo volvemos a nuestra primera fórmula y en la barra de fórmulas pulsaremos f4 hasta bloquear la fila.
Una vez hecho eso ya podemos arrastrar la fórmula hacia la derecha y hacia abajo y comprobamos que las fórmulas han quedado perfectamente definidas
FUNCION SI ANIDADA EN EXCEL
La función SI es una de las funciones más utilizadas en Excel ya que nos ayuda a probar si una condición es verdadera o falsa. Si la condición es verdadera, la función realizará una acción determinada, pero si la condición es falsa entonces la función ejecutará una acción diferente.
Es así como la función SI nos permite evaluar el resultado de una prueba lógica y tomar una acción en base al resultado. La siguiente imagen ilustra el funcionamiento de la función SI.
El diagrama anterior nos deja en claro que la función SI evaluará como máximo una prueba lógica y podremos ejecutar hasta dos posibles acciones.
Ejemplo 1:
Supongamos que tenemos un listado de edades y en una nueva columna debemos colocar la leyenda “Menor de edad” en caso de que la edad de la persona sea menor de 18 años, pero si la edad es igual o mayor a 18, entonces debemos colocar la leyenda “Mayor de edad”.
La solución a nuestro problema será desplegar dos leyendas diferentes es decir, necesitamos ejecutar dos acciones diferentes en base a la prueba lógica de mayoría de edad. Este problema se resuelve fácilmente utilizando la función SI de la siguiente manera:
=SI(A2 < 18, "Menor de edad", "Mayor de edad")
La función SI mostrará el primer mensaje solamente si la prueba lógica (A2<18) es verdadera. Si dicha prueba lógica es falsa, entonces se mostrará el segundo mensaje. Observa el resultado de aplicar esta función en nuestros datos de ejemplo:
Ejemplo 2:
Desplegar la leyenda “Tercera edad” cuando la persona tenga 65 años o más. Esto nos deja el problema con las siguientes reglas:
Menor a 18 años: “Menor de edad”
Mayor a 18 años y menor de 65 años: “Mayor de edad”
Mayor o igual a 65 años: “Tercera edad”
Ahora tenemos tres posibles acciones, y la función SI no podrá resolver por sí sola este problema, por lo que necesitamos recurrir a la función SI anidada la cual nos permitirá resolver cualquier situación en las que necesitemos evaluar más de una prueba lógica y ejecutar más de dos acciones.
La siguiente imagen muestra el funcionamiento de la función SI anidada. Observa que la clave es que, en lugar de ejecutar una segunda acción, la primera función SI incluye una segunda función SI de manera que entre ambas funciones puedan ejecutar un máximo de tres acciones.
La función SI anidada en Excel aumenta la flexibilidad de la función al ampliar el número de posibles resultados a probar así como las acciones que podemos ejecutar. Para nuestro ejemplo, la función SI anidada que resolverá adecuadamente el problema será la siguiente:
=SI(A2 < 18, "Menor de edad", SI(A2 < 65, "Mayor de edad", "Tercera edad"))
Pon especial atención al tercer argumento de la primera función SI, que en lugar de ser una acción, se ha convertido en otra función SI que a su vez tendrá la posibilidad de ejecutar otras dos acciones.
Ejemplo 3:
En este caso la idea es obtener el porcentaje de comisión y el bono correspondiente para los vendedores que alcancen cierto nivel de ventas, esto es, tenemos una tabla donde se indican los rangos de ventas con sus respectivos porcentajes de comisión y bono.
En la imagen observamos los rangos de ventas con su respectivo porcentaje de comisión y bono, la función SI anidada deberá comparar el importe de ventas de cada vendedor con esa tabla y en su caso devolver el porcentaje y bono que le corresponda.
La formula para obtener la comisión.
=SI($C10>=$B$6,$D$6,SI($C10>=$B$5,$D$5,SI($C10>=$B$4,$D$4,$D$3)))*C10
Esa formula simplemente va comparando la cantidad mínima en la tabla de comisiones con el importe de ventas, si es mayor o igual devolverá el porcentaje que esta en esa fila donde coincide y lo multiplica por el importe de ventas.
Es importante tomar en cuenta que se debe comparar a partir del valor mas grande en la tabla de criterios de comisión, esto porque el operador de comparación es >= , si iniciáramos comparando con el importe de 0.00 de ventas en la tabla, la formula siempre daría 0.00 porque todos los importes de venta serán 0.00 o mas, así que siempre se cumplirá esa condición, lo cual no seria correcto para lo que estamos buscando, ademas eso coincide con el hecho de que las formulas se evalúan de izquierda a derecha y devuelve el valor cuando la primera condición se cumple, al iniciar a comprara el mayor valor se elimina el problema de evaluar el cero en la tabla y se deja para el final.
La formula Para obtener el bono.
=SI($C10>=$B$6,$E$6,SI($C10>=$B$5,$E$5,SI($C10>=$B$4,$E$4,$E$3)))
Esta formula es prácticamente igual a la anterior, con la única diferencia que aquí no se multiplica por el importe de ventas.
El resultado de la función si anidada.
En la imagen se puede ver el resultado al aplicar la formula, la misma se aplica para el bono, solo que no se multiplica por el importe de ventas, solo devuelve el bono que le corresponde según la tabla.
Comentarios
Publicar un comentario