›
›

Bài giảng Tin học đại cương - Chương 6: Phần mềm bảng tính

pptx 124 trang ngocly 30
Download
Bạn đang xem 20 trang mẫu của tài liệu "Bài giảng Tin học đại cương - Chương 6: Phần mềm bảng tính", để tải tài liệu gốc về máy bạn click vào nút DOWNLOAD ở trên

Tài liệu đính kèm:

  • pptxbai_giang_tin_hoc_dai_cuong_chuong_6_phan_mem_bang_tinh.pptx

Nội dung text: Bài giảng Tin học đại cương - Chương 6: Phần mềm bảng tính

  1. Bài giảng Tin học đại cương CHƯƠNG 6 PHẦN MỀM BẢNG TÍNH Bộ môn Tin học cơ sở - Khoa CNTT Trường ĐH Ngân hàng TP.HCM Chương 5 - Phần mềm trình chiếu 1
  2. Nội dung 1. Khái niệm phần mềm bảng tính (PMBT) 2. Phần mềm bảng tính MS Excel 3. Nhập và hiệu chỉnh nội dung bảng tính 4. Định dạng và thay đổi cấu trúc BT 5. Sử dụng hàm trong Excel 6. Quản trị cơ sở dữ liệu 7. Biểu đồ 8. Một số chức năng khác Chương 5 - Phần mềm trình chiếu 2
  3. 1. Khái niệm PMBT  Ứng dụng tương tác tổ chức và phân tích dữ liệu dưới dạng bảng.  Bảng tính đầu tiên cho máy vi tính:VisiCalc (1979).  Các phần mềm bảng tính thông dụng ◦ Lotus 1-2-3 (Lotus software – IBM). ◦ MS Excel (Microsoft). ◦ Calc (OpenOffice). ◦ Google Docs (Google). Chương 5 - Phần mềm trình chiếu 3
  4. Các tính năng cơ bản ◦ Hỗ trợ lập công thức. ◦ Tự động tính lại. ◦ Thư viện hàm. ◦ Quản trị cơ sở dữ liệu. ◦ Biểu đồ. ◦ Macro. Chương 5 - Phần mềm trình chiếu 4
  5. 2. PMBT MS Excel  Ứng dụng bảng tính phát triển cho MS Windows và Mac OS X.  Được tích hợp trong bộ công cụ MS Office.  Phiên bản đầu cho Windows: MS Excel 2.0 (1967).  Phiên bản cuối: MS Excel 2013.  Phiên bản phổ biến hiện nay: MS Excel 2010. Chương 5 - Phần mềm trình chiếu 5
  6. Giao diện MS Excel 2010  Phong cách tương tự như Word và PowerPoint.  Vùng làm việc: Bảng => Hàng, cột, Ô.  Thanh công thức: Địa chỉ & nội dung/ công thức trong ô hiện hành.  Thanh Tab: Lựa chọn trang bảng tính. Chương 5 - Phần mềm trình chiếu 6
  7. Chương 5 - Phần mềm trình chiếu 7
  8. Cấu trúc sổ bảng tính  Tập tin bảng tính => sổ bảng tính.  Sổ bảng tính => trang bảng tính.  Số trang ban đầu: được ấn định sẵn (chế độ mặc định).  Số trang tối đa: 255 (Excel 97 - 2003), chỉ bị giới hạn bởi bộ nhớ (Excel 2007 – 2010)  Chuyển trang hiện hành: sử dụng thanh Tab. Chương 5 - Phần mềm trình chiếu 8
  9. Trang, hàng, cột  Tên mặc định: Sheet1, Sheet2, (có thể thay đổi).  Được chia thành nhiều hàng, cột (65,536 hàng, 256 cột với Excel 97 – 2003; 1,048,576 hàng, 16,384 cột với Excel 2010).  Đánh số hàng, cột: ◦ Hàng: 1 65536 ◦ Cột: A IV Chương 5 - Phần mềm trình chiếu 9
  10. Ô, vùng  Ô: giao điểm hàng, cột (đơn vị cơ sở).  Vùng: khối ô (nhiều hàng, cột) trong bảng.  Địa chỉ ô: ◦ (trang hiện hành). ◦ [ ] (trang khác).  Địa chỉ vùng: : Chương 5 - Phần mềm trình chiếu 10
  11. Nội dung bảng tính  Nội dung các ô: ◦ Chuỗi văn bản. ◦ Con số ◦ Ngày tháng, thời gian. ◦ Giá trị luận ký. ◦ Công thức.  Các đối tượng chèn thêm (biểu tượng, hình ảnh, sơ đồ, biểu đồ, ). Chương 5 - Phần mềm trình chiếu 11
  12. Thiết lập chế độ mặc định  Công cụ: Hộp thoại Excel Option.  Một số thao tác cơ bản: ◦ Đặt font chữ và số trang mặc định. ◦ Đặt chế độ lưu trữ tự động. ◦ Tùy chỉnh thanh công cụ Quick Access. ◦ Cài đặt các công cụ Add-Ins. Chương 5 - Phần mềm trình chiếu 12
  13. Hộp thoại Excel Option Chương 5 - Phần mềm trình chiếu 13
  14. Làm việc với tập tin  Tập tin bảng tính: kiểu mặc định .xls (Excel 97 – 2003), .xlsx (Excel 2007 – 2010).  Các thao tác cơ bản: tương tự như Word và PowerPoint. Chương 5 - Phần mềm trình chiếu 14
  15. 3. Nhập và hiệu chỉnh nội dung bảng tính a. Các nguyên tắc cơ bản. b. Xây dựng công thức. c. Sử dụng địa chỉ tương đối và địa chỉ tuyệt đối. d. Sử dụng công cụ tự động lấp đầy. e. Kiểm soát dữ liệu nhập với tính năng Data Validation. Chương 5 - Phần mềm trình chiếu 15
  16. Các nguyên tắc cơ bản  Mỗi ô bảng tính chứa một giá trị (DL nhập/ KQ tính toán) thuộc một kiểu xác định (chuỗi ký tự, số, ngày tháng, thời gian, giá trị luận lý).  Excel tự xác định kiểu cho các dữ liệu nhập.  Trường hợp nhập nhằng: cần nhập dữ liệu theo các qui tắc nhập dữ liệu tường minh. Chương 5 - Phần mềm trình chiếu 16
  17. Các qui tắc nhập liệu cơ bản  Chuỗi ký tự: Gõ nhập bình thường hoặc bắt đầu bởi dấu nháy đơn (“’”).  Số: gõ nhập bình thường (hoặc bắt đầu bởi các dấu “+”, “-”, “=“).  Ngày tháng, thời gian: Nhập theo dạng được hệ thống qui định.  Giá trị luận lý: TRUE/FALSE.  Công thức: Bắt đầu bởi các dấu “+”, “-”, “=“. Chương 5 - Phần mềm trình chiếu 17
  18. ▪ Excel tự động loại bỏ các chữ số 0 vô nghĩa trước các số. ▪ Các giá trị số có độ dài lớn hơn chiều rộng ô sẽ được hiển thị dưới dạng chuỗi dấu “#”. ▪ Mọi chuỗi nhập bắt đầu bởi dấu “+”, “- ”, “=” đều được hiểu là số hoặc công thức. Chương 5 - Phần mềm trình chiếu 18
  19. Gõ dấu “’” trước chuỗi ký tự có dạng tựa con số hoặc công thức ! Chương 5 - Phần mềm trình chiếu 19
  20. Ví dụ: Bảng danh sách nhân viên  Nhập thông tin nhân viên: ◦ Mã NV: 000104 ◦ Họ tên: Nguyễn Văn Dũng ◦ Ngày sinh: 16/06/2982 ◦ Số ĐT: 0906123456  Gõ dòng ghi chú bắt đầu bởi dấu gạch đầu dồng “-”.  Tham khảo Chương 5 - Phần mềm trình chiếu 20
  21. Xây dựng công thức  Bắt đầu bởi dấu “=”, “+” , “-”.  Thành phần: ◦ Hằng (số, chuỗi, ngày tháng, luận lý). ◦ Địa chỉ ô. ◦ Hàm. Chương 5 - Phần mềm trình chiếu 21
  22. Xây dựng công thức (tt)  Toán tử: ◦ Toán tử số: +, -, *, /, ^. ◦ Toán tử nối chuỗi: &: . ◦ Toán tử ngày tháng, thời gian: +, -.  Ví dụ: Formulas.xlsx Chương 5 - Phần mềm trình chiếu 22
  23. Sử dụng ĐC tương đối và ĐC tuyệt đối  Có thể sử dụng các thao tác chép – dán (hoặc cắt – dán) để tạo ra các khối dữ liệu và công thức tương tự. Chương 5 - Phần mềm trình chiếu 23
  24. - Địa chỉ ô trong công thức thay đổi ra sao khi di chuyển ? Chương 5 - Phần mềm trình chiếu 24
  25. - Tùy thuộc vào loại địa chỉ được sử dụng. Chương 5 - Phần mềm trình chiếu 25
  26. Địa chỉ tương đối  Là các địa chỉ thông thường, tự động thay đổi tương tứng khi sao chép, di chuyển công thức.  Ví dụ: Chép công thức trong ô D5 sang ô E6. [D5] = B5 * C5 => [E6] = C6 * D6 Chương 5 - Phần mềm trình chiếu 26
  27. Địa chỉ tương đối  Là các địa chỉ cố định, không thay đổi khi sao chép, di chuyển công thức.  Có thể cố định hàng, cột hay cả hai trong địa chỉ ô.  Ví dụ: Chép công thức trong ô E5 sang ô E66. [E5] = D5 * $E$2 => [E6] = D6 * $E$2 Chương 5 - Phần mềm trình chiếu 27
  28. Ví dụ ứng dụng  Lập bảng tính tiền bán hàng cho một cửa hàng tại sân bay quốc tế. Số tiền mỗi loại hàng được tính theo JPY và được qui đổi sang VND theo tỷ giá qui định.  Xem Hoa don.xlsx Chương 5 - Phần mềm trình chiếu 28
  29. Sử dụng công cụ tự động lấp đầy (AutoFill)  Cho phép tạo nhanh chuỗi giá trị tăng hoặc giảm theo mẫu (hai giá trị đầu trong chuỗi).  Ví dụ ứng dụng: ◦ Tạo dãy số thứ tự (1, 2, 3, ). ◦ Tạo dãy giá trị tuần tự (0, 50, 100, 150, ). Chương 5 - Phần mềm trình chiếu 29
  30. Sử dụng chức năng AutoFill Chương 5 - Phần mềm trình chiếu 30
  31. Kiểm soát dữ liệu nhập với Data Validation  Chỉ cho phép nhập một số kiểu dữ liệu nhất định => hạn chế sai sót khi nhập liệu.  Công cụ: Hộp thoại Data Validation. Chương 5 - Phần mềm trình chiếu 31
  32. Chương 5 - Phần mềm trình chiếu 32
  33. Kiểm soát dữ liệu nhập với Data Validation (tt)  Các dữ liệu được phép nhập ◦ Mọi giá trị. ◦ Các loại số. ◦ Số thập phân. ◦ Các phần tử trong danh sách. ◦ Ngày tháng/thời gian. ◦ Chuỗi ký tự độ dài giới hạn. ◦ Dạng chỉ định . Chương 5 - Phần mềm trình chiếu 33
  34. Kiểm soát dữ liệu nhập với Data Validation (tt)  Phạm vi số/ngày tháng/thời gian/độ dài chuỗi: ◦ Between/not between (min, max). ◦ Equal to/Not equal to (value). ◦ Greate than/Less than (value). ◦ Greate than or equal to (value). ◦ Less than or equal to (value). Chương 5 - Phần mềm trình chiếu 34
  35. Ví dụ ứng dụng  Thiết lập Data Validation kiểm soát mã loại hàng và số lương hàng bán (sheet Hoa don).  Qui tắc kiểm tra: ◦ Mả hàng: lấy từ Danh sách loại hàng (sheet Loai hang). ◦ Số lượng: Số thập phân lớn hơn 0.  Xem Data Validation.xlsx Chương 5 - Phần mềm trình chiếu 35
  36. 4. Định dạng và thay đổi cấu trúc bảng tính a. Làm việc với trang bảng tính b. Làm việc với các hàng và cột c. Làm việc với các ô và vùng Chương 5 - Phần mềm trình chiếu 36
  37. Làm việc với trang bảng tính  Các thao tác cơ bản: ◦ Chèn trang mới. ◦ Đổi tên trang. ◦ Xóa trang bảng tính. ◦ Sao chép, di chuyển trang ◦ Ẩn/hiện trang. ◦ Tạo nền trang. Chương 5 - Phần mềm trình chiếu 37
  38. Làm việc với các hàng và cột  Các thao tác cơ bản: ◦ Thêm hàng, cột. ◦ Xóa hàng, cột. ◦ Ẩn/hiện hàng, cột. ◦ Thay đổi kích thước hàng, cột. Chương 5 - Phần mềm trình chiếu 38
  39. Làm việc với ô và vùng  Các thao tác cơ bản: ◦ Định dạng ô. ◦ Trộn và tách ô. ◦ Đặt tên và quản lý tên vùng. ◦ Dán đặc biệt. ◦ Tạo ghi chú. ◦ Định dạng có điều kiện. ◦ Xóa các thành phần trong ô. Chương 5 - Phần mềm trình chiếu 39
  40. Định dạng ô  Công cụ: Hộp thoại Fotmat Cell/các lệnh trên thẻ Home.  Các nhóm định dạng ◦ Địng dạng số. ◦ Canh ô. ◦ Định dạng font chữ. ◦ Tạo khung. ◦ Chọn màu nền. ◦ Chế độ bảo vệ. Chương 5 - Phần mềm trình chiếu 40
  41. Định dạng số (Number)  Các lựa chọn: ◼ General : Dạng tổng quát ◼ Number : Số thập phân ◼ Currency : Dạng tiền tệ ◼ Date/Time : Ngày tháng/Thời gian. ◼ Percentage: Dạng phần trăm ◼ Text : Dạng văn bản ◼ Custom : Dạng tùy ý Chương 5 - Phần mềm trình chiếu 41
  42. Ví dụ  Cho 4 số + 134.567, -1234.567, 0.123 và 0. Hiển thị các số trên theo các dạng khác nhau.  Xem Number.xlsx Chương 5 - Phần mềm trình chiếu 43
  43. Canh ô (Aligmrnt)  Bao gồm: ◦ Canh ngang (General, Left, Right, Center, Fill, Justified, ). ◦ Canh dọc (Top, Bottom, Center, Justify, ). ◦ Độ thụt (Indent). ◦ Chiều chữ (Orrientation). ◦ Cho phép xuống hàng, trộn ô, tự động thu nhỏ cỡ chữ. Chương 5 - Phần mềm trình chiếu 44
  44. Trộn và tách ô  Thực hiện tương tự như trong Word.  Các cách thực hiện: ◦ Sử dụng hộp thoại Fotmat Cell (The Alignment). ◦ Sử dụng thực đơn pop-up. ◦ Sử dụng thanh công cụ (Lệnh Home | Alignment | Merge & Center). Chương 5 - Phần mềm trình chiếu 46
  45. Ví dụ ứng dụng  Tạo mẫu bảng Tổng kết năm học 2012 - 2013.  Xem Tong ket nam hoc.xlsx Chương 5 - Phần mềm trình chiếu 47
  46. Đặt tên và quản lý tên vùng  Excel cho phép đặt tên và quản lý tên cho các vùng ô.  Tên vùng  địa chỉ tuyệt đối vùng ô.  Các thao tác cơ bản: ◦ Đặt tên vùng. ◦ Thay đổi tên vùng, địa chỉ vùng. ◦ Xóa tên vùng.  Công cụ: Lệnh Define Name/Name Management. Chương 5 - Phần mềm trình chiếu 48
  47. Chương 5 - Phần mềm trình chiếu 49
  48. Dán đặc biệt  Cho phép sao chép một số thành phần nào đó của một vùng ô.  Có thể thực hiện một số tính toán khi sao chép.  Công cụ: Lệnh Paste Special. Chương 5 - Phần mềm trình chiếu 50
  49. Ví dụ ứng dụng  Cho bảng .  Yêu cầu: ◦ Ghép cột Họ và cột Tên thành một cột duy nhất Họ và tên. ◦ Chuyển bảng Tiêu chuẩn xếp loại học lực từ dạng hàng sang dạng cột. ◦ Cộng thêm 0.5 điểm cho môn M1. ◦ Chép mẫu (định dạng) bảng Tổng kết cuối năm sang sheet Sheet2. Chương 5 - Phần mềm trình chiếu 52
  50. Tạo ghi chú.  Ghi chú: chuỗi văn bản được hiển thị khi đưa con trỏ tới ô có ghi chú.  Công dụng: Làm rõ thêm nội dung, ý nghĩa ô.  Tạo, xóa ghi chú: Lệnh Insert Comment/Delete Comment (thực đơn pop-up/ngăn lệnh Review | Comments). Chương 5 - Phần mềm trình chiếu 53
  51. Định dạng có điều kiện  Định dạng được áp dụng khi thỏa mãn điều kiện nhất định.  Công cụ: Lệnh Conditional Formatting (Thẻ Home | Cells)  Ví dụ: Cho bảng danh sách điểm thi.  Yêu cầu: Tô màu nền các ô điểm thi và điểm tổng kết dưới 5.  Xem Conditional Formatting.xlsx Chương 5 - Phần mềm trình chiếu 54
  52. Chương 5 - Phần mềm trình chiếu 55
  53. Chương 5 - Phần mềm trình chiếu 56
  54. Xóa các thành phần trong ô  Công cụ: Lệnh Clear (thẻ Home | Cells).  Các lựa chọn xóa: ◦ Clear All: Xóa hết . ◦ Clear Formats: Xóa định dạng. ◦ Clear Content: Xóa nội dung. ◦ Clear Comments: Xóa ghi chú. ◦ Clear Hyperlink: Xóa hyperlink. Chương 5 - Phần mềm trình chiếu 57
  55. 5. Sử sụng hàm trong Excel a. Giới thiệu tổng quan b. Đưa hàm vào công thức c. Một số loại hàm thông dụng Chương 5 - Phần mềm trình chiếu 58
  56. Giới thiệu tổng quan  Hàm: công thức được xây dựng sẵn thực hiện công việc tính toán.  Excel có thư viện hàm phong phú với nhiều loại hàm.  Cú pháp chung của hàm: Tên_hàm (Ds tham số)  Người sử dụng có thể viết thêm các hàm mới bằng ngôn ngữ VBA. Chương 5 - Phần mềm trình chiếu 59
  57. Đưa hàm vào công thức  Chọn một trong hai cách: ◦ Gõ nhập trực tiếp từ bàn phím. ◦ Sử dụng chức năng Insert function.  Excrl đưa ra các thông báo giúp đỡ khi nhập hàm. Chương 5 - Phần mềm trình chiếu 60
  58. Chương 5 - Phần mềm trình chiếu 61
  59. Nhập tham số hàm trên hộp thoại Insert Function Chương 5 - Phần mềm trình chiếu 62
  60. Một số hàm thông dụng  Hàm số học và lượng giác.  Hàm ký tự.  Hàm ngày tháng & thời gian.  Hàm thống kê. Chương 5 - Phần mềm trình chiếu 63
  61. Hàm số học và lượng giác  ABS( )  INT( )  MOD( , )  PRODUCT( , [số 2], )  RAND()  ROUND( , )  SQRT( ) Chương 5 - Phần mềm trình chiếu 64
  62. Hàm số học và lượng giác  SUM( , [số 2], )  SUMPRODUCT ( , [vùng 2], )  PI()  COS( )  SIN( )  TAN( ) Chương 5 - Phần mềm trình chiếu 65
  63. Ví dụ  Sử dụng hàm ROUND để làm tròn số X tới hàng nghìn và hàng phần trăm.  Cho số lượng và đơn giá các loại hàng mua, tính tổng số tiền phải trả.  Tính số xe 12 chỗ phải huy động để chở các đoàn khách du lịch.  Xem Math Functions.xlsx Chương 5 - Phần mềm trình chiếu 66
  64. Hàm ký tự  LEFT( , [n])  RIGHT( , [n])  MID( , , )  UPPER( )  LOWER( )  PROPER( )  LEN( )  TRIM( )  FIND( , ,[ ]) Chương 5 - Phần mềm trình chiếu 67
  65. Ví dụ  Cho bảng danh sách hàng nhập/xuất tại một công ty với mỗi lô hàng được đánh một mã lô hàng như sau: ◦ Ký tự 1: N/X (Nhập/Xuất). ◦ Ký tự 2 – 4: Mã loại hàng. ◦ Ký tự 5 – 8: Mã nhà sản xuất.  Cho biết loại (nhập/xuất), mã loại hàng, mã nhà SX của từng lô hàng. Chương 5 - Phần mềm trình chiếu 68
  66. Ví dụ  Cho danh sách địa chỉ email của các SV trong lớp. Tách tên tài khoản và tên miền trong các địa chỉ được nêu.  Xem Ds hang nhap-xuat.xlsx. Chương 5 - Phần mềm trình chiếu 69
  67. Hàm ngày tháng & thời gian  NOW()  TODAY()  DATE( , , )  DAY( )  MONTH( )  YEAR( )  WEEKDAY( , [kiểu]) Chương 5 - Phần mềm trình chiếu 70
  68. Hàm chuyển đổi kiểu  VALUE( )  TEXT( , [ ])  DATEVALUE ( ) Chương 5 - Phần mềm trình chiếu 71
  69. Hàm luận lý  NOT( )  AND( , [biểu thức logic2], )  OR( , [biểu thức logic2], )  IF( , [gt1], [gt2]) Chương 5 - Phần mềm trình chiếu 72
  70. Ví dụ  Một cơ quan bảo hiểm xét chế độ nghỉ hưu cho các CBCNV đủ tuổi hưu (nam từ 60 tuổi, nữ từ 55 tuổi trở lên). CBCNV đủ tuổi hưu có số năm công tác từ 25 năm trở lên được lãnh lương hưu hàng tháng, ngược lại, được lĩnh trợ cấp một lần.  Cho biết KQ xét nghỉ của các CBCNV.  Xem xet nghi hưu.xlsx. Chương 5 - Phần mềm trình chiếu 73
  71. Hàm thống kê  MAX( , [gt2], )  MIN( , [gt2], )  AVERAGE( , [gt2], )  AVERAGA( , [gt2], )  AVERAGEIF( , , [vùng tính toán])  COUNT( , [n2], )  COUNTA( , [n2], )  COUNTBLANK( ) Chương 5 - Phần mềm trình chiếu 74
  72. Hàm thống kê (tt)  SUMIF( , , [vùng tính toán])  SUMIFS ( , , , [Vùng_ĐK2, điều kiện 2], )  RANK( , , [cách xếp]) Chương 5 - Phần mềm trình chiếu 75
  73. Ví dụ  Cho danh mục hàng xuất tháng 10/2013 của một công ty (mã loại hàng, số lượng, đơn giá, thành tiền). Lập bảng báo cáo thống kê tổng lượng hàng bán, tổng doanh số, giá bán bình quân của từng loại hàng.  Xem Bao cao thong ke.xlsx. Chương 5 - Phần mềm trình chiếu 76
  74. Hàm tìm kiếm và tham chiếu  VLOOKUP( , , , [cách tìm])  HLOOKUP( , , , [cách tìm])  INDEX( , , )  Các kiểu dò tìm trong VLOOKUP và HLOOKUP: ◦ Dò tìm chính xác. ◦ Dò tìm xấp xỉ. Chương 5 - Phần mềm trình chiếu 77
  75. Ví dụ  Căn cứ vào mã loại hàng, dò tìm tên hàng, đơn vị, đơn giá của các loại hàng trong bảng danh mục loại hàng (sheet HOA DON).  Căn cứ vào điểm tổng kết, tìm loại học lực của sinh viên trong bảng tiêu chuẩn xếp loại học lực (sheet TONG KET).  Xem VLOOHUP.xlsx. Chương 5 - Phần mềm trình chiếu 78
  76. Hàm thông tin  ISBLANK ( )  ISTEXT ( )  ISNONTEXT ( )  ISNUMBER ( )  ISERR ( )  ISERROR( )  ISNA ( ) Chương 5 - Phần mềm trình chiếu 79
  77. Ví dụ  Cho danh sách khách thuê phòng tại một khách sạn. Đánh dấu “X” vào cột Ghi chú đối với các khách hàng thuê phòng từ lần thứ 2 trở lên.  Xem Khach san.xlsx. Chương 5 - Phần mềm trình chiếu 80
  78. 6. Quản trị cơ sở dữ liệu a. Tổng quan về CSDL trong Excel b. Một số thao tác CSDL thông dụng c. Hàm cơ sở dữ liệu Chương 5 - Phần mềm trình chiếu 81
  79. Tổng quan về CSDL trong Excel  Cơ sở dữ liệu: tập hợp các dữ liệu được tổ chức, lưu trữ theo một cách thức nào đó cùng một tập các phép toán để thao tác, sử dụng.  Excel: CSDL phẳng (bảng 2 chiều). Chương 5 - Phần mềm trình chiếu 82
  80. Cấu trúc CSDL Excel  Dòng: Mẫu tin (Records).  Cột: Trường (Fields).  Dòng 1: Dòng tên trường.  Lưu ý: ◦ Bảng không chứa các ô được trộn. ◦ Các ô tên trường không được để trống hoặc trùng giá trị. Chương 5 - Phần mềm trình chiếu 83
  81. Chương 5 - Phần mềm trình chiếu 84
  82. Hidden Chương 5 - Phần mềm trình chiếu 85
  83. Ưu điểm  Không đòi hỏi kiến thức chuyên sâu về CSDL.  Thuận lợi cho đa số người dùng.  Phạm vi sử dụng rộng rãi.  Có nhiều khả năng phân tích. Chương 5 - Phần mềm trình chiếu 86
  84. Khuyết điểm:  Kích thước lưu trữ có giới hạn.  Xử lý chậm.  Không có khả năng thiết lập mối quan hệ.  Không đảm bảo sự nhất quán và toàn vẹn dữ liệu.  Hạn chế việc sử dụng đồng thời. Chương 5 - Phần mềm trình chiếu 87
  85. Một số thao tác cơ bản  Sắp xếp dữ liệu .  Lọc dữ liệu.  Tính tổng phụ.  Hợp nhất dữ liệu.  Lập báo cáo tổng hợp.  Tách nội dung ô thành nhiều cột. Chương 5 - Phần mềm trình chiếu 88
  86. Sắp xếp dữ liệu  Sắp xếp các dòng CSDL theo thứ tự tăng (hoặc giảm) của một số cột.  Công cụ: Lệnh Sort (thẻ Data | Sort & Filter). Chương 5 - Phần mềm trình chiếu 89
  87. Chương 5 - Phần mềm trình chiếu 90
  88. Lọc dữ liệu  Thực hiện trích lọc dữ liệu thỏa những điều kiện được định trước.  Các phương pháp lọc: ◦ Lọc tự động (AutoFilter). ◦ Loc nâng cao (Advanced Filter). Chương 5 - Phần mềm trình chiếu 91
  89. Lọc tự động  Phương pháp lọc đơn giản, dễ thực hiện.  Công cụ: Lệnh Home | Editing | Sort & Filter | Filter hđặc Data | Filter.  Nguyên tắc: Lần lượt lọc theo từng cột (hiển thị thực đơn và chỉ định điều kiện lọc cho từng cột).  Chỉ sử dụng cho các điều kiện lọc không quá phức tạp. Chương 5 - Phần mềm trình chiếu 92
  90. Ví dụ1  Cho CSDL Thống kê bán hàng (tập tin AutoFilter). Sử dụng chức năng AutoFilter, thực hiện các thao tác: ◦ Lọc các dòng chi tiết hai loại hàng A và B bán trong ngày 1/6/2013. ◦ Lọc các dòng chi tiết bán hàng có doanh số từ 1.000 tới dưới 2.000 USD.  Xem AutoFilter.xlsx. Chương 5 - Phần mềm trình chiếu 93
  91. Ví dụ 2  Cho danh sách điểm tổng kết cuối năm của các SV. Thực hiện: ◦ Cho biết DS các SV có tên bắt đầu bằng chữ “H” hoặc chữ “N”. ◦ Cho biết danh sách các SV khá, giỏi có điểm môn D1 từ 7 điểm trở lên.  Xem TKCN.xlsx. Chương 5 - Phần mềm trình chiếu 94
  92. Lọc nâng cao  Cho phép lọc dữ liệu với các điều kiện lọc phức tạp.  Cần tạo vùng điều kiện lọc (Criteria) trước khi thực hiện lọc nâng cao.  Các kiểu vùng điều kiện lọc: ◦ Vùng điều kiện dạng so sánh. ◦ Vùng điều kiện dạng công thức. Chương 5 - Phần mềm trình chiếu 95
  93. Vùng điều kiện dạng so sánh  Gồm nhiều dòng, cột.  Dòng 1: Tên các trường (sao chép hoặc tham chiếu với toán tử “-”).  Các dòng kế tiếp theo: Điều kiện ràng buộc.  Điều kiện mô tả trong các ô cùng hàng được liên kết bởi toán tử AND, điều kiện nêu trong các hàng khác nhau => toán tử OR. Chương 5 - Phần mềm trình chiếu 96
  94. Vùng điều kiện dạng công thức  Gồm nhiều dòng, cột.  Dòng 1: Tên cột điều kiện (không trùng với các tên trường).  Các dòng kế tiếp theo: Điều kiện (tham chiếu tới mẫu tin đầu tiên của CSDL)  Quan hệ giữa các dòng, ô trong vùng ĐK: tương tự như vùng ĐK dạng so sánh. Chương 5 - Phần mềm trình chiếu 97
  95. Thựcc hiện lọc  Sử dụng lệnh Data | Advanced Filte: ◦ Chỉ định vùng CSDL. ◦ Chỉ định vùng điều kiện. ◦ Chỉ định vùng chứa kết quả. ◦ Đặt tùy biến loại bỏ các dòng trùng lặp. Chương 5 - Phần mềm trình chiếu 98
  96. Chương 5 - Phần mềm trình chiếu 99
  97. Ví dụ  Thực hiện lại các yêu cầu được nêu trong hai ví dụ trước với chức năng Advanced Filter.  Xem AdvencedFilter.xlsx.  Xem TKCN.xlsx. Chương 5 - Phần mềm trình chiếu 100
  98. Tính tổng phụ  Thực hiện việc thống kê nhóm với các hàm thống kê như: Sum, Count, Average, Max, Min  Cần sắp xếp lại CSDL theo thứ tự tăng của trường được dùng để phân nhóm trước khi thống kê nhóm.  Công cụ: Lệnh Data | Subtotal. Chương 5 - Phần mềm trình chiếu 101
  99. Chương 5 - Phần mềm trình chiếu 102
  100. Chương 5 - Phần mềm trình chiếu 103
  101. Hợp nhất dữ liệu  Tổng hợp số liệu trên nhiều bảng.  Ví dụ: ◦ Lập bảng tổng hợp doanh số quý từ các bảng số liệu doanh số tháng. ◦ Lập bảng tổng hợp doanh số công ty từ các bảng số liệu doanh thu của các cửa hàng.  Công cụ: Lệnh Consolidate (thẻ Data). Chương 5 - Phần mềm trình chiếu 104
  102. Lập báo cáo tổng hợp  Tóm tắt dữ liệu, công cụ: tạo báo cáo từ CSDL Excel hay nguồn dữ liệu từ bên ngoài.  Công cụ: Lệnh Insert | Pivot Table | Pivot Table.  Ví dụ: Chương 5 - Phần mềm trình chiếu 105
  103. Chương 5 - Phần mềm trình chiếu 106
  104. Chương 5 - Phần mềm trình chiếu 107
  105. Tách nội dung ô thành nhiều cột  Tách chuỗi ký tự nội dung ô thành các từ ghi trong các cột liên tiếp.  Ví dụ: Tách cột Họ tên thành các cột chứa họ, họ lót và tên(mỗi cột chứa một từ trong họ tên).  Công cụ: Lệnh Data | Data Tools | Text to Columns. Chương 5 - Phần mềm trình chiếu 108
  106. Chương 5 - Phần mềm trình chiếu 109
  107. Hàm cơ sở dữ liệu  Thực hiện việc thống kê trên những mẫu tin trong CSDL thỏa mãn điều kiện của vùng tiêu chuẩn được thiết lập.  Cú pháp chung: Tên_hàm( , , ) Với : Tên/Stt trường trong danh sách các trường của CSDL. Chương 5 - Phần mềm trình chiếu 110
  108. Một số hàm CSDL thông dụng  DAVERAGE  DAVERAGEA  DMAX  DMIN  DCOUNT  DCOUNTA  DSUM Xem DB_Function.xlsx Chương 5 - Phần mềm trình chiếu 111
  109. 7. Biểu đồ  Tổng quan về biểu đồ và đồ thị trong Excel.  Các thao tác trên biểu đồ  Sparkline Chương 5 - Phần mềm trình chiếu 112
  110. Tổng quan  Biểu đồ: ◦ Công cụ đồ họa biểu diễn sự tương quan của dữ liệu. ◦ Công cụ phân tích dữ liệu trực quan, giúp nhận biết mối quan hệ phức tạp giữa các dữ liệu. ◦ Công cụ giải quyết các bài toán mô phỏng. Chương 5 - Phần mềm trình chiếu 113
  111. Các dạng biểu đồ  Biểu đồ cột (Column).  Biểu đồ đường (Line).  Biểu đồ tròn (Pie).  Biểu đồ thanh (Bar).  Biểu đồ vùng (Area).  Biểu đồ phân tán (XY - Scatter).  Biểu đồ cổ phiếu (Stock).  Chương 5 - Phần mềm trình chiếu 114
  112. Dữ liệu cho biểu đồ  Bảng số liệu bố trí theo hàng hoặc theo cột.  Các thành phần chính: ◦ Chuỗi dữ liệu (Data Series), sắp xếp theo hàng/cột. ◦ Tên chuỗi (Data Series Name), thường đặt tại đầu chuỗi. ◦ Mục (Category): mục số liệu trong chuỗi. Chương 5 - Phần mềm trình chiếu 115
  113. Ví dụ Bảng thống kê doanh số Chương 5 - Phần mềm trình chiếu 116
  114. Các thành phần của biểu đồ  Tiêu đề.  Trục giá trị (trực chính, trục phụ).  Trục các mục.  Vùng vẽ.  Các điểm dự liệu.  Lưới.  Chú thích. Chương 5 - Phần mềm trình chiếu 117
  115. THỐNG KÊ DOANH SỐ BÁN HÀNG 900 800 800 750 700 720 700 600 600 ố s 500 500 500 500 400 Sp . A 400 Sp. B Doanh 300 300 300 Sp. C 200 200 100 0 2010 2011 2012 2013 Năm Chương 5 - Phần mềm trình chiếu 118
  116. Các thao tác trên biểu đồ  Tạo biểu đồ.  Xóa biểu đồ.  Sao chép biểu đồ.  Di chuyển biểu đồ.  Thay đổi kích thước biểu đồ.  Thêm các thành phần của biểu đồ  Định dạng biểu đồ.  Thay đổi dữ liệu trên biểu đồ. Chương 5 - Phần mềm trình chiếu 119
  117. Các thao tác trên biểu đồ  Đặt tên cho chuỗi dữ liệu.  Hiệu chỉnh nhãn trục X.  Bổ sung thêm các đường xu hướng, xu hướng vào biểu đồ, đồ thị. Chương 5 - Phần mềm trình chiếu 120
  118. Sparkline  Sparkline: đồ thị cỡ nhỏ/đường xu hướng được đặt trong một ô.  Công dụng: giúp nhanh chóng phát hiện các số liệu bất thường trong bảng dữ liệu.  Các thao tác cơ bản: Tạo, hiệu chỉnh, xóa Sparkline.  Công cụ: Lệnh Insert | Sparkline & thanh công cụ Sparkline. Chương 5 - Phần mềm trình chiếu 121
  119. Ví dụ: Thống kê doanh thu Bảng số liệu và Sparkline Chương 5 - Phần mềm trình chiếu 122
  120. 8. Một số chức năng khác  Một số chức năng thông dụng: ◦ In bảng tính. ◦ Điều khiển khung nhìn. ◦ Bảo vệ bảng tính và bảo mật công thức  Thực hiện tương tự như trong Word và PowerPoint. Chương 5 - Phần mềm trình chiếu 123
  121. HẾT CHƯƠNG 6 Chương 5 - Phần mềm trình chiếu 124