startsida < företaget < organisera < datorer < tips
 
Villkorsstyrd formatering I

 
  Kalkylering
Kalkylering I
Kalkylering II
Kalkylering III
Kalkylering IV
Kalkylering V
Kalkylering VI
Kalkylering VII
Villkorsstyrd
kalkylering
Villkorsstyrd
formatering I
Villkorsstyrd
formatering II
Felhanterings-
kalkylering
Matriskalkylering I
Matriskalkylering II
Matriskalkylering III
Matriskalkylering IV
Matriskalkylering V
Datumkalkylering I
Datumkalkylering II
Datumkalkylering III
Datavalidering I
Datavalidering II
Cirkelreferenser
   
  Innehåll
expowera

 

Materialet publicerat med tillstånd från Dennis Wallentin.

Viktigt att notera
Vid villkorsstyrd formatering kan endast Excel:s inbyggda kalkylbladsfunktioner användas. Funktioner i t ex Analysis Toolpak fungerar inte här ej heller egna skapade tilläggsfunktioner, dvs inga funktioner i s k tilläggsverktyg (add-ins) kan användas.

Därför är vi utlämnade till vår egna fantasi vid skapandet av villkor för att erhålla samma funktionalitet - vilken utmaning!

Datumformatering
Antag att du sitter och arbetar med en tidredovisningsmodell. Du vill markera vilka datum som infaller på lördagar respektive söndagar. Hur löser du det på ett enkelt och tidsbesparande sätt? Jo, med hjälp av villkorsstyrd formatering.

Så här skapar man det:

  • Steg 1: markera cellområdet innehållande datumen, i exemplet nedan används A2:A32.
     
  • Steg 2: Välj kommandot Format / Villkorsstyrd formatering
     
  • Steg 3: Fyll i önskade villkor och formateringar.

Generellt måste villkoren kunna utvärderas i SANT eller FALSKT. En annan aspekt är att verktyget är begränsat till tre villkor.

I exemplen används VECKODAG-funktionen. Om man inte anger ett andra argument (returtyp) eller anger 1 så börjar veckan med söndag som dag 1. Vill man börja med måndag som dag 1 måste returtypen anges till 2! Svenska standard säger att veckan ska börja på måndag.

Veckodag Formeln är:
Lördagar =VECKODAG(A2)=7
Söndagar =VECKODAG(A2)=1
Lördagar & Söndagar =ELLER(VECKODAG(A2)=1;VECKODAG(A2)=7)
Veckodagar =OCH(VECKODAG(A2)>1;VECKODAG(A2)<7)
Veckodagar =ICKE(ELLER(VECKODAG(A2)=1;VECKODAG(A2)=7))
Första dagen i månaden =DAG(A2)=1
Sista dagen i månaden =DAG(A2+1)=1
1:a måndagen i månaden =OCH(VECKODAG(A2)=2;DAG(A2)<=7)
2: måndagen i månaden =OCH(VECKODAG(A2)=2;DAG(A2)>7;DAG(A2)<=14)
3:e måndagen i månaden =OCH(VECKODAG(A2)=2;DAG(A2)>14;DAG(A2)<=21)
4:e måndagen i månaden =OCH(VECKODAG(A2)=2;DAG(A2)>22;DAG(A2)<=28
5:e måndagen i månaden =OCH(VECKODAG(A2)=2;DAG(A2)>28
Markera alla helgdagar * =OM(ÄRSAKNAD(LETARAD(A2;D11:D13;0;FALSKT));0;1)=1
Markera alla dagar utan helgdagar * =OM(ÄRSAKNAD(LETARAD(A2;D11:D13;0;FALSKT));1;0)=1

* Intervallet antas innehålla datum för helgdagar mm

Formatera veckodagar
Vi går vidare med vår modell. Nästa steg är att markera veckodagarna med skilda färger.

Finns datumangivelser så kan man mha av formeln VECKODAG() få fram siffrorna 1 - 7 i intilliggande celler. Mha talformatet "DDDD" erhålls veckodagarna i klartext. Tabellen nedan visar den villkorsstyrda formateringen för olika alternativ.

Veckodag Formeln är:
Lördagar =C4=7
Söndagar =C4=1
Lördagar & Söndagar =ELLER(C4=1;C4=7)
Veckodagar =OCH(C4>1;C4<7)
Veckodagar =ICKE(ELLER(C4=1;C4=7))

Vill man sätta ytterligare färg på tillvaron så kan det ske med maximalt 6 olika färger - 3 med villkorsstyrd formatering och 3 med villkorsstyrd talformatering!

Veckodag Färg Villkorsstyrd formatering & talformat
Lördag & Söndag Svart =ELLER(C4=1;C4=7)
Måndag Grå =C4=2
Tisdag Gul =C4=3
Onsdag Röd [Röd][=4]DDDD;[Grön][=5]DDDD;[Blå]DDDD
Torsdag Grön Som ovan
Fredag Blå Som ovan

Utöver färgmarkeringar så kan man också använda sig av ramar och cell- skuggningar - Pröva!

Formatera månader
Behöver man markera månader kan det ske mha av följande formel:

  • =MÅNAD(A1)=4

Denna kan utvecklas till att t ex avse ett kvartal:

  • =ELLER(MÅNAD(A1)=1;MÅNAD(A1)=2;MÅNAD(A1)=3)

Eller om man vill markera det första halvåret:

  • =OCH(MÅNAD(A1)>=1;MÅNAD(A1)<=6)

Visuell jämförelse av cellområden
Problemet är följande: Vi har två cellområden, där värdena kontinuerligt förändras i båda områdena. Varje gång ett värde i cellområde 1 är lika med ett värde i cellområde 2 ska det markeras.

Här måste vi vara litet kluriga. Dels ska formeln kunna leta upp värdena för område 1 i område 2 och dels kunna generera ett logiskt utfall. Det första löser vi mha LETARAD - funktionen. För det andra problemet måste vi här använda oss av två logiska funktioner; ICKE och ÄRSAKNAD! Resultatet blir följaktligen formeln:

  • =ICKE(ÄRSAKNAD(LETARAD(Första cellen i område 2;Område 1;0;FALSKT)))

Viktigt är att inte använda sig av absoluta referensen:

  • =ICKE(ÄRSAKNAD(LETARAD(B2;A:A;0;FALSKT)))

En begränsning är att man inte kan leta i flera kolumner, dvs område 1 kan inte avse flera kolumner.

Visa medel, minimum och maximum
Här demonstreras några tekniker för att formatera tal i större datamängder.

Vill man visa de tal som är över, under eller lika med medelvärdet så kan man använda sig av följande formler:

  • =A1>MEDEL($A$1:$A$10)
     
  • =A1<MEDEL($A$1:$A$10)
     
  • =A1=MEDEL($A$1:$A$10)

För att erhålla maximum- respektive minimumvärdet i datamängden så kan man använda sig av följande formler:

  • =A1=MAX($A$1:$A$10)
     
  • =A1=MIN($A$1:$A$10)

Markera de största & minsta talen
Händer det att du arbetar med större datamängder och där det gäller att hitta de största eller minsta värdena? M h a villkorsstyrd formatering kan den processen bli relativ enkel.

För att markera de två största värdena i datamängden:

  • =ELLER(A1=STÖRSTA($A$1:$A$8;1);A1=
    STÖRSTA($A$1:$A$8;2))

För att markera de två lägsta värdena ersätts STÖRSTA-funktionen av MINSTA-funktionen enligt följande:

  • =ELLER(A1=MINSTA($A$1:$A$8;1);A1=
    MINSTA($A$1:$A$8;2))

För att identifiera både det största respektive lägsta värdet kan följande formel användas:

  • =ELLER(A1=MINSTA($A$1:$A$8;1);A1=
    STÖRSTA($A$1:$A$8;1))

Vill man utöka antalet villkor är det bara att lägga till dessa, såsom:

  • =ELLER(A1=STÖRSTA($A$1:$A$8;1);A1=
    STÖRSTA($A$1:$A$8;2);A1=STÖRSTA($A$1:$A$8;3);A1=
    STÖRSTA($A$1:$A$8;3))

Hitta duplikat
Att hitta dubbletter i en lista kan vara tidskrävande men underlättas mha villkors- styrd formatering.

Som vanligt(!) måste man skapa en formel som ger värdet sant eller falskt. Här presenteras ytterligare ett sätt att skapa denna förutsättning:

  • =OM(ANTAL.OM($A$1:$B$4;A1)>1;SANT;FALSKT)

Om antal överstiger 1, dvs om det finns flera celler med samma värde i listan så är påståendet sant!

Vill man markera celler vars värden inte förekommer fler gånger så kan man göra det mha följande formel:

  • =OM(ANTAL.OM($A$1:$B$4;A1)=1;SANT;FALSKT)

Jämna & Udda tal
Antag att du vill markera samtliga jämna tal i en större datamängd. Hur ska det lösas på bästa sätt?

Funktionen ÄRJÄMN tillhör Analysis Toolpak varför den inte kan användas.

Ett annorlunda angreppssätt är att utföra en division och undersöka huruvida den ger en rest eller inte, dvs om det uppstår decimala värden vid division eller inte.

För att få en logisk utvärdering måste uttrycket jämföras med ett värde. Funktionen REST ger värdet 0 när det inte finns decimala tal och värdet 1 när divisionen ger decimala tal. Divisorn, den faktor som tal ska divideras med, anges här till 2 och formelmässigt ställs villkoret upp enligt följande:

  • =REST(A1;2)=0

Vill man markera udda tal så ersätts värdet 0 med värdet 1 i ovanstående formel.

Månad & Datumkontroll
Antag att du vill få reda på vilka poster i en lista som faller inom en specifik månad.

Ett sätt är att använda sig av följande formel vid villkorsstyrd formatering:

  • =MÅNAD($A2)=MÅNAD($B$2)

Om vi antar istället att vi vill få reda på huruvida ett datum faller inom ett datumintervall, t ex från den 1:a och 15:e i samma månad.

Följande formel löser det problemet:

  • =OCH($A10<$B$10;$A10>DATUM(ÅR($B$10);
    MÅNAD($B$10);1))

Markera unika värden
Här demonstreras en teknik för att markera unika värden i en datamängd.

Följande formel utvärderas till SANT eller FALSKT

  • =ANTAL.OM(B$2:B2;B2)=1

Vill man istället formatera värden som förekommer flera gånger justeras ovanstående formel enligt följande:

  • =ANTAL.OM(B$2:B2;B2)<>1

Villkor i ett annat blad
Antag att vi vill formatera en cell i ett blad utifrån ett villkor i ett annat blads cell. Vanligtvis tillåter XL inte oss att göra det men här visas en lösning på situationen.

Exemplet bygger på att i ett annat kalkylblad finns en cell (E5) som ska utvärderas. Om värdet i cell E5 är lika med 10 så ska formatering ske, annars inte.

Formelmässigt får vi då följande lösning:

  • =INDIREKT(ADRESS(RAD(E5);KOLUMN(E5);4;
    SANT;"Villkor i ett annat blad 2"))=10

Formatera rader & kolumner
Vid design av kalkyler och tabeller kan arbetet underlättas avsevärt mha villkorsstyrd formatering.

Antag att vi vill färgsätta var 4:e rad i ett större cellområde och samtidigt färgsätta varannan kolumn.

Enklast är att använda sig av två villkor enligt följande formler:

  • För rader: =REST(RAD();4)=0
     
  • För kolumner: =REST(KOLUMN();2)=0

RAD/KOLUMN-funktionen returnerar den aktuella radens nummer och REST-funktionen utvärderar divisionen mellan rad-/kolumnnummer och divisorn. Om ingen rest förekommer så formateras rad/kolumn, dvs villkoret är sant.

Villkorsstyrd intervallformatering
Här visas hur vi kan formatera celler utifrån intervallvilllkor.

Följande förhållandena ska visas i tabellen:

  • Värden mellan 0 - 100 färgmarkeras med röd.
     
  • Värden mellan 101 - 150 färgmarkeras med gul.
     
  • Värden mellan 151 - 200 färgmarkeras med blå.
     
  • Värden mellan 201 - 300 färgmarkeras med gul.

Till sitt yttre kan vi uppfatta detta som fyra villkor men ser vi till de önskade färgmarkeringarna är det faktiskt 3 villkor.

Följande formler löser det upplevda problemet:

  • Intervall 0 - 100 : =OCH(A1>=0;A1<=100)
     
  • Intervall 101 - 150 eller intervall 201 - 300:
    =ELLER(OCH(A1>=101;A1<=150);OCH(A1>=201;A1<=300))
     
  • Intervall 151 - 200: =OCH(A1>=151;A1<=200)

Cellen A1 utgör den första cellen för villkoren.

Minsta positiva värdet
Här ska vi titta närmare på den situation där vi vill färgmarkera det minsta positiva värdet i en lista.

Initialt är det enkelt men problem uppstår när vi har tomma celler och/eller celler med värden mindre än eller lika med 0 i en lista.

Har vi en lista där samtliga värden överstiger 0 och inga tomma celler förekommer kan vi använda oss av följande enklare formel:

  • =A1=MIN($A$1:$A$8)

Har vi en lista med negativa värden, 0 och tomma celler måste en annan lösning till.

En lösning är att använda sig av följande formel:

  • =A1=STÖRSTA($A$1:$A$8;ANTAL.OM($A$1:$A$8;">0"))

Därmed kan vi få en lista utan färgmarkering när listan är tom.

Vi kan också använda oss av en matrisformel. Gör vi det ska den inte matas in som en matrisformel utan som en vanlig formel:

  • =A1=MAX(OM($A$1:$A$8>0;$A$1:$A$8))

Det bör påpekas att denna formel kan ge oväntat utfall om det inte finns något positivt värde i listan.

En annan (bättre) matrisformel är:

  • =A1=PASSA(OM($A$1:$A$8>0;$A$1:$A$8);$A$1:$A$8;0)

Vill vi färgmarkera både minsta och största positiva värdet i en lista kan vi använda oss av följande lösning:

  • Minsta: =A1=MIN(OM($A$1:$A$8>0;$A$1:$A$8))
     
  • Största: =A1=MAX(OM($A$1:$A$8>0;$A$1:$A$8))

Noterbart är att vi omedelbart vid inmatning kan få oväntade utfall - Det förklaras bäst av att XL behöver ha litet tid på sig för att utföra arbetet på önskvärt sätt!

Jämföra två listor
Antag att vi har två listor vilka vi vill jämföra med varandra.

Villkoret vid jämförelsen är att markera de poster i Lista 2 som ej finns i Lista 1.

Cellområdet för Lista 1, exklusive rubrik, namnges till "Lista1" och och villkoret anges för Lista 2 exklusive rubrik:

  • =ÄRSAKNAD(PASSA(B2;Lista1;0))

Om vi istället vill markera de poster i Lista 2 som finns i Lista 1, dvs de poster som förekommer i båda listorna, används följande villkor:

  • =PASSA(B2;Lista1;0)

Markera cellområde vid inmatning, redigering och borttag
I detta tips visas hur vi kan markera ett önskat cellområde mha datavalidering vid inmatning, redigering (F2 och F9-tangenten) och vid borttagning av data.

I det första exemplet visas hur ett avgränsat cellområde i en rad markeras (B7:E7) efter det att data har skrivits in i cellen G7. Bilden nedan visar exemplet i sin helhet:

Noterbart är att cellområdet markeras när data hanteras i raden oavsett i vilken cell det sker i.

Följande villkorsstyrda formatering används i cellområdet B2:E11:

  • =RAD()=CELL("rad")

I det andra exemplet visas hur ett avgränsat cellområde i en kolumn markeras (C13:C22) efter det att data har matats in i cellen C25, vilket följande bild visar:

Noterbart är att cellområdet markeras när data hanteras i kolumnen oavsett i vilken cell det sker i.

Följande villkorsstyrda formatering används i cellområdet B13:E22:

  • =KOLUMN()=CELL("kol")

Flera villkor
I detta tips demonstreras hur vi kan dels referera till ett villkorsområde (cellområde) och dels använda oss av flera villkorsvärden för ett villkor.

Följande bild visar exemplet i sin helhet

Följande villkor används:

  • =ELLER(C2="AA";C2="BB")=SANT
     
  • =C2="CC"=SANT
     
  • =C2="EE"=SANT

Vilka celler har formler?
I detta lilla tips visas hur vi, utan programmering, kan identifiera vilka celler som har formler i sig.

Exemplet i sin helhet visas i följande bild:

Steg 1:

Här skapar vi ett namn, såsom HarFormel, och låter det referera till ett Excel4-makro på följande sätt:

  • =HÄMTA.CELL(48;INDIREKT(ADRESS(RAD();KOLUMN())))

Steg 2:

I detta steg skapar vi det villkorsstyrda formatet och refererar till följande formel:

  • Formeln är: =HarFormel

Skapa färgmarkeringar vid inmatning
Detta tips kan komma till sin fördel när vi arbetar med formulär. Antag att vi vill underlätta för användarna med att ange uppgifter i vissa celler. Ett sätt är att färgmarkera de celler som ska ifyllas.

Bilden nedan visar ett exempel där två villkor är uppfyllda:

De villkor som ska uppfyllas för att cellerna ska blir färgmarkerade är att värdet i cell A2 = Man och en eller flera celler ej är tomma.

Följande formel styr formateringen:

  • =OCH($A$2="Man";ANTALV($A$2;$C$2;$A$5;$C$5)>0)

Markera närmaste talet eller talen
Här demonstreras ett sätt att hitta endera det sökta värdet eller det närmast värdet / de närmaste värdena i en lista. Listan i sig kan vara mer omfattande än vad exemplet visar.

Exemplet i sin helhet visas i följande bild:

Följande formel ligger till grund för villkoret:

  • =ABS(A2-$B$2)=MIN(ABS($A$2:$A$11-$B$2))
Upp
Senast uppdaterad: 2005-10-05 © Dennis Wallentin