Берилиштердеги бош саптарды жана мамычаларды алып салуу

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

Келгиле, бул маселени чечүүнүн бир нече жолдорун карап көрөлү.

Метод 1. Бош клеткаларды издөө

Бул эң ыңгайлуу болушу мүмкүн эмес, бирок, албетте, эң оңой жолу сөз кылууга арзыйт.

Ичинде көптөгөн бош саптар жана мамычаларды камтыган ушундай таблица менен иштеп жатабыз дейли (түшүнүктүү болуу үчүн баса белгиленген):

Биздин таблицабыздын биринчи тилкесинде (Б тилкесинде) ар дайым шаардын аталышы камтылганына ишенебиз дейли. Анда бул тилкедеги бош уячалар керексиз бош саптардын белгиси болот. Алардын баарын тез алып салуу үчүн, төмөнкүнү аткарыңыз:

  1. Шаарлар менен катар тандоо (B2:B26)
  2. Баскычты басыңыз F5 жана андан кийин басма сөз Бөлүп (Атайынга өтүү) же өтмөктөн тандаңыз Негизги бет — Таб жана Тандоо — Клеткалардын тобун танда (Башкы бет — Таб&Тандоо — Атайынга өтүү).
  3. Ачылган терезеде параметрди тандаңыз Бош клеткалар (Боштуктар) жана пресс-релиздер OK – биздин таблицабыздын биринчи тилкесиндеги бардык бош уячалар тандалышы керек.
  4. Азыр өтмөктөн тандаңыз Home буйрук Жок кылуу - барактан саптарды жок кылуу (Жок кылуу — Саптарды жок кылуу) же баскычтоптун жарлыгын басыңыз Ctrl+-сыз – жана биздин милдет чечилди.

Албетте, сиз дал ушундай жол менен бош мамычалардан кутулууга болот, таблицанын башын негиз катары колдонсоңуз болот.

Метод 2: бош саптарды издөө

Сиз буга чейин түшүнгөн болушуңуз керек, мурунку ыкма биздин маалыматтар сөзсүз түрдө толук толтурулган саптарды жана тилкелерди камтыса гана иштейт, аларды бош уячаларды издөөдө туташууга болот. Бирок, эгерде андай ишеним жок болсо жана маалыматтарда бош уячалар да болушу мүмкүн болсочу?

Төмөнкү таблицага көңүл буруңуз, мисалы, дал ушундай учур үчүн:

Бул жерде ыкма бир аз татаалыраак болот:

  1. А2 уячасына функцияны киргизиңиз COUNT (COUNTA), ал оң жактагы катардагы толтурулган уячалардын санын эсептеп, бул формуланы бүт таблицага көчүрөт:
  2. A2 уячасын тандап, буйрук менен чыпканы күйгүзүңүз Маалымат – чыпка (Маалымат — чыпка) же баскычтоптун жарлыгы Ctrl+өзгөрүү+L.
  3. Келгиле, нөлдөрдү эсептелген тилке боюнча чыпкалап көрөлү, башкача айтканда, маалыматтар жок бардык саптар.
  4. Чыпкаланган сызыктарды тандап, аларды буйрук менен жок кылуу калды Башкы бет — Жок кылуу -' Барактан саптарды жок кылуу (Башкы бет — Өчүрүү — Саптарды жок кылуу) же баскычтоптун жарлыгы Ctrl+-сыз.
  5. Биз чыпканы өчүрүп, маалыматтарыбызды бош сызыктарсыз алабыз.

Тилекке каршы, бул трюк мындан ары мамычалар менен аткарылышы мүмкүн эмес - Excel тилкелер боюнча чыпкалоону үйрөнө элек.

Метод 3. Макро барактагы бардык бош саптарды жана мамычаларды алып салуу

Бул тапшырманы автоматташтыруу үчүн жөнөкөй макросту да колдонсоңуз болот. Баскычтоптун жарлыгын басыңыз Alt+F11 же өтмөктөн тандаңыз иштеп — Visual Basic (Иштеп чыгуучу — Visual Basic редактору). Эгерде өтмөктөр иштеп көрүнбөйт, сиз аны иштетсеңиз болот Файл - Параметрлер - Тасма орнотуу (Файл — Параметрлер — Тасманы ыңгайлаштыруу).

Ачылган Visual Basic редакторунун терезесинде менюнун буйругун тандаңыз Кыстаруу – модуль жана пайда болгон бош модулда төмөнкү саптарды көчүрүп, чаптаңыз:

   Sub DeleteEmpty() Dim r As Long, rng As Range 'удаляем пустые строки For r = 1 ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count If Application.CountA(Rows(r)) = 0 Анда If rng Эч нерсе эмес Андан кийин орнотуңуз rng = Саптар(r) Else Коюңуз rng = Биримдик (rng, Сатар(r)) Аяктоо Эгерде Кийинки r Эгерде жок болсо rng ​​Эч нерсе эмес Анда rng.Delete 'удаляем пустые столбцы Орнотуу rng = Эч нерсе үчүн r = 1 үчүн ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count If 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 же баскыч Macros табулатура иштеп. Ачылган терезеде сиз иштеп жаткан бардык макростор, анын ичинде сиз жаңы түзүлгөн макрос тизмеси пайда болот. DeleteEmpty. Аны тандап, баскычты басыңыз Run (чуркоо) – барактагы бардык бош саптар жана мамычалар заматта жок кылынат.

Метод 4: Power Query

Биздин көйгөйдү чечүүнүн дагы бир жолу жана өтө кеңири таралган сценарий Power Query'деги бош саптарды жана мамычаларды алып салуу.

Биринчиден, келгиле, таблицабызды Power Query Query редакторуна жүктөйбүз. Сиз аны динамикалык "акылдуу" клавиатурага Ctrl+T баскычтары менен айландырсаңыз болот же жөн гана маалымат диапазонун тандап, ага ат бере аласыз (мисалы маалыматтар) формула тилкесинде аталганга айландыруу:

Эми биз Берилиштер – Берилиштерди алуу – Таблицадан / диапазондон (Маалымат – Берилиштерди алуу – Таблицадан / диапазон) буйругун колдонобуз жана бардыгын Power Queryге жүктөйбүз:

Ошондо баары жөнөкөй:

  1. Бош саптарды Башкы – Сызыкты кыскартуу – Сызыкты өчүрүү – Бош саптарды өчүрүү (Башкы бет – Саптарды өчүрүү – Бош саптарды өчүрүү) буйругу менен өчүрөбүз.
  2. Биринчи Шаар тилкесинин аталышын оң баскыч менен чыкылдатып, контексттик менюдан Башка тилкелерди ачуу буйругун тандаңыз. Биздин дасторконубуз техникалык жактан туура аталып калгандай болот. нормалдаштырылган – үч тилкеге ​​айландырылат: шаар, ай жана шаардын кесилишинен мааниси жана баштапкы таблицадан ай. Power Queryдеги бул операциянын өзгөчөлүгү, ал баштапкы маалыматтардагы бош уячаларды өткөрүп жиберет, бул бизге керек:
  3. Эми биз тескери операцияны аткарабыз – пайда болгон таблицаны баштапкы абалына кайтаруу үчүн кайра эки өлчөмдүү таблицага айлантабыз. Айлар менен тилкени тандаңыз жана өтмөктөн кайра команда тандоо Пивот тилкеси (Трансформация — Пивот мамычасы). Ачылган терезеде баалуулуктардын тилкеси катары акыркыны (Баалуу) жана өркүндөтүлгөн параметрлерден - операцияны тандаңыз. Бириктирбеңиз (Бириктирбеңиз):
  4. Натыйжаны кайра Excelге буйрук менен жүктөө калды Башкы бет — Жабуу жана жүктөө — Жабуу жана жүктөө… (Башкы бет — Жабуу&Жүктөө — Жабуу&Жүктөө...)

  • Макро деген эмне, ал кантип иштейт, макростун текстин кайда көчүрүү керек, макро кантип иштетүү керек?
  • Тизмедеги бардык бош уячаларды аталык уячалардын маанилери менен толтуруу
  • Берилген диапазондогу бардык бош уячаларды алып салуу
  • PLEX кошумчасы менен иш барагындагы бардык бош саптарды алып салуу

Таштап Жооп