Nr 100 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))
=SUMMA(INDIREKT("A1:A"&B1))
Nr 99 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";""))>0Nr 98 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%)
Nr 97 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
Nr 96 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)
Nr 95 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))Nr 94 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))}
Nr 93 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*C2S: Enklast är att styra händelseförloppet med PRODUKTSUMMA: =PRODUKTSUMMA(((A3=A1))*(B1*C1)+((A3=A2)*(B2*C2))+((A3=A3)*(B3*C3)))
Nr 92 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.
Nr 91 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.
Nr 90 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)
Nr 89 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.
Nr 88 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))
Nr 87 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)
Nr 86 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)
Nr 85 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))Nr 84 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))}Nr 83 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)))
Nr 82 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).
Nr 81 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))Nr 80 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))}
Nr 79 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))
Nr 78 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)}
Nr 77 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)
Nr 76 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))
Nr 75 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.
Nr 74 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
Nr 73 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)}
Nr 72 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.Nr 71 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.
Nr 70 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%)))
Nr 69 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)
Nr 68 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))}
Fler formler och tips om det hittas på sidan: Unika VärdenNr 67 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)) I tipset Beräkna vartannat, vart tredje tal... visas en annan lösning.
Nr 66 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))
Nr 65 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)Nr 64 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)))}
Nr 63 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))Nr 62 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")Nr 61 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))}Nr 60 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)
Nr 59 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)Nr 58 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))}
PRODUKTSUMMA((KOD(A2:A9)=38)*(FÖRSKJUTNING(A2:A9;-1;1)=SANT))
Nr 57 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))
Nr 56 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;""))}
{=MIN(OM(A1:A5;B1:B5;""))}
Nr 55 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))))}Nr 54 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)))}
Nr 53 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)
Nr 52 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)))}
Se också Unika värden
Nr 51 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))
Nr 50 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))))}
Nr 49 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())
Få fram värdet: =INDIREKT(ADRESS(RAD()-1;KOLUMN()))
Nr 48 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)
Nr 47 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))
Nr 46 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)
Nr 45 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"}))
Nr 44 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)
Nr 43 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)))}Nr 42 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.
Nr 41 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))
Nr 40 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;""))
Nr 39 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.
Nr 38 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)
* KvantitetS: =OM(Slutdatum-StartDatum<30;Tariffpris1;Tariffpris2)*Kvantitet
Nr 37 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)
Nr 36 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!
Nr 35 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!
Nr 34 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))
Nr 33 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)="*")))
Nr 32 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)))}
Nr 31 F: Hjälp! Jag behöver omvandla negativa tal till positiva. S: Pröva följande formel: =OM(A1<ABS(A1);ABS(A1);) Nr 30 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))}Nr 29 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)}
Nr 28 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")
Nr 27 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)Nr 26 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.
Nr 25 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.
Nr 24 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))} Nr 23 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) Nr 22 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))}
Nr 21 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)))}Nr 20 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.
Se också fråga 2 nedan.Nr 19 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).Nr 18 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)))}Nr 17 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.Nr 16 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) Nr 15 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)) Nr 14 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))}Nr 13 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")
Se också Villkorsstyrd beräkning av antal.Nr 12 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")).Nr 11 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) Nr 10 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))))Nr 9 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.Nr 8 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))}Nr 7 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%Nr 6 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;""))Nr 5 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". Nr 4 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)Nr 3 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. Nr 2 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").Nr 1 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.