Office: (Office 2010) Hilfe :-) Datensätze zählen und gruppieren

Hallo

ich habe Datensätze im folgenden Format dd.mm.yyyy hh:mm

Nun muss ich diese zusammenfassen mit folgender Anforderung :


wieviele Datensätze waren Montags 7:30 - 8:30 usw. bis 17:30 das gleiche für Dienstags usw...

eine Beispieldatei habe ich angehängt.

vielen Dank für schnelle Hilfe :-)

DANKE !

 

Hola,

wenn du Datum und Uhrzeit trennst und die abzufragenden Uhrzeiten in 2 separate Zellen schreibst, ginge das einfach mit Summenprodukt().

Dir ist aber schon klar, das Uhrzeiten zur halben Stunde doppelt gezählt werden würden?

Gruß,
steve1da

 

Hi

Ich bekomme das mit der Formatierung nicht hin also das trennen, stimmt wegen des doppelt Zählen würde ich dann 7:30 - 8:29 und 8:30 - 9:29


Danke für deine Hilfe

 

Hola,

also Datum und Uhrzeit lassen sich wie folgt trennen:

Code:

=GANZZAHL(A2)

sowie

Code:

=REST(A2;1)

Die Zellen dann als Datum bzw. als Uhrzeit formatieren.

Edit: kann man aber vereinfachen!
Schreib in E2 die erste Anfangszeit, in E3 die erste Endzeit, in F2 die zweite Anfangszeit, in F3 die zweite Endzeit usw.

Dann:

Code:

=SUMMENPRODUKT((TEXT(WOCHENTAG($B$2:$B$3718;2);"TTTT")=D5)*(REST($B$2:$B$3718;1)>=E$2)*(REST($B$2:$B$3718;1)<=E$3))

Dazu müssen aber die Leerzeichen hinter den Wochentagsnamen in D5 bis D10 verschwinden.

Gruß,
steve1da

 

Hi

Danke aber mit dem Summenprodukt hängt es auch !

Ganzzahl kannte ich nicht super Tipp !!!

Ich habe es nochmal angehängt und bin für Hilfe dankbar !!!

 

Hola,

warum setzt du nicht alles um was ich geschrieben habe? Die Uhrzeiten in B4 bis K4 musst du in 2 einzelne Zellen aufteilen?

Ich hab dir doch aber auch eine Lösung ohne Trennen der Zellen mitgeteilt.

Warum es mit Summenprodukt hakt, sagst du auch nicht.

Gruß,
steve1da

 

sorrx

Hi

ich komm nicht so recht mit bzw verstehe irgendwas falsch tut mir leid !

Kannst Du nochmal reinschuen !

 

Moinsens!

Ich hab mal einen etwas anderen Ansatz, der auf das unhandliche SummenProdukt() verzichtet:

Wenn in A2 ein beliebiges Datum mit Uhrzeit steht, kann mit

Code:

B2:
=WENN( 
  ODER( REST( A1; 1 ) < ZEIT( 7; 30; 0); REST( A1; 1) >= ZEIT( 17; 30; 0 )); 
  ""; 
  TEXT( A1; "TTT" ) & TEXT( GANZZAHL(( REST( A1; 1 ) - ZEIT( 7; 30; 0 )) / ZEIT( 1; 0; 0 )); "00" )
)

<zeit(7;30;0);rest(a1;1)>Eine Kennung erzeugt werden, die aus dem Wochentag (2 Buchstaben) und dem Intervall (00..09) besteht. Konkret:

Du hast ein Zeitfenster von 7:30 bis 17:30 in 10 Intervallen a 1 Stunde. Die Formel prüft nun, ob die Uhrzeit vor 7:30 oder nach 17:30 liegt. Wenn ja, ist das Ergebnis "" (leer), ansonsten wird 7:30 von der Uhrzeit abgezogen und der Rest durch 1 Stunde geteilt, was dann als ganzzahliges Ergebnis eine Zahl von 0 bis 9 liefert. Dies wird mit "00" 2stellig formatiert und davor der Wochentag auf 2 Buchstaben formatiert gehängt. Das Ergebnis könnte also z.B. "Di03" sein.

Damit hast Du eine Kennung, die Du mit ZählenWenn() hervorragend zusammenfassen kannst. Das hab ich im Beispiel in der Tabelle rechts oben gemacht. In jeder Zelle wird aus der Spalte B das zusammengezählt, was als Kennung die Kombination aus linker und oberer Beschriftung der Tabelle hat. Da die Tabelle oben "Mo" bis "So" und links "0" bis "9" hat, sind alle Kombinationen abgedeckt. Zeile "0" ist dann das Intervall von 7:30..8:29 usw.

Letztendlich ist das eine Pivot-Tabelle, nur daß diese sich immer automatisch mit aktualisiert, wenn die Basis-Daten sich verändern.

Gruß, Michael</zeit(7;30;0);rest(a1;1)>

 

HI VIELEN DANK FÜR DIE HILFE !!!! DAAAAANKE Funktioniert einwandfrei.