Excel-ის სტატისტიკური ფუნქციები: სახელმძღვანელო მაგალითებით, ნაწილი მესამე

Excel გთავაზობთ სტატისტიკური ფუნქციების ფართო სპექტრს, რომლებიც ასრულებენ გამოთვლებს საშუალოდან უფრო რთულ სტატისტიკურ განაწილებამდე და ტრენდის ხაზის ფუნქციებამდე.

ამ სტატიაში ჩვენ უფრო ღრმად ჩავუღრმავდებით Excel-ის სტატისტიკურ ფუნქციებს ტრენდის ხაზის ფუნქციების გამოსათვლელად.

გთხოვთ გაითვალისწინოთ, რომ ზოგიერთი სტატისტიკური ფუნქცია დაინერგა Excel-ის ბოლო ვერსიებში და, შესაბამისად, არ არის ხელმისაწვდომი ძველ ვერსიებში.

კითხვის სავარაუდო დრო: 12 მინუტი

ტრენდლაინის ფუნქციები

Forecast

Excel-ის პროგნოზის ფუნქცია პროგნოზირებს მომავალ წერტილს ხაზოვანი ტენდენციის ხაზზე, რომელიც შეესაბამება x და y მნიშვნელობების მოცემულ კომპლექტს.

სინტაქსი

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

საგნები

• x: რიცხვითი x-მნიშვნელობა, რომლისთვისაც გსურთ ახალი y-მნიშვნელობის პროგნოზირება.
• known_y's: ცნობილი y მნიშვნელობების მასივი
• known_x's: ცნობილი x მნიშვნელობების მასივი

გაითვალისწინეთ, რომ მასივის სიგრძე known_x უნდა იყოს იგივე, რაც known_y და დისპერსიას known_x ის არ უნდა იყოს ნული.

მაგალითად

შემდეგ ცხრილებში ფუნქცია FORECAST Excel გამოიყენება დამატებითი წერტილის პროგნოზირებისთვის საუკეთესოდ მორგებული სწორი ხაზის გასწვრივ ცნობილი x და y მნიშვნელობების სერიის მეშვეობით (შენახულია უჯრედებში F2:F7 და G2:G7).

როგორც ცხრილის F7 უჯრედშია ნაჩვენები, x=7-ზე მოსალოდნელი y მნიშვნელობის გამოსათვლელი ფუნქცია არის :=FORECAST( 7, G2:G7, F2:F7 )

ეს იძლევა შედეგს 32.666667 .

Intercept

Excel-ის პროგნოზირების ფუნქციებს შორის ვხვდებით Intercept. Excel-ის Intercept ფუნქცია ითვლის ხაზოვანი რეგრესიის ხაზის კვეთას (მნიშვნელობას y-ღერძის გადაკვეთაზე) x და 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 (Slope) Excel ითვლის წრფივი რეგრესიის ხაზის დახრილობას x და y მნიშვნელობების მოცემული ნაკრების მეშვეობით.

ფუნქციის სინტაქსი ასეთია:

სინტაქსი

= 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 მნიშვნელობების მოცემული ნაკრების მეშვეობით.

შემდეგ ფუნქცია აფართოებს ხაზოვანი ტენდენციის ხაზს დამატებითი y მნიშვნელობების გამოსათვლელად ახალი 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]: არასავალდებულო არგუმენტი, რომელიც უზრუნველყოფს რიცხვითი მნიშვნელობების ერთ ან მეტ მასივს, რომელიც წარმოადგენს ახალი x-მნიშვნელობების ერთობლიობას, რომლისთვისაც გსურთ გამოთვალოთ შესაბამისი ახალი y-მნიშვნელობები. თითოეული მასივი [new_x-ები] უნდა ემთხვეოდეს მასივს [known_x's]. თუ არგუმენტი [new_x-ები] გამოტოვებულია, დაყენებულია ტოლი [known_x's] .
• [ღირებულება]: არასავალდებულო ლოგიკური არგუმენტი, რომელიც აზუსტებს, არის თუ არა მუდმივი 'b' წრფივ განტოლებაში y = m x + b , უნდა აიძულოთ იყოს ნულის ტოლი. თვით [დაჯდა] არის TRUE (ან თუ ეს არგუმენტი გამოტოვებულია) მუდმივი b განიხილება ნორმალურად;
• თვით [დაჯდა] არის FALSE, b მუდმივი დაყენებულია 0-ზე და სწორი ხაზის განტოლება ხდება y = mx .

მაგალითად

შემდეგ ცხრილებში Excel Trend ფუნქცია გამოიყენება x და y მნიშვნელობების სერიის გასაგრძელებლად, რომლებიც დევს სწორ ხაზზე y = 2x + 10. ცნობილი x და y მნიშვნელობები ინახება A2-B5 უჯრედებში. ცხრილი და ასევე ნაჩვენებია ცხრილების გრაფიკში.

გაითვალისწინეთ, რომ არ არის აუცილებელი, რომ მოცემული წერტილები ზუსტად მოერგოს სწორი ხაზის გასწვრივ y = 2x + 10 (თუმცა ამ მაგალითში ისინი შეესაბამება). Excel-ის Trend ფუნქცია იპოვის საუკეთესოდ მორგებულ ხაზს თქვენს მიერ მოწოდებული მნიშვნელობების ნებისმიერი ნაკრებისთვის.

Trend ფუნქცია იყენებს ყველაზე მცირე კვადრატების მეთოდს საუკეთესო მორგებული ხაზის მოსაძებნად და შემდეგ იყენებს მას ახალი y მნიშვნელობების გამოსათვლელად ახალი x მნიშვნელობებისთვის.

ამ მაგალითში, მნიშვნელობები [new_x-ები] ინახება A8-A10 უჯრედებში და Excel-ის Trend ფუნქცია გამოყენებულია B8-B10 უჯრედებში ახალი შესაბამისი y მნიშვნელობების მოსაძებნად. როგორც ნაჩვენებია ფორმულის ზოლში, ფორმულა არის := TREND(B2:B5, A2:A5, A8:A10)

ხედავთ, რომ Trend ფუნქცია ფორმულების ზოლში ჩასმულია ფრჩხილებში { }. ეს მიუთითებს, რომ ფუნქცია შევიდა როგორც მასივის ფორმულა .

Growth

Excel-ის პროგნოზირების ფუნქციებს შორის ვხვდებით Growth. Ფუნქცია Growth Excel ითვლის ექსპონენციალური ზრდის მრუდს y მნიშვნელობების მოცემული ნაკრების და (სურვილისამებრ), x მნიშვნელობების ერთი ან მეტი ნაკრების მეშვეობით. შემდეგ ფუნქცია აფართოებს მრუდს დამატებითი y მნიშვნელობების გამოსათვლელად ახალი 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 მნიშვნელობების ნაკრები, რომლისთვისაც ფუნქცია ითვლის შესაბამის ახალ y მნიშვნელობებს. თუ გამოტოვებულია, ვარაუდობენ, რომ სიმრავლე [new_x-ები] უდრის [known_x's] და ფუნქცია აბრუნებს y მნიშვნელობებს, რომლებიც დევს გამოთვლილ ექსპონენციალური ზრდის მრუდზე.
• [ღირებულება]: არასავალდებულო ლოგიკური არგუმენტი, რომელიც აზუსტებს, არის თუ არა მუდმივი 'b' წრფივ განტოლებაში y = b * m^x , უნდა აიძულოთ იყოს 1-ის ტოლი. თუ [დაჯდა] არის TRUE (ან თუ ეს არგუმენტი გამოტოვებულია) მუდმივი b განიხილება ნორმალურად; თვით [დაჯდა] არის FALSE, b მუდმივი დაყენებულია 1-ზე და სწორი ხაზის განტოლება ხდება y = mx .

მაგალითად

შემდეგ ცხრილებში Excel-ის ზრდის ფუნქცია გამოიყენება x და y მნიშვნელობების სერიის გასაგრძელებლად, რომლებიც დევს ექსპონენციალური ზრდის მრუდზე y = 5 * 2^x. ისინი ინახება ელცხრილის A2-B5 უჯრედებში და ასევე გამოჩნდება ცხრილების სქემაში.

ზრდის ფუნქცია ითვლის ექსპონენციალური ზრდის მრუდს, რომელიც საუკეთესოდ ერგება ცნობილ x და y მნიშვნელობებს. ამ მარტივ მაგალითში საუკეთესოდ მორგებული მრუდი არის ექსპონენციალური მრუდი y = 5 * 2^x.

მას შემდეგ, რაც Excel გამოთვლის ექსპონენციალური ზრდის მრუდის განტოლებას, მას შეუძლია გამოიყენოს იგი ახალი y მნიშვნელობების გამოსათვლელად ახალი x მნიშვნელობებისთვის, რომლებიც მოწოდებულია უჯრედებში A8-A10.

ამ მაგალითში, მნიშვნელობები [new_x's] ინახება უჯრედებში A8-A10 და ფუნქცია Growth Excel-ის ჩასმულია B8-B10 უჯრედებში. როგორც ფორმულების ზოლშია ნაჩვენები, ამის ფორმულა არის:=Growth(B2:B5, A2:A5, A8:A10)

თქვენ ხედავთ, რომ ზრდის ფუნქცია ფორმულების ზოლში ჩასმულია ფრჩხილებში { }. ეს მიუთითებს, რომ ფუნქცია შევიდა როგორც მასივის ფორმულა .

გაითვალისწინეთ, რომ მიუხედავად იმისა, რომ ზემოთ მოყვანილ მაგალითში წერტილები ზუსტად ერგება მრუდის გასწვრივ y = 5 * 2^x, ეს არ არის არსებითი. Ფუნქცია Growth Excel იპოვის საუკეთესოდ მორგებულ მრუდს თქვენს მიერ მოწოდებული მნიშვნელობების ნებისმიერი ნაკრებისთვის.

ფინანსური ფუნქციები

ეფექტი

Ფუნქცია Effect Excel აბრუნებს ეფექტურ წლიურ საპროცენტო განაკვეთს მოცემული ნომინალური საპროცენტო განაკვეთისთვის და წლიური შედგენის პერიოდების მოცემულ რაოდენობაზე.

ეფექტური წლიური საპროცენტო განაკვეთი

ეფექტური წლიური საპროცენტო განაკვეთი არის პროცენტის საზომი, რომელიც მოიცავს საპროცენტო კაპიტალიზაციას და ხშირად გამოიყენება ფინანსური სესხების შესადარებლად სხვადასხვა კაპიტალიზაციის პირობებით.

ეფექტური წლიური საპროცენტო განაკვეთი გამოითვლება შემდეგი განტოლების გამოყენებით:

dove nominal_rate არის ნომინალური საპროცენტო განაკვეთი ე npery არის შერეული პერიოდების რაოდენობა წელიწადში.

ფუნქციის სინტაქსი ასეთია:

სინტაქსი

= EFFECT( nominal_rate, npery )

საგნები

• nominal_rate: ნომინალური საპროცენტო განაკვეთი (უნდა იყოს რიცხვითი მნიშვნელობა 0-დან 1-მდე)
• npery: შეერთების პერიოდების რაოდენობა წელიწადში (უნდა იყოს დადებითი მთელი რიცხვი).

მაგალითად

შემდეგი ცხრილი აჩვენებს Excel Effect ფუნქციის სამ მაგალითს:

თუ ფუნქციის შედეგი Effect ნაჩვენებია როგორც ათობითი ან აჩვენებს 0%-ს, ორივე ეს პრობლემა, სავარაუდოდ, ფუნქციის შემცველი უჯრედის ფორმატირებით არის განპირობებული. Effect.

ამიტომ პრობლემის გადაჭრა შესაძლებელია უჯრედის პროცენტულად, ათობითი ადგილების დაფორმატებით.

Გააკეთო ეს:

1. აირჩიეთ უჯრედები დაფორმატებისთვის პროცენტულად.
2. გახსენით "უჯრედების ფორმატირება" დიალოგური ფანჯარა ერთ-ერთი შემდეგი მეთოდის გამოყენებით:
   • დააწკაპუნეთ არჩეულ უჯრედზე ან დიაპაზონზე მარჯვენა ღილაკით და აირჩიეთ ვარიანტი უჯრედების ფორმატირება… კონტექსტური მენიუდან;
   • დააწკაპუნეთ Dialog Box Launcher-ზე ჩანართზე მდებარე ნომრების დაჯგუფებაში მთავარი Excel ლენტი;
   • გამოიყენეთ კლავიატურის მალსახმობი CTRL-1 (ანუ აირჩიეთ CTRL ღილაკი და დაჭერით, აირჩიეთ "1" (ერთი) გასაღები).
3. დიალოგურ ფანჯარაში "უჯრედების ფორმატირება":
   • დარწმუნდით, რომ ბარათი ნუმორო დიალოგური ფანჯრის ზედა ნაწილში არჩეულია.
   • აირჩიეთ პროცენტი სიიდან კატეგორია დიალოგური ფანჯრის მარცხენა მხარეს .ეს გამოაჩენს დამატებით ოფციებს მონიშვნის ველის მარჯვენა მხარეს, რაც საშუალებას მოგცემთ აირჩიოთ ათობითი ადგილების რაოდენობა, რომლის გამოჩენაც გსურთ.
   • მას შემდეგ რაც აირჩევთ ათწილადების რაოდენობას, რომლის ჩვენებაც გსურთ, დააწკაპუნეთ OK .

Nominal

Ფუნქცია Nominal Excel აბრუნებს ნომინალურ საპროცენტო განაკვეთს მოცემული ეფექტური საპროცენტო განაკვეთისთვის და შედგენილი პერიოდების მოცემულ რაოდენობაზე წელიწადში.

ფუნქციის სინტაქსი ასეთია:

სინტაქსი

= NOMINAL( effect_rate, npery )

საგნები

• effect_rate: ეფექტური საპროცენტო განაკვეთი (რიცხობრივი მნიშვნელობა 0-დან 1-მდე).
• npery: შეერთების პერიოდების რაოდენობა წელიწადში (უნდა იყოს დადებითი მთელი რიცხვი).

მაგალითად

შემდეგ ცხრილებში ფუნქცია Nominal Excel-ი გამოიყენება სამი სესხის ნომინალური საპროცენტო განაკვეთის გამოსათვლელად სხვადასხვა ვადით.

Ercole Palmeri