Mặc dù các lệnh Tìm kiếm Mục tiêu và Bảng Dữ liệu của Excel hoạt động tốt đối với các vấn đề đơn giản yêu cầu xác định mối quan hệ trực tiếp giữa các đầu vào và kết quả trong một công thức, bạn cần sử dụng bổ trợ Bộ giải khi giải quyết các vấn đề phức tạp hơn. Ví dụ: sử dụng Bộ giải để tìm giải pháp tốt nhất khi bạn cần thay đổi nhiều giá trị đầu vào trong mô hình Excel của mình và bạn cần áp đặt các ràng buộc đối với các giá trị này và / hoặc giá trị đầu ra.
Phần bổ trợ Solver hoạt động bằng cách áp dụng các phương pháp lặp đi lặp lại để tìm ra giải pháp “tốt nhất” dựa trên các yếu tố đầu vào, giải pháp mong muốn và các ràng buộc mà bạn áp đặt. Với mỗi lần lặp, chương trình áp dụng phương pháp thử-và-sai (dựa trên việc sử dụng các phương trình và bất phương trình tuyến tính hoặc phi tuyến) nhằm cố gắng tiến gần hơn đến giải pháp tối ưu.
Khi sử dụng phần bổ trợ Solver, hãy nhớ rằng nhiều vấn đề, đặc biệt là những vấn đề phức tạp hơn, có nhiều cách giải quyết. Mặc dù Bộ giải trả về giải pháp tối ưu, với các giá trị bắt đầu, các biến có thể thay đổi và các ràng buộc mà bạn xác định, giải pháp này thường không phải là giải pháp duy nhất có thể và trên thực tế, có thể không phải là giải pháp tốt nhất cho bạn. Để chắc chắn rằng bạn đang tìm ra giải pháp tốt nhất, bạn có thể muốn chạy Trình giải quyết nhiều lần, điều chỉnh các giá trị ban đầu mỗi khi bạn giải quyết vấn đề.
Khi thiết lập sự cố cho phần bổ trợ Solver trong trang tính Excel của bạn, hãy xác định các mục sau:
Sau khi bạn hoàn tất việc xác định vấn đề với các tham số này và có phần bổ trợ Solver giải quyết vấn đề, chương trình sẽ trả về giải pháp tối ưu bằng cách sửa đổi các giá trị trong trang tính của bạn. Tại thời điểm này, bạn có thể chọn giữ lại các thay đổi trong trang tính hoặc khôi phục các giá trị ban đầu cho trang tính. Bạn cũng có thể lưu giải pháp dưới dạng một kịch bản để xem sau này trước khi khôi phục các giá trị ban đầu.
Bạn có thể sử dụng phần bổ trợ Solver với Trình quản lý tình huống để giúp thiết lập vấn đề cần giải quyết hoặc lưu giải pháp để bạn có thể xem nó vào một ngày sau đó. Các ô thay đổi mà bạn xác định cho Trình quản lý kịch bản sẽ tự động được Bộ giải quyết chọn và sử dụng khi bạn chọn lệnh này và ngược lại. Ngoài ra, bạn có thể lưu giải pháp của Bộ giải cho một vấn đề dưới dạng một kịch bản (bằng cách nhấp vào nút Lưu Kịch bản trong hộp thoại Bộ giải) mà sau đó bạn có thể xem bằng Trình quản lý Tình huống.
Bước đầu tiên trong việc thiết lập vấn đề cho Bộ giải là tạo mô hình trang tính mà bạn sẽ xác định ô mục tiêu, ô biến và ô ràng buộc.
Hãy nhớ rằng Solver là một tiện ích bổ trợ. Điều này có nghĩa là, trước khi có thể sử dụng, bạn cần đảm bảo rằng chương trình bổ trợ Bộ giải vẫn được tải, như được chỉ ra bằng sự xuất hiện của nút Bộ giải trong nhóm Phân tích ở cuối tab Dữ liệu trên Ruy-băng. Nếu thiếu nút này, bạn có thể tải Trình giải quyết bằng cách mở tab Bổ trợ của hộp thoại Tùy chọn Excel (Alt + FTAA), sau đó nhấp vào nút Bắt đầu sau khi đảm bảo rằng Phần bổ trợ Excel được hiển thị trong trình đơn thả xuống Quản lý hộp danh sách ngay bên trái của nó. Sau đó, chọn hộp kiểm Giải bổ trợ trong hộp thoại Trình bổ sung để đánh dấu chọn vào đó trước khi bạn bấm OK để đóng hộp thoại và tải lại bổ trợ.
Để xác định và giải quyết vấn đề với bổ trợ Solver sau khi bạn đã tải bổ trợ và đã tạo mô hình trang tính của mình, bạn hãy làm theo các bước sau:
Bấm vào nút lệnh Solver trong nhóm Phân tích ở cuối tab Dữ liệu của ruy-băng.
Excel mở hộp thoại Tham số bộ giải.
Chỉ định các tham số để áp dụng cho mô hình trong hộp thoại Tham số bộ giải.
Bấm vào ô đích trong trang tính hoặc nhập tham chiếu ô hoặc tên phạm vi của nó trong hộp văn bản Đặt Mục tiêu.
Tiếp theo, bạn cần chọn cài đặt To. Bấm vào nút Tùy chọn tối đa khi bạn muốn giá trị của ô đích càng lớn càng tốt. Bấm vào nút tùy chọn Min khi bạn muốn giá trị của ô đích càng nhỏ càng tốt. Bấm vào nút tùy chọn Giá trị của và sau đó nhập giá trị vào hộp văn bản được liên kết khi bạn muốn giá trị của ô đích đạt đến một giá trị cụ thể.
Bấm vào tùy chọn nút tùy chọn thích hợp trong phần Tới của hộp thoại. Nếu bạn chọn nút tùy chọn Value Of, hãy nhập giá trị phù hợp vào hộp văn bản được liên kết.
Tiếp theo, chỉ định các ô có thể thay đổi - tức là những ô mà Bộ giải có thể thay đổi để đạt được mục tiêu Bằng nhau của bạn.
Bấm vào hộp văn bản Bằng cách thay đổi ô có thể thay đổi và sau đó chọn các ô để thay đổi trong trang tính hoặc nhập tham chiếu ô hoặc tên phạm vi của chúng trong hộp văn bản.
Hãy nhớ rằng để chọn các ô không liền kề trong trang tính, bạn cần giữ phím Ctrl khi nhấp vào từng ô trong vùng chọn. Để Excel chọn các ô thay đổi cho bạn dựa trên ô đích mà bạn đã chọn, hãy bấm vào nút Đoán ở bên phải của hộp văn bản này.
Trước khi yêu cầu Solver điều chỉnh mô hình của bạn, bạn có thể thêm các ràng buộc cho ô đích hoặc bất kỳ ô nào đang thay đổi để xác định giới hạn của nó khi điều chỉnh các giá trị.
(Tùy chọn) Nhấp vào nút Thêm ở bên phải của hộp danh sách Chủ đề cho Ràng buộc trong hộp thoại Tham số bộ giải.
Hành động này sẽ mở hộp thoại Thêm Ràng buộc. Khi xác định một ràng buộc, hãy chọn ô có giá trị mà bạn muốn ràng buộc hoặc chọn ô trong trang tính hoặc nhập tham chiếu ô của nó trong hộp văn bản Tham chiếu Ô. Sau đó, chọn mối quan hệ (=, <=,> =, hoặc int cho số nguyên hoặc bin cho nhị phân) từ hộp danh sách thả xuống ở bên phải và (trừ khi bạn chọn int hoặc bin ) nhập giá trị hoặc tham chiếu ô thích hợp vào Hộp văn bản ràng buộc.
Để tiếp tục thêm ràng buộc cho các ô khác được Bộ giải sử dụng, hãy nhấp vào nút Thêm để thêm ràng buộc và xóa các hộp văn bản trong hộp thoại Thêm ràng buộc. Sau đó, lặp lại Bước 5 để thêm một ràng buộc mới. Sau khi bạn hoàn tất việc xác định các ràng buộc cho ô đích và thay đổi các giá trị trong mô hình, hãy bấm OK để đóng hộp thoại Thêm Ràng buộc và quay lại hộp thoại Tham số bộ giải (hiện liệt kê các ràng buộc của bạn trong hộp danh sách Chủ đề Ràng buộc).
(Tùy chọn) Bỏ chọn hộp kiểm Đặt biến không bị ràng buộc thành không phủ định nếu bạn muốn cho phép giá trị âm khi các ô biến không bị ràng buộc.
Theo mặc định, Trình bổ trợ giải quyết sử dụng phương pháp phi tuyến GRG (Generalized Reduce Gradient) để giải mô hình có các tham số mà bạn đang thiết lập được coi là một cách rất hiệu quả để giải quyết các vấn đề phi tuyến trơn tru. Để sử dụng phương pháp LP Simplex (đối với Lập trình tuyến tính theo thuật toán Simplex) hoặc Công cụ tiến hóa để giải quyết các vấn đề không trơn tru, bạn cần thực hiện theo Bước 7.
(Tùy chọn) Chọn LP Simplex hoặc Evolution từ danh sách thả xuống Chọn phương pháp giải để sử dụng một trong các phương pháp này để giải quyết các vấn đề không êm dịu.
Nhấp vào nút Giải quyết để Bộ giải quyết vấn đề như bạn đã xác định trong hộp thoại Tham số bộ giải.
Khi bạn nhấp vào nút Giải quyết, hộp thoại Tham số bộ giải sẽ biến mất và thanh trạng thái cho biết rằng Bộ giải đang thiết lập vấn đề và sau đó thông báo cho bạn về tiến trình giải quyết vấn đề bằng cách hiển thị số trung gian (hoặc thử nghiệm) các giải pháp khi chúng được thử. Để ngắt quá trình giải pháp bất kỳ lúc nào trước khi Excel tính toán lần lặp cuối cùng, hãy nhấn phím Esc. Sau đó, Excel sẽ hiển thị hộp thoại Hiển thị Giải pháp Thử nghiệm, thông báo cho bạn biết rằng quá trình giải pháp đã bị tạm dừng. Để tiếp tục quá trình giải pháp, hãy nhấp vào nút Tiếp tục. Để hủy bỏ quy trình giải pháp, hãy nhấp vào nút Dừng.
Khi Excel kết thúc quá trình giải pháp, hộp thoại Kết quả bộ giải sẽ xuất hiện. Hộp thoại này thông báo cho bạn biết liệu Bộ giải có thể tìm ra giải pháp, đã cung cấp ô đích, ô thay đổi và các ràng buộc được xác định cho vấn đề hay không. Để giữ lại những thay đổi mà Bộ giải thực hiện trong mô hình trang tính của bạn, hãy chọn nút tùy chọn Giữ Giải pháp Bộ giải và bấm OK để đóng hộp thoại Kết quả Bộ giải. Để trả lại các giá trị ban đầu cho trang tính, hãy nhấp vào nút tùy chọn Khôi phục Giá trị Gốc để thay thế. Để lưu các thay đổi dưới dạng một kịch bản trước khi bạn khôi phục các giá trị ban đầu, hãy nhấp vào nút Lưu Kịch bản và gán tên cho kịch bản hiện tại trước khi bạn nhấp vào tùy chọn Khôi phục Giá trị Gốc và nút OK.
Hộp thoại Solver Results cho thấy rằng Solver đã tìm ra giải pháp cho vấn đề.
Không giống như khi sử dụng lệnh Goal Seek, sau khi nhấp vào nút tùy chọn Keep Solver Solution trong hộp thoại Solver Results, bạn không thể sử dụng nút lệnh Hoàn tác trên thanh công cụ Quick Access để khôi phục các giá trị ban đầu cho trang tính của mình. Nếu bạn muốn có thể chuyển đổi giữa chế độ xem “trước” và “sau” trang tính của mình, bạn phải lưu các thay đổi bằng nút Lưu Kịch bản và sau đó chọn nút tùy chọn Khôi phục Giá trị Gốc. Bằng cách đó, bạn có thể giữ lại chế độ xem “trước” trong trang tính gốc và sử dụng Trình quản lý tình huống để hiển thị chế độ xem “sau” được tạo bởi Bộ giải.
Đối với hầu hết các sự cố, các tùy chọn mặc định được Bộ giải quyết sử dụng là phù hợp. Tuy nhiên, trong một số tình huống, bạn có thể muốn thay đổi một số tùy chọn Bộ giải trước khi bắt đầu quy trình giải pháp. Để thay đổi các tùy chọn giải pháp, hãy nhấp vào nút Tùy chọn trong hộp thoại Tham số bộ giải. Sau đó, Excel sẽ mở hộp thoại Tùy chọn với tab Tất cả các phương pháp được chọn, nơi bạn có thể thực hiện tất cả các thay đổi cần thiết.
Sửa đổi các tùy chọn giải pháp trong hộp thoại Tùy chọn.
Cài đặt tùy chọn trình giải quyết Excel 2019
| Lựa chọn | Hàm số |
| Độ chính xác ràng buộc | Chỉ định độ chính xác của các ràng buộc. Số bạn nhập vào hộp văn bản này xác định xem giá trị trong ô ràng buộc có đáp ứng giá trị được chỉ định hoặc giới hạn trên hoặc giới hạn dưới mà bạn đã đặt hay không. Chỉ định một số thấp hơn (từ 0 đến 1) để giảm thời gian Người giải quyết trả về giải pháp cho vấn đề của bạn. |
| Sử dụng quy mô tự động | Chọn hộp kiểm này để Bộ giải tự động chia tỷ lệ kết quả khi giải quyết vấn đề. |
| Hiển thị kết quả lặp lại | Chọn hộp kiểm này để Trình giải quyết hiển thị kết quả cho các lần lặp lại sau khi giải quyết vấn đề. |
| Bỏ qua các ràng buộc số nguyên | Chọn hộp kiểm này để Bộ giải bỏ qua bất kỳ ràng buộc nào bạn chỉ định sử dụng số nguyên. |
| Số nguyên tối ưu (%) | Chỉ định tỷ lệ phần trăm của tiêu chí tối ưu số nguyên mà Bộ giải áp dụng trong việc giải quyết vấn đề. |
| Thời gian tối đa (giây) | Chỉ định số giây tối đa mà Người giải quyết sẽ dành để tìm ra giải pháp. |
| Lặp lại | Chỉ định số lần tối đa mà Bộ giải sẽ tính toán lại trang tính khi tìm ra lời giải. |
| Bài toán con tối đa | Chỉ định số lượng bài toán con tối đa mà Bộ giải đảm nhận khi sử dụng phương pháp Tiến hóa để giải quyết vấn đề. |
| Các giải pháp khả thi tối đa | Chỉ định số lượng giải pháp khả thi tối đa mà Người giải quyết sẽ theo đuổi khi bạn chọn Phương pháp tiến hóa để giải quyết vấn đề. |
Sau khi thay đổi các tùy chọn, bấm OK để quay lại hộp thoại Solver Parameters; từ đây, bạn có thể nhấp vào nút Giải quyết để bắt đầu quy trình giải pháp với cài đặt giải pháp mới mà bạn vừa thay đổi.
Khi bạn sử dụng phương pháp phi tuyến hoặc tiến hóa GRG (Generalized Reduced Gradient) mặc định, bạn có thể đặt cài đặt Bộ giải bổ sung bằng cách sử dụng các tùy chọn trên tab GRG Phi tuyến và Tiến hóa của hộp thoại Tùy chọn. Các tùy chọn này bao gồm thay đổi cài đặt Hội tụ, Quy mô dân số và Hạt giống ngẫu nhiên cho một trong các phương pháp cụ thể này.
Ô mục tiêu, ô biến, ô ràng buộc và các tùy chọn Bộ giải mà bạn sử dụng gần đây nhất được lưu dưới dạng một phần của trang tính Excel khi bạn nhấp vào nút Lưu trên thanh công cụ Truy nhập Nhanh (Ctrl + S). Khi bạn xác định các vấn đề khác cho cùng một trang tính mà bạn muốn lưu, bạn phải nhấp vào nút Lưu Mô hình trong hộp thoại Tùy chọn bộ giải và chỉ ra tham chiếu ô hoặc tên của phạm vi trong trang tính đang hoạt động mà bạn muốn tham số của vấn đề. đã chèn.
Khi bạn nhấp vào nút Tải / Lưu, Excel sẽ mở hộp thoại Tải / Lưu Mô hình, chứa hộp văn bản Chọn Khu vực Mô hình. Hộp văn bản này chứa các tham chiếu ô cho một phạm vi đủ lớn để chứa tất cả các tham số của vấn đề, bắt đầu với ô hiện hoạt. Để lưu các tham số của sự cố trong phạm vi này, hãy nhấp vào OK. Nếu phạm vi này bao gồm các ô có dữ liệu hiện có, bạn cần sửa đổi tham chiếu ô trong hộp văn bản này trước khi bấm OK để ngăn Excel thay thế dữ liệu hiện có.
Sau khi bạn bấm OK, Excel sẽ sao chép các tham số của vấn đề trong phạm vi được chỉ định. Các giá trị này sau đó được lưu như một phần của trang tính vào lần tiếp theo bạn lưu sổ làm việc. Để sử dụng lại các tham số vấn đề này khi giải quyết một vấn đề, bạn chỉ cần mở hộp thoại Solver Options, nhấp vào nút Load / Save để mở hộp thoại Load / Save Model, nhấp vào nút Load, sau đó chọn phạm vi chứa các lưu. tham số vấn đề. Khi bạn bấm OK trong hộp thoại Tải Mô hình, Excel sẽ tải các tham số từ phạm vi ô này vào các hộp văn bản thích hợp trong hộp thoại Tham số Bộ giải. Sau đó, bạn có thể đóng hộp thoại Solver Options bằng cách nhấp vào OK và bạn có thể giải quyết vấn đề bằng cách sử dụng các tham số này bằng cách nhấp vào nút lệnh Solve.
Hãy nhớ rằng bạn có thể sử dụng nút Đặt lại Tất cả bất cứ khi nào bạn muốn xóa tất cả các tham số được xác định cho vấn đề trước đó và trả các tùy chọn Bộ giải về mặc định của chúng.
Bạn có thể tạo ba loại báo cáo khác nhau với Trình giải quyết:
Excel đặt từng báo cáo mà bạn tạo cho một vấn đề của Bộ giải trong một trang tính riêng biệt trong sổ làm việc. Để tạo một (hoặc tất cả) trong số các báo cáo này, hãy chọn loại báo cáo (Trả lời, Độ nhạy hoặc Giới hạn) từ hộp danh sách Báo cáo của hộp thoại Kết quả của Bộ giải. Để chọn nhiều báo cáo, chỉ cần nhấp vào tên của báo cáo.
Khi bạn bấm OK để đóng hộp thoại Kết quả bộ giải (sau khi chọn giữa các tùy chọn Giữ Giải pháp Bộ giải và Khôi phục Giá trị Gốc), Excel sẽ tạo báo cáo (hoặc các báo cáo) mà bạn đã chọn trong một trang tính mới mà nó thêm vào đầu sổ làm việc . (Các tab của trang báo cáo được đặt tên theo loại báo cáo, như trong Báo cáo Trả lời 1, Báo cáo Độ nhạy 1 và Báo cáo Giới hạn 1. )
Các hàm hồi quy của Excel cho phép bạn thực hiện phân tích hồi quy hiệu quả. Hãy tìm hiểu những hàm quan trọng như FORECAST, INTERCEPT, và LINEST trong Excel để dự báo và phân tích dữ liệu.
Khi các macro Excel của bạn ngày càng trở nên mạnh mẽ và phức tạp, bạn có thể thấy rằng chúng giảm hiệu suất. Khi thảo luận về macro, từ hiệu suất thường đồng nghĩa với tốc độ. Tốc độ là tốc độ các thủ tục VBA của bạn thực hiện các tác vụ dự định của chúng. Sau đây là mười cách để giúp giữ cho macro Excel của bạn chạy ở mức hiệu suất tối ưu. Tạm dừng […]
Trước Excel 2007, các lập trình viên VBA đã sử dụng đối tượng CommandBar để tạo menu tùy chỉnh, thanh công cụ tùy chỉnh và menu phím tắt tùy chỉnh (nhấp chuột phải). Bắt đầu với Excel 2007, đối tượng CommandBar ở một vị trí khá kỳ lạ. Nếu bạn viết mã để tùy chỉnh menu hoặc thanh công cụ, Excel sẽ chặn mã đó và bỏ qua nhiều lệnh của bạn. Thay thế […]
Phân phối f là phân phối xác suất trong Excel so sánh tỷ lệ theo phương sai của các mẫu được lấy từ các quần thể khác nhau. Sự so sánh đó tạo ra một kết luận về việc liệu các phương sai trong các quần thể cơ bản có giống nhau hay không. F.DIST: Xác suất phân phối f bên trái Hàm F.DIST trả về xác suất bên trái của việc quan sát một tỷ lệ phương sai của hai mẫu càng lớn […]
Nếu bạn đã sử dụng bút hoặc bút tô sáng để vẽ trên các trang chiếu PowerPoint của mình trong khi trình bày, bạn có thể lưu các bản vẽ cho bản trình bày tiếp theo hoặc xóa chúng để lần sau khi trình chiếu, bạn sẽ bắt đầu với các trang chiếu PowerPoint sạch sẽ. Làm theo các hướng dẫn sau để xóa các bản vẽ bằng bút và bút tô sáng: Xóa từng đường một tại […]
Thư viện Kiểu chứa tệp CSS, tệp Ngôn ngữ biểu định kiểu mở rộng (XSL) và hình ảnh được sử dụng bởi các trang chính, bố cục trang và điều khiển được xác định trước trong SharePoint 2010. Để định vị tệp CSS trong thư viện Kiểu của trang xuất bản: Chọn Hành động trang → Xem Tất cả Nội dung Trang web. Nội dung của trang web xuất hiện. Thư viện Style nằm trong […]
Tìm hiểu cách sử dụng hàm XLOOKUP trong Excel 2016. Việc thay thế hàm VLOOKUP đơn giản và linh hoạt hơn đáng kể!
Đối với các vấn đề đơn giản, Bộ giải trong Excel thường nhanh chóng tìm ra các giá trị của biến Bộ giải tối ưu cho hàm mục tiêu. Tuy nhiên, trong một số trường hợp, Solver gặp khó khăn khi tìm các giá trị biến Solver tối ưu hóa hàm mục tiêu. Trong những trường hợp này, Solver thường hiển thị một thông báo hoặc một thông báo lỗi mô tả hoặc thảo luận về sự cố […]
Bạn có thể dễ dàng xóa các trường và mục được tính toán khỏi bảng tổng hợp Excel. Để xóa trường được tính toán, hãy bấm vào một ô trong bảng tổng hợp. Sau đó nhấp vào lệnh Trường, Mục & Bộ của tab Phân tích và chọn Trường được Tính từ menu con xuất hiện. Khi Excel hiển thị hộp thoại Chèn Trường Tính toán, hãy chọn […]
Công thức tính toán phần trăm phương sai trong Excel hoạt động hiệu quả trong hầu hết các trường hợp. Tuy nhiên, khi giá trị điểm chuẩn là một giá trị âm, công thức sẽ bị phá vỡ. Ví dụ, hãy tưởng tượng rằng bạn đang bắt đầu kinh doanh và dự kiến sẽ thua lỗ trong năm đầu tiên. Vì vậy, bạn cung cấp cho mình một ngân sách âm 10.000 đô la. Bây giờ hãy tưởng tượng rằng […]
