Tabla cartesiana en Excel

Convertir una tabla en una tabla cartesiana

Se trata de una función que crea una tabla de referencias cruzadas. El resultado final es, en apariencia, como una tabla dinámica. Todo el proceso está automatizado utilizando el Visual Basic de las macros de Excel.

Mediante este código se podría copiar el resultado de una consulta de agrupación —que al menos tuviese dos campos de agrupación— y cruzar la agrupación por filas y columnas.

Formato de entrada

La entrada es una tabla colocada en una hoja de cálculo cuyas columnas están dispuestas en las siguientes posiciones:

  • Las primeras columnas o campos servirán para agrupar por columnas.
  • Las siguientes columnas agruparán por filas.
  • Las últimas columnas contienen los datos.

Análisis visual

  1. Tabla de origen — Los datos de partida
  2. Filas de la tabla destino — Proceso paso a paso sobre las filas
  3. Columnas de la tabla destino — Proceso paso a paso sobre las columnas
  4. Tabla destino — Proceso para colocar los datos
Secuencia animada del proceso desde el inicio hasta el final

Función principal: CrearTablaCartesiana

Sintaxis:

Public Function CrearTablaCartesiana( _
    ByVal origen As Range, ByVal destino As Range, _
    ByVal colsColumnas As Integer, _
    ByVal colsFilas As Integer, _
    ByVal colsDatos As Integer, _
        Optional ByVal opcionCombinar As Boolean = True, _
        Optional ByVal opcionTotal As Boolean = True, _
        Optional ByVal opcionFormatear As Boolean = True, _
        Optional ByVal opcionRatios As Boolean = True, _
        Optional ByVal opcionAjustar As Boolean = True, _
        Optional ByVal opcionFijar As Boolean = True _
) As Boolean

Parámetros de la función

Parámetro Descripción
origen Una celda dentro de la tabla con los datos
destino La celda superior izquierda de la tabla resultado
colsColumnas Nº de columnas de la tabla origen destinadas a ser encabezados de columnas de la tabla destino
colsFilas Nº de columnas de la tabla origen destinadas a ser encabezados de filas en la tabla destino
colsDatos Nº de columnas de la tabla origen destinadas a ser datos en la tabla destino
opcionCombinar Combina las celdas iguales de los encabezados de filas o columnas
opcionTotal Añade una fila inferior con el total de las columnas de datos
opcionFormatear Colorea y aplica formato a las tablas
opcionAjustar Ajusta automáticamente el ancho de las columnas
opcionFijar Fija los titulos de la hoja de cálculo

Ejemplos de uso

Código Ejemplo
Call CrearTablaCartesiana( _
        ActiveSheet.Range("B2"), _
        ActiveSheet.Range("B26"), _
        2, 2, 2)
Pantallazo 1-1
Call CrearTablaCartesiana( _
        ActiveSheet.Range("B2"), _
        ActiveSheet.Range("B26"), _
        1, 3, 2)
Pantallazo 1-2
Call CrearTablaCartesiana( _
        ActiveSheet.Range("B2"), _
        ActiveSheet.Range("K2"), _
        1, 3, 4)
Pantallazo 2-1
Call CrearTablaCartesiana( _
        ActiveSheet.Range("B2"), _
        ActiveSheet.Range("K2"), _
        2, 2, 4)
Pantallazo 2-2

Fórmulas

Se pueden agregar algunas fórmulas escritas de una forma simplificada que luego se traducen a fórmulas auténticas de la hoja de cálculo. Ejemplos:

'=Precio*Cantidad Significa que multiplique la celda de la columna Precio por la celda de la columna Cantidad que se encuentren en la misma fila que la fórmula.
'=Importe/$Importe Significa que divida la celda de la columna Importe en la misma fila que la fórmula por el total de Importe situado en la fila de totales.

Las fórmulas se han de introducir cómo si fuesen texto añadiendo un apóstrofe al principio. Sólo se pueden introducir este tipo de fórmulas en las columnas correspondientes a datos y no a agrupaciones.

Código fuente

Tabla de algunos índices de colores de Excel

moduloColor

  • Constantes con los índices de colores en Excel
    • COLOR_NEGRO, COLOR_BLANCO, etc.
  • Funciones:
    • ColorearFilas — Colorea cebrando (una fila sí y otra no) las filas desde y hasta las columnas indicadas. Ignora la primera fila porque se la considera de títulos.
    • Colorear — Colorea el fondo de los rangos indicados con los colores indicados. 
    • CebrarFilas — Colorea las filas pares del rango indicado.
    • Cuadricular — Bordea las celdas del rango indicado.
    • ColorearColumnas — Colorea de N en N columnas con la lista de colores indicados.
    • ColorColumnas, ColorFilas, ColorDatos, ColorTitulos, ColorFormula — Color definido para las filas impares y pares de la tabla destino.

moduloCartesiano

  • Público
    • CrearTablaCartesiana — Función principal 
      TC TF TD
      c1 f1 d1
      c1 f2 d2
      c2 f1 d3
      c2 f2 d4
      TC c1 c2
      TF TD TD
      f1 d1 d3
      f2 d2 d4
  • Privado
    • CopiarDatosCartesianos — Copia en las posiciones correctas los datos de la tabla origen en la tabla destino
    • Formular — Calcula las fórmulas pendientes. Convierte en fórmulas reales las fórmulas simplificadas.
    • Copiar — Copia el rango origen en el destino.
    • OrdenarFilas — Ordena las filas tomando como criterio de ordenación las columnas de la primera hasta la última.
      C 1
      B 3
      A 2
      B 1
      A 2
      B 1
      B 3
      C 1
    • EliminarFilasDuplicadasConsecutivas — Borra las filas consecutivos cuyos contenidos sean los mismos.
      A
      A
      A
      B
      B
      A
      -
      -
      B
      -
    • CompactarFilas — Sube las filas inferiores aprovechando los huecos de las filas vacías.
      A
      -
      -
      B
      -
      A
      B

    • DuplicarFilas — Duplica cada fila el número de veces indicado
      A
      B
      C
      2→ A
      A
      B
      B
      C
      C
          A
      B
      3→ A
      A
      A
      B
      B
      B
    • FormatearGrupos — Combina las celdas consecutivas con el mismo valor que se encuentren dentro de la misma columna o bien dentro de la misma fila, según esté indicado en el parámetro tipo.
      A A B B B
      V W X Y Z
       ---A--- --B--
      V W X Y Z
    • BordearGrupos — Borda las columnas de arriba a abajo o las filas de izquierda a derecha según los datos de la primera fila o de la primera columna.
      ---A--- --B--
      V W X Y Z
      |    A    |   B  |
      |V W X | Y Z |
    • Transponer — Copia el rango cambiando filas por columnas y viceversa.
      A
      B
      C
       A B C
    • Borrar — Elimina el contenido de las celdas.
    • Rellenar — Copia N veces el rango origen en el rango destino.
      A B A B A B A B A B ...
    • RellenarFormato — Copia el formato de origen en el destino
    • Totalizar — Crea una fila con el total de cada columna
    • RangosIguales — Indica si los contenidos de los dos rangos coinciden

Descarga

Todo el código y ejemplos de uso

Para ver el código fuente hay que ir al Editor de Visual Basic

Módulos VBA

Comentarios

  1. 1 Francisco 2009-04-16 Hace 9 años
    Lo he actualizado a la versión 1.1
    1) He corregido la función CompararRangos para que le de igual si se trata de números o de textos.
    2) Y que no de error si la función CompactarFilas no tiene nada que hacer.
  2. 2 Francisco 2009-04-22 Hace 9 años
    Actualizado a la versión 1.2
    Corrección en la función Rellenar para que no de error si no hay nada que rellenar.
  3. 3 Francisco 2010-06-03 Hace 7 años
    Actualizado a la versión 2010-06-03.
    Corrección de varios errores cuando había pocos o muchos datos.
Proinf.net, ©2003-2017 ci 3.1.6 (CC) Esta obra está bajo una licencia de Creative Commons Este software está sujeto a la CC-GNU GPL