Cara Melintasi Referensi Sel Antara Microsoft Excel Spreadsheets

Di Microsoft Excel, tugas umum untuk merujuk sel di lembar kerja lain atau bahkan di file Excel yang berbeda. Pada awalnya, ini bisa terasa sedikit menakutkan dan membingungkan, tetapi begitu Anda memahami cara kerjanya, itu tidak terlalu sulit.

Di artikel ini, kita akan melihat bagaimana mereferensikan sheet lain dalam file Excel yang sama dan bagaimana mereferensikan file Excel yang berbeda. Kami juga akan membahas hal-hal seperti cara referensi rentang sel dalam suatu fungsi, cara membuat hal-hal lebih sederhana dengan nama yang ditentukan, dan cara menggunakan VLOOKUP untuk referensi dinamis.

Bagaimana Referensi Lembar Lain di File Excel yang Sama

Referensi sel dasar ditulis sebagai huruf kolom diikuti dengan nomor baris.

Jadi referensi sel B3 mengacu pada sel di persimpangan kolom B dan baris 3.

Saat merujuk ke sel di lembar lain, referensi sel ini didahului dengan nama lembar lain. Misalnya, di bawah ini adalah referensi ke sel B3 pada nama lembar “Januari.”

=January!B3

Tanda seru (!) Memisahkan nama sheet dari alamat sel.

Jika nama sheet berisi spasi, maka Anda harus menyertakan nama dengan tanda kutip tunggal dalam referensi.

='January Sales'!B3

Untuk membuat referensi ini, Anda bisa mengetiknya langsung ke sel. Namun, lebih mudah dan lebih dapat diandalkan untuk membiarkan Excel menulis referensi untuk Anda.

Ketik tanda sama dengan (=) ke dalam sel, klik pada tab Sheet, dan kemudian klik sel yang Anda ingin referensi silang.

Saat Anda melakukan ini, Excel menulis referensi untuk Anda di Formula Bar.



Tekan Enter untuk menyelesaikan formula.

Bagaimana Referensi File Excel Lain

Anda bisa merujuk ke sel buku kerja lain menggunakan metode yang sama. Pastikan Anda membuka file Excel lainnya sebelum mulai mengetik rumus.

Ketik tanda sama dengan (=), alihkan ke file lain, dan kemudian klik sel dalam file yang ingin Anda referensi. Tekan Enter saat Anda selesai.

Referensi silang yang lengkap berisi nama buku kerja lain yang terlampir dalam tanda kurung siku, diikuti dengan nama lembar dan nomor sel.

=[Chicago.xlsx]January!B3

Jika nama file atau lembar berisi spasi, maka Anda harus menyertakan referensi file (termasuk tanda kurung) dalam tanda kutip tunggal.

='[New York.xlsx]January'!B3

Dalam contoh ini, Anda dapat melihat tanda dolar ($) di antara alamat sel. Ini adalah referensi sel absolut (Cari tahu lebih lanjut tentang referensi sel absolut).

Saat mereferensikan sel dan rentang pada file Excel yang berbeda, referensi dibuat absolut secara default. Anda dapat mengubahnya ke referensi relatif jika diperlukan.

Jika Anda melihat rumus saat buku kerja yang dirujuk ditutup, itu akan berisi seluruh jalur ke file itu.



Meskipun membuat referensi ke buku kerja lain sangat mudah, mereka lebih rentan terhadap masalah. Pengguna membuat atau mengganti nama folder dan memindahkan file dapat merusak referensi ini dan menyebabkan kesalahan.

Menyimpan data dalam satu buku kerja, jika mungkin, lebih andal.

Bagaimana Referensi Silang Rentang Sel dalam suatu Fungsi

Merujuk sel tunggal cukup bermanfaat. Tetapi Anda mungkin ingin menulis fungsi (seperti SUM) yang mereferensikan rentang sel di lembar kerja atau buku kerja lain.

Mulai fungsi seperti biasa dan kemudian klik pada lembar dan rentang sel — seperti yang Anda lakukan pada contoh sebelumnya.

Dalam contoh berikut, fungsi SUM menjumlahkan nilai dari rentang B2: B6 pada lembar kerja bernama Penjualan.

=SUM(Sales!B2:B6)

Cara Menggunakan Nama Didefinisikan untuk Referensi Silang Sederhana

Di Excel, Anda bisa menetapkan nama ke sel atau rentang sel. Ini lebih bermakna daripada alamat sel atau rentang ketika Anda melihatnya kembali. Jika Anda menggunakan banyak referensi dalam spreadsheet Anda, memberi nama referensi itu dapat membuatnya lebih mudah untuk melihat apa yang telah Anda lakukan.

Bahkan lebih baik, nama ini unik untuk semua lembar kerja di file Excel itu.

Misalnya, kami dapat memberi nama sel 'ChicagoTotal' dan kemudian referensi silang akan berbunyi:

=ChicagoTotal

Ini adalah alternatif yang lebih bermakna untuk referensi standar seperti ini:

=Sales!B2

Sangat mudah untuk membuat nama yang ditentukan. Mulailah dengan memilih sel atau rentang sel yang ingin Anda beri nama.

Klik di Kotak Nama di sudut kiri atas, ketikkan nama yang ingin Anda tetapkan, dan kemudian tekan Enter.



Saat membuat nama yang ditentukan, Anda tidak dapat menggunakan spasi. Oleh karena itu, dalam contoh ini, kata-kata telah digabungkan dengan nama dan dipisahkan dengan huruf kapital. Anda juga dapat memisahkan kata dengan karakter seperti tanda hubung (-) atau garis bawah (_).

Excel juga memiliki Name Manager yang memudahkan pemantauan nama-nama ini di masa depan. Klik Rumus> Manajer Nama. Di jendela Pengelola Nama, Anda bisa melihat daftar semua nama yang ditentukan di buku kerja, di mana mereka berada, dan nilai apa yang saat ini mereka simpan.



Anda kemudian dapat menggunakan tombol di bagian atas untuk mengedit dan menghapus nama-nama yang ditentukan ini.

Cara Memformat Data sebagai Tabel

Saat bekerja dengan daftar data terkait yang luas, menggunakan Excel Format sebagai fitur Tabel dapat menyederhanakan cara Anda referensi data di dalamnya.

Ambil tabel sederhana berikut ini.



Ini bisa diformat sebagai tabel.

Klik pada sel dalam daftar, beralih ke tab "Beranda", klik tombol "Format sebagai Tabel", lalu pilih gaya.



Konfirmasikan bahwa rentang sel sudah benar dan bahwa tabel Anda memiliki header.



Anda kemudian dapat menetapkan nama yang bermakna untuk tabel Anda dari tab "Desain".



Kemudian, jika kami perlu menjumlahkan penjualan Chicago, kami bisa merujuk ke tabel dengan namanya (dari lembar mana saja), diikuti oleh braket persegi ([) untuk melihat daftar kolom tabel.



Pilih kolom dengan mengklik dua kali dalam daftar dan masukkan braket kotak penutup. Formula yang dihasilkan akan terlihat seperti ini:

=SUM(Sales[Chicago])

Anda dapat melihat bagaimana tabel dapat membuat referensi data untuk fungsi agregasi seperti SUM dan AVERAGE lebih mudah daripada referensi sheet standar.

Tabel ini kecil untuk keperluan demonstrasi. Semakin besar tabel dan semakin banyak lembar yang Anda miliki di buku kerja, semakin banyak manfaat yang akan Anda lihat.

Cara Menggunakan Fungsi VLOOKUP untuk Referensi Dinamis

Referensi yang digunakan dalam contoh-contoh sejauh ini semuanya telah diperbaiki pada sel atau rentang sel tertentu. Itu hebat dan seringkali cukup untuk kebutuhan Anda.

Namun, bagaimana jika sel yang Anda rujuk memiliki potensi untuk berubah ketika baris baru dimasukkan, atau seseorang mengurutkan daftar?

Dalam skenario itu, Anda tidak dapat menjamin nilai yang Anda inginkan akan tetap berada di sel yang sama dengan yang awalnya Anda referensikan.

Alternatif dalam skenario ini adalah menggunakan fungsi pencarian dalam Excel untuk mencari nilai dalam daftar. Ini membuatnya lebih tahan lama terhadap perubahan pada lembaran.

Dalam contoh berikut, kami menggunakan fungsi VLOOKUP untuk mencari karyawan di lembar lain dengan ID karyawan mereka dan kemudian mengembalikan tanggal mulai mereka.

Di bawah ini adalah contoh daftar karyawan.



Fungsi VLOOKUP melihat ke bawah kolom pertama dari sebuah tabel dan kemudian mengembalikan informasi dari kolom yang ditentukan ke kanan.

Fungsi VLOOKUP berikut mencari ID karyawan yang dimasukkan ke dalam sel A2 dalam daftar yang ditunjukkan di atas dan mengembalikan tanggal yang digabungkan dari kolom 4 (kolom keempat tabel).

=VLOOKUP(A2,Employees!A:E,4,FALSE)

Di bawah ini adalah ilustrasi tentang bagaimana rumus ini mencari daftar dan mengembalikan informasi yang benar.



Hal yang hebat tentang VLOOKUP ini atas contoh-contoh sebelumnya adalah bahwa karyawan akan ditemukan bahkan jika daftar berubah secara berurutan.

Catatan: VLOOKUP adalah formula yang sangat berguna, dan kami hanya menggores permukaan nilainya dalam artikel ini. Anda dapat mengetahui lebih lanjut tentang cara menggunakan VLOOKUP dari artikel kami tentang masalah ini.

Share :