Excel småtips

Nye tip står øverst i oversigten.


Skriv først resultat, når data forekommer
(NY 5-9-21)
XELLER - en ikke så brugt funktion

Find første - eller unikke forekomster
Find den nærmeste højere dato

Tip til betinget formatering
Ombytning af elementer i celleindhold
Sammenkædning af celleindhold
Brug af navne til beregning i Excel365
Gør bogstaver til tal
Finde næste højere værdi i et LOPSLAG
Omdanne decimal tal til klokkeslæt vist som decimal
Finde første del af celleindhold
Fjerne linjeskift og følende linjer i celle

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

Finde første del af celleindhold
Jeg bliver i samme rille som i de foregående tip. En bestemt del af et celleindhold kan findes ved hjælp af tekstfunktioner så som VENSTRE, HØJRE, MIDT m.fl afhængigt af, hvad der skal findes i. I dette konkrete eksempel, kan teksten have en vilkårlig længde, men hver tekst afsluttes altid med samme antal tegn (her 8), som ikke skal med, fx Helmer Mikkel Christensen ABCD1234 eller Viktoria Mikeline Dolores Smith-Bruhn EFGH5678.

Dette løses nemmest med denne formel (teksten i A1):

=VENSTRE(A1;LÆNGDE(A1)-9)

Den fjerner de sidste 8 tegn samt mellemrummet efter navnet.

- Til top -

Omdanne decimal tal til klokkeslæt vist som decimal
Denne formel er lavet på opfordring. Jeg ved faktisk ikke, hvad formlen skal bruges til, men her er den altså. Man skal forestille sig, at man har fx 12,25 stående i A1. Det vil man have lav et om til et klokkesløt, hvilket kan gøres ganske enkelt ved denne formel =A1/24 og så formatere som klokkeslæt. I dette tilfælde ønskes imidlertid ikke formateret som klokkeslæt men som en slags decimal. Altså 12,25 skal ikke blive til 12:15, men til 12,15. Det kan gøres med

=(HELTAL(A1)&","&(A1-HELTAL(A1))*60)*1

men kun hvis der er tale om et resultat i hele antal minutter. 12,95 omregnes således til 12,57, mens 12,96 vil give en fejl. Skal sekunderne med, kan dette bruges:

=(TIME(A1/24)&","&MINUT(A1/24)&SEKUND(A1/24))*1

Jeg ved som sagt ikke, hvad løsningen skal bruges til, men den virker. vær dog opmærksom på, at der ikke kan regnes videre med tallene på nogen meningsfuld måde.

- Til top -

Finde næste højere værdi i et LOPSLAG
Har man en sorteret liste over værdier i en kolonne (her kolonne B), og returværdier i kolonne Ckan man finde værdier, der ikke står i listen med formlen =LOPSLAG(A1;B1:C10;2;SAND). Er værdierne fx 1, 10, 20, 30 og så fremdeles i kolonne B og man indtaster 13 i A1, vil den returnere værdien fra den næmeste værdi over, altså i dette tilfælde, den returværdi, der står ud for 10 i B-kolonnen. Men hvad nu, hvis man hellere vil have returneret den værdi, der står ud for 20, altså ud for den nærmest højere værdi i B i stedet for den lavere? Jas, så kan dette bruges

=INDEKS(C1:C5;SAMMENLIGN(SAND;B1:B5>A1;0))

Formlen er en matrixformel og skal derfor afsluttes med Ctrl+Skift+Enter.

- Til top -

Gør bogstaver til tal
Endnu en løsning lavet på opfordring. Ønske: "At knytte talvædier til bogstaver, så der kan regnes på bogstaverne. Formål at kunne skrive =A+B og så få resultatet 3 eller lignende. Det er faktisk ligegyldigt, hvilke bogstaver, der vælges og hvilke tal, der knyttes til de enkelte bogstaver. Her bruger jeg alle alfabetets bogstaver fra a til å, og knytter tallene fra 1 til 29 til disse, så A = 1, B = 2 og så fremdeles.

Til formålet bruger jeg et regneark, som eventuelt kan skjules. I dette skriver jeg tallene i kolonne A, startende i A1 og i kolonne B, de tal, der skal knyttes til de enkelte bogstaver. Jeg markerer nu området med tal og bogstaver. I fanebladet Formler vælger jeg Opret ud fra markering i gruppen Definerede navne, vælger at der skal bruges navne fra venstre kolonne og klikker OK. Nu har alle mine talceller fået et bogstav som navn, og jeg kan nu bruge disse bogstaver i mine formler, fx =A*B+(F/C) og resultatet bliver 4. Navnene kan også bruges i funktioner, fx =LOPSLAG(D1;A1:B3;b;FALSK), hvor B jo repræsenterer 2, og dermed returneres en værdi fra matricens anden kolonne.

- Til top -

Brug af navne til beregning i Excel365
I alle versioner af Excel kan man navngive celler og områder. Derefter kan man bruge disse i sine formler. Har jeg fx to kolonner, den ene med priser og den anden med antal, kan jeg kan jeg markere den første af disse og navngive den pris og på samme måde navngive den anden antal. Nu kan jeg så ud for den første række skrive formlen =pris*antal, og få resultatet. I tidligere udgaver af Excel, kunne jeg så kopiere denne formel  nedad til de øvrige rækker. Dette er ikke længere nødvendigt, da Excelgør dette automatisk, når man trykker Enter efter af have skrevet formlen i den første celle.

Vær opmærksom på, at dette ikke virker, hvis området er defineret som  en Excel tabel. I så fald vil jeg få en fejl.

- Til top -

"Sammenkædning" af celleindhold
Har man to celler, A1 og B1, som indeholder fx tekster eller tal, kan man sammenkæde indholdet af disse celler med funktionen SAMMENKÆDNING (=SAMMENKÆDNING(A1!;B1). Vil man have mellemrum eller andre tegn mellem indholdet af de to celler, kan disse tilføjes, fx SAMMENKÆDNING(A1;" ";B1), som her indsætter et mellemrum. Skal det være et andet tegn, skal dette stå mellem anførselstegnene, og der kan kædes sammen med hele ord eller sætninger. Alternativ til SAMMENKÆDNING, kan man bruge sammenkædningsoperatoren '&'. =A1&" "&B1 vil give samme resultat som SAMMENKÆDNING(A1;" ";B1). Naturligvis kan flere celler kædes sammen med begge disse metoder på samme måde. Er det hele kolonner der skal sammenkædes række for række, kan man så kopiere formlen til alle rækker.

Men der er endnu en mulighed. Har jeg dette stående

A          B
Jan       Kronsell
Tom      Hansen
Georg    Bruun

skriver jeg i C1 Jan Kronsell. Dette kopierer jeg så nedad og der vil nu stå Jan Kronsell i alle cellerne. Nu klikker jeg på den knap, der kommer til synbe ved siden af fyldhåndtaget i den nederste celle og vælger Hurtigfyld. Så kopieres mit "mønster" fra den første celle til de øvrige.

Ombytning af elementer i celleindhold
Dette tip kommer som løsning på en konkret opgave. En liste over USA's stater med tilhørende postale forkortelser, var oprettet så først kom statens navn, derefter en bindestreg og så forkortelsen, som fx Alabama -, South Carolina - Sc og så videre, Denne liste skulle ændres til AL - Alabame, SV - South Carolina osv. Denne formel i en hjælpekolonne, klarer jobbet: =HØJRE(A1;2)&" - "&VENSTRE(A1;FIND(" - ";A1)). Den kan så kopieres til alle stater. Skal den opindelige kolonne slettes derefter kan man kopiere kolonnen med formlerne og indsætte samme sted som Værder. Opgav en kunne også have været løst med VBA og ville så ikke kræve en hjælpekolonne.

- Til top -

Tip til Betinget formatering
Når man  skal lave betinget formatering, kan man bruge en formel til at bestemme de celler, som skal formateres. Dette kan man gøre ved at skrive formlen i den dialogboks, der er knyttet til betinget formatering. Det kan dog gøre det besværligt, hvis man skal ændre på betingelserne for formatet, da man skal ind i dialogboksen hver gang. I stedet kan med fordel skrive formlen i en celle, og så bare referere til denne celle i dialogboksen. Så kan man ændre værdierne ved bare at ændre i formlen.

I eksemplet til venstre, skal celler i A-kolonnen, der er større end 5 farves røde, hvis teksten i B-kolonnen er 'a'. I E1 (dette kan selvfølgelig placeres længere væk fra det område, hvor det skal bruges) skriver jeg formlen =OG(A1>=6;B1="a"). og denne formel kopieres nedad. Det er vigtigt, at de formler, man bruger returnerer enten SAND eller FALSK, som vist. De celler, hvor der står SAND i E-kolonnen, er de celler, der vil blive formateret i A-kolonnen. Jeg markerer nu alle celler i A-kolonnen, vælger Betinget Formatering og vælger at bruge en Formel til at bestemme. I formelruden skirver jeg bare =E1 (hvis du bruger musen til at klikke i E1, er det vigtigt at du får sletter dollartegnene i cellereferencen, da den SKAL være relativ. Vælg et format og klik OK.

Hvis du nu vil ændre betingelserne for formateringen, kanb du bare ændre på formlen i E1, fx til =OG(A1<6;B1="b"), og nu vil det så være celle B2, som farves, da det er den eneste, som opfylder den nye betingelse.

- Til top -

Find den nærmeste højere dato
Igen en løsning baseret på et konkret problem. I A-kolonnen er registreret et antal datoer fra A1 til A10 (er området større, kan formlen ændres. I B1 indtastes en dato (eller det kan være dags dato indsat med fx IDAG(). I C1 skal nu vises den dato fra A-kolonnen der er nærmest højere end datoen i B1. Altså har man fx 29-12-20 i B1 og datoer med fem dages mellemrum fra 25-12-20 til 8-2-21, skal C1 vise 30-12-20. Det bkan klares med denne formel, der viser blank, hvis datoen i B1, er højere end den seneste dato i A-kolonnen.

=HVIS(B1>MAKS(A1:A10);"";HVIS(B1="";"";MIN(HVIS(A1:A10>B1;A1:A10;""))))

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

- Til top -

Find første - eller unikke forekomster
I en kolonne, her A-kolonnen fra A1, findes en række tal. Disse kan være unikke, eller de kan gentages. I B-kolonnen skal der skrives 1 ud for første forekomst af et tal og 0 ud for efterfølgende forekomster. Dette kan gøres med denne formel

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

Formlen skrives i B1, og kopieres nedad. Formlen kan også bruges, hvis det er tekster og ikke tal, som står i kolonnen - eller hvis det er blandede værdier.

- Til top -

XELLER - en ikke så brugt funktion
De fleste, der har arbejdet med logiske funktioner, kender funktionerne OG() og ELLER(). Disse tester på om flere betingelser er opfyldt, fx vil =OG(F2=10;F3=11;F4=12) kun returnere SAND, hvis alle tre betingelser er opfyldt - ellers returneres FALSK. =ELLER(F2=10;F3=11;F4=12) vil returnere SAND, hvis bare én af betingelserne er opfyldt, og kun hvis ingen af dem er opfyldte, returneres FALSK,. Der findes imidlertid en funktion mere af samme type, selv om den ikke anvendes særligt ofte. Det er funktionen XELLER(), også kendt som "Udelukkende ELLER (på engelsk exclusive OR). Denne funktion returnerer SAND hvis én og kun én af betingelserne er opfyldt. =XELLER(F2=10;F3=11;F4=12) vil således returnere falsk, hvis F2 = 10 og F3 = 11, uanset, hvad F4 er. Men er F2 = 9, F3 = 134 og F4=12 returneres SAND.

- Til top -

Skriv først resultat, når data forekommer
I et regneark ønskes udregnet summen af de tal, der står i et område. Det gøres nemt med formlen =SUM(område), fx =SUM(A2:A20). Problemet er, at så længe, der ikke står tal  i området, vil resultatet blive vist som 0. Her ønskes det imidlertid, at cellen med resultatet forbliver tom indtil mindst ét tal er indtastet i området, der skal sammenlægges. Det kan gøres med denne formel:

=HVIS(ER.TOM(A2:A20);"";SUM(A2:A20)) indtastet som en matrixformel (afslut med Skift+Ctrl+Enter).

Det er dog et krav, at der skal være tastet et tal i A2, ellers vises hele resultatet som tomt uanset, hvor der ellers står tal i området.

- Til top -
- Retur til Excel -