Bài giảng Môn Tin Học lớp 7 - Bài nhóm hàm tìm kiếm và tham chiếu

 1
Bài 
NHÓM HÀM TÌM KIẾM VÀ THAM CHIẾU 
(Học phần Tin học cơ sở - Tin học văn phòng) 
Những kiến thức sinh viên đã 
biết có liên quan đến bài học 
Những kiến thức mới trong bài học cần 
được hình thành 
- Biết cách tính toán dữ liệu 
trên bảng tính. 
- Sử dụng thành thạo các hàm 
tính toán số liệu. 
- Kỹ năng xử lý dữ liệu bằng các hàm của 
Excel. 
- Sử dụng linh hoạt các hàm tính toán số liệu 
để xử lý công việc. 
- Sử dụng hàm tìm kiếm và tham chiếu để 
tính toán dữ liệu 
A. Mục đích – Yêu cầu 
1. Kiến thức: 
- Hiểu được việc xử lý số liệu trong bảng tính là việc quan trọng khi phải làm việc trên Excel; 
- Biết vận dụng, phối hợp các hàm tính toán dữ liệu khác nhau để tính toán, xử lý dữ liệu đạt hiệu quả công việc. 
2. Kỹ năng 
- Biết phân tích các yêu cầu của một bài toán cụ thể; 
- Nâng cao kỹ năng xử lý bảng tính bằng các công cụ có sẵn của Excel; 
3. Thái độ: 
- Chủ động tìm hiểu các yêu cầu của công việc được giao; 
- Tích cực suy nghĩ, trao đổi, phát vấn để giải quyết công việc được giao khi học. 
B. Chuẩn bị 
1. Đồ dùng dạy học 
- Giảng viên: Giáo trình tin học cơ sở+ Tài liệu tham khảo; các bài tập in sẵn; bài giảng. 
- Học sinh: Giáo trình tin học cơ sở, tài liệu tham khảo, vở ghi. 
2. Phương pháp dạy học 
- Nêu và giải quyết vấn đề 
- Phương pháp gợi ý, câu hỏi và khung thông tin cho trước. 
 2
C. Các hoạt động dạy - học 
Thời 
gian 
Nội dung Hoạt động của giảng viên Hoạt động của học sinh 
Đồ dùng, thiết 
bị dạy học 
5’ 
Nhắc lại kiến thức đã học bài trước Ổn định tổ chức lớp Giấy A3 in cú 
pháp hàm IF 
15’ 
1. Bài toán -Nêu yêu cầu học sinh sử 
dụng hàm của Excel để hoàn 
thành dữ liệu cho bảng tính. 
-Quy định thời gian 5’ để trao 
đổi 
-Yêu cầu học sinh lên bảng 
trình bày kết quả 
- Nhận xét và bổ sung vào kết 
quả 
-Học sinh quan sát bài tập trên 
bảng rồi trao đổi, thảo luận 
giải quyết các yêu cầu. 
- Học sinh lên bảng trình bày 
kết quả bằng cách viết phấn. 
Bài tập đã 
chuẩn bị sẵn. 
Đưa ra hướng 
giải quyết 
1’ 
2. Hàm tìm kiếm và tham chiếu - Giới thiệu nhóm hàm mới 
xử lý bài toán tương tự hiệu 
quả hơn, triệt để hơn. 
- Học sinh lắng nghe, ghi vở, 
tham khảo trong giáo trình. 
4’ 
a. Cú pháp hàm VLOOKUP Nêu cú pháp của hàm 
VLOOKUP cho cả lớp theo 
dõi 
- Học sinh theo dõi, ghi vở Giấy A0 có cú 
pháp hàm 
Vlookup 
10’ 
b. Giải thích -Giải thích các tham số, ý 
nghĩa của hàm Vlookup 
- Phát vấn học sinh 
- Nêu một số chú ý khi sử 
dụng hàm Vlookup 
 + Phải lập được và sắp xếp 
tăng dần cột đầu tiên trong 
bảng tham chiếu thì mới sử 
dụng hàm này được. 
- Học sinh theo dõi, ghi vở 
- Học sinh trả lời 
Giấy A0 có giải 
thích rõ 
 3
Thời 
gian 
Nội dung Hoạt động của giảng viên Hoạt động của học sinh 
Đồ dùng, thiết 
bị dạy học 
 + Phải khai báo địa chỉ tuyệt 
đối cho phần bảng tham chiếu 
trong hàm Vlookup thì mới 
sao chép được. 
10’ 
c. Ví dụ minh họa - Nêu yêu cầu giải quyết bài 
toán ở bài toán 1 nhưng 
không sử dụng hàm IF 
- Hướng dẫn học sinh kẻ thêm 
bảng tham chiếu và điền dữ 
liệu. 
- Phát vấn học sinh trả lời 
hướng giải quyết 
- Học sinh tham khảo giáo 
trình, trao đổi thảo luận và 
nêu ra hướng giải quyết. 
- Học sinh theo dõi và thực 
hiện theo hướng dẫn của GV. 
- Đại diện học sinh lên trả lời. 
- Đưa ra bài tập 
D. Củng cố kiến thức 
- Nhắc nhở học sinh về nhà nghiên cứu kỹ hơn và giải quyết các bài toán tương tự. 
- Thực hành bài tập đã tìm hiểu trong buổi học để kiểm chứng kết quả. 
- Tương tự hàm Vlookup còn có HLookup cũng có tác dụng tìm kiếm và trả về kết quả tham chiếu. Tuy nhiên, sử dụng hàm 
HlookUp thì người dùng phải tổ chức bảng tham chiếu có các giá trị tìm kiếm tham chiếu theo hàng. 
 4
HÀM TÌM KIẾM VÀ THAM CHIẾU VLOOKUP 
Cú pháp: 
VLOOKUP(Giá trị tìm, Vùng tham chiếu, Cột tham chiếu, Điều kiện tìm) 
Giải thích: 
1. Giá trị tìm: Chỉ ra giá trị cần tìm để đối chiếu trong bảng tham chiếu. Tham số này thường 
là điều kiện để xét lấy kết quả. Ví dụ: Chức vụ, Điểm tổng, Điểm Trung bình... 
2. Vùng tham chiếu: Vùng này là điều kiện để đối chiếu với Giá trị tìm. Vùng này còn được 
gọi là Bảng tham chiếu vì người sử dụng phải tạo ra để hàm VlookUp so sánh và trả về kết 
quả tương ứng. Thông thường, tạo bảng này bên phải bảng dữ liệu gốc và cột đầu tiên là dữ 
liệu dùng để đối chiếu, các cột còn lại là cột trả về kết quả tham chiếu. 
3. Cột tham chiếu: Là giá trị số tương ứng với thứ tự cột trong Vùng tham chiếu. Khi thực 
hiện, Vlookup đối chiếu Giá trị tìm với cột đầu tiên của Vùng tham chiếu, nếu cho kết quả 
đúng (tìm thấy) thì lấy giá trị nằm trong Cột tham chiếu tương ứng để lấy ra kết quả trả về. 
Nếu không tìm thấy thì sẽ so sánh tiếp các giá trị khác ở hàng tiếp theo của vùng tham 
chiếu và lấy ra kết quả tương ứng. 
4. Điều kiện tìm: là một giá trị số 0 hoặc 1 quy định việc tìm chính xác hoặc tương đối. Nếu 
Điều kiện tìm là 0 thì Vlookup sẽ tìm những giá trị gần đúng, bằng hoặc lớn hơn giá trị tìm. 
Nếu điều kiện tìm là 1 thì Vlookup sẽ tìm chính xác giá trị tìm có nằm trong bảng tham 
chiếu hay không. Nếu có thì trả về kết quả còn không sẽ trả về giá trị #N/A. (Không có kết 
quả tương ứng) 
 5
Bài toán 1: 
Dùng các hàm đã học trong phần mềm Excel để hoàn thiện bảng tính sau: 
KẾT QUẢ THI TỐT NGHIỆP THPT NĂM 2008 
 A B C D E F G H I J K 
1 SBD Họ và tên Ngày sinh Toán Lý Hóa Văn Anh Điểm 
tổng 
Kết 
quả 
XLTN 
2 100412 Hoàng Thị An 12/08/1990 5.5 6.0 7.0 5.5 7.5 
3 100413 Lê Văn Bình 20/04/1990 4.0 2.5 5.0 4.5 6.0 
4 100414 Nguyễn Văn Hải 22/04/1990 8.0 9.0 8.5 7.5 9.5 
5 100415 Dương Văn Giang 06/09/1990 7.5 7.5 8.0 7.0 8.0 
1. Yêu cầu: 
5. Viết công thức tính điểm tổng cho bảng trên dựa vào công thức Điểm 
tổng=Toán+Lý+Hóa+Văn+Anh 
6. Xét kết quả thi tốt nghiệp dựa vào các tiêu chí: 
- Đỗ: Nếu điểm tổng >=25 
- Hỏng: Nếu điểm tổng <25 
7. Xếp loại tốt nghiệp cho học sinh 
Giỏi nếu Điểm tổng>= 40; Khá nếu Điểm tổng >= 32.5; Trung bình nếu Điểm tổng>=25 
2. Hướng giải quyết: Dùng hàm Sum để tính tổng điểm và hàm IF để xét kết quả 
- Tại ô I2 nhập công thức: = SUM(D2:H2) 
- Tại ô J2 nhập công thức: = IF(I2>=25, “Đỗ”, “Hỏng) 
 6
Bài toán 2: 
 BẢNG KẾT QUẢ HỌC TẬP NĂM 2008 
 A B C D E F G 
1 Stt Họ và tên TB Chung Xếp loại học tập Điểm Xếp loại 
2 1 Hoàng Văn Chung 8.3 0 Kém 
3 2 Vi Thị Chuyên 5.4 4 Yếu 
4 3 Nông Văn Hùng 6.2 5 Trung bình 
5 4 Lê Thị Huyên 4.3 6.5 Khá 
6 5 Lý Văn Hưng 5.8 8 Giỏi 
7 6 Vi Xuân Thanh 8.2 9 Xuất sắc 
8 7 Nguyễn Văn Thành 7.6 
9 8 Trịnh Thị Thúy 8.5 
Yêu cầu: 
- Lập bảng tham chiếu 
- Dùng hàm Vlookup để xét Xếp loại học tập cho học sinh 
Hướng dẫn: 
- Tạo bảng tham chiếu ngang hàng 
với bảng dữ liệu nhưng cách cột 
cuối cùng ít nhất 1 cột. 
- Tại ô D2 sử dụng công thức: 
=Vlookup(C2,$F$2:$G$7,2,0) CHÚ Ý! 
- HƯỚNG DẪN SỬ DỤNG CÁCH TẠO TÊN VÙNG DỮ LIỆU 
- DẪN NHẬP CÁCH SỬ DỤNG HÀM VLOOKUP CHO VIỆC 
TRUY TÌM CÁC TÊN DÀI, KHÓ NHẬP VÀO MÁY TÍNH NHƯ 
TÊN THUỐC, TÊN THIẾT BỊ