Macro para borrar los renglones que no pertenezcan a una categoría que nos interesa

Es muy común que tenemos una base de este tipo:

Nombre   Calificación   Materia
Juan         10                  Química
Juan           8                  Español
Juan           7                  Ciencias Sociales
Pedro         6                  Química
Pedro         6                  Español
Pedro        10                  Ciencias Sociales
Martha      10                  Química
Martha      10                  Español
Martha      10                  Ciencias Sociales
Juana          6                  Química
Juana          7                   Español
Juana          8                  Ciencias Sociales

Es probable que sólo queremos tener las calificaciones de Juan y Juana. Es decir, esto:


Nombre   Calificación   Materia
Juan         10                  Química
Juan           8                  Español
Juan           7                  Ciencias Sociales
Juana          6                  Química
Juana          7                   Español
Juana          8                  Ciencias Sociales

Para ello se puede hacer una macro. Pondremos un ejemplo más complicado. Tenemos un listado de municipios con su clave (en columna A) que son los únicos que queremos que queden en un listado. Queremos que los municipios que no son alguno de ellos, sean borrados.

Es decir, tenemos algo como esto:

Pero no queremos el 15002, por ejemplo. Al final quisieramos que un filtro nos mostrara no esto:


Sino esto:

 Bien, considerando que hay en esta base 190,490 registros, deberíamos almacenar el siqguiente programa:


-------------------------------------

Sub BorraCasosAjenos()
'
' Macro1 Macro
'
For N = 1 To 190490
'

Valor = ActiveCell.Value
'Adelante los casos que SI queremos
'15002
'15011
'15013
'15020
'15023
'15024
'15025
'15028
'15029
'15030
'15031
'15033
'15037
'15039
'15044
'15053
'15057
'15058
'15059
'15060
'15069
'15070
'15081
'15091
'15092
'15093
'15095
'15099
'15100
'15104
'15108
'15109
'15120
'15121
'15122
'15125


Select Case Valor

Case "15002", "15011", "15013", "15020", "15023", "15024", "15025", "15028", "15029", "15030", "15031", "15033", "15037"
Borra = "NO"

Case "15039", "15044", "15053", "15057", "15058", "15059", "15060", "15069", "15070", "15081", "15091", "15092", "15093"
Borra = "NO"

Case "15095", "15099", "15100", 15104, "15108", "15109", "15120", "15121", "15122", "15125"
Borra = "NO"

Case Else
Borra = "SI"

End Select

If Borra = "SI" Then

    Selection.EntireRow.Delete
    Else
    ActiveCell.Offset(1, 0).Range("A1").Select
   
End If
   

Next

End Sub
-----------------------------------------------

Se apreciará que lo que estamos haciendo es muy sencillo,
1. Primero con el for decimos cuantas veces se repetirá todo l oque está entre For y Next, es decir 190490 veces,
2. La instrucción Valor=ActiveCell.Value, almacena en la variable Valor, lo que esté en ese momento en la celda activa (la que parpadea)
3. Select Case Valor, lo que hace es decirle al programa que tomará en cuenta el lo que está contenido en la variable Valor para comparar los siguientes casos y tomar decisiones con respecto a ello.
4.

Case "15002", "15011", "15013", "15020", "15023", "15024", "15025", "15028", "15029", "15030", "15031", "15033", "15037"
Borra = "NO"
Lo que hace es decir que en caso de que valor valga "15002", "15011",...,"15037" entonces una variable llamada Borra va a valer "NO".

Esto de Case se repite para todos los casos que queremos conservar, podrían estár en el mismo Case pero se haría más confuso.

5. Case Else, hace que cualquier otro caso no previsto haga que Borra="SI"

6.

If Borra = "SI" Then

    Selection.EntireRow.Delete
    Else
    ActiveCell.Offset(1, 0).Range("A1").Select
   
End If

Estas instrucciones lo que hacen es que si Valor ocaiona que Borra sea "SI", es decir, si el valor no es de la lista, lo borre. De lo contrario (luego de Else) la celda activa baja a la casilla inferior.

Al terminar tenemos una base con sólo los valores que nos interesan. Es importante señalar que se debe correr la macro a partir de la casilla de la columna que contiene los valores a comparar.

Pasar a formato MANOVA

A veces, cuando se hace una corrida de MANOVA, los datos no sirven tal cual. Por ejemplo, en este caso que sigue tenemos un listado de ciduades y su gasto promedio en transporte para cada año. Pero es una base relacional, nosotros necesitamos una donde el año sea una variable, o mejor dicho un factor, es decir, no estará en una columna. Siendo así, la tabla como está, requiere un ajuste:

tenemos las variables 

id. 

CVE_LOC_SU 

NOM_LOC_SU 

POB2010 

Hog2000 

TotTr2000 

TtTrPr2010 

TotRnt2000 

RntEstim2000 

Hog2005 

TotTr2005 

TtTrPr2005 

TotRnt2005 

RntEstim2005 

Hog2010 

TrTot2010 

TtTrPr2005 

TotRnt2010 

RntEstim2005  

Pero realmente queremos una base del siguiente modo

id. 

CVE_LOC_SU 

NOM_LOC_SU 

POB2010 

Año

Hog 

TotTr 

TtTrPr 

TotRnt 

RntEstim 

Así el año pdrá ser considerado un factor y podrá entrar a un examen de MANOVA.

La forma más fácil de hacerlo es con una macro grabada. Lo que hacemos es ubicarnos en el primer valor en E2, y damos la instrucción de grabar la macro en modo relativo. Y hacemos el trabajo una sóla vez, ese trabajo consiste en lo siguiente:

Bajar una celda, marcar dos celdas hacia abajo, insertar dos renglones, copiar los valores de j2 a n2 justo abajo, osea en e3 a i3, y los de o2 a s2 en e4 a i4. Si repetimos la operación queda así.

eso lo repetimos 91 veces con una macro que queda así:

Sub AformatoAnova()

'

' Macro2 Macro

'

For N = 1 To 91

'

    ActiveCell.Offset(1, 0).Range("A1:A2").Select

    Application.CutCopyMode = False

    Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove

    ActiveCell.Offset(-1, 5).Range("A1").Select

    Range(Selection, Selection.End(xlToRight)).Select

    Selection.Cut

    ActiveCell.Offset(1, -5).Range("A1").Select

    ActiveSheet.Paste

    ActiveCell.Offset(0, 5).Range("A1:E1").Select

    Selection.Cut

    ActiveCell.Offset(1, -5).Range("A1").Select

    ActiveSheet.Paste

    ActiveCell.Offset(1, 0).Range("A1").Select

Next

End Sub

Y al final nos queda esto:

Ahora se necesita completar la tabla en cada lugar vacío con el valor de arriba. eso se logra con la macro que explicamos en este mismo blog:

http://tonamini-prog.blogspot.com/2011/08/macro-en-excel-para-relleno-con.html

Queda así:

Sólo falta añadir el año, lo cual simplemente es repetir 2000,2005 y 2010 en una columna en todos los valores.

Y así es como queda finalmente lista para correrle un MANOVA. El año será un factor y se podrá saber como varía la información en el tiempo para los tres periodos establecidos. Ahora se le cambian el nombre a las variables, pues ya no son cada columna para cada época sino para todas con una variable de año.

Macro para borrar los valores que sean iguales al de arriba

En ocasiones tenemos listados en que los elementos importantes son los primeros que tienen el primer valor diferente en una columna. Por ejemplo, en un listado de localidades, las localidades corresponden a una ciudad, cada vez que aparece el cambio de ciudad es el valor que hay que mantener, el ejemplo se ve en seguida.

En el ejemplo se aprecia un listado de localidades que conforman ciudades. Cada que hay cambio de ciudad es el renglón que hay que mantener.

La macro es la siguiente:


Sub EliminaIgualAbajo()

Anterior = 9999999

For N = 1 To 300

 Valor = ActiveCell.Value

 If Valor = Anterior Then
     Selection.EntireRow.Delete
 Else
    Anterior = Valor
    ActiveCell.Offset(1, 0).Range("A1").Select
End If

Next

End Sub

Y el resultado queda así:

Como sustituir asteriscos en Excel por otro valor, y el nombre de Dios

Hay un problema común, por ejemplo, en algunas bases de datos se les ocurre sustituir los valores confidenciales, pero cuando quiero en Excel sustituir * con otro valor, por ejemplo 9999 o A. En la imagen se muestra un ejemplo:

 Si yo -o tú, claro está- utilizas el buscador de Excel, y le dices sustituir "*" por otro valor, entenderá que lo que quieres hacer es sustituir cualquier valor (pues así se dice en idioma Excel cualquier cosa, "*") por A, y pondrá puras Aes, como se ve en la siguiente imagen:

Realmente "*" es como el nombre de Dios que no se puede pronunciar y su uso tiene efectos desconocidos. Bien. 

 Para ello está esta macro:


Sub SustituyeAsterisco()
   
    For N = 1 To 16
   
    ValorAObservar = ActiveCell.Value
   
     If ValorAObservar = "*" Then
      ActiveCell.Value = "A"
     End If
   
   
    ActiveCell.Offset(1, 0).Range("A1").Select
   
    Next
   
End Sub

Para integrarla simplemente se graba cualquier macro, se detiene, se va al menú de macros se dice modificar y ahi se pega, entonces quedará grabada. Cuando se haga eso el resultado será el siguiente:

En otra ocasión veremos como prescindir del valor límite en For Next, para que la macro actúe independientemente del número de registros.

Tu vida en un metro cuadrado. ¿Haz visto pasar tu vida?

Un artista plástico (Ignacio Uriarte) hizo una exposición en la Casa del Lago  de la UNAM. No es exactamente el tipo de arte que me gusta mucho, pero si me provoca algunas sensaciones, en este caso, agradables. Supongo que con eso basta. Uno de sus trabajos se llama Todos mis días y es simplemente una hoja de Excel, donde en cada celda están todos los días de su vida desde que nació hasta que creo esa obra. La sensación que me produjo fue extraña, todas las fechas en formato DD/MM/AA son visibles a un metro, y caben en una hoja de ploter. Que chica es la vida. Parece ordenada y realmente todos esos días debieron transcurrir diferente, sin embargo en el conteo son lo mismo.

Me decidí hacer mi propia hoja de todos mis días. El diseño será un poco diferente, cada columna será un año y tendremos tantas columnas como años. Evidentemente esto lo haremos con una macro, luego cada quien puede modificar los parámetros, incluso podemos, por ejemplo, diseñar una hoja donde el usuario anote a un recuadro con ayuda de un combo, su fecha de nacimiento y le genere automáticamente su hoja tipo "Todos mis días".

El procedimiento será el siguiente:
Lo primero que hay que hacer es cambiarle el formato a todas las celdas a tipo fecha, si no sabes como pregúntale a quién más confianza le tengas. Luego anota la fecha de tu nacimiento en la columna A, renlgón 1. Osea, así:

No es buena costumbre, pero a veces ante la prisa, es bueno ahorrar escritura de programación y en vez de teclear código grabar movimientos con las macros y luego injertarlos en el código. Haremos eso por esta vez para mostrar la técnica. Lo que queremos ahora es tener la columna A con todos los días de un año biológico, y como el primer año biológico como todos los demás tienen 365 días tenemos que copiar ese dato pero con un día aumentado en la columna de abajo, de forma que A2 es el ayer de A1, y así suscesivamente. Obviamente que es muy fácil hacer eso con un copy paste. Pero no nos serviría (salvo haciendo otras cosas) para la segunda columna, así que haremos el copy paste pero con una macro. Es decir esta macro copiara lo que hay en A1 en A2, primero copia luego baja y pone el valor. Primero hay que dar click en referencias relativas, decir grabar macro y proceder. Cuando aparezca la caja de diálogo de macro le damos un nombre, "Baja".

Cuando demos aceptar bajaremos una celda para quedar en A2. Al dar detener, quedará dada de alta una poderosisima macro que lo que hace es bajar una casilla. Ese será el código bajar que utilizaremos.
Repetimos el proceso para copiar pero con un uno aumentado, que en el contexto del formato de fecha será un día.

Podemos pedir el menu de macros y nos aparecerán los dos códigos que tenemos.

Cuando damos en el botón modificar, podemos acceder al código que se ve así:

Ahora si, tenemos este código:

Sub Bajar()
    ActiveCell.Offset(1, 0).Range("A1").Select
End Sub
Sub AumentaUnDia()
    ActiveCell.FormulaR1C1 = "=+R[-1]C+1"
    ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

Y con ese creamos este:

Sub CalculaColumna()

ActiveCell.Offset(1, 0).Range("A1").Select 'primera macro

For DiasDeUnAnio = 1 To 364
ActiveCell.FormulaR1C1 = "=+R[-1]C+1"      'segunda macro
ActiveCell.Offset(1, 0).Range("A1").Select
Next


End Sub

Esta macro lo que hará será llenar la primera columna con la secuencia de días que forman el primer año biológico. Así se verá cuando acabe de correr:

El siguiente paso es regresar a la B1, donde iniciará la columna con el segundo año de vida. Lo haremos del modo fácil.

En este momento, la macro te ha dejado en la casilla A366. Así que grabas una macro que se llama "CambiaSiguienteColumna" y te haces uno a la derecha y todo hasta arriba. Es decir, das grabar nueva macro, la llamas "CambiaSiguienteColumna", te mueves a la B366, luego das tecla fin, tecla arriba y detienes la macro. El resultado es que ahora tienes una macro con este código:


Sub CambiaSiquienteColumna()
    ActiveCell.Offset(0, 1).Range("A1").Select
    Selection.End(xlUp).Select
End Sub


Cuando se ejecute este código desde cualquier casilla en el renglon 366 si irá al primer renglón de la siguiente columna. Este proceso se podría hacer también de otros modos, pero este modo intuitivo de manejo de las macros es mejor para el principiante.

Ahora repites una operación similar a aquella en la que sumaste un día, pero un poco diferente, como resultado de la macro anterior, quedas en la columna B1, así que ahora tienes que sumarle a la casilla de la izquierda 1 año, osea 365 días. Para hacer la macro: activas macro (cuidando que esté en referencias relativas), presionas signo de más (con esto estás en modo de espera de cálculo), tecleas 365, pones otro más, mueves la flecha izquierda, copias y enter. Apagas macro.
El proceso se ve así:

Y el código queda así, si le llamaste a tu macro "SumaUnAnio"


Sub SumaUnAnio()
' SumaUnAnio Macro
    ActiveCell.FormulaR1C1 = "=+RC[-1]+365"
    ActiveCell.Offset(1, 0).Range("A1").Select
End Sub


Solo que como aprecias en la figura te deja en la celda B2, siendo que quieres estar en la B1 para aplicar tu primer macro, así que la regresas grabando una macro que suba una casilla, que es esta:



Sub subircelda()
    ActiveCell.Offset(-1, 0).Range("A1").Select
End Sub

Y ahora vienen los suspiros, pues llegó la hora de generar tantas columnas como años. Se puede hacer haciendo referencia a las macros pues también son subrutinas, o bien directamente que es como procederemos. Yo tengo prácticamente 45, así que va así la cosa:

Sub TodosMisDias()

For Edad = 1 To 45
    'dias del primer anio, primer columna
    ActiveCell.Offset(1, 0).Range("A1").Select 'primera macro

   For DiasDeUnAnio = 1 To 364
 
    ActiveCell.FormulaR1C1 = "=+R[-1]C+1"      'segunda macro
    ActiveCell.Offset(1, 0).Range("A1").Select

  Next

'cambiar de columna
      ActiveCell.Offset(0, 1).Range("A1").Select
      Selection.End(xlUp).Select
'sumar un anio
      ActiveCell.FormulaR1C1 = "=+RC[-1]+365"
      ActiveCell.Offset(1, 0).Range("A1").Select
'subir una celda
      ActiveCell.Offset(-1, 0).Range("A1").Select

Next



End Sub




Ahora corre la macro poniendo tus parámetros de edad y fecha de nacimiento y tendrás esto.

Si cabe tu vida y puedes ver los días eres un joven si no como yo un anciano. Obtendrás una sensación muy agradable cuando veas correr la macro, lo que en verdad estarás viendo, es pasar tu vida, a la velocidad que hoy crees que pasó.