Pag-unawa sa Mga Excel Cell kumpara sa Mga Pag-andar sa Saklaw sa VBA

Pag-unawa sa Mga Excel Cell kumpara sa Mga Pag-andar sa Saklaw sa VBA

Malakas ang Excel. Kung ginamit mo ito ng maraming, marahil alam mo na ang maraming mga trick gamit ang mga formula o autoformatting, ngunit ang paggamit ng Mga cell at Saklaw mga pagpapaandar sa VBA, maaari mong mapalakas ang iyong analytics sa Excel sa isang bagong bagong antas.





Ang problema sa paggamit ng mga function ng Cells at Range sa VBA ay na sa mga advanced na antas, karamihan sa mga tao ay nahihirapang maunawaan kung paano talaga gumagana ang mga pagpapaandar na ito. Ang paggamit ng mga ito ay maaaring maging lubhang nakalilito. Narito kung paano mo magagamit ang mga ito sa mga paraang hindi mo naisip.



Ang Cells Function

Hinahayaan ka ng mga function ng Cells at Range na sabihin sa iyo iyong script ng VBA eksakto kung saan sa iyong worksheet na nais mong makuha, o ilagay ang data. Ang pangunahing pagkakaiba sa pagitan ng dalawang mga cell ay kung ano ang sanggunian nila.





Mga cell karaniwang tumutukoy sa isang solong cell nang paisa-isa, habang Saklaw sumangguni sa isang pangkat ng mga cell nang sabay-sabay. Ang format para sa pagpapaandar na ito ay Mga cell (hilera, haligi) .

Tinutukoy nito ang bawat solong cell sa buong sheet. Ito ang isang halimbawa kung saan ang pag-andar ng Cells ay hindi tumutukoy sa isang solong cell:



Worksheets('Sheet1').Cells

Tinutukoy nito ang pangatlong cell mula sa kaliwa, ng tuktok na hilera. Cell C1:

Worksheets('Sheet1').Cells(3)

Ang mga sumusunod na code na sanggunian sa cell D15:



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

Kung nais mo, maaari mo ring sanggunian ang cell D15 na may 'Mga Cell (15,' D ')' --- pinapayagan kang gamitin ang titik ng haligi.

Mayroong maraming kakayahang umangkop sa kakayahang mag-refer sa isang cell gamit ang isang numero para sa haligi at cell, lalo na sa mga script na maaaring lumusot isang malaking bilang ng mga cell (at gumanap ng mga kalkulasyon sa mga ito) nang napakabilis. Darating namin iyon nang mas detalyado sa ibaba.



Ang Saklaw na Pag-andar

Sa maraming mga paraan, ang pag-andar ng Saklaw ay mas malakas kaysa sa paggamit ng Mga cell, dahil hinahayaan ka nitong mag-refer alinman sa isang solong cell, o isang tukoy na saklaw ng mga cell, nang sabay-sabay. Hindi mo gugustuhin na mag-loop sa pamamagitan ng isang pag-andar ng Saklaw, dahil ang mga sanggunian para sa mga cell ay hindi numero (maliban kung na-embed mo ang pagpapaandar ng Mga Cells sa loob nito).

Ang format para sa pagpapaandar na ito ay Saklaw (Cell # 1, Cell # 2) . Ang bawat cell ay maaaring italaga ng isang letra-numero.

Tingnan natin ang ilang mga halimbawa.

kung paano maglaro ng mga lumang laro ng pokemon

Dito, ang pag-andar sa saklaw ay tumutukoy sa cell A5:

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

Dito, ang pag-andar sa saklaw ay tumutukoy sa lahat ng mga cell sa pagitan ng A1 hanggang E20:

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

Tulad ng nabanggit sa itaas, hindi mo kailangang gumamit ng mga takdang-cell na takdang-aralin. Maaari mo talagang gamitin ang dalawang mga function ng Cells sa loob ng isang function na Saklaw upang makilala ang isang saklaw sa sheet, tulad nito:


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

Ang code sa itaas ay tumutukoy sa parehong saklaw ng paggana ng Saklaw ('A1: E20'). Ang halaga sa paggamit nito, ay papayagan kang magsulat ng code na pabago-bagong gumagana sa mga saklaw na gumagamit ng mga loop.

Ngayon na naiintindihan mo kung paano i-format ang mga function ng Cells at Range, sumisid tayo sa kung paano mo magagamit ang malikhaing paggamit ng mga pagpapaandar na ito sa iyong VBA code.

Pagproseso ng Data Sa Mga Pag-andar ng Mga Cell

Ang pag-andar ng Cells ay pinaka-kapaki-pakinabang kapag mayroon kang isang kumplikadong pormula na nais mong gumanap sa maraming mga saklaw ng mga cell. Ang mga saklaw na ito ay maaari ding umiiral sa maraming mga sheet.

Gumawa tayo ng isang simpleng halimbawa. Sabihin nating pinamamahalaan mo ang isang pangkat ng benta ng 11 katao, at bawat buwan na nais mong tingnan ang kanilang pagganap.

Maaari kang magkaroon ng Sheet1 na sinusubaybayan ang bilang ng kanilang mga benta, at ang dami ng kanilang mga benta.

Sa Sheet2 ay kung saan mo sinusubaybayan ang kanilang rating ng feedback sa huling 30 araw mula sa mga kliyente ng iyong kumpanya.

Kung nais mong kalkulahin ang bonus sa unang sheet gamit ang mga halaga mula sa dalawang sheet, maraming paraan upang magawa ito. Maaari kang magsulat ng isang pormula sa unang cell na gumaganap ng pagkalkula gamit ang data sa kabuuan ng dalawang sheet at i-drag ito pababa. Gagana na yan

Ang isang kahalili dito ay ang paglikha ng VBA script na maaring mag-trigger upang patakbuhin tuwing binubuksan mo ang sheet, o na-trigger ng isang pindutan ng utos sa sheet upang makontrol mo kapag nagkalkula ito. Maaari kang gumamit ng isang VBA script upang makuha ang lahat ng data ng mga benta mula sa isang panlabas na file.

Kaya bakit hindi lamang mag-trigger ang mga kalkulasyon para sa haligi ng bonus sa parehong script sa oras na iyon?

Ang Mga Cell Cunction sa Aksyon

Kung hindi ka pa nakasulat ng VBA sa Excel dati, kakailanganin mong paganahin ang item sa menu ng Developer. Upang gawin ito, pumunta sa File > Mga pagpipilian . Mag-click sa Ipasadya ang Ribbon . Panghuli, piliin ang Developer mula sa kaliwang pane, Idagdag pa ito sa kanang pane, at tiyaking napili ang checkbox.

Ngayon, kapag nag-click ka OK lang at bumalik sa pangunahing sheet, makikita mo ang pagpipilian sa menu ng Developer.

Maaari mong gamitin ang Isingit menu upang magsingit ng isang pindutan ng utos, o i-click lamang Tingnan ang Code upang simulan ang pag-coding.

Sa halimbawang ito, gagawin namin ang pagpapatakbo ng script tuwing bubuksan ang workbook. Upang magawa ito, mag-click lamang Tingnan ang Code mula sa menu ng developer, at i-paste ang sumusunod na bagong pagpapaandar sa window ng code.

Private Sub Workbook_Open()
End Sub

Ang window ng iyong code ay magmumukhang ganito.

Handa ka na ngayong isulat ang code upang hawakan ang pagkalkula. Gamit ang isang solong loop, maaari mong hakbangin ang lahat ng 11 mga empleyado, at sa pag-andar ng mga cell na hilahin ang tatlong mga variable na kinakailangan para sa pagkalkula.

Tandaan ang pagpapaandar ng mga cell ay may hilera at haligi bilang mga parameter upang makilala ang bawat indibidwal na cell. Gagawin namin ang 'x' sa hilera, gagamit ng isang numero upang humiling ng data ng bawat haligi. Ang bilang ng mga hilera ay ang bilang ng mga empleyado, kaya't ito ay magiging mula 1 hanggang 11. Ang tagakilala ng haligi ay magiging 2 para sa Bilang ng Benta, 3 para sa Dami ng Pagbebenta, at 2 mula sa Sheet 2 para sa Marka ng Feedback.

Ang pangwakas na pagkalkula ay gumagamit ng mga sumusunod na porsyento upang magdagdag ng hanggang sa 100 porsyento ng kabuuang iskor sa bonus. Ito ay batay sa isang mainam na bilang ng mga benta na 50, dami ng mga benta bilang $ 50,000, at isang marka ng feedback na 10.

  • (Bilang ng Benta / 50) x 0.4
  • (Dami ng Pagbebenta / 50,000) x 0.5
  • (Score ng feedback / 10) x 0.1

Ang simpleng pamamaraang ito ay nagbibigay sa mga empleyado ng benta ng isang may timbang na bonus. Para sa isang bilang ng 50, dami ng $ 50,000, at isang markang 10 --- nakukuha nila ang buong maximum na bonus para sa buwan. Gayunpaman ang anumang bagay sa ilalim ng perpekto sa anumang kadahilanan ay binabawasan ang bonus. Anumang mas mahusay kaysa sa perpektong nagpapalakas ng bonus.

Tingnan natin ngayon kung paano ang lahat ng lohika na iyon ay maaaring makuha sa isang napaka-simple, maikling script ng 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

Ito ang magiging hitsura ng output ng script na ito.

ilan ang mga tagasunod sa tiktok upang mabuhay

Kung nais mong ipakita ang haligi ng Bonus ang aktwal na dolyar na bonus kaysa sa porsyento, maaari mo itong i-multiply sa maximum na halaga ng bonus. Mas mabuti pa, ilagay ang halagang iyon sa isang cell sa isa pang sheet, at isangguni ito sa iyong code. Gagawin nitong mas madali baguhin ang halaga sa paglaon nang hindi na kinakailangang i-edit ang iyong code.

Ang kagandahan ng pag-andar ng Cells ay maaari kang bumuo ng ilang medyo malikhaing lohika upang makuha ang data mula sa maraming cells sa maraming pagkakaiba-iba ng mga sheet, at gumanap ng ilan medyo kumplikadong mga kalkulasyon kasama nila.

Maaari mong gampanan ang lahat ng uri ng mga pagkilos sa mga cell gamit ang pag-andar ng Mga Cell --- mga bagay tulad ng pag-clear ng mga cell, pagbabago ng pag-format ng font, at marami pa.

Upang tuklasin ang lahat ng maaari mong gawin sa karagdagang, tingnan ang Pahina ng Microsoft MSDN para sa object ng Cells.

Mga Pag-format ng Mga Cell Na May Saklaw na Pag-andar

Para sa pag-loop sa pamamagitan ng maraming mga cell nang paisa-isa, ang pag-andar ng Cells ay perpekto. Ngunit kung nais mong maglapat ng isang bagay sa isang buong saklaw ng mga cell nang sabay-sabay, ang pag-andar ng Range ay mas mahusay.

Ang isang kaso ng paggamit para dito ay maaaring i-format ang isang saklaw ng mga cell gamit ang script, kung natutugunan ang ilang mga kundisyon.

Halimbawa, sabihin natin kung ang bilang ng lahat ng dami ng mga benta sa lahat ng mga empleyado ng benta ay lumalagpas sa $ 400,000 sa kabuuan, nais mong i-highlight ang lahat ng mga cell sa haligi ng bonus na berde upang ipahiwatig na ang koponan ay nakakuha ng isang karagdagang bonus ng koponan.

Tingnan natin kung paano mo magagawa iyon isang KUNG pahayag .

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

Kapag tumakbo ito, kung ang cell ay higit sa layunin ng koponan, ang lahat ng mga cell sa saklaw ay mapupuno ng berde.

Ito ay isang simpleng halimbawa lamang ng maraming mga aksyon na maaari mong gampanan sa mga pangkat ng mga cell gamit ang function na Saklaw. Ang iba pang mga bagay na maaari mong gawin ay kasama ang:

  • Maglagay ng isang balangkas sa paligid ng pangkat
  • Suriin ang spelling ng teksto sa loob ng isang saklaw ng mga cell
  • I-clear, kopyahin, o gupitin ang mga cell
  • Maghanap sa pamamagitan ng isang saklaw sa pamamaraang 'Hanapin'
  • Higit pa

Tiyaking basahin ang Pahina ng Microsoft MSDN para sa saklaw na object upang makita ang lahat ng mga posibilidad.

Dalhin ang Excel sa Susunod na Antas

Ngayon na naiintindihan mo ang mga pagkakaiba sa pagitan ng mga Cell at ang mga pag-andar ng Saklaw, oras na upang dalhin ang iyong pag-script sa VBA sa susunod na antas. Ang artikulo ni Dann sa paggamit ng Mga pag-andar sa Pagbibilang at Pagdaragdag sa Excel ay magpapahintulot sa iyo na bumuo ng mas advanced na mga script na maaaring makaipon ng mga halaga sa lahat ng iyong mga set ng data nang napakabilis.

At kung nagsisimula ka lang sa VBA sa Excel, huwag kalimutan na mayroon kaming kamangha-manghang panimulang gabay sa Excel VBA para sayo din.

Magbahagi Magbahagi Mag-tweet Email Canon vs. Nikon: Aling Camera Brand Ay Mas Mabuti?

Ang Canon at Nikon ay ang dalawang pinakamalaking pangalan sa industriya ng camera. Ngunit aling tatak ang nag-aalok ng mas mahusay na lineup ng mga camera at lente?

Basahin Susunod Mga Kaugnay na Paksa
  • Pagiging produktibo
  • Programming
  • Visual Basic Programming
  • Microsoft Excel
Tungkol sa May-akda Ryan Dube(942 Mga Artikulo Nai-publish)

Si Ryan ay may kursong BSc sa Electrical Engineering. Nagtrabaho siya ng 13 taon sa automation engineering, 5 taon sa IT, at ngayon ay isang App Engineer. Isang dating Managing Editor ng MakeUseOf, binigkas siya sa mga pambansang kumperensya sa Data Visualization at naitampok sa pambansang TV at radyo.

Higit pa Mula kay Ryan Dube

Mag-subscribe sa aming newsletter

Sumali sa aming newsletter para sa mga tip sa tech, pagsusuri, libreng ebook, at eksklusibong deal!

Mag-click dito upang mag-subscribe