Автор:	© Никита Цейковец
Дата публикации:	27.06.2010

Excel способен не только представлять информацию в виде электронных таблиц и считать по чётко указанным формулам, как умный калькулятор, но и самостоятельно осуществлять решение довольно сложных задач. Например, отдельных уравнений, систем уравнений и других. Для этого служит функция "Подбор параметра" и специальная надстройка "Поиск решения".

Подбор параметра

Подбор параметра - это инструмент, с помощью которого, можно осуществлять решение простейших уравнений, например, таких как "x+3=7" или "256/x = 16", посредством подбора значения переменной.

Для этого следует в одной из ячеек указать любое число, а в другой написать в формульном виде уравнение, для которого следует подобрать решение, причём в место переменной следует указать первую ячейку с числом. После этого в меню "Сервис" надо выбрать пункт "Подбор параметра". В открывшемся диалоге в первом поле указывается адрес ячейки с уравнением, во втором значение, которое принимает выражение с участием искомой переменной, а в третьем адрес ячейки, где располагается некоторое число, посредством изменения которого и будет осуществляться процесс подбора параметра. После нажатия на кнопку "ОК" в течение нескольких секунд Excel выведет информацию о результатах выполнения процедуры подбора, а после закрытия этого информационного окна в первой ячейке, где было написано произвольное число, будет выведено значение переменной, максимально удовлетворяющее условиям.

Практический пример:

Требуется решить уравнение 5688/x = 711. Для этого в ячейке A1 указывается произвольное число, например, 3, а в ячейку B1 вписывается исходное уравнение в формульном виде, где переменная заменена на адрес ячейки с произвольным числом:
=5688/A1
Далее следует вызвать диалог подбора параметра, выбрав пункт "Подбор параметра" в меню "Сервис". Здесь в первом поле ("Установить в ячейке") следует указать адрес уравнения, то есть B2. Причём, если перед вызовом этого диалога установить системный фокус в данную ячейку, то её адрес будет подставлен уже автоматически. Во втором поле ("Значение") надо указать то значение уравнения, которое оно принимает в соответствие с условиями, то есть 711. В третьем поле ("Изменяя значение ячейки") надо указать адрес ячейки, из которой будут подставляться произвольные числа для подбора, то есть в данном случае это A1. После нажатия на кнопку "ОК" Excel выведет информацию о завершении процесса подбора. В этом окне следует нажать на кнопку "ОК", чтобы принять результаты операции. После закрытия окна, в ячейке A1 будет содержаться то значение переменной, при котором выражение из ячейки B1 принимает указанное значение, то есть искомое значение x (x = 8).

Следует учитывать тот факт, что Excel применяет численные, а не аналитические методы вычисления, поэтому результат данной операции может быть не абсолютно точным, а лишь максимально приближенным к правильному. К тому же если процесс подбора параметра останавливается сразу после нахождения хотя бы одного корня уравнения, то есть если оно имеет более двух корней, то Excel найдёт только один из них максимально близкий к указанному в начале произвольному числу. Например, для уравнения x^2 = 4, Excel во-первых, найдёт только один корень: либо 2, либо -2 (в зависимости от числа, введённого в ячейке для подбора), а во-вторых, это будет не ровно 2 по абсолютному значению, а число, несколько отличающееся от ±2 (например: 2,00000180757296).

Это объясняется тем, что в процессе выполнения некоторых операций по подбору числа, Excel имеет дело с бесконечными дробями. Однако память компьютера не бесконечна, поэтому многие числа в компьютерном представлении часто имеют малые погрешности. По этой причине, большинство арифметических операций, выглядящих одинаково на бумаге, могут несколько отличаться в каком-то дальнем разряде. Например, если последовательно выполнить на компьютере операцию деления единицы на тройку, а потом умножения получившегося результата на ту же тройку, то без задействования дополнительных аппроксимирующих функций на выходе мы не получим единицу. Так как частное 0,(3) будет представлено в конечном виде, то есть на какой-то тройке после запятой компьютер всё-таки завершит его написания из-за того, что ячейки компьютерной памяти, отведённые на это число, закончатся. После чего умножение на три будет происходить уже не с числом 0,(3), а с конечным числом, состоящим из нуля целых и определённого количества троек после запятой. Поэтому результатом расчёта выражения 1/3*3 будет не 1, а ноль и много девяток в дробной части. В Excel имеется механизм аппроксимации, поэтому достаточно простое выражение 1/3*3 будет сосчитано корректно, но на более сложных расчётах небольшие погрешности всё-таки неизбежны.

Поиск решения

Часто функционала подбора параметра не хватает. Для более сложных расчётов в Excel есть ещё один инструмент - "Поиск решения". Этот инструмент отчасти похож на Подбор параметра, но является более сложным и мощным по своим возможностям, так как он позволяет подбирать не один, а группу из нескольких параметров.

Следует отметить, что при инсталлировании приложения Microsoft Office Excel данная надстройка не устанавливается. Поэтому пункт "Поиск решения" в меню "Сервис" может отсутствовать. Для установки данной надстройки MS Excel следует в меню "Сервис" выбрать пункт "Надстройки", далее в открывшемся диалоге в списке доступных надстроек перейти на "Поиск решения" и отметить его флажком, после чего следует нажать на кнопку "ОК" и согласиться с запросом на установку дополнительного компонента. При этом может понадобиться дистрибутив Microsoft Office. После завершения установки, в меню "Сервис" должен появиться пункт "Поиск решения".

Чтобы воспользоваться этой функцией Excel следует выполнить следующие действия:

• Произвести первичный ввод данных на листе в соответствие с постановленной задачей.
• Вызвать надстройку, нажав пункт "Поиск решения" в меню "Сервис".
• В открывшемся диалоге в поле "Установить целевую ячейку" следует ввести адрес ячейки, значение которой необходимо минимизировать, максимизировать или рассчитать.
• В следующем поле, представляющем из себя группу из трёх радиокнопок, надо выбрать тип операции, которую необходимо применить к целевой ячейке: установить равным минимальному, максимальному или конкретному значению.
• В следующем поле по умолчанию стоит ноль. Это означает, что формулу необходимо минимизировать, максимизировать или приравнять к нулю, то есть сделать так, чтобы её значение стало как можно ближе к нулю. Вместо нуля можно указать любое другое число.
• В следующем поле надо указать диапазон изменяемых ячеек. Их можно ввести вручную, или нажать на идущую следом кнопку "Предположить", тогда Excel автоматически впишет в это поле максимально подходящие ячейки. Как правила, в не очень сложных случаях, предположения Excel оказываются верными.
• Далее следует нажать на кнопку "Выполнить" или просто нажать клавишу Enter.

Для решения некоторых задач может потребоваться указать область изменения каждого или некоторых неизвестных параметров. Это задаётся в том же диалоговом окне нажатием на кнопку "Добавить". После этого откроется вспомогательное диалоговое окно "Добавление ограничения", содержащее набор из трёх элементов:

1. В первом поле "Ссылка на ячейку" указывается адрес ячейки, на которую накладывается дополнительное ограничение.
2. В следующем поле из списка надо выбрать тип ограничения, где кроме "меньше равно", "равно" и "больше равно", можно так же задать целочисленность значения или двоичную форму его представления.
3. В третьем поле "Ограничение" указывается конкретное число или адрес ячейки со значением которой следует сравнивать значение параметра.

Для добавления ещё одного ограничения, следует здесь же нажать на кнопку "Добавить". После ввода всех необходимых ограничений следует нажать на клавишу "ОК".

В основном диалоге Поиска решения сразу после кнопке "Предположить" идёт список, в котором перечислены все введённые дополнительные ограничения. Если требуется отредактировать какое-либо ограничение, то сначала его следует выбрать в этом списке, а потом клавишей Tab перейти на кнопку "Изменить".

Практический пример:

Требуется решить систему из двух уравнений: x+y = 2 и x-y = 0. Для этого в ячейках A1 и A2 указываем произвольные числа, например, нули. В Ячейке B1 указываем в формульном виде первое уравнение, подставив вместо x ячейку A1, а вместо y ячейку A2:
=A1+A2
В ячейку B2 аналогично вписываем второе уравнение системы:
=A1-A2
Теперь устанавливаем курсор на ячейку B2 и в меню "Сервис" выбираем пункт "Поиск решения". Здесь в первом поле "Установить целевую ячейку" уже автоматически указан адрес $B$1. В группе радиокнопок следует выбрать вариант "значению", так как в данном случае нас интересует именно поиск конкретного значения переменных. В следующем поле, где по умолчанию указан 0, следует написать 2, так как именно это значение принимает первое уравнение из системы, содержащиеся в ячейке B1. Далее следует нажать на кнопку "Предположить"и проверить правильность указания изменяемых ячеек в поле перед этой кнопкой, там должны быть указаны ячейки A1 и A2. Теперь нам требуется добавить дополнительные ограничения, так как мы имеем дело с системой уравнений. Для этого нажимаем на кнопку "Добавить" и в открывшемся диалоге в поле "Ссылка на ячейку" указываем адрес второго уравнения, то есть B2, в поле со списком возможных вариантов ограничения выбираем обычное равенство, а в третьем поле "Ограничение" указываем значение второго уравнения, то есть 0. После этого нажимаем на кнопку "ОК" и возвращаемся в основной диалог Поиска решения. Здесь проходим до кнопки "Выполнить". Через несколько секунд будет выведено окно с информацией о завершении процесса поиска решения, где следует нажать на кнопку "ОК". После этого в ячейке A1 можно увидеть найденное значение x, а в ячейке A2 найденное значение y (x = 1, y = 1).

Разумеется, и в случае работы с Поиском решения также возможны неточности в нахождении правильных значений переменных по вышеизложенным причинам.