Atajo para agrupar y desagrupar datos de tablas dinámicas

Problema

Deseamos agrupar elementos de una tabla dinámica sin necesidad de usar los botones de la cinta de opciones.

Solución

Agrupar

Seleccionamos los elementos deseados y presionamoss: Mayús + Alt + Flecha derecha.

Si solamente seleccionamos un elemento de la tabla dinámica y es número, moneda o fecha aparecerán los siguientes menús desplegables:

Desagrupar

Seleccionamos los elementos deseados y presionamoss: Mayús + Alt + Flecha izquierda.

Estos atajos son especialmente efectivos para elementos contiguos. Mantenemos presionado el botón Mayús avanzando con el cursor para seleccionar los elementos deseados, y después tecleamos Alt más la flecha del cursor correspondiente.

Entradas relacionadas

• Abrir ficheros y aplicaciones con cuadro de diálogo e hipervínculo en Excel
• Abrir ficheros y aplicaciones con un cuadro de diálogo en Access

Aplicar una función a cada columna en R con dplyr

En esta entrada vamos a emplear la función summarise_each del paquete dplyr para aplicar una función a las columnas de una matriz o de un data frame. Es una alternativa a la función apply del paquete base que tratamos en una entrada anterior.

Problema

Por ejemplo, imaginemos que queremos calcular la media de las columnas del conjunto de datos trees, cargado por defecto en R. Tendríamos que repetir la misma instrucción para cada columna con el correspondiente nombre de la misma.

mean(trees$Girth)
mean(trees$Height)
mean(trees$Volume)

[1] 13.24839
[1] 76
[1] 30.17097

Solución

Podemos obtener el mismo resultado de manera más concisa, y sin necesidad de saber los nombres de las columnas, usando la función summarise_each.

La sintaxis de summarise_each_(tbl, funs, vars) es:

tbl – la matriz o data frame
funs – lista de funciones que utilizaremos
vars,... – variables a incluir o excluir en mutate/summarise

El operador %>% nos permite evitar paréntesis anidados y ganar en claridad. De esta manera, podemos leer las instrucciones de arriba a abajo y de izquierda a derecha, en lugar de leer desde el interior del paréntesis anidado hacia afuera. Este operador se conoce en inglés como pipe operator y dplyr lo ha adoptado del paquete magrittr. En RStudio podemos obtenerlo con el atajo: Ctrl+Mayús+M

library(dplyr) 
trees %>%
  summarise_each(funs(mean))

     Girth Height   Volume
1 13.24839     76 30.17097

Más ejemplos

Aunque el propósito de esta entrada no es explicar las principales funciones o verbos de dplyr para manipular datos, merece la pena ver algunos ejemplos más:

• Incluyendo solamente la columna Girth

trees %>%
  summarise_each(funs(mean), Girth)

  Height   Volume
1     76 30.17097

• Excluyendo la variagle Girth

trees %>%
  summarise_each(funs(mean), -Girth)

  Height   Volume
1     76 30.17097

• Filtrando las filas cuyo volumen sea superior a 30 pies cúbicos.

trees %>%
  filter(Volume > 30 )%>%
  summarise_each(funs(mean))

     Girth   Height Volume
1 16.43333 79.66667 47.175

Notas

En nuestro ejemplo no necesitaríamos utilizar la función summarise_each o apply, pues hay una función que específicamente calcula la media por columnas colMeans. Lo mismo en el caso de la función suma con la función colSums. Estas funciones son equivalentes a apply con FUN = mean o FUN = sum con MARGIN = 2, pero son mucho más rápidas.

colMeans(trees)

   Girth   Height   Volume 
13.24839 76.00000 30.17097 

colSums(trees)

 Girth Height Volume 
 410.7 2356.0  935.3 

La función summarise_each nos permite listar varias funciones.

trees %>%
  summarise_each(funs(mean, sum))

  Girth_mean Height_mean Volume_mean Girth_sum Height_sum Volume_sum
1   13.24839          76    30.17097     410.7       2356      935.3

Referencias:

• Aplicar una función a cada columna en R
• dplyr
• apply
• colSums

Normalizar tablas en Excel con el asistente para tablas dinámicas

Es frecuente encontrarse con tablas sin normalizar.

En este ejemplo, los resultados mensuales de 28 tiendas durante 3 años. En una tabla normalizada para cada registro único necesitaríamos la estructura siguiente con tres campos: Fecha, Tienda y Ventas.

Así tendríamos la tabla preparada para importar desde Access. Para evitar errores reordenando laboriosamente los datos podemos normalizar  la tabla con el Asistente para tablas y gráficos dinámicos.

1. Cambiamos los meses de texto a fechas en las tres tablas. En lugar de ENERO y FEBRERO en  la tabla 2010, tecleamos 01/01/2010 y 01/02/2010 respectivamente. Seleccionamos esas dos celdas y arrastramos el controlador de relleno hasta diciembre. O sustituimos ENERO por 01/01/2010, arrastramos el controlador de relleno y en Opciones de autorelleno elegimos Rellenar meses. Y así sucesivamente en las dos tablas restantes.

Las fechas nos permitirán distinguir los meses de distintos años, con independencia de que luego necesitemos agrupar la tabla dinámica por meses.

2. Abrimos el Asistente para tablas y gráficos dinámicos. A partir de Excel 2007, para invocarlo es necesario teclear la secuencia ALT+T+B (ALT+D+P si el idioma local es inglés) o pulsar sobre el icono agregado a la barra de herramientas de accesso rápido.

3.Seleccionamos Rangos de consolidación múltiples

3. Seleccionamos Campos de página personalizados

4.Seleccionamos el rango (1) y presionamos sobre Agregar (2) tantas veces como rangos añadamos. Finalmente hacemos clic sobre Siguiente (3) o Finalizar, si queremos la tabla dinámica en otra hoja.

5.En el panel Lista de campos de tabla dinámica, en la sección Seleccionar campos para agregar al informe desactivamos la casilla de verificación de los campos Fila y Columna.

6. Doble clic sobre la única celda visible del área valores.

7. Se creará una nueva Hoja donde podremos ver la tabla normalizada.

Cambiaremos los encabezados a Fecha, Tienda y Ventas respectivamente, y el formato de las celdas si fuera necesario. Al importar a Access podremos agregar una clave principal. Por defecto es un campo autonumérico, que rellenará automáticamente con valores de identificador únicos, comenzando por 1.

Entradas relacionadas:

1. Agrupar tablas dinámicas por meses
2. Normalización y claves

Agrupar tablas dinámicas por meses

En una tabla dinámica, al agrupar las fechas por meses obtenemos el siguiente resultado.

El problema es que al añadir un registro del año siguiente, por ejemplo de enero, aparecerá al principio de la tabla dinámica:

Para solucionarlo, pinchamos con el botón derecho sobre la tabla y en Agrupar seleccionamos Años además de Meses. También debemos hacer lo mismo si no deseamos que nos agrupe meses de distintos años.