Bài giảng Tin học ứng dụng - Chương 4: Bảng tính MS Excel - Năm học 2019-2020 - Nguyễn Đình Hoa Cương

Nội dung text: Bài giảng Tin học ứng dụng - Chương 4: Bảng tính MS Excel - Năm học 2019-2020 - Nguyễn Đình Hoa Cương

1. Học phần Tin học ứng dụng Chương 4: Bảng tính MS Excel Nguyễn Đình Hoa Cương Trường Đại học Kinh tế Khoa Hệ thống thông tin kinh tế Ngày 4 tháng 9 năm 2019 ndhcuong (HCE) Tin học ứng dụng Ngày 4 tháng 9 năm 2019 1 / 40
2. Nội dung 1 Giới thiệu MS Excel 2010 2 Các thao tác cơ bản 3 Hàm trong Excel 4 Nhóm hàm toán học 5 Nhóm hàm logic 6 Nhóm hàm thống kê 7 Nhóm hàm ngày tháng 8 Nhóm hàm kí tự 9 Nhóm hàm tìm kiếm 10 Vẽ đồ thị 11 In ấn bảng tính ndhcuong (HCE) Tin học ứng dụng Ngày 4 tháng 9 năm 2019 2 / 40
3. Nội dung 1 Giới thiệu MS Excel 2010 2 Các thao tác cơ bản 3 Hàm trong Excel 4 Nhóm hàm toán học 5 Nhóm hàm logic 6 Nhóm hàm thống kê 7 Nhóm hàm ngày tháng 8 Nhóm hàm kí tự 9 Nhóm hàm tìm kiếm 10 Vẽ đồ thị 11 In ấn bảng tính ndhcuong (HCE) Tin học ứng dụng Ngày 4 tháng 9 năm 2019 3 / 40
4. Giới thiệu phần mềm bảng tính MS Excel 2010 MS Excel là phần mềm xử lý bảng tính thông dụng cho công tác kế toán, văn phòng. Một tập bảng tính (WorkBook), viết tắt là Book. Mỗi Workbook có 255 bảng tính(Sheet). Mỗi bảng tính là tập hợp các ô, mỗi ô là một hình chữ nhật được giới hạn bởi các đường kẻ Hình 1: Biểu tượng phần mềm MS Excel (nguồn: [1]) lưới (Gridline) ngang và dọc. Chiều rộng mặc định của mỗi ô là 9 ký tự, tối đa là 254 ký tự. ndhcuong (HCE) Tin học ứng dụng Ngày 4 tháng 9 năm 2019 4 / 40
5. Giới thiệu (tt) WorkBook: là một file dữ liệu do Excel tạo ra. Vùng làm việc là vùng bảng tính(Sheet) gồm 256 cột, được đặt tên lần lượt từ trái qua phải là A, B, C, . . . , AA, BB . . . IV và 16.384 dòng đặt tên là 1, 2, 3 . . . từ trên xuống dưới Các dòng, cột giao nhau tạo thành các ô(cell). Một ô được xác định bằng địa chỉ gồm tên cột và tên dòng tạo ra nó. Ô hiện thời là ô có hộp điều khiển bao quanh. ndhcuong (HCE) Tin học ứng dụng Ngày 4 tháng 9 năm 2019 5 / 40
6. Giới thiệu (tt) Địa chỉ ô: Địa chỉ tương đối Là địa chỉ ô được viết bình thường dạng cột, dòng. Ví dụ: C9, H10 Với cách viết này địa chỉ ô có thể thay đổi với một số thao tác sao chép hoặc tính toán. Địa chỉ tuyệt đối: Là địa chỉ ô được viết kèm theo ký hiệu $ với ý nghĩa cố định địa chỉ không thay đổi trong các phép tính. Ví dụ: $B, B$5, $B$5 Địa chỉ vùng: Bao gồm địa chỉ của ô bắt đầu đến ô kết thúc của vùng làm việc. Ví dụ: B2:C4, $B2:$C4, B$2:C$4, $B$2:$C$4 ndhcuong (HCE) Tin học ứng dụng Ngày 4 tháng 9 năm 2019 6 / 40
7. Giới thiệu (tt) Excel phân biệt các loại kiểu dữ liệu cơ bản Hằng (Constant): bao gồm chuỗi kí tự hoặc số Nếu là chữ đầu tiên trong phần dữ liệu thì Excel hiểu đó là chuỗi. Nếu phần dữ liệu toàn là số thì Excel hiểu là kiểu số. Công thức (Formula) Bao gồm biểu thức trong đó chứa địa chỉ ô hoặc địa chỉ vùng trên bảng tính Để gõ công thức phải bắt đầu bằng dấu = hoặc dấu + Logic: Nhận giá trị true hoặc false ndhcuong (HCE) Tin học ứng dụng Ngày 4 tháng 9 năm 2019 7 / 40
8. Nội dung 1 Giới thiệu MS Excel 2010 2 Các thao tác cơ bản 3 Hàm trong Excel 4 Nhóm hàm toán học 5 Nhóm hàm logic 6 Nhóm hàm thống kê 7 Nhóm hàm ngày tháng 8 Nhóm hàm kí tự 9 Nhóm hàm tìm kiếm 10 Vẽ đồ thị 11 In ấn bảng tính ndhcuong (HCE) Tin học ứng dụng Ngày 4 tháng 9 năm 2019 8 / 40
9. Các thao tác cơ bản với MS Excel 1 Di chuyển con trỏ 2 Thao tác trên bảng tính: thay đổi độ rộng cột/chiều cao hàng; chèn, xóa dòng/cột/ô; 3 Tách bảng tính; cố định 1 phần bảng tính; 4 Chọn vùng dữ liệu; sao chép/cắt/dán vùng dữ liệu 5 Định dạng hiển thị số, kiểu tiền tệ; canh hàng dữ liệu. 6 Định dạng font chữ, khung viền, màu nền. ndhcuong (HCE) Tin học ứng dụng Ngày 4 tháng 9 năm 2019 9 / 40
10. Nội dung 1 Giới thiệu MS Excel 2010 2 Các thao tác cơ bản 3 Hàm trong Excel 4 Nhóm hàm toán học 5 Nhóm hàm logic 6 Nhóm hàm thống kê 7 Nhóm hàm ngày tháng 8 Nhóm hàm kí tự 9 Nhóm hàm tìm kiếm 10 Vẽ đồ thị 11 In ấn bảng tính ndhcuong (HCE) Tin học ứng dụng Ngày 4 tháng 9 năm 2019 10 / 40
11. Hàm trong Excel Lỗi Ý nghĩa ]DIV /0 Lỗi chia cho 0 Hàm là những công cụ tính toán ]N/A 1 giá trị trong công được lập sẵn để thực hiện các thức không dùng được tính toán phức tạp. ]NAME? Excel không nhận ra Cú pháp: = ( ). ]NUM! Công thức dùng một Sử dụng Wizard: số không đúng Vào Insert → Funtion ]VALUE Công thức dùng một Chọn hàm cần sử dụng đối số hoặc một toán Nhập các đối số thích hợp tử sai kiểu Kích OK để kết thúc Bảng 1: Một số lỗi thường gặp ndhcuong (HCE) Tin học ứng dụng Ngày 4 tháng 9 năm 2019 11 / 40
12. Nội dung 1 Giới thiệu MS Excel 2010 2 Các thao tác cơ bản 3 Hàm trong Excel 4 Nhóm hàm toán học 5 Nhóm hàm logic 6 Nhóm hàm thống kê 7 Nhóm hàm ngày tháng 8 Nhóm hàm kí tự 9 Nhóm hàm tìm kiếm 10 Vẽ đồ thị 11 In ấn bảng tính ndhcuong (HCE) Tin học ứng dụng Ngày 4 tháng 9 năm 2019 12 / 40
13. Nhóm hàm toán học Tên hàm Ý nghĩa Ví dụ Giá trị trả về ABS(x) Tính giá trị =ABS(-2) 2 tuyệt đối của x EXP(x) Tính lũy thừa x =EXP(0) 1 của e SQRT(x) Tính căn bậc 2 =SQRT(4) 2 của x SUM(n1,n2, ) Tính tổng các =SUM(A1:B3) Trả về tổng các đối số giá trị số trong danh sách đối số Bảng 2: Một số hàm toán học thông dụng ndhcuong (HCE) Tin học ứng dụng Ngày 4 tháng 9 năm 2019 13 / 40
14. Nhóm hàm toán học (tt) Tên hàm Ý nghĩa Ví dụ Giá trị trả về SUMIF(range, Khi các ô trong đk, sum-range) range thỏa mãn đk thì tính tổng các ô tương ứng trong sum-range. MOD(n,t) Chia lấy phần =MOD(7,3) 1 dư n/t INT(x) Lấy phần =INT(4.5) 4 nguyên của x ROUND(x,n) Làm tròn x với n =ROUND(4.567,2) 4.57 chữ số thập phân Bảng 3: Một số hàm toán học thông dụng (tt) ndhcuong (HCE) Tin học ứng dụng Ngày 4 tháng 9 năm 2019 14 / 40
15. Nội dung 1 Giới thiệu MS Excel 2010 2 Các thao tác cơ bản 3 Hàm trong Excel 4 Nhóm hàm toán học 5 Nhóm hàm logic 6 Nhóm hàm thống kê 7 Nhóm hàm ngày tháng 8 Nhóm hàm kí tự 9 Nhóm hàm tìm kiếm 10 Vẽ đồ thị 11 In ấn bảng tính ndhcuong (HCE) Tin học ứng dụng Ngày 4 tháng 9 năm 2019 15 / 40
16. Nhóm hàm logic Tên hàm Ý nghĩa Ví dụ Giá trị trả về AND(logical1, Cho giá trị =AND(A2 >= Tùy vào giá trị logical2, . . . ) đúng khi tất cả 8, Min(A2:B2) ô A2 các đối số đúng <> 5) IF(biểu thức Trả về giá trị =IF(A2=0, Tùy vào giá trị logic, value1, value1 nếu biểu “Nữ”, “Nam”) ô A2 value2) thức logic đúng ngược lại trả về value2 Bảng 4: Một số hàm logic ndhcuong (HCE) Tin học ứng dụng Ngày 4 tháng 9 năm 2019 16 / 40
17. Nhóm hàm logic (tt) Tên hàm Ý nghĩa Ví dụ Giá trị trả về NOT(đối số) Hàm phủ định =NOT( 3 > 0) False giá tr ịcủa đối số OR(logical1, Trả về giá trị =OR(A2 > 5, 3 True logical2, . . . ) đúng nếu có > 2) một đối số có giá trị đúng Bảng 5: Một số hàm logic (tt) ndhcuong (HCE) Tin học ứng dụng Ngày 4 tháng 9 năm 2019 17 / 40
18. Nội dung 1 Giới thiệu MS Excel 2010 2 Các thao tác cơ bản 3 Hàm trong Excel 4 Nhóm hàm toán học 5 Nhóm hàm logic 6 Nhóm hàm thống kê 7 Nhóm hàm ngày tháng 8 Nhóm hàm kí tự 9 Nhóm hàm tìm kiếm 10 Vẽ đồ thị 11 In ấn bảng tính ndhcuong (HCE) Tin học ứng dụng Ngày 4 tháng 9 năm 2019 18 / 40
19. Nhóm hàm thống kê Tên hàm Ý nghĩa Ví dụ Giá trị trả về Average(n1, n2, Tính trung bình =Average(A1, Tính giá trị ) cộng của n1, B2, C3) trung bình các n2, . . . giá trị số thuộc danh sách đối số Count(v1, v2, Đếm các ô có =Count(A2:C3) Tổng các ô có ) kiểu số giá trị số thuộc vùng chọn Bảng 6: Một số hàm thống kê ndhcuong (HCE) Tin học ứng dụng Ngày 4 tháng 9 năm 2019 19 / 40
20. Nhóm hàm thống kê (tt) Tên hàm Ý nghĩa Ví dụ Giá trị trả về Counta(v1, v2, Đếm các ô có =Counta(A2:C3) Tổng các ô ) dữ liệu không trống Countif(range, Đếm số các ô =Countif(A2:C3, Tổng số các ô criteria) thỏa mãn điều ">=5") trong vùng kiện cho trước chọn có giá trị lơn hơn hoặc bằng 5 Bảng 7: Một số hàm thống kê (tt) ndhcuong (HCE) Tin học ứng dụng Ngày 4 tháng 9 năm 2019 20 / 40
21. Nhóm hàm thống kê (tt) Tên hàm Ý nghĩa Ví dụ Giá trị trả về Max(v1, v2, Trả về Số lớn =Max(A2:C3) Số lớn nhất ) nhất trong các thuộc vùng đối số chọn Min(v1, v2, Trả về Số nhỏ =Min(A2:C3) Số nhỏ nhất ) nhất trong các thuộc vùng đối số chọn RANK(x, vùng trả về giá trị dữ liệu, cách thứ bậc của x sắp xếp) trong vùng dữ liệu. Bảng 8: Một số hàm thống kê (tt) ndhcuong (HCE) Tin học ứng dụng Ngày 4 tháng 9 năm 2019 21 / 40
22. Nội dung 1 Giới thiệu MS Excel 2010 2 Các thao tác cơ bản 3 Hàm trong Excel 4 Nhóm hàm toán học 5 Nhóm hàm logic 6 Nhóm hàm thống kê 7 Nhóm hàm ngày tháng 8 Nhóm hàm kí tự 9 Nhóm hàm tìm kiếm 10 Vẽ đồ thị 11 In ấn bảng tính ndhcuong (HCE) Tin học ứng dụng Ngày 4 tháng 9 năm 2019 22 / 40
23. Nhóm hàm ngày tháng Tên hàm Ý nghĩa Ví dụ Giá trị trả về Date(year, Trả về một ngày =Date(90,10,6) 06/10/90 month, day) cụ thể Day(date) Trả về ngày của =Day(19/03/08) 19 date Month(date) Trả về tháng =Month(19/03/08) 03 của date Year(date) Trả về năm của =Year(19/03/08) 2008 date Bảng 9: Một số hàm ngày tháng ndhcuong (HCE) Tin học ứng dụng Ngày 4 tháng 9 năm 2019 23 / 40
24. Nội dung 1 Giới thiệu MS Excel 2010 2 Các thao tác cơ bản 3 Hàm trong Excel 4 Nhóm hàm toán học 5 Nhóm hàm logic 6 Nhóm hàm thống kê 7 Nhóm hàm ngày tháng 8 Nhóm hàm kí tự 9 Nhóm hàm tìm kiếm 10 Vẽ đồ thị 11 In ấn bảng tính ndhcuong (HCE) Tin học ứng dụng Ngày 4 tháng 9 năm 2019 24 / 40
25. Nhóm hàm ký tự Tên hàm Ý nghĩa Ví dụ Giá trị trả về Char(n) Trả về ký tự =Char(65) A tương ứng với giá trị n trong bảng mã ASCII Left(text, n) Lấy n ký tự bên =Left(“K53QTKD”, K53Q trái của text 4) Right(text, n) Lấy n ký tự bên =Right(“K53QTKD”, QTKD phải của text 4) Len(text) Trả về độ dài =Len("K53QTKD") 7 của text Bảng 10: Một số hàm ký tự ndhcuong (HCE) Tin học ứng dụng Ngày 4 tháng 9 năm 2019 25 / 40
26. Nhóm hàm ký tự (tt) Tên hàm Ý nghĩa Ví dụ Giá trị trả về Mid(text, start, Lấy n ký tự từ =Mid("K53QTKD", 53Q n) vị trí start trong 2, 3) text Lower(text) In thường text =Lower(“K53QTKD”) k53qtkd Upper(text) In hoa text =Upper(“k53qtkd”) K53QTKD Proper(text) In hoa các chữ =Proper(“lớp Lớp K53 cái đầu từ của k53”) text Bảng 11: Một số hàm ký tự (tt) ndhcuong (HCE) Tin học ứng dụng Ngày 4 tháng 9 năm 2019 26 / 40
27. Nhóm hàm ký tự (tt) Tên hàm Ý nghĩa Ví dụ Giá trị trả về Rept(text, n) Lặp lại text liên =Rept("K53", K53 K53 tiếp n lần 2) Trim(text) Cắt bỏ các ký =Trim(“ K53 “) K53 tự trắng hai đầu của text Bảng 12: Một số hàm ký tự (tt) ndhcuong (HCE) Tin học ứng dụng Ngày 4 tháng 9 năm 2019 27 / 40
28. Nội dung 1 Giới thiệu MS Excel 2010 2 Các thao tác cơ bản 3 Hàm trong Excel 4 Nhóm hàm toán học 5 Nhóm hàm logic 6 Nhóm hàm thống kê 7 Nhóm hàm ngày tháng 8 Nhóm hàm kí tự 9 Nhóm hàm tìm kiếm 10 Vẽ đồ thị 11 In ấn bảng tính ndhcuong (HCE) Tin học ứng dụng Ngày 4 tháng 9 năm 2019 28 / 40
29. Nhóm hàm tìm kiếm Hàm tìm kiếm VLOOKUP =VLOOKUP(trị dò, bảng dò, thứ tự cột cần điền, cách dò) Hàm tìm kiếm HLOOKUP =HLOOKUP(trị dò, bảng dò, thứ tự dòng cần điền, cách dò) Trong đó: Trị dò: Chứa giá trị cần dò tìm. Bảng dò: Chứa Trị dò và các giá trị trả về. Nên dùng địa chỉ tuyệt đối Thứ tự cột/dòng cần điền: Cột/dòng đầu tiên trong Bảng dò được tính là 1, tiếp đến 2, Cách dò: 0: Tìm giá trị chính xác với trị dò; 1 (mặc định): lấy giá trị xấp xỉ với trị dò ( Bảng dò sắp xếp tăng dần) ndhcuong (HCE) Tin học ứng dụng Ngày 4 tháng 9 năm 2019 29 / 40
30. Nhóm hàm tìm kiếm - VLOOKUP & HLOOKUP Dựa vào Bảng giải thưởng 1 (hoặc 2), hãy tính tiền Trúng, Tặng thêm cho Tèo, Bi, Tí,. . . Dò chính xác (cách dò = 0). Hình 2: Minh họa (nguồn: [2]) ndhcuong (HCE) Tin học ứng dụng Ngày 4 tháng 9 năm 2019 30 / 40
31. Nhóm hàm tìm kiếm - VLOOKUP & HLOOKUP (tt) Hướng dẫn Tại ô C8 ta gõ: =VLOOKUP(B8, $A$3 : $C$5, 2, 0) hoặc =HLOOKUP(B8, $F$2 : $H$4, 2, 0) sẽ cho kết quả 500 Tại ô C12 ta gõ: =VLOOKUP(B12, $A$3 : $C$5, 2, 0) sẽ cho kết quả ]N/A Hình 3: Minh họa (nguồn: [2]) ndhcuong (HCE) Tin học ứng dụng Ngày 4 tháng 9 năm 2019 31 / 40
32. Nhóm hàm tìm kiếm - VLOOKUP & HLOOKUP (tt) Trong một đợt thi ném lon, số tiền thưởng sẽ tương ứng với số lon mà người chơi ném được với số liệu được cho trong bảng thưởng 1 (hoặc 2). Hãy xác định số tiền thưởng cho Tèo, Bi, Tí, Xíu. Dò không chính xác (cách dò = 1) Hình 4: Minh họa (nguồn: [2]) ndhcuong (HCE) Tin học ứng dụng Ngày 4 tháng 9 năm 2019 32 / 40
33. Nhóm hàm tìm kiếm - VLOOKUP & HLOOKUP (tt) Hướng dẫn Tại ô C25 ta gõ: =VLOOKUP(B25, $B$20 : $C$22, 2, 1) Hình 5: Minh họa (nguồn: [2]) ndhcuong (HCE) Tin học ứng dụng Ngày 4 tháng 9 năm 2019 33 / 40
34. Nhóm hàm tìm kiếm - Hàm MATCH Mục đích của hàm MATCH dùng để chỉ thứ tự giá trị dò tìm dựa trên miền địa chỉ cột/dòng của Mảng dò. Cú pháp hàm MATCH: =MATCH(Trị dò, Mảng dò, Cách dò) Trong đó: Trị dò: Chứa giá trị ta cần dò. Trị dò có thể là: một giá trị, một tham chiếu, hoặc một hằng chuỗi. Mảng dò: Là một dãy liên tiếp các ô chứa các giá trị dò Cách dò : 0: Lấy bằng với trị dò. Mảng dò không cần sắp xếp. 1: Lấy xấp xỉ với trị dò. Mảng dò được sắp xếp tăng dần. -1: Lấy xấp xỉ với trị dò. Mảng dò được sắp xếp giảm dần. ndhcuong (HCE) Tin học ứng dụng Ngày 4 tháng 9 năm 2019 34 / 40
35. Nhóm hàm tìm kiếm - Hàm MATCH (tt) Ví dụ: Cho bảng số liệu sau. Hãy: Xác định C nằm ở vị trí thứ mấy trong vùng E2:E5 Xác định Xăng A92 nằm ở vị trí thứ mấy trong vùng E4:G4 Hình 6: Minh họa (nguồn: [2]) Hướng dẫn: =MATCH(“C”, E2:E5, 0) → cho kết quả là 3 vì “C” nằm ở vị trí thứ 3 đếm từ trên xuống. =MATCH(“Xăng A92”, E4:G4, 0) → kết quả là 2 vì “Xăng A92” nằm ở vị trí thứ 2 đếm từ trái sang phải. ndhcuong (HCE) Tin học ứng dụng Ngày 4 tháng 9 năm 2019 35 / 40
36. Nội dung 1 Giới thiệu MS Excel 2010 2 Các thao tác cơ bản 3 Hàm trong Excel 4 Nhóm hàm toán học 5 Nhóm hàm logic 6 Nhóm hàm thống kê 7 Nhóm hàm ngày tháng 8 Nhóm hàm kí tự 9 Nhóm hàm tìm kiếm 10 Vẽ đồ thị 11 In ấn bảng tính ndhcuong (HCE) Tin học ứng dụng Ngày 4 tháng 9 năm 2019 36 / 40
37. Vẽ đồ thị Chèn biểu đồ: Chọn vùng dữ liệu Insert → Chart (hoặc nhấn biểu tượng) Các bước thao tác vẽ biểu đồ: 1 Chọn loại biểu đồ (chart type) 2 Chọn dữ liệu nguồn cho biểu đồ (Data range/Series (Name, value, x-label). 3 Tùy chọn: đặt tên biểu đồ, tên trục Hình 7: Minh họa 4 Lập vị trí biểu đồ trong bảng tính ndhcuong (HCE) Tin học ứng dụng Ngày 4 tháng 9 năm 2019 37 / 40
38. Nội dung 1 Giới thiệu MS Excel 2010 2 Các thao tác cơ bản 3 Hàm trong Excel 4 Nhóm hàm toán học 5 Nhóm hàm logic 6 Nhóm hàm thống kê 7 Nhóm hàm ngày tháng 8 Nhóm hàm kí tự 9 Nhóm hàm tìm kiếm 10 Vẽ đồ thị 11 In ấn bảng tính ndhcuong (HCE) Tin học ứng dụng Ngày 4 tháng 9 năm 2019 38 / 40
39. In ấn bảng tính 1 Định dạng trang in (Page Layout → Page Setup) 2 Chọn loại giấy 3 Đặt lề trang in 4 Đặt tiêu đề trang in 5 Chọn miền để in 6 Thực hiện in ấn Hình 8: Minh họa ndhcuong (HCE) Tin học ứng dụng Ngày 4 tháng 9 năm 2019 39 / 40
40. Tài liệu tham khảoI [1] digiscapegallery.com. Microsoft excel 2010. 2019. [2] ThS. Sử Minh Đạt. Bài giảng tin học văn phòng. ndhcuong (HCE) Tin học ứng dụng Ngày 4 tháng 9 năm 2019 40 / 40