Giao Trinh Excel

Bài 1: Làm quen với Excel I. Tổng quan về Excel I.1.

Giới thiệu Excel Excel là chương trình xử lý bảng tính nằm trong bộ Microsoft Office của hãng

phần mềm Microsoft. Cũng như các chương trình bảng tính Lotus, Quattro Pro…bảng tính của Excel cũng bao gồm nhiều ô được tạo bởi các dòng và cột, việc nhập dữ liệu và lập công thức tính toán trong Excel cũng có những điểm tương tự, tuy nhiên Excel có nhiều tính năng ưu việt và có giao diện rất thân thiện với người dùng. Hiện nay có nhiều phiên bản của Excel như Excel 5.0, Excel 95, Excel 97, Excel 2000. Chương trình này hướng dẫn sử dụng Excel 2000, nếu bạn chưa từng sử dụng các phiên bản trước của Excel thì việc học chương trình này cũng không có gì trở ngại. Khi học Excel bạn nên xem phần lý thuyết và minh hoạ, sau đó khởi động Excel vừa học vừa thực tập.

I.2.

Đặc điểm của Excel 2000 Excel 2000 là một phần trong bộ Office 2000 của Microsoft chạy trên môi trường

từ Windows 95 trở nên do đó Excel 2000 có nhiều điểm giống với Word 2000 trong cách định dạng và trang trí, khi sử dụng Excel 2000 cần chú ý một số điểm sau: Hầu hết các nguyên tắc và các thao tác thực hiện trong Excel 5.0, Excel 95 và Excel 97 đều có thể thực hiện trong Excel 2000. Các tập tin tạo bởi Excel 5.0, Excel 95 và Excel 97 đều sẽ tương thích trong Excel 2000, nhưng các tập tin tạo bởi Excel 97 sẽ không tương thích với các phiên bản trước, tuy nhiên nếu cần Excel 97 có thể ghi tập tin dưới dạng tương thích với các phiên bản trước. Excel 2000 không chạy trên Windows 3.x.

Trang 1

* Bộ Offce 2000 bao gồm Word 2000 (soạn thảo văn bản), Excel 2000 (Xử lí bảng tính), Access 2000 (quản trị cơ sở dữ liệu), Power Point 2000 (thuyết trình) và Outlook 2000 (thư tín điện tử), FrontPage (lập trình Web).

II. Khởi động và thoát khỏi Excel II.1.

Khởi động Excel: Để làm việc với Excel, hãy thực hiện như sau: Start\Programs\Microsoft Excel

(xem hình)

Cũng có thể khởi động Excel bằng cách click vào biểu tượng (shortcut) của Microsoft Excel ở trên màn hình Windows, hay click vào biểu tượng

trên thanh

Office Bar

II.2.

Thoát khỏi Excel: Cũng như các chương trình ứng dụng khác, trước khi thoát cần phải lưu lại dữ

liệu, sau đó thoát bằng một trong những cách sau: C1: Chọn File\ Exit C2: Click nút

ở góc bên phải thanh tiêu đề.

C3: Double click biểu trượng

phía trên trái thanh tiêu đề.

C3: Nhấn tổ hợp phím Alt+F4. Trang 2

C4: Click biểu trượng

phía trên trái thanh tiêu đề khi đó xuất hiện một trình

đơn chọn Close. C5: Right Click biểu trượng

trên thanh Taskbar

khi đó xuất hiện một trình đơn chọn Close. Trong trường hợp chưa lưu lại những thay đổi trên bảng tính vào đĩa, sẽ xuất hiện thông báo như sau:

Kích nút Yes nếu muốn lưu những thay đổi vào đĩa, nếu không muốn, hăy kích nút No, quay trở lại chọn Cancel để làm tiếp.

III. Màn hình làm việc của Excel III.1. Màn hình Excel

Trên màn hình Excel, chú ý đến các thành phần sau: - Thanh tiêu đề (Title bar): chứa tên phần mềm ứng dụng (Microsoft Excel) và tên tệp. Tên ngầm định do Excel đặt là Book 1, Book 2, ...

Trang 3

- Thanh thực đơn (Menu bar): chứa bộ lệnh của Excel. Đây là nơi cho phép thực hiện những lệnh cơ bản nhất của Excel. Thực đơn chính của Excel bao gồm: File, Exit, View, Insert, Format, Tools, Data, Windows, Help. Cách sử dụng thực đơn và các lệnh trên thực đơn sẽ được lần lượt giới thiệu trong các phần sau. - Thanh công cụ chuẩn (Standard Toolbar): chứa các nút giúp thực hiện nhanh các thao tác cơ bản nhất như: mở file mới đĩa

(New), gọi file cũ

(Open), ghi file vào

(Save), .... - Thanh công cụ định dạng (Formatting toolbar): chứa các nút giúp định dạng

nhanh dữ liệu như: chọn font cho dữ liệu

(font), chọn cỡ font cho dữ liệu

(Font size), .... - Hộp địa chỉ ô (Name box): cho biết địa chỉ của ô hiện thời. - Dạng nhập dữ liệu (Formula bar): chứa dữ liệu hoặc công thức của ô hiện thời trong bảng tính. - Bảng tính (Work sheet): phần bảng tính, là nơi làm việc chủ yếu trong Excel. - Danh sách các sheet (Sheet tabs): chứa danh sách các bảng tính trong file hiện thời. Ngầm định, các bảng tính có tên là Sheet 1, Sheet 2, Sheet 3, ... Các tên này có thể đổi được (xem phần sau). - Thanh trạng thái (Status bar): hiện các thông báo về trạng thái làm việc hiện thời của bảng tính.

III.2. Định dạng màn hình Excel Màn hình chuẩn của Excel có các thành phần như đã trình bầy, tuy nhiên có thể làm ẩn đi các thanh công cụ không cần thiết, hoặc cho hiển thị thêm các thanh công cụ khác bằng cách click mở trình đơn View và chọn: Formula Bar : Thanh công thức. Status Bar : Thanh trạng thái. ToolBar : Thanh công cụ, có nhiều thanh công cụ như: + Standard : Chuẩn. + Formatting : Định dạng. Trang 4

+ Drawing : Vẽ. + Picture : Hiệu chỉnh hình. +… (Click vào tên các thanh công cụ để chọn hoặc huỷ bỏ). Có thể di chuyển vị trí hiển thị của các thanh công cụ bằng cách click & drag tại khoẳng trống hoặc thanh tiêu đề của chúng để đưa đến vị trí khác trên màn hình.

IV. Giới thiệu bảng tính của Excel IV.1. Workbook Mỗi Workbook tương ứng với một file lưu trên đĩa bao gồm các Work sheet (bảng tính), các biểu đồ (Chart), .... Trong một Workbook có thể có tối đa 255 sheet. Các phiên bản cũ từ Excel 95 trở về trước, máy thường mở ngầm định 16 sheet. Từ Excel 97, máy mở ngầm định 3 Sheet. Trong Excel cho phép thay đổi số Sheet ngầm định này bằng cách sử dụng thực đơn Tools \ Options. Trong hộp Sheet in new workbook của mục General gõ số Sheet ngầm định muốn mở trong file mới. Dữ liệu trên các bảng tính khác nhau có thể liên hệ với nhau như trên một bảng tính.

IV.2. Worksheet Một tệp tin Excel (Book) sẽ có nhiều bảng tính (Worksheet), trên mỗi bảng tính bao gồm các hàng (rows), các cột (columns) và vùng giao nhau giữa cột và dòng gọi là các ô (cells). - Các hàng (rows): Excel 97 và 2000 có 65536 hàng trong một bảng tính (các phiên bản trước nó chỉ có 16384 hàng). Tiêu đề hàng (row heading) nằm ở đầu của mỗi hàng và được đánh số từ 1 đến 65536. Trang 5

- Các cột (columns): trong một bảng tính của Excel có 256 cột. Tiêu đề của cột (column heading) được đánh số theo A, B, C, ..., IU, IV. - Các ô (cells): Mỗi ô đều có địa chỉ riêng, địa chỉ này được xác định bằng tên của cột và tên của dòng tạo ra ô đó (Tên cột luôn đứng trước tên dòng). Ví dụ: A1 (ô giao giữa cột A và hàng 1), E200 (ô giao giữa cột E và hàng 200). Địa chỉ ô hiện thời hiện lên trong ô Cell reference. - Vùng ô (Range): Là tập hợp nhiều ô đứng liền nhau (hình chữ nhật chứa từ hai ô trở lên), địa chỉ của vùng được xác định bằng địa chỉ của ô bên trái và ô ở góc dưới phải của vùng. Địa chỉ vùng được ký hiệu theo quy tắc: địa chỉ của hai ô đối diện cách nhau bởi dấu hai chấm (:). Ví dụ B4:D7 là địa chỉ vùng gồm các ô B4, B5, B6, B7, C4, C5, C6, C7, D4, D5, D6, D7.

Trang 6

Câu hỏi và bài tập 1. Nêu các cách khởi động và thoát khỏi Excel. 2. Mở cửa sổ Excel, quan sát và học thuộc tên các phần tử trong bảng tính Excel. 3. Phân biệt sự khác nhau giữa một Workbook và một Worksheet. 4. Số hàng và số cột trong các phiên bản khác nhau của Excel (Excel 5.0, Excel 7.0, Excel 97) có giống nhau hay không? 5. Các địa chỉ cùng A1:C4, C7:C12, B6:E6 chỉ những ô nào trong bảng tính?

Trang 7

Bài 2: Nhập dữ liệu vào bảng tính và các thao tác với tệp I. Các kiểu dữ liệu trong Excel Trước khi nhập dữ liệu vào bảng tính, chúng ta phải hiểu được một số khái niệm cơ bản về kiểu dữ liệu trong bảng tính để tránh sự sai sót và nhầm lẫn trong quá trình tính toán và xử lý dữ liệu sau này. Trong một ô 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, Excel phân biệt các loại dữ liệu: - Hằng (constant value) - Công thức (formula value) - Các kiểu lỗi và các giá trị logic

I.1.

Kiểu hằng: Kiểu hằng là giá trị không đổi. Các giá trị này có thể là chữ (text) hoặc số

(number) I.1.1. Text: Nếu trên dòng nhập liệu có chứa chữ thì Excel hiểu đó là kiểu kí tự, ở chế độ mặc định sẽ hiển thị ở phía bên trái của ô. Ví dụ: Bảng điểm, Danh sách học sinh, .... Nếu trên dòng nhập dữ liệu có chứa ít nhất một chữ hoặc một kí tự dạng *, !, &, #, ? hoặc dấu cách (space) trở lên thì Excel tự động hiểu dữ liệu nhập vào có kiểu kí tự (text). Ví dụ: 123?, #78965, 120 456, ... Nếu gõ toàn số mà muốn máy hiểu là kí tự thì phải gõ bắt đầu bằng dấu ('). Ví dụ: Nếu muốn gõ một xâu kí tự là các chữ số 12345 thì phải gõ '12345.

Trang 8

I.1.2. Number Ký tự nhập vào là số, ở chế độ mặc định dữ liệu dạng số sẽ hiển thị ở phía bên phải của ô. Nếu số âm có thể nhập dấu "-" phía trước hoặc để trong dấu ngoặc đơn. Number bao gồm: - Các số thông thường, ví dụ 2345, 56.89, .... - Kiểu ngày tháng: Nhập ngày tháng bính thường với dấu phân cách là gạch ngang (-) hay gạch đứng (/). Phần tháng Excel có thể hiển thị số hoặc chữ bằng tiấng Anh, quy định cách hiển thị trong hộp thoại Format Cell. Một cách ngầm định, Excel lưu trữ các số ngày tháng theo các số nguyên từ 1 đến 2958525, tính từ ngày 1/1/1900 đến 31/12/9999. - Kiểu thời gian: Excel lưu trữ số thời gian theo các số thập phân từ 0 đến 0.99999999 bắt đầu từ 0:00:00 (12:00:00 A.M.) đến 23:59:59 (11:59:59 P.M.).

I.2.

Kiểu công thức Công thức là một biểu thức chứa các hằng, các địa chỉ ô, các hàm và các toán tử.

Công thức phải bắt đầu bởi dấu bằng (=), dấu cộng (+), dấu a còng @. Dạng tổng quát của công thức: = Giá trị hiển thị trong ô chứa công thức sẽ là kết quả của công thức đó. Ví dụ: Kích chuột vào ô B2 và gõ = 10+20+30, sau đó ấn phím Enter. Khi đó, kết quả hiện trong ô B2 sẽ là 60.

I.3.

Kiểu logic và các lỗi Ví dụ: Các giá trị logic như TRUE, FALSE hoặc các lỗi như #VALUE!, ... không

tự gõ vào bảng được mà chỉ xuất hiện khi trả lại giá trị của hàm hoặc biểu thức logic hoặc khi xuất hiện lỗi.

Trang 9

II. Cách nhập chữ tiếng Việt Có nhiều cách gõ tiếng Việt, nhưng phổ biến nhất là kiểu gõ VNI và TELEX.

II.1.

Kiểu gõ VNI: Kiểu gõ này dùng các phím số (hàng phím số bên trên các chữ cái) để bỏ dấu. Các

phím được quy định như sau: Sắc (1), Huyền (2), Hỏi (3), Ngã (4), Nặng (5), dấu mũ â, ê (6), dấu móc ư, ơ (7), dấu ă (8), dấu ngang đ (9). Cách gõ : Gõ phím dấu ngay sau các nguyên âm (O, Y, E, I, U, A). * Khi bỏ dấu sai phải xoá cả nguyên âm và gõ lại.

II.2.

Kiểu gõ TELEX: Kiểu gõ này dùng 2 chữ cái kế tiếp nhau không theo nguyên tắc của chữ tiếng Việt

để thể hiện dấu. Để thể hiện dấu theo quy định sau: â (aa), ă (aw), ê (ee), ư (uw), ô (oo), ơ (ow), đ (dd), sắc (s), huyền (f), nặng (j), hỏi (r), ngã (x), xoá dấu (z). Gõ chữ cái trước và có thể bỏ dấu sau khi gõ xong chữ đó, hoặc bỏ dấu sau khi gõ nguyên âm, nếu bỏ dấu cho chữ hoa phải nhấn thêm phím Shift.

III. Nhập và chỉnh sửa dữ liệu III.1. Quy tắc chung Việc nhập dữ liệu vào bảng tính được thực hiện theo các bước sau: 1. Kích hoạt ô cần nhập dữ liệu và nhập bình thường hoặc Click tại thanh công thức để nhập dữ liệu cho ô đang chọn. 2. Khi bắt đầu gõ bất kì kí tự nào từ bàn phím, kí tự đó sẽ đều được thể hiện trên dòng nhập dữ liệu (formula bar) và con trỏ text (có dạng | ) nhấp nháy trong ô hiện thời. Nếu gõ nhầm, hãy nhấn phím Backspace để xoá lỗi đó và tiếp tục gõ dữ liệu vào. Trong khi gõ dữ liệu, có thể dùng các phím sau:

Trang 10

+ End: Để chuyển nhanh con trỏ text tới cuối phần dữ liệu trên dòng nhập dữ liệu. + Home: Để chuyển nhanh con trỏ text tới đầu phần dữ liệu. + Backspace: Để xoá các kí tự bên trái của trỏ Text. + Delete: Để xoá kí tự bên phải của trỏ Text. 3. Nhấn phím Enter để chuyển sang ô tiếp theo. Chú ý: Để thay việc ấn Enter có thể dùng các phím chỉ hướng ← (Left Arrow), → (Right Arrow), ↑ (Up Arrow), ↓ (Down Arrow). Nếu thay đổi ý định nhập dữ liệu trước khi ấn Enter, hãy nhấn phím ESC hoặc kích vào hộp X trên dòng nhập dữ liệu. Khi đó dữ liệu vừa gõ sẽ không được nhập vào ô hiện thời.

III.2. Một số thủ thuật nhập dữ liệu nhanh Excel cho phép nhập dữ liệu nhanh như sau: Nếu dữ liệu cần nhập là một dãy số hoặc chữ tuân theo một quy luật nào đó, ví dụ như các dãy: 1, 2, 3, 4, ....

2, 4, 6, 8, .... Jan, Feb, Mar, ... Mon, Tue, Wed, ... ta có thể nhập nhanh như sau: Trang 11

1. Gõ dữ liệu vào hai ô đầu của dãy (hoặc chỉ cần gõ vào một ô đầu của dãy trong trường hợp cần nhập dãy tháng hoặc ngày trong tuần). 2. Chọn hai (hoặc một) ô đầu. 3. Đặt trỏ chuột tại Fillhandle cho biến thành dấu +. Sau đó rê chuột sang phải hoặc xuống dưới để tăng dãy số.

Ví dụ:

Nếu cần nhập dữ liệu giống hệt nhau trong một hoặc vài vùng, hãy làm như sau: 1. Chọn vùng định vào dữ liệu (liền nhau hoặc cách nhau). 2. Gõ dữ liệu vào ô hiện thời. 3. Giữ Ctrl và nhấn Enter. Ví dụ: ô H1, H3, H4, H6 đều có dữ liệu là kg. Để nhập nhanh dữ liệu vào các ô này cần thực hiện như sau: Phải chọn các ô H1, H3, H4, H6 (giữ phím Ctrl khi cần chọn các ô không nằm cạnh nhau). Gõ chữ "kg" vào ô H6. Tiếp tục giữ Ctrl và nhấn phím Enter, kết quả cả bốn ô H1, H3, H4, H6 đều có cùng chữ "kg". Xem hướng dẫn trong hình sau:

Nếu định gõ dữ liệu vào một vùng xác định, hãy làm như sau: Trang 12

1. Chọn sẵn vùng cần vào dữ liệu. 2. Gõ dữ liệu vào ô đầu, sau đó nhấn Tab để chuyển con trỏ sang phải, sau đó xuống dưới (xem chiều mũi tên).

Nhấn Shift + Tab để chuyển con trỏ theo chiều ngược lại. Nhấn phím Enter để chuyển con trỏ xuống dưới, sau đó sang phải.

III.3. Gõ dữ liệu kiểu text Để gõ text vào một ô, gõ text cần nhập và ấn Enter. Excel sẽ tự động dóng hàng text về mép trái của ô (xem hình 2.8)

Hình 2.8

Chú ý: •

Khi text dài hơn độ rộng của một ô, Excel cho phép kéo dài kí tự sang các ô lân cận ở bên phải, nếu chúng rỗng (xem dữ liệu trong ô A1 trên hình 2.8).

•

Trong trường hợp các ô bên phải đã chứa dữ liệu thì ta sẽ không nhìn thấy một phần text vừa gõ vào (xem dữ liệu trong các B5, B6 - dữ liệu gõ vào là "Trần Thanh Bình" và "Hoàng Tuấn Hùng"). Đừng lo lắng về điều đó, text do ta đã gõ

Trang 13

vào vẫn còn nguyên vẹn trong ô hiện thời chỉ bị giấu đi một phần thôi. Chúng ta sẽ được học cách điều chỉnh độ rộng ô hoặc định dạng text trong bài sau.

III.4. Gõ dữ liệu kiểu số Gõ số thông thường: Để gõ một số thông thường vào bảng tính, hãy làm như sau: 1. Chọn ô muốn gõ số. 2. Gõ số cần nhập. 3. Nhấn Enter. Số được nhập vào sẽ được dồn sang bên phải ô. Ví dụ: muốn gõ số 5567 vào ô C8, hãy kích ô C8, gõ số 5567 và nhấn Enter. Chú ý: •

Một cách ngầm định, trong Excel, phần thập phân được ngăn cách với phần nguyên bởi dấu chấm (.). Do đó chúng ta phải gõ số 345.6 chứ không được gõ 345,6. Nếu gõ 345,6 Excel sẽ hiểu đó là dữ liệu kiểu text.

•

Khi gõ một số âm, hãy gõ dấu trừ (-), sau đó gõ tiếp những con số. Ví dụ, -123, -564, -45.765, ...

•

Khi số được gõ vào dài hơn độ rộng của một ô, Excel sẽ thể hiện số đó dưới dạng như 3E+04 (dạng khoa học, nghĩa là 3 nhân với 104) hoặc ######. Để xem toàn bộ số, hãy nới rộng độ rộng cột (xem phần sau).

III.5. Gõ dữ liệu kiểu ngày tháng Khi gõ dữ liệu kiểu ngày tháng và giờ trong bảng tính, ta chỉ việc gõ chúng theo những mẫu mà Excel có thể tự động nhận biết. Bảng sau đây liệt kê các khuôn dạng ngày tháng (Date) và thời gian (Time) của Excel. Gõ

Kiểu

Ghi chú

4 – Apr

d –mmm

Ngày, tháng

9/23/89 23 - Sep - 89 Sep - 89

m/d/y d-mmm-yy

Tháng, ngày, năm Ngày, tháng, năm

mmm – yy

Tháng và năm

Trang 14

12:01 Am 12:01:00 Am 12:01

h:mm AM/PM h:mm:ss AM/PM h:mm

Giờ, phút sáng hoặc chiều Giờ, phút, giây sáng hoặc chiều Giờ, phút

12:01:00 9/23/89 12:00

h:mm:ss m/d/yy h:mm

Giờ, phút, giây Tháng, ngày, năm, giờ, phút

Chú ý: Có thể gõ dấu "/ " hoặc dấu "-" để ngăn cách giữa các kí tự chỉ ngày tháng. Ví dụ: 1/23/94 hoặc 23 - Jan -94. •

Có thể gõ ngày và giờ trong cùng một ô nhưng bắt buộc ngày và giờ phải phân cách nhau bởi một dấu trống. Ví dụ 1/23/94 1:30 AM.

IV. Các phương tiện sửa và xoá IV.1. Sửa dữ liệu Sau khi gõ dữ liệu, đôi khi ta cần sửa lại dữ liệu cho chính xác. Có thể sửa dữ liệu trong các trường hợp sau:

IV.2. Thay toàn bộ dữ liệu cũ bằng dữ liệu hoàn toàn mới 1. Chọn ô chứa dữ liệu muốn thay thế. 2. Gõ dữ liệu mới. 3. Nhấn phím Enter. Ví dụ: Nếu muốn thay số "564" trong ô B8 bởi số 200000, hãy kích chuột vào ô B8, gõ 200000 và nhấn Enter.

IV.3. Sửa một phần nội dung trong ô 1. Chọn ô cần sửa nội dung. 2. Gõ phím F2 hoặc kích chuột vào dòng nhập dữ liệu sao cho xuất hiện trỏ text ở cuối phần dữ liệu trong ô hiện thời hoặc trong dòng nhập dữ liệu. 3. Nếu cần thiết, dùng các phím mũi tên để chuyển đến các vị trí cần sửa. 4. Xoá phần dữ liệu sai bằng các phím Delete hoặc Backspace hoặc gõ thêm phần dữ liệu cần bổ sung.

Trang 15

5. Nhấn phím Enter sau khi sửa xong dữ liệu. Ví dụ: Nếu muốn sửa "Phạm T Hải" thành "Phạm Thanh Hải" tại ô C5, hãy kích chuột vào ô C5. Sau khi nhấn phím F2, con trỏ text xuất hiện ở cuối phần dữ liệu, dùng phím Left Arrow để chuyển trỏ text tới sau chữ T, gõ thêm "hanh" và ấn phím Enter.

IV.4. Xoá dữ liệu 1. Chọn ô chứa dữ liệu cần xoá. 2. Nhấn phím Delete.

V. Cách di chuyển con trỏ ở trong bảng tính Khi làm việc với bảng tính, cần chú ý tới vị trí con trỏ ô trong bảng tính

Khi cần nhập, sửa, xoá dữ liệu hoặc gõ công thức tại ô nào, cần chuyển con trỏ bản ghi tới ô đó.

V.1. Di chuyển con trỏ ô bằng chuột Kích chuột tại ô muốn chọn. Trong trường hợp ô cần chọn không xuất hiện trên màn hình, kích chuột vào các mũi tên của thanh cuốn dọc (Vertical scroll bar) hoặc thanh cuốn ngang (horizontal bar) để cuộn tới miền bảng tính cần thiết và kích vào ô cần chọn.

Trang 16

V.2. Di chuyển con trỏ ô bằng bàn phím

Phím ←,

Công dụng

Shift

+ Để chuyển con trỏ qua trái một ô.

Tab →, Tab

Để chuyển con trỏ qua phải một ô.

↑

Để chuyển con trỏ xuống dưới một ô.

↓

Để chuyển con trỏ xuống dưới một ô.

Ctrl + ←, →

Để chuyển con trỏ ô đến bên trái nhất hoặc bên phải nhất của vùng dữ liệu.

Ctrl + ↑, ↓

Để chuyển con trỏ ô đến đỉnh hoặc đáy của vùng dữ liệu (tức vùng trên bảng tính có chứa dữ liệu).

PgUp

Để chuyển con trỏ ô lên một màn hình.

PgDn

Để chuyển con trỏ ô xuống dưới một màn hình.

Home

Để chuyển con trỏ tới ô bên trái nhất trong một hàng.

Ctrl + Home

Để chuyển con trỏ tới góc trên bên trái của bảng tính.

Ctrl + End

Để chuyển con trỏ tới góc dưới bên trái của bảng tính.

End+←, →,

End Có tác dụng như sau: nếu ô hiện thời là ô trống, sẽ di

End+↑, chuyển đến ô trống kế tiếp theo hướng mũi tên. Nếu ô

End+↓

hiện thời đang chứa nội dung, sẽ di chuyển theo hướng mũi tên đến ô kế tiếp có chứa nội dung.

End + Enter

Chuyển con trỏ ô tới cột cuối cùng trong hàng.

V.3. Dùng hộp hội thoại GoTo 1. Nhấn phím F5 hoặc kích thực đơn Edit và kích tiếp lệnh GoTo, xuất hiện hộp hội thoại.

Trang 17

2. Gõ địa chỉ của hộp muốn chuyển tới trong hộp Reference, ví dụ I45. Muốn chuyển tới một ô trên bảng tính cụ thể nào đó, gõ tên bảng tính, dấu chấm than và địa chỉ ô. Ví dụ, Sheet2!H50. 3. Click OK. Ghi chú: Có thể chuyển nhanh tới ô cần chọn bằng cách gõ địa chỉ ô cần chuyển tới trong hộp Name Box và ấn Enter.

VI. Các thao tác với file dữ liệu VI.1. Ghi file hiện thời vào đĩa Sau khi nhập dữ liệu vào máy, dữ liệu mới chỉ lưu trữ trong bộ nhớ của máy tính. Nếu thoát khỏi Excel, dữ liệu đó sẽ mất, do đó cần phải lưu trữ các dữ liệu đó vào đĩa để sau đó sử dụng sau. Các bước thực hiện: 1. Mở thực đơn File, kích chuột vào lệnh Save (hoặc kích chuột vào nút Save trên Standard toolbar). 2. Nếu đây là lần đầu tiên lưu file vào đĩa, Excel sẽ xuất hiện hộp hội thoại Save As (xem hình).

Trang 18

3. Trong hộp Save in kích chuột vào nút Combo, xuất hiện cấu trúc folder (thư mục), kích đúp chuột vào tên các thư mục để chọn thư mục sẽ lưu trữ file. Ví dụ: Nếu muốn lưu trữ file trong thư mục Baihoc thuộc ổ C, hãy kích chuột vào nút Combo, sau đó kích đúp vào tên ổ đĩa C và kích đúp tiếp vào tên thư mục Bangtinh (xem hình)

3. Kích chuột vào hộp File name, xoá chữ Book1, gõ tên file mới, ví dụ BD10A. Tên file trong môi trường Windows 9x cho phép tối đa 255 kí tự, trong khi ở môi trường DOS và Windows 3.1, tên file chỉ được phép tối đa 8 kí tự. 4. Nhấn phím Enter hoặc kích nút Save. Excel tự động gán phần mở rộng .XLS vào tên file. Do đó tên file đầy đủ lúc này là BD10A.xls. Tên file vừa đặt sẽ xuất hiện trên thanh tiêu đề của bảng tính. Ghi chú:

• Trong trường hợp tên file vừa gõ vào trùng với tên file có sẵn tại chính thư mục vừa chọn, sẽ xuất hiện thông báo tương tự như sau:

Trang 19

• Máy thông báo rằng đã tồn tại file BD10A.xls tại thư mục Bangtinh của ổ đĩa C, bạn có muốn thay thế file cũ hay không? Nếu kích bút Yes, máy sẽ xoá file cũ và ghi nội dung của file mới vào đĩa với tên cũ. Nếu chọn No, thì file cũ sẽ được giữ nguyên và nội dung của file mới sẽ không được ghi vào đĩa. • Khi ghi file từ lần thứ hai trở đi, lệnh Save sẽ ghi dữ liệu được bổ sung hoặc thay đổi của file đó vào đĩa mà không xuất hiện hộp hội thoại.

VI.2. Ghi file hiện thời vào đĩa với tên khác Có thể sao chép file hiện thời trên máy tính thành file khác với nội dung hiện tại bằng cách sau: 1. Mở thực đơn File và chọn lệnh Save As. Xuất hiện hộp hội thoại như hình 3.1. 2. Nếu sao chép thành file mới tại thư mục cũ thì chỉ việc xoá tên file cũ trong hộp File name và gõ tên mới. Nếu muốn giữ nguyên tên file cũ nhưng sao tới ổ đĩa hoặc thư mục khác thì chỉ việc chọn lại ổ đĩa hoặc thư mục trong hộp Save in. Nếu muốn đổi cả tên file và nơi lưu trữ file thì phải làm cả hai việc trên. 3. Nhấn Enter hoặckích nút Save. Sau bước thứ 3, Excel sẽ ghi nội dung hiện thời trên máy tính vào đĩa vào một file mới với tên file khác hoặc với tên file cũ nhưng đặt ở vị trí khác. * Muốn lưu tập tin ở dạng phiên bản khác của Excel, mở khung Save as type để chọn. * Có thể click nút Tools\General Option (mở hộp thoại Save Option) để có thêm những lựa chọn khác.

Trang 20

+ Always create backup: Luôn lưu lại tệp tin cũ khi sửa đổi. + Read-only recommended: Thuộc tính chỉ được xem + Password to open: Mật khẩu khi mở tệp + Password to modify: Mật khi mở sửa.

VI.3. Gọi một file có sẵn từ đĩa ra màn hình Khi muốn gọi một file đã được lưu trữ từ đĩa ra màn hình để làm việc tiếp, hãy thực hiện các bước sau: 1. Mở thực đơn File, chọn lệnh Open (hoặc kích nút Open

trên Standard

toolbar). Xuất hiện hộp hội thoại Open. 2. Trong hộp Save in, chọn ổ đĩa và thư mục lưu trữ file cần mở. 3. Kích đúp vào tên file cần mở. Hoặc kích chọn tên file cần mở và kích vào nút Open. Ví dụ: Các bước mở file BD10A.xls được tiến hành theo các bước như hình sau: Ghi chú:

Trang 21

•

Một cách ngầm định, Excel lưu trữ 4 file được mở gần nhất ở cuối thực đơn file. Do đó, để mở một trong 4 file đó chỉ việc kích vào thực đơn File và kích vào tên file cần mở.

•

Trong Windows 9x, có thể mở file được sử dụng thường xuyên bằng cách kích vào nút Start, trỏ tiếp vào Documents và kích vào tên file cần mở.

•

Tại một thời điểm có thể mở nhiều file của Excel. Có thể xem danh sách các file đang mở ở dưới thực đơn Window. Khi cần kích hoạt file nào, hãy kích vào tên file đó.

VI.4. Tạo một file mới Để tạo một file mới hãy làm như sau: Mở thực đơn File, chọn lệnh New, xuất hiện hộp hội thoại:

Trang 22

2. Chọn Workbook và kích OK. Ghi chú: •

Có thể dùng nút New

trên Standard toolbar.

VI.5. Đóng file hiện thời 1. Mở thực đơn File. 2. Kích chọn lệnh Close. Nếu nội dung file có thay đổi, sẽ xuất hiện thông báo như hình 1.3. Chú ý lựa chọn cách trả lời thích hợp (Yes - nếu muốn lưu những thay đổi, No - nếu không muốn lưu lại những thay đổi).

Trang 23

Câu hỏi và bài tập 1. Phân biệt sự khác nhau sau khi gõ một chuỗi kí tự và sau khi gõ một dãy số. 2. Tại sao khi gõ số 100000 dưới dạng 100 000 vào một ô của bảng tính và nhấn phím Enter thì dữ liệu lại nằm bên trái ô. 3. Tại sao khi gõ ngày 26 tháng 3 năm 1999 dưới dạng 26/3/99 và nhấn phím Enter thì dữ liệu lại nằm bên trái ô. 4. Nhập các dữ liệu vào bảng tính theo mẫu sau. Khi nhập, chú ý sử dụng các kĩ thuật nhập nhanh. Sau khi nhập dữ liệu xong, kiểm tra và điều chỉnh dữ liệu cho thật chính xác. Thoát khỏi Excel, xuất hiện hộp hội thoại hỏi ta có lưu trữ file hiện thời vào đĩa không, hãy kích nút Yes và ghi vào đĩa với tên BD10A.xls. A

B

1

Bảng điểm học kì I

2

Môn: Toán

3

Lớp: 10 A

C

D

E

F

G

Hệ số 2

Hệ số 3

1T

HK

4

Hệ số 1

5 6

STT

Họ và tên

7

1

Lê Văn Thanh

8

2

9

Ngày sinh

M

15'

6/17/83

7

9

7

8

Hoàng Quốc Tuấn

5/4/83

8

10

9

9

3

Nguyễn Mạnh Hà

9/12/83

5

6

7

6

10

4

Lê Thu Nga

4/24/83

9

7

8

9

11

5

Phạm Hồng Vân

1/12/83

8

9

6

8

12

6

Nguyễn Thu Thuỷ

6/4/83

4

5

4

5

13

7

Hoàng Vân Trang

9/12/83

9

7

10

8

14

8

Vũ Tuấn Hùng

6/20/83

9

8

7

8

15

9

Vũ Thanh Hằng

9/25/83

9

9

8

10

3/15/83

6

7

7

5

16

10 Lê Hằng Nga

5. Nêu chức năng của các lệnh New, Save, Save As, Open, Close và Exit trên thực đơn File. 6. Nêu sự giống và khác nhau giữ hai lệnh Save và Save As.

Trang 24

7. Mở file vừa ghi, sửa dữ liệu của bài tập số 4 trong bài ở trên và ghi vào đĩa với tên BD10A.xls. 8. Dùng lệnh Save As để sao chép thành file BD10B.xls trong cùng thư mục. Sau đó sửa lại theo mẫu sau và ghi lại vào đĩa. A

B

1

Bảng điểm học kì I

2

Môn: Toán

3

Lớp: 10 B

C

D

E

F

G

4 5 6

Hệ số 1 STT

Họ và tên

Ngày sinh

Hệ số 2

M

15'

Hệ số 3

1T

HK

7

1 Nguyễn Vân Anh

6/7/83

6

8

7

7

8

2 Vũ Hoà Bình

6/7/83

4

5

5

4

9

3 Hoàng Mạnh Thắng

7/6/83

8

9

10

9

10

4 Nguyễn Hoàng Hải

8/1/83

7

8

8

8

11

5 Phạm Hồng Vân

7/3/83

9

9

8

9

12

6 Hoàng Thu Trang

6/5/83

9

9

8

10

13

7 Phạm Anh Tuấn

5/18/83

5

7

6

7

14

8 Trần Thanh Hải

5/8/83

9

8

10

10

15

9 Nguyễn Hoàng Hà

6/7/83

9

9

8

10

10 Nguyễn Thanh Tùng

6/15/83

8

7

8

8

16

Trang 25

Bài 3: Các thao tác với hàng, cột, ô trong một bảng tính I. Định dạng cột, dòng, ô I.1.

Thay đổi độ rộng cột

I.1.1. Dùng chuột 1. Chọn số cột cần điều chỉnh độ rộng. 2. Đặt chuột ở mép phải vùng chọn tại phần tiêu đề cột sao cho biến thành dạng mũi tên hai đầu 3. Rê chuột sang trái hoặc phải để giảm hoặc tăng độ rộng cột. 4. Thả chuột khi ưng ý. I.1.2. Dùng thực đơn 1. Chọn số cột cần điều chỉnh độ rộng. 2. Mở thực đơn Format, trỏ vào lệnh Column và kích tiếp vào Width, xuất hiện hộp hội thoại

3. Trong hộp Colummn width, gõ trị số cho độ rộng cột theo các giá trị từ 0 tới 255 (biểu thị cho số kí tự được phép gõ theo độ rộng của ô). 4. Kích OK.

I.2.

Thay đổi độ cao hàng

I.2.1. Dùng chuột 1. Chọn số hàng cần điều chỉnh độ cao.

Trang 26

2. Đặt chuột ở mép dưới vùng chọn tại phần tiêu đề hàng sao cho biến thành dạng mũi tên hai đầu. 3. Rê chuột xuống dưới hoặc lên trên để giảm hoặc tăng độ cao của hàng. Thả chuột khi ưng ý. I.2.2. Dùng thực đơn 1. Chọn số hàng cần điều chỉnh độ cao. 2. Mở thực đơn Format, trỏ vào lệnh Row và kích tiếp vào Height, xuất hiện hộp hội thoại

3. Trong hộp Row height, gõ trị số cho độ cao của hàng theo các giá trị từ 0 tới 409 (biểu thị cho chấm điểm pt được phép gõ theo độ cao của hàng). 4. Kích OK. Ghi chú: •

Nếu muốn độ rộng cột hoặc độ cao của hàng vừa khít với nội dung dài nhất được chứa trong nó, hãy kích đúp vào mép phải hoặc mép dưới trong các vùng tiêu đề cột hoặc hàng.

I.3.

Nối và tách ô: Nối: Chọn những ô muốn nối, click biểu tượng trên thanh công cụ để nối. Tách: (chỉ tách được nhữn ô đã nối) Chọn ô đã nối, sử dụng Menu Edit\Clear\

Formats.

II. Cách chọn hàng, cột và ô II.1. •

Chọn hàng Chọn một hàng: kích chuột vào tiêu đề của hàng đó.

Trang 27

•

Chọn nhiều hàng liền nhau: kích và rê chuột trongvùng tiêu đề hàng; hoặc kích vào tiêu đề hàng đầu, giữ Shift và kích vào tiêu đề hàng cuối.

•

Chọn nhiều hàng cách nhau: kích vào một tiêu đề hàng, giữ Ctrl và kích vào các tiêu đề hàng còn lại.

II.2.

Chọn cột

•

Chọn một cột: kích chuột vào tiêu đề của cột đó.

•

Chọn nhiều cột liền nhau: kích và rê chuột trong vùng tiêu đề cột; hoặc kích vào tiêu đề cột đầu, giữ Shift và kích vào tiêu đề cột cuối.

•

Chọn các cột cách nhau: kích vào tiêu đề một cột, giữ Ctrl và kích vào các tiêu đề cột còn lại.

II.3.

Chọn ô

•

Chọn một ô: kích chuột vào ô cần chọn.

•

Chọn nhiều ô liền nhau: kích và rê chuột để chọn; hoặc kích vào một ô, giữ Shift và kích ô đối diện trong vùng ô cần chọn.

•

Chọn các ô cách nhau: kích chọn một ô, giữ Ctrl và kích vào các ô còn lại.

III. Cách chèn hàng, cột và ô Đôi khi sau khi gõ dữ liệu, ta có nhu cầu chèn thêm hàng, cột hoặc ô để điền thêm dữ liệu. Các thao tác chèn thêm được thực hiện như sau.

III.1. Chèn hàng 1. Chọn số hàng cần chèn bằng cách rê chuột tại vùng tiêu đề hàng. 2. Kích nút phải của chuột tại vùng chọn để xuất hiện thực đơn ngắn và chọn lệnh Insert (hoặc mở thực đơn Insert và chọn lệnh Rows). 3. Nếu cần thiết, gõ nội dung vào các hàng trống vừa chèn thêm Ví dụ:

Trang 28

III.2. Chèn cột 1. Chọn số cột cần chèn bằng cách rê chuột tại vùng tiêu đề cột. 2. Kích nút phải của chuột tại vùng chọn để xuất hiện thực đơn ngắn và chọn lệnh Insert (hoặc mở thực đơn Insert và chọn lệnh Columns). 3. Nếu cần thiết, gõ nội dung vào các cột trống vừa chèn thêm Ví dụ:

III.3. Chèn ô 1. Chọn số ô cần chèn Kích nút phải của chuột tại vùng chọn để xuất hiện thực đơn ngắn và chọn lệnh Insert (hoặc mở thực đơn Insert và chọn lệnh Cells). Xuất hiện hộp hội thoại

3. Chọn một trong các lựa chọn sau: - Shift Cells Right: chèn và đẩy các ô được chọn sang phải. - Shift Cell Downs: chèn và đẩy các ô được chọn xuống dưới. - Entire Row: chèn toàn bộ các hàng trống lên phía trên các ô đã chọn. - Entire Colummn: chèn toàn bộ các cột trống vào bên trái các ô đã chọn. 4. Kích vào OK. 5. Nếu cần thiết, gõ nội dung vào các ô trống vừa chèn thêm Trang 29

Ví dụ:

IV. Huỷ hàng, cột và ô Khi cần huỷ hàng, cột hoặc ô hãy thực hiện theo các cách sau.

IV.1. Huỷ hàng 1. Chọn số hàng cần huỷ bằng cách rê chuột tại vùng tiêu đề hàng. 2. Kích nút phải của chuột tại vùng chọn để xuất hiện thực đơn ngắn và chọn lệnh Delete (hoặc mở thực đơn Edit và chọn lệnh Delete).

IV.2. Huỷ cột

1. Chọn số cột cần huỷ bằng cách rê chuột tại vùng tiêu đề cột. 2. Kích nút phải của chuột tại vùng chọn để xuất hiện thực đơn ngắn và chọn lệnh Delete (hoặc mở thực đơn Edit và chọn lệnh Delete). Ví dụ:

IV.3. Huỷ ô 1. Chọn số ô cần chèn

Trang 30

Kích nút phải của chuột tại vùng chọn để xuất hiện thực đơn ngắn và chọn lệnh Delete (hoặc mở thực đơn Edit và chọn lệnh Delete). Xuất hiện hộp hội thoại:

3. Chọn một trong các lựa chọn sau: - Shift Cells left: huỷ các ô được chọn và đẩy các ô bên phải ô vừa huỷ sang trái. - Shift Cell up: huỷ các ô được chọn và đẩy các ô bên dưới ô được chọn lên trên. - Entire Row: huỷ toàn bộ các hàng chứa các ô đã chọn. - Entire Column: huỷ toàn bộ các cột chứa các ô đã chọn. 4. Kích vào OK. Ví dụ:

V. Các thao tác với các bảng tính trong một Workbook V.1. Kích hoạt một bảng tính Trong một Workbook có rất nhiều WorkSheet, để kích hoạt một worksheet, hãy làm như sau: Cách 1: Kích chuột vào tên Worksheet cần kích hoạt. Nếu không nhìn thấy tên Worksheet cần thiết, hãy dùng các nút chuyển sau để hiện tên Worksheet cần kích hoạt trongdanh sách các sheet và kích vào tên nó.

Trang 31

Nút

Tác dụng chuyển tới tên bảng tính tiếp sau chuyển tới tên bảng tính cuối cùng chuyển tới tên bảng tính ngay trước đó chuyển tới tên bảng tính đầu tiên

Cách 2: kích nút phải chuột tại vùng các nút chuyển nói trên, xuất hiện danh sách các sheet. Kích chuột vào tên sheet cần kích hoạt. Nếu số sheet trên bảng tính nhiều hơn 16 sheet, kích tiếp vào More sheets..., xuất hiện hộp hội thoại Activate, kích vào tên sheet cần thiết và kích nút OK.

V.2. Đổi tên bảng tính Tên ngầm định Excel đặt cho các Worksheet là Sheet 1, Sheet 2, Sheet 3, ... Trong quá trình làm việc thực tế, việc giữ nguyên tên như vậy sẽ dẫn đến khó khăn trong việc phải nhớ Sheet nào chứa nội dung gì khi chúng ta sử dụng quá nhiều bảng tính. Excel cho phép đổi tên các Worksheet theo các bước sau: 1. Kích nút phải chuột tại tên Worksheet cần đổi, ví dụ kích nút phải tại tên Sheet. Xuất hiện thực đơn ngắn:

2. Kích tiếp vào Rename. Một quầng đen bao quanh tên cũ. 3. Gõ tên mới (ví dụ, HKI-10A, nếu Sheet 1 chứa bảng điểm học kì I của lớp 10A). 4. Nhấn Enter.

V.3. Chèn thêm bảng tính mới Trong Excel ngầm định có ba bảng tính được mở trong một Workbook. Chúng ta có thể chèn thêm bảng tính mới khi có nhu cầu theo các bước sau: Trang 32

1. Kích nút phải tại vị trí cần chèn thêm bảng tính trong danh sách các Sheet, ví dụ Sheet 3. Xuất hiện thực đơn ngắn. 2. Kích vào Insert..., xuất hiện hộp hội thoại Insert.

3. Kích chọn Worksheet và kích OK. Bảng tính mới sẽ được chèn vào vị trí hiện thời và đẩy sheet cũ (trong trường hợp này là Sheet 3) sang bên phải.

V.4. Xoá bảng tính Excel còn cho phép xoá các bảng tính. Khi xoá bảng tính, mọi dữ liệu trong nó đều bị xoá sạch. Các bước thực hiện như sau: 1. Kích nút phải chuột tại tên bảng tính cần xoá. Xuất hiện thực đơn ngắn. 2. Kích tiếp vào Delete. Xuất hiện thông báo hỏi ta có thực sự muốn xoá bảng tính hay không? 3. Kích OK nếu muốn xoá thật. Kích Cancel nếu không muốn xoá nữa.

V.5. Di chuyển hoặc sao chép một bảng tính Đôi khi chúng ta có nhu cầu chuyển hai bảng tính xa nhau nằm cạnh nhau hoặc chuyển một bảng tính tới file khác hoặc có nhu cầu sao chép thành hai bảng tính giống hệt nhau. Để làm điều đó, hãy thực hiện theo các bước sau: Trang 33

1. Kích nút phải tại tên bảng tính muốn di chuyển hoặc sao chép, xuất hiện thực đơn ngắn. 2. Kích vào Move or Copy..., xuất hiện hộp hội thoại sau

3. Tại hộp To book, chọn tên file cần chuyển hoặc sao chép bảng tính tới đó. Nếu chỉ di chuyển hoặc sao chép trong file hiện thời thì không cần phải chọn trong hộp này. Nếu muốn di chuyển hoặc sao chép tới file mới thì chọn (New book). 4. Trong hộp Before sheet, chọn tên sheet sẽ nằm bên phải bảng tính đích sau khi di chuyển hoặc sao chép. 5. Kích OK nếu muốn di chuyển bảng tính. Trong trường hợp muốn sao chép thì phải kích chọn Create a copy rồi mới kích OK.

Trang 34

Câu hỏi và bài tập 1. Nêu các thao tác chọn hàng, cột và ô. 2. Nêu các thao tác chèn và huỷ các hàng, cột, ô. 3. Nêu sự khác nhau giữa việc huỷ một ô và xoá dữ liệu trong một một ô. 4. Hãy nêu cách làm cho độ rộng của 5 cột liền nhau trong một bảng tính vừa khít nội dung của chúng chỉ bằng hai động tác chuột. 5. Điều chỉnh độ rộng cột, độ cao hàng bằng chuột và bằng thực đơn khác nhau như thế nào về độ chính xác. 6. Thực hành các thao tác kích hoạt bảng tính theo hướng dẫn trong bài. 7. Mở file BD10A.xl và BD10B.xls, chèn thêm cột giới tính và điền dữ liệu theo mẫu sau và ghi lại vào đĩa.

Mẫu 1: A B

C

D

E

F

G

Hệ số 2

Hệ số 3

1 Bảng điểm học kì I 2 Môn: Toán 3 Lớp: 10 A 4 5

Hệ số 1

6 STT Họ và tên

Ngày sinh Giới tính M

15'

1T

HK

7

1

Lê Văn Thanh

6/17/83

Nam

7

9

7

8

8

2

Hoàng Quốc Tuấn

5/4/83

Nam

8

10

9

9

9

3

Nguyễn Mạnh Hà

9/12/83

Nam

5

6

7

6

10

4

Lê Thu Nga

4/24/83

Nữ

9

7

8

9

11

5

Phạm Hồng Vân

1/12/83

Nữ

8

9

6

8

12

6

Nguyễn Thu Thuỷ

6/4/83

Nữ

4

5

4

5

13

7

Hoàng Vân Trang

9/12/83

Nữ

9

7

10

8

14

8

Vũ Tuấn Hùng

6/20/83

Nam

9

8

7

8

15

9

Vũ Thanh Hằng

9/25/83

Nữ

9

9

8

10

3/15/83

Nữ

6

7

7

5

E

F

G

16 10 Lê Hằng Nga Mẫu 2: A B 1

C

D

Bảng điểm học kì I Trang 35

2

Môn: Toán

3

Lớp: 10 B

4 5 6

Hệ số 1 STT Họ và tên

Hệ số 2

Hệ số 3

Ngày sinh Giới tính M

15'

1T

HK

7

1

Nguyễn Vân Anh

6/7/83

Nữ

6

8

7

7

8

2

Vũ Hoà Bình

6/7/83

Nữ

4

5

5

4

9

3

Hoàng Mạnh Thắng 7/6/83

Nam

8

9

10

9

10

4

Nguyễn Hoàng Hải

8/1/83

Nam

7

8

8

8

11

5

Phạm Hồng Vân

7/3/83

Nữ

9

9

8

9

12

6

Hoàng Thu Trang

6/5/83

Nữ

9

9

8

10

13

7

Phạm Anh Tuấn

5/18/83

Nam

5

7

6

7

14

8

Trần Thanh Hải

5/8/83

Nam

9

8

10

10

15

9

Nguyễn Hoàng Hà

6/7/83

Nam

9

9

8

10

16

10 Nguyễn Thanh Tùng 6/15/83

Nam

8

7

8

8

8. Mở các file BD10A.xls và BD10B.xls và đổi tên các sheet chứa dữ liệu đã gõ thành tên mới là HKI-10A và HKI-10B. 9. Chèn thêm 5 bảng tính trong file BD10A.xls và sau đó xoá chúng đi. 10. Nêu sự khác nhau giữa xoá file và xoá bảng tính. 11. Sao chép sheet HKI-10A trong cùng file BD10A.xls và đổi tên thành HKI-10A Sau đó tự vào điểm học kì II cho các bạn. Làm tương tự đối với file BD10B.xls.

12. Sao chép các sheet HKI-10A và HKI-10B sang cùng file mới có tên là BDKHOI10.xls.

Trang 36

Bài 4: Sử dụng công thức và các hàm tính toán cơ bản I. Sử dụng Công thức trong Excel I.1.

Các toán tử Khái niệm: Công thức có thể ví là trụ cột của bảng tính. Một công thức đôi khi

chỉ là một kí hiệu toán học dùng để mô tả mối liên hệ giữa các con số, ví dụ như 5+4+10 = 19; hoặc có thể là một công thức phức tạp như dq = 1/2(d x+d y) + 1/2(d x- d y) cosq +xysin2q Trong Excel, công thức bao giờ cũng phải bắt đầu bởi dấu bằng "=". Dạng tổng quát của công thức trong Excel như sau: = Trong đó, là tập hợp các hằng, các địa chỉ ô, các hàm, các toán tử,...Các toán tử trong một công thức có dạng sau: I.1.1. Toán tử số: + (cộng), / (chia) - (trừ), ^ (phép luỹ thừa) * (nhân), % (phần trăm) I.1.2. Toán tử chuỗi & nối chuỗi I.1.3. Toán tử so sánh > lớn hơn, >= lớn hơn hoặc bằng < nhỏ hơn, <= nhỏ hơn hoặc bằng = bằng, <> khác

Trang 37

I.2.

Nhập công thức vào bảng tính

I.2.1. Quy tắc chung 1. Kích hoạt một ô trống sẽ chứa giá trị của công thức. 2. Gõ dấu = 3. Gõ biểu thức. 4. Nhấn Enter. Sau khi nhấn Enter, trên ô hiện thời sẽ hiện giá trị công thức, còn bản thân công thức hiện trên dòng nhập dữ liệu. I.2.2. Công thức đơn giản Công thức cơ bản trong Excel chỉ là những phép tính, ví dụ như 45-10 = 35. Tuy nhiên, để Excel hiểu đó là công thức thì phải làm như sau: 1. Chọn ô chứa kết quả. 2. Gõ = 45 - 10 3. Nhấn Enter. Ngay sau khi nhấn Enter, trong ô vừa chọn xuất hiện giá trị 35, nhưng trên dòng nhập dữ liệu (Formula bar) xuất hiện công thức vừa gõ vào. Tuy nhiên, trong Excel, hiếm khi người ta gõ thẳng giá trị cần tính toán vào công thức mà thường dùng các địa chỉ tham chiếu đến giá trị đó. Ví dụ 1: Giả sử trong ô C1 chứa số 20, trong ô C2 chứa số 40, ta có thể tạo công thức: =20+40, nhưng không nên dùng cách như vậy. Thay vào đó, ta có thể dùng công thức sau: =C1+C2. Excel sẽ tính tổng các con số trong các ô C1 và C2 và cho giá trị là 60. Nếu thay giá trị ô C2 bằng 100, thì giá trị của công thức tự động biến thành 120. Ví dụ 2: Giả sử ô A2 chứa xâu kí tự. "Thời khoá biểu ", ô B2 chứa xâu kí tự "- Khối 10" và trong ô A4 chứa công thức: =A2&C2. Khi đó giá trị của ô A4 sẽ là "Thời khoá biểu khối 10". Trang 38

Chú ý: Khi gõ công thức phải luôn luôn bắt đầu bởi dấu bằng "=". Đôi khi, do thói quen, nếu ta gõ công thức như kiểu viết tay, ví dụ như 20+40 =. Khi đó, Excel sẽ không hiện kết quả tính toán và giá trị được hiển thị trong ô hiện thời dưới dạng"20+40=", vì Excel hiểu đó là xâu kí tự. •

Trong công thức cho phép gõ dấu cách.

•

Nếu gõ công thức chưa đầy đủ hoặc sai (ví dụ: = 1+), và nhấn Enter, Excel sẽ xuất hiện thông báo tương tự như sau:

Nếu chấp nhận kết quả do Excel gợi ý, hãy kích vào nút Yes. Nếu muốn tự sửa công thức, kích vào nút No, Excel hiện tiếp hộp hội thoại hướng dẫn cách sửa lỗi tương tự như sau:

Sau khi đọc xong hướng dẫn, kích vào nút OK, xuất hiện con trỏ text trong ô chứa công thức. Hãy tự sửa lỗi và nhấn phím Enter.

I.3.

Địa chỉ tương đối và tuyệt đối Tên của mỗi ô hay vùng được gọi là địa chỉ của ô hay vùng đó. Có 2 loại địa chỉ:

địa chỉ tuơng đối và địa chỉ tuyệt đối.

Trang 39

I.3.1. Địa chỉ tương đối: Địa chỉ bình thường của ô hoặc vùng khi nhập vào trong công thức gọi là địa chỉ tương đối. Ví dụ : = B5+F4-D6 Khi sao chép công thức (click & drag) các địa chỉ tương đối trong công thức sẽ tự biến đổi theo vị trí tương ứng ở nơi được chép đến. Ví dụ : khi sao chép công thức ở ví dụ trên sang ô bên phải phía dưới sẽ đổi thành =C6+C5+E7 I.3.2. Địa chỉ tuyệt đối: Các địa chỉ trong công thức đứng sau dấu $ sẽ trở thành địa chỉ tuyệt đối. Có thể chỉ đặt giá trị tuyệt đối theo dòng và cột. Ví dụ: =$B$5+$F4-D$6 -

$B$5 : Đặt giá trị tuyệt đối cả cột lẫn dòng (có thể nhấn F4).

-

$F4 : Đặt giá trị tuyệt đối theo cột.

-

D$6 : Đặt giá trị tuyệt đối theo dòng.

Khi sao chép công thức (click & drag), các địa chỉ tuyệt đối trong công thức sẽ không thay đổi. Ví dụ : khi sao chép công thức ở ví dụ 3 sang ô bên phải phía dưới sẽ đổi thành =$B$5+$F5+E$6

I.4.

Liên kết dữ liệu

I.4.1. Liên kết dữ liệu giữa các bảng tính Trong một tập tin Excel bao gồm nhiều bảng tính (Worksheet), khi tạo ra công thức có thể sử dụng dữ liệu trong các bảng tính kkhác nhau. Muốn nhập địa chỉ của một ô ở một bảng tính khác vào công thức, cần nhập tên của bảng tính đó và ký tự (!) trước địa chỉ ô. Trang 40

Ví dụ : =D4+Sheet2!D4 (ô D4 của bảng tính hiện hành cộng với ô D4 ở bảng tính Sheet2) I.4.2. Liên kết dữ liệu giữa các tập tin Excel: Mở các tập tin muốn liên kết trên cùng một cửa sổ Excel, muốn nhập địa chỉ ô ở một tập tin khác vào công thức, nhập tên tập tin đó (cả phần mở rộng .xls) trong dấu ngoặc vuông [], và tên bảng tính (Sheet), tất cả có thể để trong dấu nháy đơn ’’ và ký tự (!) ở trước địa chỉ ô. Ví dụ : =D4+’[ktoan.xls]Sheet1’!D4 (ô D4 của bảng tính hiện hành cộng với ô D4 ở Sheet1 của tập tin ktoan.xsl) Để chuyển đổi giữa các tập tin trong cửa sổ Excel, chọn trình đơn Window\Tên tập tin.

II. Khái niệm hàm trong Excel: Giả sử trong các ô A3, B3, C3, D3, E3, F3 chứa các số. Để tính tổng các số này chúng ta có thể dùng công thức sau: =A3+B3+C3+D3+E3+F3, nhưng cách dùng nhanh hơn cả là sử dụng hàm =SUM(A3:F3). Cú pháp chung của hàm ([Đối số]) trong đó: được sử dụng theo tên quy ước của Excel. [Đối số] có thể có hoặc không. Đối số có thể là hằng, chuỗi kí tự, địa chỉ ô, địa chỉ vùng, biểu thức hoặc các hàm lồng nhau. Sau đây, chúng tôi sẽ liệt kê một số hàm hay dùng nhất.

II.1.

Nhóm hàm số

II.1.1. Hàm SQRT Hàm căn bậc hai. Cú pháp: Trang 41

SQRT(number)

Trong đó, number là số muốn lấy căn bậc hai. Nếu số có giá trị âm, hàm SQRT trả lại giá trị lỗi #NUM!. Ví dụ: SQRT(16) bằng 4 SQRT(-16) bằng #NUM! SQRT(ABS(-16)) bằng 4 II.1.2. Hàm ABS Trả lại giá trị tuyệt đối của số. Cú pháp: ABS(number)

Trong đó, number là số thực muốn lấy giá trị tuyệt đối. Ví dụ: ABS(2) bằng 2 ABS(-2) bằng 2 Nếu A1 chứa -16, thì SQRT(ABS(A1)) bằng 4 II.1.3. Hàm ROUND Làm tròn số Cú pháp: ROUND(number,num_digits)

Trong đó, number là số muốn làm tròn. Num_digits chỉ định số chữ số muốn làm tròn. Nếu num_digits lớn hơn 0 (zero), thì số được làm tròn đến số xác định sau số thập phân. Nếu num_digits bằng 0, thì number được làm tròn tới phần nguyên. Nếu num_digits nhỏ hơn 0, thì số được làm tròn về bên trái dấu chấm thập phân. Ví dụ: ROUND(2.15, 1) bằng 2.2 Trang 42

ROUND(2.149, 1) bằng 2.1 ROUND(-1.475, 2) bằng -1.48 ROUND(21.5, -1) bằng 20

II.2.

Nhóm hàm thống kê

II.2.1. Hàm Average Hàm này trả về giá trị trung bình cộng của các đối số. Cú pháp: AVERAGE(number1,number2, ...)

Trong đó: Number1, number2, ... là 1 tới 30 đối số. Ghi chú: •

Các đối số có thể là các hằng số, tên hoặc địa chỉ ô chứa số.

•

Nếu địa chỉ ô nằm trong đối số chứa text, các giá trị logic hoặc các ô trống thì các giá trị này được bỏ qua. Tuy nhiên, các ô chứa các giá trị bằng 0 đều được tính. Ví dụ: Nếu A1:A5 chứa các số 10, 7, 9, 27, và 2, thì: AVERAGE(A1:A5) bằng 11 AVERAGE(A1:A5, 5) bằng 10

II.2.2. Hàm Max Hàm này trả lại giá trị lớn nhất trong tập hợp các giá trị. Cú pháp: MAX(number1,number2,...)

Trong đó: number1,number2,... là 1 tới 30 số muốn tìm giá trị lớn nhất của chúng. Ghi chú:

Trang 43

•

Các đối số có thể là các hằng số, các ô trống, các giá trị logic hoặc text đại diện cho số. Các đối số là các giá trị lỗi hoặc text không thể chuyển thành số được sẽ gây ra lỗi trong công thức.

•

Nếu một đối số là địa chỉ vùng, chỉ có những giá trị số trong vùng mới được tính trung bình. Các ô trống, các giá trị logic, các giá trị text và các địa chỉ trong vùng được bỏ qua. Nếu muốn tính trung bình cả cho các giá trị logic và text thì dùng hàm MAXA thay thế.

•

Nếu hàm không chứa đối số sẽ cho giá trị bằng 0. Ví dụ: Nếu A1:A5 chứa các số 10, 7, 9, 27, và 2, thì: MAX(A1:A5) bằng 27 MAX(A1:A5,30) bằng 30

II.2.3. Hàm MIN Hàm này trả lại giá trị nhỏ nhất trong tập hợp các giá trị. Cú pháp: MIN(number1,number2, ...)

Trong đó: number1, number2,... là 1 tới 30 số muốn tìm giá trị nhỏ nhất của chúng. Đặc điểm của các đối số này hoàn toàn tưng tự như các đối số của hàm MAX. Ví dụ:

Nếu A1:A5 chứa các số 10, 7, 9, 27, và 2, thì: MIN(A1:A5) bằng 2 MIN(A1:A5, 0) bằng 0 II.2.4. Hàm SUM Hàm này cho giá trị là tổng các số trong một miền ô. Cú pháp: SUM(number1,number2, ...)

Trang 44

Trong đó, number1, number2, ... là 1 tới 30 đối số muốn tính tổng giá trị của chúng. Ghi chú: •

Các số, các giá trị logic và text đạI diện cho số mà ta gõ trực tiếp trong danh sách đối số đều được tính (xem hai ví dụ đầu tiên ở dưới đây).

•

Nếu một đối số là một vùng địa chỉ thì chỉ có những giá trị số trong vùng này được tính. Các ô trống, các giá trị logic hoặc các giá trị lỗi trong vùng này bị bỏ qua (xem ví dụ thứ ba).

•

Các đối số là các giá trị lỗi hoặc text mà không thể chuyển thành số sẽ gây lỗi. Ví dụ: SUM(3, 2) bằng 5 SUM("3", 2, TRUE) bằng 6 vì các giá trị text chuyển thành giá trị số và giá

trị logic TRUE chuyển thành số 1. Khác với ví dụ trên, nếu A1 chứa "3" và B1 chứa TRUE, thì: SUM(A1, B1, 2) bằng 2 vì các tham chiếu tới các giá trị không phải là số không được chuyển thành số. Nếu các ô A2:E2 chứa 5, 15, 30, 40, và 50, thì: SUM(A2:C2) bằng 50 SUM(B2:E2, 15) bằng 150 Chú ý: •

Khi cần dùng hàm SUM, hãy sử dụng nút AutoSum ( ) trên Toolbar thực hiện phép tính nhanh hơn. Các bước thực hiện như sau: 1. Kích hoạt ô chứa kết quả. (Khi cộng một cột, hãy kích hoạt ô phía dưới sát ô

cuối cùng của cột. Khi cộng một hàng, hãy kích hoạt ô sát phía bên phải của hàng). 2. Kích chuột vào nút AutoSum. lúc này trên thanh công thức xuất hiện =SUM(Đối số chỉ vùng).

Trang 45

3. Nhấn Enter, nếu tất cả các đối số chỉ vùng phù hợp với địa chỉ vùng cần chọn. Ngược lại, phải dùng chuột để chọn lại vùng địa chỉ (liên tục hoặc rời rạc) rồi mới nhấn Enter. II.2.5. Hàm COUNT Đếm số ô chứa số và các số trong danh sách đối số. Cú pháp: COUNT(value1,value2, ...)

Trong đó, value1, value2, ... là 1 tới 30 đối có thể chứa hoặc tham chiếu đến các ô chứa các loại dữ liệu khác nhau nhưng chỉ có các số được đếm. Ví dụ: Trong bảng sau

A 1

345

2

6/9/88

3 4

5:20:15

5

TRUE

6

#DIV/0!

7

Giá tiền

ta có: COUNT(A1:A7) bằng 3 COUNT(A2:A7) bằng 2 COUNT(A1:A7, 2) bằng 4 II.2.6. Hàm RANK Hàm này trả lại số thứ tự trong một danh sách số. Trang 46

Cú pháp: RANK(number,ref,order)

Trong đó, number là số muốn tìm thứ tự của nó trong dãy số. Ref là vùng giá trị hoặc địa chỉ của vùng dãy số. Các giá trị không phải là số trong vùng này được bỏ qua. Order là thứ tự sắp xếp dãy số. Nếu order bằng 0 (zero) hoặc bỏ qua, Microsoft Excel xếp thứ bậc theo thứ tự giảm dần của dãy số. Nếu order là bất kì giá trị nào khác 0, Microsoft Excel sắp thứ bậc theo thứ tự tăng dần của dãy số. Ghi chú: •

RANK sắp xếp các số trùng nhau theo cùng bậc. Tuy nhiên, sự hiện diện của các số trùng nhau ảnh hưởng tới thứ bậc của các số khác. Ví dụ, trong chuỗi số nguyên, nếu số 10 xuất hiện hai lần và có thứ bậc là 5, thì 11 sẽ có thứ bậc là 7 (không có số nào có thứ bậc là 6). Ví dụ: Nếu A1:A5 chứa các số 7, 3.5, 3.5, 1, và 2, thì: RANK(A2,A1:A5,1) bằng 3 RANK(A1,A1:A5,1) bằng 5

II.3.

Nhóm hàm đổi kiểu

II.3.1. Hàm TEXT Hàm này đổi các giá trị số thành text với định dạng cho trước. Cú pháp: TEXT(value,format_text)

Trong đó, value là giá trị số, hoặc công thức cho giá trị số hoặc địa chỉ ô chứa giá trị số. Format_text là định dạng số kiểu text trong hộp Category thuộc mục Number trong hộp hội thoại Format Cells (sẽ được trình bày kĩ ở phần sau). Format_text không thể chứa dấu sao (*) và không thể được định dạng kiểu General. Ghi chú:

Trang 47

•

Sử dụng hàm TEXT để chuyển số thành định dạng text, và kết quả không là số nữa. Ví dụ: TEXT(2.715, "$0.00") bằng "$2.72" TEXT("4/15/91", "mmmm dd, yyyy") bằng "April 15, 1991"

II.3.2. Hàm VALUE Hàm chuyển giá trị text đại diện cho một số thành giá trị số. Cú pháp: VALUE(text)

Trong đó, text là các kí tự số nằm trong dấu nháy hoặc địa chỉ ô chứa text muốn chuyển thành số. Text có thể chỉ hằng số, ngày tháng hoặc thời gian được biểu diễn theo định dạng của Microsoft Excel. Nếu text không tuân theo các định dạng này, hàm VALUE trả về giá trị lỗi #VALUE! Ghi chú: •

Nói chung không cần dùng hàm VALUE trong công thức vì Microsoft Excel tự động chuyển text thành số khi cần thiết. Ví dụ: VALUE("$1,000") bằng 1,000 VALUE("16:48:00")-VALUE("12:00:00")

bằng

"16:48:00"-"12:00:00"

bằng 0.2, tương đương với 4 giờ và 48 phút.

II.4.

Nhóm hàm kí tự

II.4.1. Hàm LEFT Hàm này trả lại một số kí tự được chỉ định từ bên trái của chuỗi kí tự. Cú pháp: LEFT(text,num_chars)

Trang 48

Trong đó, text là chuỗi kí tự muốn trích ra một số kí tự bên trái. Num_chars chỉ định số kí tự muốn trích ra. Num_chars phải lớn hơn hoặc bằng 0. Nếu num_chars lớn hơn độ dài của text, hàm LEFT trả lại toàn bộ text. Nếu num_chars bị bỏ qua, nó trả lại một kí tự. Ví dụ: LEFT("Hà Nội", 2) bằng "Hà" Nếu A1 chứa "Huế", thì LEFT(A1) bằng "H" II.4.2. Hàm RIGHT Hàm này trả lại một số kí tự được chỉ định từ bên phải của chuỗi kí tự. Cú pháp: RIGHT(text,num_chars)

Các đối của hàm RIGHT tương tự như hàm LEFT. Ví dụ: RIGHT("Hà Nội", 3) bằng "Nội" RIGHT("Hà Nội") bằng "i" II.4.3. Hàm MID Trả lại số kí tự xác định từ chuỗi kí tự, bắt đầu từ một vị trí xác định. Cú pháp: MID(text,start_num,num_chars)

Trong đó, text là chuỗi kí tự chứa các kí tự muốn trích ra. Start_num là vị trí của kí tự đầu tiên muốn trích ra từ chuỗi kí tự. Kí tự đầu tiên của chuỗi text được đánh số là 1. Nếu start_num lớn hơn độ dài của chuỗi kí tự, hàm MID trả về chuỗi rỗng "". Nếu start_num nhỏ hơn độ dài của chuỗi kí tự nhưng start_num cộng num_chars vượt quá độ dài của chuỗi kí tự, hàm MID trả về các kí tự từ điểm bắt đầu cho tới hết chuỗi kí tự. Nếu start_num nhỏ hơn 1, hàm MID trả lại giá trị lỗi #VALUE!. Num_chars chỉ định số kí tự muốn trích ra. Nếu num_chars có giá trị âm, hàm MID trả về giá trị lỗi #VALUE! Ví dụ: Trang 49

MID("Phan Tùng", 1, 4) bằng "Phan" MID("Phan Tùng", 6, 20) bằng "Tùng" MID("1234", 5, 5) bằng "" (chuỗi rỗng) II.4.4. Hàm UPPER Chuyển text thành chữ hoa (chỉ đúng với tiếng Anh và tiếng Việt không dấu (nếu gõ tiếng Việt có dấu theo kiểu telex)) Cú pháp: UPPER(text)

Trong đó, text là chuỗi kí tự muốn chuyển thành chữ hoa. Text có thể là chuỗi kí tự hoặc địa chỉ ô. Ví dụ: UPPER("total") bằng "TOTAL" Nếu E5 chứa "sum", thì UPPER(E5) bằng "SUM" II.4.5. Hàm LOWER Chuyển text thành chữ thường(chỉ đúng với tiếng Anh và tiếng Việt không dấu (nếu gõ tiếng Việt có dấu theo kiểu telex). Cú pháp: LOWER(text)

Trong đó, text là chuỗi kí tự muốn chuyển thành chữ thường. Hàm LOWER không chuyển các kí tự không phải là text thành chữ thường. Ví dụ: LOWER("E. E. Cummings") bằng "e. e. cummings" LOWER("Apt. 2B") bằng "apt. 2b" II.4.6. Hàm PROPER Biến tất cả các chữ cái đầu từ và tất cả các kí tự ở ngay sau những kí tự đặc biệt thành chữ hoa đồng thời biến tất cả các chữ cái còn lại thành chữ thường. Trang 50

Cú pháp: PROPER(text)

Trong đó, text là chuỗi kí tự nằm trong dấu nháy, công thức cho giá trị text hoặc địa chỉ ô tham chiếu tới giá trị text. Ví dụ: PROPER("this is a TITLE") bằng "This Is A Title" PROPER("2-cent's worth") bằng "2-Cent'S Worth" PROPER("76BudGet") bằng "76Budget" II.4.7. Hàm TRIM Xoá bỏ tất cả những dấu cách thừa trừ một dấu cách giữa các từ. Cú pháp: TRIM(text)

Trong đó, text là chuỗi kí tự hoặc địa chỉ ô tham chiếu tới chuỗi kí tự. Ví dụ: TRIM(" Họ và tên ") bằng "Họ và tên "

II.5.

Nhóm hàm điều kiện

II.5.1. Hàm IF Trả lại một giá trị nếu điều kiện chỉ định được đánh giá là TRUE và trả lại giá trị kia nếu điều kiện nhận giá trị FALSE. Cú pháp: IF(logical_test,value_if_true,value_if_false)

Trong đó, logical_test là bất kì giá trị hoặc biểu thức nào được đánh giá là TRUE hoặc FALSE. Value_if_true là giá trị được hàm trả về nếu logical_test là TRUE. Value_if_true có thể là một công thức. Value_if_false là giá trị được trả về nếu logical_test có giá trị FALSE. Value_if_false có thể là công thức. Ghi chú: Trang 51

•

Có thể lồng 7 hàm IF thay cho các giá trị value_if_true và value_if_false. Xem ví dụ cuối. Ví dụ:

Trong ví dụ sau, nếu giá trị trong ô A10 là 100, khi đó, logical_test là TRUE, và giá trị tổng của vùng B5:B15 được tính. Ngược lại, logical_test là FALSE, hàm sẽ trả về giá trị là chuỗi rỗng (""). IF(A10=100,SUM(B5:B15),"")

Giả sử khi ta cần xếp loại học sinh theo điểm trung bình theo tiêu chuẩn cho trong bảng sau: Nếu điểm TB

thì xếp loại

lớn hơn hoặc bằng 8.5

Giỏi

từ 6.5 đến 8.5

Khá

từ 5.0 đến 6.5

TB

nhỏ hơn 5.0

Kém

Ta có thể dùng các hàm IF lồng nhau như sau: IF(DTB>=8.5,"Giỏi",IF(DTB>=6.5,"Khá",IF(DTB>=5.0,"TB","Kém" )))) Trong ví dụ trên, hàm IF thứ hai là đối số value_if_false của hàm IF thứ nhất. Tương tự, hàm IF thứ ba là đối số value_if_false của hàm IF thứ hai. Ví dụ, nếu đối số logical_test đầu tiên (DTB>=8.5) là TRUE, hàm trả về giá trị "Giỏi". Nếu đối số logical_test thứ nhất nhận giá trị FALSE, hàm IF thứ hai được kiểm tra tiếp, ... II.5.2. SUMIF SUMIF(Vùng 1, “điều kiện”, vùng 2) @ Tính tổng giá trị các ô trong vùng 2 tương ứng với các ô vùng 1 đã thoả mãn điều kiện. Ví dụ :

Dữ liệu trong vùng B4:B7 là 3, 6, 8, 6 Dữ liệu trong vùng C4:C7 là 6, 9, 9, 4 Trang 52

=SUMIF(B4:B7, “>5”, C4:C7)

{kết quả là =22}

Tinh tổng giá trị các ô trong vùng C4:C7 tương ứng với các ô trong vùng B4:B7 nếu giá trị các ô này lớn hơn 5, như vậy các ô có giá trị 6, 8, 6 trong vùng B4:B7 thoả mãn điều kiện, tương ứng với các ô có giá trị 9, 9, 4 trong vùng C4:C7, kết quả tổng bằng 22. II.5.3. COUNTIF COUNTIF(Vùng, “điều kiện”) @ Đếm các ô thoả mãn điều kiện trong vùng. Ví dụ: (Lấy ví dụ của B4:B7 ở ví dụ trên) =COUNTIF(B4:B7, “<5”)

II.6.

{=1}

Nhóm hàm ngày tháng

II.6.1. Date DATE(yy, mm, dd) @ Nhập ngày tháng. (yy,mm,dd) : Nhập hai số của năm tháng ngày. Ví dụ : =DATE(98,09,25)

{Kết quả =9/25/98}

(Tháng 9 ngày 25 năm 98) II.6.2. WEEKDAY WEEKDAY(Tháng-ngày-năm) @ Trả về số thứ trong tuần. Ví dụ : Dữ liệu trong ô B3 là ngày 11/06/98 =WEEKDAY(B3)

{=6}

(Ngày 6/11/98 là thứ 6) II.6.3. DAY DAY(Tháng-ngày-năm) Trang 53

@Trả về số thứ tự của ngày trong tháng. Ví dụ : =DAY(11/25/98)

{=25}

Có thể dùng hàm DAY để tính số ngày, tuần. Ví dụ : Dữ liệu trong ô C3 là ngày 11/25/98 =(DAY(C3)-DAY(B3))

{=19}

II.6.4. MONTH MONTH(Tháng-ngày-năm) @ Trả về số của tháng Ví dụ : =MONTH(B3)

{=11}

II.6.5. YEAR YEAR(Tháng-ngày-năm) @ Trả về số của năm Ví dụ : =YEAR(B3)

II.7.

{=98}

Nhóm hàm logic

II.7.1. Hàm And Trả về giá trị TRUE nếu tất cả các đối số nhận giá trị TRUE; trả về giá trị FALSE nếu một hoặc nhiều đối số nhận giá trị FALSE. Cú pháp: AND(logical1,logical2, ...)

Trong đó, logical1, logical2, ... là 1 tới 30 điều kiện có thể nhận một trong hai giá trị TRUE hoặc FALSE. Nếu đối không nhận giá trị logic, hàm AND trả về giá trị lỗi #VALUE! Ví dụ: AND(TRUE, TRUE) bằng TRUE AND(TRUE, FALSE) bằng FALSE

Trang 54

AND(2+2=4, 2+3=5) bằng TRUE Nếu B1:B3 chứa các giá trị TRUE, FALSE, và TRUE, thì AND(B1:B3) bằng FALSE. Nếu B4 chứa số giữa 1 và 100, thì AND(1
Trong đó, logical, logical2,... là 1 tới 30 điều kiện sẽ nhận một trong hai giá trị TRUE hoặc FALSE. Ví dụ: OR(TRUE) bằng TRUE OR(1+1=1,2+2=5) bằng FALSE Nếu A1:A3 chứa các giá trị TRUE, FALSE, and TRUE, thì OR(A1:A3) bằng TRUE. 1. AND(Các biểu thức điều kiện) @ Trả về giá trị “Và” logic (True hay False) của các biểu thức điều kiện. Ví dụ : =IF(AND(B3>5,E3=”A”), “Đ”, “H”) Nếu B3 lớn hơn 5 và (AND) E4 là A thì cho kết quả là “Đ”, ngược lại sẽ cho kết quả là “H”. Trang 55

II.7.3. Hàm NOT NOT(Các biểu thức điều kiện) @ Trả về giá trị “Không” Logic (True hay False) của các biểu thức điều kiện. Ví dụ : =IF(AND(B3>5, NOT(C3=0)), “Đ”, “H”) Nếu B3 lớn hơn 5 và (AND) C3 không (NOT) bằng 0 thì kết quả là “Đ”, ngược lại sẽ cho kết quả là “H”.

II.8.

Nhóm hàm tìm kiếm

II.8.1. Hàm VLOOKUP VLOOKUP(x, vùng tham chiếu, n, 0) @ Tìm giá trị x ở cột thứ nhất trong vùng tham chiếu và lấy giá trị tương ứng ở cột thứ n.

Trang 56

Ví dụ: (Xem hình trên) Dựa vào bảng mã hàng để điền tên thích hợp vào cột tên hàng của bảng phiếu giao hàng Lập công thức cho ô C13: =VLOOKUP(B13,$A$2:$B$5,2,0)

{=Sắt}

Tìm giá trị bằng giá trị của ô B13 trong cột thứ nhất của vùng A2:B5 và lấy giá trị tương ứng ở cột thứ 2. {kết quả = sắt}. Trong công thức nên đặt giá trị tuyệt đối cho địa chỉ vùng để không bị thay đổi khi sao chép.

II.8.2. Hàm HLOOKUP HLOOKUP(x, vùng tham chiếu, n, 0) @ Tương tự như hàm VLOOKUP nhưng tìm theo dòng thay vì cột. Ví dụ: (Xem hình trên) Dựa vào bảng giá để điền vào cột giá của bảng phiếu giao hàng. Lập công thức cho ô D13: =HLOOKUP(B13,$A$7:$D$8,2,0)

{=100}

Tương tự như hàm VLOOKUP nhưng tìm theo dòng thay vì cột.

II.9.

Cách sử dụng hộp Function Trong trường hợp không nhớ chính xác tên hàm hoặc cú pháp của hàm có thể

dùng cách sau: 1. Đặt trỏ text trên dòng nhập dữ liệu hoặc trong ô, nơi cần đặt hàm. 2. Chọn thực đơn Insert/Function (hoặc kích nút

trên Standard Toolbar), xuất

hiện hộp hội thoại Trang 57

3. Trong hộp Function Category chọn tên nhóm hàm

Tên nhóm ý nghĩa

Most Recently Used: các hàm hay dùng nhất All: tất cả các hàm Financial: các hàm tài chính Date & Time: các hàm kiểu ngày tháng Math & Trig: các hàm toán học Statistical: các hàm thống kê Database: các hàm cơ sở dữ liệu Lookup & Reference: các hàm tìm kiếm Text: các hàm kí tự Logical: các hàm logic Information: các hàm thông tin 4. Kích chuột trong hộp Function Name để chọn tên hàm cần thiết. 5. Sau khi kích vào OK, tên hàm xuất hiện tại vị trí cần dán hàm và xuất hiện hộp hội thoại hướng dẫn vào các đối số của hàm. Ví dụ như nếu chúng ta chọn hàm SUM, sẽ xuất hiện hộp hội thoại sau:

Trang 58

6. Sau khi vào các đối số, kích nút OK.

Trang 59

Câu hỏi và bài tập Thực hành lại tất cả ví dụ đã nêu trong bài, học thuộc cú pháp cũng như ý nghĩa của các hàm đã nêu ở trên. Mở file BD10A.xls và tính thêm các cột Điểm TB, XL và Xếp TT theo các công thức sau (xem mẫu): a) Điểm TB bằng trung bình cộng của (hệ số 1 + 2*hệ số 2 + 3*hệ số 3) b) Xếp loại (XL): Giỏi nếu điểm TB lớn hơn hoặc bằng 8.5. Khá nếu điểm TB trong khoảng từ 6.5 đến 8.5 TB nếu điểm TB trong khoảng từ 5.0 đến 6.5 Kém nếu điểm TB nhỏ hơn 5.0 c) Xếp TT (thứ tự) theo điểm TB từ cao xuống thấp. Gợi ý: Theo bảng mẫu, điểm TB của học sinh Lê Văn Thanh, tại ô I8 được tính theo công thức sau = ROUND(E8+F8+2*G8+3*H8)/7 Xếp loại của học sinh này tại ô J8 được tính thao công thức sau: =IF(I8>=8.5," Giỏi",IF(I8>=6.5,"Khá",IF(I8>=5.0,"TB","Kém"))) Xếp thứ tự của học sinh này tại ô K8 được tính theo công thức sau =RANK(I8,$I$8:$I$17) Tương tự, có thể tính cho các học sinh còn lại. Tuy nhiên, để tính nhanh nhất hãy dùng cách sao chép công thức sẽ được trình bày trong bài 5. A

B

C

D

E

F

G

H

I

J

K

1 Bảng điểm học kì I 2 Môn: Toán 3 Lớp: 10 A 4 5 6

Hệ số Hệ số Hệ số

Trang 60

1 Ngày sinh

Giới M 15' tính

7

STT

8

1

Lê Văn Thanh

9

2

Hoàng Quốc Tuấn

5/4/83 Nam

10

3

Nguyễn Mạnh Hà

11

4

12

Họ và tên

6/17/83 Nam

7

2

3

1T

HK

Điểm TB

XL

Xếp TT

9

7

8

7.7 Khá

6

8 10

9

9

9 Giỏi

2

9/12/83 Nam

5

6

7

6

6.1 TB

8

Lê Thu Nga

4/24/83 Nữ

9

7

8

9

8.4 Khá

4

5

Phạm Hồng Vân

1/12/83 Nữ

8

9

6

8

7.6 Khá

7

13

6

Nguyễn Thu Thuỷ

6/4/83 Nữ

4

5

4

5

4.6 Kém

10

14

7

Hoàng Vân Trang

9/12/83 Nữ

9

7

10

8

8.6 Giỏi

3

15

8

Vũ Tuấn Hùng

6/20/83 Nam

9

8

7

8

7.9 Khá

5

16

9

Vũ Thanh Hằng

9/25/83 Nữ

9

9

8

10

9.1 Giỏi

1

17

10 Lê Hằng Nga

3/15/83 Nữ

6

7

7

5

6 TB

9

Tính tiếp phần tổng kết điểm trong cả lớp ở cuối bảng điểm theo mẫu dưới đây, trong đó: Điểm TB cả lớp được tính theo công thức: =AVERAGE(H8:H17) Điểm TB cao nhất lớp được tính theo công thức: =MAX(H8:H17) Điểm TB thấp nhất lớp được tính theo công thức: =MIN(H8:H17)

Tổng kết Điểm TB cả lớp

7.5

Điểm TB cao nhất lớp

9.1

Điểm TB thấp nhất lớp

4.6

Trang 61

Bài 5: Sao chép và di chuyển dữ liệu I. Sao chép I.1.

Sao chép dữ liệu Khi làm việc với bảng tính, đôi khi chúng ta phải nhập dữ liệu hoặc gõ công thức

giống nhau hoặc gần giống nhau nhiều lần tại các vị trí khác nhau ở những thời điểm khác nhau. Khi đó, thay cho việc phải gő nhiều lần, chúng ta hãy dùng chức năng copy của Excel để tiết kiệm thời gian làm việc. Sau khi sao chép, kết quả của bản sao sẽ khác nhau phụ thuộc vào kiểu dữ liệu gốc trước khi sao chép: • Nếu dữ liệu gốc là hằng hoặc là công thức chỉ chứa hàng và địa chỉ tuyệt đối, kết quả sao chép sẽ không thay đổi so với bản gốc. Ví dụ: nếu dữ liệu gốc có dạng là hằng chữ như: "Bảng Tổng kết điểm", hoặc là một con số như "450000" hoặc là công thức dạng "=$A$5*12" thì dù sao chép tới đâu, kết quả bản sao vẫn giống hệt dữ liệu gốc. • Nếu dữ liệu gốc là hằng chứa địa chỉ tương đối thě kết quả sao chép sẽ khác so với bản gốc. Nó sẽ phụ thuộc vào vị trí hiện thời của ô chứa kết quả sao chép. Ví dụ, ô C12 chứa công thức "=SUM(C6:C11)". Khi ta tiến hành sao chép sang ô bên phải, thì tại ô D12 Excel sẽ đổi lại thành công thức "=SUM(D6:D11)". Nếu sao chép sang ô G18, thì công thức lúc này sẽ là "SUM(G12:G17)". Sau đây chúng ta sẽ học một số phương pháp sao chép dữ liệu khác nhau.

I.2.

Dùng chuột Ta có thể dùng chuột để sao chép nhanh dữ liệu trong phạm vi nhỏ trên màn hình.

I.2.1. Dùng nút trái chuột: 1. Chọn vùng dữ liệu gốc cần sao chép. 2. Đưa trỏ chuột tới đường biên của vùng chọn sao cho có dạng ó .

Trang 62

3. Giữ Ctrl và rê vùng lựa chọn tới đích. Ngay sau khi giữ Ctrl, sẽ xuất hiện dấu + bên cạnh con trỏ chuột. Khi bắt đầu rê chuột, một đường viền mờ sẽ bao quanh vùng dữ liệu đang được di chuyển và dưới dòng trạng thái xuất hiện thông báo "Drag to copy cell contents, use ALT key to switch sheets", nghĩa là Excel hướng dẫn chúng ta, nếu muốn sao chép sang sheet khác thì phải giữ phím Alt trong khi kích chọn tên sheet. Chú ý: • Nếu trong khi rê chuột để copy vào vùng đích mà vùng này đang chứa dữ liệu thì kết quả sao chép sẽ xoá dữ liệu đang tồn tại và thế vào vị trí đó. Nếu muốn sao và chèn kết quả vào vùng dữ liệu đă có sẵn thì phải giữ Ctrl + Shift trong khi rê chuột. Sau khi thả chuột, Excel sẽ dồn dữ liệu đang tồn tại trong vùng đích sang phải hoặc xuống dưới tuỳ theo ta thả đường viền mờ vào vị trí mép dọc hay mép ngang của các ô trong vùng đích. I.2.2. Dùng nút phải chuột 1. Chọn vùng dữ liệu gốc cần sao chép. 2. Đưa trỏ chuột tới đường biên của vùng chọn sao cho có dạng ở. 3. Hãy bấm giữ nút phải và rê chuột tới vùng đích. Trong khi rê chuột, tại dòng trạng thái xuất hiện thông báo " Drag cells; then release to see paste options - Rê chuột; sau đó thả chuột và xem các tuỳ chọn khi dán kết quả". Sau khi thả chuột, xuất hiện một thực đơn ngắn. Chọn một trong các phương án sau: •

Copy Here: dán kết quả sao chép tại đây.

•

Copy as Value Only: chỉ sao chép giá trị của các ô chứa dữ liệu gốc.

•

Copy as Format Only: chỉ sao chép định dạng giá trị của các ô chứa dữ liệu gốc.

•

Link Here: liên kết với các ô chứa dữ liệu gốc.

•

Shift Down and Copy: chèn vào các ô chứa dữ liệu ở vùng đích và đẩy chúng xuống dưới.

Trang 63

•

Shift Right and Copy: chèn vào các ô chứa dữ liệu ở vùng đích và đẩy chúng sang phải.

•

I.3.

Cancel: huỷ việc sao chép.

Dùng bộ đệm (clipboard) Clipboard là bộ đệm, sử dụng một phần bộ nhớ của máy tính để cất dữ tạm thời

các kết quả sao chép. Sau khi dùng lệnh dán (Paste), Windows sẽ sao chép dữ liệu từ clipboard vào vị trí hiện thời trong bảng tính. Các bước thực hiện: 1. Chọn vùng dữ liệu muốn sao chép 2. Làm một trong các cách sau: • Mở thực đơn Edit, chọn lệnh Copy. • Nhấn tổ hợp phím Ctrl + C. • Kích nút phải chuột và kích Copy trên thực đơn ngắn. • Kích nút Copy trên Standard toolbar. Excel xuất hiện một đường viền nhấp nháy quanh vùng dữ liệu được chọn. 3. Chọn vị trí đích chứa kết quả sao chép: • Nếu sao chép một vùng dữ liệu chỉ cần chọn một ô của vùng đích. • Nếu sao chép vào các vùng khác nhau thì phải chọn chúng đồng thời. • Nếu sao chép tới bảng tính, hoặc file khác thì mở chúng ra và chọn vị trí đích. 4. Để dán dữ liệu vào vùng đích hãy làm một trong các cách sau:

I.4.

Sao chép bình thường:

• Nhấn phím Enter (chỉ dán được một lần). • Mở thực đơn Edit, chọn lệnh Paste. • Nhấn tổ hợp phím Ctrl + V. • Kích nút phải chuột và kích Paste trên thực đơn ngắn. • Kích nút Paste

trên Standard toolbar. Trang 64

Ghi chú: • Phải đảm bảo rằng vùng đích là vùng trắng. Nếu không, dữ liệu cũ trong vùng đích sẽ bị xoá mất. • Để dán kết quả sao chép và chèn dữ liệu ở vị trí mới hãy làm như sau: + Kích nút phải tại vị trí mới và kích vào lệnh Insert Copied cells (hoặc dùng lệnh Copied cells của thực đơn Insert). Xuất hiện hộp hội thoại:

+ Chọn Shift cells right sẽ đẩy các ô hiện thời sang phải. Chọn Shift cells down sẽ đẩy các ô hiện thời xuống dưới. • Nếu muốn xoá đường viền nhấp nháy xung quanh khối dữ liệu, hăy nhấn ESC.

I.5.

Dán đặc biệt 1. Chọn đích dán kết quả sao chép. 2.Mở thực đơn Edit, chọn lệnh Paste Special (hoặc chọn Paste Special trong

thực đơn ngắn khi kích nút phải chuột). Xuất hiện hộp hội thoại

3. Trong hộp Paste Special, chọn một hoặc vài tuỳ chọn sau:

Trang 65

• Trong hộp Paste, chọn một trong các tuỳ chọn: All- sao toàn bộ hằng, công thức, kiểu định dạng sang vùng đích. Formulas - chỉ sao chép công thức sang vùng đích. Values - chỉ sao chép giá trị sang vùng đích. Formats - chỉ sao chép định dạng sang vùng đích. Comments - chỉ sao chép chú thích sang vùng đích. Validation - chỉ sao chép những thông số trong hộp hội thoại Validation sang đích. All except border - sao chép tất cả trừ đường viền sang vùng đích. • Trong hộp Operation chọn một trong các tuỳ chọn: None - sao chép và thay thế giá trị của ô nguồn vào ô đích. Add - sao chép và cộng giá trị ô nguồn và ô đích. Subtract - sao chép và trừ giá trị của ô nguồn và ô đích. Multiple - sao chép và nhân giá trị ô nguồn và ô đích. Divide - sao chép và chia giá trị của ô nguồn và ô đích. • Skip blanks được chọn nếu không muốn các ô trống trong vùng dữ liệu gốc xoá những giá trị ở vị trí tương ứng trong vùng đích. • Transpose được chọn sẽ sao chép và hoán vị trí cột/hàng trong vùng dữ liệu gốc thành vị trí hàng/cột trong vùng dữ liệu đích. • Nút Paste Link được dùng khi muốn kết quả sao chép liên kết với dữ liệu vùng gốc. Mọi sự thay đổi của dữ liệu gốc sẽ tự động dẫn đến mọi thay đổi dữ liệu trong vùng đích.

I.6.

Sao chép sang vùng lân cận nhờ AutoFill (tự động điền dữ liệu) Phương pháp tự động điền dữ liệu (AutoFill) cho phép điền dữ liệu ở các ô kề bên

các ô đă chọn theo một quy luật nhất định. Các thao tác của phương pháp này đă được trình bày, khi giới thiệu các thủ thuật nhập dữ liệu nhanh bằng cách dùng Fillhandle để mở rộng dãy dữ liệu. Chú ý rằng, khi sao chép công thức, chỉ cần chọn một ô chứa công thức.

II. Di chuyển dữ liệu Trong khi làm việc với bảng tính, đôi khi chúng ta có nhu cầu di chuyển các dữ liệu tới vị trí mới thay cho việc phải xoá chúng ở vị trĩ cũ và gõ lại ở vị trí mới. Vị trí mới của dữ liệu có thể là một ô hoặc một vùng ô khác trên cùng sheet, có thể là sheet khác trong cùng file hoặc có thể là file khác của Excel hoặc Winword, ... Trang 66

Sau đây giới thiệu một số phương pháp di chuyển dữ liệu.

II.1.

Dùng chuột Ta có thể dùng chuột để di chuyển nhanh dữ liệu trong phạm vi nhỏ trên màn

hình. II.1.1. Dùng nút trái chuột 1. Chọn vùng dữ liệu gốc cần di chuyển. 2. Đưa trỏ chuột tới đường biên của vùng chọn sao cho có dạng ở. 3. Rê vùng lựa chọn tới đích. Khi bắt đầu rê chuột, một đường viền mờ sẽ bao quanh vùng dữ liệu đang được di chuyển và dưới dòng trạng thái xuất hiện thông báo "Drag to move cell contents, use ALT key to switch sheets", nghĩa là Excel hướng dẫn chúng ta, nếu muốn di chuyển sang sheet khác thì phải giữ phím Alt trong khi kích chọn tên sheet. Chú ý: • Khi rê chuột để di chuyển dữ liệu vào vùng đích mà vùng này đang chứa dữ liệu thì sẽ xuất hiện thông báo

• Nếu kích OK, dữ liệu mới sẽ xoá dữ liệu đang tồn tại và thế vào vị trí đó. Nếu chọn Cancel, sẽ huỷ bỏ việc di chuyển dữ liệu. • Nếu muốn chèn kết quả di chuyển vào vùng dữ liệu đã có sẵn thì phải giữ Shift trong khi rê chuột. Khi bắt đầu rê chuột, một đường viền mờ sẽ bao quanh vùng dữ liệu đang được di chuyển và dưới dòng trạng thái xuất hiện thông báo "Drag to Cut and Insert cell contents, use ALT key to switch sheets". Sau khi thả chuột, Excel sẽ dồn dữ liệu đang tồn tại trong vùng đích sang phải hoặc xuống dưới tuỳ theo ta thả đường viền mờ vào vị trí mép dọc hay mép ngang của các ô trong vùng đích.

Trang 67

II.1.2. Dùng nút phải chuột 1. Chọn vùng dữ liệu gốc cần di chuyển. 2. Đưa trỏ chuột tới đường biên của vùng chọn sao cho có dạng ở. 3. Hãy bấm giữ nút phải và rê chuột tới vùng đích. Trong khi rê chuột, tại dòng trạng thái xuất hiện thông báo " Drag cells; then release to see paste options - Rê chuột; sau đó thả chuột và xem các tuỳ chọn khi dán kết quả". 4. Sau khi thả tay, xuất hiện một thực đơn ngắn. Chọn một trong các phương án sau: •

Move Here: dán kết quả di chuyển tại đây.

•

Shift Down and Move: chèn vào các ô chứa dữ liệu ở vùng đích và đẩy chúng xuống dưới.

•

Shift Right and Move: chèn vào các ô chứa dữ liệu ở vùng đích và đẩy chúng sang phải.

•

II.2.

Cancel: huỷ việc di chuyển.

Dùng bộ đệm (clipboard) Các bước thực hiện: 1. Chọn vùng dữ liệu muốn di chuyển. 2. Làm một trong các cách sau:

• Mở thực đơn Edit, chọn lệnh Move. • Nhấn tổ hợp phím Ctrl + X. • Kích nút phải chuột và kích Move trên thực đơn ngắn. • Kích nút Cut

trên Standard toolbar.

Excel xuất hiện một đường viền nhấp nháy quanh vùng dữ liệu được chọn. 3. Chọn vị trí đích chứa kết quả di chuyển: • Nếu di chuyển một vùng dữ liệu chỉ cần chọn một ô của vùng đích. • Nếu di chuyển vào các vùng khác nhau thì phải chọn chúng đồng thời.

Trang 68

• Nếu di chuyển tới bảng tính, hoặc file khác thì mở chúng ra và chọn vị trí đích. Để dán dữ liệu vào vùng đích hãy làm một trong các cách sau:

II.3.

Di chuyển bình thường:

• Nhấn phím Enter (chỉ dán được một lần). • Mở thực đơn Edit, chọn lệnh Paste. • Nhấn tổ hợp phím Ctrl + V. • Kích nút phải chuột và kích Paste trên thực đơn ngắn. • Kích nút Paste

trên Standard toolbar.

Ghi chú: • Phải đảm bảo rằng vùng đích là vùng trắng. Nếu không, dữ liệu cũ trong vùng đích sẽ bị xoá mất. • Để dán kết quả di chuyển và chèn dữ liệu ở vị trí mới hãy làm như sau: + Kích nút phải tại vị trí mới và kích vào lệnh Insert Cut cells (hoặc dùng lệnh Cut cells của thực đơn Insert). Xuất hiện hộp hội thoại:

+ Chọn Shift cells right sẽ đẩy các ô hiện thời sang phải. Chọn Shift cells down sẽ đẩy các ô hiện thời xuống dưới.

III. Xoá dữ liệu: (Ô không bị xoá) Chọn khối dữ liệu muốn xoá và thực hiện một trong những cách sau: -

Right click và chọn Clear contents

-

Chọn Edit\ Clear\ Contents.

-

Nhấn phím Delete.

Trang 69

Sau khi xoá, nếu muốn lấy lại dữ liệu có thể sử dụng tính năng Undo bằng một trong 3 cách sau: Chọn Edit\ Undo, nhấn Ctrl+Z, hoặc click biểu tượng Undo trên thanh công cụ.

IV. Sao chép định dạng, chèn ghi chú IV.1. Sao chép định dạng: (Chỉ sao chép định dạng của ô, không sao chép nội dung ô) Nếu muốn các ô khác ccó định dạng giống như một ô nào đó ta có thể thực hiện: -

Chọn ô có định dạng muốn sao chép

-

Click biểu tượng Format\ Painter

-

Click vào ô hoặc click & drug lên khối muốn sao chép.

IV.2. Chèn ghi chú (Comment): Mỗi ô đều có thể chèn ghi chú, khi đưa con trỏ chuột đến góc bên phải của ô, ghi chú sẽ hiển thị. Để chèn chi chú cần thực hiện: -

Right click tại ô muốn chèn ghi chú và chọn Insert Comment (hoặc sử dụng trình đơn Insert\ Comment).

-

Chọn Format\ Comment để định dạng kiểu chữ (font) sau đó nhập ghi chú bình thường.

IV.3. Chỉnh sửa và xoá ghi chú: -

Chỉnh sửa: Right click tại ô muốn chỉnh sửa và chọn Edit Comment

-

Xoá :Right click tại ô muốn xoá và chọn Delete Comment.

Trang 70

Câu hỏi và bài tập 1. Thực hành lại tất cả các thao tác sao chép và di chuyển dữ liệu đã nêu trong bài. 2. Nêu sự khác nhau giữa việc sao chép tất cả dữ liệu trong một bảng tính sang bảng tính khác và việc sao chép chính bảng tính đó thành hai bản. 3. Mở file BD10B.xls, tính toán tương. Trong khi tính toán có sử dụng việc sao chép công thức sao cho kết quả có dạng sau: A B 1 Bảng điểm học kỳ I 2 Môn: Toán 3 Lớp: 10 B 4 5 6 7 STT 8 9 10 11 12 13 14 15 16 17

1 2 3 4 5 6 7 8 9 10

Họ và tên Nguyễn Vân Anh Vũ Hoà Bình Hoàng Mạnh Thắng Nguyễn Hoàng Hải Phạm Hồng Vân Hoàng Thu Trang Phạm Anh Tuấn Trần Thanh Hải Nguyễn Hoàng Hà Nguyễn Thanh Tùng Tổng kết Điểm TB cả lớp Điểm TB cao nhất lớp Điểm TB thấp nhất lớp

C

D

E

F

G

H

I

J

K

Hệ số 1 Hệ số 2 Hệ số 3 Ngày Giới sinh tính 6/7/83 Nữ 6/7/83 Nữ 7/6/83 Nam 8/1/83 Nam 7/3/83 Nữ 6/5/83 Nữ 5/18/83 Nam 5/8/83 Nam 6/7/83 Nam 6/15/83 Nam

M 15' 6 4 8 7 9 9 5 9 9 8

8 5 9 8 9 9 7 8 9 7

1T

HK 7 5 10 8 8 8 6 10 8 8

7 4 9 8 9 10 7 10 10 8

Điểm XL TB 7 Khá 4.4 Kém 9.1 Giỏi 7.9 Khá 8.7 Giỏi 9.1 Giỏi 6.4 TB 9.6 Giỏi 9.1 Giỏi 7.9 Khá

Xếp TT

7.9 9.6 4.4

Trang 71

8 10 2 6 5 2 9 1 2 6

Bài 6: Định dạng cho dữ liệu Trình bày bảng tính là một công việc rất cần thiết trong quá trình làm việc với EXCEL. EXCEL lưu trữ các dữ liệu trong các ô của bảng tính cùng với các thông số định dạng của các ô đó. Việc định dạng cho các dữ liệu trong bảng tính có thể tiến hành bằng các lệnh Cells trong thực đơn Format.

I. Định dạng cho dữ liệu kiểu số Cùng một giá trị nhưng số có nhiều cách thể hiện khác nhau, để định dạng số có thể thực hiện như sau: Chọn khối muốn định dạng.

Chọn Format\Cell (Ctrl+1), sau đ ó chọn mục Number trong hộp hội thoại Format Cells và chọn tiếp các thông số định dạng sau. Trong hộp Category chọn các kiểu dữ liệu cần trình bày và chọn tiếp các thông số khác tuỳ thuộc vào loại dữ liệu đã được chọn trong hộp này. Hãy vừa chọn vừa quan sát hộp Sample để xem mẫu định dạng. Cụ thể: • •

General: định dạng chung. Với kiểu định dạng này, text gõ vào sẽ được dóng hàng bên trái và dữ liệu kiểu số sau khi gõ vào bảng tính sẽ được dóng hàng bên phải ô. Number: dùng để định dạng các số thông thường. Chọn tiếp các thông số sau: Decimal places - số chữ số sau dấu chấm thập phân, Use 1000 Separators(,) Trang 72

•

•

• • • • • •

dùng dấu phẩy để phân cách hàng nghìn, Negative numbers - chọn cách định dạng số âm. Trong hộp Sample hiện mẫu định dạng. Currency - dùng để định dạng cho các giá trị tiền tệ. Chọn tiếp các thông số sau: Decimal places - số chữ số sau dấu chấm thập phân, Symbol - chọn kí hiệu tiền tệ của các nước khác nhau (Mĩ, Anh, Đức,...) Negative numbers - chọn cách định dạng số âm. Trong hộp Sample hiện mẫu định dạng. Accounting: kí tự tiền tệ và dấu chấm thập phân của các số được định dạng theo kiểu này sẽ được dóng thẳng hàng. Chọn các thông số sau: Decimal places - số chữ số sau dấu chấm thập phân, Symbol - chọn kí hiệu tiền tệ của các nước khác nhau (Mĩ, Anh, Đức,... ). Sample hiện mẫu định dạng. Date: định dạng ngày theo mẫu được chọn trong hộp Type Time: định dạng giờ theo mẫu được trong hộp Type Percentage: định dạng theo kiểu phần trăm. Số chữ số sau dấu chấm thập phân được chọn trong hộp Decimal Places. Text: định dạng số theo kiểu Text. Special: định dạng theo mã vùng và số điện thoại Custom: tuỳ biến theo khuôn dạng được gõ trong hộp Type. Nút Delete được dùng để xoá khuôn dạng không cần dùng đến.

II. Định dạng ngày tháng

Khi nhập ngày tháng cần nhập tháng trước ngày sau, có thể chỉ cần nhập ngày, tháng và năm Excel sẽ lấy theo tháng năm hiện hành của hệ thống. Cùng một ngày nhưng có thể chọn cach hiển thị khác nhau. Trong hộp thoại Format Cell, ở khung Category ở bảng Number chọn mục Date, và chọn cách hiển thị bên khung Type. Trang 73

Ngày tháng hiển thị theo dạng tổng quát m/d/yy (tháng/ngày/năm), nếu không vừa ý với các dạng có sẵn, có thể tạo ra một dạng hiển thị riêng bằng cách chọn mục Custom trong khung Category, và gõ dạng cần thiết vào khung Type, ví dụ: dd/mm/yyyy (2 số ngày/2 số tháng/4 số năm) như 25/11/1998.

III. Định dạng vị trí Chọn mục Alignment trong hộp Format Cells để dóng hàng cho dữ liệu. Dữ liệu nhập vào bảng tính có vị trí mặc định tuỳ theo từng kiểu dữ liệu. Tuy nhiên có thể định dạng lại theo các bước sau:

Chọn khối dữ liệu. Click chọn các biểu tượng

định dạng vị trí trên thanh công

cụ hoặc chọn Format\ Cell (Ctrl+1), trong hộp thoại Format\Cells chọn bảng Alignment: Trang 74

Khung Horizontal : Click mở khng này để lựa chọn cách hiển thị hàng ngang. + General: Tổng quát + Left (Indent): Trái + Right: Phải + Center: Giữa + Justify: Đều hai bên + Fill: Vừa bằng ô, vùng + Center Across seletion: Giữa vùng Khung Vertical : Click mở khung nay để lựa chọn cách hiển thị hàng dọc. + Top: Phía trên + Center: Giữa + Botton: Phía dưới + Justify: Đều hai bên Khung Orientation : Kiểu chữ đứng hoặc nghiêng (Chỉnh độ nghiêng trong khung Degrees).

Text Control: Các điều khiển khác. Chọn Wrap Text nếu muốn Text xuống dòng trong các ô khi dài hơn độ rộng hiện thời của ô. Chọn Shrink to fit nếu muốn Text vừa khít trong các ô khi các ô đó bị co hẹp. Chọn Merge cells nếu muốn trộn các ô trên cùng hàng và cùng cột. Sau khi trộn, chỉ có dữ liệu nằm ở ô trái trên trong vùng đã được chọn hiện trong ô được trộn. Do đó, muốn ô trộn chứa tất cả dữ liệu trong vùng đã chọn thì trước khi trộn phải sao chép toàn bộ dữ liệu vào ô trái trên.

III.1. Định dạng kiểu chữ cho dữ liệu Để định dạng kiểu chữ cho dữ liệu có thể thực hiện theo các bước sau: Trang 75

1. Chọn khối muốn định dạng. 2. Chọn Format\Cell (Ctrl+1) Chọn các định dạng trong bảng Font trong hộp hội thoại Format Cells.

-

-

Font, Font style : Kiểu chữ. Size : Kích cỡ. Underline: Các kiểu chữ gạch chân được mô tả tại vị trí Underline (None bình thường, Single - gạch dưới đơn, Double - gạch dưới đúp, Single Accounting - gạch đơn kiểu tài chính đến cuối mép phải của ô, Double Accounting - gạch đúp kiểu tài chính). Color: Màu. Preview: Xem trước.

Trong khung Effects có 3 mục lựa chọn: -

Strikethrought : Gạch ngang. Superscript : Số mũ (x2). Subscript : Chỉ số (H2O).

* Trên cùng một ô để định dạng kiểu chữ khác nhau cho các thành phần dữ liệu, chọn khối thành phần (click & drag) rồi định dạng bình thường. * Lựa chọn Normal Font được dùng nếu muốn lấy lại kiểu trình bày ngầm định.

III.2. Tạo đường viền ô Mục Border trong hộp hội thoại Format Cells cho phép tạo khung (đường viền) cho khối ô hiện thời. Trang 76

Các đường kẻ dòng và cột trong Excel sẽ không thấy khi in ra giấy, nếu cần có khung kẻ phải thực hiện kẻ khung như sau: -

Chọn ô, vùng muốn kẻ khung. Chọn Format\ Cell và chọn bảng Border.

Chọn các dạng kẻ khung trong hộp thoại: -

None : không kẻ khung. Outline : kẻ vòng chung quanh khối hoặc ô. Inside : kẻ các đường bên trong khối chọn. Style : chọn dạng đường kẻ. Color : Chọn màu.

Có thể chọn từng đường kẻ trong khung Border, nếu khối chọn đã có kẻ khung, dạng khung sẽ hiển thị ở khung Text, Click vào các đường kẻ để chọn hoặc xoá.

III.3. Tô màu Mục Parttern trong hộp hội thoại Format Cells để tô màu cho các ô được chọn.Trong đó: • • •

Pattern: mẫu nền. Color: màu tô. Trong đó No Color: hủy tô màu nền. Sample: hiện mẫu tô theo các thông số vừa được chọn.

Trang 77

Trang 78

Câu hỏi và bài tập 1. Thực hành lại tất cả các thao tác định dạng dữ liệu đã nêu trongbài. 2. Mở file BD10A.xls và trình bày lại theo mẫu sau.

3. . Tự trình bày lại dữ liệu trong file BD10B.xls

Trang 79

Bài 7: Cơ sở dữ liệu trong excel và cách đặt tên cho vùng dữ liệu I. Khái niệm Trong Microsoft Excel, ta có thể dễ dàng sử dụng một danh sách như một cơ sở dữ liệu (database). Khi chúng ta thực hiện các công việc như tìm kiếm, sắp xếp hoặc tổng hợp dữ liệu theo nhóm (subtotal), Microsoft Excel tự động sử dụng những phần tử sau đây của danh sách để tổ chức dữ liệu. - Các cột trong danh sách là các trường (fields) trong cơ sở dữ liệu. - Các nhãn của cột trong danh sách là các tên trường (Field name) trong cơ sở dữ liệu. - Mỗi hàng trong danh sách là một bản ghi (record) của cơ sở dữ liệu Ví dụ: Danh sách sau tương ứng với một cơ sở dữ liệu gồm các trường có tên là Họ đệm, Tên, Chức vụ, Lớp. Mỗi hàng, kể từ hàng thứ hai trở đi tương ứng với một bản ghi của cơ sở dữ liệu. Họ đệm

Tên

Chức vụ

Lớp

Vũ Thanh

Thủy

Lớp phó

10A

Trần Hồng

Hà

Lớp phó

10A

Nguyễn Hoàng

Hà

Lớp trưởng 10A

Vũ Văn

Thanh

Lớp phó

10B

Phạm Tuấn

Hùng

Lớp phó

10B

Phạm Hoàng

Hải

Lớp trưởng 10B

Vũ Hồng

Thủy

Lớp phó

10C

Trần Thanh

Hà

Lớp phó

10C

Vũ Hồng

Thắng

Lớp trưởng 10C

Hoàng Minh

Thanh

Lớp phó

10D

Đặng Mạnh

Hùng

Lớp phó

10D

Phạm Thanh

Tùng

Lớp trưởng 10D

Trang 80

II. Cách tạo một danh sách trên bảng tính Để dễ dàng quản lí và phân tích dữ liệu trong một danh sách của Microsoft Excel, khi nhập dữ liệuvào danh sách chú ý tuân theo các hướng dẫn sau đây.

II.1.

Kích thước và vị trí của danh sách - Tránh tạo nhiều danh sách trên một bảng tính (worksheet). Một số công việc, ví

dụ như lọc dữ liệu chẳng hạn, tại một thời điểm chỉ có thể được sử dụng trên một danh sách. - Để ít nhất một cột trống (blank column) và một dòng trống (blank row) giữa danh sách và các dữ liệu khác trên bảng tính. Khi đó, Microsoft Excel có thể dễ dàng kiểm tra và chọn danh sách khi chúng ta sắp xếp, lọc hoặc chèn subtotal tự động. - Tránh đặt dòng trống và cột trống trong danh sách. - Tránh đặt dữ liệu tới hạn (critical data) ở bên trái hoặc bên phải danh sách, những dữ liệu này có thể bị ẩn đi khi chúng ta lọc danh sách.

II.2.

Các nhãn của cột (column labels) - Tạo các nhãn cột ở dòng đầu tiên của danh sách. Microsoft Excel sử dụng các

nhãn này để tạo báo cáo, để tìm và tổ chức dữ liệu. - Sử dụng phông chữ, cách dóng hàng, màu nền, đường viền ... khác với các định dạng áp dụng cho dữ liệu trong danh sách. - Khi muốn phân biệt nhãn và dữ liệu, hãy sử dụng các kiểu đường viền ô - không dùng các hàng trống (blank rows) hoặc các đường đứt đoạn (dashed lines) - để chèn các đường thẳng dưới nhãn.

II.3.

Nội dung của hàng và cột - Tạo một danh sách sao cho tất các các hàng chứa các phần tử có các thuộc tính

giống nhau. Ví dụ như danh sách học sinh, bảng điểm, bảng giá, ... - Không chèn những khoảng trống thừa ở đầu từng ô (nói cách khác, không nhấn phím spacebar ở đầu ô). Các khoảng trống thừa này sẽ ảnh hưởng tới việc sắp xếp dữ liệu và tìm kiếm dữ liệu.

Trang 81

- Không dùng hàng trống (blank row) để phân tách các nhãn cột với dòng đầu tiên của dữ liệu.

III. Cách đặt tên cho vùng dữ liệu và truy cập tới vùng đã đặt tên Trong Microsoft Excel, có thể đặt tên cho một ô hoặc cho vùng ô để dễ dàng nhớ và đọc khi tạo công thức hoặc khi cần truy nhập tới ô, vùng ô nào đó. Nếu ta có các nhãn hàng và cột trong một bảng tính, ta có thể sử dụng trực tiếp chúng trong công thức. Ví dụ = A4 + B4. Ta cũng có thể tự đặt tên cho một ô hoặc một vùng ô. Cách đặt tên cho ô hoặc vùng được tuân theo các quy tắc sau: - Kí tự đầu tiên của tên phải là một chữ cái hoặc dấu trừ chìm (_). Các kí tự còn lại có thể là chữ cái, chữ số, dấu chấm và các dấu trừ chìm. Ví dụ, các tên Dslop, Bangdiem.10A, Tuyen_sinh là hợp lệ. - Các tên không được trùng với địa chỉ ô, ví dụ như Z$100 hoặc R1C1. - Không cho phép dùng dấu cách (spaces). - Tên có độ dài tối đa 255 kí tự, có thể chứa chữ hoa và chữ thường. Microsoft Excel không phânbiệt chữ hoa và chữ thường trong tên. Ví dụ, nếu ta đã tạo tên DSCBLOP và sau đó tạo tên dscblop trong cùng một bảng tính thì tên thứ hai sẽ thay thế tên thứ nhất.

III.1. Cách đặt tên cho một ô hoặc một vùng ô 1. Chọn một ô hoặc một vùng ô liên tục hoặc rời rạc. Ví dụ, chọn toàn bộ các ô chứa danh sách cán bộ lớp như ví dụ đã nêu trên. 2. Kích vào hộp Name tại bên trái của thanh công thức.

3. Gõ tên cho ô hoặc vùng ô. Ví dụ, gõ DSCBLOP. 4. Nhấn ENTER.

Trang 82

III.2. Đặt tên cho các ô bằng cách sử dụng các nhãn hàng và cột có sẵn 1. Chọn vùng muốn đặt tên, chọn cả nhãn cột (hàng đầu của danh sách) và nhãn hàng (cột trái ngoài cùng của danh sách).

2. Thực hiện Insert / Name / Create. Xuất hiện hộp hội thoại. 3. Trong hộp Create names in, đánh dấu vị trí chứa các nhãn. Thông thường ta hay chọn Top row, Left column. Các tên được tạo theo cách này chỉ ảnh hưởng tới các ô chứa giá trị chứ không ảnh hưởng tới các nhãn hàng và nhãn cột.

III.3. Truy cập tới vùng đã đặt tên Để truy cập tới vùng đã đặt tên, làm một trong hai cách sau: - Kích vào mũi tên cạnh hộp tên (name box) ở bên trái thanh công thức và kích chọn tên cần truy cập. - Nhấn phím F5, xuất hiên hộp hội thoại Goto, kích vào tên vùng cần truy cập và kích OK. Ví dụ: Để truy cập tới vùng Dscblop, gõ F5, chọn tên vùng và kích OK. Toàn bộ danh sách sẽ được bao trong hộp đen.

Trang 83

Câu hỏi và bài tập 1. Thế nào là vùng cơ sở dữ liệu trong Excel. 2. Mở file mới, tạo bảng danh sách cán bộ lớp theo mẫu trong phần lí thuyết. Ghi vào đĩa với tên QLHS.XLS. 3. Đổi tên sheet hiện thời thành DSCB. Thực hành theo ví dụ đã trình bày trong phần lí thuyết. 4. Kích hoạt một sheet mới, tạo một bảng mới có dạng như sau

Tuyển sinh Sbd

Họ và tên

Xếp loại TN

Toán

Văn

Ngoại ngữ

Tổng điểm

Trúng tuyển

...

Làm tiếp các việc sau: a. Nhập vào danh sách tối thiểu 10 thí sinh. Yêu cầu nhập các cột sau: Sbd (số báo danh), Họ và tên, Xếp loại TN (xếp loại tốt nghiệp), Toán, Văn, Ngoại ngữ. b. Cột Tổng điểm được tính theo công thức: Tổng điểm = Toán + Văn + Ngoại ngữ + Ưu tiên Biết rằng điểm ưu tiên = 1 nếu xếp loại tốt nghiệp là giỏi. Các loại khác không được cộng điểm. c. Cột Trúng tuyển được điền là Có nếu Tổng điểm lớn hơn hoặc bằng 21. Ngược lại, điền là Không. d. Đổi tên sheet hiện thời là TUYENSINH và ghi kết quả vào đĩa. 5. Đặt tên cho danh sách vừa tạo là DSTS. 6. Truy cập tới vùng DSCBLOP. 7. Truy cập tới vùng DSTS. 8. Ghi các kết quả trên vào đĩa và đóng file.

Trang 84

Bài 8: Tìm kiếm và sắp xếp dữ liệu I. Tìm kiếm và thay thế I.1.

Tìm kiếm: Nếu có nhiều dữ liệu trong bảng tính và muốn nhanh chóng tìm đến một ô dữ liệu

nào đó, có thể sử dụng chức năng này. -

Chọn Edit\ Find (Ctrl+P).

-

Nhập các ký tự muốn tìm trong khung Find what và lựa chọn cách tìm kiếm:

+ Match case : Tìm kiếm có phân biệt chữ thường và chữ in. + Find entire cells only : Chỉ tìm dữ liệu đứng riêng trong ô. + Search : Chọn cách tìm theo cột (By Columns) hoặc theo dòng (By Rows). + Look in : Chọn loại dữ liệu cần tìm. -

I.2.

Click nút Find next để lần lượt tìm.

Thay thế: Nếu muốn tìm một từ nào đó trong bảng tính để thay thế bằng một dữ liệu khác

một cách nhanh chóng, có thể sử dụng chức năng này. -

Chọn Edit\ Replace (Ctrl+H).

Trang 85

-

Nhập các ký tự cần tìm trong khung Find what và những ký tự muốn thay ở khung Replace with, các lựa chọn khác cũng giống như ở phần tìm kiếm.

-

Click nút Find Next để lần lượt tìm kiếm. Khi tìm thấy click nút Replace để thay thế, (Replace All tự động thay thế tất cả những vị trí tìm thấy).

II. Sắp xếp dữ liệu Khi nhập dữ liệu, Microsoft Excel sử dụng trật tự sắp xếp xác định để xếp đặt các dữ liệu theo giá trị của chúng. Khi ta sắp xếp một chuỗi kí tự, Microsoft Excel sắp xếp từ trái sang phải theo từng kí tự. Ví dụ như, nếu một ô chứa chuỗi kí tự "A100," ô này sẽ được sắp sau ô chứa chuỗi kí tự "A1" và trước ô chứa chuỗi kí tự "A11." Khi sắp xếp giảm dần, Microsoft Excel sử dụng trật tự sau: - Các số được sắp xếp từ giá trị âm nhỏ nhất tới giá trị dương lớn nhất. - Chuỗi kí tự được sắp xếp theo trật tự sau: 0 1 2 3 4 5 6 7 8 9 ' - (space) ! " # $ % &( )*,. /:;? @[\]^_` {|}~ +<= >ABCDEF GHIJK LMNO PQRSTU VWXYZ. - Trong các giá trị logic, FALSE được sắp xếp trước TRUE. - Tất cả các giá trị lỗi bằng nhau. - Các ô trống luôn luôn được sắp xếp cuối cùng. Ta có thể sắp xếp lại các hàng hoặc cột bằng chức năng Sort (sắp xếp) của Excel. Khi ta sắp xếp, Microsoft Excel sắp xếp lại các hàng, các cột hoặc các ô riêng biệt theo thứ tự sắp xếp do ta chỉ định: tăng dần (ascending) - từ 1 tới 9, từ A tới Z hoặc giảm dần (descending) - từ 9 tới 1, từ Z tới A.

II.1.

Sắp xếp dữ liệu tăng hoặc giảm dần theo nội dung của một cột 1. Kích vào một ô trong cột chứa dữ liệu muốn sắp xếp.

Trang 86

2. Để sắp xếp theo thứ tự tăng dần, kích nút Sort Ascending Để sắp xếp theo thứ tự tăng dần, kích nút Sort Descending

. .

Ví dụ, chúng ta có danh sách các cán bộ lớp như sau Họ đệm

Tên

Chức vụ

Lớp

Nguyễn Hoàng

Hà

Lớp trưởng

10A

Phạm Hoàng

Hải

Lớp trưởng

10B

Vũ Hồng

Thắng

Lớp trưởng

10C

Phạm Thanh

Tùng

Lớp trưởng

10D

Vũ Thanh

Thủy

Lớp phó

10A

Trần Hồng

Hà

Lớp phó

10A

Vũ Văn

Thanh

Lớp phó

10B

Phạm Tuấn

Hùng

Lớp phó

10B

Vũ Hồng

Thủy

Lớp phó

10C

Trần Thanh

Hà

Lớp phó

10C

Hoàng Minh

Thanh

Lớp phó

10D

Đặng Mạnh

Hùng

Lớp phó

10D

Để sắp xếp danh sách theo thứ tự giá trị tăng dần của cột Lớp, kích chuột vào một ô bất kì trong cột này và kích vào nút Ascending trên thanh công cụ.

II.2.

Sắp xếp dữ liệu theo nội dung của hai hoặc nhiều cột

1. Kích một ô

trong

danh

sách

muốn sắp xếp. 2. Thực hiện

Data / Sort, xuất

hiện hộp hội thoại Sort.

Trang 87

3. Trong các hộp Sort by và Then by kích chọn tên các cột muốn sắp xếp. 4. Chọn các tuỳ chọn khác nếu cần thiết và kích OK. Trong My list has có hai tuỳ chọn: Header row - trong vùng dữ liệu vừa chọn có dòng tiêu đề, No header row - trong vùng dữ liệu vừa chọn không có dòng tiêu đề. Thông thường, nên để trạng thái chọn ngầm định của máy. Lặp lại các bước 2- 4 nếu cần thiết.

Ghi chú: •

- Nếu cột ta chỉ định trong hộp Sort by các giá trị trùng nhau, ta có thể sắp xếp các giá trị này bằng cách chỉ định cột khác trong hộp Then by đầu tiên. Nếu các giá trị trong cột thứ hai được sắp xếp này trùng nhau, ta có thể chỉ định cột thứ ba trong hộp Then by thứ hai.

•

- Nếu ta cần sắp xếp nhiều hơn ba cột, hãy sắp xếp những cột ít quan trọng nhất trước. Ví dụ như, nếu chúng ta muốn sắp xếp danh sách cán bộ theo các cột Lớp, Chức vụ, Họ đệm và Tên thì phải sắp xếp hai lần. Đầu tiên, chọn cột Tên trong hộp Sort by và kích nút OK. Lần thứ hai, kích Lớp trong hộp Sort by, kích Chức vụ trong hộp Then by thứ nhất, chọn Descending và kích Họ đệm trong hộp Then by thứ hai và kích OK. Kết quả có dạng như sau: Họ đệm

II.3.

Tên

Chức vụ

Lớp

Nguyễn Hoàng Hà

Lớp trưởng 10A

Trần Hồng

Hà

Lớp phó

10A

Vũ Thanh

Thủy

Lớp phó

10A

Phạm Hoàng

Hải

Lớp trưởng 10B

Phạm Tuấn

Hùng

Lớp phó

10B

Vũ Văn

Thanh

Lớp phó

10B

Vũ Hồng

Thắng

Lớp trưởng 10C

Trần Thanh

Hà

Lớp phó

10C

Vũ Hồng

Thủy

Lớp phó

10C

Phạm Thanh

Tùng

Lớp trưởng 10D

Đặng Mạnh

Hùng

Lớp phó

10D

Hoàng Minh

Thanh

Lớp phó

10D

Sắp xếp xếp dữ liệu theo nội dung của hàng 1. Kích một ô trên danh sách dữ liệu muốn sắp xếp.

Trang 88

2. Thực hiện Data / Sort. 3. Kích nút Options, xuất hiện hộp hội thoại Sort Options.

4. Dưới Orientation, kích Sort left to right, và sau đó kích OK. 5. Trong các hộp Sort by và Then by kích các hàng muốn sắp xếp.

II.4.

Sắp xếp theo tháng, ngày hoặc theo thứ tự do người dùng tạo

II.4.1. Tạo danh sách theo thứ tự do người dùng tạo (custom list) Nếu muốn sắp xếp dữ liệu theo một thứ tự khác thứ tự ngầm định của máy, chúng ta phải tự tạo một danh sách theo thứ tự do mình đặt ra. Ví dụ như, nếu sắp xếp cột Xếp loại trong một bảng điểm tổng kết theo thứ tự tăng dần ngầm định của Excel, thì kết quả sẽ đươc sắp theo thứ tự sau: Gioi, Kem, Kha, TB. Tuy nhiên, để hợp lí hơn, chúng ta phải tự tạo danh sách theo thứ tự: Gioi, Kha, TB, Kem và sắp xếp bảng điểm theo thứ tự đó. Hãy làm như sau: 1. Nếu ta đã nhập vào bảng tính danh sách các phần tử theo thứ tự riêng của mình thì hãy chọn danh sách đó. Ví dụ, hãy gõ vào các ô A1:A4 các giá trị sau Gioi, Kha, TB, Kem và chọn bốn ô này. 2. Thực hiện Tools / Options, kích mục Custom Lists.

Trang 89

3. Để sử dụng danh sách đã chọn, kích nút Import. Nếu chưa thực hiện bước 1, hãy gõ danh sách mới bằng cách kích New list trên hộp Custom lists và sau đó gõ các phần tử của danh sách trong hộp List entries. Các phần tử cách nhau bởi dấu phảy hoặc dấu ENTER. Kích nút Add.

Ghi chú: •

Một danh sách tự tạo có thể chứa chuỗi kí tự hoặc chuỗi kí tự xen lẫn các con số. Nếu muốn tạo danh sách chỉ chứa các con số, đầu tiên chọn đủ số ô trống chứa các phần tử của danh sách. Thực hiện Format / Cells, và kích mục Number. Áp dụng kiểu định dạng Text cho các ô trống và sau đó gõ danh sách các số trong các ô vừa định dạng.

II.4.2. Sắp xếp theo tháng, ngày hoặc theo thứ tự của danh sách tự tạo 1. Chọn một ô trong vùng hoặc toàn bộ vùng dữ liệu muốn sắp xếp. 2. Thực hiện Data / Sort. 3. Kích Options. 4. Dưới First key sort order, kích danh sách tự tạo và kích OK. 5. Kích chọn bất kì lựa chọn khác nếu cần thiết.

Ghi chú:

Trang 90

•

Trật tự sắp xếp theo thứ tự của danh sách tự tạo chỉ áp dụng cho cột được chỉ định trong hộp Sort by. Để sắp xếp nhiều cột theo thứ tự của danh sách tự tạo hãy sắp xếp từng cột riêng biệt. Ví dụ, để sắp xếp theo các cột A và B, theo thứ tự này, đầu tiên sắp xếp theo cột B, và sau đó chỉ định thứ tự của danh sách tự tạo bằng cách sử dụng hộp hội thoại Sort Options. Tiếp theo, sắp xếp theo danh sách của cột A.

Trang 91

Câu hỏi và bài tập 1. Nêu các thao tác sắp xếp dữ liệu theo một cột, theo nhiều cột và theo thứ tự của danh sách tự tạo. 2. Mở file QLHS.XLS, kích hoạt sheet DSCB và thực hiện những thao tác đã trình bày trong phần lí thuyết. 3. Kích hoạt sheet TUYENSINH và làm các công việc sau: a. Sắp xếp lại theo thứ tự tăng dần của cột Trúng tuyển. b. Sao chép danh sách những người trúng tuyển sang vị trí mới và sắp xếp lại theo thứ tự giảm dần của Tổng điểm. c. Đặt tên vùng chứa kết quả trúng tuyển là Trungtuyen1. d. Chuyển về vùng DSTS. e. Chuyển sang xem kết quả tại vùng Trungtuyen1. 4. Ghi các thay đổi vào đĩa và đóng file. 5. Kích hoạt một sheet mới, tạo một bảng mới có dạng như sau

Báo cáo Tuyển sinh Sbd

Họ và tên

Ngày sinh

Nơi sinh

Miền

Tổng điểm

Trúng tuyển

...

Làm tiếp các việc sau: a. Nhập vào danh sách tối thiểu 10 thí sinh. Yêu cầu nhập các cột sau: Sbd (số báo danh), Họ và tên, Ngày sinh, Nơi sinh, Miền (Bắc, Trung hoặc Nam) và Tổng điểm (từ 0 đến 30).

Trang 92

b. Cột Trúng tuyển được điền theo yêu cầu sau: - Có nếu thí sinh ở miền Bắc và miền Nam và Tổng điểm lớn hơn hoặc bằng 21 điểm hoặc nếu thí sinh ở miền Trung và có Tổng điểm lớn hơn hoặc bằng 20 điểm. - Không cho các trường hợp còn lại. Gợi ý: dùng công thức sau =IF (OR (AND (OR (Miền="Bắc", E2="Nam"), Tổng điểm>=21), AND (Miền="Trung", Tổng điểm>=20)), "Có", "Không") c. Sắp xếp lại danh sách theo thứ tự các miền Bắc, Trung, Nam. Nếu giá trị các miền trùng nhau thì sắp xếp theo giá trị cột Trúng tuyển. Nếu cùng giá trị cột Trúng tuyển, hãy sắp xếp theo giá trị của cột Tổng điểm. e. Đổi tên sheet hiện thời thành BCTTUYEN và ghi kết quả vào đĩa.

Trang 93

Bài 9: Tạo biểu đồ I. Các phần tử cơ bản của biểu đồ Khi muốn so sánh các dữ liệu có mối quan hệ với nhau chúng ta nên dùng biểu diễn mối tương quan này qua các biểu đồ để có cái nhìn trực quan và sinh động hơn. Để vẽ biểu đồ bắt buộc phải có danh sách dữ liệu trên bảng tính. Sau đây là một số phần tử cơ bản của biểu đồ: - Chuỗi dữ liệu (data series): là các hàng hoặc các cột chứa các dữ liệu cần so sánh với nhau. - Kiểu đồ thị (chart type): các hình thức biểu diễn đồ thị khác nhau theo dạng cột (Column), đường (line) hoặc diện tích (area), ... - Nhãn chung của biểu đồ (Chart Labels): là tên chung của cả biểu đồ. - Nhãn trục (axis labels): các nhãn trên trục biểu đồ. - Chú giải (Legend): dùng để phân biệt giữa các chuỗi dữ liệu khác nhau. Ngoài ra còn một số phần tử khác nữa sẽ được trình bày tiếp trong các phần sau.

II. Tạo biểu đồ ngầm định theo một bước Kiểu biểu đồ ngầm định của Microsoft Microsoft Excel là biểu đồ cột. Có hai cách tạo biểu đồ ngầm định. Cách 1: Chọn dữ liệu muốn biểu diễn thành đồ thị. Nhấn F11. Dữ liệu sẽ được biểu diễn dưới dạng biểu đồ cột và nằm trong một sheet riêng biệt có tên ngầm định là Chart n (n=1, 2, 3, ...). Ví dụ: Giả sử ta có danh sách dữ liệu biểu diễn tỉ lệ học sinh giỏi, khá, trung bình, kém như sau: Lớp

10A

10B

10C

10D

Xếp loại

Trang 94

30% 50% 20% 0%

Giỏi Khá TB Kém

20% 55% 25% 0%

24% 60% 14% 2%

19% 58% 21% 2%

Để so sánh tỉ lệ xếp loại hoặc sinh giữa các lớp, chúng ta chọn vùng dữ liệu trên và nhấn phím F11. Kết quả có dạng như sau:

Cách 2: •

Chọn dữ

liệu

biểu diễn

thành

muốn đồ

thị. •

Kích nút

Default

Chart

.

Đồ thị tạo

được

nhúng vào

ra

bảng tính

chứa

dữ

liệu. Trong trường hợp không nhìn thấy nút này, hãy làm như sau: 1. Thực hiện Tools / Customize. Xuất hiện hộp hội thoại,

kích

chuột

vào

mục

Commands. 2. Trong hộp Categories kích chọn Charting. 3. Dùng chuột kéo lệnh Default Chart thả vào một vị trí bất kì trên thanh công cụ chuẩn (Standard toolbar). Ví dụ: sau khi đã cài được nút Default Chart trên thanh công cụ chuẩn, chọn vùng dữ liệu nói trên và kích vào nút này, kết quả có dạng:

Trang 95

III. Cách tạo đồ thị nhờ Chart Wizarrd Chart Wizard là công cụ hướng dẫn chúng ta tạo đồ thị từng bước qua các hộp hội thoại. Các bước tạo đồ thị được tiến hành như sau: 1. Chọn các ô chứa các dữ liệu muốn xuất hiện trên biểu đồ. Các dữ liệu này có thể nằm cạnh nhau hoặc nằm cách nhau. Nếu muốn các nhãn cột và nhãn hàng xuất hiện trong biểu đồ hãy chọn cả chúng. Ví dụ, để so sánh tỉ lệ xếp loại hoặc sinh của các lớp 10A, và 10D, chúng ta hãy chọn các cột thứ nhất, thứ hai và thứ năm trong bảng dữ liệu nói trên bằng cách giữ phím CRTL và chọn các cột nói trên. Xem hình vẽ sau:

2. Kích nút Chart Wizard

. Xuất hiện hộp hội thoại Chart Wizard - Step 1 of 4 -

Chart Types. Hộp hội thoại này cho phép chúng ta chọn kiểu đồ thị (Chart type) và một kiểu đồ thị con (Chart sub -type) của nó. Muốn xem trước mẫu đồ thị sẽ được tạo, hãy kích vào nút Press and hold to view sample. Trang 96

Trong ví dụ này, chúng ta sẽ chọn kiểu Line và một kiểu biểu đồ con của nó như hình vẽ sau:

3. Kích vào nút Next, xuất hiện tiếp hộp hội thoại Chart Wizard - Step 2 of 4 - Chart Source Data sau:

Trong hộp

hội thoại

này cho phép chọn lại vùng dữ liệu trong hộp Data range (nếu cần thiết). Nếu chọn Rows, chuỗi dữ liệu sẽ được so sánh theo hàng. Nếu chọn Columns, chuỗi dữ liệu sẽ được so sánh theo cột. Trong ví dụ của chúng ta, hãy giữ nguyên các thông số như hộp hội thoại.

Trang 97

4. Kích vào nút Next, xuất hiện tiếp hộp hội thoại Chart Wizard - Step 3 of 4 - Chart Options. Trong hộp hội thoại này cho phép chọn các tuỳ chọn khác nhau cho biểu đồ. Trong bài này, chúng ta chỉ quan tâm tới các thông số trong mục Titles. - Chart title: cho phép gõ nhãn chung của biểu đồ. - Categery (X) axis: cho phép gõ nhãn của trục x. - Value (Y) axis: cho phép gõ nhãn của trục y. Xem kết quả ở hình mẫu bên phải của đồ thị. Trong ví dụ của chúng ta, hãy gõ theo hướng dẫn trên hộp hội thoại (có thể gõ tiếng Việt có dấu trong hộp này, nhưng chúng ta sẽ không nhìn thấy dấu tiếng Việt trong hộp hội thoại. Tiếng Việt chỉ hiển thị đúng ở ngoài đồ thị sau khi chúng ta định dạng lại font tiếng Việt cho chúng).

5. Kích vào nút Next, xuất hiện tiếp hộp hội thoại Chart Wizard - Step 4 of 4 - Chart Location. Trong hộp hội thoại này cho phép chọn vị trí đặt biểu đồ. - As new sheet: đặt biểu đồ ở sheet riêng với tên ngầm định do Excel đặt (Chart1, Chart2, ...) hoặc chúng ta tự gõ lại tên mới trong hộp này. - As object in: đặt biểu đồ trong sheet hiện thời, hoặc một sheet khác do chúng ta chọn bằng cách kích chuột vào mũi tên bên phải hộp này và chọn tên sheet đích. Trong ví dụ của chúng ta, giữ nguyên các thông số trong hộp hội thoại.

Trang 98

6. Kích nút Finish. Kết quả có dạng như sau:

Trang 99

Câu hỏi và bài tập 1. Nêu các phần tử cơ bản của một biểu đồ. 2. Thực tập lại ví dụ đã nêu trong phần lí tuyết. 3. Lập bảng tổng kết số học sinh nữ và nam trong từng khổi 10, 11, 12. Tính tỉ lệ số học sinh nữ và nam của từng khối so với tổng số học sinh trong toàn trường. Vẽ biểu đồ biểu diễn sự so sánh này. 4. Lập bảng tổng kết số học sinh giỏi và khá từng khổi 10, 11, 12. Tính tỉ lệ học sinh giỏi, khá của từng khối so với số học sinh giỏi, khá trong toàn trường. Vẽ biểu đồ biểu diễn sự so sánh này.

Trang 100

Bài 10: Định dạng trang và In ấn I. Định dạng trang I.1.

Định dạng trang Công việc chuẩn bị in ấn bảng tính bao gồm các phần việc được thực hiện từ lệnh

File/Page Setup. Trong hộp hội thoại Page Setup ta có thể thực hiện các công việc sau: Vào các thông số in bao gồm 4 loại thông số: 1. Page: Các thông số trang giấy in. 2. Margins: Các thông số lề trang in. 3. Header/Footer: Tiêu đề trang in (nếu cần thiết). 4. Sheet: Các thông số về dữ liệu cần in trên bảng tính.

Nút Print Preview dùng để kiểm tra trước khi in chính thức ra giấy in. Nút Print dùng để ra lệnh in. Nút Option dùng để đặt lại các thông số máy in. Trang 101

Nút OK đóng cửa sổ Page Setup. Các thông số in sẽ được mô tả cụ thể dưới đây.

I.1.1. Page • Orientation: hướng in. • Scaling: tỷ lệ in. Có hai lựa chọn sau: o o

• • •

Adjust to: tỷ lệ in so với dạng chuẩn. Fit to: dãn/nén vùng in trong số trang được chỉ ra bởi các thông số về chiều rộng (wide) và chiều dài (tall).

Paper Size: kích thước giấy in. Print Quality: chất lượng in. First Page Number: số trang bắt đầu cần đánh số.

I.1.2. Margins

• • •

Các thông số lề Top, Bottom, Left, Right: lề trên, dưới, trái, phải. From Edge/Header, Footer: khoảng cách từ mép trang giấy đến tiêu đề trang. Center on Page: in vào giữa trang theo chiều: Horizontally (ngang), Vertically (dọc).

I.1.3. Header/Footer Các thông số về tiêu đề được đưa vào tự động hoặc bằng tay khi nhấn các nút Customize Header hoặc Customize Footer. •

Left section, Center section, Right section: vị trí trình bày tiêu đề trong trang. Trang 102

•

Các nút phía trên dùng để: trình bày font chữ, chèn số trang, tổng số trang, ngày giờ hệ thống.

I.1.4. Sheet

• •

Print Area: chọn vùng dữ liệu cần in. Print Titles: tiêu đề hàng, cột của dữ liệu, trong đó: o o

•

Row to repeat at top: tiêu đề hàng. Các hàng được in ra ở mọi trang. Column to repeat at left: tiêu đề cột. Các cột được in ra ở mọi trang.

Print: lựa chọn kiểu in o o o o o

Row and Column Heading: in tiêu đề hàng, cột của bảng tính. Gridlines: in ra lưới của các ô bảng tính. Comment: in chú thích. Draft Quality: in nhanh (bỏ qua format). Black and white: in đen trắng.

Trang 103

•

Page Order: thứ tự in. o o

Down, then over: in từ trên xuống dưới trước, sau đó mới sang ngang. Over, then down: in sang ngang trước, sau đó mới xuống dưới.

II. In Công việc in trong EXCEL được thực hiện bằng lệnh File/Print. Hộp hội thoại Print xuất hiện.

• • •

Printer: tên của máy in, kiểu máy in, ... Nút Properties: chọn các thông số của máy in như khổ giấy, hướng giấy, chế độ phân giải màu, chất lượng in, ... Page Range: lựa chọn vùng in. o o

•

Print What: o o o

• • •

All: in toàn bộ các trang. Pages From...To: in hạn chế các trang từ...đến trang ...

Selection: chỉ in vùng được chọn. Selected Sheet: in các sheet đang làm việc. Entire Workbook: in toàn bộ Workbook.

Copies: số lượng bản in. Print to file: được chọn nếu muốn văn bản ra file. Nút Preview: dùng để xem tổng quát bảng tính trước khi in chính thức ra giấy in. Sau khi đã chọn xong các thông số in kích chuột tại nút OK hoặc nhấn Enter để

bắt đầu công việc in. Trang 104

Câu hỏi và bài tập 1. Thực hành lại tất cả các thao tác chuẩn bị in ấn trong bài và xem kết quả ở chế độ Print Preview.

2. Mở file BD10A.xls, mở sheets cài đặt cỡ giấy in là A4, font chữ chính trong bảng là Times New Roman và cỡ chữ nhỏ nhất là 12. Xem kết quả ở chế độ Print Preview. Nếu dữ liệu nằm trên hai trang giấy, hãy đặt lại chế độ in sao cho chúng nằm khít trên một trang in. 3. Làm tương tự với file BD10B.xls. 4. Hãy đặt chế độ in sao cho sau khi in ra, các bảng điểm của lớp 10A và 10B đều có tên sheet và ngày tháng in nằm ở góc trái phần tiêu đề trên, dòng chữ "Trường PTTH ABC" nằm ở góc phải tiêu đề trên và số trang nằm ở giữa phần tiêu đề dưới theo mẫu sau:

Trang 105

Ôn tập I. Một số thao tác cơ bản với file dữ liệu Khởi động Excel : Start /Programs /Microft Excel. Thoát khỏi Excel: File / Exit hoặc Alt + F4. Mở file mới: File /New Ghi file vào đĩa: File /Save Ghi file hiện thời vào đĩa với tên khác: File / Save As. Gọi file cũ: File /Open.

II. Nhập, sửa, xoá dữ liệu II.1.

Nhập hằng:

•

Chọn ô trống.

•

Gõ dữ liệu

•

Nhấn Enter.

II.2.

Nhập công thức:

•

Chọn ô trống

•

Gõ dấu bằng (=)

•

Gõ biểu thức

•

Nhấn Enter.

II.3.

Sửa toàn bộ dữ liệu

•

Chọn ô cần sửa

•

Gõ dữ liệu mới

•

Nhấn Enter.

II.4. •

Sửa một phần dữ liệu: Chọn ô cần sửa Trang 106

•

Nhấn F2 hoặc kích chuột lên dòng nhập dữ liệu

•

Chuyển con trỏ text tới vị trí cần sửa, xoá bớt hoặc chèn thêm dữ liệu.

•

Nhấn Enter.

II.5.

Xoá dữ liệu

•

Chọn ô cần xoá dữ liệu

•

Nhấn phim Delete.

III. Các thao tác với hàng, cột, ô III.1. Chọn hàng, cột ô •

Kích chuột vào tiêu đề hàng nếu chọn cả hàng, vào tiêu đề cột nếu chọn cả cột, vào ô nếu chọn ô.

•

Chọn các hàng, cột, ô liền nhau: chọn phần tử đầu khối. Giữ Shift và kích chuột vào phần tử đối diện ở cuối khối.

•

Chọn các hàng, cột, ô rời rạc: chọn một phần tử. Giữ Ctrl và kích chọn các phần tử còn lại.

III.2. Chèn thêm hàng, cột, ô •

Chọn số hàng, cột, ô cần chèn.

•

Kích nút phải chuột, chọn Insert hoặc dùng Insert => Rows (hoặc Columns hoặc Cells).

III.3. Huỷ thêm hàng, cột, ô •

Chọn số hàng, cột, ô cần huỷ.

•

Kích nút phải chuột, chọn Delete hoặc dùng Edit => Delete.

III.4. Thay đổi độ rộng cột •

Chọn các cột cần điều chỉnh độ rộng

•

Đặt trỏ chuột ở mép phải vùng tiêu đề và rê chuột hoặc dùng thực đơn Format / Column / Width.

III.5. Thay đổi độ cao hàng •

Chọn các hàng cần điều chỉnh độ cao

Trang 107

•

Đặt trỏ chuột ở mép dưới vùng tiêu đề và rê chuột hoặc dùng thực đơn Format / Row / Hight.

IV. Các thao tác với một bảng tính IV.1. Kích hoạt bảng tính •

Kích chuột vào tên sheet trên danh sách các sheet.

•

Dùng các nút chuyển

•

Kích nút phải tại vùng các nút chuyển và kích vào tên sheet cần thiết.

,

,

,

IV.2. Các thao tác với sheet •

Chọn sheet

•

Kích nút phải chuột vào tên sheet và chọn các lệnh sau: Rename: đổi tên bảng tính Insert: chèn thêm bảng tính Delete: xoá bảng tính Move or copy: di chuyển hoặc sao chép bảng tính.

V. Một số hàm thông dụng V.1. Các hàm thống kê Average(number1, number2, ...): tính giá trị trung bình của các số. Max(number1, number2, ...): tính giá trị lớn nhất của các số. Min(number1, number2, ...): tính giá trị nhỏ nhất của các số. Sum(number1, number2, ...): tính tổng các số. Count(number1, number2, ...): đếm các ô chứa số. Rank(number, ref, order): trả lại số thứ tự trong danh sách số.

V.2. Các hàm đổi kiểu Text(value, format_text): đổi các giá trị số thành text. Value(Text): chuyển các text đại diện cho giá trị số thành số.

Trang 108

V.3. Các hàm kí tự Left(text,num_chars): trả lại một số kí tự được chỉ định từ bên trái chuỗi kí tự. Right(text,num_chars): trả lại một số kí tự được chỉ định từ bên phải chuỗi kí tự. Mid(text,start_num,num_chars): trả lại một số kí tự từ một vị trí xác định. Upper(text): chuyển text thành chữ hoa. Lower(text): chuyển text thành chữ thường. Proper(text): chuyển chữ cái đầu từ và các kí tự ngay sau kí tự đặc biệt thành thành chữ hoa. Trim(text): xoá bỏ tất cả các kí tự trống thừa.

V.4. Các hàm toán học SQRT(number): hàm căn bậc 2. ABS(number): lấy giá trị tuyệt đối. Round(number,num_digit): làm tròn số.

V.5. Các hàm logic If(logical_test,value_if_true, value_if_ false): hàm trả lại giá trị đúng nếu biểu thức logic nhận giá trị TRUE và ngược lại. And(logical1, logical2, ...): hàm nhận giá trị TRUE nếu tất cả các đối số nhận giá trị TRUE. OR(logical1,logical2, ...): hàm nhận giá trị FALSE nếu tất cả các đối số nhận giá trị FALSE.

V.6. Cách sử dụng hộp Function Insert /Function.

VI. Sao chép và di chuyển dữ liệu VI.1. Sao chép dữ liệu •

Dùng nút trái chuột: chọn dữ liệu gốc. Giữ Ctrl và rê chuột tới đích.

•

Dùng nút phải chuột: chọn dữ liệu gốc. Dùng nút phải chuột rê tới đích và chọn một trong các lựa chọn sau trong thực đơn ngắn: Copy Here - dán kết quả sao chép tại đây, Copy as Value Only- chỉ sao chép giá trị của dữ liệu gốc, Copy as Format Only- chỉ sao chép định dạng của dữ liệu gốc, Link Here - liên kết với các Trang 109

ô chứa dữ liệu gốc, Shift Down and Copy - chèn vào các ô chứa dữ liệu ở vùng đích và đẩy chúng xuống dưới, Shift Right and Copy - chèn vào các ô chứa dữ liệu ở vùng đích và đẩy chúng sang phải, Cancel - huỷ việc sao chép. •

Dùng bộ đệm + Chọn dữ liệu gốc. + Edit / Copy + Chọn một ô vùng đích + Edit / Paste hoặc Edit / Paste Special.

VI.2. Di chuyểndữ liệu •

Dùng nút trái chuột: chọn dữ liệu gốc. Rê chuột tới đích.

•

Dùng nút phải chuột: chọn dữ liệu gốc. Dùng nút phải chuột rê tới đích và chọn một trong các lựa chọn sau trong thực đơn ngắn: Move Here - dán kết quả di chuyển tại đây, Shift Down and Move - chèn vào các ô chứa dữ liệu ở vùng đích và đẩy chúng xuống dưới, Shift Right and Move - chèn vào các ô chứa dữ liệu ở vùng đích và đẩy chúng sang phải, Cancel - huỷ việc di chuyển.

•

Dùng bộ đệm + Chọn dữ liệu gốc. + Edit / Cut + Chọn một ô vùng đích + Edit / Paste.

VII.Cơ sở dữ liệu Cơ sở dữ liệu : là một vùng hình chữ nhật gồm các hàng và các cột. Mỗi cột tương ứng với một trường. Hàng nhãn cột là tên các trường. Từ hàng thứ hai trở đi tương ứng với một bản ghi.

Trang 110

VIII.Sắp xếp dữ liệu - Kích một ô trong vùng dữ liệu - Thực hiện Data / Sort.

IX. Biểu đồ - Chọn vùng dữ liệu - Nhấn F11 hoặc kích nút Chart Default hoặc làm theo hướng dẫn của Chart Wizard.

X. Định dạng cho dữ liệu và in ấn Định dạng cho dữ liệu: Format / Cells. Cài đặt trang giấy in: Format / Page Setup. Xem trước khi in: File / Print Preview. In: File / Print.

Trang 111

Câu hỏi và bài tập 1. Hãy phân biệt sự khác nhau giữ việc xoá một file, xoá một bảng tính và xoá dữ liệu. 2. Hãy phân biệt sự khác nhau giữa việc đặt tên file và đặt tên cho bảng tính. 3. Mở file BD10A.xls, sao chép bảng tính HKI-10A và đổi tên bản sao thành HKII10A. Hãy tự vào điểm học kì II cho các bạn lớp 10A, trong đó số điểm hệ số 1 của các bạn không giống nhau. Chẳng hạn như, bạn thứ nhất chỉ có một điểm hệ số 1, bạn thứ 4 lại có 3 điểm hệ số 1, ... Sau đó tính cột điểm TB cho các bạn chỉ bằng một công thức và hoàn thành mốt các cột còn lại. Trình bày bảng điểm cho đẹp và in ra giấy hoặc xem ở chế độ Print Preview. 4. Mở file mới, ghi vào đĩa với tên là QLSACH.XLS 5. Tạo bảng dữ liệu theo mẫu sau: Stt

Ngày

Tên sách

Nhà xb

Loại

Đơn giá

Số Thành tiền lượng

1

2/2/99

Doremon –1

Kim đồng

Truyện Tranh 6500

5

2

2/2/99

Tập tô lớp 3,t1

Giáo dục

Sgk

1200

10

3

2/15/99

Tiếng Việt lớp 3,t1

Giáo dục

Sgk

3500

15

4

2/15/99

Doremon –1

Kim đồng

Truyện Tranh 6500

25

5

2/15/99

Tập tô lớp 3,t1

Giáo dục

Sgk

1200

20

6

2/15/99

Tiếng Việt lớp 4,t1

Giáo dục

Sgk

3700

9

7

3/7/99

Doremon –2

Kim đồng

Truyện Tranh 6500

30

8

3/20/99

Tiếng Việt lớp 3,t1

Giáo dục

Sgk

3500

25

9

3/20/99

Tập tô lớp 3,t1

Giáo dục

Sgk

1200

8

Tiếng Việt lớp 5,t1

Giáo dục

Sgk

4000

20

10 3/20/99

Yêu cầu: - Nhập dữ liệu cho các cột Stt, Ngày, Tên sách, Nhà Xuất bản (Giáo dục, Kim đồng, ...), Loại (SGK, Truyên Tranh, Tiểu thuyết,...), Đơn giá và Số lượng. Nhập ít nhất 10 bản ghi theo mẫu. - Cột Thành tiền được tính theo công thức sau:

Trang 112

Thành tiền = đơn giá * số lượng nếu số lượng nhỏ hơn 10. Thành tiền = đơn giá * số lượng *(1 - 5%) nếu số lượng từ 10 đến 20. Thành tiền = đơn giá * sốlượng*(1-10%) nếu số lượnglớn hơn hoặc bằng 20. 6. Đặt tên cho sheet hiện thời là BANHANG 7. Sao chép sang sheet mới, sắp xếp theo giá trị tăng dần của tên nhà xuất bản. Nếu giá trị của nhà xuất bản trùng nhau thì sắp xếp theo giá trị giảm dần của cột Thành tiền. Đặt tên vùng dữ liệu vừa sao chép sang là Bansao1. Đặt tên sheet hiện thời là TONGHOP. 8. Dùng chức năng Sutotal để tính tổng thành tiền của từng nhà xuất bản và tổng chung. Sao chép kết quả sang vùng khác của Sheet TONGHOP và đặt tên cho vùng kết quả là TH.NHAXB. Kết quả có dạng như sau:

Nhà xb

Thành tiền

Giáo dục Total

276525

Kim đồng Total

354250

Grand Total

630775

Trang 113

MỤC LỤC Bài 1: Làm quen với Excel............................................ ...........................1 I. Tổng quan về Excel........................................................ ........................1 I.1. Giới thiệu Excel............................................................................... ..1 I.2. Đặc điểm của Excel 2000........................................................ ..........1 II. Khởi động và thoát khỏi Excel....................................................... ......2 II.1. Khởi động Excel:.................................................... .........................2 II.2. Thoát khỏi Excel:............................................................. ................2 III. Màn hình làm việc của Excel.................................................... ..........3 III.1. Màn hình Excel........................................................................ .......3 III.2. Định dạng màn hình Excel.............................. ...............................4 IV. Giới thiệu bảng tính của Excel............................................... .............5 IV.1. Workbook ...................................................................................... ..5 IV.2. Worksheet .................................................................. .....................5 Câu hỏi và bài tập...................................................................... ...............7 Bài 2: Nhập dữ liệu vào bảng tính và các thao tác với tệp................................................................... ............................8 I. Các kiểu dữ liệu trong Excel ............................................................ .....8 I.1. Kiểu hằng: .................................................................................. ......8 I.1.1. Text: ................................................................................. ..........8 I.1.2. Number .......................................................................... ............9 I.2. Kiểu công thức ....................................................... ..........................9 I.3. Kiểu logic và các lỗi .................................................................. .......9 II. Cách nhập chữ tiếng Việt............................................. ......................10 Trang 114

II.1. Kiểu gõ VNI:.................................................................. ................10 II.2. Kiểu gõ TELEX:..................................................................... ........10 III. Nhập và chỉnh sửa dữ liệu....................................... .........................10 III.1. Quy tắc chung ...................................................................... ........10 III.2. Một số thủ thuật nhập dữ liệu nhanh ...........................................11 III.3. Gõ dữ liệu kiểu text ................................................................ ......13 III.4. Gõ dữ liệu kiểu số............................................................. ............14 III.5. Gõ dữ liệu kiểu ngày tháng................................................... ........14 IV. Các phương tiện sửa và xoá................................. .............................15 IV.1. Sửa dữ liệu ....................................................... ............................15 IV.2. Thay toàn bộ dữ liệu cũ bằng dữ liệu hoàn toàn mới ...................15 IV.3. Sửa một phần nội dung trong ô ....................................................15 IV.4. Xoá dữ liệu ............................................................... ....................16 V. Cách di chuyển con trỏ ở trong bảng tính.........................................16 V.1. Di chuyển con trỏ ô bằng chuột ......................................... ............16 V.2. Di chuyển con trỏ ô bằng bàn phím .................................... ...........17 V.3. Dùng hộp hội thoại GoTo ................................ ..............................17 VI. Các thao tác với file dữ liệu............................... ..............................18 VI.1. Ghi file hiện thời vào đĩa.................................... ..........................18 VI.2. Ghi file hiện thời vào đĩa với tên khác..........................................20 VI.3. Gọi một file có sẵn từ đĩa ra màn hình.........................................21 VI.4. Tạo một file mới ............................................................... ............22 VI.5. Đóng file hiện thời .................................................. .....................23 Câu hỏi và bài tập................................................................... ................24

Trang 115

Bài 3: Các thao tác với hàng, cột, ô trong một bảng tính............................................................... .........26 I. Định dạng cột, dòng, ô......................................... ................................26 I.1. Thay đổi độ rộng cột.............................................. .........................26 I.1.1. Dùng chuột ................................................... ...........................26 I.1.2. Dùng thực đơn ..................................................... ....................26 I.2. Thay đổi độ cao hàng....................................................... ...............26 I.2.1. Dùng chuột ................................................... ...........................26 I.2.2. Dùng thực đơn ..................................................... ....................27 I.3. Nối và tách ô:..................................................................... .............27 II. Cách chọn hàng, cột và ô.......................................................... ..........27 II.1. Chọn hàng................................................................................... ...27 II.2. Chọn cột................................................................................... ......28 II.3. Chọn ô............................................................................................ 28 III. Cách chèn hàng, cột và ô.................................. ................................28 III.1. Chèn hàng ................................................................................... .28 III.2. Chèn cột .................................................................................... ...29 III.3. Chèn ô .......................................................... ...............................29 IV. Huỷ hàng, cột và ô ......................................................................... ....30 IV.1. Huỷ hàng ..................................................................... .................30 IV.2. Huỷ cột ...................................................................... ...................30 IV.3. Huỷ ô .............................................................................. ..............30 V. Các thao tác với các bảng tính trong một Workbook......................31 V.1. Kích hoạt một bảng tính ................................. ...............................31 V.2. Đổi tên bảng tính ........................................................ ...................32

Trang 116

V.3. Chèn thêm bảng tính mới ...................................................... .........32 V.4. Xoá bảng tính .................................................. ..............................33 V.5. Di chuyển hoặc sao chép một bảng tính ........................................33 Câu hỏi và bài tập................................................................... ................35 Bài 4: Sử dụng công thức và các hàm tính toán cơ bản..................................................... ..............................37 I. Sử dụng Công thức trong Excel........................... ...............................37 I.1. Các toán tử.............................................................................. ........37 I.1.1. Toán tử số:....................................................... .........................37 I.1.2. Toán tử chuỗi......................................................... ...................37 I.1.3. Toán tử so sánh.................................................... .....................37 I.2. Nhập công thức vào bảng tính........................................................ .38 I.2.1. Quy tắc chung ............................................................... ...........38 I.2.2. Công thức đơn giản ................................................................ ..38 I.3. Địa chỉ tương đối và tuyệt đối.......................................... ...............39 I.3.1. Địa chỉ tương đối:...................................................... ...............40 I.3.2. Địa chỉ tuyệt đối:........................................... ...........................40 I.4. Liên kết dữ liệu ...................................................................... .........40 I.4.1. Liên kết dữ liệu giữa các bảng tính.............................. .............40 I.4.2. Liên kết dữ liệu giữa các tập tin Excel:................................ .....41 II. Khái niệm hàm trong Excel:....................................... .......................41 II.1. Nhóm hàm số...................................................... ...........................41 II.1.1. Hàm SQRT .......................................................... ...................41 II.1.2. Hàm ABS ................................................. ..............................42 II.1.3. Hàm ROUND .............................................................. ...........42 II.2. Nhóm hàm thống kê ........................................ ..............................43 II.2.1. Hàm Average........................................................... ................43 II.2.2. Hàm Max............................................................................. ....43 Trang 117

II.2.3. Hàm MIN.......................................................... ......................44 II.2.4. Hàm SUM ................................................... ...........................44 II.2.5. Hàm COUNT ............................................. ............................46 II.2.6. Hàm RANK ...................................................................... ......46 II.3. Nhóm hàm đổi kiểu .............................................. .........................47 II.3.1. Hàm TEXT ............................................................... ..............47 II.3.2. Hàm VALUE ....................................................... ...................48 II.4. Nhóm hàm kí tự..................................................................... .........48 II.4.1. Hàm LEFT .............................................................................. 48 II.4.2. Hàm RIGHT ............................................................... ............49 II.4.3. Hàm MID ........................................................ .......................49 II.4.4. Hàm UPPER ........................................................................ ...50 II.4.5. Hàm LOWER .............................................................. ...........50 II.4.6. Hàm PROPER ......................................................... ...............50 II.4.7. Hàm TRIM .......................................................... ...................51 II.5. Nhóm hàm điều kiện....................................................................... 51 II.5.1. Hàm IF ....................................................... ............................51 II.5.2. SUMIF..................................................... ...............................52 II.5.3. COUNTIF ................................................ ..............................53 II.6. Nhóm hàm ngày tháng........................................... ........................53 II.6.1. Date....................................................................... ..................53 II.6.2. WEEKDAY............................................................. ................53 II.6.3. DAY.............................................................. ..........................53 II.6.4. MONTH................................................................. .................54 II.6.5. YEAR......................................................................... .............54 II.7. Nhóm hàm logic.............................................................. ...............54 II.7.1. Hàm And........................................................ .........................54 II.7.2. Hàm OR .................................................... .............................55 II.7.3. Hàm NOT........................................................................ ........56 II.8. Nhóm hàm tìm kiếm.......................................... .............................56

Trang 118

II.8.1. Hàm VLOOKUP.................................................. ...................56 II.8.2. Hàm HLOOKUP.................................................... .................57 II.9. Cách sử dụng hộp Function ....................................... ...................57 Câu hỏi và bài tập................................................................... ................60 Bài 5: Sao chép và di chuyển dữ liệu................................... ..................62 I. Sao chép .................................................................. .............................62 I.1. Sao chép dữ liệu ............................................................ .................62 I.2. Dùng chuột .......................................................................... ...........62 I.2.1. Dùng nút trái chuột: .................................................. ...............62 I.2.2. Dùng nút phải chuột ................................................................ .63 I.3. Dùng bộ đệm (clipboard) .................................... ...........................64 I.4. Sao chép bình thường: ................................... ................................64 I.5. Dán đặc biệt ................................................................... ................65 I.6. Sao chép sang vùng lân cận nhờ AutoFill (tự động điền dữ liệu) ...66 II. Di chuyển dữ liệu ....................................................... ........................66 II.1. Dùng chuột ................................................................................... .67 II.1.1. Dùng nút trái chuột ......................................................... ........67 II.1.2. Dùng nút phải chuột ....................................... ........................68 II.2. Dùng bộ đệm (clipboard) ............................................. .................68 II.3. Di chuyển bình thường: .................................. ..............................69 III. Xoá dữ liệu:....................................................................................... .69 IV. Sao chép định dạng, chèn ghi chú......................................... ............70 IV.1. Sao chép định dạng: ....................................................... ..............70 IV.2. Chèn ghi chú (Comment):................................ .............................70 IV.3. Chỉnh sửa và xoá ghi chú:.................................................... .........70 Trang 119

Câu hỏi và bài tập................................................................... ................71 Bài 6: Định dạng cho dữ liệu.......................................................... ........72 I. Định dạng cho dữ liệu kiểu số............................. ................................72 II. Định dạng ngày tháng........................................ ................................73 III. Định dạng vị trí................................................. ................................74 III.1. Định dạng kiểu chữ cho dữ liệu....................................... .............75 III.2. Tạo đường viền ô............................................................ ..............76 III.3. Tô màu.................................................................................. ........77 Bài 7: Cơ sở dữ liệu trong excel và cách đặt tên cho vùng dữ liệu........................................................................... ..80 I. Khái niệm ..................................................................................... ........80 II. Cách tạo một danh sách trên bảng tính ...........................................81 II.1. Kích thước và vị trí của danh sách .............................................. ..81 II.2. Các nhãn của cột (column labels) ......................................... ........81 II.3. Nội dung của hàng và cột .............................. ...............................81 III. Cách đặt tên cho vùng dữ liệu và truy cập tới vùng đã đặt tên ....82 III.1. Cách đặt tên cho một ô hoặc một vùng ô .....................................82 III.2. Đặt tên cho các ô bằng cách sử dụng các nhãn hàng và cột có sẵn 83 III.3. Truy cập tới vùng đã đặt tên .................................. ......................83 Bài 8: Tìm kiếm và sắp xếp dữ liệu...................................................... ..85 I. Tìm kiếm và thay thế........................................................................... .85 I.1. Tìm kiếm:......................................................... ...............................85 I.2. Thay thế:.............................................................. ...........................85

Trang 120

II. Sắp xếp dữ liệu...................................................................... ..............86 II.1. Sắp xếp dữ liệu tăng hoặc giảm dần theo nội dung của một cột .. .86 II.2. Sắp xếp dữ liệu theo nội dung của hai hoặc nhiều cột ..................87 II.3. Sắp xếp xếp dữ liệu theo nội dung của hàng .................................88 II.4. Sắp xếp theo tháng, ngày hoặc theo thứ tự do người dùng tạo......89 II.4.1. Tạo danh sách theo thứ tự do người dùng tạo (custom list) ...89 II.4.2. Sắp xếp theo tháng, ngày hoặc theo thứ tự của danh sách tự tạo 90 Bài 9: Tạo biểu đồ................................................................ ...................94 I. Các phần tử cơ bản của biểu đồ...................................... ....................94 II. Tạo biểu đồ ngầm định theo một bước ..................................... .......94 III. Cách tạo đồ thị nhờ Chart Wizarrd ......................................... .......96 Bài 10: Định dạng trang và In ấn.......................... ..............................101 I. Định dạng trang................................................................................ ..101 I.1. Định dạng trang......................................................... ...................101 I.1.1. Page.................................................................. ......................102 I.1.2. Margins........................................................................... ........102 I.1.3. Header/Footer...................................................... ...................102 I.1.4. Sheet.................................................................... ...................103 II. In................................................................................... .....................104 Câu hỏi và bài tập................................................................ .................105 Ôn tập............................................................................. .......................106 I. Một số thao tác cơ bản với file dữ liệu........................... ...................106 II. Nhập, sửa, xoá dữ liệu............................................ ..........................106 II.1. Nhập hằng:....................................................... ...........................106

Trang 121

II.2. Nhập công thức:........................................................................ ...106 II.3. Sửa toàn bộ dữ liệu.................................................... ..................106 II.4. Sửa một phần dữ liệu:..................................................... .............106 II.5. Xoá dữ liệu............................................................................. ......107 III. Các thao tác với hàng, cột, ô................................................ ...........107 III.1. Chọn hàng, cột ô ..................................................................... ...107 III.2. Chèn thêm hàng, cột, ô......................................... ......................107 III.3. Huỷ thêm hàng, cột, ô................................................... ..............107 III.4. Thay đổi độ rộng cột............................................................. ......107 III.5. Thay đổi độ cao hàng..................................... ............................107 IV. Các thao tác với một bảng tính ............................... .......................108 IV.1. Kích hoạt bảng tính................................................... ..................108 IV.2. Các thao tác với sheet...................................... ...........................108 V. Một số hàm thông dụng..................................................................... 108 V.1. Các hàm thống kê.................................................. .......................108 V.2. Các hàm đổi kiểu....................................................... ...................108 V.3. Các hàm kí tự............................................................................. ...109 V.4. Các hàm toán học......................................................... ................109 V.5. Các hàm logic...................................................................... .........109 V.6. Cách sử dụng hộp Function................................................ ..........109 VI. Sao chép và di chuyển dữ liệu................................................... ......109 VI.1. Sao chép dữ liệu...................................................................... ....109 VI.2. Di chuyểndữ liệu.................................................... .....................110 VII. Cơ sở dữ liệu ...................................................................... ............110

Trang 122

VIII. Sắp xếp dữ liệu ........................................................... ..................111 IX. Biểu đồ ...................................................................... .......................111 X. Định dạng cho dữ liệu và in ấn......................................... ................111 MỤC LỤC............................................................... ..............................114

Trang 123