Tra cứu bản sửa đổi tệp cuối cùng

{= MAX ( IF ( ISERROR ( SEARCH (H5&'*',files)),0, ROW (files)- ROW ( INDEX (files,1,1))+1))}

Để tìm vị trí (hàng) của bản sửa đổi tệp cuối cùng trong bảng, bạn có thể sử dụng công thức dựa trên một số hàm Excel: MAX, IF, ISERROR, ROW và INDEX.

Trong ví dụ được hiển thị, công thức trong ô H6 là:

cách tính lương làm thêm giờ trong excel

{= MAX (IF (ISERROR (TÌM KIẾM (H5 & '*', tệp)), 0, ROW (tệp) -ROW (INDEX (tệp, 1,1)) + 1))}

trong đó 'tệp' là dải ô được đặt tên C4: C11.

Lưu ý: đây là công thức mảng và phải được nhập bằng control + shift + enter.

Định nghĩa bài văn

Trong ví dụ này, chúng tôi có một số phiên bản tệp được liệt kê trong bảng với ngày tháng và tên người dùng. Lưu ý rằng tên tệp được lặp lại, ngoại trừ mã được thêm vào cuối để đại diện cho phiên bản ('CA', 'CB', 'CC', 'CD', v.v.).

Đối với một tệp nhất định, chúng tôi muốn xác định vị trí (số hàng) cho lần sửa đổi cuối cùng. Đây là một vấn đề phức tạp, vì mã phiên bản ở cuối tên tệp khiến việc khớp với tên tệp khó hơn. Ngoài ra, theo mặc định, các công thức so khớp trong Excel sẽ trả về kết quả phù hợp đầu tiên, không phải kết quả phù hợp cuối cùng, vì vậy chúng ta cần giải quyết thử thách đó bằng một số kỹ thuật phức tạp.

Ở cốt lõi của công thức này, chúng tôi xây dựng một danh sách các số hàng cho một tệp nhất định. Sau đó, chúng tôi sử dụng hàm MAX để lấy số hàng lớn nhất, tương ứng với bản sửa đổi cuối cùng (lần xuất hiện cuối cùng) của tệp đó.

Để tìm tất cả các lần xuất hiện của một tệp nhất định, chúng tôi sử dụng chức năng TÌM KIẾM, được định cấu hình với ký tự đại diện dấu hoa thị (*) để khớp với tên tệp, bỏ qua mã phiên bản. TÌM KIẾM sẽ xuất hiện lỗi VALUE khi không tìm thấy văn bản, vì vậy chúng tôi đưa tìm kiếm vào ISERROR:

 
 ISERROR ( SEARCH (H5&'*',files))

Điều này dẫn đến một mảng các giá trị TRUE và FALSE như sau:

{FALSETRUEFALSEFALSETRUETRUEFALSETRUE}

cho mỗi ô trong phạm vi vba

Thật khó hiểu, nhưng TRUE biểu thị một lỗi (không tìm thấy văn bản) và FALSE biểu thị một kết quả trùng khớp. Kết quả mảng này được đưa vào hàm IF dưới dạng kiểm tra logic. Đối với giá trị nếu TRUE, chúng tôi sử dụng số 0 và đối với giá trị nếu đúng, chúng tôi cung cấp mã này, tạo ra số hàng tương đối cho phạm vi mà chúng tôi đang làm việc với:

 
 ROW (files)- ROW ( INDEX (files,1,1))+1)

Sau đó, hàm IF trả về một mảng giá trị như sau:

{10340070}

Tất cả các số ngoại trừ số 0 đại diện cho kết quả khớp với 'tên tệp1' - tức là số hàng bên trong phạm vi được đặt tên 'tệp' nơi 'tên tệp1' xuất hiện.

Cuối cùng, chúng ta sử dụng hàm MAX để nhận giá trị lớn nhất trong mảng này, trong ví dụ này là 7.

Sử dụng INDEX với số hàng này để truy xuất thông tin liên quan đến bản sửa đổi cuối cùng (tức là tên tệp đầy đủ, ngày tháng, người dùng, v.v.).

Không có phạm vi được đặt tên

Dải ô được đặt tên giúp thiết lập công thức phức tạp hơn nhanh chóng và dễ dàng, vì bạn không phải nhập địa chỉ ô bằng tay. Tuy nhiên, trong trường hợp này, chúng tôi đang sử dụng một hàm bổ sung (INDEX) để lấy ô đầu tiên của dải ô được đặt tên là 'tệp', điều này làm phức tạp mọi thứ một chút. Nếu không có phạm vi được đặt tên, công thức sẽ giống như sau:

 
{= MAX ( IF ( ISERROR ( SEARCH (H5&'*',C4:C11)),0, ROW (C4:C11)- ROW (C4)+1))}