Los Formatos Contables se pueden automatizar en gran medida y son de enorme ayuda en el trabajo diario. Si tienes un control de clientes en excel, seguramente te ayudaría una formula que busca al vendedor con mas ventas en excel. Evidentemente esto tendría que ser automatizado, es decir, conforme veamos agregando datos, el acumulado de ventas se actualiza y tambien el nombre del mejor y peor vendedor con la suma de sus ventas de cada uno.
Registro de ventas realizadas por cada vendedor.
Tenemos una base de datos de ventas registradas por vendedor, necesitamos obtener el nombre del vendedor que tiene mas ventas, cual es la formula excel para esto..?
Evidentemente, si obtenemos el acumulado de ventas de cada vendedor, ordenamos la tabla por la columna del total, ya podemos saber quien es el mejor y peor vendedor por el orden que se da, pero para fines educativos, veremos una forma en la que el nombre y total se obtiene automáticamente en celdas de cualquier hoja.
Opción de descarga al final del post
Reporte acumulado de vendedores
En base a este Registro obtenemos un acumulado por cada vendedor por mes y año, esto lo podemos hacer con la función SUMAPRODUCTO
=SUMAPRODUCTO((Vendedor=A2)*(Meses=miMes)*(Años=miAño)*Importe)
Vendedor: Hace referencia a la columna donde están los nombres de los vendedores en la hoja “Ventas“
A2: Hace referencia a la celda donde esta el nombre del vendedor en la hoja “Concentrado“
Meses: Es una matriz con los nombres de mes en base a la columna “Fecha” de la hoja “Ventas”, para obtener esta matriz se agrega un nuevo nombre de rango desde el “Administrador de Rangos” en el menú “Formulas”, en la opción “Se refiere a:” se pone la siguiente formula =NOMPROPIO(TEXTO(Fecha,"mmmm")) , esta formula es la que devuelve la matriz.
miMes: Hace referencia a la celda donde se pone el mes del que se quiere obtener el mejor y peor vendedor, esa celda esta en la hoja “Reporte“
Años: Es una matriz con los numero de año de cada fecha en la hoja de “Ventas“, esta matriz al igual que la de meses, se obtiene agregando un nombre de rango como en el caso anterior, la formula para esto es =AÑO(Fecha)
miAño: Hace referencia a la celda donde se pone el año del que se quiere obtener el mejor y peor vendedor, esa celda esta en la hoja “Reporte“
Importe: Hace referencia a la columna donde están los importes de las ventas en la hoja “Ventas“
Obteniendo el nombre del mejor y peor vendedor
En esta hoja se puede seleccionar indicar el año y seleccionar el mes del que se desea obtener el mejor y peor vendedor.
Cuando se seleccionan esos datos, suceden dos cosas, por un lado se actualiza la tabla de la hoja “Concentrado” y ademas se obtiene los nombres de mejor y peor vendedor con la suma de sus ventas.
Formula para obtener el nombre del mejor vendedor
=INDICE(Nombre_Vendedor,COINCIDIR(MAX(Ventas),Ventas,0))
Suma de ventas del mejor vendedor
=BUSCARV(B2,miData,2,FALSO)
Obtener nombre del peor vendedor
Si pretendemos Obtener el nombre del vendedor con menos ventas, solo cambiamos MAX por MIN
=INDICE(Nombre_Vendedor,COINCIDIR(MIN(Ventas),Ventas,0))
Suma de ventas del peor vendedor
La formula es igual que para el mejor vendedor puesto que solo se busca el nombre en la tabla y se devuelve el dato de la columna de ventas.
=BUSCARV(C2,miData,2,FALSO)
Consideraciones
Como se puede observar en la formula, estoy usando nombres de rangos definidos, dichos rangos son dinámicos, esto facilita mucho el proceso de elaboración de formulas porque no es necesario ir a la hoja para indicar el rango que se usara ni tampoco tienes que re-ajustar los rangos cada vez que se agregan nuevos datos.
Adicionalmente, en algunos nombres de rango se usan formulas que devuelven una matriz de datos, estos nombres luego se usan dentro de la formula, el beneficio de esto es que la formula queda mas corta y se entiende mejor lo que esta haciendo, ejemplo: =SUMAPRODUCTO((Vendedor=A2)*(Meses=miMes)*(Años=miAño)*Importe) , si no usara nombres de rango, la formula quedaría así: =SUMAPRODUCTO((Vendedor=A2)*(NOMPROPIO(TEXTO(Fecha,"mmmm"))=miMes)*(AÑO(Fecha)=miAño)*Importe)
César MR dice
Disculpa, en el comentario anterior tendría que decir:
=SUMAR.SI(Vendedor,A2,Importe)
Insisto, gracias por el Blog!!!
César MR dice
Por motivos didácticos entiendo el uso de
SUMAR.SI.CONJUNTO(Importe,Vendedor,A2)
Para este caso llegaría
SUMAR.SI(Importe,Vendedor,A2)
Puesto que no tiene más que una condición.
Felicitaciones por el blog.
Un saludo desde Coruña/España
Nolberto Lopez dice
Gracias Cesar.
Este post es de 2009, estoy actualizando precisamente artículos viejos.
La idea inicial era obtener un concentrado basado en el mes de las ventas, esto con el fin de obtener el mejor y peor vendedor de x mes, incluso por año.
Debido a un contratiempo no termine la actualización en lo referente a ese punto especifico y el post ya se había re-publicado, pero en unos minutos subiré el archivo donde hay una lista desplegable para indicar el mes y con SUMAR.SI.CONJUNTO obtener la suma para cada vendedor.
Nolberto Lopez dice
Bueno, finalmente fue necesario cambiar de función para completar el caso como lo había visualizado de inicio, es decir, que se obtenga el mejor y peor vendedor en base al mes y año de las ventas.
La función usada es SUMAPRODUCTO en lugar de SUMAR.SI.CONJUNTO
La razón de esto es que SUMAR.SI.CONJUNTO no funciona con matrices sino con rangos, de hecho esta es una de las razones por las que suelo usar mas SUMAPRODUCTO ( esta puede manejar tanto rangos como matrices ).
En este caso particular se requiere generar matrices para obtener los meses y años de acuerdo a la fecha en cada registro de ventas, la otra opción seria usar columnas adicionales para año y mes en la tabla de ventas, pero no es buena idea usr formulas en las hojas de registro, esto es principalmente porque dichas hojas suelen ser muy grandes y al tener formulas el archivo se puede volver enorme.
He actualizado el archivo y el post para reflejar lo antes indicado.
César MR dice
Acabo de ver la actualización y estoy de acuerdo en que SUMAPRODUCTO es la función que necesita este supuesto.
El único comentario, por decir algo, es que en lugar de:
NOMPROPIO(TEXTO(Fecha;”mmmm”))
yo usaría:
MAYUSC(TEXTO(Fecha;”mmmm”))
Muy buena contribución para iniciarse en cálculo matricial con excel.
Saludos desde Coruña/España
victor bravo dice
Buenos dias, si tengo los siguiente: un archivo en el que en la columna “A tengo un rango de fechas, en la columna “h” unos valores asociados a la fecha a la columna A, deseo llevar a otra pestaña el valor mas bajo de un dia cualquier de la columna A. Ejemplo
Columna A Columna H en otra pestaña columna B columna J
7 /04/2019 40500 7/01/2019 2500
8/04/2019 82000
7/04/2019 2500
a la espera de su apoyo,
victor bravo dice
Columna A Columna H
7 /04/2019 40500
8/04/2019 82000
7/04/2019 2500
en otra pestaña
columna A columna H
7/04/2019 2500
carlos crisostomo raymundo dice
deseo el mismo ejemplo del mejor trabajador, pero usando o combinando la formula buscar.
luisa dice
Y como lo aria si el nombre esta en columnas
y el numero en fila y no lo puedo cambiar?
ejemplo
juan pedro luis nombre de jugador con mas goles
2 5 1 ?
AIDIL dice
muy buena, pero ahora quiero una formula que me permita conocer los nombre de los vendedores que sean igual o mayor de cierta cantidad esipulada
alejandro dice
y como es? para el caso de obtener el producto mas vendido de un mes aplicando esta formula para un rango de fechas del formato “dd/mm/aaaa” que sea igual a X mes como octubre ya que si aplico un si a esta formula me tira error #valor en la condicion ejemplo: =si((mes(rangofechasventas)=10;INDICE(producto,COINCIDIR(MAX(cantdearticulos),cantdearticulos,0),1);””) dato:”rangofechasventas” es el rango de todas las ventas de diversos meses;”producto” es el rango de productos que se vendio de acuerdo a su fecha de venta y por ultimo “cantdearticulos” es el rango de la cantidad que se vendio para cada producto un abrazo ojala me puedan ayudar ale.
Anonymous dice
gRACIAS mE SIRVIO MUCHO, INTENTABA HACER UNA PRACTICA CON LA FUNCION BUSCAR PERO NO ME SALIA PERO CON ESTA FUNCION SI ME SALIO, mUCHAS GRACIAS
Anonymous dice
Hola muy buenos dias, me parece muy interesante esta pagina es de mucha ayuda, pero desearia que me den una ayudita en algo que no entiendo tengo una lista de 20 articulos delas cuales tienen diferentes codigos poor ejemplo para el nombre zandilla tiene el codigo 10 con un monto, enn dicha lista se repiten tres beses el codigo quiciera que los montos de ese codigo me jale a una nnueva ventana, que jale solo en nombre zandilla y con los tres montos espero me comprendan espero su respuesta a mis dudas escribamen a mi correo [email protected]
Nolberto lopez dice
puede usar la funcion k.esimo.mayor
envíeme un mensaje desde el área de contacto de este sitio… le enviare un ejemplo.
Anonymous dice
Buenas a todos,
Justo me he encontrado con este ejemplo que es igualito al que me he encontrado en el trabajo, aunque con un paso más.
Ahora gracias a esto puedo encontrar mi “vendedor con más ventas” pero, como puedo encontrar mi segundo, tercer y 4arto vendedor?
Quizás no está directamente relacionado con el tema de indices y coincidir, sinó más en el de máximos. Imagino que sigue estando relacionado con las matrices.
Con sólo coincidir puedo localizar la fila de la celda donde esta mi máximo, pero necesitaría poder localizar las celdas anteriores en orden descendente a este máximo.
En resumen, quiero saber mis X máximos de un conjunto de datos para aplicar lo aquí enseñado.
Nolberto lopez dice
en la sección de tutoriales hay un ejemplo de como hacer suma condicional… de esa forma se genera una tabla concentrada.
Anonymous dice
pero si las ventas son en varios dias y logicamente el nombre del vendedor se repite, como saber cual fue la cantidad maxima según el nombre??
Anonymous dice
myu bien si me funciono nadamas cambie las comas por punto y coma…..tengo officce 2007
lrn dice
ya quedo Luis…
en el archivo de la descarga esta correcto el rango..
te agradezco la observacion
Lluís Urpí dice
¿No deberia ser =INDICE(A1:B5; … y no A1:A5…?