Bài giảng Tìm hiểu về Microsoft excel

105 Chương 5 Microsoft EXCEL Microsoft Excel là một phần mềm trong bộ Microsoft Office của hãng Microsoft, Excel thực sự là một công cụ rất hữu ích để quản lý các dữ liệu không quá lớn và những tính toán, đánh giá hàng ngày trong nhiều lĩnh vực khác nhau. Excel có các đặc tính và ứng dụng tiêu biểu sau: Thực hiện được nhiều phép tính từ đơn giản đến phức tạp. Tổ chức và lưu trữ thông tin dưới dạng bảng như bảng lương, bảng kết toán, bảng thanh toán, bảng thống kê, bảng dự toán,... Khi có thay đổi dữ liệu, bảng tính tự động tính toán lại theo số liệu mới. Thao tác trên bảng tính có thể tạo ra các báo cáo tổng hợp hoặc phân tích có kèm theo các biểu đồ, hình vẽ minh họa,... 5.1. Các thao tác cơ bản 1. Khởi động, thoát khỏi Excel Khởi động Excel: Dùng lệnh Start | Programs | Microsoft Excel Ngoài cách khởi động trên, ta còn có thể khởi động Ms. Excel theo các cách sau: Click biểu tượng Excel trên màn hình nền. Click biểu tượng Excel trên Start Menu. Thoát khỏi Excel: Nên ghi lại tập tin soạn thảo trước khi thoát, nếu không Ms. Excel sẽ hỏi: - Chọn Yes nếu muốn ghi. - Chọn No nếu không muốn ghi. - Chọn Cancel nếu muốn hủy lệnh thoát. Các cách thoát : Click nút Close ở góc trên phải màn hình. Dùng lệnh File | Exit. Double click biểu tượng của chương trình ở góc trên trái màn hình. Nhấn tổ hợp phím Alt + F4. 106 2. Màn hình Excel Menu Bar (thực đơn ngang): Thanh chứa tên các mục lệnh chính của Excel, mỗi mục trên Menu Bar ứng với một Menu Popup (thực đơn dọc). Thao tác để mở một Menu Popup là click vào tên mục hoặc nhấn tổ hợp phím Alt + ký tự đại diện của tên mục. Standard (thanh công cụ chuẩn): chứa các biểu tượng, nút điều khiển thực hiện các chức năng thông dụng như ghi nội dung tập tin vào đĩa, mở tập tin ... Formatting (thanh định dạng): chứa các biểu tượng, nút điều khiển dùng cho việc định dạng dữ liệu bảng tính như loại font chữ, cỡ font, canh lề ... Formula (thanh công thức): hiển thị toạ độ ô hiện hành, nút huỷ bỏ (Cancel), nút chọn (Enter), nút chọn hàm, nội dung dữ liệu của ô hiện hành. Toạ độ ô Cancel Enter Nội dung dữ liệu của ô Drawing (thanh vẽ): chứa các công cụ để vẽ hình. Để hiển thị hay ẩn các thanh công cụ, ta dùng lệnh View | Toolbars, sau đó click đánh dấu hoặc bỏ dấu check tại các mục tương ứng. Title Bar Menu Bar Standard Formatting Status bar Drawing Formula Row Border Column Border 107 Workbook: cửa sổ chứa nội dung tập tin. Tên tập tin hiện trên thanh tiêu đề cửa sổ với phần mở rộng định sẵn là XLS. Tên tập tin Workbook mặc nhiên là Book# (# là số thứ tự tương ứng với những lần mở tập tin). Các thành phần của Workbook là: - ĐƯỜNG VIỀN NGANG (Column Border): ghi ký hiệu cột từ trái sang phải theo chữ cái A, B, C,...,Y, Z, AA, AB,..., IV. Cột (Column) là một tập hợp những ô theo chiều dọc. Độ rộng mặc nhiên là 9 ký tự (có thể thay đổi trị số này từ 0 đến 255). Có tổng cộng 256 cột. - ĐƯỜNG VIỀN DỌC (Row Border): ghi số thứ tự dòng từ trên xuống dưới. Dòng (Row) là một tập hợp những ô theo chiều ngang. Chiều cao mặc nhiên là 12.75 chấm điểm (có thể thay đổi trị số này từ 0 đến 409). Có tổng cộng 65536 dòng. - Ô (Cell): giao của một dòng với một cột. Địa chỉ của một ô xác định bởi cột trước dòng sau, ví dụ: B6 là địa chỉ của ô nằm trên cột B, dòng thứ 6. Ô hiện hành (Select cell) là ô có khung viền quanh. - VÙNG BẢNG TÍNH : bao gồm một hoặc nhiều ô đứng liền nhau, tên của vùng được xác định bởi tên của ô ở góc trên bên trái và tên của ô ở góc dưới phải của vùng, phân cách tên hai ô bằng dấu hai chấm (:). Ví dụ: vùng B2:D6. 108 - BẢNG TÍNH (Sheet): là một bảng gồm có 256 cột và 65536 dòng. Tên bảng tính mặc nhiên là Sheet# (# là số thứ tự). Một tập tin Excel thông thường có 3 bảng tính. Ta có thể quy định số Sheet trong một tập tin Workbook bằng lệnh Tools | Options, chọn lớp General: Chọn số lượng sheet trong mục Sheets in new workbook (có thể lên đến 255). Trong lớp General ta cũng có thể chọn phông chữ và cỡ chữ ngầm định cho các Sheet. - Status Bar: dòng chứa chế độ làm việc hiện hành hay ý nghĩa lệnh hiện hành của bảng tính và các tình trạng hiện hành của hệ thống như NumLock, Capslock,... Các chế độ làm việc thông thường gồm: Ready: sẵn sàng nhập dữ liệu. Enter : đang nhập dữ liệu. Point : đang ghi chép công thức tham chiếu đến một địa chỉ. Edit : đang điều chỉnh dữ liệu hay công thức trong ô hiện hành. Chọn chế độ này bằng cách di chuyển đến ô muốn điều chỉnh và gõ phím F2. - Trên bảng tính, con trỏ chuột là hình chữ thập rỗng, con trỏ ô là một khung chữ nhật bao xung quanh một ô, ô có con trỏ gọi là ô hiện hành. 3. Làm việc với tập tin Excel a. Tạo tập tin mới: Click biểu tượng New trên thanh Standard để tạo nhanh một tập tin mới, hoặc sử dụng lệnh File | New. b. Mở một tập tin Excel đã có trên đĩa: - Dùng lệnh File | Open (hoặc click biểu tượng trên thanh Standard) để mở cửa sổ Open. 109 - Trong cửa sổ Open, chọn ổ đĩa và thư mục chứa tập tin muốn mở trong khung Look in. - Click chọn tập tin Excel và click nút Open. c. Lưu tập tin vào đĩa: Click biểu tượng trên thanh Standard hoặc dùng lệnh File | Save - Nếu là lưu lần đầu Excel sẽ mở hộp thoại Save As (Theo mặc định tên sẽ là Book1.xls hoặc Book2.xls ...), nếu không phải lần đầu Excel sẽ tự động lưu mà không thông báo gì. - Trong trường hợp tập tin đã lưu rồi nhưng bạn muốn lưu với tên khác thì chọn lệnh File | Save As. Hộp thoại Save As: 110 - Nếu bạn muốn bảo mật tập tin, bạn có thể đặt mật khẩu cho tập tin nhằm không cho người khác mở tập tin của mình. Trong hộp thoại Save As, bạn chọn Tools | General Options. Hộp thoại Save Option hiển thị: Always create backup: tạo ra một tập tin dự phòng trước khi lưu. Password to open: tạo mật khẩu mở tập tin Password to modify: tạo mật khẩu để lưu tập tin Read only recommended: đặt thuộc tính chỉ đọc cho tập tin 4. Cách nhập dữ liệu Để nhập dữ liệu vào ô nào bạn click chuột vào ô đó để chọn, hoặc dùng các phím mũi tên trên bàn phím để di chuyển ô chọn. Tuỳ loại dữ liệu mà bạn có những cách nhập khác nhau. Khi nhập xong gõ Enter hoặc dùng các phím mũi tên để di chuyển đến các ô khác. Các kiểu dữ liệu : Trong một ô chỉ có thể chứa một kiểu dữ liệu. Kiểu dữ liệu phụ thuộc vào ký tự đầu tiên gõ vào. a. Kiểu số (Number). Ký tự đầu tiên gõ vào là các chữ số từ 0 đến 9, các dấu + - . ( $. Một số được nhập vào ở chế độ mặc định hiển thị ở bên phải của ô và ở dạng General, sau đó có thể định dạng trình bày số lại theo ý muốn bằng lệnh Format | Cells. Lưu ý: không được nhập dấu phân cách hàng ngàn. Thông thường Excel sử dụng dấu chấm (.) là dấu phân cách số lẻ; dấu phẩy (,) là dấu phân cách hàng ngàn. b. Kiểu chuỗi (Text). Ký tự đầu tiên gõ vào là các ký tự chữ từ A đến Z, các ký tự canh biên như sau: Ký tự ' để canh các ký tự trong ô về bên trái; ký tự " để canh các ký tự trong ô về bên phải; Ký tự ^ để canh các ký tự trong ô vào giữa; ký tự \ để lặp lại ký tự theo sau nó cho đến hết chiều rộng ô. Các ký tự canh biên chỉ có tác dụng khi người sử dụng có chỉ định: Tools | Option | Transition, trong đó chọn mục:Transition navigation Keys. Lưu ý: nếu muốn các số hiển thị theo dữ liệu chuỗi thì phải nhập dấu nháy đơn (‘) đứng trước dãy số, như vậy các số này sẽ không tính toán được. c. Kiểu công thức (Formular). Trong Excel, bạn có thể nhập các công thức để tính toán các dữ liệu trong bảng tính, công thức có thể là các phép toán cộng, trừ, nhân, chia … hay các hàm trong Excel … Khi nhập công thức, ký tự đầu tiên gõ vào là dấu bằng (=), cộng (+) hoặc dấu trừ (-). Kết quả trình bày trong ô không phải là các ký tự gõ vào mà chính là giá trị của công thức đó. Ví dụ : gõ =4*5+2 thì kết quả trình bày trong ô là 22. Trong thành phần của một công thức có thể gồm có : số, 111 chuỗi (phải được đặt trong cặp nháy kép), tọa độ ô, tên vùng, các toán tử, các loại hàm. Các toán tử sử dụng trong công thức: Toán tử tính toán: + (cộng) - (trừ) * (nhân) / (chia) ^ (lũy thừa) % (phần trăm) Ví dụ : = 25 + 6 kết quả {31} = 16 - 4 {12} = 80/10 {8} = 80 * 25% {20} = 2^4 {16} Toán tử chuỗi: & (nối chuỗi) Ví dụ : = 24 & “Bis” {24Bis} Lưu ý : Trong công thức muốn nhập chữ phải để trong dấu ngoặc kép. Toán tử so sánh: = (bằng) (không bằng) > (lớn hơn) >= (lớn hơn hay bằng) < (nhỏ hơn) <= (nhỏ hơn hay bằng). Độ ưu tiên của các toán tử trong công thức theo thứ tự sau: 1. Biểu thức trong ngoặc (...) 2. Lũy thừa 3. Nhân, chia 4. Cộng, trừ Trong các phép toán cùng ưu tiên, thì phép toán nào đứng trước được tính trước. Ví dụ : = 3+5*3 {18} = (3+5)*3 {24} = 5/2*3 {7.5} = 2*3^(2+1)-2 {52} d. Kiểu ngày tháng: tức là nhập ngày, tháng, ... vào máy tính, và làm cho Excel hiểu được ngày tháng để tính ra số ngày, tháng, ... Thông thường bạn cần nhập ngày tháng bằng các con số, nhưng phải nhập tháng trước ngày sau, rồi đến năm. Phân cách giữa ngày tháng năm là dấu (-) hay dấu (/), nếu không nhập năm thì Excel sẽ hiểu là năm hiện hành. Tuỳ theo cách định dạng mà Excel sẽ hiển thị tháng bằng chữ hay bằng số, nhưng thông thường tháng được hiển thị bằng 3 ký tự tiếng Anh viết tắt. Ví dụ : 25-May (ngày 25 tháng 5). Ghi chú : Khi muốn hiệu chỉnh dữ liệu của ô nào bạn cần double click vào ô đó. Có thể click chọn ô muốn nhập dữ liệu, sau đó click trỏ chuột vào khung công thức để nhập hoặc hiệu chỉnh dữ liệu, nhập xong gõ Enter. Nếu nhập tiếng Việt vào khung công thức, bạn sẽ không thấy hiển thị dấu tiếng Việt, nhưng dấu tiếng Việt vẫn hiển thị trong ô. Khi chiều dài của dữ liệu dài hơn chiều rộng của ô thì có thể thấy hiển thị như sau: - Tràn sang ô bên phải, nếu ô bên phải không có dữ liệu. 112 - Ẩn phần dữ liệu dài hơn nếu ô bên phải có dữ liệu. - Nếu là dữ liệu kiểu số hay ngày tháng dài hơn chiều rộng của ô thì có thể chỉ thấy hiển thị các dấu ###... nhưng vẫn tính toán được. Để xoá dữ liệu, bạn chọn vùng muốn xoá, sau đó gõ phím Delete hoặc dùng lệnh Edit | Clear. 5. Chọn vùng bảng tính Trước khi thực hiện một thao tác, bạn phải chọn phạm vi làm việc (có thể một vùng hoặc nhiều vùng). Chọn một ô: Di chuyển con trỏ ô đến ô chọn hoặc click tại ô chọn. Chọn một cột: Click tại ký hiệu cột muốn chọn. Chọn một dòng: Click tại ký hiệu (số thứ tự) dòng muốn chọn. Chọn nhiều dòng hoặc cột liền nhau: Drag trên các ký hiệu dòng hoặc cột muốn chọn. Chọn các dòng hoặc cột không liền nhau: Nhấn và giữ phím Ctrl, sau đó click vào ký hiệu dòng hoặc cột muốn chọn. Chọn một vùng, có các cách chọn: - Đặt con trỏ tại ô đầu vùng, ấn Shift, dùng các phím mũi tên dịch chuyển đến ô cuối vùng. - Đặt con trỏ chuột tại ô đầu vùng, drag đến ô cuối vùng. - Đặt con trỏ chuột tại ô đầu vùng, ấn và giữ phím Shift trong khi click tại ô cuối vùng. Chọn nhiều vùng: Nhấn và giữ phím Ctrl trong khi dùng chuột thực hiện thao tác chọn các vùng khác nhau. Chọn toàn bộ bảng tính: Nhấn tổ hợp phím Ctrl+A, hay click vào ô giao điểm của tiêu đề dòng và cột. 113 6. Định dạng Font chữ Chọn vùng muốn định dạng và thực hiện như sau: a. Định dạng nhanh bằng thanh định dạng (Formatting): Nếu không thấy hiển thị thanh Formatting, thì thực hiện lệnh View | Toolbar | Formatting. Click mở khung Font để chọn kiểu chữ. Click mở khung Size để chọn cỡ chữ. Các biểu tượng định dạng : Bold : chữ đậm. Italic : chữ nghiêng. Underline : gạch dưới. b. Định dạng bằng cửa sổ Format: Dùng lệnh Format | Cells ... để mở cửa sổ Format Cells, và click chọn lớp Font. Chọn kiểu chữ trong khung Font. Chọn dạng chữ trong khung Font Style. Chọn cỡ chữ trong khung Size. Click mở khung Underline để chọn dạng gạch dưới. Mở khung Color để chọn màu. Khung Effects có các mục sau: Khung F Khung Font Si 114 Strikethrough : gạch ngang chữ. Trung tâm. Superscrip : đưa lên trên. Ví dụ : 2x2 + 3x = 0. Subscrip : đưa xuống dưới. Ví dụ : H2O. 7. Hiệu chỉnh dòng và cột a. Thay đổi chiều cao dòng, độ rộng cột: Đưa trỏ chuột đến đường biên trên thanh tiêu đề của dòng hay cột (trỏ chuột chuyển thành hình mũi tên hai đầu), drag để thay đổi chiều cao dòng hay độ rộng cột. ƒ Khi double click vào đường biên trên thanh tiêu đề của dòng, bạn sẽ có chiều cao của dòng bằng chiều cao của ô dữ liệu cao nhất trong dòng. Khi double click vào đường biên trên thanh tiêu đề của cột, bạn sẽ có chiều rộng của cột bằng chiều rộng của ô dữ liệu rộng nhất trong cột. Bạn có thể hiệu chỉnh chiều cao của dòng và chiều rộng của cột từ trình đơn Format trên Menu Bar (cần phải lựa chọn các cột hoặc dòng muốn hiệu chỉnh trước khi dùng trình đơn này). Hiệu chỉnh chiều cao dòng: dùng lệnh Format | Row : - Height : hiệu chỉnh chiều cao dòng. - AutoFit : tự động hiệu chỉnh độ cao. - Hide : ẩn. - Unhide : thôi ẩn. Thực hiện tương tự với lệnh Format | Column | ... để điều chỉnh độ rộng cột. b. Chèn dòng, cột: Di chuyển ô chọn đến dòng hay cột muốn chèn và sử dụng trình đơn Insert: 115 Chèn dòng : Dùng lệnh Insert | Rows. Chèn cột : Dùng lệnh Insert | Columns. c. Xóa dòng, cột: Chọn dòng hay cột muốn xóa. Dùng lệnh Edit | Delete ... Lưu ý: Nếu bạn nhấn phím Delete thì chỉ xóa được nội dung trong vùng chọn. 8. Thao tác trên vùng a. Xóa dữ liệu vùng: Chọn vùng muốn xóa, gõ phím Del. b. Hiệu chỉnh dữ liệu vùng: Chọn vùng cần hiệu chỉnh, gõ F2. Dùng phím mũi tên chuyển điểm chèn (vạch đứng nhấp nháy) tới ký tự hoặc số sai cần sửa. Gõ phím Backspace để xóa ký tự hoặc số trước điểm chèn; gõ phím Delete để xóa ký tự hoặc số sau điểm chèn. Gõ phím Enter để kết thúc hiệu chỉnh. c. Khôi phục dữ liệu: Để hủy bỏ một thao tác vừa thực hiện, có hai cách: - Dùng bàn phím: thực hiện lệnh Edit | Undo ... hay gõ Ctrl+Z. - Dùng chuột: nháy chuột vào biểu tượng Undo trên Standard Toolbar. d. Sao chép dữ liệu vùng: Chọn vùng nguồn (Source) Thực hiện lệnh Edit | Copy (hoặc gõ Ctrl + C) Chọn vùng đích (Destination area) có cùng dạng với vùng nguồn hay di chuyển con trỏ ô đến vị trí của ô góc trên bên trái của vùng đích. Thực hiện lệnh : Edit | Paste (hoặc gõ Ctrl + V) Hoặc có thể sao chép dữ liệu vùng bằng cách đặt con trỏ chuột vào cạnh viền của vùng cần sao chép sao cho con trỏ có dạng mũi tên É, rồi ấn phím Ctrl trong khi drag kéo vùng đến vị trí mới. GHI CHÚ : Khi chép dữ liệu, nếu vùng nguồn chứa dữ liệu số hay chuỗi, kết quả vùng đích sẽ giống vùng nguồn. Nếu vùng nguồn chứa dữ liệu kiểu công thức, kết quả vùng đích sẽ thay đổi hay không tùy thuộc vào công thức trong vùng nguồn tham chiếu địa chỉ tương đối hay tuyệt đối. a. Sự khác nhau của việc sao chép một vùng có công thức tham chiếu địa chỉ tương đối, địa chỉ tuyệt đối và địa chỉ hỗn hợp. Địa chỉ tương đối (Relative Address): Địa chỉ tham chiếu có dạng . Khi chép đến vùng đích, địa chỉ tham chiếu của vùng đích sẽ thay đổi theo nghĩa phương, chiều và khoảng cách. Ví dụ: Có dữ liệu tại các ô như sau : A1=2, B1=4, A2=4, B2=6. 116 A B C D 1 2 4 8 32 2 4 6 24 144 Công thức tại ô C1 là : =A1*B1 ⇒ Kết qủa là 8 Khi sao chép công thức của ô C1 vào ô C2 thì công thức tại ô này là: =A2*B2 và kết quả là 24 Khi sao chép công thức của ô C1 vào ô D1 thì công thức tại ô này là: =B1*C1 và kết quả là 32 Địa chỉ A1, B1 trong công thức của ô C1 là địa chỉ tương đối. Địa chỉ tuyệt đối (Absolute Address): Địa chỉ tham chiếu có dạng $$. Khi chép đến vùng đích, địa chỉ tham chiếu của vùng đích sẽ giữ nguyên giống như vùng nguồn. Ví dụ: Có dữ liệu tại các ô trong bảng đổi tiền như sau: A B C 1 Số tiền đổi : 10000000 2 Ngoại tệ Tỉ giá Số NT đổi được 3 Đô la Mỹ 15752 4 Yên Nhật 144 5 Đô la Úc 11397 Số tiền đổi được ghi vào ô C1 Cột Ngoại tệ ghi các loại ngoại tệ hiện có Cột Tỉ giá ghi tỉ giá hiện hành của các loại ngoại tệ Cột Số NT đổi được tính theo công thức: Số tiền đổi/ Tỉ giá cho những ô tương ứng. Tại ô C3 nhập công thức: =$C$1/B3. Sau đó chép công thức từ ô này đến các ô còn lại trong cột, bạn thấy địa chỉ ô $C$1 trong công thức sẽ không bị thay đổi. Địa chỉ $C$1 được gọi là địa chỉ tuyệt đối. Địa chỉ hỗn hợp (Mixed Address): Địa chỉ tham chiếu có dạng $ (tuyệt đối cột, tương đối dòng) hoặc $ (tương đối cột, tuyệt đối dòng). Khi chép đến vùng đích, địa chỉ tham chiếu của vùng đích bị thay đổi một cách tương ứng hoặc theo cột hoặc theo hàng. Ví dụ : Công thức của ô C5 là : =A$5+B$5 Sao chép công thức này đến ô C6 là : =A$5+B$5 Công thức của ô C5 là : =$A5+$B5 Sao chép công thức này đến ô F5 là : =$A5+$B5 Công thức: =A1*B1 Công thức: =$C$1/B3 117 Lưu ý: Có thể chuyển đổi nhanh kiểu địa chỉ (tương đối/ tuyệt đối) bằng phím F4: Bấm F4 một lần: tuyệt đối cột, dòng; bấm F4 lần nữa: tương đối cột, tuyệt đối dòng; bấm F4 lần nữa: tuyệt đối cột, tương đối dòng; bấm F4 lần nữa: tương đối cột, dòng. b. Di chuyển dữ liệu vùng : Chọn vùng nguồn (Source). Thực hiện lệnh Edit | Cut (hoặc gõ Ctrl+X). Chọn vùng đích (Destination area) có cùng dạng với vùng nguồn hay di chuyển con trỏ ô đến vị trí của ô góc trên bên trái của vùng đích. Thực hiện lệnh Edit | Paste (hoặc gõ Ctrl+V). Hoặc có thể di chuyển dữ liệu vùng bằng cách đặt con trỏ chuột vào cạnh viền của vùng cần sao chép sao cho con trỏ có dạng mũi tên É, rồi rê chuột kéo vùng đến vị trí mới. 9. Điền dãy số tự động Điền dãy số tự động là việc điền một dãy số vào một hàng hay một cột ô. Thao tác như sau: Gõ giá trị số bắt đầu vào ô đầu tiên. Chọn vùng cần đánh số thứ tự. Thực hiện lệnh Edit | Fill | Series. Hộp đối thoại sau hiện ra: Mục Series: chọn việc đánh chuỗi số trên dòng (Rows) hay trên cột (Columns). Mục Type: chọn kiểu điền dữ kiện: - Linear: cộng với trị số bước nhảy - Growth: nhân với trị số bước nhảy - Date: theo dạng ngày - AutoFill: theo chế độ điền tự động Mục Step Value: chọn trị số bước nhảy. Mục Stop Value: chọn trị số kết thúc. 5.2. Hàm trong Excel Trong Excel, bạn có thể thành lập các công thức để tính toán giá trị của các ô. Thay vì phải nhập giá trị của ô nào đó vào công thức thì chỉ cần nhập địa chỉ của ô là đủ, Excel sẽ tự hiểu là lấy giá trị của các ô đó tính toán với nhau. Ví dụ: 118 Lưu ý : Những báo lỗi thường gặp khi nhập công thức : #VALUE! : Trong công thức có cộng và/ hoặc trừ các dữ liệu kiểu chuỗi nên làm cho giá trị trở nên vô nghĩa. #NUM! : Có thể đã sử dụng các con số trong công thức không hợp lý. #REF! : Trong công thức có tham chiếu đến ô không có thực. #NAME? : Sai tên ô hoặc tên vùng... 1. Các hàm thường dùng Excel có một số hàm mẫu (Function Wizard) dùng tiện lợi và đơn giản, ví dụ công thức =A3+A4+A5+A6+A7 có thể thay bằng hàm SUM(A3:A7). Dạng thức tổng quát của hàm: =(Danh sách đối số) Tên hàm: tên hàm mẫu do Excel quy định. Ví dụ: SUM, AVERAGE, MAX,... Danh sách đối số: có thể là các trị số, dãy các ô, địa chỉ ô, tên vùng, công thức, tên hàm. Chú ý: Hàm phải bắt đầu bởi dấu bằng (=), tên hàm không phân biệt chữ thường và chữ hoa. Đối số phải đặt trong ngoặc đơn ( ), giữa các đối số phân cách nhau bởi dấu phẩy. a. Các hàm số học: Hàm ABS(N): cho giá trị tuyệt đối của biểu thức số N Ví dụ: = ABS(-25) cho kết quả 25 = ABS(5-149) cho kết quả 144 Hàm SQRT(N): cho trị là căn bậc hai của biểu thức số N (N>0) Ví dụ: = SQRT(25) cho kết quả 5 119 Hàm INT(N): cho trị là phần nguyên của biểu thức số N Ví dụ: = INT(236.26) cho kết quả 236 Hàm PI(): cho trị là số Pi (3.141593) Hàm MOD(N,M): cho trị là phần dư của phép chia nguyên N cho M Ví dụ: = MOD(10,3) cho kết quả 1 = MOD(8,2) cho kết quả 0 Hàm ROUND(biểu_thức_số, n): làm tròn giá trị của biểu_thức_số đến n số lẻ. Nếu n > 0: làm tròn về bên phải cột thập phân. Nếu n < 0: làm tròn về bên trái cột thập phân. Nếu n = 0: làm tròn, không lấy số lẻ. Ví dụ: = ROUND(10/3,0) cho kết quả 3 = ROUND(10/3,2) cho kết quả 3.33 = ROUND(333333,-3) cho kết quả 333000 = ROUND(35123.374,2) cho kết quả 35123.37 = ROUND(12345.5432,0) cho kết quả 12346 Hàm SUM(danh sách các trị): tính tổng của các giá trị có trong danh sách. Ví dụ: Dữ liệu trong các ô: B1, B2, B3, B6 lần lượt là : 4, 8, 3, và 6. Công thức ở ô B7 là: =SUM(B1:B3,B6). Giá trị trả về trong ô B7 là 21. Hàm SUMPRODUCT: Tổng của các tích Cú pháp: SUMPRODUCT(array1, [array2], [array3],…) Nhân các thành phần tương ứng trong các dãy array1, array2, array3,… rồi tính tổng của các tích này. Ví dụ: {3*2 + 4*7 + 8*6 + 6*7 + 1*5 + 9*3} Hàm AVERAGE(danh sách các trị ): tính trung bình cộng của các giá trị có trong danh sách. Ví dụ: Dữ liệu trong các ô: B1, B2, B3, B4 lần lượt là: 4, 8, 6 và 2. Công thức ở ô B6 là: =AVERAGE(B1:B4,5). Giá trị trả về trong ô B6 là 5. Hàm MAX(danh sách các trị): tìm giá trị số học lớn nhất của các giá trị có trong danh sách. Ví dụ: Dữ liệu trong các ô : B1, B2, B3, B4 lần lượt là : 4, 8, 6, 2. =MAX(B1:B4) cho kết quả 8 Hàm MIN(danh sách các trị): tìm giá trị số học nhỏ nhất của các giá trị có trong danh sách. Ví dụ: Dữ liệu trong các ô : B1, B2, B3, B4 lần lượt là : 4, 8, 6, 2. =MIN(B1:B4) cho kết quả 2 120 Ví dụ: Xem bảng tính sau: Lưu ý: Trong Excel có sẵn tính năng tính toán nhanh như sau: Trước tiên bạn đánh dấu vùng cần tính toán rồi right click vào thanh trạng thái của Excel (nằm bên dưới màn hình, nếu không thấy bạn vào menu View, chọn Status Bar), sẽ hiện menu có các mục chọn là các giá trị cần tính: None (huỷ giá trị đã tính), Average (tính trị trung bình), Count (đếm số ô), Count Nums (chỉ đếm số ô chưa giá trị số), Max (tìm giá trị lớn nhất), Min (tìm giá trị nhỏ nhất) và Sum (tính tổng các giá trị). Tiếp theo bạn chỉ cần click một mục tương ứng thì kết quả sẽ được hiển thị ngay bên phải thanh trạng thái. b. Các hàm điều kiện: Hàm IF: Trả về các giá trị cho trước với điều kiện nào đó. Cú pháp: IF(điều kiện, trị đúng, trị sai) - Điều kiện là một biểu thức logic. - Excel sẽ kiểm tra kết quả của biểu thức logic: nếu đúng sẽ chọn trị thứ nhất (trị đúng trong cú pháp), nếu sai sẽ chọn trị thứ hai (trị sai trong cú pháp). - Bản thân trị đúng, trị sai có thể là những hằng trị, biểu thức (chuỗi, số, logic) và cũng có thể là một hàm IF. Ví dụ : Xem bảng sau : 121 Thiết lập công thức cho cột “ghi chú” với điều kiện thí sinh nào có tổng số điểm từ 16 trở lên thì ghi “Đậu”, ngược lại thì ghi “Hỏng”. Công thức ở ô F3 : =IF(E3>=16,”Đậu”, “Hỏng”). Lưu ý: Các giá trị trả về nếu là dạng chuỗi thì phải để trong dấu ngoặc kép. Hàm SUMIF: Tính tổng có điều kiện. Cú pháp: SUMIF(vùng điều kiện, điều kiện, vùng tính tổng) Nếu các ô trong vùng điều kiện thỏa mãn điều kiện thì cộng các giá trị tương ứng trong vùng tính tổng. Ví dụ: Trong bảng kết quả tuyển sinh trên. Tính tổng số điểm của các thí sinh có số điểm toán từ 9 trở lên. =SUMIF(B3:B10,”>=9”,E3:E10) {Kết quả 44} Hàm COUNTIF: Đếm có điều kiện. Cú pháp: COUNTIF(vùng muốn đếm, điều kiện đếm) Đếm các giá trị thỏa mãn điều kiện trong vùng muốn đếm. Ví dụ : Trong bảng kết quả tuyển sinh trên. Đếm có bao nhiêu điểm 9 trong kỳ thi ? =COUNTIF(B3:D10,”=9”); {kết quả 4} Lưu ý : Có thể lấy ô nào đó làm điều kiện, khi đó địa chỉ của ô không cần để trong ngoặc kép. Ví dụ : =COUNTIF(B3:D10,D3) {kết quả 4} Vì ô D3 = 9, nên có thể lấy ô này làm điều kiện thay vì phải viết “=9” trong ngoặc kép. Hàm AND(điều kiện 1, điều kiện 2,...): cho giá trị đúng khi mọi điều kiện nêu trong danh sách đều cho trị đúng. Ví dụ: =AND(3>2,5<8) {kết quả TRUE} =IF(AND(B2>8, C3>=5),”yes”,”no”) {kết quả: nếu giá trị ô B2 lớn hơn 8 và ô C3 lớn hơn hoặc bằng 5 thì cho giá trị là “yes”, ngược lại cho giá trị “no”}. 122 Hàm OR(điều kiện 1, điều kiện 2,...): cho giá trị đúng khi có bất kỳ một điều kiện nêu trong danh sách cho trị đúng. Ví dụ: =OR(3>2,5=8) {kết quả TRUE} =OR(1+1=3,2+3=6) {kết quả FALSE} Hàm NOT(điều kiện): cho trị đúng nếu điều kiện sai và cho trị sai nếu điều kiện đúng. c. Các hàm ngày tháng: Nhóm hàm ngày tháng sẽ trả về các giá trị ngày tháng để giúp ta tính toán về thời gian như số ngày, tuần, tháng năm, ... Dữ liệu ngày (Date) là một dạng đặc biệt của dữ liệu số. Dữ liệu ngày thường có dạng M/D/YY, ví dụ : 1/25/02 (ngày 25 tháng 1 năm 2002). Bạn có thể chọn các kiểu dữ liệu ngày, bằng cách dùng lệnh Format | cells, chọn lớp Number, chọn mục Date và sau đó chọn dạng dữ liệu ngày muốn dùng trong khung Type. Có thể chọn mục Custom ở khung Category và nhập dạng cần thiết vào khung Type, chẳng hạn thay đổi dữ liệu kiểu ngày theo Việt Nam (Ngày/Tháng/Năm). Dạng tổng quát của kiểu ngày theo qui định như sau: - d: hiển thị số thực tế của ngày hiện tại. - dd: hiển thị ngày theo 2 số. - ddd: hiển thị ngày theo 3 chữ viết tắt bằng tiếng Anh. - mm: hiển thị tháng theo hai số. - mmm: hiển thị tháng theo 3 chữ viết tắt. - yy: hiển thị hai số sau cùng của năm. - yyyy: hiển thị bốn số của năm. Ví dụ: Định dạng : dd/mm/yyyy Hiển thị : 15/11/2001 123 Hàm WEEKDAY(Dữ liệu kiểu ngày): Cho giá trị thứ (từ 1 đến 7) của dữ liệu kiểu ngày. Ví dụ: =Weekday(“2/9/2001”) {kết quả 1} (Ngày Quốc khánh năm 2001 là ngày chủ nhật) * Giả sử dữ liệu trong ô D3 là ngày 14 tháng 2 năm 2002. =Weekday(d3) {kết quả 5} (Ngày Valetine năm 2002 là thứ năm). Hàm TODAY(): Cho ngày tháng năm hiện hành (lấy ngày theo ngày hệ thống của máy tính). Hàm DAY(Dữ liệu kiểu ngày): Cho giá trị ngày của dữ liệu kiểu ngày. Ví dụ: =DAY("1/11/96") {kết quả 1} Hàm MONTH(Dữ liệu kiểu ngày): Cho giá trị tháng của dữ liệu kiểu ngày. Ví dụ: =MONTH("1/11/96") {kết quả 11} Hàm YEAR(Dữ liệu kiểu ngày): Cho giá trị năm của dữ liệu kiểu ngày. Ví dụ: =YEAR("1/11/96") {kết quả 1996} Hàm DAYS360(ngày bắt đầu, ngày kết thúc): Cho tổng số ngày kể từ ngày bắt đầu đến ngày kết thúc. Ví dụ: giá trị ô B2 là ngày 25/4/2001 và B3 là 2/9/2001. =DAYS360(B2,B3) {kết quả 127} d. Các hàm thống kê: Xem bảng sau : Hàm COUNT(danh sách các trị): cho số các ô chứa giá trị số trong danh sách. Ví dụ: =COUNT(-2,"VTD",5,8) {kết quả 3} =COUNT(D1:D8) {kết quả 7} Hàm COUNTA(danh sách các trị): cho số các ô chứa dữ liệu trong danh sách. Ví dụ : =COUNTA(-2,"VTD",5,8) {kết quả 4} =COUNTA(D1:D8) {kết quả 8} A B C D E F 1 TT Tên Toán Văn DTB Xếp hạng 2 1 Hùng 6 6 6.0 3 2 Hoa 5 5 5.0 4 3 Dũng 7 7 7.0 5 4 Long 6 7 6.3 6 5 An 4 5 4.3 7 6 Hòa 9 9 9.0 8 7 Lộc 8 7 7.7 124 Hàm RANK(x, Danh sách, Thứ tự): xác định thứ hạng của trị x so với các giá trị trong Danh sách. Trị x và danh sách phải là các trị số, nếu không sẽ gây ra lỗi #VALUE!. Trị x phải rơi vào một trong các trị của danh sách, nếu không sẽ gây ra lỗi #N/A!. - Thứ tự: Nếu xếp theo thứ tự giảm (số lớn nhất là hạng nhất) thì giá trị này bằng 0 hoặc không nhập giá trị này; nếu xếp theo thứ tự tăng (số nhỏ nhất là hạng nhất) thì giá trị này là 1. Ví dụ : =RANK(E2,E$2:E$8) {kết quả 5} =RANK(E3,E$2:E$8) {kết quả 6} =RANK(E4,E$2:E$8,1) {kết quả 5} e. Các hàm chuỗi: Hàm LEFT(text, n): cho trị là chuỗi con của chuỗi text được tính từ trái sang phải n ký tự. Ví dụ : =LEFT("ABCD",2) {kết quả : "AB"} Hàm RIGHT(text, n): cho trị là chuỗi con của chuỗi text được tính từ phải sang trái n ký tự. Ví dụ : =RIGHT("ABCD",2) {kết quả : "CD"} Hàm MID(text, n, m): cho trị là chuỗi con tính từ vị trí n của chuỗi text m ký tự. Ví dụ : =MID(”mùa thu Hà Nội”, 4, 3) {kết quả : “thu”} Hàm LEN(text): cho độ dài của chuỗi text. Ví dụ : =LEN("ABCD") {kết quả là 4} Hàm LOWER(text): chuyển chuỗi text thành chữ thường. Ví dụ : =LOWER("TRUNG TAM") {kết quả : “trung tam”} Hàm UPPER(text): chuyển chuỗi text thành chữ hoa. Ví dụ : =UPPER("Trung tam") {kết quả : “TRUNG TAM”} Hàm PROPER(text): chuyển các ký tự đầu từ của chuỗi text thành chữ hoa. Ví dụ : =PROPER("trung tam”) {kết quả : “Trung Tam”} Hàm TRIM(text): cắt bỏ ký tự trắng ở đầu và cuối chuỗi text. Ví dụ : =TRIM(” Hà Nội ”) {kết quả : “Hà Nội”} Ví dụ : Xem bàng tính sau: 125 Hàm FIND(find_text, text, start_num): cho vị trí của chuỗi con find_text trong chuỗi text bắt đầu tìm từ vị trí start_num, nếu bỏ qua start_num nó cho giá trị bằng 1. Hàm này phân biệt chữ HOA và thường. Ví dụ : =FIND("e","MS. Excel 6.0") {kết quả : 8} =FIND("E","MS. Excel 6.0") {kết quả : 5} =FIND("Excel","MS. Excel 6.0") {kết quả : 5} Giả sử giá trị trong ô A1 là chuỗi “Lê Văn Hùng” =LEFT(A1;Find(" ";A1)-1) {kết quả: “Lê”} Hàm SEARCH(find_text, text, start_num) Tương tự hàm FIND nhưng không phân biệt chữ HOA và thường. Hàm SUBSTITUTE(text, oldtext, newtext, instance): thay thế newtext vào vị trí oldtext trong text ở lần xuất hiện instance (nếu không có đối số này sẽ thay thế ở mọi vị trí). Ví dụ: =SUBSTITUTE("Hãy xem xem","xem","nhìn";1) {Hãy nhìn xem} =SUBSTITUTE("Hãy xem xem";"xem";"đây";2) {Hãy nhìn xem} f. Các hàm tìm kiếm: Hàm VLOOKUP(x, Bảng, Cột tham chiếu, Cách dò): dò tìm trị x ở cột bên trái của bảng, khi tìm có thì lệch qua bên phải đến Cột tham chiếu để lấy trị trong ô ở đó ứng với vị trí của x. - Bảng: là một khối các ô, thường gồm nhiều hàng và nhiều cột. Cột bên trái luôn luôn chứa các trị để dò tìm, các cột khác chứa các trị tương ứng để tham chiếu. - Cột tham chiếu: là thứ tự của cột (tính từ trái của bảng trở qua), cột đầu tiên của bảng là cột 1. - Cách dò: là số 0 hoặc số 1. Ngầm định là 1. Nếu cách dò là 1: Danh sách ở cột bên trái của bảng phải xếp theo thứ tự tăng dần. Nếu trị dò x nhỏ hơn phần tử đầu tiên trong danh sách, hàm cho trị là #N/A (Not Available: bất khả thi) Nếu trị dò lớn hơn phần tử cuối cùng trong danh sách, xem như tìm thấy ở phần tử cuối cùng. Nếu trị dò x đúng khớp với một phần tử trong danh sách (không phân biệt chữ hoa hay thường nếu là chuỗi), đương nhiên tìm thấy ở tại phần tử đó, và cho trị là trị của ô nằm trong cột tham chiếu cùng hàng với phần tử này. Nếu cách dò là 0: Danh sách ở cột bên trái của bảng không cần phải xếp theo thứ tự. Nếu trị dò x không đúng khớp với bất kỳ phần tử nào trong danh sách (không phân biệt chữ hoa hay thường nếu là chuỗi), hàm cho trị là #N/A (Not Available: bất khả thi). 126 Chỉ khi nào trị dò x đúng khớp với một phần tử trong danh sách (không phân biệt chữ hoa hay thường nếu là chuỗi), mới cho trị là trị của ô nằm trong cột tham chiếu cùng hàng với phần tử này. Ví dụ: Tính lương cho ba loại công lao động khác nhau, biết rằng số tiền cho từng loại công lao lao động là : - loại A : 20000 đồng/công. - loại B : 10000 đồng/công. - loại C : 5000 đồng/công. - Tiền lương được tính theo công thức : TiềnLương = SốCông x SốTiềnMộtCông Bạn thực hiện như sau : - Tạo bảng gồm hai cột : cột A chỉ các loại công lao động (A, B, C) và cột B chỉ số tiền công tương ứng. - Chọn ô E6, nhập công thức : - =VLOOKUP(C6,$A$1:$B$3,2,1)*D6 - Copy công thức này xuống các ô E7, E8, ... Ta có kết quả: A B C D E 1 A 20000 2 B 10000 3 C 5000 4 5 TT HỌ TÊN LOẠI SỐ CÔNG TIỀN 6 1 Tuấn A 28 560000 7 2 Anh C 25 125000 8 3 Hùng D 17 85000 9 4 Thanh B 27 270000 Nếu công thức ở ô E6 là: =VLOOKUP(C6,$A$1:$B$3,2,0)*D6 thì ta có kết quả: TT HỌ TÊN LOẠI SỐ CÔNG TIỀN 1 Tuấn A 28 560000 2 Anh C 25 125000 3 Hùng D 17 #N/A 4 Thanh B 27 270000 Chú ý: Trong công thức bạn dùng địa chỉ tuyệt đối $A$1:$B$3 để đảm bảo địa chỉ này không bị thay đổi trong quá trình Copy. Hàm HLOOKUP(x, Bảng, Hàng tham chiếu, Cách dò): Mọi nguyên tắc hoạt động của hàm HLOOKUP (Horizontal Look Up) này giống như hàm VLOOKUP (Vertical Look Up), chỉ khác là hàm 127 VLOOKUP dò tìm ở cột bên trái, tham chiếu số liệu ở các cột bên phải, còn hàm HLOOKUP dò tìm ở hàng trên cùng, tham chiếu số liệu ở các hàng phía dưới. Ví dụ : Công thức ở ô C3: =HLOOKUP(B3,$B$7:$D$8,2,0) {Máy tính} 2. Chọn hàm mẫu từ hệ thống menu Trong Excel có khoảng hơn 300 hàm thuộc nhiều lĩnh vực khác nhau. Một hàm mẫu có thể nhập vào ô qua hệ thống menu và hộp thoại bằng cách : Đặt con trỏ tại ô cần nhập hàm mẫu. Thực hiện lệnh Insert | Function hoặc click biểu tượng trên thanh Toolbar. Xuất hiện hộp thoại Paste Function : Trong hộp thoại Paste Function, tìm nhóm các hàm cần thực hiện trong khung Function Category (ví dụ All) và chọn tên hàm cần thực hiện trong khung Function Name (ví dụ AVERAGE), click OK, xuất hiện hộp thoại mới: 128 Xác định vùng ô để xác định đối số của hàm, click OK, kết quả tính hàm xuất hiện trên ô đã chọn. 5.3. Định dạng dữ liệu số và tiền tệ 1. Định dạng dữ liệu số Sau khi nhập dữ liệu, tính toán,... bạn còn phải trình bày bảng tính sao cho thích hợp với yêu cầu. Khi nhập dữ liệu kiểu số trong Excel, bạn không thể nhập dấu phân cách hàng ngàn và các đơn vị cho số (ví dụ: 100 cái). Nhưng bạn có thể dùng phương pháp định dạng để đưa chúng vào mà Excel vẫn hiểu chúng là dữ liệu kiểu số. Các bước thực hiện để định dạng số: Chọn vùng số muốn định dạng. Thực hiện lệnh Format | Cells. Trong cửa sổ Format Cells, chọn lớp Number. Trong khung Category, click chọn mục Number. - Use 1000 separator (,): Sử dụng dấu phân cách hàng ngàn (dấu phẩy). - Negative numbers: Cách hiển thị số âm (hiển thị dấu trừ, chuyển thành màu đỏ hay để trong ngoặc đơn). 129 2. Định dạng tiền tệ Cũng tương tự như định dạng số nhưng: Trong khung Category ở cửa sổ Format Cells, chọn mục Currency. Chọn dạng tiền tệ trong khung Symbol. Đối với Excel 2002 trở đi, trong khung này có hầu hết các ký hiệu viết tắt của các loại tiền tệ trên thế giới theo tiêu chuẩn quốc tế (tiền tệ của Việt Nam viết tắt là VND). Nếu muốn đưa vào một ký hiệu riêng, bạn chọn mục Custom trong khung Category. Nhập dạng cần thiết trong khung Type, khi nhập các loại đơn vị riêng, phải để trong ngoặc vuông và phải nhập dấu “$” phía trước. Ví dụ : - Nhập định dạng : #,##0 [$đồng] - Số 1000 sẽ hiển thị : 1,000 đồng. Lưu ý: Có thể dùng biểu tượng trên Formatting Toolbar để định dạng một số kiểu dữ liệu số : Biểu tượng để biểu diễn dữ liệu số kiểu tiền tệ. 130 Ví dụ : Biểu tượng để biểu diễn dữ liệu số kiểu phần trăm (%) Ví dụ : Biểu tượng để biểu diễn dữ liệu số kiểu phân nhóm, bắt đầu từ bên phải, 3 chữ số một nhóm, dùng dấu phẩy phân nhóm. Dấu chấm (.) tách phần lẻ thập phân. Ví dụ : Biểu tượng để tăng phần lẻ thập phân một chữ số 0 Ví dụ : Biểu tượng để giảm phần lẻ thập phân một chữ số 0 Ví dụ : 5.4. Trang trí bảng tính 1. Canh lề trong ô Để định dạng vị trí hiển thị dữ liệu trong ô, bạn thực hiện các bước sau: Chọn ô hoặc vùng muốn định dạng. Thực hiện lệnh Format | Cells và click chọn lớp Alignment, sau đó hiệu chỉnh các thông số. 156,456 158,358 156456 158358 156456 158358 156,456 158,358 156,456.00 158,358.00 $156,456.00 $158,358.00 15645600% 15835800% 156,456.00 158,358.00 156,456.0 158,358.0 156,456.0 158,358.0 131 Horizontal: Canh lề theo hàng ngang. General : giữ nguyên dữ liệu như khi nhập vào từ bàn phím. Left : điều chỉnh thẳng mép trái. Center : điều chỉnh giữa ô. Right : Điều chỉnh thẳng mép phải Fill : điền toàn ô bởi các ký tự có trong ô đó. Justify : điều chỉnh thẳng hai bên. Center across selection: điều chỉnh giữa qua một dãy ô. Vertical : Canh lề theo hàng dọc. Top : canh trên. Bottom : canh dưới. Center : canh giữa. Justify : canh đều hai bên. Orientation: Hiệu chỉnh hướng của chữ: Chọn dạng chữ ngang, đứng hoặc nghiêng. Chỉnh độ nghiêng trong khung Degrees. Text control: Kiểm soát chữ. Wrap text : độ rộng cố định, dữ liệu nhập vào tự động dàn qua nhiều dòng. Shink to fit : Hiệu chỉnh vừa bằng ô. Merge Cell : nối các ô đang chọn. Lưu ý: Nếu ô đã được nối, khi click thôi chọn thì ô này sẽ được tách ta như cũ. 2. Tạo các đường kẻ theo vùng ô đã chọn Chọn ô hoặc vùng muốn định dạng. Thực hiện lệnh Format | Cells và click chọn lớp Border. Style : Chọn dạng đường viền. Color : Chọn màu đường viền khung. 132 None : Không có đường viền khung. Outline : Tạo khung xung quanh vùng chọn. Inside : Tạo các đường viền khung bên trong vùng chọn. Có thể click vào các cạnh trong khung Border để tạo các đường viền tương ứng. Lưu ý : Để tạo khung nhanh, bạn có thể sử dụng biểu tượng Borders trên thanh định dạng. 3. Định dạng nền dữ liệu Chọn ô hoặc vùng muốn định dạng. Thực hiện lệnh Format | Cells và click chọn lớp Patterns. Chọn màu trong bảng Color. Có thể mở bảng Patterns để chọn các dạng màu khác. Xem mẫu trong khung Sample trước khi click OK để tạo màu. Lưu ý: Có thể chọn màu nền từ biểu tượng Fill Color và chọn màu chữ từ biểu tượng Font Color . 4. Bật tắt lưới ô của bảng tính Lưới ô trong bảng tính giúp cho việc nhìn các ô được rõ ràng, khi in nó không được in ra. Để bật/tắt lưới ô, dùng lệnh : Tools | Options, chọn lớp View, đánh dấu kiểm (hay bỏ chọn) mục Gridlines. 5.5. Sắp xếp và tìm kiếm 1. Sắp xếp Trong Excel có thể sắp xếp số liệu trong một phạm vi được chọn một cách độc lập với các ô ngoài khu vực chọn. Việc sắp xếp có thể thực hiện trên hàng ngang hoặc 133 cột dọc. Excel cho phép sắp xếp dữ liệu tối đa theo ba khóa. Ví dụ, nếu một danh sách lương sắp xếp theo từng đơn vị, trong mỗi đơn vị sắp xếp theo Tên, cùng tên sắp xếp theo Họ thì khóa thứ nhất là dữ liệu cột đơn vị, khóa thứ hai là dữ liệu cột Tên, khóa thứ ba là dữ liệu cột Họ. Các bước để sắp xếp dữ liệu : Chọn vùng dữ liệu muốn sắp xếp. Thực hiện lệnh Data | Sort. Hộp đối thoại sau hiện ra: Chọn tiêu đề cho khóa sắp xếp thứ nhất và thứ tự sắp xếp (Ascending : tăng, Descending : giảm). Chọn tiêu đề cho khóa sắp xếp thứ hai và thứ tự sắp xếp nếu có. Chọn tiêu đề cho khóa sắp xếp thứ ba và thứ tự sắp xếp nếu có. Nếu trong vùng chọn để sắp xếp có dòng đầu tiên là dòng tiêu đề thì bạn phải chọn mục Header row để dòng tiêu đề không bị đảo lộn. Nếu dòng đầu tiên không phải là dòng tiêu đề thì bạn phải chọn mục No Header row. Click nút OK để bắt đầu sắp xếp. Chú ý: muốn chỉ định sắp xếp theo hàng hay sắp xếp theo cột, thì click nút Options ... Hộp đối thoại sau hiện ra : - Sort top to bottom : sắp xếp theo hàng. - Sort left to right : sắp xếp theo cột. 2. Tìm kiếm Nếu bạn có một bảng dữ liệu khoảng vài trăm dòng thì chức năng tìm kiếm này rất cần khi bạn muốn tìm đến các dữ liệu cần thiết trong bảng tính. Muốn tìm dữ liệu ở dòng hay cột nào, bạn click chọn dòng hay cột đó để tìm nhanh hơn và thực hiện như sau: Thực hiện lệnh Edit | Find. Xuất hiện hộp thoại : 134 Nhập nội dung muốn tìm trong khung Find What. Để chọn phương pháp tìm, click vào Options>> xuất hiện hộp thoại: từ đó lựa chọn cách tìm thích hợp. Khi muốn tìm kiếm và thay thế bằng từ khác click nút Replace, xuất hiện hộp thoại: Nhập từ muốn thay thế trong khung Replace with. Click nút Replace để thay từ vừa tìm thấy, Replace All để thay thế tất cả các từ tìm được. Khi thực hiện xong, click nút Close để đóng hộp thoại. 5.6. Biểu đồ bảng tính Biểu đồ là đồ thị biểu diễn dữ liệu bảng tính. Các biểu đồ làm cho dữ liệu của bảng tính phức tạp trở thành trực quan và dễ hiểu hơn. Biểu đồ biến đổi dữ liệu từ các hàng và các cột trên bảng tính thành hình khối, đồ thị ... Biểu đồ có các trục, trên đó có các giá trị tỉ lệ tương ứng với giá trị dữ liệu trên bảng tính. 1. Các bước tạo biểu đồ Xét bảng tính dưới đây về thu nhập của một đơn vị sản xuất nông nghiệp trên ba ngành nghề: trồng trọt, chăn nuôi và thủ công từ năm 1998 đến 2002. 135 Tạo đồ thị dạng cột từ dữ liệu của cột trồng trọt. Các bước thực hiện như sau: Chọn dữ liệu trong cột Trồng trọt, kể cả tiêu đề cột (Vùng B1:B6). Thực hiện lệnh Insert | Chart hoặc click biểu tượng Chart Wizard trên Standard Toolbar. Hiển thị hộp thoại Chart Wizard - Step 1 of 4 - Chart Type. Chọn loại biểu đồ trong khung Chart type và chọn kiểu tương ứng trong khung Chart sub-type, chẳng hạn ta chấp nhận đồ thị dạng cột được gợi ý theo mặc định, tiếp đến click Next để hiển thị hộp thoại Chart Wizard – Step 2 of 4 – Chart Source Data. Màn hình này cho biết dạng của đồ thị sẽ hiện, chỉ ra các ô được gộp vào vùng dữ liệu của đồ thị. 136 Chọn lớp Series, hộp thoại của lớp này chỉ ra dãy các ô tạo thành dữ liệu và tựa cột. Click vào biểu tượng ở bên phải của vùng Category (X) axis labels làm hộp thoại thu lại để có thể nhìn thấy bảng tính. Lựa cột chứa các năm trong bảng tính để đặt năm như là các nhãn của trục X thay thế cho các nhãn đơn giản mặc định của Excel. Click một lần nữa lên biểu tượng ở bên phải của vùng Category (X) axis labels để khôi phục lại hộp thoại Source Data. Chú ý rằng các năm được chèn vào như là nhãn của trục X. Click nút Next để hiển thị hộp thoại Chart Wizard - Step 3 of 4 - Chart Options. Trong vùng Char Title gõ vào “Thu nhập về Trồng Trọt”, sau đó nhập thuyết minh cho các trục X và Y. Click nút Next để đến màn hình cuối cùng Chart Wizard - Step 4 of 4 - Chart Location. Hộp thoại này cho bạn quyết định hoặc là sẽ chèn đồ thị vào trong bảng tính hiện hành, hoặc để đồ thị như là một đối tượng riêng lẻ ở một Sheet bên trong Workbook hiện hành. 137 Đối với ví dụ này bạn sẽ đặt đồ thị trong Sheet hiện hành, click nút Finish để hoàn tất vẽ đồ thị. Cuối cùng bạn được đồ thị và có thể kéo nó đến vị trí bất kỳ trên Sheet hiện hành. Đối với dữ liệu của các cột Chăn nuôi, Thủ công bạn cũng có thể lập đồ thị dạng cột như trên. Bây giờ bạn muốn vẽ đồ thị bao gồm dữ liệu của ba nghề chung lại với nhau để đễ dàng so sánh. Khi đó, bạn lựa chọn dữ liệu của cả ba cột Trồng trọt, chăn nuôi và Thủ công (vùng B1:D6), sau đó thực hiện các bước như trên, kết quả đồ thị như sau : 2. Hiệu chỉnh biểu đồ Để hiệu chỉnh biểu đồ, trước hết bạn chọn biểu đồ cần hiệu chỉnh bằng cách click vào biểu đồ, khung viền biểu đồ sẽ hiện 8 núm ở 4 góc và điểm giữa các cạnh. Di chuyển : Drag kéo phạm vi biểu đồ đến vị trí mới. Thay đổi kích thước : Drag các nút trên khung viền để thay đổi kích thước. Right click lên biểu đồ, xuất hiện menu dọc, chọn Chart Options, xuất hiện hộp thoại Chart Option. 138 Hộp thoại này có nhiều lớp dùng để thay đổi, như thay đổi tiêu đề đồ thị, thuyết minh các cột, các đường kẻ ngang dọc, chú thích, các nhãn ... Ví dụ, hiệu chỉnh lại biểu đồ trên như sau : BiÓu ®å Tæng hîp 0 1000 2000 3000 4000 5000 1998 1999 2000 2001 2002 Trong trot Chan nuoi Thu cong Hiệu chỉnh các thành phần bên trong của biểu đồ : Xóa một thành phần : Click vào thành phần, gõ Delete. Thay đổi kích thước một thành phần : Click lên thành phần, xuất hiện 8 nút hình vuông, drag các nút để thay đổi kích thước. Di chuyển thành phần sang vị trí khác : Drag lên thành phần. Định dạng lại một thành phần : Double click vào thành phần, xuất hiện hộp thoại gồm nhiều lớp, tiến hành định dạng lại theo hướng dẫn của hộp thoại. 5.7. Cơ sở dữ liệu trên bảng tính 1. Khái niệm cơ bản về cơ sở dữ liệu (Database) Cơ sở dữ liệu là tập hợp thông tin, dữ liệu được tổ chức theo cấu trúc dòng và cột để có thể liệt kê, truy tìm, xóa, rút trích ... những dòng dữ liệu thỏa mãn một tiêu chuẩn nào đó nhanh chóng. Để thực hiện các thao tác này bạn phải tạo ra các vùng Database, Criteria và Extract. 139 Vùng Database (vùng Cơ sở dữ liệu): Gồm ít nhất hai dòng. Dòng đầu tiên chứa các tiêu đề cột, gọi là tên trường (Field Name) của CSDL. Tên các trường phải là dữ kiện kiểu chuỗi và không được trùng lặp. Các dòng còn lại chứa dữ liệu, mỗi dòng gọi là một bản ghi (Record) của CSDL. Vùng Criteria (vùng tiêu chuẩn): Chứa điều kiện để tìm kiếm, xóa, rút trích,... Vùng này gồm ít nhất hai dòng. Dòng đầu chứa tiêu đề. Các dòng còn lại chứa điều kiện. Vùng Extract (vùng trích dữ liệu) : Chứa các bản ghi của vùng Database thỏa điều kiện của vùng tiêu chuẩn. Vùng Extract cũng có dòng đầu tiên chứa các tiêu đề muốn rút trích. Chỉ cần dùng vùng này trong trường hợp thực hiện thao tác rút trích, còn các thao tác tìm kiếm, xóa,... không cần dùng đến vùng này. Ví dụ : A B C D E F G H 1 STT HỌ LÓT TÊN CHỨC VỤ SỐ CON LƯƠNG 2 1 Hồ Văn Dũng TP 2 240000 CHỨC VỤ 3 2 Lê Thị Hoa NV 1 160000 TP 4 3 Nguyễn Bính NV 1 150000 GD 5 4 Trần Hòa GD 3 300000 6 5 Hồ Văn Long NV 2 160000 7 8 9 STT HỌ LÓT TÊN CHỨC VỤ 10 1 Hồ Văn Dũng TP 11 4 Trần Hòa GD Ở đây: vùng A1:F6 là vùng Database, vùng H2:H4 là vùng Criteria, vùng A9:D11 là vùng Extract 2. Thao tác lọc, rút trích và xóa a) Trích lọc dữ liệu: Trích lọc dữ liệu tức là bạn có thể lấy ra, loại bỏ hoặc dấu đi những dòng dữ liệu không cần thiết, chỉ giữ lại các dòng dữ liệu thỏa mãn một điều kiện nào đó. Trong thao tác này chỉ cần tạo trước vùng Database và vùng Criteria, không cần tạo trước vùng Extract. Các bước thực hiện trích lọc : Di chuyển con trỏ ô vào vùng Database. Thực hiện lệnh Data | Filter | Advanced Filter. Hộp thoại sau hiện ra : Trong mục Action, chọn “Filter the List, in-place ở mục Action” (hiển thị những bản ghi thỏa mãn điều kiện ngay trên vùng CSDL). 140 Xác định địa chỉ vùng Database vào hộp List Range: gõ trực tiếp, hoặc có thể click vào biểu tượng ở bên phải hộp List Range, hộp thoại thu nhỏ lại để bạn thấy rõ bảng tính, dùng chuột chọn toàn bộ vùng Database, click lại biểu tượng ở bên phải hộp List Range để trở về lại hộp thoại Advanced Filter đầy đủ, kết quả địa chỉ của vùng Database hiện trong hộp List range. Xác định địa chỉ vùng Criteria vào hộp Criteria range : gõ trực tiếp, hoặc có thể click vào biểu tượng ở bên phải hộp Criteria range, hộp thoại thu nhỏ lại để bạn thấy rõ bảng tính, dùng chuột chọn toàn bộ vùng Criteria, click lại biểu tượng ở bên phải hộp Criteria range để trở về lại hộp thoại Advanced Filter đầy đủ, kết quả địa chỉ của vùng Criteria hiện trong hộp List range. Nếu chọn “Unique record only”, dữ liệu lọc ra nếu có những dòng trùng nhau sẽ trình bày chỉ một dòng. Gõ Enter hay click OK, các mẫu tin không thỏa điều kiện trong vùng Criteria sẽ bị dấu đi, chỉ trình bày các mẫu tin thỏa mãn điều kiện. Chú ý : Sau đó muốn liệt kê lại toàn bộ nội dung các mẫu tin của CSDL, dùng lệnh Data | Filter | Show All. Ví dụ: Trong bảng tính ở (5.7.1), sau khi trích lọc ta có bảng sau : b) Rút trích các bản ghi thỏa mãn điều kiện sang vùng khác: Trong thao tác này, cần tạo trước vùng Database, vùng Criteria và vùng Extract. Các bước thực hiện : Di chuyển con trỏ ô vào vùng Database. Thực hiện lệnh Data | Filter | Advanced Filter. Hộp đối thoại như chức năng trích lọc hiện ra. Trong mục Action, chọn “Copy to Another Location ở mục Action”. Xác định địa chỉ vùng Database vào hộp List Range. Xác định địa chỉ vùng Criteria vào hộp Criteria Range. Xác định địa chỉ các tiêu đề của vùng Extract vào hộp Copy to. Bạn chỉ cần chọn ô làm góc trên bên trái của vùng Extract. Gõ Enter hay click OK, các mẫu tin thỏa điều kiện trong vùng Criteria sẽ được chép vào vùng Extract. Ví dụ: Trong bảng tính ở (5.7.1), sau khi rút trích theo các địa chỉ trong hộp thoại Advanced Filter trên, ta có : 141 c) Xóa các bản ghi thỏa mãn điều kiện: Trong thao tác này chỉ cần tạo trước vùng Database và vùng Criteria, không cần tạo trước vùng Extract. Thực hiện các bước như thao tác trích lọc. Sau khi trong vùng CSDL liệt kê các các mẫu tin thỏa mãn điều kiện trong vùng Criteria, chọn các dòng này và thực hiện lệnh Edit | Delete Row. Thực hiện lệnh Data | Filter | Show All để trình bày trở lại các mẫu tin bị dấu đi. 3. Các dạng vùng tiêu chuẩn a) Tiêu chuẩn số: Ô điều kiện có kiểu số. Ví dụ: Tiêu chuẩn SỐ CON bằng 2: b) Tiêu chuẩn chuỗi: Ô điều kiện có kiểu chuỗi. Ví dụ : Tiêu chuẩn chức vụ là "GD": Trong ô điều kiện có thể chứa ký tự đại diện ? hoặc * Ví dụ: Tiêu chuẩn TÊN bắt đầu bằng "H": c) Tiêu chuẩn so sánh: Ô điều kiện chứa toán tử so sánh kèm với giá trị so sánh. Các toán tử so sánh gồm: > (lớn hơn) = (lớn hơn hay bằng) <= (nhỏ hơn hay bằng) = (bằng) (khác) Ví dụ : tiêu chuẩn SỐ CON lớn hơn 2: d) Tiêu chuẩn công thức: Ô điều kiện có kiểu công thức. Trong trường hợp có sử dụng tiêu chuẩn này cần lưu ý : Ô tiêu đề của vùng tiêu chuẩn phải là một tiêu đề khác với tất cả các tiêu đề của vùng Database. Trong ô điều kiện phải lấy địa chỉ của ô trong bản ghi đầu tiên để so sánh. Ví dụ: Tiêu chuẩn ký tự đầu tiên của Tên khác "H". Công thức trong ô điều kiện là : =LEFT(C2,1)"H" SỐ CON 2 CHỨC VỤ GD TÊN H* SỐ CON >2 TEN-H TRUE Xuất hiện TRUE/FALSE hay 1/0 142 e) Liên kết tiêu chuẩn: Có thể trích lọc, xóa hay rút trích các mẫu tin trong vùng Database bằng cách dùng các phép toán AND hay OR của nhiều điều kiện khác nhau. Nếu các ô điều kiện khác cột thì biểu thị phép tính AND. Nếu các ô điều kiện khác dòng thì biểu thị phép tính OR. Ví dụ: SỐ CON CHỨC VỤ 1 NV >2 GD 4. Sử dụng hộp thoại Data Form Đặt con trỏ ô nằm trong phạm vi vùng Database. Thực hiện lệnh Data | Form. Hộp thoại sau hiện ra: Trong đó hiện nội dung CSDL theo từng mẫu tin, có thể sử dụng các phím sau : - : đến bản ghi trước/sau bản ghi hiện hành. - Enter: đến bản ghi sau bản ghi hiện hành. - PgUp/PgDn: đến bản ghi đầu/cuối - Tab/Shift-Tab: di chuyển qua lại giữa các trường dữ liệu. Công dụng của những nút lệnh trong hộp đối thoại : - New: nhập bản ghi mới. - Delete: xóa bản ghi hiện hành - Restore: khôi phục dữ liệu vừa nhập/xóa - Find Prev: đến bản ghi trước bản ghi hiện hành. - Find Next: đến bản ghi sau bản ghi hiện hành. - Criteria: nhập điều kiện truy tìm - Close: đóng cửa sổ Data Form 5. Các hàm liên quan đến cơ sở dữ liệu Hàm DSUM(Vùng CSDL, N, Vùng tiêu chuẩn): cho kết quả là tổng dữ liệu số trên cột thứ N của những bản ghi trong vùng CSDL thỏa mãn điều kiện của vùng tiêu chuẩn. N là số thứ tự của cột trong vùng CSDL cần thưc hiện tính tổng, cột đầu tiên được đánh số 1. Ví dụ: Tính tổng Lương của những người có Số con bằng 1. =DSUM(A1:F6,6,H2:H3) Đây là vùng tiêu chuẩn thể hiện điều kiện: SỐ CON bằng 1 và CHỨC VỤ là “NV” hay SỐ CON lớn hơn 2 và CHỨC VỤ là “GD”. 143 ở đây vùng tiêu chuẩn: H 2 3 Hàm DMAX(Vùng CSDL, N, Vùng tiêu chuẩn): cho kết quả là giá trị lớn nhất trên cột thứ N của những bản ghi trong vùng CSDL thỏa mãn điều kiện của vùng tiêu chuẩn. Ví dụ: Tính giá trị Lương lớn nhất của những người có một con. =DMAX(A1:F6,6,H2:H3) ở đây vùng tiêu chuẩn: H 2 3 Hàm DMIN(Vùng CSDL, N, Vùng tiêu chuẩn): cho kết quả là giá trị nhỏ nhất trên cột thứ N của những bản ghi trong vùng CSDL thỏa mãn điều kiện của vùng tiêu chuẩn. Ví dụ: Tính giá trị Lương thấp nhất của những người có một con. =DMIN(A1:F6,6,H2:H3) ở đây vùng tiêu chuẩn : H 2 3 Hàm DCOUNT(Vùng CSDL, N, Vùng tiêu chuẩn): cho kết quả là số ô chứa giá trị số trên cột thứ N của những bản ghi trong vùng CSDL thỏa mãn điều kiện của vùng tiêu chuẩn. Ví dụ: Tính số người có Số con bằng 1. =DCOUNT(A1:F6, 5, H2:H3) ở đây vùng tiêu chuẩn: H 2 3 Hàm DCOUNTA(Vùng CSDL, N, Vùng tiêu chuẩn): cho kết quả là số ô chứa dữ liệu trên cột thứ N của những mẫu tin trong vùng CSDL thỏa mãn điều kiện của vùng tiêu chuẩn. Ví dụ: Tính số người có Số con bằng 2. =DCOUNTA(A1:F6,3,H2:H3) ở đây vùng tiêu chuẩn : H 2 3 Hàm DAVERAGE(Vùng CSDL,N,Vùng tiêu chuẩn): cho kết quả là giá trị trung bình trên cột thứ N của những mẫu tin trong vùng CSDL thỏa mãn điều kiện của vùng tiêu chuẩn. Ví dụ: Tính trung bình Lương của những người có Số con >= 2 =DAVERAGE(A1:F6,6,H2:H3) ở đây vùng tiêu chuẩn : H 2 3 SỐ CON 1 SỐ CON 1 SỐ CON 1 SỐ CON 1 SỐ CON 2 SỐ CON >=2 144 6. Tổng hợp số liệu theo nhóm: Subtotal Trên một CSDL thường có nhu cầu tổng hợp số liệu theo nhóm, ví dụ theo nhóm hàng, theo đơn vị, theo ngày,... Tính năng Subtotal của Excel dùng để phân tích các nhóm dữ liệu đồng thời chèn vào cuối mỗi nhóm những dòng thống kê, tính toán và một dòng tổng kết ở cuối phạm vi. Ví dụ: Giả sử ta có CSDL sau: Các bước thực hiện: a. Sắp xếp CSDL theo khóa là cột cần thực hiện tổng hợp thống kê. Ở đây, chẳng hạn ta tổng hợp theo Loại mặt hàng. Quét chọn khối A2:F15. Thực hiện lệnh Data | Sort. Xuất hiện hộp thoại Sort : hộp Sort By: chọn LOAI, Ascending. ở mục My list has: chọn Header Row. Xong click OK hoặc gõ Enter, kết quả có danh sách sau: 145 b. Đặt con trỏ vào vùng CSDL. Chọn lệnh Data | Subtotals, hộp thoại sau hiện ra: Trong hộp At each change in: chọn LOAI (vùng tin cần tạo nhóm tổng hợp) Trong hộp Use function : chọn hàm SUM (chọn hàm cần tính toán/thống kê) Trong hộp Add subtotal to : chọn SOLG và TRIGIA (chọn những trường cần tính toán/thống kê) Click OK hoặc gõ Enter. 146 Kết quả ta có bảng sau trên màn hình: Chú ý: Mé trái của bảng sẽ thấy các nút điều khiển thứ bậc: jNếu click vào nút số 1, Database chỉ còn mỗi một dòng Grand Total; nếu click vào nút số 2, Database chỉ còn lại các dòng Total và một dòng Grand Total ở cuối. Muốn hủy bỏ tính năng Data | Subtotal trong CSDL vừa làm, thực hiện lệnh Data | Subtotals, rồi chọn Remove All. 5.8. In ấn 1. Định dạng trang giấy in Thực hiện lệnh File | Page Setup, hộp thoại sau hiện ra: a. Chọn lớp Page: để định chế độ in ngang hay in dọc, chọn loại giấy in... Trong Oriention : chọn Portrait để in dọc, Lanscape để in ngang Trong Scaling : chọn tỉ lệ in. Nên giữ 100% 147 normal size. Trường hợp chỉ thiếu vài dòng có thể chọn Fit to 1 page lúc đó Excel sẽ ép lại cho vừa một trang. Trong Page Size : chọn kích cỡ giấy in. Trong Print Quality : chọn chất lượng in, càng nhiều dpi (dot per inche) nét chữ càng sắc sảo. b. Chọn lớp Margins: để canh lề. Ý nghĩa các mục trong hộp đối thoại như sau: Top : lề trên, Bottom : lề dưới, Left : lề trái, Right : lề phải. Header: khoảng cách từ mép trên trang giấy đến mép trên của phần Header. Footer: khoảng cách từ mép dưới trang giấy đến mép dưới của phần Footer. Center on Page : chọn/bỏ chọn canh giữa. Horizontally: canh giữa trang theo chiều ngang. Vertically : canh giữa trang theo chiều dọc. c. Chọn lớp Header/Footer : để định tiêu đề đầu trang/cuối trang. 148 Excel luôn luôn in một dòng trên đỉnh trang gọi là dòng đầu trang (Header) và in một dòng dưới đáy trang gọi là dòng chân trang (Footer). Mặc nhiên Excel sẽ in tên bảng tính trên Header và in số thứ tự trang dưới Footer. Việc này thường không thích hợp với chúng ta; do đó, để không in hoặc in nội dung khác ở Header và Footer bạn chọn mục Custom Header... và Custom Footer... để điều chỉnh lại. Cả hai đều dẫn đến một hộp thoại gần như nhau, trong đó ngoài các hướng dẫn còn có các biểu tượng để chọn, Ví dụ : chọn Custom Header... hộp thoại sau xuất hiện: Nội dung nhập vào Header hoặc Footer có thể trình bày một trong ba khu vực : trái, giữa, phải; muốn xuống dòng gõ Enter. ý nghĩa các biểu tượng: d. Chọn lớp Sheet: để tắt những lằn phân cách giữa các ô và khai báo vùng chọn in ... Chọn Chèn số Chèn tổng số Chèn Chèn Chèn tên tập tin Chèn tên bảng tính 149 Print Area : Nhập địa chỉ vùng muốn in trên bảng tính hiện hành. Print title : Xác định dòng hay cột in lặp lại trên mỗi trang. Gridlines : Bật/tắt những lằn phân cách giữa các ô. Thông thường tắt chế độ này. Page Order (thứ tự trang) : không nên thay đổi. Down, then Across: in từ trên xuống dưới, qua phải. Across, then Down: từ trái sang phải, xuống dưới. 2. In ra giấy Trước khi in ra giấy bạn nên xem thử việc phân bố trong một trang hoặc các trang xem vừa ý chưa bằng lệnh File | Print Preview hoặc click vào biểu tượng trên Standard Toolbar. Để in bảng tính ra giấy, bạn thực hiện lệnh File | Print (hoặc gõ Ctrl+P) Xuất hiện hộp thoại Print: Trong Find What: Selection : in vùng được chọn. Active Sheet(s) : in bảng tính hiện hành. Entire Workbook : in toàn bộ bảng tính có chứa dữ kiện trong Workbook. Copies: số bản in Print Range: phạm vi in All : in tất cả Page(s) From : ..... To : in từ trang .... đến trang .... Ô kiểm Print to File: nếu được chọn thì in ra tập tin, trái lại thì in ra máy in. Click nút OK để in. Lưu ý : Để in toàn bộ bảng tính một lần theo kiểu máy in định sẵn, bạn có thể click biểu tượng trên Standard Toolbar. 150 Bài thực hành 1 1. Khởi động Excel. 2. Thử di chuyển trong bảng tính. Nhập dữ liệu kiểu số, kiểu chuỗi, kiểu công thức. 3. Xóa toàn bộ bảng tính (Hướng dẫn: chọn vùng có dữ liệu, gõ phím Del). 4. Lập bảng tính sau : Yêu cầu : - Dữ liệu ở các cột A, B, C, D do học viên nhập vào. Tên các tựa đề : STT, Tên mặt hàng ... và dữ liệu ở mục tên mặt hàng được canh giữa ô. - Dữ liệu ở mục thành tiền tính theo công thức : Thành tiền = Số lượng * Đơn giá. - Dòng 8: cộng số tiền ở cột Thành tiền (từ ô E3 đến ô E7) đưa vào ô E8. - Dùng chức năng sửa dữ liệu thay đổi giá trị ô C6 thành 12. Quan sát giá trị ô E6 và ô E8 sau khi sửa đổi. - Lưu bảng tính với tên TH-HANH1.XLS. Hướng dẫn: thực hiện lệnh File | Save as hoặc click biểu tượng 151 Bài thực hành 2 Khởi động Excel rồi tạo bảng tính sau: Yêu cầu : Dữ liệu ở các cột A, B, C, D, E do học viên nhập vào. Dữ liệu ở mục PCCV (Phụ cấp chức vụ) được tính như sau: Nếu chức vụ "GD" được phụ cấp 50000; Nếu chức vụ "PG" hoặc "TP" được phụ cấp 40000; Nếu chức vụ "NV" và (NGAYCONG > 25) được phụ cấp 15000; Ngoài ra không có phụ cấp chức vụ. Dữ liệu ở mục LUONGTHANG được tính như sau: LUONGTHANG = LUONGNGAY * NGAYCONG Nhưng nếu (NGAYCONG > 25) thì mỗi ngày dư được tính gấp đôi. Dữ liệu ở mục TAMUNG và CONLAI được tính như sau : TAMUNG = (LUONGTHANG+PCCV)/3*2 và tính tròn đến hàng ngàn. CONLAI = LUONGTHANG+PCCV-TAMUNG. Dòng 12: cộng số tiền ở ba cột LUONGTHANG, TAMUNG, CONLAI. Dòng 14, 15, 16 tính Bình quân, Cao nhất, Thấp tương ứng với ba cột LUONGTHANG, TAMUNG, CONLAI. Lưu trử bảng tính lần thứ nhất trên đĩa với tên LG10.XLS Chép thêm bảng tính trên vào khu vực bắt đầu từ ô A20 và sửa lại tiêu đề của bảng lương là: BẢNG LƯƠNG THÁNG 11/1995 Trong BẢNG LƯƠNG THÁNG 11/1995: Bổ sung thêm hai nhân viên sau vào ngay sau tên Trần Trung Trực: 1) Nguyễn Thị Hoa, Chức vụ: PP, Lương ngày: 10000, Ngày công: 25. 2) Trần Văn Tiến, Chức vụ: NV, Lương ngày: 10000, Ngày công: 27. Xóa nhân viên Trần Đức Tâm. Lưu bảng tính trên đĩa lần cuối cùng và thoát khỏi Excel. 152 Bài thực hành 3 Dùng Excel tạo bảng tính theo mẫu sau và lưu vào đĩa với tên BAI3.XLS : A B C D E F G H I J 1 Công ty Điện Lực 2 Đơn giá 3 BẢNG CHIẾT TÍNH TIỀN ĐIỆN Trong Đ.Mức 1000 4 Tháng 11/2000 Vượt Đ.Mức 1500 5 TT Họ Tên Loại hộ Số Cũ Số mới KW T.Thụ Định Mức Vượt Đ.Mức Thành tiền Tỷ lệ (%) 6 1 Ngọc Lan SX 2546 2680 7 2 Lê Hùng KD 1254 1345 8 3 Từ Ngọc DV 2344 2390 9 4 Lê Tuấn KD 1900 1989 10 5 Anh Hoa DV 1678 1785 11 6 Trần Lan SX 1298 1312 12 7 Đức An KD 1324 1370 13 8 Hồ Đức SX 1222 1402 14 Tổng cộng 15 Trung bình 16 Cao nhất 17 Thấp nhất Các yêu cầu: Cột F (Số KW tiêu thụ) được tính: Số KW T.Thụ = SốMới - SốCũ Cột G (Định mức) được tính: Nếu loại hộ là "SX" thì Định Mức là 120; Nếu loại hộ là "KD" thì Định Mức là 100; Nếu loại hộ là "DV" thì Định Mức là 80. Cột H (Vượt định mức) được tính : Nếu (Số KW T.Thụ - Đ.Mức) < 0 thì Vượt Định Mức = 0. Nếu (Số KW T.Thụ - Đ.Mức) >= 0 thì Vượt Định Mức = Số KW T.Thụ - Đ.Mức. Cột I (Thành tiền) được tính : Nếu (Số KW T.Thụ <= Đ.Mức) thì Thành Tiền = Số KW T.Thụ * ĐơnGiáTrongĐịnhMức Nếu (Số KW T.Thụ > Đ.Mức) thì Thành Tiền = (ĐơnGiáTrongĐịnhMức * ĐịnhMức) + (ĐơnGiáVượtĐịnhMức * VượtĐịnhMức) Cột J (Tỷ lệ %) được tính: Lấy từng thành phần của cột Thành Tiền đem chia cho Tổng số của cột Thành tiền (ô I14) rồi đưa về dạng %. Tính Tổng cộng, Giá trị Trung bình, Cao nhất, Thấp nhất cho cột F, G, H, I. Định dạng số cho cột Thành tiền có dấu phẩy phân cách hàng ngàn và một chữ số phần thập phân. Copy Toàn bộ bảng CHIẾT TÍNH TIỀN ĐIỆN (vùng A5:J17) xuống vùng khác bắt đầu từ ô A20, rồi sắp xếp bảng mới Copy theo thứ tự tăng dần của cột Thành tiền. Đóng cửa sổ bảng tính và thoát khỏi Excel. 153 Bài thực hành 4 Dùng Excel tạo bảng tính theo mẫu sau và lưu vào đĩa với tên BAI4.XLS : 154 Các yêu cầu : Nhập dữ liệu vào các cột A, B, C, D, E, F, G, H, I, J, K. Trong đó: Mục Họ Và Tên được ghi ở hai cột B và C. Mục Ngày sinh (cột D) được ghi theo dạng dd/mm/yy (kiểu Việt). Mục XL H.Tập (cột F) ghi kết quả học tập cuối khóa. Kẻ khung cho bảng tính. Xác định dữ liệu cho các mục Điểm UT, Điểm TB, Vị thứ, Kết quả. Công thức ở ô L7 (Điểm UT) : =VLOOKUP(F7,$M$22:$N$24,2) Công thức ở ô M7 (Điểm TB) : =(G7*2+H7*2+I7+J7+K7+L7)/7 (Toán và văn có hệ số 2, có cộng thêm điểm ưu tiên) Công thức ở ô N7 (Vị thứ) : =RANK(M7,$M$7:$M$16) Công thức ở ô O7 (Kết quả): =IF(M7>=8,"Giỏi",IF(M7>=6.5,"Khá",IF(M7>=5,"Trung bình","Yếu"))) Sắp xếp bảng theo mục Vị thứ (Khối sắp xếp là B6:O16). Điền số thứ tự vào mục STT. Copy vùng L7: O7 ở dòng 7 sang các dòng còn lại. Công thức ở ô O4 : =TODAY() → Hàm cho ngày hiện tại của hệ thống. Ghi bảng tính lên đĩa với tên KQTN.XLS Copy toàn bộ bảng kết quả tốt nghiệp (vùng A1:O19) xuống vùng khác bắt đầu từ ô A28 rồi sắp xếp bảng theo yêu cầu sau : Ưu tiên 1 : theo thứ tự tăng dần ABC của Tên Học sinh . Ưu tiên 2 : theo thứ tự tăng dần ABC của Họ lót Học sinh. và ở phần tiêu đề: sửa lại là: KẾT QUẢ THI TỐT NGHIỆP KHỐI LỚP 12 KẾT QUẢ THI TỐT NGHIỆP (Sắp xếp theo Tên-Họ) 155 Bài thực hành 5 1. Lập bảng tính sau và lưu với tên : BAI5.XLS SAN PHAM QUY I Tæ 1 52% Tæ 2 26% Tæ 3 22% DO THI THONG KE 0 20 40 60 80 I II III IV QUY SA N P H A M Tæ 1 Tæ 2 Tæ 3 I II III IV 0 20 40 60 80 SA N P H A M QUY BIEU DO THONG KE Tæ 1 Tæ 2 Tæ 3 TỔNG KẾT SẢN PHẨM QUÝ - NĂM I II III IV Tổ 1 60 50 60 65 Tổ 2 30 40 70 50 Tổ 3 25 30 45 45 2. Lập biểu đồ trên bảng tính để biểu diễn số lượng sản phẩm trong quý I của từng tổ dạng hình tròn không gian ba chiều như sau: 3. Lập biểu đồ theo mẫu sau trên bảng tính để so sánh kết quả sản xuất giữa các tổ qua bốn quý. - Biểu đồ 1: Trong không gian hai chiều. - Biểu đồ 2: Trong không gian ba chiều. 156 Bài thực hành 6 Dùng Excel tạo bảng tính theo mẫu sau và lưu vào đĩa với tên BAI6.XLS : A B C D E F G H I 1 Nhà khách XYZ 2 BẢNG THANH TOÁN 3 Ngày thanh toán : 12/05/95 4 TT Họ lót Tên Ngày đến Số ngày ở Mã Phòng Tiền Thuê Chi phí phục vụ Tiền trả 5 1 Võ Văn Hòa 25/04/95 C02 6 2 Lê Đức Anh 11/05/95 A02 7 3 Đức Ngọc 12/05/95 B05 8 4 Hồ Tuấn 12/04/95 B02 9 5 Võ Thị Hòa 26/04/95 A01 10 6 Ngọc Lan 12/04/95 C01 11 7 Hồ Anh 24/04/95 A06 12 8 Lê Đức 20/03/95 B08 13 Tổng Cộng : 14 15 Bảng Đơn Giá 16 Loại phòng ĐGía Ngày CPhí Phục vụ 17 A 50000 10% 18 B 35000 8% 19 C 20000 5% 20 Các yêu cầu : Nhập dữ liệu cho các cột A,B,C,D,F Xác định dữ liệu cho phần còn lại, biết rằng: Số Ngày ở = Ngày thanh toán - Ngày đến Tiền thuê = Đơn giá ngày tương ứng với loại phòng * Số Ngày ở (Tham khảo bảng đơn giá ở vùng G16:I19) Lưu ý : a. Loại phòng được phân biệt dựa vào ký tự đầu tiên của Mã phòng. b. Nếu số ngày ở = 0 thì Tiền thuê =1/2 * đơn giá ngày tương ứng với loại phòng Chi phí phục vụ = Tiền thuê * Chi phí phục vụ tương ứng với loại phòng (Tham khảo bảng đơn giá ở vùng G16:I19) Tiền trả = Tiền thuê + Chi phí phục vụ Vẽ khung, ấn định kiểu chữ, định dạng số cho thích hợp. Trích trong bảng thanh toán những người có số tiền trả lớn hơn 800000 và liệt kê trên màn hình bắt đầu từ dòng 22. Đóng cửa sổ bảng tính và thoát khỏi Excel. 157 Bài thực hành 7 Dùng Excel tạo bảng tính theo mẫu sau và lưu vào đĩa với tên BAI7.XLS : A B C D E F G 1 CỬA HÀNG THỰC PHẨM ABC 2 Tháng 02/01 3 Ngày tổng kết : 4 Tên hàng Số lượng Đơn giá Thành tiền Tên hàng Đơn giá 5 Gạo 120 Gạo 4000 6 Gạo 50 Nếp 6500 7 Nếp 30 Cafe 6000 8 Gạo 500 Đậu xanh 11000 9 Nếp 100 Đường 6500 10 Cafe 500 Sữa 7000 11 Gạo 400 12 Cafe 1000 13 Nếp 70 14 Đường 600 15 Sữa 100 16 Đậu xanh 40 17 18 TỔNG TRUNG BÌNH MAX MIN SỐ LẦN Gạo Nếp Cafe Đậu xanh Đường Sữa Các yêu cầu : 1) Trong bảng A4:D16 Nhập dữ liệu cho các cột A,B. Cột C được xác định bằng cách dùng hàm VLOOKUP dò tìm ở bảng đơn giá (F4:G10). Cột D được tính theo công thức: Thành tiền = Số lượng * Đơn giá. 2) Dùng hàm TODAY để đưa ngày tháng năm hiện hành vào ô D3. 3) Tính tổng, trị trung bình, số lớn nhất, nhỏ nhất (theo mục thành tiền) của từng mặt hàng và số lần bán được theo từng mặt hàng. 4) Vẽ khung, ấn định kiểu chữ của bảng và định dạng số ở các cột Đơn giá, Thành tiền, Trung bình có dấu phẩy phân cách hàng ngàn, triệu,... và có một chữ số phần thập phân. 5) Đóng cửa sổ bảng tính và thoát khỏi Excel. 158 Bài thực hành 8 Dùng Excel tạo bảng tính theo mẫu sau và lưu vào đĩa với tên BAI8.XLS: A B C D E F G 1 Cửa hàng XYZ 2 3 Mã VT Tên Vật tư Ngày nhập PT Số lượng Đơn giá Thành tiền 4 SOBT Sơn bạch tuyết 01/01/01 N 50 800 5 X300 Ximen 300 04/01/01 N 100 4500 6 TU50 Thép U50 08/01/01 X 120 8000 7 SOAL Sơn Alkyde 24/01/01 N 100 1000 8 T15 Thép phi 15 05/05/01 N 200 10000 9 TU50 Thép U50 12/05/01 X 50 8000 10 X400 Ximen 400 16/05/01 X 120 5000 11 X300 Ximen 300 20/05/01 N 200 4500 12 X400 Ximen 400 25/07/01 X 100 5000 Tổng cộng : Các yêu cầu : 1) Nhập dữ liệu cho các cột: A, B, C, D, E, F. 2) Xác định số liệu ở cột thành tiền và tính tổng ở cột số lượng và cột thành tiền biết: Thành tiền = Số lượng * Đơn giá Định dạng số ở cột thành tiền có dấu phẩy phân cách hàng ngàn, triệu ... và có một chữ số phần thập phân. 3) Tính tổng số tiền nhập của mỗi lọai vật tư có chữ cái đầu bên trái ở cột Mã VT giống nhau (theo dạng bảng bên dưới), biết rằng vật tư nhập có phương thức (cột D): PT=N. Mã VT PT Tổng S* N ? X* N ? T* N ? 4) Trích ra các vật tư được nhập xuất trong tháng 5.