Мундариҷа
Дар бисёр ҳолатҳо сатрҳо ва сутунҳои холӣ метавонанд дар ҷадвалҳо дардовар бошанд. Функсияҳои стандартӣ барои мураттабсозӣ, филтркунӣ, ҷамъбасткунӣ, эҷоди ҷадвалҳои ҷамъоварӣ ва ғ. сатрҳо ва сутунҳои холиро бидуни гирифтани маълумоти дар паси онҳо ҷойгиршуда ҳамчун шикастани ҷадвал қабул мекунанд. Агар чунин холигоҳҳо зиёд бошанд, он гоҳ дастӣ бартараф кардани онҳо метавонад хеле гарон бошад ва бо истифода аз филтр якбора тоза кардани ҳамаро "ба таври оммавӣ" кор намекунад, зеро филтр инчунин ҳангоми танаффус "пешпохӯрда" мешавад.
Биёед якчанд роҳҳои ҳалли ин мушкилотро дида бароем.
Усули 1. Ҷустуҷӯи чашмакҳои холӣ
Ин метавонад қулайтарин набошад, аммо бешубҳа роҳи осонтаринро зикр кардан лозим аст.
Фарз мекунем, ки мо бо чунин ҷадвале кор карда истодаем, ки дар дохили он сатрҳо ва сутунҳои холӣ мавҷуданд (барои возеҳият таъкид карда мешаванд):
Фарз мекунем, ки мо боварӣ дорем, ки дар сутуни якуми ҷадвали мо (сутуни В) ҳамеша номи шаҳр мавҷуд аст. Пас ячейкаҳои холӣ дар ин сутун аломати сатрҳои холии нолозим хоҳанд буд. Барои зуд бартараф кардани ҳамаи онҳо, амалҳои зеринро иҷро кунед:
- Диапазонро бо шаҳрҳо интихоб кунед (B2:B26)
- Тугмаро пахш кунед F5 ва пас пахш кунед Равшансозӣ (Ба махсус гузаред) ё дар ҷадвал интихоб кунед Хона — Ҷустуҷӯ ва интихоб — Гурӯҳи чашмакҳоро интихоб кунед (Хона — Ҷустуҷӯ ва Интихоб — Гузаштан ба махсус).
- Дар равзанаи кушодашуда, интихобро интихоб кунед Ҳуҷайраҳои холӣ (Бланкаҳо) ва пахш кунед OK – ҳамаи ячейкаҳои холии сутуни якуми ҷадвали мо бояд интихоб карда шаванд.
- Акнун дар ҷадвал интихоб кунед хона фармон Нест кардан - Тоза кардани сатрҳо аз варақ (Нест кардан — Тоза кардани сатрҳо) ё миёнабури клавиатураро пахш кунед Ctrl+бе — ва вазифаи мо хал шуд.
Албатта, шумо метавонед аз сутунҳои холӣ маҳз ҳамин тавр, бо истифода аз сарлавҳаи ҷадвал ҳамчун асос халос шавед.
Усули 2: Ҷустуҷӯи сатрҳои холӣ
Тавре ки шумо аллакай фаҳмидед, усули қаблӣ танҳо дар сурате кор хоҳад кард, ки маълумоти мо ҳатман сатрҳо ва сутунҳои пурра пуршударо дар бар гирад, ки онҳоро ҳангоми ҷустуҷӯи ячейкаҳои холӣ пайваст кардан мумкин аст. Аммо чӣ мешавад, агар чунин эътимод вуҷуд надошта бошад ва маълумот метавонад ҳуҷайраҳои холӣ дошта бошад?
Ба ҷадвали зерин нигаред, масалан, барои чунин ҳолат:
Дар ин ҷо равиш каме душвортар хоҳад буд:
- Функсияро дар чашмаки A2 ворид кунед КОНИ (COUNTA), ки шумораи чашмакҳои пуршударо дар сатри рост ҳисоб мекунад ва ин формуларо ба тамоми ҷадвал нусхабардорӣ мекунад:
- Чашмаки А2-ро интихоб кунед ва бо фармон филтрро фаъол созед Маълумот - Филтр (Маълумот - Филтр) ё миёнабури клавиатура Ctrl+Shift+L.
- Биёед сифрҳоро аз рӯи сутуни ҳисобшуда филтр кунем, яъне ҳамаи сатрҳое, ки маълумот мавҷуд нест.
- Барои интихоб кардани сатрҳои филтршуда ва нест кардани онҳо бо фармон боқӣ мемонад Хона — Нест кардан -' Сатрҳоро аз варақ тоза кунед (Асосӣ — Тоза кардан — Нест кардани сатрҳо) ё миёнабури клавиатура Ctrl+бе.
- Мо филтрро хомӯш мекунем ва маълумоти худро бе хатҳои холӣ мегирем.
Мутаассифона, ин ҳилларо дигар бо сутунҳо иҷро кардан мумкин нест - Excel то ҳол тарзи филтр кардани сутунҳоро наомухтааст.
Усули 3. Макрос барои нест кардани ҳама сатрҳо ва сутунҳои холӣ дар варақ
Шумо инчунин метавонед макросҳои оддиро барои автоматикунонии ин вазифа истифода баред. Миёнабури клавиатураро пахш кунед Alt+F11 ё аз ҷадвал интихоб кунед таҳиякунанда — Visual Basic (Таҳиягар — Муҳаррири Visual Basic). Агар ҷадвалҳо таҳиякунанда намоён нест, шумо метавонед онро тавассути фаъол созед Файл - Имконот - Танзими лента (Файл - Интихоб - Тасмаи фармоишӣ).
Дар равзанаи муҳаррири 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 Пас агар rng Ҳеҷ чиз нест Пас муқаррар кунед rng = Сатрҳо(r) Дигар Танзим кунед rng = Иттиҳод(rng, Сатр(r)) Анҷом Агар Оянда r Агар Не rng Ҳеҷ чиз нест Пас rng.Delete 'удаляем пустые столбцы Set rng = Ҳеҷ чиз Барои r = 1 Ба ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count Агар Application.CountA(Columns(r)) = 0 Пас, агар rng чизе набошад, rng = Сутунҳо(r) дигар муқаррар кунед rng = Иттиҳод(rng, Сутунҳо() r)) End Агар Оянда бошад r Агар Не rng Ҳеҷ чиз нест, пас rng. Delete End Sub
Муҳаррирро пӯшед ва ба Excel баргардед.
Акнун комбинатсияро пахш кунед Alt+F8 ё тугмача Макрос ҷадвали таҳиякунанда. Равзанаи кушодашуда ҳамаи макросҳои дар айни замон барои иҷро кардани шумо дастрасро номбар мекунад, аз ҷумла макроси наве, ки шумо сохтаед. Тоза кардани холӣ. Онро интихоб кунед ва тугмаро пахш кунед давидан (давидан) - ҳама сатрҳо ва сутунҳои холӣ дар варақ фавран нест карда мешаванд.
Усули 4: Дархости барқ
Роҳи дигари ҳалли мушкилоти мо ва сенарияи хеле маъмул ин нест кардани сатрҳо ва сутунҳои холӣ дар Power Query мебошад.
Аввалан, биёед ҷадвали худро ба Муҳаррири Power Query Query бор кунем. Шумо метавонед онро бо миёнабури клавиатураи Ctrl+T ба як "ақл"-и динамикӣ табдил диҳед ё танҳо диапазони маълумоти моро интихоб кунед ва ба он ном диҳед (масалан Маълумот) дар сатри формула табдил ба ном:
Акнун мо фармони Маълумот - Гирифтани маълумот - Аз ҷадвал / диапазонро истифода мебарем (Маълумот - Гирифтани маълумот - Аз ҷадвал / диапазон) ва ҳама чизро ба Power Query бор мекунем:
Пас ҳама чиз оддӣ аст:
- Мо сатрҳои холиро бо фармони Home – Reduce lines – Delete lines – Нобуд кардани сатрҳои холӣ (Home – Хориҷ кардани сатр – Хориҷ кардани сатрҳои холӣ) нест мекунем.
- Дар сарлавҳаи сутуни якуми шаҳр тугмаи рости мушро клик кунед ва аз менюи контекстӣ фармони Unpivot Sutunҳои дигарро интихоб кунед. Ҷадвали мо чунин хоҳад буд, ки он аз ҷиҳати техникӣ дуруст номида мешавад, муътадил - ба се сутун табдил дода мешавад: шаҳр, моҳ ва арзиш аз чорроҳаи шаҳр ва моҳ аз ҷадвали аслӣ. Хусусияти ин амалиёт дар Power Query дар он аст, ки он ҳуҷайраҳои холии маълумоти манбаъро мегузарад, ки ба мо лозим аст:
- Акнун мо амалиёти баръаксро иҷро мекунем - мо ҷадвали ҳосилшударо дубора ба шакли дученака табдил медиҳем, то онро ба шакли аввалааш баргардонем. Сутунро бо моҳҳо ва дар ҷадвал интихоб кунед дигаргунсозии даста интихоб кунед Сутуни пивот (Тағйир додан — Сутуни ҷамъбастӣ). Дар равзанаи кушодашуда, ҳамчун сутуни арзишҳо, охиринро (Арзиш) ва дар имконоти пешрафта - амалиётро интихоб кунед. Ҷамъ накунед (Ҷамъ накунед):
- Бо фармони такрорӣ ба Excel бор кардани натиҷа боқӣ мемонад Хона — Пӯшед ва бор кунед — Пӯшед ва бор кунед… (Хона — Пӯшед ва бор кунед — Пӯшед ва бор кунед…)
- Макрос чӣ гуна аст, он чӣ гуна кор мекунад, матни макросро дар куҷо нусхабардорӣ кардан мумкин аст, макросро чӣ гуна бояд иҷро кард?
- Пур кардани ҳамаи чашмакҳои холӣ дар рӯйхат бо арзишҳои ячейкаҳои волидайн
- Хориҷ кардани ҳама ҳуҷайраҳои холӣ аз диапазони додашуда
- Хориҷ кардани ҳама сатрҳои холӣ дар варақаи корӣ бо иловаи PLEX