გაფართოებული ფილტრი და რაღაც ჯადოსნური

Excel-ის მომხმარებელთა აბსოლუტური უმრავლესობისთვის, როდესაც სიტყვა „მონაცემთა ფილტრაცია“ ჩნდება თავში, მხოლოდ ჩვეულებრივი კლასიკური ფილტრი ჩანართიდან. მონაცემები - ფილტრი (მონაცემები - ფილტრი):

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

ბაზისბანკი

დასაწყებად, ჩადეთ რამდენიმე ცარიელი სტრიქონი თქვენი მონაცემთა ცხრილის ზემოთ და დააკოპირეთ ცხრილის სათაური იქ – ეს იქნება პირობებით დიაპაზონი (მონიშნულია ყვითლად სიცხადისთვის):

ყვითელ უჯრედებსა და თავდაპირველ ცხრილს შორის უნდა იყოს მინიმუმ ერთი ცარიელი ხაზი.

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

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

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

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

მაკროს დამატება

”კარგი, სად არის აქ კომფორტი?” იკითხე და მართალი იქნები. საჭიროა არა მხოლოდ ხელებით შეიყვანოთ პირობები ყვითელ უჯრედებში, არამედ გახსნათ დიალოგური ფანჯარა, შეიყვანოთ დიაპაზონები იქ, დააჭიროთ OK. სამწუხაროა, გეთანხმები! მაგრამ "ყველაფერი იცვლება, როდესაც ისინი მოდიან ©" - მაკროები!

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

პირადი ქვესამუშაო ფურცელი_Change(ByVal სამიზნე როგორც დიაპაზონი) თუ არ იკვეთება(Target, Range("A2:I5")) არაფერია, შემდეგ შეცდომით განაახლეთ შემდეგი ActiveSheet.ShowAllData Range("A7").CurrentRegion.AdvancedFilter Actionterriri,Claceil := დიაპაზონი ("A1"). CurrentRegion End If End Sub  

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

ასე რომ, ყველაფერი ბევრად უკეთესია, არა? 🙂

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

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

დახვეწილი პუნქტები:

• * ნიშანი ნიშნავს ნებისმიერი სიმბოლოს ნებისმიერ რაოდენობას და ? - რომელიმე პერსონაჟი.
• ტექსტისა და რიცხვითი მოთხოვნების დამუშავების ლოგიკა ოდნავ განსხვავებულია. მაგალითად, პირობის უჯრედი ნომრით 5 არ ნიშნავს ხუთით დაწყებული ყველა რიცხვის ძიებას, მაგრამ B ასოს მქონე პირობის უჯრა უდრის B*, ანუ მოძებნის ნებისმიერ ტექსტს, რომელიც იწყება ასო B-ით.
• თუ ტექსტის მოთხოვნა არ იწყება = ნიშნით, მაშინ შეგიძლიათ გონებრივად ჩადოთ * ბოლოს.
• თარიღები უნდა შეიყვანოთ აშშ-ს ფორმატში თვე-დღე-წელი და წილადის მეშვეობით (თუნდაც გაქვთ Excel და რეგიონალური პარამეტრები).

ლოგიკური კავშირები AND-OR

სხვადასხვა უჯრედებში ჩაწერილი პირობები, მაგრამ ერთსა და იმავე ხაზში, ითვლება ურთიერთდაკავშირებულად ლოგიკური ოპერატორის მიერ И (და):

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

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

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

ზოგადად, "ფაილის დასრულების" შემდეგ, მოწინავე ფილტრი გამოდის საკმაოდ ღირსეული ინსტრუმენტი, ზოგან არა უარესი, ვიდრე კლასიკური ავტოფილტრი.

• სუპერფილტრი მაკროებზე
• რა არის მაკროები, სად და როგორ ჩავსვათ მაკრო კოდი Visual Basic-ში
• ჭკვიანი ცხრილები Microsoft Excel-ში