Tin học văn phòng - Chương 5: Các hàm thông dụng excel

1 CHƢƠNG 5: CÁC HÀM THÔNG DỤNG EXCEL TIN HỌC ĐẠI CƢƠNG TH Đ C 2 1. Khái niệm Hàm trong MS EXCEL: (tài liệu) LEFT (text, [num_chars]) Tên hàm Các tham số Học và sử dụng 1 hàm ta cần quan tâm: 1. Tên hàm 2. Ý nghĩa của hàm 3. Cú pháp hàm 4. Kết quả của hàm 5. Khi nào cần sử dụng hàm TH Đ C 3 Số lƣợng các tham số trong hàm có thể là - 1 tham số - 2 tham số - 3 tham số . LEN (text) LEFT (text, [num_chars]) IF (logical_test, [value_if_true], [value_if_false]) Có 2 loại tham số: - Tham số bắt buộc - Tham số không bắt buộc TH Đ C 4 Các hàm thông dụng 1. Nhóm Hàm Chuỗi 2. Nhóm Hàm Luận Lý 3. Nhóm Hàm Thời Gian 4. Nhóm Hàm Thống Kê 5. Nhóm Hàm Dò Tìm TH Đ C 5 1. Nhóm Hàm Chuỗi: xử lý các chức năng tiện ích về Chuỗi Trả về n ký tự bên trái của chuỗi Trả về n ký tự bên phải của chuỗi Trả về n ký tự tại vị trí cắt của chuỗi TH Đ C 6 1. Nhóm Hàm Chuỗi: xử lý các chức năng tiện ích về Chuỗi Trả về n ký tự bên trái của chuỗi Học và sử dụng 1 hàm ta cần quan tâm: 1. Tên hàm : Left 2. Ý nghĩa của hàm : trả về n ký tự bên trái của chuỗi 3. Cú pháp hàm : Left (chuỗi, n) 4. Kết quả của hàm : kết quả là 1 chuỗi (ký tự) 5. Khi nào cần sử dụng hàm :cắt chuỗi (ký tự) bên trái TH Đ C 7 Bài Tập 1: về nhóm hàm chuỗi TH Đ C 8 1. Cho biết ký tự thứ 1 của Mã Hàng là gì ? 2. Cho biết ký tự cuối của Mã Hàng là gì ? 3. Cho biết ký tự thứ 2 và thứ 3 của Mã Hàng là gì ? 4. Cho biết ký tự thứ 4 của Mã Hàng là gì ? 5. Cho biết ký tự thứ 3 của Mã Hàng là gì ? 6. Ghép hai ký tự đầu và ký tự cuối của Mã Hàng 7. Ghép ký tự cuối và ký tự đầu của Mã Hàng 8. Ghép ký tự thứ 2 và ký tự đầu của Mã Hàng 9. Ghép ký tự thứ 4 và hai ký tự thứ 2 và 3 của Mã Hàng TH Đ C 9 Các hàm thông dụng 1. Nhóm Hàm Chuỗi 2. Nhóm Hàm Luận Lý 3. Nhóm Hàm Thời Gian 4. Nhóm Hàm Thống Kê 5. Nhóm Hàm Dò Tìm TH Đ C 10 2. Nhóm Hàm Luận Lý : Nhóm hàm này luôn trả về kết quả chỉ đúng hoặc sai, nên hàm thường xuyên được dùng trong phép so sánh Nếu BtLogic có giá trị đúng thì giaTri_1, ngược lại làm giaTri_2 TH Đ C 11 2. Nhóm Hàm Luận Lý Trả về True nếu tất cả các BtLogic có giá trị True Trả về False nếu chỉ cần một trong n BtLogic là False Trả về False nếu tất cả các BtLogic có giá trị False Trả về True nếu chỉ cần một trong n BtLogic là True Trả về giá trị phủ định của btLogic TH Đ C 12 Bài Tập 3: về nhóm hàm luận lý TH Đ C 13 Bài Tập 4 : Kết hợp hàm chuỗi và hàm luận lý TH Đ C 14 CÁC BÀI TẬP ỨNG DỤNG TH Đ C 15 TH Đ C 16 TH Đ C 17 Control Panel Chỉnh dấu phân cách ngàn và dấu thập phân TH Đ C 18 1. Nếu ký tự đầu của Mã Hàng là D thì kết quả là 10, ngược lại là 5 2. Nếu ký tự thứ 3 của Ma Hang > 2 thì kết quả là ký tự thứ 1 của Ma Hang, ngược lại kết quả là ký tự cuối của Ma Hang 3. Nếu ký tự cuối của Ma Hang là E thì kết quả là ký tự thứ hai của Ma Hang, ngược lại kết quả là ký tự thứ 4 của Ma Hang 4. Nếu ký tự đầu của Mã Hàng là A hoặc C thì kết quả là 1, ngược lại là 2 5. Nếu ký tự cuối của Ma Hang là D hoặc E thì kết quả là hai ký tự đầu của Mã Hàng, ngược lại là 2 ký tự cuối. 6. Nếu ký tự thứ 3 của Mã Hàng <3 và ký tự thứ 4 là D thì kết quả là số 1, ngược lại là 0 7. Nếu ký tự thứ 1 của Mã Hàng là D và ký tự cuối là E thì kết quả là ký tự đầu và ký tự cuối của Mã Hàng, ngược lại kết quả là ký tự cuối và ký tự đầu của Mã Hàng TH Đ C 19 1. Nếu ký tự đầu của MaHang là M và SỐ LƯỢNG >=30 thì kết quả là 10%, ngược lại là 0% 2. Nếu SỐ LƯỢNG =30 đến <50 thì kết quả câu 2 là ký tự thứ 3 của MÃ HÀNG, ngược lại là ký tự cuối của Mã Hàng. 3. Nếu ký tự thứ 2 của MÃ HÀNG là X thì kết quả là “Hàng Xuất”, ngoài ra kết quả là “Hàng Nhập” 4. Nếu ký tự thứ nhất của Mã Hàng là M thì kết quả là Mực, C thì kết quả là Cá, ngoài ra kết quả là Tôm. TH Đ C 20 TH Đ C 21 4. Nhóm Hàm Thống Kê: Nhóm hàm này sẽ tổng hợp và thống kê từ những số liệu có sẵn TH Đ C 22 Hàm MIN(number1, [number2],) Trả về giá trị nhỏ nhất của Number1, Number2, TH Đ C 23 Hàm MAX(number1, [number2],) Trả về giá trị lớn nhất của Number1, Number2, TH Đ C 24 Hàm AVERAGE(number1, [number2],) Trả về Trung bình cộng của các số Number1, Number2, TH Đ C 25 Hàm COUNTIF(vùng đếm, điều kiện) Trả về số lần đếm ô có giá trị thỏa điều kiện TH Đ C 26 Hàm SUM(number1, [number2],) Trả về tổng của number1, number2 TH Đ C 27 Hàm SUMIF(range, criteria, [sum_range]) cộng dồn các ô thỏa điều kiện TH Đ C 28 Hàm RANK(giá trị, vùng xếp, kiểu) Trả về thứ tự xếp hạng của giá trị trong vùng; kiểu = 1 thì xếp tăng; kiểu = 0 thì xếp giảm TH Đ C 29 Vd: Cần thống kê số nhân viên và tổng lƣơng của mỗi đơn vị trong bảng thống kê TH Đ C 30 Vd1: Cần thống kê số nhân viên và tổng lƣơng của mỗi đơn vị trong bảng thống kê TH Đ C 31 Vd2: Đếm xem có bao nhiêu sinh viên khoa quản trị TH Đ C 32 CÁC BÀI TẬP ỨNG DỤNG TH Đ C 33 1. Nếu ký tự đầu của Mã Hàng là D thì kết quả là 10, ngược lại là 5 2. Nếu ký tự thứ 3 của Ma Hang > 2 thì kết quả là ký tự thứ 1 của Ma Hang, ngược lại kết quả là ký tự cuối của Ma Hang 3. Nếu ký tự cuối của Ma Hang là E thì kết quả là ký tự thứ hai của Ma Hang, ngược lại kết quả là ký tự thứ 4 của Ma Hang 4. Nếu ký tự đầu của Mã Hàng là A hoặc C thì kết quả là 1, ngược lại là 2 5. Nếu ký tự cuối của Ma Hang là D hoặc E thì kết quả là hai ký tự đầu của Mã Hàng, ngược lại là 2 ký tự cuối. 6. Nếu ký tự thứ 3 của Mã Hàng <3 và ký tự thứ 4 là D thì kết quả là số 1, ngược lại là 0 7. Nếu ký tự thứ 1 của Mã Hàng là D và ký tự cuối là E thì kết quả là ký tự đầu và ký tự cuối của Mã Hàng, ngược lại kết quả là ký tự cuối và ký tự đầu của Mã Hàng TH Đ C 34 TH Đ C 35 Các hàm thông dụng 1. Nhóm Hàm Chuỗi 2. Nhóm Hàm Luận Lý 3. Nhóm Hàm Thời Gian 4. Nhóm Hàm Thống Kê 5. Nhóm Hàm Dò Tìm TH Đ C 36 5. Nhóm Hàm Dò Tìm : Nhóm hàm này sẽ hỗ trợ người dùng tìm và lấy các giá trị cần thiết trong một ma trận số tổng hợp 1. VLOOKUP : Hàm dò tìm theo cột 2. HLOOKUP : Hàm dò tìm theo dòng TH Đ C 37 VLOOKUP (giá trị dò, bảng dò, cột giá trị, kiểu dò) Kiểu dò = 0 : dò tìm chính xác Kiểu dò 0 : dò tìm xấp xỉ TH Đ C 38 HLOOKUP (giá trị dò, bảng dò, hàng giá trị, kiểu dò) Kiểu dò = 0 : dò tìm chính xác Kiểu dò 0 : dò tìm xấp xỉ TH Đ C 39 TH Đ C 40 TH Đ C 41 1. Tính tuổi 2. Tính điểm trung bình 3. Tính các giá trị cao nhất, trung bình, thấp nhất của các cột Tuổi, Toán, Tin , Trung Bình 4. Xếp thứ hạng 5. Xếp loại theo điểm trung bình theo thang điểm sau: Xuất sắc: >= 9.5, Giỏi >=8, Khá >=7, Trung bình >=5, Kém <5 6. Đếm số học viên nam, học viên nữ