Actividad Practica N°4 - 1°C

Actividad - Funciones anidadas

Si le tomás el gusto al trabajo con las funciones anidadas, verás que cálculos que antes ocupaban dos o más celdas ahora podés realizarlos en la misma celda. Por ejemplo: si tenés que sumar el resultado de varios promedios, antes realizabas el cálculo de los promedios en celdas separadas y luego en otra celda ingresabas la función que sumaba el rango de celdas con los cálculos de los promedios. Bien, con lo visto en el presente módulo ya sabés realizar la suma de los promedios, pero con la función PROMEDIO como argumento de la función SUMA.

Por otra parte, así como eran demasiadas las funciones para desarrollarlas a todas en un único módulo del curso, es obvio que en una sola ejercitación tampoco podremos practicar todas las funciones que encontramos en las categorías de funciones vistas en las páginas del módulo.

Así que trataremos de abarcar en estos ejercicios las funciones que podés necesitar más a menudo.

Con Excel como herramienta de trabajo podrás realizar la presente ejercitación.

1. Descargá el archivo ejercitación02.xls a tu computadora (preferentemente en la carpeta Mis Documentos) y luego abrilo para trabajar en él.

2.En el libro que se abre tenés en la hoja Ventas la tabla Ventas y comisiones por ventas, donde están cargadas las ventas logradas por un grupo de vendedores y la comisión correspondiente a cada venta. La comisión se calcula de acuerdo con la Escala de comisiones por ventas que está debajo de la tabla anterior.

En este primer paso analizaremos la fórmula escrita en alguna celda del rango D5:D17. Por ejemplo, analizaremos el cálculo de la fórmula de la celda D5, con el fin de reafirmar lo explicado referente a la anidación de funciones. Con esta función se obtiene el cálculo de las comisiones por ventas, según la escala de comisiones. La función utiliza el condicional SI para averiguar en qué rango está la venta correspondiente, y de acuerdo a ello calcula el importe de la comisión multiplicando el importe de la venta por el porcentaje de la comisión que corresponda.

Para hacer la multiplicación entre el importe de las ventas y el porcentaje de las comisiones utilizamos la función PRODUCTO (aquí ya podemos ver que está anidada esta función dentro del condicional SI).

La primera parte de la función dice textual: =SI(C5<=$B$25;PRODUCTO(C5;$C$25);. Aquí verificamos si el importe de la venta de la celda C5 es menor o igual que el primer importe de la escala de comisiones ubicado en la celda B25; si esto fuera VERDADERO se calcularía la función PRODUCTO entre el importe de la venta de la celda C5 y el porcentaje de la comisión correspondiente de la celda C25.

Como para este caso puntual el importe de la celda C5 no es menor o igual que el de la celda B25, el condicional hará lo que está cuando el resultado de la comparación es FALSO, y para la fórmula que estamos analizando esto es: SI(C5<=$B$26;PRODUCTO(C5;$C$26);, o sea que está anidado otro condicional SI que ahora evalúa si el importe de la celda C5 es menor o igual que el importe de la celda B26. En este caso la respuesta es VERDADERO (1.254,00 € es menor que 2.500,00 €) por lo tanto se calcula la función PRODUCTO que está cuando el resultado de la comparación es VERDADERO (es decir 1.254,00 € x 1,50%).

3. Ahora te sugerimos que posiciones la selección en cada una de las celdas restantes del rango D5:D17 e intentes realizar el seguimiento de cada fórmula para averiguar por qué llega al resultado que visualizas en la tabla.

Ahora lo que haremos será completar con fórmulas (todas estas fórmulas incluyen funciones) las celdas de las tablas que están en blanco. Posicionate en la celda C3 e ingresá la función que calcule la suma de los valores del rango C5:C17, es decir: =SUMA(C5:C17). Luego, hacé lo propio en la celda D3 sumando los valores del rango D5:D17, es decir: =SUMA(D5:D17). Si lo has hecho bien, deberías ver los mismos resultados que visualizas en la siguiente imagen.

4. Seleccioná la celda G11 e ingresá la función que devuelva la venta máxima: =MAX(C5:C17). Luego en la celda G12 ingresá la fórmula que devuelva la venta mínima: =MIN (C5:C17). En la siguiente imagen podés visualizar los resultados que deberías ver en las celdas correspondientes.

5. Ahora seleccioná la celda H9.

Allí ingresaremos una función que devuelva el código de vendedor que más se repite. Recordá que la función que devuelve esta información es MODA.

Podríamos ingresarla de forma directa a la función, pero en este paso utilizaremos el cuadro de diálogo Insertar función. Pulsá entonces el botón Insertar función.

Después, en el campo Buscar una función: del cuadro de diálogo Insertar función escribí MODA y pulsá el botón Ir. Verás que en el cuadro de lista Seleccionar una función: solo habrá aparecido una función con el mismo nombre que el buscado: MODA.

Ahora, prestá atención, en la parte inferior del cuadro de diálogo Insertar función, a la sintaxis de la función y al comentario que describe lo que la función calcula o devuelve; luego pulsá en el botón Aceptar. En el cuadro de diálogo que aparece: Argumentos de función, en el primero de los campos: Numero1, seleccioná o de forma directa escribí el rango de celdas B5:B17.

Si has hecho bien la tarea de este paso, cuando pulses en el botón Aceptar, en la celda H9 aparecerá el resultado del cálculo, y el valor devuelto por la función será 110.

6. Ahora completaremos la información de la tabla Análisis por vendedor.

En la fila que tiene el rótulo Ventas Totales, por medio de la función SUMAR.SI, obtendremos las ventas totales por vendedor. Te enseñaremos además un truco que posibilita ingresar la fórmula una vez, y que en el mismo paso se copie la fórmula en todas las celdas de un rango, por ejemplo en el rango G5:J5. Entonces lo primero que harás será seleccionar el rango G5:J5.

Ahora, si la primera celda que has seleccionado en el rango fue G5, esa será la celda donde se ingrese la fórmula (o el texto que sea) si comenzaras a escribir. Entonces, escribí allí la fórmula que devuelva las ventas totales para el código de vendedor 100 (recordá que asumimos que la función la estás escribiendo en la celda G5, por eso el vendedor es 100; si la fórmula la estuvieras escribiendo en la celda J5 el vendedor correspondiente es 130).

La fórmula es: =SUMAR.SI($B$5:$B$17;G4;$C$5:$C$17), luego presioná la tecla Control y sin soltar presioná la tecla Intro. Si lo hiciste como indicamos, verás que la fórmula no solo ingresó en la celda G5, sino en todas las celdas de la selección G5:J5.

Observá que utilizamos referencias absolutas para los rangos de comparación y rango suma, porque sino la referencia de desplazaría hacia la derecha, como si estuvieras copiando y pegando referencias relativas.

Para el criterio de comparación sí utilizamos referencias relativas porque la referencia debe desplazarse con la fórmula. En la siguiente imagen podés ver el resultado que deberías obtener luego de ingresada la función como indicamos antes.

7.Ahora completaremos la información de las celdas del rango G6:J6. Debido a que Excel no dispone de una función promedio condicional (al estilo SUMAR.SI para las sumas), tendremos que ingresar celda por celda del rango mencionado la fórmula que devuelva el promedio de ventas para cada vendedor.

Entonces en la celda G6 ingresá =PROMEDIO(C5:C7), en la celda H6 ingresá =PROMEDIO(C8:C11), en la celda I6 ingresá =PROMEDIO(C12:C14) y en la celda J6 ingresá =PROMEDIO(C15:C17). Si has realizado bien los cálculos deberías ver en tu tabla resultados idénticos a los de la siguiente imagen.

8. Después de tantos números, vamos a ver qué podemos hacer con el texto. Pulsá en la hoja Texto para pasar a trabajar en ella.

Allí no hay más que cuatro textos que utilizaremos como argumentos de algunas funciones de manejo de texto. Pulsá en la celda B1 y, utilizando la función CONCATENAR, uní el texto de la celda A1 con un espacio en blanco y con el texto de la celda A3. La fórmula debe ingresarse de la siguiente manera: =CONCATENAR(A1;" ";A3).

Cuando ingreses esta función en la celda B1 y la confirmes, automáticamente se actualizará la función de la celda B3, que devuelve el largo del texto de la celda A1 (la fórmula de la celda B3 es =LARGO(B3)). En la siguiente imagen podés visualizar el resultado y la fórmula propiamente dicha.

9.

En este último paso de la ejercitación veremos la utilización de la función EXTRAE, por un lado, y por el otro la utilización de la función DERECHA.

Pulsá en la celda B5 e ingresá allí la función que recupere o extraiga el texto “extraer” de la cadena de texto que está en la celda A5, la fórmula que tenés que ingresar es =EXTRAE(A5;5;7).

Observá ahora que en la celda C7 hay un valor de error, esto se debe a que en la función que allí está: =B7+B8+B9, la celda B7 tiene una referencia a la celda A7, y esta celda tiene texto que no puede utilizarse para realizar la suma de la celda C7.

Vamos a solucionar este problema. Le indicaremos a Excel que recupere el número 5 del texto de la celda A7 para que lo interprete como tal la función de la celda C7 y arroje un resultado correcto. Para lograrlo, tenés que ingresar en la celda B7 la siguiente función: =DERECHA(A7;1), esta función recupera un carácter comenzando por la derecha, de la celda A7. En la siguiente imagen tenes el resultado de lo que indicamos antes.