Oppiskele Työskentely Excel-taulukoiden Kanssa | Taulukoiden ja Raporttien Automatisointi

Pyyhkäise näyttääksesi valikon

Excel-taulukko — jonka VBA tuntee nimellä ListObject — on nimetty, automaattisesti laajeneva alue, jossa on sisäänrakennetut suodatusnuolet, vuorotellut rivit ja rakenteiset sarakeviittaukset. Jos tietosi eivät vielä ole taulukkona, valitse mikä tahansa solu alueelta ja paina Ctrl+T, tai anna VBA:n luoda taulukko käyttämällä ListObjects.Add.

ListObject-viittaus

Dim ws As Worksheet
Dim tbl As ListObject
 
Set ws = ThisWorkbook.Worksheets("Reports")
Set tbl = ws.ListObjects("tblReports")
 
Debug.Print tbl.Range.Address        ' full table including header
Debug.Print tbl.DataBodyRange.Rows.Count   ' data rows only, no header

Määrittelemällä tbl As ListObject (eikä pelkästään As Range) saat käyttöösi kaikki taulukon ominaisuudet, joita tässä osiossa tarvitaan — ListRows, ListColumns ja Total Row ovat kaikki käytettävissä, kun objekti on tyypitetty oikein. Huomaa ero kahden Debug.Print-rivin välillä: tbl.Range kattaa koko taulukon otsikkorivi mukaan lukien, kun taas tbl.DataBodyRange kattaa vain sen alla olevan datan.

Lähes kaikki toiminnot — rivin lisääminen, sarakkeen summaaminen, tietueiden läpikäynti — kannattaa tehdä DataBodyRange-alueella, jotta otsikkotekstiä ei vahingossa käsitellä tietorivinä.

Rivien lisääminen

ListRows.Add lisää uuden rivin suoraan taulukon alle — ja mikä tärkeintä, kaikki rakenteiset viitekaavat muissa sarakkeissa laajenevat automaattisesti uudelle riville, mikä on yksi taulukon suurimmista eduista verrattuna tavalliseen alueeseen.

Dim newRow As ListRow
Set newRow = tbl.ListRows.Add
 
newRow.Range(1, 1).Value = "April"
newRow.Range(1, 2).Value = "North"
newRow.Range(1, 3).Value = 45200
newRow.Range(1, 4).Value = 30750
newRow.Range(1, 5).Value = 14450
newRow.Range(1, 6).Value = 41000

tbl.ListRows.Add luo tyhjän rivin ja palauttaa sen ListRow-objektina, minkä vuoksi seuraavat kuusi riviä kirjoittavat newRow:lle eivätkä suoraan tbl:lle. newRow.Range(1, 1) tarkoittaa "tämän uuden rivin rivi 1, sarake 1" — indeksointi alkaa 1:stä uudelle riville, eikä laske koko taulukon yläreunasta. Tämä on huomattavasti parempi tapa kuin etsiä laskentataulukon viimeinen rivi End(xlUp)-komennolla ja kirjoittaa käsin seuraavaan sarakkeeseen: ListRows.Add sijoittaa uuden rivin aina oikein taulukon sisälle, joten mahdollinen Total Row, rakenteiset viitekaavat ja ehdollinen muotoilu laajenevat automaattisesti kattamaan uuden rivin.

Tietueiden päivittäminen

Päivittääksesi olemassa olevan rivin, käy läpi DataBodyRange ja täsmää avainsarakkeen perusteella — tässä esimerkissä päivitetään helmikuun Central-alueen Target budjetin tarkistuksen jälkeen:

Dim r As Long
For r = 1 To tbl.DataBodyRange.Rows.Count
    If tbl.DataBodyRange.Cells(r, 1).Value = "February" And _
       tbl.DataBodyRange.Cells(r, 2).Value = "Central" Then
        tbl.DataBodyRange.Cells(r, 6).Value = 52000   ' revised Target
        Exit For
    End If
Next r

Tämä on sama ylhäältä alas, pysähdy ensimmäiseen osumaan -logiikka kuin ehtolauseissa, mutta nyt oikeille riveille kovakoodattujen arvojen sijaan: silmukka tarkistaa kuukauden ja alueen yhdessä And-operaattorilla, ja heti kun molemmat täsmäävät, Target-sarake päivitetään ja Exit For lopettaa silmukan, jotta jäljellä olevia rivejä ei käydä turhaan läpi. Käyttämällä tbl.DataBodyRange.Cells(r, 1) laskentataulukon Cells-viittauksen sijaan rivinumerointi pysyy taulukon omassa datassa — rivi 1 tarkoittaa ensimmäistä tietoriviä, riippumatta siitä, millä laskentataulukon rivillä taulukko alkaa.

Taulukkosarakkeiden viittaukset

Rakenteiset viittaukset — ListColumns("Name") — ovat luettavampia ja kestävämpiä kuin sarakkeiden laskeminen numeroin, erityisesti kun taulukkoa muokataan ja sarakkeet siirtyvät:

Dim profitCol As Range
Set profitCol = tbl.ListColumns("Profit").DataBodyRange
 
Debug.Print Application.WorksheetFunction.Sum(profitCol)
Debug.Print Application.WorksheetFunction.Average(profitCol)

ListColumns("Profit") löytää sarakkeen otsikkotekstin perusteella, ei sijaintia laskemalla, joten koodi toimii vaikka Profit siirtyisi myöhemmin sarakkeesta E sarakkeeseen F — Cells(r, 5) laskeminen käsin menisi tällöin huomaamatta rikki. Application.WorksheetFunction mahdollistaa VBA:ssa tavallisten Excel-funktioiden, kuten SUM ja AVERAGE, käytön suoraan Range-objektille, jolloin sinun ei tarvitse kirjoittaa manuaalista silmukkaa ja kerryttää summaa itse, mikä vähentää koodin määrää ja virheiden riskiä.

Tehtävä

Avaa Section_4_Reports.xlsx, tallenna se nimellä Section_4_Reports.xlsm ja varmista, että Reports-välilehden data on taulukko nimeltä tblReports (napsauta mitä tahansa solua taulukossa — Taulukkotyökalut-välilehden pitäisi ilmestyä).
Kirjoita makro, joka lisää huhtikuun rivin jokaiselle viidelle alueelle käyttäen ListRows.Add (yhteensä viisi uutta riviä, luvut voivat olla keksittyjä).
Kirjoita toinen makro, joka käyttää ListColumns("Sales").DataBodyRange ja WorksheetFunction.Sum tulostaakseen kaikkien rivien myyntien summan Välitön ikkuna -ikkunaan.

Vihje

1. Taulukon avaaminen ja varmistaminen

Tallenna tiedosto uudelleen valitsemalla Tiedosto → Tallenna nimellä ja valitse tiedostotyypiksi "Excel-makroilla laajennettu työkirja (*.xlsm)" — tähän osaan ei tarvita koodia.
Napsauta mitä tahansa solua Reports-datassa ja tarkista, että valintanauhassa näkyy Taulukkotyökalut-välilehti — tämä varmistaa, että kyseessä on oikea Excel-taulukko, ei pelkkä tavallinen alue.

2. Viiden huhtikuun rivin lisääminen ListRows.Add-komennolla

Tarvitset ListObject-muuttujan, joka viittaa tblReports-taulukkoon, ja kutsut .ListRows.Add kerran jokaista aluetta kohden — viisi erillistä kutsua tai yksi silmukka, joka toistuu viisi kertaa.
Jokaiselle uudelle riville tulee kirjoittaa kuusi arvoa: Month, Region, Sales, Expenses, Profit, Target — viittaa niihin sijainnin mukaan (newRow.Range(1, 1), (1, 2), jne.), samalla tavalla kuin luvun 4 esimerkkitehtävässä.
Viiden alueen nimet taulukossa tekee silmukasta selkeämmän kuin kirjoittaa viisi lähes samanlaista koodilohkoa käsin.

3. Myyntien summaaminen WorksheetFunctionilla

ListColumns("Sales") hakee sarakkeen otsikon perusteella — .DataBodyRange rajaa vain datasoluihin, otsikkoa ei oteta mukaan.
Application.WorksheetFunction.Sum(...) ottaa alueen suoraan — silmukkaa ei tarvita.
Debug.Print tulostaa tuloksen Välitön ikkuna -ikkunaan (Ctrl+G), ei ponnahdusikkunaan.

Ratkaisu

Option Explicit

Sub AddAprilRows()
    Dim tbl As ListObject
    Dim newRow As ListRow
    Dim regions As Variant
    Dim i As Long

    Set tbl = ThisWorkbook.Worksheets("Reports").ListObjects("tblReports")
    regions = Array("North", "South", "East", "West", "Central")

    For i = 0 To 4
        Set newRow = tbl.ListRows.Add
        newRow.Range(1, 1).Value = "April"
        newRow.Range(1, 2).Value = regions(i)
        newRow.Range(1, 3).Value = 46000 + i * 500   ' Sales — invented
        newRow.Range(1, 4).Value = 31000 + i * 300   ' Expenses — invented
        newRow.Range(1, 5).Value = 15000 + i * 200   ' Profit — invented
        newRow.Range(1, 6).Value = 41000              ' Target — invented
    Next i
End Sub

Sub PrintTotalSales()
    Dim tbl As ListObject
    Dim salesCol As Range

    Set tbl = ThisWorkbook.Worksheets("Reports").ListObjects("tblReports")
    Set salesCol = tbl.ListColumns("Sales").DataBodyRange

    Debug.Print "Total Sales: " & Application.WorksheetFunction.Sum(salesCol)
End Sub

Suorita ensin AddAprilRows ja sitten PrintTotalSales — kokonaissumman pitäisi sisältää automaattisesti viisi uutta huhtikuun riviä, koska DataBodyRange heijastaa aina taulukon nykyistä kokoa.

Oliko kaikki selvää?

Kiitos palautteestasi!

Osio 4. Luku 1

Kysy tekoälyä

Kysy mitä tahansa tai kokeile jotakin ehdotetuista kysymyksistä aloittaaksesi keskustelumme

Osio 4. Luku 1