Bagaimana Membuat Rujukan Berstruktur di Excel?
Rujukan Berstruktur bermula dengan jadual excel. Sebaik sahaja jadual yang dibuat di excel secara automatik akan membuat rujukan berstruktur untuk anda
Sekarang lihat gambar di bawah.
- Langkah 1: Saya telah memberikan pautan ke sel B3, bukannya menunjukkan pautan sebagai B2 yang ditunjukkan sebagai Jadual1 [@Jualan]. Di sini Jadual1 adalah nama jadual dan @Jualan adalah lajur yang kita rujuk. Semua sel dalam lajur ini disebut dengan nama Jadual dan diikuti dengan nama tajuk lajur.
- Langkah 2: Sekarang saya akan menukar nama jadual menjadi Data_Table dan menukar tajuk lajur ke Amount .
- Langkah 3: Untuk menukar nama jadual letakkan kursor di dalam jadual> pergi ke Reka Bentuk> Nama Jadual.
- Langkah 4: Sebutkan nama jadual sebagai Data_Table.
- Langkah 5: Sekarang ubah berikan rujukan ke sel B3.
Oleh itu, kita telah memahami bahawa rujukan berstruktur mempunyai dua bahagian Nama Jadual & Nama Lajur.
Contoh
Anda boleh memuat turun Templat Excel Rujukan Berstruktur ini di sini - Templat Excel Rujukan BerstrukturContoh # 1
Dengan menggunakan rujukan berstruktur, anda dapat menjadikan formula anda dinamik. Tidak seperti rujukan sel biasa, ia membenarkan formula menjadi hidup sekiranya terdapat penambahan dan penghapusan dalam julat data.
Izinkan saya menggunakan formula SUM untuk julat normal dan jadual excel.
Formula SUM untuk Julat Normal.
Formula SUM untuk Jadual Excel.
Izinkan saya menambahkan beberapa baris pada data jadual normal dan excel. Saya telah menambahkan 2 item baris ke data, sekarang lihat perbezaannya.
Rujukan berstruktur dalam jadual excel menunjukkan nilai yang dikemas kini tetapi julat data normal tidak menunjukkan nilai yang dikemas kini melainkan anda membuat beberapa perubahan pada formula secara manual.
Contoh # 2
Sekarang, lihat satu lagi contoh. Saya mempunyai maklumat Nama Produk, Kuantiti, dan Harga. Dengan menggunakan maklumat ini, saya perlu sampai pada Nilai Jualan.
Untuk mendapatkan nilai jualan, rumusnya adalah Harga * Harga . Mari kita gunakan formula ini dalam jadual.
Formula mengatakan [@QTY] * [@PRICE]. Ini lebih difahami daripada rujukan B2 * C2 biasa. Kami tidak mendapat nama jadual jika meletakkan formula di dalam jadual.
Masalah dengan Rujukan Berstruktur Excel
Semasa menggunakan rujukan berstruktur, kami menghadapi beberapa masalah yang terdapat di bawah.
Masalah # 1
Rujukan berstruktur mempunyai masalah mereka sendiri juga. Kita semua biasa menggunakan formula excel dan menyalin atau menyeretnya ke sel yang lain. Ini bukan proses yang sama dalam Rujukan Terstruktur, ia berfungsi sedikit berbeza.
Sekarang, lihat contoh di bawah. Saya telah menggunakan formula SUM di excel untuk julat normal.
Sekiranya saya ingin menjumlahkan Harga dan Nilai Jualan, saya hanya akan menyalin dan menampal atau menyeret formula semasa ke dua sel lain dan ini akan memberi saya nilai SUM dari Nilai & Nilai Jualan.
Sekarang gunakan formula yang sama untuk jadual excel untuk lajur Qty.
Sekarang kita mendapat jumlah lajur Kuantiti. Seperti julat normal, formula salin formula semasa dan tampalkannya ke lajur Harga untuk mendapatkan jumlah Harga.
Ya Tuhan !!! Ia tidak menunjukkan jumlah lajur Harga melainkan masih menunjukkan jumlah lajur Kuantiti sahaja. Oleh itu, kita tidak dapat menyalin dan menampal formula ini ke sel bersebelahan atau sel lain untuk merujuk pada lajur atau baris relatif.
Seret Formula untuk Menukar Rujukan
Sekarang kita tahu hadnya, kita tidak dapat melakukan pekerjaan copy-paste lagi dengan rujukan berstruktur. Lalu bagaimana kita mengatasi batasan ini?
Penyelesaiannya sangat mudah, kita hanya perlu menyeret formula dan bukannya menyalin. Pilih sel formula dan gunakan pemegang isi dan seret ke sel yang tersisa untuk menukar rujukan lajur ke Harga dan Nilai Jualan.
Sekarang kita telah mengemas kini formula untuk mendapatkan jumlah masing-masing.
Masalah # 2
Kami telah melihat satu masalah dengan rujukan struktur dan kami juga menemui jalan keluarnya, tetapi kami mempunyai satu masalah lagi di sini, kami tidak dapat menjadikan panggilan tersebut sebagai rujukan mutlak jika kami menyeret formula ke sel lain.
Mari kita lihat contoh di bawah sekarang. Saya mempunyai jadual penjualan dengan pelbagai entri dan saya ingin menggabungkan data dengan menggunakan fungsi SUMIF di excel.
Sekarang saya akan menggunakan fungsi SUMIF untuk mendapatkan nilai jualan gabungan untuk setiap produk.
Saya telah menggunakan formula untuk bulan Jan, kerana ini adalah rujukan terstruktur yang tidak dapat kami salin dan tampalkan formula ke dua lajur yang tersisa, ia tidak akan mengubah rujukan ke Februari & Mar, jadi saya akan menyeret formula.
Oh !! Saya tidak mendapat nilai dalam lajur Februari & Mac. Apa masalahnya ??? Perhatikan formula dengan teliti.
Kami telah menyeret formula dari bulan Jan. Dalam fungsi SUMIF argumen pertama adalah Criteria Range Sales_Table [Product] kerana kami menyeret formula ia berubah menjadi Sales _Table [Jan].
Jadi bagaimana kita menghadapinya ?? Kita perlu membuat hujah pertama iaitu lajur Produk sebagai lajur mutlak dan lajur lain sebagai rujukan relatif. Tidak seperti rujukan biasa, kami tidak mempunyai kemewahan menggunakan kunci F4 untuk mengubah jenis rujukan.
Penyelesaiannya adalah kita perlu menduplikasi ruangan rujukan seperti yang ditunjukkan pada gambar di bawah.
Sekarang kita boleh menyeret formula ke dua lajur yang lain. Julat Kriteria akan tetap dan rujukan lajur lain akan berubah dengan sewajarnya.
Petua Pro: Untuk menjadikan ROW sebagai rujukan mutlak, kita perlu membuat entri ROW berganda tetapi kita perlu meletakkan simbol @ sebelum nama ROW.
= Jadual_Jualan [@ [Produk]: [Produk]]
Bagaimana Mematikan Rujukan Berstruktur di Excel?
Sekiranya anda bukan peminat rujukan berstruktur, anda boleh mematikannya dengan mengikuti langkah-langkah berikut.
- Langkah 1: Pergi ke FILE> Pilihan.
- Langkah 2: Rumus> Nyahtanda Gunakan nama jadual dalam formula.
Perkara yang Perlu Diingat
- Untuk membuat rujukan mutlak dalam rujukan berstruktur, kita perlu menggandakan nama lajur.
- Kita tidak boleh menyalin formula rujukan berstruktur sebaliknya kita perlu menyeret formula.
- Kita tidak dapat melihat dengan tepat sel mana yang kita rujuk dalam rujukan berstruktur.
- Sekiranya anda tidak berminat dengan rujukan berstruktur, anda boleh mematikannya.
