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


viernes, 25 de noviembre de 2011

Sumar los valores en negrita

Descargar el fichero: suma_negritas.xlsm

En Excel es frecuente trabajar introduciendo colores o negritas en las celdas que deseamos resaltar, y luego se presenta la tarea de tener que contar o sumar los valores de las celdas que hemos resaltado. En otra entrada de este blog ya dimos varios métodos para sumar las celdas según su color. Puedes verlo en este enlace:

Contar y Sumar los valores de las celdas según su Color

Ahora vamos a ver algún método para sumar las celdas en negrita.


Método 1

Vamos a crear una función personalizada que nos indique con VERDADERO o FALSO si una celda está en negrita o no.

La función se denomina negrita y es la siguiente.

Código:

Function negrita(ByVal Rng As Range) As Boolean
'Solo para una celda
If Rng.Count <> 1 Then negrita = CVErr(xlErrNum)
'Detecta si es negrita
If Rng.Font.Bold Then negrita = True
End Function

En la celda F5 calculamos la suma por el método 1.

=SUMAPRODUCTO(B4:B12;--(C4:C12))

Utilizamos la función SUMAPRODUCTO. El par de signos menos sirven para convertir a numéricos (ceros y unos) los valores de la columna C.

Este método no es perfecto ya que si se cambia una negrita, añadiédola o quitándola, no se recalcula automáticamente hasta que no se actualicen las fórmulas de la columna C.

Método 2

El método 2 calcula directamente la suma de las celdas que están en negrita, sin necesidad de utilizar una columna auxiliar. Pare ello hemos diseñado una nueva función personalizada que se denomina suma_negritas.

Código:

Function suma_negritas(ByVal Rng As Range) As Double
Dim celda As Range
For Each celda In Rng
  If celda.Font.Bold Then
    suma_negritas = suma_negritas + celda.Value
  End If
Next
End Function
Esta fórmula personalizada calcula la suma de las celdas que se indiquen en el rango, y siempre que estén en negrita.

Si añadimos una negrita, o bien la quitamos, la suma no se actualiza automaticamente y debemos pulsar F9, que es la tecla de recálculo manual, o bien escribir algo en alguna celda cualquiera de la hoja.

Contar las negritas

Proponemos al lector que diseñe una función que cuente los valores que se encuentran en negrita, dentro de un rango dado. Por ejemplo, se podría llamar cuenta_negritas.

Y, ¿qué tal una función que calcule el máximo, pero únicamente de las celdas en negrita?. Se podría llamar max_negritas.

jueves, 17 de noviembre de 2011

Crea una hoja Index en tu Excel

Descargar el fichero: index.xlsm

Hagamos un indice en la primera hoja de un libro de Excel, para listar todas las hoja y establecer un hipervínculo que nos lleve a cada hoja. Cuando en un libro de Excel tenemos muchísimas hojas, en ocasiones es aburrido llegar a ir hasta una concreta. Para facilitar nuestro trabajo vamos a establecer una primera hoja llamada Index en la que vamos a escribir el nombre que queremos dar a cada hoja y luego las vamos a hipervincular.


Inicialmente las hojas del libro se denominan: Hoja1, Hoj2, y así hasta Hoja101.



Disponemos de una macro que renombra las hojas dándolas el nombre que hemos indicado en la columna C, comenzando en la celda C5. La primera hoja será la propia hoja en la que se encuentra el listado y que denominaremos Index. Las 100 hojas restantes irán tomando el nombre que se indica en la lista.


Código:

Sub renombra_hoja()
Dim Hoja As Worksheet
Dim Fila As Long
Fila = 5
For Each Hoja In Worksheets
  Hoja.Name = Cells(Fila, 3)
  Fila = Fila + 1
Next
End Sub



Disponemos de otra macro que crea los hipervínculos.


Código:

Sub Hipervincula()
Dim Nombre As String
Range("C5").Select
Do
  Nombre = Selection.Value
  ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
      "'" & Nombre & "'!A1", TextToDisplay:=Nombre
  ActiveCell.Offset(1, 0).Select
Loop While Not IsEmpty(ActiveCell)
Range("A1").Select
End Sub


Incluimos dos botones que lanzan las macros.


Debemos tener especial cuidado de que en la lista existan tantos nombres como hojas existen el el libro de Excel en el que estamos.

¿Y si no quiero cambiar los nombres?

En caso de que desees establecer los hipervínculos pero no quieras cambiar los nombres de las hojas, puedes recurrir al ejemplo siguiente.


Disponemos de una macro que lista los nombres de las hojas existentes y luego le pasamos la macro que crea los hipervínculos.

La macro que lista las hojas existentes es la siguiente.

Código:

Sub Lista_nombres()
Dim Hoja As Worksheet
Dim Fila As Long
Fila = 5
For Each Hoja In Worksheets
  Cells(Fila, "G") = Hoja.Name
  Cells(Fila, "F") = Fila - 4
  Fila = Fila + 1
Next
End Sub

La macro que realiza los hipervínculos la hemos llamado Hipervincula2 que es similar a la macro Hipervincula. Lo único que cambia es la celda de inicio que ataca la macro.

martes, 1 de noviembre de 2011

Consolidar tablas de datos

Puede descargar los archivos de Excel siguientes.
Ya hemos tratado el tema del consolidado. Para ello utilizábamos Tablas Dinámicas con rangos de consolidación múltiple. Puede consultar el Post que se publicó:

En esta ocasión realizaremos el trabajo de consolidación con la herramienta Datos, Consolidar.


En la versión de Excel 2007 se encuentra en el menú Datos.



Añadimos los 4 rangos de datos indicados de la Hoja 3.



Agregamos los 4 rangos.


Es importante marcar:

  • Fila superior
  • Columna izquierda
Al aceptar se genera la tabla del consolidado.


Al cambiar algún dato en la tabla de origen no cambia automáticamente en la tabla del consolidado. Para actualizar la tabla tendríamos que lanzar nuevamente la herramienta de consolidación.

domingo, 30 de octubre de 2011

Rangos variables en fórmulas

Descargar el fichero: FormulasVariables.xlsx

Algo con lo que siempre hemos soñado los usuarios avanzados de Excel es con la posibilidad de poder modificar la sintaxis de una fórmula para que se adapte de forma automática a nuestras necesidades.

Hoja1

En este caso, no es que se logre completamente este sueño, pero si seremos capaces de modificar la función SUMA para que el rango que sume sea el que nosotros elijamos sin tener que editar la fórmula, y sin tener que cambiar el rango, de forma manual.



En la columna B disponemos de 100 números aleatorios. En la celda amarilla indicamos cuantos de estos números deseamos sumar.

En la celda verde conseguimos efectuar la suma del número de sumandos indicado en la celda amarilla. Para ello, utilizamos la función:

=SUMA(INDIRECTO("B4"):INDIRECTO("B"&D5+3))

La función INDIRECTO es ya una vieja amiga de este blog y pertenece a las funciones avanzadas con las que conseguimos grandes resultados.

Además usamos el concatenador: &, que nos permite unir (concatenar) cadenas. Esto es, unimos texto o cadenas alfanuméricas.

En la celda D9 hemos efectuado las suma de los 20 primeros datos de la columna B, y para ello hemos utilizado el método tradicional, usando la función SUMA y como rango tomamos los 20 primeros valores:

=SUMA(B4:B23)

Esta fórmula tiene el inconveniente de que si en la celda amarilla cambiamos el valor, que actualmente es de 20, por otro valor, tendríamos que editar la fórmula y cambiar el rango al que se extiende la función SUMA, si realmente queremos obtener la suma del número de valores indicados en la celda amarilla.

Para ver otras maravillas que se pueden conseguir con la función INDIRECTO puede consultar estos Posts,

Hoja2

Podemos practicar sumando en horizontal.


Hoja3

Calcular el determinante de una submatriz.

En las celdas de color rosa indicamos la posición inicial de fila y columna de la submatriz e indicamos la dimensión. Necesariamente la submatriz ha de ser una matriz cuadrada donde el número de filas y columnas coincide, por lo que únicamente marcamos de color rosa el número de filas (celda E23) y el número de columnas queda igual que el de filas.


Los valores de la matriz se generan de forma aleatoria. Al pulsar la tecla F9 cambian.

Hemos marcado de color verde la submatriz dentro de la matriz principal. Para obtener este color hemos utilizado Formato condicional. Esta es la fórmula empleada en el formato condicional para la celda B5 que luego se extiende al resto de la matriz.
=Y(FILA(B5)>=$C$13+$E$19-1;FILA(B5)<=$C$13+$E$19+$E$23-2;COLUMNA(B5)>=$C$14+$E$20-1;COLUMNA(B5)<=$C$14+$E$20+$E$24-2)

miércoles, 19 de octubre de 2011

Libro Personal en Excel

Descargar el fichero: RentaGeoFracECO2011.xlsm

Mostramos un vídeo de cómo se crea el libro Personal.xlsb en Excel 2007. Este libro nos permitirá disponer de todas las macros que contenga, para que se puedan ejecutar desde cualquier otro libro que abramos en el futuro.

En este caso vamos a incluir en el libro personal las funciones creadas por el usuario:
  • VAgeo
  • VFgeo
que calculan en valor actual y el valor final de rentas geométricas.




En Excel 2003

En Excel 2003 el libro se denomina Personal.xls, y disponemos del siguiente truco:

Podemos crear una macro para que este disponible en todas las hojas de cálculo. Esto se hace con el libro PERSONAL.XLS

Personal.xls es un Libro que se carga cada vez que se inicia Excel, pero no esta visible ya que tiene el atributo de Escondido (Hidden), por lo que si necesitamos modificarlo lo debemos hacer a través del comando "Mostrar" del menú "Ventana".

En caso que no exista lo podemos crear con un libro de trabajo nuevo, salvarlo con este nombre y dejarlo en la carpeta "InicioXL" (XLStart), que se encuentra en la carpeta "Office" del directorio donde esta instalado MSOffice. Cada vez que dejamos de trabajar con este archivo, lo debemos salvar y luego activar el comando "Ocultar" del menú "Ventana", para que no se vea cuando iniciamos la aplicación.

La ruta de mi fichero en Windows XP es:

C:\Documents and Settings\Adolfo\Datos de programa\Microsoft\Excel\XLSTART\PERSONAL.XLS


En Excel 2010 y 2013

En Windows 7 y Windows 8 mi ruta para Excel 2010 y Excel 2013 es la siguiente.

C:\Users\Adolfo\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.XLSB

Desde la versión 2007 en adelante el libro Personal para a tener la extensión XLSB. En la ruta anterior es posible que no vea la carpeta AppData, si esto es así es debido a que por defecto se encuentra oculta, y hemos de pedir al Explorador de Windows que nos muestre las carpetas ocultas.

martes, 18 de octubre de 2011

Facturación por meses

Descargar el fichero: facturacion_mensual.xlsx

Veamos cómo podemos totalizar por meses con varios métodos: SUMAR.SI, SUMAPRODUCTO y sobre todo con funciones matriciales. Destaca el quinto método que no necesita emplear la columna auxiliar 'Mes' (columna D).



Se ha creado un nombre de rango que es en realidad una fórmula. Se denomina 'miMES' y la fórmula es:

=MES(Fechas)

Este nombre de rango es el que nos permite no necesitar el uso de la columna D.

Observar la celda K18 que nos permite totalizar de una forma muy especial:

{=SUMA((miMES={1;2;3;4;5;6;7;8;9;10;11;12})*Ventas)}

Si quisiéramos totalizar únicamente las ventas de los meses 1 y 2 la fórmula a emplear sería:

{=SUMA((miMES={1;2})*Ventas)}


También merece especial mención la celda K20 de color verde:

{=MIN(SI(K6:K17>0;K6:K17;""))}

Con la fórmula matricial anterior hemos conseguido calcular la facturación del mes con menor facturación positiva.

Pulse la tecla de función F9 para observar cómo cambian los datos y los resultados.

domingo, 16 de octubre de 2011

Seleccinar datos con un desplegable

Descargar el fichero: desplegable.xlsx

Utilizar un desplegable es una tarea cotidiana e intuitiva. Lo que pretendemos es que al elegir con el desplegable un año, la consecuencia sea que una tabla con datos se rellene automáticamente con datos de ese año.

Conseguiremos nuestro proposito con el uso de la función INDIRECTO de la que podemos ver unos cuantos Post publicados en este mismo Blog. Para verlos simplemente hemos de seguir este enlace:




En la tabla verde disponemos de los datos correspondientes a tres años. Deseamos que al elegir el año en el desplegable la tabla azul se alimente automáticamente con los datos correspondientes al año elegido.

Observar que la zona azul se ha obtenido con la función INDIRECTO y se ha introducido como función matricial. Esto se puede comprobar al ver las llaves {} que son el rasgo distintivo de las funciones matriciales.

Datos de partida

Los datos de partida son los que se encuentran en la tabla superior (rango B4:E9). Lo que hacemos es crear nombres de rango con los rótulos de columna:
  • Año2009
  • Año2010
  • Año2010
Para crearlos de forma masiva lo que hacemos en la versión de Excel 2007 es seleccionar el rango C4:E9 e ir al menú Fórmulas, y luego elegir Crear desde una selección. De esta forma llegaremos a esta ventana:


Marcamos únicamente donde pone 'Fila superior'.

Si tuviéramos Excel 2003, para llegar a esta ventana el recorrido es: Insertar, Nombre, Crear.

Aceptando esta ventana lo que hemos logrado es crear los tres rangos siguientes:

  • Año2009 =Hoja1!$C$5:$C$9
  • Año2010 =Hoja1!$D$5:$D$9
  • Año2011 =Hoja1!$E$5:$E$9

Tabla para el BUSCARV

Creamos la tabla amarilla para luego poder usar un BUSCARV que nos proporcione el año al elegir desde el desplegable.



Creamos el desplegable

Creamos el desplegable o ComboBox,o también llamado 'Cuadro combinado'.


Esto en Excel 2007 se consigue desde la ficha Programador, y luego en Insertar uno de los Controles de formulario denominado Cuadro combinado.


En Excel 2003 se consigue obteniendo la barra de Formularios y luego eligiendo Cuadro combinado.

Al pulsar con el botón izquierdo del ratón sobre el icono del Cuadro combinado conseguiremos que el cursor se convierta en una cruz finita, y en ese momento crearemos la diagonal del desplegable arrastrando con el ratón sobre la hoja.

Luego, pulsamos el desplegable con el botón derecho del ratón y elegimos 'Formato de control'.


Como rango de entrada ponemos G13:G15 que es donde están escritos los nombres de cabecera que previamente hemos creado.

Donde pone 'Vincular con la celda' ponemos la celda G11. Esto permitirá que al elegir en el desplegable la primera opción en la celda G11 aparezca un 1; si elegimos la segunda opción aparecerá un 2; y si elegimos la tercera opción del desplegable en la celda G11 aparecerá un 3.

BUSCARV

En la celda C11 escribimos la siguiente función:

=BUSCARV(G11;tabla;2;0)

Donde el rango tabla es: F13:G15.

Con ello conseguiremos que al cambiar el valor de la celda G11 según las elecciones que hagamos del desplegable, podamos poner en esta celda (C11) la cabecera de los datos que deseamos obtener.

Son tres posibles cabeceras que podemos obtener:

  • Año2009
  • Año2010
  • Año2010
INDIRECTO como función matricial

Finalmente hemos de emplear la función INDIRECTO. Para familiarizarnos con esta función avanzada de Excel conviene revisar algunos post donde se habla de ella o se utiliza. Para ello se puede seguir el enlace que se ha indicado al inicio de este artículo.

Como vamos a tratarla como una función matricial hemos de seguir los tres pasos típicos de toda función matricial. Para familiarizarnos con esto se aconseja ver el siguiente artículo:


Primero, señalamos el rango C12:C16, que es donde la función matricial dejará su resultado.

Segundo, escribimos la función matricial que empleamos en este caso, que es la siguiente:

=INDIRECTO(C11)

Tercero, para validar no pulsamos Enter, sino que hemos de pulsar tres teclas simultaneamente: CONTROL+MAYUCULAS+ENTER.

Resultado

Si todo ha ido bien hemos conseguido un desplegable donde al elegir el año la tabla de abajo toma los datos correspondientes a ese año de la tabla de arriba. Con ello, hemos conseguido disponer de un desplegable que permite traernos los datos que deseamos.


Esto con tablas de datos realmente grandes puede llegar a ser muy interesante.

Hoja2

Proponemos un segundo método que incluso puede ser mejor que el primero por ser más sencillo.

Consiste en utilizar como desplegable una celda con Validación de datos de tipo LISTA.



En el siguiente vídeo puede ver el proceso de creación por los dos métodos.

El 10%


Podemos crear fórmulas matriciales que hagan referencia a todo un rango o matriz. Veamos cómo se calcula el 10% del rango C12:C16.



Al variar los datos con el desplegable de la celda C11 nuestro 10% también varía.


Para saber más

Puede consultar el siguiente enlace.