Zahl im definierten Zahlenbereich finden und über Sverweis bestimmten wert anzeigen?
In meiner Excel-Tabelle stehen in Spalt A 3-4 stellige Zahlen. In Spalte K stehen Zahlenbereiche (100-200, 201-500, 501-1000, usw.), in Spalte L ein bestimmter Wert.
Nun soll ein Sverweis oder auch gerne eine andere Funktion den Wert aus Spalte "A" im definierten Bereich von Spalte "K" finden, und den entsprechenden Wert aus "L" finden.
Ich könnte das mühsam per Hand suchen, aber die Liste ist lang und vielleicht kann mir jemand von euch helfen?
Danke im Voraus.
Roland
4 Antworten
Ja dazu ist der Sverweis ja da.
du musst lediglich eine aufsteigend sortierte Liste in Spalte K haben und den 4. Schalter des Sverweis auf wahr bzw 1 stellen (oder weglassen, das dies die Standardeinstellung ist)
Siehe Bild
In Spalte K trägst du dabei immer die Untergrenze eines Bereichs ein und es darf keine Lücken geben.
Also wie in deinem Fall ist der Bereich 200,0000001 - 200,9999999 ja nicht definiert, sowas geht nicht. Aber in den meisten Fällen ist das unwichtig
zunächst mal:
=SUMMENPRODUKT((L1:L9<=obererWert)*(L1:L9>=untererWert)*L1:L9)
findet den passenden Wert, sofern nur ein einziger passt! Passen mehrere, geht das so einfach nicht (Ergebnis dann unbrauchbar und Du merkst das vllt gar nicht!!, nur mit erheblich komplizierterer Matrixformel machbar!). Diesen Wert könntest du in SVERWEIS als Suchwert verwenden.
Es ist aber gar nicht nötig, über diesen Suchwert zu gehen, denn
=SUMMENPRODUKT((L$1:L9<=obWert)*(L$1:L9>=untWert)*ZEILE(L$1:L9))
liefert direkt die Zeile, so dass Du mit
=Index(A:A;SUMMENPRODUKT((L$1:L9<=obWert)*(L$1:L9>=untWert)*ZEILE(L$1:L9))) den Wert in A:A findest.
Wie Du allerdings die Bereiche aussuchst, ist unklar. Zunächst mal sind die xl-ungeeignet angegeben. Schreib mal in Spalte O
O3: =Teil(K3;suchen("-";K3;2)+1;9)*1 , das spaltet den oberen Wert ab. dann in
U3: =links(K3;suchen("-";K3;2)-1)*1 das spaltet den unteren Wert ab
Oder Du setzt diese Formel*1 direkt anstatt obWert und untWert ein
(*1, weil die Fkt einen Text liefert, der für einen erfolgreichen Vergleich erst in eine Zahl umgewandelt wqerden muss)
Das Suchen(...;2) erlaubt es auch, negative Bereichseingrenzungen zu verwenden (hoffe ich, hab das nicht bis zum Schluss ausprobiert).
Rückfragen bitte in dieser AW, damit ich das sehe!
mit dem SVerweis kannst Du ohnehin keine Zahlen links von der Leitspalte finden, dazu bräuchte es (in Zeile 3)
=Index(A:A;Vergleich(SUMMENPRODUKT((L1:L9<=O3)*(L1:L9>=U3)*L1:L9);L$1:L9;0))
Einer von 5 Gründen, warum ich die Paarung index/Vergleich bevorzuge, (ihn in früher erstellten Dateien indes immer noch massenhaft weiterführe. Soll nicht zur Ideologie ausarten!) Durch die Möglichkeit zur direkten Ermittlung der Zeile ist das aber sowieso hinfällig.
Die Spalten O und U kannst du natürlich nach Bedarf anderswohin versetzen, hab ich nur der verbalen Übersichtlichkeit wegen dahin platziert.
Moin Iamiam, danke für den tollen Post.
Ich habe ein ähnliches Problem, deshalb bin ich auf den alten Thread gestoßen. Meine Datei enthält im Laufe des Jahres über 50.000 Zeilen, INDEX benötit aber ja (verständlicherweise) deutlich mehr Performance als der SVERWEIS.
Hast Du eine Idee, wie man Zahlen aus Bereichen ohne INDEX herausfinden kann? Es geht um Postleitzahlen...
Vielen Dank im Voraus!
Zunächst musst du L-K in meinem Beispiel D-E umsortieren.
Formel in B2:
{=INDEX(E:E;VERGLEICH(A1;RECHTS($D$1:$D$21;LÄNGE($D$1:$D$21)-FINDEN("-";$D$1:$D$21))*1;-1))}
Achtung dies ist eine Matrixformel. Die geschweiften Klammern nicht mit eingeben sondern durch abschliessen der Formel mit Strg+Shift+Enter erzeugen.
hmm sorry aber diese Lösung geht doch eher in die Richtung der "ich verkaufe ihnen eine Excellösung für das Problem"
Also eine Lösung verkaufen, die das Problem tatsächlich löst, aber durch den Anwender möglichst nicht editierbar oder verständlich ist, wodurch Nachfolgeaufträge gesichert werden, da man für jede Änderung sofort wieder ins Boot geholt wird.
Texteingaben (und 101-200 ist nunmal Text) als Zahlenbasis sollten die absolute Ausnahme sein. Selbst dann ist einmal Zerlegen und als Zahlen abspeichern aus meiner Sicht meist die sinniges Variante.
Auch Matrixfunktionen setzt man sparsam sein. Zu einen wegen dr Performance zum anderen weil das editieren damit auf ein anders Level gehoben wird.
Im hiesen Beispiel bräuchte man natürlich gar keine Datenbankfunktion, da sich das Ergebnis berechnen lässt.
Ansonsten kann ich dein Ergebnis in 2 Minuten mit Sverweis nachbauen:
http://www.fotos-hochladen.net/view/sverweis2m1ba7wlihj.jpg
Wäre die Tabelle anders Aufgebaut hätte der Fragesteller sicher danach gefragt. Ich bin nicht dafür da zu beurteilen Warum der Fragesteller etwas so will wie er es will sondern ihm lediglich eine Lösung aufzeigen.
Mal abgesehen davon dass ich der Meinung bin das der SVERWEIS nichts in Excel verloren hat, aber meine Meinung dazu ist hinreichend bekannt.
Moin @augsburgchris, lässt sich das nicht irgendwie in den XVERWEIS umwandeln?
Ich habe das gleiche Problem wie der FS, mein Datensatz umfasst aber gut 50.000 Zeilen; das schafft INDEX nicht. :-(
Die Anzahl der Zeilen des Datensatzes spielt dabei keine Rolle da die Formel ja nur soweit gezogen wird wie sie benötigt wird und mit 21 Zeilen einen relativ überschaubaren Bereich abfragt.
Beim FS nicht, bei mir leider schon.
Konnte es inzwischen aber mit einem kleinen Zwischenschritt und xverweis lösen.
So ganz verstehe ich Dich nicht.
Wenn in L ein bestimmter Wert steht, der in A gesucht werden soll, wozu dienen dann die Bereiche in K? Oder kann man die ignorieren?
Aber vielleicht haben die Anderen Dich ja besser verstanden!?
Na ja, wenn man etwas in einem Preissegment von-bis oder einem Zeitraum von-bis suchen will.
Ja, klar. Aber was soll dann:
in Spalte L ein bestimmter Wert.
und den entsprechenden Wert aus "L" finden.
Es ist doch eigentlich klar, dass der Wert aus L in A nur im definierten Bereich stehen kann.
Aber vielleicht verstehe ich den Aufbau der Tabelle wirklich nicht richtig.
Ich hoffe auf eine erklärende Antwort des FS.
Nein dafür ist er eben NICHT da