Excel -solujen ja aluefunktioiden ymmärtäminen VBA: ssa

Excel -solujen ja aluefunktioiden ymmärtäminen VBA: ssa

Excel on tehokas. Jos käytät sitä paljon, tiedät todennäköisesti jo paljon temppuja kaavojen tai automaattisen muotoilun avulla, mutta Solut ja Alue VBA -toimintoja, voit tehostaa Excel -analytiikkaasi aivan uudelle tasolle.





Ongelma solujen ja alueiden käyttämisessä VBA: ssa on se, että kehittyneillä tasoilla useimpien ihmisten on vaikea ymmärtää, miten nämä toiminnot todella toimivat. Niiden käyttäminen voi olla hyvin hämmentävää. Näin voit hyödyntää niitä tavoilla, joita et luultavasti koskaan kuvitellut.



Solut -toiminto

Solut ja alue -toimintojen avulla voit kertoa VBA -skriptisi tarkalleen mihin laskentataulukkoon haluat hakea tai sijoittaa tietoja. Suurin ero näiden kahden solun välillä on se, mihin ne viittaavat.





Solut yleensä viittaavat yhteen soluun kerrallaan, kun taas Alue viittaa soluryhmään kerralla. Tämän toiminnon muoto on Solut (rivi, sarake) .

Tämä viittaa jokaiseen soluun koko taulukossa. Tämä on yksi esimerkki siitä, että Solut -toiminto ei viittaa yhteen soluun:



Worksheets('Sheet1').Cells

Tämä viittaa ylärivin vasemmanpuoleiseen kolmanteen soluun. Solu C1:

Worksheets('Sheet1').Cells(3)

Seuraava koodi viittaa soluun D15:



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

Halutessasi voit viitata soluun D15 myös soluilla (15, D)-voit käyttää sarakkeen kirjainta.

On paljon joustavuutta voidakseen viitata soluun käyttämällä sarakkeen ja solun numeroa, etenkin skriptien avulla silmukka läpi suuri määrä soluja (ja suorittaa niihin laskutoimituksia) erittäin nopeasti. Tulemme siihen tarkemmin alla.



Alue -toiminto

Alue -toiminto on monella tapaa paljon tehokkaampi kuin solujen käyttö, koska sen avulla voit viitata joko yhteen soluun tai tiettyyn solualueeseen kerralla. Et halua kierrellä aluefunktiota, koska soluviittaukset eivät ole numeroita (ellet upota solutoimintoa sen sisälle).

Tämän toiminnon muoto on Alue (solu 1, solu #2) . Jokainen solu voidaan nimetä kirjain-numerolla.

Katsotaanpa muutamia esimerkkejä.

miten äänitän chromebookiin

Tässä aluefunktio viittaa soluun A5:

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

Tässä aluefunktio viittaa kaikkiin soluihin A1 - E20:

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

Kuten edellä mainittiin, sinun ei tarvitse käyttää numerokirjaimisia solumäärityksiä. Voit itse käyttää kahta solutoimintoa aluefunktion sisällä, jotta voit tunnistaa taulukon alueen, kuten tämä:


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

Yllä oleva koodi viittaa samaan alueeseen kuin alue ('A1: E20') -toiminto. Sen käytön arvo on, että sen avulla voit kirjoittaa koodin, joka toimii dynaamisesti alueiden kanssa silmukoiden avulla.

Nyt kun ymmärrät solujen ja alueiden toimintojen muotoilun, perehdytään siihen, miten voit käyttää näitä toimintoja luovasti VBA -koodissasi.

Tietojen käsittely solutoiminnolla

Solut -toiminto on hyödyllisin, kun sinulla on monimutkainen kaava, jonka haluat suorittaa useilla solualueilla. Nämä alueet voivat esiintyä myös useilla arkeilla.

Otetaan yksinkertainen esimerkki. Oletetaan, että johdat 11 hengen myyntitiimiä ja haluat joka kuukausi tarkastella heidän suorituksiaan.

Sinulla saattaa olla Taulukko 1 joka seuraa niiden myyntimäärää ja myyntimäärää.

Päällä Arkki 2 voit seurata heidän asiakkaidensa palautetta 30 viime päivän ajalta.

Jos haluat laskea ensimmäisen arkin bonuksen käyttämällä kahden arkin arvoja, voit tehdä tämän useilla tavoilla. Voit kirjoittaa kaavan ensimmäiseen soluun, joka suorittaa laskennan kahden taulukon tietojen avulla, ja vetää sen alas. Se onnistuu.

Vaihtoehtona tälle on VBA -komentosarjan luominen, jonka joko käynnistät, kun avaat arkin, tai laukaisun arkin komentopainikkeella, jotta voit hallita sen laskemista. Voit kuitenkin käyttää VBA -komentosarjaa kaikkien myyntitietojen hankkimiseen ulkoisesta tiedostosta joka tapauksessa.

Joten miksi et vain käynnistäisi saman sarakkeen bonussarakkeen laskelmia tuolloin?

Solut toimivat toiminnassa

Jos et ole koskaan kirjoittanut VBA: ta Excelissä, sinun on otettava käyttöön Kehittäjä -valikkokohta. Voit tehdä tämän siirtymällä osoitteeseen Tiedosto > Asetukset . Klikkaa Muokkaa nauhaa . Valitse lopuksi vasemmasta ruudusta Kehittäjä, Lisätä oikealle ruudulle ja varmista, että valintaruutu on valittuna.

Nyt kun napsautat OK ja palaa pääsivulle, näet kehittäjävalikon vaihtoehdon.

Voit käyttää Lisää Lisää komentopainike tai napsauta vain Näytä koodi aloittaaksesi koodauksen.

Tässä esimerkissä skripti suoritetaan aina, kun työkirja avataan. Voit tehdä tämän napsauttamalla Näytä koodi kehittäjävalikosta ja liitä seuraava uusi toiminto koodi -ikkunaan.

Private Sub Workbook_Open()
End Sub

Koodiikkunasi näyttää tältä.

Nyt olet valmis kirjoittamaan koodin laskennan käsittelemiseksi. Yhden silmukan avulla voit käydä läpi kaikki 11 työntekijää, ja Solut -toiminnon avulla voit hakea laskutoimitukseen tarvittavat kolme muuttujaa.

Muista, että Solut -toiminnolla on rivi ja sarake parametreina kunkin yksittäisen solun tunnistamiseksi. Teemme rivistä x, käytä kunkin sarakkeen tietoja numerolla. Rivien lukumäärä on työntekijöiden määrä, joten tämä on 1–11. Sarakkeen tunniste on 2 myyntilukua, 3 myyntimäärää ja 2 taulukkoa 2 varten palautepisteet.

Lopullisessa laskelmassa käytetään seuraavia prosenttiosuuksia lisätäkseen 100 prosenttia bonuspisteistä. Se perustuu ihanteelliseen myyntilukuun 50, myyntivolyymiin 50 000 dollaria ja palautepisteeseen 10.

  • (Myynnit/50) x 0,4
  • (Myyntivolyymi/50000) x 0,5
  • (Palautepisteet/10) x 0,1

Tämä yksinkertainen lähestymistapa antaa myyntityöntekijöille painotetun bonuksen. 50, volyymi 50 000 dollaria ja pisteet 10 --- he saavat koko kuukauden suurimman bonuksen. Kaikki mikä tahansa täydellisellä tavalla vähentää kuitenkin bonusta. Kaikki mikä on parempaa kuin ideaali, lisää bonusta.

Katsotaanpa nyt, kuinka kaikki tämä logiikka voidaan vetää pois yksinkertaisella, lyhyellä VBA -komentosarjalla:

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

Tämän skriptin tulos näyttää tältä.

mitä voit tehdä vadelmapiilla

Jos haluat, että Bonus -sarake näyttää todellisen dollarin bonuksen prosenttiosuuden sijasta, voit kertoa sen enimmäismäärällä. Parempi vielä, aseta tämä määrä toisen arkin soluun ja viittaa siihen koodissasi. Tämä helpottaisi arvon muuttamista myöhemmin ilman koodin muokkaamista.

Solut -toiminnon kauneus on, että voit rakentaa melko luovaa logiikkaa datan ottamiseksi monia soluja monilla eri arkeilla ja suorita joitakin aika monimutkaisia ​​laskelmia heidän kanssaan.

Voit suorittaa kaikenlaisia ​​toimintoja soluille käyttämällä Solut-toimintoa --- esimerkiksi solujen tyhjentämistä, fontin muotoilun muuttamista ja paljon muuta.

Jos haluat tutkia kaikkea, mitä voit tehdä edelleen, tutustu Microsoftin MSDN -sivu Cells -objektille.

Solujen muotoilu aluetoiminnolla

Solut -toiminto on täydellinen useiden solujen silmukkaamiseen yksi kerrallaan. Mutta jos haluat soveltaa jotain koko solualueelle kerralla, alue -toiminto on paljon tehokkaampi.

Yksi tapaus tähän voi olla solualueen muotoileminen komentosarjan avulla, jos tietyt ehdot täyttyvät.

Oletetaan esimerkiksi, että jos kaikkien myyntityöntekijöiden myyntivolyymi on yhteensä yli 400 000 dollaria, haluat korostaa kaikki bonussarakkeen solut vihreällä merkiksi siitä, että joukkue on ansainnut ylimääräisen tiimibonuksen.

Katsotaanpa, miten voit tehdä sen IF -lausunto .

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

Jos solu ylittää joukkuetavoitteen, kaikki alueen solut täytetään vihreänä, kun tämä suoritetaan.

Tämä on vain yksi yksinkertainen esimerkki monista toiminnoista, jotka voit suorittaa soluryhmille käyttämällä Range -toimintoa. Muita asioita, joita voit tehdä, ovat:

  • Käytä ääriviivoja ryhmän ympärille
  • Tarkista tekstin oikeinkirjoitus solualueella
  • Tyhjennä, kopioi tai leikkaa soluja
  • Hae valikoimaa Etsi -menetelmällä
  • Paljon enemmän

Muista lukea Microsoftin MSDN -sivu jotta Range -objekti näkee kaikki mahdollisuudet.

Vie Excel seuraavalle tasolle

Nyt kun ymmärrät solujen ja aluefunktioiden väliset erot, on aika viedä VBA -komentosarjasi seuraavalle tasolle. Dannin artikkeli laskenta- ja lisäämistoimintojen käyttämisestä Excelissä antaa sinun rakentaa vieläkin kehittyneempiä komentosarjoja, jotka voivat kerätä arvoja kaikkiin tietojoukkoihisi nopeasti.

Ja jos olet vasta aloittamassa VBA: ta Excelissä, älä unohda, että meillä on fantastinen Excel VBA: n johdanto -opas myös sinulle.

Jaa Jaa Tweet Sähköposti Canon vs.Nikon: Mikä kameramerkki on parempi?

Canon ja Nikon ovat kamerateollisuuden kaksi suurinta nimeä. Mutta mikä merkki tarjoaa paremman valikoiman kameroita ja objektiiveja?

Lue seuraava Liittyvät aiheet
  • Tuottavuus
  • Ohjelmointi
  • Visual Basic -ohjelmointi
  • Microsoft Excel
Kirjailijasta Ryan Dube(942 artikkelia julkaistu)

Ryanilla on BSc -tutkinto sähkötekniikasta. Hän on työskennellyt 13 vuotta automaatiotekniikassa, 5 vuotta IT -alalla ja on nyt sovellusinsinööri. MakeUseOfin entinen toimituspäällikkö, hän on puhunut kansallisissa datan visualisointia käsittelevissä konferensseissa ja hän on esiintynyt kansallisessa televisiossa ja radiossa.

Lisää Ryan Dubelta

tilaa uutiskirjeemme

Liity uutiskirjeeseemme saadaksesi teknisiä vinkkejä, arvosteluja, ilmaisia ​​e -kirjoja ja ainutlaatuisia tarjouksia!

Klikkaa tästä tilataksesi