2. Вычисления в Microsoft Excel

Вычисления в Microsoft Excel производятся на основе конкретных формул. В Microsoft Excel есть особая программа, упрощающая процесс создания формул. Это так называемый мастер функций, кнопка которого « ».

Задачи мастера функций:

• исключить некоторые типичные ошибки;

• давать по ходу дела подсказки и комментарии;

• вычислять промежуточные результаты.

При создании формул следует быть очень внимательным. Например:

• стоит поставить вместо двоеточия точку с запятой и интервал превращается в перечисление;

• если неправильно поставить скобку, функция будет неверно подсчитана;

• стоит неправильно адресоваться, можно получить деление на ноль или умножение на текст.

Виды ошибок в Microsoft Excel:

• # DEL/0!

Адрес делителя задан неверно, т.е. ваша ячейка пуста.

• # ИМЯ !

Неизвестно имя или адрес в вашей формуле. Обычно происходит при вводе русских букв в адресе нужной ячейки.

• # ЗНАЧ !

  1. в качестве аргумента используется текст вместо числа или даты;

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

     • # Ссылка !

Ячейка, к которой вы обращаетесь в данной формуле, удалена.

• # ЧИСЛО !

вы пытаетесь извлечь квадратный корень из отрицательного числа, ввели не все обязательные аргументы функции, или же результат ваших вычислений слишком велик (слишком мал) и не может быть представлен в Microsoft Excel .

Есть и другие менее распространенные виды ошибок.

3. Функции Microsoft Excel

При нажатии на « », мы видим список всех существующих в Microsoft Excel категорий функций:

• 10 последних использовавшихся функций;

• полный алфавитный перечень;

• финансовые (20 функций)

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

• дата и время

большинство ее функций ведает преобразованиями даты и времени в различные форматы;

• математические

одна из самых многочисленных; ≈60 функций;

• статистические (≈70 функций);

• ссылки и массивы;

• работа с базой данных;

• текстовые;

• логические.

Рассмотрим некоторые функции (команды), находящиеся в категории логические:

1. команда «ЕСЛИ» позволяет организовывать различные разветвления.

Ее формат: =ЕСЛИ (логическое условие; когда верно; когда неверно).

Например: =ЕСЛИ (D1*B5>0; «Отлично!»; «Увы…»).

2. Команда «И» позволяет задать несколько условий; все условия должны быть выполнены, и только в этом случае ваша команда примет значение «истина», иначе – «ложь».

Ее формат: И (логическое условие 1; логическое условие 2; …).

Может быть до 30 логических условий.

Например: =ЕСЛИ (И(G1>G2;A2-B2<0); «Да»; «Нет»).

3. Команда «ИЛИ» тоже задает несколько условий, но в отличие от команды «И» достаточно, чтобы хотя бы одно условие было истинным. В этом случае общее условие истинно.

Задача: требуется с помощью Microsoft Excel найти один корень уравнения вида F(x)=0 с помощью метода половинного деления (например: ).

Реализация алгоритма в Microsoft Excel:

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

AA

BB

VC

DD

EE

FF

GG

HH

I

J

K

L

M

Ш1

aa

bb

cc

EEps

FF(a)

FF(b)

FF(c)

F(a)*F(c)

F(b)*F(c)

b-a

Результат

Наличие корня на интервале [a;b]

22

2. задаем исходные данные:

а) в ячейку А2 вводим левую границу интервала (а);

б) в ячейку В2 вводим правую границу интервала (b);

в) в ячейку Е2 вводим точность вычисления (Eps).

3. нахождение середины отрезка:

в ячейку С2 вводим следующую формулу: =(B2+A2)/2.

4. вычисления:

• F(a). Для этого в ячейку F2 вводим: =КОРЕНЬ (А2+cos(2*A2))-3;

• F(b), F(c). Вычисляем с помощью копирования формулы из ячейки F2;

• F(a)*F(c). Для этого в ячейку I2 записываем формулу: =F2*H2;

• F(b)*F(c). В ячейку J2 вводим: =G2*H2;

• (b-a). В ячейку К2 вводим: =В2-А2.

5. определяем наличие корня на текущем интервале. В ячейку М2 вводим следующую формулу:

=ЕСЛИ (ИЛИ (I2<0; J2<0); “ЕСТЬ”; “НЕТ”).

Если в ячейке M2 появится слово “НЕТ”, то меняем значения a или b; если же слово “ЕСТЬ”, то мы продолжаем вычисления.

6. определение достижения шага, которое дает решение с заданной точностью ε.

В ячейку L2 вводим следующую формулу: =ЕСЛИ (К2>$E$2; “ “; “Корень найден с точностью Eps”).

Если мы увидели данный текст, прекращаем вычисления; если нет, то продолжаем итерации, т.е. переходим на пункт 7.

7. реализация итерационных вычислений

В ячейку А3 вводим: =ЕСЛИ ($I2<0; $A2; $C2).

В ячейку В3 вводим: =ЕСЛИ ($I2>0; $B2; $C2).

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

8. выполнение итераций происходит до момента получения информационного сообщения в столбце L: «Корень найден с точностью Eps».