Nilai carian dengan VLOOKUP, INDEX atau MATCH

Important: Artikel ini diterjemahkan oleh mesin, lihatlah notis penafian. Sila dapatkan versi Bahasa Inggeris artikel ini di sini sebagai rujukan anda

Anda mempunyai senarai nombor pejabat dan anda perlu tahu pekerja yang berada dalam setiap pejabat. Akan tetapi, lembaran kerja terlalu besar, oleh itu apa yang anda boleh lakukan? Gunakan fungsi carian. Fungsi VLOOKUP dan HLOOKUP adalah dua fungsi yang sangat berguna, begitu juga INDEX dan MATCH.

Note:  Jika anda cuba mencari Bestari Carian, ciri tersebut tidak lagi menjadi sebahagian daripada Excel.

Berikut ialah peringatan pantas cara menggunakan VLOOKUP.

=VLOOKUP(B2,C2:E7,3,TRUE)

Argumen pertama—secebis data yang digunakan oleh fungsi—ialah nilai yang anda ingin cari. Ia boleh merupakan rujukan sel atau nilai tetap seperti "smith" atau 21,000. Argumen kedua ialah julat sel yang anda fikir mengandungi nilai yang anda ingin cari. Dalam contoh ini, ia adalah C2-C7. Argumen ketiga ialah lajur dalam julat sel tersebut yang mengandungi nilai yang anda ingin lihat.

Argumen keempat adalah pilihan. Anda boleh masukkan True atau False. Jika anda masukkan TRUE atau biarkan argumen kosong, fungsi tersebut mengembalikan padanan anggaran bagi nilai yang anda tentukan dalam argumen pertama. Jika anda masukkan FALSE, fungsi tersebut akan memadankan nilai yang disediakan oleh argumen pertama. Dengan kata lain, membiarkan argumen keempat kosong atau memasukkan TRUE akan memberikan anda lebih kefleksibelan.

Contoh ini menunjukkan anda cara fungsi bekerja. Apabila anda memasukkan nilai dalam sel B2 (argumen pertama), VLOOKUP mencari sel C2-E7 (argumen kedua) dan mengembalikan padanan anggaran terhampir daripada lajur ketiga dalam julat, lajur E (argumen ketiga).

Kegunaan lazim fungsi VLOOKUP

Argumen keempat adalah kosong, oleh itu fungsi mengembalikan padanan anggaran. Jika tidak, anda perlu memasukkan satu daripada nilai dalam lajur C atau D untuk mendapatkan hasil pada semua.

Setelah anda berasa selesa dengan VLOOKUP, fungsi HLOOKUP seharusnya tidak lagi sukar untuk digunakan. Anda memasukkan argumen yang sama tetapi ia mencari nilai dalam baris dan bukannya lajur.

Cubalah

Jika anda ingin mencuba fungsi carian sebelum anda mencubanya dengan data anda sendiri, berikut adalah beberapa data sampel. Sesetengah orang suka menggunakan VLOOKUP dan HLOOKUP, yang lain lebih suka menggunakan INDEX dan MATCH bersama-sama. Cuba setiap kaedah dan tentukan kaedah yang anda paling suka.

Penggunaan VLOOKUP

Salin semua sel dalam jadual ini dan tampalkannya ke dalam sel A1 di lembaran kerja kosong dalam Excel.

Petunjuk    Sebelum anda menampal data ke dalam Excel, setkan lebar lajur untuk lajur A hingga C kepada 250 piksel dan klik Balut Teks (tab Rumah, kumpulan Penjajaran).

Ketumpatan

Kelikatan

Suhu

0.457

3.55

500

0.525

3.25

400

0.606

2.93

300

0.675

2.75

250

0.746

2.57

200

0.835

2.38

150

0.946

2.17

100

1.09

1.95

50

1.29

1.71

0

Formula

Perihalan

Hasil

'=VLOOKUP(1,A2:C10,2)

Menggunakan padanan anggaran, cari nilai 1 dalam lajur A, cari nilai terbesar yang kurang daripada atau sama dengan 1 dalam lajur A iaitu 0.946 kemudian kembalikan nilai daripada lajur B dalam baris yang sama.

=VLOOKUP(1,A2:C10,2)

'=VLOOKUP(1,A2:C10,3,TRUE)

Menggunakan padanan anggaran, cari nilai 1 dalam lajur A, cari nilai terbesar yang kurang daripada atau sama dengan 1 dalam lajur A iaitu 0.946 kemudian kembalikan nilai daripada lajur B dalam baris yang sama.

=VLOOKUP(1,A2:C10,3,TRUE)

'=VLOOKUP(0.7,A2:C10,3,FALSE)

Menggunakan padanan tepat, cari nilai 0.7 dalam lajur A. Oleh sebab tiada padanan tepat dalam lajur A, maka ralat dikembalikan.

=VLOOKUP(0.7,A2:C10,3,FALSE)

'=VLOOKUP(0.1,A2:C10,2,TRUE)

Menggunakan padanan anggaran, cari nilai 0.1 dalam lajur A. Oleh sebab 0.1 adalah kurang daripada nilai terkecil dalam lajur A, maka ralat dikembalikan.

=VLOOKUP(0.1,A2:C10,2,TRUE)

'=VLOOKUP(2,A2:C10,2,TRUE)

Menggunakan padanan anggaran, cari nilai 2 dalam lajur A, cari nilai terbesar yang kurang daripada atau sama dengan 2 dalam lajur A iaitu 1.29 kemudian kembalikan nilai daripada lajur B dalam baris yang sama.

=VLOOKUP(2,A2:C10,2,TRUE)

Penggunaan HLOOKUP

Salin semua sel dalam jadual ini dan tampalkannya ke dalam sel A1 di lembaran kerja kosong dalam Excel.

Petunjuk    Sebelum anda menampal data ke dalam Excel, setkan lebar lajur untuk lajur A hingga C kepada 250 piksel dan klik Balut Teks (tab Rumah, kumpulan Penjajaran).

Gandar

Bearing

Bolt

4

4

9

5

7

10

6

8

11

Formula

Perihalan

Hasil

'=HLOOKUP("Gandar", A1:C4, 2, TRUE)

Mencari "Gandar" dalam baris 1 dan mengembalikan nilai dari baris 2 yang berada dalam lajur yang sama (lajur A).

=HLOOKUP("Gandar",A1:C4,2,TRUE)

'=HLOOKUP("Bearing", A1:C4, 3, FALSE)

Mencari "Bearing" dalam baris 1 dan mengembalikan nilai dari baris 3 yang berada dalam lajur yang sama (lajur B).

=HLOOKUP("Bearing", A1:C4,3,FALSE)

'=HLOOKUP("B", A1:C4, 3, TRUE)

Mencari "B" dalam baris 1 dan mengembalikan nilai dari baris 3 yang berada dalam lajur yang sama. Oleh sebab padanan tepat bagi "B" tidak ditemui, nilai terbesar dalam baris 1 yang kurang daripada "B" digunakan: "Gandar," dalam lajur A.

=HLOOKUP("B", A1:C4,3,TRUE)

'=HLOOKUP("Bolt", A1:C4, 4)

Mencari "Bolt" dalam baris 1 dan mengembalikan nilai dari baris 4 yang berada dalam lajur yang sama (lajur C).

=HLOOKUP("Bolt", A1:C4,4)

'=HLOOKUP(3, {1,2,3;"a","b","c";"d","e","f"}, 2, TRUE)

Mencari nombor 3 dalam pemalar tatasusunan tiga baris dan mengembalikan nilai dari baris 2 dalam lajur yang sama (dalam kes ini, ketiga). Terdapat tiga baris nilai dalam pemalar tatasusunan, setiap baris dipisahkan dengan koma bernoktah (;). Oleh sebab "c" ditemui dalam baris 2 dan dalam lajur yang sama dengan 3, "c" dikembalikan.

=HLOOKUP(3,{1,2,3;"a","b","c";"d","e","f"},2,TRUE)

INDEX dan MATCH dalam tindakan

Contoh ini menggunakan fungsi INDEX dan MATCH bersama-sama untuk mengembalikan nombor invois terawal dan tarikh yang berkaitan untuk setiap lima bandar. Oleh sebab data dikembalikan sebagai nombor, kita menggunakan fungsi TEXT untuk memformatnya sebagai tarikh. Fungsi INDEX sebenarnya menggunakan hasil daripada fungsi MATCH sebagai argumennya. Gabungan fungsi INDEX dan MATCH digunakan sebanyak dua kali dalam setiap formula – pertama, untuk mengembalikan nombor invois dan kemudian untuk mengembalikan tarikh.

Salin semua sel dalam jadual ini dan tampalkannya ke dalam sel A1 di lembaran kerja kosong dalam Excel.

Petunjuk    Sebelum anda menampal data ke dalam Excel, setkan lebar lajur untuk lajur A hingga D kepada 250 piksel dan klik Balut Teks (tab Rumah, kumpulan Penjajaran).

Invois

Bandar

Tarikh Invois

Invois terawal mengikut bandar, dengan tarikh

3115

Atlanta

4/7/12

="Atlanta = "&INDEX($A$2:$C$33,MATCH("Atlanta",$B$2:$B$33,0),1)& ", Tarikh invois: " & TEXT(INDEX($A$2:$C$33,MATCH("Atlanta",$B$2:$B$33,0),3),"m/d/yy")

3137

Atlanta

4/9/12

="Austin = "&INDEX($A$2:$C$33,MATCH("Austin",$B$2:$B$33,0),1)& ", Tarikh invois: " & TEXT(INDEX($A$2:$C$33,MATCH("Austin",$B$2:$B$33,0),3),"m/d/yy")

3154

Atlanta

4/11/12

="Dallas = "&INDEX($A$2:$C$33,MATCH("Dallas",$B$2:$B$33,0),1)& ", Tarikh invois: " & TEXT(INDEX($A$2:$C$33,MATCH("Dallas",$B$2:$B$33,0),3),"m/d/yy")

3191

Atlanta

4/21/12

="New Orleans = "&INDEX($A$2:$C$33,MATCH("New Orleans",$B$2:$B$33,0),1)& ", Tarikh invois: " & TEXT(INDEX($A$2:$C$33,MATCH("New Orleans",$B$2:$B$33,0),3),"m/d/yy")

3293

Atlanta

4/25/12

="Tampa = "&INDEX($A$2:$C$33,MATCH("Tampa",$B$2:$B$33,0),1)& ", Tarikh invois: " & TEXT(INDEX($A$2:$C$33,MATCH("Tampa",$B$2:$B$33,0),3),"m/d/yy")

3331

Atlanta

4/27/12

3350

Atlanta

4/28/12

3390

Atlanta

5/1/12

3441

Atlanta

5/2/12

3517

Atlanta

5/8/12

3124

Austin

4/9/12

3155

Austin

4/11/12

3177

Austin

4/19/12

3357

Austin

4/28/12

3492

Austin

5/6/12

3316

Dallas

4/25/12

3346

Dallas

4/28/12

3372

Dallas

5/1/12

3414

Dallas

5/1/12

3451

Dallas

5/2/12

3467

Dallas

5/2/12

3474

Dallas

5/4/12

3490

Dallas

5/5/12

3503

Dallas

5/8/12

3151

New Orleans

4/9/12

3438

New Orleans

5/2/12

3471

New Orleans

5/4/12

3160

Tampa

4/18/12

3328

Tampa

4/26/12

3368

Tampa

4/29/12

3420

Tampa

5/1/12

3501

Tampa

5/6/12

Lanjutan tentang fungsi carian

Halaman Atas

Note: Notis Penafian Penterjemahan Mesin: Artikel ini telah diterjemah oleh sistem komputer tanpa campur tangan manusia. Microsoft menawarkan penterjemahan mesin ini untuk membantu pengguna-pengguna yang tidak bertutur dalam Bahasa Inggeris supaya dapat menikmati kandungan mengenai produk, perkhidmatan dan teknologi Microsoft. Artikel ini mungkin mengandungi ralat perbendaharaan kata, sintaks atau tatabahasa kerana ia diterjemahkan oleh mesin.

Kembangkan kemahiran anda
Jelajahi latihan
Dapatkan ciri baru terlebih dahulu
Sertai Office Insiders

Adakah maklumat ini membantu?

Terima kasih atas maklum balas anda!

Terima kasih atas maklum balas anda! Nampaknya ia mungkin akan membantu untuk menyambungkan anda kepada salah seorang daripada ejen sokongan Office kami.

×