Ejercicio 20 Excel: Objetivo: Uso de Funciones Avanzadas

Practica funciones avanzadas: CONTAR.SI, SUMAR.SI

En un libro de Excel crea una hoja llamada «salarios», en ella copia la siguiente estructura y datos

 

 

  • Introduce las fórmulas y funciones necesarias para realizar los siguientes cálculos:
  • La comisión es un 10% de las ventas.
  • El salario de cada empleado será la comisión + su base
  • Calcular el total, mínimo, máximo y promedio para cada columna
  • En la rejilla de la derecha debes introducir las funciones necesarias para realizar los siguientes cálculos:
  1. Cuantos salarios hay.
  2. Cuantos salarios hay superiores a 1200€.
  3. Cuantos empleados han tenido un volumen de ventas superior a 1000€.
  4. Suma total de las ventas inferiores a 3000€.
  5. Suma total de las ventas superiores a 6000€
  6. Suma total de los salarios mayores que 1200€
  7. Suma total de las ventas de los empleados que tienen una base que sea como mínimo de 1200€.
  8. Suma total de las ventas de los empleados que tienen una base inferior a 1000€.
  9. Define una función tal que si el resultado del ejercicio 8 es superior al del ejercicio 9 aparezca el texto «8 es superior», en caso contrario que aparezca el texto «8 es inferior»
  • Crea un gráfico de columnas en el que aparezcan las bases y las ventas agrupadas por empleado.
Si deseas puedes consultar la solución.
Volver al Índice

Ejercicio 19 Excel: Objetivo: Uso de Funciones Avanzadas

Practica funciones avanzadas: SI, BUSCARV

En un nuevo libro introduce la siguiente información con esta estructura.

 

 

  • Los títulos de las películas, el género y el precio deben aparecer automáticamente con una función BUSCARV. Para ello es aconsejable, antes de empezar, que des un nombre a todo el cuadro donde están las películas, puedes llamarlo por ejemplo Inventario.
  • La columna IVA será la cantidad por el precio de la película por el valor de la celda F3.
  • El P. IVA inc. será la cantidad por el precio más el resultado del IVA.
  • El descuento se puede calcular con una condicional (SI anidados) o con la función BUSCARV a partir del cuadro Descuentos.
    • El descuento será el porcentaje correspondiente multiplicado por P. IVA inc . (Si esta parte te cuesta demasiado puedes incluir una nueva columna que sea Cantidad descontada, y dejar que en la columna descuento que aparezca el porcentaje a descontar).
  • El P.Total será el P. IVA inc menos el descuento.
  • Haz constar en sus casillas el precio máximo y mínimo pagado por un dvd.
  • En Cantidad total debe aparecer el total de copias vendidas y en Total general la suma de los P.Total
Si deseas puedes consultar la solución.
Siguiente ejercicio

Ejercicio 18 Excel: Objetivo: Uso de Funciones Avanzadas

Practica Funciones avanzadas: SI, BUSCARV

Antes de hacer los siguientes ejercicios realiza las prácticas proporcionadas por el profesor: Función Si, Si Anidados, Cine Videoclub, Campamento, Herencias, Empresa, Final México y Población.
Introduce la siguiente hoja de cálculo en un libro nuevo. Es importante respetar la estructura, el formato y los datos

 

 

  • Para calcular el precio sin IVA se ha utilizar la tabla de tarifas. Las tarifas serán:

SI la cantidad de Huéspedes = 1
Entonces cantidad de días * tarifa individual
SI NO SI cantidad de huéspedes = 2
Entonces cantidad de días * tarifa doble
SI NO cantidad de días * tarifa familiar

  • El IVA será: Precio sin IVA x 18%
  • El precio con IVA se obtendrá sumando las casillas anteriores
  • La cantidad a descontar es el porcentaje correspondiente del precio sin IVA aplicando la tabla de descuentos. Para aplicar esta tabla tendrás que utilizar la función BUSCARV.
  • El precio final será el precio con IVA menos la cantidad a descontar.
  • Realiza un gráfico circular con el número de habitación y la cantidad de huéspedes. Con el número de habitación y el precio final realiza un gráfico de columnas.
Si deseas puedes consultar la solución.
Siguiente ejercicio

Funciones Avanzadas en Excel 2007 Parte II

Función BUSCARV

Esta función busca un valor específico en la primer columna de una tabla y devuelve, en la misma fila, un valor de otra columna de dicha tabla.

Su estructura es la siguiente:
BUSCARVvalor_buscado matriz_buscar_en  ;indicador_columnas ; ordenado )


Valor_buscado Valor que se va a buscar en la primera columna de la tabla
Matriz_buscar_en Una tabla de dos o más columnas de datos.
Indicador_columnas Número de columna de matriz_buscar_en desde la cual debe devolverse el valor coincidente.
Ordenado Valor lógico que especifica si BUSCARV va a buscar una coincidencia exacta o aproximada.

  • Si el valor es VERDADERO si no localiza ninguna coincidencia exacta, devolverá el siguiente valor. Para ello los valores de la primera columna de matriz_buscar_en deben estar ordenados ascendentemente.
  • Si es FALSO, BUSCARV sólo buscará una coincidencia exacta.
Ejemplo de uso de esta función: Imaginemos las siguientes tablas:
En la tabla principal par conocer el título, genero y precio de la película podemos usar la función BUSCARV. Se tratará de buscar el código en la tabla inferior y que nos devuelva el valor deseado.
Para el Título:
BUSCARV( A9 ; A23:D36 ; 2 ; 1 )
Para el género:
BUSCARV( A9 ; A23:D36 ; 3 ; 1 )
Para el Precio:
BUSCARV( A9 ; A23:D36 ; 4 ; 1 )

Función CONTAR.SI

La función CONTAR.SI cuenta el número de celdas dentro de un rango que cumplen un criterio especificado por el usuario.

La sintaxis de la función CONTAR.SI es:
CONTAR.SI( rango ; criterio)

  • rango El rango de celdas que se van a contar.
  • criterios Condición que determina las celdas que se van a contar. Por ejemplo, los criterios pueden expresarse como 32, «>32», B4, «manzanas» o «32».

NOTA: En los criterios se puede utilizar los caracteres comodín — signo de interrogación (?) y asterisco (*) —. El signo de interrogación corresponde a un solo carácter cualquiera y el asterisco equivale a cualquier secuencia de caracteres.

Por ejemplo, imagina que queremos contar la cantidad de comedias que hay en el siguiente listado.

La función quedaría de la siguiente forma:

= CONTAR.SI( C25:C38 ; «Comedia» )

Función SUMAR.SI

La función SUMAR.SI sirve para sumar los valores de un rango de celdas que cumple los criterios que se especifican.
La sintaxis de la función SUMAR.SI:

SUMAR.SI( rango ; Criterio ; rango_suma)
  • rango El rango de celdas que se desea evaluar según los criterios especificados.
  • criterios Condición que determina las celdas que se van a sumar. Por ejemplo, los criterios pueden expresarse como 32, «>32», B5, 32, «32», «manzanas».
  • rango_suma Las celdas que deseamos sumar si se cumple el criterio. Si se omite el argumento rango_suma, Excel suma las celdas que están especificadas en el argumento rango.
Siguiendo con la tabla anterior como ejemplo, imaginemos que queremos sumar el precio de las películas infantiles. La función quedaría de la siguiente forma:
=SUMAR.SI( C25:C38 ; «infantil» ; D25:D28 )

Funciones Avanzadas en Excel 2007 Parte I

En explicaciones anteriores ya hemos hablado sobre cómo utilizar las funciones. En este apartado nos centraremos en el uso de algunas funciones específicas y muy útiles en muchos casos, las funciones SI, Y, O y en el siguiente apartado explicaremos las funciones BUSCARV, CONTAR.SI y SUMAR.SI.

La función SI

Como ya explicamos anteriormente la función SI es una función lógica que comprueba si se cumple una condición y devuelve un valor si se evalúa como VERDADERO y otro valor si se evalúa como FALSO.
Podemos hacer uso de ella mediante la opción de insertar función y usar el cuadro de diálogo Argumentos de funcíón.
Sin embargo, ahora queremos ampliar el uso de esta función y, para ello, vamos a escribir directamente la función en la celda donde queremos que aparezca el resultado. Debemos de tener en cuenta su estructura, dando especial atención a cada uno de los símbolos utilizados.
La estructura es la siguiente:
=SI( Prueba_lógica ; Valor_verdadero ; Valor_falso )
  • La prueba lógica es la condición que queremos evaluar.
  • El valor si verdadero es el valor que aparecerá como resultado si se cumple la condición
  • El valor si falso es el valor que aparecerá si no se cumple.
Para la prueba lógica usamos los símbolos de comparación:
  • > mayor que
  • < menor que
  • >=  mayor o igual que
  • <=  menor o igual que
  • <> distinto de
Así si deseamos evaluar si la nota de un alumno situada en la celda A1 está aprobada (es mayor o igual a 5) escribiríamos lo siguiente en la celda donde queremos que aparezca el resultado:
=SI( A1>=5 ; «Apto» ; «No Apto» )
El resultado será la palabra «apto» si se cumple la condición y «no apto» si no se cumple.
Importante: Cabe notar que al escribir estas palabras en la fórmula se han utilizado comillas. Estas son necesarias siempre que utilicemos texto dentro de una fórmula.
Podemos ampliar el uso de esta función teniendo en cuenta que podemos incluir como argumento de la función SI otras funciones.

Funciones Y,  O

Estas funciones permiten evaluar varias condiciones a la vez y son ideales para utilizar en el primer argumento de la función SI.
A ambas funciones le podemos pasar varias pruebas lógicas como argumentos separados por ; (punto y coma).
Y( prueba_lógica1 ; prueba_lógica2 ; prueba_lógica3 ; …)
O( prueba_lógica1 ; prueba_lógica2 ; prueba_lógica3 ; …)
  • La función Y devuelve como resultado VERDADERO si todos los argumentos son VERDADEROS
  • La función O devuelve como resultado VERDADERO si alguno de los argumentos son VERDADEROS
Veamos algunos ejemplos de uso de estas funciones junto con la función SI:
=SI( Y( A1>=7 ; A<9 ) ; «Notable» ; «otro» )
según este ejemplo si la celda A1 tiene un valor mayor o iguala 7 Ymenor a 9 aparecerá como resultado la palabra «notable».
=SI( O( A1>=5 ; B1>=5 ) ; «Apto» ; «No apto» )
según este ejemplo si en la celda A1 O en la celda B1 aparece un valor superior o igual a 5 el resultado será la palabra «Apto». Con que ocurra en alguno de los casos se evalúa como VERDADERO
=SI( Y( A1>=5 ; B1>=5 ) ; «Apto» ; «No apto» )

según este ejemplo si en la celda A1 Y en la celda B1 aparece un valor superior o igual a 5 el resultado será la palabra «Apto». Debe ocurrir en los dos casos para ser evaluado como VERDADERO

SI ANIDADOS

En los argumentos Valor_verdadero y Valor_falso de la función SI también podemos incluir cualquier función. Hasta incluso podemos incluir la función SI. Esta idea es la que nos lleva a utilizar SI anidados, es decir un SI dentro de otro.
La estructura sería la siguiente:
=SI( Prueba_lógica1 ; Valor_Verdadero ; SI( Prueba_lógica2; Valor_Verdadero; Valor_falso ))
En este caso hemos anidado un si dentro de otro, pero podríamos continuar con la idea y anidar tantos como necesitáramos.
Al utilizar esta estructura podemos evaluar distintas situaciones en una misma fórmula obteniendo el resultado apropiado para cada situación.
Por ejemplo podríamos evaluar la nota de un alumno pero no quedarnos sólo con las situaciones de «aprobado» y «suspendido», sino evaluar los casos para «bien», «notable», «sobresaliente»,…
Observa el siguiente ejercicio práctico:
En este ejercicio para calcular las comisiones recibidas por cada uno de los trabajadores se ha utilizado la función SI anidada.
=SI( C2>=2000 ;500; SI( C2>=1000 ; 200 ; 0 ))
 Primero evalúa si las ventas>=2000
                         si se cumple se le asignan 500€,
                         si no se cumple se evalúa si ventas>=1000
                                                                si se cumple se le asignan 200€
                                                                si no cumple ninguna de las condiciones la comisión es 0
Se puede combinar los SI anidados con las funciones Y , O.
En este caso la estructura genérica quedará de la siguente manera:
=SI( Y( Prueba_lógica1 ; Prueba_lógica2 ) ; Valor_Verdadero ; SI( Y( Prueba_lógica3 ; Prueba_lógica4 ) ; Valor_Verdadero ; Valor_falso ))

Ejercicio 17 Excel: Bases de datos: ordenación, cálculo de subtotales, filtrado y tablas dinámicas

Practica con las Bases de datos

Descarga el archivo Ej17 proporcionado por el profesor.

    • Ordena la base de datos por los siguientes campos:
      • Por Edad.
      • Por Nombre.
      • Por Sexo y Edad.
      • Por Afición y fecha ingreso.
      • Por Fecha Ingreso y Cuota.
      • Por Sexo, afición y Cuota.
    • Haz las siguientes selecciones:
      • Aficionados al Baloncesto.
      • Hombres.
      • Personas que paguen más de 24€ de Cuota.
      • Personas que tengan entre 20 y 35 años.
      • Mujeres aficionadas al baloncesto.
      • Hombres aficionados a la natación o al fútbol.
      • Hombres con una cuota entre 12€ y 30€.
      • Aficionados al Baloncesto que paguen una cuota superior a 24€.
      • Hombres aficionados al fútbol que paguen una cuota entre 30€ y 42€.

 

    • Calcula el total de cuotas por sexo.
    • Calcula la media de edad por sexo.
    • Calcula cuantos registros hay de cada afición. Utiliza la función Cuenta sobre este campo.
    • Calcula el total de cuota por afición.
    • Calcula la media de cuota por afición.
    • Construye una tabla dinámica que muestre la suma de cuotas por afición y sexo.
    • Haz una tabla dinámica que muestre la media de edad por Cuota y Sexo.

Bases de datos Excel 2007

Comentaremos a continuación algunas herramientas de las que disponemos en Excel para trabajar con listas o bases de datos.

En primer lugar, debemos tener en cuenta que consideramos una base de datos en Excel a cualquier tabla que presente un listado de elementos o registros. En la primera fila de esta tabla encontraremos el nombre de cada uno de los campos o columnas.
Por ejemplo:
En Excel disponemos de una ficha en la cinta de opciones con todas las herramientas para trabajar con las bases de datos, la ficha Datos.
En esta ficha encontramos algunas opciones para obtener los datos desde alguna fuente externa y opciones para manejar las conexiones existentes. Sin embargo, nosotros partimos de que tenemos la base de datos escrita directamente en Excel.
Comentaremos las opciones del grupo Ordenar y filtrar y Esquema.
Para ordenar los registros de una base de datos tenemos dos opciones:
  • Nos colocamos sobre la columna o campo por el que queremos ordenar la tabla y pulsamos en el botón orden ascendente o en la opción orden descendente dependiendo del orden deseado.

  • Si deseamos ordenar por varios campos debemos acceder a la opción Ordenar de la ficha de opciones datos. Nos aparecerá un cuadro de diálogo donde podremos definir distintos niveles de ordenación.

 

Filtrado de registros. Filtrar los registros o filas de una tabla implica mostrar únicamente las filas deseadas, las que cumplan una determinada condición. Para filtrar los datos pulsamos en el icono Filtro de la ficha de opciones Datos.

 

En cada uno de los encabezados de las columnas aparecerá una flecha que nos permite acceder a un menú asociado a cada una de las columnas. Desde este menú podremos ordenar los datos y filtrarlos. Podemos filtrar los datos seleccionando aquellos que deseamos visualizar o eligiendo entre los distintos filtros (diferentes dependiendo del tipo de datos de la columna) que nos permiten definir condiciones más complejas.
Otra opción interesante para las bases de datos en Excel es el cálculo de subtotales. Accedemos al cuadro de diálogo Subtotales desde el icono de la ficha datos.
Importante: Antes de acceder a este cuadro de diálogo debemos ordenar la tabla por el campo que deseamos agrupar los datos.
En este cuadro de diálogo elegimos el campo por el que queremos agrupar (Para cada cambio en…) y el cálculo que deseamos realizar indicando la Función y los campos a los que se va a agregar un subtotal.
El resultado final con las opciones elegidas en la imagen sería el siguiente:
Como se puede observar se agrupan los datos por sexo y se calcula la suma de las cuotas pagadas.
Por último hablaremos brevemente de las tablas dinámicas. Esta opción la encontramos en la ficha Insertar de la cinta de opciones.
Una tabla dinámica nos permite resumir los datos de la tabla y organizarlos de tal manera que sea más sencillo su estudio. Cuando pulsamos en la opción indicada nos aparece una nueva hoja de cálculo y distintas opciones para trabajar con las tablas dinámicas. Entre estas opciones observaremos una ficha en la parte derecha de la ventana.
Elegiremos los campos que se desean agregar al informe. Y podremos definir la tabla dinámica indicando qué campos van a aparecer como rótulos de columnas, rótulos de fila y valores.
Por ejemplo:
Con lo que el resultado sería:
Esta tabla se puede modificar cambiando los campos elegidos o su posición como rótulo de fila, columna o valores. Además se pueden filtrar los datos desde la propia tabla.
Por otra parte, podríamos realizar un gráfico a partir de la tabla dinámica. Aunque podríamos realizarlo de la misma manera que cualquier otro gráfico, se creará un gráfico dinámico con opciones muy similares a las tablas dinámicas.En nuestra web podrás encontrar ejercicios para practicar con bases de datos
Siguiente lección: Funciones Avanzadas

Ejercicio 14 Excel: Objetivo: Gráficos

Practica Gráficos

Copia la siguiente hoja de cálculo:

 

  • Calcula los totales para la columna TOTAL VENTAS.
  • Crea los siguientes gráficos:
    • Realiza el gráfico de barras correspondiente al total de ventas de los diferentes meses.
    • Realiza el gráfico de barras apiladas de los meses de Enero, Febrero y Marzo.
    • Realiza el gráfico de barras apiladas de los meses de Abril, Mayo y Junio.
    • Realiza el gráfico circular del total de ventas mensuales para saber que fracción de nuestras ventas se realizó en cada uno de los meses.
    • Realiza el gráfico de líneas del producto 1y producto 2 que muestre su variación en todos los meses.
  • Inserta títulos y leyendas en todos los gráficos.
  • Modifica los datos de la hoja y observa el efecto producido en los gráficos.
Si deseas puedes consultar la solución.
Siguiente ejercicio