Archivos de la categoría Excel

Mapa Excel de Europa

mapa_excel_europa1

Un lector me había pedido disponer en Excel de un mapa de Europa y dicho y hecho. Además en este Excel os muestro como hacer paletas de colores más o menos atractivas y como poder usarlas indistintamente con el mapa. Este Excel además contiene algunas líneas de Visual Basic que considero interesantes y que veremos después. Los shape están sacados de este link, pero se retoca completamente el código Visual Basic para hacerlo (a mi entender) más sencillo. El mapa se controla desde esta zona del Excel:

mapa_excel_europa2

El Dato es lo que vamos a pintar. Evidentemente tenéis que cruzar los datos con los nombres de los 37 países que se pintan en el mapa. Disponéis del nombre en inglés y del código de país para poder hacer este cruce previo. Pero quedaros con lo siguiente, lo único que podéis modificar es el nombre en inglés, por si preferís hacer vuestro VLookp o BUSCARV Sigue leyendo Mapa Excel de Europa

Mapas estáticos municipales para estados de México. Con R y con Excel

mexico_municipios_R_excel

Podemos pintar mapas municipales de México con la ayuda de R y Excel. Esta entrada está en la línea de otra anterior para hacer esta misma tarea con mapas de España. Disponemos de los datos en Excel y tenemos intalado R y la librería sp. El objeto R con los mapas de México lo podemos encontrar en http://www.gadm.org/country seleccionamos Mexico y R Spatial Poligons Data Frame y debemos descargarnos el objeto de nivel 2. Comenzamos el trabajo con R:

library(sp)
library(RColorBrewer)

ub_mexico="C:\\TEMP\\00 raul\\MAPA\\MEX_adm2.RData"

#Creamos los objetos de R
load(ub_mexico)
mexico=gadm

unique(mexico@data$NAME_1)

Esos son los Estados mexicanos que tenemos en el mapa. Para ilustrar el ejemplo vamos a pintar el número de habitantes del estado de México. Por ello creamos un objeto sólo con el estado de México:

mexico = mexico[mexico$NAME_1=="México",]

Ahora es necesario que nos llevemos a Excel el nombre de los municipios para cruzar los datos Sigue leyendo Mapas estáticos municipales para estados de México. Con R y con Excel

Trucos Excel. Repetir filas con DESREF

desref repetir filas excel

En respuesta a una cuestión planteada por una lectora en una entrada muy parecida a esta podemos usar la función de Excel DESREF para repetir registros el número de veces que deseemos. Como sabemos DESREF parte desde una celda referenciada y nos movemos FILAS y COLUMNAS en función de los parámetros. La sintaxis es DESREF(CELDA DE REFERENCIA; FILA; COLUMNA) de modo que si ponemos DESREF(A1;0;1) haremos referencia a la celda B1 o bien si ponemos DESREF(A1;1;0) hará refrencia a la celda A2. Para repetir lo que haremos será algo de este estilo:

=DESREF(A$2;REDONDEAR.MENOS((FILA(A3)-2)/4;0);0)

Fijada la celda de referencia nos movemos por las filas y dividimos por el número de veces que deseamos repetir (ene el ejemplo 4), hacemos un redondear menos para que tengamos valores enteros de la fila y de este modo repetirá 4 veces cada fila. De todos modos tenéis en este enlace el ejemplo utilizado para que os sirva de referencia: ejemplo-de-uso-desref-2

Mapas municipales de España en R, con la ayuda de Excel

municipios extremadura R 1

El escribir sobre el BDT de Madrid me ha hecho recordar mi trabajo con statial data de R. Los mapas de spatial data los podemos obtener de forma gratuita de http://www.gadm.org/country y en este caso vamos a descargarnos para España un SpatialPolygonsDataFrame específico para R de nivel 4 que está a nivel municipal (repito nivel todas las veces necesarias), el archivo que os debéis descargar se llamará ESP_adm4.RData. Si buscáis un poco en google encontraréis documentación acerca de este tipo de archivos de R y veréis que se pueden hacer maravillas. En este caso quiero hacer un ejemplo lo más sencillo posible, muy artesanal y casero. Con un poco de imaginación podréis complicarlo o incluso hacer una herramienta en Excel que hiciera mapas con R Sigue leyendo Mapas municipales de España en R, con la ayuda de Excel

Mapas de la provincia de Madrid con BDT

Mapa Madrid por municipios

JL un lector del blog me ha descubierto esta herramienta muy útil para la creación de mapas de Madrid, se trata de una herramienta pública donde tenemos un gran número de variables para poder representar y además podemos representar nuestras propias variables. La web es:

http://www.madrid.org/bdt/Inicio.icm

El BDT de la Comunidad de Madrid, el Banco de Datos Territorial nos permite plasmar en un mapa un gran número de variables del sistema de información estadística de la Comunidad de Madrid y nuestras propias variables que podemos subir tras darnos de alta en menos de 3 minutos. A partir de ahí en Crear Mapas Propios podemos crear nuestros propios mapas de usuario. En Crear nuevas variables podemos empezar a trabajar con nuestras propias variables que primero definimos y posteriormente podemos editar los datos y subirlos desde Excel. Para subir un Excel yo recomiendo descargar uno de los que tiene datos del BDT y ponerle nuestros datos. Es muy sencillo hacer unos mapas de forma tan sencilla que JL y yo hemos hecho  cosas espectaculares.

Ahora viene una duda que os planteo a vosotros y que también puede ayudar a JL ¿Existen herramientas parecidas en otras comunidades?

 

Truco Excel. Gráfico de puntos con colores

Un gráfico de dispersión en Excel en el que los puntos puedan ser identificados si pertenecen a un grupo. Es una duda que plantearon hace unos días en el blog. Con otras herramientas es bastante sencillo, pero en el caso de Excel la tarea no es tan evidente. Para poder hacer gráficos de este tipo he construido una macro que podéis utilizar si previamente la adaptáis a vuestros datos. El código que podéis adaptar una vez halláis creado vuestro gráfico de dispersión es:

Sub Macro2()
'
' Macro realizada por analisisydecision.es
'
Dim vec As Variant

'
    ActiveSheet.ChartObjects("grafico").Activate
    ActiveChart.SeriesCollection(1).Select
    vec = ActiveChart.SeriesCollection(1).Values
    numpuntos = UBound(vec)

    For i = 1 To numpuntos
    grupo = Cells(i + 2, 3)

    ActiveChart.SeriesCollection(1).Points(i).Select

    If grupo = 1 Then Selection.Format.Fill.ForeColor.RGB = 3969653
    If grupo = 2 Then Selection.Format.Fill.ForeColor.RGB = 255
    If grupo = 3 Then Selection.Format.Fill.ForeColor.RGB = 14922893
    Next i

    ActiveSheet.ChartObjects("grafico").Activate

End Sub

Lo primero tenemos una variable tipo variant que nos permitirá obtener el número de puntos que deseamos colorear. Seleccionamos el gráfico de dispersión y la única serie de datos es la 1, no hay más. Buscamos el número de puntos que tiene nuestra serie y hacemos un bucle que se recorre cada uno de los puntos de la serie que queremos colorear. Evidentemente necesitamos saber a que grupo pertenece cada punto. Después seleccionamos punto por punto y si pertenece a un grupo le ponemos un color con Selection.Format.Fill.ForeColor.RGB, el color le podéis buscar o elegir de la paleta de colores, en este blog ya se ha escrito sobre saber el número de color. Si en vez de grupos usáis formatos condicionales y otras cosas pueden quedar resultados muy buenos. Pero esto me lo guardo para otro día. Espero que os sea útil el truco. Saludos.

Truco Excel. Formatos condicionales para crear rango de colores

Un truco de Excel poco ortodoxo. Aprovechar los colores que nos ofrece un formato condicional sin necesidad de emplear el formato condicional para crear un rango de colores. Parece un trabalenguas pero puede ser muy útil cuando trabajamos con Visual Basic. En mi caso particular es muy útil disponer de estas paletas de colores para hacer mapas mucho más vistosos. El truco es muy sencillo empezamos por escribir números del 1 hasta el número de colores que deseamos y elegimos el formato condicional a aplicar:

Para el ejemplo ponemos 20 números y elegimos el formato condicional donde el verde significa mayor y el rojo significa menor. Cualquier intento de copiar formatos o de obtener el color de la celda con Visual Basic se complica bastante. Y aquí viene el truco “poco brillante pero práctico”. Lo que hacemos es copiar los datos con el formato de color y pegarlo en Word:

Desde Word ya podemos pegarlo en Excel y no tendremos formatos condicionales que nos impidan identificar el color de la celda. En el blog ya hablamos de ello en una ocasión y aplicando esa sencilla macro obtenemos:

Una forma poco elegante pero sencilla de obtener rangos de colores en Excel. Yo hago virguerías con este truco. Saludos.

Mapa del mundo en Excel

Un mapa del mundo en Excel preparado para poner datos de la Base de Datos de la UNESCO. Está sacado de una web, cuando encuentre el link os lo pongo porque lo he perdido. Además al César lo que es del César. Sobre el fichero Excel que me descargué realicé diversas modificaciones para mejorar los resultados y darle simplicidad. Hay una hoja de datos que es donde debéis pegar los datos (preferiblemente) por otro lado está la hoja Mapa que contiene el mapa sobre el que podéis realizar las modificaciones. En esta hoja están los datos sobre los que se hace la jerarquía. La gama de colores que utiliza este mapa es de gris a rojo. Esto podéis cambiarlo vosotros mismos, en esta web se han dado pistas sobre cómo hacerlo.  No es correcto darlo todo hecho pero jugando con formatos condicionales y con esas pistas podéis obtener un mapa espectacular.

Aquí podéis descargar el mapa.

Para actualizar los colores tenéis que ejecutar esta simple macro:

Sub cambia_color()
Dim pais As String
Dim i As Integer
Dim color As Long
Dim myShape As Shape
For i = 2 To 190
pais = Cells(i, 26)
color = Cells(i, 30)
Set myShape = Sheets(1).Shapes(pais)
myShape.Fill.ForeColor.RGB = color
Next i
End Sub

En cuanto a los datos que se representan en el mapa. Tasa de mortalidad infantil en el mundo. Los países más oscuros o bien no disponen de datos o bien no han cruzado con los datos de la UNESCO. Se podrá mejorar el resultado. Algo más subjetivo, no sé como consentimos este rojo predominante en África. Será que sin ese rojo yo no podría escribir cosas como esta… Saludos.

Truco Excel. Pasar de número con formato AAAAMMDD a fecha con fórmulas

Tenía que transformar un número en formato AAAAMMDD a una fecha en Excel. Tenía que concatenar varias funciones y he pensado que a lo mejor a alguno de los lectores del blog podría interesarle. Se trata de utilizar la función de Excel FECHA con RESIDUO y ENTERO pero con cierto talento. Partimos del una fecha 20140225 y realizamos la siguiente sucesión de fórmulas:

  • Año: ENTERO(C6/10000)
  • Mes: RESIDUO(ENTERO(C6/100);100)
  • Día: RESIDUO(C6;100)
  • Todo junto en una fórmula: FECHA(ENTERO(C6/10000);RESIDUO(ENTERO(C6/100);100);RESIDUO(C6;100))

Ahí tenéis un truco interesante por si os encontráis en la misma situación que yo. Saludos.

Truco Excel. Identificar el color de una celda

Para identificar el color de una celda en Excel podemos emplear Interior.Color del siguiente modo:

Sub Macro1()

For i = 3 To 6

dato = Cells(i, 2).Interior.Color
Cells(i, 3) = dato
Next i

End Sub

No funciona con formatos condicionales, si deseáis utilizar los colores de los formatos condicionales habréis de idear cómo hacerlo o bien esperar a que tenga tiempo a redactar como lo hago yo, pero que nadie se espere un programa en VB brillante, que no fui capaz de hacerlo. Saludos.