lunes, septiembre 08, 2014

Pasar datos en filas o columnas a una matriz

En un post del año 2008 mostré como pasar los datos de una matriz (tabla bi-dimensional) a una fila o columna. Inmediatamente surge la pregunta: ¿cómo hacemos para pasar los datos de una columna o fila a una matriz de tamaño determinado?

Consideremos este ejemplo:

Queremos tomar los datos en el rango B2:B16 y pasarlos a una matriz de 5 filas y tres columnas tal como vemos en el rango E6:G10.

Si bien podemos hacerlo con fórmulas, vamos a mostrar una solución con macros, solución más flexible y práctica (podemos guardar la macro en el libro Personal y usarla en cualquier cuaderno sin necesidad de recrear las fórmulas en cada oportunidad).

Este video muestra como funciona la macro



El código de la macro es el siguiente:

Sub make_matrix()
    Dim Arr_1()
    Dim lnumRows As Long, lnumCols As Long
    Dim rngDestRange As Range, rngCellDest As Range
    Dim i As Long, j As Long, x As Long
    Dim lCounter As Long
 
     
    If Selection.Count < 4 Then
        MsgBox "Debe seleccionar un rango de por lo menos cuatro celdas", vbExclamation
        Exit Sub
    End If
 
    lnumRows = Application.InputBox("Cuantas filas en la matriz?", "Filas", , , , , , 2)
    lnumCols = Application.InputBox("Cuantas columnas en la matriz?", "Columnas", , , , , , 2)
 
    ReDim Arr_1(1 To lnumCols, 1 To lnumRows)
 
    Set rngCellDest = Application.InputBox("Posicion de la primera celda de la matriz", "Copiar matriz", , , , , , 8)
    Set rngDestRange = rngCellDest.Range(Cells(1, 1), Cells(lnumRows, lnumCols))
 
    lCounter = 0
 
    For i = 1 To lnumCols
        For j = 1 To lnumRows
            Arr_1(i, j) = Selection.Item(lCounter + 1)
            lCounter = lCounter + 1
        Next j
    Next i
 
    rngDestRange.Value = WorksheetFunction.Transpose(Arr_1)
 
 
End Sub


Antes de aplicar la macro debemos seleccionar el rango de las celdas que queremos convertir en matriz. Este rango debe ser unidimensional, fila o columna.

Una vez seleccionado accionamos la macro. En primer lugar la macro comprueba que se haya elegido un rango con por lo menos cuatro celdas (el amable lector me disculpará si no explico la razón de esta condición).

El segundo paso es determinar las dimensiones de la matriz, cuántas filas y cuantas columnas. En nuestro ejemplo el rango contiene 15 celdas, por lo que hemos definido una matriz de 5 x 3. El número de elementos en la matriz equivale al múltiplo de las dimensiones. Así por ejemplo, si definimos en nuestro ejemplo una matriz de 4 x 2, sólo los primeros 8 datos serán pasados a la matriz.

El tercer paso es determinar la posición de la primer celda de la matriz.

Una vez que hemos definido todos los parámetros usamos la orden

ReDim Arr_1(1 To lnumCols, 1 To lnumRows)

para definir un array que contenga los datos que queremos que aparezcan en la hoja.

Luego usamos este loop para introducir los datos en el array

    For i = 1 To lnumCols
        For j = 1 To lnumRows
            Arr_1(i, j) = Selection.Item(lCounter + 1)
            lCounter = lCounter + 1
        Next j
    Next i

El último paso es pasar los datos a la hoja:

rngDestRange.Value = WorksheetFunction.Transpose(Arr_1)

Nótese que usamos Transpose par invertir los datos del array que pasamos a la matriz en la hoja.

El cuaderno con el ejemplo y la macro puede descargarse aquí.

7 comentarios:

  1. Sólo quería hacerte saber que me resultan muy interesantes y didácticos tus ejemplos. Siempre los recibo por mail y entro al blog cada tanto para ver las actualizaciones.
    No siempre comento, pero creo que te sirve saber que de este lado, muchos estamos esperando ese próximo post que nos saque de nuestra ignorancia! jajajajaj
    Gracias por tu dedicación y aporte a la comunidad Excelera!

    ResponderBorrar
  2. Gracias Raimundo, me alegra que el blog te resulte útil (¿qué lado es "este lado"?) :)
    Estás invitado a mandar sugerencias de temas relacionados con Excel que te interesen. Después de 627 posts se me están acabando las ideas :(

    ResponderBorrar
  3. Fantástica web que hago tiempo que consulto y voy adaptando sus explicaciones a lo que más exactamente necesito!!! no obstante, en esta macros de aqui soy incapaz de modificarla para que el paste no sea transpose, me explico, la matriz final deseo que sea por fila y no por columna, he intentado jugar con las i e j pero no. hasta que he visto que la clave esta en la ultima funcion, en el transpose, pero no encuentro el equivalnete a que me cambie la forma de pegar... no se si me explico.

    muchas gracias de nuevo!

    ResponderBorrar
  4. Hola, la clave no está en la función TRANSPOSE sino en el orden del loop; en lugar de

    For i = 1 To lnumCols
    For j = 1 To lnumRows
    Arr_1(i, j) = Selection.Item(lCounter + 1)
    lCounter = lCounter + 1
    Next j
    Next i

    invertimos el orden de esta manera

    For j = 1 To lnumRows
    For i = 1 To lnumCols
    Arr_1(i, j) = Selection.Item(lCounter + 1)
    Debug.Print Arr_1(i, j)
    lCounter = lCounter + 1
    Next i
    Next j

    ResponderBorrar
  5. Excelente. Justo lo que necesitaba !!!!!!!!!!!!!!!! muchísimas gracias. Sólo una consulta, estuve intentando que me hiciera exactamente lo mismo pero con varias columnas a la vez, pero la macro no funcionaba lógicamente al asignar sólo una posición en donde alojar la matriz. Sería posible una ayuda? Me explico, si p.e. tengo 3 columnas consecutivas (A,B y C) con 12 filas de datos (1 a 12) y mi el objetivo es hacer 3 matrices p.e. de 3x4, una debajo de la otra, es decir, de la columna A = una matriz de A1 a C4, de la columna B = una matriz de A5 a C8, de la columna C = una matriz de A9 a C12 y así sucesivamente si tuviera infinitas columnas de datos.
    Atentamente ygracias por su inestimable ayuda.
    jggarijo@hotmail.com

    ResponderBorrar
  6. Bien, podrías usar la macro una vez para cada matriz, obviamente. Si tuvieras infinitas columnas de datos, tampoco una macro te ayudaría :)

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.