Bài tập: Luyện tập: Xử lý bảng tính

Giới thiệu bài học

Trong chương 3, chúng ta đã được làm quen với các thao tác xử lý dữ liệu trên bảng tính như sắp xếp, lọc dữ liệu, sử dụng hàm tính toán cơ bản và nâng cao, tạo biểu đồ minh họa. Bài học hôm nay sẽ giúp em tổng hợp và rèn luyện lại những kỹ năng đó thông qua một số bài tập thực hành cụ thể. Em sẽ được hướng dẫn cách phân tích yêu cầu, lựa chọn công cụ phù hợp và thực hiện từng bước trên phần mềm bảng tính. Mục tiêu là giúp em tự tin hơn khi giải quyết các bài toán thực tế liên quan đến bảng tính.

Lý thuyết cần nhớ

Trước khi làm bài tập, em hãy ôn lại một số khái niệm và thao tác chính:

• Sắp xếp dữ liệu: Dùng lệnh Sort (trên dải lệnh Data) để sắp xếp các hàng theo thứ tự tăng dần hoặc giảm dần của một cột. Em có thể sắp xếp theo nhiều cột (sắp xếp lồng nhau).
• Lọc dữ liệu: Dùng lệnh Filter (trên dải lệnh Data) để chỉ hiển thị các hàng thỏa mãn điều kiện nào đó. Sau khi lọc, em có thể chọn các tiêu chí văn bản, số hoặc tùy chỉnh (Custom Filter).
• Các hàm cơ bản: SUM (tính tổng), AVERAGE (tính trung bình), MAX (tìm giá trị lớn nhất), MIN (tìm giá trị nhỏ nhất), COUNT (đếm số ô có chứa số), COUNTA (đếm số ô không rỗng).
• Hàm có điều kiện: SUMIF, AVERAGEIF, COUNTIF dùng để tính toán trên các ô thỏa mãn một điều kiện duy nhất.
• Biểu đồ: Chọn vùng dữ liệu, vào dải lệnh Insert, chọn dạng biểu đồ phù hợp (cột, đường tròn, đường gấp khúc,...) và chỉnh sửa tiêu đề, chú thích.

Ví dụ minh họa (Bài tập thực hành)

Bài tập 1: Quản lý bán hàng đơn giản

Yêu cầu: Em có bảng dữ liệu "Doanh thu bán hàng tháng 1" gồm các cột: STT, Tên mặt hàng, Số lượng bán, Đơn giá (nghìn đồng), Thành tiền (= Số lượng * Đơn giá). Hãy thực hiện:

1. Tính cột Thành tiền cho tất cả các mặt hàng.
2. Sắp xếp danh sách mặt hàng theo thứ tự Thành tiền giảm dần.
3. Lọc ra các mặt hàng có Số lượng bán >= 50.
4. Tính tổng Thành tiền của các mặt hàng có Số lượng bán >= 50 bằng hàm SUMIF.

Hướng dẫn giải:

• Bước 1 (Tính Thành tiền): Giả sử cột Số lượng là C, cột Đơn giá là D, em nhập công thức tại ô đầu tiên của cột Thành tiền (ví dụ E2): =C2*D2. Sau đó sao chép công thức cho các ô còn lại.
• Bước 2 (Sắp xếp): Bôi đen vùng dữ liệu (gồm cả hàng tiêu đề). Vào dải lệnh Data -> Sort. Trong hộp thoại Sort, chọn: Sort by = "Thành tiền", Order = "Largest to Smallest" (Giảm dần). Nhấn OK.
• Bước 3 (Lọc dữ liệu): Bôi đen vùng dữ liệu, vào dải lệnh Data -> Filter. Khi các mũi tên xuất hiện ở hàng tiêu đề, nháy vào mũi tên của cột "Số lượng bán". Chọn Number Filters -> Greater Than or Equal To.... Nhập số 50 vào ô bên phải, nhấn OK. Bảng sẽ chỉ hiện các hàng thỏa mãn.
• Bước 4 (Tính tổng có điều kiện): Giả sử vùng điều kiện là cột Số lượng (C2:C100), điều kiện là ">=50", vùng tính tổng là cột Thành tiền (E2:E100). Em nhập công thức tại một ô trống (ví dụ G1): =SUMIF(C2:C100,">=50",E2:E100). Kết quả sẽ là tổng Thành tiền của các mặt hàng có số lượng từ 50 trở lên. (Sau bước lọc, công thức vẫn tính trên toàn bộ dữ liệu, không bị ảnh hưởng bởi lọc).

Bài tập 2: Thống kê điểm thi

Yêu cầu: Bảng dữ liệu gồm: Họ tên, Toán, Văn, Anh, Điểm trung bình (TB). Yêu cầu:

1. Tính cột Điểm TB bằng hàm AVERAGE.
2. Sử dụng hàm COUNTIF để đếm số học sinh có Điểm TB >= 8.0.
3. Sử dụng hàm AVERAGEIF để tính điểm trung bình môn Toán của các học sinh có Điểm TB >= 8.0.
4. Vẽ biểu đồ cột so sánh điểm trung bình của 5 học sinh đầu tiên trong danh sách.

Hướng dẫn giải:

• Bước 1: Giả sử điểm Toán, Văn, Anh lần lượt ở các cột B, C, D. Tại ô E2, nhập: =AVERAGE(B2:D2). Sao chép công thức.
• Bước 2: Giả sử cột Điểm TB là E2:E100. Nhập công thức: =COUNTIF(E2:E100,">=8.0"). Kết quả là số học sinh có điểm TB từ 8.0 trở lên.
• Bước 3: Vùng tính trung bình là điểm Toán (B2:B100), vùng điều kiện là cột Điểm TB (E2:E100), điều kiện ">=8.0". Nhập: =AVERAGEIF(E2:E100,">=8.0",B2:B100). Kết quả là điểm Toán trung bình của nhóm học sinh khá/giỏi.
• Bước 4: Chọn cột Họ tên (A2:A6) và cột Điểm TB (E2:E6) của 5 học sinh đầu. Vào dải lệnh Insert -> chọn biểu đồ cột (Column Chart). Thêm tiêu đề biểu đồ và nhãn trục cho phù hợp.

Ghi nhớ

1. Luôn kiểm tra vùng dữ liệu trước khi thực hiện sắp xếp hoặc lọc để tránh làm xáo trộn dữ liệu.
2. Khi dùng hàm có điều kiện (SUMIF, AVERAGEIF), cần chú ý cú pháp: =TÊN_HÀM(vùng_điều_kiện, điều_kiện, vùng_tính_toán). Phần vùng_điều_kiện và vùng_tính_toán phải có cùng số hàng.
3. Điều kiện trong hàm có thể là số (ví dụ: ">=50"), văn bản (ví dụ: "Bút bi") hoặc tham chiếu đến ô khác (ví dụ: ">" & F1).
4. Sau khi lọc, các dòng ẩn không bị xóa khỏi bảng tính. Các hàm tính toán (SUM, AVERAGE...) vẫn tính trên tất cả các dòng (cả ẩn), em cần lưu ý để tránh nhầm lẫn.

Bài tập gợi ý (Tự luyện tập)

Em hãy tự tạo một bảng tính giả định về "Kết quả khảo sát sở thích môn học" của lớp em (gồm các cột: STT, Họ tên, Môn yêu thích, Thời gian tự học (giờ/tuần)). Sau đó thực hiện các yêu cầu sau:

1. Tính thời gian tự học trung bình của tất cả học sinh. (Dùng hàm AVERAGE)
2. Đếm số học sinh có thời gian tự học >= 5 giờ/tuần. (Dùng hàm COUNTIF)
3. Tính tổng thời gian tự học của những học sinh yêu thích môn "Tin học". (Dùng hàm SUMIF)
4. Sắp xếp danh sách theo cột Thời gian tự học giảm dần.
5. Lọc ra những học sinh có thời gian tự học từ 3 đến 7 giờ/tuần (dùng Custom Filter với điều kiện "greater than or equal to 3" và "less than or equal to 7").
6. Vẽ biểu đồ cột thể hiện thời gian tự học của 5 bạn có thời gian tự học nhiều nhất.

Em hãy thực hành kỹ các bước và đối chiếu kết quả để kiểm tra tính chính xác.