Ответы к экзамену 1 курс

39. Пример решения задачи в 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:

В ячейке A1 будет находиться наименование задачи: "Вычисление значений трех квадратичных функций:".
Строка 2 будет пропущена для того, чтобы наименование не сливалось с текстом задачи. Обычно так принято в книгах и в документах.
В третей, четвертой и пятой строках в ячейках B3, B4, B5 будет размещена текстовая информация о трех заданных квадратических функциях. Этот текст относится к постановке задачи и будет располагаться так, чтобы красиво смотрелось окончательное решение задачи.
В ячейка A7 будет записан текст: "при x=". Причем этот текст желательно выравнять по правому краю ячейки A7 для того, чтобы следующее за этим текстом число 10 из ячейки b7 точно примыкало к знаку = (равно).
Далее в ячейке B7, как сказано выше, будет находится постоянное число x=10. Его желательно выравнять по левому краю ячейки, дабы оно следовало точно за знаком равно из ячейки A7. Это число можно будет использовать в расчетах, так как оно находится в числовом формате в отдельной ячейке.
Строка 8 будет пропущена, так как далее будет следовать заголовок таблицы, а затем и сама таблица, содержащая исходную информацию и результатами решения задачи.
Строка 9 будет содержать следующие наименования (заголовки) столбцов проектируемой таблицы решения задачи: № п/п; a= ; b= ; c= ; y_i= .
Ниже в строках 10, 11 и 12 будет находиться исходная информация - коэффициенты заданных квадратичных функций и результаты решения задачи - вычисленные значения показателя y_i. C этой целью предварительно нужно сформировать матрицу с исходной информацией данной задачи. Нужно занести:

в ячейку A10 число 1 - порядковый номер первой квадратичной функции y₁ = 5x² + 15x + 7;
в ячейку B10 число 5 - коэффициент при старшей степени величины x² первой квадратичной функции,
в ячейку C10 число 15 - коэффициент при первой степени переменной величины x,
в ячейку D10 число 7 - свободный член.
Аналогично нужно занести в ячейку A11 число 2 - порядковый номер второй квадратичной функции y₂ = 7x² + 12x + 3,
в ячейку B11 - число 7 - коэффициент при x²,
в ячейку C11 число 12 - коэффициент при x,
в ячейку D11 - свободный член 3.
То же самое нужно проделать для третьей квадратичной функции y₃ = 8x² + 20x + 9.
В ячейку A12 - занести число 3 - порядковый номер третьей функции,
в ячейку B12 число 8 - коэффициент при x²,
в ячейку C12 число 12 - коэффициент при x
и в ячейку D12 число 9 - свободный член третьей функции.

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

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

Щелкнуть левой кнопкой мыши по ячейке E10.
Занести знак равно =
Щелкнуть мышью по ячейке, в которой находится старший коэффициент 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.

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

На следующем рисунке отображен процесс занесения в ячейку 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 и более ячеек.

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

Содержание