Quelle: https://www.clever-excel-forum.de/Thread-Welche-Formel-nutzen
Stand: 04.09.2024 17:30
Im Forum wurde gefragt, wie ein gegebener Tarif einer Postleitzahl zugeordnet werden kann. Dabei ist die Referenz-Liste eine 2‑zeilige Kreuztabelle, wo statt der kompletten PLZ nur die ersten 2 Ziffern referenziert wurden. Der Fragesteller ist (naturgemäß) mit dem SVERWEIS() nicht zum Ziel gelangt und wurde zu Recht auf den WVERWEIS() aufmerksam gemacht. 😉
Es blieb das Problem mit den PLZ, welche eine führende Null und dann 4 Ziffern haben. Die zum jetzigen Stand aktuelle vorgeschlagene Lösung habe ich nicht referenziert, da Listen dieser Art typischerweise sehr umfangreich werden können und sich dann Power Query anbietet. In meinem Lösungsvorschlag habe ich noch 2 Postleitzahlen mit führender Null angefügt, die erste per Zahlenformat 5‑stellig dargestellt und die zweite als Text:
Die PLZ 02625 wird (natürlich) falsch ausgewertet…
Jedes Arbeitsblatt enthält 1 Tabelle, beide sind per PQ aufbereitet und ausgewertet worden. Hier die beiden Blöcke des M-Code mit kleineren Erklärungen:
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 Stichwort last but not least finden Sie noch einen Hinweis, wo und wie Sie Antworten auf eventuelle Fragen oder auch die ausgearbeitete Datei bekommen können. Grundlegendes zum Thema M-Code lesen Sie hier im Blog nach.