MS Excel

Téma: Výpočet a přiřazení kvartilu pomocí rankingu

Zaslal/a Belisarius 20.2.2018 17:24

Dobrý den,

mám excelovskou tabulku s cca 100.000 jednotlivými záznamy (řádky), rozdělenými do několika stovek kategorií. Jednotlivé položky lze v rámci kategorie seřadit podle číselné hodnoty, vícenásobný výskyt téže hodnoty nevadí.

V příloze je soubor, který vše ilustruje - obsahuje 4 kategorie, seřazené podle hodnoty ve sloupci C. Ve slouci D je součet položek v kategorii a ve sloupci E konkrétní pozice položky. Z ní se dá odvodit procentuální vyjádření ve sloupci F.

Opravdu důležitý je však sloupec G, v němž pomocí vnořené funkce "když" počítám kvartily a zároveň je přiřazuji jednotlivým položkám. Takovýmto postupem lze přižadit kvartily neomezenému množství kategorií a položek, má to však své stinné či matoucí stránky.

1) když jsem hledal spojení Excel+kvartily, všude jsem nacházel jenom odkazy na funkci "kvartil" a její novější varianty, nikde řešení s pomocnými položkami a funkcí "když", navíc vždy ve smyslu určení kvartilů, ne jejich automatického přiřazení ke všem položkám - vypočítat si kvartily mi je vzhledek k velikému množství dat nanic, potřebuji je rovnou automaticky přiřadit.

2) Jak je vidět ve slouci H, při použití funkce pro kvartil se tento výpočet liší - podle "když" končí Q1 na 13, podle "kvartil" na 15. Příčinou asi bude, že končí na 14, což mi hlási výpočet podle metody 2 na https://www.hackmath.net/cz/kalkulacka/vypocet-kvartilu-q1-q2-q3

3) Postup podle "když" funguje (minimálně na první pohled) v kategoriích od 4 do libovolně velkých, ale kategorie s 1-3 členy vrací zjevné nesmysly. Přitom jsem nikde nenašel, že by nešlo rozdělit na 4 díly jednočlennou skupinu.

Takže prosím o pomoc:

1) je zvolený postup s funkcí "když" správný?

2) Pokud nikoliv, existuje nějaký vzorec pro "kvartil", který by kvartily vypočítával a přiřazoval k záznamům v závislosti na položce kategorie?

3) Toto je otázka spíše pro matematiky, ale jak se určují kvartily u skupin menších než 4?

Díky za pomoc!

Příloha: zip