ცარიელი რიგების და სვეტების ამოღება მონაცემებში

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

მოდით შევხედოთ ამ პრობლემის გადაჭრის რამდენიმე გზას.

მეთოდი 1. ცარიელი უჯრედების ძიება

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

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

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

1. აირჩიეთ დიაპაზონი ქალაქებით (B2:B26)
2. დააჭირეთ კლავიშს F5 და შემდეგ დააჭირეთ მონიშნეთ (გადადით სპეციალურზე) ან აირჩიეთ ჩანართზე მთავარი — იპოვეთ და აირჩიეთ — აირჩიეთ უჯრედების ჯგუფი (მთავარი — იპოვეთ და აირჩიეთ — გადადით სპეციალურზე).
3. ფანჯარაში, რომელიც იხსნება, აირჩიეთ ვარიანტი ცარიელი უჯრედები (ბლანკები) და დააჭირეთ OK – ჩვენი ცხრილის პირველ სვეტში ყველა ცარიელი უჯრედი უნდა იყოს შერჩეული.
4. ახლა აირჩიეთ ჩანართზე მთავარი Command წაშლა - წაშალეთ რიგები ფურცლიდან (წაშლა — რიგების წაშლა) ან დააჭირეთ კლავიატურის მალსახმობს Ctrl+minus - და ჩვენი ამოცანა მოგვარებულია.

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

მეთოდი 2: მოძებნეთ ცარიელი რიგები

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

შეხედეთ შემდეგ ცხრილს, მაგალითად, ასეთი შემთხვევისთვის:

აქ მიდგომა ცოტა უფრო რთული იქნება:

1. შეიყვანეთ ფუნქცია A2 უჯრედში COUNT (COUNTA), რომელიც გამოთვლის შევსებული უჯრედების რაოდენობას რიგში მარჯვნივ და დააკოპირებს ამ ფორმულას მთელ ცხრილში:
2. აირჩიეთ უჯრედი A2 და ჩართეთ ფილტრი ბრძანებით მონაცემები - ფილტრი (მონაცემები - ფილტრი) ან კლავიატურის მალსახმობი Ctrl+გადაიტანოს+L.
3. გავფილტროთ ნულები გამოთვლილი სვეტით, ანუ ყველა მწკრივი, სადაც მონაცემები არ არის.
4. რჩება გაფილტრული ხაზების არჩევა და მათი წაშლა ბრძანებით მთავარი — წაშლა - წაშალეთ რიგები ფურცლიდან (მთავარი — წაშლა — რიგების წაშლა) ან კლავიატურის მალსახმობი Ctrl+minus.
5. ჩვენ ვთიშავთ ფილტრს და ვიღებთ ჩვენს მონაცემებს ცარიელი ხაზების გარეშე.

სამწუხაროდ, ამ ხრიკის გაკეთება სვეტებით აღარ შეიძლება – Excel-მა ჯერ არ ისწავლა სვეტების მიხედვით გაფილტვრა.

მეთოდი 3. მაკრო ფურცელზე ყველა ცარიელი მწკრივისა და სვეტის ამოსაღებად

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

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

   Sub DeleteEmpty() Dim r როგორც ხანგრძლივობით, rng როგორც დიაპაზონი 'удаляем пустые строки For r = 1 To ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count If Application.CountA(Rows(r)) = 0 მაშინ თუ rng არაფერია მაშინ დააყენეთ rng = რიგები(r) სხვა დააყენეთ rng = კავშირი(rng, მწკრივები(r)) ბოლოს თუ შემდეგი r თუ არა rng არაფერია მაშინ rng. წაშლა 'удаляем пустые столбцы დააყენეთ rng = არაფერი r = 1-ისთვის ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count If Application.CountA(Columns(r)) = 0 მაშინ თუ rng არაფერია, მაშინ დააყენეთ rng = სვეტები(r) სხვა დააყენეთ rng = კავშირი(rng, სვეტები( ს)) დასრულება თუ შემდეგი r თუ არა rng არაფერია მაშინ rng.წაშლა ბოლო ქვე  

დახურეთ რედაქტორი და დაბრუნდით Excel-ში. 

ახლა დააჭირეთ კომბინაციას Alt+F8 ან ღილაკი Macros tab დეველოპერი. ფანჯარაში, რომელიც იხსნება, ჩამოთვლის ყველა მაკროს, რომელიც ამჟამად ხელმისაწვდომია გასაშვებად, მათ შორის თქვენ მიერ ახლახან შექმნილ მაკროს. DeleteEmpty. აირჩიეთ ის და დააჭირეთ ღილაკს გასაშვებად (გაშვება) - ფურცელზე ყველა ცარიელი მწკრივი და სვეტი მყისიერად წაიშლება.

მეთოდი 4: Power Query

ჩვენი პრობლემის გადაჭრის კიდევ ერთი გზა და ძალიან გავრცელებული სცენარია Power Query-ში ცარიელი რიგებისა და სვეტების ამოღება.

პირველი, მოდით ჩავტვირთოთ ჩვენი ცხრილი Power Query Query Editor-ში. თქვენ შეგიძლიათ გადაიყვანოთ ის დინამიურ „ჭკვიანად“ კლავიატურის მალსახმობი Ctrl+T ან უბრალოდ შეარჩიოთ ჩვენი მონაცემთა დიაპაზონი და მიეცით სახელი (მაგ. თარიღი) ფორმულების ზოლში, გარდაიქმნება სახელად:

ახლა ჩვენ ვიყენებთ ბრძანებას Data – მიიღეთ მონაცემები – From ცხრილი / დიაპაზონი (მონაცემები – მიიღეთ მონაცემები – საწყისი ცხრილი / დიაპაზონი) და ვტვირთავთ ყველაფერს Power Query-ში:

მაშინ ყველაფერი მარტივია:

1. ცარიელ ხაზებს ვშლით ბრძანებით Home – Reduce lines – Delete lines – Delete ცარიელი ხაზები (Home – Remove Rows – Remove ცარიელი რიგები).
2. დააწკაპუნეთ მაუსის მარჯვენა ღილაკით პირველი ქალაქის სვეტის სათაურზე და კონტექსტური მენიუდან აირჩიეთ Unpivot Other Columns ბრძანება. ჩვენი მაგიდა იქნება, როგორც მას ტექნიკურად სწორად უწოდებენ, ნორმალიზებული – გარდაიქმნება სამ სვეტად: ქალაქი, თვე და ღირებულება ქალაქის კვეთიდან და თვე საწყისი ცხრილიდან. Power Query-ში ამ ოპერაციის თავისებურება ის არის, რომ ის გამოტოვებს ცარიელ უჯრედებს წყაროს მონაცემებში, რაც ჩვენ გვჭირდება:
3. ახლა ჩვენ ვასრულებთ საპირისპირო ოპერაციას - მივაბრუნებთ მიღებულ ცხრილს ორგანზომილებიანად, რათა დავაბრუნოთ იგი პირვანდელ ფორმაში. აირჩიეთ სვეტი თვეებით და ჩანართზე ტრანსფორმაციის აირჩიეთ გუნდი საყრდენი სვეტი (ტრანსფორმა — საყრდენი სვეტი). ფანჯარაში, რომელიც იხსნება, როგორც მნიშვნელობების სვეტი, აირჩიეთ ბოლო (Value), ხოლო გაფართოებულ ვარიანტებში - ოპერაცია. არ შეაგროვოთ (არ შეაგროვოთ):
4. რჩება შედეგის ატვირთვა Excel-ში ბრძანებით მთავარი — დახურვა და ჩატვირთვა — დახურვა და ჩატვირთვა… (მთავარი — დახურვა და ჩატვირთვა — დახურვა და ჩატვირთვა…)

• რა არის მაკრო, როგორ მუშაობს ის, სად დავაკოპიროთ მაკროს ტექსტი, როგორ გავუშვათ მაკრო?
• სიაში ყველა ცარიელი უჯრედის შევსება მშობელი უჯრედების მნიშვნელობებით
• ყველა ცარიელი უჯრედის ამოღება მოცემული დიაპაზონიდან
• სამუშაო ფურცლის ყველა ცარიელი მწკრივის წაშლა PLEX დანამატით