Microsoft Excel

Herbers Excel/VBA-Archiv

Geburtstage etc.

Betrifft: Geburtstage etc. von: Ingo
Geschrieben am: 10.08.2008 15:41:16

Hallo !!!

Ich habe folgendes Problem:

Ich habe ca. 800 Datensätze/-zeilen in der Tabelle "Gesamt".

- in Spalte E steht das Bundesland (z. B. NRW)
- in Spalte Q steht der Name u. Vorname
- in Spalte Y steht der Geburtstag des jeweilen
- in Spalte Z steht eine Zahlenkombination (z. B. 2359-5550)
- sollte in der Spalte Z eine Buchstabenkombination stehen, soll diese Person nicht mitgezählt werden

Nun möchte ich in einer zweiten Tabelle "Geburtstage" folgendes zusammengezählt haben:

- in Spalte A stehen die Bundesländer (ab A2 abwärts)
- in B1 steht der erste von mir bestimmte Geburtstag
- in C1 steht der zweite von mir bestimmte Geburtstag
- in D1 steht der dritte von mir bestimmte Geburtstag u s w.

Nun soll die Anzahl der Personen in B2 gezählt werden, die am gleichen Tag Geburtstag haben, wie ich ihn in B1 definiert habe (in C2 wie in C1 definiert, in D2 wie in D1 definiert usw.) und dies für jedes Bundesland einzeln.

Nun soll dieses Ergebnis noch verfeinert werden und zwar wie folgt:
- nun benötige ich für die Summe in B2 ab B50 abwärts eine Auflistung der Zeilen die gezählt wurden (für C2 ab C50 abwärts, für D2 ab C50 abwärts usw.)

Ich hoffe, ich habe mich verständlich ausgedrückt !?!

Des weiteren hoffe ich auf Eure Hilfe !!!

Vielen Dank im voraus !!!

ingo

  

Betrifft: AW: Geburtstage etc. von: {Boris}
Geschrieben am: 10.08.2008 16:05:01

Hi Ingo,

wie sind die Geburtstage eingetragen: TT.MM.JJJJ oder nur TT.MM? Und wie sollen sie demnach gezählt werden?

Und den zweiten Teil

Nun soll dieses Ergebnis noch verfeinert werden und zwar wie folgt:
- nun benötige ich für die Summe in B2 ab B50 abwärts eine Auflistung der Zeilen die gezählt wurden (für C2 ab C50 abwärts, für D2 ab C50 abwärts usw.)


versteh ich nicht.

Grüße Boris


  

Betrifft: AW: Geburtstage etc. von: Ingo
Geschrieben am: 10.08.2008 18:01:18

Hallo Boris!

1. Geburtstage sind TT.MM.JJ und meine definierte Angabe in B1 ist auch in TT.MM.JJ angebeben.
2. Es sollen die Zeilenbezeichnung in denen das Geburtsdatum mit dem Geburtsdatum, welches gesucht wird abwärts aufgelistet werden.

z.B. B2:
- gesuchter Geburtstag : 01.07.08
- in den Zeilen 123, 156, 234, 789, 2587 sind diese Geburtstage vorhanden

Zelleninhalt B2: 5

Zelleninhalt B50: 123 (oder: Zeile 123)
Zelleninhalt B51: 156 (oder: Zeile 156)
Zelleninhalt B52: 234 (oder: Zeile 234)
Zelleninhalt B53: 789 (oder: Zeile 789)
Zelleninhalt B54: 2587 (ode:r Zeile 2587)

Ich hoffe, das dies nun verständlicher ist !!!???!!!

Vielen Dank im voraus !!!


ingo


  

Betrifft: Mit Formeln - auf die Schnelle von: {Boris}
Geschrieben am: 10.08.2008 18:15:32

Hi Ingo,

Dein Blatt mit den Ausgangsdaten heißt "Tabelle1". Jetzt trägst Du in Deinem Zusammenfassungsblatt in B2 ein:

=SUMMENPRODUKT((Tabelle1!$E$1:$E$1000=$A2)*(Tabelle1!$Y$1:$Y$1000=B$1) *ISTZAHL(LINKS(Tabelle1!$Z$1:$Z$1000)+0))

Formel runter und nach rechts kopieren.

In B50 kommt die ARRAYformel:

{=WENN(ZEILEN($1:1)>SUMME((Tabelle1!$Y$1:$Y$1000=B$1)*ISTZAHL(LINKS(Tabelle1!$Z$1:$Z$1000)+0));""; KKLEINSTE(WENN((Tabelle1!$Y$1:$Y$1000=B$1)*ISTZAHL(LINKS(Tabelle1!$Z$1:$Z$1000)+0);ZEILE($1:$1000)); ZEILEN($1:1))) }

Formel auch runter und nach rechts kopieren.

{geschweifte Klammern} nicht eingeben, sondern Formeleingabe mit gleichzeitig Strg+Shift+Enter abschließen. Das erzeugt die { }.

Die Bereichsgröße (hier: bis Zeile 1000) musst Du anpassen. Aber Achtung: Die Formeln sind sehr rechenintensiv. Wunder Dich also nicht, wenn die Berechnung etwas dauert!

Grüße Boris


  

Betrifft: AW: Mit Formeln - auf die Schnelle von: Ingo
Geschrieben am: 10.08.2008 20:13:37

Hallo Boris !

Super, das funktioniert ...

bis auf, dass nun sämtliche Zeilen aufgelistet werden, die mit dem Geburtsdatum übereinstimmen.
Dieses möchte ich aber nur im Bezug auf die auch zutreffenden Bundesländer haben.

z.B. B2:
- gesuchter Geburtstag : 01.07.08
- in den Zeilen 123, 156, 234, 789, 2587 sind diese Geburtstage vorhanden
- aber nur die Zeilen 123, 234, 2587 entsprechen die von mir gesuchten Bundesländer

A2= Berlin # Zelleninhalt B2: 1
A3= Frankreich # Zelleninhalt B3: 0
A4= NRW # Zelleninhalt B4: 2
A5= Schleswig-Holstein # Zelleninhalt B5: 1
A6= USA # Zelleninhalt B6: 0
A7= Hamburg # Zelleninhalt B7: 1

Zelleninhalt B50: 123 (oder: Zeile 123)
Zelleninhalt B51: 234 (oder: Zeile 234)
Zelleninhalt B52: 2587 (oder Zeile 2587)

Weiterer Hinweis:
Die Spalte Z soll nur zum Bechnungsausschluss dienen, wenn dort eine Buchstabenkombination eingegeben wurde wie z. B. "R/AL" oder "NF"; bei einer Zahlenkombination (z.B. 2359-4561) oder wenn sie leer, ist dies kein Ausschlusskriterium.

Ich hoffe nun ist alles klar !!!

Tausend Dank für Deine Bemühungen !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!


ingo


  

Betrifft: AW: Mit Formeln - auf die Schnelle von: Ingo
Geschrieben am: 10.08.2008 20:37:46

Hallo Boris !!!

Und noch eine kleine Erweiterung zu meiner voherigen Anfrage von 20.13 Uhr:

Wie sieht die Formel ohne der Spalte Z aus !!!


Vielen dank im voraus !!!


ingo


  

Betrifft: Lad doch mal ne Beispielmappe hoch... von: {Boris}
Geschrieben am: 10.08.2008 20:48:39

Hi Ingo,

...und zwar mit den exakten Anordnungen.

Dieses möchte ich aber nur im Bezug auf die auch zutreffenden Bundesländer haben.

Woraus gehen denn die "zutreffenden" Bundesländer hervor?

Grüße Boris


  

Betrifft: AW: Lad doch mal ne Beispielmappe hoch... von: Ingo
Geschrieben am: 10.08.2008 21:36:34

Hallo Boris !!!

Beispieldatei unter: https://www.herber.de/bbs/user/54495.xls


ingo


  

Betrifft: Angepasst... von: {Boris}
Geschrieben am: 10.08.2008 21:58:55

Hi Ingo,

Spalte Z hab ich jetzt mal ganz außen vor gelassen.

B2:

=SUMMENPRODUKT((Gesamt!$E$5:$E$1000=$A2)*(Gesamt!$Y$5:$Y$1000=B$1))

B14 (ARRAYformel!):

{=WENN(ZEILEN($1:1)>SUMME((Gesamt!$Y$5:$Y$1000=B$1)*ISTZAHL(FINDEN(MTRANS($A$2:$A$8); Gesamt!$E$5:$E$1000)));"";KKLEINSTE(WENN((Gesamt!$Y$5:$Y$1000=B$1)*ISTZAHL(FINDEN(MTRANS($A$2:$A$8); Gesamt!$E$5:$E$1000));ZEILE($5:$1000)); ZEILEN($1:1))) }

Beide Formeln sowohl runter als auch nach rechts kopieren.

Grüße Boris


  

Betrifft: AW: Angepasst... von: Ingo
Geschrieben am: 10.08.2008 22:24:07

Hallo Boris !!!

Vielen Dank !!!!!!!!!!!!!!!!!!!!!!!!!!

Es funktioniert !!!!!!!!!!!!!!!!!!!!!!!!



ingo


  

Betrifft: AW: Angepasst... Unstimmigkeit !!! von: Ingo
Geschrieben am: 11.08.2008 18:32:11

Hallo Boris !!!

Leider musste ich heute feststellen, dass die Formel noch einen kleinen Fehler hat.

Beispieldatei inkl. Fehlererklärung sind hochgeladen:
https://www.herber.de/bbs/user/54522.xls

Ich hoffe Du kannst mir weiterhelfen!?!

Vielen Dank im voraus !!!


ingo


  

Betrifft: AW: Angepasst... Unstimmigkeit !!! von: {Boris}
Geschrieben am: 11.08.2008 20:04:22


Hi Ingo,

ersetze ISTZAHL durch ANZAHL, und zu Beginn auf >0 prüfen - mein Fehler, sorry:

B14:

{=WENN(ZEILEN($1:1)>SUMME((Gesamt!$Y$5:$Y$1000=B$1)*(ANZAHL(FINDEN(MTRANS($A$2:$A$9); Gesamt!$E$5:$E$1000))>0));"";KKLEINSTE(WENN((Gesamt!$Y$5:$Y$1000=B$1) *ANZAHL(FINDEN(MTRANS($A$2:$A$9); Gesamt!$E$5:$E$1000));ZEILE($5:$1000)); ZEILEN($1:1))) }

und wieder runter und nach rechts kopieren.
Den aktiven Filter auch noch zu berücksichtigen grenzt dabei an Unmöglichkeit.

Grüße Boris


  

Betrifft: AW: Angepasst... Unstimmigkeit !!! von: Ingo
Geschrieben am: 11.08.2008 21:01:42

Hallo Boris !!!

Es funktioniert !!!

Vielen Dank !!!


ingo


  

Betrifft: AW: Angepasst... Unstimmigkeit !!! von: Ingo
Geschrieben am: 12.08.2008 18:03:06

Hallo Boris !!!

Leider musste ich heute feststellen, dass die Formel immer noch einen kleinen Fehler hat.
Beispieldatei inkl. Fehlererklärung sind hochgeladen:
https://www.herber.de/bbs/user/54547.xls
Ich hoffe Du kannst mir mal wieder weiterhelfen!?!
Vielen Dank im voraus !!!



ingo


  

Betrifft: AW: Angepasst... Unstimmigkeit ..falsche Datei ! von: Ingo
Geschrieben am: 12.08.2008 18:08:09

Hallo Boris !!!
Ich habe eine falsche Fehlererklärung erstellt !!!
Richtige Datei wie folgt:
https://www.herber.de/bbs/user/54548.xls

ingo


  

Betrifft: AW: wenn ich nochmal die Pivottabelle von: Daniel
Geschrieben am: 10.08.2008 22:16:08

Hi
... ins Spiel bringen dürfte, damit die die komplette Auswertung in 2 Minuten erledigt, incl. dem Auswählen der Bundesländer, geht alles per KLICK - DRAG und DROP.
auch die Spalte BERN lässt sich noch dazu bringen, einfach im Assisteten im Layout an die entsprechende Stelle ziehen.
https://www.herber.de/bbs/user/54496.xls
Gruß, Daniel


  

Betrifft: AW: wenn ich nochmal die Pivottabelle von: Ingo
Geschrieben am: 10.08.2008 22:27:23

Hallo Daniel !!!

Vielen Dank !!!


ingo


  

Betrifft: AW: Geburtstage etc. von: Daniel
Geschrieben am: 10.08.2008 16:13:28

Hi
ich würde das ganze mit Pivot-Tabellen auswerten.
das geht schnell und einfach.
allerdings wird die Anordnung der Zellen vielleicht etwas anders sein, als du das jetzt beschrieben hast, aber falls dir das wichtig ist, musst du dich mit diesen Formeln rumschlagen, um die Daten auszuwerten (ZählenWenn mit mehren Bedingungen) : http://www.excelformeln.de/formeln.html?welcher=273

Gruß, Daniel


 

Beiträge aus den Excel-Beispielen zum Thema "Geburtstage etc."