Live-Forum - Die aktuellen Beiträge
Datum
Titel
25.04.2024 16:50:59
2 Zellen in 2 Blättern mit unterschiedlichen Bereichen vergl
25.04.2024 15:03:35
Unterschiedliche Dateien miteinander abgleichen lassen
25.04.2024 14:05:48
Makro in aktueller Zelle ausführen mit Doppelklick
25.04.2024 12:56:25
Werte von Kunden und Artikel stehen untereinander
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1400to1404
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1400to1404
1400to1404
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Frage bzgl Sverweis

Frage bzgl Sverweis
14.01.2015 10:11:07
Langmantl
Hallo,
habe den Auftrag eine Tabelle zu erstellen, die Newsletteradressen verwaltet. Es geht darum, dass die Dokumente nach Postleitzahlen sortiert sind (Also 0123 oder 0124). Ich will Auswerten lassen, wie viele Adressen jeweils in dem Bereich der einzelnen PLZ gebiete sind, also entsprechend der ersten beiden Ziffern. Also zum Beispiel 01... 10 Exemplare, 02... 3 Exemplare, 03... 4 Exemplare.
Jetzt meine Frage, ich habe da an die Sverweis und Zählenwenn Formel gedacht. Allerdings geht das auch nur mit Teildaten? Also nicht die komplette Postleitzahl sondern die ersten beiden Ziffern sollen selektiert werden.
Geht das mit meinem Denkansatz, oder gibts da noch andere komfortablere Lösungen?
Gruß aus Berlin
Benedikt

23
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Frage bzgl Sverweis
14.01.2015 10:14:29
SF
Hola,
das ginge z.B. so:
=SUMMENPRODUKT((LINKS(A1:A100;2)="01")*1)
Gruß,
steve1da

AW: Frage bzgl Sverweis
14.01.2015 10:39:12
Langmantl
Hallo, danke, könntest du mir die Formel noch näher erläutern?, Wie muss man die Einbauen, wenn ich eine Wenn Funktion mit implementieren möchte, also nur Anzeigen, wenn vorhanden

AW: Frage bzgl Sverweis
14.01.2015 10:41:05
SF
Hola,
die Formel zählt die Einträge, deren ersten beiden Zeichen von Links 01 sind.
Kannst du das mit dem Wenn mal etwas näher erläutern?
Gruß,
steve1da

AW: Frage bzgl Sverweis
14.01.2015 10:40:56
Langmantl
Hallo, danke, könntest du mir die Formel noch näher erläutern?, Wie muss man die Einbauen, wenn ich eine Wenn Funktion mit implementieren möchte, also nur Anzeigen, wenn vorhanden

Anzeige
AW: Frage bzgl Sverweis
14.01.2015 10:39:53
UweD
Hallo
so z.B.
Tabelle1
 AB
17301990Großkmehlen
17401990Frauendorf
17501993Schipkau
17601994Schipkau
17701998Schipkau
17802625Bautzen
17902627Radibor
18002627Hochkirch
18102627Kubschütz
18202627Weißenberg
18302633Göda
18402633Doberschau-Gaußig
18502681Kirschau
18602681Wilthen



Tabelle2
 AB
1PLZ 
201176
302103
403156
504214
6050
706706
807281
908100
1009130
111059
12110
131267
141349
1514190

verwendete Formeln
Zelle Formel Bereich
B2 =SUMMENPRODUKT((LINKS(Tabelle1!A:A;2)=A2)*1) 

Tabellendarstellung in Foren Version 5.49


Gruß UweD

Anzeige
AW: Frage bzgl Sverweis
14.01.2015 10:42:35
UweD 

=WENN(SUMMENPRODUKT((LINKS(Tabelle1!A:A;2)=A2)*1)>0;SUMMENPRODUKT((LINKS(Tabelle1!A:A;2)=A2)*1);"")

AW: Frage bzgl Sverweis
14.01.2015 10:57:15
Langmantl
Ich danke Euch allen, das hat mir echt weitergeholfen
Gruß aus Berlin

AW: Frage bzgl Sverweis
14.01.2015 10:59:03
Langmantl
Ich danke Euch allen, das hat mir echt weitergeholfen
Gruß aus Berlin

schneller...
14.01.2015 11:06:20
UweD
Hallo nochmal
Summenprodukt dauert lange..
Das ist schneller
=WENN(ZÄHLENWENN(Tabelle1!A:A;Tabelle2!A2&"*")>0;ZÄHLENWENN(Tabelle1!A:A;Tabelle2!A2&"*");"")

LG UweD

Anzeige
AW: schneller...
14.01.2015 11:13:50
Langmantl
Hallo,
danke dir Uwe, aber wenn ich auf Tabelle 1 (1) die komplette spalte G abgreife und auf Tabelle 2 (Auswertung) Spalte b3, zeigt er mir einen Bezugsfehler an
Gruß
Benedikt

AW: schneller...
14.01.2015 11:30:30
Langmantl
Hallo Uwe, habe folgendes Problem, ich füge deine Formel ein und ändere Sie enstrechend
=WENN(ZÄHLENWENN(Tabelle1!G:G;Tabelle2!b3&"*")>0;ZÄHLENWENN(Tabelle1!G:G;Tabelle2!b3&"*");"")
Er zeigt mir immer entweder Wert oder Bezug an, was ist da verkehrt?
Gruß Benedikt

AW: schneller...
14.01.2015 11:56:05
UweD
Hallo
sowohl Tabelle1 Spalte G als auch Tabelle2 Spalte B müssen als Text markiert sein.
inkl. der führenden 0 bei 4 stelligen PLZ.
Tabelle1
 G
212345
312345
401234
501145
6 
7 



Tabelle2
 BC
3012

verwendete Formeln
Zelle Formel Bereich
C3 =WENN(ZÄHLENWENN(Tabelle1!G:G;Tabelle2!B3&"*")>0;ZÄHLENWENN(Tabelle1!G:G;Tabelle2!B3&"*");"") 

Tabellendarstellung in Foren Version 5.49


Anzeige
AW: schneller...
14.01.2015 12:08:14
Langmantl
Hallo Uwe, habe ich alles so formatiert, allerdings hat er immer noch das Problem, kann das daran liegen? das die Bearbeiter der Spalte auch andere Zeichen drin haben? z. B. anstatt Postleitzahl steht dort die Bezeichnung Postfach XXXXXX. Leider habe ich darauf keinen Einfluss, da die Pflege der Datenbank wo anders gemacht wird.
Oder kann es an der Datenmenge liegen? fast 3000 Datensätze?
ich schicke dir mal die Datei, dann kannst du das selber sehen, dass es nicht funktioniert, vielleicht siehst du dann, wo der Fehler ist.
Gruß Benedikt
https://www.herber.de/bbs/user/94994.xlsx

Anzeige
AW: schneller...
14.01.2015 15:45:48
UweD
Du hast doch die Tabellblätter anders benannt.
B3=WENN(ZÄHLENWENN(Datenbank!G:G;B3&"*")>0;ZÄHLENWENN(Datenbank!G:G;B3&"*");"")

LG UweD

AW: schneller...
14.01.2015 16:17:15
UweD
Nicht B3=...
sondern die Formel natürlich in C3 einfügen

AW: schneller...
15.01.2015 07:47:45
Langmantl
Hallo Uwe, Danke, die Formel funktioniert soweit, allerdings habe ich in der Datenbank in der Spalte G auch Bezeichnungen, die nicht mit PLZ sondern Postfach (***) Bsp Postfach 14456
lauten, wie muss ich die Formel erweitern, dass die auch extra gezählt werden z. B. als sonstiges
Gruß
Benedikt

Anzeige
AW: schneller...
15.01.2015 07:48:02
Langmantl
Hallo Uwe, Danke, die Formel funktioniert soweit, allerdings habe ich in der Datenbank in der Spalte G auch Bezeichnungen, die nicht mit PLZ sondern Postfach (***) Bsp Postfach 14456
lauten, wie muss ich die Formel erweitern, dass die auch extra gezählt werden z. B. als sonstiges
Gruß
Benedikt

AW: Frage bzgl Sverweis
14.01.2015 11:19:35
Daniel
Hi
kommt darauf an, wie deine Postleitzahlen vorliegen.
Wenn es Texte sind (ausrichtung links, wenn als Standard formatiert), dann einfach für jeden Bereich mit: 
=ZählenWenn(A:A;"01*")

wenn es Zahlen sind (ausrichtng rechts, wenn als Standard formatiert), dann so: 
=ZählenWennS(A:A;">=1000";A:A;"

alternativ kannst du die PLZ-Gruppe auch per Formel in einer Hilfsspalte ermittlen: 
=Ganzzahl(A2/1000)

bzw wenn PLZ als Text: 
=Links(A2;2)

und dann wertest du die Tabelle über die Pivottabelle nach dieser Hilfsspalte und "Anzahl" aus.
das geht am schnellsten und du musst nicht für jeden Postzahlbereich die Formeln schreiben.
Es wird auch automatisch ermittelt, welche Bereiche überhaupt verwendet werden.
Gruß Daniel

Anzeige
AW: Frage bzgl Sverweis
14.01.2015 12:44:12
Langmantl
Hallo Daniel, die Lösung mit der Pivotabelle gefällt mir, wenn ich jetzt die PLZgruppen auswerten lasse in einer pivot, das macht er, wie bekomme ich jetzt aber auch noch die Anzahl angezeigt? da ich nicht vielk erfahrung mit Pivottabellen habe, bitte ich dich um Hilfe

AW: Frage bzgl Sverweis
14.01.2015 13:37:03
Daniel
Hi
ziehe im Pivot-Assistenten eine Spalte in das Feld "Werte". Sollte es eine Text-Spalte sein, wird wahrscheinlich schon automatisch nach Anzahl ausgewertet. Ist es eine Spalte mit Zahlenwerten, wird wahrscheinlich nach Summe ausgewertet. Das kannst du ändern, in dem du im Wertefeld auf den Button klickst und im Kontextmenü "Wertfeldeinstellungen" die entsprechende Zusammenfassungsart auswählst.
ich sehe gerade, du kannst auch ohne Hilfsspalte direkt nach den ersten 2 Stellen auswerten:
1. Erstelle die Pivottabelle und Werte für die PLZ nach Anzahl aus.
2. klicke auf die erste Spalte der Pivottabelle (PLZ) mit der rechten Maustaste und wähle im Kontextmenü "Gruppieren" aus, gib ein
- Starten: 0
- Beenden: 99999
- Nach: 1000
dann wird die Auswertung nach deinen Wünschen gruppiert.
Gruß Daniel
Gruß Daniel

Anzeige
AW: Frage bzgl Sverweis
14.01.2015 14:07:04
Langmantl
Also entweder habe ich nichts verstanden oder ....
ich markiere in der Haupttabelle alle Postleitzahlen, dann gehe ich auf erstellen Pivottabelle das mit dem Gruppieren hat nicht funktioniert, ich habe die Datei mal beigefügt, vielleicht kannst du damit was anfangen
Gruß Benedikt
https://www.herber.de/bbs/user/94996.xlsx

AW: Frage bzgl Sverweis
14.01.2015 14:40:33
Langmantl
Also entweder habe ich nichts verstanden oder ....
ich markiere in der Haupttabelle alle Postleitzahlen, dann gehe ich auf erstellen Pivottabelle das mit dem Gruppieren hat nicht funktioniert, ich habe die Datei mal beigefügt, vielleicht kannst du damit was anfangen
Gruß Benedikt
https://www.herber.de/bbs/user/94996.xlsx

Anzeige
AW: Frage bzgl Sverweis
14.01.2015 14:50:10
Daniel
Hi
das mit dem Gruppieren funktioniert nicht, weil deine PLZ Texte sind und keine Zahlen.
Dann musst du schon mit der Hilfsspalte arbeiten und dort nur die zwei Linken Zeichen anzeigen oder du musst deine PLZs in Zahlen wandeln.
Ausserdem solltste du hier keine Echtadressen veröffentlichen!
Name und Strasse sind für das Problem ja unwichtig, da könntest du für das Beispiel auch einfach "xxxx" eintragen.
Gruß Daniel

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige