"Finding solutions" in Excel 2010. Where is the add-in "Solution search" Excel

Everything in the world is changing, and this happens regardless of our desire. This immutable truth is especially well known to computer users, who have taken the habit of cardinally updating with an amazing frequency. Fortunately, office packages do not suffer much, although there are exceptions among them.

What is the importance of software packages for the office?

Any office software is a handy tool designed to work with large databases, the number of auxiliary elements in which has increased significantly. Now with the help of visualization tools, which appeared in new versions of such programs, it became much easier to work. There is a new search filter that significantly speeds up the work with a large flow of information. And Microsoft Excel 2010 itself works much faster.

It would seem that quite recently the secretaries mastered the wisdom of MS Office 2007, how the triumphal release of Office 2010 took place, which added to the unfortunate headache. But do not assume that the new version of the program "throws up" its users only difficulties.

An example of this is the "search solution" in Excel 2010. This add-in is not only useful, it also helps make your work with the spreadsheet editor more productive, allowing you to solve much more complex tasks. Especially it is convenient for optimization, which is relevant for many modern companies.

Why the 2010 version?

As for Excel of this particular version, even more significant changes took place with it. In particular, a lot of errors in the formulas were corrected, because of which in the past issues of the program quite often there were quite gross errors in the calculations. It is not necessary to mention that the slightest miscalculation when setting out the size of the premium is fraught with very unpleasant consequences.

Thanks to the new "ribbon" interface, in which it is possible to display the preview of diagrams and graphs before they are directly inserted into the table, it becomes easier for the user to compose even complex professional documents.

In addition, the current version of this table editor has introduced new types of formulas that will prove to be most useful for accountants and economists. This circumstance again shows Microsoft's "reverence" towards corporate users. Given that the following situations are typical for them, there is nothing surprising in this.

Important! If you have not previously used the "search solution" in Excel 2010, then this add-on needs to be installed separately.

How to install it?

Fortunately, it's not difficult to do this. If you are using Excel 2003 or later, you need to go to the "Tools" menu item, and then select "Add-ons" there. And where in Excel is the "search for solutions", if it is a question of later and modern versions of the program?

In the case of applying Excel from the 2007 version, the button "Finding solutions" can be found in the "Data" tab.

How to work with this?

Despite the seeming length of explanations, this superstructure works quite logically, and for its mastering one does not need to be a computer genius. So that you fully understand the principle of its application, we will consider it in the simplest example.

So, how does the "search solution" work in Excel 2010? Suppose, that before you there is a task - distribution of the premium in your organization. For the simplicity of solving the problem, let's assume that you have to distribute the bonus between all employees of some branch. The size of the bonus budget is 100 thousand rubles. To do this, you can proportionally distribute the size of the bonus payment to the size of the salary of each individual employee.

How does the work begin?

Of course, that work will begin with the development of the table, entering into it 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 (assuming it to be C8) was associated with the section that will change (E2, for example). Thus, additional formulas will be located in the C2-C7 range, through which you will calculate the size of the bonus payment for each employee.

After that, the "solution search" add-in is started in Excel 2010, after which the required values are displayed in the opened window. 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 on your own. Fortunately, there are no cardinal differences, so you do not have to understand much.

What options are available in the dialog box?

To facilitate your work, we need to talk about the values that are generally available in a specific working range.

First, this is the target cell itself. Note! Unlike some other operations, which may imply the use of several fields at once to output data, in this case it can be only one!

Secondly, be sure to keep in mind that the optimization options can be several at once. Pay attention to the maximum or minimum possible total value, as well as the specific result. If you need the latter option, you should specify the preferred result in the input field.

Also, remember that in the role of mutable cells can act as separate fields, and a whole range of them. In fact, it is from this range that the program takes the final value, comparing it with the variants of the original data.

How do I add constraints?

If you need to add some restrictions, then you need to use the "Add" button. Note that when setting such values, you must be extremely careful. Since in Excel "Finding solutions" (examples for which we are considering) is used in fairly critical operations, it is extremely important to obtain the most correct values.

By the way, it is from the constraints that these very results depend. Keep in mind that you can specify them not only for some individual cells, but for their entire ranges. In this case, what variants of signs and formulas can I use?

You can use the signs: "=", "> =", "<=". In addition, formulas are allowed: "Target", "Bean" (binary or integer value), and also "Time". Important! The latter option allows the use of all different values. This is available only in versions of Excel 2010 and above, which is another reason for the transition to them. In these office software packages, "searching for the optimal" solution in Excel is performed not only faster, but also much better.

If we are talking about calculating premiums, then the coefficient should be strictly positive. You can specify this parameter at once by several methods. It is easy to perform this operation by using the "Add" button. In addition, you can check the box "Make variables without restrictions nonnegative". Where can I find this option in older versions of the program?

If you use Excel 2007 or later, it is located by clicking on the "Options" button, which has the item "Solution search options".

We are looking for ready result

To search for a ready solution, you just need to click on the "Run" button (or "Find a solution"). After that, the "Solution search results" dialog box appears before you.

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 fixed in the table. In the case when the received value is in some ways at odds with your preferences, you just need to click on the "Cancel" button. Then the table will immediately return to the original value, after which you will continue to search for the optimal solution.

Important! If you at least somehow changed the original data, then this solution will have to be done first.

Where else can the add-in "search for solutions" Excel work? Let's look at another example.

Minimizing costs

As we said above, this function can be used to optimize production processes. Try to reduce the costs of the company, which deals with low-rise construction. There is a company, as well as three suppliers, which are engaged in the delivery of construction materials.

Of course, that in the cost of the delivered object all costs for its erection will be paid, and therefore it is in the interest of the enterprise to choose the supplier whose work will be cheaper. What data needs to be scored in the "search solution" MS Excel?

It is necessary to indicate the costs of construction materials, the requirements for them at the construction site, as well as the costs of transporting materials, taking into account each pair of "Supplier-Buyer". In the target cell, the sum of total transportation costs should be indicated.

If you did everything correctly, the "search solution" function in Excel will allow you to create the most profitable strategy of the construction business, which will bring you the maximum income. Good luck!