Fórmulas con resultados múltiples

viernes, julio 02, 2010

Hay situaciones en las que una fórmula puede dar más de un resultado. El caso clásico es con las funciones de búsqueda como BUSCARV o INDICE.


La alternativa más práctica en estos casos es usar Autofiltro (o Filtro Avanzado). Pero hay situaciones, por ejemplo cuando construimos un tablero de comandos (dashboard), donde queremos que los resultados aparezcan en un rango determinado de la hoja o en otra hoja.

Supongamos que tenemos esta tabla (la misma que usamos en la nota sobre el uso de BUSCARV en listas con valores repetidos):



En otra hoja queremos poner el nombre de un producto y que se desplieguen todas las órdenes de compra del producto.

Si usamos BUSCARV (o INDICE con COINCIDIR) para obtener todas las órdenes de compra de tornillos, sólo podemos obtener un resultado


Sólo podemos obtener un resultado por celda. No hay ninguna forma de escribir resultados de una fórmula en otra celda que no contenga la fórmula, al igual que no podemos cambiar la estructura de la hoja con fórmulas.
Si queremos poner en una hoja de Excel todos los resultados posibles de una fórmula de búsqueda tendremos que usar otras técnicas.

En esta nota mostraré dos técnicas para obtener todos los resultados. Una estrategia posible es usar múltiples fórmulas.


Establecemos por adelantado cuál pueda ser el número máximo de resultados a obtener y en un rango de celdas contiguas introducimos esta fórmula matricial:

=INDICE(od_compra;K.ESIMO.MENOR(SI(DESREF(od_compra;0;0;FILAS(od_compra);1)=$B$2;FILA(DESREF(od_compra;0;0;FILAS(od_compra);1))-FILA(DESREF(od_compra;0;0;1;1))+1;FILA(DESREF(od_compra;FILAS(od_compra)-1;0;1;1))+1);FILA()-3);2)

donde el nombre "od_compras" define el rango A2:B10 de nuestro ejemplo

Por ejemplo, en una nueva hoja ("formulas") introducimos la fórmula en la celda A4 y la copiamos en todo el rango hasta la celda A9 (estamos suponiendo que el número máximo de órdenes posibles es seis)




Dado que sólo hay tres órdenes de compras para tornillos, el resultado en las celdas A18:A20 es #¡REF!

Para ocultar estos resultados podemos usar formato condicional




Esta técnica tiene varias desventajas: el uso de fórmulas matriciales, el uso de DESREF que es volátil, y además es complicada.

Una técnica alternativa es usar la cámara junto con Autofiltro.

Activamos el Autofiltro en la tabla de datos (en la hoja "datos"). Luego seleccionamos todo el rango de la tabla sin incluir los encabezamientos y activamos la cámara; creamos una nueva hoja ("cámara") y pegamos la imagen en el lugar deseado




En la hoja Datos filtramos la tabla de acuerdo al producto deseado




La imagen en la hoja "cámara" reflejará el cambio instantáneamente




El inconveniente con este modelo es que debemos ir a la hoja Datos para hacer el filtrado. Si queremos manejar los resultados desde la hoja "cámara", tendremos que utilizar una macro para manejar el filtrado por "control remoto".

Empezamos por agregar una lista desplegables, con validación de datos, en la celda B2 de la hoja "cámara" para poder controlar el filtrado




Luego ponemos este código en un módulo común del editor de Vb

Sub filtrado_ordenes()
    Dim strCrit As String
    Dim rngTablaDatos As Range
  
    strCrit = Sheets("camara").Range("B2")
  
    Set rngTablaDatos = Sheets("datos").Range("A1").CurrentRegion
  
    If strCrit <> "Todos" Then
        rngTablaDatos.AutoFilter Field:=1, Criteria1:=strCrit
    Else
        rngTablaDatos.AutoFilter
    End If
  
End Sub


Para activar el código cuando elegimos el producto programamos este evento en el módulo de la hoja "cámara"

Private Sub Worksheet_Change(ByVal Target As Range)
  
    If Target.Address = "$B$2" Then Call filtrado_ordenes

End Sub

Con esto hemos completado nuestro modelo. Podemos ocultar la hoja "datos" de manera que nuestro usuario sólo vea la hoja "cámara" desde la cual puede manejar las que órdenes serán exhibidas en la hoja.

El archivo con el ejemplo se puede descargar gratuitamente aquí.


11 comments:

Luis 02 julio, 2010 19:32  

Otra forma para generar este tipo de listados es aumentando una celda como ID la cual esta compuesta por la concatenación del producto y un consecutivo ( tornillo-1, tonillo-2,etc) esta se consigue muy fácil con un countif con el originen sin anclar. Y para poner el numero buscado en otra hoja simplemente con un vlookup. Yo lo implemente en un proceso mensual y me funciono muy bien. Luis L

Anónimo,  03 julio, 2010 09:07  

Ojala pudieran detallar el procesidimiento que menciona Luis.
Gracias
Atentamente,
Alexx
Los Mochis, Sinaloa, México

Jorge L. Dunkelman 03 julio, 2010 11:39  

La técnica que propone Luis es usar una columna auxiliar para generar un identificador para cada valor repetido. Luego, en lugar de usar la fórmula matricial complicada que expuse, se puede usar un BUSCARV sencillo.
Tal vez publique una nota con esta técnica.

dolvera 05 julio, 2010 17:47  

Hola Jorge, he leido varias de tus entradas y me han ayudado mucho en el pasado, pero ahora necesito generar una tabla comparativa de costos en EXCEL, la idea es que tengo dos, tres o mas "proveedores" y bastantes items a comparar, debo comparar precios entre ellos, use el MIN y el IF, pero hay detalles que no supe como resolver, es decir si no tengo un valor para uno de los items me arroja el valor menor es decir 0, y me gustaria que cuando quede vacia la celda simplemente la ignore o bien tome el valor de la que si tiene.

Prov. A Prov. B
Item A $15 $16 Prov. A
Item B $15 Prov. A

Jorge L. Dunkelman 05 julio, 2010 17:52  

dolvera,
el comentario no está relacionado con el tema de la nota .
Por favor fijate en la nota sobre como enviar consultas (el enlace Ayuda en la parte superior del blog)

napanno 18 noviembre, 2010 04:04  

Hola Jorge, escribi en un post distinto pero buscando en tu blog me di cuenta que es bastante parecido a lo que estoy buscando solo que como podría hacer para ademas de mostrar numero de orden me mostrara por ejemplo id del proveedor, fecha de compra, fecha de envio y otros datos similares, estoy colgando un archivo de ejemplo para ver si puedes darme una mano en eso...
https://sites.google.com/site/napannosite/home/tabla_p_mini_prueba.rar

Jorge L. Dunkelman 18 noviembre, 2010 17:15  

napanno,
estás comentando en una nota que no está relacionada con el tema de tu consulta. Podés consultarme directamente por mail (fijate en el enlace de la pestaña Ayuda).

joaquin guitart 22 junio, 2012 20:40  

hola, queria saber si se pueden obtener los datos pero sin mostrar los duplicados.

gracias

Jorge L. Dunkelman 23 junio, 2012 20:26  

Siguiendo con el ejemplo de la nota, ¿cuáles serían los duplicados?

jose hernandez 25 noviembre, 2016 20:06  

HOLA UNA DUDA, SI SI HUBIERA UNA TERCERA COLUMNA....COMO INDICAR QUE COLUMNA TRAER EL DATO...

Jorge Dunkelman 27 noviembre, 2016 16:21  

Si usas la técnica con la cámara, tendrás que "fotografiar" la columna deseada.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP