Rosca de reyes virtual con Excel

Ahora veremos como hacer una rosca de reyes virtual con Excel.
Supongamos que tus amigos están dispersos por todos lados del mundo, pero se verán en febrero. Quieren partir la rosca y verán quien paga los tamales. Bien, aquí hay una opción. Se pasan el archivo, cada quien parte su pedazo de rosca virtualmente, graba el archivo, lo pasa al siguiente, así hasta que se complete el grupo.

La apariencia será como arriba. Es necesario hacer el dibujo que simule una rosca. Hacer la plantilla con el espacio donde se indica para que cada quien ponga su nombre.

El código está vez lo dejaremos al lector para que lo analice. Como siempre, basta con que lo copie y pegue en el área de macros para que funcione. Como apreciará aquí da una probabilidad de 1 a dos de sacar muñeco, esta se puede cambiar camgiando el código que está entre el if.


Sub PartirRosca()


Aleatorio = Int(Rnd() * 10)
If Aleatorio / 2 = Int(Aleatorio / 2) Then
    TieneMuneco = "Si tiene muneco"
    Else
    TieneMuneco = "No tiene muneco"
End If
 
With Selection.Interior
 .Pattern = xlSolid
 .PatternColorIndex = xlAutomatic
 .ThemeColor = xlThemeColorLight1
 .TintAndShade = 0
 .PatternTintAndShade = 0
End With

With Selection.Font
 .ThemeColor = xlThemeColorLight1
 .TintAndShade = 0
End With
 
Range("B2:D2").Select

Nombre = ActiveCell.Value
 
Range("O1").Select
 
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
 
    ActiveCell.Value = Nombre
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.Value = TieneMuneco
   
    ActiveCell.Offset(0, -1).Range("A1").Select
   
    Range("B2:D2").Select
 
End Sub

Cálculo de desviación estándar

Ahora la tarea que han pedido es calcular la desviación estándar en Excel de los datos que se muestran:

Pues lo más fácil como siempre es usa una de las cientos de funciones que trae Excel

=Desvest(B4:F4)

Es un listado muy simple de delitos, muchos se preguntan en clase para que puede servir sacar la desviación estándar de eso. Bien, si la desviación por tipo de delito fuera cero significaría que todos los tipos de delito se reparten del mismo modo, hay la misma cantidad de cada tipo. En cambio si la desviación es alta, indica que la zona se especializa en algún tipo.

Pero evidentemente no es lo que nos interesa si no conocer el proceso de  la fórmula y tener una opción de programación que se pueda ajustar a necesidades específicas. Por ejemplo, teniendo una macro que calcule la desviación estándar, podré más adelante utilizarla en un filtro y calcular la desviación solo de ciertos valores, o calcular la desviación de grupos específicos por separado, etc.

Hacerlo por partes tampoco implica un gran reto. Tenemos que

Y esto se traduce para el anterior ejemplo en lo siguiente:

Y visto en fórmulas del siguiente modo:

Es decir, primero a cada monto de delitos se le resta el promedio previamente calculado y se coloca en la celda de abajo. Luego esto se eleva al cuadrado. todos los cuadrados se suman, eso de divide entre el número de casos, menos uno y a eso se le saca raiz cuadrada. Es muy importante que primero se divide por el número de casos menos uno y luego se saca la raíz y no al revés. Esto es por el mismo concepto de desviación estándar. Queremos saber como se comportan los datos con respecto a la media, si es un grupo homogéneo nos dará desviación baja y un grupo heterogéneo una desviación alta. Como no hay una medida para ello. Hemos sumado la variacion total cuando restamos a cada caso la media, pero la variación sumada no nos dirá nada con respecto a la media pues será cero ya que hay lo mismo arriba que abajo de la media. Por eso la multiplicamos por si misma y así tenemos una cantidad que depende de la variación total y es positiva. Como nuestra unidad debe ser lo mismo que metemos, le sacamos raíz hasta el último.

Pues ahora el asunto es como hacer la macro de desviavión estándar. Supongamos nuestro caso del mismo modo, solamente borraremos el espacio vacío que hay en la columna A.

Haremos una macro que estando con la celda activa en la casilla F4, al lado de los datos, calcule la desviación de todo lo que esté a la izquierda. Lo importante será el proceso, pues se puede ajustar para calcularla con datos hacia abajo, hacia arriba o hasta en diagonal si a alguien le sirviera eso. 

Lo primero que necesitaremos será saber cual es la localización del cursor. O en que columna, esto se obtiene con la función

ActiveCell.Column

Y esto lo podemos asociar a una variable, por ejemplo que se llame columna:

Columna=ActiveCell.Column

Que en el caso de la anterior gráfica vale 6, pues la columna de la celda activa es la F que es la sexta. 

Ahora si, calculamos el promedio de los datos:

For n = 1 To Columna - 1

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

    DatoASumar = ActiveCell.Value

    Suma = Suma + DatoASumar

Next

Esto es, el cursor de la celda F irá cuando valga n=2, a la E pues ActiveCell.Offset(0,-1).Range("A1").Select lo que hace es hacer una celda (-1) en renglón a la izquierda a l cursor (select). Añade a un acumulador la suma hasta que llega a la celda A en este caso que es la primera y está a cinco lugares del inicio.

Con la siguiente macroinstruccion lo que haremos será anotar los resultados en una forma ordenada en la columna A

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

ActiveCell.Value = Suma

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

ActiveCell.Value = "Suma"

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

ActiveCell.Value = Suma / (Columna - 1)

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

ActiveCell.Value = "Promedio"

Promedio = Suma / (Columna - 1)

    Selection.End(xlUp).Select

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

    Selection.End(xlToLeft).Select

Ahora estamos en el renglon de los datos en la columna A, y con la siguiente macroinstrucción comenzamos a calular la varianza:

For Derecha = 1 To Columna - 1

    DatoAPotenciar = ActiveCell.Value

    

    DatoAPotenciarMenosPromedioCuadrado = (DatoAPotenciar - Promedio) * (DatoAPotenciar - Promedio)

    

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

'ActiveCell.Value = DatoAPotenciarMenosPromedioCuadrado

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

    Varianza = Varianza + DatoAPotenciarMenosPromedioCuadrado

    

    

    

    

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

Next

Finalmente el programa completo es el siguiente.

Sub DesviavionEstandar()

Columna = ActiveCell.Column

Suma = 0

For n = 1 To Columna - 1

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

    DatoASumar = ActiveCell.Value

    Suma = Suma + DatoASumar

Next

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

ActiveCell.Value = Suma

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

ActiveCell.Value = "Suma"

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

ActiveCell.Value = Suma / (Columna - 1)

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

ActiveCell.Value = "Promedio"

Promedio = Suma / (Columna - 1)

    Selection.End(xlUp).Select

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

    Selection.End(xlToLeft).Select

Varianza = 0

For Derecha = 1 To Columna - 1

    DatoAPotenciar = ActiveCell.Value

    

    DatoAPotenciarMenosPromedioCuadrado = (DatoAPotenciar - Promedio) * (DatoAPotenciar - Promedio)

    

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

'ActiveCell.Value = DatoAPotenciarMenosPromedioCuadrado

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

    Varianza = Varianza + DatoAPotenciarMenosPromedioCuadrado

    

    

    

    

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

Next

    Selection.End(xlToLeft).Select

    Selection.End(xlToLeft).Select

    Selection.End(xlDown).Select

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

ActiveCell.Value = Varianza / (Columna - 2)

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

ActiveCell.Value = "Varianza"

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

ActiveCell.FormulaR1C1 = "=SQRT(R[-1]C)"

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

ActiveCell.Value = "Desviacion estándar"

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

 Selection.End(xlUp).Select

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

    Selection.End(xlToRight).Select

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

End Sub

Se pega en el area de macros y hará lo que buscamos. La salida es la siguiente:

Se puede apreciar que es el mismo resultado que el anterior y que se puede aplicar a una lista horizontal de datos tan grande como se quiera. 

Macto para borrar columnas en la limpieza de una tabla

En ocasiones tenemos tablas que son resultado de otra tabla y en consecuencia dejan algunas columnas vacias. Por ejemplo la tabla de abajo es la relación de población, homicidios femeninos y totales.

En otra hoja del mismo libro queremos tener la relación de homicidios totales sobre los femeninos. Asi que copiamos la operación que queremos en la hoja 2.

Así que queremos borrar todas las columnas vacias intermedias.

Este es uno de los macros más fáciles que puede haber.
Nos localizamos en la primer columna vacía y prendemos la opción de grabar macro, damos la opción de guardar relativas.

Tras haber dado nombre a la macro, comenzamos a borrar dos veces con el boton para ello:

En seguida nos dirigimos a la siguiente posición de la columna vacia.

La macro habrá grabado lo siguiente:


Sub BorraColumnasVacias()
'
' BorraColumnasVacias Macro
'

'
    Selection.EntireColumn.Delete
    Selection.EntireColumn.Delete
    ActiveCell.Offset(0, 1).Range("A1").Select
 


End Sub

Solo tenemos que agregar el For, Next para el número de columnas que tengamos, por ejemplo si tenemos 10, queda así:

Sub BorraColumnasVacias()
'
' BorraColumnasVacias Macro
'
For N=1 To 10
'
    Selection.EntireColumn.Delete
    Selection.EntireColumn.Delete
    ActiveCell.Offset(0, 1).Range("A1").Select
 
Next

End Sub

Si es para 100 se le pone en vez de 10, 100, si es para 1,235,289, entonces este enúmero en vez de 10. 

Una vez así, ya se puede correr.

Nuestra tabla quedará del siguiente modo.

N!. N factorial en Excel

En una tarea de informática criminológica, el profesor nos ha pedido estimar N! en Excel para cualquier número.
n!=1*2*...*n



La forma más simple sería poner la fórmula =Fact(numero)

Pero aceptemos que para eso mejor consultamos una tabla de factoriales. Otra forma igual de aburrida sería hacerlo por multiplicaciones sucesivas en vertical.

Veamos una forma más interesante. Supongamos que se quiere tener una hoja como la de abajo donde nos localicemos en la celda amarilla  en A1 y en B1 diremos el número al que se le extraerá el factorial.

En nuestro ejemplo deseamos tener algo como lo siquiente:

Bien, el código es el siguiente:



Sub NFactorial()
    ActiveCell.Offset(0, 1).Range("A1").Select  'el cursor se hace a la derecha para extraer la información de b2 y saber hasta que número calculará

    limite = ActiveCell.Value 'asocia el valor de B2 (si efectivamente se empezó en B1) a la variable limite

    ActiveCell.Offset(0, -1).Range("A1").Select 'regresa a A1 para escribir las estimaciones
For N = 1 To limite                                                    ' Inicia el ciclo
'
    ActiveCell.FormulaR1C1 = N                                                          
    ActiveCell.Offset(1, 0).Range("A1").Select
   
Next

    ActiveCell.Offset(-1, 0).Range("A1").Select
    Selection.End(xlUp).Select
    ActiveCell.Offset(1, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=+RC[-1]*R[-1]C[-1]"    'realiza la operacion
    ActiveCell.Offset(1, 0).Range("A1").Select

'
For M = 1 To limite - 2

'
'
    ActiveCell.FormulaR1C1 = "=+RC[-1]*R[-1]C"
    ActiveCell.Offset(1, 0).Range("A1").Select
Valor = ActiveCell.Value

Next

 ActiveCell.Value = "Arriba de esta celda, el factorial buscado"


End Sub


Es una forma más lenta pero más divertida. Por cierto verás que calcula solamente hasta 171.

Limpieza de una tabla demográfica

En ocasiones tenemos tablas demográficas que tienen distintos niveles, por ejemplo las que maneja el Consejo Nacional de Población (CONAPO) que eventualmente requieren de borrar muchos datos pues tenemos el dato del país, el dato estatal y el municipal.

http://conapo.gob.mx/es/CONAPO/Municipales

La base que nos dan tiene múltiples pestañas que presenta distintas tablas cada una en un sólo libro de Excel. Por ejemplo tenemos tablas de este tipo:

Pero necesitamos algo así:



En este caso se quieren solamente los datos del País completo, Chihuahua (2do nivel) y Ciudad Juárez (tercer nivel). Para ello pedimos una macro y le ponemos nombre, por ejemplo, QuitaResto. Damos referencias relativas por su puesto. 

Comenzamos por localizarnos justo abajo de donde dice República Mexicana, en la celda B6. Presionamos shift y bajamos con la flecha de bajar. Hasta llegar a Chihuahua, la cual no subrrayamos. 0

Recortamos.

Y repetimos la operación. 

Cuando terminamos presionamos como siempre el botón de terminar macro. Nos situamos abajo de las palabras República Mexicana y ejecutamos la macro, nos va a pasar de un caso como el de la izquierda al de la derecha. 

Esta vez, no hemos modificado la macro, así como se graba se usa sin intervención del programador. Si le echamos un ojo, veremos lo que hay abajo, que es un registro del número de casillas que se desplaza hasta abajo pero esta vez no tendremos que ocuparnos. La misma tarea la podemos repetir para todas las hojas que tengan exactamente el mismo formato.


Sub QuitaResto()
'
' QuitaResto Macro
'

'
    ActiveCell.Select
    ActiveWindow.SmallScroll Down:=1
    ActiveCell.Range("A1:A213").Select
    ActiveWindow.SmallScroll Down:=-51
    ActiveWindow.Zoom = 85
    ActiveWindow.Zoom = 55
    ActiveWindow.Zoom = 25
    ActiveWindow.Zoom = 10
    ActiveWindow.ScrollRow = 52
    ActiveWindow.ScrollRow = 46
    ActiveWindow.LargeScroll Down:=-1
    ActiveWindow.Zoom = 40
    ActiveWindow.Zoom = 85
    ActiveWindow.ScrollColumn = 1
    ActiveWindow.SmallScroll Down:=3
    ActiveWindow.SmallScroll Down:=6
    ActiveWindow.SmallScroll Down:=9
    ActiveWindow.SmallScroll Down:=12
    ActiveWindow.SmallScroll Down:=15
    ActiveWindow.SmallScroll Down:=18
    ActiveWindow.SmallScroll Down:=21
    ActiveWindow.SmallScroll Down:=24
    ActiveWindow.SmallScroll Down:=27
    ActiveWindow.SmallScroll Down:=30
    ActiveWindow.SmallScroll Down:=36
    ActiveWindow.SmallScroll Down:=39
    ActiveWindow.SmallScroll Down:=42
    ActiveWindow.SmallScroll Down:=45
    ActiveWindow.SmallScroll Down:=48
    ActiveWindow.SmallScroll Down:=51
    ActiveWindow.SmallScroll Down:=54
    ActiveWindow.SmallScroll Down:=60
    ActiveWindow.SmallScroll Down:=66
    ActiveWindow.SmallScroll Down:=72
    ActiveWindow.SmallScroll Down:=75
    ActiveWindow.SmallScroll Down:=78
    ActiveWindow.SmallScroll Down:=81
    ActiveWindow.SmallScroll Down:=84
    ActiveWindow.SmallScroll Down:=90
    ActiveWindow.SmallScroll Down:=96
    ActiveWindow.SmallScroll Down:=99
    ActiveWindow.SmallScroll Down:=102
    ActiveWindow.SmallScroll Down:=105
    ActiveWindow.SmallScroll Down:=108
    ActiveWindow.SmallScroll Down:=111
    ActiveWindow.SmallScroll Down:=108
    ActiveWindow.SmallScroll Down:=111
    ActiveWindow.SmallScroll Down:=114
    ActiveWindow.SmallScroll Down:=120
    ActiveWindow.SmallScroll Down:=123
    ActiveWindow.SmallScroll Down:=120
    ActiveWindow.SmallScroll Down:=123
    ActiveWindow.SmallScroll Down:=126
    ActiveWindow.SmallScroll Down:=129
    ActiveWindow.SmallScroll Down:=132
    ActiveWindow.SmallScroll Down:=135
    ActiveWindow.ScrollRow = 140
    ActiveWindow.ScrollRow = 151
    ActiveWindow.ScrollRow = 174
    ActiveWindow.ScrollRow = 209
    ActiveWindow.ScrollRow = 215
    ActiveWindow.SmallScroll Down:=-3
    ActiveWindow.SmallScroll Down:=-6
    ActiveWindow.SmallScroll Down:=-9
    ActiveWindow.SmallScroll Down:=-12
    ActiveWindow.SmallScroll Down:=-15
    ActiveWindow.SmallScroll Down:=-18
    ActiveWindow.SmallScroll Down:=-21
    ActiveWindow.SmallScroll Down:=-24
    ActiveWindow.SmallScroll Down:=-27
    ActiveWindow.SmallScroll Down:=-30
    ActiveWindow.SmallScroll Down:=-33
    ActiveWindow.SmallScroll Down:=-36
    ActiveWindow.SmallScroll Down:=-39
    ActiveWindow.SmallScroll Down:=-42
    ActiveWindow.SmallScroll Down:=-45
    ActiveWindow.SmallScroll Down:=-48
    ActiveWindow.SmallScroll Down:=-51
    ActiveWindow.SmallScroll Down:=-54
    ActiveWindow.SmallScroll Down:=-57
    ActiveWindow.SmallScroll Down:=-63
    ActiveWindow.SmallScroll Down:=-66
    ActiveWindow.SmallScroll Down:=-69
    ActiveWindow.SmallScroll Down:=-72
    ActiveWindow.SmallScroll Down:=-75
    ActiveWindow.SmallScroll Down:=-78
    ActiveWindow.SmallScroll Down:=-81
    ActiveWindow.SmallScroll Down:=-84
    ActiveWindow.SmallScroll Down:=-87
    ActiveWindow.SmallScroll Down:=-90
    ActiveWindow.SmallScroll Down:=-93
    ActiveWindow.SmallScroll Down:=-96
    ActiveWindow.SmallScroll Down:=-99
    ActiveWindow.SmallScroll Down:=-102
    ActiveWindow.SmallScroll Down:=-105
    ActiveWindow.SmallScroll Down:=-108
    ActiveWindow.SmallScroll Down:=-111
    ActiveWindow.SmallScroll Down:=-114
    ActiveWindow.SmallScroll Down:=-117
    ActiveWindow.SmallScroll Down:=-120
    ActiveWindow.SmallScroll Down:=-123
    ActiveWindow.SmallScroll Down:=-126
    ActiveWindow.SmallScroll Down:=-129
    ActiveWindow.SmallScroll Down:=-132
    ActiveWindow.SmallScroll Down:=-135
    ActiveWindow.SmallScroll Down:=-138
    ActiveWindow.SmallScroll Down:=-141
    ActiveWindow.SmallScroll Down:=-144
    ActiveWindow.SmallScroll Down:=-147
    ActiveWindow.SmallScroll Down:=-150
    ActiveWindow.SmallScroll Down:=-153
    ActiveWindow.SmallScroll Down:=-156
    ActiveWindow.SmallScroll Down:=-159
    ActiveWindow.SmallScroll Down:=-162
    ActiveWindow.SmallScroll Down:=-165
    ActiveWindow.SmallScroll Down:=-168
    ActiveWindow.SmallScroll Down:=-171
    ActiveWindow.SmallScroll Down:=-174
    ActiveWindow.SmallScroll Down:=-177
    ActiveWindow.SmallScroll Down:=-180
    ActiveWindow.SmallScroll Down:=-183
    ActiveWindow.SmallScroll Down:=-186
    ActiveWindow.SmallScroll Down:=-189
    ActiveWindow.SmallScroll Down:=-192
    ActiveWindow.SmallScroll Down:=-195
    ActiveWindow.SmallScroll Down:=-198
    ActiveWindow.SmallScroll Down:=-201
    ActiveWindow.SmallScroll Down:=-204
    ActiveWindow.SmallScroll Down:=-207
    ActiveWindow.SmallScroll Down:=-210
    ActiveWindow.SmallScroll Down:=-213
    ActiveWindow.SmallScroll Down:=-219
    ActiveWindow.SmallScroll Down:=-222
    ActiveWindow.SmallScroll Down:=-225
    ActiveWindow.SmallScroll Down:=-222
    ActiveWindow.SmallScroll Down:=-219
    ActiveWindow.SmallScroll Down:=-216
    ActiveWindow.SmallScroll Down:=-213
    ActiveWindow.SmallScroll Down:=-210
    ActiveWindow.SmallScroll Down:=-207
    ActiveWindow.SmallScroll Down:=-204
    ActiveWindow.SmallScroll Down:=-207
    ActiveWindow.SmallScroll Down:=-210
    ActiveWindow.SmallScroll Down:=-213
    ActiveWindow.SmallScroll Down:=-216
    ActiveWindow.SmallScroll Down:=-219
    ActiveWindow.SmallScroll Down:=-222
    ActiveWindow.SmallScroll Down:=-225
    ActiveWindow.SmallScroll Down:=-222
    ActiveWindow.SmallScroll Down:=-219
    ActiveWindow.SmallScroll Down:=-216
    ActiveWindow.SmallScroll Down:=-213
    ActiveWindow.SmallScroll Down:=-210
    ActiveWindow.SmallScroll Down:=-207
    ActiveWindow.SmallScroll Down:=-204
    ActiveWindow.SmallScroll Down:=-201
    ActiveWindow.SmallScroll Down:=-198
    ActiveWindow.SmallScroll Down:=-195
    ActiveWindow.SmallScroll Down:=-192
    ActiveWindow.SmallScroll Down:=-189
    ActiveWindow.SmallScroll Down:=-186
    ActiveWindow.SmallScroll Down:=-183
    ActiveWindow.SmallScroll Down:=-180
    ActiveWindow.SmallScroll Down:=-183
    ActiveWindow.SmallScroll Down:=-180
    ActiveWindow.SmallScroll Down:=-177
    ActiveWindow.SmallScroll Down:=-174
    ActiveWindow.SmallScroll Down:=-177
    ActiveWindow.SmallScroll Down:=-174
    ActiveWindow.SmallScroll Down:=-171
    ActiveWindow.SmallScroll Down:=-168
    ActiveWindow.SmallScroll Down:=-165
    ActiveWindow.SmallScroll Down:=-162
    ActiveWindow.SmallScroll Down:=-159
    ActiveWindow.SmallScroll Down:=-156
    ActiveWindow.SmallScroll Down:=-153
    ActiveWindow.SmallScroll Down:=-147
    ActiveWindow.SmallScroll Down:=-144
    ActiveWindow.SmallScroll Down:=-141
    ActiveWindow.SmallScroll Down:=-138
    ActiveWindow.SmallScroll Down:=-132
    ActiveWindow.SmallScroll Down:=-129
    ActiveWindow.SmallScroll Down:=-126
    ActiveWindow.SmallScroll Down:=-123
    ActiveWindow.SmallScroll Down:=-120
    ActiveWindow.SmallScroll Down:=-117
    ActiveWindow.SmallScroll Down:=-114
    ActiveWindow.SmallScroll Down:=-111
    ActiveWindow.SmallScroll Down:=-108
    ActiveWindow.SmallScroll Down:=-105
    ActiveWindow.SmallScroll Down:=-102
    ActiveWindow.SmallScroll Down:=-105
    ActiveWindow.SmallScroll Down:=-108
    ActiveWindow.SmallScroll Down:=-105
    ActiveWindow.SmallScroll Down:=-102
    ActiveWindow.SmallScroll Down:=-99
    ActiveWindow.SmallScroll Down:=-96
    ActiveWindow.SmallScroll Down:=-99
    ActiveWindow.SmallScroll Down:=-102
    ActiveWindow.SmallScroll Down:=-99
    ActiveWindow.SmallScroll Down:=-96
    ActiveWindow.SmallScroll Down:=-90
    ActiveWindow.SmallScroll Down:=-87
    ActiveWindow.SmallScroll Down:=-84
    ActiveWindow.SmallScroll Down:=-81
    ActiveWindow.SmallScroll Down:=-78
    ActiveWindow.SmallScroll Down:=-75
    ActiveWindow.SmallScroll Down:=-72
    ActiveWindow.SmallScroll Down:=-69
    ActiveWindow.SmallScroll Down:=-72
    ActiveWindow.SmallScroll Down:=-75
    ActiveWindow.SmallScroll Down:=-78
    ActiveWindow.SmallScroll Down:=-75
    ActiveWindow.SmallScroll Down:=-72
    ActiveWindow.SmallScroll Down:=-69
    ActiveWindow.SmallScroll Down:=-66
    ActiveWindow.SmallScroll Down:=-63
    ActiveWindow.SmallScroll Down:=-60
    ActiveWindow.SmallScroll Down:=-57
    ActiveWindow.SmallScroll Down:=-54
    ActiveWindow.SmallScroll Down:=-51
    ActiveWindow.SmallScroll Down:=-48
    ActiveWindow.SmallScroll Down:=-45
    ActiveWindow.SmallScroll Down:=-42
    ActiveWindow.SmallScroll Down:=-39
    ActiveWindow.SmallScroll Down:=-36
    ActiveWindow.SmallScroll Down:=-33
    ActiveWindow.SmallScroll Down:=-30
    ActiveWindow.SmallScroll Down:=-27
    ActiveCell.Range("A1:A213").Select
    ActiveCell.Range("A1:A213").Select
    Selection.EntireRow.Delete
    ActiveCell.Offset(1, 0).Range("A1:A36").Select
    Selection.EntireRow.Delete
    ActiveCell.Offset(1, 0).Range("A1:A2278").Select
    Selection.EntireRow.Delete
    ActiveCell.Select
End Sub

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.