Anda disini: M Jurnal » Rumus Excel » 3D Reference Excel: 5+ Cara Menggunakan Rumus Beda Sheet

3D Reference Excel: 5+ Cara Menggunakan Rumus Beda Sheet

3D Reference adalah sebuah teknik menggunakan Rumus / Fungsi untuk perhitungan antar / beda Sheet pada Microsoft Excel. Teknik ini berguna jika Anda ingin menyelesaikan perhitungan data antar sheet dalam 1 file Excel (Workbook).

Note: Setiap Rumus / Fungsi Excel memiliki aturan penulisan masing-masing. Tentu saja penggunaan setiap rumus untuk beda sheet juga berbeda-beda pula. Panduan kali ini akan Saya fokuskan ke bagian paling mendasar dalam menggunakan Rumus / Fungsi beda Sheet.

Saya harap Anda memahami panduan ini terlebih dahulu sebelum mengaplikasikannya pada Rumus / Fungsi yang lebih kompleks (seperti VLOOKUP, IF, SUMIF ataupun kombinasi berbagai rumus lainnya). Sudah siap ? Mari kita mulai dari Aturan Penulisan 3D Reference.

Aturan Penulisan 3D Reference Excel (Rumus Beda Sheet)

Ketika menggunakan rumus dengan sumber data menggunakan Relative Reference dalam 1 sheet yang sama, Anda bisa menggunakan aturan rumus =reference. Argument reference bisa berupa sebuah cell / range data / named range / table bernama.

Penting! Sebelum masuk ke pembahasan mendalam, Saya harap Anda sudah memahami apa itu Range Data ataupun Cell pada Excel. Jika belum, silahkan kunjungi Panduan Range Excel M Jurnal.

Misalnya, jika ingin menjumlahkan cell A1 dan B1 (dalam sheet yang sama), maka Anda bisa menggunakan rumus =A1+B1.

Contoh lainnya jika ingin mencari nilai rata-rata dari Range A1:A7, maka Anda bisa menggunakan Fungsi =AVERAGE(A1:A7). Dalam contoh ini, range A1:A7 sebagai reference.

Namun jika ingin menggunakan Rumus / Fungsi Excel untuk beda / antar sheet, argument Reference (secara default) mengikuti aturan berikut:

3D Reference (Rumus Beda Sheet):
=sheet_name!reference

Dalam penggunaan Rumus / Fungsi beda Sheet, argument reference harus menyertakan argument sheet_name! yaitu Nama Sheet dari Reference yang digunakan. Sementara argument reference bisa berupa sebuah cell / range data / named range / table bernama yang terdapat pada sheet_name!.

Note: Aturan argument sheet_name! juga akan tergantung dengan nama sheet Anda, apakah nama sheet reference Anda menggunakan spasi atau tidak.

Selain itu, Anda juga bisa menggunakan argument sheet_name!reference lebih dari 1. Namun akan tergantung dengan Rumus / Fungsi apa yang akan Anda gunakan.

Misalnya =sheet_name!reference+sheet_name!reference dan seterusnya. Argument sheet_name!reference yang pertama bisa berasal dari sheet1. Sedangkan argument yang kedua bisa berasal dari sheet2 dan seterusnya.

Biar semakin paham, mari simak beberapa contoh berikut:

#1 Jika Nama Sheet Tanpa Spasi

Maksud Name Sheet tanpa Spasi itu seperti ini Sheet1. Jika Anda mengubah nama sheet tersebut menjadi Sheet 1, maka aturan penulisan argument sheet_name! akan berubah.

Saya berikan contoh nama sheet tanpa spasi. Misalnya, Saya memiliki data dalam 2 sheet berbeda. Sheet pertama Saya beri nama Sheet1. dan Sheet kedua Saya beri nama Sheet2.

Penting! Untuk memudahkan pekerjaan, Saya rekomendasikan Anda menggunakan fitur View Miltiple WorkSheets untuk melihat banyak sheet dari 1 file Excel secara bersamaan seperti gambar berikut:

Contoh Rumus Beda Sheet Excel

Dari contoh ini, Saya akan menjumlahkan data dari Cell A1 pada Sheet1 dengan data Cell A7 pada Sheet2. Kemudian hasilnya akan Saya tampilkan pada Cell C1 pada Sheet3.

Waktu yang dibutuhkan: 1 menit.

Untuk menjumlahkan data antar sheet tersebut, silahkan gunakan teknik 3D Reference Excel berikut:

  1. Ketik tanda sama dengan = untuk memulai Rumus.

    Dalam contoh ini, ketik tanda sama dengan “=” pada Cell C1 pada Sheet3
    3D Reference Excel - Cara Menggunakan Fungsi dan Rumus Beda / Antar Sheet pada Excel

  2. Pilih Argument sheet_name!reference yang pertama

    Dalam contoh ini, argument sheet_name!reference yang pertama adalah Cell A1 pada Sheet1. Klik saja cell tersebut, maka secara otomatis Excel akan menambahkan argument ke dalam rumus menjadi =Sheet1!A1
    3D Reference Excel - Cara Menggunakan Fungsi dan Rumus Beda / Antar Sheet pada Excel

  3. Tentukan Operasi Perhitungan Apa yang Anda inginkan.

    Karena dalam contoh ini Saya ingin menjumlahkan, maka ketik tanda tambah +
    3D Reference Excel - Cara Menggunakan Fungsi dan Rumus Beda / Antar Sheet pada Excel

  4. Pilih Argument sheet_name!reference yang Kedua

    Silahkan klik Cell A7 pada Sheet2 sebagai argument sheet_name!reference yang kedua sehingga rumus menjadi =Sheet1!A1+Sheet2!A7
    3D Reference Excel - Cara Menggunakan Fungsi dan Rumus Beda / Antar Sheet pada Excel

  5. (Optional) pilih argument sheet_name!reference Selanjutnya Jika Ada

    Karena dalam contoh ini tidak ada argument sheet_name!reference selanjutnya, maka silahkan tekan enter dan berikut hasilnya:
    3D Reference Excel - Cara Menggunakan Fungsi dan Rumus Beda / Antar Sheet pada Excel

Sebagai tambahan, kurang lebih berikut penjelasan rumus beda sheet tersebut:

3D Reference Excel:
=Sheet1!A1+Sheet2!A7

Artinya:
Data Pada Sheet1 Cell A1 ditambah dengan Data Pada Sheet2 Cell A7

#2 Jika Nama Sheet Dengan Spasi

Saya ulangi kembali, argument sheet_name! akan berubah jika Anda menggunakan spasi pada nama sheet. Misalnya, Saya mengubah nama Sheet2 menjadi Data Sheet2.

Contoh 3D Reference Excel - Rumus Beda / Antar Sheet

Jika nama sheet mengandung spasi, maka Excel akan mengapit nama sheet tersebut didalam argument rumus dengan kutip satu menjadi sheet_name! yaitu =Sheet1!A1+Data Sheet2!A7.

Dengan kata lain, jika nama sheet yang akan menjadi reference rumus mengandung spasi, maka Anda harus mengapitnya dengan kutip satu.

Oh ya, dari contoh-contoh di atas, Saya merekomendasikan untuk menggunakan fitur View Multiple WorkSheet. (Lupa? Baca lagi).

Note: Jika Anda tidak ingin menggunakan fitur tersebut, Anda juga bisa memasukkan argument sheet_name!reference secara manual sesuai aturan penulisan 3D Reference. Misalnya seperti contoh berikut:

Contoh 3D Reference Excel - Rumus Beda / Antar Sheet

Ada 2 WorkSheet yaitu dengan nama sheet: Pusat dan Cabang Yogyakarta. Coba perhatian beberapa kombinasi rumus dan fungsi yang Saya gunakan berikut:

Rumus Beda / Antar Sheet

Cell B4 menggunakan rumus =Pusat!C4+Cabang Yogyakarta!C4. Rumus ini akan menjumlahkan nilai pada Sheet Pusat Cell C4 dengan Sheet Cabang Yogyakarta Cell C4. Begitu juga dengan Cell B5 sampai B7 juga akan menjumlahkan masing-masing cell yang satu baris dengan B5 sampai B7.

Sementara pada Cell C4, Saya menggabungkan 3D Reference dengan Absolute Reference (lambang $) untuk menghitung persentase penjualan per item produk. Rumus pada cell tersebut: =(Pusat!C4+’Cabang Yogyakarta’!C4)/B$8

Penting! Pada SUB-BAB Pertama, Saya sudah membahas lengkap tentang penggunaan Absolute Reference, Silahkan pahami terlebih dahulu tipe reference tersebut. Lihat Pintasan Panduan

Perhatikan Rumus 3D Reference (Beda Sheet) pada Cell C4 =(Pusat!C4+’Cabang Yogyakarta’!C4)/B$8, argument yang berisi B$8 berasal dari sheet yang sama dengan hasil rumus ini. Sementara argument lainnya menyertakan nama sheet sebelum reference cell (seperti Pusat!C4)

#3 Menggunakan Fungsi (Misal SUM, AVERAGE, VLOOKUP dll)

Sebetulnya tidak sulit… Satu-satunya cara untuk memahami penggunaan 3D Reference Excel (Fungsi Antar / Beda Sheet) adalah Pahami Aturan Penulisan Masing-Masing Fungsi Tersebut

Pada awal panduan ini sudah Saya sampaikan bahwa setiap Rumus / Fungsi Excel memiliki aturan penulisan masing-masing (Lupa? Baca Lagi).

Note: Dalam setiap Panduan Fungsi Excel, Saya tidak hanya membahas aturan penulisan fungsi tersebut. Tapi Saya buat pemahasan yang lebih mendalam dengan berbagai contoh termasuk cara menggunakan Fungsi antar sheet (3D Reference).

Sementara pada panduan ini, sekilas Saya berikan contoh untuk beberapa Fungsi…

Contoh 1: Fungsi SUM + 3D Reference

Misalnya, aturan penulisan Fungsi SUM =SUM(number1,[number2],…). Jika Anda menggunakan 3D Reference (rumus antar sheet) pada Fungsi SUM, maka argument number1 atau [number2] atau … bisa Anda ganti dengan argument sheet_name!reference. Misalnya seperti hasil berikut:

Rumus SUM Beda / Antar Sheet

Cell C10 menggunakan Fungsi SUM dengan 3D Reference yaitu =SUM(Pusat!C4:C7,‘Cabang Yogyakarta’!C4:C7). Lihat! Pusat!C4:C7 mengisi argumen number1 dan ‘Cabang Yogyakarta’!C4:C7 mengisi argument [number2] pada Fungsi SUM.

Saya coba bandingkan dengan Fungsi SUM tanpa 3D Reference (lihat hasil Cell B8) Hasil kedua Fungsi SUM tersebut sama-sama Rp 63,052,400.

Penting! Sebaiknya Anda juga harus memahami aturan penulisan Fungsi SUM untuk menghindari gagal paham. Jika belum, silahkan kunjungi Panduan Mendalam Tentang Fungsi SUM Excel

Contoh 2: Fungsi AVERAGE + 3D Reference

Contoh lainnya 3D Reference dengan Fungsi AVERAGE Excel. Kombinasi ini cukup sering digunakan. Namun, coba ingat kembali aturan penulisan rumus AVERAGE =AVERAGE(number1,[number2],…).

Tidak berbeda dengan fungsi SUM bukan ? Jadi Anda bisa mengganti argument number1 atau [number2] dst menjadi argument sheet_name!reference.

Rumus AVERAGE beda / antar sheet

Cell C11 mengganti argument number1 dan [number2] dengan argument sheet_name!reference, sama seperti Fungsi SUM. Sementara Cell C12 dan C13 hanya menggunakan 1 argument saja dan menggantikan argument number1.

Penting! Selengkapnya tentang Fungsi AVERAGE, silahkan kunjungi Panduan Mendalam Fungsi AVERAGE.

Bagaimana, sejauh ini apakah ada kesulitan ? Mudah-mudahan tidak ya… Kita akan masuk ke fungsi tingkat menengah…

Contoh 3: Fungsi VLOOKUP + 3D Reference

Aturan penulisan Fungsi VLOOKUP memiliki 4 argument =VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

Dari ke empat argument tersebut, Anda bisa mengganti argument lookup_value atau table_array atau col_index_num menjadi argument sheet_name!reference.

Namun dari ketiga opsi tersebut, yang paling sering digunakan adalah mengganti table_array dengan sheet_name!reference. Keadaan ini sering terjadi ketika Anda ingin mengambil nilai dari sebuah tabel / range data yang berada pada Sheet lain.

Pada Cell B17 Saya mengganti argument table_array menjadi sheet_name!reference sehingga fungsi VLOOKUP menjadi seperti berikut =VLOOKUP(A17,Pusat!A$3:C$7,3,FALSE).

Selain itu, untuk memudahkan Copy Paste Rumus atau AutoFill rumus, Saya juga menggunakan Absolute Reference (Lambang $).

3D Reference Excel - Rumus VLOOKUP beda / antar sheet

Anda bisa lihat hasilnya, meskipun urutan Produk (Cell A17:A21) Saya acak, Excel tetap memberikan hasil yang benar.

Penting! Selengkapnya tentang cara menggunakan Fungsi VLOOKUP, silahkan kunjungi Panduan Mendalam Fungsi VLOOKUP.

Namun, jika Anda ingin contoh 3D Reference untuk Rumus / Fungsi Lainnya, silahkan kunjungi panduan masing-masing Rumus di Pusat Panduan Rumus / Fungsi Excel M Jurnal

#4 Jika Menggunakan Named Range / Format Tabel Bernama Beda Sheet

Named Range ataupun Format Tabel Bernama dapat disebut juga reference cell dinamis.

Jadi ketika Anda menggunakan named range / tabel bernama ke dalam 3D Reference (Rumus Beda Sheet), maka argument sheet_name!reference dapat Anda gantikan dengan Named Range ataupun Tabel bernama itu sendiri.

Mungkin jika dibayangkan akan terasa sulit… Biar semakin paham, coba perhatikan beberapa contoh berikut:

Contoh 1: Named Range + 3D Reference

Penting! Ada baiknya sebelum Anda mengikuti contoh ini, pahami terlebih dahulu semua tentang Named Range. Silahkan kunjungi Panduan Named Range Excel M Jurnal

Contoh Named Range + 3D Reference Excel

Saya membuat 2 buah named range yaitu Reguler untuk Sheet Reguler Range B4:C7 dan Non_Reguler untuk Sheet Non-Reguler Cell D4:E7.

Nah… karena sudah ada named range, Anda bisa menggunakan Named Range tersebut sebagai pengganti argument sheet_name!reference dari semua contoh sebelumnya.

Misalnya Saya ingin menjumlahkan Total Mahasiswa Kelas Reguler dan Non_Reguler untuk masing-masing Fakultas. Hasilnya akan Saya rekap pada Sheet 3D Reference – Named Range.

Untuk tujuan seperti ini, Anda bisa menggunakan bantuan Fungsi VLOOKUP dan operator penjumlahan (+) seperti berikut: =VLOOKUP(B4,Reguler,2,FALSE)+VLOOKUP(B4,Non_Reguler,2,FALSE)

Cara menggunakan named range + 3D Reference Excel

Perhatikan argument table_array fungsi VLOOKUP menggunakan Named Range Reguler dan Non_Reguler.

Inilah ciri khas dari Named Range. Ketika Anda menggunakan Named Range kedalam argument Rumus / Fungsi, maka Excel akan mengambil data dari Range yang sudah Anda beri nama tersebut.

Menurut Saya, Rumus / Fungsi seperti ini jauh lebih terstruktur dan mudah dibaca daripada harus menggunakan argument sheet_name!reference seperti ‘NR Reguler’!B4:C7 atau ‘NR Non-Reguler’!D4:E7

Contoh 2: Tabel Bernama + 3D Reference

Tidak berbeda jauh dengan Named Range, Kombinasi 3D Reference Excel dengan Tabel Bernama juga bisa membuat Rumus / Fungsi lebih terstruktur dan mudah dibaca.

Contoh 3D Reference + Format Tabel Bernama

Perhatikan baik-baik tabel diatas… Semua data pada Sheet Tabel Reguler dan Tabel Non-Reguler sama-sama disusun pada Row yang sama yaitu ROW 3, 4, 5, 6, dan 7. Ingat baik-baik point ini. Kemudian lihat beberapa hasil kombinasi Fungsi dan Rumus berikut:

Cara menggunakan Rumus Beda Sheet antar tabel bernama

Hasil pada Cell B4 sampai B7 tidak berbeda dengan ketika Anda menggunakan named Range. Dalam contoh ini, argument table_array juga menggunakan Table Name (bukan sheet_name!reference) yaitu =VLOOKUP(B4,T_Reguler,2,FALSE)+VLOOKUP(B4,T_Non_Reguler,2,FALSE)

Namun ada 1 kelebihan Format Tabel Bernama dibandingkan 3D Reference Named Range, yaitu Anda bisa menggunakan struktur tabel ke dalam argument rumus seperti hasil pada Range E4 yang menggunakan Rumus:

3D Reference Tabel Bernama:
=T_Reguler[@Mahasiswa]+T_Non_Reguler[@Mahasiswa]

Artinya:
Data Pada Tabel T_Reguler Kolom Mahasiswa ditambah dengan Data Pada Tabel T_Non_Reguler Kolom Mahasiswa

Seperti yang Saya sebutkan sebelumnya, perhatikan susunan Tabel T_Reguler dan T_Non_Reguler. Setiap baris tabel disusun pada ROW yang sama (ROW 4, 5, 6, dan 7).

Daan… Rumus tersebut juga Saya gunakan pada Row yang sama pada sheet Total Mahasiswa.

Struktur Tabel sebagai argument rumus pada 3D Reference Excel

Jika kondisi data Anda sama seperti contoh ini (disusun dalam baris yang sama), maka Anda bisa menggunakan Struktur Tabel ke dalam argument rumus.

Namun, jika tidak… Kemungkinan besar Excel akan memberikan hasil Pesan Error #VALUE! seperti pada Cell E10 sampai E13. Tentu jauh lebih cocok menggunakan VLOOKUP daripada struktur tabel ke dalam rumus.

So…

Cara Menggunakan 3D Reference Excel (Rumus Beda / Antar Sheet) bergantung seperti apa data Anda dan Rumus / Fungsi apa yang akan Anda gunakan.

Rolan mardani

Penting! Ada baiknya sebelum Anda mengikuti contoh ini, pahami dahulu semua tentang Format Tabel Bernama. Silahkan kunjungi Panduan Tabel Bernama Excel M Jurnal

Bonus: Rekap Data Beda Sheet

Salah satu kegunaan 3D Reference Excel yang paling sering digunakan adalah untuk melakukan perhitungan antar sheet untuk mendapatkan ringkasan data seperti total, rerata dll (alias merekap data).

Fitur ini pun bisa membuat rekap data Anda ter-update secara otomatis. Jadi ketika ada perubahan data pada Sheet Sumber, maka Sheet Rekap-an data juga akan ikut ter-update.

Atau bisa juga ketika Anda ingin menambahkan sheet baru (untuk data yang baru), Anda bisa memilih apakah sheet tersebut akan otomatis dimasukkan ke dalam rumus rekapan data antar sheet atau tidak.

Sepertinya semakin menarik atau hanya perasaan Saya saja… baiklah, coba perhatikan contoh berikut.

Saya memiliki 3 data Penjualan Produk setiap Cabang masing-masing dalam Sheet Sumatera, Jawa dan Kalimantan. Data setiap sheet tersusun dalam format yang sama seperti gambar berikut:

Cara rekap data antar sheet berbeda menggunakan 3D Reference Excel

Note: Setiap Sheet harus berdampingan. Seperti contoh diatas, urutan Sheet Saya Sumatera > Jawa > Kalimantan. Jangan sampai ada sheet lain yang tidak akan diperhitungkan dalam rekap data berada ditengah-tengah sheet sumber (reference).

Kemudian, Saya membuat 1 Sheet Baru dengan nama Rekap Data. Susunan data pada Sheet ini pun “Sengaja” Saya buat sama seperti 3 Sheet lainnya (meskipun boleh berbeda).

Dalam Sheet Rekap Data, Saya akan hitung total penjualan semua cabang (Sumatera, Jawa dan Kalimantan).

Jika berkaca dari beberapa contoh sebelumnya, Anda mungkin akan menggunakan argument sheet_name!reference. Ya… rumus tersebut memang bisa digunakan.

Tapiii… Anda harus mengubah kembali rumus ketika menambahkan sheet baru (jika ingin menyertakannya dalam Sheet Rekap Data.

Saya punya Solusinya… Coba gunakan Rumus ini…

Cara merekap data antar / beda sheet menggunakan 3D Reference Excel

3D reference Excel (Rekap Data):
=SUM(Sumatera:Kalimantan!B4)

Artinya:
Jumlahkan Data Dari Sheet Sumatera sampai Sheet Kalimantan pada setiap Cell B4

Penjelasan:

Argument Sumatera:Kalimantan! artinya sama seperti sebuah Range Data. Misalnya Range A1:C1 adalah Cell A1, B1, dan C1.

Cara merekap data antar / beda sheet menggunakan 3D Reference Excel

Begitu juga Sumatera:Kalimantan! artinya Sheet Sumatera, kemudian sheet sebelah kanan dari sheet Sumatera yaitu Jawa (lihat gambar). Dan terakhir adalah Sheet Kalimantan.

Jadi Fungsi =SUM(Sumatera:Kalimantan!B4), artinya semua data Cell B4 dari Sheet tersebut akan dijumlahkan oleh Fungsi SUM.

Note: Dalam contoh ini hanya ada 1 Sheet diantara Sheet Sumatera dan Kalimantan, yaitu Sheet Jawa. Apabila ada sheet lain diantara Sheet tersebut, maka Excel juga akan menjumlahkan data cell B4 dari sheet tersebut.

Misalnya, Saya tambahkan Sheet Sulawesi diantara Sheet Jawa dan Kalimantan dengan format data yang sama dengan Sheet tersebut. Secara Otomatis, Excel juga menghitung nilai Cell B4 pada Sheet Sulawesi ke dalam Rumus pada Sheet Rekap Data. Lihat hasilnya…

Rumus 3D reference Excel untuk merekap data seperti=SUM(Sumatera:Kalimantan!B4)

Hasil rumus =SUM(Sumatera:Kalimantan!B4) berubah sesuai penambahan nilai dari Sheet yang baru (Sulawesi).

Selanjutnya, karena data pada Sheet Sumatera sampai Kalimantan serta Sheet Rekap Data disusun dengan format yang sama, maka Anda bisa melakukan AutoFill ataupun Copy Paste Rumus untuk merekap semua data penjualan dari cell lainnya. Berikut hasilnya:

AutoFill dan Copy Paste Rumus beda sheet

Bagaimana, mudah bukan memahami teknik 3D Reference Excel ? Saya harap Anda tidak menemukan kesulitan. Tapi Saya yakin ada banyak sekali keadaan berbeda yang mungkin tidak sesuai dengan Panduan ini.

Jika Panduan ini belum menyelesaikan permasalahan Anda, silahkan sampaikan pada kolom komentar. Insya Allah kita cari solusi terbaik bersama-sama.

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: (Anda Disini).
  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: Cara menggunakan Rumus / Fungsi pada File Excel (WorkBook) berbeda.
  6. Hyperlinks: Cara menghubungkan (tersinkron) File Excel ke File lainnya atau ke Situs Website dll.
  7. Copy Exact Formula: Cara copy rumus / fungsi otomatis menyesuaikan referensi.
  8. Percent Change: Contoh menggunakan Nilai Absolut / Referensi Absolut / Symbol $ lengkap.

Komentar Paling Lambat di Balas Pukul 23:59.