Brian có một dãy số với 240 ô. Trong hàng này, các con số đang giảm dần và cuối cùng, tại một thời điểm nào đó trong 240 ô đó, trở thành 0. Các số 0 sẽ tiếp tục điền vào các ô còn lại trong hàng. Brian cần viết một phương trình sẽ trả về giá trị khác 0 cuối cùng trong hàng.

Có nhiều cách khác nhau để có thể trả lại giá trị mong muốn.

(Không phải lúc nào điều đó cũng xảy ra với Excel? Bạn có thể nghĩ ra rất nhiều cách để nhận được kết quả.) Nói chung, bạn có thể sử dụng công thức thông thường hoặc công thức mảng.

Nếu bạn muốn sử dụng công thức thông thường, đây là công thức bạn có thể thử:

=OFFSET(A6,0,(COUNT(A6:IF6)-COUNTIF(A6:IF6,0))-1)

Hàm COUNTIF đếm số lượng giá trị 0 và hàm COUNT xác định số ô trong phạm vi. Trừ một giá trị khác và điều chỉnh 1 sẽ cho giá trị OFFSET vào “mảng” các ô có giá trị khác 0 cuối cùng nằm. Công thức này giả định rằng các giá trị bắt đầu trong cột A; nếu chúng bắt đầu trong một cột khác thì bạn sẽ cần điều chỉnh giá trị được cung cấp bởi phần COUNT / COUNTIF của công thức để thể hiện phần bù từ cột đầu tiên.

Đây là một biến thể ngắn hơn của công thức, dựa trên việc thực hiện bù trừ từ phía bên phải của phạm vi thay vì phía bên trái:

=OFFSET(IF6,0,-COUNTIF(A6:IF6,0))

Trong trường hợp này, điều quan trọng là IF6 phải là đầu bên phải thực tế của phạm vi. Công thức hoạt động bằng cách đếm số lượng giá trị 0 trong phạm vi (tất cả đều ở phía bên phải của phạm vi) và sau đó tính toán địa chỉ ô của ô cuối cùng (IF6) trừ đi số lượng không.

Đây là phiên bản sử dụng hàm INDEX, thay vào đó:

=INDEX(A6:IF6,,MATCH(0,A6:IF6,0)-1)

Phiên bản này thậm chí còn ngắn hơn, sử dụng hàm LOOKUP:

=LOOKUP(1,1/(6:6>0),6:6)

Công thức mảng cũng có thể được sử dụng. (Công thức mảng được nhập bằng cách nhấn Ctrl + Shift + Enter.) Công thức này sử dụng hàm INDIRECT:

=INDIRECT("R6C" & MAX((A6:IF6>0)*COLUMN(A6:IF6)),FALSE)

Công thức mảng này sử dụng một triển khai thú vị của hàm LOOKUP để tìm kết quả chính xác:

=LOOKUP(9.99999999999999E+307,IF(A6:IF6<>0,A6:IF6))

Đây là một công thức mảng khác có thể được sử dụng, lần này sử dụng hàm OFFSET để tìm giá trị khác 0 cuối cùng trong hàng 6:

=OFFSET(A6,0,MIN(IF(6:6=0,COLUMN(6:6),300))-2)

Đây là một biến thể thậm chí còn ngắn hơn:

=MIN(IF(A6:IF6>0,A6:IF6))

Tất cả các công thức được trình bày cho đến nay đều phụ thuộc vào thực tế là các số trong hàng thực sự giảm – chúng đi từ bất kỳ số nào bắt đầu và dần dần đi về 0. Nếu các con số không giảm, thì bạn có thể sử dụng một loại công thức mảng khác để xác định giá trị khác 0 cuối cùng trong hàng:

=INDEX(6:6,MAX(IF(A6:IF6<>0,COLUMN(A6:IF6))))

Đầu tiên, công thức xác định cột lớn nhất trong hàng (trong trường hợp này là hàng 6) có giá trị không bằng 0, sau đó nó sử dụng hàm INDEX để lấy giá trị từ cột đó trong hàng đó.

Như bạn có thể nói, có khá nhiều cách để tìm giá trị khác 0 cuối cùng trong một hàng. Chọn một trong những điều mà bạn yêu thích; không có đúng hay sai trong trường hợp này.

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

Mẹo này (11250) áp dụng cho Microsoft Excel 2007, 2010, 2013 và 2016.