Menghubungkan Perhitungan Rumus Antar Sheet di Excel

Penulis
Advernesia
54
Menghubungkan Perhitungan Rumus Antar Sheet di Excel
Menghubungkan Perhitungan Rumus Antar Sheet di Excel

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…

ARTIKEL TERKAITARTIKEL TERBARU

54 DISKUSI PEMBACA

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

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

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

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

  9. 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 Kakak Meynando, menurut saya caranya sama, tinggal referensikan menggunakan rumus antar sheet...
      Semoga membantu 🙂

  12. Saya masih belum bisa mempraktekannya ya, padahal udah sesuai instruksinya. huhu salah dilangkah mana ya, jadi bingung

  13. mau minta solusinya dong x aja ada yg bsa kasih sarannya,kalau maw menggabungkan 4 sheet atau lebih di jadikan satu dgn nama item yg urutannya berbeda beda dan qty nya pun berbeda lebih baik pakai rumusnya apa iia dan gmn proses nya ?

    • Hai, Kak Yhamin
      Saran saya bisa menggabungkan sheet berdasarkan kolom saja, misal dari kolom A-E isinya sheet 1, F-J isinya sheet 2, begitu seterusnya
      Kemudian untuk melihatnya secara lebih mudah bisa ditambahkan garis pembatas menggunakan border antar kolom sheet yang telah digabungkan.
      Untuk formula yang tepat bisa menggunakan formula biasa sesuai dengan kebutuhan sheet kakak.
      Semoga membantu 🙂

  14. kalau kita punya data atau sejumlah deret angka, bagaimanakah cara mendapatkan rumusnya? agar kita bisa mendapatkan deret angka berikutnya? misal deret angka 12, 34, 23, 65, 43, A, dst, untuk mendapatkan nilai A, tentu kita harus dapat rumusnya dulu, nah adakah caranya agar bisa memperoleh rumus itu secara otomatis dengan menggunakan excel ini?

  16. Hai kak.
    bagaimana ya cara mengambil data di dalam 30 sheet untuk ditampilkan di sheet k 31dengan cepat pengambilan datanya?
    terimksh kak

    • Hai, Kak Agus
      Kakak dapat menggunakan fitur auto fill pada referensi antar sheet untuk mengambil data dan formulanya
      Jika kakak hanya mengambil nilai datanya saja, dapat menggunakan fitur Advanced Filter
      Semoga membantu 🙂

    • Bisa kasih contoh kak.
      misal nih saya ambil data di sheet 1 - 30.
      lalu ingin sy tampilkankan dlm satu sheet misal di sheet 31. Gitu bgmn kak rumusnya.
      kalau ambil data kan ='sheet1'!A1 misal.
      cara mengubah sheet 1 biar cepat sampai sheet 30 itu ka bgmn?

    • Hai, Kak Agus gunakan Formula INDIRECT
      Misalnya 

          |A      |B      |C      |D      |E
----|-------|-------|-------|-------|---
1   |Sheet1 |Sheet2 |Sheet3 |Sheet4 |
----|-------|-------|-------|-------|---
2   |=INDIRECT(A1 & "!A1")          |

      Itu mengambil data A1 dari Sheet1, Sheet2, dst, bisa pakai Autofill untuk menulisnya
      Tulis nama sheet di baris 1, referensikan ke baris 2 pada formula INDIRECT
      =INDIRECT(A1 & "!A1") lalu autofill ke kanan, maka formula akan otomatis diisi
      Semoga membantu 🙂

    • Mohon maaf bukan itu mksdnya kak.🙏
      kita kan punya worksheet, (30 worksheet) dalam 1 file excel atau dalam 1 workbook . Lah bgmn caranya kak menggambil data dalam 30 worksheet trsbt utk ditaruH dalam worksheet ke 31 itu, secara berurutan dengan cepat kak

    • Hai kak Agus misalnya sesuai pertanyaan sebelumnya

      kalau ambil data kan ='sheet1'!A1 misal.
cara mengubah sheet 1 biar cepat sampai sheet 30 itu ka bgmn?

      Menurut saya itu bisa pakai INDIRECT, bisa diubah langsung di sel nama sheet, supaya tanpa mengetik bisa ditambahkan data validation di sel nama sheet.
      Kemudian supaya terurut bisa digunakan A1 A2 A3 dst
      =INDIRECT($A$1 & "!A" & ROW())
      Semoga membantu 🙂

  17. Assalamualaikum...
    Mohon pencerahannya donk, gimana cara membuat dua sheet yang isinya kebalikan, sheet pertama pada setiap sel ada angkanya ketika dihapus di sheet kedua muncul angka yang dihapus di sheet pertama Contoh
    Sheet 1 Sheet 2
    A B C D A B C D
    10 11 12 13 (dihapus) 10 11 12 13 (muncul)

    • Wa'alaikumusalam Kak Zaenal Muttaqin
      Kasus tersebut terkait dengan I/O software
      Mungkin kakak bisa membuatnya dengan kode Macro VBA
      Semoga membantu 🙂

  18. mau tamya kak...saya mau nambah rows di sheet 1 kan sdh ada daftar nama misalnya ada 13 daftar nama terus saya mau nambah 5 lagi sedangkan sheet 2 3456789 dst itu terkait sama sheet 1 ..klw sheet 1 di tambah daftar nama maka sheet lainnya otomatis nama nya nambah...tapi ini kok gk bisa ya saya udah tambah sheet 1 daftar nama tapi sheet lainnya gk nambah kak terus bagaimana ini?

  19. Hello ka, mau tanya
    Saya punya tabel, tabelnya ini ada tiga kolom, dari tanggal 1 smapi 30
    Nah, kalau sy mau mengubah rumus, biar gak ngubah satu2 itu gmna caranya ka soalnnya ada rumus matwmatiknya juga

    Terima kasih

    • Hai, Kak Nana itu dapat dilakukan dengan autofill
      Ubah rumusnya satu di kolom awal, dan juga atur pengalamatan sel apakah absolut, semi-absolut, atau relatif
      Semoga membantu 🙂

  20. Sy punya tabel pembayaran angsuran kridit terdiri no, no. Pelangan, tgl angsuran, nm pelangan, jlh angsuran, cara pembayaran, ket dan di sheet sy beri nama angsuran terdiri dr bulan ke, amgsuran, total cicilan, tgl tenpo, tgl angsuran dan paraf

    Sy mau tanya bgmn cara memangil tgl angauran di tabel pembayaran di letakan di tabel angsuran

1 2

Ayo Berdiskusi Bersama! Belajar Lebih Indah dengan Saling Membantu :)

Tulis komentar
Masukkan nama Anda