Tự học Excel

75 trang phuongnguyen 10280

Download

Bạn đang xem 20 trang mẫu của tài liệu "Tự học Excel", để 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:

tu_hoc_excel.pdf

Nội dung text: Tự học Excel

Khoa CNTT - VĐH Mở Excel 1 I. khởi động và màn hình Excel 1. Khởi động Sau khi khởi động WINdows làm theo một trong các cách sau - Nháy chuột vào biểu t−ợng Microsoft Excel ở góc trên bên phải màn hình. - Nếu dùng Win 3.1x, trong cửa sổ Microsoft Office đã mở chọn biểu t−ợng Microsoft Excel rồi nháy đúp chuột. - Nếu dùng Win 95, nháy vào Start / Program / Microsoft Excel 2. Màn hình a - Các thành phần của màn hình Excel Màn hình của Excel là một cửa sổ đã đ−ợc phóng to và trông gần giống màn hình của Word, bao gồm các thành phần sau : - Thanh tiêu đề (Title bar) : ở dòng trên cùng của màn hình, khi mới khởi động Excel tại đây ghi Microsoft Excel - Book1, khi ta đặt tên cho bảng tính, tên này kèm theo phần mở rộng . XLS sẽ thay thế từ Book1. - Các thanh Menu, Công cụ, Định dạng giống nh− của Word. Phần lớn các biểu t−ợng trên các thanh này có công dụng ý nghĩa nh− trong Word, ý nghĩa của một số biểu t−ợng dùng riêng cho Excel đ−ợc ghi trên trang 3. - Thanh Công thức (Formula Bar) : là dòng thứ năm của màn hình hiển thị toạ độ (địa chỉ hoặc tên) ô, nút huỷ bỏ :, nút lựa chọn ;, nội dung dữ liệu trong ô hiện tại (ô có khung viền chung quanh) - Thanh Trạng thái (Status bar) : là dòng cuối cùng hiển thị các chế độ hoạt động của Excel : Ready : Đang sẵn sàng làm việc. Enter : Đang nhập dữ liệu hay công thức. Pointer : Đang ghi công thức tham chiếu đến một địa chỉ. Edit : Đang điều chỉnh dữ liệu hay công thức trong ô hiện tại - Thanh thẻ tên bảng tính (Sheet tabs) : là dòng ngay trên thanh Trạng thái, hiển thị tên của các bảng tính (khi chúng ch−a đ−ợc đặt tên, tại đây ghi Sheet1, Sheet2, , Sheet16). Bên trái là các nút chuyển tới các bảng tính.
Khoa CNTT - VĐH Mở Excel 2 - Thanh cuộn Dọc (Vertical Scroll Bar), cuộn Ngang (Horizontal Scroll Bar) giống nh− trong Word. - Cửa sổ Bảng tính (Worksheet Window) là phần lớn nhất dùng để nhập dữ liệu, tính toán, vẽ đồ thị nh− sau : Hộp Tên (Địa chỉ) Nút chọn toàn bộ bảng tính Tên cột Con trỏ ô Số thứ tự hàng Mốc điền Nút chuyển tới bảng tính Thẻ tên bảng tính Bảng tính hiện tại Thanh thẻ tên bảng tính b - Các thành phần của cửa sổ Bảng tính : - Cột (Column) : Là tập hợp các ô trong bảng tính theo chiều dọc đ−ợc đánh thứ tự bằng chữ cái (từ trái sang phải bắt đầu từ A, B, C, AA, AB đến IV, tổng số có 256 cột). Ngoài cùng bên trái là nút chọn (đánh dấu khối) toàn bộ bảng tính. - Hàng (Row) : Là tập hợp các ô trong bảng tính theo chiều ngang đ−ợc đánh thứ tự bằng số từ 1 đến 16.384). - ô (Cell) : Là giao của một cột và một hàng. Địa chỉ của ô đ−ợc xác định bằng cột tr−ớc, hàng sau, ví dụ C4, AB25. ô hiện tại : Là ô có khung viền chung quanh với một chấm vuông nhỏ ở góc phải d−ới (Mốc điền) hay còn gọi là Con trỏ ô (sau đây gọi tắt là con trỏ). Toạ độ của ô này đ−ợc hiển thị trên thanh Công thức. - Con trỏ bàn phím : Là vạch đứng | nhấp nháy để biểu thị vị trí ký tự sẽ đ−ợc chèn vào. - Con trỏ chuột có các dạng sau : Dấu ⎜ : dùng để đ−a con trỏ ô về vị trí nào đó. Dấu Û : dùng để chọn lệnh, biểu t−ợng hoặc cuộn bảng tính.
Khoa CNTT - VĐH Mở Excel 3 c - Dịch chuyển con trỏ ô trong Bảng tính theo một trong các cách sau : - Trỏ chuột vào ô cần chuyển tới, bấm nút trái. - ấn các phím mũi tên : chuyển tới các hàng, cột lân cận. • PgUp, PgDn : lên hoặc xuống một màn hình. • Home : Về ô A1. • Tab : Sang phải một màn hình • Shift + Tab : Sang trái một màn hình • End + Home : Đến ô cuối cùng của bảng tính - F5, địa chỉ ô, ↵ : Về ô đó, ví dụ để về nhanh ô H22, ta ấn phím F5, gõ H22 rồi ấn ↵ Chú ý : Các thao tác có dấu • chỉ thực hiện đ−ợc khi chọn lệnh Tools, Option, Transition, điền dấu : ở tuỳ chọn Transition Navigation Keys 3. Ra khỏi Excel theo một trong các cách sau : - Chọn File, Exit hoặc - ấn Alt + F4 - Nháy đúp chuột tại dấu trừ ở góc trái trên của màn hình để trở về Windows 3.x. - Nháy vào dấu : trên cùng ở góc phải của màn hình để trở về Windows 95. ý nghĩa các biểu t−ợng trên thanh công cụ (ToolBar) Help : Trợ giúp Tip Wizard : Gợi ý Zoom Control : Phóng to, thu nhỏ bảng tính Drawing : Vẽ một hình Texbox : Hộp văn bản AutoSum : Tính tổng Function Wizard : Gọi hàm Sort Acsending : Sắp xếp tăng Sort Decsending : Sắp xếp giảm Chart Wizard : Vẽ đồ thị
Khoa CNTT - VĐH Mở Excel 4 ý nghĩa các biểu t−ợng trên thanh định dạng (Formating) Font Color : Mầu chữ Color : Mầu nền Borders : Đ−ờng viền Decrease Decimal : Bớt đi 1sõ thập phân Increase Decimal : Tăng thêm 1sõ th.p. Comma Style : Dấu tách nghìn, triệu, tỷ Percent Style: Nhân với 100 và điền dấu % Currency Style : Điền dấu tiền tệ Center Accross Columns : Căn vào giữa của nhiều ô ý nghĩa các thành phần của thanh công thức (FormULA BAR) Hộp Tên (Địa chỉ ) ô hiện tại Nội dung ô hiện tại Nút gọi hàm Nút huỷ bỏ nội dung vừa gõ Nút điền nội dung vừa gõ II. các thao tác với bảng tính 1. L−u (ghi) bảng tính lên đĩa Chọn biểu t−ợng Save hoặc mục File, Save. Nếu đây là lần đầu tiên thực hiện thao tác này với bảng tính, ta phải gõ vào tên cho bảng tính trong ô File Name theo quy tắc đặt tên tệp của DOS, Excel sẽ tự gán kiểu .XLS cho nó. Tên bảng tính sẽ xuất hiện trên thanh tiêu đề của cửa sổ. Sau đó trong quá trình làm việc, ta th−ờng xuyên ghi bảng tính lên đĩa bằng cách trên mà không cần đặt tên cho nó. Nếu ta cần l−u giữ bảng tính với tên khác, chọn mục File, Save As và đặt tên mới cho nó.
Khoa CNTT - VĐH Mở Excel 5 2. Mở bảng tính đ∙ có trên đĩa Chọn biểu t−ợng Open hoặc mục File, Open. Xuất hiện hộp thoại Open với danh sách các bảng tính trong khung File Name đ−ợc xếp theo thứ tự A,B,C. Ta chọn tệp cần thiết rồi chọn OK. Nếu bảng tính ta cần lại ở trên đĩa khác hoặc th− mục khác, ta chọn đĩa từ ô Drives, nháy đúp tại th− mục cần thiết của khung Directories Excel còn có cách mở bảng tính khác: chọn mục File, xuất hiện Menu dọc với danh sách 4* bảng tính mới làm gần nhất ở phía d−ới. Ta chọn tệp cần thiết từ danh sách này. Danh sách các bảng tính này có thể nhiều hơn tuỳ thuộc vào ng−ời cài đặt. 3. Đóng bảng tính Tr−ớc khi chuyển sang bảng tính khác hoặc làm việc khác, phải ghi tệp lên đĩa sau đó mới đóng nó bằng cách chọn mục File, Close. Nếu quên ch−a ghi tệp lên đĩa, Excel sẽ hỏi : Do you want to save change to . XLS ? Chọn Yes để ghi lại, No để không ghi những thay đổi vừa tạo ra cho bảng tính. 4. Chèn thêm 1 bảng tính - Insert / Worksheet, hoặc - Nháy nút phải chuột trên thanh thẻ tên bảng tính để gọi Menu tắt (sau đây chúng ta quy −ớc gọi thao tác này là [Menu tắt], chọn Insert Worksheet 5. Xoá bớt 1 bảng tính - Edit, Delete Sheet, hoặc - [Menu tắt], Delete sheet 6. Đổi tên bảng tính - Nháy đúp vào thẻ tên (tức là vào tên bảng tính, sau đây chúng ta quy −ớc gọi là thẻ tên) trên thanh thẻ tên hoặc Format / Sheet / Rename hoặc [Menu tắt], Rename - Gõ vào tên mới, ↵ hoặc OK 7. Sao chép / Chuyển 1 bảng tính Cách 1: Giữ Ctrl trong khi kéo thả thẻ tên tại một thẻ tên khác (Sheet khác). Nếu không giữ Ctrl bảng tính sẽ đ−ợc chuyển đi. Cách 2: Edit, Move or Copy Sheet. Chọn vị trí đặt bảng tính hiện tại tr−ớc bảng tính nào trong khung Before Sheet. Nếu đánh dấu chọn vào Creat a Copy, Excel sẽ sao chép bảng tính chứ không chuyển nó. * Con số này có thể khác, tuỳ thuộc vào việc chọn Tools, Options, Recently Used Files List
Khoa CNTT - VĐH Mở Excel 6 Chỉ dùng cách 2 nếu bảng tính nguồn và đích cách xa nhau (không thấy thẻ tên của chúng cùng một lúc). 8. Tách bảng tính Có 3 cách giúp ta đồng thời thấy những phần khác nhau của bảng tính Cách 1: Trỏ chuột vào thanh tách cho xuất hiện mũi tên 2 đầu, kéo thả nó tại vị trí cần tách. Cách 2: Đ−a con trỏ ô về vị trí cần tách, chọn mục Window / Split. Sau đó để bỏ tách chọn Window / Remove Split Cách 3: Đ−a con trỏ ô về vị trí cần tách, chọn mục Window / Freeze Panes. Sau đó để bỏ tách chọn Window / Unfreeze Panes 9. ẩn và hiện lại 1 bảng tính - Chọn Format, Sheet, Hide để ản bảng tính - Để hiện lại bảng tính Format, Sheet, Unhide 10. Bảo vệ bảng tính Để những thiết lập về Bảo vệ và che giấu ô (xem trang 26) có tác dụng, cần bảo vệ bảng tính. Cách làm nh− sau : - Tool / Protection - Chọn Protect Sheet để bảo vệ bảng tính, chọn Protect Workbook để bảo vệ tập bảng tính. - Nếu cần thiết gõ mật khẩu vào vùng Password. 2 lần gõ phải giống nhau và l−u ý rằng mật khẩu trong Excel phân biệt chữ hoa với chữ th−ờng ! - Để bỏ tình trạng bảo vệ : chọn Tool / Protection / Unprotect Sheet hay Unprotect Workbook. Nếu có mật khẩu, phải gõ vào, nếu đúng ta mới cập nhật đ−ợc bảng tính. 11. Chọn nhiều bảng tính a. Liền kề : Nháy chuột vào thẻ tên đầu, giữ Shift trong khi nháy chuột vào thẻ tên cuối b. Cách nhau : Giữ Ctrl trong khi lần l−ợt nháy chuột vào các thẻ tên c. Để bỏ việc chọn một bảng tính nào : giữ Ctrl trong khi nháy chuột vào thẻ tên của bảng tính đó IIi. Xử lý dữ liệu trong bảng tính 1. Các kiểu dữ liệu Trong mỗi ô chỉ có thể chứa một kiểu dữ liệu. Kiểu dữ liệu của ô phụ thuộc vào ký tự đầu tiên gõ vào. Các kiểu dữ liệu trong một ô đ−ợc phân ra nh− sau : a - Dạng chuỗi (Text) - Bắt đầu bởi các chữ cái a đến z hoặc A đến Z
Khoa CNTT - VĐH Mở Excel 7 - Những dữ liệu chuỗi dạng số nh− : số điện thoại, số nhà, mã số, .v.v. khi nhập vào phải bắt đầu bằng dấu nháy đơn (') và không có giá trị tính toán. - Theo mặc định, dữ liệu dạng chuỗi đ−ợc căn sang trái ô. b - Dạng số (Number) Bắt đầu bởi : - Các số từ 0 đến 9. - Các dấu +, - , (, *, $ (hoặc một dấu đơn vị tiền khác tuỳ thuộc vào việc đặt các thông số quốc tế của Windows). - Theo mặc định, dữ liệu dạng số đ−ợc căn sang phải ô. c - Dạng công thức (Formulas) Bắt đầu bởi các dấu = hoặc +. Sau khi ấn ↵ công thức nhập vào chỉ thể hiện trên thanh công thức còn kết quả của nó đ−ợc thể hiện trong ô. Nếu thấy : Có thể là do : ##### : Cột quá hẹp #DIV/0! : Chia cho 0 #NAME? : Thực hiện phép tính với một biến không xác định (tên không gắn với một ô hay một vùng nào cả) #N/A : Tham chiếu đến một ô rỗng hoặc không có trong danh sách #VALUE! : Sai về kiểu của toán hạng (ví dụ : lấy số chia cho ký tự hoặc ngày tháng) d - Dạng Ngày (Date), giờ (Time) Trong cách trình bày d−ới đây : DD là 2 con số chỉ Ngày MM là 2 con số chỉ Tháng YY là 2 con số chỉ Năm Nhập theo dạng MM/DD/YY hoặc DD/MM/YY tuỳ thuộc vào việc đặt các thông số quốc tế của Windows, ví dụ nếu đặt thông số quốc tế kiểu Pháp, ta gõ vào 27/09/04, tr−ờng hợp dùng kiểu Mỹ (ngầm định) ta gõ vào 09/27/04. Khi nhập sai dạng thức, Excel tự động chuyển sang dạng chuỗi (căn sang trái ô) và ta không thể dùng dữ liệu kiểu này để tính toán. Có thể nhập ngày bằng cách : - nhập hàm =DATE(YY,MM,DD), đây là cách nhập ngày tốt nhất. - sau đó chọn Format, Cells, Number, Date và chọn dạng thể hiện ngày ở khung bên phải. Đặc biệt : Ctrl + ; (dấu chấm phẩy) cho Ngày hệ thống Ctrl + Shift + ; cho Giờ hệ thống - Theo mặc định, dữ liệu dạng ngày tháng đ−ợc căn sang phải ô.
Khoa CNTT - VĐH Mở Excel 8 2. Các toán tử trong công thức a - Toán tử số + cộng - trừ * nhân (ví dụ = 10*50 cho kết quả 500) / chia = 126/3 42 ^ luỹ thừa = 5^2 25 % phần trăm = 50%*600 300 Thứ tự −u tiên của các phép toán nh− sau : luỹ thừa tr−ớc rồi đến nhân chia và sau cùng mới đến cộng trừ. Các phép toán cùng mức −u tiên (nh− nhân chia hoặc cộng trừ) thực hiện từ trái sang phải. Muốn thay đổi thứ tự −u tiên, dùng các cặp ngoặc tròn, toán tử trong cặp ngoặc ở sâu nhất sẽ đ−ợc thực hiện tr−ớc. Ví dụ: các ô A1, B1, C1 chứa các số 2,3, 4, nếu trong ô D1 gõ =A1+B1*C1 sẽ đ−ợc kết quả 14, gõ =(A1+B1)*C1 sẽ đ−ợc kết quả 20. b - Toán tử chuỗi & Nối chuỗi ="Tin "&"hoc" Tin học c - Toán tử so sánh > lớn hơn >= lớn hơn hoặc bằng khác B1 sẽ nhận đ−ợc kết quả TRUE = A1<=B1 sẽ nhận đ−ợc kết quả FALSE, v.v 3. Nhập dữ liệu a - Dữ liệu bất kỳ - Đ−a con trỏ ô về ô cần thiết. - Nhập dữ liệu theo loại dạng thức. - Để kết thúc việc nhập dữ liệu, làm theo một trong các cách sau : • ấn phím Enter, con trỏ ô sẽ xuống ô d−ới. • ấn một phím mũi tên để đ−a con trỏ ô sang ô cần thiết, ví dụ ấn phím → sẽ đ−a con trỏ ô sang bên phải. • Chọn nút ; (màu xanh lá cây) trên thanh công thức. • Trỏ chuột vào ô cần tới, ấn nút trái.
Khoa CNTT - VĐH Mở Excel 9 Có thể dùng miền nhập dữ liệu sau đây: Giả sử cần nhập 2 cột : Dũng 200 Vân 300 Khoa 150 Tuấn 180 Hà 250 vào các ô A1: B5. Ta làm theo các b−ớc sau : - Đánh dấu khối miền A1:B5 (xem trang 13 về cách đánh dấu khối) - Để nhập theo từng hàng, ấn phím Tab sau mỗi lần gõ dữ liệu vào một ô. - Để nhập theo từng cột, ấn phím ↵ sau mỗi lần gõ dữ liệu vào một ô. H−ớng dịch chuyển của ô nhập dữ liệu khi ấn Tab hoặc ↵ : A B A B 1 1 2 2 3 3 4 4 5 5 ấn Tab ấn ↵ b. Dữ liệu trong các ô giống nhau : - Đánh dấu khối miền cần điền dữ liệu (ví dụ A1:B5) - Gõ vào dữ liệu (ví dụ số 2000) - Kết thúc việc nhập bằng cách ấn phím Ctrl + ↵. Kết quả là : miền A1:B5 sẽ đ−ợc điền kín bởi số 2000 c. Dữ liệu trong các ô tuân theo một quy luật • Chuỗi số với b−ớc nhảy là 1: - Đ−a con trỏ về ô đầu tiên của miền, gõ vào số bắt đầu, ví dụ để đánh số thứ tự cho một số ô bắt đầu từ 1, ta gõ 1. - Trỏ chuột vào mốc điền cho xuất hiện dấu + màu đen, giữ phím Ctrl trong khi kéo và thả chuột tại ô cuối của miền (từ đây về sau ta gọi thao tác này là điền tự động (AutoFill)). Kết quả ta đ−ợc chuỗi số 1, 2, 3, • Chuỗi số với b−ớc nhảy bất kỳ: - Đ−a con trỏ về ô đầu tiên của miền, gõ vào số bắt đầu, ví dụ để có chuỗi số chẵn ta gõ 2 vào một ô nào đó. - Về ô d−ới (hoặc bên phải) của miền, gõ vào số tiếp theo, ví dụ 4.
Khoa CNTT - VĐH Mở Excel 10 - Đánh dấu khối 2 ô này, trỏ chuột vào mốc điền cho xuất hiện dấu + màu đen, kéo và thả chuột tại ô cuối của miền. A B 1 2 2 4 Kéo dấu + và thả tại ô 3 A7, ta sẽ đ−ợc chuỗi 4 số 2,4,6,8,10,12,14 tai 5 miền A1:A7 6 7 • Chuỗi Ngày tháng tăng : - Đ−a con trỏ về ô đầu tiên của miền, gõ vào ngày tháng năm bắt đầu. - Trỏ chuột vào mốc điền cho xuất hiện dấu +, bấm giữ nút phải, kéo thả tại ô cuối miền. - Trong menu tắt chọn : Fill Days : để tăng 1 ngày (ví dụ : 15/04/1999, 16/04/1999, 17/04/1999) Fill Months : để tăng 1 tháng (ví dụ : 15/04/1999, 15/05/1999, 15/06/1999) Fill Years : để tăng 1 năm(ví dụ : 15/04/1999, 15/05/2000, 15/06/2001) • Điền một danh sách tự tạo : - Nếu danh sách này ch−a có thì phải tạo bằng cách : - Tools / Option / Custom List - Trong khung List Entries lần l−ợt nhập các giá trị cho danh sách, hết mỗi giá trị bấm ↵ để xuống dòng. Ví dụ : Thứ Hai ↵ Thứ Ba ↵. Cuối cùng ta đ−ợc nh− hình sau : - OK - Để sử dụng : - nhập một giá trị có trong danh sách tự tạo vào ô đầu - điền tự động tới ô cuối miền (xem cách điền tự động ở trang 9).
Khoa CNTT - VĐH Mở Excel 11 d. Dữ liệu kiểu công thức - Phải bắt đầu bởi dấu = hoặc các dấu +, − - Khi cần lấy số liệu ở ô nào nháy chuột vào ô đó hoặc gõ vào địa chỉ ô Ví dụ : để tính L−ơng theo công thức : L−ơng = Số NC x Tiền 1 Ngày làm theo các b−ớc sau : 1. Chọn miền E2:E7 (vì công thức tính trong miền giống nhau : đều lấy số ở cột C nhân với số ở cột D- xem phần b. ở trên) 2. Gõ vào dấu = 3. Trỏ chuột vào ô C2, ấn nút trái (hoặc gõ C2) 4. Gõ dấu * 5. Trỏ chuột vào ô D2, ấn nút trái (hoặc gõ D2). Tại ô E2 và thanh công thức xuất hiện = C2*D2 6. ấn Ctrl + ↵. Tại các ô từ E2 đến E7, Excel cho kết quả tính L−ơng của từng ng−ời. Ta cũng có thể làm nh− sau : - Bỏ qua b−ớc 1 - Thực hiện các b−ớc từ 2 đến 5, sau b−ớc 5 chọn nút ; trên thanh công thức (hoặc ấn ↵) - Để tính L−ơng cho những ng−ời còn lại, đ−a con trỏ về ô E2, điền tự động cho tới ô E7. A B C D E F 1 STT Tên Số NC Tiền L−ơng Tỷ lệ 1 Ngày 2 1 Luận 20 50 = C2*D2 =E2/$E$8 3 2 Uyên 26 40 4 3 Hải 18 35 5 4 Minh 22 45 6 5 Tuấn 15 20 7 6 Hằng 16 25 8 Cộng =SUM(E2:E7) • Tính tỷ lệ phần trăm L−ơng của từng ng−ời so với Tổng L−ơng : 1. Đ−a con trỏ về ô F2 2. Gõ vào dấu =, dùng chuột chọn ô E2 (hoặc gõ E2), gõ dấu /, nháy chuột vào ô E8 (hoặc gõ E8). Chọn nút ; trên thanh công thức (hoặc ấn ↵) 3. Để tính Tỷ lệ cho những ng−ời còn lại, đ−a con trỏ về ô F2, sao chép công thức tính bằng cách điền tự động cho tới ô F7. Tại các ô F3:F7 xuất hiện #DIV/0! (chia cho 0). Khi đ−a con trỏ về ô F3, ta thấy trên thanh công thức ghi = E3/E9, Excel đã lấy số ở ô bên trái
Khoa CNTT - VĐH Mở Excel 12 (E3) chia cho số ở cách đó 6 ô (E9), tức là đã sử dụng địa chỉ t−ơng đối. Để báo cho Excel lấy lần l−ợt các số từ E2 đến E7 chia cho số cố định ở ô E8 (ô này là địa chỉ tuyệt đối), ta làm nh− sau : - Đ−a con trỏ ô về E2, nháy đúp nút chuột, sau đó đ−a con trỏ bàn phím về ngay tr−ớc ký hiệu E8, ấn phím F4, dấu $ đ−ợc điền vào tr−ớc và giữa ký hiệu đó, ấn ↵ rồi copy công thức này xuốn ô E7. - Đánh dấu khối các ô từ E2 đến E7 (xem phần 5a d−ới đây), chọn biểu t−ợng % (Percent Style) trên thanh định dạng, Excel đổi ra dạng phần trăm và điền dấu % cho các số. Để lấy chính xác hơn, ta chọn các ô này rồi chọn biểu t−ợng Increase Decimal, mỗi lần ấn chuột tại biểu t−ợng này, các con số lại đ−ợc thêm một số thập phân. • Tính Tổng L−ơng và ghi vào ô E8: - Đ−a con trỏ về ô E8 - Gõ vào dấu =, trỏ chuột vào biểu t−ợng ∑ (AutoSum), nháy đúp. Nếu dùng bàn phím, gõ vào công thức =SUM(E2:E7) e. Công thức mảng Ngoài việc sao chép công thức t−ơng đối nh− trên, Excel còn cung cấp một ph−ơng tiện khác gọn gàng hơ, đó là mảng (array) Mảng đ−ợc dùng khi ta gặp phải một l−ợng tính toán nhiều và phức tạp trên một vị trí nhỏ hẹp, hoặc dùng để thay thế các công thức cần lặp lại nhiều lần, mảng giúp tiết kiệm đ−ợc bộ nhớ (thay vì phải có 100 công thức sao chép vào 100 ô, chỉ cần một công thức mảng là đủ). • Nhập một công thức mảng (array formula): - Về ô cần thiết, nếu là dãy ô, phải đánh dấu khối chúng (xem trang 13). ở ví dụ trên bôi đen miền E2:E7. - Khác với loại công thức t−ơng đối (trong ví dụ trên là = C2*D2), ở đây phải nhập toàn bộ dãy ô : =C2:C7*D2:D7. - Kết thúc phải ấn Ctrl + Shift + ↵ (giữ đồng thời 2 phím Ctrl và Shift trong khi ấn ↵). Excel sẽ bao công thức mảng này trong cặp ngoặc nhọn : {=C2:C7*D2:D7)]} • Sửa một công thức mảng : - Về ô bất kỳ đã áp dụng công thức mảng cần sửa - Nháy chuột lên thanh công thức. Các dấu ngoặc { } biến mất. - Tiến hành sửa công thức. Sửa xong bấm Ctrl + Shift + ↵ • Chỉ định (bôi đen) một dãy mảng : Về ô bất kỳ của dãy mảng làm theo một trong hai cách sau : Cách 1 : Bấm Ctrl + / (giữ phím Ctrl trong khi ấn phím sổ chéo xuôi) Cách 2 : Bấm F5, chọn Special sau đó chọn Current Array
Khoa CNTT - VĐH Mở Excel 13 4. Sửa, xoá dữ liệu a - Xoá - Đ−a con trỏ ô về ô hoặc chọn miền cần thiết - ấn phím Delete b - Sửa : Làm theo một trong hai cách sau : - Nháy đúp chuột tại ô (hoặc ấn phím F2), nội dung của ô xuất hiện tại ô đó và tại thanh công thức, đ−a con trỏ bàn phím về chỗ cần thiết và sửa. Ta nên bấm chuột tại thanh công thức và sửa tại đó, sửa xong chọn ký hiệu ; (hoặc ấn ↵) để ghi lại, chọn : (hoặc ấn Esc) để huỷ bỏ mọi sửa đổi. - Nhập dữ liệu mới cho ô đó, sau đó ấn phím ↵ 5. Các thao tác với khối Khối là một miền chữ nhật trên bảng tính. a - Đánh dấu (chọn) khối - Trỏ chuột vào một góc của khối, bấm nút trái đồng thời kéo chuột về góc đối diện theo đ−ờng chéo hoặc - Đ−a con trỏ ô về một góc của khối, giữ phím Shift đồng thời sử dụng các phím mũi tên để đ−a con trỏ ô về góc đối diện theo đ−ờng chéo. Chú ý : Sau khi đ−ợc chọn, toàn bộ khối, trừ ô ở góc xuất phát, đổi mầu. Các ph−ơng pháp đặc biệt : + Khối là một miền liên tục : ♦ Chọn khối bất kỳ : Đ−a con trỏ ô về góc trái trên của khối, giữ phím Shift đồng thời ấn nút trái chuột tại ô ở góc phải d−ới của nó. ♦ Chọn một cột : ấn nút trái chuột tại tên cột đó (các chữ A, B, ) , hoặc ấn Ctrl + Dấu cách. ♦ Chọn một hàng : ấn nút trái chuột tại số thứ tự của hàng đó (các số 1,2, ), hoặc ấn Shift + Dấu cách. ♦ Chọn toàn bộ bảng tính : ấn nút trái chuột tại nút chọn toàn bộ bảng tính (bên trái cột A phía trên hàng 1) hoặc ấn tổ hợp phím Ctrl + Shift + dấu cách + Chọn các ô rời rạc : Đ−a con trỏ tới ô đầu định chọn, giữ Ctrl và bấm nút trái chuột tại các ô định chọn tiếp theo. + Khối là các miền rời rạc : Chọn vùng đầu, giữ Ctrl,bấm nút trái và rê chuột tại các miền khác. Ví dụ : Để chọn đ−ợc các miền nh− hình d−ới đây, ta làm theo các b−ớc sau : - Chọn miền liên tục B2:C3
Khoa CNTT - VĐH Mở Excel 14 - ấn giữ phím Ctrl và chọn miền liên tục E4:G7 - Giữ nguyên phím Ctrl và chọn miền liên tục C9:F11 b - Copy, xoá, dán khối dùng bộ nhớ đệm (th−ờng dùng khi miền đích và miền nguồn cách nhau hoặc để dán một khối vào nhiều chỗ khác nhau). Tr−ớc khi thực hiện các thao tác sau, ta phải chọn khối cần thiết. + Copy : Chọn biểu t−ợng Copy, hoặc ấn Ctrl+C, hoặc chọn mục Edit, Copy. + Xoá : Chọn biểu t−ợng Cut, hoặc ấn Ctrl+X, hoặc chọn mục Edit, Cut. + Lấy khối từ bộ nhớ đệm ra dán vào bảng tính - Đ−a con trỏ ô tới góc trái trên của miền đích. - Chọn biểu t−ợng Paste, hoặc ấn Ctrl+V, hoặc chọn mục Edit, Past. c - Copy, chuyển khối dùng chuột - Chọn khối cần thiết - Trỏ chuột vào biên của khối sao cho xuất hiện Û. Sau đó thực hiện một trong các thao tác sau : + Giữ Ctrl đồng thời kéo và thả khối tại vị trí đích để Copy khối. Nếu trong b−ớc này không dùng phím Ctrl thì khối sẽ đ−ợc chuyển tới vị trí đích, hoặc + Bấm giữ nút phải chuột, kéo và thả khối tại vị trí đích. Xuất hiện thực đơn cho phép chọn một trong các ph−ơng án. Copy : Copy toàn bộ, bao gồm cả dữ liệu và khuôn dang. Move : Chuyển dữ liệu. Copy Value : Chỉ copy dữ liệu. Copy Format : Chỉ copy khuôn dạng. Chú ý : Khi copy dữ liệu - Nếu miền nguồn chứa dữ liệu số hoặc chuỗi, kết quả miền đích sẽ giống miền nguồn. - Nếu miền nguồn chứa công thức, kết quả miền đích sẽ thay đổi hay không tuỳ thuộc vào công thức trong miền nguồn tham chiếu đến địa chỉ t−ơng đối hay địa chỉ tuyệt đối.
Khoa CNTT - VĐH Mở Excel 15 6. Xử lý ô, cột, hàng trong bảng tính a - Thay đổi kích th−ớc cột, hàng • Một cột / một hàng : - Cột : Trỏ chuột vào vạch đứng ở bên phải tên cột sao cho xuất hiện ╫ , kéo và thả vạch đó tại vị trí mới. - Hàng : Trỏ chuột vào vạch ngang d−ới số thứ tự hàng sao cho xuất hiện ╪ , kéo và thả vạch đó tại vị trí mới. Kéo và thả vạch này tại vị trí mới để thay đổi độ rộng cột C Kéo và thả vạch này tại vị trí mới để thay đổi chiều cao hàng 4 • Nhiều cột / Nhiều hàng : - Cột : - Chọn một số ô của các cột. - Format, Colum, Width. - Gõ vào độ rộng mới cho các cột - OK hoặc ↵ - Hàng : - Chọn một số ô của các hàng. - Format, Row, Height. - Gõ vào chiều cao mới cho các hàng - OK hoặc ↵ b - Chèn thêm cột, hàng, ô • Cột : - Chọn khối là tên các cột (các chữ A, B, ) tại vị trí cần chèn, cần thêm bao nhiêu cột ta chọn bấy nhiêu. - Chọn Insert, Columns. Excel sẽ chèn thêm các cột trống và đẩy các cột đ−ợc chọn sang phải • Hàng : - Chọn khối là số thứ tự của các hàng (các số 1, 2, ) tại vị trí cần chèn, cần thêm bao nhiêu hàng ta chọn bấy nhiêu. - Chọn Insert, Rows. Excel sẽ chèn thêm các hàng trống và đẩy các hàng đ−ợc chọn xuống d−ới.
Khoa CNTT - VĐH Mở Excel 16 • ộ : - Chọn khối là các ô tại vị trí cần chèn (nh− ở ví dụ d−ới đây là các ô C3:D4), cần thêm bao nhiêu ô ta chọn bấy nhiêu. - Chọn Insert, Cells. xuất hiện hộp thoại Insert Chọn Để - Shift Cells Right : đẩy các ô đ−ợc chọn sang phải - Shift Cells Left : đẩy các ô đ−ợc chọn xuống d−ới - OK hoặc ↵ : thực hiện lệnh c - Xoá cột, hàng, ô Cột : - Chọn khối là tên các cột (các chữ A, B, ) tại vị trí cần xoá, cần xoá bao nhiêu cột ta chọn bấy nhiêu. - Chọn Edit, Delete. Hàng : - Chọn khối là số thứ tự các hàng (các số 1, 2, ) tại vị trí cần xoá, cần xoá bao nhiêu cột ta chọn bấy nhiêu. - Chọn Edit, Delete. ô : - Chọn khối là các ô cần xoá. - Chọn Edit, Delete. Xuất hiện hộp thoại Delete Chọn Để - Shift Cells Left : chuyển dữ liệu của các ô bên phải sang vùng bị xoá - Shift Cells Up : chuyển dữ liệu của các ô phía d−ới lên vùng bị xoá. - Entire Row : xoá toàn bộ các hàng chứa vùng đ−ợc chọn. - Entire Column : xoá toàn bộ các cột chứa vùng đ−ợc chọn. - OK hoặc ↵ : thực hiện lệnh d - Chuyển hàng thành cột và ng−ợc lại Giả sử cần chuyển dữ liệu đang đ−ợc xếp thành cột trong các ô A2:A6 thành hàng tại các ô C3:F3, ta làm nh− sau :
Khoa CNTT - VĐH Mở Excel 17 A B C D E F G 1 2 STT 3 Tên STT Tên Toán Tin Anh 4 Toán 5 Tin 6 Anh - Chọn các ô muốn cho đổi chỗ hay còn gọi là miền nguồn (A2:A6) - Chọn biểu t−ợng Copy hoặc Ctrl+C - Đ−a con trỏ về ô đầu tiên của miền dán (C3). - Chọn mục Edit, Past Special, đánh dấu chọn ô Transpose - OK Chú ý : Miền nguồn và miền dán không đ−ợc giao nhau (không đ−ợc có ô chung) f - ẩn/ hiện cột, hàng Để tiện cho thao tác, trên các bảng tính, nhất là các bảng tính lớn, ng−ời ta th−ờng cho ẩn các cột (hoặc hàng) không cần thiết. Khi nào cần lại cho chúng hiện trở lại. Cách làm nh− sau : - Chọn các cột (hoặc các hàng) cần ẩn đi. - Chọn Format, Column (hoặc Row). - Chọn Hide. Tại vị trí các cột (hoặc hàng) ẩn, xuất hiện đ−ờng kẻ dọc (hoặc ngang) đậm, các cột (hoặc hàng) bị ẩn vẫn có tác dụng (vẫn sử dung để tính toán). Để cho chúng hiện trở lại, ta làm nh− : - Chọn các cột (hoặc các hàng) liền kề với chúng. Ví dụ cần cho các cột C, D, E hiện trở lại, ta chọn các cột đứng ngay tr−ớc và sau chúng : B, F; cần cho các hàng 4, 5, 6 hiện trở lại, ta chọn các hàng ở ngay trên và ngay d−ới chúng : 3, 7. - Chọn Format, Column (hoặc Row), Unhide g - Cố định cột, hàng tiêu đề ở các bảng tính lớn, khi cuộn xem hoặc nhập dữ liệu ở phần d−ới thì không còn thấy tiêu đề cột của chúng ở hàng trên cùng, do đó rất dễ bị nhầm lẫn giữa cột nọ với cột kia. T−ơng tự nh− vậy, khi cuộn xem hoặc nhập dữ liệu ở bên phải thì không còn thấy các cột trái nhất (nh− các cột Họ tên, Mặt hàng, ), rất dễ bị nhầm lẫn hàng nọ với hàng kia, Excel giúp ta cố định cột, hàng tiêu đề, cách làm nh− sau : - Đ−a con trỏ về ô chuẩn để cố định. ô này phải nằm ngay d−ới hàng và ngay bên phải cột cần cố định. Ví dụ, cần cố định các cột A, B và các hàng 1, 2, ta đ−a con trỏ về ô C3 - Chọn Window, Freeze Pane. Tại ô chuẩn xuất hiện 2 đ−ờng kẻ dọc và ngang.
Khoa CNTT - VĐH Mở Excel 18 - Để huỷ bỏ việc cố định cột, hàng tiêu đề, chọn Window, Unfreeze Pane 7. Định dạng dữ liệu a - Định dạng ký tự - Chọn miền dữ liệu cần định dạng, sau đó • Dùng chuột Font Font Size Color Font Color - Chọn một kiểu chữ từ hộp Font - Chọn một cỡ chữ từ hộp Size - Chọn Bold để (hoặc bỏ) in đậm, chọn Italic để (hoặc bỏ) in nghiêng, chọn Underline để (hoặc bỏ) gạch chân - Chọn Color để đặt mầu nền. - Chọn Font Color để đặt mầu chữ. • Dùng Menu (để có thể chọn nhiều mục đồng thời) - Format, Cells, Font - Chọn Color để đặt mầu chữ. - Chọn một kiểu chữ từ hộp Font - Chọn một cỡ chữ từ hộp Size - Trong Font Style chọn Italic để in nghiêng, chọn Bold để in đậm, chọn Bold Italic để in vừa nghiêng vừa đậm, chọn Regular để ký tự trở lại bình th−ờng. - Trong Underline chọn một kiểu gạch chân : None (bỏ gạch chân), Single (gạch bằng nét đơn), Double (gạch bằng nét đôi), Single Accounting (gạch bằng nét đơn kiểu tài chính đến cuối mép phải của
Khoa CNTT - VĐH Mở Excel 19 ô), Double Accounting (gạch bằng nét đôi kiểu tài chính đến cuối mép phải của ô) - Chọn Color để đặt mầu chữ. - Trong ô Effect : chọn Strikerthough (gạch ngang), Superscript (số mũ), Subscript (chỉ số) - Chọn Normal Font nếu muốn bỏ mọi trình bầy và lấy lại kiểu ngầm định. • Dùng bàn phím Dùng tổ hợp phím Để Ctrl + B In đậm Ctrl + I In nghiêng Ctrl + U Gạch chân Ctrl + 5 Gạch ngang Ctrl + Shift + F Mở danh sách Font chữ Ctrl + Shift + P Mở danh sách cỡ chữ b - Định dạng số - Chọn miền dữ liệu cần định dạng, sau đó chọn các biểu t−ợng t−ơng ứng trên thanh định dạng (xem ý nghĩa của chúng trên trang 4) Số Dùng biểu t−ợng Đổi thành 123 Currency Style (Điền dấu tiền tệ) $ 123.00 123456 Comma Style (Dấu tách nghìn, triệu, tỷ) 123,456.00 0.156 Percent Style (Nhân với 100 và điền dấu %) 15.6 % 12.346 Increase Decimal (lấy thêm 1 số thập phân) 12.3460 12.346 Decrease Decimal (giảm đi 1 số thập phân) 12.35 Trong các ví dụ trên, các thông số quốc tế (dấu tiền tệ; dấu tách nghìn, triệu, tỷ; dấu thập phân) là kiểu Mỹ (ngầm định). Có thể đặt lại các thông số này cho phù hợp. Để ẩn (che đi) các số không (0) - Chọn các ô cần thiết - Chọn Format, Cells - Chọn Number - Trong ô Code gõ vào 0;0; - OK c - Canh biên (dóng hàng) - Chọn vùng dữ liệu cần canh biên • Dùng biểu t−ợng (chỉ căn đ−ợc theo chiều ngang)
Khoa CNTT - VĐH Mở Excel 20 Căn một tiêu đề vào giữa nhiều ô Căn thẳng trái Căn thẳng phải Căn vào giữa Ví dụ : Để có tiêu đề nh− d−ới đây, ta làm nh− sau : A B C D E F G H I K L M 1 đại học mở hn cộng hoà x∙ hội chủ nghĩa việt nam 2 khoa cntt Độc lập - Tự do - Hạnh phúc 3 Tại ô A1 gõ đại học mở hn Tại ô A2 gõ khoa cntt Chọn miền A1:D2, chọn biểu t−ợng (căn vào giữa nhiều ô) Tại ô E1 gõ cộng hoà x∙ hội chủ nghĩa việt nam Tại ô E2 gõ Độc lập - Tự do - Hạnh phúc Chọn miền E1:K2, chọn biểu t−ợng Căn vào giữa nhiều ô • Dùng Menu căn đ−ợc theo cả chiều ngang lẫn chiều dọc - Format, Cells. Xuất hiện hộp thoại Format Cells - Chọn Aligment Các thông số dóng hàng của dữ liệu trong ô bao gồm : Horizontal : theo chiều ngang General: Ký tự đ−ợc căn về bên trái, số đ−ợc căn sang phải, các giá trị logic và lỗi đ−ợc căn vào giữa.
Khoa CNTT - VĐH Mở Excel 21 Left,Center,Right : Căn sang trái, vào giữa, sang phải. Fill : Tự điền đầy ô (trong ô chỉ cần gõ 1 nhóm ký tự sau đó chọn mục này, Excel sẽ điền đầy miền bằng nhóm này) Justify : Căn thẳng 2 bên. Tr−ờng hợp độ rộng của cột nhỏ hơn nội dung bên trong nó, Excel tự động tăng chiều cao của hàng. Center Across : Căn vào giữa miền đã chọn (t−ơng tự nh− việc chọn Selection biểu t−ợng ← a → ) Orientation : H−ớng phân bố Wrap Text : Nếu chọn sẽ cho xuống dòng trong các ô khi nội dung v−ợt quá chiều ngang ô (nh− cột Trung bình) d - Kẻ khung - Chọn vùng dữ liệu cần kẻ khung • Dùng chuột - Chọn ỉ của biểu t−ợng Borders. - Chọn tiếp loại đ−ờng kẻ cần thiết. • Dùng Menu - Chọn Format, Cells, Border. - Chọn tiếp đ−ờng cần kẻ trong khung Border : Outline : Viền xung quanh khối ô Left, Right, Top, Bottom : Viền trái, phải, trên, d−ới mỗi ô Color : Mầu của đ−ờng kẻ - Chọn kiểu đ−ờng kẻ trong khung Style
Khoa CNTT - VĐH Mở Excel 22 e - Tô mầu - Format, Cells, Patterns - Chọn Patterns để đặt mầu nền. - Chọn Color để đặt mầu tô. f - Định dạng tự động Excel có sẵn một số khuôn mẫu đ−ợc sử dụng để định dạng. Các b−ớc nh− sau : - Chọn vùng dữ liệu cần định dạng - Format, AutoFormat - Chọn kiểu tạo khuôn trong danh sách Table Format - Nút Option cho phép thực hiện việc tạo khuôn tự động hạn chế trên các các kiểu dữ liệu nhất định. - Vùng Sample là mẫu của khuôn đ−ợc minh hoạ trên một ví dụ giả định. 8. Đặt tên cho ô Đặt tên cho ô hay nhóm ô có tác dụng sau : - Tên dễ đọc, dễ nhớ. Nh− ở ví dụ trên trang 11, tên "TongLuong" dễ hiểu và dễ nhớ hơn công thức =SUM(E2:E7). - Khi đã gán tên có thể tham chiếu tới ô hay nhóm ô bằng tên, ví dụ trong ô F2 ta có thể thay địa chỉ $E$8 bằng tên TongLuong. - Việc di chuyển về một ô (hay miền) đã đ−ợc đặt tên trở nên nhanh chóng và thuận tiện. Tên phải bắt đầu bởi chữ cái hoặc dấu _ (nối d−ới), \ (sổ chéo ng−ợc), có độ dài nhiều nhất là 255 ký tự và không đ−ợc chứa dấu cách. Để dễ đọc, các chữ cái đầu mỗi từ trong tên nên viết hoa, ví dụ TongLuong hay nối các từ bằng dấu _ (nối d−ới), ví dụ Bang_Tra_Cuu. Không nên gõ dấu tiếng Việt trong tên.
Khoa CNTT - VĐH Mở Excel 23 a - Đặt tên cho ô hay nhóm ô bằng tay Sau khi chọn ô hay nhóm ô cần đặt tên làm theo một trong hai cách sau: Cách 1 : - Nháy chuột tại ỉ của hộp Tên trên thanh công thức, địa chỉ của ô đổi sang mầu xanh. - Xoá địa chỉ đó đi, gõ vào tên rồi ấn ↵ Cách 2 : - Chọn mục Insert, Name, Define (hoặc Ctrl + F3) - Trong khung Names in Workbook gõ vào tên cho ô hay nhóm ô. - OK b - Đặt tên theo tiêu đề của cột hay hàng (tự động) Các b−ớc nh− sau : - Chọn ô hay nhóm ô cần đặt tên gồm cả các tiêu đề cột hoặc hàng, nh− ví dụ trên trang 11 định đặt tên cho dãy ô ghi số ngày công, ta chọn C1:C7. - Chọn Insert, Name, Create (hoặc Ctrl+Shift+F3). - ý nghĩa các mục trong hộp Create Name nh− sau : Top Row : Lấy ô ở hàng đầu (của khối đã chọn) làm tên Bottom Row : Lấy ô ở hàng cuối (của khối đã chọn) làm tên Left Column : Lấy ô ở cột bên trái (của khối đã chọn) làm tên Right Column : Lấy ô ở cột bên phải (của khối đã chọn) làm tên - OK. Kết quả là ở ví dụ trên, miền C2:C7 đ−ợc đặt tên là Số_NC c - Dán tên vào công thức Khi nhập hay sửa công thức, thay vì điền vào địa chỉ của ô (hay miền) đã đ−ợc đặt tên, ta - ấn F3 (hoặc nháy chuột tại ỉ của Hộp Tên trên thanh công thức, hoặc chọn mục Insert, Name, Paste ) - Chọn tên cần thiết từ danh sách rồi OK
Khoa CNTT - VĐH Mở Excel 24 Nháy chuột tại đây để chọn tên c - Về nhanh một ô (hay miền) đ∙ đ−ợc đặt tên Có thể đ−a con trỏ về nhanh một ô (hay miền) đã đ−ợc đặt tên bằng một trong 2 cách sau : Cách 1 : - Nháy chuột tại ỉ của Hộp tên trên thanh công thức và chọn tên cần thiết từ danh sách (hoặc gõ địa chỉ của ô). Cách 2 : - ấn F5 - Chọn tên cần thiết từ danh sách - OK Đây cũng là ph−ơng pháp kiểm tra xem Tên đã đ−ợc đặt cho một ô (hay miền) nào. d - Xoá tên - Chọn Insert, Name, Define. - Chọn tên cần xoá từ danh sách - Chọn Delete - Chọn OK hoặc Close. e - Chú ý - Nếu muốn lấy tên của nhóm ô này để đặt cho nhóm ô khác, tr−ớc hết phải xoá tên đó đi. 9. Ghi chú cho ô Có thể thêm phần ghi chú cho từng ô riêng rẽ. Những ghi chú này giúp ta giải thích bảng tính của mình một cách rõ ràng hơn (tại sao lại dùng hàm này hay đạt địa chỉ kia là tuyệt đối .v.v ). a - Tạo ghi chú - Về ô cần tạo ghi chú. - Mở hộp thoại CellNote theo một trong hai các sau : Cách 1 : - Shift + F2 Cách 2 : - Chọn mục Insert / Note - Gõ vào lời ghi chú trong khung Text Note - OK để đóng hộp thoại.
Khoa CNTT - VĐH Mở Excel 25 Một chấm vuông mầu đỏ ở góc phải trên của ô chỉ ra rằng ô đó đã đ−ợc ghi chú. Nếu không thấy chấm vuông này hãy làm nh− sau : - Tools / Options / View - Đánh dấu chọn vào ô Note Indicator Hình d−ới đây cho thấy các ô H7 và I7 có ghi chú. Nếu đang dùng Excel 7 hoặc Excel 97 thì khi rà chuột vào ô I7, nội dung ghi chú đ−ợc hiển thị b - Xem / Sửa / Xoá các ghi chú - Nếu cần xem ghi chú của một ô : chỉ việc rà chuột vào ô đó, một khung với nội dung ghi chú xuất hiện. Để tắt khung này : rà chuột sang ô khác. - Để sửa hay xoá một ghi chú : mở hộp thoại CellNote - Chọn ghi chú cần thiết trong khung Note In Sheet - Tiến hành sửa nội dung của ghi chú trong khung Text Note. Để xoá ghi chú : chọn Delete
Khoa CNTT - VĐH Mở Excel 26 10. Bảo vệ ô Trong một số tr−ờng hợp, để giới hạn quyền sử dụng của ng−ời khác, ví dụ : không cho họ sửa đổi một số ô này hay xem công thức ở một số ô khác .v.v hãy sử dụng tính năng bảo vệ và che dấu ô. Cách làm nh− sau : - Chọn ô hay nhóm ô cần bảo vệ. - Chọn mục Format / Cells / Protection. - Trong hộp thoại đánh dấu chọn vào Locked : để khoá không cho sửa đổi Hidden : để che dấu công thức - OK Sau đó cần phải bảo vệ bảng tính thì những thiết lập trên mới có tác dụng. Xem phần “7. Bảo vệ bảng tính “ ở trang 6. iV. hàm trong Excel Hàm (Function) đ−ợc xem nh− là các công thức định sẵn nhằm thực hiện các tính toán chuyên biệt. Trên ô thực hiện hàm sẽ cho một giá trị hoặc một thông báo lỗi. Excel có trên 300 hàm và đ−ợc phân loại thành từng nhóm. 1. Quy tắc sử dụng hàm - Các hàm có dạng tổng quát : TêNHàM(Các tham biến). Ví dụ : TODAY( ) cho kết quả là ngày hiện tại trong máy 27/09/04 (hàm không cần tham biến) LEN("Excel 5.0") cho kết quả độ dài của chuỗi là 9 (hàm 1 tham biến) AVERAGE(A1,B5,D8) cho kết quả là trung bình cộng các số trong các ô A1, B5, D5 (hàm nhiều tham biến) - Tên hàm có thể viết th−ờng hay hoa hoặc vừa viết th−ờng vừa viết hoa đều đ−ợc. - Các tham biến có thể có hoặc không nh−ng phải đặt trong hai dấu ( ) và cách nhau bởi dấu phẩy (nh− trong tài liệu này), chấm phẩy hoặc một dấu ngăn cách nào khác tuỳ theo cách đặt các thông số quốc tế. Trong 1 hàm có thể chứa nhiều nhất 30 tham biến nh−ng không đ−ợc v−ợt quá 255 ký tự. - Trong hàm không đ−ợc có dấu cách. - Hàm phải đ−ợc bắt đầu bởi dấu = hoặc dấu của một phép tính. Tr−ờng hợp dùng một hàm để làm tham biến cho một hàm khác (hàm lồng nhau, nhiều nhất là 7 mức) không cần viết dấu = tr−ớc tên hàm đó. Ví dụ : các ô A1, B1 chứa số đo các cạnh của tam giác vuông, khi đó công thức = SQRT(SUM(A1^2, B1^2)) gõ tại ô C1 cho số đo cạnh huyền.
Khoa CNTT - VĐH Mở Excel 27 ở đây, SQRT là hàm khai căn bậc 2, SUM là hàm tính tổng (bình ph−ơng của 2 cạnh góc vuông), ta thấy tr−ớc hàm này không có dấu = vì nó đ−ợc dùng làm tham biến (đối số) cho hàm SQRT. 2. Nhập hàm vào bảng tính Có 3 cách nhập hàm vào bảng tính : - Gõ vào từ bàn phím - Dùng biểu t−ợng (Function Wizard) - Dùng menu Để nhập hàm : đ−a con trỏ ô về ô cần thiết rồi chọn một trong các cách sau : a - Gõ vào từ bàn phím: - Gõ dấu = - Gõ vào tên hàm, dấu (, các tham biến theo đúng dạng thức quy định, dấu ) b - Dùng biểu t−ợng Function Wizard. Các b−ớc nh− sau : 1 -Chọn biểu t−ợng trên thanh công cụ. Xuất hiện hộp thoại Function Wizard nh− sau : ý nghĩa của các nhóm hàm trong khung Function Category nh− sau : Most Recently Used : Các hàm sử dụng gần đây nhất All : Tất cả các hàm Financial : Hàm tài chính Date & Time : Hàm Ngày và Giờ Math & Trig : Hàm Toán và L−ợng giác Statistical : Hàm thống kê Lookup & Reference : Hàm Tìm kiếm và Tham chiếu
Khoa CNTT - VĐH Mở Excel 28 Database : Hàm Cơ sở dữ liệu Text : Hàm xử lý ký tự Logical : Hàm Logic Information : Các hàm Thông tin về ô, về bảng tính .v.v. 2 -Chọn nhóm hàm trong khung Function Category. Khi di chuyển thanh sáng đến nhóm nào, Excel sẽ liệt kê các hàm của nhóm đó theo thứ tự chữ cái trong khung Function Name. 3 -Chọn nhóm hàm trong khung Function Name 4 -Chọn Next. Xuất hiện hộp thoại Function Wizard liệt kê công dụng, khung các tham biến cần nhập, khung chứa giá trị kết quả của hàm Điền các tham biến của hàm bằng cách ấn nút chuột vào khung cần thiết, sau đó nhập từ bàn phím hoặc rê chuột trên miền dữ liệu (ở hình trên là chọn miền A3:C3). Tr−ờng hợp cần gọi hàm khác, chọn nút fx bên trái của khung. 5 - Chọn Finish. Excel tắt hộp thoại và ghi kết quả của hàm vào ô. 6 -L−u ý : + ý nghĩa các nút lệnh khác : - Next : chuyển sang b−ớc tiếp theo : Function Wizard step 2 of 2 - Back : lùi về b−ớc tr−ớc : Function Wizard step 1 of 2 - Cancel : huỷ bỏ lệnh + Khi con trỏ ô ở tại ô đang chứa hàm, nếu chọn Function Wizard Excel sẽ chuyển sang việc chỉnh sửa hàm, xuất hiện hộp thoại Editing Function 1 of 1 sau :
Khoa CNTT - VĐH Mở Excel 29 c - Dùng Menu - Chọn mục Insert, Function. Xuất hiện hộp thoại Function Wizard - Các b−ớc còn lại giống nh− khi chọn biểu t−ợng fx (trang ) 3. Một số hàm th−ờng dùng 3.1. Hàm ngày tháng DATE(year,month,day) Chỉ ra ngày dạng số ứng với ngày tháng năm. Ví dụ : =DATE(04,09,27) trả về 27-09-04 DAY(date) Số ngày trong tháng của biến ngày tháng date. Ví dụ : =DAY(27-09-04) trả về 27 =DAY("27-Sep") trả về 27 MONTH(date) Số tháng trong năm của biến ngày tháng date. Ví dụ : =MONTH(27-09-04) trả về 9 =MONTH("27-Sep") trả về 9 Time(hour, minute, second) Chỉ ra thời gian dạng số. Ví dụ : =TIME(19,5,14) trả về 19:05:14 hoặc 7:05 PM WEEKDAY(date) Chỉ ra số thứ tự của ngày trong tuần của biến ngày tháng date (Thứ Hai là ngày thứ 1, Thứ Ba là ngày thứ 2, , Chủ Nhật là ngày thứ 7) Ví dụ : =WEEKDAY(27-09-04) trả về 6 YEAR(date) Số năm của biến ngày tháng date.
Khoa CNTT - VĐH Mở Excel 30 Ví dụ : =YEAR(27-09-04) trả về 2004 3.2. Hàm ký tự EXACT(text1, text2) Nhận giá trị TRUE hay FALSE phụ thuộc vào hai chuỗi text1 và text2 có giống hệt nhau hay không. Ví dụ : =EXACT("EXCEL","EXCEL") trả về TRUE =EXACT("EXCEL","Excel") trả về FALSE FIND(find_text, text, atnum) Vị trí của chuỗi con (Find_text) trong chuỗi lớn (text ) bắt đầu từ vị trí atnum, nếu bỏ qua atnum nó đ−ợc cho bằng 1. Hàm này phân biệt chữ HOA và th−ờng Ví dụ : =FIND("e","Excel 5.0") trả về 4 =FIND("E","Excel 5.0") trả về 1 =FIND("A","Excel 5.0") trả về #VALUE! (lỗi) FIXED(number, decimal, no_commas) Chuyển số (number) thành chuỗi dạng cố định với decimal số thập phân. Nếu không ghi decimal sẽ cho 2 chữ số thập phân. Nếu no_commas là FALSE (hoặc không ghi), chuỗi in ra sẽ có dấu ngăn cách nghìn triệu nh− ví dụ 1, nếu no_commas là TRUE, chuỗi in ra sẽ không có dấu ngăn cách ngàn triệu nh− ví dụ 2 (chú ý ở ví dụ này có 2 dấu phẩy) Ví dụ : ô A1 chứa số 12345.6789, khi đó =FIXED(A1,3) trả về 12 345.679 =FIXED(A1, ,TRUE) trả về 12345.68 LEFT(text, number) Lấy number ký tự bên trái của text. Ví dụ : =LEFT("Excel 5.0", 5) trả về Excel LEN(text) Độ dài của chuỗi ký tự text. Ví dụ : =LEN("Excel 5.0", 5) trả về 9 LOWER(text) Chuyển text thành chữ th−ờng. Ví dụ : =LOWER("EXCEL") trả về excel MID(text, numstart, numchar) Trả lại numchar ký tự của text bắt đầu từ vị trí numstart. Ví dụ : =MID("Excel for Windows",11,3) trả về Win PROPER(text)
Khoa CNTT - VĐH Mở Excel 31 Chuyển các chữ cái đầu từ của text thành chữ viết hoa. Ví dụ : =PROPER("excel for windows") trả về Excel For Windows Chú ý : nếu text là tiếng Việt, hàm này sẽ cho kết quả sai. Ví dụ : = PROPER("việt") trả về Viửt REPLACE(oldtext, numstart, numchar, newtext) Thay newtext vào oldtext bắt đầu từ vị trí numstart và có độ dài numchar. Ví dụ : =REPLACE("Tôi học Quattro 5.0",9,7,"Excel") trả về Tôi học Excel 5.0 REPT(text,number) Lặp lại text liên tiếp number lần. Ví dụ : =REPT("Tin học ",3) trả về Tin học Tin học Tin học RIGHT(text, number) Lấy ra number ký tự bên phải của text. Ví dụ : =RIGHT("Excel 5.0", 3) trả về 5.0 SEARCH(find_text, text, atnum) T−ơng tự hàm Find nh−ng không phân biệt chữ in hoa hay chữ in th−ờng. SUBSTITUTE(text, oldtext, newtext, instance) Thay thế newtext vào vị trí oldtext trong text ở lần xuất hiện instance (Nếu không có đối số này sẽ thay thế ở mọi vị trí). Ví dụ : =SUBSTITUTE("Hãy xem xem", "xem", "nhìn",1) trả về Hãy nhìn xem =SUBSTITUTE("Hãy xem xem", "xem", "đây",2) trả về Hãy xem đây TRIM(text) Cắt bỏ các ký tự trống vô nghĩa trong text. Ví dụ : =TRIM(" MS Excel 5.0 ") trả về MS Excel 5.0 UPPER(text) Chuyển text thành chữ in hoa toàn bộ. Ví dụ : =UPPER("excel") trả về EXCEL VALUE(text) Chuyển text sang số. Ví dụ : =VALUE(RIGHT("Tel. 533332",6)) trả về 533332 3.3. Hàm toán học ABS(x) : Giá trị tuyệt đối của số x.
Khoa CNTT - VĐH Mở Excel 32 ACOS(x) : Hàm arccos của x, x nằm trong khoảng từ -1 đến 1. Giá trị góc trả về theo radian từ 0 đến π. Nếu muốn chuyển kết quả sang độ, nhân nó với 180/PI( ) ASIN(x) : Hàm arcsin của x, x nằm trong khoảng từ -1 đến 1. Giá trị góc trả về theo radian từ -π/2 đến π/2. ATAN(x) : Hàm arctangent của x. T−ơng tự nh− ASIN(x) COS(x) : Hàm cos của góc x, x là góc theo radian, nếu là độ, phải nhân nó với PI( )/180. Ví dụ : = COS(0.785398) trả về 0.707107 (cos của π/4) = COS(45*PI( )/180) trả về 0.707107 (cos của 45o) COSH(x) : Hàm Cos Hyperbolic của góc x. COuntif(range, criteria) : Đếm số ô không rỗng thoả tiêu chuẩn cho tr−ớc Range Là tập hợp các ô mà ta muốn đếm Criteria Tiêu chuẩn, có thể là số, chữ hoặc biểu thức, xác định xem sẽ đếm ô nào. Xét bảng trên trang 55, để đếm số ng−ời + Có L−ơng bằng 50, dùng công thức : Công thức =COUNTIF(D2:D10,50) cho kết quả là 3 + Tuổi từ 30 trở lên dùng công thức : =COUNTIF(D2:D10,”>=30”) (phải để biểu thức trong dấu nháy kép ”) + Tên bắt đầu bằng chữ H dùng công thức : =COUNTIF(A2:A10,”H*”) (phải để chữ và dấu * trong dấu nháy kép ”) Hàm COUNTIF chỉ đếm theo 1 điều kiện, ví dụ chỉ đếm số Nữ hoặc chỉ đếm số Tuổi từ 30 trở lên. Nếu cần đếm số Nữ có tuổi từ 30 trở lên (kết hợp 2 điều kiện) phải dùng hàm DCOUNT (xem trang 57) DEGREES(x) : Đổi radian sang độ Ví dụ : = DEGREES(PI( )/4) trả về 45 (độ) EXP(x) : Hàm mũ ex (trả về luỹ thừa của e). Ví dụ : = EXP(1) trả về 2.718281 INT(x) : Số nguyên lớn nhất không v−ợt quá (nhỏ hơn) x. Ví dụ : = INT(2.789) trả về 2 = INT(-2.4) trả về -3 (vì -3 nhỏ hơn -2.4) LN(x) : logarit tự nhiên của x. LOG(number, base) : Logarit cơ số base của number LOG10(number) : Logarit cơ số 10 của number (log10x) MDETERM(DC) : Định thức ma trận cho bởi địa chỉ vùng DC.
Khoa CNTT - VĐH Mở Excel 33 MINVERSE(DC) : Ma trận ng−ợc của DC. MMULT(M1,M2) : Tích hai ma trận M1,M2. MOD(n,t) : Số d− của phép chia nguyên n/t. Ví dụ : =MOD(11,3) trả về 2 PI( ) : Trả về số π bằng 3.141592654 RAND( ) : Số ngẫu nhiên giữa 0 và 1. RANDBETWEEN(n1, n2) Số nguyên ngẫu nhiên giữa hai số nguyên n1 và n2. ROUND(x,n) Làm tròn số x với độ chính xác đến con số thứ n - Nếu n 0 thì x đ−ợc làm tròn đến chữ số bên trái thứ n của dấu (chấm) thập phân Ví dụ : ô A1 chứa số 347 645.146, khi đó công thức =ROUND(A1,2) trả về 347 645.15 =ROUND(A1,1) trả về 347 645.10 =ROUND(A1,-3) trả về 348 000 (làm tròn đến hàng nghìn) SIGN(x) : Xác định dấu của một số. Trả về 1 nếu x > 0, 0 nếu x = 0, -1 nếu x<0. Ví dụ : = SIGN(10) trả về 1 = SIGN(4-4) trả về 0 = SIGN(7-25) trả về -1 SIN(x) : hàm Sin của x. SINH(x) : hàm Sin Hyperbolic của x. SQRT(x) : hàm căn bậc hai của x. SUM(n1, n2, ) : tổng của các số n1, n2, SUMif(range,criteria,sum_range) : Cộng những ô thoả điều kiện nào đó. Range Là vùng ô để so sánh với Criteria Criteria Là điều kiện cộng, có thể là số, chữ hoặc biểu thức. Quyêt định ô nào trong vùng Sum_Range sẽ đ−ợc cộng Sum_Range Là vùng ô sẽ đ−ợc cộng Các ô trong Sum_range chỉ đ−ợc cộng nếu các ô t−ơng ứng với nó trong Range thoả mãn Criteria Xét bảng trên trang 55, để tính tổng l−ơng của những ng−ời + là Nữ, dùng công thức : = SUMIF(C2 : C10, “Nữ”,D2 : D10) cho kết quả 280 + của những ng−ời có L−ơng từ 50 trở lên, dùng công thức :
Khoa CNTT - VĐH Mở Excel 34 = SUMIF(D2 : D10,">50",D2 : D10) (chú ý dấu nháy kép ” ) + Tên bắt đầu bằng chữ H dùng công thức : = SUMIF(A2 : A10,”H*”,D2 : D10) (chú ý dấu nháy kép ” và dấu * ) Hàm SUMIF chỉ tính tổng theo 1 điều kiện. Nếu cần tính tổng theo 2 điều kiện trở lên phải dùng công thức mảng hoặc hàm DSMUM (xem trang 57) TAN(x) : hàm Tang của góc x. TANH(x) : hàm Tang Hyperbolic của x. TRUNC(x) Cắt bỏ phần thập phân của số x để chỉ lấy phần nguyên. Ví dụ : = TRUNC(2.789) trả về 2 = TRUNC(-2.4) trả về -2, trong khi đó = INT(-2.4) trả về -3 3.4. Hàm Logic AND(logic1,logic2, ) Nhận giá trị TRUE (Đúng) nếu tất cả các biểu thức logic1, logic2, đều là TRUE, nhận giá trị FALSE (Sai) nếu có ít nhất một đối số là FALSE Ví dụ : =AND(5>3,6>4) trả về TRUE =AND(5>3,6 =16,"Ng−ời lớn","Trẻ em") cho kết quả là Ng−ời lớn nếu tuổi từ 16 trở lên, là Trẻ em nếu tuổi d−ới 16 Để kiểm tra xem ô D2 chứa số nào dùng = IF(D2>0,"Số d−ơng", IF(D2=0,"Số không",Số âm")) NOT(logic) : hàm phủ định. Ví dụ : = NOT(1+1>2) trả về TRUE = NOT(1+1=2) trả về FALSE OR(logic1,logic2, ) Nhận giá trị TRUE nếu một trong các biểu thức logic1,logic2, là TRUE, nhận giá trị FALSE nếu tất cả các biểu thức đó là FALSE. Ví dụ : =OR(5>3,6<=4) trả về TRUE =OR(5<3,6<=4) trả về FALSE
Khoa CNTT - VĐH Mở Excel 35 TRUE( ) : nhận giá trị logic TRUE. 3.5. Hàm thống kê AVERAGE(num1, num2, ) Tính trung bình cộng của các số num1, num2, Ví dụ : =AVERAGE(10,8,9,3) trả về 7.5 COUNT(Địa chỉ) : tính số các ô dữ liệu kiểu số trong miền Địa chỉ Ví dụ : Các ô từ A1 đến E5 chứa các giá trị sau : A B C D E 1 STT Tên sách Số l−ợng Đơn giá Thành tiền 2 1 Excel 5.0 10 15000 150000 3 2 Word 6.0 15 14000 210000 4 3 Access 5 12000 60000 5 Cộng 30 420000 khi đó, công thức = COUNT(A1:E5) trả về 14 COUNTA(DC) Tính số các ô không rỗng trong vùng DC. Ví dụ : = COUNTA(A1:E5) trả về 23 (có 2 ô rỗng là A5 và D5) LARGE(DC,k) : Phần tử lớn thứ k trong vùng DC. Ví dụ : = LARGE(E1:E5,1) trả về 420000 = LARGE(E1:E5,3) trả về 150000 MAX(num1,num2, ) : giá trị lớn nhất của các số num1,num2, Ví dụ : = MAX(E1:E5) trả về 420000 MIN(num1,num2, ) : giá trị nhỏ nhất của các số num1,num2, Ví dụ : = MIN(E1:E5) trả về 60000 MODE(DC) : trả lại giá trị hay gặp nhất trong vùng DC. Ví dụ : = MODE(1,2,4,5,2) trả về 2 RANK(số, Danh sách, Tuỳ chọn) : xác định thứ hạng của số so với chuỗi các số trong danh sách, tức là xem số đó đứng thứ mấy trong chuỗi số - xếp giảm dần nếu không có Tuỳ chọn hoặc Tuỳ chọn bằng 0 (Ví dụ 1). - xếp tăng dần nếu Tuỳ chọn là một số lớn hơn 0 (Ví dụ 2). Địa chỉ của Danh sách phải là tuyệt đối.
Khoa CNTT - VĐH Mở Excel 36 Ví dụ : 1. Giả sử ở các ô E3:E12 ghi điểm trung bình của các thí sinh, để xếp thứ căn cứ vào điểm trung bình, tại ô F3 nhập =RANK(E3,$E$3:$E$12) hoặc =RANK(E3,$E$3:$E$12,0), sau đó copy công thức này xuống các ô từ E4 đến E12. Ví dụ : 2. Giả sử ở các ô B3:B12 ghi thời gian chạy 100 m của các vận đọng viên, để xếp thứ căn cứ vào thời gian, tại ô C3 nhập =RANK(B3,$B$3:$B$12,1), sau đó copy công thức này xuống các ô từ C4 đến C12. Có thể thay số tham số thứ 3 bằng một số bất kỳ khác 1. SMALL(DC,k) : phần tử nhỏ thứ k trong vùng DC. Ví dụ : = SMALL(E1:E5,1) trả về 60000 = SMALL(E1:E5,3) trả về 210000 3.6. Hàm Tìm kiếm và Tham chiếu vlookup(lookup_value, table_array, col_index_num, range_lookup) Lookup_value Giá trị đ−ợc tìm kiếm trên cột bên trái của Table_array Table_array Vùng tìm kiếm hay còn gọi là bảng tra cứu, địa chỉ phải là tuyệt đối, nên đặt tên cho vùng (xem trang 11 về cách đặt tên). Col_index_num Số thứ tự cột trong table_array, nơi VLOOKUP sẽ lấy giá trị trả về Range_lookup Giá trị logic xác định việc tìm kiếm là chính xác hay gần đúng, nếu là: True hay 1 Cột đầu tiên phải đ−ợc sắp xếp tăng dần (khi đó có thể bỏ qua tham số thứ 4 này). Khi không thấy sẽ lấy kết quả gần đúng, vì thế còn gọi là dò tìm không chính xác. False hay 0 Cột đầu tiên không cần sắp xếp .Tìm chính xác, trả về #N/A nếu không thấy. (Xem ví dụ ở trang 72) Ví dụ : Bảng sau là kết quả thi của học sinh, dựa vào điểm Trung bình hãy xếp loại học sinh theo thang điểm : Kém 5 Trung bình 7 Khá 8 Giỏi 9.5 Xuất sắc Để dùng hàm VLOOKUP, ta cần thực hiện các b−ớc sau : - Trong miền C15:D19 gõ vào thang điểm trên d−ới dạng cột. Vì đây là cách dò tìm không chính xác (trong một khoảng) nên chỉ gõ vào cận d−ới (theo chiều tăng) của mỗi loại. Nh− vậy : lookup_value là E3 (điểm Trung bình của học sinh thứ nhất) table_array là miền $C$15: $D$19 (miền địa chỉ tuyệt đối, không đ−a hàng tiêu đề C14: D14 vào)
Khoa CNTT - VĐH Mở Excel 37 col_index_num là 2 vì cần lấy giá trị của cột Loại, cột này có số thứ tự là 2 trong miền $C$14: $D$19. range_lookup là 1 hoặc TRUE đều đ−ợc, có thể bỏ qua tham biến này - Tại ô G3 điền vào công thức = VLOOKUP(E3,$C$14: $D$19,2) ta nhận đ−ợc Trung bình - Copy công thức ở ô G3 xuống các ô từ G4 đến G12, Excel sẽ xếp loại cho các học sinh còn lại. A B C D E F G 2 TT Tên Toán Tin Trung bình Xếp thứ Xếp loại 3 1 Hùng 4 7 5.5 4 2 Bình 6 8 7.0 Khá 5 3 Vân 8 9 8.5 Giỏi = VLOOKUP(E3,$C$14: $D$19,2) 6 4 Bình 9 10 9.5 Xuất sắc 7 5 Doanh 5 8 6.5 Trung bình 8 6 Loan 5 4 4.5 Kém 9 7 Anh 9 6 7.5 Khá 10 8 Thu 3 5 4.0 Kém 11 9 Khánh 6 7 6.5 Trung bình 12 10 Ngân 10 8 9.0 Giỏi 13 14 Điểm Loại 15 0 Kém 16 5 Trung bình 17 7 Khá 18 8.5 Giỏi 19 9.5 Xuất sắc 20 21 Điểm 0 5 7 8.5 9.5 22 Loại Kém Trung bình Khá Giỏi Xuất sắc hlookup(lookup_value, table_array, row_index_num,range_lookup) Hàm này hoạt động giống nh− hàm VLOOKUP, điểm khác là : Lookup_value Giá trị đ−ợc tìm kiếm trên hàng đầu tiên của Table_array Table_array Vùng tìm kiếm viết thành hàng Row_index_num Số thứ tự hàng trong table_array, nơi HLOOKUP sẽ lấy giá trị trả về - Trong miền A21: F22 ta gõ vào thang điểm trên d−ới dạng hàng, chú ý chỉ gõ vào cận d−ới (theo chiều tăng) của mỗi loại. Nh− vậy : lookup_value là E3 (điểm Trung bình của học sinh thứ 1)
Khoa CNTT - VĐH Mở Excel 38 table_array là miền $B$21: $F$22 (miền địa chỉ tuyệt đối, không đ−a cột tiêu đề A21: A22 vào) row_index_num là 2 vì cần lấy giá trị của hàng Loại, hàng này có số thứ tự là 2 trong miền B21: F22. - Tại ô F3 điền vào công thức = HLOOKUP(E3,$B$21: $F$22,2), ta nhận đ−ợc Trung bình - Copy công thức ở ô G3 xuống các ô từ G4 đến G12, Excel sẽ xếp loại cho các học sinh còn lại. 3.7. Hàm Cơ sở dữ liệu Xem mục IX. 4. Các hàm CSDL (trang 55) v. đồ thị Khả năng biểu diễn số liệu bằng đồ thị của Excel rất phong phú. Các biểu đồ đ−ợc cài đặt trên bảng tính tăng thêm sức hấp dẫn và thuyết phục của số liệu. 1. Các b−ớc tạo đồ thị mới 1 - Chọn miền dữ liệu cần đ−a vào vẽ đồ thị, ví dụ B2:F6, chú ý chọn cả hàng tiêu đề 1992, 1993, và cột tên các bảng tính để làm nhãn cho đồ thị. 1 -Chọn biểu t−ợng (ChartWizard), con trỏ chuột trở thành dấu + 2 -Xác định miền hình chữ nhật tại vùng trống của bảng tính để đ−a đồ thị vào bằng cách trỏ chuột vào đỉnh trái trên của nó, ấn và giữ nút trái đồng thời kéo chuột về đỉnh phải d−ới, thả nút chuột, xuất hiện hộp thoại ChartWizard - Step 1 of 5 3 -Nếu cần, sửa lại địa chỉ của miền dữ liệu đ−a vào vẽ đồ thị trong khung Range bằng cách gõ trực tiếp hoặc dùng chuột chọn miền dữ liệu cần thiết (xem trang 13)
Khoa CNTT - VĐH Mở Excel 39 ý nghĩa các nút trong hộp thoại nh− sau : Cancel : huỷ bỏ việc vẽ đồ thị Next : chuyển sang b−ớc tiếp theo Back : quay trở lại b−ớc tr−ớc Finish : tự động thực hiện tất cả các b−ớc cho đến kết thúc Nếu chọn Next, xuất hiện hộp thoại ChartWizard - Step 2 of 5 với 15 kiểu đồ thị của Excel (9 nhóm loại hai chiều và 6 nhóm loại 3 chiều). 5 -Chọn một kiểu đồ thị (ví dụ 3-D Column), có thể gõ ký tự có gạch chân của kiểu cần thiết rồi ấn ↵ .Chú ý rằng loại Pie chỉ áp dụng cho một dãy số, và th−ờng dùng để so sánh một với tất cả các yếu tố, ví dụ so sánh số hàng bán đ−ợc của 1 quý với cả năm. Chọn Next, xuất hiện hộp thoại ChartWizard - Step 3 of 5 với các dạng của kiểu đồ thị đ−ợc chọn.
Khoa CNTT - VĐH Mở Excel 40 6 -Chọn một dạng cho đồ thị, nếu dùng bàn phím, gõ số thứ tự của dạng cần chọn (ví dụ gõ số 4). Chọn Next, xuất hiện hộp thoại ChartWizard - Step 4 of 5 với đồ thị của miền dữ liệu đ−ợc chọn. 7 -Nếu cần thay đổi, sửa các thông số sau đây cho đồ thị : Data Series in : Vẽ dữ liệu theo hàng (Rows) hay Cột (Columns) Use First Colums (Rows): Sử dụng Cột (Hàng) thứ for Category [X] Axis Labels : làm tiêu đề cho trục X for Legend Text : làm văn bản cho chú thích. Chọn Next, xuất hiện hộp thoại ChartWizard - Step 5 of 5. 8 -Điền các thông số sau : Add a Legend : (Yes/No) : Có bổ sung chú thích của các dãy số liệu vào đồ thị không ?
Khoa CNTT - VĐH Mở Excel 41 Chart Titles : Tiêu đề chung của đồ thị Axis Titles : Tiêu đề của các trục Category [X] : Tiêu đề cho trục X Value [Z] : Tiêu đề cho trục Z (trong không gian 3 chiều) Sau các b−ớc trên, một đồ thị sẽ đ−ợc đ−a vào bảng tính tại vị trí đã định. 2. Thiết lập lại đồ thị 1 -Chọn đồ thị bằng cách trỏ chuột vào trong lòng nó và bấm nút trái. Xuất hiện khung bao quanh đồ thị với các chấm vuông ở các góc và giữa các cạnh. 2 -Chọn biểu t−ợng ChartWizard, Excel lần l−ợt đ−a ta trở lại các b−ớc đã nêu trên.
Khoa CNTT - VĐH Mở Excel 42 3. Chỉnh sửa đồ thị a - Chỉnh sửa các đối t−ợng : 1 -Trỏ chuột vào trong lòng đồ thị và nháy đúp, xuất hiện khung chữ nhật có cạnh là các sọc chéo với chấm vuông hoặc một cửa sổ riêng. 2 -Để sửa kiểu đồ thị chọn Format, Chart Type Để sửa các đối t−ợng khác, nháy đúp tại đối t−ợng cần thiết (ví dụ tiêu đề chung của đồ thị), xuất hiện khung chữ nhật với chấm vuông tại các góc và giữa các cạnh. Chọn Format. Nội dung mục đầu tiên của menu dọc tuỳ thuộc việc tr−ớc đó đối t−ợng nào đ−ợc chọn, nh− trong ví dụ này là Select Chart Title (tiêu đề của đồ thị). Chọn mục này và tiến hành các chỉnh sửa cần thiết. Để Cần Thay đổi kích th−ớc Trỏ chuột vào chấm vuông sao cho xuất hiện mũi tên 2 đầu, kéo và thả nó tại vị trí mới. Chuyển đối t−ợng Trỏ chuột vào một cạnh sao cho xuất hiện Û, kéo và thả nó tại vị trí mới. Xoá một đối t−ợng ấn phím Delete Kết thúc sửa Trỏ chuột vào vùng ngoài đồ thị bấm nút trái. b - Bổ sung / bỏ đ−ờng kẻ l−ới : 1 -Trỏ chuột vào trong lòng đồ thị và nháy đúp, xuất hiện khung chữ nhật có cạnh là các sọc chéo với chấm vuông. 2 -Chọn Insert, Gridlines. Đánh dấu chọn vào các ô cần thiết để bổ sung các đ−ờng kẻ l−ới, bỏ dấu chọn để huỷ bỏ các đ−ờng này: Major Gridlines Đ−ờng kẻ chính Minor Gridlines Đ−ờng kẻ phụ
Khoa CNTT - VĐH Mở Excel 43 c - Bổ sung thêm đ−ờng biểu diễn : Ví dụ : để bổ sung thêm đ−ờng biểu diễn số liệu của năm 1996 vào đồ thị : - Chọn miền dữ liệu cần bổ sung (miền G2:G6) - Kéo và thả miền này vào đồ thị. 4. Đồ thị kiểu đèn chiếu (Slideshow) Có thể tạo nhiều dạng đồ thị cho một hoặc nhiều bảng tính sau đó cho trình chiếu ra màn hình nh− phim đèn chiếu với các kiểu màn hình, thời gian chuyển và âm thanh phát ra tr−ớc mỗi lần chuyển. a - Tạo đồ thị kiểu đèn chiếu theo các b−ớc sau : 1. Mở một hoặc nhiều bảng tính đã tạo đồ thị. 2. Chọn File, New. Hộp thoại New xuất hiện (*) 3. Chọn Sildes 4. Chọn OK (hoặc ấn ↵ ). Xuất hiện màn hình Slides1 5. Từ menu chính chọn mục Windows, chọn bảng tính có các đồ thị đã vẽ. 6. Chọn đồ thị cần đ−a vào đèn chiếu. * Nếu không xuất hiện hộp thoại này, ta chọn mục Tools, Add-In, sau đó đánh dấu chọn vào mục Slideshow Template (nếu mục này có, nếu không, phải cài bổ sung tính năng này cho Excel).
Khoa CNTT - VĐH Mở Excel 44 7. Từ menu chính chọn mục Edit, Copy hoặc ấn Ctrl + V, xuất hiện đ−ờng đứt nét chạy xung quanh đồ thị. 8. Chọn Windows, chọn tiếp Slides1. Xuất hiện màn hình Slides1 nh− trên. 9. Chọn nút Paste Slide. Xuất hiện hộp thoại Edit Slide : Effect : Chọn các kiểu màn hình chuyển tiếp giữa hai đồ thị : Cut throught black : Màu đen Fade : Sáng (tối) dần Vertical blinds : Mành cuốn theo chiều đứng Horizontal blinds : Mành cuốn theo chiều ngang Test : Xem thử kiểu màn hình chuyển tiếp. Advance : Đặt một trong hai tuỳ chọn sau : Manual : Nếu chọn, sẽ tắt chế độ tự động chuyển tiếp từ đồ thị này sang đồ thị khác. Khi trình chiếu ta phải ấn phím dấu cách hoặc nút chuột thì Excel mới chuyển sang đồ thị sau. Time : Tự động chuyển tiếp từ đồ thị này sang đồ thị khác sau thời gian bao nhiêu giây (phải cung cấp con số tại ô này). Sound : Đặt âm thanh cho kiểu đèn chiếu. Choose : Chọn file âm thanh (có tên *.WAV) trong th− mục Windows Test Sound : Nghe thử âm thanh Clear : Xoá bỏ việc cài đặt âm thanh 10. Chọn OK để kết thúc việc tạo kiểu đồ thị đèn chiếu. Màn hình Slides1 xuất hiện. 11. Lặp lại các b−ớc từ 5 đến 10 để tạo kiểu đồ thị đèn chiếu khác. 12. Để trình diễn các kiểu đồ thị đèn chiếu, chọn nút Start Show của màn hình Slide1. Hộp thoại Start Show xuất hiện
Khoa CNTT - VĐH Mở Excel 45 - Nếu đánh dấu chọn vào ô Repeat show until 'Esc' is pressed thì để ngừng việc trình diễn ta phải ấn phím Esc - Trong hộp Initial Slide : Chỉ định trình diễn đồ thị đèn chiếu thứ mấy. 13. Để ghi lại tệp các kiểu đồ thị đèn chiếu, chọn nút File, chọn tiếp : - Save : Chấp nhận tên file mặc định là SLIDE1.XLS - Save As : Đặt tên khác cho file b - Sửa đồ thị kiểu đèn chiếu : 1. Mở file đèn chiếu cần chỉnh sửa. Cửa sổ màn hình của đèn chiếu xuất hiện. 2. Thực hiện các thao tác chỉnh sửa : - Bổ sung thêm đồ thị đèn chiếu : mở bảng tính có các đồ thị đã vẽ rồi thực hiện các b−ớc từ 5 - 10 của mục a ở trên. - Chỉnh sửa các thông số của một kiểu đồ thị : + Chọn đồ thị (Slide Image) cần chỉnh sửa + Chọn nút Edit Slide + Sửa các thông số theo ý muốn - Xoá một kiểu đồ thị : + Chọn đồ thị (Slide Image) cần xoá + Chọn nút Delete Row 3. Kết thúc việc sửa, chọn biểu t−ợng Save c - Trình diễn đồ thị kiểu đèn chiếu : 1. Mở file đèn chiếu cần thiết. 2. Chọn nút Start Show (b−ớc 12 của mục a ở trên) vIi. kiểu trình bày Giống trong Word, các kiểu trình bày (Style) giúp định dạng các ô của bảng tính một cách nhanh chóng và thống nhất.
Khoa CNTT - VĐH Mở Excel 46 1. Tạo một kiểu trình bày mới - Đ−a con trỏ về ô (hoặc chọn miền) cần tạo, nếu là bảng tính mới đ−a con trỏ về ô A1. - Chọn mục Format, Style. Xuất hiện hộp thoại Style - Trong khung Style Name chọn kiểu trình bày đã tạo hoặc gõ vào tên cho kiểu trình bày mới. - Chọn nút Modify. Xuất hiện hộp thoại Format Cells (xem trang 18) Number : Định dạng số Aligment : Canh biên (dóng hàng) Font : Định dạng ký tự Border : Kẻ khung Pattern : Đặt mầu nền Protection : Bảo vệ ô - Tiến hành định dạng dữ liệu. - Chọn Add để bổ sung kiểu trình bày mới. - Chọn OK để kết thúc. 2. áp dụng một kiểu trình bày a. Dùng Menu - Đ−a con trỏ về ô (hoặc chọn miền) cần áp dụng - Chọn Format, Style - Chọn kiểu trình bày t−ơng ứng từ danh sách Style Name - OK b. Dùng thanh công cụ (giống thao tác copy định dạng của Word) 1 - Đ−a con trỏ về ô (hoặc chọn miền) cần áp dụng 2 - Chọn biểu t−ợng Format Painter (chổi sơn). Con trỏ chuột lúc này có kèm theo chổi sơn.
Khoa CNTT - VĐH Mở Excel 47 3 - Dùng con trỏ chuột để chọn (bôi đen) nhóm ô cần định dạng theo kiểu trình bày. Để áp dụng một kiểu trình bày cho các miền rời rạc (cách xa nhau), ở b−ớc 2 ta cần ấn đôi nút chuột tại biểu t−ợng Format Painter. Sau b−ớc 3, chọn biểu t−ợng này lần nữa để kết thúc việc. vIii. Macros Cũng giống trong Word, các Macro của Excel dùng để tự động các công việc mà ta phải th−ờng xuyên thực hiện. Chúng có thể gán cho phím nóng, thanh công cụ hoặc menu để tiện cho việc sử dụng. 1. Ghi một Macro mới 1 - Chọn Tools, Record - Chọn Macro Record New để thu Macro mới - Chọn Use Relative Reference để thu Macro theo địa chỉ t−ơng đối, nghĩa là nếu khi thu macro, thao tác thực hiện trên một ô thì khi chạy, thao tác đó sẽ đ−ợc thực hiện tại một ô khác hoặc một dãy các ô. Xuất hiện hộp thoại Record New Macro : 2 - Trong ô Macro Name gõ vào tên cho Macro (bất đầu bằng chữ cái, sau đó là các chữ cái hoặc con số, không đ−ợc dùng các ký hiệu, dấu cách hoặc dấu tiếng Việt). 3 - Trong ô Description gõ vào lời mô tả các công việc mà Macro này sẽ thực hiện. 4 - Chọn Options. Trong khung Assign to chọn cách gán Macro cho Menu hay tạo phím gõ tắt cho nó.
Khoa CNTT - VĐH Mở Excel 48 Stop Macro ♦ Nếu đánh dấu chọn vào mục Menu Item on Tools Menu, phải gõ vào tên của mục. Sau này nếu chọn mục Tools từ menu chính, ta sẽ thấy mục này đ−ợc xếp vào cuối của menu dọc. ♦ Nếu đánh dấu chọn vào mục Shortcut Key, Excel đề nghị một ký tự bắt đầu bằng chữ e, nếu muốn ta có thể xoá chữ e và gõ vào ký tự khác. Sau này để chạy Macro ấn tổ hợp phím Ctrl+phím này. L−u ý rằng phím tắt trong Excel phân biệt chữ hoa với chữ th−ờng. Ví dụ khi gõ vào chữ E làm phím tắt, lúc thực hiện macro phải bấm Ctrl+Shift+e. 5 - Trong khung Store in, chọn Personal Macro Workbook để có thể dùng Macro này trong các bảng tính khác, nếu không Macro chỉ có tác dụng tại tập bảng tính hiện thời (This WorkBook) hoặc trong bảng tính mới (New WorkBook). 6 - Chọn Visual Basic để các thao tác mà ta thu đ−ợc thể hiện bằng ngôn ngữ này trong một đơn thể (Module) 7 - Chọn OK để bất đầu ghi lại các thao tác. Nh− ở ví dụ này, ta cần chọn các biểu t−ợng %, lấy thêm số lẻ, in đậm, gạch chân. 8 - Chọn nút Stop để kết thúc việc ghi Macro. Nếu không thấy nút này, chọn mục Tools, Record Macro, Stop Recording. 2. Gán Macro cho thanh công cụ - Chọn View, Tools. Xuất hiện hộp thoại Toolbars - Chọn nút Customize. - Trong khung Categories chọn Custom. - Kéo một biểu t−ợng ít dùng nào đó trên thanh công cụ và thả trên bảng tính để có chỗ. Kéo một nút của hộp thoại Customize và thả trên thanh công cụ. Xuất hiện hộp thoại Assign Macro.
Khoa CNTT - VĐH Mở Excel 49 - Chọn tên của Macro cần thiết (nếu có) từ danh sách. Có thể chọn nút Record và thực hiện các b−ớc từ 2 đến 8 của mục 1. ở trên để tạo và gán một Macro mới cho thanh công cụ. - Chọn OK - Chọn Close 3. Chạy Macro a - Đ−a con trỏ về chỗ cần áp dụng Macro. b - Chọn biểu t−ợng của Macro, hoặc từ Menu chính chọn Tools, sau đó chọn tiếp mục cần thiết, hoặc ấn tổ hợp phím nóng tuỳ theo tr−ớc đó ta đã gán Macro cho thanh công cụ, Menu hoặc ấn tổ hợp phím nóng. Cũng có thể chạy một Macro theo cách sau : - Chọn Tools, Macro. Hộp thoại Macro hiện ra. - Chọn Macro cần thiết từ danh sách Macro Name. - Chọn nút Run. 4. Xoá Macro - Chọn Tools, Macro. Hộp thoại Macro hiện ra. - Chọn Macro cần xoá từ danh sách Macro Name. - Chọn Delete, Yes Ix. quản trị dữ liệu 1. Khái niệm cơ bản Cơ sở dữ liệu (CSDL) là tập hợp các dữ liệu đ−ợc sắp xếp trên một vùng chữ nhật (gồm ít nhất 2 hàng) của bảng tính theo quy định sau : − Hàng đầu tiên ghi các tiêu đề của dữ liệu, mỗi tiêu đề trên một cột. Các tiêu đề này đ−ợc gọi là tr−ờng (field) − Từ hàng thứ hai trở đi chứa dữ liệu, mỗi hàng là một bản ghi (record) - Chú ý : + Tên các tr−ờng phải là dạng ký tự, không đ−ợc dùng số, công thức, toạ độ ô ).Nên đặt tên tr−ờng ngắn gọn, không trùng lắp. + Không nên có miền rỗng trong CSDL 2. Sắp xếp dữ liệu - Nếu định sắp xếp cho toàn bộ CSDL: về ô bất kỳ của nó. Nếu chỉ định sắp xếp cho một số bản ghi : chọn miền dữ liệu cần đ−a vào sắp xếp. Xuất hiện hộp thoại Sort. Excel có thể sắp xếp theo 3 khoá (điều kiện). Chọn (bấm nút chuột tại) ỉ của khung này. Xuất hiện danh sách trải xuống ghi tên hoặc thứ tự các tr−ờng. Chọn tr−ờng cần thiết.
Khoa CNTT - VĐH Mở Excel 50 Sau đây là ý nghĩa các mục : - Sort by : cột −u tiên nhất trong khoá sắp xếp. - Then by : cột −u tiên thứ hai và thứ ba trong khoá sắp xếp - Ascending : sắp xếp tăng dần - Desending : sắp xếp giảm dần - My List Has : Header Row (hoặc No Header Row): miền dữ liệu chứa (hoặc không chứa) hàng tiêu đề Nút Option - Case Sensitive : phân biệt chữ hoa với chữ th−ờng - Orientation : Sort Top To Bottom : sắp xếp các dòng trong CSDL Sort Left To Right : sắp xếp các cột trong CSDL Chọn OK để bắt đầu sắp xếp. Hình trên là hộp thoại Sort với các thông số để danh sách đ−ợc xếp theo vần A, B, C của Tên, những bản ghi trùng tên xếp ng−ời có điểm Trung bình cao lên trên, những bản ghi trùng điểm Trung bình xếp ng−ời có điểm Tin cao lên trên. 3. Lọc dữ liệu a - Các yếu tố cơ bản Để thực hiện lọc dữ liệu, phải xác định các yếu tố cơ bản sau trên bảng tính : - Miền dữ liệu (Database) : chứa toàn bộ dữ liệu cần xử lý, kể cả hàng tiêu đề. - Miền tiêu chuẩn (Criteria) : là miền bất kỳ trên bảng tính ngoài vùng CSDL, chứa các tiêu chuẩn (điều kiện mà các bản ghi phải thải mãn). Miền tiêu chuẩn gồm tối thiểu 2 hàng : hàng đầu chứa tiêu đề của miền tiêu chuẩn. Các tiêu đề này hoặc là tên tr−ờng hoặc là tên bất kỳ phụ thuộc vào ph−ơng pháp thiết lập tiêu chuẩn trực tiếp hay gián tiếp). Từ hàng thứ hai trở đi là tiêu chuẩn của CSDL.
Khoa CNTT - VĐH Mở Excel 51 Miền tiêu chuẩn so sánh trực tiếp (TCSSTT): cho phép đ−a vào các tiêu chuẩn để so sánh dữ liệu trong một tr−ờng với một giá trị nào đó. TCSSTT đ−ợc tạo ra theo nguyên tắc sau : − Hàng đầu ghi tiêu đề cho các tiêu chuẩn, lấy tên tr−ờng làm tiêu đề. − Hàng thứ hai trở đi để ghi các tiêu chuẩn so sánh, tr−ớc các giá trị đó có thể thêm các toán tử so sánh nh− >, >=, = 5 Trung bình từ 5 trở lên Tuổi Lọc ra những ng−ời 18 tuổi 18 Để lọc ra những ng−ời đạt điểm Trung bình từ 5 đến 8 (trong khoảng) làm nh− sau : Trên hàng tiêu đề của tiêu chuẩn phải có 2 ô đều ghi tr−ờng Trung bình, ngay phía d−ới ghi điều kiện (trên cùng một hàng) : Trung bình Trung bình >=5 8 Để lọc ra danh sách Nam hoặc Tuổi trên 21 : Điều kiện ghi trên hai hàng : Tuổi GT Nam > 21
Khoa CNTT - VĐH Mở Excel 52 Miền tiêu chuẩn so sánh gián tiếp (TCSSGT) hay còn gọi là tiêu chuẩn công thức: cho phép đ−a vào các tiêu chuẩn để so sánh dữ liệu hoặc một phần dữ liệu trong một tr−ờng với một giá trị nào đó. TCSSGT đ−ợc tạo ra theo nguyên tắc sau : − Hàng đầu ghi tiêu đề cho các tiêu chuẩn. Tiêu đề này có thể đặt bất kỳ nh−ng không đ−ợc trùng với tên tr−ờng nào. − Từ hàng thứ hai trở đi ghi các tiêu chuẩn so sánh, mỗi tiêu chuẩn là một công thức. Công thức này phải chứa địa chỉ của bản ghi đầu tiên. Kết quả thực hiện công thức này là một giá trị Logic : TRUE (Đúng) hoặc FALSE (Sai) Năm sinh =YEAR(C3) < 1975 Sinh tr−ớc 1975 Khi ấn ↵ , tại ô tiêu chuẩn này sẽ xuất hiện FALSE (vì Năm sinh của Hùng là 1978) Ngày sinh chẵn =MOD(DATE(C3),2) =0 Sinh vào Ngày chẵn Khi ấn ↵ , tại ô tiêu chuẩn này sẽ xuất hiện TRUE (vì Ngày sinh của Hùng là 30) - Miền đích (Copy to) : miền trống trên bảng tính, dùng để chứa các bản ghi đạt tiêu chuẩn. b - Lọc tự động (AutoFilter) - Chọn miền dữ liệu định lọc (kể cả hàng tiêu đề). - Data, Filter - Chọn AutoFilter, Excel tự động chèn những mũi tên vào bên phải của các tên tr−ờng. - Chọn ỉ tại cột chứa dữ liệu dùng làm tiêu chuẩn để lọc (ví dụ tại cột Xếp loại). - Chọn một trong các mục tại Menu : [All] : Hiện toàn bộ các bản ghi [Blanks] : Chỉ hiện các bản ghi trống [Nonblanks] : Chỉ hiện các bản ghi không trống [Custom ] : Dùng các toán tử so sánh (sẽ đ−ợc trình bày chi tiết trong phần tiếp theo)
Khoa CNTT - VĐH Mở Excel 53 Phần còn lại là danh sách giá trị của các bản ghi trong CSDL tại cột đó. Khi cần lọc các bản ghi theo một giá trị cụ thể nào đó chỉ cần chọn giá trị đó trong Menu (ví dụ chọn Trung bình). Dùng các toán tử so sánh Khi chọn mục này xuất hiện hộp thoại sau với 2 khung nhỏ để ghi tiêu chuẩn so sánh : Chọn 1 tiêu chuẩn : trong khung thứ nhất (khung trên) : - Nhấn nút chuột tại ỉ bên trái, chọn một toán tử so sánh (=, >, >=, ). Gõ vào hoặc bấm nút chuột tại ỉ bên phải, sau đó chọn một giá trị định so sánh. - Chọn OK Chọn 2 tiêu chuẩn : trong khung thứ hai : - Đặt tiêu chuẩn thứ nhất (nh− đã trình bày ở trên) - Chọn nút And (Và, trong khoảng đối với số) hoặc Or (Hoặc là, ngoài khoảng đối với số) - Đặt tiêu chuẩn thứ hai : bấm nút chuột tại ỉ bên trái, chọn một toán tử so sánh (=, >, >=, ). Gõ vào hoặc bấm nút chuột tại ỉ bên phải, sau đó chọn một giá trị định so sánh.
Khoa CNTT - VĐH Mở Excel 54 - Chọn OK Hình trên đặt tiêu chuẩn lọc danh sách học sinh Kém, Giỏi, Xuất sắc (điểm Trung bình =8.5) Huỷ lọc dữ liệu : - Huỷ lọc trong 1 cột : Bấm nút chuột tại ỉ của cột đó, chọn [All] - Huỷ lọc toàn bộ : - Data, Filter, chọn AutoFilter để xoá dấu 9 - Hiện lại tất cả các hàng trong danh sách đ−ợc lọc : - Data, Filter, Show All c - Lọc nâng cao (Advanced Filter) Advanced Filter dùng để tìm các bản ghi thoả mãn các điều kiện phức tạp hơn. Chức năng lọc nâng cao này ứng với với các tiêu chuẩn so sánh gián tiếp, bắt buộc phải dùng miền tiêu chuẩn. Các b−ớc nh− sau : - Tạo miền tiêu chuẩn. - Chọn miền dữ liệu định lọc - Data, Filter - Chọn Advanced Filter, ý nghĩa các mục trong hộp thoại Advanced Filter nh− sau : Action : Filter the List, in place : Lọc tại chỗ (ngay tại vị trí của CSDL chỉ hiện các bản ghi thoả mãn tiêu chuẩn lọc) Copy to Another Location : Trích các bản ghi đạt tiêu chuẩn lọc sang miền khác của bảng tính, địa chỉ của miền này đ−ợc xác định tại khung Copy to List Range : Địa chỉ miền dữ liệu nguồn đem lọc Criteria Range : Địa chỉ miền tiêu chuẩn Copy to : Địa chỉ miền đích để chứa các bản ghi đạt tiêu chuẩn lọc) Unique Record Only : Chỉ hiện 1 bản ghi trong số các bản ghi trùng nhau. Ví dụ : Để lọc ra danh sách các học sinh Nữ đạt điểm Trung bình từ 7 trở lên (Xếp loại từ Khá trở lên) của danh sách trên trang 55 ta điền vào các mục trong hộp thoại Advanced Filter nh− hình sau.
Khoa CNTT - VĐH Mở Excel 55 Kết quả là tại miền A17 : J20 ta có các bản ghi đạt tiêu chuẩn đã nêu. A B C D E F G H I J 1 kết quả thi cuối kỳ - lớp tin học cơ sở 2 TT Tên Ngày sinh Tuổi Gt Toán Tin Trung bình Xếp thứ Xếp loại 3 1 Hùng 30/01/78 Nam 4 7 5.5 8 Trung bình 4 2 Bình 21/08/74 Nữ 7 7 7.0 5 Khá 5 3 Vân 12/11/70 Nữ 8 9 8.5 3 Giỏi 6 4 Bình 15/06/77 Nữ 9 10 9.5 1 Xuất sắc 7 5 Doanh 05/12/76 Nam 5 8 6.5 7 Trung bình 8 6 Loan 18/09/77 Nữ 5 4 4.5 9 Kém 9 7 Anh 23/04/68 Nam 9 6 7.5 4 Khá 10 8 Thu 01/05/73 Nữ 3 5 4.0 10 Kém 11 9 Bình 26/02/71 Nam 6 8 7.0 5 Khá 12 10 Ngân 12/05/75 Nữ 10 8 9.0 2 Giỏi 13 14 Gt Trung bình 15 Nữ >=7 16 17 TT Doan Ngày sinh Tuổi Gt Toán Tin Trung bình Xếp th Xếp loại h 18 2 Bình 21/08/74 Nữ 7 7 7.0 5 Khá 19 3 Vân 12/11/70 Nữ 8 9 8.5 3 Giỏi 20 10 Ngân 12/05/75 Nữ 10 8 9.0 2 Giỏi 4. Các hàm Cơ sở dữ liệu (CSDL) Dùng để trả lại một giá trị từ CSDL theo một điều kiện nào đó. CSDL sau đây dùng để minh hoạ cho các ví dụ. A B C D 1 Tên Tuổi Giới tínhL−ơng 2 An 30 Nam 50 3 Hoà 28 Nữ 40 4 Thanh 35 Nam 80 5 Bình 29 Nữ 60 6 Vân 40 Nữ 100 7 Phúc 28 Nam 50 8 Kim 21 Nữ 30 9 Oanh 38 Nữ 50 10 Hùng 28 Nam 80 11 12 Vần H Tuổi Giới tính L−ơng 13 FALSE 28 Nữ >=50 14
Khoa CNTT - VĐH Mở Excel 56 DSUM (database, field, criteria) Tính tổng trên một cột (field) của CSDL (database) thoả mãn điều kiện ghi trong miền tiêu chuẩn (criteria) Ví dụ : để tính tổng l−ơng của Nữ, tại một ô trống nào đó của bảng tính, sau khi nhập công thức = DSUM(A1:D10, 4 ,C12:C13) ta nhận đ−ợc giá trị 280. Trong công thức này : A1:D10 là địa chỉ CSDL (database) C12:C13là địa chỉ miền tiêu chuẩn (criteria) 4 là số thứ tự của cột l−ơng (cột cần tính tổng) tính từ cột thứ nhất của CSDL (cột Tên), có thể thay con số này bằng "L−ơng"(bao trong dấu nháy kép) hoặc D1 (ô có tr−ờng L−ơng) Khi chỉ tính theo 1 điều kiện thì có thể thay hàm DSUM bằng SUMIF. Công thức trên có thể thay bằng =SUMIF(C2:C10,”Nữ”,D2:D10). Nh−ng khi tính theo từ 2 điều kiện trở lên hàm DSUM thể đ−ợc thay bằng công thức mảng (xem trang 12). Ví dụ để tính tổng L−ơng của những ng−ời là Nữ có L−ơng từ 50 trở lên, công thức sau : =DSUM(A1:D10,D1,C12:D13) sẽ đ−ợc thay bằng công thức mảng : ={SUM(IF(C2:C10=”Nữ”,1,0)*IF(D2:D10>=50,1,0)* D2:D10)} DAVERAGE (database, field, criteria) Tính giá trị trung bình cộng trên một cột (field) của CSDL (database) thoả mãn điều kiện ghi trong miền tiêu chuẩn (criteria) Ví dụ : để tính tuổi trung bình của Nữ, sau khi nhập công thức = DAVERAGE(A1:D10,2,C12:C13) ta nhận đ−ợc giá trị 30.4 (Tuổi là cột thứ 2 tính từ trái sang, vì vậy trong công thức trên có thể thay con số này bằng "Tuổi") DMAX (database, field, criteria) Tính giá trị lớn nhất trên một cột (field) của CSDL (database) thoả mãn điều kiện ghi trong miền tiêu chuẩn (criteria) Ví dụ : để tính L−ơng cao nhất của những ng−ời 28 tuổi, sau khi nhập công thức = DMAX(A1:D10,4,B12:B13) ta nhận đ−ợc giá trị 80 DMIN (database, field, criteria) Tính giá trị nhỏ nhất trên một cột (field) của CSDL (database) thoả mãn điều kiện ghi trong miền tiêu chuẩn (criteria)
Khoa CNTT - VĐH Mở Excel 57 Ví dụ : để tính L−ơng thấp nhất của những ng−ời 28 tuổi, sau khi nhập công thức = DMIN(A1:D10,4,B12:B13) ta nhận đ−ợc giá trị 40 DCOUNT (database, field, criteria) Đếm số bản ghi của CSDL (database) trên cột số (field) thoả mãn điều kiện ghi trong miền tiêu chuẩn (criteria) Ví dụ : để đếm số ng−ời là Nữ có L−ơng từ 50 trở lên, công thức : =DCOUNT(A1:D10,D1,C12:D13) cho ta kết quả là 3 Ví dụ : để đếm số ng−ời có chữ cái đầu của Tên là H, sau khi tạo miền TCSSGT với tiêu đề Vần H (hoặc nội dung bất kỳ) tại ô A12 và tiêu chuẩn =LEFT(A2,1)="H" tại ô A13, nhập công thức = DCOUNT(A1:D10, 2 ,D12:D13) ta nhận đ−ợc giá trị 2. Cần chú ý rằng tại vị trí tham biến thứ 2 (field) của hàm DCOUNT chỉ đ−ợc ghi số thứ tự (hoặc tên) của tr−ờng số bất kỳ chứ không đ−ợc ghi số thứ tự (hoặc tên) của tr−ờng ký tự hoặc ngày tháng. Nh− trong ví dụ trên có thể lấy số 4 (chứ không đ−ợc lấy số 1 hoặc 3) thay cho số 2. DCOUNTA (database, field, criteria) Đếm số ô không rỗng của cột bất kỳ (field) thoả mãn điều kiện ghi trong miền tiêu chuẩn (criteria). Khác với DCOUNT, trong công thức của hàm này có thể ghi số thứ tự hoặc tên của tr−ờng bất kỳ nào của CSDL. 5. Tổng kết theo nhóm a - Tổng kết theo một loại nhóm (SubTotal) : Chức năng mới này của Excel 5.0 cho phép tạo các dòng tổng kết trong một CSDL. SubTotal sẽ chèn tại các vị trí cần thiết các tính toán thống kê theo yêu cầu của ng−ời sử dụng. Các b−ớc thực hiện nh− sau : 1/ Sắp xếp dữ liệu theo tr−ờng cần tạo SubTotal (ở ví dụ trên là tr−ờng Xếp loại). 2/ Chọn Data, Subtotals, xuất hiện hộp thoại Subtotal. Trong đó :
Khoa CNTT - VĐH Mở Excel 58 − At Each Change in : Chọn tr−ờng mà theo tr−ờng này, tại mỗi vị trí thay đổi, Excel sẽ chèn vào một dòng Tổng kết - tức là dòng thực hiện các phép tính (ở ví dụ trên là tr−ờng Xếp loại, cứ mỗi khi chuyển sang loại học sinh khác Excel lại chèn dòng kết quả tính vào tại các dòng 5,8,12, 15,17). − Use Function : Chọn hàm để tính toán tổng kết dữ liệu. Hàm ngầm định là SUM. ở ví dụ trên dùng hàm Average để tính giá trị trung bình của một số tr−ờng. − Add SubTotal to : Chọn các tr−ờng cần tính toán (ở ví dụ trên là tính Trung bình các tr−ờng Tuổi, Toán, Tin, Trung bình). Các tuỳ chọn khác : − Replace Current SubTotal : Khi tạo dòng tổng kết mới dòng này sẽ thay thế dòng tổng kết cũ. Theo ngầm định các dòng tổng kết sẽ nối tiếp nhau. − Page Break Between Group : Chèn dấu ngắt trang tại mỗi vị trí có dòng SubTotal (tức là đ−a mỗi nhóm sang một trang riêng biệt). − Summary Below Data : Đặt dòng tổng kết ở cuối mỗi nhóm. Nếu bỏ dấu : dòng này sẽ đ−ợc đ−a lên tr−ớc mỗi nhóm. − Remove All : Huỷ bỏ mọi SubTotal đã thực hiện. 3/ Chọn Ok để bắt đầu tạo. b - Tổng kết theo nhiều loại nhóm (PivotTable - Bảng Tổng hợp) : Chức năng này của Excel cho phép tự động hoá quá trình tổng kết theo nhiều loại nhóm, phân tích và đánh giá số liệu mà sau đây chúng tôi gọi là
Khoa CNTT - VĐH Mở Excel 59 Bảng Tổng hợp. Bảng sau là một CSDL về doanh thu của một cơ quan kinh doanh gồm 2 cửa hàng ký hiệu là Số 1 và Số 2. A B C D E 1 Ngày Nhân viên Cửa hàng Sản phẩm Tiền 2 29-08-97 H−ơng Số 1 Kẹo 20 3 29-08-97 Lan Số 1 Bánh 10 4 30-08-97 Chi Số 1 Bánh 40 5 30-08-97 Nga Số 2 Mứt 15 6 30-08-97 Vân Số 2 Kẹo 25 7 03-09-97 Chi Số 1 Bánh 30 8 03-09-97 Lan Số 1 Kẹo 45 9 03-09-97 Nga Số 2 Kẹo 20 10 04-09-97 Chi Số 1 Mứt 10 11 04-09-97 Vân Số 2 Bánh 35 Từ CSDL trên, tổng hợp theo từng cửa hàng, từng nhân viên và từng ngày cho bảng Tổng hợp sau : Cửa hàng (All) ỉ Sum of Tiền Sản phẩm Nhân viên Ngày Bánh Kẹo Mứt Grand Total Chi 03-09-97 30 0 0 30 04-09-97 0 0 10 10 30-08-97 40 0 0 40 Chi Total 70 0 10 80 H−ơng 29-08-97 0 20 0 20 H−ơng Total 0 20 0 20 Lan 03-09-97 0 45 0 45 29-08-97 10 0 0 10 Lan Total 10 45 0 55 Nga 03-09-97 0 20 0 20 30-08-97 0 0 15 15 Nga Total 0 20 15 35 Vân 04-09-97 35 0 0 35 30-08-97 0 25 0 25 Vân Total 35 25 0 60 Grand Total 115 110 25 250
Khoa CNTT - VĐH Mở Excel 60 Các khái niệm cơ bản ở bảng CSDL trên, tiêu đề của các cột là các "tr−ờng" (field): Ngày, Nhân viên, Cửa hàng, Sản phẩm và Tiền. Các tr−ờng này sẽ tham gia vào việc xây dựng bảng Tổng hợp này. Bảng Tổng hợp đ−ợc chia làm 4 vùng : Page Field : Toàn bộ dữ liệu đ−ợc tổng kết theo từng nhóm (Item) của tr−ờng này. Page Field luôn nằm ở phía trên của bảng Tổng hợp. Trong ví dụ này, Page Field là Cửa hàng gồm 2 nhóm Số 1 và Số 2. Row Field : Mỗi nhóm dữ liệu của tr−ờng này đ−ợc tổng kết trên một dòng, vì vậy đ−ợc gọi là "Row". Nếu số Row Field nhiều hơn 1, PivotTable sẽ tổng kết các tr−ờng này theo kiểu lồng nhau theo thứ tự từ trên xuống d−ới. Trong ví dụ này, có 2 Row Field là Nhân viên và Ngày, tr−ờng Nhân viên đ−ợc tổng kết tr−ớc rồi mới đến tr−ờng Ngày. Column Field : Mỗi nhóm dữ liệu của tr−ờng này đ−ợc tổng kết trên một cột, vì vậy đ−ợc gọi là "Column". Nếu số Nhóm này nhiều hơn 1, PivotTable sẽ tổng kết các nhóm này theo thứ tự từ trái qua phải. Trong ví dụ này, các Nhóm đ−ợc tổng kết thành các cột (tr−ờng) theo thứ tự ABC của tên nhóm : Bánh, Kẹo Mứt. Data Field : vùng chính của Bảng Tổng hợp ghi kết quả của một phép toán. Trong ví dụ này, số liệu cuả tr−ờng Tiền đ−ợc đ−a vào và phép toán tổng kết là lấy tổng (SUM). Tạo mới một Bảng Tổng hợp - Chọn miền dữ liệu kể cả tiêu đề của các cột (ví dụ A1:E11) - Chọn mục Data, PivotTable Hộp thoại PivotTable Wizard step 1 of 4 xuất hiện. - Chọn Microsoft Excel List of Database - Chọn Next Hộp thoại PivotTable Wizard step 2 of 4 xuất hiện. Nếu cần, sửa lại địa chỉ của miền dữ liệu bằng cách gõ trực tiếp hoặc dùng chuột chọn lại. - Chọn Next. Xuất hiện hộp thoại PivotTable Wizard Step 3 of 4. - Chọn các tr−ờng để đ−a vào các vùng của bảng bằng cách kéo và thả tên tr−ờng ở bên phải hộp thoại tới các vùng đ−ợc ấn định trong bảng. ở ví dụ này chúng tôi đ−a vào các mũi tên (chỉ h−ớng kéo thả) để ta đọc tiện thao tác. Cụ thể, ta Kéo tr−ờng thả tại vùng Cửa hàng PAGE Nhân viên ROW Ngày ROW Sản phẩm COLUMN Tiền DATA
Khoa CNTT - VĐH Mở Excel 61 Sau thao tác trên, hộp thoại có dạng : Chọn Next để chuyển sang b−ớc tiếp theo. Xuất hiện hộp thoại PivotTable Wizard Step 4 of 4. Trong hộp PivotTable Starting Cell ta chỉ ra địa chỉ của ô đầu tiên (góc trái trên) của bảng Tổng hợp (ví dụ : nếu ta chọn ô A13, Excel sẽ tự điền vào Sheet1!$A$13). Nếu không, bảng Tổng hợp sẽ đ−ợc để ở Sheet
Khoa CNTT - VĐH Mở Excel 62 mới (tiếp ngay sau Sheet hiện tại). Trong hộp PivotTable Name ta gõ vào tên của bảng Tổng hợp (tr−ờng hợp cần tạo nhiều bảng Tổng hợp cho một CSDL), ví dụ Tổng kết Bán hàng. ý nghĩa các lựa chọn khác nh− sau : Grand Total For Columns : Tạo thêm cột tổng cho mỗi số liệu của Column Field. Grand Total For Rows : Tạo thêm hàng tổng cho mỗi số liệu của Row Field. Save Data With Table Layout : Bảng Tổng hợp đ−ợc l−u trữ cùng với Format đ−ợc tạo. AutoFormat Table : Tự động tạo khuôn cho Bảng Tổng hợp Điều chỉnh Bảng Tổng hợp Thay đổi vị trí các tr−ờng : Trong Bảng Tổng hợp kéo và thả tên tr−ờng tại vị trí mong muốn. ở ví dụ trên, có thể đổi chỗ cho hai tr−ờng Nhân viên và Ngày bằng cách kéo tr−ờng Nhân viên về bên phải tr−ờng Ngày và thả tại đó. Bổ sung các tr−ờng : - Chọn ô bất kỳ của Bảng Tổng hợp - Chọn mục Data, PivotTable - Thực hiện lại các b−ớc nh− khi tạo mới bảng Tổng hợp để bổ sung tr−ờng vào các vị trí mong muốn. Xoá tr−ờng : Trong Bảng Tổng hợp kéo tên tr−ờng cần xoá ra khỏi bảng. Sửa tên tr−ờng : Đ−a con trỏ ô về tr−ờng cần thiết trong Bảng Tổng hợp và tiến hành sửa tên nh− sửa dữ liệu của bảng tính
Khoa CNTT - VĐH Mở Excel 63 Thay đổi hàm số tính toán ở chế độ mặc định số liệu của vùng DATA đ−ợc lấy tổng và các cột hoặc hàng Total cũng đ−ợc lấy tổng. Có thể thay đổi các cách tính này bằng các hàm số khác nh− đếm (Count), tính trung bình (Average), tìm cực đại (Max), tìm cực tiểu (Min), Các b−ớc nh− sau : - Trong Bảng Tổng hợp chọn tr−ờng tr−ớc đó ta đã đ−a vào vùng DATA (nh− ở ví dụ trên trang 59, chọn ô Grand Total) - Chọn mục Data, PivotTable Field, xuất hiện hộp thoại sau : - Chọn hàm t−ơng ứng danh sách Summarize by - Chọn OK Tự động điều chỉnh Bảng Tổng hợp khi dữ liệu gốc thay đổi Sau khi sửa số liệu của CSDL - Chọn ô bất kỳ của Bảng Tổng hợp - Chọn mục Data, Refresh Data X. trình bày trang 1. Chọn cỡ giấy, h−ớng in - Chọn File, Page Setup. Xuất hiện hộp thoại Page Setup - Chọn Page - Chọn cỡ giấy trong khung Paper Size - Chọn Landscape để in ngang giấy, Portait để in dọc - OK
Khoa CNTT - VĐH Mở Excel 64 2. Đặt lề (5) (1) - Chọn File, Page Setup - Chọn Margins Chọn Để đặt khoảng cách cho (3) (4) Top Lề trên (1) Bottom Lề d−ới (2) Left Lề trái (3) Right Lề phải (4) Header Lề của tiêu đề đầu trang (5) (2) Footer Lề của tiêu đề cuối trang (6) (6) - Đặt lại khoảng cách các lề cho phù hợp, cần chú ý rằng các khoảng cách này đ−ợc tính bằng cm hoặc inch. 3. Tạo tiêu đề đầu / cuối trang a - Chọn tiêu đề đầu / cuối trang theo mẫu : - Chọn File, Page Setup - Chọn Header /Footer - Chọn Header hoặc Footer, xuất hiện danh sách các mẫu, ý nghĩa của chúng nh− sau: (none) Để trống phần tiêu đề đầu/ cuối trang Page 1 In chữ Page kèm theo số trang Page 1 of ? In chữ Page kèm theo số trang của tổng số trang Một mẫu (sample) xuất hiện cho ta thấy kết quả của việc chọn - Chọn OK
Khoa CNTT - VĐH Mở Excel 65 b - Tự tạo tiêu đề đầu / cuối trang: - Chọn File, Page Setup - Chọn Header /Footer - Chọn Custom Header hoặc Custom Footer, xuất hiện hộp thoại sau : Chèn Ngày, giờ Đánh số trang Chèn tên File Chọn Font chữ Phần bên trái Phần giữa Phần bên phải của tiêu đề của tiêu đề của tiêu đề - Cần tạo cho phần nào của tiêu đề đ−a con trỏ đến phần đó và gõ văn bản vào hoặc chọn các nút t−ơng ứng. Ví dụ để tạo tiêu đề nh− các trang lẻ của tài liệu này, ta gõ vào Khoa CNTT tại ô Left Section và nháy chuột tại nút Chọn Font chữ để trình bày kiểu, cỡ chữ. Làm t−ơng tự nh− vậy với từ Excel ở ô Center Section. Để có số trang ở góc phải tiêu đề, ta đ−a con trỏ về ô Right Section và chọn nút Đánh số trang - OK 4. Chèn / xoá dấu ngắt trang a - Chèn dấu ngắt trang dọc và ngang : - Chọn ô nơi ta muốn bắt đầu trang mới. - Insert, Page Break Excel chèn dấu ngắt trang (là các đ−ờng kẻ nét đứt) ở trên và bên trái ô đ−ợc chỉ định. b - Chỉ chèn một dấu ngắt trang ngang : - Chọn hàng nơi ta muốn bắt đầu trang mới. - Insert, Page Break Excel chèn dấu ngắt trang ở trên hàng đ−ợc chỉ định. c - Chỉ chèn một dấu ngắt trang dọc : - Chọn cột nơi ta muốn bắt đầu trang mới. - Insert, Page Break Excel chèn dấu ngắt trang ở bên trái cột đ−ợc chỉ định. d - Xoá một dấu ngắt trang: - Chọn ô bất kỳ ngay phía d−ới dấu ngắt trang ngang hoặc ngay bên phải dấu ngắt trang dọc.
Khoa CNTT - VĐH Mở Excel 66 - Insert, Remove Page Break e - Xoá tất cả các dấu ngắt trang: - Chọn toàn bộ bảng tính. - Insert, Remove Page Break 5. Lặp lại tiêu đề của bảng tính khi sang trang ở các bảng tính lớn, mỗi khi sang trang, để tránh phải gõ lại các tiêu đề, ta làm nh− sau : - Chọn File, Page Setup - Chọn Sheet, xuất hiện hộp thoại sau : - Nếu cần lặp lại các hàng tiêu đề, trong khung Rows to Repeat at Top điền vào số thứ tự của chúng. Ví dụ, ở bài tập 3 trang 72, để mỗi khi sang trang, tiêu đề TT Họ và tên CV tại hàng 2 của bảng tính đ−ợc lặp lại, ta điền vào $2:$2 (bằng cách gõ trực tiếp hoặc dùng chuột chọn 1 ô bất kỳ nào của hàng đó). - Nếu cần lặp lại các cột bên trái bảng tính trong khung Columns to Repeat at Left điền vào số thứ tự của chúng. Ví dụ, ở bài tập 3 trang sau khi chèn dấu ngắt trang dọc tại cột K, ta muốn ở trang đầu in các cột từ TT Họ và tên đến Tạm ứng (từ cột A đến cột I ), còn ở trang sau in các cột TT Họ và tên và cột Còn lại, ta điền vào $A:$B (vì cần lặp lại 2 cột TT Họ và tên) bằng cách gõ trực tiếp hoặc dùng chuột chọn 2 ô bất kỳ nào trên cùng hàng của 2 cột đó. - Chọn OK. Ta chỉ thấy tiêu đề lặp lại khi chọn biểu t−ợng Print Preview (kính lúp) hoặc khi in ra giấy.
Khoa CNTT - VĐH Mở Excel 67 6. Không in l−ới có sẵn của bảng tính Để Excel chỉ in khung cho các ô do ta kẻ mà không in l−ới có sẵn cho các ô khác của bảng tính, ta làm nh− sau : - Chọn File, Page Setup - Chọn Sheet - Bỏ dấu chọn x tại ô Grid - OK xi. in Tr−ớc khi in nên : - Chọn mục File, Print Preview (hoặc biểu t−ợng Print Preview) để xem bố cục (hình thù) bảng tính - Kiểm tra xem máy in đã sẵn sàng ch−a (đã nối với máy tính ch−a, đã bật ch−a, đã nạp giấy ch−a v.v ). Để in toàn bộ bảng tính, chọn biểu t−ợng Print trên thanh công cụ. Để in từng phần bảng tính : - Chọn mục File, Print. xuất hiện hộp thoại sau : Selections : In một khối đã đ−ợc chọn tr−ớc đó. Selected Sheet(s) : In một hoặc nhóm bảng tính Entire Workbook : In toàn bộ tập bảng tính Pages : In một dãy các trang : điền vào số thứ tự của trang đầu dãy trong ô From, số thứ tự của trang cuối trong ô To - Chọn OK.
Khoa CNTT - VĐH Mở Excel 68 xII. làm việc với nhiều bảng tính - Lần l−ợt mở các bảng tính. Trên màn hình là bảng tính đ−ợc mở sau cùng. Để chuyển sang làm việc với bảng tính khác : - Chọn Window, Excel liệt kê các bảng tính đang mở với số thứ tự của chúng, tên bảng tính chứa con trỏ có dấu √ ở bên trái - Chọn bảng tính mà ta cần chuyển tới (hoặc gõ số thứ tự của bảng tính đó) Muốn cho hiện ra tất cả các bảng tính trên màn hình : - Chọn Window, Arrange. Xuất hiện hộp thoại Arrange Windows Chọn mục Để sắp xếp các cửa sổ bảng tính Title Phủ kín màn hình Horizontal Theo chiều ngang Vertical Theo chiều dọc Cascade So le - Để chuyển sang bảng tính nào, kích chuột tại cửa sổ của bảng tính đó hoặc ấn Ctrl+F6. Ta có thể thực hiện các thao tác với các cửa sổ này nh− : sắp xếp lại, thay đổi kích th−ớc, đóng v.v Có thể copy, chuyển khối từ bảng tính này sang bảng tính khác (xem các thao tác với khối ở trang 13). Khi không cần bảng tính nào ta nên đóng nó lại (để giải phóng bớt bộ nhớ) bằng cách chuyển về bảng tính đó, chọn File, Close hoặc đóng cửa sổ của bảng tính đó lại
Khoa CNTT - VĐH Mở Excel 69 xiii. trao đổi thông tin với word 1. Chuyển các kết quả của Excel cho Word Theo các b−ớc sau : - Chọn miền dữ liệu của bảng tính cần chuyển cho Word (xem cách đánh dấu khối ở trang 13). Nếu là đồ thị chỉ cần trỏ chuột vào trong lòng nó, ấn nút trái, xuất hiện chấm vuông tại các cạnh và góc của khung. - Chọn biểu t−ợng Copy trên thanh công cụ hoặc mục Edit, Copy. Nếu không cần làm việc với Excel nữa, thoát khỏi ch−ơng trình này (xem trang 4). Nếu ta đ−a vào bộ nhớ một khối lớn, hãy trả lời Yes cho câu hỏi Save Large Clipboard From ? - Khởi động Word (nếu cần), trong Word ta mở tài liệu và đ−a con trỏ về vị trí cần thiết, sau đó làm theo một trong hai cách sau : Cách 1 : Chọn biểu t−ợng Paste trên thanh công cụ hoặc mục Edit, Paste. Cách 2 : Chọn mục Edit, Paste Special, Paste Link. Sự khác nhau giữa 2 cách dán : Bằng cách 2, mọi thay đổi của đối t−ợng trong Excel sẽ đ−ợc phản ảnh trong Word. Trong khi đó ở cách 1 sự sửa đổi không đ−ợc phản ảnh. Chú ý : Không thể đ−a đồng thời một lúc bảng tính và đồ thị sang cho Word đ−ợc. Ta phải thực hiện các thao tác trên lần l−ợt cho từng loại một. 2. Chuyển bảng đ∙ gõ bằng Word cho Excel Khả năng tính toán trong bảng của Word rất yếu. Nêú cần tính toán trong các bảng đã đ−ợc gõ bằng Word, nên chuyển chúng cho Excel theo các b−ớc sau : - Trong Word, chọn các ô hay toàn bộ bảng đã gõ. - Chọn biểu t−ợng Copy trên thanh công cụ hoặc mục Edit, Copy. Nếu không cần làm việc với Word nữa, ta ra khỏi ch−ơng trình này. Nếu ta đ−a vào bộ nhớ một khối lớn, hãy trả lời Yes cho câu hỏi Save Large Clipboard From ? - Khởi động Excel (nếu cần), trong Excel mở bảng tính và đ−a con trỏ về vị trí cần thiết, , sau đó làm theo một trong hai cách sau : Cách 1 : Chọn biểu t−ợng Paste trên thanh công cụ hoặc mục Edit, Paste. Cách 2 : Chọn mục Edit, Paste Special, Paste Link. Bằng cách 2, mọi thay đổi của đối t−ợng trong Word sẽ đ−ợc phản ảnh trong Excel. Trong khi đó ở cách 1 sự sửa đổi không đ−ợc phản ảnh.
Khoa CNTT - VĐH Mở Excel 70 bài thực hành Bài 1. Lập bảng doanh thu sau : A B C D E F G 1 Dự tính thu nhập năm 1997 công ty X 2 Hạng mục Qúy I Qúy II Qúy III Qúy IV Cả năm VNĐ 3 Bán : 14000 15000 16000 24000 69000 4 Chi phi : 5 L−ơng 2000 2000 2000 2000 8000 6 Trả lãi 1200 1400 1500 1600 5700 7 Nhà 600 600 600 600 2400 8 Quảng cáo 900 2000 1700 4000 8600 9 Vật t− 4000 4200 4500 5000 17700 10 Tổng chi : 8700 10200 10300 13200 42400 11 Thu nhập: 12 Qúy 5300 4800 5700 10800 13 Tới qúy 5300 10100 15800 26600 Tỷ giá 11.000 - Không gõ dữ liệu ở các cột F, G và các hàng 10,12,13. Cách tính nh− sau : Cả năm = Σ Các quý, tại ô F3 gõ = SUM(B3:E3) Tổng chi = Σ Các mục chi, tại ô B10 gõ = SUM(B5:B9) Thu nhập Quý = Bán - Tổng chi, tại ô B12 gõ = B3-B10 Thu nhập tới quý = Luỹ kế các quý tr−ớc, tại ô B13 gõ = B12 tại ô C13 gõ = B13+C12 copy công thức từ ô C13 sang D13:E13 - Chèn thêm 1 hàng vào giữa các hàng 3 và 4 để tính % tiền bán đ−ợc của từng quý so với cả năm. - Trong cột G tính tiền bán đ−ợc và tiền chi phí quy ra tiền Việt theo tỷ giá ghi ở ô cuối bảng (ô G13) - Vẽ đồ thị : tiền bán đ−ợc của các quý số liệu các mục chi tiền bán đ−ợc và tổng chi của các quý
Khoa CNTT - VĐH Mở Excel 71 Bài 2. Lập bảng kết quả thi sau : A B C D E F G H I J 1 kết quả thi cuối kỳ - lớp tin học cơ sở 2 TT Tên Ngày sinhTuổi Gt Toán Tin Trung Xếp Xếp loại bình thứ 3 1 Hùng 30/01/78 Nam 4 7 5.5 8 Trung bình 4 2 Bình 21/08/74 Nữ 7 7 7.0 5 Khá 5 3 Vân 12/11/70 Nữ 8 9 8.5 3 Giỏi 6 4 Bình 15/06/77 Nữ 9 10 9.5 1 Xuất sắc 7 5 Doanh 05/12/76 Nam 5 8 6.5 7 Trung bình 8 6 Loan 18/09/77 Nữ 5 4 4.5 9 Kém 9 7 Anh 23/04/68 Nam 9 6 7.5 4 Khá 10 8 Thu 01/05/73 Nữ 3 5 4.0 10 Kém 11 9 Bình 26/02/71 Nam 6 8 7.0 5 Khá 12 10 Ngân 12/05/75 Nữ 10 8 9.0 2 Giỏi 13 Cao nhất 10 10 9.5 14 Trung bình 6.6 7.2 6.9 15 Thấp nhất 3.0 4.0 4.5 - Không gõ dữ liệu ở các cột A, D, H, I, J và các hàng 13, 14, 15 - Đánh số Thứ tự - Tính Tuổi : =YEAR(TODAY( ))-YEAR(C3) - Tính Điểm Trung bình = AVERAGE(F3:G3) - Tính các giá trị cao nhất (MAX), trung bình (AVERAGE), thấp nhất (MIN) của các cột Tuổi, điểm Toán, Tin, Trung bình - Xếp thứ = RANK(H3,$H$3:$H$12) - Xếp loại theo Điểm trung bình theo thang điểm sau : Kém 5 Trung bình 7 Khá 8 Giỏi 9.5 Xuất sắc bằng 2 cách : + dùng hàm IF = IF(H3>=9.5,"Xuất sắc",IF(H3>=8,"Giỏi",IF(H3>=7,"Khá", IF(H3>=5,"Trung bình", "Kém")))) + dùng hàm HLOOKUP hoặc VLOOKUP (trang 37) - Đánh lại thứ tự xếp thứ - Sắp xếp danh sách theo ABC của Tên học viên, những ng−ời trùng tên xếp ng−ời có điểm Trung bình cao lên trên. - Đ−a ra 4 nhóm danh sách xếp loại (Giỏi, Khá, Trung bình, Kém) và số học viên của mỗi loại. Tính xem mỗi loại chiếm bao nhiêu phần trăm của tổng số.
Khoa CNTT - VĐH Mở Excel 72 Bài 3. Lập bảng l−ơng sau, chú ý không gõ số trong các cột từ F đến K. A B C D E F G H I K 1 Cơ quan X bảng l−ơng tháng 12 - 1996 2 TT Họ và tên CV LCB NC PC L−ơng Thu Tạm Còn CV nhập ứng lại 3 1 Ng. Văn Dũng TP 1600 25 3500 40000 43500 20000 23500 4 2 Trần Mai Lan NV 1200 21 0 25200 25200 17000 8200 5 3 Vũ Tú Nam PP 1400 24 3000 33600 36600 20000 16600 6 4 Lê Đình H−ng BV 110025 1000 27500 28500 19000 9500 7 5 Phạm Văn Lộc PG 1700 15 4000 25500 29500 20000 9500 8 6 Trần Tuấn Anh NV 1000 25 0 25000 25000 17000 8000 9 7 Lý Thông GĐ 2000 25 5000 50000 55000 20000 35000 10 8 Nguyễn Ba PP 1500 18 3000 27000 30000 20000 10000 11 9 Đỗ Văn Xuân BV 1000 17 1000 17000 18000 12000 6000 12 10 Trần Văn Hợp TP 1700 20 3500 34000 37500 20000 17500 13 14 CV PCCV 15 GĐ 5000 16 PG 4000 17 TP 3500 18 PP 3000 19 NV 0 20 BV 1000 Công thức tính nh− sau : 1. L−ơng = L−ơng cơ bản (LCB) x Số ngày công (NC), tại ô G3 gõ: = D3*E3 2. Phụ cấp chức vụ (PCCV) ghi tại miền F14:G20. Để tính PCCV cho từng ng−ời, ta làm nh− sau : - Đặt tên cho miền F15:G20 là BangPCCV - Tại ô F3 gõ : = VLOOKUP(C3, BangPCCV, 2, 0). Vì cột thứ nhất của Bảng PCCV không xếp tăng dần nên phải thêm tham biến thứ t− (số 0) cho hàm. Có thể thay số 0 này bằng chữ False Thu nhập = Phụ cấp chức vụ + L−ơng, tại ô H3 gõ công thức: = F3 + G3. Cách tính Tạm ứng nh− sau : Mỗi ng−ời tạm ứng 2/3 số l−ơng (làm tròn đến hàng nghìn), nếu số tiền tạm ứng tính ra > 20000 thì lấy bằng 20000 tại ô F3 gõ công thức : =MIN(20000,ROUND(2/3*H3,-3)) 3. Còn lại = Thu nhập - Tạm ứng, tại ô K3 gõ : I3-H3 4. Trong câu 2, thay hàm VLOOKUP bằng hàm HLOOKUP.
Khoa CNTT - VĐH Mở Excel 73 mục lục I. khởi động và màn hình Excel 1 1. Khởi động 1 2. Màn hình 1 3. Ra khỏi Excel 3 II. các thao tác với bảng tính 4 1. L−u (ghi) bảng tính lên đĩa 4 2. Mở bảng tính đã có trên đĩa 5 3. Đóng bảng tính 5 4. Chèn thêm 1 bảng tính 5 5. Xoá bớt 1 bảng tính 5 6. Đổi tên bảng tính 5 7. Sao chép / Chuyển 1 bảng tính 5 8. Tách bảng tính 6 9. ẩn và hiện lại 1 bảng tính 6 10. Bảo vệ bảng tính 6 11. Chọn nhiều bảng tính 6 IIi. Xử lý dữ liệu trong bảng tính 6 1. Các kiểu dữ liệu 6 2. Các toán tử trong công thức 8 3. Nhập dữ liệu 8 4. Sửa, xoá dữ liệu 13 5. Các thao tác với khối 13 6. Xử lý ô, cột, hàng trong bảng tính 15 7. Định dạng dữ liệu 18 8. Đặt tên cho ô 22 9. Ghi chú cho ô 24 10. Bảo vệ ô 26 iV. hàm trong Excel 26 1. Quy tắc sử dụng hàm 26 2. Nhập hàm vào bảng tính 27 3. Một số hàm th−ờng dùng 29 v. đồ thị 38 1. Các b−ớc tạo đồ thị mới 38 2. Thiết lập lại đồ thị 41 3. Chỉnh sửa đồ thị 42 4. Đồ thị kiểu đèn chiếu (Slideshow) 43
Khoa CNTT - VĐH Mở Excel 74 vIi. kiểu trình bày 45 1. Tạo một kiểu trình bày mới 46 2. áp dụng một kiểu trình bày 46 vIii. Macros 47 1. Ghi một Macro mới 47 2. Gán Macro cho thanh công cụ 48 3. Chạy Macro 49 4. Xoá Macro 49 Ix. quản trị dữ liệu 49 1. Khái niệm cơ bản 49 2. Sắp xếp dữ liệu 49 3. Lọc dữ liệu 50 4. Các hàm Cơ sở dữ liệu (CSDL) 55 5. Tổng kết theo nhóm 57 X. trình bày trang 63 1. Chọn cỡ giấy, h−ớng in 63 2. Đặt lề 64 3. Tạo tiêu đề đầu / cuối trang 64 4. Chèn / xoá dấu ngắt trang 65 5. Lặp lại tiêu đề của bảng tính khi sang trang 66 6. Không in l−ới có sẵn của bảng tính 67 xi. in 67 xII. làm việc với nhiều bảng tính 68 xiii. trao đổi thông tin với word 69 1. Chuyển các kết quả của Excel cho Word 69 2. Chuyển bảng đã gõ bằng Word cho Excel 69 bài thực hành 70
Viện đại học mở hà nội khoa công nghệ tin học giáo trình EXCEL Biên soạn: Lê Quang Huy Hà nội, 6 -1999