Giáo trình Tin học ứng dụng - Microsoft Excel 2007 - Thanh Tu

Giỏo trỡnh tin học ứng dụng – Microsoft Excel 2007 Biờn soạn: thanhtusydney@gmail.com MỤC LỤC CHƢƠNG I: TỔNG QUAN VỀ MICROSOFT EXCEL ......................................... - 1 - 1.1 GIỚI THIỆU VỀ MICROSOFT EXCEL ..................................................... - 1 - 1.1.1 Giới thiệu Microsoft Excel ....................................................................... - 1 - 1.1.2 Cài đặt Microsoft Excel............................................................................ - 1 - 1.1.3 Khởi động: ................................................................................................ - 3 - 1.1.4 Thoỏt chƣơng trỡnh ................................................................................... - 3 - 1.1.5 Giới thiệu mà hỡnh làm việc Microsoft Excel .......................................... - 4 - 1.1.5.1 Cửa sổ làm việc ................................................................................. - 4 - 1.1.5.2 Chức năng cỏc thực đơn Menu ......................................................... - 4 - 1.2 CẤU TRÚC BẢNG TÍNH ........................................................................... - 5 - 1.2.1. Workbook ................................................................................................ - 5 - 1.2.2 Worksheet ................................................................................................. - 5 - 1.2.3 Tạo mới một tài liệu Workbook. .............................................................. - 5 - 1.2.4 Lƣu một tài liệu. ....................................................................................... - 5 - 1.2.5 Mở một Workbook đó lƣu trong ổ đĩa . ................................................... - 6 - 1.2.6 Kiểu dữ liệu trong bảng tớnh .................................................................... - 6 - 1.3 CÁC THAO TÁC CƠ BẢN TRONG BẢNG TÍNH. .................................. - 6 - 1.3.1 Di chuyển con trỏ trong bảng tớnh............................................................ - 6 - 1.3.2 Cỏc toỏn tử trong Excel. ........................................................................... - 7 - 1.3.3 Cỏc thao tỏc với dữ liệu. ........................................................................... - 7 - 1.3.3.1 Thao tỏc chọn vựng dữ liệu. .............................................................. - 7 - 1.3.3.2 Sao chộp dữ liệu. ............................................................................... - 7 - 1.3.3.3 Di chuyển dữ liệu. ............................................................................. - 7 - 1.3.3.4 Dỏn dữ liệu. ....................................................................................... - 8 - 1.3.3.5 Xoỏ vựng dữ liệu được chọn. ............................................................ - 8 - 1.3.3.6 Chốn thờm Rows (dũng), Columns (cột), Cell (ụ) ............................. - 8 - 1.3.3.7 Xoỏ dũng, cột, ụ................................................................................. - 9 - 1.4 CễNG CỤ ĐỊNH DẠNG DỮ LIỆU BẢNG TÍNH. ..................................... - 9 - Giỏo trỡnh tin học ứng dụng – Microsoft Excel 2007 Biờn soạn: thanhtusydney@gmail.com 1.4.1 Sử dụng hộp thoại Format Cell ............................................................... - 9 - 1.4.1.1 Thẻ Number: ..................................................................................... - 9 - 1.4.1.2 Thẻ Alignment: ............................................................................... - 10 - 1.4.1.3 Thẻ Font ......................................................................................... - 11 - 1.4.1.4 Thẻ Border ...................................................................................... - 11 - 1.4.1.5 Thẻ Fill ........................................................................................... - 11 - 1.4.1.6 Thẻ Protection: Bảo vệ dữ liệu. .................................................... - 11 - 1.4.2 Sử dụng thẻ định dạng Home ............................................................... - 11 - 1.5 ĐỊNH DẠNG BẢNG TÍNH ........................................................................ - 12 - 1.5.1 Thay đổi độ rộng của cột và chiều cao của dũng. ................................. - 12 - 1.5.1.1 Độ rộng cột. .................................................................................... - 12 - 1.5.1.2 Chiều cao của dũng. ....................................................................... - 12 - 1.5.1.3 Định dạng ụ dữ liệu(Cell). ............................................................ - 12 - 1.5.3 Xoay dữ liệu. ......................................................................................... - 14 - 1.5.4 Cố định hàng, cột. .................................................................................. - 14 - CHƢƠNG II: SỬ DỤNG CễNG THỨC VÀ HÀM TRONG EXCEL. ............... - 15 - 2.1 ĐỊA CHỈ TRONG EXCEL ........................................................................ - 15 - 2.1.1 Khỏi niệm địa chỉ .................................................................................. - 15 - 2.1.2 Địa chỉ tƣơng đối ................................................................................... - 15 - 2.1.3 Địa chỉ tuyệt đối .................................................................................... - 15 - 2.1.4 Địa chỉ hỗn hợp: .................................................................................... - 15 - 2.2 CễNG THỨC VÀ HÀM TRONG EXCEL. ............................................. - 16 - 2.2.1 Khỏi niệm cụng thức và hàm. ................................................................ - 16 - 2.2.2 Cỏc nhúm hàm trong Excel: .................................................................. - 16 - 2.2.2.1 Nhúm hàm toỏn học: ...................................................................... - 16 - 2.2.2.2 Nhúm hàm thống kờ. ....................................................................... - 17 - 2.2.2.3 Nhúm hàm sử lý chuỗi ký tự. .......................................................... - 19 - 2.2.2.4 Nhúm hàm thời gian. ...................................................................... - 22 - 2.2.2.5. Nhúm hàm tỡm kiếm. ...................................................................... - 24 - 2.2.2.6. Nhúm hàm Logic. .......................................................................... - 27 - Giỏo trỡnh tin học ứng dụng – Microsoft Excel 2007 Biờn soạn: thanhtusydney@gmail.com 2.2.2.7. Nhúm hàm tỏi chớnh (tham khảo) ..................................................- 28 - 2.2.2.8 Nhúm hàm cơ sở dữ liệu ..................................................................- 33 - CHƢƠNG III : KHAI THÁC VÀ QUẢN Lí CƠ SỞ DỮ LIỆU ..........................- 38 - 3.1 GIỚI THIỆU..................................................................................................- 38 - 3.1.1 Khỏi niệm dữ liệu (Data) ........................................................................- 38 - 3.1.2 Khỏi niệm cơ sở dữ liệu (Database) .......................................................- 38 - 3.1.3 Vựng dữ liệu (Database) ........................................................................- 38 - 3.1.4 Vựng tiờu chuẩn (Criteria) .....................................................................- 39 - 3.2 MỘT SỐ LỆNH SỬ DỤNG TRONG CƠ SỞ DỮ LIỆU. .........................- 39 - 3.2.1 Sử dụng hộp thoại Data Form ................................................................- 39 - 3.2.1.1 Khỏi niệm và khởi động thanh cụng cụ Data form .........................- 39 - 3.2.1.2 Sử dụng cụng cụ Data Form ...........................................................- 40 - 3.2.2.2 Cỏc bước xắp xếp dữ liệu ................................................................- 41 - 3.2.2 Lọc dữ liệu ..................................................................................................- 42 - 3.2.2.1 Lọc bằng chế độ Auto Filter. ...............................................................- 42 - 3.2.2.2 Lọc bằng Advanced Filter. ..................................................................- 43 - 3.2.2.3 Thống kờ theo nhúm (Subtotal) .......................................................- 44 - 3.2.2.4 Chức năng Consolidate ...................................................................- 45 - 3.2.2.5 Chức năng Pivottable ......................................................................- 46 - 3.2.2.6 Chức năng Validation .....................................................................- 47 - 3.2.2.7 Chức năng Conditional Formatting ................................................- 49 - CHƢƠNG IV: ĐỒ THỊ ...........................................................................................- 51 - 4.1 GIỚI THIỆU BIỂU ĐỒ ................................................................................- 51 - 4.1.1 Vẽ biểu đồ ..............................................................................................- 51 - 4.1.2 Hiệu chỉnh biểu đồ. ................................................................................- 52 - 4.1.3 Cỏc thao tỏc với biểu đồ. ........................................................................- 52 - CHƢƠNG V: IN ẤN VÀ BẢO VỆ DỮ LIỆU .......................................................- 54 - 5.1 Cài đặt trang in: .........................................................................................- 54 - 5.2 Định dạng Header and Footer. ..................................................................- 55 - 5.3 In ấn dữ liệu. ..............................................................................................- 56 - Giỏo trỡnh tin học ứng dụng – Microsoft Excel 2007 Biờn soạn: thanhtusydney@gmail.com CHƢƠNG VI. XÂY DỰNG BÀI TOÁN QUẢN Lí VẬT TƢ ........................... - 58 - CHƢƠNG VII: PHẦN BÀI TẬP TỰ LÀM .......................................................... - 69 - Giỏo trỡnh tin học ứng dụng – Microsoft Excel 2007 Page | - 1 - Biờn soạn: thanhtusydney@gmail.com CHƢƠNG I: TỔNG QUAN VỀ MICROSOFT EXCEL 1.1 GIỚI THIỆU VỀ MICROSOFT EXCEL 1.1.1 Giới thiệu Microsoft Excel Microsoft Excel là một bộ phận trong bộ phần mềm của Microsoft Office, là phần mềm bảng tính phục vụ công việc quản lý. L-u trữ ở mức độ nhỏ và trung bình rất tiện lợi cho công việc của các bạn. Excel tạo và quản lý sổ sách điện tử với dao diện thân thiện, dễ làm việc. Mỗi trang làm việc là các bảng đã đ-ợc kẻ sẵn với khả năng tính toán và l-u chữ cho phép. + Thực hiện đ-ợc các phép tính từ đơn giản đến phức tạp, có sự hỗ trợ của các hàm tính toán từ cơ bản dến nâng cao. + Tổ trức và l-u giữ thông tin d-ới dạng bảng nh-: bảng l-ơng, bảng kế toán, bảng thanh toán, bảng xuất nhập hàng + khi có sự thay đổi, cập nhập dữ liệu thì bảng tính sẽ tự động tính không cần phải tính toán lại. + Sử dụng bảng tính tạo ra các báo cáo tổng hợp có kèm theo các biểu đồ, đồ thị và các hình ảnh minh hoạ + Ngoài ra Excel còn có nhiều các tính năng -u việt khác. - Với các tính năng mạnh mẽ của Excel nh- vậy sẽ cho phép bạn giải quyết đ-ợc những công việc quản lý và l-u trữ. Đặc biệt nó không thể thiếu cho công việc ở các văn phòng trong cơ quan, các doanh nghiệp vừa và nhỏ đến các công ty lớn. 1.1.2 Cài đặt Microsoft Excel - Chọn mở ổ đĩa chứa bộ cài Microsoft Office Click double trỏi chuột vào biểu tƣợng “Setup” trong thƣ mục cài đặt Mở CD key sử dụng cài đặt Giỏo trỡnh tin học ứng dụng – Microsoft Excel 2007 Page | - 2 - Biờn soạn: thanhtusydney@gmail.com - Cửa sổ cài đặt chƣơng trỡnh xuất hiện, nhập Cdkey vào cửa sổ cài đặt từ file Cdkey. - Nhấn Continue (2) để tiếp tục cài đặt chƣơng trỡnh. - Chọn Install now => cài đặt mặc định của chƣơng trỡnh - Chọn Customize (3) => tựy chọn cài đặt theo ngƣời sử dụng: + Installation options : tựy chọn cỏc ứng dụng cài đặt + File Location: chọn đƣờng dẫn cho thƣ mục cài đặt(mặc định C:\Program file\ ) + User Information : thụng tin ngƣời cài đặt. - Nhấn Install Now (4) tiếp tục quỏ trỡnh cài đặt => Vui lũng chờ phần mềm cài đặt trong một vài phỳt. 1 2 3 4 Giỏo trỡnh tin học ứng dụng – Microsoft Excel 2007 Page | - 3 - Biờn soạn: thanhtusydney@gmail.com - Nhấn “Close” hoàn thành quỏ trỡnh cài đặt. 1.1.3 Khởi động: - Cỏch 1: Chọn Start/ Program/ Microsoft Office/ Microsoft office Excel 2007. - Cỏch 2: Click double trỏi chuột vào biểu tƣợng Microsoft Excel 2007 trờn màn hỡnh (Desktop). 1.1.4 Thoỏt chƣơng trỡnh - Cỏch 1: Click Office Button/ Chọn Exit Excel. - Cỏch 2: Click vào Office Button / Chọn Close hoặc sử dụng Ctrl + W. - Cỏch 3: Sử dụng tổ hợp phớm Alt + F4. - Cỏch 4: Click vào phớa trờn, gúc phải cửa sổ làm việc. 1 2 3 4 Giỏo trỡnh tin học ứng dụng – Microsoft Excel 2007 Page | - 4 - Biờn soạn: thanhtusydney@gmail.com - Chỳ ý khi đúng cửa sổ làm việc nếu bạn chƣa lƣu sẽ xuất hiện hộp thoại hỏi: nếu bạn muốn lƣu thỡ chọn (Yes), nếu bạn khụng muốn lƣu thỡ chọn (No), chọn (Cancel) để huỷ bỏ thao tỏc. 1.1.5 Giới thiệu mà hỡnh làm việc Microsoft Excel 1.1.5.1 Cửa sổ làm việc 1.1.5.2 Chức năng cỏc thực đơn Menu - Home: Chứa cỏc cụng cụ định dạng, cỏc nhúm lệnh đƣợc sử dụng thƣờng xuyờn trong quỏ trỡnh làm việc nhƣ: Font, Paragraph, Style, Editing - Insert: Chứa cỏc cụng cụ chốn vào bảng tớnh nhƣ: Picture, Chart, WordArt, Symbol, Clip Art ... - Page Layout: Chứa cỏc lệnh thiết lập, cài đặt cho trang bảng tớnh, cài đặt trang in dữ liệu Giỏo trỡnh tin học ứng dụng – Microsoft Excel 2007 Page | - 5 - Biờn soạn: thanhtusydney@gmail.com - Formulas: Là nơi cung cấp cỏc nhúm hàm, cỏc cụng thức. Cung cấp cỏc cụng cụ đặt tờn bảng tớnh. - Data: Cho phộp liờn kết với cỏc đối tƣợng khỏc nhƣ: From (Access, Web, Text..), Chứa cỏc lệnh làm việc với cơ sở dữ liệu (CSDL). - Review: Chức năng kiểm tra chớnh tả (Spelling), chức năng bảo vệ dữ liệu (Protect Sheet, Protect Workbook). - View: Cỏc chế độ hiển thị cửa sổ làm việc: Gridlines, Zoom, Window - Developer: Cỏc thiết kế mở rộng: Macros, Visual basic... 1.2 CẤU TRÚC BẢNG TÍNH 1.2.1. Workbook - Workbook: Là một tập tin làm việc nhƣ: tớnh toỏn, lƣu giữ tài liệu., một Workbook chứa nhiều Worksheet nờn cú thể tổ trức lƣu chữ nhiều loại thụng tin khỏc nhau. 1.2.2 Worksheet - WorkSheet: Cũn gọi là “Sheet” hay “bảng tớnh” là nơi lƣu giữ và làm việc với dữ liệu. Một Worksheet cú nhiều ụ đƣợc tạo bởi cỏc dũng và cỏc cột. Một Worksheet bao gồm: cú 256 cột và 65536 dũng ( với Excel 2003), cú 16384cột và 1048576 dũng ( với Excel 2007). Giao điểm giữa dũng và cột là gọi là cell (ụ), mỗi một cell đƣợc xem nhƣ là một bảng tớnh điện tử. 1.2.3 Tạo mới một tài liệu Workbook. - Cỏch 1: Vào Office Button / Chọn New -> Chọn Create. - Cỏch 2: Click vào biểu tƣợng trờn thanh Quick access toolbar. - Cỏch 3: Sử dụng tổ hợp phớm Ctrl + N. 1.2.4 Lƣu một tài liệu. - Cỏch 1: Vào Officce Button / Save as.. (hoặc nhấn phớm F12) -> tại mục Save in -> chọn đƣờng dẫn đến ổ đĩa -> Chọn thƣ mục lƣu tệp tin -> Gừ tờn file lƣu trong mục File name -> Click chọn Save để lƣu. - Cỏch 2: Chọn Office Button / chọn Save (hoặc sử dụng tổ hợp phớm Ctrl + S) Giỏo trỡnh tin học ứng dụng – Microsoft Excel 2007 Page | - 6 - Biờn soạn: thanhtusydney@gmail.com - Cỏch 3: Click vào biểu tƣợng đĩa mềm trờn thanh Quick access toolbar. 1.2.5 Mở một Workbook đó lƣu trong ổ đĩa . - Cỏch 1: Vào Office Button / chọn Open/ đƣờng dẫn/ tờn file cần mở/ Open. - Cỏch 2: Sử dụng tổ hợp phớm Ctrl + O. - Cỏch 3: Click biểu tƣợng Open trờn thanh Quick access tollbar. 1.2.6 Kiểu dữ liệu trong bảng tớnh  Trong Excel cú cỏc kiểu dữ liệu sau: + Kiểu số(number): khi nhập nhập dữ liệu số vào cell -> số đƣợc canh sang phải. + Kiểu chữ (text): khi nhập nhập dữ liệu vào cell -> dữ liệu đƣợc canh sang trỏi. + Kiểu ngày (date): khi nhập dữ liệu vào cell -> dữ liệu đƣợc canh sang phải. * Chỳ ý: - Khi nhập kiểu ngày phải chỳ ý là mỏy tớnh của bạn đang đặt hệ ngày là: mm/dd/yyyy hay là: dd/mm/yyyy. - Khi nhập số mà canh sang trỏi thỡ phải xoỏ bỏ định dạng hoặc định dạng lại. 1.3 CÁC THAO TÁC CƠ BẢN TRONG BẢNG TÍNH. 1.3.1 Di chuyển con trỏ trong bảng tớnh. - Di chuyển bằng con trỏ, bằng cỏc phớm:  , , , . - Di chuyển tới dũng đầu tiờn (Ctrl + ). - Xuống dũng cuối cựng (Ctrl+ ). - Chở về cột đầu tiờn (Ctrl + ). - Tới cột cuối cựng (Ctrl + ). Tờn file mở Giỏo trỡnh tin học ứng dụng – Microsoft Excel 2007 Page | - 7 - Biờn soạn: thanhtusydney@gmail.com - Xuống một ụ: (Enter), sang trỏi (Tab), sang phải: (Shift + Tab), chở về cột A (Home), chở về A1 (Ctrl + Home), ...vvv. 1.3.2 Cỏc toỏn tử trong Excel. - Cỏc toỏn tử cơ bản trong Excel bao gồm: Phộp cộng: + Phộp trừ: - Phộp nhõn: * Luỹ thừa: ^ Phộp: > Phộp: Phộp chia: / Nối chuỗi: & Phần trăm: % Phộp: >= Phộp: <= 1.3.3 Cỏc thao tỏc với dữ liệu. 1.3.3.1 Thao tỏc chọn vựng dữ liệu. - Sử dụng con trỏ chuột nhấp trỏi chuột kộo và nhả chuột. - Sử dụng tổ hợp phớm Ctrl + A để bụi đen lựa chọn toàn bộ bảng tớnh. - Sử dụng bàn phớm phớm , , , , kết hợp với phớm Shift để lựa chọn vựng dữ liệu làm việc. - Sử dụng tổ hợp phớm (Ctrl + A): để bụi đen toàn bộ khối văn bản. - Sử dụng (Ctrl + Shift + ): bụi đen từ vị trớ con trỏ về cột đầu tiờn. - Sử dụng (Ctrl + Shift + ): bụi đen từ vị trớ con trỏ về dũng đầu tiờn. - Sử dụng (Ctrl + Shift + ): bụi đen từ vị trớ con trỏ về dũng cuối cựng. - Sử dụng (Ctrl + Shift + ): bụi đen từ vị trớ con trỏ về cột cuối cựng. 1.3.3.2 Sao chộp dữ liệu. - Cỏch 1: Chọn vựng dừ liệu cần sao chộp -> chọn thẻ Home / chọn Copy. Hoặc (Click phải chuột chọn lệnh Copy) - Cỏch 2: Chọn bụi đen dữ liệu cần sao chộp -> sử dụng tổ hợp phớm Ctrl + C. 1.3.3.3 Di chuyển dữ liệu. - Cỏch 1: Chọn bụi đen phần dừ liệu cần di chuyển -> chọn thẻ Home/ Chọn Cut. Hoặc (click phải chuột chọn lệnh Cut). - Cỏch 2: Chọn bụi đen dữ liệu cần di chuyển -> Sử dụng Ctrl + X. Giỏo trỡnh tin học ứng dụng – Microsoft Excel 2007 Page | - 8 - Biờn soạn: thanhtusydney@gmail.com 1.3.3.4 Dỏn dữ liệu. - Chọn nơi dỏn dữ liệu: Chọn thẻ Home/ chọn Paste. Hoặc (Click phải chuột chọn lệnh Paste). - Sử dụng tổ hợp phớm Ctrl + V. 1.3.3.5 Xoỏ vựng dữ liệu được chọn. - Chọn vựng dữ liệu xoỏ -> nhấn chọn Delete trờn bàn phớm. Hoặc: + Chọn thẻ Home/ Clear Contents xoỏ dữ liệu cũn định dạng giữ nguyờn. + Chọn thẻ Home/ Clear Comments xoỏ chỳ thớch. + Chọn thẻ Home/ Clear Formats xoỏ địng dạng cũn giữ nguyờn dữ liệu. + Chọn thẻ Home / Clear All xoỏ cả định dạng và dữ liệu. 1.3.3.6 Chốn thờm Rows (dũng), Columns (cột), Cell (ụ) a, Chốn dũng - Trờn thanh Ribbon/ chọn thẻ Home / chọn Insert/ chọn Insert Sheet Rows (dũng sẽ đƣợc chốn về phớa trờn vị trớ đặt con trỏ). b, Chốn cột - Trờn thanh Ribbon / chọn thẻ Home / chọn Insert/ chọn Insert Sheet Columns (cột sẽ đƣợc chốn về phớa trỏi vị trớ con trỏ). c, Chốn ụ - Trờn thanh Ribbon/ chọn thẻ Home / chọn Insert/ chọn Insert cells. - Shift cells Right: Chốn một ụ về phớa phải vị trớ con trỏ chuột. - Shift cells down: Chốn một ụ về phớa dƣới vị trớ con trỏ chuột. - Entire row: chốn thờm một dũng về trờn vị trớ con trỏ chuột. - Entire column: chốn một cột về phớa trỏi vị trớ con trỏ chuột. Giỏo trỡnh tin học ứng dụng – Microsoft Excel 2007 Page | - 9 - Biờn soạn: thanhtusydney@gmail.com 1.3.3.7 Xoỏ dũng, cột, ụ.  Trờn thanh Ribbon/ chọn thẻ Home / chọn Delete / chọn Delete Cells, chọn: + Shift cells left: xoỏ một ụ về phớa trỏi. + Shift cell up: xoỏ một ụ ở phớa trờn. + Entire row: xoỏ một dũng. + Entire column: xoỏ một cột. 1.4 CễNG CỤ ĐỊNH DẠNG DỮ LIỆU BẢNG TÍNH. 1.4.1 Sử dụng hộp thoại Format Cell - Chọn vựng dữ liệu cần định dạn