Excel формулалары: Excel формулалары дегеніміз не және оларды пайдалану жолы

«Excel формулалары» термині кез келген комбинацияға қатысты болуы мүмкін операторы Excel және/немесе Excel функциялары.

Excel формуласы электрондық кесте ұяшығына = белгісін, содан кейін қажетті операторларды және/немесе функцияларды теру арқылы енгізіледі. Бұл негізгі қосу сияқты қарапайым болуы мүмкін (мысалы, “=A1+B1”) немесе Excel операторлары мен бірнеше кірістірілген Excel функцияларының күрделі комбинациясы болуы мүмкін.

Excel операторлары

Excel операторлары сандық мәндерде, мәтінде немесе ұяшық сілтемелерінде әрекеттерді орындайды. Excel операторларының төрт түрі бар.

Сұрақ:

• Арифметикалық операторлар
• Мәтіндік операторлар
• Салыстыру операторлары
• Сілтеме операторлары

Операторлардың төрт түрін сипаттайық:

Арифметикалық операторлар

Excel арифметикалық операторлары және олардың бағалану реті келесі кестеде көрсетілген:

Арифметикалық операторлардың басымдылығы

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

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

Арифметикалық операторлардың мысалдары

Excel мәтіндік операторы

Excel бағдарламасының біріктіру операторы (& белгісімен белгіленген) қосымша жалғыз мәтін жолын жасау үшін мәтін жолдарын қосады.

Біріктіру операторының мысалы

Келесі формула мәтін жолдарын біріктіру үшін біріктіру операторын пайдаланады.SMITH« « және «John«

Excel салыстыру операторлары

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

Салыстыру операторларының мысалдары

Төмендегі электрондық кестелер функциямен пайдаланылатын салыстыру операторларының мысалдарын көрсетеді IF Excel.

Сілтеме операторлары

Excel сілтеме операторлары электрондық кестедегі ауқымдарға сілтеме жасау кезінде пайдаланылады. Анықтамалық операторлар:

Сілтеме операторларының мысалдары

1-мысал – Excel ауқымының операторы

Келесі электрондық кестедегі C1 ұяшығы үшін пайдаланылатын ауқым операторы көрсетіледі defiаралықты аяқтаңыз A1-B3. Содан кейін ауқым функцияға беріледі SUM ұяшықтардағы мәндерді қосатын Excel A1-B3 және мәнді қайтарады 21.

2-мысал – Excel біріктіру операторы

Жасуша C1 келесі электрондық кестенің бірі үшін пайдаланылатын біріктіру операторын көрсетеді defiекі диапазондағы ұяшықтардан тұратын ауқым емес A1-A3 e A1-B1. Содан кейін алынған ауқым функцияға беріледі SUM Excel бағдарламасында, ол біріктірілген ауқымдағы мәндерді қосады және мәнді қайтарады 12.

Excel біріктіру операторы ұяшық сияқты шынайы математикалық біріктіруді қайтармайтынын ескеріңіз A1, ол екі ауқымға да кіреді A1-A3 e A1-B1 сомасын есептегенде екі рет есептеледі).

3-мысал – Excel қиылысу операторы

Келесі электрондық кестедегі C1 ұяшығы үшін пайдаланылатын қиылысу операторы көрсетіледі defiауқымдардың қиылысындағы ұяшықтарда жасалған ауқымды аяқтаңыз A1-A3 e A1-B2. Алынған диапазон (диапазон A1-A2) содан кейін функциясына беріледі SUM қиылысатын ауқымдағы мәндерді қосатын және мәнді қайтаратын Excel 4.

Excel операторлары туралы қосымша ақпаратты мына жерден алуға болады Microsoft Office веб-сайты.

Excel функциялары

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

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

Excel статистикалық функциялары:

Сан және жиілік

• COUNT: берілген ұяшықтар немесе мәндер жиынындағы сандық мәндердің санын қайтарады;
• COUNTA: Берілген ұяшықтар немесе мәндер жиынындағы бос орындардың санын қайтарады;
• COUNTBLANK: берілген ауқымдағы бос ұяшықтардың санын қайтарады;
• COUNTIF: берілген шартты қанағаттандыратын ұяшықтар санын (берілген ауқымдағы) қайтарады;
• COUNTIFS: көрсетілген шарттар жинағын қанағаттандыратын ұяшықтар санын (берілген ауқымның) қайтарады (Excel 2007 нұсқасындағы жаңа);
• FREQUENCY: берілген массивтен көрсетілген ауқымдарға түсетін мәндердің санын көрсететін массивді қайтарады;

Максималды және Минималды іздеу

• MAX: Берілген сандар тізімінен ең үлкен мәнді қайтарады
• MAXA: берілген мәндер тізімінен ең үлкен мәнді қайтарады, мәтінді және логикалық мәнді санайды FALSE 0 мәні ретінде және логикалық мәнді санау TRUE 1 мәні ретінде
• MAXIFS: Бір немесе бірнеше шартқа негізделген көрсетілген тізімдегі мәндердің ішкі жиынынан ең үлкен мәнді қайтарады. (Excel 2019 жаңа нұсқасы)
• MIN: Берілген сандар тізімінен ең кіші мәнді қайтарады
• MINA: Берілген мәндер тізімінен ең кіші мәнді қайтарады, мәтінді және FALSE логикалық мәнін 0 мәні ретінде және TRUE логикалық мәнін 1 мәні ретінде санайды.
• MINIFS: Бір немесе бірнеше шартқа негізделген көрсетілген тізімдегі мәндердің ішкі жиынынан ең кіші мәнді қайтарады. (Excel 2019 бағдарламасындағы жаңалықтар)
• LARGE: берілген K мәні үшін берілген сандар тізімінен K-ші ең үлкен мәнді қайтарады
• SMALL: берілген K мәні үшін берілген сандар тізімінен K-ші КІШІ мәнді қайтарады

Медиа

• AVERAGE: Берілген сандар тізімінің орташа мәнін береді
• AVERAGEA: Берілген сандар тізімінің орташа мәнін қайтарады, мәтінді және FALSE логикалық мәнін 0 мәні ретінде және TRUE логикалық мәнін 1 мәні ретінде санайды.
• AVERAGEIF: Берілген шартқа сәйкес келетін берілген ауқымдағы ұяшықтардың орташа мәнін есептейді (Excel 2007 нұсқасындағы жаңа)
• AVERAGEIFS: Бірнеше шартқа сәйкес келетін берілген ауқымдағы ұяшықтардың орташа мәнін есептейді (Excel 2007 нұсқасындағы жаңа)
• MEDIAN: Берілген сандар тізімінің медианасын (ортаңғы мән) қайтарады
• MODE: Берілген сандар тізімінің режимін (ең жиі мән) есептейді (функциямен ауыстырылады Mode.Sngl Excel 2010 нұсқасында)
• MODE.SNGL: Берілген сандар тізімінің режимін (ең жиі мәні) есептейді (Excel 2010 нұсқасындағы жаңа: функцияны ауыстырады Mode)
• MODE.MULT: Жиымдағы немесе деректер ауқымындағы ең жиі мәндердің тік массивін қайтарады (Excel 2010 нұсқасындағы жаңа)
• GEOMEAN: Берілген сандар жиынының геометриялық ортасын қайтарады
• HARMEAN: Берілген сандар жиынының гармоникалық ортасын қайтарады
• TRIMMEAN: Берілген мәндер жиынының ішкі орташа мәнін береді

Орын ауыстырулар

• PERMUT: Нысандардың берілген саны үшін ауыстырулар санын қайтарады
• PERMUTATIONA: Жалпы нысандардан таңдауға болатын нысандардың берілген санына (қайталаулары бар) ауыстырулар санын қайтарады (Excel 2013 нұсқасындағы жаңа)

Сенімділік аралықтары

• CONFIDENCE: Қалыпты үлестірімді пайдаланып, жалпы орта үшін сенімділік аралығын қайтарады (Excel 2010 бағдарламасындағы Confidence.Norm функциясымен ауыстырылған)
• CONFIDENCE.NORM: Қалыпты үлестіру арқылы жалпы орта үшін сенімділік аралығын қайтарады (Excel 2010 нұсқасындағы жаңа: Сенім функциясын ауыстырады)
• CONFIDENCE.T: Студенттік t үлестірімін пайдаланып, жалпы орта үшін сенімділік аралығын қайтарады (Excel 2010 нұсқасындағы жаңа)

Процентильдер және квартилдер

• PERCENTILE: К 0 – 1 (қоса алғанда) ауқымында болатын берілген ауқымдағы мәндердің K-ші пайыздық мәнін қайтарады (Excel 2010 бағдарламасындағы Percentile.Inc функциясымен ауыстырылған)
• PERCENTILE.INC: К 0 – 1 (қоса алғанда) ауқымында болатын берілген ауқымдағы мәндердің K-ші пайыздық мәнін қайтарады (Excel 2010 нұсқасындағы жаңалық: пайыздық функцияны ауыстырады)
• PERCENTILE.EXC: Берілген ауқымдағы мәндердің K-ші пайыздық мәнін қайтарады, мұнда K 0 – 1 (ерекше) ауқымында (Excel 2010 нұсқасындағы жаңа)
• QUARTILE: 0 – 1 (қоса) пайыздық мәнге негізделген берілген сандар жиынының көрсетілген квартилін қайтарады (Excel 2010 бағдарламасындағы Quartile.Inc функциясымен ауыстырылған)
• QUARTILE.INC: 0 – 1 (қоса алғанда) пайыздық мәніне негізделген берілген сандар жиынының көрсетілген квартилін қайтарады (Excel 2010 нұсқасындағы жаңа: Квартиль функциясын ауыстырады)
• QUARTILE.EXC: 0 – 1 (ерекше) пайыздық мәнге негізделген берілген сандар жиынының көрсетілген квартилін қайтарады (Excel 2010 нұсқасындағы жаңа)
• RANK: Берілген мәндер массивінде берілген мәннің статистикалық дәрежесін қайтарады (Excel 2010 бағдарламасындағы Rank.Eq функциясымен ауыстырылады)
• RANK.EQ: Берілген сандар тізімінің режимін (ең жиі мән) қайтарады (бірден көп мән бірдей дәрежеге ие болса, сол жиынның ең жоғарғы дәрежесі қайтарылады) (Excel 2010 нұсқасындағы жаңа: Rank функциясын ауыстырады)
• RANK.AVG: Берілген мәндер массивінде берілген мәннің статистикалық дәрежесін қайтарады (егер бірнеше мәндер бірдей дәрежеге ие болса, орташа дәреже қайтарылады) (Excel 2010 нұсқасындағы жаңа)
• PERCENTRANK: Деректер жиынындағы мәннің дәрежесін пайызбен қайтарады (0 – 1 қоса алғанда) (Excel 2010 бағдарламасындағы Percentrank.Inc функциясымен ауыстырылған)
• PERCENTRANK.INC: Деректер жиынындағы мән дәрежесін пайызбен қайтарады (0 – 1 қоса) (Excel 2010 нұсқасындағы жаңа: Percentrank функциясын ауыстырады)
• PERCENTRANK.EXC: Деректер жиынындағы мәннің дәрежесін пайызбен қайтарады (0 – 1 қоспағанда) (Excel 2010 нұсқасындағы жаңа)

Ауытқу және дисперсия

• AVEDEV: Деректер нүктелерінің орташа мәнінен абсолютті ауытқуларының орташа мәнін береді
• DEVSQ: Деректер нүктелерінің жиынының таңдамалы орташа мәнінен ауытқу квадраттарының қосындысын қайтарады
• STDEV: Берілген мәндер жиынының стандартты ауытқуын қайтарады (басшылық үлгісін білдіреді) (Excel 2010 бағдарламасындағы St.Dev функциясымен ауыстырылған)
• STDEV.S: Берілген мәндер жиынының стандартты ауытқуын қайтарады (басшылық үлгісін білдіреді) (Excel 2010 нұсқасындағы жаңа: STDEV функциясын ауыстырады)
• STDEVA: Берілген мәндер жиынының стандартты ауытқуын қайтарады (басшылық үлгісін білдіреді), мәтінді және FALSE логикалық мәнін 0 мәні ретінде және TRUE логикалық мәнін 1 мәні ретінде санайды.
• STDEVP: Берілген мәндер жиынының стандартты ауытқуын қайтарады (бүкіл жиынтықты білдіреді) (Excel 2010 бағдарламасында StdPDev функциясымен ауыстырылған)
• STDEV.P: Берілген мәндер жиынының стандартты ауытқуын қайтарады (бүкіл популяцияны білдіреді) (Excel 2010 нұсқасындағы жаңа: STDEV функциясын ауыстырады)
• STDEVPA: Берілген мәндер жиынының стандартты ауытқуын қайтарады (бүкіл жиынтықты білдіреді), мәтінді және FALSE логикалық мәнін 0 мәні ретінде және TRUE логикалық мәнін 1 мәні ретінде санайды.
• VAR: Берілген мәндер жиынының дисперсиясын қайтарады (басшылық үлгісін білдіреді) (Excel 2010 бағдарламасында SVar функциясымен ауыстырылған)
• VAR.S: Берілген мәндер жиынының дисперсиясын қайтарады (басшылық үлгісін білдіреді) (Excel 2010 нұсқасындағы жаңа – Var функциясын ауыстырады)
• VARA: Берілген мәндер жиынының дисперсиясын қайтарады (бастылық үлгісін білдіреді), мәтінді және FALSE логикалық мәнін 0 мәні ретінде және TRUE логикалық мәнін 1 мәні ретінде санайды
• VARP: Берілген мәндер жиынының дисперсиясын қайтарады (бүкіл жиынды білдіреді) (Excel 2010 нұсқасындағы Var.P функциясымен ауыстырылған)
• VAR.P: Берілген мәндер жиынының дисперсиясын қайтарады (бүкіл популяцияны білдіреді) (Excel 2010 нұсқасындағы жаңа – Varp функциясын ауыстырады)
• VARPA: Берілген мәндер жиынының дисперсиясын қайтарады (бүкіл жиынтықты білдіреді), мәтінді және FALSE логикалық мәнін 0 мәні ретінде және TRUE логикалық мәнін 1 мәні ретінде санайды
• COVAR: Бастапқы ковариантты қайтарады (яғни, екі берілген деректер жиынындағы әрбір жұп үшін ауытқулар өнімдерінің орташа мәні) (Excel 2010 бағдарламасындағы Covariance.P функциясымен ауыстырылған)
• COVARIANZA.P: Бастапқы ковариантты қайтарады (яғни, екі берілген деректер жиынындағы әрбір жұп үшін ауытқулар өнімдерінің орташа мәні) (Excel 2010 нұсқасындағы жаңа: Covar функциясын ауыстырады)
• COVARIANZA.S: Үлгі ковариансын қайтарады (яғни берілген екі деректер жиынындағы әрбір жұп үшін ауытқулар өнімдерінің орташа мәні) (Excel 2010 нұсқасындағы жаңа)

Болжау функциялары

• FORECAST: x және y мәндерінің берілген жиынына бекітілген сызықтық тренд сызығында болашақ нүктені болжайды (функциямен ауыстырылады FORECAST.LINEAR Excel 2016 нұсқасында)
• FORECAST.ETS: Бар мәндер қатарына негізделген уақыт шкаласы бойынша болашақ мәнді болжау үшін экспоненциалды тегістеу алгоритмін пайдаланады (Excel 2016 нұсқасындағы жаңа – Mac жүйесіне арналған Excel 2016 нұсқасында қол жетімді емес)
• FORECAST.ETS.CONFINT: Белгіленген мақсатты күндегі болжам мәні үшін сенімділік аралығын қайтарады (Excel 2016 нұсқасындағы жаңа – Mac жүйесіне арналған Excel 2016 нұсқасында қол жетімді емес)
• FORECAST.ETS.SEASONALITY: Белгілі бір уақыт қатары үшін Excel анықтаған қайталанатын үлгінің ұзындығын қайтарады (Excel 2016 нұсқасындағы жаңа – Mac жүйесіне арналған Excel 2016 нұсқасында қол жетімді емес)
• FORECAST.ETS.STAT: Уақыт қатарының болжамы туралы статистикалық мәнді қайтарады (Excel 2016 нұсқасындағы жаңа – Mac жүйесіне арналған Excel 2016 нұсқасында қол жетімді емес)
• FORECAST.LINEAR: x және y мәндерінің берілген жиынына бекітілген сызықтық тренд сызығында болашақ нүктені болжайды (Excel 2016 нұсқасындағы жаңа (Mac үшін Excel 2016 емес) – Болжау функциясын ауыстырады)
• INTERCEPT: x және y мәндерінің қатары арқылы ең қолайлы регрессия сызығын есептейді, осы сызық у осін кесетін мәнді қайтарады
• LINEST: x және y мәндерінің қатары арқылы ең жақсы сәйкестік сызығының трендін сипаттайтын статистикалық ақпаратты қайтарады
• SLOPE: x және y мәндерінің берілген жиыны арқылы сызықтық регрессия сызығының көлбеуін қайтарады
• TREND: y мәндерінің берілген жиыны арқылы тренд сызығын есептейді және жаңа x мәндерінің берілген жиыны үшін қосымша y мәндерін қайтарады
• GROWTH: берілген x және y мәндерінің жиынына негізделген экспоненциалды өсу трендіндегі сандарды қайтарады
• LOGEST: x және y мәндерінің берілген жиыны үшін экспоненциалды тренд параметрлерін қайтарады
• STEYX: x және y мәндерінің берілген жиыны үшін регрессия жолындағы әрбір x үшін болжанған y мәнінің стандартты қатесін қайтарады

Ercole Palmeri