(Forum) Einer Postleitzahl den passenden Tarif zuordnen

Publiziert am 8. September 2024 von Günther (Admin)

Quelle:  https://www.clever-excel-forum.de/Thread-Welche-Formel-nutzen
Stand: 04.09.2024 17:30

Im Forum wurde gefragt, wie ein gegeben­er Tarif ein­er Postleitzahl zuge­ord­net wer­den kann. Dabei ist die Ref­erenz-Liste eine 2‑zeilige Kreuzta­belle, wo statt der kom­plet­ten PLZ nur die ersten 2 Zif­fern ref­eren­ziert wur­den. Der Fragesteller ist (naturgemäß) mit dem SVERWEIS() nicht zum Ziel gelangt und wurde zu Recht auf den WVERWEIS() aufmerk­sam gemacht. 😉

Es blieb das Prob­lem mit den PLZ, welche eine führende Null und dann 4 Zif­fern haben. Die zum jet­zi­gen Stand aktuelle vorgeschla­gene Lösung habe ich nicht ref­eren­ziert, da Lis­ten dieser Art typ­is­cher­weise sehr umfan­gre­ich wer­den kön­nen und sich dann Pow­er Query anbi­etet. In meinem Lösungsvorschlag habe ich noch 2 Postleitzahlen mit führen­der Null ange­fügt, die erste per Zahlen­for­mat 5‑stellig dargestellt und die zweite als Text:

Die PLZ 02625 wird (natür­lich) falsch aus­gew­ertet…

Jedes Arbeits­blatt enthält 1 Tabelle, bei­de sind per PQ auf­bere­it­et und aus­gew­ertet wor­den. Hier die bei­den Blöcke des M-Code mit kleineren Erk­lärun­gen:

let
    // OHNE Überschrif importieren!
    Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Spalte1", type text}, {"Spalte2", Int64.Type}, {"Spalte3", Int64.Type}, {"Spalte4", Int64.Type}, {"Spalte5", Int64.Type}, {"Spalte6", Int64.Type}, {"Spalte7", Int64.Type}, {"Spalte8", Int64.Type}, {"Spalte9", Int64.Type}, {"Spalte10", Int64.Type}, {"Spalte11", Int64.Type}, {"Spalte12", Int64.Type}, {"Spalte13", Int64.Type}, {"Spalte14", Int64.Type}, {"Spalte15", Int64.Type}, {"Spalte16", Int64.Type}, {"Spalte17", Int64.Type}, {"Spalte18", Int64.Type}, {"Spalte19", Int64.Type}, {"Spalte20", Int64.Type}, {"Spalte21", Int64.Type}, {"Spalte22", Int64.Type}, {"Spalte23", Int64.Type}, {"Spalte24", Int64.Type}, {"Spalte25", Int64.Type}, {"Spalte26", Int64.Type}, {"Spalte27", Int64.Type}, {"Spalte28", Int64.Type}, {"Spalte29", Int64.Type}, {"Spalte30", Int64.Type}, {"Spalte31", Int64.Type}, {"Spalte32", Int64.Type}, {"Spalte33", Int64.Type}, {"Spalte34", Int64.Type}, {"Spalte35", Int64.Type}, {"Spalte36", Int64.Type}, {"Spalte37", Int64.Type}, {"Spalte38", Int64.Type}, {"Spalte39", Int64.Type}, {"Spalte40", Int64.Type}, {"Spalte41", Int64.Type}, {"Spalte42", Int64.Type}, {"Spalte43", Int64.Type}, {"Spalte44", Int64.Type}, {"Spalte45", Int64.Type}, {"Spalte46", Int64.Type}, {"Spalte47", Int64.Type}, {"Spalte48", Int64.Type}, {"Spalte49", Int64.Type}, {"Spalte50", Int64.Type}, {"Spalte51", Int64.Type}, {"Spalte52", Int64.Type}, {"Spalte53", Int64.Type}, {"Spalte54", Int64.Type}, {"Spalte55", Int64.Type}, {"Spalte56", Int64.Type}, {"Spalte57", Int64.Type}, {"Spalte58", Int64.Type}, {"Spalte59", Int64.Type}, {"Spalte60", Int64.Type}, {"Spalte61", Int64.Type}, {"Spalte62", Int64.Type}, {"Spalte63", Int64.Type}, {"Spalte64", Int64.Type}, {"Spalte65", Int64.Type}, {"Spalte66", Int64.Type}, {"Spalte67", Int64.Type}, {"Spalte68", Int64.Type}, {"Spalte69", Int64.Type}, {"Spalte70", Int64.Type}, {"Spalte71", Int64.Type}, {"Spalte72", Int64.Type}, {"Spalte73", Int64.Type}, {"Spalte74", Int64.Type}, {"Spalte75", Int64.Type}, {"Spalte76", Int64.Type}, {"Spalte77", Int64.Type}, {"Spalte78", Int64.Type}, {"Spalte79", Int64.Type}, {"Spalte80", Int64.Type}, {"Spalte81", Int64.Type}, {"Spalte82", Int64.Type}, {"Spalte83", Int64.Type}, {"Spalte84", Int64.Type}, {"Spalte85", Int64.Type}, {"Spalte86", Int64.Type}, {"Spalte87", Int64.Type}, {"Spalte88", Int64.Type}, {"Spalte89", Int64.Type}, {"Spalte90", Int64.Type}, {"Spalte91", Int64.Type}, {"Spalte92", Int64.Type}, {"Spalte93", Int64.Type}, {"Spalte94", Int64.Type}, {"Spalte95", Int64.Type}, {"Spalte96", Int64.Type}}),
    #"Umgekehrte Zeilen" = Table.ReverseRows(#"Geänderter Typ"),
    // Jetzt 'passt' die Überschrift
    #"Höher gestufte Header" = Table.PromoteHeaders(#"Umgekehrte Zeilen", [PromoteAllScalars=true]),
    // Automatisch erstellt, kann aber gelöscht werden
    #"Geänderter Typ1" = Table.TransformColumnTypes(#"Höher gestufte Header",{{"PLB", type text}, {"30", Int64.Type}, {"31", Int64.Type}, {"29", Int64.Type}, {"32", Int64.Type}, {"38", Int64.Type}, {"20", Int64.Type}, {"21", Int64.Type}, {"22", Int64.Type}, {"26", Int64.Type}, {"27", Int64.Type}, {"28", Int64.Type}, {"33", Int64.Type}, {"34", Int64.Type}, {"39", Int64.Type}, {"49", Int64.Type}, {"59", Int64.Type}, {"4", Int64.Type}, {"6", Int64.Type}, {"7", Int64.Type}, {"14", Int64.Type}, {"19", Int64.Type}, {"23", Int64.Type}, {"24", Int64.Type}, {"25", Int64.Type}, {"35", Int64.Type}, {"36", Int64.Type}, {"37", Int64.Type}, {"40", Int64.Type}, {"42", Int64.Type}, {"44", Int64.Type}, {"45", Int64.Type}, {"46", Int64.Type}, {"47", Int64.Type}, {"48", Int64.Type}, {"50", Int64.Type}, {"51", Int64.Type}, {"57", Int64.Type}, {"58", Int64.Type}, {"98", Int64.Type}, {"99", Int64.Type}, {"1", Int64.Type}, {"3", Int64.Type}, {"8", Int64.Type}, {"9", Int64.Type}, {"10", Int64.Type}, {"12", Int64.Type}, {"13", Int64.Type}, {"15", Int64.Type}, {"16", Int64.Type}, {"41", Int64.Type}, {"52", Int64.Type}, {"53", Int64.Type}, {"56", Int64.Type}, {"60", Int64.Type}, {"61", Int64.Type}, {"63", Int64.Type}, {"65", Int64.Type}, {"96", Int64.Type}, {"97", Int64.Type}, {"2", Int64.Type}, {"17", Int64.Type}, {"18", Int64.Type}, {"54", Int64.Type}, {"55", Int64.Type}, {"64", Int64.Type}, {"67", Int64.Type}, {"68", Int64.Type}, {"69", Int64.Type}, {"74", Int64.Type}, {"90", Int64.Type}, {"91", Int64.Type}, {"95", Int64.Type}, {"66", Int64.Type}, {"70", Int64.Type}, {"71", Int64.Type}, {"72", Int64.Type}, {"73", Int64.Type}, {"75", Int64.Type}, {"76", Int64.Type}, {"77", Int64.Type}, {"85", Int64.Type}, {"86", Int64.Type}, {"89", Int64.Type}, {"92", Int64.Type}, {"93", Int64.Type}, {"78", Int64.Type}, {"79", Int64.Type}, {"80", Int64.Type}, {"81", Int64.Type}, {"82", Int64.Type}, {"83", Int64.Type}, {"84", Int64.Type}, {"87", Int64.Type}, {"88", Int64.Type}, {"94", Int64.Type}}),
    #"Entpivotierte andere Spalten" = Table.UnpivotOtherColumns(#"Geänderter Typ1", {"PLB"}, "Attribut", "Wert"),
    #"Entfernte Spalten" = Table.RemoveColumns(#"Entpivotierte andere Spalten",{"PLB"}),
    // Kleiner Umweg, um die (durchaus elegantere) M-Funktion 'Text.PadStart' zu umgehen, um bei Bedarf eine führende 0 (Null) voranzufügen
    #"Präfix hinzugefügt" = Table.TransformColumns(#"Entfernte Spalten", {{"Attribut", each "0" & _, type text}}),
    #"Extrahierte letzte Zeichen" = Table.TransformColumns(#"Präfix hinzugefügt", {{"Attribut", each Text.End(_, 2), type text}}),
    #"Umbenannte Spalten" = Table.RenameColumns(#"Extrahierte letzte Zeichen",{{"Attribut", "PLZ-Bereich"}})
in
    #"Umbenannte Spalten"

Und für Tabelle2:

let
    Quelle = Excel.CurrentWorkbook(){[Name="Tabelle2"]}[Content],
    // TEXT, damit führende Nullen erhalten bleiben
    #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"PLZ", type text}}),
    // Festschreibung der Reihenfolge
    #"Hinzugefügter Index" = Table.AddIndexColumn(#"Geänderter Typ", "Index", 0, 1, Int64.Type),
    #"Eingefügte erste Zeichen" = Table.AddColumn(#"Hinzugefügter Index", "Erste Zeichen", each Text.Start([PLZ], 2), type text),
    #"Zusammengeführte Abfragen" = Table.NestedJoin(#"Eingefügte erste Zeichen", {"Erste Zeichen"}, Tabelle1, {"PLZ-Bereich"}, "Tabelle1", JoinKind.LeftOuter),
    #"Erweiterte Tabelle1" = Table.ExpandTableColumn(#"Zusammengeführte Abfragen", "Tabelle1", {"Wert"}, {"Wert"}),
    // Hinweis: Die ursprüngliche Reihenfolge beachten!
    #"Umbenannte Spalten" = Table.RenameColumns(#"Erweiterte Tabelle1",{{"Wert", "Tarifzone"}}),
    #"Sortierte Zeilen" = Table.Sort(#"Umbenannte Spalten",{{"Index", Order.Ascending}}),
    // Eventuell die Spalte 'PLZ' löschen und 'Tarifzone'im Arbeitsblatt  direkt neben 'PLZ' platzieren
    #"Entfernte Spalten" = Table.RemoveColumns(#"Sortierte Zeilen",{"Index", "Erste Zeichen"})
in
    #"Entfernte Spalten"

Und hier, beim Stich­wort last but not least find­en Sie noch einen Hin­weis, wo und wie Sie Antworten auf eventuelle Fra­gen oder auch die aus­gear­beit­ete Datei bekom­men kön­nen. Grundle­gen­des zum The­ma M-Code lesen Sie hier im Blog nach.

Post Views: 92
Dieser Beitrag wurde unter Allgemein veröffentlicht. Setze ein Lesezeichen auf den Permalink.