Menggunakan referensi terstruktur dengan tabel Excel

Saat Anda membuat tabel Excel, Excel menetapkan nama ke tabel, dan ke setiap header kolom dalam tabel. Saat Anda menambahkan rumus ke tabel Excel, nama-nama tersebut bisa otomatis muncul saat Anda memasukkan rumus dan memilih referensi sel di tabel alih-alih memasukkannya secara manual. Berikut ini contoh apa yang dilakukan Excel:

Daripada menggunakan referensi sel eksplisit	Excel menggunakan nama tabel dan kolom
=Sum(C2:C7)	=SUM(DeptSales[Sales Amount])

Kombinasi nama tabel dan kolom itu disebut referensi terstruktur. Nama dalam referensi terstruktur disesuaikan tiap kali Anda menambahkan atau menghapus data dari tabel.

Referensi terstruktur juga muncul ketika Anda membuat rumus di luar tabel Excel yang mereferensikan data tabel. Referensi bisa memudahkan untuk menemukan tabel dalam buku kerja yang besar.

Untuk menyertakan referensi terstruktur dalam rumus Anda, pilih sel tabel yang ingin Anda referensikan, alih-alih mengetikkan referensi selnya dalam rumus. Mari kita gunakan contoh data berikut untuk memasukkan rumus yang secara otomatis menggunakan referensi terstruktur untuk menghitung jumlah komisi penjualan.

Sales Person	Wilayah	Sales Amount	% Commission	Commission Amount
Joe	Utara	260	10%
Robert	Selatan	660	15%
Michelle	Timur	940	15%
Erich	Barat	410	12%
Dafna	Utara	800	15%
Rob	Selatan	900	15%

1. Salin contoh data dalam tabel di atas, termasuk judul kolom, lalu tempel ke sel A1 lembar kerja Excel yang baru.
2. Untuk membuat tabel, pilih sel mana pun dalam rentang data, lalu tekan Ctrl+T.
3. Pastikan kotak Tabel saya memiliki header dicentang, lalu pilih OK.
4. Di sel E2, ketik tanda sama dengan (=), lalu pilih sel C2.
   Di bilah rumus, referensi terstruktur [@[Sales Amount]] muncul setelah tanda sama dengan.
5. Ketik tanda bintang (*) tepat setelah tanda kurung tutup, lalu pilih sel D2.
   Di bilah rumus, referensi terstruktur [@[% Commission]] muncul setelah tanda bintang.
6. Tekan Enter.
   Excel secara otomatis akan membuat kolom terhitung dan menyalin rumus ke seluruh kolom untuk Anda, menyesuaikannya untuk setiap baris.

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, pilih sel E2
2. Di bilah rumus, enter =C2*D2 lalu 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 tab Desain Tabel di pita.
2. Ketikkan nama yang Anda inginkan dalam kotak Nama Tabel , lalu tekan Enter.

Dalam contoh data kami, kami menggunakan nama DeptSales.

Gunakan aturan berikut ini untuk nama tabel:

• Gunakan karakter yang valid Selalu awali nama dengan huruf, karakter garis bawah (_), atau garis miring terbalik ().\ Gunakan karakter huruf, angka, titik, dan garis bawah untuk sisa nama. Anda tidak dapat menggunakan "C", "c", "R", atau "r" untuk nama, karena keduanya telah ditetapkan sebagai pintasan untuk memilih kolom atau baris untuk sel aktif saat Anda memasukkannya dalam kotak Nama atau Buka kotak .

• Jangan gunakan referensi sel Nama tidak boleh sama dengan referensi sel, seperti 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 memiliki hingga 255 karakter.
• Gunakan nama tabel yang unik Nama duplikat tidak diperbolehkan. Excel tidak membedakan antara karakter huruf besar dan kecil dalam nama, jadi jika Anda memasukkan "Penjualan" tetapi sudah memiliki nama lain yang disebut "SALES" di buku kerja yang sama, Anda akan diminta untuk memilih nama yang unik.
• Menggunakan pengidentifikasi objek Jika Anda berencana memiliki perpaduan tabel, PivotTable, dan bagan, sebaiknya awali nama Anda dengan tipe objek. Misalnya: tbl_Sales untuk tabel penjualan, pt_Sales untuk PivotTable penjualan, dan chrt_Sales untuk bagan penjualan, atau ptchrt_Sales untuk PivotChart penjualan. Tindakan ini akan menyimpan semua nama Anda dalam daftar terurut di Pengelola Nama.

Aturan sintaks referensi terstruktur

Anda juga dapat memasukkan atau mengubah referensi terstruktur secara manual dalam rumus, tetapi untuk melakukannya, akan membantu memahami sintaks referensi terstruktur. Mari kita lihat contoh rumus berikut:

=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:[Jumlah Penjualan] dan [Jumlah Komisi] 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 tertentu dari tabel, 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],[Jumlah Penjualan]] dan DeptSales[[#Data],[Jumlah Komisi]] adalah referensi terstruktur, diwakili oleh string yang dimulai dengan nama tabel dan diakhiri dengan penentu kolom.

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

• Gunakan tanda kurung di sekitar penentu Semua penentu tabel, kolom, dan item khusus harus dikurung 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 Tetapi mereka tidak memerlukan tanda kutip ketika digunakan dalam referensi terstruktur. Angka atau tanggal, seperti 2014 atau 1/1/2014, juga dianggap sebagai string teks. Anda tidak dapat menggunakan ekspresi dengan header kolom. Misalnya, ekspresi DeptSalesFYSummary[[2014]:[2012]] tidak akan berfungsi.

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

Berikut daftar karakter khusus yang membutuhkan tanda kurung ekstra dalam rumus:

• Tab
• Umpan baris
• Pengembalian kereta
• Koma (,)
• Colon (:)
• Titik (.)
• Braket kiri ([)
• Braket kanan (])
• Tanda pound (#)
• Tanda kutip tunggal (')
• Tanda kutip ganda (")
• Tanda kurung kurawal kiri ({)
• Penjaga gigi kanan (})
• Tanda dolar ($)
• Tanda sisipan (^)
• Simbol "dan" (&)
• Tanda bintang (*)
• Tanda plus (+)
• Tanda sama dengan (=)
• Tanda minus (-)
• Simbol lebih besar dari (>)
• Simbol kurang dari (<)
• Tanda pembagian (/)
• Di tanda (@)
• Garis miring terbalik (\)
• Tanda seru (!)
• Tanda kurung kiri (()
• Tanda kurung kanan ())
• Tanda persen (%)
• Tanda tanya (?)
• Backtick (')
• Semicolon (;)
• Tilde (~)
• Garis bawah (_)

• Gunakan karakter escape untuk beberapa karakter khusus di header kolom Beberapa karakter memiliki arti khusus dan memerlukan penggunaan tanda kutip tunggal (') sebagai karakter pelarian. Misalnya: =DeptSalesFYSummary['#OfItems]

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

• Braket kiri ([)
• Braket kanan (])
• Tanda pound (#)
• Tanda kutip tunggal (')
• Di tanda (@)

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

Disarankan untuk menggunakan satu spasi:

• Setelah braket kiri pertama ([)
• Sebelum tanda kurung kanan terakhir (]).
• Setelah koma.

Operator referensi

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

Referensi terstruktur ini:	Mengacu ke:	Dengan menggunakan:	Yang merupakan rentang sel:
=DeptSales[[Sales Person]:[Region]]	Semua sel di dua atau beberapa kolom yang berdekatan	: operator rentang (titik dua)	A2:B7
=DeptSales[Sales Amount],DeptSales[Commission Amount]	Gabungan dua atau beberapa kolom	, operator gabungan (koma)	C2:C7, E2:E7
=DeptSales[[Sales Person]:[Sales Amount]] DeptSales[[Region]:[% Commission]]	Irisan dua atau beberapa kolom	operator irisan (spasi)	B2:C7

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.

Penentu item khusus ini:	Mengacu ke:
#All	Seluruh tabel, termasuk header kolom, data, dan total (jika ada).
#Data	Hanya baris data.
#Headers	Hanya baris header.
#Totals	Hanya baris total. Jika tidak ada, hasilnya adalah kosong.
#This Row atau @ atau @[Column Name]	Hanya sel dalam baris yang sama dengan rumus. Penentu ini tidak dapat digabungkan dengan penentu item khusus lainnya. Gunakan penentu ini untuk memberlakukan perilaku perpotongan implisit untuk referensi atau untuk mengesampingkan perilaku perpotongan implisit dan mengacu ke nilai tunggal dari kolom. Excel secara otomatis mengubah penentu #This Row untuk menyingkat penentu @ specifier dalam tabel yang memiliki lebih dari satu baris data. Tetapi jika tabel Anda hanya memiliki satu baris, Excel tidak menggantikan penentu #This Baris, yang dapat menyebabkan hasil penghitungan yang tidak terduga saat Anda menambahkan lebih banyak baris. Untuk menghindari masalah perhitungan, pastikan Anda memasukkan beberapa baris di tabel Anda sebelum memasukkan rumus referensi terstruktur apa pun.

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 Jumlah Komisi, yang menghitung jumlah komisi dalam dolar, Anda dapat menggunakan rumus berikut:

Tipe referensi terstruktur	Contoh	Komentar
Tidak memenuhi syarat	=[Sales Amount]*[% Commission]	Mengalikan nilai terkait dari baris saat ini.
Sepenuhnya memenuhi syarat	=DeptSales[Sales Amount]*DeptSales[% Commission]	Mengalikan nilai terkait untuk setiap baris untuk kedua kolom.

Aturan umum yang harus diikuti adalah ini: Jika menggunakan referensi terstruktur dalam tabel, seperti saat membuat kolom terhitung, Anda dapat menggunakan referensi terstruktur yang tidak memenuhi syarat, tetapi jika 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.

Referensi terstruktur ini:	Mengacu ke:	Yang merupakan rentang sel:
=DeptSales[[#All],[Sales Amount]]	Semua sel di dalam kolom Sales Amount.	C1:C8
=DeptSales[[#Headers],[% Commission]]	Header kolom % Commission.	D1
=DeptSales[[#Totals],[Region]]	Kolom total Wilayah. Jika tidak ada baris Total, maka hasilnya null.	B8
=DeptSales[[#All],[Sales Amount]:[% Commission]]	Semua sel dalam Sales Amount dan % Commission.	C1:D8
=DeptSales[[#Data],[% Commission]:[Commission Amount]]	Hanya data kolom % Commission dan Commission Amount.	D2:E7
=DeptSales[[#Headers],[Region]:[Commission Amount]]	Hanya header kolom antara Region dan Commission Amount.	B1:E1
=DeptSales[[#Totals],[Sales Amount]:[Commission Amount]]	Total dari kolom Sales Amount hingga Commission Amount. Jika tidak ada baris Total, maka hasilnya null.	C8:E8
=DeptSales[[#Headers],[#Data],[% Commission]]	Hanya header dan data % Commission.	D1:D7
=DeptSales[[#This Row], [Commission Amount]] atau =DeptSales[@Commission Amount]	Sel di irisan dari baris saat ini dan kolom Commission Amount. Jika digunakan dalam baris yang sama dengan header atau baris total, tindakan ini akan mengembalikan kesalahan #VALUE!. Jika Anda mengetik bentuk referensi terstruktur (#This Row) yang lebih panjang dalam tabel dengan beberapa baris data, Excel secara otomatis menggantinya dengan bentuk yang lebih singkat (@). Fungsi keduanya sama.	E5 (Jika baris saat ini adalah 5)

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 Pelengkap Otomatis Rumus.

• Memutuskan apakah akan membuat referensi terstruktur untuk tabel dalam semi-pilihan Secara default, ketika Anda membuat rumus, memilih rentang sel dalam tabel akan memilih semi 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 rumusdalam> dialogOpsi File>Rumus>Bekerja dengan rumus.

• Menggunakan buku kerja dengan tautan eksternal ke tabel Excel di buku kerja lain Jika buku kerja berisi tautan eksternal ke tabel Excel di buku kerja lain, buku kerja sumber tertaut tersebut harus terbuka di Excel untuk menghindari kesalahan #REF! di buku kerja tujuan yang berisi tautan. Jika Anda membuka buku kerja tujuan terlebih dahulu dan kesalahan #REF! muncul, kesalahan tersebut akan teratasi 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 Ketika 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 dari rentang ini menjadi referensi terstruktur yang setara.

• Nonaktifkan header kolom Anda dapat 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 akan terpengaruh, dan Anda masih dapat menggunakannya dalam rumus. Referensi terstruktur yang merujuk langsung ke header tabel (misalnya =DeptSales[[#Headers],[%Commission]]) akan menghasilkan #REF.

• Menambahkan atau menghapus kolom dan baris ke tabel Karena rentang data tabel sering berubah, referensi sel untuk referensi terstruktur disesuaikan 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 ketika Anda menyalin atau memindahkan rumus yang menggunakan referensi terstruktur.

  Catatan

  Menyalin referensi terstruktur dan mengisi 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 dirangkum dalam tabel berikut.

Jika arah pengisian adalah:	Dan saat mengisi, Anda menekan:	Lalu:
Atas atau bawah	Tidak ada	Tidak ada penyesuaian penentu kolom.
Atas atau bawah	Ctrl	Penyesuaian penentu kolom seperti sebuah seri.
Kanan atau kiri	Tidak ada	Penyesuaian penentu kolom seperti sebuah seri.
Atas, bawah, kanan, atau kiri	Shift	Alih-alih menimpa nilai di sel saat ini, nilai sel saat ini dipindahkan, dan penentu kolom disisipkan.

Perlu bantuan lainnya?

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

Topik Terkait

Gambaran umum tabel Excel
Membuat dan memformat tabel
Menghitung total data dalam tabel Excel
Memformat tabel Excel
Mengubah ukuran tabel dengan menambahkan atau menghapus baris dan kolom
Memfilter data dalam rentang atau tabel
Mengonversi tabel menjadi rentang
Masalah kompatibilitas tabel Excel
Mengekspor tabel Excel ke SharePoint
Gambaran umum rumus di Excel