Menggunakan referensi terstruktur dengan tabel Excel

Catatan:  Kami ingin secepatnya menyediakan konten bantuan terbaru dalam bahasa Anda. Halaman ini diterjemahkan menggunakan mesin dan mungkin terdapat kesalahan tata bahasa atau masalah keakuratan. Kami bertujuan menyediakan konten yang bermanfaat untuk Anda. Dapatkah Anda memberi tahu kami apakah informasi ini bermanfaat untuk Anda di bagian bawah halaman ini? Berikut artikel dalam bahasa Inggris untuk referensi.

Saat Anda membuat tabel Excel, Excel menetapkan nama untuk tabel, dan untuk setiap header kolom dalam tabel. Saat Anda menambahkan rumus ke tabel Excel, nama-nama itu bisa muncul secara otomatis saat Anda memasukkan rumus dan pilih referensi sel dalam tabel dan bukan secara manual memasukkan mereka. Berikut adalah contoh apa Excel:

Daripada menggunakan referensi sel eksplisit

Excel menggunakan nama tabel dan kolom

=Sum(C2:C7)

=SUM(DeptSales[Sales Amount])

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

Referensi terstruktur juga muncul saat Anda membuat rumus di luar tabel Excel yang mengacu ke data tabel. Referensi bisa memudahkan pencarian tabel di buku kerja yang besar.

Untuk menyertakan referensi terstruktur di dalam rumus Anda, klik sel tabel yang ingin Anda referensikan daripada mengetikkan referensi sel di dalam rumus. Mari kita gunakan data sampel berikut ini untuk memasukkan rumus yang secara otomatis menggunakan referensi terstruktur untuk menghitung jumlah komisi penjualan.

Penjualan Orang

Wilayah

Sales Amount

% Commission

Jumlah komisi

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, dan menempelkannya ke dalam sel A1 lembar kerja Excel yang baru.

  2. Untuk membuat tabel, pilih setiap sel dalam rentang data, dan tekan Ctrl + T.

  3. Pastikan kotak tabel saya memiliki header dicentang, dan klik OK.

  4. Di sel E2, ketik tanda sama dengan (=), dan klik sel C2.

    Di bilah rumus, referensi terstruktur [@[Sales Amount]] muncul setelah tanda sama dengan.

  5. Ketik tanda bintang (*) langsung setelah kurung tutup, dan klik 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. Dalam 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 saja dalam tabel untuk memperlihatkan Alat tabel > desain tab pada pita.

  2. Ketik nama yang Anda inginkan di kotak Nama tabel , dan tekan Enter.

Di dalam contoh data kami, kami menggunakan nama DeptSales.

Gunakan aturan berikut ini untuk nama tabel:

  • Gunakan karakter yang valid  Memulai nama dengan huruf, karakter garis bawah (_), atau garis miring terbalik (\). Gunakan huruf, angka, periode, dan karakter seluruh nama garis bawah. Anda tidak bisa menggunakan "C", "c", "R" atau "r" untuk nama, karena mereka sudah ditetapkan sebagai pintasan untuk memilih kolom atau baris untuk sel aktif ketika memasukkannya di kotak nama atau Buka .

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

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

  • Gunakan tidak lebih dari 255 karakter Nama tabel bisa memiliki 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.

  • Gunakan pengidentifikasi objek  Jika Anda berencana memiliki campuran tabel, PivotTable dan bagan, itu ide yang baik untuk Awali nama Anda dengan tipe objek. Misalnya: tbl_Sales untuk tabel penjualan, pt_Sales penjualan PivotTable dan chrt_Sales untuk bagan penjualan, atau ptchrt_Sales untuk PivotChart penjualan. Ini mempertahankan semua nama Anda di daftar di Pengatur nama.

Aturan sintaks referensi terstruktur

Anda juga bisa memasukkan atau mengubah referensi terstruktur secara manual dalam rumus tetapi untuk melakukan itu, ini akan membantu untuk memahami sintaks referensi terstruktur. Mari kita 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. Referensi data tabel, tanpa salah satu header atau total baris. Anda bisa menggunakan nama tabel default, seperti Table1, atau Ubah ke Gunakan nama kustom.

  • Penentu kolom:   [Sales Amount]dan[Commission Amount] adalah penentu kolom yang menggunakan nama kolom yang mewakili. Mereka referensi kolom data, tanpa baris header atau total kolom mana pun. Selalu mengapit penentu dalam tanda kurung, seperti yang diperlihatkan.

  • Penentu item:   [#Totals] dan [#Data] adalah penentu item khusus yang mengacu 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], [Sales Amount]] dan DeptSales [[#Data], [Commission Amount]] adalah referensi terstruktur, 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 tabel, kolom, dan penentu item khusus perlu dimasukkan dalam tanda kurung siku ([]) yang cocok. Penentu yang berisi penentu lain memerlukan luar tanda kurung cocok untuk dimasukkan dalam tanda kurung penentu lain yang cocok. Misalnya: = DeptSales [[Sales orang]: [Region]]

  • Semua header kolom adalah string teks    Tapi mereka tidak memerlukan tanda kutip saat digunakan dalam referensi terstruktur. Nomor atau tanggal, seperti 2014 atau 1/1/2014, juga dianggap sebagai string teks. Anda tidak bisa menggunakan ekspresi dengan header kolom. Sebagai contoh, 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

  • Line feed

  • Kembali ke awal

  • Koma (,)

  • Titik dua (:)

  • Tanda titik (.)

  • Kurung kiri ([)

  • Kurung kanan (])

  • Tanda pagar (#)

  • Tanda kutip tunggal (')

  • Tanda kutip ganda (|")

  • Kurung kurawal kiri ({)

  • Kurung kurawal kanan (})

  • Simbol uang dolar ($)

  • Tanda sisipan (^)

  • Simbol "dan" (&)

  • Tanda bintang (*)

  • Tanda tambah (+)

  • Tanda sama dengan (=)

  • Tanda minus (-)

  • Simbol lebih besar dari (>)

  • Simbol kurang dari (<)

  • Tanda bagi (/)

  • 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 membutuhkan karakter escape (') di rumus:

  • Kurung kiri ([)

  • Kurung kanan (])

  • Tanda pagar(#)

  • Tanda kutip tunggal (')

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 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 bisa 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. Tapi jika tabel Anda memiliki hanya satu baris, Excel tidak menggantikan penentu #This Row, yang mungkin menyebabkan hasil penghitungan yang tidak diharapkan saat Anda menambahkan baris lainnya. 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, Commission Amount, yang menghitung jumlah komisi dalam mata uang dolar, Anda bisa menggunakan rumus berikut ini:

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 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.

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 baris saat ini dan Commission Amount kolom. Jika digunakan dalam baris yang sama sebagai header atau baris total, ini akan mengembalikan #VALUE! kesalahan.

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 ketika 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 membuat referensi terstruktur untuk tabel di semi pilihan    Secara default, saat Anda membuat rumus, mengklik sel rentang dalam memilih semi tabel sel dan secara otomatis memasukkan referensi terstruktur daripada rentang sel dalam rumus. Perilaku semi-pemilihan ini membuatnya lebih mudah untuk memasukkan referensi terstruktur. Anda bisa mengubah perilaku ini atau menonaktifkan dengan memilih atau mengosongkan kotak centang Gunakan tabel nama dalam rumus di dalam File > opsi > rumus > dialog 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 dalam buku kerja lain, buku kerja sumber tertaut tersebut harus dibuka di Excel untuk menghindari #REF! kesalahan dalam buku kerja tujuan yang berisi link. Jika Anda membuka buku kerja tujuan pertama dan #REF! kesalahan muncul, mereka akan diatasi jika Anda lalu buka buku kerja sumber. Jika Anda membuka buku kerja sumber terlebih dahulu, Anda akan melihat kode kesalahan tidak ada.

  • Mengonversi rentang menjadi tabel dan tabel menjadi rentang    Saat Anda mengonversi tabel menjadi rentang, semua referensi sel berubah ke mereka sama absolut referensi gaya A1. Saat Anda mengonversi rentang menjadi tabel, Excel secara otomatis tidak mengubah referensi sel apa pun dari rentang ini untuk referensi terstruktur mereka sama.

  • Menonaktifkan header kolom    Anda bisa beralih ke header kolom tabel dan menonaktifkan 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 yang merujuk secara langsung ke header tabel terstruktur (misalnya = DeptSales [[#Headers], [% Commission]]) akan menghasilkan #REF.

  • Tambahkan atau Hapus kolom dan baris dalam tabel    Karena tabel rentang data sering berubah, referensi sel untuk referensi terstruktur secara otomatis menyesuaikan. Misalnya, jika Anda menggunakan nama tabel dalam sebuah rumus untuk menghitung semua sel data dalam tabel, dan Anda lalu menambahkan baris data, referensi sel secara otomatis menyesuaikan.

  • 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 melakukan mengisi referensi terstruktur yang tidak hal yang sama. Ketika Anda menyalin semua referensi terstruktur tetap sama, saat ketika Anda mengisi rumus, referensi terstruktur yang sepenuhnya memenuhi syarat menyesuaikan penentu kolom seperti sebuah seri sebagaimana diringkas 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

Bukannya menimpa nilai di sel saat ini, nilai sel saat ini dipindahkan dan penentu kolom disisipkan.

Perlu bantuan lainnya?

Anda selalu dapat bertanya kepada pakar di Komunitas Teknologi Excel, mendapatkan dukungan di Komunitas Jawaban, atau menyarankan fitur maupun fitur baru di Suara Pengguna Excel.

Topik terkait

Gambaran umum tabel Excel
Video: membuat dan memformat tabel Excel
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 dalam Excel

Kembangkan keterampilan Office Anda
Jelajahi pelatihan
Dapatkan fitur baru terlebih dahulu
Gabung ke Office Insiders

Apakah informasi ini bermanfaat?

Terima kasih atas umpan balik Anda!

Terima kasih atas umpan balik Anda! Sepertinya menghubungkan Anda ke salah satu agen dukungan Office kami akan sangat membantu.

×