›
›

Bài giảng Tin học ứng dụng - Chương III: Excel - Đại học Công nghiệp TP Hồ Chí Minh

pptx 151 trang Hùng Dũng 04/01/2024 770
Download
Bạn đang xem 20 trang mẫu của tài liệu "Bài giảng Tin học ứng dụng - Chương III: Excel - Đại học Công nghiệp TP Hồ Chí Minh", để 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_ung_dung_chuong_iii_excel_dai_hoc_cong_ngh.pptx

Nội dung text: Bài giảng Tin học ứng dụng - Chương III: Excel - Đại học Công nghiệp TP Hồ Chí Minh

  1. BỘ CÔNG THƯƠNG TRƯỜNG ĐH CÔNG NGHIỆP TP. HCM Chương III: Excel.2010
  2. Chương III. Excel I. Giới thiệu Microsoft Excel 2010 II. Thao tác với bảng tính III. Hàm trong Excel IV. Chèn các đối tượng vào bảng tính V. Xử lý dữ liệu VI. In và định dạng trang in VII. Các chức năng khác VIII. Bài tập Excel 9:11 PM Microsoft Excel 2010 2
  3. I.1 Giới thiệu Microsoft Excel 2010 v Microsoft Excel là một phần mềm ­ chương trình ứng dụng, mà khi chạy chương trình ứng dụng này sẽ tạo ra một bảng tính và bảng tính này giúp ta dễ dàng hơn trong việc thực hiện. v Bảng tính Excel bao gồm nhiều Cell được tạo ra giữa cột và hàng. v Hiện nay Excel được sử dụng rộng rãi trong môi trường doanh nghiệp nhằm phục vụ các công việc tính toán từ thông dụng cho đến phức tạp như các chương trình kế toán bằng Excel. 9:11 PM Microsoft Excel 2010 3
  4. I.2 Khởi động Excel ü B1. Từ màn hình (cửa sổ) chính của Windows nhấp chuột nút Start ở góc dưới bên trái ü B2. Di chuyển chuột lên trên đến chữ All Programs, rồi di chuyển chuột sang phải chọn Microsoft Office, sau đó di chuyển chuột đến chữ Microsoft Office Excel 2010 thì dừng lại. ü B3. Nhấp chuột vào biểu tượng để khởi động Excel. Hoặc tại Start ­> Run, gõ Excel nhấn Enter 9:11 PM Microsoft Excel 2010 4
  5. I.3 Thoát khỏi chương trình EXCEL v Thao tác: Nhấp chuột vào nút ở góc trên cùng bên phải để thoát khỏi Excel. Hoặc cũng có thể thoát Excel bằng cách nhấn file tab và chọn Exit . 9:11 PM Microsoft Excel 2010 5
  6. v Note: Các khái niệm căn bản - Worksheet:Workbook: Còn Trong gọi tắtExcel, là sheet, một làworkbook nơi lưu trữ là và một làm tậpviệc vớitin màdữ liệu,trên nóđó cònbạn được làm gọiviệc là (tính bảng toán, tính. Mộtvẽ đồ worksheet thị, ) chứavà lưu nhiều trữ ôdữ (cell), liệu. các Vì ô mỗiđược workbook tổ chức thành có thể các chứacột và cácnhiều dòng. sheet Worksheet (bảng tính), được do chứa vậy bạntrong có workbook. thể tổ chức, Một Worksheetlưu trữ nhiều chứa loại được thông 16,384 tin có liêncột quanvà 1,048,576 với nhau dòng chỉ (phiêntrong mộtbản cũtập chỉ tin chứa (file). được Một 256 workbook cột và 65,536 chứa dòng) rất nhiều. worksheet hay chart sheet. 9:11 PM Microsoft Excel 2010 6
  7. I.4 Các thành phần trong cửa sổ Excel v Cấu trúc của bảng tính WORKBOOK WORKSHEET CHART SHEET 1048566 16384 17.179.869.184DÒNG CỘT CELL 9:11 PM Microsoft Excel 2010 7
  8. v Note: Các khái niệm căn bản - Chart sheet: Cũng là một sheet trong workbook, nhưng nó chỉ chứa một đồ thị. Một chart sheet rất hữu ích khi bạn muốn xem riêng lẻ từng đồ thị. - Sheet tabs: Tên của các sheet sẽ thể hiện trên các tab đặt tại góc trái dưới của cửa sổ workbook. Để di chuyển từ sheet này sang sheet khác ta chỉ việc nhấp chuột vào tên sheet cần đến trong thanh sheet tab. 9:11 PM Microsoft Excel 2010 8
  9. I.4 Các thành phần trong cửa sổ Excel • Title bar • Quick Access Toolbar • Group Ribbon • Name box • Formula bar • Sheet Tab • Status Bar 9:11 PM Microsoft Excel 2010 9
  10. I.4 Các thành phần trong cửa sổ Excel Tuỳ chỉnh Quick Access Toolbar Là thanh công cụ cho phép truy cập nhanh các chức năng thường sử dụng có thể tuỳ chọn sắp xếp các chức năng này. 9:11 PM Microsoft Excel 2010 10
  11. I.4 Các thành phần trong cửa sổ Excel v Ribbon gồm các tab sau: Home Insert Page layout Formulas Data Review View Add-in Developer Và tuỳ theo từng đối tượng sẽ xuất hiện các Tab ngữ cảnh theo từng đối tượng đó 9:11 PM Microsoft Excel 2010 11
  12. II. Thao tác với bảng tính II.1 Các thao tác với tập tin II.1.1 Lưu tập tin ­ Chọn lệnh file\ save hoặc Ctrl + S ­ Hoặc nhấn lệnh save trên thanh quick access Toolbar xuất hiện hộp thoại • Mục File name: gõ vào tên tập tin • Mục Save in: chọn ổ đĩa và Folder muốn lưu tập tin\ nhấn nút save để lưu tập tin • Chọn đuôi mở rộng *.xlsx • Save as types: 9:11 PM Microsoft Excel 2010 12
  13. II.1 Các thao tác với tập tin II.1.2:Tạo tập tin mới ­ Chọn File tab\ New\ Blank Document ­ click trên thanh quick access toolbar (Ctrl + N) II.1.3: Mở tập tin có sẵn ­ Chọn lệnh File\ Open hoặc Ctrl + O ­ Mục look in: chọn thư mục chứa tập tin muốn mở Nội dung của tập tin được chọn sẽ xuất hiện trong danh sách bên dưới. Kích đúp chuột vào tập tin muốn mở. 9:11 PM Microsoft Excel 2010 13
  14. II.2 Thiết lập các thông số cho môi trường làm việc ­ Để khai báo định dạng cho các kiểu dữ liệu trong Excel. Ta phải khai báo các thông số qui ước cho hệ điều hành thao tác như sau: • Vào Start Control Panel Region and Language Xuất hiện hộp thoại Chọn Additional Setting để thêm các định dạng • Xuất hiện hộp thoại Customize Format 9:11 PM Microsoft Excel 2010 14
  15. II.2 Thiết lập các thông số cho môi trường làm việc(tt) Sau khi khai báo các thông số của hệ điều hành tại màn hình Excel chọn Tab File chọn Options xuất hiện hộp thoại Options chọn Tab General thiết lập các thông số cần thiết sau: Standard Font: Chọn Font, Size chữ mặc định  Default file Location: chọn vị trí mặc định khi mở hoặc lưu tập tin EXCEL 9:11 PM Microsoft Excel 2010 15
  16. II.3 Thao tác nhập liệu II.3.1 Di chuyển trong bảng tính Bạn có thể dùng chuột, các phím tab và ­ nhóm phím mũi tên, thanh cuốn dọc, thanh cuốn ngang, sheet tab, các tổ hợp phím tắt để di chuyển qua lại giữa các sheet hay đi đến các nơi bất kỳ trong bảng tính. 9:11 PM Microsoft Excel 2010 16
  17. II.3 Thao tác nhập liệu II.3.2 Nhập liệu dữ liệu Muốn nhập liệu vào Cell nào thì di chuyển con trỏ đến Cell đó Kết thúc nhập liệu bằng phín Enter Nếu Cell đã có nội dung thì sẽ bị thay bằng nội dung mới Nhấn Phím chức năng F2 để hiệu chỉnh nội dung trong 1 Cell Ngắt dòng trong 1 Cell dùng tổ hợp phím Alt + Enter 9:11 PM Microsoft Excel 2010 17
  18. II.3 Thao tác nhập liệu II.3.3 Các kiểu dữ liệu Kiểu Chuỗi, ký tự (Text): khi nhập mặc định kiểu dữ liệu sẽ canh trái trong Cell Kiểu số (Number): khi nhập mặc định kiểu dữ liệu số canh bên phải của Cell Kiểu công thức (formula): là một biểu thức toán học, bắt đầu bằng dấu = hoặc dấu + bao gồm các dữ liệu số, chuỗi, toán tử và hàm 9:11 PM Microsoft Excel 2010 18
  19. II.3 Thao tác nhập liệu Nếu dữ liệu kiểu số hoặc công thức mà có ký tự canh biên(') thì Excel sẽ xem tất cả là dữ liệu kiểu chuỗi Nếu khi nhập các dạng dữ liệu ngày, giờ hoặc số không đúng với định dạng đã khai báo thì excel không nhận diện được sẽ tự động chuyển sang dữ liệu kiểu chuỗi. 9:11 PM Microsoft Excel 2010 19
  20. II.4 Khối dữ liệu và phạm vi thao tác II.4.1 Khái niệm khối ­ Khối là 1 Cell hoặc nhiều Cell liên tục ­ Địa chỉ khối được xác định bằng: : VD: Khối dự liệu B2: E7 là gồm tất cả các Cell từ B2 đến E7 . Cách chọn khối + Chọn một Cell + Chọn một cột + Chọn một khối + Chọn một hàng ­ Ngoài ra có thể dùng Phím Shift + các phím mũi tên để chọn khối. 9:11 PM Microsoft Excel 2010 20
  21. II.4 Khối dữ liệu và phạm vi thao tác II.4.2 Khái niệm phạm vi ­ Gồm 1 khối hoặc nhiều khối. ­ Cách chọn phạm vi nhiều khối: Dùng chuột quét khối đầu tiên, bấm giữ phím Ctrl rồi quét chọn các khối khác 9:11 PM Microsoft Excel 2010 21
  22. II.5 Hiệu chỉnh dữ liệu II.5.1 Sửa dữ liệu ­ Gồm hai cách: Cách 1: nhấp đôi chuột tại Cell cần sửa để sửa sửa xong ấn Enter Cách 2: đặt dấu nháy tại Cell cần sửa rồi ấn phím chức năng F2 để sửa xong nhấn Enter. 9:11 PM Microsoft Excel 2010 22
  23. II.5 Hiệu chỉnh dữ liệu II.5.2 Xoá dữ liệu gồm hai cách ­ Cách 1: Chọn phạm vi cần xoá nhấm phím Delete ( chỉ xoá nội dung) ­ Cách 2: Chọn phạm vi cần xoá chọn Tab Home chọn Editing Clear •Clear All: Xoá tất cả nội dung và định dạng + Chú thích •Clear Formats: Chỉ xoá định dạng •Clear Contents: Chỉ xoá nội dung •Clear Comments: Chỉ xoá chú thích •Clear Hypelinks: Chỉ xoá các liên kết 9:11 PM Microsoft Excel 2010 23
  24. II.5 Hiệu chỉnh dữ liệu II.5.3 Sao chép dữ liệu ­ Cách 1: dùng chuột quét Chọn dữ liệu cần sao chép, nhấn và giữ phím Ctrl di chuyển chuột tới nơi cần sao chép ( di chuyển chuột tại vị trí đường viền của phạm vi). ­ Cách 2: Chọn phạm vi dữ liệu Home Clipboard Copy( Ctrl + C) sau đó đưa con trỏ đến vị trí đích, Chọn Tab Home Group Clipboard Paste( Ctrl + V). Ngoài ra sử dụng nút Handle để sao chép cho các Cell liền kề theo 4 hướng. 9:11 PM Microsoft Excel 2010 24
  25. II.5 Hiệu chỉnh dữ liệu II.5.4 Di chuyển dữ liệu ­ Cách 1: chọn dữ liệu cần sao chép dùng chuột di chuyển tới vị trí cần (di chuyển chuột tại vị trí đường viền của phạm vi). ­ Cách 2: Chọn phạm vi dữ liệu Home Clipboard Cut(Ctrl + X) sau đó đưa con trỏ đến vị trí đích, Chọn Tab Home Group Clipboard Paste( Ctrl + V). 9:11 PM Microsoft Excel 2010 25
  26. II.6 Lập công thức Excel II.6.1 Khái niệm công thức ­ Công thức giúp bảng tính hữu ích hơn rất nhiều, chúng ta dùng công thức để tính toán từ các dữ liệu lưu trữ trên bảng tính, khi dữ liệu thay đổi các công thức này sẽ tự động cập nhật các thay đổi và tính ra kết quả mới. ­ Công thức trong Excel được nhận dạng là do nó bắt đầu là dấu = và sau đó là sự kết hợp của các toán tử, các trị số, các địa chỉ tham chiếu và các hàm. 9:11 PM Microsoft Excel 2010 26
  27. Toán tử ToánChức tử năng Ví dụ Mô tả Kết quả Ưu tiên + Cộng =3+3 3 cộng 3 là 6 : (hai- chấm)Trừ (1 khoảng trắng)=45-4 , Toán 45tử trừ tham 4 còn 41 chiếu 1 II.6* LậpNhân công thức=150*.05 Excel 150 nhân 0.50 thành 7.5 / Chia(dấu phẩy) =3/3 3 chia 3 là 1 II.6.1^ KháiLũy thừa niệm công thức=2^4 =16^(1/4) 2 lũy thừa 4 thành 16 Lấy căn bậc 4 của 16 thành ­ Các toán tử– trong công thức Số âm2 (ví dụ –1) 2 & Nối chuỗi =”Lê” & “Thanh” Nối chuỗi “Lê” và “Thanh” lại thành “Lê Thanh” ­ Thứ tự ưu% tiên của các toán Phầntử trăm 3 = Bằng =A1=B1 Ví dụ ô A1=3, ô B1=6 Kết quả: FALSE > Lớn hơn ^ =A1>B1 Lũy thừaVí dụ ô A1=3, ô B1=6 Kết quả: FALSE 4 = Lớn hơn* hoặc và bằng / =A1>=B1 Nhân Vívà dụ ôchia A1=3, ô B1=6 Kết quả: FALSE 5 Khác + và – =A1 , các =, giữa 2<> vùng =B1:B6 A3:D3So sánhTrả về giá trị của ô 8 9:11 PM Microsoft Excel 2010 27
  28. II.6 Lập công thức Excel II.6.2 Các loại địa chỉ Cell (khối) ­ Địa chỉ tương đối: A1 * B1 ­ Địa chỉ tuyệt đối : $A$1 * $B$1 ­ Địa chỉ hỗn hợp : $A1 * B$1 v Thao tác đặt tên khối: - Cách 1: Quét khối vùng địa chỉ muốn đặt tên gõ tên khối vào hộp NameBox( trên thanh công thức) - Cách 2: Để quản lý tên các khối đã đặt vào Formulas chọn nhóm Defined Names Name Manager xuất hiện hộp hội thoại 9:11 PM Microsoft Excel 2010 28
  29. II.6 Lập công thức Excel II.6.3 Cách lập công thức ­ Công thức bắt đầu bằng dấu = ­ Sau đó là sự kết hợp của các toán tử, các trị số, các địa chỉ tham chiếu và các hàm. ­ Nên tránh nhập trực tiếp các giá trị, con số vào công thức mà nên dùng đến tham chiếu. ­ Nếu trong công thức có sử dụng tên khối có thể nhấn F3 để chèn tên khối. ­ Khi đưa các tham số vào công thức là chuỗi thì bắt buộc phải đặt trong dấu “ ” 9:11 PM Microsoft Excel 2010 29
  30. II.6 Lập công thức Excel II.6.4 Các lỗi hay gặp trong công thức Lỗi Giải thích #DIV/0! Trong công thức có phép chia cho 0(Zero) hoặc chia cho rỗng #NAME? Đánh sai tên hàm, hay tham chiếu hoặc thiếu dấu nháy #N/A Công thực tham chiếu đến ô mà có dùng hàm NA để kiểm tra sự tồn tại của dữ liệu hoặc hàm không có kết quả #NULL? Hàm sử dụng dữ liệu giao nhau của hai vùng mà hai vùng này không có phần chung nên phần giao nhau rỗng #NUM! Vấn đề đối với giá trị, nhần số âm trong khi đúng phải là giá trị dương #REF! Tham chiếu bị lỗi, thường là do ô tham chiếu trong hàm bị xoá #VALUE! Công thức tính toán có chứa dữ liệu không đúng 9:11 PM Microsoft Excel 2010 30
  31. II.7 Định dạng dữ liệu ­ Sử dụng các lệnh trong Tab Home để định dạng. Có các Group sau: ­ Khi cần định dạng phức tạp hơn ta quét khối dữ liệu cần định dạng Click chuột phải chọn Format Cells Xuất hiện hộp thoại Format Cells Gồm các Tab sau: 9:11 PM Microsoft Excel 2010 31
  32. II.8 Hiệu chỉnh bảng tính( cell, Row, Column, sheet) Chèn Ô, cột, dòng, sheet Chọn Tab Home Nhóm Cell Lệnh Insert  Shift Cells Right: chèn 1 ô đẩy 1 ô cùng hàng sang phải.  Shift cells Down: Chèn 1 ô đẩy các ô cùng cột xuống phía dưới.  Entire Row: chèn một hàng vào vị trí hiện tại.  Entire Column: Chèn thêm một cột vào vị trí hiện tại. ­ Ngoài ra có thể click chuột vào vị trí cần chèn Insert 9:11 PM Microsoft Excel 2010 32
  33. II.8 Hiệu chỉnh bảng tính( cell, Row, Column, sheet) ­ Xoá Ô, cột, Dòng, Sheet : Chọn Tab Home Cells lệnh Delete ü Shift Cells Left: Xoá ô kéo các ô cùng hàng sang trái ü Shift Cells Up: Xoá một Ô, kéo các ô cùng cột ở phía dưới lên ü Entire Row: Xoá một hàng ở vị trí hiện tại ü Entire Column: Xoá cột ở vị trí hiện tại ­ Ngoài ra có thể Click chuột tại vị trí cần xoá Delete 9:11 PM Microsoft Excel 2010 33
  34. II.8 Hiệu chỉnh bảng tính( cell, Row, Column, sheet) v Ẩn Cột, Dòng, Sheet : chọn 1 trong các cách sau ­ Cách 1: Chọn Tab Home Cells Format Hide & UnHide ­ Cách 2: Kích Chuột trên thanh tiêu đề hàng\ Cột chọn lệnh Hide/ UnHide v Đổi tên Sheet, Màu Tab Sheet, sao chép, di chuyển Sheet Chọn Tab Home Cells Format Rename sheet, Move or Copy sheet, Tab Color 9:11 PM Microsoft Excel 2010 34
  35. III. Hàm Trong Excel III.1 Khái Niệm Hàm v Hàm là một thành phần của dữ liệu loại công thức và được xem là những công thức được xây dựng sẵn nhằm thực hiện các công việc tính toán phức tạp v Dạng Tổng Quát: (Tham số 1, Tham số 2, ) ­ Tên hàm: là tên quy ước của hàm, không phân biệt chữ hoa hay chữ thường ­ Các tham số: Đặt cách nhau bởi dấu “,” hoặc “;” tuỳ theo khai báo trong môi trường của hệ điều hành. 9:11 PM Microsoft Excel 2010 35
  36. III.1 Khái Niệm Hàm (tt) ­ Cách Nhập Hàm: chọn một trong những cách sau - Cách 1: Chọn Tab Fomulas Function Library lệnh Insert Function hoặc chọn hàm trong nhóm hàm tương tự - Cách 2 : Gõ trực tiếp hàm từ bàn phím. 9:11 PM Microsoft Excel 2010 36
  37. III.2 Các Hàm Thông Dụng III.2.1 Nhóm Hàm toán học & lượng giác ( Math & Trig) v INT (Number) Công dụng: làm tròn số Number đến số nguyên gần nhất. Ví dụ: = INT (5.9) kết quả trả về 5 = INT (­ 4.3) kết quả trả về ­ 5 9:11 PM Microsoft Excel 2010 37
  38. III.2 Các Hàm Thông Dụng v Hàm MOD ( Number, Divisor) Công dụng: trả về số dư của phép chia số nguyên ( Number) cho số bị chia (Divisor) Ví Dụ: = Mod(7,3) kết quả trả về là 1 9:11 PM Microsoft Excel 2010 38
  39. III.2 Các Hàm Thông Dụng v Hàm Round (Number, Num_Digits) Công dụng: làm tròn số ( Number) Đến vị trí xác định bởi (Num_Digits). ­ Nếu Num_ Digits là số dương thì làm tròn phần thập phân. ­ Nếu Num_ Digits là số âm thì làm tròn phần nguyên. Ví Dụ: = Round(2324.1234,0) 2324 = Round(2324.1354,2) 2324.14 = Round(2354.1354,­2) 2400 = Round(2354.1354, ­ 3) 2000 9:11 PM Microsoft Excel 2010 39
  40. III.2 Các Hàm Thông Dụng Hàm SUM( Number 1, Number 2, ) Công dụng: Hàm trả về tổng của các Number 1, Number 2, . Ví dụ: = Sum (10,20,5 ) 35 = Sum ( 100, 256, 24) 380 9:11 PM Microsoft Excel 2010 40
  41. III.2 Các Hàm Thông Dụng Hàm SUMIF( Range, Criteria, [ Sum_Range] Hàm dùng để tính tổng có điều kiện, với các đối số sau: + Range: Khối chứa giá trị để kiểm tra điều kiện tính tổng + Criteria: Điều kiện để kiểm tra từng giá trị trong khối điều kiện + Sum _ Range: Khối chứa giá trị cần tính tổng ( nếu không có tham số Sum_ Range thì Excel sẽ coi Sum_Range = Range). 9:11 PM Microsoft Excel 2010 41
  42. III.2 Các Hàm Thông Dụng v Hàm SumifS: Cú pháp SUMIFS(sum_range,criteria_range1,criteria1, criteri a_range2,criteria2 ) +Sum_range là các ô cần tính tổng, bao gồm các con số, tên vùng, mãng hay các tham chiếu đến các giá trị. Các ô trống hay chứa chuỗi (Text) sẽ bị bỏ qua. +Criteria_range1, criteria_range2, có thể khai báo từ 1 đến 127 vùng dùng để liên kết với các điều kiện cho vùng. +Criteria1, criteria2, có thể có từ 1 đến 127 điều kiện ở dạng con số, biểu thức, tham chiếu hoặc chuỗi. 9:11 PM Microsoft Excel 2010 42
  43. III.2 Các Hàm Thông Dụng vHàm SumifS (tt) Lưu Ý khi sử dụng hàm ­ Mỗi ô trong vùng sum_range chỉ được cộng khi thoả tất cả các điều kiện Ô trong sum_range thoả điều kiện (True) tương đương với số 1, và không thoả điều kiện (False) tương đương với số 0 (zero) ­ Mỗi criteria_range phải có cùng kích thước và hình dạng giống với sum_range. Ví dụ kích thước của sum_range là 3x2 (3 dòng 2 cột) thì criteria_range phải có kích thước là 3x2 (3 dòng 2 cột) ­ Ta có thể sử dụng các ký tự thay thế như ?, * trong điều kiện. Dấu ? thay cho một ký tự nào đó và dấu * thay cho một chuỗi nào đó. Khi điều kiện sau lại là dấu ? hay * thì bạn đặt thêm dấu ~ phía trước nó. 9:11 PM Microsoft Excel 2010 43
  44. III.2 Các Hàm Thông Dụng vHàm SUMPRODUCT( ARRAY1, [ARRAY 2], [ARRAY 3], ) ­ Tính tổng của tích các phần tử trong các mảng tương ứng. Array 1, Array 2 có thể từ 2 cho đến 255 mảng và các mảng này phải cùng kích thước với nhau. A B C v SumProduct(A1:A3,B1:B3,C1: 1 3 5 9 2 1 6 4 C3) kết Quả 271 3 2 7 8 9:11 PM Microsoft Excel 2010 44
  45. III.2 Các Hàm Thông Dụng III.2.2 Hàm xử lý chuỗi v Hàm LEFT : Lấy n các ký tự bên trái của 1 chuỗi ­ Cú pháp: = LEFT(Text, [num_chars]) VD: Tại ô B2 có lập công thức sau: = Left(A2,2) A B 1 Mã Hàng Loại Hàng 2 AB23DC AB 3 BP45FC BP 4 FAA23G FA v Lưu Ý: Các khoảng trắng cũng được coi là 1 ký tự. 9:11 PM Microsoft Excel 2010 45
  46. III.2 Các Hàm Thông Dụng v Hàm Right: Lấy n các ký tự bên Phải của 1 chuỗi ­ Cú pháp: = Right(Text, [num_chars]) VD: Tại ô B2 có lập công thức sau: = Right(A2,4) A B 1 Mã Hàng Loại Hàng 2 AB23DC 23DC 3 BP45FC 45FC 4 FAA23G A23G v Lưu Ý: Các khoảng trắng cũng được coi là 1 ký tự. 9:11 PM Microsoft Excel 2010 46
  47. III.2 Các Hàm Thông Dụng v Hàm MID Lấy n các ký tự của 1 chuỗi, kể từ vị trí xác định ­ Cú pháp: = MID(Text, start_num, num_chars) VD: Tại ô B2 có lập công thức sau: = Mid(A2,3,2) A B 1 Mã Hàng Loại Hàng 2 AB23DC 23 3 BP45FC 45 4 FAA23G A2 9:11 PM Microsoft Excel 2010 47
  48. III.2 Các Hàm Thông Dụng v Hàm PROPER: Đổi ký tự đầu của 1 từ thành chữ in ­ Cú Pháp = PROPER(Chuỗi ký tự) VD: Tại ô B2 có lập công thức sau: = PROPER(A2) A B 1 Họ và tên Kết Quả 2 trần thanh hoàng Trần Thanh Hoàng 3 nguyễn anh sơn Nguyễn Anh Sơn 4 vũ thuý hường Vũ Thuý Hường 9:11 PM Microsoft Excel 2010 48
  49. III.2 Các Hàm Thông Dụng v Hàm TRIM: Trả về chuỗi ký tự sau khi đã cắt bỏ các ký tự dư thừa. Các ký tự dư thừa là các đầu chuỗi, cuối chuỗi hoặc là nhiều hơn 1 ký tự giữa hai từ ­ Cú Pháp = TRIM(Chuỗi ký tự) VD: Tại ô B2 có lập công thức sau: = Trim(A2) A B 1 Họ và tên Kết Quả 2 Trần Thanh Hoàng Trần Thanh Hoàng 9:11 PM Microsoft Excel 2010 49
  50. III.2 Các Hàm Thông Dụng v Hàm LEN: Trả về giá trị chiều dài của chuỗi ký tự ­ Cú Pháp = Len(Chuỗi ký tự) VD: Ô B2 lập công thức sau: = Len(A2) A B 1 Họ và tên Kết Quả 2 Trần Thanh Hoàng 16 9:11 PM Microsoft Excel 2010 50
  51. III.2 Các Hàm Thông Dụng v Hàm VALUE: Đổi giá trị chuỗi ra giá trị số ­ Cú Pháp = VALUE(Chuỗi ký tự) VD: Ô B2 lập công thức sau: = value(right(A2,4)) A B 1 Họ và tên Kết Quả 2 huia1023 0123 9:11 PM Microsoft Excel 2010 51
  52. III.2 Các Hàm Thông Dụng III.2.3 Các Hàm Xử Lý Ngày Giờ (Date &Time) ÆHàm TODAY() Trả về giá trị ngày tháng năm của hệ thống ÆHàm NOW() Trả về giá trị ngày tháng năm và giờ phút của hệ thống ÆHàm DAY( Biểu thức ngày – tháng – năm) Trả về giá trị Phần Ngày của Biểu thức ÆHàm MONTH(Biểu Thức ngày ­ Tháng – Năm) Trả về phần giá trị Tháng của biểu thức Ví Dụ: Month(29/09/2011) 09 9:11 PM Microsoft Excel 2010 52
  53. III.2 Các Hàm Thông Dụng ÆHàm YEAR(Biểu thức Ngày – Tháng – Năm) ­ Trả về giá trị Phần Năm trong biểu thức ÆHàm weekday(biểu thức ngày – tháng – năm, Kiểu trả về) ­ Trả về giá trị là số thứ trong tuần của biểu thức ngày tháng năm ­ Các giá trị kiểu trả có ý nghĩa như sau: ØKiểu 1: chủ nhật là 1 và tuần tự cho đến thứ 7 ØKiểu 2: Thứ hai là 1 và tuần tự cho đến thứ 7 9:11 PM Microsoft Excel 2010 53
  54. III.2 Các Hàm Thông Dụng III.2.4 Nhóm hàm luận lý v Hàm AND: Trả về True nếu tất cả đối số là True, Trả về False nếu ít nhất một đối số là False ­ Cú pháp: AND(logical1,logical2,. . .) Ví dụ: =IF(AND(A2=“A”,B2>= 20),"Đậu","Rớt") A B C 1 Khối Thi Điểm Thi K.Quả 2 A 24 Đậu 3 A 19 Rớt 4 B 20 Rớt 9:11 PM Microsoft Excel 2010 54
  55. III.2 Các Hàm Thông Dụng v Hàm OR: Trả về True nếu ít nhất một đối số là True, Trả về False nếu tất cả các đối số là False ­ Cú pháp: OR(logical1,logical2,. . .) Ví dụ: =IF(OR(A2=“A”,B2>=20,"Đậu","Rớt") A B C 1 Khối Thi Điểm Thi K.Quả 2 A 24 Đậu 3 B 20 ĐẬU 4 D 19 RỚT 9:11 PM Microsoft Excel 2010 55
  56. III.2 Các Hàm Thông Dụng v Hàm NOT: Trả về giá trị True nếu Logical có giá trị FALSE và ngược lại ­ Cú pháp: = NOT(logical) VD: Tại Ô C2 = Not(A2>B2) A B C 1 Toán văn K.Quả 2 10 5 FALSE 3 7 8 TRUE 4 9 4 FALSE 9:11 PM Microsoft Excel 2010 56
  57. III.2 Các Hàm Thông Dụng v Hàm IF: Thực hiện một công việc nào đó dựa vào điều kiện so sánh ­ Cú pháp: = IF(logical_test, [value if true], [value if false]) VD: Tại Ô C2 = If(B2>20, “Đậu”, “Rớt” A B C 1 Khối Thi Điểm Thi K.Quả 2 A 24 Đậu 3 B 20 ĐẬU 4 D 19 RỚT 9:11 PM Microsoft Excel 2010 57
  58. III.2 Các Hàm Thông Dụng III.2.5 Nhóm hàm dò tìm & Tham chiếu vHàm VLOOKUP: Hàm này tìm kiếm 1 giá trị trong cột bên trái của vùng dữ liệu và trả về giá trị trên cột thứ n nếu tìm thấy, hoặc trả về #N/A nếu không tìm thấy ­ Cú pháp: =VLOOKUP(lookup_value, table_aray, col_index_num, [range_lookup]) +lookup value: Là giá trị tìm kiếm trong cột bên trái của bảng dò +table aray: Là vùng chứa dữ liệu được tìm kiếm cho trước +col index num: Là số thứ tự các cột trong vùng dữ liệu nơi mà Hàm Vlookup sẽ lấy giá trị trả về, n=1 chỉ cột đầu tiên, n=2 là cột thứ 2,. . . +[x]: Là kiểu dò tìm , với x = 0 thì tìm kiếm chính xác, x = 1 thì tìm kiếm gần đúng. 9:11 PM Microsoft Excel 2010 58
  59. III.2 Các Hàm Thông Dụng Ví dụ : Tên hàng dựa vào mã hàng và bảng phụ Tại ô B2 đặt Công thức sau: = Vlookup(A2,$E$1:$F$4,2,0) E F A B Bảng phụ 1 Mã Hàng Tên hàng 1 Mã H Tên 2 KE Kẹo 2 DU Đường 3 DU Đường 3 KE Kẹo 4 SU Sữa 4 SU Sữa 9:11 PM Microsoft Excel 2010 59
  60. III.2 Các Hàm Thông Dụng vHàm HLOOKUP: Hàm này tìm kiếm 1 giá trị trong hàng đầu tiên của vùng dữ liệu và trả về giá trị trên hàng thứ n nếu tìm thấy, hoặc trả về #N/A nếu không tìm thấy ­ Cú pháp: HLOOKUP( Trị dò, Bảng dò, N, [X]) +Trị dò: Là giá trị tìm kiếm trong cột bên trái của vùng dữ liệu. v+Bảng dò: Là vùng chứa dữ liệu được tìm kiếm cho trước (vùng dữ liệu chứa địa chỉ tuyệt đối) v+N: Là số thứ tự các hàng trong vùng dữ liệu nơi mà Hàm Vlookup sẽ lấy giá trị trả về, n=1 chỉ hàng đầu tiên, n=2 là hàng thứ 2,. . . v+[X]: Là kiểu dò tìm, với x = 0 thì tìm kiếm chính xác, x = 1 thì tìm kiếm gần đúng. 9:11 PM Microsoft Excel 2010 60
  61. III.2 Các Hàm Thông Dụng Ví dụ: xếp loại dựa vào điểm số và bảng phụ = HLOOKUP(A2,$E$2:$J$9,2,1) A B 1 Điểm Xếp loại Kiểu dò tìm không chính xác 2 3 Kém 3 6 T Bình 4 9 Xuất sắc E F G H I j 1 Bảng phụ 2 ĐTB 2 5 7 8 9 3 Xếp loại Kém T Bình Khá Giỏi Xuất Sắc 9:11 PM Microsoft Excel 2010 61
  62. III.2 Các Hàm Thông Dụng v Hàm MATCH( Trị dò, bảng dò, [Cách dò]) ­Cú Pháp: = Match(Lookup_Array, Lookup_Array, Match_type) ­Hàm trả về số thứ tự của trị dò trong khối bảng dò, ­Thông thường bảng dò chỉ là 1 hàng hay là 1 cột ­Số thứ tự trị dò được tính từ trên xuống nếu bảng dò là 1 cột hoặc từ dưới lên nếu bảng dò là 1 hàng - Match_type = 1: tìm tương đối, danh sách của bảng dò phải sắp xếp theo thứ tự tăng dần. Nếu tìm không thấy sẽ trả vị trí của giá trị lớn nhất nhưng nhỏ hơn trị dò 9:11 PM Microsoft Excel 2010 62
  63. III.2 Các Hàm Thông Dụng - Match_type = 0: Tìm chính xác, nếu tìm không thấy sẽ trả về lỗi #N/A - Match_type = -1: Tìm tương đối, danh sách các giá trị dò tìm phải sắp xếp theo giá trị giảm dần. Nếu tìm không thấy sẽ trả về giá trị nhỏ nhất nhưng lớn hơn trị dò A B 1 CH #N/A Ví dụ: Match(“TH”,A1:A4,0) 3 2 DT #N/A 3 TH 3 4 DL #N/A 9:11 PM Microsoft Excel 2010 63
  64. III.2 Các Hàm Thông Dụng vHàm INDEX: Trả về giá trị trong của ô giao nhau giữa thứ tự hàng và thứ tự cột trong bảng dò ­Cú pháp: Index(bảng dò, hàng dò, cột dò) Ví dụ : Index(A1:H4,2,3) kết quả 15 Index(A1:H4,4,6) kết quả 37 A B C D E F G H 1 10 12 14 16 18 20 22 24 2 11 13 15 17 19 21 23 25 3 26 28 30 32 34 36 38 40 4 27 29 31 33 35 37 39 41 9:11 PM Microsoft Excel 2010 64
  65. III.2 Các Hàm Thông Dụng v Hàm OFFSET ­ Cú pháp OFFSET(reference, Rows, cols, [height], [width]) + Reference: Vùng địa chỉ gốc, khối gốc + Rows: Số dòng dịch lên hay dịch xuống tính từ Reference + Cols: Số cột dịch qua tính từ Reference + [Height]: Chỉ định số dòng muốn tham chiếu trả về, nếu bỏ qua tham số này thì số dòng của khối trả về = khối gốc ban đầu + [width]: Chỉ định số cột muốn tham chiếu trả về, nếu bỏ qua tham số này thì số cột của khối mới = khối gốc ban đầu. 9:11 PM Microsoft Excel 2010 65
  66. III.2 Các Hàm Thông Dụng Ví dụ : A B C D E F G H = Offset(B2,1,2) 1 10 12 14 16 18 20 22 24 Kết quả : 32 2 11 13 15 17 19 21 23 25 = Sum(offset(B2,1,2,2,3) 3 26 28 30 32 34 36 38 40 4 27 29 31 33 35 37 39 41 Kết quả: 207 = Count(Offset(B2,1,2,2,3) Kết quả: 6 v Hàm OFFSET có thể làm đối số bên trong các hàm khác như một vùng địa chỉ tham chiếu 9:11 PM Microsoft Excel 2010 66
  67. III.2 Các Hàm Thông Dụng vHàm ROW([Reference]) : hàm trả về chỉ số dòng đầu tiên của ô đầu tiên trong vùng tham chiếu ­ Nếu không có đối số Reference, xem như trả về chỉ số của ô hiện tại, ô đang lập công thức Ví dụ 1: = ROW(D3:F4) 3 A B C D E F Ví dụ 2 :Tại A4 đặt công thức sau: 1 10 12 14 16 18 20 = ROW() 4 2 11 13 15 17 19 21 3 26 28 30 32 34 36 Ví dụ 2: không có đối số reference 4 4 29 31 33 35 37 Do đó hàm row trả về trị số ở hàng Ở ngay ô đặt công thức – Coi và tìm hiểu hàm ROWS 9:11 PM Microsoft Excel 2010 67
  68. III.2 Các Hàm Thông Dụng vHàm Column([Reference]) : hàm trả về chỉ số cột đầu tiên của ô đầu tiên trong vùng tham chiếu ­ Nếu không có đối số Reference, xem như trả về chỉ số của ô hiện tại, ô đang lập công thức Ví dụ 1: = Column(D3:F4) 4 Ví dụ 2: Tại A4 đặt A B C D E F công thức sau: 1 10 12 14 16 18 20 = Column () 1 2 11 13 15 17 19 21 Ví dụ 2: không có 3 26 28 30 32 34 36 đối số reference 4 4 29 31 33 35 37 Do đó hàm Column trả về trị số ở hàng Ở ngay ô đặt công thức – Coi và tìm hiểu hàm Columns 9:11 PM Microsoft Excel 2010 68
  69. III.2 Các Hàm Thông Dụng III.2.6 Nhóm hàm thống kê (Statistical) v Hàm Max( Number1, Number2, ) ­ Hàm trả về số lớn nhất trong các đối số Number1, 2 n Ví dụ: = Max(10,35,56,78,100) 100 = Max(A1:F4) 37 A B C D E F = Max(B1:B4) 29 1 10 12 14 16 18 20 = Max(A2:F2) 21 2 11 13 15 17 19 21 3 26 28 30 32 34 36 4 4 29 31 33 35 37 9:11 PM Microsoft Excel 2010 69
  70. III.2 Các Hàm Thông Dụng ­ Hàm MaxA( value1,[value2], [value3], ) ­ Hàm trả về số lớn nhất trong các đối số value1, 2 N ­ Tính các ô trong đối số có giá trị True là 1 ­ Tính các ô trong đối số có giá trị False là 0 Tính các ô trong đối số có giá trị chuỗi là 0 = MaxA(10,35,56,78,100) 100 = MaxA(A1:D4) 32 A B C D 1 cá -10 True 0 = MaxA(A1:A4) ? 2 11 13 15 17 = MaxA(A4:D4) ? 3 -1 28 30 32 4 False -1 Tên -33 9:11 PM Microsoft Excel 2010 70
  71. III.2 Các Hàm Thông Dụng v Hàm Min( Number1, Number2, ) ­Hàm trả về số nhỏ nhất trong các đối số Number1, 2 N Ví dụ: = Min(10,35,56,78,100) 10 = Min(A1:F4) 4 A B C D E F = Min(B1:B4) 12 1 10 12 14 16 18 20 = Min(A1:F3) 6 2 11 13 15 17 19 21 3 26 28 30 6 34 36 4 4 29 31 33 35 37 9:11 PM Microsoft Excel 2010 71
  72. III.2 Các Hàm Thông Dụng v Hàm MinA( value1,[value2], [value3], ) ­ Hàm trả về số nhỏ nhất trong các đối số value1, 2 N ­ Tính các ô trong đối số có giá trị True là 1 ­ Tính các ô trong đối số có giá trị False là 0 ­ Tính các ô trong đối số có giá trị chuỗi là 0 = MinA(10,35,56,78,100) 10 = MinA(A1:D4) ­10 A B C D = MinA(A1:A4) ­1 1 cá -10 True 0 = MinA(A4:D4) ? 2 -1 13 15 17 = MinA(A2:D2) ­> ? 3 0 28 30 32 4 False -1 Tên True 9:11 PM Microsoft Excel 2010 72
  73. III.2 Các Hàm Thông Dụng v Hàm AVERAGE (number1, number2, ) ­ Hàm trả về giá trị trung bình các đối số number1, 2 n ­ Từng đối số có thể là 1 nhóm các giá trị số hoặc 1 mảng Ví dụ:Tại ô E2 lập công thức Average(A2:C2) A B C D E 1 Toán Hoá Văn T.cộng T.bình 2 5 10 7 22 7.333 3 9 5 6 19 6.666 4 6 5 8 19 6.333 9:11 PM Microsoft Excel 2010 73
  74. III.2 Các Hàm Thông Dụng v Hàm AVERAGEA(VALUE1, VALUE2, ) ­ Hàm trả về giá trị trung bình của đối số ­ Đối số có thể là 1 nhóm các giá trị số hoặc 1 mảng ­ Tính các ô trong đối số có giá trị True là 1 ­ Tính các ô trong đối số có giá trị False là 0 ­ Tính các ô trong đối số có giá trị chuỗi là 0 Ví dụ:Tại ô E1 lập công thức = AverageA(A1:D1) A B C D E 1 true kết quảfalse = 5.2520 Test 5.25 2 true True 0 true 0.75 9:11 PM Microsoft Excel 2010 74
  75. III.2 Các Hàm Thông Dụng v Hàm AVERAGEIF(Range,Criteria, [Average_Range]) ­ Hàm tính trung bình có điều kiện ­ Range: khối giá trị để kiểm tra điều kiện ­ Criteria: điều kiện để kiểm tra từng giá trị trong khối điều kiện. ­ Cách đặt điều kiện tương tự như Sumif ­ Average_range: khối giá trị để tính trung bình, nếu bỏ qua tham số này Excel tự hiểu là tính trung bình trên khối range(tham số đầu tiên) ­ Hàm AverageIF:không phân biệt chữ hoa hay thường Hàm trả về lỗi #DIV/0! Nếu không có giá trị thoả mãn đ/kiện hoặc tất cả các giá trị đều không phải kiểu số. 9:11 PM Microsoft Excel 2010 75
  76. III.2 Các Hàm Thông Dụng ­ Hàm AverageIf trả về lỗi #VALUE! Nếu tham số thứ 2( Criteria) là chuỗi có chiều dài > 255 ký tự Ví dụ: Tính điểm trung bình khối A tại ô E5 lập công thức sau: AVERAGEIF(A2:A5,E3,B2:B5) E F A B 1 Vùng ĐK 1 Khối Điểm 2 Khối 2 A 19 3 A 3 B 23 4 Điểm T Binh 4 A 11 5 15.66 5 A 17 9:11 PM Microsoft Excel 2010 76
  77. III.2 Các Hàm Thông Dụng v Hàm COUNT(Value1, Value2, ) - Hàm chỉ đếm các đối số Value1, Value2 là giá trị số - Các đối số Value trong hàm có thể là bất kỳ giá trị nào hoặc là tham chiếu đến 1 dãy ô (mảng). Có từ 1 255 value Ví Dụ: A B C Tính tổng số thí sinh dự thi 1 Khối Điểm Tại Ô C5 tạo công thức sau: 2 A 19 = Count(B2:B4) kết quả là 3 3 B 23 Tại Ô C4 tạo công thức sau: 4 A 11 0 = Count(A2:A4) Kết quả là 0 * Hàm count chỉ đếm các giá trị là số 5 Tổng số TS 3 9:11 PM Microsoft Excel 2010 77
  78. III.2 Các Hàm Thông Dụng v Hàm COUNTA(Value1, Value2, ) - Hàm đếm tất cả các đối số Value1, Value2 Không rỗng Các đối số Value trong hàm có thể là bất kỳ giá trị nào hoặc là tham chiếu đến 1 dãy ô (mảng). Có từ 1 255 value A B C Ví Dụ:Tính tổng các ô có dữ liệu tại 1 Khối Điểm bảng bên 2 A 19 False Tại Ô C5 tạo công thức sau: 3 B 23 True = Counta(A1:C4) kết quả là 12 * Tất các đối số đều được hàm Counta 4 A 11 0 đếm kể cả các giá trị logical 5 Ts các ô 12 9:11 PM Microsoft Excel 2010 78
  79. III.2 Các Hàm Thông Dụng v Hàm COUNTBLANK(Value1, Value2, ) ­ Hàm đếm tất cả các đối số Value1, Value2 là các ô rỗng ­ Các đối số Value trong hàm có thể là bất kỳ giá trị nào hoặc là tham chiếu đến 1 dãy ô (mảng). Có từ 1 255 value A B C Ví Dụ:Tính tổng các ô có dự liệu tại 1 Khối Điểm bảng bên 2 A False Tại Ô C5 tạo công thức sau: 3 B True = Counta(A1:C4) kết quả là 3 4 A 0 5 Ts ô rỗng 3 9:11 PM Microsoft Excel 2010 79
  80. III.2 Các Hàm Thông Dụng v Hàm CountIF(Range, Criteria). Đếm các ô thoả điều kiện Range: vùng mà điều kiện được so sánh Criteria: là chuỗi mô tả điều kiện Thí dụ: Tính ra số thí sinh dự thi là khối A Tại Ô E4 lập công thức sau: Countif(A2:A5, E2) 3 A B E F 1 Khối Điểm 1 Vùng ĐK 2 A 19 2 A 3 B 23 3 TS thí sinh khối A 4 A 11 4 3 5 9:11A PM 17Microsoft Excel 2010 80
  81. III.2 Các Hàm Thông Dụng ­ Cp: CountIFS(Range_Criteria1, Criteria1, Range_Criteria2, Criteria2, ) ­ Hàm CountIFS : Đếm tổng các ô có từ 2 điều kiện trở lên Range_Criteria1,2 : vùng mà các điều kiện được so sánh ­ Criteria1,2 : là các điều kiện tương ứng với các Range_ Criteria1,2 ­ Các vùng Range_ Criteria1, 2 n phải có cùng kích thước(như cùng số hàng và cột) Ví dụ: Trong bảng tính sau tính ra có bao nhiêu mặt hàng là nhớt của hãng sản xuất British Petro và có giá > 85000 9:11 PM Microsoft Excel 2010 81
  82. III.2 Các Hàm Thông Dụng vHàm RANK( Number, Ref,[Order]) hàm xếp hạng ­ Hàm trả về thứ hạng của Number trong vùng Ref, với Order là cách xếp hạng + Number: Là số để so sánh xếp hạng + REF: Bảng để so sánh xem Number thuộc hạng nào + Kết quả: Xếp hạng phụ thuộc vào đối số thứ 3 [Order] vNếu Order bằng 0 hoặc bỏ qua thì kết quả xếp hạng có thứ tự Lớn là hạng nhất ( xếp hạng điểm số học tập) vNếu Order = 1 hoặc <> 0 thì kết quả xếp hạng có thứ tự Nhỏ nhất sẽ là hạng nhất( xếp loại theo thể thao) 9:11 PM Microsoft Excel 2010 82
  83. III.2 Các Hàm Thông Dụng III.2.7 Nhóm hàm thông tin (Information) vHàm ISNA(value): Hàm kiểm tra xem giá trị Value có phải là #N/A hay không, đúng trả về TRUE, sai trả về FALSE vHàm ISERROR(value): Hàm kiểm tra xem value có phải là 1 trong các lỗi (#N/A, #Value!, #Ref!, #DIV/0!, #NUM!, #Name?, #Null) hay không. Đúng trả về TRUE, sai trả FALSE vHàm ISBLANK(value): Hàm kiểm tra xem ô Value có phải là ô trắng hay không. Đúng trả về TRUE, sai trả về FALSE vISNUMBER(Value): Hàm kiểm tra các giá trị có phải là số hay không. Đúng trả về TRUE, sai trả về FALSE vISTEXT(Value): Hàm kiểm tra các giá trị có phải là chuỗi hay không. Đúng trả về TRUE, sai trả về FALSE 9:11 PM Microsoft Excel 2010 83
  84. III.3 Công thức mảng: ­ Công thức mảng hỗ trợ thống kê và tính toán dựa trên nhiều điều kiện khác nhau và được thực hiện trên mảng dữ liệu ­ Cú pháp: = {Hàm Mục tiêu(điều kiện 1)*(điều kiện 2)* *(cột tính toán)} + Hàm mục tiêu: tuỳ theo yêu cầu (Sum, Count, Max, Min, Average, ) +Điều kiện 1, 2, là các biểu thức điều kiện, thực hiện các biểu thức so sánh kết quả trả về là True(1), False(0) + Cột tính toán : cột dữ liệu chính cho hàm mục tiêu thực hiện tính toán • Công thức mảng kết thúc bằng tổ hợp Phín Ctrl +Shift+Enter • Các mảng tính toán phải bằng nhau(cùng số cột số dòng bắt đầu và kết thúc) • Các mảng không được có cột tiêu đề 9:11 PM Microsoft Excel 2010 84
  85. III.3 Công thức mảng: Ví dụ: Tính tổng số điện thoại mà NV Thọ đã bán. Lập công thức sau: = {SUM(IF(A2:A7=“Điện thoại”,1,0)*if(B2:B7=“Thọ”,1,0)*C2:C7)} kết quả là: 19 A B C A B C 1 Sản phẩm Người bán Số lượng SP N Bán Số lượng 2 Máy lạnh Thọ 2 0 * 1 0 3 Máy lạnh Sơn 5 0 * 0 0 4 Điện thoại Minh 7 1 * 0 0 5 Điện thoại Thọ 9 1 * 1 9 6 Điện thoại Thọ 10 1 * 1 10 7 Ti vi Thọ 7 0 * 1 0 9:11 PM Microsoft Excel 2010 85
  86. IV. Chèn các đối tượng vào bảng tính IV.1 Chèn các đối tượng đồ hoạ ­ Vào Tab Insert Group Illustration chọn Picture,Clip Art, Shapes. Cách chèn tương tự như trong Microsoft Word IV.2 Chèn các đối tượng dạng chuỗi: ­ Vào Tab Insert Group Text chọn Text Box, Header & Footer, WordArt, Object. Cách chèn tương tự như trong Microsoft Word IV.3 Chèn ký tự đặc biệt và công thức toán học: ­ Chọn Tab Insert Group Text chọn Symbol, Equation Cách chèn tương tự như trong Microsoft Word 9:11 PM Microsoft Excel 2010 86
  87. IV.4 Chèn các đối tượng là biểu đồ ­ Biểu đồ là sự biểu diễn các con số, dữ liệu bằng hình ảnh để người đọc nắm bắt thông tin một cách trực quan hơn. ­ Đồ thị có thể được dùng để biểu diễn sự biến động của chuỗi số liệu trong bảng tính cho phép khái quát và dự đoán trong tương lai. Các bước thực hiện: + Quét chọn vùng dữ liệu (bao gồm cả dòng & cột tiêu đề): A1:B4 A B + Chọn Tab Insert Chọn Charts chọn 1 Bảng thống kê loại đồ thị tương ứng ngay lập tức ta 2 Tên Hàng Lợi Nhuận có đồ thị. Và trên thanh Ribbon xuất hiện 3 Đường 1,178,208 3 Tab ngữ cảnh là : Design, Layout, Format để giúp chúng ta hiệu chỉnh đồ 4 Kẹo 1,436,164 thị. 5 Sữa 1,200,000 9:11 PM Microsoft Excel 2010 87
  88. IV.4 Chèn các đối tượng là biểu đồ ­ Để hiệu chỉnh đồ thị ta nhấp chuột chọn đồ thị sai đó chọn từng thành phần trên đó để hiệu chỉnh( có thể dùng nhóm phím 4 mũi tên trên bàn phín để chọn) 1. Chart Title 7. Horizontal Axis 2. Chart Area 8. Data Table 3. Plot Area 9. Horizontal 4. Data Label 10. Verical Gridlines 5. Legend 11.Verical Axis 6.Horizontal Gridlines 12.Verical Axis Title 9:11 PM Microsoft Excel 2010 88
  89. IV.4 Chèn các đối tượng là biểu đồ 9:11 PM Microsoft Excel 2010 89
  90. IV.4 Chèn các đối tượng là biểu đồ Các lệnh tuỳ biến để hiệu chỉnh bản đồ gồm 3 Tab v Design: vNhóm Type qChange Chart Type: thay đổi kiểu biểu đồ. vNhóm Data qSelect Data: lựa chọn, thay đổi dữ liệu của biểu đồ. qSwitch Row/Column: chuyển đổi giữa cột và dòng vNhóm Chart Layouts: lựa chọn các dạng bố cụ mẫu cho biểu đồ. vNhóm Chart Styles: lựa chọn các dạng định dạng mẫu cho biểu đồ. vNhóm Location: di chuyển biểu đồ sang Chart Sheet mới. 9:11 PM Microsoft Excel 2010 90
  91. IV.4 Chèn các đối tượng là biểu đồ v Layout: - Group Current Selection: Lựa chọn các thành phần trên biểu đồ định dạng riêng từng phần. - Group Insert • Picture: chèn hình vào biểu đồ • Shapes: chèn các khối đơn giản vào biểu đồ • Text Box: chèn textbox vào biểu đồ 9:11 PM Microsoft Excel 2010 91
  92. IV.4 Chèn các đối tượng là biểu đồ v Group Lables q Chart Title: thêm, xóa, di chuyển tiêu đề của biểu đồ q Axis Title: thêm, xóa, di chuyển tiêu đề của trục hoành (hoặc trục tung) q Legend: thêm, xóa, di chuyển chú thích cho biểu đồ q Data Lables: thêm, xóa, di chuyển nhãn dữ liệu q Data Table: thêm, xóa bảng dữ liệu của biểu đồ q Nhóm Axes q Axes: thay đổi định dạng và bố cục của trục tung, trục hoành. q Gridlines: ẩn/ hiện đường lưới cho biểu đồ. 9:11 PM Microsoft Excel 2010 92
  93. IV.4 Chèn các đối tượng là biểu đồ v Tab Format: q Shape Styles q Shape Fill: chọn màu nền q Shape Outline: chọn màu đường viền q Shape Effects: chọn hiệu ứng q WordArt Styles q Text Fill: chọn màu chữ q Text Outline: chọn màu viền quanh chữ q Text Effects: chọn hiệu ứng cho chữ 9:11 PM Microsoft Excel 2010 93
  94. IV.4 Chèn các đối tượng là biểu đồ v Một số thao tác đối với biểu đồ: - Thay đổi dữ liệu, chú thích, giá trị trục hoành Nhấp chuột phải vào biểu đồ chọn Select Data xuất hiện hộp thoại Select Data Source. Xóa bớt một chuỗi số liệu khỏi biểu đồ Chọn chuỗi số liệu trên đồ thị nhấn Delete để xóa. 9:11 PM Microsoft Excel 2010 94
  95. IV.4 Chèn các đối tượng là biểu đồ v Thêm chuỗi dữ liệu mới vào biểu đồ - B1: Chọn đồ thị cần thêm chuỗi mới vào. - B2: Chart Tools Design Data Select Data. Hộp thoại Select Data Source xuất hiện. B3: Nhấp nút Add, hộp thoại Edit Series xuất hiện B4: Đặt tên cho chuỗi mới tại Series Name (bằng tham chiếu hoặc nhập trực tiếp tên vào từ bàn phím) và chọn vùng chứa dữ liệu tại Series Values - B5: Ngoài ra ta có thể thêm nhanh chuỗi mới vào đồ thị bằng cách chép (Ctrl+C) dữ liệu của nó vào bộ nhớ, sau đó chọn đồ thị và dán (Ctrl+V) vào đồ thị. 9:11 PM Microsoft Excel 2010 95
  96. IV.4 Chèn các đối tượng là biểu đồ v Thêm chuỗi dữ liệu mới vào biểu đồ - B1: Chọn đồ thị cần thêm chuỗi mới vào. - B2: Chart Tools Design Data Select Data. Hộp thoại Select Data Source xuất hiện. B3: Nhấp nút Add, hộp thoại Edit Series xuất hiện B4: Đặt tên cho chuỗi mới tại Series Name (bằng tham chiếu hoặc nhập trực tiếp tên vào từ bàn phím) và chọn vùng chứa dữ liệu tại Series Values - B5: Ngoài ra ta có thể thêm nhanh chuỗi mới vào đồ thị bằng cách chép (Ctrl+C) dữ liệu của nó vào bộ nhớ, sau đó chọn đồ thị và dán (Ctrl+V) vào đồ thị. 9:11 PM Microsoft Excel 2010 96
  97. IV.4 Chèn các đối tượng là biểu đồ v Thêm đường xu hướng vào biểu đồ - Khi vẽ các đồ thị với dữ liệu theo thời gian chúng ta thường vẽ thêm đường xu hướng để biết được xu hướng trong tương lai của tập dữ liệu. Để thêm đường xu hướng bạn vào: - Chart Tools Layout Analysis Trendline: chọn kiểu đường xu hướng từ danh sách hay vào More Trendline Options 9:11 PM Microsoft Excel 2010 97
  98. V. Xử lý số liệu V.1 Sắp xếp dự liệu (Soft) ­ B1: Chọn phạm vi cần sắp xếp. VD: quét khối từ A1:I12 9:11 PM Microsoft Excel 2010 98
  99. V.1 Sắp xếp dự liệu (Soft) - B2: Vào tab Home nhóm Editing lệnh Sort & Filter Hoặc tab Data nhóm Sort & Filter lệnh Sort - Sort Smallest to Largest: sắp xếp từ bé đến lớn theo cột đầu tiên. - Sort Largest to Smallest: sắp xếp từ lớn đến bé theo cột đầu tiên. Custom Sort: tùy chọn sắp xếp theo các cột. 9:11 PM Microsoft Excel 2010 99
  100. V.1 Sắp xếp dự liệu (Soft) v Khi chọn Custom Sort (hoặc lệnh Sort) sẽ xuất hiện màn hình sau: 9:11 PM Microsoft Excel 2010 100
  101. V.1 Sắp xếp dự liệu (Soft) - Sort by: Chọn cột (Field) làm tiêu chí chính để sắp xếp. - Then by: Chọn cột làm tiêu chí sắp xếp phụ nếu có (Excel sẽ sắp xếp dựa vào tiêu chí phụ này khi tiêu chí chính trong mục Sort by bị trùng). - My data has headers: tích vào nếu vùng dữ liệu quét chọn để sort có dòng tiêu đề. 9:11 PM Microsoft Excel 2010 101
  102. V.2 Trích lọc dữ liệu: ­ Chức năng này giúp trích ra những bản ghi vùng dữ liệu thoả mãn những yêu cầu đặt ra. Có 2 cách lọc: Trích lọc tự động và Trích lọc nâng cao. 1/ Trích lọc tự động( Auto Filter) ­ B1: Chọn vùng dữ liệu muốn lọc. ­ B2: Vào tab Home nhóm Editing lệnh Sort & Filter Filter Hoặc tab Data nhóm Sort & Filter lệnh Filter ­ Lúc đó trên tiêu đề mỗi cột sẽ xuất hiện biểu tượng lọc là các Menu DropDown. 9:11 PM Microsoft Excel 2010 102
  103. V.2 Trích lọc dữ liệu: 1/ Trích lọc tự động( Auto Filter) 9:11 PM Microsoft Excel 2010 103
  104. V.2 Trích lọc dữ liệu: ­ B3: Muốn lọc theo điều kiện ở cột nào thì nhấp chuột trái vào biểu tượng mũi tên trên cột đó tích chọn các giá trị muốn lấy (nhấp chuột lần nữa để bỏ chọn). 9:11 PM Microsoft Excel 2010 104
  105. V.2 Trích lọc dữ liệu: ­ Ngoài ra ta có thể chọn lệnh Number Filters để thiết lập điều kiện lọc tùy ý màn hình sau sẽ xuất hiện 9:11 PM Microsoft Excel 2010 105
  106. V.2 Trích lọc dữ liệu: vChọn phép toán so sánh cần dùng trong hộp danh sách bên trái: vNhập vào hoặc chọn giá trị làm điều kiện trong hộp danh sách bên phải. Lưu ý có thể sử dụng 2 ký tự đại diện để lập điều kiện: • Dùng dấu ? để thay thế cho 1 ký tự bất kỳ. • Dùng dấu * để thay thế cho dãy ký tự bất kỳ có chiều dài bất kỳ. vCó thể kết hợp thêm một điều kiện lọc nữa bằng cách chọn tương tự trong hai hộp danh sách phía dưới nhưng phải thông qua hai phép toán And hoặc Or. Để huỷ lệnh lọc ta chọn lại lệnh Data ­ Filter ­ AutoFilter một lần nữa. 9:11 PM Microsoft Excel 2010 106
  107. V.2 Trích lọc dữ liệu: Trích lọc nâng cao_Advanced Filter v Dùng để lọc dữ liệu theo nhiều điều kiện hoặc trích dữ liệu đến nơi khác. Với cách lọc này, ta phải lập một bảng điều kiện riêng trước khi gọi lệnh Advanced Filter. Ưu điểm là người dùng có thể tự thiết lập được vùng điều kiện trích lọc theo nhiều điều kiện phức tạp. Các bước thực hiện: A. Bước 1: Tạo bảng điều kiện (Criteria) v Bảng điều kiện có thể đặt ở bất cứ vị trí nào và bao gồm: • Hàng đầu tiên: tên các trường làm điều kiện trích lọc (Nên copy từ danh sách dữ liệu ra để tránh sai sót). • Các hàng tiếp theo: là các giá trị cho điều kiện tương ứng. 9:11 PM Microsoft Excel 2010 107
  108. V.2 Trích lọc dữ liệu: Các trường hợp điều kiện: v Chỉ có một điều kiện chính xác: Vd: Trích lọc ra các sinh viên có giới tính là “Nữ”. 9:11 PM Microsoft Excel 2010 108
  109. V.2 Trích lọc dữ liệu: Các trường hợp điều kiện (tt) v Chỉ có một điều kiện gần đúng • Nếu dữ liệu điều kiện là số: Sử dụng các toán tử điều kiện v Vd: Trích lọc ra các sinh viên viên có điểm Trung Bình lớn hơn hoặc bằng 7. 9:11 PM Microsoft Excel 2010 109
  110. V.2 Trích lọc dữ liệu: Các trường hợp điều kiện(tt) v Nếu dữ liệu điều kiện là chuỗi: Sử dụng các ký tự đại diện (? , *) Vd: Trích lọc ra các sinh viên có họ “Nguyễn”. 9:11 PM Microsoft Excel 2010 110
  111. V.2 Trích lọc dữ liệu: Các trường hợp điều kiện(tt) - Phải thỏa mãn hai hay nhiều điều kiện cùng lúc. Vd: Trích lọc ra các sinh viên “Nam” có điểm Trung Bình lớn hơn 7. Lưu ý: Các điều kiện phải cùng thỏa mãn ta để các giá trị trên cùng dòng. 9:11 PM Microsoft Excel 2010 111
  112. V.2 Trích lọc dữ liệu: Các trường hợp điều kiện(tt) - Chỉ cần thỏa mãn một trong các điều kiện. Vd: Trích lọc rac các sinh viên có điểm Toán >5 hoặc diểm Tin >7. Lưu ý: Nếu chỉ cần thỏa mãn 1 trong các điều kiện ta để các giá trị trên các dòng khác nhau. 9:11 PM Microsoft Excel 2010 112
  113. V.2 Trích lọc dữ liệu: B. Bước 2: Chọn tab Data nhóm Sort & Filter lệnh Advanced Action: Filter the list, in­place: Kết quả lọc xuất hiện ngay trên CSDL gốc. Các dòng không thỏa mãn điều kiện sẽ bị ẩn. Copy to another location: Kết quả lọc sẽ được trích sang một vùng khác. 9:11 PM Microsoft Excel 2010 113
  114. V.2 Trích lọc dữ liệu: - List range: Chọn vùng địa chỉ của bảng dữ liệu cần lọc. - Criteria range: Địa chỉ bảng điều kiện đã tạo trước đó (cách xác định tương tự List range). - Copy to: Mục này chỉ xuất hiện khi ở mục Action chọn “Copy to another location”. Xác định địa chỉ của một ô bất kỳ ngoài vùng trống dự kiến sẽ chứa kết quả. - Unique records only: Nhấp tích chuột vào nếu không muốn lọc lấy dữ liệu trùng điều kiện (Nếu có nhiều dòng dữ liệu trùng điều kiện thì chỉ lấy 1 dòng). 9:11 PM Microsoft Excel 2010 114
  115. V.3 Tạo Tổng Cấp Dưới( Subtotals) - Lệnh dùng để nhóm dữ liệu theo từng nhóm đồng thời chèn vào cuối mỗi nhóm những dòng thống kê tính toán (gọi là các tổng con - Subtotals) và một dòng tổng kết ở cuối phạm vi (gọi là tổng lớn - GrandTotal). vCác bước thực hiện: Bước 1: Sắp xếp CSDL theo cột làm khoá (muốn nhóm theo cột nào thì cột đó gọi là cột làm khoá) Bước 2: Quét chọn vùng CSDL, vào tab Data nhóm Outline lệnh Subtotat, xuất hiện hộp thoại sau: 9:11 PM Microsoft Excel 2010 115
  116. V.3 Tạo Tổng Cấp Dưới( Subtotals) •At each change in: Chọn trường làm khoá để sắp xếp •Use Function: Chọn hàm sử dụng để thống kê •Add SubTotal to: Đánh dấu vào những cột cần thống kê giá trị •Replace current Subtotals: Thay các hàng Subtotal tạo trước •Page Break Between Groups: Tự động động tạo dấu ngắt trang giữa các nhóm dữ liệu. •Sumary Below data: Tạo các dòng thống kê phía dưới các nhóm dữ liệu. 9:11 PM Microsoft Excel 2010 116
  117. V.3 Tạo Tổng Cấp Dưới( Subtotals) §Và cuối cùng chúng ta có kết quả như sau: 9:11 PM Microsoft Excel 2010 117
  118. V.4 Tạo dữ liệu tổng hợp từ các dữ liệu chi tiết (Consolidate) v Được sử dụng để tạo dữ liệu tổng hợp từ những dữ liệu chi tiết (được chọn lựa trên cùng một hoặc nhiều tập tin bảng tính khác nhau). v Nếu dữ liệu chi tiết có cùng cấu trúc (có cùng số lượng trường, tên trường và kiểu dữ liệu từng trường hoàn toàn như nhau) thì dữ liệu tổng hợp sẽ có cấu trúc tương tự như các dữ liệu chi tiết và mỗi bản ghi của dữ liệu tổng hợp sẽ là dữ liệu tổng hợp từ các bản ghi trong các dữ liệu chi tiết. v Nếu các dữ liệu chi tiết không có cùng cấu trúc thì nhất thiết phải có chung ít nhất trường ngoài cùng bên trái để làm khoá. Lúc đó dữ liệu tổng hợp sẽ có dạng gộp các dữ liệu chi tiết theo qui tắc: v Các trường trùng tên sẽ được tổng hợp theo hàm người dùng chọn. v Các trường không trùng tên sẽ được ghép nối. 9:11 PM Microsoft Excel 2010 118
  119. V.4 Tạo dữ liệu tổng hợp từ các dữ liệu chi tiết (Consolidate) Các bước thực hiện vBước 1: Chuẩn bị các bảng dữ liệu chi tiết vBước 2: Đặt con trỏ chuột tại vị trí sẽ chứa kết quả tổng hợp. vVào tab Data nhóm Data Tools lệnh Consolidate xuất hiện hộp thoại sau: 9:11 PM Microsoft Excel 2010 119
  120. V.4 Tạo dữ liệu tổng hợp từ các dữ liệu chi tiết (Consolidate) vFunction: Chọn hàm cần dùng để tổng hợp. vReference: Nhập hoặc dùng chuột để quét chọn và ấn nút Add lần lượt địa chỉ các bảng dữ liệu chi tiết cần tổng hợp. vTop Row: Tạo dòng tiêu đề cho bảng tổng hợp. vLeft Column: Tạo tiêu đề cột đầu tiên cho bảng tổng hợp. vCreate link to source data: Tạo mối liên kết từ bảng tổng hợp đến các bảng chi tiết nhằm mục đích nếu có sự thay đổi trong các bảng dữ liệu chi tiết thì các dữ liệu liên quan trong bảng tổng hợp cũng tự thay đổi theo. 9:11 PM Microsoft Excel 2010 120
  121. V.5 Kiểm tra nhập liệu (Data validation) v Khi xây dựng bảng tính trong Excel, một số trường hợp yêu cầu nhập liệu là những giá trị chỉ có thể là số nguyên giới hạn, số thập phân, ngày, giờ, trong danh sách có sẵn, hoặc chuỗi có độ dài nhất định, v Làm sao để Excel biết được dữ liệu do người dùng nhập vào đó có đúng yêu cầu? Làm sao để Excel có thể thông báo lỗi và cảnh báo co người dùng biết họ cần phải nhập giá trị gì tại ô xác định. v Tất cả các yêu cầu đó sẽ được thực hiện thông chức năng Data Validation của Excel. 9:11 PM Microsoft Excel 2010 121
  122. V.5 Kiểm tra nhập liệu (Data validation) Các bước thực hiện: v Bước 1: Quét chọn các ô trên bảng tính muốn Excel kiểm tra việc nhập liệu. v Bước 2: Vào tab Data nhóm Data Tools lệnh Data Validation xuất hiện hộp thoại sau: 9:11 PM Microsoft Excel 2010 122
  123. V.5 Kiểm tra nhập liệu (Data validation) Tab Settings: cài đặt điều kiện nhập liệu (Validation criteria) Hộp Allow: chọn một trong các định dạng điều kiện. Có các dạng sau: v Any value: dạng mặc định, cho phép nhập bất cứ giá trị gì vào ô. v Whole number: chỉ cho phép nhập giá trị là số nguyên trong khoảng theo điều kiện so sánh tương ứng trong Data. v Decimal: chỉ cho phép nhập giá trị là số nguyên hoặc số thập phân trong khoảng theo điều kiện so sánh tương ứng trong Data. v List: chỉ cho phép nhập giá trị có tồn tại trong một danh sách cho trước. 9:11 PM Microsoft Excel 2010 123
  124. V.5 Kiểm tra nhập liệu (Data validation) Tab Settings: cài đặt điều kiện nhập liệu (Validation criteria) Hộp Allow: chọn một trong các định dạng điều kiện. Có các dạng sau: v Date: chỉ cho phép nhập giá trị ở dạng ngày tháng năm trong khoảng theo điều kiện so sánh tương ứng trong Data. v Time: chỉ cho phép nhập giá trị ở dạng giờ phút giây trong khoảng theo điều kiện so sánh tương ứng trong Data. v Text length: chỉ cho phép nhập vào chuỗi có chiều dài xác định (tính bằng số ký tự, kể cả khoảng trắng và dấu). v Custom: cho phép người dùng tự thiết lập định dạng của dữ liệu được phép nhập. 9:11 PM Microsoft Excel 2010 124
  125. V.5 Kiểm tra nhập liệu (Data validation) Thẻ Input Message: Hiển thị thông báo yêu cầu khi nhập liệu 9:11 PM Microsoft Excel 2010 125
  126. V.5 Kiểm tra nhập liệu (Data validation) Tab Error Alert: Hiển thị thông báo lỗi khi nhập không đúng dữ liệu được yêu cầu. 9:11 PM Microsoft Excel 2010 126
  127. VI. IN VÀ ĐỊNH DẠNG TRANG IN VI.1 Định dạng trang in Để điều chỉnh các thông số thiết lập cho trang in ta vào tab File Print Page Setup : 9:11 PM Microsoft Excel 2010 127
  128. VI.1 Định dạng trang in v Thẻ Page: chọn hướng in và khổ in v Orientation: Thay đổi kiểu in v Portrait: Định dạng kiểu in đứng v Landscape: Định dạng kiểu in ngang v Paper size: Chọn khổ giấy in 9:11 PM Microsoft Excel 2010 128
  129. VI.1 Định dạng trang in vThẻ Margins: Định dạng lề trang • Top: Lề trên • Left: Lề trái • Bottom: Lề dưới • Right: Lề phải • Header: Tiêu đề trên • Footer: Tiêu đề dưới • Center on page: Định nội dung in nằm giữa trang theo chiều ngang (Horizontally) hay theo chiều dọc (Vertically) 9:11 PM Microsoft Excel 2010 129
  130. VI.1 Định dạng trang in 9:11 PM Microsoft Excel 2010 130
  131. VI.1 Định dạng trang in v Thẻ Header/Footer: tạo tiêu đề đầu (Header) và chân (Footer) trang in. v Custom Header: tạo tiêu đề đầu trang in v Custom Footer: tạo tiêu đề chân trang in. 9:11 PM Microsoft Excel 2010 131
  132. VI.1 Định dạng trang in vThẻ Sheet: chọn vùng in • Print area: xác định vùng in • Rows to repeat at top: chọn các dòng cần in lặp lại ở đầu mỗi trang. • Columns to repeat at left: chọn các cột cần in lặp lại bên trái mỗi trang. • Gridlines: In nội dung bảng tính có đường lưới. • Row and column header: In cả tiêu đề cột và số thứ tự dòng. • Black and white: Chỉ in trắng đen. v Draft quality: Chế độ in lợt 9:11 PM Microsoft Excel 2010 132
  133. VI.1 Định dạng trang in v Thẻ Sheet: chọn vùng in v Comments: In chú thích (None: Không in; At end of sheet: In chú thích ở cuối trang; As displayed on sheet: In như đang hiển thị trên sheet) v Cell errors as: Chỉ định in các ô bị lỗi (Displayed: In như hiển thị lỗi; Blank: Để trống; ­­ Thay các ô lỗi bằng dấu gạch; #N/A: Các ô bị lỗi thì in chữ #N/A) v Page order: Chỉ định thứ tự in các trang Ø Down, then over: Từ trên xuống và từ trái qua phải Ø Over, then down: Từ trái qua phải và từ trên xuống 9:11 PM Microsoft Excel 2010 133
  134. VI.1 Định dạng trang in v Thẻ Sheet: chọn vùng in v Comments: In chú thích (None: Không in; At end of sheet: In chú thích ở cuối trang; As displayed on sheet: In như đang hiển thị trên sheet) v Cell errors as: Chỉ định in các ô bị lỗi (Displayed: In như hiển thị lỗi; Blank: Để trống; ­­ Thay các ô lỗi bằng dấu gạch; #N/A: Các ô bị lỗi thì in chữ #N/A) v Page order: Chỉ định thứ tự in các trang Ø Down, then over: Từ trên xuống và từ trái qua phải Ø Over, then down: Từ trái qua phải và từ trên xuống 9:11 PM Microsoft Excel 2010 134
  135. VI.2 Các chế độ hiển thị trang in trong Excel v MS Excel 2010 hỗ trợ 5 kiểu xem Workbook: v Normal: Đây là chế độ hiển thị thường xuyên trong quá trình nhập liệu, tính toán, và là chế độ mặc định của Excel. v Page Layout: xem theo dạng phân trang in. v Page Break Preview: Hiển thị bảng tính với các dấu phân trang, tại đây bạn có thể chia lại trang bằng cách kéo thả các đường chia cách trang. v Custom Views: cài đặt chế độ xem theo ý người dùng. v Full Screen: xem toàn màn hình. Nhấn phím esc để trở về bình thường. 9:11 PM Microsoft Excel 2010 135
  136. VI.3 In bản tính v Chọn tab File Print lệnh Print (như hình bên dưới) 9:11 PM Microsoft Excel 2010 136
  137. VI.3 In bản tính vName: Chọn tên máy in vPrint range: chọn các trang cần in. vAll để in hết tất cả các trang. vPage(s) để in từ trang (From) đến trang(To) vNumber of copies: Số bản in của mỗi trang 9:11 PM Microsoft Excel 2010 137
  138. VI.3 In bản tính vPrint what: vSelection: In các ô và đối tượng đang chọn. vActive Sheet(s): In sheet hiện hành. vEntire workbook: In tất cả các sheet. vList: Chỉ in danh sách được chọn trên sheet. 9:11 PM Microsoft Excel 2010 138
  139. VII. CÁC CHỨC NĂNG KHÁC VII.1 Thiết lập Freeze Panes vThiết lập vùng cố định trên bảng tính khi lăn các thanh cuộn (Scroll Bar). vBước 1: chọn vị trí góc đánh dấu sẽ cố định (bắt đầu từ dòng phía trên và cột bên trái của ô được chọn sẽ được cố định). vBước 2: chọn tab View nhóm Window lệnh Freeze Panes 9:11 PM Microsoft Excel 2010 139
  140. VII.1 Thiết lập Freeze Panes 9:11 PM Microsoft Excel 2010 140
  141. VII.1 Thiết lập Freeze Panes - Freeze Panes: thiết lập cố định tại vị trí đã chọn trong bước 1. - Freeze Top Row: cố định dòng đầu. Freeze First Column: cố định cột đầu. - Lưu ý: Mỗi bảng tính (sheet) chỉ có 1 vị trí Freeze Panes. Muốn bỏ hay thay đổi sang vị trí khác ta chọn tab View nhóm Window lệnh Unfreeze Panes. 9:11 PM Microsoft Excel 2010 141
  142. VII.2 Kiểm tra đánh giá công thức - Trong quá trình lập công thức tính toán với bảng tính đặc biệt là các công thức phức tạp có nhiều phép toán cũng như sử dụng nhiều hàm. Do đó nếu cần kiểm tra việc thực hiện tính toán của Excel trong những công thức như vậy có đúng với ý đồ người dùng hay không một cách thủ công sẽ rất phức tạp. Nhưng với chức năng kiểm tra đánh giá công thức của Excel (Evaluate Formula) việc đó sẽ đơn giản hơn rất nhiều. v Thao tác thực hiện: v Chọn công thức cần kiểm tra, đánh giá. v Chọn tab Formulas nhóm Formula Auditing lệnh Evaluate Formula: 9:11 PM Microsoft Excel 2010 142
  143. VII.2 Kiểm tra đánh giá công thức v Thao tác thực hiện: v Chọn công thức cần kiểm tra, đánh giá. v Chọn tab Formulas nhóm Formula Auditing lệnh Evaluate Formula: 9:11 PM Microsoft Excel 2010 143
  144. VII.3 Sao chép đặc biệt vTrong quá trình sao chép, đôi khi chúng ta chỉ cần sao chép một số chọn lọc nào đó mà không phải là toàn bộ nội dung gốc. Khi đó thay vì dùng lệnh Paste ta sẽ sử dụng lệnh Paste Special • Các bước thực hiện: vBước 1: chọn vùng cần sao chép, ra lệnh Copy vBước 2: chọn vị trí cần sao chép đến, chọn tab Home nhóm Clipboard lệnh Paste Paste Special 9:11 PM Microsoft Excel 2010 144
  145. VII.3 Sao chép đặc biệt 9:11 PM Microsoft Excel 2010 145
  146. VII.3 Sao chép đặc biệt Tùy chọn dán Ý nghĩa All Dán cả giá trị và định dạng của vùng nguồn Formulas Dán giá trị và công thức, không định dạng Values Chỉ dán giá trị và kết quả của công thức, không định dạng Formats Chỉ dán vào định dạng, bỏ qua tất cả giá trị và công thức Comments Chỉ dán vào chú thích, bỏ qua tất cả giá trị và công thức Validation Chỉ dán vào các qui định xác thực dữ liệu cho vùng đích All except borders Dán vào mọi thứ chỉ loại bỏ khung viền Columns widths Chỉ dán vào thông tin quy định độ rộng cột Formulas and Dán vào giá trị, công thức và các định dạng gốc number formats của các con số, các định dạng khác bị loại bỏ Values and Dán vào giá trị, kết quả của công thức number formats và các định dạng gốc của các con số 9:11 PM Microsoft Excel 2010 146
  147. VII.3 Sao chép đặc biệt Tính toán Ý nghĩa None Không kèm theo việc tính toán nào trên dữ liệu sắp dán vào Add Công các giá trị của vùng nguồn vào các ô tương ứng của vùng đích Subtract Các ô mang giá trị của vùng đích sẽ trừ đi các ô tương ứng của vùng nguồn Multiply Các ô mang giá trị của vùng đích sẽ nhân với các ô tương ứng của vùng nguồn Divide Các ô mang giá trị của vùng đích sẽ chia cho các ô tương ứng của vùng nguồn Skip blanks Không dán đè ô rỗng ở vùng nguồn vào ô có giá trị ở vùng đích Transpose Dán vào và đảo dòng thành cột hoặc ngược lại Paste Link Dán vào và tham chiếu ô đích đến ô nguồn 9:11 PM Microsoft Excel 2010 147
  148. VII.3 Định dạng theo điều kiện (Conditional Formatting) v Conditional Formatting là công cụ cho phép bạn áp dụng định dạng cho một ô hay nhiều ô trong bảng tính và sẽ thay đổi định dạng tùy theo giá trị của ô hay giá trị của công thức. v Ví dụ như bạn có thể tạo cho định dạng của ô đó là chữ in đậm màu xanh khi giá trị của nó lớn hơn 100. Khi giá trị của ô thoả điều kiện thì các định dạng bạn tạo ra ứng với điều kiện đó sẽ được áp dụng cho ô đó. Nếu giá trị của ô không thoả điều kiện bạn tạo ra thì định dạng của ô đó sẽ áp dụng định dạng mặc định (default formatting). v Định dạng theo điều kiện khi áp dụng vào các ô (cell) nó sẽ đè lên các định dạng thông thường của ô về màu sắc, kiểu thể hiện văn bản và số Tuy nhiên nếu chúng ta xoá bỏ định dạng theo điều kiện của các ô thì định dạng đã có trước kia của các ô này sẽ được phục hồi. 9:11 PM Microsoft Excel 2010 148
  149. VII.3 Bảo vệ bảng tính v Conditional Formatting là công cụ cho phép bạn áp dụng định dạng choTrong quá trình làm việc với bảng tính, đôi khi chúng ta cần phải bảo vệ an toàn cho các công thức, dữ liệu, định dạng, cấu trúc của bảng tính, mà không muốn người dùng khác có thể xem, thay đổi hay xóa được. v Chức năng bảo vệ bảng tính (Protect Sheet) của Excel sẽ giúp thực hiện các đòi hỏi đó một cách linh hoạt. Các bước thực hiện: v Bước 1: Chọn toàn bộ bảng tính. v Bước 1: Đánh dấu các ô dữ liệu và ô công thức trên bảng tính cần được bảo vệ hay che dấu công thức (nếu là ô công thức). (Xem mục 2.7 – Thẻ Protection). v Bước 2: Vào Tab File Info Protect Workbook Protect Current Sheet 9:11 PM Microsoft Excel 2010 149
  150. VII.3 Bảo vệ bảng tính 9:11 PM Microsoft Excel 2010 150
  151. VII.3 Bảo vệ bảng tính v Bước 3: Nhập vào Password bảo vệ (nếu cần). v Bước 4: Chọn các tác vụ cho phép người dùng được thực hiện đối với bảng tính v Bước 5: Nhấn OK để hoàn tất, ngay lập tức Excel sẽ thực thi chức năng bảo vệ bảng tính. 9:11 PM Microsoft Excel 2010 151