Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1876to1880
1876to1880
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Auswertung Anwesenheit in einem Zeitraum

Forumthread: Auswertung Anwesenheit in einem Zeitraum

Auswertung Anwesenheit in einem Zeitraum
13.04.2022 18:31:54
MaMe
Hallo Zusammen,
brauche Hilfe, da ich einfach nicht das Ziel erreiche... geht um folgendes...
Habe eine Tabelle in der Mitgliedsnamen stehen (Spalte A2:A500), in Spalte B steht das Eintrittsdatum, in Spalte steht das Austrittsdatum - alternativ ist Spalte C 'leer' wenn die Mitgliedschaft noch besteht.
In Spalte D steht ein Kürzel für die Art der Mitgliedschaft, 1 z.B. für Vollmitglied, während in Spalte E ggf. eine Zusatzinfo steht, 'R' steht z.B. für ruhend
Nun benötige ich eine Auswertung wieviele Mitglieder es für den Monat 01-2021 (02-2021, 03-2021) usw. gab --
((dabei ist der Tag des Ein-/Austritt irrelevant - der Monat bzw. die Mitgliedschaft wird jeweils 'ganz' gezählt)) -- abhängig vom Eintrag in Spalte D.
Ich benötige also für jeden Monat einzeln die Anzahl der 'Voll',Mitglieder, der 'Teil'-Mitglieder und der 'ruhenden' Mitglieder
Spalte A • Spalte B • Spalte C • Spalte D • Spalte E
Maxi Muster • 15.02.2010 • 13.04.2022 • 1
Moritz Mann • 25.11.2021 • 31.12.2021 • 0,5
Brian Master • 01.01.2021 • 'leer' • 1 • R ab 01.09.2021
Marco Aster • 01.01.2022 • 01.04.2022 • 1
Das Ergebnis für '1' und Januar 2021 wäre im Beispiel '2', da sowohl Maxi Muster als auch Brian Master im Januar Vollmitglied waren. Das Ergebnis für 09/2021 wäre hier dann '1', da Brian Master 'ruht' und in dieser Auswertung nicht mehr mitgezählt wird.
Habe mich mit Summenprodukt versucht - allerdings werden einige Mitglieder offensichtlich 'doppelt' gezählt und das mit dem >R ab 01.09.2021 PS: in Zelle N2 ist der Monat Januar 2021 (01.01.2021) angelegt, in Zelle N3 entsprechend Februar 2021 (01.02.2021) usw.
Meine Formel:

=SUMMENPRODUKT(($B$2:$B$500>=$N2)*($C$2:$C$500=$N2)*($C$2:$C$500="") +(($F$2:$F$200
Wie muß die Formel aussehen damit das klappt - wo liegt mein Fehler und wie krieg ich noch ein Kriterium ("R") dazu ?
Danke im voraus
Anzeige

7
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Auswertung Anwesenheit in einem Zeitraum
13.04.2022 20:00:20
onur
Poste eine Datei oder sollen wir sie anhand der Beschreibung selbst nachbauen?
Ich könnte dir die Formel aber auch beschreiben: Fängt mit "=ZählenWenns" an und hört mit ")" auf. :)
AW: Angaben sind nicht eindeutig ...
13.04.2022 20:06:07
neopa
Hallo MaBe,
... Du wertest mit Deiner Formel einen Bereich in Spalte F aus hast dort aber keine Werte zu stehen bzw. keine Angaben dazu vorgegeben.
Auch sollten auszuwertende Datumsangaben nur als solche angegeben werden, also die "Ruhendstellung" ohne Zusatztexte.
Mein Formelvorschlag wäre für meine Interpretation Deiner Daten dann wie folgt. Formeln nach unten ziehend kopieren.
Arbeitsblatt mit dem Namen 'Tabelle1'
 ABCDEFGHN
1NameabbisTypruhend ab MGR-MGMonat
2Maxi Muster15.02.201013.04.20221  2001.21
3Moritz Mann25.11.202131.12.20210,5  2002.21
4Brian Master01.01.2021 101.09.2021 2003.21
5Marco Aster01.01.202201.04.20221  3004.21
6Ernst Meier07.04.2021 1  3005.21
7      3006.21
8      3007.21
9      0008.21
10      2109.21
11      2110.21
12      2,5111.21
13      2,5112.21
14         

ZelleFormel
G2{=SUMME(((B$2:B$500=MONATSENDE(N2;0))*((C$2:C$500+(WENN(E$2:E$500="";HEUTE();E$2:E$500-TAG(E$2:E$500))))>=N2))*D$2:D$500)}
H2=SUMMENPRODUKT((E$2:E$500=MONATSENDE(N2;0))*(E$2:E$500>0)*D$2:D$500)
Achtung, Matrixformel enthalten!
Die geschweiften Klammern{} werden nicht eingegeben.
Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Gruß Werner
.. , - ...
Anzeige
AW: Angaben sind nicht eindeutig ...
15.04.2022 15:52:40
MaMe
Hallo Onur & Hallo Werner,
@Onur - sorry... bastle schon recht lange an dem 'Teil' rum, das sich die Tabelle fast schon bildlich im Kopf befindet...
@Werner:
SORRY! Du hast Recht... allerdings habe ich die Formel aus meiner 'Originaltabelle' genommen und dann das $F$ übersehen (wäre $B$ gewesen) - denn die Spalte ist tatsächlich 'gefüllt'...
habe Deinen Vorschlag nachgebildet - funktioniert soweit auch - besonders das mit dem Ruhend ! - hierfür schonmal ein großes DANKE
Leider werden die Mitglied-Anzahlen nicht so abgebildet wie ich sie benötige - mein Fehler, habe das nicht klar formuliert :-(
Es gibt verschiedene Abstufungen '1', '0,75', '0,5' und '0,1' - abhängig von der Anzahl der Stunden die eingebracht werden - so kann sich das während der Zugehörigkeit auch mal ändern - dann wird z.B. aus Typ '1' ein Typ '0,5' - angepasst auf Deinen Vorschag stünde diese Info in Spalte 'F' - das Änderungsdatum käme dann in Spalte 'D'
hier die nachgebildete und ergänzte Tabelle:
https://www.herber.de/bbs/user/152465.xlsx
schon mal Danke im Voraus :-)
Anzeige
AW: wäre auch realisierbar ...
17.04.2022 09:26:02
neopa
Hallo MaMe,
... allerdings sind nun Deine Angaben in E1:F1 noch nicht eindeutig. Sollte in in E1 nicht "Änderung ab" und in F1 "Änderungsart" stehen, oder? Denn so wie von Dir bisher dargestellt, ist das "R" in F4 eine "Doppelung" und die Angaben E8 und F8 nicht eindeutig zu deuten.
Auch sollten zu einer etwas vereinfachenden Formeldefinition in O1:R1 nur Zahlenwerte stehen, welche bei Bedarf durch benutzerdefiniertes Zahlenformat so formatiert werden könnten, dass sie wie von Dir in Zelle erfasst dargestellt werden. Also z.B. in P1 und Q1 das benutzerdefinierte Zahlenformat: "Teil "0,00 oder einfach für die Textwerte eine Zeile vor die 1. Zeile eingefügt wird. Wäre das so ok?
Gruß Werner
.. , - ...
Anzeige
AW: wäre auch realisierbar ...
20.04.2022 15:53:43
MaMe
Hallo Werner,
zu 1.: japp, E1 = 'Änderung ab' und F1 = 'Änderungsart' • Es gibt nicht nur 'Ruhend' (R), sonderen auch andere Gründe der 'Änderung' wie EZ oder LZ - was gleichbedeutend mit 'ruhend' ist - aber es gibt eben auch Änderungen von Voll (1) zu Teil (0,75) usw. ... - hatte diese Infos bis zu Deiner Antwort in einer Zelle stehen - habe diese dann getrennt in E1 und F1, in der Hoffnung das eine zusätzliche 'Hilfsspalte' ausreicht
zu 2.: (wieder mal sorry! hätte ich dazuschreiben sollen) in Spalte O bis R (bzw. S) sollen die Ergebnise aus der/den Formeln stehen, habe deshalb ich hier nur händisch gezählte Ergebnisse drin, da die Formel noch nicht ganz passt... - sollen eben keine '2,5' Mitglieder bei O12 stehen haben, sondern '2' bei O12 und '1' bei Q12 - weil man die Mitglieder nicht 'teilen' kann ;-)
Muß die Auswertung auch noch für 2020 und 2019 vornehmen - sobald die Tabelle 'steht' wird das dann jemand anders übernehmen... (also für 2022 etc...)
Die Tabelle ansich ist recht umfangreich - und die Auswertung der 'Anzahl' der Mitglieder ist nur ein Bruchteil der Infos, die hier enthalten sind bzw. daraus gezogen werden...
PS.: für jedes Folgejahr wird die laufende Tabelle kopiert und lediglich mit der neuen Jahreszahl versehen... in der Tabelle stehend alle Mitglieder drin die es gibt und GAB - sie wird auch für Serienbriefe genommen und für vieles anderes - die ausgeschiedenen Mitglieder bleiben drin, weil manche wieder eintreten und man dann auf den 'Altdaten' weiter aufbaut und weil sie ggf. immer noch noch mit Post beehrt werden ;-)
Anzeige
AW: dann teste mal ...
20.04.2022 17:59:46
neopa
Hallo MaMe,
... folgende Formeln H2:J2 und N2 ziehend nach unten kopieren und die Formeln aus Spalte J anschließend noch nach rechts bis Spalte M. (Die farbig hinterlegten Datenwerte habe ich zu Testzwecken mal abgeändert). In I1 brauchst Du nur noch die Jahreszahl einschreiben.
Arbeitsblatt mit dem Namen 'Auswert_2'
 ABCDEFGHIJKLMN
1NameEintrittAustrittTypabÄnderung Hilfsspalte202110,750,50,1ruhend
2Maxi Muster15.02.201013.04.20221   13.04.22Januar2    
3Moritz Mann25.11.202131.12.20210,5   31.12.21Februar2    
4Brian Master01.01.2021 101.09.2021LZ 01.09.21März2    
5Marco Aster01.01.202204.04.20221   04.04.22April3    
6Ernst Meier07.04.2021 115.06.20210,75 15.06.21Mai3    
7Ilse Sommer01.12.2021 0,1   20.04.22Juni31   
8Elke Winter01.12.202131.05.20220,7501.10.20210,1 01.10.21Juli21   
9        August21   
10        September21  1
11        Oktober11 11
12        November11111
13        Dezember11121

ZelleFormel
H2=(WENN(C2="";WENN(E2;E2;HEUTE());WENN(E2="";C2;MIN(C2;E2))))*(B2>0)
I2=(ZEILE(A1)&"-"&I$1)+0
J2=SUMMENPRODUKT((($B$2:$B$499=MONATSENDE($I2;0))*(($H$2:$H$499-TAG($H$2:$H$499)+1)>=$I2)*($D$2:$D$499=J$1)+ISTZAHL($F$2:$F$499)*(($H$2:$H$499-TAG($H$2:$H$499)+1=$I2)*(WECHSELN(WECHSELN(WECHSELN($F$2:$F$499&"R";"R";0);"EZ";0);"LZ";"")+0)=J$1)))
N2=SUMMENPRODUKT((E$2:E$499=MONATSENDE(I2;0))*(E$2:E$499>0)*(WECHSELN(WECHSELN(F$2:F$499;"L";"E");"EZ";"R")="R"))
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Gruß Werner
.. , - ...
Anzeige
AW: dann teste mal ...
06.05.2022 14:33:50
MaMe
Hallo Werner,
SORRY das ich erst jetzt antworte... andere Dinge gingen vor...
Formel funktioniert - ein dickes Danke hierfür - auch wenn ich so gar nicht weiß, was das mit dem 'WECHSELN' auf sich hat. Muß das Formelkonstrukt mal in einer ruhigen Phase austesten :-)
Formel/Tabelle steht und ne andere Lady kann das jetzt weiter pflegen/bearbeiten - nochmal Danke !!
Anzeige
Anzeige
Anzeige

Infobox / Tutorial

Auswertung der Anwesenheit in Excel

Schritt-für-Schritt-Anleitung

Um die Anwesenheit in Excel zu berechnen, folge diesen Schritten:

  1. Datenstruktur erstellen: Stelle sicher, dass deine Excel-Tabelle die folgenden Spalten hat:

    • A: Mitgliedsnamen
    • B: Eintrittsdatum
    • C: Austrittsdatum (oder leer, wenn die Mitgliedschaft besteht)
    • D: Typ der Mitgliedschaft (1 für Vollmitglied, 0,5 für Teilmitglied, R für ruhend)
    • E: Zusatzinfo (optional)

  2. Monatswerte definieren: Lege in einer separaten Spalte die Monate fest, für die du die Auswertung durchführen möchtest (z.B. Januar 2021 in N2, Februar 2021 in N3 usw.).

  3. Formel zur Berechnung der Anzahl der Mitglieder: Verwende die folgende Formel in einer Zelle, um die Anzahl der Vollmitglieder für den Monat zu berechnen:

    =SUMMENPRODUKT(($B$2:$B$500<=MONATSENDE(N2;0))*(($C$2:$C$500="") + ($C$2:$C$500>=N2))*($D$2:$D$500=1))

    Diese Formel zählt alle Vollmitglieder, die im gewählten Monat anwesend sind.

  4. Für Teil- und ruhende Mitglieder anpassen: Ändere das Kriterium in der Formel entsprechend, um die Anzahl der Teilmitglieder (0,5) oder ruhenden Mitglieder (R) zu zählen.

Häufige Fehler und Lösungen

  • Doppelte Zählung: Wenn Mitglieder doppelt gezählt werden, überprüfe die Datumsangaben in den Spalten B und C. Achte darauf, dass die Bedingungen in deiner Formel korrekt sind.

  • Falsche Formeln: Stelle sicher, dass du die richtigen Zellbezüge verwendest. Beispielsweise, wenn du in Spalte F Werte hast, die nicht berücksichtigt werden, ändere die Formel entsprechend.

  • Formatierung: Achte darauf, dass die Datumswerte als solche formatiert sind, um die korrekte Berechnung zu gewährleisten.

Alternative Methoden

  • Pivot-Tabellen: Du kannst auch eine Pivot-Tabelle verwenden, um die Anwesenheit zu analysieren. Ziehe die Mitgliedsnamen in die Zeilen und die Typen in die Spalten. Verwende die Zählfunktion für die Mitgliedsnamen, um die Anzahl der Mitglieder pro Typ zu ermitteln.

  • Filter-Funktion: Nutze die Filterfunktion von Excel, um nur die relevanten Datensätze für den gewählten Zeitraum anzuzeigen. So kannst du schnell die Anzahl der Mitglieder in einem bestimmten Zeitraum ermitteln.

Praktische Beispiele

  • Beispiel 1: Angenommen, du möchtest die Anzahl der Vollmitglieder für Januar 2021 zählen. Du setzt die Formel in Zelle O2:

    =SUMMENPRODUKT(($B$2:$B$500<=MONATSENDE(N2;0))*(($C$2:$C$500="") + ($C$2:$C$500>=N2))*($D$2:$D$500=1))

  • Beispiel 2: Um die ruhenden Mitglieder für den gleichen Zeitraum zu zählen, verwende:

    =SUMMENPRODUKT(($B$2:$B$500<=MONATSENDE(N2;0))*($D$2:$D$500="R"))

Tipps für Profis

  • Verwende benutzerdefinierte Formate: Du kannst benutzerdefinierte Zahlenformate verwenden, um die Anzeige deiner Mitgliedschaftstypen anzupassen.

  • Dynamische Bereiche: Nutze die Tabelle-Funktion in Excel, um dynamische Bereiche zu erstellen, die sich automatisch anpassen, wenn du neue Daten hinzufügst.

  • Makros: Wenn du regelmäßig die gleiche Auswertung durchführen musst, kann ein Makro hilfreich sein, um den Prozess zu automatisieren.

FAQ: Häufige Fragen

1. Wie kann ich die Anwesenheit für mehrere Monate gleichzeitig auswerten?
Du kannst die oben genannten Formeln in mehreren Zellen anpassen, indem du die Monatsreferenzen entsprechend änderst.

2. Warum funktioniert die Formel nicht?
Überprüfe, ob die Datentypen korrekt sind. Datumswerte müssen als solche formatiert sein, und die Typen in Spalte D sollten den erwarteten Werten entsprechen.

3. Kann ich auch andere Typen von Mitgliedschaften in die Auswertung einbeziehen?
Ja, passe die Formeln entsprechend an, um die gewünschten Mitglieder zu zählen. Nutze die Bedingungen in den Formeln, um verschiedene Typen zu berücksichtigen.

Beliebteste Forumthreads (12 Monate)

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