Tài liệu Microsoft office excel 2003

: Sau khi chọn kiểu và màu sắc cho đường kẻ, Click vào các biểu tượng kẻ khung để kẻ khung cho khối dữ liệu đã chọn.  Lưu ý: Có thể sử dụng biểu tượng Borders trên thanh Formatting. Bật/tắt các đường kẻ ô trong bảng tính: Dùng Tools  Options  View  Gridlines Chọn Gridlines để hiện đường lưới Trang 17 / 59 4.5 Tạo màu nền cho ô  Đánh dấu nội dung cần tạo màu nền.  Dùng Format  Cells, chọn Tab Patterns hoặc chọn biểu tượng (Fill Color) trên thanh Formatting. 4.6 Tóm tắt 4.6.1 Câu hỏi 1. Nêu cách mở hộp thoại Format Cells? 2. Trong hộp thoại Format Cells gồm những Tab nào? 3. Để định dạng cách hiển thị dữ liệu kiểu số và ngày tháng ta sử dụng Tab nào? 4. Để định dạng kiểu chữ ta sử dụng Tab nào? 5. Để định dạng vị trí, căn biên và hướng xoay của văn bản ta sử dụng Tab nào? 6. Để kẻ khung cho bảng tính ta sử dụng Tab nào? 7. Để tạo màu nền cho ô ta sử dụng Tab nào? 4.7 Bài tập Bài 1: BÁO CÁO BÁN HÀNG THÁNG 8 NĂM 2005 A B C D E F G Trang 18 / 59 1 S TT Tên Hàng Ngày Bán Số Lượng Đơn Giá Thành Tiền Tỷ Lệ 2 1 Xi măng 8/8/2005 123 USD 15 3 2 Sắt 9/8/2005 12 USD 20 4 3 Thép 11/8/2005 158 USD 54 5 4 Cát 15/08/05 1456 USD 25 6 5 Vôi 17/08/05 1236 USD 10 7 6 Gạch 20/08/05 1236 USD 24 8 7 Gạch thẻ 21/08/05 1569 USD 15 9 8 Gạch bông 22/08/05 1456 USD 12 10 9 Sơn 27/08/05 12 USD 67 11 Tổng Cộng Yêu cầu: 1. Nhập và lưu lại tập tin với tên BAOCAO.xls. 2. Định dạng bảng tính như trên. 3. Thành tiền = số lượng * đơn giá. 4. Tỷ lệ = tiền của từng mặt hàng / tổng tiền của các mặt hàng. Bài 2 Yêu cầu: 1. Tạo tập tin và lưu với tên PHANBOHH.XLS. 2. Nhập dữ liệu và trình bày bảng tính như trên. 3. Định dạng sao cho có chữ “VNĐ” trong cột ĐƠN GIÁ. 4. Thành tiền của từng đại lý = số lượng * đơn giá. 5. Cộng thành tiền: Là tổng thành tiền của từng mặt hàng của các đại lý. 6. Tỷ lệ: Là tỷ lệ thành tiền của từng mặt hàng so với giá trị của toàn bộ mặt hàng. 7. Copy các công thức cho các ô còn lại. Trang 19 / 59 Bài 3 Yêu cầu: 8. Tạo tập tin và lưu trên đĩa C:\BAOCAO.XLS. 9. Nhập dữ liệu và trình bày bảng tính như trên. 10. Định dạng sao cho có chữ “USD” trong cột ĐƠN GIÁ. 11. Thành tiên = số lượng * đơn giá. 12. Tỷ lệ = tiền của mặt hàng / tổng số tiền. 13. Copy các công thức cho các ô còn lại. 5 Bài 4: Các thao tác dữ liệu đặc biệt trong Excel 5.1 Các thao tác Copy và Paste Special Trong một ô dữ liệu có thể gồm nhiều thành phần: Giá trị (Value), công thức (Formula), định dạng (Format). Microsoft Excel cho phép sao chép một trong các thành phần đó từ ô này qua ô khác.  Cách thức Copy các thành phần dữ liệu:  Chọn vùng cần sao chép.  Chọn Edit  Copy (hoặc nhấn Ctrl + C).  Chọn vùng đích.  Chọn Edit  Paste Special, hộp thoại Paste Special xuất hiện: Trang 20 / 59  Chọn Formulas: Chép công thức trong ô.  Chọn Values: Chép giá trị trong ô.  Chọn Formats: Chép định dạng trong ô.  Chọn All: Chép toàn bộ.  Transpose: Chuyển đổi dòng thành cột, cột thành dòng. 5.2 Thao tác trên Sheet  Chọn Sheet: Click vào tên Sheet để chọn Sheet làm việc.  Đổi tên Sheet  Click phải chuột lên Sheet cần đổi tên.  Chọn mục Rename.  Nhập vào tên mới, nhập xong nhấn Enter để hoàn thành việc đổi tên.  Chèn thêm Sheet  Click phải lên Sheet phía bên phải Sheet cần chèn.  Chọn Insert.  Xoá bỏ Sheet  Click phải lên Sheet cần xoá.  Chọn Delete.  Xuất hiện hộp thoại, chọn Delete. 5.3 Liên kết dữ liệu Trong Microsoft Excel ta có thể Copy hoặc liên kết dữ liệu giữa các bảng tính với nhau.  Chép dữ liệu giữa 2 bảng tính (hoặc trang bảng tính).  Mở 2 bảng tính (hoặc trang bảng tính) trên 2 của sổ.  Đến bảng tính nguồn, chọn vùng muốn chép.  Thực hiện lệnh Edit  Copy.  Đến bảng tính đích. Trang 21 / 59  Chọn Edit  Paste Special.  Có thể thực hiện các phép toán giữa vùng nguồn và vùng đích như sau:  None: Chép không tính toán.  Add: Cộng vùng nguồn với vùng đích, kết quả để ở vùng đích.  Subtract: Trừ vùng đích với vùng nguồn, kết quả để ở vùng đích.  Multiply: Nhân vùng nguồn với vùng đích, kết quả để ở vùng đích.  Divide: Chia vùng nguồn với vùng đích, kết quả để ở vùng đích.  Tham chiếu dữ liệu giữa các Sheet và bảng tính khác nhau: Trong Microsoft Excel, ta có thể tham chiếu đến các địa chỉ của các ô trong bảng tính khác hoặc trong Sheet khác nhau như tham chiếu đến địa chỉ của các ô khác trong cùng Sheet. Ví dụ: Công thức: = [Book1.xls]Sheet1!$C$13+100 Sẽ cho kết quả bằng giá trị ở ô C13 thuộc Sheet1 của bảng tính có tên là Book1 cộng với 100. Vậy công thức tổng quát để tham chiếu đến một địa chỉ ô trên bảng tính khác có dạng như sau:  Lưu ý: Có thể tham chiếu đến một ô nào đó ở bảng tính khác bằng cách: Khi đang lập công thức cho ô cần tính, Click vào ô cần tham chiếu ở bảng tính khác để lấy địa chỉ. 5.4 Sắp xếp cơ sở dữ liệu  Bước 1: Chọn phạm vi các mẫu tin trong cơ sở dữ liệu cần sắp xếp.  Bước 2: Chọn Data  Sort, xuất hiện hộp thoại Sort. [tên bảng tính]tên Sheet!địa chỉ ô cần tham chiếu Trang 22 / 59  Bước 3: Chọn tên cột tiêu chuẩn sắp xếp thứ nhất trong mục Sort by, chọn 1 trong 2 kiểu sắp xếp: Ascending: tăng dần, Descending: giảm dần.  Bước 4: Nếu sắp xếp theo nhiều tiêu chuẩn thì chọn cột xét tiêu chuẩn tiếp theo trong mục Then by…  Bước 5: Chọn OK.  Lưu ý: Trong cửa sổ My data range has, chọn Header Row nếu vùng chọn có bao gồm tiêu đề và ngược lại. 5.5 Tóm tắt 5.5.1 Câu hỏi 1. Tiện ích của lệnh Paste Special là gì? 2. Chức năng của lệnh Sort dùng để làm gì? 5.6 Bài tập BẢNG KÊ NHẬP HÀNG THÁNG 3/2005 A B C D 1 STT Mặt Hàng Đơn Vị Tính Nhập Trong Tháng 2 1 Đồng hồ SELKO Cái 21 3 2 Tủ lạnh HITACHI Cái 30 4 3 Quạt bàn Cái 160 5 4 Bàn ủi Thái Lan Cái 25 6 5 Bia BGI Thùng 500 7 6 Bia Sài Gòn Thùng 900 8 7 Bia Tiger Thùng 700 9 8 TV màu Sony Cái 90 10 9 Đĩa CD ROM Hộp 250 Trang 23 / 59 BẢNG KÊ XUẤT HÀNG THÁNG 3/2005 A B C D 1 STT Mặt Hàng Đơn Vị Tính Xuất Trong Tháng 2 1 Đồng hồ SELKO Cái 12 3 2 Tủ lạnh HITACHI Cái 29 4 3 Quạt bàn Cái 160 5 4 Bàn ủi Thái Lan Cái 26 6 5 Bia BGI Thùng 468 7 6 Bia Sài Gòn Thùng 850 8 7 Bia Tiger Thùng 680 9 8 TV màu Sony Cái 86 10 9 Đĩa CD ROM Hộp 240 BẢNG TÍNH XUẤT - NHẬP - TỒN THÁNG 3/2005 A B C D E F G 1 STT Mặt Hàng Đơn Vị Tính Tồn Cuối Tháng 2 Xuất Nhập Tồn Cuối Tháng 2 1 Đồng hồ SELKO Cái 20 3 2 Tủ lạnh HITACHI Cái 15 4 3 Quạt bàn Cái 156 5 4 Bàn ủi Thái Lan Cái 12 6 5 Bia BGI Thùng 58 7 6 Bia Sài Gòn Thùng 12 8 7 Bia Tiger Thùng 84 9 8 TV màu Sony Cái 15 10 9 Đĩa CD ROM Hộp 159 Yêu cầu: 1. Trình bày bảng nhập hàng trên Sheet1. Đổi tên Sheet1 thành NhapHang. 2. Trình bày bảng xuất hàng trên Sheet2. Đổi tên Sheet2 thành XuatHang. 3. Trình bày bảng tính tồn trên Sheet3. Đổi tên Sheet3 thành Ton. 4. Sử dụng lệnh Paste Special: Copy 2 cột nhập, xuất của 2 bảng nhập hàng và xuất hàng dán vào 2 cột nhập và xuất của bảng tồn (chỉ Copy phần giá trị - Value) 5. Tính tồn cuối tháng = tồn đầu tháng + nhập – xuất. Trang 24 / 59 IF(logical_test, [value_if_true], [value_if_false]) OR(logical1, [logical 2], …) AND(logical1, [logical 2], …) Tên_hàm(các tham số) 6 BÀI 5: Giới thiệu hàm trong Excel 6.1 Giới thiệu  Excel cung cấp nhiều hàm hỗ trợ tính toán và thống kê dữ liệu.  Toàn bộ các hàm Excel được liệt kê trong biểu tượng (Insert Function).  Cú pháp chung của hàm:    Ví dụ: = SUM(4, 5) Sum là tên hàm và 4, 5 là 2 tham số.  Lưu ý: Tên hàm trong Excel không phân biệt chữ thường và chữ hoa. 6.2 Một số hàm Logic 6.2.1 Hàm AND  Cú pháp:   Với logical là biểu thức.  Ý nghĩa: Trả về giá trị đúng khi tất cả các biểu thức tham gia đều đúng.  Ví dụ 1: = AND(7 > 5, 3 > 2)  Hàm trả về kết quả là TRUE.  Ví dụ 2: = AND(7 > 5, 6 > 8)  Cho kết quả là FALSE. 6.2.2 Hàm OR  Cú pháp:   Với logical là biểu thức.  Ý nghĩa: Trả về giá trị đúng khi một trong các biểu thức tham gia có giá trị đúng.  Ví dụ 1: = OR(7 > 5, 3 < 2)  Hàm trả về kết quả là TRUE.  Ví dụ 2: = OR(7 8)  Hàm trả về kết quả là FALSE.  Ví dụ 3: = OR(7 < 5, 6 <= 8, 1 = 1)  Hàm trả về kết quả là TRUE. 6.2.3 Hàm điều kiện IF  Cú pháp:   Logical_test: Là biểu thức điều kiện.  Value_if_true: Là giá trị nếu đúng.  Value_if_false: Là giá trị nếu sai.  Ý nghĩa: Hàm sẽ kiểm tra biểu thức điều kiện, nếu biểu thức điều kiện đúng thì biểu thức giá trị đúng sẽ được tính, ngược lại thì biểu thức điều kiện sai sẽ được tính.  Ví dụ 1: = IF(7 > 5, 10 + 2, 5 * 2)  Cho kết quả là 12.  Ví dụ 2: = IF(7 < 5, 50, 100)  Cho kết quả là 100. Trang 25 / 59 LOWER(text) MID(text, start_num, num_chars) RIGHT(text, [num_chars]) LEFT(text, [num_chars]) 6.3 Các hàm xử lý chuỗi 6.3.1 Hàm trích chuỗi 6.3.1.1 Hàm LEFT  Cú pháp:   Text: Là chuỗi ký tự gốc.  Num_chars: Số ký tự muốn lấy.  Ý nghĩa: Trích ra n ký tự ở bên trái của chuỗi được chỉ định.  Ví dụ: = LEFT("abcd", 2)  Trả về giá trị là "ab". = LEFT("abcd")  Trả về giá trị là "a" (Nếu bỏ qua tham số thứ 2 thì sẽ tự động hiểu là giá trị 1). 6.3.1.2 Hàm RIGHT  Cú pháp:   Text: Là chuỗi ký tự gốc.  Num_chars: Số ký tự muốn lấy.  Ý nghĩa: Trích ra n ký tự ở bên phải của chuỗi được chỉ định.  Ví dụ: = RIGHT("abcd", 3)  Trả về giá trị là "bcd".  = RIGHT("abcd",1)  Trả về giá trị là "d". 6.3.1.3 Hàm MID  Cú pháp:   Text: Là chuỗi ký tự gốc.  Start_num: Là vị trí bắt đầu.  Num_chars: Số ký tự muốn lấy.  Ý nghĩa: Trích ra n ký tự tính từ vị trí thứ p của chuỗi được chỉ định.  Ví dụ: = MID("abcd", 2, 2)  Trả về giá trị là "bc". = MID("abcd", 3, 2)  Trả về giá trị là "cd". 6.3.2 Hàm đổi chuỗi 6.3.2.1 Hàm LOWER  Cú pháp:   Ý nghĩa: Chuyển hết thành ký tự thường trong chuỗi.  Ví dụ: = LOWER(“Trường Thế Giới Tin Học – Ngoại Ngữ Infoworld”)   Cho kết quả là: trường thế giới tin học – ngoại ngữ infoworld. Trang 26 / 59 "text 1" & "text 2" & … PROPER(text) UPPER(text) 6.3.2.2 Hàm UPPER  Cú pháp:   Ý nghĩa: Chuyển hết thành ký tự in hoa trong chuỗi.  Ví dụ: = UPPER (“ infoworldshool”)  Cho kết quả là: INFOWORLDSHOOL. 6.3.2.3 Hàm PROPER  Cú pháp:   Ý nghĩa: Chuyển ký tự đầu của mỗi từ thành chữ in hoa.  Ví dụ: = PROPER(“microsoft excel”)   Cho kết quả là: Microsoft Excel. 6.3.2.4 Toán tử nối chuỗi: &  Cú pháp:   Với text 1 và text 2 là hai chuỗi.  Ví dụ: = "tôi sẽ " & "làm được bài này"  Cho kết quả là: tôi sẽ làm được bài này. 6.4 Tóm tắt 6.4.1 Câu hỏi 1. Trong Excel, để biến chữ thường thành chữ in hoa, ta dùng lệnh, hàm hay tổ hợp phím nào sau đây: a. Hàm LOWER b. Shift + F3 c. Hàm UPPER d. Format  Change Case 2. Trong Excel, hãy cho biết kết quả của công thức sau: = MID(“A0B1C2”, 2, 3). a. B1 b. B1C c. 0B1 d. 1C2 3. Trong Excel, ô A1 = AB10C, hãy cho biết kết quả của công thức sau = IF(MID(A1, 3, 1) = 1, 100000, 200000). a. 100000 b. 200000 c. Quá ít đối số. d. Quá nhiều đối số. Trang 27 / 59 4. Trong Excel a. Lập công thức tính toán trước nhập dữ liệu sau. b. Nhập dữ liệu trước, nhập công thức tính toán sau. c. Cả hai câu a và b đều sai. d. Cả hai câu a và b đều đúng. 5. Trong Excel, giả sử ô A1 có giá trị ABCDE, muốn lấy 3 ký tự CDE ta dùng hàm nào sau đây: a. = RIGHT(A1, 3) b. = MID(A1, 3, 3) c. Cả câu a và b đều đúng. d. Cả câu a và b đều sai. 6.5 Bài tập 6.5.1 Bài tập 1 BẢNG TÍNH TIỀN THƯỞNG THÁNG 8/2005 A B C D E F G 1 ST T Họ Và Tên Chức Vụ Lương Cơ Bản Ngày Công Tiền Thưởng Ký Nhận 2 1 Trần Sang Giám Đốc 550,000 26 3 2 Trương Thị Chi Phó Giám Đốc 435,000 24 4 3 Nguyễn Thị Hai Trợ Lý 379,000 25 5 4 Phạm Thị Hà Kế Toán 350,000 18 6 5 Trần Hoà Lê Kế Toán 345,000 16 7 6 Nguyễn Bính Kế Toán 345,000 26 8 7 Trần Nam Nhân Viên VT 273,000 25 9 8 Vũ Thuý Bình Nhân Viên VT 405,000 25 10 9 Hà Cẩm Lan Lễ Tân 425,000 26 11 10 Nguyễn Huy Lễ Tân 237,000 22 12 11 Hà Lan Anh Thủ Kho 237,000 22 13 Tổng Cộng Yêu cầu: 1. Nhập và lưu tập tin với tên TIENTHUONG.xls. 2. Định dạng bảng tính như trên. 3. Cột tiền thưởng được tính như sau:  Nếu ngày công >= 22 thì tiền thưởng là tháng lương cơ bản, còn ngược lại sẽ không có tiền thưởng. 4. Tính tiền thưởng của mỗi nhân viên và tổng số tiền thưởng. Trang 28 / 59 6.5.2 Bài tập 2 BÁO CÁO XUẤT KHẨU HÀNG HOÁ A B C D E F G H I 1 S TT Tên Hàng Ký Hiệu Th án g N ăm Mã Nước SL ĐG Thành Tiền 2 1 Thể Thao Mùa Đông SPW1201NOW 45000 16 3 2 Jacket JKT1101FRA 7000 21 4 3 Quần Áo Trẻ Em CHL1101ENG 16000 7 5 4 Bảo Hộ Lao Động NIK0901POR 6500 8 6 5 Thể Thao Mùa Đông SPW1201DEN 2000 16 7 6 Jacket JKT0102GER 800 20 8 7 Thể Thao Mùa Hè SPS1002JAP 400 15 9 Tổng Cộng Yêu cầu: 1. Nhập và lưu tập tin với tên BAOCAO.xls. 2. Định dạng bảng tính như trên. 3. Cột tháng: Căn cứ vào ký tự thứ 4 và thứ 5 của cột ký hiệu hàng hoá. 4. Cột năm: Căn cứ vào ký tự thứ 6 và thứ 7 của cột ký hiệu hàng hoá. 5. Cột mã nước: Căn cứ vào 3 ký tự cuối của cột ký hiệu hàng hoá. 6. Cột thành tiền = số lượng * đơn giá. 6.5.3 Bài tập 3 Yêu cầu: Trang 29 / 59 14. TÊN HÀNG: Nếu kí tự bên trái của MÃ HÀNG là “B” thì ghi “Bàn”, “G” thì ghi “Ghế”, còn ngược lại ghi “Tủ”. 15. ĐƠN GIÁ: a. Nếu kí tự bên trái của MÃ HÀNG là “B” thì ĐƠN GIÁ =80000 , “G” thì ĐƠN GIÁ =15000, “T” thì ĐƠN GIÁ = 250000. b. TRỊ GIÁ = SỐ LƯỢNG * ĐƠN GIÁ. 16. TỈ LỆ THUẾ: a. Nếu ký tự bên phải của MÃ HÀNG là “1” thì TỈ LỆ THUẾ = 10%, ngược lại thì TỈ LỆ THUẾ =5%. b. THUẾ = TRỊ GIÁ * TỈ LỆ THUẾ. c. DOANH THU = TRỊ GIÁ - THUẾ. 6.5.4 Bài tập 4 Yêu cầu: 17. LỚP HỌC: Nếu kí tự bên trái của MÃ SỐ là D thì ghi “Đồ Họa” Nếu kí tự bên trái của MÃ SỐ là Q thì ghi “Quản Lý” Nếu kí tự bên trái của MÃ SỐ là V thì ghi “Văn Phòng” BÌNH QUÂN = (MÔN 1 + MÔN 2 + MÔN 3) / 3 18. KẾT QUẢ : Nếu BÌNH QUÂN >=5 thì ghi “ĐẠT”,ngược lại thì ghi “THI LẠI” XẾP LOẠI : theo tiêu chuẩn sau: Nếu BÌNH QUÂN >=9 thì ghi “Giỏi” , nếu BÌNH QUÂN >=7 thì ghi “Khá”, nếu BÌNH QUÂN >=5 thì ghi “TB”, ngược lại thì ghi “YẾU” Trang 30 / 59 VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) 6.5.5 Bài tập 5 Yêu cầu: 1. Tạo tập tin và lưu trên đĩa C:\KQ_TUYENSINH.XLS. 2. Chèn vào trước cột Toán 2 cột NGÀNH THI và KHU VỰC; trước cột KẾT QUẢ 2 cột TỔNG CỘNG v à ĐIỂM CHUẨN. 3. Sắp xếp bảng tính theo cột TÊN tăng dần. 4. Lập công thức điền dữ liệu vào cột KHU VỰC. Biết: KHU V ỰC là ký tự thứ 2 của MÃ SỐ. 5. Lập công thức điền dữ liệu vào cột NGÀNH THI. Biết: NGÀNH THI là ký tự thứ 1 của MÃ SỐ. Nếu ký tự đầu là A thì NGÀNH THI là Máy tính, B: Điện tử, C: Xây dựng, D: Hóa. 6. Lập công thức điền dữ liệu vào cột ĐIỂM CHUẨN. Biết: Nếu NGÀNH THI là Máy tính thì ĐIỂM CHUẨN là 19 , Điện tử: 17 , Xây dựng: 15, Hóa: 13. 7. Tính điểm tổng cộng là tổng điểm 3 môn. 8. Hãy lập công thức điền kết quả như sau :Nếu thí sinh có điểm tổng cộng lớn hơn hay bằng điểm chuẩn của ngành mình dự thi sẽ có kết quả là “Đậu”,ngược lại là “Rớt”. 9. Copy các công thức cho các ô còn lại. 7 Bài 6: Các hàm truy tìm dữ liệu, hàm số học và thống kê 7.1 Các hàm truy tìm dữ liệu 7.1.1 Hàm VLOOKUP Thực hiện việc tìm kiếm giá trị x trong một vùng dữ liệu được chỉ định, nếu thấy thì sẽ lấy giá trị ở cột xác định tương ứng.  Cú pháp:  Lookup_value: Là giá trị dò tìm. Giá trị được tìm kiếm trên cột bên trái của table_array. Trang 31 / 59 ROUND(number, num_digit) MOD(number, divisor) INT(number) HLOOKUP(lookup_value, table_aray, row_index_num, [range_lookup])  Table_array: Là vùng dò tìm. Vùng dò tìm bao gồm một cột bên trái chứa dữ liệu cần tìm và các cột bên phải chứa các dữ liệu cần lấy. Địa chỉ nên là tuyệt đối (nên đặt tên cho vùng).  Col_index_num: Số thứ tự cột trong table_array, nơi VLOOKUP sẽ lấy giá trị trả về.  Range_lookup: Giá trị Logic xác định việc tìm kiếm chính xác hay gần đúng, nếu là:  0 (hoặc FALSE): Là tìm kiếm chính xác, nếu tìm thấy thì lấy kết quả ở cột tương ứng, nếu không tìm thấy thì trả về giá trị #N/A.  1 (hoặc TRUE): Là tìm kiếm gần đúng, nếu không tìm thấy thì lấy giá trị gần đúng. Điều kiện của tìm kiếm gần đúng là vùng dữ liệu phải được sắp xếp theo thứ tự tăng dần của cột đầu tiên. 7.1.2 Hàm HLOOKUP Thực hiện việc tìm kiếm giá trị x trong một vùng dữ liệu được chỉ định, nếu thấy thì sẽ lấy giá trị ở dòng xác định tương ứng.  Cú pháp:  Lookup_value: Là giá trị dò tìm.  Table_array: Là vùng dò tìm.  Row_index_num: Là số thứ tự dòng cần lấy trong table_array.  Range_lookup: Là phương thức dò tìm. 7.2 Một số hàm số học 7.2.1 Hàm INT  Cú pháp:  Ý nghĩa: Hàm lấy phần nguyên của một số. Khi dữ kiện trong Cell là chuỗi thì kết quả là #VALUE!. Khi không có dữ kiện trong Cell thì kết quả là 0.  Ví dụ: = INT(12.7)  Trả về kết quả là 12. = INT(10.2)  Trả về kết quả là 10. 7.2.2 Hàm MOD  Cú pháp:  Number: là số bị chia.  Divisor: là số chia.  Ý nghĩa: Trả về phần dư của phép chia nguyên. Kết quả có cùng dấu với số chia.  Ví dụ: = MOD(25, 7)  Trả về kết quả là 4. 7.2.3 Hàm ROUND Trang 32 / 59 SUM(number1, [number2], …) AVERAGE(number1, [number2], ...) MIN(number1, [number2], ...) MAX(number1, [number2], ...)  Cú pháp:  Ý nghĩa: Làm tròn số number với độ chính xác đến con số thứ num_digit.  Nếu num_digit > 0 thì number sẽ được làm tròn đến chữ số thập phân thứ num_digit.  Nếu num_digit = 0 thì number sẽ được làm tròn đến số nguyên gần nhất.  Nếu num_digit < 0 thì number sẽ được làm tròn đến chữ số bên trái num_digit của dấu thập phân.  Ví dụ: Ô A1 chứa số 347654.126  = ROUND(A1, -3)  Kết quả trả về 348000  = ROUND(A1, 2)  Kết quả trả về 347654.13 7.2.4 Hàm MAX  Cú pháp:  Number1, number2,...: Từ 1 đến 30 đối số. Các đối số là giá trị số, hoặc tên khối, dãy hay tham chiếu đến những giá trị số. Các giá trị chuỗi, Logic, Cell rỗng sẽ bị bỏ qua.  Ý nghĩa: Trả về giá trị lớn nhất trong danh sách các đối số.  Ví dụ: = MAX(10, 2, 8)  Trả về giá trị là 10. 7.2.5 Hàm MIN  Cú pháp:  Number1, number2, ...: Từ 1 đến 30 đối số. Các đối số là giá trị số, hoặc tên khối, dãy hay tham chiếu đến những giá trị số. Các giá trị chuỗi, Logic, Cell rỗng sẽ bị bỏ qua.  Ý nghĩa: Trả về giá trị nhỏ nhất trong danh sách các đối số.  Ví dụ: = MIN(10, 2, 8) trả về giá trị là 2. 7.2.6 Hàm AVERAGE  Cú pháp:  Number1, number2, ...: Từ 1 đến 30 đối số. Các đối số là giá trị số, hoặc tên khối, dãy hay tham chiếu đến những giá trị số. Các giá trị chuỗi, Logic, Cell rỗng sẽ bị bỏ qua.  Ý nghĩa: Trả về giá trị trung bình của các đối số.  Ví dụ: = AVERAGE(17, 2, 8)  Trả về giá trị là 9. 7.2.7 Hàm SUM  Cú pháp: Trang 33 / 59 SUMIF(range, criteria, [sum_range]) COUNTIF(range, criteria) COUNTA(value1, [value2], ...) COUNT(value1, [value2], ...) RANK(number, ref, [order])  Number1, number2, …: Từ 1 đến 30 đối số. Các đối số là giá trị số, hoặc tên khối, dãy hay tham chiếu đến những giá trị số. Các giá trị chuỗi, Logic, Cell rỗng sẽ bị bỏ qua.  Ý nghĩa: Hàm tính tổng của các đối số. 7.2.8 Hàm RANK  Cú pháp:  Number: Là ô muốn xếp hạng.  Ref: Dãy địa chỉ hoặc một tên tham chiếu đến một vùng.  Order:  Nếu là 0 (hoặc bỏ trống): Thứ tự xếp hạng được tính theo giá trị đã sắp xếp giảm dần trong danh sách các số.  Nếu khác 0: Thứ tự xếp hạng được tính theo giá trị đã sắp xếp tăng dần trong danh sách các số.  Ý nghĩa: Xếp hạng một số trong danh sách các số. 7.2.9 Hàm COUNT  Cú pháp:  Value1, value2, ...: Từ 1 đến 30 đối số.  Những đối số nào có giá trị số thì được đếm. Nếu đối số là một dãy hay một tham chiếu thì chỉ những giá trị nào là số mới được đếm. Giá trị sẽ trả về 0 (Zero) nếu không có giá trị số nào được tìm thấy trong đối số.  Ý nghĩa: Đếm số Cell dữ liệu kiểu số trong miền địa chỉ. 7.2.10 Hàm COUNTA  Cú pháp:  Ý nghĩa: Đếm số Cell không rỗng trong miền địa chỉ. Các giá trị được đếm là giá trị chuỗi, giá trị số, giá trị ngày, giá trị rỗng do hàm trả về và kể cả giá trị lỗi nhưng không kể Cell rỗng. 7.2.11 Hàm COUNTIF  Cú pháp:  Range: Là phạm vi Cell cần đếm.  Criteria: Là tiêu chuẩn để được đếm. Tiêu chuẩn này dưới hình thức một giá trị số, biểu thức hay chuỗi ký tự để chọn lọc điều kiện cho Cell nào được đếm.  Ý nghĩa: Đếm số Cell trong vùng thỏa điều kiện đã cho. 7.2.12 Hàm SUMIF  Cú pháp:  Range: Là vùng ô để so sánh với criteria. Trang 34 / 59  Criteria: Là điều kiện cộng, có thể là số, chữ hoặc biểu thức. Quyết định ô nào trong vùng sum_range sẽ được cộng.  Sum_range: Là vùng ô sẽ được cộng.  Ý nghĩa: Tính tổng của những ô trên vùng sum_range nếu vùng điều kiện range của dòng tương ứng thoả mãn điều kiên criteria. 7.3 Tóm tắt 7.3.1 Câu hỏi 1. Trong Excel, giả sử ô D4 có công thức sau: = HLOOKUP(A4, A10:D12, 5, 0) kết quả sai ở thành phần nào? a. Thành phần thứ 1. b. Thành phần thứ 2. c. Thành phần thứ 3. d. Thành phần thứ 4. 2. Trong Excel, ô D4 = 233333.33 kết quả của hàm = ROUND(D4, -3) là: a. 233333 b. 233000 c. 233333.330 d. Sai vì quá nhiều tham số 3. Trong Excel, kết quả của hàm = INT(H4, 2) là: a. Tìm phần dư của phép chia H4 cho 2. b. Tìm phần thương nguyên của phép chia ô H4 cho 2. c. Làm tròn giá trị trong ô H4 với 2 số lẻ. d. Sai vì quá nhiều tham số. 4. Trong Excel, giả sử ô A1 có giá trị 12345, muốn lấy giá trị 34 ta dùng hàm nào sau đây: a. = MOD(INT(A1/10),100) b. = MID(A1,3,2) c. Các câu a và b đều đúng. d. Các câu a và b đều sai. 5. Trong Excel, các ô A1 = 5, A2 = X và A3 = 4.5. Hãy cho biết kết quả của công thức sau: = AVERAGE(A1:A3). a. 4.75 b. 5 c. Báo lỗi #NAME d. Báo lỗi #DIV/0 6. Trong Excel, các ô A1 = X, A2 = 01, A3 = 02 và A4 = 03. Hãy cho biết kết quả của công thức sau: = COUNT(A1:A4). a. 3 b. Sẽ báo quá nhiều đối số. Trang 35 / 59 c. 0 d. Trong Excel không có hàm này. 7. Trong Excel, muốn xếp hạng ta dùng hàm nào sau đây: a. LEN b. UPPER c. PROPER d. RANK 7.4 Bài tập 7.4.1 Bài tập 1 BẢNG THANH TOÁN PHỤ CẤP THÁNG 3/2005 A B C D E F G H 1 2 S TT Mã Họ Và Tên Phòng Ban Chức Vụ Phụ Cấp Chức Vụ Phụ Cấp Trách Nhiệm Tổng Phụ Cấp 3 1 A01 Trần Sang GĐ 4 2 B02 Trương Thị Chi PGĐ 5 3 C01 Nguyễn Thị Hai PP 6 4 A02 Phạm Thị Hà NV 7 5 C03 Trần Hoà Lê NV 8 6 B03 Nguyễn Bính NV 9 7 A02 Trần Nam TP 10 8 B02 Vũ Thuý Bình NV 11 9 A02 Hà Cẩm Lan NV 12 10 C03 Nguyễn Huy NV 13 11 B01 Hà Lan Anh NV 14 Tổng Cộng BẢNG DÒ TÌM 1 A B C D 1 Mã Phòng Ban A B C 2 Tên Phòng Ban Kế Hoạch Kế Toán Vật Tư BẢNG DÒ TÌM 2 A B C 1 Chức Vụ Phụ Cấp Hệ Số Trách Nhiệm Trang 36 / 59 2 GĐ 250000 2.5 3 PGĐ 180000 1.5 4 TP 150000 1.2 5 PP 100000 1.1 6 NV 1 Yêu cầu: 1. Nhập và lưu tập tin với tên PHUCAP.xls. 2. Cột phòng ban: Ký tự đầu của mã nhân viên quy định mã phòng ban của họ, căn cứ vào ký tự đầu và bảng dò tìm 1, sử dụng hàm HLOOKUP để điền vào mã phòng ban. 3. Căn cứ vào cột chức vụ và bảng dò tìm 2, sử dụng hàm VLOOKUP để tính phụ cấp chức vụ. 4. Phụ cấp trách nhiệm = 150.000 * hệ số trách nhiệm. Hệ số trách nhiệm tra trong bảng phụ cấp. 5. Tính tổng phụ cấp. 7.4.2 Bài tập 2 : Tạo tập tin và lưu với tên KQ_THI.XLS. Yêu cầu: 1. THẤP NHẤT là điểm thấp nhất cũa 3 môn. 2. TRUNG BÌNH là điểm trung bình của 3 môn, làm tròn 2 số lẻ. 3. CAO NHẤT, THẤP NHẤT theo cột cho từng môn. 4. XẾP LOẠI: - Giỏi: Nếu TRUNG BÌNH >=8, không có môn nào dưới 6.5 và thi đủ các môn. - Khá: Nếu 6.5 <= TRUNG BÌNH < 8, không có môn nào dưới 5 và thi đủ các môn. - TB: Nếu 5 <= TRUNG BÌNH <6.5, không có môn nào dưới 3.5 và thi đủ môn. - Yếu: Các trường hợp còn lại. Trang 37 / 59 5. Hãy đếm số học viên. 6. Đếm số học viên không thi môn NGOẠI NGỮ. - Định dạng bảng tính như trên. 7.4.3 Bài tập 3 Yêu cầu: 1. Nhập dữ liệu cho cột Họ VÀ TÊN, HỆ SỐ, NGÀY CÔNG, Kì 1 của bảng tính như trên. 2. Điền chuỗi sồ thứ tự vào cột STT. 3. Tính: a. Giá trị trong [t1] bằng cách sử dụng hàm COUNT. b. Giá trị trong các ô [t2] và [t3] là tổng các cột HỆ SỐ, NGÀY CÔNG một cách tương ứng. c. Giá trị trong ô [t4] bằng giá trị trong ô [t1] * Lương Bình Quân. d. Lương Tổng Cộng = Ngày Công * Hệ Số * Lương Hệ Số. Trong đó : 1. Lương Hệ Số = [t4] / ([t2] * Ngày Công). 2. Kết quả tính toán được làm tròn đến hàng trăm. 3. Lương Kỳ 2 = Lương Tổng Cộng – Lương Kỳ 1. 4. Giá trị trong các ô [t5] và [t6] là tổng các cột Lương Kỳ 1, Lương Kỳ 2 tương ứng. 4. Sử dụng các hàm MAX, MIN, AVERAGE để tính giá trị cao nhất , thấp nhất và trung bình cho các cột tương ứng. 5. Định dạng các cột Lươ