Înțelegerea celulelor Excel vs. Funcțiile Range în VBA

Înțelegerea celulelor Excel vs. Funcțiile Range în VBA

Excel este puternic. Dacă îl folosești mult, probabil că știi deja multe trucuri folosind formule sau formatare automată, dar folosind Celulele și Gamă funcții în VBA, puteți crește analiza Excel la un nivel complet nou.





Problema utilizării funcțiilor Cells and Range în VBA este că, la niveluri avansate, majoritatea oamenilor au dificultăți în a înțelege cum funcționează efectiv aceste funcții. Folosirea lor poate deveni foarte confuză. Iată cum poți să le folosești în moduri pe care probabil nu ți le-ai imaginat niciodată.



Funcția de celule

Funcțiile Cells and Range vă permit să spuneți scriptul dvs. VBA exact unde doriți să obțineți sau să plasați datele din foaia de lucru. Principala diferență dintre cele două celule este ceea ce fac referire.





Celulele de obicei referință la o singură celulă la un moment dat, în timp ce Gamă face referire la un grup de celule simultan. Formatul acestei funcții este Celule (rând, coloană) .

Aceasta face referire la fiecare celulă din întreaga foaie. Este singurul exemplu în care funcția Celule nu face referință la o singură celulă:



Worksheets('Sheet1').Cells

Aceasta face referire la a treia celulă din stânga, din rândul de sus. Celula C1:

Worksheets('Sheet1').Cells(3)

Următorul cod face referință la celula D15:



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

Dacă doriți, puteți face referire și la celula D15 cu „Celule (15,„ D ”)” --- aveți voie să utilizați litera coloanei.

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



Funcția Range

În multe feluri, funcția Range este mult mai puternică decât utilizarea celulelor, deoarece vă permite să faceți referire fie la o singură celulă, fie la un anumit interval de celule, dintr-o dată. Nu veți dori să parcurgeți o funcție Range, deoarece referințele pentru celule nu sunt numere (cu excepția cazului în care încorporați funcția Cells în interiorul acesteia).

Formatul acestei funcții este Gama (celula # 1, celula # 2) . Fiecare celulă poate fi desemnată printr-un număr de literă.

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

de ce discul meu rulează la 100

Aici, funcția intervalului face referință la celula A5:

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

Aici, funcția de gamă face referință la toate celulele cuprinse între A1 și E20:

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

După cum sa menționat mai sus, nu trebuie să utilizați alocări de celule cu litere numerice. Puteți utiliza de fapt două funcții de celule în interiorul unei funcții Range pentru a identifica un interval pe foaie, astfel:


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

Codul de mai sus face referire la același interval ca funcția Range („A1: E20”). Valoarea utilizării acestuia este că vă va permite să scrieți cod care funcționează dinamic cu intervale folosind bucle.

Acum, că înțelegeți cum să formatați funcțiile Cells and Range, să ne aruncăm în modul în care puteți folosi creativ aceste funcții în codul dvs. VBA.

Prelucrarea datelor cu funcția de celule

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 pe mai multe foi.

Să luăm un exemplu simplu. Să presupunem că gestionezi o echipă de vânzări formată din 11 persoane și în fiecare lună vrei să te uiți la performanțele acestora.

Ai putea avea Foaia 1 care urmărește numărul vânzărilor și volumul vânzărilor.

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

Dacă doriți să calculați bonusul pe prima foaie folosind valorile din cele două foi, există mai multe moduri de a face acest lucru. Puteți scrie o formulă în prima celulă care efectuează calculul folosind date pe cele două foi și trageți-o în jos. Asta va merge.

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

Deci, de ce să nu declanșezi calculele pentru coloana bonus din același script la acel moment?

Funcția de celule în acțiune

Dacă nu ați scris niciodată VBA în Excel până acum, va trebui să activați elementul de meniu Dezvoltator. Pentru a face acest lucru, mergeți 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 Bine și reveniți la foaia principală, veți vedea opțiunea din meniul Dezvoltator.

Puteți utiliza Introduce pentru a insera un buton de comandă sau doar faceți clic Vizualizaț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, trebuie doar să faceți clic pe Vizualizați codul din meniul dezvoltatorului și lipiți următoarea funcție nouă în fereastra de cod.

Private Sub Workbook_Open()
End Sub

Fereastra codului dvs. va arăta cam așa.

Acum sunteți gata să scrieți codul pentru a gestiona calculul. Folosind o singură buclă, puteți parcurge toți cei 11 angajați și, cu funcția Celule, introduceț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, vom folosi 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 din Foaia 2 pentru Scorul de feedback.

Calculul final folosește următoarele procente pentru a adăuga până la 100 la sută din scorul bonus total. Se bazează pe un număr ideal de vânzări de 50, un volum de vânzări de 50.000 USD și un scor de feedback de 10.

  • (Număr de vânzări / 50) x 0,4
  • (Volumul vânzărilor / 50.000) x 0,5
  • (Scor de feedback / 10) x 0,1

Această abordare simplă oferă angajaților din 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 sub perfect pe orice factor reduce bonusul. Orice lucru mai bun decât ideal crește bonusul.

Acum, să vedem cum toată logica poate fi extrasă într-un script VBA foarte simplu și scurt:

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

Așa va arăta rezultatul acestui script.

câți adepți pe tiktok să intre în direct

Dacă doriți ca coloana Bonus să arate bonusul real în dolari, mai degrabă decât procentul, îl puteți înmulți cu suma maximă a bonusului. Mai bine, așezați suma respectivă într-o celulă pe o altă foaie și indicați-o în codul dvs. Acest lucru va facilita modificarea valorii ulterior fără a fi nevoie să vă editați codul.

Frumusețea funcției Cells este că puteți construi o logică destul de creativă pentru a extrage date multe celule pe mai multe foi diferite și efectuați unele calcule destul de complexe cu ei.

Puteți efectua tot felul de acțiuni asupra celulelor folosind funcția Celule --- lucruri precum ștergerea celulelor, schimbarea formatării fontului și multe altele.

Pentru a explora tot ce puteți face în continuare, consultați Pagina MSDN Microsoft pentru obiectul Cells.

Formatarea celulelor cu funcția Range

Pentru a parcurge mai multe celule pe rând, funcția Cells este perfectă. Dar dacă doriți să aplicați ceva la o întreagă gamă de celule simultan, funcția Range este mult mai eficientă.

Un caz de utilizare pentru aceasta ar putea fi formatarea unei game de celule folosind script, dacă sunt îndeplinite anumite condiții.

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

Să aruncăm o privire la modul în care puteți face asta cu o declarație 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

Când se execută acest lucru, dacă celula depășește obiectivul echipei, toate celulele din interval vor fi completate în verde.

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

  • Aplicați o schiță în jurul grupului
  • Verificați ortografia textului într-o gamă de celule
  • Ștergeți, copiați sau tăiați celulele
  • Căutați într-o gamă cu metoda „Găsiți”
  • Mult mai mult

Asigurați-vă că citiți fișierul Pagina MSDN Microsoft pentru ca obiectul Range să vadă toate posibilitățile.

Treceți Excel la nivelul următor

Acum că înțelegeți diferențele dintre funcțiile Cells și Range, este timpul să vă duceți scripturile VBA la nivelul următor. Articolul lui Dann despre utilizarea funcțiilor de numărare și adăugare în Excel vă va permite să creați scripturi și mai avansate, care pot acumula valori în toate seturile de date foarte repede.

Și dacă tocmai începeți cu VBA în Excel, nu uitați că avem un fantastic ghid introductiv pentru Excel VBA pentru tine deasemenea.

Acțiune Acțiune Tweet E-mail Canon vs. Nikon: Ce marcă de camere este mai bună?

Canon și Nikon sunt cele mai mari două nume din industria camerelor. Dar ce marcă oferă o gamă mai bună de camere și obiective?

Citiți în continuare Subiecte asemănătoare
  • Productivitate
  • Programare
  • Programare Visual Basic
  • Microsoft Excel
Despre autor Ryan Dube(942 articole publicate)

Ryan are o diplomă de licență în inginerie electrică. A lucrat 13 ani în ingineria automatizării, 5 ani în IT, iar acum este inginer de aplicații. Fost editor manager al MakeUseOf, a vorbit la conferințe naționale privind vizualizarea datelor și a fost prezentat la TV și radio naționale.

Mai multe de la Ryan Dube

Aboneaza-te la newsletter-ul nostru

Alăturați-vă newsletter-ului pentru sfaturi tehnice, recenzii, cărți electronice gratuite și oferte exclusive!

Faceți clic aici pentru a vă abona