Cómo usar BUSCAR para encontrar el último valor de inventario
Como analista de datos e ingeniero industrial, la gestión precisa del inventario es crítica. A menudo, necesitamos saber el estado más reciente de un producto, es decir, el último registro de movimiento o cantidad disponible. Este instructivo técnico te guiará para resolver este desafío utilizando las capacidades avanzadas de búsqueda en Excel.
Definición del problema
En el contexto de la gestión de almacenes de una empresa de manufactura de componentes electrónicos, mantenemos un registro diario de las entradas y salidas de materias primas. Tenemos una base de datos transaccional donde cada fila representa un movimiento (entrada, salida, ajuste) de un SKU específico (Stock Keeping Unit). El reto operativo es el siguiente: si necesitamos saber la cantidad actual de un componente específico (ej. «Resistor 10k»), no podemos simplemente tomar el último valor de la columna de cantidad, ya que los registros pueden estar desordenados cronológicamente o pueden existir múltiples movimientos para el mismo producto en el mismo día. Necesitamos identificar el registro más reciente (basado en la fecha) para ese producto y extraer su valor de inventario asociado.
Explicación técnica
Para resolver este problema de «último valor basado en una condición», la función simple BUSCARV (VLOOKUP) es insuficiente, ya que solo busca coincidencias exactas y no maneja la lógica de «máximo valor de fecha». La solución más robusta en Excel moderno (Microsoft 365) implica una combinación de funciones dinámicas como FILTRAR junto con MAX o, en versiones anteriores, una combinación matricial compleja de INDICE, COINCIDIR y MAX. Dado que el objetivo es encontrar el *último* valor, la estrategia se centra en:
- Identificar la fecha máxima registrada para el producto deseado.
- Usar esa fecha máxima como criterio de búsqueda para obtener el valor de inventario correspondiente.
Utilizaremos la función MAXIFS (o una combinación de MAX y SI en versiones antiguas) para encontrar la fecha más reciente para un SKU específico, y luego INDICE/COINCIDIR para recuperar el valor asociado a esa fecha.
Guía paso a paso
Paso 1: Configuración de la Base de Datos de Inventario
Primero, debemos estructurar nuestros datos de manera limpia. Asumiremos que tenemos las siguientes columnas: ID Transacción, SKU del Producto, Fecha del Movimiento, y Cantidad en Stock (o cambio de stock).
Acción: Ingresa los datos de ejemplo en la hoja de cálculo, comenzando en la celda A1.
Por qué: Esto crea el conjunto de datos transaccional sobre el cual operaremos.
Resultado: Una tabla de datos organizada cronológicamente y por producto.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | ID Transacción | SKU Producto | Fecha Movimiento | Cantidad Stock |
| 2 | T001 | R10K | 01/03/2024 | 1000 |
| 3 | T002 | C500 | 01/03/2024 | 5000 |
| 4 | T003 | R10K | 05/03/2024 | 1200 |
| 5 | T004 | R10K | 10/03/2024 | 1150 |
| 6 | T005 | C500 | 15/03/2024 | 5500 |
| 7 | T006 | R10K | 15/03/2024 | 1300 |
Paso 2: Determinar la Fecha Más Reciente para el SKU Objetivo
Supongamos que queremos encontrar el último valor de inventario para el SKU R10K. Necesitamos encontrar la fecha más tardía asociada a este SKU.
Acción: En una celda auxiliar (ej. E2), escribe la siguiente fórmula (asumiendo que el SKU objetivo «R10K» está en D1): =MAXIFS(C:C, B:B, D1). Si usas una versión antigua de Excel, deberás usar una fórmula matricial compleja.
Por qué: MAXIFS permite encontrar el valor máximo (la fecha más tardía) en el rango C:C, siempre y cuando el rango B:B coincida con el criterio (el SKU deseado).
Resultado: La celda E2 mostrará la fecha 15/03/2024.
Paso 3: Encontrar la Fila Correspondiente a la Fecha Máxima
Ahora que tenemos la fecha más reciente (15/03/2024), necesitamos encontrar la fila exacta donde ocurrió ese evento para el SKU R10K.
Acción: Utilizaremos INDICE y COINCIDIR. En la celda F2, introduce la fórmula: =INDICE(D:D, COINCIDIR(E2, C:C, 0)). (Nota: Esta fórmula solo encuentra la primera coincidencia de la fecha máxima, lo cual es suficiente si la fecha máxima es única para ese SKU).
Por qué: COINCIDIR busca la posición de la fecha máxima (E2) dentro de la columna de fechas (C:C). INDICE devuelve el valor de la columna de fechas (D:D) en esa posición encontrada.
Resultado: La celda F2 mostrará la fecha 15/03/2024 (confirmando la posición).
Paso 4: Extraer el Último Valor de Inventario
Finalmente, usamos la posición encontrada en el Paso 3 para extraer el valor de la columna de Cantidad Stock (Columna D).
Acción: En la celda G2, escribe la fórmula final: =INDICE(D:D, COINCIDIR(E2, C:C, 0)). (Si la fecha máxima es única, esta fórmula funcionará. Si hay múltiples movimientos el mismo día, se debe refinar el criterio de búsqueda para incluir el ID de transacción o usar FILTRAR).
Por qué: Esta fórmula busca la posición de la fecha máxima (E2) en la columna de fechas (C:C) y devuelve el valor correspondiente de la columna de Cantidad Stock (D:D) en esa misma posición.
Resultado: La celda G2 mostrará el valor 1300, que es el último valor de inventario registrado para R10K.
| D1 | E1 | F1 | G1 | |
|---|---|---|---|---|
| 1 | R10K | Fecha Máx. | Posición | Último Stock |
| 2 | R10K | =MAXIFS(C:C, B:B, D1) | =INDICE(D:D, COINCIDIR(E2, C:C, 0)) | =INDICE(D:D, COINCIDIR(E2, C:C, 0)) |
| 2 (Resultado) | R10K | 15/03/2024 | 7 | 1300 |
Ejercicio propuesto
Escenario de Práctica: Utiliza la misma tabla de datos proporcionada en el Paso 1. Ahora, en lugar de buscar el SKU R10K, busca el SKU C500. Aplica la secuencia de pasos (Paso 2, Paso 3 y Paso 4) para determinar cuál fue el último valor de inventario registrado para C500. Documenta en tu hoja de cálculo los valores obtenidos en las celdas E2, F2 y G2 para este nuevo SKU.
Extensión Analítica: Si tuvieras una columna adicional de «Tipo de Movimiento» (Entrada/Salida), modifica la fórmula del Paso 2 para que solo considere movimientos de «Entrada» al calcular la fecha máxima, asegurando que el último registro sea una recepción de stock.
Errores habituales
Al implementar estas fórmulas complejas, es común tropezar con varios obstáculos. Aquí se detallan los tres más frecuentes:
1. Olvidar el formato de fecha
Error: Si la columna de fechas (Columna C) no está formateada como «Fecha» en Excel, la función MAXIFS tratará las fechas como texto, y la comparación fallará o devolverá un resultado incorrecto.
Solución: Selecciona toda la columna de fechas y aplica el formato de celda «Fecha Corta» o «Fecha Larga» antes de ejecutar cualquier fórmula.
2. Uso incorrecto de rangos absolutos/relativos
Error: Al arrastrar la fórmula de búsqueda a diferentes SKUs, si no se fijan correctamente los rangos de la base de datos (ej. $B:$B en lugar de B:B), la referencia se desplazará y buscará en datos incorrectos.
Solución: Siempre que la base de datos sea fija, utiliza referencias de columna completas (ej. C:C) o utiliza referencias absolutas (ej. $B$2:$B$100) para asegurar que el rango de búsqueda no cambie.
3. Confundir la posición de retorno en INDICE/COINCIDIR
Error: En la fórmula =INDICE(Columna_Resultado, COINCIDIR(Valor_Buscado, Columna_Busqueda, 0)), muchos usuarios invierten los argumentos. Si se coloca la columna de búsqueda como el primer argumento de INDICE, la fórmula devolverá la fecha en lugar de la cantidad.
Solución: Recuerda la estructura: INDICE debe apuntar a la columna que quieres *obtener* (Cantidad Stock), y COINCIDIR debe apuntar a la columna que estás *comparando* (Fecha).
Deja una respuesta