Tin học 9 – Bài 8A. Hàm tính tổng theo điều kiện SUMIF

Mức độ kiến thức, kỹ năng cần đạt

• Nhận biết:
  • Mô tả được chức năng của hàm SUMIF.
  • Nhận diện đúng các tham số trong cú pháp hàm: range, criteria, [sum_range].
• Thông hiểu:
  • Giải thích được cơ chế hoạt động của hàm khi có và không có tham số [sum_range].
  • Phân biệt được cách viết tiêu chí (criteria) đối với dữ liệu số, văn bản và biểu thức so sánh.
• Vận dụng:
  • Sử dụng hàm SUMIF để tính tổng các giá trị thỏa mãn điều kiện cho trước trong bảng tính quản lý tài chính.
  • Vận dụng linh hoạt địa chỉ tuyệt đối/tương đối khi sao chép công thức.

Năng lực số tích hợp

Bài học này góp phần phát triển các năng lực số sau theo Phụ lục khung năng lực số:

• 1.3.NC1 (Quản lý dữ liệu): Thao tác được dữ liệu trên bảng tính (sử dụng hàm, công thức) để trích xuất và tổng hợp thông tin tài chính theo yêu cầu cụ thể.
• 5.2.NC1 (Xác định nhu cầu và giải pháp): Áp dụng được công cụ xử lý bảng tính để giải quyết bài toán thực tế về quản lý thu chi của dự án hoặc cá nhân.

1. Cú pháp và nguyên lý hoạt động của hàm SUMIF

Excel cung cấp hàm SUMIF để tính tổng các giá trị trong một phạm vi thỏa mãn một điều kiện xác định.

a) Cú pháp hàm

=SUMIF(range, criteria, [sum_range])

Trong đó:

• range (bắt buộc): Khối ô tính chứa các ô cần được kiểm tra theo tiêu chí (vùng điều kiện).
• criteria (bắt buộc): Tiêu chí để xác định ô tính trong range thỏa mãn điều kiện tính tổng. Tiêu chí có thể là số, biểu thức so sánh (ví dụ: ">5000") hoặc văn bản (ví dụ: "Lương cứng").
• [sum_range] (tùy chọn): Khối ô tính chứa các giá trị thực tế cần tính tổng. Nếu bỏ qua tham số này, Excel sẽ tính tổng ngay trên các ô thuộc range.

b) Ví dụ minh họa

Giả sử có bảng tính Thu (Hình 2 – SGK), để tính tổng tiền thu được từ khoản “Lương”, công thức tại ô H2 được viết như sau:

=SUMIF(B$3:B$12, F2, D$3:D$12)

Giải thích:

• B$3:B$12: Vùng chứa tên các khoản thu (range).
• F2: Ô chứa tiêu chí “Lương” (criteria).
• D$3:D$12: Vùng chứa số tiền tương ứng cần tính tổng (sum_range).

Phát triển năng lực số

Mục tiêu: Thực hành viết hàm SUMIF với các dạng tiêu chí khác nhau (văn bản và so sánh số) để hiểu rõ cơ chế lọc dữ liệu (Năng lực 1.3 – Quản lý dữ liệu).

• Yêu cầu: Trên trang tính Thu, tính tổng số tiền của các khoản thu trên 5 triệu đồng và tính tổng thu nhập từ “Lương cứng”.
• Hướng dẫn thực hiện:
  1. Mở tệp QuanLiTaiChinh.xlsx, chọn trang tính Thu.
  2. Tại ô tính tổng các khoản > 5 triệu, nhập công thức:
     =SUMIF(D3:D12, ">5000")
     (Lưu ý: Không cần tham số sum_range vì vùng điều kiện chính là vùng tính tổng).
  3. Tại ô tính tổng “Lương cứng”, nhập công thức:
     =SUMIF(C3:C12, "Lương cứng", D3:D12)
• Kết quả mong đợi:
  • Ô tính > 5000 sẽ hiển thị tổng các giá trị lớn hơn 5.000 trong cột Số tiền.
  • Ô tính “Lương cứng” sẽ hiển thị tổng số tiền tương ứng với nội dung “Lương cứng” ở cột Nội dung.

2. Sao chép công thức và địa chỉ vùng dữ liệu

Khi sử dụng hàm SUMIF cho bảng thống kê (nhiều dòng), cần lưu ý về địa chỉ ô tính khi sao chép công thức.

• Tham số range và sum_range: Thường sử dụng địa chỉ tuyệt đối (ví dụ: $B$3:$B$12) để cố định vùng dữ liệu khi sao chép công thức xuống các ô dưới.
• Tham số criteria: Thường sử dụng địa chỉ tương đối (ví dụ: F2) nếu tiêu chí nằm trong một ô tính và thay đổi theo từng dòng của bảng thống kê.

Ví dụ thực tế:

Để tính tổng tiền cho từng khoản thu trong bảng thống kê (Hình 2 – SGK), tại ô H2 nhập công thức sau đó sao chép xuống các ô H3:H6

=SUMIF($B$3:$B$12, F2, $D$3:$D$12)

3. Thực hành: Quản lý tài chính dự án

Nhiệm vụ: Sử dụng hàm SUMIF để hoàn thành bảng thống kê tài chính cho dự án trong tệp QuanLiTaiChinh.xlsx (Bài 7A).

Hướng dẫn thực hiện:

Bước 1: Tính tổng tiền theo từng khoản thu (Sheet Thu)

1. Tại ô I2 (hoặc ô tương ứng trong cột Tổng tiền của bảng thống kê), nhập hàm SUMIF.
2. Xác định range: Cột Khoản thu ($B$3:$B$14 – Lưu ý dùng địa chỉ tuyệt đối).
3. Xác định criteria: Ô chứa tên khoản thu bên bảng thống kê (F2).
4. Xác định sum_range: Cột Số tiền ($D$3:$D$14).

=SUMIF($B$3:$B$14, F2, $D$3:$D$14)

Bước 2: Tính tổng tiền chi theo từng hạng mục (Sheet Chi)

1. Chuyển sang trang tính Chi.
2. Tại bảng thống kê chi, sử dụng SUMIF để tính tổng cho “Ăn”, “Ở”, “Học tập”,…
3. Công thức mẫu: =SUMIF($B$3:$B$14, F2, $D$3:$D$14) (Tùy chỉnh địa chỉ vùng dữ liệu phù hợp với bảng Chi).

Bước 3: Thực hiện các yêu cầu thống kê phức tạp

• Tính tổng chi cho các lần chi trên 10 triệu đồng:
  =SUMIF(D3:D14, “>10000”)
• Tính tổng chi mua xăng:
  =SUMIF(C3:C14, “Xăng”, D3:D14)

Phát triển năng lực số

Mục tiêu: Sử dụng công cụ bảng tính để giải quyết vấn đề thực tế, đánh giá tình hình tài chính (Năng lực 5.2 – Giải quyết vấn đề kỹ thuật/nhu cầu).

• Yêu cầu: Phân tích dữ liệu chi tiêu của nhóm dựa trên kết quả tính toán.
• Hướng dẫn thực hiện:
  1. Dựa vào cột “Tổng tiền” vừa tính được ở bảng thống kê Chi.
  2. So sánh các hạng mục chi tiêu (Ăn, Ở, Di chuyển…).
  3. Tạo thêm một yêu cầu thống kê mới: Tính tổng các khoản chi cho “Du lịch” (dựa vào cột Nội dung chi tiết).
• Kết quả mong đợi: Học sinh không chỉ tính đúng con số mà còn nhận xét được khoản nào chiếm tỷ trọng lớn nhất, từ đó đề xuất điều chỉnh kế hoạch tài chính.

Luyện tập & Vận dụng

Bài tập vận dụng

Mở bảng tính quản lý chi tiêu của gia đình hoặc nhóm em. Hãy lập công thức để:

1. Tính tổng tiền đã chi cho việc “Mua sách vở”.
2. Tính tổng các khoản chi phát sinh có giá trị dưới 50.000 đồng.
3. Thống kê tổng thu nhập từ các nguồn “Thưởng” trong năm.