Roger đã hỏi liệu có cách nào để tính toán khoảng thời gian giữa các lần xuất hiện của các giá trị trong danh sách không. Ví dụ: anh ta có vài nghìn số trong cột A. Nhìn vào giá trị trong ô A351, lần cuối cùng giá trị đó xuất hiện trong danh sách là ở ô A246. Anh ấy muốn một công thức có thể được đặt trong ô B351 và trả về 105, sự khác biệt giữa 351 và 246.

Cách tiếp cận này khó thực hiện trong Excel vì Excel không giỏi tìm kiếm ngược – lên một cột. Nếu tiền đề có thể được đảo ngược, thì nhiệm vụ trở nên đơn giản hơn nhiều. Ví dụ: nếu một công thức trong ô B246 có thể trả về giá trị 105, cho biết khoảng thời gian cho đến lần xuất hiện tiếp theo của giá trị trong ô A246, thay vì tính lần xuất hiện cuối cùng. Công thức sau đây tính toán lần xuất hiện tiếp theo của giá trị trong ô A1:

=MATCH(A1,A2:$A$65536,0)

Đặt công thức này vào ô B1 và ​​sao chép nó xuống tuy nhiên cần phải có nhiều ô. Nếu giá trị trong cột A không xuất hiện lại trong cột, thì công thức trả về lỗi # N / A. Nếu bạn muốn có công thức trả về 0, thì các công việc sau đây:

=IF(ISNA(MATCH(A1,A2:$A$65536,0)),0,MATCH(A1,A2:$A$65536,0))

Nếu bạn hoàn toàn phải đếm ngược (tìm lần xuất hiện trước thay vì lần xuất hiện tiếp theo), thì cách dễ nhất để làm điều đó là với một hàm do người dùng xác định. Hàm sau đây, RowInterval, sẽ xem xét ngược lại qua một dải mà bạn chỉ định và trả về khoảng mong muốn:

Function RowInterval(TestCell As Range, LookHere As Range) As Long     Dim varValue As Variant     Dim lngRow As Long

Application.Volatile     varValue = TestCell.Value          'Check for occurrences of the test value in the search range     If WorksheetFunction.CountIf(LookHere, varValue) > 0 Then         With LookHere             'Get the last row of the search range             lngRow = .Row + .Rows.Count - 1             'Start with the last cell in the search range and work up             Do Until .Item(lngRow, 1).Value = varValue                 lngRow = lngRow - 1             Loop         End With         'Subtract the number of the row containing the found occurrence         'from the number of the row containing the test value         RowInterval = TestCell.Row - lngRow     End If End Function

Để sử dụng hàm, bạn sẽ đặt công thức sau vào ô B2, sau đó sao chép công thức xuống số ô mong muốn:

=RowInterval(A2,A$1:A1)

_Lưu ý: _

Nếu bạn muốn biết cách sử dụng các macro được mô tả trên trang này (hoặc trên bất kỳ trang nào khác trên trang ExcelTips), tôi đã chuẩn bị một trang đặc biệt bao gồm thông tin hữu ích.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (10258) áp dụng cho Microsoft Excel 2007, 2010, 2013, 2016, 2019 và Excel trong Office 365. Bạn có thể tìm thấy phiên bản của mẹo này cho giao diện menu cũ hơn của Excel tại đây: