Kanssa Soluja ja Solualueita, Excel VBA (Valitse, Kopioi, Siirrä, Muokkaa)
Kun työskentelee Excel, suurimman osan aikaa on käytetty laskentataulukon alue – käsittelee soluja ja alueita.
Ja jos haluat automatisoida työsi Excel VBA: n avulla, sinun täytyy tietää, miten työskennellä solut ja alueet VBA: n avulla.
VBA: ssa on paljon erilaisia asioita, joita voi tehdä vaihteluväleillä (kuten select, copy, move, edit jne.).
joten voit käsitellä tätä aihetta, aion rikkoa tämän opetusohjelman osiin ja näyttää, miten työskennellä solujen ja alueiden kanssa Excel VBA käyttäen esimerkkejä.,
aloitetaan.
Jos olet kiinnostunut oppimaan VBA helppo tapa tutustu Online-Excel VBA-Koulutus.
Tämä Opetusohjelma Kattaa:
Valitsemalla Solu / Alue Excel VBA: n avulla
työskennellä solut ja alueet Excel VBA: n avulla, sinun ei tarvitse valita sitä.
useimmissa tapauksissa on parempi olla valitsematta soluja tai alueita (kuten tulemme näkemään).,
siitä huolimatta on tärkeää, että käyt tämän osion läpi ja ymmärrät, miten se toimii. Tämä on ratkaisevan tärkeää teidän VBA oppiminen ja paljon käsitteitä tässä käytetään koko tämän opetusohjelman.
joten aloitetaan hyvin yksinkertaisesta esimerkistä.
Valitset Yhden Solun Käyttämällä VBA
Jos haluat valita yksittäisen solun aktiivinen arkki (sanoa, A1), niin voit käyttää alla koodi:
Sub SelectCell()Range("A1").SelectEnd Sub
edellä koodi on pakollinen ’Sub” ja ”End Sub’ osa, ja rivi koodia, joka valitsee soluun A1.,
Range(”A1”) kertoo VBA: lle solun osoitteen, johon haluamme viitata.
Select on Alueobjektin menetelmä ja valitsee Range-objektissa määritellyt solut / alueet. Soluviittaukset on liitettävä kahteen sitaattiin.
tämä koodi osoittaisi virheen, jos karttalevy on aktiivinen arkki. Karttalevy sisältää kaavioita, eikä sitä käytetä laajalti. Koska siinä ei ole soluja/alueita, yllä oleva koodi ei voi valita sitä ja päätyisi näyttämään virhettä.
Huomaa, että kun haluat valita solun aktiivisen levyn, sinun tarvitsee vain määrittää solun osoite.,
Mutta, jos haluat valita solun toinen arkki (sanotaan Taulukko2), sinun täytyy ensin aktivoida Taul2 ja valitse sitten solu, se.
Sub SelectCell()Worksheets("Sheet2").ActivateRange("A1").SelectEnd Sub
vastaavasti voit myös aktivoida työkirjan, sitten aktivoida siihen tietyn laskentataulukon ja sitten valita solun.
Sub SelectCell()Workbooks("Book2.xlsx").Worksheets("Sheet2").ActivateRange("A1").SelectEnd Sub
Huomaa, että kun viitataan työkirjoja, sinun täytyy käyttää koko nimi sekä tiedostotunniste (.xlsx yllä olevassa koodissa). Jos työkirjaa ei ole koskaan tallennettu, sinun ei tarvitse käyttää tiedostotunnistetta.,
Nyt, nämä esimerkit eivät ole kovin hyödyllisiä, mutta näet myöhemmin tässä opetusohjelmassa, miten voimme käyttää samoja käsitteitä kopioida ja liittää soluja Excel (VBA).
juuri kun valitsemme solun, voimme myös valita vaihteluvälin.
vaihteluvälin tapauksessa kyseessä voi olla kiinteä kokoalue tai muuttuva kokoalue.
kiinteällä kokoalueella tietäisit, kuinka suuri alue on ja voit käyttää VBA-koodisi tarkkaa kokoa. Vaihtelevan kokoisella mallistolla ei kuitenkaan ole hajuakaan, kuinka suuri valikoima on, ja täytyy käyttää hieman VBA-magiaa.
katsotaan, miten tämä tehdään.,
Fix-kokoisen alueen valitseminen
tässä on koodi, joka valitsee alueen A1:D20.
Sub SelectRange()Range("A1:D20").SelectEnd Sub
Toinen tapa tehdä tämä on käyttää alla koodi:
Sub SelectRange()Range("A1", "D20").SelectEnd Sub
yllä Oleva koodi tekee top-vasen solun osoitteen (A1) ja oikeassa alareunassa solun osoite (D20) ja valitsee koko alueella. Tämä tekniikka on hyödyllinen, kun olet työskennellyt vaihtelevasti kokoinen vaihtelee (kuten näemme, kun Lopussa omaisuutta käsitellään myöhemmin tässä oppaassa).,
Jos haluat valinta tapahtuu eri työkirjassa tai eri laskentataulukossa, sitten sinun täytyy kertoa VBA-tarkat nimet nämä esineet.
esimerkiksi alla oleva koodi valitsisi vaihteluvälin A1:D20 Sheet2-laskentataulukossa Book2-työkirjassa.
Sub SelectRange()Workbooks("Book2.xlsx").Worksheets("Sheet1").ActivateRange("A1:D20").SelectEnd Sub
Nyt, mitä jos et tiedä, kuinka monta riviä ovat siellä. Mitä jos haluat valita kaikki solut, joilla on arvo siinä.
näissä tapauksissa, sinun täytyy käyttää menetelmiä on esitetty seuraavassa luvussa (valitsemalla vaihtelevasti kokoinen alue).,
Valitsemalla Vaihtelevasti Kokoinen Alue
On olemassa erilaisia tapoja, joilla voit valita solualueen. Valitsemasi menetelmä riippuisi siitä, miten tiedot on jäsennetty.
tässä osiossa aion kattaa joitakin hyödyllisiä tekniikoita, jotka ovat todella hyödyllisiä, kun työskentelet vaihtelee VBA.
Valitse Käyttäen CurrentRange Omaisuutta
tapauksissa, joissa et tiedä kuinka monta riviä/saraketta ovat tiedot, voit käyttää CurrentRange omaisuutta Range-objekti.
Currentrangen ominaisuus kattaa kaikki data-alueella olevat vierekkäiset täytetyt solut.,
Alla on koodi, joka valitsee nykyisen alueen, joka pitää solun A1.
Sub SelectCurrentRegion()Range("A1").CurrentRegion.SelectEnd Sub
edellä menetelmä on hyvä, kun sinulla on kaikki tiedot, kuten pöytä, ilman tyhjiä rivejä/sarakkeita se.
Mutta jos sinulla on tyhjiä rivejä/sarakkeita tietojen, se ei valitse niitä, kun tyhjiä rivejä/sarakkeita. Alla olevassa kuvassa CurrentRegion-koodi valitsee tiedot riviin 10 asti, koska rivi 11 on tyhjä.
tällaisissa tapauksissa, et ehkä halua käyttää UsedRange omaisuutta-Laskentataulukko-Objektin.,
Valitse Käyttäen UsedRange Omaisuutta
UsedRange avulla voit viitata tahansa soluja, jotka ovat muuttuneet.
joten alla oleva koodi valitsisi kaikki aktiivilevyn käytetyt solut.
Sub SelectUsedRegion()ActiveSheet.UsedRange.SelectEnd Sub
Huomaa, että jos sinulla on far-off-solu, joka on käytetty, se olisi pidettävä edellä koodi ja kaikki solut, kunnes se käytetään solu olisi valittu.
valitse käyttämällä Loppuomaisuutta
nyt, tämä osa on todella hyödyllinen.
lopun ominaisuuden avulla voit valita viimeisen täytetyn solun., Tämän avulla voit jäljitellä vaikutus Control alas / ylös nuolinäppäin tai Control Oikea/Vasen näppäimiä.
yritetään ymmärtää tätä esimerkin avulla.
Oletetaan, että sinulla on aineisto, kuten on esitetty alla ja haluat nopeasti valitse viimeinen täynnä solut sarakkeessa A.
ongelma tässä on se, että tietoja voidaan vaihtaa ja et tiedä, kuinka monta solut ovat täynnä. Jos sinun täytyy tehdä tämä näppäimistöllä, voit valita solun A1, ja sitten käyttää Control + nuolinäppäintä, ja se valitsee viimeisen täytetty solu sarakkeessa.
nyt katsotaan, miten tämä tehdään VBA: n avulla., Tämä tekniikka on kätevä, kun haluat nopeasti hypätä viimeinen täytetty solu vaihtelevasti kokoinen sarakkeeseen
Sub GoToLastFilledCell()Range("A1").End(xlDown).SelectEnd Sub
edellä koodi olisi hypätä viimeinen täytetty solu sarakkeessa A.
Samalla tavalla, voit käyttää Lopussa(xlToRight) hypätä viime täytetty solu peräkkäin.
Sub GoToLastFilledCell()Range("A1").End(xlToRight).SelectEnd Sub
Nyt, mitä jos haluat valita koko sarakkeen sijasta hyppäämällä viime täytetty solu.,
Voit tehdä, että käyttämällä koodi alla:
Sub SelectFilledCells()Range("A1", Range("A1").End(xlDown)).SelectEnd Sub
edellä koodi, olemme käyttäneet ensimmäinen ja viimeinen viittaus soluun, joka meidän täytyy valita. Riippumatta siitä, kuinka monta täytettyä solua on olemassa, edellä oleva koodi valitsee kaikki.
Muista, esimerkiksi yläpuolella, jossa valitaan alue A1:D20 käyttämällä seuraavaa koodiriviä:
Range(”A1″,”D20”)
Tässä A1 oli ylhäältä vasemmalla olevan solun ja D20 oli oikeassa alareunassa solun välillä. Voimme käyttää samaa logiikkaa valitessamme vaihtelevan kokoisia valikoimia., Mutta koska emme tiedä oikean solun tarkkaa osoitetta, käytimme päätykiinteistöä saadaksemme sen.
alueella(”A1”, vaihteluväli(”A1″).End (xlDown)),” A1″tarkoittaa ensimmäistä solua ja kantamaa (”A1”).End (xlDown) viittaa viimeiseen soluun. Koska olemme toimittaneet molemmat viittaukset, Valitse menetelmä valitsee kaikki solut näiden kahden viittauksen välillä.
vastaavasti voit valita myös kokonaisen tietojoukon, jossa on useita rivejä ja sarakkeita.
alla oleva koodi valitsisi kaikki täytetyt rivit / sarakkeet alkaen solusta A1.,
Sub SelectFilledCells()Range("A1", Range("A1").End(xlDown).End(xlToRight)).SelectEnd Sub
yllä olevassa koodissa on käytetty vaihteluväliä(”A1”).Loppu (xlDown).End (xlToRight) saada viittaus pohja-oikea täytetty solu dataset.
Ero Käyttäen CurrentRegion ja Loppuun
Jos olet ihmettelevät, miksi käyttää Loppuun omaisuuden valitse täytetty alue, kun meillä on CurrentRegion omaisuutta, haluan kertoa teille ero.
End-ominaisuuden avulla voit määrittää aloitussolun., Esimerkiksi, jos sinulla on TIEDOT A1:D20, mutta ensimmäinen rivi ovat otsikot, voit käyttää loppuun ominaisuus valita tiedot ilman otsikoita (käyttämällä koodia alla).
Sub SelectFilledCells()Range("A2", Range("A2").End(xlDown).End(xlToRight)).SelectEnd Sub
Mutta CurrentRegion olisi automaattisesti valitse koko aineisto, mukaan lukien otsikot.
tähän mennessä tässä opetussuunnitelmassa on nähty, miten eri tavoin voidaan viitata erilaisiin soluihin.
Nyt katsotaanpa joitakin tapoja, joilla voimme todella käyttää näitä tekniikoita saada joitakin työstä.,
Kopioi soluja/vaihteluvälejä käyttäen VBA
kuten mainitsin tämän opetusohjelman alussa, solun valitseminen ei ole tarpeen sen toimintojen suorittamiseksi. Näet tässä osiossa, miten kopioida soluja ja alueita edes valitsematta näitä.
aloitetaan yksinkertaisesta esimerkistä.
Kopiointi Yksittäinen Solu
Jos haluat kopioida solun A1 ja liitä se soluun D1, alla koodi olisi tehdä se.
Sub CopyCell()Range("A1").Copy Range("D1")End Sub
Huomaa, että kopio menetelmä range-objektin kopioita solu (kuten Ctrl +C) ja liittää sen määritettyyn kohteeseen.,
yllä olevassa esimerkkikoodissa kohde on määritelty samalla rivillä, jossa käytetään Kopiomenetelmää. Jos haluat tehdä oman koodin jopa enemmän luettavissa, voit käyttää alla koodi:
Sub CopyCell()Range("A1").Copy Destination:=Range("D1")End Sub
edellä mainitut koodit kopioi ja liitä-arvo sekä muotoilu/kaavat siihen.
kuten Olet ehkä jo huomannut, yllä oleva koodi kopioi solun valitsematta sitä. Riippumatta siitä, missä olet laskentataulukossa, koodi kopioi solun A1 ja liittää sen D1: een.
huomaa myös, että yllä oleva koodi korvaa minkä tahansa solun D2 olemassa olevan koodin., Jos haluat Excel kertoa, jos on jo jotain solun D1 ilman merkintää sitä, voit käyttää koodia alla.
Kopiointi Korjata Kokoinen Alue
Jos haluat kopioida A1:D20 J1:M20, voit käyttää alla koodi:
Sub CopyRange()Range("A1:D20").Copy Range("J1")End Sub
kohde solu, sinun tarvitsee vain määrittää osoite ylhäällä vasemmalla solu. Koodi kopioisi tarkan kopioalueen automaattisesti kohteeseen.
samalla konstruktiolla voi kopioida tietoja yhdeltä arkilta toiselle.
alla Oleva koodi kopioi A1:D20 aktiivinen arkki Taulukko2.,
Sub CopyRange()Range("A1:D20").Copy Worksheets("Sheet2").Range("A1")End Sub
edellä kopioi tiedot aktiivisesta arkki. Joten varmista, että arkki, joka on tiedot on aktiivinen arkki ennen koodin. Turvalliseksi, voit myös määrittää laskentataulukon nimen kopioinnin tiedot.
Sub CopyRange()Worksheets("Sheet1").Range("A1:D20").Copy Worksheets("Sheet2").Range("A1")End Sub
hyvä asia edellä koodi on, että ei väliä mikä levy on aktiivinen, se on aina kopioida tietoja Taulukko1 ja liitä se Taulukko2.
nimettyä valikoimaa voi kopioida myös käyttämällä sen nimeä viittauksen sijaan.,
esimerkiksi, jos sinulla on nimetty alue nimeltään ’SalesData’, voit käyttää alla oleva koodi kopioi tiedot Taulukko2.
Sub CopyRange()Range("SalesData").Copy Worksheets("Sheet2").Range("A1")End Sub
Jos soveltamisala nimetty alue on koko työkirjan, sinun ei tarvitse olla levy, joka on nimetty alue suorittaa tämän koodin. Koska nimettyä valikoimaa on scopattu työkirjaa varten, voit käyttää sitä mistä tahansa arkista tätä koodia käyttäen.
Jos sinulla on taulukko, jonka nimi on Taulukko1, voit käyttää alla oleva koodi kopioi se Taulukko2.
Sub CopyTable()Range("Table1").Copy Worksheets("Sheet2").Range("A1")End Sub
Voit myös kopioida alue toiseen Työkirjaan.,
seuraavassa esimerkissä kopioin Excel-taulukon (taulukko 1) Book2-työkirjaan.
Sub CopyCurrentRegion()Range("Table1").Copy Workbooks("Book2.xlsx").Worksheets("Sheet1").Range("A1")End Sub
Tämä koodi toimisi vain, jos Työkirja on jo auki.
Kopiointi Vaihtelevan Kokoinen Alue
Yksi tapa kopioida muuttujan kokoinen alueet on muuntaa nämä osaksi nimeltä vaihtelee tai Excel-Taulukko ja käytä koodeja, kuten on esitetty edellisessä luvussa.
mutta jos et pysty siihen, voit käyttää range-objektin Currentregionia tai pääteomaisuutta.
alla Oleva koodi kopioi nykyisen alueen aktiivinen arkki ja liitä se Taulukko2.,
Sub CopyCurrentRegion()Range("A1").CurrentRegion.Copy Worksheets("Sheet2").Range("A1")End Sub
Jos haluat kopioida vain ensimmäisen sarakkeen tiedot set kunnes viimeinen täytetty solu ja liitä se Taulukko2, voit käyttää alla koodi:
Sub CopyCurrentRegion()Range("A1", Range("A1").End(xlDown)).Copy Worksheets("Sheet2").Range("A1")End Sub
Jos haluat kopioida rivejä sekä sarakkeita, voit käyttää alla oleva koodi:
Sub CopyCurrentRegion()Range("A1", Range("A1").End(xlDown).End(xlToRight)).Copy Worksheets("Sheet2").Range("A1")End Sub
Huomaa, että kaikki nämä koodit älä valitse solut, samalla saada teloitettiin. Yleensä, löydät vain kourallinen tapauksia, joissa sinun todella täytyy valita solu/alue ennen työstää sitä.,
määrittämällä vaihteluvälit Objektimuuttujille
toistaiseksi olemme käyttäneet solujen täyttä osoitetta (kuten työkirjoja(”Book2.xlsx”).Laskentataulukot (”Sheet1”).Alue(”A1”).
jotta koodisi olisi helpommin hallittavissa, voit määrittää nämä alueet objektimuuttujille ja sitten käyttää näitä muuttujia.
esimerkiksi alla koodi, en ole määritetty lähde-ja kohde-alue-objektin muuttujia, ja sitten käytetään näitä muuttujia kopioida tietoja yksi alue toiseen.
aloitamme julistamalla muuttujat Vaihteluväliobjekteiksi., Sitten määritämme vaihteluvälin näille muuttujille Asetuslaskelman avulla. Kun vaihteluväli on annettu muuttujalle, voit käyttää muuttujaa.
Syötä Tiedot Seuraavassa Tyhjä Solu (Käyttäen Input Laatikko)
Voit käyttää Input laatikot, jotta käyttäjä voi syöttää tietoja.
esimerkiksi, oletetaan, että sinulla on data set alla ja haluat syöttää myynti ennätys, voit käyttää syöttöruutuun VBA. Koodin avulla voimme varmistaa, että se täyttää tiedot seuraavalla tyhjällä rivillä.,
yllä Oleva koodi käyttää VBA-syöttöruutuun saada tuotantopanokset käyttäjä, ja sitten tulee panoksina määritelty soluja.
huomaa, että emme käyttäneet tarkkoja soluviittauksia. Sen sijaan, olemme käyttäneet loppuun ja Offset omaisuutta löytää viimeinen tyhjä solu ja täyttää tiedot siinä.
tämä koodi ei ole läheskään käyttökelpoinen. Jos esimerkiksi syötät tekstijonon, kun syöttölaatikko pyytää määrää tai määrää, huomaat, että Excel sallii sen. Voit käyttää If-ehtoa tarkistaaksesi, onko arvo numeerinen vai ei, ja salli se sen mukaisesti.,
Silmukoiden Läpi Soluja / Solualueita
toistaiseksi voimme nähdä, miten voit valita, kopioida, ja kirjoita tiedot soluihin ja alueet.
tässä jaksossa nähdään, miten kierretään joukon soluja/rivejä / sarakkeita välillä. Tämä voi olla hyödyllistä, kun haluat analysoida kunkin solun ja suorittaa joitakin toimia sen perusteella.
esimerkiksi, jos haluat korostaa joka kolmannen rivin valinnassa, sinun on kierrettävä läpi ja tarkistettava rivinumero., Samoin, jos haluat korostaa kaikki negatiiviset solut muuttamalla fontin väriä punaiseksi, sinun täytyy silmukan läpi ja analysoida kunkin solun arvo.
tässä on koodi, joka kiertää rivien läpi valituissa soluissa ja korostaa vaihtoehtoisia rivejä.
Sub HighlightAlternateRows()Dim Myrange As RangeDim Myrow As RangeSet Myrange = SelectionFor Each Myrow In Myrange.RowsIf Myrow.Row Mod 2 = 0 ThenMyrow.Interior.Color = vbCyanEnd IfNext MyrowEnd Sub
yllä Oleva koodi käyttää MOD-toiminto tarkistaa rivin numeron valinta. Jos rivinumero on tasainen, se korostuu syaanivärissä.
tässä on toinen esimerkki, jossa koodi menee jokaisen solun läpi ja korostaa soluja, joilla on negatiivinen arvo siinä.,
Sub HighlightAlternateRows()Dim Myrange As RangeDim Mycell As RangeSet Myrange = SelectionFor Each Mycell In MyrangeIf Mycell < 0 ThenMycell.Interior.Color = vbRedEnd IfNext MycellEnd Sub
Huomaa, että voit tehdä saman käyttäen Ehdollista Muotoilua (joka on dynaaminen ja parempi tapa tehdä tämä). Tämä esimerkki on vain tarkoitus osoittaa, miten silmukoiden toimii soluja ja solualueita, VBA.
Mihin VBA-Koodi
Ihmettelet, jossa VBA-koodi menee Excel-työkirja?
Excelissä on VBA-taustalehti nimeltä VBA editor. Sinun täytyy kopioida ja liittää koodi VB Editor module koodi ikkuna.
tässä ovat vaiheet tehdä tämä:
- Siirry Kehittäjä-välilehteen.,
- Klikkaa Visual Basic vaihtoehto. Tämä avaa VB-editorin taustatiedoissa.
- Hankkeen Explorer ruudussa VB-Editorissa, napsauta mitä tahansa esinettä työkirja, johon haluat lisätä koodin. Jos et näe Project Explorer, siirry Näytä välilehti ja klikkaa Project Explorer.
- siirry kohtaan Lisää ja klikkaa moduulia. Tämä asettaa työkirjaasi varten moduuliobjektin.
- kopioi ja liitä koodi moduulin ikkunaan.,
Voit Myös Seuraavat Excel Tutorials:
- Työskentely Laskentataulukoita VBA: n avulla.
- työskentelee työkirjojen kanssa käyttäen VBA: ta.
- käyttäjien määrittelemien toimintojen luominen Exceliin.
- for Next Loop in Excel VBA – a Beginner ’ s Guide with Examples.
- miten Excel VBA InStr-toimintoa käytetään (käytännön esimerkein).
- Excel VBA Msgbox.
- miten makro kirjataan Exceliin.
- miten toimii makro Excelissä.
- Miten luodaan lisäosa Exceliin.,
- Excel Personal Macro Workbook/Save & käytä makroja kaikissa työkirjoissa.
- Excel VBA Events – an Easy (and Complete) Guide.
- Excel VBA Error Handling.
- tietojen lajittelu Excelissä VBA: n (a Step-by-Step Guide) avulla.
- 24 hyödyllistä Excel – Makroesimerkkiä VBA-aloittelijoille (käyttövalmis).