Применение формул

Применение формул

Применение формул

Назначение формул

Операторы формул

При создании примеров уже применяли простейшие формулы. Рассмотрим правила составления формул. Длина формул не может превышать 1 024 символа. Формула всегда начинается со знака равенства (=). Символ равенства должен быть только один. Перед и/или после символа равенства не должно быть пробелов. В формулах могут применяться следующие операторы (Таблица 1):

Таблица 1. Операторы Excel

Оператор	Название оператора
+	Сложение
-	Вычитание
*	Умножение
/	Деление
^	Возведение в степень
&	Конкатенация (склеивание)
=	Логическое сравнение «равно»
>	Логическое сравнение «больше чем»
<	Логическое сравнение «меньше чем»
>=	Логическое сравнение «больше чем или равно»
<=	Логическое сравнение «меньше чем или равно»
<>	Логическое сравнение «не равно»

Примеры применения формул:

=В3*С3  умножение двух чисел в ячейках В3 и С3;

=D5^2  число в ячейке D возводится в квадрат;

=(B2*C2)/D2  числа в ячейках B2 и C2 умножаются друг на друга и результат делится на число в ячейке D2. В данном примере круглые скобки можно было бы не применять, так как B2 и C2 и так будут умножаться перед делением на D2, но и применение скобок также не будет считаться ошибкой;

=B3*C3  расчет процента С3 от числа в ячейке В3. Число C3 должно обязательно быть форматировано в виде процентного формата. Если C3 не форматирован как процентный формат, то формула должна была бы быть: B3*C3/100;

=A4&B4&С4  конкатенация или склеивание, или сцепление нескольких текстовых строк, например, в ячейке А4 находится текст «Маша», в ячейке В4 находится «+», в ячейке С4 текст «Саша», в результате получится «Маша+Саша». Если находится текст соответственно в ячейках А4, В4 и С4: 12, 34, 56, то получится 123456.

Приоритеты операторов

Каждый оператор имеет свой приоритет выполнения при прочих равных условиях. Эти приоритеты можно свести в таблицу (Таблица 2):

Таблица 2. Приоритеты операторов

Оператор	Приоритет	Название оператора
^	1	Возведение в степень
*	2	Умножение
/	2	Деление
+	3	Сложение
-	3	Вычитание
&	4	Конкатенация (склеивание)
=	5	Логическое сравнение «равно»
<	5	Логическое сравнение «больше чем»
>	5	Логическое сравнение «меньше чем»
>=	5	Логическое сравнение «больше чем или равно»
<=	5	Логическое сравнение «меньше чем или равно»
<>	5	Логическое сравнение «не равно»

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

=(В4-С4)*D4

=(B4+C4)*(D4-E4)

=((B4+C4)+2)*(D4-(E4+5))

=((((((A5+B5)+C5)-D5)*2)/3)-E5)*0,13

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

Ввод формул

Ввод формулы осуществляется в той ячейке, в которой необходимо отразить результат расчета, сцепления строк, логических действий. Не нужно бояться, что программа перепутает формулу с данными — символ равенства перед формулой (=) объясняет программе Microsoft Excel, что вводится именно формула, а не данные. Символ = вводится только один. Пробел ни перед символом =, ни после него не нужен. Если после ввода формулы появляется сообщение об ошибке, которые всегда имеют первым символом знак #, то необходимо проанализировать — в чем вы допустили ошибку и исправить ее.

Кроме ручного способа ввода формулы существует способ путем указания. Это, если так можно выразиться, полуручной способ ввода формулы. Опробуем этот способ на файле Книга1 (Рис.  1).

Итак, для ввода формулы путем указания выделите ячейку F6 и посмотрите на полосу состояния. В левой части находится надпись Готово. В ходе работы мы будем ориентироваться по надписям, которые будут сменяться в этом индикаторе полосы состояния. Введите в ячейку F6 символ равно (=). На полосе состояния слово Готово сменится словом Ввод. Щелчком мыши перейдите в ячейку В6. При этом ячейка, по которой был выполнен щелчок, будет окружена пунктирной бегущей рамкой. После того, как вы пометили ячейку В6, введите с клавиатуры символ умножения (*). Обратите внимание, что пунктирная рамка вокруг ячейки В6 исчезла, став цветной, а курсор переместится в ячейку F6. Щелчком мыши перейдите в ячейку С6. Нажмите на клавишу Enter на клавиатуре. В ячейке F6 будет то же значение, что и в ячейке D6. Созданные формулы можно отредактировать так же, как и содержание любой ячейки с данными. Для редактирования формулы выделите ячейку, в которой находится формула и или нажмите на клавишу F2, или выполните двойной щелчок левой клавишей мыши по этой ячейке, или щелкните мышью в строку формул (мы всегда используем именно этот способ редактирования).

Ссылки в формулах

Виды ссылок

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

Разберем, чем же отличаются друг от друга эти ссылки.

Относительные ссылки при копировании формул автоматически изменяются в соответствии с новым положением формулы;

Абсолютные ссылки не меняются при копировании формул;

Смешанные ссылки являются наполовину абсолютными и наполовину относительными.

Если формулы с относительными ссылками мы создавали, то теперь создадим формулы с абсолютными и смешанными ссылками. Если с применением относительных ссылок мы использовали формулу следующего вида:

=B6*C6

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

=$B$6*$C$6

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

Смешанная ссылка может быть представлена так:

=$B$6*C6

=$B6*$C$6

=$B$6*C$6

=$B$6*$C$6

=B$6*$C$6

Относительную ссылку можно превратить как в абсолютную, так и в смешанную ссылку и, обратно, в относительную. Для того чтобы найти нужный вариант ссылки выделите ячейку F6. Поставьте курсор между символами В и 6, то есть в первом множителе и понажимайте несколько раз клавишу F4. Каждый раз ссылка В6 будет менять свой тип. Если выделить мышью всю формулу и опять понажимать клавишу F4, то теперь вся формула будет менять тип ссылки.

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

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

=B2*C2

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

=B3*C3

То есть к исходным адресам ячеек прибавилась 1.

Тем не менее, не всегда это нужно. Например, в следующем примере (Рис.  1) Итого в $ рассчитается с помощью относительных ссылок, а вот Итого в руб. так считать нельзя.

Рис.  1. Создание абсолютной ссылки

В ячейке E3 мы должны умножить Итого в $ на Курс. То есть формула будет следующей:

=D3*D1

В ячейке E3 все будет правильно, но как только мы начнем копировать в следующие ячейки, то в ячейке E4 формула будет такой:

=D4*D2

А что у нас находится в ячейке D2? Там находится текст, и формула будет равна 0. В других ячейках столбца D также будут ошибки. Как видно из этого примера, относительные ссылки в этом примере применять нельзя. Дело в том, что адрес ячейки D1 не может изменяться. Во всех формулах столбца D курс всегда должен браться из ячейки D1. То есть нам нужно «заморозить» в формуле ячейку D1. «Замораживание» адреса ячейки выполняется с помощью абсолютной ссылки. Для преобразования относительной ссылки в абсолютную, нужно нажать на клавишу F4 сразу же после включения адреса ячейки в формулу. То есть в нашем примере в ячейке E3 мы должны написать следующую формулу:

=D3*D1

Как только мы щелкнули по ячейке D1, адрес которой нужно сделать абсолютной, нужно нажать на клавишу F4. Сразу же после этого адрес D1 станет $D$1, то есть перед именем столбца и именем строки появились символы доллара. Никакого отношения к реальным долларам этот символ не имеет: просто так помечается абсолютная ссылка. Полная формула станет такой:

=D3*$D$1

Вся дальнейшая технология расчета такая же, как и при создании формул с относительными ссылками. То есть нажмите на клавишу Enter и скопируйте эту исходную формулу с помощью тонкого крестика ( @ [272]). Число абсолютных ссылок в одной формуле может быть любое (то есть не обязательно одна).