Uso eficiente de las hojas de cálculo

Una hoja de cálculo es una aplicación informática que se puede emplear para tareas que necesiten trabajar con datos en forma tabular (sobre una “hoja cuadriculada” formada por filas y columnas) y realizar diversos cálculos con ellos. Su objetivo básico es proporcionar un entorno simple para introducir datos (principalmente numéricos) y a partir de ellos obtener nuevos valores mediante fórmulas y funciones. Además, este tipo de aplicaciones permiten controlar los formatos de presentación así como generar gráficos basados en los datos introducidos o calculados. 

Si bien todos los usuarios tienen ya un conocimiento básico de hojas de cálculo, este taller busca desarrollar, mediante ejercicios prácticos guiados, el uso adecuado y eficiente de los programas de hoja de cálculo. Se empleará Microsoft Excel, dada su gran difusión actual, aunque los ejercicios pueden realizarse con cualquier versión de la aplicación, incluso de aplicaciones similares como Calc, para adquirir las siguientes competencias:

Existen multitud de recursos y tutoriales explicativos de los diferentes programas y versiones; entre ellos se sugiere, por ejemplo:

En cualquier caso se desaconseja el "estudio" de los mismos, ya que se buscará el aprendizaje basado en la realización de ejercicios, "aprender haciendo", si es el caso recurriendo a dichos recursos como ayuda o consulta.

[Descargar los archivos de trabajo que se utilizarán en los diferentes ejercicios +]

Ejercicio 1: nivelación

Tomando la hoja de cálculo con datos de las ventas de un almacén, realizar las siguientes acciones:

  1. (formatos numéricos) Aplicar a los datos de unidades el separador de miles del modo más adecuado.
  2. (formatos numéricos) Aplicar el formato de euros a los datos de precios y a los de portes por unidad.
  3. (formatos numéricos) Establecer un descuento base del 2% en la celda a la derecha de la leyenda Descuento y un IVA del 21% en la celda a la derecha de la leyenda IVA.
  4. (formato de celdas) Aplicar el formato de bordes que se considere oportuno
  5. (formato condicional) Destaca automáticamente en negrita los datos de unidades que sean mayores que 750.
  6. (formato condicional) Destaca también automáticamente con fondo rojo aquellos datos de unidades inferiores a 250.
  7. (fórmulas y arrastre) Calcular el precio base de las ventas de los diferentes productos, primero para las ASAS y luego, arrastrando, copiar el cálculo para todos los demás.
  8. (formato de columnas) Autoajustar el tamaño de la columna en la que no se puedan visualizar todos los datos.
  9. (fórmulas con referencias absolutas) Calcular el descuento a aplicar en las ASAS y copiar arrastrando para todos los demás productos.
  10. (fórmulas) Calcula el precio neto de cada venta.
  11. (fórmulas con nombres) Nombra como IVA la celda donde hemos puesto el tipo de IVA aplicable y utiliza la expresión IVA para calcular el importe del IVA de las asas, y luego arrastrando el de los demás productos.
  12. (fórmulas) Calcula el precio de cada venta.
  13. (funciones sencillas) Calcula eficientemente con la función Suma, el número total de unidades y el monto total de cada uno de los conceptos de base, descuento, neto, IVA y precio de venta, y aplica el formato adecuado.
  14. (funciones sencillas) Calcula de la forma más eficiente el valor medio, máximo y mínimo de la columna de unidades, aplicando el formato adecuado.
  15. (detalles en la copia/arrastre de fórmulas y funciones) Calcula, arrastrando o copiando las fórmulas de las unidades, el valor medio, máximo y mínimo de las restantes columnas de datos, asegurando el formato adecuado.
  16. (funciones: función SI): Indica en la columna de Tipo de transporte, en función de la región de procedencia del Almacén, si se trata de transporte Urbano (si el Almacén está en el Centro) o Interurbano (en los demás casos). Hazlo primero para las ASAS, pero de forma que luego puedas replicarlo para los demás productos.
  17. (funciones: función BUSCARV) Determina el coste de los portes unitarios de cada una de las ventas según la ubicación de cada almacén, en función de la tabla en la misma hoja, y determina con ello el coste de los portes de cada venta (multiplicándolos por las unidades).
  18. (funciones: función BUSCARV) Indica en la columna tamaño si el envío de cada producto se considera Pequeño, Mediano o Grande, según la tabla de rangos contenida en la misma hoja que indica que de 0 a menos de 500 es un envío pequeño, que de 500 a menos de 1000 es un envío mediano y que de 1000 en adelante es un envío grande.
  19. (configurar página para impresión) Prepara la hoja de cálculo para imprimirla en una sola hoja en horizontal con los márgenes que estimes oportunos, añadiendo un encabezado con el título "Ventas almacén" y un pie de página que a la izquierda incluya el nombre del archivo y a la derecha la fecha de impresión.
  20. Guardar la hoja de cálculo trabajada con otro nombre (el que quieras darle) para usarla en posteriores ejercicios. 

Ejercicio 2:  trabajo con hojas grandes; visualización e impresión

Tomando la hoja de cálculo con el listado de la matrícula del conservatorio

  1. (inmovilizar paneles) Fija la primera fila como "títulos" para que se vea siempre en pantalla que es cada columna.
  2. (configurar página / imprimir títulos) Prepara la hoja para impresión, ajustando el diseño y configurando la página de manera que todas las columnas se vean en el ancho de una página en vertical, y que en todas las hojas que se vayan a imprimir aparezca siempre la "fila de títulos" (que rotula cada columna).
  3. (configurar página) Añade un encabezado para la impresión, y un pie de página con el número de página y el número total de páginas del documento impreso.

Ejercicio 3: trabajo con varias hojas

A partir de la hoja con los datos de la población por nivel de estudios 

  1. Crea una segunda hoja (y llámala porcentajes) en que se calculen y muestren los porcentajes de población correspondientes a cada nivel de estudios en cada uno de los años.
  2. Crea una tercera hoja (y llámala incremento) que indique la tasa de incremento (en porcentaje, con dos decimales) de año en año para cada nivel de estudios desde 1979 hasta 2004. La tasa de incremento, en tanto por uno, se calculará como

Ejercicio 4: gráficos

A partir del libro con datos de precios de inmuebles en la provincia de Valencia

  1. Representa gráficamente la serie de datos con la evolución de los precios contenidos en la hoja "Burjassot" (gráfico de 1 serie; 1D).
  2. Representa gráficamente la información contenida en la hoja "varios ...", y explora diferentes maneras de presentar los datos (gráfico de varias series; 2D).
  3. De los datos contenidos en la hoja "muchos ..." representa gráficamente únicamente los datos de Sagunto y Puerto de Sagunto (selección de datos para gráfico).

Ejercicio 5: gráficos+

A partir del libro con datos de población por nivel de estudios trabajado anteriormente

  1. Representa la información de los porcentajes de la segunda hoja en el gráfico que se considere más adecuado, situándolo en una hoja justo a continuación de la hoja "porcentajes".
  2. Representa la información de las tasas de incremento de los analfabetos y de la población total a lo largo de los años en el gráfico que se considere más adecuado, en una quinta hoja.
  3. Representa en otra hoja la distribución de la población por nivel de estudios en el año 1988. 

Ejercicio 6: compendio

A partir del archivo con los datos demográficos de la década de los 90 por comunidades autónomas y por provincias

  1. Aplica el formato que se considere más adecuado, y prepara las hojas para su óptima visualización e impresión.
  2. Sobre los datos de las comunidades autónomas, utiliza el formato condicional para resaltar en verde los años en que aumenta la población de una comunidad, y en rojo los que disminuya.
  3. Crea una nueva hoja indicando el porcentaje sobre el total nacional que supone la población de cada comunidad autónoma, resaltando aquellos valores superiores al 10%.
  4. Realiza un gráfico en que se visualice adecuadamente la población por comunidad autónoma en el año 2000.
  5. Realiza un gráfico en que se visualice adecuadamente la evolución a lo largo de los años de la población de las 4 comunidades autónomas más pobladas.

Ejercicio 7: repaso

A partir del archivo de ventas de la frutería, calcular el precio de venta y la comisión aplicable en cada caso (utilizando la función BUSCARV)

Ejercicio 8: funciones

A partir de los datos electorales históricos de la Comunidad Valenciana, extraídos de http://www.argos.gva.es/ahe/index.html , mediante el uso de funciones:
  1. añade una columna indicando qué tipo de elección es: G (generales), L (locales), A (autonómicas), E (europeas) (función EXTRAE)
  2. añade una columna en que ponga únicamente el año de la elección (función EXTRAE)
  3. añade una fila que cuente el número de convocatorias a las que ha concurrido cada formación (función CONTAR)
  4. añade una fila que cuente el número de convocatorias en las que una formación ha obtenido más de 200.000 votos (función CONTAR.SI)
  5. crea otra hoja en que muestre el porcentaje de votos obtenido por cada formación en caso de haberse presentado (función SI y función ESNUMERO)
  6. añade una columna en que indique en qué ocasiones PP y PSPV han superado ambos el 25% de votos (poniendo "ambos fuertes"), y en cuáles no (función Y)
  7. calcula el número de ocasiones en que algún partido ha superado el 50% de los votos.
  8. determina, de entre las seis formaciones indicadas (descartando otras) la posición dentro de cada elección teniendo en cuenta el número total de votos (función JERARQUÍA)

Ejercicio 9: trabajo con listas: ordenación y filtrado

Tomando de nuevo la hoja de cálculo con el listado de la matrícula del conservatorio  

  1. (ordenación simple) Ordena la lista por nota decreciente.
  2. (ordenación compuesta) Ordena la lista por día y hora de matrícula.
  3. (ordenación compuesta) Ordena la lista por día y hora de matrícula, y en caso de igualdad por nota decreciente.
  4. (filtrado) Filtra la lista para ver sólo los estudiantes de arpa.
  5. (filtrado compuesto) Filtra la lista para ver sólo los estudiantes de primero de piano.
  6. (filtro de número) Filtra la lista para ver sólo los estudiantes de cualquier instrumento y año que tengan una nota de 7 o superior.

Ejercicio 10: trabajo con listas; repaso

Con el archivo de las notas de los estudiantes:  

  1. ordenar la lista según la nota del examen, de mayor a menor
  2. ordenar la lista según la nota del examen de mayor a menor, pero primero los del grupo A y luego los del grupo B
  3. ordenar la lista por clase y después por los apellidos
  4. filtrar para mostrar sólo los de grupo A
  5. filtrar para mostrar sólo los que hayan obtenido en el examen 7 o más.
  6. filtrar para mostrar sólo los del grupo B que hayan aprobado el examen.

Ejercicio 11: compendio

A partir del archivo de notas, calcular la nota final de cada estudiante teniendo en cuenta que:

  1.  la nota de prácticas es la media de las dos notas de prácticas P1 y P2
  2. si se aprueba el examen, la nota final se obtiene ponderando la nota del examen por el 60% y la nota del conjunto de prácticas por el 40%
  3. si no se aprueba el examen, la nota final es la del examen
  4. además, se debe especificar la calificación, que es SUSPENSO para notas menores que 5, APROBADO entre 5 y 7, NOTABLE entre 7 y 9 y SOBRESALIENTE a partir de 9
  5. indicar qué porcentaje de estudiantes han superado la asignatura
  6. indicar cuántos sobresalientes hay
  7. si faltara alguna de las notas, la nota final sería 0 y la calificación sería NO PRESENTADO
  8. (OPCIONAL) un/una estudiante es candidato/a a Matrícula de Honor cuando tiene una nota a partir de 9 y está entre el 5% de las notas más altas (solo se puede dar una MH cada 20 estudiantes)

Ejercicio 12: tablas dinámicas

Tomando una vez más la hoja de cálculo con el listado de la matrícula del conservatorio, emplea las tablas dinámicas para calcular

  1. importe total de las matrículas ingresadas, distribuido por especialidad;
  2. importe total de las matrículas ingresadas, distribuido por especialidad y curso;
  3. número de estudiantes por curso;
  4. número de estudiantes citados por especialidad y día de matrícula;
  5. número de estudiantes de cada curso citados por día y hora de matrícula;
  6. número de estudiantes de cada curso citados cada día, pero mostrando solo los de una especialidad cada vez;
  7. nota media de los estudiantes por cada especialidad (y ordena las especialidades según la nota media); 
  8. nota media de los estudiantes por especialidad y curso;
  9. número de estudiantes por nota, agrupando aquellos de 1 a 2, de 2 a 3, … ;
  10. número de estudiantes por nota, agrupando de medio en medio punto a partir de 5.

Ejercicio 13: tablas dinámicas; repaso

Sobre el ejercicio de las notas de clase trabajado anteriormente, determinar y mostrar:

  1. número de estudiantes de cada clase;
  2. nota media de los estudiantes de cada clase;
  3. número de estudiantes por calificación en cada clase;
  4. nota máxima y mínima de los estudiantes de cada clase;
  5. número de estudiantes que han superado la asignatura y de los que no la han superado.

Ejercicio 14: tablas dinámicas; repaso

Sobre el ejercicio de ventas de la frutería trabajado anteriormente, determinar y mostrar:

  1. cuántos son los ingresos totales por cada producto, y cuál se vende más;
  2. cuáles son los productos más vendidos en cada región;
  3. la evolución de las ventas por regiones y meses;
  4. indicar para cada mes qué es lo que se vende más, frutas o verduras.

Ejercicio 15: compendio

Con los datos de ventas de productos de aviación:

y a continuación realiza las acciones necesarias para responder a las siguientes preguntas:

y realiza al menos dos gráficos ilustrativos que consideres pertinentes (al menos uno de los dos de una de las dos últimas preguntas).

Ejercicio 16: series

En una hoja de cálculo nueva, descubrir la forma más eficiente de escribir, en diferentes columnas:

  1. los números del 1 al 20;
  2. números del 100 al 200 de 10 en 10 (100, 110, ..., 200);
  3. las fechas consecutivas del 28 de febrero al 19 de marzo;
  4. las fechas de todos los domingos de este trimestre;
  5. los días de la semana;
  6. los meses del año;
  7. una serie de rótulos para 6 muestras de datos que ponga: muestra1, muestra2, ... muestra6.

Hacer en una hoja nueva un calendario con todos los días de lunes a viernes de este cuatrimestre.

Ejercicio 17: importar datos de otros formatos

Importar archivo de texto

Introduce los datos del archivo de texto poblacion_capitales a una hoja de cálculo y prepáralos y formatéalos para poder hacer operaciones con ellos.

Traer datos de otras fuentes (I)

Coger la tabla de la clasificación general de la Vuelta a España (buscándola en la página web de La Vuelta) e introducirla en una hoja de cálculo.

Traer datos de otras fuentes (II)

Accede a una página web como

  https://www.visor-libros.com/tienda/autores

coge los autores cuyo apellido empiece por Z e introdúcelos en una hoja de cálculo que tenga una columna para los apellidos y otra para los nombres.

Ejercicio 18: herramientas de Análisis de hipótesis

Dada la información de un préstamo de interés fijo y plazos mensuales, en que conocemos la fecha de concesión, el capital solicitado, el tipo de interés anual aplicable y la duración del préstamo en años:

  1. calcula la cantidad a pagar mensualmente;
  2. redondea dicha cantidad al céntimo de euro;
  3. desarrolla la hoja de cálculo para mostrar el capital pendiente después de realizar cada uno de los pagos mensuales (ver el desarrollo de series) ; 
  4. emplea la herramienta de administración de escenarios para visualizar y guardar la situación con diferentes capitales solicitados y tipos de interés, y genera un resumen con la cantidad a pagar mensualmente y la cantidad adeudada al final del primer año de préstamo;
  5. emplea la herramienta de búsqueda de objetivos para saber qué tipo de interés tendríamos que conseguir de la entidad bancaria para financiar un capital de 1.000 euros más al que habíamos pedido inicialmente en el mismo número de meses y pagando la misma cantidad mensual (y guarda también esta situación como un nuevo escenario).

Ejercicio 19: compendio

Con la hoja de datos de las ventas de un almacén trabajada y guardada al final del ejercicio 1

  1. trabaja con la administración de escenarios analizando la situación con distintos descuentos y tipos de IVA y realiza un resumen de resultados de los ingresos netos totales y de los impuestos devengados;
  2. trabaja con la búsqueda de objetivos para obtener resultados concretos, por ejemplo qué descuento debería aplicarse para obtener una cifra de ingresos netos concreta, y guarda la situación como un nuevo escenario; 
  3. guardar los diferentes escenarios analizados, tanto los diferentes tipos de descuentos e impuestos como los valores necesarios para alcanzar un determinado objetivo e incluye un resumen de todos ellos.

Ejercicio 20: repaso

Tomando de nuevo la hoja de cálculo con el listado de matrícula del conservatorio, realiza las operaciones necesarias para:

  1. indicar en una nueva columna cuál sería el posible importe de la matrícula calculado incrementando un 20% el precio de la matrícula para los estudiantes de piano, disminuyendo un 10% el precio para los de arpa y aplicando un incremento del 3% para los estudiantes de las demás especialidades;
  2. incluir en una nueva columna un rótulo “estudiar” para aquellos estudiantes con importe de matrícula 0 y nota inferior a 5 (y ningún rótulo para el resto de estudiantes);
  3. mostrar el número de estudiantes por especialidad agrupados por notas entre 0 y 2,5, entre 2,5 y 5, entre 5 y 7,5 y entre 7,5 y 10;
  4. determinar qué incrementos o decrementos deberían tener las matrículas de los estudiantes de piano, arpa o los del resto de especialidades (modificando la propuesta del punto segundo) para obtener un importe total de las matrículas igual a 70.000 euros (empléese la herramienta de búsqueda de objetivos para encontrar los valores, y la de escenarios para hacer un resumen de las diferentes opciones posibles).