Excelдеги SUMIF функциясы жана бир нече шарттар боюнча суммалоо

Excel укмуштуудай функционалдык программа. Ал тургай, орнотулган функциялар топтому дээрлик бардык тапшырмаларды аткаруу үчүн жетиштүү. Көпчүлүккө тааныш болгон стандарттуулардан тышкары, аз эле адамдар уккандары да бар. Бирок ошол эле учурда алар пайдалуу болушун токтотпойт. Алардын адистиги тар жана аларга дайыма эле муктаждык боло бербейт. Бирок, эгерде сиз алар жөнүндө билсеңиз, анда критикалык учурда алар абдан пайдалуу болушу мүмкүн.

Бүгүн биз ушундай функциялардын бири жөнүндө сүйлөшөбүз - SUMMESLIMN.

Эгерде колдонуучунун алдында белгилүү бир критерийлерге басым жасоо менен бир нече баалуулуктарды жыйынтыктоо милдети коюлса, анда функцияны колдонуу зарыл. SUMMESLIMN. Бул функцияны колдонгон формула бул шарттарды аргумент катары кабыл алып, андан кийин аларга жооп берген маанилерди кошуп, андан кийин табылган маани жазылган уячага киргизилет. 

SUMIFS функциясынын деталдуу сүрөттөлүшү

Функцияны карап чыгуудан мурун SUMMESLIMN, адегенде анын жөнөкөй версиясы эмне экенин түшүнүшүңүз керек - СУММЕСЛИ, анткени биз карап жаткан функция ага негизделген. Ар бирибиз, балким, буга чейин көп колдонулган эки иш менен тааныш - СУМ (баалардын жыйындысын жүргүзөт) жана ЭГЕР (Белгиленген шартка каршы маанини сынайт).

Аларды бириктирсеңиз, башка функцияны аласыз - СУММЕСЛИ, ал маалыматтарды колдонуучу белгилеген критерийлерге ылайык текшерет жана ошол критерийлерге жооп берген сандарды гана кошот. Эгерде биз 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" мааниси бирге, ал эми "ЖАЛГАН" нөлгө айланат. 
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 бухгалтердик эсептен баштап билимге чейин ишмердүүлүктүн ар кандай тармагында натыйжалуу болууга мүмкүндүк берет. Эгер сиз жогоруда айтылбаган башка тармакта карьера куруп жатсаңыз да, бул функция сизге акча табууга жардам берет. Бул үчүн ал баалуу.

Эң негизгиси, бул, тилекке каршы, чектелген ресурс болуп саналат, убакытты үнөмдөөгө мүмкүндүк берет. Эки функцияны колдонуу үчүн бир нече секунд бар окшойт, бирок сиз көп сандагы кайталануучу операцияларды аткарууга туура келгенде, бул секундалар башка нерсеге жумшалышы мүмкүн болгон сааттарды кошот. Андыктан биз сизге бул функцияны колдонууну сунуштайбыз. Анын үстүнө, бул укмуштуудай жөнөкөй.