Office: Rechnen mit PLZ und Länderkürzeln...

Werte Excelgemeinde,

folgendes Problem:

In meiner Spalte A stehen die ersten 2 Buchstaben von Postleitzahlenkürzeln (z.B. 01, 08, 13, 26,...) UND Länderkürzel (z.B. NL, POL, USA, ...), die beide als Standardformat festgelegt sind (stehen linksbündig in der Zelle)

In meiner Spalte B stehen die zugehörigen Verkehrsmittel als Code (z.B. 1=PKW, 2=Fahrrad, 3=Schiff, ...).

Ich will jetzt mit meiner Summenproduktformel ausrechnen, woher die Personen mit welchem Verkehrsmittel reisen. Dabei ist das Problem, dass ich gerne die PLZ nicht als Text in Klammern ("PLZ") in die Formel einbinden möchte, sondern als tatsächliche Zahl, die ich irgendwoanders eingebe (z.B. 08). Wie kann ich das anstellen? Wie kann ich Excel sagen, das 08 ungleich 8 ist, wenn ich das Format irgendwie von Text auf Zahlen umstelle?

Für Hinweise bin ich wie immer sehr dankbar.

LG, Mat

 

Kannst deine Tabelle mal anhängen?

 

okay, mach ich morgen...

 

okay, hier mal eine Tabelle, wo ich hoffe das Problem kommt rüber:

 

Hallo!

Hilft dies?

=SUMMENPRODUKT(($A$2:$A$1000=TEXT(F$3;"00"))*($B$2:$B$1000=$E8)*$C$2:$C$1000)

 

Ja hilft bestens, vielen Dank! Aber was bedeutet denn TEXT(F$3;"00")?

Hat die "00" was mit den Zahlen in der Zelle zu tun, weil ich hab ja auch PLZ Kürzel wie "174" und Länder die ausgeschrieben sind?

 

Hallo!

Die Funktion TEXT wandelt Zahlen in Text (was sonst...).

Um die Länge dynamisch zu halten, könntest du sowas versuchen:

TEXT(F$3;WIEDERHOLEN(0;LÄNGE(F$3)))

 

Nochmal ganz kurz zum Verständnis:

1.) Die "00" in TEXT(F$3;"00") begrenzt die Länge des eingegebenen Zahlenkürzels auf 2 Stellen?

2.) Eine auf nur 2 Stellen limitierte Eingabe kann vermieden werden, wenn ich dagegen TEXT(F$3;WIEDERHOLEN(0;LÄNGE(F$3))) eingebe?

 

Hallo!

LÄNGE(XY) bestimmt die Zeichenzahl der Zelle,
WIEDERHOLEN(0;LÄNGE(XY)) schreibt so viele Nullen wie die Zelle lang ist.

Also ergibt TEXT(XY;WIEDERHOLEN(0;LÄNGE(XY)))
z.B. TEXT(XY;"00"), wenn in XY z.B. "12" steht, und
z.B. TEXT(XY;"0000"), wenn in XY z.B. "1234" steht.

 

Ah danke... alles klar.

es gibt aber nun noch ein anderes Problem: dummerweise sehe ich grade, das ein kleiner Teil der PLZ in Spalte A doch schon als Zahl formatiert ist (stehen rechtsbündig in der Zelle)...

Wie kann ich gleichzeitig mit der Zahl im Textformat und der Zahl im Zahlenformat aus einer Spalte rechnen?...

 

Hallo!

Dann wandel doch einfach alles in Text:

=SUMMENPRODUKT((TEXT($A$2:$A$1000;"00")=TEXT(F$3;"00"))*($B$2:$B$1000=$E8)*$C$2:$C$1000)

 

Hmm okay... ich weis Schatzi, du hast immer viel geduld mit mir auch wenn ich nochmal doof nachfrage aber dieses Vorspiel: (TEXT($A$2:$A$1000;"00") in der Formel wandelt mögliche Zahlen von Spalte A einheitlich in Text um, komme was wolle?

Müsste es nicht eigentlich so heißen, um die verschiedenen Längen zu berücksichtigen?
=SUMMENPRODUKT((TEXT($A$2:$A$1000;WIEDERHOLEN(0;LÄNGE($A$2:$A$1000)))=TEXT(F$3;WIEDERHOLEN(0;LÄNGE(F$3)))*($B$2:$B$1000=$E8)*$C$2:$C$1000)

LG, Mat

 

Richtig!

Ich war tippfaul...

 

Guten Morgen,

noch 2 Sachen:

1.) Hab jetzt nochmal geschaut und meine Formel, die dynamisch die Länge und das Textformat der Zelle berücksichtigen sollte, scheint doch nicht zu funktionieren:
=SUMMENPRODUKT((TEXT($A$2:$A$1000;WIEDERHOLEN(0;LÄNGE($A$2:$A$1000)))=TEXT(F$3;WIEDERHOLEN(0;LÄNGE(F$3)))*($B$2:$B$1000=$E8)*$C$2:$C$1000)

Im Ergebnis schreibt er die Formel als Text in die Zelle.

Die hier scheint aber zu funktionireren:
=SUMMENPRODUKT(($A$2:$A$1000=TEXT(F$3;WIEDERHOLEN(0;LÄNGE(F$3))))*($B$2:$B$1000=$E8)*$C$2:$C$1000)
Weis aber nicht, ob das Ergebnis dann korrekt ist?

2.) NEU: In Spalte D stehen jetzt Personentypen zwischen 1 und 10. Ich will nun in der Summenproduktformel aber nur für die Personentypen 2 und 3 die Personenanzahl aus Spalte C addieren. Muss ich jetzt die Formel so lang hier machen:
=SUMMENPRODUKT(($D$2:$D$1000=3)*($A$2:$A$1000=TEXT(F$3;WIEDERHOLEN(0;LÄNGE(F$3))))*($B$2:$B$1000=$E8)*$C$2:$C$1000)+
SUMMENPRODUKT(($D$2:$D$1000=2)*($A$2:$A$1000=TEXT(F$3;WIEDERHOLEN(0;LÄNGE(F$3))))*($B$2:$B$1000=$E8)*$C$2:$C$1000)

oder gibt es noch eine rechenzeitärmere und kürzere Formel? (Faktisch steht das "+" für UND)


LG, Mat

 

Hallo!

1. Deine erste Formel hat 9 geöffnete, aber nur 8 geschlossene Klammern...
Da stimmt was nicht!

2.
=SUMMENPRODUKT((($D$2:$D$1000=3)+($D$2:$D$1000=2))*($A$2:$A$1000...

Achtung: Faktisch steht das "+" für ODER!!!!