Tengo una hoja de cálculo que calcula el requisito de corriente constante de una batería. Quiero poder encontrar una batería adecuada a partir de la duración de carga establecida que tenga una corriente constante mayor o igual a mi valor calculado.
Estoy intentando usar una tabla de búsqueda donde mi "Duración de carga" (Amarillo) elige una columna de una matriz y luego mi función de búsqueda (Verde) encuentra un valor >= a mi valor de referencia calculado (Azul). También me gustaría otra función que encontrara el modelo de la misma fila que el valor encontrado de la matriz.
Duración de carga es un menú desplegable que coincide con las columnas correspondientes. Actualmente tengo una celda que elige la columna del array:
=MATCH(C39,'Battery Lookup Table'!B2:O2,0)
Probé varias funciones de búsqueda para encontrar los valores actuales constantes más cercanos:
=VLOOKUP(C46,'Battery Lookup Table'!B4:O36,MATCH(C39,'Battery Lookup Table'!B2:O2,0),TRUE)
=VLOOKUP(C46,'Battery Lookup Table'!B4:O36,N49)
=(HLOOKUP(C46,'Battery Lookup Table'!S56:AW69,O50,TRUE))
Cada una de estas tablas de búsqueda ha producido el mismo valor.
También intenté ordenar los valores de menor a mayor, ya que BUSCARV y BUSCARH tienen dificultades para descifrarlos. También intenté cambiar de fila durante el tiempo sin suerte.
Número de modelo:=INDEX('Battery Lookup Table'!A4:A36,MATCH(TRUE,'Battery Lookup Table'!M4:M25>=C46,0))
Actualmente, mi función de búsqueda irá a la columna correcta, pero la mayoría de las veces utilizará una fila/valor cerca de la parte inferior de la tabla. Ninguno de los valores encontrados fue el valor más cercano.
Respuesta1
VLOOKUPcompara un valor con elprimerocolumna de un rango y entrega el valor de una columna especificada del rango en la fila coincidente. Por tanto, su primera fórmula
=VLOOKUP(C46,'Battery Lookup Table'!B4:O36,MATCH(C39,'Battery Lookup Table'!B2:O2,0),TRUE)
coincide C46(que creo que es 500,8 en su ejemplo) con los valores en range 'Battery Lookup Table'!B4:B36. Este no es el rango con el que desea comparar, pero probablemente explica por qué obtiene resultados incorrectos.
De hecho, ya ha identificado la columna correcta que debe coincidir con su MATCHfunción. Puede utilizar la INDEXfunción para seleccionar parte de un rango. La expresion
INDEX('Battery Lookup Table'!B4:O36,,n)
proporciona elnorte'ésima columna de 'Battery Lookup Table'!B4:O36así poner su MATCHfunción en lugar denorteproporciona la columna correcta de valores con los que C46se pueden comparar. Al hacer esto se obtiene la expresión:
INDEX('Battery Lookup Table'!B4:O36,,MATCH(C39,'Battery Lookup Table'!B2:O2,0))
Para que las cosas sean fáciles de seguir, abreviaré esta expresión comolista.
Ahora quieres hacer 2 cosas:
- Encuentre el valor más pequeño enlistaque es mayor o igual a
C46 - Encuentra la batería que corresponde a este valor.
Aquí el truco no está en usar VLOOKUP()sino en su "primo" más flexible INDEX(MATCH()). Mientras que VLOOKUPcoincide con un valor en la primera columna de un rango y entrega un valor de una columna a la derecha, la versión más flexible permite que el valor provenga de la misma columna o de una a la izquierda.
Además, hay 3 formas posibles de hacer coincidir usando MATCH, dependiendo de si el valor del tercer argumento es -1,0 o 1. La más simple de ellas es la coincidencia exacta que utiliza el valor de 0 (como ya lo hizo al comparar con Battery Lookup Table'!B2:O2) . El valor de -1 busca la posición del valor más pequeño en el rango de búsqueda que es mayor o igual al valor de búsqueda. Este es su requisito, pero requiere que el rango de búsqueda se coloque endescendiendoorden. (El valor de 1 encuentra la posición del valor más grande en el rango de búsqueda que es menor o igual al valor de búsqueda y requiere que el rango de búsqueda esté en orden ascendente; este no es su requisito).
Los valores en Battery Lookup Tabletodos parecen estar en orden ascendente, por lo que reorganizar sus datos para que las filas aparezcan en orden inverso satisfará la condición de orden descendente: 100G99 será la primera fila de datos y 50G05 la última. También debe eliminar las filas en blanco, ya que los valores en ellas se tratarán como cero, por lo que potencialmente causarán problemas al violar el requisito de orden descendente.
La expresion
MATCH(C46,list,-1))
proporciona el puesto enlistadel valor que está buscando y su valor real (para mostrar en la celda de fondo verde) lo proporciona
=INDEX(list,MATCH(C46,list,-1))
El modelo de batería es proporcionado por
=INDEX('Battery Lookup Table'!A4:A46,MATCH(C46,list,-1))
Sustituyendo la expresión porlista(anotado anteriormente) en estas dos fórmulas da como resultado expresiones engorrosas con un grado de repetición en la primera y cálculos repetidos en MATCH(C46,list,-1)ambas. Estas repeticiones no son una buena práctica y, si es posible, deben evitarse.
Una forma de hacerlo sería almacenar en las celdas de la hoja de cálculo los valores de los números de columna y fila coincidentes. Respectivamente, estos son:
=MATCH(C39,'Battery Lookup Table'!B2:O2,0)- denotamos pornorte, y
=MATCH(C46, INDEX('Battery Lookup Table'!B4:B36,,n),-1)- denotamos pormetro
y los resultados de la búsqueda son entonces
INDEX('Battery Lookup Table'!B4:O36,m,n)(clasificación constante a la duración de carga requerida) y
INDEX('Battery Lookup Table'!A4:A36,m) (número de modelo de batería correspondiente)
dóndenorteymetroson las dos referencias de celda que contienen los números de columna y fila calculados.