«Beregner (12 tråder): 2%» … AHRGH@#!

For en tid tilbake ble jeg leid inn for å diagnostisere et problem med en Excelbok som til stadighet stoppet opp med meldingen Beregner (12 tråder) og en prosent bak som nesten ikke rikket seg. En helt moderat fil og noen få formler, altså lite som kunne fortelle noe om hvorfor problemet oppstod. Med andre ord – et herlig oppdrag!!!

Problemet viste seg å være relatert til XOPPSLAG() med to søkebegreper, og ligner på problemet vi ser på – og løser – i denne artikkelen.

Problem-formel:

=XOPPSLAG($F2&G$1;$B:$B&$C:$C;$D:$D)

Løsning:

=XOPPSLAG($F2&G$1;$B:.$B&$C:.$C;$D:.$D)

Hensikten med problem-formelen

=XOPPSLAG($F2&G$1;$B:$B&$C:$C;$D:$D)

Formelen trekker ut salget per dag og produkt. Søkebegrepet $F2&G$1 skal gjenfinnes i kolonne B og C, og så skal verdien i kolonne D returneres hvis det blir treff på disse. Formelen i G2 leter etter dag 1 i kolonne B og Produkt1 i kolonne C, og returnerer 6724. Greit nok.

Det er 28 produkter og 365 dager, så det er 10.220 formler. Det er 10.000 rader i tabellen som gjennomsøkes – det er ikke alarmerende mange. Så hva er trøbbelet?

Trøbbelet er at XOPPSLAG ikke begrenser seg til å lete gjennom de 10.000 radene vi har brukt i tabellen, men går gjennom alle radene i arkfanen, altså 1.048.576 rader. Det resulterer i at antall rader som evalueres går fra ca 102 millioner til 10,7 milliarder. Det er trøbbel.

De nye matrisefunksjonene (XOPPSLAG, FILTRER, UNIK, SORTER etc) ser ut til å oppføre seg på samme måte – i kontrast til f.eks gode gamle FINN.RAD() som ser ut til å holde seg til radene som faktisk er brukt.

Løsninger

Når vi bruker de nye matrisefunksjoneneer det lurt å ikke bruke hele kolonner i formelen.

Løsning 1:

I stedet for:

=XOPPSLAG($F2&G$1;$B:$B&$C:$C;$D:$D)

Kan vi skrive

=XOPPSLAG($F2&G$1;$B2:$B50000&$C2:$C50000;$D2:$D50000)

Ulempen med dette er at vi plutselig får 51000 rader i tabellen.

Løsning 2:

I stedet for

=XOPPSLAG($F2&G$1;$B:$B&$C:$C;$D:$D)

Skriver vi

=XOPPSLAG($F2&G$1;$B:.$B&$C:.$C;$D:.$D)

(Og her mente ChatGPT at jeg var ute og syklet, men jeg er altså ikke det.) Når vi angir B:.B (kolon med punktum etter) i stedet for B:B, betyr dette at alle radene i kolonne B skal inkluderes frem til den siste som er benyttet. Det kan gjerne være tomme rader innimellom.

TRIMMEOMRÅDE() er funksjonen vi tar i bruk her, og formelen

=XOPPSLAG($F2&G$1;TRIMMEOMRÅDE($B:$B)&TRIMMEOMRÅDE($C:$C);TRIMMEOMRÅDE($D:$D))

kan heldigvis også skrives slik:

=XOPPSLAG($F2&G$1;$B:.$B&$C:.$C;$D:.$D)

XOPPSLAG og TRIMMEOMRÅDE er tilgjengelig i Microsoft 365 og Excel Online

Les mer om TRIMMEOMRÅDE her

Last ned Excelboka her: