Razumijevanje Excel ćelija u odnosu na funkcije raspona u VBA

Razumijevanje Excel ćelija u odnosu na funkcije raspona u VBA

Excel je moćan. Ako ga često koristite, vjerojatno već znate mnogo trikova pomoću formula ili automatskog oblikovanja, ali koristeći Stanice i Domet funkcijama u VBA -u, možete poboljšati svoju analitiku programa Excel na potpuno novu razinu.





Problem s korištenjem funkcija Ćelije i raspon u VBA -i je taj što na naprednim razinama većina ljudi teško razumije kako te funkcije zapravo rade. Njihova upotreba može biti vrlo zbunjujuća. Evo kako ih možete koristiti na načine koje vjerojatno niste ni zamišljali.



Funkcija stanica

Funkcije ćelija i raspona omogućuju vam da kažete svoju VBA skriptu točno gdje na svom radnom listu želite dobiti ili postaviti podatke. Glavna razlika između dviju stanica je ono na što se pozivaju.





Stanice obično upućuju na jednu ćeliju odjednom, dok Domet upućuje na grupu stanica odjednom. Format ove funkcije je Ćelije (redak, stupac) .

Ovo se odnosi na svaku ćeliju na cijelom listu. To je jedan primjer gdje funkcija Ćelije ne upućuje na jednu ćeliju:



Worksheets('Sheet1').Cells

Ovo se odnosi na treću ćeliju s lijeve strane, gornjeg reda. Ćelija C1:

Worksheets('Sheet1').Cells(3)

Sljedeći kod upućuje na ćeliju D15:



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

Ako želite, možete također referencirati ćeliju D15 sa 'Ćelije (15,' D ')' --- dopušteno je koristiti slovo stupca.

Postoji velika fleksibilnost u mogućnosti pozivanja na ćeliju pomoću broja za stupac i ćeliju, posebno sa skriptama koje to mogu petlja kroz veliki broj ćelija (i izvršite proračune na njima) vrlo brzo. U nastavku ćemo do toga doći detaljnije.



Funkcija raspona

Na mnogo načina, funkcija Raspon je daleko moćnija od korištenja ćelija, jer vam omogućuje upućivanje na jednu ćeliju ili na određeni raspon ćelija, odjednom. Nećete se htjeti petljati kroz funkciju raspona jer reference za ćelije nisu brojevi (osim ako unutar njih ugradite funkciju Ćelije).

Format ove funkcije je Raspon (ćelija #1, ćelija #2) . Svaka ćelija može biti označena slovnim brojem.

Pogledajmo nekoliko primjera.

kako instalirati Windows 10 na USB

Ovdje se funkcija raspona odnosi na ćeliju A5:

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

Ovdje se funkcija raspona odnosi na sve ćelije između A1 do E20:

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

Kao što je gore spomenuto, ne morate koristiti dodjele ćelija s brojevima. Za identifikaciju raspona na listu možete koristiti dvije funkcije Ćelije unutar funkcije Raspon, poput ove:


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

Gornji kôd upućuje na isti raspon kao i funkcija Range ('A1: E20'). Vrijednost njegove uporabe je u tome što bi vam omogućilo pisanje koda koji dinamički radi s rasponima pomoću petlji.

Sada kada razumijete kako formatirati funkcije Ćelije i raspon, zaronimo u to kako možete kreativno koristiti te funkcije u svom VBA kodu.

Obrada podataka s funkcijom ćelija

Funkcija ćelija najkorisnija je kada imate složenu formulu koju želite izvesti u više raspona ćelija. Ovi rasponi također mogu postojati na više listova.

Uzmimo jednostavan primjer. Recimo da upravljate prodajnim timom od 11 ljudi i svaki mjesec želite pogledati njihovu izvedbu.

Možda ste imali List 1 koji prati njihov broj prodaje i obujam prodaje.

Na List 2 gdje možete pratiti njihove ocjene povratnih informacija od klijenata vaše tvrtke u posljednjih 30 dana.

Ako želite izračunati bonus na prvom listu koristeći vrijednosti iz dva lista, to možete učiniti na više načina. Možete unijeti formulu u prvu ćeliju koja izvodi izračun pomoću podataka na dva lista i povući je prema dolje. To će uspjeti.

Alternativa ovome je stvaranje VBA skripte koju pokrećete ili kad god otvorite list, ili je pokreće naredbeni gumb na listu kako biste mogli kontrolirati kada izračunava. Ionako biste mogli koristiti VBA skriptu za izvlačenje svih podataka o prodaji iz vanjske datoteke.

Zašto onda jednostavno ne pokrenete izračune stupca bonusa u istoj skripti u to vrijeme?

Funkcija stanica na djelu

Ako nikada prije niste napisali VBA u Excelu, morat ćete omogućiti stavku izbornika Developer. Da biste to učinili, idite na Datoteka > Opcije . Kliknite na Prilagodite vrpcu . Na kraju, odaberite Developer u lijevom oknu, Dodati u desno okno i provjerite je li potvrđen okvir.

Sada, kada kliknete u redu i vratite se na glavni list, vidjet ćete opciju izbornika Developer.

Možete koristiti Umetnuti izbornik za umetanje naredbenog gumba ili samo kliknite Prikaži kôd za početak kodiranja.

U ovom primjeru skriptu ćemo pokrenuti pri svakom otvaranju radne knjige. Da biste to učinili, samo kliknite Prikaži kôd s razvojnog izbornika i zalijepite sljedeću novu funkciju u prozor koda.

Private Sub Workbook_Open()
End Sub

Vaš kôd prozor će izgledati otprilike ovako.

Sada ste spremni za pisanje koda za rukovanje izračunom. Koristeći jednu petlju, možete proći kroz svih 11 zaposlenika, a pomoću funkcije Ćelije povucite tri varijable potrebne za izračun.

Zapamtite da funkcija Ćelije ima redak i stupac kao parametre za identifikaciju svake pojedinačne ćelije. Napravit ćemo 'x' red, upotrijebiti broj za traženje podataka svakog stupca. Broj redaka je broj zaposlenih, pa će to biti od 1 do 11. Identifikator stupca bit će 2 za broj prodaje, 3 za obujam prodaje i 2 iz lista 2 za ocjenu povratnih informacija.

Konačni izračun koristi sljedeće postotke za zbrajanje do 100 posto ukupnog bonusa. Temelji se na idealnom broju prodaje od 50, obujmu prodaje na 50.000 dolara i ocjeni povratnih informacija od 10.

  • (Broj prodaje/50) x 0,4
  • (Količina prodaje/50.000) x 0,5
  • (Ocjena povratne informacije/10) x 0,1

Ovaj jednostavan pristup zaposlenicima u prodaji daje ponderirani poen. Za brojanje 50, volumen od 50.000 USD i ocjenu 10 --- oni dobivaju cijeli maksimalni bonus za mjesec. Međutim, sve ispod savršenog po bilo kojem faktoru smanjuje bonus. Sve bolje od idealnog povećava bonus.

Pogledajmo sada kako se sva ta logika može izvući u vrlo jednostavnom, kratkom VBA skriptu:

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

Ovako će izgledati izlaz ove skripte.

što možete učiniti s pi od maline

Ako želite da stupac Bonus prikazuje stvarni dolar, a ne postotak, mogli biste ga pomnožiti s maksimalnim iznosom bonusa. Još bolje, stavite taj iznos u ćeliju na drugom listu i navedite to u svom kodu. To bi olakšalo kasnije promjenu vrijednosti bez potrebe za uređivanjem koda.

Ljepota funkcije ćelija je u tome što možete izgraditi prilično kreativnu logiku iz koje ćete izvući podatke mnogo stanica na mnogo različitih listova, a neke izvedite prilično složeni izračuni sa njima.

Pomoću funkcije Ćelije možete izvesti sve vrste radnji na ćelijama --- stvari poput brisanja ćelija, mijenjanja oblikovanja fonta i još mnogo toga.

Da biste istražili sve što možete učiniti dalje, provjerite Microsoft MSDN stranica za objekt Cells.

Formatiranje ćelija s funkcijom raspona

Za petlje kroz mnoge ćelije jednu po jednu, funkcija Ćelije je savršena. Ali ako želite primijeniti nešto na cijeli niz ćelija odjednom, funkcija Raspon je daleko učinkovitija.

Jedan od primjera za to može biti oblikovanje raspona ćelija pomoću skripte, ako su ispunjeni određeni uvjeti.

Na primjer, recimo da ako ukupan iznos ukupnog volumena prodaje svih zaposlenih u prodaji premaši ukupno 400.000 USD, želite istaknuti sve ćelije u stupcu bonusa zelenom bojom kako biste označili da je tim zaradio dodatni timski bonus.

Pogledajmo kako to možete učiniti IF izjava .

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

Kad se ovo pokrene, ako je ćelija iznad cilja tima, sve ćelije u rasponu biti će ispunjene zelenom bojom.

Ovo je samo jedan jednostavan primjer mnogih radnji koje možete izvršiti na grupama ćelija pomoću funkcije Raspon. Ostale stvari koje možete učiniti uključuju:

  • Nanesite obris oko grupe
  • Provjerite pravopis teksta unutar raspona ćelija
  • Očistite, kopirajte ili izrežite ćelije
  • Pretražujte raspon metodom 'Pronađi'
  • Mnogo više

Svakako pročitajte Microsoft MSDN stranica da objekt Range vidi sve mogućnosti.

Podignite Excel na sljedeću razinu

Sada kada razumijete razlike između funkcija Ćelije i raspon, vrijeme je da svoje VBA skripte podignete na višu razinu. Dannov članak o korištenju funkcija brojanja i dodavanja u Excelu omogućit će vam izgradnju još naprednijih skripti koje mogu vrlo brzo akumulirati vrijednosti u svim vašim skupovima podataka.

A ako tek počinjete s VBA -om u Excelu, ne zaboravite da imamo fantastičnu uvodni vodič za Excel VBA i za tebe.

Udio Udio Cvrkut E -pošta Canon protiv Nikona: Koja je marka fotoaparata bolja?

Canon i Nikon dva su najveća imena u industriji fotoaparata. No, koja marka nudi bolju ponudu kamera i objektiva?

Pročitajte Dalje Povezane teme
  • Produktivnost
  • Programiranje
  • Visual Basic programiranje
  • Microsoft Excel
O autoru Ryan Dube(942 objavljenih članaka)

Ryan ima diplomu elektrotehnike. Radio je 13 godina u automatizaciji, 5 godina u IT -u, a sada je inženjer aplikacija. Bivši glavni urednik MakeUseOf -a, govorio je na nacionalnim konferencijama o vizualizaciji podataka i bio je predstavljen na nacionalnoj televiziji i radiju.

Više od Ryana Dubea

Pretplatite se na naše obavijesti

Pridružite se našem biltenu za tehničke savjete, recenzije, besplatne e -knjige i ekskluzivne ponude!

Kliknite ovdje za pretplatu