„Excel“ ląstelių ir diapazono funkcijų supratimas VBA

„Excel“ ląstelių ir diapazono funkcijų supratimas VBA

„Excel“ yra galinga. Jei jį naudojate daug, tikriausiai jau žinote daug gudrybių naudodami formules ar automatinį formatavimą, tačiau pasinaudodami Ląstelės ir diapazonas VBA funkcijas, galite patobulinti „Excel“ analizę iki visiškai naujo lygio.





Naudojant ląstelių ir diapazono funkcijas VBA problema yra ta, kad aukštesniame lygyje daugumai žmonių sunku suprasti, kaip šios funkcijos iš tikrųjų veikia. Jų naudojimas gali būti labai painus. Štai kaip galite jomis pasinaudoti taip, kaip tikriausiai niekada neįsivaizdavote.



Ląstelių funkcija

Ląstelių ir diapazono funkcijos leidžia jums pasakyti jūsų VBA scenarijų kur tiksliai norite gauti savo darbalapio, arba įdėti duomenis. Pagrindinis skirtumas tarp dviejų langelių yra tai, ką jie nurodo.





Ląstelės paprastai nurodo vieną langelį vienu metu, tuo tarpu diapazonas iš karto nurodo ląstelių grupę. Šios funkcijos formatas yra Ląstelės (eilutė, stulpelis) .

Tai nurodo kiekvieną langelį visame lape. Tai yra vienas pavyzdys, kai ląstelės funkcija nenurodo vienos ląstelės:



Worksheets('Sheet1').Cells

Tai nurodo trečią viršutinės eilutės langelį iš kairės. C1 langelis:

Worksheets('Sheet1').Cells(3)

Šis kodas nurodo langelį D15:



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

Jei norite, taip pat galite nurodyti langelį D15 su „Ląstelės (15,„ D “)“-jums leidžiama naudoti stulpelio raidę.

Yra daug lankstumo, kai galima nurodyti langelį naudojant stulpelio ir langelio skaičių, ypač naudojant scenarijus kilpą labai daug ląstelių (ir atlieka jose skaičiavimus) labai greitai. Žemiau mes prie to pateiksime išsamiau.



Diapazono funkcija

Daugeliu atžvilgių diapazono funkcija yra daug galingesnė nei naudojant langelius, nes ji leidžia vienu metu nurodyti vieną langelį arba konkretų langelių diapazoną. Jūs nenorėsite perjungti diapazono funkcijos, nes nuorodos į langelius nėra skaičiai (nebent įterpiate ląstelės funkciją jos viduje).

Šios funkcijos formatas yra Diapazonas (1 langelis, 2 langelis) . Kiekvieną langelį galima žymėti raide-skaičiumi.

Pažvelkime į keletą pavyzdžių.

kodėl negaliu nieko spustelėti savo užduočių juostoje

Čia diapazono funkcija nurodo langelį A5:

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

Čia diapazono funkcija nurodo visas ląsteles nuo A1 iki E20:

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

Kaip minėta aukščiau, jums nereikia naudoti skaičių ir raidžių langelių priskyrimų. Iš tikrųjų galite naudoti dvi ląstelių funkcijas diapazono funkcijos viduje, kad nustatytumėte lapo diapazoną, pavyzdžiui:


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

Aukščiau pateiktas kodas nurodo tą patį diapazoną kaip ir diapazono („A1: E20“) funkcija. Verta jį naudoti, nes tai leidžia jums rašyti kodą, kuris dinamiškai veikia su diapazonais, naudojant kilpas.

Dabar, kai suprantate, kaip formatuoti langelių ir diapazono funkcijas, pasinerkime, kaip galite kūrybiškai pasinaudoti šiomis funkcijomis savo VBA kode.

Duomenų apdorojimas naudojant ląstelių funkciją

Funkcija Ląstelės yra naudingiausia, kai turite sudėtingą formulę, kurią norite atlikti keliuose langelių diapazonuose. Šie diapazonai taip pat gali būti keliuose lapuose.

Paimkime paprastą pavyzdį. Tarkime, jūs valdote 11 žmonių pardavimo komandą ir kiekvieną mėnesį norite pažvelgti į jų veiklą.

Gali turėti 1 lapas kuris stebi jų pardavimų skaičių ir pardavimo apimtį.

Įjungta 2 lapas čia galite stebėti jų klientų atsiliepimų įvertinimą per pastarąsias 30 dienų.

Jei norite apskaičiuoti premiją pirmame lape, naudodami dviejų lapų vertes, tai galite padaryti keliais būdais. Galite įrašyti formulę į pirmąjį langelį, kuris atlieka skaičiavimą, naudodamas dviejų lapų duomenis, ir vilkite jį žemyn. Tai veiks.

Alternatyva yra sukurti VBA scenarijų, kurį arba paleidžiate, kai atidarote lapą, arba suaktyvina lapo komandinis mygtukas, kad galėtumėte valdyti, kada jis skaičiuojamas. Bet kokiu atveju galite naudoti VBA scenarijų, kad surinktumėte visus pardavimo duomenis iš išorinio failo.

Taigi kodėl gi ne tik tuo metu inicijuoti to paties scenarijaus premijos stulpelio skaičiavimus?

Ląstelės veikia veikdamos

Jei niekada anksčiau nerašėte „VBA“ programoje „Excel“, turėsite įgalinti kūrėjo meniu elementą. Norėdami tai padaryti, eikite į Failas > Galimybės . Spustelėkite Tinkinti juostelę . Galiausiai kairėje srityje pasirinkite Kūrėjas, Papildyti į dešinę sritį ir įsitikinkite, kad pažymėtas žymimasis laukelis.

Dabar, kai spustelėsite Gerai ir grįžkite į pagrindinį lapą, pamatysite kūrėjo meniu parinktį.

Galite naudoti Įdėti meniu, kad įterptumėte komandos mygtuką, arba tiesiog spustelėkite Peržiūrėti kodą pradėti koduoti.

Šiame pavyzdyje scenarijus bus paleistas kiekvieną kartą atidarius darbaknygę. Norėdami tai padaryti, tiesiog spustelėkite Peržiūrėti kodą iš kūrėjo meniu ir įklijuokite šią naują funkciją į kodo langą.

Private Sub Workbook_Open()
End Sub

Jūsų kodo langas atrodys maždaug taip.

Dabar esate pasiruošę parašyti kodą, kad galėtumėte atlikti skaičiavimus. Naudodami vieną kilpą, galite pereiti visus 11 darbuotojų, o naudodami langelių funkciją įtraukite tris skaičiavimui reikalingus kintamuosius.

Atminkite, kad ląstelės funkcija turi eilutes ir stulpelius kaip parametrus, skirtus identifikuoti kiekvieną atskirą langelį. Padarysime „x“ eilutę, naudokite skaičių, kad pateiktumėte kiekvieno stulpelio duomenų užklausą. Eilučių skaičius yra darbuotojų skaičius, taigi tai bus nuo 1 iki 11. Stulpelio identifikatorius bus 2 pardavimų skaičiui, 3 pardavimų apimčiai ir 2 iš 2 lapo atsiliepimų balas.

Galutiniame skaičiavime naudojami šie procentai, kad būtų pridėta iki 100 procentų viso premijos balo. Jis pagrįstas idealiu pardavimų skaičiumi - 50, pardavimo apimtis - 50 000 USD ir atsiliepimų balas - 10.

  • (Pardavimų skaičius/50) x 0,4
  • (Pardavimo apimtis/50 000) x 0,5
  • (Atsiliepimų balas/10) x 0,1

Šis paprastas metodas suteikia pardavimo darbuotojams svertinę premiją. Už 50 skaičių, 50 000 USD apimtį ir 10 balų-jie gauna visą maksimalią mėnesio premiją. Tačiau viskas, kas nėra tobula, sumažina premiją. Viskas, kas geriau nei idealu, padidina premiją.

Dabar pažiūrėkime, kaip visa ši logika gali būti ištraukta naudojant labai paprastą, trumpą VBA scenarijų:

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

Štai kaip atrodys šio scenarijaus išvestis.

ką tu gali padaryti su aviečių pi

Jei stulpelyje „Premija“ norite matyti faktinę dolerio premiją, o ne procentą, galite ją padauginti iš didžiausios premijos sumos. Dar geriau, įdėkite tą sumą į kito lapo langelį ir nurodykite jį savo kode. Taip būtų lengviau vėliau pakeisti vertę neredaguojant kodo.

Ląstelių funkcijos grožis yra tas, kad galite sukurti gana kūrybingą logiką, kad gautumėte duomenis daug ląstelių daugelyje skirtingų lapų ir atlikite kai kuriuos gana sudėtingi skaičiavimai su jais.

Naudodami langelių funkciją, galite atlikti įvairius langelių veiksmus, pvz., Išvalyti langelius, pakeisti šrifto formatavimą ir dar daugiau.

Norėdami ištirti viską, ką galite padaryti toliau, patikrinkite „Microsoft MSDN“ puslapis objektui Cells.

Ląstelių formatavimas su diapazono funkcija

Ląstelių funkcija yra tobula, kad vienu metu būtų galima pereiti per daugelį langelių. Bet jei norite ką nors pritaikyti visam ląstelių diapazonui vienu metu, diapazono funkcija yra daug efektyvesnė.

Tam gali būti naudojamas ląstelių diapazono formatavimas naudojant scenarijų, jei įvykdomos tam tikros sąlygos.

Pvz., Tarkime, jei visų pardavimo darbuotojų pardavimo apimtis iš viso viršija 400 000 USD, norite pažymėti visas premijos stulpelio langelius žalia spalva, o tai reiškia, kad komanda uždirbo papildomą komandos premiją.

Pažiūrėkime, kaip tai galite padaryti IF pareiškimas .

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

Kai tai vykdoma, jei langelis viršija komandos tikslą, visos diapazono ląstelės bus užpildytos žalia spalva.

Tai tik vienas paprastas pavyzdys iš daugybės veiksmų, kuriuos galite atlikti su ląstelių grupėmis naudodami diapazono funkciją. Kiti dalykai, kuriuos galite padaryti, yra šie:

  • Taikykite kontūrą aplink grupę
  • Patikrinkite teksto rašybą langelių diapazone
  • Išvalyti, nukopijuoti arba iškirpti langelius
  • Ieškokite diapazone naudodami „Rasti“ metodą
  • Daug daugiau

Būtinai perskaitykite „Microsoft MSDN“ puslapis kad Range objektas matytų visas galimybes.

Perkelkite „Excel“ į kitą lygį

Dabar, kai suprantate ląstelių ir diapazono funkcijų skirtumus, laikas perkelti savo VBA scenarijų į kitą lygį. Dano straipsnis apie skaičiavimo ir pridėjimo funkcijų naudojimą „Excel“ leis sukurti dar sudėtingesnius scenarijus, kurie labai greitai gali sukaupti visų duomenų rinkinių vertes.

Ir jei dar tik pradedate naudotis VBA „Excel“, nepamirškite, kad turime fantastišką įvadinis „Excel VBA“ vadovas tau taip pat.

Dalintis Dalintis „Tweet“ Paštu „Canon“ prieš „Nikon“: kuris fotoaparato prekės ženklas yra geresnis?

„Canon“ ir „Nikon“ yra du didžiausi pavadinimai fotoaparatų pramonėje. Tačiau kuris prekės ženklas siūlo geresnę fotoaparatų ir objektyvų seriją?

Skaityti toliau Susijusios temos
  • Produktyvumas
  • Programavimas
  • Visual Basic programavimas
  • „Microsoft Excel“
Apie autorių Ryanas Dube(Paskelbti 942 straipsniai)

Ryanas turi elektrotechnikos bakalauro laipsnį. Jis 13 metų dirbo automatikos inžinerijos srityje, 5 metus IT srityje, o dabar yra programų inžinierius. Buvęs „MakeUseOf“ vadovaujantis redaktorius, jis kalbėjo nacionalinėse duomenų vizualizavimo konferencijose ir buvo rodomas nacionalinėje televizijoje ir radijuje.

Daugiau iš Ryan Dube

Prenumeruokite mūsų naujienlaiškį

Prisijunkite prie mūsų naujienlaiškio, kad gautumėte techninių patarimų, apžvalgų, nemokamų el. Knygų ir išskirtinių pasiūlymų!

Norėdami užsiprenumeruoti, spustelėkite čia