Top-Themen

AppleEntwicklungHardwareInternetLinuxMicrosoftMultimediaNetzwerkeOff TopicSicherheitSonstige SystemeVirtualisierungWeiterbildungZusammenarbeit

Aktuelle Themen

Administrator.de FeedbackApache ServerAppleAssemblerAudioAusbildungAuslandBackupBasicBatch & ShellBenchmarksBibliotheken & ToolkitsBlogsCloud-DiensteClusterCMSCPU, RAM, MainboardsCSSC und C++DatenbankenDatenschutzDebianDigitiales FernsehenDNSDrucker und ScannerDSL, VDSLE-BooksE-BusinessE-MailEntwicklungErkennung und -AbwehrExchange ServerFestplatten, SSD, RaidFirewallFlatratesGoogle AndroidGrafikGrafikkarten & MonitoreGroupwareHardwareHosting & HousingHTMLHumor (lol)Hyper-VIconsIDE & EditorenInformationsdiensteInstallationInstant MessagingInternetInternet DomäneniOSISDN & AnaloganschlüsseiTunesJavaJavaScriptKiXtartKVMLAN, WAN, WirelessLinuxLinux DesktopLinux NetzwerkLinux ToolsLinux UserverwaltungLizenzierungMac OS XMicrosoftMicrosoft OfficeMikroTik RouterOSMonitoringMultimediaMultimedia & ZubehörNetzwerkeNetzwerkgrundlagenNetzwerkmanagementNetzwerkprotokolleNotebook & ZubehörNovell NetwareOff TopicOpenOffice, LibreOfficeOutlook & MailPapierkorbPascal und DelphiPeripheriegerätePerlPHPPythonRechtliche FragenRedHat, CentOS, FedoraRouter & RoutingSambaSAN, NAS, DASSchriftartenSchulung & TrainingSEOServerServer-HardwareSicherheitSicherheits-ToolsSicherheitsgrundlagenSolarisSonstige SystemeSoziale NetzwerkeSpeicherkartenStudentenjobs & PraktikumSuche ProjektpartnerSuseSwitche und HubsTipps & TricksTK-Netze & GeräteUbuntuUMTS, EDGE & GPRSUtilitiesVB for ApplicationsVerschlüsselung & ZertifikateVideo & StreamingViren und TrojanerVirtualisierungVisual StudioVmwareVoice over IPWebbrowserWebentwicklungWeiterbildungWindows 7Windows 8Windows 10Windows InstallationWindows MobileWindows NetzwerkWindows ServerWindows SystemdateienWindows ToolsWindows UpdateWindows UserverwaltungWindows VistaWindows XPXenserverXMLZusammenarbeit
GELÖST

Benötige Excel Formel zur Datumsüberprüfung auf Kalendewoche und Addition bestimmter Werte

Frage Microsoft Microsoft Office

Mitglied: Alucard7

Alucard7 (Level 1) - Jetzt verbinden

16.01.2012, aktualisiert 17:21 Uhr, 4353 Aufrufe, 5 Kommentare

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

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
Mitglied: bastla
16.01.2012 um 17:51 Uhr
Hallo Alucard7!

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)
Da es sich um eine Array-Formel handelt, ist die Eingabe (auch nach jeder Änderung) mit der Tastenkombination Strg + Umsch + Eingabetaste abzuschließen. In der Bearbeitungszeile wird die Formel dann innerhalb geschwungener Klammern angezeigt.

Grüße
bastla
Bitte warten ..
Mitglied: Alucard7
16.01.2012 um 18:06 Uhr
Hi 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.

Ich sehe insgesamt das Problem, aus einer ganzen Spalte 7 bestimmte, geprüfte Daten dynamisch zu holen und deren Zeilennachbarn zu addieren.

Gerade die absoluten und relativen Bezüge scheinen mir ziemlich problematisch.

mfg Alucard7
Bitte warten ..
Mitglied: bastla
16.01.2012 um 18:17 Uhr
Hallo Alucard7!
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))
lauten und so alle Daten bis maximal zur Zeile 6000 erfassen; für die gesamten Spalten A und B wäre
=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))
zu verwenden (wobei dabei dann allerdings, mit entsprechender Auswirkung auf die Performance, tatsächlich alle 1.048.576 Zeilen abgearbeitet werden) ...

Grüße
bastla
Bitte warten ..
Mitglied: Alucard7
16.01.2012 um 18:55 Uhr
Hi bastla,

Danke für die schnelle Antwort!

Das werde ich morgen noch genauer testen, vielleicht habe ich was übersehen.

Schönen Abend noch ;)

mfg Alucard7
Bitte warten ..
Mitglied: Alucard7
17.01.2012 um 10:17 Uhr
Hi bastla,

ich habe die Formel gerade getestet und kann berichten, dass sie super funktioniert

Vielen Dank für die kompetente Hilfe ;)

mfg Alucard7
Bitte warten ..
Neuester Wissensbeitrag
Windows 10

Powershell 5 BSOD

(8)

Tipp von agowa338 zum Thema Windows 10 ...

Ähnliche Inhalte
Microsoft Office
gelöst Excel-Formel oder VBA (7)

Frage von nicki01 zum Thema Microsoft Office ...

Microsoft Office
gelöst In Excel das Datum aus einer Registerlasche in einer Formel verwenden (7)

Frage von michael1306 zum Thema Microsoft Office ...

Microsoft Office
gelöst Excel: Matirx mit Formel automatisch befüllen (5)

Frage von sims zum Thema Microsoft Office ...

Microsoft Office
Excel verliert die Formel, behält aber den Wert (10)

Frage von derausgroenloh zum Thema Microsoft Office ...

Heiß diskutierte Inhalte
Microsoft
Ordner mit LW-Buchstaben versehen und benennen (21)

Frage von Xaero1982 zum Thema Microsoft ...

Netzwerkmanagement
gelöst Anregungen, kleiner Betrieb, IT-Umgebung (18)

Frage von Unwichtig zum Thema Netzwerkmanagement ...

Windows Update
Treiberinstallation durch Windows Update läßt sich nicht verhindern (17)

Frage von liquidbase zum Thema Windows Update ...