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 cellskuggningar - 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.
Detta upplägg kan även användas vid framtagning av en helgdagslista för en månadskalender.
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 villkorsstyrd 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
Länkar till tips som berör detta:
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))