Ta bort nollor

[lebn]

Hej

Någon som vet om det går att ta bort nollor i Excel?

Jag har Office 2003 och när jag kopierar och sen klistrar in enbart värden, smyger excel in nollor i celler utan värde. Detta innebär att när jag sedan vill räkna ut ex de tre minsta värdena i en kolumn, så tar excel hänsyn till nollvärdena och ger fel resultat.

Jag vill alltså INTE DÖLJA NOLLOR,  utan på ett enkelt sätt ta bort celler med nollvärden i.

Någon som kan hjälpa mig?

Tacksam för tips.

Lebn

[Ove Söderlund]

Antag att cellområdet är C4:C8 så fungerar följande formel:

=OM(MIN(C4:C8)=0;MINSTA(C4:C8;ANTAL.OM(C4:C8;"=0")+1);MIN(C4:C8))

 

Den fetstilda, rödmarkerade texten +1 ger dig det minsta icke-noll-värdet. Väljder du +2 eller +3 så får du näst minsta resp. tedje minsta värdet.

Du får så klart ändra C4:C8 till det cellområde du vill beräkna istället.

 

Den här formeln hämtades från excel.tips.net

[lebn]

Antag att cellområdet är C4:C8 så fungerar följande formel:

=OM(MIN(C4:C8)=0;MINSTA(C4:C8;ANTAL.OM(C4:C8;"=0")+1);MIN(C4:C8))

 

Den fetstilda, rödmarkerade texten +1 ger dig det minsta icke-noll-värdet. Väljder du +2 eller +3 så får du näst minsta resp. tedje minsta värdet.

Du får så klart ändra C4:C8 till det cellområde du vill beräkna istället.

 

Den här formeln hämtades från excel.tips.ne

Tack för svar!

Men jag skrev i mitt exempel 3 minsta värdena,och då kan din formel kanske användas, men i själva verket är mitt ark stort och det handlar om minst 10 minsta värdena. Din formeln blir då enormt stor och otymplig. Så jag undrar fortfarande, finns det inget smidigare sätt att beräkna N antal minsta, men inte nollor?

Mvh

Leif

[lebn]

Följande värden i exemplet i kolumn A

A

0

5

4

3

0

1

2

etc

 

Nu vill jag beräkna summan av N antal minsta värden. Om jag för enkelhetens skull 

säger att jag vill beräkna de 3 minsta värdena, så ska jag i exemplet ovan, få fram 6 och inte 1. Nollorna ska alltså inte tas med i beräkningen. 

Jag fyller kolumnen fylls efter hand med värden, så från början har jag inte ens tre värden, och då vill jag inte få felmeddelande i formel cellen, utan jag vill att den är blank tills jag har åtminstone 3 värden (som detta exempel bygger på)

Jag kan skriva en matris formel:

 =SUMMA(MINSTA(A11:J11;{1;2;3}))

Problemet med denna formel är att har jag inte fyllt i tre värden från början, får jag #OGILTIGT! och har jag nollor i några av cellerna, så beräknar denna matris utifrån 

nollorna och minsta sammanlagda värdet blir fel. 

Jag kan istället skriva följande formel:

=OM(ANTAL(B14:Z14)<9;"";PRODUKTSUMMA(MINSTA(B14:Z14;RAD($1:$8))))

Den formel löser problemet med #OGILTIGT!  - men den räknar noll värden tyvärr,

så jag måste kolla hela arket manuellt, så att det inte finns en enda cell med nollvärde i, vilket är tidskrävande och det finns risk att fel smyger in och resultatet blir i så fall åter blir missvisande.

Så - jag hoppas detta är klargörande. 

Alltså - jag söker en formel som inte räknar nollvärden och som inte ger fel meddelande (#OGILTIGT!) när jag börjar fylla mitt ark. 

Mvh

Leif

[Monshi]

Felmeddelandet kan du lätt ta bort med OMFEL()  eller OM(ÄRFEL(..)) om det är enda problemet med formeln.

 

men för att ta bort nollor:

{ =SUMMA(MINSTA(OM(A2:A15=0;"";A2:A15);{1;2;3})) }

men värdet blir galet om du har färre än tre värden vilket du måste hantera med en av de ovan givna.

Vill du räkna summa även med färre värden så går även det att lösa.

[lebn]

Tack för svar!

Jag vet inte hur jag ska förklara mitt problem tydligare, men jag försöker igen, eftersom problemet är inte löst.

Min excel fil består av flera flikar, som är på olika sätt är länkade till det aktuella arbetsbladet/fliken. I en formel kolumn (H)  vill jag få fram summan av N antal minsta värden - men inte nollvärden.

 

De svar jag fått löser detta problem, när jag fyllt i värden i kolumn B, C, D, E, F etc.

Men innan jag fyllt dessa kolumner med värden, löser era förslag inte mitt problem.

 

Om nollvärden:

Jag har valt att dölja nollvärdena (Verktyg/Alternativ - bocka ur visa nollvärden) för

att inte en massa synliga nollor ska synas. Eftersom testet inte har utfallit med 0, (noll) utan deltagaren har inte närvarat vid första testet osv.

Dessa nollvärden, som jag får när jag kopierar och klistrar in enbart värden, beror troligen på att i det ursprungliga arket, finns det inget värde för tex. Adam vid första testet. Och jag tror att excel tolkar det som ett noll-värde, när jag sedan kopierar denna tomma länkade cell till mitt aktuella arbetsblad.

 

Det aktuella arbetsbladet:

I kolumn A står en lista från A2 - A30 med namn. Alltså A2 = Adam, A3 = Bertil, A4 = Calle etc. ända till A30 = Örjan .

När jag nu i Kolumn B2 - B30 klistrar in värden från kolumn G2 - G30, som in sin tur är länkade från en annan flik i mitt dokument uppstår detta fenomen att B2 fylls med värdet noll, som jag alltså dolt, men som Excel tar hänsyn till ändå. 

Detta betyder att kolumn B har en del dolda nollvärden, som alltså inte skall räknas. 

 

Så här kan det se ut, när jag första gången klistrar in de länkade värdena:

 

Kolumn A   Kolumn B   Kolumn C      Kolumn D      Kolumn E       Kolumn F (osv)

2Adam        (dold 0)                                                                           

3 Bertil            2

4 Calle            3

5 David .     (dold 0) 

 

Nu har alltså Adam och David ett dolt nollvärde i B2, resp B5. 

 

Och nu till problemet:

Första gången jag klistrar in värdena, (från kolumn G till kolumn skall det alltså inte stå någonting alls i formel kolumnen, som i mitt exempel är kolumn H. .

 

Alltså inget meddelande i H2 av typen #OGILTIGT! eller av typen  ##### i H5. 

Det ska inte stå nånting alls, fram till dess att jag fyllt i minst tre kolumner; och i Adams fall måste minst B2, C2, D2 och E2 fyllas för att excel ska räkna fram Adams 3 minsta sammanlagda minsta värde i kolumn/cell H2. Dessförinnan ska alltså H2 vara en tom cell.

 

Om ni inte förstår nu, vet jag inte hur jag ska förklara mitt problem.

Men jag hoppas på svar!

Mvh 

Leif

[lebn]

Tillägg:

Jag vet inte varför det dök upp en smiley i dokumentet ovan- det var inte min avsikt.

ursäkta.

Leif

[Monshi]

Något som detta då

{ =OM(OCH(ANTALV(A1:A4)=4;ANTAL.OM(A1:A4;0)<2);SUMMA(MINSTA(A1:A4;{1;2;3}));"") }

 

om jag förstår dig rätt. Om det inte stämmer ska jag läsa igenom ditt inlägg igen.

 

[Ove Söderlund]

Mitt förslag till lösning, se bifogad fil.

 

Förklaring i korthet:

Skapa tre kolumner längre till höger i bladet, du kan dölja dessa kolumner efter att du har slutfört konstruktionen av bladet.

 

Kolumn H innehåller villkorskollen att om fler än 2 tomma eller nollvärden (baserat på att du kommer att utföra beräkningen på fem kolumner. Utökar du till fler kolumner måste villkoret ändras (antal kolumner - 3). Uppfylls villkoret så är cellen blank, annars beräkna summan av de tre extra kolumnerna (som då kan vara dolda)

 

summa_tre_minsta.xls

 

 

[lebn]

Återigen stort tack för svar!

Ove - din modell fungerar! Den blir lite klumpig att skapa, då jag inte söker de tre minsta värdena utan de 10 minsta under en serie som brukar vara runt 30 omgångar. Lite olika från år till år. Men som sagt det är bara att skapa fler dolda kolumner och bygga upp minsta värdena ända upp till 10. 

Monshi - om du orkar, skriv din modell en gång till, men nu med de rätta värdena, dvs det är 10 sammanlagda minsta omgångar jag eftersöker. När jag försöker ändra din formel utifrån de rätta förhållandena, så händer en del underliga saker, om jag har en dold nolla eller om jag tar bort den dolda nollan ger olika utfall, och det kan ju bero på att jag missförstått nån del i din formel. Tacksam om du skriver den igen, med rätt förutsättningar! För om du får den att fungera, så är den ju smidigare än Oves, som är lite mer otymplig att bygga.

Mvh 

Leif

[Monshi]

Det gäller bara att jag får rätt kriterier så ska matrisformeln gå att få att fungera.

Tror jag.

Du vill summera över de tio minsta talen under premissen att det finns minst tre värden, icke nollvärden. Om färre än tre värden ska inget returneras annars summan av de minsta-värden som finns, max tio stycken.

Dvs om färre än tio värden är det egentligen summan av alla du vill ha, om det finns fler än tio värden summan av de tio lägsta (exklusive nollvärden).

 

Har jag tolkat dig rätt denna gång?

[Monshi]

Typ som

{ =OM(OCH((ANTALV(B4:S4)-ANTAL.OM(B4:S4;0))>=3;(ANTALV(B4:S4)-ANTAL.OM(B4:S4;0))<10);SUMMA(B4:S4);OM((ANTALV(B4:S4)-ANTAL.OM(B4:S4;0))>=10;SUMMA(MINSTA(B4:S4;{1;2;3;4;5;6;7;8;9;10}));"")) }

fast intressant hur mitt Excel buggade när denna räknades ut initialt.

Matrisformel givetvis.

 

Ha, MH hann före..

Finns mer än ett sätt  och bra att slippa matrisinmatning.

[lebn]

Jösses, hör ni excel fantomer - nu måste jag dra i handbromsen.

Glöm det där med tre minsta - det var ju som jag redan skrivit, ett exempel, för att hålla det enkelt.

 

Jag vill få fram de 10 minsta sammanlagda värdena i en serie, som över tid , kommer att omfatta runt 30 värden. (Jag matar in ett nytt värde för varje deltagare / vecka)

 

Och innan jag matat in minst 10 positiva värden (inte noll värden!!) ska formelcellen inte visa någonting. Formelcellen ska vara tom.

Allt eftersom jag fyller på med fler värden skall alltså formelcellen ta hänsyn till de nya värdena som matas in och om så behövs, ge ett nytt minsta sammanlagda värde.

 

Jag sammanfattar problemet:

 

1. Formel cellen (AE1)ska vara tom till minst 10 positiva värden och inga nollvärden, har matats in på raden A1 - AD1 

 

2. Om det förekommer nollvärde i någon(/några av cellerna på raden A1-AD1 så måste fler positiva värden tillkomma innan formel cellen (AE1) ger resultat

 

3. Allteftersom jag matar in nya värden på raden A1-AD1 så skall formelcellen (AE1) räkna om, och om så behövs, ge ett nytt minsta, sammanlagda värde.

 

4. Efter tex. 15 veckor ska jag alltså kunna meddela samtliga deltagare, att just nu är NN den person som har minsta sammanlagda värde. Och efter ca 30 veckor ska jag tala om vem som presterar det bästa resultatet för året. Fortfarande talar jag om det minsta sammanlagda värdet av de tio bästa resultaten för resp person.

 

Detta är mitt problem, och jag väntar med spänning om ni kan lösa detta med en formel, och inte som Ove gjorde med 10 dolda kolumner. I hans lösning visar formel cellen inte någonting förrän alla villkor är uppfyllda. Men de dolda kolumnerna däremot kan ha en massa #### , men eftersom dessa kolumner är dolda så stör det inte utseendet på arbetsbladet, men lösningen i sig är lite otymplig, det blir ju 10 extra kolumner att skriva formler i. 

 

Jag tackar också er för allt arbete som ni lägger ner på detta problem. Tack!

 

Mvh

Leif

[lebn]

Äntligen!! Toppen!! Tusen tack MH_resurrected!!

Din andra formel med RAD(INDIREKT... löste problemet. Å, fantastiskt!

Har testat nu ett flertal gånger, manipulerat med både dold nolla och helt tomma celler och din formel håller ihop det!

Tack så hemskt mycket, fantastiskt!

Mvh

Leif