MS-Office-Forum

Zurück   MS-Office-Forum > Microsoft Office > Microsoft Excel
Registrieren Forum Hilfe Alle Foren als gelesen markieren

Banner und Co.

Antworten
Ads
Themen-Optionen Ansicht
Alt 24.08.2017, 13:43   #1
motiongroup
Neuer Benutzer
Neuer Benutzer
Standard Excel2010 - Datumsauswertung von Zellen mit Datumsbereichen

Hallo, ich such mal wieder eine für mich zu knifflige Formel für eine Datumsauswertung.

Ich habe von externer Quelle eine Tabelle bekommen die in einer Zelle Datumsbereiche darstellt die ich auswerten muss um eine spezielle Statistik aufzubereiten.
Problem an der Geschichte ist das die Bereiche in einer Zelle ausgeführt sind und ich nicht dahinter komme wie ich die Urlaubstage summieren kann.

Ich habe die Tabelle unten angefügt

eventuell kann mir einer dabei helfen..

ich suche die Summe der Tage an der Person X in den Jahren 2005 bis 20xx an einem gewissen Tag im Urlaub war.
Angehängte Dateien
Dateityp: xlsx Datumsbereich.xlsx (11,6 KB, 18x aufgerufen)
motiongroup ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 24.08.2017, 13:54   #2
Jonas0806
MOF User
MOF User
Standard

Hallo,

das verstehe ich nicht ganz. Bitte trage in deiner Datei mal dein gewünschtes Ergebnis händisch ein.

__________________

Gruß Jonas
Jonas0806 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 24.08.2017, 14:07   #3
motiongroup
Threadstarter Threadstarter
Neuer Benutzer
Neuer Benutzer
Standard

in der Spalte Bx sollte für jeden Tag Spalte Ax der die Summe der konsumierten Tage aller Jahre stehen. Als Beispiel der 1.Juni wurde von Person x von 2005-2017 max. 18x Konsumiert..

Problem ist nur das sie mir die Daten VON BIS in einzelne Zellen gepackt haben.
motiongroup ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 24.08.2017, 15:18   #4
Jonas0806
MOF User
MOF User
Standard

Hallo,

das gibt ja ein riesen Matrixformelmonster...das ist es IMHO nicht wert.

Schaffe eine ordentliche Datenbasis, z.B. mit Text in Spalten o.Ä. in welcher Du dann richtige Datumswerte hast. Eine Spalte von, eine Spalte bis - Dann kann man auch mit <= und >= arbeiten.

__________________

Gruß Jonas
Jonas0806 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 24.08.2017, 15:24   #5
motiongroup
Threadstarter Threadstarter
Neuer Benutzer
Neuer Benutzer
Standard

das wäre die Monsteraufgabe ... 2000 Mitarbeiter jeder auf einem eigenen Blatt..
motiongroup ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 24.08.2017, 18:40   #6
motiongroup
Threadstarter Threadstarter
Neuer Benutzer
Neuer Benutzer
Standard

ich habe die Daten nun mal aufgetrennt von den Datumsbereichen her.

könnte mir nun jemand weiterhelfen ich stehe wirklich an..
Angehängte Dateien
Dateityp: xlsx Datumsbereich.xlsx (13,6 KB, 14x aufgerufen)
motiongroup ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 25.08.2017, 06:38   #7
Jonas0806
MOF User
MOF User
Standard

Guten Morgen,

so hatte ich das nicht gemeint. Hast Du für jeden deiner 2.000 MA so eine Datei?

__________________

Gruß Jonas
Jonas0806 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 25.08.2017, 07:45   #8
Fennek11
MOF Profi
MOF Profi
Standard

Hallo,

nachdem ich es dreimal gelesen und die Bsp-Dateien angesehen habe:

- mit VBA sollte es lösbar sein
- die Datenstruktur für die MA müßte bekannte sein: jeder MA ein sheet oder?
- vor dem koden muss die Datenstruktur bestätigt sein
- der Ausfwand ist erheblich

Erste Idee: die Zeiträume auflösen in eine Zeile pro MA, Tag und Monat, damit später eine Pivot-Tabelle möglich ist.

Die Kommunen in D sind oft etwas "klamm" und das dürfte in Austria nicht anders sein, aber falls es nicht gelingt, sehr effiziente Ansätze zu finden, dürfte der Aufwand zu groß für ein Forum werden. Dann bliebe nur der Hinweis auf Dienstleister.

mfg
Fennek11 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 25.08.2017, 08:04   #9
ebs17
MOF Guru
MOF Guru
Standard

Nicht die Auswertung ist das Problem, sondern die vorgelegte "Struktur" der Ausgangsdaten.
Code:

Mitarbeiter  Zeitraumbeginn  Zeitraumende
Susi         12.06.2016      28.06.2016
Susi         02.02.2017      04.03.2017
Egon         23.03.2017      15.04.2017
Mit einer solchen einfachen Liste würde man unter Hinzuziehung einer Kalendertabelle, die fortlaufend alle Tage des benötigten Zeitraumes enthält, nur eine einzige Anweisung benötigen:
Code:

SELECT 
   U.Mitarbeiter, 
   U.TagZahl, 
   U.MonatsZahl, 
   COUNT(*) AS AnzahlTage 
FROM 
   (
      SELECT 
         M.Mitarbeiter, 
         K.Kalendertag, 
         K.TagZahl, 
         K.MonatsZahl 
      FROM 
         Mitarbeitertabelle AS M, 
         Kalendertabelle AS K 
      WHERE 
         K.Kalendertag BETWEEN M.Zeitraumbeginn 
            AND 
         M.Zeitraumende 
   ) AS U 
GROUP BY 
   U.Mitarbeiter, 
   U.TagZahl, 
   U.MonatsZahl
Das ist eine SQL-Anweisung. Mit einer Pivottabelle könnte man vmtl. Ähnliches tun.

Die Aufgabe sollte also vorwiegend darin bestehen, den Datensalat in eine verwendungsfähige Struktur zu überführen. Bei bekannten Strukturen könnte man das über VBA erledigen.

__________________

Ein freundliches Glück Auf!

Eberhard

Abfrageperformance ist kein Geheimnis
SQL ist leicht: {0}:{1}:{2}:{3}:{4}:{5}:{6}:{7}:{8}:{9}:{10}:{11}:{12} <= geklammerte Zahlen sind Einzelthemen
Dein Dankeschön: DBWiki => Spende

Geändert von ebs17 (25.08.2017 um 08:18 Uhr).
ebs17 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 25.08.2017, 08:20   #10
motiongroup
Threadstarter Threadstarter
Neuer Benutzer
Neuer Benutzer
Standard

Ich schildere euch den Ablauf wie die Blätter entstehen

Ausgelesen wird das ganze aus einer HTML aus unserem Intranet
Diese Seite wird via PHP generiert aus den Daten einer SQL Datenbank auf die ich KEINEN Zugriff habe sonst wäre es kein Problem.
Darf ich nicht, gut soll so sein..
Aus der HTML wird wie gesagt der Inhalt extrahiert und als Blatt gespeichert.

Aus 2000 Mitarbeitern hole ich mir genau 15 für meine Abteilung die natürlich im laufe der Zeit auch wechseln können.

Das Blatt hat genau den obigen Inhalt mit dem ewig gleichen Aufbau aus den extrahierten Daten.

Ziel der ganzen Angelegenheit ist es über den Verlauf der Haupturlaubszeit von 1.6 - 16.9 eine Statistik der Mitarbeiter zu erstellen um etwaige Ungerechtigkeiten in der Planung auszuschließen.
Sprich ich möchte die Häufigkeit der letzten Jahre seit 2005 bis X der einzelnen Urlaubstage der Mitarbeiter vergleichen können um Mitarbeiter X sagen zu können, Kamerad du bist in der Statistik im Bspw. August von bis am Häufigsten im Urlaub gewesen, jetzt sind einmal die anderen dran. Transparent als Aushang damit es keine Diskussionen gibt..
Das Problem "Silvester-, Energie-, Oster- und Weihnachtsferien" habe ich ja Dank eurer Hilfe in einem anderen Fred sensationell umgesetzt und die Leute sind davon begeistert.

Was bleibt, ich habe 15 Blätter mit Blattnamen Mitarbeiter die mir aus den 2000 übrig bleiben und diese liegen wie im ersten XLSX vor..


Wenn sichs nicht über Funktionen erledigen lässt muss ich es so oder so mit VBA in Angriff nehmen..

Das Ergebnis sollte danach in einem separaten Blatt jeden einzelnen Mitarbeiter nach ID untereinander ausgeben werden und farblich aufbereitet werden.

Zitat:

Die Kommunen in D sind oft etwas "klamm" und das dürfte in Austria nicht anders sein, aber falls es nicht gelingt, sehr effiziente Ansätze zu finden, dürfte der Aufwand zu groß für ein Forum werden. Dann bliebe nur der Hinweis auf Dienstleister.

Ein wahres Wort gelassen ausgesprochen
Angehängte Dateien
Dateityp: xlsx Kopie von Datumsbereich.xlsx (10,3 KB, 4x aufgerufen)

Geändert von motiongroup (25.08.2017 um 08:29 Uhr).
motiongroup ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 25.08.2017, 08:35   #11
Jonas0806
MOF User
MOF User
Standard

Hallo,

Zitat: von motiongroup Beitrag anzeigen

Ausgelesen wird das ganze aus einer HTML aus unserem Intranet

Wäre dann vielleicht Power Query eine Alternative? Nur mal grundsätzlich, dann könnte man schon den Import der Daten aufbereiten. Dazu müsste dann natürlich die HTML-Datei bekannt sein.

Das ist nur eine Idee, ob das letztendlich dann mit PQ auch lösbar ist, muss man sich im Detail anschauen.

__________________

Gruß Jonas
Jonas0806 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 25.08.2017, 08:39   #12
Fennek11
MOF Profi
MOF Profi
Standard

Hallo,

ein erster Ansatz mit VBA um EINE Zelle in ein datenbankähnliche Struktur zu übertragen:

PHP-Code:

Function iFen(rng As RangeMA As StringJahr As Integer)
Dim WS As Worksheet
Dim Anfang 
As DateEnde As Date
Dim Zeit
    Fall 
InStr(1rng.Value"-")
    
Select Case Fall
        
Case Is 0
            Anfang 
rng.Value
            Call Liste
(MArng.ValueJahr)
        Case Else
            
Zeit Split(rng.Value"-")
            
Anfang Zeit(0)
            
Ende Zeit(1)
            For 
Anfang To Ende
                Call Liste
(MAiJahr)
            
Next i
    End Select
End 
Function
Sub Liste(ByVal MA As StringByVal Tag As DateByVal Jahr As Integer)
Set WS Sheets("Liste"später anderes Workbook
With WS
lr 
= .Cells(Rows.Count"A").End(xlUp).Row 1
        
.Cells(lr"A") = MA
        
.Cells(lr"B") = Tag
        
.Cells(lr"C") = Jahr
End With
End Sub
Sub Start
()
Dim MA As String
Dim Jahr 
As Integer
MA 
"MA1"
Jahr 2017
For 1 To 2
    Call iFen
(Cells(i1), MAJahr)
Next i
End Sub 
Ein Schleife über alle Urlaubszeiten und alle MA muss noch gelegt werden.

mfg

(Die anderen Beiträge habe ich eben erst gesehen und noch nicht berücksichtigt)
Angehängte Dateien
Dateityp: xlsm Wien.xlsm (17,3 KB, 7x aufgerufen)
Fennek11 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 25.08.2017, 13:42   #13
motiongroup
Threadstarter Threadstarter
Neuer Benutzer
Neuer Benutzer
Standard

Danke schon mal für deine Mühe dieses Problem vorm WOE noch anzugehen..

ich habe den Code mal laufen lassen und werde ihn Morgen versuchsweise auf einem Blatt adaptieren und schauen dass ich das über das ganze Blatt ziehen kann..

PowerQuery muss ich mir mal ansehen besser gesagt die Datenbankspezis
Die sind aber morgen nicht im Dienst..

Da die HTML Seite aber keine Datumswerte sondern Symbole auswertet, kann ich mir nicht vorstellen das über PQ ein Krieg zu gewinnen ist.

mal schauen.

Vielen Dank für euere Hilfe und SCHWOE
motiongroup ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 26.08.2017, 00:51   #14
Drogist
MOF Koryphäe
MOF Koryphäe
Standard

Also,
mit Power Query habe ich es eben hinbekommen, aus diesen wirklich "unanständig" gelieferten Daten (1. Beitrag) eine Liste zu erstellen, wo pro Ereignis (Urlaub) der erste und letzte Tag extrahiert und die Dauer berechnet wird. Das ist dann schon einmal eine solide Basis ...

__________________

Internette Grüße
Drogist

(Gut) Gefunden bei Storax: "This isn't a code writing service, you need to do some research and have a go at writing some of your own code."
Ich verwende Excel 2019. Wenn du keine Angaben über deine Version gemacht hast gehe ich davon aus, dass auch du mindestens diese Version hast.

¿¿ Wer schützt mich vor den Datenschützern ??
Drogist ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 26.08.2017, 11:09   #15
motiongroup
Threadstarter Threadstarter
Neuer Benutzer
Neuer Benutzer
Standard

Danke für die Rückmeldung Drogist das mit PQ funktioniert nicht da in der HTML keine Werte sondern ein Grünes Quadrat als Urlaub ausgewertet wird..

andere Frage, ich habe das PRoblem nun soweit aufgedröselt das diese seltsame Formatierung nun aufgedröselt wird und separat abgelegt wird..

Ich habe das XLSX unten angefügt..

Bei der Datumsbereich Abfrage komme ich aber auf ein falsches Ergebnis

Zelle C21 =
Code:

=WENN(UND(A21>=$A$6; A21<=$A$7);1;0)
Blackout ich sehe den Fehler nicht..

Eventuell kann mir jemand auf die Sprünge helfen.
Angehängte Dateien
Dateityp: xlsx Datumsbereich.xlsx (57,7 KB, 7x aufgerufen)
motiongroup ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Ads
Antworten


Aktive Benutzer in diesem Thema: 1 (Registrierte Benutzer: 0, Besucher: 1)
 
Themen-Optionen
Ansicht

Forumregeln
Es ist Ihnen nicht erlaubt, neue Themen zu verfassen.
Es ist Ihnen nicht erlaubt, auf Beiträge zu antworten.
Es ist Ihnen nicht erlaubt, Anhänge anzufügen.
Es ist Ihnen nicht erlaubt, Ihre Beiträge zu bearbeiten.

vB Code ist An.
Smileys sind An.
[IMG] Code ist An.
HTML-Code ist An.
Gehe zu


Alle Zeitangaben in WEZ +1. Es ist jetzt 04:15 Uhr.



Powered by: vBulletin Version 3.6.2 (Deutsch)
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.

Copyright ©2000-2018 MS-Office-Forum. Alle Rechte vorbehalten.
Copyright ©Design: Manuela Kulpa ©Rechte: Günter Kramer
Eine Verwendung der Inhalte in anderen Publikationen, auch auszugsweise,
ist ohne ausdrückliche Zustimmung der Autoren nicht gestattet.