Utfordring i Excel

[Møllerodden]

utfordringen er at jeg har timeregistrering som registreres på forskjellige maskin nr, og hver person som skriver timer kan ha vært borti flere eller samme maskin i løp av en uke. Jeg skulle hatt en formel eller en vb kode for å summere alle timene som har samme maskinnr. Er det noen som kan hjelpe meg med det ?

Hilsen Frode

[Jonas]

Hvordan er disse timene lagret?

[Møllerodden]

listen består av det som er vist under, vi har 17 maskiner som igjen har forskjellige priser(spesialpriser og normalpriser) dette skiller vi med maskin nummer og maskin tillegg. Jeg vil lage en formel som summerer de timene som har samme maskin nr og maskin tillegg. Problemet mitt er at jeg må ha en formel som samler de timene som har likt maskinnr og maskintillegg slik at jeg får skilt det fra hverandre. Det er også interessant med en kode i xl sin VB bit som gjør jobben.

 

 

 

| Timer Ordinær | Overtid 50% | Overtid 100% | Maskin.nr | MaskinTillegg |

 

 

 

Hilsen Frode |

[aadnk]

Dersom du vil gjøre dette via VBA, kan du jo alltids benytte deg av følgende funksjon:

 

Public Function AddCells(sCondition As String, objRange As Range) As Double

   Dim objCell As Object, sCellName As String, bTemp As Boolean, Tell As Long
   Dim aCondition, aStatement, aCommands, sTemp As String
   
   ' Splitt strengen opp i mindre deler
   aCondition = Split(sCondition, ";")
   
   ' Gå gjennom alle cellene i Range-objektet
   For Each objCell In objRange.Cells
   
       ' Hent cellenavnet (fant dessverre ingen bedre metoder)
       sCellName = Chr(vbKeyA + objCell.Column - 1) & objCell.Row
       
       ' Gå gjennom alle kommandoene for å se hvorvidt noen henspeiler til denne cellen
       For Tell = LBound(aCondition) To UBound(aCondition)
       
           ' Hent denne linjen
           sTemp = aCondition(Tell)
       
           ' Gjør det mulig å unnlate radbetegnelsen eller kolonnebetegnelsen
           sTemp = Replace(sTemp, "%X", Chr(vbKeyA + objCell.Column - 1))
           sTemp = Replace(sTemp, "%Y", objCell.Row)
       
           ' Del opp linjen
           aStatement = Split(sTemp, "=", 2)
       
           ' Hvis kommandoen peker til denne cellen ...
           If Trim$(aStatement(0)) = sCellName Then
           
               ' Denne delen kan faktisk bestå av flere ting
               aCommands = Split(aStatement(1), " ")
           
               ' ... sjekk hva den ber oss gjøre
               Select Case aCommands(0)
               Case "+" ' Adder verdi
               
                   ' Vi skal addere dersom boolean-verdien er sann
                   If bTemp Then
                   
                       ' Legg til verdien
                       AddCells = AddCells + objRange.Worksheet.Range(aCommands(1)).Value
                   
                   End If
               
               Case "&", "&&", "||", "^" ' Boolean
               
                   If Mid(aCommands(1), 1, 1) = "%" Then
                       ' Vi skal kun sammenligne cellen med tekst
                       sTemp = Mid(aCommands(1), 2)
                   Else
                       ' Kommandoen peker til en celle
                       sTemp = objRange.Worksheet.Range(aCommands(1)).Value
                   End If
               
                   Select Case aCommands(0)
                   Case "&": bTemp = CBool(sTemp = objCell.Value)
                   Case "&&": bTemp = bTemp And CBool(sTemp = objCell.Value)
                   Case "||": bTemp = bTemp Or CBool(sTemp = objCell.Value)
                   Case "^": bTemp = bTemp Xor CBool(sTemp = objCell.Value)
                   End Select
               
               End Select
           
           End If

       Next
   
   Next

End Function

 

Når du nå ønsker å få summen av rekke A helt inntil A29 der cellen D og E er like, kan du gjøre som følger:

 

MsgBox AddCells("D%Y=& E%Y;E%Y=+ A%Y", Range("A2", "E29"))

 

Skal du istedenfor finne summen av rekke B, må denne koden benyttes:

 

MsgBox AddCells("D%Y=& E%Y;E%Y=+ B%Y", Range("A2", "E29"))

 

Range delen av kommandoen burde være klinkende klart, mens den første paramenteren kan virke nokså kompleks i førstningen. Men la oss se nærmere på den:

 

I virkeligheten vil strengen faktisk oppfattes som to strenger. Semikolonet er en slags avgrenser som definerer hvor den siste strengen sluttet og den nye begynner. Følgelig har vi to strenger:

• D%Y=& E%Y
  
• E%Y=+ A%Y
  

Funskjonen vil behandle begge strengene overfor ALLE cellene i Range. I disse strengene kan du finne to forskjellige variabler - %X og %Y. Disse står bare for den gjeldende posisjonen i søkeprosessen. Dersom vi eksempelvis har kommet til rad 5, vil D%Y leses som celle D5.

 

Likhetstegnet i mellom har kun som funksjon å fortelle søkefunksjonen i hvilken celle neste del av strengen skal eksekveres. Har man kommet til celle D5, vil D%Y gi utslag.

 

Etter likhetstegnet kommer vi til kommandoen som kjøres, hvilket alltid er etterfulgt av en av disse tegnene: +, &, &&, || eller ^. "+" betyr ganske enkelt at man skal addere cellen som angis ved siden av dersom en variabel, kalt bTemp, er sann. Man kan sette verdien på denne variabelen gjennom de andre tegnene:

• & - Gir sann dersom den gjeldende cellen er lik den angitte cellen ved siden av.
  
• && - Som ovenfor, bare her brukes AND-operatøren på den tidligere verdien.
  
• || - Her benyttes OR-operatøren.
  
• ^ - Her benyttes XOR-operatøren.
  

"&&", "||" og "^" er i bunn og grunn kun nødvendige dersom du skal sjekke flere celler for likhet. De er dermed ikke nødvendige i ditt tilfelle, men jeg la dem til for sikkerhetsskyld.

Endret 2. april 2005 av aadnk

[Møllerodden]

Det var en heftig løsning du kom med, takker for det arbeidet der. Jeg forstår deler av koden din og har prøvd å legge den inn slik som dette:

 

Public Function AddCells(sCondition As String, objRange As Range) As Double

 

Dim objCell As Object, sCellName As String, bTemp As Boolean, Tell As Long

Dim aCondition, aStatement, aCommands, sTemp As String

 

' Splitt strengen opp i mindre deler

aCondition = Split(sCondition, ";")

 

' Gå gjennom alle cellene i Range-objektet

For Each objCell In objRange.Cells

 

' Hent cellenavnet (fant dessverre ingen bedre metoder)

sCellName = Chr(vbKeyA + objCell.Column - 1) & objCell.Row

 

' Gå gjennom alle kommandoene for å se hvorvidt noen henspeiler til denne cellen

For Tell = LBound(aCondition) To UBound(aCondition)

 

' Hent denne linjen

sTemp = aCondition(Tell)

 

' Gjør det mulig å unnlate radbetegnelsen eller kolonnebetegnelsen

sTemp = Replace(sTemp, "%X", Chr(vbKeyA + objCell.Column - 1))

sTemp = Replace(sTemp, "%Y", objCell.Row)

 

' Del opp linjen

aStatement = Split(sTemp, "=", 2)

 

' Hvis kommandoen peker til denne cellen ...

If Trim$(aStatement(0)) = sCellName Then

 

' Denne delen kan faktisk bestå av flere ting

aCommands = Split(aStatement(1), " ")

 

' ... sjekk hva den ber oss gjøre

Select Case aCommands(0)

Case "+" ' Adder verdi

 

' Vi skal addere dersom boolean-verdien er sann

If bTemp Then

 

' Legg til verdien

AddCells = AddCells + Range(aCommands(1)).Value

 

End If

 

Case "&", "&&", "||", "^" ' Boolean

 

If Mid(aCommands(1), 1, 1) = "%" Then

' Vi skal kun sammenligne cellen med tekst

sTemp = Mid(aCommands(1), 2)

Else

' Kommandoen peker til en celle

sTemp = Range(aCommands(1)).Value

End If

 

Select Case aCommands(0)

Case "&": bTemp = CBool(sTemp = objCell.Value)

Case "&&": bTemp = bTemp And CBool(sTemp = objCell.Value)

Case "||": bTemp = bTemp Or CBool(sTemp = objCell.Value)

Case "^": bTemp = bTemp Xor CBool(sTemp = objCell.Value)

End Select

 

End Select

 

End If

 

Next

 

Next

 

End Function

 

Private Sub CommandButton1_Click()

MsgBox AddCells("D%Y=& E%Y;E%Y=+ A%Y", Range("A3", "E29"))

End Sub

 

 

Private Sub CommandButton2_Click()

MsgBox AddCells("D%Y=& E%Y;E%Y=+ B%Y", Range("A3", "E29"))

End Sub

 

Når jeg debugger får jeg bare resultatet 0 i en msgbox, jeg kan ikke se hva feilen er heller. Jeg har da selfølgelig fylt ut tabellen med verdier.

Hilsen Frode

[aadnk]

Jeg kan godt ha misforstått deg i hvorledes og når funksjonen skal summere tallene, men hva jeg antok var at dette skulle forekomme når kolonne D er lik kolonne E i en rad. Har du dette i verdiene?

 

Nå som jeg har sett nærmere på koden, har jeg sett en liten feiltagelse. Jeg har benyttet Range-objektet uten å tenke over at funksjonen kunne ha blitt plassert i en modul eller liknende. Derfor må du erstatte Range med objRange.Worksheet.Range. Jeg har for øvrig oppdatert min forrige post slik at koden nå er korrekt.

[Møllerodden]

Jeg har nok forklart dette litt for dårlig, det beklager jeg. Det er når det blir registrert flere forekomster av kombinasjonen D og E det skal summeres:

 

Ordinære timer | overtid50% | overtid100% | MaskinNr | Maskintillegg

-------5-----------------3----------------2-------------17-------------3--------------

-------7-------------------------------------------------20-------------3--------------

-------5-----------------2-------------------------------17-------------3--------------

-------4--------------------------------------------------6--------------1-------------

 

Her vil det bli summert slik

maskin 17/3 = 17 timer

maskin 20/3 = 7 timer

maskin6/1 = 4 timer

 

Hilsen Frode

Endret 2. april 2005 av Møllerodden

[aadnk]

En mulighet er å sortere alle rader på bakgrunn av rad D og E, og derav summere/trekke sammen alle duplikerte elementer. Du kan eksempelvis sortere verdiene som følger:

 

Range("A1", "E4").Sort Range("D1", "E4"), xlAscending

[Møllerodden]

Takker så mye for hjelpen, jeg fikk en del gode tips i den koden du laget. Selv om jeg ikke forsto alt. Men jeg valgte å løse problemet mitt ved å lage en macro som produserer en Pivottabell som igjen kan brukes til å samle det jeg vil ha.

Takk for at du brukte tid på dette, jeg betaler tilbake med å hjelpe andre her inne.

Hilsen Frode