Formula generik
=LOOKUP(2,1/(ISNUMBER(FIND(filename,range))),range)
Ringkasan
Untuk mencari versi fail terkini dalam senarai, anda boleh menggunakan formula berdasarkan fungsi LOOKUP bersama fungsi ISNUMBER dan FIND. Dalam contoh yang ditunjukkan, formula dalam sel G7 adalah:
=LOOKUP(2,1/(ISNUMBER(FIND(G6,files))),files)
di mana "fail" adalah julat bernama B5: B11.
Konteks
Dalam contoh ini, kami mempunyai sebilangan versi fail yang disenaraikan dalam jadual dengan tarikh dan nama pengguna. Perhatikan bahawa nama fail diulang dengan penghitung di akhir sebagai nombor semakan - 001, 002, 003, dll.
Dengan nama fail, kami ingin mendapatkan semula nama semakan terakhir atau terakhir. Terdapat dua cabaran:
- Tantangannya ialah kod versi di akhir nama fail menjadikannya lebih sukar untuk dipadankan dengan nama fail.
- Secara lalai, formula padanan Excel akan mengembalikan perlawanan pertama, bukan perlawanan terakhir.
Untuk mengatasi cabaran ini, kita perlu menggunakan beberapa teknik yang sukar.
Penjelasan
Formula ini menggunakan fungsi LOOKUP untuk mencari dan mengambil nama fail yang hampir sama. Nilai carian adalah 2, dan lookup_vector dibuat dengan ini:
1/(ISNUMBER(FIND(G6,files)))
Di dalam coretan ini, fungsi FIND mencari nilai dalam G6 di dalam julat bernama "file" (B5: B11). Hasilnya adalah susunan seperti ini:
(1;#VALUE!;1;1;#VALUE!;#VALUE!;1)
Di sini, nombor 1 mewakili kecocokan, dan ralat # NILAI mewakili nama fail yang tidak sepadan. Susunan ini masuk ke fungsi ISNUMBER dan keluar seperti ini:
(TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE)
Nilai ralat sekarang SALAH, dan nombor 1 sekarang BENAR. Ini mengatasi cabaran # 1, kita sekarang mempunyai susunan yang menunjukkan dengan jelas fail mana dalam senarai yang mengandungi nama fail yang diminati.
Seterusnya, array digunakan sebagai penyebut dengan 1 sebagai pengangka. Hasilnya kelihatan seperti ini:
(1;#DIV/0!;1;1;#DIV/0!;#DIV/0!;1)
yang menjadi LOOKUP sebagai lookup_vector. Ini adalah penyelesaian yang sukar untuk mencabar # 2. Fungsi LOOKUP hanya beroperasi dalam mod padanan anggaran, dan secara automatik mengabaikan nilai ralat. Ini bererti dengan 2 sebagai nilai pencarian, VLOOKUP akan berusaha mencari 2, gagal, dan mundur ke angka sebelumnya (dalam kes ini sepadan dengan 1 terakhir di kedudukan 7). Akhirnya, LOOKUP menggunakan 7 seperti indeks untuk mengambil fail ke-7 dalam senarai fail.
Mengendalikan carian kosong
Anehnya, fungsi FIND mengembalikan 1 jika nilai pencarian adalah rentetan kosong (""). Untuk mengelakkan perlawanan palsu, anda boleh membungkus formula dalam JIKA dan menguji carian kosong:
=IF(G6"",LOOKUP(2,1/(ISNUMBER(FIND(G6,files))),files),"")
