Izpratne par Excel šūnām un diapazona funkcijām VBA

Izpratne par Excel šūnām un diapazona funkcijām VBA

Excel ir spēcīgs. Ja jūs to izmantojat daudz, jūs, iespējams, jau zināt daudzus trikus, izmantojot formulas vai automātisko formatējumu, bet izmantojot Šūnas un Diapazons funkcijas VBA, varat uzlabot savu Excel analītiku pilnīgi jaunā līmenī.





Šūnu un diapazona funkciju izmantošanas problēma VBA ir tā, ka augstākajā līmenī lielākajai daļai cilvēku ir grūti saprast, kā šīs funkcijas faktiski darbojas. To izmantošana var kļūt ļoti mulsinoša. Lūk, kā jūs varat tos izmantot tādā veidā, kā jūs, iespējams, nekad neesat iedomājies.



Šūnu funkcija

Šūnu un diapazona funkcijas ļauj jums pateikt jūsu VBA skripts tieši to, kur savā darblapā vēlaties iegūt vai ievietot datus. Galvenā atšķirība starp abām šūnām ir tā, uz ko tās atsaucas.





Šūnas parasti atsaucas uz vienu šūnu vienlaikus, kamēr Diapazons atsaucas uz šūnu grupu vienlaikus. Šīs funkcijas formāts ir Šūnas (rinda, kolonna) .

Tas atsaucas uz katru šūnu visā lapā. Šis ir viens piemērs, kur funkcija Šūnas neatsaucas uz vienu šūnu:



Worksheets('Sheet1').Cells

Tas attiecas uz augšējās rindas trešo šūnu no kreisās puses. Šūna C1:

Worksheets('Sheet1').Cells(3)

Šāds kods atsaucas uz šūnu D15:



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

Ja vēlaties, varat arī atsaukties uz šūnu D15 ar 'Šūnas (15,' D ')'-jums ir atļauts izmantot kolonnas burtu.

Ir daudz elastības, lai varētu atsaukties uz šūnu, izmantojot kolonnu un šūnu skaitli, jo īpaši ar skriptiem, kas to var cilpa cauri liels šūnu skaits (un veic tiem aprēķinus) ļoti ātri. Tālāk mēs pie tā nonāksim sīkāk.



Diapazona funkcija

Daudzējādā ziņā diapazona funkcija ir daudz jaudīgāka nekā šūnu izmantošana, jo tā ļauj vienlaikus atsaukties uz vienu šūnu vai noteiktu šūnu diapazonu. Jūs nevēlaties atkārtot funkciju diapazons, jo atsauces uz šūnām nav skaitļi (ja vien jūs neiekļūstat šūnu funkciju tās iekšpusē).

Šīs funkcijas formāts ir Diapazons (šūna #1, šūna #2) . Katru šūnu var apzīmēt ar burtu-ciparu.

Apskatīsim dažus piemērus.

kā iegūt vairāk filmu vietnē Netflix

Šeit diapazona funkcija atsaucas uz šūnu A5:

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

Šeit diapazona funkcija atsaucas uz visām šūnām starp A1 un E20:

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

Kā minēts iepriekš, jums nav jāizmanto šūnu burti ar ciparu burtiem. Faktiski diapazona funkcijas ietvaros varētu izmantot divas šūnu funkcijas, lai identificētu diapazonu lapā, piemēram:


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

Iepriekš minētais kods atsaucas uz to pašu diapazonu kā diapazona ('A1: E20') funkcija. Izmantojot to, vērtība ir tāda, ka tas ļautu jums rakstīt kodu, kas dinamiski darbojas ar diapazoniem, izmantojot cilpas.

Tagad, kad jūs saprotat, kā formatēt šūnas un diapazona funkcijas, iedziļināsimies, kā jūs varat radoši izmantot šīs funkcijas savā VBA kodā.

Datu apstrāde ar šūnu funkciju

Funkcija Šūnas ir visnoderīgākā, ja jums ir sarežģīta formula, kuru vēlaties izpildīt vairākos šūnu diapazonos. Šie diapazoni var pastāvēt arī vairākās lapās.

Ņemsim vienkāršu piemēru. Pieņemsim, ka jūs vadāt pārdošanas komandu 11 cilvēku sastāvā un katru mēnesi vēlaties aplūkot viņu sniegumu.

Jums varētu būt 1. lapa kas izseko viņu pārdošanas apjomu un pārdošanas apjomu.

Ieslēgts 2. lapa ir vieta, kur jūs izsekojat viņu atsauksmju vērtējumu par jūsu uzņēmuma klientiem pēdējo 30 dienu laikā.

Ja vēlaties aprēķināt prēmiju pirmajā lapā, izmantojot abu lapu vērtības, to var izdarīt vairākos veidos. Jūs varētu ierakstīt formulu pirmajā šūnā, kas veic aprēķinu, izmantojot datus abās lapās, un velciet to uz leju. Tas izdosies.

Alternatīva tam ir izveidot VBA skriptu, kuru vai nu aktivizēt, kad atverat lapu, vai aktivizēt ar komandas pogu lapā, lai jūs varētu kontrolēt, kad tā tiek aprēķināta. Jebkurā gadījumā varat izmantot VBA skriptu, lai visus pārdošanas datus ievilktu no ārēja faila.

Tātad, kāpēc ne tikai aktivizēt aprēķinus par bonusa kolonnu tajā pašā skriptā tajā laikā?

Šūnas darbojas darbībā

Ja nekad iepriekš neesat rakstījis VBA programmā Excel, jums ir jāiespējo izvēlnes vienums Izstrādātājs. Lai to izdarītu, dodieties uz Fails > Iespējas . Klikšķiniet uz Pielāgot lenti . Visbeidzot, kreisajā rūtī izvēlieties Izstrādātājs, Pievienot to labajā rūtī un pārliecinieties, vai ir atzīmēta izvēles rūtiņa.

Tagad, noklikšķinot labi un atgriezieties galvenajā lapā, jūs redzēsit izvēlnes opciju Izstrādātājs.

Jūs varat izmantot Ievietot izvēlni, lai ievietotu komandas pogu, vai vienkārši noklikšķiniet Skatīt kodu lai sāktu kodēšanu.

Šajā piemērā skripts tiks palaists katru reizi, kad tiks atvērta darbgrāmata. Lai to izdarītu, vienkārši noklikšķiniet Skatīt kodu no izstrādātāja izvēlnes un koda logā ielīmējiet šo jauno funkciju.

Private Sub Workbook_Open()
End Sub

Jūsu koda logs izskatīsies apmēram šādi.

Tagad jūs esat gatavs uzrakstīt kodu, lai veiktu aprēķinus. Izmantojot vienu cilpu, jūs varat iziet cauri visiem 11 darbiniekiem un ar funkciju Šūnas ievilkt trīs aprēķinam nepieciešamos mainīgos.

Atcerieties, ka funkcijas Šūnas rindas un kolonnas ir parametri, lai identificētu katru atsevišķu šūnu. Mēs izveidosim rindu “x”, izmantojiet skaitli, lai pieprasītu katras kolonnas datus. Rindu skaits ir darbinieku skaits, tāpēc tas būs no 1 līdz 11. Kolonnas identifikators būs 2 pārdošanas skaitam, 3 pārdošanas apjomam un 2 no 2. lapas atsauksmju rādītājam.

Galīgajā aprēķinā izmantojiet šādus procentus, lai pievienotu līdz 100 procentiem no kopējā bonusa rezultāta. Tas ir balstīts uz ideālu pārdošanas apjomu 50, pārdošanas apjomu 50 000 USD un atsauksmes punktu skaitu 10.

  • (Pārdošanas skaits/50) x 0,4
  • (Pārdošanas apjoms/50 000) x 0,5
  • (Atsauksmju rādītājs/10) x 0,1

Šī vienkāršā pieeja piešķir pārdošanas darbiniekiem svērtu bonusu. Par 50 skaitļiem, 50 000 ASV dolāru apjomu un 10 punktu skaitu-viņi saņem visu maksimālo mēneša bonusu. Tomēr viss, kas ir perfekts jebkurā faktorā, samazina bonusu. Viss, kas ir labāks par ideālu, palielina bonusu.

Tagad apskatīsim, kā visu šo loģiku var izvilkt ļoti vienkāršā, īsā VBA skriptā:

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

Šādi izskatīsies šī skripta iznākums.

ko var darīt ar aveņu pi

Ja vēlaties, lai slejā Bonuss tiktu parādīts faktiskais dolāra bonuss, nevis procenti, to varētu reizināt ar maksimālo bonusa summu. Vēl labāk, ievietojiet šo summu citas lapas šūnā un norādiet to savā kodā. Tādējādi būtu vieglāk vēlāk mainīt vērtību, nerediģējot kodu.

Šūnu funkcijas skaistums ir tāds, ka jūs varat izveidot diezgan radošu loģiku, lai iegūtu datus daudzas šūnas daudzās dažādās lapās un izpildiet dažas diezgan sarežģīti aprēķini ar viņiem.

Jūs varat veikt visu veidu darbības šūnās, izmantojot funkciju Šūnas-piemēram, šūnu notīrīšanu, fontu formatējuma maiņu un daudz ko citu.

Lai izpētītu visu, ko varat darīt tālāk, apskatiet Microsoft MSDN lapa Šūnu objektam.

Šūnu formatēšana ar diapazona funkciju

Lai veiktu cilpu pa vienai pa vienai, šūnu funkcija ir ideāla. Bet, ja vēlaties kaut ko piemērot visam šūnu diapazonam vienlaikus, diapazona funkcija ir daudz efektīvāka.

Viens no piemēriem tam varētu būt šūnu diapazona formatēšana, izmantojot skriptu, ja ir izpildīti noteikti nosacījumi.

Piemēram, pieņemsim, ka visu pārdošanas darbinieku pārdošanas apjoms kopumā pārsniedz 400 000 ASV dolāru, jūs vēlaties izcelt visas šūnas bonusa slejā zaļā krāsā, lai apzīmētu, ka komanda ir nopelnījusi papildu komandas bonusu.

Apskatīsim, kā jūs to varat izdarīt IF paziņojums .

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

Ja šūna darbojas, ja šūna ir virs komandas mērķa, visas diapazona šūnas tiks aizpildītas zaļā krāsā.

Šis ir tikai viens vienkāršs piemērs daudzajām darbībām, kuras varat veikt šūnu grupās, izmantojot funkciju Diapazons. Citas lietas, ko varat darīt, ir šādas:

  • Pielietojiet kontūru ap grupu
  • Pārbaudiet teksta pareizrakstību šūnu diapazonā
  • Notīriet, kopējiet vai izgrieziet šūnas
  • Meklējiet diapazonā, izmantojot metodi 'Atrast'
  • Daudz vairāk

Noteikti izlasiet Microsoft MSDN lapa lai Range objekts redzētu visas iespējas.

Paceliet Excel nākamajā līmenī

Tagad, kad jūs saprotat atšķirības starp šūnām un diapazona funkcijām, ir pienācis laiks pacelt savu VBA skriptu nākamajā līmenī. Danna raksts par skaitīšanas un pievienošanas funkciju izmantošanu programmā Excel ļaus jums izveidot vēl sarežģītākus skriptus, kas ļoti ātri var uzkrāt vērtības visām jūsu datu kopām.

Un, ja jūs tikko sākat darbu ar VBA programmā Excel, neaizmirstiet, ka mums ir fantastisks ievada rokasgrāmata programmai Excel VBA arī tev.

Kopīgot Kopīgot Čivināt E -pasts Canon pret Nikon: kurš kameras zīmols ir labāks?

Canon un Nikon ir divi lielākie nosaukumi kameru nozarē. Bet kurš zīmols piedāvā labāku kameru un objektīvu klāstu?

Lasīt Tālāk Saistītās tēmas
  • Produktivitāte
  • Programmēšana
  • Vizuālā pamata programmēšana
  • Microsoft Excel
Par autoru Raiens Dube(Publicēti 942 raksti)

Raiens ir ieguvis bakalaura grādu elektrotehnikā. Viņš ir strādājis 13 gadus automatizācijas inženierijā, 5 gadus IT jomā un tagad ir lietotņu inženieris. Bijušais MakeUseOf vadošais redaktors, viņš uzstājās nacionālajās datu vizualizācijas konferencēs un tika demonstrēts valsts televīzijā un radio.

Vairāk no Raiena Dube

Abonējiet mūsu biļetenu

Pievienojieties mūsu informatīvajam izdevumam, lai iegūtu tehniskus padomus, pārskatus, bezmaksas e -grāmatas un ekskluzīvus piedāvājumus!

Noklikšķiniet šeit, lai abonētu