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, 4366 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 ..
Ähnliche Inhalte
Microsoft Office
gelöst Excel 2010 Makro Ausgabe in bestimmter Tabelle ausgeben (6)

Frage von dressa zum Thema Microsoft Office ...

Microsoft Office
MS Office Excel - Formel wird angezeigt, aber nicht berechneter Wert!

Tipp von holli.zimmi zum Thema Microsoft Office ...

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

Frage von nicki01 zum Thema Microsoft Office ...

Neue Wissensbeiträge
Windows 10

Plattformdienst für unbekannte Geräte (7023)

Tipp von KMUlife zum Thema Windows 10 ...

Administrator.de Feedback

Umgangsformen auf der Seite

(7)

Information von Frank zum Thema Administrator.de Feedback ...

Windows 10

Windows 8.x oder 10 Lizenz-Key aus dem ROM auslesen mit Linux

(11)

Tipp von Lochkartenstanzer zum Thema Windows 10 ...

Heiß diskutierte Inhalte
Windows Server
Windows Server Komplettspiegelung (15)

Frage von pdiddo zum Thema Windows Server ...

Batch & Shell
gelöst Dir tc Befehl unter Windows 10 macht Probleme (14)

Frage von sugram zum Thema Batch & Shell ...

Windows Server
Windows Server 2016 RDS Remoteapp Anzeigefehler (11)

Frage von qlnGenius zum Thema Windows Server ...