Stuart có một loạt các bài đọc trong một trang tính. Trong cột đầu tiên, anh ta có ngày tháng liên quan đến các số đọc và trong cột thứ hai, anh ta có các số đọc thực tế. Stuart muốn có một công thức sẽ trả về ngày đầu tiên mà tại đó số đọc trở thành số âm. Nói cách khác, công thức phải tìm giá trị đầu tiên là số âm trong cột thứ hai và sau đó trả về ngày được kết hợp với giá trị đó.

Có thể có nhiều giá trị âm trong cột thứ hai, nhưng anh ta chỉ cần ngày được kết hợp với giá trị âm đầu tiên.

Có một số cách để tiếp cận vấn đề này. Tất cả các phương pháp đều giả định rằng các ngày trong cột A theo thứ tự tăng dần và các kết quả đọc trong cột B không theo bất kỳ loại thứ tự nào có thể nhận biết được. (Nói cách khác, các bài đọc có thể tăng lên trên và dưới 0 vào bất kỳ ngày nào.)

Với điều kiện bạn có một số quyền kiểm soát bố cục của trang tính, bạn có thể thêm một cột công việc trung gian trong cột C, được sử dụng để cho biết khi nào một giá trị âm. Chỉ cần đặt một công thức như thế này trong cột C, ở bên phải của mỗi bài đọc:

=IF(B1<0,A1,"")

Công thức này trả về ngày trong cột A nếu giá trị trong B dưới 0 (âm), nếu không, nó không trả về giá trị nào. Tất cả những gì bạn cần làm là tìm giá trị nhỏ nhất trong cột C:

=MIN(C:C)

Định dạng kết quả dưới dạng ngày và nó đại diện cho ngày mà tại đó các kết quả đọc đầu tiên trở thành số âm.

Một cách tiếp cận khác là bỏ qua việc sử dụng cột trung gian và sử dụng công thức mảng để xác định ngày. Giả sử dữ liệu nằm trong phạm vi A1: B42, bạn có thể sử dụng bất kỳ công thức nào sau đây:

=MIN(IF(B1:B42<0,A1:A42,""))

=OFFSET($A$1,MATCH(TRUE,$B$1:$B$42<0,0)-1,,,)

=INDEX(A:A,MIN(IF(B1:B42<0,ROW(B1:B42))))

=INDEX(A1:A42,MATCH(TRUE,B1:B42<0,0))

=INDIRECT("A"&MIN(IF(B1:B42<0,ROW(B1:B42))),TRUE)

Hãy nhớ rằng đây là tất cả các công thức mảng, vì vậy bạn cần nhập bất kỳ công thức nào bạn chọn bằng cách nhấn Shift + Ctrl + Enter. Định dạng kết quả dưới dạng ngày tháng và đó là câu trả lời bạn tìm kiếm.

Nếu muốn, bạn cũng có thể sử dụng một macro đơn giản để xác định ngày:

Function GetFirstNegative(rngdata)

Dim c As Variant

For Each c In rngdata         If c < 0 Then             GetFirstNegative = c.Offset(0, -1)

Exit Function         Else             GetFirstNegative = "All Data is Positive"

End If     Next End Function

Trong trang tính của bạn, bạn sẽ sử dụng hàm do người dùng định nghĩa này theo cách sau:

=GetFirstNegative(B1:B42)

_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 (12321) áp dụng cho Microsoft Excel 2007, 2010, 2013 và 2016.

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: