☰

1. Holen Sie sich die Namen des ersten und letzten Blattes

tag-icon • tag-icon microsoft-excel worksheet-function
1. Holen Sie sich die Namen des ersten und letzten Blattes

Ich habe eine Datei, in der Daten pro Tag in separate Arbeitsblätter (TABs) eingegeben werden. Das Layout ist identisch. Ich suche nach einer Lösung, um aus diesen separaten TABs einen dynamischen Durchschnitt der letzten 1–3 Monate zu ermitteln. Jeden Tag wird ein neues TAB erstellt. Das jeden Monat manuell zu tun, würde viel zu viel Zeit in Anspruch nehmen. Irgendwelche Vorschläge?

Antwort1

Sie können es mit Daten > Konsolidierung oder 3D-Formel versuchen

Beispiel: Sie haben folgende Blätter: Jan Feb Mar Zusammenfassung

Angenommen, Sie haben auf jedem Blatt einige Zahlen in A1

Gehen Sie zum Übersichtsblatt und geben Sie in A1 die folgende Formel ein: =average('Jan:Mar'!A1)

Antwort2

Dazu führen wir zwei Schritte aus:

1. Holen Sie sich die Namen des ersten und letzten Blattes

So wie ich das verstehe, fügen Sie für jeden Tag ein neues Blatt hinzu. Sie müssen sicherstellen, dass die Blattnamen gleich formatiert sind. Nehmen wir beispielsweise an, das Format ist: TT-MM-JJ (der 1. August 2020 lautet also 01-08-20).

In diesem Schritt möchten wir eineTabellenbereichstextder letzten 3 Monate (zum Beispiel:01-03-20:01-01-20), die von der INDIREKTEN Formel verwendet werden soll, um eine 3D-Formel zu erstellen (im zweiten Schritt).

Nehmen wir an, dassLDATEhält diezuletztrelevantes Datum (es kann also ein Verweis auf eine andere Zelle sein oder eine Formel wie HEUTE() oder HEUTE()-1 usw.). Entscheiden Sie nun, was dasErsterelevantes Datum. Wenn es 3*30 Tage vor dem letzten Datum liegt, verwenden Sie:

=LDATE-3*30

Oder, wenn es genau 3 Monate vor DATE sein soll, verwenden Sie:

=DATE(YEAR(LDATE),MONTH(LDATE)-3,DAY(LDATE))

Nennen wir dieses erste DateFDATE.

Schließlich ist dies unser formatierter Blattbereichstext (beachten Sie, dass dies davon ausgeht, dass Sie die Blattnamen als TT-MM-JJ formatiert haben. Ändern Sie es bei Bedarf einfach in ein anderes Format):

="'"&TEXT(LDATE,"DD-MM-YY")&":"&TEXT(FDATE,"DD-MM-YY")&"'!"

Der Wert sollte jetzt folgendermaßen aussehen:'14-07-20:14-04-20'!

Nennen wir den gesamten Bereich alsBLATTBEREICH.

2. INDIREKT + 3d Formel

Die Funktion INDIREKT gibt einen gültigen Verweis aus einer gegebenen Textzeichenfolge zurück, und „Tabelle1:Tabelle4“!A1:B2 ist ein Bereich aller A1:B2-Zellen in allen Tabellen zwischen Tabelle1 und Tabelle4.

Mit dem Folgenden ermitteln Sie den Durchschnitt Ihres Dynamikbereichs (vorausgesetzt, Sie benötigen beispielsweise den Durchschnitt aller A3-Zellen):

=AVERAGE(INDIRECT(SHEETRANGE&"A3"))

Oder, wenn wir alles in einer Zelle ablaufen lassen wollen (mit Ausnahme von LDATE, das Sie selbst definieren können):

=AVERAGE(INDIRECT("'"&TEXT(LDATE,"DD-MM-YY")&":"&TEXT(DATE(YEAR(LDATE),MONTH(LDATE)-3,DAY(LDATE)),"DD-MM-YY")&"'!"&"A3"))

Viel Glück!

verwandte Informationen