<
  • Главная
Статьи

Funkcia SUMMESLE, ako aj SUMMESLES podľa dvoch kritérií

  1. Hľadať podľa značiek
tajnosť »11. júna 2011 Dmitry 243582 zobrazenie

Predstavte si tabuľku, v ktorej sú názvy oddelení (alebo účtov, alebo niečo iné) uvedené v riadkoch v rade.

Vypočítajte bunky podľa kritéria
Pre každé oddelenie je potrebné vypočítať celkovú sumu. Mnohí to s filtrom a písanie s perami v bunkách.
Aj keď to možno urobiť jednoducho a jednoducho s jednou funkciou - SUMMESLI .
SUMMESLES (SUMIF) - Vypočítava bunky, ktoré spĺňajú danú podmienku (môže byť špecifikovaná iba jedna podmienka). Túto funkciu možno použiť aj v prípade, ak je tabuľka rozdelená do stĺpcov podľa období (mesačne, v každom mesiaci, troch stĺpcoch - Príjmy | Výdavky | Rozdiel) a musíte vypočítať celkovú sumu za všetky obdobia iba podľa príjmov, výdavkov a rozdielov.

Súhrnne existujú tri argumenty pre SUMMESLI: Range , Criterion , Range_Summing .
= SUMMESLE (A1: A20000; A1; B1: B20000)
= SUMIF (A1: A20000, A1, B1: B20000)

  • Rozsah (A1: A20000) - označuje rozsah s kritériami. tj Stĺpec, v ktorom chcete vyhľadať hodnotu uvedenú argumentom Kritérium .
  • Kritériom (A1) je hodnota (text alebo číslica, ako aj dátum), ktoré sa musia nachádzať v rozsahu . Môže obsahovať zástupné znaky "*" a &quot;?". tj špecifikovaním kritéria "* mass *" na zhrnutie hodnôt, v ktorých sa vyskytuje slovo "mass". Slovo „hmotnosť“ sa zároveň môže vyskytnúť kdekoľvek v texte, alebo v bunke môže byť iba jedno slovo. A zadaním "hmotnosti *" sa spočítajú všetky hodnoty začínajúce na "hmotnosť". &quot;?" - nahrádza iba jeden znak, t. zadaním "mas? a" môžete spočítať riadky s hodnotou "mass" a hodnotou "mask" atď.
    Ak je kritérium napísané v bunke a stále potrebujete použiť zástupné znaky, môžete vytvoriť odkaz na túto bunku pridaním potrebnej. Predpokladajme, že musíte zhrnúť hodnoty, ktoré obsahujú slovo „celkom“. Slovo "total" je napísané v bunke A1, zatiaľ čo v stĺpci A môžu byť rôzne pravopisné hodnoty obsahujúce slovo "total": "súčty za jún", "súčty za júl", "súčty za marec". Vzorec by potom mal vyzerať takto:
    = LETO (A1: A20000; "*" & A1 a "*"; B1: B20000)
    "*" & A1 & "*" - znak & (znak) kombinuje niekoľko hodnôt do jedného. tj výsledok je "* výsledok *".
    Na lepšie pochopenie princípu fungovania vzorcov je lepšie použiť nástroj Calculate Formula : Ako zobraziť kroky na výpočet vzorcov
    Všetky textové kritériá a kritériá s logickými a matematickými znakmi musia byť uzavreté v úvodzovkách (= SUMMESLI (A1: A20000; "total"; B1: B20000)). Ak je kritériom číslo, kotácie sa nevyžadujú. Ak chcete nájsť otazník alebo hviezdičku priamo, musíte pred neho umiestniť tildu (~).
    Informácie o tilde a jej vlastnostiach nájdete v tomto článku: Ako nahradiť / odstrániť / nájsť hviezdičku?
  • Sum_Range (B1: B20000) (nepovinný argument) - špecifikuje rozsah súčtov alebo číselných hodnôt, ktoré sa majú sčítať.

Ako to funguje: funkcia vyhľadáva rozsah pre hodnotu zadanú argumentom Kritérium a keď je nájdená zhoda, sumarizuje údaje uvedené argumentom Range_Amount. tj ak máme názov stĺpca v stĺpci A a čiastku v stĺpci B, potom špecifikujeme oddelenie vývoja ako kritérium bude mať za následok súčet všetkých hodnôt stĺpca B, oproti ktorému sa oddelenie vývoja nachádza v stĺpci A. V skutočnosti SumArrangement nemusí mať rovnakú veľkosť ako argument Range a to nespôsobí chybu samotnej funkcie. Avšak pri definovaní buniek na sčítanie sa horná ľavá bunka argumentu Range_Amount použije ako východisková bunka pre súčet a potom sa sčítajú bunky zodpovedajúce veľkosti a tvaru argumentu Range.

Niektoré funkcie
Posledný argument funkcie (Sum_And_Band: B1: B20000) je voliteľný. To znamená, že nemôže byť špecifikovaný. Ak ho nezadáte, funkcia sčíta hodnoty určené argumentom Range . Na čo slúži. Napríklad musíte získať súčet iba tých čísel, ktoré sú väčšie ako nula. V stĺpci A sumy. Potom bude funkcia vyzerať takto:
= LETO (A1: A20000; "> 0")

Čo by sa malo zvážiť: range_summing a rozsah by sa mali rovnať počtu riadkov. V opačnom prípade môžete získať nesprávny výsledok. Optimálne, ak bude vyzerať vo vzorcoch, ktoré som dal: rozsah a rozsah súčtov začínajú od jedného riadku a majú rovnaký počet riadkov: A1: A20000; B1: B20000

Súčet dvoch alebo viacerých kritérií
Ale čo robiť, keď kritériá pre sumáciu 2 a viac? Predpokladajme, že je potrebné zhrnúť iba tie sumy, ktoré patria do jedného oddelenia a len na určitý dátum. Happy vlastníci kancelárskych verzií 2007 a vyššie môžu používať funkciu SUMMESLIMN:
= SUMMESLIMN ($ C $ 2: $ C $ 50; $ A $ 2: $ A $ 50; $ I $ 3; $ B $ 2: $ B $ 50; $ H8)
$ C $ 2: $ C $ 50 - range_summing. Prvý argument špecifikuje rozsah buniek obsahujúcich čiastky, ktoré budú zhromaždené do jedného.
$ A $ 2: $ A $ 50, $ B $ 2: $ B $ 50 - Range_Criteria. Určuje rozsah buniek, v ktorých chcete hľadať zhodu podľa kritéria.
$ I $ 3, $ H8 - kritérium. Tu, ako v SUMMESLI, povolené zástupné znaky * a ? a pracujú rovnakým spôsobom.

Špecifiká zadávania argumentov: najprv je zadané kritérium (sú očíslované), potom je hodnota (kritérium) uvedená priamo v bodkočiarke, ktorá musí byť v tomto rozsahu nájdená - $ A $ 2: $ A $ 50; $ I $ 3. A nič iné. Nemali by ste sa pokúšať najprv špecifikovať všetky rozsahy, a potom kritériá pre ne - funkcia bude buď dať chybu, alebo nebude sumarizovať to, čo je potrebné.

Všetky podmienky sú porovnávané podľa princípu I. To znamená, že ak sú splnené všetky uvedené podmienky. Ak aspoň jedna podmienka nie je splnená, funkcia preskočí riadok a nič nepridá.
Čo sa týka LETO, súčet a kritériá by sa mali rovnať počtu riadkov.

pretože SUMMESLIMN sa objavil len vo verziách programu Excel, od roku 2007, potom ako môžu byť v takýchto prípadoch nešťastní užívatelia predchádzajúcich verzií? Veľmi jednoduché: použite inú funkciu - SUMPRODUCT. Nebudem maľovať argumenty, pretože Je ich veľa a sú to všetky skupiny hodnôt. Táto funkcia násobí polia uvedené argumentmi. Pokúsim sa opísať všeobecný princíp použitia tejto funkcie na zhrnutie údajov o niekoľkých podmienkach.
Na vyriešenie problému sčítania podľa viacerých kritérií bude funkcia vyzerať takto:
= SUMPRODUCT (($ A $ 2: $ A $ 50 = $ I $ 3) * ($ B $ 2: $ B $ 50 = H5), $ C $ 2: $ C $ 50)
$ A $ 2: $ A $ 50 - rozsah dátumov. $ I $ 3 je dátum kritéria, pre ktoré je potrebné údaje zhrnúť.
$ B $ 2: $ B $ 50 - mená oddelení. H5 - názov oddelenia, údaje o ktorých sa musí sčítať.
$ C $ 2: $ C $ 50 - rozsah s čiastkami.

Analyzujeme logiku, pretože pre mnohých to bude úplne nejasné len pri pohľade na túto funkciu. Ak len preto, že v tejto pomoci nie je opísaná táto aplikácia. Pre väčšiu čitateľnosť znížte veľkosť rozsahov:
= SUMPRODUCT (($ A $ 2: $ A $ 5 = $ I $ 3) * ($ B $ 2: $ B $ 5 = H5), $ C $ 2: $ C $ 5)
Takže výraz ($ A $ 2: $ A $ 5 = $ I $ 3) a ($ B $ 2: $ B $ 5 = H5) sú logické a návratové polia logickej FALSE a TRUE. TRUE, ak sa bunka rozsahu $ A $ 2: $ A $ 5 rovná hodnote bunky $ I $ 3 a bunky rozsahu $ B $ 2: $ B $ 5 sa rovná hodnote bunky H5. tj máme nasledujúce:
= SUMPRODUCT ({FALSE; TRUE; TRUE; FALSE} * {FALSE; FALSE; TRUE; FALSE}; $ C $ 2: $ C $ 50)
Ako môžete vidieť, v prvom poli sú dve podmienky pre danú podmienku av druhej. Ďalej sú tieto dve polia vynásobené (za to zodpovedá násobiaci znak (*)). Keď dôjde k násobeniu, implicitná konverzia polí FALSE a TRUE na číselné konštanty 0 a 1 ({0; 1; 1; 0} * {0; 0; 1; 0} *) nastane. Ako viete, keď vynásobíme nulou, dostaneme nulu. Výsledkom je jediné pole:
= SUMPRODUCT ({0; 0; 1; 0}; $ C $ 2: $ C $ 50)
Potom sa pole {0; 0; 1; 0} vynásobí radom čísel v rozsahu $ C $ 2: $ C $ 50:
= SUMPRODUCT ({0; 0; 1; 0}; {10; 20; 30; 40})
V dôsledku toho dostaneme 30. Čo sme potrebovali - dostaneme len sumu, ktorá spĺňa kritérium. Ak existuje viac ako jedna suma, ktorá spĺňa kritérium, potom sa sčítajú.

Výhoda SUMMYROIZV
Ak argumenty majú namiesto znamienka násobenia znamienko plus:
($ A $ 2: $ A $ 5 = $ I $ 3) + ($ B $ 2: $ B $ 5 = H5)
potom sa podmienky porovnajú podľa princípu OR: t. celkové sumy sa sčítajú, ak je splnená aspoň jedna podmienka: buď $ A $ 2: $ A $ 5 sa rovná hodnote bunky $ I $ 3 alebo bunky rozsahu $ B $ 2: $ B $ 5 sa rovná hodnote bunky H5.
To je výhoda SUMMPRODUCT cez SUMMESLIMN. SUMMESLIMN nemôže spočítať hodnoty podľa princípu OR, len podľa princípu AND (musia byť splnené všetky podmienky).

nedostatky
SUMPRODUCT nemôže použiť zástupné znaky * a ?. Je možné použiť presnejšie, ale nebudú vnímané ako špeciálne znaky, ale ako hviezdička a otáznik. Myslím si, že je to významná nevýhoda. A aj keď to možno obísť, používam iné funkcie v rámci SUMPRODUCT - bolo by to skvelé, keby funkcia mohla nejakým spôsobom používať zástupné znaky.

V príklade nájdete niekoľko príkladov funkcií pre lepšie pochopenie toho, čo je napísané vyššie.

Stiahnite si príklad

Množstvo podľa niekoľkých kritérií (41,5 KiB, 10 477 stiahnutí)

Pozri tiež:
Súčet buniek podľa farby výplne
Súčet buniek podľa farby písma
Súčet buniek podľa bunkového formátu
Vypočítajte množstvo buniek podľa farby výplne
Vypočítajte množstvo buniek podľa farby písma
Ako zhrnúť údaje z niekoľkých listov, vrátane stavu

Článok pomohol? Zdieľajte odkaz so svojimi priateľmi! Video tutoriály

{"Bottom bar": {"textstyle": "statický", "textpositionstatic": "bottom", "textautohide": true, "textpositionmarginstatic": 0, "textpositiondynamic": "bottomleft", "textpositionmarginleft": 24, " textpositionmarginright ": 24," textpositionmargintop ": 24," textpositionmarginbottom ": 24," texteffect ":" slide "," texteffecteasing ":" easOutCubic "," texteffectduration ": 600," texteffectslidedirection ":" left "," texteffectslidedirection " : 30, "texteffectdelay": 500, "texteffectseparate": false, "texteffect1": "slide", "texteffectslidedirection1": "right", "texteffectslidedistance1": 120, "texteffecteasing1": "easOutCubic", "texteffectduration1": 600 , "texteffectdelay1": 1000, "texteffect2": "slide", "texteffectslidedirection2": "right", "texteffectslidedistance2": 120, "texteffecteasing2": "easOutCubic", "texteffectduration2": 600, "texteffectdelay2": 1500, " textcss ":" zobraziť: blok; vypchávky: 12px; zarovnať text: doľava; "," textbgcss ":" zobraziť: blok; pozícia: absolútna; horná: 0px; doľava: 0px; šírka: 100%; výška: 100% ; farba pozadia: # 333333; opacity: 0.6; filter: a lpha (opacity = 60); "," titlecss ":" zobraziť: blok; poloha: relatívna; font: bold 14px "Lucida Sans Unicode", "Lucida Grande", sans-serif, Arial; color: #fff; "," descriptioncss ":" zobraziť: blok; poloha: relatívna; font: 12px "Lucida Sans Unicode", "Lucida Grande", sans-serif, Arial; farba: #ff; margin-top: 8px; "," buttoncss ":" zobraziť: blok; poloha: relatívna; margin-top: 8px; "," texteffectresponsive ": true," texteffectresponsivesize ": 640," titlecssresponsive ":" font-size: 12px; "," descriptioncssresponsive ":" display: none: important; "," buttoncssresponsive ": "", "addgooglefonts": false, "googlefonts": "", "textleftrightpercentforstatic": 40}}

Hľadať podľa značiek

prístup jablkové hodinky Multex výhľad Power Query a Power BI VBA pracuje v editore Správa kódu VBA Bezplatné doplnky Dátum a čas Grafy a grafy papiere Ochrana údajov Internet Obrázky a objekty Listy a knihy Makrá a VBA Add-ons nastavenie vytlačiť Vyhľadať údaje Zásady ochrany osobných údajov pošta relácie Práca s aplikáciami Práca so súbormi Vývoj aplikácií Súhrnné tabuľky zoznamy Školenia a webináre finančné formátovanie Vzorce a funkcie Excel funkcie Funkcie VBA Bunky a rozsahy Akcie spoločnosti MulTEx analýza údajov chyby a závady v programe Excel referencie Môže obsahovať zástupné znaky "*" a "?
Quot;?
Zadaním "mas?
Pretože SUMMESLIMN sa objavil len vo verziách programu Excel, od roku 2007, potom ako môžu byť v takýchto prípadoch nešťastní užívatelia predchádzajúcich verzií?


Новости
  • Виртуальный хостинг

    Виртуальный хостинг. Возможности сервера распределяются в равной мере между всеми... 
    Читать полностью

  • Редизайн сайта

    Редизайн сайта – это полное либо частичное обновление дизайна существующего сайта.... 
    Читать полностью

  • Консалтинг, услуги контент-менеджера

    Сопровождение любых интернет ресурсов;- Знание HTML и CSS- Поиск и обновление контента;-... 
    Читать полностью

  • Трафик из соцсетей

    Сравнительно дешевый способ по сравнению с поисковым и контекстным видами раскрутки... 
    Читать полностью

  • Поисковая оптимизация

    Поисковая оптимизация (англ. search engine optimization, SEO) — поднятие позиций сайта в результатах... 
    Читать полностью