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)
Descargar ejemplo de formula para buscar mejor vendedor