Excel merupakan salah satu aplikasi pengolah data yang paling populer di dunia bisnis. Namun, terkadang kita membutuhkan data tertentu dari sebuah tabel atau sheet yang cukup besar dan sulit untuk diambil secara manual.
Rumus Excel Mengambil Data Dari Tabel
Saat bekerja dengan Excel, seringkali kita perlu mengambil data tertentu dari sebuah tabel yang lebih besar. Hal ini dapat dilakukan dengan menggunakan rumus-rumus tertentu pada Excel. Berikut adalah beberapa rumus Excel yang dapat digunakan untuk mengambil data dari tabel:
VLOOKUP
VLOOKUP (Vertical Look Up) merupakan rumus Excel yang paling sering digunakan untuk mengambil data dari tabel. Rumus ini bekerja dengan cara mencocokkan nilai tertentu pada suatu kolom, kemudian mengambil nilai yang ada pada kolom lain di baris yang sama.
Contohnya, kita memiliki data pada tabel seperti di bawah ini:
| Nama | Umur | Alamat |
|---|---|---|
| Andi | 25 | Jakarta |
| Budi | 30 | Bandung |
| Cindy | 27 | Surabaya |
Jika kita ingin mengambil alamat dari seseorang dengan nama “Budi”, kita dapat menggunakan rumus VLOOKUP sebagai berikut:
=VLOOKUP("Budi", A2:C4, 3, FALSE)
Penjelasan:
- “Budi” adalah nilai yang ingin kita cari
- A2:C4 adalah range data di mana kita ingin mencari nilai “Budi”
- 3 menunjukkan bahwa nilai yang ingin kita hasilkan berada pada kolom ketiga (kolom “Alamat”)
- FALSE menunjukkan bahwa kita ingin mencocokkan nilai secara eksak (tidak memperbolehkan kesalahan pengejaan atau karakteristik lainnya)
Hasilnya akan menjadi:
“Bandung”
INDEX dan MATCH
Rumus INDEX dan MATCH dapat digunakan untuk mengambil data dari tabel dengan cara yang lebih fleksibel. Kita dapat menggunakan kombinasi kedua rumus ini untuk mencari nilai pada sebuah baris atau kolom tertentu dalam tabel.
Dalam kombinasi INDEX dan MATCH, rumus INDEX digunakan untuk mengambil nilai dari array data, sementara rumus MATCH digunakan untuk mencari posisi baris atau kolom yang sesuai dengan kriteria yang diberikan.
Contohnya, kita memiliki data pada tabel seperti di bawah ini:
| Nama | Umur | Alamat |
|---|---|---|
| Andi | 25 | Jakarta |
| Budi | 30 | Bandung |
| Cindy | 27 | Surabaya |
Jika kita ingin mengambil alamat dari seseorang dengan nama “Budi”, kita dapat menggunakan kombinasi rumus INDEX dan MATCH sebagai berikut:
=INDEX(C2:C4, MATCH("Budi", A2:A4, 0))
Penjelasan:
- C2:C4 adalah range data di mana kita ingin mencari alamat Budi
- “Budi” adalah nilai yang ingin kita cari pada kolom nama (range A2:A4)
- 0 menunjukkan bahwa kita ingin mencocokkan nilai secara eksak (tidak memperbolehkan kesalahan pengejaan atau karakteristik lainnya)
Hasilnya akan menjadi:
“Bandung”
Cara Mengambil Data Dari Sheet Lain di Excel
Selain mengambil data dari tabel di sheet yang sama, kita juga dapat mengambil data dari sheet yang berbeda pada file Excel yang sama. Berikut adalah langkah-langkah yang dapat dilakukan:
- Pilih sel pada sheet tempat di mana Anda ingin menampilkan data yang diambil dari sheet lain.
- Ketikkan tanda sama dengan (=).
- Pergi ke sheet lain yang berisi data yang ingin diambil.
- Pilih sel yang berisi data tersebut.
- Tekan Enter.
Contohnya, kita memiliki dua sheet dalam satu file Excel: Sheet1 dan Sheet2. Di Sheet1, kita ingin menampilkan data dari sel B2 di Sheet2. Berikut adalah langkah-langkah yang dapat dilakukan:
- Pergi ke Sheet1.
- Pilih sel tempat di mana Anda ingin menampilkan data dari Sheet2 (misalnya C3).
- Ketikkan tanda sama dengan (=).
- Pergi ke Sheet2.
- Pilih sel yang berisi data yang ingin diambil (B2).
- Tekan Enter.
Hasilnya akan menjadi:
Data yang ada pada sel B2 di Sheet2 akan ditampilkan pada sel C3 di Sheet1.
Rumus Excel Mengambil 4 Angka Terakhir
Saat bekerja dengan Excel, seringkali kita perlu mengambil sebagian nilai dari sebuah sel. Misalnya, jika kita memiliki nomor telepon berikut: “081234567890”, kita mungkin ingin mengambil empat digit terakhir saja (misalnya “7890”).
Agar dapat mengambil angka terakhir dari sebuah sel, kita dapat menggunakan kombinasi rumus RIGHT dan LEN sebagai berikut:
=RIGHT(A2, 4)
Penjelasan:
- A2 adalah sel di mana nilai akan diambil
- 4 menunjukkan bahwa kita ingin mengambil empat karakter dari sebelah kanan
Cara Mengambil Data Berdasarkan Nilai atau Record yang Sama di Excel
Terkadang kita perlu mengambil data tertentu dari suatu tabel berdasarkan nilai tertentu yang kita tentukan. Misalnya, kita memiliki tabel yang berisi data siswa, dan kita ingin mengambil nilai matematika dari siswa dengan nama “Andi”.
Untuk melakukan hal ini, kita dapat menggunakan rumus-rumus seperti VLOOKUP atau INDEX-MATCH yang telah dijelaskan di atas. Namun, ada juga cara lain yang dapat digunakan untuk mengambil data berdasarkan nilai atau record yang sama di Excel.
Caranya adalah dengan menggunakan rumus SUMIF atau AVERAGEIF.
SUMIF
Rumus SUMIF dapat digunakan untuk menjumlahkan nilai dalam range data tertentu berdasarkan kriteria yang diberikan. Caranya adalah dengan menggunakan sintaks berikut:
=SUMIF(range, criteria, sum_range)
Penjelasan:
- range: adalah range data di mana kita ingin mencari nilai
- criteria: adalah kriteria yang ingin kita cari pada range data
- sum_range: adalah range data yang akan dijumlahkan jika kriteria terpenuhi
Contohnya, kita memiliki data pada tabel seperti di bawah ini:
| Nama | Matematika | Bahasa Inggris | Sejarah |
|---|---|---|---|
| Andi | 90 | 80 | 70 |
| Budi | 80 | 85 | 75 |
| Cindy | 85 | 70 | 90 |
Jika kita ingin menjumlahkan nilai matematika dari siswa dengan nama “Andi”, kita dapat menggunakan rumus SUMIF sebagai berikut:
=SUMIF(A2:A4, "Andi", B2:B4)
Penjelasan:
- A2:A4 adalah range data di mana kita ingin mencari nama “Andi”
- “Andi” adalah nilai yang ingin kita cari pada range data (kolom “Nama”)
- B2:B4 adalah range data yang akan dijumlahkan jika kriteria (nama “Andi”) terpenuhi (kolom “Matematika”)
Hasilnya akan menjadi:
90
AVERAGEIF
Rumus AVERAGEIF dapat digunakan untuk menghitung rata-rata dari nilai dalam range data tertentu berdasarkan kriteria yang diberikan. Caranya mirip dengan rumus SUMIF, namun menggunakan fungsi AVERAGE sebagai ganti SUM.
=AVERAGEIF(range, criteria, average_range)
Penjelasan:
- range: adalah range data di mana kita ingin mencari nilai
- criteria: adalah kriteria yang ingin kita cari pada range data
- average_range: adalah range data yang akan dihitung rata-ratanya jika kriteria terpenuhi
Kembali ke contoh sebelumnya, jika kita ingin menghitung rata-rata nilai matematika dari siswa dengan nama “Andi”, kita dapat menggunakan rumus AVERAGEIF sebagai berikut:
=AVERAGEIF(A2:A4, "Andi", B2:B4)
Penjelasan:
- A2:A4 adalah range data di mana kita ingin mencari nama “Andi”
- “Andi” adalah nilai yang ingin kita cari pada range data (kolom “Nama”)
- B2:B4 adalah range data yang akan dihitung rata-ratanya jika kriteria (nama “Andi”) terpenuhi (kolom “Matematika”)
Hasilnya akan menjadi:
90
FAQ
1. Apa perbedaan antara VLOOKUP dan INDEX-MATCH?
VLOOKUP dan INDEX-MATCH adalah dua rumus Excel yang umum digunakan untuk mengambil data dari tabel. Perbedaan utama antara keduanya adalah cara mereka bekerja.
VLOOKUP bekerja dengan cara mencocokkan nilai pada kolom tertentu, kemudian mengambil nilai dari kolom lain pada baris yang sama. Rumus ini mudah digunakan, namun terbatas pada satu kolom saja.
Sementara itu, INDEX-MATCH bekerja dengan cara mencari posisi baris atau kolom tertentu berdasarkan kriteria yang diberikan, kemudian mengambil nilai dari sel di posisi tersebut. Rumus ini lebih fleksibel dan dapat digunakan untuk mengambil nilai dari beberapa kolom sekaligus.
2. Apa saja rumus yang dapat digunakan untuk mengambil data dari sheet lain di Excel?
Beberapa rumus Excel yang dapat digunakan untuk mengambil data dari sheet lain di Excel adalah:
- =Sheet2!A1, untuk mengambil nilai dari sel A1 di Sheet2
- =SUM(Sheet2!A1:A5), untuk menjumlahkan nilai di antara sel A1 dan A5 di Sheet2
- =COUNT(Sheet2!A1:A10), untuk menghitung jumlah sel yang memiliki nilai di antara sel A1 dan A10 di Sheet2
Video Tutorial
Berikut adalah video tutorial mengenai cara mengambil data dari tabel dan sheet lain di Excel:
