Информатика Базовый курс (стр. 103 из 188)
=ИНДЕКС(ЛГРФПРИБЛ(В1:В20;А1:А20);1)
12. Сделайте текущей ячейку D2. Повторите операции, описанные в пп. 3-9, иливведите вручную следующую формулу:
=ИНДЕКС(ЛГРФПРИБЛ(В1 :В20;А1 :А20);2)
Теперь ячейки С2 и D2 содержат, соответственно, коэффициенты а и Ъ уравнения наилучшего показательного приближения.
3 3 6 Глава 12. Обработка данных средствами электронных таблиц 
ПП Для интерполяции или экстраполяции оптимальной кривой без явного определения ее параметров можно использовать функции ТЕНДЕНЦИЯ (для линейной зависимости) и РОСТ (для показательной зависимости).13. Для построения наилучшей прямой другим способом дайте команду Сервис •Data Analysis (Анализ данных).
14. Откроется одноименное диалоговое окно. В списке Analysis Tools (Инструменты анализа) выберите пункт Regression (Регрессия), после чего щелкните на кнопке ОК.
15.
В поле Input Y Range (Входной интервал Y) укажите методом протягивания диапазон, содержащий значения функции (столбец В).16.
В поле Input X Range (Входной интервал X) укажите методом протягивания диапазон, содержащий значения независимой переменной (столбец А).17. Установите переключатель New Worksheet (Новый рабочий лист) и задайте длянего имя Результат расчета.
18.
Щелкните на кнопке О К и по окончании расчета откройте рабочий лист Результат расчета. Убедитесь, что вычисленные коэффициенты (см. ячейки В17 и В18) совпали с полученными первым методом.19. Сохраните рабочую книгу book.xls.
Я Мы научились анализировать с помощью программы Excel экспериментальные данные с использованием метода наименьших квадратов. Мы применили для вычислений разные средства программы Excel. Мы получили информацию, необходимую для построения графиков нужных приближений. 
Упражнение 12.6. Применение таблиц подстановки
30 мин Задача. Построить графики функций, коэффициенты которых определены в предыдущем упражнении.
1.
Запустите программу Excel (Пуск • Программы • Microsoft Excel) и откройте рабочую книгу book.xls.2. Выберите щелчком на ярлычке рабочий лист Обработка эксперимента.
3.
Так как программа Excel не позволяет непосредственно строить графики функций, заданных формулами, необходимо сначала табулировать формулу, то есть создать таблицу значений функций для заданных значений переменной. Сделайте текущей ячейку СЗ и занесите в нее значение 0. Эта ячейка будет использоваться как ячейка ввода, на которую будут ссылаться формулы.4.
Методом протягивания выделите значения в столбце А. Дайте команду Правка • Копировать, чтобы перенести эти данные в буфер обмена. Сделайте текущей ячейку F2 и дайте команду Правка • Вставить, чтобы скопировать заданные значения независимой переменной в столбец F» начиная со второй строки. 5. В ячейку G1 введите формулу =C3*$C$1+$D$1. Здесь СЗ — ячейка ввода, а в качестве других ссылок используются вычисленные методом наименьших квадратов коэффициенты уравнения прямой.Практическое занятие 337
6.
В ячейку Н1 введите формулу =$D$2*$C$2AC3 для вычисления значения показательной функции. В программе Excel можно табулировать несколько функций одной переменной в рамках единой операции.7.
Выделите прямоугольный диапазон, включающий столбцы F, G и Н и строки отстроки 1, содержащей формулы, до последней строки с данными в столбце F.8. Дайте команду Данные • Таблица подстановки. Выберите поле Подставлять значения по строкам в и щелкните на ячейке ввода СЗ.
9.
Щелкните на кнопке ОК, чтобы заполнить пустые ячейки в столбцах G и Н выделенного диапазона значениями формул в ячейках первой строки для значений независимой переменной, выбранных из столбца F.10.
Переключитесь на рабочий лист Диаграмма 1 (если используемое по умолчанию название листа с диаграммой было изменено, используйте свое название).11.
Щелкните на кнопке Мастер диаграмм на стандартной панели инструментов ипропустите первый этап щелчком на кнопке Далее.12.
Выберите вкладку Ряд и щелкните на кнопке Добавить. В поле Имя укажите: Наилучшая прямая. В поле Значения X укажите диапазон ячеек с данными в столбце F, а в поле Значения Y укажите диапазон ячеек в столбце G.13. Еще раз щелкните на кнопке Добавить. В поле Имя укажите: Показательнаяфункция. В поле Значения X укажите диапазон ячеек с данными в столбце F, а в поле Значения Y укажите диапазон ячеек в столбце Н.
14. Щелкните на кнопке Готово, чтобы перестроить диаграмму в соответствии с новыми настройками.
15. Сохраните рабочую книгу book.xls.
ГП Мы научились создавать таблицу подстановки, содержащую значения заданных формул для нужных значений независимой переменной. Мы применили эту возможность программы Excel для построения графиков функций, заданных формулами. Мы также научились редактировать ранее построенную диаграмму, нанося на нее дополнительные графики.Упражнение 12.7. Решение уравнений средствами
программы Excel
15 мин Задача. Найти решение уравнения х3 - Зх2 + х=-1.1.
Запустите программу Excel (Пуск • Программы • Microsoft Excel) и откройте рабочую книгу book.xls, созданную ранее.2. Создайте новый рабочий лист (Вставка • Лист), дважды щелкните на его ярлычке и присвойте ему имя Уравнение.
3.
Занесите в ячейку А1 значение 0.4.
Занесите в ячейку В1 левую часть уравнения, используя в качестве независимой переменной ссылку на ячейку А1. Соответствующая формула может, например, иметь вид =А1Л 3-3*А1Л 2+А1.
338 Глава 12. Обработка данных средствами электронных таблиц5. Дайте команду Сервис • Подбор параметра.
6.
В поле Установить в ячейке укажите В1, в поле Значение задайте -1, в поле Изменяя значение ячейки укажите А1.7. Щелкните на кнопке ОК и посмотрите на результат подбора, отображаемый вдиалоговом окне Результат подбора параметра. Щелкните на кнопке ОК, чтобы сохранить полученные значения ячеек, участвовавших в операции.
8.
Повторите расчет, задавая в ячейке А1 другие начальные значения, например0,5 или 2. Совпали ли результаты вычислений? Чем можно объяснить различия? 9. Сохраните рабочую книгу book.xls. 
ГП Мы научились численно решать с помощью программы Excel уравнения, содержащие одно неизвестное и задаваемые формулой. Мы выяснили, что при наличии нескольких корней результат решения уравнения зависит от того, какое число было выбрано в качестве начального приближения. Упражнение 12.8. Решение задач оптимизации 
30 мин Задача. Завод производит электронные приборы трех видов (прибор А, прибор В и прибор С), используя при сборке микросхемы Трех типов (тип 1, тип 2 и тип 3). Расход микросхем задается следующей таблицей: | Прибор А | Прибор В | Прибор С | |
| Тип 1 | 2 | 5 | 1 |
| Тип 2 | 2 | 0 | 4 |
| ТипЗ | 2 | 1 | 1 |
Стоимость изготовленных приборов одинакова.