Excel er kraftig. Hvis du bruker det mye, vet du sikkert allerede mange triks ved hjelp av formler eller automatisk formatering, men ved hjelp av Cells and Range- funksjoner i VBA, kan du øke Excel-analysene til et helt nytt nivå.
Problemet med å bruke Cells and Range-funksjonene i VBA er at på avanserte nivåer har de fleste vanskelig å forstå hvordan disse funksjonene egentlig fungerer. Å bruke dem kan bli veldig forvirrende. Slik kan du gjøre bruk av dem på måter du sannsynligvis aldri har forestilt deg.
Cellens funksjon
Cells and Range-funksjonene lar deg fortelle VBA-skriptet ditt 4 Feil du kan unngå når du programmerer Excel Makroer med VBA 4 feil du kan unngå når du programmerer Excel Makroer med VBA Enkel kode og makroer er nøklene til Microsoft Excel supermakt. Selv ikke-programmører kan enkelt legge til imponerende funksjonalitet i regnearkene med Virtual Basics for Applications (VBA). Bare unngå disse programmeringsbegynnerfeilene! Les mer nøyaktig hvor i regnearket du vil skaffe, eller plasser data. Hovedforskjellen mellom de to cellene er hva de refererer til.
Celler refererer vanligvis til en enkelt celle om gangen, mens Range refererer til en gruppe celler samtidig. Formatet for denne funksjonen er Celler (rad, kolonne) .
Dette refererer til hver enkelt celle i hele arket. Det er det eneste eksemplet hvor Cells-funksjonen ikke refererer til en enkelt celle:
Worksheets("Sheet1").Cells Dette refererer til den tredje cellen fra venstre, på den øverste raden. Celle C1:
Worksheets("Sheet1").Cells(3) Følgende kodereferanser celle D15:
Worksheets("Sheet1").Cells(15, 4) Hvis du vil, kan du også referere til celle D15 med "Celler (15, " D ")" - du har lov til å bruke kolonnebrevet.
Det er mye fleksibilitet i å kunne referere til en celle ved å bruke et tall for kolonne og celle, spesielt med skript som kan løpe gjennom et stort antall celler (og utføre beregninger på dem) veldig raskt. Vi kommer nærmere til det nedenfor.
Range-funksjonen
På mange måter er Range-funksjonen langt kraftigere enn å bruke celler, fordi den lar deg referere til enten en enkelt celle eller et bestemt utvalg av celler, alt på en gang. Du kommer ikke til å lykkes gjennom en Range-funksjon, fordi referansene for celler ikke er tall (med mindre du legger inn cellens funksjon i det).
Formatet for denne funksjonen er Range (Cell # 1, Cell # 2) . Hver celle kan betegnes med et bokstavnummer.
La oss se på noen få eksempler.
Her refererer rekkefølgefunksjonen til celle A5:
Worksheets("Sheet1").Range("A5") Her refererer rekkefølgefunksjonen til alle celler mellom A1 til E20:
Worksheets("Sheet1").Range("A1:E20") Som nevnt ovenfor trenger du ikke å bruke tallboks-celleoppgaver. Du kan faktisk bruke to cellefunksjoner inne i en Range-funksjon for å identifisere et område på arket slik:
With Worksheets("Sheet1") .Range(.Cells(1, 1), _ .Cells(20, 5)) End With Koden ovenfor refererer til samme område som funksjonen Range ("A1: E20") gjør. Verdien i å bruke den er at det vil tillate deg å skrive kode som dynamisk fungerer med områder ved hjelp av løkker. Hvordan gjør-mens looper jobber med dataprogrammering. Hvordan gjør-mens looper jobber med dataprogrammering Looper er en av de første kontrolltypene du ' ll lære i programmering. Du vet sannsynligvis om mens og for looper, men hva oppnår en runde? Les mer .
Nå som du forstår hvordan du formaterer Celler og Range-funksjonene, la oss dykke på hvordan du kan gjøre kreativ bruk av disse funksjonene i VBA-koden din.
Behandling av data med cellefunksjon
Cell-funksjonen er mest nyttig når du har en kompleks formel som du vil utføre på tvers av flere celler. Disse områdene kan også eksistere på tvers av flere ark.
La oss ta et enkelt eksempel. La oss si at du administrerer et salgsteam på 11 personer, og hver måned vil du se på resultatene deres.
Du kan ha Sheet1 som følger deres salgstall, og deres salgsvolum.
På Sheet2 er du sporet sin tilbakemelding vurdering de siste 30 dagene fra bedriftens kunder.
Hvis du vil beregne bonusen på det første arket ved hjelp av verdier fra de to arkene, er det flere måter å gjøre dette på. Du kan skrive en formel i den første cellen som utfører beregningen ved å bruke data over de to arkene, og dra den ned. Det vil fungere.
Et alternativ til dette er å skape VBA-skript som du enten utløser for å løpe når du åpner arket eller utløses av en kommandoknapp på arket slik at du kan kontrollere når det beregnes. Du kan kanskje bruke et VBA-skript for å trekke inn alle salgsdata fra en ekstern fil uansett.
Så hvorfor ikke bare utløse beregningene for bonuskolonnen i det samme skriptet på det tidspunktet?
Cellens funksjon i aksjon
Hvis du aldri har skrevet VBA i Excel før, må du aktivere menyelementet Utvikler. For å gjøre dette, gå til Fil > Valg . Klikk på Tilpass bånd . Til slutt velger du Utvikler fra venstre rute, Legg til den i høyre rute, og kontroller at avkrysningsruten er valgt.
Nå, når du klikker OK og går tilbake til hovedarket, ser du alternativet Utvikler-meny.
Du kan bruke Sett inn- menyen for å sette inn en kommandoknapp, eller bare klikk Vis kode for å starte kodingen.
I dette eksemplet får vi skriptet til å løpe hver gang arbeidsboken åpnes. For å gjøre dette, klikker du bare på Vis kode fra utviklermenyen, og lim inn følgende nye funksjon i kodevinduet.
Private Sub Workbook_Open() End Sub Kodesvinduet ser ut som dette.
Nå er du klar til å skrive koden for å håndtere beregningen. Ved å bruke en enkelt loop kan du gå gjennom alle 11 ansatte, og med Celle-funksjonen trekkes inn de tre variablene som trengs for beregningen.
Husk at Cells-funksjonen har rad og kolonne som parametere for å identifisere hver enkelt celle. Vi lager "x" raden, bruk et tall for å be om hver kolonnes data. Antallet av rader er antall ansatte, så dette vil være fra 1 til 11. Kolonnidentifikatoren vil være 2 for salgstal, 3 for salgsvolum og 2 fra ark 2 for tilbakemeldingspoeng.
Den endelige beregningen bruker følgende prosentpoeng for å legge opp til 100 prosent av den totale bonuspoengsummen. Det er basert på et ideelt salgstall som er 50, salgsvolum som $ 50 000, og en tilbakemelding på 10.
- (Salgstall / 50) x 0, 4
- (Salgsvolum / 50 000) x 0, 5
- (Tilbakemeldingspoeng / 10) x 0, 1
Denne enkle tilnærmingen gir salgsansatte en vektet bonus. For en telling på 50, volum på $ 50.000, og en poengsum på 10-får de hele maksimal bonus for måneden. Men alt under perfekt på noen faktor reduserer bonusen. Noe bedre enn ideelt øker bonusen.
La oss se hvordan all den logikken kan trekkes av i et veldig enkelt, kort 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 Dette er hva resultatet av dette skriptet vil se ut.
Hvis du vil ha bonuskolonnen, viser den faktiske dollarbonusen i stedet for prosentandelen, kan du multiplisere den med det maksimale bonusbeløpet. Enda bedre, plasser det beløpet i en celle på et annet ark, og referer det til i koden din. Dette vil gjøre det lettere å endre verdien senere uten å måtte redigere koden din.
Den vakre funksjonen Cells er at du kan bygge litt pen kreativ logikk for å trekke inn data fra mange celler på tvers av mange forskjellige ark, og utføre noen ganske komplekse beregninger med dem.
Du kan utføre alle slags handlinger på celler ved hjelp av Cells-funksjonen - ting som å rydde opp cellene, endre skriftformatering og mye mer.
For å utforske alt du kan gjøre videre, sjekk ut Microsoft MSDN-siden for Cells-objektet.
Formatering av celler med rekkeviddefunksjon
For å løpe gjennom mange celler en om gangen, er Celle-funksjonen perfekt. Men hvis du vil bruke noe på et helt utvalg av celler samtidig, er Range-funksjonen langt mer effektiv.
En brukstilstand for dette kan være å formatere en rekke celler ved hjelp av skript, hvis visse betingelser er oppfylt.
For eksempel, la oss si om resultatet av all salgsvolum på alle salgsmedarbeidere overstiger $ 400.000 totalt, vil du markere alle cellene i bonuskolonnen i grønt for å indikere at laget har fått en ekstra lagbonus.
La oss se på hvordan du kan gjøre det med en IF-setning Slik bruker du IF-setninger i Microsoft Excel Slik bruker du IF-setninger i Microsoft Excel Uansett om du er en erfaren ekspert eller et regnearkbegynner, vil du sjekke ut dette guide til IF-setninger i Excel. Les mer .
Private Sub Workbook_Open() If Worksheets("Sheet1").Cells(13, 3).Value>400000 Then ActiveSheet.Range("D2:D12").Interior.ColorIndex = 4 End If End Sub Når dette løper, hvis cellen er over lagmålet, fylles alle celler i intervallet i grønt.
Dette er bare et enkelt eksempel på de mange handlingene du kan utføre på grupper av celler ved hjelp av Range-funksjonen. Andre ting du kan gjøre inkluderer:
- Bruk en oversikt rundt gruppen
- Kontroller stavingen av tekst i en rekke celler
- Fjern, kopiere eller kutte celler
- Søk gjennom en rekkevidde med "Finn" -metoden
- Mye mer
Sørg for å lese Microsoft MSDN-siden for Range-objektet for å se alle mulighetene.
Ta Excel til neste nivå
Nå som du forstår forskjellene mellom Celler og Range-funksjonene, er det på tide å ta VBA-skripting til neste nivå. Danns artikkel om bruk av telling og legge til funksjoner i Excel vil tillate deg å bygge enda mer avanserte skript som kan samle verdier på tvers av alle datasettene dine veldig raskt.
Og hvis du bare begynner med VBA i Excel, ikke glem at vi har en fantastisk introduksjonsguide til Excel VBA Excel VBA Programmering Tutorial for Beginners Excel VBA Programmering Tutorial for Beginners VBA er et Microsoft Office-verktøy. Du kan bruke den til å automatisere oppgaver med makroer, angi utløsere og mye mer. Vi vil introdusere deg til Excel visuell grunnleggende programmering med et enkelt prosjekt. Les mer for deg også.