¡Descarga experto en excel tema 4 y más Ejercicios en PDF de Competencias en MS Microsoft Excel solo en Docsity!
Y
a hemos visto cómo introducir datos en listas y hacer operaciones para trabajar con estos datos: ordenar los datos, verlos con un formulario, filtrar los datos según las condiciones que quieras, etc.
En esta lección vamos a ver otra herramienta más de Excel que puedes usar en tus listas de datos: los subtotales automáticos.
En ocasiones te puede interesar calcular los totales parciales y totales globales de una determinada columna de la lista. Podrías calcular estos valores a través de fórmulas y funciones , pero Excel te proporciona una opción automática para realizar estos cálculos: al utilizar subtotales automáticos , se insertan y se escriben automáticamente los títulos y las fórmulas en las filas de totales.
Excel presenta estos resultados en forma de esquema y nos permite desplazarnos a distintos niveles en este resumen de datos. Este esquema que muestra Excel permite agrupar las filas de la lista, para mostrar la información que hay de manera resumida.
Copyright (c) Computer Aided Education S.A. 1
1. SSUBTOTALES AAUTOMÁTICOS
Por ejemplo, en la hoja de la página anterior, vemos los clientes de una determinada empresa, con su ciudad, la fecha en que empezaron a comprarnos y la cantidad de dinero que nos han pagado por sus compras. En este caso, a través de los subtotales automáticos podemos obtener, de una manera rápida y sencilla, la cantidad de dinero ingresada en cada ciudad.
Para crear los subtotales automáticos en una lista, lo primero que tenemos que hacer es ordenar la lista respecto al campo para el que queramos obtener los subtotales.
Recuerda que puedes ordenar la lista con los botones Orden ascendente y Orden descendente o con la opción de menú Datos - Ordenar.
Una vez ordenada la lista, ponte en una celda de la lista y selecciona la opción de menú Datos - Subtotales , con lo que aparecerá el cuadro de diálogo de la derecha.
En la lista Para cada cambio en indicarás la columna por la que quieras agrupar los datos de la lista y calcular los subtotales.
En la lista Usar función deberás indicar la función que vas a utilizar para calcular los subtotales
Las funciones que puedes usar para crear subtotales son:
Suma : suma los valores de las celdas Cuenta : sirve para contar el número de datos que hay en las celdas. Promedio : calcula el valor medio de los datos. Máx , Mín : obtiene el valor máximo o el mínimo, respecti- vamente. Producto : el resultado de esta función es el producto o multiplicación de los datos. Contar números : esta función devuelve el número de datos de las celdas que son números.
2
Para ello, ejecuta la orden Subtotales dos veces, eligiendo cada vez una columna distinta en Para cada cambio en y desactivando la casilla de verificación Reemplazar subtotales actuales la segunda vez que se llama a la orden.
Ten en cuenta también que tienes que ordenar la lista, como segundo criterio de ordenación, por el campo que crea el subtotal anidado.
Por ejemplo, en la hoja anterior, hemos ordenado la lista por la ciudad y luego por el año. Luego hemos creado los subtotales automáticos por la ciudad, y, tras aceptar la ventana, hemos vuelto a crear los subtotales, pero esta vez por el año y desactivando la casilla Reemplazar subtotales actuales.
Ten en cuenta que, siempre que la casilla de verificación Reemplazar subtotales actuales esté activada, se cambiarán todos los cálculos y subtotales al aceptar el cuadro de diálogo de Subtotales.
Otra cosa que podemos hacer es utilizar más de una función en los subtotales. Para ello tendremos que ejecutar dos veces la orden Subtotales , eligiendo cada vez una función distinta y desactivando la casilla Reemplazar subtotales actuales la segunda vez.
Para eliminar los subtotales, podemos ejecutar la orden Subtotales y pulsar el botón Quitar todos. Además, recuerda que, cuando trabajas con listas filtradas , los subtotales sólo tienen efecto sobre las filas visibles.
4
Ten en cuenta que los valores de los totales generales siempre se calculan a partir de los datos detallados y no de las filas de subtotales. Por ejemplo, si utilizas la función para calcular la media de los datos (función Promedio ), la fila de totales mostrará el promedio de todos los datos detallados (es decir, el promedio de los valores de todas las filas), y no el promedio de los valores de los subtotales.
Además, los valores de los totales y subtotales se vuelven a calcular automáticamente al editar los datos detallados, es decir, los valores de las distintas columnas de las filas.
C
uando muestras los subtotales automáticos, Excel pone en forma de esquema la lista, agrupando las distintas filas con cada fila de subtotal asociada y las filas de subtotales con la fila del total general.
En este esquema con los subtotales, puedes mostrar u ocultar los distintos datos de la lista a través de los botones que hay en la zona izquierda de la hoja, a la izquierda de los números de fila.
Los botones que están en la parte superior de esta zona permiten desplazarse por los distintos niveles del esquema. Debajo de los botones de símbolos de esquema se presenta un esquema del estado actual de la hoja.
Los cuadros con el símbolo en su interior indican que se puede ampliar su contenido, para ver todas las filas que forman dicho subtotal.
Copyright (c) Computer Aided Education S.A. 5
2. OORGANIZACIÓN DDE SSUBTOTALES
Para ocultar todas las filas, pulsa el botón del primer nivel. Para que se vean todas las filas, haz clic en el botón del nivel que está más a la derecha (por ejemplo, si tienes tres niveles , pulsa en ).
También es interesante, para crear un informe, añadir gráficos para mostrar o analizar los datos de un modo sencillo.
Para crear un gráfico a partir de los subtotales, oculta primero las filas de detalle y quédate sólo con las filas de subtotales. Luego selecciona las celdas de la lista sobre las que quieras crear el gráfico y utiliza el asistente para gráficos para crearlo.
Ten en cuenta que los datos que se presentan en el gráfico son los datos de las filas que se ven en la hoja. Al aumentar o disminuir el detalle de los datos resumidos, el gráfico se actualiza.
Así pues, hacer un informe a partir de los datos de una lista, utilizando todas las posibilidades que proporciona Excel, puede convertirse en una tarea sencilla.
H
asta ahora hemos visto cómo realizar cálculos de forma automática. Pero, a veces, interesa hacer cálculos de celdas o filas de una lista que cumplan sólo ciertos criterios.
Estos cálculos se pueden realizar mediante el uso de funciones en las fórmulas y, por tanto, puedes poner los resultados en cualquier lugar de la hoja de cálculo.
Vamos a ver algunas de las funciones que tiene Excel para realizar totales avanzados:
Copyright (c) Computer Aided Education S.A. 7
Al crear los subtotales en una lista, tam- bién puedes hacer que se inserten saltos de página entre los dis- tintos grupos de subtotales, con la opción Salto dde ppági- na eentre ggru- pos del cuadro de diálogo de Subtotales. De esta forma, cada grupo se imprimirá en una página distinta.
4. TTOTALES AAVANZADOS
z SUMAR.SI(rango;criterio;sum_rango)
Esta función sirve para realizar un cálculo parcial sobre los datos de una lista: sumará sólo los valores de la lista que cumplan cierto criterio.
En el primer argumento se indica el rango de la lista a evaluar, es decir, la columna donde estarán los datos que compararemos con el criterio. En el segundo argumento indicamos el criterio a seguir en la evaluación, que aplicaremos sobre el rango indicado en el argumento anterior. Y el tercer argumento se utiliza para indicar el rango que contiene los valores que quieres sumar.
Por ejemplo: =SUMAR.SI(D4:D15;''<15/04/2003'';F4:F15)
Esta función comprueba, para cada una de las filas entre la 4 y la 15 , si los valores de las celdas que están en la columna D son anteriores al 15/04/2003 y, si lo son, suma el contenido de las celdas de la columna F , obteniendo el resultado total de esta suma.
z CONTAR.SI(rango;criterio)
Esta función se utiliza para saber qué número de filas cumplen un determinado criterio.
En el primer argumento se indica el rango de evaluación, en el que vas a contar las celdas, y en el segundo, el criterio. Dicho de otra forma: esta función devuelve el número de celdas del rango que cumplen el criterio.
Por ejemplo: =CONTAR.SI(D4:D15;''<15/04/2003'')
Esta fórmula contará el número de celdas entre D4 y D15 que cumplen que tienen un valor de antes del 15/04/.
z CONTAR.BLANCO(rango)
Mientras que con la función anterior contábamos las celdas que cumplían un determinado criterio, con esta podremos calcular las celdas en blanco dentro de una lista.
En este caso tenemos un solo argumento, en el que indicaremos el rango del que queremos contar el número de celdas en blanco.
8
Las funciones que tiene Excel en la categoría de bases de datos son:
BDPROMEDIO : calcula el promedio o media de una serie de números. BDCONTAR : cuenta el número de celdas que tienen números. BDCONTARA : cuenta el número de celdas que tienen algún valor (es decir, que no están en blanco). BDEXTRAER : obtiene un único registro de la lista, según los criterios. Si hay más de un registro que cumple los criterios o no hay ninguno que los cumpla, la función devuelve un valor de error. BDMAX : encuentra el valor máximo. BDMIN : encuentra el valor mínimo. BDPRODUCTO : multiplica los valores de una columna de la lista. BDDESVEST : calcula la desviación estándar, teniendo en cuenta que los datos de la lista son una muestra de la población total. BDDESVESTP : calcula la desviación estándar, teniendo en cuenta que los datos de la lista representan el conjunto de la población de datos. BDSUMA : suma los números de una columna de la lista. BDVAR : calcula la varianza, basándose en una muestra de la población. BDVARP : calcula la varianza, teniendo en cuenta que los datos de la lista representan el total de la población.
10
