Kết hợp công thức SUMIF và OFFSET, danh sách Xác thực và Hộp tổ hợp để trả về bản tóm tắt từ dữ liệu được chọn theo tháng – thực sự là một sự kết hợp mạnh mẽ!

Bước 1: Chọn các mục từ danh sách Xác thực

Chọn một mục từ danh sách Xác thực (cột A trong ảnh chụp màn hình)

cho phép các công thức được nhập vào các ô trong cột C và D để xác định văn bản và trả về kết quả tóm tắt từ cột chứa tiêu chí (mục đã chọn) cho tháng được chọn trong Hộp Tổ hợp.

Để thêm danh sách Xác thực vào một dải ô:

  1. Chọn phạm vi ô (trong ảnh chụp màn hình ở trang trước, các ô được chọn là A12: A15)

  2. Chọn các ô A19: A23.

  3. Từ menu Dữ liệu, chọn Xác thực.

  4. Trong hộp thoại Xác thực Dữ liệu, hãy chọn tab Cài đặt và chọn Danh sách từ hộp Cho phép.

  5. Trong hộp Nguồn, nhấn F3, chọn Tên được xác định cho danh sách (Cấp 3 trong Ví dụ này, xem ảnh chụp màn hình ở trang cuối cùng để biết mẹo này) và nhấp vào OK.

Bước 2: Nhập công thức trả về số dư tóm tắt cho các mục đã chọn Công thức trong ô C12: C15 là:

SUMIF (DataLevel3, A12, OFFSET (DataLevel3,0, MonthSelectionNumber + 2))

Công thức trong ô D12: D15 là:

SUMIF (DataLevel3, A12, OFFSET (DataLevel3,0, MonthSelectionNumber + 2-12))

Giải thích:

Công thức SUMIF trong cột C tóm tắt số dư từ cột tháng 12 năm 2003; công thức SUMIF trong cột D tóm tắt số dư từ cột tháng 12 năm 2002.

Công thức SUMIF có ba đối số:

Đối số đầu tiên:

Phạm vi để đánh giá theo tiêu chí được nhập trong đối số thứ hai của công thức SUMIF. Trong ví dụ này, phạm vi là DataLevel3, là Tên được xác định cho cột C trong cơ sở dữ liệu Bảng cân đối. Nó chứa các mục Cấp 3 của Bảng cân đối kế toán, chẳng hạn như Tiền mặt, Các khoản phải thu, Hàng tồn kho, v.v.

Đối số thứ hai:

Tiêu chí là mục được chọn từ danh sách Xác thực Cấp 3.

Đối số thứ ba:

Cột mà dữ liệu sẽ được tóm tắt. Điều này sẽ được chọn theo công thức OFFSET cho cột tháng, được điều chỉnh bằng số được chọn từ Danh sách tháng trong Hộp tổ hợp. Công thức OFFSET cho phép chuyển hướng tháng đã chọn khỏi cột cơ sở (cột C trong ảnh chụp màn hình bên dưới).

Cách thức hoạt động của công thức OFFSET

Cột 29 là số cột của tháng 12 năm 2003 và số cột của tháng 12 năm 2002 là 17, tức là 12 cột trước đó (xem ảnh chụp màn hình bên dưới).

Cách thay đổi tiêu đề đầu trang trong trang tính từ ký tự thành số: 1. Từ menu Công cụ, chọn Tùy chọn.

  1. Trong tab Chung, và kiểm tra kiểu tham chiếu R1C1.

Khi tháng 12 năm 2003 được chọn từ danh sách thả xuống Hộp tổ hợp (Danh sách tháng), số tháng trong danh sách đó là 24 (điều này được tính bằng cách xác định số tháng từ tháng 1 năm 2002 đến tháng 12 năm 2003:

2 năm * 12 tháng = 24). Ô được liên kết với Combo Box nhận giá trị là 24.

Trong bảng dữ liệu, cột 3 là cột cơ sở mà công thức SUMIF đánh giá tiêu chí trong đối số thứ hai của công thức SUMIF.

Trong trường hợp này, tháng 12 năm 2003 cách cột C 24+ 2 = 26 (2 = Cột D & Cột E).

Trong đối số thứ ba, Sum_range nên cách cột cơ sở 26 cột. Công thức OFFSET trả về kết quả là 26 và làm cho công thức SUMIF tóm tắt các số liệu từ cột tháng 12 năm 2003.

Mẹo này được trích từ cuốn sách Financial Statements.xls, Ấn bản lần thứ 2.

Ảnh chụp màn hình // Sự kết hợp điện