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

Anuncios

OBTENER EL VALOR MÁXIMO Y MÍNIMO EN UNA MATRIZ

Hola a todos!

En el post de hoy voy a tratar un aspecto importante a la hora de trabajar con matrices, esto es, obtener el valor máximo o mínimo en un array (matriz).

No siempre trabajaremos con rangos, donde la obtención del valor máximo o mínimo es sencilla (utilizando las funciones MAX o MIN), muchas veces tenemos que utilizar matrices en nuestros procesos y en ocasiones debemos extraer ciertos valores.

Existen varias formas de hacer este trabajo, aunque hoy os mostraré la que yo suelo utilizar. Dependiendo de si la matriz nos viene creada o la debemos crear nosotros, el proceso será más o menos extenso.

Voy a suponer que tenemos los datos en una hoja Excel y debemos pasarlos a una matriz para luego determinar el valor mínimo y máximo de ese rango:

OBTENER EL VALOR MAXIMO Y MINIMO EN UNA MATRIZ

Ejecutando el siguiente código vamos a poder obtener los datos que necesitamos:

Sub OBTENER_MAX_MIN()
Dim rng As Range, fin As Long, celda As Variant
Dim sCadena As String, listMatriz As Object, nDato As Variant, nCadena As String
Dim oMatriz As Variant, n As Long, Min As Double, Max As Double
'Seleccionamos rango de números
Set rng = Selection
fin = rng.Count
'Controlamos que seleccionamos un rango de celdas con datos
If fin <= 1 Then Exit Sub
'Componemos una string con los valores seleccionados
For Each celda In rng
sCadena = Trim(sCadena) & " " & celda.Value
Next celda
'Pasamos los datos a una matriz (Lista)
Set listMatriz = CreateObject("System.Collections.ArrayList")
For Each nDato In Split(sCadena, " ")
listMatriz.Add nDato
Next nDato
'Ordenamos los datos
listMatriz.Sort
'Pasamos los datos a una cadena de texto
For Each nDato In listMatriz
nCadena = Trim(nCadena) & " " & nDato
Next nDato
'Convertimos el string en una matriz y con un loop extraemos valor mínimo y máximo
oMatriz = Split([nCadena], " ")
For n = LBound(oMatriz) To UBound(oMatriz)
If n = LBound(oMatriz) Then Min = oMatriz(n)
If n = UBound(oMatriz) Then Max = oMatriz(n)
Next
'Mostramos resultado en msgbox
MsgBox ("El valor mínimo es: " & Min & " y el valor máximo es: " & Max)
'Liberamos espacio en memoria
Set listMatriz = Nothing
End Sub

Lo verdaderamente importante de la macro es el método utilizado para obtener el valor máximo y mínimo. Es decir, componemos un array:

'Pasamos los datos a una matriz (Lista)
Set listMatriz = CreateObject("System.Collections.ArrayList")
For Each nDato In Split(sCadena, " ")
listMatriz.Add nDato

Next nDato

y ordenamos los datos:

listMatriz.Sort

A continuación volvemos a pasar la información a una cadena de texto con los datos ordenados:

'Pasamos los datos a una cadena de texto
For Each nDato In listMatriz
nCadena = Trim(nCadena) & " " & nDato
Next nDato

En este punto, la información que tenemos es una cadena de texto totalmente ordenada, aquí lo podéis ver mostrando el contenido de la variable nCadena:

OBTENER EL VALOR MAXIMO Y MINIMO EN UNA MATRIZ1

Con esta información, ahora ya podemos obtener los datos que estamos buscando. Dado que sabemos que el primer valor de la cadena es el mínimo y el último el máximo, solo tenemos extraer esa información.

Esto lo podemos hacer de varias formas, pero dado que estamos trabajando con matrices, esta es la solución que voy a utilizar. Pasamos los datos de nuevo a una matriz y mediante un bucle “for-next” y con las funciones LBound y UBound que determinan el inicio de la matriz (siempre es 0) y el final, (en este caso 60, dado que son 60 elementos seleccionados), podemos detectar con un condicional cuando “n” es igual a LBound o “0” y obtendremos el mínimo, y de la misma forma, cuando “n” es igual a UBound obtendremos el máximo valor:

oMatriz = Split([nCadena], " ")
For n = LBound(oMatriz) To UBound(oMatriz)
If n = LBound(oMatriz) Then Min = oMatriz(n)
If n = UBound(oMatriz) Then Max = oMatriz(n)
Next

Como habéis podido observar, ya hemos conseguido la información que necesitábamos. Ahora podemos incluir esta información en nuestros procesos o cálculos dado que está contenida en ambas variables (Min y Max).

Para que veáis el resultado de la macro, he pasado los resultados a un msgbox para ver de manera informativa los datos:

OBTENER EL VALOR MAXIMO Y MINIMO EN UNA MATRIZ2

Aunque debería haber utilizado directamente un matriz, sin hacer referencia a la hoja ni al rango seleccionado, creo que de esta forma se ilustra mejor el ejemplo.

Y esto es todo, espero que esta metodología os ayude en vuestros procesos.

Descarga el archivo de ejemplo pulsando en: OBTENER EL VALOR MÁXIMO Y MÍNIMO EN UNA MATRIZ

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

Donate Button with Credit Cards

¡¡Muchas gracias!!