›
›

Giáo trình Tin học văn phòng - Hoàng Vũ Luân

doc 74 trang hapham 4540
Download
Bạn đang xem 20 trang mẫu của tài liệu "Giáo trình Tin học văn phòng - Hoàng Vũ Luân", để tải tài liệu gốc về máy bạn click vào nút DOWNLOAD ở trên

Tài liệu đính kèm:

  • docgiao_trinh_tin_hoc_van_phong_hoang_vu_luan.doc

Nội dung text: Giáo trình Tin học văn phòng - Hoàng Vũ Luân

  1. TRƯỜNG ĐẠI HỌC SƯ PHẠM HUẾ TRUNG TÂM TIN HỌC HOÀNG VŨ LUÂN GIÁO TRÌNH HUẾ – 2005
  2. MỤC LỤC MỤC LỤC 2 BÀI 1 TỔNG QUAN VỀ EXCEL 5 1.1. Giới thiệu - Các chức năng chính của Excel 5 1.2. Khởi động và kết thúc Excel 5 1.3. Các thành phần và khái niệm cơ bản 7 BÀI 2 LÀM VIÊC VỚI BẢNG TÍNH 12 2.1. Các thao các cơ bản 12 2.2. Sử dụng menu FILE 15 2.3. Sử dụng hàm (function) trong công thức 16 BÀI 3 CÁC HÀM THƯỜNG DÙNG TRONG EXCEL 18 3.1. Các hàm tính toán và thống kê (Statistical) 18 3.2. Các hàm lôgic 20 3.3. Các hàm chuỗi (string), văn bản (text) 21 3.4. Các hàm ngày, giờ (Date & Time) 22 3.5. Các hàm tìm kiếm và tham chiếu (Lookup & Reference) 23 BÀI 4 TẠO BIỂU ĐỒ - CHART WIZARD 25 4.1. Các thành phần của biểu đồ 25 4.2. Sử dụng Chart Wizard 25 4.3. Hiệu chỉnh, tạo dạng biểu đồ 27 BÀI 5 CƠ SỞ DỮ LIÊU TRONG EXCEL 30 5.1. Khái niệm về cơ sở dữ liệu (Data Base) 30 5.2. Hướng dẫn tạo danh sách trong Excel 30 5.3. Các hàm CSDL 31 BÀI 6 CÁC THAO TÁC TRÊN DANH SÁCH DỮ LIÊU 34 6.1. Sắp xếp dữ liệu ([DATA]\SORT) 34 6.2. Lọc dữ liệu ([DATA]\FILTER) 35 6.3. Thống kê theo nhóm ([DATA]\SUBTOTALS 38 6.4. Sử dụng [Data]Forms 40 6.5. Phân tích bảng dữ liệu [Data]Pivot Table 40 BÀI 7 CÁC CHỨC NĂNG BỔ SUNG 45 7.1. Định dạng trang: [File] Page Setup 45 7.2. Xem trước khi in: [File]Print Preview 46 7.3. Chức năng in: [File]Print 46 7.4. Sử dụng tính năng [Data]Group and Outline 47 7.5. Quản lý vùng màn hình làm việc 47 7.6. Dấu các hàng/cột 47 7.7. Định dạng có điều kiện 47 7.8. Kiểm tra dữ liệu khi nhập 48 7.9. Bảo vệ dữ liệu 50 2 Hoàng Vũ Luân
  3. HƯỚNG DẪN THỰC HÀNH MS-EXCEL 51 BÀI MỞ ĐẦU HƯỚNG DẪN THỰC HÀNH EXCEL 51 BÀI SỐ 1 52 BÀI SỐ 2a 53 BÀI SỐ 2b 55 BÀI SỐ 3 55 BÀI SỐ 4a 56 BÀI SỐ 4b 58 BÀI SỐ 5a 59 BÀI SỐ 5b 60 BÀI SỐ 6 61 BÀI SỐ 7a 62 BÀI TẬP 7b 64 BÀI SỐ 8 65 BÀI SỐ 9 67 BÀI SỐ 10 69 BÀI SỐ 11 70 BÀI SỐ 12 71 TIN HỌC ỨNG DỤNG - MICROSOFT EXCEL - 3
  4. TIN HỌC ỨNG DỤNG - MICROSOFT EXCEL 4 Hoàng Vũ Luân
  5. BÀI 1 TỔNG QUAN VỀ EXCEL 1.1.Giới thiệu - Các chức năng chính của Excel 1. Giới thiệu Excel là trình ứng dụng bảng tính trong Windows, thuộc bộ công cụ văn phòng Microsoft Office (MsOffice). Excel là ứng dụng đa văn bản – nghĩa là có thể mở đồng thời nhiều hơn một cửa sổ văn bản. Các thao tác trong Excel tuân theo tiêu chuẩn của Windows, như: làm việc với cửa sổ, các hộp đối thoại, hệ thống menu, sử dụng mouse, các biểu tượng lệnh Excel có thể được cài đặt một cách độc lập, nhưng thông thường là qua bộ cài đặt MsOffice. Đường dẫn đến chương trình EXCEL.EXE thường là Programs\Microsoft Office\ 2. Các chức năng chính của Excel Là một ứng dụng bảng tính, mỗi cửa sổ văn bản của Excel là một WorkBook, trong đó gồm nhiều Sheet – mỗi Sheet có thể là bảng tính, biểu đồ hoặc macro bảng tính. Các Sheet có thể độc lập hoặc phụ thuộc nhau tùy vào sự tổ chức của người sử dụng. Khi lưu (save) WorkBook, Excel tự động thêm phần mở rộng là XLS. Chức năng chính của Excel bao gồm: - tính toán, phân tích, tạo biểu đồ, lập báo cáo trên các dữ liệu được tổ chức theo dạng bảng 2 chiều (mô hình quan hệ). - chia sẻ dữ liệu với các ứng dụng khác Các chức năng này một phần được thực hiện thông qua các hàm đã được thiết kế sẵn hoặc hàm do người sử dụng tự tạo; phần khác thông qua các công cụ được tổ chức trong hệ thống menu hoặc biểu tượng lệnh. Là ứng dụng trong bộ MsOffice nên Excel được tối ưu hóa để sử dụng các tính năng bổ sung, như nhập văn bản từ Word, tạo chữ nghệ thuật từ WordArt, chèn văn bản toán học từ Equation, bổ sung hình ảnh từ ClipArt Gallery Ngược lại, Excel cũng cung cấp các phương thức để các ứng dụng khác có thể sử dụng được các chức năng mạnh của nó. Ngoài ra, Excel còn được thiết kế để có thể sử dụng các nguồn dữ liệu từ các ứng dụng khác, như dữ liệu từ FoxPro, từ Lotus 1-2-3 1.2.Khởi động và kết thúc Excel 1. Khởi động Có nhiều cách khởi động chương trình Excel, dưới đây là một số cách thông dụng: + Chọn biểu tượng Excel từ thanh Shortcut Bar hoặc thanh Quick Launch (nếu có) TIN HỌC ỨNG DỤNG - MICROSOFT EXCEL - BÀI 15
  6. thanh Quick Launch + Từ nút Start : [START]\Programs\(Microsoft Office)\Microsoft Office Excel + Hoặc khởi động từ biểu tượng Excel trong cửa sổ Windows Explorer 2. Kết thúc Sau khi hoàn tất phiên làm việc trong Excel, ta kết thúc nó bằng một trong các cách sau: + Chọn lệnh từ menu: [FILE]\Exit + Nhấn vào nút đóng cửa sổ [] ở góc trên phải, hoặc nhấn Alt–F4 F Nếu các WorkBook có sửa đổi và chưa ghi lại sự thay đổi, thì Excel sẽ yêu cầu ta xác nhận việc có ghi hay không trước khi kết thúc. Chọn [Yes] để ghi, [No] để kết thúc và không ghi, nhấn [Cancel] để hủy lệnh và tiếp tục làm việc với Excel. - Cần phải hoàn tất thao tác nhập dữ liệu trong 1 ô (nếu đang nhập dở dang) trước khi kết thúc cửa sổ WorkBook. 6 Hoàng Vũ Luân
  7. 1.3.Các thành phần và khái niệm cơ bản 1. Cửa sổ ứng dụng Cửa sổ chính của Excel là vùng màn hình chứa chương trình Excel khi nó được khởi động, tương tự như các cửa sổ ứng dụng khác trong Windows. Gồm các thành phần như: các đường viền giới hạn kích thước cửa sổ; thanh tiêu đề chứa tiêu đề chương trình và tên WorkBook đang làm việc; thanh menu ngang, các thanh công cụ (Toolbar) chứa các biểu tượng lệnh; các nút lệnh của cửa sổ (hộp điều khiển, nút Minimize, Maximize/Restore, Close); vùng làm việc (desktop) và cuối cùng là dòng trạng thái chứa thông báo và trạng thái làm việc. 2. Cửa sổ workbook (văn bản) Ngay sau khi khởi động, thông thường Excel đưa ra một workbook mới để người sử dụng có thể bắt đầu làm việc. WorkBook này được đặt trong một cửa sổ văn bản nằm trong vùng desktop của cửa sổ ứng dụng. Khi được maximize (cực đại hóa), tiêu đề của cửa sổ workbook nằm chung với tiêu đề của chương trình. Mỗi Workbook bao gồm nhiều Sheet. Mỗi Sheet là một lưới các ô (cell) được tổ chức thành hàng (row) và cột (column). Row WorkBook ® Sheet Cell Column Ngay phía trên vùng bảng tính có 2 thành phần: một là hộp tên (Name Box) chứa tên của ô (hoặc dãy các ô) hiện thời đang được chọn; hai là thanh công thức (Formula Bar) chứa nội dung hoặc cho phép nhập nội dung của ô đang chọn. Phía dưới là thanh chứa tên các sheet trong workbook và thanh cuốn ngang, bên phải là thanh cuốn dọc. TIN HỌC ỨNG DỤNG - MICROSOFT EXCEL - BÀI 17
  8. 3. Tổ chức bảng tính (sheet) trong Excel a. Đánh địa chỉ hàng, cột và ô Trong Excel, hàng được đánh số (gán nhãn) từ 1, 2, đến 16384 (hoặc 65536); cột được đánh thứ tự từ A, B, , Z, AA, , IV (256 cột). Giao của cột và hàng là ô (cell) với địa chỉ xác định là: [nhãn cột][nhãn dòng], ví dụ: ô F15 là giao của cột F và dòng 15, hoặc được xác định theo cách R[số hiệu dòng]C[số hiệu cột], như R5C8 là ô tại dòng 5 cột 8 (tức cột H). Đối với ô trong một sheet của một workbook nào đó, thì địa chỉ dạng đầy đủ là: ‘Path\[Tên_workbook]Tên_Sheet’!Tham_chiếu_ô Trong đó: + Path là đường dẫn đầy đủ của workbook sẽ tham chiếu; + Tham_chiếu_ô hoặc là tên 1 ô, hoặc một khoảng các ô. Khoảng các ô là tập hợp các ô có dạng: một dãy liên tục các ô (giới hạn trong một khung hình chữ nhật) và/hoặc các ô rời rạc. Trong đó các ô liên tục (khoảng các ô) được viết theo dạng ô_đầu_tiên_trên_trái:ô_cuối_cùng_dưới_phải, ví dụ: A4:C7 là khoảng liên tục 12 ô giới hạn bởi 3 cột (A, B, C) và 4 hàng (4, 5, 6, 7); các ô rời rạc cách nhau bởi dấu phân cách (thông thường là dấu phẩy), ví dụ: C5, E9, F12 là dãy 3 ô rời nhau. b. Nội dung của các ô Mỗi ô có thể chứa dữ liệu hoặc công thức tính toán. Ÿ Dữ liệu có thể là : - chuỗi ký tự (character/string) ví dụ Họ và tên - số (numeric) 125 - ngày (date) 08/12/1998 - giờ (time) 8:15:25 Ÿ Công thức tính toán có dạng: ký tự đầu tiên là dấu bằng (=) tiếp theo là một biểu thức. Ví dụ: tại ô A3 nếu ta nhập =5+3 thì sau khi nhấn Enter, nội dung của A3 sẽ là 8. + Biểu thức tính toán được định nghĩa là một tập hợp các toán tử và toán hạng được viết theo quy tắc (cú pháp) do Excel quy định. Trong đó: . Toán tử là các phép toán số học: + (cộng), - (trừ), * (nhân), / (chia), % (phần trăm), ^ (lũy thừa); hoặc các toán tử so sánh: (lớn hơn: greater than), = (bằng: equal to), = (lớn hơn hoặc bằng: greater than or equal to), <> (không bằng: not equal to), và toán tử nối chuỗi & (ví dụ: “HO”&“TEN” ® “HOTEN”). . Toán hạng có thể là giá trị hằng (constant), một tham chiếu ô, một nhãn (label), tên (name) hoặc là một hàm (function) của workbook. Các hằng chuỗi được bao trong cặp nháy kép “ ”. Ÿ Hàm (function) có dạng: Tên_hàm(danh sách đối số – nếu có), trong đó cặp ngoặt đơn là bắt buột. Do hàm thực hiện một quá trình xử lý hay tính toán và trả về một kết quả nên nó có thể xuất hiện bất kỳ ở đâu trong một biểu thức mà ở đó có thể có một toán hạng. Ngoài ra, Excel còn cho phép khả năng các hàm lồng nhau, nghĩa 8 Hoàng Vũ Luân
  9. là một hàm có thể xuất hiện trong danh sách đối số của một hàm khác. Ví dụ: SUM(A1, SUM(C5:F7)) F Ví dụ về công thức: = 15 + (4 * A6) – SUM(B2:B4) Trong đó: 5, 4 là các hằng; A6, B2:B4 là các tham chiếu ô; SUM là tên hàm; + * – là các toán tử. F Giá trị của công thức được Excel tự động cập nhật khi có sự thay đổi liên quan đến mỗi một giá trị của toán hạng trong đó. c. Tham chiếu tương đối và tuyệt đối Để tham chiếu đến các ô, có hai cách: tham chiếu tương đối và tham chiếu tuyệt đối. Ÿ Tham chiếu tương đối xác định vị trí tương đối từ ô chứa tham chiếu đến ô được tham chiếu. Ví dụ: trong công thức tại ô C3 có chứa tham chiếu đến ô A2 được hiểu như là: xuất phát tại ô hiện thời (C3) sang trái 2 cột (từ C sang A) và di chuyển lên 1 hàng (từ hàng thứ 3 lên 2) để lấy dữ liệu tại đó. - Với tham chiếu tương đối, khi người sử dụng sao chép công thức từ ô này sang ô khác thì giá trị tham chiếu tự động thay đổi. Ví dụ, nếu sao chép công thức trong C3 ở trên sang K5 thì tham chiếu đến A2 sẽ đổi lại là I4 (giữ nguyên sự tương đối từ K5 đến I4: sang trái 2 và lên 1). - Từ đây suy ra, nếu sao chép công thức theo chiều dọc thì số hiệu hàng sẽ bị thay đổi, số hiệu cột được giữ nguyên. Tương tự, nếu sao chép theo chiều ngang thì gía trị cột bị thay đổi, giữ lại số hiệu dòng. - Ví dụ: xét bảng sau: C D F 12 5 7 =SUM(C12:D12) 13 6 8 =SUM(C13:D13) 14 4 9 ? 15 =SUM(C12:C14) =SUM(D12:D14) Tại ô F12 nhập công thức tính tổng các ô từ C12 đến D12, khi đó nếu sao chép công thức đến ô F13 thì tham chiếu sẽ thay đổi thành tổng các ô từ C13 đến D13, sao chép đến F14 thì sẽ thành =SUM(C14:D14). Tương tự, khi chép ngang từ C15 sang D15 thì số hiệu 12 và 14 không đổi, mà đổi giá trị cột từ C sang D. Ÿ Tham chiếu tuyệt đối xác định sự tuyệt đối trong cách tham chiếu, nghĩa là luôn hướng đến các vị trí cố định (theo hàng và/hoặc theo cột) nào đó của bảng tính khi sao chép công thức. Vì ô được xác định bởi hàng và cột, nên sự tuyệt đối ở đây có thể chỉ tác động đến hàng, đến cột hoặc cả hai. Excel dùng ký tự $ đặt trước tên hàng hoặc tên cột để chỉ sự tuyệt đối. Ví dụ, E1 chứa công thức = $A$1 + $B1 + C$1 + D1, bao gồm tuyệt đối ở A1, tuyệt đối theo hàng ở B1, theo cột ở C1 và tương đối ở D1. Khi đó nếu sao chép công thức này đến ô H5 thì sẽ tự động đổi lại là: = $A$1 + $B5 + F$1 + G5. F Tên của một khoảng các ô được xem là một tham chiếu tuyệt đối. F Trong thực hành, sau khi nhập tham chiếu ô ta dùng phím F4 để chuyển đổi giữa các loại tham chiếu TIN HỌC ỨNG DỤNG - MICROSOFT EXCEL - BÀI 19
  10. d. Đặt tên cho một khoảng ô Một khoảng các ô có thể được gán bởi một tên để dễ sử dụng. Ví dụ, thay cho việc viết công thức: = SUM(E7:E9) với E7 đến E9 chứa dữ liệu về doanh số bán của 3 tháng 7, 8 và 9 ta có thể viết: = SUM(Quy3), trong đó Quy3 đại diện cho các khoảng các ô E7:E9. Để thực hiện điều này ta tiến hành như sau: 1. Chọn các ô E7 đến E9 (drag ngang qua các ô E7, E8 và E9) 2. Click mouse vào hộp tên (Name Box) và nhập vào đó tên: Quy3 3. Nhấn Enter để xác nhận việc đặt tên cho các ô đang chọn. F Hoặc có thể sử dụng menu [INSERT]\Name\Define như sau: - Lưu ý đến dòng cuối, tham chiếu đến khoảng ô E7:E9 đã được Excel tự động chuyển thành tham chiếu tuyệt đối. Nếu muốn xóa một tên đã định nghĩa ta chọn tên cần xóa và dùng nút [Delete].  Sau khi đã gán tên cho một khoảng ô, ta có thể kiểm tra lại tên bằng cách chọn lại khoảng ô đó, nếu thấy xuất hiện tên trong hộp tên thì đã gán đúng, ngược lại cần xóa tên đã gán sai và tiến hành gán lại.  Hộp tên còn cho phép ta chọn hoặc chuyển nhanh đến một khoảng ô: Chỉ cần nhập tên, hoặc tham chiếu ô vào hộp tên thì Excel sẽ tự động chọn và chuyển vị trí màn hình đến vùng được chọn. - Hướng dẫn đặt tên: (b1. Chọn các ô cần đặt tên b2. Click vào hộp tên và nhập tên b3. Nhấn Enter) Kiểm tra lại tên đã đặt: click vào nút [ ] ta nhìn thấy tên đã đặt trong hộp rơi xuống. 10 Hoàng Vũ Luân
  11. - Xóa tên đã gán sai: dùng menu [Insert]\Name\Define Trong hộp [Define Name], ta chọn tên cần xóa và chọn nút [Delete]. TIN HỌC ỨNG DỤNG - MICROSOFT EXCEL - BÀI 1 11
  12. BÀI 2 LÀM VIÊC VỚI BẢNG TÍNH 2.1.Các thao các cơ bản 1. Chọn các ô, hàng, cột - Chọn 1 ô: click vào ô muốn chọn - Chọn nhiều ô liên tục: chọn ô đầu tiên (góc trên trái) của khoảng cần chọn, sau đó drag (kéo lê) mouse (hoặc shift-click) đến vị trí cuối (góc dưới phải) . - Để chọn thêm các ô rời rạc nhấn giữ phím Ctrl trong khi click vào các ô. F Ngoài ra, còn có thể chọn nhanh bằng cách nhập khoảng cần chọn vào hộp tên. - Việc chọn hàng hoặc cột tương tự như chọn ô. Thay cho click vào ô, ta click vào nhãn cột hoặc số hiệu hàng để chọn 1 cột hoặc 1 hàng. Chọn nhiều liên tục bằng kỹ thuật drag hoặc shift-click. Chọn rời rạc bằng Ctrl-click. (Drag ở đầu dòng, đầu cột để chọn một hoặc nhiều dòng và chọn một hoặc nhiều cột) 2. Các cách thực hiện lệnh - Dùng menu hoặc shortcut menu (dùng Right-click) - Dùng biểu tượng lệnh trong thanh công cụ 12 Hoàng Vũ Luân
  13. - Dùng phím tắt 3. Chèn, xóa các ô, hàng, cột 1- Chọn các ô, hàng, cột cần tác động 2- Click mouse phải (Right-click) làm xuất hiện shortcut menu 3- Chọn lệnh thích hợp từ shortcut menu: Insert để chèn thêm Delete để xóa Clear contents để xóa nội dung các ô (hoặc nhấn phím Del) F Có thể dùng menu thay cho việc nhấn R-click. Menu [Edit]\Del hoặc [Edit]\Clear để xóa. Menu [Insert]\Cells, Rows hoặc Columns để chèn thêm. F Số đối tượng chèn thêm vào hoặc xóa đi bằng với số đối tượng đã chọn (ví dụ, nếu đang chọn 3 hàng thì lệnh Insert sẽ chèn 3 hàng) 4. Nhập và sửa chữa nội dung của ô Ÿ Nhập: Chọn ô, sau đó nhập nội dung. Lưu ý đến cách thức Excel chỉnh lề tùy thuộc vào dạng dữ liệu sẽ nhập. Nếu là chuỗi ® chỉnh trái; ngày, giờ hoặc số ® chỉnh phải. Để nhập chuỗi các số như 2356 ta thêm dấu nháy đơn (‘) phía trước chuỗi số này: ‘2356. Kết thúc việc nhập nội dung bằng phía Enter. Nếu không muốn thay đổi nội dung đã có trước đó thì nhấn Esc. Ÿ Sửa: Nhấn Double-click (D-click) hoặc F2 vào một ô đang chọn để sửa chữa nội dung, nếu chỉ click vào ô thì dữ liệu nhập vào sẽ thay dữ liệu đã có trước đó. Trong chế độ sửa chữa có thể dùng các phím ¬, ®, Home, End để di chuyển. Dùng dấu bằng (=) để bắt đầu nhập công thức. Nếu trong công thức cần tham chiếu ô thì hoặc là tự nhập tên các ô cần tham chiếu, hoặc là dùng mouse để chọn. Ví dụ: tại ô D7 cần nhập công thức tính: = B7 + C7, đầu tiên nhập dấu =, sau đó dùng mouse click vào ô B7, nhập dấu + và click vào C7 và nhấn Enter để hoàn tất. Tiến hành tương tự đối với khoảng liên tục các ô (dùng drag thay cho click). nhấn enter ta được  Tạo dạng ô (Format Cells): Excel cung cấp nhiều khả năng trong việc thay đổi dạng thức của một ô. Ví dụ: đối với 1 con số có thể được biểu diễn bằng nhiều dạng: nó có thể là số, là ngày, giờ, biểu diễn phần trăm, tiền tệ với ký hiệu $, đ TIN HỌC ỨNG DỤNG - MICROSOFT EXCEL - BÀI 2 13
  14.  Để tạo dạng một hoặc nhiều ô, đầu tiên ta chọn chúng, sau đó dùng menu [Format]Cells hoặc nhấn phím phải và chọn Format Cells Trong hộp thoại có chứa nhiều mục phục vụ cho việc tạo dạng. Chúng bao gồm: - Mục [Number] dùng để tạo dạng số, gồm: Number tạo dạng các con số, Currency dạng tiền tệ; Date, Time dạng ngày giờ; Percentage dạng phần trăm; Fraction dạng phân số; Text dạng văn bản  Custom là dạng đặc biệt, nó cho phép người sử dụng tự điều chỉnh việc tạo dạng. Một số mã tạo dạng hay dùng là: * Mã tạo dạng số: (ví dụ số cần tạo dạng: 3149.457) Mã Kết quả Ý nghĩa 0 3149 Làm tròn đến hàng đơn vị (không có số lẻ) 0.00 3149.46 Lấy 2 chữ số lẻ #,##0 3,149 Có ký tự phân cách nhóm 3 số và làm tròn 0.00% 314945.70% Theo dạng % có hai số lẻ 0.00 “đ” 3149.46 đ Thêm chuỗi vào kết quả (trong cặp “ ”) * Mã tạo dạng ngày (ví dụ: ngày 15 tháng 7 năm 1999) Mã Kết quả Ý nghĩa dd/mm/yy 15/07/99 ngày/tháng/năm, mỗi vị trí có 2 số mm/dd/yy 07/15/99 tháng/ngày/năm, mỗi vị trí có 2 số mm/dd/yyyy 07/15/1999 năm có 4 chữ số dd-mmm-yy 15-Jul-99 tên tháng có 3 ký tự viết tắt - Mục [Alignment] dùng để chỉnh sắp dữ liệu theo 2 hướng: ngang (Horizontal), đứng (Vertical); và điều khiển việc cho phép văn bản xuống dòng (Wrap text) hay 14 Hoàng Vũ Luân
  15. trải lấp sang các ô bên cạnh. Ngoài ra còn cho phép quay văn bản theo các góc quay khác nhau. - Mục [Font] dùng để tạo dạng về font chữ. - Mục [Border] dùng cho việc thiết lập đường viền của các ô với rất nhiều lựa chọn: 5. Tạo dãy tự động Một dãy số liệu liên tục (ví dụ: a1, a2, ) có thể được tạo ra một cách tự động bằng cách nhập số liệu đầu (a1), sau đó chọn nó sẽ làm xuất hiện một nút vuông nhỏ ở góc dưới phải, di chuyển mouse đến vị trí này (mouse có hình dấu cộng) kéo lê nút vuông đến ô cuối của dãy số liệu sẽ tạo ra một dãy liên tục. Nếu dãy là dãy số (ví dụ: 1, 2, ) thì phải nhấn thêm phím Ctrl để tạo dãy liên tục. Nếu ô đầu tiên là công thức thì việc kéo nút điều khiển sẽ sao chép công thức đến các ô, khi đó các tham chiếu sẽ được điều chỉnh tự động. Drag 2.2.Sử dụng menu FILE Menu File chứa các lệnh tác động lên tổng thể workbook, bao gồm: + New Bắt đầu tạo mới một workbook. + Open Mở một workbook đã ghi trước đó. + Close Đóng cửa sổ workbook đang làm việc. TIN HỌC ỨNG DỤNG - MICROSOFT EXCEL - BÀI 2 15
  16. + Save Ghi workbook đang làm việc ra file. Toàn bộ Sheet có trong workbook sẽ cùng được lưu trữ trong một file có phần mở rộng là XLS. + Save as Ghi ra dưới một tên mới. Nếu là lần đầu tiên ghi file thì chức năng này sẽ tự động được chọn thay cho Save. + Print preview Xem tổng thể workbook trước khi in chính thức. + Print In toàn bộ workbook, hoặc chỉ in Sheet đang làm việc hiện thời. + Exit Kết thúc phiên làm việc với Excel. 2.3.Sử dụng hàm (function) trong công thức Hàm có thể được nhập trực tiếp trong công thức hoặc sử dụng công cụ chèn hàm của Excel. Để chèn hàm vào công thức, dùng menu [Insert]Function hoặc click vào biểu tượng Function Wizard [fx]. Chủ đề: Các hàm thường dùng Liệt kê tất cả các hàm Các hàm tài chính Ngày và giờ Toán và lượng giác Thống kê Tìm kiếm và tham chiếu Hàm cơ sở dữ liệu Hàm văn bản Hàm lôgic Hàm thông tin Trong hộp thoại Function Wizard có hai bảng, bên trái là chủ đề các hàm, bên phải là hàm sẽ chọn. Các hàm được tổ chức theo chủ đề để dễ sử dụng. Sau khi chọn được hàm thích hợp, sẽ thực hiện tiếp các bước còn lại để nhập nội dung cho các đối số của hàm. Ví dụ, đối với hàm SUM cần chỉ ra khoảng các ô sẽ lấy tổng Trong quá trình nhập nội dung, để tham chiếu đến các ô ta có thể dùng mouse để chỉ định các ô cần tham chiếu mà không cần phải nhập trực tiếp. Cũng có thể sử dụng việc đặt tên để đơn giản hóa việc quản lý các ô. Sau khi hoàn tất nhấn [Finish] hoặc [OK] để kết thúc. 16 Hoàng Vũ Luân
  17.  Lưu ý: nếu tiến trình nhập công thức ở một ô chưa xong thì không thể kết thúc Excel, khi đó sẽ có thông báo lỗi về việc chưa hoàn tất công thức (hoặc trong công thức có lỗi) - Và tiếp đó là thông báo không thể kết thúc Excel:  Để giải quyết lỗi này, ta nhấn OK để quay về ô chứa công thức đang nhập và nhấn ESC để hủy công thức, sau đó sẽ có thể kết thúc Excel bình thường. TIN HỌC ỨNG DỤNG - MICROSOFT EXCEL - BÀI 2 17
  18. BÀI 3 CÁC HÀM THƯỜNG DÙNG TRONG EXCEL 3.1.Các hàm tính toán và thống kê (Statistical) 1. Hàm Sum * Cú pháp: SUM(danh sách đối số) Hàm Sum tính tổng các số có mặt trong danh sách đối số của nó. * Trong đó: danh sách đối số có thể là hằng (số hoặc chuỗi số), khoảng các ô chứa số hoặc một hàm trả về giá trị kiểu số. * Ví dụ: Sum(2, “4”, True) = 7, vì “4” được chuyển thành số 4; True=1 (False=0) Nếu C2 chứa 7; C3 là 4; C4 là 10 thì Sum(C2:C4) = 21 2. Hàm Max * Cú pháp: MAX(danh sách đối số) Hàm Max trả lại giá trị số lớn nhất trong danh sách đối số. * Ví dụ: Max(2, 5, 7) = 7 Nếu C2 chứa 7; C3 là 4; C4 là 10 thì Max(C2:C4) = 10 3. Hàm Min * Cú pháp: MIN(danh sách đối số) Hàm Min trả lại giá trị số nhỏ nhất trong danh sách đối số. 4. Hàm Average * Cú pháp: AVERAGE(danh sách đối số) Hàm Average trả lại giá trị trung bình cộng của các số trong danh sách đối số. * Ví dụ: Average(2, 5, 7) = 4.66(6) Nếu C2 chứa 7; C3 là 4; C4 là 10 thì Average(C2:C4) = (7+4+10)/3 = 7 5. Hàm Round * Cú pháp: ROUND(số, số_chữ_số) Hàm Round trả lại giá trị số (kiểu số thực) đã được làm tròn đến độ chính xác tùy thuộc vào số_chữ _số. Nếu: - số chữ số > 0 làm tròn phần lẻ ví dụ: Round(21.546, 2) = 21.55 - số chữ số = 0 lấy số nguyên gần nhất ví dụ: Round(21.546, 0) = 22 - số chữ số < 0 làm tròn phần nguyên ví dụ: Round(21.546, -1) = 20 6. Hàm Int * Cú pháp: INT(số) Hàm Int trả lại phần nguyên của số (làm tròn số đến số nguyên gần bằng nhất) So sánh với hàm Round(n, 0) là hàm làm tròn trên, Int là hàm làm tròn dưới. * Ví dụ: Int(2.57) = 2; Int(7/4) = 1; Int(-3.49) = -4 18 Hoàng Vũ Luân
  19. 7. Hàm Mod * Cú pháp: MOD(số, số chia) Hàm Mod tính phần dư trong phép chia nguyên của số với số chia * Ví dụ: Mod(13, 4) = 1 vì 13 chia 4 được 3, dư 1 Giả sử tại ô C3 chứa số ngày làm việc, khi đó công thức = Int(C3/7) sẽ cho số tuần; và công thức = Mod(C3, 7) cho lại số ngày lẻ. 8. Hàm Count * Cú pháp: COUNT(danh sách đối số) Danh sách đối số có thể là các giá trị số, ngày, logic, chuỗi hoặc một danh sách tham chiếu. Hàm Count đếm các giá trị kiểu số (hoặc giá trị có thể chuyển tự động thành kiểu số, như: kiểu ngày, chuỗi số, lôgic ) có mặt trong danh sách đối số. Hàm này có thể kết hợp với hàm Sum để thay cho Average khi cần đảm bảo tính chính xác của phép lấy trung bình một khoảng giá trị mà không chắc các giá trị này là số. * Ví dụ: Count(13, “432”, “abc”) = 2 vì chuỗi 432 được chuyển thành giá trị số Count(12/8/98, TRUE) = 2 các kiểu ngày, lôgic được chuyển thành số 9. Hàm CountA * Cú pháp: COUNTA(danh sách đối số) Hàm CountA đếm số các giá trị có mặt trong danh sách đối số và chỉ bỏ qua không đếm các ô rỗng trong khoảng tham chiếu. * Ví dụ: Xét 4 ô A1:D1 như sau: A B C D 1 xy 25 True Khi đó: CountA(A1:D1) = 3 vì B1 rỗng 10. Hàm Rank * Cú pháp: RANK(số cần xếp hạng, danh sách số, phương pháp sắp) Hàm Rank trả lại thứ hạng của số cần xếp hạng theo các giá trị của danh sách số. Nếu phương pháp sắp bằng 0 (hoặc bỏ qua) thì danh sách số được sắp theo thứ tự giảm dần, ngược lại là thứ tự tăng dần trước khi so sánh giá trị. Ví dụ nếu cần sắp thứ hạng học lực thì phương pháp sắp sẽ là 0, nếu cần sắp thứ hạng thành tích vận động viên theo thời gian thì phương pháp sắp sẽ khác 0 (bằng 1 chẳng hạn). Đối với các giá trị bằng nhau trong danh sách số thì thứ hạng tương ứng của chúng sẽ bằng nhau. * Ví dụ: Giả sử các ô từ A1 đến A5 chứa giá trị tương ứng là 5, 6, 9, 4, 8 Khi đó: Rank(A1, A1:A5, 1) = 2 (thứ tự tăng: 4, 5, 6, 8, 9) nhưng: Rank(A1, A1:A5, 0) = 4 (thứ tự giảm: 9, 8, 6, 5, 4)  Ví dụ tổng hợp về các hàm tính toán, thống kê: A B C D E F G 1 5 7 6 =SUM(A1:C1) =MAX(A1:C1) =MIN(A1:C1) =AVERAGE(A1:C1) 2 3 9 8 =SUM(A2:C2) 2 2.57 21.546 3 2 4 7 =SUM(A3:C3) =MOD(B2,E2) =INT(F2) =ROUND(G2,2) 4 6 9 5 =SUM(A4:C4) =RANK(A1,A1:A4,0) =ROUND(G2,0) 5 a x =COUNT(A4:C5) =COUNTA(A4:C5) =ROUND(G2,-1) TIN HỌC ỨNG DỤNG - MICROSOFT EXCEL - BÀI 3 19
  20. Từ các công thức và số liệu cho ở trên, ta có các kết quả sau: 3.2.Các hàm lôgic Các hàm logic thường sử dụng, hoặc tạo ra - các biểu thức logic. Biểu thức logic là một biểu thức được lượng giá bởi hai giá trị: đúng (True = 1) và sai (False = 0). Trong đó các toán hạng có thể là hằng, tham chiếu ô, kết quả của một hàm nhưng chúng được liên kết với nhau bởi các toán tử so sánh ( , =, =, 4; “A” 8; (2*10) = ((5-2)*9) 1. Hàm And * Cú pháp: AND(danh sách biểu thức logic) Hàm And trả lại giá trị True nếu tất cả các biểu thức logic trong danh sách là True; ngược lại nếu có ít nhất một biểu thức logic trong danh sách nhận giá trị False thì hàm trả lại giá trị False. * Ví dụ: And(3>2, “Toi” >= “Ta”, C3=0) sẽ là True nếu ô C3 chứa giá trị 0 vì khi đó 3 biểu thức trong danh sách đều nhận giá trị True; ngược lại sẽ có giá trị False. 2. Hàm Or * Cú pháp: OR(danh sách biểu thức logic) Hàm OR trả lại giá trị False nếu tất cả các biểu thức logic trong danh sách là False; ngược lại nếu có ít nhất một biểu thức logic trong danh sách nhận giá trị True thì hàm trả lại giá trị True. * Ví dụ: OR(3<=2, “Anh” = “Em”, C3=0) sẽ là False nếu ô C3 chứa giá trị khác 0 vì khi đó cả 3 biểu thức trong danh sách đều nhận giá trị False; ngược lại sẽ có giá trị True. 3. Hàm Not * Cú pháp: NOT(biểu thức logic) Hàm Not đổi ngược giá trị của biểu thức logic. (Not(True) = False và Not(False)=True) * Ví dụ: Not(3<5) = True vì 3<5 là False 4. Hàm If * Cú pháp: IF(bt_logic, giá trị nếu bt_logic đúng, giá trị nếu bt_logic sai) 20 Hoàng Vũ Luân
  21. Hàm If căn cứ vào sự lượng giá của bt_logic để trả về một trong hai giá trị: giá trị nếu bt_logic đúng và giá trị nếu bt_logic sai. Giá trị trả lại có thể lại được nhận thông qua kết quả của một hàm khác. Điều này chính là khả năng lồng nhau của các hàm trong Excel. * Ví dụ: Giả sử tại ô A3 chứa thông tin về trình độ văn hóa. Khi đó công thức: If(A3 = “ĐH”, “Đại học”, If(A3 = “CĐ”, “Cao đẳng”, “Trung cấp”)) sẽ trả về một trong 3 chuỗi ký tự “Đại học”, “Cao đẳng” hoặc “Trung cấp” tùy thuộc vào nội dung của A3 là “ĐH”, “CĐ” hay “TC” 3.3.Các hàm chuỗi (string), văn bản (text) Các hàm loại này thường yêu cầu đối số có kiểu ký tự, hoặc kết quả của hàm trả lại là một kiểu ký tự. Nếu kết quả của các hàm là một chuỗi số thì có thể được sử dụng trong các phép tính toán vì Excel tự động chuyển chuỗi số thành giá trị số khi cần thiết. Trong các trường hợp khác cần lưu ý đến kiểu của giá trị để xử lý đúng. 1. Hàm Len * Cú pháp: LEN(text) Hàm Len lấy đối số text có kiểu ký tự và trả lại độ dài của text. Các ký tự trống (khoảng cách) trong text cũng được đếm như các ký tự khác. * Ví dụ: Len(“Đây là một chuỗi”) = 16 Len(“”) = 0 (chuỗi rỗng) Nếu ở ô G2 có chứa chuỗi “MASO 125” thì Len(G2) = 8 2. Hàm Left * Cú pháp: LEFT(text, num) Hàm Left trả lại num ký tự bên trái nhất của text. Nếu bỏ qua đối số num thì xem như lấy ký tự đầu tiên của text. Nếu num lớn hơn độ dài của text thì lấy toàn bộ text. * Ví dụ: Left(“AC035”, 2) = “AC” (lấy 2 ký tự bên trái) Left(“AC35”) = “A” (bỏ qua đối số num) 3. Hàm Right * Cú pháp: RIGHT(text, num) Hàm Right trả lại num ký tự bên phải nhất của text. Nếu bỏ qua đối số num thì xem như lấy ký tự cuối cùng của text. Nếu num lớn hơn độ dài của text thì lấy toàn bộ text. * Ví dụ: Right(“AC035”, 2) = “35” (lấy 2 ký tự bên phải) Right(“AC035”) = “5” (bỏ qua đối số num xem như lấy 1 ký tự) Right(“A035”, 2)*20 = 700 (tự động chuyển kiểu khi tính toán) 4. Hàm Mid * Cú pháp: MID(text, pos, num) Hàm Mid trả lại num ký tự của text, tính từ vị trí pos. Ký tự đầu tiên của text được đếm là 1. Nếu pos lớn hơn độ dài của text thì trả lại chuỗi rỗng. * Ví dụ: Mid(“AC035”, 2, 3) = “C03” (tính từ vị trí thứ 2, lấy 3 ký tự) TIN HỌC ỨNG DỤNG - MICROSOFT EXCEL - BÀI 3 21
  22. Mid(“AC035”, 6, 1) = “” (vị trí pos vượt quá độ dài của text) 5. Nối hai chuỗi & * Cú pháp: text1 & text2 Toán tử nối chuỗi & lấy hai đối số kiểu ký tự text1 và text2 để tạo thành chuỗi mới là hợp của hai chuỗi này. * Ví dụ: “MASO là ” & Left(“A124”) ® “MASO là A” 3.4.Các hàm ngày, giờ (Date & Time) Dữ liệu kiểu ngày trong Excel được tính từ ngày 01/01/1900 đến 31/12/2078 (hoặc 31/12/9999 trong Excel 97) tương ứng với giá trị từ 1 đến 65380. Kiểu ngày có thể được chuyển thành một số và ngược lại; ví dụ: ngày 05/01/1900 được tính như là giá trị 5, và giá trị 1245 được xem như ngày 29/05/1903. Tùy theo mã định dạng ngày của Windows mà Excel có thể tạo định dạng mặc định là mm-dd-yy (tháng-ngày-năm) hay dd-mm-yy (ngày-tháng-năm). Khi nhập ngày trong một ô cần lưu ý đến điều này, và nếu ngày hợp lệ thì Excel thường tự động chỉnh phải ngược lại là chỉnh trái. Trong thực hành, thường nhập thử ngày 13 tháng 01 để kiểm tra (13/01/98), nếu sau khi nhập, giá trị được chỉnh lề phải, thì dạng ngày là dd/mm/yy, ngược lại là dạng ngày mm/dd/yy. Để nhập năm lớn hơn 2000 cần phải nhập đủ 4 chữ số chỉ năm, nếu nhập 2 chữ số thì Excel tự động hiểu là thuộc năm 19xx. Dạng giờ trong Excel là hh:mm:ss (giờ:phút:giây), giờ có thể theo dạng 12 giờ (dùng ký tự AM và PM để biểu thị buổi sáng, chiều) hoặc dạng 24 giờ. Trên các kiểu dữ liệu này có thể thực hiện các phép tính số học thông thường như +, - giữa các đối số kiểu ngày (tính toán sự chênh lệch giữa hai ngày) hoặc giữa một đối số kiểu ngày với một số nguyên (tăng hoặc giảm ngày). 1. Hàm Day * Cú pháp: DAY(dãy số kiểu ngày) Hàm Day trả lại giá trị ngày (từ 1 đến 31) từ dãy số kiểu ngày. Dãy số kiểu ngày có thể là một chuỗi ngày (“03/01/98”) hoặc tham chiếu đến ô chứa giá trị ngày. * Ví dụ: Day(“07/21/98”) = 21 (ngày 21 tháng 7 – dạng mm/dd/yy) 2. Hàm Month * Cú pháp: MONTH(dãy số kiểu ngày) Hàm Month trả lại giá trị tháng (từ 1 đến 12) từ dãy số kiểu ngày. * Ví dụ: Month(“07/21/98”) = 7 (ngày 21 tháng 7 – dạng mm/dd/yy) 3. Hàm Year * Cú pháp: YEAR(dãy số kiểu ngày) Hàm Year trả lại giá trị năm (từ 1900 đến năm lớn nhất có thể) từ dãy số kiểu ngày. * Ví dụ: Year(“07/21/98”) = 1998 (năm nhập 2 chữ số được xem như 19xx) 22 Hoàng Vũ Luân
  23. 4. Hàm Now * Cú pháp: NOW() Hàm Now không có đối số, trả lại giá trị là một dãy số gồm ngày, tháng, năm và giờ hiện tại đang được lưu trong đồng hồ của máy tính. * Ví dụ: Year(Now()) trả lại năm hiện tại (là 2005) Giả sử ô C7 chứa ngày sinh, khi đó công thức: Year(Now()) - Year(C7) sẽ cho giá trị là tuổi của đối tượng. 5. Các hàm về thời gian * Hour ® trả lại giá trị giờ (1 – 24) * Minute ® trả lại giá trị phút (1 – 60) * Second ® trả lại giá trị giây (1 – 60) 3.5.Các hàm tìm kiếm và tham chiếu (Lookup & Reference) Các hàm trong nhóm này thường sử dụng một bảng chứa các giá trị sẽ trả lại qua một quá trình tìm kiếm theo khóa tìm. Bảng này thường được gọi là bảng dò hay bảng tìm và để dễ xử lý ta nên đặt tên cho bảng. Có hai loại bảng: bảng ngang và bảng dọc. a) Bảng ngang (chứa giá trị cần tìm theo hàng ngang), ví dụ: LPH A B C TTUAN x1 x2 x3 TNGA y1 y2 y3 Y Ở bảng trên, các giá trị cần tìm A, B, C trải ra theo phương ngang ở hàng số 1; các giá trị x1, x2, x3 (ở hàng 2) và y1, y2, y3 (ở hàng 3) là các giá trị sẽ trả lại sau quá trình tìm. Các ô LPH, TTUAN, TNGAY chỉ có tính chất tham khảo, làm rõ ý nghĩa của bảng, chứ không tham gia vào quá trình tìm kiếm. Thực chất bảng trên chỉ có 9 ô (từ ô chứa A đến ô chứa y3. b) Bảng dọc (chứa giá trị cần tìm theo hàng dọc), ví dụ: LPH TTUAN TNGA Y A x1 y1 B x2 y2 C x3 y3 Các ô chứa A, B, C ở cột 1, các giá trị còn lại ở cột 2 và cột 3. Tương ứng với hai loại bảng ở trên là hai loại hàm tìm kiếm: HLOOKUP và VLOOKUP. 1. Hàm VLookup * Cú pháp: VLOOKUP(gt_tìm, bảng_tìm, cột_trả_gt, cách_tìm) Nếu cách_tìm = 1 (True) thì cột đầu tiên của bảng_tìm được sắp thứ tự tăng dần. TIN HỌC ỨNG DỤNG - MICROSOFT EXCEL - BÀI 3 23
  24. Ngược lại, nếu cách_tìm = 0 (False) thì bảng_tìm không yêu cầu sắp. Hàm VLOOKUP tìm trong cột đầu tiên của bảng_tìm một giá trị hợp lệ so với gt_tìm, sau đó nếu tìm thấy thì sẽ trả lại giá trị tương ứng theo hàng ở cột_trả_gt. Bảng_tìm là một bảng, trong đó cột đầu tiên (đánh số 1) của bảng này chứa các giá trị tương hợp với các giá trị sẽ tìm. Các cột còn lại chứa các giá trị trả lại tương ứng với hàng của giá trị tìm thấy. * Ví dụ: VLookUp(“B”, [Bảng_tìm], 2, 0) = x2 2. Hàm HLookup * Cú pháp: HLOOKUP(gt_tìm, bảng_tìm, hàng_trả_gt, cách_tìm) Nếu cách_tìm = 1 (True) thì hàng đầu tiên của bảng_tìm được sắp thứ tự tăng dần. Ngược lại, nếu cách_tìm=0 (False) thì bảng_tìm không yêu cầu sắp. Hàm HLOOKUP tìm trong hàng đầu tiên của bảng_tìm một giá trị hợp lệ so với gt_tìm, sau đó nếu tìm thấy thì sẽ trả lại giá trị tương ứng theo cột ở hàng_trả_gt. Bảng_tìm là một bảng, trong đó hàng đầu tiên (đánh số 1) của bảng này chứa các giá trị tương hợp với các giá trị sẽ tìm. Các hàng còn lại chứa các giá trị trả lại tương ứng với cột của giá trị tìm thấy. * Ví dụ: HLookUp(“C”, [Bảng_tìm], 3, 0) = y3 3. Hàm Columns * Cú pháp: COLUMNS(khoảng tham chiếu) Hàm Columns trả lại số cột trong khoảng tham chiếu. * Ví dụ: Columns(A5:C10) = 3 (từ A ® C là 3 cột) 4. Hàm Rows * Cú pháp: ROWS(khoảng tham chiếu) Hàm Rows trả lại số hàng trong khoảng tham chiếu. * Ví dụ: Rows(A5:C10) = 6 (từ 5 ® 10 là 6 hàng) 24 Hoàng Vũ Luân
  25. BÀI 4 TẠO BIỂU ĐỒ - CHART WIZARD Ngoài việc sử dụng mảng hai chiều để tổ chức dữ liệu, trong Excel để thể hiện mối tương quan giữa các dãy số liệu ta có thể biểu diễn chúng bởi biểu đồ (chart). Biểu đồ có dạng đồ hình, được phân thành nhiều kiểu: Area (kiểu vùng); Bar (thanh); Column (cột); Line (đường); Pie (cung tròn chứa góc); XY-Scatter (điểm rời rạc) Những kiểu này có thể được biểu diễn theo dạng 2 chiều (2-D) hoặc 3 chiều (3-D). 4.1.Các thành phần của biểu đồ + Vùng dữ liệu: một khoảng liên tục hoặc rời rạc các ô bảng tính được chọn để dùng làm dữ liệu cho biểu đồ, có thể tổ chức theo hàng hay cột gọi là dãy (series) dữ liệu. Mỗi ô tạo thành một điểm dữ liệu trên biểu đồ và được đánh dấu (markers) bởi các kí hiệu khác nhau. Vùng dữ liệu có thể bao gồm một hàng (hoặc cột) chứa các nhãn (label). + Trục tọa độ: hệ thống các đường thẳng đứng hoặc ngang xác định tỉ lệ biểu diễn các điểm dữ liệu, trên các trục có chứa các vạch đánh dấu tỉ lệ (tick mark). Các trục thường có hai loại: trục chủ đề (category) và trục giá trị (value). + Hộp ghi chú (legend): chứa các dấu hiệu biểu diễn các dãy số liệu có mặt trong biểu đồ. Có thể đặt legend tại một vị trí tùy ý trong biểu đồ. + Tiêu đề (title): dòng văn bản tạo nhãn cho biểu đồ (chart title) và các trục. Biểu đồ mẫu 3 5 3 0 2 5 2 0 F o o d G a s 1 5 M o t e l 1 0 5 0 J a n F e b M a r A p r M a y J u n 4.2.Sử dụng Chart Wizard Ÿ Bước 1: Chọn vùng dữ liệu chứa số liệu của biểu đồ. Có thể chọn liên tục hoặc rời rạc nhóm các ô theo hàng hoặc cột. TIN HỌC ỨNG DỤNG - MICROSOFT EXCEL - BÀI 4 25
  26. Ÿ Bước 2: Click vào biểu tượng Chart Wizard hoặc chọn [Insert]\Chart. Khi con trỏ có dạng dấu cộng (+), ta vẽ một khung chữ nhật xác định kích thước và vị trí ban đầu của biểu đồ. Lưu ý: có thể chọn vẽ tại sheet đang chứa số liệu hoặc tại một sheet khác. Ÿ Bước 3: Xuất hiện hộp chọn vùng dữ liệu: Vì vùng dữ liệu đã chọn ở bước 1, nên ta chọn [Next] để sang bước kế tiếp (chọn kiểu biểu đồ), hoặc chọn [Finish] để kết thúc (dùng dạng mặc định của biểu đồ), sau này có thể tạo dạng lại. Nếu vùng dữ liệu xuất hiện ở đây chưa đúng thì có thể dùng mouse để chọn lại vùng khác. Ÿ Bước 4: Chọn kiểu biểu đồ và chọn một dạng con của nó Ví dụ, kiểu Column có các dạng như: Ÿ Bước 5: Cung cấp các thông tin bổ sung, như: - Dữ liệu được tổ chức theo hàng hay cột (Data Series in Rows/Columns) - Trong vùng dữ liệu nếu có chứa nhãn thì chỉ ra số thứ tự của hàng hay cột để tạo nhãn cho các trục ở các giá trị [x] và [y], nếu không thì nhập số 0 cho các giá trị này: Use First [x] Row(s) for Category (X) Axis Labels Use First [y] Column(s) for Series(Y) Axis Labels - Tùy chọn việc bổ sung một Legend vào biểu đồ 26 Hoàng Vũ Luân
  27. - Nhập tiêu đề cho biểu đồ và tiêu đề cho các trục. 4.3.Hiệu chỉnh, tạo dạng biểu đồ Sau khi chọn [Finish], một biểu đồ sẽ xuất hiện trong vùng chữ nhật mà ta đã xác định ở bước 2. Ta có thể tiếp tục điều chỉnh, tạo dạng, thêm/loại bỏ các thành phần của biểu đồ. Để thực hiện, chỉ cần nhấn D-click vào các vùng thích hợp và điều chỉnh thông tin trong các hộp thoại tương ứng. Ví dụ, có thể thay đổi các thành phần như: kích thước, màu sắc, font chữ, loại bỏ các đường lưới, sửa đổi hoặc bổ sung dữ liệu; thay đổi kiểu biểu đồ theo dạng phù hợp  Riêng trong Excel-97 (hoặc 2k3), công cụ tạo biểu đồ đơn giản hơn. Người sử dụng không cần thiết phải vẽ trước vùng sẽ chứa biểu đồ (ở Bước 2) vì Excel sẽ tự động tạo ra. Ngoài ra, Excel-97 còn bổ sung thêm nhiều dạng biểu đồ. Dưới đây giới thiệu các hộp thoại của Chart Wizard trong Excel-97: * Bước 1 (của 4 bước): chọn kiểu Chart - Trong bước này, ta chọn một kiểu chart chính và sau đó chọn trong tập các kiểu con của nó một kiểu thích hợp. Hình dưới minh họa kiểu Column và các kiểu con của nó. * Bước 2: Chỉ định nguồn dữ liệu sẽ sử dụng trong Chart -Ở đây lưu ý rằng, nếu trước khi thực hiện lệnh tạo biểu đồ ta đã chọn hoặc đứng trong vùng dữ liệu thì Excel sẽ tự động đánh dấu xác định vùng dữ liệu. - Thường thì Excel phân tích đúng về dãy giá trị của dữ liệu theo cột (Series in Columns) hay theo dòng (Series in Rows), trong trường hợp cần thiết ta có thể chỉ định lại giá trị này. TIN HỌC ỨNG DỤNG - MICROSOFT EXCEL - BÀI 4 27
  28. * Bước 3: Bổ sung các tùy chọn -Ở bước này có nhiều tùy chọn để ta có thể thay đổi, bổ sung vào biểu đồ những thành phần theo yêu cầu. * Bước 4: Tùy chọn vị trí đặt biểu đồ -Ở đây ta có thể chỉ định vị trí của biểu đồ trong các Sheet đã có hay là tạo một Sheet khác để chứa biểu đồ.  Sau khi nhấn Finish ta có kết quả sau: 28 Hoàng Vũ Luân
  29. 5 0 0 0 0 0 B IÏÍU À Ö Ì LÛ Ú N G 4 0 0 0 0 0 3 0 0 0 0 0 2 0 0 0 0 0 1 0 0 0 0 0 0 A n T h u ãy S ú n H û ú n g H u ân g * Ta có thể dễ dàng hiệu chỉnh các phần tử trong biểu đồ bằng cách nhấn D-click vào vị trí cần tác động. * Giới thiệu công cụ tạo dạng biểu đồ: TIN HỌC ỨNG DỤNG - MICROSOFT EXCEL - BÀI 4 29
  30. BÀI 5 CƠ SỞ DỮ LIÊU TRONG EXCEL 5.1.Khái niệm về cơ sở dữ liệu (Data Base) CSDL (còn gọi là bảng dữ liệu) là tập hợp có cấu trúc các thông tin có liên hệ với nhau, được tổ chức theo một nguyên tắc nhất định nhằm phản ánh thuộc tính của một lớp đối tượng. Có các mô hình tổ chức CSDL như: mô hình phân cấp; mô hình mạng; mô hình quan hệ Trong đó, mô hình quan hệ có thể được biểu diễn bởi mảng 2 chiều, tổ chức thành hàng và cột. Mỗi hàng chứa thông tin về một đối tượng được gọi là một mẫu tin (record), mỗi cột chứa thông tin phản ánh thuộc tính chung của các đối tượng, được gọi là trường dữ liệu (field). Trong Excel, CSDL được tổ chức theo mô hình quan hệ dưới dạng danh sách (list). Danh sách là một dạng đặc biệt của bảng tính, bao gồm một khoảng liên tục các ô. Trong danh sách, hàng đầu tiên chứa tên của các cột, các hàng còn lại chứa dữ liệu về các đối tượng trong danh sách. 5.2.Hướng dẫn tạo danh sách trong Excel Microsoft Excel cung cấp nhiều chức năng thuận tiện trong việc quản lý và phân tích dữ liệu trong một danh sách. Để tận dụng những chức năng này, hãy nhập dữ liệu trong danh sách theo những gợi ý sau: Ÿ Về kích thước và vị trí - Không nên có nhiều hơn một danh sách trong một worksheet. - Nên để tối thiểu là một hàng và cột trống phân cách danh sách với các dữ liệu của bảng tính. Điều này làm cho Excel dễ dàng nhận biết một cách tự động danh sách sẽ làm việc. - Không nên để các hàng không có dữ liệu trong danh sách. - Tránh đặt dữ liệu quan trọng bên trái hoặc phải của danh sách, vì dữ liệu có thể bị che dấu khi tiến hành lọc danh sách. Ÿ Về các nhãn cột: - Nên tạo nhãn cột trong hàng đầu tiên của danh sách. Excel dùng các nhãn để tạo báo cáo, tìm kiếm và tổ chức dữ liệu. - Sử dụng tạo dạng font chữ, chỉnh sắp cho các nhãn cột khác với dữ liệu trong danh sách. Dùng đường viền quanh các ô của nhãn trong hàng đầu tiên để phân cách với vùng dữ liệu. Ÿ Về nội dung: - Thiết kế danh sách sao cho tất cả các hàng có các đề mục tương tự trong cùng một cột. - Tránh thêm vào các khoảng trống ở đầu các ô, vì điều này có ảnh hưởng đến quá trình sắp xếp và tìm kiếm. 30 Hoàng Vũ Luân
  31. - Không dùng các hàng trắng để phân cách nhãn cột với dữ liệu. Ÿ Đặt tên: - Nên đặt tên cho danh sách để thuận tiện trong việc tác động lên danh sách (như tính toán, lọc thông tin ) - Khi chọn vùng dữ liệu của danh sách để đặt tên cần chú ý phải chọn cả dòng đầu tiên trong danh sách có chứa nhãn cột. 5.3.Các hàm CSDL Excel cung cấp nhiều hàm để làm việc với cơ sở dữ liệu dạng danh sách. Các hàm này đều có chung một cấu trúc cú pháp, chỉ khác nhau về chức năng. 1. Cấu trúc tổng quát của hàm CSDL Dfunction(database,field,criteria) - Tên hàm bắt đầu bằng ký tự D, tiếp đó là các tên như SUM, MIN, (ví dụ: DSUM) - database là một CSDL dạng danh sách của Excel, thông thường là một tên đã định nghĩa trước của danh sách cần tác động. - field là tên nhãn cột đóng trong cặp nháy kép hoặc số thứ tự của cột trong danh sách (tính từ cột 1) hoặc là tên tham chiếu của nhãn cột sẽ bị tác động bởi hàm (ví dụ: tính toán trên một cột của danh sách). - criteria là vùng điều kiện xác định các điều kiện cần thiết mà hàm phải thỏa mãn để tác động lên trường dữ liệu đã chỉ ra bởi field. Ÿ Chức năng: Hàm CSDL tác động lên trường dữ liệu (filed) của danh sách (database) theo các điều kiện xác định bởi vùng điều kiện (criteria). 2. Tạo vùng điều kiện để sử dụng với các hàm CSDL Criteria là một tham chiếu đến một khoảng các ô có chứa các điều kiện đặc biệt cho hàm. Hàm CSDL sẽ trả lại kết quả tính toán trên một cột của danh sách phù hợp với những ràng buộc được chỉ ra bởi vùng điều kiện. Ở vùng điều kiện thường chứa một nhãn cột đại diện các giá trị trong cột tham gia vào điều kiện. Tham chiếu đến vùng điều kiện có thể được nhập vào hàm như một khoảng ô hoặc thông qua tên đã gán cho khoảng ô này. ŸDạng tổng quát của vùng điều kiện: Tên trường (nhãn cột) ví dụ: LƯƠNG điều kiện >= 525000 Trong ô chứa điều kiện có thể sử dụng các toán tử quan hệ: >, =, , = hoặc các ký tự thay thế ?, * tương tự như trong hệ điều hành MS-DOS (ví dụ: điều kiện X* nghĩa là dữ liệu dạng chuỗi bắt đầu bởi X, các ký tự còn lại tùy ý). Để tìm chính xác các giá trị kiểu chuỗi, ta sử dụng dạng: = “=giá trị_chuỗi”. Lưu ý rằng, kết quả của các hàm chuỗi (Left, Right, Mid) là kiểu chuỗi. Vùng điều kiện có thể chứa nhiều ô Tên trường và nhiều điều kiện khác nhau có thể đặt cùng hàng hoặc khác hàng. Các điều kiện bố trí cùng hàng mang nghĩa của toán tử AND; các điều kiện bố trí trên nhiều hàng mang nghĩa OR. Ví dụ: TIN HỌC ỨNG DỤNG - MICROSOFT EXCEL - BÀI 5 31
  32. LƯƠNG LƯƠNG >= 350000 = 350000 và (AND) LƯƠNG = 350000 Đại học =350000 và (AND) TĐVH là Đại học hoặc (OR) LƯƠNG <= 250000 và TĐVH là bất kỳ (vì ô tương ứng không chứa giá trị điều kiện). F Lưu ý: Vùng điều kiện có thể được tổ chức ở những hàng đầu tiên của bảng tính, sau này có thể dấu (hide) chúng đi mà không làm ảnh hưởng đến dữ liệu trong bảng tính. Hoặc có thể tổ chức chúng ở một sheet khác với sheet chứa danh sách. 3. Giới thiệu một số hàm CSDL thông dụng của Excel 1. DSUM(database, field, criteria) Hàm DSUM tính tổng cột field trong database theo điều kiện được chỉ ra bởi criteria. Ví dụ: Trong một danh sách lương, có thể tính tổng của cột Tổng Lương theo điều kiện Lương Cơ Bản hoặc theo Trình Độ Văn Hóa 2. DMAX, DMIN, DAVERAGE Các hàm này trả lại giá trị lớn nhất (max), nhỏ nhất (min), trung bình (average) của một cột dữ liệu (field) trong một danh sách theo điều kiện (criteria) xác định. 3. DCOUNT(database, [field,] criteria) DCOUNTA(database, [field,] criteria) Hàm DCOUNT đếm số các ô có chứa giá trị số; DCOUNTA dùng để đếm các ô khác trống (nghĩa là đếm các ô có chứa dữ liệu) trong một cột của danh sách hợp với điều kiện chỉ định bởi vùng điều kiện. Điểm đặc biệt ở đây là: đối số [filed,] có thể bị bỏ qua, khi đó hai hàm này sẽ tiến hành đếm trong tất cả các record (hàng) của danh sách. 4. Ví dụ về hàm CSDL Giả sử có danh sách dữ liệu sau (từ B2 đến D7 - xem bảng minh họa) Ta có thể tính tổng cộng của cột TONG theo điều kiện mặt hàng là loại GAO, như sau: = DSUM(B2:D7, “TONG”, B25:B26) (nhập tên trường) hoặc = DSUM(DATA10, 3, B25:B26) (số thứ tự cột) hoặc = DSUM(DATA10, D2, CRT10) (số hiệu ô) (Trong đó DATA10 là tên của danh sách dữ liệu; CRT10 là tên của vùng điều kiện B25:B26) A B C D 1 2 TEN SLUONG TONG 3 BOT 324 1145340 4 GAO 454 6878100 32 Hoàng Vũ Luân
  33. 5 BOT 656 3312800 6 GAO 431 1318860 7 GAO 455 4641000 25 TEN SLUONG 26 GAO >450 27 BOT Hoặc bổ sung điều kiện SLUONG > 450, khi đó sử dụng vùng điều kiện là B25:C26. Để tính với điều kiện mặt hàng là GAO hoặc BOT, ta dùng vùng B25:B27. Để tính số lượng trung bình của mặt hàng GAO, ta dùng công thức: = DAVERAGE(BANG10, “SLUONG”, CRT10) và = DMAX(BANG10, “TONG”, CRT10) tính tổng lớn nhất theo GAO. 5. Ví dụ về thiết lập vùng điều kiện - Điều kiện ngày ở trong một tháng nào đó: NGAY NGAY >=01/05/99 <01/06/99 nghĩa là ngày thuộc phạm vi tháng 5/1999 - Giả sử mã số Xij có ký tự đầu đại diện cho khu vực (với X là A, B, C ), i, j là hai số tùy ý. Khi đó điều kiện thuộc khu vực A là: MSO A* - Nếu điều kiện không thuộc vào một trường, ví dụ chỉ có trường NgSinh (ngày sinh), nhưng yêu cầu tính theo tuổi thì thường chúng ta phải tạo thêm một cột trung gian (ví dụ cột Tuoi) và lập điều kiện theo cột mới tạo này. Nhưng cần chú ý khi chọn danh sách dữ liệu để tính thì phải bao gồm luôn cả cột mới bổ sung này, nếu không Excel sẽ báo lỗi là tên chưa được định nghĩa.  Trong Excel còn có một dạng đặc biệt có thể chứa công thức ngay trong vùng điều kiện. TIN HỌC ỨNG DỤNG - MICROSOFT EXCEL - BÀI 5 33
  34. BÀI 6 CÁC THAO TÁC TRÊN DANH SÁCH DỮ LIÊU Hầu hết các thao tác trên danh sách dữ liệu của Excel được thực hiện thông qua menu DATA. Bao gồm các chức năng như: - Sort Sắp xếp dữ liệu trong danh sách - Filter Lọc thông tin trong danh sách và có thể trích ra một danh sách con với những điều kiện xác định - Subtotal Tạo một cách tự động các dòng tính sum, min, max, avarage trên dữ liệu thỏa điều kiện chỉ định. - Form Chuyển danh sách dữ liệu thành một dạng khác, cho phép thao tác nhanh hơn trong một số tình huống. - Pivot table Tạo bảng phân tích số liệu tự động từ danh sách. 6.1.Sắp xếp dữ liệu ([DATA]\SORT) Chức năng sắp xếp nhằm mục đích tổ chức lại bảng dữ liệu theo một thứ tự nào đó. Khi thực hiện chức năng này, bảng dữ liệu sẽ bị thay đổi nhưng các công thức bên trong sẽ được tự động thay đổi cho phù hợp. Có hai loại thứ tự: theo chiều tăng (Ascending) và theo chiều giảm (Descending). Việc sắp xếp thường tiến hành theo một tiêu chuẩn của một cột nào đó (được gọi là trường khóa) và có thể chọn nhiều khóa để sắp đồng thời. Các bước tiến hành trong Excel như sau: 1. Đặt con trỏ trong vùng dữ liệu của danh sách, hoặc chọn vùng dữ liệu cần sắp 2. Gọi menu [Data] và chọn chức năng Sort. Xuất hiện bảng sắp xếp như sau: - Để sắp dữ liệu trong danh sách ta chọn một tên cột, nếu trong mục My list has (Trong danh sách có) có đánh dấu (Ÿ) Header row (dòng tiêu đề của cột) làm khóa sơ cấp để sắp xếp (nhập tên vào hộp [Sort by], hoặc chọn từ danh sách rơi xuống); sau đó có thể tùy chọn 1 hoặc 2 cột làm khóa thứ cấp [Then by]. Trong các khóa, đánh dấu vào ô Ascending để sắp dữ liệu theo chiều tăng dần; Descending để sắp giảm dần. Nếu cần, có thể thêm thông tin bằng cách chọn [Options]: đánh dấu vào 34 Hoàng Vũ Luân
  35. ô [Case sensitive] để phân biệt chữ hoa–thường; và chọn hướng sắp: từ trên xuống (Sort top to bottom) hoặc từ trái sang phải (Sort left to right). - Dữ liệu trước khi sắp: BẢNG THANH TOÁN TIỀN CÔNG STT MSO CVIEC D-MUC TH-HIEN TIEN-CONG THUONG TONG GCHU 1 XA Sửa chữa 3 3 360000 36000 396000 2 YA Bảo trì 2 3 300000 0 300000 3 XB Sửa chữa 6 5 800000 80000 880000 X 4 ZA Thay thế 4 7 1050000 0 1050000 X 5 ZB Thay thế 2 4 800000 0 800000 6 YB Bảo trì 8 7 910000 91000 1001000 X 7 XA Sửa chữa 5 5 600000 60000 660000 8 YA Bảo trì 3 2 200000 20000 220000 X 9 XA Sửa chữa 4 6 720000 0 720000 - Dữ liệu sau khi sắp theo cột CVIEC tăng dần và TONG giảm dần BẢNG THANH TOÁN TIỀN CÔNG STT MSO CVIEC D-MUC TH-HIEN TIEN-CONG THUONG TONG GCHU 6 YB Bảo trì 8 7 910000 91000 1001000 X 2 YA Bảo trì 2 3 300000 0 300000 8 YA Bảo trì 3 2 200000 20000 220000 X 3 XB Sửa chữa 6 5 800000 80000 880000 X 9 XA Sửa chữa 4 6 720000 0 720000 7 XA Sửa chữa 5 5 600000 60000 660000 1 XA Sửa chữa 3 3 360000 36000 396000 4 ZA Thay thế 4 7 1050000 0 1050000 X 5 ZB Thay thế 2 4 800000 0 800000 6.2.Lọc dữ liệu ([DATA]\FILTER) Lọc dữ liệu nhằm mục đích cho phép xem hoặc lấy các thông tin từ bảng dữ liệu mà thỏa mãn các điều kiện nào đó. Trong Excel có hai phương pháp, đó là lọc tự động và lọc nâng cao. Tùy vào mục đích sử dụng ta có thể chọn một trong hai phương pháp này. Nhưng tại một thời điểm, chỉ có một danh sách được phép lọc tự động. Đầu tiên, chọn vùng dữ liệu cần lọc thông tin ra; sau đó chọn menu [Data]\Filter. Chức năng này có các tùy chọn sau: 1. Bật/tắt lọc tự động (AutoFilter) Khi chọn mục này, bên phải các ô trong danh sách sẽ xuất hiện một dấu drop- down []. TIN HỌC ỨNG DỤNG - MICROSOFT EXCEL - BÀI 6 35
  36. Trong mỗi dấu là một danh sách các nhãn dữ liệu có mặt trong cột dữ liệu và các từ khóa: All (chọn toàn bộ), Top 10 (hiện 10 dòng đầu), Custom (tự điều chỉnh điều kiện lọc) Ví dụ, click vào ô [CVIEC] ở trên, và chọn giá trị Bảo trì thì dữ liệu sẽ được lọc và chỉ hiện ra các hàng thỏa điều kiện lọc CVIEC = “Bảo trì”. Tiến hành tương tự với các cột khác ta sẽ kết hợp được các điều kiện cần thiết để lấy thông tin theo yêu cầu. Trong trường hợp điều kiện lọc phức tạp (không đúng với một giá trị trong các nhãn dữ liệu), khi đó ta có thể tùy biến điều kiện bằng cách chọn Custom sau khi nhấn vào  và nhập điều kiện vào hộp sau: Trong ô đầu tiên, ta chọn các toán tử quan hệ như , = , ở ô tiếp theo bên phải ta nhập (hoặc chọn) giá trị theo yêu cầu. Khi điều kiện có dạng như >= một giá trị và <= một giá trị khác thì ta tiếp tục chọn toán tử logic: And hoặc Or để tạo điều kiện dạng kết hợp và nhập thông tin vào 2 ô dưới. Lưu ý, tương tự khi sử dụng điều kiện của các hàm cơ sở dữ liệu, ở đây ta có thể nhập thêm các dấu thay thế ? (đại diện 1 ký tự) và * (đại diện một dãy ký tự tùy ý). Ÿ Sau khi đã lọc, có thể hủy bỏ sự lọc bằng cách chọn lại [Data]\Filter\AutoFilter (chức năng bật/tắt) hoặc chọn [Data]\Filter\Show All (hiển thị tất cả) để cho hiện lại toàn bộ danh sách và tiến hành lọc theo hướng khác. 36 Hoàng Vũ Luân
  37. Ÿ Đặc điểm của lọc tự động là: - Tiến hành lọc ngay trong danh sách dữ liệu. Kết quả sau lọc có thể được copy sang một vùng khác. Tốc độ lọc nhanh, dễ thay đổi điều kiện lọc. - Vùng điều kiện lọc hạn chế, không tiến hành được với một số loại điều kiện phức tạp. Đặc biệt chỉ cung cấp tối đa 2 biểu thức đối với một trường dữ liệu. Để khắc phục điều này, Excel cung cấp một khả năng bổ sung, đó là lọc nâng cao. 2. Lọc nâng cao (Advanced Filter) Khi chọn chức năng này, sẽ xuất hiện hộp đối thoại sau: *Ở mục Action (hành động) ta có 2 tùy chọn: () Filter the list, in-place ® lọc danh sách và đặt tại chỗ (tương tự AutoFilter) () Copy to another location ® cho phép copy dữ liệu sau khi lọc sang một vùng khác * Trong hộp [List range] (vùng danh sách), ta chọn vùng dữ liệu sẽ lọc, hoặc nhập tên của danh sách dữ liệu đã đặt trước đó. * Hộp [Criteria range] yêu cầu nhập vào đó vùng điều kiện để điều khiển quá trình lọc. Vùng điều kiện này tương tự vùng điều kiện trong đối số của các hàm database. *Nếu mục Copy to another location được đánh dấu, thì hộp [Copy to] sẽ có hiệu lực để ta nhập vào đó tham chiếu đến ô đầu tiên của một vùng bảng tính sẽ chứa dữ liệu sau khi lọc. *Đánh dấu vào hộp kiểm tra [ ] Unique records only để điều khiển việc lọc sẽ chỉ tác động lên những record hay mẫu tin đơn nhất, không trùng nhau. Trong trường hợp hộp này để trống thì trong kết quả lọc được phép chứa các record có dữ liệu trùng nhau. Ÿ Lưu ý: - Khi sử dụng chức năng lọc dữ liệu để trích thông tin sang một vùng bảng tính khác với vùng chứa dữ liệu (thông thường là khác sheet) thì trước khi gọi menu Advanced Filter cần di chuyển đến đứng tại Sheet dự định sẽ chứa thông tin kết quả. Khi đó nếu danh sách dữ liệu và vùng điều kiện chưa được đặt tên và cần chỉ ra trực tiếp, ta trước hết click mouse vào tên sheet chứa danh sách, sau đó mới dùng mouse để chọn vùng. - Khi chọn vị trí (Copy to) sẽ chứa dữ liệu, lưu ý rằng vùng phía dưới và bên phải của vị trí này cần có đủ chỗ trống tối thiếu chứa đủ dữ liệu kết quả, trong trường hợp không đủ vùng trống cần thiết thì có thể sẽ ghi đè lên thông tin đã có trước đó. TIN HỌC ỨNG DỤNG - MICROSOFT EXCEL - BÀI 6 37
  38. - Thông tin sau khi lọc và copy đến một vị trí mới chỉ chứa các kết quả của các công thức tính toán, do đó không thể tự động thay đổi được. Khi dữ liệu trong bản gốc có thay đổi thì phải tiến hành lọc lấy kết quả mới để cập nhật số liệu. Do đó, việc trích (lọc) thông tin chỉ có tác dụng tại thời điểm cần lập báo cáo. - Đối với việc lọc và đặt tại chỗ (in-place) thường sử dụng để kiểm tra, so sánh thông tin trong danh sách Khi này, các hàng không thỏa điều kiện lọc sẽ bị che dấu (hidden), để tái hiện lại danh sách đầy đủ, ta chọn [Data]\Filter\Show All. 6.3.Thống kê theo nhóm ([DATA]\SUBTOTALS) Trong một danh sách, ta có thể thống kê số liệu trên các cột dựa trên giá trị cùng loại (cùng chủ đề) của một cột dữ liệu làm khóa thống kê. Ví dụ, trong danh sách giáo viên cần thống kê tổng lương, tổng thưởng theo các nhóm giáo viên; hoặc trong danh sách bán hàng cần thống kê lượng hàng bán được của từng mặt hàng Để thực hiện những yêu cầu này, Excel cung cấp một công cụ khá mạnh, đó là chức năng SubTotal trong menu DATA. F Do phải thống kê theo chủ đề, nên trước khi sử dụng chức năng này danh sách cần phải được sắp thứ tự theo chủ đề. Ÿ Ví dụ với danh sách BẢNG THANH TOÁN TIỀN CÔNG: (đã sắp thứ tự theo CVIEC) STT MSO CVIEC D-MUC TH-HIEN TIEN-CONG THUONG TONG GCHU 6 YB Bảo trì 8 7 910000 91000 1001000 X 2 YA Bảo trì 2 3 300000 0 300000 8 YA Bảo trì 3 2 200000 20000 220000 X 3 XB Sửa chữa 6 5 800000 80000 880000 X 9 XA Sửa chữa 4 6 720000 0 720000 7 XA Sửa chữa 5 5 600000 60000 660000 1 XA Sửa chữa 3 3 360000 36000 396000 4 ZA Thay thế 4 7 1050000 0 1050000 X 5 ZB Thay thế 2 4 800000 0 800000 Để tính tổng tiền thưởng và tổng tiền theo tên công việc ta tiến hành các bước sau: Bước 1: Sắp thứ tự danh sách theo cột CVIEC (kết quả thể hiện ở hình trên) Bước 2: Chọn chức năng [DATA]\SUBTOTALS 38 Hoàng Vũ Luân
  39. * Thay thế các subtotals hiện thời (nếu trước đó đã thực hiện lệnh này) * Ngắt trang giữa các nhóm * Đặt giá trị thống kê bên dưới dữ liệu (nếu chọn) hoặc đặt bên trên dữ liệu (nếu không đánh dấu) * Xóa các thống kê đã tạo trước đó. -Ở mục [At each change in:] ta chọn giá trị của cột sẽ dùng làm chủ đề khi thống kê -Ở mục [Use function:] ta chọn loại hàm sẽ thực hiện chức năng thống kê. -Ở mục [Add subtotal to:] ta đánh dấu vào các cột sẽ thực hiện sự thống kê Trong các hộp kiểm tra ta đánh dấu vào chức năng phù hợp. Trong ví dụ này, ta chọn hàm SUM làm hàm thống kê, các cột cần thống kê là THUE và TONG, chủ đề là TEN; cho phép thay thế subtotal đã tạo trước đó và giá trị thống kê được đặt bên dưới dữ liệu. Chọn [OK] ta có kết quả sau: Bên dưới các nhóm hàng là các nhãn do Excel tự động tạo ra, ta có thể sửa đổi tùy ý. Chú ý ở bên trái có xuất hiện các dấu hiệu đặc biệt - đó chính là khả năng tự động lập nhóm (outline) của Excel. Để mở rộng hoặc thu hẹp các nhóm, ta click vào các dấu [+], [-] hoặc chọn các mức 1, 2, 3 ở phía trên của chúng. Giả sử nếu ta chỉ cần số liệu tổng quát về doanh số bán của các mặt hàng và số liệu tổng toàn bộ, ta sẽ chọn số 2, khi đó danh sách thu hẹp lại chỉ còn 4 hàng (3 hàng Total và 1 hàng Grand Total). Sau khi thống kê, ta có thể điều chỉnh hoặc xóa bỏ bằng cách chọn lại chức năng này và chọn phương pháp phù hợp (ví dụ: chọn [Remove All] để xóa subtotal). TIN HỌC ỨNG DỤNG - MICROSOFT EXCEL - BÀI 6 39
  40. 6.4.Sử dụng [Data]Forms Danh sách dữ liệu được tổ chức theo hàng và cột, bình thường ta làm việc với toàn bộ danh sách. Trong trường hợp cần làm việc với từng hàng (record) ta sẽ gặp khó khăn nếu danh sách có nhiều trường. Excel cung cấp một dạng thức đặc biệt - đó là Data Forms, chuyên dùng để nhập hoặc xem thông tin theo từng hàng, trong đó các trường (nhãn cột) được bố trí theo chiều dọc. Trước khi dùng chức năng Data Forms, dữ liệu của bạn cần được tổ chức theo kiểu danh sách với hàng đầu tiên chứa các nhãn mà Excel sẽ sử dụng làm tên trường (field) trong forms. Tối đa Excel có thể hiển thị đồng thời 32 trường. Các chức năng trong khi dùng Forms gồm: a. [New] thêm một record vào cuối danh sách (chèn thêm một hàng) b. [Delete] xóa một record c. [Restore] phục hồi nội dung đã bị sửa đổi d. [Find] tìm kiếm e. [Criteria] lập điều kiện Đối với các trường công thức, Excel chỉ hiển thị nhưng không cho phép thay đổi. Khi thêm một record vào cuối danh sách, Excel sẽ tự động sao chép công thức ở các trường của record phía trên vào các trường này; các trường chứa dữ liệu được nhập bình thường. Dùng phím Tab và Shift-Tab để di chuyển giữa các trường. Để tìm kiếm thông tin theo một tiêu chuẩn nào đó, trước hết ta dùng nút Criteria để nhập điều kiện vào các trường, sau đó sử dụng các nút tìm trước (Find Prev) và kế tiếp (Find Next) để di chuyển đến vị trí mong muốn. Điều kiện tìm kiếm ở đây đơn giản hơn điều kiện ở chức năng Filter, mỗi biểu thức trường chỉ chứa một giá trị để so sánh, ta nhập giá trị với các toán tử so sánh vào các ô để tạo biểu thức tìm. 6.5.Phân tích bảng dữ liệu [Data]Pivot Table Pivot Table là một chức năng mạnh của Excel, nó cho phép người sử dụng phân tích với một số lượng lớn các dữ liệu một cách nhanh chóng. Ta có thể xoay các hàng và cột để xem các phân tích khác nhau của nguồn dữ liệu, lọc dữ liệu bằng cách hiển thị các trang hoặc xem chi tiết các vùng cần thiết. Ta có thể tạo bảng phân tích từ danh sách dữ liệu của Excel hoặc từ một nguồn dữ liệu bên ngoài Excel. Ta sẽ minh họa chức năng này bằng bảng dữ liệu làm ví dụ sau: STT NBAN TEN SLUONG TGIA THUE TONG 1 05/06/99 BAP 324 1134000 11340 1145340 2 07/06/99 BIA 454 6810000 68100 6878100 40 Hoàng Vũ Luân
  41. 3 09/06/99 BOT 656 3280000 32800 3312800 4 05/06/99 GAO 431 1293000 25860 1318860 5 07/06/99 KEO 455 4550000 91000 4641000 6 05/06/99 BAP 564 1974000 19740 1993740 7 07/06/99 BIA 657 9855000 98550 9953550 8 09/06/99 BOT 432 2160000 21600 2181600 9 05/06/99 KEO 544 5440000 108800 5548800 10 09/06/99 GAO 767 2301000 46020 2347020  Bước 1: Khai báo nguồn dữ liệu Sau khi thực hiện lệnh Pivot Table ta sẽ khai báo nguồn dữ liệu sử dụng để phân tích, có nhiều tùy chọn nhưng thường thì ta chọn mục đầu: () Microsoft Excel list or database. Sau đó nhấn Next để sang bước 2.  Bước 2: Chỉ định bảng dữ liệu Nếu trước đó chưa chọn bảng thì sau khi xuất hiện hộp thoại, dùng mouse chọn danh sách dữ liệu cần phân tích. Nhấn [Next] để sang bước 3 của Wizard.  Bước 3: Chọn vị trí cho các trường cần phân tích Bước này quyết định kiểu phân tích sẽ tiến hành trên bảng dữ liệu. Tùy theo ý đồ, ta sẽ thiết lập các vị trí thích hợp cho các trường, bao gồm 4 vị trí: TIN HỌC ỨNG DỤNG - MICROSOFT EXCEL - BÀI 6 41
  42. [PAGE] dữ liệu đặt ở đây thường là loại có phân nhóm, ở danh sách dữ liệu làm ví dụ ta sẽ chọn trường TEN để đưa vào đây. Khi phân tích ta có thể chọn All để xem toàn bộ hoặc chỉ chọn từng nhóm để xem. [COLUMN] và [ROW] chọn các trường sẽ cung cấp thông tin và tùy ý bố trí theo cột (column) hay hàng (row). [DATA] là vùng chính của bảng, ở đó số liệu cần quan tâm nhất sẽ hiển thị và được tính toán tự động theo tùy chọn (bao gồm tính tổng sum, min, max, average ) tương tự chức năng subtotal. Sau khi hoàn tất chọn Next để chuyển sang bước 4.  Bước 4: Chọn vị trí đặt bảng phân tích Ở bước này có 2 tùy chọn, hoặc ta sẽ lưu bảng phân tích trong một worksheet mới, hoặc là sẽ đặt chung với danh sách dữ liệu. Nếu chọn mục 2: Existing worksheet, ta cần nhập vào vị trí sẽ lưu (tương tự chức năng Copy to trong khi lọc nâng cao) Đến đây ta có thể chọn [Finish] để hoàn tất việc tạo bảng phân tích hoặc chọn [Options ] để bổ sung các tùy chọn trước khi kết thúc. Với danh sách dữ liệu ở trên, ta có thể tạo bảng phân tích như sau: 42 Hoàng Vũ Luân
  43.  Các tùy chọn của bảng phân tích: Thêm các mục tóm tắt dữ liệu (sử dụng các hàm trong subtotal: sum, min, max, average ) ở cuối các cột hoặc cuối hàng dữ liệu và tự động tạo dạng bảng TIN HỌC ỨNG DỤNG - MICROSOFT EXCEL - BÀI 6 43
  44.  Điều chỉnh bảng phân tích - Sau khi tạo xong, ta có thể điều chỉnh, định lại dạng của bảng phân tích bởi các thao tác như: thiết lập lại các tùy chọn như ở trên; hoặc bổ sung, điều chỉnh vị trí các mục, xóa các mục không cần thiết - Nhấn D-click vào tên một trường sẽ làm xuất hiện hộp thay đổi các thuộc tính của nó, như thay đổi vị trí của trường 44 Hoàng Vũ Luân
  45. BÀI 7 CÁC CHỨC NĂNG BỔ SUNG Phần này nhằm mục đích giới thiệu một số tính năng bổ sung nhằm nâng cao hiệu quả khi sử dụng Excel. Một số mục chỉ có tính giới thiệu làm cơ sở để tiếp tục tự tìm hiểu. 7.1.Định dạng trang: [File] Page Setup Trong mục này có các chức năng giống với Word như khai báo giấy (Page), thiết lập các lề (Margins), tạo các dòng tiêu đề đầu và chân trang (Header/Footer). Ngoài ra, trong Excel có bổ sung một tính năng đặc thù của bảng biểu, đó là cho phép tạo ra các hàng lặp lại ở đỉnh (Rows to repeat at top) và các cột lặp lại ở bên trái (Columns to repeat at left) khi sang trang mới. Nghĩa là có thể tạo các hàng/cột thống nhất trên các trang. Để chọn các hàng/cột sẽ lặp lại khi sang trang, ta chọn hộp thích hợp sau đó dùng mouse chọn các hàng trong bảng tính. Trong một số tình huống có thể thiết lập nút [´] Gridlines để in các đường lưới của bảng, ngược lại tắt chức năng này để không in. Nếu muốn Excel tự động chuyển màu sắc của bảng sang dạng đen trắng thì chọn nút [´] Black and white. Ngoài ra, nếu bảng tính có nhiều trang thì ta có thể chỉ định hướng in: in xuống rồi sang phải (Down, then over) hay ngược lại (Over, then down). TIN HỌC ỨNG DỤNG - MICROSOFT EXCEL - BÀI 7 45
  46. 7.2.Xem trước khi in: [File]Print Preview Click vào nút Margins để bật/tắt các dấu hiệu đặt lề và chỉnh cột Chức năng này cho phép kiểm tra bảng tính một cách tổng thể trước khi quyết định in chính thức. Ở đây, ta có thể tiến hành nhiều phép hiệu chỉnh, như: thay đổi lề, cột để bảng tính có thể vừa khít trên một trang Nhấn ESC hoặc [Close] để kết thúc xem. 7.3.Chức năng in: [File]Print Các thông số in trong Excel hầu hết đều sử dụng theo chuẩn của Windows, như chọn loại máy in, có muốn in ra file để sau đó đưa đi in ở một máy khác hay không ([] Print to file), chọn khoảng trang sẽ in (All: in toàn bộ, From To để chỉ định các trang sẽ in) Các mục trong vùng Print what (In cái gì?) có chứa một số mục riêng của Excel. Nếu muốn in chỉ một vùng bảng tính thì chọn vùng này trước khi thực hiện lệnh 46 Hoàng Vũ Luân
  47. in, sau đó đánh dấu vào mục Selection. Để in bảng tính ở Sheet đang làm việc thì chọn mục Active sheet(s) và chọn mục Entire workbook để in toàn bộ file (gồm tất cả các sheet). Ngoài ra có thể chọn số bản sao trên một trang (Number of copies) và cách sắp khi in (Collate). 7.4.Sử dụng tính năng [Data]Group and Outline Chức năng này cho phép quản lý các hàng và cột theo nhóm và có thể tạo subtotal một cách tự động. Đầu tiên chọn các hàng/cột, sau đó thực hiện lệnh group sẽ nhóm các hàng/cột này lại với nhau. Muốn hủy nhóm, chọn lệnh Ungroup. Việc group làm cho quá trình xử lý bảng tính đơn giản hơn, đồng thời tạo ra khả năng dấu các số liệu quan trọng 7.5.Quản lý vùng màn hình làm việc Chức năng [Window]Split nhằm chia màn hình thành các vùng khác nhau, giúp cho việc xem xét, nhập dữ liệu đơn giản hơn, vì nó cho phép di chuyển trong các vùng khác nhau một cách độc lập. Sau khi đã phân chia, chọn lệnh Remove split để xóa việc phân chia này. Chức năng [Window]Freeze panes cho phép tạo một vùng cố định trong khi di chuyển trong vùng khác, thường sử dụng đối với các bảng có nhiều hàng hoặc nhiều cột. Lệnh Unfreeze panes để hủy lệnh trước đó. 7.6.Dấu các hàng/cột Khi chọn một nhóm các hàng/cột và nhấn phím phải ta có thể thực hiện nhanh lệnh Hide để dấu các hàng/cột này; ngược lại, lệnh Unhide dùng để hiện lại các vùng đã che dấu. cột B đã được dấu 7.7.Định dạng có điều kiện Ngoài những định dạng thông thường, Excel còn cung cấp một khả năng định dạng nâng cao. Đó tùy theo những điều kiện xác định mà hình thức các ô sẽ được biến đổi phù hợp. TIN HỌC ỨNG DỤNG - MICROSOFT EXCEL - BÀI 7 47
  48. Menu sử dụng là: - Ví dụ: định dạng các ô trong cột TTHU với điều kiện giá trị lớn hơn 100000 thì khung viền bằng nét đứt và chữ trắng, nền đen: + Từ hộp thoại định dạng có điều kiện, ta chọn kiểu điều kiện là: Cell Value Is, trong hộp phép toán, ta chọn so sánh lớn hơn: greater than, trong hộp giá trị ta nhập số 100000. Chọn nút lệnh để thực hiện định dạng, nhấn OK ta được:  Lệnh này cho phép kết hợp đồng thời 3 điều kiện (chọn nút để thêm điều kiện)  Ngoài kiểu xét điều kiện theo giá trị , còn có kiểu cao cấp hơn: xét điều kiện theo công thức 7.8.Kiểm tra dữ liệu khi nhập Với những dữ liệu quan trọng, khâu kiểm tra dữ liệu trong khi nhập rất là cần thiết. Hầu hết các chương trình làm việc với dữ liệu đều có trang bị tính năng này. Trong Excel, điều này được thực hiện qua menu [Data]\Validation 48 Hoàng Vũ Luân
  49. - Hộp [Allow] cho phép chọn kiểu dữ liệu, giá trị Any value cho phép nhập tùy ý như khi chưa đặt. - Hộp [Data] chọn phép toán. Trong minh họa trên, điều kiện là: nhập giá trị số (Decimal) trong khoảng (between) nhỏ nhất (Minimum) là 0, lớn nhất (Maximum) là 100. - Nếu có lỗi khi nhập, nội dung và kiểu thông báo lỗi được chọn ở mục Kiểu (style) báo lỗi là thông báo (Information); tiêu đề (Title) thông báo là "Dữ liệu sai", nội dung thông báo (Error message): "Cần nhập số trong khoảng 0 100". Với khai báo trên, khi nhập giá trị sai, sẽ xuất hiện thông báo sau: TIN HỌC ỨNG DỤNG - MICROSOFT EXCEL - BÀI 7 49
  50. 7.9.Bảo vệ dữ liệu Ngoài việc kiểm tra dữ liệu, Excel còn cung cấp các tính năng bảo mật dữ liệu. Có nhiều cấp độ bảo mật: từ bảo vệ workbook đến sheet và cả nội dung từng ô (cell). Tùy theo mục đích, ta chọn kiểu bảo vệ (Protection) phù hợp. Trong các kiểu, người sử dụng đều nhập một mật khẩu bảo vệ và thiết lập các tùy chọn về quyền của người dùng trên các đối tượng được bảo vệ. 50 Hoàng Vũ Luân
  51. HƯỚNG DẪN THỰC HÀNH MS-EXCEL BÀI MỞ ĐẦU HƯỚNG DẪN THỰC HÀNH EXCEL 1. Khởi động Excel, quan sát màn hình, ghi nhận các thành phần của chương trình, vùng làm việc 2. Thực hiện các thao tác cơ bản như: - Nhập và chỉnh sửa dữ liệu ở các ô tùy ý - Di chuyển giữa các ô. - Chọn ô, cột, hàng (một và nhiều). - Cắt dán dữ liệu. - Chọn toàn bộ và xóa. 3. Kỹ thuật điền dãy số tự động: - Tại ô A4 nhập số 1, chọn ô A4 ® làm xuất hiện nút vuông điều khiển, nhấn Ctrl và kéo nút điều khiển sang phải đến ô G4 thả mouse, thả Ctrl để tạo ra dãy số từ 1 đến 7. Sau đó lần lượt kéo các ô từ A4 đến C4 (kéo 3 lần) xuống đến các hàng A13, B13, C13 để tạo ra một bảng các số. Nhấn D-click vào nút điều khiển của ô D4, E4 và quan sát kết quả. 4. Điền công thức tự động: - Tại ô F5 nhập công thức tính tổng các ô B5:E5 như sau: đầu tiên nhập dấu =, dùng mouse chỉ vào ô B5, nhập dấu +, chỉ vào ô C5 cho đến khi được: = B5+C5+D5+E5, nhấn Enter để hoàn tất. - Chọn ô F5, kéo nút điều khiển xuống đến F13; sau đó chọn lại F5 và kéo sang G5, nhấn D-click ở nút điều khiển của G5. - So sánh 2 thao tác ở trên. Lần lượt chọn các ô F5, F6, F7 và quan sát ở thanh công thức để nhận thấy sự thay đổi tự động của công thức tính. Tương tự đối với các ô ở cột G. 5. Kẻ khung viền, tô bóng - Chọn các ô từ A4 đến G13 tạo khung viền hai nét bên ngoài và 1 nét bên trong. - Chọn A4:G4 (hàng đầu của bảng) và tô màu tùy ý.  Cuối cùng ta có kết quả như sau: HƯỚNG DẪN THỰC HÀNH EXCEL - BÀI MỞ ĐẦU 51
  52.  Ngoài ra, ta có thể thực hành thêm các lệnh như: chỉnh sắp các cột (theo các vị trí trái, giữa, phải) và chỉnh nội dung của một ô ra giữa của khoảng các ô (chọn khoảng sẽ chỉnh giữa, click vào biểu tượng )  Đóng bảng tính (nhấn Ctrl-F4, hoặc chọn [File]Close) và mở một bảng tính mới (New) để thực hành bài số 1. BÀI SỐ 1  Khởi động Excel, từ một Workbook mới hãy tạo bảng dữ liệu sau và thực hiện tuần tự các yêu cầu của bài thực hành. STT HỌ TÊN PHÁI NGÀY SINH LCB LƯƠNG CÒN LẠI 1 An Nam 05/02/65 3.26 2 Thủy Nữ 23/12/64 2.14 3 Hương Nữ 17/03/66 3.12 4 Hùng Nam 09/04/64 2.46 Câu 1 Nhập dữ liệu (cột STT dùng kỹ thuật điền dãy số tự động) Câu 2 Chèn một dòng trước người tên Hương và nhập thêm dữ liệu như sau (điều chỉnh cột STT cho đúng): 3 Sơn Nam 02/11/65 2.56 Câu 3 Chèn một cột trước cột Còn lại và nhập vào tên ô là Tạm ứng Thực hiện tính toán ở các cột như sau: Câu 4 Lương bằng LCB nhân với 144000 Câu 5 Tạm ứng bằng 25% Lương Câu 6 Còn lại bằng Lương trừ Tạm ứng Câu 7 Trang hoàng và lưu bảng tính với tên là BTAP1.XLS Câu 8 Sau khi lưu, đóng và mở lại bảng tính để kiểm tra, đồng thời sử dụng các lệnh tạo dạng như: đổi font, tạo khung viền, màu, chỉnh dạng ; các lệnh sao chép, cắt dán dữ liệu  Kiểm tra lại các khái niệm “tham chiếu tương đối”, “tham chiếu tuyệt đối” và dùng phương pháp đặt tên cho khoảng các ô để đặt tên cho vùng bảng tính ở trên. 52 Hoàng Vũ Luân
  53.  Hướng dẫn thực hành: 1. Thường bắt đầu tạo bảng dữ liệu từ ô A3 trở đi. Trong khi nhập ngày cần lưu ý, nếu nhập ngày đúng dạng thì sẽ tự động chỉnh phải, ngược lại cần kiểm tra xem ngày có dạng mm/dd/yy hay dạng dd/mm/yy để nhập cho đúng. 2. Đặt con trỏ ở ô có STT là 3, nhấn Mouse phải và chọn Insert trong menu rơi xuống sau đó chọn tiếp Entire Row để chèn 1 hàng (hoặc chọn hàng bằng cách click vào số hiệu hàng sau đó dùng lệnh Insert để chèn). 3. Tương tự câu 2, đặt con trỏ ở ô Tạm ứng, nhấn mouse phải và chọn Insert, sau đó chọn Entire Column để chèn 1 cột. 4. Nhập công thức: = [LCB] * 144000, trong đó [LCB] là tham chiếu đến ô chứa LCB (dùng Mouse chỉ vào ô chứa giá trị LCB tương ứng với hàng đang lập công thức, không phải chỉ vào ô có tên là LCB) 5. Nhập công thức: = [Lương] * 25% 6. Nhập công thức: = [Lương] - [Tạm ứng]  Sau khi tạo được công thức trong ô đầu tiên, dùng mouse kéo nút điều khiển của ô này xuống các ô phía dưới để tự động điền công thức trong các ô còn lại (hoặc nhấn D-click nếu có thể). Ta có kết quả như sau: STT HỌ TÊN PHÁI NGÀY SINH LCB LƯƠNG TẠM ỨNG CÒN LẠI 1 An Nam 05/02/65 3.26 469440 117360 352080 2 Thủy Nữ 23/12/64 2.14 308160 77040 231120 3 Sơn Nam 02/11/65 2.56 368640 92160 276480 4 Hương Nữ 17/03/66 3.12 449280 112320 336960 5 Hùng Nam 09/04/64 2.46 354240 88560 265680 BÀI SỐ 2a  Sử dụng các hàm IF, SUM, MAX, MIN, AVERAGE, ROUND Bảng tính lương Tháng 6 năm 2005 STT Họ tên Cvu Lương CB Ngày công Lương Tạm ứng Thực nhận 1 Linh TP 3.16 23 436080 145000 291080 (đ) 2 Vuong NV 2.44 25 366000 122000 244000 (đ) 3 Thuy PP 3.12 26 505440 150000 355440 (đ) 4 Yen GD 5.46 29 1081080 150000 931080 (đ) 5 Tien DV 2.56 30 537600 150000 387600 (đ) 6 Loi PGD 3.92 15 352800 118000 234800 (đ) 7 Hung NV 1.92 18 207360 69000 138360 (đ) 8 Tri NV 3.24 19 369360 123000 246360 (đ) 9 Loan TK 2.92 22 385440 128000 257440 (đ) 10 Nhung DV 3.14 26 508680 150000 358680 (đ) Tổng cộng 4749840 1305000 3444840 (đ) Max = 931080 Min = 138360 Average = 344484 Câu 1 Nhập dữ liệu (cột STT dùng kỹ thuật điền dãy số tự động) Câu 2 Tính Lương = 6000 * Lương CB * Ngày công (Trong đó, nếu ngày công > 25 thì từ ngày 26 trở đi mỗi ngày được tính thành 2 HƯỚNG DẪN THỰC HÀNH EXCEL - BÀI SỐ 2a 53
  54. ngày, ví dụ: nếu ngày công là 27 thì số dư ra là 2 ngày được tính thành 4 ngày, do đó tổng ngày sẽ là 25+4) Câu 3 Tạm ứng = 1/3 Lương (nhưng tối đa chỉ cho tạm ứng 150000đ) Câu 4 Thực nhận = Lương - Tạm ứng Câu 5 Tính tổng cộng các cột Lương, Tạm ứng, Thực nhận Câu 6 Cho biết giá trị cao nhất, thấp nhất, trung bình của Thực nhận Câu 7 Làm tròn cột Tạm ứng đến ngàn đồng Câu 8 Định dạng các cột tiền theo dạng tiền (đ) đồng Việt Nam Câu 9 Trang trí, lưu bảng tính với tên là BTAP2.XLS  Hướng dẫn thực hành: 1. Lưu ý các vùng tô xám là kết quả sau khi tính toán, dùng để kiểm tra. 2. [Lương] = 6000 * [Lương CB] * IF([Ngày công] 150000, 150000, [Lương]/3) Nếu một phần ba số lương không vượt quá 150000 thì cho tạm ứng đúng bằn một phần ba lương, ngược lại chỉ cho tạm ứng 150000. 5. Sử dụng hàm SUM, nhập công thức “= SUM(” và dùng mouse để chọn vùng cần tính tổng. Sau đó kéo sang các ô bên cạnh để tạo công thức tự động cho các tổng kế tiếp. 6. Dùng các hàm MAX, MIN và AVERAGE 7. Sửa công thức trong cột Tạm ứng thành = ROUND([IF( )], -3). Lưu ý giá trị -3 dùng để làm tròn đến hàng ngàn (3 số 0). Sau khi sửa xong, kéo xuống phía dưới để thay thế. 8. Chọn ô, dùng menu [Format]\Cells và sử dụng định dạng: 0 “(đ)”, hoặc dùng định dạng #.##0 “(đ)” để tạo thêm dấu phân cách nhóm 3 số. 9. Tạo khung viền, tô bóng. Dòng tiêu đề nhập ở cột A, sau đó dùng mouse chọn một vùng ngang qua các ô để chỉnh tiêu đề ra giữa các ô.  Chọn Sheet2 và hoàn tất bài tập tương tự số 2b (không có phần hướng dẫn). 54 Hoàng Vũ Luân
  55. BÀI SỐ 2b STT TÊN LOẠI SLƯỢNG ĐGIÁ1 ĐGIÁ2 THTIỀN THUẾ TCỘNG 1 Toán 1 20 3000 3200 60000 6000 66000 2 Lý 1 15 2500 2800 37500 3750 41250 3 Hóa 2 10 4000 4500 45000 4500 49500 4 Sinh 1 5 2000 2300 10000 1000 11000 5 Sử 1 10 3500 3800 35000 3500 38500 6 Địa 2 25 2500 2600 65000 6500 71500 7 Văn 1 15 4500 4700 67500 6750 74250 8 Tiếng Việt 1 10 3000 3300 30000 3000 33000 9 Anh Văn 2 20 5000 5200 104000 10400 114400 10 Giáo dục 1 15 4000 4300 60000 6000 66000 SUM 514000 51400 565400 MIN 10000 1000 11000 MAX 104000 10400 114400 AVERAGE 51400 5140 56540 Câu 1 Tính thành tiền bằng số lượng nhân đơn giá tùy thuộc vào loại (nếu loại 1 thì đơn giá 1, ngược lại nếu là loại 2 thì tính theo đơn giá 2) Câu 2 Tính Thuế bằng 10% của Thành tiền Câu 3 Tính tổng cộng bằng tổng của thành tiền với thuế Câu 4 Tính tổng, giá trị lớn nhất, nhỏ nhất, trung bình của các cột THTIỀN, THUẾ và TCỘNG. BÀI SỐ 3  Sử dụng hàm IF, rèn luyện việc lập các mệnh đề logic làm điều kiện; khả năng lồng nhau của các hàm IF. Dùng hàm RANK để sắp thứ tự (cách dùng tham chiếu tuyệt đối). BẢNG ĐIỂM SAP HOTEN KHOI TOAN VAN NNGU TONG KQUA XLOAI 1 THONG A 9 10 8 36 DAU GIOI 10 SI D 3 2 5 15 ROT X 5 SANG C 7 6 7 26 DAU KHA 7 DAN D 2 9 6 23 ROT X 3 TRI C 6 8 8 30 DAU KHA 4 KHA A 7 7 8 29 DAU KHA 8 TAM A 5 6 4 20 DAU TBINH 2 GIOI D 9 8 8 33 DAU GIOI 6 DAT C 5 6 8 25 DAU TBINH 8 YEU C 1 8 3 20 ROT X Câu 1 Nhập dữ liệu Câu 2 Cột tổng được tính căn cứ vào KHOI: nếu KHOI A thì điểm TOAN nhân 2; KHOI D điểm NNGU nhân 2; KHOI C điểm VAN nhân 2. HƯỚNG DẪN THỰC HÀNH EXCEL - BÀI SỐ 2b 55
  56. Câu 3 Cột KQUA được tính như sau: là ĐẬU đối với TONG lớn hơn hoặc bằng 20, nhưng không có điểm môn nào dưới 3; ngược lại KQUA sẽ là RỚT Câu 4 Cột XLOAI được tính nếu KQUA là ĐẬU và căn cứ vào TONG: - Loại GIOI nếu TONG >= 32 - Loại KHA nếu TONG từ 26 đến dưới 32 - Loại TRBINH đối với trường hợp còn lại + Nếu KQUA là RỚT thì đánh dấu X vào vị trí XLOAI Câu 5 Cột SAP xếp thứ tự theo cột TONG với điểm cao nhất là 1. Câu 6 Trang trí và lưu bảng tính với tên BTAP3.XLS  Hướng dẫn thực hành: 2. Sử dụng hàm IF, ta có công thức sau: IF([KHOI]="A",[TOAN]*2+[VAN]+[NNGU],IF([KHOI]=D,[TOAN]+[VAN]+[ NNGU]*2, [TOAN]+[VAN]*2+[NNGU])) Lưu ý rằng, tùy thuộc vào KHOI để quyết định sẽ nhân hệ số 2 đối với môn thích hợp. Có 3 điều kiện (A, B, C) nên có 2 hàm IF lồng nhau; nếu có n điều kiện thì sẽ có n-1 hàm IF lồng nhau. Các bài tập ở sau sẽ sử dụng các hàm tìm kiếm HLOOKUP và VLOOKUP thay cho việc dùng nhiều hàm IF lồng nhau. 3. Điều kiện để Đậu là: Tổng điểm >= 20, và điểm của từng môn > 2. Ta sử dụng IF với hàm AND để tạo biểu thức điều kiện: IF(AND([TONG]>=20, [TOAN]>2, [VAN]>2, [NNGU]>2), "ĐẬU", "RỚT") 4. Vì KQUA phải ĐẬU, nên đầu tiên cần kiểm tra KQUA, sau đó mới xét điểm để xếp loại: IF([KQUA]="ĐẬU", IF([TONG]>=32, "GIOI", IF([TONG]>=26, "KHA","TRBINH")),"X") Trong công thức trên lưu ý cách tính cận của các giá trị. 5. Dùng hàm RANK để sắp thứ tự. Theo yêu cầu sắp theo thứ tự giảm (điểm cao nhất có thứ hạng 1) nên phương thức sắp bằng 0, ta có: [SAP] = RANK([TONG], danh_sách_điểm, 0) Trong đó danh_sách_điểm là cột (gồm 10 ô) chứa tổng điểm (trong bài là từ ô có điểm 36 đến ô cuối có điểm 20; sau khi dùng mouse chọn các ô xong, nhấn F4 để tạo tham chiếu tuyệt đối, nếu không thì khi sao chép công thức xuống dưới sẽ gây ra lỗi). Trong câu này, ngoài việc sử dụng hàm RANK ta còn lưu ý đến cách dùng của tham chiếu tuyệt đối mà hàm RANK là một trong số những hàm thường dùng kiểu tham chiếu này. BÀI SỐ 4a  Các hàm INT, MOD xử lý số nguyên; các phép toán trên dữ liệu kiểu ngày (hiệu của 2 ngày), định dạng kiểu ngày. Vận dụng hàm tìm kiếm HLOOKUP để tìm một giá trị tham gia vào quá trình tính toán. KHÁCH SẠN BẠCH LIÊN MS LPH NDEN NDI STU SNG TTUAN TNGAY THTIEN 56 Hoàng Vũ Luân
  57. a1 A 06/12/95 06/15/95 0 3 0 đ 330,000 đ 330,000 đ a2 C 06/12/95 06/15/95 0 3 0 đ 225,000 đ 225,000 đ a3 C 06/12/95 06/21/95 1 2 500,000 đ 150,000 đ 650,000 đ a4 B 06/12/95 06/25/95 1 6 600,000 đ 540,000 đ 1,140,000 đ a5 B 06/12/95 06/28/95 2 2 1,200,000 đ 180,000 đ 1,380,000 đ a6 C 06/17/95 06/29/95 1 5 500,000 đ 375,000 đ 875,000 đ a7 A 07/01/95 07/03/95 0 2 0 đ 220,000 đ 220,000 đ a8 A 07/02/95 07/09/95 1 0 700,000 đ 0 đ 700,000 đ a9 C 07/25/95 08/10/95 2 2 1,000,000 đ 150,000 đ 1,150,000 đ a10 B 07/26/95 08/12/95 2 3 1,200,000 đ 270,000 đ 1,470,000 đ Tổng cộng: 5,700,000 đ 2,440,000 đ 8,140,000 đ Loại phòng A B C Đơn giá tiền tuần 700000 600000 500000 Đơn giá tiền ngày 110000 90000 75000  Lưu ý: Trong bảng dữ liệu trên, STU, SNG là số tuần và số ngày lưu trú của khách. TTUAN, TNGAY là tiền trọ theo tuần và theo ngày (vì khách sạn giảm giá đối với khách thuê phòng đăng ký theo tuần). Câu 1 Từ giá trị ngày đến và ngày đi hãy tính số tuần (STU) và số ngày (SNG) lưu trú (ví dụ: 12 ngày thì tính là 1 tuần và 5 ngày). Câu 2 Dựa vào bảng giá tiền theo từng loại A, B, C cho trên, hãy tính số tiền theo tuần và theo ngày của các khách trọ Câu 3 Tính TTIEN bằng tổng của tiền tuần và tiền ngày; tính tổng cộng cho các cột TTUAN, TNGAY và TTIEN Câu 4 Định dạng cho các cột ngày đi và ngày đến theo dạng năm có 4 chữ số (ví dụ 1995) và định dạng cho các cột tiền có dạng #,## đ. Câu 5 Trang trí và lưu với tên BTAP4.XLS  Hướng dẫn thực hành: 1. Một tuần gồm 7 ngày; do đó số tuần bằng phần nguyên của số ngày lưu trú chia cho 7. Số ngày sau khi tính tuần sẽ là phần dư của phép chia 7. Ta có công thức tính như sau: [STU] = INT(([NDI]-[NDEN])/7) [SNG] = MOD([NDI]-[NDEN], 7) 2. Để biết đơn giá (theo loại phòng) ta dò tìm trong bảng giá, và vì bảng giá bố trí số liệu theo chiều ngang nên ta dùng hàm HLOOKUP. Khi đó đơn giá tiền tuần ở hàng thứ 2 và đơn giá tiền ngày ở hàng thứ 3 của bảng tìm. Ta có: [TTUAN] = [STU] * HLOOKUP([LPH], bảng_tìm, 2, 0) [TNGAY] = [SNG] * HLOOKUP([LPH], bảng_tìm, 3, 0) - Trong đó, bảng_tìm là khoảng gồm 9 ô (có khung tô đậm) từ ô có giá trị A đến ô có chứa 75000. Dùng mouse để chọn 9 ô này, sau đó nhấn F4 để tạo tham chiếu tuyệt đối. - Có thể đặt tên cho vùng 9 ô của bảng đơn giá (ví dụ Table4) khi đó trong công thức ta chỉ cần nhập tên Table4 ở vị trí của bảng_tìm. HƯỚNG DẪN THỰC HÀNH EXCEL - BÀI SỐ 4a 57
  58.  Sau khi ghi xong, copy nội dung Sheet1 sang Sheet2 và sửa lại dữ liệu ở bảng tìm thành bảng dọc và dùng VLOOKUP để làm lại câu 2. (để copy sheet, có thể chọn tên Sheet ở dòng chứa tên các Sheet và nhấn Ctrl+Drag kéo sang bên cạnh để copy). BÀI SỐ 4b  Sử dụng hàm tìm kiếm VLOOKUP MSO TEN SLUONG TTIEN GCHU A DOS 40 4800000 X B WORD 20 2800000 C EXCEL 35 4550000 X A DOS 25 3000000 C EXCEL 35 4550000 X B WORD 15 2100000 C EXCEL 40 5200000 X B WORD 25 3500000 A DOS 45 5400000 X Mã số Tên Đơn giá A DOS 120000 B WORD 140000 C EXCEL 130000 Câu 1 Chọn Sheet3 của BTAP4 để nhập dữ liệu. Câu 2 Căn cứ vào bảng chứa tên và đơn giá của mã số để điền thông tin vào cột TEN Câu 3 Tính TTIEN bằng số lượng nhân đơn giá tùy thuộc vào loại, và tạo dạng với đơn vị tiền là $ (dạng #,##0 “$”) Câu 4 Cột GCHU đánh dấu X nếu TTIEN lớn hơn 4000000, ngược lại để trống (Lưu ý chuỗi trống là chuỗi có dạng “” ) Câu 5 Trang trí và ghi lại những thay đổi vừa tạo ra ở Sheet3. 58 Hoàng Vũ Luân
  59. BÀI SỐ 5a  Sử dụng các hàm chuỗi (LEFT, RIGHT, MID ) để trích ra các ký tự dùng trong các hàm tìm kiếm. BÁO CÁO DOANH THU STT MAH TEN SLG TTIEN VCHUYEN TONG 1 XL0 Xăng 50 225,000 đ 0 đ 225,000 đ 2 DS1 Dầu 35 105,000 đ 630 đ 105,630 đ 3 NS3 Nhớt 60 600,000 đ 12,600 đ 612,600 đ 4 DL0 Dầu 35 122,500 đ 0 đ 122,500 đ 5 XS2 Xăng 70 280,000 đ 2,800 đ 282,800 đ 6 XL1 Xăng 50 225,000 đ 1,125 đ 226,125 đ 7 DL3 Dầu 40 140,000 đ 2,520 đ 142,520 đ 8 NL2 Nhớt 30 330,000 đ 4,620 đ 334,620 đ 9 NS0 Nhớt 70 700,000 đ 0 đ 700,000 đ 10 XS3 Xăng 65 260,000 đ 3,900 đ 263,900 đ TONG CONG 2,987,500 đ 28,195 đ 3,015,695 đ Þ Trong đó, ký tự đầu của mã hàng (MAH) đại diện cho mặt hàng (TEN); ký tự thứ hai đại diện cho giá sỉ (S) hay lẻ (L); ký tự cuối là khu vực. (0, 1, 2 và 3). Mối quan hệ được cho trong bảng sau: Mã Tên Giá sỉ Giá lẻ Khu vực 1 Khu vực 2 Khu vực 3 X Xăng 4000 4500 0.50% 1.00% 1.50% D Dầu 3000 3500 0.60% 1.20% 1.80% N Nhớt 10000 11000 0.70% 1.40% 2.10% Câu 1 Căn cứ vào ký tự đầu của MAH và bảng dữ liệu ở trên để điền tên thích hợp vào cột TEN. Câu 2 Tính thành tiền (TTIEN) bằng số lượng (SLG) nhân đơn giá; trong đó đơn giá tùy thuộc vào giá sỉ hay lẻ. Câu 3 Tính tiền vận chuyển (VCHUYEN) với điều kiện: nếu khu vực 0 thì miễn tiền vận chuyển, các khu vực khác tính theo giá trị % của cột thành tiền tương ứng với từng khu vực cho trong bảng. Câu 4 Tính tổng (TONG) bằng thành tiền cộng chuyên chở và tính tổng cộng các cột TTIEN, VCHUYEN, TONG. Sau đó định dạng cho các cột biểu diễn giá tiền theo dạng #,##0 đ. Câu 5 Trang trí và lưu với tên BTAP5.XLS  Hướng dẫn thực hành:  Đối với các bảng dữ liệu dùng để tìm thông tin ta nên gán tên để dễ thao tác và xử lý. Giả sử ta đặt tên cho bảng tìm là Table5. 1. Để lấy ký tự đầu của MAH, ta dùng hàm LEFT. Vì bảng tìm bố trí theo cột nên ta dùng hàm VLOOKUP, cột 2 chứa giá trị tên loại hàng. Ta có: VLOOKUP(LEFT([MAH]), TABLE5, 2, 0) 2. Vì có hai loại giá tùy thuộc vào ký tự thứ hai của MAH, nên ta phải dùng IF để xác định vị trí cột chứa giá thích hợp, nếu giá sỉ thì cột 3 chứa đơn giá, ngược lại HƯỚNG DẪN THỰC HÀNH EXCEL - BÀI SỐ 5a 59
  60. sẽ là cột 4. Dùng hàm MID để lấy các ký tự ở giữa chuỗi. Ta có công thức tính như sau: [TTIEN] = [SLG] * VLOOKUP(LEFT([MAH), TABLE5, IF(MID([MAH),2,1) = "S", 3, 4), 0) 3. Dùng hàm IF kiểm tra điều kiện khu vực là 0 hay <> 0; sau đó dùng VLOOKUP để tính % tương ứng với từng khu vực và nhân với TTIEN. Lưu ý , nếu KV=1 thì cột trả lại là 5, KV=2 thì cột là 6, KV=3 thì cột là 7 Þ KV+4 = số hiệu cột sẽ trả lại. Do đó ta có công thức: IF(RIGHT[MAH]="0", 0, VLOOKUP(LEFT([MAH]), TABLE5, RIGHT([MAH])+4, 0) * [TTIEN]) Biểu thức: RIGHT([MAH])+4 xác định giá trị của cột trả lại tương ứng với từng khu vực. Nếu không nhận xét như trên, ta phải dùng 2 hàm IF lồng nhau để xác định vị trí của cột cần lấy: If(kv=1, 5, if(kv=2, 6, 7))  Sau khi hoàn tất và ghi file, chọn Sheet2 và thực hành bài tương tự 5b: BÀI SỐ 5b BÁO CÁO DOANH THU SAP MSO VATTU NGNHAP SLNHAP TGNHAP SLXUAT TGXUAT GHICHU A1 02/06/99 15 15 C2 04/06/99 20 15 B1 05/06/99 30 25 C1 08/06/99 10 10 A2 15/06/99 25 20 A1 17/06/99 30 25 C2 25/06/99 35 30 B1 27/06/99 20 20 B2 30/06/99 25 20  Trong đó, MSO gồm 2 ký tự, ký tự đầu chỉ tên vật tư (A, B, C) ký tự cuối chỉ loại (1, 2). Bảng dưới đây cho biết đơn giá nhập, xuất của từng vật tư ứng với các loại 1 và 2: MSO VATTU GNHAP1 GNHAP2 GXUAT1 GXUAT2 A XANG 3000 3500 4000 4500 B DAU 2000 2500 3000 3500 C NHOT 10000 10500 11000 15000 Câu 1 Căn cứ vào MSO điền tên vật tư vào cột VATTU Câu 2 Tính tổng tiền nhập (TGNHAP) của các loại bằng số lượng nhân đơn giá nhập tùy thuộc vào loại. Tương tự hãy tính tổng tiền xuất (TGXUAT) Câu 3Ở cột ghi chú đánh dấu X nếu đã xuất hết số lượng nhập. Câu 4 Cột SAP ghi thứ tự của các hàng tương ứng theo TGXUAT với giá trị cao là 1 (sắp theo chiều giảm). BÀI SỐ 6  Tạo biểu đồ bằng Chart Wizard. Tham chiếu tuyệt đối. 60 Hoàng Vũ Luân
  61. PHIẾU THEO DÕI TIỀN GỞI TIẾT KIÊM Lai suat 1.10% So tien Them bot Cong tien Thang Dau thang Lai Cuoi thang cuoi thang cuoi thang 1 20000000 220000 20220000 500000 20720000 2 20720000 2000000 3 -300000 4 -500000 5 200000 6 1000000 7 600000 8 -200000 9 -500000 10 1000000 Tổng số tiền có được sau 12 tháng Câu 1 Tính tiền Lãi = Số tiền đầu tháng * Lãi suất Câu 2 Số tiền cuối tháng = Số tiền đầu tháng + Lãi Câu 3 Cộng tiền cuối tháng = Cuối tháng + Thêm bớt cuối tháng Câu 4Số tiền đầu tháng sau = Cộng tiền cuối tháng của tháng trước đó Câu 5 Tính số tiền sẽ có được sau 12 tháng Câu 6 Lập biểu đồ minh họa số tiền có được của đầu các tháng Câu 7 Trang trí và lưu với tên BTAP6.XLS  Hướng dẫn thực hành: 1. Lưu ý rằng, để sao chép công thức đúng thì tham chiếu đến ô Lai suat cần phải đặt là tham chiếu tuyệt đối.  Sau khi tính xong ở các ô đầu tiên, sao chép công thức xuống phía dưới và lưu ý chỉ khi sao chép ở ô cuối của bảng thì số liệu mới được điền vào đầy đủ. 5. Trong bài chỉ có dữ liệu của 10 tháng. Để tính đến 12 tháng, ta xem phần thêm bớt cuối tháng bằng 0, và tính theo công thức: Giả sử tổng sau 10 tháng là x. Khi đó ta có Tổng sau 12 tháng = (x + x*Lai suat) + (x + x*Lai suat)*Lai suat Với Lai suat ở đây là ô chứa giá trị 1,1%. 6. Vẽ biểu đồ Bước 1 Chọn vùng dữ liệu: bao gồm cột Tháng và cột Dau thang Bước 2 Chọn biểu tượng ChartWizard, con trỏ có dạng dấu + (Vẽ một vùng trên bảng tính để đặt biểu đồ) Bước 3 Cung cấp thông tin cần thiết sau đó chọn [Finish] để kết thúc. Tiến hành theo các bước hướng dẫn trong phần lý thuyết  Biểu đồ có dạng: HƯỚNG DẪN THỰC HÀNH EXCEL - BÀI SỐ 6 61
  62. 3 0 0 0 0 0 0 0 2 5 0 0 0 0 0 0 2 0 0 0 0 0 0 0 1 5 0 0 0 0 0 0 1 0 0 0 0 0 0 0 5 0 0 0 0 0 0 0 1 2 3 4 5 6 7 8 9 1 0 Sau khi tạo được biểu đồ, chọn từng phần tử trong biểu đồ để chỉnh sửa và xem kết quả để hiểu thêm về các thành phần trong một biểu đồ. BÀI SỐ 7a  Tạo bảng dữ liệu, sử dụng các hàm cơ sở dữ liệu (DSUM, DAVERAGE ), tổ chức các vùng điều kiện. NHÀ MÁY NƯỚC HUẾ STT KHHANG KVUC METK TTIEN PTHU TTHU 1 VAN A 45 58500 0 58500 2 HOANG B 65 91000 6500 97500 3 VO C 23 34500 3450 37950 4 TRAN B 14 19600 1400 21000 5 LE C 78 117000 11700 128700 6 BUI A 93 120900 0 120900 7 VU A 90 117000 0 117000 8 NGUYEN C 24 36000 3600 39600 9 BUI B 56 78400 5600 84000 10 LE B 78 109200 7800 117000 Khu vực Phụ thu Đơn giá A 0 1300 B 100 1400 [bảng_tìm] C 150 1500 Câu 1 Tính Thành tiền = Mét khối * Đơn giá (tùy thuộc khu vực) Câu 2 Tính Tổng thu = Thành tiền + Phụ thu (theo khu vực) Câu 3 Tính tổng tiêu thụ lớn nhất, nhỏ nhất và trung bình của số mét khối đã tiêu thụ của từng khu vực và ghi kết quả vào bảng sau: Khu Vực A B C Tổng 296400 319500 206250 Lớn nhất 120900 117000 128700 Bé nhất 58500 21000 37950 Trung bình 98800 79875 68750 Câu 4 Vẽ đồ thị minh họa cho bảng ở câu 3 Câu 5 Trang trí và lưu file với tên BTAP7.XLS  Hướng dẫn thực hành: 1. Dùng Vlookup để tính đơn giá của từng khu vực sau đó nhân với số mét khối, ta có: (cột 3 của bảng tìm chứa đơn giá) 62 Hoàng Vũ Luân
  63. [TTIEN]=[METK] * VLOOKUP([KVUC], [Bang_Tìm], 3, 0) 2. Tương tự trên, với cột 2 của bảng tìm chứa phụ thu ta có: [TTHU]=[TTIEN] + VLOOKUP([KVUC], [Bang_Tìm], 2, 0) 3. Dùng các hàm cơ sở dữ liệu DSUM, DMIN, DMAX, DAVERAGE với các điều kiện về khu vực được tổ chức như sau: KVUC KVUC KVUC A B C Lưu ý rằng, các nhãn tham gia trong điều kiện phải chính xác như nhãn cột trong bảng dữ liệu, thường ta dùng chức năng copy để sao chép các nhãn cột để tránh sai sót.  Giả sử bảng dữ liệu được gán tên là DATA7a, ta có công thức để tính tổng tiêu thụ của khu vực A là: DSUM(DATA7a, “TTHU”, [đkA]), với [đkA] là hai ô KVUC và A ở vùng điều kiện trên. Tương tự đối với công thức ở các ô còn lại. - Trong thực hành, ta sao chép các công thức sang các ô bên cạnh, sau đó sửa lại cho chính xác. 4. Đồ thị: 400000 300000 A B 200000 C 100000 0 Tong Lon nhat Be nhat Trung binh HƯỚNG DẪN THỰC HÀNH EXCEL - BÀI TẬP 7b 63
  64. BÀI TẬP 7b  Vận dụng các hàm cơ sở dữ liệu có kết hợp các hàm chuỗi. Ôn tập các hàm tìm kiếm (Thực hành tiếp trong Sheet2 của bài 7a) DANH SÁCH LƯƠNG THÁNG 06 NĂM 2005 SO MASO HOTEN PHAI CHVU TĐOVH LGCB PHCAP NGCONG THUONG CGLUONG AFD8 Hồng 460 23 CFC1 Thanh 310 24 CMT5 Sơn 330 23 BMC7 Hoàng 430 25 CMT3 Tâm 320 24 CFT3 Lan 320 22 CFC6 Mai 360 26 CFT4 Thúy 350 23 CMD2 Hùng 310 20 CMC9 Tình 380 23 MaxL= ? MinL= ? AveL= ?  MASO cho trên gồm 4 ký tự MS1, MS2, MS3 và MS4, ký tự đầu là chức vụ, thứ hai là phái, thứ ba là trình độ văn hóa và ký tự cuối là số năm công tác, với các giá trị như sau: MS1 Chức vụ MS2 Phái MS3 Trình độ văn hóa MS4 Năm công tác A TP F Nữ D Đại học B PP M Nam C Cao đẳng C NV T Trung cấp Câu 1 Căn cứ vào MASO chèn thông tin vào các cột PHAI, CHVU, TĐOVH Câu 2 Tính PHCAP = PCCV+THNIEM, với THNIEM = NAMCT * 6000 và PCCV được tính như sau: CHVU PCCV TP 40000 PP 25000 NV 10000 Câu 3 Tính THUONG. Biết: Nếu NGCONG>=25, THUONG = 120.000; nếu 23<=NGCONG<25, THUONG=70.000; còn lại THUONG=20.000 Câu 4 Tính CGLUONG = LGCB*1200 + PHCAP + THUONG Câu 5 Tính Lương cao nhất MaxL Lương thấp nhất MinL Lương trung bình AveL Câu 6 Cột SO được đánh số theo CGLUONG với mức cao nhất là 1 Câu 7 Tính tổng PHCAP, THUONG và CGLUONG theo PHAI và lưu vào Sheet3, theo mẫu sau: Phái Phụ cấp Thưởng Cộng lương Nam ? ? ? Nữ ? ? ? 64 Hoàng Vũ Luân
  65. Câu 8 Trang trí và ghi lại các thay đổi  Hướng dẫn thực hành: 1. Dùng Vlookup và các hàm chuỗi để lấy thông tin. 2. Dùng Vlookup để lấy phụ cấp chức vụ tương ứng với chức vụ. 3. Dùng 2 hàm IF lồng nhau. 5. Dùng các hàm Max, Min và Average 7.Tạo vùng điều kiện theo phái, tạo bảng báo cáo trong Sheet3 và dùng DSUM để tính. BÀI SỐ 8  Các thao tác cơ bản trên danh sách dữ liệu: SORT, FILTER Danh sách học viên được cấp học bổng - Năm học 2004-2005 STT HOTEN NGSINH NOISINH TRBINH HBONG MADIEM Tuoi 1 TUAN 05/15/80 HUE 4.2 0 A01 19 2 VIET 02/13/75 Q.BINH 7.5 50000 B03 24 3 ANH 11/11/78 HUE 6.7 50000 C04 21 4 HUNG 10/12/76 DA NANG 8.3 50000 A03 23 5 HOA 04/01/82 HA TINH 8.7 150000 C02 17 6 THUAN 12/18/78 NGHE AN 4.0 0 B01 21 7 VAN 01/01/81 DA NANG 4.2 0 B05 18 8 SON 09/09/77 HUE 5.5 50000 A02 22 9 BINH 10/10/79 HA TINH 8.3 100000 C01 20 Câu 1 Nhập bảng dữ liệu trong Sheet1 và nhập danh sách điểm sau trong Sheet2: Mã điểm Điểm cơ sở Chuyên môn Ngoại ngữ C01 9.0 8.0 8.0 B05 7.0 2.5 3.0 B01 2.0 4.5 5.5 A02 5.5 6.5 4.5 C02 9.0 8.5 8.5 A03 8.5 9.0 7.5 C04 9.0 2.5 8.5 B03 6.0 7.5 9.0 A01 4.5 6.0 2.0 Câu 2 Căn cứ vào MADIEM và bảng mã điểm trên để tính điểm trung bình (TRBINH) Câu 3 Tính học bổng (HBONG) theo các điều kiện sau: - nếu TRBINH >=8.5 và tuổi =8.0 và tuổi 20 thì học bổng là: 50000 đ - ngoài ra không có học bổng. Câu 4 Trích ra những bảng tính khác (lưu vào Sheet3): - những học viên có mức học bổng 150000 đ - những học viên có mức học bổng 100000 đ - những học viên có mức học bổng 50000 đ HƯỚNG DẪN THỰC HÀNH EXCEL - BÀI SỐ 8 65
  66. - những học viên không có học bổng. Câu 5 Trích ra những bảng tính khác (lưu vào Sheet3) những học viên trong các độ tuổi sau: - từ 16 đến 18 - 19 hoặc 20 - trên 20 tuổi. Câu 6 Trang trí và lưu với tên BTAP8.XLS Câu 7 Thực hiện các thao tác sắp thứ tự theo HOTEN, NGSINH, TRBINH sau mỗi lần sắp hãy quan sát sự thay đổi (có thể nhấn Undo và Redo)  Hướng dẫn thực hành: 2. Để tính điểm trung bình cần phải biết 3 cột điểm, do đó dùng hàm AVERAGE với 3 lần dùng VLOOKUP, mỗi lần chỉ thay đổi cột trả lại giá trị: Average(Vlookup( , ,2,0), Vlookup( , ,3,0), Vlookup( , ,4,0))  Ngoài ra, Excel còn có 2 hàm cho phép lấy một khoảng các ô mà không phải dùng nhiều lần các hàm VLOOKUP; đó là hàm OFFSET và MATCH. Hàm OFFSET có 5 đối số như sau: - OFFSET(vị trí gốc, độ dời đứng, độ dời ngang, số hàng, số cột kết quả) Với ý nghĩa: tính từ vị trí gốc, dời lên trên (âm)/dời xuống dưới (dương), dời sang trái (âm)/dời sang phải (dương) bao nhiêu ô; và khoảng cần trả lại sẽ chứa bao nhiêu hàng, bao nhiêu cột. - Hàm MATCH gần giống các hàm tìm kiếm nhưng không trả lại giá trị tìm mà chỉ trả lại vị trí (thứ tự) của giá trị tìm thấy trong khoảng tìm. MATCH(giá trị tìm, khoảng tìm, phương thức)  Từ hai hàm trên ta có cách giải khác để tính điểm trung bình như sau: = AVERAGE(OFFSET(vị trí gốc, MATCH(mã điểm, bảng mã, 0), 0, 1, 3)) trong đó: vị trí gốc được chọn là ô chứa giá trị [Điểm cơ sở]; mã điểm là số hiệu mã điểm của từng người; bảng mã là danh sách toàn bộ mã điểm cần tìm. Hàm Match có nhiệm vụ tính độ dời xuống khi tìm thấy mã điểm trong danh sách; độ dời ngang là 0 (vì các ô sẽ lấy có ô đầu tiên cùng cột với vị trí gốc); số ô cần lấy là 1 hàng và 3 cột. 3. Trong điều kiện tính học bổng có sử dụng giá trị tuổi, nhưng trong bảng dữ liệu không có giá trị này, do đó ta sẽ tạo thêm một cột trung gian để tính tuổi (nhằm đơn giản hóa điều kiện) và tuổi được tính bởi hiệu của năm hiện thời với năm sinh, ta có: [TUOI] = YEAR(NOW())-YEAR([NGSINH]) khi đó, giả sử E6 là ô chứa điểm trung bình, ta có công thức sau: IF(AND(E6>=8.5, [TUOI] =8, [TUOI] =5, [TUOI]>20), 50000, 0))) 4. Để lọc và ghi kết quả ta dùng chức năng lọc nâng cao (Advanced Filter) với các vùng điều kiện về học bổng như sau: HBONG HBONG HBONG HBONG 150000 100000 50000 0  Lưu ý, sau khi tạo vùng điều kiện ở Sheet3, ta đặt con trỏ ở một ô trống trước khi sử dụng lệnh lọc. 66 Hoàng Vũ Luân
  67. 5. Tương tự, sử dụng lọc nâng cao với các điều kiện lọc như sau: Tuoi Tuoi Tuoi Tuoi >=16 20 20 7. Trước hết chọn tên trường cần sắp (ví dụ, chọn trường HOTEN), sau đó mới sử dụng lệnh [DATA]SORT. Sau mỗi lần sắp, nên thay đổi một số yếu tố để hiểu rõ về chức năng này. BÀI SỐ 9  Các thao tác cơ bản trên danh sách dữ liệu: SORT, FILTER, SUBTOTAL BẢNG BÁN HÀNG MAH NBAN TEN SLUONG TGIA THUE TONG 1 12/12/97 BAP 324 2 12/12/97 BIA 454 3 14/12/97 BOT 656 4 11/01/98 GAO 431 5 20/01/98 KEO 455 6 25/01/98 BAP 564 7 01/02/98 BIA 657 8 11/02/98 BOT 432 9 05/02/98 KEO 544 10 12/03/98 GAO 767 Tổng trị giá các mặt hàng bán trong tháng 2/1998 ??? Câu 1 Tính trị giá (TGIA) bằng số lượng (SLUONG) nhân đơn giá (DGIA), với đơn giá và thuế được cho ở bảng sau: Tên Đơn giá Thuế BAP 3500 1% BIA 15000 1% GAO 3000 2% KEO 10000 2% BOT 5000 1% Câu 2 Tính thuế (THUE) theo số liệu trên và chú ý rằng nếu trị giá dưới 100000 đồng thì không thu thuế. Sau đó tính tổng cộng (TONG) bằng trị giá cộng với thuế Câu 3 Trích ra danh sách các mặt hàng BAP, GAO, BOT và lưu vào Sheet2. Câu 4 Tính tổng các cột TGIA, THUE và tổng số lần bán theo từng loại mặt hàng và lưu vào bảng sau ở Sheet3: Tên Số lần bán Tổng trị giá Tổng thuế BAP BIA GAO KEO BOT Câu 5 Dùng kết quả ở câu 4 để vẽ đồ thị so sánh tổng trị giá của từng loại mặt hàng Câu 6 Trích ra hai bảng tính bán hàng ứng với 2 năm: 1997 và 1998 HƯỚNG DẪN THỰC HÀNH EXCEL - BÀI SỐ 9 67
  68. Câu 7 Tính tổng trị giá các mặt hàng bán trong tháng 2/1998 Câu 8 Sắp thứ tự (Sort) bảng theo cột TEN với chiều giảm dần (Descending) Câu 9 Dùng SubTotal để tính tổng các cột SLUONG, TGIA, TONG. Sau đó thay tổng bằng các hàm khác như Min, Max, Average  Trang trí và lưu với tên BTAP9.XLS  Hướng dẫn thực hành: 2. Vì có điều kiện nên khi tính thuế ta cần phải xét xem trị giá lớn hơn hay nhỏ hơn 100000, do đó có công thức sau: IF([TGIA]<100000, 0, [TGIA]*VLOOKUP( )) 3. Lập vùng điều kiện dạng hoặc (OR) để lọc. 4. Để tính tổng số lần bán ta dùng DCOUNTA, các giá trị khác thì dùng DSUM.  Đối với phép tính tổng theo điều kiện, ngoài hàm DSUM Excel còn cung cấp một hàm tương đương, đó là SUMIF Cú pháp: SUMIF(khoảng_sẽ_tính, điều_kiện, khoảng thật sự sẽ tính) Trong đó, khoảng_sẽ_tính tham chiếu đến khoảng các ô sẽ tham gia tính tổng; điều_kiện thường có dạng “biểu thức so sánh”; riêng khoảng thật sự sẽ tính là tùy chọn, nhưng nếu đưa vào thì tổng kết quả sẽ tính trong vùng này. * Ví dụ: xét bảng số liệu sau: A B C D E F 1 BAP 5 BAP BIA BAP BAP 2 BIA 8 7 9 5 4 3 BAP 7 4 GAO 9 5 BIA 6 - Khi đó công thức: SUMIF(A1:A5,"BAP",B1:B5) sẽ có giá trị là 12; tương đương với việc dùng hàm DSUM với điều kiện tên hàng là BAP. - Tương tự ta có: SUMIF(C1:F1,"BAP",C2:F2) = 16  Thử dùng SUMIF để giải lại câu 4 ở trên.  Cùng dạng với SUMIF là hàm COUNTIF(khoảng ô, điều kiện) dùng để đếm số các ô trong khoảng ô hợp với điều kiện. 6. Lập vùng điều kiện từ ngày 01/01/1997 đến 31/12/1997 (năm 1997) và tương tự để tính năm 1998. 7. Lập vùng điều kiện có dạng ngày bán lớn hơn hoặc bằng ngày 01/02/1998 và nhỏ hơn ngày 01/03/1998 (trong khoảng tháng 2)  Ngoài phương pháp dùng một khoảng ngày như trên, ta còn có thể sử dụng dạng công thức trong vùng điều kiện để tính. Ví dụ, đối với câu 6 có thể lập điều kiện dạng =YEAR(ô đầu tiên chứa dữ liệu ngày)=1997 (lưu ý trong công thức trên có hai dấu =) và nhãn tên trường cần phải bỏ trống. Þ Tương tự, điều kiện trong câu 7 sẽ là =MONTH(ô chứa ngày)=2. BÀI SỐ 10  Bài tập tổng hợp - dạng đề thi BẢNG GHI TÊN, GHI ĐIỂM 68 Hoàng Vũ Luân
  69. Điểm chuẩn = 17 TT SBD HOTEN TEN TRUONG BAN DVAN DTOAN TONG KETQUA A00 AI C A11 BINH A A20 CHAU B B31 ANH A B42 BAO C C50 DUNG B C61 HANH B C71 HUONG A D82 DUONG A D90 PHUOC C Mã trường Tên trường Ký tự đầu của SBD là mã trường A QUOC HOC ký tự cuối của SBD là điểm ưu tiên. B HAI BA TRUNG C NGUYEN HUE D GIA HOI Câu 1 Căn cứ vào ký tự đầu của SBD điền thông tin vào TEN TRUONG Câu 2Căn cứ vào SBD điền DVAN và DTOAN (theo bảng điểm ở Sheet2) Câu 3 Tính TONG theo các yêu cầu sau: * Nếu ban A hoặc B thì điểm Toán hệ số 2 * Nếu ban C thì điểm Văn hệ số 2 * Cộng thêm điểm ưu tiên Câu 4 Tính KETQUA: Đậu nếu TONG lớn hơn hoặc bằng Điểm chuẩn ngược lại là Rớt Câu 5 Lọc ra các danh sách và ghi vào Sheet3 những người: a) Có kết quả Rớt b) Có kết quả Đậu và tổng điểm lớn hơn 23 Câu 6 Lập báo cáo theo mẫu sau: Học sinh trường Điểm trung bình Số học sinh Đậu QUOC HOC ? ? HAI BA TRUNG ? ? NGUYEN HUE ? ? GIA HOI ? ? Câu 7 Cột TT đánh số theo cột TONG với giá trị cao nhất là 1 Bảng điểm (Lưu trong Sheet2) SBD DTOAN DVAN D90 4 7 D82 5 4 C71 8 7 C61 9 8 HƯỚNG DẪN THỰC HÀNH EXCEL - BÀI SỐ 10 69
  70. C50 5 5 B42 3 7 B31 5 4 A20 4 5 A11 7 9 A00 6 8  Ghi bài thực hành với tên BTAP10.XLS BÀI SỐ 11  Các bài tập bổ sung. Sử dụng hàm SumProduct để giải bài toán tính điểm trung bình các môn theo các hệ số tùy ý.  SUMPRODUCT(mảng_1, mảng_2, ) - Hàm SumProduct tính tổng các tích một cách tương ứng từ các phần tử của mảng 1 với mảng 2 cho đến tối đa 32 mảng được phép sử dụng - các mảng này phải có cùng số chiều.  Lưu ý: hàm PRODUCT(so1, so2, ) thực hiện phép nhân liên tiếp các so1, so2, với nhau. Ví dụ: Product(2,4,5) = 2*4*5 = 40. 70 Hoàng Vũ Luân
  71. BẢNG GHI ĐIỂM TT HTEN M1 M2 M3 M4 M5 TRBINH 1 AN 5 7 6 8 7 6.8 2 BINH 8 7 9 6 8 7.5 3 SON 9 9 8 7 8.1 4 VAN 8 7 6 9 5 7.3 5 TUAN 8 4 5 8 5.5 6 LAN 3 5 4 5 8 4.9 7 HOA 4 7 6 7 5 5.9 8 QUANG 5 4 3 5 4 4.3 9 VINH 9 9 9 8 8.8 10 THANH 9 7 9 8 9 8.5 * Với hệ số các môn M1 đến M5 được cho trong bảng sau: Môn M1 M2 M3 M4 M5 Hệ số 2 1 3 4 2 Câu 1 Căn cứ vào hệ số, tính điểm trung bình (TRBINH). Gợi ýĐiểm trung bình được tính bằng cách lấy tổng điểm các môn có nhân hệ số sau đó chia cho tổng số các hệ số (ở trên là bằng 2+1+3+4+2=12). Công thức có dạng: SUMPRODUCT(điểm, he_so)/SUM(he_so) Trong đó điểm tham chiếu đến các ô chứa điểm của từng học sinh; he_so là vùng chứa thông tin về hệ số.  Hãy để ý tình huống có một số học sinh được miễn một số môn học (ô điểm để trống) thì công thức có còn đúng không? Rõ ràng trong tình huống này, phép chia cho toàn bộ tổng của các hệ số sẽ làm cho điểm trung bình bị thấp xuống. Thay vào đó ta phải xét xem môn nào được miễn để giảm đi hệ số của môn đó. Sử dụng hàm SumIf ta có thể khắc phục tình huống này: SUMPRODUCT(điểm, he_so)/SUMIF(điểm, “>=0”, he_so)  Hàm SumIf lúc này có nhiệm vụ tính tổng các hệ số tương ứng với các môn học có điểm lớn hơn hoặc bằng 0, do đó sẽ không tính những môn miễn học (có giá trị rỗng) Câu 2 Sử dụng hàm SumProduct với các giá trị bố trí theo cột Tên hàng Số lượng Giá_1 Giá_2 A12 20 3000 4000 C21 30 1000 3000 E23 25 2000 4000 D32 50 4000 5000 Tổng cộng (số_lượng ´ giá) = ? ?  Giả sử cần tính tổng cộng toàn bộ các mặt hàng trên theo đơn giá loại 1 mà không phải tính tổng từng mặt hàng, ta sử dụng hàm SumProduct như sau: SumProduct(so_luong, don_gia_1) sẽ cho kết quả: 340000. Tương tự tính tổng theo đơn giá loại 2. BÀI SỐ 12  Sử dụng công cụ Solver để giải các bài toán đặc biệt. HƯỚNG DẪN THỰC HÀNH EXCEL - BÀI SỐ 12 71