Excel småtips
Nye tip står øverst i
oversigten.
Brug ikke Flet og centrer (NY
17-10-21)
Skriv først resultat, når data
forekommer
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).
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).
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.
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.
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")
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.
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)
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
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.
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))))
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.
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)
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.
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.
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.
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.
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.
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
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.
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)
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.
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.
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.
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.
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.
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.
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.
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.
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.
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))
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.
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.
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.
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.
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.
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).
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.
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.
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.
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
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.
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)
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.
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)))))))
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))
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)
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.
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.
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.
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.
"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.
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.
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.
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.
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.
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.
Brug ikke Flet og centrer
Excel indeholder en funktion, Flet og centrer, som fletter markerede celler
sammen til én enkelt celle og centrerer indholdet i cellen længst til venstre i
den nye celle. Står der noget i de andre celler, slettes dette indhold.
Samtidigt kan den flettede celle skabe problemer i forbindelse med indsæt og
sletning af kolonner, og også ved kopiering af formler, vil dette ikke fungere
optimalt. I stedet kan man bruge funktionen Centrer over markeringen, som findes
i fanebladet Justering under Formatér celler. Funktionen vælges fra ruden
Vandret.