ამ სტატიაში განვიხილავთ, თუ როგორ გავაანალიზოთ მონაცემები კრებსითი ცხრილის გამოყენებით. ტრენინგისთვის შეგიძლიათ გამოიყენოთ ამ ბმულზე არსებული ცხრილი (მარტივი ცხრილი.xlsx).
პირველი, რისი ანალიზიც შეიძლება დაგჭირდეთ კრებსითი ცხრილის გამოყენებით, არის ქვეჯამების შეჯამება. ჩვენს მაგალითში, ეს შეიძლება იყოს გაყიდვების მოცულობის გამოთვლა ყველა მაღაზიისთვის თითოეული თარიღისთვის.
ამისათვის დააწკაპუნეთ კრებსითი ცხრილის რომელიმე მწკრივის სათაურზე (ჩვენს მაგალითში ეს არის ველები თარიღი, გაყიდვის პუნქტიდა ტელეფონის ბრენდი), და გახსნილ ჩანართებში ღერძულ ცხრილებთან მუშაობაგადადით ჩანართზე Პარამეტრები. თქვენ უნდა დააჭიროთ მასზე არსებულ ღილაკს. ველის პარამეტრებიჯგუფში აქტიური ველი.
ფანჯარაში, რომელიც იხსნება, პირველი ჩანართი იქნება ჩანართი.
ასეთი სანიშნეს არარსებობა ნიშნავს, რომ თქვენ არ შეგირჩევიათ მწკრივის სათაური, ანუ კურსორი მოთავსებულია ციფრული მნიშვნელობის მქონე უჯრედზე.
სანიშნე ქვეჯამები და ფილტრებითქვენ შეგიძლიათ აირჩიოთ პირობა ქვეჯამების ჩვენებისთვის. შემოთავაზებულია შემდეგი პირობები:
ავტომატური ქვეჯამების დაყენებით, ვიღებთ შემდეგ ცხრილს, რომელიც შეიცავს ქვეჯამებს თითოეული პირობისთვის:
თუ ქვეჯამების დაყენება ბრძანებით ველის პარამეტრები, არ იძლევა ხილულ შედეგებს, შეამოწმეთ პარამეტრები ქვეჯამების ჩვენებისთვის ბრძანების გამოყენებით ქვეჯამებიჯგუფები განლაგებაჩანართები კონსტრუქტორი.
ვთქვათ, გვინდა გამოვაჩინოთ ქვეჯამები მხოლოდ თარიღებისთვის, ქვეჯამების დამალვა გაყიდვების პუნქტებისთვის. ამისათვის დააწკაპუნეთ ცხრილის ნებისმიერ ველზე მაღაზიის სახელწოდებით და გამოიძახეთ კონტექსტური მენიუ. მასში თქვენ უნდა გააუქმოთ მდგომარეობა ქვეჯამი: გასაყიდი წერტილი. როგორც ვხედავთ, ქვეჯამები რჩება მხოლოდ თარიღებისთვის:
ხშირად საჭიროა მონაცემების დახარისხება PivotTable-ში, რათა უკეთ გავიგოთ. ამისათვის უბრალოდ აირჩიეთ ველი, რომლის მიხედვითაც გსურთ დახარისხება, გადადით ჩანართზე გენერალი, ჯგუფში რედაქტირებადააჭირეთ ღილაკს დახარისხება და გაფილტვრადა დააყენეთ თქვენთვის საჭირო დახარისხების პირობები.
კრებსითი ცხრილის ინფორმაციის გასაანალიზებლად ძალიან სასარგებლო ფუნქციაა მონაცემების დაჯგუფების შესაძლებლობა. მაგალითად, ჩვენ უნდა დავაჯგუფოთ ჩვენი გაყიდვები თვის კვირების მიხედვით. ამისათვის აირჩიეთ თარიღები, რომლებიც შედის პირველ კვირაში (15.05-21.05):
გთხოვთ, გაითვალისწინოთ, რომ შერჩევის გამარტივებისთვის, ჩვენ დავაშალეთ მონაცემები ცალკეული მაღაზიებისთვის, ღილაკის + გამოყენებით უჯრედის მარცხენა მხარეს მაღაზიის სახელით.
შემდეგი, თქვენ უნდა გაუშვათ ბრძანება დაჯგუფება შერჩევითჯგუფები ჯგუფიჩანართები Პარამეტრები. ცხრილში გამოჩნდება ახალი სვეტი, რომელშიც ველი ჯგუფი 1გავაერთიანებთ ჩვენ მიერ არჩეულ ველებს.
რჩება მხოლოდ ჯგუფის სახელის გადარქმევა უჯრედის უბრალოდ რედაქტირებით:
დაჯგუფების გასაუქმებლად, უბრალოდ გამოიყენეთ ბრძანება გაუჯგუფებაიმავე ჯგუფიდან, გასაუჯგუფებელი ველის არჩევის შემდეგ. გთხოვთ გაითვალისწინოთ, რომ თქვენ არ შეგიძლიათ გააუქმოთ ველი, რომელიც ჩვენ შევიტანეთ კრებსითი ცხრილის აგების პირობაში - მაგალითად, ველი გასაყიდი წერტილიან თარიღი.
მოდით განვიხილოთ მონაცემების ჩვენების სხვა გზა, რომელიც დაგვეხმარება გავაანალიზოთ ინფორმაცია ჩვენი ცხრილიდან. მაგალითად, ჩვენ უნდა ვიცოდეთ შემოსავლის მოცულობა არა ფულადი თვალსაზრისით, არამედ მთლიანი შემოსავლის პროცენტულად მთელი გაყიდვების პერიოდისთვის.
ამისათვის აირჩიეთ ნებისმიერი უჯრედი ჩვენი კრებსითი ცხრილის შემოსავლების სვეტში. ამის შემდეგ თქვენ უნდა შეასრულოთ ბრძანება ველის პარამეტრებიჯგუფში აქტიური ველიჩანართები Პარამეტრები.
დიალოგურ ფანჯარაში, რომელიც იხსნება, გადადით ჩანართზე დამატებითი გამოთვლებიდა აირჩიეთ ჩამოსაშლელი მენიუდან მთლიანი პროცენტი სვეტების მიხედვით. OK ღილაკზე დაჭერის შემდეგ ჩვენი ცხრილი ასე გამოიყურება:
თუ ცხრილში მონაცემები არ იქნება ნაჩვენები პროცენტულად, შეამოწმეთ უჯრედების ნომრის ფორმატის პარამეტრები (ეს შეიძლება გაკეთდეს დაუყოვნებლივ დიალოგურ ფანჯარაში ველის პარამეტრებიღილაკზე დაჭერით რიცხვითი ფორმატი, ან კონტექსტური მენიუდან შესაბამისი ფანჯრის გამოძახებით).
Excel 2007-ში მონაცემთა სიების (მონაცემთა ცხრილების) გასაანალიზებლად, რომლებსაც აქვთ მრავალი სტრიქონი ან ჩანაწერი, ხშირად გამოიყენება ინსტრუმენტები, როგორიცაა PivotTables. PivotTables ბევრად აადვილებს მონაცემთა ნახვას, მანიპულირებას და შეჯამებას Excel 2007 სიებში.
იმისათვის, რომ Excel-მა შეყვანილ მონაცემებთან იმუშაოს, როგორც მონაცემთა ცხრილი, და არა როგორც მონაცემთა მარტივი ნაკრები, ის უნდა იყოს ფორმატირებული ცხრილის სახით. ამისათვის დააწკაპუნეთ ცხრილის ღილაკზე ჩასმა ჩანართზე. ცხრილის შექმნა დიალოგურ ფანჯარაში (სურათი 1) მიუთითეთ განკუთვნილი დიაპაზონი, რომელშიც განთავსდება ცხრილი და აირჩიეთ ველი Table with headers.
ბრინჯი. ერთი.
ფორმატირება გამოყენებული იქნება ამ დიაპაზონში, ე.ი. გამოიყენება ნაგულისხმევად დაყენებული ცხრილის სწრაფი სტილი, ხოლო გააქტიურებულია კონტექსტუალური ხელსაწყოები ზოგადი სახელწოდებით "ცხრილებთან მუშაობა", რომლებიც შედის კონტექსტუალური ჩანართში Design. თქვენ ასევე შეგიძლიათ გამოიყენოთ ფორმატი როგორც ცხრილის ხელსაწყო საწყისი ჩანართზე ცხრილის ფორმატირებისთვის.
ბრინჯი. 2.
ამ ცხრილში მოხსენების შესაქმნელად მიზანშეწონილია გამოიყენოთ მძლავრი "Pivot Table" ინსტრუმენტი. ამ ხელსაწყოს მონაცემთა სიებში ან მონაცემთა ცხრილებზე გამოსაყენებლად, მონაცემთა ცხრილის ერთ-ერთი უჯრედი უნდა იყოს გააქტიურებული, მაგალითად, უჯრა "მარაგში" ცხრილში. შემდეგ დააჭირეთ ღილაკს "Pivot Table", რომელიც მდებარეობს "Table" ჯგუფში "Insert" ჩანართზე (სურათი 3).
ბრინჯი. 3.
დიალოგურ ფანჯარაში Create PivotTable, რომელიც იხსნება, უნდა აირჩიოთ (მოინიშნოთ) ცხრილი ან დიაპაზონი და მიუთითოთ სად გსურთ მოხსენების განთავსება (სასურველია ახალ ფურცელზე), შემდეგ დააწკაპუნეთ OK. გაიხსნება კრებსითი ცხრილის სპეციალური ოსტატი (სურათი 4).
ბრინჯი. ოთხი.
მოხსენების სურათი (PivotTable1) ნაჩვენებია სამუშაო ფურცლის მარცხენა მხარეს, ხოლო კრებსითი ცხრილის შექმნის ხელსაწყოები განთავსებულია ფურცლის მარჯვენა მხარეს: ოთხი ცარიელი ადგილი და ველების სია. ანგარიშის შესაქმნელად, მარჯვენა მხარეს გადაიტანეთ საჭირო ველები კრებსითი ცხრილის შესაბამის უბნებზე: „ანგარიშის ფილტრი“, „სვეტების სახელები“, „სტრიქონების სახელები“ და „მნიშვნელობები“.
მაგალითად, თუ აირჩევთ ველებს: საწყობის ნომერი, სახელწოდება, ფასი (UAH) და გადაიტანეთ ისინი შესაბამის უბნებზე: "სვეტების სახელები", "სტრიქონების სახელები" და "მნიშვნელობები", მაშინ ისინი გამოჩნდება ამ ადგილებში მარჯვენა მხარე. ამ შემთხვევაში, კრებსითი ცხრილი ან ანგარიში აშენდება სამუშაო ფურცლის მარცხენა მხარეს (ნახ. 5).
ბრინჯი. 5.
აღსანიშნავია, რომ „ღირებულებების“ არეალში კეთდება ზოგიერთი მათემატიკური გამოთვლები, მაგალითად, შეჯამება (ჯამობა ფასის ველში). გამოთვლის ტიპის შესაცვლელად, "ღირებულებების" ველში დააწკაპუნეთ მაუსის მარცხენა ღილაკით ველზე "თანხა ფასის მიხედვით" და გახსნილ მენიუში აირჩიეთ "ღირებულების ველის პარამეტრები" ბრძანება, შემდეგ დიალოგურ ფანჯარაში "ღირებულების ველის პარამეტრები". აირჩიეთ საჭირო ფუნქცია და დააჭირეთ ღილაკს OK.
კრებსითი ცხრილის სტრუქტურის შესაცვლელად ფურცლის მარჯვენა მხარეს გადაიტანეთ ველები კრებსითი ცხრილის სხვა უბანზე ან წაშალეთ ისინი. უნდა აღინიშნოს, რომ ველის წასაშლელად, ის ცხრილის გარეთ უნდა გადაიტანოთ.
Excel 2007-ში მონაცემების ანალიზის მძლავრი ინსტრუმენტებია:
რა-თუ ანალიზის ხელსაწყოები მოთავსებულია მონაცემთა ჩანართზე მონაცემთა ინსტრუმენტების ჯგუფში, და იპოვეთ გადაწყვეტილებები მონაცემთა ჩანართზე ანალიზის ჯგუფში.
პარამეტრების შერჩევა იძლევა ერთი არგუმენტის ფუნქციისთვის პარამეტრის შერჩევის პრობლემის გადაწყვეტას. სცენარის მენეჯერი შექმნილია სცენარების შესაქმნელად (ჯერ დაამატეთ რამდენიმე სცენარი სხვადასხვა შემთხვევისთვის), შემდეგ კი გადახედეთ სცენარებს პროცესის პროგნოზირებისთვის და სცენარის შესახებ მოხსენების შესაქმნელად.
პროგრამა „ამოხსნის ძიება“ განკუთვნილია განტოლებათა რთული სისტემების, წრფივი და არაწრფივი ოპტიმიზაციის ამოცანების ამოსახსნელად. Solver დანამატი ეფუძნება განმეორებით მეთოდებს.
Excel არ არის მხოლოდ ცხრილების რედაქტორი, არამედ ძლიერი ინსტრუმენტი სხვადასხვა მათემატიკური და სტატისტიკური გამოთვლებისთვის. აპლიკაციას აქვს ამ ამოცანებისთვის განკუთვნილი ფუნქციების დიდი რაოდენობა. თუმცა, ყველა ეს ფუნქცია არ არის ჩართული ნაგულისხმევად. სწორედ ასეთ ფარულ ფუნქციებს ეკუთვნის ინსტრუმენტების ნაკრები. "Მონაცემთა ანალიზი". მოდით გავარკვიოთ, როგორ შეიძლება მისი ჩართვა.
ისარგებლოს ფუნქციით გათვალისწინებული ფუნქციებით "Მონაცემთა ანალიზი", თქვენ უნდა გაააქტიუროთ ხელსაწყოების ჯგუფი "ანალიზის პაკეტი" Microsoft Excel-ის პრეფერენციებში გარკვეული ნაბიჯების შემდეგ. ამ მოქმედებების ალგორითმი თითქმის იგივეა პროგრამის 2010, 2013 და 2016 წლების ვერსიებისთვის და მხოლოდ მცირე განსხვავებები აქვს 2007 წლის ვერსიაში.
ამ ნაბიჯების დასრულების შემდეგ, მითითებული ფუნქცია გააქტიურდება და მისი ხელსაწყოების ნაკრები ხელმისაწვდომი იქნება Excel-ის ლენტაზე.
ახლა ჩვენ შეგვიძლია გავუშვათ ჯგუფში არსებული ნებისმიერი ინსტრუმენტი "Მონაცემთა ანალიზი".
თითოეულ ფუნქციაში მუშაობას აქვს მოქმედებების საკუთარი ალგორითმი. ჯგუფის ზოგიერთი ინსტრუმენტის გამოყენება "Მონაცემთა ანალიზი"აღწერილია ცალკეულ გაკვეთილებში.
როგორც ხედავთ, თუმცა ხელსაწყოთა ყუთი "ანალიზის პაკეტი"და სტანდარტულად არ არის გააქტიურებული, მისი ჩართვის პროცესი საკმაოდ მარტივია. ამავდროულად, ქმედებების მკაფიო ალგორითმის ცოდნის გარეშე, ნაკლებად სავარაუდოა, რომ მომხმარებელმა შეძლოს სწრაფად გაააქტიუროს ეს ძალიან სასარგებლო სტატისტიკური ფუნქცია.
დავალება #1
სტატისტიკური მონაცემების ანალიზი MS Excel-ში
სამუშაოს მიზანი: ვისწავლოთ სტატისტიკური მონაცემების დამუშავება MS Excel-ის ჩაშენებული ფუნქციების გამოყენებით; შეისწავლეთ ანალიზის პაკეტის მახასიათებლები და მისი ხელსაწყოები: " შემთხვევითი რიცხვების გენერაცია », "ბარული დიაგრამა" , " Აღწერითი სტატისტიკა"დამუშავების სიჩქარის გაზომვების მაგალითზე.
ლაბორატორიული სამუშაოს "სატრანსპორტო საშუალებების სიჩქარის გაზომვა" (დისციპლინაში "გზაების კვლევა და დიზაინი") მითითებების შესაბამისად, დაამუშავეთ ექსპერიმენტული გაზომვის მონაცემები მათემატიკური სტატისტიკის მეთოდების გამოყენებით Excel პროგრამაში. Რისთვის:
1. სტატისტიკური მახასიათებლების გამოთვლა ჩაშენებული ფუნქციების გამოყენებით: - მოძრაობის სიჩქარის მინიმალური მნიშვნელობა Vmin;
მოძრაობის სიჩქარის მაქსიმალური მნიშვნელობა Vmax; - ვავის მოძრაობის სიჩქარის საშუალო მნიშვნელობა;
სტანდარტული გადახრა S;
საშუალო Sp-ის სტანდარტული გადახრა;
სტუდენტის კოეფიციენტი (ნდობის ინტერვალის დასადგენად) t; - ნდობის ინტერვალი Р = 0.95-ისთვის.
2. მიიღეთ სტატისტიკური მახასიათებლები ინსტრუმენტის გამოყენებით "Აღწერითი სტატისტიკა"დამატებითი პაკეტიდან" მონაცემთა ანალიზი ".
3. შექმენით სიჩქარის განაწილების ჰისტოგრამა.
4. ააგეთ კუმულაციური მრუდი (კუმულაციური სიხშირის მრუდი).
5. შექმენით თეორიული მრუდი მოძრაობის სიჩქარის განაწილებისთვის.
საკმარისი რაოდენობის საწყისი მონაცემების მისაღებად (სიჩქარის გაზომვის შედეგები), გამოიყენეთ სიმულაციური ექსპერიმენტი " შემთხვევითი რიცხვების თაობა» დამატებები „მონაცემთა ანალიზი“.
პ.პ.ს შესრულებისას. 3 და 4, აირჩიეთ სიჩქარის ინტერვალი ("ჯიბე" - Excel ტერმინოლოგიაში), რომელიც საშუალებას გაძლევთ მიიღოთ ყველაზე სიმეტრიული ჰისტოგრამა, რომელიც აჩვენებს ნორმალურ განაწილების კანონს.
შესრულების ნიმუში მოცემულია მიმაგრებულ ფაილში BasicsPC1-Student.xls.
გაიდლაინები
დავუშვათ, რომ ჩვენ გავაკეთეთ 10 ექსპერიმენტის სერია, რომელიც გავზომეთ X მნიშვნელობა. ცხრილი 1. "ექსპერიმენტის დამუშავების" ფურცლის სავარაუდო ხედი.
D და E სვეტების ჩანაწერები არის მინიშნებები, რომლებიც დაგეხმარებათ გაარკვიოთ რა მახასიათებლები გამოვთვალოთ. სვეტი F ჯერ ცარიელი უნდა იყოს, მასში განთავსდება ჩვენი ფორმულები.
დავიწყოთ შედეგების დამუშავება ცდების რაოდენობის გამოთვლით n.
მნიშვნელობების რაოდენობის დასადგენად გამოიყენება სპეციალური ფუნქცია სახელწოდებით COUNT. ფუნქციების მქონე ფორმულის შესაყვანად გამოიყენეთ Function Wizard, რომელიც გაშვებულია ბრძანებით "Insert Function" მენიუს "Insert" - "Function" ან ღილაკზე ინსტრუმენტთა პანელზე ეტიკეტირებული f x.
დავაწკაპუნოთ უჯრედზე F6, სადაც შედეგი უნდა იყოს და დავიწყოთ Function Wizard.
სამუშაოს პირველი ნაბიჯი (სურათი 1) ემსახურება სასურველი ფუნქციის არჩევას.
ექსპერიმენტული მონაცემების დასამუშავებლად გამოიყენება სტატისტიკური ფუნქციები. ამიტომ, პირველ რიგში, კატეგორიების სიაში აირჩიეთ კატეგორია " სტატისტიკური". მეორე ფანჯარაში ჩნდება სტატისტიკური ფუნქციების სია.
ფუნქციების სია დალაგებულია ანბანურად, რაც აადვილებს ჩვენთვის საჭირო COUNT ფუნქციის პოვნას („ითვლის რიცხვების რაოდენობას არგუმენტების სიაში“).
თუ ეს ფუნქცია მონიშნულია, დააჭირეთ ღილაკს Ok და გადადით მე-2 ნაბიჯზე.
მეორე ნაბიჯი (სურათი 2) გამოიყენება ფუნქციის არგუმენტების დასაყენებლად.
COUNT ფუნქციამ უნდა განსაზღვროს რომელი რიცხვები უნდა დათვალოს, ან რომელ უჯრედებშია განთავსებული ეს რიცხვები. ექსპერიმენტების სერიის დამუშავების შემდეგი ორი ეტაპი ანალოგიურად ტარდება.
უჯრედში F7, AVERAGE ფუნქციის გამოყენებით, ნიმუშის საშუალო მნიშვნელობა გამოითვლება, F8 უჯრედში ნიმუშის სტანდარტული გადახრა STDEV ფუნქციის გამოყენებით. .
უჯრედების იგივე დიაპაზონი ემსახურება ამ ფუნქციების არგუმენტებს.
ნდობის ინტერვალის გამოსათვლელად აუცილებელია სტუდენტის კოეფიციენტის განსაზღვრა. ეს დამოკიდებულია შეცდომის ალბათობაზე (ზოგადად მოცემული სანდოობით 95%, შეცდომის ალბათობა არის 5%) და თავისუფლების ხარისხების რაოდენობაზე n-1).
სტუდენტის კოეფიციენტის საპოვნელად გამოიყენება Excel-ის სტატისტიკური ფუნქცია STUDISTRIP („სტუდენტის განაწილების შებრუნებული“). ამ ფუნქციის თავისებურება ის არის, რომ პირველი არგუმენტი, რიცხვი 5% (ან 0.05) შეყვანილია შესაბამის ფანჯარაში კლავიატურიდან. მეორესთვის, მიუთითეთ იმ უჯრედის მისამართი, სადაც მდებარეობს n მნიშვნელობა, შემდეგ დაამატეთ "-1" ფანჯარაში. ჩვენ ვიღებთ ჩანაწერს "F6-1".
ნდობის ინტერვალის საპოვნელად გამოიყენება ჩვეულებრივი გამრავლების ფორმულა. რა თქმა უნდა, ასოების ნაცვლად უნდა იყოს უჯრედების მისამართები, სადაც არის სტუდენტის კოეფიციენტი და საშუალოს სტანდარტული გადახრა. როგორც წესი, ნდობის ინტერვალის მნიშვნელობა მრგვალდება ერთ მნიშვნელოვან ფიგურამდე, გარემოს იგივე რიგი უნდა იყოს საშუალოზე. აქედან გამომდინარე, საბოლოო შედეგი შეიძლება დაიწეროს შემდეგნაირად: 95% საიმედოობით, X = 14,80 ± 0,05. დასასრულს, ჩვენ ვიანგარიშებთ ფარდობით შეცდომას X: = CI / X cf-ის განსაზღვრისას (ფორმულა: „=F11/F7“). ფარდობითი შეცდომის მნიშვნელობა ჩვეულებრივ გამოხატულია პროცენტულად, გვაქვს 0.3%.
2 და 3 ამოცანები შესრულებულია "ანალიზის პაკეტის" დანამატის გამოყენებით (მენიუდან Tools . Data Analysis Histogram).
დანამატის დასაინსტალირებლად გამოიძახეთ მენიუ Tools Add-ons და ინსტალაციისთვის ხელმისაწვდომი დანამატების შემოთავაზებული სიიდან აირჩიეთ „ანალიზის პაკეტი“ (იხ. დანამატების ინსტალაცია
Excel კომპიუტერზე.doc).
