Microsoft Excel

Herbers Excel/VBA-Archiv

Mit formel Anfang und ende bestimmen

Betrifft: Mit formel Anfang und ende bestimmen von: Stephan
Geschrieben am: 09.08.2008 09:19:13

Hallo zusammen,
Wie kan ich mit einer formel den anfang und das ende Festlegen?

Ich habe eine Tabelle mit dem Datum vom 1.8.08-31.8.08, und möchte nur eine woche anzeigen, das heisst in einer zelle "A1" das anfangdatum (4.8.2008) in der "A2" das enddatum (10.8.2008) und "A3" die berechnung (Summe der Tagen).

Besten Dank
Stephan

  

Betrifft: AW: Mit formel Anfang und ende bestimmen von: {Boris}
Geschrieben am: 09.08.2008 09:58:59

Hi Stephan,

Tabelle1

 ABCD
104.08.2008Von01.08.20081
211.08.2008Bis02.08.20082
360Summe03.08.20083
460Alternative04.08.20084
5  05.08.20085
6  06.08.20086
7  07.08.20087
8  08.08.20088
9  09.08.20089
10  10.08.200810
11  11.08.200811
12  12.08.200812
13  13.08.200813
14  14.08.200814
15  15.08.200815
16  16.08.200816
17  17.08.200817
18  18.08.200818
19  19.08.200819

Formeln der Tabelle
ZelleFormel
A3=SUMME(BEREICH.VERSCHIEBEN(D1;TAG(A1)-1;;A2-A1+1))
A4=SUMMEWENN(C:C;">="&A1;D:D)-SUMMEWENN(C:C;">"&A2;D:D)


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

Grüße Boris


  

Betrifft: AW: Mit formel Anfang und ende bestimmen von: Stephan
Geschrieben am: 09.08.2008 15:28:37

Besten Dank für die Antwort,
kan ich A1 & A2 auch automatisch auslesen von der Kolonne C, zum beispiel wen ich die Wochennumer habe?

Wen ich nach wochennummer suche, kan ich den Bereich zusammenzählen (Summe der Woche), kan ich auch das Start und enddatum ausgeben so wie in Zelle A1 & A2?

Besten dank

Stephan


  

Betrifft: Wo stehen denn die KW´s? von: {Boris}
Geschrieben am: 09.08.2008 15:48:21

Grüße Boris


  

Betrifft: AW: Wo stehen denn die KW´s? von: Stephan
Geschrieben am: 09.08.2008 16:10:24

In der Kolonne "E", entschuldige habe es vergessen zu erwähnen.

Danke Stephan


  

Betrifft: Montag einer Kalenderwoche von: {Boris}
Geschrieben am: 09.08.2008 18:13:13

Hi Stephan,

Tabelle1

 ABCD
1Mo, 24.03.2008 KW13
2So, 30.03.2008 Jahr2008

Formeln der Tabelle
ZelleFormel
A1=DATUM(D2;1;7*D1-3-WOCHENTAG(DATUM(D2;;); 3))
A2=A1+6


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

Grüße Boris


  

Betrifft: AW: Montag einer Kalenderwoche von: Stephan
Geschrieben am: 09.08.2008 18:49:59

Super das ist toll.
Jetzt nur noch eine Frage, kan ich den 30.3.2008 auch mit formel berechnen?
Den meine Firma ist etwas Komisch, die berechen die Woche nur bis ende Monat, das heisst wen die Woche geteilt ist (KW27 30.6.2008 - 6.7.2008), wird die Woche KW27 im Juni mit dem 30.6.2008 berechnet, und KW27 im Juli vom 1.7.2008 - 6.7.2008 Berechent.

:-) Danke Stephan


  

Betrifft: AW: Montag einer Kalenderwoche von: Stephan
Geschrieben am: 09.08.2008 21:37:11

Hallo Boris, habe das Tool für das einfügen der Tabellen ausprobiert, sehr gut!!

Hier nochmals meine Frage:
Wie Kan ich das Datum Auslesen aus dem "Arbeitsblatt", so das ich auf dem "Wochen Auslesen" das Datum
in A10 - B10 und die KW in C1 automatisch suchen und transferieren kann?
Problem ist auch KW5 die ja 28-31.1.08 noch im Januar gerechnet wird und 1-3.02.08 im Februar.
Die Rechnung im G ist OK. (die formel ist gemacht mit "=summenprodukt"), vieleicht geht das auch mit dem Datum?
Besten Dank für deine Hilfe
Stephan

ArbeitsBlatt

 ABCD
1230.12.2007Sonntag52 
1331.12.2007Montag116.00
1401.01.2008Dienstag1 
1502.01.2008Mittwoch1 
1603.01.2008Donnerstag1 
1704.01.2008Freitag113.00
1805.01.2008Samstag1 
1906.01.2008Sonntag1 
2007.01.2008Montag27.50
2108.01.2008Dienstag213.00
2209.01.2008Mittwoch26.00
2310.01.2008Donnerstag27.50
2411.01.2008Freitag27.50
2512.01.2008Samstag2 
2613.01.2008Sonntag2 
2714.01.2008Montag3 
2815.01.2008Dienstag313.00
2916.01.2008Mittwoch3 
3017.01.2008Donnerstag3 
3118.01.2008Freitag37.50
3219.01.2008Samstag3 
3320.01.2008Sonntag3 
3421.01.2008Montag4 
3522.01.2008Dienstag4 
3623.01.2008Mittwoch4 
3724.01.2008Donnerstag4 
3825.01.2008Freitag4 
3926.01.2008Samstag4 
4027.01.2008Sonntag4 
4128.01.2008Montag5 
4229.01.2008Dienstag5 
4330.01.2008Mittwoch5 
4431.01.2008Donnerstag5 
4501.02.2008Freitag5 
4602.02.2008Samstag5 
4703.02.2008Sonntag5 
4804.02.2008Montag6 
4905.02.2008Dienstag6 
5006.02.2008Mittwoch6 


Wochen Auslesen

 ABCFG
1001.01.200806.01.20081 9.00
1107.01.200813.01.20082 35.10
1214.01.200820.01.20083 45.50
1321.01.200827.01.20084 20.00
1428.01.200831.01.20085 40.25
15     
1601.02.200803.02.20085 10.50
1704.02.200810.02.20086 62.00
1811.02.200817.02.20087 51.25
1918.02.200824.02.20088 65.75
2025.02.200829.02.20089 36.25


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4


  

Betrifft: AW: Auswertung Monate / Wochen von: Erich G.
Geschrieben am: 10.08.2008 08:22:46

Hallo Stephan,
schau dir mal diese Mappe an: https://www.herber.de/bbs/user/54477.xls

Darin findest du ein paar Formeln und Pivottabellen zur Auswertung über Monat und Kalenderwoche.
Gebraucht wird immer auch das Jahr. Der 31.12.07 gehört in den Monat 12/2007 und die Woche 01/2008.

Kannst du damit etwas anfangen?

Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort


  

Betrifft: AW: Auswertung Monate / Wochen von: Erich G.
Geschrieben am: 10.08.2008 09:30:17

Hi Stephan,
hier noch eine (auch kürzere) Version: https://www.herber.de/bbs/user/54482.xls

Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort


  

Betrifft: AW: Auswertung Monate / Wochen von: Stephan
Geschrieben am: 10.08.2008 22:09:11

Besten Dank für die Antwort.

Kan ich das auch ohne Pivot Tabelle in die Einzelnen Zellen holen?
Wie Z.b.:

 LMN
2Wo abWo bisWoche
3   
431.12.200731.12.20081
501.01.200806.01.20081
607.01.200813.01.20082
714.01.200820.01.20083
821.01.200827.01.20084
928.01.200830.01.20085
1001.02.200803.02.20085
1104.02.200810.02.20086


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

Danke Stephan


  

Betrifft: AW: Auswertung Monate / Wochen von: Erich G.
Geschrieben am: 10.08.2008 23:58:03

Hi Stephan,
macht viel mehr Arbeit als Pivot, geht aber auch:

 FGHI
6Wo abWo bisWoSumme
7Mo     24.12.2007So     30.12.2007520,01
8Mo     31.12.2007Mo    31.12.200710,10
9Di      01.01.2008So     06.01.200811111,00

Formeln der Tabelle
ZelleFormel
F7=MIN(C:C)
G7=MIN(F7+6-REST(F7-2;7); DATUM(JAHR(F7); MONAT(F7)+1;))
H7=KÜRZEN((F7-DATUM(JAHR(F7+3-REST(F7-2;7)); 1;REST(F7-2;7)-9))/7)
I7=SUMMENPRODUKT($D$3:$D$999*($C$3:$C$999=F7))
F8{=WENN(MAX($C$3:$C$999)=MAX(F$7:F7); "";MIN(WENN($C$3:$C$999>F7;$C$3:$C$999)))}
G8=WENN(F8<>"";MIN(F8+6-REST(F8-2;7); DATUM(JAHR(F8); MONAT(F8)+1;)); "")
H8=WENN(F8<>"";KÜRZEN((F8-DATUM(JAHR(F8+3-REST(F8-2;7)); 1;REST(F8-2;7)-9))/7); "")
I8=WENN(F8<>"";SUMMENPRODUKT($D$3:$D$999*($C$3:$C$999=F8)); "")
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen

Und hier die Mappe dazu: https://www.herber.de/bbs/user/54497.xls

Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort


  

Betrifft: AW: Auswertung Monate / Wochen von: Stephan
Geschrieben am: 12.08.2008 16:04:20

Besten Dank Erich, das war super.
Kanst du mir sagen was die {} bedeuten? für was die sind?
Kanst du mir im Schnellverfahren die formeln erklären? Es sind zahlen drin die ich nicht zuordnen kann.
Ich hätte die formel anpassen wollen das er nur nach wochen sucht und nicht mehr nach wochen und monat.

Also dieses Forum finde ich sehr gut, und die Leute sind Hilfsbereit.

p.s: Danke auch für die Italienischen formeln. (Wen ich die Tabelle in einem Deutschen Excel aufmache funktionieren die Formel noch, oder gib es ein Tool um die Formel umzuwandeln?)

Herzlichen Dnak
Stephan



  

Betrifft: AW: Auswertung Monate / Wochen von: Erich G.
Geschrieben am: 12.08.2008 18:16:53

Hi Stephan,
zu den geschweiften Klammern:
In dem Kasten mit den Formeln in meinem vorigen Beitrag findest du folgenden Hinweis:
Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen


Wenn du auf "Matrix verstehen" klickst, kommst du auf die Seite Matrix verstehen. Schau dir das bitte einmal an.

Zu den Formeln:
So leicht lassen sich die Formeln nicht "im Schnellverfahren" erklären.
Das Tabellenblatt enthält mittlerweile ja etliche Formeln.
Bitte schreib mal genau, welchen Teil welcher Formel du nicht verstehst.

Noch ein Tipp: Wenn du dir Formeln zu Datum/Zeit/Kalenderwoche ansehen möchtest, dann hier:
http://www.excelformeln.de/formeln.html?gruppe=1

Überhaupt findest du bei www.excelformeln.de viele, viele Beispiele für nützliche Matrixformeln.

Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort


 

Beiträge aus den Excel-Beispielen zum Thema "Mit formel Anfang und ende bestimmen"