Pochopenie buniek programu Excel a funkcií rozsahu vo VBA

Pochopenie buniek programu Excel a funkcií rozsahu vo VBA

Excel je výkonný. Ak ho veľa používate, pravdepodobne už poznáte veľa trikov s použitím vzorcov alebo automatického formátovania, ale s využitím Bunky a Rozsah Vďaka funkciám vo VBA môžete analytiku Excelu vylepšiť na úplne novú úroveň.





Problém pri používaní funkcií Bunky a Rozsah vo VBA spočíva v tom, že na pokročilých úrovniach väčšina ľudí ťažko porozumie tomu, ako tieto funkcie skutočne fungujú. Ich používanie môže byť veľmi mätúce. Tu je návod, ako ich môžete využiť spôsobmi, ktoré ste si pravdepodobne nikdy nepredstavovali.



Funkcia buniek

Funkcie Bunky a Rozsah vám to povedia váš skript VBA presne tam, kde na pracovnom hárku chcete získať alebo umiestniť údaje. Hlavným rozdielom medzi týmito dvoma bunkami je to, na čo odkazujú.





Bunky zvyčajne odkazujú na jednu bunku naraz, pričom Rozsah odkazuje na skupinu buniek naraz. Formát pre túto funkciu je Bunky (riadok, stĺpec) .

Toto odkazuje na každú bunku v celom hárku. Je to jeden príklad, kde funkcia Bunky neodkazuje na jednu bunku:



Worksheets('Sheet1').Cells

Toto odkazuje na tretiu bunku zľava v hornom riadku. Bunka C1:

Worksheets('Sheet1').Cells(3)

Nasledujúci kód odkazuje na bunku D15:



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

Ak by ste chceli, môžete tiež odkazovať na bunku D15 pomocou „Bunky (15,„ D “)--môžete použiť písmeno stĺpca.

Schopnosť odkazovať na bunku pomocou čísla pre stĺpec a bunku je veľmi flexibilná, najmä v prípade skriptov, ktoré môžu pretočiť veľký počet buniek (a vykonávať na nich výpočty) veľmi rýchlo. Podrobnejšie sa k tomu dostaneme nižšie.



Funkcia rozsahu

V mnohých ohľadoch je funkcia Range oveľa výkonnejšia než používanie buniek, pretože vám umožňuje naraz odkazovať buď na jednu bunku, alebo na konkrétny rozsah buniek. Nebudete chcieť opakovať funkciu Range, pretože referencie na bunky nie sú čísla (pokiaľ do nej nevložíte funkciu Bunky).

Formát pre túto funkciu je Rozsah (bunka č. 1, bunka č. 2) . Každá bunka môže byť označená číslom písmena.

Pozrime sa na niekoľko príkladov.

ako zistím, či bol môj účet na Facebooku hacknutý

Tu funkcia rozsahu odkazuje na bunku A5:

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

Tu funkcia rozsahu odkazuje na všetky bunky medzi A1 až E20:

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

Ako bolo uvedené vyššie, nemusíte používať priradenia buniek s číselnými písmenami. Na identifikáciu rozsahu na hárku by ste v skutočnosti mohli použiť dve funkcie buniek vo funkcii rozsahu:


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

Vyššie uvedený kód odkazuje na rovnaký rozsah ako funkcia Range ('A1: E20'). Hodnota pri jeho použití je, že vám umožní napísať kód, ktorý dynamicky pracuje s rozsahmi pomocou slučiek.

Teraz, keď chápete, ako formátovať funkcie buniek a rozsahu, poďme sa ponoriť do toho, ako môžete tieto funkcie vo svojom kóde VBA kreatívne využiť.

Spracovanie údajov s funkciou buniek

Funkcia Bunky je najužitočnejšia, ak máte zložitý vzorec, ktorý chcete vykonávať vo viacerých rozsahoch buniek. Tieto rozsahy môžu existovať aj vo viacerých listoch.

Zoberme si jednoduchý príklad. Povedzme, že riadite predajný tím s 11 ľuďmi a každý mesiac sa chcete pozrieť na ich výkon.

Môžete mať List 1 ktorá sleduje ich počet predajov a objem ich predaja.

Zapnuté List 2 je miesto, kde sledujete hodnotenie ich spätnej väzby od klientov vašej spoločnosti za posledných 30 dní.

Ak chcete vypočítať bonus na prvom hárku pomocou hodnôt z dvoch hárkov, môžete to urobiť niekoľkými spôsobmi. Do prvej bunky, ktorá vykonáva výpočet, môžete zapísať vzorec pomocou údajov cez dva listy a presunúť ju nadol. To bude fungovať.

Alternatívou k tomu je vytvorenie skriptu VBA, ktorý spustíte buď pri otvorení hárka, alebo sa spustí pomocou príkazového tlačidla na hárku, aby ste mohli ovládať jeho výpočet. Na získanie všetkých údajov o predaji z externého súboru môžete aj tak použiť skript VBA.

Prečo teda v tom čase nespustíte výpočty pre bonusový stĺpec v rovnakom skripte?

Bunky fungujú v akcii

Ak ste nikdy predtým nepísali VBA v Exceli, budete musieť povoliť položku ponuky Vývojár. Ak to chcete urobiť, prejdite na stránku Súbor > možnosti . Kliknite na Prispôsobte stuhu . Nakoniec na ľavom paneli vyberte položku Vývojár Pridať na pravom paneli a uistite sa, že je začiarknuté políčko.

Teraz, keď kliknete OK a vráťte sa na hlavný list, zobrazí sa možnosť ponuky Vývojár.

Môžete použiť Vložiť ponuku, ak chcete vložiť príkazové tlačidlo, alebo jednoducho kliknite Zobraziť kód začať kódovať.

V tomto prípade spustíme skript pri každom otvorení zošita. Ak to chcete urobiť, stačí kliknúť Zobraziť kód z ponuky pre vývojárov a do okna kódu prilepte nasledujúcu novú funkciu.

Private Sub Workbook_Open()
End Sub

Okno s kódom bude vyzerať asi takto.

Teraz ste pripravení napísať kód na spracovanie výpočtu. Pomocou jednej slučky môžete prejsť všetkými 11 zamestnancami a pomocou funkcie Bunky získate tri premenné potrebné na výpočet.

Nezabudnite, že funkcia Bunky má ako parameter na identifikáciu každej jednotlivej bunky riadok a stĺpec. Vytvoríme riadok „x“, na vyžiadanie údajov každého stĺpca použijeme číslo. Počet riadkov je počet zamestnancov, takže bude od 1 do 11. Identifikátor stĺpca bude 2 pre počet predajov, 3 pre objem predaja a 2 z listu 2 pre skóre spätnej väzby.

Pri konečnom výpočte sa pomocou nasledujúcich percentuálnych podielov sčítava až 100 percent z celkového bonusového skóre. Vychádza z ideálneho počtu predajov, ktoré sú 50, objemu predaja 50 000 dolárov a skóre spätnej väzby 10.

  • (Predajný počet/50) x 0,4
  • (Objem predaja/50 000) x 0,5
  • (Skóre spätnej väzby/10) x 0,1

Tento jednoduchý prístup poskytuje zamestnancom predaja vážený bonus. Pri počte 50, objeme 50 000 dolárov a skóre 10-získajú celý maximálny bonus za mesiac. Čokoľvek, čo je v každom faktore dokonalé, znižuje bonus. Čokoľvek lepšie ako ideálne zvyšuje bonus.

Teraz sa pozrime, ako je možné celú túto logiku odvodiť vo veľmi jednoduchom, krátkom skripte 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 vyzerať výstup tohto skriptu.

koľko sledovateľov na tiktoku bude zverejnených

Ak by ste chceli, aby stĺpec Bonus zobrazoval skutočný dolárový bonus namiesto percenta, mohli by ste ho vynásobiť maximálnou sumou bonusu. Ešte lepšie je umiestniť túto čiastku do bunky na inom hárku a odkazovať na ňu vo svojom kóde. To uľahčí neskoršiu zmenu hodnoty bez toho, aby ste museli upravovať kód.

Krása funkcie Bunky spočíva v tom, že môžete vytvoriť celkom kreatívnu logiku, z ktorej môžete čerpať údaje veľa buniek v mnohých rôznych listoch a niektoré vykonajte dosť zložité výpočty s nimi.

S bunkami môžete vykonávať najrôznejšie akcie pomocou funkcie Bunky-napríklad vymazanie buniek, zmena formátovania písma a ďalšie.

Ak chcete preskúmať všetko, čo môžete urobiť ďalej, pozrite sa na Stránka Microsoft MSDN pre objekt Bunky.

Formátovanie buniek s funkciou rozsahu

Na cyklické prechádzanie mnohými bunkami naraz je funkcia Bunky perfektná. Ak však chcete naraz použiť niečo na celý rozsah buniek, funkcia Range je oveľa efektívnejšia.

Jedným z príkladov použitia môže byť formátovanie rozsahu buniek pomocou skriptu, ak sú splnené určité podmienky.

Povedzme napríklad, že ak súčet všetkých objemov predaja u všetkých predajných zamestnancov prekročí 400 000 dolárov, chcete zvýrazniť všetky bunky v stĺpci bonusov zelenou farbou, čo znamená, že tím získal extra tímový bonus.

Pozrime sa, ako to môžete dosiahnuť vyhlásenie 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

Keď sa to spustí, ak je bunka nad cieľom tímu, všetky bunky v rozsahu sa vyplnia zelenou farbou.

Toto je len jeden jednoduchý príklad z mnohých akcií, ktoré môžete vykonať so skupinami buniek pomocou funkcie Rozsah. Medzi ďalšie veci, ktoré môžete urobiť, patrí:

  • Aplikujte obrys okolo skupiny
  • Skontrolujte pravopis textu v rozsahu buniek
  • Vymazať, kopírovať alebo vystrihnúť bunky
  • Hľadajte v rozsahu pomocou metódy „Nájsť“
  • Oveľa viac

Nezabudnite si prečítať Stránka Microsoft MSDN aby objekt Range videl všetky možnosti.

Posuňte Excel na ďalšiu úroveň

Teraz, keď chápete rozdiely medzi funkciami buniek a rozsahu, je načase posunúť vaše skriptovanie VBA na ďalšiu úroveň. Dannov článok o používaní funkcií počítania a pridávania v programe Excel vám umožní vytvoriť ešte pokročilejšie skripty, ktoré dokážu veľmi rýchlo akumulovať hodnoty vo všetkých vašich množinách údajov.

A ak ešte len začínate s VBA v Exceli, nezabudnite, že sa máme fantasticky úvodný sprievodca programom Excel VBA aj pre teba.

zdieľam zdieľam Tweet E -mail Canon vs. Nikon: Ktorá značka fotoaparátu je lepšia?

Canon a Nikon sú dve najväčšie mená v kamerovom priemysle. Ale ktorá značka ponúka lepšiu radu fotoaparátov a objektívov?

Čítajte ďalej Súvisiace témy
  • Produktivita
  • Programovanie
  • Programovanie v jazyku Visual Basic
  • Microsoft Excel
O autorovi Ryan Dube(942 publikovaných článkov)

Ryan má bakalársky titul z elektrotechniky. Pracoval 13 rokov v automatizačnom inžinierstve, 5 rokov v IT a teraz je Apps Engineer. Bývalý vedúci redaktor MakeUseOf vystupoval na národných konferenciách o vizualizácii údajov a bol vystupovaný v národnej televízii a rozhlase.

Viac od Ryana Dubeho

prihlásiť sa ku odberu noviniek

Pripojte sa k nášmu bulletinu a získajte technické tipy, recenzie, bezplatné elektronické knihy a exkluzívne ponuky!

Kliknutím sem sa prihlásite na odber