In 6 Schritten zu deiner Excel-Vorlage für die Mitgliederliste im Verein / inkl. Download

In meiner „Laufbahn“ als Kassier in meinem Heimat-Fußballverein kenne ich den Aufwand, den eine Mitgliederliste machen kann.

Wir nutzen selbst eine Software unseres Dachverbandes dafür.

Aber ich bin, wenn ich ehrlich bin, kein Fan von Software-Lösungen, wo es nicht unbedingt eine Software braucht, denn

  1. sie kosten Geld und dieses Geld kann in den eigentlichen Vereinszweck viel sinnvoller investiert werden
  2. sie bieten zu viele Funktionen. Die meisten davon benötigt man in der Praxis aber nicht.
  3. Deswegen sind sie zu komplex aufgebaut, überfordern schnell und es macht wenig Spaß, damit zu arbeiten

Ich biete speziell für Vereine, eine einfache Excel-Vorlage für die Mitgliederliste an.

Sie ist beschränkt auf das Wesentliche, so wie ich es aus meiner praktischen Erfahrung heraus erlebe:

  • Schnelles Anlegen bzw. Ändern von Mitgliederdaten
  • Auf einen Blick: Wer hat bzw. hatte welche Funktionärstätigkeit?
  • Auf Knopfdruck: Geburtstagsliste generieren
  • Automatisch: Statistik über die Eintritte und Austritte pro Jahr
  • Automatisch 2 😉: Information, welche Mitglieder für langjährige Mitgliedschaft zu ehren sind

Was genau ich damit meine und wie du die Liste selbst aufbaust, findest du in diesem Video:

Mein Youtube Video zum Erstellen der Excel-Vorlage Mitgliederliste für Vereine

Das Ziel ist es, eine automatisierte und dynamische Excel-Vorlage für deine Mitgliederliste zu erstellen, die effizient und anpassungsfähig ist, um Zeit und Geld für deinen Verein zu sparen.

Du kannst dir die fertige Excel-Vorlage für die Mitgliederliste hier herunterladen:

Oder du gehst mit mir die Schritt-für-Schritt-Anleitung durch und wir bauen gemeinsam für deinen Verein die Excel-Vorlage der Mitgliederliste kostenlos nach.

Stammdaten-Tabelle für die Mitgliederliste erstellen

Zunächst legen wir eine Stammdaten-Tabelle an. Diese Tabelle enthält allgemeine Informationen wie Anrede, Geschlecht, Mitgliedsbeiträge usw., die für die Mitgliederverwaltung / Mitgliederliste erforderlich sind.

  1. Neues Tabellenblatt anlegen: Erstelle ein neues Tabellenblatt und benenne es in „Stammdaten“ um
  2. Liste für Titel anlegen:
    • Erstelle eine Tabelle mit den möglichen Titeln (z.B. Dr., Prof.)
    • Markiere die Tabelle, drücke STRG + T, um eine „intelligente“ Tabelle zu erstellen
    • Gib der Tabelle einen eindeutigen Namen, z.B. „Titel“
  3. Dropdown-Menü für Titel einrichten
    • Gehe auf den Reiter „Formeln“ und dann auf „Namensmanager“ und dann auf „Neu“
    • Erstelle einen neuen Namen, z.B. „Titel_Dropdown“ und wähle dann bei „Bezieht sich auf“ die Titel-Tabelle ohne die Überschriftenzeile aus
  4. Geschlecht-Tabelle erstellen
    • Füge eine Tabelle mit Geschlechtern (z.B. männlich, weiblich, divers) hinzu
    • Markiere und formatierte diese Tabelle wie bereits bei der Titel-Tabelle beschrieben und erstelle einen Namen, z.B. „Geschlecht_Dropdown“
  5. Mitgliedsbeiträge-Tabelle erstellen
    • Erstelle je eine Tabelle für männlich und weiblich und füge dort jeweils die Staffelung der Mitgliedsbeiträge nach Alter ein (z.B. ab 0 Jahre: 20€, ab 15 Jahre: 25€, ab 18 Jahre: 35€
    • Weise diesen Tabellen ebenfalls einen eindeutigen Namen zu, z.B. „Beiträge_männlich“ und „Beiträge_weiblich“
  6. Geburtstagsgrüße-Tabelle erstellen:
    • Erstelle eine Tabelle, in der festgelegt wird, ab welchem Alter und in welchen Abständen Mitglieder Geburtstagsgrüße erhalten sollen (z.B. ab 60 Jahren alle 5 Jahre); benenne diese Spalte z.B. „Geburtstagsgrüße Alter“
    • Gib der Tabelle einen eindeutigen Namen, z.B. „Geburtstage_Alter“
    • Füge in dieser Tabelle eine Spalte „Jahrgang“ ein und ermittele dort mittels einer Formel, welcher Geburtsjahrgang graturliert werden soll:
    • =JAHR(HEUTE())-[@[Geburtstagsgrüße Alter]]
  7. Funktionärstabelle erstellen:
    • Erstelle eine Tabelle mit einer Auflistung der Funktionärstätigkeiten in eurem Verein (z.B. Kassierer, Vorsitzender)
    • Gehe dann genau so vor wie bereits bei der „Titel-Tabelle“ beschrieben
Excel-Vorlage Mitgliederliste Stammdaten
Fertiges Tabellenblatt der Stammdaten für die Mitglieder
Excel-Vorlage Mitgliederliste Namens-Manager
Beispiel einer Dropdown-Liste für die Mitgliederliste, die über den Namens-Manager vergeben wurde
Excel-Vorlage Mitgliederliste Formel Jahrgang ermitteln
Formel zur Ermittlung des Jahrgangs für die Geburtstagsgrüße

ACHTUNG:

Wenn ihr eine andere Mitgliedsbeitragsstaffeln in eurem Verein habt, dann schreibt mir gerne und wir finden eine Lösung, wie wir diese Staffel in der Excel-Vorlage für die Mitgliederliste so aufbauen, dass sich die Mitgliedsbeiträge automatisch ermitteln lassen.

Schreibe mir gerne direkt eine Mail an info@bernd-giehl-excel-profi.de

Mitgliederliste anlegen

Nun erstellen wir das Hauptblatt der Excel-Vorlage, die Mitgliederliste, in der alle Stammdaten der Mitglieder eures Vereins eingetragen werden.

  1. Neues Tabellenblatt anlegen: Erstelle ein neues Tabellenblatt und nenne es „Mitgliederliste“
  2. Überschriften erstellen: Füge in der ersten Zeile die folgenden Spalten-Überschriften ein: Anrede, Titel, Vorname, Nachname, Geschlecht, Geburtsdatum, Straße, Hausnummer, PLZ, Ort, Telefon, E-Mail, Bank, IBAN, Eintrittsdatum, Mitgliedsbeitrag
  3. Dropdown-Menüs einrichten:
    • Für „Anrede“, „Titel“ und „Geschlecht“ richtest du ein Dropdown-Menü ein
    • Gehe auf „Daten“ > „Datenüberprüfung“ > bei „Zulassen“ wählst du „Liste“ aus
    • Wähle bei „Quelle“ jeweils die passenden Namen mit F3 aus (z.B. „Titel_Dropdown“, „Geschlecht-Dropdow“
  4. Eingabe-Einschränkungen für Geburtsdatum und Eintrittsdatum:
    • Richte für die Felder „Geburtsdatum“ und „Eintrittsdatum“ eine Datenüberprüfung ein
    • Gehe auf „Daten“ > „Datenüberprüfung“ > wähle in „Zulassen“ das „Datum“ aus und bei „Daten“ wähle „größer als“ aus
    • Im Feld „Anfangsdatum“ kannst du dann z.B. den 01.01.1900 eingeben
    • So werden in den Feldern für „Geburtsdatum“ und „Eintrittsdatum“ nur Eingaben von Datumswerten, die nach dem 01.01.1900 sind, erlaubt
  5. Mitgliederliste als intelligente Tabelle formatieren:
    • Markiere die Überschriften und die erste Zeile darunter, drücke STRG + T und wähle „Tabelle hat Überschriften“
  6. Mitgliedsbeitrag automatisch berechnen:
    • Erstelle eine Formel, die den Mitgliedsbeitrag auf Basis des Alters und Geschlechts automatisch berechnet.
    • =WENN([@Geschlecht]=“männlich“;XVERWEIS(JAHR(HEUTE())-JAHR([@Geburtsdatum])-1;Beiträge_männlich[Alter ab];Beiträge_männlich[Beitrag];;-1;1);XVERWEIS(JAHR(HEUTE())-JAHR([@Geburtsdatum])-1;Beiträge_weiblich[Alter ab];Beiträge_weiblich[Beitrag];;-1;1))   – Diese Formel nutzt den `XVERWEIS`, um das Alter in den Mitgliedsbeitragstabellen zu suchen und den entsprechenden Beitrag zurückzugeben.
    • Diese Funktion prüft zunächst, ob das Mitglied männlich ist. Dann wird ein XVERWEIS verwendet. Der XVERWEIS hat ein Kriterium `Suchkriterium`. Als Suchkriterium setzen wir eine Formel ein, die das aktuelle Kalenderjahr ermittelt und davon das Geburtsjahr des Mitglieds minus 1 Kalenderjahr errechnet. Entscheidend im XVERWEIS ist, dass wir als Vergleichsmodus -1 einsetzen, wir suchen also das Suchkriterium bzw. das nächstkleinere Element. Wenn unser Mitglied weiblich ist, dann setzen wir in der Wenn-Funktion denselben XVERWEIS ein, nur suchen wir nun in der Mitgliedsbeitragstabelle der weiblichen Mitglieder nach dem passenden Beitrag
    • … klingt kompliziert; im oben verlinkten Video habe ich es ausführlich beschrieben ab Minute 14 😊
Excel-Vorlage Mitgliederliste Überschriften
Überschriften der Excel-Vorlage für die Datenpflege in der Mitgliederliste
Excel-Vorlage Mitgliederliste Dropdown-Menü Liste
Beispiel einer Dropdown-Liste mit einer hinterlegten Liste, hier für die Auswahl des Geschlecht in der Mitgliederliste
Excel-Vorlage Mitgliederliste Datenüberprüfung Datum
Beispiel einer Datenüberprüfung auf ein Datum, hier für das Erfassen des Geburtsdatums in der Mitgliederliste
Excel-Vorlage Mitgliederliste Formel zur Ermittlung des Mitgliedsbeitrags
Formel zur automatischen Ermittlung des Mitgliedsbeitrages in der Mitgliederliste

Nerd-Wissen 😉

Der XVERWEIS ist die bessere Version des SVERWEIS. Er ist in den neueren Excel-Versionen verfügbar. Wenn du ihn in deiner Excel-Version hast, empfehle ich dir, nimm den XVERWEIS statt den SVERWEIS … warum? Einen Vergleich von XVERWEIS mit SVERWEIS findest du in meinem Blog-Beitrag

Funktionärsübersicht in der Mitgliederliste erstellen

In diesem Schritt erstellen wir eine Tabelle, die uns auf einen Blick zeigen wird, wer welche Funktion ausübt oder ausgeübt hat.

  1. Neues Tabellenblatt anlegen: Erstelle ein neues Tabellenblatt und nenne es `Funktionärsübersicht`.
  2. Überschriften erstellen: „Name“, „Position“, „Seit Kalenderjahr“, „Bis Kalenderjahr“, „Dauer“
  3. Dropdown-Menü für Position einrichten: Richte ein Dropdown-Menü für `Position` wie zuvor beschrieben ein (z.B. mit `Position_Dropdown`).
  4. Eingabe-Einschränkungen für Seit- und Bis-Datum: Richte für die Felder `Seit Kalenderjahr` und `Bis Kalenderjahr` eine Datenüberprüfung ein, sodass nur ganze Zahlen erfasst werden können (z.B. Zahlen größer als 1900
  5. Dauer automatisch berechnen:
    • Erstelle eine Formel, die die Dauer der Funktionärstätigkeit berechnet. Beispiel:
    • =WENN(Bis=““; HEUTE()-Seit; Bis-Seit)
    • Diese Formel berücksichtigt, ob ein `Bis-Datum` angegeben ist oder nicht, und berechnet die Jahre entsprechend. Ist ein Bis-Datum eingegeben, dann wird der Zeitraum vom Bis-Datum zum Seit-Datum berechnet. Ist kein Bis-Datum eingegeben, dann wird der Zeitraum von heute bis zum Seit-Datum berechnet.
Excel-Vorlage Mitgliederliste Funktionärsübersicht Formel für Dauer
Überschriften der Funktionärsübersicht und Formel zur Ermittlung der Dauer der Funktionärstätigkeit

Geburtstagsliste aus der Mitgliederliste automatisch erstellen lassen

Ab einem gewissen Alter besuchen wir in der Vorstandschaft das Geburtstagskind, um im Namen des Vereins Glückwünsche zu übermitteln.

Hierfür benötigen wir in unserer Excel-Vorlage eine Geburtstagsliste, die wir so aufbauen, dass sie aus der Mitgliederliste unseres Vereins auf Knopfdruck aktualisiert werden kann 😊

  1. Lade die „Geburtstagsliste“ aus dem Stammdatenblatt in Power Query
    • Gehe auf „Daten“ > „Aus Tabelle/ Bereich“, um die Mitgliederliste in den Power Query-Editor zu laden.
    • Klicke auf „Schließen und Laden“ und wähle dann „Nur Verbindung erstellen“ aus
  2. Mitgliederliste in den Power Query laden:
    • Gehe auf „Daten“ > „Aus Tabelle/ Bereich“, um die Mitgliederliste in den Power Query-Editor zu laden.
    • Füge neue Spalten hinzu, in dem du im Menüband auf „Spalten hinzufügen“ klickst, dann markiere die Spalte mit dem Geburtsdatum und klicke anschließend im Menüband auf „Datum“. Dort wählst du „Jahr“ aus. Wiederhole die Schritte und füge dann Spalten für „Monat“ und „Tag“ hinzu
    • Klicke auf „Schließen und Laden“ und wähle dann „Nur Verbindung erstellen“ aus
  3. Geburtstagsliste erstellen:
    • Kombiniere die Mitgliederliste mit der Geburtstagsgrüße-Tabelle, um die Mitglieder zu ermitteln, die dieses Jahr einen Ehrungsgeburtstag haben.
    • Klicke dazu im Power Query auf „Start“, dann auf „Kombinieren“ und dann auf „Abfragen zusammenführen“
    • Wähle aus der Mitglieder-Abfrage die Spalte mit dem Kalenderjahr aus und nimm dann die zweite Abfrage mit den Geburtstagsgrüßen und wähle dort ebenfalls das Jahr aus
    • Es gibt nun rechts außen eine neue Spalte. Dort klickst du auf die beiden Pfeile und wählst dort die Spalte `Geburtstagsgrüße Alter` aus
    • Anschließend filterst du diese Spalte, in dem du alle Werte mit `null` ausschließt
    • Sortierte die Spalten mit Drag and Drop in eine gewünschte Reihenfolge
    • Sortiere die Liste nach Monat und Tag, sodass die bevorstehenden Geburtstage an oberster Stelle stehen und entscheide, welche Spalten du angezeigt haben möchtest
  4. Ergebnis in Excel laden: Lade die kombinierte Abfrage als Tabelle zurück in Excel
Excel-Vorlage Mitgliederliste Geburtstagsliste
Fertige Geburtstagsliste zeigt automatisiert eine aktuelle Übersicht der zu gratulierenden Mitglieder
Excel-Vorlage Mitgliederliste PowerQuery Schritte Geburtstage Alter
Power Query Ausschnitt mit einem Überblick der getätigten Schritte in der Abfrage „Geburtstage_Alter“
Excel-Vorlage Mitgliederliste PowerQuery Schritte Geburtstagsliste
Power Query Ausschnitt mit einem Überblick der getätigten Schritte in der Abfrage „Mitgliederliste“

Statistiken zu Ein- und Austritten erstellen

Eine der wichtigsten Informationen für Vereinsfunktionäre ist, aus meiner Erfahrung heraus, wie viele Eintritte und Austritte es gab (klar, der Kontostand ist auch wichtig 😉 … und wenn du hier eine Excel-Vorlage für deine Einnahmen-Überschuss-Rechnung (EÜR) suchst, schau dir meinen Blogbeitrag dazu gerne an).

Hierzu erstellen wir eine Statistik, die uns genau das auf Knopfdruck zeigen wird.

  1. Spalte „Austritt“ hinzufügen: Ergänze die Mitgliederliste um eine Spalte `Austritt`, in der das Austrittsdatum vermerkt wird.
  2. Power Query für Eintritte und Austritte erstellen:
    • Lade die Mitgliederliste erneut in Power Query mit „Daten“  und dann „aus Tabelle / Bereich einfügen“
    • Nenne die Auswertung in „Eintritte“ um
    • Wähle die Spalte `Eintritt am` aus
    • Klicke auf „Spalte hinzufügen“, dann auf „Datum“ und dann auf „Jahr“ um aus dem Eintrittsdatum das Kalenderjahr herauszulesen
    • Klicke dann auf „Start“, auf „Gruppieren nach“ und wähle dann dort die Spalte mit dem Eintrittsjahr aus und lasse so die Eintritte pro Jahr zählen
    • Klicke auf „Schließen und Laden“ und wähle dort „Nur Verbindung erstellen“ aus
  3. Power Query für Austritte erstellen:
    • Gehe wie oben beschrieben vor, nur wählst du jetzt die Spalte „Austritte am“ aus
    • Filtere in der Spalte der Kalenderjahre die Jahre mit null heraus
  4. Ein- und Austritte im Power Query kombinieren:
    • Im Power Query klicken wir auf „Kombinieren“, dann auf „Abfragen zusammen führen“ und wählen „Abfrage als neue Abfrage zusammenführen“
    • Aus den beiden Abfragen „Eintritte“ und „Austritte“ wählen wir uns jeweils die Spalte mit dem Kalenderjahr aus
    • Ganz wichtig ist nun, dass wir bei „Join-Art“ die Option „Vollständiger äußerer Join (alle Zeilen aus beiden)“ auswählen. Das nimmt aus beiden Abfragen alle Kalenderjahre mit, unabhängig davon, ob es in einem Kalenderjahr mal nur Eintritte und keine Austritte gab, oder umgekehrt.
    • Jetzt ist eine neue Abfrage entstanden, die wir „Eintritte und Austritte“ nennen können
    • Wähle in dieser Abfrage die Spalten so aus, dass du am Ende eine Spalte mit den Kalenderjahren hast, eine mit der Anzahl der Eintritte und eine mit der Anzahl der Austritte
    • Lade diese Abfrage als Tabelle zurück in Excel.
Excel-Vorlage Mitgliederliste Statistik der Eintritte und Austritte
Statistik der Eintritte und Austritte in der Mitgliederliste
Excel-Vorlage Mitgliederliste PowerQuery Eintritte Gruppieren
Power Query Ausschnitt mit einem Überblick der getätigten Schritte in der Abfrage Eintritte inkl. der Einstellungen bei Gruppieren nach
Excel-Vorlage Mitgliederliste PowerQuery Austritte Zeilen filtern
Power Query Ausschnitt mit einem Überblick der getätigten Schritte in der Abfrage „Austritte“ inkl. der Einstellungen bei Filtern der Null-Werte
Excel-Vorlage Mitgliederliste PowerQuery Vollständiger äußerer Join
Power Query Ausschnitt für das Zusammenbringen der Eintritte und Austritte mit dem „Vollständigen äußeren Join“

Ehrungen für langjährige Mitgliedschaft

Und auch das kennt jeder Vereinsfunktionär, langjährige Mitglieder werden in einem geeigneten Rahmen geehrt.

Daher fügen wir eine Funktion hinzu, die Mitglieder automatisch auflistet, die eine Ehrung für langjährige Mitgliedschaft erhalten sollen.

  1. Mitglieder-Ehrungen Tabelle erstellen: Füge in den Stammdaten eine Tabelle ein, in der festgelegt wird, nach wie vielen Jahren Mitgliedschaft eine Ehrung erfolgen soll (z.B. 20 Jahre, 30 Jahre, usw.), gehe dazu wie oben beschrieben vor, z.B. mit der „Titel-Tabelle“.
  2. Ehrungs-Formel in der Mitgliederliste:
    • Erstelle eine Formel in der Mitgliederliste, die prüft, ob ein Mitglied in diesem Jahr eine Ehrung erhalten sollte. Füge dazu eine neue Spalte „Ehrung“ in der Mitgliederliste hinzu.
    • =WENN([@[Austritt am]]=““;XVERWEIS(JAHR(HEUTE())-JAHR([@[Eintritt am]]);Mitgliederehrung[Mitgliederehrung];Mitgliederehrung[Mitgliederehrung];““);““)
    • Diese Formel prüft, ob das Mitglied aktiv ist, indem wir abfragen, ob die Spalte `Austritt am` einen Eintrag hat. Ist sie leer, ist unser Mitglied also noch aktiv und wir prüfen mit dem XVERWEIS die Ehrung ab. Dazu verwenden wir als Suchkriterium eine Formel, die uns die Mitgliedsjahre berechnet und suchen dann, ob es ein Jahr ist, für das eine Ehrung gemäß der `Ehrungstabelle` erfolgt
  3. Pivot-Tabelle für Ehrungen erstellen:
    • Erstelle eine Pivot-Tabelle, die alle Mitglieder zusammenfasst, die in einem bestimmten Jahr geehrt werden sollen
    • Dazu klicke auf die „Mitgliederliste“ und im Menüpunkt „Tabellenentwurf“ auf „Mit PivotTable zusammenfassen“ und wähle dort neues Arbeitsblatt aus
    • In die Spalten ziehen wir „Ehrung“ und filtern dann die Null-Einträge heraus. In die Zeilen ziehen wir uns außerdem noch den Vornamen und den Nachnamen und in die Werte ziehen wir den Vornamen. Dadurch zählt Excel automatisch die Anzahl der zu ehrenden Mitglieder.
Excel-Vorlage Mitgliederliste Formel Mitgliederehrung
Formel in der Mitgliederliste um zu sehen, ob ein Mitglied für langjährige Mitgliedschaft geehrt werden soll
Excel-Vorlage Mitgliederliste Pivot Tabelle Ehrungen
Pivot-Tabelle für die Ehrungen, mit der auf Knopfdruck die zu ehrenden Mitglieder aus der Mitgliederliste ermittelt werden können

Fazit zur Excel-Vorlage Mitgliederliste

Diese Excel-Vorlage für deine Mitgliederliste im Verein bietet eine umfassende und dynamische Lösung für die Verwaltung von Vereinsmitgliedern. Durch die Nutzung von Power Query, intelligenten Tabellen und ausgeklügelten Formeln wird die Pflege der Mitgliederdaten erheblich erleichtert. Die Liste ist flexibel, passt sich automatisch an neue Daten an und sorgt dafür, dass wichtige Termine, wie Geburtstage und Ehrungen,

Ähnliche Beiträge

Wer schreibt hier?

Bernd Giehl, Excel-Profi.

In meinem Beruf als Demand Planner (zu Deutsch: Absatzplaner) beschäftige ich mich mit Zukunftsprognosen zur Absatzlage meines Arbeitgebers

Das heißt, ich versuche herauszufinden, wie viel wir in der Zukunft verkaufen werden 🕵️

Natürlich spielen hier 🔢 Zahlen, Daten, Fakten 🔢 eine große Rolle.

Und natürlich ist Excel ein großer Bestandteil meiner täglichen Arbeit. So arbeite ich gut und gerne > 30h pro Woche mit Excel. 🤓

Ich weiß, wenn man es mit all seiner Macht nutzt 💪, kann man mit Excel sehr viel Zeit sparen 

Dieses Wissen gebe ich in meinem Blog weiter, damit auch du Zeit und Nerven sparst bei deiner Arbeit mit Excel.

 

Nach oben scrollen
SENDINBLUE HTML HIER EINFÜGEN
Cookie Consent mit Real Cookie Banner