Herbers Excel-Forum - das Archiv

Bewerten Sie hier bitte das Excel-Portal

Anzahl pro Woche: zu viel verlangt?

Bild

Betrifft: Anzahl pro Woche: zu viel verlangt?
von: Franz W.
Geschrieben am: 13.12.2003 20:41:45
Hallo Fachleute,

ich hab eine Frage, von der ich nicht weiß ob sie überhaupt lösbar ist. Ich habe folgende Tabelle:

- das erste Datum in A15 wird eingetragen, die folgenden und die bedingte Formatierung folgen daraus

- die Anzahl pro Namen wird oben bei den einzelnen Namen zusammengezählt

- die Gesamtanzahl im Zeitraum wird in H3 berechnet

Was ich jetzt noch bräuchte, ist die Gesamtanzahl pro Kalenderwoche, das heißt also die roten Zahlen, die neben jedem Sonntag stehen bzw. beim Ultimo. Gibt es dafür eine (Formel?-)Lösung?

Vielen Dank schon mal im Vouraus und beste Grüße
Franz

 


  
 ABCDEFGHI
1  Barbara10   Gesamt: 
2 Fanny12     
3  Sabrina13   44 
4  Simon9     
5         
6von        
701.12.2003        
8bis        
931.12.2003        
10         
11KW   49        
1249        
132003        
14         
1501.12.2003  Simon  Barbara  Sabrina  Sabrina    
1602.12.2003        
1703.12.2003        
1804.12.2003  Simon  Simon  Fanny     
1905.12.2003        
2006.12.2003        
2107.12.2003       7
2208.12.2003        
2309.12.2003        
2410.12.2003        
2511.12.2003  Simon  Simon  Barbara     
2612.12.2003  Fanny  Fanny      
2713.12.2003  Sabrina  Sabrina  Simon  Barbara  Barbara   
2814.12.2003       10
2915.12.2003        
3016.12.2003  Barbara  Barbara  Barbara     
3117.12.2003  Sabrina  Simon  Fanny  Barbara    
3218.12.2003  Fanny  Fanny  Fanny  Fanny    
3319.12.2003  Sabrina  Sabrina      
3420.12.2003        
3521.12.2003       13
3622.12.2003        
3723.12.2003  Barbara  Fanny  Sabrina  Simon    
3824.12.2003  Barbara  Fanny  Sabrina  Simon    
3925.12.2003        
4026.12.2003        
4127.12.2003        
4228.12.2003       8
4329.12.2003   Fanny  Fanny     
4430.12.2003   Sabrina  Sabrina  Sabrina  Sabrina   
4531.12.2003       6
Formeln der Tabelle
D1 : =WENN(ZÄHLENWENN(StdBereich;" Barbara")=0;"";ZÄHLENWENN(StdBereich;" Barbara"))
D2 : =WENN(ZÄHLENWENN(StdBereich;" Fanny")=0;"";ZÄHLENWENN(StdBereich;" Fanny"))
D3 : =WENN(ZÄHLENWENN(StdBereich;" Sabrina")=0;"";ZÄHLENWENN(StdBereich;" Sabrina"))
H3 : =SUMME(D1:D14;G1:G14)
D4 : =WENN(ZÄHLENWENN(StdBereich;" Simon")=0;"";ZÄHLENWENN(StdBereich;" Simon"))
A7 : =A15
A9 : =DATUM(JAHR(A18);MONAT(A18)+1;0)
A11 : ="KW " & (KÜRZEN((A15-WOCHENTAG(A15;2)-DATUM(JAHR(A15+4-WOCHENTAG(A15;2));1;-10))/7))
A12 : =KÜRZEN((A15-DATUM(JAHR(A15+4-WOCHENTAG(A15;2));1;-9+WOCHENTAG(A15;3)))/7)
A13 : =JAHR(A15)+(A12=1)*(MONAT(A15)=12)
A16 : =A15+1
A17 : =A16+1
A18 : =A17+1
A19 : =A18+1
A20 : =A19+1
A21 : =A20+1
A22 : =A21+1
A23 : =A22+1
A24 : =A23+1
A25 : =A24+1
A26 : =A25+1
A27 : =A26+1
A28 : =A27+1
A29 : =A28+1
A30 : =A29+1
A31 : =A30+1
A32 : =A31+1
A33 : =A32+1
A34 : =A33+1
A35 : =A34+1
A36 : =A35+1
A37 : =A36+1
A38 : =A37+1
A39 : =WENN(MONAT(A38+1)=MONAT(A38);A38+1;"")
A40 : =WENN(MONAT(A39+1)=MONAT(A39);A39+1;"")
A41 : =WENN(MONAT(A40+1)=MONAT(A40);A40+1;"")
A42 : =WENN(MONAT(A41+1)=MONAT(A41);A41+1;"")
A43 : =WENN(MONAT(A42+1)=MONAT(A42);A42+1;"")
A44 : =WENN(MONAT(A43+1)=MONAT(A43);A43+1;"")
A45 : =WENN(MONAT(A44+1)=MONAT(A44);A44+1;"")
 
Excel Tabellen einfach im Web darstellen   Excel Jeanie HTML  2.0    Download  
Bild

Betrifft: AW: Anzahl pro Woche: zu viel verlangt?
von: Josef Ehrensberger
Geschrieben am: 13.12.2003 21:16:55
Hallo Franz,

schau mal hier.

http://www.excelformeln.de/formeln.html?welcher=358

Gruß Sepp
Bild

Betrifft: AW: Anzahl pro Woche: zu viel verlangt?
von: Franz W.
Geschrieben am: 13.12.2003 21:55:38
Hallo Sepp,

vielen Dank für den Tipp, klingt ja vielversprechend. Allerdings hänge ich leider!! Habe jetzt mal in "I21" eingetragen:

{=SUMME(WENN(A15:A45<>"";(KÜRZEN((A15:A45-WOCHENTAG(A15:A45;2)-DATUM(JAHR(A15:A45+4-WOCHENTAG(A15:A45;2));1;-10))/7)=A12)*(JAHR(A15:A45)=A13)*B15:H45))}

Und bekomme " #WERT! ". Kannst Du da den Fehler finden? Ich suche auch weiter...

Vielen Dank und Grüße
Franz
Bild

Betrifft: AW: Anzahl pro Woche: zu viel verlangt?
von: Josef Ehrensberger
Geschrieben am: 13.12.2003 22:04:29
Hallo Franz,

hab deine Tabelle nicht nachgebaut, aber
eine vermutung!

Array-Formel > Du musst die geschweiften Klammern
mit STRG+SHIFT+ENTER eingeben! (Cursor in der Bearbeitungsleiste!)

Gruß Sepp
Bild

Betrifft: AW: Anzahl pro Woche: zu viel verlangt?
von: Franz W.
Geschrieben am: 13.12.2003 22:08:04
Hallo Sepp,

das hab ich schon. Das ist aber leider auch so ziemlich das einzige, was ich von Array-Formeln verstehe, ansonsten hab ich da so meine Schwierigkeiten damit. Darum hab ich halt versucht, die Vorgaben aus dem Link von Dir anzupassen. Und weiß jetzt leider nicht wo ich suchen soll.

Grüße
Franz
Bild

Betrifft: lade das teil mal hoch! o.T
von: Josef Ehrensberger
Geschrieben am: 13.12.2003 22:10:55
/
Bild

Betrifft: AW: lade das teil mal hoch!
von: Franz W.
Geschrieben am: 13.12.2003 22:17:02
Hallo Sepp,

ganz vielen Dank für die Mühe:

Die Datei https://www.herber.de/bbs/user/2450.xls wurde aus Datenschutzgründen gelöscht


Beste Grüße
Franz
Bild

Betrifft: und zurück!
von: Josef Ehrensberger
Geschrieben am: 13.12.2003 23:13:31
Hallo Franz,

schau mal ob es das ist was Du willst?

https://www.herber.de/bbs/user/2451.xls

Gruß Sepp
Bild

Betrifft: Ja, aber: zu viel verlangt?
von: Franz W.
Geschrieben am: 13.12.2003 23:25:48
Hallo Sepp,

toll, faszinierend. Da muss ich wohl noch ein bisschen üben, bis ich auf sowas kommen kann! Tiefer Bückling :-))) !!!

Aber jetzt komme ich zu meinem urprünglichen Betreff zurück, weil ich nicht weiß, ob und wie das jetzt machbar ist. Da monatlich in "A15" ein neues Datum eingegeben wird, ist der Sonntag immer in einer anderen Zeile. Die Formel lässt sich ja von I15 bis I45 runterkopieren, aber der WErt sollte natürlich nur am Sonntag bzw. am Ultimo stehen und nicht an allen Tagen. Wo in dieser Formel müsste jetzt ncoh die Abfrage nach dem Wochentag = Sonntag bzw. dem Letzten des Monats rein? Ist das jetzt noch ein Riesenaufwand oder für einen wie Dich nur noc ein Klacks???

Beste Grüße
Franz

P.S.: Ich studier inzwischen Deine Formel :-))))))))
Bild

Betrifft: Oder halt!! Bed. Formatierung...
von: Franz W.
Geschrieben am: 13.12.2003 23:28:56
Hallo Sepp,

kann die nicht erwünschten Werte ja über die bedingte Foramtierung ausblenden.

Ganz vielen Dank für Deine Hilfe und eine gute Nacht
Franz
Bild

Betrifft: AW: und zurück!
von: Franz W.
Geschrieben am: 14.12.2003 00:04:35
Hallo Sepp,

jetzt bin ich doch noch über was gestolpert: Deine tolle Formel klappt nur bei den Monaten mit 31 Tagen. Lässt sich das noch anpassen? Oder kann ich in A43 - A45 (also 29. - 31. d. M.) was anderes reinschreiben, damit Deine Formel im Februar und den Monaten mit 30 Tagen nicht gestört ist???

Viele hoffnungsfrohe Grüße
Franz
Bild

Betrifft: AW: und zurück!
von: Josef Ehrensberger
Geschrieben am: 14.12.2003 02:21:14
Hallo Franz,

ich glaube es sieht jetzt schon recht gut aus.

https://www.herber.de/bbs/user/2453.xls

An der Formel im Datumsbereich arbeite ich noch.

Gruß Sepp
Bild

Betrifft: Mensch, Du bist ja phänomenal
von: Franz W.
Geschrieben am: 14.12.2003 08:20:19
Guten Morgen Sepp,

werkelst da die ganze Nacht (sorry, dass ich nicht mehr durchgehalten hab - aber sonst hätt ich heut früh nicht mit meinen Kids aufstehen können :-)))) und kriegst auch noch tolle Lösungen!! Wie kommt man auf sowas?!? Es funktioniert bestens, ganz vielen Dank dafür!!! Und dass im Februar am Ende der Datumsspalte jetzt 4 mal 28.2. steht, das krieg ich selber weg.

Noch mal besten Dank und viele Grüße
Franz
Bild

Betrifft: AW: Mensch, Du bist ja phänomenal
von: Josef Ehrensberger
Geschrieben am: 14.12.2003 08:37:32
Guten Morgen Franz,

hab das gleiche "Schiksal" wie Du!

Unser Sebastian (19 Monate) wollte auch nicht mehr Schlafen.

Es freut mich das dir meine lösung gefällt.

Bis dann - Sepp
Bild

Betrifft: Und das ohne Schlaf, Du Ärmster :----)) ...
von: Franz W
Geschrieben am: 14.12.2003 08:45:01
Hallo Sepp,

ja gefällt mir bestens, die zusätzliche "bed. Formatierung" in der Datumsspalte mach ich per VBA im Change-Ereignis nach der Datums-Änderung in A15, das geht schon. Bin rundum glücklich!!!

Schönen Sonntag Dir (und Deinem Kleinen ;-))) und beste Grüße
Franz
Bild

Betrifft: AW: Anzahl pro Woche: zu viel verlangt?
von: Reinhard
Geschrieben am: 13.12.2003 23:26:49
Hallo Franz,
über Namen definieren habe ich die 5 Bereiche B15:H21, B22:H28, usw. als Woche1, Woche2. Woche3, usw. benannt.
Dann folgende Formel in I21:
=ZEILEN(Woche1)*SPALTEN(Woche1)-ANZAHLLEEREZELLEN(Woche1)
analog dazu für die anderen Wochen in I28, I35, usw.
anbei deine dementsprechend Datei: https://www.herber.de/bbs/user/2452.xls
Gruß
Reinhard
Bild

Betrifft: AW: Anzahl pro Woche: zu viel verlangt?
von: Franz W.
Geschrieben am: 13.12.2003 23:32:46
Hallo Reinhard,

vielen Dank für Deine Hilfe. Ich hab Deinen Vorschlag noch bis zu Ende durchgedacht. Aber ich weiß nicht, ob das mit den fest benannten Bereichen hinhaut, weil ja mit jedem neuen Monat die Sonntage woanders stehen, die Wochen also nicht immer über dieselben Bereiche gehen. Werde es mir aber noch genauer anschauen.

Vielen Dank und Grüße
Franz
Bild

Betrifft: AW: Anzahl pro Woche: zu viel verlangt?
von: udo
Geschrieben am: 13.12.2003 21:22:50
Hi Franz,

ganz schnell noch , bin gleich weg ...

wenn ich dich richtig verstanden habe, dann willst du alle Zahlen zusammenzählen die immer hinter einem Sonntag stehen in Spalte I oder ???

dann nimm doch eine Zählenwenn Formel , und als Bedingung " Wochentag(1) " ist immer ein Sonntag.

z.B. ZählenWenn(I15:I45;Wochentag(1))

müsste doch eigentl. klappen , ???

Gruß udo


........................................................................................................................................................................................................................................................................................

Hier mal noch zusätzliche , brauchbare Beispiele von versch. Helfern aus dem Forum , sowie mir selbst , ... viel Erfolg allerseits , Gruß udo

Jahreskalender mit Markierfunktionen ( Für Studenten bestens geeignet ) , ( v. udo )
https://www.herber.de/bbs/user/2251.zip ( 900 Kb ) ****

Zahlen vertauschen per Zufall ( udo ) **
https://www.herber.de/bbs/user/2036.xls ( 60 Kb )

Markierhilfe ( udo ) ***
https://www.herber.de/bbs/user/2035.xls ( 48 Kb )
https://www.herber.de/bbs/user/2117.zip ( 170 Kb )

Arbeitszeiterfassung mit Jahreskalender & Feiertagsübersicht ... Update kommt demnächst ( v. udo , EXCEL XP , 2000 ) ***
https://www.herber.de/bbs/user/1834.zip ( 347 Kb )

Datumwert abgleichen / suchen und entspr. Zeilen kopieren in andere Tabellen ( v.udo ) **
https://www.herber.de/bbs/user/1920.xls ( 70 Kb )

intelligente Gültigkeit - erweitert sich autom. um neue Einträge in der Gültigkeitsvorgabe ( v. Boris ) *****
https://www.herber.de/bbs/user/1953.xls ( 40 Kb )

verschiedenen Beispiele zusammen , mit allen mögl. Formeln ( v. Max ) *****
https://www.herber.de/bbs/user/1946.xls ( 300 Kb )

Suchen X Einträgen, je nach Eingabe v. Buchstaben ( v. Heinz ) ****

Die Datei https://www.herber.de/bbs/user/1959.xls wurde aus Datenschutzgründen gelöscht

( 44 Kb )


Hajos Excelseite mit tollen Beispielen mit & ohne VBA's ( v. Hajo_Zi ) *****
http://home.media-n.de/ziplies/


Bewertungen meinerseits ( ***** sehr z. empfh. Tipp , **** sehr gut , *** gut , ** unspektakulär aber brauchbar ... )

( Anregeungen oder Tipps speziell zu meinen ( udo ) Beispielen gerne an udo-de@web.de )

........................................................................................................................................................................................................................................................................................

Bild
Excel-Beispiele zum Thema " Anzahl pro Woche: zu viel verlangt?"
Seitenanzahl eintragen Funktion ANZAHL2 ohne ausgeblendete Spalten
Anzahl der über ParamArray festgelegten Parameter Anzahl eines bestimmten Wochentages im Monat
Anzahl der einzufügenden Zeilen abfragen Bestimmte Anzahl von Zeichen zulassen
Seitennummer und Seitenanzahl per VBA in Kopfzeile Nur AJM in TextBox akzeptieren und Anzahl zählen
Anzahl von CheckBoxes in einer UserForm ermitteln Anzahl der gelb hinterlegten Zellen ermitteln