Archivo de la categoría: Excel

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 Seguir 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.

Truco Excel. Agrupar valores en un campo de una tabla dinámica

Si necesitamos agrupar un campo de una tabla dinámica tenemos dos opciones. Por un lado agrupar manualmente o bien crear intervalos de determinado tamaño. Para agrupar datos manualmente seleccionamos los elementos que deseamos agrupar en la tabla dinámica y pulsamos botón derecho agrupar:

Inmediatamente se nos crea un nuevo campo en la tabla dinámica que por sufijo tendrá un 2 Seguir leyendo Truco Excel. Agrupar valores en un campo de una tabla dinámica

Truco (muy fácil) de SAS. Leer un rango de una hoja Excel

Cuando tenemos rangos en nuestras hojas Excel y deseamos que se conviertan en tabla SAS podemos emplear la sentencia libname de este modo:

libname selec "C:\TEMP\rangos.xlsx";
data rango;
set selec.rango;
run;
libname selec clear;

Asisgnamos la librería al archivo Excel que deseamos leer y tan simple como referenciar al rango en nuestro paso data. Se interactúa fácil entre Excel y SAS. Saludos.

Truco SAS. Unir todas las hojas de un Excel en una

Empleamos LIBNAME con SAS para acceder a Excel. Es un truco con limitaciones y que se tiene que ir mejorando a futuro. Se trata de leer todas las hojas de un libro Excel y pegarlas horizontalmente en otra hoja QUE NO DEBE EXISTIR PREVIAMENTE. La macro es la siguiente, no se acompaña de un ejemplo de uso debido a su sencillez:

 %macro une_excel(ubicacion, nombre_union);
libname selec &ubicacion.;

proc sql noprint;
select "SELEC.'"||memname||"'n" into:lista_excel separated by " " 
from dictionary.members 
where libname = "SELEC";
quit;

data SELEC.&nombre_union.;
set &lista_excel.;
run;

libname selec clear;
%mend;

%une_excel("C:\TEMP\unir_excel2\unidos.xlsx", todas);

Creamos una librería SAS a un libro Excel determinado. Leemos con DICTIONARY las hojas que tiene dicho libro y las unimos en una hoja de ese libro al que será la última. Como buena costumbre el desasignamos la librería con LIBNAME CLEAR. Como se ha indicado antes tiene limitaciones, por ejemplo no debe existir la hoja final con la unión. Pero es un buen ejemplo de uso de LIBNAME + EXCEL y DISTIONARY. Saludos.