›
›

Giáo trình Tin học ứng dụng - Trần Công Nghiệp

pdf 130 trang hapham 3050
Download
Bạn đang xem 20 trang mẫu của tài liệu "Giáo trình Tin học ứng dụng - Trần Công Nghiệp", để 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:

  • pdfgiao_trinh_tin_hoc_ung_dung_tran_cong_nghiep.pdf

Nội dung text: Giáo trình Tin học ứng dụng - Trần Công Nghiệp

  1. TRƯỜNG ĐẠI HỌC KINH TẾ VÀ QUẢN TRỊ KINH DOANH PHÒNG THỰC HÀNH KINH DOANH Trần Công Nghiệp GIÁO TRÌNH TIN HỌC ỨNG DỤNG (Bản thảo) NHÀ XUẤT BẢN MÁY IN CANON ĐỂ BÀN THÁI NGUYÊN 2008
  2. Trần Công Nghiệp - Giáo trình Tin học ứng dụng (bản thảo) MỤC LỤC MỤC LỤC 1 CHƯƠNG 1 MỘT SỐ KIẾN THỨC TIN HỌC CĂN BẢN 3 1.CĂN BẢN VỀ WNDOWS 3 1.1 Khởi động và thoát khỏi windows 3 1.2 Windows Explorer 4 1.3 Tệp tin (file) 6 1.4 Thư mục (Folder hay Directory) 6 1.5 Đổi tên file, đổi tên thư mục 6 1.6 Sao chép (copy) tập tin hay thư mục. 7 1.7. Di chuyển thư mục, file 7 1.8 Xóa thư mục, tập tin 7 1.8 Phục hồi thư mục hay tập tin bị xóa. 8 1.9 Quản lí đĩa 8 1.10. Thiết lập cách biểu diên ngày giờ, số và tiền tệ 9 1.11. Chạy chương trình trong Windows 9 2. CĂN BẢN VỀ EXCEL 11 2.1 Giới thiệu 11 2.2 Worksheet, workbook, địa chỉ 14 2.3 Các dạng dữ liệu trong Excel 17 2.4 Các phép tính trong Excel 18 2.5 Sử dụng hàm trong Excel 19 2.6. Công thức mảng 27 BÀI TẬP CHƯƠNG 1 29 CHƯƠNG 2 GIẢI BÀI TOÁN TỐI ƯU 35 1. CƠ SỞ LÝ THUYẾT CỦA BÀI TOÁN TỐI ƯU 35 1.1. Bài toán quy hoạch tuyến tính (linear programming) 35 1.2. Bài toán quy hoạch phi tuyến (nonlinear programming) 40 2. QUY TRÌNH GIẢI BÀI TOÁN TỐI ƯU TRONG EXCEL 40 2.1 Mô tả bài toán 40 2.2 Các bước tiến hành giải bài toán tối ưu trong Excel 41 2.3 Ý nghĩa các lựa chọn của Solver 48 2.4 Một số thông báo lỗi thường gặp của Solver 49 2.5 Phân tích độ nhạy của bài toán 50 3. GIẢI HỆ PHƯƠNG TRÌNH TUYẾN TÍNH 52 BÀI TẬP CHƯƠNG 2 53 CHƯƠNG 3: QUẢN LÝ TÀI CHÍNH 57 1.KHẤU HAO TÀI SẢN CỐ ĐỊNH 57 1.1.Khái niệm về tài sản cố định và khấu hao tài sản cố định 57 1.2.Các phương pháp tính khấu hao tài sản cố định 57 1.3.Các hàm tính khấu hao tài sản cố định 60 2. PHÂN TÍCH HIỆU QUẢ VỐN ĐẦU TƯ 64 2.1 Dòng tiền 64 2.2 Các chỉ tiêu chủ yếu đánh giá hiệu quả dự án đầu tư 70 2.3 Các hàm đánh giá hiệu quả dự án đầu tư trong Excel 72 2.4. Các chỉ tiêu khác 76 3. Đầu tư chứng khoán 77 3.1 Tính lãi gộp cho một trái phiếu trả vào ngày tới hạn 77 3.2 Tính lãi gộp của một chứng khoán trả theo định kỳ 78 Phòng Thực hành kinh doanh – Bộ môn Tin học ứng dụng = trang 1=
  3. Trần Công Nghiệp - Giáo trình Tin học ứng dụng (bản thảo) 3.3 Tính tỉ suất chiết khấu của một chứng khoán 79 3.4 Tính lãi suất của một chứng khoán được đầu tư hết 79 3.5.Tính số tiền thu được vào ngày tới hạn của một chứng khoán được đầu tư hết 80 BÀI TẬP CHƯƠNG 3 80 CHƯƠNG 4: PHÂN TÍCH HỒI QUY TƯƠNG QUAN VÀ DỰ BÁO KINH TẾ 83 1. HỒI QUY VÀ TƯƠNG QUAN 83 1.1. Phân tích tương quan 83 2. HỒI QUY TUYẾN TÍNH ĐƠN 89 2.1. Cơ bản về hồi quy tuyến tính đơn 89 3. HỒI QUY TUYẾN TÍNH BỘI 91 3.1 Cơ bản về hồi quy tuyến tính bội 91 3.2 Kiểm định sự phù hợp của mô hình hồi quy bội 92 3.3 Khoảng tin cậy và kiểm định giả thuyết của mô hình hồi quy bội 93 4. HỒI QUY PHI TUYẾN 93 5. QUY TRÌNH PHÂN TÍCH HỒI QUY TRONG EXCEL 93 5.1 Phân tích hồi quy đơn trong Excel 94 5.2 Phân tích hồi quy bội trong Excel 98 5.3 Phân tích hồi quy phi tuyến trong Excel 100 6. DỰ BÁO KINH TẾ 101 6.1 Dự báo bằng phương pháp hồi quy tương quan 102 6.2 Dự báo nhanh sử dụng các hàm của Excel 103 6.3.Dự báo bằng các phương pháp ngoại suy thống kê 104 BÀI TẬP CHƯƠNG 4 108 CHƯƠNG 5: PHÂN TÍCH DỮ LIỆU THỐNG KÊ 111 1.CÁC THAM SỐ CƠ BẢN CỦA MẤU THỰC NGHIỆM 111 1.1 Một số tham số cơ bản đặc trưng cho mẫu thực nghiệm 111 1.2 Các tham số đặc trưng cho hình dạng phân bố của mẫu 113 1.3 Các hàm tính toán các tham số đặc trưng cho phân bố thực nghiệm 113 2. BIỂU ĐỒ PHÂN BỐ THỰC NGHIỆM 114 3. CÔNG CỤ THỐNG KÊ MÔ TẢ 117 4. PHÂN TÍCH PHƯƠNG SAI 118 4.1 Mô hình phân tích phương sai một nhân tố 118 4.2 Mô hình phân tích phương sai hai nhân tố không tương tác 119 4.3 Mô hình phân tích phương sai hai nhân tố có tương tác 121 5. QUY TRÌNH PHÂN TÍCH PHƯƠNG SAI TRONG EXCEL 122 5.1 Phân tích phương sai một nhân tố trong Excel 122 5.2 Phân tích phương sai hai nhân tố không tương tác trong Excel 123 5.3 Phân tích phương sai hai nhân tố có tương tác trong Excel 125 BÀI TẬP CHƯƠNG 5 127 Phòng Thực hành kinh doanh – Bộ môn Tin học ứng dụng = trang 2=
  4. Trần Công Nghiệp - Giáo trình Tin học ứng dụng (bản thảo) CHƯƠNG 1 MỘT SỐ KIẾN THỨC TIN HỌC CĂN BẢN 1.CĂN BẢN VỀ WNDOWS Windows là hệ điều hành sử dụng giao diện đồ họa và là sản phẩm của tập đoàn Microsoft. Các phiên bản gần đây của windows là hệ điều hành đa nhiệm và đa người sử dụng. Gần 80% các máy tính cá nhân (PC) trên thế giới sử dụng hệ điều hành windows vì tính thân thiện, dễ sử dụng của hệ điều hành này. Windows có chức năng điều khiển phần cứng của máy tính; tạo môi trường cho các ứng dụng khác hoạt động; quản lý việc lưu trữ thông tin trên đĩa; cung cấp khả năng kết nối giữa các máy tính. Cho đến thời điểm hiện tại, tập đoàn Micrrosoft đã cho xuất xưởng nhiều phiên bản windows như windows 95, windows 98, windows ME, windows NT, windows 2000, windows XP, windows 2003, và gần đây nhất là windows Vista. Trong phạm vi giáo trình này, chúng tôi trình bày một số nét căn bản về windows XP. 1.1 Khởi động và thoát khỏi windows Để khởi động windows XP, nhấn vào nút POWER trên máy tính và chờ windows hoàn tất quá trình khởi động. Sau khi windows XP khởi động xong sẽ có giao diện như hình 1.1 Hình 1. 1 Màn hình windows XP sau khi khởi động xong. Để thoát khỏi windows (tắt máy) cần phải thoát hết các ứng dụng và thao tác theo trình tự sau: Chọn START / TURN OFF COMPUTER.Hộp thoại Turn Off Computer hiện ra như hình Phòng Thực hành kinh doanh – Bộ môn Tin học ứng dụng = trang 3=
  5. Trần Công Nghiệp - Giáo trình Tin học ứng dụng (bản thảo) 1.2. Tùy chọn Stand By tạm dừng hoạt động của máy và chuyển sang chế độ chờ. Khi người sử dụng tác động vào bàn phím hoặc con chuột thì máy hoạt động trở lại. Tùy chọn Turn off: Tắt hoàn toàn máy tính. Tùy chọn Restart: Khởi động lại máy tính Hình 1. 2 Hộp thoại Turn Off Computer Từ menu Start, nếu chọn Log Off sẽ thoát khỏi Windows để thiết lập lại môi trường nhưng không tắt máy. Hình 1.3 minh họa trùy chọn Log Off. Hình 1. 3 Màn hình Log Off Windows Lựa chọn Switch User trong hình1.3 cho phép đăng nhập hệ thống với tên khác. Ngoài ra, trên một số máy tính có hỗ trợ chức năng Hibernate (ngủ đông). Khi sử dụng chức năng này, các chương trình ứng dụng đang chạy sẽ được đóng băng tạm thời và máy tính tắt. Khi khởi động lại, máy trở về đúng trạng thái như trước khi thực hiện Hibernate. Để thực hiện, nhấn phím Shift trên bàn phím, lúc này nút Stand by biến thành nút Hibernate, bấm chuột vào nút này để thực hiện lệnh. 1.2 Windows Explorer Windows Explorer là công cụ quản lý tập tin (File), thư mục (Folder), và các ổ đĩa (Driver) trên máy tính. Sử dụng Windows Explorer, người sử dụng có thể sao chép (copy), di chuyển (move), đổi tên (rename), và tìm kiếm các tập tin hay các thư mục. Có thể truy cập Windows Explorer từ Desk top bằng cách kích chuột phải vào My Computer, chọn Explore như hình 1.4 Hình 1. 4 Truy cập Explorer từ My Computer Phòng Thực hành kinh doanh – Bộ môn Tin học ứng dụng = trang 4=
  6. Trần Công Nghiệp - Giáo trình Tin học ứng dụng (bản thảo) Cũng có thể sử dụng các cách khác như kích chuột phải vào menu Start, chọn Explore hay chọn Start / Programs/ Accessories / Windows Explorer. Windows Explorer trình bày dưới dạng hai cửa sổ. Cửa sổ bên trái hiển thị thông tin tổng quát. Cửa sổ bên phải hiện thị thông tin chi tiết của thư mục hay ổ đĩa được chọn. Hình 1.5 minh họa cửa sổ Hình 1. 5 Màn hình Windows Explorer - Up One Level: Chuyển lên thư mục cha. - Cut: Cắt đối tượng đã được chọn vào Clipboard. - Copy: Sao chép đối tượng đã được chọn vào Clipboard. - Paste: Chép nội dung từ Clipboard đến vị trí đã được chỉ định. - Undo: Hủy bỏ lệnh vừa thực hiện. - Delete: Xóa đối tượng đã được chọn. - Properties: Hiển thị hộp thoại cho biết thuộc tính của đối tượng đã chọn. - Các chế độ trình bày màn hình như: Detail, List, Title, Hình 1. 6 Các nút công cụ trên thanh Toolbar Phòng Thực hành kinh doanh – Bộ môn Tin học ứng dụng = trang 5=
  7. Trần Công Nghiệp - Giáo trình Tin học ứng dụng (bản thảo) 1.3 Tệp tin (file) Tệp tin là tập hợp các thông tin, dữ liệu được tổ chức theo một cấu trúc nhất định. Mỗi têp tin được lưu trên đĩa với tên phân biệt. Trong cùng một cấp lưu trữ không cho phép các tệp tin có tên trùng nhau. Mỗi hệ điều hành có quy tắc đặt tên cho các tệp tin khác nhau. Tên của tệp tin thường gồm hai phần là phần tên và phần mở rộng. Phần mở rộng có thể có hoặc có thể không. Tên của tệp tin do người tạo ra têp tin đặt. Tên file không được chứa các ký tự đặc biệt (là các kí tự / \ ? * : “ |). Phần mở rộng thường có 3 ký tự và thường do các chương trình ứng dụng tạo ra. 1.4 Thư mục (Folder hay Directory) Để thuận tiện cho quản lí thông tin trong máy tính, người ta sử dụng các thư mục. Các tệp tin có quan hệ với nhau thường được lưu trữ trong một thư mục. Thư mục có cấu trúc như hình cây. Thư mục gốc chính là ổ đĩa. Ví dụ thư mục gốc C:\ hay D:\. Thư mục được đặc trưng bởi tên. Tên thư mục không dài quá 255 kí tự và không được chứa các kí tự đặc biệt. Thư mục cũng có thể rỗng hoặc chứa thư mục khác. Trong cùng một cấp không được phép có thư mục có tên trùng nhau. Thư mục đang làm việc được gọi là thư mục hiện hành. Để tạo thư mục, trong màn hình Explorer hay My Computer, chuyển đến vị trí lưu thư mục. Nhấn phím phải chuột chọn New | folder. Nhập vào tên thư mục và nhấn Enter đề kết thúc. Hình 1.7 minh họa thao tác này. Hình 1. 7 Tạo thư mục từ Explorer. Có thể tạo thư mục trong Explorer bằng cách truy cập menu File, chọn new folder. 1.5 Đổi tên file, đổi tên thư mục Để đổi tên thư mục hay đổi tên file, chọn file hoặc thư mục cần đổi tên. Nhấn chuột phải lên file hay thư mục vừa chọn và chọn rename. Nhập vào tên mới và nhấn Enter. Chú ý, khi đổi tên file thì cần giữ nguyên phần mở rộng của tên file (extension) vì trong hệ điều hành windows các phần mềm ứng dụng nhận được các file do chúng tạo ra thông qua phần mở rộng của tên file. Cũng có thể chọn file (hay thư mục) cần đổi tên, nhấp chuột trái, nhập vào tên mới cho tập tin hoặc thư mục và kết thúc bằng cách nhấn phím Enter. Phòng Thực hành kinh doanh – Bộ môn Tin học ứng dụng = trang 6=
  8. Trần Công Nghiệp - Giáo trình Tin học ứng dụng (bản thảo) 1.6 Sao chép (copy) tập tin hay thư mục. Sao chép thư mục là tạo một bản sao thư mục bao gồm các tập tin và các thư mục con bên trong thư mục đó. Sao chép tệp tin hay một nhóm tệp tin là tạo một bản sao của tập tin hay nhóm các tệp tin. Để sao chép thư mục hay tập tin, chọn thư mục hay tập tin cần sao chép. Nhấn chuột phải vào thư mục hay tệp tin và chọn copy. Mở ổ đĩa hay thư mục muốn sao chép đến, nhấn chuột phải và chọn paste. Cũng có thể sử dụng tổ hợp phím nóng CTRL +C để copy và CTRL+V để dán. Cũng có thể sử dụng thao tác drag and drop (kéo và thả) để copy và dán một nhóm thư mục hay một nhóm tập tin. Đối với các ổ đĩa có thể tháo rời như đĩa mềm, đĩa CD, đĩa flash, có thể sao chép tệp tin (nhóm tệp tin), thư mục (nhóm thư mục) bằng cách chọn các tệp tin hay thư mục, nhấn chuột phải và chọn send to. Chọn ổ đĩa đích để hoàn tất quá trình sao chép như trong hình 1.8. Hình 1. 8 Sao chép thư mục sang đĩa CD 1.7. Di chuyển thư mục, file Di chuyển một (hay một nhóm thư mục) là chuyển toàn bộ thư mục (nhóm thư mục) bao gồm các tập tin và thư mục con đến vị trí mới. Di chuyển một tập tin (hay một nhóm tập tin) là chuyển tập tin (nhóm tập tin) đến vị trí mới. Để di chuyển thư mục (hay tập tin), chọn thư mục (tập tin) đó, kích chuột phải và chọn cut. Chuyển đến ổ đĩa hay thư mục đích, nhấn phím phải chuột và chọn paste. Cũng có thể sử dụng tổ hợp phím nóng CTRL +X để cắt và CTRL + V để dán hoặc sử dụng menu edit / cut để cắt các thư mục hoặc tập tin ở vùng nguồn và dùng menu edit / paste để dán các thư mục hoặc file vào vùng đích. 1.8 Xóa thư mục, tập tin Xóa thư mục (nhóm thư mục) là xóa bỏ toàn bộ thư mục (nhóm thư mục) bao gồm các tệp tin và thư mục con. Để xóa thư mục (nhóm thư mục) hay tập tin (nhóm tập tin) chọn thư mục (nhóm thư muc) hoặc tập tin (nhóm tập tin) muốn xóa. Kích phím phải chuột lên thư mục (nhóm thư mục) hay tập tin (nhóm tập tin) đó và chọn delete. Cũng có thể nhấn phím delete trên bàn phím hoặc chọn menu edit / delete. Một hộp thoại hiện ra để xác nhận việc xóa thư mục hay tập tin như trong hình 1.7. Chọn Yes để xác nhận việc xoá; Chọn No để huỷ bỏ việc xoá. Phòng Thực hành kinh doanh – Bộ môn Tin học ứng dụng = trang 7=
  9. Trần Công Nghiệp - Giáo trình Tin học ứng dụng (bản thảo) 1.8 Phục hồi thư mục hay tập tin bị xóa. Các tập tin và thư mục bị xóa chỉ có thể phục hồi được nếu khi xóa chúng được xóa theo kiểu tạm thời, tức là chỉ bị đưa vào trong thùng rác (Recycle Bin) mà chưa bị xoá vĩnh viễn. Để phục hồi lại thư mục đã bị xoá tạm thời, làm như sau: Hình 1. 9 Hộp thoại xác nhận xóa thư mục Truy cập vào thùng rác (Recycle Bin) trên màn hình nền desktop. Tìm thư mục muốn phục hồi, kích phải chuột lên thư mục đó và chọn Restore (hoặc bấm vào Restore this Item trên File and Folder tasks). Lưu ý: Để xoá vĩnh viễn không thể phục hồi được, chọn Recycle Bin và chọn Delete file hoặc thư mục muốn xoá, hoặc kết hợp dùng phím Shift khi xoá. 1.9 Quản lí đĩa Trong Windows, đĩa mềm thường được k ý hiệu là A:\ hoặc B:\. Ổ đĩa cứng vật lý thường được chia thành các ổ đĩa logic (partition). Ổ đĩa logic đầu tiên thường được k í hiệu là C:\. Thường thì nên chia ổ đĩa vật lý thành một số ổ logic, trong đó ổ logic đầu tiên sử dụng để cài đặt hệ điều hành và các chương trình ứng dụng. Các ổ khác (D, E, ): lần lượt dùng để lưu dữ liệu hoặc có thể cài đặt các chương trình ứng dụng khi ổ đĩa C quá tải. Để hiển thị danh sách các ổ đĩa chọn My Computer Để xem dung lượng và kích thước còn trống trên các ổ đĩa C1: Vào menu View | Details C2: Kích phải chuột lên nền màn hình trắng phía bên trái cửa sổ My Computer | view | Details Hình 1. 10 Xem dung lượng và kích thước còn trống của ổ đĩa Phòng Thực hành kinh doanh – Bộ môn Tin học ứng dụng = trang 8=
  10. Trần Công Nghiệp - Giáo trình Tin học ứng dụng (bản thảo) Để truy cập ổ đĩa, từ màn hình My computer hoặc từ màn hình Explorer, chọn ổ đĩa bằng cách kích kép chuột trái vào tên ổ đĩa. Để đổi tên ổ đĩa, từ màn hình My computer (hoặc từ Explorer) chọn ổ đĩa muốn đổi tên, nhân phím phải chuột, chọn rename, nhập vào tên mới và nhấn Enter Hình 1. 11 Đổi tên ổ đĩa cứng 1.10. Thiết lập cách biểu diên ngày giờ, số và tiền tệ Sau khi cài đặt Windows, máy tính sẽ biểu diễn ngày, giờ, số, tiền tệ theo chuẩn của Mỹ, muốn thiết lập lại cách biểu diễn này, khởi động bộ công cụ control panel bằng menu start | settings | Control panel cửa sổ Control Panel xuất hiện. Để thay đổi cách biểu diễn ngày giờ, từ màn hình Control Panel, chọn Date, Time, Language, and Regional Options | Regional and Language Options. Hộp thoại Regional and Language Options xuất hiện, chọn Customize. Thẻ number có các lựa chọn về biểu diễn dữ liệu kiểu số. Trong đó Decimal symbol thể hiện cách biểu diễn dấu thập phân; degital grouping symbol biểu diễn dấu ngăn cách lớp đơn vị, lớp nghìn, lớp triệu ; negative sign symbol thể hiện biểu diễn dấu âm và list seperator thể hiện dấu ngăn cách danh sách. Lựa chọn cách biểu diễn thích hợp và kích chọn OK. Thẻ date thể hiện cách biểu diễn ngày. Như có thể thấy trong hình 1.13, người sử dụng có thể cài đặt cách biểu diễn ngày kiểu Việt nam theo dạng dd-mm-yyyy. Trong đó dd là hai chữ số biểu diễn ngày; mm là hai chữ số biểu diễn tháng; yyyy là bốn chữ số biểu diễn năm. 1.11. Chạy chương trình trong Windows. Để chạy chương trình trong windows, thực hiện một trong các cách sau: Cách 1: Tìm và chạy chương trình từ shortcut trên desktop. Nếu chương trình cài đặt có tạo một shortcut trên nền desktop thì chạy chương trình bằng cách kích kép chuột vào shortcut Phòng Thực hành kinh doanh – Bộ môn Tin học ứng dụng = trang 9=
  11. Trần Công Nghiệp - Giáo trình Tin học ứng dụng (bản thảo) trên desktop. Hình 1. 12 Công cụ Control Panel Hình 1. 13 Hộp thoại Customize Regional Option – thẻ Number Hình 1. 14 Hộp thoại Customize Regional Option – thẻ Date Cách 2: Tìm và chạy chương trình từ menu Start. Chọn Start | Programs . Chọn chương trình muốn chạy và kích đúp phím trái chuột để chạy. Hình 1.14 mô tả cách chạy chương trình Microsoft Excel từ menu Start. Cách 3: Tìm và chạy chương trình từ menu Start | Run. Để chạy chương trình từ cửa sổ Run của menu Start, có thể nhập trực tiếp tên chương trình vào cửa sổ Run như hình 1.15. Chú ý cần nhập đầy đủ cả đường dẫn. Có thể dùng nút Browse để tìm đến vị trí có chương trình càn chạy và thực hiện chạy chương trình.Chỉ các file có phần mở rộng là *.com, *.exe, *.bat mới có thể thực thi được. Phòng Thực hành kinh doanh – Bộ môn Tin học ứng dụng = trang 10=
  12. Trần Công Nghiệp - Giáo trình Tin học ứng dụng (bản thảo) Hình 1. 15 Chạy chương trình từ menu programs Hình 1. 16 Chạy chương trình từ cửa sổ Run 2. CĂN BẢN VỀ EXCEL 2.1 Giới thiệu Microsoft Excel là phần mềm bảng tính điện tử nổi tiếng trong gói phần mềm Mcrosoft Office của Microsoft. Excel chạy trên môi trường Windows và được dùng phổ biến trong công tác văn phòng, trong quản lí bởi tính đơn giản, trực quan và dễ sử dụng của nó. Cho đến thời điểm hiện tại (năm 2007) Microsoft đã đưa ra thị trường phiên bản Office 7.0. Tuy nhiên trên thị trường đang sử dụng phổ biến phiên bản Office XP hoặc Office 2003. Giáo trình này trình bày các điểm chung ứng dụng cho các phiên bản Office 97, 2000, XP, 2003 nhưng minh họa bằng Office 2003. Để cài mới Office hoặc cài đặt bổ sung thêm một số tính năng của Office. Có thể cài đặt Office từ đĩ CD hay từ ổ cứng. Nếu cài dặt từ CD thì đưa đĩa CD vào ổ CD quá trình cài đặt sẽ tự động tiến hành. Nếu cài đặt từ ổ cứng thì tìm đếm thư mục chứa bộ cài đặt và chạy file Setup.exe. Thông thường bộ cài đặt thường chứa tại D:\setup\Office. Hình 1.16 minh họa cách chạy file setup.exe trong thư mục D:\setup\office2003. Khi cửa sổ cài đặt hiện ra yêu cầu nhập số đăng ký (product key) tiến hành nhập như trong hình 1.17. Nhấn NEXT khi quá trình nhập số đăng ký kết thúc. Chấp nhận các điều kiện về bản quyền phần mềm và nhấn NEXT. Cửa sổ như hình 1.18 hiện ra, chọn cài đặt Custom Install để chọn các tính năng nâng cao ứng dụng cho môn học Tin học ứng dụng. Ở bước tiếp Phòng Thực hành kinh doanh – Bộ môn Tin học ứng dụng = trang 11=
  13. Trần Công Nghiệp - Giáo trình Tin học ứng dụng (bản thảo) theo chọn Choose Advanced Customization of Applications. Nhấn NEXT để tiếp tục quá trình cài đặt. Hình 1. 17 Chạy file setup để cài đặt MS Office Hình 1. 18 Nhập số đăng ký và chấp nhận điều kiện bản quyền Khi quá trình cài đặt kết thúc, nhấn OK. Để khởi động Excel, thực hiện một trong các cách sau Cách 1: Kích đúp vào biểu tượng Excel trên nền desktop. Cách 2: Từ menu Start | Programs | Microsft office | Microsoft Office Excel 2003 như trong hình 1.14. Phòng Thực hành kinh doanh – Bộ môn Tin học ứng dụng = trang 12=
  14. Trần Công Nghiệp - Giáo trình Tin học ứng dụng (bản thảo) Hình 1. 19 Chọn Custom Install để cài các tính năng nâng cao cho Excel Hình 1. 20 Chọn Runall from Mycomputer để cài đặt các gói Add-ins cho Excel Các thành phần chính của màn hình làm việc của Excel như trong hình 1.20 Phòng Thực hành kinh doanh – Bộ môn Tin học ứng dụng = trang 13=
  15. Trần Công Nghiệp - Giáo trình Tin học ứng dụng (bản thảo) Hình 1. 21 Màn hình làm việc của Excel Thanh tiêu đề (Title): Thanh tiêu đề chứa biểu tượng của Excel, tên tệp hiện tại, và nút thu nhỏ cửa sổ (minimize), nút phóng to cửa sổ (maximize) và nút đóng cửa sổ (close). Thanh bảng chọn (Menu): Thanh menu liệt kê các lệnh chính của Excel. Mỗi mục ứng với một menu dọc, để mở menu dọc nháy chuột vào tên mục. Thanh công cụ chuẩn (Standard): Thanh công cụ standard chứa một số lệnh thông dụng của Excel dưới dạng các nút biểu tượng. Các lệnh này có thể được truy xuất trực tiếp bằng chuột. Để hiện tên các nút trỏ chuột lên trên nút mà không nháy chuột. Thanh công cụ đinh dạng (Formatting): Thanh formattng chứa các lệnh dưới dạng các nút có biểu tượng để định dạng dữ liệu của bảng tính như kiểu font, cỡ font Thanh công thức (Formula): gồm các ô Namebox (hiển thị toạ độ của ô hiện hành), Insert Function (nhập công thức vào ô), Formula bar (nội dung dữ liệu của ô hiện hành). Thanh cuốn (Scroll): Hai thanh trượt bên phải và bên trái cửa sổ dể hiển thị những phần bị che khuất của bảng tính. Dòng trạng thái (Status): Dòng chứa chế độ làm việc hiện hành như Ready (sẵn sàng nhập dữ liệu), Enter (đang nhập dữ liệu), Point (đang ghi chép công thức tham chiếu đến một địa chỉ), Edit (đang điều chỉnh dữ liệu hay công thức trong ô hiện hành) hay ý nghĩa lệnh hiện hành của bảng tính và các tình trạng hiện hành của hệ thống như NumLock, Capslock Danh sách sheet và các nút dịch chuyển (Sheet tab): Tên các sheet được hiển thị trên các vạt (tab) ở góc trái dưới cửa sổ workbook. Để di chuyển ví trí của sheet trong workbook kích giữ và thả chuột đến ví trí mới. Vùng làm việc: Với phiên bản 2003 về trước vùng làm việc bao gồm hơn 16 triệu ô bảng tính . 2.2 Worksheet, workbook, địa chỉ Workbook. Một file bảng tính Excel được gọi là một Workbook. Workbook ngầm định có tên là book#. Khi mới khởi động, file bảng tính đầu tiên ngầm định có tên là book1.xls. Phần mở rộng XLS do Excel tự động thêm vào khi đặt tên file. Worksheet: Mỗi workbook chứa 256 worksheet (hay chartsheet). Các worksheet hay chartsheet được gọi là các trang bảng tính. Các trang này có thể chứa dữ liệu, công thức tính toán, đồ thị, bản đồ. Thêm một Worksheet mới: Dùng một trong các cách sau để chèn thêm một Sheet mới C1: Vào menu Insert | Worksheet. C2: Kích phải chuột lên một sheet bất kỳ nào đó trên bảng tính chọn Insert, hộp thoại Insert Phòng Thực hành kinh doanh – Bộ môn Tin học ứng dụng = trang 14=
  16. Trần Công Nghiệp - Giáo trình Tin học ứng dụng (bản thảo) hiện ra như hình 1.21,chọn Worksheet và bấm nút OK để chèn. Hình 1. 22 Chèn thêm worksheet Hình 1. 23 Đổi tên worksheet Đổi tên một Sheet: Các bước đổi tên một sheet bao gồm: B1: Chọn Sheet muốn đổi tên. B2: Dùng một trong các cách: C1: Kích phải chuột lên Sheet đã chọn, chọn Rename, sau đó nhập tên cho Sheet như hình 1.22. C2: Vào menu Format | Sheet | Rename, sau đó nhập tên cho Sheet B3: Nhập tên xong gõ Enter để kết thúc Sắp xếp vị trí các sheet. Để sắp xếp vị trí các sheet, chọn một trong các cách sau: C1: Nhấp chuột vào tên sheet muốn di chuyển kéo và thả vào vị trí mong muốn. C2: Kích phải chuột lên sheet muốn di chuyển chọn Move or Copy, hộp thoại Move or Copy hiện ra. To book: Vị trí Workbook muốn sao chép tới hoặc di chuyển tới Before sheet: Vị trí đích muốn sheet chuyển tới. Hình 1. 24 Sắp xếp vị trí worksheet Xoá bỏ một Sheet. Để xóa worksheet, dùng một trong các cách sau C1: Chọn Sheet muốn xoá, vào menu Edit | Delete sheet C2: Kích phải chuột vào sheet muốn xoá chọn Delete Nếu sheet không chứa dữ liệu thì Excel xóa mà không hỏi gì. Nếu sheet có chứa dữ liệu, sẽ Phòng Thực hành kinh doanh – Bộ môn Tin học ứng dụng = trang 15=
  17. Trần Công Nghiệp - Giáo trình Tin học ứng dụng (bản thảo) hiện ra hộp thoại hỏi xác nhận thao tác xóa như hình 1.24. Để xác nhận thao tác xóa, nhấn nút delete. Để hủy nhấn nút cancel. Hình 1. 25 Hộp thoại xác nhận xóa dữ liệu Cell. Mỗi worksheet chứa hơn 16 triệu ô (cell). Ô là giao của các cột và các dòng. Từ phiên bản Office 2003 trở về trước, mỗi sheet có 256 cột ký hiệu từ A-IV. Đồng thời mỗi sheet có 65536 dòng ký hiệu từ 1- 65536. Trong phiên bản Excel 2007, số cột trong một worksheet là 16 384 đánh số từ A- XFD và số dòng là 1 048 576 đánh số từ 1-1 048 576. Do đó mỗi sheet có 17 178 820 608 000. Các ô được tham khảo bằng địa chỉ ô. Dạng địa chỉ thông dụng là Cột Dòng Ví dụ ô A1 là giao của cột A dòng 1. Ngoài ra còn sử dụng dạng địa chỉ R1C1 (số thứ tự dòng số thứ tự cột) Để chọn một dòng trong bảng tính, bấm chuột trái vào chữ số ký hiệu dòng đó. Để chọn một cột, bấm chuột trái vào chữ cái ký hiệu cột đó. Để chọn một vùng gồm nhiều ô liên tục, bấm giữ chuột trái và kéo. Cũng có thể sử dụng phím shift kết hợp với phím mũi tên hoặc phím shift kết hợp với bấm chuột trái để chọn một vùng. Để chọn nhiều vùng không liền nhau, nhấn giữ phím Ctrl trong khi bấm chuột trái. Để chọn cả bảng tính, bấm chuột trái vào vị trí góc trên cùng bên trái của bảng tính, nơi giao nhau của tên hàng và tên cột hoặc dùng tổ hợp phím Ctrl + A. Để điều chỉnh độ rộng của dòng bấm giữ và kéo chuột trên đường phân cách giữa hai vị trí tiêu đề tên cột. Để thay đổi độ rộng của dòng, bấm giữ và kéo chuột trên đường phân cách giữa hai dòng tại vị trí tiêu đề dòng. Để thay đổi độ rộng của nhiều cột, chọn vùng có các cột cần thay đổi độ rộng, vào menu Format | Column | Width, hộp thoại column width xuất hiện. Gõ vào độ rộng cột cần thay đổi rồi chọn OK hay nhấn Enter. Làm tương tự cho dòng (row). Địa chỉ tương đối: Địa chỉ tương đối có dạng cột dòng ví dụ B3. Một công thức có chứa địa chỉ tương đối khi copy đến vị trí mới địa chỉ sẽ tự động biến đổi. Địa chỉ tuyệt đối: Địa chỉ tuyệt đối có dạng $cột$dòng. Ví dụ $B$3. Một công thức có chứa địa chỉ tuyệt đối khi copy đến vị trí mới địa chỉ không thay đổi. Địa chỉ hỗn hợp: Địa chỉ hỗn hợp là dạng kết hợp của cả địa chỉ tương đối và địa chỉ tuyệt đối theo dạng $cột dòng (ví dụ $B3) hay cột $dòng (ví dụ B$3). Khi copy một công thức có chứa địa chỉ hỗn hợp, thành phần tuyệt đối không biến đổi, thành phần tương đối biến đổi. Để chuyển đổi giữa các dạng địa, sử dụng phím chức năng F4. Phòng Thực hành kinh doanh – Bộ môn Tin học ứng dụng = trang 16=
  18. Trần Công Nghiệp - Giáo trình Tin học ứng dụng (bản thảo) 2.3 Các dạng dữ liệu trong Excel Nắm vững các dạng dữ liệu rất quan trọng để giải các bài toán trong Excel. Để định dạng dữ liệu, lựa chọn (bôi đen) vùng dữ liệu, chọn menu format | cells | numbers. Cửa sổ như hình 1.24 hiện ra. Có thể sử dụng tổ hợp phím tắt Ctrl + 1 hoặc kích chuột phải vào vùng đã chọn cũng cho cùng kết quả. Hình 1. 26 Định dạng dữ liệu Có các dang dữ liệu sau General - Dữ liệu tổng quát: Kiểu này do Excel tự động nhận dạng Number - Dữ liệu số: Dữ liệu kiểu số tự động căn thẳng bên phải ô. Theo ngầm định, kiểu số sử dụng dấu chấm (.) để ngăn cách phần thập phân và phần nguyên. Biểu diễn số âm trong Excel sử dụng 4 cách như trong hình 1.26 Cách 1: Sử dụng dấu trừ (-) như trong toán học. Cách 2: Sử dụng dấu ngoặc đơn như trong kế toán. Cách 3: Sử dụng màu đỏ. Cách 4: Vừa dùng màu đỏ vừa dùng dấu ngoặc đơn. Các hàm tài chính của Excel sử dụng cách thứ tư. Để sử dụng dấu phân cách phần nghìn, bấm chọn ô Use 1000 separator (,). Thay đổi số các số sau dấu phân cách thập phân trong ô Decimal places. Hình 1. 27 Biểu diễn số âm trong Excel Currency - Dữ liệu kiểu tiền tệ: Dữ liệu kiểu tiền tệ tự động căn thẳng bên phải ô. Dạng này Phòng Thực hành kinh doanh – Bộ môn Tin học ứng dụng = trang 17=
  19. Trần Công Nghiệp - Giáo trình Tin học ứng dụng (bản thảo) biểu diễn các đơn vị tiền tệ của các nước trên thế giới. Dữ liệu kiểu tiền tệ biểu diễn số thập phân giống như dữ liệu kiểu số. Accounting - Dữ liệu kiểu kế toán: Dữ liệu kiểu kế toán tự động căn thẳng bên phải ô. Dữ liệu kiểu kế toán cũng biểu diễn các đơn vị tiền tệ tương tự như dữ liệu kiểu currency, tuy nhiên không có cách biểu diễn số âm. Date - Kiểu ngày tháng: Dữ liệu kiểu ngày tháng tự động căn thẳng bên phải ô. Kiểu ngày tháng có thể thực hiện với các phép tính số học. Theo ngầm định, kiểu ngày tháng nhập vào Excel theo dạng MM/DD/YY. (MM: Biểu diễn tháng. DD: Biểu diễn ngày. YY: Biểu diễn năm). Trong cửa sổ type liệt kê các cách hiển thị ngày trong Excel. Time - Kiểu thời gian: Kiểu thời gian tự động căn thẳng bê phải ô. Biểu diễn thời gian trong Excel có dạng HH:MM:SS. (HH: Chỉ giờ. MM: Chỉ phút SS: Chỉ giây). Percentage - Kiều phần trăm: Kiểu phần trăm tự động căn thẳng bên phải ô. Kiểu phần trăm đổi một số sang dạng phần trăm bằng cách nhân với 100 và thêm dấu % vào sau số đó. Fraction - Kiểu phân số: Kiểu phân số tự động căn thẳng bên phải ô. Kiểu phân số biểu diễn các số ở dạng phân số. Kiểu hiển thị được chọn trong mục Type. Text - Kiểu ký tự: Kiểu ký tự là sự pha trộn của các chữ cái các chữ số và các ký tự đặc biệt Dữ liệu kiểu ký tự tự động căn trái. Sử dụng dữ liệu kiểu ký tự trong hàm hoặc trong các phép toán phải được bao giữa cặp dấu nháy kép (“”). Lưu ý: Cặp dấu nháy kép khác 2 cặp dấu nháy đơn (‘’). . Chú ý: Với các dãy kí tự bắt đầu bằng số 0 vô nghĩa (ví dụ số điện thoại 0913 ) khi nhập vào bảng tính, Excel sẽ tự động cắt đi số 0 đầu tiên. Để giữ lại số 0 này, sử dụng dấu (‘) trước khi dãy kí tự hoặc định dạng ô kiểu ký tự. Scientific - Kiểu rút gọn: Áp dụng cho kiểu số. Khi biểu diễn các số quá lớn hoặc quá nhỏ, Excel đưa về dạng rút gọn để tiết kiệm không gian. Ví dụ: 123.45 = 1.23E+02; 0.00123 = 1.23E-03 Special - Kiểu đặc biệt: Kiểu này dùng để biểu diễn các dạng đặc biệt như mã số bưu điện, số điện thoại kiểu Mỹ Custom - Kiểu do người dùng định nghĩa: Với kiểu này người dùng có thể định nghĩa cách hiển thị các dạng dữ liệu theo ý muốn. Ví dụ: Khi nhập ngày vào Excel, có thể sử dụng cách ngầm định MM/DD/YY (tháng/ngày/năm). Sau đó sử dụng kiểu custom để định dạng thành ngày kiểu Việt Nam bằng cách nhập vào cửa sổ type DD/MM/YYYY (Ngày/Tháng/Năm) như trong hình 1.26 2.4 Các phép tính trong Excel Trong Excel có thể thực hiện các phép toán số học (+, -, *, / và ^), các phép toán logic (>=, <= ), và phép toán ghép chuỗi (&). Bảng 1.1 tóm tắt các phép toán và cách dùng. Phòng Thực hành kinh doanh – Bộ môn Tin học ứng dụng = trang 18=
  20. Trần Công Nghiệp - Giáo trình Tin học ứng dụng (bản thảo) Hình 1. 28 Dùng kiểu Custom để định dạng ngày tháng Bảng 1. 1 Các phép tính trong Excel Phép toán Ý nghĩa Ví dụ + Phép cộng 1+2 = 3 - Phép trừ 5-2 = 3 * Phép nhân 3*5 = 15 / Phép chia 27/3 = 9 ^ Luỹ thừa 2^3 = 23 = 8 % Phần trăm 5% = 0.05 & Nối chuỗi “Việt” & “Nam” = “Việt Nam” > Lớn hơn 4 > 3 = True = Lớn hơn hoặc băng “Aa” >= “aa” = False Khác “a” <> “A” = True Thứ tự thực hiện các phép toán như sau: phép toán lũy thừa, phép toán nhân chia, phép toán cộng trừ, phép toán logic.Để thực hiện các phép toán trái với thứ tự này, sử dụng dấu ngặc đơn ( ). 2.5 Sử dụng hàm trong Excel Hàm (Function) trong Excel là một tổ hợp các công thức đã được xây dựng sẵn nhằm thực hiện các tính toán hay xử lí chuyên biệt nào đó. Hàm được đặc trưng bởi tên hàm, dấu mở đóng ngoặc và danh sách các tham số. VD: Hàm tính tổng =Sum(2,5,3) = 10. Tên hàm là các tên chuẩn do Excel quy định. Dấu mở đóng ngoặc bắt buộc phải có. Đối số là các dữ liệu đầu vào để tính toán hay xử lý. Đối số có thể có hoặc không có. Nếu có nhiều hơn một đối số thì các đối số được ngăn cách bởi dấu “,”. Trường hợp dấu “,” được sử dụng để Phòng Thực hành kinh doanh – Bộ môn Tin học ứng dụng = trang 19=
  21. Trần Công Nghiệp - Giáo trình Tin học ứng dụng (bản thảo) làm dấu thập phân thì dấu ngăn cách là dấu “;”. Xem hình 1.12 mục 1.10 để thiết lập cách biểu diễn kiểu số. Các đối số có thể là kiểu số, kiểu ngày tháng, là địa chỉ ô, kiểu ký tự, có thể là hàm khác. Số lượng đối số tối đa là 30. Trong Excel 2007, số lượng đối số tối đa là 64. Nếu ký tự nhập vào từ bàn phím thì phải bao trong cặp dấu nháy kép (ví dụ “A”). Khi sử dụng các hàm lồng nhau, hàm nọ làm đối số cho hàm kia, Excel cho phép các hàm lồng nhau tối đa là 7 cấp. Trong Excel 2007, hàm có thể lồng nhau tối đa là 50 cấp. Để nhập hàm vào bảng tính, sử dụng một trong các cách sau: Cách 1: Nhập hàm trực tiếp vào ô chứ kết quả tính toán theo cú pháp = tenham(danh sách tham số). Có thể nhập trực tiếp từ thanh công thức. Cách 2: Nhập hàm từ giao diện Insert function. Truy cập menu Insert | function hoặc từ biểu tượng fx trên thanh công thức Khi đó, hộp thoại insert fucntion sẽ hiện ra như hình 1.29. chọn tên hàm từ mục select a fucntion. Có thể chọn mục select a category để chọn nhóm hàm nhằm thu hẹp phạm vi tìm kiếm hàm. Mục category chỉ ra một số nhóm hàm như nhóm hàm tài chính (finacial), nhóm hàm ngày tháng và thời gian (date & time), nhóm hàm toán học và lượng giác (math & trig), nhóm hàm thống kê (statistical), nhóm hàm tìm kiếm và tham khảo (lookup & reference), nhóm hàm cơ sở dữ liệu (database), nhóm hàm xử lý ký tự (text), nhóm hàm logic (logical), nhóm hàm thông tin bảng tính (information) và nhóm hàm kỹ thuật máy tính (engineering). Hình 1. 29 Hộp thoại Insert function Một số hàm thông dụng trong Excel 2.5.1. Một số hàm toán học Bảng 1.2 trình bày một số hàm toán học thông dụng Bảng 1. 2 Một số hàm toán học thông dụng Tên hàm Công dụng Cú pháp, giải thích ABS Tính trị tuyệt đối của một =ABS(number) số numbers số muốn lấy trị tuyệt đối. AVERAGE Tính trung bình cộng của =AVERAGE(number1,number2, ) các đối số number1,number2 có thể có từ 1 đến 30 số cần tính trung bình cộng. Nếu đối số là tham số kiểu text, logic, ô rỗng thì giá trị đó được bỏ qua. Nếu Phòng Thực hành kinh doanh – Bộ môn Tin học ứng dụng = trang 20=
  22. Trần Công Nghiệp - Giáo trình Tin học ứng dụng (bản thảo) Tên hàm Công dụng Cú pháp, giải thích là zero thì được tính. COUNTIF Đếm các ô trong phạm vi =COUNTIF(range, criteria) thỏa mãn điều kiện đã cho Range: vùng cần đếm Criteria: điều kiện EXP Tính lũy thừa cơ số e =EXP(number) number số mũ của cơ số e. INT Làm tròn xuống số=INT(number) nguyên gần nhất number số cần làm tròn LN Tính logarit cơ số tự=LN(number) nhiên của một số number là số thực dương ta muốn tính logarit tự nhiên của nó. LN là nghịch đảo của EXP: lũy thừa cơ số e. LOG Tính logarit =LOG(number, base) number là số thực dương ta muốn tính logarit. base là cơ số để tính logarit, mặc định là 10. MOD Tính phần dư của phép =MOD(number, divisor) chia Number: Số bị chia Divisor: Số chia POWER Tính lũy thừa của một số =POWER(number, power) number cơ số, power số mũ. Có thể dùng toán tử ^ để thay thế hàm POWER. SUMIF Tính tổng tất cả các số=SUMIF(range,criteria,sum_range) trong dãy ô thỏa điều kiện range dãy ô muốn tính toán. Nó có thể là dãy ô chứa điều kiện hoặc dãy ô vừa chứa điều kiện, vừa chứa các giá trị để tính tổng. criteria điều kiện để tính tổng có thể là số, biểu thức, hoặc kiểu văn bản. sum_range là dãy giá trị cần tính tổng. Nếu không có sum_range thì range là dãy chứa giá trị để tính tổng. SUMPRODUCT Tính tổng các tích các =SUMPRODUCT(array1, array2, ) phần tử tương ứng trong array1,array2, có thể có từ 2 đến 30 dãy số các mảng giá trị cùng kích thước. Nếu các mảng giá trị không cùng kích thước hàm sẽ trả về lỗi #VALUE! Một phần tử bất kỳ trong mảng không phải là số thì coi là zero. SQRT Tính căn bậc 2 của một số =SQRT(number) number số thực bất kỳ. Nếu number là số âm, hàm trả về lỗi #NUM! SUMSQ Tính tổng bình phương =SUMQ(number1, number2, ) các các số number1,number2, có thể có từ 1 đến 30 số thực bất kỳ SUMX2PY2 Tính tổng của tổng bình =SUMX2PY2(array_x,array_y) phương các phần tử tương array_x,array_y, dãy ô hoặc giá trị kiểu ứng của 2 mảng giá trị mảng. Nếu các mảng giá trị không cùng kích thước hàm sẽ trả về lỗi #VALUE!. Một phần tử bất kỳ trong mảng rỗng, kiểu text, kiểu logic Phòng Thực hành kinh doanh – Bộ môn Tin học ứng dụng = trang 21=
  23. Trần Công Nghiệp - Giáo trình Tin học ứng dụng (bản thảo) Tên hàm Công dụng Cú pháp, giải thích được bỏ qua, zero được tính. SUM Tính tổng của các số =SUM(number1, number2, ) number1, number2 có 1 đế 30 số cần tính tổng. Số, giá trị logic, hay chữ đại diện cho số mà bạn gõ trực tiếp vào công thức thì được tính. Công thức tham chiếu tới giá trị logic, text đại diện cho số thì giá trị đó được bỏ qua. 2.5.2. Một số hàm xử lý ký tự Bảng 1.3 liệt kê một số hàm xử lý ký tự thông dụng. Các hàm khác có thể tham khảo trong danh sách các hàm của Excel. Bảng 1. 3 Một số hàm xử lý ký tự thông dụng Tên hàm Công dụng Cú pháp LEFT Trích bên trái một chuỗi =LEFT(text,num_chars) text là chuỗi cần trích ký tự. num_chars là ký tự cần trích bên trái chuỗi text LEN Tính độ dài một chuỗi =LEN(text) text là nội dung cần xác định độ dài MID Trích chuỗi con từ một chuỗi =MID(text,start_num,num_chars) text là chuỗi hoặc tham chiếu đến chuỗi. start_num: vị trí bắt đầu trích lọc chuỗi con trong text. num_chars: số ký tự của chuỗi mới cần trích từ chuỗi text. Nếu start_num: lớn hơn chiều dài chuỗi text thì hàm trả về chuỗi rỗng "". start_num: nhỏ hơn 1 hàm trả về lỗi #VALUE!. num_chars: âm MID trả về lỗi #VALUE! RIGHT Trích bên phải một chuỗi =RIGHT(text, num_chars) text là chuỗi cần trích ký tự. num_chars là ký tự mà bạn cần trích bên phải chuỗi text. Nếu num_chars lớn hơn độ dài của chuỗi thì sẽ trả về toàn bộ chuỗi text. num_chars nếu bỏ qua thì mặc định là 1. num_chars không nhận số âm SEARCH Tìm kiếm một chuỗi SEARCH(find_text,within_text,star_num) find_text: chuỗi cần tìm. Có thể dùng ký tự ? để đại diện một ký tự đơn, dùng * để đại diện một nhóm ký tự. Nếu muốn tìm dấu ? hoặc dấu * thì gõ dấu ~ trước ký tự đó. within_text: chuỗi chứa chuỗi cần tìm. star_num: vị trí bắt đầu tìm kiếm. SEARCH không phân biệt chữ thường, chữ hoa. SERACH tìm không có kết quả sẽ trả về lỗi Phòng Thực hành kinh doanh – Bộ môn Tin học ứng dụng = trang 22=
  24. Trần Công Nghiệp - Giáo trình Tin học ứng dụng (bản thảo) Tên hàm Công dụng Cú pháp #VALUE! TRIM Xóa tất cả các ký tự trắng của =TRIM(text) chuỗi trừ những khỏang đơn dùng text chuỗi cần xóa các ký tự trắng. để làm khỏang cách bên trong chuỗi. VALUE Chuyển một chuỗi thành số. =VALUE(text) text là chuỗi văn bản đại diện cho một số. text là định dạng số, ngày tháng, hoặc thời gian bất kỳ được Microsoft Excel công nhận. Nếu không phải định dạng trên sẽ trả về lỗi #VALUE! 2.5.3.Một số hàm logic Bảng 1.4 nêu một số hàm logic thường dùng. Thông tin về các hàm logic khác độc giả tham khảo trong trợ giúp trực tuyến của Excel. Bảng 1. 4 Một số hàm logic Tên hàm Công dụng Cú pháp =AND(logical_1,logical_2, ) Trả về kết quả TRUE nếu tất cảlogical_1,logical_2 là các đều kiện cần kiểm điều kiện đều TRUE, Trả vềtra. Các điều kiện có thể là biểu thức, vùng AND FALSE nếu một trong các điều tham chiếu hoặc mảng giá trị. Các điều kiện kiện FALSE. phải có giá trị là TRUE hoặc FALSE. Nếu 1 trong các điều kiện có giá trị không phải Logic, hàm AND trả về lỗi #VALUE! =OR(logical_1,logical_2, ) logical_1,logical_2 là các đều kiện cần kiểm Trả về TRUE nếu một trong các tra. Các điều kiện có thể là biểu thức, vùng OR điều kiện là TRUE. Trả về FALSE tham chiếu hoặc mảng giá trị. Các điều kiện nếu tất cả các điều kiện là FALSE. phải có giá trị là TRUE hoặc FALSE. Nếu 1 trong các điều kiện có giá trị không phải Logic, hàm AND trả về lỗi #VALUE! =IF(logical_test,value_if_true,value_if_false) logical_test: điều kiện để xét, logical có thể là Trả về một giá trị nếu điều kiện là kết quả của một hàm luận lý như AND, OR, IF đúng, Trả về một giá trị khác nếu value_if_true: giá trị trả về nếu điều kiện điều kiện là sai. logical_test là TRUE. value_if_false: giá trị trả về nếu điều kiện logical_test là FALSE. 2.5.4.Một số hàm tìm kiếm và tham chiếu Bảng 1. 5 Một số hàm tìm kiếm Tên hàm Công dụng Cú pháp Dò tìm một giá trị ở dòng đầu =HLOOKUP(lookup_value,table_array, HLOOKUP tiên của một bảng dữ liệu. Nếu row_index_num,range_lookup) Phòng Thực hành kinh doanh – Bộ môn Tin học ứng dụng = trang 23=
  25. Trần Công Nghiệp - Giáo trình Tin học ứng dụng (bản thảo) Tên hàm Công dụng Cú pháp tìm thấy sẽ trả về giá trị ở cùng lookup_value: là tìm một giá trị dùng để tìm cột với giá trị tìm thấy trên hàng kiếm, nó có thể là một giá trị, một tham chiếu được chỉ định. Hàm HLOOKUP hay một chuỗi ký tự. table_array là vùng chứa thường dùng để điền thông tin dữ liệu cần tìm. Đây là bảng dữ liệu phụ có vào bảng dữ liệu từ bảng dữ liệu nội dung thường cố định, bao quát để lấy dữ phụ. liệu. Các giá trị ở dòng đầu tiên có thể là giá trị số, chuỗi ký tự, hoặc logic. Nếu range_lookup là TRUE thì các giá trị ở dòng đầu tiên của bảng dữ liệu phụ này phải được sắp xếp tăng dần Nếu không hàm HLOOKUP sẽ trả giá trị không chuẩn xác. row_index_num số thứ tự dòng trên bảng dữ liệu phụ mà dữ liệu cần lấy. Giá trị trả về nằm trên dòng chỉ định này và ở cột mà hàm tìm thấy giá trị dò tìm lookup_value. range_lookup là giá trị logic được chỉ định để HLOOKUP tìm kiếm chính xác hay là tương đối. Nếu range_lookup là TRUE hàm sẽ trả về kết quả tìm kiếm tương đối. Nếu không tìm thấy kết quả chính xác, nó sẽ trả về một giá trị lớn nhất mà nhỏ hơn giá trị tìm kiếm lookup_value. Nếu range_lookup là FALSE hàm tìm kiếm chính xác, nếu không có trả về lỗi #N/A Khi dùng hàm HLOOKUP để điền dữ liệu cho một bảng dữ liệu thì trong công thức cần phải tạo địa chỉ tuyệt đối cho bảng dữ liệu phụ table_array để công thức đúng cho các hàng còn lại khi copy công thức xuống các ô bên dưới. Nếu lookup_value nhỏ hơn giá trị nhỏ nhất trong dòng đầu tiên của bảng dữ liệu phụ, HLOOKUP trả về lỗi #N/A!. Dò tìm một giá trị ở cột đầu tiên =VLOOKUP(lookup_value,table_array,ro bên trái của một bảng dữ liệu. w_index_num,range_lookup) Nếu tìm thấy sẽ trả về giá trị ởÝ nghĩa các tham số và cách sử dụng tương tự cùng trên dòng với giá trị tìm như HLOOKUP VLOOKUP thấy trên cột mà bạn chỉ định. Hàm VLOOKUP thường dùng để điền thông tin vào bảng dữ liệu từ bảng dữ liệu phụ. 2.5.5.Một số hàm ngày tháng và thời gian Bảng 1.6 trình bày một số hàm ngày tháng và thời gian thông dụng. Các hàm ngày tháng và thời gian khác độc giả tham khảo trong trợ giúp trực tuyến (Help) của Excel. Phòng Thực hành kinh doanh – Bộ môn Tin học ứng dụng = trang 24=
  26. Trần Công Nghiệp - Giáo trình Tin học ứng dụng (bản thảo) Bảng 1. 6 Một số hàm thời gian Tên hàm Công dụng Cú pháp =DATE(year,month,day) year số chỉ năm, có thể có từ 1 đến 4 ký số. Microsoft Excel tự biên dịch đối số năm tùy thuộc vào đối số ngày tháng đang dùng. Nếu số năm year nằm từ 0 đến 1899 thì nó được Trả về một chuỗi hoặc một số thểcộng với 1900 để tính. Ví dụ year là 105 thì hiện một ngày tháng đầy đủ. Nếu year được hiểu trong công thức là 2005. Nếu DATE định dạng ô là General trước khi 1900 = 10,000 thì chuỗi ngày tháng. hàm trả về lỗi #NUM! month số chỉ tháng. Nếu số tháng lớn hơn 12 thì hàm tự quy đổi 12 = 1 năm và cộng vào year số năm tăng lên do số tháng. day số chỉ ngày. Nếu số ngày lớn hơn số ngày của tháng thì hàm tự quy đổi là tăng số tháng. =DAY(serial_number) Trả về thứ tự của ngày từ chuỗi serial_number dạng chuỗi số tuần tự của DAY ngày tháng. ngày cần tìm. Ngày tháng này nên nhập bằng hàm DATE hoặc kết quả trả về từ hàm khác. =DATEVALUE(date_text) date_text là dạng chuỗi văn bản đại diện cho Trả về một chuỗi số thập phân ngày tháng. Ví dụ: "20/11/2005" thì chuỗi bên DATEVAL biểu thị ngày tháng được đại diện trong dấu nháy kép là đại diện cho ngày tháng. UE bởi chuỗi văn bản date_text. date_text trong Excel dành cho Windows giới hạn trong khoảng từ "01/01/1900" đến "31/12/9999" nếu quá khoảng ngày DATEVALUE sẽ trả về lỗi #VALUE!. =MONTH(serial_number) Trả về thứ tự của tháng từ giá trịserial_number dạng chuỗi số tuần tự của MONTH kiểu ngày tháng. tháng cần tìm. Ngày tháng này nên nhập bằng hàm DATE hoặc kết quả trả về từ hàm khác. =YEAR(serial_number) serial_number dạng chuỗi hoặc số thập phân Trả về năm của một giá trị hoặc đại diện ngày tháng mà bạn cần tìm số năm YEAR chuỗi đại diện cho ngày tháng. của nó. Giá trị này nên được nhập bằng hàm DATE hoặc là kết quả các công thức hoặc hàm khác. 2.5.6 Một số hàm cơ sở dữ liệu Các hàm cơ sở dữ liệu cho phép thực hiện các phép tính có nhiều hơn một điều kiện. Tuy nhiên cần xây dựng vùng điều kiện (bảng phụ) trước khi sử dụng hàm cơ sở dữ liệu. Điều kiện trực tiếp là những điều kiện mà không chứa công thức ở trong. Với một trường (cột) có từ 2 điều kiện trở lên thì điều kiện xảy ra đồng thời AND (và) được thể hiện trên cùng 1 dòng. Điều kiện xảy ra không đồng thời OR (hoặc) được thể hiện trên các dòng khác nhau. Điều kiện gián tiếp: Là những điều kiện có chứa công thức bên trong, giá trị của vùng điều kiện này Phòng Thực hành kinh doanh – Bộ môn Tin học ứng dụng = trang 25=
  27. Trần Công Nghiệp - Giáo trình Tin học ứng dụng (bản thảo) là giá trị logic (TRUE hoặc FALSE). Tiêu đề vùng điều kiện gián tiếp không được trùng với tiêu đề của bất kỳ trường nào. Bảng 1. 7 Một số hàm cơ sở dữ liệu Tên hàm Công dụng Cú pháp Đếm xem trong vùng dữ liệu = DCOUNT(database,field,criteria) có bao nhiêu giá trị ở cột cần Database: cơ sở dữ liệu. DCOUNT đếm có dạng số thoả mãn Field: trường cần đếm vùng điều kiện. Criteria: điều kiện =DSUM(database,filed, criteria) Tính tổng các giá trị trong cột Database: cơ sở dữ liệu. cần tính tổng nằm trong vùng DSUM Field: trường cần tính tổng dữ liệu thoả mãn vùng điều Criteria: điều kiện kiện. =DMAX(database,filed, criteria) Database: cơ sở dữ liệu. Tìm giá trị lớn nhất trong cột DMAX Field: trường cần tìm giá trị lớn nhất thỏa mãn vùng điều kiện Criteria: điều kiện =DMIN(database,filed, criteria) Database: cơ sở dữ liệu. Tìm giá trị nhỏ nhất trong cột DMIN Field: trường cần tìm giá trị nhỏ nhất thỏa mãn vùng điều kiện Criteria: điều kiện =DAVERAGE(database,filed, criteria) Tìm giá trị trung bình cộng Database: cơ sở dữ liệu. DAVERAGE trong cột thỏa mãn vùng điều Field: trường cần tìm giá trị trung bình cộng kiện Criteria: điều kiện MỘT SỐ LỖI THƯỜNG GẶP TRONG EXCEL ##### Lỗi độ rộng . Lỗi này sinh ra khi cột thiếu độ rộng. Dùng chuột kéo độ rộng cột ra cho phù hợp. Khi giá trị ngày tháng hoặc thời gian nhập vào là số âm cũng phát sinh lỗi này. #VALUE! Lỗi giá trị. Lỗi này sinh ra khi công thức được nhập vào một chuỗi trong khi hàm yêu cầu một số hoặc một giá trị logic. Có thể do đang nhập một hoặc chỉnh sửa các thành phần của công thức mà vô tình nhấn Enter. Cũng có thể do nhập quá nhiều tham số cho một toán tử hoặc một hàm trong khi chúng chỉ dùng một tham số. Ví dụ =LEFT(A2:A5). Một trường hợp sinh lỗi # VALUE khi là thi hành một marco (lệnh tự động) liên quan đến một hàm mà hàm đó trả về lỗi #VALUE! #DIV/0! Lỗi chia cho 0. Lỗi này sinh ra do nhập vào công thức số chia là 0. Ví dụ = MOD(10,0) hoặc số chia trong công thức là một tham chiếu đến ô trống. #NAME! Sai tên. Lỗi này do dùng những hàm không thường trực trong Excel như EDATE, EOMONTH, NETWORKDAYS, WORKDAYS, Khi đó cần phải vào menu Tools - Add-ins. Phòng Thực hành kinh doanh – Bộ môn Tin học ứng dụng = trang 26=
  28. Trần Công Nghiệp - Giáo trình Tin học ứng dụng (bản thảo) Đánh dấu chọn vào tiện ích Analysis ToolPak. Cũng có thể do nhập sai tên một hàm số. Trường hợp này xảy tra khi dùng bộ gõ tiếng Việt ở chế độ Telex vô tình làm sai tên hàm như IF thành Ì, VLOOKUP thành VLÔKUP. Trường hợp dùng những ký tự không được phép trong công thức cũng phát sinh lỗi này. Một số trường hợp khác bao gồm hhập một chuỗi trong công thức mà không có đóng và mở dấu nháy đôi."" hoặc không có dấu 2 chấm : trong dãy địa chỉ ô trong công thức. #N/A Lỗi dữ liệu. Lỗi này sinh ra khi giá trị trả về không tương thích từ các hàm dò tìm như VLOOKUP, HLOOKUP, LOOKUP hoặc MATCH hoặc dùng hàm HLOOKUP, VLOOKUP, MATCH để trả về một giá trị trong bảng chưa được sắp xếp. Cũng có thể do không đồng nhất dữ liệu khi sử dụng địa chỉ mảng trong Excel. Trường hợp quên một hoặc nhiều đối số trong các hàm tự tạo hoặc dùng một hàm tự tạo không hợp lý cũng sinh ra lỗi này. . #REF! Sai vùng tham chiếu. Lỗi này phát sinh do xóa những ô đang được tham chiếu bởi công thức. Cũng có khi do dán những giá trị được tạo ra từ công thức lên chính vùng tham chiếu của công thức đó. Có thể do liên kết hoặc tham chiếu đến một ứng dụng không thể chạy được. #NUM! Lỗi dữ liệu kiểu số. Lỗi này phát sinh do dùng một đối số không phù hợp trong công thức sử dụng đối số là dữ liệu kiểu số. Ví dụ công thức chỉ tính số dương nhưng lại nhập vào số âm. Có thể do dùng hàm lặp đi lặp lại dẫn đến hàm không tìm được kết quả trả về. Cũng có trường hợp do dùng một hàm trả về một số quá lớn hoặc quá nhỏ so với khả năng tính toán của Excel. #NULL! Lỗi dữ liệu rỗng. Lỗi này do dùng một dãy tóan tử không phù hợp hoặc dùng một mảng không có phân cách. 2.6. Công thức mảng Trong Excel có một khả năng độc đáo là tính toán với các mảng dữ liệu. Các công thức kiểu này được gọi là công thức mảng. Một công thức mảng có thể trả kết quả về một ô hay là một vùng (range). Một số hàm của Excel thao tác với công thức mảng như MINVERSE,MMULT, TREND, LINEST, LOGEST, GROWTH Để nhập công thức mảng, chọn vùng chứa kết quả, nhập công thức mà nhấn đồng thời tổ hợp phím Control+Shift+Enter (CSE). Đây là điểm khác biệt quan trọng giữa công thức mảng và công thức thường. Công thức thường kết thúc việc nhập công thức bằng phím Enter. Chú ý rằng Excel tự động sinh ra cặp dấu { } bao lấy công thức mảng. Sử dụng công thức mảng có các ưu điểm sau: - Tránh được vô tính sao chép sai công thức do chạy địa chỉ tham chiếu - Tránh được việc vô tình xóa hay sửa chữa một ô nào đó của vùng công thức mảng vì công thức mảng không cho phép sửa chữa hay xóa một ô trong vùng có công thức mảng. - Tránh việc người sử dụng không thành thạo Excel làm xáo trộn các công thức 2.6.1 Công thức mảng trả kết quả vể một vùng nhiều ô Phòng Thực hành kinh doanh – Bộ môn Tin học ứng dụng = trang 27=
  29. Trần Công Nghiệp - Giáo trình Tin học ứng dụng (bản thảo) Hình 1.30 minh họa việc sử dụng công thức mảng. Thông thường để tính toán giá trị cho cột thành tiền, cần phải sử dụng 6 phép toán nhân (nhập công thức cho ô D2 rồi copy công thức cho vùng D2:D7). Tuy nhiên khi sử dụng công thức mảng chỉ cần nhập một công thức duy nhất. Chọn vùng sẽ lưu kết quả (trong hình 1.30 là vùng D2:D7), nhập công thức = B2:B7*C2:C7 nhấn CSE. Như vậy vùng D2:D7 khi xem sẽ thấy công thức {= B2:B7*C2:C7}. Hình 1. 30 Công thức mảng trả về một vùng 2.6.2. Công thức mảng trả kết quả về một ô Vận dụng ý tưởng trên, nếu không cần tính toán cột thành tiền mà chỉ cần tính tổng tiền thu được thì có thể sử dụng công thức mảng như sau: {=SUM(B2:B7*C2:C7)}. Hình 1.31 Minh họa cách sử dụng công thức này. Chú ý rằng trường hợp này có thể sử dụng công thức =SUMPRODUCT(B2:B7,C2:C7) cũng cho cùng kết quả. Hình 1. 31 Công thức mảng trả kết quả về một ô Trong các chương sau, ta sẽ có dịp sử dụng một số công thức mảng của Excel áp dụng cho hồi quy và dự báo. 2.6.3 Hiệu chỉnh công thức mảng Do không thể xóa một ô, thay đổi nội dung của ô hay chèn thêm một ô vào vùng có chứa công Phòng Thực hành kinh doanh – Bộ môn Tin học ứng dụng = trang 28=
  30. Trần Công Nghiệp - Giáo trình Tin học ứng dụng (bản thảo) thức mảng, để hiệu chỉnh công thức mảng, kích hoạt thanh công thức hay nhấn phím F2, Excel sẽ bỏ cặp dấu { } và khi hiệu chỉnh xong lại nhấn tổ hợp phím CSE để kết thúc. Để mở rộng hay thu hẹp công thức mảng nhiều ô, chọn toàn bộ vùng chứa công thức mảng trên bảng tính. Nhấn phím F2 để vào chế độ hiệu chỉnh. Nhấn Control+Enter để chuyển về công thức thường. Chọn vùng xuất kết quả mới bằng cách thu hẹp hay mở rộng vùng cũ. Nhấn phím F2 để vào chế độ hiệu chỉnh. Kết thúc bằng CSE. BÀI TẬP CHƯƠNG 1 Bài 1.1. Tạo thư mục trong ổ đĩa C: theo dạng C:\tenthumuc. Trong đó tenthumuc là tên lớp của sinh viên. Bài 1.2. Sử dụng các công thức đã học để điền vào các cột của bảng tính sau Đơn Thành STT Mã HĐ Mặt hàng Ngày Đơn vị SLượng giá tiền 1 K2AM3 07-12 5 2 H5BN4 12-12 2 3 K7AN3 08-12 12 4 H9BP4 09-12 7 5 B9NAB 11-12 3 6 B8NCB 07-12 5 7 K3AB3 10-12 6 8 K5AL0 14-12 4 9 B9NPB 13-12 10 10 K8AP3 14-12 20 Tổng Cộng Bảng Danh Mục Đơn giá Mã Tên mặt hàng Đơn vị (1000 đ) KA3 Giấy phô to A3 Ram 39,5 HB4 Bút chì đen Tá 15,2 KA0 Giấy A0 Tờ 10 BNB Bút bi Bến nghé Hộp 22,1 Tổng số tiền bán được của các mặt hàng Giấy Bút chì Bút bi Sử dụng ký tự thứ 1, thứ 3 và 5 để tim tên hàng trong bản danh mục. Tính tổng số tiền bán được bằng hàm DSUM. Ghi bài vào thư mục vừa tạo ở câu 1 theo dạng C:\tenthumuc\tenfile.xls. Trong đó tenfile bao gồm “họ tên sinh viên, chương 1, bài số 2”. Phòng Thực hành kinh doanh – Bộ môn Tin học ứng dụng = trang 29=
  31. Trần Công Nghiệp - Giáo trình Tin học ứng dụng (bản thảo) Bài 1.3. Nhập bảng I bảng II bảng III trong sheet 1. Nhập danh sách khách hàng trong sheet 2 Mã đk Số cũ Số mới 1259 CQC 572 793 1574SHB 2550 2802 2337SHA 1500 1580 4210CQB 3200 3450 4879CQA 4400 4650 5046CQD 2800 2970 5282KDB 4500 4980 5781SHC 2400 2450 6254KDA 1880 2200 8219KDC 1250 1350 9231KDD 4321 4492 Bảng phu I Bảng phụ II K vực SH CQ KD Kvực A B C D A 500 800 900 SH 150 100 80 75 B 600 900 1000 CQ 250 200 170 150 C 650 1000 1250 KD 200 150 120 100 D 700 1200 1350 Bảng phụ III Vượt DM Số lần Hệ số Dưới 1 1.5 Từ1 lần đến dưới 2 2.0 Trên hoặc bằng 2 3.0 Yêu cầu:. Tính số điện tiêu thụ của từng khách hàng.Tính định mức điện cho từng khách hàng dựa vào 2 ký tự thứ 5&6, và ký tự cuối của Mã DK và bảng II. Tính giá điện cho từng khách hàng dựa vào 2 ký tự thứ 5 & 6 và ký tự cuối Mã DK và bảng phụ I. Tính tiền điện đối với phần tiêu thụ điện trong định mức. Tính tiền điện đối với phần tiêu thụ vượt định mức dự theo hệ số phụ số lần vượt định mức (KTT-DM)/DM trong bảng III Tính số tiền khách hàng phải trả Sắp thứ tự bảng tính theo cột định mức giảm dần. Tính bảng thống kê sau SH CQ KD Tổng tiền điện Tiền điện vượt định mức cao nhất Tổng tiền điện vượt định mức Dựa vào bảng này vẻ biểu đồ Tổng tiền điện và tiền điện vượt định mức cho từng khách hàng Trích thông tin về những khách hàng tiêu thụ vượt định mức khu vực D. Phòng Thực hành kinh doanh – Bộ môn Tin học ứng dụng = trang 30=
  32. Trần Công Nghiệp - Giáo trình Tin học ứng dụng (bản thảo) Ghi bài vào thư mục vừa tạo ở câu 1 theo dạng C:\tenthumuc\tenfile.xls. Trong đó tenfile bao gồm “họ tên sinh viên ,chương 1, bài số 3” Bài 1.4. Sử dụng các công thức đã học của Excel để điền vào các cột của bảng tính sau Tên S Đơn Thành Loại hđ Ngày Mã hàng Loại lượng giá tiền X 23-02 FECT-L1 100 N 25-02 ALXD-L2 50 X 01-03 FECT-L2 20 X 15-03 FECT-L3 50 X 28-03 ALXD-L1 100 N 29-03 FECC-L1 500 X 04-04 ALXD-L3 30 2 ký tự đầu của mã hàng Tên hàng Đơn giá xuất hàng (1kg) FE Thép xây dựng 2 kí tự cuối L1 L2 L3 AL Nhôm xây dựng Loại hàng Loại 1 Loại 2 Loại 3 FE 80000 60000 20000 AL 120000 100000 40000 Thống kê Tổng thành tiền Tên hàng Xuất Nhập FE AL Nếu loại hóa đơn là X (xuất) thì tra trong bảng phụ. Nếu loại hóa đơn là N (nhập) thì giá chỉ bằng 85% giá trong bảng phụ. Thành tiền = Sluợng*Đơn giá. Nếu hóa đơn bằng X và ngày 15/3 thì được giảm 10%. Điền thông tin vào bảng thống kê. Ghi bài vào thư mục vừa tạo ở câu 1 theo dạng C:\tenthumuc\tenfile.xls. Trong đó tenfile bao gồm “họ tên sinh viên ,chương 1, bài số 4” Bài 1.5. Sử dụng các công thức đã học của Excel để điền vào các cột của bảng tính sau Giá điện vượt định mức bằng giá điện định mức*1.35 Tạo bảng thống kê số tiền các hộ CQ, và các hộ CB phải nộp. Phòng Thực hành kinh doanh – Bộ môn Tin học ứng dụng = trang 31=
  33. Trần Công Nghiệp - Giáo trình Tin học ứng dụng (bản thảo) Tiền Tiền Điện Chủ Loại Số Trong Vượt Phải Số cũ trong Ngoài kế hộ hộ mới Đ mức Đ mức trả ĐM ĐM DK001 CQ-A 210 280 DK002 NN-D 220 330 DK003 CB-A 270 300 DK004 CB-C 270 350 DK005 CQ-A 300 340 DK006 CB-E 280 330 DK007 CB-C 210 340 DK008 CN-D 230 350 DK009 NN-D 260 320 DK010 CN-E 270 340 Bảng Định mức CQ CB CN KD NN A 50 40 20 60 30 B 60 40 50 80 60 C 70 55 70 95 85 D 75 65 95 120 90 E 85 70 110 150 95 Bảng giá điện (US Cent) A B C D E CQ 30 40 45 55 70 CB 15 25 25 50 50 CN 12,5 24,5 20,5 50,5 40,5 KD 35,7 60 70,5 80 90,5 NN 10 20 15 45 35 Ghi bài vào thư mục vừa tạo ở câu 1 theo dạng C:\tenthumuc\tenfile.xls. Trong đó tenfile bao gồm “họ tên sinh viên ,chương 1, bài số 5” Bài 1.6. Sử dụng các công thức đã học của Excel để điền vào các cột của bảng tính sau Số Tên Ngày lượng Tiền Thành STT Mã HĐ hàng bán (kg) Đơn giá giảm tiền 1 HF01E 15-01 50,5 2 HC02U 10-02 100,0 3 HX01M 20-04 200,0 Phòng Thực hành kinh doanh – Bộ môn Tin học ứng dụng = trang 32=
  34. Trần Công Nghiệp - Giáo trình Tin học ứng dụng (bản thảo) 4 HF03E 30-03 20,8 5 HC08U 20-04 50,8 6 HA02L 10-02 30,5 7 HX03M 15-01 50,2 Bảng đơn giá Mã Tên Đơn giá mỗi tháng (đồng/1kg) hàng hàng 1 2 3 4 FE Sắt 5000 5500 5000 5500 AL Nhôm 7000 8000 9000 9000 CU Đồng 3000 300 3500 4000 Xi XM măng 8000 8500 9000 10000 Bảng thống kê Tháng 1 2 3 4 Tổng thành tiền Tổng số lượng của mặt hàng xi măng bán trong tháng 1 Ghi bài vào thư mục vừa tạo ở câu 1 theo dạng C:\tenthumuc\tenfile.xls. Trong đó tenfile bao gồm “họ tên sinh viên ,chương 1, bài số 6”. Bài 1.7. Sử dụng các công thức đã học của Excel để điền vào các cột của bảng tính sau Thành Số Ngày Mã chuyến Địa điểm Miễn giảm tiền 1 15-07 CH04 2 20-08 CH02 3 01-07 CH01 4 05-09 CH03 5 01-08 CH04 6 10-07 CH03 Bảng phụ Mã chuyến Địa điểm Loại xe Giá CH01 SaPa 52 15,000,000 đồng CH02 Hà Nội 24 20,000,000 đồng CH03 Lạng sơn 12 5,000,000 đồng CH04 Hạ Long 52 10,000,000 đồng Bảng thống kê Tháng Tổng số Tổng giá trị Phòng Thực hành kinh doanh – Bộ môn Tin học ứng dụng = trang 33=
  35. Trần Công Nghiệp - Giáo trình Tin học ứng dụng (bản thảo) chuyến 7 8 9 Nếu địa điểm là Sapa thì được giảm 10%. Ghi bài vào thư mục vừa tạo ở câu 1 theo dạng C:\tenthumuc\tenfile.xls. Trong đó tenfile bao gồm “họ tên sinh viên ,chương 1, bài số 7”. Bài 1.8. Sử dụng các công thức đã học của Excel để điền vào các cột của bảng tính sau Ghi bài vào thư mục vừa tạo ở câu 1 theo dạng C:\tenthumuc\tenfile.xls. Trong đó tenfile bao gồm “họ tên sinh viên ,chương 1, bài số 8”. Đơn Số Ngày Thành STT Mã Hàng Tên hàng vị lượng bán Đơn giá tiền 1 PC586 14 02-04 2 PC686 10 12-04 3 FD120 12 09-04 4 HD420 4 22-04 5 HD850 10 18-04 6 PC586 4 27-04 7 FD120 10 11-04 8 FD185 10 06-04 Bảng tồn kho tháng 3 Đơn giá Số Mã hàng Tên hàng (USD) lượng PC586 Máy tính 586 700 20 PC686 Máy tính 686 1000 30 HD420 Đĩa cứng Samsung 80,5 20 HD850 Đĩa cứng MAXTOR 120,2 14 FD120 USB Ultra 4,9 10 FD185 USB Traveller 6,2 20 Tổng kết tháng 4 Mặt hàng Số lượng Thành tiền PC HD FD Phòng Thực hành kinh doanh – Bộ môn Tin học ứng dụng = trang 34=
  36. Trần Công Nghiệp - Giáo trình Tin học ứng dụng (bản thảo) CHƯƠNG 2 GIẢI BÀI TOÁN TỐI ƯU 1. CƠ SỞ LÝ THUYẾT CỦA BÀI TOÁN TỐI ƯU Trong toán học, thuật ngữ tối ưu hóa chỉ việc nghiên cứu các bài toán có dạng Cho trước: một hàm f : A -> R từ tập hợp A tới tập số thực Tìm: một phần tử x0 thuộc A sao cho f(x0) ≤ f(x) với mọi x thuộc A ("cực tiểu hóa") hoặc sao cho f(x0) ≥ f(x) với mọi x thuộc A ("cực đại hóa"). Một phát biểu bài toán như vật đôi khi được gọi là một quy hoạch toán học (mathematical programming). Nhiều bài toán thực tế và lý thuyết có thể được mô hình theo cách tổng quát trên. Miền xác định A của hàm f được gọi là không gian tìm kiếm. Thông thường, A là một tập con của không gian Euclid Rn, thường được xác định bởi một tập các ràng buộc, các đẳng thức hay bất đẳng thức mà các thành viên của A phải thỏa mãn. Các phần tử của A được gọi là các lời giải khả thi. Hàm f được gọi là hàm mục tiêu, hoặc hàm chi phí. Lời giải khả thi nào cực tiểu hóa (hoặc cực đại hóa, nếu đó là mục đích) hàm mục tiêu được gọi là lời giải tối ưu. Thông thường, sẽ có một vài cực tiểu địa phương và cực đại địa phương, trong đó một cực tiểu địa phương x* được định nghĩa là một điểm thỏa mãn điều kiện: với giá trị δ > 0 nào đó và với mọi giá trị x sao cho XX-* £d (2. 1) công thức sau luôn đúng f(X*) ≤ f(X) (2. 2) Nghĩa là, tại vùng xung quanh x*, mọi giá trị của hàm đều lớn hơn hoặc bằng giá trị tại điểm đó. Cực đại địa phương được định nghĩa tương tự. Thông thường, việc tìm cực tiểu địa phương là dễ dàng. Cần thêm các thông tin về bài toán (chẳng hạn, hàm mục tiêu là hàm lồi) để đảm bảo rằng lời giải tìm được là cực tiểu toàn cục. Các lĩnh vực con chính của tối ưu hóa bao gồm: quy hoạch tuyến tính, quy hoạch phi tuyến, quy hoạch lõm, quy hoạch lồi 1.1. Bài toán quy hoạch tuyến tính (linear programming) Mô hình toán học của bài toán QHTT tổng quát có thể viết như sau: n Hàm mục tiêu: f (x1, , x2 ) = åc j x j ® max(min) (2. 3) j=1 với các ràng buộc (điều kiện): n å aij x j = bi ,(i Î I1 ) (2. 4) j=1 n å aij x j ³ bi ,(i Î I 2 ) (2. 5) j=1 Phòng Thực hành kinh doanh – Bộ môn Tin học ứng dụng = trang 35=
  37. Trần Công Nghiệp - Giáo trình Tin học ứng dụng (bản thảo) n å aij x j £ bi ,(i Î I3 ) (2. 6) j=1 xj là các số thực Trong đó: I1, I2, I3 là tập các chỉ số (I1, I2, I3 không giao nhau), aij, bi, cj với là các hằng số (có thể là tham số), n là số biến số. xj với là các biến số (ẩn số) của bài toán. Một số khái niệm: (1) Một nhóm ràng buộc có hệ véc tơ tương ứng độc lập tuyến tính được gọi là các ràng buộc độc lập tuyến tính. Các ràng buộc dấu luôn là độc lập tuyến tính. (2) Phương án: Một véc tơ x = (x1,x2, ,xn) thoả mãn hệ ràng buộc của bài toán gọi là một phương án của bài toán. Để phân biệt tính chất của các ràng buộc (cả ràng buộc dấu) đối với một phương án cụ thể, người ta đưa ra khái niệm ràng buộc chặt và ràng buộc lỏng. + Nếu đối với phương án x mà ràng buộc i thoả mãn với dấu đẳng thức (2.4) hoặc xi = 0 (nếu là ràng buộc dấu) thì phương án x thoả mãn chặt ràng buộc i hay ràng buộc i là chặt đối với phương án x. + Nếu đối với phương án x mà ràng buộc i thoả mãn với dấu bất đẳng thức (2.5), (2.6) hoặc xi > 0, xi < 0 (tuỳ thuộc ràng buộc loại gì) thì phương án x thoả mãn lỏng ràng buộc i hay ràng buộc i là lỏng đối với phương án x. Ràng buộc i có dạng phương trình thì nó sẽ là chặt với mọi phương án của bài toán, nếu có dạng bất phương trình thì nó có thể là chặt đối với phương án này và là lỏng đối với phương án kia. (3) Phương án tối ưu (phưong án tốt nhất): Một phương án mà tại đó trị số hàm mục tiêu đạt cực tiểu (hoặc cực đại) gọi là phương án tố ưu. (4) Phưong án tốt hơn: Xét bài toán có f(x) min (max) và hai phương án x1, x2 của nó. Phương án x1 gọi là tốt hơn phương án x2 nếu f(x1) ≤ f (x2) (hay f(x1) ≥ f(x2). Nếu có các dấu bất đẳng thức thực sự thì gọi là tốt hơn thực sự. Một bài toán có tồn tại phương án tối ưu gọi là bài toán giải được và ngược lại nếu không có phương án tối ưu gọi là bài toán không giải được. Bài toán không giải được là do một trong hai nguyên nhân sau: + Bài toán không có phương án + Bài toán có phương án, nhưng hàm mục tiêu không bị chặn dưới khi tìm min f(x) hoặc không bị chặn trên nếu khi tìm max f(x) trên tập phương án. (5) Phương án cực biên (PACB): Một phương án thoả mãn chặt n ràng buộc độc lập tuyến tính được gọi là phương án cực biên. Phòng Thực hành kinh doanh – Bộ môn Tin học ứng dụng = trang 36=
  38. Trần Công Nghiệp - Giáo trình Tin học ứng dụng (bản thảo) Một bài toán có số ràng buộc (kể cả ràng buộc dấu nếu có) ít hơn n thì chắc chắn sẽ không có phương án cực biên dù nó có phương án. Phương án cực biên thoả mãn chặt đúng n ràng buộc gọi là phương án cực biên không suy biến, thoả mãn chặt hơn n ràng buộc gọi là phương án cực biên suy biến. Nếu tất cả các phương án cực biên của bài toán đều không suy biến thì gọi là bài toán không suy biến, ngược lại là bài toán suy biến. 1.1.1.Bài toán quy hoạch tuyến tính dạng chính tắc Bài toán quy hoạch tuyến tính dạng chính tắc có các ràng buộc dạng phương trình và các ràng buộc về dấu ở dạng bất phương trình theo dạng sau. Tìm max (hoặc min) của hàm f(x) = c1x1 + c2x2 + + cnxn (2. 7) Thỏa mãn các ràng buộc a11x1 + a12x2 + + a1nxn = b1 xj ≥ 0 ; j= 1 n Nếu các ràng buộc phương trình trở thành ràng buộc bất phương trình thì bài toán được gọi là bài toán quy hoạch tuyến tính dạng chuẩn. 1.1.2.Bài toán quy hoạch tuyến tính mở rộng Một lớp các bài toán quy hoạch tuyến tính mở rộng như bài toán đầu tư, bài toán lập kế hoạch sản xuất hay bài toán vận tải được phát biểu theo dạng sau: 1.1.2.1.Bài toán khẩu phần thức ăn Người ta cần có một lượng (tối thiểu) chất dinh dưỡng i=1,2, ,m do các thức ăn j=1,2, ,n cung cấp. Gọi : aij là số lượng chất dinh dưỡng loại i có trong 1 đơn vị thức ăn loại j. (i=1,2, ,m) và (j=1,2, , n) bi là nhu cầu tối thiểu về loại dinh dưỡng i cj là giá mua một đơn vị thức ăn loại j Vấn đề đặt ra là phải mua các loại thức ăn như thế nào để tổng chi phí bỏ ra là ít nhất mà vẫn đáp ứng được yêu cầu về dinh dưỡng. Vấn đề này được giải quyết theo mô hình sau đây : Gọi xj ≥0 (j= 1,2, ,n) là số lượng thức ăn thứ j cần mua . Tổng chi phí cho việc mua thức ăn là : n z= = c x + c x + + c x (2. 8) åcj x j 1 1 2 2 n n j=1 Vì chi phí bỏ ra để mua thức ăn phải là thấp nhất nên yêu cầu (2.9) cần được thỏa mãn Phòng Thực hành kinh doanh – Bộ môn Tin học ứng dụng = trang 37=
  39. Trần Công Nghiệp - Giáo trình Tin học ứng dụng (bản thảo) Tìm min z = c1x1 + c2x2 + + cnxn (2. 9) Lượng dinh dưỡng i thu được từ thức ăn 1 là : ai1x1 (i=1 -> m) Lượng dinh dưỡng i thu được từ thức ăn 2 là : ai2x2 Lượng dinh dưỡng i thu được từ thức ăn n là : ainxn Vậy lượng dinh dưỡng thứ i thu được từ các loại thức ăn là : ai1x1+ai2x2+ +ainxn (i=1 ->m) Vì lượng dinh dưỡng thứ i thu được phải thỏa yêu cầu bi về dinh dưỡng loại đó nên có ràng buộc sau : ai1x1+ai2x2+ +ainxn ≥ bi (i=1 ->m) Tóm lại, bài toán dẫn đến mô hình toán sau đây : Tìm min z = c1x1 + c2x2 + + cnxn Thỏa mãn các ràng buộc a11x1 + a12x2 + + a1nxn ≥b1 a21x1 + a22x2 + + a1nxn ≥b2 am1x1 + am2x2 + + amnxn ≥bm xj ≥ 0 ; j= 1 n 1.1.2.2.Bài toán lập kế hoạch sản xuất Từ m loại nguyên liệu hiện có người ta muốn sản xuất n loại sản phẩm. Gọi : aij là lượng nguyên liệu loại i dùng để sản xuất 1 sản phẩm loại j. (i=1,2, ,m) và (j=1,2, , n) bi là số lượng nguyên liệu loại i hiện có cj là lợi nhuận thu được từ việc bán một đơn vị sản phẩm loại j Vấn đề đặt ra là phải sản xuất mỗi loại sản phẩm là bao nhiêu sao cho tổng lợi nhuận thu được từ việc bán các sản phẩm lớn nhất trong điều kiện nguyên liệu hiện có. Gọi xj ≥ 0 là số lượng sản phẩm thứ j sẽ sản xuất (j=1,2, ,n).Tổng lợi nhuận thu được từ việc bán các sản phẩm là: Z = c1x1 + c2x2 + + cnxn Vì yêu cầu lợi nhuận thu được cao nhất nên cần tìm max hàm mục tiêu nghĩa là: Max z= c1x1 + c2x2 + + cnxn Lượng nguyên liệu thứ i=1->m dùng để sản xuất sản phẩm thứ 1 là ai1x1 Phòng Thực hành kinh doanh – Bộ môn Tin học ứng dụng = trang 38=
  40. Trần Công Nghiệp - Giáo trình Tin học ứng dụng (bản thảo) Lượng nguyên liệu thứ i=1->m dùng để sản xuất sản phẩm thứ 2 là ai2x2 Lượng nguyên liệu thứ i=1->m dùng để sản xuất sản phẩm thứ n là ainxn Vậy lượng nguyên liệu thứ i dùng để sản xuất tất cả các sản phẩm là ai1x1+ai2x2+ +ainxn Vì lượng nguyên liệu thứ i=1->m dùng để sản xuất các loại sản phẩm không thể vượt quá lượng được cung cấp là bi nên : ai1x1+ai2x2+ +ainxn ≤  bi (i=1,2, ,m) Vậy theo yêu cầu của bài toán, xây dựng được mô hình sau đây: Tìm max z = c1x1 + c2x2 + + cnxn Thỏa mãn các ràng buộc a11x1 + a12x2 + + a1nxn ≤ b1 a21x1 + a22x2 + + a2nxn ≤ b2 am1x1 + am2x2 + + amnxn ≤ bn xj ≥ 0 (j= 1,2, , n) 1.1.2.3.Bài toán vận tải Người ta cần vận chuyển hàng hoá từ m kho đến n cửa hàng bán lẻ. Lượng hàng hoá ở kho i là si (i=1,2, ,m) và nhu cầu hàng hoá của cửa hàng j là dj (j=1,2, ,n). Cước vận chuyển một đơn vị hàng hoá từ kho i đến của hàng j là cij (j=1,2, ,n). Cước vận chyển một đơn vị hàng hoá từ kho i đến của hàng j là cij ³ 0. Giả sử tổng hàng hóa có ở các kho và tổng nhu cầu hàng hóa có ở các cửa hàng là bằng nhau (cân bằng thu phát), tức là: n m (2. 10) åSi= å d j i=1 j = 1 Trường hợp không cân bằng thu phát thì người ta tạo ra một cột thu (phát) giả có cước vận chuyển từ các kho i đến cửa hàng thu giả j bằng không. Bài toán đặt ra là lập kế hoạch vận chuyển để tiền cước là nhỏ nhất, với điều kiện là mỗi cửa hàng đều nhận đủ hàng và mỗi kho đều trao hết hàng. Gọi xij ≥ 0 là lượng hàng hoá phải vận chuyển từ kho i đến cửa hàng j. Cước vận chuyển chuyển hàng hoá i đến tất cả các kho j là: n (2. 11) åcij x ij j=1 Cước vận chuyển tất cả hàng hoá đến tất cả kho sẽ là : m n z= (2. 12) ååcij x ij i=1 j = 1 Căn cứ vào yêu cầu của bài toán, xây dựng được mô hình toán sau: m n Tìm min z= (2. 13) ååcij x ij i=1 j = 1 Thỏa mãn các ràng buộc: Phòng Thực hành kinh doanh – Bộ môn Tin học ứng dụng = trang 39=
  41. Trần Công Nghiệp - Giáo trình Tin học ứng dụng (bản thảo) m å xij = d j (j= 1,2, ,n) i=1 Xij ≥ 0 (i=1,2, ,m) (j=1,2, ,n) 1.2. Bài toán quy hoạch phi tuyến (nonlinear programming) Bài toán quy hoạch phi tuyến, khác với bài toán quy hoạch tuyến tính, là một lớp các bài toán tối ưu mà các hàm số có mặt trong bài toán không nhất thiết là tuyến tính. Về cơ bản có thể phát biểu bài toán quy hoạch phi tuyến như sau: cho trước các hàm số f, g1, , gm của n biểu thức, hãy xác định vectơ n chiều x = (x1, x2, , xn) thoả mãn các điều kiện xj ≥ 0, j = 1, 2, , n. gi(x) ≤ 0, i = 1, 2, , m. và đạt cực tiểu toàn cục của hàm mục tiêu f(x). Cần chú ý rằng, về nguyên tắc mỗi bài toán Quy hoạch phi tuyến nhờ phương pháp hàm phạt có thể đưa về một dãy các bài toán cực trị không điều kiện. Tuy nhiên, trong trường hợp này thường các bài toán phụ là những bài toán nhiều cực trị và việc giải chúng gặp rất nhiều khó khăn. Trong thực tế kinh tế và kế hoạch sản xuất (nói riêng, trong các vấn đề phân bổ) thường gặp một cách có hệ thống các bài toán mà trong đó các ràng buộc là tuyến tính, còn hàm cần làm cực đại là lồi (hàm cần làm cực tiểu là lõm). Để giải bài toán nhiều cực trị này đã có các phương pháp đặc biệt sử dụng sự kiện là vectơ tối ưu cho mỗi bài toán ấy trùng một trong các đỉnh của đa diện ràng buộc. 2. QUY TRÌNH GIẢI BÀI TOÁN TỐI ƯU TRONG EXCEL 2.1 Mô tả bài toán Để giải thành công các bài toán tối ưu, bước đầu tiên và quan trọng nhất là mô tả bài toán. Trong bước này cần xác định được các biến quyết định, hàm mục tiêu và các ràng buộc. Thông thường, ngoài các ràng buộc không âm, đôi khi là các ràng buộc các biến quyết định chỉ nhận giá trị nguyên hay giá trị nhị phân, còn có các ràng buộc hạn chế về năng lực sản xuất, về dự trữ nguyên vật liệu Các kiến thức chuyên ngành như marketing, quản trị sản xuất hay kinh tế sẽ giúp cho người lập bài toán mô tả thành công các ràng buộc của bài toán. Trong bài toán kế hoạch sản xuất, các biến quyết định là sản lượng mối loại hàng hóa cần được sản xuất. Các ràng buộc là giới hạn về nguyên vật liệu và ràng buộc về dấu của các biến quyết định. Hàm mục tiêu là lợi nhuận của kế hoạch sản xuất. Trong bài toán vận tải, hàm mục tiêu là tổng cước phí vận tải của phương án. Các biến quyết định là lượng hàng vận chuyển từ Si đến dj. Các ràng buộc là tổng lượng hàng có ở kho i và toognr lượng hàng nhập về cửa hàng j. Ngoài ra còn có ràng buộc về dấu của các biến quyết định. Tùy từng trường hợp cụ thể để xác định các biến quyết đinh, xây dựng hàm mục tiêu và các ràng buộc. Chỉ có mô tả đúng bài toán mới có thể hy vọng tìm được lời giải đúng cho bài Phòng Thực hành kinh doanh – Bộ môn Tin học ứng dụng = trang 40=
  42. Trần Công Nghiệp - Giáo trình Tin học ứng dụng (bản thảo) toán. 2.2 Các bước tiến hành giải bài toán tối ưu trong Excel 2.2.1 Cài thêm trình Solver để giải bài toán tối ưu trong Excel Trình cài thêm (add-ins) Solver thường có mặt trong gói phần mềm MS Office khi cài đặt với lựa chọn complete (cài đủ) hoặc khi lựa chọn cài đặt custom (theo ý người sử dụng) với lựa chọn cho Excel là run all from my computer (cài đặt Excel với đầy đủ các thành phần). Để cài thêm Solver, truy cập menu Tools/ add-ins, chọn Solver add-in trong cửa sổ add-ins như hình 2.1. Nhấn OK để hoàn tất việc bổ sung Solver add-in. Trường hợp không thấy Solver add-in trong hộp thoại hình 2.1 nghĩa là MS Office đã cài đặt chưa đủ, cần tiến hành cài đặt lại MS Office như đã trình bày trong mục 2.1 chương 1. Trình cài thêm Solver của Excel cho phép giải được các bài toán tối ưu bao gồm cả quy hoạch tuyến tính, quy hoạch phi tuyến, quy hoạch nguyên với số biến số tối đa là 200 và số lần lặp tối đa là 32767 (ngầm định là 100 lần) và thời gian tối đa để giải bài toán 32767 giây (ngầm định là 100 giây). Solver sử dụng thuật toán giảm gradient (Generalized Reduced Gradient - GRG2) cho các bài toán tối ưu hóa phi tuyến (nonlinear optimization) do Leon Lasdon, ở University of Texas, Austin, và Allan Waren, ở Cleveland State University xây dựng. Phương pháp tìm kiếm cực trị của Solver là phương pháp Newton và phương pháp gradient (conjugate). Ở đây Solver coi bài toán tối ưu tuyến tính là trường hợp riêng của bài toán tối ưu phi tuyến. H.2. 1 Hộp thoại add-ins để cài thêm trình Solver 2.2.2 Xây dựng bài toán trong Excel Sau bước mô tả bài toán và chuẩn bị công cụ Solver add-ins, cần tiến hành xây dựng bài toán trong Excel. Quy trình xây dựng bài toán tối ưu trong Excel bao gồm các công việc sau: - Tạo một bộ nhãn bao gồm: hàm mục tiêu, tên các biến quyết định, các ràng buộc. Bộ nhãn này có tác dụng giúp đọc kết quả dễ dàng trong Excel. - Gán cho các biến quyết định một giá trị khởi đầu bất kỳ. Có thể chọn giá trị khởi đầu bằng không. Phòng Thực hành kinh doanh – Bộ môn Tin học ứng dụng = trang 41=
  43. Trần Công Nghiệp - Giáo trình Tin học ứng dụng (bản thảo) - Xây dựng hàm mục tiêu. - Xây dựng các ràng buộc. - Truy cập menu Tools/Solver. Xuất hiện cửa sổ Solver paramaters như hình 2.2. Nhập các tham số cho trình Solver và chọn solve. - Phân tích các kết quả của bài toán. H.2. 2 Hộp thoại Solver parameters để nhập các tham số cho bài toán tối ưu Các thuật ngữ trong hộp thoại Solver parameters trình bày trong bảng 2.1. Các lựa chọn và ý nghĩa các nút lệnh trong hộp thoại Solver Options được trình bày trong bảng 2.2 Bảng 2. 1 Ý nghĩa các nút lệnh trong hộp thoại Solver parameters Thuật ngữ Ý nghĩa Set taret cell Ô chứa hàm mục tiêu (ô đích) Equal to max Chọn mục này khi cần tìm max của hàm mục tiêu Equal to min Chọn mục này khi cần tìm min của hàm mục tiêu Equal to value of Chọn mục này và nhập giá trị vào ô hình chữ nhật bên cạnh nếu muốn ô đích bằng một giá trị nhất định. By changing cells Chọn các ô chứa các biến của bài toán Subject to the Mục này dùng để nhập các ràng buộc của bài toán constrains Hiển thị hộp thoại Add constraint để thêm các ràng buộc Hiển thị hộp thoại Change Constraint để thay đổi ràng buộc Để xóa ràng buộc đã chọn Để đoán các giá trị trong các ô không chứa công thức do công thức trong ô đích (target cell) trỏ đến. Thực hiện việc giải bài toán Đóng hộp thoại Solver parameters mà không tiến hành giải bài toán Hiển thị hộp thoại Solver options để ghi mô hình bài toán, nạp lại mô hình đã ghi hoặc nhập các lựa chọn khác cho việc giải bài toán Xóa các thiết lập cho bài toán hiện tài và khôi phục các thiết lập ngầm định Hiển thị trợ giúp cho Solver 2.2.3 Ví dụ 2.1: Giải bài toán quy hoạch tuyến tính sau dùng Solver Phòng Thực hành kinh doanh – Bộ môn Tin học ứng dụng = trang 42=
  44. Trần Công Nghiệp - Giáo trình Tin học ứng dụng (bản thảo) Tìm max f(x) = 8x1 + 6x2 Thỏa mãn các ràng buộc 4x1 + 2x2 ≤ 60 2x1 + 4x2 ≤ 48 x1, x2 ≥ 0 Bước 1: Bố trí dữ liệu trong bảng tính Excel như hình 2.3. H.2. 3 Bố trí dữ liệu trong bảng tính cho ví dụ 1 Bước 2. Tiến hành giải bài toán Chuyển con trỏ đến ô $D$4. Truy cập menu Tools/ Solver; xuất hiện hộp thoại Solver parameters. Mục Equal to chọn Max nếu làm cực đại hàm mục tiêu, chọn Min nếu làm cực tiểu hàm mục tiêu. Mục by changing cells chọn các ô chứa các biến của bài toán, trong ví dụ này là khối ô $B$3: $C$3 như hình 2.4. H.2. 4 Nhập các tham số cho Solver Nhấn nút add để nhập ràng buộc. Hộp thoại add constraint xuất hiện như trong hình 2.5. Phòng Thực hành kinh doanh – Bộ môn Tin học ứng dụng = trang 43=
  45. Trần Công Nghiệp - Giáo trình Tin học ứng dụng (bản thảo) Nhập ràng buộc của bài toán.( Trong hình 2.4 dòng đầu tiên của mục Subject to the constraints là các ràng buộc không âm trên các biến. Dòng tiếp sau là hai phương trình ràng buộc). Trong hình 2.5 hộp Cell Reference để chọn các ô cần đặt ràng buộc lên chúng. Hộp giữa để chọn loại ràng buộc. Có các loại ràng buộc như >=, <=, =, integer (số nguyên), binary (số nhị phân, chỉ nhận giá trị 0 hay 1). Kết thúc việc nhập ràng buộc bằng nút OK. Sau khi kết thúc nhập ràng buộc, hộp thoại Solver parameters xuất hiện trở lại như hình 2.4. Nhấn nút options để hiển thị Solver option như hình 2.6. Chọn mục Assum Linear Model. H.2. 5 Hộp thoại nhập ràng buộc Nhấn OK để trở lại màn hình 2.4 và chọn Solve để giải bài toán. Hộp thoại Solver Results xuất hiện như trong hình 2.7. Kết quả giải bài toán khi chọn Keep Solver Solution từ hình 2.7 nằm trong các ô $D$4:$C$4 và giá trị hàm mục tiêu nằm trong ô $D$4 như trong hình 2.8. Kết quả là phương án tối ưu là X= (12; 6) và giá trị hàm mục tiêu f(x) = 132. H.2. 6 Hộp thoại Solver options để nhập các tùy chọn Phòng Thực hành kinh doanh – Bộ môn Tin học ứng dụng = trang 44=
  46. Trần Công Nghiệp - Giáo trình Tin học ứng dụng (bản thảo) H.2. 7 Lựa chọn cách hiển thị kết quả khi giải bài toán tối ưu Trường hợp chọn Restore Original Values, các giá ô trong bảng tính được đưa trở lại giá trị ban đầu như hình 2.3. Kết hợp với Report Answer sẽ cho kết quả sang một trang bảng tính (sheet) riêng với các tham số như hình 2.9. Trong kết quả từ hình 2.9, giá trị Slack ứng với các ràng buộc ≤ cho thấy các nguồn lực còn chưa sử dụng hết khi lấy vế phải của ràng buộc trừ đi giá trị các biến quyết định nhân với hệ số tương ứng của ràng buộc đó. Giá trị Surplus ứng với các ràng buộc mang dấu ≥ cho thấy nguồn lực bị vượt quá tại điểm đã cho. 2.2.4. Ví dụ 2.2 Một công ty sản xuất 3 loại sản phẩm ký hiệu lần lượt là SP1, SP2, SP3 bằng cách lắp ráp từ các chi tiết tiêu chuẩn hóa ký hiệu CT1, CT2, CT3, CT4, CT5. Số lượng mỗi chi tiết trong từng sản phẩm, số lượng dự trữ của từng loại chi tiết và lợi nhuận đơn vị của từng loại sản phẩm cho trong bảng 2.2. Giả sử thị trường có khả năng tiêu thụ hết số sản phẩm được sản xuất. Hãy tìm phương án sản xuất để tối đa hóa lợi nhuận. Lợi nhuận ứng với phương án đó bằng bao nhiêu? Bảng 2. 2 dữ kiện bài toán trong ví dụ 2 Tên chi tiết Lượng dự trữ Số lượng chi tiết trong mỗi sản phẩm SP1 SP2 SP3 CT1 450 1 1 0 CT2 250 1 0 0 CT3 800 2 2 1 CT4 450 1 1 0 CT5 600 2 1 1 Lợi nhuận đơn vị (USD/sp) 75 50 35 H.2. 8 Hiển thị kết quả giải bài toán trongví dụ 1 khi lựa chọn keep solution Phòng Thực hành kinh doanh – Bộ môn Tin học ứng dụng = trang 45=
  47. Trần Công Nghiệp - Giáo trình Tin học ứng dụng (bản thảo) H.2. 9 Hiển thị kết quả giải bài toán trong ví dụ 1 khi chọn Report Answer Bước 1: Mô tả bài toán. Các biến quyết định trong trường hợp này là sản lượng mỗi loại sản phẩm, ký hiệu x1, x2, x3 (điều kiện x1, x2, x3 không âm và là số nguyên). Hàm mục tiêu là tìm max tổng lợi nhuận có dạng: MAX Z= 75 X1 + 50X2 + 35 X3. Các ràng buộc ngoài ràng buộc về dấu của các biến quyết định còn có ràng buộc về số lượng chi tiết sử dụng để sản xuất sản phẩm không được vượt quá số chi tiết dự trữ của mỗi loại. Nghĩa là: X1 + X2 ≤ 450 X2 ≤ 250 2X1 + 2X2 + X3 ≤ 800 X1 + X2 ≤ 450 (số lượng dữ trữ của chi tiết CT4) 2X1 + X2 + X3 ≤ 600 Bước 2: Bố trí dữ liệu trong bảng tính Excel Dữ liệu được bố trí trong bảng tính Excel như trong hình 2.10. Nhập các ràng buộc của bài toán như trong hình 2.11. Chú ý chọn option và chọn mục Assume Linear Modle và mục Assume non Negative.Kết quả giải bài toán khi chọn Keep như trong hình 2.12. Như có thể thấy từ hình 2.12, phương án sản xuất tối ưu nằm trong ô $C$3:$E$3 và phương án đó là X = (200; 200; 0). Lợi nhuận ứng với phương án sản xuất tối ưu nằm trong ô $F$11 với giá trị là 250000 (USD). Vùng $F$4:$F$8 cho thấy ở phương án sản xuất đã chọn vẫn còn dư nguồn lực. Đó là còn lại 50 CT1, 50 CT2 và 50 CT4 chưa dùng đến. Phòng Thực hành kinh doanh – Bộ môn Tin học ứng dụng = trang 46=
  48. Trần Công Nghiệp - Giáo trình Tin học ứng dụng (bản thảo) H.2. 10 Bố trí dữ liệu trong bảng tính cho ví dụ 2 H.2. 11 Nhập ràng buộc cho ví dụ 2 H.2. 12 Hiển thị kết quả của ví dụ 2 2.2.5. Ví dụ 2.3: Ví dụ sau trình bày bài toán quy hoạch phi tuyến với các ràng buộc là các phương trình tuyến tính (nonlinear programming with linear in constraints). 2 2 2 2 2 Tìm min f(x) = -x1 + 2x1 – x2 + 4x2 – x3 + 8x3 – x4 + 14x4 – x5 + 18x5 -180 Thỏa mãn các ràng buộc sau: Phòng Thực hành kinh doanh – Bộ môn Tin học ứng dụng = trang 47=
  49. Trần Công Nghiệp - Giáo trình Tin học ứng dụng (bản thảo) -x1 -2x2 + x3 + 2x4 + 3x5 ≤ 85 -7x1 + 9x2 -5x3 + 33x4 -11x5 ≤ 500 2x1 –x2 + 2x3 –x4 + 2x5 ≤ 150 1.3 x1 + x2 + x3 + x4 + x5 ≤ 300 x1 + x2 + x3 + x4 + x5 ≤ 300 x1, x2 , x3, x4, x5 ≥ 0 Đối với bài toán quy hoạch phi tuyến cần bỏ chọn Assume Linear Model trong mục Option. Cách chuẩn bị bài toán trong Excel như trong hình 2.13. Ở đây chọn phương án xuất phát là X(50, 50, 50, 50, 50). H.2. 13 chuẩn bị bài toán cho ví dụ 3 Kết quả giải bài toán khi chọn keep solution result như trong hình 2.14. Phương án tối ưu là X(0, 190, 0, 0, 110) và giá trị hàm mục tiêu là -45640. H.2. 14 Kết quả giải bài toán trong ví dụ 3 2.3 Ý nghĩa các lựa chọn của Solver Khi chọn Options trong hộp thoại Solver Parameters, Solver cho phép chọn một số tùy chọn để tiến hành giải bài toán như thời gian giải bài toán, số lần lặp, độ chính xác của kết quả, thuật toán tìm tối ưu . Bảng 2.3 trình bày ý nghĩa của các lựa chọn này. Bảng 2. 3 Ý nghĩa các nút trong hộp thoại Solver option Phòng Thực hành kinh doanh – Bộ môn Tin học ứng dụng = trang 48=
  50. Trần Công Nghiệp - Giáo trình Tin học ứng dụng (bản thảo) Tùy chọn Ý nghĩa Thời gian giải bài toán. Ngầm định là 100 s. Giá trị tối đa là 32767 s Số lần lặp. Ngầm định là 100. Số lần lặp tối đa là 32767 Độ chính xác. Giá trị này luôn nằm trong khoảng [0,1] để điều chỉnh sai số cho các ràng buộc. Giá trị càng gần 0 càng đòi hỏi độ chính xác cao của các ràng buộc Giá trị này tính bằng (%) và có tác dụng đối với các bài toán có ràng buộc nguyên. Giá trị lựa chọn càng lớn thì bài toán càng giải nhanh Mức độ hội tụ của hàm mục tiêu. Giá trị này nằm trong khoảng [0, 1]. Lựa chọn này chỉ có ý nghĩa đối với bài toán quy hoạch phi tuyến. Sau 5 lần lặp cuối cùng, nếu thay đổi trong ô chứa hàm mục tiêu nhỏ hơn giá trị này thì Solver dừng quá trình tính toán.Giá trị này càng nhỏ thì thời gian tính toán càng dài. Giả thiết mô hình tuyến tính. Chọn mục này đối với bài toán quy hoạch tuyến tính. Giả thiết các biến không âm. Chọn mục này khi có ràng buộc về dấu của các biến. Chọn mục này khi giá trị đầu vào và kết quả có độ lớn khác nhau. Ví dụ tìm tối đa hóa lợi nhuận khi đầu tư tính bằng triệu dolla Chọn mục này khi muốn Solver hiển thị các kết quả trung gian của mỗi bước lặp. Chỉ thị cho Solver cách ước lượng giá trị theo một phương tìm kiếm. Tangent: Ngoại suy sử dụng xấp xỉ bậc nhất. Quadratic: Ngoại suy sử dụng xấp xỉ bậc hai. Lựa chọn này cho độ chính xác cao hơn đối với các bài toán quy hoạh phi tuyến Chỉ thị cho Solver cách tính đạo hàm riêng phần cho hàm mục tiêu và các hàm ràng buộc. Forward dùng khi giá trị của các ràng buộc thay đổi chậm Central dùng khi các ràng buộc biến đổi nhanh và khi Solver báo không thể cải tiến kết quả thu được. Chỉ thị cho Solver sử dụng thuật toán nào để tìm kiếm giá trị tối ưu. Thuật toán Newton sử dụng nhiều bộ nhớ nhưng ít số lần lặp Thuật toán Conjugate: sử dụng ít bộ nhớ nhưng số lần lặp nhiều hơn. Áp dụng với các bài toán lớn. Hiển thị hộp thoại Load modle để xác định vùng địa chỉ của mô hình bài toán cần nạp vào. Chọn nơi lưu mô hình bài toán. Sử dụng khi muốn lưu nhiều mô hình trên một worksheet. 2.4 Một số thông báo lỗi thường gặp của Solver Trong một số trường hợp Solver không thể giải được bài toán, khi đó có các thông báo sau: Solver could not find feasible solution: bài toán không có lời giải chấp nhận được hoặc các giá trị khởi đầu của các ô chứa biến số quá xa các giá trị tối ưu. Có thể thay đổi giá trị khởi đầu để giải lại bài toán. Phòng Thực hành kinh doanh – Bộ môn Tin học ứng dụng = trang 49=
  51. Trần Công Nghiệp - Giáo trình Tin học ứng dụng (bản thảo) The maximum iteration was reached, continue anyway? Số bước lặp đã đặt đến giá trị lựa chọn trong mục options mà chưa tìm được tối ưu. Có thể tăng số bước lặp trong Solver options để giải bài toán. The maximum time limit was reached, continue anyway? Thời gian chạy vượt quá giá trị lựa chọn trong mục options mà chưa tìm được tối ưu. Có thể tăng thời gian trong Solver options để giải bài toán. 2.5 Phân tích độ nhạy của bài toán Môi trường kinh doanh thường là bất định là rất khó lường vì có rất nhiều yếu tố thay đổi như luật lệ, sự biến động kinh tế, những thay đổi của các nhà cung cấp Các nhà quản lý thường phải đối mặt với những thay đổi như vậy khi ra quyết định. Các nhà quản lý thường sử dụng các mô hình toán học để ra quyết định. Các mô hình này không thể tránh khỏi có những yếu tố bất định hoặc thay đổi ở các giai đoạn khác nhau nhưng khi lập mô hình người ta giả thiết rằng các yếu tố này xác định và không biến đổi. Phân tích độ nhạy là một tập hợp các hoạt động sau khi giải bài toán nhằm nghiên cứu xem lời giải của bài toán thay đổi như thế nào khi các yếu tố của mô hình thay đổi. Phân tích này còn được gọi là phân tích độ ổn định của lời giải hay phân tích what-if, mô hình hóa tình huống, phân tích sự biến động, phân tích sự bất định Sự bất định trong các mô hình có thể có nguồn gốc khác nhau như thông tin không đầy đủ, sự biến động của yếu tố đầu vào hoặc sự thay đổi không dự đoán được trong tương lai. Một số tình huống điển hình bao gồm: Phân tích tình huống: Trường hợp này người ta giả định những sự kết hợp có thể của những tham số bất định và giải bài toán cho từng trường hợp. Bằng cách giải quyết các tính huống khác nhau và nghiên cứu lời giải thu được, người ta quan sát độ nhạy và ra quyết định. Phân tích tình huống xấu nhất: Kỹ thuật này phân tích các tình huống cận biên ngay từ giai đoạn lập kế hoạch. Tiếp cận Monte-Carlo: cách tiếp cận này giả thiết rằng sự bất định của các mô hình được xác định nhờ các hàm phân phối thống kê của chúng. Nhìn chung các cách tiếp cận trên đều nhằm tìm xem sự thay đổi theo các hướng của các tham số ảnh hưởng như thế nào đến giá trị tối ưu. Nói cách khác, khi nào thì giá trị tối ưu thay đổi khi thay đổi giá trị của các tham số. Solver cung cấp một công cụ để phân tích độ nhạy. Tùy chọn này nhận được khi chọn Sensityvity và Limits trong Report từ hình 2.7. Tùy chọn Sensitivity cho thấy lời giải thay đổi thế nào khi có sự thay đổi nhỏ ở ô đích và các ô chứa ràng buộc. Nếu có ràng buộc là số nguyên thì không sinh ra tùy chọn này. Đối với các bài toán quy hoạch phi tuyến, Sensitivity Report cho giá trị giảm gradient và nhân tử Lagrange. Đối với bài toán quy hoạch tuyến tính, tùy chọn này cho các thông tin về shadow price (là sự thay đổi giá trị tối ưu của hàm mục tiêu khi vế phải của ràng buộc tăng một đơn vị), các hệ số của hàm mục tiêu (thông tin về mức độ tăng giảm cho phép) và phạm vi biến động của vế phải các ràng buộc. Tùy chọn Limits liệt kê các giá trị của ô đích (ô chứa hàm mục tiêu) và các ô chứa biến quyết định (adjustable cells) Phòng Thực hành kinh doanh – Bộ môn Tin học ứng dụng = trang 50=
  52. Trần Công Nghiệp - Giáo trình Tin học ứng dụng (bản thảo) cùng với giá trị giới hạn trên và giá trị giới hạn dưới của chúng. Giá trị giới hạn trên (upper) là giá trị lớn nhất mà một biến quyết định có thể nhận trong khi giữa nguyên giá trị của các biến khác mà vẫn thỏa mãn các ràng buộc. Giá trị giới hạn dưới (lower) là giá trị nhỏ nhất mà một biến có thể nhận. Solver không phát sinh Report Limits khi có ràng buộc là số nguyên. Hình 2.15 trình bày Sensitivity Report và hình 2.16 trình bày Limits Report cho ví dụ 2. Như có thể thấy từ hình 2.15. Hệ số thứ nhất của hàm mục tiêu (ứng với x1) là 8 và có thể tăng thêm 4 hoặc giảm 5 trong khi giữ nguyên các giá trị khác mà không làm thay đổi các ràng buộc (kể cả giá trị hàm mục tiêu và các biến x1, x2). Ràng buộc 1 có giá trị ở vế phải (RHS) là 60 và có thể tăng 36 hay giảm 36 không làm thay đổi các ràng buộc khác đồng thời vẫn thỏa mãn điều kiện tối ưu của bài toán. Độc giả có thể thử lại với giá trị này để thấy độ nhạy của bài toán. Giá trị Reduced Cost cho thấy các hệ số của hàm mục tiêu phải tăng thêm trước khi các biến quyết định đạt được giá trị không âm trong lời giải tối ưu. H.2. 15 Phân tích độ nhạy của bài toán trong ví dụ 2 H.2. 16 Giới hạn các ô chứa biến quyết định trong ví dụ 2 Phòng Thực hành kinh doanh – Bộ môn Tin học ứng dụng = trang 51=
  53. Trần Công Nghiệp - Giáo trình Tin học ứng dụng (bản thảo) 3. GIẢI HỆ PHƯƠNG TRÌNH TUYẾN TÍNH George Dantzig đã chứng minh rằng, bài toán quy hoạch tuyến tính về lý thuyết có thể coi là bài toán giải hệ bất phương trình tuyến tính. Vì vậy, có thể sử dụng Solver để giải các hệ phương trình tuyến tính một cách tiện lợi. Giải các hệ phương trình tuyến tính được ứng dụng trong kinh tế để tìm điểm hòa vốn, tìm sản lượng hoặc giá cả làm cân bằng cung cầu Khi sử dụng Solver để giải hệ phương trình thì không cần hàm mục tiêu. Cũng có thể coi một trong số các phương trình của hệ là hàm mục tiêu với target cell được nhập vào giá trị value of. Các phương trình còn lại của hệ được coi là các ràng buộc. Khi sử dụng các gói phần mềm quy hoạch tuyến tính khác để giải hệ phương trình thì có thể tạo ra một hàm mục tiêu giả. Ngoài ra có thể viết hệ phương trình tuyến tính thành dạng ma trân AX = B khi đó X= A-1B. Sử dụng hàm MINVERSE(array) để tính ma trận A-1 và hàm MMULT(array1, arrray2) để thực hiện phép nhân ma trận. Cả hai hàm này sử dụng công thức mảng trả về kết quả là một dãy ô như trong chương 1 đã nêu. Ví dụ 2.4: Giải hệ phương trình tuyến tính sau 2x1 + 3x2 + x3 = 4 x1 + x2 – x3 = 3 2x1 – 2x2 – x3 = 1. Chuẩn bị bài toán trong Excel như hình 2.17. Nhập ràng buộc như trong 2.18. Kết quả giải bài toán như trong hình 2.19. H.2. 17 Giải hệ phương trình tuyến tính bằng Solver H.2. 18 Điền các tham số cho Solver để giải hệ phương trình Phòng Thực hành kinh doanh – Bộ môn Tin học ứng dụng = trang 52=
  54. Trần Công Nghiệp - Giáo trình Tin học ứng dụng (bản thảo) H.2. 19 Kết quả giải hệ phương trình dùng Solver Như có thể thấy từ hình 2.19. Kết quả giải hệ phương trình là x1 = 1, x2 = 1 và x3 = -1. Chú ý bỏ chọn Assume non-negative trong Solver Options khi giải hệ phương trình bằng Solver. Độc giả tự thực hiện bài giải hệ phương trình bằng MINVERSE và MMULT như là một bài tập thực hành. BÀI TẬP CHƯƠNG 2 2 2 Bài 2.1 Tìm min f(x) = -x1 – 2x2 + 0.5x1 + 0.5 x2 . Thỏa mãn các ràng buộc 2x1 + 3x2 + x3 = 6 x1 + 4x2 + x4 = 5 x1, x2, x3 , x4 ≥ 0 Ghi bài vào thư mục vừa tạo ở chương 1 theo dạng C:\tenthumuc\tenfile.xls. Trong đó tenfile bao gồm “họ tên sinh viên ,chương 2, bài số 1”. Bài 2.2. Có 4 phân xưởng sản xuất ký hiệu PX1, PX2, PX3, PX4, cần sản xuất 4 loại sản phẩm ký hiệu SP1, SP2, SP3, SP4. Chi phải sản xuất sản phẩm i ở xưởng j như bảng sau (đvt 1000 đ) : SP1 SP2 SP3 SP4 PX1 100 4000 800 550 PX2 200 3500 750 500 PX3 400 2000 700 400 PX4 300 5000 600 450 Hãy phân công cho mỗi đơn vị sản xuất một loại sản phẩm sao cho tổng chi phí là nhỏ nhất. Ghi bài vào thư mục vừa tạo ở chương 1 theo dạng C:\tenthumuc\tenfile.xls. Trong đó tenfile bao gồm “họ tên sinh viên ,chương 2, bài số 2”. Bài 2.3. Ba sản phẩm A, B, C được lắp ráp từ các module tiêu chuẩn hóa ký hiệu CT1, CT2, CT3, CT4, CT5. Số lượng module dùng cho mỗi loại sản phẩm và lợi nhuận thu được nếu bán mỗi sản phẩm, số lượng các module dự trữ cho trong bảng sau Phòng Thực hành kinh doanh – Bộ môn Tin học ứng dụng = trang 53=
  55. Trần Công Nghiệp - Giáo trình Tin học ứng dụng (bản thảo) Tên module A B C dự trữ (bộ) CT1 1 1 0 450 CT2 1 0 0 250 CT3 2 2 1 800 CT4 1 1 0 450 CT5 2 1 1 600 Lợi nhuận đơn vị ($) 75 50 35 Hãy lập kế hoạch sản xuất để đạt lợi nhuận tối đa. Khi đó còn lại những module nào chưa dùng hết. Lợi nhuận thu được là bao nhiêu. Có thể tìm được phương án sản xuất tối ưu mà sử dụng hết số module không? Ghi bài vào thư mục vừa tạo ở chương 1 theo dạng C:\tenthumuc\tenfile.xls. Trong đó tenfile bao gồm “họ tên sinh viên ,chương 2, bài số 3”. Bài 2.4. Một xí nghiệp sản xuất hai loại sản phẩm ký hiệu SP1 và SP2 để bán trên thị trường. năng lực sản xuất cho phép sản xuất tối đa 500 tấn SP1 và tối đa 500 tấn SP2 mỗi ngày. Cả hai sản phẩm đều sản xuất từ cùng một loại nguyên vật liệu. Mỗi tấn sản phẩm SP1 cần 5 thùng NVL và mỗi tấn sản phẩm SP2 cần 6 thùng NVL. Mỗi ngày xí nghiệp chỉ có thể có tối đa 3600 thùng NVL. Để sản xuất 1 tấn SP1 cần 1 công nhân và 1 tấn SP2 cần 2 công nhân. Mỗi ngày xí nghiệp có 960 công nhân làm việc. Mõi tấn SP1 có lợi nhuận là 7 (USD) và mỗi tấn SP2 có lợi nhuận là 10 (USD). Hỏi mỗi ngày xí nghiệp nên sản xuất mỗi loại sản phẩm với số lượng bao nhiêu để tối đa hóa lợi nhuận. Lợi nhuận tối ưu trong một ngày là bao nhiêu? Nếu lợi nhuận của sản phẩm SP2 tăng lên thành 11 (USD) thì kết quả bài toán sẽ thay đổi thế nào về sản lượng sản xuất và sản lượng tối ưu? Nếu thay đổi số lượng NVL dự trữ (tăng hay giảm 1 thùng) thì lợi nhuận thay đổi thế nào? Giá trị tối ưu thay đổi thế nào nếu năng lực tối đa về sản xuất SP2 thay đổi thành 1000. Ghi bài vào thư mục vừa tạo ở chương 1 theo dạng C:\tenthumuc\tenfile.xls. Trong đó tenfile bao gồm “họ tên sinh viên ,chương 2, bài số 4”. Bài 2.5 Một công ty sở hữu 4 mỏ đá có thể khai thác đá với chất lượng khác nhau trong đó hai chỉ tiêu chất lượng chủ yếu là lượng Ca và Mg (tính bằng số biểu kiến). Công ty cần sản xuất 6000 tấn đá/năm. Yêu cầu mỗi tấn đá có hàm lượng Ca là 0.9 và Mg là 2.3. Biết hàm lượng Ca và Mg có trong mỗi tấn đá của từng mỏ, chi phí để khai thác mỏ và trữ lượng của mỗi mỏ như bảng sau: Hỏi công ty cần phải khai thác mỗi mỏ bao nhiêu tấn đá để thỏa mãn yêu cầu sản xuất. Ghi bài vào thư mục vừa tạo ở chương 1 theo dạng C:\tenthumuc\tenfile.xls. Trong đó tenfile bao gồm “họ tên sinh viên ,chương 2, bài số 5”. Phòng Thực hành kinh doanh – Bộ môn Tin học ứng dụng = trang 54=
  56. Trần Công Nghiệp - Giáo trình Tin học ứng dụng (bản thảo) Khả năng khai Chi phí để duy thác tối đa trì hoạt động của Mỏ Hàm lượng Ca Hàm lượng Mg (tấn/năm) mỏ (triệu USD/năm) M1 1.0 2.3 2000 3.5 M2 0.7 1.6 2500 4.0 M3 1.5 1.2 1300 4.0 M4 0.7 2.1 3000 2.0 Bài 2.6 Một công ty có 2 xí nghiệp sản xuất, 4 kho và cần phục vụ cho 5 khách hàng. Công ty muốn giảm tổng chi phí vận chuyển gồm vận chuyển hàng hóa từ xí nghiệp đến kho, từ xí nghiệp đến khách hàng và từ kho đến khách hàng. Ràng buộc rằng lượng sản phẩm từ xí nghiệp đến kho phải bằng lượng sản phẩm từ kho đến khách hàng. Mỗi xí nghiệp có khả năng sản xuất 60000 (tấn/tháng). Chi phí vận chuyển từ xí nghiệp đến các kho và khả năng chứa của các kho như bảng sau: Kho 1 Kho 2 Kho 3 Kho 4 Xí nghiệp 1 ($/tấn) 0.5 0.5 1.0 0.2 Xí nghiệp 2 ($/tấn) 1.5 0.3 0.5 0.2 Khả năng chứa (tấn) 45000 20000 30000 15000 Chi phí vận chuyển từ xí nghiệp đến từng khách hàng và từ kho đến từng khách hàng, nhu cầu của từng khách hàng như bảng sau Khách hàng Khách hàng Khách hàng Khách hàng Khách hàng 1 2 3 4 5 XN 1 ($/tấn) 1.75 2.50 1.50 2.00 1.50 XN 2 ($/tấn) 2.00 2.50 2.50 1.50 1.00 Kho 1 ($/tấn) 1.50 1.50 0.50 1.50 3.00 Kho 2 ($/tấn) 1.00 0.50 0.50 1.00 0.50 Kho 3 ($/tấn) 1.00 1.50 2.00 2.00 0.50 Kho 4 ($/tấn) 2.50 1.50 0.20 1.50 0.50 Nhu cầu (tấn) 30000 23000 15000 32000 16000 Hãy lập kế hoạch vận chuyển để tổng chi phí vận chuyển là nhỏ nhất? Kết quả tối ưu thay đổi thế nào nếu tăng chi phí vận chuyển từ kho 1 đến khách hàng 2 thêm 0.1 ($/tấn)? Kết quả tối ưu thay đổi thế nào nếu giảm chi phí vận chuyển từ xí nghiệp 2 đến khách hàng 4 một lượng là 1.2 ($/tấn)? Tổng chi phí vận tải tăng (giảm) bao nhiêu nếu khả năng dữ trữ của kho 2 tăng thêm được 10000 tấn? Ghi bài vào thư mục vừa tạo ở chương 1 theo dạng C:\tenthumuc\tenfile.xls. Trong đó tenfile Phòng Thực hành kinh doanh – Bộ môn Tin học ứng dụng = trang 55=
  57. Trần Công Nghiệp - Giáo trình Tin học ứng dụng (bản thảo) bao gồm “họ tên sinh viên ,chương 2, bài số 6”. Bài 2.7. Một công ty công viên cần phải duy trì hoạt động của công viên với yêu cầu số công lao động từ thứ hai đến chủ nhật lần lượt là 17; 13; 14; 15; 18; 24; 22 công. Người lao động làm việc 5 ngày và nghỉ hai ngày bất kì trong tuần. Công viên này quyết định chia số lao động thuê được thành 7 kíp làm việc ký hiệu A, , G. trong đó kíp A gồm những người muốn nghỉ chủ nhật và thứ hai, kíp B gồm những người muốn nghỉ thứ hai và thứ ba, kíp C gồm những người muốn nghỉ thứ 3 và thứ 4, kíp D gồm những người muốn nghỉ thứ 4 và thứ 5, kíp E gồm những người muốn nghỉ thứ 5 và thứ 6, kíp F gồm những người muốn nghỉ thứ 6 và thứ 7, kíp G gồm những người muốn nghỉ thứ bảy và chủ nhật. Cho biết lương công nhân là $40/người/ngày. Hỏi cần thuê tổng số bao nhiêu công nhân và phân công mỗi kíp bao nhiêu người để chi phí là nhỏ nhất mà thỏa mãn yêu cầu về số lao động của mỗi ngày đồng thời thỏa mãn nguyện vọng của công nhân? Ghi bài vào thư mục vừa tạo ở chương 1 theo dạng C:\tenthumuc\tenfile.xls. Trong đó tenfile bao gồm “họ tên sinh viên ,chương 2, bài số 7”. Bài 2.8. Sử dụng kết quả của bài 2.7 với điều kiện mở rộng như sau: Mỗi công nhân được đưa ra một bản kiến nghị trong đó có ghi sở thích làm việc của mình trong từng kíp. Số 7 ứng với mức ưu tiên cao nhất và 1 ứng với mức ưu tiên thấp nhất. Kíp A B C D E F G Kíp A B C D E F G Công nhân 1 4 6 5 3 2 7 1 Công nhân 14 4 6 7 3 2 5 1 Công nhân 2 3 4 5 2 1 7 6 Công nhân 15 4 5 7 6 3 2 1 Công nhân 3 6 5 2 7 4 3 1 Công nhân 16 4 3 2 5 6 7 1 Công nhân 4 4 5 3 6 2 7 1 Công nhân 17 6 5 7 3 2 1 4 Công nhân 5 5 4 2 6 7 3 1 Công nhân 18 7 6 5 4 3 2 1 Công nhân 6 1 2 3 4 5 6 7 Công nhân 19 6 5 3 4 2 7 1 Công nhân 7 7 5 3 1 6 4 2 Công nhân 20 4 5 3 6 7 2 1 Công nhân 8 4 3 2 5 6 1 7 Công nhân 21 7 6 5 3 4 1 2 Công nhân 9 5 4 3 2 7 6 1 Công nhân 22 7 6 2 5 3 4 1 Công nhân 10 1 3 2 5 6 7 4 Công nhân 23 6 5 7 3 4 2 1 Công nhân 11 6 7 2 1 3 4 5 Công nhân 24 5 6 4 3 1 2 7 Công nhân 12 5 4 6 7 3 2 1 Công nhân 25 6 4 7 5 3 2 1 Công nhân 13 1 2 3 4 5 6 7 Hãy phân mỗi công nhân vào từng kíp cho thích hợp sao cho thỏa mãn mức độ sở thích tổng cộng của toàn công ty là cực đại. Ghi bài sheet riêng cùng file với bài 2.7 Phòng Thực hành kinh doanh – Bộ môn Tin học ứng dụng = trang 56=
  58. Trần Công Nghiệp - Giáo trình Tin học ứng dụng (bản thảo) CHƯƠNG 3: QUẢN LÝ TÀI CHÍNH 1.KHẤU HAO TÀI SẢN CỐ ĐỊNH 1.1.Khái niệm về tài sản cố định và khấu hao tài sản cố định Dưới góc độ quản trị kinh doanh, tài sản cố định (TSCĐ) là những tư liệu lao động thỏa mãn đồng thời hai điều kiện: Có thời hạn sử dụng lớn hơn một năm và có giá trị lớn hơn một khoản tiền được quy định trước. Theo quy định hiện hành thì TSCĐ cần phải có giá trị lớn hơn 10 triệu đồng. Có nhiều cách phân chia TSCĐ tùy theo tiêu chí phân chia như phân chia theo hình thái biểu hiện thì có TSCĐ hữu hình và TSCĐ vô hình. Phân chia theo quyền sở hữu thì có TSCĐ của doanh nghiệp và TSCĐ thuê ngoài. Trong quá trình tham gia vào hoạt động sản xuất kinh doanh, TSCĐ bị giảm dần giá trị và giá trị sử dụng. Hiện tượng này gọi là hao mòn tài sản cố định. Hao mòn hữu hình TSCĐ là hiện tượng giảm dần tính năng kỹ thuật của TSCĐ do các nguyên nhân như lực cơ học, hoặc do ảnh hưởng của môi trường như ăn mòn điện hóa, mối mọt mục Hao mòn vô hình TSCĐ là hiện tượng TSCĐ bị giảm dần giá trị do tiến bộ của khoa học kỹ thuật và quản lý thể hiện ở cùng một khoản tiền có thể mua hay sản xuất được một TSCĐ có tính năng kỹ thuật tốt hơn. Do vậy TSCĐ cũ tự nhiên bị mất giá. Khấu hao TSCĐ là biện pháp nhằm chuyển một phần giá trị của TSCĐ vào giá thành sản phẩm do TSCĐ đó sản xuất ra để sau một thời gian nhất định có đủ tiển mua được một TSCĐ khác tương đương với TSCĐ cũ. Về bản chất, khấu hao TSCĐ chính là tái sản xuất giản đơn TSCĐ. Khấu hao TSCĐ là một yếu tố chi phí trong giá thành sản phẩm của doanh nghiệp nên việc lập kế hoạch khấu hao TSCĐ nằm trong nội dung của công tác lập kế hoạch tài chính của doanh nghiệp và có ý nghĩa vô cùng quan trọng đối với doanh nghiệp. Để tính toán khấu hao TSCĐ cần phải định nghĩa một số khái niệm sau: Nguyên giá của TSCĐ (ký hiệu Kbđ) là giá trị thực tế của TSCĐ khi đưa vào sử dụng. Đối với máy móc thiết bị, nguyên giá bao gồm giá mua (hay sản xuất) cộng với chi phí vận chuyển và lắp đặt. Giá trị còn lại của TSCĐ (ký hiệu Kcl) là giá trị thực tế của TSCĐ tại một thời điểm xác định. Giá trị còn lại được xác định căn cứ vào giá thị trường khi đánh giá TSCĐ. Về phương diện kế toán, giá trị còn lại của TSCĐ được xác định bằng hiệu số giữa nguyên giá và giá trị hao mòn (hay lượng trích khấu hao lũy kế tính đến thời điểm xác định). 1.2.Các phương pháp tính khấu hao tài sản cố định Có hai cách tính khấu hao TSCĐ là khấu hao theo thời gian và khấu hao theo sản phẩm. Đổi với TSCĐ là các máy móc vạn năng thường khấu hao theo thời gian. Đối với TSCĐ là máy móc chuyên dùng thường khấu hao theo sản phẩm. Khi tính khấu hao TSCĐ theo thời gian, có thể tính theo phương pháp khấu hao đều (tuyến Phòng Thực hành kinh doanh – Bộ môn Tin học ứng dụng = trang 57=