Benötige Excel Formel zur Datumsüberprüfung auf Kalendewoche und Addition bestimmter Werte
Hi an alle Excel Experten,
da die Frage etwas komplizierter ist, mache ich sie lieber an einem konkreten Beispiel fest:
Ich habe 2 Tabellen in Excel 07:
1. Tagestabelle
...
2. Wochentabelle
...
Nun möchte ich eine Formel für das Feld ' Wochentabelle' `Anzahl KW 1,KW 2, ...` erstellen, die die Datumsspalte aus Tabelle 1 ausliest und bei jedem Datum, das der KW 1 entspricht, die Anzahl addiert und ausgibt.
So das im Endeffekt die 2. Tabelle sich automatisch auffüllt.
Ergebnis sollte in diesem Beispiel dann sein:
2. Wochentabelle
Leider geht die richtige Überprüfung auf die KW in Excel 07 nur mit einer längeren Formel, hier zur Hilfe:
KÜRZEN((A1-DATUM(JAHR(A1+3-REST(A1-2;7));1;REST(A1-2;7)-9))/7)
Ich weiß nicht, ob mein Vorhaben überhaupt möglich ist, bin aber für jeden Hinweis dankbar ;)
mfg Alucard7
Ich habe 2 Tabellen in Excel 07:
1. Tagestabelle
Datum | Anzahl |
08.01.2011 | 12 |
09.01.2011 | 123 |
10.01.2011 | 45 |
11.01.2011 | 6 |
12.01.2011 | 78 |
2. Wochentabelle
Datum | KW | Anzahl |
03.01 - 09.01 | 1 | |
10.01 - 16.01 | 2 |
Nun möchte ich eine Formel für das Feld ' Wochentabelle' `Anzahl KW 1,KW 2, ...` erstellen, die die Datumsspalte aus Tabelle 1 ausliest und bei jedem Datum, das der KW 1 entspricht, die Anzahl addiert und ausgibt.
So das im Endeffekt die 2. Tabelle sich automatisch auffüllt.
Ergebnis sollte in diesem Beispiel dann sein:
2. Wochentabelle
Datum | KW | Anzahl |
03.01 - 09.01 | 1 | 135 |
10.01 - 16.01 | 2 | 129 |
Leider geht die richtige Überprüfung auf die KW in Excel 07 nur mit einer längeren Formel, hier zur Hilfe:
KÜRZEN((A1-DATUM(JAHR(A1+3-REST(A1-2;7));1;REST(A1-2;7)-9))/7)
Ich weiß nicht, ob mein Vorhaben überhaupt möglich ist, bin aber für jeden Hinweis dankbar ;)
mfg Alucard7
Please also mark the comments that contributed to the solution of the article
Content-Key: 179010
Url: https://administrator.de/contentid/179010
Printed on: April 27, 2024 at 04:04 o'clock
5 Comments
Latest comment
Hallo Alucard7!
Wenn die abgebildeten Tabellen jeweils in A1 beginnen und die erste Tabelle "Tagestabelle" heißt, könntest Du folgende Formel versuchen:
Da es sich um eine Array-Formel handelt, ist die Eingabe (auch nach jeder Änderung) mit der Tastenkombination
Grüße
bastla
Wenn die abgebildeten Tabellen jeweils in A1 beginnen und die erste Tabelle "Tagestabelle" heißt, könntest Du folgende Formel versuchen:
=SUMME((KÜRZEN((Tagestabelle!$A$2:$A$6-DATUM(JAHR(Tagestabelle!$A$2:$A$6+3-REST(Tagestabelle!$A$2:$A$6-2;7));1;REST(Tagestabelle!$A$2:$A$6-2;7)-9))/7)=B2)*Tagestabelle!$B$2:$B$6)
Strg
+ Umsch
+ Eingabetaste
abzuschließen. In der Bearbeitungszeile wird die Formel dann innerhalb geschwungener Klammern angezeigt.Grüße
bastla
Hallo Alucard7!
Die absoluten Bezüge sind erforderlich, damit die Formel für die restlichen Kalenderwochen nach unten kopiert werden kann. Alternativ könnte auch ein Bereichsname verwendet werden.
Um auch für leere Zellen vorzusorgen, könnte die Formel auch
lauten und so alle Daten bis maximal zur Zeile 6000 erfassen; für die gesamten Spalten A und B wäre
zu verwenden (wobei dabei dann allerdings, mit entsprechender Auswirkung auf die Performance, tatsächlich alle 1.048.576 Zeilen abgearbeitet werden) ...
Grüße
bastla
soweit ich das jetzt teten konnte, hat diese Formel nur das Datum für den 03.01 geholt und geschrieben, anstatt aller Daten für die 1. KW
Bei meinem (erfolgreichen) Test steht "08.01.2011" in Zelle A2 der Tabelle "Tagestabelle" und "12" in Zelle B2 dieser Tabelle und die Daten reichen nur bis zur Zeile 6 ...Die absoluten Bezüge sind erforderlich, damit die Formel für die restlichen Kalenderwochen nach unten kopiert werden kann. Alternativ könnte auch ein Bereichsname verwendet werden.
Um auch für leere Zellen vorzusorgen, könnte die Formel auch
=SUMME(WENN(ISTZAHL(Tagestabelle!$A$1:$A$6000);(KÜRZEN((Tagestabelle!$A$1:$A$6000-DATUM(JAHR(Tagestabelle!$A$1:$A$6000+3-REST(Tagestabelle!$A$1:$A$6000-2;7));1;REST(Tagestabelle!$A$1:$A$6000-2;7)-9))/7)=B2)*Tagestabelle!$B$1:$B$6000;0))
=SUMME(WENN(ISTZAHL(Tagestabelle!$A:$A);(KÜRZEN((Tagestabelle!$A:$A-DATUM(JAHR(Tagestabelle!$A:$A+3-REST(Tagestabelle!$A:$A-2;7));1;REST(Tagestabelle!$A:$A-2;7)-9))/7)=B2)*Tagestabelle!$B:$B;0))
Grüße
bastla