Original link: https://editor.leonh.space/2023/solver/
In this article, I will become a little teacher of LibreOffice to introduce you to the solver, and I will explain it with practical examples below.
In a self-checkout machine that can change money, assuming that the money to be changed is 12,345, then we can expect that customers should not want to receive more than a thousand ten-yuan and five-yuan coins in change, but rather some Large-denomination banknotes plus a few coins, the question is, what kind of currency combination should our self-checkout machine provide to meet the expectations of customers?
For the above question, we can express it in LibreOffice Calc as follows:
in:
- The amount of change is 12,345, which is a prerequisite that cannot be violated. No matter how the combination of change is put together, the amount must be 12,345. This undeniable condition is called a hard constraint.
- In the currency denomination table, there are the quantity of each currency, and the amount multiplied by the denomination. The bottom column is the total input amount, which should be equal to 12,345, and the total currency quantity. For now, fill in 0 first.
- The so-called expectations of customers, specifically, is to keep the amount of currency as small as possible, and do not give coins to those who can give big bills. Our goal is to change the amount of various denominations arbitrarily, so that the total amount is 12,345, and the amount of currency is as large as possible. few.
After understanding the above three points, let’s first come to the result obtained by the wisdom of workers:
In the picture above, the customer received a currency combination of 12 thousand-yuan banknotes, 3 hundred-yuan banknotes, 4 ten-yuan coins, and 1 five-yuan coin, totaling 20 currencies.
Then use the solver to do calculations, open the LibreOffice Calc menu bar “Tools”, “Solver”, and enter some cell parameters:
in:
- The target cell is “C17”, which is the total amount of currency, and the result is expected to be “Minimum”.
- The variable cell is “C5:C15”, which is the quantity of each denomination.
- The hard constraint is “D17 = D2”, that is, the change amount should be 12,345.
Additionally, the solver has some options:
Here we have set the variable to be non-negative and the variable to be an integer. Other options can be played by yourself.
After the option is confirmed, you can press “Solve”, and the result of the solution is as follows:
As a result of the solver, the customer got a currency combination of 6 2,000-yuan bills, 1 200-yuan bill, 1 100-yuan bill, 2 20-yuan coins, and 1 5-yuan coin, totaling 11 currencies.
Compared with the original worker’s wisdom, isn’t the guest happier?
The above example is demonstrated by LibreOffice Calc, but the solver also exists in other spreadsheet applications. It is called “solver” in Gnumeric, and “planning solver” in Excel. Many people write examples of planning solvers on the Internet.
In addition to this simple example, the solver can also be applied to things such as schedules, class schedules, scheduling, and path planning, and such requirements often require the use of more professional solvers, such as Timefold, etc., in the future Have the opportunity to introduce again.
This article is transferred from: https://editor.leonh.space/2023/solver/
This site is only for collection, and the copyright belongs to the original author.