Kursus
Google Sheets Tingkat Menengah
4 jam
57.9K
Terkadang, data science bukan tentang deep learning, big data, atau sistem rekomendasi. Dalam beberapa kasus, khususnya jika Anda bekerja di sektor seperti keuangan atau di perusahaan yang selama ini sangat bergantung pada Excel, kemungkinan besar Anda akan sering berurusan dengan spreadsheet Excel. Saya akui, saya termasuk contoh yang terakhir. Perusahaan tempat saya bekerja sangat mengandalkan Excel dan, karena itu, saya harus mempelajari banyak kemampuan Excel untuk beberapa tanggung jawab pekerjaan saya.
Meskipun R dan Python punya keunggulan jelas dibandingkan Excel dalam banyak hal, Excel tetap merupakan alat yang kuat untuk tugas-tugas tertentu seperti membuat laporan bisnis. Namun, untuk tutorial ini, saya akan berfokus pada salah satu fungsi bawaan Excel yang sangat membantu saat bekerja dengan berbagai tabel Excel secara bersamaan.
Fungsi yang saya maksud adalah VLOOKUP. VLOOKUP memungkinkan pengguna mengaitkan kolom yang berbeda pada lembar yang sama atau antarlembardaftar kerja yang sama. Intinya, fungsi ini memungkinkan pengguna mencari nilai dari satu kolom dan mengembalikan nilai yang bersesuaian dari kolom lain. Fungsionalitas ini sangat berguna jika Anda memiliki tabel data besar yang tidak diurutkan dan Anda perlu mengaitkannya berdasarkan suatu kolom (idealnya berisi kunci unik).
Mari saya ilustrasikan bagaimana proses penggunaan fungsi VLOOKUP antar kolom pada lembar yang sama dengan sebuah tabel data sederhana:
Yang Anda lihat pada cuplikan layar di atas adalah ekspresi VLOOKUP umum yang ditulis sebagaimana biasanya untuk laporan bisnis. Komponennya sebagai berikut:
- Fungsi IFERROR: Digunakan untuk mengganti error dengan tanda hubung (-) jika kunci kosong atau tidak ada di tabel data. Ini adalah praktik yang baik untuk penanganan error agar lembar kerja tetap rapi saat terjadi error, tetapi tentu tidak diperlukan untuk membuat VLOOKUP berfungsi.
- Argumen VLOOKUP:
- E2 adalah nilai pencarian (dalam contoh ini kita menggunakan KOG, tetapi bisa berupa kunci apa pun). Nilai ini harus berada di kolom paling kiri dari rentang tabel yang Anda pilih untuk dicari.
- B1:C18 adalah rentang/array tabel tempat mencari nilai. Kolom paling kiri memiliki indeks 1 seperti yang ditunjukkan. Kolom ini juga harus berisi nilai pencarian agar fungsi bekerja.
- 2 adalah nomor indeks kolom dari rentang tabel yang dipilih tempat Anda ingin mengambil nilai. Pada contoh ini kita ingin mengambil deskripsi, jadi kita pilih kolom ke-2 dari array tabel.
- FALSE adalah penanda untuk menunjukkan bahwa Anda menginginkan kecocokan tepat dari nilai pencarian. Dalam hal ini, kita memilih FALSE karena Anda menginginkan kecocokan tepat untuk kunci KOG. Opsi lainnya adalah memilih TRUE, yang mencari kecocokan mendekati dari nilai pencarian dan menjadi nilai default jika Anda tidak menentukan argumen ini. Umumnya tidak disarankan memilih TRUE jika Anda mencocokkan kunci atau string karena dapat menghasilkan hasil yang aneh.
Sekarang, setelah menulis pernyataan VLOOKUP tersebut, jika Anda menekan ENTER di Excel, Anda akan mendapatkan hasil berikut:
Seperti yang Anda lihat, fungsi VLOOKUP mengembalikan hasil yang sesuai, yaitu "King of Gondor", yang merupakan deskripsi yang terkait dengan kunci KOG. Hal ini berlaku untuk kunci apa pun yang sekarang Anda masukkan di sel E2. Misalnya, mari kita lihat apa yang terjadi jika kita memasukkan kunci KIN.
The King in the North! Benar sekali, jika kita memasukkan kunci KIN, kita akan mendapatkan deskripsi tersebut untuk Jon(Snow) tercinta.
Namun, VLOOKUP punya lebih banyak kegunaan daripada sekadar mengambil satu nilai berdasarkan sebuah kunci. Misalkan, kita membagi tabel tersebut menjadi dua dan masing-masing ditempatkan pada lembar berbeda dalam buku kerja yang sama.
Menggabungkan Tabel
 |
 |
Seperti yang Anda lihat, kini data terbagi dalam dua lembar berbeda (S1 dan S2) dan kunci pada tabel di lembar kedua (S2) berada dalam urutan yang berbeda. Untungnya, kolom kunci sama di kedua lembar terlepas dari urutannya, sehingga kita dapat menggabungkan data kembali di S1 menggunakan VLOOKUP (Anda bisa menganggapnya sebagai left join satu kolom). Sintaks untuk mencapainya sebagai berikut:
Kali ini, argumen pertama, nilai pencarian, menjadi B2 karena kita ingin mencocokkan kunci B2 pada tabel di S2. Rentang tabel sekarang menjadi seluruh tabel data di S2 dengan kolom pertama berisi kunci umum. Anda mungkin memperhatikan bahwa rentang tabel memiliki tanda dolar. Ini dilakukan dengan menekan F4 setelah menentukan rentang, dan berfungsi untuk mengunci rentang sehingga saat Anda menyeret rumus ke bawah di S1, fungsi VLOOKUP akan tetap merujuk ke rentang asli tanpa ikut bergeser. Nomor indeks kolom tetap 2 karena kita menginginkan deskripsi, yaitu kolom kedua di S2. Terakhir, argumen terakhir yang kita gunakan adalah FALSE karena kita menginginkan kecocokan tepat untuk kunci.
Langkah terakhir adalah cukup menyeret rumus ke bawah hingga entri terakhir pada tabel. Tabel hasil di S1 adalah sebagai berikut:
Voilá! Hanya dengan itu, kita menyatukan kembali data menggunakan VLOOKUP. Menggabungkan tabel seperti ini sangat umum dilakukan saat bekerja di lingkungan bisnis dan SQL, R, atau Python bukanlah alat yang biasa digunakan oleh para eksekutif senior. Misalnya, Anda mungkin memiliki dua file yang berisi detail berbeda tentang peluang penjualan berkualifikasi yang bisa dikaitkan menggunakan ID peluang unik untuk laporan Excel penjualan bulanan (contoh terbaik penggunaan Excel dalam kasus saya).
Menggunakan Kecocokan Mendekati
Sejauh ini Anda mungkin memperhatikan bahwa kita tidak menggunakan TRUE sebagai argumen terakhir (alias kecocokan mendekati) pada fungsi VLOOKUP mana pun. Ini karena kita berfokus pada pencocokan tepat untuk kunci unik. Namun, Anda dapat menggunakan TRUE sebagai argumen terakhir di VLOOKUP dengan aman saat menangani data numerik.
Misalnya, anggaplah kita menjual ikan, dan kita menawarkan diskon berikut ketika pelanggan membeli dalam jumlah tertentu (Jika ada yang bertanya kenapa ikan, sederhana saja. Ikan itu keren!).
Fungsi VLOOKUP di dalam sel F2 kali ini adalah:
Setelah rumus tersebut diterapkan, diskon akan menjadi yang paling mendekati ambang pada tabel. Misalnya, jika kita membeli 25 ikan, diskon kita 5%, jika kita membeli 70 ikan, diskon kita 10%, dan jika kita membeli 120, maka diskon kita 15%. Semua contoh ini dapat dilihat di bawah:
 |
 |
 |
Pengantar INDEX-MATCH
VLOOKUP adalah fungsi yang kuat untuk mengaitkan kolom yang berbeda. Namun, ada cara alternatif untuk melakukannya; kombinasi INDEX-MATCH. Tidak seperti VLOOKUP yang merupakan satu fungsi, INDEX-MATCH adalah kombinasi dua fungsi, dan memiliki beberapa keunggulan dibanding VLOOKUP standar meskipun penulisannya sedikit lebih kompleks.
Untuk keperluan tutorial ini, kita akan membahas contoh sederhana di mana kita menggunakan INDEX-MATCH untuk menggabungkan dua tabel berdasarkan kunci umum seperti yang kita lakukan di atas dengan VLOOKUP dan menjelaskan sintaksnya (lihat di bawah).
Komponen kombinasi INDEX-MATCH adalah sebagai berikut:
- Argumen INDEX:
- M2:M18: Ini adalah rentang kolom yang nilainya ingin Anda ambil, dalam hal ini kita menginginkan kolom deskripsi, jadi kita pilih rentang tersebut. Perhatikan bahwa kita menekan F4 setelah memilih rentang untuk menguncinya agar tidak bergeser saat kita menyeret rumus.
- Argumen MATCH:
- B2: Ini adalah kunci yang ingin kita cari. Ini setara dengan nilai pencarian di VLOOKUP.
- L2:L18: Ini adalah rentang kolom yang berisi kunci tempat nilai pencarian harus ditemukan. Perhatikan bahwa di sini kita juga menggunakan F4.
- 0: Ini sama dengan FALSE di VLOOKUP. Ini adalah penanda untuk kecocokan tepat.
Melihat sintaksnya, kita bisa menyadari salah satu keunggulan utama INDEX-MATCH dibanding VLOOKUP. Dengan INDEX-MATCH kita memiliki imunitas kolom. Artinya, jika sekarang kita memutuskan untuk menambahkan kolom tambahan antara Key dan Description pada tabel kanan di atas, tidak masalah karena rumus akan menyesuaikan untuk tetap merujuk ke kolom Description. Anda bisa melihatnya di bawah:
Dengan VLOOKUP, hal ini tidak mungkin tanpa kembali mengubah rumus karena referensi kolomnya statis. Dengan kata lain, pada contoh di atas, awalnya Anda akan memasukkan angka 2 sebagai nomor indeks kolom untuk mengambil Description. Namun, setelah menambahkan kolom Fish, fungsi VLOOKUP sekarang akan mengembalikan nilai pada kolom Fish karena kolom tersebut kini menjadi kolom ke-2 dalam rentang tabel. Anda harus membuka fungsi VLOOKUP secara manual dan mengubah nomor indeks kolom menjadi 3 agar fungsi kembali bekerja dengan benar.
Dengan demikian, untuk menggabungkan deskripsi ke nama pada tabel di sebelah kiri menggunakan INDEX-MATCH prosedurnya sama seperti dengan VLOOKUP. Kita cukup menyeret rumus ke bawah seperti ditunjukkan berikut:
Perhatikan bahwa untuk menggabungkan tabel antarlembardaftar kerja yang sama, prosedurnya tetap sama. Satu-satunya perbedaan adalah Anda harus memilih rentang kolom yang berisi kunci umum serta rentang kolom yang nilainya ingin Anda ambil dari lembar kedua.
Kesimpulan
Semoga Anda menikmati tutorial ini. Fungsi-fungsi VLOOKUP dan INDEX-MATCH pada dasarnya esensial bagi siapa pun yang bekerja dengan Excel di lingkungan bisnis. Saya mendorong Anda untuk menggali lebih dalam INDEX-MATCH melampaui yang dibahas di sini karena fungsi ini memiliki banyak kemampuan menarik seperti pencocokan yang peka huruf besar-kecil (Ya, secara default INDEX-MATCH dan VLOOKUP tidak peka huruf besar-kecil) atau pencocokan berdasarkan beberapa kolom. Terus belajar; langit adalah batasnya!
Jika Anda ingin mempelajari lebih lanjut tentang VLOOKUP, ikuti kursus Data Analysis with Spreadsheets dari DataCamp.
