Excel småtips
Nye tip står øverst i
oversigten.
Variable overskrift i diagrammer (Ny
9-11-14)
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ælger 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(B1;$A$1:$B$10)=2
Tilføj endnu en betingelse og
gentag med =PLADS(B1;$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 date 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 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 nedersti 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 stpr 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.
=HVIS(UGEDAG(DATO(ÅR(A1);1;1);2)>4;UGE.NR(A1)-1;UGE.NR(A1)) - 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 kilometre
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, datoer i stigende orden. I kolonne B tekst, tal eller tom. For at
finde ud af,m hvor mange udfylde 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, and 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, dser 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 dette 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 retuerner henholdsvis 16:00 og 08:00, men som tekster. Ved
at gange hver af dem med 1, ændres de til tal, og subtaktionen 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 (et 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 et argument:
=MINDSTE((B2:B29;F2:F27;J2:J17;N2:N21);2).
Variable 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å dun 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 -
- Retur til Excel -
|