Calcular la posición de un elemento en un rango de Excel.

jueves, enero 22, 2009

Un lector me consultaba cómo calcular la posición del último 5 en esta lista



Podemos ver que el último 5 ocupa el octavo lugar en la lista (o vector) y que se encuentra en la fila 9 de la hoja. En esta nota veremos cómo encontrar dinámicamente la última posición de cualquier elemento de la lista.

Por comodidad empezamos por definir un nombre que contenga el rango de la lista




En la celda D1 ponemos el número de la lista cuya última posición buscamos (esto nos permitirá usar nuestra fórmula en forma dinámica) y en la celda E1 ponemos esta fórmula matricial

=MAX((FILA(Valores)-1)*(Valores=$D$1))

Como toda fórmula matricial debe ser ingresada a la celda apretando simultáneamente Ctrl+Mayúscula+Enter



Podemos explicar esta fórmula mostrando la alternativa de usar columnas auxiliares. En ese caso empezamos por construir una columna auxiliar para calcular la posición del elemento en la lista. Esto lo hacemos calculando la fila de la celda con la función FILA y restando del resultado el número de filas desde la fila 1 hasta la primer fila de la lista (en nuestro caso 1)



Nuestra segunda columna auxiliar calculará si el valor de la celda coincide con el valor que estamos buscando



Como vemos el número 5 ocupa el tercer y el octavo lugar en la lista.

Nuestra tercer columna auxiliar consiste en multiplica Auxiliar1 por Auxiliar 2



Ahora es fácil ver que la fórmula =MAX(H2:H12) da como resultado 8, que es la posición del último 5 en la lista. Todo esto lo hemos comprimido en una única fórmula matricial, como mostramos más arriba.

Ahora, envalentonados con nuestro dominio de Excel, queremos calcular la posición del primer 5. No podemos usar la función MIN ya que en el vector de la fórmula matricial siempre habrá algún 0 y por eso siempre nos dará la posición 0 como resultado.
Aquí echamos mano a la función K.ESIMO.MENOR. Esta función da como resultado el k-ésimo menor valor de un conjunto de datos. En nuestro caso queremos encontrar el menor valor de la matriz (FILA(Valores)-1)*(Valores=$D$1) (es decir, Auxiliar 3) sin tomar en cuenta los valores 0.
Para esto necesitamos calcular cuantos 0 hay en el vector. La idea más obvia es usar CONTAR.SI, pero esta función tiene un problema: no se puede usar en fórmulas matriciales. En lugar de CONTAR.SI usaremos SUMA:

=K.ESIMO.MENOR(( FILA(Valores)-1)*(Valores=Hoja1!$D$1)*(((Valores=D1)<>0));SUMA(--(FILA(Valores)*(Valores=D1)=0))+1)

Recordemos que ésta es una fórmula matricial (Ctrl+Mayúsculas+Enter)

Para simplificar nuestra fórmula definimos el nombre "auxiliar" que contendrá la matriz creada por la expresión

auxiliar =( FILA(Valores)-1)*(Valores=Hoja1!$D$1)



Ahora podemos construir la fórmula en forma más legible:

=K.ESIMO.MENOR(auxiliar*(((Valores=D1)<>0));SUMA(--(auxiliar=0))+1)




El archivo con el ejemplo se puede descargar aquí


Technorati Tags:

7 comments:

Carlos Ponce de León,  09 marzo, 2009 20:46  

Hola Jorge.

Me ha encantado tu solución, pero investigando algo más, quisiera remarcar que la función CONTAR.SI si se puede utilizar en fórmulas matriciales siempre que uses la referencia del rango en lugar del nombre. De hehco he modificado tu fórmula con CONTAR.SI y me ha funcionado tanto en la versión 2003, como en la 2007.

También se puede solucionar con SUMAR(SI(Auxiliar=0;1;0))+1} -en la fórmula matricial (esto evita el uso de los dos guiones).

Tampoco entiendo por qué vuelves a multiplicar Auxiliar por ((Valores=D1)<>0)), ya que auxiliar ya tiene únicamente las filas en las que aparece el valor en D1.

En fin, te propongo que la fórmula final quede
=K.ESIMO.MENOR(Auxiliar;SUMA(SI(Auxiliar=0;1;0))+1)

como fórmula matricial.

Jorge L. Dunkelman 09 marzo, 2009 23:27  

Gracias por el aporte.
Cuando dices que si se puede usar CONTAR.SI en fórmulas matriciales, te refieres a CONTAR(SI?

Roger Guerrero,  23 marzo, 2009 16:03  

Me parece que para encontrar la posicion del primer valor en aparecer basta con usar la formula:
=MATCH(valores,d1,0)
No conozco su traduccion al español.

Gerson Pineda H 26 mayo, 2009 01:34  

Hola Jorge
Solo para agregar algo mas a tus funciones matriciales, para convertirlas en normales, solo pon al principio la función SUMAPRODUCTO y listo, espero hayan comprendido mi aporte

Ejemplo, mi formula no tiene encabezado en el rango:

=SUMPRODUCT(MAX((ROW(A1:A10))*(A1:A10=C1)))

y

=SUMPRODUCT(SMALL(AUXILIAR*(((VALORES=C1)<>0)),SUM(--(AUXILIAR=0))+1))

Saludos

Anónimo,  03 agosto, 2010 20:45  

tengo una duda, espero puedan ayudarme..

mi problema es el siguiente: tengo valores continuos descendentes incluyendo ceros al final, mi problema esta en que al usar la formular k.esime.menor, necesito excluir los valores iguales a 0 de los resultados.
estan ordenados horizontalmente.

Jorge L. Dunkelman 07 agosto, 2010 13:47  

Eso es justamente lo que muestro en esta nota (y también en esta.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP