Live-Forum - Die aktuellen Beiträge
Datum
Titel
19.04.2024 19:41:34
Bei VBA Ausführung Anzeige einfrieren
19.04.2024 17:05:40
Zählen von Zeilen, in denen in bestimmten Bereich Daten sind
19.04.2024 16:34:52
gemeinsamer Filter über drei Spalten
19.04.2024 15:39:42
Exceltabelle daten per combobox zu word Datei
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1476to1480
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1476to1480
1476to1480
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

In Monatskalender zweiten Tag suchen

In Monatskalender zweiten Tag suchen
18.02.2016 12:11:41
Jan
Hallo,
leider klappt der Upload der Datei hier immer noch nicht daher muss es wohl ohne gehen, aber folgende Problematik stellt sich mir:
Ich habe in Spalte A1 - A31 einen dynamischen Monatskalender d.h.
A B
1 01.02.16 - Mo 5
2 02.02.16 - Di 6
3 03.02.16 - So 3
4 ....
Nun möchte ich gern z.B. in Zelle C7 folgendes realisieren:
O-Ton "Suche den ersten Sonntag im Bereich A1 - A31 (z.B. A3) und summiere die 7 folgenden Werte der Spalte B (also summiere die Arbeitsstunden in Zelle b für die folgenden 7 Zeilen nach A3)"
Ebenso soll dann die zweite, dritte, vierte und ggf auch 5 Woche im Monat ermittelt werden.
Hat jemand eine Idee?
MfG,
Jan

20
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: mit SUMME() von INDEX() und AGGREGAT() ...
18.02.2016 12:44:51
INDEX()
Hallo Jan,
... allerdings sei bemerkt: am 3.02.16 war kein Sonntag!
Folgende Formel in C7:
=SUMME(INDEX(B:B;AGGREGAT(15;6;ZEILE(A1:A31)/(REST(A1:A31;7)=1);1)+1):INDEX(B:B;AGGREGAT(15;6; ZEILE(A1:A31)/(REST(A1:A31;7)=1);1)+7))
Gruß Werner
.. , - ...

evtl. so?
18.02.2016 13:14:35
Michael
Hi zusammen,
Werner fährt ja immer das große Geschütz mit AGGREGAT auf, aber wenn direkt unter der Tabelle in B keine weiteren Werte vorhanden sind, könnte es das auch tun, in C1 und runterkopieren:
=WENN(WOCHENTAG(A1)=1;SUMME(B2:B8);"")
Macht zwar nicht genau das Gleiche...
Schöne Grüße,
Michael

Anzeige
AW: evtl. so?
18.02.2016 13:31:54
Jan
Hallo Michael,
vielen Dank für deine Antwort aber die funktioniert in diesem Fall denke ich nicht da es ja auch mehrere Sonntage in einem Monat gibt und der Sonntag ja auch nicht immer auf den ersten den Monats fällt.
MfG,
Jan

AW: mit SUMME() von INDEX() und AGGREGAT() ...
18.02.2016 13:24:50
INDEX()
Hallo Werner,
erstmal vielen Dank und ja am 03.02. war Montag. Es war nur ein Beispiel.
Ich hab auch endlich die Datei hochladen können: https://www.herber.de/bbs/user/103679.xlsx
Ich möchte finalisiert gern das er erkennt wann die erste volle Woche im Monat ist und dann die Stunden aus Spalte F für diese addiert und in P38 schreibt.
Für die zweite Woche in P 41 usw. .
Dein Formel konnte ich darauf leider nicht anwenden. Ich habe da aber sicher auch einen Fehler gemacht.
Evtl. klappt es ja mit der angehangenen Datei besser.
MfG
Jan

Anzeige
AW: die für Deine Tabelle angepasste Formel ...
18.02.2016 13:48:32
...
Hallo Jan,
... sieht dann so aus:
=SUMME(INDEX(F:F;AGGREGAT(15;6;ZEILE(A21:A99)/(REST(A21:A99;7)=1);1)+1):INDEX(F:F;AGGREGAT(15;6; ZEILE(A21:A99)/(REST(A21:A99;7)=1);1)+7)) Dieser müsstest Du natürlich noch das Zellformat: [h]:mm
zuweisen.
Gruß Werner
.. , - ...

AW: die für Deine Tabelle angepasste Formel ...
18.02.2016 13:59:32
Jan
Hallo Werner,
ja das scheint zu funktionieren vielen Dank. Mich würde allerdings noch interessieren wie die Formel für die zweite / dritte Woche ausschaut.
So ganz durchschaue ich auch die Formel nicht. Wenn es dir nichts aus macht, könntest du mir dann bitte in einer Formel noch erklären was oder wofür die fett gedruckten Sachen stehen?
=SUMME(INDEX(F:F;AGGREGAT(15;6;ZEILE(A21:A99)/(REST(A21:A99;7)=1);1)+1) :INDEX(F:F;AGGREGAT(15;6; ZEILE(A21:A99)/(REST(A21:A99;7)=1);1)+7))
Sorry aber ich würde es gern gesamt verstehen. Ich bin heut vielleicht auch etwas auf dem Schlauch. Danke.
MfG,
Jan

Anzeige
AW: dann ersetze ... ;1)... durch 2;) ...
18.02.2016 14:14:16
...
Hallo Jan,
... also für die 3. Woche ungetestet so:
=SUMME(INDEX(F:F;AGGREGAT(15;6;ZEILE(A21:A99)/(REST(A21:A99;7)=1);3)+1) :INDEX(F:F; AGGREGAT(15;6; ZEILE(A21:A99)/(REST(A21:A99;7)=1);3)+7))
Für die 4. Woche analog könnte es aber evtl. in manchen Monaten zu Überlappungen mit Deiner Gesamtsumme in Spalte F kommen. Dazu muss die Formel noch etwas angepasst werden. Dazu und zu einer ausführlicheren Erklärung fehlt es mir momentan an Zeit.
F:F steht natürlich für die Spalte F (Deine Zeitwerte) Die Argumente 15;6 der Funktion AGGREGAT() sind in der MSO-Hilfe beschrieben.
Gruß Werner
.. , - ...

Anzeige
AW: die weiteren gewünschten Erklärungen und ...
18.02.2016 18:20:11
...
Hallo,
... in der 5 Monatswoche wird es und bei der 4. Woche kann es im Ausnahmefall wie bereits erwähnt dazu kommen, dass die Summierung mit der bisherigen Formel Deine Werte in F52:F53 mit einbezieht. Das ist natürlich nicht gewollt.
Deshalb nun wie angekündigt folgende erweiterte Formel in P35 und dies dann ziehend nach unten kopiert.

=WENN(O35="Gesamt ";SUMME(INDEX(F:F;AGGREGAT(15;6;ZEILE(A$21:A$99)/(REST(A$21:A$99;7)=1);
ZÄHLENWENN(O$35:O35;O$35))+1):INDEX(F:F;MIN(AGGREGAT(15;6;ZEILE(A$21:A$99)/(REST(A$21:A$99;7)=1);
ZÄHLENWENN(O$35:O35;O$35))+7;VERWEIS(9^9;A$1:A$99;ZEILE(A$1:A$99)))));"")
Mit der Teilformel: VERWEIS(9^9;A$1:A$99;ZEILE(A$1:A$99)) wird die letzte Datumszeile ermittelt, die als Maximalzeilenwert durch die eingefügte MIN()-Funktion erkannt wird.
Die Teilformel REST(A$21:A$99;7)=1 ermittelt für einen Sonntags-Datumswert ein WAHR
Die + 1 in der Formel ist notwendig, weil Du nach dem 1. Sonntag suchen aber mit der Auswertung ab Montag beginnen wolltest. Ich würde gleich nach dem 1. Montag suchen durch REST(A$21:A$99;7)=2 In diesem Fall würde das +1 entfallen. Dafür müsste dann aber in dem zweiten Formelteil anstelle +7 eine + 6 eingesetzt werden.
Die Formel in P35 würde somit in P35:
=WENN(O35="Gesamt ";SUMME(INDEX(F:F;AGGREGAT(15;6;ZEILE(A$21:A$99)/(REST(A$21:A$99;7)=2);
ZÄHLENWENN(O$35:O35;O$35))):INDEX(F:F;MIN(AGGREGAT(15;6;ZEILE(A$21:A$99)/(REST(A$21:A$99;7)=2);
ZÄHLENWENN(O$35:O35;O$35))+6;VERWEIS(9^9;A$1:A$99;ZEILE(A$1:A$99)))));"")
so lauten.
Gruß Werner
.. , - ...

Anzeige
huch - bei mir wäre das lediglich
18.02.2016 18:47:45
WF
.....&"F"&MIN(51;zweiter Vergleich + x)
WF

AW: dies ist statisch, ich habs dyn. definiert owT
18.02.2016 19:27:51
...
Gruß Werner
.. , - ...

kapiert der Normalo nur nicht
18.02.2016 19:45:18
WF
.

AW: gib ihm aber wenigsten die Chance owT
18.02.2016 19:51:37
...
Gruß Werner
.. , - ...

Da gibt es noch wichtigeres Abzuklären
18.02.2016 20:29:50
Josef
Hallo
Jan schreibt:
O-Ton "Suche den ersten Sonntag im Bereich A1 - A31…und summiere die 7 folgenden Werte
Das heisst also Summe Montag bis Sonntag
Was ist jetzt, wenn der 1. des Monats ein Montag ist?
In diesem Fall liefern die Formeln von WF und Werner unterschiedliche Resultate.
Wobei sich Werner genau an die Vorgaben von Jan hält, und nach dem ersten Sonntag sucht.
Jan sollte uns deshalb noch schreiben was für ihn nun richtig ist.
Gruss Sepp

Anzeige
AW: ergänzend hierzu ...
19.02.2016 07:56:27
...
Guten Morgen Sepp,
... auf die Thematik: Beginn eines Monats mit einem Montag (Beispiel: akt. Monat Feb. 16)) hatte ich bereits hingewiesen und dafür auch eine entsprechend modifizierte Lösungsformel gestern eingestellt.
Doch auch diese berücksichtigt nicht die Möglichkeiten, die man aus der Tabelle entnehmen kann. Da kein Monat 5 Wochen (volle) beinhalten kann, Jan aber 5 Wochen auswerten will, könnte es z.B. auch sein, dass nicht nur in der 5. Woche eine Restsumme ermittelt werden soll sondern auch in der 1. Woche, zumal ja jeder Tag (nicht nur die Werktage) ausgewertet werden soll.
Deshalb wollte ich gestern schon eine Lösungsformel aufstellen, die auf Basis der beinhaltenden und tangierenden Kalenderwochen des Monats beruht. Hatte dies aber dann doch unterlassen, weil ich mich vorläufig an der Vorgabe orientiert habe.
Hier ist also wirklich Jan gefragt nochmal nachzudenken bzw. exakt zu definieren was wirklich benötigt wird.
Gruß Werner
.. , - ...

Anzeige
vereinfachung der Formeln
19.02.2016 10:11:30
Josef
Hallo Werner
Nachdem ich jetzt die Formel von Jan für MW 1 analysiert habe, denke ich schon, dass deine Variante ab Zelle O38 das gewünschte Ergebnis bringt.
Zum einfacheren Verstehen habe ich jetzt einmal ein Beispiel mit statischen Formeln erstellt.
Auch die Formel in O35 habe ich noch etwas eingekürzt.
Die 5. MW habe ich jetzt einmal weggelassen. Ich denke, da sind noch andere Bedingungen zu beachten, die nur Jan kennt.
Gruss Sepp
Dienstzeit & Mehrarbeit

 OP
34MW 1 
35Gesamt 45:15
36  
37MW 2 
38Gesamt 36:30
39  
40MW 3 
41Gesamt 35:00
42  
43MW 4 
44Gesamt 37:00

Formeln der Tabelle
ZelleFormel
P35=(SUMME(F21:INDEX(F:F;28-WOCHENTAG(A21;2)))+(WOCHENTAG(A21;2)>1)*F17)*(WOCHENTAG(A21)>1)
P38=SUMME(INDEX(F:F;29-WOCHENTAG(A21;2)):INDEX(F:F;35-WOCHENTAG(A21;2)))
P41=SUMME(INDEX(F:F;36-WOCHENTAG(A21;2)):INDEX(F:F;42-WOCHENTAG(A21;2)))
P44=SUMME(INDEX(F:F;43-WOCHENTAG(A21;2)):INDEX(F:F;49-WOCHENTAG(A21;2)))


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Anzeige
AW: Dein Formelergebnis in P35 ...
19.02.2016 11:52:56
...
Hallo Sepp,
... stimmt mit dem von Jan ermittelten Wert überein. Dieses hatte ich dagegen bewusst ignoriert, weil es nicht seiner verbalen Vorgabe entsprach, an der ich mich orientiert hatte. Mit meiner Formel kam und kommt da nämlich bezogen auf die Beispieldatei stets nur 35h als Ergebnis heraus.
Deine hier zuletzt dargestellten Ergebnisse für MW2 und MW4 könnten verwirren. Diese hast Du bestimmt für von Dir veränderte Datenwerte in Spalte F ermittelt? Denn auch ermittele ich als Ergebnis ebenfalls immer nur 7*5=35h
Im Zusammenhang mit Deiner Aussage zur 5.MW müsste mE auch die Ermittlung der 1. MW hinterfragt werden.
Wie auch immer, wie ich bereits heute Morgen geschrieben hatte, halte ich eine derartige "Wochen"-Auswertung für zumindest ungewöhnlich. Eine eindeutige Regelung wäre dagegen, wie von mir bereits auch geschrieben, wenn die Auswertung immer Kalenderwochen zugeordnet wird.
Aber da ist Jan gefragt.
Gruß Werner
.. , - ...

Anzeige
veränderte Datenwerte in Spalte F
19.02.2016 13:28:02
Josef
Hallo Werner
Ja die Ergebnisse basieren auf veränderte Datenwerte in Spalte F
Ich habe diese Werte etwas variiert, um die Unterschiede im Ergebnis besser zu erkennen.
Leider habe ich das vergessen zu erwähnen.
Ermittlung der 1. MW wie das Jan macht, ergibt schon einen Sinn. So hat er als Ergebnis immer die Stunden einer ganzen Woche. Er wollte eine Formel für (ab) P38 demzufolge stimmen seine Vorgaben schon.
Gruss Sepp

AW: ich hatte stets nur vor Augen ...
19.02.2016 14:28:17
...
Hallo Sepp,
... die Eingangsaussage von Jan: "Suche den ersten Sonntag im Bereich A1 - A31 (z.B. A3) und summiere die 7 folgenden Werte der Spalte B (also summiere die Arbeitsstunden in Zelle b für die folgenden 7 Zeilen nach A3)" die sich jedoch noch nicht auf die dann eingestellte Datei bezog.
Die Formel von Jan in der Datei hatte ich mir deswegen nicht weiter angeschaut, weil ich davon ausging, dass diese ein ihn nicht zufriedenstellendes Ergebnis liefert.
Aber wie bereits geschrieben, Jan sollte müsste sich schon dazu erst äußern.
Gruß Werner
.. , - ...

mit INDIREKT verständlicher
18.02.2016 14:17:59
WF
Hi,
folgende Arrayformel:
{=SUMME(INDIREKT("F"&VERGLEICH(2;WOCHENTAG(A1:A51);0)&":F"&VERGLEICH(2;WOCHENTAG(A1:A51);0)+6))}
Für die Folgewoche:
7 nach dem 1. Vergleich und 13 nach dem 2. Vergleich addieren
dann +14 und +2o
etc.
WF

AW: sehe ich das etwas anders ...
18.02.2016 15:54:09
...
Hallo WF,
... das ist einerseits sicher teils subjektiv. Andererseits ist objektiv Deine Formel (in der von Dir eingesetzten INDIREKT()-Form) sehr unflexibel, weil beim Einfügen oder Löschen einer Spalte vor Spalte F die Formel angepasst werden muss. Ansonsten Du nutze gleich die Z1S1-Schreibweise dieser Funktion.
Auch bedarf meine Formel keines Matrixformelabschlusses wie die INDIREKT()-Formel und ist deshalb und wegen der Volatilität von INDIREKT() auch ein Tick günstiger.
Anstelle REST() in meiner Formel hätte ich auch WOCHENTAG() nutzen können, wem das verständlicher ist.
Aber jeder kann natürlich die Formel so konstruieren und nutzen, wie er es gewohnt ist. Ich nutze eben AGGREGAT().
Gruß Werner
.. , - ...

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige