jueves, 18 de enero de 2018

Doble búsqueda con cálculo condicionado

Resolveremos hoy un problema interesante de búsqueda múltiple con el matiz que se condiciona el cálculo a devolver.
Veamos el planteamiento para entender algo mejor el problema:



La idea es obtener, según la matriz de información en B3:E6, y para cada registro de la tabla principal en H3:J18 la fecha de vencimiento correspondiente a las condiciones de B3:E6.
Por ejemplo, el primer registro corresponde a
-la Categoría: cat1
-el Concepto: conc1
al que se le aplicará un vencimiento de 15 días;
en nuestro ejemplo habrá que sumar a 28/06/2017 + 15 días con resultado: 13/07/2017.
En otros registros, se sumarán meses naturales o años completos...
Esto es, el cálculo variará según el tipo de intervalo (día, mes, año).


Esta doble búsqueda la gestionaremos con las funciones INDICE y COINCIDIR pero matricialmente ejecutadas... lo que nos permitirá recuperar del rango B3:E6 por un lado las uds y por otro el tipo (día, mes o año).

Veamos nuestras formulaciones, en K3 insertamos (recuerda validar presionando Ctrl+Mayusc+Enter):
=SI.ERROR(INDICE($E$3:$E$6;COINCIDIR($H3&$I3;$B$3:$B$6&$C$3:$C$6;0));"")
la clave del asunto es que concatenando elementos de Categoría + Concepto podremos recuperar el valor deseado...

Luego podremos copiar la fórmula al resto del rango: K4:K18.



De forma similar para el siguiente dato en L3 insertamos (validar con Ctrl+Mayusc+Enter):
=SI.ERROR(INDICE($D$3:$D$6;COINCIDIR($H3&$I3;$B$3:$B$6&$C$3:$C$6;0));0)

y copiamos al resto del rango L4:L18.


Con los valores recuperados de 'Tipo' y 'Uds' ya estamos en disposición de calcular el vencimiento de cada registro...
En M3:
=SI.ERROR(ELEGIR(COINCIDIR(K3;{"día";"mes";"año"};0);J3+L3;FECHA.MES(J3;L3);FECHA.MES(J3;12*L3));"")



Lo interesante de esta fórmula ELEGIR es que a partir del 'Tipo' recuperado con la matricial anterior,
nos permite seleccionar qué cálculo realizar:
ELEGIR(COINCIDIR(K3;{"día";"mes";"año"};0);J3+L3;FECHA.MES(J3;L3);FECHA.MES(J3;12*L3))
la función COINCIDIR sobre la constante matricial {"día";"mes";"año"} devuelve un número índice 1,2 ó 3, que tomaremos como indicador de posición para optar por un cálculo u otro.
Si la coincidencia del tipo es 1, i.e., el 'Tipo' es día, entonces calcularemos: J3+L3 (fecha + Uds)
Si la coincidencia del tipo es 2, i.e., el 'Tipo' es mes, entonces calcularemos: FECHA.MES(J3;L3) (sumamos x meses a la fecha)
Si la coincidencia del tipo es 3, i.e., el 'Tipo' es año, entonces calcularemos: FECHA.MES(J3;12*L3) (sumamos x años a la fecha)


Con la función SI.ERROR gestionamos en todos los casos el error en las búsquedas...

martes, 16 de enero de 2018

Pasar una matriz de varias columnas a un vector de una sola columna

Hace algunos días me llegó a través de un comentario una cuestión clásica en Excel:
Cómo pasar una matriz de varias columnas a un vector de una sola columna



Lo haremos de un par de formas.
Una primera con fórmulas.
En primer lugar hemos asignado un nombre definido al rango de celdas con valores:
datos =Hoja2!$A$2:$B$11

A partir de ese nombre definido, en las celda E2:E21, añadimos la siguiente fórmula:
=INDICE(datos;1+ENTERO((FILA(A1)-1)/COLUMNAS(datos));RESIDUO(FILA(A1)-1+COLUMNAS(datos);COLUMNAS(datos))+1)
algo larga, pero lo relevante es que con la fórmula:
1+ENTERO((FILA(A1)-1)/COLUMNAS(datos))
obtenemos la posición de las filas a recuperar del rango 'datos', aumentando el número de fila de dos en dos.

De forma similar con
RESIDUO(FILA(A1)-1+COLUMNAS(datos);COLUMNAS(datos))+1
obtenemos la posición de la columna, para nuestro ejemplo, 1, 2, 1, 2, 1, 2, etc...

Según se ve en la imagen siguiente:



Se observa como los algoritmos empleados devuelven el recorrido ordenado de cada elemento de nuestra matriz...

Otra forma, quizá mas simple, sea empleando una macro, así añadiremos dentro de un módulo estándar de nuestro proyecto de VB:

Sub ConvertirMatrizVector()
'pedimos al usuario selecciona la primera celda donde desplegar el vector destino
Set celdadestino = Application.InputBox("Celda Inicio Destino:", Type:=8)

Dim fd As Long
fd = 0
'recorremos fila por fila del rango seleccionado
For Each fila In Selection.Rows
    'copiamos la fila completa
    fila.Copy
    'y pegamos en el destino adecuado
    celdadestino.Offset(fd, 0).PasteSpecial Paste:=xlValues, Transpose:=True
    'incrementando la posición de fila destino...
    fd = fd + fila.Columns.Count
Next fila
End Sub



O también esta otra macro.

Sub ConvertirMatrizVector_v2()
'pedimos al usuario selecciona la primera celda donde desplegar el vector destino
Set celdadestino = Application.InputBox("Celda Inicio Destino:", Type:=8)

Dim fd As Long
fd = 0
'recorremos las filas del rango seleccionado
For f = 1 To Selection.Rows.Count
    'y recorremos las columnas del rango seleccionado
    For c = 1 To Selection.Columns.Count
        'llevamos el valor de cada celda del rango a la celda destino
        celdadestino.Offset(fd, 0).Value = Selection.Cells(f, c).Value
        fd = fd + 1
    Next c
Next f
End Sub



Consiguiendo en cualquiera de los tres casos el objetivo: convertir una matriz en un vector

jueves, 11 de enero de 2018

VBA: Limitar elementos de un ComboBox a la lista cargada

Se trata hoy de limitar qué elementos podemos insertar en un ComboBox dentro del contexto de formulario de usuario, para evitar incluir elementos fuera de la lista permitida.

Para mostrar estas propiedades ejercitaremos un ejemplo de búsqueda de datos desde un Userform.
Tenemos un listado de datos en A1:D19, con campos ID, Fecha, Concepto e Importe.
A partir del ID en un ComboBox rellenaremos tres TextBox.
Lo importante es que queremos controlar que el ComboBox asociado al ID se rellena solo con los datos habilitados.


Nuestros datos en la hoja de cálculo:

VBA: Limitar elementos de un ComboBox a la lista cargada



Por otro lado insertaremos un UserForm en nuestro proyecto, con
1- cuatro etiquetas - label,
2- tres TextBox:
txtFecha
txtConcepto
txtImporte
3- un CommandButton
cmdSalir
4- un ComboBox
cmbID

VBA: Limitar elementos de un ComboBox a la lista cargada



Lo importante será las propiedades del ComboBox a tocar para conseguir nuestro objetivo:
.MatchRequired = True
.Style = 2-fmStyleDropDownList
Modificando estas dos propiedades conjuntamente (recomendado) conseguimos la meta, y nos será imposible introducir un elemento fuera de los listados...

Estas propiedades del Combobox las podemos ajustar desde la ventana de propiedades del Control, o bien desde la programación, como se puede ver a continuación

En la ventana de código del UserForm incluimos:

Private Sub UserForm_Initialize()
'ajustamos propiedades para evitar introducir elementos fuera de la lista mostrada
Me.cmbID.MatchRequired = True
Me.cmbID.Style = fmStyleDropDownList
'cargamos los elementos del rango A2:A19
Me.cmbID.RowSource = "A2:A19"
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub cmbID_Change()
'limpia los campos informados
Me.txtFecha.Value = ""
Me.txtConcepto.Value = ""
Me.txtImporte.Value = ""

'gestiona la búsqueda del ID deseado
Dim fila As Long
fila = Range("A2:A19").Find(what:=Me.cmbID.Value).Row

'carga los TextBox con valores de la lista...
Me.txtFecha.Value = CDate(Cells(fila, "B").Value)
Me.txtConcepto.Value = CStr(Cells(fila, "C").Value)
Me.txtImporte.Value = Format(CDbl(Cells(fila, "D").Value), "#,##0")

End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub cmdSalir_Click()
'descarga y cierra el UserForm
Unload Me
End Sub



El resultado es el esperado... se nos hace imposible meter valores en el ComboBox distintos de los listados...