Tuščių duomenų eilučių ir stulpelių pašalinimas

Tuščios eilutės ir stulpeliai daugeliu atvejų gali būti lentelėse kančia. Standartinės rūšiavimo, filtravimo, apibendrinimo, suvestinių lentelių kūrimo ir tt funkcijos tuščias eilutes ir stulpelius suvokia kaip lentelės lūžį, nerenkant toliau už jų esančių duomenų. Jei tokių spragų yra daug, tuomet jų pašalinimas rankiniu būdu gali kainuoti labai brangiai, o naudojant filtravimą visų iš karto „masiškai“ pašalinti nepavyks, nes filtras taip pat „užklups“ per pertraukas.

Pažvelkime į kelis šios problemos sprendimo būdus.

1 būdas. Ieškokite tuščių langelių

Tai gali būti ne pats patogiausias, bet tikrai lengviausias būdas yra vertas paminėti.

Tarkime, kad susiduriame su tokia lentele, kurioje yra daug tuščių eilučių ir stulpelių (paryškinta aiškumo dėlei):

Tarkime, kad esame tikri, kad pirmajame mūsų lentelės stulpelyje (B stulpelyje) visada yra miesto pavadinimas. Tada tušti langeliai šiame stulpelyje bus nereikalingų tuščių eilučių ženklas. Norėdami greitai juos visus pašalinti, atlikite šiuos veiksmus:

  1. Pasirinkite diapazoną su miestais (B2:B26)
  2. Paspauskite klavišą F5 ir tada paspauskite Pabrėžti (Eiti į Specialųjį) arba pasirinkite skirtuke Pagrindinis puslapis — Rasti ir pasirinkti — Pasirinkite langelių grupę (Pagrindinis – Rasti ir pasirinkti – Eiti į specialųjį).
  3. Atsidariusiame lange pasirinkite parinktį Tuščios ląstelės (Tuščios vietos) ir paspauskite OK – turėtų būti pažymėti visi tušti langeliai pirmajame mūsų lentelės stulpelyje.
  4. Dabar pasirinkite skirtuke Pagrindinis Komanda Ištrinti – ištrinti eilutes iš lapo (Ištrinti – ištrinti eilutes) arba paspauskite spartųjį klavišą "Ctrl"+minusas – ir mūsų užduotis išspręsta.

Žinoma, lygiai taip pat galite atsikratyti tuščių stulpelių, kaip pagrindą naudodami lentelės antraštę.

2 būdas: ieškokite tuščių eilučių

Kaip jau galbūt supratote, ankstesnis metodas veiks tik tuo atveju, jei mūsų duomenyse būtinai yra visiškai užpildytos eilutės ir stulpeliai, kuriuos galima naudoti ieškant tuščių langelių. Bet kas, jei tokio pasitikėjimo nėra, o duomenyse taip pat gali būti tuščių langelių?

Pažvelkite į šią lentelę, pavyzdžiui, kaip tik tokiu atveju:

Čia metodas bus šiek tiek sudėtingesnis:

  1. A2 langelyje įveskite funkciją SKAIČIAVIMAS (COUNTA), kuris apskaičiuos užpildytų langelių skaičių eilutėje dešinėje ir nukopijuos šią formulę į visą lentelę:
  2. Pasirinkite langelį A2 ir įjunkite filtrą naudodami komandą Duomenys – filtras (Duomenys – filtras) arba spartusis klavišas "Ctrl"+perėjimas+L.
  3. Išfiltruokime nulius pagal apskaičiuotą stulpelį, ty visas eilutes, kuriose nėra duomenų.
  4. Belieka pasirinkti filtruojamas eilutes ir jas ištrinti komanda Pagrindinis – Ištrinti – Ištrinti eilutes iš lapo (Pagrindinis – Ištrinti – Ištrinti eilutes) arba spartusis klavišas "Ctrl"+minusas.
  5. Išjungiame filtrą ir gauname duomenis be tuščių eilučių.

Deja, šio triuko su stulpeliais daryti nebegalima – „Excel“ dar neišmoko filtruoti pagal stulpelius.

3 būdas. Makrokomandas, skirtas pašalinti visas tuščias lapo eilutes ir stulpelius

Norėdami automatizuoti šią užduotį, taip pat galite naudoti paprastą makrokomandą. Paspauskite spartųjį klavišą Kitas+F11 arba pasirinkite iš skirtuko ryškalas - Visual Basic (Kūrėjas – „Visual Basic“ redaktorius). Jei skirtukai ryškalas nematomas, galite jį įjungti Failas – Parinktys – Juostos sąranka (Failas – Parinktys – Tinkinti juostelę).

Atsidariusiame Visual Basic redaktoriaus lange pasirinkite meniu komandą Įterpimas – modulis ir pasirodžiusiame tuščiame modulyje nukopijuokite ir įklijuokite šias eilutes:

   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 rng yra nieko, tada nustatyti rng = Eilutės(r) Kita Nustatyti rng = Sąjunga(rng, Eilutės(r)) Pabaiga, jei Kitas r Jei Ne rng yra Niekas Tada rng.Delete 'удаляем пустые столбцы Nustatyti rng = nieko, r = 1 iki ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count If Application.CountA(Stulpeliai(r)) = 0 Tada Jei rng yra nieko, tada nustatykite rng = stulpeliai(r) Else Set rng = Sąjunga(rng, stulpeliai() r)) Pabaiga, jei sekantis r Jei ne rng yra nieko Tada rng.Ištrinti Pabaiga

Uždarykite redaktorių ir grįžkite į „Excel“. 

Dabar paspauskite derinį Kitas+F8 arba mygtuką Makrokomandos kortelė ryškalas. Atsidariusiame lange bus pateiktos visos šiuo metu galimos paleisti makrokomandos, įskaitant ką tik sukurtą makrokomandą. IštrintiTuščias. Pasirinkite jį ir spustelėkite mygtuką paleisti (bėgti) – visos tuščios eilutės ir stulpeliai lape bus iš karto ištrintos.

4 būdas: Power Query

Kitas būdas išspręsti problemą ir labai dažnas scenarijus yra pašalinti tuščias eilutes ir stulpelius iš Power Query.

Pirmiausia įkelkime lentelę į „Power Query“ užklausų rengyklę. Galite konvertuoti jį į dinaminį „išmanųjį“ naudodami spartųjį klavišą Ctrl+T arba tiesiog pasirinkti mūsų duomenų diapazoną ir suteikti jam pavadinimą (pvz. Duomenys) formulės juostoje, konvertuojant į pavadintą:

Dabar naudojame komandą Duomenys – Gauti duomenis – Iš lentelės / diapazono (duomenys – Gauti duomenis – iš lentelės / diapazono) ir įkeliame viską į „Power Query“:

Tada viskas paprasta:

  1. Tuščias eilutes triname komanda Pagrindinis – Sumažinti eilutes – Ištrinti eilutes – Ištrinti tuščias eilutes (Pagrindinis – Pašalinti eilutes – Pašalinti tuščias eilutes).
  2. Dešiniuoju pelės mygtuku spustelėkite pirmojo miesto stulpelio antraštę ir kontekstiniame meniu pasirinkite komandą Unpivot Other Columns. Mūsų lentelė bus, kaip ji techniškai teisingai vadinama, normalizuotas – paversta į tris stulpelius: miestas, mėnuo ir vertė iš miesto sankirtos ir mėnuo iš pirminės lentelės. Šios operacijos „Power Query“ ypatumas yra tas, kad ji praleidžia tuščius langelius šaltinio duomenyse, o to mums reikia:
  3. Dabar atliekame atvirkštinę operaciją – gautą lentelę paverčiame atgal į dvimatę, kad sugrąžintume pradinę formą. Pasirinkite stulpelį su mėnesiais ir skirtuke Transformacija pasirinkti komandą Sukamasis stulpelis (Transformacija – sukimosi stulpelis). Atsidariusiame lange kaip reikšmių stulpelį pasirinkite paskutinę (Value), o išplėstinėse parinktyse – operaciją Nejungti (Neapibendrinti):
  4. Belieka įkelti rezultatą atgal į Excel su komanda Pagrindinis puslapis – Uždaryti ir įkelti – Uždaryti ir įkelti… (Pagrindinis - Uždaryti ir įkelti - Uždaryti ir įkelti į...)

  • Kas yra makrokomanda, kaip ji veikia, kur nukopijuoti makrokomandos tekstą, kaip paleisti makrokomandą?
  • Visų tuščių sąrašo langelių užpildymas pirminių langelių reikšmėmis
  • Visų tuščių langelių pašalinimas iš nurodyto diapazono
  • Visų tuščių eilučių pašalinimas iš darbalapio naudojant PLEX priedą

Palikti atsakymą