Bagaimana Cara Membuat Referensi Terstruktur di Excel?
Referensi terstruktur dimulai dengan tabel Excel. Saat tabel dibuat di Excel, secara otomatis membuat referensi terstruktur untuk Anda.
Sekarang perhatikan gambar di bawah ini.
- Langkah 1: Kami telah memberikan tautan ke sel B3. Alih-alih menampilkan tautan sebagai B2, itu ditampilkan sebagai Tabel1[@Penjualan]. Di sini Table1 adalah nama tabelnya, dan @Sales adalah kolom yang kita rujuk. Semua sel dalam kolom ini disebut dengan nama Tabel dan diikuti dengan nama judul kolom.
- Langkah 2: Sekarang, kita akan mengubah nama tabel menjadi Data_Table dan judul kolom menjadi Amount .
- Langkah 3: Letakkan kursor di dalam tabel > buka Desain Tabel >Nama Tabel untuk mengubah nama tabel.
- Langkah 4: Sebutkan nama tabel sebagai Data_Table.
- Langkah 5: Sekarang, beri referensi ke sel B3.
Jadi, kami telah memahami bahwa referensi terstruktur memiliki dua bagian: Nama Tabel dan Nama Kolom.
Contoh
Contoh 1
Menggunakan referensi terstruktur, Anda dapat membuat rumus Anda dinamis. Tidak seperti referensi sel normal, ini memungkinkan rumus untuk aktif jika terjadi penambahan dan penghapusan dalam rentang data.
Izinkan saya menerapkan rumus SUM untuk rentang normal dan tabel Excel.
Rumus SUM untuk Kisaran Normal.
Rumus SUM untuk Tabel Excel.
Izinkan saya menambahkan beberapa baris ke data tabel normal dan Excel. Kami telah menambahkan 2 item baris ke data dan sekarang lihat perbedaannya.
Referensi terstruktur di tabel Excel menunjukkan nilai yang diperbarui. Tetap saja, rentang data normal tidak memperlihatkan nilai yang diperbarui kecuali jika Anda membuat beberapa perubahan pada rumus secara manual.
Contoh #2
Sekarang, lihat satu contoh lagi. Kami memiliki informasi nama produk, kuantitas, dan harga. Dengan menggunakan informasi ini, kita perlu sampai pada nilai penjualan.
Rumusnya adalah Qty * Price untuk mendapatkan nilai penjualan. Mari kita terapkan rumus ini ke tabel.
Rumus mengatakan [@QTY] * [@PRICE]. Ini lebih 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 terstruktur juga memiliki masalah. Kita semua sudah tidak asing lagi dengan penerapan rumus excel Rumus ExcelIstilah “rumus excel dasar” mengacu pada fungsi umum yang digunakan di Microsoft Excel untuk melakukan perhitungan sederhana seperti penjumlahan, rata-rata, dan perbandingan. SUM, COUNT, COUNTA, COUNTBLANK, AVERAGE, MIN Excel, MAX Excel, LEN Excel, TRIM Excel, IF Excel adalah sepuluh rumus dan fungsi excel teratas.baca selengkapnya dan salin atau seret ke sel lain yang tersisa. Ini bukan proses yang sama dengan referensi terstruktur. Ini bekerja sedikit berbeda.
Sekarang, lihat contoh di bawah ini. Kami telah menerapkan rumus SUM di excel Rumus SUM Di ExcelFungsi SUM di excel menambahkan nilai numerik dalam rentang sel. Dikategorikan di bawah fungsi Matematika dan Trigonometri, dimasukkan dengan mengetikkan ‘=SUM’ diikuti dengan nilai yang akan dijumlahkan. Nilai yang diberikan ke fungsi dapat berupa angka, referensi sel, atau rentang. Baca selengkapnya untuk rentang normal.
Jika kita ingin menjumlahkan harga dan nilai penjualan, kita akan menyalin dan menempelkan atau menyeret rumus saat ini ke dua sel lainnya, memberi kita nilai SUM dari harga dan nilai penjualan.
Sekarang, terapkan rumus yang sama untuk tabel Excel untuk kolom “Jumlah”.
Sekarang, kami mendapatkan jumlah dari kolom “Jumlah”. Seperti kisaran normal, salin rumus saat ini dan tempelkan ke kolom “Harga” untuk mendapatkan harga total.
Itu tidak menunjukkan total kolom “Harga”. Sebaliknya, itu hanya menampilkan total kolom “Jumlah”. 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 keterbatasannya. Misalnya, kita tidak bisa lagi melakukan pekerjaan salin-tempel dengan referensi terstruktur. Jadi, lalu bagaimana kita mengatasi keterbatasan ini?
Solusinya sangat sederhana. Pertama, kita perlu menyeret rumus alih-alih menyalinnya. Pilih sel rumus, gunakan pegangan isianPegangan isian di Excel memungkinkan Anda menghindari penyalinan dan menempelkan setiap nilai ke dalam sel dan alih-alih menggunakan pola untuk mengisi informasi. Salib kecil ini adalah alat serbaguna di Excel suite yang dapat digunakan untuk entri data, transformasi data, dan banyak aplikasi lainnya. Baca lebih lanjut, dan seret ke dua sel yang tersisa untuk mengubah referensi kolom menjadi “Harga” dan “Obral Nilai.”
Sekarang, kami telah memperbarui formula untuk mendapatkan total masing-masing.
Masalah #2
Kami telah melihat satu masalah dengan referensi struktur. Kami juga menemukan solusinya, tetapi kami memiliki satu masalah lagi: kami tidak dapat menjadikan panggilan sebagai referensi absolut jika kami menyeret rumus ke sel lain.
Mari kita lihat contoh di bawah ini sekarang. Kami memiliki tabel penjualan dengan banyak entri, dan kami ingin menggabungkan data menggunakan fungsi SUMIF di excelFungsi SUMIF Di ExcelFungsi SUMIF Excel menghitung jumlah rentang sel berdasarkan kriteria yang diberikan. Kriteria dapat mencakup tanggal, angka, dan teks. Misalnya, rumus ‘=SUMIF(B1:B5, ‘<=12′)’ menambahkan nilai dalam rentang sel B1:B5, yang kurang dari atau sama dengan 12. baca selengkapnya.
Kami akan menerapkan fungsi SUMIF untuk mendapatkan nilai penjualan gabungan untuk setiap produk.
Kami telah menerapkan rumus untuk bulan Jan. Sayangnya, karena ini adalah referensi terstruktur, kami tidak dapat menyalin dan menempelkan rumus ke dua kolom yang tersisa. Namun, itu tidak akan mengubah referensi ke Feb dan Mar, sehingga kami akan menyeret rumusnya.
Kami tidak mendapatkan nilai apa pun di kolom “Feb” dan “Mar”. Apa masalahnya? Perhatikan baik-baik formulanya.
Kami telah menyeret formula dari bulan Jan. Karena kita menyeret rumus, argumen pertama fungsi SUMIF adalah rentang kriteria, Sales_Table [Product] . Oleh karena itu, telah berubah menjadi Sales_Table [Jan].
Jadi, bagaimana kita menghadapinya? Kita perlu menjadikan argumen pertama, yaitu kolom “Produk” sebagai kolom absolut dan kolom lainnya sebagai referensi relatif. Tidak seperti referensi normal, kami tidak memiliki kemewahan menggunakan tombol F4 untuk mengubah tipe referensi.
Solusinya adalah menduplikasi kolom referensi, seperti yang ditunjukkan pada gambar di bawah ini.
Sekarang, kita dapat menyeret rumus ke dua kolom lainnya yang tersisa. Sekali lagi, rentang kriteria akan konstan, dan referensi kolom lainnya akan berubah.
Pro Tip : Untuk menjadikan ROW sebagai referensi absolut Referensi Absolut Referensi absolut di excel adalah jenis referensi sel di mana sel yang dirujuk tidak berubah, seperti yang terjadi pada referensi relatif. Dengan menekan f4, kita dapat membuat formula untuk referensi absolut.Baca lebih lanjut, kita perlu membuat entri ROW ganda, tetapi kita harus meletakkan simbol @ sebelum nama ROW.
=Sales_Table[@[Produk]:[Produk]]
Bagaimana 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
- Kita harus menggandakan nama kolom untuk membuat referensi absolut dalam referensi terstruktur.
- Kami tidak dapat menyalin rumus referensi terstruktur. Sebagai gantinya, kita perlu menyeret rumusnya.
- Kami tidak dapat melihat sel mana yang kami rujuk dalam referensi terstruktur.
- Kami dapat mematikannya jika Anda tidak tertarik dengan referensi terstruktur.
Artikel yang Direkomendasikan
Artikel ini telah menjadi panduan tentang apa itu Referensi Terstruktur di Excel. Di sini kita belajar cara membuat referensi terstruktur di Tabel ExcelTabel ExcelDi excel, tabel adalah rentang dengan data dalam baris dan kolom, dan akan diperluas saat data baru dimasukkan ke dalam rentang di baris atau kolom baru mana pun dalam tabel. Untuk menggunakan tabel, klik pada tabel dan pilih rentang data. Baca selengkapnya dengan contoh dan soal serta cara mematikannya. Anda dapat mempelajari lebih lanjut dari Excel dari fungsi-fungsi berikut: –
- Referensi Campuran di ExcelReferensi Campuran Di ExcelReferensi campuran adalah jenis referensi sel yang berbeda dari referensi sel absolut dan relatif. Dalam referensi sel campuran, kami hanya merujuk ke kolom sel atau baris sel.Baca lebih lanjut
- Referensi Melingkar di ExcelReferensi Melingkar Di ExcelReferensi melingkar di Excel mengacu pada sel yang sama yang sedang kita kerjakan. Referensi melingkar adalah jenis pop-up atau peringatan yang ditampilkan oleh Excel bahwa kami menggunakan referensi melingkar dalam rumus kami dan perhitungannya mungkin salah. Baca selengkapnya
- Referensi Relatif di ExcelReferensi Relatif Di ExcelDi Excel, referensi relatif adalah tipe referensi sel yang berubah saat rumus yang sama disalin ke sel atau lembar kerja yang berbeda. Katakanlah kita memiliki =B1+C1 di sel A1, dan kita menyalin rumus ini ke sel B2 dan menjadi C2+D2.baca lebih lanjut
- Referensi Absolut di ExcelReferensi Absolut Di ExcelReferensi absolut di excel adalah jenis referensi sel di mana sel yang dirujuk tidak berubah, seperti yang terjadi pada referensi relatif. Dengan menekan f4, kita dapat membuat formula untuk referensi absolut.baca lebih lanjut
