ORDENAR ELEMENTOS DE UN LISTBOX UTILIZANDO OBJETO ARRAYLIST

Hola a todos!:

En esta web hay varios post dedicados a los listbox, tanto en formularios como objetos en la hoja. En el post de hoy me gustaría mostrar un código en el que vamos a ver un método para cargar los datos en nuestro listbox y también para ordenarlos.

Imaginad los siguientes datos:

ORDENAR ELEMENTOS DE UN LISTBOX UTILIZANDO OBJETO ARRAYLIST

Por un lado tenemos un listado de nombres y apellidos y por otro de números. Nuestro objetivo es pasar al listbox los datos seleccionados y ordenarlos.

Veamos el código que os propongo:

Sub ORDENAR_LIST()
'Definimos variables
Dim MiMatriz As Object, Celda As Variant, nItem As Variant
'Vaciamos listbox
Call BORRAR
With Sheets("ORDENAR")
'Creamos objeto ArrayList
Set MiMatriz = CreateObject("System.Collections.ArrayList")
'Pasamos los datos seleccionados al ArrayList
'Si la celda está vacía, no la tenemos en cuenta
'Formateamos el contenido de cada celda como texto
For Each Celda In Selection
If Not IsEmpty(Celda) Then MiMatriz.Add CStr((Celda))
Next Celda
'Ordenamos
MiMatriz.Sort
'Pasamos la información al listbox
For Each nItem In MiMatriz
.ListBox1.AddItem (nItem)
Next nItem
End With
End Sub

Sub BORRAR()
With Sheets("ORDENAR")
'Limpiamos combo y listbox
.ListBox1.Clear
End With
End Sub

Como podéis observar, vamos a trabajar con la selección como un rango de datos. Para ello tendremos que crear el objeto arraylist que nos permitirá ordenar la información de menor a mayor con el método .Sort.

Básicamente, con una instrucción For – Each vamos pasando cada elemento de la selección a nuestro arraylist (en este ejemplo no tendremos en cuenta las celdas vacías) y además damos formato texto a cada valor de las celdas seleccionadas (CStr) evitando así errores de tipos y también en el método Sort a la hora de comparar los datos para la ordenación.

Una vez pasados los datos, ordenamos. Si quisiéramos invertir el orden, simplemente tendríamos que incluir una nueva línea debajo del .Sort:

MiMatriz.Reverse

Una vez que lo tenemos, solo tenemos que pasar los datos al listbox mediante otro For-Each.

El resultado es para los nombres:

ORDENAR ELEMENTOS DE UN LISTBOX UTILIZANDO OBJETO ARRAYLIST_1

Y para los números:

ORDENAR ELEMENTOS DE UN LISTBOX UTILIZANDO OBJETO ARRAYLIST_2

Como podéis comprobar, el método funciona perfectamente para ambos casos.

Espero que os haya resultado de interés!.

Descarga el archivo de ejemplo pulsando en: ORDENAR ELEMENTOS DE UN LISTBOX UTILIZANDO OBJETO ARRAYLIST

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

Mediante la suscripción al blog, la realización comentarios o el uso del formulario de contacto estás dando tu consentimiento expreso al tratamiento de los datos personales proporcionados según lo dispuesto en la ley vigente (LOPD). Tienes más información al respecto en esta página del blog: Política de Privacidad y Cookies

Anuncios

ORDENAR VALORES UTILIZANDO FÓRMULAS

Hola a todos:

Estos días he recibido multitud de consultas y hoy me gustaría escribir un post muy sencillo sobre la posibilidad de ordenar valores utilizando únicamente fórmulas.

En la consulta inicial me preguntan cómo se pueden ordenar una serie de valores siempre numéricos tanto de menor a mayor como a la inversa, de mayor a menor.

Bien, esto lo podemos hacer con una sencilla fórmula, veamos los datos:

ORDENAR VALORES UTILIZANDO FORMULAS

En este caso es una relación de personas con una cifra que ha alcanzado (de lo que sea, nos vale como ejemplo).

La cuestión es ahora cómo ordenar esos números sin utilizar el filtro o el comando de ordenar de la cinta de opciones.

Pues bien, podemos utilizar la siguiente función:

=K.ESIMO.MAYOR($B$2:B10;FILAS($B$2:B2))

Con  K.esimo.Mayor vamos a obtener el número mayor de un rango, en este caso, al mover la fórmula nos va a ir mostrando el mayor según el rango seleccionado.

Y el resultado será el siguiente:

ORDENAR VALORES UTILIZANDO FORMULAS_1

Como podéis ver, conseguir ordenar el rango de Mayor a Menor. Si necesitamos invertirlo y hacerlo de Menor a Mayor, utilizaremos K.esimo.Menor.

Pero el lector me solicitó si también podía conseguir que se mostrase en otra columna los nombres relacionados con los números ordenados. Y en efecto, sí podemos obtenerlos, y lo vamos a hacer con la siguiente fórmula:

=INDIRECTO(DIRECCION(COINCIDIR(K.ESIMO.MAYOR($B$2:B10;FILAS($B$2:B2));$B$2:B10;0)+1;1))

Dado que con la fórmula inicial podemos ordenar los números en relación a la lista inicial, con la función Coincidir podemos obtener la fila en la que se encuentra nuestro número antes de ordenarlo, y  si sabemos la fila, con la función Dirección podemos obtener la celda que nos interesa de la columna A para obtener el nombre.

Así:

ORDENAR VALORES UTILIZANDO FORMULAS_3

He indicado con flechas en rojo la dirección con la que obtenemos las celdas de referencia que nos interesa.

Ahora, para obtener el nombre, utilizaremos la función indirecto:

ORDENAR VALORES UTILIZANDO FORMULAS_4

Y así, finalmente obtenemos toda la información que necesitamos.

Bien!!

Pero a los pocos días el mismo lector me solicitó si de alguna manera podría incluir rangos dinámicos en ambas fórmulas para no tener que ir actualizando los rangos a mano cada vez que el listado se fuese incrementando.

Pues eso también se puede realizar, la manera es incluyendo la función Desref en las dos fórmulas, y lo haremos sustituyendo en ambas fórmulas la parte que hace referencia a los rangos por:

DESREF($B$2; 0; 0; CONTARA(B:B))

Y así ya podemos obtener la misma información, pero con rangos dinámicos:

Para ordenar:

=K.ESIMO.MAYOR(DESREF($B$2; 0; 0; CONTARA(B:B));FILAS($B$2:B2))

Para obtener los nombres:

=INDIRECTO(DIRECCION(COINCIDIR(K.ESIMO.MAYOR(DESREF($B$2; 0; 0; CONTARA($B:$B));FILAS($B$2:B2));DESREF($B$2; 0; 0; CONTARA($B:$B));0)+1;1);1)

En resumen:

ORDENAR VALORES UTILIZANDO FORMULAS_5

Y eso es todo!. Como podéis observar, lo hemos conseguido.

Es importante comentar que está fórmula es solo válida para datos numéricos y en los que no existen duplicados. Si contamos con duplicados tendríamos que trabajar con una columna auxiliar en la que introducir algún elemento para “desempatar” esos datos, por ejemplo añadiendo una cifra muy pequeña a los números originales.

Descarga el archivo de ejemplo pulsando en: ORDENAR VALORES UTILIZANDO FÓRMULAS

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

Mediante la suscripción al blog, la realización comentarios o el uso del formulario de contacto estás dando tu consentimiento expreso al tratamiento de los datos personales proporcionados según lo dispuesto en la ley vigente (LOPD). Tienes más información al respecto en esta página del blog: Política de Privacidad y Cookies

MODIFICAR BASE DE DATOS DESDE UN LISTBOX CON UN FILTRO APLICADO

Hola a todos:

En el post de hoy trataré un tema muy específico de los listbox (o cuadros de lista) en los formularios. Se trata de cómo podemos seleccionar desde un listbox CON UN FILTRO APLICADO la información relacionada en un base de datos.

Para comprender el concepto, es necesario explicarlo con un ejemplo:

MODIFICAR BASE DE DATOS DESDE UN LISTBOX CON UN FILTRO APLICADO

En la imagen podemos apreciar como el listbox está filtrado por varios criterios y como en la hoja hemos capturado la fila correspondiente al registro seleccionado en el listbox.

Para poder conseguir esto, debemos siempre crear en nuestra base de datos un indice que vaya desde el 1 hasta el final indicando el número en cada celda: 1, 2, 3 …hasta el final del rango con datos. En mi ejemplo es la primera columna y la nombro como ÍNDICE.

Esto lo automatizaremos cada vez que inicialicemos el formulario que carga los datos en el listbox, así:

Private Sub UserForm_Initialize()
Dim i As Long, fin As Long, Mirango() As Integer

'Borramos ID's e inicializamos de nuevo.

'Los ID deben ser consecutivos a partir de 1

fin = Application.CountA(Sheets("BBDD").Range("B:B"))
Sheets("BBDD").Range("A2:A" & fin).Select
Selection.Clear
ReDim Mirango(1 To fin)
For i = 1 To fin:
Mirango(i) = i
Next i
Worksheets("BBDD").Range("A2:A" & UBound(Mirango)).Value = _
Application.WorksheetFunction.Transpose(Mirango)
Worksheets("BBDD").Range("A1").Select
'Indicamos el número de columnas que tendrá el listbox

Me.ListBox1.ColumnCount = 7

'Definimos tamaño de los espacios

Me.ListBox1.ColumnWidths = "30pt;150pt;150pt;50pt;50pt;60pt"

'Cargamos listbox

Me.ListBox1.RowSource = ("A2:G") & Worksheets("BBDD").Range("A" & Rows.Count).End(xlUp).Row

End Sub

En rojo está el código de realiza esto, primero limpia datos de la primera columna excepto encabezados y luego a través de un array iniciamos la numeración del 1 hasta el final. El último paso es transponer los datos en la columna A.

Es importante tener en cuenta que este paso lo hará automáticamente cada vez que mostremos el formulario.

Luego, en el evento ListBox1_Click(), solo tenemos que pasar este código para cada vez que seleccionemos una línea en nuestro cuadro de lista, está se seleccione también en la hoja:

Private Sub ListBox1_Click()
Dim i As Long
Dim dato As Integer
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) Then dato = ListBox1.List(i)
Next i
Sheets("BBDD").Cells(dato + 1, 1).Activate
End Sub

Y os estaréis preguntando porqué hacemos todo esto, pues es sencillo, es fundamental por si queremos modificar los datos a través del listbox. De esta forma podremos modificar lo que hayamos seleccionado, aunque se encuentre filtrado, en nuestra base de datos:

MODIFICAR BASE DE DATOS DESDE UN LISTBOX CON UN FILTRO APLICADO_1

He aprovechado la misma base de datos del post: REALIZAR BÚSQUEDAS DEPENDIENTES EN UN LISTBOX y también parte de la programación para poder ilustrar cómo podemos resolver la cuestión presentada.

Simplemente comento dos líneas de código que he agregado para que el formulario de modificación funcione correctamente:

Para cargar los datos seleccionado en el listbox y en la hoja:

Private Sub UserForm_Initialize()
For j = 1 To 6
Me.Controls("TextBox" & j).Value = ActiveCell.Offset(0, j)
Next j
End Sub

Y para modificar:

Private Sub CommandButton1_Click()
'Definimos variables
Dim i As Long, j As Long, fin As Long, Mirango() As Integer
'Pasamos datos modificados a la hoja
For j = 1 To 6
ActiveCell.Offset(0, j).Value = Me.Controls("Textbox" & j).Value
Next j
'Marcamos de nuevo elemento a modificar
For i = 0 To FORMULARIO.ListBox1.ListCount - 1
If FORMULARIO.ListBox1.Selected(i) Then dato = FORMULARIO.ListBox1.List(i)
Next i
Sheets("BBDD").Cells(dato + 1, 1).Activate
'Actualizamos listbox
FORMULARIO.ListBox1.RowSource = ("A2:G") & Worksheets("BBDD").Range("A" & Rows.Count).End(xlUp).Row
Unload Me
End Sub

En este ejemplo solo he implementado un formulario para realizar modificaciones, cualquier otra mejora como realizar un alta o una baja o que los cambios se guarden automáticamente os lo dejo como ejercicio para practicar : )

Y eso es todo, espero que este método os haya resultado de interés : )

Descarga el archivo de ejemplo pulsando en: MODIFICAR BASE DE DATOS DESDE UN LISTBOX CON UN FILTRO APLICADO

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

Mediante la suscripción al blog, la realización comentarios o el uso del formulario de contacto estás dando tu consentimiento expreso al tratamiento de los datos personales proporcionados según lo dispuesto en la ley vigente (LOPD). Tienes más información al respecto en esta página del blog: Política de Privacidad y Cookies

AÑADIR AÑOS, MESES Y DÍAS A UNA FECHA EN VBA

Hola a todos:

En el post de hoy veremos un método para añadir años, meses y días a una fecha. Esto normalmente en nuestra hoja de excel lo hacemos con la función FECHA() combinada con las funciones AÑO, MES Y DIA y sumando a cada función el número que se quiere aumentar.

En VBA tenemos una función específica para realizar esto, se trata de DATE.ADD con una sintaxis muy sencilla y que indico a continuación:

DateAdd(“Intervalo de tiempo”, Número a sumar, “fecha”).

Estas son las expresiones que podemos especificar en el campo “Intervalo”:

yyyy Año
q Trimestre
m Mes
y Día del año
d Día
w Día de la semana
ww Semana
h Hora
n Minuto
s Segundo

Sin embargo, esto solo funciona para un campo cada vez que utilizamos la función, es decir, no podemos añadir en la misma función años, meses y días.

Pero en VBA tenemos una solución muy sencilla, veamos el ejemplo:

AÑADIR AÑOS, MESES Y DIAS A UNA FECHA EN VBA

Para poder obtener el resultado que esperamos, utilizaremos la siguiente rutina:

Sub AÑADIR_FECHA()
'Declaramos variables
Dim i As Long, fin As Long
Dim años As Date, meses As Date, dias As Date
With Sheets("Hoja1")
'Borramos información de la columna E
fin = Application.CountA(.Range("A:A"))
If fin > 1 Then .Range("E2:E" & fin).ClearContents
'añadimos años, luego meses y finalmente días
For i = 2 To fin
años = DateAdd("YYYY", .Cells(i, 2), .Cells(i, 1))
meses = DateAdd("M", .Cells(i, 3), años)
dias = DateAdd("D", .Cells(i, 4), meses)
'Mostramos la nueva fecha
.Cells(i, 5) = dias
Next i
End With
End Sub

Y así obtenemos esto:

AÑADIR AÑOS, MESES Y DIAS A UNA FECHA EN VBA_1

Como podéis observar, a través de un for vamos añadiendo, primero los años, luego los meses y finalmente los días.

Y eso es todo por hoy, espero que os resulte de interés a la hora de realizar cálculos actuariales, financieros o de otro tipo : )

Descarga el archivo de ejemplo pulsando en: AÑADIR AÑOS, MESES Y DÍAS A UNA FECHA EN VBA

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

Mediante la suscripción al blog, la realización comentarios o el uso del formulario de contacto estás dando tu consentimiento expreso al tratamiento de los datos personales proporcionados según lo dispuesto en la ley vigente (LOPD). Tienes más información al respecto en esta página del blog: Política de Privacidad y Cookies

SUMAS ACUMULADAS POR RANGO SEGÚN CRITERIOS

Hola a todos:

Hoy vamos a seguir con las fórmulas, en concreto con el mundo de las sumas acumuladas y la problemática que surge en determinados casos.

Pongamos un ejemplo visual del problema:

SUMAS ACUMULADAS POR RANGO SEGÚN CRITERIOS

Como podéis observar se trata de obtener el sumatorio de los importes en cada rango en función de si en la columna C hay datos (en este caso, fechas).

Esta tarea es posible que se pueda resolver con una fórmula matricial (tengo pendiente estudiarlo), sin embargo, lo podemos resolver con una combinación de varias fórmulas:

En concreto la fórmula que os propongo es la siguiente:

=SI(FILA(B2)>2;SI.ERROR(SI(C2<>"";SUMA($A$2:INDIRECTO("A"&FILA(B2)));"")-SUMA($B$2:INDIRECTO("B"&(FILA(C2)-1)));"");SI(C2<>"";SUMA($A$2:INDIRECTO("A"&FILA(B2)));""))

Es importante en esta fórmula tener presente el número de fila en el que estamos situados en cada momento, dado que la segunda celda (teniendo en cuenta que tenemos encabezados), la fórmula será diferente a las demás, por ello indico un condicional al inicio de la fórmula dependiendo de si la fila en la que estamos es mayor o menor de 2.

La idea es primero capturar el acumulado desde la fila A2 hasta la fila en la que estamos:

=SUMA($A$2:INDIRECTO("A"&FILA(B2)))

Y luego restar lo que ya hemos acumulado

-SUMA($B$2:INDIRECTO("B"&(FILA(C2)-1)))

De esta forma conseguimos el resultado requerido.

SUMAS ACUMULADAS POR RANGO SEGÚN CRITERIOS_1

Y eso es todo : )

Espero que os sea de utilidad!.

Descarga el archivo de ejemplo pulsando en: SUMAS ACUMULADAS POR RANGO SEGÚN CRITERIOS

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

Mediante la suscripción al blog, la realización comentarios o el uso del formulario de contacto estás dando tu consentimiento expreso al tratamiento de los datos personales proporcionados según lo dispuesto en la ley vigente (LOPD). Tienes más información al respecto en esta página del blog: Política de Privacidad y Cookies

OBTENER EL VALOR DE UNA CELDA SEGÚN EL VALOR MÁXIMO DE UNA MATRIZ CON VARIAS CONDICIONES (PARTE II)

Hola a todos:

Hoy toca un poco de formulación : )

Hace un tiempo ya desarrollé una matricial para obtener el valor de una celda según el valor máximo de otra celda en una matriz. Esto lo podéis encontrar en este post: OBTENER VALOR DE UNA CELDA SEGÚN EL VALOR MÁXIMO DE UNA MATRIZ

En ese ejemplo utilizábamos tan solo un criterio, el valor del ID y en base a eso encontrar el valor de la celda. Pues bien, imaginad que debemos tener en cuenta dos ID´s más (ID2 e ID3).

Esta es la base de cálculo:

OBTENER EL VALOR DE UNA CELDA SEGUN EL VALOR MAXIMO DE UNA MATRIZ CON VARIAS CONDICIONES (PARTE II)

Un ejemplo de lo que necesitaríamos buscar sería, ID1=1321, ID2=1 e ID3=B

Si usamos un filtro veremos los parámetros, y al igual que en el post anterior, según los criterios de los ID tenemos que obtener el valor de Cantidad1 en función del valor máximo de Cantidad2, es decir 27

OBTENER EL VALOR DE UNA CELDA SEGUN EL VALOR MAXIMO DE UNA MATRIZ CON VARIAS CONDICIONES (PARTE II)_1

Pues bien, para conseguir este resultado, utilizaremos la siguiente fórmula matricial:

{=INDIRECTO(DIRECCION(COINCIDIR(MAX(SI(A:A=H1;SI(B:B=H2;SI(C:C=H3;E:E))));E:E;0);4))}

Para comprender las funciones utilizadas y su funcionamiento os remito al post anterior. En este ejercicio, hemos ampliado únicamente la parte de la función que hace referencia a los criterios y con la que obtendremos el valor del campo Cantidad2: 830

{=MAX(SI(A:A=H1;SI(B:B=H2;SI(C:C=H3;E:E))))}

El resto de la matricial detectará la posición de la celda anterior.

El ejercicio quedaría así:

OBTENER EL VALOR DE UNA CELDA SEGUN EL VALOR MAXIMO DE UNA MATRIZ CON VARIAS CONDICIONES (PARTE II)_2

Como habéis podido ver se trata “anidar” varios condicionales dentro de la función MAX y ejecutar matricialmente la función.

Recordad que para introducir las fórmulas matriciales debemos seleccionar la fórmula y presionar: CTRL + MAYUS + ENTRAR

Y esto es todo, espero que os sea de utilidad : )

Descarga el archivo de ejemplo pulsando en: OBTENER EL VALOR DE UNA CELDA SEGÚN EL VALOR MÁXIMO DE UNA MATRIZ CON VARIAS CONDICIONES (PARTE II)

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

Mediante la suscripción al blog, la realización comentarios o el uso del formulario de contacto estás dando tu consentimiento expreso al tratamiento de los datos personales proporcionados según lo dispuesto en la ley vigente (LOPD). Tienes más información al respecto en esta página del blog: Política de Privacidad y Cookies

ORDENAR EL CONTENIDO NUMÉRICO DE UNA CELDA

Hola a todos:

El post de hoy, como de costumbre, surge como respuesta a una consulta. El lector buscaba una solución para poder ordenar el contenido números de una celda (pero con los números separados por comas).

Sobre este asunto ya he escrito varias entradas relacionadas, en concreto en este post: ORDENAR LAS PALABRAS DE UNA CELDA ALFABÉTICAMENTE.

Aunque en ese ejemplo trabajaba con palabras, una sencilla modificación podría servir para realizar el ejercicio. Por ejemplo, si tenemos este número en una celda: 8,2,9,9,6,5 y queremos este dato: 2,5,6,8,9,9 utilizaremos esta función (UDF):

Function OrdenarNumCom(ByVal micelda As String)
'Declaramos las variables
Dim Matriz As Object, numero As Variant, num As Variant
Dim inum As String
'Creamos colección arraylist para ir agregando los elementos de la matriz
Set Matriz = CreateObject("System.Collections.ArrayList")
'Por cada objeto/palabra contenida en la celda seleccionada
For Each numero In Split(micelda, ",")
'Añadimos cada palabra a la matriz con un bucle
Matriz.Add numero
Next numero
'Una vez la matriz creada la ordenamos
Matriz.Sort
'Pasamos los datos ya ordenados a una cadena de texto
For Each num In Matriz
inum = inum & "," & num
Next num
OrdenarNumCom = Trim(Mid(inum, 2, Len(inum)))
'Limpiamos variable de objeto
Set Matriz = Nothing
End Function

Si lo que queremos hacer es ordenar los números pero sin comas, por ejemplo: 715017 y queremos obtener esto: 011577 utilizaremos esta macro:

Function Ordenanum(ByVal micelda As String)
'Declaramos las variables
Dim Matriz As Variant, num As Variant, inum As String
Dim numero As String, j As Long
'Creamos colección arraylist para ir agregando los elementos de la matriz
Set Matriz = CreateObject("System.Collections.ArrayList")
'Por cada núumero contenido en la celda seleccionada
For j = 1 To Len(micelda) Step 1
numero = Mid(micelda, j, 1)
'Añadimos cada número a la matriz con un bucle
Matriz.Add numero
Next j
'Una vez la matriz creada la ordenamos
Matriz.Sort
'Pasamos los datos ya ordenados
For Each num In Matriz
inum = inum & "" & num
Next num
Ordenanum = inum
'Limpiamos variable de objeto
Set Matriz = Nothing
End Function

Con estas dos funciones podremos realizar perfectamente el trabajo. Y en el caso de la función que ordena los números separados por comas, si el separador es otro, solo tenemos que cambiarlo en el código y funcionará perfectamente.

ORDENAR EL CONTENIDO NUMÉRICO DE UNA CELDA

Y esto es todo, espero que os sea de utilidad.

Descarga el archivo de ejemplo pulsando en: ORDENAR EL CONTENIDO NUMÉRICO DE UNA CELDA

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

Mediante la suscripción al blog, la realización comentarios o el uso del formulario de contacto estás dando tu consentimiento expreso al tratamiento de los datos personales proporcionados según lo dispuesto en la ley vigente (LOPD). Tienes más información al respecto en esta página del blog: Política de Privacidad y Cookies