Adios a ese molesto GetPivotData()

Con frecuencia me cuestionan si las nuevas funcionalidades de Excel tienen la finalidad de hacer la vida mas facil o mas difícil a los usuarios… y mi respuesta casi siempre es que las nuevas caracteristicas tienden a hacer mas facil la vida de los usarios si estos se toman la molestia de pagar el precio de poner un poco de atención a los cuadros de dialogo y razonar sobre cual es la mejor manera de hacerlo.
El ejemplo clasico es la funcion GetPivotData que sin preguntar se activa cada vez que hacemos referencia a un valor que existe en una tabla pivote.
Esto no deja de tener algunas ventajas para un usuario experimentado, pero para la gran mayoría es un dolor de cabeza tener que lidiar con este favor no solicitado que nos hicieron los programadores de Microsoft.









La solucion es muy simple y muy poco conocida también siga estos pasos:
1.- En la barra de herramientas de clic derecho y vaya al menú “Customise” o “Personalizar”























2.- Busque en el tab “Commands” el menú “Data” y busque el comando “Generate Pívot Data”






















3.- Arréstelo a la barra de herramientas de su preferencia y listo.

A partir de ahora esta listo para evitar esos molestos GetPivotData y solo utilizarlo cuando mejor le convenga.

Tal vez ahora se preguntara “mmmm y como para que es bueno el dichoso GetPivotData?” y claro que sirve, de hecho es una funcion que ahorra bastante tiempo y evita errores.

Pero eso sera la proxima vez.

Formato condicional en gráficas

Una usauria me pregunta si es posible dar formato condicional a una gráfica de barras, de forma que la columna con el valor mínimo tenga color rojo, y la que tenga el valor máximo sea verde.

Suponiendo la siguiente tabla de datos:

Si hacemos una gráfica de columnas estándar, obtenemos lo siguiente:

Desde luego, se puede cambiar manualmente el color a los puntos (barras) máximo y mínimo de la serie. La desventaja es que, si los datos son cambiantes, habrá que estar coloreando las barras cada que esto ocurra. Lo que buscamos es un formato condicional, automático, que en todo momento de el color adecuado a cada columna.

Excel no tiene una forma directa de hacer este ajuste. De cualquier forma, podemos simular este efecto, llevando a cabo unos cuantos ajustes en nuestros datos y en nuestra gráfica.
Iniciamos aquí el uso de las series auxiliares o dummy series, imprescindibles para elaborar varios tipos de gráficas avanzadas. Comenzamos por añadir a nuestra tabla de datos tres columnas, como sigue:


En la celda C2 tenemos la fórmula:

=SI(B2=MAX($B$2:$B$13),B2,NOD())

En D2:

=SI(B2=MIN($B$2:$B$13),B2,NOD() )

Y en E2:

=SI(ESNOD(C2)*ESNOD(D2), B2,NOD())

Con estas fórmulas, logramos que la columna C muestre únicamente el valor máximo de la serie, la D el mínimo y la E todos los demás. Las celdas restantes muestran resultados de "error". El objetivo de estas fórmulas es crear una matriz de números y valores #N/A. Recordemos de notas anteriores que al graficar, Excel ignora los valores de error y los interpola de forma automática.

El siguiente paso es ajustar el rango de origen de la gráfica. Damos clic derecho en cualquier parte de la gráfica y seleccionamos Datos de origen... y en Rango de datos escribimos el rango:

=Hoja1!$A$1:$A$13,Hoja1!$C$1:$E$13

Es decir, ignoramos la columna B. Aceptamos el cuadro obteniendo la siguiente gráfica:

Observamos que los valores máximo y mínimo tienen colores diferentes. El efecto se logra ya que en realidad estamos graficando tres series de datos (de las cuales las dos primeras constan de un solo punto), pero formuladas de tal manera que aparentan ser una sola.

Podemos ver también que las columnas o series (que antes eran puntos de una sola serie) no aparecen correctamente centrados en sus respectivas categorías. Para solucionar esto, damos clic derecho en la gráfica, clic en Tipo de gráfico... y cambiamos el subtipo de gráfico a Columna apilada (¿por qué?). Obtenemos:

Por último, dí unos cuanto ajustes cosméticos: cambié el color del área de trazado a blanco, el tipo de las líneas de división a línea punteada, y el color de cada serie. Además cambié, en la escala del eje vertical (y), el valor de la división principal a 2 y el de la secundaria a 1. Eliminé el borde del área del gráfico y la subleyenda Valores de la leyenda principal. El resultado final:

Si en este momento cambiamos los valores, y cambia la posición de los valores máximo y mínimo, veremos que los colores se ajustarán en consecuencia.

Pie de página III

Continúa de la nota anterior.

La tercera consulta es configurar Excel de forma que todo libro nuevo tenga pies de página en todas las hojas. Interesante.

La solución que propongo consiste en, primero, crear manualmete un libro que contenga pie de página en todas sus hojas. Después, guardarlo como plantilla en la carpeta de inicio de Excel, de forma que al inicializarlo se abra automáticamente. Posteriormente, crear una macro personal sencilla que abra esta plantilla. Luego, algo de personalización: asignar esta macro al comando Archivo - Nuevo, y asociarle el método abreviado Ctrl + u (utilizado por default por el comando Archivo - Nuevo). Para hacer esto aún más "invisible", guardaremos la plantilla con el nombre "Libro"; de esta forma al abrirla, Excel le cambiará el nombre a "Libro1" (cuando se abre una plantilla, Excel la convierte a libro normal y le agrega un consecutivo al final del nombre, conservando intacto el archivo original). Así pues, esta será la primera solución formal en Excel que desarrollemos.

El primer paso es abrir un libro nuevo (Ctrl + u) e insertar pies de página en las hojas, manualmente o utilizando la macro de la primera nota (sugiero que el libro tenga unas 6 hojas o más, para no tener que insertar otras después).

Posteriormente, damos Archivo - Guardar como... En nombre de archivo escribimos "Libro", y en Guardar como tipo: ponemos Plantilla. Guardamos en la ruta predifinida para plantillas o en cualquier otra ruta. Si queremos que Excel abra la plantilla al inicializarlo, guardamos una copia en la carpeta de arranque (XLSTART, pueden ubicarla realizando una búsqueda en Windows).

Ahora, la macro. Escribimos este código en un módulo del libro de macros personal:

Sub mimacro()

Workbooks.Open Filename:= _

-----"C:\Documents and Settings\leonel.quezada\Datos de programa _
-----\Microsoft\Plantillas\Libro.xlt"
End Sub

Cerramos el editor de Visual Basic. Obviamente, tienen que ajustar la ruta en la que han guardado el archivo en sus equipos.

A continuación asignamos el método abreviado Ctrl + u a la macro (utilizado actualmente por el comando Abrir - Nuevo...). Con Herramientas - Macro - Macros - clic en la macro - Opciones...

El siguiente paso es personalizar el comando Archivo - Nuevo. Para lograrlo, ejecutamos Herramientas - Personalizar... (Alt, h, z). Esto nos llevará al cuadro de diálogo Personalizar:

Con este cuadro abierto, damos clic en el menu de Excel Archivo, clic derecho en Nuevo... y clic en Asignar macro...:

En el cuadro Asignar macro seleccionamos la macro que hicimos (en el ejemplo, mimacro) y aceptamos el cuadro de diálogo. Cerramos el cuadro Personalizar.

En adelante, todos los archivos nuevos que abramos con Archivo - Nuevo... o con Ctrl + u, tendrán configurado el pie de página correctamente.

En lo personal, yo utilizaría la macro de la primera nota, ejecutándola cuando fuera requerido. Es la más sencilla de realizar y deja los archivos libres de macros. Mi segundo lugar en preferencias es la presente solución (además de que sirve para ejercitar otros temas). La desventaja es que si insertamos una hoja, ésta no contendrá pie de página. La opción de la segunda nota no la utilizaría ya que implica infestar de macros todos archivos en los que la usemos (consideremos que muchos usuarios simplemente ODIAN las macros, en algo que yo llamo macrofobia). Aparentemente lo mejor es agregar la macro de evento de la segunda nota a esta plantilla, pero esto también implicaría el uso forzoso de macros. En todo caso, la decisión corresponde al usuario final.

Pie de página II

Continúa de la nota anterior.

Veamos ahora el segundo caso, agregar pie de página al insertar una hoja nueva. La única manera de lograr esto es escribiendo una macro dentro del libro mismo al que le estaremos insertando hojas. No podemos escribirla dentro del libro de macros personal, ya que no se trata de una macro "normal", sino de una macro de evento. Estas macros de evento se ejecutan automáticamente al realizarse determinada acción (el evento) por parte del usuario. En este caso, se trata del evento NewSheet. Todo el código que asociemos a este evento se ejecutará única y exclusivamente al insertar una hoja en el libro lo contiene. Es por esto que no podemos guardarlo en el libro de macros personal, ya que en ese caso solo se ejecutaría al insertar una hoja en este mismo libro de macros personal, lo cual nunca sucederá, ya que este libro solo lo utilizamos como contenedor de nuestras macros de uso general, además de que está oculto. Como dije al principio, ya habrá tiempo para profundizar en este tema.

Seguimos los siguientes pasos:

Asegúrense de tener abierto el libro al cual asignarán el código. Acto seguido, abrimos el editor de Visual Basic (Alt + F11). Identificamos el libro en el que queremos trabajar en el Explorador de proyectos y damos doble clic en él.


Abrimos la subcarpeta Microsoft Excel Objetos y doble clic en el objeto ThisWorkbook.

En la primera lista despleglable de la parte superior del Editor, seleccionamos Workbook, y en la segunda lista selecionamos NewSheet:

Con esto aparecerán en el Editor la primera y la última línea de código del evento NewSheet. En medio de estas dos líneas, escribimos el mismo código de la nota anterior, excepto la primera y la última líneas, debiendo quedar como sigue:

Private Sub Workbook_NewSheet(ByVal Sh As Object)

Dim ws As Worksheet

Dim nombre As String

Set ws = ActiveSheet

nombre = Application.UserName

---With ws.PageSetup

------.LeftFooter = "&11&Z&F"

------.CenterFooter = "&11&D"

------.RightFooter = "&""Arial,Normal""&11Elaboró: " & _

---------UCase(Left(nombre, 1)) & Mid(nombre, 2, InStr(1, nombre, ".") - 2) _

---------& " " & UCase(Mid(nombre, InStr(1, nombre, ".") + 1, 1)) & _

---------Mid(nombre, InStr(1, nombre, ".") + 2)

---End With

End Sub

Finalmente, hacemos una prueba. Dado que esta es una macro del evento NewSheet, simplemente insertamos una hoja en el libro. Si lo hicieron correctamente, pueden comprobar que ya contiene pie de página.

Finalizamos en la siguiente nota.

Pies de página autómaticos

Un usaurio me pregunta cómo insertar rápidamente un pie de página en una hoja. En realidad fueron tres consultas en una: cómo insertar autómaticamente un pie de página, cómo insertar un pie de página automáticamente al insertar una hoja, y cómo abrir un libro nuevo con pies de página incluídos. Dado que $oy amable, responderé a las tres, en ese mismo orden.

Antes de comenzar, diré que, extrañamente, Excel no tiene una manera directa de hacer esta configuración automáticamente. Obviamente, podemos ir a Archivo - Cofigurar página - Encabezado y pie de página - Personalizar pie de página... clic en el botón Ruta de archivo - Aceptar (¿así o más clics?) y hacer la configuración correspondiente. Pero si queremos que esto sea automático, la única vía es utilizando macros. Como aún no hemos visto prácticamente nada acerca de macros avanzadas, solo proporcionaré las macros y el modo de utilizarlas, sin detenerme a explicarlas. Posteriormente tendremos tiempo.

Supongamos que queremos insertar el siguiente pie de página: en la sección izquierda, la ruta completa del archivo, en la sección central la fecha, y en la sección derecha la leyenda "Elaboró: [nombre usuario]"

Para el primer caso, insertar un pie de página automáticamente, abrimos el libro de macros personal, con Herramientas - Macro - Editor de Visual Basic (o Alt + F11). Damos doble clic en PERSONAL.XLS. Si lo deseamos, insertamos un nuevo módulo, con Insertar - Módulo. En la zona de edición, escribimos, tal cual, el siguiente código (desde "Sub" hasta "End Sub"):

Sub piepagina()

Dim ws As Worksheet
Dim nombre As String

Set ws = ActiveSheet
nombre = Application.UserName

---With ws.PageSetup
------.LeftFooter = "&11&Z&F"
------.CenterFooter = "&11&D"
------.RightFooter = "&""Arial,Normal""&11Elaboró: " & _
---------UCase(Left(nombre, 1)) & Mid(nombre, 2, InStr(1, nombre, ".") - 2) _
---------& " " & UCase(Mid(nombre, InStr(1, nombre, ".") + 1, 1)) & _
---------Mid(nombre, InStr(1, nombre, ".") + 2)
---End With

End Sub

Damos Archivo - Guardar (o Ctrl + S), y cerramos la ventana del Editor de Visual. A continuación, hacemos una prueba: Activamos la hoja a la que queremos insertar el pie de página, y ejecutamos la macro, con Herramientas - Macro - Macros (o Alt + F8) - Ejecutar.
Verificamos con Vista previa.

Finalmente, asignamos un método abreviado a la macro. Hay que volver al cuadro Macro (alt + F8). Clic en Opciones... y en Tecla de método abreviado: escribimos la letra que deseemos (cuidándonos de no utilizar un método que ya esté siendo utilizado, como Ctrl + c, ya que Excel ejecutaría siempre la macro, no la instrucción original, en este caso, copiar). Digamos Ctrl + q. Clic en Aceptar y cerramos el cuadro Macro. Ahora, al presionar Ctrl + q, Excel insertará el pie de página en la hoja activa.

Continuamos en la siguiente nota.

Navegar en el libro - tip

No es infrecuente trabajar con libros que tienen demasiadas hojas. Lo que es sí es infrecuente es recordar en que posición exacta se encuentran algunas hojas específicas, lo cual nos obliga a recorrer de principio a fin todas las etiquetas de hojas del libro en cuestión. Un proceso muy lento, a veces. Una forma poco conocida de viajar rápidamente a determinada hoja es, en los botones de navegación...

dar clic derecho, con lo cual Excel nos presentará una mini lista con los nombres de todas las hojas de nuestro libro:

Damos clic en el nombre de la hoja a la que queremos ir (o bién, en Más hojas...), y listo.

Esto no nos quita la obligación de manejar cierta organización en nuestras hojas. Yo, por ejemplo, coloco primeramente, en el extremo izquierdo del navegador, las hojas de resumen de información, a continuación, en ese orden, las hojas de cálculos, hojas de tablas de valores, hojas de datos de entrada y al final, formatos de salida. Así, podrán tener una idea más o menos clara de a que zona dirigirse en cada situación.