Formula umum
(=MAX(IF(ISERROR(SEARCH(H5&"*",files)),0,ROW(files)-ROW(INDEX(files,1,1))+1)))
Ringkasan
Untuk menemukan posisi (baris) dari revisi file terakhir dalam sebuah tabel, Anda dapat menggunakan rumus yang didasarkan pada beberapa fungsi Excel: MAX, IF, ISERROR, ROW, dan INDEX.
Dalam contoh yang diperlihatkan, rumus di sel H6 adalah:
(= MAX (IF (ISERROR (SEARCH (H5 & "*", files)), 0, ROW (files) -ROW (INDEX (files, 1,1)) + 1)))
di mana "file" adalah rentang bernama C4: C11.
Catatan: ini adalah rumus array dan harus dimasukkan dengan control + shift + enter.
Konteks
Dalam contoh ini, kami memiliki sejumlah versi file yang tercantum dalam tabel dengan tanggal dan nama pengguna. Perhatikan bahwa nama file diulang, kecuali untuk kode yang ditambahkan di akhir untuk mewakili versi ("CA", "CB", "CC", "CD", dll.).
Untuk file tertentu, kami ingin mencari posisi (nomor baris) untuk revisi terakhir. Ini adalah masalah yang rumit, karena kode versi di akhir nama file membuat nama file lebih sulit untuk dicocokkan. Selain itu, secara default, rumus kecocokan Excel akan mengembalikan kecocokan pertama, bukan kecocokan terakhir, jadi kita perlu mengatasi tantangan itu dengan beberapa teknik yang rumit.
Penjelasan
Inti dari rumus ini, kami membuat daftar nomor baris untuk file tertentu. Kemudian kami menggunakan fungsi MAX untuk mendapatkan nomor baris terbesar, yang sesuai dengan revisi terakhir (kemunculan terakhir) dari file itu.
Untuk menemukan semua kemunculan file tertentu, kami menggunakan fungsi SEARCH, dikonfigurasi dengan wildcard asterisk (*) untuk mencocokkan nama file, mengabaikan kode versi. SEARCH akan memunculkan kesalahan NILAI ketika teks tidak ditemukan, jadi kami membungkus pencarian di ISERROR:
ISERROR(SEARCH(H5&"*",files))
Ini menghasilkan larik nilai TRUE dan FALSE seperti ini:
(SALAH; BENAR; SALAH; SALAH; BENAR; BENAR; SALAH; BENAR)
Ini membingungkan, tetapi TRUE menunjukkan kesalahan (teks tidak ditemukan), dan FALSE menunjukkan kecocokan. Hasil larik ini dimasukkan ke dalam fungsi IF sebagai pengujian logika. Untuk nilai jika TRUE, kami menggunakan nol, dan untuk nilai jika benar, kami menyediakan kode ini, yang menghasilkan nomor baris relatif untuk rentang yang kami kerjakan:
ROW(files)-ROW(INDEX(files,1,1))+1)
Fungsi IF kemudian mengembalikan larik nilai seperti ini:
(1; 0; 3; 4; 0; 0; 7; 0)
Semua angka kecuali nol mewakili kecocokan untuk "namafile1" - yaitu nomor baris di dalam rentang bernama "file" di mana "namafile1" muncul.
Terakhir, kami menggunakan fungsi MAX untuk mendapatkan nilai maksimum dalam larik ini, yaitu 7 dalam contoh ini.
Gunakan INDEX dengan nomor baris ini untuk mengambil informasi yang berhubungan dengan revisi terakhir (yaitu nama file lengkap, tanggal, pengguna, dll).
Tanpa rentang bernama
Rentang bernama mempercepat dan mempermudah penyiapan rumus yang lebih kompleks, karena Anda tidak perlu memasukkan alamat sel secara manual. Namun, dalam kasus ini, kami menggunakan fungsi tambahan (INDEX) untuk mendapatkan sel pertama dari rentang bernama "file", yang sedikit memperumit masalah. Tanpa rentang bernama, rumusnya terlihat seperti ini:
(=MAX(IF(ISERROR(SEARCH(H5&"*",C4:C11)),0,ROW(C4:C11)-ROW(C4)+1)))
