Kuidas valem töötab, kui. Funktsiooni kasutamise näited vaikivad Excelis

Funktsiooni kasutatakse selleks, et kasutada kindlaksmääratud kriteeriumile vastava vahemiku väärtuste kokkuvõtmiseks.

Funktsiooni kirjeldus

Funktsiooni kasutatakse selleks, et kasutada kindlaksmääratud kriteeriumile vastava vahemiku väärtuste kokkuvõtmiseks. Näiteks kolonnis numbritega on vaja kokku võtta ainult väärtusi, mis ületavad numbrit 5. Selle jaoks saate kasutada määratud valemit allpool:
\u003d Summeeritud (B2: B25, "\u003e 5") Selles näites kontrollitakse kriteeriumi täitmist summeeritavad väärtused. Vajadusel võib seisundit rakendada ühele vahemikule ja vastavate väärtuste kokkuvõtmine teisest vahemikust. Näiteks valem:
\u003d B2: B5; "Ivan"; c2: C5) ainult C2 väärtused: C5 vahemik on kokku võetud, mille jaoks B2: B5 vahemikus on "Ivan".
Teine näide funktsiooni kasutamise kohta kuvatakse alltoodud joonisel:

Näide kasutamisest on vaikne kasumi arvutamiseks tingimusega

Süntaks

\u003d Silent (vahemik; seisund; [kaugus_suuming])

Argumendid

seisund kaugus_suuming

Kohustuslik argument. Rakkude valikud, mida mõõdetakse kriteeriumide alusel. Rakud igas vahemikus peab sisaldama numbreid, nimesid, massiivi või viiteid numbritele. Tühjad rakud ja teksti väärtused sisaldavad rakud vahele jäetakse.

Kohustuslik argument. Tingimus kujul numbri, ekspressiooni, linke rakkude, teksti või funktsiooni, mis määrab, millised rakud tuleb kokku võtta. Näiteks võib seisundit esindada järgmiselt: 32, "\u003e 32", B5, "32", "õunad" või täna ().

Oluline! Kõik tekstilised tingimused ja tingimused loogiliste ja matemaatiliste märkidega tuleb sõlmida kahekordse hinnapakkumistega ("). Kui tingimus on number, ei ole vaja hinnapakkumisi kasutada.

Valikuline argument. Rakud, mille väärtused on kokku võetud, kui need erinevad vahemikku määratletud rakkudest. Kui argumentide valik_sum jäetakse välja, Microsoft Excel. Kokkuvõttes on argumentide vahemikus määratletud rakud (samad rakud, millele tingimus on rakendatud).

Märkused

  • Argumendis saab seisundit kasutada metamärke: küsimärk (?) Ja tärniga (*). Küsimuste märk vastab ühele igale iseloomule ja tärniga - mis tahes tähemärkide järjestus. Kui soovite leida küsimuse kaubamärgi (või tärniga) otse, peate panna märk "Tilda" (~).
  • Funktsioon on vaikne tagastab ebaõigeid tulemusi, kui seda kasutatakse stringiga pikemate stringidega pikemaks kui 255 tähemärki.
  • Argument Range_sum ei pruugi väheneda suurusega argumentide vahemikuga. Tegelike rakkude kindlaksmääramisel kasutatakse argumentide vahemiku ülemist vasakpoolset rakku esialgse ja seejärel argumentide vahemiku vahemiku rakkude vahemiku rakke.

Me lisame nende väärtuste lisamise liinidel, mille väljad vastavad korraga kahele kriteeriumile (tingimus ja). Kaaluge teksti kriteeriume, numbrilisi ja kriteeriume kuupäevadel. Me analüüsime COMPALIMNi funktsiooni ( ), Inglise versioon SUMIFS ().

Allika tabelina võtke tabel kahe veerguga (väljad): tekst " Viljad"Ja numbriline" Kogus laos"(Vt näite faili).

Task1 (1 teksti kriteerium ja 1 numbriline)

Leia arv kastid kaupade teatud Viljad Jamis Kastide tasakaal laost Mitte vähem minimaalne. Näiteks kastide arv kaubaga virsikud (kamber D. 2 ), Kes on ülejäänud kastid laos\u003e \u003d 6 (kamber E. 2 ) . Me peame saama tulemuse 64. Arvutust saab realiseerida valemite komplekt, anname mitu (vt näiteks faili Lehekülg ja number):

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

Süntaksi omadused: SmereVulimn (intervall_suuming; intervall_liviya1; seisund1; intervall_lokulation2; tingimus2 ...)

  • B2: B13 Intervall_Suming - kokkusaamise rakud, sealhulgas nimed, massiivid või viited, mis sisaldavad numbreid. Tühjad väärtused ja tekst ignoreeritakse.
  • A2: A13.ja B2: B13 intervall_lovia1; intervall_loculia2; ... moodustavad 1 kuni 127 vahemikke, milles vastava seisundi kontrollitakse.
  • D2.ja "\u003e \u003d" & E2 seisukord1; Seisund2; ... See on 1 kuni 127 tingimust numbri, väljenduse kujul, lingid rakule või tekstile, milles määratletakse rakud kokku.


Argumentide järjekord valatakse sumümbeni () funktsioonidesse ja nad vaikivad (). Sumymelimn () argument intervall_Suming See on esimene argument ja vaikne () - kolmas. Nende sarnaste funktsioonide kopeerimisel ja redigeerimisel on vaja tagada argumentide täpsustamine õiges järjekorras.

2. Teine võimalus \u003d Summepaatiline ((a2: A13 \u003d D2) * (B2: B13); - (B2: B13\u003e \u003d E2)
Kirjeldame üksikasjalikumalt kliendifunktsiooni kasutamist ():

  • Arvutuse A2: A13 \u003d D2 on massiiv (FALSE: Love: Lies: Tõde: Tõde: Tõde: FALSE: FALSE: FALSE: FALSE: Vale: vale) väärtus vastab väärtuse kokkusattumusele veerus AGA Kriteeriumid, s.o. Sõna virsik. Massiivi saab näha esile A2: A13 \u003d D2-s ja seejärel vajutades;
  • B2: B13 arvutamise tulemus (3: 5: 11: 98: 4: 8: 56: 2: 4: 6: 10: 11), s.o. lihtsalt väärtused veerust B. ;
  • The elementide korrutamine of Arrays (A2: A13 \u003d D2) * (B2: B13) on (0: 0: 0: 0: 0: 8: 56: 0: 0: 0: 0: 0). Väärtuse väärtuse arvu korrutamisel saadakse 0; ja tõe väärtus (\u003d 1) number saadakse;
  • Me analüüsime teist tingimust: arvutuse tulemus - (B2: B13\u003e \u003d E2) on massiiv (0: 0: 1: 1: 0: 1: 1: 0: 0: 1: 1: 1). Väärtused veerus " Kastide arv laos", Mis vastab kriteeriumile\u003e \u003d E2 (s.o\u003e \u003d 6) vastavad 1;
  • Lisaks muutub suvandi () paaride funktsiooni funktsiooni elemendid massiivide elemendid ja kokkuvõtlikult saadud tooted. Me saame - 64.

3. Kompassifunktsiooni teine \u200b\u200bteostusvariant on valem \u003d SUMPERED ((A2: A13 \u003d D2) * (B2: B13) * (B2: B13\u003e \u003d E2)).

Vajadusel saab kuupäevad sisestada otse valemis \u003d Smerevylmn (B6: B17; A6: A17; "\u003e\u003e\u003e 01/15/2010"; A6: A17; "<=06.03.2010")

Valikutingimuste kuvamiseks tekstisõks, kasutage valemit \u003d "Müük ajavahemikuks" ja tekst (D6; "dd.mm.yg") & "" By "& Text (E6;" dd.mm.yg ")

Viimasel valemi kasutamisel.

Task4 (kuu)

Veidi muutke eelmise ülesande seisundit: leiame kogu müügi kuus (vt näiteks faili Lehtkuu).

Valemid on ehitatud sarnaselt ülesandele 3, kuid kasutaja tutvustab mitte 2 kuupäeva, vaid kuu nime (eeldatakse, et tabel on 1 aasta jooksul).


Kolmanda ülesande lahendamiseks näeb välja selline konfigureeritud autofiltriga tabel (vt näiteks faili Lehed 2 kuupäeva).


Varem tabelis tuleb muuta ja võimaldada tulemuste string.

Kokkuvõte väärtustest, mis vastavad määratud kriteeriumile (tingimus), kasutatakse funktsiooni; , Inglise versioon SUMIF ().

Süntaksi funktsioon

Smerei(Vahemik; Seisukord; [Kaugus_suuming]

Vahemik - rakkude valik, milles otsitakse vastava argumendi väärtused Seisukord. Vahemik võib sisaldada numbreid, kuupäevad, tekstiväärtusi või linke teistesse rakkudesse. Kui teine \u200b\u200bargument - Kaugus_suuming - välja jäetud, siis argument Vahemik Samuti on vahemik, mille abil on tehtud rahuldava argumendi väärtused. Seisukord (sel juhul Vahemik Peab sisaldama numbrit).

Seisukord - kriteerium numbri, ekspressiooni või teksti kujul, mis määrab kindlaks, millised rakud tuleb kokku võtta. Näiteks argument Seisukord Seda saab väljendada 32, "õunad" või "\u003e 32".

Kaugus_suuming - vahemikus rakud sisaldavad numbrid, mis on kokku võetud juhul, kui rakud rakud argument Vahemikvastavad argumendile Seisukord. Kaugus_suuming - Vabatahtlik argument. Kui see on välja jäetud, tehakse kokkuvõte argumendis märgitud rakkude vahemikus Vahemik.

Näited

Mõtle juhul, kui argument Kaugus_suuming välja jäetud. Sellisel juhul tehakse summeerimine esimeses argumendis märgitud rakkude vahemikus Vahemik (s.o see peab sisaldama numbrit). Samuti otsib see vastava argumendi väärtusi SeisukordSeejärel võetakse kokku. Olgu see vahemik B5: B15 Vaadake näite faili.

Me lahendame ülesanded:

  • leia kõigi numbrite summa rohkem kui või võrdne 10. Vastus: 175. Vormel: \u003d Smerey (B5: B15; "\u003e \u003d 10")
  • leia kõigi numbrite summa alla 10. Vastus: 42. Vormel: \u003d Silent (B5: B15; "<=10")
  • leia kõigi positiivsete numbrite summa vahemikus. Valem: \u003d Smeerunud (B5: B15; "\u003e 0"). Alternatiivina, mis kasutab kliente (), näeb välja selline: \u003d SUMPERED (B5: B15) * (B5: B15\u003e 0))

Ülesande kuju tingimused(Kriteeriumid) piisavalt paindlik. Näiteks valemis \u003d SMERELY (B5: B15; D7 & D8) kriteerium<=56 задан через ссылку D7&D8 : в D7 Sisaldab teksti väärtust<=, а в D8. - number 56 (vt joonis allpool). Näiteks kasutaja saab hõlpsasti muuta kriteeriumi rakus D7 . Samaväärne valem \u003d Silent (B5: B15; "<=56") или \u003d Silent (B5: B15; "<="&56) või \u003d Silent (B5: B15; "<="&D8) või \u003d SMERELY (B5: B15; D7 & 56).

Hea pärastlõunal, kallis abonentide ja blogi külastajate veebisait. Hiljuti me arvasime ettevõtte valemile ja otsustasin kirjutada teise väga kasuliku funktsiooni Excel teise väga kasuliku funktsiooni kohta. Smerei. Mõlemad funktsioonid on võimelised "siduma" andmeid erinevatest allikatest (tabelid) võtmeväljale ja teatud tingimustel on vahetatavad. Samal ajal on nende ametisse nimetamise ja kasutamise olulised erinevused.

Kui rakenduse loovutamine on lihtsalt "tõmmata" andmed ühest Exceli kohast teise, siis kasutatakse neid, et võtta kokku numbriliste andmete kokkuvõtmiseks vastavalt antud kriteeriumile. Selle valemi peamine eesmärk on andmete kokkuvõtmine vastavalt mõnele tingimusele. Umbes see on kirjutatud abiga. Kuid see tööriist saab tõeks muudel eesmärkidel. Näiteks kasutan ma tihti valemit, mida ma kasutan tihti HDP asemel, kuna see ei anna vigu # N / D, kui midagi ei leia. Ja see muudab selle suureks ja kiirendab tööd.

Funktsioon tuleb edukalt kohandada, et lahendada erinevaid ülesandeid. Seetõttu kaalume käesolevas artiklis 1 (ühte), vaid 2 (kaks) näidet. Esimene seostatakse määratud kriteeriumi kokkuvõttega, teine \u200b\u200b- andmete "tõmbamisega", mis on ettevõtte alternatiivina.

Näide summeerimisest kasutades funktsiooni vaikides

Tabelis on esitatud positsioonid, nende kogused, samuti kuuluvad konkreetse grupi (esimene veerg). Kaaluge lihtsustatud kasutamise lihtsustatud kasutamist, kui peame arvutama summa ainult positsioonides, väärtused, mis vastavad teatavale tingimusele. Näiteks tahame teada, kui palju top seda müüakse, s.o. Need, kelle väärtus ületab 70 ühikut. Otsi sellistest kaupadest silmadega ja seejärel kokkuvõtlikult käsitsi mitte väga mugav ja kiiresti, nii et funktsioon on siin väga sobiv.

Esiteks eraldame raku, kus summa arvutatakse. Järgmisena helistage funktsioonide viisardile. See on ikoon fx. Valemis rida. Lisaks otsime nimekirjas funktsiooni, nad vaikivad ja klõpsavad seda. Avaneb dialoogiboks, kuhu selle ülesande lahendamiseks peate täitma ainult kaks väljapakutud kolme (esmalt) välja.


Seetõttu nimetasin seda näidet lihtsustatud. Miks 2 (kaks) 3-st (kolm)? Kuna meie kriteerium asub summeerimise vahemikus.

In "Vahemikus" valdkonnas, Exceli tabeli ala on näidatud, kus kõik allika väärtused asuvad, kust sa pead valima midagi ja seejärel korda. See on tavaliselt komplekt hiirega.

"Kriteeriumi" valdkonnas, tingimus, milles valem valitakse. Meie puhul täpsustame "\u003e 70". Kui te ei pane hinnapakkumisi, siis on nad ise. Kuid üldiselt peaks selline kriteerium olema jutumärkides.

Viimane väli "dapad_suuming" ei täida, kuna see on juba esimeses valdkonnas määratud.

Seega võtab funktsiooni vaikne kriteerium ja hakkab valima kõik kindlaksmääratud vahemiku väärtused, mis vastavad määratud kriteeriumile. Pärast seda korda kõik valitud väärtused. See tähendab, kuidas funktsioon algoritm töötab.

Täitke funktsiooni funktsiooni Wizard, vajutage klaviatuuril suure "Enter" nupule või Wizard aknasse, anname hiire ok. SIMCTING jaoks peaks arvutatud väärtus ilmuma funktsiooni sisestatud funktsiooni kohale. Minu näites osutus ta 224 tk. See tähendab, et üle 70 tükki müüdavate kaupade koguväärtus oli 224 ° C. (Seda peetakse viisardi akna alumises vasakus nurgas isegi enne "OK" vajutamist. See on kõik. See oli lihtsustatud näide, kui kriteerium ja summeerimise vahemik on ühes kohas.

Nüüd vaatame näiteks, kui kriteerium ei lange kokku summeerimisvahemikuga. Selline olukord esineb palju sagedamini. Kaaluge samu tingimuslikke andmeid. Vajame, et teada saada summa mitte enam-vähem kui mis tahes väärtuse, kuid konkreetse kaubagrupi summa, tunnistage, grupp

Selleks vabastage taas lahtri tuleviku arvuga ja helistage funktsioonide viisardile. Nüüd esimeses aknas täpsustame vahemikku, kus kriteerium sisaldub kokkusaamise valimisel, st meie puhul on see veerg tooterühmade nimedega. Seejärel on kriteerium ise käsitsi ette nähtud, jättes salvestuse "grupi G" vastavas valdkonnas või lihtsalt näitavad hiirerakku soovitud kriteeriumiga. Viimane aken on vahemik, kus summeerivad andmed asuvad. Ma täpsustan selle hiirega, kuid trükkimine ei tohi käsitsi skoorida.


Tulemuseks on G-153PC-st müüdavate kaupade summa.

Niisiis, me vaatasime, kuidas arvutada ühe summa ühe konkreetse kriteeriumi. Ülesanne on aga sagedamini, kui on vaja arvutada mitu summat mitme kriteeriumi jaoks. Ei ole midagi lihtsamat! Näiteks peame välja selgitama iga rühma jaoks müüdud kaupade hulka. See tähendab nelja (nelja) rühma 4 (nelja) väärtused (A, B, B ja D). Selleks on tavaliselt kusagil Exceli ekspansioonide nimekiri rühmade nimekirja eraldi plaadi kujul. On selge, et rühmade nimed peaksid täpselt kokku langema allika tabelis rühmade nimedega. Lisage kohe lõpliku rea, kus summa on veel null.

Seejärel määratakse valem esimese rühma jaoks ja ulatub kõigile teistele. Siin peate lihtsalt pöörama tähelepanu linkide suhtelisusele. Kriteeriumide ja summeerimise vahemik peab olema absoluutsed viited, nii et valemi venitamisel ei pea nad "alla minema", vaid kriteerium ise, esiteks peate määrama hiire (mitte registreeruma käsitsi registreerida), teiseks, teiseks Seal peab olema suhteline viide, nii et iga summa on oma summeerimise kriteerium.

Sarnase arvutusega sarnaste arvutustega täidetud väljad näivad välja.


Nagu võib näha esimese rühma jaoks ja müüdud kaupade kogus oli 161 tk (mustri vasakus vasakus nurgas). Nüüd klõpsake nuppu Enter ja venitada valemi alla.

Kõik summad arvutati ja nende kogu tulemus on 535, mis langeb kokku lähteandmete tulemusega. Niisiis, kõik väärtused olid kokku võetud, nad ei jäänud midagi. Viimane kasutusvõimalus oli vaikne, ma isiklikult kohtun üsna sageli.

Funktsiooni kasutamise näide vaikib andmete võrdlemiseks

Kuna ma ei ole enam enam märkinud, on funktsioon vaikne, saate kasutada andmete ligamenti mõistlikku. Tõepoolest, kui te kokku ühe väärtuse kokku, siis see väärtus on. See aritmeetiline vara ei ole kasulik kasutada kasu. On isegi võimalik, et Excel disainerid ei tea sellist barbaarset kasutamist oma vaimusünnitust.

Lühidalt öeldes on need kergesti reguleeritavad FRP funktsiooni alternatiivina. Miks kasutada kaalu, kui on olemas ARP? Ma seletan. Esiteks, nad vaikivad, erinevalt EPP-st, andmevormingu suhtes tundmatu ja ei väljasta viga, kus see kõige vähem ootab; Teiseks, nad vaigistavad vigade asemel väärtuste puudumise tõttu antud kriteeriumi küsimustes 0 (null), mis võimaldab ilma tarbetute žesteta arvutada vahemiku tulemusi valemiga. Siiski on üks miinus. Kui soovitud tabelis on iga kriteerium korratakse, siis vastavad väärtused on kokku võetud, mis ei ole alati "pull-up". See on parem olla valvur. Teisest küljest on sageli vaja tõmmata väärtusi määratud asukohta, samas kui konverteeritud positsioonid on volditud. Te peate lihtsalt teadma funktsiooni vaikiva ja kasutamise omadusi vastavalt kasutusjuhendile.

Nüüd kaaluge näidet selle kohta, kuidas funktsioon on vaikne, osutub sobivamaks andmete karmistamisele kui UPR. Olgu andmed näitena, mida müüte mõnda kaupa jaanuaris. Me tahame teada, kuidas nad veebruaris muutunud. Võrdlus on mugav toota samas tabletis pärast teise veeru lisamist paremale ja täitke oma andmed veebruaris. Kuskil teisel Exhelev-failis on veebruari statistika kogu vahemikus, kuid me tahame neid positsioone analüüsida, mille jaoks see on vajalik suurest failist kõikide kaupade müügi statistikaga, kinnitate soovitud väärtusi meie märgile. Alusta, proovige kasutada UPR-i valemit. Kriteeriumina kasutame toote koodi. Tulemuseks on pildil.


On selge, et üks positsioon ei jõudnud järele ja viga # N / D väljastatakse numbrilise väärtuse asemel. Tõenäoliselt ei olnud see toode veebruaris lihtsalt müüdud ja seetõttu puudub see andmebaas veebruaris. Selle tulemusena on viga # N / D on näidatud summa. Kui ei ole palju positsioone, siis probleem ei ole suur, see on piisav, et viga käsitsi kustutada ja summa korrektselt ümber arvutatakse. Rindade arvu saab siiski mõõta sadade võrra ja käsitsi reguleerimise arv ei ole täiesti õige lahus. Nüüd kasutame HDP asemel valemit.


Tulemuseks on sama, ainult selle asemel, et viga # n / d, see annab nulli, mis võimaldab teil tavaliselt arvutada summa (või muu indikaator, näiteks keskmine) lõpp-liinil. See on peamine idee, miks nad peavad mõnikord kasutama HDP asemel. Suur hulk positsioone, mõju on veelgi rohkem käegakatsutavamaks.

Tegelikult on sageli vaja töötada suured andmeside massiivid, mis korduvalt ületada mõõtmed monitori. Vigade nägemiseks ja jälgimiseks on väga raske. Aga kõik shoals ujuvad järgnevate arvutustega. Sellised näited ei ole mugavad demonteerimiseks artikli ekraanipilte, nii et ma kutsun teid, et näha spetsiaalselt salvestatud videokeelt funktsiooni kohta.

Ja siin on veel üks õppetund vaikses



Soovitame lugeda