Înțelegerea celulelor Excel față de funcțiile de domeniu în VBA

Excel este puternic. Dacă o folosiți foarte mult, probabil că deja cunoașteți multe trucuri folosind formule sau autoformatări, dar folosiți celulele și Gamă funcții în VBA, puteți crește analiza Excel la un nivel complet nou.

Problema cu utilizarea funcțiilor celule și a gamei în VBA este că la nivelurile avansate, majoritatea oamenilor au dificultăți în a înțelege cum funcționează aceste funcții. Folosirea lor poate deveni foarte confuză. Iată cum puteți să le folosiți în moduri pe care probabil nu le-ați imaginat niciodată.

Funcția de celule

Funcțiile de celule și gama vă permit să spuneți scriptul dvs. VBA 4 Greseli pe care le puteți evita atunci când programați macro-uri Excel cu VBA 4 greșeli pe care le puteți evita atunci când programați macro-uri Excel cu VBA Codul simplu și macrocomenzile sunt cheile pentru superputerele Microsoft Excel. Chiar și non-programatorii pot adăuga cu ușurință funcționalități impresionante la foile lor de calcul cu Virtual Basics for Applications (VBA). Doar evitați aceste greșeli de programare începător! Citiți mai exact unde vă aflați pe foaia de lucru pe care doriți să o obțineți sau plasați datele. Principala diferență dintre cele două celule este ceea ce se referă.

celulele de obicei, trimiteți o singură celulă la un moment dat, în timp ce Gamă referă la un grup de celule simultan. Formatul pentru această funcție este Celule (rând, coloană).

Aceasta se referă la fiecare celulă din întreaga coală. Este un exemplu în care funcția celule nu face referire la o singură celulă:

Worksheets ( "Sheet1"). Celule

Aceasta trimite referință la a treia celulă din stânga, din rândul de sus. Celula C1:

Worksheets ( "Sheet1"). Celulele (3)

Următoarele coduri de referință ale celulei D15:

Worksheets ( "Sheet1"). Celulele () 15,4

Dacă ați fi vrut, puteți face referire și la celula D15 “Celulele (15,”D”)”-vi se permite să utilizați litera de coloană.

Există o mulțime de flexibilitate în a putea trimite o celulă folosind un număr pentru coloană și celulă, mai ales cu scripturi care pot prin buclă un număr mare de celule (și efectuați calcule pe ele) foarte repede. Vom ajunge la acest lucru mai detaliat mai jos.

Funcția de interval

În multe moduri, funcția Range este mult mai puternică decât utilizarea celulelor, deoarece vă permite să trimiteți fie o singură celulă, fie o anumită gamă de celule, toate simultan. Nu doriți să treceți prin intermediul unei funcții Range, deoarece referințele pentru celule nu sunt numere (cu excepția cazului în care încorporați funcția Cells din interiorul acesteia).

Formatul pentru această funcție este Raza (celula # 1, celula 2). Fiecare celulă poate fi desemnată printr-un număr de literă.

Să ne uităm la câteva exemple.

Aici, funcția de domeniu este referindu-se la celula A5:

Foi de lucru ( "Sheet1"). Range ( "A5")

Aici, funcția range este referința tuturor celulelor între A1 și E20:

Foi de lucru ( "Sheet1") Range. ( "A1: E20")

După cum sa menționat mai sus, nu este necesar să utilizați asignări de celule numerice. Ați putea folosi de fapt două funcții de celule în interiorul unei funcții Range (Gamă) pentru a identifica o gamă pe foaie, astfel:

 Cu foile de lucru ("Sheet1") .Range (.Cells (1, 1), _ .Cells (20, 5) 

Codul de mai sus se referă la același interval ca și intervalul (“A1: E20”). Valoarea de utilizare a acestuia este că vă permite să scrieți un cod care funcționează dinamic cu intervale folosind buclele. Cum funcționează în timp ce lucrează în programarea calculatoarelor Cum funcționează în timp ce circuitele funcționează în programarea calculatoarelor Buclele sunt unul dintre primele tipuri de control pe care le- Voi învăța în programare. Probabil știți despre timp și pentru bucle, dar ce realizează o buclă de timp? Citeste mai mult .

Acum, că înțelegeți cum să formatați funcțiile celule și gama, să ne aruncăm asupra modului în care puteți utiliza în mod creativ aceste funcții în codul dvs. VBA.

Procesarea datelor cu funcția celulelor

Funcția Celule este cea mai utilă atunci când aveți o formulă complexă pe care doriți să o efectuați pe mai multe game de celule. Aceste intervale pot exista și în mai multe coli.

Să luăm un exemplu simplu. Să presupunem că gestionați o echipă de vânzări de 11 persoane, iar în fiecare lună doriți să vă uitați la performanța lor.

Ai putea avea Foaia1 care urmărește numărul de vânzări și volumul vânzărilor.

Pe Foaie2 este locul în care vă urmăriți evaluarea feedback-ului pentru ultimele 30 de zile de la clienții companiei dvs..

Dacă doriți să calculați bonusul din prima foaie utilizând valorile din cele două coli, există mai multe moduri de a face acest lucru. S-ar putea să scrieți o formulă în prima celulă care efectuează calculul utilizând datele din cele două foi și trageți-o în jos. Asta va funcționa.

O alternativă la acest lucru este crearea scriptului VBA pe care îl declanșați să îl executați ori de câte ori deschideți foaia sau declanșat de un buton de comandă de pe foaie, astfel încât să puteți controla când se calculează. S-ar putea să utilizați un script VBA pentru a trage toate datele de vânzare dintr-un fișier extern oricum.

Deci, de ce nu doar să declanșați calculele pentru coloana bonus în același script la acel moment?

Funcția celulelor în acțiune

Dacă nu ați scris niciodată VBA în Excel înainte, va trebui să activați elementul din meniul Developer. Pentru a face acest lucru, du-te la Fişier > Opțiuni. Click pe Personalizați panglica. În cele din urmă, alegeți Dezvoltator din panoul din stânga, Adăuga în panoul din dreapta și asigurați-vă că este bifată caseta de selectare.

Acum, când faceți clic O.K și reveniți la foaia principală, veți vedea opțiunea de meniu pentru dezvoltatori.

Puteți utiliza funcția Introduce meniu pentru a insera un buton de comandă sau doar faceți clic pe Afișați codul pentru a începe codarea.

În acest exemplu, vom face scriptul să ruleze de fiecare dată când se deschide registrul de lucru. Pentru a face acest lucru, faceți clic pe Afișați codul din meniul dezvoltatorului și inserați următoarea funcție nouă în fereastra de cod.

Private Sub Workbook_Open () End Sub

Fereastra de cod va arata cam asa.

Acum sunteți gata să scrieți codul pentru a face față calculului. Folosind o singură bucla, puteți trece prin toți cei 11 angajați și cu funcția Cells trageți cele trei variabile necesare pentru calcul.

Amintiți-vă că funcția celule are rând și coloană ca parametri pentru a identifica fiecare celulă individuală. Vom face “X” rândul, utilizați un număr pentru a solicita datele fiecărei coloane. Numărul de rânduri este numărul de angajați, deci acesta va fi de la 1 la 11. Identificatorul coloanei va fi 2 pentru numărul de vânzări, 3 pentru volumul vânzărilor și 2 pentru foaia de feedback 2.

Calculul final utilizează următoarele procente pentru a adăuga până la 100% din scorul bonusului total. Se bazează pe un număr de vânzări ideale de 50, volumul vânzărilor fiind de 50.000 $ și un scor de feedback de 10.

• (Numărul de vânzări / 50) x 0,4
• (Volum vânzări / 50.000) x 0.5
• (Scor de feedback / 10) x 0.1

Această abordare simplă oferă angajaților de vânzări un bonus ponderat. Pentru un număr de 50, un volum de 50.000 $ și un scor de 10 - primesc întregul bonus maxim pentru lună. Cu toate acestea, orice lucru perfect sub orice factor reduce bonusul. Orice mai bun decât ideal crește bonusul.

Acum, să vedem cum poate fi trasă toată această logică într-un script VBA foarte simplu, scurt:

(X, 2) .Value / 50) * 0.4 _ + (foi de lucru ("Sheet1") Celule (x, 3) .Value / 50000) * 0.5 _ + (Foile de lucru (Sheet2). 

Acesta va fi rezultatul acestui script.

Dacă doriți să aveți coloana bonusului să afișeze bonusul actual de dolar și nu procentul, puteți să îl multiplicați cu suma bonusului maxim. Mai bine, plasați această sumă într-o celulă dintr-o altă foaie și trimiteți-o în codul dvs. Acest lucru ar ușura modificarea ulterioară a valorii fără a fi necesară modificarea codului.

Frumusețea funcției celule este că puteți construi o logică destul de creativă pentru a trage din date multe celule în multe foi de diferență și să efectuați niște calcule destul de complexe cu ele.

Puteți efectua tot felul de acțiuni pe celule utilizând funcția Celule - lucruri precum curățarea celulelor, modificarea formatării fonturilor și multe altele.

Pentru a explora tot ce puteți face mai departe, verificați pagina Microsoft MSDN pentru obiectul Cells.

Formatarea celulelor cu funcție de interval

Pentru looping prin mai multe celule una câte una, funcția Cells este perfectă. Dar dacă doriți să aplicați ceva într-o gamă întreagă de celule simultan, funcția Range este mult mai eficientă.

Un caz de utilizare pentru acest lucru ar putea fi formatarea unei serii de celule folosind script-ul, dacă sunt îndeplinite anumite condiții.

De exemplu, să presupunem că, dacă numărul total al vânzărilor din toți angajații de vânzări depășește 400.000 $ în total, doriți să evidențiați toate celulele din coloana de bonus verde pentru a indica faptul că echipa a câștigat un bonus suplimentar pentru echipă.

Să aruncăm o privire la modul în care puteți face acest lucru cu o instrucțiune IF Cum să utilizați instrucțiunile IF în Microsoft Excel Cum să utilizați instrucțiunile IF în Microsoft Excel Indiferent dacă sunteți expert expert sau începător de foaie de calcul, veți dori să verificați acest lucru ghidul la instrucțiunile IF din Excel. Citeste mai mult .

În cazul în care se utilizează fișiere de lucru ("Sheet1"), celulele (13, 3) .Value> 400000 Atunci ActiveSheet.Range ("D2: D12") Interior.ColorIndex =

Când aceasta se execută, dacă celula este peste obiectivul echipei, toate celulele din interval vor fi completate cu verde.

Acesta este doar un exemplu simplu al numeroaselor acțiuni pe care le puteți efectua pe grupuri de celule utilizând funcția Range. Alte lucruri pe care le puteți face includ:

• Aplicați un contur în jurul grupului
• Verificați ortografia textului în interiorul unui interval de celule
• Ștergeți, copiați sau tăiați celule
• Căutați printr - o gamă cu “Găsi” metodă
• Mult mai mult

Asigurați-vă că ați citit pagina Microsoft MSDN pentru obiectul Range pentru a vedea toate posibilitățile.

Ia Excel la nivelul următor

Acum, că înțelegeți diferențele dintre funcțiile celule și funcțiile Range, este timpul să faceți scriptarea VBA la nivelul următor. Articolul lui Dann privind folosirea funcțiilor de numărare și adăugare în Excel vă va permite să construiți și mai multe scripturi avansate care pot acumula foarte repede valori în toate seturile de date.

Și dacă începeți doar cu VBA în Excel, nu uitați că avem un ghid introductiv fantastic pentru Excel VBA Exercițiul de programare Excel VBA pentru începători Exercițiul de programare Excel VBA pentru începători VBA este un instrument de putere Microsoft Office. Puteți să-l utilizați pentru a automatiza sarcinile cu ajutorul macrocomenzilor, a stabili declanșatoare și multe altele. Vă vom prezenta programarea de bază Visual Excel cu un proiect simplu. Citiți mai mult și pentru dvs..

.