Pochopení buněk aplikace Excel a funkcí rozsahu ve VBA

Pochopení buněk aplikace Excel a funkcí rozsahu ve VBA

Excel je mocný. Pokud to používáte hodně, pravděpodobně už znáte spoustu triků pomocí vzorců nebo automatického formátování, ale používání Buňky a Rozsah funkce ve VBA, můžete vylepšit analytiku Excelu na zcela novou úroveň.





Problém při používání funkcí buněk a rozsahu ve VBA spočívá v tom, že na pokročilých úrovních většina lidí těžko chápe, jak tyto funkce ve skutečnosti 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 Cells and Range vám to prozradí váš skript VBA přesně tam, kde na listu chcete získat nebo umístit data. Hlavní rozdíl mezi těmito dvěma buňkami je v tom, na co odkazují.





Buňky obvykle odkazují na jednu buňku najednou, zatímco Rozsah odkazuje na skupinu buněk najednou. Formát pro tuto funkci je Buňky (řádek, sloupec) .

Toto odkazuje na každou buňku v celém listu. Je to jeden příklad, kdy funkce Cells neodkazuje na jedinou buňku:



Worksheets('Sheet1').Cells

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

Worksheets('Sheet1').Cells(3)

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



Worksheets('Sheet1').Cells(15,4)

Pokud byste chtěli, můžete také odkazovat na buňku D15 pomocí 'Buňky (15,' D ')' --- můžete použít písmeno sloupce.

Existuje velká flexibilita v možnosti odkazovat na buňku pomocí čísla pro sloupec a buňku, zejména u skriptů, které mohou smyčka skrz velký počet buněk (a provádět na nich výpočty) velmi rychle. K tomu se dostaneme podrobněji níže.



Funkce rozsahu

Funkce Range je v mnoha ohledech mnohem výkonnější než používání buněk, protože vám umožňuje odkazovat buď na jednu buňku, nebo na konkrétní 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 pro tuto funkci je Rozsah (buňka č. 1, buňka č. 2) . Každá buňka může být označena číslem písmene.

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

jaký je rozdíl mezi televizorem a monitorem

Zde funkce rozsahu odkazuje na buňku A5:

Worksheets('Sheet1').Range('A5')

Zde funkce rozsahu odkazuje na všechny buňky mezi A1 až E20:

Worksheets('Sheet1').Range('A1:E20')

Jak bylo uvedeno výše, nemusíte používat přiřazení buněk s číslicemi. Ve skutečnosti byste mohli použít dvě funkce buněk uvnitř funkce Rozsah k identifikaci rozsahu na listu, například takto:


With Worksheets('Sheet1')
.Range(.Cells(1, 1), _
.Cells(20, 5))
End With

Výše uvedený kód odkazuje na stejný rozsah jako funkce Range ('A1: E20'). Hodnota při jeho používání spočívá v tom, že vám umožní psát kód, který dynamicky pracuje s rozsahy pomocí smyček.

Nyní, když rozumíte tomu, jak formátovat funkce buněk a rozsahu, pojďme se ponořit do toho, jak můžete tyto funkce kreativně využít v kódu VBA.

Zpracování dat s funkcí buněk

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

Ukaž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á máte List 1 který sleduje jejich počet prodejů a jejich objem prodeje.

Na List 2 je místo, kde sledujete jejich hodnocení zpětné vazby od klientů vaší společnosti za posledních 30 dní.

Pokud chcete vypočítat bonus na prvním listu pomocí hodnot z obou listů, můžete to provést několika způsoby. Do první buňky, která provádí výpočet pomocí dat přes dva listy, můžete napsat vzorec a přetáhnout jej dolů. To bude fungovat.

Alternativou k tomu je vytvoření skriptu VBA, který spustíte buď při každém otevření listu, nebo jej spustíte příkazovým tlačítkem na listu, abyste mohli ovládat, jak se počítá. K získání všech údajů o prodeji z externího souboru můžete stejně použít skript VBA.

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

Funkce buněk v akci

Pokud jste nikdy předtím nenapsali VBA v Excelu, budete muset 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 v levém podokně vyberte Vývojář, Přidat do pravého podokna a zkontrolujte, zda je zaškrtnuto políčko.

Nyní, když kliknete OK a vraťte se zpět na hlavní list, uvidíte možnost nabídky Vývojář.

Můžete použít Vložit nabídku pro vložení příkazového tlačítka, nebo jednoduše klikněte Zobrazit kód začít kódovat.

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

Private Sub Workbook_Open()
End Sub

Okno s kódem bude vypadat nějak takto.

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

Pamatujte, že funkce buněk má jako parametr k identifikaci každé jednotlivé buňky řádek a sloupec. Uděláme řádek 'x', pomocí čísla si vyžádáme data každého sloupce. Počet řádků je počet zaměstnanců, bude tedy od 1 do 11. Identifikátor sloupce bude 2 pro počet prodejů, 3 pro objem prodeje a 2 z listu 2 pro skóre zpětné vazby.

Konečný výpočet používá následující procenta k sečtení 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 $ 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 poskytuje prodejním zaměstnancům vážený bonus. Při počtu 50, objemu 50 000 $ a skóre 10 --- získají celý maximální bonus za měsíc. Cokoli pod dokonalým v jakémkoli faktoru však snižuje bonus. Cokoli lepšího než ideálního zvyšuje bonus.

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

Private Sub Workbook_Open()
For x = 2 To 12
Worksheets('Sheet1').Cells(x, 4) = (Worksheets('Sheet1').Cells(x, 2).Value / 50) * 0.4 _
+ (Worksheets('Sheet1').Cells(x, 3).Value / 50000) * 0.5 _
+ (Worksheets('Sheet2').Cells(x, 2).Value / 10) * 0.1 _
Next x
End Sub

Takto bude vypadat výstup tohoto skriptu.

co se dá dělat s malinovým pi

Pokud byste chtěli, aby sloupec Bonus zobrazoval skutečný dolarový bonus místo procenta, 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 Cells spočívá v tom, že si můžete vytvořit docela kreativní logiku, ze které můžete získávat data mnoho buněk napříč mnoha různými listy a některé provést docela složité výpočty s nimi.

S buňkami můžete provádět všechny druhy akcí pomocí funkce Buňky-například mazání buněk, změna formátování písma a mnoho dalšího.

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

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

Pro smyčku mnoha buňkami najednou je funkce Cells perfektní. Pokud ale chcete použít 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í rozsahu buněk pomocí skriptu, pokud jsou splněny určité podmínky.

Řekněme například, že pokud součet veškerého objemu prodeje u všech prodejních zaměstnanců přesáhne celkem 400 000 USD, chcete všechny buňky ve sloupci bonus zvýraznit zeleně, což znamená, že tým získal extra týmový bonus.

Pojďme se podívat na to, jak to můžete udělat prohlášení IF .

Private Sub Workbook_Open()
If Worksheets('Sheet1').Cells(13, 3).Value > 400000 Then
ActiveSheet.Range('D2:D12').Interior.ColorIndex = 4
End If
End Sub

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

Toto je jen jeden jednoduchý příklad z mnoha akcí, které můžete provádět se skupinami buněk pomocí funkce Rozsah. Mezi další věci, které můžete udělat, patří:

  • Okolo skupiny použijte obrys
  • Zkontrolujte pravopis textu uvnitř řady buněk
  • Vymažte, zkopírujte nebo vyjměte buňky
  • Procházejte rozsah pomocí metody „Najít“
  • Mnohem více

Určitě si přečtěte Stránka Microsoft MSDN aby objekt Range viděl všechny možnosti.

Posuňte Excel na další úroveň

Nyní, když chápete rozdíly mezi funkcemi buněk a rozsahu, je na čase posunout 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ážou velmi rychle akumulovat hodnoty napříč všemi vašimi datovými sadami.

A pokud s VBA v Excelu teprve začínáte, nezapomeňte, že máme fantastické úvodní průvodce Excel VBA i pro tebe.

Podíl Podíl tweet E-mailem Canon vs. Nikon: Která značka fotoaparátu je lepší?

Canon a Nikon jsou dvě největší jména v oboru fotoaparátů. Ale která značka nabízí lepší řadu fotoaparátů a objektivů?

Číst dále Související témata
  • Produktivita
  • Programování
  • Programování v jazyce Visual Basic
  • Microsoft Excel
O autorovi Ryan Dube(942 publikovaných článků)

Ryan má bakalářský titul z elektrotechniky. Pracoval 13 let v automatizačním inženýrství, 5 let v IT a nyní je Apps Engineer. Bývalý vedoucí redaktor MakeUseOf hovořil na národních konferencích o vizualizaci dat a vystupoval v národní televizi a rozhlase.

Více od Ryana Dubeho

Přihlaste se k odběru našeho zpravodaje

Připojte se k našemu zpravodaji a získejte technické tipy, recenze, bezplatné elektronické knihy a exkluzivní nabídky!

Kliknutím sem se přihlásíte k odběru