Có thể đôi khi bạn đang tập hợp một sổ làm việc và bạn muốn giữ một bản tóm tắt trên một trang tính và thông tin chi tiết trên một trang tính khác.

Ví dụ: giả sử Sheet1 là trang tính tóm tắt của bạn và bạn có thông tin chi tiết cho các tài khoản ngân hàng trên Sheet2. Khi xem thông tin chi tiết, bạn có ngày trong cột A và số dư cho các tài khoản khác nhau trong cột B, C và D. Vì vậy, thông tin chi tiết là một bảng hiển thị tiến trình hoạt động của số dư ngân hàng vào các ngày khác nhau.

Khi tập hợp thông tin tóm tắt của bạn trên Sheet1, bạn nhận ra rằng bạn cần tham chiếu các số liệu cuối cùng trong các cột B, C và D. Những số liệu này đại diện cho số dư mới nhất và do đó hoàn hảo cho bản tóm tắt của bạn. Bạn làm nó như thế nào? Đặc biệt là khi bạn tiếp tục thêm thông tin vào bảng tính chi tiết của mình theo thời gian?

Trên thực tế, có một số cách để tiếp cận vấn đề. (Thông thường có một số cách để giải quyết bất kỳ vấn đề nào trong Excel.) Một cách là sử dụng hàm VLOOKUP. Tại điểm trong bản tóm tắt mà bạn muốn số dư mới nhất từ ​​cột B của chi tiết (Sheet2), bạn sẽ đặt công thức sau:

=VLOOKUP(MAX(Sheet2!$A:$A),Sheet2!$A:$D,2)

Để thay đổi các tham chiếu cho hai số dư tài khoản khác, bạn chỉ cần thay đổi số cuối cùng (2) thành 3 (cho tài khoản trong cột C) hoặc 4 (cho tài khoản trong cột D). Hàm hoạt động vì nó tìm kiếm giá trị lớn nhất trong cột A chứa ngày tháng. Sau đó, nó tìm kiếm trong bảng dữ liệu (Sheet2! $ A: $ D) và tìm độ lệch thích hợp cho cột mong muốn.

Phương pháp này hoạt động tốt, miễn là không có ngày nào trong cột A quá số dư cuối cùng đã nhập. Nếu có, thì các giá trị trả về sẽ luôn không chính xác.

Một cách khác để tiếp cận vấn đề là sử dụng hàm INDEX kết hợp với COUNT hoặc COUNTA. Nếu các cột chi tiết không chứa bất kỳ văn bản nào (ngay cả trong các tiêu đề cột), thì bạn sẽ sử dụng hàm COUNT. Nếu có văn bản bao gồm, thì COUNTA được ưu tiên. Tại điểm bạn muốn bao gồm số dư cuối cùng từ cột B của chi tiết, bạn sẽ sử dụng công thức sau:

=INDEX(Sheet2!B:B,COUNTA(Sheet2!B:B))

Nó nhìn vào bảng, xác định số lượng ô không trống trong cột B, rồi kéo con số từ ô không trống cuối cùng đó. Để điều chỉnh công thức cho cột C và D, chỉ cần thay đổi các tham chiếu B thành C hoặc D.

Vẫn còn một cách khác để giải quyết vấn đề là sử dụng hàm OFFSET, như sau:

=OFFSET(Sheet2!B1,COUNTA(Sheet2!B:B)-1,0)

Hàm này trả về giá trị của độ lệch ô từ ô tham chiếu cơ sở. Trong trường hợp này, ô cơ sở là Sheet2! B1. Hàm COUNTA được sử dụng để xác định có bao nhiêu hàng cần bù trừ từ cơ sở và số 0 chỉ định rằng khoảng bù phải nằm trong cùng cột với tham chiếu cơ sở. Để thay đổi công thức cho cột C và D, chỉ cần thay đổi tất cả các tham chiếu đến B thành C hoặc D.

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

Mẹo này (12470) á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: