Können Sie mir bitte bei der folgenden Anforderung helfen?
Ich habe Bereiche in den Spalten A und B gepflegt. Relevante Werte für jeden Bereich sind in Spalte C aufgeführt. Siehe beispielsweise unten:
| Column A | Column B | Column C |
| 1 | 10 | A |
| 15 | 20 | B |
| 21 | 30 | C |
Jetzt habe ich eine weitere Spalte E, in die ich eine Zahl eingegeben habe, zum Beispiel 17. Jetzt möchte ich wissen, in welchem Bereich diese Zahl liegt, damit der entsprechende Wert aus Spalte C eingefügt werden kann. In diesem Beispiel liegt 17 im Bereich von 15 bis 20, der sich in Zeile 2 befindet und dessen Wert in Spalte C B ist. Daher sollte für die Zahl 17 die Ausgabe B sein. Wenn ich 25 eingebe, sollte die Ausgabe C sein. Wenn ich jedoch 12 eingebe, sollte die Ausgabe #N/A sein, da diese Zahl in keinen Bereich fällt.
Darf ich wissen, ob es Excel-Formeln gibt, mit denen ich diese Art von Ausgabe erhalten kann? Ich bevorzuge die Verwendung von Formeln gegenüber Code. Vielen Dank für Ihre Zeit, um mich diesbezüglich anzuleiten.
Mit freundlichen Grüßen, Sridhar
Antwort1
Ja, das ist möglich. Sie benötigen eine zusätzliche Zeile in der Tabelle, damit es funktioniert.
Ich habe die Zeile hinzugefügt
| 0 | 0 | #N/A |
in die Tabelle als erste Zeile, so dass die Tabelle nunA1:C4.
Ich verwende die INDEXFunktion, um zu entscheiden, welche Zeile ich zurückgeben muss.
Um die Zeile zu berechnen, verwende ich SUMPRODUCTdie Zeile, die INDEX verwenden wird.
Die vollständige Formel lautet:
=INDEX(C1:C4,SUMPRODUCT(--(A1:A4<=D1),--(B1:B4>=D1),ROW(C1:C4)))
mit dem einzucheckenden WertD1
Durchlaufen der Formel mitT1 (T1)enthalten 17:
=INDEX(C1:C4,SUMPRODUCT(--(A1:A4<=17),--(B1:B4>=17),ROW(C1:C4)))
=INDEX(C1:C4,SUMPRODUCT(--({0,1,15,21}<=17),--({0,10,20,30}>=17),{1,2,3,4}))
=INDEX(C1:C4,SUMPRODUCT(--({True,True,True,False}),--({False,False,True,True}),{1,2,3,4}))
=INDEX(C1:C4,SUMPRODUCT({1,1,1,0}),({0,0,1,1}),{1,2,3,4}))
=INDEX(C1:C4,(1*0*1 + 1*0*2 + 1*1*3 + 0*1*4))
=INDEX(C1:C4,3)
="B"
Wenn die Zahl außerhalb des Bereichs liegt, gibt es keinen Teil im SUMPRODUCT, in dem die Wertezeile nicht gleich ist 0. Daher wird der erste Wert im Bereich zurückgegeben, der #N/A ist. Dieselbe Formel, aber mit 12inT1 (T1):
=INDEX(C1:C4,SUMPRODUCT(--(A1:A4<=12),--(B1:B4>=12),ROW(C1:C4)))
=INDEX(C1:C4,SUMPRODUCT(--({0,1,15,21}<=12),--({0,10,20,30}>=12),{1,2,3,4}))
=INDEX(C1:C4,SUMPRODUCT(--({True,True,False,False}),--({False,False,True,True}),{1,2,3,4}))
=INDEX(C1:C4,SUMPRODUCT({1,1,0,0}),({0,0,1,1}),{1,2,3,4}))
=INDEX(C1:C4,(1*0*1 + 1*0*2 + 0*1*3 + 0*1*4))
=INDEX(C1:C4,0)
="#N/A"
Es ist technisch nicht#N / Aist es ein Array aller möglichen Antworten, aber da wir die Formel nicht als Array-Formel eingegeben haben, erhalten wir nur den ersten Wert