V tomto článku sa naučíme Ako počítať riadky, ak v programe Excel zodpovedá aspoň n číslam.
Scenár:
Jednoducho povedané, pri práci s údajmi o číslach. Potrebujeme extrahovať počet riadkov, ktoré majú prinajmenšom, najviac alebo presne n hodnôt z konkrétneho riadku, ktorý spĺňa uvedené kritériá v programe Excel. Napríklad pri výpočte počtu študentov, ktorí v testoch podávajú priemerné výsledky. Tento druh problémov s počítaním môžeme vykonávať v dlhom zozname pomocou nižšie uvedeného vzorca programu Excel.
Použité funkcie vzorca a syntax vzorca:
Bude použitá nasledujúca funkcia. Pred použitím sa teda trochu zoznámte.
- Funkcia SUM
- Funkcia MMULT
- Funkcia TRANSPOSE
- COLUMN (funkcia)
Z týchto uvedených funkcií zostrojíme vzorec.
Použite vzorec:
| { = SUM ( - (MMULT ( - ((údaje) <kritika), TRANSPOZÍCIA (STĹPEC (údaje) 0)) = n)) } |
Poznámka: Pre zložené rovnátka ( { } ) Použiť Ctrl + Shift + Enter pri práci s poľami alebo rozsahmi v programe Excel. Štandardne sa vo vzorci vygenerujú kučeravé zátvorky. NEPOKÚŠAJTE sa pevne kódovať znaky zložených zátvoriek.
údaje: zväzok čísel ako údajov.
= n: presný počet výskytov (použite <= na nanajvýš resp >= minimálne)
krit: kritériá, s ktorými sa má zhodovať
Príklad:
Toto všetko môže byť mätúce na pochopenie. Otestujme teda tento vzorec spustením na príklade uvedenom nižšie. Tu máme za sebou údaje o predaji pre konkrétne ID predaja. Musíme nájsť počet predajných ID, kde predajca nedokončil predaj 40 presne dvakrát. Tu sú kritériá <40 a výskyt je 2 (= 2 pre presný výskyt)
Vzorec:
| { = SUM ( - (MMULT ( - ((C4: H15) <40), TRANSPOZÍCIA (STĹPEC (C4: H15) 0)) = 2)) |
Vysvetlenie:
- Funkcia COLUMN vracia pole s číslom stĺpca z prvej bunky v poli do poslednej bunky v poli. {3; 4; 5; 7; 8}
- ^0, táto operácia s akýmikoľvek číslami polí vráti pole 1 s.
- Funkcia TRANSPOSE prevádza riadky maticového poľa na stĺpce a stĺpce na riadky. Tu sa pole 1 s v rade prevedie na pole 6 (1 s) v stĺpcoch. {1; 1; 1; 1; 1; 1}
- - ((C4: H15) <40), táto operácia vráti hodnoty ako 1 s ako PRAVDIVÉ hodnoty (zodpovedajúce kritériá) a 0 s ako falošné hodnoty.
- Funkcia MMULT preberá maticové násobenie 2 polí a vracia hodnoty, ako je uvedené nižšie.
= SUM ( - ({1; 0; 1; 0; 2; 1; 0; 0; 1; 2; 3; 3} = 2))
- Funkcia SUM započítava hodnoty v poli zodpovedajúcom 2, pretože výskyt je požadovaný.
- Na dosiahnutie výsledku použite Ctrl + Shift + Enter namiesto. Pretože toto je maticový vzorec.
Tu sú polia pre funkciu uvedené ako referencia na pole. Ručne použite dvojité (-) Stlačením klávesu Enter získate výsledok.
Ako vidíte na obrázku vyššie, existujú 2 predajcovia, ktorí nedokončili ani 40 predajov. Toto je počet do zodpovedajúcich riadkov v údajoch, kde 2 čísla sú menšie ako 40. Výskyt počtu a kritériá, ktoré sa majú kontrolovať, môžete prispôsobiť v riadkoch tabuľky. Nasleduje príklad, ako získať grófa s rôznymi kritériami a rôznymi výskytmi.
Spočítajte riadky zodpovedajúce tržbám nad 100 s 3 ako presný výskyt:
Teraz by sme chceli zistiť, koľko predajcov dosahuje mierne dobré výsledky v predaji. Na to potrebujeme, aby bol počet predajcov, ktorí uskutočnili predaj, vyšší ako 100 presne trikrát.
Použite vzorec:
| = SUM ( - (MMULT ( - ((C4: H15)> 100), TRANSPOSE (STĹPEC (C4: H15) 0)) = 3)) |
> 100: číslo musí byť väčšie ako 100.
= 3: výskyt sa rovná 3
Tu sú polia pre funkciu uvedené ako referencia na pole. Ručne použite dvojité (-) Stlačením klávesu Enter získate výsledok.
Ako vidíte, existujú 3 predajcovia, ktorí sa majú stredne dobre. Toto je počet do zodpovedajúcich riadkov v údajoch, kde 3 predaje sú vyššie ako 100.
Tu sú všetky pozorovacie poznámky týkajúce sa použitia vzorca.
Poznámky:
- Vzorec funguje iba s číslami.
- Funkcie MMULT a TRANSPOSE sú maticovou funkciou, preto ich pred použitím skontrolujte.
- Ak vzorec vráti chybu #HODNOTA, vo vzorci musí byť prítomná kontrola zložených zátvoriek, ako je uvedené v príkladoch v článku.
- Negatívny znak (-) zmení hodnoty, TRUE alebo 1 s na FALSE alebo 0 s a FALSE alebo 0 s na TRUE alebo 1 s.
- Operácie ako sa rovná ( = ), menej ako sa rovná ( <= ), väčší než ( > ) alebo sa nerovná () je možné vykonať v rámci použitého vzorca, iba s číslami.
Dúfam, že tento článok o tom, ako počítať riadky, ak je v programe Excel n zhodných hodnôt, je vysvetľujúci. Tu nájdete ďalšie články o vzorcoch počítania. Ak sa vám páčili naše blogy, zdieľajte ich so svojimi prvými na Facebooku. A tiež nás môžete sledovať na Twitteri a Facebooku. Radi by sme vás počuli, dajte nám vedieť, ako môžeme zlepšiť, doplniť alebo inovovať našu prácu a zlepšiť ju pre vás. Napíšte nám na emailovú stránku
Nájdite posledný riadok údajov s číslami v programe Excel : V rade textových hodnôt nájdite posledné údaje v programe Excel.
Ako používať funkciu SUMPRODUCT v programe Excel: Vráti SUMU po vynásobení hodnôt vo viacerých poliach v programe Excel.
COUNTIFS s rozsahom dynamických kritérií : Počet buniek, ktoré sa odlišujú od ostatných hodnôt buniek v programe Excel.
COUNTIFS zápas dvoch kritérií : Počet buniek zodpovedajúcich dvom rôznym kritériám v zozname v programe Excel.
COUNTIFS s ALEBO pre viac kritérií : Počet buniek s viacerými kritériami sa zhoduje pomocou funkcie OR.
Funkcia COUNTIFS v programe Excel : Počet buniek závislých od iných hodnôt buniek.
Ako používať Countif vo VBA v programe Microsoft Excel : Počítanie buniek pomocou kódu Visual Basic for Applications.
Ako používať zástupné znaky v programe Excel : Spočítajte bunky zodpovedajúce frázam pomocou zástupných znakov v programe Excel
Populárne články:
50 Skratka pre Excel, ktorá zvýši vašu produktivitu : Zrýchlite sa vo svojej úlohe. Týchto 50 skratiek vám umožní pracovať ešte rýchlejšie v Exceli.
Ako používať tFunkcia VLOOKUP v programe Excel : Toto je jedna z najpoužívanejších a najobľúbenejších funkcií programu Excel, ktorá sa používa na vyhľadávanie hodnôt z rôznych rozsahov a hárkov.
Ako používať funkciu COUNTIF v programe Excel : Počítajte hodnoty s podmienkami pomocou tejto úžasnej funkcie. Na počítanie konkrétnych hodnôt nie je potrebné filtrovať údaje. Funkcia Countif je potrebná na prípravu palubnej dosky.
Ako používať funkciu SUMIF v programe Excel : Toto je ďalšia zásadná funkcia palubnej dosky. To vám pomôže zhrnúť hodnoty za konkrétnych podmienok.