Пример решения задачи в MS Excel

Поясним, как используются абсолютные и относительные адреса в программном комплексе Excel на примере решения следующей задачи.
Задача.
Вычислить значения трех квадратичных функций:
y₁ = 5x² + 15x + 7
y₂ = 7x² + 12x + 3
y₃ = 8x² + 20x + 9
в точке x=10.

Расчет можно легко провести устно.
Для этого подставим x=10 в приведенные выше формулы.
Получим:
y₁ = 5*10² + 15*10 + 7 = 500 + 150 + 7 = 657
y₂ = 7*10² + 12*10 + 3 = 700 + 120 + 3 = 823
y₃ = 8*10² + 20*10 + 9 = 800 + 200 + 9 = 1009

Отметим, что величина x=10 остается постоянной для трех заданных формул, так как в данной задаче вычисление значений функций нужно производить в одной и той же точке x=10. Легко видеть, что коэффициенты a, b, c заданных квадратичных функий не постоянны и являются переменными величинами. Поэтому, по всей видимости, необходимо отвести специальную ячейку, например, B7, для хранения постоянного числа x=10, а коэффициенты a, b, c хранить в прямоугольной таблице (матрице).

Отметим следующие существенные моменты, которые учитываются на этапе проектирования (моделирования) процесса решения задачи в программном комплексе Excel:

1. В ячейке A1 будет находиться наименование задачи: "Вычисление значений трех квадратичных функций:".

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

3. В третей, четвертой и пятой строках в ячейках B3, B4, B5 будет размещена текстовая информация о трех заданных квадратических функциях. Этот текст относится к постановке задачи и будет располагаться так, чтобы красиво смотрелось окончательное решение задачи.

4. В ячейка A7 будет записан текст: "при x=". Причем этот текст желательно выравнять по правому краю ячейки A7 для того, чтобы следующее за этим текстом число 10 из ячейки b7 точно примыкало к знаку = (равно).

5. Далее в ячейке B7, как сказано выше, будет находится постоянное число x=10. Его желательно выравнять по левому краю ячейки, дабы оно следовало точно за знаком равно из ячейки A7. Это число можно будет использовать в расчетах, так как оно находится в числовом формате в отдельной ячейке.

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

7. Строка 9 будет содержать следующие наименования (заголовки) столбцов проектируемой таблицы решения задачи: № п/п; a=; b=; c=; y_i=.

8. Ниже в строках 10, 11 и 12 будет находиться исходная информация - коэффициенты заданных квадратичных функций и результаты решения задачи - вычисленные значения показателя y_i.
C этой целью предварительно нужно сформировать матрицу с исходной информацией данной задачи.
Нужно занести:

o в ячейку A10 число 1 - порядковый номер первой квадратичной функции y₁ = 5x² + 15x + 7;

o в ячейку B10 число 5 - коэффициент при старшей степени величины x² первой квадратичной функции,

o в ячейку C10 число 15 - коэффициент при первой степени переменной величины x,

o в ячейку D10 число 7 - свободный член.

o Аналогично нужно занести в ячейку A11 число 2 - порядковый номер второй квадратичной функции y₂ = 7x² + 12x + 3,

o в ячейку B11 - число 7 - коэффициент при x²,

o в ячейку C11 число 12 - коэффициент при x,

o в ячейку D11 - свободный член 3.

o То же самое нужно проделать для третьей квадратичной функции y₃ = 8x² + 20x + 9.

o В ячейку A12 - занести число 3 - порядковый номер третьей функции,

o в ячейку B12 число 8 - коэффициент при x²,

o в ячейку C12 число 12 - коэффициент при x

o и в ячейку D12 число 9 - свободный член третьей функции.

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

9. Как известно, любая формула всегда начнется со знака равно: =B10*$B$7*$B$7+C10*$B$7+D10 и заканчивается нажатием клавиши Enter. Следует обратить внимание на то, что формула расчетов одновременно отображается в строке формул и поэтому эту строку можно использовать для занесения формулы или для ее корректировки.

10. Опишем подробно как заносить формулу для вычисления показателя y₁ в ячейку E10:

1. Щелкнуть левой кнопкой мыши по ячейке E10.

2. Занести знак равно =

3. Щелкнуть мышью по ячейке, в которой находится старший коэффициент 5 первой квадратичной функции при переменной x². В формуле отобразится его адрес 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.

11. На следующем рисунке показано, как в ячейку E11 будет занесена формула расчетов для вычисления значения показателя y₂: =B11*$B$7*$B$7+C11*$B$7+D11. Происходит это аналогично подробно описанному выше алгоритму ввода формулы расчетов в ячейку E10.

12. На следующем рисунке отображен процесс занесения в ячейку E12 формулы для вычисления значения показателя y₃: =B12*$B$7*$B$7+C12*$B$7+D12. Происходит это аналогично подробно описанному выше алгоритму ввода формулы расчетов в ячейку E10.
Однако, после занесения формулы для вычисления y₁ в ячейку E10 Excel позволяет упростить процесс занесения последующих формул для вычисления значений аналогичных величин y₂ и y₃ в ячейки 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 и более ячеек.

На последнем рисунке представлено окончательное решение задачи.

Дата добавления: 2015-04-24