Eliminarea rândurilor și coloanelor goale din date

Rândurile și coloanele goale pot fi o durere în tabele în multe cazuri. Funcțiile standard de sortare, filtrare, rezumare, creare de tabele pivot etc. percep rândurile și coloanele goale ca o pauză de tabel, fără a prelua datele aflate mai în spatele lor. Dacă există multe astfel de goluri, atunci îndepărtarea manuală a acestora poate fi foarte costisitoare și nu va funcționa să le eliminați toate odată „în vrac” folosind filtrarea, deoarece filtrul se va „împieci” și la pauze.

Să ne uităm la mai multe moduri de a rezolva această problemă.

Metoda 1. Căutați celule goale

Poate că aceasta nu este cea mai convenabilă, dar cu siguranță cea mai ușoară cale este demnă de menționat.

Să presupunem că avem de-a face cu un astfel de tabel care conține multe rânduri și coloane goale în interior (evidențiate pentru claritate):

Să presupunem că suntem siguri că prima coloană a tabelului nostru (coloana B) conține întotdeauna numele unui oraș. Apoi, celulele goale din această coloană vor fi un semn de rânduri goale inutile. Pentru a le elimina rapid pe toate, procedați în felul următor:

1. Selectați intervalul cu orașe (B2:B26)
2. Apăsați tasta F5 apoi apăsați Evidențiați (Mergi la Special) sau selectați din filă Acasă — Găsiți și selectați — Selectați un grup de celule (Acasă — Găsiți&Selectați — Mergeți la special).
3. În fereastra care se deschide, selectați opțiunea Celulele goale (Goluri) și apăsați OK – trebuie selectate toate celulele goale din prima coloană a tabelului nostru.
4. Acum selectați pe filă Acasă Comandă Ștergere – Ștergeți rândurile din foaie (Șterge — Șterge rândurile) sau apăsați comanda rapidă de la tastatură Ctrl+minus – iar sarcina noastră este rezolvată.

Desigur, puteți scăpa de coloanele goale exact în același mod, folosind antetul tabelului ca bază.

Metoda 2: Căutați rânduri goale

După cum probabil v-ați dat seama deja, metoda anterioară va funcționa numai dacă datele noastre conțin în mod necesar rânduri și coloane complet completate, care pot fi conectate atunci când căutați celule goale. Dar dacă nu există o astfel de încredere, iar datele pot conține și celule goale?

Aruncă o privire la următorul tabel, de exemplu, doar pentru un astfel de caz:

Aici abordarea va fi puțin mai complicată:

1. Introduceți în celula A2 funcția COUNT (COUNTA), care va calcula numărul de celule completate din rândul din dreapta și va copia această formulă în întregul tabel:
2. Selectați celula A2 și activați filtrul cu comanda Date – Filtru (Date — Filtru) sau scurtătură de la tastatură Ctrl+Schimba+L.
3. Să filtrăm zerourile după coloana calculată, adică toate rândurile în care nu există date.
4. Rămâne să selectați liniile filtrate și să le ștergeți cu comanda Acasă — Ștergeți -' Ștergeți rândurile din foaie (Acasă — Șterge — Șterge rânduri) sau scurtătură de la tastatură Ctrl+minus.
5. Oprim filtrul și obținem datele fără linii goale.

Din păcate, acest truc nu se mai poate face cu coloane – Excel nu a învățat încă să filtreze după coloane.

Metoda 3. Macro pentru a elimina toate rândurile și coloanele goale de pe o foaie

De asemenea, puteți utiliza o macrocomandă simplă pentru a automatiza această sarcină. Apăsați comanda rapidă de la tastatură Alt+F11 sau selectați din filă dezvoltator - Visual Basic (Dezvoltator — Editor Visual Basic). Dacă file dezvoltator nu este vizibil, îl puteți activa prin intermediul Fișier – Opțiuni – Configurare panglică (Fișier — Opțiuni — Personalizare panglică).

În fereastra editorului Visual Basic care se deschide, selectați comanda de meniu Inserare – Modul iar în modulul gol care apare, copiați și lipiți următoarele rânduri:

   Sub DeleteEmpty() Dim r As Long, rng As Range 'удаляем пустые строки Pentru r = 1 To ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count If Application.CountA(Rows(r)) = 0 rng Este Nimic Apoi Set rng = Rânduri(r) Altfel Set rng = Uniune(rng, Rânduri(r)) Sfârșit Dacă Următorul r Dacă Nu rng Este Nimic Atunci rng.Delete 'удаляем пустые столбцы Set rng = Nimic pentru ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count Dacă Application.CountA(Columns(r)) = 1 Atunci dacă rng este nimic, atunci Set rng = Columns(r) Altfel Set rng = Union(rng, Columns( r)) End If Next r If Not rng Este Nimic Atunci rng.Delete End Sub  

Închideți editorul și reveniți la Excel. 

Acum apăsați combinația Alt+F8 sau butonul Macrocomenzi fila dezvoltator. Fereastra care se deschide va lista toate macrocomenzile disponibile în prezent pentru a le executa, inclusiv macrocomandă pe care tocmai ați creat-o. DeleteEmpty. Selectați-l și faceți clic pe butonul Alerga (alerga) – toate rândurile și coloanele goale de pe foaie vor fi șterse instantaneu.

Metoda 4: Power Query

O altă modalitate de a ne rezolva problema și un scenariu foarte comun este eliminarea rândurilor și coloanelor goale din Power Query.

Mai întâi, să încărcăm tabelul nostru în Editorul de interogări Power Query. Îl puteți converti într-unul dinamic „inteligent” cu comanda rapidă de la tastatură Ctrl+T sau pur și simplu selectați intervalul nostru de date și dați-i un nume (de exemplu Date) în bara de formule, conversia în named:

Acum folosim comanda Data – Obține date – Din tabel / interval (Date – Obține date – Din tabel / interval) și încărcăm totul în Power Query:

Atunci totul este simplu:

1. Stergem liniile goale cu comanda Home – Reduce lines – Delete lines – Delete empty lines (Home – Remove Rows – Remove empty rows).
2. Faceți clic dreapta pe antetul primei coloane Oraș și selectați comanda Unpivot Other Columns din meniul contextual. Tabelul nostru va fi, așa cum este numit corect din punct de vedere tehnic, normalizat – convertit în trei coloane: oraș, lună și valoare de la intersecția orașului și luna din tabelul inițial. Particularitatea acestei operațiuni în Power Query este că omite celulele goale din datele sursă, care este ceea ce avem nevoie:
3. Acum efectuăm operația inversă – transformăm tabelul rezultat înapoi într-unul bidimensional pentru a-l readuce la forma inițială. Selectați coloana cu luni și pe filă Transformare alege o echipa Coloana pivot (Transformare — coloană pivot). În fereastra care se deschide, sub formă de coloană de valori, selectați ultima (Valoare), iar în opțiunile avansate – operația Nu agregați (Nu agregați):
4. Rămâne să încărcați rezultatul înapoi în Excel cu comanda Acasă — Închideți și încărcați — Închideți și încărcați în... (Acasă — Închide&Încărcare — Închide&Încărcare la...)

• Ce este o macrocomandă, cum funcționează, unde să copiați textul unei macrocomenzi, cum să rulați o macrocomandă?
• Completarea tuturor celulelor goale din listă cu valorile celulelor părinte
• Eliminarea tuturor celulelor goale dintr-un interval dat
• Eliminarea tuturor rândurilor goale dintr-o foaie de lucru cu suplimentul PLEX