< návrat zpět

MS Excel

Téma: Průměr z prvních 5 nenulových hodnot ve sloupci rss

Zaslal/a SG 22.9.2013 19:32

Dobrý den, prosím o jakoukoli radu: potřeboval bych co nejjednodšším způsobem zadat Excelu: "v zadaném sloupci vypočti aritmetický průměr z prvních 5 nenulových hodnot (odshora)". Pro konkrétní sloupec to jde samozřejmě jednoduše, ale potřebuji to zakomponovat do složitější funkce, kde bude pozice první nenulové hodnoty ve sloupci variabilní..takže bych to potřeboval napsat nějak obecně. děkuji za jakoukoli radu

Zaslat odpověď >
Strana:  1 2   další »
Opičák(22.9.2013 20:45)#015371
Opičák
Třeba takto, ale jestli se to hodí, nevím. Jistě přijde někdo s "jednobuněčným" řešením.
Příloha: zip15371_pr5.zip (7kB, staženo 27x)
citovat
icon AL(22.9.2013 23:49)#015372
avatar
@Opičák:Jistě přijde někdo s "jednobuněčným" řešením.
Tak v tomto prípade už som bol dosť na pochybách, či sa to dá dostať do jednej bunky. Dá, i keď využiteľnosť je prinajmenšom diskutabilná:

=PRŮMĚR(NEPŘÍMÝ.ODKAZ(ODKAZ(LARGE(((Data<>0)*ŘÁDEK(Data));COUNTIF(Data;"<>0"));SLOUPEC(Data)));
NEPŘÍMÝ.ODKAZ(ODKAZ(LARGE(((Data<>0)*ŘÁDEK(Data));COUNTIF(Data;"<>0")-1);SLOUPEC(Data)));
NEPŘÍMÝ.ODKAZ(ODKAZ(LARGE(((Data<>0)*ŘÁDEK(Data));COUNTIF(Data;"<>0")-2);SLOUPEC(Data)));
NEPŘÍMÝ.ODKAZ(ODKAZ(LARGE(((Data<>0)*ŘÁDEK(Data));COUNTIF(Data;"<>0")-3);SLOUPEC(Data)));
NEPŘÍMÝ.ODKAZ(ODKAZ(LARGE(((Data<>0)*ŘÁDEK(Data));COUNTIF(Data;"<>0")-4);SLOUPEC(Data))))

pochopiteľne, maticovo 1citovat
icon AL(23.9.2013 0:09)#015373
avatar
Vlastne, zjednodušiť sa to dá a dokonca do už celkom udržiavateľného tvaru:

=SUMA(NEPŘÍMÝ.ODKAZ(ODKAZ(LARGE(((Data<>0)*ŘÁDEK(Data));COUNTIF(Data;"<>0"));SLOUPEC(Data))&":"&ODKAZ(LARGE(((Data<>0)*ŘÁDEK(Data));COUNTIF(Data;"<>0")-4);SLOUPEC(Data))))
/
COUNTIF(NEPŘÍMÝ.ODKAZ(ODKAZ(LARGE(((Data<>0)*ŘÁDEK(Data));COUNTIF(Data;"<>0"));SLOUPEC(Data))&":"&ODKAZ(LARGE(((Data<>0)*ŘÁDEK(Data));COUNTIF(Data;"<>0")-4);SLOUPEC(Data)));"<>0")

-4 znamená priemer z piatich
-5 by bol priemer zo šiestich atd
(opäť maticový vzorec)citovat
icon AL(23.9.2013 0:29)#015374
avatar
A nakoľko mi v predošlých veziách vadila prítomnosť volatilných funkcií, tak som sa hecol a tu je výsledný pokus, mám za to, že už pomerne príjemný na používanie:

=SUMA(INDEX(Data;LARGE((Data<>0)*ŘÁDEK(Data);COUNTIF(Data;"<>0"))):INDEX(Data;LARGE((Data<>0)*ŘÁDEK(Data);COUNTIF(Data;"<>0")-4)))
/
COUNTIF(INDEX(Data;LARGE((Data<>0)*ŘÁDEK(Data);COUNTIF(Data;"<>0"))):INDEX(Data;LARGE((Data<>0)*ŘÁDEK(Data);COUNTIF(Data;"<>0")-4));"<>0")citovat
Opičák(23.9.2013 2:20)#015375
Opičák
tak jsem se pokusil taky a vyšlo mi:
=SUMA(POSUN(NEPŘÍMÝ.ODKAZ(ODKAZ(ŘÁDEK(data);SLOUPEC(data)));0;0;SMALL((data<>0)*ŘÁDEK(data);COUNTIF(data;0)+5)))/5

maticový vz.

edit:
chtěl jsem to řešit tak, abych mohl volit počet sčítaných nenulových buněk, takže pokud zadám číslo 5 (nebo jiné) třeba do G1, pak stačí vzorec upravit na:
=SUMA(POSUN(NEPŘÍMÝ.ODKAZ(ODKAZ(ŘÁDEK(data);SLOUPEC(data)));0;0;SMALL((data<>0)*ŘÁDEK(data);COUNTIF(data;0)+G1)))/G1
a volbou v G1 stanovím počet prvních nenulových buněk pro průměr.
@ AL
myslím, že ve vzorcích zbytečně vypočítáváš dělitel (tedy v tomto případě 5 - počet nenulových čísel), to je přeci dáno zadáním, tak stačí "suma .../5".citovat
icon eLCHa(23.9.2013 7:15)#015376
eLCHa
Lze využít chyby při dělení 0
Maticový
=PRŮMĚR(IFERROR(1/(1/Data*(COUNTIF(POSUN(Data;0;0;ŘÁDEK(Data)-ŘÁDEK(INDEX(Data;1))+1;1);"<>0")<6));""))

nebo s Opičákovým G1

=PRŮMĚR(IFERROR(1/(1/Data*(COUNTIF(POSUN(Data;0;0;ŘÁDEK(Data)-ŘÁDEK(INDEX(Data;1))+1;1);"<>0")<=G1));""))

Musím říct, že IFERROR opravdu chybělo ;)))

edit:
mno a když příhlédnu k tomu co psal Opičák
myslím, že ve vzorcích zbytečně vypočítáváš dělitel (tedy v tomto případě 5 - počet nenulových čísel), to je přeci dáno zadáním, tak stačí "suma .../5".

=SUMA(Data*(COUNTIF(POSUN(Data;0;0;ŘÁDEK(Data)-ŘÁDEK(INDEX(Data;1))+1;1);"<>0")<=G1))/G1citovat
icon eLCHa(23.9.2013 7:29)#015377
eLCHa
Ale pokud by to mělo být tak, aby to pochopil každy
tak pomocný sloupec a obyčejné AVERAGEIF
Příloha: zip15377_averageif.zip (6kB, staženo 28x)
citovat
icon eLCHa(23.9.2013 10:24)#015382
eLCHa
@Opičák, @AL

Pokud oblast nezačíná na řádku 1, tak to vypadá, že vám to nepočítá dobřecitovat
icon AL(23.9.2013 10:40)#015383
avatar
@Elcha: "Pokud oblast nezačíná na řádku 1, tak to vypadá, že vám to nepočítá dobře"
jj, ale v zadaní bolo, že sa týka celých stĺpcov, t.j. od riadku 1.. Každopádne, to sa dá ľahko upraviť, pokiaľ oblasť nezačína na 1. riadku, všade, kde je vo vzorci Riadok, je potrebné odpočítať prvý riadok oblasti data a pripočítať 1.
Skôr ma trápi, že pokiaľ tam sú prázdne bunky alebo nenumerické hodnoty, tak to nepočíta správne, takže vzorec by po zapracovaní uvedených eventualít znovu trochu nabobtnal.

@Opičák: s deliteľom samozrejme pravda, som to prekombinoval trochu 1

Inak ale, snažil som sa odstrániť volatilné funkcie, preto Index, nie Posun, tým pádom je môj vzorec dlhší, než tie Vaše..citovat
icon eLCHa(23.9.2013 10:49)#015384
eLCHa
kde bude pozice první nenulové hodnoty ve sloupci variabilní.

pokud by bylo jisté, že začíná na 1.řádku, tak
=SUMA(Data*(COUNTIF(POSUN(Data;0;0;ŘÁDEK(Data);1);"<>0")<=G1))/G1

Já bych si těmi volatilními-nevolatilními nekomplikoval život ;))citovat

Strana:  1 2   další »

Uživatelské menu

Nejste přihlášen(a)
avatar\n

Menu

On-line nástroje

Formulář Faktura

Formulář Faktura IV

Oblíbený formulář Faktura byl vylepšen a rozšířen.
Více se dočtete zde.

Aktivní diskuse

Čas od do

lubo • 19.4. 16:30

Makro smyčka

MilanKop • 19.4. 10:46

Makro smyčka

elninoslov • 19.4. 9:02

Čas od do

elninoslov • 19.4. 8:46

Čas od do

jarek1111 • 18.4. 13:46

Čas od do

lubo • 18.4. 11:13

Čas od do

jarek1111 • 18.4. 8:32