Excel статистикалык функциялары: мисалдар менен окуу куралы, үчүнчү бөлүк

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

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

Сураныч, кээ бир статистикалык функциялар Excelдин акыркы версияларында киргизилген, ошондуктан эски версияларында жеткиликтүү эмес экенин эске алыңыз.

Болжолдуу окуу убактысы: 12 мүнөт

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

Forecast

Excel'дин Болжолдоо функциясы сызыктуу тренд сызыгында келечектеги чекиттин берилген x жана y баалуулуктарынын жыйындысына туура келерин болжолдойт.

синтаксиси

= FORECAST( x, known_y's, known_x's )

аргументтер

• x: Жаңы y маанисин алдын ала айткыңыз келген сандык x-маани.
• known_y's: Белгилүү y баалуулуктарынын массиви
• known_x's: Белгилүү x баалуулуктарынын массиви

массивинин узундугуна көңүл буруңуз known_x менен бирдей болушу керек known_y жана дисперсиясы known_x ал нөл болушу керек эмес.

мисал

Кийинки электрондук жадыбалда функция FORECAST Excel белгилүү x жана у маанилеринин сериясы аркылуу эң туура түз сызык боюнча кошумча чекитти болжолдоо үчүн колдонулат (F2:F7 жана G2:G7 клеткаларында сакталган).

Электрондук жадыбалдын F7 уячасында көрсөтүлгөндөй, х=7де күтүлгөн у маанисин эсептөө функциясы :=FORECAST( 7, G2:G7, F2:F7 )

Бул натыйжа берет 32.666667 .

Intercept

Excelдин болжолдоо функцияларынын ичинен биз табабыз Intercept. Excel'дин Intercept функциясы х жана у маанилеринин берилген топтому боюнча сызыктуу регрессия сызыгынын кесилишин (y огунун кесилишиндеги маани) эсептейт.

синтаксиси

= INTERCEPT( known_y's, known_x's )

аргументтер

• known_y's: Белгилүү y баалуулуктарынын массиви
• known_x's: Белгилүү x баалуулуктарынын массиви

массивинин узундугуна көңүл буруңуз known_x менен бирдей болушу керек known_y жана дисперсиясы known_x ал нөл болушу керек эмес.

мисал

Төмөнкү таблица функциянын мисалын көрсөтөт Intercept Excel программасы аркылуу сызыктуу регрессия сызыгы өткөн чекитти эсептөө үчүн колдонулат known_x жана known_y (F2:F7 жана G2:G7 уячаларында көрсөтүлгөн) y огу менен кесилишет.

Le known_x жана known_y таблицадагы графикке түшүрүлгөн.

Электрондук жадыбалдын F9 уячасында көрсөтүлгөндөй, Intercept функциясынын формуласы болуп саналат :=INTERCEPT( G2:G7, F2:F7 )

бул натыйжа берет 2.4 .

Slope

Дагы бир абдан кызыктуу болжолдоо функциясы - эңкейиш (Slope) Excel берилген x жана у маанилеринин топтому аркылуу сызыктуу регрессия сызыгынын жантайышын эсептейт.

Функциянын синтаксиси:

синтаксиси

= SLOPE( known_y's, known_x's )

аргументтер

• known_y's: Белгилүү y баалуулуктарынын массиви
• known_x's: Белгилүү x баалуулуктарынын массиви

массивинин узундугуна көңүл буруңуз known_x менен бирдей болушу керек known_y жана дисперсиясы known_x ал нөл болушу керек эмес.

мисал

Төмөнкү таблица функциянын мисалын көрсөтөт Slope сызыктуу регрессия сызыгынын жантаюусун эсептөө үчүн колдонулган Excel (нак). known_x жана known_y, F2:F7 жана G2:G7 уячаларында.

Le known_x жана known_y таблицадагы графикке түшүрүлгөн.

Электрондук жадыбалдын F9 уячасында көрсөтүлгөндөй, Intercept функциясынын формуласы болуп саналат :=SLOPE( G2:G7, F2:F7 )

бул натыйжа берет 4.628571429.

Trend

Абдан кызыктуу Excel болжолдоо функциясы болуп саналат ТРЕНД Excel (Trend) сызыктуу тренд сызыгын y баалуулуктарынын берилген топтому жана (милдеттүү эмес), берилген x маанилеринин топтому аркылуу эсептейт.

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

Функциянын синтаксиси:

синтаксиси

= TREND( known_y's, [known_x's], [new_x's], [const] )

аргументтер

• known_y's: Белгилүү y баалуулуктарынын массиви
• [known_x's]: Белгилүү x маанилеринин бир же бир нече массивдери. Бул кошумча аргумент, эгер берилсе, узундугу топтому менен бирдей болушу керек known_y's. Эгерде алынып салынса, [known_x's] {1, 2, 3, …} маанисин алат.
• [new_x's]: Сиз тиешелүү жаңы y маанилерин эсептегиңиз келген жаңы x-маанилердин топтомун билдирген сандык маанилердин бир же бир нече массивдерин камсыз кылуучу кошумча аргумент. Ар бир массив [new_x's] [ массивине дал келиши керекknown_x's]. Эгерде аргумент [new_x's] алынып салынды, ал [ барабар болуп коюлдуknown_x's].
• [наркы]: Сызыктуу теңдемеде туруктуу "b" болушун көрсөтүүчү кошумча логикалык аргумент y = m x + b , нөлгө барабар болууга мажбурлоо керек. Өзүм [нарк] TRUE (же бул аргумент жок кылынса) b константасы нормалдуу түрдө каралат;
• Өзүм [нарк] ЖАЛГАН болсо, b константасы 0гө коюлат жана түз сызык теңдемеси болот y = mx .

мисал

Төмөнкү таблицада Excel Trend функциясы у = 2x + 10 түз сызыгында жаткан x жана у маанилеринин сериясын кеңейтүү үчүн колдонулат. Белгилүү x жана y маанилери A2-B5 уячаларында сакталат. электрондук жадыбал жана электрондук жадыбалдын графигинде да көрсөтүлөт.

Берилген чекиттер y = 2x + 10 түз сызыгын бойлото так дал келүүсү маанилүү эмес экенине көңүл буруңуз (бирок бул мисалда алар туура келет). Excelдин Тренд функциясы сиз берген баалуулуктардын бардык топтому үчүн эң ылайыктуу линияны табат.

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

Бул мисалда, маанилери [new_x's] A8-A10 уячаларында сакталат жана Excel'дин Тренд функциясы B8-B10 уячаларында жаңы тиешелүү y маанилерин табуу үчүн колдонулган. Формула тилкесинде көрсөтүлгөндөй, формула болуп саналат := TREND( B2:B5, A2:A5, A8:A10 )

Формула тилкесиндеги Тренд функциясы { } кашаанын ичинде камтылганын көрөсүз. Бул функция катары киргизилгендигин көрсөтүп турат массив формуласы .

Growth

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

Функциянын синтаксиси:

синтаксиси

= GROWTH( known_y's, [known_x's], [new_x's], [const] )

аргументтер

• known_y's: Белгилүү y баалуулуктарынын массиви
• [known_x's]: Белгилүү x маанилеринин бир же бир нече массивдери. Бул кошумча аргумент, эгер берилсе, узундугу топтому менен бирдей болушу керек known_y's. Эгерде алынып салынса, [known_x's] {1, 2, 3, …} маанисин алат.
• [new_x's]: Функция тиешелүү жаңы у маанилерин эсептеген жаңы x маанилеринин жыйындысы. Эгерде алынып салынса, анда [new_x's] дегенге барабарknown_x's] жана функция эсептелген экспоненциалдык өсүү ийри сызыгында жаткан y маанилерин кайтарат.
• [наркы]: Сызыктуу теңдемеде туруктуу "b" болушун көрсөтүүчү кошумча логикалык аргумент y = b * m^x , 1ге барабар болууга мажбурлоо керек. Эгерде [нарк] TRUE (же бул аргумент жок кылынса) b константасы нормалдуу түрдө каралат; Өзүм [нарк] ЖАЛГАН болсо, b константасы 1гө коюлат жана түз сызык теңдемеси болот y = mx .

мисал

Төмөнкү таблицада Excel өсүү функциясы экспоненциалдык өсүү ийри сызыгында y = 5 * 2^x турган x жана y баалуулуктарынын сериясын кеңейтүү үчүн колдонулат. Булар электрондук жадыбалдын A2-B5 уячаларында сакталат жана ошондой эле электрондук жадыбал диаграммасында пайда болот.

Өсүү функциясы берилген белгилүү x жана y маанилерине эң туура туура келген экспоненциалдык өсүү ийри сызыгын эсептейт. Бул жөнөкөй мисалда, эң ылайыктуу ийри сызык экспоненциалдык ийри y = 5 * 2^x болуп саналат.

Excel экспоненциалдык өсүү ийри сызыгынын теңдемесин эсептеп чыккандан кийин, аны A8-A10 уячаларында берилген жаңы x маанилери үчүн жаңы у маанилерин эсептөө үчүн колдоно алат.

Бул мисалда, маанилери [new_x's] A8-A10 уячаларында жана функцияда сакталат Growth Excel B8-B10 уячаларына киргизилген. Формула тилкесинде көрсөтүлгөндөй, мунун формуласы:=Growth( B2:B5, A2:A5, A8:A10 )

Формула тилкесиндеги Өсүү функциясы { } кашаанын ичинде турганын көрө аласыз. Бул функция катары киргизилгендигин көрсөтүп турат массив формуласы .

Жогорудагы мисалдагы чекиттер y = 5 * 2^x ийри сызыгына туура келгенине карабастан, бул маанилүү эмес. Функция Growth Excel сиз берген баалуулуктардын бардык топтому үчүн эң ылайыктуу ийри сызыкты табат.

Финансылык функциялар

Effect

Функция Effect Excel берилген номиналдык пайыздык чен үчүн жана жылына кошулган мезгилдердин берилген саны үчүн эффективдүү жылдык пайыздык ченди кайтарат.

Жылдык эффективдүү пайыздык чен

Жылдык эффективдүү пайыздык чен пайыздык капиталдаштырууну камтыган пайыздык өлчөм болуп саналат жана көп учурда ар кандай капиталдаштыруу шарттары менен финансылык кредиттерди салыштыруу үчүн колдонулат.

Жылдык эффективдүү пайыздык чен төмөнкү теңдемени колдонуу менен эсептелет:

көгүчкөн nominal_rate номиналдык пайыздык чен болуп саналат e npery жылына кошулган мезгилдердин саны болуп саналат.

Функциянын синтаксиси:

синтаксиси

= EFFECT( nominal_rate, npery )

аргументтер

• nominal_rate: Номиналдуу пайыздык чен (0 жана 1 ортосундагы сандык маани болушу керек)
• npery: Жылына кошулган мезгилдердин саны (оң бүтүн сан болушу керек).

мисал

Төмөнкү таблица Excel Effect функциясынын үч мисалын көрсөтөт:

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

Демек, маселени уячаны ондук бөлүкчөлөр менен пайыз менен форматтоо менен чечсе болот.

Бул үчүн:

1. Процент катары форматтоо үчүн клеткаларды тандаңыз.
2. Төмөнкү ыкмалардын бирин колдонуп "Формат ячейка" диалог кутусун ачыңыз:
   • Тандалган уячаны же диапазону оң баскыч менен чыкылдатып, параметрди тандаңыз Уячаларды форматтоо… контексттик менюдан;
   • Өтмөктөгү Сандарды топтоодогу Диалог кутусун ишке киргизгичти басыңыз Home Excel лентасы;
   • Баскычтоптун жарлыгын колдонуңуз CTRL-1 (б.а. CTRL баскычын тандап, аны басып туруп, "1" (бир) баскычын тандаңыз).
3. "Формат ячейка" диалог терезесинде:
   • Картаны текшериңиз Номери диалог кутусунун жогору жагында тандалган.
   • тандоо Процент тизмеден категория диалог кутусунун сол жагында .Бул белги кутучасынын оң жагында кошумча параметрлерди алып чыгып, сиз пайда болгуңуз келген ондук орундардын санын тандоого мүмкүндүк берет.
   • Көргүңүз келген ондук орундардын санын тандап алгандан кийин, чыкылдатыңыз OK .

Nominal

Функция Nominal Excel берилген эффективдүү пайыздык чен үчүн номиналдык пайыздык ченди жана жылына кошулган мезгилдердин берилген санын кайтарат.

Функциянын синтаксиси:

синтаксиси

= NOMINAL( effect_rate, npery )

аргументтер

• effect_rate: Эффективдүү пайыздык чен (0 жана 1 ортосундагы сандык маани).
• npery: Жылына кошулган мезгилдердин саны (оң бүтүн сан болушу керек).

мисал

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

Ercole Palmeri