BUSCARV en modo aproximado para clasificar clientes según su facturación,
facilitando la toma de decisiones en planes de descuento, priorización y recursos.📐 Funciones de Excel utilizadas
🔍 BUSCARV (modo aproximado)
Sintaxis: =BUSCARV(valor_buscado; tabla_matriz; indicador_columnas; [ordenado])
- valor_buscado: el valor que queremos buscar (ej. la venta de un cliente).
- tabla_matriz: rango que contiene la tabla de referencia (dos columnas: límite inferior y categoría).
- indicador_columnas: número de columna de la tabla matriz de la cual queremos el resultado (2 para categoría).
- [ordenado]:
VERDADERO(o 1) para modo aproximado. La tabla debe estar ordenada de menor a mayor por la primera columna.
¿Cómo funciona el modo aproximado? Busca el valor más grande menor o igual al valor buscado. Es ideal para rangos (ej. de 0 a 1000 → «Bajo», de 1001 a 5000 → «Medio», etc.). Requiere que la tabla auxiliar esté ordenada ascendentemente.
⚠️ SI.ERROR (para variante)
Sintaxis: =SI.ERROR(valor; valor_si_error)
Permite capturar errores (como #N/A, #VALOR, etc.) y devolver un mensaje personalizado o un valor alternativo. Lo usaremos para gestionar casos donde el valor buscado no esté dentro de los rangos definidos.
📋 Datos de ejemplo
Tabla de clientes (rango A1:B11 en Excel):
| Cliente | Venta (€) |
|---|---|
| Empresa A | 500 |
| Empresa B | 1.200 |
| Empresa C | 3.000 |
| Empresa D | 7.500 |
| Empresa E | 15.000 |
| Empresa F | 800 |
| Empresa G | 4.500 |
| Empresa H | 20.000 |
| Empresa I | 6.000 |
| Empresa J | 2.500 |
Tabla auxiliar de rangos (rango D1:E5 en Excel):
| Límite inferior | Categoría |
|---|---|
| 0 | Bajo |
| 1.001 | Medio |
| 5.001 | Alto |
| 10.001 | Premium |
📝 Desarrollo paso a paso
Paso 1: Ubicación de los datos
- Clientes y ventas: rango
A1:B11(fila 1 encabezados, datos desde A2:B11). - Tabla auxiliar: rango
D1:E5(encabezados en D1:E1, datos en D2:E5). - Vamos a agregar una columna Categoría en
C2:C11usando BUSCARV.
Paso 2: Fórmula para asignar categoría
En la celda C2 escribimos la siguiente fórmula y la arrastramos hasta C11:
=BUSCARV(B2; $D$2:$E$5; 2; VERDADERO) B2es la venta del primer cliente.$D$2:$E$5es la tabla auxiliar fija (con referencias absolutas).2indica que queremos la segunda columna (Categoría).VERDADEROactiva el modo aproximado.
Paso 3: Resultados obtenidos
La tabla final, con la categoría asignada a cada cliente (celdas destacadas en verde):
| Cliente | Venta (€) | Categoría |
|---|---|---|
| Empresa A | 500 | Bajo |
| Empresa B | 1.200 | Medio |
| Empresa C | 3.000 | Medio |
| Empresa D | 7.500 | Alto |
| Empresa E | 15.000 | Premium |
| Empresa F | 800 | Bajo |
| Empresa G | 4.500 | Medio |
| Empresa H | 20.000 | Premium |
| Empresa I | 6.000 | Alto |
| Empresa J | 2.500 | Medio |
✅ La tabla auxiliar está ordenada ascendentemente (0, 1001, 5001, 10001). Para ventas de 500, el mayor menor o igual es 0 → «Bajo». Para 7500, el mayor menor o igual es 5001 → «Alto».
🔁 Variantes del ejercicio
Variante 1: Umbral dinámico modificable
Permitimos que el usuario cambie los límites de la tabla auxiliar desde celdas externas. Por ejemplo, queremos reclasificar «Alto» a partir de 7.000 € en lugar de 5.001 €.
En lugar de valores fijos en D2:E5, referenciamos celdas de entrada (ej. G2:G5).
=BUSCARV(B2; $G$2:$H$5; 2; VERDADERO) Nueva tabla auxiliar dinámica (celdas G2:H5):
| Límite | Categoría |
|---|---|
| 0 | Bajo |
| 1.001 | Medio |
| 7.000 | Alto |
| 10.001 | Premium |
Al cambiar el límite de «Alto» de 5001 a 7000, las ventas entre 5001 y 6999 pasarán a ser «Medio». Ejemplo: Empresa I (6.000 €) ahora es Medio (antes Alto).
| Cliente | Venta | Categoría (dinámica) |
|---|---|---|
| Empresa I | 6.000 | Medio |
| Empresa D | 7.500 | Alto |
📌 Esta variante muestra cómo modificar umbrales sin reescribir la fórmula, simplemente actualizando la tabla auxiliar.
Variante 2: Manejo de errores con SI.ERROR
Si un valor de venta es negativo o no numérico, BUSCARV puede devolver #N/A.
Usamos SI.ERROR para mostrar un mensaje amigable.
Supongamos que el cliente «Empresa K» tiene una venta de -200 (error en datos).
=SI.ERROR(BUSCARV(B12; $D$2:$E$5; 2; VERDADERO); "Sin categoría") Resultado:
| Cliente | Venta | Categoría (con SI.ERROR) |
|---|---|---|
| Empresa K | -200 | Sin categoría |
⚠️ También podemos usar SI.ERROR para valores mayores que el último límite (aunque en modo aproximado devolverá la última categoría, pero si la tabla estuviera incompleta, capturaría el error).
✏️ Ejercicio propuesto
Segmentación por rentabilidad (margen sobre ventas)
Dispones de una lista de productos con su coste y precio de venta. Calcula el margen (%) = (precio – coste) / precio * 100. Luego, crea una tabla auxiliar con los siguientes rangos de margen y asigna una categoría de rentabilidad:
- < 10% → "Bajo"
- 10% – 25% → «Medio»
- 25% – 45% → «Alto»
- > 45% → «Premium»
Instrucciones:
- Construye una tabla con al menos 8 productos (nombre, coste, precio).
- Calcula el margen en una columna auxiliar.
- Usa
BUSCARV(modo aproximado) para asignar la categoría de rentabilidad. - Incluye una variante con
SI.ERRORpara manejar posibles divisiones entre cero o valores atípicos. - Prueba a cambiar los umbrales de la tabla auxiliar y observa cómo se reclasifican los productos.
💡 Pista: Ordena la tabla auxiliar de menor a mayor según el límite inferior del margen.
Recuerda que BUSCARV aproximado busca el mayor valor ≤ al buscado.
Ejercicio práctico de Excel para Ingeniería Industrial · Segmentación con BUSCARV
Deja una respuesta