Foro de Ayuda Excel

Buenas!!!

Os muestro la imagen de mi base de datos en plan sencillito.



Mi problema es que quiero crear una formula para la columna B de mi hoja PRODUCTOS que me busque ese codigo de producto en la hoja de COMPRAS y me devuelva el ultimo valor conocido en la columna de precios para ese codigo de producto.
En el ejemplo me tendria que salir que el precio de la cocacola seria 1,20 y para la cerveza 0,50 €.

Como os he comentado la base de datos que planteo aqui es una base de datos sencilla, pero con la que realmente quiero aplicarlo tiene más de 4.000 codigos de productos y 11.000 filas en la hoja de COMPRAS.

He estado buscando alguna solución por internet, pero no encuentro nada. Además de este, que en principio, es un problema menor tengo otro con la misma base de datos que ya os plantearé en el caso de que deis con la tecla.

Muchas gracias.

Hola Jaime Cruz


Te anexo un ejemplo, a lo mejor te podria funcionar, para que de el resultado corecto, debes introducirlo con formula matricial ctr+maysc+enter {}, lo puedes ajustar a tus necesidades, el detalle es que creo que como es formula matricial seguramnte te consumirá muchos recursos...


saludos

Archivos Adjuntos

Ultimo dato ingresado.zip (7,2 KB, 27 visitas)

Prueba con la función buscarv:

=SI(A4;BUSCARV(A4;A2:C5;3;FALSO);"")

Si pusieras la celda A5 el valor a buscar seria el de la cerveza

Salu2...

__________________
Mi web Mi foro

Toldeman he intentado aplicar tu formula, pero está mal expresada y tampoco consigo encontrarle la lógica de lo que querias hacer. Gracias de todas formas.

Por otra parte, he visto que la solución que propones, Neverdelimon1, funciona perfectamente, pero te agradeceria que me explicaras algo más sobre la formula.

La formula en sí es esta para B2 en la hoja de PRODUCTOS:

{=INDIRECTO("COMPRAS!C"&MAX((((COMPRAS!$B$2:$B$7)= PRODUCTOS!A2)*(FILA(COMPRAS!$B$2:$B$7)))))}

La verdad es que no he visto nunca formulas matriciales. Por lo que he estado leyendo si se cumple la condición reduce la matriz poco a poco hasta que se da la última coincidencia. Por eso supongo que es lo que comentas de que agota más recursos.

Por lo que veo al usar la función indirecto intentas buscar la referencia en la columna C de la hoja COMPRAS que cumple la condicion que buscamos, es decir, que sea la ultima fila en que coincide el codigo del producto.

Mi duda esta en esta parte:
MAX((((COMPRAS!$B$2:$B$7)=PRODUCTOS!A2)*(FILA(COMP RAS!$B$2:$B$7)))))}

He calculado =FILA(COMPRAS!$B$2:$B$7) y me da que el resultado es 2.
Como lo metes dentro del parentesis en que va el maximo tambien he independizado esa parte de la funcion y he puesto:
=MAX(FILA(COMPRAS!$B$2:$B$7))
El resultado me sigue saliendo 2, pero es que si cambio la expresion MAX por MIN el resultado sigue siendo el mismo.

Si calculo =MAX(COMPRAS!$B$2:$B$7)=PRODUCTOS!A2) el resultado es 2.

Como ves estoy algo pegado en saber lo que has querido hacer. Conozco las formulas que usas, pero no entiendo el trasfondo.

Por otra parte, si el hacerlo de forma matricial consume muchos recursos (precisamente hoy trabajando sobre esa hoja de calculo me decia que tenia problemas de memoria), sabrias otra forma de hacerlo con alguna formula.

Gracias

Me autocorrijo:

Si calculo =MAX(COMPRAS!$B$2:$B$7)=PRODUCTOS!A2) el resultado es 1.

Visto el gran numero de registros en tu base, aun cuando se pudeiran construir formulas matriciales o pseudomatriciales, creo que la,solucion mas eficiente es la que se obtendria con Buscarv, pero con un pequeño detalle, ordenando la tabla por la fecha de compra en forma descendente, con lo cual al buscar el producto tomara le registro mas reciente y traera el valor de la columna Precio asociado.
si quieres usar formulas, puedes probar esto
=INDICE(COMPRAS!$C$2:$C$7;COINCIDIR(MAX(INDICE((CO MPRAS!$B$2:$B$7=PRODUCTOS!A2)*COMPRAS!$A$2:$A$7;0) );INDICE((COMPRAS!$B$2:$B$7=PRODUCTOS!A2)*COMPRAS! $A$2:$A$7;0);))
Puede parecer Pesada, pero lo es menos que otras con INDIRECTO, pruebala en mas de 3000 filas

__________________
GALILEOGALI
http://excelgali.mejorforo.net/

Si tus datos siempre estan ordenados por fecha, esta opción puede servir.

=BUSCAR(2;1/(COMPRAS!$B$2:$B$7=A2);COMPRAS!$C$2:$C$7)

Saludos

Hola Jaime Cruz

Para que puedes ver lo que hace la fórmula, te recomiendo que te vayas al menú Heramientes--- Auditoria de Fórmulas --Evaluar fórmula, ahi se muestra paso a paso como la va resolviendo excel internamente, de forma general te comento la lógica, aunque humildemente confieso que no soy un experto.

los falsos equivalen a 0 Falso=0
los verdaderos equivalen a 1 Verdadero=1

La función indirecto ya la tienes, con MAX localizamos la fila mayor que coincida con el producto, recuerda introducir la formula presionando ctr+mays+enter porque si no el resutado es distinto

formula original
{=MAX((((COMPRAS!$B$2:$B$7)=PRODUCTOS!A2)*(FILA(CO MPRAS!$B$2:$B$7))))}

Comienzo paso por paso
MAX((({"COCA-COLA"\"otro"\"COCA-
COLA"\"CERVEZA"\ "CERVEZA "\ "COCA-COLA})=PRODUCTOS!A2
)*(FILA(COMPRAS! $B$2: $B$7))))

Efectua la comparación

MAX((({VERDADERO\FALSO\VERDADERO\FALSO\VERDADERO\ VERDADERO} )*(FILA(COMPRAS! $B$2: $B$7))))

Se extraen el número de fila de cada dato en el rango B2 a B7

MAX(({VERDADERO\FALSO\VERDADERO\FALSO\FALSO\VERDAD ERO}* ({2\3\4\5\6\7})))


Se hace la multiplicación de las matrices, recordando (Verdadero=1, falso=0)

MAX(({2\0\4\0\0\7}))

se obitiene el número máximo

7
que es nuestra última fila que coincide con lo que buscamos...

La otra opción que se me ocurre por el número de filas que manejas seria un macro, que consumiria menos tiempo ya que solo se produciria al evento, aunque definitivamente la opción que te muesta el master Gali es mucho mejor y la del master SailePaty wow es la ideal

Saludos...

Vaya caña de exceleros. Que barbaridad!!!

Y yo que creía que lo dominaba!!!

Muchas gracias por toda la explicación que me has dado NEVERDELIMON. Ya lo he entendido a la perfección (menos mal que me lo has explicado porque si no no tenía ni idea de que es lo que se estaba multiplicando) y por lo que he estado viendo me sirve para comprender también como esta hecha la fórmula de GALILEOGALI, que he probado y que he visto que funciona perfectamente. Ahora lo que me falta es comprender del todo esa magnífica función, que por lo que he visto por encima tiene mandanga para entenderla.

También he visto que funciona la fórmula de SAILEPATY, pero ya tengo un mareo en la cabeza importante, así que mañana analizaré detenidamente todas vuestras propuestas. En principio, y a simple vista, SAILEPATY plantea la mejor opción o al menos la más sencilla visualmente.

Gracias a todos, que habeis demostrado que sois unos cracks.

He puesto en practica la solucion que me habeis dado los tres. La de GalileoGali no la he conseguido entender, pero intentando extrapolarla tampoco he conseguido que salga bien.

De todas formas con las otras dos los resultados salen perfectamente. El handicap que se me ha presentado es que poniendo esa formula para las casi 4000 filas de que dispone la base de datos de productos, simplemente con insertar una columna (incluso fuera del rango de las formulas) se pone a pensar y a recalcular todo.

He visto que hay una opcion en el excel 2007 (el que tengo en mi casa), que supongo que tambien estara en el office 2000 (el que tengo en el trabajo) que permite decirle cuando tiene que hacer los calculos a la hoja de excel. Supongo que por ahí vendría la solución.

Por otra parte y avanzando más en otros problemas que tengo con la misma hoja de cálculo os planteo lo que quiero ya con la base de datos real, aunque algo trastocada en los datos.

Ya con las soluciones que me habeis dado lo que haré sera calcular el ultimo precio de cada producto en la hoja DE PRODUCTOS e incluso creare una columna en la hoja de COMPRAS que me muestre el último precio anterior de ese mismo producto. Esto lo haria tambien para añadir otra columna más que le permita a los tecnicos de la empresa saber si hay alguna divergencia de precio de un determinado producto con respecto al precio inmediatamente anterior, y en el caso de que las hubiera que se le mostrara de alguna forma que tiene que revisarlas. Pero vamos para esto ya en principio cuento con lo que ya me habeis explicado.

Luego mis otros dos problemas son los siguientes:
1- Si de la fase anterior al tecnico de la empresa le canta que hay una diferencia en precio, pues ya se pondria a sacar un filtro en el que indicando un determinado codigo de producto salgan todas las compras realizadas hasta la fecha.
El problema que he visto al intentar hacer esto es que no se puede realizar un filtro condicionado al valor de un texto de una celda, o al menos yo no se hacerlo.
Probe con una macro el otro día, pero tardaba mucho (ahora a lo mejor tardaría bastante menos porque lo que he hecho es quitar muchas de las formulas que antes habia de datos ya muy antiguos y pegarlas de forma especial como valores lo que ha bajado el tamaño de la base de datos de 25 MB a unos 4).
Luego aqui tambien me surgen dudas de saber si lo mejor es que el filtro se realice directamente sobre la hoja de compras sobre la que trabajan los tecnicos o que mediante una macro se cree una replica de la hoja de calculo y se muestre toda la relación de operaciones que se han realizado para ese codigo de producto.
2- Hacer exactamente lo mismo que antes pero para los codigos de obra de forma que se pueda sacar los datos de como van los gastos en materiales de las distintas obras.
Pero claro para hacer todo esto tendría que ser algo operativo y que no tardase demasiado.

A ver si me podeis asesorar para encontrarle una solución. Gracias.

Aqui podeis ver la BASE DE DATOS