SUM.HVIS funktion som ignorerer skjulte celler

På denne side viser jeg, hvordan man kan lave en regnearksfunktion som Excels SUM.HVIS, men en som ser bort fra skjulte celler.

I Excel kan du ikke få regnearksfunktionen SUM.HVIS til at ignorere skjulte celler. Der er en kringlet omvej, hvor man bruger SUMPRODUKT, SUBTOTAL og FORSKYDNING, men formlen bliver temmelig langhåret, og for brugeren er det meget nemmere at bruge en regnearksfunktion skrevet i VBA.

Herunder følger funktionen, som blev til efter et læserønske. Den er ikke lynhurtig, hvis den skal summere rigtig mange celler, men den virker og er nem at bruge.

For at kopiere koden, markerer du den bare med musen, trykker CTRL+C og indsætter den i et VBA-modul med CTRL+V.

Når du har indsat koden, vil dialogboksen se sådan ud ( i Excel 2003), når du vælger funktionen SUMHVISSKJULT under "Indsæt funktion" og kategorien "Brugerdefineret".

Hvis Operator lades tom, vil funktionen bruge "=". Selvfølgelig kan funktionen også indtastes i en celle.

Selvom der er skjulte celler i sumområdet, ignoreres de. Skjulte celler i kriterieområdet ignoreres ikke, men det kan nemt laves.

I Excels SUM.HVIS-funktion skrives operator og kriterium i ét felt, men for nemheds skyld og for ikke at sløve funktionen, har jeg skilt dem ad.

Som med andre regnearksfunktioner kan man også indsætte formlen direkte i en celle, men så skal man selv have styr på parametrenes rækkefølge.

Bemærk, at parameteren "Kriterium" er deklareret som datatypen String. Et kriterium kan være et tal eller tekst, og ved at deklarere inputtet som String, undgår man fejl, hvis brugeren indtaster et ord (tekst) og undlader at sætte det i citationstegn som fx "Ord".

Havde man defineret Kriterium som datatypen Variant, ville Ord uden citationstegn udløse en Type mismatch-fejl.

Derfor indlæser vi kriteriet i en variabel, vTjek, af datatypen Variant, og er kriteriet numerisk, konverterer vi det til et tal med VBA-funktionen CDbl(Kriterium).

Function SUMHVISSKJULT(Kriterieområde As Range, Sumområde As Range, _
Operator As String, Kriterium As String) As Double
'Excels SUM.HVIS medtager skjulte celler i summen.
'Denne funktion gør ikke.

Dim bSkjult As Boolean     'True hvis en celle er skjult
Dim bEjOpfyldt As Boolean  'True hvis et kriterium ikke er opfyldt
Dim lCount As Long         'Tæller
Dim dSum As Double         'Resultat
Dim rCell As Range         'Range variabel
Dim vTjek As Variant       'Internt kriterium

On Error GoTo ErrorHandle

'Fjerner evt. mellemrum før og efter den aritmetiske operator
Operator = Trim$(Operator)

If InStr(1, Operator, " ") Then
   MsgBox "Mellemrum ikke tilladt i operator."
   Exit Function
End If

If Len(Operator) = 0 Then Operator = "="

'Kriteriet indlæses i variablen vTjek, og hvis det er numerisk,
'konverteres det til et tal.
If IsNumeric(Kriterium) Then
   vTjek = CDbl(Kriterium)
Else
   vTjek = Kriterium
End If

'Gennemløb cellerne i Sumområde
For Each rCell In Sumområde
   'Reset flag
   bSkjult = False
   bEjOpfyldt = False
   'Læg 1 til tæller
   lCount = lCount + 1
   With rCell
      'Hvis cellen er i en skjult række
      'eller kolonne sættes bSkjult = True
      If Rows(.Row).Hidden = True Or _
      Columns(.Column).Hidden = True Then bSkjult = True
   End With
   'Hvis cellen ikke var skjult
   If bSkjult = False Then
      'Hvis tælleren ikke er større end antallet af celler i
      'i Kriterieområde, tjekker vi, om cellen overholder
      'kriteriet. Gør den ikke det, sættes bEjOpfyldt = True.
      If lCount <= Kriterieområde.Count Then
         With Kriterieområde.Item(lCount)
            Select Case Operator
               Case "="
                  If .Value <> vTjek Then bEjOpfyldt = True
               Case ">"
                  If .Value <= vTjek Then bEjOpfyldt = True
               Case "<"
                  If .Value >= vTjek Then bEjOpfyldt = True
               Case ">=", "=>"
                  If .Value < vTjek Then bEjOpfyldt = True
               Case "=<", "<="
                  If .Value > vTjek Then bEjOpfyldt = True
               Case "<>"
                  If .Value = vTjek Then bEjOpfyldt = True
            End Select
         End With
      End If
   End If
   'Hvis kriteriet blev opfyldt og cellen ikke er skjult,
   'lægger vi sum-cellens værdi til den gamle sum.
   If bEjOpfyldt = False And bSkjult = False Then dSum = dSum + rCell.Value
Next

SUMHVISSKJULT = dSum

BeforeExit:
Set rCell = Nothing
Exit Function
ErrorHandle:
MsgBox Err.Description
Resume BeforeExit
End Function

Du kan tilføje en kort beskrivelse, som vises i indsæt funktion-dialogboksen. På billedet højere oppe skrev jeg: "Som Excels SUM.HVIS, men skjulte celler ignoreres."

Det gør du ved at vælge "Makroer" (eller tryk ALT+F8), og en liste med makroer vil komme frem (hvis der er nogle makroer). Men funktioner vises ikke! Skriv eller indkopier funktionens navn og klik "Indstillinger". Så får du mulighed for at skrive lidt.

Husk, at regnearksfunktioner kun kan returnere en værdi, når de kaldes fra en celleformel. De kan ikke ændre noget som helst andet som fx cellens farve.