Excel småtips

Nye tip står øverst i oversigten.

Fjerne linjeskift og følende linjer i celle
(Ny 4-11-17)
Udvidet brug af SUMPRODUKT()
Finde en del af en celle
Find største alfanumeriske værdi i en kolonne
Finde værdien i kolonnen til venstre for største værdi i en kolonne
Farve hel række med betinget formatering
Betinget formatering på grundlag af tom celle
Ombyt cellers indhold
Liste over ark i mappe
Jokertegn i TÆL.HVIS og SUM.HVIS

Variabel overskrift i diagrammer

Funktionen MINDSTE() kan kun tage et argument, så hvad med spredte områder?
Beregninger af tid, der er importeret som tekst
Undgå formelfejl med SUM
Skift til Automatisk beregningsindstilling ved åbning

Omregn tidsindtastninger fra tal til klokkeslæt

Fejl i funktionen REST()
Omdan "tal som tekst" til talværdier
Betinget formatering med betingelse i andet ark
Afrund uden at afrunde hver enkelt celle
Finde antal udfyldte værdier inden for et datointerval.
Markere celler, der står spredt i regnearket, men son har samme indhold
Opslag, hvor det, der søges efter, kun er en del af det, der søges i.
Genfind formularfunktionen i 2007/2010
Omregning af timer til minutter
Tre (eller andet antal) bogstaver i datoforkortelse
Undgå at #I/T" vises i en celle
Find største forskel mellem to tal i kolonne
Printerafhængighed       
Beregn korrekt ugenummer 

Find mindste/største med kriterier 
Find tallet længst til højre
Kopier data som billede fra Ark1 til Ark2 i Excel 2007
Tæl antal skift af værdi i et område
MAKS.HVIS findes ikke, hvad så?
Regn på enkelt værdier i navngivent område
Betinget formatering 0 værdier 
Sammenlægge hver anden kolonne/række
Undersøge om værdi findes i en liste.
Omregn arbejdstid i timer og minutter til dage, timer og minutter

Omdanne tal til klokkeslæt
Bestemme om en celle indeholder en bestemt tekst, og dernæst tal
Bestemme antal tal i et område  
Afrunding af ører før/efter 25 ørens afskaffelse.
Tælle unikke værdier i et område.
Returner kolonnebogstav(er) med en formel
Kontroller CPR-nummer
Find "vindere" med Betinget formatering
Sikre unikke værdier i område

Test for "ikke tom" tom celle
Maks()/Min() i stedet for HVIS()


- Retur til Excel -

MAKS()/MIN() i stedet for HVIS()
I nogle sammenhænge kan man spare en HVIS() sætning, og bruge MAKS() eller MIN() i stedet. Hvis en elev har været til omeksamen, og fået en ny karakter, skal den højeste af de to, enten den oprindelige, eller den nye, bruges på eksamensbeviset. Dette kan gøres ved at skrive de to karakterer i fx A1 og A2. Man kan så bruge formlen

=HVIS(A1>A2;A1;A2), men det kan også løses med =MAKS(A1:A2).

- Til top -

Test for "ikke tom" tom celle.
Ved hjælp af funktionen ER.TOM() kan man teste om en celle er tom. Dette kan så bruges i andre beregninger, fx =HVIS(ER.TOM(A1);10;10*A1). Hvis A1 imidlertid er tom som følge af en formel, fx =HVIS(A2<10;"5";""), vil ovenstående formel give en fejl, da cellen jo i virkeligheden ikke er tom. Den indeholder jo formlen. Man kan lave sin egen funktion, der tester for om cellen reelt er tom eller om den indeholder en formel, men man kan også bruge funktionen LÆNGDE(). Denne funktioner tester nemlig på længden af det, der vises i cellen, så erstat den første formel med følgende: =HVIS(LÆNGDE(A1)=0;10;10*A1).

- Til top -

Sikre at der kun indtastes unikke værdier i et område.
For at sikre, at der i et givent område, fx en række, en kolonne eller en matrix, kun indtasts den samme værdi én gang, kan datavalidering bruges. Marker det område, der skal kontrolleres. Vælg Data- Datavalidering. I ruden Tillad vælges Brugerdefineret. I ruden Formel indtastes:

=TÆL.HVIS(A:A;A1)=1

Hvor A:A er området, og A1 er den første celle i området. Ovenstående formel modvirker således dubletter i kolonne A.

- Til top -

Find vindere med betinget formatering.
Såfremt du har et antal tal fordelt over et større celleområde, og du skal finde de tre største tal, og markere disse med hver sin farve, fx Guld, Sølv og Bronze (findes reelt ikke i Excel, men substitueringsfarver kan bruges), kan du  bruge betinget formatering. Start med at markere hele det område, du har tal i. Vælg så Betinget formatering i menuen Formater. I dialogboksen vælger du Formlen er i stedet for Celleværdien er. I ruden indsætter du så denne formel: 

=PLADS(A1;$A$1:$B$10)=1

hvor A1 rettes til den første celle i dit markerede område, og $A$1:$B4$10 rettes til det område, du har dine tal i. Vælg en formatering (Guld).

Tilføj en ny betingelse og indsæt denne formel =PLADS(A1;$A$1:$B$10)=2

Tilføj endnu en betingelse og gentag med =PLADS(A1;$A$1:$B$10)=3

I begge ovenstående tilfælde, skal du selvfølgelig også vælge farve, når du har placeret formlen.

- Til top -

Kontrol af CPR-Nummer
Et CPr-nummers validitet kan kontrolleres med en VBA funktion eller makro, men det kan faktisk også gøres ved hjælp af en almindelig formel i et regneark. Formlen forudsætter at cpr-nummeret indtastes celle A1 i formatet xxxxxx-xxxx, altså med bindestreg. Hele formlen skal indtastes ud i et:

=HVIS(VÆRDI(HØJRE(A1;1))=11-REST(VENSTRE(A1)*4+MIDT(A1;2;1)*3+MIDT(A1;3;1)*2+MIDT(A1;4;1)*7+MIDT(A1;5;1)*6+MIDT(A1;6;1)*5+(MIDT(A1;8;1)*4+MIDT(A1;9;1)*3)+MIDT(A1;10;1)*2;11);"Cpr-nummer OK";"Cpr-nummer ikke gyldigt")

- Til top -

Returner kolonnebogstav(er) med en formel
Under Småmakroer, kan man finde en funktion, der returnerer en kolonnes bogstav evt., bogstaver. Det samme kan gøres med nedenstående formel:

=MIDT(ADRESSE(RÆKKE(INDIREKTE(A1));KOLONNE(INDIREKTE(A1)));2;FIND("$";ADRESSE(RÆKKE(INDIREKTE(A1));KOLONNE(INDIREKTE(A1));2))-1)

I A1 skrives adressen på den celle, men skal finde kolonnebogstavet for.

- Til top -

Tælle unikke celleværdier i et område
For at tælle antallet af værdier i et område, kan man bruge =TÆL(A1:A10), hvis området kun indeholder tal ellerTÆLV(A1;:A10), hvis området også indeholder tekster. Vil man tælle antallet af unikke værdier, kan denne formel bruges:

=SUMPRODUKT((A1:A10<>"")/TÆL.HVIS(A1:A10;A1:A10&""))

Tilføjelsen &"" sikrer at formlen ikke giver fejl, hvis der er tomme celler i det område, der tælles.

Unikke værdier kan også tælles med

=TÆL(HVIS(FREKVENS(A1:A500;A1:A500)>0;A1:A500))

Eller med (Ny 12-12-10)

=SUM(((FREVENS(A2:A100;A2:A100)>0)+0)

- Til top -

Afrunding af ørebeløb før/efter 25 ørens afskaffelse
I dag kan ørebeløb afrundes ved hjælp af enten

=AFRUND(A2/25;2)*25 eller
=HELTAL(AFRUND(A3*4;0))/4

Når 25-'øren afskaffes 1. oktober 2008, kan følgende formel bruges:

=AFRUND(A1/50;2)*50

- Til top -

Antal tal i et område.
Såfremt antallet af tal, der står i et område ikke må overstige et bestemt tal, kan man sikre dette via datavalidering. Antag, at der i området B1:B20 kun må stå et nærmere specificeret antal tal, fx 5, kan dette løses med følgende:

Marker alle celler i det anførte område. Vælg dernæst Data - Datavalidering. Vælg Brugerdefineret, og indlæg denne formel i valideringsreglen:

=TÆL(B1:B20)<=5.

Find på en snedig fejlmeddelelse, hvis nogen alligevel taster flere tal. Såfremt antallet af tal skal være variabelt, fx defineret i celle A1, kan formlen rettes til =TÆL($B$1:$B$20)<=$A$1.

- Til top -

Bestemme om en celle indeholder en bestemt tekst, og dernæst tal.
Sådan lød "udfordringen": En formel, der kan undersøge om en af følgende kombinationer er opfyldt:

P+pasnr = P1234567890
K+kørekortnr = K12345678
G+type+nr = GmilitærID123456789"

Og løsningen blev

=OG(ELLER(VENSTRE(AA5;1)="P"; VENSTRE(AA5;1)="K"; VENSTRE(AA5;1)="G");ER.TAL(VÆRDI(HØJRE(AA5;1))))

- Til top -

Omdanne tal til klokkeslæt
Af og til er der nogle, der gerne vil indtaste klokkeslæt som 1234 (i stedet for 12:34) eller '0234 (i stedet for 02:34) eller evt. 234 (i stedet for o2:34). Det er der ingen problemer i, før man begynder at regne på klokkeslættene, for så går den ikke længere, da Excel jo kun opfatter datoer i formen tt:mm eller tt:mm:ss som klokkeslæt. Ved hjælp af en makro, kan man omdanne indtastningerne til klokkeslæt i den celle, hvor de tastes, men hvis man kan "leve med" en ekstra kolonne, kan omdannelsen ske via denne formel:

=(HELTAL(A1/100)+REST(A1/100;HELTAL(A1/100))*100/60)/24

Det tal, der skal omdannes står i A1.

- Til top -

Omregne arbejdstid i timer og minutter til dage, timer og minutter.
Hvis du fx har en arbejdstid på 7,4 timer pr. dag (7 timer og 24 minutter), og ved, at du har arbejdet   timer og minutter og gerne vil have det omregnet, kan denne lille "fikse" formel bruges:

=HELTAL(A1/7,24*24) & " dage, " & HELTAL(VÆRDI(TIME(A1)&","&MINUT(A1))-(HELTAL(A1/7,24*24)*7,24)) & " timer og " & AFRUND((VÆRDI(TIME(A1)&","&MINUT(A1))-(HELTAL(A1/7,24*24)*7,24)-HELTAL(VÆRDI(TIME(A1)&","&MINUT(A1))-(HELTAL(A1/7,24*24)*7,24)))*100;0)

NB! Husk, at formlen skal skrives på en enkelt linje.

- Til top -


Returnere 0 eller 1, ved ikke fundet opslag
Der findes en del artikler om opslag, men af og til, er man slet ikke interesseret i få returneret en værdi, men kun i at undersøge, om det man leder efter faktisk findes på en liste. I dette eksempel vil vi gerne vide, om den værdi vi indtaster i celle A1 findes på en liste, der befinder sig i cellerne A1 til B70 i Ark2.  Tallet vi leder efter, skal i givet fald stå i områdets første kolonne. Hvis værdien findes, ønsker vi et 1 tal i vores celle; findes værdien ikke, skal der står 0. Følgende formel løser opgaven.

=HVIS(ER.IKKE.TILGÆNGELIG(LOPSLAG(A1;Ark2!A1:B70;1;FALSK));0;1)

- Til top -

Sammenlægge hver anden kolonne
I et regneark står der tal i alle kolonner i række 1, fra A til K kolonnen. Jeg ønsker nu at sammenlægge alle tal i kolonnerne A, C, E.... osv. Dette kan gøres med en Sum-formel, der dog hurtigt bliver vanskelig at vedligeholde, fx
=SUM(A1;C1;E1;....). Der skal ikke mange kolonner til, før det bliver besværligt. I stedet kan følgende bruges:

=SUMPRODUKT((REST(KOLONNE(A1:K1);2)<>0)*(A1:K1))

er det hver anden række, der skal sammentælles bruges fx

=SUMPRODUKT((REST(RÆKKE(A1:A8);2)<>0)*(A1:A8))

Begge summerer kolonner/rækker begyndende med den første. Skal i stedet begyndes med den anden, rettes "=0" til "<>0" i begge formler.

Ønskes i stedet hver tredje række, kan formlen ændres til

=SUMPRODUKT((REST(RÆKKE(A1:A8);3)=0)*(A1:A8))

I dette tilfælde kan det bestemmes, hvilke rækker der indgår ved at ændre =0 til =1 eller =2.

- Til top -

Betinget formatering - 0-værdier
I en celle skal cellen have forskellig baggrund afhæng af om celleværdien er større end, mindre end eller lig med 0. Ved større end 0 skal baggrunden være blå. Ved mindre end 0 skal baggrunde være Rød. ved præcis 0, skal værdien være Gul. Ellers skal cellen ikke formateres.

De to første er nemme. Under Betinget formatering angiver man henholdsvis Celleværdien er større end 0 og vælger blå baggrund og Celleværdien mindre 0 og vælger Rød baggrund. Desværre kan man ikke bare vælge at celleværdien skal være lig med 0 i den sidste variant, da cellen så også farves gul, hvis cellen er tom. I stedet vælges Formlen er, og denne formel indsættes:

=ELLER(IKKE(ER.TOM(A1));0)

hvor A1 rettes til den celle, der testes for.

- Til top -

Regne på enkeltværdier i navngivent område
I Excel kan man navngive områder. Fx kan jeg navngivet området A1:A100 til Liste. Skal jeg så finde summen af området, kan jeg bare skrive =SUM(Liste). Men kan jeg også regne på enkeltværdier i området, under anvendelse af navnet? Svaret er, Ja, det kan jeg godt på én betingelse. Betingelsen er, at formlen skal stå i samme række som den værdi, der skal regnes på. Skal jeg fx lægge indholdet af X10 til en celle i området liste, kan jeg, i samme række som den pågældende celle i området står lave en formel, der løses opgaven. Skal jeg lægge indholdet af A12 sammen med X10, kan jeg et sted i række 12 skrive

=Liste+X10.

- Til top -

MAKS.HVIS findes ikke, hvad så?
Excel indeholder funktioner så som SUM.HVIS() og TÆL.HVIS, men ikke fx MIN.HVIS eller MAKS.HVIS. Sådanne opgaver kan dog løses med forholdsvis simple formler. I kolonne A findes ugedagenes navne. I kolonne b nogle tilhørende værdier. Vil jeg finde den største værdi, der forekommer en mandag, kan jeg bruge

=MAKS(HVIS(A1:A200="mandag";B1:B200))

Formlen indtastes som en matrixformel (afslut med Ctrl+Skift+Enter)

=MIN(HVIS(A1:A200="mandag";B1:B200))

Gennemsnittet kan findes med

=MIDDEL(HVIS(A1:A21="mandag";B1:B21))

Om det er nemmere end

=SUM.HVIS(A1:A21;"mandag";B1:B21)/TÆL.HVIS(A1:A21;"mandag")

er en smags sag.

- Til top -

Tæl antal skift af værdi i et område
Denne formel tæller hvor gange gange der sker en skift af værdi i et kolonneområde. Har man fx værdierne 1, 2, 2, 1, 2 i området A1:A5, sker der 3 skift. Formlen der tæller hedder:

SUM(HVIS(A2:A5<>A1:A4;1;0)) og det skal indtastes som en matrixformel, altså afslut med Ctrl+Skift+Enter.

Funktionen sammenligner hver celle med cellen oven over, for at se om de to værdier er ens. Er det tilfældet, sker der ingenting, er de to forskellige tælles op med 1. Resultatet er summen af 1-tallerne.

Formlen kan også bruges på rækker:

=SUM(HVIS(B1:E1<>A1:D1;1;0))

Eller som en variant af SUMPRODUKT (ikke matrix, altså ganske almindelig afslutning:

=SUMPRODUKT(--(A1:A5<>A2:A5))

For en VBA løsning se Småmakroer.

Til top -

Kopier data som billede fra Ark1 til Ark2 i Excel 2007
Af og til har man brug for at kopiere data fra et ark i en mappe til et andet ark i mappen, men sætte det ind som et billede. I Excel 2007, kan det gøres ved at markere det, man vil kopiere. Vælg pilen nederst i Sætind knappen i Startfanen. Vælg Som billede - Kopier som billede. Når man skal sætte ind klikker man igen på pilen nederst i Sæt ind-knappen, vælger Som Billede - Sæt ind som billede.

Ønsker man at sætte ind som en kæde, skal man ikke kopiere som billede, men kopiere på normal vis. Når man skal sætte ind klikker man igen på pilen nederst i Sæt ind-knappen, og vælger Som Billede - Sæt ind som billedkæde.

- Til top -

Find tallet længst til højre
I kolonne A til kolonne T kan der stå tal i en given række (i eksemplet række 2). der behøver ikke at stå tal i alle celler og tallene er tilfældige (altså uden sortering). Vi skal nu finde det tal, der står længst til højre, hvilket kan gøres med denne formel:

=INDEKS(A2:T2;(MAKS(HVIS(A2:T2>0;KOLONNE(A2:T2)))))

Formlen indtastes som matrixformel, det vil sige at der afsluttes med Ctrl+Skift+Enter

- Til top -

Finde mindste/største med to kriterier.
Funktionen MINDSTE() giver mulighed for at at finde den mindste værdi, næstmindste værdi og så fremdeles i et område. Den virker imidlertid ikke, hvis der skal kriterier på.  I en kolonne har jeg bogstaverne A-F gentaget flere gange og i en anden bogstaverne G-J gentaget flere gange. I tredje kolonne har jeg så nogle tal. Jeg vil nu gerne finde den næstmindste værdi i kolonnen med tal, om hvilke det gælder, at der i den første kolonne står A og i den anden står H. Dette kan gøres med denne formel:

=MINDSTE(HVIS((A1:A100="A")*(B1:B100="H");C1:C!00);2)

Formlen indtastes som matirxformel med Ctrl+Skift+Enter. Formlen kan selvfølgelig ændres til STØRSTE eller finde andre værdier end næstmindste ved at ændre det sidste 2-tal.

- Til top -

Find korrekt ugenummer
Funktionen UGE.NR() som er en funktion i Analysis Toolpak tilføjelsesprogrammet, beregner ugenumre efter den amerikanske model, hvor Uge 1, altid er den uge med 1. januar. Sådan er det ikke i Danmark. Her er uge et den første uge med fire dage, eller sagt på en anden måde: Uge 1 er altid den uge, hvor den første torsdag i året forekommer. Det korrekte ugenummer kan kan beregnes med nedenstående formel (datoen, der skal beregnes ud fra, står i A1). Tak til Iver Jørgensen for at foreslå den rigtige formel, idet min oprindelige lavede fejl i visse år.

=HELTAL(REST(HELTAL((A1+2924)/7)*28;1461)/28+1)

- Til top -

Printerafhængighed
Som alle andre officeprogrammer er også Excel printerafhængigt. Det betyder i praksis at et regneark formateres i overensstemmelse med den valgte printerdriver. Det er derfor en god idé at vælge hvilken printer regnearket skal udskrives på inden man opstiller sit regneark, da et senere valg af en anden printer kan ændre udseendet helt.

- Til top -

Største forskel mellem to tal
I en kolonne findes en række tal i stigende orden. Der er ingen system i tallene, da det er daglige aflæsninger, fx af en kilometertæller, en elmåler eller lignende. For at finde dagen med flest kørte kilometer eller størst forbrug osv. kan denne formel bruges:=MAKS(A2:A30-A1:A29), indtastet som matrixformel, dvs. afsluttet med Ctrl+Skift+Enter.

- Til top -

Undgå at #I/T" vises i en celle
Fejlen #I/T! vises typisk i en celle, hvis fx et opslag ikke returnerer en værdi, men også i en række andre sammenhænge. Dette kan være et æstetisk problem, men også et praktisk. Her antages følgende opslagsfunktion: LOPSLAG(D1;om;2;FALSK), men metoderne kan bruges på alle andre formler, der kan returnere #I/T!.

Er der kun tale om et æstetisk problem kan det løses ved hjælp af betinget formatering: Marker cellen eller området, der skal undersøges Opret en betinget formatering (i 2003 eller ældre, skal anvendes Formlen er, i 2007 skal anvendes Ny Regel - Brug en formel til at bestemme...). Som formel indtastes: =ER.IKKE.TILGÆNGELIG(E1), hvor E1 skal erstattes af cellen, der skal formateres eller den første markerede celle i det område, der skal markeres. Som format vælges nu samme skriftfarve som baggrundsfarven har. Så "skjules" fejlmeddelelsen.

Er der tale om et praktisk problem, fx at man skal regne videre på tallet, og at resultatet her også bliver #I/T!, må man erstatte sin oprindelige formel med en ny: fx kan LOPSLAG(D1;om;2;FALSK) erstattes med =HVIS(ER.IKKE.TILGÆNGELIG(LOPSLAG(D1;om;2;FALSK));"";LOPSLAG(D1;om;2;FALSK)). Fejlen erstattes nu med en blank og cellen kan indgå i fx SUM-formler. Bruger man derimod direkte referencer, fx =E1+E2+E3 og fejlen er i E1, vil man nu i resultatcellen få fejlen #VÆRDI!.

For at løse sidstnævnte problem, kan man kombinere de to løsninger: Formlen rettes til =HVIS(ER.IKKE.TILGÆNGELIG(LOPSLAG(D1;om;2;FALSK));0;LOPSLAG(D1;om;2;FALSK)) og der formateres som ovenfor anført. Cellen med fejl tæller nu som 0 i beregningen.

Begge metoder kan også anvendes på andre fejltyper. I så fald erstattes ER.IKKETILGÆNGELIG(), med ER.FEJL() i løsningerne.

- Til top -

Tre (eller andet antal) bogstaver i datoforkortelse
I Excel kan man formatere datoer som tal i forskellige varianter, men også som navne eller forkortelser på ugedage, fx kan 28-7-2011 formateres som som "torsdag" eller "to" (i begge tilfælde uden anførselstegn). Dette sker under brugerdefineret celleformat, hvor man angiver formatet til henholdsvis "dddd" eller "ddd" (igen uden anførselstegn). Imidlertid er det ikke muligt at formatere med fx 3 eller 4 tegn, altså "tor" eller "tors". Ønsker man dette, kan man  bruge en hjælpekolonne, som senere kan skjules. Denne kan være i samme ark; i eksemplet kolonne X, men kan også være i et andet ark. I denne indsætter man datoerne helt som normal. Der hvor man så har brug for ugedagen med fx 1 eller 3 tegn, indsætter man denne formel:

=VENSTRE(TEKST(X1;"dddd");3)

Ovenstående returnerer ugedagen for datoen i X1 med tre tegn.  Antallet af tegn bestemmes ved at ændre det sidste 3 tal.

- Til top -

Omregning af timer til minutter
Dette tip skulle måske høre til i en af artiklerne om tidsberegninger, men der kom det ikke med, så derfor er det her. Nogen vil måske spørge, hvad problemet er. At omregne timer til minutter er bare at gange med 60, og det har de helt ret i. Men her problemet et andet. Jeg har netop fået spørgsmålet, der i sin enkelhed går på: "Jeg har indtastet en masse tider, fx 4:47, 9:23 osv., og når jeg prøver at lægge dem sammen, får jeg ikke det rigtige antal minutter i alt." Det er fordi Excel opfatter en indtastning af 4:47 som 4 timer og 47 minutter, ikke 4 minutter og 47 sekunder. Det skulle have været indtastet som 0:04:47, og det er der mange, der enten glemmer, eller synes er besværligt. Ønsker man derfor at omregne 4:47 til 0:04:47 er opskriften denne:

I en tom celle i regnearket skrives 60. Denne celle kopieres. Marker nu alle de celler, som skal ændres fra timer og minutter til minutter og sekunder. Vælg Rediger - Indsæt speciel (2003) eller Sætind-knappen i Start-fanebladet og vælg Indsæt speciel (du kan også vælge det fra højrekliksmenuen). I dialogboksen markeres Divider. Formater cellerne til formatet tt:mm:ss eller bare mm:ss. Så er det på plads.

- Til top -

Genfind formular-funktionen i 2007/2010
I Excel 2003 og tidligere kunne man under menupunktet Data finde funktionen Formular. I Excel 2007 og 2010 findes denne funktion ikke længere under fanebladet Data - eller for den sags skyld under nogen af de andre faneblade. Den findes imidlertid stadig i Excel, den er bare gemt særdeles godt. Hvis du vil bruge funktionen, er du nødt til selv at føje knappen til værtøjslinjen Hurtig Start.

Klik på pilen til højre for Hurtig Start og vælg "Flere kommandoer." I ruden Vælg kommandoer fra, vælger du kategorien "Kommandoer, der ikke er på båndet". Et stykke nede på listen findes kommandoen "Formular". Klik på - og tilføj denne. Klik OK. Så kan du bruge formularfunktionen som i gamle dage.

- Til top -

Opslag, hvor det søgte kun er en del af det, der søges i
I kolonne C1:C100 findes nogle tekster af typen Jan (100), Ib (200), Karen (300), Irmgard (400). Teksterne kan forekomme flere gang, men det samme tal står kun én gang i c-kolonnen, og forekommer altså ikke med flere tekster. Vi vil nu gerne kunne slå op efter fx 400, og så have returneret den værdi, der står i E-kolonnen i samme række som Irmgard (400) forekommer i C-kolonnen.  Opslagsværdien indtastes i Ark2, celle A1 og værdien skal returneres i Ark2, celle D5, hvor denne formel skal anbringes:

=LOPSLAG(("*"&A1&"*";Ark1!C1:E100;3;Falsk),

"*"&A1&*"*" introducerer jokertegn (wildcards) i søgeværdien. Står der 400 i A1, vil funktionen gennemsøge C-kolonnen  Ark1 efter *400* og vil derfor finde Irmgard (400). Resten af formlen er en normal LOPSLAGS-funktion. Det er kun argumentet Opslagsværdi, der er anderledes. Problemet kunne også være løst med en makro, se Opslag ved hjælp af makro.

- Til top -

Markere celler, der står spredt i regnearket, men som har samme indhold

  • Brug funktionen Søg - fanebladet Startside.

  • Søg efter det indhold, cellerne har.

  • Klik på Find alle. Nu vises en liste i dialogboksen med alle forekomster.

  • Klik på den øverste af disse. Tryk Ctrl +A for at markere alle.

  • Luk dialogboksen. Så er alle celler med dette indhold markerede.

- Til top -

Finde antal udfyldte værdier inden for et datointerval
I kolonne A står datoer i stigende orden. I kolonne B kan være tekst, tal eller cellen kan være tom. For at finde ud af, hvor mange udfyldte celler, der findes i et givent datointerval, kan dette bruges:

=SUMPRODUKT((A1:A3000>=G1)*(A1:A3000<=G2)*(B1:B3000<>""))

I G1 indtastes startdato for det ønskede interval, i G2 slutdato for intervallet.

- Til top -

Afrund uden at afrunde hver enkelt celle
I et celleområde findes et antal celler (her A1:A4), der er indtastede med decimaler, fx 4,5 - 5,5 - 6,6 og 7,1. Disse kan jeg lægge sammen med SUM() og få 23,7. Formaterer jeg nu cellerne til 0 decimaler, står der 5, 6, 7, og 7, og lagt sammen giver det fortsat 23,7. Jeg vil imidlertid gerne have det til at give 25 uden at afrunde hver enkelt celle og uden at indstille Excel til Præcision som vist. Det kan gøres med denne matrixformel. Husk at afslutte med Ctrl+Skift+Enter.

=SUM(AFRUND(A1:A4;0))

- Til top -

Betinget formatering med betingelse i andet ark
Hvis man skal lave betinget formatering, og den celle, som skal opfylde betingelsen, står i et andet ark, end den/de celler, der skal formateres, vil man opdage, at man ikke i betingelsen kan referere til celler, der står i et andet ark. Antag, at en celle i Ark1 skal være rød, hvis A1 i Ark2 har værdien 1. Under alle omstændigheder skal bruges Brug en formel til at bestemme, hvilke celler, der skal formateres. I formelruden kan man så skrive =Ark2!A1=1, og når man klikker OK, får man denne fejl: "Referencer til andre regneark og projektmapper kan ikke bruges som Betinget formatering-kriterier". Problemet kan dog løses, ved at navngive den celle, der indeholder kriteriet, altså A1 i Ark2. Kaldes den fx Betingelse, kan man i formel vinduet skrive =Betingelse=1 og så vil det virke.

- Til top -

Omdan "tal som tekst" til talværdier
Når man importerer tal fra eksterne kilder, så som regnskabsprogrammer, statistikprogrammer og lignende., sker det ofte at Excel opfatter disse tal som om de var tekster. Det vil sige at de venstrestilles, ikke kan bruges i opslagsfunktioner med mere. Problemet kan som regel løses på følgende måde:

Skriv et 1-tal i en tom celle. Kopier denne celle. Marker alle de tal, der skal omdannes fra tekst til tal. Vælg Indsæt speciel. Marker Mulitplicer i dialogboksen og klik på OK. Nu skulle du være kørende.

Fejl i funktionen REST()
Funktionen REST, somn har denne syntaks =REST(tal der skal divideres;tal, der skal divideres med) har en fejl, der betyder at den ikke kan håndtere tal større end 268.435.455 hvis divisionen, der undersøges er med 2. Allerede ved næste tal 268.435.456 returnerer den #NUM! i stedet for resten. Dette skyldes den måde funktionen fungerer internt på. Fejlen opstår, når andet argument (divisoren) i funktionen ganget med 134.217.728 er mindre end eller lig end det tal, der undersøges (første agument).  Er divisoren således 3 i stedet for 2, er det største tal, der kan håndteres 402.653.183, er divisoren 4, er tallet  536.870.911 og så fremdeles.

Problemet kan løses ved at undgå at bruge REST-funktionen og i stedet udføre denne beregning:

Tal, der skal undersøges - (HELTAL(Tal, der skal undersøges/divisor)*Divisor).

Der er dog også andre muligheder i forskellige situationer. Skal man fx undersøge om et cpr-nummer tilhører en mand eller kvinde, kan denne formel bruges: =HVIS(REST(HØJRE(F19);2)=0;"kvinde";"mand") under forudsætning af at cpr-nummeret er indtastet uden bindestreg.

- Til top -

Omregn tidsindtastninger fra tal til klokkeslæt
I en kolonne er klokkeslæt indtastet som tre eller firecifrede tal, fx 1230 for 12:30 eller 800 for 08:00. Disse tal vil vi nu gerne lave om til "rigtige" klokkeslæt med timer og minutter. Dette kan gøres nemt i en hjælpekolonne, ved hjælp af denne formel:

=(HELTAL(A1/100)+REST(A1/100;HELTAL(A1/100))*100/60)/24

hvor det forudsættes at de oprindelige tal står i A1. Hjælpekolonnen skal formateres som klokkeslæt.

- Til top -

Problemer med skift til manuel beregning
Excel kan indstilles til at beregne manuelt, altså ved tryk på F9 eller automatisk, når der sker ændringer i regnearket. I sig selv giver dette ikke anledning til problemer, men i hvert fald i Excel 2010 vil Excels standardindstilling blive sat som den første mappe, man åbner efter at Excel er startet. Hvis denne er gemt med Manuel beregning, vil Excel blive indstillet til dette, og så vil også de følgende mapper blive åbnet med manuel beregning, selv om de er gemt med automatisk. Løsningen kan være at oprette mappen person.xlsm. Den indeholder makroer, og oprettes første gang man indspiller en makro i den. Man kan indspille en simpel makro, der ikke gør noget, og så indstille beregningen til Automatisk. Så længe der er en makro i person.xlsm, åbnes denne automatisk samtidigt med at Excel starter, og altså før man selv åbner mapper. Da person.xlsm altid åbnes skjult, vil man ikke lægge mærke til det, men de "almindelige" mapper, vil blive åbnet i standardindstillingen Automatisk.

Desværre ser det ud til, at der også er en mindre fejl/uhensigtsmæssighed i programmet, som gør at nogle mapper altid åbnes i Manuel beregning, uanset om man sikrer at Excel er indstillet til Automatisk, når de gemmes, og ligeledes sikrer at Excel er indstillet til Automatisk, når de åbnes. Der er ingen umiddelbar løsning på dette problem, men man kan afhjælpe det ved at følge beskrivelsen under Småmakroer.

Undgå formelfejl med SUM
Af og til skal man opbygge en formel, der referer til celler, hvis indhold, man ikke kender på forhånd. Hvis dette indhold (enten via indtastning eller "beregning"), kan blive en tekst, kan der opstå fejl i den refererende formel. Lad mig se på et eksempel. Antag denne formel: =(A1+A3)*A2. A1 og A2 indeholder altid tal, mens A3's indhold bestemmes af denne formel: =HVIS(A1<10;3;"Tallet er for stort"). Så længe A1 er mindre end 10 vil det ikke give problemer, men bliver A1 ti eller mere, vil den oprindelige formel give fejlværdien #VÆRDI!. Dette kan undgås ved at "pakke" den "usikre" reference ind i en SUM, altså =(A1+SUM(A3))*A2. Dette vil bevirke at A3 ignoreres i formlen, hvis den ikke indeholder tal.

- Til top -

Beregninger af tid, der er importeret som tekst
En celle, fx A1 indeholder følgende registrering: 08:00-16:00. Dette opfattes af Excel som tekst, men repræsenterer i virkeligheden en tidsregistrering, som nok burde have været foretaget i to celler. Her importeres data imidlertid fra et eksternt program i dette format. Ønsker er at trække det første tal fra det sidste uden at opdele indholdet i cellen i to, og dette kan gøres med

=(HØJRE(A1;5)*1)-(VENSTRE(A1;5)*1)

De to funktioner er tekstfunktioner, som returnerer henholdsvis 16:00 og 08:00, men som tekster. Ved at gange hver af dem med 1, ændres de til tal, og subtraktionen foretages.

- Til top -

Mindste, kan kun tage to argumenter, så hvad hvis mine områder er spredt?
Funktionerne MINDSTE() og STØRSTE() kan finde henholdsvis den n'-mindste eller -største værdi, fx vil MINDSTE(A1:A100;3) finde den tredjemindste værdi i området. Problemet er, at MINDSTE kun kan have to argumenter (ét semikolon), så hvad nu med områder, der ligger spredt og i som derfor skal adskilles med semikolon, fx B2:B29;F2:F27;J2:J17;N2:N21. =MINDSTE(B2:B29;F2:F27;J2:J17;N2:N21;2) vil give fejlen "Du har indtastet for mange argumenter til denne funktion". Løsningen er simpelthen at placere alle områderne inde i en parentes - så opfattes de kun som ét argument: =MINDSTE((B2:B29;F2:F27;J2:J17;N2:N21);2).

- Til top -

Variabel overskrift i diagrammer
Normalt når man laver et diagram, kan man hente overskriften til diagrammet fra en celle. Ved at lave denne celle som en formel, kan man nemt gøre sine overskrifter dynamiske i stedet for at de er statiske. Fx vil en formel som =A1&" "&B1 hente data fra A1 og A2 ind i overskrifterne. Når indholdet af disse celler ændres, ændres overskriften i diagrammet også. Formlen kan naturligvis udvides, så den referer til flere celler og indeholder fast tekst. Hver af de celler, der refereres til kan i sig selv  indeholde formler, og på den måde kan diagramoverskrifter/forklaringer gøres så dynamiske, som det ønskes.

- Til top -

Jokertegn i TÆL.HVIS og SUM.HVIS
Normalt vil man bruge TÆL.HVIS på denne måde: TÆL.HVIS(A1:A10;3) eller TÆL.HVIS(A1:A10;"Else"), og SUM.HVIS bruges på samme måde. Det er midlertid muligt, kun at søge på en del af en celles indhold ved hjælp af jokertegn. En asterisk (*) kan stå i stedet for hvad som helst og et ukendt antal tegn: TÆL.HVIS(A1:A10;"*e*") vil således tælle alle celler, der indeholder et 'e'. Er der kun en asterisk, tælles i henhold til den, fx tæller TÆL.HVIS(A1:A10;"*e") alle de celler, der slutter på 'e'. Det er også muligt at have mere end et jokertegn, fx TÆL.HVIS(A1:A5;"*e*s*"). I stedet for en asterisk kan man bruge et spørgsmålstegn (?), som erstatter netop et tegn. TÆL.HVIS(A1:A10:"e?" vil skåledes tælle de celler, hvor der er netop et tegn efter 'e', fx 'Hansen', men ikke 'Hansens'.

SUM.HVIS kan tilsvarende bruges med jokertegn, men det kan ikke bruges på samme måde i fx SUMPRODUKT.

- Til top -

Liste over ark i mappe
Dette tip refererer til en ikke specielt kendt funktion i Excel, nemlig muligheden for at få vist en pop-up menu med en oversigt over alle de ark, der findes i en given mappe. Placer musen over "bladringsknapperne" til venstre for arkfanerne, og højreklik. Så vises en oversigt over alle ark. Klik på et arknavn i listen, og man skifter til dette ark.

 

- Til top -

Ombyt celleindhold
Også dette tip henviser til en ikke særlig kendt funktion i Excel. Har man to celler i to kolonner, ved siden ad hinanden kan man bytte om på de to cellers indhold ved hjælp af musen. Det samme gør sig gældende, hvis cellerne står i to rækker under hinanden. Ønsker man at ombytte a og b i eksemplet til venstre klikker man på cellen med a. Der efter skal man holde skift-tasten nede, mens man trækker med musen til højre kan af cellen med b nås. Når man "er" det rigtigste sted vises der noget, som mest af alt ligner et stort I og så skal man slippe musen, hvorefter de to celler ombyttes. ´Det samme kan også gøres i rækker.

Betinget formatering på grundlag af tom celle
Dette tip ligger i forlængelse af tippet om at teste for "Ikke tom" tom celle. Her gælder det, dog at en celle skal have ét format, hvis en anden celle er tom, og et andet, hvis den ikke er. I eksempelt skal cellen skifte farve, hvis en anden celle ikke er tom, men det kunne være andre formateringer også.

I cellen, der skal betinget formateres vælges Betinget formatering og "Brug en formel til at bestemme". Indsæt denne formel: =IKKE(ER.TOM(A1). Dette virker dog ikke, hvis der står en formel i A1, som kan give et tomt resultat. I så fald kan denne bruges =LÆNGDE(A1)<>0

- Til top -

Farve hel række med betinget formatering
I B-kolonnen står nogle værdier. Hele den række i området A1:I100, hvor den største værdi i B-kolonnen står, skal formateres med gul baggrundsfarve. Brug Betinget formatering og "Brug en formel til at bestemme". Indsæt denne formel i betingelsesruden og vælg det relevante format:

=$B1=MAKS($B$1:$I$100)

Husk dollartegnene. Der er vigtige, hvis formateringen skal fungere.

- Til top -

Finde værdien i kolonnen til venstre for største værdi i en kolonne
I to kolonnen, her A og B findes nogle værdier. I en celle ønskes returneret den værdi i A-kolonnen, der står ud for det største tal i B-kolonnen. Denne formel løser opgaven

=INDEKS(G1:H100;SAMMENLIGN(MAKS(H1:H100);H1:H100;0);1)

- Til top -

Find største alfanumeriske værdi i en kolonne
At finde den største numeriske værdi i en kolonne, kan gøre med fx =MAKS(A1:A50). Derimod er det lidt mere tricky, hvis man skal finde den største alfanumeriske værdi. Denne formel løser problemet

=INDEKS(A1:A5;SAMMENLIGN(MAKS(TÆL.HVIS(A1:A5;"<"&A1:A5));TÆL.HVIS(A1:A5;"<"&A1:A5);0))

i det, der tages udgangspunkt i tegnenes placering i ASCII-tabellen. Specialttegn har lavere værdi en tal og tal lavere end bogstaver. For at finde den mindste værdi rettet MAKS til MIN.

- Til top -

Finde en del af en celle
Fordi en ting er nem, behøver det ikke at give en kort formel. I en celle står følgende tekst:

/Kategori/123-vandpistoler-automat/12345-Det skal findes/

Fra dette vil vi gerne hente teksten "Det skal findes" altså alt, hvad der kommer efter den sidste bindestreg. Det kan gøres med denne "nemme og overskuelige" formel:

=MIDT(MIDT(A1;FIND("/";A1;FIND("/";A1;FIND("/";A1;1)+1)+1)+1;LÆNGDE(A1)-(FIND("/";A1;FIND("/";A1;FIND("/";A1;1)+1)+1)+1));FIND("-";MIDT(A1;FIND("/";A1;FIND("/";A1;FIND("/";A1;1)+1)+1)+1;LÆNGDE(A1)-(FIND("/";A1;FIND("/";A1;FIND("/";A1;1)+1)+1)+1));1);LÆNGDE(MIDT(A1;FIND("/";A1;FIND("/";A1;FIND("/";A1;1)+1)+1)+1;LÆNGDE(A1)-(FIND("/";A1;FIND("/";A1;FIND("/";A1;1)+1)+1)+1))))

Når der er så mange FIND skyldes det at vi skal finde hver skråstreg for sig og ligeledes den sidste bindestreg.

En kortere formel til løsning af samme opgave er lavet af bruger Dafodil på Eksperten.dk, og jeg håber at blive tilgivet for at bringe den her:

=HØJRE(A1;(LÆNGDE(A1)-(LÆNGDE(VENSTRE(A1;SØG("-";A1;(SØG("/";A1;(SØG("/";A1;(SØG("/";A1)+1)))+1)))))))

- Til top -

Udvidet brug af SUMPRODUKT()
Kolonne A og B indeholder navne. Kolonne C og D indeholder værdier. De samme navne forekommer i både kolonne A og kolonne B, men ikke i amme rækkefølge. Hvis navnet står i A står den tilhørende værdi i C, står navnet i B står værdien i D. Vi vil gerne sammenlægge alle de værdier, som "hører til "Jens", uanset om Jens står i A eller B. Dette kan gøres med

=SUMPRODUKT((A1:B10="Jens")*(C1:D10))

- Til top -

Fjerne linjeskift og følgende linjer i celle
I en celle står tekst på to linjer. Her vil man gerne bevare kun teksten i linje 1, mens linjeskiftet og teksten i følgende linjer fjernes. Det kan gøres med følgende formel

=MIDT(A1;1;FIND(TEGN(10);A1;1)-1)

- Til top -
- Retur til Excel -