Referensi Eksternal: Cara Membuat Rumus Antar Sheet di Excel

Ketika bekerja dengan data di Excel, sering kali kita perlu menghubungkan informasi dari worksheet atau workbook lain. Hubungan ini disebut sebagai referensi eksternal, yaitu penggunaan data yang berasal dari luar lembar kerja aktif.

Artikel ini membahas cara membuat referensi beda sheet, referensi beda file, serta bagaimana memberi nama pada referensi eksternal untuk mempermudah penggunaannya.

File: referensi-eksternal-excel.xlsx, data-beda-file.xlsx.

Apa Itu Referensi Eksternal di Excel?

Referensi eksternal adalah referensi yang mengacu pada cell di luar worksheet aktif. Cell ini bisa berada di worksheet berbeda dalam satu workbook atau di workbook lain.

=A1

=Sheet1!A1

='[Data.xlsx]Sheet1'!A1

='C:\Folder\[Data.xlsx]Sheet1'!A1

Rumus Beda Sheet di Excel

Jika data yang diperlukan berada di sheet lain dalam workbook yang sama, gunakan referensi beda sheet dalam rumus. Referensi beda sheet menggunakan format SheetName!CellReference dalam rumus.

=MAX(Sheet1!B2:B6)

Jika nama sheet mengandung spasi atau karakter istimewa, gunakan petik tunggal:

=MAX('Penjualan Mei'!B2:B6)

Cara menggunakan Rumus Beda Sheet

Langkah-langkah:

1. Buka sheet di mana rumus akan ditulis.
2. Di cell target, ketikkan rumus. Contoh: =MAX(.
3. Buka sheet sumber.
4. Pilih cell atau range yang diinginkan, misalnya B2:B6.
5. Ketik tutup kurung ).
6. Tekan Enter.

Hasil rumus:

=MAX('01'!B2:B6)

Rumus Excel Beda File

Jika data yang dibutuhkan berada di workbook lain, kita dapat membuat referensi beda file.

='[Data.xlsx]Sheet1'!$B$2:$B$6)

='[Data Penjualan.xlsx]Sheet 1'!$B$2:$B$6)

='D:\Studio\[Data Penjualan.xlsx]Sheet1'!$B$2:$B$6

Hal penting:

• Nama file harus selalu berada dalam kurung siku.
• Nama file dan nama sheet berada di antara petik tunggal.
• Excel tidak mengizinkan dua file dengan nama sama dibuka bersamaan untuk menghindari konflik referensi.

Cara Membuat Rumus Excel Beda File

1. Buka workbook tujuan and workbook sumber.
2. Pada salah satu worksheet di workbook tujuan, ketik rumus. Misalkan =AVERAGE(.
3. Pada workbook sumber, pilih cell atau range yang diinginkan.
4. Ketik tutup kurung ), lalu tekan Enter.

Hasil rumus:

=AVERAGE('[data-beda-file.xlsx]Sheet 01'!$B$2:$B$6)

Cara Memberi Nama pada Rumus Referensi Eksternal

Jika referensi eksternal sering digunakan dan cukup panjang, sebaiknya berikan nama pada rumus agar lebih ringkas, serta mudah dibaca dan digunakan.

Cara memberi nama rumus referensi eksternal:

1. Klik Formulas → Defined Names → Define Name.
2. Masukkan nama (misalnya Rerata_Penjualan_Juni).
3. Tentukan Scope, yaitu Workbook atau Worksheet.
4. Berikan keterangan yang sesuai di Comment.
5. Masukkan referensi eksternal pada Refers To. Misalkan: =AVERAGE('[data-beda-file.xlsx]Sheet 01'!$B$2:$B$6).
6. Klik OK.

Baca: Cara Memberikan Nama di Excel.

Cara Menggunakan Nama Referensi Eksternal

Setelah referensi eksternal diberi nama, kita bisa menggunakannya di berbagai worksheet atau workbook.

Format penulisan nama referensi:

=Rerata_Penjualan_Juni

=NamaSheet!Rerata_Penjualan_Juni

='[NamaFile.xlsx]NamaSheet'!Rerata_Penjualan_Juni

Cara Menggunakan Nama Referensi dengan Paste Name

Excel menyediakan fitur Paste Name untuk mempermudah penggunaan referensi eksternal, alih-alih menuliskan langsung.

Cara menggunakan Paste Name:

1. Buka sheet tujuan, ketik = (tanda sama dengan).
2. Pada Formulas → Use in Formula, klik Paste Names (atau cukup tekan F3), untuk menampilkan kotak dialog Paste Name.
3. Pilih nama referensi yang diinginkan.
4. Klik OK atau tekan Enter.

Hasil: Excel akan memasukkan nama referensi eksternal secara otomatis tanpa perlu mengetik manual.

Kesimpulan

Referensi eksternal di Excel memungkinkan kita untuk menghubungkan data dari sheet atau workbook lain, meningkatkan fleksibilitas dalam pengolahan data. Dengan memahami format penulisan referensi, penggunaan nama referensi eksternal, serta teknik Paste Name, pengguna dapat bekerja lebih cepat dan lebih efisien dalam membuat rumus antar file.