Mantener siempre actualizada una Tabla Dinámica en Excel (2)

Hace más de cinco años, en este mismo blog, escribí un artículo donde explicaba la forma de "Mantener actualizada constantemente una tabla dinámica". En esa ocasión, el artículo estaba referido a que la tabla refleje constantemente los datos de la tabla origen, pero se asumía que el rango de datos no cambiaba de tamaño. Pero, ¿qué ocurre si el rango de datos de la tabla - origen de datos crece o decrece?. Esta es una pregunta que me han hecho muchas veces últimamente y, además, desde varios ámbitos (empresarial y académico). Así pues, aquí propongo un par de soluciones para este problema, pero para casos distintos.

CASO 1: la tabla origen de datos puede tener formato de Tabla de Excel.

Si la tabla donde están los datos de origen puede tener formato de Tabla de Excel y esto no causa ningún inconveniente, el problema ya no lo es, puesto que tan solo aplicando ese formato a la tabla-origen (y haciendo que nuestra tabla dinámica está basada en ella), la tabla dinámica siempre tendrá como origen de datos la totalidad de datos, aunque hayan crecido o disminuido.

Para aplicar un formato de Tabla de Excel a un rango de celdas donde hay datos, tenemos que asegurarnos de que no hay celdas combinadas y de que cada columna tiene un título de encabezado. Después, desde la ficha Inicio, hacemos clic en "Dar formato como tabla":

Ahora, veremos que aparece una galería de estilos donde tenemos que elegir el que más nos guste. Posteriormente, debemos confirmar los datos del cuadro de diálogo que aparece... y .... ya está.

Por último, sería recomendable dar un nombre a la nueva Tabla de Excel. Lo podemos hacer desde la ficha Diseño (la ficha contextual que aparece cuando tenemos seleccionada una celda de la nueva Tabla). Podemos ver a la izquierda un campo para editar el nombre de la Tabla de Excel.

Una vez hecho todo esto, debemos tener en cuenta que...

• Si ya teníamos la tabla dinámica construida y basada en el antiguo rango de datos, debemos decirle a Excel ahora que la tabla dinámica debe basarse en nuestra nueva Tabla de Excel. Para ello, debemos ir a la ficha Analizar (teniendo alguna celda de la tabla dinámica seleccionada), hacer clic en "Cambiar origen de datos..." y seleccionar la nueva tabla completamente.

 

• Si tenemos que construir "desde cero" nuestra tabla dinámica, tan solo tenemos que hacerlo normalmente, seleccionando como origen de datos nuestra Tabla de Excel.

CASO 2: la tabla origen de datos NO puede tener formato de Tabla de Excel.

Si nuestra tabla - origen de datos no puede tener formato de Excel (por cualquier motivo), la solución debe ser radicalmente distinta y debemos recurrir a las macros. Aunque... ¡tranquilos!, tan solo son unas líneas de código.

El proceso sería el siguiente:

• Hacemos clic con el botón derecho en la pestaña de la hoja donde está la tabla dinámica que debe estar actualizada constantemente. Acto seguido, se abrirá el editor de VBA con una ventana grande a la derecha. Ahí debemos escribir lo siguiente:

       Private Sub Worksheet_Activate()
          ActiveSheet.PivotTables("MiTD").ChangePivotCache _
          ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
          SourceData:=Sheets("MiHoja").Range("B8").CurrentRegion)
      End Sub

      Donde MiTD debe ser sustituido por el nombre de la tabla dinámcia (el nombre de una TD lo podemos ver si tenemos una celda de la misma seleccionada, hacemos clic en la ficha Analizar y miramos a la izquierda).

     Donde MiHoja debe ser sustituido por el nombre de la hoja donde está la tabla - origen de datos.

      Y donde B8 es un ejemplo, pero cada usuario debe escribir ahí la referencia de la primera celda de la tabla - origen de datos (por ejemplo, el primer título de los encabezados). Una condición a tener en cuenta es que la tabla - origen de datos no debe tener filas completas vacías.

•  Cerramos el editor de VBA, cerramos Excel y aceptamos Guardar los cambios.

Y ya lo tenemos.

La clave de este código la expresión "CurrentRegion", que es la que, de forma automática, detecta cuál es el rango de datos de la tabla, siempre que la referencia de ejemplo B8 sea una celda de la tabla. Por otro lado, comentar que el código se ha introducido en el evento WorkSheet_Activate, por lo tanto, la actualización del rango se realiza cada vez que seleccionamos la hoja de la tabla dinámica.


José Manuel Pomares Medrano

Agrupar tablas dinámicas por número de semana

Cualquier usuario de Excel que utilice frecuentemente tablas dinámcias sabe que es posible agrupar la información que estas ofrecen por periodos (minutos, horas, días, meses, trimestres, años).

Aunque de forma indirecta, también podemos agrupar los datos de una tabla dinámica por el nº de semana correspondiente. Esto es algo que, según he comprobado con mi experienccia al impartir clases de Excel a empresa, es más frecuente de lo que podamos pensar y... este será el tema de este post.

Como decíamos, es posible realizar este tipo de agrupación de forma indirecta. Es decir, creando previamente en la tabla de datos original un campo (columna) cuyas celdas contengan una función que nos calcule el número de semana.

Dicho gráficamente, tenemos que conseguir que el campo FECHA de nuestra tabla...

...tenga un campo compañero con el número de semana calculado....

Esto se puede conseguir si introducimos en cada celda de la tabla la fórmula...

... donde podemos ver concatenados mediante el símbolo ampersand (&):

• La palabra "Semana " (con espacio al final y entre comillas)
• La función NUM.DE.SEMANA que tiene dos argumentos separados por punto y coma:
• En el primero de ellos debemos hacer referencia a la celda que tiene la fecha (la celda de la columna FECHA).
• En el segundo argumento debemos escribir un código que determinará el sistema de numeración de semana que deseamos. Por ejmplo, el Sistema 2 que se ajusta a la norma ISO europea necesita el código 21 (como vemos en la imagen anterior). Si deseamos otro sistema de numeración de semana, para conocerlo debemos acudir a la página de ayuda de la función NUM.DE.SEMANA de Microsoft.

Una vez llegados aquí, en nuestra tabla dinámica solo tenemos que:

• Actualizar (Alt+F5), para que el campo esté disponible en la lista de campos
• Insertar el campo "SEMANA" (arrastrándolo, normalmente al cuadrante "Filas")

José Manuel Pomares Medrano

Las 10 ventajas de hacer una Tabla de Excel

No quería dejar en el olvido la promesa que hice en el artículo "La magia y productividad de las Tablas en Excel". Esta promesa no era otra que hacer un post donde se resumieran las principales características diferenciales de lo que Excel llama "Tabla". Recomendamos encarecidamente leer ese artículo como "aperitivo". En él, ya indicábamos cómo hacer una Tabla de Excel y hacíamos una breve reseña de alguna característica y ventaja.

Vamos ahora a  resumir las 10 que consideramos más importantes, pero antes que nada, es necesario que el lector tenga configuradas de forma correcta algunas opciones de Excel. Para ello, vamos a las Opciones de Excel  / Revisión / Opciones de autocorrección  y marcamos todas las casillas de la ficha "Autoformato mientras se escribe".

Recordamos que a las Opciones de Excel, se accede en Excel 2007 mediante el botón de Office grande que hay arriba a la izquierda y en Excel 2010 mediante la ficha Archivo.

Arrancamos...

1. Formatos y estilos visuales automáticos con dos clicks

Para dar formato como "Tabla de Excel" a una región de datos en forma de tabla, podemos hacerlo situándonos en cualquier celda de esa región

 ...y desde la ficha Inicio / Estilos / Dar formato como Tabla . ...

...seleccionamos el estilo que más nos guste ...

... y después aceptamos el rango propuesto:

2. Retorno automático y agrega fila automáticamente

En la última fila de la Tabla, cuando llegamos al último campo de la derecha, si presionamos la tecla TAB, Excel detecta automáticamente que lo lógico es seguir escribiendo en una nueva fila y sitúa el cursor en la fila de abajo y en el primer campo de la tabla.

Como extensión de esta ventaja, Excel acoge dentro del rango* de la Tabla, la fila donde se sitúa el cursor después de haber realizado un "retorno automático".

*Recordemos en el artículo "La magia y productividad de las Tablas en Excel" que el rango de una Tabla queda reconocido como una "entidad" especial en Excel.

3. Autorrellenado de fórmulas en columnas

Cualquier usuario iniciado en Excel sabe que si ponemos una fórmula en una celda, tenemos la posibilidad de  "arrastrarla" o utilizar la herramienta de "autorrelleno" para que se extienda a lo largo de varias celdas. En las Tablas de Excel, si ponemos una fórmula en cualquier celda de una columna, ésta se extenderá a toda la columna de forma automática. Esto es una venta enorme, especialmente si trabajamos con tablas que contienen muchos registros, pues nos ahorra mucho tiempo.

4. Autoselección de filas, columnas y de la tabla entera

De forma similar al "Autorrellenado" de formulas, si queremos seleccionar toda una columna de datos contenidos en una Tabla de Excel, no es necesario hacerlo de forma manual arrastrando la selección a lo largo de todo el rango de la columna. Basta con situarnos encima del borde superior de la columna y vemos que aparece una flecha.

En ese momento, si hacemos 1 click, quedarán seleccionados TODOS LOS DATOS DE LA COLUMNA. Si hacemos un segundo click, quedará seleccionada TODA LA COLUMNA (incluido el encabezado). Todo lo dicho anteriormente es aplicable a filas (situando el cursor en el borde izquierdo de cualquier fila) y para la Tabla entera (poniendo el cursor en la esquina superior izquierda).

5. Copiar y pegar respeta el formato de relleno de celda

Todos sabemos que si copiamos una o varias celdas y las pegamos en otro lugar, el formato del relleno de las celdas, también se copia y pega cambiando el aspecto de las celdas de destino (a no ser que utilicemos "Pegar valores"). Pues bien, en las Tablas de Excel, podemos copiar en otro lugar y pegar encima de la Tabla y el fondo siempre será el originalmente establecido. Es necesario saber que, si proporcionamos un color de relleno desde el botón a tal efecto (icono del bote de pintura), este prevalecerá sobre el formato nativo de la Tabla.


6. Titulos inteligentes ante el scroll de pantalla

Si hacemos un scroll de pantalla hacia abajo con el ratón o con la barra de desplazamiento vertical y estamos dentro de una Tabla de Excel, los encabezados de ésta no desaparecen, sino que los tendremos disponibles a la altura de los títulos de las columnas. Quizá se entienda mejor viéndolo:

Vemos que los títulos de columna B, C, D y E  han desaparecido siendo sustituidos por los encabezados de la Tabla. De esta forma siempre los tenemos disponibles, aunque necesitemos ver la parte baja de una Tabla.


7. Movimiento inteligente de columnas

Las columnas de una Tabla de Excel son "entidades" u objetos que se pueden cambiar de orden arrastrándolas simplemente. Para ello, si leemos la ventaja nº 4, recordaremos que haciendo 2 clicks en el borde de una columna, queda TODA LA COLUMNA (encabezados incluidos) SELECCIONADA. Pues bien, una vez hecho esto, podemos hacer click en un borde de la selección de la columna y arrastrarla hacia otra posición:

8. Agrega automáticamente columna

Si queremos añadir una columna a la derecha de una Tabla y que quede acogida dentro del rango, sólo tenemos que escribir el título del encabezado en la celda de la derecha que en principio está fuera del rango de la Tabla. Automáticamente, Excel agrega un columna al rango y reconoce lo que hemos escrito como un encabezado más.



9. Gestión inteligente del nombre de los títulos de encabezados

Quizá a estas alturas el lector ya se ha dado cuenta de que, cuando tenemos una "Tabla de Excel" y estamos dentro de ella, las referencias que se hagan a celdas o títulos de encabezado no son del tipo "B4" (por ejemplo) ... o "C7".... sino que las Tablas tienen su propio lenguaje. Vemos a continuación cómo al hacer referencia a otra celda poniendo el signo igual y haciendo click en ella, no aparece la clásica referencia  =D54 , sino que aparece =[@[€VENTAS]] , que quiere decir que estamos haciendo referencia a la celda de la misma fila donde estamos (el símbolo @) que está en la columna [€ VENTAS].

Así las cosas, se nos plantean unas dudas: ¿Que ocurre si cambiamos el nombre del titulo de un encabezado de columna, después de haber introducido fórmulas con ese tipo de referencias? ¿Perderemos las referencias correctas?.  La respuesta es NO.  Si cambiáramos un título, se produciría el cambio en todas las fórmulas que tengamos con esa referencia.

Tenemos que decir que el "lenguaje" de referencia a celdas que acabamos de ver pertenece a Excel 2010. Sin embargo, podemos ver a continuación que en Excel 2007, cambia significativamente:

En 2007, las referencias son más largas, puesto que expresan toda la "ruta" empezando a poner el nombre de la tabla y después, en vez de una simple arroba para significar que la referencia esta en la misma fila (como hace en Excel 2010), pone la expresión [#Esta fila].

10. Totaliza de forma correcta las columnas con filtros

Aunque a algunos usuarios no expertos les parezca sorprendente, si Excel debe operar con un rango de celdas de una columna en la que existen columnas ocultas debido a un filtro u otras razones, la operación NO SERÁ CORRECTA. Podemos verlo en la siguiente tabla (No es una "Tabla de Excel") donde hay un filtro aplicado (y por lo tanto hay filas ocultas) y vemos cómo la suma es incorrecta:

Sin embargo, con una Tabla de Excel, esto no ocurre porque si añadimos una fila de totales (podemos hacerlo estando situados en cualquier celda de la Tabla de Excel y en la ficha contextual Diseño / Grupo Opciones de estilo de tabla / activar la casilla "Fila de totales"), automáticamente tenemos la posibilidad de totalizar la tabla, pero con la función SUBTOTALES, la cual no se ve afectada por los filtros o las filas ocultas:

Si desea llevar a la práctica todo lo que le hemos ofrecido en este artículo, puede asistir a nuestros cursos prácticos de Excel o puede solicitarlos in company:

Ordenar una tabla de Excel por varios campos a la vez

Muy pocos conocen este truco o "rincón" de Excel y sin embargo estamos hablando de una herramienta que es utilizable en uno de los ámbitos más populares dentro de la utilización de Excel:  el manejo de grandes cantidades de datos en forma de tabla.

Se trata de la posibilidad de ordenar una tabla aplicando criterios a varios campos simultáneamente. Casi todos los usuarios de Excel conocen los autofiltros que se pueden aplicar a una serie de datos dispuestos en forma de tabla (y también a lo que se llama formalmente una "Tabla de Excel")...

pero sólo es posible aplicar un filtro a la vez y sobre un campo. Al intentar aplicar un segundo filtro, el primero desaparece.

No es muy conocido que es posible aplicar varios filtros simultáneamente y que esto se puede conseguir seleccionando la opción Ordenar por color / Orden personalizado... que hay en los autofiltros. 

Esta ópción de los menús de autofiltros nos llevan a obtener el siguiente cuadro:

...en el que podemos hacer uso de distintos niveles de filtros de forma simultánea seleccionando  las columnas por las que queremos ordenar, el objeto que será ordenado (valores, colores, etc.) y el criterio  de ordenación. Cada vez que queramos agregar un nivel de ordenación, lo hacemos desde el botón de arriba a la izquierda "Agregar nivel".

Por ejemplo, quedaría así el cuadro para ordenar 2 campos distintos simultáneamente con criterios distintos:

... y así quedaría la tabla ordenada:

Es entendible que no sea muy conocida esta opción, puesto que no es muy intuitiva la forma de acceder a ella. Recordemos que el menú del autofiltro era... Ordenar por color / Orden personalizado...  y lo de "Ordenar por color" despista bastante.

Si desea llevar a la práctica todo lo que le hemos ofrecido en este artículo, puede asistir a nuestros cursos prácticos de Excel o puede solicitarlos in company:

La magia y productividad de las "Tablas de "Excel"

Muchos trabajamos con listas de datos en una hoja de cálculo, pero pocos conocen el hecho de que Excel es capaz de proporcionar muchísima productividad a nuestras tareas en ese ámbito.

Cuando trabajamos con una lista o tabla de datos en la que existe un encabezado o título por cada columna y abajo, una serie de valores, en principio y salvo que nosotros lo provoquemos, esos datos son considerados por Excel como datos independientes y sin relación entre ellos.

Por ejemplo (sólo como ejemplo), no considera que lo que nosotros vemos como un título de columna, sea eso... un título que explica los datos que hay a continuación más abajo.

La cuestión es que si nosotros quisiéramos, Excel podría reconocer "oficialmente" a los datos de una lista que tenga una o varias columnas, como "Tabla". Esto nos proporcionaría muchas posibiliades de productividad.

Para ello, tan solo tendríamos que:

1) Estar situados en cualquier celda de nuestra, en principio, lista de datos.

2) Hacer click en el botón "Dar formato como tabla" que hay en el grupo "Estilos" de la ficha "Inicio" en Excel.

3) Seleccionar el estilo que más nos guste entre los obtenidos en el desplegable y....

4) Aceptamos el cuadro que aparece:

¡¡..voila...!!

Lo que antes podría ser algo así:

...ahora y casi por "arte de magia" (con sólo 3 clicks de ratón), puede ser así.... (depende del estilo elegido):

Acaban de crear formalmente una TABLA en Excel... y esto les abre a un mundo de posibilidades.... 

¿Quieren ahora ustedes un ejemplo de productividad? ¿Sí?

Manténganse con el cursor dentro de la tabla creada y activen la casilla "Fila de totales" que hay en el grupo "Opciones de estilo de tabla" de la ficha "Diseño" (....ficha que aparece sólo cuando estamos dentro de la tabla creada formalmente). Obtendrán esto...

Fíjese en la fila añadida y sitúe el cursor encima del total. Verá que obtiene un desplegable con el cual puede tener disponible, no sólo la suma total de la columa, sino que puede elegir entre el promedio, el nº de filas (cuenta), el valor máximo de la columna, el mínimo, etc.

...podríamos hacer un post interminable con todas las posibilidades que ofrecen las tablas "formales" . En vez de ello, lo dejaremos para un artículo en el que mostraremos Las 10 ventajas de hacer una Tabla de Excel.

Si desea llevar a la práctica todo lo que le hemos ofrecido en este artículo, puede asistir a nuestros cursos prácticos de Excel o puede solicitarlos in company:

 

Mantener siempre actualizada una Tabla Dinámica en Excel

Todos los usuarios que utilicen Tablas Dinámicas, saben que estas deben ser actualizadas para que muestren los posibles cambios que hubiere en los datos de origen.

De forma básica, esta actualización se puede hacer de dos formas:

• Manual:  mediante el botón actualizar que vemos en la ficha Opciones (sólo disponible cuando el cursor está situado en la Tabla Dinámica).
• Automática: activando la casilla "Actualizar al abrir el archivo" que hay en la pestaña "Datos" del cuadro de diálogo "Opciones" que podemos activar desde la ficha "Opciones" (como hemos dicho antes, sólo disponible cuando el cursor está situado en la Tabla Dinámica".

Vamos, en este artículo, a proponer una tercera alternativa de actualización (automática) mediante una pequeña rutina de código que debemos introducir en el evento "open" de la hoja donde está alojada la Tabla Dinámica.

¿Cómo se hace esto?

Paso 1

• Presionamos Alt + F11, o bien en la Ficha Programador, hacemos click en el botón "Visual Basic". Se abre el Editor de Visual Basic.
• En el explorador de proyectos, seleccionamos la hoja donde está la Tabla Dinámica que queremos actualizar de forma automática:

• Con el botón derecho, hacemos click en dicha hoja y seleccionamos "Ver código".

Paso 2

...hecho lo anterior, se nos abre una ventana a la derecha, donde debemos escribir:

Private Sub Worksheet_Activate()
ActiveSheet.PivotTables("NombreTablaDinámica").PivotCache.Refresh
End Sub

...donde vemos que "NombreTablaDinñamica" debe ser el nombre de nuesrta Tabla Dinámica, el cual podemos previamente definir y ver en el campo "Nombre de Tabla Dinámica" que hay en la ficha "Opciones" de las Herramientas de Tablas Dinámicas (recordamos una vez más que debemos estar situados en la Tabla Dinámica para obtener esta ficha):

El efecto que hemos conseguido es que, cada vez que activamos la hoja donde hemos alojado el código, se actualiza la Tabla Dinámica.

Debemos recordar dos cuestiones:

• Cada vez que actualizamos....¡¡se actualizan todas las Tablas Dinámicas del libro que tengan exactamente el mismo origen de datos!!.
• No es necesario que la hoja que cause la actualización sea donde está la Tabla Dinámica.

Paso 3

No hay que olvidar que cuando introducimos código en un archivo de Excel, debemos "Guardarlo como..." un archivo de tipo .xlsm (Habilitado para macros). Por lo tanto, debemos ir a la pestaña Archivo (si estamos en Excel 2010, 2013 o 2016) o el botón de Office (si estamos en Excel 2007) y después elegir el comando "Guardar como..." y después el tipo de archivo que hemos comentado anteriormente.

... Aceptar.

  
José Manuel Pomares Medrano

Tablas Dinámicas (V): Opciones de DISEÑO

Con los anteriores cuatro artículos, hemos dado un repaso a los dos primeros pasos en la creación de una tabla dinámica. Nos falta el tercero: dar los últimos retoques al aspecto y diseño.

Cuando tenemos seleccionada cualquier celda de una Tabla Dinámica, aparecen 2 fichas: OPCIONES y DISEÑO , que Microsoft las llama "Herramientas de Tabla Dinámica". Hasta ahora, además del proceso básico de creación de una Tabla Dinámica, hemos conocido esencialmente la primera (OPCIONES). Ahora debemos avanzar y dar un repaso a las opciones de DISEÑO, las cuales sirven para decidir el aspecto final que tendrá nuestra Tabla Dinámica.

Algunas de las posibilidades ya las hemos visto, pues el cuadro de "Opciones de Tabla Dinámica" ya nos ofrecía la posibilidad de elegir algunas configuraciones. Pero hay otras que debemos saber:

Mostrar subtotales

Cuando tenemos varios campos en las filas, nuestra Tabla Dinámica se convierte en un esquema jerarquizado. Con la opción de "Mostrar totales", podemos hacer que en cada campo de jerarquía (los que encabezan y resumen los detalles) aparezca el subtotal en la parte superior. En la imagen de la derecha, podemos ver tanto el menú donde podemos elegir la opción, como el resultado. También podemos probar con la opción de que aparezcan en la parte inferior de cada campo de jerarquía, eligiendo la opción adecuada en el mismo menú..

Diseño de informe

A través del menú "Diseño de página", podemos elegir cómo se organizan las filas:

Por defecto, las tablas Dinámicas con más de un campo en las filas o en las columnas, se muestran con un formato llamado "Forma compacta" y que podríamos expresar gráficamente así:

Después tenemos la "Forma de esquema":

... y también la "Forma tabular":

Diseño de informe

Con el menú "Filas en blanco"...

...podemos hacer que nuestra Tabla Dinámica tenga un aspecto mas claro separando mediante una fila en blanco los detalles de un campo de jerarquía y otro:

Filas con bandas / Columnas con bandas

También podemos hacer, de forma automática, que la Taba Dinámica tenga el conocido aspecto de "pijama" alternando un color más oscuro y otro más claro. Esto se hace activando la casilla "Filas con bandas":

.... en caso de que queramos este efecto con las columnas, debemos activar la casilla "Columnas con bandas".


Estilos de Tabla Dinámica

Por último, tenemos la opción de dar a toda la Tabla Dinámica (teniendo seleccionada cualquier celda de  ella) un aspecto que se adapte a nuestras necesidades o gustos mediante el desplegable que hay en el grupo "Estilos de Tabla Dinámica":

Vemos que abajo hay dos opciones:

• Nuevo estilo de tabla dinámica... que nos permitirá construir un estilo personalizado
• Borrar... que nos permitirá borrar el estilo y opciones de diseño que tenga aplicados hasta el momento nuestra Tabla Dinámica. Aunque es necesario decir que si tuviéramos formatos aplicados mediante las herramientas tradicionales de "relleno", alineación, negrita, etc. NO se borrarán. Es decir, sólo borra los formatos establecidos mediante las opciones de diseño.

Ver Tablas Dinámicas (I): ¿Qué son y para qué sirven?

Ver Tablas Dinámicas (II): ¿Cómo crear una Tabla Dinámica?

Ver Tablas Dinámicas (III): ¿Cómo crear una Tabla Dinámica? 2ª parte

Ver Tablas Dinámicas (IV): OPCIONES de configuración

  José Manuel Pomares Medrano