Estoy acostumbrado a trabajar con BUSCARV pero esta vez tengo un desafío.No quiero el primer valor coincidente, pero elúltimo.¿Cómo? (Estoy trabajando con LibreOffice Calc pero una solución de MS Excel debería ser igualmente útil).
La razón es que tengo dos columnas de texto con miles de filas, digamos que una es una lista de beneficiarios de transacciones (Amazon, Ebay, empleador, tienda de comestibles, etc.) y la otra es una lista de categorías de gastos (salarios, impuestos, hogar, alquiler, etc.). Algunas transacciones no tienen la misma categoría de gasto siempre y quiero elegir la utilizada más recientemente. Tenga en cuenta que la lista no está ordenada por ninguna columna (de hecho, por fecha) y no quiero cambiar el orden de clasificación.
Lo que tengo (excluyendo el manejo de errores) es la fórmula habitual de "primera coincidencia":
=VLOOKUP(
[payee field] , [payee+category range] , [index of category column] ,
0 )
He vistosolucionesasí, pero me sale #DIV/0!errores:
=LOOKUP(2 , 1/( [payee range] = [search value] ) , [category range] )
La solución puede ser cualquier fórmula, no necesariamente BUSCARV. También puedo intercambiar las columnas de beneficiario/categoría. Simplemente no hay cambios en la columna de clasificación, por favor.
Puntos de bonificación por una solución que elija elmás frecuentevalor en lugar del último!
Respuesta1
Puede utilizar una fórmula matricial para obtener datos del último registro coincidente.
=INDEX(IF($A$1:$A$20="c",$B$1:$B$20),MAX(IF($A$1:$A$20="c",ROW($A$1:$A$20))))
Ingrese la fórmula usando Ctrl+ Shift+ Enter.
Esto funciona como la construcción INDEX/ de a , pero con un condicional usado en lugar de .MATCHVLOOKUPMAXMATCH
Tenga en cuenta que esto supone que su tabla comienza en la fila 1. Si sus datos comienzan en una fila diferente, deberá ajustar la ROW(...)parte restando la diferencia entre la fila superior y 1.
Respuesta2
(Respondiendo aquí como una pregunta no separada para datos ordenados).
Si los datoseranordenado, puede usarlo VLOOKUPcon el range_lookupargumento TRUE(u omitirlo, ya que es el valor predeterminado), que se describe oficialmente para Excel como "búsqueda de coincidencia aproximada".
En otras palabras, para datos ordenados:
- establecer el último argumento en
FALSEdevuelve elprimerovalor, y - establecer el último argumento en
TRUEdevuelve elúltimovalor.
Esto está en gran medida indocumentado y es oscuro, pero se remonta a VisiCalc (1979) y hoy se mantiene al menos en Microsoft Excel, LibreOffice Calc y Google Sheets. En última instancia, se debe a la implementación inicial de LOOKUPen VisiCalc (y de ahí VLOOKUPy HLOOKUP), cuando no existía un cuarto parámetro. El valor se encuentra porbúsqueda binaria, utilizando un límite izquierdo inclusivo y un límite derecho exclusivo (una implementación común y elegante), lo que da como resultado este comportamiento.
Técnicamente, esto significa que uno comienza la búsqueda con el intervalo candidato [0, n), donde nes la longitud de la matriz, y la condición invariante del bucle es que A[imin] <= key && key < A[imax](el límite izquierdo es <= el objetivo, el límite derecho, que comienza uno después del final, es > el objetivo; para validar, verifique los valores en los puntos finales antes o verifique el resultado después) y, sucesivamente, dividir y elegir el lado que conserve esta invariante: por exclusión, un lado lo hará, hasta llegar a un intervalo con 1 término, [k, k+1)y el El algoritmo luego regresa k. No es necesario que sea una coincidencia exacta (!): es solo la coincidencia más cercana desde abajo. En caso de coincidencias duplicadas, esto resulta en la devolución delúltimocoincidir, ya que requiere que el siguiente valor seamayor queque la clave (o el final de la matriz). En caso de duplicados es necesarioalgunocomportamiento, y esto es razonable y fácil de implementar.
Este comportamiento se indica explícitamente en este antiguo artículo de Microsoft Knowledge Base (énfasis agregado): "XL: Cómo devolver la primera o la última coincidencia en una matriz" (Q214069):
Puede utilizar la función LOOKUP() para buscar un valor dentro de una matriz de datos ordenados y devolver el valor correspondiente contenido en esa posición dentro de otra matriz. Si el valor de búsqueda se repite dentro de la matriz,devuelve la última coincidencia encontrada. Este comportamiento es válido para las funciones BUSCARV(), BUSCARH() y BUSCAR().
A continuación se presenta la documentación oficial de algunas hojas de cálculo; En ninguno de los dos se indica el comportamiento de "última coincidencia", pero está implícito en la documentación de Google Sheets:
-
VERDADEROasume que la primera columna de la tabla está ordenada numérica o alfabéticamente y luego buscaráel valor más cercano.
-
Si
is_sortedestáTRUEo se omite,el partido más cercano(menor o iguala la clave de búsqueda) se devuelve
Respuesta3
Si los valores en la matriz de búsqueda son secuenciales (es decir, está buscando el valor más grande, como la última fecha), ni siquiera necesita usar la función INDIRECTA. Pruebe este código simple:
=MAX(IF($A$1:$A$20="c",$B$1:$B$20,)
Nuevamente, ingrese la fórmula usando CTRL + SHIFT + ENTER
Respuesta4
Probé el valor más frecuente. No estoy seguro si funcionaría en libreOffice, pero parece funcionar en Excel.
=ÍNDICE($B$2:$B$9,COINCIDIR(MAX(--($A$2:$A$9=D2)*CONTAR.SI($B$2:$B$9,$B$2:$B$9,$A$2 :$A$9,D2)),--($A$2:$A$9=D2)*CONTAR.SI($B$2:$B$9,$B$2:$B$9,$A$2:$A$9,D2 ),0))
La columna A sería el beneficiario, la columna B sería la categoría, D2 es el beneficiario por el que desea filtrar. No estoy seguro de por qué pone saltos de línea adicionales en la función anterior.
Mi función para encontrar la última celda sería la siguiente:
=INDIRECTO("B" & MAX(--($A$2:$A$9=D2)*FILA($A$2:$A$9)))
Indirecto me permite especificar la columna que quiero devolver y encontrar la fila directamente (por lo que no necesito restar el número de filas de encabezado).
Ambas funciones deben ingresarse usandoCtrl+mayús+entrar