Unidad 21: Resumir los datos 21.0 Introducción Las tablas grandes (y las no tan grandes) de números y textos de Excel pueden parecer un gran reto. Pero Excel se encuentra en su elemento cuando hace descripciones sencillas a partir de ellas, o pone el contenido en categorías, resumiendo las categorías y haciendo que los resultados tengan sentido. Esta unidad trata de cómo utilizar Excel para poder entender tales tablas de datos.

21.1 Características de un conjunto de datos Describir todos los datos Es normal tener que resumir un conjunto de números de forma muy concisa – incluso con un solo número. Hay un conjunto pequeño de funciones de Excel que hacen esto en todas las celdas que se han especificado como sus entradas:      

SUMA CONTAR CONTARA PROMEDIO MAX MIN

El uso de CONTAR, CONTARA y SUMA se ha tratado en la Unidad 20 que trataba de las funciones matemáticas. Trataremos aquí de las restantes.

PROMEDIO La función PROMEDIO calcula el promedio1 de todas las celdas que contengan números especificados con sus entradas. El PROMEDIO(A1:B2) es equivalente a la SUMA(A1:B2)/CONTAR(A1:B2). Ejemplo

La fórmula =PROMEDIO(G1:H4,J1)

1

Técnicamente, calcula la media de los números (más que el promedio modal o el de la mediana).

© 2010 Excel with Business

1

Unidad 21: Resumir los datos

calcula el promedio de los resultados de todas las celdas en el campo G1:H4 y de la celda J1 Lo porción de los números contenidos en la definición es importante – si tiene datos en los que una celda vacía significa que la cantidad es cero (por ejemplo en la base de datos de las ventas, una celda en blanco podría significar que no se han realizado ventas durante un cierto periodo), la función PROMEDIO producirá un resultado equivocado ya que esa celda la ignorará en vez de contarla como cero. Recomendamos que la función PROMEDIO se use después de considerarlo bien (y pocas veces). Por la razón mencionada arriba y porque es normalmente más evidente lo que ocurre si el cálculo se divide entre SUMA/CONTAR.

MAX o MIN Estas dos funciones funcionan exactamente del mismo modo que las anteriores, pero dan el máximo (o el menor) valor de la celda en las celdas especificadas como entradas. Son útiles para comprobar que todos sus datos caen dentro de un rango esperado.

Barra de Estado La Barra de Estado de Excel (vea la Unidad 1: El Panel de Control de Excel) registra tales características sencillas en todas las celdas seleccionadas – su valor de promedio; el número de las celdas seleccionadas con contenidos y el número total (SUMA). En este ejemplo, se han seleccionadas todas las celdas en el rectángulo oscuro y los resultados aparecen en la Barra de Estado en la base de la captura de pantalla.

© 2010 Excel with Business

2

Unidad 21: Resumir los datos

Puede alterar esta configuración por defecto – por ejemplo, hacerlo de modo que registre el Máximo de celdas seleccionadas o que deje de mostrar una de las características por defecto – solo haga clic con el botón derecho del ratón en la Barra de Estado para personalizarlo de esta forma:

Cuando use el Cuenta de la Barra de Estado, tenga cuidado. Al igual que la función CONTARA (y no, como la función CONTAR), la opción cuenta en la Barra de Estado incluye celdas que no se han formateado como números. Si quiere excluir las celdas formateadas como texto de la cuenta de la Barra de Estado, debe especificar la cuenta numérica.

21.2 Cuenta condicional Hasta ahora en esta unidad, hemos usado Excel para describir el conjunto de datos como un todo. Ahora vamos a centrarnos en cómo describir categorías dentro de los datos.

© 2010 Excel with Business

3

Unidad 21: Resumir los datos

Ejemplo

Un ejemplo de la clase de base de datos que nos interesa es la que aparece en esta hoja de cálculo:

´Si quisiéramos saber cuántos empleados hay en cada equipo, si la tabla fuese pequeña, se podría contar manualmente. Si fuese algo más grande, se podrían ordenar las filas por nombre del equipo (vea la Unidad 13: Ordenar y Filtrar) y facilitaría un poco el recuento manual; pero si la base de datos fuese muy grande o se actualizase con regularidad, el método manual demandaría mucho tiempo. En su lugar, hay una función que se puede usar para contar las celdas (como lo hace CONTAR), pero solo si sus contenidos son algo en particular. Esta función se llama CONTAR.SI.

En la hoja de cálculo que aparece arriba, si se especifica =CONTAR.SI(B2:B12,"Estrategia") cogerá el campo B2:B12 (la primera entrada) y contará cuántas celdas exactamente tienen la entrada Estrategia. El resultado será 3. Excel llama al conjunto de celdas que se cuentan (en este ejemplo, B2:B12), el “rango” y al texto que selecciona cuáles de las celdas se van a contar (en este ejemplo, “Estrategia”), el “criterio”. Cosas a tener en cuenta si se usa CONTAR.SI de esta manera:

© 2010 Excel with Business

4

Unidad 21: Resumir los datos

Los datos que desea contar deben introducirse como la segunda entrada y entre comillas (en el ejemplo de arriba, “Estrategia”). 

  



El contenido completo de la celda debe coincidir con esa serie para que sea contada - así que, por ejemplo, si en el ejemplo de antes hubiese habido una celda que dijese Director de Estrategia, esa no se habría contado. La coincidencia no distingue entre mayúsculas y minúsculas – si en una celda hubiese dicho “estrategia”, habría sido contada. Si está contando números, puede no poner las comillas. Sería un buen hábito el entrar para establecer la referencia del “rango” como absoluta (vea la Unidad 11 sobre las Referencias de las Celdas). Así que, en el ejemplo anterior, se debería haber introducido el rango como $B$2:$B$12 Cuente las celdas en blanco introduciendo "" como segundo argumento: CONTAR.SI(C7:C14,"")

21.3 Contar categorías CONTAR.SI es mucho más poderosa si el “criterio” se mantiene en una celda en lugar de introducir el texto como “Strategy”- ya que permite contra muchas categorías diferentes en el conjunto de datos. Como siempre, esto se ve mejor con un ejemplo: Ejemplo

Volviendo a nuestro ejemplo en la sección anterior, hemos reemplazado “Estrategia” en la formula por un enlace a la celda D3 que contiene la palabra Estrategia.

El resultado de esta fórmula es exactamente el mismo que si hubiésemos escrito “Estratagio” en vez de D3 – ahora podemos copiar la celda E3 extendiéndola a E4, E5 y E6 y la función CONTAR funcionará también con los otros Nombres del equipo:

© 2010 Excel with Business

5

Unidad 21: Resumir los datos

Ahora es aparente porqué fue una buena idea establecer la referencia del “rango” – de modo que cuando se copie la formula, el rango no se cambie y solo cambie la categoría que se cuenta. El uso de esta técnica permite la creación rápida de resúmenes sencillos a partir de grandes hojas de cálculo.

Consejo Use Datos > Ordenar y Filtrar > Avanzado para producir una lista única (vea la Unidad 13: Ordenar y Filtrar) de categorías a contar.

Uso avanzado Puede combinar columnas (ej. entrar en la celda C1 =A1&B1) para producir categorías combinadas. Por ejemplo, podría combinar las columnas que contengan sabores de refrescos y tamaños de paquetes para producir entradas como COLABOTELLA o COLALATA. Esto construye categorías más detalladas para las que se puede usar CONTAR.SI. Las Tablas Dinámicas (ver Unidad 22: Tablas Dinámicas) aquí podrían ser la solución.

21.4 Sumar las categorías De la misma forma que la función CONTAR.SI permite contar por categoría, SUMAR.SI añade números por categoría. Ejemplo

Esta hoja de cálculo amplía nuestro ejemplo de la base de datos para incluir el número de días de permiso que lleva cada empleado. En este caso, en vez de contar filas de la base de datos por cada equipo, como hicimos con el ejemplo con CONTAR.SI, vamos a sumar los días de permiso de cada equipo.

© 2010 Excel with Business

6

Unidad 21: Resumir los datos

SUMAR.SI requiere un rango extra como su tercera entrada (en la hoja de cálculo de arriba está destacado en púrpura). Este rango extra contiene valores que deben sumarse. Normalmente, la sintaxis de SUMAR.SI, entonces, es: SUMAR.SI(,,)

© 2010 Excel with Business

7

Unidad 21: Resumir los datos