Archivo de la categoría: Excel

Evita problemas con Excel desde R. De tocar el dato a un proceso

En estos días hemos vivido una situación con Excel y los datos de COVID de UK peculiar. Hemos aparecido todos en las redes sociales para reírnos de Excel y de los que usan Excel. De nuevo partidarios de Matlab, R, Python,… ¡a la gresca! Mi opinión la podéis leer en Twitter y creo que sobre este tema puedo opinar. En mi vida profesional me he especializado en cambiar equipos de negocio, por ese motivo tanto ir y venir de compañía. Uno de esos cambios consiste en transformar super usuarios de Excel a usuarios de herramientas más apropiadas para la gestión de la información.

Mi carrera en el sector de la gestión de la información comenzó introduciendo SAS allí donde Business Objects  y Access ya eran insuficientes. Continué con SAS durante muchos años, siempre con guiños al software libre porque R me permitía llegar allá donde SAS n€c€sitaba otro$ módulo$. En los últimos años estoy alternando el uso de Python con R. Sin embargo, mis ponencias y mis presentaciones de servicios están incluyendo el siguiente gráfico:

Soy partidario de SAS, es la mejor herramienta, de SPSS no opino porque no he trabajado lo suficiente (¿sigue existiendo Clementine?) pero no son gratis, una PYME no puede permitirse un alto gasto en software.  Python con sus IDE y Pandas son muy duros para personas acostumbradas a las fórmulas de Excel. Ahí aparece R y sobre todo el universo tidyverse y RStudio, es la clave del cambio y lo que debe hacer que todos esos super usuarios de Excel miren hacía R. Por eso se me ocurrió ese dibujo y ahí estoy.

No usemos Excel como motor de BBDD, no toquemos manualmente los datos, empecemos a entender que la gestión de datos no es un buscar objetivo, la gestión de datos es un proceso que, además, puede ser usado por otro y se puede reproducir no es estático. Se trata de evitar fórmulas que solo entiende el autor, programas de Visual Basic que no entiende ni  el autor y yo sugiero crear un flujo de este modo: chimpún %>% chimpún %>% chimpún. No es difícil y no se trata de prescindir de Excel, porque a Excel ya irán tus datos preparados, nunca podrás tocar un dato previo ya que pasamos del dato en bruto guardado en una BBDD o repositorio al dato final que si usarás en Excel.

Si soy yo el que te ayuda ese proceso prefiero usar R:

  1. Es gratuito, te instalas R y RStudio y los paquetes que necesites.
  2. Es sencillo, unas instrucciones te permiten hacer con dplyr el 90% del trabajo que haces en Excel con cientos de fórmulas.

Un ejemplo práctico, pasando datos de R a Excel

Por último me gustaría ilustrar este trabajo con un ejemplo para poder evaluar la complejidad y como podrías plantear este trabajo, necesitamos leer diariamente los datos de COVID que publica Datadista para disponer de un Excel con esos datos y pintar nuestro seguimiento, también sería interesante obtener un pequeño resumen que nos permita localizar datos atípicos en la serie que se publica. Vamos a emplear la librería openxlsx para generar un Excel con este propósito. El primer paso será leer los datos de Datadista (incluyen los programas para facilitar la cosa) y crear nuestros objetos en R:

library(dplyr)
library(ggplot2)
library(gridExtra)
library(grid)

data <-
  read.csv("https://raw.githubusercontent.com/datadista/datasets/master/COVID%2019/nacional_covid19.csv",
           check.names=FALSE)

colnames(data)[1] = 'fecha'

data$fecha <- as.Date(data$fecha, "%Y-%m-%d")
data$Casos.nuevos <- c( NA, diff(data$casos_pcr))
data$Altas.nuevas <- c( NA, diff(data$Altas))
data$Fallecimientos.nuevos <- c( NA, diff(data$fallecimientos))
data$UCI.nuevos <- c(NA, diff(data$ingresos_uci))

Un ejemplo claro, por algún motivo en Windows el nombre de la variable fecha contiene algún carácter extraño, modifico el nombre con colnames, esta acción queda reflejada en un proceso. Si se producen cambios en los campos de la tabla quedan recogidos en un código que puede ser reproducible por cualquier persona de la organización, podemos (es conveniente) llevar un control de versiones del código por si se produjeran cambios. Las fórmulas empleadas no distan de las que usamos en Excel y si se desconoce que fórmula usar R tiene una comunidad de usuarios por detrás que estarán dispuestos a ayudar, eso no pasa con SAS (por ejemplo).

Continuamos y deseamos realizar un gráfico con los casos nuevos por día para buscar posibles anomalías:

p1 <- ggplot(data,aes(x=fecha,y=Casos.nuevos),na.rm = TRUE) +
  geom_line() +
  geom_smooth() + ggtitle("Casos nuevos") + theme_minimal()

p2 <- ggplot(data,aes(x=fecha,y=Fallecimientos.nuevos),na.rm = TRUE) +
  geom_line() + geom_smooth() + ggtitle("Fallecimientos nuevos") + theme_minimal()

p3 <- ggplot(data,aes(x=fecha,y=UCI.nuevos),na.rm = TRUE) + geom_line() +
  geom_smooth() + ggtitle("UCI nuevos") + theme_minimal()

p <- grid.arrange(p1, p2, p3, ncol=2)

Este paso puede ser más duro, hacer gráficos en R ya no es seleccionar datos y tipo de gráfico pero en dos líneas hemos hecho un gráfico, es el mismo para las tres variables que deseamos analizar y deseamos unir todos los gráficos de ggplot en uno lo hacemos con una función que se llama grid.arrange. Esto lo haces una vez, no lo repites siempre y si lo intentas hacer con Visual Basic lo conseguirás pero otro compañero no podrá abrir las macros, a otro no le funciona,… -¿esto quién lo hizo? -¡pero si ya está jubilado!

Ahora lo que hacemos es poner estos datos en un Excel para hacer nuestros resúmenes, nuestro cuadro de mando con tablas dinámicas,… lo que sea para lo que Excel si es una gran herramienta (si, he puesto cuadros de mando). Este código también es más complejo pero siempre es el mismo:

library(openxlsx)

excel <- createWorkbook()
addWorksheet(excel, sheetName = "DatosCOVID")

print(p)
writeDataTable(excel,sheet = "DatosCOVID", x=data)
insertPlot(excel, sheet = "DatosCOVID", startRow = 2, startCol = length(data) + 2, )

saveWorkbook(excel, "C:/temp/datos_covid.xlsx")
remove(excel)

Creamos un “entorno excel” con createWorkbook al que podemos añadir hojas con addWoksheet y en esas hojas añadimos elementos, una tabla con writeDataTable y un gráfico, que necesitamos imprimir previamente, con insertPlot. Vemos que los datos que publica el Ministerio sobre el Covid tienen anomalías, parece ser que un día concreto resucitaron 1.900 personas, podríamos ir al código y eliminarlo.

Para un usuario de Excel pueden parecer complicadas las fórmulas sin embargo podemos evitar problemas referenciando celdas, modificando celdas, borrando elementos,… Y no es más complejo que una fórmula de Excel.

Leer una tabla en PDF con Excel (a través de R)

Hay situaciones en las que tenemos datos en pdf y los necesitamos exportar a Excel para graficar o cruzar esos datos. En ocasiones es mejor meter esos datos a mano, otras veces disponemos de un software de pago que nos permite realizar esa tarea y también hay páginas web que nos permiten cambiar el formato del pdf. En nuestro caso simplemente necesitamos una tabla que está en formato pdf para disponer de esos datos en Excel, más sencillo, copiar del pdf y pegar en Excel esa tabla. Si está en texto el pdf se puede complicar y si está en modo imagen más. Si empleas windows en tu esta entrada puede ser de utilidad ya que usando de R podrás hacer está tarea de copiar pdf y pegar Excel de un modo más rápido, te cuento paso por paso en video.

El primer paso será la instalación de R y RStudio:

Una vez hemos disponemos de R y Rstudio en nuestro equipo descarga este programa en R que te permitirá exportar una tabla a R con las instrucciones que resumo a continuación:

Esta entrada del blog quiero que sea dinámica, me gustaría mejorar el proceso porque tiene posibilidades de dar a conocer R a personas sin conocimientos de programación. Podemos crear desde una aplicación hasta leer url. Saludos.

Trucos Excel. Convertir texto en un resultado o fórmula

Es posible que en Excel tengamos fórmulas que provengan de la concatenación de algunas celdas y necesitemos ejecutar o crear una fórmula. En este caso he encontrado una función muy sencilla que podemos añadir a nuestro libro de macros personal o directamente a nuestro libro. La función es genial y sencilla y proviene de este foro:

https://www.mrexcel.com/board/threads/eval-function-without-the-morefunc-add-in.62067/

Function Eval(Ref As String)
Application.Volatile
Eval = Evaluate(Ref)
End Function

Tiene muchos años pero podéis comprobar que funciona perfectamente. Esta solución me parece más elegante que otras, aunque es probable que MS haya optado por incluir una función que haga esta labor, lo desconozco. Saludos.

Truco Excel. Calculadora desde la barra de herramientas de acceso rápido

Hace tiempo que no ponía trucos de Excel y el de hoy es muy sencillo pero que a mi me ha sido de especial utilidad, un acceso a la calculadora para esos cálculos rápidos que siempre he hecho con la calculadora de sobre mesa. Se trata de añadir un acceso a la Calculadora de Windows desde la barra de herramientas de acceso rápido. Vamos a personalizar la barra:

Y desde ese menú nos vamos a Más comandos. Se abre un cuadro de diálogo que nos sugiere elementos a añadir pero la calculadora saldrá si queremos ver todos los elementos disponibles:

Seleccionamos la calculadora y ya tenemos nuestro acceso rápido. Evidentemente podéis añadir muchos más accesos a esa barra, en este caso problemas con mi vieja calculadora han hecho que sea necesario el uso de la calculadora de Windows.

Medias ponderadas en Excel. Crear tu propia función

Hace años conocí a una persona que no sabía hacer medias ponderadas con Excel, hoy esa persona es una referencia dentro de este ecosistema de Inteligencia Artificial, Big Data, Machine Learning, Unsupervised Learning,… total, una referencia en la venta de humo porque me imagino que seguirá sin saber hacer una media ponderada en Excel con el SUMAPRODUCTO y por eso realizo esta entrada en homenaje a esas grandes locomotoras que echan humo y más humo pero que ahí siguen. Además también es útil para varias cosas más como:

  • Crear nuestra propia función en Excel
  • Emplear rangos en funciones de Excel
  • Crear sumas acumuladas con un bucle en nuestra función
  • Emplear funciones propias de Excel en nuestra función de visual basic

La función es sencilla y replica la forma habitual de hacer medias ponderadas en Excel con el SUMAPRODUCTO del dato del que deseamos calcular la media por el campo de ponderación dividido por la suma del campo de ponderación:

Public Function MEDIAPONDERADA(Valor As Range, Ponderacion As Range)

 If Valor.Rows.Count <> Ponderacion.Rows.Count Then
 Sample = "Tamaños distintos de rango"
 Exit Function
 
 ElseIf Valor.Rows.Count = 1 Then
 Sample = "Solo un número no se puede"
 Exit Function
 End If

 acum = 0
 For i = 1 To Valor.Rows.Count
   dato = Valor(i) * Ponderacion(i)
   acum = acum + dato
 Next
 
 MEDIAPONDERADA = acum / Excel.WorksheetFunction.Sum(Ponderacion)

End Function

Esta función la ponéis en el libro de macros personal y cuando escribáis =MEDIAPONDERADA ya la tendréis a vuestra disposición en todas las sesiones de Excel. Saludos.

Truco Excel. Producto cartesiano de dos campos

Hacía tiempo que no ponía trucos en Excel y hoy os traigo un truco que puede ser de utilidad cuando tienes que hacer combinaciones. Se trata de realizar el producto cartesiano mediante una macro de Excel, además os pongo el enlace al propio Excel para que podáis rellenar los campos a cruzar. No creo que haga falta describir que es un producto cartesiano de dos campospero de forma resumida se puede decir que es crear el total de pares de esos dos campos, un todos con todos, es útil cuando quieres hacer combinaciones (como ya he dicho). La macro en Visual Basic se podrá hacer mejor, pero a mi se me ha ocurrido hacer un triple bucle, probablemente se pueda hacer con n campos pero si tenéis que realizar productos cartesianos más complejos es preferible que lo hagáis con otra herramienta. El código empleado es este:

Sub ProductoCartesiano()
    
L1 = Range(Range("A2"), Range("A2").End(xlDown)).Rows.Count
L2 = Range(Range("B2"), Range("B2").End(xlDown)).Rows.Count

Cells(1, 4) = Cells(1, 1)
Cells(1, 5) = Cells(1, 2)

i = 2
j = 1


While i <= L1 * L2
While j <= L1
k = 1
While k <= L2

Cells(i, 4) = Cells(j + 1, 1)
Cells(i, 5) = Cells(k + 1, 2)

k = k + 1
If k > L2 Then j = j + 1
i = i + 1

Wend
Wend
Wend
End Sub

Nada emocionante pero tiene su “talento”. Si no queréis complicaros la vida directamente podéis descargar del siguiente enlace la hoja del cálculo que realiza este proceso:

Producto_cartesiano_excelV0

Saludos.

Truco Excel. Transponer una fila en varias columnas con DESREF

Creo que alguna vez me lo han preguntado. Se trata de tranponer en Excel el contenido de una fila en varias columnas, como es habitual (sobre todo si escribo yo) una imagen vale más que mil palabras:

Transponer varias columnas

En este caso se trata de pasar de una fila a 3 columnas por lo que se trata de que la función DESREF tiene que moverse en función del elemento que va a transpone. En este caso empezando desde A1 tenemos que generar un autonumérico para las columnas que se ha de mover de 3 en 3 por lo que multiplicaremos por 3 y sumaremos la columna:

Para el primer elemento: =DESREF($A$1;0;(FILA(A1)-FILA($A$1))*3)
Para el segundo elemento: =DESREF($A$1;0;(FILA(B1)-FILA($A$1))*3+1)
Para el tercer elemento: =DESREF($A$1;0;(FILA(C1)-FILA($A$1))*3+2)

Ya lo veis, se mueve de 3 en 3 la columna 0 el primer elemento la 1 el segundo y el 2 el tercero.

Mapas municipales de España con Excel y QGIS

mapa_españa_municipal7
Un tweet a @r_vaquerizo me preguntaba por mapas para representar datos a nivel municipal en España. Estas cuestiones las suelen plantear porque los mapas en Excel que hay publicados en el blog están muy extendidos y quieren herramientas similares pero a otro nivel, ya sea comarcal, municipal, código postal… Hacer estos mapas con Excel es muy complicado porque estos mapas no dejan de ser un gran rompecabezas que colocamos en Excel. Sin embargo podemos emplear QGIS para realizar este tipo de mapas, QGIS es libre, tiene muchas posibilidades y ‘comunica’ a la perfección con Excel y como ejemplo de ello vamos a realizar un mapa con la población total de la provincia de Zaragoza. Para esta tarea el mejor mapa que he encontrado es este, no tiene restricciones de uso pero sobre todo es muy simple y tiene un campo código de municipio que nos permite cruzar a la perfección con los datos del INE. Comencemos a trabajar.

Descargado el mapa vemos que tenemos 7 archivos que contienen información sobre el mapa, los dejamos en una carpeta y abrimos QGIS, aquí creamos un nuevo proyecto y añadimos una capa vectorial entonces nos pide seleccionar un archivo a abrir y de ese mapa que nos hemos descargado seleccionamos el archivo *.shp y obtendremos:

mapa_españa_municipal1

Tenemos un mapa de municipios de toda España pero necesitamos seleccionar Zaragoza, sobre el panel de capas duplicamos esa capa Municipios ETRS89_30N y con el botón derecho del ratón podemos realizar un filtro Seguir leyendo Mapas municipales de España con Excel y QGIS

Truco Excel. Unir todos los libros en una hoja

unir_excel1

Los trucos Excel referentes a la unión de varios libros en uno tienen mucho éxito en esta web, además era necesario crear una versión que uniera de forma horizontal. No es una unión como la pueda hacer Power Query de anexar tablas con cierto sentido teniendo en cuenta el nombre de las columnas y demás, se trata
de unir todas las celdas de un conjunto de libros de forma horizontal en otro libro resultante como ilustra la figura de arriba. Se unirán todos los campos unos encima de otros independientemente de si se llaman igual o no, si queremos anexar tablas es recomendable usar herramientas más específicas. El funcionamiento es muy sencillo pero
lo vamos a ilustrar con imágenes, el primer paso es pulsar directamente el botón y seleccionar los archivos a unir:

unir_excel2

Ahora sólo tenemos que especificar el archivo de destino, puede existir o no, el proceso lo sustituye:

unir_excel3

Y et voilá! Ya tenemos nuestro archivo resultante Seguir leyendo Truco Excel. Unir todos los libros en una hoja

Truco Excel. Actualizar el filtro de todas las tablas dinámicas de mi libro

Traigo hoy al blog una macro de Excel que nos permite recorrer todas las hojas de un libro y dentro de las hojas nos permite recorrer todas las tablas dinámicas y actualizar un campo. Cuando tenemos un informe que se basa en tablas dinámicas y tiene una actualización mensual nos podemos encontrar con la necesidad de cambiar sólo un elemento de la tabla dinámica para actualizar el informe. Este era el caso de mi compañera, hay una entrada en el blog que ya trataba el tema pero esta nueva macro supone otra vuelta de tuerca sobre ella, no sólo recorre y actualiza todas las tablas dinámicas de una hoja, además lo hace de todo el libro. El código de Visual Basic para Excel es:

[sourcecode language=”vb”]
Sub filtros()
‘Macro creada por www.analisisydecision.es

Dim pt As PivotTable
Dim actual, nuevo As String
actual = 201701
nuevo = 201702

For i = 1 To Worksheets.Count

Sheets(i).Activate
For Each pt In ActiveSheet.PivotTables
pt.PivotFields("mes").EnableMultiplePageItems = True
With pt.PivotFields("mes")
.PivotItems(nuevo).Visible = True
.PivotItems(actual).Visible = False
End With
Next pt
Next i
[/sourcecode]

En PivotFields ponemos el nombre del campo que deseamos actualizar. Evidentemente necesitamos esconder el valor actual y poner el valor nuevo recorriendo todas las hojas con el bucle For i = 1 To Worksheets.Count y recorriendo todas las tablas dinámicas de cada hoja con For Each pt In ActiveSheet.PivotTables. Lo que si necesitó estar activo es seleccionar varios elementos del campo que deseamos actualizar y por ello fue necesario poner EnableMultiplePageItems = True no llegamos a entender el motivo pero es importante que “Seleccionar varios elementos” esté activo en la tabla dinámica. Espero que estas pocas líneas de código os sean de utilidad, por aquí han automatizado mucho trabajo manual. Saludos.