Giáo án điện tử chuyên đề Tin học ứng dụng 10 kết nối Bài 4: Hoàn thiện dự toán với hàm tìm kiếm

CHÀO MỪNG CẢ LỚP

ĐẾN BUỔI HỌC NÀY!

KHỞI ĐỘNG

Làm thế nào để điền tự động đơn giá các phòng từ cột B bảng Giá_phòng vào cột D của bảng Dự toán kinh phí?

a) Bảng Giá_phòng ở trang tính Bài 1

b) Bảng Dự toán kinh phí ở trang tính Bài 4

a) Bảng Giá_phòng ở trang tính Bài 1

b) Bảng Dự toán kinh phí ở trang tính Bài 4

Gợi ý:

• Ô D7 có giá trị là 400.000
• Ô D8 có giá trị là 600.000
• Ô D9 có giá trị là 750.000
• Các giá trị này có được dựa vào cột B bảng Giá_phòng ở trang tính Bài 1.

BÀI 4:

HOÀN THIỆN DỰ TOÁN

VỚI HÀM TÌM KIẾM

NỘI DUNG BÀI HỌC

HÀM TÌM KIẾM VLOOKUP

THỰC HÀNH

HÀM TÌM KIẾM

VLOOKUP

1

Hàm tìm kiếm VLOOKUP

Ví dụ

Xếp loại HS theo điểm số:

Sau kì kiểm tra, ta có kết quả bài thi tương ứng với học sinh như bảng 1. Ta cần xếp loại theo điểm số dựa trên bảng 2 (B11:C15).

Hàm tìm kiếm VLOOKUP

Ví dụ

Xếp loại HS theo điểm số:

Kết quả sau khi sử dụng hàm tìm kiếm VLOOKUP:

Hàm tìm kiếm VLOOKUP

• Em hãy nêu và giải thích cú pháp của hàm VLOOKUP.
• Quan sát, tìm hiểu và phân tích ví dụ ở Hình 4.2.

Hình 4.2. Cách hoạt động của hàm VLOOKUP trong Vùng tìm kiếm

Cú pháp của hàm VLOOKUP:

= VLOOKUP(lookup_value; table_array; col_index_ num; [range_lookup])

lookup_value: Giá trị cần tìm kiếm, tra cứu. Có thể là số, đoạn văn bản (đặt trong dấu nháy kép), giá trị logic, hoặc một tham chiếu ô.

table_array: Vùng tìm kiếm chứa giá trị cần tìm. Trong đó, cột chứa giá trị cần tìm phải là cột đầu tiên của vùng tìm kiếm.

Cú pháp của hàm VLOOKUP:

= VLOOKUP(lookup_value; table_array; col_index_ num; [range_lookup])

col_index_num: Số thứ tự của cột chứa kết quả trả về trong vùng tìm kiếm.

range_lookup: Một giá trị logic cho biết hàm VLOOKUP cần phải tìm kết quả chính xác hay tương đối (tham số này có thể có hoặc không).

Nếu nhập TRUE hoặc bỏ qua, hàm VLOOKUP sẽ trả về chính xác giá trị lớn nhất nhỏ hơn giá trị cần tìm.

Nếu nhập FALSE, hàm trả về chính xác giá trị cần tìm. Nếu trong vùng tìm kiếm không có giá trị nào khớp với giá trị tìm kiếm, hàm VLOOKUP sẽ trả về #N/A.

Ví dụ: Công thức lệnh là ô D7 là = VLOOKUP("Phòng đơn"; 'Bài 1'!A3: B6; 2; FALSE) → Trong vùng A3:B6 của trang tính Bài 1, tra cứu hàng dữ liệu nào có giá trị trong cột đầu tiên chính xác là "Phòng đơn", thì kết quả tại cột thứ hai của hàng đó vào ô D7. Kết quả trả về của lệnh này là 400.000 (ô D7 được điền số 400.000).

Hình 4.2. Cách hoạt động của hàm VLOOKUP trong Vùng tìm kiếm

GHI NHỚ

Sử dụng hàm VLOOKUP khi ta cần tìm dữ liệu trong bảng hoặc vùng dữ liệu theo hàng với cú pháp:

VLOOKUP(lookup_value; table_array; col_index _num; [range_lookup])

CÂU HỎI (SGK - TR.42)

1. Trong công thức = VLOOKUP(“Phòng đơn”;‘Bài 1‘!A3:B6;2;FALSE) chỉ ra:

a) Giá trị cần tìm lookup_value. b) Vùng tìm kiếm table_array.

c) Số thứ tự cột chứa kết quả trả về col_index_num trong vùng tìm kiếm.

d) Cách tìm kiếm range_lookup.

2. Với 2 bảng dữ liệu trong Hình 4.1, cho biết ô D8 sẽ được điền kết quả là gì nếu công thức ô đó là:

a) = VLOOKUP(“Phòng đôi”;‘Bài 1’!A3:B6;2;FALSE)

b) = VLOOKUP(C8;‘Bài 1’!A3:B6;2;FALSE)

CÂU HỎI (SGK - TR.42)

3. Viết công thức lệnh VLOOKUP với các yêu cầu sau:

• Giá trị cần tìm lookup_value: nằm ở ô có địa chỉ E10.
• Vùng tìm kiếm table_array: có địa chỉ B3:E10
• Cột chứa kết quả trả về col_index_num trong vùng tìm kiếm là cột C.
• Cách tìm kiếm range_lookup: tim chính xác.

Trả lời

Câu 1. = VLOOKUP("Phòng đơn"; 'Bài 1'! A3:B6; 2; FALSE)

• Giá trị cần tìm lookup_value trong công thức trên là "Phòng đơn".
• Vùng tìm kiếm table_array: Vùng A3: B6 trên trang tính Bài 1.
• Số thứ tự cột chứa kết quả trả về col_index_num là 2. Nghĩa là: trả về kết quả tại cột thứ 2 trong vùng tìm kiếm, cụ thể là cột B.

Trả lời

Câu 2.

a) 600.000

b) 600.000

Câu 3.

= VLOOKUP(E10;B3:E10;2;FALSE)

THỰC HÀNH

2

Hình 4.3. Mẫu Dự toán kinh phí chuyến đi

Nhiệm vụ chung: Sử dụng hàm VLOOKUP để tra cứu, điền đơn giá cho từng loại phòng khách sạn (vùng D7:D9, chữ xanh) và tiếp tục hoàn thiện bảng Dự toán đã thực hiện ở Bài 3 theo mẫu Hình 4.3.

Nhiệm vụ 1: Sử dụng hàm VLOOKUP để tra cứu, điền đơn giá cho các loại phòng khách sạn tại vùng D7:D9 trong Hình 4.3.

Nhiệm vụ 2: Điền tổng tiền dịch vụ phòng khách sạn vào các ô E7:E9 (nền vàng).

Nhiệm vụ 3: Tính tổng chi phí chuyến đi tại ô E15 (Hình 4.3).

Hình 4.3. Mẫu Dự toán kinh phí chuyến đi

Nhiệm vụ 1: Sử dụng hàm VLOOKUP để tra cứu, điền đơn giá

cho các loại phòng khách sạn tại vùng D7:D9 trong Hình 4.3

• Bước 1: Mở tệp Bảng tính chương trình báo giá, lưu lại với tên mới là CT. Baogia. <Tên nhóm>. <Ngày tạo>. xlsx. Đổi tên trang tính Bài 3 thành Bài 3, 4.
• Bước 2: Xây dựng công thức hàm VLOOKUP để điền vào các ô D7, D8 và D9.
• Bước 3: Kiểm tra tính đúng đắn của các công thức đã thực hiện.

Nhiệm vụ 2: Điền tổng tiền dịch vụ phòng khách sạn

vào các ô E7:E9 (nền vàng)

• Bước 1: Thảo luận cách tính tổng tiền dịch vụ phòng khách sạn:
• Tương tự với tổng tiền phòng đôi và phòng ba.
• Bước 2: Điền công thức tính tổng tiền từng loại phòng.

Nhiệm vụ 2: Điền tổng tiền dịch vụ phòng khách sạn

vào các ô E7:E9 (nền vàng)

• Tại ô E7: = D7*B7*B5
• Tại ô E8: = D8*B8*B5
• Tại ô E9: = D9*B9*B5
• Bước 3: Kiểm tra tính đúng đắn của các công thức đã thực hiện.

Hình 4.4. Công thức ô E7

Nhiệm vụ 3: Tính tổng chi phí chuyến đi tại ô E15 (Hình 4.3)

• Bước 1: Thảo luận cách tính tổng chi phí chuyến đi tại ô E15:
• Tổng chi phí chuyến đi = Tổng tiền các loại phòng đơn, phòng đôi, phòng ba + Tổng tiền các dịch vụ khác.
• Bước 2: Điền công thức cho ô E15 bằng hàm SUM đã học:
• Bước 3: Kiểm tra tính đúng đắn của các công thức đã thực hiện.

LUYỆN TẬP

Hình sau là kết quả điểm của một phòng thi và

Bảng thống kê điểm thi của một lớp:

Hình 4.5. Bảng điểm theo phòng thi

Hình 4.6. Bảng điểm theo lớp

1. Quan sát công thức tại ô I3 dưới đây và trả lời các câu hỏi:

= VLOOKUP(“A201”;B3:D10;3;FALSE)

• Giá trị cần tìm lookup_value trong công thức trên là gì?
• Hãy chỉ ra vùng tìm kiếm table_array trong công thức đó.
• Tham số col_index_num bằng 3 nghĩa là gì?
• Công thức này sẽ tìm gần đúng hay tìm chính xác? Tại sao?
• Với công thức này, ô I3 sẽ có giá trị bằng bao nhiêu?

2. Nếu thay thế công thức tại ô I3 trên bằng công thức:

= VLOOKUP(G3;B3:D10;3;FALSE)

thì kết quả trả về tại ô I3 có thay đổi hay không? Tại sao?

3. Tiếp tục điền công thức cho các ô còn lại trong cột I và J.

Gợi ý: Sử dụng địa chỉ tuyệt đối cho vùng tìm kiếm để đảm bảo tính đúng đắn của công thức khi sao chép.

Trả lời:

Câu 1. = VLOOKUP("A201";B3:D10;3; FALSE)

• Giá trị cần tìm lookup_value trong công thức lên là "A201".
• Vùng tìm kiếm table_array: vùng B3:D10 trên trang tính Bảng điểm - Phòng thi số 5.
• Số thứ tự cột chứa kết quả trả về col_index_num là 3. Nghĩa là: trả về kết quả tại cột thứ 3 trong vùng tìm kiếm, cụ thể là D.
• Công thức này sẽ tìm chính xác, vì range_lookup là FALSE.
• Với công thức này, ô I3 có giá trị là 8.

Trả lời:

Câu 2. Nếu thay thế công thức tại ô I3 trên bằng công thức = VLOOKUP (G3; B3:D10; 3; FALSE) thì kết quả trả về tại ô I3 không thay đổi. Vì giá trị cần tìm lookup_value trong công thức trên là tham chiếu ô G3, trùng với "A201".

Bảng 1 là danh sách hành khách đã đăng kí chuyến bay.

Bảng 1. Danh sách hành khách

Bảng 2 là danh sách các hạng vé.

Bảng 2. Danh sách hạng vé

VẬN DỤNG

VẬN DỤNG

--------------- Còn tiếp ---------------