Summenprodukt

Question

Moin Zusammen,
ich habe folgendens problem
in Tablle 1 sp. 1= Postleitzahl sp.2= strasse mit hausnummer
jetzt möchte ich diedaten bestimmten touren zuordnen, allerdings gibt es bei langen strassen mehrer touren die die str. beliefern.
1. frage
gibt es eine möglichkeit bei summenprodukt einen "platzhalter" für die hausnummer in der formel einzugeben? Damit die daten unabhänig von der hausnummer gezählt werden.
abc str. 1 in der formel "abc str. ?"
2. frage
oder gibt es eine möglichkeit die hausnummer aus der spalte (mittels formel) "zu exportieren"?

falls jemand sonst einen anderen gedanken hat ...ich bin für jeden vorschlag offen und für jede hilfe dankbar
sturmflut

Answer 1

Hallo sturmflut,

ich würde die Hausnummer in eine getrennte Spalte schreiben, sonst wird das Auslesen derselben in der Formel zu kompliziert.

Hausnummern können ja ein- und mehrstellig sein und zusätzlich noch Buchstaben enthalten.

Gruß
Rainer

Answer 2

ja ..würde ich auch gerne ..leider macht das zuviel arbeit, da es um ca. 20000 datensätze geht die monatich neu erstellt werde.:o(( und eine IT Umstellung kommt zur zeit nicht in frage.
deshalb die frage nach dem "platzhalter" für die Hausnummer

Answer 3

Hallo sturmflut,

mit Platzhaltern hast Du da schlechte Karten und auch SUMMENPRODUKT() hilft Dir da nicht.

Vielleicht hilft mein Beispiel mit Hilfsspalte weiter.

www.file-upload.net/download-2064371/Hausnummer.xls.html

Gruß
Rainer

Answer 4

Hallo sturmflut,

ich habe hier mal einen Vorschlag zum Aufsplitten von Straße und Hausnummer auf 2 Spalten. Er ist sicherlich nicht so perfekt wie andere, die man z. B. hier (www.excelformeln.de/formeln.html?gruppe=2) nachlesen kann, dafür aber einfach, robust und nicht sehr anspruchsvoll gegenüber dem Datenmaterial, das in der Praxis ja manchmal doch recht vielfältig ist. Allerdings muss zwischen der Straßenbezeichnung und der abzutrennenden Hausnummer (mindestens) ein Leerzeichen stehen.

Man müsste sich zuerst für eine nicht zu klein und nicht zu groß bemessene Zahl entscheiden, welche Anzahl an Zeichen von der Hausnummer und allen ihren möglichen Zusätzen nicht überschritten wird. Dabei sind eventuell solche Fälle wie Bahnhofstraße 326 - 328, Am Froschteich 199 a - h, Baseler Platz 4 und, wenn's ganz schlimm kommt, auch Hauptweg 34 Seitenflügel und Hauptweg 35 Hinterhaus zu berücksichtigen. Nehmen wir z. B. die 9, obwohl das dann mit dem Baseler Platz 4 leider schief geht.

Nun wird innerhalb der letzten 10 Zeichen (wegen Startposition = Länge - 9) nach der genauen Position des 1. Leerzeichens in diesem Teil gesucht und an dieser Stelle aufgesplittet. Bleibt (hoffentlich!) links die Straße und rechts der Rest inklusive Hausnummer.

Probier's einfach mal aus! Es kostet Dich nur 2 leere Spalten und ein paar Minuten, um einen ersten Eindruck zu gewinnen.

in B3: die erste Straße und Hausnummer

in C3: =LINKS(B3; SUCHEN(" "; B3; MAX(LÄNGE(B3) - 9; 1)) - 1)

in D3: =GLÄTTEN(TEIL(B3; SUCHEN(" "; B3; MAX(LÄNGE(B3) - 9; 1)) + 1; LÄNGE(B3)))

Wie gesagt, für die Länge des Restes (9) muss das Optimum anhand der tatsächlichen Daten ermittelt werden.

MfG Charlotte

Answer 5

super ...
danke charlotte
das macht die sache um längen einfacher

Answer 6

Hallo sturmflut,

freut mich für Dich und danke für die Rückmeldung! Aber wie gesesagt, in der Praxis kann das - gerade bei etwas unübersichtlichen 20000 Datensätzen - schwierig bis unmöglich werden, eine geeignete Stelle zu finden, wo man das Ganze nach einer einheitlichen Regel "zerhacken" will. Mit einem "Gerader Weg 1" und einer "Straße des 17. Juni 253 - 256" hast Du da gar keine Chance.

Denn das Problem liegt weder in der Frage Formel oder VBA, noch in der Frage, ob man es ein bisschen einfacher oder ein bisschen komplizierter machen will, sondern schlicht daran, dass sich für einige praktische Fälle keine formale Regel findet, die auf alles passt.

Ich weiß nicht, ob es in der "Straße des 17. Juni" auch Hausnummern mit Buchstabenzusätzen gibt, aber wenn ja, dann hätte man da eine Folge von Text - Zahl - Text - Zahl - Text mit mindestens 5 Leerzeichen dazwischen. Wo trennen? - Das kann ein "dummes" Programm schon ganz schön überfordern, aber wenn der Programmierer auch nicht in der Lage ist, eine Regel vorzugeben, ist es ja kein Wunder.

Gut, Schluss mit dem bla-bla. Das allerbeste ist sowieso, Straße und Hausnummer in getrennten Feldern/Zellen zu halten, dann spart man sich den ganzen Ärger und zusammenfügen kann man sie ja ganz leicht.

Gruß,
Charlotte