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

4.3.3. Формулы и функции
Формулапредставляет собой выражение, по которому выполняется вычисление на листе Excel, формула начинается с символа равенства «=». Примеры формул: =5+2*3 ; =A1+B1*3 ; =(A1 > 0) OR (C3 > 1), где OR-логическая функцияИЛИ.Набор формулы осуществляется в строке ввода или в текущей ячейке и после нажатия Enter в строке ввода отображается введенная формула, а в текущей ячейке – результат вычисления по данной формуле. Таким образом, в общем случае формула – это выражение, состоящее из чисел, ссылок на ячейки (адресов ячеек), функций, арифметических и логических операций. Различают арифметические и логические формулы, результат вычисления арифметической формулы является числом, а логические формулы принимают только два значения: "Истина" или "Ложь".

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

^ Арифметические операторы служат для выполнения арифметических операций: +, -, *( умножение), /(деление), ^ (возведение в степень).

Операторы сравнения применятся для сравнения двух значений, а результат сравнения - логическое значение: «Истина» или «Ложь». Примерами данных операторов являются: =, >, <, >=,<=,<>(не равно).

^ Текстовый оператор конкатенации (амперсанд &) используется для объединения текстовых строк в одну, например ("Северный"&"ветер").

Логические операторы используются, когда математическая формула содержит условие, например: y = ax+b, если x =>0 или y=cx2, если х<0.

Логические формулы кроме операторов сравнения могут содержать специальные логические операторы: NOT - "НЕ", AND - "И", OR - "ИЛИ".

^ Функции. Под функцией понимаютзаранее определенную стандартную формулу, которая выполняет вычисления над заданными аргументами в указанном порядке и возвращает результат. Функции позволяют выполнять как простые, так и сложные вычисления. Например функция =СУММ(А1:А5) соответствует записи =А1+А2+А3+А4+А5 и состоит из имени и аргументов: СУММ - это имя функции, а А1:А5 – аргументы, заключаемые в круглые скобки. Функция ОКРУГЛ(В9;3) округляет число в ячейке В9, а параметр 3 определяет количество цифр округляемого числа. В некоторых случаях может потребоваться использование функции как одного из аргументов другой функции. Например, в формуле ЕСЛИ(СРЗНАЧ(А3:А9)>36;СУММ(В3:В9);0) функция СРЗНАЧ вложена в функцию ЕСЛИ для сравнения среднего значения нескольких чисел с числом 36.

В Excel имеются следующие основные виды функций:

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

Все функции имеют одинаковый формат записи, включающий имя функции и аргументы, например, формула для суммирования чисел из ячеек блока B5:E5имеет вид: =СУММ(B5:E5).

Выбор подходящей функции осуществляется в Excel с помощью специальной программы ^ Мастер функций. Для наглядности анализа результатов вычислений в таблице имеется возможность отображать связи между аргументами и результатами вычислений. Например, если ячейка В5 содержит формулу =А2+А5, то эти две ячейки являются влияющими на результат формулы в ячейке В5.

Для создания формул с использованием функций используют Мастер функций, вызываемый нажатием на кнопкуf_x в строке формул. В диалоговом окне Мастер функций- шаг 1 из 2 в раскрывающемся списке Категория необходимо выбрать категорию функции, затем в списке функций нужно выбрать функцию и нажать кнопку ОК После выбора функции появляется диалоговое окно Аргументы функции (рис. 4.10).

В поля аргументов диалогового окна следует ввести аргументы функции, в качестве которых могут быть ссылки на ячейки, числа, логические выражения и т. п. Адреса ячеек можно вводить с клавиатуры или использовать выделение диапазона ячеек мышью, числа и логические выражения в качестве аргументов, как правило, вводят с клавиатуры. В нашем примере диапазон адресов чисел (В2:Е2), после определения аргументов функции следует нажать кнопку ОК. Следует отметить, что некоторые функции могут не иметь аргументов.

Для более быстрого выполнения некоторых операций без запуска Мастера функций можно использовать кнопку Автосумма на панели инструментов Стандартная, обеспечивающую выполнение суммирования, поиска максимума, минимума, среднего из диапазона заданных адресов чиселОшибки в формулах Excel.Если формула построена неправильно, Excel формирует соответствующую ошибку, основными причинами появления которых следующие:

• #ЗНАЧ! - используется недопустимый тип аргумента;
• #ДЕЛ/0! - в формуле выполняется деление на ноль;
• #ИМЯ? - Excel не может определить используемое в формуле имя;
• #ССЫЛКА! - используется недопустимая ссылка на ячейку;
• #Н/Д - неопределенные данные, при некорректном определении аргументов функции;
• #ПУСТО! - задано пересечение двух областей, не имеющих общих ячеек.

Копирование формул

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

1. Щелкните на любой непустой ячейке в строке 10. Вы увидите, что выбранная формула ссылается на ячейки строки 2.
2. Выделите диапазон B10:F10.
3. Нажатием клавиш Ctrl+C скопируйте ячейки.
4. Щелкните на ячейке В11, а затем на кнопке Вставить панели инструментов Стандартная. Появится еще одна строка ячеек с формулами. Щелкните на любой из них и взгляните в строку формул. Вы увидите, что формулы новых ячеек ссылаются на данные строки 3, как показано на рис. 10.9.
5. Щелкните на ячейке В 12 и нажмите клавиши Ctrl+V, чтобы вставить еще одну строку. Формулы этой строки уже будут ссылаться на данные строки 4. Таким образом, номера ячеек данных изменяются ровно на столько клеток, на сколько смещается вставляемая формула.

Примечание

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

1. Вставьте тот же самый фрагмент в ячейки В13 и В14.
2. Скопируйте заголовки строк из ячеек А2:А6 в ячейки А10:А14.

Примечание

Обычно Excel формирует относительные ссылки на ячейки. Но иногда нужно сослаться в формуле на клетку с константой, которая не должна меняться при копировании формулы в другую ячейку или при перемещении исходных данных. В этом случае пользуйтесь абсолютными ссылками, которые отличаются от относительных наличием символа $ перед буквой столбца, номером строки или перед обеими этими характеристиками, например $Е$15. В такой ссылке не изменяется та часть, перед которой стоит знак $. Например, копируя формулу =СУММ($В$2:$0$6) (=SUM($B$2:$G$6)) в другую ячейку, вы получите тот же самый результат, что и в исходной клетке формулы.