Summe-ZählenWenn-Indirekt... und automatisches Ausfüllen

[K_Joe]

Hallo zusammen!

Zu meinem Grundproblem konnte ich bereits eine Lösung finden, aber jetzt hakt es noch an einer Stelle und ich sehe den Wald vor lauter Bäumen nicht mehr...


Erstmal, worum es geht:

Ich habe eine Excel-Tabelle (Mac-Version 2011) zur Verwaltung einer Tafel (Lebensmittelausgabe für Bedürftige). Im ersten Tabellenblatt sind die Stammdaten der Klienten hinterlegt. Danach habe ich für jeden Ausgabetag (es ist immer ein Samstag) ein Tabellenblatt, benannt mit dem jeweiligen Datum. Dort stehen in der ersten Spalte die Namen der Klienten (per Formel übernommen aus dem ersten Blatt mit den Stammdaten) und in der zweiten Spalte wird eingetragen, ob der Klient am jeweiligen Tag da war oder nicht.

1 = Klient war da. Spalte leer = Klient war nicht da. Daraus errechnen sich für den jeweiligen Tag einige wichtige Informationen, zB wie viele Erwachsene und Kinder mit der aktuell vorhandenen Warenmenge zu versorgen sind.


Die Problemstellung:

Ich möchte im ersten Tabellenblatt eine Statistik, wie oft der jeweilige Klient die Tafel besucht hat. Dafür habe ich prinzipiell schon eine Lösung gefunden:

Code: [Auswählen]

=SUMME(ZÄHLENWENN(INDIREKT("'"&S7:S16&"'!G7");1))
Zur Erläuterung: in S7 bis S16 stehen die Namen der zu berücksichtigenden Tabellenblätter, in der Spalte G steht eine "1", wenn der Klient an diesem Tag da war (in Zeile 7 steht der erste Name, daher G7). Das funktioniert mal soweit.

Was nicht funktioniert, ist das automatische Ausfüllen nach unten. Also wenn ich die Zelle, in der die Formel steht, nach unten weiterziehe, ändert sich partout dieser eine Wert nicht. Es sollte aber G7, G8, G9, G10 usw. sein.

Es gibt ja eine Formel, die die aktuelle Zeilennummer ausspucken soll, aber mit der habe ich es nicht geschafft (oder sie funktioniert nicht innerhalb der Matrix-Formel). Ich habe über eine Lösung gelesen, bei der man eine Z1S1-Schreibweise benutzen muss, aber da steigt mein Verstand endgültig aus...

Meine Frage konkret: Wie schaffe ich es, diese Formel so fortzusetzen, dass sich immer die Zeilennummer in der Spalte G um 1 erhöht?

Danke im Voraus an alle User für das Lesen meines langen Beitrages

Joe

[steve1da]

Hola,

einfacher wäre es, wenn die Daten alle auf einem Blatt stehen würden.

Code: [Auswählen]

=SUMME(ZÄHLENWENN(INDIREKT("'"&$S$7:$S$16&"'!G"&ZEILE(A7));1))
Gruß,
steve1da

[K_Joe]

Stimmt, das hatten wir ursprünglich auch. Aber dann habe ich sehr viele Spalten im Blatt und das macht es unübersichtlich und fehleranfällig - zB wurde oft in die falsche Spalte eingetragen. Dass man Spalten ausblenden oder das Fenster teilen kann, hilft hier wenig weiter, weil ich das File so gestalten muss, dass auch absolute Laien damit umgehen können 

Nachtrag: Die $ hatt ich auch schon drin, hat aber nichts genützt.

[gmg-cc]

Und eine wirklich tiefer greifende Hilfe wäre möglich, wenn du eine Mustertabelle mit anonymisierten Daten aber dem bisherigen, identischem Aufbau (als *.xlsx) hier einstellen würdest.

[K_Joe]

Kein Problem!

Hier ist das File, ich habe einfach Namen und Adressen rausgelöscht, die sind für die Berechnungen eh irrelevant.

https://dl.dropboxusercontent.com/u/24538560/TOET_BDF_Klienten_Test.xlsx

Danke!

[steve1da]

Hola,

mit meinem Vorschlag bekomme ich die gleichen Ergebnisse wie du.

Gruß,
steve1da

[gmg-cc]

Kein Problem!
Hier ist das File, ich habe einfach Namen und Adressen rausgelöscht, die sind für die Berechnungen eh irrelevant.

Doch ein Problem, wenn ich nicht nur den Moment betrachte sondern das Projekt als Ganzes und etwas zukunftsweisend betrachte. Ich habe nichts gegen Name 1, Name 2, ..., solange der gleiche Name immer das identische Pseudonym hat. Aber für den Moment ist dir ja geholfen ...

[K_Joe]

Das verstehe ich jetzt nicht, sorry, bitte erkläre mir das nochmal 

Geholfen ist mir im Moment leider noch nicht...

[steve1da]

Hola,

Geholfen ist mir im Moment leider noch nicht...

ich erhalte mit meinem Vorschlag die gleichen Ergebnisse wie du. Dazu hattest du dich aber nicht mehr geäußert.

Gruß,
steve1da

[K_Joe]

Hola,

Geholfen ist mir im Moment leider noch nicht...

ich erhalte mit meinem Vorschlag die gleichen Ergebnisse wie du. Dazu hattest du dich aber nicht mehr geäußert.

Gruß,
steve1da

Ich wüsste keine passende Antwort, die uns weiterbringt. Du hast mir damit bestätigt, dass es eben bei dir auch nicht funktioniert. Danke fürs Ausprobieren jedenfalls!

[steve1da]

Hola,

Du hast mir damit bestätigt, dass es eben bei dir auch nicht funktioniert.

wo das denn?

Die Ergebnisse deiner Formel in Stammdaten, O7, O8, O9....erhalte ich mit meiner vorgeschlagenen Formel auch, die man einfach runterziehen kann.

Scheinbar hast du es nicht ausprobiert.

Gruß,
steve1da

[K_Joe]

OK, nochmal langsam, vielleicht verrenne ich mich gerade.

Ich verwende zur Ermittlung der Anzahl der Besuche die Formel aus meinem Eröffnungsthread.

Wenn ich den jetzt nach unten ziehe, soll sich der Paramater G7 auf G8, G9 usw. ändern. Das tut er aber nicht.

Du hast geschrieben, dass du zum gleichen Ergebnis kommst. Daher habe ich diese Aussage so verstanden, dass es eben auch bei dir "nicht funkioniert".

[steve1da]

Hola,

in meiner ersten Antwort habe ich folgende Formel geschrieben:

Code: [Auswählen]

=SUMME(ZÄHLENWENN(INDIREKT("'"&$S$7:$S$16&"'!G"&ZEILE(A7));1))
Überlesen?

Gruß,
steve1da

[K_Joe]

Ja

Denn ich dachte, das war für den Fall gemeint, dass alles auf 1 Blatt steht. Sorry, mein Fehler.

[K_Joe]

Günthers Meinung würde mich jetzt noch interessieren... Ich bin immer für Optimierungsvorschläge zu haben 

[gmg-cc]

Alles auf 1 Blatt ist schon OK und besser. Die Auswertung könnte dann urch jemanden, der/di einigermaßen fit in Excel ist, per PivotTable erfolgen. Und die einzugebenden Namen, etc. in einer Intelligenten Tabelle/Liste festhalten. Da kann ganz prima zu Beginn der Ausgabe ein Filter für den Tag gesetzt werden. Vielleict auch so, dass kein Datum geschrieben werden muss, weil auch fehlerträchtig. Ab Abend dann ganz einfach das Datum mit einem Schlag ausfüllen.

[K_Joe]

Alls klar. Vielen Dank für eure Tipps!

@steve1da: Die neue Formel kann ich erst morgen ausprobieren, aber ich gehe mal davon aus, dass es funktioniert.

@Günther: Das bekomme ich sicher hin, wenn ich mich intensiv damit auseinandersetze... es ist nur einem Frage, ob/wann ich die Zeit dafür finde...

[gmg-cc]

Na ja Joe,

so nette Dinge wie Eingabemaske (VBA) und Auswahl der Klienten (Hilfe suchenden) sowie Einblenden von den wichtigen Anmerkungen (xyz fehlt noch) habe ich ja noch gar nicht angesprochen. Aber so hast d erst einmal ein Ziel, welches sich durchzusetzen lohnt.

[K_Joe]

OMG Das wäre dann ja Luxus pur Ich werd mal nachforschen, ob sich nicht in unserer Nähe jemand findet, der uns da unterstützt!

Ach ja, und danke nochmals für die Hilfestellung!

[gmg-cc]

Ich meine das jetzt ernst: Stell doch einmal ein kleines Schildchen auf: "Sprichst du VBA?" und "Do you code in VBA?"
In deutsch gerne die DU-Anrede, das ist so üblich. Hintergrund: Ich glaube, dass jemand, der auf die Unterstützung der Tafel angewiesen ist, sehr gerne etwas geben würde. Ich weiß, wovon ich rede ...

[steve1da]

Hola,

und ansonsten sind wir ja auch noch da

Ich helfe sehr gerne im Rahmen meiner Möglichkeiten für ein solches Projekt anstatt irgendwelchen Fragenden Tabellen zu bauen, um ihre Chefs zu befriedigen.

Gruß,
steve1da

[K_Joe]

Guten Morgen!

Ich bin immer wieder beeindruckt, wie mächtig Excel ist. Bin eigentlich gelernter Drucktechniker/Grafiker und vergleiche es oft mit Photoshop bzw. InDesign. Für einen Laien oft unbedienbar und wenn man es nicht gelernt hat, weiß man gar nicht, welche Möglichkeiten diese Programme bieten. Und vor allem WIE "man" mit diesen Programmen nun mal arbeitet und wie nicht.

Ihr werdet wahrscheinlich die Hände über dem Kopf zusammenschlagen, wenn ihr so eine Laien-Tabelle seht 

Ein paar Fortgeschrittenen-Tricks hatte ich mal im Gymnasium, aber das ist über 15 Jahre her... und wann braucht Otto Normalverbraucher schon solche Funktionen?

Begonnen hat ja als reine Klientenliste, dann kamen stückweise neue Anforderungen dazu. Am wichtigsten ist, dass die jeweiligen Tagesleiter die Anwesenheit der Klienten eingeben und somit eine automatische Summe haben, wie die Ware aufgeteilt werden muss. Unter den Tagesleitern sind einige das Arbeiten am PC gewohnt, aber andere stehen damit auf Kriegsfuß. Daher bin ich bemüht, die Tabelle so einfach wie möglich zu halten, auch wenn es dadurch in Wirklichkeit eigentlich umständlicher wird.

Für mich ist in weiterer Folge die Frequenz der Besuch interessant und so ergibt eines das andere... im Endeffekt wäre eine richtige Datenbank natürlich lässig... wenn man neue Klienten mittels Maske eingeben könnte, eine Klientennummer sowie ein Ablaufdatum der Berechtigungskarte automatisch hinterlegt wird usw...

@Günther: Danke! Ich nehme den Vorschlag durchaus ernst

Man könnte auch ein Schulprojekt draus machen. Ich werde mal die Fühler ausstrecken.

@steve1da Ebenfalls Danke für deine Hilfe!

[Frank Furter]

Hola,

...

Code: [Auswählen]

=SUMME(ZÄHLENWENN(INDIREKT("'"&$S$7:$S$16&"'!G"&ZEILE(A7));1))
Gruß,
steve1da

hallo, wenn man weiß, wo die formel in soll, ist doch die Z1S1-schreibweise beim INDIREKT() einfacher und kürzer... ... angenommen sie soll in O7 stehen dann

Code: [Auswählen]

=SUMME(ZÄHLENWENN(INDIREKT("'"&$S$7:$S$16&"'!Z()S(-8)";);1))und als Matrix-Formel abschließen