BÀI 5. SỬ DỤNG ĐỊA CHỈ TƯƠNG ĐỐI, TUYỆT ĐỐI TRONG CÔNG THỨC

PHẦN I. HỆ THỐNG CÂU HỎI, BÀI TẬP TRONG SGK

KHỞI ĐỘNG

Hình 1 là bảng tính tiền công theo ca làm của một tổ sản xuất được lập bằng Excel. Em hãy trao đổi với bạn để lập công thức tính:

- Tổng số ca của người đầu tiên sao cho khi sao chép đến tất cả các ô tính trong khối ô tính E5:E9 ta vẫn được kết quả đúng.

Tổng số ca = Số ca ngày + Số ca đêm.

- Tiền công của người đầu tiên sao cho khi sao chép đến tất cả các ô tính trong khối ô tính F5:F9 ta vẫn được kết quả đúng.

Tiền công = Tổng số ca x Số tiền/1 ca (tại ô tính F2).

1. ĐỊA CHỈ TƯƠNG ĐỐI

Làm: 

Em hãy lập công thức tính Tổng cộng số ca ngày tại ô tính C10 và cho biết khi sao chép công thức này đến ô tính D10 thì:

a) Địa chỉ cột của các ô tính trong công thức thay đổi như thế nào? Tại sao?

b) Địa chỉ hàng của các ô tính trong công thức có thay đổi hay không? Tại sao?

2. ĐỊA CHỈ HỖN HỢP, ĐỊA CHỈ TUYỆT ĐỐI

Làm: 

1. Em hãy lập công thức tính Tổng tiền công tại ô tính C11 để khi sao chép công thức này đến các ô tính D11, E11 vẫn cho kết quả đúng.

2. Hình 5 là bảng tính được lập để tính tiền lãi gửi ngân hàng. Cách tính tiền lãi như sau: Tiền lãi = Tiền gốc × Lãi suất × Số tháng.

Nhập công thức nào sau đây vào ô tính B5 để khi sao chép đến tất cả các ô tính trong các khối ô tính B6:B10, C5:D10 vẫn cho kết quả đúng? Tại sao?

LUYỆN TẬP

Luyện tập 1:Phần mềm bảng tính Excel có các loại địa chỉ ô tính nào?

Luyện tập 2: Phát biểu nào dưới đây sai?

1. Địa chỉ tương đối là địa chỉ ô tính không có dấu $ được thêm vào trước tên cột và tên hàng.
2. Địa chỉ tuyệt đối là địa chỉ ô tính có dấu $ được thêm vào trước tên cột và tên hàng.
3. Địa chỉ hỗn hợp là địa chỉ ô tính có dấu $ chỉ được thêm vào trước tên cột hoặc tên hàng.
4. Chỉ có hai loại địa chỉ ô tính là địa chỉ tương đối và địa chỉ tuyệt đối.

Luyện tập 3: Ghép nối địa chỉ ô tính ở cột bên trái với đặc điểm tương ứng ở cột bên phải khi sao chép công thức.

Luyện tập 4: Với bảng tính ở Hình 6, em hãy cho biết kết quả sao chép công thức ở ô tính B2 đến ô tính C3 khi công thức tại ô tính B2 lần lượt là:

THỰC HÀNH

Thực hành 1: Mở tệp bảng tính ở Hình 1 (giáo viên cung cấp) và thực hiện các công việc dưới đây:

a) Lập công thức tính Tổng số ca, Tiền công cho người đầu tiên. Sau đó sao chép công thức để tính cho những người còn lại.

b) Lập công thức tính Tổng cộng, Tổng tiền công tại ô tính C10, C11 cho cột Số ca ngày. Sau đó sao chép công thức để tính cho cột Số ca đêm, Tổng số ca. Lưu lại bảng tính.

c) Lưu lại tập tính với tên khác là Tiencongheso.xlsx. Chèn thêm hàng vào vị trí hàng 3. Nhập dữ liệu hệ số ca ngày là 1.2, hệ số của ca đêm là 1.5 lần lượt vào các ô tính C3, D3. Lập công thức tính Tổng số ca cho người đầu tiên theo công thức:

Tổng số ca = Số ca ngày x hệ số ca ngày + Số ca đêm × hệ số ca đêm

Sao chép công thức để tính cho những người còn lại.

Thực hành 2: Mở tệp bảng tính ở Hình 5 (giáo viên cung cấp) và thực hiện các công việc sau:

a) Tại ô tính B5, nhập công thức tính tiền lãi, sau đó sao chép công thức đến khối ô tính B6:B10, C5:D10.

b) Kiểm tra và cho biết kết quả tính tiền lãi ở ô tính D10 có đúng hay không? Tại sao?

Thực hành 3:

Khởi động Excel, nhập dữ liệu và định dạng trang tính để có bảng 2 tính tương tự Hình 7 và thực hiện các công việc sau:

a) Lập công thức tính Tiền lãi của mặt hàng đầu tiên (ô tính G5) theo công thức:

Tiền lãi = Giá bán (VNĐ) x Số lượng bán – Giá nhập (USD) × Tỉ giá/1 USD × Số lượng bán; sao chép công thức để tính Tiền lãi cho các mặt hàng còn lại.

b) Lập công thức tính Tiền hàng tồn của mặt hàng đầu tiên (ô tính H5), biết rằng:

Tiền hàng tồn = (Số lượng nhập – Số lượng bán) × Giá nhập (USD) × Tỉ giá/1 USD; sao chép công thức để tính Tiền hàng tồn cho các mặt hàng còn lại.

c) Thay đổi Tỉ giá tại ô tính H3, quan sát và cho biết kết quả ở những ô tính nào thay đổi theo? Theo em việc lưu trữ tỉ giá tại ô tính H3 như trong bảng tính sẽ mang lại lợi ích gì?

d) Lưu bảng tính với tên Doanh_thu_cua_hang.xlsx và thoát khỏi Excel.

VẬN DỤNG

Điểm trung bình học kì môn học được tính bằng trung bình các điểm thành phần nhân với hệ số của điểm thành phần đó (điểm thường xuyên có hệ số là 1, điểm kiểm tra giữa kì có hệ số là 2, điểm kiểm tra học kì có hệ số là 3). Em hãy tạo bảng tính theo dõi kết quả học tập của em và lập công thức tính điểm trung bình học kì để khi thay đổi hệ số thì chỉ cần cập nhật hệ số mà không phải lập lại công thức.

PHẦN II. 5 PHÚT TRẢ LỜI CÂU HỎI, BÀI TẬP SGK

KHỞI ĐỘNG

Đáp án KD: 

- Tổng số ca: Tại ô E4 sử dụng công thức: =C4+D4. Sau đó sao chép công thức đến khối ô tính E5:E9 để tính cho những người còn lại.

- Tiền công: Tại ô F4 sử dụng công thức: =E4*F$2. Sau đó sao chép công thức đến khối ô tính F5:F9 để tính cho những người còn lại.

1. ĐỊA CHỈ TƯƠNG ĐỐI

Đáp án L: 

• Ô C10: =SUM(C4:C9)
• Khi sao chép đến ô D10 thì: Địa chỉ cột của ô tính trong công thức thay đổi từ C thành D vì công thức đang tính tổng số ca đêm ở cột D, địa chỉ hàng không đổi vì sự thay đổi này đảm bảo tính tổng các giá trị từ ô D4 đến D9.

2. ĐỊA CHỈ HỖN HỢP, ĐỊA CHỈ TUYỆT ĐỐI

Đáp án L: 

1. =C10*F$2$. 

2. D

LUYỆN TẬP

Đáp án LT1: 

Địa chỉ tương đối, địa chỉ hỗn hợp, địa chỉ tuyệt đối.

Đáp án LT2: D

Đáp án LT3: 1 - c; 2 - d; 3 - a; 4 - b.

Đáp án LT4:

A. 9                       B. 3                        C. 9                        D. 6

THỰC HÀNH

Đáp án TH1:

a) 

• Tổng số ca: Tại ô E4: =C4+D4. Sau đó sao chép đến khối ô tính E5:E9.
• Tiền công: Tại ô F4: =E4*F$2. Sau đó sao chép đến khối ô tính F5:F9.

b) 

• Tổng cộng: Tại ô C10: =SUM(C4:C9), sao chép đến ô D10 và E10.
• Tổng tiền công: Tại ô C11: =C10*$F$2, sao chép đến ô D11 và E11.

c) Lưu tệp. Chèn thêm vào hàng 3 hệ số ca này và ca đêm lần lượt vào các ô C3, D3. Tổng số ca: Tại ô E4: =C4*C$3+D4*D$3, sao chép đến khối ô tính E5:E9.

Đáp án TH2:

a) Tại ô B5: =$A$3*B$4*$A5, sao chép đến khối ô tính B6:B10, C5:D10

b) Kết quả đúng.

Đáp án TH3:

a) Tại ô G5: =F5*E5-D5*$H$3*E5, sao chép từ ô G6 đến G14.

b) Tại ô H5: =(C5-E5)*D5*$H$3, sao chép công thức để tính cho các mặt hàng còn lại.

c) Kết quả ở những ô tính G5:G14, H5:H14 thay đổi. Việc lưu trữ tỉ giá giúp dễ dàng tính toán lại khi tỉ giá có sự thay đổi.

d) Lưu bảng tính.

VẬN DỤNG

Đáp án VD: