Sekiranya anda sudah lama membaca petua Excel, anda selalu menemui seseorang yang bercakap mengenai penggunaan fungsi Excel INDEX () & MATCH () dan bukannya Excel VLOOKUP. Bercakap untuk diri saya sendiri, selalu sukar untuk mencuba dan menguasai DUA fungsi baru secara serentak. Tetapi, ini adalah muslihat yang hebat. Beri saya lima minit dan saya akan cuba menerangkannya dalam bahasa Inggeris yang mudah.
Ulasan 30 saat VLOOKUP
Katakan anda mempunyai jadual rekod pekerja. Lajur pertama adalah nombor pekerja, dan lajur selebihnya adalah pelbagai data mengenai pekerja. Bila-bila masa anda mempunyai nombor pekerja di lembaran kerja, anda boleh menggunakan VLOOKUP untuk mengembalikan data tertentu mengenai pekerja tersebut. Sintaksnya adalah VLOOKUP (nilai, julat data, col. No, FALSE). Ia mengatakan kepada Excel, "Pergi ke rentang data. Cari baris yang mempunyai (nilai) di lajur pertama julat data. Kembalikan nilai (col no.) Dari baris itu. Setelah anda mengetahui, ia sangat ringkas dan hebat.
Masalah
Suatu hari, anda mempunyai situasi di mana anda mempunyai nama pekerja, tetapi memerlukan nombor pekerja. Dalam gambar berikut, anda mempunyai nama di A10 dan perlu mencari nombor pekerja di B10.
Apabila medan kunci berada di sebelah kanan data yang ingin diambil, VLOOKUP tidak akan berfungsi. Sekiranya hanya VLOOKUP yang akan menerima -1 sebagai nombor lajur, tidak akan ada masalah. Tetapi, tidak. Salah satu penyelesaian yang biasa adalah dengan memasukkan lajur A baru untuk sementara waktu, menyalin lajur nama ke lajur A baru, mengisi dengan VLOOKUP, Tampal Nilai Khas, kemudian hapus lajur sementara A. Pro Excel mungkin dapat melakukan gerakan ini ketika mereka tidur.
Saya akan mencadangkan anda menyahut cabaran dan cuba menggunakan kaedah satu langkah ini. Ya, anda mesti meletakkan formula di dinding anda selama beberapa minggu, tetapi anda melakukannya dengan VLOOKUP suatu masa dahulu juga, bukan?
Saya rasa sebab ini sangat sukar adalah kerana anda menggunakan dua fungsi yang mungkin tidak pernah anda gunakan sebelumnya. Oleh itu, izinkan saya memecahnya menjadi dua bahagian.
Pertama, terdapat fungsi INDEX (). Ini adalah fungsi yang sangat mengerikan. Apabila seseorang mengatakan "indeks", ia tidak memunculkan apa-apa dalam fikiran saya yang serupa dengan fungsi ini. Indeks memerlukan tiga hujah.
=INDEX(data range, row number, column number)
Dalam bahasa Inggeris, Excel masuk ke julat data dan mengembalikan nilai pada persimpangan baris nombor (nombor nombor) dan lajur nombor nombor (baris nombor). Hei, fikirkanlah - ini cukup mudah, bukan? =INDEX($A$2:$C$6,4,2)akan memberi anda nilai dalam B5.
Memohon INDEX () kepada masalah kami, anda boleh memikirkan bahawa untuk kembali jumlah pekerja daripada julat, anda akan menggunakan ini: =INDEX($A$2:$A$6,?,1). Sebenarnya, sekeping ini kelihatan sangat remeh sehingga nampak tidak berguna. Tetapi, apabila anda mengganti tanda tanya dengan fungsi MATCH (), anda mempunyai jalan penyelesaian.
Inilah sintaksnya:
=MATCH(Value, Single-column data range, FALSE)
Ini memberitahu Excel, "Cari julat data dan beritahu saya nombor baris relatif di mana anda mencari padanan untuk (data). Jadi, untuk mencari baris mana yang mempunyai pekerja di A10, anda akan menggunakan =MATCH(A10,$B$2:$B$6,FALSE). Ya, ini lebih kompleks daripada Indeks , tetapi semestinya tepat di lorong pro VLOOKUP. Sekiranya A10 mengandungi "Miller, Bob" maka PERTANDINGAN ini akan mengembalikan bahawa dia berada di barisan ke-3 julat B2: B6.
Itu ada - fungsi MATCH () memberitahu fungsi Index baris mana yang hendak dilihat - anda sudah selesai. Ikuti fungsi Indeks, ganti tanda tanya kami dengan fungsi MATCH, dan anda kini boleh melakukan setara dengan VLOOKUPs ketika medan kunci tidak ada di lajur kiri. Berikut adalah fungsi untuk digunakan:
=INDEX($A$2:$A$6,MATCH(A10,$B$2:$B$6,FALSE),1)
Nota melekit di dinding saya sebenarnya menunjukkannya sebagai dua garis. Mula-mula saya menulis penjelasan untuk MATCH (). Di bawah ini saya menulis penjelasan untuk INDEX (). Saya kemudian melukis corong antara keduanya untuk menunjukkan bahawa fungsi MATCH () jatuh ke argumen ke-2 fungsi INDEX ().
Beberapa kali pertama saya perlu melakukan ini, saya tergoda untuk membanting lajur sementara A yang baru di sana, tetapi saya terpaksa menahannya dengan cara ini. Ia lebih pantas, dan memerlukan manipulasi yang lebih sedikit. Oleh itu, pada masa berikutnya anda berharap dapat memasukkan nombor negatif dalam fungsi VLOOKUP, cubalah kombinasi INDEX dan MATCH yang pelik ini untuk menyelesaikan masalah anda.
