Công thức SUMIF tóm tắt dữ liệu theo tiêu chí. Công thức OFFSET cho phép chúng tôi thay đổi tham chiếu. Sự kết hợp của hai công thức và bổ sung Hộp tổ hợp cho phép bạn dễ dàng tóm tắt dữ liệu theo tiêu chí từ bảng dữ liệu.

Ví dụ

Nhiệm vụ: Tóm tắt Lãi và lỗ theo các mục P&L (xem ảnh chụp màn hình P&L, cột B) cho tháng 1 năm 2001 hoặc bất kỳ tháng nào khác mà bạn chọn.

Bước 1: Chỉ định tên cho cột B trong trang tính.

Chọn cột B, nhấn Ctrl + F3, nhập ColB vào hộp Tên * trong trang tính, sau đó bấm OK.

Bước 2: Công thức SUMIF

Công thức SUMIF * tóm tắt dữ liệu theo tiêu chí.

  • Công thức có 3 đối số:

Đối số đầu tiên – Range – ColB * (cột B trong sheet bảng dữ liệu).

Đối số thứ hai – Tiêu chí * – b3 (văn bản: Doanh thu).

Đối số thứ ba – Sum_range * – D: D (cột D, tháng 1 năm 2001). Đây là phạm vi dữ liệu mà từ đó dữ liệu theo tiêu chí sẽ được tóm tắt.

Vấn đề

Công thức SUMIF là tuyệt vời để tóm tắt dữ liệu theo tiêu chí. Tuy nhiên, có một hạn chế: bạn không thể thực hiện bất kỳ thay đổi nào trong phạm vi tóm tắt khi sử dụng công thức SUMIF *. Trong ví dụ trên, bạn đã tổng hợp dữ liệu từ cột tháng 1 năm 2001. Vậy thì làm cách nào để bạn có thể dễ dàng thay đổi phạm vi của tổng để tính tổng từ cột của tháng 3 hoặc tháng 4 (thay vì từ tháng 1)?

Có một đối số thứ ba trong công thức VLookup *: thay đổi số cột cũng làm thay đổi số cột giao nhau.

Công thức Index * linh hoạt hơn nhiều; bạn có thể thay đổi cả số hàng và số cột.

Công thức SUMIF cần trợ giúp và giải pháp là thêm công thức OFFSET *.

Bước 3: Công thức OFFSET

OFFSET

Bước 4: Kết hợp công thức OFFSET và SUMIF

OFFSET

Hộp kết hợp

Bước 5: Hộp tổ hợp (đọc thêm cách thêm Hộp tổ hợp) Thêm Hộp tổ hợp.

Tên của ô được liên kết với hộp tổ hợp là MonthNumber. Trong ảnh chụp màn hình, hãy xem đối số thứ ba của công thức SUMIF trong thanh công thức. Tên của ô được liên kết với Hộp Tổ hợp xuất hiện ở đây.

Kết hợp SUMIF, OFFSET và hộp Tổ hợp cung cấp một công cụ cực kỳ mạnh mẽ để truy vấn và tổng hợp dữ liệu từ một báo cáo. Ảnh chụp màn hình // Tóm tắt dữ liệu theo tiêu chí từ bảng dữ liệu bằng công thức Sumif, công thức Offset và Combo Box trong Microsoft Excel //images/screenshots/299-1.JPG [image: //images/screenshots/299-1_s.JPG [ Tổng hợp dữ liệu theo tiêu chí từ bảng dữ liệu sử dụng công thức Sumif, công thức Offset và Combo Box trong Microsoft Excel]] image: https: //www.office-skill.site/images/f [image, width = 20, height = 1 ] //images/screenshots/299-2.JPG [image: //images/screenshots/299-2_s.JPG [Tóm tắt dữ liệu theo tiêu chí từ bảng dữ liệu sử dụng công thức Sumif, công thức Offset và Combo Box trong Microsoft Excel]] image: https: //www.office-skill.site/images/f [image, width = 20, height = 1] //images/screenshots/299-3.JPG [image: // images / screenshot / 299- 3_s.JPG [Tổng hợp dữ liệu theo tiêu chí từ bảng dữ liệu sử dụng công thức Sumif, công thức Offset và Combo Box trong Microsoft Excel]] image: https: //www.office-skill.site/images/f [image, width = 20 , height = 1]