Ввод Формул

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

 

Все формулы Excel начинаются со знака равенства (=).

=45+56-27

Давайте рассмотрим пример ввода этой формулы в ячейку В2.

Сделайте ячейку В2 активной.

 

Введите формулу как показано на рисунке.

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

 

Посмотрите на рисунок. Мы вводили в ячейку математическое выражение, но после завершения ввода в ячейке появляется результат вычисления – 74. При этом в строке формул мы видим запись математического выражения.

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

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

Давайте рассмотрим применение ссылок в формулах на примере.

Введите в ячейки значения как показано на рисунке. 

 

В ячейке D2 нам необходимо просуммировать значения трёх ячеек, находящихся слева. 

1. В ячейке D2 введите знак равенства «=».

 

2. Установите указатель мыши на ячейку А2 и один раз щёлкните левой кнопкой мыши. В ячейке D2 после знака равно автоматически появится ссылка на ячейку А2.

 

3. Введите знак «+» (плюс).

4. Установите указатель мыши на ячейку В2 и один раз щёлкните левой кнопкой мыши. В ячейке D2 после знака плюс автоматически появится ссылка на ячейку В2.

 

5. Введите знак «+» (плюс).

6. Установите указатель мыши на ячейку С2 и один раз щёлкните левой кнопкой мыши. В ячейке D2 автоматически появится ссылка на ячейку С2. 

 

Завершите ввод формулы, нажав клавишу <Enter>.

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

 

В ячейке D2 появляется результат вычисления – число 60. По виду оно ничем не отличается от содержимого ячеек, расположенных слева. Однако в этой ячейке находится формула, а в остальных ячейках числовые значения.

Запомните очень важное правило: 

ЕСЛИ ВЫ СОБИРАЕТЕСЬ ИЗМЕНИТЬ ЧИСЛОВОЕ ЗНАЧЕНИЕ В ЯЧЕЙКЕ, ВСЕГДА ОБРАЩАЙТЕ ВНИМАНИЕ НА СТРОКУ ФОРМУЛ В ЯЧЕЙКЕ МОЖЕТ НАХОДИТЬСЯ ФОРМУЛА.

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

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

В вышеприведённом примере введите в ячейку А2 число 25.

 

В ячейке D2 автоматически появится новый результат сложения.

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

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

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

 

1. Установите указатель мыши в правый нижний угол копируемой ячейки. Указатель примет вид чёрного крестика.

2. Нажмите левую кнопку мыши и, удерживая её нажатой, переместите указатель мыши вниз, как показано на рисунке.

3.    Отпустите левую кнопку мыши.

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

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

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

Введите числовые значения в ячейки А3, В3 и С3. В ячейке D3 появится результат сложения.

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

 

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

Ячейка или диапазон	Запись
Ячейка в столбце A и строке 5	A5
Диапазон ячеек: столбец А, строки 10-17	A10:A17
Диапазон ячеек: строка 23, столбцы А-E	А23:E23
Все ячейки в строке 3	3:3
Все ячейки в строках с 6 по 18	6:18
Все ячейки в столбце В	В:В
Все ячейки в столбцах с А по Е	А:Е
Диапазон ячеек: столбцы А-E, строки 10-20	A10:E20

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

Давайте рассмотрим конкретный пример. Цена на товар указана в долларах США. Известен курс на момент продажи. Необходимо рассчитать цену в украинских гривнах.

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

 

В ячейке D3 перемножается значение ячеек В3 и С3. 

D3 перемножается значение ячеек В3 и С3.

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

 

Этот тип ссылки называется относительной. Относительная ссылка основана на относительной позиции ячейки, содержащей формулу и ячейки, на которую указывает ссылка. Формулу в ячейке D3 программа прочитает следующим образом: содержимое второй ячейки слева (В3) умножить на содержимое ячейки слева от ячейки с формулой (С3). 

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

Для того чтобы сделать ссылку абсолютной, необходимо ввести знаки доллара перед названием столбца и номером строки, например $A$1.

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

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

 

1. Введите в ячейки D1 «Курс $, грн.», в ячейку Е1 – «5,05» как показано на рисунке.
2. В ячейку Е2 скопируйте содержимое из ячейки D2 – «Цена, грн.»
3. Введите в ячейку Е3 формулу, как показано на рисунке (=В3*Е1). Справа от единицы мигает курсор.

4. На клавиатуре нажмите клавишу <F4> в самом верхнем ряду. Ссылка на ячейку Е1 изменится. Нажимайте клавишу <F4>, пока в ячейке не появится значение - $E$1.

 

Перед именем столбца и номером строки появятся значки $.

Для завершения ввода в ячейку формулы нажмите клавишу <Enter> на клавиатуре.

Установите указатель мыши в правый нижний угол ячейки Е3 и, перетаскивая маркер заполнения, скопируйте формулу в ячейки Е4 и Е5.

Ссылка на ячейку Е1 не изменилась.

 

Теперь при изменении курса доллара в ячейке Е1 цена в столбце Е будет пересчитываться автоматически. Для пересчёта цены в столбце D необходимо ввести новый курс в каждую ячейку столбца С. А представьте, если бы у нас было несколько тысяч записей.  Однако и в этом случае мы можем использовать абсолютную ссылку. Достаточно в ячейку С3 ввести формулу (=$Е$1) и скопировать формулу в ячейки С4 и С5. Теперь при изменении курса в ячейке Е1 цены будут пересчитываться автоматически и в столбце D и в столбце Е.

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

Кроме абсолютных и относительных ссылок бывают ещё смешанные ссылки.

Смешанная ссылка содержит либо абсолютный столбец и относительную строку ($В1), либо абсолютную строку и относительный столбец (B$1).

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