Заказ № 3250 Воронежский государственный лесотехнический университет. «Информатика» на отлично! Лабораторная работа №3 Тема: «Программирование на рабочем листе Microsoft Excel: функции категорий математические, логические, ссылки и массивы,...

Воронежский государственный лесотехнический университет.

Дисциплина: «Информатика»

Работа выполнена и оформлена на отлично, принята с первого раза, без доработок.

После покупки вы получите файл Excel.

В работе раскрыты вопросы и выполнены задания, представленные ниже, в оглавлении.

Лабораторная работа №3 Тема: «Программирование на рабочем листе Microsoft Excel: функции категорий математические, логические, ссылки и массивы, статистические»

Упражнение 1. Для заданных целых чисел определить количество чисел,

кратных 3 (рис. 1).

Для выполнения задания:

1. В соответствующих ячейках рабочего листа добавьте необходимые подписи для данных и заданный диапазон чисел.

2. В ячейку В4 введите формулу: =ЕСЛИ(ОСТАТ(A4;3)=0;1;0), которая далее копируется на диапазон В5:В20.

3. В ячейку С4 (соответственно в диапазон С4:С20) введите формулу: =ЕСЛИ(B4=1;"Данная точка делится без остатка на 3";). Пользовательский формат для диапазона С4:С20: ;;[Белый]

4. В ячейку С22 введите формулу: =СУММ(B4:B20)

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

Упражнение 2. Удвоить числа, расположенные на нечётных местах одномерного массива, и определить их сумму (рис. 2).

Для выполнения задания:

1. В соответствующие ячейки рабочего листа добавьте необходимые подписи для данных и заданный диапазон чисел.

2. В ячейку В4 введите формулу: =ЕСЛИ(ОСТАТ(СТРОКА(A4);2)<>0;2*A4;0),

которая далее копируется на диапазон В5:В12.

3. В ячейку В15 введите формулу: =СУММ(B4:B11).

При необходимости можно отформатировать ячейки с данными и текстом,

используя соответствующие кнопки панели инструментов Форматирование.

Рис. 1 Количество точек, кратных 3

Рис. 2 Сумма удвоенных чисел, расположенных на нечётных местах

одномерного массива

Упражнение 3. Подсчитать в заданном двумерном массиве количество отрицательных элементов (рис. 3).

Рис. 3 Подсчёт отрицательных элементов массива

Для выполнения задания:

1. Заполните ячейки A4:D6 необходимыми значениями.

2. В ячейку F4 введите формулу: =СЧЁТЕСЛИ(A4:D6;"<0").

Упражнение 4. Заданы четыре матрицы A, B, C, D одинаковой размерности, содержащие по 3 строки и по 4 столбца. Найти 5A- cos (B) C²- D (рис. 4).

Для выполнения задания:

1. На рабочий лист поместите данные соответствующих матриц A, B, C, D (соответственно в диапазоны A2:D4 , A7:D9 , A12:D14 , A17:D19 ).

2. В ячейку F3 введите Результирующая матрица.

3. Используя команду Вставка | Объект | вкладка Новый | Тип объекта Microsoft Equation 3.0 , вставьте формулу 5A- cos (B) C²- D для наглядности.

4. В ячейку F7 введите формулу: =5*A2:D4-COS(A7:D9)+СТЕПЕНЬ(A12:D14;2)-A17:D19 и нажмите клавишу <Enter>.

5. Выделите диапазон F7:I9 , установите указатель мыши в строку

формул и нажмите клавиши <Ctrl>+<Shift>+<Enter>.

Рис. 4 Вычисление результирующей матрицы

Решение системы линейных уравнений

В общем случае решение линейной системы АХ=В, где А – матрица коэффициентов, В – вектор-столбец свободных членов, Х – вектор-столбец

неизвестных, имеет вид Х=А⁻¹В, где А⁻¹ – матрица, обратная к матрице А.

Это вытекает из того, что при решении матричных уравнений при Х должна

остаться единичная матрица Е. Умножая слева обе части уравнения АХ=В на

А⁻¹, получаем решение линейной системы уравнений.

Упражнение 5 . Найти решение системы линейных уравнений А²X = В, значения соответствующих матрицы и вектора-столбца имеют вид:

А=[23/11 7/4], В = [3/2].

Результат выполнения упражнения представлен на рис. 5.

Для решения системы линейных уравнений:

1. Значения матрицы А поместите в ячейки А4:В5 .

2. Значения столбца свободных членов поместите в ячейки D4:D5 .

3. Выделите диапазон A8:B9 , в ячейку A8 введите формулу: =МУМНОЖ(A4:B5;A4:B5)

Установите указатель мыши в строку формул и нажмите одновременно клавиши <Ctrl>+<Shift>+<Enter>.

4. Выделите диапазон D8:E9 , в ячейку D8 введите формулу: =МОБР(A8:B9)

Установите указатель мыши в строку формул и нажмите одновременно <Ctrl>+<Shift>+<Enter>.

5. Для получения результатов решения системы линейных уравнений следует перемножить полученную матрицу А² и столбец свободных членов В. Для этого выделите диапазон A12:A13 , в котором столько же строк, сколько в первой матрице А² и столбцов, сколько во второй матрице В. В ячейку А12 введите формулу: =МУМНОЖ(D8:E9;D4:D5)

Установите указатель мыши в строку формул и нажмите <Ctrl>+<Shift>+<Enter>.

Рис. 5 Решение системы линейных уравнений

Нахождение корней уравнения

В общем виде уравнение n-ой степени выглядит следующим образом:

F(x)=a₀xⁿ+a₁xⁿ⁻¹+…+ an-₁x+an=0

где n – некоторое положительное число, a₀…. an – произвольные комплексные числа, причём старший коэффициент a₀ должен быть не равен нулю.

Выражение f(x) называется многочленом (полиномом) n– ой степени от неизвестного x. Если при некотором x= x₀ выполняется равенство f(x)=0, то x₀ называется корнем многочлена f(x).

Действительными корнями многочлена будут абсциссы точек пересечения его графика с осью X и только они.

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

Число отрицательных корней многочлена равно числу сохранения знаков в системе коэффициентов этого многочлена или меньше этого числа на чётное число (теоремы Декарта и Бюдана - Фурье).

Для отыскания корней уравнений произвольной степени в MS Excel необходимо:

1. Произвести табулирование заданной функции на некотором интервале с целью выявления (локализации) корней уравнения (перемена знака в значении функции).

2. После локализации корней установить предельное число итераций и погрешность для вычисления корней (выполнить команду Сервис | Параметры и установить необходимые опции на вкладке Вычисления ).

3. Выполнить вычисление корней уравнения с использованием средства Подбор параметра (выполнить команду Сервис | Подбор параметра ).

4. Построить график исследуемой функции.

Упражнение 6 . Найти все корни уравнения: x⁵+2x⁴+5x³+8x²-7x-3=0

1. Выполните приближённое табулирование функции: f(x)= x⁵+2x⁴+5x³+8x²-7x-3=0 на отрезке [-10;10]:

• в ячейки А9:А29 введите аргумент функции – значения отрезка [-10;10] с шагом 1;

• в ячейку В9 внесите формулу: =A$5*A9^5+B$5*A9^4+C$5*A9^3+D$5*A9^2+E$5*A9+F$5 и скопируйте её значение на весь диапазон табулирования В9:В29 (рис. 6);

• вычислите значения функции f(x)на этом диапазоне. Определите по результатам вычислений, что значение функции f(x)меняет знак на отрезке [-3;1].

2. Для более точного табулирования функции на заданном отрезке:

• в ячейки D9:D49 введите аргумент функции f(x) - значение отрезка [-3;1] с шагом 0,1.

• в ячейку E9 введите формулу, аналогичную формуле для ячейки B9, и скопируйте её на весь диапазон значений аргумента функции:

=A$5*D9^5+B$5*D9^4+C$5*D9^3+D$5*D9^2+E$5*D9+F$5

• вычислите значение функции f(x) на этом диапазоне и постройте график для табулированной функции.

Результаты точного табулирования функции дают 3 изменения знака на

отрезке [-3;1], что свидетельствует о наличии корней уравнения f(x)=0.

3. С помощью средства Подбор параметра определите корни уравнения:

• Для вычисления 1 корня поместите указатель в ячейку D18 (либо D19) и выполните команду Данные | Анализ «что-если» | Подбор параметра (рис.7). Получим 1 корень уравнения:

x₁=-2,07299558373219;

• аналогично вычислите оставшиеся 2 корня:

x₂=0,328044321086697;

x₃=0,789262359841618

4. Выделите диапазон области значения функции ( Е18:Е50 ) и воспользуйтесь мастером построения диаграмм. Для построения графика используйте типы диаграмм График и Точечная.

Рис. 6 Вычисление корней многочлена

Рис. 7 Нахождение корня уравнения с использованием средства Подбор параметра

Построение графиков, поверхностей, алгебраических и трансцендентных линий на плоскости

Построение графиков функций предполагает следующие шаги:

Подготовить диапазон области определения функции с помощью маркера автозаполнения.

1. Рассчитать значение функции на данном диапазоне, используя формулы и функции рабочего листа MS Excel и маркер автозаполнения.

2. Выделить диапазон области определения и области значения функции и воспользоваться мастером построения диаграмм. Для построения графиков лучше использовать типы диаграмм График и Точечная .

3. Отформатировать полученный график.

Для построения некоторых алгебраических и трансцендентных линий на плоскости, заданных в полярных координатах f (ρ,ϕ)=0, следует:

1. Подготовить диапазон изменения координаты ϕ.

2. Рассчитать значение функции на данном диапазоне в полярных координатах ρ = ρ(ϕ).

3. Рассчитать значения x и y в декартовой системе координат по формулам:

x=p cos,

y= ρ sin ϕ.

4. Выделить диапазон области определения и области значения функции, т.е. все значения x и y на рабочем листе, и воспользоваться мастером построения диаграмм. Для построения графиков лучше использовать типы диаграмм График и Точечная .

5. Отформатировать полученный график.

Для построения поверхности первого порядка и построения поверхностей второго порядка в случае, когда третья координата входит в уравнение поверхности в квадрате, следует:

1. Подготовить диапазон изменения функции по двум координатам, расположив изменения одной координаты вдоль некоторого столбца вниз, а другой − вдоль прилегающей строки вправо.

2. Ввести на пересечении координат необходимую формулу для построения поверхности и воспользоваться маркером автозаполнения для её копирования на всю область построения поверхности.

3. Выделить подготовленные данные и воспользоваться мастером построения диаграмм (тип диаграммы − Поверхность).

4. Отформатировать полученную поверхность.

Упражнение 7. Построить график функции y= cos³(πx).

Для выполнения задания:

1. Задайте область определения X вводом начальных данных 0 и 0,1, а затем маркером автозаполнения подготовьте весь диапазон A4:A24 .

2. В ячейку B4 введите формулу = СТЕПЕНЬ (COS (ПИ()*A4);3) = и

скопируйте её на диапазон B4:B24 .

3. Постройте график функции с помощью мастера диаграмм.

4. Отформатируйте полученный график (рис. 8).

Рис. 8 График функции y=cos³(πx)

Упражнение 8. Построить функцию, заданную уравнением в полярных координатах:

ρ=a sin(kϕ).

Для выполнения задания:

1. Задайте область определения ϕ вводом начальных данных 0 и 0,05, а затем маркером автозаполнения подготовьте весь диапазон A4:A84 .

2. Для расчёта ρ в ячейку B4 введите формулу с учетом того, что k=3 (для трехлепестковой розы) =C$2*SIN(3*A4) и скопируйте её на диапазон B4:B84.

3. Для расчёта x и y в ячейки C4 и D4 соответственно введите формулы

=B4*COS(A4),

=B4*SIN(A4)

и скопируйте их на диапазоны С4:С84 и D4:D84 .

4. Постройте график функции с помощью мастера диаграмм, используя тип диаграммы Точечная.

5. Отформатируйте полученный график (рис. 9).

Рис. 9 График трёхлепестковой розы

Упражнение 9. Построить поверхность:

z=x³/2-(y+2)²

Для выполнения задания:

1. Задайте диапазон, как показано на рис. 10.

2. Для расчёта z при изменяющихся x и y в ячейку B2 введите формулу:

=$A2^3/2-(B$1+2)^2

3. Выделите подготовленные данные и постройте с помощью мастера диаграмм поверхность.

4. Отформатируйте полученную поверхность.

Рис. 10 Диапазон данных для построения поверхности

Упражнение 10. Построить сферу: x² + y²+ z² = 1

Для выполнения задания:

1. Задайте диапазон, как показано на рис. 11. В диапазон B2:B43 вводятся значения от -1 до 1 с шагом 0,1, причём каждое значение дублируется последовательно. Аналогично вводятся значения и для диапазона C2:AR2 .

2. Для расчёта z при изменяющихся x и y в ячейку С2 введите формулу =КОРЕНЬ(1-$B2^2-C$1^2)*ЕСЛИ(ОСТАТ($A2;2)=0;1;-1)

3. В диапазон A2:A43 введите повторяющиеся числа 2 и 3 для определения знака в формуле.

4. Выделите подготовленные данные и постройте с помощью мастера диаграмм поверхность.

5. Отформатируйте полученную поверхность, как показано на рис. 13.

Рис. 11. Диапазон области определения сферы

Задания для самостоятельной работы

1. Найти все корни уравнения: x³+3x²+3x-14=0

2. Постройте:

а) графики функций

y=(4+x²e⁻ˣ) / (4+√x⁴+sin² (x)

z={√1+5x²- sin²(x), x≤0

(7+x) ² / ³√4+e⁻⁰´⁷ ͯ, x>0

б) поверхность z=(5x+2) √x+3cos²(y)

в) Каппа: ρ = actg ϕ;

г) двухполосный гиперболоид: x²/a ²+ y²/b² – z²/c² = -1

Все задание в прикрепленном демо-файле, так как на сайте некорректно отображаются таблицы, формулы и символы.

Данная работа проверена и одобрена модераторами сайта.

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

Также, при необходимости, после покупки, Вы можете заказать на данном сайте необходимые дополнения к работе.