Pochopení buněk Excel vs. funkce rozsahu ve VBA

Excel je silný. Pokud ji používáte hodně, pravděpodobně už znáte spoustu triků pomocí vzorců nebo autoformátování, ale využíváte Buňky a Rozsah funkce ve VBA, můžete zvýšit své Excel analýzy na zcela novou úroveň.

Problém s použitím funkcí Cells and Range ve VBA spočívá v tom, že na pokročilé úrovni má většina lidí jen těžko pochopit, jak tyto funkce skutečně fungují. Jejich používání může být velmi matoucí. Zde je návod, jak je můžete využít způsoby, které jste si pravděpodobně nikdy nepředstavovali.

Funkce buněk

Funkce buněk a rozsahu vám řeknou skriptu VBA 4 chyby, kterým se můžete vyhnout při programování maker Excelu s VBA 4 chyby, kterým se můžete vyhnout při programování maker Excelu s VBA Jednoduché kódy a makra jsou klíčem k supervelmocím aplikace Microsoft Excel. Dokonce i neprogramátoři mohou snadno přidat působivé funkce do svých tabulek pomocí Virtual Basics for Applications (VBA). Jen se vyhněte těmto chybám programátorských začátečníků! přesně kde na listu chcete získat nebo umístit data. Hlavní rozdíl mezi těmito dvěma buňkami je to, na co odkazují.

Buňky obvykle odkazují na jednu buňku současně Rozsah odkazuje na skupinu buněk najednou. Formát této funkce je Buňky (řádek, sloupec).

Toto odkazuje na každou jednotlivou buňku v celém listu. Je to jeden příklad, kde funkce buněk neodkazuje na jednu buňku:

Pracovní listy („List1“)

Toto odkazuje na třetí buňku zleva v horním řádku. Buňka C1:

Pracovní listy ("List1"). Buňky (3)

Následující kód odkazuje na buňku D15:

Pracovní listy („List1“). Buňky (15,4)

Pokud jste chtěli, můžete také odkazovat na buňku D15 “Buňky (15,”D”)”-můžete použít písmeno ve sloupci.

Existuje velká flexibilita v možnosti odkazovat na buňku pomocí čísla pro sloupec a buňku, zejména se skripty, které to dokážou projít velké množství buněk (a provádět na nich výpočty) velmi rychle. K tomu se dostaneme podrobněji níže.

Funkce Range

V mnoha ohledech je funkce Range mnohem silnější než použití buněk, protože umožňuje odkazovat na jednu buňku nebo na určitý rozsah buněk najednou. Nebudete chtít procházet funkcí Range, protože odkazy na buňky nejsou čísla (pokud do ní nevložíte funkci Cells).

Formát této funkce je Range (Cell # 1, Cell # 2). Každá buňka může být označena písmenem.

Podívejme se na několik příkladů.

Funkce rozsahu zde odkazuje na buňku A5:

Pracovní listy („List1“). Rozsah („A5“)

Funkce rozsahu zde odkazuje na všechny buňky mezi A1 až E20:

Pracovní listy („List1“). Rozsah („A1: E20“)

Jak bylo uvedeno výše, nemusíte používat přiřazení číslicových buněk. Ve skutečnosti můžete použít dvě funkce buněk uvnitř funkce Range k identifikaci rozsahu na listu, například takto:

 S listy ("List1") .Range (.Cells (1, 1), _ .Cells (20, 5)) End with 

Výše uvedený kód odkazuje na stejný rozsah jako rozsah (“A1: E20”) funkce. Hodnota při použití je, že vám umožní psát kód, který dynamicky pracuje s rozsahy pomocí smyček. Jak smyčky do-while pracují v počítačovém programování Jak smyčky do-while pracují v počítačovém programování smyčky jsou jedním z prvních typů ovládacích prvků, které ' Naučím se programovat. Pravděpodobně víte něco o smyčkách a pro smyčky, ale co se dosáhne smyčky do-while? .

Nyní, když pochopíte, jak formátovat funkce buněk a rozsahů, pojďme se pustit do toho, jak můžete kreativně využít tyto funkce v kódu VBA.

Zpracování dat s funkcí buněk

Funkce buněk je nejužitečnější, pokud máte složitý vzorec, který chcete provádět ve více oblastech buněk. Tyto rozsahy mohou také existovat na více listech.

Vezměme si jednoduchý příklad. Řekněme, že řídíte prodejní tým 11 lidí a každý měsíc se chcete podívat na jejich výkon.

Možná jste List1 který sleduje jejich prodejní objem a objem jejich prodeje.

Na List2 je místo, kde sledujete jejich zpětnou vazbu od klientů vaší společnosti za posledních 30 dní.

Pokud chcete vypočítat bonus na prvním listu pomocí hodnot z obou listů, existuje několik způsobů, jak toho dosáhnout. Mohli byste napsat vzorec v první buňce, která provádí výpočet pomocí dat přes dva listy a přetáhnout jej dolů. To bude fungovat.

Alternativou je vytvoření skriptu VBA, který spouštíte při každém otevření listu, nebo je spuštěn příkazovým tlačítkem na listu, takže můžete řídit, kdy se má vypočítat. Můžete také použít skript VBA k načtení všech prodejních dat z externího souboru.

Proč tedy spouštět výpočty pro sloupec bonusů ve stejném skriptu v té době?

Buňky fungují v akci

Pokud jste v Excelu ještě nikdy nenapsali VBA, musíte povolit položku nabídky Vývojář. Chcete-li to provést, přejděte na Soubor > Možnosti. Klikněte na Přizpůsobte pás karet. Nakonec zvolte Developer v levém podokně, Přidat do pravého podokna a ujistěte se, že je zaškrtnuto políčko.

Nyní, když kliknete OK a přejděte zpět na hlavní list, zobrazí se možnost nabídky vývojáře.

Můžete použít Vložit Chcete-li vložit příkazové tlačítko, klikněte na Zobrazit kód začít kódování.

V tomto příkladu spustíme skript při každém otevření sešitu. Stačí kliknout na Zobrazit kód z nabídky vývojáře a vložte následující novou funkci do okna kódu.

Soukromý dílčí sešit_Open () End Sub

Vaše kódové okno bude vypadat asi takto.

Nyní jste připraveni napsat kód pro zpracování výpočtu. Pomocí jediné smyčky můžete projít všech 11 zaměstnanců a pomocí funkce Cells načíst tři proměnné potřebné pro výpočet.

Nezapomeňte, že funkce buněk má řádek a sloupec jako parametry pro identifikaci každé jednotlivé buňky. Vyrobíme “X” v řádku použijte číslo k vyžádání dat každého sloupce. Počet řádků je počet zaměstnanců, takže to bude od 1 do 11. Identifikátor sloupce bude 2 pro počet prodejů, 3 pro objem prodeje a 2 z listu 2 pro hodnocení zpětné vazby.

Konečný výpočet používá následující procenta k přidání až 100 procent z celkového bonusového skóre. Je založen na ideálním počtu prodejů 50, objemu prodeje 50 000 USD a skóre zpětné vazby 10.

• (Počet prodejů / 50) x 0,4
• (Objem prodeje / 50 000) x 0,5
• (Skóre zpětné vazby / 10) x 0,1

Tento jednoduchý přístup dává zaměstnancům prodeje vážený bonus. Za počet 50, objem 50 000 $ a skóre 10 získají celý maximální bonus za měsíc. Bonus však snižuje cokoli pod dokonalým faktorem. Bonus zvyšuje cokoli lepšího, než je ideální.

Nyní se podívejme, jak lze tuto logiku vytáhnout ve velmi jednoduchém krátkém skriptu VBA:

Soukromý dílčí sešit_Open () Pro x = 2 až 12 listů ("List1"). Buňky (x, 4) = (Pracovní listy ("List1"). Buňky (x, 2) .Hodnota / 50) * 0,4 _ + (Pracovní listy ("List1"). Buňky (x, 3) .Hodnota / 50000) * 0,5 _ + (Pracovní listy ("List2"). Buňky (x, 2) .Hodnota / 10) * 0,1 _ Další x Konec Sub 

Takto bude vypadat výstup tohoto skriptu.

Pokud byste chtěli, aby sloupec Bonus zobrazoval skutečný dolarový bonus spíše než procento, můžete jej vynásobit maximální částkou bonusu. Ještě lepší je umístit tuto částku do buňky na jiném listu a odkazovat na ni v kódu. To by usnadnilo pozdější změnu hodnoty bez nutnosti úpravy kódu.

Krása funkce buněk je v tom, že si můžete vytvořit docela kreativní logiku, ze které můžete stahovat data mnoho buněk napříč mnoha různými listy a provádět s nimi docela složité výpočty.

Pomocí funkce Buňky můžete provádět nejrůznější akce na buňkách, jako je čištění buněk, změna formátování písma a mnoho dalšího.

Chcete-li prozkoumat vše, co můžete dělat dále, podívejte se na stránku Microsoft MSDN pro objekt Cells.

Formátování buněk s funkcí rozsahu

Pro opakování mnoha buněk najednou je funkce buněk perfektní. Ale pokud chcete aplikovat něco na celou řadu buněk najednou, funkce Range je mnohem efektivnější.

Jedním případem použití může být formátování řady buněk pomocí skriptu, jsou-li splněny určité podmínky.

Řekněme například, že součet veškerého objemu prodeje napříč všemi zaměstnanci prodeje přesáhl celkem 400 000 $, chcete zvýraznit všechny buňky v bonusovém sloupci zeleně, což znamená, že tým získal extra týmový bonus.

Pojďme se podívat, jak to můžete udělat pomocí příkazu IF Jak používat příkazy IF v aplikaci Microsoft Excel Jak používat příkazy IF v aplikaci Microsoft Excel Ať už jste zkušený odborník nebo začátečník tabulky, měli byste se na to podívat průvodce příkazy IF v Excelu. .

Private Sub Workbook_Open () If Worksheet ("Sheet1"). Buňky (13, 3) .Value> 400000 Then ActiveSheet.Range ("D2: D12"). Interior.ColorIndex = 4 End If End Sub

Když to běží, pokud je buňka nad cílem týmu, budou všechny buňky v rozsahu vyplněny zeleně.

Toto je pouze jeden jednoduchý příklad mnoha akcí, které můžete provést na skupinách buněk pomocí funkce Range. Mezi další věci patří:

• Použijte obrys kolem skupiny
• Zkontrolujte pravopis textu uvnitř řady buněk
• Vymazání, kopírování nebo oříznutí buněk
• Prohledávejte rozsah pomocí “Nalézt” metoda
• Mnohem více

Nezapomeňte si přečíst stránku Microsoft MSDN pro objekt Range a zobrazit všechny možnosti.

Vezměte Excel na další úroveň

Nyní, když rozumíte rozdílům mezi buňkami a funkcemi Range, je čas pustit skriptování VBA na další úroveň. Dannův článek o používání funkcí počítání a přidávání v Excelu vám umožní vytvářet ještě pokročilejší skripty, které dokáží velmi rychle akumulovat hodnoty ve všech vašich datových sadách..

A pokud jste právě začínali s VBA v Excelu, nezapomeňte, že máme fantastického úvodního průvodce pro Excel VBA. Excel VBA Programovací výuka pro začátečníky Excel VBA Programovací výuka pro začátečníky VBA je nástroj Microsoft Office. Můžete jej použít k automatizaci úkolů pomocí maker, nastavení spouště a mnoho dalšího. Představíme vám programování jazyka Visual Basic s jednoduchým projektem. pro vás také.