Tuščių langelių pašalinimas iš diapazono

Problemos formulavimas

Turime daugybę langelių su duomenimis, kuriuose yra tuščių langelių:

 

Užduotis yra pašalinti tuščius langelius, paliekant tik langelius su informacija.

1 būdas. Grubus ir greitas

  1. Pradinio diapazono pasirinkimas
  2. Paspauskite klavišą F5, kitas mygtukas Pabrėžti (Specialus). Atsidariusiame lange pasirinkite Tuščios ląstelės(Tuščios vietos) ir spauskite OK.

    Tuščių langelių pašalinimas iš diapazono

    Pažymėti visi tušti langeliai diapazone.

  3. Meniu duodame komandą ištrinti pasirinktus langelius: spustelėkite dešinįjį pelės klavišą- Ištrinti langelius (Ištrinti langelius) su poslinkiu aukštyn.

2 būdas: masyvo formulė

Kad būtų paprasčiau, pavadinkime savo darbo diapazonus naudodami Vardų tvarkytuvė (Vardų vadybininkas) kortelė formulė (Formulės) arba „Excel 2003“ ir senesnėse versijose meniu Įterpti – Vardas – Priskirti (Įterpti – pavadinimas – apibrėžti)

 

Pavadinkite diapazoną B3:B10 Turi Tuščias, diapazonas D3:D10 – NėraTuščia. Diapazonai turi būti griežtai vienodo dydžio ir gali būti bet kur vienas kito atžvilgiu.

Dabar pasirinkite pirmąjį antrojo diapazono langelį (D3) ir įveskite į jį šią baisią formulę:

=IF(EILUTA() -EILUTE(NeTuščia)+1>NOTROWS(TaipTuščia)-COUNTBLANK(TaipTuščia);"";INDIRECT(ADDRESS(LOWEST((IF(Empty<>"",ROW(Empty);ROW()) + EILUTĖS (yra tuščių))); LINIJA () - EILUČIA (nėra tuščių) + 1); Stulpelis (yra tuščių); 4)))

Angliškoje versijoje tai bus:

=IF(EILUTA()-EILUTE(NeTuščia)+1>EILTELĖS(Tuščia)-COUNTBLANK(Tuščia),"",NETEISINIS(ADRESAS(MAŽAS((JEI(Tuščia<>"",EILUTĖ(Tuščia),ROW()) +EILTELĖS(Tuščios))),EILUTĖS()-EILUTE(NeTuščios)+1),Stulpelis(Tuščios),4)))

Be to, jį reikia įvesti kaip masyvo formulę, ty po įklijavimo paspauskite įeiti (kaip įprasta) ir Ctrl + Shift + Enter. Dabar formulę galima nukopijuoti naudojant automatinį užbaigimą (nuvilkite juodą kryžių apatiniame dešiniajame langelio kampe) – ir gausime pradinį diapazoną, bet be tuščių langelių:

 

3 būdas. Pasirinktinė funkcija VBA

Jei kyla įtarimas, kad dažnai teks kartoti tuščių langelių pašalinimo iš diapazonų procedūrą, geriau vieną kartą prie standartinio rinkinio pridėti savo tuščių langelių pašalinimo funkciją ir naudoti ją visais vėlesniais atvejais.

Norėdami tai padaryti, atidarykite Visual Basic redaktorių (ALT + F11), įdėkite naują tuščią modulį (meniu Įterpimas – modulis) ir nukopijuokite šios funkcijos tekstą ten:

Funkcija NoBlanks(DataRange As Range) As Variant() Dim N Kaip Long Dim N2 As Long Dim Rng As Range Dim MaxCells As Long Dim Result() Kaip Variant Dim R Kaip Long Dim C Kaip Long MaxCells = Application.WorksheetFunction.Max( _ Application.Caller.Cells.Count, DataRange.Cells.Count) ReDim Result (1 iki MaxCells, 1 to 1) Kiekvienam Rng DataRange.Cells Jei Rng.Value <> vbNullString Tada N = N + 1 Rezultatas(N, 1) ) = Rng.Value End If Next Rng N2 = N + 1 To MaxCells Rezultatas(N2, 1) = vbNullString Kitas N2 Jei Application.Caller.Rows.Count = 1 Tada NoBlanks = Application.Transpose(Result) Else NoBlanks = Result Funkcija „Baigti, jei baigti“.

Nepamirškite išsaugoti failo ir grįžti iš „Visual Basic“ redaktoriaus į „Excel“. Norėdami naudoti šią funkciją mūsų pavyzdyje:

  1. Pasirinkite pakankamą tuščių langelių diapazoną, pavyzdžiui, F3:F10.
  2. Eikite į meniu Įterpimas – funkcija (Įterpti – funkcija)arba spustelėkite mygtuką Įterpimo funkcija (Įterpti funkciją) kortelė formulė (Formulės) naujesnėse „Excel“ versijose. Kategorijoje Vartotojo apibrėžta (nustatyta naudotojo) pasirinkti mūsų funkciją Be tuščių.
  3. Nurodykite šaltinio diapazoną su tuštumais (B3:B10) kaip funkcijos argumentą ir paspauskite Ctrl + Shift + Enternorėdami įvesti funkciją kaip masyvo formulę.

:

  • Visų tuščių lentelės eilučių ištrynimas iš karto naudojant paprastą makrokomandą
  • Visų tuščių eilučių pašalinimas iš darbalapio vienu metu naudojant PLEX priedą
  • Greitai užpildykite visas tuščias ląsteles
  • Kas yra makrokomandos, kur įterpti makrokomandos kodą VBA

 

Palikti atsakymą