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: MS Excel
Hola Jorge.
ResponderBorrarMe 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.
Gracias por el aporte.
ResponderBorrarCuando dices que si se puede usar CONTAR.SI en fórmulas matriciales, te refieres a CONTAR(SI?
Me parece que para encontrar la posicion del primer valor en aparecer basta con usar la formula:
ResponderBorrar=MATCH(valores,d1,0)
No conozco su traduccion al español.
MATCH() = COINCIDIR()
ResponderBorrarHola Jorge
ResponderBorrarSolo 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
tengo una duda, espero puedan ayudarme..
ResponderBorrarmi 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.
Eso es justamente lo que muestro en esta nota (y también en esta.
ResponderBorrar