Senast än noll i rad

June 27

Brian har en rad med siffror med 240 celler. På den här raden, siffrorna stadigt sjunkande och kommer så småningom, någon gång under dessa 240 celler, blir 0. nollor kommer att fortsätta att fylla de återstående cellerna i raden. Brian behöver skriva en ekvation som kommer tillbaka den sista icke-noll värde i rad.

Det finns en mängd olika sätt att det önskade värdet kan returneras. (Inte som alltid verkar vara fallet med Excel? Du kan komma med massor av sätt att få ett resultat.) I allmänhet kan du använda en vanlig formel eller en matrisformel.

Om du vill använda en vanlig formel, här är en som du kan prova:

= OFFSET (A6,0, (COUNT (A6: IF6) -COUNTIF (A6: IF6,0)) - 1)

Den COUNTIF Funktionen räknar antalet nollvärden och COUNT funktion bestämmer antalet celler i intervallet. Subtrahera från varandra och justering med 1 ger OFFSET värdet i "array" av celler där de sista icke-noll lögner värde. Denna formel antar att värdena börjar i kolumn A; om de börjar i en annan kolumn då måste du justera värdet tillhandahålls av COUNT / COUNTIF del av formeln för att representera förskjutningen från den första kolumnen.

Här är en kortare variant av formeln, baserat på att göra en förskjutning från den högra sidan av intervallet snarare än den vänstra sidan:

= OFFSET (IF6,0, -COUNTIF (A6: IF6,0))

I detta fall är det viktigt att IF6 vara den faktiska högra änden av intervallet. Formeln fungerar genom att räkna antalet nollvärden i intervallet (alla vid den högra sidan av intervallet) och sedan beräkna cell adressen för den sista cellen (IF6) minus antalet nollor.

Här är en version som använder funktionen INDEX, istället:

= INDEX (A6: IF6,, PASSA (0, A6: IF6,0 -1))

Denna version är ännu kortare, med hjälp av funktionen LETAUPP:

= LETAUPP (1,1 / (6: 6> 0), 6: 6)

Matrisformler kan också användas. (Array formler in genom att trycka Ctrl + Shift + Enter.) Den här använder INDIREKT funktion:

= INDIREKT ("R6C" & MAX ((A6: IF6> 0) * KOLUMN (A6: IF6)), FALSKT)

Denna matrisformel använder en intressant tillämpning av funktionen LETAUPP att hitta rätt resultat:

= LETAUPP (9.99999999999999E + 307, IF (A6: IF6 <> 0, A6: IF6))

Här är en annan matrisformel som kan användas, den här gången med hjälp OFFSET funktionen för att hitta den sista icke-noll värde i rad 6:

= FÖRSKJUTNING (A6,0, MIN (IF (6: 6 = 0, KOLUMN (6: 6), 300)) - 2)

Här är en ännu kortare variant:

= MIN (OM (A6: IF6> 0, A6: IF6))

Alla dessa formler som presenteras hittills beror på det faktum att siffrorna i raden faktiskt minska-de går från oavsett början nummer är och stadigt går mot noll. Om siffrorna inte minska, då kan du använda en annan typ av matrisformel för att fastställa den sista icke-noll i raden:

= INDEX (6: 6, MAX (IF (A6: IF6 <> 0, KOLUMN (A6: IF6))))

Formeln först fastställer den maximala kolumnen i raden (i detta fall rad 6) som har ett värde som inte är lika med noll, då den använder funktionen INDEX för att få värdet från den kolumnen i den raden.

Som du kan berätta, det finns en hel del sätt att hitta den sista icke-noll i rad. Välj den som slår din fantasi; det finns inget rätt eller fel i detta fall.

ExcelTips är din källa för kostnadseffektiv Microsoft Excel utbildning. Detta tips (3785) gäller för Microsoft Excel 97, 2000, 2002, och 2003. Du kan hitta en version av detta tips för menyfliksområdet i Excel (Excel 2007 och senare) här: Senast än noll i rad.