BÀI 9a. TỔNG HỢP, ĐỐI CHIẾU THU,CHI

KHỞI ĐỘNG

Trong bảng tính quản lí tài chính gia đình, bạn Toàn lập bảng tổng hợp thu, chi như ở Hình 1.

Theo em, dữ liệu tại cột Thu, Chi trong trang tỉnh Tổng hợp liên quan đến dữ liệu ở những trang tính nào? Làm thế nào để đưa ra lời nhắc ở cột Cân đối theo kết quả đối chiếu dữ liệu trong các ô tính tương ứng tại các cột Thu, Chỉ?

Giải rút gọn:

- Theo em, dữ liệu tại cột Thu, Chi trong trang tỉnh Tổng hợp liên quan đến dữ liệu ở những trang tính “Thu nhập” và trang tính “Chi phí”

- Để đưa ra lời nhắc ở cột Cân đối theo kết quả đối chiếu dữ liệu trong các ô tính tương ứng tại các cột Thu, Chi ta cần so sánh kết quả Thu và Chi

KHÁM PHÁ

1. Tổng hợp thu, chi theo tháng

a) Trích xuất giá trị tháng từ dữ

Làm

Nêu các việc cần làm để trích xuất giá trị tháng từ dữ liệu kiểu ngày để được kết quả như Hình 3.

Giải rút gọn:

- Ta dùng hàm MONTH, nhập công thức =MONTH(A3)

- Sao chép công thức từ ô tính B3 đến khối ô B4:B14

b) Tổng hợp thu, chi theo tháng

Làm

Nêu các việc thực hiện tính tổng số tiền các khoản chi theo tháng để được kết quả như ở Hình 7.

Giải rút gọn:

Bước 1: Chọn ô tính B3, gõ “=SUMIF(“.

Bước 2: Chọn trang tính thu.

Bước 3: Chọn khối ô tính B3:B12. Sau đó gõ dấu phẩy ",".

Bước 4: Chọn trang tính Tổng hợp

Bước 5: Chọn ô tính A3. Sau đó gõ dấu phẩy “,”.

Bước 6: Chọn trang tính thu.

Bước 7: Chọn khối ô tính E3:E12 rồi gõ phím Enter.

2. Sử dụng hàm IF để đối chiếu, hỗ trợ cân đối thu, chi

Làm

Tại Hình 8, em hãy cho biết

a) Kết quả khi sao chép công thức ở ô tính D3 đến khối ô tính D4:D5.

b) Kết quả khi thực hiện nhập công thức: =IF(C3>B3,"Chi > Thu",IF(C3-B3, "Chi = Thu", "Chi < Thu")) vào ô tính D3 rồi sao chép công thức đến khối ô tính D4:D5.

Giải rút gọn:

a) Kết quả sẽ là

Ô d2: Chi<=Thu

Ô d3: Chi<=Thu

b) Kết quả:

Ô D1: Chi>Thu

Ô D2: Chi=Thu

Ô D3: Chi<Thu

LUYỆN TẬP

Tại trang tính Tổng hợp như ở Hình 8, thực hiện nhập công thức ở ý b) tại

hoạt động của mục 2 vào ô tính D3 rồi sao chép đến khối ô tính D4:D5 ta được kết quả như ở Hình 1. Theo em, bảng tổng hợp thu, chi ở Hình 1 sẽ thay đổi thế nào khi thực hiện một trong hai việc dưới đây.

a) Trong bảng thu nhập, thay dữ liệu tại ô tính E4 từ 3.000 thành 5 000 (Hình 9).

b) Trong bảng chỉ tiêu, thay dữ liệu tại ô tính E7 từ 2.000 thành 2 500 (Hình 10).

Giải rút gọn:

a) Khi đó cột thu tháng 1 nhập sẽ tăng lên 22000 nên kết quả  tại ô D3 sẽ là Chi<=Thu

b) Khi đó cột chi tháng 2 sẽ tăng lên 5700 nên không có thay đổi tại Hình 8

THỰC HÀNH

Mở bảng tính QuanLiTaiChinh.xlsx em đã làm ở Bài 8A và thực hiện các yêu cầu sau:

a) Trong trang tính Thu, thực hiện thêm cột Tháng, sử dụng hàm Month trích xuất giá trị tháng từ dữ liệu cột ngày để được kết quả như hình 2.

b) Trong trang tính Chi, thực hiện thêm cột tháng, sử dụng hàm month trích xuất giá trị tháng từ dữ liệu ở cột ngày để được kết quả như Hình 3.

c) Thực hiện thêm trang tính mới, đổi tên trang tính, sử dụng hàm SUMIF để tổng hợp dữ liệu thu, chi theo tháng để được kết quả như Hình 7.

d) Sử dụng một hàm IF đối chiếu, hỗ trợ cân đối thu, chi để được kết quả tương tự như ở hình 11.

e) Sử dụng hàm IF lồng nhau đối chiếu, hỗ trợ cân đối thu, chi để được kết quả tương tự như hình 1.

Giải rút gọn:

Hướng dẫn:

a) Trong trang tính "Thu":

Thêm cột mới sau cột "Ngày".

Sử dụng công thức =MONTH(B2) (giả sử "Ngày" ở cột B) cho ô đầu tiên.

Kéo công thức xuống áp dụng cho tất cả các dòng.

b) Trong trang tính "Chi":

- Thêm một cột mới sau cột "Ngày" và đặt tên là "Tháng".

- Trong ô đầu tiên của cột "Tháng" (ví dụ: ô C2), sử dụng công thức: =MONTH(B2) (giả sử cột "Ngày" nằm trong cột B và dòng đầu tiên là tiêu đề).

- Kéo công thức này xuống để áp dụng cho tất cả các dòng dữ liệu.

c) Thêm một trang tính mới:

Thêm trang tính mới, đặt tên mong muốn.

Ô A1: Nhập "Tháng".

Ô B1: Nhập "Thu".

Ô C1: Nhập "Chi".

Ô A2: Nhập "Tháng 1".

Ô B2: Sử dụng công thức: =SUMIF(Thu!C:C, A2, Thu!D:D) (giả sử dữ liệu thu trong "Thu", "Tháng" ở C, "Thu" ở D).

Ô C2: Sử dụng công thức: =SUMIF(Chi!C:C, A2, Chi!D:D) (giả sử dữ liệu chi trong "Chi", "Tháng" ở C, "Chi" ở D).

Kéo công thức xuống cho các tháng khác.

d) Sử dụng hàm IF để cân đối thu và chi:

Ô D2: Sử dụng công thức: =IF(B2>C2, "Thiếu", IF(B2<C2, "Thừa", "Đồng bằng"))

e) Sử dụng hàm IF lồng nhau để cân đối thu và chi:

Ô E2: Sử dụng công thức: =IF(B2=C2, "Đồng bằng", IF(B2>C2, "Thiếu " & TEXT(B2-C2, "#,##0"), "Thừa " & TEXT(C2-B2, "#,##0")))

VẬN DỤNG

Làm việc nhóm, mở bảng tính QuanLiTaiChinhCLB.xlsx nhóm em đã làm ở Bài 8A và thực hiện các yêu câu sau:

a) Trong bảng dữ liệu thu, thực hiện thêm cột Tháng và sử dụng hàm MONTH để trích xuất giá trị tháng từ dữ liệu ngày thu.

b) Trong bảng dữ liệu chi, thực hiện thêm cột Tháng và sử dụng hàm MONTH để trích xuất giá trị tháng từ dữ liệu ngày chi.

c) Sử dụng hàm SUMIF để tổng hợp dữ liệu thu, chi theo tháng.

d) Sử dụng hàm IF đối chiếu thu, chi theo tháng và đưa ra lời nhắc hỗ trợ cân đối thu, chi.

Giải rút gọn:

a) Trong bảng dữ liệu thu:

Thêm cột mới, đặt tên "Tháng".

Ô B2 (bảng thu): Sử dụng =MONTH(A2) (ngày thu ở cột A).

Ô D2 (bảng chi): Sử dụng =MONTH(C2) (ngày chi ở cột C).

Sao chép công thức cho các ô còn lại trong cột "Tháng".

b) Thực hiện các bước tương tự trong bảng dữ liệu chỉ:

- Thêm một cột mới và đặt tên cột là "Tháng".

- Trong ô đầu tiên của cột "Tháng" (ví dụ: ô D2), sử dụng công thức sau để trích xuất giá trị tháng từ dữ liệu ngày chi: =MONTH(C2) (giả sử ngày chi nằm trong cột C).

- Sao chép công thức này cho tất cả các ô trong cột "Tháng", để trích xuất giá trị tháng cho tất cả các dòng trong bảng dữ liệu chi.

c) Để tổng hợp dữ liệu thu theo tháng:

Tạo bảng mới, đặt tên cột "Tháng" và "Tổng thu".

Cột "Tháng": Điền giá trị tháng (1-12).

Ô B2: Sử dụng =SUMIF(Sheet1!$B$2:$B$100, A2, Sheet1!$D$2:$D$100) (giả sử:

Dữ liệu thu trong Sheet1.

"Tháng" thu ở B.

"Tổng thu" ở D.

Dữ liệu thu tối đa 100 dòng).

Sao chép công thức cho các ô còn lại trong cột "Tổng thu".

d) Để sử dụng hàm IF để đưa ra lời nhắc hỗ trợ cân đối thu, chi theo tháng:

Thêm cột "Lời nhắc" vào bảng tổng hợp.

Ô E2: Sử dụng =IF(D2 > E2, "Thu vượt chi", IF(D2 < E2, "Chi vượt thu", "Cân đối")) (giả sử:

"Tổng thu" ở D.

"Tổng chi" ở E).

Sao chép công thức cho các ô còn lại trong cột "Lời nhắc".