Excel

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

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

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

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

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

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

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.

Yo no sé hacergrá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: ...

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

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