Ich habe eine Arbeitsmappe mit Blättern, eines für jedes Jahr, und muss den Durchschnitt aller Monate ermitteln, die im Jahr dieses Blattes vergangen sind. Die Blätter sind nach Jahr benannt und ich habe die Formel benannt: MONTHS.
=LET(year,VALUE(MID(CELL("filename"),SEARCH("]",CELL("filename"))+1,31)),IF(YEAR(TODAY())=year,MONTH(TODAY())-1,IF(year>YEAR(TODAY()),0,12)))
Und verwenden Sie es, um einen Durchschnitt zu erhalten:
Meistens funktioniert es, aber wenn ich Dinge in anderen Zellen oder Blättern mache, gibt es ein#BERECHNUNGFehler, dass leere Arrays nicht unterstützt werden. Wenn ich das Blatt neu berechne, behebt es sich von selbst, aber es ist sehr ärgerlich, das ständig tun zu müssen. Gibt es eine Möglichkeit, dies zu verhindern?
Antwort1
Meine persönliche Meinung, die auf jahrelanger Nutzung von APL beruht, ist, dass die Unterstützung leerer Arrays unerlässlich ist. Die Nichtunterstützung leerer Arrays ist ein Fehler von Microsoft.
Antwort2
Ich habe keine TAKE()Informationen zur Verfügung und kann daher nicht definitiv sagen: „Das ist Ihr Problem.“ Ich werde auf zwei Aspekte eingehen: Ihren MONTHSbenannten Bereich, der wahrscheinlich auf das ARBEITSMAPPE beschränkt ist, aber auf das BLATT beschränkt sein muss, damit er eine bestimmte Zelle auf jedem einzelnen Blatt angeben kann, und möglicherweise TAKE()auf zwei potenzielle Probleme, nämlich die Angabe ,,des Zeilenparameters und das erste unten angesprochene Problem, dass MONTHSmanchmal „0“ zurückgegeben wird, was bedeutet, TAKE()dass keine Spalten zurückgegeben werden sollen, also ein leeres Array.
Die Tatsache, dass Sie dieses Problem in einer Situation erwähnen, in der Sie an anderen Blättern arbeiten und dann zurückkehren, tendiert zur ersten Kategorie, dem Umfang MONTHSund daher CELL()nicht der Angabe einer bestimmten Zelle. Aber die Tatsache, dass die Angabe von 0Spalten wahrscheinlich ein leeres Array generiert und Ihnen mitgeteilt wird, dass leere Arrays nicht unterstützt werden, deutet darauf hin, dass dies das Problem ist. In diesem Fall sollte es sich jedoch nicht so einfach lösen. „Sollte nicht“ ist natürlich nicht gleichbedeutend mit „wird nicht“. Also die Grauzone.
Im Folgenden finden Sie jedoch drei wichtige Dinge, die Sie leicht überprüfen können. Um es einfacher zu machen, habe ich das Material mit dem Bereich „Named Range“ getestet und es ist so. (Ich habe auch die Standardmethode getestet, mit der Excel gezwungen wird, den beschriebenen Fehler niemals zurückzugeben, und es löst die Probleme nicht.) Welche Auswirkungen es haben könnte TAKE(), kann ich jedoch anhand von Tests nicht sagen. Sie werden jedoch sehen, wie Sie diese beiden Dinge leicht testen können. Ich setze auf den Bereich, der MONTHSam besten zu den beschriebenen Problemen passt. Das kommt also zuerst:
Ihre Formel hat drei mögliche Ergebnisse, eines davon ist 0.
So weit, so gut. In Ihrer Funktion verwenden Sie jedoch deren Ergebnisse, um anzugeben, wie viele Spalten an die Funktion TAKE()gesendet werden sollen .AVERAGE()
Aber wenn Sie das Array bilden und die MONTHSFormel zurückgibt 0, geben Sie an, TAKE()dass 0Spalten aus der Zeile übernommen werden sollen, in der es sich befindet. 0Spalten bedeuten ein leeres Array.
Damit dies für Sie funktioniert, benötigen Sie einen komplexeren Ansatz. Zum Beispiel:
Verwenden Sie einen , um das Ergebnis IF()zu testen . Wenn , wird ein Ergebnis von 0,00 $ in Zelle O5 zurückgegeben. Wenn dies nicht das Ergebnis ist, verwenden Sie die Formel so wie sie ist.MONTHS00AVERAGE()
Oder
Geben Sie a 1statt a zurück 0, wenn der yearberechnete Wert in der Zukunft liegt. Man könnte meinen, dass alle Zeilen auf diesem Blatt leer wären, sodass Sie den Durchschnitt einer Zelle mit einem Nullwert nehmen und Null zurückgeben würden. Wenn nicht, könnte eine ähnliche Logik erkennbar sein.
Ein weiterer Gedanke, der nicht direkt damit zusammenhängt und im Moment auch keine Probleme verursacht, wäre, dass CELL("filename")Excel durch Ihre Schreibweise gezwungen wird, jede Instanz seiner Verwendung auf ALLEN Blättern, nicht nur auf diesem, bei jeder Neuberechnung von Excel neu zu berechnen. Und wenn ich von der Arbeit auf einem anderen Blatt komme, habe ich das Problem, das durch einen Teil dessen, was Sie in der Frage angeben, beschrieben wird: Wenn Sie auf dem anderen Blatt sind und Excel eines der anderen Blätter neu berechnet, verwendet es das Blatt, auf dem Sie sich befinden, um eine Rendite zu erzielen, CELL()und nicht das Blatt, auf dem es verwendet wird, mit Ausnahme des Blattes, auf dem Sie sich befinden. Alle anderen erhalten also unglückliche Ergebnisse und Excel scheint dies zu erkennen und gibt auf allen einen Fehler zurück. Wie ich unten erwähne, ist dies nur für Sie ärgerlich oder peinlich, wenn sich ein Chef darüber beschwert, wenn die Blätter Sackgassen sind und ihre Ergebnisse keine Präzedenzzellen für andere Blätter sind, wie z. B. Summierungsblätter über die Jahre. Aber wenn sie es sind, speisen sie einen Fehler in diese Vorwärtsverwendungen ein, was dazu führt, dass sie Fehler zurückgeben ...
Dazu also: Dies verursacht ein Problem, nicht beim Bearbeiten des aktuellen Blatts, sondern beim Navigieren, um grundsätzlich jede beliebige Bearbeitung auf einem anderen Blatt vorzunehmen. Wenn Sie dies tun, geben die anderen Arbeitsblätter einen #VALUE!Fehler an ihre Zellen aus, die verwenden MONTHS. Das ist wohl kein so großes Problem, solange jedes Blatt in sich geschlossen ist und nicht in weitere Blätter einspeist. Sie würden auf das Blatt gehen, um etwas zu tun, und wären verärgert, die Auswirkung des Fehlers zu sehen (sicherlich eine „leere Array“-Situation, obwohl ich keine habe TAKE()und daher die genaue Auswirkung nicht testen kann … es könnte sein, dass es ein Array von Fehlern zurückgibt, um den Durchschnitt zu berechnen, und daher nicht den Fehler, sondern seinen eigenen Fehler AVERAGE()ausgibt . In jedem Fall wäre es nur ärgerlich für Sie und störend für die Chefs, aber bei Ihrem ersten Eintrag auf dem Blatt würde es sich lösen.#CALC!#VALUE!
Sie können dieses Problem beheben, indem Sie einen MONTHSBereich mit SHEET statt mit WORKBOOK erstellen. Und indem Sie für jede der beiden CELL()darin enthaltenen Funktionen eine Zelle angeben (eine beliebige Zelle, aber warum nicht Zelle A1?). Sobald Sie dies tun, ist das Problem behoben.
Zugegeben, das bedeutet, dass der benannte Bereich für jedes vorhandene Blatt erstellt werden muss, aber wenn man eine Vorlage hat oder neue Jahre hinzufügt, indem man ein aktuelles Blatt kopiert und Daten entfernt, wird der benannte Bereich mit BLATT-Bereich jedes Mal erstellt, wenn er kopiert und umbenannt wird. Also kein Problem für die Zukunft. Und löschen Sie MONTHSanschließend den benannten Bereich mit ARBEITSMAPPE-Bereich.
Wenn TAKE()die Zuweisung des „ ,,so no rows“-Parameters und manchmal die Zuweisung eines „ 0for the columns“-Parameters nicht die Ursache des Problems ist (ich gehe nicht so weit, aber es könnte sein, dass MS dort seine gesamte philosophische Herangehensweise geändert hat: Sie scheinen nicht INDEX()mehr das zu tun, was sie früher getan haben), dann CELLS()ist wahrscheinlich die Verwendung das Problem.
Soweit es geht, ist alles, was ich derzeit über die Funktion finden kann, nur ihr wiedergekäuter Werbebrei oder nett wiedergekäut in Form von netten Beispielen, Texten und Videos, aber da NIEMAND sich mit den Problemen der realen Nutzung befasst, spreche ich nicht aus der Erfahrung, die ich für Ihre Nutzung getestet habe. Ich stelle fest, dass MS in den letzten Jahren den Ansatz verfolgt hat, die alte „ ,,) INDEX()can use“ nicht zuzulassen, sondern dass sie, wenn sie sagen, dass der Zeilenparameter erforderlich ist, nicht einmal ihr altes „entweder Zeilen oder Spalten müssen angegeben werden“ meinen, sondern dass Sie, unabhängig davon, ob Sie einen Spaltenparameter angeben, einen Zeilenparameter angeben MÜSSEN. Wenn man außerdem hofft, dass sie Ihr ,,MONTHS)Argument so interpretieren, als ob sie „DIESE Zeile, so viele Spalten“ meinen, hofft man, dass sie eine implizite Schnittmenge verwenden, was in Wirklichkeit nicht der Fall sein könnte, da sie sich in der Praxis und als Konzept ABSICHTLICH davon entfernt haben.
Die Probleme sehen also wie folgt aus:
Sie müssen
MONTHSbenannte Bereiche mit SHEET-Bereich anstelle der (vermutlich) WORKBOOK-Version verwenden.TAKE()Das Problem besteht darin, dass die manchmal von zurückgegebene „0“ nicht verarbeitet wirdMONTHS.TAKE()Das Problem liegt darin, dass das Fehlen eines Zeilenparameters nicht behandelt wird.TAKE()wobei sowohl in Punkt 2 als auch in Punkt 3 oben das Problem besteht.
Bearbeiten:Nach all dieser Recherche und ohne etwas Hilfreiches zu finden, habe ich dies gepostet und dann auf eine weitere Suchseite geklickt … und eine Website gefunden, die ein Beispiel mit dem ,,MONTHS)oben oft erwähnten Teil enthält und zeigt, TAKE()dass es funktioniert, da der Ansatz verfolgt wird, alles bezüglich der Zeilen zu ignorieren und so alle Zeilen zurückzugeben, wie es bei der einfachen Verwendung von Parametern der INDEX()Fall wäre.
DIESER Aspekt ist also kein Problem. Tut mir leid, ich habe die grundlegende Antwort zu sehr bearbeitet, um diesen Teil zu entfernen, ohne ihn endgültig komplett neu zu schreiben, und jetzt ist es zu spät dafür, da mein Bett ruft. Aber diese Verwendung ist NICHT das Problem, da sein Beispiel eine angemessene Rendite für diese Verwendung zeigt. Sie steht hier:
https://www.get-digital-help.com/wie-man-die-take-funktion-nutzt/
Teil 4. im Layout, ungefähr 20–25 % der Seite nach unten.