lunes, 18 de mayo de 2015

Agrupa tablas

Archivo de Excel utilizado: agrupa_tablas.xlsm

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.





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

3 comentarios:

  1. Estimado Adolfo, tu blog es excelente, ojalá me puedas ayudar con mi fórmula:

    =SI((Y(P15="Ascendente")),SI (15>O15>-5,"Verde",SI(-10<O15<-5,"Amarillo",SI(-10<O15,"Rojo",SI(15<015,"Fallo")))))

    Lo que pasa es que por default me elige verde, aunque cambie el valor de la celda O15.

    Muchas gracias, un cordial saludo

    ResponderEliminar
  2. Estimado Adolfo, tu blog es excelente, ojalá me puedas ayudar con mi fórmula:

    =SI((Y(P15="Ascendente")),SI (15>O15>-5,"Verde",SI(-10<O15<-5,"Amarillo",SI(-10<O15,"Rojo",SI(15<015,"Fallo")))))

    Lo que pasa es que por default me elige verde, aunque cambie el valor de la celda O15.

    Muchas gracias, un cordial saludo

    ResponderEliminar
    Respuestas
    1. Hola Laura.
      Los condicionales dobles en Excel no se pueden poner como en matemáticas. La expresión 15>O15>-5 en matemáticas es perfectamente entendible, pero en Excel no se entiende. Tendrías que sustituirla por una expresión que usa el operador lógico Y, de la siguiente forma:
      Y(15>O15,O15>-5)
      Otro comentario. Normalmente cuando tenemos muchos SI dentro de otros SI nos quedan fórmulas muy largas y complicadas de seguir. En estos casos una buena alternativa es crear una pequeña tabla con la casuística y luego usar la función BUSCARV sobre esa tabla. De esta forma se entiende mejor y el manejo es más sencillo.
      Un saludo.

      Eliminar