Упражнение.
Пример решения задачи в Excel.
Рассмотрим, как используются абсолютные и относительные адреса в программном комплексе Excel на примере решения следующей задачи.
Задача.
Вычислить значения трех квадратичных функций:
y1 = 5x2 + 15x + 7
y2 = 7x2 + 12x + 3
y3 = 8x2 + 20x + 9
в точке x=10.
Расчет можно легко провести устно.
Для этого подставим x=10 в приведенные выше формулы.
Получим:
y1 = 5*102 + 15*10 + 7 = 500 + 150 + 7 = 657
y2 = 7*102 + 12*10 + 3 = 700 + 120 + 3 = 823
y3 = 8*102 + 20*10 + 9 = 800 + 200 + 9 = 1009
Величина x=10 остается постоянной для трех заданных формул, так как в данной задаче вычисление значений функций нужно производить в одной и той же точке x=10. Легко видеть, что коэффициенты a, b, c заданных квадратичных функций не постоянны и являются переменными величинами. Поэтому, по всей видимости, необходимо отвести специальную ячейку, например, B7, для хранения постоянного числа x=10, а коэффициенты a, b, c хранить в прямоугольной таблице (матрице).
На трех рисунках ниже показан проект решения данной задачи. Отметим следующие существенные моменты, которые учитывались на этапе проектирования (моделирования) процесса решения задачи в программном комплексе Excel:
|
|
- В ячейке A1 будет находиться наименование задачи: "Вычисление значений трех квадратичных функций:".
- Строка 2 будет пропущена для того, чтобы наименование не сливалось с текстом задачи. Обычно так принято в книгах и в документах.
- В третей, четвертой и пятой строках в ячейках B3, B4, B5 будет размещена текстовая информация о трех заданных квадратических функциях. Этот текст относится к постановке задачи и будет располагаться так, чтобы красиво смотрелось окончательное решение задачи.
- В ячейка A7 будет записан текст: "при x=". Причем этот текст желательно выравнять по правому краю ячейки A7 для того, чтобы следующее за этим текстом число 10 из ячейки b7 точно примыкало к знаку = (равно).
- Далее в ячейке B7, как сказано выше, будет находится постоянное число x=10. Его желательно выравнять по левому краю ячейки, дабы оно следовало точно за знаком равно из ячейки A7. Это число можно будет использовать в расчетах, так как оно находится в числовом формате в отдельной ячейке.
- Строка 8 будет пропущена, так как далее будет следовать заголовок таблицы, а затем и сама таблица, содержащая исходную информацию и результатами решения задачи.
- Строка 9 будет содержать следующие наименования (заголовки) столбцов проектируемой таблицы решения задачи: № п/п; a=; b=; c=; yi=.
- Ниже в строках 10, 11 и 12 будет находиться исходная информация - коэффициенты заданных квадратичных функций и результаты решения задачи - вычисленные значения показателя yi.
C этой целью предварительно нужно сформировать матрицу с исходной информацией данной задачи.
Нужно занести: - в ячейку A10 число 1 - порядковый номер первой квадратичной функции y1 = 5x2 + 15x + 7;
- в ячейку B10 число 5 - коэффициент при старшей степени величины x2 первой квадратичной функции,
- в ячейку C10 число 15 - коэффициент при первой степени переменной величины x,
- в ячейку D10 число 7 - свободный член.
- Аналогично нужно занести в ячейку A11 число 2 - порядковый номер второй квадратичной функции y2 = 7x2 + 12x + 3,
- в ячейку B11 - число 7 - коэффициент при x2,
- в ячейку C11 число 12 - коэффициент при x,
- в ячейку D11 - свободный член 3.
- То же самое нужно проделать для третьей квадратичной функции y3 = 8x2 + 20x + 9.
- В ячейку A12 - занести число 3 - порядковый номер третьей функции,
- в ячейку B12 число 8 - коэффициент при x2,
- в ячейку C12 число 12 - коэффициент при x
- и в ячейку D12 число 9 - свободный член третьей функции.
На этом постановка задачи и подготовка исходной матрицы расчетов завершается.
На следующем этапе нужно указать, какие будут использованы формулы для выполнения расчетов, то есть как получить вычисленные значения показателя yi.
|
|
- На первом рисунке ниже показано как в ячейку E10 будет занесена формула для вычисления показателя y1. Rак известно, любая формула всегда начнется со знака равно: =B10*$B$7*$B$7+C10*$B$7+D10 и заканчивается нажатием клавиши Enter. Следует обратить внимание на то, что формула расчетов одновременно отображается в строке формул и поэтому эту строку можно использовать для занесения формулы или для ее корректировки.
Опишем подробно как заносить формулу для вычисления показателя y1 в ячейку E10:
1. Щелкнуть левой кнопкой мыши по ячейке E10.
2. Занести знак равно =
3. Щелкнуть мышью по ячейке, в которой находится старший коэффициент 5 первой квадратичной функции при переменной x2. В формуле отобразится его адрес B10. Ввести знак умножения и щелкнуть по ячейке, содержащей число 10. В формуле отобразится адрес B7 этого числа. Далее нажать функциональную клавишу F4, и знак доллара появится перед строкой и столбцом в адресе B7. Еще раз щелкнуть по знаку умножения на клавиатуре. Далее еще раз щелкнуть по ячейке, содержащей число 10 и нажать F4. В результате в формуле будет =B10*$B$7*$B$7 Далее нажать на знак плюс и аналогично ввести адреса чисел 15 и переменой x=10. Получим теперь формулу =B10*$B$7*$B$7+C10*$B$7 Остается таким же образом ввести адрес D10 для числа 7 - свободного члена и нажать клавишу Enter, чтобы завершить процесс ввода формулы в ячейку E10.
- На следующем рисунке показано, как в ячейку E11 будет занесена формула расчетов для вычисления значения показателя y2: =B11*$B$7*$B$7+C11*$B$7+D11. Происходит это аналогично подробно описанному выше алгоритму ввода формулы расчетов в ячейку E10.
- На следующем рисунке отображен процесс занесения в ячейку E12 формулы для вычисления значения показателя y3: =B12*$B$7*$B$7+C12*$B$7+D12. Происходит это аналогично подробно описанному выше алгоритму ввода формулы расчетов в ячейку E10.
Однако, после занесения формулы для вычисления y1 в ячейку E10 Excel позволяет упростить процесс занесения последующих формул для вычисления значений аналогичных величин y2 и y3 в ячейки E11 и E12. Проанализируйте, чем похожи и чем отличаются три рассматриваемые формулы, указанные вместе ниже:
=B10*$B$7*$B$7+C10*$B$7+D10
=B11*$B$7*$B$7+C11*$B$7+D11
=B12*$B$7*$B$7+C12*$B$7+D12.
Видно, что указанные формулы содержат неизменяющийся адрес $B$7. Это адрес постоянной для данной задачи величины x=10. Такие адреса, как было сказано выше, называют абсолютными. Отличаются же формулы тремя группами адресов: это адреса B10, B11 и B12, а также адреса C10, C11, C12 и адреса D10, D11 и D12. Это адреса коэффициентов a, b, c соответственно 1-й, 2-й и 3-й квадратичной функции. Ввиду того, что формулы расчетов идентичны и различаются только адресами коэффициентов a, b, c, изменяющихся относительно 1-й, 2-й и 3-й строки, в Excel предусмотрен инструмент копирования формул. Он состоит в следующем:
|
|
Нужно щелкнуть по ячейке E10, в которой уже находится формула, которую Вы хотите скопировать в ячейки E11 и E12. Появится крупный белый жирный плюс на выбранной ячейке. Далее переместитесь в нижний правый угол ячейки и поймайте тот момент, когда этот плюс преобразуется в маленький изящный черный крестик. Нажмите в этот момент левую кнопку мыши и перемещайтесь мышкой вниз на ячейки E11 и E12. Произойдет копирование формулы и настройка изменяющихся, то есть относительных адресов, применительно к новой строке. Во второй строке на месте B10 вместо B10 появится B11 и так далее вместо C10 появится C11, вместо D10 появится D11. То же самое аналогично произойдет и в третьей строке.
Так в Excel с вводом понятий абсолютного и относительного адресов упрощается трудоемкий процесс занесения нужных формул в большие группы из 2, 3 и более ячеек.
- На последнем рисунке представлено окончательное решение задачи.
Задание для самостоятельной работы
Задача.
Даны три функции:
линейная функция: y1=2x + 10;
квадратичная функция: y2=7x2 + 8x + 10;
и кубическая функция: y3=15x3 - 3x2 + 4x - 23.
Вычислить значение каждой заданной функции в точке x, используя программный комплекс Excel. За значение х принять день (двузначное число своего рождения).