Archivos de la categoría Trucos

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.

Macro SAS. Número de variables de un dataset en una macro

Una macro que nos permite saber el número de variables que tiene un conjunto de datos SAS. Es una petición de una lectora y la macro es análoga a otra que ya pusimos en el blog allá por 2010. Veamos cómo funciona:

%macro numvars(datos);
 %global numvars;
 /*ABRIMOS EL CONJUNTO DE DATOS PARA VER SUS CARACTERISTICAS*/
 %let datosid = %sysfunc(open(&datos));
 /*SI ESTA ABIERTO ENTONCES LA FUNCION ATTRN NOS DA EL NUMERO DE VARIABLES*/
 %if &datosid %then %do;
 %let numvars =%sysfunc(attrn(&datosid,nvars));
 /*CERRAMOS EL CONJUNTO DE DATOS*/
 %let rc = %sysfunc(close(&datosid));%end;
 %mend;
data ejemplo;
 a=1;
 b=2;
 c=3;
 d=4;
 f=5;
 g=6;
 run;
%numvars(ejemplo);
 %put _user_;

Utilizamos las funciones I/O de SAS, en concreto ATTRN que unido a NVARS nos permite saber el número de variables que tiene un dataset, el número de variables lo ponemos en la macrovariable global &numvars..

Saludos.

Macro SAS. Crear variables dummy desde una variable categórica

En alguna ocasión ya he conjugado el verbo dumificar y preparando una segmentación he creado una macro SAS que genera variables dummy a partir de variables categóricas, es decir, si la variable A toma valores 1, 2 y 3 tendría que generar A_1 con valor 1 si A toma 1 y con valor 0 en caso contrario, A_2 tiene valor 1 si A es igual a 2 y A_3 tiene valor 1 si A es igual a 3, no es complicado de comprender, pasamos de una variable con 3 niveles a 3 variables con valores 0 o 1. Para esto podemos emplear arrays o la siguiente macro:

%macro dumificar(varib, grupos, mv);
%global &mv.;
data instruccion;
do i=1 to &grupos.;

instruccion="&varib._"||compress(put(i,3.))||
"=0; IF &varib.="||put(i,3.)||" THEN &varib._"||compress(put(i,3.))||"=1";

output;
end;
run;

proc sql noprint ;
select instruccion into:&mv. separated by ";"
from instruccion;
quit;
proc delete data=instruccion;quit;
%mend;

La intención es crear de forma automática un código del tipo VARIABLE_1=0; IF VARIABLE = 2 THEN VARIABLE_1=1; La macro tiene 3 parámetros, VARIB que es la variable que deseamos dumificar, GRUPOS que es el número de grupos de la variable que vamos a transformar en dummies y MV que es el nombre de la macrovariable que tiene el código SAS que generamos de forma automática. A modo de ejemplo de uso:

data aleatorios;
do i=1 to 1000 ;
datoA = min(ranpoi(2,4),9);
if datoA <= 3 then datoB = ranpoi(89,2);
else if datoA <= 5 then datoB = min(ranpoi(89,6),6);
else datoB = min(ranpoi(89,2),3);
output;
end;
drop i;
run;

%dumificar(datoA, 9, dumifica_datoA);
%dumificar(datoB, 8, dumifica_datoB);

data aleatorios;
set aleatorios;
&dumifica_datoA.;
&dumifica_datoB.;
run;

Espero que os sea de utilidad esta macro. 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 para EMB Emblem. Cambiar el nivel base de un factor

Un buen truco que me han descubierto hoy para los usuarios de EMB Emblem, como cambiar el nivel base de un factor de datos sin necesidad de pasar por los datos (habitualmente SAS) o sin hacerlo a posteriori (habitualmente Excel y lo que hacía el ahora escribiente). Cuando se generan los datos se genera el fichero binario *.BID y el fichero que se emplea para leer ese fichero *.FAC; para alterar el nivel base debemos abrir este archivo *.FAC con un block de notas o cualquier editor de texto plano. Al abrirlo tendremos lo siguiente:

XXXXXXXXXX        --> ES EL NOMBRE DEL ARCHIVO
99                                 -->ES EL NÚMERO DE FACTORES
XXXXXXXXXX.Bid --> ES EL NOMBRE DEL ARCHIVO BINARIO CON LOS DATOS
0
99999999                   --> ES EL NÚMERO DE REGISTROS
No. Levels Base         --> ES LA DESCRIPCION DE LO QUE VIENE A CONTINUACIÓN
XXXXXXXXXXXX  --> NOMBRE DE UN FACTOR
999                 99         --> EL PRIMERO ES EL NÚMERO DE NIVELES Y EL SEGUNDO ES EL NIVEL BASE

ASÍ PARA TODOS LOS FACTORES. Si deseamos cambiar el nivel base de un factor sólo tenemos que cambiar ese segundo número que hay tras cada definición de los factores. Un truco muy sencillo y que será de utilidad para aquellos que usáis EMB Emblem. 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.