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

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

• STDEV ფუნქცია.
• სტანდარტული გადახრის ფუნქცია.
• STDEV ფუნქცია

ჩვენ დაგვჭირდება ეს ფუნქციები გაყიდვების სტატისტიკაში, რათა განვსაზღვროთ გაყიდვების სტაბილურობა (XYZ ანალიზი). ეს მონაცემები შეიძლება გამოვიყენოთ როგორც ფასებისთვის, ასევე ასორტიმენტის მატრიცის შესაქმნელად (დარეგულირებისთვის) და გაყიდვების სხვა სასარგებლო ანალიზებისთვის, რაზეც აუცილებლად ვისაუბრებ მომავალ სტატიებში.

Წინასიტყვაობა

მოდით შევხედოთ ფორმულებს ჯერ მათემატიკური ენაზე, შემდეგ კი (ტექსტში ქვემოთ) დეტალურად გავაანალიზებთ ფორმულას Excel-ში და როგორ გამოიყენება მიღებული შედეგი გაყიდვების სტატისტიკის ანალიზში.

ამრიგად, სტანდარტული გადახრა არის შემთხვევითი ცვლადის სტანდარტული გადახრის შეფასება xრაც შეეხება მის მათემატიკურ მოლოდინს მისი დისპერსიის მიუკერძოებელი შეფასებით)))) ნუ შეგეშინდებათ გაუგებარი სიტყვების, იყავით მოთმინება და ყველაფერს გაიგებთ!

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

ახლა სტანდარტული გადახრა არის შემთხვევითი ცვლადის სტანდარტული გადახრის შეფასება xმის მათემატიკურ მოლოდინთან შედარებით, მისი დისპერსიის მიუკერძოებელი შეფასებით:

დისპერსია;

- მეშერჩევის ელემენტი;

ნიმუშის ზომა;

ნიმუშის საშუალო არითმეტიკული:

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

სამი სიგმის წესი() - ნორმალურად განაწილებული შემთხვევითი ცვლადის თითქმის ყველა მნიშვნელობა დევს ინტერვალში. უფრო მკაცრად, დაახლოებით 0,9973 ალბათობით, ნორმალურად განაწილებული შემთხვევითი ცვლადის მნიშვნელობა მდგომარეობს მითითებულ ინტერვალში (იმ პირობით, რომ მნიშვნელობა მართალია და არ არის მიღებული ნიმუშის დამუშავების შედეგად). ჩვენ გამოვიყენებთ 0.1-ის მომრგვალებულ ინტერვალს

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

ახლა სტანდარტული გადახრის ფუნქცია Excel-ში

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

გაყიდვების სტაბილურობის დასადგენად, ჩვენ არ გვჭირდება ჩავუღრმავდეთ სტანდარტული გადახრის ფუნქციების ყველა ვარიანტს. ჩვენ გამოვიყენებთ მხოლოდ ერთს:

STDEV ფუნქცია

STDEV(ნომერი 1;ნომერი 2;... )

ნომერი 1, ნომერი 2,..- 1-დან 30-მდე რიცხვითი არგუმენტი, რომელიც შეესაბამება საერთო პოპულაციას.

ახლა მოდით შევხედოთ მაგალითს:

შევქმნათ წიგნი და თვითნაკეთი მაგიდა. თქვენ გადმოწერთ ამ მაგალითს Excel-ში სტატიის ბოლოს.

Გაგრძელება იქნება!!!

Გამარჯობა კიდევ. აბა!? თავისუფალი წუთი მქონდა. Გავაგრძელოთ?

ასე რომ, გაყიდვების სტაბილურობა დახმარებით STDEV ფუნქციები

სიცხადისთვის, ავიღოთ რამდენიმე იმპროვიზირებული საქონელი:

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

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

ასე რომ, ჩვენ გვაქვს გაყიდვები, ახლა ჩვენ უნდა გამოვთვალოთ გაყიდვების საშუალო ღირებულებები პერიოდის მიხედვით.

საშუალო მნიშვნელობის ფორმულა არის AVERAGE (პერიოდული მონაცემები), ჩემს შემთხვევაში ფორმულა ასე გამოიყურება = AVERAGE (C6: E6)

ჩვენ ვიყენებთ ფორმულას ყველა პროდუქტზე. ეს შეიძლება გაკეთდეს არჩეული უჯრედის მარჯვენა კუთხის დაჭერით და სიის ბოლომდე გადატანით. ან მოათავსეთ კურსორი პროდუქტის სვეტზე და დააჭირეთ შემდეგი კლავიშების კომბინაციებს:

Ctrl + Down გადააადგილებს კურსორს სიის ზედა ნაწილში.

Ctrl + Right, კურსორი გადადის ცხრილის მარჯვენა მხარეს. კიდევ ერთხელ მარჯვნივ და ჩვენ მივალთ სვეტში ფორმულით.

ახლა ჩვენ ვამაგრებთ

Ctrl + Shift და დააჭირეთ ზემოთ. ამ გზით ჩვენ ვირჩევთ იმ ადგილს, სადაც ფორმულა იქნება დახატული.

და კლავიშთა კომბინაცია Ctrl + D გადაათრევს ფუნქციას იქ, სადაც გვჭირდება.

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

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

ჩვენ ვწერთ ფუნქციას და ვაყენებთ თითოეული პერიოდის გაყიდვების ღირებულებებს ფუნქციის მნიშვნელობებში. თუ თქვენ გაქვთ გაყიდვები ცხრილში ერთმანეთის მიყოლებით, შეგიძლიათ გამოიყენოთ დიაპაზონი, როგორც ჩემს ფორმულაში =STDEV(C6:E6) ან ჩამოთვალოთ საჭირო უჯრედები, გამოყოფილი მძიმით =STDEV(C6;D6;E6)

ახლა ყველა გაანგარიშება მზად არის. მაგრამ როგორ იცით, რა იყიდება მუდმივად და რა არა? მოდით დავაყენოთ კონვენცია XYZ სადაც,

X სტაბილურია

Y - მცირე გადახრებით

Z - არასტაბილური

ამისათვის ჩვენ ვიყენებთ შეცდომის ინტერვალებს. თუ რყევები მოხდება 10%-ის ფარგლებში, ჩვენ ვივარაუდებთ, რომ გაყიდვები სტაბილურია.

თუ 10-დან 25 პროცენტამდე, ეს იქნება Y.

და თუ ვარიაციის ღირებულება აღემატება 25%-ს, ეს არ არის სტაბილურობა.

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

IF (H6<0,1;"X";ЕСЛИ(H6<0,25;"Y";"Z"))

შესაბამისად, ჩვენ ვავრცელებთ ყველა ფორმულას ყველა სახელისთვის.

ვეცდები დაუყოვნებლივ ვუპასუხო კითხვას, რატომ არის 10% და 25% ინტერვალები?

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

მაგრამ აქ არის პროდუქტი 2, უკვე აშკარაა დესტაბილიზაცია. და ჩვენი გამოთვლები აჩვენებს Z, რომელიც გვეუბნება, რომ გაყიდვები არ არის სტაბილური. პროდუქტი 3 და პროდუქტი 5 აჩვენებს სტაბილურ შესრულებას, გთხოვთ გაითვალისწინოთ, რომ ვარიაცია 10%-ის ფარგლებშია.

იმათ. პროდუქტი 5 45, 46 და 45 ქულებით აჩვენებს ვარიაციას 1%, რაც არის სტაბილური რიცხვების სერია.

მაგრამ პროდუქტი 2 ინდიკატორებით 10, 50 და 5 აჩვენებს ვარიაციას 93%, რაც არ არის სტაბილური რიცხვების სერია.

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

"Y" არ გამოვიდა ჩემს ცხრილში, ვფიქრობ, რიცხვების სერიების სიცხადისთვის, ის უნდა დაემატოს. მე დავხატავ პროდუქტს 6...

ხედავთ, რიცხვების სერია 40, 50 და 30 აჩვენებს 20% ცვალებადობას. როგორც ჩანს, დიდი შეცდომა არ არის, მაგრამ გავრცელება მაინც მნიშვნელოვანია...

და ასე რომ შევაჯამოთ:

10.50.5 - Z არ არის სტაბილური. ვარიაცია 25%-ზე მეტი

40,50,30 - თქვენ შეგიძლიათ ყურადღება მიაქციოთ ამ პროდუქტს და გააუმჯობესოთ მისი გაყიდვები. ვარიაცია 25%-ზე ნაკლები, მაგრამ 10%-ზე მეტი

45,46,45 - X არის სტაბილურობა, თქვენ ჯერ არაფრის გაკეთება არ გჭირდებათ ამ პროდუქტით. ვარიაცია 10%-ზე ნაკლები

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

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

დისპერსიის გამოთვლა

შემთხვევითი ცვლადის ვარიაცია არის შემთხვევითი ცვლადის კვადრატული გადახრის მათემატიკური მოლოდინი მისი მათემატიკური მოლოდინისაგან.

ჩვენ ვიანგარიშებთ საერთო პოპულაციის მიხედვით

ხალიჩის გამოსათვლელად. ელოდება პროგრამის გამოყენებას DISP.G ფუნქციით და მისი სინტაქსი ასე გამოიყურება: “=DISP.G(Number1;Number2;…)”.

მაქსიმუმ 255 არგუმენტის გამოყენება შეიძლება, მეტი არა. არგუმენტები შეიძლება იყოს მარტივი რიცხვები ან მითითებები იმ უჯრედებზე, რომლებშიც ისინი მითითებულია. მოდით შევხედოთ როგორ გამოვთვალოთ დისპერსიები Microsoft Excel-ში:

1. პირველი ნაბიჯი არის უჯრედის არჩევა, სადაც გამოთვლის შედეგი იქნება ნაჩვენები და შემდეგ დააჭირეთ ღილაკს "ფუნქციის ჩასმა".

2. გაიხსნება ფუნქციების მართვის გარსი. აქ თქვენ უნდა მოძებნოთ "DISP.G" ფუნქცია, რომელიც შეიძლება იყოს "სტატისტიკური" ან "სრული ანბანური სია" კატეგორიაში. როდესაც ის იპოვეს, აირჩიეთ და დააჭირეთ "OK".

3. გაიხსნება ფანჯარა ფუნქციის არგუმენტებით. მასში თქვენ უნდა აირჩიოთ ხაზი "ნომერი 1" და ფურცელზე აირჩიეთ უჯრედების დიაპაზონი რიცხვების სერიებით.

4. ამის შემდეგ, გაანგარიშების შედეგები გამოჩნდება იმ უჯრედში, სადაც ფუნქცია იყო შეყვანილი.

ასე შეგიძლიათ მარტივად იპოვოთ დისპერსიები Excel-ში.

ჩვენ ვაკეთებთ გამოთვლებს ნიმუშის საფუძველზე

ამ შემთხვევაში, Excel-ში ნიმუშის განსხვავება გამოითვლება მნიშვნელით, რომელიც მიუთითებს არა რიცხვების მთლიან რაოდენობაზე, არამედ ერთზე ნაკლებზე. ეს კეთდება უფრო მცირე შეცდომისთვის სპეციალური ფუნქციის DISP.V გამოყენებით, რომლის სინტაქსია =DISP.V(Number1;Number2;...). მოქმედებების ალგორითმი:

• როგორც წინა მეთოდში, თქვენ უნდა აირჩიოთ უჯრედი შედეგისთვის.
• Function Wizard-ში უნდა იპოვოთ "DISP.B" კატეგორიაში "სრული ანბანური სია" ან "სტატისტიკური".

• შემდეგი, გამოჩნდება ფანჯარა და თქვენ უნდა გააგრძელოთ იგივე გზა, როგორც წინა მეთოდით.

ვიდეო: დისპერსიის გამოთვლა Excel-ში

დასკვნა

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

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

მაქსიმალური და მინიმალური ღირებულება

საშუალო წრფივი გადახრა

საშუალო წრფივი გადახრა არის აბსოლუტური (მოდული) გადახრების საშუალო გაანალიზებული მონაცემთა ნაკრებიდან. მათემატიკური ფორმულა არის:

ა- საშუალო წრფივი გადახრა,

X- გაანალიზებული მაჩვენებელი,

X̅- ინდიკატორის საშუალო მნიშვნელობა,

ნ

Excel-ში ამ ფუნქციას ეძახიან SROTCL.

SROTCL ფუნქციის არჩევის შემდეგ, ჩვენ მივუთითებთ მონაცემთა დიაპაზონს, რომელზეც უნდა მოხდეს გაანგარიშება. დააჭირეთ "OK".

დისპერსია

(მოდული 111)

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

s 2- დაკვირვების მონაცემებიდან გამოთვლილი ნიმუშის დისპერსია,

X- ინდივიდუალური ღირებულებები,

X̅- არითმეტიკული საშუალო ნიმუშისთვის,

ნ- მნიშვნელობების რაოდენობა გაანალიზებულ მონაცემთა ნაკრებში.

შესაბამისი Excel ფუნქციაა DISP.G. შედარებით მცირე ნიმუშების ანალიზისას (დაახლოებით 30 დაკვირვებამდე), უნდა გამოიყენოთ , რომელიც გამოითვლება შემდეგი ფორმულის გამოყენებით.

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

აირჩიეთ სასურველი ვარიანტი (ზოგადი ან შერჩევითი), მიუთითეთ დიაპაზონი და დააჭირეთ ღილაკს "OK". შედეგად მიღებული მნიშვნელობა შეიძლება იყოს ძალიან დიდი გადახრების წინასწარი კვადრატის გამო. სტატისტიკაში დისპერსია ძალიან მნიშვნელოვანი მაჩვენებელია, მაგრამ ის ჩვეულებრივ გამოიყენება არა მისი სუფთა სახით, არამედ შემდგომი გამოთვლებისთვის.

Სტანდარტული გადახრა

სტანდარტული გადახრა (RMS) არის დისპერსიის საფუძველი. ამ მაჩვენებელს ასევე უწოდებენ სტანდარტულ გადახრას და გამოითვლება ფორმულის გამოყენებით:

ზოგადი მოსახლეობის მიერ

ნიმუშის მიხედვით

თქვენ შეგიძლიათ უბრალოდ აიღოთ დისპერსიის საფუძველი, მაგრამ Excel-ს აქვს მზა ფუნქციები სტანდარტული გადახრისთვის: STDEV.Gდა STDEV.V(ზოგადი და სანიმუშო პოპულაციებისთვის, შესაბამისად).

სტანდარტული და სტანდარტული გადახრა, ვიმეორებ, სინონიმებია.

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

ვარიაციის კოეფიციენტი

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

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

STANDARDEV.G()/AVERAGE()

მონაცემთა დიაპაზონი მითითებულია ფრჩხილებში. საჭიროების შემთხვევაში გამოიყენეთ ნიმუშის სტანდარტული გადახრა (STDEV.V).

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

თქვენ ასევე შეგიძლიათ შეცვალოთ ფორმატი კონტექსტური მენიუდან არჩევით, სასურველი უჯრედის მონიშვნისა და მარჯვენა ღილაკით დაწკაპუნებით.

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

რხევის კოეფიციენტი

მონაცემთა დისპერსიის კიდევ ერთი მაჩვენებელი დღეს არის რხევის კოეფიციენტი. ეს არის ვარიაციის დიაპაზონის თანაფარდობა (სხვაობა მაქსიმალურ და მინიმალურ მნიშვნელობებს შორის) საშუალოსთან. არ არსებობს მზა Excel ფორმულა, ამიტომ მოგიწევთ სამი ფუნქციის გაერთიანება: MAX, MIN, AVERAGE.

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

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

ახლა გირჩევთ უყუროთ ვიდეო გაკვეთილს.

Საღამო მშვიდობისა

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

Excel იყენებს ამ დისპერსიის ფუნქციის რამდენიმე ვარიაციას:

მათემატიკური თეორია

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

კონკრეტულად რას აკეთებს სტანდარტული გადახრა? ის აფასებს X შემთხვევითი ცვლადის სტანდარტულ გადახრას მის მათემატიკური მოლოდინის მიმართ, მისი დისპერსიის მიუკერძოებელი შეფასების საფუძველზე. დამეთანხმებით, დამაბნეველად ჟღერს, მაგრამ ვფიქრობ, სტუდენტები მიხვდებიან, რაზე ვსაუბრობთ რეალურად!

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

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

პრაქტიკული განხორციელება Excel-ში

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

ჯერ გადახედეთ ფუნქციის მართლწერას და როგორც ხედავთ, ეს ძალიან მარტივია:

სტანდარტული გადახრა.G(_number1_;_number2_; ....), სადაც:

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

პირველ რიგში, ჩვენ უნდა გამოვთვალოთ საშუალო თვეების მიხედვით. ამისთვის გამოვიყენებთ AVERAGE ფუნქციას და მივიღებთ ფორმულას: = AVERAGE(C4:E4).
ახლა, ფაქტობრივად, STANDARDEVAL.G ფუნქციის გამოყენებით შეგვიძლია ვიპოვოთ სტანდარტული გადახრა, რომლის ღირებულებაში უნდა შევიტანოთ პროდუქტის გაყიდვები თითოეული პერიოდისთვის. შედეგი არის შემდეგი ფორმის ფორმულა: =სტანდარტული გადახრა.Г(C4;D4;E4).
ისე, სამუშაოს ნახევარი შესრულებულია. შემდეგი ნაბიჯი არის "ვარიაციის" ფორმირება, ეს მიიღება საშუალო მნიშვნელობის, სტანდარტული გადახრის გაყოფით და შედეგის პროცენტებად გადაქცევით. ჩვენ ვიღებთ შემდეგ ცხრილს:
ისე, ძირითადი გამოთვლები დასრულებულია, რჩება მხოლოდ იმის გარკვევა, გაყიდვები სტაბილურია თუ არა. ავიღოთ პირობად, რომ 10%-იანი გადახრები ითვლება სტაბილურად, 10-დან 25%-მდე ეს არის მცირე გადახრები, მაგრამ 25%-ზე მეტი არაფერი აღარ არის სტაბილური. პირობების მიხედვით შედეგის მისაღებად გამოვიყენებთ ლოგიკურს და შედეგის მისაღებად დავწერთ ფორმულას:

IF (H4<0,1;"стабильно";ЕСЛИ(H4<0,25;"нормально";"не стабильно"))

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

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

მას შემდეგ რაც ჩამოწერეთ პირობები, მაგალითად, "სტაბილური" - მწვანე, "ნორმალური" - ყვითელი და "არასტაბილური" - წითელი, მივიღებთ ლამაზ და გასაგებ ცხრილს, რომელშიც შეგიძლიათ ნახოთ, რას უნდა მიაქციოთ ყურადღება პირველ რიგში.

VBA-ს გამოყენება STDEV.Y ფუნქციისთვის

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

ფუნქცია MyStDevP(Arr) Dim x, aCnt&, aSum#, aAver#, tmp# თითოეული x-ისთვის Arr aSum = aSum + x "გამოთვალეთ მასივის ელემენტების ჯამი aCnt = aCnt + 1 "გამოთვალეთ ელემენტების რაოდენობა შემდეგი x aAver = aSum / aCnt "საშუალო მნიშვნელობა თითოეული x-ისთვის Arr tmp = tmp + (x - aAver) ^ 2 "გამოთვალეთ მასივის ელემენტებს შორის სხვაობის კვადრატების ჯამი და საშუალო მნიშვნელობა შემდეგი x MyStDevP = Sqr(tmp / aCnt ) "გამოთვალეთ STANDARDEV.G() დასრულების ფუნქცია

ფუნქცია MyStDevP(Arr) Dim x , aCnt & , aSum #, aAver#, tmp# თითოეული x In Arr aSum = asum + x "გამოთვალეთ მასივის ელემენტების ჯამი

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

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

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

STD ფუნქცია არგუმენტად აბრუნებს მთელი პოპულაციის სტანდარტულ გადახრას.

STDEV.V, STDEV.G, STDEV და STDEV გამოყენების მაგალითები

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

წყაროს მონაცემთა ცხრილი:

პირველი, მოდით გამოვთვალოთ კლიენტების საშუალო რაოდენობა, რომლებთანაც მენეჯერები მუშაობდნენ ყოველდღიურად:

საშუალო (B2:B11)

ეს ფუნქცია ითვლის საშუალო არითმეტიკას B2:B11 დიაპაზონისთვის, რომელიც შეიცავს მონაცემებს პირველი მენეჯერის მიერ ყოველდღიურად მიღებული კლიენტების რაოდენობის შესახებ. ანალოგიურად, ჩვენ ვიანგარიშებთ კლიენტების საშუალო რაოდენობას დღეში მეორე მენეჯერისთვის. ჩვენ ვიღებთ:

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

STDEV.B(B2:B11)

B2:B11 – შესწავლილი მნიშვნელობების დიაპაზონი. ანალოგიურად, ჩვენ განვსაზღვრავთ სტანდარტულ გადახრას მეორე მენეჯერისთვის და ვიღებთ შემდეგ შედეგებს:

როგორც ხედავთ, პირველი მენეჯერის შესრულების მაჩვენებლები ხასიათდება მნიშვნელობების მაღალი ცვალებადობით (გაფანტვით) და, შესაბამისად, საშუალო არითმეტიკული აბსოლუტურად არ ასახავს შესრულების რეალურ სურათს. 1.2-ის გადახრა მიუთითებს მეორე მენეჯერის უფრო სტაბილურ და, შესაბამისად, ეფექტურ მუშაობაზე.



Excel-ში STANDARDEV ფუნქციის გამოყენების მაგალითი

მაგალითი 2. კოლეჯის სტუდენტების ორ სხვადასხვა ჯგუფს ჩაუტარდა გამოცდა იმავე დისციპლინაში. მოსწავლეთა მუშაობის შეფასება.

წყაროს მონაცემთა ცხრილი:

მოდით განვსაზღვროთ მნიშვნელობების სტანდარტული გადახრა პირველი ჯგუფისთვის ფორმულის გამოყენებით:

STDEV (A2:A11)

ანალოგიურ გამოთვლას გავაკეთებთ მეორე ჯგუფისთვისაც. შედეგად ვიღებთ:

მიღებული მნიშვნელობები მიუთითებს იმაზე, რომ მეორე ჯგუფის მოსწავლეები გაცილებით უკეთ იყვნენ მომზადებულნი გამოცდისთვის, ვინაიდან ქულების გავრცელება შედარებით მცირეა. გაითვალისწინეთ, რომ STANDARDEV ფუნქცია გარდაქმნის ტექსტის მნიშვნელობას „ჩავარდნილი“ რიცხვითი მნიშვნელობით 0 (ნულოვანი) და ითვალისწინებს მას გაანგარიშებისას.

STANDARDEV.G ფუნქციის მაგალითი Excel-ში

მაგალითი 3. დაადგინეთ სტუდენტების გამოცდისთვის მომზადების ეფექტურობა უნივერსიტეტის ყველა ჯგუფისთვის.

შენიშვნა: წინა მაგალითისგან განსხვავებით, გაანალიზდება არა ნიმუში (რამდენიმე ჯგუფი), არამედ სტუდენტების მთელი რაოდენობა - საერთო პოპულაცია. სტუდენტები, რომლებიც არ ჩააბარებენ გამოცდას, მხედველობაში არ მიიღება.

მოდით შეავსოთ მონაცემთა ცხრილი:

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

საშუალო (B2:B21)

გადახრის დასადგენად, ჩვენ შემოგთავაზებთ ფორმულას:

STDEV.G(B2:B21)

შედეგად ვიღებთ:

მიღებული მონაცემები მიუთითებს აკადემიურ მოსწრებას საშუალოზე ოდნავ დაბალი (<4), величина разброса характеризует довольно большое количество студентов, получивших 5 и 3 соответственно (учитывая, что анализировались только данные из диапазона от 3 до 5).

Excel-ში სტანდარტული გადახრის ფუნქციის მაგალითი

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

მონაცემთა ცხრილი:

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

STDEV (B2:B21)

შედეგად ვიღებთ:

მნიშვნელობების მაღალი გავრცელება თანმიმდევრობით მიუთითებს სტუდენტების დიდ რაოდენობაზე, რომლებმაც ჩააბარეს გამოცდა.

STDEV.V, STDEV.G, STDEV და STDEV გამოყენების მახასიათებლები

STDEV და STDEV ფუნქციებს აქვთ იდენტური სინტაქსი, როგორიცაა:

FUNCTION (მნიშვნელობა1; [მნიშვნელობა2];…)

აღწერა:

• FUNCTION - ზემოთ განხილული ორი ფუნქციიდან ერთ-ერთი;
• ღირებულება 1 - აუცილებელი არგუმენტი, რომელიც ახასიათებს ერთ-ერთი ნიმუშის მნიშვნელობას (ან საერთო პოპულაციას);
• [მნიშვნელობა2] – არჩევითი არგუმენტი, რომელიც ახასიათებს შესასწავლი დიაპაზონის მეორე მნიშვნელობას.

შენიშვნები:

1. ფუნქციის არგუმენტები შეიძლება შეიცავდეს სახელებს, ციფრულ მნიშვნელობებს, მასივებს, ციფრული მონაცემების დიაპაზონების მითითებებს, ლოგიკურ მნიშვნელობებს და მათზე მითითებებს.
2. ორივე ფუნქცია იგნორირებას უკეთებს ცარიელ მნიშვნელობებს და ტექსტურ მონაცემებს, რომლებიც შეიცავს მონაცემთა გაცემულ დიაპაზონში.
3. ფუნქციები აბრუნებენ შეცდომის კოდს #VALUE, თუ შეცდომის მნიშვნელობები ან ტექსტური მონაცემები, რომლებიც ვერ გადაიქცევა ციფრულ მნიშვნელობებად, გადაეცა არგუმენტებად.

STDEV.V და STDEV.G ფუნქციებს აქვთ შემდეგი სინტაქსი:

FUNCTION (ნომერი1, [ნომერი2],…)

აღწერა:

• FUNCTION – ნებისმიერი ფუნქცია STANDARDDEVIATION.V ან STANDARDDEVIATION.G;
• ნომერი1 – საჭირო არგუმენტი, რომელიც ახასიათებს რიცხობრივ მნიშვნელობას, რომელიც აღებულია ნიმუშიდან ან მთელი პოპულაციისგან;
• ნომერი2 – არჩევითი არგუმენტი, რომელიც ახასიათებს შესასწავლი დიაპაზონის მეორე ციფრულ მნიშვნელობას.

შენიშვნა: ორივე ფუნქცია არ შეიცავს ციფრებს, რომლებიც წარმოდგენილია როგორც ტექსტური მონაცემები და არც ლოგიკური მნიშვნელობები TRUE და FALSE, გაანგარიშების პროცესში.

შენიშვნები:

1. სტანდარტული გადახრა ფართოდ გამოიყენება სტატისტიკურ გამოთვლებში, როდესაც მნიშვნელობების დიაპაზონის საშუალო პოვნა არ იძლევა მონაცემთა განაწილების ნამდვილ წარმოდგენას. ეს გვიჩვენებს მნიშვნელობების განაწილების პრინციპს კონკრეტულ ნიმუშში ან მთელ თანმიმდევრობაში საშუალო მნიშვნელობასთან შედარებით. მაგალითი 1 ნათლად შეისწავლის ამ სტატისტიკური პარამეტრის პრაქტიკულ გამოყენებას.
2. ფუნქციები STANDARDEVAL და STANDDREVAL.B უნდა იქნას გამოყენებული პოპულაციის მხოლოდ ნაწილის გასაანალიზებლად და გამოთვლა პირველი ფორმულის გამოყენებით, ხოლო STANDARDEV.G და STANDARDEVAL უნდა აიღონ შეყვანილი მონაცემები მთელი პოპულაციის შესახებ და გამოთვალონ მეორე ფორმულის გამოყენებით.
3. Excel შეიცავს ჩაშენებულ ფუნქციებს STDEV და STDEV, რომლებიც შენარჩუნებულია Microsoft Office-ის ძველ ვერსიებთან თავსებადობისთვის. ისინი შეიძლება არ იყოს ჩართული პროგრამის შემდგომ ვერსიებში, ამიტომ მათი გამოყენება არ არის რეკომენდებული.
4. სტანდარტული გადახრის საპოვნელად გამოიყენება ორი საერთო ფორმულა: S=√((∑_(i=1)^n▒(x_i-x_av)^2)/(n-1)) და S=√((∑_ (i= 1)^n▒(x_i-x_ср)^2)/n), სადაც:

• S – სტანდარტული გადახრის სასურველი მნიშვნელობა;
• n - განიხილება მნიშვნელობების დიაპაზონი (ნიმუში);
• x_i – ინდივიდუალური მნიშვნელობა ნიმუშიდან;
• x_avg – საშუალო არითმეტიკული მნიშვნელობა განხილული დიაპაზონისთვის.