Cara Mencari Berbilang Nilai Dalam Excel: Langkah Demi Langkah

Apabila bekerja dengan set data yang besar dalam Microsoft Excel, mungkin sukar untuk mencari berbilang nilai yang sepadan dengan beberapa syarat. Beberapa fungsi terbina dalam, contohnya VLOOKUP, pada asalnya direka bentuk untuk berfungsi dengan nilai tunggal.

Beberapa fungsi Excel boleh digabungkan untuk mencari berbilang nilai. Ia termasuk fungsi VLOOKUP, INDEX, MATCH dan IF. Versi semasa Excel boleh menggunakan tatasusunan dinamik manakala versi lama menggunakan formula tatasusunan.

Artikel ini akan menunjukkan kepada anda cara menggunakan fungsi ini dalam formula yang mencari berbilang nilai dalam data anda.

Mari pergi!

Isi kandungan

• Cara Menggunakan VLOOKUP dengan Berbilang Nilai
  • Apakah Formula Array?
  • Contoh VLOOKUP
  • Versi Terkini Excel
  • Versi Lama
  • Padanan Tepat lwn Padanan Anggaran
  • Lagi Mengenai VLOOKUP
• Cara Menggunakan INDEX Dan PADANAN untuk Mencari Berbilang Nilai
  • Cara Menggunakan INDEX dan MATCH Bersama dalam Excel 365
  • Bekerja dengan Versi Lama Excel
• Cara Menggunakan Fungsi IF untuk Mencari Berbilang Nilai
  • Versi Semasa Excel
  • Versi Lama Excel
• Cara Menggunakan SUMPRODUCT untuk Pelbagai Kriteria
• 3 Ralat Biasa dengan Fungsi Carian
  • 1. #N/A Ralat
  • 2. #REF! Kesilapan
  • 3. Ralat Pekeliling
• Cara Menggunakan IFERROR dengan Fungsi Carian
• 7 Teknik Carian Lanjutan
  • 1. Carian Kedudukan Relatif
  • 2. Fungsi KECIL
  • 3. Fungsi ROW
  • 4. Fungsi PENAPIS
  • 5. Lajur Pembantu
  • 6. Tatasusunan Dinamik
  • 7. Power Query
• Fikiran Akhir

Cara Menggunakan VLOOKUP dengan Berbilang Nilai

Fungsi VLOOKUP sering digunakan untuk mencari nilai tunggal dalam julat data. Walau bagaimanapun, anda juga boleh mencari berbilang padanan dalam Excel dengan formula carian ini.

Secara lalai, ia hanya akan mengembalikan nilai padanan pertama yang ditemuinya. Walau bagaimanapun, anda boleh mengubah suai fungsi untuk mengembalikan berbilang nilai dengan menggunakan formula tatasusunan.

Apakah Formula Array?

Formula tatasusunan ialah formula yang boleh melakukan pengiraan pada tatasusunan data. Ia dipanggil formula tatasusunan kerana ia boleh mengembalikan tatasusunan hasil, bukannya satu nilai.

Terdapat beberapa langkah mudah semasa membuat formula tatasusunan:

1. Pilih julat sel untuk dicari.

2. Masukkan formula dalam bar formula.

3. Tekan Ctrl + Shift + Enter untuk melengkapkannya.

Sintaks formula tatasusunan adalah serupa dengan formula biasa, tetapi ia termasuk pendakap kerinting {} di sekeliling formula. Pendakap kerinting menunjukkan bahawa formula ialah formula tatasusunan dan ia akan mengembalikan tatasusunan nilai.

Contoh dalam artikel ini akan menunjukkan kepada anda cara menggunakan formula tatasusunan dengan betul.

Contoh VLOOKUP

Contoh kami mempunyai lima item dalam lajur A lembaran kerja:

• epal

• pisang

• roti

• lobak merah

• ceri

Tugasnya adalah untuk memeriksa sama ada tiga buah tertentu ada dalam senarai ini: epal, pisang dan ceri.

Versi Terkini Excel

Sintaks VLOOKUP bergantung pada versi Excel yang anda gunakan.

Versi terbaharu ( Excel 365 atau Excel 2021 ) menyokong tatasusunan dinamik. Ciri ini membolehkan formula mengembalikan berbilang hasil yang "tumpah" ke dalam sel bersebelahan.

Ini ialah sintaks (tanda sama memulakan formula):

=VLOOKUP(nilai_carian, tatasusunan_jadual, bilangan_indeks_kol, [julat_carian])

• lookup_value: Nilai yang anda ingin cari.

• table_array: Keseluruhan jadual data yang anda ingin cari.

• col_index_num: Nombor lajur jadual dalam table_array yang mengandungi data yang anda ingin pulangkan.

• range_lookup: Pilihan. Menentukan sama ada anda mahu padanan tepat atau padanan anggaran.

Contoh khusus kami menggunakan formula ini:

=VLOOKUP({“epal”,”pisang”,”ceri”},A1:A5,1,SALAH)

Apabila formula dimasukkan ke dalam sel B1, hasilnya tumpah ke dalam sel C1 dan D1. Gambar ini menunjukkan contoh dalam tindakan:

Versi Lama

Jika anda menggunakan versi Excel yang lebih lama yang tidak menyokong tatasusunan dinamik (cth, Excel 2019 atau lebih awal), anda perlu menggunakan pendekatan yang sedikit berbeza dengan formula tatasusunan.

Ikut langkah-langkah ini:

1. Klik pada sel di mana anda ingin memaparkan keputusan untuk item pertama (cth, lajur B).

2. Taip formula berikut tanpa menekan Enter lagi: =VLOOKUP({“apple”,”banana”,”cherry”}, A1:A5, 1, FALSE)

3. Tekan Ctrl + Shift + Enter untuk mengubahnya menjadi formula tatasusunan.

4. Salin sel B1 dan tampalkannya ke dalam sel di bawah (atau gunakan pemegang isian).

Apabila anda menggunakan Ctrl + Shift + Enter, Excel menambah pendakap kerinting di sekeliling formula. Ini menunjukkan bahawa ia adalah formula tatasusunan.

Padanan Tepat lwn Padanan Anggaran

Secara lalai, fungsi VLOOKUP menggunakan padanan anggaran. Ini bermakna ia akan mengembalikan padanan terdekat yang boleh ditemui, walaupun nilai sel bukan padanan tepat.

Jika anda ingin melakukan padanan tepat, anda boleh menetapkan argumen range_lookup kepada FALSE.

Perlu diingat bahawa anggaran padanan berfungsi paling baik dengan nilai berangka tersusun. Ia biasanya tidak sesuai apabila nilai sel ialah teks.

Lagi Mengenai VLOOKUP

Jika anda ingin mengetahui lebih lanjut tentang fungsi serba boleh ini, lihat artikel ini:

• bagaimana untuk membandingkan dua lajur dalam Excel dengan VLOOKUP

• bagaimana untuk mencari percanggahan dengan VLOOKUP

Memandangkan anda telah ditetapkan dengan fungsi VLOOKUP, mari kita lihat dua fungsi lain yang boleh melakukan fungsi itu dengan cara yang berbeza: INDEX dan MATCH.

Cara Menggunakan INDEX Dan PADANAN untuk Mencari Berbilang Nilai

Anda boleh menggabungkan fungsi INDEX dan MATCH bersama-sama untuk mencari berbilang nilai dalam berbilang baris.

Fungsi INDEX dalam Excel mengembalikan nilai atau rujukan kepada sel dalam julat tertentu.

=INDEX(tatasusunan, nombor_baris, [nombor_lajur])

• tatasusunan: Julat sel yang akan dicari untuk nilai.

• row_num: Nombor baris dalam tatasusunan untuk mengembalikan nilai.

• column_num: (Pilihan) Nombor lajur dalam tatasusunan untuk mengembalikan nilai. Jika ditinggalkan, fungsi mengembalikan keseluruhan baris.

Fungsi MATCH dalam Excel mengembalikan kedudukan nilai dalam julat yang ditentukan.

=MATCH(lookup_value, lookup_array, [match_type])

• lookup_value: Nilai yang hendak dicari dalam lookup_array.

• lookup_array: Julat sel untuk dicari untuk lookup_value.

• match_type: (Pilihan) Jenis perlawanan yang akan dilakukan. Jika ditinggalkan, fungsi melakukan padanan tepat.

Cara Menggunakan INDEX dan MATCH Bersama dalam Excel 365

Untuk menggunakan INDEX dan MATCH bersama-sama untuk mencari berbilang nilai dalam Excel, anda perlu menggunakan formula tatasusunan.

Bekerja dengan data sampel terdahulu, ini ialah formula dalam Excel 365:

=INDEX(A1:A5, MATCH({“epal”,”pisang”,”ceri”}, A1:A5, 0))

Contoh di atas terpecah seperti ini:

• INDEX: ini mengembalikan nilai sel dalam julat tertentu berdasarkan nombor baris dan lajur yang diberikan. Dalam kes ini, ia akan mengembalikan nilai dari julat A1:A5.

• A1:A5: Ini ialah julat jadual yang ditentukan tempat anda mencari nilai dan dari mana hasilnya akan dikembalikan.

• MATCH: ini mencari item yang ditentukan dalam julat sel dan mengembalikan kedudukan relatif item tersebut dalam julat.

• {“apple”,”banana”,”cherry”}: ini ialah pemalar tatasusunan yang mengandungi nilai yang anda ingin cari.

• A1:A5: ini ialah julat di mana MATCH akan mencari nilai daripada pemalar tatasusunan.

• 0: ini ialah jenis padanan untuk fungsi MATCH. Dalam kes ini, ia ialah 0, yang bermaksud anda sedang mencari padanan tepat dan bukannya padanan rapat.

 Gambar ini menunjukkan formula dalam tindakan:

Bekerja dengan Versi Lama Excel

Jika anda menggunakan fail Excel yang lebih lama yang tidak menyokong tatasusunan dinamik (cth, Excel 2019 atau lebih awal), anda perlu menggunakan pendekatan yang berbeza.

Oleh kerana versi lama tidak menyokong formula "tumpah" ke dalam sel bersebelahan, anda perlu membahagikan penggunaan kepada tiga formula berasingan.

Ikut langkah-langkah ini:

1. Klik pada sel di mana anda mahu keputusan untuk item pertama (cth, sel B1)

2. Taip formula di bawah:

3. =INDEX(A1:A5, MATCH(“epal”, A1:A5, 0))

4. Tekan Enter untuk melaksanakan formula.

5. Taip formula ini dalam sel B2: =INDEX(A1:A5, MATCH("pisang", A1:A5, 0))

6. Taipkan formula ini dalam sel B3: =INDEX(A1:A5, MATCH("ceri", A1:A5, 0))

Gambar ini menunjukkan rujukan sel B3:

Fungsi INDEX dan MATCH bukanlah satu-satunya fungsi yang membolehkan anda mencari berbilang nilai. Dalam bahagian seterusnya, kami melihat bagaimana anda boleh menggunakan fungsi IF sebagai alternatif.

Cara Menggunakan Fungsi IF untuk Mencari Berbilang Nilai

Satu lagi cara untuk mencari nilai sel berbilang berdasarkan kriteria tertentu ialah menggunakan fungsi IF dengan fungsi lain.

Fungsi IF membolehkan anda menguji berbilang keadaan dan mengembalikan hasil yang berbeza bergantung pada hasil ujian tersebut.

Sebagai contoh, katakan anda mempunyai jadual data jualan dengan lajur untuk Produk dan Jualan. Anda ingin mencari dan menjumlahkan jumlah jualan untuk dua daripada tiga produk.

Versi Semasa Excel

Untuk mencari jumlah lajur Jualan dengan produk sama ada "Apple" atau "Pisang" menggunakan fungsi IF, anda boleh menggunakan formula tatasusunan dengan fungsi IF, SUM dan OR.

Dengan mengandaikan data anda bermula dalam sel A1, gunakan formula berikut:

=SUM(JIKA((A2:A4=”Epal”)+(A2:A4=”Pisang”), B2:B4, 0))

Bahagian (A2:A4=”Epal”)+(A2:A4=”Pisang”) mencipta tatasusunan yang mempunyai nilai 1 jika sel dalam julat A2:A4 mengandungi “Epal” atau “Pisang”, dan 0 sebaliknya .

Pernyataan IF menyemak setiap elemen argumen tatasusunan. Jika nilainya ialah 1 (iaitu, produk itu sama ada "Epal" atau "Pisang"), ia mengambil nilai yang sepadan dalam lajur Jualan (julat B2:B4); jika tidak, ia memerlukan 0.

Fungsi SUM menjumlahkan nilai daripada fungsi IF, dengan berkesan menjumlahkan nilai jualan untuk kedua-dua "Apple" dan "Banana".

Gambar ini menunjukkan formula dalam tindakan pada julat carian:

Versi Lama Excel

Dalam Excel 2019 atau lebih awal, anda perlu menggunakan formula tatasusunan. Ikut langkah-langkah ini:

1. Taip formula tetapi jangan tekan Enter.

2. Tekan Ctrl + Shift + Enter untuk menjadikannya formula tatasusunan.

Excel akan menambah kurungan kerinting {} di sekeliling formula, menunjukkan ia adalah formula tatasusunan.

Seterusnya, kami melihat cara anda boleh menggunakan SUMPRODUCT untuk mencari beberapa nilai berdasarkan kriteria anda. Mari pergi!

Cara Menggunakan SUMPRODUCT untuk Pelbagai Kriteria

Fungsi SUMPRODUCT juga membolehkan anda mencari berbilang nilai berdasarkan berbilang kriteria.

Kerana ia tidak memerlukan penggunaan formula tatasusunan, sintaks adalah sama tanpa mengira versi Excel.

Menggunakan data yang sama seperti dalam contoh sebelumnya, formula kelihatan seperti ini:

=SUMPRODUCT((A2:A4=”Epal”)+(A2:A4=”Pisang”), B2:B4)

Bahagian (A2:A4=”Epal”)+(A2:A4=”Pisang”) mencipta tatasusunan yang mempunyai nilai 1 jika sel dalam julat A2:A4 mengandungi “Epal” atau “Pisang”, dan 0 sebaliknya .

Fungsi SUMPRODUCT mendarabkan elemen tatasusunan dengan elemen yang sepadan dalam lajur Jualan (julat B2:B4). Ia kemudian menjumlahkan nilai yang terhasil, dengan berkesan menjumlahkan nilai jualan untuk kedua-dua "Epal" dan "Pisang".

Formula di bawah menunjukkannya dalam tindakan:

Fungsi Excel sangat mengagumkan apabila ia berfungsi seperti yang diharapkan, tetapi kadangkala anda mungkin menghadapi ralat. Dalam bahagian seterusnya, kami membincangkan beberapa ralat biasa dan cara anda boleh menanganinya.

3 Ralat Biasa dengan Fungsi Carian

Fungsi carian kadangkala boleh mengembalikan ralat yang boleh mengecewakan dan memakan masa untuk menyelesaikan masalah. Tiga ralat paling biasa yang anda akan hadapi ialah:

1. #N/A Ralat

2. #REF! Kesilapan

3. Kesilapan Pekeliling

1. #N/A Ralat

Ralat #N/A berlaku apabila nilai carian tidak ditemui dalam tatasusunan carian.

Terdapat beberapa sebab mengapa ralat ini mungkin berlaku, termasuk:

• nilai carian salah eja atau salah.

• tatasusunan carian tidak diisih dalam tertib menaik.

• nilai carian tiada dalam set data.

Apabila nilai carian tiada dalam set data, ini adalah maklumat yang berguna. Walau bagaimanapun, pengguna Excel yang tidak berpengalaman mungkin berfikir bahawa #N/A bermakna ada sesuatu yang tidak kena dengan formula. Bahagian seterusnya menunjukkan cara untuk menjadikannya lebih mesra pengguna.

2. #REF! Kesilapan

#REF! ralat berlaku apabila tatasusunan carian atau tatasusunan kembali dipadamkan atau dialihkan.

Ralat ini boleh dibetulkan dengan mengemas kini rujukan sel dalam fungsi carian.

3. Ralat Pekeliling

Semasa anda menggabungkan fungsi dalam formula kompleks, Excel mungkin memberitahu anda bahawa anda mempunyai rujukan bulat.

Anda akan mendapati perkara ini lebih mudah untuk disiasat dengan menggunakan panduan kami untuk mencari rujukan pekeliling dalam Excel .

Cara Menggunakan IFERROR dengan Fungsi Carian

Fungsi IFERROR ialah alat yang berguna untuk mengendalikan ralat dalam fungsi carian. Ia membolehkan anda menentukan nilai atau formula untuk dikembalikan jika fungsi carian mengembalikan ralat.

Sintaks untuk fungsi IFERROR adalah seperti berikut:

=IFERROR(nilai, nilai_jika_ralat)

· nilai: nilai atau formula yang anda ingin nilai.

· Value_if_error: nilai atau formula untuk dikembalikan jika argumen pertama mengembalikan ralat.

Sebagai contoh, katakan anda mempunyai fungsi VLOOKUP yang mencari berbilang nilai dalam jadual. Dalam gambar di bawah, salah satu nilai tidak wujud dalam julat data yang dicari.

Seperti yang anda lihat, ralat #N/A dipaparkan, yang boleh mengelirukan pengguna Excel yang tidak berpengalaman.

Sebaliknya, anda boleh menggunakan IFERROR untuk memaparkan sel kosong atau mesej yang mengatakan "Tidak dijumpai" dengan sintaks ini:

=IFERROR(VLOOKUP(lookup_value, table_array, column_index, FALSE), “Not found”)

Dalam contoh ini, jika fungsi VLOOKUP mengembalikan ralat, fungsi IFERROR akan mengembalikan mesej "Tidak ditemui".

Gambar ini menunjukkan formula dalam tindakan. Lajur B mempunyai nilai yang tiada, manakala lajur C dan lajur D telah menemui padanan.

Kami telah membincangkan banyak perkara setakat ini, dan anda akhirnya bersedia untuk mempelajari teknik yang lebih maju untuk carian, yang merupakan topik bahagian seterusnya.

7 Teknik Carian Lanjutan

Mencari berbilang nilai dalam Excel boleh menjadi tugas yang mencabar, terutamanya apabila berurusan dengan set data yang besar. Anda mungkin menghadapi masalah prestasi dengan pemprosesan yang perlahan.

Terdapat tujuh teknik carian lanjutan yang boleh anda gunakan untuk menjadikan proses lebih mudah dan cekap.

• Carian kedudukan relatif

• Fungsi KECIL

• fungsi ROW

• Fungsi PENAPIS

• Lajur pembantu

• Tatasusunan Dinamik

• Power Query

1. Carian Kedudukan Relatif

Salah satu cara paling mudah untuk mencari berbilang nilai dalam Excel ialah dengan menggunakan carian kedudukan relatif. Ini melibatkan penetapan baris dan lajur mengimbangi daripada sel tertentu untuk mencari nilai yang dikehendaki.

Contohnya, jika anda mempunyai jadual data dan ingin mencari nilai yang dua baris ke bawah dan tiga lajur di sebelah kanan sel tertentu, anda boleh menggunakan formula berikut:

=OFFSET(sel, 2, 3)

2. Fungsi KECIL

Satu lagi teknik berguna untuk carian nilai berbilang adalah menggunakan fungsi KECIL. Fungsi ini mengembalikan nilai ke-n terkecil dalam julat sel.

Dengan menggabungkan fungsi ini dengan fungsi carian lain seperti INDEX dan MATCH, anda boleh mencari berbilang nilai berdasarkan kriteria tertentu.

Sebagai contoh, formula berikut mencari nilai kedua terkecil dalam julat sel yang memenuhi syarat tertentu:

=INDEX(data, PADANAN(KECIL(JIKA(kriteria, julat), 2), julat, 0))

3. Fungsi ROW

Fungsi ROW juga boleh digunakan untuk carian nilai berbilang dalam Excel. Fungsi ini mengembalikan nombor baris sel tertentu, yang boleh digunakan untuk merujuk sel dalam jadual data.

Sebagai contoh, formula berikut mencari nilai dalam jadual berdasarkan pengecam unik:

=INDEX(data, MATCH(unique_identifier, data[unique_identifier], 0), column_index_number)

4. Fungsi PENAPIS

Fungsi FILTER tidak tersedia dalam versi Excel yang lebih lama.

Dalam Excel 365, anda boleh menggunakannya untuk menapis julat sel berdasarkan kriteria tertentu dan hanya mengembalikan nilai yang memenuhi kriteria tersebut. Ini ialah sintaks dan tiga argumen:

=FILTER(tatasusunan, sertakan, [jika_kosong])

• tatasusunan: Data khusus yang anda ingin tapis.

• include: Argumen sertakan ialah kriteria atau syarat yang anda mahu gunakan pada tatasusunan.

• [if_empty] (pilihan): Nilai untuk dikembalikan jika tiada baris atau lajur memenuhi kriteria yang dinyatakan dalam argumen include.

Sebagai contoh, formula berikut berfungsi pada data contoh untuk mencari padanan untuk dua daripada tiga item dalam lajur pertama dan jumlah nilai sepadannya dalam lajur kedua.

=JUMLAH(PENAPIS(B2:B4, (A2:A4=”Epal”)+(A2:A4=”Pisang”)))

Gambar ini menunjukkan bilangan baris yang dipadankan dan jumlah nilai yang berbeza:

5. Lajur Pembantu

Anda boleh menggunakan lajur pembantu untuk menyertai berbilang medan bersama-sama dalam fungsi seperti VLOOKUP.

Katakan anda bekerja dengan nama pertama dan nama keluarga dalam helaian berasingan. Anda boleh menggabungkannya dalam lajur pembantu yang dirujuk dalam formula akhir.

6. Tatasusunan Dinamik

Seperti yang telah anda pelajari dalam contoh terdahulu, pengguna Microsoft 365 boleh memanfaatkan tatasusunan dinamik untuk carian nilai berbilang dalam Excel.

Tatasusunan dinamik membolehkan anda mengembalikan berbilang nilai daripada satu formula, menjadikannya lebih mudah untuk mencari sejumlah besar data.

7. Power Query

Power Query ialah alat berkuasa dalam Excel yang boleh digunakan untuk mengembalikan nilai berdasarkan berbilang kriteria.

Sebagai contoh, video ini menemui data yang tidak kemas dalam hamparan dan membersihkannya.

Fikiran Akhir

Itu melengkapkan penyelaman mendalam kami ke dalam seni mencari pelbagai nilai dalam Excel! Sebaik sahaja anda telah menguasai formula VLOOKUP, INDEX, MATCH dan tatasusunan, anda akan mendapati diri anda melalui set data yang kompleks seperti pisau panas melalui mentega.

Kuncinya ialah memahami sintaks dan logik di sebalik setiap formula. Perlu diingat bahawa formula ini boleh menjadi rumit, jadi penting untuk meluangkan masa anda dan menguji formula anda dengan teliti sebelum bergantung padanya untuk analisis data yang penting.

Excel ialah alat yang berkuasa, tetapi ia hanya sebaik pengguna. Oleh itu, teruskan mengasah kemahiran tersebut, teruskan bereksperimen dan tidak lama lagi anda akan menjadi mahir dalam carian berbilang nilai. Sehingga masa yang akan datang, teruskan mengetuk nombor tersebut dan buat Excel melakukan kerja keras untuk anda!