Solver đã là một phần bổ trợ miễn phí kể từ những ngày của Lotus 1-2-3
Excel không phải là chương trình bảng tính đầu tiên. Lotus 1-2-3 không phải là chương trình bảng tính đầu tiên. Chương trình bảng tính đầu tiên là VisiCalc vào năm 1979. Được phát triển bởi Dan Bricklin và Bob Frankston, VisiCalc được xuất bản bởi Dan Fylstra. Ngày nay, Dan điều hành Frontline Systems. Công ty của ông đã viết Solver được sử dụng trong Excel. Nó cũng đã phát triển một bộ phần mềm phân tích toàn bộ hoạt động với Excel.
Nếu bạn có Excel, bạn có Solver. Nó có thể không được kích hoạt, nhưng bạn có nó. Để bật Solver trong Excel, hãy nhấn alt = "" + T sau đó nhấn I. Thêm dấu kiểm bên cạnh Solver.
Để sử dụng thành công Solver, bạn phải xây dựng một mô hình trang tính có ba yếu tố:
- Phải có một ô Mục tiêu. Đây là ô mà bạn muốn thu nhỏ, tối đa hóa hoặc đặt thành một giá trị cụ thể.
- Có thể có nhiều ô đầu vào. Đây là một cải tiến cơ bản so với Goal Seek, chỉ có thể xử lý một ô đầu vào.
- Có thể có những ràng buộc.
Mục tiêu của bạn là xây dựng các yêu cầu về lịch trình cho một công viên giải trí. Mỗi nhân viên sẽ làm việc liên tục năm ngày và sau đó có hai ngày nghỉ. Có bảy cách khả thi khác nhau để lên lịch cho ai đó trong năm ngày liên tục và hai ngày nghỉ. Chúng được hiển thị dưới dạng văn bản trong A4: A10. Các ô màu xanh lam trong B4: B10 là các ô đầu vào. Đây là nơi bạn chỉ định có bao nhiêu người làm việc cho mỗi lịch trình.
Ô Mục tiêu là tổng số tiền lương mỗi tuần, được hiển thị trong B17. Đây là phép toán đơn giản: Tổng số người từ B11 nhân với mức lương 68 đô la mỗi người mỗi ngày. Bạn sẽ yêu cầu Solver tìm cách giảm thiểu tiền lương hàng tuần.
Hộp màu đỏ hiển thị các giá trị sẽ không thay đổi. Đây là số lượng người bạn cần làm việc công viên vào mỗi ngày trong tuần. Bạn cần ít nhất 30 người vào những ngày cuối tuần bận rộn - nhưng chỉ cần 12 người vào thứ Hai và thứ Ba. Các ô màu cam sử dụng SUMPRODUCT để tính toán số lượng người sẽ được lên lịch mỗi ngày dựa trên các đầu vào trong các ô màu xanh lam.
Các biểu tượng trong hàng 15 cho biết bạn cần thêm người hay ít người hơn hoặc liệu bạn có chính xác số lượng người phù hợp hay không.
Đầu tiên, tôi đã cố gắng giải quyết vấn đề này mà không có Solver. Tôi đi cùng 4 nhân viên mỗi ngày. Điều đó thật tuyệt, nhưng tôi không có đủ người vào Chủ nhật. Vì vậy, tôi bắt đầu tăng lịch làm việc để có thêm nhân viên vào Chủ nhật. Tôi đã kết thúc với một thứ hoạt động hiệu quả: 38 nhân viên và 2,584 đô la tiền lương hàng tuần.
Nhấp vào biểu tượng Bộ giải trên tab Dữ liệu. Nói với Solver rằng bạn đang cố gắng đặt mức lương trong B17 ở mức tối thiểu. Các ô đầu vào là B4: B10.
Ràng buộc thuộc loại rõ ràng và không quá rõ ràng.
Ràng buộc rõ ràng đầu tiên là D12: J12 phải> = D14: J14.
Tuy nhiên, nếu bạn cố gắng chạy Solver ngay bây giờ, bạn sẽ nhận được kết quả kỳ lạ khi bạn có số người theo tỷ lệ nhỏ và có thể là số âm những người làm việc theo lịch trình nhất định.
Mặc dù có vẻ rõ ràng là bạn không thể thuê 0,39 người, nhưng bạn cần thêm các ràng buộc để nói với Solver rằng B4: B10 là> = 0 và B4: B10 là số nguyên.
Chọn Simplex LP làm phương pháp giải và chọn Giải. Trong giây lát, Solver đưa ra một giải pháp tối ưu.
Solver đã tìm ra cách để trang trải cho công viên giải trí sử dụng 30 nhân viên thay vì 38. Khoản tiết kiệm mỗi tuần là 544 đô la - hoặc hơn 7.000 đô la trong suốt mùa hè.
Lưu ý năm sao dưới đây Nhân viên Cần thiết. Lịch trình mà Solver đề xuất đáp ứng nhu cầu chính xác của bạn trong năm trong bảy ngày. Sản phẩm phụ là bạn sẽ có nhiều nhân viên vào thứ Tư và thứ Năm hơn mức bạn thực sự cần.
Tôi có thể hiểu cách Solver đưa ra giải pháp này. Bạn cần rất nhiều người vào thứ bảy, chủ nhật và thứ sáu. Một cách để thu hút mọi người đến đó vào ngày đó là cho họ nghỉ thứ Hai và thứ Ba. Đó là lý do tại sao Solver cho 18 người nghỉ thứ Hai và thứ Ba.
Nhưng chỉ vì Solver đưa ra một giải pháp tối ưu không có nghĩa là không có các giải pháp tối ưu tương đương khác.
Khi tôi chỉ phỏng đoán về nhân sự, tôi không thực sự có một chiến lược tốt.
Bây giờ Solver đã đưa cho tôi một trong những giải pháp tối ưu, tôi có thể đội mũ logic của mình. Có 28 nhân viên trong độ tuổi đại học vào thứ Tư và thứ Năm khi bạn chỉ cần 15 hoặc 18 nhân viên sẽ dẫn đến rắc rối. Sẽ không có đủ để làm. Thêm vào đó, với số lượng nhân viên chính xác vào năm ngày, bạn sẽ phải gọi cho ai đó ngoài giờ nếu người khác gọi đến ốm.
Tôi tin tưởng Solver rằng tôi cần có 30 người để làm việc này. Nhưng tôi cá rằng tôi có thể sắp xếp lại những người đó để phù hợp với lịch trình và cung cấp một khoảng đệm nhỏ vào những ngày khác.
Ví dụ: cho ai đó nghỉ thứ Tư và thứ Năm cũng đảm bảo rằng người đó làm việc vào thứ Sáu, thứ Bảy và Chủ nhật. Vì vậy, tôi đã chuyển một số công nhân theo cách thủ công từ hàng Thứ Hai, Thứ Ba sang hàng Thứ Năm Thứ Tư. Tôi tiếp tục cắm các kết hợp khác nhau theo cách thủ công và đưa ra giải pháp này có chi phí trả lương tương tự như Solver nhưng vô hình tốt hơn. Tình trạng thừa nhân viên hiện tồn tại trong bốn ngày thay vì hai ngày. Điều đó có nghĩa là bạn có thể xử lý các cuộc gọi từ Thứ Hai đến Thứ Năm mà không cần phải gọi cho ai đó từ cuối tuần của họ.
Thật tệ khi tôi có thể đưa ra một giải pháp tốt hơn Solver? Không. Thực tế là tôi sẽ không thể đạt được giải pháp này nếu không sử dụng Solver. Sau khi Solver đưa cho tôi một mô hình giảm thiểu chi phí, tôi có thể sử dụng logic về các yếu tố vô hình để giữ nguyên bảng lương.
Nếu bạn cần giải quyết các vấn đề phức tạp hơn Solver có thể xử lý, hãy xem các trình giải Excel cao cấp có sẵn từ Frontline Systems: http://mrx.cl/solver77.
Cảm ơn Dan Fylstra và Frontline Systems về ví dụ này. Walter Moore minh họa tàu lượn XL.
Xem video
- Solver đã là một phần bổ trợ miễn phí kể từ những ngày của Lotus 1-2-3
- Solver là sản phẩm của người sáng lập Visicorp Dan Fylstra
- Bộ giải trong Excel của bạn là một phiên bản nhỏ hơn của bộ giải công suất lớn
- Tìm hiểu thêm về bộ giải chuyên nghiệp: http://mrx.cl/solver77
- Để cài đặt Solver, gõ alt = "" + T rồi chọn I. Chọn Solver.
- Bộ giải sẽ được tìm thấy ở bên phải của tab Dữ liệu
- Bạn muốn có một ô mục tiêu mà bạn đang cố gắng thu nhỏ hoặc tối đa hóa.
- Bạn có thể chỉ định nhiều ô đầu vào.
- Bạn có thể chỉ định các ràng buộc, bao gồm một số ràng buộc mà bạn không mong đợi:
- Không có nửa người: Sử dụng INT cho Số nguyên
- Người giải quyết sẽ tìm ra một giải pháp tối ưu, nhưng có thể có những giải pháp khác là ràng buộc
- Khi bạn nhận được giải pháp Solver, bạn có thể tinh chỉnh nó.
Bản ghi video
Học Excel từ podcast, tập 2036 - Intro to Solver!
Được rồi, tôi đang podcasting toàn bộ cuốn sách này, hãy nhấp vào chữ "i" ở góc trên bên phải để truy cập danh sách phát, nơi bạn có thể phát tất cả video!
Chào mừng bạn trở lại với netcast, tôi là Bill Jelen. Gần đây, chúng tôi đã nói về một số phân tích What-If, chẳng hạn như Goal Seek, bạn biết đấy, với một ô đầu vào mà bạn đang thay đổi, nhưng nếu bạn có thứ gì đó phức tạp hơn thì sao? Có một công cụ tuyệt vời tên là Solver, Solver đã có từ lâu, tôi đảm bảo nếu bạn có Excel và đang chạy trên Windows, bạn có Solver, chỉ có thể là nó chưa được bật. Vì vậy, để bật tính năng này, bạn phải truy cập alt = "" T rồi đến I, vậy là T cho Tom, tôi cho kem và chọn hộp này cho Bộ giải, nhấp vào OK và sau vài giây, bạn sẽ có tab Solver ở đây ở phía bên tay phải. Được rồi, và chúng tôi sẽ thiết lập một mô hình ở đây mà người giải có thể giải được, chúng tôi có một công viên giải trí, chúng tôi đang cố gắng hẹn gặp bao nhiêu nhân viên. Mọi người đều làm việc năm ngày liên tục, vì vậy ở đó 'thực sự là bảy lịch trình khả thi khi bạn nghỉ, Chủ nhật Thứ Hai, Thứ Hai Thứ Ba, Thứ Ba Thứ Tư. Chúng tôi phải tìm ra bao nhiêu nhân viên để đưa vào mỗi lịch trình đó.
Và vì vậy, chỉ cần một phép toán nhỏ đơn giản ở đây, thực hiện một số SUMPRODUCTs, số lượng nhân viên nhân với Chủ nhật để tính xem có bao nhiêu người ở đó vào Chủ nhật, Thứ Hai, Thứ Ba, Thứ Tư. Và những gì chúng tôi đã học được thông qua việc vận hành công viên giải trí này là chúng tôi cần rất nhiều người vào Thứ Bảy và Chủ Nhật. Thứ bảy và chủ nhật 30 người, trong tuần thứ hai, thứ ba, loại chậm thì 12 nhân viên mới làm được. Được rồi, bạn biết đấy, chỉ cần đến đây và bắt đầu tìm hiểu, cố gắng tìm ra con số phù hợp, bạn có thể tiếp tục cắm mọi thứ vào, nhưng với bảy lựa chọn khác nhau, sẽ mất mãi mãi, được rồi.
Bây giờ trong Solver, những gì chúng ta có là, chúng ta có một loạt các ô đầu vào và trong phiên bản miễn phí của Solver, tôi nghĩ bạn có thể có, có phải là một trăm không? Tôi không biết, có một số con số, và nếu bạn phải vượt qua con số đó, có một Trình giải quyết cao cấp mà bạn có thể nhận được từ Hệ thống tiền tuyến. Được rồi, chúng tôi có một số ô đầu vào, chúng tôi có một số ô ràng buộc và sau đó bạn phải đưa tất cả xuống con số cuối cùng. Vì vậy, trong trường hợp của tôi, tôi đang cố gắng giảm thiểu tiền lương mỗi tuần, vì vậy con số xanh đó là những gì tôi muốn thử và tối ưu hóa, được rồi, vì vậy đây là những gì chúng ta sẽ làm!
Solver, đây là ô mục tiêu, đó là ô màu xanh lá cây và tôi muốn đặt nó thành giá trị tối thiểu, tìm ra nhân sự mà tôi nhận được giá trị tối thiểu, bằng cách thay đổi các ô màu xanh lam này. Và đây là các ràng buộc, được rồi, vì vậy ràng buộc đầu tiên là tổng lịch trình phải> = phần màu đỏ, và chúng ta có thể làm tất cả những điều đó như một ràng buộc duy nhất. Hãy xem điều này thú vị như thế nào, tất cả các ô này phải> = các ô tương ứng này ở đây, tuyệt vời, nhấp vào Thêm, được rồi, nhưng sau đó có những thứ khác mà bạn sẽ không nghĩ đến. Ví dụ: tại thời điểm này, Solver có thể quyết định rằng tốt nhất nên có 17 người trong lịch trình này, 43 người trong lịch trình và -7 người trong lịch trình này. Được rồi, vì vậy chúng tôi phải nói với Solver rằng các ô đầu vào này phải là một số nguyên, nhấp vào Thêm. Ngoài ra, chúng ta không thể để ai đó không xuất hiện,và họ sẽ trả lại tiền lương cho chúng tôi, phải không? Vì vậy, chúng tôi sẽ nói những ô này phải> = 0, nhấp vào Thêm, chúng tôi quay lại bây giờ, chúng tôi có ba ràng buộc ở đó.
Có ba cách khác nhau để giải, và cách này tuân theo phép toán tuyến tính, vì vậy chúng ta chỉ có thể đi Simplex LP. Nếu cách này không hoạt động, thì hãy thử hai cách còn lại, tôi đã gặp trường hợp Simplex nói rằng nó không thể tìm ra giải pháp và một trong hai cách còn lại hoạt động. Frontline Systems có các hướng dẫn tuyệt vời về Solver, tôi chỉ đang cố gắng hướng dẫn bạn qua bài đầu tiên của bạn ở đây hôm nay, tôi không tuyên bố mình là một chuyên gia về Solver. Một khi tôi có một Solver không hoạt động, và tôi đã gửi một ghi chú đến Frontline Systems, và thật tuyệt vời, tôi đã nhận lại được bức thư dài 5 trang tuyệt vời này, phải không, từ chính Dan Fylstra, chủ tịch của Solver! Và nó bắt đầu: "Bill thân mến, rất vui khi được nghe từ bạn!" Và sau đó tiếp tục trong 4,9 trang, tất cả đều ở trên đầu tôi, được rồi. Nhưng bạn biết đấy, tôi biết đủ về Solver để vượt qua điều này, được thôi,vì vậy chúng tôi sẽ nhấp vào đây trên Giải quyết, nó đã tìm thấy giải pháp, "Tất cả các Ràng buộc và điều kiện tối ưu đều được thỏa mãn." Tôi sẽ giữ điều đó, tôi có thể tạo một số báo cáo, không cần phải làm điều đó ngay bây giờ. Ồ, tôi thực sự có thể lưu một kịch bản, hôm qua tôi đã làm vui về các kịch bản, có lẽ Solver sẽ có thể tạo một kịch bản mới cho tôi, vì vậy chúng tôi sẽ nhấp vào OK.
Được rồi, và chắc chắn nó đã giúp chúng tôi tiết kiệm tiền, trước đây chúng tôi đã viết 2584, và bây giờ nó đã đưa chúng tôi xuống năm 2040. Vì vậy, chúng tôi cần nhiều người nghỉ vào Thứ Hai và Thứ Ba, được rồi, một số người, 2 người nghỉ vào Thứ Năm Thứ Năm, và rồi thứ sáu thứ bảy. Chà, điều này thật tuyệt vời, tôi sẽ không bao giờ ngẫu nhiên nghĩ ra bộ câu trả lời này, được rồi, nhưng điều đó có nghĩa đó là câu trả lời hay nhất? Vâng, nó có nghĩa là đó là mức lương tối thiểu, nhưng tôi có thể đưa ra một loạt câu trả lời khác mà vẫn có mức lương tối thiểu này. Có những cách khác để làm điều đó, đó có thể là một lịch trình tốt hơn một chút. Chẳng hạn như hiện tại, chúng tôi có 28 người vào Thứ Tư và Thứ Năm, khi chúng tôi chỉ cần 15 và 18, đó là rất nhiều người. Hãy nghĩ về những người làm việc tại các công viên giải trí, đây là những đứa trẻ đại học về nhà nghỉ ngơi,điều này sẽ trở nên rắc rối nếu chúng ta có thêm nhiều người. Và vào thứ Hai, thứ Ba, chúng tôi đã chết ngay cả, chính xác nơi chúng tôi muốn. Vì vậy, điều đó có nghĩa là nếu bất kỳ ai tôi sắp bị ốm, bây giờ chúng tôi sẽ phải, bạn biết đấy, gọi cho ai đó và trả tiền cho họ thời gian rưỡi, bởi vì họ đã làm việc năm ngày khác.
Được rồi, chỉ với một số phép toán nhỏ đơn giản ở đây, nếu tôi lấy 8 từ Thứ Hai, Thứ Ba và thành 10, và lấy 8 đó và cộng chúng vào Thứ Năm Thứ Tư, được rồi. Bây giờ tôi có một giải pháp Solver với cùng một câu trả lời, năm 2040, họ đã có đúng số người. Tôi chỉ cần cân đối lịch trình, và bây giờ chúng tôi có thêm 8 người, 8 người phụ, 3 người phụ và 2 người phụ, và chính xác những gì chúng ta cần vào cuối tuần, đó là kịch bản đầy đủ của nhân viên. Đối với tôi, điều này tốt hơn một chút so với những gì Solver nghĩ ra, điều đó có nghĩa là trình giải quyết đó đã thất bại? Không, hoàn toàn không, bởi vì tôi sẽ không bao giờ kết thúc được điều này nếu không có Solver. Khi Solver đã cho tôi câu trả lời, vâng, tôi đã có thể điều chỉnh nó một chút và đến đó, được thôi. Mẹo # 37, “40 Mẹo Excel hay nhất mọi thời đại”, gần cuối 40 mẹo đầu tiên đó, phần giới thiệu nhỏ tuyệt vời về Solver.Hướng dẫn cho tất cả các podcast trong loạt bài này ở đây, “MrExcel XL - 40 Mẹo Excel hay nhất mọi thời đại”, bạn có thể có sách điện tử chỉ với $ 10, sách in với $ 25, nhấp vào chữ “i” ở trên cùng -cách tay phải!
Được rồi, tóm tắt lại: Solver, nếu bạn đang sử dụng phiên bản Windows của Excel, Lotus 1-2-3, thì nó ở đó, nó được tạo bởi người sáng lập Visicorp Dan Fylstra. Đây là phiên bản miễn phí của các bộ giải hạng nặng, đây là liên kết để xem các bộ giải hạng nặng, sẽ nằm trong phần nhận xét trên YouTube. Có thể chúng chưa được cài đặt, alt = "" TI, đánh dấu chọn Bộ giải, nhìn ở bên phải tab Dữ liệu để tìm Bộ giải. Được rồi, bạn phải có một ô mục tiêu mà bạn đang cố gắng thu nhỏ hoặc tối đa hóa hoặc đặt thành một giá trị, một dải ô đầu vào. Chỉ định các ràng buộc, bao gồm cả những điều không mong đợi, như tôi phải nói "Không có nửa người" và "Không có người tiêu cực". Solver sẽ tìm ra giải pháp tối ưu, nhưng có thể có những giải pháp khác có liên quan và bạn có thể điều chỉnh nó để có được giải pháp tốt hơn.
Được rồi, bạn đã có rồi, tôi muốn cảm ơn bạn đã ghé qua, chúng ta sẽ gặp lại bạn vào lần sau với một netcast khác từ!
Tải tập tin
Tải xuống tệp mẫu tại đây: Podcast2036.xlsx
