Excel ცხრილებთან მუშაობისას ხშირად საჭიროა გარკვეული კრიტერიუმის მიხედვით ან რამდენიმე პირობით შერჩევა. პროგრამას შეუძლია ამის გაკეთება სხვადასხვა საშუალებებით. მოდით გაერკვნენ, თუ როგორ უნდა შეფასდეს Excel- ის სხვადასხვა ვარიანტების გამოყენება.
შერჩევა
მონაცემთა შერჩევა შედგება შერჩევის პროცედურაში იმ შედეგების ზოგადი მასალისაგან, რომელიც დააკმაყოფილებს განსაზღვრულ პირობებს, მათ შემდგომ გამოცემას ცალკეულ სიაში ან საწყისი დიაპაზონში.
მეთოდი 1: გამოიყენეთ გაფართოებული ავტოფილტრი
მარტივი გზა, რათა შერჩევა გამოიყენოს მოწინავე autofilter. განვიხილოთ როგორ გავაკეთოთ ეს კონკრეტული მაგალითით.
- შეარჩიეთ ტერიტორია ფურცელზე, მონაცემებში, რომელთა შერჩევა გსურთ. ჩანართში "მთავარი" დააჭირეთ ღილაკს "სორტირება და ფილტრი". იგი მოთავსებულია პარამეტრების ბლოკში. რედაქტირება. სიის შემდეგ, რომელიც იხსნება ამის შემდეგ, დააჭირეთ ღილაკს. "ფილტრი".
განსხვავებულად გაკეთება შესაძლებელია. ამისათვის, ფურცლის არეალის შერჩევის შემდეგ, გადატანა tab- ზე "მონაცემები". დააჭირეთ ღილაკს "ფილტრი"რომელიც განთავსებულია ჯგუფის ფირზე "სორტირება და ფილტრი".
- ამ მოქმედების შემდეგ, ხატები გამოჩნდება მაგიდაზე, რათა დაიწყოს ფილტრაცია სახით პატარა სამკუთხედების აღმოჩნდა თავდაყირა მარჯვენა კიდეზე საკნები. დააჭირეთ ამ ხატულას სვეტის სათაურში, რომელზეც ჩვენ გვინდა, რომ შევარჩიოთ. საწყისი მენიუში, დააწკაპუნეთ პუნქტზე "ტექსტური ფილტრები". შემდეგი, აირჩიეთ პოზიცია "საბაჟო ფილტრი ...".
- საბაჟო ფილტრაციის ფანჯარა გააქტიურებულია. შესაძლებელია დაწესდეს ლიმიტი, რომელზეც შერჩევა მოხდება. ჩამოსაშლელი სიისთვის, რომელიც შეიცავს სვეტის ნომრების ფორმატის საკნების, რომელიც ჩვენ ვიყენებთ მაგალითს, შეგიძლიათ აირჩიოთ ერთი ხუთი ტიპის პირობები:
- უდრის;
- არ არის თანაბარი;
- მეტი;
- უფრო დიდი ან თანაბარი;
- ნაკლებად
მოდით განვსაზღვროთ მდგომარეობა როგორც მაგალითი, რომ ჩვენ შეგვიძლია მხოლოდ აირჩიეთ ღირებულებები, რისთვისაც შემოსავლების მოცულობა აღემატება 10,000 რუბლს. დააყენეთ პოზიცია "მეტი". შეიყვანეთ მნიშვნელობა მარჯვენა ზღვარზე "10000". აქციის შესასრულებლად დააჭირეთ ღილაკს. "OK".
- როგორც ხედავთ, ფილტრაციის შემდეგ არსებობს მხოლოდ ხაზები, სადაც შემოსავლების მოცულობა აღემატება 10,000 რუბლს.
- მაგრამ იმავე სვეტში შეგვიძლია დავამატოთ მეორე მდგომარეობა. ამისათვის დაბრუნდით საბაჟო ფილტრის ფანჯარაში. როგორც ხედავთ, ქვედა ნაწილში არსებობს სხვა მდგომარეობის შეცვლა და შესაბამისი შეყვანის ველი. მოდით ახლა დააყენოთ ზედა შერჩევის ლიმიტი 15,000 რუბლი. ამისათვის დააყენეთ პოზიციის შეცვლა "ნაკლები", ხოლო მინდორზე მარჯვნივ შევაფასებთ მნიშვნელობას "15000".
გარდა ამისა, არსებობს შეცვლა პირობები. მას ორი პოზიცია აქვს "და" და "ან". ჩვეულებრივ, იგი პირველ ადგილზეა. ეს იმას ნიშნავს, რომ მხოლოდ ხაზები, რომლებიც აკმაყოფილებენ ორივე შეზღუდვებს დარჩება შერჩევაში. თუ იგი თანამდებობაზეა "ან", მაშინ იქნება ღირებულებები, რომლებიც შეესაბამება ორ პირობას. ჩვენს შემთხვევაში, თქვენ უნდა დააყენოთ შეცვლა "და", ანუ, დატოვეთ ეს ნაგულისხმევი პარამეტრი. ყველა ღირებულების შეყვანის შემდეგ დააჭირეთ ღილაკს. "OK".
- ახლა მაგიდას აქვს მხოლოდ ხაზები, რომელშიც შემოსავლების რაოდენობა არანაკლებ 10,000 რუბლია, მაგრამ არ აღემატება 15,000 რუბლს.
- ანალოგიურად, კონფიგურაციის ფილტრები სხვა სვეტში. ამავდროულად, ასევე შესაძლებელია გაფილტვრის შენახვა წინა პირობებით, რომლებიც სვეტში მითითებულია. ასე რომ, ვნახოთ, როგორ შერჩეულია ფილტრის გამოყენებით ფილტრის გამოყენებით ფორმატი. დაწკაპეთ ფილტრის ხატულა შესაბამის სვეტში. სიაში მონიშნეთ ელემენტი. "თარიღის ფილტრი" და "საბაჟო ფილტრი".
- საბაჟო autofilter ფანჯარა იწყება. შეასრულეთ შედეგების შერჩევა ცხრილში 4 დან 6 მაისის ჩათვლით. იმ შემთხვევაში, თუ თქვენ ხედავთ, კიდევ უფრო მეტი ვარიანტია, ვიდრე ნომრის ფორმატისთვის. აირჩიეთ პოზიცია "შემდეგ ან თანაბარი". სფეროში მარჯვენა, მითითებული ღირებულება "04.05.2016". ქვედა ბლოკში, დააყენეთ პოზიცია "ან ტოლია". შეიყვანეთ მნიშვნელობა მარჯვენა სფეროში "06.05.2016". პირობითი თავსებადობის შეცვლა დარჩა ნაგულისხმევი პოზიციაზე - "და". იმისათვის, რომ გამოიყენოთ ფილტრაცია მოქმედებაში, დააჭირეთ ღილაკს "OK".
- როგორც ხედავთ, ჩვენი სია კიდევ უფრო მცირდება. ახლა მხოლოდ ის დარჩა ხაზები, რომელშიც შემოსავლების ოდენობა მერყეობს 10,000-დან 15,000 რუბლს 04.05-დან 06.05.2016 წლამდე.
- ჩვენ შეგვიძლია აღადგინოთ ფილტრაცია ერთ-ერთ სვეტში. ამის გაკეთება შემოსავლების ღირებულებებისთვის. დაწკაპეთ autofilter ხატი შესაბამის სვეტში. ჩამოსაშლელ სიაში დააჭირეთ პუნქტს. "ფილტრის წაშლა".
- როგორც მოგეხსენებათ, ამ ქმედებების შემდეგ, ნიმუშის ოდენობით ნიმუშის გამორთვა მოხდება და თარიღების მიხედვით შერჩევა მხოლოდ დარჩება (04.05.2016 - 06.05.2016).
- ამ მაგიდასთან აქვს კიდევ ერთი სვეტი - "სახელი". იგი შეიცავს მონაცემებს ტექსტურ ფორმატში. ვნახოთ, თუ როგორ უნდა შეიქმნას ნიმუში ფილტრაციის გამოყენებით ამ ფასეულობებით.
დააჭირეთ ფილტრის ხატულა სვეტის სახელში. სიის მიხედვით "ტექსტური ფილტრები" და "საბაჟო ფილტრი ...".
- მომხმარებლის autofilter window ხელახლა ხსნის. მოდით გავაკეთოთ ნიმუში სახელით. "კარტოფილი" და "ხორცი". პირველ ბლოკში, მდგომარეობა გადართულია "თანაბარი". სფეროში უფლება მას შევა სიტყვა "კარტოფილი". ასევე ქვედა ბლოკის შეცვლა პოზიციაზე "თანაბარი". სფეროში საპირისპირო მას ჩვენ შესვლის - "ხორცი". და შემდეგ ჩვენ გავაკეთეთ ის, რაც ჩვენ არ გავაკეთეთ ადრე: ჩვენ მითითებული თავსებადობა შეცვლა პოზიცია "ან". ეკრანზე გამოჩნდება ხაზი, რომელიც შეიცავს ნებისმიერ პირობებს. დააჭირეთ ღილაკს "OK".
- როგორც ხედავთ, ახალ ნიმუშში არის შეზღუდვები თარიღიდან (04/05/2016 დან 05/06/2016) და სახელით (კარტოფილი და ხორცი). შემოსავლების ოდენობა არ არსებობს.
- თქვენ შეგიძლიათ მთლიანად წაშალოთ ფილტრი იმავე მეთოდის გამოყენებით, რომლითაც გამოიყენეთ იგი. და არა აქვს მნიშვნელობა რა მეთოდი გამოიყენებოდა. ფილტრის გადასალახად, tab- ში ყოფნა "მონაცემები" დააჭირეთ ღილაკს "ფილტრი"რომელიც მასპინძლობს ჯგუფში "სორტირება და ფილტრი".
მეორე ვარიანტი მოიცავს ჩანართის შეცვლას "მთავარი". აქ ჩვენ შეასრულებთ ღილაკს ლენტი ღილაკს. "სორტირება და ფილტრი" ბლოკში რედაქტირება. აქტიურ სიაში დააჭირეთ ღილაკს. "ფილტრი".
ზემოთ აღნიშნული ორი მეთოდის გამოყენებისას ფილტრაცია ამოიღება და ნიმუშის შედეგები გაწმენდილი იქნება. ანუ, ცხრილში გამოჩნდება მთელი მასივი მონაცემები, რომელსაც აქვს.
გაკვეთილი: ავტო ფილტრის ფუნქცია Excel- ში
მეთოდი 2: გამოიყენე Array ფორმულა
თქვენ ასევე შეგიძლიათ გააკეთოთ შერჩევა კომპლექსური მასივის ფორმულის გამოყენებით. წინა ვერსიისგან განსხვავებით, ეს მეთოდი უზრუნველყოფს ცალკე ცხრილის შედეგს.
- იმავე ფურცელზე შეიქმნება ცარიელი მაგიდა იმავე სვეტების სახელწოდების სათაურში, როგორც კოდის სახით.
- აირჩიეთ ახალი ცხრილის პირველი სვეტის ყველა ცარიელი საკანი. შექმენით კურსორი ფორმულაში. მხოლოდ აქ ფორმულა შევა, შერჩევისას მითითებული კრიტერიუმების მიხედვით. ჩვენ შეარჩიეთ ხაზები, შემოსავლის ოდენობა, რომელიც აჭარბებს 15,000 რუბლს. ჩვენს კონკრეტულ მაგალითში, შეყვანილი ფორმულა გამოიყურება ასე:
= (INDEX (A2: A29; LOWEST (IF (15000 <= C2: C29; STRING (C2: C29); ""); STRING () - STRING ($ C $ 1)) - STRING ($ C $ 1))ბუნებრივია, თითოეულ შემთხვევაში უჯრედისა და მერყეობის მისამართი განსხვავდება. ამ მაგალითში შეგიძლიათ შეადაროთ ფორმულა კოორდინატებთან ერთად ილუსტრაციაში და მოერგოს თქვენს მოთხოვნებს.
- რადგან ეს არის მასივი ფორმულა, იმისათვის, რომ გამოიყენოთ ეს ქმედება, თქვენ უნდა დააჭიროთ არა ღილაკს შეიყვანეთდა კლავიატურის კომბინაცია Ctrl + Shift + Enter. ჩვენ ამას ვაკეთებთ.
- მეორე სვეტის შერჩევის თარიღები და ფორმულა ბარიში კურსორის დაყენება, შეიყვანეთ შემდეგი გამოხატვა:
(B2: B29; LOWEST (IF (15000 <= C2: C29; STRING (C2: C29); ""); STRING () - STRING ($ C $ 1)) - STRING ($ C $ 1))დააჭირეთ კლავიატურის კომბინაციას Ctrl + Shift + Enter.
- ანალოგიურად, შემოსავალთან ერთად სვეტში შედის შემდეგი ფორმულა:
(C2: C29; LOWEST (IF (15000 <= C2: C29; STRING (C2: C29); ""); STRING () - STRING ($ C $ 1)) - STRING ($ C $ 1))კიდევ ერთხელ, ჩვენ ვხსნით კომბინაციას Ctrl + Shift + Enter.
სამივე შემთხვევაში, მხოლოდ კოორდინატების ცვლილებებია, ხოლო დანარჩენი ფორმულები სრულიად იდენტურია.
- როგორც ხედავთ, მაგიდასთან ივსება მონაცემები, მაგრამ მისი გამოჩენა არ არის მიმზიდველი, გარდა ამისა, თარიღის ღირებულებები შევსებულია არასწორად. აუცილებელია ამ ხარვეზების გამოსწორება. არასწორი თარიღი იმის გამო, რომ შესაბამისი სვეტების ფორმატის ფორმატი საერთოა, ჩვენ უნდა გამოვყოთ თარიღის ფორმატი. აირჩიეთ მთელი სვეტი, მათ შორის უჯრედების შეცდომები და დააკლიკეთ შერჩევა მაუსის მარჯვენა ღილაკით. სიაში, რომელიც გამოჩნდება პუნქტში "მობილური ფორმატი ...".
- ფორმატირების ფანჯარაში, რომელიც იხსნება, გახსენით ჩანართი "ნომერი". ბლოკში "ხმების ფორმატები" აირჩიეთ მნიშვნელობა "თარიღი". ფანჯრის მარჯვენა ნაწილში შეგიძლიათ აირჩიოთ სასურველი ტიპის თარიღის ჩვენება. პარამეტრების დაყენების შემდეგ დააჭირეთ ღილაკს. "OK".
- ახლა თარიღი სწორად არის ნაჩვენები. მაგრამ, როგორც ხედავთ, მაგიდის მთლიანი ქვედა ივსება უჯრედები, რომლებიც შეიცავს არასწორი ღირებულებას. "# NUM!". სინამდვილეში ესენი არიან საკნები, რომლებსაც არ გააჩნიათ საკმარისი მონაცემები ნიმუშიდან. უფრო მიმზიდველი იქნებოდა, თუ ისინი ცარიელი იყო ყველა ცარიელი. ამ მიზნით, ჩვენ ვიყენებთ პირობითი ფორმატირებას. აირჩიეთ ყველა უჯრა მაგიდაზე, გარდა თავით. ყოფნა tab "მთავარი" დააჭირეთ ღილაკს "პირობითი ფორმატირება"რომელიც ბლოკის ინსტრუმენტები "სტილები". სიაში შეარჩიეთ ელემენტი "შექმენით წესი ...".
- ფანჯარაში, რომელიც იხსნება, აირჩიეთ წესი წესი "შეიტანეთ მხოლოდ საკნები, რომლებიც შეიცავს". პირველ რიგში წარწერა "ფორმის მხოლოდ უჯრედები, რომელთათვისაც ხდება შემდეგი პირობა" აირჩიე პოზიცია "შეცდომები". შემდეგ დააჭირეთ ღილაკს "ფორმატი ...".
- ფორმატირების ფანჯარაში, რომელიც იხსნება, გადადით tab- ზე "შრიფტი" და შეარჩიეთ თეთრი ფერის შესაბამისი ველი. ამის შემდეგ დააჭირეთ ღილაკს. "OK".
- დაწკაპეთ ღილაკს ზუსტად იგივე სახელით კონდიცირების ფანჯარაში დაბრუნების შემდეგ.
ახლა მზა ნიმუში გვაქვს ცალკე სათანადოდ მოწყობილი ცხრილისთვის განსაზღვრული შეზღუდვისთვის.
გაკვეთილი: პირობითი ფორმატირება Excel- ში
მეთოდი 3: ნიმუში რამდენიმე პირობით ფორმულით
ისევე, როგორც ფორმის გამოყენებით, ფილტრის გამოყენებით, შეგიძლიათ რამდენიმე ნიმუში. მაგალითად, მოდით მიიღოს ყველა იმავე წყარო მაგიდა, ისევე როგორც ცარიელი მაგიდა, სადაც შედეგები იქნება ნაჩვენები, უკვე შესრულებული რიცხვითი და პირობითი ფორმატირება. შეარჩიეთ პირველი ლიმიტი შერჩევის ქვედა ზღვარს 15,000 რუბლის შემოსავლისთვის და მეორე მდგომარეობა 20,000 რუბლის ზედა ზღვარია.
- ჩვენ ცალკე სვეტში შევა ნიმუშის სასაზღვრო პირობები.
- ისევე, როგორც წინა მეთოდით, შეცვალეთ ახალი მაგიდის ცარიელი სვეტები და შეიყვანეთ შესაბამისი სამი ფორმულები. პირველ სვეტში შეიყვანეთ შემდეგი გამოსახულება:
(C2: C29); STRING (C2: C29) - STRING ($ C $ 1)) - STRING ($ 2) - STRING ($ D $ 2 = C2: C29) C $ 1))მომდევნო სვეტში ზუსტად იგივე ფორმულები შევა, მხოლოდ კოორდინატების შეცვლა ოპერატორის სახელით. INDEX შესაბამისი სვეტებისათვის საჭიროა წინა მეთოდით ანალოგიით.
შესვლის შემდეგ ყოველთვის არ უნდა დაგვავიწყდეს, რომ შეიყვანოთ მალსახმობი გასაღებები Ctrl + Shift + Enter.
- წინა მეთოდის უპირატესობა ის არის, რომ თუ ჩვენ გვინდა შეცვალოს ნიმუში საზღვრები, მაშინ ჩვენ არ უნდა შეიცვალოს მასივის ფორმულა, რომელიც თავისთავად საკმაოდ პრობლემატურია. საკმარისია შეიცვალოს სასაზღვრო რიცხვები ფურცელზე არსებული პირობების სვეტში, ვისაც მომხმარებელი სჭირდება. შერჩევის შედეგები ავტომატურად შეიცვლება ავტომატურად.
მეთოდი 4: შემთხვევითი შერჩევა
Excel- ში სპეციალური ფორმულით SLCIS შემთხვევითი შერჩევა შეიძლება გამოყენებულ იქნას. აუცილებელია გარკვეული შემთხვევებისთვის, როდესაც მუშაობენ დიდი რაოდენობით მონაცემები, როდესაც თქვენ უნდა წარმოადგინოთ ზოგადი სურათის გარეშე ყოვლისმომცველი ანალიზი ყველა მასივი მონაცემები.
- მაგიდის მარცხნივ, გამოტოვეთ ერთი სვეტი. შემდეგი სვეტის უჯრედზე, რომელიც პირველ რიგში უჯრედის მონაცემებით საპირისპიროა, შეიტანეთ ფორმულა:
= RAND ()ეს ფუნქცია აჩვენებს შემთხვევითი რიცხვით. იმისათვის, რომ გაააქტიუროთ, დააჭირეთ ღილაკს შეიტანეთ.
- შემთხვევითი რიცხვების მთლიანი სვეტის შესაქმნელად, კურსორი დააყენეთ საკანში ქვედა მარჯვენა კუთხეში, რომელიც უკვე შეიცავს ფორმულას. შევსების მარკერი გამოჩნდება. გაიგეთ ის ქვემოთ მაუსის მარცხენა ღილაკზე დაჭერით პარალელურად მაგიდასთან მონაცემებით.
- ახლა გვაქვს რიგი საკნები ივსება შემთხვევითი რიცხვები. მაგრამ ის შეიცავს ფორმულას SLCIS. ჩვენ უნდა ვიმუშაოთ სუფთა ფასეულობებით. ამისათვის კოპირება ცარიელი სვეტის მარჯვნივ. აირჩიეთ რიგი უჯრედების შემთხვევითი რიცხვები. განთავსდა ჩანართი "მთავარი"დაწკაპეთ ხატი "ასლი" ფირზე.
- აირჩიეთ ცარიელი სვეტი და დაწკაპეთ მაუსის მარჯვენა ღილაკით, კონტექსტური მენიუს ჩამოყევით. ჯგუფურ ინსტრუმენტებში "ჩასმა პარამეტრები" აირჩიე პუნქტი "ღირებულებები"გამოსახულია პიქტოგრამის სახით ნომრები.
- ამის შემდეგ, ყოფნა tab "მთავარი"დაწკაპეთ უკვე ნაცნობი ხატი "სორტირება და ფილტრი". ჩამოსაშლელ სიაში შეაჩერე შერჩევის პუნქტი "საბაჟო დალაგება".
- დახარისხება პარამეტრების ფანჯარა გააქტიურებულია. დარწმუნდით, რომ შეამოწმეთ ყუთი პარამეტრის შემდეგ. "ჩემი მონაცემები შეიცავს სათაურებს"თუ არსებობს ქუდი, მაგრამ არ არის checkmark. სფეროში "დალაგება" განსაზღვროს სვეტის სახელი, რომელიც შეიცავს შემთხვევითი რიცხვების კოპირებულ ღირებულებებს. სფეროში "სორტირება" დატოვეთ ნაგულისხმევი პარამეტრები. სფეროში "ორდენი" შეგიძლიათ აირჩიოთ ვარიანტი "აღმავალი"და ასე შემდეგ "კლებადი". შემთხვევითი ნიმუშისთვის, ეს არ არის მნიშვნელოვანი. პარამეტრების გაკეთების შემდეგ დააჭირეთ ღილაკს. "OK".
- ამის შემდეგ, მაგიდის ყველა მნიშვნელობა იმართება შემთხვევითი რიცხვების აღმავალი ან დაღმავალი მიზნით. თქვენ შეგიძლიათ მიიღოთ ნებისმიერი პირველი ხაზი ცხრილისაგან (5, 10, 12, 15 და ა.შ.) და ისინი შეიძლება ჩაითვალოს შემთხვევითი ნიმუშის შედეგზე.
გაკვეთილი: დალაგების და ფილტრის მონაცემების Excel
როგორც ხედავთ, Excel- ის ნიმუში შეიძლება გაკეთდეს, როგორც ავტომატური ფილტრის დახმარებით და სპეციალური ფორმულების გამოყენებით. პირველ შემთხვევაში, შედეგი იქნება ნაჩვენები ორიგინალური მაგიდა, ხოლო მეორე - ცალკეულ ტერიტორიაზე. არსებობს შესაძლებლობა, გააკეთოს არჩევანი, როგორც ერთი პირობით, და რამდენიმე. გარდა ამისა, თქვენ შეგიძლიათ შეასრულოთ შემთხვევითი შერჩევა ფუნქციის გამოყენებით SLCIS.
