ит

51

Рис. 4.1 Ведомость по расчёту заработной платы работников научно-проектного отдела

1. В ячейку А2 ввести название ведомости – Расчёт заработной платы работников научно-проектного отдела. Выделить диапазон ячеек A1:K2, объединить ячейки кнопкой Объединить и поместить в центре на панели инструментов, а кнопкой расположить надпись По правому краю.

2. В ячейки A3:H3 ввести названия полей ведомости: № пп, Фамилия И.О., Должность, Тарифная ставка, Стаж, k, Надбавка за стаж, Итого,

Процент налога, Удержать, Выплата, отцентрировать их командой Фор-

мат→Ячейки→Выравнивание.

3. Коэффициент k определяется по следующему алгоритму: 0,1 если отработано до 5 лет включительно, 0,2 – от 5 до 10 лет включительно, 0,25 – от 10 до 15 лет включительно, 0,3 свыше 15 лет. Формула для ячейки F4 (рис. 4.2):

=ЕСЛИ(E4<=5;0,1;ЕСЛИ(И(E4>5;E4<=10);0,2;ЕСЛИ(И(E4>10;E4<=15);0,25;0,3)))

4.В ячейку G4 записать: =D4*F4 и изменить формат результата командой Формат→Ячейки→Число, из списка Числовые форматы выбрать Все форматы, в поле Тип ввести пользовательский формат # ##0,00р.;

5.В ячейку H4 ввести формулу: =D4+G4 и указать пользовательский формат # ##0,00р. При необходимости увеличить размер столбца H.

6.Процент налога определяется в зависимости значения итоговой суммы (столбец Итого): 2% если итог составляет до 7 000 р. включительно, 10% – более 7 000 р. до 10 000 р. включительно, 20% – более 10 000 р. до 25 000 р. включительно, 35% – превышающие 25 000 р. (рис. 4.3):

=ЕСЛИ(H4<=7000;0,02;ЕСЛИ(И(H4>7000;H4<=10000);0,1;

ЕСЛИ(И(H4>10000;H4<=25000);0,25;0,35)))

формат числа для ячейки – Процентный.

52

Рис. 4.2 Ввод формулы в ячейку I4

7.В ячейку J4 записать: =I4*H4 (пользовательский формат # ##0,00р.).

8.Выплата это сумма к выдаче: Итого без Удержать.

9.Создать пользовательский формат данных, учитывающий стаж работы для столбца Стаж: до 5 лет – данные представлены жёлтым цветом, от 5 до 10

–синим, от 10 до 15 – зелёным, свыше 15 – красным. Для этого вызывается ко-

манда Формат Ячейки и для ячейки E4 указывается пользовательский фор-

мат [Красный] # ##0;. После этого выбрать команду Формат Условное фор-

матирование и заполнить согласно рис. 4.3.

Рис. 4.3 Использование условного форматирования для представления данных столбца Стаж

53

10.Для учета недопустимости ввода отрицательных чисел в столбец E за-

полнить вкладку Параметры команды Данные→Проверка как на рис. 4.4.

Рис. 4.4 Команда меню Данные→Проверка вкладка Параметры

А на вкладке Сообщение об ошибке (рис. 4.5) той же команды указать настройки появляющегося при вводе ошибочных данных сообщения.

Рис. 4.5 Команда Данные→Проверка вкладка Сообщение об ошибке

11.Чтобы вывести постоянное сообщение: Тарифная ставка. Будьте внимательны при вводе тарифной ставки для поля Тарифная ставка (рис. 4.6)

54

необходимо вызвать команду Данные→Проверка и заполнить вкладку Сооб-

щение для ввода.

Рис. 4.6 Сообщение для поля Тарифная ставка 12.Изменить формат столбца Тарифная ставка на # ##0,00р.;[Красный]

"Тарифная ставка не может быть отрицательной" (рис. 4.7) чтобы при вводе от-

рицательных значений появлялось предупреждение (рис. 4.6).

Рис. 4.7 Форматирование столбца Тарифная ставка

Использование функций категории логические, математические для для действий с массивами данных

В работе используются следующие встроенные функции:

СУММ(аргумент1;аргумент2;…) – выполняет суммирование аргументов

(можно воспользоваться кнопкой панели инструментов).

ОСТАТ(число;делитель) – возвращает остаток от деления аргумента число на делитель. Результат имеет такой же знак, как и делитель.

55

СТРОКА(ссылка) – возвращает номер строки, определяемой ссылкой.

Если ссылка опущена, то предполагается, что это ссылка на текущую ячейку.

СЧЁТЕСЛИ(диапазон;критерий) – подсчитывает количество ячеек внут-

ри диапазона, удовлетворяющих заданному критерию (например, «>32»).

МУМНОЖ(массив1;массив2) – возвращает произведение матриц. Ре-

зультатом является массив с числом строк, как в массиве1 и с числом столбцов,

как в массиве2. Количество столбцов массива1 равно количеству строк масси-

ва2. Оба массива содержат только числа (иначе выведется ошибка #ЗНАЧ!).

СЧЁТ(значение1; значение2; ...) – подсчитывает количество чисел в спи-

ске аргументов.

МОБР(массив) – Возвращает обратную матрицу для квадратной матри-

цы, хранящейся в массиве. Все ячейки массива должны содержать значения

(иначе ошибка #ЗНАЧ!.

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

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

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

56

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

1.Заполнить столбец А данными как показано на рис.4.8.

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

3.В ячейку С4 (соответственно в диапазон С4:С20) ввести формулу:

=ЕСЛИ(B4=1;"Данная точка делится без остатка на 3";"").

Пользовательский формат для диапазона С4:С20: ;;[Белый]

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

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

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

Упражнение 3. Удвоить числа, расположенные на нечётных местах од-

номерного массива, и определить их сумму (рис. 4.9). Для выполнения задания:

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

2.В ячейку В4 ввести формулу:

=ЕСЛИ(ОСТАТ(СТРОКА(A4);2)<>0;2*A4;0),

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

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

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

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

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

57

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

Рис. 4.10 Подсчёт отрицательных элементов массива Для выполнения задания:

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

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

Упражнение 5. Заданы матрицы A, B, C, D размерности 3х4. Найти

5A cos(B) C2 D (рис. 4.11).

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

58

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

1. Поместить данные матриц A, B, C, D соответственно в диапазоны

A2:D4, A7:D9, A12:D14, A17:D19.

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

3.Командой Вставка Объект на вкладке Новый выбрать Тип объек-

та – Microsoft Equation 3.0 и вставить формулу 5A cos(B) C2 D. 4. В ячейку F7 ввести формулу:

=5*A2:D4-COS(A7:D9)+СТЕПЕНЬ(A12:D14;2)-A17:D19

и нажать клавишу Enter.

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

мул и нажать одновременно клавиши Ctrl+Shift+Enter.

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

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

эффициентов, В – вектор-столбец свободных членов, Х – вектор-столбец неиз-

вестных, имеет вид X A 1B, где A 1 – матрица, обратная к матрице А. Это вытекает из того, что при решении матричных уравнений при Х должна остать-

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

лучаем решение линейной системы уравнений.

Упражнение 6. Найти решение системы линейных уравнений A2X B,

значения соответствующих матрицы и вектора-столбца имеют вид:

23	7		3
A	4	,	B .
11			2

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

1.Значения матрицы А поместить в ячейки А4:В5 (рис.4.12), а значения столбца свободных членов – в ячейки D4:D5.

2.Выделить диапазон A8:B9, в ячейку A8 ввести формулу:

=МУМНОЖ(A4:B5;A4:B5)

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

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

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

4. Для получения результатов решения системы линейных уравнений

следует перемножить полученную матрицу A 2 и столбец свободных членов В.

59

Для этого надо выделить диапазон A12:A13, в котором столько же строк,

сколько в первой матрице A 2 и столбцов, сколько во второй матрице В. В

ячейку А12 ввести формулу: =МУМНОЖ(D8:E9;D4:D5)

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

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

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

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

f (x) a0xn a1xn 1 ... an 1x an 0,

где n – некоторое положительное число, a0, ,an – произвольные комплекс-

ные числа, причём старший коэффициент a0 должен быть не равен нулю.

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

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

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

60

Число отрицательных корней многочлена равно числу сохранения зна-

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

Для отыскания корней уравнений произвольной степени в MS Excel не-

обходимо:

1.Произвести табулирование заданной функции на некотором интервале

сцелью выявления (локализации) корней уравнения (перемена знака в значе-

нии функции).

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

вис→Параметры и установить необходимые опции на вкладке Вычисления).

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

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

Упражнение 7. Найти все корни уравнения:

x5 2x4 5x3 8x2 7x 3 0

1. На отрезке [–10;10] выполнить приближённое табулирование функции f (x) x5 2x4 5x3 8x2 7x 3 0. Для этого в ячейки А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 (рис. 4.13).

2. Определить по результатам вычислений, что значение функции f (x)

меняет знак на отрезке [–3;1].

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

гом 0,1. Затем в ячейку E9 записать формулу:

=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.

4.Для вычисления 1 корня поместить указатель в ячейку D18 (либо D19)

ивыполнить команду Сервис→Подбор параметра (рис. 4.14). Получается 1

корень уравнения: x1 -2,07299558373219.