Pag-alis ng mga walang laman na row at column sa data

Ang mga walang laman na row at column ay maaaring maging sakit sa mga talahanayan sa maraming kaso. Ang mga karaniwang function para sa pag-uuri, pag-filter, pagbubuod, paggawa ng mga pivot table, atbp. ay nakikita ang mga walang laman na row at column bilang isang table break, nang hindi kinukuha ang data na matatagpuan sa likod ng mga ito. Kung mayroong maraming ganoong mga puwang, kung gayon ang manu-manong pag-alis sa mga ito ay maaaring maging napakamahal, at hindi ito gagana upang alisin ang lahat nang sabay-sabay "nang maramihan" gamit ang pag-filter, dahil ang filter ay "matitisod" din sa mga break.

Tingnan natin ang ilang mga paraan upang malutas ang problemang ito.

Paraan 1. Maghanap ng mga walang laman na cell

Maaaring hindi ito ang pinaka-maginhawa, ngunit tiyak na ang pinakamadaling paraan ay karapat-dapat na banggitin.

Ipagpalagay na nakikitungo tayo sa naturang talahanayan na naglalaman ng maraming walang laman na mga hilera at mga haligi sa loob (naka-highlight para sa kalinawan):

Ipagpalagay natin na ang unang column ng ating table (column B) ay laging naglalaman ng pangalan ng isang lungsod. Kung gayon ang mga walang laman na cell sa column na ito ay magiging tanda ng mga hindi kinakailangang walang laman na row. Upang mabilis na alisin ang lahat ng ito, gawin ang sumusunod:

  1. Pumili ng saklaw na may mga lungsod (B2:B26)
  2. Pindutin ang susi F5 at pagkatapos ay pindutin I-highlight (Pumunta sa Espesyal) o pumili sa tab Home — Hanapin at Piliin — Pumili ng grupo ng mga cell (Home — Hanapin at Piliin — Pumunta sa espesyal).
  3. Sa window na bubukas, piliin ang opsyon Walang laman na mga cell (Blanko) at pindutin ang OK – dapat piliin ang lahat ng walang laman na cell sa unang column ng aming table.
  4. Ngayon pumili sa tab Tahanan Utos Tanggalin - Tanggalin ang mga hilera mula sa sheet (Tanggalin - Tanggalin ang mga hilera) o pindutin ang keyboard shortcut Ctrl+binawasan – at nalutas ang aming gawain.

Siyempre, maaari mong alisin ang mga walang laman na column sa eksaktong parehong paraan, gamit ang table header bilang batayan.

Paraan 2: Maghanap ng mga walang laman na row

Tulad ng maaaring naisip mo na, gagana lang ang nakaraang paraan kung ang aming data ay kinakailangang naglalaman ng mga row at column na ganap na napuno, na maaaring ma-hook kapag naghahanap ng mga walang laman na cell. Ngunit paano kung walang ganoong kumpiyansa, at ang data ay maaaring naglalaman din ng mga walang laman na cell?

Tingnan ang sumusunod na talahanayan, halimbawa, para sa ganoong kaso:

Narito ang diskarte ay magiging mas nakakalito:

  1. Ipasok sa cell A2 ang function COUNT (COUNTA), na kakalkulahin ang bilang ng mga napunong cell sa hilera sa kanan at kopyahin ang formula na ito pababa sa buong talahanayan:
  2. Piliin ang cell A2 at i-on ang filter gamit ang command Data – Salain (Data — Filter) o keyboard shortcut Ctrl+Ilipat+L.
  3. I-filter natin ang mga zero ayon sa kinakalkulang column, ibig sabihin, lahat ng row kung saan walang data.
  4. Ito ay nananatiling piliin ang mga na-filter na linya at tanggalin ang mga ito gamit ang utos Home — Tanggalin -' Tanggalin ang mga hilera mula sa sheet (Home — Tanggalin — Tanggalin ang mga hilera) o keyboard shortcut Ctrl+binawasan.
  5. Isinasara namin ang filter at kinukuha ang aming data nang walang mga linyang walang laman.

Sa kasamaang palad, ang trick na ito ay hindi na magagawa sa mga column – hindi pa natututunan ng Excel kung paano mag-filter ayon sa mga column.

Paraan 3. Macro para alisin ang lahat ng walang laman na row at column sa isang sheet

Maaari ka ring gumamit ng simpleng macro para i-automate ang gawaing ito. Pindutin ang keyboard shortcut Alt+F11 o pumili mula sa tab developer — Visual Basic (Developer — Visual Basic Editor). Kung tabs developer ay hindi nakikita, maaari mo itong paganahin sa pamamagitan ng File – Opsyon – Ribbon Setup (File — Mga Pagpipilian — I-customize ang Ribbon).

Sa window ng Visual Basic editor na bubukas, piliin ang command ng menu Ipasok – Module at sa lalabas na walang laman na module, kopyahin at i-paste ang mga sumusunod na linya:

   Sub DeleteEmpty() Dim r As Long, rng As Range 'удаляем пустые строки Para sa r = 1 Sa ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count If Application.CountA(Rows(r)) = 0 Then If rng Is Nothing Then Set rng = Rows(r) Else Set rng = Union(rng, Rows(r)) End If Next r Kung Hindi rng Is Nothing Then rng.Delete 'удаляем пустые столбцы Set rng = Nothing For r = 1 To ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count If Application.CountA(Columns(r)) = 0 Then If rng Is Nothing Then Set rng = Columns(r) Else Set rng = Union(rng, Columns( r)) End Kung Susunod r Kung Hindi rng Ay Wala Then rng.Delete End Sub

Isara ang editor at bumalik sa Excel. 

Ngayon pindutin ang kumbinasyon Alt+F8 o pindutan Macros tab developer. Ililista ng window na bubukas ang lahat ng mga macro na kasalukuyang magagamit mo upang patakbuhin, kasama ang macro na kakagawa mo lang. DeleteEmpty. Piliin ito at i-click ang pindutan Tumakbo (tumakbo) – lahat ng walang laman na row at column sa sheet ay agad na tatanggalin.

Paraan 4: Power Query

Ang isa pang paraan upang malutas ang aming problema at isang napakakaraniwang sitwasyon ay ang pag-alis ng mga walang laman na row at column sa Power Query.

Una, i-load natin ang aming talahanayan sa Power Query Query Editor. Maaari mo itong i-convert sa isang dynamic na "matalinong" gamit ang keyboard shortcut na Ctrl+T o piliin lamang ang aming hanay ng data at bigyan ito ng pangalan (halimbawa data) sa formula bar, nagko-convert sa pinangalanang:

Ngayon ginagamit namin ang command na Data - Kumuha ng data - Mula sa talahanayan / saklaw (Data - Kumuha ng Data - Mula sa talahanayan / saklaw) at i-load ang lahat sa Power Query:

Kung gayon ang lahat ay simple:

  1. Tinatanggal namin ang mga walang laman na linya gamit ang command na Home - Bawasan ang mga linya - Tanggalin ang mga linya - Tanggalin ang mga walang laman na linya (Home - Alisin ang Mga Rows - Alisin ang mga walang laman na row).
  2. Mag-right-click sa heading ng unang column ng Lungsod at piliin ang Unpivot Other Columns command mula sa context menu. Ang aming talahanayan ay, gaya ng wastong tawag dito, na-normalize – na-convert sa tatlong column: lungsod, buwan at halaga mula sa intersection ng lungsod at buwan mula sa orihinal na talahanayan. Ang kakaiba ng operasyong ito sa Power Query ay ang paglaktaw nito sa mga walang laman na cell sa source data, na siyang kailangan natin:
  3. Ngayon ay ginagawa namin ang reverse operation - binabalik namin ang resultang talahanayan sa isang two-dimensional na isa upang maibalik ito sa orihinal nitong anyo. Piliin ang column na may mga buwan at sa tab Pagbabagong-anyo pumili ng isang pangkat Pivot column (Transform — Pivot Column). Sa window na bubukas, bilang isang hanay ng mga halaga, piliin ang huli (Halaga), at sa mga advanced na pagpipilian - ang operasyon Huwag pagsama-samahin (Huwag pagsama-samahin):
  4. Nananatili itong i-upload ang resulta pabalik sa Excel gamit ang utos Tahanan — Isara at I-load — Isara at I-load sa… (Home — Isara&I-load — Isara&I-load sa…)

  • Ano ang isang macro, paano ito gumagana, kung saan kokopyahin ang teksto ng isang macro, kung paano magpatakbo ng isang macro?
  • Punan ang lahat ng mga walang laman na cell sa listahan ng mga halaga ng mga cell ng magulang
  • Pag-alis ng lahat ng walang laman na cell mula sa isang ibinigay na hanay
  • Tinatanggal ang lahat ng walang laman na row sa isang worksheet gamit ang PLEX add-on

Mag-iwan ng Sagot