WCM Forum - Wieder einmal Excel

WCM Forum (http://www.wcm.at/forum/index.php)

- Software (http://www.wcm.at/forum/forumdisplay.php?f=5)

- - Wieder einmal Excel (http://www.wcm.at/forum/showthread.php?t=160165)

Wieder einmal Excel

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!!

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

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:

Code:



'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

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

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

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

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
:bier:

Code:

'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

@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 :D

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

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 :cool: :cool: und keinen dienstplan zu hause habe!