Formula generik
(=MAX(IF(ISERROR(SEARCH(H5&"*",files)),0,ROW(files)-ROW(INDEX(files,1,1))+1)))
Ringkasan
Untuk mencari kedudukan (baris) semakan fail terakhir dalam jadual, anda boleh menggunakan formula berdasarkan beberapa fungsi Excel: MAX, IF, ISERROR, ROW, dan INDEX.
Dalam contoh yang ditunjukkan, formula dalam sel H6 adalah:
(= MAX (JIKA (ISERROR (CARI (H5 & "*", fail)), 0, ROW (fail) -ROW (INDEX (fail, 1,1)) + 1)))
di mana "fail" adalah julat bernama C4: C11.
Catatan: ini adalah formula larik dan mesti dimasukkan dengan kawalan + shift + enter.
Konteks
Dalam contoh ini, kami mempunyai sebilangan versi fail yang disenaraikan dalam jadual dengan tarikh dan nama pengguna. Perhatikan bahawa nama fail diulang, kecuali kod yang ditambahkan pada akhir untuk mewakili versi ("CA", "CB", "CC", "CD", dll.).
Untuk fail yang diberikan, kami ingin mencari kedudukan (nombor baris) untuk semakan terakhir. Ini adalah masalah yang sukar, kerana kod versi di akhir nama fail menjadikannya lebih sukar untuk dicocokkan pada nama fail. Juga, secara lalai, formula padanan Excel akan mengembalikan perlawanan pertama, bukan perlawanan terakhir, jadi kita perlu mengatasi cabaran itu dengan beberapa teknik yang sukar.
Penjelasan
Pada inti formula ini, kami membina senarai nombor baris untuk fail tertentu. Kemudian kami menggunakan fungsi MAX untuk mendapatkan nombor baris terbesar, yang sepadan dengan semakan terakhir (kejadian terakhir) fail tersebut.
Untuk mencari semua kejadian fail tertentu, kami menggunakan fungsi SEARCH, dikonfigurasi dengan wildcard asterisk (*) agar sesuai dengan nama file, mengabaikan kod versi. SEARCH akan membuang NILAI ralat apabila teks tidak dijumpai, jadi kami membungkus carian di ISERROR:
ISERROR(SEARCH(H5&"*",files))
Ini menghasilkan pelbagai nilai BENAR dan SALAH seperti ini:
(SALAH; BENAR; SALAH; SALAH; BENAR; BENAR; SALAH; BENAR)
Ia membingungkan, tetapi BENAR mewakili ralat (teks tidak dijumpai), dan SALAH mewakili kecocokan. Hasil array ini dimasukkan ke dalam fungsi IF sebagai ujian logik. Untuk nilai jika BENAR, kami menggunakan sifar, dan untuk nilai jika benar, kami menyediakan kod ini, yang menghasilkan nombor baris relatif untuk julat yang kami bekerjasama:
ROW(files)-ROW(INDEX(files,1,1))+1)
Fungsi IF kemudian mengembalikan pelbagai nilai seperti ini:
(1; 0; 3; 4; 0; 0; 7; 0)
Semua nombor kecuali sifar mewakili perlawanan untuk "nama file1" - iaitu nombor baris di dalam julat bernama "fail" di mana "nama fail1" muncul.
Akhirnya, kami menggunakan fungsi MAX untuk mendapatkan nilai maksimum dalam array ini, iaitu 7 dalam contoh ini.
Gunakan INDEX dengan nombor baris ini untuk mendapatkan maklumat yang berkaitan dengan semakan terakhir (iaitu nama fail penuh, tarikh, pengguna, dll).
Tanpa julat bernama
Julat bernama menjadikannya cepat dan mudah untuk menyusun formula yang lebih kompleks, kerana anda tidak perlu memasukkan alamat sel dengan tangan. Namun, dalam kes ini, kami menggunakan fungsi tambahan (INDEX) untuk mendapatkan sel pertama dari rentang bernama "fail", yang sedikit menyulitkan. Tanpa julat yang dinamakan, formula seperti ini:
(=MAX(IF(ISERROR(SEARCH(H5&"*",C4:C11)),0,ROW(C4:C11)-ROW(C4)+1)))
