Excel бағдарламасындағы SUMIF функциясы және бірнеше шарттар бойынша қосындылау

Excel - керемет функционалды бағдарлама. Тіпті кірістірілген мүмкіндіктер жиынтығы кез келген дерлік тапсырманы орындау үшін жеткілікті. Көпшілікке таныс стандарттылардан басқа, аз адамдар естігендері де бар. Бірақ сонымен бірге олар пайдалы болуын тоқтатпайды. Олардың мамандануы тар, және оларға әрқашан қажеттілік бола бермейді. Бірақ егер сіз олар туралы білсеңіз, қиын сәтте олар өте пайдалы болуы мүмкін.

Бүгін біз осындай функциялардың бірі туралы сөйлесетін боламыз - SUMMESLIMN.

Егер пайдаланушының алдында белгілі бір критерийлерге назар аудара отырып, бірнеше мәндерді қорытындылау міндеті тұрса, онда бұл функцияны пайдалану қажет. SUMMESLIMN. Бұл функцияны пайдаланатын формула осы шарттарды аргументтер ретінде қабылдайды, содан кейін оларға сәйкес келетін мәндерді қосады, содан кейін табылған мән жазылған ұяшыққа енгізіледі. 

SUMIFS функциясы Толық сипаттамасы

Функцияны қарастырмас бұрын SUMMESLIMN, алдымен оның қарапайым нұсқасы қандай екенін түсінуіңіз керек – СУММЕСЛИ, өйткені біз қарастырып отырған функция осыған негізделген. Әрқайсымыз жиі қолданылатын екі функциямен бұрыннан таныс шығармыз – SUM (мәндерді қорытындылауды жүзеге асырады) және ЕГЕР (мәнді көрсетілген шартқа қарсы тексереді).

Егер сіз оларды біріктірсеңіз, сіз басқа функцияны аласыз - СУММЕСЛИ, ол деректерді пайдаланушы көрсеткен критерийлерге сәйкес тексереді және сол критерийлерге сәйкес келетін сандарды ғана қосады. Егер Excel бағдарламасының ағылшын тіліндегі нұсқасы туралы айтатын болсақ, онда бұл функция SUMIF деп аталады. Қарапайым сөзбен айтқанда, -language атауы ағылшын тіліндегінің тікелей аудармасы болып табылады. Бұл функцияны әртүрлі мақсаттарда пайдалануға болады. Атап айтқанда, оны балама ретінде пайдалануға болады VPR, яғни жазып алыңыз

Функцияның негізгі айырмашылығы SUMMESLIMN  әдеттегі функциядан СУММЕСЛИ бірнеше критерийлер қолданылады. Оның синтаксисі бір қарағанда өте күрделі, бірақ мұқият зерттегенде бұл функцияның логикасы өте қарапайым екені белгілі болды. Алдымен деректер тексерілетін ауқымды таңдау керек, содан кейін талдау орындалатын сәйкестік шарттарын орнату керек. Және мұндай операцияны көптеген жағдайлар үшін жасауға болады.

Синтаксистің өзі:

СУМИФТЕР(қосынды_ауқым, 1-шарт_ауқым, 1-шарт, [2-шарт_ауқым, 2-шарт], …)

Қолайлы жерлерде белгілі бір жағдайда қолайлы ұяшықтардың массивтерін қою керек. 

Аргументтерді толығырақ қарастырайық:

1. жиынтық_ауқым. Бұл аргумент, сондай-ақ 1-шарт пен 1-шарттың ауқымы қажет. Бұл жинақтауды қажет ететін ұяшықтар жиынтығы.
2. Шарт_ауқым1. Бұл жағдай тексерілетін ауқым. Ол келесі аргументпен жұптастырылған – Шарт 1. Шартқа сәйкес мәндердің қосындысы алдыңғы аргументте көрсетілген ұяшықтардың ішінде жүзеге асырылады.
3. Шарт 1. Бұл аргумент тексерілетін критерийлерді көрсетеді. Оны, мысалы, мына жолмен орнатуға болады: “> 32”.
4. 2-шарт диапазоны, 2-шарт… Мұнда келесі шарттар дәл осылай орнатылады. Егер бірнеше шарттарды көрсету қажет болса, 3-шарт ауқымы және 3-шарт аргументтері қосылады. Синтаксис келесі аргументтер үшін бірдей.

Функция 127 жұпқа дейін шарттар мен ауқымдарды максималды өңдеуге мүмкіндік береді. 

Сіз оны бірден бірнеше салада пайдалана аласыз (біз тек бірнешеуін береміз, тізім одан да ұзағырақ):

1. Бухгалтерлік есеп. Мысалы, функцияны пайдалану жақсы SUMMESLIMN белгілі бір сомадан асатын шығындар үшін тоқсан бойынша жиынтық есептерді жасау, мысалы. Немесе белгілі бір баға санатынан бір өнім бойынша есеп жасаңыз.
2. Сатуды басқару. Мұнда да функция өте пайдалы болуы мүмкін. Мысалы, біздің алдымызда белгілі бір тұтынушыға белгілі бір уақытта сатылған тауардың өзіндік құнын ғана қорытындылау міндеті тұр. Ал мұндай жағдайда функция SUMMESLIMN өте пайдалы болуы мүмкін.
3. Білім. Біз бүгін осы саладан көбірек практикалық мысалдар келтіреміз. Атап айтқанда, оны студенттердің бағалары туралы қысқаша мәліметтерді алу үшін пайдалануға болады. Сіз бір пән үшін немесе жеке бағалар үшін таңдай аласыз. Адам бірден бағалауды таңдайтын бірнеше критерийлерді қоя алады, бұл өте ыңғайлы және көп уақытты үнемдей алады.

Көріп отырғаныңыздай, бұл функция үшін қолданбалардың ауқымы өте кең. Бірақ бұл оның жалғыз еңбегі емес. Бұл мүмкіндіктің тағы бірнеше артықшылықтарын қарастырайық:

1. Бірнеше критерийлерді қою мүмкіндігі. Неліктен бұл артықшылық? Сіз қалыпты функцияны пайдалана аласыз СУММЕСЛИ! Және бәрі ыңғайлы болғандықтан. Критерийлердің әрқайсысы үшін жеке есептеулер жүргізудің қажеті жоқ. Барлық әрекеттерді алдын ала, тіпті бұрын да бағдарламалауға болады. деректер кестесі қалай құрылады. Бұл тамаша уақыт үнемдеу.
2. Автоматтандыру. Қазіргі заман – автоматтандыру дәуірі. Өз жұмысын дұрыс автоматтандыруды білетін адам ғана көп ақша таба алады. Сондықтан Excel және функцияны меңгеру мүмкіндігі SUMMESLIMN атап айтқанда, мансап құрғысы келетін кез келген адам үшін өте маңызды. Бір функцияны білу бір мезгілде бірнеше әрекетті орындауға мүмкіндік береді. Міне, біз осы мүмкіндіктің келесі артықшылығына көшеміз.
3. Уақытты үнемдеу. Тек бір функцияның бірден бірнеше тапсырманы орындауына байланысты.
4. Қарапайымдылық. Синтаксис бір қарағанда көлемділігіне байланысты айтарлықтай ауыр болғанымен, шын мәнінде бұл функцияның логикасы өте қарапайым. Алдымен деректер ауқымы, содан кейін белгілі бір шартқа сәйкестігі тексерілетін мәндер ауқымы таңдалады. Және, әрине, шарттың өзі де көрсетілуі керек. Және бірнеше рет. Шын мәнінде, бұл функция тек бір логикалық құрылымға негізделген, бұл оны белгіліге қарағанда қарапайым етеді VPR оны бірдей мақсаттарда қолдануға болатынына қарамастан, сонымен қатар критерийлердің үлкен санын ескере отырып. 

SUMIFS функциясын қолдану мүмкіндіктері

Бұл функцияны пайдаланудың бірнеше мүмкіндіктері бар, оларға назар аудару керек. Ең алдымен, бұл функция мәтін жолдары немесе нөлдер бар ауқымдарды елемеді, себебі бұл деректер түрлерін арифметикалық үлгіде қосу мүмкін емес, тек жолдар сияқты біріктірілген. Бұл функция мұны істей алмайды. Сондай-ақ келесі шарттарға назар аудару керек:

1. Бұл мән түрлерін олардағы мәндерді қосымша қосу үшін ұяшықтарды таңдау шарттары ретінде пайдалануға болады: сандық мәндер, логикалық өрнектер, ұяшық сілтемелері және т.б. 
2. Егер мәтін, логикалық өрнектер немесе математикалық белгілер тексерілсе, онда мұндай критерийлер тырнақша арқылы көрсетіледі.
3. 255 таңбадан асатын терминдерді қолдануға болмайды.
4. Қойылмалы таңбаларды пайдаланып мәндерді таңдау үшін шамамен өлшемдерді пайдалануға болады. Сұрақ белгісі бір таңбаны ауыстыру үшін қолданылады, ал көбейту белгісі (жұлдызша) бірнеше таңбаны ауыстыру үшін қажет. 
5. Қосынды диапазонындағы логикалық мәндер олардың түріне сәйкес автоматты түрде сандық мәндерге түрлендіріледі. Осылайша, «TRUE» мәні біреуге, ал «FALSE» - нөлге айналады. 
6. Егер #VALUE болса! ұяшықта қате пайда болса, бұл жағдай мен жиынтық ауқымдарындағы ұяшықтардың саны әртүрлі екенін білдіреді. Бұл аргументтердің өлшемдері бірдей екеніне көз жеткізу керек. 

SUMIFS функциясын қолдану мысалдары

функция SUMMESLIMN бір қарағанда күрделі емес сияқты. Бірақ көбірек түсінікті болу үшін функцияны қалай пайдалануға болатынының бірнеше практикалық мысалдарын қарастырайық SUMMESLIMN. Бұл тақырыпты ашуды әлдеқайда жеңілдетеді.

Шартты жиынтық динамикалық диапазон

Сондықтан бірінші мысалдан бастайық. Бізде студенттердің белгілі бір пән бойынша оқу бағдарламасын қалай орындайтыны туралы ақпаратты қамтитын кесте бар делік. Бағалар жинағы бар, үлгерімі 10 балдық жүйемен бағаланады. Тапсырма – тегі А әрпінен басталатын және ең төменгі ұпайы 5 болатын студенттердің емтихан бағасын табу.

Кесте осылай көрінеді.

Жоғарыда сипатталған критерийлер негізінде жалпы баллды есептеу үшін келесі формуланы қолдануымыз керек.

Аргументтерді толығырақ сипаттайық:

1. C3:C14 – біздің жиынтық ауқымымыз. Біздің жағдайда ол шарт диапазонымен сәйкес келеді. Оның ішінен соманы есептеу үшін пайдаланылатын ұпайлар таңдалады, бірақ біздің критерийлерге сәйкес келетіндер ғана.
2. «>5» - біздің бірінші шартымыз.
3. B3:B14 - екінші шартқа сәйкестік үшін өңделетін екінші жиынтық ауқым. Жиынтық диапазонмен сәйкестік жоқ екенін көреміз. Бұдан қорытындыға келеміз: қосынды диапазоны мен шарт диапазоны бірдей болуы мүмкін немесе бірдей болмауы мүмкін. 
4. «A*» екінші диапазон болып табылады, ол тек фамилиясы А әрпінен басталатын студенттер үшін бағаларды таңдауды көрсетеді. Біздің жағдайда жұлдызша кез келген таңбалар санын білдіреді. 

Есептеулерден кейін келесі кестені аламыз.

Көріп отырғаныңыздай, формула динамикалық диапазонға және пайдаланушы көрсеткен шарттарға негізделген мәндерді қорытындылады.

Excel бағдарламасындағы шарт бойынша таңдамалы қосынды

Енді соңғы тоқсанда қай елдерге қандай тауарлар жөнелтілгені туралы ақпарат алғымыз келеді делік. Осыдан кейін шілде және тамыз айларындағы жөнелтулерден түскен жалпы кірісті табыңыз.

Кестенің өзі осылай көрінеді. 

Соңғы нәтижені анықтау үшін бізге осындай формула қажет.

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

Осы формула бойынша жүргізілген есептеулер нәтижесінде келесі нәтижені аламыз.

Назар! Бұл формула өте үлкен көрінеді, бірақ біз тек екі критерийді қолдандық. Деректер ауқымы бірдей болса, төменде көрсетілгендей формуланың ұзындығын айтарлықтай қысқартуға болады.

SUMIFS функциясы бірнеше шарттар бойынша мәндерді қосу үшін

Енді түсінікті болу үшін тағы бір мысал келтірейік. Бұл жағдайда кесте алдыңғы жағдайдағыдай болып қалады. 

Біз келесі формуланы қолданамыз (бірақ оны массив формуласы ретінде жазамыз, яғни CTRL + SHIFT + ENTER пернелер тіркесімі арқылы енгіземіз).

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

Функциядан кейін SUMMESLIMN формулада көрсетілген критерийлер негізінде мәндер жиымын қосады (яғни Қытай және Грузия елдері), алынған массив әдеттегі функциямен жинақталады СУМ, ол массив формуласы ретінде жазылады.

Егер шарттар бірнеше жұп үшін массив тұрақтысы ретінде берілген болса, онда формула қате нәтиже береді.

Енді қорытындыларды қамтитын кестеге назар аударайық.

Көріп отырғаныңыздай, біз жетістікке жеттік. Сіз де міндетті түрде жетістікке жетесіз. Бұл салада үлкен жетістік. Бұл Excel бағдарламасын үйрену жолына енді ғана аяқ басқан адам түсіне алатын өте қарапайым функция. Ал біз қазірдің өзінде функция екенін білеміз SUMMESLIMN бухгалтерлік есептен бастап білімге дейінгі кез келген қызмет саласында тиімді болуға мүмкіндік береді. Жоғарыда сипатталмаған кез келген басқа салада мансап құрып жатсаңыз да, бұл мүмкіндік сізге ақша табуға көмектеседі. Сондықтан ол құнды.

Ең бастысы, бұл уақытты үнемдеуге мүмкіндік береді, бұл, өкінішке орай, шектеулі ресурс. Екі функцияны қолдану үшін бірнеше секунд бар сияқты көрінеді, бірақ көп қайталанатын операцияларды орындау қажет болғанда, бұл секундтар басқа нәрсеге жұмсалуы мүмкін сағаттарды қосады. Сондықтан бұл мүмкіндікті пайдаланып жаттығуды ұсынамыз. Оның үстіне, бұл керемет қарапайым.