Tukšu šūnu noņemšana no diapazona

Problēmas formulēšana

Mums ir virkne šūnu ar datiem, kas satur tukšas šūnas:

 

Uzdevums ir noņemt tukšās šūnas, atstājot tikai šūnas ar informāciju.

1. metode. Rupji un ātri

  1. Sākotnējā diapazona izvēle
  2. Nospiediet taustiņu F5, nākamā poga Izcelt (Īpašs). Atvērtajā logā atlasiet Tukšas šūnas(Tukšas vietas) un noklikšķiniet OK.

    Tukšu šūnu noņemšana no diapazona

    Ir atlasītas visas tukšās šūnas diapazonā.

  3. Mēs izvēlnē dodam komandu dzēst atlasītās šūnas: ar peles labo pogu noklikšķinietSākot no Dzēst šūnas (Dzēst šūnas) ar nobīdi uz augšu.

2. metode: masīva formula

Lai vienkāršotu, nosauksim mūsu darba diapazonus, izmantojot Nosaukuma vadītājs (Vārdu pārvaldnieks) tab formula (Formulas) vai programmā Excel 2003 un vecākām versijām — izvēlni Ievietot - Nosaukums - Piešķirt (Ievietot — Nosaukums — Definēt)

 

Nosauciet diapazonu B3:B10 HaveEmpty, diapazons D3:D10 – NavTukšs. Diapazoniem jābūt stingri vienāda izmēra, un tie var atrasties jebkurā vietā attiecībā pret otru.

Tagad atlasiet otrā diapazona pirmo šūnu (D3) un ievadiet tajā šo biedējošo formulu:

=IF(RINDA() -RINDA(NavTukšs)+1>NOTROWS(JāTukšs)-COUNTBLANK(JāTukšs);””;INDIRECT(ADRESS(ZEMĀKĀ((IF(Tukšs<>“”,RINDA(Tukšs);ROW()) + RINDAS (Ir tukšas))))

Angļu valodas versijā tas būs:

=IF(RINDA()-RINDA(NavTukša)+1>RINDAS(Tukša)-SKAITSTUKSTE(Tukša),"",NETIEŠĀ(ADRESE(SMALL((JA(Tukšs<>"",RINDA(Tukšs),RINDA()) +RINDAS(Ir tukšs))),RINDA()-RINDA(NavTukšs)+1),SLEJA(Ir tukšs),4)))

Turklāt tas jāievada kā masīva formula, ti, nospiediet pēc ielīmēšanas ienākt (kā parasti) un Ctrl + Shift + Enter. Tagad formulu var nokopēt, izmantojot automātisko pabeigšanu (velciet melno krustiņu šūnas apakšējā labajā stūrī) - un mēs iegūsim sākotnējo diapazonu, bet bez tukšām šūnām:

 

3. metode. Pielāgota funkcija VBA

Ja ir aizdomas, ka bieži nāksies atkārtot tukšo šūnu noņemšanas procedūru no diapazoniem, tad labāk ir vienreiz pievienot standarta komplektam savu tukšo šūnu noņemšanas funkciju un izmantot to visos turpmākajos gadījumos.

Lai to izdarītu, atveriet Visual Basic redaktoru (ALT + F11), ievietojiet jaunu tukšu moduli (izvēlni Ievietot - modulis) un nokopējiet tur šīs funkcijas tekstu:

Funkcija NoBlanks(DatuRange As Range) Kā Variant() Dim N Tik Gars Dim N2 Tik Long Dim Rng Kā Diapazons Dim MaxCells Tik Long Dim Result() Kā Variants Dim R Tik Long Dim C Kā Long MaxCells = Application.WorksheetFunction.Max( _ Application.Caller.Cells.Count, Data Range.Cells.Count) ReDim Result(1 līdz MaxCells, 1 to 1) Katram Rng in DataRange.Cells If Rng.Value <> vbNullString then N = N + 1 Result(N, 1) ) = Rng.Value End If Next Rng For N2 = N + 1 To MaxCells Result(N2, 1) = vbNullString Nākamais N2 Ja Application.Caller.Rows.Count = 1 Tad NoBlanks = Application.Transpose(Result) Citādi NoBlanks = Rezultāts Beigt, ja beigu funkcija

Neaizmirstiet saglabāt failu un pārslēgties atpakaļ no Visual Basic redaktora uz Excel. Lai izmantotu šo funkciju mūsu piemērā:

  1. Atlasiet pietiekamu tukšo šūnu diapazonu, piemēram, F3:F10.
  2. Dodieties uz izvēlni Ievietot - funkcija (Ievietot — funkcija)vai noklikšķiniet uz pogas Ievietošanas funkcija (Ievietot funkciju) tab formula (Formulas) jaunākās Excel versijās. Kategorijā Lietotājs definēts (Lietotājs definēts) izvēlieties mūsu funkciju Bez tukšām vietām.
  3. Norādiet avota diapazonu ar tukšumiem (B3:B10) kā funkcijas argumentu un nospiediet Ctrl + Shift + Enterlai ievadītu funkciju kā masīva formulu.

:

  • Visu tukšo rindu dzēšana tabulā uzreiz, izmantojot vienkāršu makro
  • Visu tukšo rindu noņemšana darblapā vienlaikus, izmantojot PLEX papildinājumu
  • Ātri aizpildiet visas tukšās šūnas
  • Kas ir makro, kur ievietot makro kodu VBA

 

Atstāj atbildi