Excel бағдарламасындағы ТОП 15 формулалар

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

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

Бұл көп әрекеттерді қажет ететін бір түрдегі көптеген әрекеттерді үнемі орындау керек болса пайдалы болуы мүмкін.

Қызық болды ма? Содан кейін ең пайдалы 15 Excel формуласын шолуға қош келдіңіз.

Кейбір терминология

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

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

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

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

Ал енді осының барлығын іс жүзінде тексеріп көрейік.

Формула 1: VLOOKUP

Бұл функция кестеден қажетті ақпаратты табуға және қайтарылған нәтижені белгілі бір ұяшықта көрсетуге мүмкіндік береді. Функция атауының аббревиатурасы «Вертикалды көрініс» дегенді білдіреді.

Синтаксис

Бұл 4 аргументі бар өте күрделі формула және оны пайдалану көптеген мүмкіндіктерге ие.

Синтаксис:

=VLOOKUP(іздеу_мәні, кесте, баған_нөмірі, [ауқым_іздеу])

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

1. Іздеу үшін мән.
2. Кесте. Бірінші бағанда іздеу мәні, сондай-ақ қайтарылатын мән болуы қажет. Соңғысы кез келген жерде орналасқан. Пайдаланушы формула нәтижесін қайда кірістіру керектігін өз бетінше шеше алады. 
3. Баған нөмірі.
4. Аралық қарау. Бұл қажет болмаса, осы аргументтің мәнін өткізіп жіберуге болады. Бұл функция табуы керек сәйкестіктің дәлдік дәрежесін көрсететін логикалық өрнек. «True» параметрі көрсетілсе, Excel іздеу мәні ретінде көрсетілгенге ең жақын мәнді іздейді. Егер «False» параметрі көрсетілсе, функция бірінші бағандағы мәндерді ғана іздейді.

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

Формула 2: Егер

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

Синтаксис

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

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

Мұнда логикалық өрнек критерийді тікелей сипаттайтын формуланы білдіреді. Оның көмегімен деректердің белгілі бір шартқа сәйкестігі тексеріледі. Тиісінше, «өтірік болса мән» аргументі сол тапсырмаға арналған, тек айырмашылығы оның мағынасы жағынан қарама-қарсы айна. Қарапайым сөзбен айтқанда, егер шарт расталмаса, онда бағдарлама белгілі бір әрекеттерді орындайды.

Функцияны пайдаланудың тағы бір жолы бар IF – кірістірілген функциялар. Мұнда 64-ке дейін тағы да көптеген шарттар болуы мүмкін. Скриншотта берілген формулаға сәйкес пайымдаудың мысалы келесідей. Егер A2 ұяшығы екіге тең болса, онда «Иә» мәнін көрсету керек. Егер оның мәні басқаша болса, D2 ұяшығы екіге тең екенін тексеру керек. Егер иә болса, онда «жоқ» мәнін қайтару керек, егер бұл жерде шарт жалған болып шықса, формула «мүмкін» мәнін қайтаруы керек.

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

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

Мұндағы синтаксис:

=Егер(ISBLANK(ұяшық нөмірі),"Бос","Бос емес").

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

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

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

Формула 3: SUMIF

функция СУММЕСЛИ белгілі бір критерийлерге сәйкестігін ескере отырып, деректерді қорытындылауға мүмкіндік береді.

Синтаксис

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

=SUMIF(ауқым, шарт, [қосынды_ауқым])

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

1. Шарт. Бұл аргумент ұяшықтарды функцияға беруге мүмкіндік береді, олар одан әрі қосындыға жатады.
2. Жиынтық ауқымы. Бұл аргумент міндетті емес және шарт жалған болса, қосылатын ұяшықтарды көрсетуге мүмкіндік береді.

Осылайша, бұл жағдайда Excel көшу саны 100000-нан асатын сұраулар бойынша деректерді қорытындылады.

Формула 4: SUMMESLIMN

Бірнеше шарт болса, онда байланысты функция пайдаланылады SUMMESLIMN.

Синтаксис

Бұл функцияның формуласы келесідей көрінеді:

=SUMIFS(қосынды_ауқым, шарт_ауқым 1, шарт1, [шарт_ауқым2, 2-шарт], …)

Екінші және үшінші аргументтер қажет, атап айтқанда «1-шарт ауқымы» және «1-шарт ауқымы».

Формула 5: COUNTIF және COUNTIFS

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

Синтаксис

Бұл функцияны енгізу үшін келесі формуланы көрсету керек:

= COUNTIF (ауқым, өлшемдер)

Берілген аргументтер нені білдіреді?

1. Ауқым - санау жүргізілетін ұяшықтар жиынтығы.
2. Критерийлер – ұяшықтарды таңдау кезінде ескерілетін шарт.

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

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

=COUNTIFS(1-шарт_ауқым, 1-шарт, [2-шарт_ауқым, 2-шарт],…)

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

Формула 6: ҚАТЕЛІК

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

Синтаксис

Бұл функцияның екі аргументі бар. Синтаксис келесідей:

=ҚАУІПСІЗ(мән;қате_егер_мән)

Аргументтердің сипаттамасы:

1. Мән қателер үшін тексерілген формуланың өзі болып табылады.
2. Қате болса мәні қате анықталғаннан кейін пайда болатын нәтиже.

Егер мысалдар туралы айтатын болсақ, онда бұл формула бөлу мүмкін болмаса, «Есептеудегі қате» мәтінін көрсетеді.

Формула 7: СОЛ

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

Оның синтаксисі келесідей:

=СОЛ(мәтін,[таңбалар_саны])

Ықтимал аргументтер:

1. Мәтін – белгілі бір фрагментті алғыңыз келетін жол.
2. Таңбалар саны тікелей шығарылатын таңбалар саны болып табылады.

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

Формула 8: PSTR

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

Оның синтаксисі келесідей:

=MID(мәтін,бастау_позициясы,таңбалар_саны).

Аргументтерді кеңейту:

1. Мәтін – қажетті мәліметтерді қамтитын жол.
2. Бастапқы позиция - мәтінді шығарудың басы ретінде қызмет ететін сол кейіпкердің позициясы.
3. Таңбалар саны – формула мәтіннен шығару керек таңбалар саны.

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

Формула 9: PROPISN

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

=МІНДЕТТІ(мәтін)

Бір ғана дәлел бар – мәтіннің өзі өңделетін болады. Ұяшық сілтемесін пайдалануға болады.

Формула 10: ТӨМЕН

Негізінде берілген мәтіннің немесе ұяшықтың әрбір әрпін кіші әріппен жазатын кері функция.

Оның синтаксисі ұқсас, мәтінді немесе ұяшық мекенжайын қамтитын бір ғана аргумент бар.

Формула 11: ІЗДЕУ

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

Бұл формуланың үлгісі:

=СӘйкестендіру(іздеу_мәні, іздеу_жиымы, сәйкестік_түрі)

Алғашқы екі аргумент қажет, соңғысы міндетті емес.

Сәйкестендірудің үш жолы бар:

1. 1-ден кем немесе оған тең.
2. Нақты – 0.
3. Ең кіші мән, -1-ге тең немесе үлкен.

Бұл мысалда біз кілт сөздердің қайсысынан кейін 900-ге дейін басу болатынын анықтауға тырысамыз.

Формула 12: DLSTR

Бұл функция берілген жолдың ұзындығын анықтауға мүмкіндік береді.

Оның синтаксисі алдыңғыға ұқсас:

=DLSTR(мәтін)

Сонымен, оны SEO-сайтты жылжыту кезінде мақала сипаттамасының ұзақтығын анықтау үшін пайдалануға болады.

Оны функциямен біріктіру де жақсы IF.

Формула 13: ҚОСУ

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

Синтаксис:

=CONCATENATE(мәтін1,мәтін2,мәтін3);

Формула 14: PROPNACH

Бұл функция бас және кіші әріптерді ауыстырады.

Синтаксис өте қарапайым:

=PROPLAN(мәтін)

Формула 15: БАСПА

Бұл формула мақаладан барлық көрінбейтін таңбаларды (мысалы, жол үзілімдерін) алып тастауға мүмкіндік береді.

Оның синтаксисі келесідей:

=PRINT(мәтін)

Аргумент ретінде ұяшықтың мекенжайын көрсетуге болады.

Қорытындылар

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

Бұл нәтиже берді деп үміттенемін және сіз өзіңіз үшін көптеген пайдалы нәрселерді білдіңіз.