Všetky rovnaké ako určité množstvo. Príklady používania funkcie sú tiché v programe Excel

Ak chcete zhrnúť hodnoty, ktoré spĺňajú špecifikované kritérium (stav), použije sa funkcia; , Anglická verzia SUMIF ().

Funkcia syntaxe

Smutei(Rozsah; Stav; \\ T [REGET_SUMING])

Rozsah - Rozsah buniek, v ktorých sú vyhľadávané hodnoty príslušného argumentu Stav. Rozsah môže obsahovať čísla, dátumy, hodnoty textu alebo odkazy na iné bunky. V prípade ďalšieho argumentu - Range_suming - vynechané, potom argument Rozsah Je tiež rozsah, ktorým sú vykonané hodnoty uspokojujúceho argumentu. Stav (v tomto prípade Rozsah Musí obsahovať čísla).

Stav - Kritérium vo forme čísla, výrazu alebo textu, ktorý určuje, ktoré bunky by mali byť zhrnuté. Napríklad argument Stav Môže byť vyjadrený ako 32, "jablká" alebo "\u003e 32".

Range_suming - Rozsah buniek obsahujúcich čísla, ktoré budú zhrnuté v prípade, že bunky uvedené v argumente Rozsahzodpovedať argumentu Stav. Range_suming - Voliteľný argument. Ak je vynechaný, zhrnutie bude vykonané v rozsahu buniek uvedených v argumente Rozsah.

Príklady

Zvážte prípad, keď argument Range_suming vynechané. V tomto prípade sa summovanie uskutoční v rozsahu buniek uvedených v prvom argumente Rozsah (t.j. musí obsahovať čísla). Bude tiež vyhľadávať hodnoty príslušného argumentu Stavktoré sa potom zhrnujú. Nech je to rozsah B5: B15 Pozrite si príklad.

Riešime úlohy:

nájdite súčet všetkých čísel viac ako alebo rovných 10. Odpoveď: 175. Vzorec: \u003d Smerey (B5: B15; "\u003e \u003d 10")
nájdite súčet všetkých čísel menších alebo rovných 10. Odpoveď: 42. Vzorec: \u003d Tichý (B5: B15; "<=10")
nájdite súčet všetkých kladných čísel v rozsahu. Vzorec: \u003d Smolne (B5: B15; "\u003e 0"). Alternatívou pomocou funkcie sémpy () vyzerá takto: \u003d Sumperované (B5: B15) * (B5: B15\u003e 0))

Tvar úlohy podmienka(Kritériá) dostatočne flexibilné. Napríklad vo vzorci \u003d Smolne (B5: B15; D7 & D8) kritérium<=56 задан через ссылку D7&D8 : в D7. Obsahuje textovú hodnotu<=, а в D8. - číslo 56 (pozri obrázok nižšie). Užívateľ môže napríklad ľahko zmeniť kritérium pomocou bunky D7. . Ekvivalentný vzorec \u003d Tichý (B5: B15; "<=56") или \u003d Tichý (B5: B15; "<="&56) alebo \u003d Tichý (B5: B15; "<="&D8) alebo \u003d Smolne (B5: B15; D7 & 56).

Funkcia sa používa na použitie, ak je potrebné zhrnúť hodnoty rozsahu zodpovedajúce zadanému kritériu.

Popis funkcie

Funkcia sa používa na použitie, ak je potrebné zhrnúť hodnoty rozsahu zodpovedajúce zadanému kritériu. Napríklad v stĺpci s číslami je potrebné zhrnúť iba hodnoty presahujúce číslo 5. Na tento účel môžete použiť špecifikovaný vzorec nižšie:
\u003d Summovaný (B2: B25, "\u003e 5") V tomto príklade sa kontrolujú súhrnné hodnoty pre dodržiavanie kritéria. V prípade potreby môže byť stav aplikovaný na jeden rozsah a zhrnúť zodpovedajúce hodnoty z iného rozsahu. Napríklad, vzorec:
\u003d B2: B5; "Ivan"; C2: C5) Sú zhrnuté iba hodnoty z rozsahu C2: C5 sú zhrnuté, pre ktoré zodpovedajúce hodnoty z rozsahu B2: B5 sú "Ivan".
Ďalším príkladom používania funkcie je znázornený na obrázku nižšie:

Príklad použitia je tichý na výpočet zisku s podmienkou

Syntax

\u003d Tichý (rozsah; stav; [Rush_suming])

Argumenty

rozsah range_suming

Povinné argument. Rozsah buniek meraných podľa kritérií. Bunky v každom rozsahu musia obsahovať čísla, mená, polí alebo odkazy na čísla. Prázdne bunky a bunky obsahujúce hodnoty textu sú preskočené.

Povinné argument. Podmienka vo forme čísla, expresie, odkazov na bunku, text alebo funkciu, ktorá určuje, ktoré bunky musia byť zhrnuté. Napríklad podmienka môže byť reprezentovaná nasledovne: 32, "\u003e 32", B5, "32", "jablká" alebo dnes ().

DÔLEŽITÉ! Všetky textové podmienky a podmienky s logickými a matematickými príznakmi musia byť uzavreté v dvojitých úvodzoch ("). Ak je stav číslo, nie je potrebné používať úvodzovky.

Voliteľný argument. Bunky, ktorých hodnoty sú zhrnuté, ak sa líšia od buniek špecifikovaných ako rozsah. Ak je argument range_sum vynechaný, Microsoft Excel. Bunky uvedené v rozsahu argumentov (rovnaké bunky, na ktoré sa vzťahuje stav), sú zhrnuté.

Pripomienka

V argumente môže byť podmienka použitý zástupné znaky: otáznik (?) A hviezdička (*). Otázka Označiť sa zhoduje s akýmkoľvek znakom a hviezdičkou - akákoľvek sekvencia znakov. Ak chcete nájsť otáznik (alebo hviezdička) priamo, musíte dať znak "Tilda" (~).
Funkcia je ticho vracia nesprávne výsledky, ak sa používa na prispôsobenie struny dlhším ako 255 znakmi s reťazcom # znamená!.
Argument Rough_sum sa nemusí zhodovať s rozsahom argumentu. Pri určovaní skutočných buniek, ktoré majú byť sčítané, horná ľavá bunka sortimentu argumentov sa používa ako počiatočná, a potom sú zhrnuté bunky rozsahu rozsahu zodpovedajúcej argumentovému rozsahu.

Urobíme pridanie hodnôt tých, ktorých polia spĺňajú dve kritériá naraz (stav a). Zvážte textové kritériá, číselné a kritériá vo formáte dátumov. Analyzujeme funkciu summamenta ), Anglická verzia SUMIFS ().

Ako zdrojový stôl podniknite stôl s dvoma stĺpcami (poliami): text " Plody"A NUMERIC" Na sklade"(Pozri príklad súboru).

Úloha1 (1 textové kritérium a 1 číselné)

Nájdite počet krabíc tovaru s určitým Ovocie A, ktorý Zostatok boxov v sklade Nie menej minimálne. Napríklad počet krabíc s komoditou broskyne (bunka D. 2 ), ktorí majú zvyšok krabíc na sklade\u003e \u003d 6 (bunka E. 2 ) . Musíme získať výsledok 64. Výpočet môže byť realizovaný súborom vzorcov, dávame niekoľko (pozri príklad súboru Text a číslo listu):

1. =Smerevilimn (B2: B13; A2: A13; D2; B2: B13; "\u003e \u003d" & E2)

Funkcie syntaxe: SmereVulimn (interval_suming; interval_liviya1; stav1; interval_loculation2; podmienok2 ...)

B2: B13 interval_suming - Bunky na súčet vrátane názvov, polí alebo odkazov obsahujúcich čísla. Prázdne hodnoty a text sú ignorované.
A2: A13.a B2: B13 interval_lovia1; interval_loculia2; ... predstavujú od 1 do 127 rozsahov, v ktorých sa kontroluje zodpovedajúci stav.
D2.a "\u003e \u003d" & E2 stav1; Stav2; ... Je to od 1 do 127 podmienok vo forme niekoľkých, výrazov, odkazov na bunku alebo text definujúci, ktoré bunky budú zhrnuté.

Poradie argumentov sa nalial do funkcií SUMYMBRM () a ticho (). V argumente SUMMYMELIMNU () interval_suming Je to prvý argument a tichý () - tretí. Pri kopírovaní a úprave týchto podobných funkcií je potrebné zabezpečiť, aby argumenty boli uvedené v správnom poradí.

2. Ďalšia možnosť \u003d Sumpatic ((A2: A13 \u003d D2) * (B2: B13); - (B2: B13\u003e \u003d E2))
Podrobnejšie popíšeme použitie funkcie sépuboty ():

Výsledok výpočtu A2: A13 \u003d D2 je pole (False: Láska: Lies: Lies: Pravda: Pravda: Pravda: FALSE: FALSE: FALSE: FALSE: FALSE: LIE) Hodnota pravdy zodpovedá koincidencii hodnoty stĺpec ALE Kritériá, t.j. Slovné slovo broskyňa. Pole je možné vidieť zvýraznením v A2: A13 \u003d D2 a potom stlačte;
Výsledok výpočtu B2: B13 je (3: 5: 11: 98: 4: 8: 56: 2: 4: 6: 10: 11), t.j. len hodnoty z stĺpca B. ;
Výsledok násobenia elementu (A2: A13 \u003d D2) * (B2: B13) je (0: 0: 0: 0: 0: 8: 0: 0: 0: 0: 0: 0). Pri násobení čísla hodnoty hodnoty hodnoty 0; a hodnota pravdy (\u003d 1) sa získa číslo;
Budeme analyzovať druhú podmienku: výsledok výpočtu - (B2: B13\u003e \u003d E2) je pole (0: 0: 1: 1: 0: 1: 1: 0: 0: 1: 1: 1). Hodnoty v stĺpci " Počet krabíc na sklade", Ktoré spĺňajú kritérium\u003e \u003d E2 (t.j.\u003e \u003d 6) zodpovedá 1;
Ďalej, funkcia sépov () párov premenných prvkov polí a sumarizuje získané produkty. Dostávame - 64.

3. Ďalšie uskutočnenie funkcie sémpy () je vzorec \u003d Sumpered ((A2: A13 \u003d D2) * (B2: B13) * (B2: B13\u003e \u003d E2)).

V prípade potreby môžu byť dátumy zadané priamo vo vzorci \u003d SmerevylMN (B6: B17; A6: A17; "\u003e \u003d 01/15/2010"; A6: A17; "<=06.03.2010")

Ak chcete zobraziť podmienky výberu v textovom reťazci, použite vzorca \u003d "Predaj za obdobie od" & Text (D6; "DD.MM.MO") & "podľa" a textu (E6; "DD.MM.YG")

V poslednom vzorec sa používa.

TASK4 (mesiac)

Mierne upravte stav predchádzajúcej úlohy: Celkový predaj za mesiac nájdeme (pozri príklad súboru Mesačný mesiac).

Formuláry sú postavené podobne ako úloha 3, ale používateľ predstavuje 2 dátumy, ale názov mesiaca (predpokladá sa, že tabuľka je do 1 roka).

Ak chcete vyriešiť 3. úlohu, takto vyzerá tabuľka s nakonfigurovaným automatickým horákom (pozri príkladový súbor Dátumy listu 2).

Predtým musí byť tabuľka prevedená a povoliť reťazec výsledkov.

Dobré popoludnie, Vážení predplatitelia a Blogová návštevníci. Najnovšie sme prišli na vzorec podniku a rozhodol som sa písať o ďalšej veľmi užitočnej funkcii Excel o ďalšej veľmi užitočnej funkcii. Smutei. Obe tieto funkcie sú schopné "viazať" údaje z rôznych zdrojov (tabuľky) na kľúčovom poli a za určitých podmienok sú zameniteľné. Zároveň existujú výrazné rozdiely v ich vymenovaní a používaní.

Ak je aplikácia zadaním jednoducho "vytiahnuť" údaje z jedného miesta Excel do druhého, potom sa používajú na zhrnutie numerických údajov na zhrnutie podľa daného kritéria. Hlavným účelom tohto vzorca je súčet údajov v súlade s určitou podmienkou. Približne je napísané v pomoci. Tento nástroj sa však splní na iné účely. Napríklad, často používam vzorec, ktorý často používam namiesto HDP, pretože nedodáva chyby # n / d, keď niečo nemôže nájsť. A to robí to skvelé a urýchľuje prácu.

Funkcia musí byť úspešne prispôsobená na vyriešenie rôznych úloh. Preto v tomto článku považujeme 1 (druhé), ale až 2 (dva) príklady. Prvá bude spojená so súhrnom špecifikovaného kritéria, druhý - s "ťahaním" údajov, ktorý je ako alternatíva k podniku.

Príklad sumácie pomocou funkcie je tichý

Tabuľka zobrazuje pozície, ich množstvá, ako aj patriace do konkrétnej skupiny tovaru (prvý stĺpec). Zvážte zjednodušené používanie zjednodušeného použitia, keď potrebujeme vypočítať sumu len v pozíciách, hodnoty, na ktorých zodpovedajú určitému stavu. Chceme napríklad vedieť, koľko top sa predáva, t.j. Tých, ktorých hodnota presahuje 70 jednotiek. Vyhľadajte takýto tovar s očami a potom sumarizujte manuálne nie veľmi pohodlné a rýchlo, takže funkcia je tu veľmi vhodná.

Po prvé, prideľujeme bunku, kde sa bude vypočítaná suma. Ďalej zavolajte sprievodcu funkcií. Toto je ikona fx. v riadku vzorca. Ďalej hľadáme funkciu v zozname, sú tiché a kliknete na to. Otvorí sa dialógové okno, kde na vyriešenie tejto úlohy, musíte vyplniť iba dve (prvé) polia týchto troch navrhovaných.

Preto som nazval tento príklad zjednodušený. Prečo 2 (dva) z 3 (tri)? Pretože naše kritérium sa nachádza v rozsahu sústava.

V poli "Range" je uvedená oblasť tabuľky Excel, kde sú všetky zdroje hodnoty umiestnené, z ktorého potrebujete niečo vybrať a potom zložiť. Zvyčajne sa nastaví pomocou myši.

V poli "kritérium" sa bude zvolený stav, v ktorom vzorec. V našom prípade špecifikujeme "\u003e 70". Ak nevkladajte citácie, potom sú nahrané. Všeobecne platí, že takéto kritérium by malo byť v úvodzoch.

Posledné pole "dapad_suming" nevyplní, pretože je už uvedený v prvom poli.

Funkcia teda tount tichá trvá kritérium a začína vybrať všetky hodnoty zo zadaného rozsahu, ktorý spĺňa špecifikované kritérium. Po tom, všetky vybrané hodnoty sú zložité. To je, ako funguje algoritmus.

Vyplňte funkcie v sprievodcovi funkcií, stlačte veľké tlačidlo "ENTER" na klávesnici, alebo v okne sprievodcu, dáme myš v poriadku. Pre SIMCTing by sa mala vypočítaná hodnota zobraziť na mieste funkcie zadanej funkcie. V mojom príklade sa ukázalo 224ks. To znamená, že celková hodnota tovaru predávaného v množstve viac ako 70 kusov bola 224PC. (Toto je vidieť v ľavom dolnom rohu okna Sprievodcu ešte pred stlačením tlačidla "OK"). To je všetko. Bol to zjednodušený príklad, keď kritérium a summatačný rozsah je na jednom mieste.

Pozrime sa na príklad, keď kritérium sa nezhoduje s rozsahom súčtu. Táto situácia sa vyskytuje oveľa častejšie. Zvážte tie isté podmienené údaje. Potrebujeme zistiť sumu, ktorá nie je viac či nišia ako akákoľvek hodnota, ale množstvo špecifickej skupiny tovaru, priznanie, skupiny

Ak to chcete urobiť, znova uvoľnite bunku s budúcim počtom množstva a zavolajte sprievodcu funkcií. Teraz v prvom okne špecifikujeme rozsah, kde kritérium je obsiahnuté výberom pre súčet, to znamená, že je to stĺpec s menami skupín výrobkov. Ďalej je samotné kritérium predpísané buď manuálne, zanecháva záznam "skupina G" v príslušnom poli alebo jednoducho označte bunku myšej požadovaným kritériom. Posledné okno je rozsah, v ktorom sa nachádza súhrnné údaje. Uvádzam ho pomocou myši, ale tlač nie je dovolené skóre manuálne.

Výsledkom bude súčet tovaru predávaného zo skupiny G - 153PC.

Tak sme sa pozreli na to, ako vypočítať jednu sumu na jedno špecifické kritérium. Úloha je však častejšie, keď je potrebné vypočítať niekoľko sumy pre niekoľko kritérií. Nie je nič jednoduchšie! Musíme napríklad zistiť množstvo tovaru predaného pre každú skupinu. Že máte na mysli 4 (štyri) hodnoty v 4. (štyroch) skupinách (A, B, B a D). Na to, zvyčajne niekde na Export Export sa vykoná zoznam skupín vo forme samostatnej dosky. Je jasné, že mená skupín by mali presne zhodovať s menami skupín v tabuľke Zdroj. Ihneď pridajte konečnú čiaru, kde je suma stále nulová.

Vzorec je potom predpísaný pre prvú skupinu a natiahne sa všetkým ostatným. Tu potrebujete venovať pozornosť relativite odkazov. Rozsah s kritériami a rozsahom súhrnu musí byť absolútnymi odkazmi, takže pri natiahnutí vzorec nebudú "ísť dole", ale samotné kritérium, najprv musíte zadať myš (a nie registrovať manuálne), po druhé, Musí existovať relatívny odkaz, takže každá suma má svoje vlastné kritérium súhrn.

Vyplnené polia Sprievodca poľa s podobným výpočtom budú vyzerať takto.

Ako možno vidieť, pre prvú skupinu, a množstvo predaného tovaru predstavovalo 161ks (dolný dolný roh vzoru). Teraz kliknite na ENTER a roztiahnite vzorec nadol.

Vypočítali sa všetky sumy a ich celkový výsledok je 535, ktorý sa zhoduje s výsledkom v zdrojových údajoch. Takže všetky hodnoty boli zhrnuté, nič neteškali. Posledná možnosť použitia bola tichá, osobne sa stretávam dosť často.

Príklad použitia funkcie je tichý na porovnanie údajov

Ako som už viac zaznamenal, funkcia je tichá, môžete použiť rozumné pre väzbu údajov. V skutočnosti, ak zhrnutíte jednu hodnotu, potom bude táto hodnota. Táto aritmetická vlastnosť nie je hriech na použitie v prospech. Je dokonca možné, že Excel dizajnéri nevedia o takomto barbarskom použití ich mozgov.

Stručne povedané, ľahko sa ľahko upravia na banda údajov ako alternatívu k funkcii FRP. Prečo používať hmotnosť, ak je ARP? Vysvetlím. Po prvé, ticho, na rozdiel od EĽS, necitlivého do formátu údajov a nevydáva chybu, v ktorej na ňu čaká; Po druhé, tiché namiesto chýb z dôvodu nedostatku hodnôt na daný kritérium otázky 0 (nula), čo umožňuje bez zbytočných gestov na výpočet výsledkov rozsahu so vzorcom. Existuje však jeden mínus. Ak sa v požadovanej tabuľke, akékoľvek kritérium opakuje, potom sú zodpovedajúce hodnoty zhrnuté, čo nie je vždy "pull-up". Je lepšie byť na stráži. Na druhej strane je často potrebné vytiahnuť hodnoty na určené miesto, zatiaľ čo konvertované polohy sú zložené. Stačí poznať vlastnosti funkcie ticho a používať podľa návodu na obsluhu.

Teraz zvážte príklad, ako je funkcia tichá, ukáže sa, že je vhodnejšie na uťahovanie údajov ako UPR. Nechajte údaje z príkladu, ktoré predávate určitý tovar v januári. Chceme vedieť, ako sa vo februári zmenili. Porovnanie je vhodné vyrábať v tej istej tablete, po pridaní ďalšieho stĺpca doprava a vyplniť svoje údaje na február. Niekde v inom súbore Exhelev existujú štatistiky pre február v celom rozsahu, ale chceme analyzovať tieto pozície, pre ktoré sa vyžaduje z veľkého súboru s predajovou štatistikou všetkého tovaru, utiahnite požadované hodnoty do nášho znaku. Ak chcete začať, skúste použiť vzorec UPR. Ako kritérium budeme používať kód produktu. Výsledok je na obrázku.

Je zrejmé, že jedna poloha nezachytila \u200b\u200ba namiesto numerickej hodnoty sa vydáva chyba # n / d. S najväčšou pravdepodobnosťou, vo februári sa tento produkt jednoducho nepredával, a preto je v databáze pre február neprítomný. Výsledkom je, že chyba # n / d je uvedená v súčte. Ak nie je veľa pozícií, potom problém nie je veľký, stačí manuálne odstrániť chybu a množstvo bude správne prepočítané. Počet riadkov však možno merať stovkami a počítať na manuálnom nastavení nie je úplne správne riešenie. Teraz použijeme vzorec namiesto HDP.

Výsledok je rovnaký, len namiesto chyby # n / d, poskytuje nulu, ktorá vám umožní normálne vypočítať množstvo (alebo iné indikátor, napríklad priemer) v konečnom riadku. To je hlavná myšlienka, prečo musia niekedy použiť namiesto HDP. S veľkým počtom pozícií bude účinok ešte hmatateľný.

V skutočnosti je často potrebné pracovať s veľkými dátovými poliami, ktoré opakovane prekračujú rozmery monitora. Ak chcete vidieť a chyby sledovania je veľmi ťažké. Ale všetky škály plávajú v následných výpočtoch. Takéto príklady nie sú vhodné na demontáž pomocou screenshots v článku, takže vás pozývam, aby ste videli špeciálne nahraný video jazyk o funkcii.

A tu je ďalšia lekcia o tichom