ჰორიზონტალური სვეტის ფილტრაცია Excel-ში

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

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

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

მეთოდი 1. ახალი FILTER ფუნქცია

თუ თქვენ ხართ Excel 2021-ის ახალ ვერსიაზე ან Excel 365-ის გამოწერაზე, შეგიძლიათ ისარგებლოთ ახლად დანერგილი ფუნქციით ფილტრი (ფილტრი), რომელსაც შეუძლია წყაროს მონაცემების გაფილტვრა არა მხოლოდ რიგების, არამედ სვეტების მიხედვით. ამ ფუნქციის მუშაობისთვის საჭიროა დამხმარე ჰორიზონტალური ერთგანზომილებიანი მასივი, სადაც თითოეული მნიშვნელობა (TRUE ან FALSE) განსაზღვრავს, ვაჩვენოთ თუ, პირიქით, დავმალოთ შემდეგი სვეტი ცხრილში.

მოდით დავამატოთ შემდეგი ხაზი ჩვენი ცხრილის ზემოთ და ჩავწეროთ მასში თითოეული სვეტის სტატუსი:

• ვთქვათ, ჩვენ ყოველთვის გვინდა პირველი და ბოლო სვეტების (სათაურები და ჯამები) ჩვენება, ამიტომ მათთვის მასივის პირველ და ბოლო უჯრედებში ვაყენებთ მნიშვნელობას = TRUE.
• დარჩენილი სვეტებისთვის, შესაბამისი უჯრედების შიგთავსი იქნება ფორმულა, რომელიც ამოწმებს ჩვენთვის საჭირო მდგომარეობას ფუნქციების გამოყენებით. И (და) or OR (OR). მაგალითად, რომ ჯამი არის 300-დან 500-მდე.

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

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

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

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

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

თუ ყველა ეს პირობა დაკმაყოფილებულია, მაშინ იმისათვის, რომ ავაშენოთ კრებსითი ცხრილი, რომელიც ჰგავს ჩვენს თავდაპირველ ცხრილს, ის (ორიგინალი) უნდა გაფართოვდეს ჯვარედინი ჩანართიდან ბრტყელში (ნორმალიზება). და ამის გაკეთების უმარტივესი გზაა Power Query-ის დანამატი, მონაცემთა ტრანსფორმაციის ძლიერი ინსტრუმენტი, რომელიც ჩაშენებულია Excel-ში 2016 წლიდან. 

ესენია:

1. გადავიყვანოთ ცხრილი „ჭკვიან“ დინამიურ ბრძანებად მთავარი – ფორმატირება როგორც ცხრილი (მთავარი - ფორმატირება როგორც ცხრილი).
2. იტვირთება Power Query-ში ბრძანებით მონაცემები – ცხრილიდან / დიაპაზონიდან (მონაცემები – ცხრილიდან / დიაპაზონი).
3. ჩვენ ვფილტრავთ ხაზს ჯამებით (შეჯამებას ექნება თავისი ჯამები).
4. დააწკაპუნეთ მაუსის მარჯვენა ღილაკით პირველი სვეტის სათაურზე და აირჩიეთ სხვა სვეტების ჩაკეცვა (გააუქმეთ სხვა სვეტები). ყველა არჩეული სვეტი გარდაიქმნება ორად - თანამშრომლის სახელი და მისი ინდიკატორის მნიშვნელობა.
5. სვეტის გაფილტვრა ჯამებით, რომლებიც შევიდა სვეტში ატრიბუტი.
6. მიღებული ბრტყელი (ნორმალიზებული) ცხრილის მიხედვით ბრძანებით ვაშენებთ საყრდენ ცხრილს მთავარი — დახურვა და ჩატვირთვა — დახურვა და ჩატვირთვა… (მთავარი — დახურვა და ჩატვირთვა — დახურვა და ჩატვირთვა…).

ახლა თქვენ შეგიძლიათ გამოიყენოთ კრებსითი ცხრილებში არსებული სვეტების გაფილტვრის შესაძლებლობა - სახელებისა და ერთეულების წინ ჩვეულებრივი საკონტროლო ნიშნები ხელმოწერის ფილტრები (ლეიბლის ფილტრები) or ფილტრები მნიშვნელობის მიხედვით (მნიშვნელობის ფილტრები):

და, რა თქმა უნდა, მონაცემების შეცვლისას, თქვენ უნდა განაახლოთ ჩვენი მოთხოვნა და რეზიუმე კლავიატურის მალსახმობით Ctrl+Alt+F5 ან გუნდი მონაცემები - განაახლეთ ყველა (მონაცემები — ყველას განახლება).

მეთოდი 3. მაკრო VBA-ში

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

დავუშვათ, ჩვენ გვინდა გავფილტროთ სვეტები ფრენის დროს, სადაც მენეჯერის სახელი ცხრილის სათაურში აკმაყოფილებს A4 ყვითელ უჯრედში მითითებულ ნიღაბს, მაგალითად, იწყება ასო "A" (ანუ მიიღეთ "Anna" და "Arthur". " როგორც შედეგი). 

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

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

Private Sub Worksheet_Change (ByVal Target As Range) If Target.Address = "$A$4" მაშინ დიაპაზონის თითოეული უჯრედისთვის ("D2:O2") თუ cell = True მაშინ cell.EntireColumn.Hidden = False Else cell.EntireColumn.Hidden = True End If Next cell End If End Sub  

მისი ლოგიკა ასეთია:

• ზოგადად, ეს არის მოვლენის დამმუშავებელი სამუშაო ფურცელი_შეცვლა, ანუ ეს მაკრო ავტომატურად იმუშავებს მიმდინარე ფურცელზე ნებისმიერი უჯრედის ნებისმიერ ცვლილებაზე.
• შეცვლილი უჯრედის მითითება ყოველთვის იქნება ცვლადში სამიზნე.
• პირველ რიგში, ჩვენ ვამოწმებთ, რომ მომხმარებელმა ზუსტად შეცვალა უჯრედი კრიტერიუმით (A4) - ამას აკეთებს ოპერატორი. if.
• შემდეგ ციკლი იწყება თითოეული… ნაცრისფერ უჯრედებზე (D2:O2) გამეორება TRUE / FALSE ინდიკატორის მნიშვნელობებით თითოეული სვეტისთვის.
• თუ შემდეგი ნაცრისფერი უჯრედის მნიშვნელობა არის TRUE (true), მაშინ სვეტი არ არის დამალული, წინააღმდეგ შემთხვევაში ჩვენ ვმალავთ მას (თვისება დამალული).

•  დინამიური მასივის ფუნქციები Office 365-დან: FILTER, SORT და UNIC
• კრებსითი ცხრილი მრავალხაზოვანი სათაურით Power Query-ის გამოყენებით
• რა არის მაკროები, როგორ შევქმნათ და გამოიყენოთ ისინი