Cómo usar XLOOKUP() y VLOOKUP() en Excel para calcular comisiones basadas en el precio de compra
Cómo utilizar XLOOKUP() y VLOOKUP() en Excel para calcular comisiones
Es común realizar un seguimiento del progreso a través de puntos de referencia. Las comisiones por ventas son un buen ejemplo de este tipo de configuración. Específicamente, el porcentaje de comisión aumenta con el total de la compra. Por ejemplo, si el total está entre $1 y $299, la comisión es del 3%; si el total está entre $300 y $499, la comisión es del 4%, y así sucesivamente. En este artículo, te mostraré cómo utilizar tanto XLOOKUP() como VLOOKUP() en Microsoft Excel para devolver la comisión apropiada según el precio total de la compra. Parece más difícil de lo que realmente es. En este caso, ninguna de las dos funciones demuestra ser superior a la otra.
Sobre XLOOKUP() en Excel
XLOOKUP() es una de las varias funciones de array dinámico relativamente nuevas. Si alguna vez has ingresado una expresión usando Ctrl + Shift + Enter, ya estás familiarizado con cómo Excel solía funcionar con arrays dinámicos. Gracias a la nueva función de array dinámico, este tipo de expresiones son mucho más fáciles de crear y mantener, ya que puedes ingresar la expresión como lo harías normalmente, presionando solo Enter. Los resultados se "derraman" en las celdas de abajo, llenando tantas como sea necesario para completar los cálculos de la expresión. Eso se llama "rango derramado". Si ves un error de derrame, significa que el rango necesario para cumplir con la función no está disponible. Esto significa que puedes usar una función para devolver múltiples columnas (o filas) de valores resultantes.
Para obtener más información sobre la sintaxis y los beneficios de esta función relativamente nueva, lee Cómo utilizar la función de array dinámico XLOOKUP() en Excel.
El problema
A veces, XLOOKUP() tiene ventajas obvias sobre VLOOKUP(), pero no siempre. Supongamos que deseas devolver un balance continuo de las comisiones adeudadas y que el porcentaje de comisión depende del monto total de la compra. Probablemente te hayas encontrado con situaciones en las que el porcentaje de comisión es un monto fijo, pero en este caso, el porcentaje depende del valor de la venta. Al trabajar en los requisitos, podrías pensar que se requieren ambas valores en la tabla de búsqueda de comisiones: "si la compra está por encima de este valor pero por debajo de este otro, usa x porcentaje". Esa suposición podría hacer que la solución sea más difícil de lograr de lo necesario, pero no porque realmente sea así. Con cualquiera de las funciones de búsqueda, solo necesitas buscar un valor: el valor bajo o alto, pero no ambos.
El nuevo Microsoft Store: una nueva era para las aplicaciones en WindowsEl simple conjunto de datos mostrado en Figura A tiene columnas vacías para el porcentaje de comisión, el monto de la comisión y el total acumulado de comisiones ganadas. La tabla de búsqueda a la derecha almacena los puntos de referencia y los porcentajes para los grupos de montos de compra. Comencemos con una solución utilizando XLOOKUP(). La tabla de búsqueda a la derecha expresa los grupos de comisiones. Como puedes ver, el porcentaje aumenta a medida que los precios de compra aumentan (creando grupos de límites bajos y altos).
Figura A
Cómo utilizar XLOOKUP() para calcular comisiones en Excel
Revisemos los requisitos de comisión utilizando datos reales esta vez. El primer valor de venta es de $1,208. La tabla de búsqueda a la derecha muestra que $1,208 se encuentra en el nivel de comisión del 20% (inferior a $1,499 pero superior a $1,000).
Revisemos la tabla de búsqueda antes de continuar. Observarás que los valores bajos y altos fluyen consecutivamente desde el nivel anterior hacia el siguiente nivel, y lo hacen de manera consistente. Esta configuración es importante para obtener resultados correctos; ningún valor se omite. Podrías intercambiar las cosas y aún funcionaría. Por ejemplo, si el valor alto del primer nivel fuera de $400, el valor bajo en el siguiente nivel sería de $401.
Mencionemos brevemente la sintaxis de XLOOKUP():
Tsurugi Linux: La distribución de Linux especializada en Forensics e Incident Response=XLOOKUP(valor_buscado, matriz_búsqueda, matriz_devolución, [si_no_se_encuentra], [modo_coincidencia], [modo_búsqueda])
El único argumento opcional que usaremos es [modo_coincidencia], pero primero, tracemos los argumentos requeridos:
- valor_buscado son los valores de compra en la columna C.
- matriz_búsqueda es los datos de búsqueda o origen, que es J2:L7: la tabla de búsqueda a la derecha.
- matriz_devolución son los valores de porcentaje que deseas devolver, que se encuentran en la columna J.
Como los valores de compra en la columna C no están ordenados, usaremos [modo_coincidencia] para especificar el tipo de coincidencia. El valor predeterminado es 0 para "encontrar una coincidencia exacta". En su lugar, usaremos -1, que es "encontrar una coincidencia exacta; devolver el elemento más pequeño siguiente si no se encuentra coincidencia". También podrías preguntarte cómo una función de búsqueda puede devolver los valores de porcentaje cuando están a la izquierda de las columnas de límite bajo y alto. Esa es una de las nuevas mejoras de la función XLOOKUP(): puedes hacer referencia a columnas a la izquierda de la columna de búsqueda. Ahora, pongámonos a trabajar.
Primero, ingresa la función
=XLOOKUP(C3,$K$2:$K$7,$J$2:$J$7,,-1)
en la celda F3 y copia a las celdas restantes en esa columna. Observa que las dos referencias de matriz son absolutas; eso es importante. Si conviertes la tabla de búsqueda en un objeto de tabla, tus referencias se verán algo así:
macOS Monterey: Nuevas funciones y características del sistema operativo de Apple=XLOOKUP(C3,Table2[[#All],[Low]],Table2[[#All],[Per]],,-1)
Las referencias #ALL se pueden eliminar:
=XLOOKUP(C3,Table2[Low],Table2[Per]],,-1)
Como puedes ver en Figura B, esta función devuelve el porcentaje de comisión apropiado de la tabla de búsqueda a la derecha.
Figura B
Cómo volver a Windows 10 desde Windows 11: Guía de recuperación y solución de problemasLa función XLOOKUP() devuelve los porcentajes de comisión de la columna J en función de los valores de compra de la columna C. Una vez que tienes estos valores, el resto de la hoja es pan comido:
- Ingresa la expresión =C3*F3 en G3 y copia en las celdas restantes en la columna G.
- Ingresa la expresión =G3 en H3. De esta manera, se devolverá el primer valor de comisión en el conjunto de datos.
- Ingresa la expresión =H3+G4 en la celda H4. De esta manera, se sumará la primera comisión con la segunda. Copia esta sencilla expresión a las celdas restantes en la columna H para crear un total acumulado de comisiones.
La hoja completa se muestra en Figura C. Las columnas de comisión y total acumulado dependen del valor de porcentaje de comisión devuelto por XLOOKUP(). Podrías devolver valores de altos simplemente, pero solo necesitas una columna de búsqueda. Incluir ambos es excelente para la documentación o para compartir con los usuarios, pero no son necesarios para obtener resultados.
Figura C
A medida que los porcentajes de comisión cambian, puedes actualizar rápidamente los valores de referencia (los límites bajo, alto y porcentajes). No es necesario modificar las funciones y expresiones. Todo sigue funcionando.
Si aún no has actualizado a Microsoft 365, debes utilizar VLOOKUP(). La buena noticia es que no es más difícil. Si tienes Microsoft 365, te recomiendo utilizar XLOOKUP(), pero es poco probable que Microsoft deprecie las funciones de búsqueda anteriores en un futuro próximo. Ahora, veamos cómo funciona VLOOKUP() con esta configuración.
¡Sorprendente! Linux kernel 5.15 trae una sorpresa especial para los usuarios de NTFSCómo utilizar VLOOKUP() en Excel
Probablemente estés familiarizado con VLOOKUP(). No puede manejar un valor de resultado que se encuentra a la izquierda del valor de búsqueda, pero esa es la disposición que tenemos en la tabla de búsqueda. (Hice eso a propósito para enfatizar una gran diferencia entre las dos funciones.) Usando VLOOKUP(), debes mover los valores de porcentaje a la derecha de los valores de límite bajo y alto, como se muestra en Figura D.
Figura D
La sintaxis de la función VLOOKUP() es diferente a XLOOKUP(), pero el resultado será el mismo. Ingresa la siguiente función en F3 y copia en las celdas restantes:
=VLOOKUP(C3,$J$2:$L$7,3,TRUE)
Como puedes ver en Figura E, la función devuelve los mismos valores de porcentaje. Las fórmulas para las columnas G y H son las mismas que antes:
Cómo activar la aplicación Mouse utilities en Windows 10 PowerToys- G3: =C3*F3
- H3: =G3
- H4: =H3+G4
Figura E
La hoja completa se muestra en Figura F. El argumento TRUE encuentra la coincidencia más cercana, lo que significa que no tenemos que ordenar el conjunto de datos. Además, no necesitamos tanto los valores bajos como los altos para obtener resultados.
Figura F
En este caso, no hay una ventaja real de utilizar XLOOKUP() aparte de no tener que reorganizar la tabla de búsqueda, lo cual podría ser importante. En el futuro, te recomiendo que comiences a utilizar XLOOKUP(), pero no te preocupes por cambiar las hojas existentes.
Cómo implementar Wekan en tu centro de datos para una gestión eficiente de proyectosEn Newsmatic nos especializamos en tecnología de vanguardia, contamos con los artículos mas novedosos sobre Software, allí encontraras muchos artículos similares a Cómo usar XLOOKUP() y VLOOKUP() en Excel para calcular comisiones basadas en el precio de compra , tenemos lo ultimo en tecnología 2023.
Artículos Relacionados