VLOOKUP boleh guna semula (VLOOKUP)

Kami mempunyai senarai pesanan dengan nombor dan nama barang. Saya ingin, sebagai contoh, untuk menarik keluar dari meja dengan nombor pesanan semua barang yang termasuk di dalamnya. Lebih kurang begini:

Ciri yang mengagumkan VLOOKUP (VLOOKUP) dalam keadaan sedemikian ia hanya akan membantu sebahagiannya, kerana dapat mengekstrak data hanya dengan padanan pertama yang ditemui, iaitu hanya akan memberi kita Apples. Untuk mencari dan mengekstrak semua item daripada jadual, lebih baik menggunakan formula tatasusunan. Suka yang ini:

=INDEKS($B$2:$B$16;KELEBIHAN(IF($E$2=A2: A16;LINE(B2:B16)-1;»»);LINE()-5))

Ia mesti dimasukkan seperti berikut:

1. pilih sel di mana keputusan harus dipaparkan (dalam contoh kami, ini ialah julat D6:D20)
2. masukkan (salin formula ke dalam sel pertama) julat
3. tekan Ctrl + Beralih + Masukkan

Penolakan unit dalam serpihan STRING(B2:B16)-1 dilakukan kerana pengepala jadual. Atas sebab yang sama, untuk mengimbangi anjakan dalam julat yang terhasil berbanding dengan yang asal, nombor lima dalam serpihan ditolak STRING()-5

Untuk menyembunyikan #NUM! ralat yang akan muncul dalam sel kosong dalam julat yang terhasil D6:D20, anda boleh menggunakan fungsi semakan ralat IF dan EOSH, menggantikan formula kami dengan yang lebih kompleks sedikit:

=IF(EOSH(ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5)));»»;ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5)))

Dalam Excel 2007, fungsi IFERROR yang lebih mudah muncul – ia membolehkan anda menyelesaikan masalah dengan lebih padat:

=IFERROR(ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5));»»)

PS

Dalam versi bahasa Inggeris Excel, fungsi ini akan kelihatan seperti ini:

=INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5))

=IF(ISERR(INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5))),»»,INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5)))

=IFERROR(INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5)),»»)

• Menggunakan Fungsi VLOOKUP untuk Mencari Data dalam Jadual
• Versi peningkatan fungsi VLOOKUP2 yang boleh mencari dalam mana-mana lajur dan bukan hanya nilai pertama
• VLOOKUP2 dan VLOOKUP3 berfungsi daripada alat tambah PLEX
• Apakah formula tatasusunan dan untuk apa ia digunakan?