Everything in the world is changing, and this happens regardless of our desire. This indisputable truth is especially well known to computer users, whose software has taken the habit of radically updating with amazing frequency. Fortunately, office suites do not suffer too much, although there are exceptions among them.
What is the importance of office software packages?
Any office software is a convenient tool created for working with large databases, the number of auxiliary elements in which has increased significantly. Now, with the help of visualization tools that appeared in new versions of such programs, it has become much easier to work. A new search filter has appeared, significantly accelerating the work with a large flow of information. And Microsoft Excel 2010 itself is much faster.
It would seem that just recently secretaries mastered the wisdom of MS Office 2007, as the triumphant release of Office 2010 took place, which added a headache to the unfortunate. But it should not be considered that the new version of the program “throws” to its users only difficulties.
An example of this is “finding a solution” in Excel 2010. This add-in is not only useful, but also helps to make your work with the spreadsheet editor much more productive, allowing you to solve much more complex tasks. It is especially convenient for optimization, which is relevant for many modern companies.
Why exactly the 2010 version?
As for Excel of this particular version, even more significant changes have occurred with it. In particular, a lot of errors in the formulas were fixed, which is why quite serious errors in the calculations often appeared in previous releases of the program. It is not worth mentioning that the slightest miscalculation in setting the size of the prize is fraught with very unpleasant consequences.
Thanks to the new “ribbon” interface, in which it is possible to display a preliminary view of charts and graphs before they are directly inserted into the table, it becomes easier for the user to compose even complex documents of a professional level.
In addition, new types of formulas have been introduced into the current version of this spreadsheet editor that will prove most useful to accountants and economists. This circumstance, again, shows the "reverence" of Microsoft towards corporate users. Given that the situations below are specific to them, there is nothing surprising in this.
Important! If you haven’t applied “solution search” in Excel 2010 before, then this add-in must be installed separately.
How to install it?
Fortunately, this is a snap. If you use Excel 2003 and older, then for this you need to go to the menu item "Service", and then select the item "Add-ons" there. And where in Excel is the “search for solutions” if we are talking about later and modern versions of the program?
In the case of using Excel from the 2007 version, the “Solution Search” button can be found in the “Data” tab.
How to work with it?
Despite the apparent volume of explanations, this add-on works quite logically, and for its development you do not need to be a computer genius. In order for you to fully understand the principle of its application, we will consider it with a simple example.
So, how does the “solution search” work in Excel 2010? Suppose that you are faced with a task - the distribution of bonuses in your organization. For simplicity of solving the problem, suppose that you have to distribute the bonus among all employees of a branch. The size of the bonus budget is 100 thousand rubles. This can be done proportionally by distributing the size of the bonus payment to the wage of each individual employee.
Where does the work begin?
Of course, the work will begin with the development of the table, the introduction of all the required data and formula expressions. As a result, the total value of the entire bonus amount will be considered.
It is important to know that the target cell (suppose it will be C8) was associated with the section that will change (E2, for example). Thus, in the range C2-C7, additional formulas will be located by which you will calculate the size of the bonus payment for each employee.
After that, the “search for solution” add-in is launched in Excel 2010, after which the required values are set in the window that opens. Please note that in different versions of the office suite the appearance of these dialog boxes can vary greatly, so you will have to deal with the situation yourself. Fortunately, there are no cardinal differences, so you won’t have to figure it out for a long time.
What options are available in the dialog box?
To facilitate your work, we must talk about the values that are generally available in a particular operating range.
The first is the target cell itself. Note! Unlike some other operations, which may involve the use of several fields at once for data output, in this case there can only be one!
Secondly, be sure to keep in mind that there may be several options for optimization. Pay attention to the maximum or minimum possible final value, as well as to the specific result. If you need the latter option, you should accurately indicate your preferred result in the input field.
It should also be remembered that both individual fields and their entire range can act as variable cells. In fact, it is from this range that the program takes the final value, comparing it with the options for the source data.
How to add restrictions?
If you need to add any restrictions, then you need to use the "Add" button. Note that when setting such values, you need to be extremely careful. Since in Excel “Search for a solution” (examples for which we are considering) is used in fairly responsible operations, it is extremely important to obtain the most correct values.
By the way, these very results depend on limitations. Keep in mind that you can set them not only for some individual cells, but also for their entire ranges. What options for signs and formulas can I use?
You may use the signs: "=", "> =", "<=". In addition, the following formulas are allowed: “Integer”, “Bin” (binary or integer value), as well as “Times”. Important! The latter option allows the use of all different values. This is available only in versions Excel 2010 and higher, which is another reason for switching to them. In these office software packages, the “search for the best” solution in Excel is not only faster, but also much better.
If we are talking about calculating the premium, then the coefficient should be strictly positive. You can set this parameter by several methods at once. It is easy to perform this operation using the "Add" button. In addition, you can select the “Make variables non-negative non-negative variables” checkbox. Where can I find this option in older versions of the program?
If you use Excel 2007 and older, then it can be found by clicking on the “Options” button, which contains the item “Solution Search Options”.
We are looking for a finished result
To search for a ready-made solution, you just need to click on the “Run” button (or “Find a solution”). After that, the “Solution Search Results” dialog box appears.
If you are satisfied with the final answer, then you just need to click on the "OK" button. After that, the answer you like will be recorded in the table. In the case when the obtained value is somewhat at variance with your preferences, you just need to click on the “Cancel” button. Then the table will immediately return to its original value, after which you will continue to search for the optimal solution.
Important! If you have somehow changed the source data, then this decision will have to be performed first.
Where else can the Excel "search for solution" add-in come in handy? Let's look at another example.
Cost minimization
As we said above, this function can be used to optimize production processes. Let's try to reduce the costs of a company that is engaged in low-rise construction. There is a company itself, as well as three suppliers who are engaged in the supply of building materials.
Of course, all the costs of its construction will be included in the cost of the commissioned facility, and therefore, in the interests of the enterprise, choose the supplier whose work will be the cheapest. What data should be hammered into the “search for solution” of MS Excel?
The costs of construction materials, the needs for them at the construction site, as well as the costs of transporting materials should be indicated, taking into account each pair “Supplier-buyer”. The target cell should indicate the amount of total transportation costs .
If you did everything right, the “solution search” function in Excel will allow you to create the most profitable strategy for the construction business, which will bring you maximum income. Good luck