Musik Charts mit Excel Ermitteln?

um die +/- eines Songs auszuwerten wollte ich noch wissen ob man auch auswerten kann um wieviel Plätze sich ein Song verbessert bzw. verschlechtert hat.

Hmm? Hast Du doch schon vor der Nase, statt

=VORZEICHEN(WENNFEHLER(MITTELWERTWENNS([Platz];[Titel];[@Titel];[KW];[@KW]-1);0) - WENNFEHLER(MITTELWERTWENNS([Platz];[Titel];[@Titel];[KW];[@KW]);0))

einfach VORZEICHEN weglassen.

=(WENNFEHLER(MITTELWERTWENNS([Platz];[Titel];[@Titel];[KW];[@KW]-1);0) - WENNFEHLER(MITTELWERTWENNS([Platz];[Titel];[@Titel];[KW];[@KW]);0))

Dann musst Du jedoch die bedingte Formatierung anpassen, der Typ steht auf Prozent und bei den tatsächlichen Werten kommen da andere Symbole... logisch!? Mach irgendwas wie Zahl >=1.

Andreas.

Zu 2.
Formatiere die KW-Spalte als Standard (nicht als Datum), dann siehst Du die Nummern 1-52 in der Spalte und kannst auch danach filtern.

Zu 3.
Genau, traue nie einer Statistik die Du nicht selber gefälscht hast.
Bei jedem Turnier gibt es die Plätze 1,2,3 und wenn nun 2 Spieler den 1. Platz und 2 weitere Spieler den 2. Platz belegt haben, dann gibt es IMMER 2 Gold- und 2 Silbermedaillen.

Du kannst die Fälschung auch automatisieren, addiere zu der Bewertungsformel einfach eine kleine Zufallszahl.
L3: =10-[@Platz]*0,5+ZUFALLSZAHL()/10

Zu Ps.
a) wie lang ein Song in den Charts war bzw. was die beste und Schlechteste Platzierung
Um es zu verstehen: Mach mal eine neue PT ziehe den Titel in die Zeilen und KW in die Werte, bei Zahlen macht die PT immer eine Summe.
Klick in der PT mit rechts in die KW Spalte \ Werte zusammenfassen nach \ Anzahl
Zieh den Platz in die Werte \ Werte zusammenfassen nach \ Min
Zieh den Platz in die Werte \ Werte zusammenfassen nach \ Max

Das kannst Du natürlich auch so in Deiner Top10-PT machen.

b) Verbesserung bzw. Verschlechterung eines Songs
Da hast Du jetzt ein Problem weil die Daten nur für ein Jahr in der Tabelle sind.
In KW 1 müsstest Du ja auf die KW52 des letzten Jahres zugreifen, die hast Du nicht, also geht das in KW1 nicht.

Ansonsten ist es "relativ" einfach, Du brauchst ja nur die Differenz vom letztem zu diesem Platz.
Wenn der letzte 2 war und dieser 1, dann 2-1=1 also aufgestiegen, andersrum 1-2=-1 also abgestiegen.

Berücksichtigen wir die Quelle mal mit, dann ist es einfach:

Machen wir eine Spalte "Letzter Platz" hinzu mit dieser Formel
=SUMMEWENNS([Platz];[Titel];[@Titel];[Quelle];[@Quelle];[KW];[@KW]-1)

Machen wir noch eine Spalte "AufAb" hinzu
=VORZEICHEN([@LetzterPlatz]-[@Platz])
und wenden eine Bedingte Formatierung mit 3 Pfeil-Symbolsatz an, fertig.

Von der Quelle unabhängig ist das nicht so einfach, weil die Anzahl der Titel in einer Woche nicht mir der Anzahl der Titel in dieser Woche übereinstimmen müssen.
Um das zu rechnen brauchen wir den Mittelwert des Platzes eines jeden Titels in jeder KW.
=MITTELWERTWENNS([Platz];[Titel];[@Titel];[KW];[@KW]-1)
Das gibt jedoch einen Fehler zurück wenn der Titel in der Vorwoche nicht vorhanden ist. Also
=WENNFEHLER(MITTELWERTWENNS([Platz];[Titel];[@Titel];[KW];[@KW]-1);0)
und ebenso für diese Woche
=WENNFEHLER(MITTELWERTWENNS([Platz];[Titel];[@Titel];[KW];[@KW]);0)

Das können wir dann gleich kombinieren in unserer AufAb Spalte
=VORZEICHEN(WENNFEHLER(MITTELWERTWENNS([Platz];[Titel];[@Titel];[KW];[@KW]-1);0) - WENNFEHLER(MITTELWERTWENNS([Platz];[Titel];[@Titel];[KW];[@KW]);0))

Andreas.

Zu 2.

Ah, Du meinst den Filter in der Tabelle!?

Ja, da darfst Du Dich gerne bei Microsoft beschweren, bei denen ist immer Sonntag der 1. Tag der Woche.

How do I give feedback on Microsoft Office? - Office Support

Mach die KW in die Tabelle rein, dann kannst Du auch danach filtern.

Zu 3.

Klares nein, das Feld Platz in der PT kannst Du vergessen, die Berechnung des Ranges in der PT erfolgt nach den Punkten die in der Tabelle anhand des Platzes berechnet werden. Das Feld Platz gibt in Deinem Sinne in der PT keine sinnigen Werte.

Zu Ps.

Auch nein, wenn es mehrere gleiche Bewertungen gibt, dann gibt die PT die möglichen aus. Hier die Top 3 von 6, wobei jeder Platz/Wert 2x belegt/identisch ist.

Andreas.

Hallo Daniel,

kannst Du machen, ich würde es nicht machen, mein Tip: Investiere nicht soo viel Arbeit da rein, was Du bisher gesehen hast ist nur die Spitze des Eisberges.

Also kucken wir mal was der Vorteil wäre wenn wir je Jahr eine neue Tabelle machen: Entweder müssten wir die Datenquelle der Pivottabelle ändern oder die Pivottabelle neu erstellen. Ich bin mir nicht sicher ob die Felder in der PT erhalten bleiben bei einem Wechsel der Datenquelle... muss Du probieren. So oder so, das ist mit Arbeit verbunden.

Also eine neue Datei, würde auch gehen, dann wäre da ja schon eine Tabelle drin, in der ist das Datum, die Bewertungsformel und in der ersten Woche sind da ein paar Zeilen wo ein Titel drin steht ist, alle anderen sind leer. Probier es jetzt mal in der Beispieldatei aus und aktualisiere die PT... wie Du siehst berücksichtigt eine PT auch leere Zellen!

Machen wir mal den Umkehrschluss: Was ist wenn wir die Daten die wir haben und die PT so lassen und einfach immer neue Daten dazu tun. Macht also pro Jahr 1560 Zeilen, hattest Du ja schon ausgerechnet. Was kann Excel? 1048576 Zeilen / 1560 sind rund 672 Jahre. Mal realistisch gesehen, selbst nach 10 Jahren und 10 Quellen kommst Du nur auf 52000 Zeilen, das ist für Excel pippi-wenig, ein Datensplitting macht erst irgendwo bei 500000 Zeilen Sinn (je nachdem wieviel Spalten man hat), weil dann dauert die Auswertung schon einige Sekunden.

Eine sinnige Erweiterung wäre sich einzelne Arbeitsblätter je Quelle anzulegen und dort die neuen Daten einzupflegen, ggf. kann man auch Power Query verwenden um die Daten von einer Webseite zu ziehen. In jedem Fall kann man PQ verwenden um die einzelnen Datenquellen zu kombinieren und so die Datenquelle der PT erzeugen... ggf. mit einem Datenmodell, was dann noch weitere Auswertungen via DAX ermöglicht.

Vom Rennwagen in den Du jetzt eingestiegen bist dann Schrittweise bis zum springenden Monstertruck mit Flammen aus den Auspuffrohren ist es noch ein gutes Stück. ;-) Aber jeder hat mal klein angefangen, selbst ich lerne immer noch dazu. Bei dem riesigen Umfang den Excel kann vergisst man dann das eine oder andere wieder...

Learning by doing, wie man so schön sagt. Viel Spaß dabei.

Andreas.

Was ich zum Thema KW noch gerne wissen möchte ist für die Pivot das Datum und die KW wichtig oder reicht es aus wenn ich nur eine Spalte Datum (01.01.2024) anlege.

das kleine Feld 2023 vor der Überschrift hab ich übrigens eingefügt weil ich am Ende gerne hätte das wenn ich diesen Wert ändere das sich die Werte in der Spalte Datum automatisch aktualisieren

a) Kannst Du halten wie ein Dachdecker.

Wenn ich Werte habe die sich über einen großen Zeitraum erstrecken, dann kann ich fragen wie sehen die Daten nur in einem bestimmten Zeitabschnitt aus. Das nennt man Granularität oder auch Auflösung.

Eine Pivottabelle kann selber aus dem Datum das Jahr, das Quartal und den Monat auflösen, die Woche nicht. Wenn Du nach Wochen auflösen willst, dann brauchst Du eine Spalte KW.

b) Was macht das für einen Sinn??? Wenn am 7.12.2023 Helene auf Platz 1 war und Du 2024 in Dein kleines Feld schreibst, dann ändert sich das Datum zu 7.12.2024... ich hab von Musik keine Ahnung, aber ich gehe jede Wette ein das an dem Tag Helene nicht mehr auf Platz 1 ist!

c) Was das mit der Gruppierung soll erschließt sich mir auch nicht, es ist außerhalb der Tabelle von daher der Pivottabelle egal, da kannst Du hinschreiben was Du willst. Hast Du schon mal den Filter geöffnet?

Ist alles schon vorhanden:

Okay, ich schnappe mir mal die Daten aus "Schlager Charts" und dann ziehe ich mal das Datum über den Titel, da passiert nämlich was merkwürdiges:

So kann man nach bestimmten Zeiträumen gruppieren. Wenn Du nun einen Datenschnitt einfügst, dann kannst Du auch welche für diese automatisch erzeugten Felder erstellen.

Ich filter mal Schlager in 2022, dann sieht es so aus:

Bleibt noch ein letztes, der Titel alleine ist mir zu wenig, wir wollen auch noch den Interpreten sehen. Also ziehen wir das Feld mal unter den Titel.

Sieht nicht so dolle aus, oder?

Eine Pivottabelle hat verschiedene Layouts, dieses hier ist das Kurzformat.

Geh in die Registerkarte Entwurf \ Berichtslayout \ Im Tabellenformat anzeigen.

Dann sieht es im ersten Moment noch schlimmer aus!

Entwurf \ Teilergebnisse \ Teilergebnisse nicht anzeigen

PivotTable-Analyze \ Schaltflächen +/-

Das war's, der Anfang ist gemacht. Zum Abschluss nochmal der Link zur Datei:

https://www.dropbox.com/scl/fi/7n7emakusadfwefvt6w3r/857861cb-70cd-4fda-9ada-fef514d1e5a7.xlsx?rlkey=1gb2ytruv9hb2stvb80gfxqmd&dl=1

Fast vergessen: Irgendwas bunt machen bei 1560 Zeilen pro Jahr und das über mehrere Jahre... Ich kann es nachvollziehen das Du denkst das ist schick, ich war auf der Schiene auch mal. So bis 100 Zeilen geht es gerade noch so eben, alles darüber ist nutzlos.

Mit der Pivottabelle kannst Du ruckzuck die Songs zählen und siehst das Ergebnis auf einen Blick. Bedingte Formatierung funktioniert mit Formeln... aufwendig bis der Arzt kommt. Versuch Dir mal vorzustellen was für ein Aufwand es ist das was ich Dir gezeigt habe nur mit Formeln nachzubauen.

Andreas.