Одна из наиболее актуальных проблем компьютерного обучения – проблема отбора и использования педагогически целесообразных обучающих программ.

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

В данной статье я представила те задачи, решение которых с помощью MS EXCEL позволяет получить наглядное, доступное для понимания учащимися решение, показать его логику, рациональность. Попутно учащиеся получают устойчивые навыки работы с программой.

Нахождение корней уравнения с помощью подбора параметра

Пример 1.

Пусть известно, что в штате больницы состоит 6 санитарок, 8 медсестер, 10 врачей, 3 заведующих отделениями, главный врач, заведующий аптекой, заведующая хозяйством и заведующий больницей. Общий месячный фонд зарплаты составляет 1000 000 условных единиц. Необходимо определить, какими должны быть оклады сотрудников больницы.

Решение такой задачи можно искать методом перебора. Однако в лучшем случае на это уходит много времени. Можно предложить другой способ решения. В EXCEL он реализован как поиск значения параметра формулы, удовлетворяющего ее конкретному значению.

Построим модель решения этой задачи. За основу возьмем оклад санитарки, а остальные оклады будем вычислять, исходя из него: во столько-то раз или на столько-то больше. Говоря математическим языком, каждый оклад является линейной функцией от оклада санитарки: A i *С+В i , где С – оклад санитарки; А i и В i – коэффициенты, которые для каждой должности определяют следующим образом:

• медсестра получает в 1,5 раза больше санитарки (А 2 =1,5; В 2 =0);
• врач – в 3 раза больше санитарки (А 3 =3; В 3 =0);
• заведующий отделением – на 30 y.e. больше, чем врач (А 4 =3; B 4 =30);
• заведующий аптекой – в 2 раза больше санитарки (А 5 =2; В 5 =0);
• заведующий хозяйством – на 40 y.e. больше медсестры (А 6 =1,5; В 6 =40);
• заведующий больницей – на 20 y.e. больше главного врача (А 8 =4; В 8 =20);
• главный врач – в 4 раза больше санитарки (А 7 =4; В 7 =0);

Зная количество человек на каждой должности, нашу модель можно записать как уравнение: N 1 *(A 1 *C+B 1)+N 2 *(A 2 *C+B 2)+...+N 8 *(A 8 *C+B 8) = 1000000, где N 1 – число санитарок, N 2 – число медсестер и т.д.

В этом уравнении нам известны A 1 ...A 8 , B 1 ...B 8 и N 1 ...N 8 , а С неизвестно. Анализ уравнения показывает, что задача вычисления заработной платы свелась к решению линейного уравнения относительно С. Предположим, что зарплата у санитарки 150,00 y.e.

Введите исходные данные в рабочий лист электронной таблицы, как показано ниже.

Оклад мед. Работников
Должность			Зарплата	Количество сотрудников	Суммарная зарплата
Санитарка
Медсестра
Зав. отделением
Зав. аптекой
Главврач
Зав. больницей
Общий фонд равен

В столбце D вычислите заработную плату для каждой должности. Например, для ячейки D4 формула расчета имеет вид =B4*$D$3+C4.

В столбце F вычислите заработную плату всех работников данной должности. Например, для ячейки F3 формула расчета имеет вид =D3*E3.

В ячейке F11вычислите суммарный фонд заработной платы больницы. Рабочий лист электронной таблицы будет выглядеть, как показано ниже.

Оклад мед. Работников
Должность			Зарплата	Количество сотрудников	Суммарная зарплата
Санитарка
Медсестра
Зав. отделением
Зав. аптекой
Главврач
Зав. больницей
Общий фонд равен

Чтобы определите оклад санитарки так, чтобы расчетный фонд был равен заданному надо:

• Активизировать команду Подбор параметра во вкладке Данные / Работа с данными /Анализ «Что, если» ;
• В поле "Установить в ячейке" появившегося окна ввести ссылку на ячейку F11, содержащую формулу;
• В поле "Значение" набрать искомый результат 1000000;
• В поле "Изменяя значение ячейки" ввести ссылку на изменяемую ячейку D3 и щелкните на кнопке ОК.

Анализ задачи показывает, что с помощью Excel можно решать линейные уравнения. Конечно, такое уравнение может решить любой школьник. Однако, благодаря этому простому примеру стало, очевидным, что поиск значения параметра формулы, удовлетворяющего ее конкретному значению, – это не что иное, как численное решение уравнений. Другими словами, используя Excel, можно решать любые уравнения с одной переменной.

Задание для учащихся:

Составить несколько вариантов штатного расписания с использованием функции Подбор параметра и оформить ихв виде таблицы:

• Изменить количество сотрудников на различных должностях;
• Подобрать зарплату санитарки в новых условиях;
• Составить таблицу нескольких вариантов штатного расписания.

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

Пусть дано уравнение, записанное в виде x=F(x). Выбирают некоторое начальное приближение x 1 и подставляют его вместо x в F(x). Полученное значение x 2 =F(x 1) этой функции считают вторым приближением. Далее находят третье приближение по формуле x 3 =F(x 2) и так далее. Таким образом, получаем последовательность x 1 , x 2 , x 3 ,…, x n ,… чисел, имеющая предел α. Тогда если функция F(x) непрерывна, из равенства x n+1 =F(x n) получаем α=F(α). Это означает, что α является решением уравнения x=F(x).

Пример 2.

Пусть нам дан многочлен третьей степени:

x 3 -0,01x 2 -0,7044x+0,139104=0.

Так как мы ищем корни полинома третьей степени, то имеются не более трех вещественных корней. Для нахождения корней их первоначально надо локализовать, то есть найти интервалы, на которых они существуют. Такими интервалами локализации корней могут служить промежутки, на концах которых функция имеет противоположный знак. С целью нахождения интервалов, на концах которых функция изменяет знак, необходимо построить ее график или протабулировать ее. Составим таблицу значений функции на интервале [-1;1] с шагом 0,2. Для этого необходимо:

• Ввести в ячейку A2 значение -1, а в ячейку A3 значение -0,8.
• Выбрать диапазон A2:A3, расположить указатель мыши на маркере заполнения этого диапазона и протянуть его на диапазон A4:A12, аргумент протабулирован.
• В ячейку B2 ввести формулу: =A2^3-0,01*A2^2-0,7044*A2+0,139104
• Выбрать ячейку B2. Расположить указатель мыши на маркере заполнения этой ячейки и протянуть его на диапазон B3:B12. Функция также протабулирована.

Значение аргумента х	Значение функции у

Из таблицы видно, что полином меняет знак на интервалах [-1; -0,8], и , и поэтому на каждом из этих интервалов имеется свой корень. Так как полином третьей степени имеет не более трех корней, то они все локализованы.

Прежде чем приступить к нахождению корней при помощи подбора параметра, необходимо выполнить некоторую подготовительную работу:

• Установить точность, с которой находится корень. Корень при помощи подбора параметра находится методом последовательных приближений. Для этого в Настройке панели быстрого доступа / Другие команды, и на вкладке Формулы диалогового окна Параметры Exel задайте в Параметрах вычислений относительную погрешность и предельное число итераций равными 0,00001 и 1000, соответственно.
• Отвести на рабочем листе ячейку, например С2, под искомый корень. Эта ячейка будет играть двойную роль. До применения подбора параметра в ней находится начальное приближение к корню уравнения, а после применения – найденное приближенное значение корня.
• Корень при помощи подбора параметра находим методом последовательных приближений. Поэтому в ячейку C2 надо ввести значение, являющееся приближением к искомому корню. В нашем случае, первым отрезком локализации корня является [-1;-0,8]. Следовательно, за начальное приближение к корню разумно взять среднюю точку этого отрезка -0,9.
• Отвести ячейку, например D2, под функцию, для которой ведется поиск корня, причем вместо неизвестной у этой функции должна указываться ссылку на ячейку, отведенную под искомый корень. Таким образом, в ячейку D2 введите формулу: =C2^3-0,01*C2^2-0,7044*C2+0,139104

Аналогично надо поступить с двумя другими искомыми корнями:

• Отвести ячейку C8 под второй корень, ввести в нее начальное приближение 0,3, а в ячейку D8 ввести следующую формулу: =C8^3-0,01*C8^2-0,7044*C8+0,139104
• Отвести ячейку C10 под второй корень, ввести в нее начальное приближение 0,7, а в ячейку D10 ввести следующую формулу: =C10^3-0,01*C10^2-0,7044*C10+0,139104

Результаты выполненных действий приведены в таблице.

Значение х	Значение у	Начальное приближение до применения метода	Значение функции

Теперь можно переходить к нахождению первого корня уравнения:

Выберете команду Подбор параметра . На экране отобразится диалоговое окно Подбор параметра .

• В поле Установить в ячейке введите ссылку на ячейку D2. В этом поле дается ссылка на ячейку, в которой введена формула, вычисляющая значение левой части уравнения. Для нахождения корня с помощью подбора параметра уравнение надо представить в таком виде, чтобы его правая часть не содержала переменную.
• В поле Значение введите 0. Здесь указывается значение из правой части уравнения.
• В поле Изменяя значение ячейки введите C2. В данном поле приводится ссылка на ячейку, отведенную под переменную.
• Нажмите кнопку OK .

На экране отображается окно Результат подбора параметра с результатами работы команды Подбор параметра . Кроме того, рассматриваемое средство помещает найденное приближенное значение корня в ячейку C2. В данном случае оно равно -0,920. Аналогично в ячейках C8 и C10 находятся два оставшихся корня. Они равны 0,210 и 0,721.

Значение х	Значение у	Корень уравнения	Значение функции

Задание для учащихся:

Найти все корни уравнений

1. Х 3 -2,92Х 2 +1,4355Х+0,791136=0

2. Х 3 -2,56Х 2 -1,3251Х+4,395006=0

3. Х 3 +2,84Х 2 -5,6064Х-14,766336=0

Нахождение корней уравнения методом деления отрезка пополам

Краткая теория метода. Пусть непрерывная функция F(x) имеет значения разных знаков на концах отрезка , то есть F(a)F(b)<0.Тогда уравнение F(x)=0 имеет корень внутри этого отрезка. Отрезок отрезком локализации корня. Пусть c=(a+b)/2 – середина отрезка . Если F(a)F(c)<=0, то корень находится на отрезке , который берем за новый отрезок локализации корня. Если F(a)F(c)>0, то за новый отрезок локализации корня берем .Отметим, что новый отрезок локализации корня в два раза меньше первоначального. Процесс деления отрезка для локализации корня продолжаем до тех пор, пока его длина не станет меньше ε, точности нахождения корня. В этом случае любая точка отрезка локализации отличается от корня не более чем на ε/2.

Найдем корни уравнения x 2 –2=0 с точностью до 0,001 методом деления отрезка пополам. За первоначальный отрезок локализации корня выбран . Для реализации этого метода введите в ячейки рабочего листа формулы либо значения, приведенные ниже в таблице:

Ячейка	Формула или значение
	=(A3^2-2)*(C3^2-2)
	ЕСЛИ(B3–A3<$B$1;""Корень найден и равен "" & текст (C3;""0,000""); "" "")
	ЕСЛИ (D3<=0; A3;C3)
	ЕСЛИ(D3<=0; C3; B3)
	=(A4^2-2)*(C4^2-2)
	ЕСЛИ(B4-A4<$B$1; ""Корень найден и равен "" & текст(C4; ""0,000""); "" "")

Теперь осталось только выбрать диапазон A4:F4, расположить указатель мыши на маркере его заполнения и пробуксировать его вниз до тех пор, пока в столбце F не появится сообщение о том, что корень найден. В данном случае сообщение появится в ячейке F14, а значение корня с точностью до 0,001 равно 1,415.

Число шагов можно определить заранее и скопировать формулы в диапазон из необходимого числа строк. Число шагов до нахождения корня определяется по формуле: +1 (1), где [x] есть целая часть числа х, t – заданная точность.

В заключение отмечу, что в рассмотренном примере использовались:

• Операция конкатенации строк, которая объединяет несколько строк в одну (обозначается символом амперсанта &). При объединении двух строк вторая строка добавляется непосредственно в конец первой строки.
• Функция рабочего листа из категории функций по работе с текстом ТЕКСТ (TEXT). Данная функция преобразует значение в текст в заданном числовом формате.

Задание для учащихся:

Вычислить корень уравнения cosx = x на отрезке с точностью до 0,001. Число шагов для определения корня вычислить при помощи формулы (1).

Использование MS EXEL значительно расширяет круг задач, которые можно использовать в обучении. Это обусловлено возможностью передачи трудоемких операций компьютеру, например, при решении уравнений методами итераций и деления отрезка пополам.

Литература:

1. Информатика в школе / Под ред. Макаровой Н. В. – СПб: Питер Ком, 1999.
2. Символоков Л.В. Решение бизнес задач в Microsoft Office – М.: ЗАО "Издательство БИНОМ", 2001.
3. Шохолович В.Ф. Информационные технологии обучения. Информатика и образование. 1998. – №2.
4. Игнекова Г.С. Методические аспекты подготовки учителя информатики. Информатика и образование. 1998. – №3.

Micrisoft Office Excel 2007 – специальная программа Windows, позволяющая составлять различные таблицы с вводимыми данными. Более того, данная программа позволяет решать уравнения.

Открываем Excel 2007. Для наиболее простого решения уравнения воспользуйтесь функцией «поиск решений». Правда, во многих стандартных пакетах Office данная надстройка не установлена. Чтобы установить, откройте параметры Office Excel, которые находятся в правом нижнем углу всплывающего нижнего диалогового окна. В открывшемся меню кликаем в следующей последовательности: «надстройки» - «Поиск решения» - «перейти».

После перехода установите галочку рядом с пунктом «поиск решения» и нажмите OK.

Затем Excel выполнит настройку программы.

Затем, чтобы решить уравнение, введите его в поле листа. Пусть ваше уравнение с двумя переменными: F(x1,x2)=3×1+2×2 – max, в случае определенных ограничений:

• X1 - x2 ≥ -2
• 3×1 - 2×2 ≤ 6
• 2×1+3×2 ≥ 2
• X2 ≤ 3
• X1 ≥ 0
• X2 ≤ 0

Введите в колонку А таблицы Excel переменные х1 и x2. Затем выделите синим цветом поле, где расположены полученные значения переменных. Затем в колонке А введите саму функцию F(x1, x2)=. А справа от нее выделите красным цветом ту ячейку, в которой будет находиться значение данной функции.

Затем в красное поле введите само уравнение 3×1+2×2. Учтите, что х1 – ячейка В1, а х2 – ячейка В2.

Теперь введите в поле все ограничения.

Затем перейдите в раздел «поиск решений» (папка данные). Найдите поле «установить целевую ячейку», куда нужно поставить красную ячейку. Напротив «=» пишем максимальное значение.
В поле «изменяя ячейки» добавьте синие ячейки – х1, х2.

Если вы ввели все ограничения, проверьте их правильность, после чего нажмите кнопку «выполнить». В случае если все данные введены верно, то программа должна рассчитать неизвестные. В нашем случае х1=4, ч2=3 и F(x1,x2)=18. Уравнение решено.

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

Способ 1: матричный метод

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

14x1 +2x2 +8x4 =218
7x1 -3x2 +5x3 +12x4 =213
5x1 +x2 -2x3 +4x4 =83
6x1 +2x2 +x3 -3x4 =21

1. Заполняем матрицу числами, которые являются коэффициентами уравнения. Данные числа должны располагаться последовательно по порядку с учетом расположения каждого корня, которому они соответствуют. Если в каком-то выражении один из корней отсутствует, то в этом случае коэффициент считается равным нулю. Если коэффициент не обозначен в уравнении, но соответствующий корень имеется, то считается, что коэффициент равен 1 . Обозначаем полученную таблицу, как вектор A .



2. Отдельно записываем значения после знака «равно». Обозначаем их общим наименованием, как вектор B .



3. Теперь для нахождения корней уравнения, прежде всего, нам нужно отыскать матрицу, обратную существующей. К счастью, в Эксель имеется специальный оператор, который предназначен для решения данной задачи. Называется он МОБР . Он имеет довольно простой синтаксис:

   МОБР(массив)

   Аргумент «Массив» — это, собственно, адрес исходной таблицы.

   Итак, выделяем на листе область пустых ячеек, которая по размеру равна диапазону исходной матрицы. Щелкаем по кнопке «Вставить функцию» , расположенную около строки формул.



4. Выполняется запуск Мастера функций . Переходим в категорию «Математические» . В представившемся списке ищем наименование «МОБР» . После того, как оно отыскано, выделяем его и жмем на кнопку «OK» .



5. МОБР . Оно по числу аргументов имеет всего одно поле – «Массив» . Тут нужно указать адрес нашей таблицы. Для этих целей устанавливаем курсор в это поле. Затем зажимаем левую кнопку мыши и выделяем область на листе, в которой находится матрица. Как видим, данные о координатах размещения автоматически заносятся в поле окна. После того, как эта задача выполнена, наиболее очевидным было бы нажать на кнопку «OK» , но не стоит торопиться. Дело в том, что нажатие на эту кнопку является равнозначным применению команды Enter . Но при работе с массивами после завершения ввода формулы следует не кликать по кнопке Enter , а произвести набор сочетания клавиш Ctrl+Shift+Enter . Выполняем эту операцию.



6. Итак, после этого программа производит вычисления и на выходе в предварительно выделенной области мы имеем матрицу, обратную данной.



7. Теперь нам нужно будет умножить обратную матрицу на матрицу B , которая состоит из одного столбца значений, расположенных после знака «равно» в выражениях. Для умножения таблиц в Экселе также имеется отдельная функция, которая называется МУМНОЖ . Данный оператор имеет следующий синтаксис:

   МУМНОЖ(Массив1;Массив2)

   Выделяем диапазон, в нашем случае состоящий из четырех ячеек. Далее опять запускаем Мастер функций , нажав значок «Вставить функцию» .



8. В категории «Математические» , запустившегося Мастера функций , выделяем наименование «МУМНОЖ» и жмем на кнопку «OK» .



9. Активируется окно аргументов функции МУМНОЖ . В поле «Массив1» заносим координаты нашей обратной матрицы. Для этого, как и в прошлый раз, устанавливаем курсор в поле и с зажатой левой кнопкой мыши выделяем курсором соответствующую таблицу. Аналогичное действие проводим для внесения координат в поле «Массив2» , только на этот раз выделяем значения колонки B . После того, как вышеуказанные действия проведены, опять не спешим жать на кнопку «OK» или клавишу Enter , а набираем комбинацию клавиш Ctrl+Shift+Enter .



10. После данного действия в предварительно выделенной ячейке отобразятся корни уравнения: X1 , X2 , X3 и X4 . Они будут расположены последовательно. Таким образом, можно сказать, что мы решили данную систему. Для того, чтобы проверить правильность решения достаточно подставить в исходную систему выражений данные ответы вместо соответствующих корней. Если равенство будет соблюдено, то это означает, что представленная система уравнений решена верно.



Способ 2: подбор параметров

Второй известный способ решения системы уравнений в Экселе – это применение метода подбора параметров. Суть данного метода заключается в поиске от обратного. То есть, основываясь на известном результате, мы производим поиск неизвестного аргумента. Давайте для примера используем квадратное уравнение






Этот результат также можно проверить, подставив данное значение в решаемое выражение вместо значения x .

Способ 3: метод Крамера

Теперь попробуем решить систему уравнений методом Крамера. Для примера возьмем все ту же систему, которую использовали в Способе 1 :

14x1 +2x2 +8x4 =218
7x1 -3x2 +5x3 +12x4 =213
5x1 +x2 -2x3 +4x4 =83
6x1 +2x2 +x3 -3x4 =21


1. Как и в первом способе, составляем матрицу A из коэффициентов уравнений и таблицу B из значений, которые стоят после знака «равно» .



2. Далее делаем ещё четыре таблицы. Каждая из них является копией матрицы A , только у этих копий поочередно один столбец заменен на таблицу B . У первой таблицы – это первый столбец, у второй таблицы – второй и т.д.



3. Теперь нам нужно высчитать определители для всех этих таблиц. Система уравнений будет иметь решения только в том случае, если все определители будут иметь значение, отличное от нуля. Для расчета этого значения в Экселе опять имеется отдельная функция – МОПРЕД . Синтаксис данного оператора следующий:

   МОПРЕД(массив)

   Таким образом, как и у функции МОБР , единственным аргументом выступает ссылка на обрабатываемую таблицу.

   Итак, выделяем ячейку, в которой будет выводиться определитель первой матрицы. Затем жмем на знакомую по предыдущим способам кнопку «Вставить функцию» .



4. Активируется окно Мастера функций . Переходим в категорию «Математические» и среди списка операторов выделяем там наименование «МОПРЕД» . После этого жмем на кнопку «OK» .



5. Запускается окно аргументов функции МОПРЕД . Как видим, оно имеет только одно поле – «Массив» . В это поле вписываем адрес первой преобразованной матрицы. Для этого устанавливаем курсор в поле, а затем выделяем матричный диапазон. После этого жмем на кнопку «OK» . Данная функция выводит результат в одну ячейку, а не массивом, поэтому для получения расчета не нужно прибегать к нажатию комбинации клавиш Ctrl+Shift+Enter .



6. Функция производит подсчет результата и выводит его в заранее выделенную ячейку. Как видим, в нашем случае определитель равен -740 , то есть, не является равным нулю, что нам подходит.



7. Аналогичным образом производим подсчет определителей для остальных трех таблиц.



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



9. Теперь пора найти корни уравнения. Корень уравнения будет равен отношению определителя соответствующей преобразованной матрицы на определитель первичной таблицы. Таким образом, разделив поочередно все четыре определителя преобразованных матриц на число -148 , которое является определителем первоначальной таблицы, мы получим четыре корня. Как видим, они равны значениям 5 , 14 , 8 и 15 . Таким образом, они в точности совпадают с корнями, которые мы нашли, используя обратную матрицу в способе 1 , что подтверждает правильность решения системы уравнений.



Способ 4: метод Гаусса

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

14x1 +2x2 +8x3 =110
7x1 -3x2 +5x3 =32
5x1 +x2 -2x3 =17


1. Опять последовательно записываем коэффициенты в таблицу A , а свободные члены, расположенные после знака «равно» — в таблицу B . Но на этот раз сблизим обе таблицы, так как это понадобится нам для работы в дальнейшем. Важным условием является то, чтобы в первой ячейке матрицы A значение было отличным от нуля. В обратном случае следует переставить строки местами.



2. Копируем первую строку двух соединенных матриц в строчку ниже (для наглядности можно пропустить одну строку). В первую ячейку, которая расположена в строке ещё ниже предыдущей, вводим следующую формулу:

   B8:E8-$B$7:$E$7*(B8/$B$7)

   Если вы расположили матрицы по-другому, то и адреса ячеек формулы у вас будут иметь другое значение, но вы сможете высчитать их, сопоставив с теми формулами и изображениями, которые приводятся здесь.

   После того, как формула введена, выделите весь ряд ячеек и нажмите комбинацию клавиш Ctrl+Shift+Enter . К ряду будет применена формула массива и он будет заполнен значениями. Таким образом мы произвели вычитание из второй строки первой, умноженной на отношение первых коэффициентов двух первых выражений системы.



3. После этого копируем полученную строку и вставляем её в строчку ниже.



4. Выделяем две первые строки после пропущенной строчки. Жмем на кнопку «Копировать» , которая расположена на ленте во вкладке «Главная» .



5. Пропускаем строку после последней записи на листе. Выделяем первую ячейку в следующей строке. Кликаем правой кнопкой мыши. В открывшемся контекстном меню наводим курсор на пункт «Специальная вставка» . В запустившемся дополнительном списке выбираем позицию «Значения» .



6. В следующую строку вводим формулу массива. В ней производится вычитание из третьей строки предыдущей группы данных второй строки, умноженной на отношение второго коэффициента третьей и второй строки. В нашем случае формула будет иметь следующий вид:

   B13:E13-$B$12:$E$12*(C13/$C$12)

   После ввода формулы выделяем весь ряд и применяем сочетание клавиш Ctrl+Shift+Enter .



7. Теперь следует выполнить обратную прогонку по методу Гаусса. Пропускаем три строки от последней записи. В четвертой строке вводим формулу массива:

   Таким образом, мы делим последнюю рассчитанную нами строку на её же третий коэффициент. После того, как набрали формулу, выделяем всю строчку и жмем сочетание клавиш Ctrl+Shift+Enter .



8. Поднимаемся на строку вверх и вводим в неё следующую формулу массива:

   =(B16:E16-B21:E21*D16)/C16

   Жмем привычное уже нам сочетание клавиш для применения формулы массива.



9. Поднимаемся ещё на одну строку выше. В неё вводим формулу массива следующего вида:

   =(B15:E15-B20:E20*C15-B21:E21*D15)/B15

   Опять выделяем всю строку и применяем сочетание клавиш Ctrl+Shift+Enter .



10. Теперь смотрим на числа, которые получились в последнем столбце последнего блока строк, рассчитанного нами ранее. Именно эти числа (4 , 7 и 5 ) будут являться корнями данной системы уравнений. Проверить это можно, подставив их вместо значений X1 , X2 и X3 в выражения.



Как видим, в Экселе систему уравнений можно решить целым рядом способов, каждый из которых имеет собственные преимущества и недостатки. Но все эти методы можно условно разделить на две большие группы: матричные и с применением инструмента подбора параметров. В некоторых случаях не всегда матричные методы подходят для решения задачи. В частности тогда, когда определитель матрицы равен нулю. В остальных же случаях пользователь сам волен решать, какой вариант он считает более удобным для себя.

Надстройка Excel «Поиск решения» – это аналитический инструмент, который позволяет нам быстро и легко определить, когда и какой результат мы получим при определенных условиях. Возможности инструмента поиска решения намного выше, чем может предоставить «подбор параметра » в Excel.

Основные отличия между поиском решения и подбором параметра:

1. Подбор нескольких параметров в Excel.
2. Наложение условий ограничивающих изменения в ячейках, которые содержат переменные значения.
3. Возможность использования в тех случаях, когда может быть много решений одной задачи.

Примеры и задачи на поиск решения в Excel

Рассмотрим аналитические возможности надстройки. Например, Вам нужно накопить 14 000$ за 10 лет. На протяжении 10-ти лет вы хотите каждый год откладывать на депозитный счет в банке по 1000$ под 5% годовых. Ниже на рисунке построена таблица в Excel, по которой хорошо видно остаток накопленных средств на каждый год. Как видно при таких условиях депозитного счета и взносов накопления цель не будет достигнута даже через 10 лет. При решении данной задачи можно пойти двумя путями:

1. Найти банк, который предлагает более высокую процентную ставку по депозитам.
2. Увеличить размер ежегодных накопительных взносов на банковский счет.

Мы можем изменять переменные значения в ячейках B1 и B2 так, чтобы подобрать необходимые условия для накопления необходимой суммы денег.

Надстройка «Поиск решения» - позволяет нам одновременно использовать 2 этих варианта, чтобы быстро смоделировать наиболее оптимальные условия для достижения поставленной цели. Для этого:

Как видно программа немного увеличила процентную ставку и сумму ежегодных взносов.



Ограничение параметров при поиске решений

Допустим, вы пошли в банк с этой таблицей, но банк отказывается поднять Вам процентную ставку. В таком случаи нам нужно узнать, насколько нам придется повысить сумму ежегодных вложений. Мы должны установить ограничение на ячейку с одним переменным значением. Но перед началом измените значения в переменных ячейках на исходные: в B1 на 5%, а в B2 на -1000$. А теперь делаем следующее.

Значительная часть задач, которые решаются с помощью электронных таблиц, предполагают, что для обнаружения нужного результата у пользователя уже есть хоть какие-то исходные данные. Однако Exсel 2010 располагает необходимыми инструментами, с помощью которых можно решить эту задачу наоборот – подобрать нужные данные, чтобы получить необходимый результат.

«Поиск решения» и является одним из таких инструментов, максимально удобных для «задач оптимизации». И если ранее вам еще не приходилось его использовать, то сейчас самое время исправить это.

Итак – начинаем с установки данной надстройки (поскольку самостоятельно она не появится). К счастью сейчас сделать это можно достаточно просто и быстро – открываем меню «Сервис», а уже в нем «Надстройки»

Останется только в графе «Управление» указать «Надстройки Excel», а после нажать кнопочку «Перейти».

После этого несложного действия кнопка активации «Поиска решения» будет отображаться в «Данных». Как и показано на картинке

Давайте рассмотрим, как правильно используется поиск решений в Excel 2010, на нескольких простых примерах.

Пример первый .

Допустим, что вы занимаете пост начальника крупного отдела производства и необходимо правильно распределить премии сотрудникам. Допустим, общая сумма премий составляет 100 000 рублей, и необходимо, чтобы премии были пропорциональны окладам.

То есть, сейчас нам необходимо подобрать правильный коэффициент пропорциональности, чтобы определить размер премии относительно оклада.

В первую очередь необходимо быстро составить (если ее еще нет) таблицу, где будут хранится исходные формулы и данные, согласно которым и можно будет получить желаемый результат. Для нас этот результат – суммарная величина премии. А сейчас внимание – целевая ячейка С8 должна быть с помощью формул связана с искомой изменяемой ячейкой под адресом Е2. Это критично. В примере мы связываем их используя промежуточные формулы, которые и отвечают за высчитывание премии каждому сотруднику (С2:С7).

Теперь можно активировать «Поиск решений». Откроется новое окошко, в котором нам необходимо указать необходимые параметры.

Под «1 » обозначена наша целевая ячейка. Она может быть только одна.

«2 » — это возможные варианты оптимизации. Всего можно выбрать «Максимальное», «Минимальное» или «Конкретное» возможные значения. И если вам необходимо именно конкретное значение, то его нужно указать в соответствующей графе.

«3 » — изменяемых ячеек может быть несколько (целый диапазон или же отдельно указанные адреса). Ведь именно с ними и будет работать Excel, перебирая варианты так, чтобы получилось значение, заданное в целевой ячейке.

«4 » — Если понадобиться задать ограничения, то стоит воспользоваться кнопкой «Добавить», но мы это рассмотрим чуть позже.

«5 » — кнопка перехода к интерактивным вычислениям на основе заданной нами программы.

Но теперь вернемся к возможности изменять наше задание, воспользовавшись кнопкой «Добавить». Данный этап является довольно ответственным (не менее чем построение формул), поскольку именно ограничение позволяют получить правильный результат на выходе. Здесь все сделано максимально удобно, так что задать их вы сможете не только для всего диапазона сразу, но и для определенных ячеек.

Для этого можно использовать ряд определенных (и знакомых всем пользователям Excel 2010) знаков «=», «>=», «<=», а также варианты «цел» (от «целое»), «бин» («бинарное» или же «двоичное»), «раз» («все разные»).

Но в нашем примере ограничение может быть лишь одно – положительный коэффициент. Задать его, конечно, можно несколькими способами – либо используя «Добавить» (что называют «явно указать ограничение»), либо просто отметить действующей функцию «Сделать переменные без ограничений неотрицательными». Это можно сделать в надстройке «Поиск решения», нажав на кнопочку «Параметры».

Кстати, после подтверждения параметров и запуска программы (кнопочка «Выполнить»), вы сможете в таблице просмотреть полученный результат. Тогда программа продемонстрирует окошко «результатов поиска».

Если продемонстрированный результат полностью вам подходит, тогда останется только вновь подтвердить его (кнопочка «ОК»), что зафиксирует результат в вашей таблице. Если же что-то в расчетах вас не устраивает, то необходимо отменить результат (кнопочка «Отмена»), вернуться к предыдущему состоянию нашей таблицы и исправить допущенные ошибки.

Правильное решение задачи примера должно получиться вот таким

Очень важно — чтобы получить правильный результат даже при малейшем изменении исходных данных необходимо перезапустить «Поиск решений».

Чтобы более подробно взглянуть на то, как действует данная программа, давайте разберем еще один пример.

Допустим, вы являетесь владельцем крупного мебельного предприятия и необходимо наладить производство таким образом, чтобы получить максимально возможную прибыль. Вы производите только книжные полки, при этом всего двух моделей – «А» и «В», производство которых ограничивается исключительно наличием (или отсутствием) высококачественных досок, а также машинным временем (обработка на станке).

Модель «А» требует 3 м 3 досок, а модель «В» — на 1 м 3 больше (то есть – 4). От своих поставщиков вы за неделю получаете максимум 1700 м 3 досок. При этом модель «А» создается за 12 минут работы станка, а «В» — за 30 минут. Всего в неделю станок может работать не более 160 часов.

Вопрос – сколько всего изделий (и какой модели), должна выпускать фирма за неделю, чтобы получить максимально возможную прибыль, если полочка «А» дает 60 рублей прибыли, а «В» — 120?

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

Любым удобным способом запускаем наш «Поиск решений», вводим данные, производим настройку.

Итак, рассмотрим то, что мы имеем. В целевой ячейке F7 содержится формула, которая и рассчитает прибыль. Параметр оптимизации устанавливаем на максимум. Среди изменяемых ячеек у нас значится «F3:G3». Ограничения – все обнаруженные значения должны быть целыми числами, неотрицательными, общее количество потраченного машинного времени не превышает отметку 160 (наша ячейка D9), количество сырья не превышает 1700 (ячейка D8).

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

Активируем программу, и она подготавливает решение.

Впрочем, это не единственное решение и у вас вполне может выскочить другой результат. Это может произойти даже в том случае, если все данные были указаны верно и ошибок в формулах тоже не было

Да. Это может произойти даже в том случае, если мы сказали программе искать целое число. И если это вдруг произошло, то необходимо просто провести дополнительную настройку «Поиска решений». Открываем окно «Поиска решений» и входим в «Параметры».

Наш верхний параметр отвечает за точность. Чем он меньше, тем выше точность и в нашем случае это значительно повышает шансы получить целое число. Второй параметр («Игнорировать целочисленные ограничения») и дает ответ на вопрос, как мы смогли получить такой ответ с тем, что в запросе явно указали целое число. «Поиск решений» просто проигнорировал это ограничение в связи с тем, что так ему сказали расширенные настройки.

Так что будьте предельно внимательны в будущем.

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

Итак, строительная компания дает заказ на перевозку песка, который берется от 3 поставщиков (карьеров). Его необходимо доставить 5 разным потребителям (которыми выступают строительные площадки). Стоимость доставки груза включена в себестоимость объекта, так что наша задача обеспечить доставку груза на стройплощадки с минимальными затратами.

Мы имеем – запас песка в карьере, потребность стройплощадок в песке, затрату на транспортировку «поставщик-потребитель».

Необходимо найти схему оптимальной перевозки груза (куда и откуда), при которой общая затрата на перевозку была бы минимальной.

Серые ячейки нашей таблицы содержат формулы суммы по столбцам и строкам, а целевая ячейка – формула для общего подсчета затраты на доставку груза. Запускаем наш «Поиск решения» и вносим необходимые настройки

После этого приступаем к поиску решения этой задачки

Впрочем, не будем забывать, что достаточно часто транспортные задачи могут быть усложнены некоторыми дополнительными ограничителями. Допустим, возникло осложнение на дороге и теперь из карьера 2 просто технически невозможно доставить груз на стройплощадку 3. Чтобы учесть это, необходимо просто дописать дополнительное ограничение «$D$13=0». И если теперь запустить программу, то результат будет иным

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

Вот и все по данному вопросу.

Мы выполнили поиск решений в Excel 2010 — для решения сложных задач