Cedric cần xác định giá trị của ô từ thứ hai đến ô cuối cùng trong cột mà anh ta thường xuyên thêm giá trị vào đó. Anh ta đã thấy các công thức để lấy giá trị cuối cùng, nhưng không phải giá trị thứ hai đến cuối cùng.

Có nhiều công thức bạn có thể tiếp cận nhu cầu này. Theo nhiều khía cạnh, phương pháp bạn chọn sẽ phụ thuộc vào đặc điểm của dữ liệu trong cột. Giả sử rằng dữ liệu của bạn nằm trong cột A, là số và không có ô trống trong bất kỳ ô nào trong cột. Trong trường hợp đó, bạn có thể sử dụng công thức sau:

=INDEX(A:A,COUNT(A:A)-1)

Lưu ý rằng công thức này sẽ không trả về kết quả chính xác nếu có bất kỳ giá trị văn bản nào trong cột A — ngay cả khi giá trị văn bản đó nằm trong tiêu đề. Nếu bạn muốn bù cho tiêu đề, bạn có thể sửa đổi công thức theo cách này. (Lưu ý rằng nó chỉ kiểm tra phạm vi A2: A1000. Nếu dữ liệu của bạn vượt quá hàng 1.000, thì bạn sẽ cần phải sửa đổi phạm vi được chỉ định.)

=INDEX(A2:A1000,COUNT(A2:A1000)-1)

Nếu dữ liệu số của bạn có thể chứa các ô trống, thì bất kỳ công thức nào sau đây sẽ hoạt động tốt:

=OFFSET(INDIRECT("A"&MATCH(9^9,A:A)),-1,0)

=INDEX(A:A,MATCH(9.9E+23,A:A,1)-1,1)

=INDEX(A:A,(MATCH(LOOKUP(10000,A:A),A:A)-1),1)

=INDEX(A:A,AGGREGATE(14,6,ROW(A:A)*A:A/A:A,2))

Bạn nên nhận ra rằng nếu ô từ thứ hai đến ô cuối cùng trong cột trống, thì các công thức này sẽ trả về 0. (Chà, ngoại trừ công thức sử dụng hàm AGGREGATE. Nó trả về giá trị trong ô trống đầu tiên trước đó ô thứ hai đến ô cuối cùng.)

Nếu dữ liệu của bạn trong cột A có thể chứa số hoặc văn bản (nhưng vẫn không có ô trống), thì bạn cần một công thức khác. Một trong hai cách này sẽ làm được:

=INDEX(A:A,COUNTA(A:A)-1,1)

=INDIRECT("A"&COUNTA(A:A)-1)

Nếu có thể có ô trống trong dữ liệu của bạn, thì bạn có thể sử dụng một trong hai cách sau:

=INDEX(A:A,MATCH(LOOKUP(2,1/(A:A<>""),A:A),A:A,0)-1)

=INDEX(A:A,LOOKUP(2,1/(A:A<>""),ROW(A:A))-1)

Một lần nữa, nếu ô thứ hai đến cuối cùng trống, thì công thức trả về 0.

Cuối cùng, bạn cũng có thể sử dụng loại công thức sau, miễn là các ô của bạn trong cột sử dụng một dải ô đã đặt tên (trong trường hợp này là MyData).

Tuy nhiên, hãy nhớ rằng khi bạn thêm nhiều thông tin hơn vào cột, bạn sẽ cần đảm bảo rằng phạm vi được đặt tên bao gồm dữ liệu đã thêm.

=INDEX(MyData,ROWS(MyData)-1)

Bạn nên lưu ý rằng hầu như tất cả các công thức được đề cập trong mẹo này đều sử dụng -1 trong đó. Điều này cho biết bạn không muốn ô cuối cùng trong phạm vi, mà là ô thứ hai đến cuối cùng (“một lên” từ cuối phạm vi).

Nếu bạn muốn một khoảng chênh lệch khác với phần dưới cùng của phạm vi, bạn có thể thay đổi phần này của mỗi công thức. Ví dụ: nếu bạn muốn có 2 ô từ dưới cùng của dải ô, bạn sẽ thay đổi tất cả các trường hợp của -1 thành -2.

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

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