Tutorial ini menerangkan sintaks fungsi Vookup Google dan menunjukkan cara menggunakan formula VLOOKUP untuk menyelesaikan tugas-tugas kehidupan sebenar.

Apabila bekerja dengan data yang saling berkaitan, salah satu cabaran yang paling biasa ialah mencari maklumat di pelbagai helaian. Anda sering melakukan tugas -tugas sedemikian dalam kehidupan seharian, contohnya ketika mengimbas papan jadual penerbangan untuk nombor penerbangan anda untuk mendapatkan masa dan status yang berlepas. Google Sheets Vlookup berfungsi dengan cara yang sama - melihat dan mengambil data yang sepadan dari jadual lain pada lembaran yang sama atau dari lembaran yang berbeza.

Pendapat yang meluas adalah bahawa VLOOKUP adalah salah satu fungsi yang paling sukar dan tidak jelas. Tetapi itu tidak benar! Sebenarnya, mudah untuk melakukan vlookup di Helaian Google, dan dalam seketika anda akan memastikannya.

Petua. Bagi pengguna Microsoft Excel, kami mempunyai tutorial Excel Vlookup yang berasingan dengan contoh formula.

Google Sheets VLOOKUP - Sintaks dan Penggunaan

Fungsi VLOOKUP di Google Helaian direka untuk melakukan carian menegak - cari nilai utama (pengenal unik) ke lajur pertama dalam julat yang ditentukan dan mengembalikan nilai dalam baris yang sama dari lajur lain.

Sintaks untuk fungsi Google Sheets VLOOKUP adalah seperti berikut:

3 hujah pertama diperlukan, yang terakhir adalah pilihan:

Carian_key - adalah nilai untuk mencari (nilai carian atau pengenal unik). Sebagai contoh, anda boleh mencari perkataan "epal", nombor 10, atau nilai dalam sel A2.

Julat - Dua atau lebih lajur data untuk carian. Fungsi Google Sheets Vlookup sentiasa mencari dalam lajur pertama julat .

Indeks - Nombor lajur dalam julat yang mana nilai yang sepadan (nilai dalam baris yang sama seperti carian_key ) harus dikembalikan.

Lajur pertama dalam julat mempunyai indeks 1. Jika indeks kurang dari 1, formula VLOOKUP mengembalikan #Value! ralat. Jika ia lebih besar daripada bilangan lajur dalam julat , VLOOKUP mengembalikan #REF! ralat.

Is_sorted - Menunjukkan sama ada lajur carian disusun (benar) atau tidak (palsu). Dalam kebanyakan kes, palsu adalah disyorkan.

• Jika is_sorted adalah benar atau ditinggalkan (lalai), lajur pertama julat mesti disusun dalam urutan menaik , iaitu dari A hingga z atau dari terkecil hingga terbesar.

  Dalam kes ini formula VLOOKUP mengembalikan perlawanan anggaran . Lebih tepat lagi, ia mencari perlawanan tepat terlebih dahulu. Jika perlawanan tepat tidak dijumpai, formula mencari perlawanan terdekat yang kurang daripada atau sama dengan Search_Key . Jika semua nilai dalam lajur Lookup lebih besar daripada kekunci carian, ralat #N/A dikembalikan.

• Jika is_sorted ditetapkan kepada palsu, tiada penyortiran diperlukan. Dalam kes ini, formula VLOOKUP mencari perlawanan tepat . Jika lajur Lookup mengandungi 2 atau lebih nilai yang sama dengan Search_Key , nilai pertama yang dijumpai dikembalikan.

Pada pandangan pertama, sintaks mungkin kelihatan agak rumit, tetapi contoh formula Vookup di bawah Google akan menjadikan perkara lebih mudah difahami.

Katakan anda mempunyai dua jadual: jadual utama dan jadual carian seperti ditunjukkan dalam tangkapan skrin di bawah. Jadual mempunyai lajur yang sama ( ID pesanan ) yang merupakan pengenal yang unik. Anda bertujuan untuk menarik status setiap pesanan dari jadual carian ke jadual utama.

Sekarang, bagaimana anda menggunakan Google Sheets Vlookup untuk menyelesaikan tugas? Untuk memulakan, mari kita tentukan hujah -hujah untuk formula vlookup kita:

• Search_Key - ID Pesanan (A3), nilai yang akan dicari dalam lajur pertama jadual carian.
• Julat - Jadual Lookup ($ F $ 3: $ g $ 14). Sila perhatikan bahawa kami mengunci julat dengan menggunakan rujukan sel mutlak kerana kami merancang untuk menyalin formula ke pelbagai sel.
• Indeks - 2 Kerana lajur status dari mana kita mahu mengembalikan perlawanan adalah lajur 2 ^ND dalam julat .
• Is_sorted - palsu kerana lajur carian kami (f) tidak disusun.

Meletakkan semua hujah bersama, kami mendapat formula ini:

=VLOOKUP(A3,$F$3:$G$14,2,false)

Masukkannya di sel pertama (D3) jadual utama, salin lajur, dan anda akan mendapat hasil yang serupa dengan ini:

Adakah formula VLOOKUP masih sukar untuk anda fahami? Kemudian lihatlah dengan cara ini:

5 Perkara Yang Perlu Diketahui Mengenai Google Sheets Vlookup

Seperti yang telah anda fahami, fungsi Google Sheets Vlookup adalah perkara dengan nuansa. Mengingati lima fakta mudah ini akan menghalang anda daripada masalah dan membantu anda mengelakkan kesilapan VOLLOOK yang paling biasa.

1. Google Sheets Vlookup tidak dapat melihat kirinya, ia sentiasa mencari di lajur pertama (paling kiri) julat. Untuk melakukan vlookup kiri , gunakan formula perlawanan Indeks Google Sheets.
2. VLOOKUP dalam Lembaran Google adalah kes-tidak sensitif , bermakna ia tidak membezakan huruf kecil dan huruf besar. Untuk carian sensitif kes , gunakan formula ini.
3. Jika VLOOKUP mengembalikan keputusan yang salah, tetapkan hujah IS_Sorted kepada palsu untuk mengembalikan perlawanan tepat. Jika ini tidak membantu, periksa sebab -sebab lain yang mungkin mengapa VLOOKUP gagal.
4. Apabila IS_Sorted ditetapkan kepada benar atau ditinggalkan, ingatlah untuk menyusun lajur pertama julat dalam urutan menaik. Dalam kes ini, fungsi VLOOKUP akan menggunakan algoritma carian binari yang lebih cepat yang berfungsi dengan betul hanya pada data yang disusun.
5. Google Sheets VLOOKUP boleh mencari dengan perlawanan separa berdasarkan watak Wildcard : tanda tanya (?) Dan Asterisk (*). Sila lihat contoh formula VLOOKUP ini untuk maklumat lanjut.

Cara Menggunakan VLOOKUP di Helaian Google - Contoh Formula

Sekarang anda mempunyai idea asas bagaimana Google Sheets Vlookup berfungsi, sudah tiba masanya untuk mencuba tangan anda dalam membuat beberapa formula sendiri. Untuk membuat contoh di bawah VOLLOOW lebih mudah untuk diikuti, anda boleh membuka sampel VLOOKUP Google Sheet.

Cara VLOOKUP dari lembaran yang berbeza

Dalam spreadsheet kehidupan sebenar, jadual utama dan jadual carian sering tinggal di lembaran yang berbeza. Untuk merujuk formula VLOOKUP anda ke lembaran lain dalam spreadsheet yang sama, letakkan nama lembaran kerja diikuti dengan tanda seru (!) Sebelum rujukan julat. Contohnya:

=VLOOKUP(A2,Sheet4!$A$2:$B$13,2,false)

Formula ini akan mencari nilai dalam A2 dalam julat A2: A13 pada Sheet4, dan mengembalikan nilai yang sepadan dari lajur B (lajur 2 ^ND dalam julat ).

Jika nama lembaran termasuk ruang atau aksara bukan alphabetical, pastikan untuk memasukkannya dalam tanda petikan tunggal. Contohnya:

=VLOOKUP(A2,'Lookup table'!$A$2:$B$13,2,false)

Petua. Daripada menaip rujukan ke lembaran lain secara manual, anda boleh memasukkan lembaran Google untuk anda secara automatik. Untuk ini, mula menaip formula VLOOKUP anda dan apabila ia datang kepada hujah pelbagai , beralih ke lembaran carian dan pilih julat menggunakan tetikus. Ini akan menambah rujukan kepada formula, dan anda hanya perlu menukar rujukan relatif (lalai) kepada rujukan mutlak. Untuk melakukan ini, sama ada taipkan tanda $ sebelum huruf lajur dan nombor baris, atau pilih rujukan dan tekan F4 untuk bertukar -tukar antara jenis rujukan yang berbeza.

Helaian Google dengan watak Wildcard

Dalam situasi apabila anda tidak tahu keseluruhan nilai carian (carian_key), tetapi anda tahu sebahagian daripadanya, anda boleh melakukan carian dengan watak -watak wildcard berikut:

• Tanda tanya (?) Untuk memadankan watak tunggal, dan
• Asterisk (*) untuk memadankan sebarang urutan aksara.

Katakan anda ingin mendapatkan maklumat mengenai pesanan tertentu dari jadual di bawah. Anda tidak dapat mengingat ID pesanan sepenuhnya, tetapi anda ingat bahawa watak pertama adalah "A". Oleh itu, anda menggunakan asterisk (*) untuk mengisi bahagian yang hilang, seperti ini:

=VLOOKUP("a*",$A$2:$C$13,2,false)

Lebih baik lagi, anda boleh memasuki bahagian carian yang diketahui dalam beberapa sel dan menyatukan sel itu dengan "*" untuk mencipta formula VLOOKUP yang lebih serba boleh:

Untuk menarik item: =VLOOKUP($F$1&"*",$A$2:$C$13,2,false)

Untuk menarik jumlahnya: =VLOOKUP($F$1&"*",$A$2:$C$13,3,false)

Petua. Sekiranya anda perlu mencari tanda tanya sebenar atau watak asterisk, letakkan tilde (~) sebelum watak, misalnya "~*".

Formula Perlawanan Indeks Helaian Google untuk VLOOKUP Kiri

Salah satu batasan yang paling ketara dalam fungsi VLOOKUP (baik dalam Excel dan Helaian Google) adalah bahawa ia tidak dapat melihat kirinya. Iaitu, jika lajur carian bukan lajur pertama dalam jadual carian, Google Sheets Vlookup akan gagal. Dalam situasi seperti ini, gunakan formula perlawanan indeks yang lebih kuat dan lebih tahan lama:

Sebagai contoh, untuk mencari nilai A3 (carian_key) dalam g3: g14 (lookup_range) dan mengembalikan perlawanan dari f3: f14 (return_range), gunakan formula ini:

=INDEX($F$3:$F$14, MATCH (A3, $G$3:$G$14, 0))

Tangkapan skrin berikut menunjukkan formula perlawanan indeks ini dalam tindakan:

Satu lagi kelebihan formula perlawanan indeks berbanding dengan VLOOKUP adalah bahawa ia adalah kebal terhadap perubahan struktur yang anda buat dalam lembaran kerana ia merujuk lajur kembali secara langsung. Khususnya, memasukkan atau memadam lajur dalam jadual carian memecahkan formula VLOOKUP kerana nombor indeks "keras dikodkan" menjadi tidak sah, manakala formula perlawanan indeks tetap selamat dan bunyi.

Untuk maklumat lanjut mengenai perlawanan indeks, sila lihat mengapa perlawanan indeks adalah alternatif yang lebih baik untuk VLOOKUP. Walaupun tutorial di atas mensasarkan Excel, perlawanan indeks dalam lembaran Google berfungsi dengan cara yang sama, kecuali nama -nama argumen yang berlainan.

Vlookup sensitif kes di Helaian Google

Dalam kes-kes apabila kes teks penting, gunakan perlawanan indeks dalam kombinasi dengan fungsi yang benar dan tepat untuk membuat formula array Google Vlookup yang sensitif kes sensitif:

Dengan mengandaikan kunci carian dalam sel A3, julat carian adalah G3: G14 dan julat pulangan adalah F3: F14, formula berjalan seperti berikut:

=ArrayFormula(INDEX($F$3:$F$14, MATCH (TRUE,EXACT($G$3:$G$14, A3),0)))

Seperti yang ditunjukkan dalam tangkapan skrin di bawah, formula tidak mempunyai masalah dengan membezakan huruf besar dan huruf kecil seperti A-1001 dan A-1001:

Petua. Menekan CTRL Shift Enter semasa menyunting formula memasukkan fungsi ArrayFormula pada permulaan formula secara automatik.

Formula VLOOKUP adalah yang paling biasa tetapi bukan satu -satunya cara untuk melihat ke dalam Helaian Google. Bahagian seterusnya dan akhir tutorial ini menunjukkan alternatif.

Gabungkan Lembaran: Alternatif Formula Bebas untuk Google Helaian VLOOKUP

Sekiranya anda mencari cara bebas formula visual untuk melakukan vooksup Google Spreadsheet, pertimbangkan untuk menggunakan Lembaran Gabungan. Anda boleh mendapatkannya secara percuma dari kedai tambahan Google Sheets:

Sebaik sahaja add-on ditambah ke Helaian Google anda, anda boleh menemuinya di bawah tab Sambungan :

Dengan penggabungan lembaran penggabungan di tempat, anda sudah bersedia untuk memberikan ujian lapangan. Data sumber sudah biasa dengan anda: kami akan menarik maklumat dari lajur status berdasarkan ID pesanan :

1. Pilih mana -mana sel dengan data dalam helaian utama dan klik Sambungan > Helaian Gabungan > Mula .

   Dalam kebanyakan kes, add-on akan mengambil keseluruhan jadual untuk anda secara automatik. Jika tidak, sama ada klik butang pilih auto atau pilih julat dalam helaian utama anda secara manual, dan kemudian klik Seterusnya :

   

2. Pilih julat dalam lembaran carian . Julat ini tidak semestinya saiz yang sama seperti julat dalam lembaran utama. Dalam contoh ini, jadual carian mempunyai 2 baris lebih daripada jadual utama.

   

3. Pilih satu atau lebih lajur utama (pengecam unik) untuk membandingkan. Oleh kerana kami membandingkan lembaran dengan ID pesanan , kami hanya memilih lajur ini:

   

4. Di bawah lajur carian , pilih lajur (s) dalam lembaran carian dari mana anda ingin mengambil data. Di bawah lajur utama , pilih lajur yang sepadan dalam lembaran utama yang anda mahu menyalin data.

   Dalam contoh ini, kami menarik maklumat dari lajur status pada lembaran carian ke dalam lajur status pada lembaran utama:

   

5. Secara pilihan, pilih satu atau lebih tindakan tambahan. Selalunya, anda ingin menambah baris yang tidak sepadan dengan akhir jadual utama , iaitu menyalin baris yang hanya ada dalam jadual carian hingga akhir jadual utama. Dan kemas kini jadual utama anda di mana ia berada:

   

Klik gabungan , biarkan lembaran gabungan menambah seketika untuk diproses, dan anda baik untuk pergi!

Video: Cara Menggunakan Helaian Gabungan ke Vlookup Tanpa Formula

VOLLOOKUP MULTIPLE MEMBUAT KAJIAN MUDAH!

Data Penapis & Ekstrak adalah alat Lembaran Google yang lain untuk carian lanjutan. Tambahan boleh mengembalikan semua perlawanan, bukan hanya yang pertama seperti fungsi VLOOKUP. Selain itu, ia boleh menilai pelbagai syarat , melihat ke arah mana -mana , dan mengembalikan semua atau bilangan perlawanan yang ditentukan sebagai nilai atau formula .

Mengingati bahawa gambar bernilai seribu perkataan, mari kita lihat bagaimana add-on berfungsi pada data kehidupan sebenar. Menganggap, beberapa pesanan dalam jadual sampel kami mengandungi beberapa item, dan anda ingin mengambil semua item pesanan tertentu. Formula VLOOKUP tidak dapat melakukan ini, sementara fungsi pertanyaan yang lebih kuat dapat. Masalahnya ialah fungsi ini memerlukan pengetahuan bahasa pertanyaan atau sekurang -kurangnya sintaks SQL. Tidak mempunyai keinginan untuk menghabiskan hari belajar ini? Pasang penapis & ekstrak data add-on dan dapatkan formula sempurna dalam beberapa saat!

Dalam lembaran Google anda, klik Sambungan > Data Penapis & Ekstrak > Mula , dan tentukan kriteria carian:

1. Pilih julat dengan data anda (A1: D15).
2. Tentukan berapa banyak perlawanan untuk kembali (semua dalam kes kita).
3. Pilih lajur mana yang akan mengembalikan data dari ( item , jumlah dan status ).
4. Tetapkan satu atau lebih syarat. Kami mahu menarik maklumat mengenai input nombor pesanan dalam F2, jadi kami mengkonfigurasi hanya satu syarat: pesanan ID = F2.
5. Pilih sel kiri atas untuk hasilnya.
6. Klik Hasil Pratonton untuk memastikan anda akan mendapat apa yang anda cari.
7. Jika semuanya baik, klik sama ada memasukkan formula atau tampal hasil .

Untuk contoh ini, kami memilih untuk mengembalikan perlawanan sebagai formula. Oleh itu, kini anda boleh menaip sebarang nombor pesanan dalam F2, dan formula yang ditunjukkan dalam tangkapan skrin di bawah akan dikira semula secara automatik:

Untuk mengetahui lebih lanjut mengenai Add-On, lawati halaman utama Data Penapis & Ekstrak atau dapatkannya sekarang dari Google Workspace Marketplace:

VIDEO: Cara VLOOKUP Pelbagai Perlawanan Menggunakan Tambah

Begitulah cara anda boleh membuat carian Helaian Google. Saya mengucapkan terima kasih kerana membaca dan berharap dapat melihat anda di blog kami minggu depan!

Spreadsheet dengan contoh formula

Contoh spreadsheet vlookup

Atas ialah kandungan terperinci VLOOKUP di Helaian Google dengan contoh formula. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!