Часть 2. Формулы и функции

Краткие теоретические сведения

I. Формулы

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

- возведение в степень и выражения в скобках;

- умножение и деление;

- сложение и вычитание.

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

II. Изменение формул

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

III. Перемещение и копирование формул

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

При копировании формула перемещается в другое место таблицы, при этом абсолютные ссылки не изменяются, а относительные ссылки изменяются. При копировании формул можно управлять изменением адресов ячеек или ссылок. Если перед всеми атрибутами адреса ячейки поставить символ ―$‖, то это будет абсолютная ссылка. Изменятся только те атрибуты адреса ячейки, перед которыми не стоит символ ―$‖(относительные ссылки). Для быстрой установки $‖ в ссылке ее нужно выделить в формуле и нажать F4.

IV. Распространение формул

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

1. Установите курсор в ячейку с формулой.

2. Подведите указатель мыши к маркеру заполнения. Изображение указателя изменяется на черный крестик.

3. Нажмите левую кнопку мыши и, удерживая ее нажатой, перемещайте курсор до нужного места.

4. Для завершения распространения формулы отпустите кнопку.

Когда формула для ячейки не может быть правильно вычислена Excel выводит в ячейку значение ошибки. Если формула содержит ссылку на ячейку, которая содержит значение ошибки, то эта формула также будет выводить значение ошибки.

V. Функции в Excel

Функции Excel — это специальные, заранее созданные формулы для сложных вычислений, в которые пользователь должен ввести только аргументы.

Функции состоят из двух частей: имени функции и одного или нескольких аргументов. Имя функции описывает операцию, которую эта функция выполняет, например, СУММ.

Аргументы функции - задают значения или ячейки, используемые функцией, они всегда заключены в круглые скобки. Открывающая скобка ставится без пробела сразу после имени функции. Даже если функция не имеет аргументов, она все равно должна содержать круглые скобки. При использовании в функции нескольких аргументов они отделяются один от другого точкой с запятой. Найти нужную функция можно, выбрав «Вставить функцию» на вкладке «Формулы».

Упражнение 3.

1. Создала новую книгу в Excel.

2. Используя возможности автозаполнения в ячейках B2:B20 разместила элементы арифметической прогрессии с первым элементом «1», вторым «3»

3. Ввела в ячейку С1 число «2»

4. Ввела в ячейку С2 формулу вида =B2*С1

5. Навела курсор мыши на правый нижний угол ячейки с введенной формулой и распространила ее на диапазон ячеек С3:С20

6. Просмотрела получившийся результат и убедилась, что при вводе формулы происходит автоматическое изменение индексов

7. В ячейку D1 ввела число «3»

8. Ввела в ячейку D2 формулу вида =B2*$D$1

9. Навела курсор мыши на правый нижний угол ячейки с введенной формулой и распространила ее на диапазон ячеек D3:D20

10. Просмотрела получившийся результат и убедилась, что при вводе формулы происходит автоматическое изменение номера строки вектора B при этом всегда в качестве второго сомножителя выступает содержимое D1

11. В ячейку E1 ввела число «4»

12. Ввела в ячейку E2 формулу вида =$B$2*$E$1

13. Навела курсор мыши на правый нижний угол ячейки с введенной формулой и распространила ее на диапазон ячеек E3:E20

14. Отобразила влияющие ячейки, для этого поместила курсор в ячейку С8 и выбрала пункт меню «Формулы», затем инструмент «Влияющие ячейки». Пронаблюдала полученный эффект. Повторила действие, последовательно помещая курсор в ячейки D8 и E8 и применяя функцию «Влияющие ячейки».

15. Убрала зависимости посредством меню «Формула», инструмент «Убрать стрелки»

16. Перешла на лист 2 к ячейке H2

17. Возьмем содержимое одной из ячеек листа 1. В строку формул ввела выражение =6* перешла в лист 1 и щелкнула левой кнопкой мыши по ячейке D14 и нажала Enter.

18. Сохранила книгу.

Упражнение 4.

1. Открыла файл «ПерваяКнига».

2. В ячейки H1, I1, J1 добавила текст «Среднее», «Минимальное», «Максимальное» соответственно.

3. В ячейки H2, I2 и J2 добавила функции, вычисляющие среднее значение (СРЗНАЧ), минимальный (МИН) и максимальный (МАКС) элементы среди ячеек с B2 по G2 соответственно.

4. Распространила данные функции на 2 строки вниз.

5. Сохранила полученные результаты.

Упражнение 5.

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

1. Создала новую книгу.

2. В блок ячеек B3:K3 внесла значения согласно табл. 8

3. С помощью функции СРЗНАЧ нашла выборочное среднее.

4. Вычислила стандартную ошибку среднего путём деления СКО выборки на квадратный корень из объёма выборки

5. Задавшись доверительной вероятностью p = 0.95, определила случайную погрешность путём произведения коэффициента Стьюдента t95%,N на стандартную ошибку среднего.

6. Задавшись верхней границей погрешности прибора равной 0.1, рассчитала полную погрешность результата измерения

7. Сохранила полученные результаты