Más sobre tablas dinamicas

Vamos a continuar con las tablas dinámicas. Recuperamos los datos de la entrada anterior, sobre los que vamos a seguir trabajando.

Si realizamos los pasos de la entrada anterior la tabla obtenida era:

Vamos a avanzar un poco más. Sobre la tabla vamos a limitar el número de países que aparecen en la tabla:

Al pinchar en la flecha de los rótulos de fila aparece un menú con la relación de países. Si deseamos limitar la tabla a menos países procederemos de la siguiente forma:

• 
  
  Pinchamos en la marca de Seleccionar todo. Así desactivamos todos los países.
• 
  
  Ahora pinchamos solo en la marca de los países que interesan que aparezcan en la tabla
• 
  
  Por ejemplo, activaremos a EEUU, Italia y Méjico.
• 
  
  Pulsamos en aceptar.

El resultado será el siguiente:

 Como vemos,  ahora tenemos los países que nos interesaban. Además, la cifra del Total general se ha adecuado a solo los países mostrados. Si queremos recuperar a todos los países volveremos a pulsar en rótulos de fila y hacer clic en la marca de Seleccionar todos. Recuperamos a todos los países.

Ahora vamos a modificar la disposición de la información de forma que lo mismo se muestra de forma distinta. Vamos a mover Origen de Rotulos de fila a rotulos de columna, simplemente arrastrando el campo:

El resultado es el siguiente:

Mismos datos organizados de otra manera. Hay totales para cada fila y para cada columna. Se pueden activar o desactivar a nuestro gusto. Vamos a diseño, totales generales y seleccionamos la opción que nos interese:

Activando la última opción la tabla queda así:

Los totales de las distintas herramientas han desaparecido.

Seguiremos trabajando las tablas dinámicas en posteriores entradas.

Tablas dinámicas

Vamos a construir una tabla dinámica a partir de una serie de datos. Veremos los pasos que nos permitirán ver una tabla dinámica. Lo vamos a realizar usando Excel 2007. Ahora trabajaremos un poco diferente a versiones anteriores de Excel. Ahora no usaremos un a asistente. Iremos a insertar donde podremos usar las herramientas propias de la tabla dinámica.

En primer lugar podemos partir de una tabla dentro de Excel. Para este post usaremos la siguiente:

Seleccionamos toda la tabla incluyendo los títulos de la fila 1. Pulsamos en insertar y en tabla dinámica pinchamos y seleccionamos la primera opción tal como aparece en la imagen:

Aparece la ventana Crear tabla dinámica. Como tenemos seleccionados los datos sobre los que vamos a construir nuestra tabla con los mismos. En Elija dónde desea colocar el informe de tabla dinámica seleccionamos Hoja de cálculo existente y marcamos como ubicación A19.

El resultado que se obtiene es el siguiente:

Para construir la tabla tenemos que trabajar con la ventana Lista de campos de tabla dinámica que también ha  aparecido junto con el área anterior.

Para crear la tabla trabajaremos con la lista de campos. Para crear la primera tabla arrastramos los campos Nombre y origen a la zona de Rótulos de fila. El orden en el que los coloquemos va a hacer que el resultado sea distinto. En este caso, hemos colocado antes origen y después Nombre. Y por último trasladamos unidades a Suma de valores. En los tres casos, los nombres de los campos respectivos quedan marcados en la parte superior de la lista de campos.

Con los cambios comentados el resultado obtenido es el siguiente:

De esta forma tenemos generada la tabla dinámica. Cualquier modificación sobre la misma se puede realizar rápidamente añadiendo campos a los distintos rótulos.

Más cosas que podemos hacer en una tabla dinámica es obtener el detalle de un dato concreto de la tabla. Por ejemplo, si hacemos doble clic en el dato ubicado en la celda B31, obtendremos las filas de la tabla que dan lugar a ese dato, correspondiente a Gran Bretaña. El resultado que obtenemos ahora es:

que se ubica en una hoja nueva que se crea al hacer doble clic sobre la celda.

Con una tabla dinámica podemos organizar los datos de varias formas, obteniendo distintos totales, dependiendo de como se coloquen los datos 

Formato ejes de coordenadas en gráficos

Vamos a modificar un poco la visualización de los gráficos. Se puede modificar a nuestro gusto el color de los valores que se representan en el eje de coordenadas. Permite que los gráficos nos puedan aportar una información más clara que nos ayuda a captar mejor los resultados de una encuesta, o datos estadísticos que pudiéramos representar.

Partimos de una serie de datos, tal como los que se muestran:

A partir de estos datos vamos a obtener un gráfico de barras 3D con el asistente de gráficos. El resultado es:

Al existir valores negativos, las barras correspondientes salen del nivel 0 hacia abajo. Vamos a hacer que en el eje de coordenadas los valores inferiores a 0 aparezcan en color rojo. Seguimos los siguientes pasos:

Pinchamos con el botón dcho. en los valores del eje de coordenadas donde seleccionamos Formato de ejes:

En ventana que aparece seleccionamos la pestaña número, en categoría seleccionamos Personalizada y en tipo seleccionamos la entrada marcada en la imagen. Como se ve, consta de dos partes separadas por un punto y coma:

• La primera parte hace referencia a los valores positivos
• La segunda parte hace referencia a los números negativos, además aparece entre corchetes la palabra Rojo que hace que valores negativos aparezcan en color rojo.

Con esa opción marcada pulsamos en aceptar. El resultado es el mostrado a continuación:

Como vemos, el valor -5 del eje de coordenadas es rojo por ser menor que cero. También podemos modificar la escala para que aparezcan más valores en el eje de coordenadas:

En la pestaña Escala modificamos el valor de unidad mayor, escribimos 2 y pulsamos en aceptar. De esta forma el gráfico sería el siguiente:

Ahora el eje de coordenadas presenta mas valores y que van de dos en dos comenzando en -4 hasta 24. Ahora tenemos más una escala que muestra más pasos por debajo de cero. Deshacemos el cambio para seguir como antes.

Ahora nos interesa que los valores superiores a 15 aparezcan en azul. Y que sigan en rojo los valores negativos. Volvemos a formato de ejes en la pestaña de Número y en Personalizada. Seleccionamos la misma opción que antes y en tipo la modificamos de la siguiente forma:

[Azul][>15]#.##0 _€;[Rojo][<0]-#.##0 _€

Se hace de esta forma, así marcamos que zonas van a tener el correspondiente color. Con esta opción el gráfico queda así:

Cada color va entre corchetes, y ponemos entre corchetes los límites de los valores que van a tener el correspondiente color (véase >15 y <0 en nuestro ejemplo para el azul y el rojo respectivamente).

Siempre podremos actuar sobre las propiedades de un gráfico para modificarlo a nuestro gusto. Se pueden alterar para destacar una parte del gráfico que nos pueda interesar.

Localizar celdas iguales con el formato condicional

A partir de un gran conjunto de datos pretendemos ver aquellos que se repiten un número de veces determinado. Haremos uso del formato condicional para buscar datos duplicados que pudiera contener una lista larga. Vamos a ver como podemos hacerlo.

Vamos a partir de una serie de datos introducidos en el rango A1:F17. Los datos son:

 Como se observa, existen datos variados, algunos de ellos se repiten varias veces, otros se repiten una o dos veces y otros no se repiten. Vamos a averiguar cuales se repiten. En primer lugar, vamos a ver y destacar los que se repiten mas de tres veces. Usaremos el formato condicional.

Pasos

• Seleccionaremos todo el rango de datos, A1:F17. Nos cuidaremos de tener la celda A1 activa.
• Iremos a Formato / Formato condicional

• Sustituimos valor de la celda por Fórmula en el correspondiente menú que nos permitirá escribir la siguiente fórmula, tal como muestra la imagen, =contar.si($A$1:$F$17;A1)>=3

• Ahora pulsaremos en el botón formato para modificar el aspecto que tendrán las celdas que cumplan el criterio que acabamos de escribir. Activamos la pestaña tramas y seleccionamos el color:

• Pulsamos en aceptar, y volvemos a pulsar en aceptar para ver el resultado:

Los valores 89, 23 y la cadena de texto hola aparecen al menos tres veces. El resto de datos dentro del rango considerado no cumplen la condición y por tanto no presentan el formato que hemos aplicado a los datos que aparecen tres o más veces.

Si queremos saber que datos se repiten dos veces, entonces volvemos a aplicar formato condicional. Procedemos de la siguiente forma:

Seleccionamos todo el rango, A1:F17, vamos a Formato / Formato condicional... y vemos que aparece la primera condición. Pulsamos en el botón agregar para añadir la nueva condición. Tecleamos la nueva condición, =contar.si($A$1:$F$17;A1)=2, de la siguiente forma:

Así, los datos que aparecen en solo dos celdas tendrán otro color que nos permitirán visualizarlos pulsando en aceptar dos veces:

Podríamos añadir una condición más con el botón agregar del cuadro de dialogo Formato condicional, con el que hacer destacar otros datos que estén ubicados en el rango.

Si pulsamos en una celda dentro del rango considerado, por ejemplo C5, y vamos a formato / Formato condicional, observamos lo siguiente:

La referencia a la celda A1 ha cambiado a C5 en ambas condiciones. Esto es así debido  a que el formato condicional lo hemos aplicado al todo el rango y que la referencia a la celda A1 no está fijada, es una referencia relativa. Esto permite que en el resto de celdas la referencia relativa permite que haga referencia a la celda correspondiente.

Concluyendo, el formato condicional permite visualizar o detectar datos incorporados repetidos en un amplio rango de celdas.

Añadir una serie nueva a un gráfico

En ciertas ocasiones es interesante añadir más datos a un gráfico ya realizado. Esta entrada va a tratar de mostrarlo usando la versión 2007 de Excel. Para realizar un gráfico seleccionamos los datos y podemos realizarlo seleccionando el tipo de gráfico, por ejemplo de columnas, de la forma que muestra la siguiente imagen:

Suponemos que tenemos los siguientes datos en una hoja:

A partir de ellos obtenemos un gráfico 3D pulsando en insertar, columna, Columna en 3D:

que nos permite obtener:

Ahora que ya tenemos el gráfico de partida, vamos a añadir una nueva serie de datos. Imaginemos que tenemos datos del producto B y los deseamos añadir a este gráfico.

En primer lugar, escribimos los nuevos datos junto a los del producto A:

Ahora tenemos un gráfico, y nuevos datos que deseamos incorporar al gráfico. Para hacerlo, pulsamos con el botón derecho en cualquier barra del gráfico. Del menú contextual seleccionamos Seleccionar datos...:

que permite abrir un cuadro de dialogo:

Aquí pulsamos en el botón agregar que abre otro cuadro de dialogo:

En nombre de la serie escribimos la cabecera de la serie que se incorpora, y en valores de la serie, seleccionamos los datos que queremos añadir. Pulsamos en el botón que está encima del botón Aceptar. Después seleccionamos los datos numéricos, y para regresar a la ventana anterior pulsamos donde se encuentra la flecha:

Pulsamos en aceptar, que nos devuelve a la ventana:

Volvemos a pulsar en aceptar, que nos permite ver a la nueva serie ya incorporada al gráfico, que es lo que queríamos:

Solo nos queda el detalle del título del gráfico. Se lo añadimos. Con el gráfico activo, en Herramientas de gráfico, Presentación seleccionamos Título del gráfico, Encima del gráfico:

Aparece en el gráfico un cuadro de texto donde tecleamos el nuevo título:

Como vemos, ya tenemos el título, también vemos que aparece en la leyenda el reflejo de la serie recién incorporada, "Ventas del producto B".

En resumen, en pocos pasos podemos añadir nuevas series a graficos que permite enriquecer la información que deseamos mostrar. Herramienta útil que podemos utilizar siempre que nos haga falta.

Unir celdas en Excel

En un post anterior describíamos como colocar el contenido de varias celdas en otra con la función concatenar. También lo podemos hacer sin el uso de una función, en este caso tenemos a nuestra disposición el carácter &, con el que podremos juntar varias celdas en una sola.

Vamos a ver como se realiza. Queremos colocar en la celda D6 el contenido de las celdas B5 y B7:

Ahora en la celda donde queremos el resultado, tecleamos el signo =, hacemos clic en la celda B5 y ahora vamos a colocar un espacio para que no aparezcan juntos el contenido de ambas celdas. Colocamos el signo &, abrimos comillas, pulsamos la barra espaciadora y cerramos comillas. Colocamos otro & y pulsamos en la celda B7 como podemos observar en la siguiente imagen:

 

El resultado es el que buscamos:

Vamos a ver otro ejemplo, vamos a unir el contendido de la celda B7 con una cadena de texto que va a ser el carácter % seguido de un espacio. Lo hacemos de esta forma:

Con lo que el resultado es:

Resumiendo, es sencillo unir varias celdas en solo una. Usaremos el símbolo &, que nos permite ir uniendo varias celdas en una. Podemos unir el contenido de cuantas nos interesen, cada vez que se coloque una nueva pondremos el símbolo &, sin olvidarnos del uso de comillas si queremos añadir también una cadena de texto. 

Ocultar el contenido de una celda

Muchos trabajos que tenemos que realizar con Excel exigen la tarea de usar múltiples funciones que nos dan unos resultados. Normalmente esos resultados son los que buscamos. Pero, a veces, los datos que obtenemos son datos intermedios que hacen falta para calcular otros datos. Puede ser interesante o conveniente que esos datos no estén visibles. Entonces Excel nos permite ocultar esos datos al usuario. Tendremos que personalizar el formato de la celda o celdas que queremos ocultar.

Vamos a verlo en un ejemplo donde ocultaremos el contenido de la celda C4. Escribimos un dato en la misma:

Como C4 es la celda activa, su contenido se muestra también en la barra de fórmulas. Ahora nos colocamos en C4 y pinchamos el botón derecho del ratón. Se muestra el correspondiente menú contextual donde seleccionaremos Formato de celdas...

Se abre la ventana Formato de celdas: En el panel Categorías seleccionamos Personalizada, que está al final del panel.

En tipo borramos estándar y escribimos tres veces punto y coma:

Pulsamos en Aceptar y hemos ocultado el contenido de la celda, como muestra la siguiente imagen:

Si queremos que se vuelva a ver, con la celda seleccionada, volvemos a formato / celdas y borramos los 3 puntos y comas. Pulsamos en aceptar y ya está.

También podíamos haberlo hecho cambiando el color del texto. Lo ponemos con el mismo color de fondo de la celda y así se oculta lo que hemos escrito en la misma.