Mundarija
Ko'p hollarda bo'sh satrlar va ustunlar jadvallarda og'riq bo'lishi mumkin. Saralash, filtrlash, umumlashtirish, pivot jadvallarni yaratish va hokazolarning standart funktsiyalari bo'sh qatorlar va ustunlarni ularning orqasida joylashgan ma'lumotlarni olmasdan, jadval tanaffusi sifatida qabul qiladi. Agar bunday bo'shliqlar ko'p bo'lsa, ularni qo'lda olib tashlash juda qimmatga tushishi mumkin va filtrlash yordamida bir vaqtning o'zida "ommaviy" olib tashlash ishlamaydi, chunki filtr tanaffuslarda ham "qoqilib qoladi".
Keling, ushbu muammoni hal qilishning bir necha usullarini ko'rib chiqaylik.
1-usul. Bo'sh hujayralarni qidiring
Bu eng qulay bo'lmasligi mumkin, lekin, albatta, eng oson yo'lini eslatib o'tish kerak.
Aytaylik, biz juda ko'p bo'sh qatorlar va ustunlarni o'z ichiga olgan jadval bilan ishlaymiz (aniqlik uchun ta'kidlangan):
Aytaylik, jadvalimizning birinchi ustunida (B ustuni) har doim shahar nomi borligiga aminmiz. Keyin ushbu ustundagi bo'sh katakchalar keraksiz bo'sh qatorlar belgisi bo'ladi. Ularning barchasini tezda olib tashlash uchun quyidagilarni bajaring:
- Shaharlar bilan diapazonni tanlang (B2:B26)
- Tugmani bosing F5 tugmasini bosing va keyin tugmasini bosing ta'kidlash (Maxsusga o'tish) yoki yorliqda tanlang Bosh sahifa — Top va tanlash — Hujayralar guruhini tanlang (Uy — Top&Tanlash — Maxsusga o‘tish).
- Ochilgan oynada variantni tanlang Bo'sh hujayralar (Blankalar) va matbuot OK - jadvalimizning birinchi ustunidagi barcha bo'sh katakchalarni tanlash kerak.
- Endi yorliqda tanlang Bosh sahifa buyruq O'chirish - varaqdan qatorlarni o'chirish (O'chirish - qatorlarni o'chirish) yoki klaviatura yorliqlarini bosing Ctrl+manfiy - va bizning vazifamiz hal qilindi.
Albatta, jadval sarlavhasini asos qilib ishlatib, xuddi shu tarzda bo'sh ustunlardan xalos bo'lishingiz mumkin.
2-usul: bo'sh qatorlarni qidiring
Siz allaqachon tushunganingizdek, avvalgi usul faqat bizning ma'lumotlarimizda bo'sh hujayralarni qidirishda ulanishi mumkin bo'lgan to'liq to'ldirilgan qatorlar va ustunlar bo'lsa ishlaydi. Ammo bunday ishonch bo'lmasa va ma'lumotlarda bo'sh hujayralar ham bo'lishi mumkinmi?
Masalan, bunday holat uchun quyidagi jadvalni ko'rib chiqing:
Bu erda yondashuv biroz qiyinroq bo'ladi:
- Funktsiyani A2 katakka kiriting COUNT (COUNTA), bu o'ngdagi qatordagi to'ldirilgan katakchalar sonini hisoblab chiqadi va ushbu formulani butun jadvalga ko'chiradi:
- A2 katakchani tanlang va buyruq bilan filtrni yoqing Ma'lumotlar - Filtr (Ma'lumotlar - Filtr) yoki klaviatura yorlig'i Ctrl+smena+L.
- Nollarni hisoblangan ustun, ya'ni ma'lumotlar bo'lmagan barcha qatorlar bo'yicha filtrlaymiz.
- Filtrlangan chiziqlarni tanlash va ularni buyruq bilan o'chirish qoladi Bosh sahifa — Oʻchirish -' varaqdan qatorlarni oʻchirish (Uy — Oʻchirish — Qatorlarni oʻchirish) yoki klaviatura yorlig'i Ctrl+manfiy.
- Biz filtrni o'chirib, bo'sh qatorlarsiz ma'lumotlarimizni olamiz.
Afsuski, bu hiylani endi ustunlar yordamida amalga oshirish mumkin emas - Excel ustunlar bo'yicha qanday filtrlashni hali o'rganmagan.
3-usul. Varaqdagi barcha bo'sh satr va ustunlarni olib tashlash uchun so'l
Ushbu vazifani avtomatlashtirish uchun oddiy makrosdan ham foydalanishingiz mumkin. Klaviatura yorlig'ini bosing Alt+F11 yoki yorliqdan tanlang dasturchi - Visual Basic (Dasturchi — Visual Basic muharriri). Agar yorliqlar dasturchi ko'rinmaydi, siz uni yoqishingiz mumkin Fayl - Variantlar - Tasmani o'rnatish (Fayl - Variantlar - Tasmani sozlash).
Ochilgan Visual Basic muharriri oynasida menyu buyrug'ini tanlang Qo'shish - modul va paydo bo'lgan bo'sh modulda quyidagi qatorlarni nusxalash va joylashtirish:
Sub DeleteEmpty() Dim r As Long, rng As Range 'udalyaem pustye stroki For r = 1 To ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count Agar Application.CountA(Rows(r)) = 0 bo'lsa, agar rng Hech narsa emas, keyin o'rnating rng = Qatorlar(r) Boshqa o'rnating rng = Birlashma (rng, Qatorlar(r)) Agar Keyingi r bo'lmasa, rng hech narsa emas, keyin rng.Delete 'udalyaem pustye stolbtsy O'rnating rng = Hech narsa uchun r = 1 uchun ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count Agar Application.CountA(Columns(r)) = 0 bo'lsa, Agar rng hech narsa bo'lmasa, rng = Columns(r) boshqasini o'rnating rng = Union(rng, Columns() r)) End If Next r If Not rng Nothing then rng.Delete End Sub
Tahrirlovchini yoping va Excelga qayting.
Endi kombinatsiyani bosing Alt+F8 yoki tugma macros tab dasturchi. Ochilgan oynada siz hozirda ishga tushirishingiz mumkin bo'lgan barcha makroslar, shu jumladan siz yaratgan makros ro'yxati ko'rsatiladi. DeleteEmpty. Uni tanlang va tugmani bosing yugurish (yugurish) - varaqdagi barcha bo'sh qatorlar va ustunlar bir zumda o'chiriladi.
4-usul: Power Query
Muammoni hal qilishning yana bir usuli va juda keng tarqalgan stsenariy Power Query-da bo'sh qatorlar va ustunlarni olib tashlashdir.
Birinchidan, jadvalimizni Power Query Query Editor-ga yuklaymiz. Siz uni Ctrl+T klaviatura yorlig'i yordamida dinamik "aqlli"ga aylantirishingiz yoki shunchaki ma'lumotlar diapazonimizni tanlab, unga nom berishingiz mumkin (masalan, ma'lumotlar) formulalar qatorida quyidagiga aylantiriladi:
Endi biz Ma'lumotlar - Ma'lumotlarni olish - Jadvaldan / diapazondan (Ma'lumotlar - Ma'lumotlarni olish - Jadvaldan / diapazondan) buyrug'idan foydalanamiz va barchasini Power Query-ga yuklaymiz:
Keyin hamma narsa oddiy:
- Bosh sahifa - Chiziqlarni qisqartirish - Chiziqlarni o'chirish - Bo'sh satrlarni o'chirish (Bosh sahifa - Qatorlarni o'chirish - Bo'sh qatorlarni o'chirish) buyrug'i bilan bo'sh satrlarni o'chirib tashlaymiz.
- Birinchi shahar ustunining sarlavhasini o'ng tugmasini bosing va kontekst menyusidan "Boshqa ustunlarni ochish" buyrug'ini tanlang. Bizning jadvalimiz, texnik jihatdan to'g'ri nomlanganidek, normallashtirilgan – uchta ustunga aylantirildi: shahar, oy va shahar kesishmasidan qiymat va asl jadvaldan oy. Power Query-dagi ushbu operatsiyaning o'ziga xos xususiyati shundaki, u manba ma'lumotlaridagi bo'sh kataklarni o'tkazib yuboradi, bu bizga kerak bo'ladi:
- Endi biz teskari operatsiyani bajaramiz - natijada olingan jadvalni asl shakliga qaytarish uchun uni ikki o'lchovliga aylantiramiz. Oylar va yorliqda ustunni tanlang O'tkazish jamoani tanlang Pivot ustuni (Transformatsiya - pivot ustun). Ochilgan oynada qiymatlar ustuni sifatida oxirgi (Qiymat) va kengaytirilgan variantlarda - operatsiyani tanlang. Birlashtirmang (Birlashtirmang):
- Natijani buyruq bilan Excelga qayta yuklash qoladi Bosh sahifa — Yopish va yuklash — Yopish va yuklash… (Uy — Yopish va yuklash — Yopish va yuklash…)
- Makros nima, u qanday ishlaydi, makros matnini qayerdan nusxalash kerak, makrosni qanday ishga tushirish kerak?
- Ro'yxatdagi barcha bo'sh kataklarni ota-onalarning qiymatlari bilan to'ldirish
- Berilgan diapazondan barcha bo'sh hujayralarni olib tashlash
- PLEX qo'shimchasi bilan ishchi varaqdagi barcha bo'sh qatorlarni olib tashlash