Swyddogaeth SUMIF yn Excel a swm yn ôl amodau lluosog

Mae Excel yn rhaglen hynod ymarferol. Mae hyd yn oed y set nodwedd adeiledig yn ddigon i gwblhau bron unrhyw dasg. Ac ar wahân i'r rhai safonol, sy'n gyfarwydd i lawer, mae yna hefyd rai nad oes llawer o bobl wedi clywed amdanyn nhw. Ond ar yr un pryd, nid ydynt yn peidio â bod yn ddefnyddiol. Mae ganddynt arbenigedd culach, ac nid oes eu hangen bob amser. Ond os ydych chi'n gwybod amdanynt, yna ar adeg dyngedfennol gallant fod yn ddefnyddiol iawn.

Heddiw byddwn yn siarad am un o swyddogaethau o'r fath - CRYNODEB.

Os yw'r defnyddiwr yn wynebu'r dasg o grynhoi sawl gwerth, gan ganolbwyntio ar feini prawf penodol, yna mae angen defnyddio'r swyddogaeth CRYNODEB. Mae'r fformiwla sy'n defnyddio'r swyddogaeth hon yn cymryd yr amodau hyn fel dadleuon, yna'n crynhoi'r gwerthoedd sy'n eu bodloni, ac yna mae'r gwerth a ddarganfuwyd yn cael ei roi i mewn i'r gell y mae wedi'i ysgrifennu ynddi. 

Swyddogaeth SUMIFS Disgrifiad Manwl

Cyn ystyried y swyddogaeth CRYNODEB, rhaid i chi ddeall yn gyntaf beth yw fersiwn symlach ohoni - SYMIAU, gan mai arno y mae'r swyddogaeth yr ydym yn ei hystyried yn seiliedig. Mae'n debyg bod pob un ohonom eisoes yn gyfarwydd â dwy swyddogaeth a ddefnyddir yn aml - SUM (sy'n crynhoi gwerthoedd) a OS (profi gwerth yn erbyn amod penodedig).

Os byddwch chi'n eu cyfuno, byddwch chi'n cael swyddogaeth arall - SYMIAU, sy'n gwirio'r data yn erbyn meini prawf a bennir gan y defnyddiwr ac yn adio'r niferoedd hynny sy'n bodloni'r meini prawf hynny yn unig. Os byddwn yn siarad am y fersiwn Saesneg o Excel, yna gelwir y swyddogaeth hon yn SUMIF. Mewn geiriau syml, mae'r -language name yn gyfieithiad uniongyrchol o'r un Saesneg. Gellir defnyddio'r swyddogaeth hon at amrywiaeth o ddibenion. Yn benodol, gellir ei ddefnyddio fel dewis arall VPR, hynny yw, ysgrifennu i lawr

Y prif wahaniaeth rhwng y swyddogaeth CRYNODEB  o'r swyddogaeth arferol SYMIAU yw bod nifer o feini prawf yn cael eu defnyddio. Mae ei gystrawen yn eithaf cymhleth ar yr olwg gyntaf, ond o edrych yn fanylach, mae'n ymddangos bod rhesymeg y swyddogaeth hon yn syml iawn. Yn gyntaf mae angen i chi ddewis yr ystod lle bydd y data'n cael ei wirio, ac yna gosod yr amodau ar gyfer cydymffurfiad â'r dadansoddiad. A gellir cyflawni llawdriniaeth o'r fath ar gyfer nifer eithaf mawr o amodau.

Y gystrawen ei hun yw:

SUMIFS(sum_range, condition_range1, condition1, [condition_range2, condition2], …)

Mewn mannau addas mae angen gosod araeau o gelloedd sy'n addas mewn achos penodol. 

Edrychwn ar y dadleuon yn fwy manwl:

1. Swm_ystod. Mae angen y ddadl hon, yn ogystal â'r ystod o amod 1 ac amod 1. Mae'n set o gelloedd y mae angen eu crynhoi.
2. Cyflwr_ystod1. Dyma'r ystod lle bydd y cyflwr yn cael ei wirio. Mae wedi'i baru â'r ddadl nesaf - Amod 1. Mae crynhoi gwerthoedd sy'n cyfateb i'r maen prawf yn cael ei wneud o fewn y celloedd a nodir yn y ddadl flaenorol.
3. Cyflwr1. Mae'r ddadl hon yn nodi'r meini prawf i'w gwirio yn eu herbyn. Gellir ei osod, er enghraifft, fel hyn: “> 32”.
4. Ystod cyflwr 2, Amod 2… Yma, gosodir yr amodau canlynol yn yr un modd. Os oes angen nodi mwy nag ychydig o amodau, yna ychwanegir y dadleuon Amrediad Amod 3 ac Amod 3. Yr un yw'r gystrawen ar gyfer y dadleuon canlynol.

Mae'r swyddogaeth yn caniatáu prosesu uchafswm o hyd at 127 pâr o amodau ac ystodau. 

Gallwch ei ddefnyddio mewn sawl maes ar unwaith (byddwn yn rhoi ychydig yn unig, mae'r rhestr mewn gwirionedd hyd yn oed yn hirach):

1. Cyfrifo. Er enghraifft, mae'n dda defnyddio'r swyddogaeth CRYNODEB creu adroddiadau cryno, fesul chwarter ar gyfer gwariant dros swm penodol, er enghraifft. Neu crëwch adroddiad ar un cynnyrch o gategori pris penodol.
2. Rheoli gwerthiant. Yma hefyd gall y swyddogaeth fod yn ddefnyddiol iawn. Er enghraifft, rydym yn wynebu'r dasg o grynhoi cost nwyddau a werthwyd i gwsmer penodol ar amser penodol yn unig. Ac mewn sefyllfa o'r fath, y swyddogaeth CRYNODEB Gall fod yn ddefnyddiol iawn.
3. Addysg. Byddwn yn rhoi mwy o enghreifftiau ymarferol o'r maes hwn heddiw. Yn benodol, gallwch ei ddefnyddio i gael crynodeb o raddau myfyrwyr. Gallwch ddewis ar gyfer pwnc unigol neu ar gyfer graddau unigol. Gall person osod sawl maen prawf ar unwaith ar gyfer dewis yr asesiad, sy'n gyfleus iawn ac a all arbed llawer o amser.

Fel y gallwch weld, mae'r ystod o gymwysiadau ar gyfer y swyddogaeth hon yn eang iawn. Ond nid dyma ei unig rinwedd. Gadewch i ni edrych ar ychydig mwy o fuddion sydd gan y nodwedd hon:

1. Y gallu i osod meini prawf lluosog. Pam fod hyn yn fantais? Gallwch ddefnyddio'r swyddogaeth arferol SYMIAU! Ac i gyd oherwydd ei fod yn gyfleus. Nid oes angen gwneud cyfrifiadau ar wahân ar gyfer pob un o'r meini prawf. Gellir rhaglennu pob cam gweithredu ymlaen llaw, hyd yn oed cyn hynny. sut bydd y tabl data yn cael ei ffurfio. Mae hwn yn arbediad amser gwych.
2. Awtomatiaeth. Yr oes fodern yw'r oes o awtomeiddio. Dim ond y person sy'n gwybod sut i awtomeiddio ei waith yn iawn all ennill llawer. Dyna pam y gallu i feistroli Excel a'r swyddogaeth CRYNODEB yn arbennig, mor bwysig i unrhyw berson sydd eisiau adeiladu gyrfa. Mae gwybod un swyddogaeth yn caniatáu ichi gyflawni sawl gweithred ar unwaith, fel un. A dyma ni'n symud ymlaen at fudd nesaf y nodwedd hon.
3. Arbed amser. Dim ond oherwydd y ffaith bod un swyddogaeth yn cyflawni nifer o dasgau ar unwaith.
4. Symlrwydd. Er gwaethaf y ffaith bod y gystrawen yn eithaf trwm ar yr olwg gyntaf oherwydd ei swmp, mewn gwirionedd, mae rhesymeg y swyddogaeth hon yn syml iawn. Yn gyntaf, dewisir ystod o ddata, yna ystod o werthoedd, a fydd yn cael eu gwirio i weld a ydynt yn cydymffurfio â chyflwr penodol. Ac wrth gwrs, rhaid nodi'r cyflwr ei hun hefyd. Ac felly sawl gwaith. Mewn gwirionedd, mae'r swyddogaeth hon yn seiliedig ar un lluniad rhesymegol yn unig, sy'n ei gwneud yn symlach na'r un adnabyddus VPR er gwaethaf y ffaith y gellir ei ddefnyddio at yr un dibenion, gan ystyried nifer fwy o feini prawf hefyd. 

Nodweddion defnyddio swyddogaeth SUMIFS

Mae sawl nodwedd o ddefnyddio'r swyddogaeth hon y mae angen i chi dalu sylw iddynt. Yn gyntaf oll, mae'r swyddogaeth hon yn anwybyddu ystodau â llinynnau testun neu nulliau, gan na ellir ychwanegu'r mathau hyn o ddata at ei gilydd mewn patrwm rhifyddol, dim ond cydgatenu fel llinynnau. Ni all y swyddogaeth hon wneud hyn. Mae angen i chi hefyd dalu sylw i'r amodau canlynol:

1. Gallwch ddefnyddio'r mathau hyn o werthoedd fel amodau ar gyfer dewis celloedd i ychwanegu ymhellach y gwerthoedd a gynhwysir ynddynt: gwerthoedd rhifol, mynegiadau boolean, cyfeiriadau cell, ac ati. 
2. Os yw testun, ymadroddion rhesymegol neu arwyddion mathemategol yn cael eu gwirio, yna mae meini prawf o'r fath yn cael eu pennu trwy ddyfyniadau.
3. Methu defnyddio termau mwy na 255 nod.
4. Mae'n bosibl defnyddio meini prawf bras ar gyfer dewis gwerthoedd gan ddefnyddio wildcards. Defnyddir y marc cwestiwn i ddisodli un nod, ac mae angen yr arwydd lluosi (seren) i ddisodli nodau lluosog. 
5. Mae gwerthoedd Boole sydd yn yr ystod grynodeb yn cael eu trosi'n awtomatig i werthoedd rhifol yn ôl eu math. Felly, mae'r gwerth "TRUE" yn troi'n un, a "FALSE" - yn sero. 
6. Os yw #VALUE! gwall yn ymddangos mewn cell, mae'n golygu bod nifer y celloedd yn yr ystodau cyflwr a chrynhoad yn wahanol. Mae angen i chi wneud yn siŵr bod maint y dadleuon hyn yr un peth. 

Enghreifftiau o ddefnyddio swyddogaeth SUMIFS

swyddogaeth CRYNODEB ddim mor gymhleth ag y mae'n ymddangos ar yr olwg gyntaf, mae'n troi allan. Ond i gael mwy o eglurder, gadewch i ni edrych ar rai enghreifftiau ymarferol o sut y gallwch chi ddefnyddio'r swyddogaeth CRYNODEB. Bydd hyn yn ei gwneud hi'n llawer haws ymchwilio i'r pwnc.

Crynhoad cyflwr ystod ddeinamig

Felly gadewch i ni ddechrau gyda'r enghraifft gyntaf. Gadewch i ni ddweud bod gennym dabl sy'n cynnwys gwybodaeth am sut mae myfyrwyr yn ymdopi â'r cwricwlwm mewn pwnc penodol. Mae set o raddau, asesir perfformiad ar raddfa 10 pwynt. Y dasg yw dod o hyd i'r radd ar gyfer arholiad y myfyrwyr hynny y mae eu henw olaf yn dechrau gyda'r llythyren A, a'u sgôr isaf yw 5.

Mae'r tabl yn edrych fel hyn.

Er mwyn i ni gyfrifo cyfanswm y sgôr yn seiliedig ar y meini prawf a ddisgrifir uchod, mae angen i ni gymhwyso'r fformiwla ganlynol.

Gadewch i ni ddisgrifio'r dadleuon yn fwy manwl:

1. C3: C14 yw ein hystod crynhoi. Yn ein hachos ni, mae'n cyd-fynd â'r ystod cyflwr. Oddi bydd yn cael ei ddewis pwyntiau a ddefnyddir i gyfrifo'r swm, ond dim ond y rhai sy'n dod o dan ein meini prawf.
2. “>5” yw ein cyflwr cyntaf.
3. B3:B14 yw'r ail amrediad crynhoi sy'n cael ei brosesu i gyd-fynd â'r ail faen prawf. Gwelwn nad oes unrhyw gyd-ddigwyddiad â'r ystod o grynodebau. O hyn deuwn i'r casgliad y gall ystod y crynhoi ac ystod y cyflwr fod yn union yr un fath neu beidio. 
4. “A*” yw'r ail ystod, sy'n pennu'r dewis o farciau yn unig ar gyfer y myfyrwyr hynny y mae eu henw olaf yn dechrau ag A. Yn ein hachos ni, mae seren yn golygu unrhyw nifer o nodau. 

Ar ôl cyfrifiadau, rydym yn cael y tabl canlynol.

Fel y gallwch weld, mae'r fformiwla yn crynhoi'r gwerthoedd yn seiliedig ar yr ystod ddeinamig ac yn seiliedig ar yr amodau a bennir gan y defnyddiwr.

Crynhoad dethol yn ôl amod yn Excel

Nawr mae'n debyg ein bod am gael gwybodaeth am ba nwyddau a gludwyd i ba wledydd yn ystod y chwarter diwethaf. Ar ôl hynny, darganfyddwch gyfanswm y refeniw o gludo nwyddau ar gyfer Gorffennaf ac Awst.

Mae'r bwrdd ei hun yn edrych fel hyn. 

Er mwyn pennu'r canlyniad terfynol, mae angen fformiwla o'r fath arnom.

=(СУММЕСЛИМН(D2:D14;A2:A14;»=июнь»;B2:B14;»Товар_2″;C2:C14;»Казахстан»)+(СУММЕСЛИМН(D2:D14;A2:A14;»=август»;B2:B14;»Товар_2″;C2:C14;»Казахстан»)))

O ganlyniad i'r cyfrifiadau a wneir gan y fformiwla hon, rydym yn cael y canlyniad canlynol.

Sylw! Mae'r fformiwla hon yn edrych yn eithaf mawr er mai dim ond dau faen prawf a ddefnyddiwyd gennym. Os yw'r ystod ddata yr un peth, yna gallwch leihau hyd y fformiwla yn sylweddol, fel y dangosir isod.

Swyddogaeth SUMIFS i grynhoi gwerthoedd ar draws amodau lluosog

Nawr gadewch i ni roi enghraifft arall i'w darlunio. Yn yr achos hwn, mae'r tabl yn aros yr un fath ag yn yr achos blaenorol. 

Rydyn ni'n defnyddio'r fformiwla ganlynol (ond rydyn ni'n ei ysgrifennu fel fformiwla arae, hynny yw, rydyn ni'n ei nodi trwy'r cyfuniad allweddol CTRL + SHIFT + ENTER).

=СУММ(СУММЕСЛИМН(D2:D14;B2:B14;»Товар_1″;C2:C14;{«Китай»;»Грузия»}))

Ar ôl y swyddogaeth CRYNODEB yn crynhoi'r amrywiaeth o werthoedd yn seiliedig ar y meini prawf a nodir yn y fformiwla (hynny yw, gwledydd Tsieina a Georgia), mae'r arae sy'n deillio o hyn yn cael ei grynhoi gan y swyddogaeth arferol SWM, sy'n cael ei ysgrifennu fel fformiwla arae.

Os cafodd yr amodau eu pasio fel cysonyn arae ar gyfer mwy nag un pâr, yna bydd y fformiwla yn rhoi canlyniad anghywir.

Nawr gadewch i ni edrych ar y tabl sy'n cynnwys y cyfansymiau.

Fel y gwelwch, rydym wedi llwyddo. Byddwch yn sicr yn llwyddo hefyd. Llwyddiant mawr yn y maes hwn. Mae hon yn swyddogaeth syml iawn y gall person sydd newydd osod troed ar y llwybr dysgu Excel ei ddeall. Ac rydym eisoes yn gwybod bod y swyddogaeth CRYNODEB yn eich galluogi i fod yn effeithiol mewn unrhyw faes gweithgaredd, o gyfrifeg i addysg hyd yn oed. Hyd yn oed os ydych chi'n adeiladu gyrfa mewn unrhyw faes arall nad yw wedi'i ddisgrifio uchod, bydd y nodwedd hon yn dal i'ch helpu i ennill arian. Dyna pam ei bod hi'n werthfawr.

Yn bwysicaf oll, mae'n caniatáu ichi arbed amser, sydd, yn anffodus, yn adnodd cyfyngedig. Mae'n ymddangos bod yna ychydig eiliadau i gymhwyso dwy swyddogaeth, ond pan fydd yn rhaid i chi berfformio nifer enfawr o weithrediadau ailadroddus, yna mae'r eiliadau hyn yn ychwanegu hyd at oriau y gellid eu gwario ar rywbeth arall. Felly rydym yn argymell eich bod yn ymarfer defnyddio'r nodwedd hon. Ar ben hynny, mae'n hynod o syml.