Tài liệu học Excel

Nội dung text: Tài liệu học Excel

1. 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.
2. 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ị
3. 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
4. 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 ô.
5. 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.
6. 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
7. 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
8. 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.
9. 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.
10. 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)
11. 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
12. 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
13. 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
14. 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
15. 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.
16. 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.
17. 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 :
18. 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.
19. 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)
20. 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.
21. 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.
22. 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).
23. 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.
24. 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ó.