Obsah
Formulace problému
Máme rozsah buněk s daty, které obsahují prázdné buňky:
Úkolem je odstranit prázdné buňky a ponechat pouze buňky s informacemi.
Metoda 1. Hrubá a rychlá
- Výběr původního rozsahu
- Stiskněte klávesu F5, tlačítko další Zdůraznit (Speciální). V okně, které se otevře, vyberte Prázdné buňky(Prázdné) a klepněte na tlačítko OK.
Vyberou se všechny prázdné buňky v rozsahu.
- Pro smazání vybraných buněk dáme v nabídce příkaz: kliknout pravým tlačítkem- Odstraňte buňky (Smazat buňky) s posunem nahoru.
Metoda 2: Vzorec pole
Pro zjednodušení pojmenujme naše pracovní rozsahy pomocí Správce jmen (správce jmen) Karta vzorec (vzorce) nebo v Excelu 2003 a starším menu Vložit – Název – Přiřadit (Vložit — Název — Definovat)
Pojmenujte rozsah B3:B10 HaveEmpty, rozsah D3:D10 – ŽádnéPrázdné. Rozsahy musí mít striktně stejnou velikost a mohou být umístěny kdekoli vůči sobě navzájem.
Nyní vyberte první buňku druhého rozsahu (D3) a zadejte do ní tento děsivý vzorec:
=IF(ŘÁDEK() -ŘÁDEK(NePrázdný)+1>NOTROWS(AnoPrázdný)-COUNTBLANK(AnoPrázdný);””;NEPŘÍMÉ(ADRESA(NEJNIŽŠÍ((POKUD(Prázdný<>””,ŘÁDEK(Prázdný);ŘÁDEK) + ŘÁDKY(Jsou prázdné))); ŘÁDEK()-ŘÁDEK(Ne prázdné)+1); SLOUPEK (Jsou prázdné); 4)))
V anglické verzi to bude:
=POKUD(ŘÁDEK()-ŘÁDEK(NePrázdný)+1>ŘÁDKY(Prázdný)-POČET PRÁZDNY(Prázdný),””,NEPŘÍMÝ(ADRESA(MALÝ((POKUD(Prázdný<>””,ŘÁDEK(Prázdný),ŘÁDEK)() +ROWS(HaveEmpty))),ROW()-ROW(NoEmpty)+1),COLUMN(HaveEmpty),4)))
Navíc musí být zadán jako maticový vzorec, tj. po vložení stiskněte vstoupit (jako obvykle) a Ctrl + Shift + Enter. Nyní lze vzorec zkopírovat pomocí automatického doplňování (přetažením černého křížku v pravém dolním rohu buňky) – a získáme původní rozsah, ale bez prázdných buněk:
Metoda 3. Vlastní funkce ve VBA
Pokud existuje podezření, že budete muset postup pro odstranění prázdných buněk z rozsahů často opakovat, pak je lepší do standardní sady jednou přidat vlastní funkci pro odstranění prázdných buněk a použít ji ve všech následujících případech.
Chcete-li to provést, otevřete Editor jazyka Visual Basic (ALT + F11), vložte nový prázdný modul (menu Vložit – Modul) a zkopírujte tam text této funkce:
Funkce 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 až MaxCells, 1 to 1) pro každý Rng v 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 Funkce
Nezapomeňte soubor uložit a přepnout zpět z editoru jazyka Visual Basic do Excelu. Použití této funkce v našem příkladu:
- Vyberte dostatečný rozsah prázdných buněk, například F3:F10.
- Přejděte do nabídky Vložit – Funkce (Vložit — Funkce)nebo klikněte na tlačítko Funkce vložení (funkce vložení) Karta vzorec (vzorce) v novějších verzích Excelu. V kategorii Definováno uživatelem (definováno uživatelem) vyberte naši funkci Žádné mezery.
- Určete zdrojový rozsah s mezerami (B3:B10) jako argument funkce a stiskněte Ctrl + Shift + Enterzadejte funkci jako maticový vzorec.
:
- Smazání všech prázdných řádků v tabulce najednou pomocí jednoduchého makra
- Odstranění všech prázdných řádků v listu najednou pomocí doplňku PLEX
- Rychle vyplňte všechny prázdné buňky
- Co jsou makra, kam vložit kód makra ve VBA