Lý Thuyết Excel

TRƯỜNG TRUNG CẤP CÔNG NGHỆ THÔNG TIN TM.COMPUTER LÝ THUYẾT_EXCEL 1 (Version 2010) I. THIẾT LẬP CÁC TÙY CHỌN FILE  OPTIONS ....................................... 3 1. Nhóm General: Thiết lập các tùy chọn thông dụng của Excel. .......................... 3 2. Nhóm Formulas: Các thiết lập liên quan đến công việc tính toán, công thức, cách xử lý lỗi. ............................................................................................................. 3 3. Nhóm Proofing: Các tùy chọn liên quan đến việc định dạng văn bản và kiểm tra lỗi chính tả: .......................................................................................................... 3 4. Nhóm Save: Dùng để thiết lập các tùy chọn liên quan đến lưu trữ, sao lưu, khôi phục dữ liệu. ................................................................................................................ 3 5. Nhóm Advanced: Thiết lập các tùy chọn nâng cao ............................................... 3 6. Customize: Thiết lập các nút lệnh cho thanh truy cập nhanh. ........................... 4 7. Add-Ins: Bổ sung thêm các ứng dụng khác trong Excel. ..................................... 4 8. Trust Center: Thiết lập các chức năng bảo vệ an toàn cho bảng tính. ............ 4 II. SỬ DỤNG CÁC TÍNH NĂNG CAO CẤP CỦA EXCEL ................................. 4 1. Thiết lập giá trị giới hạn nhập liệu trong ô (Validation) ............................. 4 2. Sử dụng lệnh sao chép và dán đặc biệt trong Excel (Paste Special) .... 4 3. Tùy biến chú thích cho ô trong bảng tính ........................................................... 5 4. Nhập dữ liệu đồng thời trên nhiều Sheet: ........................................................... 5 5. Sử dụng tính năng lập dàn trong Excel ................................................................ 5 6. Liên kết dữ liệu ................................................................................................................. 5 7. Tạo danh sách .................................................................................................................... 5 III. SỬ DỤNG CÁC TÍNH NĂNG ĐỊNH DẠNG CÓ ĐIỀU KIỆN ....................... 6 1. Tô màu dòng xen kẽ ....................................................................................................... 6 2. Tô màu dữ kiệu theo điều kiện ................................................................................. 6 3. Tìm dữ liệu trùng ............................................................................................................. 7 IV. SỬ DỤNG CÁC HÀM NÂNG CAO VÀ THỐNG KÊ ..................................... 7 1. Hàm tham chiếu ............................................................................................................... 7  HÀM MATCH() ................................................................................................................. 7 TRƯỜNG TRUNG CẤP CÔNG NGHỆ THÔNG TIN TM.COMPUTER LÝ THUYẾT_EXCEL 2  HÀM INDEX(): ................................................................................................................. 8  HÀM INDERECT(): ......................................................................................................... 9 2. Hàm thời gian .................................................................................................................... 9  HÀM DATEDIF() Công dụng: Hàm trả về một giá trị là số ngày, số tháng hay số năm giữa hai khoảng thời gian theo tùy chọn. Cú pháp: ............................. 9  HÀM EDATE() ................................................................................................................ 10 3. Hàm tìm ra giá trị lớn/nhỏ thứ k trong dãy kiểm tra.................................. 10  HÀM SMALL() ................................................................................................................ 10  HÀM LARGE () ............................................................................................................... 10 V. THỐNG KÊ CƠ SỞ DỮ LIỆU ................................................................ 11 1. Sử dụng công thức mảng ........................................................................................... 11 2. Sắp xếp dữ liệu ............................................................................................................... 11 3. Tính toán theo cấp bậc SUBTOTAL........................................................................ 12 4. Hàm thống kê Cơ sở dữ liệu ..................................................................................... 12 =DSUM( Bảng dữ liệu, Tiêu đề cột tính tổng, Vùng điều kiện) .............................. 12 =DCOUNTA( Bảng dữ liệu, Tiêu đề cột đếm, Vùng điều kiện) ............................... 12 =DMAX( Bảng dữ liệu, Tiêu đề cột tính giá trị lớn nhất, Vùng điều kiện) .......... 13 =DMIN(Bảng dữ liệu, Tiêu đề cột tính giá trị nhỏ nhất, Vùng điều kiện) ........... 13  Hàm SUBTOTAL() ...................................................................................................... 13 5. Thống kê dữ liệu với Consolidate .......................................................................... 14 6. Thống kê dữ liệu với chức năng Data Table ..................................................... 14 7. Thống kê dữ liệu với PivotTable ............................................................................. 16 8. Rút trích dữ liệu theo điều kiện .............................................................................. 17 9. Import & Export dữ liệu ............................................................................................. 17 TRƯỜNG TRUNG CẤP CÔNG NGHỆ THÔNG TIN TM.COMPUTER LÝ THUYẾT_EXCEL 3 I. THIẾT LẬP CÁC TÙY CHỌN FILE  OPTIONS 1. Nhóm General: Thiết lập các tùy chọn thông dụng của Excel.  Use Interface Options: Các thiết lập giao diện cửa sổ làm việc của Excel.  When creating new workbook: Các thiết lập mặc dịnh khi tạo mới một workbook như: kiểu chữ, kích thước chữ, số lượng sheet được hiển thị.  Personalize your copy of Micosoft Excel: Thông tin cá nhân sử dụng. 2. Nhóm Formulas: Các thiết lập liên quan đến công việc tính toán, công thức, cách xử lý lỗi.  Caculation Options: Các tùy chọn liên quan đến việc tính toán:  Automatic: Tự động tính toán, cập nhật dữ liệu trong bảng tính.  Automatic except for data table: Tự động tính toán tất cả dữ liệu trong bảng, ngoại trừ dữ liệu trong vùng sử dụng chức năng Data Table.  Manual: Tắt chức năng tính toán tự động, khi làm việc với các bảng tính lớn việc tính toán tự động sẽ làm cho Excel chạy chậm hơn vì phải thường xuyên cập nhật kết quả.  Working with formulas: Các tùy chọn liên quan đến công thức.  Error Checking thiết lập việc kiểm tra lỗi.  Error Checking rules: Quy tắc kiểm tra lỗi. 3. Nhóm Proofing: Các tùy chọn liên quan đến việc định dạng văn bản và kiểm tra lỗi chính tả:  AutoCrrect Options: Thiết lập bộ chữ tắt, sửa lỗi và định dạng dữ liệu nhập vào.  When correcting spelling in Microsoft Office programs: Thiết lập việc kiểm tra lỗi chính tả trong khi làm việc với các ứng dụng của Microsoft. 4. Nhóm Save: Dùng để thiết lập các tùy chọn liên quan đến lưu trữ, sao lưu, khôi phục dữ liệu. - Save workbooks: Thiết lập các tùy chọn liên quan đến việc lưu trữ dữ liệu như:  Save file in this format: Kiểu định dạng file.  Save AutoRecover in formation every: thời gian tự động lưu tài liệu trong khi đang làm việc.  Keep the last autosaved version if I close without saving: Tự động lưu phiên bản cuối cùng nếu đóng tài liệu mà chưa lưu.  AutoRecover file location: Vị trí lưu tập tin phục hồi.  Default file location: Vị trí mặc định. - AutoRecover exceptions for: Thiết lập đường dẫn tạo bản sao lưu dự phòng. - Offline editing options for document management server files: Thiết lâp việc chỉnh sửa những bảng tính được làm việc theo nhóm thông qua mạng máy tính. 5. Nhóm Advanced: Thiết lập các tùy chọn nâng cao - Editing Options: Thiết lập cho việc nhập liệu. TRƯỜNG TRUNG CẤP CÔNG NGHỆ THÔNG TIN TM.COMPUTER LÝ THUYẾT_EXCEL 4 - Cut, Copy, and Paste: Thiết lập cho việc cắt, sao chép và dán. - Print: Thiết lập cho công việc in ấn. - Display: Thiết lập các hiển thị trong bảng tính. - … 6. Customize: Thiết lập các nút lệnh cho thanh truy cập nhanh. 7. Add-Ins: Bổ sung thêm các ứng dụng khác trong Excel. 8. Trust Center: Thiết lập các chức năng bảo vệ an toàn cho bảng tính. II. SỬ DỤNG CÁC TÍNH NĂNG CAO CẤP CỦA EXCEL 1. Thiết lập giá trị giới hạn nhập liệu trong ô (Validation) - Chọn Tab Data  Trong ngăn Data Tools  Data Validation  Data Validation.  Thẻ Setting: Cho phép thiết lập cài đặt về điều kiện nhập liệu trong Validation criteria.  Whole number: Chỉ cho phép nhập số nguyên.  Decimal: Chỉ cho phép nhập số thực.  List: Chỉ cho phép nhập liệu từ một danh sách sẵn có.  Date: Chỉ cho phép nhập liệu kiểu ngày/tháng/năm  Text length: Chỉ cho phép nhập liệu là chuỗi.  Thẻ Input Message: Hiển thị thông tin nhập liệu khi di chuyển chuột vào ô đó.  Show input message when cell is selected: Bật (tắt) chế độ hiển thị thông báo.  Title: Nội dung tiêu đề hiển thị.  Error message: Nội dung thông báo.  Thẻ Error Alert : Xuất thông báo cảnh báo khi người sử dụng nhập dữ liệu sai.  Show error alert after invalid data is entered: Hiển thị cảnh báo lỗi sau khi dữ liệu nhập không hợp lệ.  Style: Kiểu cảnh báo: Stop, Warning, Information  Title: Nội dung tiêu đề thông báo  Input message: Nội dung thông báo. 2. Sử dụng lệnh sao chép và dán đặc biệt trong Excel (Paste Special) + Chọn vùng ô chứa công thức cần sao chép  Chọn Copy trong nhóm Clipboard (Hoặc nhấn phải chuột  Copy). + Tại vị trí cần sao chép tới  Paste Special (Hoặc nhấn phải  Chọn Paste Special)  Chọn một trong các lựa chọn sau: - All: Dán cả giá trị và định dạng của vùng nguồn - Formulas: Chỉ lấy giá trị và công thức, không lấy định dạng. - Values: Chỉ dán giá trị và kết quả của công thức, không lấy định dạng. - Formats: Chỉ dán vào định dạng và bỏ qua tất cả các giá trị và công thức. TRƯỜNG TRUNG CẤP CÔNG NGHỆ THÔNG TIN TM.COMPUTER LÝ THUYẾT_EXCEL 5 - Comments: Dán vào chú thích của các ô và bỏ qua tất cả các giá trị và công thức. - Validation: Dán các quy định cho dữ liệu của vùng đích. - … - Transpose: Dán và chuyển đổi dữ liệu từ cột thành dòng và ngược lại. - Paste Link: Dán và tham chiếu ô đích đến ô nguồn. 3. Tùy biến chú thích cho ô trong bảng tính - Chọn ô trong bảng tính cần chèn chú thích  Chọn Tabb Review  Trong nhóm Comment  Chọn New Comment  Nhập chú thích. - Ngoài ra có thể sử dụng chức năng chú thích cho toàn bộ khối dữ liệu bằng lệnh Data  Validation  Chọn thẻ Input Message  Nhập nội dung chú thích  OK. 4. Nhập dữ liệu đồng thời trên nhiều Sheet: - Nhóm các Sheet lại: Chọn Sheet thứ nhất (nơi sẽ nhập dữ liệu)  Giữ phím Ctrl và chọn tên các Sheet còn lại  Nhập dữ liệu vào Sheet hiện hành thì dữ liệu đó sẽ tự động nhập vào các Sheet còn lại. - Lúc này tập tin được ở trạng thái Group (Trên tiêu đề tập tin có kèm chữ Group trong móc vuông). - Sau khi nhập xong dữ liệu cho các Sheet nếu muốn bỏ nhóm: Chọn Sheet đầu tiên  Giữ phím Ctrl  Chọn các Sheet còn lại  Chọn Ungroup. 5. Sử dụng tính năng lập dàn trong Excel + Thiết lập dàn: - Xác định vùng cột/dòng (Không chọn ô) - Chọn Tab Data  Trong nhóm Outline  Group  Group - Click vào dấu (+) để truy xuất nhóm, Click vào ấu (-) để ẩn một nhóm. + Xóa bỏ dàn: - Chọn Tab Data  Trong nhóm Outline  Ungroup  Clear Outline 6. Liên kết dữ liệu - Liên kết trong Workbook: Chọn vị trí cần liên kết  Vào Tab Insert  Hyperlink  Bookmark  Chọn Sheet cần liên kết  OK. - Liên kết ngoài Workbook: Chọn vị trí cần liên kết  Vào Tab Insert  Hyperlink  Chọn đường dẫn đến một tập khác cần liên kết tới  OK. 7. Tạo danh sách + Customlist: - Cách 1: Vào File  Options  Advanced  Trong nhóm General chọn mục Edit Custom List  Nhập danh sách trong hộp List entries, các danh sách cách nhau bởi dấu phẩy (,)  Add. TRƯỜNG TRUNG CẤP CÔNG NGHỆ THÔNG TIN TM.COMPUTER LÝ THUYẾT_EXCEL 6 - Cách 2: Nhập danh sách cần tạo  Data  Validation  Trong mục Allow chọn List  Xác định khối danh sách cần tạo trong Source  OK - Cách sử dụng danh sách đã tạo: Nhập một giá trị bất kỳ có trong danh sách  Sử dụng Fill handle để điền danh sách. + List động: Nhập danh sách cần tạo list  Xác định khối danh sách vừa nhập  Insert  Table  Có thể đánh dấu vào mục My table has headrs (dòng đầu tiên của cột danh sách làm tiêu đề cho bảng)  OK. III. SỬ DỤNG CÁC TÍNH NĂNG ĐỊNH DẠNG CÓ ĐIỀU KIỆN 1. Tô màu dòng xen kẽ - Xác định khối dữ liệu  Chọn Tab Home  Conditional Formating  New Rule  Use Formula to determine wich cells to format  Trong ô: Format values where this formula is true  Nhập công thức xác định số dòng tô màu xen kẽ  OK. 2. Tô màu dữ kiệu theo điều kiện Là công cụ cho phép áp dụng định dạng cho một ô (Cell) hay nhiều ô (Range of cells) 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. Thao tác: - Xác định khối cần định dạng. - Chọn Tab Home  Trong nhóm Styles  Conditional Formating:  Highlight Cell Rules: Định dạng màu cho ô tùy theo giá trị trong ô.  Top/Bottom Rules: Điều kiện định dạng ứng với giá trị lớn nhất hoặc nhỏ nhất.  Data Bars: Định dạng theo kiểu biểu đồ cho giá trị trong ô theo thứ tự từ nhỏ đến lớn.  Color Scales: Định dạng màu nền biến đổi theo giá trị trong ô từ nhỏ đến lớn.  Icon Sets: Hiển thị các icon trực tiếp trong các ô. Các icon hiển thị phụ thuộc vào giá trị chứa trong ô.  New Rule: Lập điều kiện định dạng ở mức độ phức tạp, có nhiều điều kiện và có sử dụng công thức: - Format all cells based on their values: Định dạng tất cả các ô dựa vào giá trị của nó. - Format only cells that contain: Định dạng những ô có chứa các điều kiện. - Format only top or bottom ranked values: Định dạng cho những ô được xếp theo thứ hạng cao hay thấp. - Format only values that are above or below average: Định dạng cho những ô trên/dưới mức trung bình. - Format only unique or duplicate values: Định dạng dữ liệu trùng/không trùng. TRƯỜNG TRUNG CẤP CÔNG NGHỆ THÔNG TIN TM.COMPUTER LÝ THUYẾT_EXCEL 7 - Use a formula to determine which cells to format: Sử dụng công thức để xác định điều kiện định dạng.  Clear Rules: Xoá tất cả các định dạng đang áp dụng cho các ô được chọn.  Manage Rules: Quản lý, thay đổi, chỉnh sửa các điều kiện đã được thiết lập. 3. Tìm dữ liệu trùng - Xác định khối dữ liệu  Chọn Tab Home  Conditional Formating  Highlight Cells Rules  Duplicate Values. IV. SỬ DỤNG CÁC HÀM NÂNG CAO 1. Hàm tham chiếu  HÀM MATCH() Cú pháp: =MATCH(Giá trị tìm,Mảng dò tìm,Cách thức dò tìm) Công dụng:Trả về vị trí của một giá trị trong mảng một chiều. - Giá trị tìm: là giá trị cần tìm trong mảng.(Tương tự như hàm VLOOKUP). - Bảng dò tìm: là mảng một chiều,tức tập hợp các ô phải ở trên cùng một dòng hoặc một cột. - Cách thức dò tìm: Tương tự như hàm VLOOKUP nhưng nhận thêm giá trị là -1 khi mảng dò tìm sắp xếp giảm dần. Ví dụ: Bảng 1: 1 A B C 2 Mã CV Chức vụ Phụ cấp 3 TP Trưởng phòng 50 4 PP Phó phòng 25 5 KT Kế toán 20 Bảng 2: 9 A B C D 10 Mã khu vực QD LD TN 11 Tên khu vực Quốc doanh Liên doanh Tư nhân Yêu cầu: +Tính số thứ tự của Mã khu vực là QD: Ta lập công thức như sau: =MATCH(“QD”,A10:D10,0)  2 +Tính số thứ tự của mã chức vụ là KT: TRƯỜNG TRUNG CẤP CÔNG NGHỆ THÔNG TIN TM.COMPUTER LÝ THUYẾT_EXCEL 8 =MATCH(“KT”,A2:A6,0)  4  HÀM INDEX(): Cú pháp : =INDEX(Bảng dò tìm,Thứ tự cột, thứ tự dòng) Công dụng : +Tìm giá trị giao giữa dòng và cột có thứ tự là thứ tự dòng,thứ tự cột cho trước. Xét ví dụ trên : =INDEX(A3:C6,3,3)  20 =INDEX(A3:C6,4,2)  Nhân viên Diễn giải: A3:C6: Mảng dò tìm 3: là cột thứ 3 4: là dòng thứ 4  Ta quan sát giao giữa cột 3 dòng 4 là giá trị 10 trong bảng 1 Ghi chú :Thứ tự dòng hoặc thứ tự cột thường được xác định bởi hàm MATCH. Ví dụ : Bảng 1: A B C D 1 Họ tên Mã TS Mã Khu vực Đểm cộng 2 Hoa HN 1 ? 3 Hồng NT 2 4 Hùng NT 3 5 Sơn DN 1 6 Minh NA 3 TRƯỜNG TRUNG CẤP CÔNG NGHỆ THÔNG TIN TM.COMPUTER LÝ THUYẾT_EXCEL 9 Bảng 2: A B C D 12 1 2 3 13 HN 4 5 10 14 NT 3 6 15 15 DN 2 7 20 16 NA 1 8 25 Yêu cầu: + Dựa vào vào Mã TS và Mã khu vực tra ở bảng 2 để tính điểm cộng . Ta lập công thức cho ô D2 trên bảng 1 như sau: =INDEX($B$13:$D$16,MATCH(B2,$A$13:$A$16,0),MATCH(C2,$B$12:$D$12,0))  HÀM INDERECT(): Công dụng: Trả về một tham chiếu từ một chuỗi ký tự. Cú pháp: =INDERECT(ref_text,[a1]) Ý nghĩa: + Ref_text: Tham chiếu đến một ô (tham chiếu có dạng là A1 hay R1C1). - Nếu ref_text là một ô tham chiếu không hợp lệ, hàm sẽ trả về lỗi #REF!. + a1: là một giá trị logic xác định kiểu tham chiếu của ref_text. - Nếu a1=1 (hay True) thì ref_text có dạng tham chiếu là A1 (mặc định là để trống). - Nếu a1=2 (hay False) thì ref_text có dạng tham chiếu là R1C1. 2. Hàm thời gian  HÀM DATEDIF() Công dụng: Hàm trả về một giá trị là số ngày, số tháng hay số năm giữa hai khoảng thời gian theo tùy chọn. Cú pháp: = DATEDIF(Start_day, End_day, Unit)  Start_day: Ngày bắt đầu của khoảng thời gian cần tính toán  End_day: Ngày cuối của khoảng thời gian cấn tính toán (phải lớn hơn ngày bắt đầu).  Unit: Chọn đơn vị kết quả trả về (khi dùng trong hàm phải gõ trong dấu ngoặc kép). Các loại đơn vị có thể chọn lựa: TRƯỜNG TRUNG CẤP CÔNG NGHỆ THÔNG TIN TM.COMPUTER LÝ THUYẾT_EXCEL 10  “y” : số năm chênh lệch giữa ngày bắt đầu và ngày cuối (Chỉ lấy phần nguyên).  “m”: số tháng chênh lệch giữa ngày bắt đầu và ngày cuối (Chỉ lấy phần nguyên).  “d” : số ngày chênh lệch giữa ngày bắt đầu và ngày cuối.  “md” : Hàm sẽ trả về số ngày lẻ của tháng (số ngày chưa tròn tháng) giữa hai khoảng thời gian.  “ym” : Hàm sẽ trả về số tháng lẻ của năm (số tháng chưa tròn năm) giữa hai khoảng thời gian.  “yd” : Hàm sẽ trả về số ngày lẻ của năm (số ngày chưa tròn năm) giữa hai khoảng thời gian.  HÀM EDATE() Công dụng: Trả về một ngày nào đó, tính từ mốc thời gian cho trước và cách mốc thời gian này một số tháng được chỉ định. (Sử dụng khi tình hạn bảo hành sản phẩm hoặc đáo hạn hợp đồng). Cú pháp: Ví dụ: Ngày mua 15/05/2010; Thời gian bảo hành là 18 tháng  Ngày hết bảo hành = EDATE(“15/05/2010”,18)  15/11/2011 3. Hàm tìm ra giá trị lớn/nhỏ thứ k trong dãy kiểm tra  HÀM SMALL() Công dụng: Trả về giá trị nhỏ thứ k trong một khối dữ liệu. Cú pháp: Khối dữ liệu: là vùng dữ liệu hàm sẽ kiểm tra. k: ạ ỏ Ví dụ: =SMALL({5,7,10,12,18},3)  10  HÀM LARGE () Công dụng: Trả về giá trị lớn nhất thứ k trong một khối dữ liệu. Cú pháp: =EDATE(Start_date, months) =SMALL(Khối dữ liệu,k) =LARGE(Khối dữ liệu, k) TRƯỜNG TRUNG CẤP CÔNG NGHỆ THÔNG TIN TM.COMPUTER LÝ THUYẾT_EXCEL 11 Ví dụ: =LARGE({5,7,10,12,18},2)  12 V. THỐNG KÊ CƠ SỞ DỮ LIỆU 1. Sử dụng công thức mảng  Ý nghĩa và cách dùng:  Mảng là tập hợp các phần tử có quan hệ độc lập với nhau. Mảng bao gồm mảng 1 chiều và mảng 2 chiều:  Mảng 1 chiều là vùng số liệu được nằm trên một dòng (mảng ngang) hoặc trên một cột (mảng dọc).  Màng 2 chiều là một vùng số liệu có dạng hình chữ nhật bao gồm nhiều dòng, nhiều cột.  Công thức mảng có thể trả kết quả là nhiều ô hay chỉ 1 ô. Nếu kết quả trả về nhiều ô thì phải chọn các ô đó rồi mới nhập công thức.  Kết thúc một công thức mảng: Sử dụng tổ hợp phím Ctrl + Shift + Enter  Ưu điểm khi sử dụng công thức mảng:  Đảm bảo chính xác về kết quả.  Tránh được việc xóa, thay đổi công thức