Menghubungkan Perhitungan Rumus Antar Sheet di Excel

23

Perhitungan rumus antar sheet di Excel dapat mempermudah dalam melakukan analisis data dalam jumlah besar. Baik rumus sederhana seperti penjumlahan antar sheet, maupun penggunaan di tingkat menengah seperti VLOOKUP antar sheet pada Excel. Sebelum menghubungkan formula antar sheet, anda juga dapat membaca mengenai tutorial alamat sel untuk mempermudah memahami tutorial berikut ini.

Baca juga: Alamat Sel pada Excel

A. Cara Mereferensikan Alamat Sel dari Sheet Lain

Menghubungkan formula dengan sheet lain pada dasarnya menggunakan sistematika alamat sel yang telah kita pelajari sebelumnya, hanya saja dengan penambahan argumen nama sheet di awal alamat sel dan dipisahkan dengan tanda '!'.

Mereferensikan sel dari sheet lain
=nama_sheet!alamat_sel
Mereferensikan range dari sheet lain yang telah diberi nama
=nama_sheet!nama_range
Mereferensikan range yang tidak diberi nama
=nama_sheet!alamat_sel:alamat_sel
  • nama_sheet merupakan nama dari sheet yang akan mereferensikan alamat sel yang dapat dilihat pada tab sheet Microsoft Excel.
  • alamat_sel yang digunakan sama dengan cara menggunakan alamat sel biasa baik alamat sel relatif, semi absolut maupun absolut.

B. Contoh Penjumlahan Antar Sheet pada Excel

Misalkan akan dibuat formula penjumlahan di sheet1 dimana nilai yang diambil berasal dari sheet2.

Ilustrasi Penjumlahan Antar Sheet Pada Excel
Ilustrasi Penjumlahan Antar Sheet Pada Excel

Sehingga formula yang dilakukan di sel A2 sheet1 adalah sebagai berikut.

=Sheet2!A2+Sheet2!B2

Dengan A direferensikan dengan sheet2!A2 dan B direferensikan dengan sheet2!B2.

Kemudian lakukan autofill sampai A6 seperti ilustrasi di atas.


C. Contoh Perhitungan VLOOKUP Antar Sheet

Sebelumnya anda dapat membaca tutorial Rumus Excel VLOOKUP dan HLOOKUP Beserta Contohnya, untuk lebih memahami contoh berikut.

Akan dicari predikat yang cocok untuk mahasiswa berikut saat akan diwisuda. Dimana data mahasiswa terdapat pada sheet dengan judul 'Mahasiswa'.

Vlookup antar sheet

Kemudian diketahui predikat mahasiswa adalah 0-1.99 (tidak lulus), 2-2.75 (Memuaskan), 2.76-3.50 (Sangat Memuaskan), 3.51-3.99 (Dengan Pujian), 4.00 (Sempurna). Sehingga tabel lookup IPK dapat dibuat pada sheet 'Predikat' pada tabel berikut,

Predikat kelulusan antar sheet

Berikut langkah-langkah yang dilakukan.

PENTING: Penggunaan koma (,) atau semicolon (;) dalam FORMULA disesuaikan dengan versi Microsoft Excel dan konfigurasi bahasa pada komputer anda.

  1. Memberi Nama Range pada Tabel Lookup pada Sheet 'Predikat'

    Misalkan name range A3:B7 adalah 'IPK'

    Membuat name range antar sheet di Excel

  2. Simpan Workbook yang Dibuat 'CTRL+S'

    Misalkan dengan nama 'Mahasiswa' dan klik Save seperti gambar berikut.

    Menyimpan workbook antar sheet Excel

  3. Membuat Formula VLOOKUP dengan Nama Range pada Sheet

    Sehingga dapat digunakan table_array dari formula VLOOKUP adalah referensi range 'IPK' pada sheet Predikat yaitu Predikat!IPK

    Cara menggunakan VLOOKUP antar sheet di Excel
    Contoh cara menggunakan VLOOKUP antar sheet di Excel
    =VLOOKUP(B2,Predikat!IPK,2,TRUE)
  4. Melakukan Autofill pada Sheet 'Mahasiswa'

Catatan: Jika saat anda memasukkan formula keluar notifikasi untuk memilih file, maka pilih nama file yang anda simpan pada langkah ke-2. Begitu juga saat melakukan autofill. Setelah selesai, formula akan berubah otomatis menjadi seperti berikut.

=VLOOKUP(B2,Mahasiswa.xlsx!IPK,2,TRUE)

Tutorial lainnya: Daftar Isi Tutorial Excel


Sekian artikel Menghubungkan Perhitungan Rumus Antar Sheet di Excel. Nantikan artikel menarik lainnya dan mohon kesediaannya untuk share dan juga menyukai Fans Page Advernesia. Terima kasih…

23 KOMENTAR

  1. Sama-sama mas...
    Mohon untuk melengkapi form identitas sebelum berkomentar mas...
    Silakan melihat-lihat tutorial lainnya di Advernesia

    • yang ninggalin link aktif/link mati di hapus aja kak, membahayakan lamanmu dimata google

    • iya kidu... lumayan juga link aktif/mati yang terus muncul...
      Terutama komentar spam...

  2. Advertise, minta tolong donk, cara buat kolom yang dijadikan pitcure dengan $, contoh =$P$100:$U$106

    • Terima kasih atas komentarnya mas Peni...
      Untuk menambah picture pada sel atau range... bisa menggunakan fitur format picture...
      Saya akan sangat senang membuat tutorial ini kedepannya...

  3. TERIMA KASIH BANYAK INFORMASI RUMUS - RUMUS EXCELNYA MUDAH DIPAHAMI. GOOD JOB....FORMAT PENJELASANNYA MENARIK...SEMOGA BISA BELAJAR LAGI DARI WEB INI ADVERNESIA KEREN...

    • Terima kasih kembali kak Tika, senang rasanya artikel di situs ini dapat memberikan manfaat bagi pembacanya...

    • Terima kasih kembali dik Adam...
      Adik bias mempelajari Excel lebih mudah melalui Daftar Isi di situs ini...

    • Terima kasih kembali kak Ircham
      Semoga membantu meningkatkan performa pekerjaan kakak 🙂

  4. Dear,
    Ada ga formula untuk memilih & mengambil sebuah nilai data dari beberapa sheet excel,
    dimana nantinya sheet yang diambil datanya itu, harus berasal dari sheet dengan nama sheet yang sama persis dengan nama data yang sedang dicari.
    Misal :
    Ada 3 sheet data (A,B,C) , 1 sheet rekap
    Sheet 1 (nama sheet = A). Data cell di A1 = Apel
    Sheet 2 (nama sheet = B). Data cell di A1 = Jeruk
    Sheet 3 (nama sheet = C). Data cell di A1 = Mangga
    Di sheet rekap, ingin ditampilkan ketika ditulis B (refers to nama sheet), di baris selanjutnya langsung akan mencari sheet B cell A1, yaitu Jeruk
    Sama halnya saat ditulis C, maka secara otomatis di baris selanjutnya adalah Mangga.
    Intinya, formula agar muncul jawaban Jeruk ketika ditulis B, Apel ketika ditulis A, Mangga ketika ditulis C.

    Thanks
    Andre

    • Dear,

      Kak Andre dapat menggunakan rumus IF bertingkat dengan referensi output antar sheet
      Misal, pada sheet rekap dengan data input A1 dan data output dengan mengambil nilai dari sel A1 sheet referensi
      =IF(A1="A",Sheet1!A1,IF(A1="B",Sheet2!A1,IF(A1="C",Sheet3!A1,"Input Data")))
      Formula dapat menyelesaikannya secara otomatis bergantung sheet referensi jika ditarik kebawah (autofill)

      Untuk penyelesaian yang lebih kompleks, misalnya dengan 2 input data kasus penilaian, Misal A1=nama matakuliah, b1=NIM, dengan ouput C1= nilai, dapat menggunakan formula If bertingkat dengan gabungan VLOOKUP antar sheet

      Semoga bermanfaat 🙂

  5. Kak bagaimana cara penjumlahan harga
    contoh ya : Sheet 1 itu "Order" dan sheet 2 "Payment"
    disheet 1 kolom D4 ,E4,F4,G4 = nilai quantitynya peritemnya dan saya mau memasukan (auto fill) di sheet 2 Kolom E4 total jumlahnya setelah dikalikan harga, bagaimana crumusnya, atau ada wa yg bisa dihubungi?

    • Hai Kakak Kiki, untuk itu dapat menerapkan cara tutorial diatas
      Kontak saya ada di halaman Contact Us, bisa dijelaskan lebih detail nama-nama kolom tersebut di sini?

    • Hai Kak Lutfi, kira-kira kendalanya di bagian mana?
      Bisa disampaikan di sini kak 🙂

    • Hai Kakak Meynando, menurut saya caranya sama, tinggal referensikan menggunakan rumus antar sheet...
      Semoga membantu 🙂

AYO BERKOMENTAR

Tulis komentar
Masukkan nama Anda