Menggunakan referensi terstruktur dengan tabel Excel

Apa yang terjadi jika saya menggunakan referensi sel eksplisit?

Jika Anda memasukkan referensi sel eksplisit di kolom terhitung, akan lebih sulit bagi Anda untuk melihat rumus apa yang digunakan untuk menghitung.

1. Di lembar kerja sampel Anda, klik sel E2

2. Di bilah rumus, masukkan =C2*D2 dan tekan Enter.

Perlu diperhatikan bahwa saat Excel menyalin rumus ke bawah kolom, Excel tidak menggunakan referensi terstruktur. Jika, misalnya, Anda menambahkan kolom antara kolom C dan D yang sudah ada, Anda harus merevisi rumus Anda.

Bagaimana cara mengubah nama tabel?

Saat Anda membuat tabel Excel, Excel membuat nama tabel default (Table1, Table2, dan seterusnya), tapi Anda bisa mengubah nama tabel agar lebih bermakna.

1. Pilih sel mana pun dalam tabel untuk memperlihatkan Alat Tabel > tab Desain pada pita.

2. Ketikkan nama yang Anda inginkan dalam kotak Nama Tabel , lalu tekan Enter.

Di dalam contoh data kami, kami menggunakan nama DeptSales.

Gunakan aturan berikut ini untuk nama tabel:

• Gunakan karakter yang valid  Selalu mulai sebuah nama dengan karakter huruf, garis bawah (_), atau garis miring terbalik (\). Gunakan karakter huruf, angka, titik, dan garis bawah untuk sisa nama. Anda tidak bisa menggunakan "C", "c", "R", atau "r" untuk nama, karena huruf itu sudah ditetapkan sebagai pintasan untuk memilih baris atau kolom untuk sel aktif ketika Anda memasukkannya di dalam kotak Nama atau Masuk Ke.

• Jangan gunakan referensi sel  Nama tidak boleh sama seperti referensi sel, misalnya Z$100 atau R1C1.

• Jangan gunakan spasi untuk memisahkan kata  Spasi tidak dapat digunakan dalam nama. Anda dapat menggunakan karakter garis bawah (_) dan titik (.) sebagai pemisah kata. Misalnya, DeptSales, Sales_Tax atau Firt.Quarter.

• Gunakan tidak lebih dari 255 karakter Nama tabel dapat berisi hingga 255 karakter.

• Gunakan nama tabel unik Nama duplikat tidak diperbolehkan. Excel tidak membedakan antara karakter huruf besar dan kecil dalam nama jadi jika Anda memasukkan "Penjualan" tapi sudah memiliki nama lain berjudul "PENJUALAN" di dalam buku kerja yang sama, Anda akan diminta untuk memilih nama yang unik.

• Menggunakan pengidentifikasi objek  Jika Anda berencana untuk memiliki campuran tabel, PivotTable dan bagan, ada baiknya untuk mengawali nama Anda dengan tipe objek. Misalnya: tbl_Sales untuk tabel penjualan, pt_Sales untuk penjualan PivotTable, dan chrt_Sales untuk bagan penjualan, atau ptchrt_Sales untuk penjualan PivotChart. Ini menyimpan semua nama Anda dalam daftar berurutan di Manajer Nama.

Aturan sintaks referensi terstruktur

Anda juga bisa memasukkan atau mengubah referensi terstruktur secara manual dalam rumus tetapi untuk melakukan itu, itu akan membantu memahami sintaks referensi terstruktur. Mari kita membahas contoh rumus berikut ini:

=SUM(DeptSales[[#Totals],[Sales Amount]],DeptSales[[#Data],[Commission Amount]])

Rumus ini memiliki komponen referensi terstruktur berikut ini:

• Nama tabel:    DeptSales adalah nama tabel kustom. Nama tabel kustom mereferensikan tabel data, tanpa header atau baris total. Anda bisa menggunakan nama tabel default, seperti Tabel1, atau mengubahnya agar menggunakan nama kustom.

• Penentu kolom:    [Sales Amount] dan [Commission Amount] adalah penentu kolom yang menggunakan nama kolom yang diwakilinya. Penentu kolom ini mereferensikan data kolom, tanpa header kolom atau baris total. Selalu masukkan penentu dalam tanda kurung seperti yang diperlihatkan.

• Penentu item:    [#Totals] dan [#Data] adalah penentu item khusus yang merujuk ke bagian tabel tertentu, seperti baris total.

• Penentu tabel:    [[#Totals],[Sales Amount]] dan [[#Data],[Commission Amount]] adalah penentu tabel yang mewakili bagian luar referensi terstruktur. Referensi luar mengikuti nama tabel, dan Anda memasukkannya di tanda kurung siku.

• Referensi terstruktur:    (DeptSales[[#Totals],[Sales Amount]] dan DeptSales[[#Data],[Commission Amount]] adalah referensi terstruktur, yang dinyatakan oleh string yang dimulai dengan nama tabel dan diakhiri dengan penentu kolom.

Untuk membuat atau mengedit referensi terstruktur secara manual, gunakan aturan sintaks ini:

• Gunakan tanda kurung untuk mengapit penentu    Semua penentu tabel, kolom, dan item khusus harus dimasukkan ke dalam tanda kurung yang sesuai ([ ]). Penentu yang berisi penentu lain memerlukan tanda kurung luar yang sesuai untuk memasukkan tanda kurung dalam yang sesuai pada penentu lain tersebut. Misalnya: =DeptSales[[Sales Person]:[Region]]

• Semua header kolom adalah string teks    Namun tidak memerlukan tanda kutip saat digunakan dalam referensi terstruktur. Angka atau tanggal, seperti 2014 atau 1/1/2014, juga dianggap sebagai string teks. Anda tidak bisa menggunakan ekspresi dengan header kolom. Misalnya, ekspresi DeptSalesFYSummary[[2014]:[2012]] tidak akan berfungsi.

Gunakan tanda kurung di sekitar header kolom dengan karakter khusus    Jika terdapat karakter khusus, seluruh header kolom perlu dimasukkan dalam tanda kurung, yang berarti bahwa tanda kurung ganda diperlukan dalam penentu kolom. Misalnya: =DeptSalesFYSummary[[Total $ Amount]]

Berikut ini adalah daftar karakter khusus yang membutuhkan kurung ekstra dalam rumus tersebut:

• Tab

• Umpan baris

• Pengangkutan kembali

• Tanda koma (,)

• Titik dua (:)

• Titik (.)

• Kurung siku kiri ([)

• Kurung siku kanan (])

• Tanda paund (#)

• Tanda kutip tunggal (')

• Tanda kutip ganda (")

• Kurung kurawal kiri ({)

• Kurung kurawal kanan (})

• Tanda dolar ($)

• Tanda kurus (^)

• Simbol "dan" (&)

• Tanda bintang (*)

• Tanda plus (+)

• Tanda sama dengan (=)

• Tanda minus (-)

• Simbol lebih besar dari (>)

• Simbol kurang dari (<)

• Tanda pembagian (/)

• Tanda (@)

• Garis miring terbelakang (\)

• Tanda seru (!)

• Tanda kurung kiri (()

• Tanda kurung kanan ())

• Tanda persen (%)

• Tanda tanya (?)

• Backtick (')

• Titik koma (;)

• Tilde (~)

• Garis bawah (_)

• Gunakan karakter yang dihilangkan untuk beberapa karakter khusus dalam header kolom    Beberapa karakter memiliki makna khusus dan memerlukan penggunaan tanda kutip tunggal (') sebagai karakter yang dihilangkan. Misalnya: =DeptSalesFYSummary['#OfItems]

Berikut adalah daftar karakter khusus yang memerlukan karakter escape (') dalam rumus:

• Kurung siku kiri ([)

• Kurung siku kanan (])

• Tanda paund(#)

• Tanda kutip tunggal (')

• Tanda (@)

Gunakan karakter spasi untuk meningkatkan keterbacaan dalam referensi terstruktur    Anda bisa menggunakan karakter spasi untuk meningkatkan keterbacaan referensi terstruktur. Misalnya: =DeptSales[ [Sales Person]:[Region] ] atau =DeptSales[[#Headers], [#Data], [% Commission]]

Direkomendasikan untuk menggunakan spasi tunggal:

• Setelah tanda kurung siku kiri pertama ([)

• Sebelum tanda kurung siku kanan terakhir (]).

• Setelah tanda koma.

Operator referensi

Agar lebih fleksibel dalam menentukan rentang sel, Anda bisa menggunakan operator referensi berikut ini untuk menggabungkan penentu kolom.

Penentu item khusus

Untuk merujuk pada bagian tabel tertentu, seperti hanya baris total, Anda bisa menggunakan salah satu penentu item khusus berikut ini dalam referensi terstruktur.

Referensi terstruktur yang memenuhi syarat di kolom terhitung

Saat membuat kolom terhitung, Anda sering menggunakan referensi terstruktur untuk membuat rumus. Referensi terstruktur ini bisa tidak memenuhi syarat atau sepenuhnya memenuhi syarat. Misalnya, untuk membuat kolom terhitung, yang disebut Commission Amount, yang menghitung jumlah komisi dalam dolar, Anda bisa menggunakan rumus berikut:

Aturan umum yang harus diikuti adalah ini: Jika Anda menggunakan referensi terstruktur dalam tabel, seperti saat Anda membuat kolom terhitung, Anda bisa menggunakan referensi terstruktur yang tidak memenuhi syarat, tetapi jika Anda menggunakan referensi terstruktur di luar tabel, Anda perlu menggunakan referensi terstruktur yang sepenuhnya memenuhi syarat.

Contoh penggunaan referensi terstruktur

Berikut adalah beberapa cara untuk menggunakan referensi terstruktur.

Tips untuk bekerja dengan referensi terstruktur

Pertimbangkan hal berikut saat Anda bekerja dengan referensi terstruktur.

• Menggunakan Rumus LengkapiOtomatis    Anda mungkin berpendapat bahwa menggunakan Rumus LengkapiOtomatis sangat berguna ketika Anda memasukkan referensi terstruktur dan untuk memastikan penggunaan sintaks yang benar. Untuk informasi selengkapnya, lihat Menggunakan Rumus LengkapiOtomatis.

• Memutuskan apakah akan menghasilkan referensi terstruktur untuk tabel dalam semi-pilihan    Secara default, saat Anda membuat rumus, mengklik rentang sel dalam tabel semi-memilih sel dan secara otomatis memasukkan referensi terstruktur, bukan rentang sel dalam rumus. Dengan perilaku semi-pemilihan ini, memasukkan referensi terstruktur menjadi jauh lebih mudah. Anda dapat mengaktifkan atau menonaktifkan perilaku ini dengan memilih atau mengosongkan kotak centang Gunakan nama tabel dalam rumus dalam dialog Opsi > File > Rumus > Bekerja dengan rumus.

• Menggunakan buku kerja dengan link eksternal ke tabel Excel di buku kerja lain    Jika buku kerja berisi link eksternal ke tabel Excel di buku kerja lain, buku kerja sumber yang ditautkan harus terbuka di Excel untuk menghindari kesalahan #REF! dalam buku kerja tujuan yang berisi link. Jika Anda membuka buku kerja tujuan terlebih dahulu dan #REF! kesalahan muncul, kesalahan tersebut akan diatasi jika Anda kemudian membuka buku kerja sumber. Jika Anda membuka buku kerja sumber terlebih dahulu, Anda tidak akan melihat kode kesalahan.

• Mengonversi rentang menjadi tabel dan tabel menjadi rentang    Saat Anda mengonversi tabel menjadi rentang, semua referensi sel berubah menjadi referensi gaya A1 absolut yang setara. Saat Anda mengonversi rentang menjadi tabel, Excel tidak secara otomatis mengubah referensi sel rentang ini menjadi referensi terstruktur yang setara.

• Menonaktifkan header kolom    Anda bisa mengaktifkan dan menonaktifkan header kolom tabel dari tab Desain tabel > Baris Header. Jika Anda menonaktifkan header kolom tabel, referensi terstruktur yang menggunakan nama kolom tidak terpengaruh, dan Anda masih bisa menggunakannya dalam rumus. Referensi terstruktur yang merujuk langsung ke header tabel (misalnya =DeptSales[[#Headers],[%Commission]]) akan menghasilkan #REF.

• Menambahkan atau menghapus kolom dan baris dalam tabel    Karena rentang data tabel sering berubah, referensi sel untuk referensi terstruktur menyesuaikan secara otomatis. Misalnya, jika Anda menggunakan nama tabel dalam rumus untuk menghitung semua sel data dalam suatu tabel, dan Anda kemudian menambahkan satu baris data, referensi sel secara otomatis menyesuaikan diri.

• Mengganti nama tabel atau kolom    Jika Anda mengganti nama kolom atau tabel, Excel secara otomatis mengubah penggunaan header dan kolom dalam semua referensi terstruktur yang digunakan dalam buku kerja tersebut.

• Memindahkan, menyalin, dan mengisi referensi terstruktur    Semua referensi terstruktur tetap sama saat Anda menyalin atau memindahkan rumus yang menggunakan referensi terstruktur.

  Catatan: Menyalin referensi terstruktur dan melakukan isian referensi terstruktur bukanlah hal yang sama. Saat Anda menyalin, semua referensi terstruktur tetap sama, sementara ketika Anda mengisi rumus, referensi terstruktur yang sepenuhnya memenuhi syarat menyesuaikan penentu kolom seperti seri seperti yang diringkas dalam tabel berikut ini.

Perlu bantuan lainnya?

Anda selalu dapat bertanya kepada ahli di Komunitas Teknologi Excel atau mendapatkan dukungan di Komunitas.

Topik Terkait

Gambaran umum tabel
ExcelVideo: Membuat dan memformat tabel
ExcelMenjumlahkan data dalam tabel
ExcelMemformat tabel
ExcelMengubah ukuran tabel dengan menambahkan atau menghapus baris dan kolom
Memfilter data dalam rentang atau tabel
Mengonversi tabel menjadi rentang
Masalah
kompatibilitas tabel ExcelMengekspor tabel Excel ke SharePoint
Gambaran umum rumus di Excel