Giáo trình Micrpsoft Excel - Trường trung cấp công nghệ thông tin TM.Computer

Chia sẻ: Nguyen Hoang Duc | Ngày: | Loại File: PDF | Số trang:18

Thêm vào BST

Báo xấu

81
lượt xem 11
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

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.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Giáo trình Micrpsoft Excel - Trường trung cấp công nghệ thông tin TM.Computer

TRƯỜNG TRUNG CẤP CÔNG NGHỆ THÔNG TIN TM.COMPUTER (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 1 LÝ THUYẾT_EXCEL
TRƯỜNG TRUNG CẤP CÔNG NGHỆ THÔNG TIN TM.COMPUTER  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 2 LÝ THUYẾT_EXCEL
TRƯỜNG TRUNG CẤP CÔNG NGHỆ THÔNG TIN TM.COMPUTER 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. 3 LÝ THUYẾT_EXCEL
TRƯỜNG TRUNG CẤP CÔNG NGHỆ THÔNG TIN TM.COMPUTER - 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. 4 LÝ THUYẾT_EXCEL
TRƯỜNG TRUNG CẤP CÔNG NGHỆ THÔNG TIN TM.COMPUTER - 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. 5 LÝ THUYẾT_EXCEL
TRƯỜNG TRUNG CẤP CÔNG NGHỆ THÔNG TIN TM.COMPUTER - 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. 6 LÝ THUYẾT_EXCEL
TRƯỜNG TRUNG CẤP CÔNG NGHỆ THÔNG TIN TM.COMPUTER - 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: 7 LÝ THUYẾT_EXCEL
TRƯỜNG TRUNG CẤP CÔNG NGHỆ THÔNG TIN TM.COMPUTER =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 8 LÝ THUYẾT_EXCEL
TRƯỜNG TRUNG CẤP CÔNG NGHỆ THÔNG TIN TM.COMPUTER 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: 9 LÝ THUYẾT_EXCEL
TRƯỜNG TRUNG CẤP CÔNG NGHỆ THÔNG TIN TM.COMPUTER  “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: =EDATE(Start_date, months) 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: =SMALL(Khối dữ liệu,k) 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: =LARGE(Khối dữ liệu, k) 10 LÝ THUYẾT_EXCEL
TRƯỜNG TRUNG CẤP CÔNG NGHỆ THÔNG TIN TM.COMPUTER 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 (Công thức mảng không cho phép sửa, xóa).  Tính toán thống kê với nhiều điều kiện phức tạp.  Cấu trúc thống kê của công thức mảng: =Hàm(IF(Điều kiện, Mảng giá trị 1 [,Mảng giá trị 2])  Hàm: Sử dựng các hàm thống kê theo mục đích  Điều kiện: Nếu có nhiều điều kiện thì nối các biểu thức điều kiện bằng dấu * (thay cho hàm And), dấu + (thay cho hàm Or).  Sử dụng dấu ngoặc để thể hiện thứ tự tính toán của điều kiện, không được sử dụng hàm And và hàm Or trong công thức mảng. 2. Sắp xếp dữ liệu - Xác định nội dung cần sắp xếp (bôi đen bảng tính không gồm tiêu đề). - Chọn Tab Data  Sort + Chọn cột tiêu thức cần sắp xếp chính trong Sort by + Chọn kiểu sắp xếp trong Order:  Smallest to Largest: Tăng dần  Largest to Smallest: Giảm dần + Nếu sắp xếp từ 2 cột trở lên: Click Add Level  Chọn cột tiêu thức cần sắp xếp tiếp theo trong Then by,… + OK. 11 LÝ THUYẾT_EXCEL
TRƯỜNG TRUNG CẤP CÔNG NGHỆ THÔNG TIN TM.COMPUTER (Hoặc có thể sử dụng ký hiệu Sort A to Z hoặc Sort Z to A trong Tab Data) Lưu ý: Khi thực hiện sắp xếp theo dòng, sau khi theo 2 thao tác đầu tiên như trên  Chọn Options  Chọn mục Sort Left to Right  OK và tiếp tục thao tác còn lại như trên. 3. Tính toán theo cấp bậc SUBTOTAL  Công dụng: Cho phép thực hiện việc thống kê, tính toán ở những nhóm dữ liệu khác nhau trên những cột điều kiện đưa ra. Excel sẽ tự động chèn vào những dòng thống kê ở cuối mỗi nhóm, và cuối mỗi bảng dữ liệu là một dòng thống kê chung cho toàn bộ cơ sở dữ liệu.  Các thao tác như sau: - Sắp xếp bảng dữ liệu theo chiều tăng dần hay giảm dần. - Xác định toàn bộ bảng dữ liệu  Chọn Tab Data  Trong ngăn Outline  Chọn SubTotal  hộp thoại xuất hiện:  At each change in: Chọn Field cần nhóm.  Use funtion: Chọn hàm thống kê.  Add SubTotal to: Đánh dấu các Field cần thống kê.  Các tuỳ chọn khác:  Replace current SubTotals: Thay thế lệnh Subtotal hiện thời trên cơ sở dữ liệu.  Page Breake between Groups: :Tự động ngắt trang theo từng nhóm.  Summary below Data: Hiển thị dòng thống kê tổng hợp chung cho toàn bộ bảng. - OK  Ghi chú: Để huỷ bỏ thống kê: Vào Data  Subtotal  Remove All 4. Hàm thống kê Cơ sở dữ liệu  Hàm tính tổng theo điều kiện: - Bước 1: Tạo vùng điều kiện (Giống thao tác rút trích) - Bước 2: Dùng hàm: =DSUM( Bảng dữ liệu, Tiêu đề cột tính tổng, Vùng điều kiện)  Hàm đếm số mẫu tin khác trống thoả mãn điều kiện: - Bước 1: Tạo vùng điều kiện (Giống thao tác rút trích) - Bước 2: Dùng hàm: =DCOUNTA( Bảng dữ liệu, Tiêu đề cột đếm, Vùng điều kiện)  Hàm tính giá trị lớn nhất trên một cột của vùng dữ liệu tại những dòng thoả mãn điều kiện của vùng tiêu chuẩn: - Bước 1: Tạo vùng điều kiện (Giống thao tác rút trích) 12 LÝ THUYẾT_EXCEL
TRƯỜNG TRUNG CẤP CÔNG NGHỆ THÔNG TIN TM.COMPUTER - Bước 2: Dùng hàm: =DMAX( Bảng dữ liệu, Tiêu đề cột tính giá trị lớn nhất, Vùng điều kiện)  Hàm tính giá trị nhỏ nhất trên một cột của vùng dữ liệu tại những dòng thoả mãn điều kiện của vùng tiêu chuẩn: - Bước 1: Tạo vùng điều kiện (Giống thao tác rút trích) - Bước 2: Dùng hàm: =DMIN(Bảng dữ liệu, Tiêu đề cột tính giá trị nhỏ nhất, Vùng điều kiện)  Hàm tính giá trị trung bình trên một cột của vùng dữ liệu tại những dòng thoả mãn điều kiện của vùng tiêu chuẩn: - Bước 1: Tạo vùng điều kiện (Giống thao tác rút trích) - Bước 2: Dùng hàm: =DAVERAGE(Bảng dữ liệu, Tiêu đề cột tính giá trị TB, Vùng điều kiện)  Hàm SUBTOTAL() Công dụng: Là hàm tính toán cho một nhóm con trong một danh sách hoặc bảng dữ liệu tuỳ theo phép tính mà bạn chọn lựa trong đối số thứ nhất. Cú pháp: =SUBTOTAL(function_num, ref1, ref2,...) - Function_num: là các số từ 1 đến 11 (hay 101 đến 111) quy định hàm nào sẽ được dùng để tính toán trong SUBTOTAL. Khi ban hành hàm ứng dụng sẽ liệt kê một hộp danh sách bao gồm các hàm số và đi kèm các Function_num. - Ref1, ref2: Các vùng địa chỉ tham chiếu để thực hiện phép tính trên đó. Ghi chú: - Hàm SUBTOTAL chỉ tính toán cho các cột số liệu theo chiều dọc, không tính toán theo chiều ngang. Ví dụ: =SUBTOTAL(2, {20, 80, "ABC", 150})  3 (COUNT) =SUBTOTAL(1, {20, 80, "ABC", 150})  83.333333 (AVERAGE)  Hàm tính tổng các tích trong 2 mảng tương ứng Dùng hàm: = SUMPRODUCT(mảng 1, mảng 2,..) 13 LÝ THUYẾT_EXCEL
TRƯỜNG TRUNG CẤP CÔNG NGHỆ THÔNG TIN TM.COMPUTER Ví dụ 1: =SUMPRODUCT(20,10,5)  1000 Ví dụ 2: Tính tổng tiền của bảng sau: A B 2 Số lượng Đơn giá 3 10 5 4 20 6 5 30 8 6 60 19 7 Tổng tiền 1,550  Công thức tại địa chỉ ô B7 =SUMPRODUCT(A3:A6,B3:B6) 5. Thống kê dữ liệu với Consolidate  Cách dùng: Dùng để tổng hợp nội dung số liệu từ nhiều khối trên nhiều Worksheet hay nhiều Workbook thành một khối. - Chuẩn bị khối ô nơi sẽ nhận kết quả thống kê, sử dụng lệnh Copy  Paste để thực hiện tạo dòng tiêu đề cho bảng. - Quét chọn khối ô dùng chứa kết quả lệnh Consolidate. - Chọn Tab Data  Trong ngăn Data Tools  Chọn Consolidate:  Function: Chọn hàm muốn áp dụng thống kê.  Referrence: Dùng chuột quét chọn khối nguồn từ các Sheet. - Nhấn nút Add để ghi vào All Referentces - Lặp lại cho các khối nguồn khác tham dự trong Consolidate - Trong khung Use Lablels in đánh dấu cả 2 mục Top row và Left column. - Đánh dấu mục Create links source data để liên kết dữ liệu. - OK 6. Thống kê dữ liệu với chức năng Data Table Công dụng: Sử dụng khi các ô trong 1 khối có chung công thức tính toán nhưng các thành phần trong công thức khác nhau (thường phải lập công thức trong 1 ô và thực hiện copy tương tự).  Table 1 biến: (Dựa vào một điều kiện để tính): Các ô trong bảng được tính toán theo công thức mẫu tại dòng đầu tiên hoặc cột đầu tiên tuỳ theo các giá trị đầu vào (biến điều kiện) nằm trên dòng hay cột. Các bước thực hiện: - Bước 1: Nhập các giá trị đầu vào sao cho các giá trị này ở trên 1 dòng hoặc trên 1 cột. - Bước 2: Đánh dấu chọn 1 ô bất kỳ ngoài bảng làm ô tham biến (thay thế cho các giá trị đầu vào). 14 LÝ THUYẾT_EXCEL
TRƯỜNG TRUNG CẤP CÔNG NGHỆ THÔNG TIN TM.COMPUTER - Bước 3: Cài đặt công thức cho bảng Table theo nguyên tắc sau:  Nếu các điều kiện (biến) nằm trên cột thì công thức được cài đặt ở cột bên phải, ô bên trên của cột giá trị đầu vào.  Nếu các điều kiện (biến) nằm trên dòng thì công thức được cài đặt ở dóng bên dưới ô bên trái của dòng giá trị đầu vào. Các điều kiện thống kê theo cột Ô cài đặt công thức Các Điều Kiện Thống Kê Ô tham biến cột Các điều kiện thống kê theo dòng: Ô tham biến dòng Các Điều Kiện Thống Kê Ô cài đặt công thức - Bước 4: Quét chọn vùng chứa các ô công thức và các giá trị đầu vào (không quét ô tham biến). - Bước 5: Chọn Tab Data  Data Tools  What-If Analysis  Data Table…  Nếu các giá trị đầu vào theo cột thì ô tham biến sẽ được xác định trong mục Column Input Cell.  Nếu các giá trị đầu vào theo dòng thì ô tham biến sẽ được xác định trong mục Row Input Cell. - OK. 15 LÝ THUYẾT_EXCEL
TRƯỜNG TRUNG CẤP CÔNG NGHỆ THÔNG TIN TM.COMPUTER  Table 2 biến: (Dựa vào 2 điều kiện để tính): Sử dụng khi các ô trong bảng thống kê có chung một công thức nhưng khác nhau về điều kiện. Thường người sử dụng phải lập nhiều điều kiện, nhiều công thức để thực hiện tính toán. Với Table 2 biến chỉ sử dụng 1 công thức tính 1 vùng điều kiện cho toàn bộ bảng thống kê. Các bước thực hiện: - Bước 1: Nhập danh sách các giá trị đầu vào theo cột. - Bước 2: Tại cột bên phải kế tiếp – bắt đầu từ ô bên trên, nhập danh sách các giá trị đầu vào theo dòng. - Bước 3: Chọn 2 ô bất kỳ ngoài bảng làm ô tham biến thay thế cho các giá trị đầu vào theo cột và theo dòng. - Bước 4: Lập vùng điều kiện (ngoài bảng). Gán cho ô trên cùng của cột chứa giá trị đầu vào trên bảng chính bằng ô tham biến của cột và gán cho ô trên cùng của cột chứa giá trị đầu vào trên bảng chính bằng ô tham biến của dòng. - Bước 5: Lập công thức tại ô giao nhau của các giá trị đầu vào. Ô cài đặt công thức Ô tham biến dòng Các điều kiện Ô tham biến cột - Bước 6: Quét chọn vùng chứa công thức và các điều kiện. - Bước 7: Chọn Tab Data  Data Tools  What-If Analysis  Data Table…  Ô tham biến trên dòng trong mục Row Input Cell.  Ô tham biến trên cột trong mục Column Input Cell.  OK. 7. Thống kê dữ liệu với PivotTable  Chức năng: Tổng hợp, tóm tắt và phân tích dữ liệu từ các danh sách với nhiều góc độ và nhiều cấp độ khác nhau. 16 LÝ THUYẾT_EXCEL
TRƯỜNG TRUNG CẤP CÔNG NGHỆ THÔNG TIN TM.COMPUTER  Thao tác:  Con trỏ phải đặt trong vùng bảng tính cần phân tích và tổng hợp dữ liệu.  Trên thanh Ribbon chọn Tab Insert  Chọn PivotTable  Hiển thị hộp thoại Create PivotTable.  Trong mục Table/Range: Xác định vùng dữ liệu  Chọn vị trí để hiển thị Pivot Table trong mục: Choose where you want the Pivot Table report to be placed:  New Worksheet: Hiển thị Pivot Table trên một Sheet mới  Existing Worksheet: Hiển thị Pivot Table tại vị trí Sheet hiện hành.  OK. 8. Rút trích dữ liệu theo điều kiện Cách 1: - Tạo vùng điều kiện gồm ít nhất 2 dòng: + 01 dòng chứa tiêu đề (Là tiêu đề cột cần quan tâm) + 01 dòng chứa điều kiện (Hoặc các dòng chứa điều kiện) - Chọn Tab Data  Sort & Filter  Advanced: + Xác định khối dữ liệu trong: List Range (Quét bảng dữ liệu bao gồm 1 dòng tiêu đề) + Xác định vùng điều kiện trong: Criteria Range + Chọn (đánh dấu mục) Copy to another location. + Xác định vị trí đầu tiên của vùng rút trích trong Copy to. + OK. Cách 2: - Bôi đen 1 dòng tiêu đề sát với bảng dữ liệu. - Chọn Tab Data  Sort & Filter  Filter. + Bạn thấy xuất hiện biểu tượng lọc (mũi tên) bên phải của các cột. Muốn rút trích theo điều kiện đặt ra tại cột nào, click chuột vào mũi tên bên phải của cột đó và đánh dấu chọn các điều kiện cần trích lọc. + Sau khi chọn, các mẫu tin không thoả điều kiện bị che khuất (coi như không tồn tại trong bảng tính), tên các dòng thoả điều kiện chuyển sang màu xanh dương. + Đánh dấu vùng dữ liệu đã được lọc  Click biểu tượng Copy. + Đặt con trỏ tại vị trí đầu tiên sẽ đưa kết quả lọc dữ liệu tới  Paste. Cách 3: Thực hiện theo tiêu chuẩn công thức. - Tạo vùng điều kiện gồm 2 dòng. - 01 dòng chứa tiêu đề có tên khác với tên của các cột trong bảng dữ liệu. - 01 dòng chứa điều kiện: Gán cho dòng đầu tiên thoả điều kiện rút trích (nếu thoả mãn cho kết quả là TRUE; không thoả mãn cho kết quả là FALSE). - Các thao tác còn lại như cách 1. 9. Import & Export dữ liệu 17 LÝ THUYẾT_EXCEL
TRƯỜNG TRUNG CẤP CÔNG NGHỆ THÔNG TIN TM.COMPUTER  Import vào Excel từ file dữ liệu kiểu TXT (Chuẩn bị dữ liệu từ file .TXT) - Mở bảng tính Excel cần Import dữ liệu. - Chọn Tab Data  Trong nhóm Get External Data  Chọn From text  Chọn tập tin cần Import dữ liệu  Import (Dữ liệu Import chỉ hỗ trợ các file với định dạng *.txt, *.prn, *.csv). - Start import at row: Xác định thứ tự dòng sẽ được imiport (thông thường là dòng số 1). - Next. - Đánh dấu vào  Space : Cứ một khoảng cách sẽ được tạo thành một cột trong Excel. - Next. - Finish. - Trong cửa sổ Import Data xác định vị trí chứa dữ liệu:  Existing worksheet: Dữ liệu import sẽ hiển thị tại sheet hiện hành.  New worksheet: Dữ liệu import sẽ hiển thị trên một sheet mới.  Export dữ liệu Excel sang file có kiểu .txt - Mở bảng tính cần Export dữ liệu - Vào Tab File  Save As type: - File name: Nhập tên file (nếu cần). - Save as type chọn kiểu định dạng file: Unicode Text (*.txt) - Save  OK - Yes. 18 LÝ THUYẾT_EXCEL