§3.2 Организация вычислений в электронных таблицах

Основным назначением электронных таблиц является организация всевозможных вычислений. Вы уже знаете, что:

- вычисление — это процесс расчёта по формулам;

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

5.2.1. Относительные, абсолютные и смешанные ссылки

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

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

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

1)относительные — зависящие от положения формулы;

2)абсолютные — не зависящие от положения формулы.

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

Относительные ссылки.

Задание 1: создайте книгу электронной таблицы. В созданной книге выполните следующие действия:

1) В ячейку В2 введите формулу: =С2+1

2) Скопируйте данную формулу

3) Вставьте скопированную формулу в ячейки В3, В4, В5, В6, В7 и D2, Е2, F2, G2.

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

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

При копировании формулы вдоль столбца и вдоль строки относительная ссылка автоматически корректируется так:

- смещение на один столбец приводит к изменению в ссылке одной буквы в имени столбца;

- смещение на одну строку приводит к изменению в ссылке на единицу номера строки.

Абсолютные ссылки.

Задание 2: в созданной книге электронной таблицы на 2 листе выполните следующие действия:

1) В ячейку В2 введите формулу: =$С$2+1

2) Скопируйте данную формулу

3) Вставьте скопированную формулу в ячейки В3, В4, В5, В6, В7 и D2, Е2, F2, G2.

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

Абсолютная ссылка в формуле всегда ссылается на ячейку, расположенную в определённом (фиксированном) месте. В абсолютной ссылке перед каждой буквой и цифрой помещается знак $, например $А$1. При изменении позиции ячейки, содержащей формулу, абсолютная ссылка не изменяется. При копировании формулы вдоль строк и вдоль столбцов абсолютная ссылка не корректируется.

Смешанные ссылки.

Задание 3: в созданной книге электронной таблицы на 3 листе выполните следующие действия:

1) В ячейку В2 введите формулу: =$С2+A$1

2) Скопируйте данную формулу

3) Вставьте скопированную формулу в ячейки В3, В4, В5, В6, В7 и D2, Е2, F2, G2.

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

Смешанная ссылка содержит либо абсолютно адресуемый столбец и относительно адресуемую строку ($А1), либо относительно адресуемый столбец и абсолютно адресуемую строку (А$1). При изменении позиции ячейки, содержащей формулу, относительная часть адреса изменяется, а абсолютная часть адреса не изменяется.

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

5.2.2. Встроенные функции

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

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

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

- СУММ (SUM) — суммирование аргументов, указанных в скобках, или суммирование аргументов диапазона, указанного в скобках;

- МИН (MIN) — определение наименьшего значения из списка аргументов;

- МАКС (МАХ) — определение наибольшего значения из списка аргументов

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

5.2.3. Логические функции

При изучении предшествующего материала вы неоднократно встречались с логическими операциями НЕ, И, ИЛИ (NOT, AND, OR). Построенные с их помощью логические выражения вы использовали при организации поиска в базах данных, при программировании различных вычислительных процессов.

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

Например, логическое выражение, соответствующее двойному неравенству 0<А1<10, в электронных таблицах будет записано как И(А1>0, А1<10).

Условная функция

Для проверки условий при выполнении расчётов в электронных таблицах реализована логическая функция ЕСЛИ (IF), называемая условной функцией.

Условная функция имеет следующую структуру:

ЕСЛИ (<условие>; <действие1>; <действие2>)

Здесь <условие> — логическое выражение, т. е. любое выражение, построенное с помощью операций отношения и логических операций, принимающее значения ИСТИНА или ЛОЖЬ. Если логическое выражение истинно, то значение ячейки, в которую записана условная функция, определяет <действие1>, если ложно - <действие2>*.

Что напоминает структура условной функции?