Office: (Office 2013) Bundesländer nach PLZ-Bereich zuordnen/definieren

Hi liebes Forum,

ich habe ähnliche Beiträge gefunden, die Lösungen passen jedoch irgendwie nicht auf mein Problem, bzw. vllt. habe ich diese auch nicht verstanden.

Mein Problem wirkt auf den ersten Blick vllt. einfach, aber bei der Ausführung scheitere ich.

Ich habe eine Tabelle angefügt. Ich möchte einen PLZ-Bereich mit einer PLZ vergleichen. Liegt der Bereich innerhalb, soll der Wert übernommen werden.

Und das muss ich dann mit Postleitzahlen aus ganz Deutschland machen. Habe auch das PDF mit den Bereichen eingefügt. Das sind viele Werte, ich hoffe, dass die Verschachtelung nicht zu groß wird. Vllt. kann man das auch anders lösen.

Die Werte stimmen nicht und sind ffiktiv, es geht mir nur erstmal um die Formel.

Grüße
Mesut

:)

 

Hallo,

du könntest es etwa so lösen:

Tabelle1

	D	E	F	G	H	I
1	Datentabelle				Auswertung
2	PLZ von	PLZ bis	Bundesland		PLZ	Bundesland
3	63598	64753	Bayern		64753	Bayern
4	64754	65326	Hessen		64754	Hessen
5	65327	66587	Berlin		64755	Hessen
6					65327	Berlin
7					65328	Berlin

verwendete Formeln
Zelle	Formel	Bereich	N/A
I3:I7	=INDEX($F:$F;SUMMENPRODUKT((H3>=$D:$D)*(H3<=$E:$E)*(ZEILE($G:$G))))

Beachte zum blau markierten Teil: Die Spalte ist hier egal, wichtig ist aber, dass dieser Bereich in Zeile 1 beginnt und genauso lang ist, wie die anderen Spaltenbereiche in der Formel.


Übrigens hat deine PDF-Tabelle (mindestens) einen Fehler:

Zu welchem Bundesland gehört jetzt PLZ 14715?

Hilft dir das weiter?

VG
Santa

 

Hi Mc Santa,

danke für deine Hilfe.

Ich habe "leider" in der Zwischenzeit eine elegantere Lösung gefunden, weil ich im Netz auch eine Liste fand, wo jede PLZ einzeln aufgeführt ist.

Allerdings war deine Hilfe bestimmt nicht umsonst, werde ich oder andere bestimmt noch irgendwann mal gebrauchen können.

Ich habe Deine Lösung allerdings noch nicht ausprobieren können, weil ich ziehmlich im Stress bin.

Aber ich habe die Formel auch nicht verstanden um ehrlich zu sein, habe noch nie was von der Funktion INDEX gehört, geschweide denn gelernt.

Kannst du das mit einfachen Worten erklären, die offizielle Erklärung verstehe ich nicht wirklich.

http://office.microsoft.com/de-de/ex...010069831.aspx

Freue mich!

Danke!

Vg
Mesut

 

Hallo,

die Indexfunktion ist nicht schwer zu verstehen und danach kann man sie kreativ anwenden *Smilie
Du gibst zuerst einen Bereich an: zb C5:G10
als zweites gibst du eine Zeile an: zb 3
und als drittes gibst du eine Spalte an: zb 2

Index gibt nun den Wert der Zelle zurück: Die 3. Zeile und 2. Spalte aus dem Bereich C5:G10, also den Inhalt der Zelle D7.
Code:

Interessant wird es dadurch, dass du die Zahlen für Zeile und Spalte nicht fest angeben musst, sondern diese durch andere Formeln errechnen lassen kannst. Das mache ich oben zb über die Funktion Summenprodukt().

VG
Santa

 

Hi Santa,

es tut mir fast leid das sagen zu müssen, du hast dir so Mühe gegeben, aber ich hab nichts verstanden.

Dein Beispiel passt garnicht zu Deiner Tabelle, D7 z.B. ist leer. Auch sehe ich Spalte C garnicht, du sagst aber, dass der Bereich C5:G10 ist. Auch G ist leer.

Verstehe ich was falsch?

Danke Gruß
Mesut

 

Meine letzte Antwort (#4) ist ein komplett unabhängiges Beispiel und bezieht sich auch nur allgemein auf die Funktion Index.
Ich wollte erst einmal nur die Formel Index erklären, dann kann man darauf aufbauen und später auch erklären, wie ich sie bei der ursprünglichen Antwort (#2) benutzt habe.

Also lies dir meine Erklärung noch einmal durch und versuche sie zu verstehen, ohne es auf die Postleitzahlen und die Tabelle von oben zu beziehen *Smilie
Ist es jetzt klarer?

VG Santa

 

Also nicht ganz. Es ergibt schon sinn, aber mir fehlt das Suchkriterium oder ich kapiere es einfach nicht. Die Formel ist ja dem Sverweis sehr ähnlich.

Kannst du das vllt. mal anhang einer Excelbeispielliste, die ich hochgeladen habe demonstrieren, dann kapiere ich es definitiv.

Der Bereich ist ja auf zwei Zellen aufgeteilt

Zelle A2 XXXXX
Zelle B2 YYYYY
= XXXXX - YYYYY (Bereich)

Die Zeile und Spalte gibt an, welcher Wert aus der Matrix ausgegeben werden muss, aber nach welchem Suchkriterium.

Tausend Dank!

Gruß
Mesut

 

Kann mir jemand das mit der Index Funktion vllt. kurz und einfach erklären?

 

Hallo,

die Position wird mit diesem Teil der Funktion ermittelt:

 

Hallo
Ich habe dasselbe problem wie Sie. Ich habe auch eine liste mit allen PLZ gefunden. Aber ich habe den S-Verweis probiert und komme nicht weiter. Können Sie mir sagen, wie Sie das Problem gelöst haben?
Beste Grüße
Fady