Формулы. Абсолютная и относительная адресация
Начнем рассмотрение вычислений в ЭТ с простой задачи. Пусть имеется список из 7 человек, для каждого из которых известны фамилия, инициалы, должность, оклад за день работы и число отработанных дней. Требуется вычислить заработок каждого лица.
Формируем таблицу, начиная с ячейки A3, в соответствии с рис. 7. Для исправления ошибок в ячейках электронной таблицы используется режим редактирования строки ввода, который включается клавишей <F2>. Завершение редактирования обеспечивается клавишами <ENTER> (с сохранением изменений) или <ESC> (без сохранения изменений).
Рис. 7. Исходные данные для примера вычислений
Если при вводе информации ширина ячейки представляется недостаточной, ее можно скорректировать после завершения ввода всех данных. В ЭТ есть возможность подобрать ширину столбца автоматически ("Формат/Столбец/Оптимальная ширина...").
Для вычисления заработка нужно просто перемножить попарно числа из третьей (C) и четвертой (D) колонок. Результаты вычислений должны быть в пятой колонке (E). Формулу (т.е. правила) для вычислений можно написать один раз, а потом скопировать. Формулу надо писать там, где должен появиться первый результат (E4, под заголовком "Заработок"). Переводим указатель активной ячейки в клетку E4 и нажимаем клавишу “=” (указание на начало ввода формулы). После этого щелкаем левой кнопкой по ячейке, в которой записан оклад за день (C4), нажимаем на клавиатуре знак операции (умножение – “*”) и щелкаем левой кнопкой по ячейке с количеством отработанных дней (D4), после чего нажимаем <ENTER>. В ячейке E4 появляется результат (1100), а переместив указатель активной ячейки на E4, в строке ввода можно увидеть формулу =C4*D4.
Теперь скопируем эту формулу в оставшиеся ячейки. Поместив указатель активной ячейки на E4, в главном меню выберем команду копирования ("Правка/Копировать"). После этого выделим ячейки E5:E11 и вызовем команду вставки ("Правка/Вставить"). Результаты - рис. 8.
Рис. 8. Результаты вычисления и копирования формулы.
Если изменить какие-то числа в столбцах C и D, то числа в столбце E будут автоматически пересчитываться.
Перемещая указатель активной ячейки по столбцу E, можно заметить, что адреса ячеек в расчетной формуле изменяются. Это происходит потому, что в нашей формуле использованы относительные адреса ячеек. Формула просто перемножает содержимое ячеек, находящихся слева от ячейки с результатом. Таким образом, формула "запомнила" взаимное расположение ячеек с данными и с результатом и при копировании это взаимное расположение сохраняется. Это очень полезное свойство ЭТ, избавляющее от необходимости писать одну и ту же формулу много раз.
Если в какой-либо ячейке расчетного столбца (столбца "Заработок") перейти в режим редактирования (<F2>), то можно увидеть формулу и выделенные цветом ячейки, содержащие данные для формулы (рис. 8).
На следующем этапе посчитаем налог на доходы физических лиц, который будет начислен на рассчитанные ранее значения заработка. Пусть ставка налога фиксирована и составляет 13%. Тогда наша таблица дополняется в соответствии с рис. 9.
Рис. 9. Добавление параметра для вычислений.
Сумму налога легко сосчитать по правилу "Сумма налога = заработок*ставка_налога". Указав соответствующие адреса ячеек, в ячейке F4 записываем формулу =E4*D1 и копируем ее во все оставшиеся ячейки. При этом получается неожиданный результат (рис. 10).
Рис. 10. Неправильная организация вычислений с параметром.
В этом случае использование относительной адресации привело к ошибке – запомнив взаимное расположение ячеек результата и исходных данных (заработка первого в списке и ставки налога) программа ЭТ повторяет это взаимное расположение для остальных строк списка (в чем можно убедиться, войдя в режим редактирования, как показано на рис. 5а). Чтобы не создавать дополнительный столбец с одним и тем же значением ставки налога, в соответствующей формуле надо использовать абсолютный адрес ячейки, содержащей параметр (в данном случае – значение ставки налога). Для указания абсолютного адреса к букве столбца или номеру строки добавляется префикс "$" и формула для расчета суммы налога приобретает вид =E4*$D$1 (для добавления символов "$" при редактировании формулы можно использовать комбинацию клавиш <Shift>+<F4>). Отредактировав формулу в ячейке F4, копируем ее снова в оставшиеся ячейки и получаем правильный результат (рис. 11).
Рис. 11. Правильная организация вычислений с параметром.
Итак, абсолютный адрес указывает программе ЭТ, что нужно всегда обращаться к одной и той же ячейке (если поставлено два префикса $), строке (если $ поставлен перед номером строки) или столбцу (если $ - перед буквой столбца). Использование абсолютных адресов позволяет работать с условно-постоянными величинами (ставка налога, курс валюты, текущая дата и пр.), причем их значения заносятся в таблицу только один раз, что экономит время и место.
Нужно заметить, что формулы позволяют связывать между собой не только ячейки в пределах одного листа, но и ячейки на разных листах документа. В результате можно строить "трехмерные " электронные таблицы.