startsida < företaget < organis. < datorer < frågor
 
Kalkylering 1

 
  Frågor och svar
Allmänt
Tabeller
Kalkylering 1
Kalkylering 2
Text och format
Tid och datum
Tekniska problem
   
  Innehåll
expowera

 

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

F: Hur ska jag göra för att styra antal rader summering ska ske för i en kolumn?

S: Antag att din tabell finns i A-kolumnen och antalr ader anges i cell B1 så kontrolleras antal rader mha en av följande två formler:

=SUMMA(FÖRSKJUTNING(A1;0;0;B1;1))
eller
=SUMMA(INDIREKT("A1:A"&B1))


F: I en tabell vill jag färgmarkera samtliga poster som har beteckningen "AB" i sig.

S: Använd följande formel för villkorsstyrd formatering:
=LÄNGD(A4)-LÄNGD(BYT.UT(BYT.UT(A4;"AB";"");"ab";""))>0


F: Jag håller på och skapar ett avgiftssystem. En avgift är om 10 % av grundbeloppet och uppgår maximalt till 1000 kr. Hur gör jag för att styra utfallet?

S: Enklast är att använda sig av en OM-sats:
=OM(A1*10%>1000;1000;A1*10%)


F: Vill formatera de celler som inte har heltal i sig mha av villkorsstyrd formatering?

S: Formeln nedan ger lösningen och där cellen D2 är den första cellen för området:
=HELTAL(D2)<>D2


F: Hur avrundar man till närmaste 5-tal?

S: Jo, med följande formel:
=AVRUNDA(A1/5;0)*5

Finns tillgång till tilläggsverktyget Analysis ToolPak kan fölajnde funktion användas:
=MAVRUNDA(A1;5)


F: Vad är enklaste sättet att få fram det mest frekventa värdet i en lista och hur erhålls antal gånger det värdet förekommer?

S: Följande formler ger dig vägledning:

Mest frekventa värde:
=TYPVÄRDE(A1:A100)

Antal gånger:
=ANTAL.OM(A1:A100;TYPVÄRDE(A1:A100))


F: Jag behöver hitta det minsta värdet i en lista men värdet måste vara större än 0?

S: Jo, det kan ske med en matrisformel:
{=MIN(OM(B2:B1000<>0;B2:B1000))}


F: Hur ska jag göra för att lösa följande kalkylscenario:

Om A3=A1 så B1*C1 +
Om A3=A2 så B2*C2 +
Om A3=A3 så B3*C2

S: Enklast är att styra händelseförloppet med PRODUKTSUMMA:
=PRODUKTSUMMA(((A3=A1))*(B1*C1)+((A3=A2)*(B2*C2))+
((A3=A3)*(B3*C3)))


F: Om jag har ett belopp på 10.000 år 1, en årlig ränta på 10 % och en period om 10 år - Hur stort är beloppet efter 10 år?

S: För att beräkna detta utgår vi ifrån "ränta-på-ränta"-principen. XL saknar funktionen för det varför vi får skapa den själva:
=Belopp x (1+Räntesats)Antal Perioder. I ditt exempel så blir det
 =10000*(1+0,1)^10 --> 25937,42 Kr.


F: Jag vill summera värden i en kolumn men endast för de rader där ett värde finns i motsvarande rad i en annan kolumn - Går det?

S: Hm, det krävs en "enklare" matrisformel för att tillgodose detta:
=PRODUKTSUMMA(A1:A5*ÄRTAL(B1:B5))

Intervallet A1:A5 utgör summaområdet och B1:B5 villkorsområdet där evaluering sker mha funktionen ÄRTAL.


F: Hur gör jag för att i en SUMMA.OM-formel referera till en cell som håller villkoret?

S:Jo, genom att skapa exempelvis följande formel:
=SUMMA.OM(A1:A10;"<"&B1;A1:A10)


F: Jag behöver, på ett enkelt sätt, summera varannan kolumns värde, såsom A1+C1. Kolumnområdet omfattar A till AM.

S: Följande matrisformel löser problemet ifråga:
{=SUMMA((REST(KOLUMN(A1:AM1);2)=1)*A1:AM1)}

Värdet 1 ersätts med 0 i formeln om summering ska ske med start i B1.


F: Varje dag infogar jag en ny rad i en sammanställning. Det sker på den rad där summeringen sker. Det gör att min summa-formel flyttas ned en rad men att den följaktligen inte inkluderar den infogade raden - Har du någon bra lösning?

S: En möjlig lösning är att skapa följande formel:
=SUMMA(A1:FÖRSKJUTNING(A60;-1;1))


F: Tack för svaret på föregående fråga (se fråga 86 nedan). Hur kan jag beräkna medelvärdet med samma förutsättningar?
S: Jo, det kan ske med följande formel:
=PRODUKTSUMMA(ÄRTAL(A1:A1000)*B1:B1000)/ANTAL(A1:A1000)


F: För att summera i en kolumn vill jag att användarna först ska ha skrivit in ett värde i en annan kolumn på samma rad. Hur kan en lösning se ut?

S: Antag att det är A- och B-kolumnen det gäller och att summering av värden ska ske för B-kolumnen:
=PRODUKTSUMMA(ÄRTAL(A1:A1000)*B1:B1000)


F: Jag vill få fram både antal tal och summan av dessa i ett visst intervall, t ex mellan 5 och 10?

S: Det finns (som vanligt) några alternativ, varav dessa är enklast:

För att få fram antal tal:
=PRODUKTSUMMA((A1:A20>=5)*(A1:A20<=10))

För att få fram summan av talen:
=PRODUKTSUMMA((A1:A20>=5)*(A1:A20<=10)*(A1:A20))


F: Hur gör jag för att få fram antal tomma respektive antal ifyllda rader i ett området. Det är bara textvärden i området.

S: Förutsatt att det endast är textvärden i området:

Följande matrisformel ger antal tomma rader:
{=SUMMA(N(FREKVENS(OM(A1:C10>"";RAD(A1:C10));
RAD(A1:C10))=0))-1}

Följande matrisformel ger antal ifyllda rader:
{=SUMMA(N(FREKVENS(OM(A1:C10>"";RAD(A1:C10));RAD
(A1:C10))>0))}


F: Jag behöver räkna antal unika tal i en serie - Hur ska jag göra?

S: Titta närmare på Unika värden och om du avser värden som endast förekommer en gång i listan så kan följande formel ge dig en lösning:
=PRODUKTSUMMA(1/(ANTAL.OM(A1:A4;A1:A4)))


F: Är det möjligt att erhålla antal decimaler ett tal har, t ex 12345,123456

S: Matrisformeln nedan ger korrekt antal decimaler, 6.
{=OM(A1=HELTAL(A1);0;PASSA(A1;AVRUNDA(A1;RAD($1:$15));0))}

Då XL endast kan hantera upp till 15 decimaler är det meningslöst att ha högre tal i formeln ($15).


F: Kan jag använda mig av två villkor i en SUMMA.OM-formel?

S: Följande formler (utan inbördes rangordning) ger dig det önskade resultatet:

Alternativ 1 - Matrisformel:
{=SUMMA(OM(A1:A1000=10;1;0)*OM(B1:B1000>2;1;0)*C1:C1000)}

Alternativ 2 - Mer kompakt matrisformel:
{=SUMMA((A1:A1000=10)*(B1:B1000>2)*C1:C1000)}

Alternativ 3 - En ytterligare modifierad matrisformel:
=PRODUKTSUMMA((A1:A1000=10)*(B1:B1000>2)*(C1:C1000))


F: Jag vill kunna summera tal i en rad men bara i varannan kolumn - går det?

S: För att räkna jämna tal i en rad:
{=SUMMA(OM(REST(KOLUMN(A1:D1);2)=0;A1:D1;0))}

För att räkna udda tal i en rad:
{=SUMMA(OM(REST(KOLUMN(A2:D2);2)=1;A2:D2;0))}

För att räkna jämna tal i en kolumn:
{=SUMMA(OM(REST(RAD(A1:A4);2)=1;A1:A4;0))}

För att räkna udda tal i en kolumn:
{=SUMMA(OM(REST(RAD(A1:A4);2)=0;A1:A4;0))}


F: Hur ska jag göra för att erhålla antal värden i en rad som är större än värdena i ovanstående rad?

S: Funktionen PRODUKTSUMMA är här ganska användbar:
=PRODUKTSUMMA(ÄRTAL(A2:D2)*ÄRTAL(A3:D3)*(A3:D3>A2:D2))


F: Hur ska jag göra för att räkna antal poster som faller inom en månad?

S: Använd följande matrisformel som antar att cellområdet utgörs av A1:A300 och att den önskade månaden finns angiven i cell E1:
{=SUMMA((MÅNAD(A1:A300)=E1)*1)}


F: Vilken formel ger mig möjlighet att räkna antal rader som har samma innehåll i två kolumner?

S: Antag att du vill räkna antal rader med samma innehåll för cellområdet A1:B6. Formeln blir då:
=PRODUKTSUMMA((A1:A5=B1:B5)*1)


F: Varje vecka uppdaterar jag en lista. Jag har en formel som rangordnar talen. Den formel måste jag nu ändra på varje vecka med sista cellreferensen. Är det möjligt att automatisera?

S: OK, antag att din lista börjar i cell A4:
=RANG(A4;FÖRSKJUTNING($A$4;0;0;ANTAL($A$4:$A$1000);1))


F: Jag har en lista med kundnummer, fakturakod, datum och belopp. Jag behöver identifiera det senaste fakturadatumet för olika kunder.

S: Det kan ske bäst med en matrisformel:
{=MAX((A2:A1000=E2)*(B2:B1000=F2)*C2:C1000)}

Där E2 innehåller kundnummer och F2 representerar fakturakod. Cellområdet C2:C1000 utgör datum för listan.
Det erhållna värdet får datumformateras.


F: Hur gör jag för att omvandla en matris om 4 rader och 1 kolumn till 4 kolumner och 1 kolumn?

S: Enklast är att kopiera cellområdet och välja kommandot Redigera | Klistra in special. I dialogrutan bockas alternativet "Transponera" för och avslutas.


F: Jag har en lång lista som kontinuerligt förändras. Den innehåller många blankrader men jag behöver alltid veta det första värdet i listan oavsett i vilken cell det finns i.

S: Hm, följande formel löser nog problemet:
=FÖRSKJUTNING(A1;MIN(OM(A1:A10000<>"";RAD(A1:A10000)-1));0)

Det kan också lösa mha en matrisformel:
{=INDEX(A1:A10000;MINSTA(OM(LÄNGD(RENSA(A1:A10000))<>
0;RAD(A1:A10000);"");1);1)}


F: Jag har en loggbok för flygtid med datum, vilken flygplanstyp och antal timmar. Jag skulle vilja summera ihop de senaste 30 dagarna flygtimmar för en viss flygtyp.

S: En lösning är att använda sig av en matrisformel:
{=SUMMA(1*(IDAG()-A1:A2500<E2)*(B1:B2500=F2)*(C1:C2500))}

Där cellen E2 innehåller t ex 30 (dagar) och F2 innehåller flygplanstyp.
I A-kolumnen finns datum, B-kolumnen flygplanstyp och i C-kolumnen antal flygtimmar.


F: Jag behöver tydliggöra vilka poster som faller inom ett visst datumintervall, t ex 2001-01-01 och 2001-01-12. Slutdatumet återfinns alltid i en cell. Jag har försökt med villkorsstyrd formatering men utan framgång.

S: Villkoret ska uppfattas som >=2001-01-01 och <=2001-01-12.

Markera cellområdet med datumen. Välj villkorsstyrd formatering och ange följande formel:
=OCH($A1<=$C$1;$A1>DATUM(MÅNAD($C$1);1;ÅR($C$1)))

Där cell C1 innehåller datumet 2001-01-12 och cell A1 utgör den första cellen i området med datumangivelser.


F: Jag har följande villkor som ska utvärderas i en formel:

- Om H27 är tom så ska inget värde visas.
- Om H27 har ett värde som är mindre eller lika med 450 så ska detta värde visas.
- Om dessa två villkor är falska så ska en multiplikation mellan H27 och 20% ske.

S: Med följande nästlade OM-funktion erhålls önskat utfall:
=OM(H27="";"";OM(SUMMA(H27*20%)<=450;450;SUMMA(H27*20%)))


F: Jag importerar stora datamängder till XL. Datumformatet utgörs av 20010401. Jag vill kunna summera varje månad och år!

S: Det kan ske mha följande formel:
=PRODUKTSUMMA((VÄNSTER(A1:A7;6)=C2)*B1:B7)

C2 innehåller här textvärdet, t ex 200104. Enklast att skapa textvärdet är att sätta en apostrof framför värdet vid inmatning.

Finns det riktiga datum så kan följande formel användas:
=PRODUKTSUMMA((ÅR(A1:A7)=2001)*(MÅNAD(A1:A7)=4);B1:B7)


F: Jag vill erhålla antal unika tal i en osorterad serie - hur kan det lösas bäst?

S: Hm, följande matrisformel ger en lösning:
{=SUMMA(ANTAL.OM(A1:A7;A1:A7)/OM(ICKE(ANTAL.OM

(A1:A7;A1:A7));1;ANTAL.OM(A1:A7;A1:A7))^2)}

Eller så kan följande kortare matrisformel användas:
{=SUMMA(N(FREKVENS(A1:A7;A1:A7)>0))}


F: Hur ska jag göra för att summera var 3:e tal i en lista?

S: Du kan använda följande formel:
=PRODUKTSUMMA(A1:A9*(REST(RAD(A1:A9)-RAD(A1)+1;3)=0))


F: Jag vill erhålla antalet tal som finns i två grupperingar.

S: Följande matrisformel demonstrerar en möjlig lösning:
=ANTAL(PASSA($B$11:$D$11;($B$4:$D$4);FALSKT))


F: I en kurs görs ett par moment. Samtliga moment poängsätts efter samma mall. Den totala summan per student ska innefatta alla moment utom de två moment som har lägst poäng.

S: Följande exempel visar hur man löser problemet, där varje moment finns representerad i en kolumn:
=SUMMA(B2:E2)-MINSTA(B2:E2;1)-MINSTA(B2:E2;2)


F: Jag vill summera värden i B-kolumnen men endast om de faller inom ett intervall som finns i A-kolumnen. Hur gör jag?

S: Det finns ett flertal exempel på intervallsummering på webbplatsen men generellt gäller:
{=SUMMA(OM(A1:A10>=5,IF(A1:A10<=10,B1:B10)))}


F: I en kolumn har jag unika värden, dvs de förekommer endast en gång i listan. Jag behöver ange varje värde 4 gånger efter varandra i en annan kolumn. Är det möjligt?

S: Nästan allt är möjligt i XL!

Ange följande formel och kopiera den nedåt i kolumnen:
=INDIREKT("A"&HELTAL((RAD()-1)/4+1))


F: Jag vill hämta ett värde från ett annat blad men vet inte vad det arbetsbladet heter från tid till annan. Har du några förslag?

S: Börja med att ange bladets namn i t excell A1. Ange sedan följande formel:
=INDIREKT("'"&A1&"'!B1")


F: Jag vill få fram om värden i cellområde 1 förekommer i cellområde 2. Om ja, så ska SANT returneras annars FALSKT.

S: Försök med följande matrisformel:
{=ELLER(ANTAL.OM(B1:C3;A1:A3))}


F: I en kolumn har jag värdena 1 och 2. I de tre intilliggande kolumnerna har jag värden som jag vill beräkna medelvärdet för men endast för de värden som har värdet 1 i den första kolumnen.

S: Om vi antar att A-kolumnen har värdena 1 och 2 och att data finns i kolumnerna B till D.
=(SUMMA.OM(A:A;1;B:B)+SUMMA.OM(A:A;1;C:C)+
SUMMA.OM(A:A;1;D:D))/ANTAL.OM(A:A;1)


F: I en kolumn har jag datum och i en intilliggande kolumn värden. Jag vill summera ihop de värden där datumet är mindre än för ett år sedan.

S: Det går att lösa med följande formel:
=SUMMA.OM(A1:A300;">"&DATUM(ÅR(IDAG())-1;MÅNAD
(IDAG());DAG(IDAG()));B1:B300)


F: Jag skulle vilja räkna alla &-tecken som finns i kolumn A om ordet SANT finns på raden ovanför i kolumn B.

S: Tecknet & representeras av talet 38. Utifrån denna uppgift kan vi lösa det på följande sätt:
{=SUMMA((KOD(A2:A9)=38)*(FÖRSKJUTNING(A2:A9;-1;1)=SANT))}
eller
PRODUKTSUMMA((KOD(A2:A9)=38)*(FÖRSKJUTNING
(A2:A9;-1;1)=SANT))


F: Jag skulle vilja räkna alla ¤-tecken som finns i kolumn A om ordet SANT finns på samma rad i kolumn B.

S: I min dators teckenuppsättning representeras ¤-tecknet av talet 164. Givet denna uppgift kan vi lösa det på följande sätt:

Med en matrisformel:
{=SUMMA((KOD(A1:A9)=164)*(B1:B9=SANT))}

Finns det tomma celler i cellområdet måste det beaktas på följande sätt:
{=SUMMA(((A1:A9)=TECKENKOD(164))*(B1:B9=SANT))}

Alternativt kan följande formler användas:
=PRODUKTSUMMA((KOD(A1:A9)=164)*(B1:B9=SANT))

=PRODUKTSUMMA(((A1:A9)=TECKENKOD(164))*(B1:B9=SANT))


F: Hur ska jag kunna erhålla största respektive minsta värdet i en kolumn utifrån villkoret att värdena i en annan kolumn är ">0"?

S: Det går att lösa med bl a följande matrisformler:
{=MAX(OM(A1:A5;B1:B5;""))}
och
{=MIN(OM(A1:A5;B1:B5;""))}


F: Jag använder mig av följande formel för att räkna fram summan utifrån ett villkor:
=PRODUKTSUMMA((B2:B3000=1)*(A2:A3000))

Jag vill slippa ange den sista cellreferensen (A- och B3000) i formeln - istället vill jag att den alltid är den sist ifyllda cellen i intervallet 2:3000!

S: Hm, det går att lösa men formeln blir inte särskild "vacker":
=PRODUKTSUMMA((INDIREKT("B2:B"&MAX((A2:A3000<>"")
*RAD(2:3000)))=1)*(INDIREKT("A2:A"&MAX((A2:A300<>"")*RAD(2:3000)))))

Eller som en matrisformel:
{=SUMMA.OM(INDIREKT("B2:B"&MAX((A2:A3000<>"")*RAD(2:3000)));
1;INDIREKT("A2:A"&MAX((A2:A3000<>"")*RAD(2:3000))))}


F: Jag har en lista, vilken omfattar området A6:A200 och B6:B200. Jag använder mig av funktionen ANTAL.OM för att erhålla antal poster som överensstämmer med ett villkor i cell D1 för området A6:A200. Nu vill jag räkna antal mth till detta villkor och ett villkor för det andra området, B6:B200. I det andra villkoret vill jag kunna ange t ex begynnelsebokstaven och XL ska leta fram alla poster som börjar på denna bokstav. Går det att lösa?

S: OK, vi antar att det andra villkoret finns i cell E1 och att cellområdet B6:B200 omfattar endast textvärden. Följande matrisformel löser förhoppningsvis problemet:
{=SUMMA((A6:A200=D1)*(OM(ÄRTAL(SÖK(E1&"*";B6:B200));1;0)))}


F: I en rad behöver jag leta upp det första positiva tal.

S: Följande formel ger dig lösningen på problemet:
=INDEX(A1:D1;PASSA(0;A1:D1;1)+1)


F: Hur ska göra för att räkna fram antal unika artikelnummer som finns i en lång lista?

S: Ett sätt är att använda följande matrisformel:
{=SUMMA(OM(ANTAL.OM(Lista;Lista)<>0;1/ANTAL.OM(Lista;Lista)))}


F: I en arbetsblad har jag två listor. Jag vill kunna få fram antalet värden som återfinns i båda listorna!

S: Antag att listorna omfattar cellområdena A2:A50 och B2:B50. För att erhålla antal värden kan följande matrisformel användas:
{=SUMMA(N(A2:A50=B2:B50))}
eller
=PRODUKTSUMMA(N(A1:A200=B1:B200))


F: Jag vill räkna antalet värden som har formatet "123456AA" i en lista, dvs de 6 första tecknen ska vara tal och de 2 sista tecknen text.

S: Här får vi använda oss av en matrisformel:
{=SUMMA((ÄRTAL(VÄNSTER(Lista;6)*1))*N(ÄRTEXT(HÖGER(Lista;2))))}


F: Jag vill kunna få fram celladressen ovan en annan cell samt även kunna erhålla värdet i den cellen.

S: Det finns två generella formler som kan användas här:
Få fram celladressen:
=ADRESS(RAD()-1;KOLUMN())
eller
Få fram värdet:
=INDIREKT(ADRESS(RAD()-1;KOLUMN()))


F: I en kolumn har jag datum och i en annan värden. Värdena vill jag löpande summera utifrån kriteriet mindre än dagens datum!

S: Hm, antag att datumen finns i kolumn A och värdena i kolumn B. Följande formel summerar löpande värdena i B-kolumnen:
=SUMMA.OM(A:A;"<"&IDAG();B:B)


F: Jag har ett villkor (VECKODAG=1) för en datumkolumn och vill att XL summerar i en annan kolumn. I den sistnämnda kolumnen förekommer såväl blanka celler som celler med alfa-numeriska tecken. Har du någon bra lösning?

S: Följande formel visar på en möjlig lösning:
=PRODUKTSUMMA((VECKODAG(A2:A14;2)=1)*ÄRTAL(B2:B14))


F: I ett cellområde vill jag att man ska ange värden i 00-formatet och mellan 0 - 99.

S: Formatera cellområdet med ett eget talformat, 00. Använd därefter dataverifiering med följande formel:
=OCH(ÄRTAL(F5);LÄNGD(TEXT(D5;"00"))=2)


F: I en kolumn vill jag räkna antal gånger bokstäverna "A" och "B" förekommer men bara under förutsättning att värdet 10 finns i en annan kolumn - Hur gör jag?

S: Använd PRODUKTSUMMA-funktionen enligt följande:
=PRODUKTSUMMA((A2:A20=10)*(B2:B20={"A";"B"}))


F: I en kolumn har jag ett flertal celler med datumangivelser. Om dessa datums månad överensstämmer med dagens ska värdet "Sant" returneras annars "Falskt".

S: Det finns några alternativ till lösning att tillgå men den enklaste är:
=MÅNAD(IDAG())=MÅNAD(A1)


F: I en formel behöver jag få veta vilket det sist inmatade värdet är i en kolumn. I vissa kolumner finns det alltid värden medan i andra förekommer det också tomma rader.

S: Många alternativa lösningar finns att tillgå i tipset: Hitta sista värdet i kolumnen/raden.

Kolumner utan tomma rader:
=FÖRSKJUTNING(Blad1!$A$1;ANTALV(Blad1!$A:$A)-1;0)

Kolumner med tomma rader:
{=INDEX($A:$A;MAX((LÄNGD(RENSA($A$1:$A$10))>0)*RAD(A1:A10)))}


F: Jag har två kolumner med data, A och B. I A-kolumnen förekommer det tomma celler. Jag vill löpande kunna ändra på villkoret. Det jag vill erhålla är antalet poster som överensstämmer med villkoret. Villkoret är alltid =<.

S: Följande matrisformel hjälper dig:
{=SUMMA((A2:A8<>"")*(B2:B8<=D3))}

Där cellen D3 innehåller talet.


F: Jag har en cell, Betalningsdag, som idag innehåller formeln NU()+30 men den tar inte hänsyn till om förfallodag blir en lördag eller söndag.

S: Mha funktionen VECKODAG kan problemet lösas enligt följande:
=OM(VECKODAG(NU()+30)=1;NU()+28; OM(VECKODAG(NU()+30)=1;NU()+29; NU()+30))


F: Jag vill att cell A2 antingen ska visa 25 eller 30. För att visa 30 ska värdet i cell A1 finnas i intervallet 11 och 14,5 och för att visa 25 ska värdet i cell A1 finnas i intervallet 14,6 och 16.

S: Följande formel löser ditt problem:
=OM(OCH(A1>=11;A1<=14,5);30;OM(OCH(A1>=14,6;A1<=16);25;""))


F:Jag behöver stämma av att värdena i en kolumn har samma värde, t ex värdet 1. Då jag har ca 12000 rader så går det långsamt...

S: Försök med formeln:
=OCH(ABS(C1-MEDEL(C1:C12000))<0,0001;ANTAL(C1:C12000)
=RADER(C1:C12000))

Om alla värdena är identiska erhålls värdet SANT annars FALSKT.


F: Jag har ett debiteringsunderlag där om antalet dagar överstiger 30 mellan två datum så ska ett pris gälla annars ett annat. Följande data finns tillgänglig:

* Datum (Start- & Slutdatum)
* Pris för < 30 dagar (Tariffpris 1)
* Pris för > 30 dagar (Tariffpris 2)
* Kvantitet

S:
=OM(Slutdatum-StartDatum<30;Tariffpris1;Tariffpris2)*Kvantitet


F: Jag vill kunna erhålla antingen minipriset eller, om det ger högre pris, Pris/m3*antal m3. Hur ska jag lösa det med en formel?

S: Följande formel löser situationen:
=MAX(Minipris;Pris/m3*Antal m3)


F: Jag vill i en kolumn leta reda på ett visst värde och hämta värdet i cellen under det värdet.

S: Hm, antag att vi har alla värdena i kolumn A och uppslagsvärdet i cell B2. Följande formeln hämtar värdet under det värde som anges i cell B2:
=FÖRSKJUTNING($A$1;PASSA(B1;$A:$A;0);0)

Återfinns inte uppslagsvärdet i kolumnen returneras #Saknas!


F: Jag har ett flertal kolumner som innehåller artikelnummer. Vad jag önskar göra är att räkna antalet av olika artikelserier - Går det?

S: OK, antag att vi har följande artiklar i cellområdet A1:A3: B1231, B2231, C1134. Följande formel ger antalet artiklar med värdet 2 i den 3:e positionen:
=ANTAL.OM(A1:A3;"??2*")

Rätt svar: 2!


F: Hur kan jag summera värdena i varannan kolumn, t ex för kolumnområdet A:O?

S: Jo, mha följande "enkla" formel:
=PRODUKTSUMMA((REST(KOLUMN(A:O);2))*(A2:O2))

Se också Beräkna vartannat, vart tredje tal...


F: Jag har en del värden som innehåller asterisk-tecknet (*) efter sig, såsom 15*. Hur får jag bort asterisk-tecknet?

S: För att rensa bort tecknet kan följande formel användas:
=TEXTNUM(VÄNSTER(A1;LÄNGD(A1)-(HÖGER(A1;1)="*")))


F: Jag har ett cellområde som innehåller textvärden där varje tal föregås av en bokstav (A), t ex A20. Hur ska jag göra för att summera värdena?

S: Hm, det krävs en matrisformel enligt följande uppställning:
{=SUMMA(TEXTNUM(OM(VÄNSTER(A8:A12)="A";
ERSÄTT(A8:A12;1;1;"");0)))}


F: Hjälp! Jag behöver omvandla negativa tal till positiva.

S: Pröva följande formel:
=OM(A1<ABS(A1);ABS(A1);)


F: Jag har en lång lista med datum där jag vill räkna antal rader för varje år och månad.

S: Jo då, det kan lösas på följande sätt:

Alternativ I:
=PRODUKTSUMMA(N(ÅR(A2:A200)=1999))
=PRODUKTSUMMA(N(MÅNAD(A2:A200)=2))

Alternativ II:
{=SUMMA(OM(ÅR(A2:A200)=1999;1))}
{=SUMMA(OM(MÅNAD(A2:A200)=2;1))}


F: Jag vill kunna summera per avdelning. Varje avdelning identifieras genom de tre första siffrorna, såsom 101...,201....

S: Här kan vi använda oss av en matrisformel där de tre första tecken extraheras ur sifferserierna:
{=SUMMA((VÄNSTER(A12:A15;3)="201")*B12:B15)}


F: I en kolumn har jag olika felkoder, såsom DF01 och DF08. Jag vill räkna antal förekomster för koderna men bara två om två, dvs antal DF01 och DF08.

S: Det finns två lösningar på problemet, en matrisformeln och en "vanlig":
{=SUMMA((A3:A10="DF01")+(A3:A10="DF08"))}

=SUMMA(ANTAL.OM(A3:A10;{"DF01";"DF08"}))
Klamrarna anges manuellt här.

Vill man ha antalet separerade men ändå i samma cell kan följande formel bli aktuell:
=ANTAL.OM(A3:A10;"DF01")&" "&ANTAL.OM(A3:A10;"DF08")


F: Jag vill jämföra tidsangivelser i celler med systemklockan - hur gör jag?

S: Jo, det kan gå med nedanstående formel och som fungerar med villkorsstyrd formatering:
=A1<REST(NU();1)


F: I en kolumn har jag förnamn, i en intilliggande kolumn efternamn och i en tredje kolumn årtal. Det är ca 14.000 namn och jag vill få reda på antal unika namn, dvs namn som bara förekommer en gång i tabellen.

S: Hm, följande formel löser det upplevda problemet:
=PRODUKTSUMMA((PASSA(A2:A6&TECKENKOD(13)&B2:B6;A2:A6
&TECKENKOD(13)&B2:B6;0)=RAD(A2:A6)-RAD(INDEX(A2:A6;1;1))
+1)*(C2:C6=D2))

Cellen D2 innehåller årtalet.


F: I en kolumn har jag månader, i en annan veckodagen (må - fre) och i en tredje kolumn antal körda mil. Hur kan jag få fram medelvärdet för antal körda mil på torsdagar i månad 4, dvs april?

S: Följande matrisformel genererar det önskade medelvärdet:
{=SUMMA((A2:A260="4")*(B2:B260="To")*C2:C260)/SUMMA
((A2:A260="4")*(B2:B260="To"))}

Formeln förutsätter att månadsangivelsen är i textformat, "4". Har man talvärden istället så ersätts villkoret med talet, dvs 4.


F: Hur får jag fram det lägsta positiva talet i en större datamängd?

S: Jo, det går att lösa med följande matrisformel:
{=MIN(OM(A10:D12>0;A10:D12))}


F: Jag har en cell där man ska ange cellreferenser, t ex B1:C1. På basis av angivna cellreferenser vill jag summera det valda cellområdet.

S: Här kan man använda den (fina) funktionen INDIREKT på följande sätt:
SUMMA(INDIREKT(Inmatningscell)


F: Jag vill summera radnummer, t ex rad 1 t o m rad 5 ska ge summan 15. Är det möjligt?

S: Jo, det går faktiskt att räkna fram mha av en matrisformel:
{=SUMMA(RAD(1:5))}


F: Jag vill få fram summan för hela tiotal i en serie, dvs summera 10, 20, 30 och så vidare.

S:Denna formel gäller också för större tal:
{=SUMMA(B10:B17*ÄRTAL(HITTA("0";B10:B17;1)))}


F: Jag vill få fram antal udda och jämna tal i en lista.

S: För att få fram antal udda tal kan man använda matrisformeln:
={=SUMMA(REST(E1:E50;2))}

För jämna tal kan man använda matrisformeln:
={=SUMMA(OM(REST(E1:E50;2)=0;1;0))}

För enstaka värden kan man använda sig av funktionerna ÄRJÄMN och ÄRUDDA, vilket förutsätter att Analysis Toolpak är installerat.


F: Jag vill räkna antal tomma celler i en lista som består av text - hur gör jag?

S: Det löses med formeln:
=ANTAL.TOMMA(A1:A7).

Vill man räkna antal icke tomma rader i en textlista så kan man använda formeln:
=ANTALV(A1:A7).


F: Jag vill räkna fram medelvärdet för en datamängd och där jag vill exkludera det lägsta och högsta värdena samt 0-värdet.

S: I XL finns TRIMMEDEL-funktionen med vilken man lösa problemet. Funktionen måste dock kombineras med OM-funktionen och vara en matris-formel:
{=TRIMMEDEL(OM(A1:A7=0;"";A1:A7);1-(SUMMA(N(A1:A7<>0))-2)/
SUMMA(N(A1:A7<>0)))}


F: Hur ska jag göra för att separera tal från varandra, dvs 123 ska bli tre separata cellvärden, A2=1, A3=2 och A4=3.

S: Två alternativa formler kan ge vägledning här:
=EXTEXT($A$1;B2;1)

där A1 innehåller tal och B1 den position som önskas erhållas.

{=EXTEXT($A$1;{1;2;3;4})}

där den inre matrisen innehåller alla önskade positioner.


F: Jag vill alltid referera till cell A20, oavsett om jag infogar nya rader eller ta bort rader.

S: Lösningen ligger i att använda sig av INDIREKT-funktionen som behandlar argumentet som textsträng och inte som en faktisk cellreferens.
=INDIREKT(A20)


F: Hur får jag fram antal celler vars värden överstiger genomsnittet för cellområdet?

S: Här sker lösningen med en ganska spännande(!) formel:
=ANTAL.OM(A1:A8;">"&MEDEL(A1:A8))


F: Jag har två kolumner, där den ena innehåller bokstäver och den andra kolumnen värden. Jag vill summera värdena på basis av bokstäverna samt att värdena är inom intervallet 5 - 10.

S:Mha följande matrisformel kan det lösas:
{=SUMMA(OM((A1:A10="A")*(B1:B10>=5)*(B1:B10<=10);B1:B10))}


F: Hur gör jag för att få fram antal värden i ett intervall, t ex mellan 5 och 10?

S: Funktionen ANTAL.OM ger dig lösningen:
=ANTAL.OM(A1:A100;">=5")-ANTAL.OM(A1:A100;">10")


F: Jag vill utföra summeringar, vilka ska ske fr.o.m. den rad där formeln anges till slutet av intervallet, t ex rad 2 =SUMMA(A2:A8) och rad 3 =SUMMA(A3:A8) och så vidare.

S: Hm, det kan lösas m h a INDIREKT-funktionen enligt följande:
=SUMMA(INDIREKT("A" & RAD() & ":A8")).


F: I två intill liggande kolumner har jag värden som ska subtraheras med varandra. I en kolumn intill vill jag antingen få resultatet av subtraktionen om det är större än 0 (>0) och värdet 0 om det är negativt, dvs < 0.

S: Använd MAX-funktionen i den tredje kolumnen på följande sätt:
=MAX(A1-B1;0)


F: Hur kan jag få "rullande" medeltal för en större datamängd, dvs =MEDEL(B1:B7), MEDEL(B8:B14) osv?

S: Det kan lösas i två steg:

Steg I: Skapa följande formel i t ex D1:
=MEDEL(B1:B7)

Steg 2: Skapa följande formel i D2 och kopiera den nedåt i kolumnen:
=MEDEL(INDIREKT("B"&1+RAD(D1)*7&":B"&7*(RAD(D2))))


F: Jag ska summera olika grupper av artiklar, varje grupp har unika identifierare, såsom 21, 22 osv. Dessa har dock olika textbaserade prefix framför sig.

S: Formelmässigt kan man lösa det på följande sätt:
{=SUMMA(OM(ÄRFEL(SÖK("**21";A2:A5));0;B2:B5))}

alternativ kan det också lösa mha av pivottabell.


F: Jag har räknat fram medelvärden för en lista. Nu vill jag kunna få fram det värde i listan som ligger närmast medelvärdet.

S: Jo, det går att lösa mha formler:
{=INDEX(A1:A11;PASSA(MIN(ABS(A1:A11-MEDEL(A1:A11)));
ABS(A1:A11-MEDEL(A1:A11));0))}


F: Vid försäljning om 800 tkr erhålls 10% kommission och därutöver 20 %. Jag vill kunna få fram den totala kommissionen per dag och att 20% tar vid där 10% slutar.

S: Försök med följande formel:
=MIN(SUMMA(A1:A5);800)*10%+MAX(0;SUMMA(A1:A5)-800)*20%


F: Jag vill få fram olika medeltal i en lista. Problemet är att det förekommer båda tomma celler, text och värden i listan.

S: Du kan använda följande formel:
=MEDEL(OM(ÄRTAL(A2:A6);A2:A6))
eller
=MEDEL(OM((A2:A6<20)*(ÄRTAL(A2:A6));A2:A6;""))


F: Jag vill kunna hitta det minsta positiva värdet i en tabell. Det förekommer både positiva och negativa tal i serien.

S: För att få fram det minsta positiva talet kan följande formel användas:
=MINSTA(A1:A8;(ANTAL.OM(A1:A8;"<1")+1)).

Värdet "0" beaktas här inte som ett positivt värde, därav villkoret "<1".


F: Vid import av data får jag ej önskat format på personnummer, t ex 199908172435. Hur gör jag för att få fram 990817-2435?

S: Följande formel löser problemet:
=EXTEXT(A1;3;6)&"-"&HÖGER(A1;4)


F: Jag vill kunna generera slumptal i ett intervall, t ex mellan 25 och 50.

S: Se till att Analysis Toolpak är installerat och använd funktionen SLUMP.MELLAN; t ex:
=SLUMP.MELLAN(25;50).

För att XL ska ta fram nya tal krävs att omräkning sker, vilket ordnas m h a F9-tangenten.


F: Hur får jag fram kön på basis av personnummer?

S: Tillse att du har Analysis Toolpak installerat.

Den tekniska lösningen blir då:
=OM(ÄRUDDA(EXTEXT(A2;10;1));"Man";"Kvinna").

Vänder man på villkoret så blir det:
=OM(ÄRJÄMN(EXTEXT(A2;10;1));"Kvinna";"Man").


F: Hur kan jag räkna antal värden m h t att de ska understiga ett värde i en annan cell?

S: Använd funktionen ANTAL.OM enligt följande exempel:
=ANTAL.OM(A2:A100;"<"&B2)

där cellen B2 utgör värdecellen.

Upp
Senast uppdaterad: 2005-10-05 © Dennis Wallentin