Razumijevanje Excel ćelija i raspona funkcija u VBA

Excel je moćan. Ako ga puno koristite, vjerojatno već znate puno trikova pomoću formula ili automatskog formatiranja, ali koristeći Stanice i opseg funkcija u VBA, možete poboljšati Excel analitiku na potpuno novu razinu.

Problem s korištenjem funkcija Stanica i Raspon u VBA je taj što na naprednim razinama većina ljudi teško razumije kako ove funkcije zapravo rade. Njihova upotreba može postati vrlo zbunjujuća. Evo kako ih možete koristiti na načine na koje vjerojatno nikad niste ni zamislili.

Funkcija stanica

Funkcije ćelija i raspona omogućuju vam da ispričate svoju VBA skriptu 4 greške koje možete izbjeći kad programirate Excel makronaredbe s VBA 4 greške koje možete izbjeći pri programiranju Excel makronaredbi s VBA Jednostavni kôd i makronaredbe ključevi su Microsoft Excel supersila. Čak i neprogrameri mogu lako dodati impresivne funkcionalnosti u svoje proračunske tablice pomoću Virtual Basics for Applications (VBA). Samo izbjegavajte ove početničke pogreške u programiranju! točno tamo gdje na vašem radnom listu želite dobiti ili smjestiti podatke. Glavna razlika između dviju ćelija je u onome na što se pozivaju.

Stanice obično upućuju na jednu ćeliju odjednom, dok opseg odobrava odjednom skupinu stanica. Format ove funkcije je Stanice (red, stupac).

To se odnosi na svaku ćeliju na cijelom listu. To je jedini primjer gdje funkcija Stanice ne odnosi se na jednu ćeliju:

Listova ( "SHEET1"). Ćelijama

To se odnosi na treću ćeliju s gornjeg reda. Ćelija C1:

Listova ( "SHEET1") (. 3) Stanice

Sljedeći kod referira na ćeliju D15:

Listova ( "SHEET1"). Stanice (15.4)

Ako želite, možete se obratiti i ćeliji D15 “Stanice (15,”D”)”-dopušteno vam je korištenje slova stupca.

Postoji velika fleksibilnost u mogućnosti referenciranja stanice pomoću broja za stupac i ćeliju, posebno sa skriptama koje mogu petlja kroz veliki broj ćelija (i vrše izračune na njima) vrlo brzo. Do toga ćemo detaljnije doći u nastavku.

Funkcija raspona

U mnogočemu, funkcija Range daleko je moćnija od korištenja ćelija jer vam omogućuje isticanje bilo jedne ćelije, bilo određenog raspona ćelija, sve odjednom. Nećete se htjeti kretati kroz opseg, jer reference za ćelije nisu brojevi (osim ako unutar nje ugrađujete funkciju Stanice).

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

Pogledajmo nekoliko primjera.

Ovdje funkcija raspona referencira stanicu A5:

Radni listovi ( "Sheet1"). Raspon ( "A5")

Ovdje funkcija raspona referencira sve stanice između A1 do E20:

Radni listovi ( "Sheet1") raspona. ( "A1: E20")

Kao što je gore spomenuto, ne morate koristiti dodjele ćelija s brojevima. Zapravo biste mogli upotrijebiti dvije ćelijske funkcije unutar funkcije Range da biste identificirali raspon na listu, ovako:

 Sa Radnim listovima ("Sheet1") .Range (.Cells (1, 1), _ .Cells (20, 5)) End with 

Gore navedeni kôd odnosi se na isti raspon kao i raspon (“A1: E20”) funkcija radi. Vrijednost njegove upotrebe je što će vam omogućiti da napišete kôd koji dinamički radi s rasponima koristeći petlje. Kako petlje dok traje, dok rade u računalnom programiranju Kako petlje rade dok petlje rade u petlji za programiranje računala jedna su od prvih vrsta kontrole koju imate Naučit ću se u programiranju. Vjerojatno znate za dok i za petlje, ali o čemu se radi petlja dok ne radite? .

Sada kada razumijete kako formatirati ćelije i raspon funkcija, zavirimo u način na koji kreativno možete koristiti ove funkcije u svom VBA kodu.

Obrada podataka s funkcijom stanica

Funkcija Stanice najkorisnija je kada imate složenu formulu koju želite izvesti u više raspona ćelija. Ti rasponi mogu postojati i na više listova.

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

Možda bi Sheet1 koji prati njihov broj prodaje i njihovu količinu prodaje.

Na Sheet2 je mjesto na kojem pratite njihovu ocjenu za zadnjih 30 dana od klijenata vaše tvrtke.

Ako želite izračunati bonus na prvom listu koristeći vrijednosti iz dva lista, postoji više načina da se to postigne. Mogli biste napisati formulu u prvu ćeliju koja izračunava pomoću podataka na dva lista i povucite je prema dolje. To će uspjeti.

Alternativa ovome je stvaranje VBA skripte koju ili pokrećete da se pokrene kad god otvorite list ili vas pokrene naredbeni gumb na listu kako biste mogli kontrolirati kada izračunava. Možete upotrebljavati VBA skriptu da ionako izvučete sve podatke o prodaji iz vanjske datoteke.

Pa zašto onda ne pokrenuti izračune za bonus stupac u istoj skripti u to vrijeme?

Stanica funkcionira

Ako nikada prije niste pisali VBA u Excelu, morat ćete omogućiti stavku izbornika za razvojne programere. Da biste to učinili, idite na Datoteka > Opcije. Kliknite na Prilagodite vrpcu. Na kraju odaberite Developer s lijevog okna, Dodati na desno okno i provjerite je li potvrdni okvir.

Sada, kad kliknete u redu i vratite se na glavni list, vidjet ćete opciju izbornika za razvojne programere.

Možete koristiti Umetnuti izbornik za umetanje naredbenog gumba ili samo kliknite Pogledaj kod za početak kodiranja.

U ovom ćemo primjeru pokrenuti skriptu svaki put kada se radna knjiga otvori. Da biste to učinili, samo kliknite Pogledaj kod iz izbornika programera i zalijepite sljedeću novu funkciju u prozor koda.

Private Sub Workbook_Open () Završetak Sub

Prozor vašeg koda izgledat će ovako.

Sada ste spremni napisati kôd za obračun s računom. Korištenjem jedne petlje možete proći kroz svih 11 zaposlenika i pomoću funkcije Cells povući tri varijable potrebne za proračun.

Zapamtite da funkcija Stanice ima red i stupac kao parametre za prepoznavanje svake pojedinačne ćelije. Mi ćemo napraviti “x” retka, upotrijebite broj da biste zatražili podatke svakog stupca. Broj redova je broj zaposlenih, tako da će to biti od 1 do 11. Identifikator stupaca bit će 2 za broj prodaje, 3 za volumen prodaje i 2 za listu 2 za ocjenu povratnih informacija.

Konačni izračun koristite sljedeće postotke za dodavanje do 100 posto ukupnog bonusa. Temelji se na idealnom broju prodaje od 50, količini prodaje 50 000 dolara i ocjeni povratnih informacija 10.

• (Broj prodaje / 50) x 0,4
• (Količina prodaje / 50 000) x 0,5
• (Ocjena povratnih informacija / 10) x 0,1

Ovaj jednostavan pristup zaposlenima u prodaji daje ponderirani bonus. Za broj od 50, volumen od 50 000 dolara i ocjenu 10 - dobivaju cijeli maksimalni bonus za mjesec dana. Međutim, ništa u skladu sa bilo kojim čimbenikom smanjuje bonus. Sve što je bolje od idealnog povećava bonus.

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

Private Sub Workbook_Open () Za x = 2 do 12 radnih listova ("Sheet1"). Stanice (x, 4) = (Radni listovi ("Sheet1"). Stanice (x, 2) .Value / 50) * 0,4 _ + (Radni listovi ("Sheet1"). Ćelije (x, 3) .Value / 50000) * 0.5 _ + (Radni listovi ("Sheet2"). Stanice (x, 2) .Value / 10) * 0.1 _ Sljedeći x Kraj Sub 

Ovako će izgledati ishod ove skripte.

Ako želite da stupac Bonus prikazuje stvarni bonus dolara, a ne postotak, možete ga pomnožiti s maksimalnim iznosom bonusa. Još bolje, stavite taj iznos u ćeliju na drugi list i navedite ga u svom kodu. To će olakšati naknadnu promjenu vrijednosti bez potrebe za uređivanjem koda.

Ljepota funkcije Stanice je u tome što možete izgraditi prilično kreativnu logiku iz koje izvlačite podatke mnoge ćelije na raznim listovima i s njima izvodite prilično složene proračune.

Možete izvoditi sve vrste radnji na stanicama pomoću funkcije Cells - stvari poput čišćenja ćelija, promjena formatiranja slova i još mnogo toga.

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

Formatiranje ćelija s rasponom funkcije

Za pregledavanje više stanica po jednu, funkcija Stanice je savršena. Ali ako želite primijeniti nešto na čitav niz ćelija odjednom, funkcija Range daleko je učinkovitija.

Jedan slučaj upotrebe za ovo može biti oblikovanje raspona ćelija pomoću skripte, ako su ispunjeni određeni uvjeti.

Na primjer, recimo ako ukupni iznos prodaje svih zaposlenika prodaje premašuje ukupno 400.000 USD, želite sve stanice u bonus stupcu označiti zelenom bojom, što znači da je tim zaradio dodatni bonus za tim.

Pogledajmo kako to možete učiniti s IF izjavom Kako koristiti IF izjave u programu Microsoft Excel Kako koristiti IF izjave u programu Microsoft Excel Bez obzira jeste li iskusni stručnjak ili početnik proračunske tablice, morat ćete to provjeriti Vodič za IF izjave u Excelu. .

Private Sub Workbook_Open () Ako su radni listovi ("Sheet1"). Ćelije (13, 3) .Value> 400000 Onda ActiveSheet.Range ("D2: D12"). Interijer.ColorIndex = 4 Kraj ako Kraj Sub

Kad se ovo pokrene, ako ćelija pređe cilj tima, sve ćelije u rasponu biti će ispunjene zelenom bojom.

Ovo je samo jedan jednostavan primjer mnogih radnji koje možete izvesti na skupinama ćelija pomoću funkcije Raspon. Ostale stvari koje možete učiniti su:

• Primijenite obris oko grupe
• Provjerite pravopis teksta u rasponu ćelija
• Očistite, kopirajte ili izrežite stanice
• Pretražite niz s rasponom “Pronaći” način
• Mnogo više

Provjerite Microsoft MSDN stranicu za objekt Range da biste vidjeli sve mogućnosti.

Podnesite Excel na sljedeću razinu

Sada kada razumijete razlike između funkcija Cells i Range, vrijeme je da svoj VBA scenarij prebacite na sljedeću razinu. Dannov članak o korištenju brojanja i dodavanja funkcija u Excelu omogućit će vam izradu još naprednijih skripti koje mogu vrlo brzo sakupljati vrijednosti u svim vašim podacima.

A ako tek započinjete s VBA u Excelu, ne zaboravite da imamo fantastičan uvodni vodič za Excel VBA. Vodič za programiranje programa Excel VBA za početnike Programski vodič za Excel VBA za početnike VBA je električni alat za Microsoft Office. Možete ga koristiti za automatizaciju zadataka s makronaredbama, postavljanje okidača i puno više. Upoznat ćemo vas s Excel vizualnim osnovnim programiranjem jednostavnim projektom. i za vas.