lunes, 20 de junio de 2011

Eliminar Filas y Columnas vacías

Descargar el fichero: LimpiaFilas.xlsm


Es frecuente que un informe generado por otro programa se exporte a Excel, por ejemplo en  SAP. Muchos programas contemplan la exportación directa a Excel, y en otras ocasiones lo que se hace es generar un fichero plano, de texto. Sea por un método o por otro, habitualmente los informes contienen filas vacías, y columnas vacías. Presentamos una macro que elimina las filas y las columnas en blanco, dejando el informe compactado.

Hoja 1



Hoja 2

La macro que elimina las filas vacías es la siguiente.


En la macro recorremos todas las filas del rango usado (UsedRange). Se basa en detectar el número de celdas vacías con la función CountA, que en español se denomina CONTARA. Esta función es similar a la función CONTAR. La diferencia es que CONTAR cuenta únicamente valores numéricos, y CONTARA cuenta todo tipo de celdas, sean estas numéricas o de texto.

Creamos la variable Cadena que por concatenación va acumulando todas las filas vacías que se eliminarán. Al concatenar las diferentes filas las separamos por una coma (,) lo que hace que al final tengamos que eliminar la última coma, cosa que se hace con la expresión:
 Left(Cadena, Len(Cadena) - 1).

Esta macro aplicada a hojas en las que sea necesario eliminar muchas filas puede llegar a dar un error derivado de que la variable Cadena necesita una lóngitud mayor de la que es capaz de soportar. Para evitar ese inconveniente hemos creado la macro de la hoja 3 que también elimina las filas vacías, pero lo hace cada vez que las localiza.

Código:

Sub EliminarFilasVacias()
Dim Cadena As String, Fila As Long
With Worksheets("Hoja2") 'Nombre de la hoja
    For Fila = 1 To .UsedRange.Rows.Count
        If WorksheetFunction.CountA(.Rows(Fila)) = 0 Then
            Cadena = Cadena & Fila & ":" & Fila & ","
        End If
    Next Fila
    If Cadena <> "" Then
        Application.ScreenUpdating = False
        .Range(Left(Cadena, Len(Cadena) - 1)).Delete
        Application.ScreenUpdating = True
    End If
End With
End Sub
Esta macro se aplica a la Hoja2 que es la siguiente. Se lanza con un botón que hay en la parte superior izquierda de la hoja.


Hoja 3

En la Hoja3 disponemos de una macro que elimina las filas vacías y otra que elimina la columnas vacías.

La macro que elimina las filas vacías es la siguiente. Es similar a la que hemos utilizado en la Hoja 2.

Código:

Sub EliminarFilasEnBlanco()
Dim n As Long 'nº filas
Dim i As Long
Dim Fila As String
n = ActiveSheet.UsedRange.Rows.Count
For i = n To 1 Step -1
   Fila = i & ":" & i
   If WorksheetFunction.CountA(Range(Fila)) = 0 Then
      Range("A" & i).EntireRow.Delete
   End If
Next i
End Sub
Hemos necesitado hacer el bucle desde n hasta 1 ya que si lo hacemos de 1 a n en caso de existir dos o más filas vacías contiguas quedaría una de ellas sin eliminar.

Para eliminar las columnas vacías existe una macro similar. Es la siguiente.

Código:

Sub EliminarColumnasVacias()
Dim n As Integer 'nº columnas
Dim i As Integer
Dim col As String
n = ActiveSheet.UsedRange.Columns.Count
For i = n To 1 Step -1
    If WorksheetFunction.CountA(Cells(1, i).EntireColumn) = 0 Then
        Cells(1, i).EntireColumn.Delete
    End If
Next i
End Sub

Otra variante para eliminar filas en blanco es la siguiente.

Código:

Sub EliminarFilasEnBlanco_bis()
Dim Fila As Long
For Fila = ActiveSheet.UsedRange.Rows.Count To 1 Step -1
    If WorksheetFunction.CountA(ActiveSheet.Rows(Fila)) = 0 Then
        Cells(Fila, 1).EntireRow.Delete
    End If
Next Fila
End Sub

Si no deseamos que se vea el movimiento en pantalla que origina la eliminación de filas podemos añadir al inicio de la macro (después de los Dim) la línea:
  • Application.ScreenUpdating = False
Y al final de la macro pondríamos la línea:
  • Application.ScreenUpdating = True
Esto hace que mejore el tiempo de ejecución, apreciándose en hojas de muchísimas filas.

Pulsando sobre el desplegable de la hoja 3 podemos generar un informe con filas y columnas en blanco que podemos hacer desaparecer eligiendo las opciones adecuadas en el desplegable (ComboBox).


Otra variante que Elimina Filas Vacías

Sub Elimina_Filas_Vacias()
Dim n As Long 'nº filas
Dim i As Long
Dim Fila As String
n = ActiveSheet.UsedRange.Rows.Count
For i = n To 1 Step -1
Fila = i & ":" & i
If WorksheetFunction.CountA(Range(Fila)) = 0 Then
  Range("A" & i).EntireRow.Delete
End If
Next i
End Sub

jueves, 16 de junio de 2011

UserForm para alimentar una base de datos

Puede descargar el fichero: UserForm.xlsm


Vamos a crear un UserForm, que es un formulario que nos permitirá pedir al usuario que introduzca una serie de datos para ir creando una tabla en Excel, en forma de base de datos.

Supongamos que se trata de la empresa UniLink, Co. Ltd.
Su departamento de personal está creando una pequeña base de datos sobre Excel. Se trata de una tabla con 4 campos: Nombre, Departamento, Extensión y eMail.

Disponemos de un botón incrustado que lanza el proceso y llama al Formulario de entrada de datos.


La macro asociada al botón es la siguiente.


Vamos al Editor de Visual Basic y pedimos insertar un UserForm, para ello utilizamos el menú Insertar que se encuentra en el propio Editor de Visual Basic, y luego pulsamos sobre UserForm.


Esto genera el UserForm1, que aparecerá como una zona rectangular sobre la que posteriormente iremos introduciendo diferentes elementos del Cuadro de herramientas.


Seguidamente se muestra el Cuadro de Herramientas.


Después de trabajar un rato sobre nuestro primer UserForm quedará así:


Al ejecutar el UserForm1 veremos lo siguiente.


Disponemos de la ventana de propiedades para ir cambiando algunas, como por ejemplo el rótulo que aparece en la cabecera del UserForm1. Hemos denominado al formulario 'Ficha personal'. Esto se hace modificando la propiedad Caption.


Otra ventana importante es la ventana del Proyecto. Finalmente llegaremos a manejar tres formularios, que se pueden ver en la imagen siguiente como:
  • UserForm1
  • UserForm2
  • UserForm3


Pulsando con el botón derecho del ratón sobre el UserForm1 podemos pedir que se vea el objeto (el propio UserForm1) o que se vea el código asociado a sus botones y demás herramientas de control.


Si pedimos Ver código del UserForm1 obtendremos la siguiente ventana.


Aquí se programan todos los botones y etiquetas que utilizamos en el UserForm1.


En el desplegable de arriba, a la izquierda podemos elegir diferentes elementos como se ve en la siguiente imagen.


En el desplegable de la derecha podemos elegir diferentes eventos.



El UserForm2 es el siguiente.


El código del UserForm2 se muestra en la siguiente imagen.


El UserForm3 es el siguiente.



El código del UserForm3 se muestra en la siguiente imagen.


Un Ejemplo

En el siguiente vídeo se puede ver un ejemplo realizando un UserForm. Jose Luis Martín es uno de mis alumnos que ha creado una Calculadora Financiera programando todos los pasos con VBA en Excel.

miércoles, 15 de junio de 2011

Selección de Gráficos

Descargar el fichero: selectivo.xlsx


Podemos seleccionar diferentes gráficos con solo pulsar sobre un botón o marcar una opción. Vamos a ver un sistema que permite elegir unos u otros datos sin más que seleccionar una opción. Estos datos son los que alimentan el gráfico y por tanto conseguimos que el gráfico cambie simplemente eligiendo una opción al pulsar un botón.

Hoja 1

Supongamos que tenemos información en forma de tabla sobre dos plantas industriales, una en Barcelona y otra en Madrid.

  • La Tabla 1 (planta de Barcelona) está en el rango B4:F8
  • La Tabla 2 (planta de Madrid) está en el rango B10:F14

Debajo de estas tablas planteamos un sistema que nos permita elegir la planta que deseamos mostrar en el gráfico. Esto se hace estableciendo unas Opciones de formulario, y en este caso, concretamente, hemos elegido dos Botones de opción, uno para Barcelona y otro para Madrid.


Al elegir una u otra planta industrial queda en la celda C23 el número 1 o 2 según hubiéramos elegido la primera opción (Barcelona) o la segunda opción (Madrid).

Ahora creamos una tabla que servirá para alimentar los datos del gráfico. La tabla se encuentra en el rango H19:L23. Para vincular esta tabla con las tablas de Madrid o Barcelona según lo elegido por el usuario, utilizamos la siguiente formula para la celda H20 que luego copiamos al resto de la tabla.

=SI($C$23=1;"Barcelona";"Madrid")

Los rangos utilizados en la Hoja1 son:

  • Barcelona =Hoja1!$C$6:$F$8 
  • Madrid =Hoja1!$C$12:$F$14  

El interior de la tabla se calcula con fórmulas matriciales.

=SI($C$23=1;Barcelona;Madrid)

Incluso la tabla está formateada con Formato condicional para que tome los colores y formatos de la tabla de Barcelona o Madrid según la elección del usuario.


La tabla resultante y el gráfico asociado son los siguientes.



Hoja 2

En la Hoja2 el usuario elige el trimestre que desea representar en el gráfico. Para elegir el trimestre el usuario dispone de un Control de formulario denominado Barra de desplazamiento.


El trimestre elegido se recoge en la celda D16, y con ella se crea un vector (rango D19:D22) que indica el trimestre elegido.

En la tabla I19:K24 se crea una tabla vinculada a las dos tablas anteriores tomando los datos del trimestre elegido por el usuario.


Los rangos creados en la Hoja2 son los siguientes.

  • BCN =Hoja2!$C$6:$F$8
  • MAD =Hoja2!$C$12:$F$14
  • vector =Hoja2!$D$19:$D$22

Veamos como se calcula la Tabla que alimenta el gráfico.

  • El rango J20:J22 de color verde se consigue multiplicando el vector de los trimestres por el rango MAD. La fórmula matricial es: =MMULT(MAD;vector)
  • El rango K20:K22 de color amarillo se consigue multiplicando el vector de los trimestres por el rango BCN. La fórmula matricial es: =MMULT(BCN;vector)

La función MMULT es la que permite multiplicar matrices. Las matrices que se multiplican son MAD o BCN según se trate de la información de Madrid o Barcelona, por el vector de los trimestres. El orden de multiplicación es importante, ya que cambiadas de orden darían error.

Pruebe a cambiar los trimestres pulsando en el control de formulario denominado Barra de desplazamiento, y comprobará como cambia la tabla que se encuentra debajo del gráfico, que es la que alimenta el propio gráfico. La consecuencia de todo esto es que puede modificar el gráfico que se muestra sin más que pulsar un botón.

Para saber más

Puede consultar el siguiente enlace.

Rango Dinámico

Descargar el fichero: RangoDinamico.xlsx


Si tenemos una tabla que cambia de tamaño nos vemos obligados a tener que redefinir el rango de los datos que queremos incluir en un gráfico o en una tabla dinámica. Si la tabla contiene un número de filas que varían con frecuencia es bastante tedioso tener que cambiar el rango de datos para el gráfico, y lo mismo con la tabla dinámica. Vamos a exponer un método por el cual no tendrá que preocuparse por el tamaño de su tabla ya que tanto el gráfico como la tabla dinámica tomará el tamaño necesario de forma automática.

En nuestro fichero de ejemplo disponemos de cuatro columnas con datos generados de forma aleatoria. Si queremos ampliar el número de filas de la tabla lo único que debemos hacer es copiar su última fila hacia abajo. Para reducir su número de filas borraremos, por abajo, tantas como deseemos.

Inicialmente la tabla tiene 20 registros (filas de datos).


Deseamos crear un gráfico como el siguiente donde se muestren los 20 puntos que corresponden a los 20 datos de Facturación a lo largo del tiempo. Hemos añadido una línea de tendencia (recta de regresión).



Si queremos que el gráfico ajuste el número de puntos a los que contenga la tabla independientemente de su tamaño y de forma automática debemos definir los rangos que tomará el gráfico. La definición de rangos será un tanto curiosa ya que no nos limitaremos a seleccionar el área que deseamos asociar al rango sino que trabajaremos con fórmulas.

Vamos a utilizar la función DESREF y la función CONTARA de forma anidada.

Función DESREF

La función DESREF es una función que puede emprearse de dos maneras:

  1. Utilizando los 3 primeros argumentos permite extraer un valor de una celda que se encuentre en una tabla. Para ello debemos indicar desde que celda debemos comenzar a contar (punto de partida), indicando el número de filas hacia abajo que nos hemos de desplazar, y el número de columnas a la derecha que nos debemos mover. Esta forma de emplear DESREF no es matricial.
  2. Utilizando los 5 argumentos de la función. En este caso la función es matricial y permite extraer un bloque de celdas. Se ha de indicar el punto de partida, la celda de la esquina superior izquierda y las dimensiones del bloque.
La sintaxis de DESREF en su versión matricial es:

=DESREF(ref;filas;columnas;alto;ancho)

donde

  • ref es la celda desde la que partimos. Es el punto de partida (Km. 0). Para los que conozcan Madrid es el kilómetro cero de la Puerta del Sol
  • filas es el número de filas hacia abajo que nos hemos de desplazar para llegar a la celda que marca la equina superior izquierda del rango que deseamos extraer. Si las filas son negativas nos movemos hacia arriba, siempre tomando como punto de partida nuestro kilómetro cero
  • columnas es el número de columnas a la derecha que nos hemos de desplazar para llegar a la celda que marca la esquina superior izquierda del rango que deseamos extraer. Si las columnas son negativas indica que nos movemos a la izquierda de nuestro kilómetro cero
  • alto es el número de filas que contiene el rango que deseamos extraer
  • ancho es el número de columnas que contiene el rango que deseamos extraer
Si queremos utilizar esta fórmula debemos considerar que se trata de una fórmula matricial y que por tanto se han de dar los tres pasos que siempre requieren este tipo de fórmulas:
  1. Paso 1. Primero debemos seleccionar el rango donde la fórmula matricial dejará su resultado. Esto es importante, ya que muchas fórmula matriciales no dejan su resultado un una sola celda, sino en un rango de celdas. Este primer paso indica que este rango debemos seleccionarle.
  2. Escribimos la fórmula matricial de que se trate. En este caso sería DESREF si queremos probar con ella.
  3. Para validar la fórmula no pulse ENTER. Se deben pulsar simultáneamente las tres teclas siguientes: CONTROL + MAYUSCULAS + ENTER

Función CONTARA

Hablemos ahora de la función CONTARA. Para contar existen dos funciones:
  • CONTAR: cuenta las celdas numéricas del rango que indiquemos
  • CONTARA: Cuenta todo, números y texto
Aplicadas estas funciones sobre la columna E, donde se encuentra la facturación los resultados obtenidos son:

  • =CONTAR(E:E)      da como resultado 20, que son los registros de la tabla
  • =CONTARA(E:E)   da como resultado 21, que son los registros numéricos más la cabecera
Creación de Rangos

Vamos a crear un nombre de rango denominado laFactura que incluya todos los datos de facturación. Incluirá desde la celda E3 hacia abajo hasta el final de la columna E. Inicialmente el rango será E3:E22 ya que  la tabla inicialmente tiene 20 registros. Pero al indicar el rango que se asocia a ese nombre de rango lo haremos con fórmula para que sera automático y se ajuste a la longitud de la columna E que exista en cada momento.

La fórmula para el nombre de rango laFactura es:

=DESREF(Hoja1!$E$2;1;0;CONTARA(Hoja1!$E:$E)-1;1)

Ahora vamos a crear el nombre de rango laFecha para se ajuste al rango que marca los datos de fecha de la columna B, desde la celda B3 hasta la última fecha que se encuentre en la columna B.

La fórmula para el nombre de rango laFecha es:

=DESREF(Hoja1!$B$2;1;0;CONTARA(Hoja1!$B:$B)-1;1)


Los dos rangos anteriores se utilizaran para crear el gráfico dinámico. Para la tabla dinámica que crearemos posteriormente vamos a definir el nombre de rango Tabla que abarca toda la tabla incluida la cabecera, y las cuatro columnas que la constituyen.

La fórmula para el nombre de rango Tabla es:

=DESREF(Hoja1!$B$2;0;0;CONTARA(Hoja1!$E:$E);4)



Nombrar rangos

Ahora debemos nombrar los rangos.

  • En Excel 2003 la forma ortodoxa de definir un rango es seleccionando Insertar, Nombre, Definir. 
  • En Excel 2007 debemos ir a la pestaña de Fórmulas y pulsar sobre la opción Administrador de Nombres.


Gráfico Dinámico

Creamos un gráfico de tipo Dispersión del tipo Dispersión solo con marcadores.


Indicamos el origen de los datos indicando los nombres de rango. Para el eje X utilizamos el rango laFecha, y para el eje Y utilizamos el rango laFactura.


Importante

Al indicar el rango para el eje X e Y debemos indicar no solo el nombre del rango sino también el nombre del fichero, sin olvidar el signo de admiración (!) y el signo igual al inicio de la expresión (=).

Para los valores X de la serie debemos escribir exactamente lo siguiente:

=RangoDinamico.xls!laFecha

Para los valores Y de la serie debemos escribir exactamente lo siguiente:

=RangoDinamico.xls!laFactura

Si copiamos la última fila de la base de datos hacia abajo hasta la fila 102 habremos obtenido 100 registros que automáticamente quedarán representados en el gráfico sin necesidad de que toquemos los rangos. El resultado podría ser el siguiente.


Observamos que los puntos representados en el gráfico han aumentado automáticamente pasando de los 20 iniciales a los 100 que ahora.


Tabla Dinámica

Vamos a crear una Tabla Dinámica utilizando los 20 datos iniciales de nuestra tabla.

Al confeccionar la Tabla dinámica nos piden el rango de los datos y en ese momento indicaremos el nombre de rango Tabla que previamente hemos creado.

En Configuración de campo elegimos Cuenta.


Obtenemos una Tabla Dinámica similar a la siguiente, donde el número de elementos totales es de 20.


Al ampliar el número de filas de la tabla hasta 100 filas seguimos viendo en la Tabla dinámica que el número de elementos es de 20. Para poder ver los 100 elementos debemos actualizar la Tabla dinámica. Esto se consigue pulsando con el botón derecho del ratón sobre la propia Tabla dinámica y eligiendo en el menú contextual la opción Actualizar.


Tras actualizar la Tabla dinámica podemos observar que ahora son 100 los datos considerados.


lunes, 13 de junio de 2011

Convertir una Tabla de doble entrada en una BD

Descargar el fichero: Cronograma.xlsx


Vamos a convertir una tabla de doble entrada (con cabeceras de fila y columna) en una auténtica base de datos, esto es, una tabla donde la información se organiza por campos (columnas). Para ello utilizaremos Tablas Dinámicas con Rangos de Consolidación Múltiple. Posteriormente, partiendo de la base de datos, resumiremos la información utilizando nuevamente Tablas Dinámicas, o la utilidad que tiene Excel denominada Subtotales.

jueves, 9 de junio de 2011

Estructuras repetitivas: Do While ... Loop

Descargar el fichero: do_while_loop.xlsm


Cuando conocemos cuantas veces se ha de repetir el bucle usamos la estructura FOR ... NEXT, pero cuando desconocemos el número de veces que se ha de repetir el bucle debemos ir a una estructura del tipo DO WHILE ... LOOP, o alguna de sus variantes. Esto es lo que vamos a ver en esta ocasión.

Do While ... Loop

Hacer mientras se cumpla la condición.

La sintaxis de esta estructura es la siguiente:

Do While condición
....
....
Loop
Esta estructura permite hacer un bucle que se repetirá mientras se siga cumpliendo la condición. El flujo del programa se ejecuta de arriba hacia abajo. Al llegar a la linea superior del bucle se analiza la condición y si es verdadera se ejecutan las líneas interiores del bucle. Al llegar al Loop se devuelve el flujo del programa al inicio, y se vuelve a analizar la condición. Este proceso se repite mientras la condición sea verdadera, por tanto, en algún momento se debe producir algún cambio en las sentencias interiores del bucle que hagan que la condición se transforme en falsa para que no se siga repitiendo el bucle de forma indefinida. En el momento en el que la condición es falsa el bucle ya no se ejecuta y se devuelve el control del flujo del programa a las sentencias que se encuentres debajo del Loop.

Hemos diseñado un ejemplo donde generamos números aleatorios entre 1 y 100, y se los asignamos a la variable i.

i = Int(Rnd * 100) + 1

Con la variable s vamos a sumar los valores que va tomando i. Esto es lo que se denomina un ACUMULADOR, que va añadiendo valores a la variable s.

s = s + i

Esta expresión no se debe interpretar como lo haríamos en matemáticas. En programación el signo igual indica que lo que hay a la derecha se va a asignar a la variable que hay a la izquierda.

La interpretación correcta es que tomamos el antiguo valor de la variable s y le sumamos lo que vale i, y esa suma se asigna al nuevo valor que ahora toma la variable s.

Los valores que van tomando las variables i y s se escriben en las columnas 2 y 3, que son las columnas B y C.

Para que la fila vaya cambiando en el instrucción Cells(fila, columna) hemos creado una variable que precisamente se llama fila y que dentro del bucle, y al final de éste, incrementa uno.

fila=fila+1

De esta forma, la filas se van incrementando de uno en uno a medida que el bucle va haciendo sus ciclos. Esto es lo que en informática se llama CONTADOR.

La condición que hace que se repita el bucle es s<1000, lo que provoca que el bucle se repita MIENTRAS se cumpla que s, que es el acumulado de la variable i, sea inferior a 1000.


El resultado es que disponemos de dos columnas con números aleatorios. En la columna B los números aleatorios son enteros entre 1 y 100, y en la columna C se encuentran sus acumulados. El último número de la columna C será un número entre 1000 y 1099, que al ser mayor o igual a 1000, hará que la macro se detenga.

Necesitamos borrar el rango que hemos escrito en las columnas B y C, para que al generar una nueva serie ve valores se haga sobre una zona limpia. Esto se hace con la macro Borra.



Do ... Loop While

Do
....
....
Loop While condición


Esta estructura es similar a la anterior pero la condición se evalúa al final. La consecuencia inmediata de esto es que el flujo del programa entra en el bucle directamente la primera ver que se ejecuta. Al final se evalúa la condición y se decide si se repite el bucle, pero al menos se ha ejecutado una vez.





Do ... Loop Until

Hacer hasta que se cumpla la condición

Do
....
....
Loop Until condición


La condición se evalúa y el bucle se repite hasta que se cumpla la condición.

Hemos tenido que cambiar la condición para obtener resultados similares. Ahora la condición es s>=1000 ya que el bucle se repite hasta que se cumpla la condición.

También podríamos haber puesto el comando NOT, sin necesidad de tocar la condición. Quedaría así:

Loop Until Not (s < 1000)


Una variante poco ortodoxa

La siguiente macro (Bucles4) es una variante de la anterior. Decimos que es poco ortodoxa debido a que el Loop no lleva condición que haga que finalice, por lo que en teoría los ciclos del bucle seguirían indefinidamente. Para detener estos infinitos ciclos del bucle lo que hacemos es incluir dentro del bucle un condicional (IF) que haga que cuando se cumple cierta condición la macro finalize (END).


Sub Bucles4()
Dim i As Byte
Dim s As Integer
Dim fila As Byte
Borra
s = 0
fila = 1
Do
   i = Int(Rnd * 100) + 1
   s = s + i
   Cells(fila, 2) = i
   Cells(fila, 3) = s
   fila = fila + 1
   If s > 1000 Then End
Loop
End Sub


Hoja 2

En la Hoja2 disponemos de una tabla y deseamos desplazarnos por ella hasta que encontremos una celda vacía.

Sub Desplaza()
Do While Not IsEmpty(ActiveCell)
   ActiveCell.Offset(1, 0).Activate
Loop
End Sub

Nos situamos en una celda que contiene datos, por ejemplo, la celda B4 y ejecutamos la macro. al finalizar la ejecución el cursor aparecerá en la primera celda vacía que encuentre en la columna B.

La línea

ActiveCell.Offset(1, 0).Activate

lo que hace es desplaze el cursor una celda hacia abajo respecto a su posición actual. Como esta línea esta dentro de un bucle se ejecutará repetidamente hasta que el flujo del programa se salga de bucle.

El bucle se repite mientras la celda activa no sea una celda vacía. Esto supone, que el cursor se vaya desplazando por la columna donde se dejara inicialmente y que no pare de bajar hasta que se encuentre con una celda vacía.


Este bucle es muy útil para encontrar en una base de datos el final de la tabla. Para ello, debemos situar el cursor en la columna donde se encuentre el campo llave o campo clave. Esto ha de ser así ya que sabemos que al tratarse de una variable sin espacios vacíos el cursor bajará hasta el final.

Práctica (Hoja 3)

Le invitamos a crear un código que se desplace hacia arriba y que se sitúe en una tabla en la cabecera de la columna donde se entrontrara inicialmente el cursor.


En la Hoja3, sitúe el cursor en la celda B24. El objetivo es que la macro desplace el cursor de forma ascendente hasta la celda que hace el papel de cabecera de esta columna, en nuestro caso, la celda B3.

Hoja 4

Convertir en mayúsculas los valores de un rango. Esto se hace con UCase y queremos pasar a minúsculas se usaría LCase. Veamos el código.


Sub Ciudades()
Dim R As Range
Set R = ActiveCell.CurrentRegion
Dim Inicial As Long
Dim Final As Long
Inicial = 1
Final = R.Count
Dim i As Long                      'Índice
Dim Ciudad As String               'Texto de la celda
i = Inicial
Do
  Ciudad = R.Cells(i).Value        'Toma la ciudad
  R.Cells(i).Value = UCase(Ciudad) 'Escribe la ciudad en mayúsculas
  i = i + 1                        'Contador
Loop While i <= Final
End Sub

miércoles, 8 de junio de 2011

Tablas Dinámicas y Campos calculados

Descargar el fichero: TDcampocalculado.xlsx


Las Tablas Dinámicas han supuesto la revolución en Hojas de Cálculo de los últimos años. Permiten generar informes rápidos y flexibles. Si usted llega a conocer bien su funcionamiento puede cambiar radicalmente la gestión de su departamento o unidad de negocio.

En este artículo vamos a crear una Tabla Dinámica partiendo de una base de datos. En la tabla dispondremos de los costes de diferentes departamentos de la empresa para el año 2010 y la previsión para 2011. Crearemos un campo calculado que nos permita observar el incremento de cada departamento en estos años.

La base de datos de partida es sencilla.

Coste por Proyecto y Departamento


En Excel 2007 vamos al menú Insertar y luego Tabla Dinámica. Siguiendo unos sencillos pasos llegamos a crear una tabla dinámica como la que se muestra en la siguiente imagen:



Disponemos de los costes del año 2010 y la previsión para 2011 por cada uno de los departamentos. Los cuatro proyectos se han establecido como filtro de página en la parte superior de la tabla dinámica.

Ahora deseamos disponer de una columna más que nos indique la variación porcentual experimentada por los costes entre los años 2010 y 2011. Este objetivo se podría lograr por varios métodos:

  1. Escribiendo en la celda D5 la fórmula: =C5/B5-1. Esta fórmula nos da el incremento en tanto por uno. Para verlo en porcentaje basta pulsar sobre el icono de porcentaje (%).
  2. Establecer la fórmula anterior pero vinculando sobre las celdas C5 y B5. En este caso veremos que la fórmula utiliza la función IMPORTARDATOSDINAMICOS. Esta forma de trabajar tiene la ventaja de que ésta función apunta a la tabla dinámica y por tanto no perdemos el vínculo dinámico con la base de datos.
  3. Crear un campo calculado. Este es el método que utilizaremos en este artículo.


Creación del campo calculado

En Excel 2007 con el cursor sobre la tabla dinámica veremos arriba una nueva opción denominada:

Herramientas de tabla dinámica

Al pulsar sobre ella se abren un nuevo menú sobre el que pulsaremos sobre Formulas.


La imagen anterior puede diferir de la que usted pueda ver en pantalla, ya que en Excel 2007 la cinta de opciones muestra diferentes iconos, o los muestra más o menos resumidos en función de la resolución de su pantalla y del tamaño de ventana que utilice.

Al pulsar sobre Fórmulas elegimos Campo calculado.


Aparece una ventana denominada Insertar campo calculado en el que crearemos la fórmula:

=’2011′/’2010′-1

La fórmula se crea introduciendo los campos (columnas) de la tabla dinámica. En este caso calculamos el porcentaje de variación por la clásica fórmula:

Valor Final / Valor Inicial -1

Expresión que es igual a la siguiente:

(Valor Final – Valor Inicial) / Valor Inicial

En nuestro caso los costes del año 2010 son los valores iniciales y las previsiones para 2011 son los valores finales.


Esto genera una nueva columna que denominamos Var.% que recoge la variación porcentual de los costes entre los años 2010 y 2011. Inicialmente los valores que nos dan están en tanto por uno y hemos de ser nosotros los que debemos dar formato a esos valores como Porcentaje de dos decimales.


Los campos calculados son muy útiles al trabajar con tablas dinámicas y tienen la ventaja de que no perdemos el vínculo dinámico con la base de datos.

Gráfico Dinámico

Sitúa el cursor sobre la tabla dinámica y pulsa sobre la opción que verás arriba denominada Herramientas de tabla dinámica. Luego pulsa sobre el icono que te permite crear un gráfico dinámico tal y como se muestra en la siguiente imagen.


Elegimos el tipo de gráfico y de forma instantánea dispondremos de un gráfico muy flexible con muchas opciones que podemos modificar.



Ejercicio propuesto

En la Hoja3 disponemos de una base de datos con 200 registros con los siguientes campos: Fecha, Artículo, Facturación y Unidades. Nuestro objetivo es crear una tabla dinámica agrupada por meses y trimestres en la que introducimos un campo calculado que nos proporcione el precio medio de venta en cada mes.

Todos los datos de la base de datos son aleatorios. Así la fecha es un valor aleatorio del primer semestre del año 2011, y se genera con la fórmula:

=ALEATORIO.ENTRE(FECHA(2011;1;1);FECHA(2011;6;30))

Los posibles artículos son cinco y se generan aleatoriamente con la fórmula:

=ELEGIR(ALEATORIO.ENTRE(1;5);”Art1″;”Art2″;”Art3″;”Art4″;”Art5″)

En Excel 2003 y anteriores para que no de error la fórmula ALEATORIO.ENTRE debemos haber activado el complemento de Herramientas para análisis. Esto se puede activar en el menú Herramientas, Complementos.

Agrupando las fechas simultáneamente por meses y por trimestres obtenemos la tabla dinámica que se muestra en la imagen.



Ahora hemos de crear el campo calculado que insertará una nueva columna en la tabla dinámica. Pretendemos calcular el precio medio, por tanto hemos de dividir la facturación entre el número de unidades.


La tabla dinámica que obtenemos ya incorpora el campo calculado Precio medio.


Los resultados numéricos que usted obtenga serán diferentes de los que se muestran en la anterior imagen, esto es debido a que la base de datos trabaja con valores aleatorios.

Podemos ver cómo cambian los valores de la tabla dinámica al actualizarla. Para ello pulse con el botón derecho del ratón sobre la tabla dinámica y elija Actualizar.



Excel 2010

Para crear un campo calculado en Excel 2010 sigue estos pasos:
  1. Partimos de los datos originales
  2. Creamos la tabla dinámica pulsando sobre: Insertar, Tabla dinámica, y diseñamos la tabla según nuestras preferencias
  3. La tabla dinámica ya esta creada. Ahora nos situamos con el cursor dentro de cualquier celda de la tabla dinámica y veremos arriba una pestaña denominada “Herramientas de tabla dinámica”. Esta pestaña tiene dos sub-pestañas denominadas: Opciones y Diseño. Nos situamos en Opciones.
  4. Pinchamos sobre Cálculos y luego sobre Campos, elementos y conjuntos, y finalmente pinchamos sobre Campo calculado.
  5. Luego se siguen los pasos vistos en Excel 2007 ya que el proceso de creación del campo calculado es similar.