Archivo de la categoría: Excel

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.

Nuevo mapa por provincias en Excel de España. Actualiza los colores en RGB

Mapa_excel_provincias_españa_nuevo

A raiz de esta entrada Otto F. Wagner me envió hace unos meses una nueva versión del conocido mapa de provincias de España en Excel y tenía pendiente subirlo a la web. La diferencia con los anteriores es que puedes elegir el color de inicio y el color de fin jugando con RGB:

Mapa_excel_provincias_españa_nuevo2

——Descarga aquí la nueva versión en formato ZIP———–

Truco Excel. Repetir un campo a partir de otro que define el número de repeticiones

En realidad necesitamos repetir filas, pero el número de repeticiones está parametrizado por otro campo. Partimos de un rango donde la primera columna es un valor que necesita ser repetido el número de filas que nos indica la segunda columna. Es una duda que planteaba un lector del blog:

Hola, a ver si me podeis ayudar,
quiero repetir cada fila el número de veces que hay en cada celda que le corresponde.
Es decir tengo una variable Nombre (Columna A) y otra Frecuencia (Columna B)
Nombre Frecuencia
A 5
B 8
C 25
D 12
… ….

La idea es repetir la la Fila A, 5 veces, la fila B, 8 veces y así sucesivamente.
En realidad es lo inverso a crear una tabla de frecuencias desde una matriz de datos.

Tenía en la nevera un truco de Excel que puede hacer esta tarea y que también subiré al blog en los próximos días, en realidad esta entrada y la siguiente son modificaciones de un anterior truco Excel que subí al blog. En esta imagen podéis ver lo que hace:

excel_repite_filas

La macro que lo realiza es esta:

[source languaje=”vb”]
Sub rango_columnas2()

Dim rango, celda As Variant
Dim i As Long, j As Long, k As Long
Dim col As Long

rango = Application.InputBox(Prompt:="Seleccione el rango a repetir sin cabeceras", Type:=8)
Set celda = Application.InputBox(Prompt:="Seleccione donde quiere poner los datos", Type:=8)

‘Esta es la parte que permite ubicar la salida
celda.Select
k = 0

‘Esto recorre el rango
For i = 1 To UBound(rango, 1)
For j = 1 To rango(i, 2)
ActiveCell.Offset(k, 0).Value = rango(i, 1)
k = k + 1
Next
Next

End Sub
[/source]

Se ejecutan 2 cuadros con el método Application.InputBox, en el primero seleccionamos el rango de datos sin cabeceras y en el segundo indicamos donde queremos que salgan los datos. Se trata de recorrer el rango y pararse a repetir las primera columna del rango tantas veces como lo indica la segunda columna. Un proceso sencillo con Visual Basic pero que sería interesante replicar con funciones de Excel, en este caso creo que INDIRECTO sería la más adecuada. En el siguiente enlace podéis descargar el ejemplo:

repite_filas

En siguientes fechas daré más vueltas a este bucle para realizar transposiciones de datos. Saludos.

 

Truco Excel. Pasar un rango de varias columnas a una

Macro de Excel que nos permite pasar de varias columnas a una sola. De momento no es una función, es un código que sorprende por su sencillez:

Sub rango_columnas()

Dim rango As Variant
Dim i As Long, j As Long, k As Long
Dim col As Long

rango = Selection.Value

'Esta es la parte que permite ubicar la salida
col = Selection.Column
k = Selection.Row

'Esto recorre el rango y realiza la trasposición
For i = 1 To UBound(rango, 1)
    For j = 1 To UBound(rango, 2)
        Cells(k, col + UBound(rango, 2)).Value = rango(i, j)
        k = k + 1
    Next
Next

End Sub

Este código lo ponéis tal cual en vuestro Excel y os ilustro a continuación sobre su funcionamiento. Lo primero es seleccionar el rango de columnas que deseamos transponer:

varias_columnas_a_una_excel1

Ahora sólo ejecutamos la macro rango_columnas:

varias_columnas_a_una_excel2

Y aparece justo al lado del rango que deseamos transponer a una sola columna:

varias_columnas_a_una_excel3

En este caso el resultado lo obtenemos en la columna F. Jugando con los índices i y j de nuestra macro podremos modificar la forma de la transposición. Me parece una macro útil y por eso lo comparto con vosotros. Saludos.

Trucos Excel. Mapa de Colombia por departamentos

mapa-colombia-excel

El mapa Excel de Colombia por departamentos era tarea pendiente, el modo en el que se ha hecho es el habitual y por ello lo primero que hay que hacer es citar al usuario de Wikipedia  Shadowxfox – Trabajo propio, CC BY-SA 4.0, https://commons.wikimedia.org/w/index.php?curid=44930910 es importante comentar que seleccioné este por el tamaño, en Excel se ve bien poniendo el zoom al 35% (¡!) y es que cada vez veo menos. El mapa puede representar 5 grupos  y estos grupos se pondrán en la columna C donde podemos cruzar por nombre. Comentar que falta el departamento de las islas de San Andrés y Providencia porque no sabía muy bien como ubicarlas y como representarlas en el mapa. 

Así pues el funcionamiento no puede ser más sencillo. Podemos pintar hasta 5 grupos, los colores de estos 5 grupos los podemos seleccionar en las correspondientes celdas de la columna H. Los grupos que clasifican los departamentos los pondremos en la columna C y con el botón pintar se realiza el mapa. Un esquema de los elementos del Excel Seguir leyendo Trucos Excel. Mapa de Colombia por departamentos