Excel - směrodatná odchylka z určitých buněk

toto ti pomôže

2910294-funkce-averageif-v-excelu

Díky za reakci! :) To se však vztahuje k tomu prvnímu, ale co se týče té směrodatné odchylky a modusu, tak stále nevím. Potřebuji udělat smodch jen z některých buněk, ale jelikož je jich hodně, nechci je jednotlivě označovat. Jde to tedy udělat, aby to sečetlo jen některé buňky? Díky!

Mám pre Teba iné riešenie:

Pod celú tabuľku si zadaj funkciu SUBTOTAL.
Je to zaujímavá multifunkcia, ktorá z tabuľky vypočítava rôzne funkcie podľa zadaného parametra, pričom na rozdiel od samostatných funkcií zahrnutých v tejto multifunkcii zohľadňuje výsledok filtrácie.

T.j. SUBTOTAL(9;...) t.j. súčet, na rozdiel od funkcie SUM (tiež súčet), zohľadňuje len hodnoty po aplikovaní filtra. Takže:
1: výsledok SUBTOTAL vidíš i pri aktívnom filtri (SUM nevidíš, resp vidíš, len ak ho umiestniš tak, aby ho aktívny filter neskryl)
2: výsledok zvolenej funkcie v rámci multifunkcie SUBTOTAL počíta výsledok len z viditeľných hodnôt filtrácie (SUM počíta súčet aj z riadkov, ktoré sú aktívnym filtrom skryté)

SUBTOTAL obsahuje množstvo funcií, medzi nimi i Tvoju odchýlku.

Takže ak Ťa zaujíma odchýlka pre mužov, zapneš filter "muži" a vo funkcii SUBTOTAL(8;...) t.j. štandardná odchýlka, budeš vidieť odchýlku pre mužov.
Ak chceš vidieť odchýlku pre mužov a ženy, len zmeníš filter na "muži" + "ženy"...

Priemery pre mužov a ženy budeš vidieť vo funkcii SUBTOTAL(1;...) parameter 1 = priemer; pri filtri "muži" + "ženy"...

Odporúčam pozrieť si i nápovedu k funkcii...
Používaj parametre 1÷11

A keďže SUBTOTAL neobsahuje funkciu MODUS (MODE), dá sa táto funkcia (rešpektujúca aktívny filter!) získať trikom s pomocným stĺpcom. Viď príloha. V bunke C1 je maticový (CSE) vzorec, t.j. na jeho zadanie sa nestláča samotný ENTER, ale trojkombinácia CTRL+SHIFT+ENTER

Ešte to môžeš upraviť tak, že bunku C1 presunieš do D1 a stĺpec C skryješ (aby si nevidel pomocný stĺpec).
Musíš si ale byť vedomý, že ak budeš predlžovať zoznam, musíš stĺpec C odkryť aby si v ňom mohol potiahnuť vzorec až po nový posledný riadok.

P.S. MODUS je najčastejšie sa vyskytujúca hodnota v zozname. Pokiaľ je tam ale viac hodnôt s rovnakým počtom výskytu, berie sa najnižšia hodnota.
To, že v bunke C1 máš okrem výsledku aj popis "Modus =", je dosiahnuté vlastným formátom bunky

P.S.P.S. Siki, vieš to splácnuť do jedného maticového? Mohlo by to ísť, ale na todlenc su já krátký

Jde to viz soubor

nerozumeli sme sa...
Myslel som vplácnout do maticového vzorca v C1 vzorce z oblasti C2:C22...
Tie Tvoje vzorce sú natvrdo, t.j. len z mužov, len zo žien a len z detí.
Ja chcem aby sa moja bunka s červeným písmom menila podľa filtra, kde môžu byť i kombinácie m+d, m+z, z+d.
Mne to funguje ale len s pomocným stĺpcom. Ten pomocný stĺpec chcem odbúrať... a preto to vplácnutí

Tak sem trochu pohledal a našel na to vhodnou fci excelu =>AGGREGATE

Není to sice maticově, ale vypadá to, že vrací relevantní výsledek

No ja ODPADNEM!!!!!!!!!!!!!!!!!!!!!!

To si AKO našiel?!?!?!!?!?!?! Blahoželám!!!

Takže SUBTOTAL škrtám
AGGREGATE zahŕňa viac možností, takže všetko čo dotazovateľ potrebuje (priemer, odchýlku aj modus) možno elegantne vyriešiť jedinou multifunkciou AGGREGATE.

Otázne je, od ktorej verzie Excelu je AGGREGATE k dispozícii a či dotazovateľ má verziu s touto funkciou.

Prikladám vzor s ukážkou výsledkov AGRREGATE (červené pozadie) aj SUBTOTAL (modré pozadie)

Siki, si proste macher!!! A to nielen s veľkým "M", ale všetkými písmenami!

Zdravím, ahoj! Všem díky za reakci :) Bohužel však ve svém Excelu 2007 funkci "AGGREGATE" nemám. Funkce "SUBTOTAL" funguje, ale právě že pouze při aktivním filtru. Nešlo by to nějak udělat, aby funkce fungovala i při filtru vypnutém? To by vyřešilo vše, co potřebuji. Díky moc chlapi! :)

Niečo by sa s tým dalo robiť, ale bolo by potrebné vedieť, či to, čo si uviedol, bolo len ako príklad a jedná sa o iné údaje, či v skutočnosti nie je viac vstupných druhov (muži, ženy, deti), či potrebuješ mať pred sebou naraz všetky výpočty (priemer, odchýlku, modus) pre všetky možné kombinácie a pod
V prípade Tvojho príkladu by to bolo najjednoduchšie tak, že miesto toho, aby si filtrom musel manuálne dosahovať všetky kombinácie, by nimi prebehlo makro (ktoré dopredu potrebuje poznať všetky požadované kombinácie) a pri každej z kombinácií prevezme výsledky a uloží ich ako hodnoty. Aby sa to nekomplikovalo, makro by bolo spúšťané tlačidlom, vždy po zmene tabuľky.
Samozrejme šlo ba zautomatizovať aj to, že by makro samo striehlo na zmeny na hárku a vykonalo výpočty po každej zmene, ale tým sa nabaľujú ďalšie požiadavky a celé riešenie je náročnejšie

Mám samozřejmě jinou tabulku, ale princip je naprosto stejný. Vložil jsem tabulku, která funguje na naprosto stejném principu. Potřebuji jednoduše zjistit průměry, směrodatné odchylky a modusy pro jednotlivá pohlaví. Má tabulka je však delší, tzn. potřeboval bych to udělat nějak "chytře", abych manuálně neoznačoval jednotlivá políčka. Tabulka viz příloha.

No to je perfektní! Mockrát díky! Ale když to zkouším u sebe v Excelu, jaktože mi to nefunguje? Mám tam stejný vzorec jako ty, ale i přesto mi to nefunguje :/ Viz: https://youtu.be/rIcxdMwmtgU

Jedná se o maticové vzorce, jejich ukončení se děje trojkombinací CTRL+SHIFT+ENTER (složené závorky se doplní automaticky) nikoliv jen enter.

Přikládám ještě soubor, kde se kritérium bere z buněk, opět maticové vzorce.

Naprosto super, už funguje, díky moc! Koukám na ten tvůj druhý soubor a není mi jasný, jakej je rozdíl mezi tvým prvním řešením a druhým řešením?

rozdiel je v tom, že Ti Siki vyriešil i problém s preklepom - orientuje sa podľa prvého písmena

V prvním řešení je kritérium zadáno "natvrdo" (muž, žena, dítě). Ve druhém si vzorec bere první písmeno z oblasti kritérií a jako kritérium se bere buňka vedle vzorce, ze které si bere první písmeno po mezeře.

Rozumím, díky moc! Tohle již funguje, paráda. Teď ještě bádám nad těmi společnými průměry. Pokud chci například průměr ženy + muži, jak na to? Zkoušel jsem averageifs, ale přes tuto funkci mi to nefunguje :/ Dělám nějaké chyby? Viz: https://youtu.be/AOcJEqAeRi8 .... Díky!

A už sme tam, že jo?
Nepísal si pred chvíľou, že vlastne si rád, že to funguje len pre "čisté" kategórie???

A sme na začiatku...

Takže tu je moja varianta, kde máš vypočítané všetky možné kombinácie
Žiadny vzorec.
Všetko je v makre, ktoré po sebe všetko pomocné uprace. Rozanalyzuj si ho
Makro usleduje aj rozrastanie tabuľky (ale v tej polohe v akej je) ale nezapracoval som Sikiho kontrolu preklepu. Tá neustriehne, keď napríklad miesto "žena" napíšeš "žubrienka"
Proste tam si musíš dať pozor. Cez overenie údajov si môžeš preddefinovať vylúčenie nesprávnych vstupných hodnôt.
Tabuľka obsahuje čisté hodnoty.

Malá oprava:
V makre uprav predposledný riadok:

Range("D5:D22,G1,J1,M1").ClearContents

na:

Range("D5:D" & MaxRiadok & ",G1,J1,M1").ClearContents

Buď řešení, které Ti poskytl Robert, nebo takto pomocí vzorců.
Opět se jedná o vzorce maticové.

PS: fce AVERAGEIFS mi taky vrací chybu dělení nulou , a jelikož nemám náladu řešit proč použil sem maticově fci PRŮMĚR.

Nespomínal si náhodou predtým potrebu výsledkov aj pre kombinácie muži+ženy, ženy+deti, muži+deti ?

A propos, výsledky nebudeš mať správne, pokiaľ v tabuľke budeš mať preklepy

Ano, původně jsem to zmiňoval, ale když teď vidím, jak jsem na tom bídně, tak budu rád, když vyřeším vůbec tu odchylku :D

Jojo, překlepu jsem si všiml, je mi jasné, že chyby tam být nesmí, ale byla to pouze ilustrace, díky za upozornění :)

Otázne je, od ktorej verzie Excelu je AGGREGATE k dispozícii

Tak sem nahodil staré verze, a funkce je dostupná od verze Excelu 2010.

Aha, než jsem to tu všechno přečetl, tak jsem vyrobil to, co vlastně není potřeba pouze s funkcí "průměr". Nejsem excelentní excelista.

Jo, já to blbě pochopil. Nicméně koukám, že Ti to někdo i vyřešil. Nejsem ani statistik a tyhle funkce (averageif a spol.) jsem nikdy nepoužil. Navíc jsem měl ještě před asi čtyřmi lety MS Office 2 000 a tam to možná ani nebylo.
Zkusil jsem, jestli bych to uměl a použil bych funkce AVERAGEIF (na ty průměry) a COUNTIF (na počet výskytů.

Nic ve zlým ale ještě furt to chápeš blbě

Celkom by som sa divil, keby sa v tomto chaose Vety ešte dokázal zorientovať

Perfektní! Siki i Roberte, moc děkuji oběma! :) Sikiho řešení je snazší, tzn. pro mě geniální Vyznám se v tom a vše jsem již udělal = díky moc hoši, moc jste mi pomohli! :)