jueves, 21 de mayo de 2015

Reordenar Tablas dinámicas

Vamos a generar una Tabla dinámica partiendo de una Base de Datos. La idea es ver que las Tablas dinámicas nos permiten organizar los campos (columnas de la base de datos) como mejor nos parezca para llegar a obtener el informe deseado.

El archivo de Excel que contiene el caso práctico se puede descargar del siguiente enlace.

miércoles, 20 de mayo de 2015

Fechas vacaciones

Cómo saber si se solapan las vacaciones de los trabajadores de una empresa.

Mostramos un caso donde se ve de forma gráfica si los periodos vacacionales se solapan o no.

Puede descargar el archivo de Excel siguiente.


Pulse la tecla F9 de recálculo manual para ver cómo cambia la simulación.


Se ve repetido el informe. Arriba todas las barras son azules. Abajo se ha marcado la zona de solapamiento en color naranja.

lunes, 18 de mayo de 2015

Agrupa tablas

Deseamos agrupar varias tablas de doble entrada para formar con ellas una base de datos y luego poderla atacar con Tablas dinámicas. Vamos a resolverlo mediante dos métodos.

  • Método 1: usando Tablas dinámicas con rángos de consolidación múltiple
  • Método 2: con macro


Método 1: usando Tablas dinámicas con rángos de consolidación múltiple


Usaremos Tablas dinámicas con rangos de consolidación múltiple para poder agrupar o consolidar varias tablas de doble entrada.



Puede descargar el siguiente archivo de Excel que contiene el caso desarrollado en el vídeo.

Método 2: con macro

Esta es la macro que permite agrupar varias tablas de doble entrada para formar una verdadera base de datos con 4 campos.



Sub CreaBD()
Dim n As Byte 'número de tablas de doble entrada
Dim i As Integer, j As Long, k As Integer, r As Long, p As Byte, q As Long
Dim nBD As Long 'número de registros de la BD completa
Dim Celda As Range
Dim Tabla() As Range 'Es un objeto en forma matricial
Dim rngStart As Range
Dim rngEnd As Range
Dim A()
Dim BD() 'Base Datos en forma matricial. 4 campos
n = InputBox("¿Cuántas tablas de doble entrada desea agrupar?", , 3)
ReDim A(n, 5)
ReDim Tabla(n) 'Si n fuera 3 tendríamos 3 tablas que son objetos de tipo Range
For i = 1 To n 'por cada tabla
  Set Celda = Application.InputBox( _
    prompt:="Seleccione una celda de la Tabla " & i, Type:=8)
    'Type:=8 significa: una celda de referencia como un objeto Range
  A(i, 1) = Celda.Worksheet.Name 'nombre de la Hoja donde está la tabla
  Set Tabla(i) = Worksheets(A(i, 1)).Range(Celda.Address).CurrentRegion
  A(i, 2) = Tabla(i).Rows.Count 'Número de filas de la tabla
  A(i, 3) = Tabla(i).Columns.Count 'Número de columnas de la tabla
  Set rngStart = Tabla(i).Cells(1, 1)
  Set rngEnd = Tabla(i).Cells(Tabla(i).Rows.Count, Tabla(i).Columns.Count)
  A(i, 4) = rngStart 'Primera celda de la tabla
  A(i, 5) = rngEnd 'Última célda de la tabla
  nBD = nBD + (A(i, 2) - 1) * (A(i, 3) - 1) 'acumula el número de registros de cada tabla
Next i
'Como ya sabemos la dimensión de BD la redimensionamos
ReDim BD(4, nBD)
'Escribimos la base de datos BD
For i = 1 To n 'para cada Tabla
  For j = 1 To A(i, 2) - 1  'Para cada fila de la tabla (ciudades)
    For k = 1 To A(i, 3) - 1  'Para cada columna de la tabla (meses)
      r = r + 1 ' r es el número de registro de la base de datos BD
      BD(1, r) = Tabla(i).Cells(j + 1, 1) 'Ciudad
      BD(2, r) = Tabla(i).Cells(1, k + 1) 'Mes
      BD(3, r) = Tabla(i).Cells(j + 1, k + 1) 'Valor
      BD(4, r) = A(i, 1) 'Tabla
    Next k
  Next j
Next i
'Añadimos una hoja nueva
Sheets.Add After:=Sheets(Sheets.Count)
'Vamos a la hoja nueva
Worksheets(Sheets.Count).Activate
'Creamos la cabecera de la BD
[B2] = "Base de datos consolidada"
[B4] = "Ciudad"
[C4] = "Mes"
[D4] = "Valor"
[E4] = "Tabla"
'Escribimos la base de datos
For p = 1 To 4 'las 4 columnas
  For q = 1 To r ' r es el número de registros de la base de datos BD
    Cells(q + 4, p + 1) = BD(p, q)
  Next q
Next p
End Sub

El evento SelectionChange

Vamos a crear un caso práctico sencillo que nos permitirá comprender cómo funciona el evento SelectionChange. Primero veamos lo que es un Evento.

Programación por Eventos

En muchos de los lenguajes de programación modernos existe la denominada programación por eventos que permite lanzar procedimientos cuando se detecta que ha sucedido un evento. Los eventos pueden ser de muchos tipos y dependen del lenguaje utilizado. Por ejemplo, un evento puede ser pulsar una vez el ratón, o pulsar dos veces sobre el ratón, o pulsar sobre el botón derecho, o escribir en una celda, o actualizar la hoja de cálculo, o ir a otra hoja. En otros lenguajes, un evento podría ser pasar el cursor del ratón sobre un banner que hace que la publicidad que contiene se agrande o que comience un vídeo o un sonido.

En Excel se están aumentando los eventos disponibles en cada versión, en especial los relacionados con Tablas dinámicas. En Excel 2010 son los siguientes.


El evento Worksheet.SelectionChange

El evento Worksheet.SelectionChange nos permite lanzar un procedimiento cada vez que seleccionamos un rango de celdas diferente.

Caso práctico

El caso práctico que planteamos consiste en proporcionar en una celda la suma de los valores numéricos que contengan el rango que seleccionemos. De tal forma que al ir cambiando la selección inmediatamente y sin lanzan ninguna macro por nuestra parte en la celda D4 (de color amarillo) nos de continuamente el valor de la suma de los elementos seleccionados.



La programación del evento es la siguiente que se debe colocar en la zona de código correspondiente a la Hoja1 que es donde están los elementos que deseamos sumar.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim celda As Range
Dim suma As Double
For Each celda In Target
  If IsNumeric(celda) Then
    suma = suma + celda.Value
  End If
Next
[D4] = suma
End Sub

Puede descargar el archivo de Excel del siguiente enlace.

domingo, 17 de mayo de 2015

Eliminar espacios en blanco

Es frecuente que necesitemos eliminar espacios en blanco especialmente cuando hemos importado datos de algún sistema de reporting y hemos utilizado Datos > Texto en columnas.

Excel dispone de dos funciones que nos permiten eliminar espacios de una cadena alfanumérica. Son las siguientes.

=Espacios(texto)


Función de la categoría Texto. Quita todos los espacios del texto excepto los espacios individuales entre palabras. Esto es, elimina los espacios que existen antes y después de una frase y de los interiores deja uno entre palabras, pero si existe más de uno solo deja uno.

Ejemplo


Tenemos el siguiente texto:     Hola Madrid  y  Lisboa   

Contiene tres espacios al inicio de la frase, tres al final, uno antes de Madrid y una pareja de espacios antes de la 'y' otra pareja después.

Aplicando la función

=Espacios("   Hola Madrid  y  Lisboa   ")

obtenemos lo siguiente

Hola Madrid y Lisboa

Es una frase normal donde únicamente existen tres espacios situados entre palabras. Por tanto, se han eliminado 3 espacios al inicio, 3 al final, y 2 interiores.


=Sustituir(Texto;Texto_original;Texto_nuevo;[Núm_de_ocurrencia])


Reemplaza el texto existente con texto nuevo en una cadena.

Ejemplo


Disponemos de la siguiente frase en la celda A1

Balance de Enero y Cuenta de Resultados de Enero

Deseamos cambiar el mes de Enero por el de Febrero, para ello podemos usar la función Sustituir

=Sustitutir(A1;"Enero";"Febrero")

El cuarto argumento Núm_de_ocurrencia es optativo y si no se indica ningún valor se sustituyen todos los Eneros que se encuentren.

El resultado obtenido será el siguiente

Balance de Febrero y Cuenta de Resultados de Febrero


El carácter 160 en Excel


El carácter 160 en Excel se ve como un espacio en blanco pero no lo es, ya que el espacio en blanco se corresponde con el carácter 32. Cuando importamos texto o datos de Internet o de algún sistema de contabilidad, facturación, logística, etc, pudieran importarse caracteres que aparentemente son espacios en blanco pero en realidad no lo son ya que se trata del famoso carácter 160.

Reemplazar


Lo que procede en muchos casos es sustituir ese carácter por un verdadero espacio en blanco, o en otras ocasiones, lo que interesa es eliminarlo completamente.

Para sustituirle podemos usar Buscar y Reemplazar, por ejemplo pulsando Control+L, y así poder reemplazar el carácter 160 por un verdadero espacio en blanco.

El carácter 160 se puede generar en una celda escribiendo la siguiente fórmula.

=CARACTER(160)

También se puede obtener pulsando Alt+255. El número se debe marcar con el teclado numérico mientras se mantiene presionada la tecla Alt.

Macro 1


Otro método para reemplazar todo es usar la siguiente macro.

Como las macros no se puden deshacer es conveniente que grabe antes de lanzar la macro ya que el resultado de la macro sobre nuestra hoja aún no está probado.


Sub Reemplazalo()
Cells.Replace What:=Chr(160), Replacement:=Chr(32), LookAt:=xlPart, SearchOrder _
  :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub



Macro 2


Existe una segunda macro que es posible que le guste más ya que se trata de una función programada en VBA que lo que hace es ir recorriendo todos y cada uno de los caracteres de una celda y elimina todos los caracteres 160 y también todos los caracteres 32. Esto es, deja el contenido de la celda sin ningún espacio. La función es la siguiente.


Function EliminaEspacios(texto As String) As String
Dim i As Byte
Dim n As Byte 'es la longitud de la cadena
Dim Letra As String * 1
Dim NewTexto As String 'nueva cadena sin espacios
n = Len(texto)
For i = 1 To n
  Letra = Mid(texto, i, 1)
  'chr(32) es un espacio en blanco
  'chr(160) es similar a un espacio en blanco en Excel
  If Letra <> Chr(160) And Letra <> Chr(32) Then
    NewTexto = NewTexto & Letra
  End If
Next i
EliminaEspacios = NewTexto
End Function


Caso práctico


Paso 1

Vamos a tomar un listado de los apellidos más frecuentes en España de la siguiente página web. Los tomamos simplemente seleccionando y copiando y pegando en Excel.



El resultado lo encontrará en la Hoja1 del siguiente fichero de Excel.




Paso 2

En la Hoja2 separamos en tres columnas lo importado usando para ello Datos > Texto en columnas.


Si probamos con "Delimitados" y pedimos que use como delimitador el 'Espacio' podemos comprobar que no conseguimos nuestro objetivo ya que no se detecta ningún espacio. El motivo es que lo que parecen ser espacios en realidad se trata del carácter 160 que no es detectado como espacio.

Esto nos obliga usar la separación de columnas "De ancho fijo" obteniendo lo que se puede ver en la Hoja2.

Paso 3

Para eliminar las finas vacías y las filas donde únicamente aparece la letra del abecedario lo que hacemos es lo siguiente.

  • Seleccionamos todo el área de datos incluida cabecera
  • Pulsamos F5 que es Ir a... y pulsamos sobre el botón Especial
  • Elegimos la opción 'Celdas en blanco'
  • Eso lo que hace es seleccionar las filas que deseamos eliminar
  • Ahora vamos al menú Inicio > Eliminar > Eliminar filas de hoja
  • Con esto hemos eliminado las filas que no nos interesaban, las vacías y las del abecedario
  • El resultado lo puede ver en la Hoja3

Paso 4

En la Hoja4 vamos a utilizar la fórmula =EliminaEspacios(texto) para quitar tanto el carácter 160 como el carácter 32, que es el espacio en blanco, en caso de que lo hubiera.

Sitúese en la celda E2 y escriba la fórmula siguiente.

=EliminaEspacios(B2)

Copie esa fórmula a su derecha a las celdas F2 y G2.

Copie la celda E2:G2 y copie sus fórmulas hasta abajo, hasta la fila 1035.

Observe que los datos numéricos de la columna F no son verdaderamente numéricos. Son texto, se puede ver que están alineados al la izquierda. Para convertirlos en verdaderamente numéricos disponemos de dos métodos.

Método 1. Cambien la fórmula de la celda F3 por la siguiente y cópiela hacia abajo.
=VALOR(EliminaEspacios(C3))

Método 2. Cambien la fórmula de la celda F3 por la siguiente y cópiela hacia abajo.
=--EliminaEspacios(C3)

Si todo ha funcionado bien ya tiene la base de datos limpia de espacios y limpia del famoso carácter 160.

Paso 5

Ahora solo falta copiar la base de datos, que son las columnas E, F y G, y pegarlas con pegado especial valores en la Hoja5.


Otra posibilidad


Otra posibilidad para resolver este caso práctico consiste en importar de página web la tabla de apellidos y luego usar Datos > Texto en columnas. La ventaja de este método es que en este caso no necesitamos usar la macro que quita el carácter 160 ya que al vincular con página web este carácter no nos da problemas. Finalmente convendría romper el vínculo.