10.12. Ввод формул и исходных данных

Написание формул. Ввод в формулы адресов ячеек с данными

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

В программе Excel используется два способа ввода адресов ячеек с исходными данными:

• ввод адреса с клавиатуры;

• ввод щелчком мыши на адресуемой ячейке.

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

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

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

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

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

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

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

Заполнение ячеек данными

Д

Примеры заполнения ячеек
Содержимое первой ячейки	Содержимое последующих ячеек
Пн	Вт	Ср	Чт	…
Среда	Четверг	Пятница	Суббота	…
Апрель	Май	Июнь	Июль	…
а	а	а	а	…

ля автоматизации процесса заполнения таблиц в программе Excel предусмотрена специальная операция — Заполнение. Достаточно указать в первой ячейке (или в первых двух ячейках) образец заполнения, а затем поместить курсор мыши на маленький квадрат в правом нижнем углу заполненной ячейки (курсор при этом превращается в перекрестие) и расширить выделение при нажатой левой кнопке мыши вправо по строке или вниз по столбцу - на ячейки, которые должны быть заполнены. Excel самостоятельно продолжит ряд и заполнит указанную область.

Н

Примеры заполнения ячеек
Содержимое двух первых ячеек		Содержимое последующих ячеек
1990	1991	1992	1993	…
1	3	5	7	…
10	5	0	-5	…

апример, запишем в двух последующих ячейках столбца числа: 2 и 4; выделим эти ячейки и, ухватив мышкой угол нижней ячейки, растянем выделение на последующие ячейки столбца. Как только кнопка мыши будет отпущена, в стоблце появится последовательность четных чисел. При этом в процессе выделения области ячеек небольшое окошко сообщает о том, какое значение присваивается текущей ячейке.

Дополнительные примеры приведены в таблицах.

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

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

Заполнение ячеек формулами

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

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

Относительные и абсолютные ссылки на ячейки

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

Относительная адресация используется тогда, когда адреса, содержащиеся в порожденных заполнением формулах, изменяются от ячейки к ячейке. В этом случае Excel при порождении формул сохраняет не адрес ячейки, а ее положение относительно ячейки с результатом (относительно формулы). Пример: если в ячейке В5 есть формула, использующая данные ячейки с адресом А5, то Excel отмечает у себя не адрес А5, а то, что нужное значение находится в соседней ячейке слева. Если заполнить той же формулой следующую ниже ячейку, т.е. ячейку В6, то здесь в вычислениях будет использовано значение из ячейки А6.

Если адрес в формуле является абсолютным, то при заполнении формулой других ячеек данный адрес не изменяется, и во всех формулах, порожденных Excel в рамках процедуры заполнения, будет использоваться один и тот же адрес. Для того чтобы программа Excel могла различать относительные и абсолютные адреса, в абсолютных адресах ставится символ $ (знак доллара) перед буквой столбца и номером ячейки. Например, адрес ячейки с абсолютной адресацией может выглядеть так: $А$3.

Для того чтобы изменить тип адресации достаточно добавить или, наоборот, удалить символы $ из формулы в строке формул. Другой способ заключается в позиционировании курсора в строке формул на соответствующий адрес и нажатии клавиши [F4] несколько раз, пока не установится требуемый тип адресации.