Ввод и редактирование данных в режиме формы

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

В Excel 2007 необходимо добавить соответствующую кнопку на панель быстрого доступа: щелкнуть кнопку Настройка панели быстрого доступа (вверху слева), выбрать пункт Другие команды. Далее (вверху слева) в поле Выбрать команды выбрать Все команды, в списке ниже выбрать Форма, Добавить, ОК.

Для открытия окна формы надо щелкнуть в любую ячейку таблицы и нажать кнопку Форма на панели быстрого доступа. Переход между строками – с помощью Назад и Далее. Добавление и удаление записей – Добавить и Удалить. Поиск записей осуществляется с помощью кнопки Критерии (Ввести значение, ENTER, выход – с помощью кнопки Правка).

Скрытие (и отображение) строк /столбцов.

Выделить строки, на Главная-Ячейки нажать Формат. В списке выбрать скрыть (или отобразить), в подменю выбрать Скрыть строки.

Для возврата строк: выделить строки, прилегающие к скрытым. Далее аналогично предыдущему.

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

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

На Данные-Работа с данными щелкнуть Анализ «что если» (треугольник списка внизу справа), выбрать Подбор параметра, в поле Установить в ячейке ввести ссылку на ячейку, содержащую формулу, в поле Значение ввести заданное значение (которое должно быть достигнуто). В поле Изменяя значение ячейки ввести ссылку на ячейку, значение которой нужно подобрать (т.е. содержащей искомое значение параметра). Упомянутая формула должна ссылаться на эту ячейку.

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

Пример: решение квадратного уравнения ax²+bx+c=0.

В ячейки A2,B2,C2 вводим значения a,b,c. В ячейку D2 вводим приближенное значение х. Вводим в ячейку E2 формулу =A2*D2^2+B2*D2+C2 . На Данные-Работа с данными щелкнуть треугольник внизу справа, Подбор параметров. В поле Установить в ячейке указываем E2, в поле Значение указываем 0 (число ноль), а в поле Изменяя значение указываем D2. Жмем на OK. Для примерной локализации корней предварительно можно построить график функции у = ax²+bx+c (координаты пересечения кривой с осью Х).

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

Для решения подобных задач в Excel применяется надстройка Поиск решения , для подключения которой следует:

кнопка Office, Параметры Excel, Надстройки, в поле Управление выбрать (внизу) Надстройки Excel, Перейти, в поле Надстройки включить флажок Поиск решения, ОК.

1. для решения системы нелинейных уравнений

Пример: решить систему x²+y² =3

3x+2y =1

Решение (x,y) системы совпадает с парой значений, доставляющих наименьшее значение (равное нулю) функции f(x,y)=(x^2+y^2 -3)^2 + (3x+2y -1)^2. В действительности, Excel ищет минимум функции f(x,y).

В ячейки A2 и B2 ввести начальные приближения для x и y : (1 и -1). В ячейку С3 ввести формулу для функции (зависящей от значений двух параметров) : = (A2^2+B2^2-3)^2 + (3*A2+2*B2-1)^2 . (Значения х и у, при которых это выражение обращается в ноль, являются решением системы уравнений). Чтобы найти решение: на Данные-Анализ щелкнуть Поиск решения. В поле Установить целевую надо указать ссылку на ячейку С3 с формулой для функции, в поле Изменяя ячейки ввести A2:B2, Выполнить.

В результате (если поиск окажется успешным) в ячейках A2 и B2 окажутся значения х и у, а в ячейке С2 – соответствующее значение функции, близкое к нулю.

Для получения приближенных начальных значений х и у следует вычислить значения функции на сетке значений (x_k,y_k) и выбрать значения функции, наиболее близкие к нулю. Следует выбрать на свободном месте листа квадрат (или прямоугольник), например, А3:J14. В первый столбец А4:А14 ввести значения x_i, во 2-ю строку - значения у_к. На свободное место скопировать формулу с соответствующими абсолютными и относительными адресами.

2. для нахождения наименьшего (или наибольшего) значения функции

Как в предыдущем пункте 1, но в диалоговом окне Поиск решения для Равной выбрать вариант минимальному (или максимальному) значению. Если имеются ограничения на значения переменных, например, x > 1 и y >2, то: в окне Поиск решения щелкаем Добавить, в появившееся окне Добавление ограничения в поле Ссылка на ячейку вписываем адрес ячейки, содержащей x, в среднее поле – знак >, в поле Ограничение вписываем число 1, и аналогично для y. Выполнить.

Вариант более сложного ограничения (например, 2x+3y > 4) см. ниже.

3. для решения задачи линейного программирования

Частный случай пункта 2 (случай линейной функции).

Примеры:

Пример 1. Требуется найти значения переменных х и у, которые обеспечивают максимальное значение целевой функции f(x,y)=2x+3y при наличии ограничений: x >=0, y >=0, x+2y <=0, 2x+y<=3.

Решение. Ячейки А1 и В1 отводим под значения х и у. В ячейку А2 вводим формулу = 2*А1+3*В1. В ячейку А3 вводим =А1 (т.е. левую часть 1-го ограничения x >=0 ), в А4 вводим = В1, в А5 - =А1+2*В1, в ячейку А6 вводим = 2*А1+В1.

На Данные-Анализ щелкнуть Поиск решения. В диалоговом окне Поиск решения в поле Установить целевую ячейку ввести А2, в поле Изменяя ячейки ввести A1:B1. Далее щелкнуть на Добавить в списке Ограничения. Появится диалоговое окно Добавление ограничения. В его поле Ссылка на ячейку ввести А3, затем (правее) выбрать операцию сравнения (>=), затем в поле Ограничение задается константа 0 ( или, в общем случае, ссылка на ячейку со значением или формулой). Кнопка ОК добавляет ограничение и закрывает окно. Для ограничения x+2y <=0 : Добавить,в поле Ссылка на ячейку ввести А5, затем выбрать операцию сравнения <=, затем в поле Ограничение задается константа 3.

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

Пример 2. Требуется купить 300 г сыра (230 руб.кг), 500 г колбасы (295 руб.кг) а также некоторое количество бананов (65 руб.кг) и конфет (190 руб.кг). Всего надо затратить 400 руб, конфет требуется не более 500 г. Сколько бананов и конфет надо приобрести ?

Решение. В ячейку С7 ввести формулу = 0,3*230 + 0,5*295+С5*65+С6*190. На Данные-Анализ щелкнуть Поиск решения, в поле Установить целевую ячейку ввести С7, в поле Изменяя ячейки ввести С5:С6. В группе Равной установить переключатель в положение Значению и ввести 400, Далее щелкнуть на Добавить в списке Ограничения. Появится диалоговое окно Добавление ограничения. В его поле Ссылка на ячейку ввести С6, затем (правее) выбрать операцию сравнения (<=), затем в поле Ограничение задается константа 0,5. Выполнить. В результате в ячейках С5 и С6 будут значения, обеспечивающие значение 400 в С7. В появившемся окне выбрать один из вариантов: сохранить найденное решение или восстановить исходные значения, OK.

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

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