Өгөгдөл дэх хоосон мөр, багануудыг устгаж байна

Ихэнх тохиолдолд хоосон мөр, багана нь хүснэгтэд өвдөлт үүсгэдэг. Эрэмбэлэх, шүүх, нэгтгэн дүгнэх, пивот хүснэгт үүсгэх гэх мэт стандарт функцууд нь хоосон мөр, баганыг хүснэгтийн завсарлага гэж хүлээн зөвшөөрдөг бөгөөд тэдгээрийн цаана байгаа өгөгдлийг авахгүй. Хэрэв ийм олон цоорхой байгаа бол тэдгээрийг гараар арилгах нь маш их зардал шаарддаг бөгөөд шүүлтүүрийг ашиглан бүгдийг нэг дор "бөөнөөр нь" арилгах нь ажиллахгүй, учир нь шүүлтүүр нь завсарлагааны үед "бүдрэх" болно.

Энэ асуудлыг шийдэх хэд хэдэн арга замыг авч үзье.

Арга 1. Хоосон нүднүүдийг хайх

Энэ нь хамгийн тохиромжтой биш байж болох ч хамгийн хялбар арга бол мэдээжийн хэрэг дурдах нь зүйтэй юм.

Бид дотор нь олон хоосон мөр, багана агуулсан ийм хүснэгттэй харьцаж байна гэж бодъё (тодорхой болгох үүднээс тодруулсан):

Манай хүснэгтийн эхний баганад (Б багана) үргэлж хотын нэрийг агуулсан гэдэгт итгэлтэй байна гэж бодъё. Дараа нь энэ баганын хоосон нүднүүд нь шаардлагагүй хоосон мөрүүдийн шинж тэмдэг болно. Тэдгээрийг бүгдийг нь хурдан арилгахын тулд дараах зүйлийг хийнэ үү.

  1. Хот бүхий мужийг сонгоно уу (B2:B26)
  2. Товчлуурыг дарна уу F5 Дараа нь дар Тодруул (Тусгай руу очих) эсвэл таб дээрээс сонгоно уу Нүүр хуудас — Олоод сонгох — Нүдний бүлэг сонгоно уу (Нүүр хуудас — Хай, сонгох — Тусгай руу очих).
  3. Нээгдсэн цонхонд сонголтыг сонгоно уу Хоосон эсүүд (хоосон) болон дар OK – Хүснэгтийн эхний баганын бүх хоосон нүдийг сонгох ёстой.
  4. Одоо таб дээрээс сонгоно уу Нүүр хуудас тушаал Устгах - хуудаснаас мөрүүдийг устгах (Устгах - мөрүүдийг устгах) эсвэл гарын товчлолыг дарна уу Ctrl+хасах - Тэгээд бидний даалгавар шийдэгдсэн.

Мэдээжийн хэрэг, та хүснэгтийн толгой хэсгийг үндэс болгон ашиглан хоосон багануудаас салж болно.

Арга 2: Хоосон мөрүүдийг хайх

Та аль хэдийн ойлгосон байх, өмнөх арга нь зөвхөн бидний өгөгдөлд хоосон нүднүүдийг хайх үед залгаж болох бүрэн дүүрэн мөр, багана агуулсан тохиолдолд л ажиллах болно. Гэхдээ ийм итгэл үнэмшил байхгүй, өгөгдөл нь хоосон нүднүүдийг агуулж байвал яах вэ?

Жишээлбэл, ийм тохиолдлыг дараах хүснэгтээс харна уу.

Энд арга нь арай илүү төвөгтэй байх болно:

  1. А2 нүдэнд функцийг оруулна уу COUNT (COUNTA), энэ нь баруун талд байгаа мөрөнд бөглөсөн нүднүүдийн тоог тооцоолж, энэ томьёог бүхэлд нь хүснэгтэд хуулна.
  2. A2 нүдийг сонгоод шүүлтүүрийг командаар асаана Өгөгдөл - Шүүлтүүр (Өгөгдөл - шүүлтүүр) эсвэл гарын товчлол Ctrl+Shift+L.
  3. Тооцоолсон багана, өөрөөр хэлбэл өгөгдөл байхгүй бүх мөрүүдийг тэгийг шүүж үзье.
  4. Шүүсэн мөрүүдийг сонгоод командын тусламжтайгаар устгахад л үлддэг Нүүр хуудас — Устгах -' Шийтээс мөр устгах (Нүүр хуудас - Устгах - Мөр устгах) эсвэл гарын товчлол Ctrl+хасах.
  5. Бид шүүлтүүрийг унтрааж, хоосон мөргүйгээр мэдээллээ авдаг.

Харамсалтай нь энэ заль мэхийг баганаар хийх боломжгүй болсон - Excel нь баганаар шүүж сураагүй байна.

Арга 3. Хуудас дээрх бүх хоосон мөр, баганыг арилгах макро

Та мөн энэ ажлыг автоматжуулахын тулд энгийн макро ашиглаж болно. Гарын товчлолыг дарна уу Alt+F11 эсвэл таб дээрээс сонгоно уу Developer - Visual Basic (Хөгжүүлэгч — Visual Basic редактор). Хэрэв таб Developer харагдахгүй байгаа тул та үүнийг идэвхжүүлж болно Файл - Сонголтууд - Туузны тохиргоо (Файл - Сонголтууд - Туузыг тохируулах).

Нээгдэх Visual Basic редакторын цонхноос цэсний командыг сонгоно Оруулах - Модуль гарч ирэх хоосон модульд дараах мөрүүдийг хуулж буулгана уу.

   Sub DeleteEmpty() Dim r As Long, rng As Range 'удаляем пустые строки For r = 1 To ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count If Application.CountA(Rows(r)) = 0 Дараа нь If rng юу ч биш Дараа нь тохируул rng = Мөр(r) Өөр тохируул rng = Холбоо(rng, Мөр(r)) Төгсгөл Дараа нь r Хэрэв үгүй ​​бол rng юу ч биш Дараа нь rng.Delete 'удаляем пустые столбцы Set rng = Юу ч For r = 1 To ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count Хэрэв Application.CountA(Columns(r)) = 0 Хэрэв rng юу ч биш бол rng = Columns(r) гэж тохируулна уу. Өөрөөр тохируулна уу rng = Холбоо (rng, Багана(r)) r)) End If Next r If Not rng юу ч биш Дараа нь rng.Delete End Sub

Редакторыг хаагаад Excel рүү буцна уу. 

Одоо хослолыг дарна уу Alt+F8 эсвэл товчлуур Макроны таб Developer. Нээгдсэн цонхонд таны шинээр үүсгэсэн макро зэрэг ажиллуулах боломжтой бүх макронуудыг жагсаана. Устгах хоосон. Үүнийг сонгоод товчлуур дээр дарна уу Run (гүйх) – хуудас дээрх бүх хоосон мөр, баганууд нэн даруй устах болно.

Арга 4: Power Query

Бидний асуудлыг шийдэх өөр нэг арга бөгөөд маш түгээмэл хувилбар бол Power Query дахь хоосон мөр, багануудыг арилгах явдал юм.

Эхлээд хүснэгтээ Power Query Query Editor руу ачаалъя. Та үүнийг Ctrl+T товчлуурын товчлуурын тусламжтайгаар динамик "ухаалаг" болгон хувиргах эсвэл зүгээр л манай өгөгдлийн хүрээг сонгоод нэр өгөх боломжтой (жишээ нь). Өгөгдөл) томъёоны мөрөнд нэрлэсэн болгон хувиргах:

Одоо бид Data – Мэдээлэл авах – Хүснэгтээс / мужаас (Өгөгдөл – Хүснэгтээс мэдээлэл авах – Хүснэгтээс / муж) командыг ашиглаж Power Query руу бүгдийг ачаална.

Дараа нь бүх зүйл энгийн:

  1. Бид хоосон мөрүүдийг Нүүр хуудас – Мөрийг багасгах – Мөрийг устгах – Хоосон мөрийг устгах (Нүүр хуудас – Мөр арилгах – Хоосон мөрүүдийг устгах) командын тусламжтайгаар устгадаг.
  2. Хотын эхний баганын гарчиг дээр хулганы баруун товчийг дараад контекст цэснээс Unpivot Other Columns командыг сонгоно. Манай хүснэгт нь техникийн хувьд зөв нэрлэгдсэний дагуу ийм байх болно. хэвийн болгосон – гурван баганад хөрвүүлэв: хот, сар, хотын уулзвараас авсан утга, анхны хүснэгтээс сар. Power Query дахь энэхүү үйлдлийн онцлог нь эх өгөгдлийн хоосон нүднүүдийг алгасах явдал бөгөөд энэ нь бидэнд хэрэгтэй зүйл юм:
  3. Одоо бид урвуу үйлдлийг хийж байна - үүссэн хүснэгтийг анхны хэлбэрт нь буцаахын тулд хоёр хэмжээст болгон хувиргана. Таб дээрээс сартай баганыг сонгоно уу Хувиргах баг сонгох Пивот багана (Өөрчлөх - Пивот багана). Нээгдсэн цонхонд утгуудын багана болгон сүүлчийн (утга) хэсгийг сонгоод, нэмэлт сонголтуудаас үйлдлийг сонгоно уу. Бүү нэгтгэ (Бүү нэгтгэ):
  4. Үр дүнг командын тусламжтайгаар Excel рүү буцааж оруулахад л үлддэг Нүүр хуудас — Хаах ба ачаалах — Хаах ба ачаалах... (Нүүр хуудас — Хаах&Ачаалах — Хаах&Ачаалах...)

  • Макро гэж юу вэ, энэ нь хэрхэн ажилладаг, макро текстийг хаана хуулах, макро хэрхэн ажиллуулах вэ?
  • Жагсаалтын бүх хоосон нүдийг эх нүднүүдийн утгуудаар дүүргэнэ
  • Өгөгдсөн мужаас бүх хоосон нүдийг устгаж байна
  • PLEX нэмэлтээр ажлын хуудасны бүх хоосон мөрийг устгаж байна

хариу үлдээх