Trang 41
SAU BÀI HỌC NÀY EM SẼ:
• Biết sử dụng hàm VLOOKUP.
+ Vận dụng hảm VLOOKUP để tra cứu tự động đơn giá, hoàn thiện bảng dự toán của Chương trình báo giá.
| Khởi động
Hình 4.1 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 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í? |
HÀM TÌM KIẾM VLOOKUP
Đề đ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í ở hoạt động khởi động, ta có thể dùng công thức như công thức tại ô D7 là = 'Bài 1'!B4. Tuy nhiên, có một cách khác là sử dụng hàm tra cứu, hay còn gọi là hàm tìm kiếm VLOOKUP. Đây là một trong những hàm được đánh giá là hữu ích nhất của phần mềm bảng tính.
Hàm tìm kiếm VLOOKUP có cú pháp như sau:
VLOOKUP(lookup_value; table_array; col_index_num; [range_lookup])
Trong đó:
• lookup_value: Giá trị cần tìm kiếm, tra cứu. Giá trị này 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.
• 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ề giá trị lớn nhất nhỏ hơn giá tị cần tìm.
◊ Nếu nhập FALSE, hàm VLOOKUP 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.
Trang 42
Ví dụ: Công thức lệnh là ô D7 là = VLOOKUP(“Phòng đơn”;'Bài1'!A3:B6;2FALSE) có nghĩa là:
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á tị trong cột
đầu tiên chính xác là "Phòng đơn", thì điền 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, nghĩa là ô 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
1. Tìm Từng hàng trong vùng Tìm kiếm A3:B6, hàng chứa giá trị "Phòng đơn ở cột đầu tên là hàng số 4
2. Trả lại kết quả tại cột thứ hai trên hàng số 4, chính là ô B4
Vùng tìm kiếm A3:B6
Hàm VLOOKUP thực hiện dò tìm theo từng hàng trong vùng tìm kiếm, do đó, VLOOKUP côn được gọi là "hàm tìm kiếm theo hàng”. Hàm tìm kiếm theo cột HLOOKUP.
sẽ được khám phá trong phản Vận dụng.
| 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
Trong công thức = VLOOKUP("Phòng đơn”;'Bài1'A3:B6;2;FALSE) hãy chỉ ra:
a) Giá trị cần tìm lookup_value.
b) Vùng tìm kiếm tabIe_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_lockup.
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ài1"!A3:B6;2;FALSE)
b) = VLOOKUP(C8;'Bài1'!A3:B6;2;FALSE)
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: tìm chính xác.
Lưu ý:
- Tham số col_index_ num là số thứ tự của cột chứa kết quả trả về trong vùng tìm kiếm, do đó, tham số này luôn là một số (chẳng hạn 4, 2, 3....)
Trang 43
- Cách đếm số thứ tự cột trong vùng tìm kiếm: Nếu vùng tìm kiếm là B3:E10, thì ta đếm: 1: cột B; 2: cột C; 3: cột D; 4: cột E.
THỰC HÀNH
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 như mẫu dưới đây:
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.
Biết rằng:
- D7 là ô cần điền đơn giá của loại phòng đơn.
- Đơn giá này nằm ở cột thứ 2 trong bảng Giá_ phòng ở trang tính Bài 1, hay chính là vùng dữ liệu 'Bài1'!A3:B6
Có thể mô tả bằng lời công thức cần thiết cho ô D7 như sau: Tra cứu trong bảng Giá_phòng, tìm hàng có cột đầu tiên là "Phòng đơn", trả về kết quả ở cột thứ 2 cùng
hàng đó.
Trang 44
Như vậy, dễ dàng xác định 4 tham số của hàm theo bằng sau:
| Tham số | Giá trị |
| Ioolup_value | C7 hoặc "phòng đơn" |
| table_array | 'Bài 1'!A3:B6 hoặc Giá_phòng |
| col_index_num | 2 |
| range_lookup | FALSE |
Từ đó suy ra công thức ô D7 là:
=VLOOKUP(“phòng đơn”;'Bài1'!A3:B6;2;FALSE)
hoặc viết gọn lại là
= VLOOKUP(C7;Giá_phòng;2;FALSE)
Thực hiện tương tự để tìm và điền công thức tại các ô 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).
Hướng dẫn:
Bước 1: Thảo luận cách tính tổng tiền dịch vụ phòng khách sạn đã xác định ở tiết thực hành Bài 1
Tổng tiền phòng đơn = số lượng phòng đơn x đơn giá phòng đơn x số đêm khách ở.
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.
Như đã thảo luận tại Bước 1, ta có công thức tổng tiền phòng đơn tại ô E7 như hình dưới đây.
Hình 4.4. Công thức 6 E7
Tương tự công thức ô E8 và E9 lần lượt là
=D8*B8*B5
=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.
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 ô E16.
Tổng chi phí chuyến đi = Tổng tiền các loại phòng đơn, đôi, ba + Tổng tiền các dịch vụ khác.
Trang 45
Bước 2: Điền công thức cho ô E16 bằng hàm SUM đã học:
= SUM(E7:E14)
Bước 3: Kiểm tra tính đúng đắn của các công thức đã thực hiện.
Hãy nhớ lưu lại bài làm của em.
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, ô l3 sẽ có giá trị bằng bao nhiêu?
2. Nếu thay thế công thức tại ô l3 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.
Trang 46
VẬN DỤNG
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
| TT | Tên khách | Mã vé | Hạng vé |
| 1 | Ngô Văn Quý | DE | |
| 2 | Nguyễn Quý An | E | |
| 3 | Lê Đức Hải | E2 | |
| 4 | Đỗ Thế Dũng | 5B | |
| 5 | Nguyễn Thị Giang | B | |
| 6 | Hà Thị Châu | E1 |
Bảng 2 là danh sách các hạng vé.
Bảng 2. Danh sách hạng vé
| Kí hiệu | Hạng vé |
| B | Thương gia |
| DE | Phổ thông đặc biệt |
| E | Phổ thông linh hoạt |
| E1 | Phổ thông tiêu chuẩn |
| E2 | Phổ thông tiết kiệm |
1. Tìm công thức phù hợp để điền thông tin tương ứng vào cột hạng vé của Bảng 1.
2. Khám phá cách sử dụng hàm HLOOKUP để thực hiện yêu cầu tại câu 1 khi Bảng 2 được trình bày lại như Bảng 3 dưới đây.
Bảng 3. Danh sách hạng vé
| Kí hiệu | B | DE | E | E1 | E2 |
| Hạng vé | Thương gia | Phổ thông đặc biệt | Phổ thông linh hoạt | Phổ thông tiêu chuẩn | Phổ thông tiết kiệm |
3. Mô tà sự khác nhau về cách thức hoạt động của hàm VLOOKUP và HLOOKUP dựa vào Hình 4.7 dưới đây, từ đó, rút ra kết luận khi nào sử dụng VLOOKUP, khi nào sử dụng HLOOKUP.
Hình 4.7. Mô tả hoạt động hàm VLOOKUP và hàm HLOOKUP
=VLOOKUP("X";A2:E7;4;FALSE)
=HLOOKUP("X";G2:L7;4;FALSE)
