sábado, 31 de julio de 2010

Agrupar datos en tablas dinámicas

Seguimos con la serie de datos que estamos trabajando en la entrada anterior. Recordando la lista de datos es:


Vamos a construir una nueva tabla dinámica como sigue:

Una vez que insertamos la tabla, marcamos los campos que nos interesa para la tabla: Origen, nombre y unidades. El resultado es:



Lo que nos interesa hacer ahora es unir países por continentes. Agruparemos por un lado a EEUU y Méjico y por el otro al resto de países.  En la propia tabla hacemos clic sobre EEUU (ubicada en la celda A7) con la tecla CTRL pulsada hacemos clic en A20 (Méjico). Vamos a Herramientas de tabla dinámica /Opciones y pulsamos en Agrupar selección:


Automáticamente aparecen EEUU y Méjico bajo la denomínación de Grupo1:



Podemos cambiar su nombre, nos posicionamos en la celda donde está Grupo1 y en la barra de fórmulas escribimos América. Seleccionamos después los países europeos con la tecla ctrl y pinchando en la denominación que tiene algo de sangría en la celda.


Ahora cambiamos grupo2 por Europa del mismo modo con lo cual la tabla queda así:



Si queremos ver solo las unidades de cada continente desactivaremos en Lista de campos de la tabla dinámica sobre el primer panel los campos nombre y origen:


Al establecer las agrupaciones hemos observado que tenemos un nuevo campo, Origen2 que agrupa a Europa y a América, y que permitirá mostrarlo en la tabla con el resto de campos.

jueves, 29 de julio de 2010

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.

miércoles, 28 de julio de 2010

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 

viernes, 23 de julio de 2010

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.

jueves, 22 de julio de 2010

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.

viernes, 16 de julio de 2010

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.

Actualizar tablas en Excel

En el siguiente ejercicio vamos a ver como actualizar tablas en excel. Nos centraremos en modificar unos precios con código VBA. Modificare...