Создание формул в Excel

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

Порядок ввода формулы

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

Выделим произвольную ячейку, например А1. В строке формул введем =2+3 и нажмем Enter. В ячейке появится результат (5). А в строке формул останется сама формула.

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

1. В первую очередь выполняются выражения внутри скобок.
2. Умножение и деление имеют более высокий приоритет чем сложение и вычитание.
3. Операторы с одинаковым приоритетом выполняются слева направо.

Мой вам совет - ИСПОЛЬЗУЙТЕ СКОБКИ. В этом случае вы обезопасите себя от случайной ошибки в вычислениях с одной стороны, а с другой - скобки значительно облегчают чтение и анализ формул. Если в формуле количество закрывающих и открывающих скобок не совпадает, Excel выдаст сообщение об ошибке и предложит вариант ее исправления. Сразу после ввода закрывающей скобки Excel отображает жирным шрифтом (или другим цветом) последнюю пару скобок, что очень удобно при наличии в формуле большого количества скобок.

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

Введите в ячейку А1 число 10, а в ячейку А2 - число 15. В ячейке А3 введите формулу =А1+А2. В ячейке А3 появится сумма ячеек А1 и А2 - 25. Поменяйте значения ячеек А1 и А2 (но не А3!). После смены значений в ячейках А1 и А2 автоматически пересчитывается значение ячейки А3 (согласно формулы).

Дабы не ошибиться при вводе адресов ячеек, можно использовать при вводе ссылок мышь. В нашем случае надо проделать следующее:

1. Выделить ячейку А3 и ввести в строке формул знак равенства.
2. Щелкнуть на ячейке А1 и ввести знак плюс.
3. Щелкнуть на ячейке А2 и нажать Enter.

Результат будет аналогичным.

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

Относительная ссылка указывает на ячейку, согласно ее положения относительно ячейки, содержащей формулу. Обозначение относительной ячейки - А1.

Абсолютная ссылка указывает на ячейку, местоположение которой неизменно. Обозначение абсолютной ячейки - $A$1.

Смешанная ссылка содержит комбинацию относительной и абсолютной ссылок - $A1, A$1.

Для быстрого изменения типа ссылки используется клавиша F4. Введите в ячейку А1 любое число. В ячейку А2 введите формулу =А1. Затем нажимайте клавишу F4. После каждого нажатия клавиши тип ссылки будет меняться.

 

 

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

Для начала разберем относительные ссылки. Введите в ячейки следующие значения:

• А1 - 20 В1 - 200
• А2 - 30 В2 - 300

В ячейку А3 введите формулу =А1+А2 и нажмите Enter.

Теперь установите курсор на правый нижний угол ячейки А3, нажмите правую кнопку мыши и протяните на ячейку В3 и отпустите кнопку мыши. Появится контекстное меню, в котором надо выбрать "Копировать ячейки".

После этого значение формулы из ячейки А3 будет скопировано в ячейку В3. Активизируйте ячейку В3 и посмотрите какая формула получилась - В1+В2. Почему так получилось? Когда мы записали в ячейке А3 формулу А1+А2, Excel интерпретировал эту запись так: "Взять значения из ячейки, расположенной в текущем столбце на две строки выше и сложить со значением ячейки, расположенной в текущем столбце на одну строку выше". Т.е. скопировав формулу из ячейки А3, например, в ячейку С43, мы получим - С41+С42. Вот в этом прелесть относительных ссылок, формула как бы сама подстраивается под наши задачи.

Рассмотрим теперь абсолютные ссылки.

Введите в ячейки следующие значения:

• А1 - 20 В1 - 200
• А2 - 30 В2 - 300
• Введите в ячейку С1 число 5.

В ячейку А3 введите следующую формулу =А1+А2+$C$1. Аналогичным образом скопируйте формулу из А3 в В3. Посмотрите что получилось. Относительные ссылки "подстроились" под новые значения, а вот абсолютная - осталась неизменной.

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

Например, чтобы записать в ячейку А1 (Лист 1) ссылку на ячейку А5 (Лист2), надо проделать следующее:

• выделить ячейку А1 и ввести знак равенства;
• щелкните на ярлыке "Лист 2";
• щелкните на ячейке А5 и нажмите клавишу ввода;
• после этого будет активизирован опять Лист 1 и в ячейке А1 появится следующая формула =Лист2!А5.

Ссылка на ячейку из другой книги будет выглядеть так - =[Книга2]Лист2!А5.

Редактирование формул происходит аналогично редактированию текстовых значений в ячейках. Т.е. надо активизировать ячейку с формулой выделением или двойным щелчком мыши, а затем произвести редактирование, используя, при необходимости, клавиши Del, Backspace. Фиксация изменений выполняется клавишей Enter.

Использование текста в формулах

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

Цифры от 0 до 9 , + - е Е /  

Еще можно использовать пять символов числового форматирования:

$ % ( ) пробел

При этом текст должен быть заключен в двойные кавычки.

Неправильно: =$55+$33

Правильно: ="$55"+$"33"

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

Для объединения текстовых значений служит текстовый оператор & (амперсанд). Например, если ячейка А1 содержит текстовое значение "Иван", а ячейка А2 - "Петров", то введя в ячейку А3 следующую формулу =А1&А2, получим "ИванПетров".

Для вставки пробела между именем и фамилией напишите так =А1&" "&А2.

Амперсанд можно использовать для объединения ячеек с разными типами данных. Так, если в ячейке А1 находится число 10, а в ячейке А2 - текст "мешков", то в результате действия формулы =А1&А2, мы получим "10мешков". Причем результатом такого объединения будет текстовое значение.