Nội dung
“Tìm kiếm giải pháp” là một bổ trợ Excel, qua đó có thể chọn giải pháp tốt nhất cho các vấn đề dựa trên các hạn chế đã chỉ định. Chức năng này giúp bạn có thể lên lịch cho nhân viên, phân phối chi phí hoặc đầu tư. Biết cách hoạt động của tính năng này sẽ giúp bạn tiết kiệm thời gian và công sức.
Tìm kiếm giải pháp là gì
Kết hợp với nhiều tùy chọn khác trong Excel, có một chức năng ít phổ biến hơn, nhưng cực kỳ cần thiết “Tìm kiếm giải pháp”. Mặc dù thực tế là việc tìm ra nó không hề dễ dàng, nhưng việc tìm hiểu và sử dụng nó sẽ giúp giải quyết nhiều vấn đề. Tùy chọn xử lý dữ liệu và đưa ra giải pháp tối ưu từ những tùy chọn được phép. Bài viết mô tả cách hoạt động trực tiếp của Tìm kiếm Giải pháp.
Cách bật tính năng “Tìm kiếm giải pháp”
Mặc dù hiệu quả, tùy chọn được đề cập không nằm ở vị trí nổi bật trên thanh công cụ hoặc menu ngữ cảnh. Hầu hết người dùng làm việc trong Excel không nhận thức được sự hiện diện của nó. Theo mặc định, chức năng này bị tắt, để hiển thị nó, hãy thực hiện các tác vụ sau:
- Mở “Tệp” bằng cách nhấp vào tên thích hợp.
- Nhấp vào phần "Cài đặt".
- Sau đó chọn tiểu mục “Tiện ích bổ sung”. Tất cả các tiện ích bổ sung của chương trình sẽ được hiển thị tại đây, dòng chữ “Quản lý” sẽ xuất hiện bên dưới. Ở bên phải của nó sẽ có một menu bật lên, nơi bạn nên chọn “Excel Add-in”. Sau đó nhấp vào “Bắt đầu”.
1 - Một cửa sổ bổ sung “Phần bổ trợ” sẽ được hiển thị trên màn hình. Chọn hộp bên cạnh chức năng mong muốn và nhấp vào OK.
- Chức năng mong muốn sẽ xuất hiện trên ruy-băng ở bên phải phần “Dữ liệu”.
Giới thiệu về Mô hình
Thông tin này sẽ cực kỳ hữu ích cho những ai mới làm quen với khái niệm “mô hình tối ưu hóa”. Trước khi sử dụng “Tìm kiếm giải pháp”, bạn nên nghiên cứu tài liệu về các phương pháp xây dựng mô hình:
- tùy chọn đang được xem xét sẽ giúp bạn có thể xác định được phương pháp tốt nhất để phân bổ vốn cho các khoản đầu tư, tải mặt bằng, cung cấp hàng hóa hoặc các hành động khác khi cần thiết để tìm ra giải pháp tốt nhất.
- “Phương pháp tối ưu” trong tình huống như vậy có nghĩa là: tăng thu nhập, giảm chi phí, nâng cao chất lượng, v.v.
Các tác vụ tối ưu hóa điển hình:
- Xác định kế hoạch sản xuất, trong đó lợi nhuận từ việc bán hàng hóa đã giải phóng sẽ là tối đa.
- Xác định bản đồ giao thông vận tải, trong đó chi phí vận chuyển được giảm thiểu.
- Tìm kiếm sự phân phối của một số máy móc cho nhiều loại công việc khác nhau để giảm chi phí sản xuất.
- Xác định thời gian hoàn thành công việc ngắn nhất.
Quan trọng! Để chính thức hóa nhiệm vụ, cần tạo ra một mô hình phản ánh các thông số chính của lĩnh vực chủ thể. Trong Excel, mô hình là một tập hợp các công thức sử dụng các biến. Tùy chọn được xem xét tìm kiếm các chỉ số sao cho hàm mục tiêu lớn hơn (nhỏ hơn) hoặc bằng giá trị được chỉ định.
Giai đoạn chuẩn bị
Trước khi đặt một chức năng trên ruy-băng, bạn cần hiểu cách hoạt động của tùy chọn. Ví dụ, có thông tin về việc bán hàng hóa được chỉ ra trong bảng. Nhiệm vụ là chỉ định mức chiết khấu cho mỗi mặt hàng, sẽ là 4.5 triệu rúp. Tham số được hiển thị bên trong một ô được gọi là đích. Dựa vào đó, các thông số khác được tính toán.
Nhiệm vụ của chúng ta sẽ là tính chiết khấu mà nhân với tổng số tiền bán các sản phẩm khác nhau. 2 yếu tố này được kết nối với nhau bằng một công thức được viết như sau: = D13 * $ G $ 2. Trong D13, tổng số lượng thực hiện được ghi và $ G $ 2 là địa chỉ của phần tử mong muốn.
Sử dụng chức năng và thiết lập nó
Khi công thức đã sẵn sàng, bạn cần sử dụng trực tiếp hàm:
- Bạn cần chuyển sang phần “Dữ liệu” và nhấp vào “Tìm kiếm giải pháp”.
- “Tùy chọn” sẽ mở ra, nơi các cài đặt bắt buộc được đặt. Trong dòng “Tối ưu hóa hàm mục tiêu:” bạn nên chỉ định ô hiển thị tổng số chiết khấu. Có thể tự quy định tọa độ hoặc chọn từ tài liệu.
- Tiếp theo, bạn cần vào phần cài đặt của các thông số khác. Trong phần “Tới:”, có thể đặt giới hạn tối đa và tối thiểu hoặc một con số chính xác.
- Sau đó, trường “Thay đổi giá trị của các biến:” được điền. Tại đây, dữ liệu của ô mong muốn được nhập, chứa một giá trị cụ thể. Tọa độ được đăng ký độc lập hoặc ô tương ứng trong tài liệu được nhấp vào.
- Sau đó, tab “Theo hạn chế:” được chỉnh sửa, nơi đặt các hạn chế đối với dữ liệu được áp dụng. Ví dụ: phân số thập phân hoặc số âm bị loại trừ.
- Sau đó, một cửa sổ sẽ mở ra cho phép bạn thêm các hạn chế trong các phép tính. Dòng đầu tiên chứa tọa độ của một ô hoặc toàn bộ phạm vi. Theo các điều kiện của nhiệm vụ, dữ liệu của ô mong muốn được chỉ ra, nơi chỉ báo chiết khấu được hiển thị. Khi đó dấu hiệu so sánh được xác định. Nó được đặt thành “lớn hơn hoặc bằng” để giá trị cuối cùng không có dấu trừ. “Giới hạn” ở dòng 3 là 0 trong trường hợp này. Cũng có thể đặt giới hạn bằng “Thêm”. Các bước tiếp theo giống nhau.
- Khi các bước trên được hoàn thành, giới hạn đã đặt sẽ xuất hiện ở dòng lớn nhất. Danh sách có thể lớn và sẽ phụ thuộc vào độ phức tạp của các phép tính, tuy nhiên, trong một tình huống cụ thể, 1 điều kiện là đủ.
- Ngoài ra, có thể chọn các cài đặt nâng cao khác. Ở dưới cùng bên phải có một tùy chọn “Tùy chọn” cho phép bạn thực hiện việc này.
- Trong cài đặt, bạn có thể đặt "Độ chính xác của giới hạn" và "Giới hạn của giải pháp". Trong tình huống của chúng tôi, không cần thiết phải sử dụng các tùy chọn này.
- Khi cài đặt hoàn tất, chức năng tự khởi động - nhấp vào “Tìm giải pháp”.
- Sau khi chương trình thực hiện các phép tính cần thiết và đưa ra các phép tính cuối cùng trong các ô cần thiết. Sau đó, một cửa sổ với kết quả sẽ mở ra, nơi kết quả được lưu / hủy hoặc các tham số tìm kiếm được định cấu hình theo một tham số mới. Khi dữ liệu đáp ứng các yêu cầu, giải pháp tìm được sẽ được lưu. Nếu bạn chọn trước hộp “Quay lại hộp thoại tùy chọn tìm kiếm giải pháp”, một cửa sổ có cài đặt chức năng sẽ mở ra.
- Có khả năng các tính toán bị sai sót hoặc cần phải thay đổi dữ liệu ban đầu để có được các chỉ số khác. Trong tình huống như vậy, bạn cần mở lại cửa sổ cài đặt và kiểm tra kỹ thông tin.
- Khi dữ liệu là chính xác, một phương pháp thay thế có thể được sử dụng. Với những mục đích này, bạn cần nhấp vào tùy chọn hiện tại và chọn phương pháp phù hợp nhất từ danh sách xuất hiện:
- Tìm giải pháp sử dụng Gradient tổng quát cho các vấn đề phi tuyến tính. Theo mặc định, tùy chọn này được sử dụng, nhưng có thể sử dụng tùy chọn khác.
- Tìm lời giải cho các bài toán tuyến tính dựa trên phương pháp đơn giản.
- Sử dụng tìm kiếm tiến hóa để hoàn thành nhiệm vụ.
Chú ý! Khi các tùy chọn trên không thể đối phó với tác vụ, bạn nên kiểm tra lại dữ liệu trong cài đặt, vì đây thường là lỗi chính trong các tác vụ như vậy.
- Khi nhận được chiết khấu mong muốn, bạn vẫn phải áp dụng nó để tính số lượng chiết khấu cho từng mặt hàng. Vì mục đích này, phần tử đầu tiên của cột “Số tiền chiết khấu” được tô sáng, công thức được viết «= D2 * $ G $ 2» và nhấn "Enter". Dấu hiệu đô la được đặt xuống để khi công thức được kéo dài đến các đường liền kề, G2 không thay đổi.
- Số tiền chiết khấu cho vật phẩm ban đầu bây giờ sẽ nhận được. Sau đó, bạn nên di chuyển con trỏ qua góc của ô, khi nó trở thành “dấu cộng”, LMB được nhấn và công thức được kéo dài đến các dòng cần thiết.
- Sau đó, bàn cuối cùng sẽ sẵn sàng.
Tải / Lưu Tùy chọn Tìm kiếm
Tùy chọn này hữu ích khi áp dụng các tùy chọn ràng buộc khác nhau.
- Trong menu Tùy chọn Trình tìm giải pháp, bấm Tải / Lưu.
- Nhập phạm vi cho khu vực mô hình và nhấp vào Lưu hoặc Tải.
Khi lưu mô hình, một tham chiếu được nhập vào 1 ô của cột trống nơi mô hình tối ưu hóa sẽ được đặt. Trong khi tải mô hình, một tham chiếu được nhập vào toàn bộ phạm vi chứa mô hình tối ưu hóa.
Quan trọng! Để lưu các cài đặt cuối cùng trong menu Tùy chọn Giải pháp, một sổ làm việc sẽ được lưu. Mỗi trang tính trong đó có các tùy chọn bổ trợ Trình giải quyết riêng. Ngoài ra, có thể đặt nhiều hơn 1 nhiệm vụ cho một trang tính bằng cách nhấp vào nút “Tải hoặc Lưu” để lưu các tác vụ riêng lẻ.
Một ví dụ đơn giản về việc sử dụng Solver
Cần phải xếp các thùng hàng bằng các thùng chứa sao cho khối lượng của nó là lớn nhất. Bể có thể tích 32 mét khối. m. Một hộp đầy có khối lượng 20 kg, thể tích của nó là 0,15 mét khối. m. Hộp - 80 kg và 0,5 cu. m. Yêu cầu tổng số hộp đựng ít nhất là 110 chiếc. Dữ liệu được tổ chức như thế này:
Các biến mô hình được đánh dấu bằng màu xanh lá cây. Hàm mục tiêu được đánh dấu màu đỏ. Hạn chế: theo số lượng container nhỏ nhất (lớn hơn hoặc bằng 110) và theo trọng lượng (= SUMPRODUCT (B8: C8, B6: C6) - tổng khối lượng bì trong thùng chứa.
Bằng cách tương tự, chúng tôi xem xét tổng khối lượng: = SUMPRODUCT (B7: C7, B8: C8). Một công thức như vậy là cần thiết để đặt giới hạn về tổng thể tích của các thùng chứa. Sau đó, thông qua “Tìm kiếm giải pháp”, các liên kết được nhập đến các phần tử với các biến, công thức và bản thân các chỉ số (hoặc liên kết đến các ô cụ thể). Tất nhiên, số lượng vùng chứa là một số nguyên (đó cũng là một hạn chế). Chúng tôi nhấn "Tìm giải pháp", kết quả là chúng tôi tìm thấy số lượng thùng chứa như vậy khi tổng khối lượng là tối đa và tất cả các hạn chế đã được tính đến.
Tìm kiếm giải pháp không tìm thấy giải pháp
Một thông báo như vậy sẽ bật lên khi hàm được đề cập không tìm thấy tổ hợp các điểm số biến thỏa mãn từng ràng buộc. Khi sử dụng phương pháp Simplex, rất có thể không có giải pháp.
Khi một phương pháp giải các bài toán phi tuyến được sử dụng, trong mọi trường hợp bắt đầu từ các chỉ số ban đầu của các biến, điều này cho thấy rằng giải pháp khả thi khác xa với các tham số như vậy. Nếu bạn chạy hàm với các chỉ báo ban đầu khác của các biến, thì có lẽ sẽ có một giải pháp.
Ví dụ, khi sử dụng phương pháp phi tuyến tính, các phần tử của bảng với các biến không được điền và hàm không tìm thấy nghiệm. Điều này không có nghĩa là không có giải pháp. Bây giờ, có tính đến kết quả của một đánh giá nhất định, dữ liệu khác được nhập vào các phần tử có các biến gần với các biến nhận được.
Trong bất kỳ tình huống nào, ban đầu bạn nên kiểm tra mô hình xem có xung đột ràng buộc không. Thông thường, điều này có liên quan đến việc lựa chọn tỷ lệ hoặc chỉ số giới hạn không phù hợp.
Trong ví dụ trên, chỉ báo thể tích tối đa là 16 mét khối. m thay vì 32, bởi vì hạn chế như vậy mâu thuẫn với các chỉ số về số lượng chỗ ngồi tối thiểu, vì nó sẽ tương ứng với số lượng 16,5 mét khối. m.
Kết luận
Dựa trên điều này, tùy chọn “Tìm kiếm giải pháp” trong Excel sẽ giúp giải quyết các vấn đề cụ thể khá khó hoặc không thể giải quyết theo những cách thông thường. Khó khăn khi áp dụng phương pháp này là ban đầu tùy chọn này bị ẩn, đó là lý do tại sao hầu hết người dùng không biết đến sự hiện diện của nó. Ngoài ra, hàm khá khó học và sử dụng nhưng nếu nghiên cứu đúng cách sẽ mang lại lợi ích to lớn và thuận tiện cho việc tính toán.