TRIMMEOMRÅDE(): Hvor euforisk kan man bli av et punktum?

Ganske, spør du meg! I Excel (Microsoft 365) har funksjonen TRIMMEOMRÅDE() kommet (TRIMRANGE på engelsk), og det løser flere utfordringer jeg har hatt i Excel fra tidenes morgen. YESS! Kort forklart gir den muligheten til å avgrense antall rader og kolonner i et område du angir i en formel. OG du kan sparke i gang funksjonen med et enkelt punktum i formelen. Heng på!

Introduksjonen av TRIMMEOMRÅDE innebærer også at mitt hjertebarn EgR() pensjoneres.

Du finner lenke til eksemplet jeg bruker nederst i artikkelen

Utfordringen

Utfordringen som nå er løst elegant, oppstod når vi hadde formler knyttet til en liste, og denne listen ble endret. Et eksempel:

I fanen Navn har vi en liste med navn

I fanen e-post legger vi på domenenavnet på den gamle måten. Vi skriver den første formelen i C2 og drar formelen nedover. Syv navn = syv formler. Og hvis du ser på Cathinka Olsen i bildet under, har det sneket seg inn en feil allerede. I rad tre kan du se at vi har en aina.kopi. Vi går til fanen Navn og sletter raden.

Da mister formelen i C3 referansen sin, og listen ser plutselig slik ut:

Jeg setter aina.kopi tilbake, og forsøker meg på å sette inn en ny rad med Einar Ruud før David Hansen i fanen Navn. Det som skjer i fanen E-post, er at erik.olsen på rad 8 i E-post nå henter informasjon fra rad 9 i fanen Navn, mens Einar Ruud ikke kommer opp i listen. Ingen formler peker på Einar, stakkar. Nå blir det bare rot fremover…

Det siste og mest bittert erfarte problemet er når vi legger til navn på listen, og listen med formler er for kort.

De fleste har nok funnet måter å omgå disse problemene på. Greia er at det ikke lenger er et problem.

Løsninger

Nok om potensielt trøbbel og over til løsninger med høyst subjektiv karaktersetting.

Karakter: 2

Etter at mange av funksjonene i Excel har fått matrisefunksjonalitet, altså at de kan returnere mange rader og/eller kolonner, har vi kunnet løse utfordringene over. Den tradisjonelle formelen som bare gjelder for en rad…

=Navn!B2 & "@excelguru.no"

Karakter: 2+

… kan erstattes av formelen under, som strekker seg over flere rader. Ulempen med formelen under, er at den strekker seg fra rad 2 til 200, og returnerer «@excelguru.no» når den kommer forbi siste navn i navnlisten. Ut over det er den dynamisk som alle matriseformler – hvis navnelisten endres, oppdateres formelresultatet.

=Navn!B2:B200 & "@excelguru.no"

Karakter: 3+

Vi går til neste, som er det første eksemplet med akseptabel karakter. Her bruker vi FILTRER(), og sier at listen som behandles går fra B2 til B200, men det er en forutsetning (Navn!B2:B200<>0) at cellen ikke skal ha verdien 0, som er verdien i en tom celle i Excel. Denne fungerer strålende, og vil også ignorere celler som er tomme inne i listen. Smart å bruke når vi kan ha tomme rader i listen. Skummelt hvis det er en tom celle i B-kolonnen men verdier i andre kolonner vi skulle hatt med.

=FILTRER(Navn!B2:B200;Navn!B2:B200<>0) & "@excelguru.no"

Karakter: 5

Nå kommer vi til det nye og revolusjonerende med TRIMMEOMRÅDE(). Her angir vi at formelen maks skal behandle rader ned til rad 999, men kun inkludere området ned til siste brukte rad. Formelen vil inkludere tomme rader inne i listen.

=TRIMMEOMRÅDE(Navn!B2:B999) & "@excelguru.no"

TRIMMEOMRÅDE() har parametre for hvilke tomme rader / kolonner som skal utelates:

=TRIMMEOMRÅDE(Område;[rad_trim_modus];[kolonne_trim_modus]) , hvor du kan ha verdier fra 0 til 3 på hver:
0 – Ingen
1 – Foranstilte
2 – Etterfølgende
3 – Begge (Standard)
Det betyr at hvis du ikke angir noen parametre, vil TRIMMEOMRÅDE fjerne tomme rader før første rad med verdier og etter siste rad med verdier, og tilsvarende for tomme kolonner før første kolonne med verdier og etter siste kolonne med verdier. Her ser du at rad 1 og 10/11 samt kolonne A og E utelates:

Karakter: 6 (+!)

Så kommer vi dit hvor euforien satte inn for meg. I stedet for å bruke TRIMMEOMRÅDE(), kan vi skrive formelen over slik:

Du ser de små punktumene før og etter kolon? Juhuu. «A1.:.E12» betyr at området skal avgrenses til første rad med verdier i området ned til siste rad med verdier. Det er viktig å merke seg at tomme rader og kolonner inne i området IKKE blir utelatt. Ønsker vi det, må vi bruke FILTRER()
«=A1.:.E12» gir identisk resultat som «=TRIMMEOMRÅDE(A1:E12)»

Formelen «A1:.E12»

«A1:.E12» vil inkludere området fra første rad jeg angir (selv om den ikke har verdier), ned til siste brukte rad, og alle kolonner fra A til siste kolonne med verdier.
«=A1:.E12» gir identisk resultat som «=TRIMMEOMRÅDE(A1:E12;2;2)«

Denne siste varianten (:.) er min favoritt, siden jeg alltid har kontroll på hvilken kolonne og rad tabellene mine starter i.

TRIMMEOMRÅDE og den nye syntaksen .:. løser en rekke potensielle problemer for meg:

• Hvis jeg har glemt å kopiere formelen nedover
• Hvis jeg har endret formelen på det jeg trodde var rad 2 og kopiert den nedover, men har glemt å scrolle opp til rad 2 (lett å glemme at jeg har låst rad 1…)
• Kolonnen med formler er for kort…
• Excelboka blir stor og treig fordi jeg har formler som strekker seg over unødvendig mange rader.

TRIMRANGE i Visual Basic

For deg som programmerer i Visual Basic, har du sikkert en funksjon som finner første ledige rad i en tabell. TRIMRANGE er (i skrivende stund) ikke tilgjengelig i VBA direkte, men vi kan bruke den via EVALUATE. Funksjonen jeg tester ut med lovende resultater heter ffR(), og ser slik ut:

Option Explicit

'Test ffR function
Sub TestFirstFreeRow()

    Dim Rng As Range
    Set Rng = shNavn.Range("A1:E10000")
    Dim FirstFreeRow As Long
    FirstFreeRow = ffR(Rng)
        
    Debug.Print FirstFreeRow

End Sub

'***********************************************
'*** Returns first free row in the Rng range ***
'*** 19.01.2025 Excelguru.no                 ***
'***********************************************
Function ffR(Rng As Range)

    'Handle empty range
    If WorksheetFunction.CountBlank(Rng) = Rng.Cells.Count Then
        ffR = 1
        Exit Function
    End If

    'Not empty - go on!
    Dim FullAdr As String
    FullAdr = Rng.Address(External:=True)

    Dim rng1 As Range
    Set rng1 = Evaluate("TrimRange(" & FullAdr & ",2,2)")
    ffR = rng1.Rows.Count + 1

End Function

HERLIIIIG!

Last ned Excelboka her: