Wieder einmal Excel

[Ger_17]

Hallo Miteinander!

Folgendes Problem bzw. Frage:
Ich soll in Excel jede Woche einen neuen Dienstplan für ca. 40 Mitarbeiter und ca. 100 Kunden erstellen. Logischerweise arbeiten nicht immer alle 40 Mitarbeiter, da sie krank, auf Urlaub oder Zeitausgleich sind. Auch die Kunden wollen/benötigen von Zeit zu Zeit keinen Einsatz, da sie auf Urlaub, Krankenhaus, ... sind. Deshalb verschiebe ich im Dienstplan die Kunden großteils per Drag n Drop, teilweise lösche ich sie aber auch heraus und gebe sie per hand neu ein. Deshalb kann es vorkommen, dass ich vergesse einen Kunden einzugeben. Ich habe aus diesem grund mit hilfe von formeln und meinen bescheidenen excel kenntnissen eine überprüfung geschrieben, die ohne probleme funktioniert, allerdings nur wenn man den dienstplan nicht umgestaltet! Tausch man die kunden unter den mitarbeitern aus (per Drag n Drop), entstehen bezugsfehler bzw. nach einiger zeit wird die ganze überprüfungs-formel total verändert unbd dadurch fehlerhaft. Ich suche deshalb nach einer möglichkeit eine formel so zu schreiben, dass egal was innerhalb des bereiches auf den sich die formel bezieht, geschieht immer nur dieser bereich in der formel bleibt und nicht verändert wird (ohne logische mitdenken von excel).

Ein Bsp. Mit der angehängten Beispieldatei:
Wenn man beim „Mitarbeiter 1“ den „Kunden E“ um 1 zeile nach unten schieb, dann hat man rechts einen Bezugsfehler, der sich auch auf das 2te excel-sheet, die kontrolle, auswirkt! Wenn man sich weiter spielt und die kunden unter den verschiedenen mitarbeitern austausch verändert sich die kontrollformel und wird fehlerhaft, selbst wenn man die bezugsfehler korrigiert!

Mit F4, also dem sperren der Zellen mittels $-Zeichen hatte ich keinen Erfolg!
Wenn jemand eine andere, bessere Möglichkeit/Idee hat den dienstplan zu überprüfen bitte einfach posten!!

[Ger_17]

ich kann mir nicht vorstellen, dass keiner von euch weiß wie man dieses problem lösen kann!!

[jak]

Bei der Beschreibung kann ich mir das schon Vorstellen ...

Also kurzgefaßt:

Du hast Kunden und Mitarbeiter und willst die Summe aller Stunden pro Kunde?

Ich würde das mit VBA machen:

'Function lookForName
'Determines if a customer's name is already found in the
'second (control) sheet; returns the row number if it is found
'else the negative number of the last row
'The Function terminates if it finds two empty lines
Function lookForName(cName As String) As Integer
  Dim colNum, rowNum As Integer
  Dim custName As String
  colNum = 1
  rowNum = 1
  Do
    custName = Worksheets("Kontrolle").Cells(rowNum, colNum).Value
    If (custName = cName) Then
      lookForName = rowNum
      Exit Function
    End If
    rowNum = rowNum + 1
    'the next line is to be sure that the function only terminates after
    'two empty lines
    If (custName = "") Then custName = Worksheets("Kontrolle").Cells(rowNum, colNum).Value
  Loop Until custName = ""
  lookForName = (rowNum - 1) * -1
End Function


Sub Makro1()
  Dim name, temp As String 'name of the customer, temp
  Dim wTime As Single 'The worktime
  Dim i, j, targetRow As Integer 'count variables
  Dim colStart, rowStart, nextCol, timeCol, checkCol As Integer
  colStart = 3 'start value for colums (=C)
  rowStart = 2 'start value for rows (=2)
  nextCol = 3 'the distance to the next column
  timeCol = -1 'the relative position of the
    'Column where the time is stored
  checkCol = 1 'The relative position of the column
    ' in which to check if a Column counts
    ' 1 or "true" means count, everything else means don't count
  i = colStart
  j = rowStart
  Do
    Do
      name = Worksheets("Dienstplan").Cells(j, i).Value
      temp = Worksheets("Dienstplan").Cells(j, i + checkCol).Value
      If ((temp = "1") Or (temp = "true")) Then
        wTime = Worksheets("Dienstplan").Cells(j, i + timeCol).Value
        targetRow = lookForName((name))
        If (targetRow > 0) Then 'Name already exists
          Dim c As Object
          Set c = Worksheets("Kontrolle").Cells(targetRow, 2)
          c.Value = c.Value + wTime
        Else 'Name not found--> create new cells
          targetRow = targetRow * -1
          Worksheets("Kontrolle").Cells(targetRow, 1).Value = name
          Worksheets("Kontrolle").Cells(targetRow, 2).Value = wTime
        End If
      End If
      j = j + 1
      If (name = "") Then name = Worksheets("Dienstplan").Cells(j, i).Value
    Loop Until (name = "")
    i = i + nextCol
    j = rowStart
    name = Worksheets("Dienstplan").Cells(j, i).Value
  Loop Until (name = "")

End Sub

Der Code ist sicher nicht die effizienteste Art es zu lösen, aber es ist zumindest mal eine Lösung

Jak

[Ger_17]

@jak
is die angabe wirklich so schwer verständlich??

kurz gesagt ich ich hab kunden und mitarbeiter und will die anzahl der einsätze pro kunde pro tag

also:
Kunde A: Mo 3 Einsätze, Dienstag 2 Einsätze, Mittwoch, ...
Kunde B: Mo 0 Einsätze, Dienstag 3 Einsätze, ...

also muss man die suchanfrage auf einen bestimmten bereich (den wochentag) eingrenzen können

vba check ich überhaup nicht, ich bräuchte dann noch ne kurze erklärung wie ich den code in excel einfügen soll!

THX

[jak]

In deinem Beispielfile kommen keine Wochentage vor, daher fehlt das auch im sourcecode.

VBA ist die Sprache in der man Makros schreibt.
Erstellen kann man ein Makro indem man in Excel Alt-F11 drückt (dann kommt der VBA-Editor, im linken oberen Fenster auf die aktuelle Datei rechtsklickt und "einfügen"-"Modul" auswählt.
Dort kann man dann den Quelltext hineinkopieren. Ausführen kann man ihn dann in Excel über Extras-Makro-Makro.

Wenn du ein aktualisiertes Beispiel file postest (mit Wochentagen) kann ich den Quelltext überarbeiten.

Jak

[Ger_17]

ich hab die wochentage absichtlich nicht ins beispielfile getan, weil ich ned erwartet habe, dass mir jemand die komplette überprüfung neu schreibt!

ich wollte eigentlich nur wissen wie man fixe bezüge auf eine zelle macht. mit fixen bezug habe ich gemeint, dass wenn ich die zelle (zb. a5) auf die der bezug steht, verschiebe (zb. nach b10) der bezug immer noch auf a5 steht und nicht durch "mitdenken von excel" auf b10 geändert wird!

ich finde es echt super von dir jak, dass du dir soviel arbeit wegen mir antust!

ich habe das beispiel file jetzt erweitert!

allerdings sollte ich den quelltext zumindest halbwegs verstehen, da ich über 40 mitarbeiter und über 100 kunden habe und ihn dann entsprechend erweitern müsste!

THX im voraus

[jak]

'Function lookForName
'Determines if a customer's name is already found in the
'second (control) sheet; returns the row number if it is found
'else the negative number of the last row
'The Function terminates if it finds two empty lines
Private Function lookForName(cName As String) As Integer
  Dim colNum, rowNum As Integer
  Dim custName As String
  colNum = 1    'The column where the names are stored
  rowNum = 4    'The row to start in
  Do
    custName = Worksheets("Kontrolle").Cells(rowNum, colNum).Value
    If (custName = cName) Then
      lookForName = rowNum
      Exit Function
    End If
    rowNum = rowNum + 1
    'the next line is to be sure that the function only terminates after
    'two empty lines
    If (custName = "") Then custName = Worksheets("Kontrolle").Cells(rowNum, colNum).Value
  Loop Until custName = ""
  lookForName = (rowNum - 1) * -1
End Function


'Sub CountHoursByCustomer()
'Count the hours for every customer
'This is done in three loops. The first iterates over all weekdays, the second
' one over all employees, the third over all hours.
Sub CountHoursByCustomer()
  Dim name, temp, wday As String 'name of the customer, temp, day of week
  Dim wTime As Single 'The worktime
  Dim i, currentCol As Integer 'count variables
  Dim colStart, rowStart, nextCol, timeCol, targetRow As Integer
  Dim nextDayCol, wdayCol, wdayRow, rowsPerDay, emptyRows As Integer
  'begin of configuration
  colStart = 4  'start value for colums (=D)
  rowStart = 4  'start value for rows (=4)
  nextCol = 3   'the distance to the next column
  timeCol = -1  'the position of the Column where the time is stored
    ' relative to the customers name
  nextControlCol = 4    'Distance to the next column in the check sheet.
  startControlCol = 3   ' The first column in the control sheet (=C)
  wdayCol = 1   'The column in which the weekdays are found (=A)
  rowsPerDay = 12   'The number of rows each day has
  emptyRows = 2 'The number of empty rows between the weekdays
  'end of configuration
  wdayRow = rowStart
  currentControlCol = startControlCol
  'First loop: loop over all weekdays
  Do
  wday = Worksheets("Dienstplan").Cells(wdayRow, wdayCol).Value
  If (wday <> "") Then
    currentCol = colStart
    
    'Second loop: loop over all employees
    Do
      i = wdayRow
      
      'Third Loop: Loop over all hours
      Do
        name = Worksheets("Dienstplan").Cells(i, currentCol).Value
        wTime = Worksheets("Dienstplan").Cells(i, currentCol + timeCol).Value
        If (wTime > 0) Then
          targetRow = lookForName((name))
          If (targetRow > 0) Then 'Name already exists
            Dim c As Object
            Set c = Worksheets("Kontrolle").Cells(targetRow, currentControlCol)
            c.Value = c.Value + wTime
          Else 'Name not found--> create new cells
            targetRow = targetRow * -1
            Worksheets("Kontrolle").Cells(targetRow, 1).Value = name
            Worksheets("Kontrolle").Cells(targetRow, currentControlCol).Value = wTime
          End If
        End If
        i = i + 1
      Loop Until i >= (rowsPerDay + wdayRow)
      
      currentCol = currentCol + nextCol
      i = wdayRow
      name = Worksheets("Dienstplan").Cells(i, currentCol).Value
    Loop Until name = ""
    
  End If
  wdayRow = wdayRow + rowsPerDay + emptyRows
  currentControlCol = currentControlCol + nextControlCol
  Loop Until wday = ""
  
End Sub

Das Programm funktioniert nur, wenn die Tage im selben Abstand sind d.h. jeder Tag ist 12 Zeilen lang, danach kommen zwei Leerzeilen (ist einstellbar indem du die Werte unter rowsPerDay und emptyRows änderst).

Sollte funktionieren, wenn was nicht geht oder du eine genauere Erklärung brauchst einfach posten.

<edit>
Das "Mitdenken" von Excel kann man vielleicht einfach verhindern indem man die Zellen schützt.

Btw.: Wielange hast du eigentlich für diese Überprüfung gebraucht? Die WENN Konstrukte sehen ziemlich kompliziert aus
</edit>

Jak

[Ger_17]

@jak
das mitdenken kann man leider auch nicht durch schützen der zelle verhindern, da excel ab dem zeitpunkt in den man die zelle schützt das ergebnis der formel als zahl einträgt.

die "WENN Konstrukte", wie du sie so schön nennst, sehen nicht nur kompliziert aus, bei der anzahl von mitarbeitern die ich habe, ist die abfrage für 1 Kunden pro Tag ca eine A4 seite lang!!!!Wie lange das gedauert hat ist dann eh fast offensichtlich

aber viel mehr zeit habe ich in reparaturarbeiten der formeln investiert, da nach jeder dienstplanänderung irgendwas nicht gepasst hat!

ich sag erstmal danke für die viele mühe!!!!!

ob deine überprüfung funktioniert kann ich dir aber erst im laufe der nächsten oder vielleich sogar erst übernächsten woche sagen, da ich derzeit im krankenstand bin und keinen dienstplan zu hause habe!