Reto 01: ¿Cuál es el producto más vendido?
Todos tenemos unos días mejores que otros. Días de esos en que por más que quieres, nuestro cerebro no funciona bien…
Eso es lo que me ha pasado este fin de semana
Una persona que compró “BUSCARV y otras funciones de búsqueda“, me escribió el sábado un correo electrónico para intentar solucionar una duda que tenía con un ejercicio del libro que él había modificado. Cuando vi el correo me dispuse a echarle a una mano, pero me costó muchísimo tiempo, más de lo habitual, poder darle una respuesta.
La fórmula que le envié no me convenció del todo. Funcionaba perfectamente, pero me pareció demasiado complicada. Sé perfectamente que es posible simplificarla o incluso llegar al mismo resultado de una forma totalmente diferente, pero en ese momento no fui capaz de hacer que mi cabeza funcionase bien.
Y por eso te propongo el siguiente reto que durará toda esta semana. Escribe tu respuesta en los comentarios:
Reto 1: ¿Cuál es el producto más vendido?
Ten en cuenta esta imagen. Modificando el mes en la celda G4 debes conseguir que en G6 aparezca como resultado el producto con mayor venta del mes correspondiente. Puedes descargarte este archivo en la parte de abajo.
Esta es la respuesta que le di a esta persona:
=INDIRECTO(DIRECCION((COINCIDIR(MAX(DESREF(A4;1;COINCIDIR(G4;B4:D4);CONTARA(A5:A8)));DESREF(A4;1;COINCIDIR(G4;B4:D4);CONTARA(A5:A8)))+4);1))
Fórmula editada. Información en los comentarios. La fórmula correcta es la siguiente:
=INDIRECTO(DIRECCION((COINCIDIR(MAX(DESREF(A4;1;COINCIDIR(G4;B4:D4;0);CONTARA(A5:A8)));DESREF(A4;1;COINCIDIR(G4;B4:D4;0);CONTARA(A5:A8));0)+4);1))
Como ves, es un poco difícil de entender.
Ahora es tu turno.
Escribe un comentario con la fórmula que escribirías en G6 para que dé como resultado la mayor venta del mes. No es posible adjuntar archivos. Por favor, comenta tu fórmula.
La semana que viene publicaré todas las respuestas.
Hola a todos,
Sergio, aquí va mi propuesta:
=INDICE(A5:A8,COINCIDIR(MAX(INDICE(B5:D8,,COINCIDIR(G4,B4:D4,0))),INDICE(B5:D8,,COINCIDIR(G4,B4:D4,0)),))
Gracias, Silvia!! Es una solución mejor que la mía…
Según la configuración del teclado, es posible que haya que cambiar las comas por punto y coma.
Sergio una variacion a la de Silvia
=INDICE(A5:A8,COINCIDIR(MAX(DESREF(B5:D8,,COINCIDIR(G4,B4:D4,)-1,,1)),DESREF(B5:D8,,COINCIDIR(G4,B4:D4,)-1,,1),),)
Gerson, gracias por tu respuesta. Todavía no he tenido oportunidad de probarlas pero en un rato lo haré.
Otra mas por si te interesa
=INDICE($A$5:$A$8,INDICE(SUMA((B5:D8=MAX(INDICE(B5:D8,,COINCIDIR(G4,B4:D4,))))*(FILA(A5:A8)-4)),),)
Hola SERGIO, SILVIA, GERSON PINEDA y a todos los que participan directa o indirectamente en el reto!
Dejo otra versión distinta a las anteriormente presentadas:
=BUSCAR(1;0/FRECUENCIA(0;1/(1+INDICE(B5:D8;;COINCIDIR(G4;B4:D4;))));A5:A8)
Mi separador es “;”. ¡Bendiciones a todos!
Muy bien, John!! Nunca me imaginaría que las funciones BUSCAR y FRECUENCIA podrían estar en la misma fórmula….
Buenas!!! Mi formula es simplemente un buscarv inverso donde el valor buscado es el máximo de la columna D.
=INDICE(A4:D8;COINCIDIR(MAX(D5:D8);D4:D8;0);1)
Jaime, gracias por el aporte. Desgraciadamente no es una respuesta válida ya que el máximo estará en la columna que determina la celda G4.
hola, a lo mejor no es la mejor solución, pero a mí me ha facilitado muchas veces el trabajo.
lo primero que hago es copiar la lista de productos en otra columna o en la misma columna encima o debajo de donde están los datos.
luego aplico la función BDMAX(A4:D8;G4;A11:A15), donde
A4:D8 ==> señala toda la base de datos
G4 ==> señala el criterio (en este caso el mes), lo he probado con una validación de datos y funciona
A11:A15 ==> señala donde he colocado los datos una vez copiadas (en este caso los productos)
y consigo el dato pedido,
un saludo
Hola se me olvido una cosa, hay que dar nombre a todos los campos, yo he nombrado como producto (celda A4), la columna donde están los productos,
un saludo
Yo prefiero la solución de Juan, aunque se salga del contexto del reto, con las siguientes celdas:
A4 =”Producto”
F5 =”Máximo”
H4 =”Producto”
H5 =”*”
G4 = es el mes elegido
G5 =BDMAX(A4:D8;G4;H4:H5)
G6 =BDEXTRAER(A4:D8;A4;G4:G5)
Además matamos dos pájaros de un tiro obteniendo el producto más vendido en G6 y el máximo vendido de ese producto en G5, aunque en orden contrario, ¿se entiende?
Yo no soy muy de funciones de base de datos, pero la solución funciona perfectamente.
Gracias, Pedro.
Las funciones de bases de datos pueden sustituir a las tablas dinámicas, que tardan en actualizar debido a usar memoria caché, a las fórmulas farragosas, que no las entiende casi nadie, o incluso a fórmulas matriciales, que son volátiles por naturaleza y se recalculan siempre.
Son fáciles de aprender pues su sintaxis es similar: BDFunción(base_de_datos, campo , criterios).
Pero dan problemas, como todas las funciones a las que se saca de sus casillas, quiero decir, de sus celdas.
En tu reto, si la celda D5 = 1363 (dos productos de marzo con el mismo máximo) se genera error ya que, si más de un registro coincide con los criterios, BDEXTRAER devuelve el valor de error #¡NUM!
Con tu fórmula devuelve: Producto 4, sin avisar que Producto 1 comparte el mismo valor máximo…
Gajes de nuestro oficio que tiene que lidiar continuamente con las excepciones…
=SI(G4=”ENERO”;BUSCARV(MAX(INDIRECTO(G4));ELEGIR({21};A5:A8;B5:B8;C5:C8;D5:D8);2;0);SI(G4=”FEBRERO”;BUSCARV(MAX(INDIRECTO(G4));ELEGIR({31};A5:A8;B5:B8;C5:C8;D5:D8);2;0);SI(G4=”MARZO”;BUSCARV(MAX(INDIRECTO(G4));ELEGIR({41};A5:A8;B5:B8;C5:C8;D5:D8);2;0))))
He utilizado la funcion elegir, que creo que todavia no se ha publicado.
espero que os guste, ya que con esta funcion se puede hacer que BUSCARV funcione en direccion contraria.
Dioni, he probado tu solución, pero no ha funcionado. Aparece el error #REF!
cdelarosam: Sergio, siento tener que decirte, que la solución que apuntas no funciona cuando el mayor valor está en el producto primero (Producto 1) para cualquiera de los meses. Puedes hacer la prueba por ejemplo poniendo el valor 2000 en la celda C5.
Pongo debajo una solución válida que parece enreversada pero funciona:
=INDICE(A:D;COINCIDIR(MAX(INDIRECTO(SUSTITUIR(DIRECCION(1;COINCIDIR(G4;B4:D4;1)+1;4);”1″;””)&”:”&SUSTITUIR(DIRECCION(1;COINCIDIR(G4;B4:D4;1)+1;4);”1″;””)));INDIRECTO(SUSTITUIR(DIRECCION(1;COINCIDIR(G4;B4:D4;1)+1;4);”1″;””)&”:”&SUSTITUIR(DIRECCION(1;COINCIDIR(G4;B4:D4;1)+1;4);”1″;””));0);1)
Tienes razón cdelarosam, no funciona cuando el primer valor es el máximo. Voy a revisarla.
He copiado tu solución en la celda en cuestión y funciona perfectamente
cdelarosam, la solución que indicaba no funcionaba porque no había introducido el tipo de coincidencia de la función COINCIDIR. Si pruebas esta solución (modificada), verás que funciona correctamente:
=INDIRECTO(DIRECCION((COINCIDIR(MAX(DESREF(A4;1;COINCIDIR(G4;B4:D4;0);CONTARA(A5:A8)));DESREF(A4;1;COINCIDIR(G4;B4:D4;0);CONTARA(A5:A8));0)+4);1))
Ofrezco otra solución que funciona bien y es bastante fácil de entender:
=INDICE(A5:A8;COINCIDIR(MAX(DESREF(A4;1;COINCIDIR(G4;B4:D4;0);CONTARA(A5:A8)));DESREF(A4;1;COINCIDIR(G4;B4:D4;0);CONTARA(A5:A8));0))
Hola
No se porque te da el error, ya que yo la he vuelto a probar , y me funciona perfectamente, utilizo para la celda G4 una validación de B4:D4.
creo que pueden ser la comillas para identificar los meses, ya que no son iguales las que tengo en la hoja que las que se han copiado en el comentario.
=SI(G4=”ENERO”;BUSCARV(MAX(INDIRECTO(G4));ELEGIR({21};A5:A8;B5:B8;C5:C8;D5:D8);2;0);SI(G4=”FEBRERO”;BUSCARV(MAX(INDIRECTO(G4));ELEGIR({31};A5:A8;B5:B8;C5:C8;D5:D8);2;0);SI(G4=”MARZO”;BUSCARV(MAX(INDIRECTO(G4));ELEGIR({41};A5:A8;B5:B8;C5:C8;D5:D8);2;0))))
Dioni, el error que me aparece es debido a que la función INDIRECTO, por lo menos en las versiones instaladas en mi equipo, necesitan argumento con formato de texto, es decir, entre comillas. No obstante, aunque he puesto comillas a la referencia G4 sigue dando error porque la funcion MAX devuelve como resultado 0. Si utiliza BUSCARV para encontrar el valor 0, será imposible de encontrar…
Hola Sergio, dejo mi aporte.. una funcion logica: lo siento pero el excel que yo uso esta en ingles , solo tienes que traducir las funciones, bendiciones a todos.. : =IF(G4=”Enero”,INDEX(A5:D8,MATCH(MAX(B5:B8),B5:B8,0),1),IF(G4=”Febrero”,INDEX(A5:D8,MATCH(MAX(C5:C8),C5:C8,0),1),IF(G4=”Marzo”,INDEX(A5:D8,MATCH(MAX(D5:D8),D5:D8,0),1),FALSE)))
IF=SI | INDEX =INDICE | MATCH=COINCIDIR | MAX=MAX , Sludos y bendiciones a todos!!
Gracias, David. He probado la traducción al español de tu función y funciona (valga la redundancia) perfectamente:
=SI(G4=”Enero”;INDICE(A5:D8;COINCIDIR(MAX(B5:B8);B5:B8;0);1);SI(G4=”Febrero”;INDICE(A5:D8;COINCIDIR(MAX(C5:C8);C5:C8;0);1);SI(G4=”Marzo”;INDICE(A5:D8;COINCIDIR(MAX(D5:D8);D5:D8;0);1);FALSO)))
Las comillas,son en la fórmula, tal y como tu dices es un funcion de texto.
A lo que me refiero es que por ejemplo en el “”” enero” de la formula las comillas no son iguales, cambialas por las qe admita tu version.
Hola a todos, veo que ya han agotado las opciones mas adecuadas, coloco otra opción que si bien se que no es la mejor, funciona.
=DESREF($A$4;COINCIDIR(MAX(SI(G4=B4;B5:B8;SI(G4=C4;C5:C8;D5:D8)));SI(G4=B4;B5:B8;SI(G4=C4;C5:C8;D5:D8));0);0;;)
Saludos
juanjg, ¿por qué tu solución no es la mejor?. Funciona perfectamente, así que es igual de válida que las demás.
Gracias.
Hola Buenas tardes,
He utilizado un proceso menos técnico y más rudimentario, (“como contar con los dedos”), pero también es efectivo y entendible.
He dividido la formula en dos partes:
1. En la fila 9, en los totalizadores de cada mes, he insertado la siguiente formula:
En B9: =INDICE($A$5:$A$8;COINCIDIR((MAX(B5:B8));B5:B8;0))
(Arrastrar a C9 y D9).
Esta fórmula, nos permite ver instantáneamente el producto más vendido en cada mes. Aunque opcionalmente, se puede ocultar la fila o utilizar una fuente transparente para ocultar estos datos, a gusto del usuario.
2. Y en la celda G6, he insertado una simple formula de BUSCARH, que nos encontrará también el producto más vendido en el mes seleccionado
=SI.ERROR(BUSCARH(G4;B4:D9;6;FALSO);””)
Fantástico, Ángel. Solución válida. Si quisiera ocultar los totales de las columnas, no utilizaría colores de fuente u ocultar la fila, sino introducir el formato de número personalizado “”. Así no se verá el valor.
Como puedo hacer si ademas de productos, tengo grupo de productos? Por ejemplo el producto 1 y 2 son del mismo grupo, 3 y 4 de otro grupo, y quiero saber el mas vendido por grupo? Gracias.
=INDICE(A:A,COINCIDIR(MAX(DESREF(A:A,0,COINCIDIR(G4,4:4,0)-1)),DESREF(A:A,0,COINCIDIR(G4,4:4,0)-1),0))
Hola, soy nuevo pero con muchas ganas de aprender, tengo el siguiente dilema.
Necesito tener un ranking de los 10 articulos mas vendidos, pero mi bd es totalmente diferente a lo expuesto, los encabezados de mi planilla tienen:
Empresa – Número – Fecha – Vendedor – Turno – Artículo – Descripción – Color -Descripción color – TLL – Cant. – % – Precio Total – %
para este caso cual seria la formula a ocupar? gracias!
Buen día, logré dar con la solución pero la fórmula me salió más larga, igual se las comparto:
=INDICE(C3:F7,SI(I2=D2,COINCIDIR(MAX(D3:D7),D3:D7,0),SI(I2=E2,COINCIDIR(MAX(E3:E7),E3:E7,0),COINCIDIR(MAX(F3:F7),F3:F7,0))),1)
Favor adecuarla al rango del archivo original
Hice está función que es muy sencilla pero parece estar funcionando bien
=SI(G4=”enero”,MAX(B5:B8),SI(G4=”febrero”,MAX(C5:C8),SI(G4=”marzo”,MAX(D5:D8),”no aplica”)))
Sé que si necesitariamos todo el año, pues habría que agregar SI’s y quedaria larga pero sencilla.
Saludos
=INDICE(ITEMS,COINCIDIR(MAX(INDIRECTO(G4)),Marzo,0))