Funkcia hodnotenia v príkladoch programu Excel. Štatistické funkcie Excelu

10 júl

Dobré popoludnie, milí odberatelia a čitatelia blogu. Dnes si povieme niečo o triedení údajov v excelovských tabuľkách. Ale triedenie nie je jednoduché, ale s určitou podmienkou - nemôžete zmeniť poradie údajov v tabuľke.

Naše problémy by skutočne vyriešil jednoduchý filter, ale akékoľvek vzostupné alebo zostupné zoradenie zmení pozíciu údajov v tabuľke podľa ich veľkosti. Zoberme si taký príklad. Je tam tabuľka:

Ak zapneme filter od najväčšieho po najmenší, tak sa poradie predajní zmení, keďže je potrebné vidieť najlepšie a najhoršie výsledky. Vyberte hlavičku tabuľky, na karte „Domov“ kliknite na tlačidlo „Zoradiť a filtrovať“ a potom vyberte riadok „Filter“. Budete si môcť vybrať kritériá triedenia. Podľa podmienky zvoľte triedenie "Od najväčšieho po najmenšieho".

Poradie obchodov sa zmenilo, ale podľa stavu problému by to nemalo byť povolené. V tomto prípade je ideálna funkcia RANK. Počnúc Excelom 2010 boli zavedené nové funkcie RANK.EQ a RANK.AVG. Funkcia RANK bola ponechaná kvôli kompatibilite s predchádzajúcimi verziami programu, takže bude použitá podobná funkcia RANK.RW.

Hlavným rozdielom medzi funkciami je RANK.RV berie do úvahy rovnaké hodnoty a priraďuje im rovnakú úroveň.

RANK.AVG - vráti priemer medzi dvoma rovnakými stupňami (ich poradové čísla).

Syntax vzorca bude nasledovná:

číslo— číselná hodnota, pre ktorú sa počíta poradie (povinný argument);

objednať — poradie nastavenia hodnosti (ak je 0 alebo pole zostane prázdne, potom priradenie hodnosti bude v zostupnom poradí, ak je 1, poradie bude vzostupne, voliteľný argument).

Urobme nasledovné:

pridať do našej tabuľky ďalší stĺpec „Umiestniť v zisku“;
zavolajte sprievodcu funkciou;
zadajte do vyhľadávacieho poľa RANK.РВ;
kliknite na "OK";
vyplňte argumenty funkcie.

V tomto vzorci je rozsah od B2 do B10 špecificky fixný, aby bolo možné použiť značku automatického dopĺňania. Natiahneme to na koniec stola. Výsledok na seba nenechá dlho čakať.

Funkcia RANK.EQ vracia poradie čísla v zozname čísel, to znamená jeho veľkosť vzhľadom na ostatné hodnoty v zozname.

Popis funkcie RANK.EQ

Vráti poradie čísla v zozname čísel, to znamená jeho veľkosť vzhľadom na ostatné hodnoty v zozname. Ak má viacero hodnôt rovnaké poradie, vráti sa najvyššie poradie z tejto množiny hodnôt. Ak je zoznam zoradený, potom poradie čísla bude jeho pozíciou.

Syntax

=RANK.EQ(číslo, referencia, [poradie])

Argumenty

Požadovaný.Číslo, pre ktoré sa určuje poradie.

Voliteľné.Číslo, ktoré určuje spôsob triedenia.

Poznámky

Ak je hodnota argumentu poradie 0 (nula) alebo je vynechaná, Excel určí poradie čísla, ako keby bol odkaz zostupne zoradený zoznam.
Ak je hodnotou argumentu poradie akékoľvek číslo iné ako nula, Excel určí poradie čísla, ako keby bol odkaz vzostupne zoradený zoznam.
Funkcia RANK.EQ priraďuje duplicitným číslam rovnaké hodnoty poradia. Prítomnosť opakovaných čísel však ovplyvňuje poradie nasledujúcich čísel.
Napríklad, ak zoznam celých čísel zoradených vzostupne obsahuje číslo 10 dvakrát, ktoré má poradie 5, číslo 11 bude mať poradie 7 (žiadne z čísel nebude mať poradie 6).
V niektorých prípadoch môže byť potrebné použiť definíciu poradia založenú na vzťahu. Povedzme, že v predchádzajúcom príklade chcete získať hodnotenie 5,5 pre číslo 10. Ak to chcete urobiť, pridajte korekčný faktor (pozri nižšie) k hodnote vrátenej funkciou RANK.EQ. Tento koeficient možno použiť v prípadoch, keď sa poradie počíta v zostupnom poradí (argument „poradie“ je nulový alebo sa vynechá) aj vzostupne (hodnota argumentu „poradie“ sa nerovná nule).
Korekčný faktor pre súvisiace hodnosti

Pri práci s údajmi je často potrebné zistiť, aké miesto v agregovanom zozname zaujíma ten či onen ukazovateľ z hľadiska hodnoty. V štatistike sa tomu hovorí poradie. Excel má nástroje, ktoré používateľom umožňujú rýchlo a jednoducho vykonať tento postup. Poďme zistiť, ako ich používať.

Na vykonanie hodnotenia v Exceli sú k dispozícii špeciálne funkcie. V starších verziách aplikácie bol jeden operátor navrhnutý na vyriešenie tohto problému − RANK. Z dôvodov kompatibility je ponechaný v samostatnej kategórii vzorcov a v moderných verziách programu, ale ak je to možné, je stále žiaduce v nich pracovať s novšími analógmi. Patria sem štatistické operátory RANK.RV A RANK.SR. O rozdieloch a algoritme na prácu s nimi si povieme neskôr.

Metóda 1: Funkcia RANK.EQ

Operátor RANK.RV vykoná spracovanie údajov a zobrazí v zadanej bunke poradové číslo zadaného argumentu zo súhrnného zoznamu. Ak má viacero hodnôt rovnakú úroveň, operátor vypíše najvyššiu hodnotu zo zoznamu hodnôt. Ak majú napríklad dve hodnoty rovnakú hodnotu, obom bude priradené druhé číslo a ďalšia najväčšia hodnota bude mať štvrté. Mimochodom, operátor RANK v starších verziách Excelu, takže tieto funkcie možno považovať za identické.

Syntax tohto operátora je napísaná takto:

Argumenty "číslo" A "odkaz" sú povinné a "objednať"- voliteľné. Ako argument "číslo" musíte zadať odkaz na bunku, ktorá obsahuje hodnotu, ktorej sériové číslo chcete zistiť. Argument "odkaz" obsahuje adresu celého rozsahu, ktorý je hodnotený. Argument "objednať" môže mať dva významy - «0» A "1". V prvom prípade sa poradie počíta v zostupnom poradí a v druhom prípade vo vzostupnom poradí. Ak tento argument nie je zadaný, potom ho program automaticky považuje za nulový.

Tento vzorec je možné napísať ručne do bunky, kde chcete zobraziť výsledok spracovania, ale pre mnohých používateľov je pohodlnejšie nastaviť zadávanie cez okno Funkcionári.

Metóda 2: Funkcia RANK.AVG

Druhá funkcia, ktorá vykonáva operáciu hodnotenia v Exceli, je RANK.SR. Na rozdiel od funkcií RANK A RANK.RV, ak sa hodnoty viacerých prvkov zhodujú, tento operátor vráti priemernú úroveň. To znamená, že ak sú dve hodnoty rovnakej veľkosti a idú po hodnote číslo 1, potom im bude pridelené číslo 2,5.

Syntax RANK.SR veľmi podobná schéme predchádzajúceho operátora. Vyzerá to takto:

Vzorec je možné zadať manuálne alebo pomocou Sprievodcu funkciou. Poslednou možnosťou sa budeme zaoberať podrobnejšie.

Ako vidíte, v Exceli existujú dve funkcie na určenie poradia konkrétnej hodnoty v rozsahu údajov: RANK.RV A RANK.SR. Pre staršie verzie programu použite operátora RANK, čo je v podstate úplný analóg funkcie RANK.RV. Hlavný rozdiel medzi vzorcami RANK.RV A RANK.SR je, že prvý z nich naznačuje najvyššej úrovni keď sa hodnoty zhodujú, a druhý zobrazí priemer ako desatinný zlomok. Toto je jediný rozdiel medzi týmito operátormi, no treba ho brať do úvahy pri výbere, ktorá funkcia je pre používateľa najvhodnejšia.

Na zoradenie údajov v Exceli sa používajú štatistické funkcie RANK, RANK.RV, RANK.AVG. Všetky vrátia číslo čísla v zoradenom zozname číselných hodnôt. Pozrime sa bližšie na syntax, príklady.

Príklad funkcie RANK v Exceli

Funkcia sa používa pri zaraďovaní do zoznamu čísel. To znamená, že vám umožňuje zistiť hodnotu čísla vo vzťahu k iným číselným hodnotám. Ak zoradíte zoznam vo vzostupnom poradí, funkcia vráti pozíciu čísla. Napríklad v poli čísel (30;2;26) bude mať číslo 2 poradie 1; 26-2; 30 -3 (ako najväčšia hodnota v zozname).

Syntax funkcie:

číslo. Pre ktoré chcete určiť číslo v rebríčku.
Link. Pole čísel alebo rozsah buniek s číselnými hodnotami. Ak zadáte ako argument len čísla, funkcia vráti chybu. Nečíselné hodnoty nemajú priradené číslo.
Objednať. Spôsob objednávania čísel v zozname. Možnosti: argument je "0" alebo vynechaný - hodnota 1 je priradená maximálnemu číslu v zozname (ako keby bol zoznam zoradený zostupne); argumentom je akékoľvek nenulové číslo - poradové číslo 1 je priradené najmenšiemu číslu v zozname (ako keby bol zoznam zoradený vzostupne).

Definujme poradie čísel v zozname bez opakovaní:

Argument, ktorý určuje, ako sú čísla usporiadané, je "0". Preto v tejto funkcii boli čísla priradené k hodnotám od najväčšej po najmenšiu. Maximálny počet 87 má priradené číslo 1.

Tretí stĺpec zobrazuje vzorec so vzostupným poradím.

Poďme určiť počet hodnôt v zozname, kde sú duplicitné hodnoty.

Duplicitné čísla sú zvýraznené žltou farbou. Všetky majú rovnaký počet. Napríklad číslu 7 v druhom stĺpci je priradené číslo 9 (v druhom riadku aj v deviatom); v treťom stĺpci - 3. Ale žiadne z čísel v druhom stĺpci nebude mať 10 a v treťom - 4.

Aby sa poradia neopakovali (niekedy to bráni používateľovi vyriešiť úlohu), používa sa nasledujúci vzorec:

Môžete nastaviť limity pre fungovanie funkcie. Napríklad stačí zoradiť hodnoty od 0 do 30. Na vyriešenie problému použite funkciu IF (=IF(A2

Hodnoty, ktoré zodpovedajú zadanej podmienke, sú zvýraznené sivou farbou. Pre čísla väčšie ako 30 sa zobrazí prázdny reťazec.

Príklad funkcie RANK.EQ v Exceli

Verzie Excelu od roku 2010 zaviedli funkciu RANK.EQ. Toto je absolútna analógia predchádzajúcej funkcie. Syntax je rovnaká. Písmená „PB“ v názve označujú, že ak vzorec nájde rovnaké hodnoty, funkcia vráti najvyššie poradové číslo (to znamená prvý prvok nájdený v zozname rovných).

Ako môžete vidieť z príkladu, danú funkciu zaobchádza s duplicitnými číslami v zozname presne tak, ako by to bolo v normálnom vzorci. Ak je potrebné vyhnúť sa opakovaniu hodností, použijeme iný vzorec (pozri vyššie).

Príklad funkcie RANK.AVG v Exceli

Vráti čísla číselnej hodnoty v zozname (poradové číslo vzhľadom na ostatné hodnoty). To znamená, že vykonáva rovnakú úlohu. Vráti priemer iba vtedy, ak sa nájdu rovnaké hodnoty.

Tu je výstup funkcie:

Vzorec v zostupnom stĺpci je: =RANK.AVG(A2,$A$2:$A$9,0). Funkcia teda priradila hodnote 87 priemerné číslo 1,5.

Povedzme, že v zozname čísel sú tri duplicitné hodnoty (zvýraznené oranžovou farbou).

Funkcia priradila každému z nich hodnotenie 5, čo je priemer 4, 5 a 6.

Porovnajme prácu dvoch funkcií:

Pripomeňme, že tieto dve funkcie fungujú iba v Exceli 2010 a novšom. V starších verziách môžete na tento účel použiť vzorec poľa.

Stiahnite si príklady hodnotiacej funkcie RANK v Exceli.

Všetky vyššie opísané príklady vám teda umožňujú automatizovať prácu s hodnotiacimi údajmi a hodnotami bez triedenia.

Potrebujete každému číslu v Exceli priradiť číslo, aby ste ich mohli zoradiť podľa tohto čísla? Pre textové údaje môžete vymyslieť zložité štruktúry, ale pre číselné údaje existuje v Exceli špeciálna funkcia RANK. Vzťahuje sa na číslo statické funkcie a je celkom užitočná. V článku hovoríme aj o nových funkciách z Excelu 2010 RANK.CP() a RANK.РВ()

Ako použiť. Funkcia RANK v Exceli

Funkcia RANK sa používa pomerne jednoducho. Sada argumentov je tiež jednoduchá, konkrétne:

Popis argumentov:

číslo-číslo, pre ktoré chcete určiť poradie/miesto v poli
Odkaz- pole hodnôt, v ktorých sa určuje poradie/miesto čísla
Poradie je voliteľný argument.

Ak zadáte 0 alebo tento argument nevyplníte, poradie sa určí v zostupnom poradí. Tie. najvyšší počet 2000 bude na 1. mieste.

Ak zadáte akékoľvek iné číslo, maximálna hodnota bude pod posledným miestom.

Na prvom obrázku je podrobne znázornené, ako používať funkciu.

Poznámka. Ak sú v zozname 2 rovnaké čísla - v našom prípade 1400, budú im priradené rovnaké hodnoty poradia - 2, t.j. druhé miesto. V tomto prípade ďalšie miesto už nebude 3, ale 4 (1 250). Dosť dôležitá vlastnosť, ktorá nie každému vyhovovala. Vývojári teda pokračovali a vyrobili doplnkové funkcie RANK.CP() a RANK.RV() v Exceli 2010.

RANK.CP

Ak existuje 1 400 3 alebo viac čísel s rovnakou hodnotou, všetkým bude pridelené poradie =2 - to je pre normálnu funkciu RANK. Funkcia RANK.AVG vypočíta miesto ako priemer 3 miest, t.j. 3 miesta sú na druhom, treťom a štvrtom mieste

RANK dá všetkým 3 číslam druhé miesto.

RANK.CH umiestni všetkých troch do štvrtého miesta.

RANK.RV

Je to jednoduché – ide o rovnakú funkciu RANK len pre nové verzie Excelu – 2010 a staršie.

Príklad súboru

O triedení a hľadaní najväčšej hodnoty si môžete prečítať v tomto článku.

(Navštívené 7 463 krát, dnes 16 návštev)

Funkcie hodnotenia

Na vykonanie hodnotenia v Exceli sú k dispozícii špeciálne funkcie. V starších verziách aplikácie bol na vyriešenie tohto problému určený jeden operátor – RANK. Z dôvodov kompatibility je ponechaný v samostatnej kategórii vzorcov a v moderných verziách programu, ale stále je žiaduce v nich pracovať s novšími analógmi, ak je to možné. Patria sem štatistické operátory RANK.EQ a RANK.AVG. O rozdieloch a algoritme na prácu s nimi si povieme neskôr.

Metóda 1: Funkcia RANK.EQ

Operátor RANK.RV spracuje údaje a odošle poradové číslo zadaného argumentu z agregovaného zoznamu do zadanej bunky. Ak má viacero hodnôt rovnakú úroveň, operátor vypíše najvyššiu hodnotu zo zoznamu hodnôt. Ak majú napríklad dve hodnoty rovnakú hodnotu, obom bude priradené druhé číslo a ďalšia najväčšia hodnota bude mať štvrté. Mimochodom, operátor RANK v starších verziách Excelu robí to isté, takže tieto funkcie možno považovať za identické.

Syntax tohto operátora je napísaná takto:

Argumenty "číslo" a "odkaz" sú povinné, zatiaľ čo argumenty "poradie" sú voliteľné. Ako argument "číslo" je potrebné zadať odkaz na bunku, ktorá obsahuje hodnotu, ktorej poradové číslo chcete zistiť. Argument referencie obsahuje adresu celého rozsahu, ktorý je hodnotený. Argument "objednávka" môže mať dve hodnoty - "0" a "1". V prvom prípade sa poradie počíta v zostupnom poradí a v druhom prípade vo vzostupnom poradí. Ak tento argument nie je zadaný, potom ho program automaticky považuje za nulový.

Tento vzorec je možné zapísať ručne do bunky, kde chcete zobraziť výsledok spracovania, ale pre mnohých používateľov je pohodlnejšie nastaviť vstup cez okno Sprievodca funkciou.

lekcia: Sprievodca funkciami v Exceli

Metóda 2: Funkcia RANK.AVG

Druhá funkcia, ktorá vykonáva operáciu hodnotenia v Exceli, je RANK.AVG. Na rozdiel od funkcií RANK a RANK.RW, ak sa hodnoty viacerých prvkov zhodujú, tento operátor vráti priemernú úroveň. To znamená, že ak sú dve hodnoty rovnakej veľkosti a idú po hodnote číslo 1, potom im bude pridelené číslo 2,5.

Syntax RANK.AVG je veľmi podobná syntaxi predchádzajúceho príkazu. Vyzerá to takto:

Vzorec je možné zadať manuálne alebo pomocou Sprievodcu funkciou. Poslednou možnosťou sa budeme zaoberať podrobnejšie.

Vyberieme bunku na hárku, aby sa zobrazil výsledok. Rovnakým spôsobom ako predtým prejdite do Sprievodcu funkciou pomocou tlačidla „Vložiť funkciu“.
Po otvorení okna Sprievodca funkciami vyberte názov RANK.AVG v zozname kategórie „Štatistika“ a kliknite na tlačidlo „OK“.
Aktivuje sa okno argumentov. Argumenty pre tento operátor sú úplne rovnaké ako pre funkciu RANK.EQ:
- Číslo (adresa bunky obsahujúcej prvok, ktorého úroveň by sa mala určiť);
- Link (súradnice rozsahu, v rámci ktorého sa hodnotenie vykonáva);
- Objednávka (voliteľný argument).
Zadávanie údajov do polí prebieha presne rovnakým spôsobom ako v predchádzajúcom operátorovi. Po vykonaní všetkých nastavení kliknite na tlačidlo "OK".
Ako vidíte, po vykonaných akciách sa výsledok výpočtu zobrazil v bunke označenej v prvom odseku tejto inštrukcie. Samotný súčet predstavuje miesto, ktoré konkrétna hodnota zaberá medzi ostatnými hodnotami v rozsahu. Na rozdiel od výsledku RANK.EQ môže mať výsledok operátora RANK.AVG zlomkovú hodnotu.
Rovnako ako v predchádzajúcom vzorci, zmenou referencií z relatívnych na absolútne a pomocou rukoväte výberu môžete zoradiť celý rozsah údajov pomocou automatického dopĺňania. Algoritmus akcií je úplne rovnaký.

lekcia: Ďalšie štatistické funkcie v programe Microsoft Excel

lekcia: Ako na automatické dopĺňanie v Exceli

Ako vidíte, v Exceli existujú dve funkcie na určenie poradia konkrétnej hodnoty v rozsahu údajov: RANK.EQ a RANK.AVG. Pre staršie verzie programu sa používa operátor RANK, ktorý je v skutočnosti úplným analógom funkcie RANK.RV. Hlavný rozdiel medzi vzorcami RANK.EQ a RANK.AVG je v tom, že prvý z nich označuje najvyššiu úroveň, keď sa hodnoty zhodujú, a druhý zobrazuje priemer ako desatinný zlomok. Toto je jediný rozdiel medzi týmito operátormi, no treba ho brať do úvahy pri výbere, ktorá funkcia je pre používateľa najvhodnejšia.

Sme radi, že sme vám mohli pomôcť vyriešiť problém.

Opýtajte sa svoju otázku v komentároch a podrobne opíšte podstatu problému. Naši odborníci sa pokúsia odpovedať čo najrýchlejšie.

Často je užitočné vypočítať poradie hodnôt z daného rozsahu údajov. Napríklad, ak máte pracovný hárok obsahujúci ročné čísla predaja pre predajcov, môžete zistiť, ako sa každý z nich umiestnil, od najvyššieho po najnižšie.

Ak ste už funkciu použili RANK, možno ste si všimli, že pozície vrátené touto funkciou nemajú vzťahy, ktoré by vám mohli vyhovovať. Napríklad, ak sú dve hodnoty spojené tretím miestom, potom funkcia RANK obom priradí číslo 3. Môžete radšej priradiť každému priemerné hodnotenie - inými slovami, hodnotenie 3,5 pre obe hodnoty umiestnené na treťom mieste.

Excel 2010 obsahuje novú funkciu tzv RANK.CP ktorý rieši tento problém. Na obr. Obrázok 98.1 zobrazuje tabuľku, ktorá používa dve metódy na zoradenie hodnôt v stĺpci. Prvá metóda (stĺpec C) aplikuje funkciu RANK a stĺpec D špecifikuje Nová funkcia RANK.SR. Rozsah B2:B16 sa nazýva Predaj.

Všimnite si, že dvaja ľudia sú na treťom mieste. Funkcia RANK priradí každému z nich hodnotu 3 a funkcia RANK.CP -3,5. Navyše traja ľudia sú na desiatom mieste. Funkcia RANK dáva každému z nich poradie 10 a funkciu RANK.CP, na druhej strane dáva týmto trom hodnotenie 11, čo je v priemere 10,11 a 12.

Vzorce, ktoré používajú funkciu RANK.CP, vráti chybu v predchádzajúce verzie Excel. Ak sa chystáte zdieľať svoj zošit s ľuďmi, ktorí používajú verziu Excelu staršiu ako v roku 2010, namiesto funkcie môžete použiť vzorec poľa RANK.SR.

Pozrite sa na obr. 98.1 a do riadku 4 zadajte nasledujúci vzorec poľa. Potom ho skopírujte do buniek nižšie. Výsledok bude úplne rovnaký ako pri použití funkcie RANK.SR:
=SUM(1*(B4

Vzorec poľa je špeciálny druh vzorca, ktorý funguje s internými poľami údajov. Pri zadávaní vzorca poľa musíte stlačiť Ctrl+Shift+Enter(nie len Zadajte) na označenie, že ide o vzorec poľa. Excel pridá okolo vzorca zložené zátvorky, aby vám pripomenul, že ide o vzorec poľa. Ak nekliknete Ctrl+Shift+Enter, vzorec nebude fungovať.

Cystitída je jedným z najčastejších zápalových ochorení. Častejšie to ženy trpia kvôli zvláštnostiam anatomickej štruktúry, ale cystitída sa vyskytuje aj u mužov. Pravdepodobnosť vzniku ochorenia sa zvyšuje po utrpení zápalových ochorení, prítomnosti chronických ložísk infekcie v tele alebo akéhokoľvek iného stavu, ktorý prispieva k potlačeniu imunitného systému tela v dôsledku porušenia stravy.