Archive

Posts Tagged ‘Matrical’

Simular BUSCARV con valores repetidos en Excel

May 17, 2012 1 comment

Como hemos comentado en posts anteriores, la función BUSCARV es de las más socorridas para hacer búsquedas de datos en tablas. La complicación llega cuando tenemos en nuestra tabla valores repetidos.

En la siguiente tabla, BUSCARV nos traerá el valor de 67 si queremos traer las ventas de ‘Sergio’.

image

Ahora bien, en nuestra tabla tenemos otro valor para ‘Sergio’ que está en la fila 9, que son 9 ventas. Para obtener tal resultado utilizamos una fórmula matrical propuesta por Chip Pearson.

Suponiendo que ‘nombres’ es nuestra tabla y que deseamos obtener el valor dependiendo de la posición que deseemos.

={INDICE(nombres,K.ESIMO.MENOR(SI(DESREF(nombres,0,0,FILAS(nombres),1)=B14,FILA(DESREF(nombres,0,0,FILAS(nombres),1))-FILA(DESREF(nombres,0,0,1,1))+1,FILA(DESREF(nombres,FILAS(nombres)-1,0,1,1))+1),B15),2)}

image

Nota: al copiar se deben eliminar los las llaves de inicio y fin, ya que se ingresarán automáticamente al presionar Ctlr + Shift + Enter, ya que es fórmula matrical.

En caso de que ingresemos una posición inexistente, el resultado que nos arrojará será #¡REF!

Para eso vamos a combinar la fórmula con la función SI.ERROR para obtener un mensaje personalizado en caso de error. La fórmula quedaría:

{=SI.ERROR(INDICE(nombres,K.ESIMO.MENOR(SI(DESREF(nombres,0,0,FILAS(nombres),1)=B14,FILA(DESREF(nombres,0,0,FILAS(nombres),1))-FILA(DESREF(nombres,0,0,1,1))+1,FILA(DESREF(nombres,FILAS(nombres)-1,0,1,1))+1),B15),2),"No hay datos")}

Saber el valor de la última celda con datos en Excel con DESREF

DESREF: Devuelve una referencia a un rango que es un número especificado de filas y columnas de una referencia dada.

Quise adelantarme con la descripción de la función DESREF que nos da Excel, para tratar de entenderla. La nos arroja una referencia en base al número de filas o columnas dadas, lo que equivale a la función OFFSET de vba. Podemos encontrar más información en el sitio de Microsoft.

En la tabla de la imagen se muestran los datos algunos pedidos realizados en el año 2011. El dato que necesitamos obtener es la fecha del última pedido realizado. Para realizar la función necesitamos tomar como referencia una columna que tenga todos los datos completos, para que la función CONTARA nos de la cantidad de filas llenas.

Fórmula (ya que la función es matrical debemos ingresar Ctrl + Shift + Enter)

=DESREF(E1,CONTARA(D:D)-1,0,CONTARA(D:D),1)

Imagen de la tabla

image

Otros usos de DESREF: Hacer un rango dinámico para una Tabla dinámica en Excel