CHỦ ĐỀ E - ỨNG DỤNG TIN HỌC – E3 – SỬ DỤNG BẢNG TÍNH ĐIỆN TỬ NÂNG CAO

BÀI 5 – THỰC HÀNH TỔNG HỢP

Câu 1: Trường em tổ chức một hội thi văn nghệ, em hãy lập bảng tính lưu điểm của các thí sinh. Biết rằng có 6 thầy/cô giáo trong Ban giám khảo. Các thầy cô giáo chấm điểm cho từng thí sinh theo thang điểm 10. Em thực hiện các yêu cầu sau:

1) Lập bảng lưu điểm của tất cả các thầy/cô giáo cho các thí sinh tại trang tính thứ nhất. Đặt tên trang tính là “DS diem”.

- Thiết lập xác thực dữ liệu cho các ô tính lưu điểm sao cho chỉ có thể nhập số thập phân trong khoảng từ 0 đến 10.

- Thêm một cột Tổng điểm và thực hiện tính tổng điểm của từng thí sinh.

- Thêm một cột Xếp giải ngay bên phải cột Tổng điểm. Thực hiện điền giá trị cho cột Xếp giải theo quy tắc như sau: Nếu tổng điểm từ 48 điểm trở lên thì xếp giải “A”, nếu tổng điểm từ 42 đến dưới 48 điểm thì xếp giải “B”; nếu tổng điểm từ 36 đến dưới 42 điểm thì xếp giải “C”; còn lại thì không xếp giải

2) Thêm một trang tính thứ hai và đặt tên là “Thống kê”. Tại trang tính này, thực hiện lần lượt các yêu cầu sau:

- Thống kê số lượng thí sinh đạt các giải khác nhau. Vẽ biểu đồ thể hiện tỉ lệ các thí sinh đạt các giải khác nhau.

- Tính điểm trung bình của từng giám khảo đã cho tất cả thí sinh. Vẽ biểu đồ so sánh điểm trung bình đã cho của từng giám khảo.

Bài giải chi tiết:

Vận dụng kiến thức đã học và tham khảo hướng dẫn bên dưới để thực hành.

1) Lập bảng tính “DS diem”

- Tạo một trang tính mới và đặt tên là “DS diem”

- Thiết lập xác thực dữ liệu cho các ô tính lưu điểm sao cho chỉ có thể nhập số thực trong khoảng từ 0 đến 10. (Chọn “data” -> “data validation” -> đặt điều kiện cho phạm vi này

- Ghi danh sách các thí sinh vào cột A

- Ghi danh sách các giám khảo vào hàng 1 từ cột B trở đi

- Các ô trong bảng tính lưu điểm của các giám khảo sẽ được sử dụng để ghi điểm cho từng thí sinh theo thang điểm 10.

Thêm cột “Tổng điểm”

- Thêm cột mới bên phải cột cuối cùng đặt tên là “Tổng điểm”

- Trong ô đầu tiên của cột Tổng điểm (VD: B2) nhập công thức =SUM(B2:G2) để tính tổng điểm của thí sinh thứ nhất

- Sao chép công thức này xuống các dòng khác trong cột Tổng điểm

Thêm cột “Xếp giải”

- Xếp giải đặt ở cột C

- Sử dụng công thức =IF(AND(B2>=36,B2<42),“C”,IF(AND(B2>=42,B2<48), “B”,“”)) sau đó sao chép cho các thí sinh cột xếp giải.

2) Thêm trang tính thống kê

- Tạo trang tính mới đặt tên là “Thong ke” rồi điền thông tin

- Cột B: =Countif(DS_Diem!C,A2) để thống kê lượng thí sinh đạt giải tương ứng

- Chọn vẽ biểu đồ phù hợp trong Insert -> Chart

- Cột C: =Average(DS_diem!B2:G11) để tính điểm trung bình từng giám khảo

- Chọn vẽ biểu đồ phù hợp trong Insert -> Chart

Câu 2: Lập bảng tính theo dõi chỉ số BMI của tổ em.

Nhiệm vụ: Em hãy lập bảng tính theo dõi chỉ số BMI của các thành viên trong tổ theo các yêu cầu sau:

1) Lưu được họ tên, ngày tháng năm sinh và các chỉ số cân nặng, chiều cao của từng bạn ở đầu năm học. Thiết lập điều kiện cho các ô ngày tháng năm sinh để chỉ nhập được ngày tháng đúng trong năm sinh tương ứng của học sinh khối 9; Cân năng W (đơn vị Kilogam) và chiều cao H (đơn vị mét) là kiểu số thập phân trong khoảng phù hợp.

2) Tính được chỉ số BMI của từng bạn theo công thức BMI = W/H2. Kết quả BMI được định dạng hiển thị một chữ số phần thập phân.

3) Phân loại mức độ cân nặng dựa trên chỉ số BMI đã tính theo quy tắc: Nếu BMI <18.5 thì kết luận “Thiếu cân”, nếu 18.5 ≤ BMI < 25 thì kết luận “Bình thường, nếu 25≤BMI < 30 thì kết luận “thừa cân” nếu BMI ≥ 30 thì kết luận “Béo phì”.

4) Lọc danh sách các bạn trong diện kết luận khác “Bình thường” và lưu sang một trang tính mới. sắp xếp danh sách này theo thứ tự giảm dần chỉ số BMI.

5) Tạo một trang tính mới và đặt tên là “Thong ke”. Tại trang tính này, thực hiện:

– Thống kê số lượng các bạn theo từng mức kết luận. Vẽ biểu đồ thể hiện tỉ lệ các mức kết luận.

– Tính trung bình cộng cân nặng của các học sinh theo từng mức kết luận, kết quả được định dạng hiển thị một chữ số phần thập phân. Vẽ biểu đồ so sánh các giá trị trung bình này.

Bài giải chi tiết:

Để lập bảng tính theo dõi chỉ số BMI của các thành viên trong tổ theo các yêu cầu trên, bạn có thể làm theo các bước dưới đây trong Excel:

Bước 1: Lập bảng và thiết lập điều kiện nhập liệu

1.Tạo bảng dữ liệu

+ Mở Excel và tạo bảng với các cột: “Họ tên”, “Ngày tháng năm sinh”, “Cân nặng (kg)”, “Chiều cao (m)”, “BMI”, và “Kết luận”.

2.Thiết lập điều kiện cho ô ngày tháng năm sinh

+ Chọn cột “Ngày tháng năm sinh” và vào “Data” -> “Data Validation”.

+ Trong mục “Allow”, chọn “Date”.

+ Trong mục “Data”, chọn “between”.

+ Nhập khoảng thời gian cho năm sinh của học sinh khối 9, ví dụ từ 01/01/2009 đến 31/12/2009.

3.Thiết lập điều kiện cho cân nặng và chiều cao

+ Chọn cột “Cân nặng (kg)” và vào “Data” -> “Data Validation”.

+ Trong mục “Allow”, chọn “Decimal”.

+ Trong mục “Data”, chọn “between”.

+ Nhập khoảng phù hợp cho cân nặng, ví dụ từ 20 đến 150 kg.

+ Chọn cột “Chiều cao (m)” và vào “Data” -> “Data Validation”.

+ Trong mục “Allow”, chọn “Decimal”.

+ Trong mục “Data”, chọn “between”.

+ Nhập khoảng phù hợp cho chiều cao, ví dụ từ 1.2 đến 2.2 m.

Bước 2: Tính chỉ số BMI

1.Tính BMI

+ Tại cột “BMI”, nhập công thức tính BMI = Cân nặng (kg) / (Chiều cao (m))^2.

+ Ví dụ, tại ô E2: =C2/(D2^2).

+ Định dạng ô BMI để hiển thị một chữ số phần thập phân: Chọn cột “BMI”, vào “Home” -> “Number” -> “More Number Formats” -> “Number” và chọn “1” trong “Decimal places”.

Bước 3: Phân loại mức độ cân nặng

1.Phân loại mức độ cân nặng dựa trên BMI

+ Tại cột “Kết luận”, sử dụng công thức IF để phân loại mức độ cân nặng.

+ Ví dụ, tại ô F2:

=IF(E2<18.5,”Thiếu cân”, IF(E2<25,”Bình thường”, IF(E2<30,”Thừa cân”,”Béo phì”)))

Bước 4: Lọc và lưu danh sách

1.Lọc danh sách các bạn trong diện kết luận khác “Bình thường”

+ Chọn bảng dữ liệu và vào “Data” -> “Filter”.

+ Lọc cột “Kết luận” để chỉ hiển thị các giá trị khác “Bình thường”.

+ Copy các dòng đã lọc và dán vào một trang tính mới.

2. Sắp xếp danh sách theo thứ tự giảm dần chỉ số BMI

+ Chọn bảng dữ liệu đã lọc ở trang tính mới và vào “Data” -> “Sort”.

+ Chọn sắp xếp theo cột “BMI” theo thứ tự giảm dần.

Bước 5: Thống kê và vẽ biểu đồ

1.Tạo trang tính mới tên là “Thong ke”

+ Vào “Sheet” và chọn “New Sheet”, đặt tên là “Thong ke”.

2.Thống kê số lượng các bạn theo từng mức kết luận

+ Tạo bảng thống kê với các cột: “Kết luận”, “Số lượng”.

+ Sử dụng hàm COUNTIF để đếm số lượng học sinh ở từng mức kết luận.

+ Ví dụ, tại ô B2 (Thiếu cân): =COUNTIF(Sheet1!F:F,”Thiếu cân”).

+ Lặp lại cho các mức kết luận khác.

3.Vẽ biểu đồ thể hiện tỉ lệ các mức kết luận

+ Chọn bảng thống kê vừa tạo.

+ Vào “Insert” -> “Chart” -> “Pie Chart” để tạo biểu đồ tròn thể hiện tỉ lệ các mức kết luận