Bài giảng Excel căn bản (Bản đẹp)

=1= MỤC LỤC CHƯƠNG 1. GIAO DIỆN EXCEL 2010 VÀ THAO TÁC CƠ BẢN ............................................... 4 1.1. Giới thiệu Ms Excel 2010 ................................................................................................................... 4 1.1.1. Những thuật và ý nghĩa của nó .................................................................................................... 4 1.1.2. Giao diện ...................................................................................................................................... 4 1.1.3. Sử dụng phím tắt trong Excel 2010 ............................................................................................. 5 1.1.4. Truy cập nhanh thanh Ribbon bằng bàn phím ............................................................................. 5 1.1.5. Sử dụng Shortcut Menu (thực đơn ngữ cảnh) .............................................................................. 5 1.1.6. Tùy biến thanh truy cập nhanh .................................................................................................... 6 1.1.7. Sheet Tab ..................................................................................................................................... 6 1.2. Thao tác với bảng tính......................................................................................................................... 7 1.2.1. Thao tác với Workbook ............................................................................................................... 7 1.2.2. Lưu Workbook (Save) ................................................................................................................. 8 1.2.3. Đóng Workbook (Close).............................................................................................................. 8 1.2.4. Sắp xếp các Workbook ................................................................................................................ 9 1.2.5. Thao tác với Worksheet ............................................................................................................... 9 1.2.6. Thao tác với dẫy ô ..................................................................................................................... 13 1.2.7. Kỹ thuật chọn dãy ô bằng chuột ................................................................................................ 14 2.2.8. Kỹ thuật chọn dãy ô bằng bàn phím .......................................................................................... 15 1.2.9. Chọn dãy ô bằng lệnh GoTo ...................................................................................................... 15 1.2.10. Sao chép di chuyển dãy ô ........................................................................................................ 16 1.2.11. Thêm chú thích vào ô .............................................................................................................. 16 1.2.12. Chèn thêm một ô, một hàng, một cột vào bảng tính ................................................................ 16 1.2.13. Thay đổi kích thước của dòng, cột ........................................................................................... 17 1.2.14. Ẩn / hiện dòng, cột................................................................................................................... 18 1.2.15. Ghép, trộn các ô (Merge & Center) và kẻ khung tô mầu ......................................................... 19 1.2.16. Di chuyển giữa các ô trong bảng tính ...................................................................................... 20 CHƯƠNG 2. NHẬP VÀ ĐỊNH DẠNG BẢNG TÍNH ....................................................................... 21 2.1. Nhập dữ liệu vào bảng tính ............................................................................................................... 21 2.1.1. Những điều cần biết khi nhập dữ liệu ........................................................................................ 21 2.1.2. Nhập dữ liệu vào một dãy .......................................................................................................... 21 =2= 2.2. Định dạng dữ liệu .............................................................................................................................. 23 2.2.1. Thiết lập Control panel để định dạng dữ liệu ............................................................................ 23 2.2.2. Định dạng dữ liệu cho số ........................................................................................................... 25 2.2.3. Định dạng dữ liệu ngày tháng năm và thời gian ........................................................................ 26 2.2.4. Định dạng dữ liệu Text .............................................................................................................. 27 2.2.5. Tạo chỉ số trên, chỉ số dưới ........................................................................................................ 28 2.3. Canh chỉnh trên ô .............................................................................................................................. 28 2.3.1. Ngắt dòng trong ô ...................................................................................................................... 28 2.3.2. Ép dữ liệu text cho vừa với chiều rộng ô ................................................................................... 28 2.3.3. Canh lề cho dữ liệu .................................................................................................................... 28 2.3.4. Nhập những ký tự đặc biệt ......................................................................................................... 29 CHƯƠNG 3. CÔNG THỨC VÀ HÀM TRONG EXCEL ................................................................ 31 3.1. Công thức trong Excel ...................................................................................................................... 31 3.1.1. Những điều cần biết về công thức ............................................................................................. 31 3.1.2. Các loại công thức ..................................................................................................................... 31 3.1.3. Nhập và sửa công thức............................................................................................................... 34 3.1.4. Tham chiếu địa chỉ ô trong công thức ....................................................................................... 34 3 .1.5. Làm việc với dãy tên trong công thức ...................................................................................... 35 3.1.6. Xử lý lỗi công thức .................................................................................................................... 39 3.2. Hàm trong Excel ............................................................................................................................... 39 3.2.1. Khái niệm về hàm ...................................................................................................................... 40 3.2.2. Cấu trúc một hàm ...................................................................................................................... 40 3.2.3. Các nhóm hàm trong Excel........................................................................................................ 40 3.2.4. Nhập hàm vào trong công thức .................................................................................................. 41 3.3. Một số nhóm hàm thông dụng trong Excel ....................................................................................... 42 3.3.1. Nhóm hàm toán học (Match Functions) .................................................................................... 42 3.3.2. Nhóm hàm xử lý văn bản (Text Functions) ............................................................................... 47 3.3.4. Nhóm hàm thống kê (Statistical Functions) .............................................................................. 55 3.4.5. Nhóm hàm ngày tháng và thời gian (Date & Time Functions) ................................................. 58 3.3.6. Nhóm hàm dò tìm (Lookup Functions) ..................................................................................... 63 CHƯƠNG 4. BIỂU ĐỒ VÀ IN ẤN ..................................................................................................... 74 4.1. Các thuật ngữ trên biểu đồ ................................................................................................................ 74 =3= 4.1.1. Khái niệm .................................................................................................................................. 74 4.1.2.Ý nghĩa các loại biểu đồ trong Excel .......................................................................................... 74 4.1.3. Khối dữ liệu để vẽ biểu đồ ......................................................................................................... 76 4.1.4. Các thành phần thông dụng trên biểu đồ ................................................................................... 76 4.1.5. Cách tạo biểu đồ trong Excel ..................................................................................................... 77 4.1.6. Hiệu chỉnh và định dạng đồ thị .................................................................................................. 82 4.2. Định dạng trang bảng tính và in ấn ................................................................................................... 89 4.2.1. Định dạng trang bảng tính ......................................................................................................... 89 4.2.2. In ấn trong Excel ........................................................................................................................ 91 CHƯƠNG 5. CƠ SỞ DỮ LIỆU EXCEL ............................................................................................ 94 5.1. Tìm hiểu về cơ sở dữ liệu Excel........................................................................................................ 94 5.1.1. Khái niệm về cơ sở dữ liệu Excel .............................................................................................. 94 5.1.2. Tạo lập một cơ sở dữ liệu trên Excel ......................................................................................... 94 5.1.3. Nhập dữ liệu vào cơ sở dữ liệu .................................................................................................. 95 5.2. Thực hiện các chức năng cơ bản trên CSDL Excel .......................................................................... 97 5.2.1. Sắp xếp các dòng CSDL ............................................................................................................ 97 5.2.2. Lọc dữ liệu ............................................................................................................................... 100 5.2.3. Tổng hợp dữ liệu Subtotals ...................................................................................................... 108 5.2.4. Tổng kết với bảng trọng tâm Pivot Table ................................................................................ 111 5.3. Thực hiện các chức năng nâng cao trên CSDL Excel ..................................................................... 113 5.3.1. Định dạng bảng - Format as Table ......................................................................................... 113 5.3.2. Kiểm tra dữ liệu – Data Validation (DV) ................................................................................ 115 5.3.3. Tổng hợp dữ liệu từ nhiều bảng - Consolidate ........................................................................ 122 5.3.4. Định dạng có điều kiện - Conditional Formatting ................................................................. 124 5.4. Một số hàm trong cơ sở dữ liệu ...................................................................................................... 137 5.4.1. Cấu trúc chung của hàm cơ sở dữ liệu. .................................................................................... 137 5.4.2. Các hàm cơ sở dữ liệu thông dụng. ......................................................................................... 138 TÀI LIỆU THAM KHẢO ................................................................................................................. 141 =4= CHƯƠNG 1. GIAO DIỆN EXCEL 2010 VÀ THAO TÁC CƠ BẢN 1.1. Giới thiệu Ms Excel 2010 1.1.1. Những thuật và ý nghĩa của nó • Workbook: Đây là một tập tin để làm các công việc như: tính toán, vẽ đồ thị, ...và lưu trữ dữ liệu. Một workbook có thể chứa nhiều Worksheet, khi ghi tệp tin vào bộ nhớ ngoài tệp tin có định dạng .xlsx • Worksheet: Còn được gọi là Sheet, là nơi lưu trữ và làm việc với dữ liệu. Một worksheet được lưu trong workbook. Trong Excel 2010, một worksheet chứa được 16,384 cột và 1,048,576 dòng, giao giữa dòng và cột gọi là ô. • Chart sheet: Thực chất là một Sheet trong workbook, nhưng nó chỉ chứa một đồ thị. Vì vậy, nếu chỉ muốn xem từng đồ thị riêng lẻ thì Chart Sheet là lựa chọn tối ưu. • Sheet Tab: Tên của các Sheet sẽ thể hiện trên các thẻ đặt ở góc bên trái phía dưới của cửa sổ workbook. Excel cho phép di chuyển từ Sheet này sang Sheet khác bằng thao tác đơn giản là nhấp chuột vào tên Sheet cần đến trong Sheet Tab. 1.1.2. Giao diện Hệ thống Ribbon Thay cho các thanh Menu và Toolbar truyền thống là các nhóm nút lệnh được trình bày theo chiều ngay phía trên màn hình, giúp người dùng sử dụng thuận tiện hơn, đó chính là Ribbon. Hệ thống Ribbon của Excel 2010 Bộ công cụ Ribbon có các Tab: Home, Insert, Page Layout, Formulas, Data, Reviews, View, Developer, và Add-Ins làm cho giao diện trở lên trực quan, dễ nhìn, rõ ràng và dễ sử dụng hơn. Các nút lệnh được tập hợp lại theo nhóm chức năng, hầu như không thiếu một lệnh nào (bởi vậy nên Excel 2010 không cần có menu nữa). Ẩn / hiện hệ thống Ribbon =5= Để tiết kiệm không gian làm việc, Excel cho phép ẩn các Ribbon, chỉ chừa lại các tên nhóm. Khi đó, các nhóm lệnh chỉ hiện ra khi nhấp chuột vào tên nhóm. Hệ thống Ribbon đã được ẩn đi Để ẩn / hiện Ribbon, có thể thực hiện một trong ba thao tác sau:  Double-click vào bất kỳ tên Tab nào trên Ribbon.  Nhấn tổ hợp phím Ctrl+F1.  Click vào Quick Access Toolbar rồi chọn Minimize the Ribbon. 1.1.3. Sử dụng phím tắt trong Excel 2010 Nếu là người đã sử dụng Excel các phiên bản trước đó, chắc hẳn người sử dụng sẽ thuộc khá nhiều tổ hợp phím tắt của Excel như nhấn Alt+T+O để mở hộp thoại Options, Alt+D+S sẽ mở hộp thoại Sort, v.v... Phiên bản Excel 2010 vẫn sử dụng những tổ hợp phím tắt đã có ở những phiên bản trước. 1.1.4. Truy cập nhanh thanh Ribbon bằng bàn phím Trong Excel 2010, cho truy cập Ribbon bằng bàn phím bằng cách nhấn phím Alt khi đó trên thanh Ribbon xuất hiện những ký tự như ở hình dưới đây: Nhấn Alt để truy cập nhanh Ribbon bằng bàn phím Những chữ cái vừa xuất hiện đó đại diện cho tên của các Tab trên Ribbon. Cần truy cập Tab nào, chỉ cần gõ chữ cái tương ứng hoặc có thể dùng các phím mũi tên để di chuyển qua lại giữa các Tab. Giả sử, nhấn phím H để gọi Tab Home, Ribbon sẽ ngay lập tức cho biết thêm các phím tắt của từng nhóm nút lệnh trong Tab Home Nếu muốn trở lại vùng làm việc trên bảng tính nhấn Esc hoặc nhấn Alt một lần 1.1.5. Sử dụng Shortcut Menu (thực đơn ngữ cảnh) =6= Khi Right-click vào một ô, hoặc một nhóm các ô, hoặc một đối tượng nào đó trên bảng tính, Excel 2010 sẽ xuất hiện một Shortcut menu ngay tại vị trí con chuột , chứa các lệnh thông dụng mà Excel nghĩ rằng bạn có thể áp dụng cho đối tượng mà bạn chọn. Tuy nhiên, ở Excel 2010, ngoài những lệnh thông thường như đã có ở Excel 2003, Shortcut menu này còn có thêm nhóm nút lệnh dùng để định dạng font chữ, định dạng kiểu dữ liệu và định dạng ô bảng tính, giúp bạn tiết kiệm được khá nhiều thời gian khi làm việc với Excel. 1.1.6. Tùy biến thanh truy cập nhanh Excel 2010 cũng cho phép bạn đưa những nút lệnh thường hay sử dụng nhất lên một chỗ gọi là Quick Access Toolbar, nằm ở phía trên bên trái của cửa sổ chương trình Mặc định ban đầu, ở đây chỉ có 3 nút lệnh là Save, Undo và Redo. Để thêm các lệnh khác. Bạn nhấn vào nút mũi tên xổ xuống ở bên phải thanh Quick Access Toolbar. Trong cửa sổ mở ra có sẵn một số lệnh (New, Open...), muốn thêm nút lệnh nào vào thì nhấn chọn lệnh đó 1.1.7. Sheet Tab Sheet Tab là nhóm phím dùng để di chuyển qua lại giữa các Sheet nằm ở góc dưới bên trái, ở ngoài cùng bên phải của Sheet Tab có thêm nút Insert Worksheet, dùng để chèn một Sheet mới vào trong bảng tính. =7= 1.2. Thao tác với bảng tính 1.2.1. Thao tác với Workbook Tạo Workbook mới Có nhiều cách để tạo một workbook mới áp dụng cho bất kỳ phiên bản Excel nào, là nhấn tổ hợp phím Ctrl+N để tạo ra workbook trắng Nếu như bạn muốn tạo một workbook mới dựa theo những khuôn mẫu có sẵn (template), thì: Nhấn Tab File \New, hộp thoại mở ra chọn Installed Templates rồi lựa một trong những templates có sẵn đã được cài vào máy khi bạn cài đặt Excel 2010. Bạn cũng có thể tạo mới một workbook dựa theo cấu trúc của một workbook đã có sẵn trên máy, bằng cách nhấn vào mục New from existing... Mở Workbook đã có sẵn (Open) Có nhiều cách để mở một workbook có sẵn. Song cách dễ nhất và có thể áp dụng cho bất kỳ phiên bản Excel nào, là nhấn tổ hợp phím Ctrl+O. Sau đó bạn chọn workbook cần mở trong hộp thoại Open rồi nhấn nút Open. Hoặc, bạn nhấn Tab File \ Open, một hộp thoại sẽ mở ra giống như khi bạn nhấn Ctrl+O. Sau đó bạn chọn workbook cần mở trong hộp thoại Open rồi nhấn nút Open. Excel 2010 sẽ lưu lại tên của những workbook được mở ra gần nhất trong khung Recent Documents của Tab File. Tuy nhiên, Excel 2010 có thể lưu lại đến 50 workbook được mở ra gần nhất, trong khi Excel 2003 thì chỉ có 9. =8= 1.2.2. Lưu Workbook (Save) Có nhiều cách để lưu một workbook, nhưng nhanh nhất và thích hợp với bất kỳ phiên bản Excel nào, vẫn là tổ hợp phím Ctrl+S hoặc Shift+F12. Với riêng Excel 2010, thì bạn nhấn Tab File \Save. Trong trường hợp muốn lưu workbook với một tên khác, với mọi phiên bản của Excel, bạn chỉ cần nhấn F12. Excel 2010 cung cấp một cơ chế sao lưu tự động trong một khoảng thời gian nhất định (mặc định là 10 phút/lần) để bảo toàn dữ liệu, phòng khi người sử dụng không ghi hoặc mất điện đột xuất, bạn có thể chỉnh lại thời gian mặc định mày Nhấn Tab File \ Excel Options \ Save, bạn chỉ cần đánh dấu vào tuỳ chọn Save AutoRecover Information Every rồi chỉnh lại thời gian sao lưu tự động theo ý muốn Tùy chỉnh thời gian Auto Save trong Option của Excel 2010 Bảo mật khi Save Để bảo mật cho workbook, bạn có thể gán mật mã bảo vệ, và chỉ có ai biết mật mã mới mở được. Để gán mật mã cho workbook, bạn làm như sau: 1. Nhấn Tab File \ Save As, hộp thoại Save As hiện ra. 2. Nhấn nút Tools \ General Options..., hộp thoại General Options hiện ra. 3. Nhập mật mã mở (Password to open) và mật mã hiệu chỉnh (Password to modify) cho workbook. Hai mật mã này nên khác nhau để tăng bảo mật. Sau đó nhấn nút OK. 4. Xác nhận lại mật mã mở workbook. Sau đó nhấn nút OK. 5. Nhấn nút Save để hoàn tất. 1.2.3. Đóng Workbook (Close) Có nhiều cách để đóng một workbook. Cách dễ nhất và có thể áp dụng cho bất kỳ phiên bản Excel nào, là nhấn tổ hợp phím Ctrl+W, hoặc Alt+F4 =9= Hoặc, nhấn Tab File \ Close với Excel 2010. 1.2.4. Sắp xếp các Workbook Excel 2010 cung cấp khả năng sắp xếp các workbook được mở cùng lúc. Nhờ vậy, việc tham khảo qua lại giữa các các workbook thuận tiện hơn. Cách làm chọn View \ Windows \ Arrange All , tiếp theo chọn cho mì