Archivos de la categoría Excel

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:

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

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 Sigue leyendo Trucos Excel. Mapa de Colombia por departamentos

Nuevo y muy mejorado mapa de España por provincias con Excel

Nuevo_mapa_españa1

Hacía tiempo que no publicaba un mapa de España de Excel, aquí tenéis una nueva versión que mejora mucho  a las anteriores. La primera mejora y la que más destaca es que nos permite incluir datos, además ponemos los nombres de las provincias para todos aquellos que dominen poco la geografía española. Podemos pintar hasta 4 datos distintos que se pueden seleccionar en el desplegable que tenéis arriba. Ahora los colores van en dos escalas que podéis seleccionar vosotros:

Nuevo_mapa_españa_excel2

A la hora de meter los datos a nivel provincial es necesario ir a la hoja datos_mapa en ella tenéis los 4 datos que podéis pintar, estos datos irán en un ranking que  a la postre asigna colores a los shapes que componen el conjunto de imágenes que hace el mapa de Excel Sigue leyendo Nuevo y muy mejorado mapa de España por provincias con Excel

Truco Excel. Abrir múltiples libros de Excel en distintas hojas de un nuevo libro

Hace tiempo escribí sobre el método de Excel GetOpenFilename para abrir archivos desde Excel a través del explorador de archivos ahora le damos una nueva vuelta de tuerca a aquella entrada y de forma simple podemos abrir múltiples libros de Excel que además se añadirán de forma sucesiva en un nuevo libro. En este link podéis descargaros el archivo y como veréis no tiene nada. Un botón Abrir Excel realiza el proceso, se abre el explorador de Windows y podéis seleccionar múltiples archivos Excel que se almacenan en un array. La macro a ejecutar es la siguiente:

Sub abre_libros()
Dim Hoja As Object, rango As String
Dim libros As Variant

'Ventana con archivos
libros = Application.GetOpenFilename _
("Archivos Excel (*.xls*), *.xls*", 2, "Abrir archivos", , True)
'Es necesario seleccionar archivos
If IsArray(libros) Then
'Creamos un libro nuevo
Workbooks.Add
libro_actual = ActiveWorkbook.Name
'Ahora pegamos las hojas
For i = LBound(libros) To UBound(libros)
Workbooks.Open libros(i)
libro_nuevo = ActiveWorkbook.Name
For Each Hoja In ActiveWorkbook.Sheets
Hoja.Copy after:=Workbooks(libro_actual).Sheets(Workbooks(libro_actual).Sheets.Count)
Next
Workbooks(libro_nuevo).Close False
Next
End If
End Sub

No se me ocurrió como hacer el código más sencillo y al final lo que hace es recorrer el array de libros que abre y añadir las hojas sobre un libro nuevo. Tiene algunas limitaciones en la forma en la que pega las nuevas hojas pero nada que pueda ser muy complicado de solucionar. Ahora tenéis que mejorar la entrada más visitada de este blog para que pueda unir en uno los Excel que se abren.

DESREF para trasponer en Excel varias columnas

Hoy han planteado una duda en el blog que me ha parecido interesante porque es un buen ejemplo de uso de la función DESREF de Excel para trasponer filas en columnas con cierto criterio. La idea que planteaba es realizar una trasposición de 3 en 3 elementos como indica en la figura:

ejemplo_trasponer_desref

Para entender como funciona la función DESREF lo mejor es pensar en lo siguiente: fijado un punto nos movemos x-filas ; x-columnas. En el ejemplo concreto el punto inicial está en la celda $A$1;0;0 si nos desplazamos a la derecha haremos $A$1;1;0 es decir, nos movemos a la derecha +1 y siempre mantenemos la columna porque estamos trasponiendo filas en columnas. Las coordenadas de la fila para la trasposición en nuestro ejemplo quedarían:

+0 +1 +2
+3 +4 +5
+6 +7 +8
+9 +10 +11

 

En este caso se ha hecho:

ejemplo_trasponer_desref_2

La fórmula lee un dato que va de 3 en 3 a la izquierda y suma +1 o +2 si es el primer o el segundo elemento que deseamos en columna. Evidentemente esta fórmula es mejorable pero es un buen ejemplo de  DESREF en Excel. Saludos.

Truco Excel. Función para identificar el color de una celda

En alguna entrada anterior ya vimos como identificar el color de una celda con Excel. Recientemente me trasladaron una duda, se trataba de realizar una acción determinada si el color de la celda era distinto. Algo muy habitual cuando realizas alguna validación visual y marcas celdas con otro color. La solución es sencilla, se trata de crear nuestra propia función que identifique el color de la celda:

Function color_celda(celda As Range)
color_celda = celda.Interior.Color
End Function

En este caso podríamos realizar funciones del tipo =SI(color_celda(A1)<> 16777215; ACCION1; ACCION2) además podemos darle otra vuelta de tuerca y si deseamos ordenar por colores podemos hacer:

Function color_orden(celda As Range)
color_orden = celda.Interior.ColorIndex
End Function

Establecemos un orden de colores y podemos realizar más acciones. Un truco sencillo que puede ayudar a las intervenciones manuales sobre nuestros libros de Excel. Saludos.

Truco Excel. Insertar imágenes con Visual Basic

Si deseáis insertar una imagen en Excel desde Visual Basic mediante una macro tenéis que ejecutar un código similar a este:


Sub inserta_imagen(hoja)
Sheets(hoja).Select
ActiveSheet.Pictures.Insert("C:\grafico.png").Select
With Selection.ShapeRange
.Top = Range("B5").Top
.Left = Range("B5").Left
End With
End Sub

En una hoja de vuestro libro de Excel insertáis el archivo especificado. Luego lo ubicáis donde sea necesario. En el ejemplo que os he puesto en la celda B5. Truco sencillo, saludos.

Truco Excel. Gráficos de dispersión que identifican los puntos

 

Gráfico Dispersión con colores Excel
Gráfico Dispersión con colores Excel

Yo no sé hacer gráficos de dispersión con Excel en los que se identificaran los puntos mediante un color, es necesario programar en visual basic para hacerlo. Imagino que se podrá hacer de forma más elegante pero hoy quería mostraros que esa tarea se puede llevar a cabo mediante macros. Los datos que tenemos tienen un valor para X, un valor para Y y un valor que nos identifica el grupo de cada registro. En el ejemplo que os voy a adjuntar se identifican dentro de la nube de puntos aleatorios 2 grupos marcados con un 0 o un 1, por lo cual tendremos 2 colores para identificar esos puntos. ¿Cómo variamos los colores? Sencillo, una macro recorre punto a punto y pone otro color si pertenece al grupo 1, los que pertenezcan al grupo 0 tendrán el color por defecto. Este color por defecto será el negro y el color para los 1 será el rojo. El código de la macro es:

Sub colores()
'
' colores macro creada por www.analisisydecision.es

On Error GoTo noselecciona

'Establecemos el color inicial
ActiveChart.SeriesCollection(1).Select
Selection.Format.Fill.ForeColor.RGB = RGB(50, 50, 50)

'Modificamos aquellos puntos que tienen un 1
para = 0
fila = 2
Do While para = 0
If Cells(fila, 3) = 1 Then ActiveChart.SeriesCollection(1).Points(fila - 1).Format.Fill.ForeColor.RGB = RGB(250, 50, 0)
If Cells(fila, 1) = "" Then para = 1
fila = fila + 1
Loop

noselecciona:
If Err Then MsgBox ("Ha de seleccionar el gráfico")

End Sub

Descárgate el archivo

Hay un control de errores por si no tenemos ningún gráfico seleccionado. Una vez seleccionado el gráfico damos a toda la serie el color negro, se emplea escala RGB para asignar colores. Después vamos a recorrer punto por punto hasta que no haya datos y si encontramos un 1 en la variable grupo entonces el RGB será de color rojo. Cuando ya no hay más observaciones el proceso se detiene.

Como es habitual, os enseño a pescar, es evidente que se pueden hacer más grupos, que se pueden seleccionar los colores, etc. Creo que es bastante sencillo si buscamos en el blog y si entendemos esta simple macro. Saludos.

Truco Excel. Actualizar los filtros de una tabla dinámica con Visual basic

Imaginad que tenéis que cambiar uno o varios filtros de todas las tablas dinámicas de una hoja y cada una de las tablas dinámicas tiene un nombre distinto o hay un número distinto de tablas dinámicas en cada hoja. Eso dificulta a la hora de crear un bucle para la modificación de filtros. Pues este truco de Excel os permitirá actualizar un filtro de una fecha (o cualquier otro) para todas las tablas dinámicas de una hoja. El código es muy sencillo y no creo necesario subiros a la web algún ejemplo:

Sub filtros()
'Macro creada por www.analisisydecision.es
Dim pt As PivotTable

For Each pt In ActiveSheet.PivotTables

With pt.PivotFields("fecha")
.PivotItems("Dec-10").Visible = False
.PivotItems("Mar-10").Visible = True
End With
Next pt

End Sub

Para cada tabla dinámica (pivot table) actualiza el campo fecha, quita diciembre de 2010 y pone marzo de 2010. Es importante destacar que el filtro emplea fechas en lengua inglesa aunque vosotros en la tabla dinámica la veáis en lengua española. Esto es importante porque más de uno se ha vuelto loco con ese problema en las macros que modifican tablas dinámicas. También se puede plantear una versión que actualice todas las hojas de un libro. Espero que sea de utilidad. Saludos.