Conclusión: Aprenda a calcular comisiones en Excel para un plan por niveles básico y una tabla de tarifas usando las funciones IF, VLOOKUP o XLOOKUP.
Nivel de habilidad: Intermedio
Video Tutorial
Calcular comisiones en Excel puede ser una tarea muy complicada. Esto es especialmente cierto si ha intentado usar múltiples declaraciones IF para calcular comisiones para cada nivel en una tabla de tarifas.
Este artículo explicará cómo usar la función VLOOKUP para hacer este proceso mucho más fácil. El secreto es establecer el último argumento en la búsqueda visual en TRUE, para encontrar la coincidencia más cercana.
Por favor, consulte mi artículo sobre Cómo Usar VLOOKUP para Encontrar la coincidencia Más Cercana para obtener una explicación detallada sobre cómo establecer el último argumento en VERDADERO.
Actualizar: La publicación se ha actualizado para incluir instrucciones para calcular comisiones con la nueva función XLOOKUP. Aquí hay un video de artículo & en XLOOKUP.
Descargar archivo
Descargar el archivo para seguir.
Cálculos de Comisiones-SI Vs VLOOKUP Vs XLOOKUP.xlsx (31.5 KB)
Cálculo de comisiones con VLOOKUP
Con un plan de comisiones simple, normalmente tiene una tabla de tarifas que enumera la tasa de pago en cada nivel de ventas. A medida que el representante de ventas realiza más ventas, su tasa de pago generalmente aumentará.
La tasa de pago podría ser una cantidad fija en dólares, porcentaje de ingresos, porcentaje de cuota, etc. El trabajo del VLOOKUP es encontrar el monto de ventas del representante en la tabla de tarifas y devolver la tasa de pago correspondiente.
Para este ejemplo, nuestro plan de comisiones tiene el siguiente aspecto:
- Rep vende $0-$50,000, ganan 5%
- Rep vende $51,000$100,000 dólares, ganan un 7%
- Rep vende $100,001-$150,000, ganan 10%
- Rep vende más de $150,001, ellos ganan 15%
La rep hacer una cierta cantidad de ventas durante el mes. Él / ella ganará una cierta tasa de pago dependiendo del nivel de ventas alcanzado.
Podemos usar una fórmula de VLOOKUP para calcular la tasa de pago para un monto de ventas determinado (valor de búsqueda). Para que esto funcione, necesitamos establecer el último argumento en la búsqueda visual en TRUE.
Con el último argumento establecido en TRUE, vlookup encontrará la coincidencia más cercana al valor de búsqueda que sea menor o igual a la cantidad de búsqueda. Esto básicamente nos permite encontrar un valor entre rangos de dos números (niveles).
Esta es la razón por la que el último argumento en BUSCARV se denomina ordenado. Cuando este argumento es TRUE, VLOOKUP busca entre rangos de valores en la columna mínimo de niveles para encontrar una coincidencia exacta o un valor menor que el valor de búsqueda.
Cómo configurar la Tabla de tarifas
Al colocar esta tabla de tarifas en Excel, solo necesita enumerar el mínimo de niveles para el rango de búsqueda. De nuevo, la VLOOKUP buscará una «coincidencia más cercana» que sea menor o igual al valor de búsqueda. Si encuentra un valor que es mayor que el valor de búsqueda, devolverá la fila anterior.
En este ejemplo, para una tabla de tarifas de comisiones, la primera fila del rango de búsqueda debe ser cero. Esto se debe a que el representante de ventas podría tener ventas de 0 0 y el valor de búsqueda sería cero. Si el valor de búsqueda (cantidad de ventas) fuera un número negativo, la vlookup devolvería un error.
Es importante saber esto y configurar la tabla de tarifas para todos los posibles valores de búsqueda.
Si el monto de las ventas es mayor que la última fila en el rango de búsqueda, la vlookup devolverá la última fila. Por ejemplo, si el representante hizo ventas de $175,000, entonces vlookup devolvería el 15%.
Calcular Comisiones para Devolver un Valor en dólares
El pago también se puede devolver como un valor en dólares, en lugar de un porcentaje. Con esta configuración, el pago será una tarifa plana.
Esto significa que el pago será el mismo, independientemente del monto de las ventas dentro del nivel. En el siguiente ejemplo, si el pago será de $1,000 si la cantidad de ventas es de $55.000 o $95,000.
El pago NO está en una escala móvil. Es una tarifa plana para cada nivel.
Si está buscando un cálculo de escala móvil, consulte mi artículo sobre cálculo de comisiones con una estructura de tarifas por niveles utilizando SUMPRODUCT.
Cálculos de comisiones con XLOOKUP
También podemos utilizar la nueva función XLOOKUP para este cálculo. Funciona muy similar a VLOOKUP al encontrar la coincidencia más cercana.
Ventajas con XLOOKUP
Hay dos diferencias y ventajas principales con XLOOKUP:
- Con XLOOKUP especificamos la matriz de búsqueda y la matriz de retorno como rangos separados. Esto es B4: B7 y D4: D7 en la imagen de arriba. Esta es una ventaja sobre VLOOKUP porque podemos insertar o eliminar filas entre las columnas B y D, y la fórmula seguirá funcionando.
Cuando agregamos / eliminamos columnas dentro de la matriz de tabla de VLOOKUP, tenemos que cambiar manualmente el número de índice de la columna o usar una fórmula para calcularlo dinámicamente. - XLOOKUP NO requiere que los datos se ordenen cuando se usa Coincidencia exacta o el siguiente elemento más pequeño/más grande para el modo coincidencia. XLOOKUP buscará el siguiente elemento más pequeño / más grande y devolverá el resultado de esa fila / columna.
VLOOKUP requiere que los datos se ordenen cuando el último argumento es VERDADERO. Por lo general, querrá ordenar sus datos para este tipo de tablas de todos modos, pero es bueno saber que puede poner nuevos niveles de tasa en la parte inferior de la tabla y XLOOKUP funcionará.
Inconveniente de XLOOKUP
La principal desventaja de XLOOKUP es la compatibilidad. Tanto usted como todos los usuarios de su archivo deben estar en una versión de Microsoft/Office 365 que tenga XLOOKUP. NO es compatible con versiones anteriores ni está disponible en versiones anteriores de Excel.
Por lo tanto, VLOOKUP funciona muy bien en este escenario y es mucho más fácil de escribir que una fórmula IF anidada.
Evitar fórmulas IF anidadas
Un enfoque común para calcular comisiones es usar sentencias IF. Con una tabla de tarifas como esta, tendría que escribir varias declaraciones IF. Básicamente tienes que escribir una instrucción IF para cada nivel (fila) de la tabla.
Luego tiene que unir todas las declaraciones IF en una fórmula larga y fea. Se denominan sentencias IF anidadas.
Siempre trato de evitar IFS anidados cuando es posible porque son difíciles de leer y entender, y pueden ser más lentos para que Excel los calcule. Si tiene miles de fórmulas IF anidadas en su libro de trabajo, el tiempo de cálculo podría ralentizarse.
La fórmula FI es mucho más difícil de mantener. Requiere modificación cuando se agregan/eliminan filas de la tabla. VLOOKUP y XLOOKUP NO requieren este tipo de mantenimiento.
Si utiliza IF, asegúrese de hacer que cada referencia de celda a la tabla de tasas sea una referencia absoluta (F4 en el teclado). De lo contrario, obtendrá resultados incorrectos al copiar la fórmula hacia abajo.
Usar VLOOKUP es mucho más fácil y limpio que usar fórmulas IF anidadas. Como puedes ver en este ejemplo, el vlookup te permite usar una fórmula para calcular la tasa de pago de comisiones para cualquier cantidad de ventas dada.
Recursos adicionales
- Cómo Usar VLOOKUP para Encontrar el Último Argumento de Coincidencia Más Cercano Es IGUAL a VERDADERO
- Cómo Calcular Comisiones Acumuladas por Niveles con SUMPRODUCT
- VLOOKUP Explicado en Términos Simples en Starbucks
- VLOOKUP & 2266>
- La Nueva Función XLOOKUP para Excel + Tutorial de video
- Función IF Explicada: Cómo Escribir una Fórmula de Instrucción IF en Excel
¿Cómo Se Usa VLOOKUP con la Coincidencia Más cercana?
La VLOOKUP con la técnica de concordancia más cercana también se puede usar para cálculos de tramos de impuestos, coincidencia de precios, etc. ¿Para qué se utiliza esta técnica para calcular?