Мазмұны
Көптеген жағдайларда бос жолдар мен бағандар кестелерде ауырсыну болуы мүмкін. Сұрыптауға, сүзуге, қорытындылауға, жиынтық кестелерді жасауға және т.б. арналған стандартты функциялар бос жолдар мен бағандарды олардың артында орналасқан деректерді алмай-ақ, кесте үзілімі ретінде қабылдайды. Егер мұндай бос орындар көп болса, оларды қолмен жою өте қымбат болуы мүмкін және сүзгілеу арқылы барлығын бірден «жаппай» жою жұмыс істемейді, өйткені сүзгі үзілістерде де «сүрініп қалады».
Бұл мәселені шешудің бірнеше жолдарын қарастырайық.
1-әдіс. Бос ұяшықтарды іздеу
Бұл ең қолайлы болмауы мүмкін, бірақ, әрине, ең оңай жолы атап өтуге тұрарлық.
Біз көптеген бос жолдар мен бағандарды қамтитын кестемен жұмыс істеп жатырмыз делік (анық болу үшін бөлектелген):
Кестенің бірінші бағанында (В бағанында) әрқашан қала атауы бар екеніне сенімдіміз делік. Сонда бұл бағандағы бос ұяшықтар қажетсіз бос жолдардың белгісі болады. Олардың барлығын жылдам жою үшін келесі әрекеттерді орындаңыз:
- Қалалары бар ауқымды таңдаңыз (B2:B26)
- Пернені басыңыз F5 содан кейін түймесін басыңыз Назар аударыңыз (Арнайы бөліміне өту) немесе қойындыда таңдаңыз Басты — Табу және таңдау — Ұяшықтар тобын таңдаңыз (Басты бет — Табу&Таңдау — Арнайыға өту).
- Ашылған терезеде опцияны таңдаңыз Бос ұяшықтар (Бос орындар) және баспасөз OK – кестенің бірінші бағанындағы барлық бос ұяшықтарды таңдау керек.
- Енді қойындыда таңдаңыз Home бұйрық Жою – парақтан жолдарды жою (Жою — жолдарды жою) немесе пернелер тіркесімін басыңыз Ctrl+минус – және біздің міндетіміз шешілді.
Әрине, кесте тақырыбын негізге ала отырып, бос бағандардан дәл осылай құтылуға болады.
2-әдіс: бос жолдарды іздеу
Сіз бұрыннан түсінгеніңіздей, алдыңғы әдіс деректерімізде бос ұяшықтарды іздеу кезінде қосылуға болатын толық толтырылған жолдар мен бағандар болған жағдайда ғана жұмыс істейді. Бірақ мұндай сенімділік болмаса және деректерде бос ұяшықтар да болуы мүмкін болса ше?
Мысалы, дәл осындай жағдай үшін келесі кестені қараңыз:
Мұнда тәсіл сәл қиынырақ болады:
- А2 ұяшығына функцияны енгізіңіз COUNT (COUNTA), ол оң жақтағы жолдағы толтырылған ұяшықтардың санын есептейді және осы формуланы бүкіл кестеге көшіреді:
- А2 ұяшығын таңдап, пәрмен арқылы сүзгіні қосыңыз Деректер – Сүзгі (Деректер — Сүзгі) немесе пернелер тіркесімі Ctrl+ауысым+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 Содан кейін If rng Ештеңе емес Содан кейін орнатыңыз rng = Жолдар(r) Басқа орнатыңыз rng = Бірлестіріңіз (rng, Жолдар(r)) Егер Келесі r Егер болмаса rng Ештеңе емес Содан кейін rng.Delete 'удаляем пустые столбцы орнату rng = Ештеңе үшін r = 1 үшін ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count, Application.CountA(Columns(r)) = 0, Егер rng ешнәрсе болмаса, онда rng = Columns(r) орнатыңыз Else Rng = Union(rng, Columns() орнатыңыз r)) End If Next r If Not rng ештеңе болмаса, онда rng.Delete End Sub
Редакторды жауып, Excel бағдарламасына оралыңыз.
Енді комбинацияны басыңыз Alt+F8 немесе батырма макростар қойындысы әзірлеуші. Ашылған терезеде іске қосу үшін қол жетімді барлық макростар, соның ішінде жаңа жасалған макростар тізімі көрсетіледі. DeleteEmpty. Оны таңдап, түймені басыңыз жүгіру (жүгіру) – парақтағы барлық бос жолдар мен бағандар бірден жойылады.
4-әдіс: Power Query
Мәселені шешудің тағы бір жолы және өте кең таралған сценарий - Power Query ішіндегі бос жолдар мен бағандарды жою.
Алдымен кестені Power Query Query өңдегішіне жүктейміз. Оны Ctrl+T пернелер тіркесімі арқылы динамикалық «ақылдыға» түрлендіруге немесе жай ғана деректер ауқымын таңдап, оған атау беруге болады (мысалы, мәліметтер) формула жолында аталғанға түрлендіру:
Енді Деректер – Деректерді алу – Кестеден / диапазоннан (Деректер – Деректерді алу – Кестеден / диапазон) командасын қолданамыз және барлығын Power Query-ге жүктейміз:
Сонда бәрі қарапайым:
- Басты – Сызықтарды азайту – Жолдарды жою – Бос жолдарды жою (Басты бет – Жолдарды жою – Бос жолдарды жою) командасы арқылы бос жолдарды жоямыз.
- Бірінші Қала бағанының тақырыбын тінтуірдің оң жақ түймешігімен басып, контекстік мәзірден Басқа бағандарды жою пәрменін таңдаңыз. Біздің үстеліміз техникалық тұрғыдан дұрыс аталғандай болады, қалыпқа келтірілген – үш бағанға түрлендірілді: қала, ай және қала қиылысындағы мән және бастапқы кестеден ай. Power Query бағдарламасындағы бұл әрекеттің ерекшелігі, ол бастапқы деректердегі бос ұяшықтарды өткізіп жібереді, бұл бізге қажет:
- Енді біз кері операцияны орындаймыз – алынған кестені бастапқы түріне қайтару үшін оны екі өлшемдіге айналдырамыз. Айлары бар бағанды және қойындыда таңдаңыз трансформация команда таңдау Жиынтық баған (Түрлендіру — жиынтық баған). Ашылған терезеде мәндер бағанасы ретінде соңғыны (Мән), ал кеңейтілген опцияларда операцияны таңдаңыз. Біріктірмеңіз (Біріктірмеңіз):
- Нәтижені Excel бағдарламасына пәрменмен кері жүктеу қалады Негізгі бет — Жабу және жүктеу — Жабу және жүктеу… (Басты бет — Жабу&Жүктеу — Жабу&Жүктеу...)
- Макрос дегеніміз не, ол қалай жұмыс істейді, макрос мәтінін қайда көшіру керек, макросты қалай іске қосу керек?
- Тізімдегі барлық бос ұяшықтарды ата-аналық ұяшықтардың мәндерімен толтыру
- Берілген ауқымдағы барлық бос ұяшықтарды жою
- PLEX қондырмасы бар жұмыс парағындағы барлық бос жолдарды жою