Excel

Aunque me lo agradezcan poco el mapa por comunidades de Excel está teniendo un gran éxito. Mientras preparo un mapa por provincias en Excel he elaborado el siguiente tutorial para crear mapas en Excel a partir de archivos SVG. El punto de partida, disponer de Inkscape software libre para la elaboración de dibujos y Excel. Podemos buscar mapas en la wikipedia, en este caso mapa por provincias de España. Se trata de utilizar ese archivo svg y crear un Excel con objetos de ms-office que provienen del archivo svg que hemos abierto con el Inkscape. Juntamos las piezas del puzle y ya podemos trabajar con el mapa.

Quería plantear hoy un truco Excel que no es un truco como tal, si no más bien una idea para que podáis hacer más vistosos vuestros informes con gráficos de caracteres, sobre todo para aquellos dinosaurios que hagáis muchos ranking o puntuaciones. La idea es muy sencilla se trata de emplear la función REPETIR de Excel pero «con talento». =REPETIR(«|»;E12) nos repite el pipe | tantas veces como le indiquemos en la casilla E12. Pero hay algo que si tiene importancia, la fuente que elijamos para el pipe:

Vamos a mejorar muchos de nuestros informes con mapas de España realizados a través de Excel. Os contaré la metodología que empleo para realizarlos y a partir de ahí vosotros podréis mejorarlos. Voy a realizar un mapa de España por Comunidades Autónomas y le vamos a modificar en función del gasto en prestaciones por desempleo (http://www.tt.mtin.es/periodico/laboral/201006/mayo.pdf).Todo empieza por conocer los archivos SVG y pasa por agradecer a los usuarios de la wikipedia que distribuyan todo tipo de mapas realizados por ellos mismos y de uso libre. En este caso buscamos un mapa de España por Comunidades Autónomas . Necesitamos una aplicación libre para manejar estos mapas y la mejor, más adecuada y la que yo uso es Inkscape la conocí ayer por la mañana, después me harté de botellines, me fui a sembrar unas guindillas y a día de hoy ya hago mis pinitos con el portátil del trabajo, porque en el mío no funciona. Da problemas en Win 64 bits (¡si!, uso win, como tú que lees esto ya que es muy poco probable que pertenezcas al 3% que usa Mac o al 3% que usa Linux). Con esta aplicación tenemos a nuestro alcance ver perfectamente que es lo que hace el XMLy que espero termine en un paquete de R para hacer mapas de España.Bien, tenemos Inkscape y el mapa por Comunidades de Wikipedia y ahora viene un trabajo muy sencillo, muy aburrido y muy en la línea de un tipo mediocre como el que escribe ahora mismo. Voy a copiar en Inkscape y pegar en Excel cada Comunidad Autónoma, lo transformo en un objeto de Ms-Office para que me sea más sencillo modificarle las propiedades y le doy un nombre a cada figura. Tras 5 minutos haciendo un puzle tengo algo parecido a esto en Excel:

Con Visual Basic podemos modificar la configuración regional. Podemos crear macros en Excel que nos realicen esta tarea. De este modo si trabajamos con aplicaciones que tienen configuración americana podemos cambiar con una macro, pegar los valores y volver a cambiar la configuración. Para poner separador decimal «.» y separador de miles «,» tendremos que emplear el siguiente código:

Sub formato_americano()'' formato_americano Macro'

With Application

.DecimalSeparator = "."

.ThousandsSeparator = ","

.UseSystemSeparators = False

End With

End Sub

Es un excelente ejemplo de uso de Application. De forma análoga si deseamos volver a la configuración europea solo debemos emplear los separadores del sistema:

A la hora de referenciar en Excel celdas de tablas dinámicas es muy molesto encontrarnos con referencias del tipo « +IMPORTARDATOSDINAMICOS(«CLIENTES»; F3;»POTENCIAL»;2)» El importardatosdinamicos puede resultarnos muy molesto para trabajar con fórmulas que normalmente arrastramos. Para evitar este problema hemos de colocar en la barra de herramientas el botón «Generar GetPivotData». Esto lo hacemos ubicándonos en una barra de herramientas y con el botón derecho nos vamos a Personalizar y entre los botones de datos tenemos el Generar GetPivotData, como vemos en la figura adjunta. Lo seleccionamos y lo pulsamos cuando deseemos no tener la dichosa fórmula del importardatosdinamicos y podemos hacer fórmulas más habituales.

Una de las tareas más comunes en Excel es la de transponer filas. En ocasiones hemos de transformar columnas en filas o viceversa:

Es muy habitual copiar y pegar transponiendo pero esta labor es muy manual cuando manejamos hojas con gran cantidad de fórmulas y que pueden generar informes automáticos. Para transponer contamos con la ayuda de la función INDIRECTO de exce. En la ayuda se define como:

« Devuelve la referencia especificada por una cadena de texto. Las referencias se evalúan de inmediato para presentar su contenido. Use INDIRECTO cuando desee cambiar la referencia a una celda en una fórmula sin cambiar la propia formula »

Hay operaciones que pueden ser muy habituales de hacer con Excel y necesitaríamos una función propia que la realizara. A continuación vamos a plantear una situación en la que es especialmente útil crear una función específica y guardarla en el libro de macros personal para utilizarla en nuestras sesiones SAS. En este caso concreto vamos a crear una función que nos redondeé un número a 0 o 0.5, es decir, 6,3 valdría 6 y 6,7 valdría 6,5. Para realizarlo sin una función específica emplaríamos múltiples funciones de Excel, por ejemplo:

El siguiente truco Excel es muy sencillo. Se trata de una consulta web que nos permite obtener el cambio de divisas a Euro casi practicamente on-line. La consulta se realiza sobre la web www.finanzas.com sitio que además quiero recomendar, desde él sigo la imparable caida de mis valores…

La consulta es la siguiente:

Sub cambio_divisas()

'

'

    With Application

        .DecimalSeparator = "."

        .ThousandsSeparator = ","

        .UseSystemSeparators = False

    End With

    With ActiveSheet.QueryTables.Add(Connection:= _

        "URL;http://www.finanzas.com/divisas", Destination:=Range("A1"))

        .Name = "divisas_1"

        .FieldNames = True

        .RowNumbers = False

        .FillAdjacentFormulas = False

        .PreserveFormatting = True

        .RefreshOnFileOpen = False

        .BackgroundQuery = True

        .RefreshStyle = xlInsertDeleteCells

        .SavePassword = False

        .SaveData = True

        .AdjustColumnWidth = True

        .RefreshPeriod = 0

        .WebSelectionType = xlSpecifiedTables

        .WebFormatting = xlWebFormattingNone

        .WebTables = "1"

        .WebPreFormattedTextToColumns = True

        .WebConsecutiveDelimitersAsOne = True

        .WebSingleBlockTextImport = False

        .WebDisableDateRecognition = False

        .WebDisableRedirections = False

        .Refresh BackgroundQuery:=False

    End With

    With Application

        .DecimalSeparator = "."

        .ThousandsSeparator = ","

        .UseSystemSeparators = True

    End With

End Sub

Primero cambiamos los separadores de miles y decimales, posteriormente realizamos la consulta a www.finanzas.com/divisas y ya tenemos tabulada la información. Además no sobreescribimos anteriores consultas, siempre se añadirán gracias a RefreshStyle por lo que podemos hacer un seguimiento del cambio. A mí me ha sido muy útil para seguir el efecto del precio de unos fondos de inversión.

Inicio una serie de mensajes con trucos de Excel que considero pueden ser de utilidad para realizar tareas habituales en nuestro trabajo diario. Este primer truco Excel consiste en una macro que nos permite crear en borradores correos electrónicos. También nos permitiría enviarlos directamente, pero en ese caso habríamos de tener permisos para que otras aplicaciones accedieran a Outlook. Veamos el código que emplea:

Sub Correo()

Dim ol As Object, myItem As Object

Dim adjunto As String

Set ol = CreateObject("outlook.application")

Set myItem = ol.CreateItem(olMailItem)

adjunto = “C:\temp\fichero.xls”