viernes, agosto 26, 2016

La función UNIRCADENAS en versiones anteriores a Excel 365

Una de las novedades en Excel 365 (2016) es la función UNIRCADENAS(), como ya mencioné en esta nota. Esta función es muy útil cuando queremos unir los valores de distintas celdas en un único valor textual. En las versiones anteriores de Excel podíamos hacerlo usando el operador "&" (que en inglés se llama ampersand y en castellano "et.") o la función CONCATENAR().
En ambos casos, cuando queremos unir valores de varias celdas, se trata de una tarea tediosa, en particular cuando queremos usar un separador entre los textos.
UNIRCADENAS() permite definir el separador e ingresar un rango de celdas lo cual facilita enórmemente la tarea. El problema es que esta función está disponible sólo para los usuarios de Excel 365 (creo que hasta hoy Microsoft no ha actualizado la versión stand-alone de Excel 2016).
La solución para los usuarios de versiones anteriores es programar un función definida por el usuario (UDF) o una macro.
La ventaja de la función UDF es que se actualizará automáticamente con cada cambio en alguna de las celdas del rango; la desventaja es que puede afectar la velocidad de recálculo del cuaderno, en particular si usamos muchas de estas funciones.
La ventaja de la macro es que no afectará la velocidad de recálculo del cuaderno, pero tendremos que activarla cada vez que efectuemos un cambio en los valores del rango.

Publico aquí ambos códigos que sugiero guardar en el cuaderno PERSONAL de manera que puedan ser usados en cualquier cuaderno.

Función UDF Unir_Cadenas

Function Unir_Cadenas(varSep As Variant, rngVals As Range)
    Dim strTemp As String
    Dim rngCell As Range
  
    For Each rngCell In rngVals
        strTemp = strTemp & rngCell & varSep
    Next rngCell
  
    Unir_Cadenas = Mid(strTemp, 1, Len(strTemp) - 1)
  
End Function


Una vez guardado el código usamos el asistente de fórmulas para activar la función


Y definimos el separador y el rango de celdas que contiene los valores a unir


Si queremos un espacio como separador, pondremos " " (espacio encerrado entre dos comillas); si no queremos ningún separador usaremos "" (dos comillas).

Macro concatenatar_rango

Sub concatenatar_rango()
    Dim strTemp As String
    Dim rngVals As Range, rngCell As Range
    Dim varSep As Variant
    Dim rngDest As Range
  
    On Error GoTo errCancel
  
    Set rngVals = Application.InputBox("Seleccione el rango de celdas a unir", "Rango a unir", Type:=8)
    varSep = Application.InputBox("Entre el separador", "Separador", Type:=2)
    Set rngDest = Application.InputBox("Seleccione la celda de destino", "Destino", Type:=8)
  
    For Each rngCell In rngVals
        strTemp = strTemp & rngCell & varSep
    Next rngCell
  
    rngDest = Mid(strTemp, 1, Len(strTemp) - 1)
  
    Exit Sub
  
errCancel:
Exit Sub
  
End Sub


Al activar la macro debemos seleccionar el rango de celdas, el separador y finalmente la celda de destino, como puede verse en este video



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


viernes, agosto 05, 2016

De matriz a columna con Power Query

En el año 2008 publiqué una nota sobre como pasarlos datos de una matriz de varias columnas a una sola columna. La tarea la realizábamos con fórmulas que combinaban las funciones DESREF(), TRUNCAR(), RESIDUO() y COLUMNAS() entre otras. Como se puede intuir, bastante complicado. Agreguemos a esto los problemas de rendimiento que se presentan cuando tratamos de aplicarlas a matrices de cierto tamaño (recordemos que DESREF es volátil).

Un lector me consultaba sobre una macro para convertir una matriz de 24 columnas por 600 filas a una sola columna. La idea de usar una macro era obvia; aplicar fórmulas con DESREF a 14400 celdas de una hoja es una muy mala idea en términos de rendimiento.

Una macro puede dar una buena solución y una búsqueda en la Internet les permitirá encontrar varios códigos para hacerlo. Sin embargo, desde la aparición del Power Query (Excel 2010 en adelante), podemos hacerlo con facilidad y eficiencia sin necesidad de echar mano a las macros.


Suficiente cháchara; vamos a mostrar cómo hacerlo. Supongamos una matriz de 24 columnas por 600 filas que contiene valores numéricos y no tiene encabezamientos


El primer paso es cargar la matriz en la ventana del power query usando la opción “From Table”

asegurándonos de quitar la marca de la opción "My table has headers" (mi tabla tiene encabezados)


Una vez en la ventana del Power Query, agregamos una columna de índices (en el próximo paso se entenderá el objetivo)

Excel crea una columna de números consecutivos a partir del 1. La nueva columna será la última a la derecha (para la presentación en la nota he movido la columna a la izquierda, pero esta acción no es indispensable). Con esta columna seleccionada, usamos la opción "Unpivot other columns" en "Transform"


El reusltado es el siguiente


Ahora removemos las columnas "Index" y "Attribute" 


y cargamos la columna restante a una hoja del cuaderno (Close and Load)


¡Misión cumplida

Este video muestra el proceso, que se realiza en menos de un minuto!