Dalam dunia bisnis dan profesional, keahlian dalam menggunakan Microsoft Excel akan sangat berguna untuk meningkatkan efisiensi dalam mengolah data. Salah satu hal yang dapat dilakukan dalam Excel adalah mengambil data dari lembar kerja atau sheet yang berbeda. Pada artikel ini, kita akan membahas cara melakukan ambil data dari sheet lain di Excel dengan menggunakan rumus VLOOKUP dan INDIRECT.
Gambar dan Penjelasan
Untuk memulai, kita perlu mengetahui struktur dari rumus VLOOKUP dan INDIRECT. Rumus VLOOKUP dapat digunakan untuk mencari nilai dalam sebuah tabel dengan memiliki dua atau lebih kolom, di mana kolom pertama berisi nilai-nilai pencarian. Sedangkan rumus INDIRECT digunakan untuk mengacu pada sel atau range yang terletak di sheet yang berbeda.
Pertama-tama, mari kita lihat contoh penggunaan rumus VLOOKUP untuk mengambil data dari sheet yang berbeda. Contoh kasusnya adalah kita memiliki data penjualan barang dari beberapa cabang, dan kita ingin mengetahui total penjualan dari seluruh cabang. Kita memiliki dua lembar kerja dalam satu file Excel, di mana lembar kerja pertama berisi data penjualan dari cabang A dan B, sedangkan lembar kerja kedua berisi data penjualan dari cabang C dan D.
Pertanyaan 1: Bagaimana cara menggunakan rumus VLOOKUP dalam kasus ini?
Jawaban:
Pertama-tama, kita perlu mengambil data penjualan dari lembar kerja pertama dan kedua dengan menggunakan rumus VLOOKUP. Rumus VLOOKUP akan mencari nilai penjualan dari sel yang kita pilih di lembar kerja pertama, dan kemudian mencocokkan nilainya dengan sel yang memiliki kriteria yang sama di lembar kerja kedua. Untuk mengambil nilai penjualan dari sel A2 pada lembar kerja pertama, kita dapat menggunakan rumus berikut:
=VLOOKUP(A2,'Cabang C-D'!$A:$B,2,FALSE)
Di sini, ‘Cabang C-D’ adalah nama sheet yang berisi data penjualan dari cabang C dan D, dan $A:$B adalah range seluruh data pada sheet tersebut. Angka 2 menunjukkan bahwa kita ingin mengambil nilai dari kolom kedua pada range data tersebut, yaitu kolom penjualan. FALSE menunjukkan bahwa kita ingin mencocokkan nilai persis, sehingga hanya nilai yang sama secara tepat yang akan ditemukan.
Setelah kita mengambil nilai penjualan dari cabang C dan D, kita dapat menggunakan rumus SUM untuk menjumlahkan nilai tersebut dan mendapatkan total penjualan dari semua cabang. Kita dapat menaruh rumus berikut pada sel di lembar kerja manapun yang kita inginkan:
=SUM('Cabang A-B'!$B:$B,'Cabang C-D'!$B:$B)
Di sini, ‘$B:$B’ menunjukkan bahwa kita ingin menjumlahkan nilai yang terdapat pada kolom B pada range seluruh data pada sheet tersebut.
Selain menggunakan rumus VLOOKUP, kita juga dapat menggunakan rumus INDIRECT untuk mengambil data dari sheet yang berbeda. Contohnya, kita ingin mengekspor data dari beberapa lembar kerja pada satu file Excel ke dalam satu lembar kerja baru yang terpisah. Kita memiliki empat lembar kerja, di mana setiap lembar kerja berisi data keuangan bulanan untuk satu tahun penuh. Masing-masing lembar kerja diberi nama sesuai dengan tahun-nya, yaitu 2020, 2021, 2022, dan 2023. Kita ingin membuat sebuah lembar kerja baru yang berisi data keuangan dari empat tahun tersebut, dengan menggunakan rumus INDIRECT.
Pertanyaan 2: Bagaimana cara menggunakan rumus INDIRECT dalam kasus ini?
Jawaban:
Pertama-tama, kita perlu mengenal penggunaan tanda seru (!) dalam mengacu pada sheet yang berbeda. Tanda seru digunakan untuk memisahkan nama sheet dengan nama range sel, seperti pada contoh berikut:
=Sheet2!$A$1
Di sini, ‘$A$1’ adalah alamat sel pada Sheet2 yang ingin kita gunakan. Kita juga dapat mengganti nama sheet dengan menggunakan nama range name, seperti pada contoh berikut:
=Sales!$A$1
Di sini, ‘Sales’ adalah nama range name yang telah kita atur sebelumnya. Kita dapat mengatur range name dengan memilih sel atau range sel yang ingin kita gunakan, dan kemudian memilih Insert > Name > Define (atau dengan menekan Ctrl + Shift + F3). Di jendela dialog yang muncul, kita dapat menentukan nama untuk range name tersebut.
Kembali ke contoh kasus kita, kita ingin mengambil data keuangan dari empat lembar kerja, yaitu 2020, 2021, 2022, dan 2023, dan menampilkannya dalam satu lembar kerja baru. Kita membuat sebuah lembar kerja baru yang kosong, dan kemudian menuliskan nama-nama tahun dari lembar kerja yang ingin kita ambil datanya di sel-sel di baris pertama. Di bawah setiap nama tahun, kita akan menuliskan data keuangan bulanan yang ingin kita ambil dari setiap lembar kerja tahunan.
Untuk mengambil data keuangan dari lembar kerja 2020, kita dapat menuliskan rumus berikut pada sel yang ingin menampilkan data tersebut:
=INDIRECT("'2020'!"&CELL("address",B2))
Di sini, ‘2020’ adalah nama sheet yang berisi data keuangan bulanan untuk tahun 2020, dan B2 adalah sel yang berisi data keuangan bulanan untuk bulan Januari. Kita menggunakan fungsi CELL untuk mengambil alamat sel B2, dan kemudian menyatukan alamat tersebut dengan nama sheet menggunakan operator ‘&’ (ampersand). Setelah kita mengetik rumus di sel yang pertama, kita dapat menarik ke bawah untuk menyelesaikan data keuangan bulanan untuk tahun 2020.
Kita akan melakukan hal yang sama untuk tahun-tahun berikutnya, yaitu menuliskan rumus berikut pada sel-sel yang ingin menampilkan data tersebut:
=INDIRECT("'2021'!"&CELL("address",B2))
=INDIRECT("'2022'!"&CELL("address",B2))
=INDIRECT("'2023'!"&CELL("address",B2))
Setelah semua rumus dituliskan, kita akan mendapatkan sebuah lembar kerja yang berisi data keuangan bulanan dari empat tahun yang berbeda. Kita dapat menghitung total atau rata-rata dari data tersebut dengan menggunakan rumus-rumus Excel lainnya, seperti SUM dan AVERAGE.
FAQ
1. Apakah saya dapat menggunakan rumus VLOOKUP dan INDIRECT untuk mengambil data dari file Excel yang berbeda?
Jawaban: Ya, kita dapat menggunakan rumus VLOOKUP dan INDIRECT untuk mengambil data dari file Excel yang berbeda. Untuk menggunakan rumus VLOOKUP, kita perlu menambahkan referensi file Excel yang ingin kita gunakan. Contoh:
=VLOOKUP(A2,'[Data Penjualan.xlsx]Sheet2'!$A:$B,2,FALSE)
Di sini, ‘Data Penjualan.xlsx’ adalah nama file Excel yang ingin kita gunakan, dan ‘Sheet2’ adalah nama sheet pada file tersebut. Untuk menggunakan rumus INDIRECT, kita perlu menambahkan referensi file Excel dan nama sheet menggunakan tanda kutip ganda ganda dan tanda kurung siku. Contoh:
=INDIRECT("'[Data Penjualan.xlsx]Sheet2'!$A$1")
2. Dapatkah saya menggunakan rumus VLOOKUP untuk mencari nilai yang cocok dengan lebih dari satu kriteria?
Jawaban: Ya, kita dapat menggunakan rumus VLOOKUP untuk mencari nilai yang cocok dengan lebih dari satu kriteria dengan menggunakan rumus INDEX dan MATCH. Contoh:
=INDEX(range, MATCH(1, (criteria1=range1)*(criteria2=range2), 0))
Di sini, ‘range’ adalah range data di mana kita ingin mencari nilai, ‘criteria1’ dan ‘criteria2’ adalah kriteria yang ingin kita gunakan untuk mencocokkan nilai, dan ‘range1’ dan ‘range2’ adalah range di mana kriteria-kriteria tersebut terdapat. Kita perlu mengetikkan rumus dengan menekan Ctrl + Shift + Enter untuk membuatnya menjadi rumus array.
Video Tutorial: Cara Mengambil Data Dari Sheet Lain Di Excel.
Demikianlah cara mengambil data dari sheet lain di Excel dengan menggunakan rumus VLOOKUP dan INDIRECT. Dengan menguasai teknik ini, kita dapat meningkatkan efektivitas dan efisiensi dalam mengolah data di Excel. Jangan lupa juga untuk mencoba menggunakan fitur-fitur lain di Excel untuk mengoptimalkan pengelolaan data Anda.
