Bagaimana cara membuat referensi terstruktur di Excel?
Referensi Terstruktur dimulai dengan tabel excel. Segera setelah tabel dibuat di excel, secara otomatis membuat referensi terstruktur untuk Anda.
Sekarang lihat gambar di bawah ini.
- Langkah 1: Saya telah memberikan tautan ke sel B3, alih-alih menunjukkan tautan sebagai B2, tautan itu ditampilkan sebagai Tabel1 [@ Penjualan]. Di sini Tabel1 adalah nama tabel dan @Sales adalah kolom yang kita maksud. Semua sel di kolom ini dirujuk dengan nama Tabel dan diikuti dengan nama judul kolom.
- Langkah 2: Sekarang saya akan mengubah nama tabel menjadi Data_Table dan mengubah judul kolom menjadi Jumlah .
- Langkah 3: Untuk mengubah nama tabel, tempatkan kursor di dalam tabel> buka Desain> Nama Tabel.
- Langkah 4: Sebutkan nama tabel sebagai Data_Table.
- Langkah 5: Sekarang ubah memberikan referensi ke sel B3.
Jadi kita telah memahami bahwa referensi terstruktur memiliki dua bagian Nama Tabel & Nama Kolom.
Contoh
Anda dapat mendownload Template Excel Referensi Terstruktur di sini - Template Excel Referensi TerstrukturContoh 1
Dengan menggunakan referensi terstruktur, Anda dapat membuat rumus Anda dinamis. Tidak seperti referensi sel normal, ini memungkinkan rumus untuk dijalankan jika terjadi penambahan dan penghapusan dalam rentang data.
Izinkan saya menerapkan rumus SUM untuk rentang normal dan tabel excel.
Rumus SUM untuk Rentang Normal.
Rumus SUM untuk Tabel Excel.
Izinkan saya menambahkan beberapa baris ke data tabel normal dan excel. Saya telah menambahkan 2 item baris ke data, sekarang lihat perbedaannya.
Referensi terstruktur di tabel excel menunjukkan nilai yang diperbarui tetapi rentang data normal tidak menunjukkan nilai yang diperbarui kecuali Anda membuat beberapa perubahan pada rumus secara manual.
Contoh # 2
Sekarang, lihat satu contoh lagi. Saya memiliki informasi Nama Produk, Kuantitas, dan Harga. Dengan menggunakan informasi ini saya perlu sampai pada Nilai Penjualan.
Untuk mendapatkan nilai jual rumusnya adalah Qty * Price . Mari terapkan rumus ini di tabel.
Formula mengatakan [@QTY] * [@PRICE]. Ini lebih bisa dimengerti daripada referensi normal B2 * C2. Kami tidak mendapatkan nama tabel jika kami meletakkan rumus di dalam tabel.
Masalah dengan Referensi Terstruktur Excel
Saat menggunakan referensi terstruktur, kami menghadapi beberapa masalah yang tercantum di bawah ini.
Masalah # 1
Referensi yang terstruktur juga memiliki masalah tersendiri. Kita semua terbiasa menerapkan rumus excel dan menyalin atau menyeretnya ke sel lain yang tersisa. Ini bukan proses yang sama dalam Referensi Terstruktur, cara kerjanya sedikit berbeda.
Sekarang, lihat contoh di bawah ini. Saya telah menerapkan rumus SUM di excel untuk kisaran normal.
Jika saya ingin menjumlahkan Harga dan Nilai Penjualan, saya hanya akan menyalin dan menempel atau menyeret rumus saat ini ke dua sel lainnya dan itu akan memberi saya nilai SUM dari Harga & Nilai Penjualan.
Sekarang terapkan rumus yang sama untuk tabel excel untuk kolom Qty.
Sekarang kita mendapatkan jumlah kolom Qty. Seperti rentang normal, rumus menyalin rumus saat ini dan menempelkannya ke kolom Harga untuk mendapatkan total Harga.
Ya Tuhan!!! Kolom ini tidak menampilkan total kolom Harga melainkan masih menampilkan kolom Total Qty saja. Jadi, kami tidak dapat menyalin dan menempelkan rumus ini ke sel yang berdekatan atau sel lain untuk merujuk ke kolom atau baris relatif.
Seret Rumus untuk Mengubah Referensi
Sekarang kita tahu batasannya, kita tidak bisa lagi melakukan pekerjaan copy-paste dengan referensi terstruktur. Lalu bagaimana cara mengatasi keterbatasan ini?
Solusinya sangat sederhana kita hanya perlu menyeret rumus daripada menyalin. Pilih sel rumus dan gunakan gagang isian dan seret ke dua sel yang tersisa untuk mengubah referensi kolom ke Harga dan Nilai Jual.
Sekarang kami telah memperbarui rumus untuk mendapatkan total masing-masing.
Masalah # 2
Kami telah melihat satu masalah dengan referensi struktur dan kami menemukan solusinya juga, tetapi kami memiliki satu masalah lagi di sini, kami tidak dapat menjadikan panggilan sebagai referensi absolut jika kami menyeret rumus ke sel lain.
Mari kita lihat contoh di bawah ini sekarang. Saya memiliki tabel penjualan dengan banyak entri dan saya ingin menggabungkan data dengan menggunakan fungsi SUMIF di excel.
Sekarang saya akan menerapkan fungsi SUMIF untuk mendapatkan nilai penjualan gabungan untuk setiap produk.
Saya telah menerapkan rumus untuk bulan Jan, karena ini adalah referensi terstruktur, kami tidak dapat menyalin dan menempelkan rumus ke dua kolom yang tersisa, itu tidak akan mengubah referensi ke Feb & Mar, jadi saya akan menyeret rumus tersebut.
Oh !! Saya tidak mendapatkan nilai apa pun di kolom Feb & Mar. Apa masalahnya ??? Perhatikan rumusnya dengan cermat.
Kami telah menyeret rumus dari bulan Januari. Dalam fungsi SUMIF, argumen pertama adalah Kriteria Range Sales_Table [Produk] karena kita menyeret rumus itu memiliki perubahan ke Sales _Table [Jan].
Jadi bagaimana kita menghadapinya ?? Kita perlu membuat argumen pertama yaitu kolom Produk sebagai kolom absolut dan kolom lain sebagai referensi relatif. Tidak seperti referensi biasa, kami tidak memiliki kemewahan menggunakan tombol F4 untuk mengubah tipe referensi.
Solusinya adalah kita perlu menduplikasi kolom referensi seperti yang ditunjukkan pada gambar di bawah ini.
Sekarang kita dapat menyeret rumus ke reaming dua kolom lainnya. Rentang Kriteria akan konstan dan referensi kolom lainnya akan berubah sesuai.
Tip Pro: Untuk menjadikan ROW sebagai referensi absolut kita perlu membuat entri ROW ganda tetapi kita perlu meletakkan simbol @ sebelum nama ROW.
= Sales_Table [@ [Produk]: [Produk]]
Bagaimana Cara Mematikan Referensi Terstruktur di Excel?
Jika Anda bukan penggemar referensi terstruktur, Anda dapat mematikannya dengan mengikuti langkah-langkah di bawah ini.
- Langkah 1: Buka FILE> Opsi.
- Langkah 2: Rumus> Hapus centang Gunakan nama tabel dalam rumus.
Hal-hal untuk diingat
- Untuk membuat referensi absolut dalam referensi terstruktur, kita perlu menggandakan nama kolom.
- Kami tidak dapat menyalin rumus referensi terstruktur sebagai gantinya kami perlu menyeret rumus.
- Kami tidak dapat melihat dengan tepat sel mana yang kami maksud dalam referensi terstruktur.
- Jika Anda tidak tertarik dengan referensi terstruktur, Anda dapat menonaktifkannya.
