martes, 13 de diciembre de 2011

Función Personalizada

Puede descargar el fichero: Funcion_Personalizada.xlsm


Programar una función personalizada en #Excel es una tarea sencilla y que nos puede reportar un gran ahorro de tiempo. Dispone de un vídeo donde podemos ver el proceso para crear una función que calcula el margen comercial de un establecimiento comercial.





Vídeo


domingo, 11 de diciembre de 2011

Eliminar filas con cierta periodicidad

Descargar el fichero: elimina_filas.xlsm

Para eliminar filas que se repiten con cierta periodicidad hemos diseñado una macro. Es frecuente que al importar informes de otras aplicaciones obtengamos un texto plano que al volcarse a Excel lleva muchas líneas de "basura" que deseamos eliminar. Si las filas que deseamos suprimir se repiten de forma periódica podemos utilizar la macro que explicaremos seguidamente.

Ya habíamos hablado de eliminar filas y columnas vacías en otro post:

Eliminar Filas y Columnas vacias

En esta ocasión lo que pretendemos es eliminar ciertas filas que se repiten periódicamente.


En nuestro ejemplo disponemos de un texto que comienza en la fila 4, donde se alternan algunas filas que nos resultan válidas (color azul) y otras que nos resultan inútiles (color rojo). La macro se lanza pulsando el botón que hemos incrustado, y lo primero que hace es preguntarnos por la primera fila válida, y finalmente nos pregunta por la segunda fila válida. La propia macro calcula el final del texto en esa hoja y procede a eliminar las filas inútiles.

La macro no la hemos colocado en un módulo como es habitual sino en ThisWorkbook. Véase la imagen siguiente.



Es importante observar que la macro comienza a eliminar de abajo hacia arriba, ya que en caso contrario, se iría alterando el número que ocupan las filas, al ser eliminadas.


Código:

Sub elimina_filas_inutiles()
Dim primera As Long
Dim segunda As Long
Dim ultima As Long
Dim i As Long
primera = InputBox("Indique la primera fila válida", , 4)
segunda = InputBox("Indique la segunda fila válida", , 11)
ultima = ActiveCell.SpecialCells(xlLastCell).Row
For i = ultima To primera Step -1
    If (i Mod (segunda - primera)) - primera <> 0 Then
        Cells(i, 1).EntireRow.Delete
    End If
Next i
End Sub

La clave del proceso está en el operador Mod que es el módulo. En español se denomina resto, que se corresponde con la función RESIDUO de Excel.

Fechador: da la fecha al cambiar un dato

Descargar el fichero: fechador.xlsm

Fechador contiene una macro que establece la fecha y la hora junto a cierta celda cuando ésta experimenta cambios. Esto se logra manejando los eventos, concretamente el evento Change que detecta cuando se produce un cambio en alguna celda de una hoja. Podemos ejecutar la macro cuando el cambio se produzca en todas las celdas, o bien únicamente en una de ellas, o en un rango, y esto lo conseguimos manejando el target. Veamos cómo se hace.

Hoja 1



Al escribir en cualquier celda de la columna D, y en particular en las celdas verdes, y bajo ellas, al pulsar INTRO, automáticamente se anotará en la celda de la derecha la fecha y hora actuales, siempre que su ordenador esté correctamente puesto en fecha y hora.

Esto se consigue con una pequeña macro que se ha de escribir, no en un Módulo como es habitual al programar macros, sino en la Hoja1. Vea la siguiente imagen. Por cierto, para pasar de un elemento a otro de los de la imagen, o de un módulo a otro, no basta con hacer click con el ratón, se ha de hacer doble click.



Al situarnos dentro del Editor de Visual Basic en la Hoja1, a la derecha aparecerá la posibilidad de manejar dos desplegables. Hemos de elegir "Worksheet" en el de la izquierda y "Change" en le de la derecha.


Código:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.MoveAfterReturn = False
If Target.Count = 1 Then
  If (Target.Column = 4 And Target <> 0) Then
    Target.Offset(0, 1) = Now
  End If
End If
End Sub


Hoja 2

Nos situamos ahora en la Hoja 2. En este caso pretendemos que se indique la fecha y la hora de forma automática en la columna E, siempre que se efectúen anotaciones o cambios en cualquier celda del rango B7:D20.



Vamos al Editor de Visual Basic, y en el Explorador de Proyectos nos situamos en la Hoja2. Para ello debemos pulsar doble click sobre la Hoja2, tal y como se muestra en la siguiente imagen.



En este caso la macro cambia ligeramente.

Código:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.MoveAfterReturn = False
If Target.Count = 1 Then
  If Not Intersect(Target, Range("B5:D39")) Is Nothing Then
    Cells(Target.Row, "E") = Now
  End If
End If
End Sub

viernes, 9 de diciembre de 2011

Creación de una campana de Gauss con Excel

Descargar el fichero: campana.xlsm

Veamo cómo crear una campana de Gauss. Trataremos de ver gráficamente el Teorema Central del Límite. Partimos de una serie de números aleatorios que se distribuyen según una distribución uniforme entre 0 y 1. U[0,1]

Los aleatorios se obtienen con la función de Excel:

=ALEATORIO()

El teorema central del límite nos dice que necesitaríamos infinitos valores, pero lo haremos con únicamente 12 valores.

Calculamos la media de estos valores con la función:

=PROMEDIO(datos)

Esta media se encuentra en la celda C1, y cada vez que escribimos algo en una celda, o cada vez que pulsamos la tecla F9, se recalculan los valores, ya que se basan en números aleatorios.


Queremos que los valores de la celda C1 (el promedio) se copien secuencialmente en la columna E, y de forma automática. Para conseguir nuestro objetivo utilizaremos una macro.


La macro anota permite escribir los valores de los promedios en la columna E, poniendo tantos como necesitemos. En la imagen el bucle FOR...NEXT llega hasta 10.000 valores.


Histograma

Para crear el histograma de frecuencias definimos los intervalos. Elegimos 20 intervalos y para obtenerlos en la columna G hacemos una serie que comienza en 0 y finaliza en 1, con un intervalo de 0,05.

A su derecha dejamos preparada una zona donde mediante la función FRACUENCIA determinaremos cuantos datos, de entre las 10.000 medias generadas, se encuentran dentro de cada intervalo.



La función Frecuencia tiene la siguiente sintaxis:

=FRECUENCIA(datos;grupos)

  • donde los datos son lo valores de la columna E, que es donde se encuentran las 10.000 anotaciones de los promedios generados
  • donde grupos es el rango G1:G21 que es done se encuntran los intervalos que hemos definido

Es una función matricial que requiere tres pasos:

  1. Seleccionar con el ratón (o con el teclado) la zona donde la función dejará sus resultados. En este caso abarca más de una celda, concretamente es la zona amarilla
  2. Se escribe la función matricial propiamente dicha. En este caso, se escribe la función Frecuencia
  3. No se valida pulsando INTRO. Se han de pulsar simultáneamente las tres teclas siguientes: CONTROL+MAYÚSCULAS+INTRO
Para saber más sobre funciones matriciales consulte el siguiente post:


Si ponemos en horizontal los valores obtenidos con la función Frecuencia, y con un poco de imaginación se puede ver ya la campana de Gauss.


Realicemos el gráfico.


Media Móvil

Si pulsamos sobre el gráfico con el botón derecho del ratón, podemos elegir "Agregar línea de tendencia", y de todas ellas elegir la Media Móvil. Así obtendremos el siguiente gráfico.


Vídeo