AW: außerdem
15.08.2022 12:11:25
Christian
Hallo Daniel,
auch das habe ich ja bereits gesagt, dass man ja trotzdem Hilfsspalten einfügen kann und Formeln optimieren kann. Wobei ich jetzt dazu sagen muss, da hat sich in 13 Jahren so vieles aufgebaut, was ich mit den Formeln alles berücksichtige und abdecke, das würde eine lange Liste an Dingen, die auch die neuen Formeln leisten müssen. Weil auch wenn ein Profi sicherlich die Formeln effektiver formulieren kann als ich als Laie, habe ich mir ja trotzdem mit jedem einelnen Schritt etwas dabei gedacht.
Zu deiner Anmerkung mit der Geschwindigkeit, da gebe ich dir natürlich recht.
Ich versuche das ganze mal aufzubröseln. Ich wollte es eigentlich vermeiden, weil ich mir schon denke dass das mehr verwirrt als hilft, aber wenn wir Performance verbessern wollen, bleibt wohl keine andere Wahl.
Also erstmal allgemein handelt es sich um eine Liste von Filmen und Schauspielern, die in diesen Filmen mitgespielt haben.
Quelle der Daten sind die Blätter Filme und Leute, welche wiederrum als Datenquelle CSV Dateien nutzen, die ich bei www.imdb.com zusammengestellt und heruntergeladen habe.
Bei IMDB können jedoch auch Änderungen stattfinden, z.b. das ein Schauspieler durch Hochzeit einen neuen Namen hat oder dass ein vorher unbekanntes Geburtsdatum jetzt bekannt ist oder dass IMDB merkt, dass ein Film schon früher veröffentlicht wurde und daher das VÖ Datum ändert, oder es kann auch einfach sein, dass ich Einträge der Liste hinzufügen möchte.
Aus den genannten Gründen lade ich regelmäßig neue CSV Dateien runter und aktualisiere die entsprechenden PQ Abfragen.
Neue Einträge im Blatt Ergebnis mache ich einfach in dem ich die entsprechenden Codes in Spalte A und D einfüge und die weiteren Berechnunge machen dann die Formeln.
gut nun zu den einzelnen Formeln:
Spalte B:
=XVERWEIS(A2;Filme!$A$2:INDEX(Filme!A:A;neue!E$1);Filme!$B$2:INDEX(Filme!B:B;neue!E$1);"";0;1)
ist denke ich klar, XVERWEIS sucht passend zu dem Code in Spalte A den Filmtitel
Spalte C:
=WENN(XVERWEIS(A2;Filme!$A$2:INDEX(Filme!A:A;neue!E$1);Filme!$C$2:INDEX(Filme!C:C;neue!E$1);"";0;1) ="";0;XVERWEIS(A2;Filme!$A$2:INDEX(Filme!A:A;neue!E$1);Filme!$C$2:INDEX(Filme!C:C;neue!E$1);"";0;1))
ist denke ich klar, XVERWEIS sucht passend zu dem Code in Spalte A das Veröffentlichungsdatum, ist kein Datum vorhanden wird 0 ausgegeben.
Hierzu ist folgendes zu sagen, ich gebe 0 aus und nicht "" weil wenn ich "" ausgäbe und dann Spalte C wie es aktuell ist aufsteigend sortiere, die Leerzellen am Anfang stünden, sie sollen aber am Ende stehen, daher die 0. Die 0 habe ich dann über benutzerbedingte Formatierung ausgeblendet.
Spalte E:
=XVERWEIS(D2;Leute!$A$2:INDEX(Leute!A:A;neue!E$2);Leute!$B$2:INDEX(Leute!B:B;neue!E$2);"";0;1)
ist denke ich klar, XVERWEIS sucht passend zu dem Code in Spalte D den Namen des Schauspielers
Spalte F:
=WENN(XVERWEIS(D2;Leute!$A$2:INDEX(Leute!A:A;neue!E$2);Leute!$C$2:INDEX(Leute!C:C;neue!E$2);"";0;1) ="";"";XVERWEIS(D2;Leute!$A$2:INDEX(Leute!A:A;neue!E$2);Leute!$C$2:INDEX(Leute!C:C;neue!E$2);"";0;1))
ist denke ich klar, XVERWEIS sucht passend zu dem Code in Spalte D den Geburtstag des Schauspielers und gibt "" aus, wenn das Datum unbekannt ist.
Spalte G:
=WENN(ODER(C2=0;F2="");"";DATEDIF(F2+1461000;C2+1461000;"Y"))
prüft erstmal ob Geburtstag und Veröffentlichungsdatum vorhanden sind und wenn ja berechnet sie wie alt der Schauspieler in Jahren war als der Film veröffentlicht wurde. Die Addition von 4000 jahren oder 1461000 Tagen ist bereits ein Vorgriff darauf dass ich jetzt schon weiß dass irgendwann auch mal Leute in der Liste stehen werden, die vor dem 1.1.1900 geboren sind und auch weiß dass die PQ Abfrage daraus dann eine negative Zahl machen wird.
Spalte H
=WENN(G2="";"";DATEDIF(F2+1461000;C2+1461000;"YD"))
das selbe wie in Spalte G nur mit YD, da ich ODER(C2=0;F2="") schon in Spalte G geprüft habe, reicht es jetzt aus zu Prüfen ob G2 leer ist.
Spalte I
=WENN(G2="";"";C2-F2)
Altersunterschied in Tagen, brauche ich ausschließlich für den Text in Spalte R.
Spalte J
=WENN(G2="";"";RANG(I2;$I$2:INDEX(I:I;neue!E$3);1))
Berechnung des Ranges der Werte in Spalte I, brauche ich nur für statistische Zwecke.
Bei den Formeln in K bis N wird es jetzt wieder komplizierter.
Alle Formeln in diesen Spalten sind so aufgebaut, dass sie nur Filme berücksichtigen, die zum Zeitpunkt in Spalte C bereits veröffentlicht waren, keine Filme, die danach veröffentlicht wurden, daher reite ich auch so auf der Wichtigkeit der Sortierung rum.
Fangen wir an mit Spalte K
=WENN(G2="";"";WENN(ZÄHLENWENN(D2:INDEX(D:D;neue!$E$3);D2)=1;F2;""))
Sinn dieser Formel ist der Gedanke, dass in Spalte F jedesmal ein Geburtstag steht, sofern er bekannt ist. Diese Formel sorgt dafür dass wenn ein Schauspieler in mehreren Filmen mitgespielt hat, der Geburtstag nur noch beim ältesten Film in dem er mitgespielt hat ausgegeben wird, bei allen neueren Filmen nicht. Dies ist eine reine Hilfsspalte um Spalten M und N zu berechnen.
Spalte L
=WENN(C2=0;"";MAX(F2:INDEX(F:F;neue!$E$3)))
Gibt in jeder Zeile das Geburtsdatum des bis dato jüngsten Schauspielers aus (rein für Statistik)
=WENN(ZÄHLENWENN(K2:INDEX(K:K;neue!$E$3);">0")
Gibt in jeder Zeile das Geburtsdatum des bis dato 30. jüngsten Schauspielers aus (rein für Statistik). Deshab auch zuerst die Prüfung ob überhaupt bereits 30 Schauspieler mit Geburtsdatum vorhanden sind.
Spalte N
=WENN(G2="";"";RANG(F2;K2:INDEX(K:K;neue!$E$3);0))
die für meine Berechnungen eigentlich interessanteste Formel, die frage der wievielt jüngste Schauspieler in der Liste die Person zum Zeitpunkt der Veröffentlichung des Filmes war, An dem Punkt kann ich auch am einfachsten deutlich machen, wozu die Hilfsspalte K gut ist, wenn ich Spalte F als Bezug nehmen würde, würde jeder Schauspieler der mehrfach in der Liste steht, auch mehrfach bei der Berechnung des Ranges gezählt werden. In Spalte K steht jedes Datum pro Schauspieler nur noch einmal und damit wird jeder Schauspieler auch nur einmal in die Berechnung des Ranges mit einbezogen.
Spalte O
=WENN(ODER(K2="";M2="";N2>30);"";DATEDIF(M2;C2;"Y"))
Berechnung des Unterschiedes in Jahren zwischen der 30. jüngsten Person und dem jeweiligen Veröffentlichungsdatum (auch wieder rein für die Statistik)
Die Oder Formel dient dazu, dass nur in den Zeilen etwas ausgegeben wird, in denen sich im Vergleich zur nachfolgenden Zeile etwas ändert.
K2="" ist die Prüfung ob der Schauspieler schonmal in einem älteren Film mitgespielt hat
M2="" ist die Prüfung ob zu dem gefragten Zeitpunkt überhaupt schon 30 Schauspieler in der Liste standen
N2>30 ist die Prüfung, ob der Schauspieler in der Zeile in der wir uns befinden zu den 30 jüngsten Schauspielern gehört hat.
Spalte P
=WENN(O2="";"";DATEDIF(M2;C2;"YD"))
selbes mit YD
Spalte Q
=WENN(O2="";"";C2-M2)
selbes in Tagen
Spalte R kann man ja denke ich so lassen wie es ist
Noch eine Anmerkung zu den Formeln in den Spalten K bis N
Alle 4 Formeln brücksichtigen immer nur die Filme, die zu dem jeweiligen Zeitpunkt veröffentlicht waren, nicht die Filme, die danach veröffentlicht wurden.
Jetzt ist es aber so, dass die neusten Filme zuerst in der Liste stehen und die ältesten zuletzt.
Deshalb sind in diesen Formeln die Bereiche so aufgebaut, dass in der ersten Zeile noch alle Zeilen berücksichtigt werden und dann mit jeder weiteren Zeile eine weniger.
oder in Formeln ausgedrückt an Beispiel von der Formel in Spalte K
=ZÄHLENWENN(D2:INDEX(D:D;neue!$E$3);D2)
Der Wert D2 ändert sich in jeder Zeile auf D3, D4 usw. während das Ende des Bereichs, welches ich in neue!E3 berechnet habe immer konstant bleibt.
Wenn du jetzt noch auf neue!E3 schaust, fällt dir sicher auf, dass dort 825 steht, obwohl die Tabelle Ergebnis bis Zeile 838 geht. Das liegt daran dass alle Formeln voraussetzen, dass ein Veröffentlichungsdatum bekannt ist, daher habe ich die Zeilen in denen keines steht, außen vorgelassen.
Ich hoffe ich habs jetzt geschafft alle Gedankegänge die dahinterstecken zu Papier zu bringen
Christian