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

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: MS Excel

Catalogo de imágenes en Excel – Nota aclaratoria

Desde la publicación de la nota sobre el manejo de una base de datos con imágenes en Excel, hace más de dos años, he recibido muchos pedidos de aclaración sobre la técnica empleada. Sucede que varios de mis lectores no han logrado usar los métodos allí descritos en sus propios modelos.
En un intento de facilitar la explicación he creado una animación que pueden descargar en este enlace.
Una vez pulsado el enlace se abrirá la página de descarga. El enlace no es visible a primera vista



El archivo está comprimido, por lo que deberá ser descomprimido



Pueden apretar el botón Open o el botón Save para guardar el archivo y abrirlo más tarde.
El archivo comprimido contiene tres archivos.



Para ver la animación deben activar el archivo HTML (la animación se abrirá en una página del navegador).
Están invitados a dejar comentarios en especial sobre la técnica de animación como herramienta didáctica.

Technorati Tags: MS Excel

Agrupar datos de tablas dinámicas de acuerdo a valores numéricos

Hasta ahora hemos visto como agrupar (y desagrupar) datos en una tabla dinámica en base a campos de fechas o texto. Existe también la posibilidad de agrupar de acuerdo a valores numéricos.
Volviendo a al ejemplo de las tiendas, supongamos que queremos agruparlas por el monto de ventas en intervalos de 150000, es decir, un grupo con tiendas con ventas de 0 a 149999, otro grupo de 150000 a 299999 y así sucesivamente.
Antes de agrupar nuestra tabla dinámica se ve así



Como sólo podemos agrupar y desagrupar por campos de filas o de columnas, agregamos el campo Ventas (que ya aparece en el área de datos) al área de filas



Seleccionamos el campo Ventas del área de filas (en nuestro ejemplo la celda B2) y abrimos el menú Agrupar y mostrar detalle-Agrupar



Como Excel ha detectado que el campo contiene números (y éstos no son fechas) abre automáticamente el formulario Agrupar-Automático.
Excel detecta automáticamente los valores mínimo (Comenzar en) y máximo (Terminar en) del rango, los cuales pueden ser cambiados de ser necesario. En nuestro caso ponemos 0 como mínimo y 500000 como máximo; en la ventanilla Por del formulario ponemos 150000. Apretamos Aceptar



Ahora tenemos que arrastrar el campo Ventas del área de filas y ponerlo a la izquierda del campo Tienda. Al hacerlo obtenemos este resultado



Podemos obtener el mismo resultado creando un campo adicional en la base de datos, pero este método es más eficiente.

Technorati Tags: MS Excel