Excel бағдарламасындағы IF функциясы. Мысалдар (бірнеше шарттармен)

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

Мысалы, егер мән белгілі бір мәнге сәйкес келсе, ұяшықта бір белгі көрсетіледі. Олай болмаса, басқаша. Бұл тиімді құралды тәжірибеде толығырақ қарастырайық.

Excel бағдарламасындағы IF функциясы (жалпы ақпарат)

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

1. Барлық А бағанында жұп сандарды тексеріңіз.
2. Егер жұп сан табылса, осындай және осындай мәндерді қосыңыз.
3. Егер жұп сан табылмаса, «табылған жоқ» деген жазуды көрсетіңіз.
4. Алынған санның жұп екенін тексеріңіз. 
5. Егер иә болса, оны 1-тармақта таңдалған барлық жұп сандарға қосыңыз.

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

Бір шарты бар IF функциясының синтаксисі

Excel бағдарламасындағы кез келген функция формула арқылы орындалады. Функцияға деректерді беру керек үлгі синтаксис деп аталады. Оператор жағдайында IF, формула осы пішімде болады.

=Егер (логикалық_өрнек, мән_егер_шын, мән_егер_жалған)

Синтаксисті толығырақ қарастырайық:

1. Логикалық өрнек. Бұл Excel тексеретін шарттың өзі, сәйкестік немесе сәйкессіздік. Сандық және мәтіндік ақпаратты тексеруге болады.
2. Мән_егер_шын болса. Тексерілетін деректер көрсетілген шарттарға сәйкес келсе, ұяшықта көрсетілетін нәтиже.
3. мән_егер_жалған. Тексерілетін деректер шартқа сәйкес келмесе, ұяшықта көрсетілетін нәтиже.

Міне, түсінікті болу үшін мысал.

Мұнда функция A1 ұяшығын 20 санымен салыстырады. Бұл синтаксистің бірінші абзацы. Мазмұн осы мәннен үлкен болса, формула жазылған ұяшықта «20-дан жоғары» мән көрсетіледі. Жағдай осы шартқа сәйкес келмесе – «20-дан кем немесе тең».

Ұяшықтағы мәтін мәнін көрсеткіңіз келсе, оны тырнақшаға алуыңыз керек.

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

Біз санды емес, мәтінді тексергіміз келгендіктен, бірінші аргумент B2 = «кемшіліктер».

Бірнеше шарты бар IF функциясының синтаксисі

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

Түсінікті болу үшін синтаксисті келтірейік.

=Егер(логикалық_өрнек,мән_шын болса, ЕГЕР(логикалық_өрнек,_егер_шын, мән_егер_жалған))

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

Міне мысал.

Ал мұндай формуланың көмегімен (төмендегі скриншотта көрсетілген) әр оқушының үлгерімін талдауға болады.

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

ЖӘНЕ және НЕМЕСЕ операторларының көмегімен IF функциясын қалай кеңейтуге болады

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

ЖӘНЕ шарты бар IF функциясы

Кейде өрнекті бірден бірнеше шарттарға тексеру қажет. Ол үшін функцияның бірінші аргументінде жазылған ЖӘНЕ функциясы қолданылады IF. Ол келесідей жұмыс істейді: егер а бірге тең және а 2-ге тең болса, мән c болады.

«НЕМЕСЕ» шарты бар IF функциясы

НЕМЕСЕ функциясы да осыған ұқсас жұмыс істейді, бірақ бұл жағдайда шарттардың біреуі ғана дұрыс. Мүмкіндігінше 30-ға дейін жағдайды осылайша тексеруге болады. 

Функцияларды қолданудың кейбір жолдары берілген И и OR функция аргументі ретінде IF.

Екі кестедегі мәліметтерді салыстыру

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

Екі кестені салыстыру үшін функцияны пайдаланыңыз COUNTIF. Оны толығырақ қарастырайық.

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

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

Біз бірінші тағамдық процессордың техникалық сипаттамаларына сәйкес ассортиментті таңдаймыз.

Осыдан кейін келесі мәзірлерді басыңыз: Шартты пішімдеу – ереже жасау – пішімделген ұяшықтарды анықтау үшін формуланы пайдаланыңыз.

Пішімдеу формуласы түрінде функцияны жазамыз =COUNTIF (салыстыратын ауқым; бірінші кестенің бірінші ұяшығы)=0. Салыстыру диапазоны ретінде екінші тағамдық процессордың мүмкіндіктері бар кесте қолданылады.

Адрестердің абсолютті екеніне көз жеткізу керек (жол және баған атауларының алдында доллар белгісі бар). Excel нақты мәндерді іздеуі үшін формуладан кейін =0 қосыңыз.

Осыдан кейін ұяшықтарды пішімдеуді орнату керек. Мұны істеу үшін үлгінің жанындағы «Формат» түймесін басу керек. Біздің жағдайда біз толтыруды қолданамыз, себебі бұл осы мақсат үшін ең қолайлы. Бірақ сіз қалаған пішімдеуді таңдай аласыз.

Біз диапазон ретінде баған атауын тағайындадық. Бұл ауқымды қолмен енгізуден әлдеқайда ыңғайлы.

Excel бағдарламасындағы SUMIF функциясы

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

1. Барлық сатушылардың жалпы кірісі бар жолды қосыңыз және формуланы енгізгеннен кейін нәтиже болатын ұяшықты басыңыз. 
2. Біз формулалар жолының жанында орналасқан fx түймесін табамыз. Содан кейін іздеу арқылы қажетті функцияны табуға болатын терезе пайда болады. Операторды таңдағаннан кейін «OK» түймесін басу керек. Бірақ қолмен енгізу әрқашан мүмкін.
   

   

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

   

4. Бірінші аргумент - ауқым. Мұнда критерийлерге сәйкестігін тексергіңіз келетін ұяшықтарды енгізесіз. Біз туралы айтатын болсақ, бұл қызметкерлердің лауазымдары. D4:D18 ауқымын енгізіңіз. Немесе жай ғана қызықты ұяшықтарды таңдаңыз.
5. «Критерийлер» өрісіне позицияны енгізіңіз. Бізде – «сатушы». Жиынтық ауқымы ретінде біз қызметкерлердің жалақысы көрсетілген ұяшықтарды көрсетеміз (бұл қолмен орындалады және оларды тінтуірдің көмегімен таңдаңыз). «Жарайды» түймесін басыңыз, біз сатушы болып табылатын барлық қызметкерлердің аяқталған есептелген жалақысын аламыз.

Бұл өте ыңғайлы екеніне келісіңіз. Ол емес пе?

Excel бағдарламасындағы SUMIFS функциясы

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

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

Аргумент енгізу диалогтық терезесі арқылы тек бесеуін көрсетуге болады. Егер сізге қосымша критерийлер қажет болса, оларды бірінші екеуі сияқты логикаға сәйкес қолмен енгізу керек болады.

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

1. Жиынтық ауқымы. Қорытындыланатын ұяшықтар.
2. 1-шарт диапазоны – белгілі бір критерийге сәйкестігі тексерілетін диапазон. 
3. 1-шарт - шарттың өзі.
4. 2-критерийлер диапазоны критерийге сәйкес тексерілетін екінші ауқым болып табылады.
5. 2-шарт - екінші шарт.

Әрі қарай логика ұқсас. Нәтижесінде Оңтүстік филиалының барлық басшыларының жалақысын анықтадық.

Excel бағдарламасындағы COUNTIF функциясы

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

1. Алдымен, сатушылар санын қамтитын жолды қосыңыз. Осыдан кейін нәтиже көрсетілетін ұяшықты басу керек.
2. Осыдан кейін сіз «Формулалар» қойындысынан табуға болатын «Функцияны енгізу» түймесін басуыңыз керек. Санаттар тізімі бар терезе пайда болады. Бізге «Толық алфавиттік тізім» тармағын таңдау керек. Тізімде бізді формула қызықтырады COUNTIF. Оны таңдағаннан кейін біз «OK» түймесін басуымыз керек.
   

   

3. Осыдан кейін бізде осы ұйымда жұмыс істейтін сатушылардың саны бар. Ол «сатушы» сөзі жазылған ұяшықтардың санын санау арқылы алынды. Барлығы қарапайым. 

Excel бағдарламасындағы COUNTSLIM функциясы

Формулаға ұқсас SUMMESLIMN, бұл формула бірнеше шарттарға сәйкес келетін ұяшықтардың санын санайды. Синтаксис ұқсас, бірақ формуладан сәл өзгеше SUMMESLIMN:

1. Шарт диапазоны 1. Бұл бірінші критерий бойынша сыналатын ауқым.
2. Шарт 1. Тікелей бірінші критерий.
3. Шарт диапазоны 2. Бұл екінші критерий бойынша сыналатын ауқым. 
4. 2-шарт.
5. Ауқымның шарттары 3.

Және тағы басқа.

Сонымен, функция IF Excel-де – жалғыз емес, адамның өмірін айтарлықтай жеңілдететін ең көп таралған әрекеттерді автоматты түрде орындайтын оның бірнеше түрі бар. 

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

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

Оң қолдағы функция IF және оның нұсқалары Excel парағын күрделі алгоритмдерде әрекет ете алатын толыққанды бағдарламаға айналдыруға мүмкіндік береді. Функцияның қалай жұмыс істейтінін түсіну IF макростарды үйрену жолындағы алғашқы қадам – электрондық кестелермен икемді жұмыс істеудің келесі қадамы. Бірақ бұл қазірдің өзінде кәсіби деңгей.