Musik Charts mit Excel Ermitteln?

Hallo Daniel,

Zu 1. Das Datum der 1. KW in einem Jahr kann man z.B. so berechnen:

B2: =("4.1."&A2)-REST("2.1."&A2;7)

Zu 2. In der PT ist ein Filter nach "Jahr" 2024 gesetzt, am 7.1.2024 gibt es jedoch keine Daten, da steht Dein Jahr auf 2023 und 2013:

Du vertust Dich mit dem Feld Jahr und dem Jahr aus dem Datum, daher hatte ich das in Erscheinungsjahr umbenannt => klare Unterscheidung! Wenn keine Daten da sind gibt die PT auch nichts aus.

Zu 3. Kannst Du halten wie ein Dachdecker.

Wenn Du Top3 auf Titel anwendest, dann bekommst Du die Top3 von Titel.

Wenn Du Top3 auf Interpret anwendest, dann bekommst Du die Top3 von Interpret. 

Wenn Du Top3 auf Genre anwendest, dann bekommst Du die Top3 von Genre.

Wenn Du alles zusammen anwendest bekommst Du die Schnittmenge der gefilterten Daten.

Zu Ps. Die Quelle der PT ist die Tabelle, wenn Du die Datei kopierst, das Jahr änderst und neue Daten eingibst, wieso willst Du dann die PT neu anlegen??? Macht keinen Sinn, die vorhandene funktioniert.

Andreas.

Ich habe jetzt die Tabelle für 2024 in einer neuen Arbeitsmappe eingerichtet allerdings hab ich noch ein Formel bzw. Filter Probleme:

1.vor der Tabelle habe ich mir 2 Spalten angelegt eine mit den Wochentagen & eine mit der KW anhand dieser Daten würde ich mir gerne das Datum berechnen lassen damit ich in der Zeile mit dem Jahr jedes Jahr nur das Jahr ändern muss und sich der Rest automatisch aktualisiert natürlich für Jedes Jahr ein eigenes Blatt.

2.statt der Gruppierungen würde ich den Datums Filter gerne so einstellen das immer nur eine Woche angezeigt wird das Problem ist aber das Excel den Sonntag der Aktuellen Woche immer rausschmeißt.

3.muss ich bei der Pivot die Spalte Interpret auch filtern oder reicht es wenn ich nur die Spalte Titel filtere bzw. sortiere um die Top 5/10 zu ermitteln?

Ps. Das Top 5/10 Ergebnis sollte aus allen 3 Quellen Bestehen da es sein kann das bei einer Einzelauswertung Pro Quelle am Ende speziell bei den Top 5 nicht genügend Songs übrig bleiben. Die Pivot muss ich pro Jahr leider auch neu anlegen. Das mit PQ klingt mir zu Komplex trage die Daten lieber per Hand nach.

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.

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...

c) Was das mit der Gruppierung soll erschließt sich mir auch nicht,...

https://learn-attachment.microsoft.com/api/attachments/b0dad090-3748-4167-8907-572bf1215633?platform=QnA&fud_access=wJJIheezUklbAN2ppeDns8cDNpYs3nCYjgitr%2BfFBh2dqlqMuW7np3F6Utp%2FKMltnRRYFtVjOMO5tpbpW9UyRAwvLeec5emAPixgq9ta07Dgnp2aq5eJbnfd%2FU3qhn54dSP0eozKzBVJ1fMt1XPUwoWxrP0sBKh2bQjNTemR06E5asSPEPTAmh%2BoYq0DJ1HX6RtPmxu%2FXyoerjAm3e%2FqWAHNLyb9SgAFAf12VaJ4MHIw%2Ble1uoVYK2U8SMdcCr6SHoAsd8B3iPw3P5eVGdPnhczYcvyXvq5TASpmUJ3YXA%2B9SVDU5q6QqfoYRdz2uaFENghvAjkCck09TguvpYpdElEgkBZ9L%2BgX2Api6BnOeQ2qBIGBHnF582CqYThIvsIo0eMhtYogPYY8f0B9QY6J67jI5zngPEOj9p7Mbu7IoUM%3D

Das war's, der Anfang ist gemacht.

Nur um das ganze jetzt grob zusammenzufassen damit ich mir am Wochenende die Fertige Vorlage für 2024 erstellen kann:

✔️Ich erstelle mir eine Tabelle mit 52 10er Blöcken x3 weil ich ja 3 Quellen auswerten möchte mit den ganzen Filtern etc,... wenn die Filter eine bessere alternative zu den Gruppierungen ist kann ich die 1.3 Spalten Löschen.

✔️und zum Thema kleines Feld mit dem Jahr war meine Idee nur das wenn ich auf das Feld klicke das ich wie bei einem Hamburgersymbol das Folgejahr auswählen kann und dann mit der entsprechenden Formel wieder das Datum einstelle und eine neue Wertung beginnen kann ohne dafür am Ende über die Jahre mehrere Tabellenblätter verwenden zu müssen. Habe das mal so ähnlich in einem YouTube Tutorial gesehen.

✔️Ich hätte zum Abschluss gerne das wenn ich jetzt die Vorlage für die Folgejahre einrichte daß wenn ich in der 1.Zeile das Datum eintrage dass sich für das restliche Jahr die Spalte so aktualisiert das je nachdem welche Quelle die 1.ist die ausgewertet wird:

Mo. = Mix1.de

Do. = Schlager-Charts

So. = Schlagerparade

Fortgeführt wird.

Weil wenn jetzt z.B.: die 1.Quelle Sonntag ist muss ich ja eine andere Formel zum Aktualisieren des Datums verwenden als bei Montag bzw. Donnerstag.

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.