Tukšo rindu un kolonnu noņemšana datos

Tukšas rindas un kolonnas daudzos gadījumos var radīt grūtības tabulās. Standarta funkcijas šķirošanai, filtrēšanai, apkopošanai, rakurstabulu veidošanai utt. tukšas rindas un kolonnas uztver kā tabulas pārtraukumu, neuzņemot datus, kas atrodas tālāk aiz tām. Ja šādu spraugu ir daudz, to manuāla noņemšana var izmaksāt ļoti dārgi, un, izmantojot filtrēšanu, visus uzreiz noņemt “vairumā” neizdosies, jo filtrs “paklups” arī pārtraukumos.

Apskatīsim vairākus veidus, kā atrisināt šo problēmu.

1. metode. Meklēt tukšas šūnas

Tas var nebūt ērtākais, bet noteikti ir jāpiemin vienkāršākais veids.

Pieņemsim, ka mums ir darīšana ar šādu tabulu, kurā ir daudz tukšu rindu un kolonnu (skaidrības labad izcelts):

Pieņemsim, ka esam pārliecināti, ka mūsu tabulas pirmajā kolonnā (B kolonna) vienmēr ir norādīts pilsētas nosaukums. Tad tukšas šūnas šajā kolonnā būs zīme par nevajadzīgām tukšām rindām. Lai tos visus ātri noņemtu, rīkojieties šādi:

  1. Izvēlieties diapazonu ar pilsētām (B2:B26)
  2. Nospiediet taustiņu F5 un pēc tam nospiediet Izcelt (Doties uz īpašo) vai atlasiet cilnē Sākums — Atrast un atlasīt — atlasiet šūnu grupu (Sākums — Atrast un atlasīt — Pāriet uz īpašo).
  3. Atvērtajā logā atlasiet opciju Tukšas šūnas (Tukšas vietas) un nospiediet OK – ir jāatlasa visas tukšās šūnas mūsu tabulas pirmajā kolonnā.
  4. Tagad cilnē atlasiet Sākumlapa Komanda Dzēst — dzēst rindas no lapas (Dzēst — dzēst rindas) vai nospiediet īsinājumtaustiņu Ctrl+mīnus – un mūsu uzdevums ir atrisināts.

Protams, no tukšām kolonnām var atbrīvoties tieši tādā pašā veidā, par pamatu izmantojot tabulas galveni.

2. metode: meklējiet tukšas rindas

Kā jūs, iespējams, jau esat sapratuši, iepriekšējā metode darbosies tikai tad, ja mūsu datos noteikti ir pilnībā aizpildītas rindas un kolonnas, kuras var izmantot, meklējot tukšas šūnas. Bet ko tad, ja šādas pārliecības nav un datos var būt arī tukšas šūnas?

Apskatiet, piemēram, šādu tabulu tieši šādam gadījumam:

Šeit pieeja būs nedaudz sarežģītāka:

  1. Šūnā A2 ievadiet funkciju COUNT (COUNTA), kas aprēķinās aizpildīto šūnu skaitu rindā pa labi un kopēs šo formulu visā tabulā:
  2. Atlasiet šūnu A2 un ieslēdziet filtru ar komandu Dati - filtrs (Dati — filtrs) vai īsinājumtaustiņš Ctrl+maiņa+L.
  3. Izfiltrēsim nulles pēc aprēķinātās kolonnas, ti, visas rindas, kurās nav datu.
  4. Atliek atlasīt filtrētās līnijas un izdzēst tās ar komandu Sākums — Dzēst — Dzēst rindas no lapas (Sākums — Dzēst — Dzēst rindas) vai īsinājumtaustiņš Ctrl+mīnus.
  5. Mēs izslēdzam filtru un iegūstam savus datus bez tukšām rindām.

Diemžēl ar kolonnām šo triku vairs nevar izdarīt – Excel vēl nav iemācījies filtrēt pēc kolonnām.

3. metode. Makro, lai no lapas noņemtu visas tukšās rindas un kolonnas

Varat arī izmantot vienkāršu makro, lai automatizētu šo uzdevumu. Nospiediet īsinājumtaustiņu cits+F11 vai izvēlieties no cilnes attīstītājs - Visual Basic (Izstrādātājs — Visual Basic redaktors). Ja cilnes attīstītājs nav redzams, varat to iespējot, izmantojot Fails - Opcijas - Lentes iestatīšana (Fails — opcijas — pielāgot lenti).

Atvērtajā Visual Basic redaktora logā atlasiet izvēlnes komandu Ievietot - modulis un parādītajā tukšajā modulī kopējiet un ielīmējiet šādas rindiņas:

   Sub DeleteEmpty() Dim r As Long, rng As Range 'удаляем пустые строки For r = 1 Līdz ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count Ja Application.CountA(Rows(r)) = 0 rng nav nekas, tad iestatīt rng = rindas(r) cits iestatīt rng = savienība(rng, rindas(r)) beigas, ja nākamais r Ja nav rng nav nekas, tad rng.Delete 'удаляем пустые столбцы Iestatīt rng = nekas priekš r = 1 uz ActiveSheet.UsedRange.Column — 1 + ActiveSheet.UsedRange.Columns.Count If Application.CountA(Columns(r)) = 0 then If rng ir nekas, tad Set rng = Columns(r) Citādi Set rng = Union(rng, Columns( r)) Beigt, ja nākamais r Ja nav, rng nav nekas, tad rng.Delete End Sub

Aizveriet redaktoru un atgriezieties programmā Excel. 

Tagad nospiediet kombināciju cits+F8 vai pogu Makro tab attīstītājs. Atvērtajā logā būs uzskaitīti visi pašlaik izpildei pieejamie makro, tostarp tikko izveidotais makro. DzēstTukšs. Atlasiet to un noklikšķiniet uz pogas skrējiens (skriet) – visas tukšās rindas un kolonnas lapā tiks nekavējoties izdzēstas.

4. metode: Power Query

Vēl viens veids, kā atrisināt mūsu problēmu un ļoti izplatītu scenāriju, ir noņemt tukšās rindas un kolonnas programmā Power Query.

Vispirms ielādēsim tabulu Power Query vaicājumu redaktorā. Varat to pārvērst par dinamisku “viedo” ar īsinājumtaustiņu Ctrl+T vai vienkārši atlasiet mūsu datu diapazonu un piešķiriet tam nosaukumu (piemēram, Datums) formulu joslā, pārvēršot par nosaukumu:

Tagad mēs izmantojam komandu Dati - Iegūt datus - No tabulas / diapazona (Dati - Iegūt datus - No tabulas / diapazona) un ielādējam visu programmā Power Query:

Tad viss ir vienkārši:

  1. Tukšās rindas izdzēšam ar komandu Sākums – Samazināt rindas – Dzēst rindas – Dzēst tukšās rindas (Sākums – Noņemt rindas – Noņemt tukšas rindas).
  2. Ar peles labo pogu noklikšķiniet uz pirmās pilsētas kolonnas virsraksta un konteksta izvēlnē atlasiet komandu Unpivot Other Columns. Mūsu tabula būs, kā to tehniski pareizi sauc, normalizēts – pārveidots trīs kolonnās: pilsēta, mēnesis un vērtība no pilsētas krustpunkta un mēnesis no sākotnējās tabulas. Šīs operācijas īpatnība programmā Power Query ir tāda, ka tā izlaiž tukšas šūnas avota datos, kas mums ir nepieciešams:
  3. Tagad veicam apgriezto darbību – iegūto tabulu pārvēršam atpakaļ par divdimensiju, lai atgrieztu to sākotnējā formā. Atlasiet kolonnu ar mēnešiem un cilnē transformācija izvēlēties komandu Rakursa kolonna (Transformācija — rakursleja). Atvērtajā logā kā vērtību kolonnu atlasiet pēdējo (Vērtība), bet papildu opcijās - darbību Neapkopot (Neapkopot):
  4. Atliek augšupielādēt rezultātu atpakaļ programmā Excel ar komandu Sākums — Aizvērt un ielādēt — Aizvērt un ielādēt… (Sākums — Aizvērt&Ielādēt — Aizvērt&Ielādēt...)

  • Kas ir makro, kā tas darbojas, kur kopēt makro tekstu, kā palaist makro?
  • Visu tukšo šūnu aizpildīšana sarakstā ar vecākšūnu vērtībām
  • No noteiktā diapazona tiek noņemtas visas tukšās šūnas
  • Visu tukšo rindu noņemšana darblapā, izmantojot PLEX papildinājumu

Atstāj atbildi