36

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?

Reto Ayuda Excel

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.

Reto01

 

 

¡Compartir es vivir!
Sergio
 

La destreza y el perfeccionismo quizá sean las dos virtudes que me permiten ayudar a mis clientes a facilitar las tareas administrativas de sus negocios.

Haz clic aquí para dejar un comentario 36 comentarios
Silvia - 16/11/2015

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)),))

Responder
    Sergio - 16/11/2015

    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.

    Responder
Gerson Pineda - 16/11/2015

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),),)

Responder
    Sergio - 16/11/2015

    Gerson, gracias por tu respuesta. Todavía no he tenido oportunidad de probarlas pero en un rato lo haré.

    Responder
Gerson Pineda - 16/11/2015

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)),),)

Responder
John Jairo - 16/11/2015

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!

Responder
Sergio - 16/11/2015

Muy bien, John!! Nunca me imaginaría que las funciones BUSCAR y FRECUENCIA podrían estar en la misma fórmula….

Responder
jaime cruz - 17/11/2015

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)

Responder
    Sergio - 17/11/2015

    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.

    Responder
Juan - 17/11/2015

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

Responder
Juan - 17/11/2015

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

Responder
pedrowave - 17/11/2015

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?

Responder
Sergio - 17/11/2015

Yo no soy muy de funciones de base de datos, pero la solución funciona perfectamente.
Gracias, Pedro.

Responder
pedrowave - 17/11/2015

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…

Responder
DIONI - 18/11/2015

=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))))

Responder
DIONI - 18/11/2015

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.

Responder
    Sergio - 18/11/2015

    Dioni, he probado tu solución, pero no ha funcionado. Aparece el error #REF!

    Responder
cdelarosam - 18/11/2015

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)

Responder
    Sergio - 19/11/2015

    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

    Responder
    Sergio - 19/11/2015

    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))

    Responder
Sergio - 19/11/2015

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))

Responder
DIONI - 19/11/2015

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))))

Responder
    Sergio - 19/11/2015

    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…

    Responder
David Dávila - 19/11/2015

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)))

Responder
David Dávila - 19/11/2015

IF=SI | INDEX =INDICE | MATCH=COINCIDIR | MAX=MAX , Sludos y bendiciones a todos!!

Responder
    Sergio - 19/11/2015

    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)))

    Responder
dioni - 21/11/2015

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.

Responder
JUANJG - 21/11/2015

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

Responder
    Sergio - 22/11/2015

    juanjg, ¿por qué tu solución no es la mejor?. Funciona perfectamente, así que es igual de válida que las demás.

    Gracias.

    Responder
Angel - 22/11/2015

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);””)

Responder
    Sergio - 22/11/2015

    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.

    Responder
Martin - 13/06/2017

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.

Responder
Daniel - 13/10/2017

=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))

Responder
Angel - 24/02/2018

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!

Responder
Luis - 12/12/2018

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

Responder
Sandra - 05/06/2019

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

Responder

Escribe una respuesta:

Powered by WishList Member - Membership Software