Տվյալների մեջ դատարկ տողերի և սյունակների հեռացում

Դատարկ տողերն ու սյունակները շատ դեպքերում կարող են ցավ պատճառել աղյուսակներում: Տեսակավորելու, զտելու, ամփոփելու, առանցքային աղյուսակների ստեղծման և այլնի ստանդարտ գործառույթները դատարկ տողերն ու սյունակները ընկալում են որպես աղյուսակի ընդմիջում՝ առանց դրանց հետևում գտնվող տվյալները հավաքելու: Եթե ​​կան շատ նման բացեր, ապա դրանք ձեռքով հեռացնելը կարող է շատ ծախսատար լինել, և ֆիլտրման միջոցով միանգամից «մեծաքանակ» հեռացնելը չի ​​աշխատի, քանի որ ֆիլտրը նույնպես «կսայթաքի» ընդմիջումների ժամանակ:

Դիտարկենք այս խնդիրը լուծելու մի քանի ուղիներ:

Մեթոդ 1. Փնտրեք դատարկ բջիջներ

Սա գուցե ամենահարմարը չէ, բայց միանշանակ ամենահեշտ ճանապարհը արժանի է հիշատակման։

Ենթադրենք, մենք գործ ունենք նման աղյուսակի հետ, որը պարունակում է բազմաթիվ դատարկ տողեր և սյունակներ ներսում (ընդգծված պարզության համար).

Ենթադրենք, մենք համոզված ենք, որ մեր աղյուսակի առաջին սյունակը (սյունակ B) միշտ պարունակում է քաղաքի անունը։ Այնուհետև այս սյունակի դատարկ բջիջները կլինեն անհարկի դատարկ տողերի նշան: Բոլորը արագ հեռացնելու համար կատարեք հետևյալը.

1. Ընտրեք տիրույթ քաղաքներով (B2:B26)
2. Սեղմեք ստեղնը F5 եւ ապա սեղմեք Առաջ քաշել (Գնալ Հատուկ) կամ ընտրեք ներդիրում Գլխավոր — Գտնել և ընտրել — Ընտրել բջիջների խումբ (Տուն — Գտնել և ընտրել — Գնալ հատուկ).
3. Բացվող պատուհանում ընտրեք տարբերակը Դատարկ բջիջներ (Բլանկներ) եւ սեղմեք OK – մեր աղյուսակի առաջին սյունակի բոլոր դատարկ բջիջները պետք է ընտրվեն:
4. Այժմ ընտրեք ներդիրում Գլխավոր Հրաման Ջնջել – Ջնջել տողերը թերթից (Ջնջել — Ջնջել տողերը) կամ սեղմեք ստեղնաշարի դյուրանցումը Ctrl+մինուս - և մեր խնդիրը լուծված է:

Իհարկե, դուք կարող եք ազատվել դատարկ սյունակներից ճիշտ նույն կերպ՝ հիմք ընդունելով աղյուսակի վերնագիրը։

Մեթոդ 2. Փնտրեք դատարկ տողեր

Ինչպես արդեն հասկացել եք, նախորդ մեթոդը կաշխատի միայն այն դեպքում, եթե մեր տվյալները պարտադիր պարունակեն ամբողջությամբ լցված տողեր և սյունակներ, որոնց վրա կարելի է կցել դատարկ բջիջներ որոնելիս: Բայց ի՞նչ, եթե այդպիսի վստահություն չկա, և տվյալները կարող են նաև դատարկ բջիջներ պարունակել:

Նայեք հետևյալ աղյուսակին, օրինակ, հենց այսպիսի դեպքի համար.

Այստեղ մոտեցումը մի փոքր ավելի բարդ կլինի.

1. A2 բջիջում մուտքագրեք գործառույթը COUNT (COUNTA), որը կհաշվարկի աջ տողում լցված բջիջների թիվը և այս բանաձևը պատճենելու է ամբողջ աղյուսակում.
2. Ընտրեք A2 բջիջը և հրամանով միացրեք զտիչը Տվյալներ - զտիչ (Տվյալներ — Զտիչ) կամ ստեղնաշարի դյուրանցում Ctrl+Հերթափոխություն+L.
3. Եկեք զտենք զրոները ըստ հաշվարկված սյունակի, այսինքն՝ բոլոր տողերը, որտեղ տվյալներ չկան։
4. Մնում է ընտրել զտված տողերը և ջնջել դրանք հրամանով Գլխավոր — Ջնջել — Ջնջել տողերը թերթից (Տուն — Ջնջել — Ջնջել տողերը) կամ ստեղնաշարի դյուրանցում Ctrl+մինուս.
5. Մենք անջատում ենք ֆիլտրը և ստանում մեր տվյալները առանց դատարկ գծերի։

Ցավոք, այս հնարքն այլևս հնարավոր չէ անել սյունակներով. Excel-ը դեռ չի սովորել, թե ինչպես զտել ըստ սյունակների:

Մեթոդ 3. Մակրո՝ թերթի բոլոր դատարկ տողերն ու սյունակները հեռացնելու համար

Այս առաջադրանքը ավտոմատացնելու համար կարող եք նաև օգտագործել պարզ մակրո: Սեղմեք ստեղնաշարի դյուրանցումը ալտ+F11 կամ ընտրեք ներդիրից երեվակիչ — Visual Basic (Մշակող — Visual Basic խմբագիր). Եթե ​​ներդիրները երեվակիչ տեսանելի չէ, դուք կարող եք այն միացնել միջոցով Ֆայլ – Ընտրանքներ – Ժապավենի տեղադրում (Ֆայլ — Ընտրանքներ — Անհատականացնել ժապավենը).

Visual Basic խմբագրիչի պատուհանում, որը բացվում է, ընտրեք ցանկի հրամանը Ներդիր – մոդուլ և հայտնված դատարկ մոդուլում պատճենեք և տեղադրեք հետևյալ տողերը.

   Sub DeleteEmpty() Dim r Քանի դեռ, rng Որպես 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. Ջնջել 'удаляем пустые столбцы Սահմանել rng = Ոչինչ r = 1 To ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count If Application.CountA(Columns(r)) = 0 Ապա Եթե rng-ը ոչինչ չէ, ապա Սահմանել rng = Columns(r) Else Սահմանել rng = Union(rng, Columns( r)) Վերջ, եթե հաջորդը r Եթե ոչ rng-ը ոչինչ է, ապա rng: Ջնջել վերջի ենթ  

Փակեք խմբագրիչը և վերադարձեք Excel: 

Այժմ հարվածեք համադրությանը ալտ+F8 կամ կոճակ Մակրո ականջակալ երեվակիչ. Բացվող պատուհանում կցուցադրվեն բոլոր մակրոները, որոնք ներկայումս հասանելի են ձեզ գործարկելու համար, ներառյալ հենց նոր ստեղծած մակրոները: Ջնջել Դատարկ. Ընտրեք այն և սեղմեք կոճակը Վազում (վազել) – թերթի բոլոր դատարկ տողերն ու սյունակները անմիջապես կջնջվեն:

Մեթոդ 4. Power Query

Մեր խնդիրը լուծելու ևս մեկ տարբերակ և շատ տարածված սցենար է Power Query-ում դատարկ տողերն ու սյունակները հեռացնելը:

Նախ, եկեք բեռնենք մեր աղյուսակը Power Query Query Editor-ում: Դուք կարող եք այն վերածել դինամիկ «խելացիի» ստեղնաշարի դյուրանցմամբ Ctrl+T կամ պարզապես ընտրել մեր տվյալների տիրույթը և տալ անուն (օրինակ Ամսաթիվ) բանաձևի տողում, վերածելով անվանման՝

Այժմ մենք օգտագործում ենք հրամանը Տվյալներ – Ստացեք տվյալներ – Աղյուսակից / միջակայքից (Տվյալներ – Ստացեք տվյալներ – աղյուսակից / միջակայք) և ամեն ինչ բեռնում ենք Power Query-ում.

Այնուհետև ամեն ինչ պարզ է.

1. Դատարկ տողերը ջնջում ենք Home – Reduce lines – Delete lines – Delete դատարկ տողերը (Home – Remove Rows – Remove դատարկ տողերը) հրամանով:
2. Աջ սեղմեք Քաղաքի առաջին սյունակի վերնագրի վրա և համատեքստի ընտրացանկից ընտրեք Unpivot Other Columns հրամանը: Մեր սեղանը կլինի, ինչպես տեխնիկապես ճիշտ է կոչվում. նորմալացվեց – վերածվել է երեք սյունակի՝ քաղաք, ամիս և արժեք քաղաքի խաչմերուկից և ամիս սկզբնական աղյուսակից: Power Query-ում այս գործողության առանձնահատկությունն այն է, որ այն բաց է թողնում աղբյուրի տվյալների դատարկ բջիջները, ինչը մեզ անհրաժեշտ է.
3. Այժմ մենք կատարում ենք հակառակ գործողությունը. ստացված աղյուսակը նորից վերածում ենք երկչափի, որպեսզի այն վերադարձնենք իր սկզբնական ձևին: Ընտրեք սյունակը ամիսներով և ներդիրում Փոխակերպում ընտրիր թիմ Առանցքային սյունակ (Փոխակերպում — առանցքային սյունակ). Բացվող պատուհանում որպես արժեքների սյունակ ընտրեք վերջինը (Value), իսկ առաջադեմ տարբերակներում՝ գործողությունը. Մի համախմբեք (Մի համախմբեք):
4. Մնում է արդյունքը ետ վերբեռնել Excel հրամանով Գլխավոր — Փակել և բեռնել — Փակել և բեռնել… (Տուն — Փակել և բեռնել — Փակել և բեռնել…)

• Ի՞նչ է մակրոն, ինչպե՞ս է այն աշխատում, որտեղ պատճենել մակրոյի տեքստը, ինչպե՞ս գործարկել մակրո:
• Լրացնելով ցանկի բոլոր դատարկ բջիջները մայր բջիջների արժեքներով
• Բոլոր դատարկ բջիջները հեռացնելով տվյալ տիրույթից
• Աշխատանքային թերթի բոլոր դատարկ տողերի հեռացում PLEX հավելումով