Ich habe eine Kalkulationstabelle, die den Dauerstrombedarf einer Batterie berechnet. Ich möchte aus der eingestellten Ladedauer eine geeignete Batterie finden, deren Dauerstrom größer oder gleich meinem berechneten Wert ist.
Ich versuche, eine Nachschlagetabelle zu verwenden, in der meine „Ladedauer“ (Gelb) eine Spalte aus einem Array auswählt und meine Nachschlagefunktion (Grün) dann einen Wert >= zu meinem berechneten Referenzwert (Blau) findet. Ich hätte auch gerne eine andere Funktion, die das Modell aus derselben Zeile wie der gefundene Wert aus dem Array findet.
Ladedauer ist ein Dropdown-Menü, das den entsprechenden Spalten entspricht. Ich habe derzeit eine Zelle, die die Spalte des Arrays auswählt:
=MATCH(C39,'Battery Lookup Table'!B2:O2,0)
Ich habe mehrere Nachschlagefunktionen ausprobiert, um die nächstgelegenen Konstantstromwerte zu finden:
=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))
Jede dieser Nachschlagetabellen hat denselben Wert erzeugt.
Ich habe auch versucht, die Werte vom kleinsten zum größten zu sortieren, da VLOOKUP und HLOOkUP Schwierigkeiten beim Entschlüsseln haben. Ich habe auch versucht, für die Zeitdauer in Zeilen zu wechseln, aber ohne Erfolg.
Modell-Nr:=INDEX('Battery Lookup Table'!A4:A36,MATCH(TRUE,'Battery Lookup Table'!M4:M25>=C46,0))
Derzeit geht meine Nachschlagefunktion zur richtigen Spalte, verwendet aber meistens eine Zeile/einen Wert am unteren Ende der Tabelle. Keiner der gefundenen Werte war jemals der nächstliegende Wert.
Antwort1
VLOOKUPgleicht einen Wert mit demErsteSpalte eines Bereichs und liefert den Wert aus einer angegebenen Spalte des Bereichs in der übereinstimmenden Zeile. Daher ist Ihre erste Formel
=VLOOKUP(C46,'Battery Lookup Table'!B4:O36,MATCH(C39,'Battery Lookup Table'!B2:O2,0),TRUE)
stimmt C46mit den Werten im Bereich überein (was in Ihrem Beispiel meiner Meinung nach 500,8 ist) 'Battery Lookup Table'!B4:B36. Dies ist nicht der Bereich, mit dem Sie übereinstimmen möchten, aber es erklärt wahrscheinlich, warum Sie falsche Ergebnisse erhalten.
Tatsächlich haben Sie bereits die richtige Spalte identifiziert, mit der Ihre MATCHFunktion abgeglichen werden soll. Sie können die INDEXFunktion verwenden, um einen Teil eines Bereichs auszuwählen. Der Ausdruck
INDEX('Battery Lookup Table'!B4:O36,,n)
bietet dieN'te Spalte, 'Battery Lookup Table'!B4:O36also setzen Sie Ihre MATCHFunktion an die Stelle vonNliefert die richtige Wertespalte, mit der C46verglichen werden kann. Dadurch ergibt sich der Ausdruck:
INDEX('Battery Lookup Table'!B4:O36,,MATCH(C39,'Battery Lookup Table'!B2:O2,0))
Der Einfachheit halber werde ich diesen Ausdruck wie folgt abkürzen:Liste.
Sie möchten jetzt zwei Dinge tun:
- Finden Sie den kleinsten Wert inListedas ist größer oder gleich
C46 - Suchen Sie die Batterie die diesem Wert entspricht.
VLOOKUP()Der Trick besteht hier darin, nicht den flexibleren „Cousin“ zu verwenden INDEX(MATCH()). Während „Cousin“ VLOOKUPeinen Wert in der ersten Spalte eines Bereichs abgleicht und einen Wert aus einer Spalte rechts davon liefert, lässt die flexiblere Version zu, dass der Wert entweder aus derselben Spalte oder aus einer Spalte links davon stammt.
Darüber hinaus gibt es 3 Möglichkeiten zum Abgleichen mit MATCH, je nachdem, ob der Wert des dritten Arguments -1,0 oder 1 ist. Die einfachste davon ist das exakte Abgleichen, bei dem der Wert 0 verwendet wird (wie Sie es bereits beim Abgleichen mit getan haben Battery Lookup Table'!B2:O2). Der Wert -1 findet die Position des kleinsten Werts im Suchbereich, der größer oder gleich dem Suchwert ist. Dies ist Ihre Anforderung, erfordert jedoch, dass der Suchbereich inabsteigendReihenfolge. (Der Wert 1 findet die Position des größten Werts im Nachschlagebereich, der kleiner oder gleich dem Nachschlagewert ist und erfordert, dass der Nachschlagebereich in aufsteigender Reihenfolge vorliegt – das ist nicht Ihre Anforderung.)
Die Werte in Ihrem Battery Lookup TableDatensatz scheinen alle in aufsteigender Reihenfolge zu sein. Wenn Sie Ihre Daten also so neu organisieren, dass die Zeilen in umgekehrter Reihenfolge erscheinen, wird die Bedingung der absteigenden Reihenfolge erfüllt – 100G99 ist die erste Datenzeile und 50G05 die letzte. Sie müssen auch die leeren Zeilen entfernen, da die Werte in diesen als Null behandelt werden und daher möglicherweise Probleme verursachen, da die Anforderung der absteigenden Reihenfolge verletzt wird.
Der Ausdruck
MATCH(C46,list,-1))
bietet die Position inListedes gesuchten Wertes und seines tatsächlichen Wertes (zur Anzeige in Ihrer grünen Hintergrundzelle) wird bereitgestellt durch
=INDEX(list,MATCH(C46,list,-1))
Das Batteriemodell wird bereitgestellt von
=INDEX('Battery Lookup Table'!A4:A46,MATCH(C46,list,-1))
Ersetzen des Ausdrucks durchListe(siehe oben) in diese beiden Formeln führt zu umständlichen Ausdrücken mit einem gewissen Grad an Wiederholung in der ersten und wiederholten Berechnungen in MATCH(C46,list,-1)beiden. Diese Wiederholungen sind keine gute Praxis und sollten, wenn möglich, vermieden werden.
Eine Möglichkeit hierfür besteht darin, in den Arbeitsblattzellen die Werte der entsprechenden Spalten- und Zeilennummern zu speichern. Diese lauten:
=MATCH(C39,'Battery Lookup Table'!B2:O2,0)- bezeichnen mitN, Und
=MATCH(C46, INDEX('Battery Lookup Table'!B4:B36,,n),-1)- bezeichnen mitM
und die Suchergebnisse werden dann
INDEX('Battery Lookup Table'!B4:O36,m,n)(Dauerleistung bei geforderter Belastungsdauer) und
INDEX('Battery Lookup Table'!A4:A36,m) (entsprechende Batteriemodellnummer)
WoNUndMsind die beiden Zellbezüge, die die berechneten Spalten- und Zeilennummern enthalten.