Realizar cálculos en los conjuntos de datos

1. Mediante fórmulas

Los filtros elaborados en el capítulo anterior permiten ver solo las filas correspondientes a uno o varios criterios. Además de la presentación de estos detalles, probablemente querrá obtener cálculos estadísticos sobre los datos filtrados.

Una regla importante es siempre colocar los cálculos por encima de las listas de datos. Así no tendrá problemas al añadir nuevos datos. Además, si coloca los cálculos a la derecha de las listas, el filtro podría ocultar las filas con fórmulas de cálculo.

Cuando vamos a aplicar filtros sucesivos, queremos ver los elementos siguientes:

Las fórmulas de cálculo "TODOS LOS GASTOS" no deben verse afectadas por los filtros, se van a usar dos funciones de cálculo:

Para las fórmulas de cálculo "GASTOS FILTRADOS" que deben verse afectadas por los filtros, solo será necesaria una función: la función matemática SUBTOTALES.

La sintaxis de la función SUBTOTALES es la siguiente: =SUBTOTALES(nº_función,ref1,[ref2],...])

Los argumentos ref1, ref2 representan a las celdas o rangos de celdas (254 máx.) para las que quiere obtener un subtotal.

El argumento nº_función representa un número comprendido entre 1 y 11 ó 101 y 111. Los valores del argumento nº_función se detallan más abajo.

* Los valores ocultos son los de las filas ocultas por el comando Ocultar.

La función SUBTOTALES ignora toda fila no comprendida en el resultado de una lista filtrada, sea cual sea el número del argumento nº_función empleado.

De momento...

1. ¿Qué funciones utilizar para los cuadros resumen simples?

a. Presentación

Excel pone a su disposición numerosas funciones para realizar cálculos estadísticos. Las primeras funciones que debe dominar son las que permiten diseñar cálculos en función de un criterio. Los resultados de este tipo de cálculo dependen del valor de cada celda de un rango, llamado rango de criterios.

Nuestro primer cuadro resumen se construirá a partir del libro Facturación.xlsx que reúne todas las facturas de una empresa durante un año.

Nuestro objetivo es obtener diferentes estadísticas por tipo de trabajo, tipo de cliente y técnico.

En primer lugar, diseñe en la hoja ESTAD las tablas que se presentan a continuación.

Disponemos de tres funciones para realizar los cálculos:

Número de facturas: función CONTAR.SI

Sintaxis: =CONTAR.SI(RangoCriterio;Criterio)

Importe total: función SUMAR.SI

Sintaxis: =SUMAR.SI(RangoCriterio;Criterio;RangoSuma)

Importe medio: función PROMEDIO.SI

Sintaxis: =PROMEDIO.SI(RangoCriterio;Criterio;RangoPromedio)

El argumento RangoCriterio corresponde al rango de celdas en el que debe comprobarse el criterio.

El argumento Criterio es un número, una expresión, una referencia de celda o una cadena de texto entre comillas que determina sobre qué celdas aplicar el cálculo de síntesis.

El argumento RangoSuma o RangoPromedio representa el rango de celdas en el que se debe calcular la suma o el promedio.

Cuando realiza cálculos en bases de datos grandes, le aconsejamos que nombre los rangos y que utilice los nombres en las fórmulas más que seleccionar el conjunto de celdas cuando va a crear las fórmulas. Este método será aún más eficaz si los datos y las estadísticas se encuentran en hojas de cálculo diferentes.

Antes de escribir las fórmulas, vamos a crear los nombres que se emplearán.

Se debe dar nombre a cuatro rangos de celdas:

• TipoTrabajo

• TipoCliente

• Técnico

• Importe

Cuando sustituya los datos del año n-1 por los datos del año n, el número de filas no será idéntico. Para que nuestras estadísticas sean útiles en las facturas de los futuros años, es preferible, por seguridad...

1. Principio

El cálculo matricial le permitirá obtener resultados allí donde otras funciones requieren realizar cálculos intermedios. Generalmente, esto puede evitarle tener que insertar columnas suplementarias en las tablas. Un cálculo matricial utiliza un solo rango o varios rangos de datos de igual tamaño llamados matrices.

Aplicado a rangos de datos que contienen muchas filas, esta técnica de cálculo requiere cantidades de recursos considerables, lo cual a veces nos hace aumentar el tiempo de cálculo de Excel. Si se da cuenta de que este tiempo de cálculo va en detrimento de su trabajo, desactive el modo de cálculo de Excel y utilice el modo de cálculo manual.

Una fórmula matricial debe necesariamente validarse no mediante Intro sino mediante la combinación de teclas CtrlMayúsIntro. Una vez validada la fórmula, Excel pone la fórmula entre llaves. Cuando modifique una fórmula matricial, debe volver a validar mediante CtrlMayúsIntro. Si olvida utilizar esta combinación, aparecerá un mensaje de error.

Le proponemos que practique con algunos ejemplos para ilustrar esta técnica de cálculo. Una vez dominado el principio, podrá adaptarlo a sus propios casos concretos.

Cada hoja de este libro contiene los ejercicios propuestos.

2. Aplicar esta técnica a algunos ejemplos concretos

a. Número de hombres de más de 30 años

Dispone de una base de datos de 18 filas con los sexos y edades de diferentes personas (hoja Ej1). A partir de esta lista, quiere conocer el número de hombres de más de 40 años.

La fórmula debe constar de dos criterios unidos por un Y:

• El sexo debe ser igual a la celda E2.

• El sexo debe ser superior a la celda E3.

Por supuesto, en este caso es posible utilizar la función CONTAR.SI.CONJUNTO para llegar al resultado reducido, pero nuestra pequeña fórmula matricial le permitirá aprender las bases de esta técnica de cálculo.

 Coloque el cursor en la celda F5.

 Escriba la fórmula: =SUMA((B2:B18=F2)*(C2:C18>F3))

 Confirme pulsando CtrlMayúsIntro.

La fórmula pasa a ser: {=SUMA((B2:B18=F2)*(C2:C18>F3))}

Esto requiere algunas explicaciones:...

1. Interés

Las tablas que utiliza a menudo constan de datos repartidos en varias hojas de cálculo; por ejemplo, puede tener una hoja de cálculo por comercial, departamento, sector, familia de producto, etc. Cada hoja por supuesto está compuesta de datos y de fórmulas y, generalmente, todas estas hojas tienen la misma estructura.

Obtener resúmenes a partir de numerosas hojas puede convertirse enseguida en una tarea fastidiosa si no emplea las técnicas adecuadas. Siempre puede llegar a un resultado correcto, pero a menudo a costa de fórmulas pesadas y complicadas para actualizar los datos a medida que varían. Añadir nuevas hojas de cálculo que el cuadro resumen tenga en cuenta no debe hacer que las actualizaciones sean difíciles.

Una vez dominadas las funciones que abordamos, esta parte le permitirá aplicar técnicas de consolidación fiables.

2. Cuadros resumen multihojas

Como comentamos al principio del capítulo, en sus tablas debe dar prioridad a la vista de los totales en el encabezado de las tablas y no a pie de estas. Así no tendrá que descender x filas para ver las síntesis y si añade nuevas filas de datos, no afectará a la vista.

Este libro consta de nueve hojas de cálculo idénticas, cada una relativa a las ventas realizadas por una agencia de su empresa. Una décima hoja resumirá los datos del conjunto de agencias.

La hoja de las ventas de su agencia en Álava:

Todas las hojas tienen la misma estructura.

Este es el conjunto de pestañas del libro:

a. Sumar los datos de varias hojas mediante fórmulas

Nuestro primer trabajo consistirá en sumar los valores de las celdas D3 de cada hoja de las agencias.

Es imposible realizar esta suma manualmente añadiendo celdas en cada hoja. Con nueve hojas, aún podría ser posible, pero si el número de hojas por resumir se hace demasiado grande, la fórmula obtenida no estaría realmente optimizada.

La fórmula obtenida añadiendo los valores sería la siguiente:

=ALAVA!D3+ALBACETE!D3+ALICANTE!D3+BARCELONA!D3+BILBAO!D3+CACERES!D3+GRANADA!D3+SALAMANCA!D3+SEVILLA!D3

ALAVA!D3 indica a Excel que coja el contenido de la celda...