Bagaimana cara menarik data dari spreadsheet google lain berdasarkan kriteria?

Saat Anda harus merujuk ke data dari beberapa lembar atau tabel, mencarinya secara fisik bisa sangat melelahkan. Google Sheets membantu mengatasi masalah ini dengan fungsi VLOOKUP-nya

Dengan menggunakan VLOOKUP, atau fungsi Pencarian Vertikal, Anda dapat menggunakan Google Spreadsheet untuk mencari nilai secara otomatis dan mengambil data yang cocok dari tabel lain. Tabel ini bisa berada di lembar yang sama atau lembar yang berbeda

Satu-satunya masalah adalah sintaks fungsi VLOOKUP hanya memungkinkan Anda mencari satu kolom dalam satu waktu

Jadi, jika Anda memiliki banyak kriteria atau ingin memeriksa beberapa kolom yang saling terkait pada saat yang sama, Anda harus menemukan jalan keluar dari batasan ini

Ini dapat dilakukan dengan memasangkan fungsi VLOOKUP dengan fungsi Google Sheets lainnya, bahkan fungsi IMPORTRANGE

Daftar isi

Sintaks Fungsi VLOOKUP Reguler

Secara umum, fungsi VLOOKUP memiliki sintaks berikut

=VLOOKUP(search_key, range, index, [is_sorted])

Di Sini,

• search_key adalah nilai kunci yang ingin Anda cari dari tabel lain. Ini bisa berupa nilai atau referensi ke sel yang berisi nilai
• rentang adalah rentang sel (dalam tabel sumber) di mana Anda ingin fungsi VLOOKUP mencari kunci_pencarian. Ingatlah untuk memastikan bahwa rentang ini berisi kolom dengan search_key sebagai kolom pertamanya. Juga, pastikan itu berisi kolom dengan nilai target Anda (yang ingin Anda ambil)
• indeks adalah nomor kolom dalam rentang yang berisi nilai target yang ingin Anda ambil. Perhatikan bahwa kolom pertama dalam rentang memiliki indeks 1, kolom kedua memiliki indeks 2, dan seterusnya
• is_sorted adalah parameter opsional. Ini bisa BENAR atau SALAH. Ini menunjukkan apakah kolom pencarian perlu diurutkan atau tidak

Misalnya, pada gambar berikut, kita memiliki dua tabel. Satu berisi informasi pribadi tentang karyawan dan satu lagi berisi total penjualan mereka pada bulan tertentu

Kedua tabel memiliki kolom umum, ID Karyawan, jadi ini dapat dianggap sebagai nilai kunci atau pengidentifikasi unik di kedua tabel

Untuk menampilkan tarif per jam, katakanlah, ID Karyawan "E010", Anda perlu mengambilnya dari tabel Karyawan menggunakan fungsi VLOOKUP. Inilah yang akan Anda masukkan di sel F3

=VLOOKUP(A3,$A$3:$C$8,3,false)

Sehingga didapatkan hasil sebagai berikut

Kapan kita perlu VLOOKUP Banyak Kriteria di Google Sheets?

Kasus di atas hanya melibatkan pencarian satu kriteria, kecocokan untuk ID Karyawan. Namun, seringkali kriterianya lebih kompleks dari itu

Mungkin ada sejumlah alasan untuk berbagai kriteria VLOOKUP. Berikut beberapa contohnya

1. Anda mungkin memiliki kolom terpisah untuk nama depan dan belakang di tabel sumber. Jadi, Anda perlu mencari kedua kolom untuk mengambil nilai yang sesuai, seperti yang ditunjukkan di bawah ini
   
2. Anda mungkin perlu memeriksa kepuasan 2 kondisi atau lebih untuk mengambil nilai. Misalnya, Anda mungkin memerlukan jumlah skor hanya dari siswa yang lulus ujian dan belajar bahasa Prancis, seperti yang ditunjukkan di bawah ini
   
3. Anda mungkin perlu mencari tabel terpisah untuk menemukan Gaji seorang karyawan dari departemen tertentu dalam kode Area tertentu
   

Ada banyak situasi lain seperti yang tercantum di atas. Tapi Anda mendapatkan intinya

Anda mungkin berpendapat bahwa menggunakan FILTER akan menjadi pilihan yang lebih mudah untuk situasi di atas. Namun, FILTER tidak dapat mengambil data dari lembar yang berbeda

Menggunakan fungsi IF juga akan berlebihan karena Anda kemudian harus berurusan dengan banyak IF bersarang (dan tidak ada yang suka itu. )

Cara VLOOKUP Banyak Kriteria di Google Sheets

Mari kita ambil contoh berikut, di mana Anda memiliki satu tabel yang berisi Bonus yang sesuai dengan kode masing-masing Departemen dan Area

Pada tabel 2, kita perlu mencari Bonus yang sesuai dengan Departemen tertentu dalam kode Area tertentu dan menampilkan nilai yang diambil di kolom Bonus (kolom E)

Ada dua cara untuk melakukannya menggunakan Fungsi VLOOKUP

• Menggunakan Kolom Pembantu
• Menggunakan Fungsi ARRAYFORMULA

Baca lebih banyak. PENCARIAN Google Spreadsheet

Menggunakan Kolom Pembantu untuk VLOOKUP Beberapa Kriteria di Google Sheets

Metode pertama melibatkan penggunaan kolom "Pembantu" tambahan, yang akan berisi kombinasi sel dalam kriteria

Dalam contoh kita, kita dapat menyisipkan kolom Pembantu tepat sebelum kolom Departemen, sehingga dapat menjadi kolom pertama dari rentang pencarian.

Kolom Pembantu dapat berisi kombinasi kode Departemen dan Area untuk setiap baris, dipisahkan dengan spasi

Mari kita lihat langkah-langkah membuat dan menggunakan kolom pembantu ini dengan VLOOKUP untuk mendapatkan nilai Bonus yang sesuai untuk Tabel 2

1. Sisipkan kolom baru tepat sebelum kolom pertama Tabel 1. Ini akan bertindak sebagai kolom Pembantu kami. Untuk ini, Anda perlu mengklik kanan tajuk kolom dari kolom pertama (kolom A) dan memilih 'Sisipkan 1 kiri' dari menu konteks yang muncul
   
2. Pilih sel pertama dari kolom yang baru dibuat (sel A4) dan ketik rumusnya. =B4&” “&C4
3. Tekan tombol Kembali. Sel A4 sekarang harus berisi konten sel B4 dan C5 bersama-sama, hanya dipisahkan oleh spasi
   
4. Klik dua kali pada gagang isian sel A4 untuk menyalin rumus ke seluruh sel di kolom A. Inilah tampilan kolom A pada titik ini
   
5. Sekarang kolom pembantu sudah siap, kita bisa melanjutkan dan menggunakan fungsi VLOOKUP. Anda akan melihat bahwa penambahan kolom baru telah menggeser isi tabel 2 satu sel ke kanan. Jika mau, Anda dapat memilih konten dan memindahkannya kembali ke satu sel di sebelah kiri
   
6. Selanjutnya, pilih sel E17 (Bonus Column of Table 2) dan ketik rumusnya. =VLOOKUP(B17&” “&C17,$A$4. $D$12,4, salah)
7. Tekan tombol kembali
8. Anda sekarang akan melihat nilai bonus yang sesuai dengan “Operasi BH12” dari Tabel 1
   
9. Klik dua kali pada gagang isian sel E17 untuk menyalin rumus ke seluruh sel di kolom A
10. Anda sekarang akan melihat semua nilai bonus yang sesuai dengan setiap Departemen dan Kode Area di Tabel 2
    

Catatan. Saat mengetikkan rumus VLOOKUP, jangan lupa untuk mengunci referensi pada parameter kedua dengan menekan tombol F4. Ini akan memastikan bahwa rentang pencarian tidak bergeser saat rumus disalin ke sel lainnya

Penjelasan Formula

Mari uraikan rumus VLOOKUP di bawah ini untuk memahami setiap parameter yang digunakan

=VLOOKUP(B17&" "&C17,$A$4:$D$12,4, false)

• search_key. Kunci pencarian yang kami gunakan di sini adalah kombinasi nilai kode Departemen dan Area yang ingin kami cari, dipisahkan oleh spasi (yang persis seperti format yang kami gunakan di kolom Pembantu kami)
• jangkauan. Kami tahu bahwa rentang pencarian kami harus selalu memiliki kolom pencarian sebagai kolom pertamanya. Rentang pencarian sekarang dimulai dari A4 hingga D12, karena A4 adalah sel pertama kolom pembantu kami
• indeks. Karena penambahan kolom tambahan di sebelah kiri, kolom target kami kini telah bergeser satu sel ke kanan. Oleh karena itu, kolom Bonus sekarang berada di indeks 4 dari rentang pencarian kami
• is_sorted. Nilai FALSE untuk parameter ini menunjukkan bahwa kolom pertama rentang pencarian tidak perlu diurutkan dalam urutan menaik

Karena kolom search_key dan Helper memiliki format yang persis sama (memiliki Departemen diikuti dengan spasi, diikuti dengan kode Area), fungsi VLOOKUP dapat dengan mudah mencari Bonus yang sesuai dan mengembalikannya

Menggunakan Fungsi ARRAYFORMULA untuk VLOOKUP Beberapa Kriteria di Google Sheets

Cara ini kurang lebih sama dengan cara pertama. Satu-satunya perbedaan adalah kali ini kolom pembantu dibuat secara dinamis, daripada harus membuat kolom tambahan secara fisik

Metode ini menggunakan fungsi ARRAYFORMULA untuk membuat tabel 'virtual' yang berisi kolom-kolom berikut

• Kolom yang menyimpan kombinasi sel dalam kriteria
• Kolom target dari rentang pencarian

Mari kita terapkan ini pada kasus kita. Berikut langkah-langkah membuat dan menggunakan fungsi ARRAYFORMULA dengan VLOOKUP untuk mendapatkan nilai Bonus yang sesuai untuk Tabel 2

1. Pilih sel E17 (Kolom bonus Tabel 2) dan ketik rumusnya.

    =ARRAYFORMULA(VLOOKUP(B17&" "&C17,{$A$4:$A$12&" "&$B$4:$B$12,$C$4:$C$12},2,false))

2. Tekan tombol kembali
3. Anda sekarang akan melihat nilai Bonus yang sesuai dengan Departemen. Operasi dan Kode Area. BH12 dari Tabel 1
   
4. Klik dua kali pada gagang isian sel E17 untuk menyalin rumus ke seluruh sel di kolom A
5. Anda sekarang akan melihat semua nilai Bonus yang sesuai dengan setiap Departemen dan Kode Area di Tabel 2
   

Catatan. Saat mengetikkan rumus VLOOKUP, jangan lupa untuk mengunci referensi pada parameter kedua dengan menekan tombol F4. Ini akan memastikan bahwa rentang pencarian tidak bergeser saat rumus disalin ke sel lainnya

Penjelasan Formula

Mari uraikan rumus yang kita gunakan untuk memahami apa yang sebenarnya terjadi

=ARRAYFORMULA(VLOOKUP(B17&" "&C17,{$A$4:$A$12&" "&$B$4:$B$12,$C$4:$C$12},2,false))

• Pertama, kami menggunakan ARRAYFORMULA untuk membuat semacam tabel virtual. Tabel virtual ini berisi kolom-kolom berikut
  • Kolom berisi kombinasi nilai dari sel $A$4. $A$12 dan sel $B$4. $B$12, dipisahkan dengan spasi di antara setiap nilai sel. $2. $A$9&” “&$B$2. $B$9
  • Kolom yang berisi nilai dari kolom Bonus pada Tabel 1. C$4. $C$12
• Ini ditentukan dalam kurung kurawal karena kami ingin mengembalikan array atau tabel sel virtual. =ArrayFormula({$A$2. $A$9&” “&$B$2. $B$9,$C$2. $D$9})

Perhatikan bahwa kami menggunakan "," sebagai pemisah untuk rumus array karena kami ingin memperlakukan bagian sebagai kolom di samping satu sama lain, seperti yang ditunjukkan di bawah ini

• Jika kami ingin mereka menjadi baris di atas satu sama lain, kami akan menggunakan titik koma ";"
• Selanjutnya, kita terapkan fungsi VLOOKUP menggunakan rumus array di atas sebagai parameter keduanya. Parameter lainnya sama seperti sebelumnya
  • search_key. Kunci pencarian lagi adalah kombinasi dari nilai kode Departemen dan Area yang ingin kita cari, dipisahkan oleh spasi
  • jangkauan. Rentangnya sekarang adalah array atau tabel virtual. {$A$2. $A$9&” “&$B$2. $B$9,$C$2. $D$9}
  • indeks. Karena tabel virtual yang dibuat hanya terdiri dari dua kolom, di mana Bonus adalah kolom kedua, kami menggunakan 2 sebagai nilai indeks
  • is_sorted. Nilai FALSE untuk parameter ini menunjukkan bahwa kolom pertama rentang pencarian tidak perlu diurutkan dalam urutan menaik

Karena kunci_pencarian dan kolom pertama dari larik yang dikembalikan dalam rentang memiliki format yang persis sama (memiliki Departemen diikuti dengan spasi, diikuti dengan kode Area), fungsi VLOOKUP dapat dengan mudah mencari Bonus yang sesuai dan mengembalikannya

Ini hanyalah ikhtisar singkat tentang dua cara di mana kita dapat menggunakan fungsi VLOOKUP ketika ada beberapa kriteria yang harus dipertimbangkan

Kami mendorong Anda untuk mencoba sendiri contoh yang kami diskusikan dalam tutorial ini. Ini akan membantu Anda merasakan bagaimana rumus bekerja dan akan memberi Anda pemahaman yang lebih dalam tentangnya

Saya harap Anda menemukan tutorial ini bermanfaat

Tutorial Google Spreadsheet lain yang mungkin Anda sukai

• Cara VLOOKUP dari Sheet Lain di Google Sheets
• Cara Menggunakan fungsi INDIRECT di Google Sheets
• Cara Membandingkan Dua Kolom di Google Sheets (untuk Kecocokan dan Perbedaan)
• Cara Menggabungkan Sel Di Google Sheets
• Cara Mendapatkan Nilai Terakhir di Kolom di Google Sheets (Formula Pencarian)
• Cara Menggunakan Fungsi SUMIFS di Google Sheets

Postingan Terpopuler

5 Cara Sederhana untuk Menyoroti Duplikat di Google Sheets

JIKA BERISI Formula Google Sheets [2 Opsi Pintar]

Cara Menerapkan Formula ke Seluruh Kolom di Google Sheets

Cara VLOOKUP Dari Lembar Lain di Google Lembar

Puncak

Sumit adalah Pakar Google Spreadsheet dan Microsoft Excel. Dia memberikan pelatihan spreadsheet kepada perusahaan dan telah dianugerahi penghargaan bergengsi Excel MVP oleh Microsoft atas kontribusinya dalam berbagi pengetahuan Excel dan membantu orang.

3 pemikiran pada “Cara VLOOKUP Banyak Kriteria di Google Sheets (Langkah Mudah)”

1. Amber

   Saya pikir ini memiliki beberapa kesalahan ketik
   Untuk menampilkan tarif per jam, katakanlah, ID Karyawan "E010", Anda perlu mengambilnya dari tabel Karyawan menggunakan fungsi VLOOKUP. Inilah yang akan Anda masukkan di sel E3

   =VLOOKUP(A3,$A$3. $C$8,3, salah)

   Dan sebaliknya harus mengatakan

   Untuk menampilkan tarif per jam, katakanlah, ID Karyawan "E010", Anda perlu mengambilnya dari tabel Karyawan menggunakan fungsi VLOOKUP. Inilah yang akan Anda masukkan di sel **F3

   =VLOOKUP(E3,$A$3. $C$8,3, salah)

   • Christopher Daniel

     Tangkapan bagus Amber, terima kasih

2. Ariyo

   Terima kasih Sumut untuk artikelnya

   Saya punya 2 tabel, saya ingin membandingkan 4 kolom serupa di kedua tabel lalu mengembalikan nilainya di kolom kelima menggunakan vlookup

   Bagaimana cara menarik data secara otomatis dari satu spreadsheet ke spreadsheet lainnya?

   Salin sel dari satu lembar ke lembar lainnya dengan. Dari Excel (atau aplikasi spreadsheet apa pun), buka atau buat lembar baru. Pilih sel tempat Anda ingin menarik data. Ketik = segera diikuti dengan nama lembar sumber Anda, tanda seru, dan nama sel yang akan disalin. Misalnya, = Daftar

   Bagaimana cara menarik data tertentu dari Google Sheets?

   Cukup pilih kolom (atau rentang sel dalam kolom) tempat Anda ingin menarik data dan memperluas salah satu grup berdasarkan tugas Anda. .

   Jalankan Power Tools dari menu Google Sheets. Ekstensi > Perkakas Listrik > Mulai

   Pergi ke grup Teks

   Temukan dan klik ikon Ekstrak

   Rumus mana yang dapat mengambil data dari spreadsheet Google lainnya?

   Impor data dari sheet di dokumen lain . Buka lembar tujuan Anda. Pilih sel kosong. Perhatikan bahwa data Anda akan ditransfer di sudut kiri atas

   Bagaimana cara mengisi data secara otomatis di Google Sheets berdasarkan sel lain?

   Pemformatan Bersyarat Berdasarkan Nilai Sel Lain .

   Pilih sel yang ingin Anda format

   Klik "Format" di bilah navigasi, lalu pilih "Pemformatan Bersyarat. "

   Di bawah "Aturan Format", pilih "Rumus khusus adalah. "

   Tulis rumus Anda, lalu klik "Selesai. "

   Konfirmasi aturan Anda telah diterapkan dan periksa sel