Lehrling braucht Nachhilfe

[Lehrling]

Hallo zusammen,

ich bin hier neu und habe bevor ich mich stundenlange im Netz herumgeschlagen habe, mich entschieden, sich diesem Forum anschließen zu wollen.
Ich hoffe, meine Fragen sind nicht zu trivial, um nicht beantwortet werden zu wollen. Schauen wir mal.

Ich bin gerade dran eine Excel-Tabelle zu erstellen, die unsere Schul- und Vereinssportler ihren Erfolg/Verlauf aufzeigen.
Ich habe dazu im Anhang einen Auszug meiner erstellten Tabelle beigefügt. Insgesamt sind es über 10k Einträge, die erweitert werden.

Ich habe gelesen, daß Pivot-Tabellen recht dynamisch sind, welche ich deshalb das nutzen will.
Gleichzeitig will ich eine Art dynamisches Dash-Board aufbauen, damit ich zur Jahreshauptversammlung die entsprechenden Informationen schnell liefern kann.

Aufgabenstellung:
An wieviel Trainingsterminen hat Sportler X teilgenommen?
Wie viel Trainingstermine gab es im Monat X?
Welche Klasse hat die meisten Theorie%?
Welches sind die 5 besten Disziplinen von Sportler X?

Wenn es recht ist: ich würde gerne zur Übersicht jeweils immer nur eine Frage stellen wollen und abarbeiten. Vielleicht ergründe ich dann weitere Probleme selbst. Ist das in Ordnung für Euch?


Folgende Frage(n), wie ich das anstellen soll:
Die Tabelle erweitert sich ja jedes Mal. Die Anzahl an den Sportlern, den ausgeübten Disziplinen etc. verändert sich immer wieder. 
Wie kann ich z. b. einen automatischen Trainingszeitenwert (Mittelwert) ermitteln, der sich am Monat X im Jahr/Zeitraum X orientiert? 

Manuell suche/filtere ich den gewünschten Monat, markiere die Zellen, addiere die Inhalte und teile sie durch die Anzahl der markierten Zellen. ==> bzw. =Mittelwert().
VBA scheidet mangels Wissen aus

Lieben Dank
alex

[Ralf A]

Hi Alex,

die Struktur Deiner Tabelle sollte noch ausgebaut werden. Denkbar ist ja, dass es mehrere Amelie's oder Peter gibt oder auch mehrere Monate, Jahre etc. Aber vielleicht sollte Deine Vorlage ja nur als Beispielvorlage dienen...

Meine bevorzugte Vorgehensweise wäre Power Query. In Deinem Fall, den Cursor in eine Zelle mit Daten der Quelltabelle setzen, Menü Daten --> Aus Tabelle Bereich. 
Da ich Dir schon mal eine Pivottable für die Termine und Termine pro Ort und Monat als Beispiel angefügt habe, kannst Du auch über das Menü Daten --> Abfragen und Verbindungen direkt zum PQ Editor gelangen. Doppelklick auf "Termine". Links im PQ Editor siehst Du dann alle erstellten Abfragen. Wenn Du auf eine davon klickst und im PQ Menü Start auf Erweiterter Editor klickst, kannst Du den M-Code sehen und bearbeiten. Ich habe Deine Daten in der Abfrage Termine nach Sportler, Disziplin und Monat1 gruppiert. Schließ den erweiterten Editor wieder. 


In der Mitte des Editors siehst Du die durch PQ erstellte Ergebnistabelle. Auf der rechten Seite, unterhalb von Angewendete Schritte stehen alle Bearbeitungsschritte der Abfrage Termine. Wenn Du auf einen dieser Schritte klickst, siehst Du, dass sich Tabelle verändert und das Ergebnis aller bis zu diesem Schritt ausgeführten Schritte. 

Du kannst analog zu diesem Beispiel versuchen, Deine anderen Auswertungswünsche in weiteren, separaten Abfragen zu erstellen. Im PQ Editor, Menü Start--> Neue Quelle --> Andere Quellen
Wenn Du eine neue Abfrage fertiggestellt hast, gehe zum Menü Datei --> Schließen und laden in... Dort kannst Du auswählen, ob, wo und wie die Daten angezeigt werden sollen. In Deinem Fall empfehle ich Pivot-Table Bericht auszuwählen.
In der danach erstellten Pivottable kannst Du alle in der Tabelle erzeugten Datenspalten je nach Bedarf in Zeilen-, Spalten- oder als Wert-felder (Ergebnis einer Aggregation) ziehen.

Natürlich kannst Du die Pivottable auch direkt aus der Quelldatentabelle erstellen (also ohne Umweg über PQ), aber ich denke, gerade die Gruppierung ist via PQ visuell verständlicher.

Ansonsten empfehle ich Dir für die Erstellung von Dashboards folgenden Link

[Lehrling]

Hallo Ralf,

danke für die ausführliche Erklärung. 
Ja, die Teilnehmernamen sind natürlich mit Nachnamen, von dem her eindeutig.
Daten habe ich ab 2019 für jeden Monat und Ort. etc. (insgesamt 98 Tausend Einträge - deswegen in der Tat nur ein kleiner Ausschnitt).

Meine Tabelle ist/soll die Quelldatei sein.
Mit Power Query muss ich mich erst beschäftigen. Dachte immer, daß das eine Art Verknüpfung zu einer anderen Datenquelle sei....

Wenn es Dir recht ist, würde ich Deine Erklärung erst einmal verstehen und abarbeiten wollen, bis ich dann mit der nächsten Frage dazu komme, denn aktuell fehlt mir
auf den ersten Blick die Anzahl der max. möglichen Trainingsminuten im Mittelwert und Teilnahmen pro Monat und Ort. 
Den Wert Anzahl nochmals reinzunehmen und den Mittelwert berechnen lassen ist hier der falsche Weg von mir.
Hast Du dazu ggf. eine IDee?

Danke und Grüße
Alex

[Ralf A]

Moin,

das stimmt schon, PQ ist eine Verknüpfung zu anderen Daten....Wie gesagt, geht auch ohne PQ. PQ hatte ich nur deshalb enpfohlen, weil man da besser sehen kann was passiert. Die eigentliche Wahl der Mittel ist Pivot.
Wenn Du in der Tabelle1 irgendwo in den Datenbereich klickst und im Menü Einfügen mal auf Empfohlene Pivottables, dann bekommst Du schon ein paar Vorschläge präsentiert, zwischen denen Du Dich nur noch entscheiden musst.

[Lehrling]

Hallo Ralf,

ja, Deine Idee mit dem PQ ist in der Tat sehr hilfreich zum Verstehen.
PQ und Pivot soweit klar.

Ich schaffe es aber nicht, dass ich die Trainingsminuten berechnet bekomme.
Aktuell (und weil keine Zellen in einer Tabelle leer sein sollen) wird mir in jeder Zeile eine Minutenzahl angezeigt.
Und bei den Sportlern benötige ich die genutzte Zeit (jeweils identisch und hinter jeder Disziplin), damit wir die Disziplin einzeln mit den anderen Sportler vergleichen können.
Werden diese Minuten jedoch addiert, kommt ein falscher Wert (16x93min als Angebot und 16x92min als genutzt) heraus. Und da steckt mein aktuelles und ursprüngliches Problem:

Wie erhalte ich für den jeweiligen Ort und den jeweiligen Monat die einmalige Trainingszeit? Also einmalig 93min als angebotene Zeit?

Danke für Deine Zeit und Geduld.

VG Alex

[Ralf A]

...ich schau mir das morgen nochmal an. Wäre gut, wenn Du bis dahin mal zeigst, was Du schon hast...

Zum Thema Pivot hier noch ein richtig gutes Tutorial

[ws-53]

Werden diese Minuten jedoch addiert, kommt ein falscher Wert (16x93min als Angebot und 16x92min als genutzt) heraus. Und da steckt mein aktuelles und ursprüngliches Problem:

Genauso steht es aber in der Mappe. Wenn dies falsch ist, dann musst du die richtigen Werte eintragen !!!

[Lehrling]

Hallo Ralf,

das, was ich bis dato versucht habe, führte nicht zum Ergebnis, was ich will. Solange ich das mit den angebotenen Termineinheiten nicht hinbekomme, widme ich mich keinem andren Problem.

Danke für die Geduld - und nicht stressen. Ich hänge damit auch schon 2 Monate herum und bekomme die Automatik nicht hin.

VG Alex

---

Genauso steht es aber in der Mappe. Wenn dies falsch ist, dann musst du die richtigen Werte eintragen !!!

Hi,

hast Du dir das nur durchgelesen was ich geschrieben habe? Und auch so nachvollziehen können?
Die Daten stammen nicht von mir, sondern von einer fremden SW, die die Daten genau so ausgibt.

Ich versuche nun für unsere Bedürfnisse mit den Daten zu arbeiten. Dazu gehört aber nicht die Veränderung von Werten, sondern die Berechnung.
HÄNDISCH kann ich selbstverständlich mir einen Mittelwert generieren. - Ich will aber bei >10k Zeileneinträgen und für die zukünftigen Einträge eine Automatik. Die bisherige Lösung via SVERWEIS stößt ja dann an Grenzen, wenn sich Orte oder Namen ändern und in die Formel eingegriffen werden muss.
Über PIVOT kann eine unerfahrene Person das aber steuern.

[Ralf A]

Werden diese Minuten jedoch addiert, kommt ein falscher Wert (16x93min als Angebot und 16x92min als genutzt) heraus. Und da steckt mein aktuelles und ursprüngliches Problem:

...ah... jetzt klingelts.... 

Du willst statt der Summe (die standardmäßig in den Wertfeldern gebildet wird) den Mittelwert angezeigt bekommen....
Klick mit der rechten Maustaste auf das entsprechende Wertefeld in der Pivotauswahl-Wertefeldliste und wähle Wertfeldeinstellungen aus. Dort kannst Du nicht nur die gewünschte Aggregation sondern auch das Format festlegen (dafür gibts unten einen Extrabutton).

[ws-53]

Die Daten stammen nicht von mir, sondern von einer fremden SW, die die Daten genau so ausgibt.

Wenn die Daten nicht so interpretiert werden dürfen, wie diese in der Tabelle stehen, dann fehlt die Erläuterung, wie diese zu interpretieren sind !!!

Auch deine Fragen:

An wieviel Trainingsterminen hat Sportler X teilgenommen?
Wie viel Trainingstermine gab es im Monat X?
Welche Klasse hat die meisten Theorie%?
Welches sind die 5 besten Disziplinen von Sportler X?

lassen sich ohne Erläuterungen nicht beantworten !!!

Fangen wir mit Peter in Stuttgart an, für den es im Jan. 2019 16 Einträge gibt. Somit kann festgestellt werden, dass er 16 Disziplinen trainiert hat, aber an wie vielen Terminen er teilgenommen hat, lässt sich nicht feststellen. Genauso wenig lässt sich die Gesamtanzahl an Terminen in einem Monat feststellen. Wenn nur ein Bruchteil einer Klasse zu 100% Theorie gelernt hat, dann hat diese aber vermutlich nicht die meiste Theorie. Und nach welchen Kriterien sollen die "besten" Disziplinen ermittelt werden ???