Excel Vereinskasse Formelproblem

Computer: Software & Programmierung Office Software
1

Hallo an ALLE,

ich habe ein kleines Problem, hoffe Ihr könnt mir weiterhelfen. Also ich bin beauftragt worden die Vereinskasse zu führen. Um mir die Arbeit zu erleichtern habe ich angefangen eine Excel Tabelle zu fertigen.

Um mein Problem etwas besser zu beschreiben versuche ich es mit einem Beispiel besser darzustellen.

Spalte 1: da stehen Nr., also die Anzahl der Vereinsmitglieder, dies sind Kinder im Alter von ca. 12 Jahren
Spalte 2: Da steht der Nachname der Kinder
Spalte 3: Da steht der Vorname der Kinder
Spalte 4: Hier steht der Name des Vaters des Kindes Spalte 5: Hier steht der Name der Mutter des Kindes Spalte 6: Hier soll der zu zahlender Betrag stehen Spalte 7: Und hier soll stehen, die Kinder mit den gleichen Nachnamen Geschwister sind oder nicht

Mein Problem ist es: Excel soll mir den zu zahlenden Betrag anzeigen

Also… als erstes soll excel in der Spalte 2 „Nachname“ gucken ob 2 oder mehr Kindernachnamen gleich sind wenn JA dann soll er, bei den Nachnamen die gleich sind, auch in die Spalte 4 „Name des Vaters“ und Spalte 5 „Name der Mutter“ gucken ob diese auch gleich sind - also ob diese Kinder Geschwister sind oder nicht… Wenn 2 oder mehr Nachnamen gleich sind und die Namen der Eltern auch gleich sind dann soll der zu zahlten betrag 7,5 euro sein, sonst 10 euro und in der letzten Spalte soll auch noch ausgeschrieben sein ob diese Kinder Geschwister sind oder nicht … also es reicht auch ein Ja oder Nein

Beispiel:

Nr. 1: Müller, Sina, Peter (Vater), Mona (Mutter)
Nr. 2: Müller, Ines, Peter (Vater), Mona (Mutter)
Nr. 3: Backes, Reiner, Andreas (Vater), Katarina (Mutter)

Also Nr. 1 und Nr. 2 wären Geschwister, weil sie beide denselben Nachnamen, Vaternamen und Mutternamen haben und hätten demnach einen zu Zahlenden Beitrag in Höhe von 7,5 euro

Ich habe es mit etlichen Formel versucht (Zählewenn, Wenndann, Und…) habe es leider nicht hingekriegt… Hoffe ich konnte es verständlich rüberbringen und hoffe Ihr könnt mir weiterhelfen. Falls die Fragestellung unverständlich ist kann ich versuchen es nochmal zu erläutern.

Vielen Dank im Voraus

2

Hallo,

das kann man mit einem kleinen Trick erreichen:

Generiere eine Spalte in der Du Nachname&NameVater&NameMutter verkettest =Spalte(„Nachname“)&Spalte(„Vater“)&Spalte(„Mutter“)
Dieser Spalte (kann man auch ausblenden) gibst Du dann einen Namen (für sverweis)

In der Spalte wo der Mitgliedsbeitrag drin steht prüfst Du dann ob der sverweis existiert und setzt je nachdem den Mitgliedsbeitrag:

=wenn(istfehler(sverweis(Spalte(„Nachname“)&Spalte(„Vater“)&Spalte(„Mutter“);NamevonDeinerSpalte;1;0));„10 Euro“;„7,5 Euro“)

In der Spalte für Geschwister kannst Du dann prüfen was in der Spalte Beitrag steht:

=wenn(SpalteBeitrag=„7,5 Euro“;„Ja“;„Nein“)

Hoffe geholfen zu haben

3

super vielen DANK, dass mit dem sverweis ist mir nicht eingefallen. ich muss irgendwo einen fehler machen denn es klappt nicht so ganz. Es steht nämlich bei allen Mitgliedern derselbe Betrag.

Ich habe eine neue Spalte eingefügt mit der Verkettung so wie du es beschrieben hast.

Anschließend habe ich die Formel eingegeben mit istfehler und sverweis. Aber das Problem ist es kommt immer 7,5 raus bei jedem Mitglied. Wo mache ich Fehler???

4

Schwer zu sagen wo der Fehler genau liegt. Kannst Du Deine Formel veröffentlichen bzw. einfacher ist es wenn Du mir die Tabelle mit Beispieldaten sendest, dann ist es leichter für mich den Fehler in der Formel zu finden ([email protected])

Jetzt noch Werbung in eigener Sache:

http://www.termin-klick.de

5

Hi,

  1. das ist ja nicht eine Frage, die man unbedingt privat beantworten muß, stell sie doch einfach ins Excelbrett, die Beantwortung dazu ist durchaus von allgemeinem Interesse
    2.)stell einen Beispielfile zur Verfügung, damit sich die Beantworter das nachbauen ersparen
    3.)die abfrage nach gleichem Familiennamen find ich in der Zeit von Patchworkfamilien, Alleinerziehern etc nicht zielführend

Gruss

M@x

6

Hallo,
was ist denn wenn die Mutter ihren Mädchennamen behalten hat aber trotzdem die leibliche Mutter ist?
Mache doch einfach eine Hilfsspalte mit der kannst du die Formel besser steuern. Überschrift Spalte F Geschwister, Eintrag ja oder nix. Formel lautet z.B.: =Wenn(F2=„ja“;7,50;10)
Liebe Grüße - Wolfgang

7

Hier kann ich leider nicht helfen.

8

Hallo,
nur über die Namen kann man das Problem nicht lösen.
Zunächst benötigst Du eine Tabelle in der Art:
ID, Name, Vorname, KindVonID, ElternVonID
Damit kann man dann über die Kind-Eltern-Beziehung eine entsprechende Auswertung machen.
Dabei ist es natürlich ein Problem, dass eine Person mehrere Kinder haben kann. Evtl. muss man die Eltern-Kind-Beziehung noch mal in einer zweiten Tabelle abbilden.

Also etwa so:
ElternID, KindID
E1, K1
E1, K2

Dann kann man in der Personen-Tabelle mit ZählenWenn oder ZählenWennS in der Beziehungstabelle zählen.

Gruß
Vincenz

9

Hallo,

war im Urlaub, daher die späte Antwort.

Direkt zur Excel-Problemstellung:
Ich würde eine Hilfsspalte ergänzen, in der ich Nachname, Name des Vaters, Name der Mutter verkette, also so: „=B2&D2&E2“.
Anschließend zähle ich in der Spalte, in der der Betrag stehen soll, die Anzahl der Vorkommen der jeweiligen Namensverkettung in der Hilfsspalte. Ist diese Zahl größer als 1, dann gehe ich davon aus, dass es sich um Geschwister handelt. Der Betrag ergibt sich demnach aus „=WENN(ZÄHLENWENN(H:H;H2)>1;7,5;10)“.

Die Geschwister ja/nein Spalte verstehe ich nicht.

Zum inhaltlichen Problem:
Ist das nur ein fiktives Beispiel, das die eigentliche Problemstellung umschreiben soll, oder handelt es sich um das reale Problem der Vereinskasse? Falls letzteres, dann ist die gesamte Lösung doch sehr realitätsfremd. Im Ernst: Ich kenne Familien, in denen 2 Kinder den Namen Müller tragen, 1 weiteres Meier, Vater heißt Schmidt, Mutter heißt Meier. Eltern verheiratet, Kinder aus jeweils erster Ehe. Ich würde das ganze Problem wohl eher mit einer Datenbank, z.B. Access, lösen, in der ich eine Eltern-Kind-Beziehung abbilde und auch gleich die Adressen verwalte.

VG, Mertel

10

es ist nur ein fiktives beispiel… ich brauche ansätze um weiterzukommen

11

Hallo sansonite,

aufgrund meines Urlaubs kommt meine Antwort mit einiger Verspätung.

M.E. lässt sich Dein Problem mittels einer sog. „Matrix-Formel“ lösen. Näheres hierzu findest Du in der Excel-Hilfe (F1).
Die Sache ist zwar nicht ganz einfach, hilft aber oft in verzwickten Situationen.

An Deiner Stelle würde ich allerdings die Tabelle etwas anders aufbauen und insbesondere einen „Familien-Index“ einbauen. Dies kann zweckmäßigerweise z.B. in Form einer „Familien-Mitgliedsnummer“ (oder einer Hilfsspalte in welcher die Vornamen der Eltern und der Familienname zusammengefasst werden erfolgen.
Z.B. =A2&A4&A5 -> das ergibt z.B. SchmidtHaraldHeike)
Du hast dann nur noch eine Spalte zum Vergleichen und kommst dann mit einer einfachen „Zählenwenn“-Formel zu einem Ergebnis (solange die Familienstrukturen nicht zu kompliziert werden: 1. Kind heißt Fritz Schneider, der Vater Horst Maier-Schneider und die Mutter Inge Schneider, geschiedene Huber. Fritz’ Schwester aus 1. Ehe der Mutter heißt Carola Huber - und jetzt?)
:wink:
Also ich tendiere zum numerischen „Familienindex“!

Gruß Uli

12

Hallo
Gut Ding will Weile haben, sorry für die Verspätung, hoffe das wird jetzt dafür ganz gut… :wink:

Also, ich habe mir deine Tabelle wie beschrieben gebaut:
Die Zeile 1 enthält die Spaltenüberschriften
Nr;Nachname;Vorname;Vater;Mutter,Betrag;Geschwister;Elternname (die letzen beiden (Spalten G und H) habe ich angefügt.

Zuf Lösung:

Schritt 1. Verketten des Elternnamens
In Spalte H füge ich den Elternnamen durch die Verkettung des Vonamens des Vater (Spalte D) mit dem Vornamen der Mutter (Spalte E) und dem Nachnamen (Spalte B) mit folgender Formel zusammen:
=VERKETTEN(D2;" & „;E2;“ ";B2)
Damit steht der Elternname von Reiner mit „Andrea & Katarina Backes“ und jener von Ines und Sina mit „Peter & Mona Müller“ in deiner Tabelle

Schritt 2: Sortieren
Nun sortiere die Tabelle nach Spalt H (Elternname) und Spalte C (Kinder-Vorname). Damit ist die Tabelle nach den Familien sortiert, die Geschwister stehen unmittelbar nacheinander. OK?

Schritt 3: Festlegen ob noch Geschwister vermerkt sind mit folgender Formel in der Spalte G:
=WENN(ODER(H2=H1;H2=H3);„JA“;„NEIN“)

Das sind zwei in einander verschachtelte Formeln, beginnen wir mit ODER(H2=H1;H2=H3): die ODER()-Funktion ermittelt hier zwei Wahrheitswerte - Konkret ist H2 (Andreas / Katarina Backes) gleich wie H1 (die Spaltenüberschrift)? oder ist H2 (Andreas / Katarina Backes) gleich wie H3 (Peter & Mona Müller)? Das stimmt natürlich in beiden Fällen nicht, weshalb die oder() Funktion mit dem Wahrheitswert „FALSCH“ endet. Für die Zeile 3 mit den Eltern von Ines ist der zweite Vergleich WAHR und in Zeilte 4 mit den Eltern von Sina ist der erste Vergleich WAHR, weshalb die ODER()-Funktion in beiden Fällen mit WAHR endet (ein einziger WAHR-Wert führt insgesamt zu WAHR, nur wenn sämtliche Werte FALSCH lauten ist auch das Gesamtresultat FALSCH - so die Logik).
Kommt noch die umschliessende WENN()-Funktion. Diese nimmt den Wahrheitswert von der eben beschriebenen ODER()-Funktion: Ist dieser WAHR Schreiben wir „JA“, sonst (FALSCH) „NEIN“ rein.
Du solltest jetzt in der Spalte G für Reiner = NEIN, Ines = JA, Sina = JA haben.

Schritt 4: Berechnen der Kosten pro Kind (bei Geschwistern EUR 7.50, bei Einzelkindern EUR 10.00) mit folgender Formel:
=WENN(G2=„JA“;7.5;WENN(G2=„NEIN“;10;0))
Wenn in G2 JA steht setzen wir 7.5 ein sonst prüfen wir ob in G1 NEIN steht: Steht NEIN drin setzen wir 10 sonst 0)

Schritt 5: Formatieren
Formatiere nun Spalte als Währungwert mit zwei fixen Nachkommastellen und dem nachfolgenden EURO Zeichen.

Sezte noch eine Summenformel zuunterst in der Spalte F um zu sehen wieviel du insgesamt einnehmen solltest.

Wenn dich die Spalte H stört, kannst du sie auch ausblenden (nicht löschen!). Die Formel funktioniert auch wenn die Spalte ausgeblendet ist. Ich persönlich finde es allerdings schöner, wenn die Werte mit denen die Formeln arbeiten auch sichtbar sind, es ist einfacher nachvollziehbar, was abgeht.

Anmerkung zum Schluss:
Das funktioniert solange gut, wie die zusammengefügten Elternnamen eindeutig sind. Heissen zwei Elternpaare gleich (was eher unwahrscheinlich ist), müsstest du ein zusätzliches, unterscheidendes Merkmal einbauen und in der Eltern-Formel berücksichtigen.

Gruss aus der Schweiz
Markus