Home-theater-designers
Excel rất có khả năng khi bạn có tất cả dữ liệu cần thiết cho các tính toán của mình.
Nhưng sẽ không tốt nếu nó có thể giải quyết các biến không xác định ?
Với Goal Seek và phần bổ trợ Solver, nó có thể. Và chúng tôi sẽ chỉ cho bạn cách thực hiện. Đọc tiếp để có hướng dẫn đầy đủ về cách giải một ô duy nhất với Goal Seek hoặc một phương trình phức tạp hơn với Solver.
Cách sử dụng tính năng tìm kiếm mục tiêu trong Excel
Goal Seek đã được tích hợp sẵn trong Excel. Nó nằm dưới Dữ liệu tab, trong Những gì nếu phân tích thực đơn:
Đối với ví dụ này, chúng tôi sẽ sử dụng một bộ số rất đơn giản. Chúng tôi có ba phần tư doanh số bán hàng và mục tiêu hàng năm. Chúng ta có thể sử dụng Goal Seek để tìm ra những con số cần có trong Q4 để đạt được mục tiêu.
Như bạn có thể thấy, tổng doanh số hiện tại là 114.706 chiếc. Nếu chúng ta muốn bán 250.000 vào cuối năm, chúng ta cần bán bao nhiêu trong Q4? Tìm kiếm Mục tiêu của Excel sẽ cho chúng ta biết.
Dưới đây là cách sử dụng Goal Seek, theo từng bước:
- Nhấp chuột Dữ liệu> Phân tích điều gì xảy ra> Tìm kiếm mục tiêu . Bạn sẽ thấy cửa sổ này:
- Đặt phần 'bằng' của phương trình của bạn trong Đặt ô đồng ruộng. Đây là con số mà Excel sẽ cố gắng tối ưu hóa. Trong trường hợp của chúng tôi, đó là tổng số doanh số bán hàng của chúng tôi trong ô B5.
- Nhập giá trị mục tiêu của bạn vào Giá trị đồng ruộng. Chúng tôi đang tìm kiếm tổng số 250.000 đơn vị được bán, vì vậy chúng tôi sẽ đặt '250.000' vào trường này.
- Cho Excel biết biến nào cần giải quyết trong Bằng cách thay đổi ô đồng ruộng. Chúng tôi muốn xem doanh số bán hàng của chúng tôi trong Q4 cần phải như thế nào. Vì vậy, chúng tôi sẽ yêu cầu Excel giải quyết ô D2. Nó sẽ trông như thế này khi nó sẵn sàng hoạt động:
- Đánh VÂNG để giải quyết cho mục tiêu của bạn. Khi nó có vẻ tốt, chỉ cần đánh VÂNG . Excel sẽ cho bạn biết khi Goal Seek tìm ra giải pháp.
- Nhấp chuột VÂNG một lần nữa và bạn sẽ thấy giá trị giải phương trình của bạn trong ô bạn đã chọn Bằng cách thay đổi ô .
Trong trường hợp của chúng tôi, giải pháp là 135.294 đơn vị. Tất nhiên, chúng tôi có thể tìm thấy điều đó bằng cách trừ tổng số đang chạy cho mục tiêu hàng năm. Nhưng Goal Seek cũng có thể được sử dụng trên ô đã có dữ liệu trong đó . Và điều đó hữu ích hơn.
Lưu ý rằng Excel sẽ ghi đè dữ liệu trước đó của chúng tôi. Đó là một ý tưởng tốt để chạy Tìm kiếm Mục tiêu trên bản sao dữ liệu của bạn . Bạn cũng nên ghi chú vào dữ liệu đã sao chép của mình rằng dữ liệu được tạo bằng Goal Seek. Bạn không muốn nhầm lẫn nó với dữ liệu hiện tại, chính xác.
máy tính của tôi windows 10 có tương thích không
Vì vậy, Goal Seek là một tính năng Excel hữu ích , nhưng nó không phải là tất cả ấn tượng. Chúng ta hãy xem xét một công cụ thú vị hơn nhiều: trình bổ sung Solver.
Trình giải quyết của Excel làm được gì?
Tóm lại, Solver giống như một phiên bản đa biến của Goal Seek . Nó sử dụng một biến mục tiêu và điều chỉnh một số biến khác cho đến khi nó nhận được câu trả lời bạn muốn.
Nó có thể giải quyết giá trị lớn nhất của một số, giá trị nhỏ nhất của một số hoặc một số chính xác.
Và nó hoạt động trong các ràng buộc, vì vậy nếu không thể thay đổi một biến hoặc chỉ có thể thay đổi trong một phạm vi được chỉ định, Solver sẽ tính đến điều đó.
Đó là một cách tuyệt vời để giải quyết nhiều biến không xác định trong Excel. Nhưng việc tìm kiếm và sử dụng nó không hề đơn giản.
Hãy xem cách tải bổ trợ Solver, sau đó chuyển sang cách sử dụng Solver trong Excel 2016.
Cách tải phần bổ trợ Solver
Excel không có Solver theo mặc định. Đây là một phần bổ trợ, vì vậy, giống như các tính năng Excel mạnh mẽ khác, bạn phải tải nó trước. May mắn thay, nó đã có trên máy tính của bạn.
Hướng tới Tệp> Tùy chọn> Bổ trợ . Sau đó bấm vào Đi kế bên Quản lý: Phần bổ trợ Excel .
Nếu menu thả xuống này cho biết điều gì đó khác với 'Excel Add-Ins', bạn cần phải thay đổi nó:
Trong cửa sổ hiện ra, bạn sẽ thấy một vài tùy chọn. Đảm bảo hộp bên cạnh Phần bổ trợ Solver được kiểm tra và nhấn VÂNG .
Bây giờ bạn sẽ thấy Người giải quyết nút trong Phân tích nhóm của Dữ liệu chuyển hướng:
Nếu bạn đã sử dụng Công cụ phân tích dữ liệu , bạn sẽ thấy nút Phân tích dữ liệu. Nếu không, Solver sẽ tự xuất hiện.
Bây giờ bạn đã tải bổ trợ, hãy xem cách sử dụng nó.
Cách sử dụng Solver trong Excel
Có ba phần đối với bất kỳ hành động nào của Bộ giải: mục tiêu, các ô biến và các ràng buộc. Chúng tôi sẽ đi qua từng bước.
- Nhấp chuột Dữ liệu> Bộ giải . Bạn sẽ thấy cửa sổ Tham số bộ giải bên dưới. (Nếu bạn không thấy nút bộ giải, hãy xem phần trước về cách tải bổ trợ Bộ giải.)
- Đặt mục tiêu ô của bạn và cho Excel biết mục tiêu của bạn. Mục tiêu nằm ở đầu cửa sổ Bộ giải và nó có hai phần: ô mục tiêu và lựa chọn tối đa hóa, thu nhỏ hoặc một giá trị cụ thể. Nếu bạn chọn Max , Excel sẽ điều chỉnh các biến của bạn để có được số lớn nhất có thể trong ô mục tiêu của bạn. Min ngược lại: Solver sẽ giảm thiểu số mục tiêu. Giá trị của cho phép bạn chỉ định một số cụ thể để Solver tìm kiếm.
- Chọn các ô biến mà Excel có thể thay đổi. Các ô biến được đặt với Bằng cách thay đổi các ô biến đồng ruộng. Bấm vào mũi tên bên cạnh trường, sau đó bấm và kéo để chọn các ô mà Bộ giải sẽ làm việc với. Lưu ý rằng đây là những tất cả các ô điều đó có thể khác nhau. Nếu bạn không muốn một ô thay đổi, đừng chọn nó.
- Đặt các ràng buộc trên nhiều biến hoặc biến riêng lẻ. Cuối cùng, chúng ta đi đến các ràng buộc. Đây là nơi Solver thực sự mạnh mẽ. Thay vì thay đổi bất kỳ ô biến nào thành bất kỳ số nào nó muốn, bạn có thể chỉ định các ràng buộc phải được đáp ứng. Để biết chi tiết, hãy xem phần về cách thiết lập các ràng buộc bên dưới.
- Khi tất cả thông tin này đã có, hãy nhấn Giải quyết để nhận được câu trả lời của bạn. Excel sẽ cập nhật dữ liệu của bạn để bao gồm các biến mới (đây là lý do tại sao chúng tôi khuyên bạn nên tạo bản sao dữ liệu của mình trước).
Bạn cũng có thể tạo báo cáo mà chúng ta sẽ xem xét ngắn gọn trong ví dụ về Trình giải quyết của chúng tôi bên dưới.
Cách đặt Ràng buộc trong Bộ giải
Bạn có thể nói với Excel rằng một biến phải lớn hơn 200. Khi thử các giá trị biến khác nhau, Excel sẽ không dưới 201 với biến cụ thể đó.
Để thêm một ràng buộc, hãy nhấp vào Thêm vào bên cạnh danh sách ràng buộc. Bạn sẽ nhận được một cửa sổ mới. Chọn ô (hoặc các ô) được giới hạn trong Tham chiếu ô , sau đó chọn một toán tử.
Dưới đây là các toán tử có sẵn:
- <= (ít hơn hoặc bằng)
- = (tương đương với)
- => (lớn hơn hoặc bằng)
- NS (phải là số nguyên)
- là (phải là 1 hoặc 0)
- Tất cả đều khác
Tất cả đều khác là một chút khó hiểu. Nó chỉ định rằng mọi ô trong phạm vi mà bạn chọn Tham chiếu ô phải là một số khác. Nhưng nó cũng chỉ định rằng chúng phải nằm trong khoảng từ 1 đến số ô. Vì vậy, nếu bạn có ba ô, bạn sẽ kết thúc bằng các số 1, 2 và 3 (nhưng không nhất thiết phải theo thứ tự đó)
Cuối cùng, thêm giá trị cho ràng buộc.
Điều quan trọng cần nhớ là bạn có thể chọn nhiều ô cho Tham chiếu Ô. Ví dụ: nếu bạn muốn sáu biến có giá trị trên 10, bạn có thể chọn tất cả chúng và nói với Solver rằng chúng phải lớn hơn hoặc bằng 11. Bạn không phải thêm ràng buộc cho mỗi ô.
Bạn cũng có thể sử dụng hộp kiểm trong cửa sổ Bộ giải chính để đảm bảo rằng tất cả các giá trị mà bạn không chỉ định ràng buộc đều không âm. Nếu bạn muốn các biến của mình trở thành số âm, hãy bỏ chọn hộp này.
Một ví dụ về bộ giải
Để xem tất cả những điều này hoạt động như thế nào, chúng tôi sẽ sử dụng phần bổ trợ Solver để tính toán nhanh. Đây là dữ liệu mà chúng tôi đang bắt đầu:
Trong đó, chúng tôi có năm công việc khác nhau, mỗi công việc trả một tỷ lệ khác nhau. Chúng tôi cũng có số giờ một công nhân lý thuyết đã làm ở mỗi công việc đó trong một tuần nhất định. Chúng tôi có thể sử dụng phần bổ trợ Solver để tìm ra cách tối đa hóa tổng số tiền thanh toán trong khi vẫn giữ các biến nhất định trong một số ràng buộc.
Dưới đây là các ràng buộc mà chúng tôi sẽ sử dụng:
- Không có việc làm có thể giảm xuống dưới bốn giờ.
- Công việc 2 phải là hơn tám giờ .
- Công việc 5 phải là ít hơn mười một giờ .
- Tổng số giờ làm việc phải là bằng 40 .
Có thể hữu ích khi viết ra các ràng buộc của bạn như thế này trước khi sử dụng Solver.
Đây là cách chúng tôi thiết lập điều đó trong Solver:
Đầu tiên, hãy lưu ý rằng Tôi đã tạo một bản sao của bảng vì vậy chúng tôi không ghi đè lên bản gốc chứa giờ làm việc hiện tại của chúng tôi.
Và thứ hai, hãy xem rằng các giá trị trong các ràng buộc lớn hơn và nhỏ hơn là một cao hơn hoặc thấp hơn hơn những gì tôi đã đề cập ở trên. Đó là bởi vì không có tùy chọn lớn hơn hoặc nhỏ hơn. Chỉ có lớn hơn-hoặc-bằng-và nhỏ hơn-hoặc-bằng-với.
Hãy đánh Giải quyết và hãy xem chuyện gì xảy ra.
Solver tìm thấy một giải pháp! Như bạn có thể thấy ở bên trái của cửa sổ trên, thu nhập của chúng tôi đã tăng 130 đô la. Và tất cả các ràng buộc đã được đáp ứng.
cách đặt mật khẩu trên ổ đĩa flash
Để giữ các giá trị mới, hãy đảm bảo Giải pháp Keep Solver được kiểm tra và đánh VÂNG .
Tuy nhiên, nếu bạn muốn biết thêm thông tin, bạn có thể chọn một báo cáo từ phía bên phải của cửa sổ. Chọn tất cả các báo cáo mà bạn muốn, cho Excel biết liệu bạn có muốn chúng được phác thảo hay không (tôi khuyên bạn nên làm như vậy) và nhấn VÂNG .
Các báo cáo được tạo trên các trang tính mới trong sổ làm việc của bạn và cung cấp cho bạn thông tin về quá trình mà phần bổ trợ Solver đã trải qua để nhận được câu trả lời của bạn.
Trong trường hợp của chúng tôi, các báo cáo không thú vị lắm và không có nhiều thông tin thú vị ở đó. Nhưng nếu bạn chạy một phương trình Solver phức tạp hơn, bạn có thể tìm thấy một số thông tin báo cáo hữu ích trong các trang tính mới này. Chỉ cần nhấp vào + ở bên cạnh bất kỳ báo cáo nào để biết thêm thông tin:
Các tùy chọn nâng cao của Solver
Nếu bạn không biết nhiều về thống kê, bạn có thể bỏ qua các tùy chọn nâng cao của Solver và chỉ chạy nó như hiện tại. Nhưng nếu bạn đang chạy các phép tính lớn và phức tạp, bạn có thể muốn xem xét chúng.
Rõ ràng nhất là phương pháp giải quyết:
Bạn có thể chọn giữa GRG Nonlinear, Simplex LP và Evolution. Excel cung cấp một lời giải thích đơn giản về thời điểm bạn nên sử dụng từng cái. Một lời giải thích tốt hơn đòi hỏi một số kiến thức về thống kê và hồi quy.
Để điều chỉnh các cài đặt bổ sung, chỉ cần nhấn vào Tùy chọn cái nút. Bạn có thể cho Excel biết về tính tối ưu của số nguyên, đặt các giới hạn thời gian tính toán (hữu ích cho các bộ dữ liệu lớn) và điều chỉnh cách các phương pháp giải GRG và Evolution thực hiện các phép tính của chúng.
Một lần nữa, nếu bạn không biết bất kỳ điều nào trong số này có nghĩa là gì, đừng lo lắng về nó. Nếu bạn muốn biết thêm về phương pháp giải quyết nào sẽ sử dụng, Engineer Excel có bài báo hay sẽ mang lại cho bạn . Nếu bạn muốn có độ chính xác tối đa, Tiến hóa có lẽ là một cách tốt để đi. Chỉ cần lưu ý rằng nó sẽ mất một thời gian dài.
Tìm kiếm và giải quyết mục tiêu: Đưa Excel lên cấp độ tiếp theo
Bây giờ bạn đã cảm thấy thoải mái với những kiến thức cơ bản về giải các biến không xác định trong Excel, một thế giới tính toán bảng tính hoàn toàn mới sẽ mở ra cho bạn.
Goal Seek có thể giúp bạn tiết kiệm thời gian bằng cách thực hiện một số phép tính nhanh hơn và Solver bổ sung một lượng lớn sức mạnh cho Khả năng tính toán của Excel .
Nó chỉ là một vấn đề để làm cho thoải mái với họ. Bạn càng sử dụng chúng, chúng sẽ càng trở nên hữu ích hơn.
Bạn có sử dụng Goal Seek hoặc Solver trong bảng tính của mình không? Bạn có thể cung cấp những mẹo nào khác để nhận được câu trả lời tốt nhất từ chúng? Chia sẻ suy nghĩ của bạn trong phần bình luận bên dưới!
Đăng lại Đăng lại tiếng riu ríu E-mail 5 mẹo để nạp đầy năng lượng cho máy VirtualBox Linux của bạnBạn mệt mỏi với hiệu suất kém do các máy ảo cung cấp? Đây là những gì bạn nên làm để tăng hiệu suất VirtualBox của mình.
Đọc tiếp Chủ đề liên quan- Năng suất
- Bảng tính
- Microsoft Excel
- Mẹo Microsoft Office
Dann là một nhà tư vấn chiến lược nội dung và tiếp thị, người giúp các công ty tạo ra nhu cầu và khách hàng tiềm năng. Anh ấy cũng viết blog về chiến lược và tiếp thị nội dung tại dannalbright.com.
Xem thêm từ Dann AlbrightTheo dõi bản tin của chúng tôi
Tham gia bản tin của chúng tôi để biết các mẹo công nghệ, đánh giá, sách điện tử miễn phí và các ưu đãi độc quyền!
Bấm vào đây để đăng ký