Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Formel gesucht, wie sverweis aber Suche von/bis

Formel gesucht, wie sverweis aber Suche von/bis
27.05.2019 11:43:36
Lukas
Hallo zusammen,
hoffe der Titel verwirrt nicht zu sehr, im Prinzip will ich überhaupt nichts schweres von Excel, mir will aber keine Lösung einfallen, ich habe eine Aufstellung von Postleitzahlen und Zonen (Für Speditionspreisliste)
PLZ 1010-1700 ist Zone 1
PLZ 2000-2199 ist Zone 2
PLZ 2200-2599 ist Zone 1
in Summe hab ich 21 solche Zeilen mit Zonen, und wie ihr sehen könnt, die Zonen nicht nach PLZ fortlauden, sondern es kommt Zone 1, dann ein Teil Zone 2, dann ein paar PLZ wieder Zone 1, wieder Zone 2 etc.
Ich habe eine Liste aller österreichischer PLZ und will nur, dass bei jeder davon gem. meiner Zonen Liste die richtige Zone zugewiesen wird. Verschachtelte Wenn viel zu viel Aufwand (und gar nicht möglich) sverweis wäre ein Grundgedanke, jedoch weiß ich nicht, ob es geht (wenn ja, wie) dass der Suchwert nicht ein fixes Feld ist, sondern ein "von-bis" Bereich.
Vielleicht kann mir jemand helfen :)
Besten Dank
Gruß
Lukas

11
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: nicht SVERWEIS() aber z.B. mit VERWEIS() ...
27.05.2019 11:47:24
neopa
Hallo Lukas,
... kann man eine entsprechende Auswertung vornehmen. Doch momentan ist nicht eindeutig, was wie genau wo steht.
(D)eine kleine Beispieldatei könnte hilfreich sein.
Gruß Werner
.. , - ...
AW: Formel gesucht, wie sverweis aber Suche von/bis
27.05.2019 11:51:26
Tilman
heyho,
schau dir mal diese Funktionen an:
=index(suchbereich;AGGREGAT(15;6;ZEILE(suchbereich)/(suchbereichobere PLZ) ...
ich beutz die funktion um in nem Dateumsbereich von Bis etwas zu suchen und mir dann anzeigen zu lassen.
am besten du lädst ne beispieldatei hoch damit man dir das am beispiel anpasst
=VERGLEICH(A1;{0;1700;2199;9999};1)
27.05.2019 12:02:09
WF
Hi,
die PLZ steht in A1
Ich gehe davon aus, dass es die PLZ 0 bis 1009; 1701 bis 1999 und über 2599 nicht gibt.
WF
Anzeige
AW: Formel gesucht, wie sverweis aber Suche von/bis
27.05.2019 12:09:18
Lukas
Hallo zusammen,
danke für die raschen Antworten, anbei die Datei (hoffe habe das richtig gemacht...)
https://www.herber.de/bbs/user/130048.xlsx
wie schon richtig erwähnt, hat die Liste der Zonen mehr PLZ als es eig gibt, da ist einfach "von-bis" abgedeckt, daher hab ich auch eine PLZ Liste von jenen die es tatsächlich gibt.
Spalte A enthält alle echten PLZ, die Formel soll in Spalte B die jeweilige Zone zurückgeben.
Diese sind ein paar Spalten weiter, Spalte H ist PLZ von, Spalte I ist PLZ bis, und Spalte J dann die Zonen. und da sollte die entsprechende Zone eben in Spalte B rückgegeben werden.
lg
Anzeige
AW: Formel gesucht, wie sverweis aber Suche von/bis
27.05.2019 12:39:16
Günther
Moin Lukas,
nicht per Formel dafür aber schnell und "sauber" ;-). Voraussetzung: Du musst Power Query als (kostenloses) Microsoft Add-In installieren und dich damit beschäftigen...
Muster-Lösung auf Nachfrage -> per PM/E-Mail oder in meiner Cloud, da ich meine Ordner-Struktur nicht wegen eines Forums umstelle ...
https://my.hidrive.com/share/jja8mbx2c6
Gruß
Günther
AW: es geht dann auch einfach SVERWEIS() ...
27.05.2019 12:46:11
neopa
Hallo Lukas,
... wenn Du Deine Daten in H6:J26 aufwärts sortierst.
Dann in B2: =SVERWEIS(A2;H$6:J$26;3) und Formel nach unten kopieren.
Gruß Werner
.. , - ...
AW: es geht dann auch einfach SVERWEIS() ...
27.05.2019 13:08:26
Günther
Stimmt, Werner.
Manchmal ist "back to the roots" doch eine Überlegung wert... ;-)
Gruß
Günther
Anzeige
AW: Formel gesucht, wie sverweis aber Suche von/bis
27.05.2019 12:57:42
Daniel
Hi
am besten mit der Lösung von Werner, dh die PLZ-Zonenzuordnung nach der ersten PLZ-Spalte aufsteigend sortieren und dann den SVerweis mit 4.Parameter = WAHR oder nicht angegeben.
solltest du die Liste nicht sortieren wollen, dann gäbs auch noch die Variante mit SummeWenns:
B2: =SUMMEWENNS($J$6:$J$26;$H$6:$H$26;"="&A2)
wichtig ist, hier, dass es bei den PLZ-Bereichen keine Überschneidungen gibt.
Gruß Daniel
AW: Formel gesucht, wie sverweis aber Suche von/bis
27.05.2019 13:35:29
Lukas
ich bedanke mich bei allen für die schnelle Lösung :) aber warum funktioniert das? ich checks nicht :D
ich hatte ja auch den Sverweis versucht, normal sind meine sverweise immer mit Parameter Wahr also genaue Übereinstimmung, also dachte ich nehme ich falsch für ungefähre, das war aber zur Hälfte falsch, was macht Excel wenn man hier nichts vorgibt? (es funktioniert, habe 10 Stichproben gemacht) :)
Anzeige
AW: Formel gesucht, wie sverweis aber Suche von/bis
27.05.2019 14:40:59
Daniel
Hi
Andersrum:
Falsch: Genaue Übereinstimmung erforderlich
Wahr: Ergebnis auch bei Zwischenwerten
Wobei bei Wahr beachtet werden muss, dass die Liste nach der Suchspalte aufsteigend sortiert sein muss und bei Zwischenwerten der nächstkleinere Wert in der Liste als Ergebnis verwendet wird.
Gruß Daniel
wenn man nichts vorgibt gilt WAHR
27.05.2019 16:52:10
Daniel
dh die Liste muss aufsteigend sortiert sein.
Ist die das nicht, bekommt man trotzdem ein Ergebnis, dieses ist aber Unsinn.
das liegt an der Suchmethode des SVerweis mit 4. Parameter = WAHR:
- Hole dir einen Wert aus der Mitte der Daten.
- Vergleiche diesen Wert mit dem Suchwert, ist er kleiner, mach mit der oberen Datenhälfte weiter, ansonsten mit der unteren.
- Wiederhole den Vorgang mit der Datenteil, der den Suchwert enthalten muss solange, bis nur noch ein Wert übrig bleibt.
Gruß Daniel
Anzeige

324 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige