Obsah
Formulácia problému
Máme rozsah buniek s údajmi, ktoré obsahujú prázdne bunky:
Úlohou je odstrániť prázdne bunky a ponechať len bunky s informáciami.
Spôsob 1. Hrubý a rýchly
- Výber pôvodného rozsahu
- Stlačte kláves F5, tlačidlo ďalej Zdôrazniť (špeciálne). V okne, ktoré sa otvorí, vyberte Prázdne bunky(prázdne miesta) a kliknite na tlačidlo OK.
Vyberú sa všetky prázdne bunky v rozsahu.
- V ponuke dávame príkaz na odstránenie vybratých buniek: kliknite pravým tlačidlom myši- Odstrániť bunky (Odstrániť bunky) s posunom nahor.
Metóda 2: Vzorec poľa
Pre zjednodušenie vymenujme naše pracovné rozsahy pomocou Správca mien (Správca mien) pútko vzorec (vzorce) alebo v Exceli 2003 a staršom menu Vložiť – Názov – Priradiť (Vložiť — Názov — Definovať)
Pomenujte rozsah B3:B10 HaveEmpty, rozsah D3:D10 – ŽiadnePrázdne. Rozsahy musia mať striktne rovnakú veľkosť a môžu byť umiestnené navzájom kdekoľvek.
Teraz vyberte prvú bunku druhého rozsahu (D3) a zadajte do nej tento strašidelný vzorec:
=AK(RIADOK() -RIADOK(NiePrázdne)+1>NOTROWS(ÁnoPrázdne)-COUNTBLANK(ÁnoPrázdne);””;NEPRIAME(ADRESA(NAJNIŽŠIE((AK(Prázdne<>””,RIADOK(Prázdne);RIADOK)) + RIADKY (sú prázdne))); RIADOK () - RIADOK (žiadne prázdne) + 1); STĹPEC (sú prázdne); 4)))
V anglickej verzii to bude:
=AK(RIADOK()-RIADOK(žiadnePrázdne)+1>RIADKY(Prázdne)-POČET PRÁZDNE(Prázdne),””,NEPRIAME(ADRESA(MALÉ((AK(Prázdne<>””,RIADOK(Prázdne),RIADOK)) +RIADKY(MaťPrázdne))),RIADOK()-RIADOK(NiePrázdne)+1),STĺpec(Vyprázdniť),4)))
Navyše musí byť zadaný ako maticový vzorec, tj po vložení stlačte vstúpiť (ako obvykle) a Ctrl + Shift + Enter. Teraz je možné vzorec skopírovať pomocou automatického dopĺňania (potiahnite čierny krížik v pravom dolnom rohu bunky) – a získame pôvodný rozsah, ale bez prázdnych buniek:
Metóda 3. Vlastná funkcia vo VBA
Ak existuje podozrenie, že budete musieť často opakovať postup na odstraňovanie prázdnych buniek z rozsahov, potom je lepšie pridať do štandardnej sady raz vlastnú funkciu na odstránenie prázdnych buniek a použiť ju vo všetkých nasledujúcich prípadoch.
Ak to chcete urobiť, otvorte editor jazyka Visual Basic (ALT + F11), vložte nový prázdny modul (menu Vložiť – Modul) a skopírujte tam text tejto funkcie:
Funkcia NoBlanks(DataRange As Range) As Variant() Dim N As Long Dim N2 As Long Dim Rng As Range Dim MaxCells As Long Dim Result() As Variant Dim R As long Dim C As Long MaxCells = Application.WorksheetFunction.Max( _ Application.Caller.Cells.Count, DataRange.Cells.Count) ReDim Result(1 to MaxCells, 1 to 1) for each 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 Next N2 If Application.Caller.Rows.Count = 1 Then NoBlanks = Application.Transpose(Result) Else NoBlanks = Result End If End Funkcia
Nezabudnite uložiť súbor a prepnúť späť z editora jazyka Visual Basic do Excelu. Ak chcete použiť túto funkciu v našom príklade:
- Vyberte dostatočný rozsah prázdnych buniek, napríklad F3:F10.
- Prejdite do ponuky Vložiť – Funkcia (Vložiť – Funkcia)alebo kliknite na tlačidlo Funkcia vloženia (Vložiť funkciu) pútko vzorec (vzorce) v novších verziách Excelu. V kategórii Definované užívateľom (Definované užívateľom) vyberte si našu funkciu Žiadne prázdne miesta.
- Zadajte zdrojový rozsah s medzerami (B3:B10) ako argument funkcie a stlačte Ctrl + Shift + Enterzadajte funkciu ako vzorec poľa.
:
- Vymazanie všetkých prázdnych riadkov v tabuľke naraz pomocou jednoduchého makra
- Odstránenie všetkých prázdnych riadkov v pracovnom hárku naraz pomocou doplnku PLEX
- Rýchlo vyplňte všetky prázdne bunky
- Čo sú makrá, kam vložiť kód makra vo VBA