Bài giảng Tin học ứng dụng trong kinh doanh - Hoàng Nguyên Khai

n hàng vào Column, Drag trường Quầy vào Row , Drag trường Số lượng vào Data. Ta được kết quả như sau Tạo báo cáo tổng hợp trên nhiều bảng Đánh dấu khối phần dữ liệu cần tổng hợp và chọn [Menu]Data – PivotTable and PivotChart Report. - Chọn Multiple consolidation ranges và PivotTable 45 Chọn Create a single page feild for me Khai báo các vùng dữ liệu gốc để tham gia tổng hợp trên từng bảng trong phần Range, mỗi vùng khai báo xong ta chọn Add để thêm vào danh sách All ranges Khai báo nơi đặt Pivot và chọn Finish để hoàn tất Step 3: Hoặc xác định vị trí đặt của Pivot Table, rồi click vào Layout Vd: Tạo Pivot từ các bảng số liệu sau 46 Ta được kết quả Các thao tác khác Khi ta thay đổi dữ liệu gốc thì dữ liệu trong Pivot không tự động thay đổi. Để dữ liệu trong Pivot được cập nhật đúng với dữ liệu gốc ta phải chuyển con trỏ vào vùng Pivot và chọn [Menu]Data – Refresh Data hoặc Thay đổi cấu trúc Pivot :Drag các vùng trong Pivot tới vị trí mình cần Thay đổi thuộc tính từng vùng hoặc xóa vùng Click vào vùng mình cần rồi chọn Xóa Pivot Đánh dấu toàn bộ vùng chứa Pivot và bấm Del 3- Bài toán phân tích độ nhạy WHAT - IF Phân tích độ nhạy (Sentitive Analysis) Phân tích độ nhạy là dạng phân tích nhằm trả lời câu hỏi “điều gì sẽ xảy ra nếu như ” (what – if). Phân tích này xem xét yếu tố đầu vào nào là quan trọng nhất (yếu tố mang tính chất rủi ro) ảnh hưởng đến kết quả bài toán (lợi nhuận hoặc chi phí). 47 Phân tích độ nhạy (Sentitive Analysis) Trong Excel hỗ trợ phân tích độ nhạy 1 chiều và hai chiều, nghĩa là chỉ đánh giá được tối đa 2 yếu tố rủi ro. Lưu ý phân tích độ nhạy không xét đến mối quan hệ tương quan giữa các biến. Trong trường hợp phân tích bài toán với một biến đầu vào thay đổi ta gọi là phân tích độ nhạy một chiều. Trong trường hợp phân tích bài toán với hai biến đầu vào thay đổi ta gọi là phân tích độ nhạy hai chiều. MỘT BIẾN VÀ NHIỀU CÔNG THỨC Tạo bảng dữ liệu với 1 giá trị nhập thay đổi sẽ ảnh hưởng trên nhiều công thức a) Xây dựng bảng dữ liệu (Data- Table) Row input hoặc Column input: là dòng hoặc cột đầu tiên chứa các giá trị thay đổi của biến. Dòng hoặc cột thứ hai chứa các kết quả của một công thức, tương ứng với từng giá trị thay đổi của biến. Cell đầu tiên của dòng hoặc cột này chứa công thức có biến thay đổi giá trị, các cell còn lại được để trống. Khi ta ra lệnh tạo bảng, Excel sẽ điền kết quả vào các cell trống này. MỘT BIẾN VÀ NHIỀU CÔNG THỨC Có thể thêm nhiều dòng hoặc cột kế tiếp chứa các kết quả khác, mỗi dòng hoặc cột có 1 công thức liên hệ đến biến tại cell đầu. b) Chọn Data – table vừa tạo Chỉ đánh dấu phần bảng chứa các giá trị của biến và các dòng để chứa kết quả tương ứng MỘT BIẾN VÀ NHIỀU CÔNG THỨC c) Chọn lệnh [Menu]Data – Table d) Nhập địa chỉ cell dùng làm biến vào Row Input Cell (nếu tạo bảng theo dòng) Column Input Cell (nếu tạo bảng theo cột) e) Chọn OK 48 Một công ty bán máy tính theo hình thức trả góp trong 18 tháng. Khách hàng phải trả một số tiền ban đầu mới nhận máy (Trả kỳ đầu). Phần tiền còn lại sẽ góp trong 18 tháng với số tiền góp đều trong 17 tháng, tháng cuối (thứ 18) trả nốt phần tiền còn lại (trả kỳ cuối). Lập bảng tính tính số tiền trả góp hàng tháng và số tiền trả kỳ cuối với số tiền trả kỳ đầu thay đổi. VÍ DỤ - MỘT BIẾN VÀ NHIỀU CÔNG THỨC • B6: Nhập 5000000 (ô này là biến) • Nhập các giá trị từ C8:H8, đây là các giá trị thay đổi cho biến • B9: Nhập = INT((B5 – B6)/18). Tính tiền phải trả cho mỗi tháng • B10: Nhập = B5 – B6 – B9*17. Tính tiền còn lại Chọn OK thì được kết quả sau • Đánh dấu Data-table từ B8:H10 • Chọn [Menu]Data - Table • Nhập B6 vào Row Input Cell • Chọn OK HAI BIẾN VÀ MỘT CÔNG THỨC Tạo bảng dữ liệu với 2 giá trị nhập thay đổi sẽ ảnh hưởng trên 1 công thức a) Xây dựng bảng dữ liệu (Data- Table) Row input: là dòng đầu tiên chứa các giá trị thay đổi của biến thứ nhất. Column input: là cột đầu tiên chứa các giá trị thay đổi của biến thứ hai. Cell đầu tiên của dòng và cột đầu chứa công thức có 2 biến thay đổi giá trị, các cell còn lại được để trống. Khi ta ra lệnh tạo bảng, Excel sẽ điền kết quả vào các cell trống này. 49 HAI BIẾN VÀ MỘT CÔNG THỨC b) Chọn Data – table vừa tạo Chỉ đánh dấu phần bảng chứa các giá trị của biến và các dòng để chứa kết quả tương ứng c) Chọn lệnh [Menu]Data – Table d) Nhập địa chỉ Cell dùng làm biến thứ nhất vào Row Input Cell Cell dùng lam biến thứ hai vào Column Input Cell e) Chọn OK Một công ty bán máy tính theo hình thức trả góp trong 18 tháng. Cách thức trả là khách hàng phải trả một số tiền mới nhận máy (Trả kỳ đầu), phần tiền còn lại sẽ góp trong 18 tháng với số tiền góp đều trong 17 tháng, tháng cuối (thứ 18) trả nốt phần tiền còn lại (Trả kỳ cuối). Lập bảng tính số tiền trả kỳ cuối với số tiền trả kỳ đầu và giá bán máy thay đổi. VÍ DỤ - HAI BIẾN VÀ MỘT CÔNG THỨC B5: Nhập 14167000 (ô này là biến thứ nhất) B6: Nhập 5000000 (ô này là biến thứ hai) Nhập các giá trị từ B10:B11, đây là các giá trị thay đổi cho biến thứ nhất Nhập các giá trị từ C9:F9, đây là các giá trị thay đổi cho biến thứ hai B9: Nhập = B5 – B6 – B7*17 Đánh dấu Data-table từ B9:F11 Chọn [Menu]Data - Table Nhập B6 vào Row Input Cell, nhập B5 vào Column Input Cell HAI BIẾN VÀ MỘT CÔNG THỨC 50 Chọn OK thì được kết quả sau: HAI BIẾN VÀ MỘT CÔNG THỨC Bài toán HỒI QUI ĐƠN Cơ bản về hồi quy tuyến tính đơn Dạng đơn giản nhất của một mô hình hồi qui chứa một biến phụ thuộc (còn gọi là "biến đầu ra," "biến nội sinh," hay "biến-Y") và một biến độc lập đơn (còn gọi là "hệ số," "biến ngoại sinh," hay "biến-X"). Phương trình hồi quy tuyến tính đơn có thể biểu diễn theo dạng: Y = b0 + b1X (hay Y=aX+b) Bài toán HỒI QUI ĐƠN Cơ bản về hồi quy tuyến tính đơn Trong thực tế không chỉ có biến X ảnh hưởng đến Y mà còn có các yếu tố ngẫu nhiên khác ảnh hưởng đến Y nên phương trình trên được viết thành: Y= b0 + b1x + e Trong đó e là các sai số. Trong thống kê và trong kinh tế lượng, người ta sử dụng phương pháp bình phương cực tiểu để ước lượng các hệ số b1 và b0. Bài toán HỒI QUI ĐƠN Cơ bản về hồi quy tuyến tính đơn Trong thực tế không chỉ có biến X ảnh hưởng đến Y mà còn có các yếu tố ngẫu nhiên khác ảnh hưởng đến Y nên phương trình trên được viết thành: Y= b0 + b1x + e Trong đó e là các sai số. Trong thống kê và trong kinh tế lượng, người ta sử dụng phương pháp bình phương cực tiểu để ước lượng các hệ số b1 và b0. 51 Bài toán HỒI QUI ĐƠN Hàm SLOPE và INTERCEPT  Ý nghĩa: Hàm SLOPE để tính hệ số góc a và hàm INTERCEPT để tính hệ số tự do b của hàm hồi quy tuyến tính đơn y=ax+b. Thay các hệ số a, b này vào hàm số với giá trị đã biết của x hoặc y ta sẽ tìm ra giá trị còn lại cần dự báo. Cú pháp:  = SLOPE(known_y’s, known_x’s)  = INTERCEPT(known_y’s, known_x’s) Bài toán HỒI QUI ĐƠN Hàm SLOPE và INTERCEPT Trong đó:  known_y’s là các giá trị hoặc vùng địa chỉ của tập số liệu phụ thuộc quan sát được  known_x’s là các giá trị hoặc vùng địa chỉ của tập số liệu độc lập quan sát được. Ví dụ Thống kê giá trị sản xuất và tiêu thụ điện năng trong 12 tháng người ta thu được các số liệu sau Biết giá trị sản xuất (y) có quan hệ với điện năng tiêu thụ (x) theo dạng y = b0 + b1x. Hãy ước lượng các tham số b0 và b1. Trình bày cách nhập số liệu vào Excel và sử dụng hàm slope, intercept để ước lượng các tham số của hàm hồi quy đơn. 52 Bài toán HỒI QUI ĐƠN Hàm Trend Ý nghĩa: Hàm Trend dùng để trả về giá trị dọc theo đường hồi quy (theo phương pháp bình phương nhỏ nhất) Cú pháp: =TREND(known_y’s, known_x’s, new_x’s, const) Bài toán HỒI QUI ĐƠN Hàm Trend Trong đó:  known_y’s, known_x’s, new_x’s là các giá trị hoặc vùng địa chỉ chứa giá trị đã biết của x, y tương ứng và giá trị mới của x.  const là hằng số. Ngầm định nếu const = 1 (True) thì hồi quy theo hàm y = ax + b, nếu const = 0 (False) thì hồi quy theo hàm y = ax. Bài toán HỒI QUI ĐƠN Hàm FORECAST  Ý nghĩa: Hàm Forecast tính, ước lượng giá trị tương lai căn cứ vào giá trị hiện tại.  Cú pháp: =FORECAST(x, known_y’s, known_x’s) Bài toán HỒI QUI ĐƠN Hàm FORECAST Trong đó:  x là giá trị dùng để dự báo.  known_y’s là các giá trị hoặc vùng địa chỉ của tập số liệu phụ thuộc quan sát được  known_x’s là các giá trị hoặc vùng địa chỉ của tập số liệu độc lập quan sát được. 53 Ví dụ Lợi nhuận của doanh nghiệp phụ thuộc vào giá thành sản phẩm. Hãy dự báo lợi nhuận mà doanh nghiệp sẽ đạt được khi giá thành sản phẩm là 270.000 đồng. Ví dụ Dùng hàm TREND dự báo lợi nhuận mà doanh nghiệp sẽ đạt được khi giá thành sản phẩm là 270.000 đồng. Ta có kết quả và công thức như sau: Ví dụ Dùng hàm FORECAST dự báo lợi nhuận mà doanh nghiệp sẽ đạt được khi giá thành sản phẩm là 270.000 đồng. Ta có kết quả và công thức như sau: Nhận xét: dù sử dụng hàm TREND hay hàm FORECAST đều cho ta các kết quả giống nhau. Sử dụng trình cài thêm Regression Ngoài việc sử dụng các hàm để dự báo cho mô hình hồi quy tuyến tính như đã trình bày ở phần trên, ta có thể sử dụng trình cài thêm Regression trong bộ phân tích dữ liệu Data Analysis. Quy trình lập bảng hồi quy tuyến tính trong Excel - Nhập số liệu vào bảng tính đồng thời theo từng cột . - Chọn Tools\ Data Analysis\ Regression, OK . Các bảng hộp thoại lần lượt được xuất hiện như sau: 54 Sử dụng trình cài thêm Regression Các bảng hộp thoại lần lượt được xuất hiện như sau: Sử dụng trình cài thêm Regression Hộp thoại khai báo các thông số của mô hình hồi qui: ĐC vùng biến phụ thuộc ĐC vùng biến độc lập Lựa chọn nơi xuất kết quả Kết quả sau khi chạy hồi qui, Ta có  Hệ số b (intercept)= 353.6699277  Hệ số góc a=-0.000240217 4- Bài toán tính vòng 55 4- BÀI TOÁN TÍNH VÒNG Trong thực tế chúng ta gặp nhiều vấn đề vòng vo cần giải quyết. Ví dụ như: Xây một căn nhà trị giá 100000000 đồng, khi bán nhà ta phải đóng thuế 5% trên giá bán, vậy giá trị căn nhà là Giá bán = 100000000 + thuế. Vấn đề đặt ra là chưa có thuế thì không tính được giá bán, nhưng thuế lại chỉ tính được khi có giá bán. Vậy tính làm sao? Giải quyết bài toán Vấn đề đặt ra là phải tính cái nào trước để giải quyết ổn thỏa. Như ví dụ trên thì ta có thể giải đơn giản như sau: Gọi X là giá bán, ta có X = 100000000 + 5%X, giải phương trình này là ta có giá bán. Giải quyết bài toán bằng Excel Máy tính giải quyết vấn đề theo cách Thử và Sai. Cho X một giá trị ngẫu nhiên, thử vào phương trình, nếu sai thì thay đổi giá trị X theo phương pháp hội tụ Thử tiếp. Quá trình này dừng sau khi đạt đủ số lần thử do ta quy định hoặc đã đạt được giá trị nằm trong khoảng sai số cho phép Cách mở tính vòng Chọn [Menu]Tools – Options – Calculation Đánh dấu phần Iteration Iteration 56 Khai báo tính vòng - Khai báo số lần thử tối đa trong phần Maximum iterations (sau khi thử đủ số lần này dù không tìm ra kết quả cũng sẽ dừng) - Khai báo sai số trong phần Maximum change (đây là độ chênh lệch của gíá trị tìm được và giá trị đúng, nếu nhỏ hơn sai số này thì dừng dù chưa đủ số lần thử) Calculation - Khai báo các chọn lựa khác trong hộp thoại: Automatic: Tự động tính Automatic except table: Tính toán tất cả các công thức phụ thuộc vào bảng tính khi ta truy cập tới nó Manual: Do người dùng quyết định tính, chỉ khi nào người dùng chọn Calc Now (F9) thì mới tính cho tất cả các bảng tính đang mở hay Calc Sheet thì mới tính cho bảng tính hiện hành Recalculate before save: tính toán lại trước khi lưu bảng Workbook options Update remote references: Tính toán và cập nhật các công thức có truy cập tới phần mềm khác Precision as displayed: Chuyển đổi số từ dạng đầy đủ sang dạng ngắn gọn 1904 date system: chuyển từ ngày bắt đầu của hệ thống là 1/1/1900 sang ngày 2/1/1904 Save external link values: Lưu trữ cả giá trị gốc của dữ liệu liên kết trong các bảng tính liên kết, các giá trị này sẽ được dùng trong bảng tính liên kết khi ta mất bảng tính gốc. Accept labels in formula: Cho phép dùng tên khối trong công thức 5- Tạo Scenario 57 5. TẠO SCENARIO Mục đích là tạo các bộ số liệu khác nhau trong cùng một vùng trên bảng tính. Điều nay giúp cho các công thức có thể tính trên nhiều số liệu khác nhau tại cùng một vùng. Vd: Ta có thể đặt ra 3 bộ số liệu ứng với 3 tháng trong bài toán theo mẫu sau 5. TẠO SCENARIO Chọn [Menu]Tools – Scenario Chọn Add để thêm một bộ số liệu mới Đặt tên cho bộ số liệu trong phần Scenario name, khai báo các cell cần thay đổi giá trị trong phần Changing cells (chỉ khai báo các cell chứa giá trị, không khai báo cell chứa công thức) 5. TẠO SCENARIO 58 Nhập các số liệu của bộ số liệu vào 5. TẠO SCENARIO Thể hiện bộ số liệu Chọn [Menu]Tools – Scenario Chọn tên bộ số liệu cần thể hiện trong danh sách và chọn Show 5. TẠO SCENARIO Sửa bộ số liệu Chọn [Menu]Tools – Scenario Chọn tên bộ số liệu cần sửa trong danh sách và chọn Edit Xóa bộ số liệu Chọn [Menu]Tools – Scenario Chọn tên bộ số liệu cần xóa trong danh sách và chọn Delete 5. TẠO SCENARIO Tạo tổng hợp số liệu Chọn [Menu]Tools – Scenario Chọn Summary 59 Tạo tổng hợp số liệu - Nhập những cell cần tổng hợp giá trị vào Result cells (những cell này đã được thiết lập công thức để tổng hợp) - Chọn Scenario Summary để tổng hợp theo mẫu thường hay Scenario Pivot Table report để tổng hợp theo mẫu Pivot Table. Tạo tổng hợp số liệu 6- Bài toán tìm giá trị để đạt mục tiêu GOAL SEEK 6. BÀI TOÁN GOAL SEEK Goal seek là một phần nằm trong tập hợp các công cụ thường được gọi là What-if analysis Khi biết kết quả mong muốn của một công thức nhưng giá trị đầu vào của công thức quyết định kết quả này, chúng ta có thể sử dụng Goal Seek để thực hiện. 60 6. BÀI TOÁN GOAL SEEK Nói chung Goal seek thường dùng để giải phương trình 1 ẩn. Trong kinh tế Goal seek (Hàm mục tiêu) thường áp dụng trong các bài toán như tính doanh thu hòa vốn, thay đổi một chỉ tiêu chi phí nào đó để có được lợi nhuận như mong muốn, tính tổng chi phí cho tổng chi phí ròng phải trả 6. BÀI TOÁN GOAL SEEK Để thực hiện trước tiên ta phải lập bảng tính theo một mẫu nào đó, mà cụ thể là nên thực hiện 2 điều sau đây: Chọn 1 ô làm ẩn (By changing cell). Đây là ô mà mà Goal Seek sẽ xử lý lặp đi lặp lại để cố gắng tìm ra giá trị gần đúng nhất. (Ô này phải chứa giá trị, không chứa công thức và phải tham gia vào công thức trong ô kết quả ta muốn giá trị đạt tới). Thiết lập công thức tính toán theo yêu cầu nào đó tại 1 ô khác (Set cell - chú ý: công thức này phải có mối quan hệ với ô chứa ẩn ở trên). 6. BÀI TOÁN GOAL SEEK Chọn [Menu]Tools – Goal Seek Địa chỉ ô chứa công thức Giá trị mà công thức đạt được Địa chỉ ô chứa ẩn số 6. BÀI TOÁN GOAL SEEK Sản xuất 100 sản phẩm, mỗi sản phẩm trị giá 20 đồng, đóng thuế 3 đồng thì thu được 1700 đồng. Hỏi nếu muốn thu được 1900 đồng thì thuế mỗi sản phẩm phải là bao nhiêu? Nhận xét: C2 (thuế) là ẩn ta phải tìm => by changing cell D2 là nơi chứa công thức để tính => Set cell 1900 là giá trị mục tiêu mà ta cần đạt được 61 6. BÀI TOÁN GOAL SEEK Chọn cell D2 (trong D2 chứa công thức = A2 * (B2 – C2)) Chọn [Menu]Tools – Goal Seek Nhập theo hình sau 6. BÀI TOÁN GOAL SEEK Trả lời: Để đạt được doanh thu 1900 thì thuế cho mỗi sản phẩm là 1 7- Bài toán tìm lời giải tối ưu SOLVER 7. BÀI TOÁN SOLVER Đối với việc tìm lời giải cho bài toán tối ưu (optimal problem) khó khăn lớn nhất khi đã biết được thuật toán là chi phí tính toán sẽ rất lớn do dữ liệu cần xử và số phương án. Và việc tính toán thủ công để tìm phương án tối ưu trong thực tế là không khả thi. Với cách tiếp cận trên, MicroSoft Excel đã xây dựng công cụ Solver giúp giải các bài toán tối ưu. Nội dung phần này sẽ hướng dẫn cách sử dụng công cụ Solver để tìm phương án tối ưu thông qua một số bài toán tối ưu quen thuộc như bài toán vận tải tối ưu, bài toán sản xuất tối ưu hay bài toán lựa chọn phương án tối ưu 62 Mục đích là tìm kết quả tối ưu cho một bài toán kinh tế. Sản lượng tối đa với một số nguyên liệu, chi phí hạn chế Đạt một mức độ sao cho chi phí nhỏ nhất và vận hành thiết bị sản xuất không vượt quá quy định về an toàn Phí vận chuyển nhỏ nhất giữa nơi sản xuất và nơi tiêu thụ Hỗn hợp nguyên vật liệu sao cho đạt chất lượng với yêu cầu chi phí thấp nhất 7. BÀI TOÁN SOLVER 1. Add-Ins Solver Trước khi sử dụng, chúng ta cần phải thêm tiện ích này vào Excel. Tùy theo phiên bản Excel mà bạn đang sử dụng thì cách làm khác nhau đôi chút. Tuy nhiên, cách thực hiện Add-In Solver cho các phiên bản Excel 97-2003 là giống nhau và cách thực hiện Add-In Solver trong Excel 2007-2010 cũng tương tự nhau. 7. BÀI TOÁN SOLVER 1.1. Add-In Solver cho Excel 2003 Bước 1. Vào Tools | chọn Add-Ins. Hộp thoại Add-Ins xuất hiện: chọn mục Add-Ins 7. BÀI TOÁN SOLVER Bước 2. Check vào mục chọn Solver Add-in. Bước 3. Nhấn nút OK đóng hộp Add-Ins Sau đó trong thực đơn Tools mới xuất hiện thêm lệnh Solver 7. BÀI TOÁN SOLVER 63 1.2. Add-In Solver cho Excel 2007 -2010 Do giao diện Excel 2007 thay đổi nhiều so với các phiên bản Excel cũ nên các bước thực hiện Add-Ins sẽ khác đôi chút. Bước 1. Nhấp chuột vào nút Office | chọn Excel Option 7. BÀI TOÁN SOLVER 1.2. Add-In Solver cho Excel 2007 - 2010 Bước 2. Trong hộp thoại Excel Options, chọn Add-Ins từ danh sách bên trái, danh sách các Add-Ins trong Excel được liệt kê trong hộp Add-Ins với các phân nhóm khác nhau. 7. BÀI TOÁN SOLVER 1.2. Add-In Solver cho Excel 2007 - 2010 Bước 3. Tại Manage, chọn Excel Add-Ins từ danh sách và nhấn nút Go... để mở hộp thoại Add-Ins. 7. BÀI TOÁN SOLVER 1.2. Add-In Solver cho Excel 2007 - 2010 Bước 4. Chọn Solver Add-in từ danh sách Add-Ins avaiable và nhấn nút OK. Bước 5. Trong ngăn Data xuất hiện thêm nhóm Analysis chứa lệnh Solver. 7. BÀI TOÁN SOLVER 64 1.2. Add-In Solver cho Excel 2007 -2010 Bước 5. Trong ngăn Data xuất hiện thêm nhóm Analysis chứa lệnh Solver. 7. BÀI TOÁN SOLVER 2. Sử dụng Solver  Vào Tools/chọn Solver Địa chỉ Ô chứa hàm mục tiêu Chọn phương án cho hàm mục tiêu Nhập ĐC khối ô chứa giá trị thay đổi Thêm các ràng buộc 7. BÀI TOÁN SOLVER Thêm các ràng buộc  Khi ta click vào nút add để thêm các ràng buộc , xuất hiện cửa sổ sau: 7. BÀI TOÁN SOLVER ĐC ô chịu ràng buộc Lựa chọn phép toán ràng buộc ĐC ô chứa giá trị ràng buộc Sau khi xác định các tham số, kích nút Solver, Xuất hiện hộp thoại giữ các giá trị đã phân tích phục hồi lại giá trị ban đầu 7. BÀI TOÁN SOLVER 65 7. BÀI TOÁN SOLVER Thiết lập các thông số cho SOLVER Khi ta click vào nút OPTION, xuất hiện cửa sổ sau: Tham số Giải thích Max Time Thời gian tối đa để giải bài toán, giá trị mặc định là 100 giây dùng cho các bài toán đơn giản. Thời gian tối đa có thể nhập là 32.767 giây. Iterations Số lần lặp tối đa để giải bài toán, giá trị mặc định là 100 lần dùng cho các bài toán đơn giản. Thời gian tối đa có thể nhập là 32.767 giây. 7. BÀI TOÁN SOLVER Precision Độ chính xác của bài toán. Tại đây có thể nhập vào các số trong khoảng 0 và 1. Số càng gần 0 thì độ chính xác càng cao. Giá trị này điều chỉnh độ sai số cho tập ràng buộc. Giá trị mặc định là 1 phần triệu. Tolerance Chỉ áp dụng đối với bài toán có ràng buộc nguyên. Nhập vào sai số có thể chấp nhận được, sai số càng lớn thì tốc độ giải càng nhanh. Giá trị mặc định là 5%. 7. BÀI TOÁN SOLVER Convergence Chỉ áp dụng cho các bài toán không tuyến tính. Tại đây nhập vào các số trong khoảng 0 và 1. Số càng gần 0 thì độ chính xác càng cao và cần nhiều thời gian hơn. Assume Linear Model Chọn để tăng tốc độ giải bài toán khi tất cả quan hệ trong mô hình là tuyến tính. 7. BÀI TOÁN SOLVER 66 Assume Non- Negative Chọn tùy chọn này nếu muốn Solver giả định là tất cả các biến là không âm. Use Automatic Scaling Chọn khi bài toán mà các dữ liệu nhập và xuất có sự khác biệt lớn. Ví dụ bài toán tối ưu % lợi nhuận trên hàng triệu USD vốn đầu tư. Show Iteration Results Chọn nếu muốn Solver tạm dừng lại và hiển thị kết quả sau mỗi lần lặp. 7. BÀI TOÁN SOLVER Estimates Chọn phương pháp cho Solver dùng để ước lượng các biến: Tangent: Sử dụng cách xấp xỉ tuyến tính bậc nhất Quadratic: Sử dụng cách xấp xỉ bậc bốn Derivatives Chọn cách để ưức lượng hàm mục tiêu và các ràng buộc Forward: được dùng phổ biến hơn, khi đó các giá trị của ràng buộc biến đổi chậm. Central: Dùng khi các giá trị của ràng buộc biến đổi nhanh và được dùng khi Solver báo không thể cải tiến kết quả thu được 7. BÀI TOÁN SOLVER Search Quy định giải thuật tìm kiếm kết quả cho bài toán Newton: là phương pháp mặc định, nó sử dụng nhiều bộ nhớ hơn và số lần lặp ít hơn. Conjugate: Cần bộ nhớ ít hơn nhưng số lần lặp nhiều hơn. 7. BÀI TOÁN SOLVER 7. BÀI TOÁN SOLVER Các bước để giải các bài toán tối ưu sử dụng Solver thực hiện theo các bước sau: Bước 1: Xây dựng hàm mục tiêu (Objective Function) Bước 2: Xây dựng các ràng buộc (Constraints) Bước 3: Tổ chức dữ liệu trên bảng tính Excel Bước 4: Sử dụng Solver để tìm phương án tối ưu 67 7.1 Bài toán quy hoạch Xét bài toán quy hoạch: c1x1 + c2x2 + +cnxn = f(x)  max/min a11x1 + a12x2 + a1nxn Q b1 a21x1 + a22x2 + a2nxn Q b2 .. am1x1 + am2x2 + amnxn Q bm Trong đó Q là một trong các phép toán quan hệ   = thứ tự các phép toán quan hệ trong các ràng buộc là tuỳ ý 7.1 Bài toán quy hoạch Giả sử với 2 bước đầu tiên ta có hàm mục tiêu và các ràng buộc của bài toán tối ưu được thiết lập như sau: Bước 1: Hàm mục tiêu: f(x) = 2x1+8x2-5x3+15x4 Max Bước 2: Với các ràng buộc : 3x1-x2+x3+10x4 =5 x1+2x2+x3+5x4 >=9 2x1+10x2+2x3-5x4 <=26 xj> =0, j =1..4 Sử dụng Solver để tìm phương án tối ưu cho bài toán trên. 7.1 Bài toán quy hoạch Bước 3: Tổ chức dữ liệu (Chú ý Có thể khởi tạo giá trị cho các biến x1, x2, x3, x4 là 0 hoặc để trống.) 7.1 Bài toán quy hoạch Bước 4: Sử dụng Solver để tìm phương án tối ưu(Vào Menu Tools chọn Hộp thoại Solver xuất hiện, thiết lập các tham số cho solver như sau: 68 7.1 Bài toán quy hoạch Giải thích các tham số: Set Target Cell: Ô chứa giá trị hàm mục tiêu (F5) Equal to: Chọn Max, (vì mong muốn kết quả hàm mục tiêu đạt giá trị MAX) By changing Cells: Các ô chứa phương án tối ưu cần tìm ($B$4:$E$4) (nơi chứa các giá trị khởi đầu của biến) Subject to the Constraints: Chứa các ràng buộc của hàm mục tiêu. Gồm 4 ràng buộc như đã cho. (3 ràng buộc biểu thức, 1 ràng buộc giá trị kết quả các biến >=0) 7.1 Bài toán quy hoạch Kết quả: phương án tối ưu cho các giá trị của Xi là (0,3,0,0.8) và giá trị hàm mục tiêu đạt giá trị lớn nhất là 36.  Vd: Một trại chăn nuôi nuôi 3 loại bò: bò sữa, bò cày và bò thịt. Số liệu nghiên cứu cho như sau: Tìm số bò mỗi loại cần nuôi để đạt tổng số tiền lợi nhuận lớn nhất. Biết rằng số bò sữa không quá 18 con. Loại bò Chi phí Bò sữa Bò cày Bò thịt Dự trữ Vốn 123 127 162 7020 Chi phí chăn nuôi 18 15 15 800 Lợi nhuận 59 49 57 7.2 Bài toán sản xuất Bước 1: Xây dựng hàm mục tiêu (Objective Function) Gọi X1, X2, X3, là số bò cần nuôi (với Xi>=0 và nguyên ; X1<=18). Vậy mỗi phương án sản xuất là một vector X có dạng : Hàm mục tiêu: f(x) = 59X1 + 49X2 + 57X3 với mong muốn đạt MAX 7.2 Bài toán sản xuất 69 Bước 2: Xây dựng các ràng buộc (Constraints) Các ràng buộc:  123X1 + 127X2 + 162X3 <= 7020  18X1 + 15X2 + 15X3 <= 800  X1 <= 18  X1, X2, X3 >=0, Nguyên 7.2 Bài toán sản xuất Bước 3: Tổ chức dữ liệu trên bảng tính Excel 7.2 Bài toán sản xuất Giải thích: số liệu trên bảng tính Excel 7.2 Bài toán sản xuất Dữ liệu đầu vào A11:E17 vùng dữ liệu đầu vào (chứa các giá trị của bảng số liệu đã cho) Dữ liệu đầu ra Vần đề khởi tạo các biến ( yêu cầu học viên tô nền màu vàng ở vùng giá trị các biến) B21: 0 (X1) C21: 0 (X2) D21: 0 (X3) E21: SUM(B21:D21) Tổng số bò các loại sẽ nuôi (X1+X2+X3). 7.2 Bài toán sản xuất 70 Vấn đề vốn B22: B14*B$21 (123X1) Vốn cần có để mua lượng X1 bò sữa C22: C14*C$21 (127X2) Vốn cần có để mua lượng X2 bò cày D22: D14*D$21 (162X3) Vốn cần có để mua lượng X3 bò thịt E22: SUM(B23:D23) Tổng vốn mua các loại bò (123X1 + 127X2 + 162X3)  7.2 Bài toán sản xuất Vấn đề chi phí B23: B15*B$21 (18X1) Chi phí cần có để nuôi lượng X1 bò sữa C23: C15*C$21 (15X2) Chi phí cần có để nuôi lượng X2 bò cày D23: D15*D$21 (15X3) Chi phí cần có để nuôi lượng X3 bò thịt E23: SUM(B23:D23) Tổng chi phí khi nuôi các loại bò (18X1 + 15X2 + 15X3) 7.2 Bài toán sản xuất Vấn đề tiền lời B24: B16*B$21 (59X1) ) Tiền lời có được khi nuôi bò lượng X1 bò sữa. C24: C16*C$21 (49X2) Tiền lời có được khi nuôi bò lượng X2 bò cày. D24: D16*D$21 (57X3) Tiền lời có được khi nuôi bò lượng X3 bò thịt. E24: SUM(B24:D24) Tổng tiền lời thu được khi