Anda disini: M Jurnal » Rumus Excel » Cara Menggunakan Rumus Excel Beda File /External Reference

Cara Menggunakan Rumus Excel Beda File /External Reference

Jika Anda ingin menggunakan Rumus Excel untuk beda File, maka ada baiknya pahami penggunaan External Reference. Dalam Excel, External Reference merupakan sebuah reference yang digunakan dalam sebuah file Excel ke File Excel lainnya.

Misalnya, Saya memiliki 2 buah file Excel dengan nama Cabang 1 dan Cabang 2. Kemudian Saya menggunakan Rumus SUM ke dalam File Cabang 1. Rumus ini akan menjumlahkan data yang terdapat pada File Cabang 2.

Nah, keadaan seperti ini lah yang disebut dengan External Reference dalam Excel atau Penggunaan Rumus Beda File.

Namun, agar bisa menggunakan External Reference, mulailah pelajari dari aturan penulisannya terlebih dahulu. Untuk menambah pemahaman, Saya melengkapi panduan ini dengan beberapa contoh. Sudah siap ? Mari kita mulai…

Aturan Penulisan Rumus Excel Untuk Beda File

Masing-masing rumus Excel memiliki aturan penulisan tersendiri. Terkadang ada Rumus yang memiliki aturan serupa dan ada juga yang berbeda. Begitu juga dengan External Reference. Sederhananya, aturan penulisan rumus Excel beda file seperti berikut:

Aturan Penulisan Rumus Excel Beda File (External Reference):
=[File_Name]Sheet_Name!Reference

Secara default, hanya ada 1 argument External Reference yang terdiri dari 3 bagian. Ketiga bagian tersebut memiliki arti sebagai berikut:

  1. [File_Name] adalah nama file Excel yang menjadi sumber. Nama File ini harus menyertakan Format File sumber seperti .xlsx atau .xls atau .csv.
  2. Sheet_Name adalah nama Sheet yang menjadi sumber data. Nama Sheet ini memiliki aturan yang sama dengan penggunaan 3D Reference.
  3. Reference adalah cell tujuan (sumber). Dan ini juga sama dengan penggunaan 3D Reference.

Penting! Sheet_Name dan Reference sudah Saya bahas lengkap pada Panduan 3D Reference (Rumus Beda Sheet). Saya harap Anda sudah memahami penggunaan 3D Reference sebelum melanjutkan. Selengkapnya lihat Pintasan Panduan.

Ingat!, aturan penulisan External Reference di atas hanya aturan default (rumus standar). Misalnya rumus seperti ini: =[Cabang1.xlsx]Sheet1!B2

Rumus Excel Beda File (External Reference):
=[Cabang1.xlsx]Sheet1!B2

Artinya:
Ambil data pada File Cabang1.xlsx Sheet Dengan Nama: Sheet1 Cell B2

Tentu sudah jelas dong bahwa Rumus ini akan mengambil data pada Cell B2 dalam Sheet1 dari File Excel yang bernama Cabang1.xlsx.

Nah, jika Anda menggunakan Rumus / Fungsi yang lebih kompleks, tentu Anda juga wajib memahami aturan penulisan masing-masing Fungsi tersebut.

Misalnya menggunakan Fungsi SUM. Tentu Anda juga harus memahami bahwa Fungsi SUM terdiri dari beberapa argument juga seperti (number1,[number2],…). Jadi Anda harus memahami Argument number1, [number2] dan … dalam Fungsi SUM tersebut.

Penting! mempelajari aturan penulisan masing-masing Rumus dan Fungsi Excel, silahkan kunjungi Artikel: Kumpulan Rumus dan Fungsi Excel.

Karena Panduan ini lebih Saya khususkan untuk panduan belajar dasar-dasar penggunaan Rumus Excel Beda File (External Reference). Sekarang, coba perhatikan beberapa contoh berikut:

Contoh Membuat External Reference

Misalnya, katakanlah Saya memiliki 2 File Excel berbeda masing-masing dengan nama Cabang1.xlsx dan Cabang Pusat.xlsx.

Pada file Cabang1.xlsx, Saya memiliki data Penjualan pada Sheet Penjualan. Ingat, nama sheet nya adalah Penjualan. Sedangkan pada File Cabang Pusat.xlsx, Saya juga memiliki data pada Sheet Penjualan Pusat. Kurang lebih tampilan ke dua file seperti ini:

Contoh Membuat External Reference Excel

Penting! Untuk memudahkan pekerjaan, Anda bisa menggunakan fitur Arrange Excel atau Multitasking Windows untuk membuka dan melihat 2 atau lebih file Excel secara bersamaan. Selengkapnya silahkan ikuti Panduan Cara Lihat Banyak File Excel Bersamaan.

Kemudian Saya membuat File Excel Baru dengan Nama Rekap Penjualan.xlsx. Dalam file ini, Saya akan menghitung total penjualan dari ke dua File sebelumnya (Cabang1.xlsx dan Cabang Pusat.xlsx).

Silahkan perhatikan cara menggunakan Rumus Beda File Excel (External Reference) berikut:

Cara Menggunakan Rumus Excel untuk Beda File / External Reference
  1. Pertama, Buka Semua File Excel yang menjadi sumber data (Cabang1.xlsx dan Cabang Pusat.xlsx) serta File Rekap Penjualan.xlsx (jika ada). Pastikan ke 3 File sudah tersimpan dalam komputer Anda. Lihat Panduan Save & Save As Excel.
  2. Kedua, pada File Excel Rekap Penjualan.xlsx, silahkan ketik tanda sama dengan (untuk memulai rumus) pada Cell yang Anda inginkan.
  3. Ketiga, klik Cell B6 pada WorkBook Cabang1.xlsx sehingga rumus menjadi =[Cabang1.xlsx]Penjualan!$B$6
  4. Keempat, ketik tanda tambah (+).
  5. Kelima, klik Cell B6 pada WorkBook Cabang Pusat.xlsx kemudian tekan Enter. Lihat hasilnya:
Contoh Rumus Excel untuk Beda File / External Reference

Excel memberikan hasil 62,160,000 yaitu hasil dari 31,920,000 + 30,240,000.

Terlihat sangat sederhana dan mudah bukan ? Tapi Saya yakin dengan mengetahui makna External Reference dari Rumus yang digunakan bisa menambah pemahaman Anda.

Apa Makna External Reference Tersebut ?

Rumus Excel Beda File (External Reference):
=[Cabang1.xlsx]Penjualan!$B$6+‘[Cabang Pusat.xlsx]Penjualan Pusat’!$B$6

Artinya:
Hitung Nilai pada File Cabang1.xlsx Sheet Penjualan Cell B6 ditambah dengan Nilai pada File Cabang Pusat.xlsx Sheet Penjualan Pusat Cell B6

Ada beberapa hal yang ingin Saya jelaskan… Perhatikan, Format ke-2 External Reference dalam argument Rumus tersebut berbeda.

  • [Cabang1.xlsx]Penjualan!$B$6
  • [Cabang Pusat.xlsx]Penjualan Pusat!$B$6

Pada External Reference yang kedua, Excel mengapit [File_Name] dan Sheet_Name menggunakan Kutip 1. Sementara Argument External Reference yang pertama tidak.

Hal ini terjadi karena Nama File dan Nama Sheet sumber menggunakan 2 kata yang dipisah dengan spasi yaitu Cabang Pusat.xlsx dan Sheet Penjualan Pusat.

Konsepnya sama saja dengan penggunaan 3D Reference bukan ? Jadi jangan heran jika Anda menemukan kondisi demikian.

Oh ya, Secara default, Excel juga menerapkan Absolute Reference (simbol $) pada Nama Cell yaitu $B$6.

Jadi jika Anda ketik Rumus External Reference secara manual, maka Anda boleh-boleh saja mengetik simbol $ secara manual ataupun tidak menggunakannya sama sekali. Ini tergantung… Yaa… tergantung tujuan Anda.

Kenapa Tiba-Tiba Rumus Tersebut Berubah ?

Secara default, Rumus yang menggunakan External Reference akan berubah dan menyesuaikan setelah Anda mengubah / menyimpan / menutup File Excel yang menjadi sumber.

Dalam contoh ini, Saya menyimpan dan menutup File Excel sumber (Cabang1.xlsx dan Cabang Pusat.xlsx). File-file tersebut Saya simpan dalam Folder Latihan pada Disk E. Setelah menutup ke dua File Excel tersebut, perhatikan perubahaan Rumus yang ada pada File Rekap Penjualan.xlsx.

Kenapa External Reference tiba-tiba berubah ?

Benar bukan ? External Reference kini menyertakan nama Folder dan Disk tempat menyimpan File Excel yang menjadi sumber tersebut. Sehingga rumus berubah menjadi:

Rumus Excel Beda File (External Reference):
=‘E:\Latihan[Cabang1.xlsx]Penjualan’!$B$6+‘E:\Latihan[Cabang Pusat.xlsx]Penjualan Pusat’!$B$6

E:\Latihan adalah lokasi penyimpanan File Excel tersebut.

Namun perlu Anda ingat, Jangan sembarangan memindahkan File Excel Sumber ke Lokasi penyimpanan lain. Ini bisa menyebabkan kesalahan pada Rumus Anda. Coba perhatikan penjelasan berikut:

Bagaimana Jika Saya Mengubah Salah Satu File Sumber ? Apakah Rumus Akan Berubah ?

Pertama-tama, close terlebih dahulu File Excel sumber dan Rekap Penjualan.xlsx. Save atau Save As jika perlu.

Kemudian, buka File Cabang1.xlsx. Saya mengubah nilai pada Cell B6 sampai E6 menjadi seperti gambar berikut:

Cara Update Rumus Excel untuk Beda File

Selanjutnya simpan file Cabang1.xlsx tersebut dan buka File Rekap Penjualan.xlsx. Secara otomatis, Excel akan memberikan pilihan apakah Anda ingin update data dari Rumus yang menggunakan External Reference atau tidak.

Cara Update Rumus Excel untuk Beda File / External Reference

Klik Update jika Anda ingin menerapkan perubahan dari File Excel Cabang1.xlsx. Klik Don’t Update jika tidak.

Setelah klik Update, secara otomatis, Cell yang menggunakan External Reference akan berubah dan menyesuaikan nilai dari File Sumber seperti gambar berikut:

Cara Update Rumus Excel untuk Beda File / External Reference

Note: Excel akan memberikan peringatan berbeda jika terdapat kesalahan pada rumus atau jika Excel tidak berhasil menemukan File Sumber. Coba perhatikan penjelasan berikut:

Kesalahan Rumus Excel Beda File Yang Sering Terjadi

Oh ya, sebelum membahas kesalahan Rumus Excel beda file, coba perhatikan Security Warning berikut:

Mengatasi Security Warning pada External Reference Excel

Tidak perlu khawatir jika ini yang Anda alami. Karena Security Warning ini hanya peringatan dari Excel bahwa External Reference yang ada dalam file Excel ini tidak otomatis ter-update.

Untuk melanjutkan, silahkan klik Enable Content. Dan selanjutnya Anda tidak akan melihat peringatan tersebut.

Sementara itu, kesalahan yang sering terjadi dalam menggunakan Rumus pada File Excel yang berbeda adalah jika Anda sengaja / tidak sengaja mengubah lokasi penyimpanannya atau mengubah nama File Sumber.

Misalnya, (terlebih dahulu, close semua file Excel) kemudian ubah lokasi penyimpanan File Cabang1.xlsx ke Disk E > Folder Percobaan (sebelumnya ada pada Disk E > Folder Latihan).

Sekarang, coba buka File Rekap Penjualan.xlsx. Secara otomatis Excel akan memberikan Pesan Peringatan seperti gambar berikut:

Kesalahan yang sering terjadi ketika menggunakan Rumus Excel untuk Beda File / External Reference

Pesan Peringatan ini sama seperti ketika Anda mengubah isi dari salah satu file Sumber. Disini belum terlihat apakah ada yang salah.

Untuk mengetahui apakah terjadi kesalahan atau tidak, silahkan klik Update. Kesalahan sudah pasti terjadi ketika Excel memberikan peringatan seperti gambar berikut:

Cara Mengatasi Kesalahan External Reference Excel

Ini artinya, Excel tidak menemukan salah satu File Excel yang menjadi Sumber data External Reference. Hal ini terjadi karena Saya memindahkan lokasi penyimpanan salah satu file sumber ke Folder lain.

Selain itu, jika Anda mengubah nama salah satu File Sumber, Excel juga akan memberikan peringatan yang sama, bahwa Excel tidak dapat menemukan File Sumber.

Jika ini yang terjadi, Anda bisa mengembalikan File Excel sumber ke lokasi penyimpanan sebelumnya atau mengubah Link pada Rumus.

Cara Edit Link Rumus Beda File Excel

Untuk mengubah link pada Rumus yang menggunakan External Reference, silahkan ikuti tahap-tahap berikut:

Cara Mengubah Rumus Excel untuk Beda Sheet / External Reference
  1. Pertama, klik Tab Data pada Ribbon.
  2. Kedua, Klik Edit Link pada Connections Group.
  3. Ketiga, muncul Window Edit Links, disini Anda bisa mengatur koneksi ke File Excel lain. Karena Saya mengubah lokasi penyimpanan File Cabang1.xlsx, maka klik nama File tersebut.
  4. Keempat, Klik Change Source…
  5. Kelima, muncul Window Change Source, silahkan cari Lokasi Penyimpanan File tersebut (yang baru). Kemudian klik kiri dan Klik Ok.
  6. Keenam, Klik Close pada Window Edit Links.

Jika Anda melakukan 6 tahap di atas dengan benar, sudah dapat dipastikan tidak ada lagi kesalahan External Reference dalam File Excel ini.

BONUS: Alternatif Rumus Excel Beda File Untuk Rekap Data

Dalam Panduan ini, Saya menggunakan contoh data penjualan yang mana setiap data pada masing-masing File Excel sumber tersusun dengan format yang sama.

Sebetulnya susunan data seperti ini memiliki banyak keuntungan. Salah satunya, Anda bisa menggunakan fitur Consolidate untuk menggabungkan data dari 2 atau lebih WorkBook (File Excel) seperti gambar berikut:

Consolidate Excel Tersinkron

Penting! Selengkapnya tentang Fitur Consolidate, silahkan Pelajari Panduan Menggabungkan 2 Atau Lebih File Excel.

Next… Ada banyak sekali materi tentang Cell Reference yang wajib Anda ketahui. Silahkan gunakan Pintasan Panduan berikut untuk melanjutkan ke SUB-BAB berikutnya…

Pintasan Panduan Cell Reference Excel

  1. Cell References: Pentingnya Cell References pada Excel dalam menggunakan Rumus / Fungsi. Pelajari semua tentang Referensi Relatif, Absolut Kolom / Baris, dan Absolut ( Lambang Dollar $ ).
  2. 3D-Reference: Cara menggunakan Rumus / Fungsi untuk Beda Sheet serta 5+ Contoh Mendalam (Bonus Studi Kasus membuat Rekap Data Antar Sheet).
  3. Structured References: Referensi terstruktur pada Tabel untuk membuat rumus / fungsi lebih mudah dipahami.
  4. Union & Intersect: Belajar Operator Rumus / Fungsi Excel. Seperti koma (,) atau titik koma (;), dan spasi ( ).
  5. External References: (Anda Disini).
  6. Hyperlinks: Cara menghubungkan (tersinkron) File Excel ke File lainnya atau ke Situs Website dll.
  7. Percent Change: Contoh menggunakan Nilai Absolut / Referensi Absolut / Symbol $ lengkap.
  8. Copy Exact Formula: Cara copy rumus / fungsi otomatis menyesuaikan referensi.

Komentar Paling Lambat di Balas Pukul 23:59.