Menghubungkan Perhitungan Rumus Antar Sheet di Excel

19

Perhitungan rumus antar sheet di Excel dapat mempermudah dalam melakukan analisis data dalam jumlah besar. Baik rumus sederhana seperti penjumlahan antar sheet pada Excel, 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

Dimana A direferensikan dengan sheet2!A2 dan B direferensikan dengan sheet2!B2 lalu diperoleh formula berikut ini,

=Sheet2!A2+Sheet2!B2

Kemudian dilakukan autofill sampai A6 seperti ilustrasi di atas.


C. Contoh Perhitungan VLOOKUP Antar Sheet

Sebelumnya anda juga dapat membaca tutorial Pengertian 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
    Cara menggunakan VLOOKUP antar sheet di Excel
    Cara menggunakan VLOOKUP antar sheet di Excel

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

    =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 menjadi

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

Baca juga 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…

19 KOMENTAR

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

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

    • 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…

  2. 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…

  3. 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 🙂

    • Terima kasih sudah dijawab dengan baik.
      Untuk kasus yang saya contohnya di atas (sebenarnya saya sederhanakan dari kasus di project saya), sepertinya bisa menggunakan rumus IF.

      Namun untuk realnya, rasanya kurang praktis jika saya harus menggunakan rumus IF bertingkat, karena total sheet nya ada lebih dari 200 sheet,
      Dari lebih dari 200 sheet tsb, ingin dipilih data yang sesuai untuk ditampilkan di sheet rekap.
      Sedangkan data yang dibutuhkan untuk ditampilkan sekitar 600 data.

      Kira2 apakah masih ada cara selain menggunakan rumus IF ?

      Thanks

      Andre

    • 200 sheet itu untuk Microsoft Excel menurut saya sangat besar, dan ruwet untuk rumus IF
      1. Sebagai alternatif di tingkat menengah, mungkin bisa menggunakan Tabel Pivot antar sheet

      2. Jika kakak sudah memahami Excel macros dengan visual basic, mungkin akan menjadi alternatif lebih efisien. Namun untuk ini saya belum bisa memberikan contoh penggunaannya, karena saya pribadi belum mempelajarinya 🙂 karena menurut kawan saya yang sudah di tingkat lanjut, macros sangat membantu untuk mengolah data besar dengan logika yang lebih kompleks

      3. alternatif yang lebih baik untuk tingkat office, dapat menggunakan Microsoft Access
      4. Untuk developer, hal ini menjadi lebih baik jika menggunakan SQL

      Semoga dapat membantu kak 🙂

AYO BERKOMENTAR

Tulis komentar
Masukkan nama Anda