Mostrando las entradas con la etiqueta Transponer. Mostrar todas las entradas
Mostrando las entradas con la etiqueta Transponer. Mostrar todas las entradas

lunes, agosto 15, 2016

De matriz a columna o fila con Vba (macros)

En el post anterior vimos como Power Query nos permite convertir una matriz de varias columnas a una única columna de valores.

La nota surgió de una consulta sobre como convertir una matriz de datos (un rango de Excel con varias filas y varias columnas) a un rango de una única columna. Mi lector pedía que mostrara como hacerlo con macros. En su lugar mostré como hacerlo con Power Query y ésto por dos motivos:
  1. porque estoy maravillado con las posibilidades del Power Query;
  2. porque es mucho más fácil aprender a usar el Power Query que aprender Vba.
Sin embargo mi lector insistía en que la tarea debía hacerse con macros. A continuación publico el código para hacer la tarea. Al activar la macro debemos seleccionar el rango de la tabla a convertir; luego la primer celda de la columna o fila y finalmente elegir si queremos transformar la tabla en fila o columna únicas.

Este video muestra el proceso




El cuaderno con los códigos (del Userform y del módulo de Vba) puede descargarse aquí.

El modelo consta de un Userform


con sus códigos

y la procedura en el módulo común

Sub table_to_column_or_row()
    Dim rngTable As Range, rngCell As Range
    Dim rngDest As Range
    Dim intIndexCount As Integer, iX As Integer
    Dim valArray()
    Dim intOption As Integer
   
   
    On Error GoTo errCancel
    Set rngTable = Application.InputBox("Seleccione el rango de la tabla", "De tabla a columna", Type:=8)
    Set rngDest = Application.InputBox("Seleccione celda de destino", "Destino", Type:=8)
    On Error GoTo 0
   
    intIndexCount = rngTable.Count
   
    ReDim valArray(intIndexCount)
   
    For iX = 1 To intIndexCount
        valArray(iX - 1) = rngTable(iX)
    Next iX
   
   
   
    ufOptions.Show
   
    With ufOptions
        If .opbColumna Then intOption = 1
        If .opbFila Then intOption = 2
    End With
    Unload ufOptions
   
   
    Application.ScreenUpdating = False
   
    Select Case intOption
        Case Is = 1
            Set rngDest = rngDest.Resize(UBound(valArray), 1)
            rngDest = Application.Transpose(valArray)
        Case Is = 2
            Set rngDest = rngDest.Resize(1, UBound(valArray))
            rngDest = valArray
    End Select
      
    Application.ScreenUpdating = True
   
    Exit Sub
   
errCancel:
Exit Sub
       
End Sub


lunes, febrero 03, 2014

Transponer rangos que contienen referencias

Excel facilita la tarea de transponer rangos, es decir, invertir el orden filas/columnas. Todo lo que hay que hacer es seleccionar el rango, copiarlo (Ctrl+C) y pegarlo en otra área usando la opción Transponer de Pegado Especial.
El proceso funciona sin problemas si el rango a transponer contiene sólo valores




y por lo general también si el rango contiene fórmulas. En el ejemplo de arriba, la celda C3 (Enero/Norte) contiene la fórmula

=SUMAPRODUCTO((Mes=$B3)*(Sucursal=C$2)*Ventas)

donde "Mes", "Sucursal" y "Ventas" son nombres definidos que se refieren a los rangos que contienen los datos.

En la tabla transpuesta las ventas de Enero de la zona norte se encuentran en la celda C13 que contiene la fórmulas

=SUMAPRODUCTO((Mes=C$12)*(Sucursal=$B13)*Ventas)



Pero hay casos en que Excel puede dar resultados incorrectos como en este ejemplo. Los datos en el rango D2:E6 se refieren a los datos del rango B2:B11


El valor en D2 está ligado a B2; E2 a B7; D3 a B3; etc.

Dado que las referencias son relativas, al transponer el rango Excel "reordena" las referencias



Podemos transponer copiando sólo lo valores (Pegado Especial-Valores-Transponer), pero si queremos conservar las referencias/fórmulas podemos aplicar esta técnica que publicó Chandoo en su blog:


  1. Seleccionamos el rango a transponer (D2:E6 en nuestro ejemplo);
  2. usamos Buscar y Reemplazar (Ctrl+L) para reemplazar los "=" por "#"
  3. copiamos (Ctrl+C) y transponemos el rango modificado;
  4. en el rango transpuesto reemplazamos los "#" por "="
Al reemplazar los "=" por "#", las referencias se convierten en constantes; al reemplazar los "#" por "=" en el rango transpuesto, convertirmos las constantes nuevamente en referencias. Este video muestra el proceso





Este método es útil sólo si los símbolos "=" aparecen al principio de la referencia/fórmula.