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í.

Generador de facturas con base de datos

En enero del 2008, hace más de seis años, publique el post "Numerador automático para facturas o recibos en Excel". Entre los distintos problemas del modelo propuesto en la nota, uno de los más mencionados es cómo producir copias de las facturas emitidas.

Para poder producir informes, analizar la ventas o producir copias no necesitamos guardar las facturas sino los datos de las facturas.

El modelo que presento en esta nota cuenta con una plantilla genérica para producir las facturas (o sus copias) y una base de datos (en una hoja) donde se van almacenando los datos de las facturas producidas.

A partir de esta base de datos podemos no sólo producir copias sino también analizar las ventas desde todo tipo de ángulo o combinaciones de ángulos: por cliente, por período, por producto, comparar clientes, comprar productos, etc., usando las distintas herramientas que Excel pone a nuestra disposición como, por ejemplo, tablas dinámicas.

En resumen:

El modelo Generador de facturas de JLD-Excel es una pequeña aplicación desarrollada enteramente en Excel que permite generar facturas, imprimirlas y guardar sus datos.

Con el modelo Factura con base de datos se puede:

·        Producir facturas e imprimirlas.

·        Guardar los datos en una base datos (en una hoja de Excel).

·        Producir copias de las facturas previamente guardadas.

·        Producir informes en base a los datos guardados en la base de datos.

Este video demuestra el uso del modelo

El costo del archivo es de 20 Euros (o su equivalente en otras monedas). Para realizar la compra debe apretar el carrito de compra



Al apretar el icono será llevado a esta página

Una vez efectuada la compra (apretar el botón "Checkout with PayPal), recibirá un mail con el enlace para descargar el archivo. Por favor, tomar en cuenta que el mail puede demorar hasta 24 horas. En caso de no recibir el enlace dentro de las 24 horas, revisar el spam o ponerse en contacto conmigo directamente.

La descarga de la guía del modelo es gratuita.

El cuaderno y el proyecto Vba no están protegidos con contraseñas y el usuario es libre de modificar y emplear partes del modelo, hojas y códigos.

El modelo no requiere instalación de ningún tipo; sencillamente se guarda como todo archivo Excel. 

Funciones que dan #VALOR en referencias a cuadernos cerrados

Cada tanto recibo consultas en relación a fórmulas que dan el resultado #VALOR cuando contienen referencias a cuadernos cerrados.
Lo que intriga a estos usuarios de Excel es que este comportamiento no se da con todas las funciones. Ciertas funciones, como BUSCARV funcionan correctamente también si el cuaderno remoto está cerrado; otras, como CONTAR.SI dan #VALOR si el cuaderno remoto está cerrado, error que se corrige al abrir el cuaderno.

Veamos este ejemplo: tenemos dos cuadernos con las ventas anuales de dos sucursales resumidas por mes, Sucursal 1.xlsx y Sucursal 2.xlsx. En un tercer cuaderno queremos crear un reporte que muestre el total de ventas de cada sucursal  y el número de meses en que las ventas fueron superiores a los 200,000

Como puede apreciarse, la fórmula en la celda C3 contiene una referencia explícita al cuaderno de donde extraemos los datos

=CONTAR.SI('[Sucursal 1.xlsx]Hoja1'!$B$2:$B$13,">200000")

Al cerrar los cuadernos Sucursal 1.xlsx y Sucursal 2.xlsx la referencia incluye la dirección completa del archivo remoto.

Ahora cerramos el cuaderno con el reporte. Al volver a abrirlo veremos:

Como vemos, la función SUMA sigue mostrando los resultados, pero la función CONTAR.SI no puede resolver la referencia y da el resultado de error #¡VALOR!

Hay varios remedios para este problema. En el caso específico de la función CONTAR.SI podemos usar en su lugar una combinación de SUMAR y SI de esta manera:

=SUMA(SI('[Sucursal 1.xlsx]Hoja1'!$B$2:$B$13>200000,1,0))

Esta fórmula es matricial y debe ser introducida en la celda apretando simultáneamente Ctrl-Mayúsculas-Enter

También podemos usar la función SUMAPRODUCTO, que no debe ser introducida matricialmente, de esta manera

=SUMAPRODUCTO(--('[Sucursal 1.xlsx]Hoja1'!$B$2:$B$13>200000))

Nótese el doble símbolo "--"inmediatamente después del primer paréntesis. Su función es forzar a los valores VERDADERO y FALSO creados por la función SUMAPRODUCTO a tomar el valor 1 y 0 respectivamente.

Otras funciones que dan #¡VALOR! cuando se refieren a cuadernos cerrados son SUMAR.SI, SUMAR.SI.CONJUNTO, CONTAR.SI.CONJUNTO, CONTAR.BLANCO, PROMEDIO.SI, INDIRECTO (tema que trate en esta nota del año 2006), DESREF y varias de las funciones base de datos como BDCONTARA y BDPROMEDIO).