Основы работы в Gnumeric

2.5 Формулы. Абсолютная и относительная адресация.

Формулы в электронных таблицах предназначены для вычислений значений в ячейках таблицы на основе данных, записанных в другие ячейки. Результатом работы формулы может быть число, дата, текст или отсутствие данных (т.е. после вычислений можно получать пустые ячейки). Формула записывается в той ячейке, в которой должен быть результат. Ввод формулы начинается с нажатия на символ "=" на клавиатуре, после чего вводятся числа, адреса ячеек и знаки операций. Например, формула "=3+5" даст всегда результат "8", а формула "=A3/12" будет давать различные результаты при изменении значения в ячейке A3.

Таким образом, при изменении содержимого ячеек, адреса которых используются в формулах, результаты пересчитываются автоматически. Эта особенность является ключевой для всех электронных таблиц. Из-за этого свойства крайне не рекомендуется использовать в формулах конкретные значения, если только они не являются неотъемлемой частью правил вычисления (например, если площадь круга вычисляется в евклидовой геометрии как , то двойка может использоваться в формуле вычисления площади круга, а вот конкретное значение радиуса – нет).

Рис. 2.45. Исходные данные для задачи о заработках

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

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

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

Формируем таблицу, начиная с ячейки A3, в соответствии с рис. 2.45. Для исправления ошибок в ячейках электронной таблицы используется режим редактирования строки ввода, который включается клавишей <F2>. Завершение редактирования обеспечивается клавишами <ENTER> (с сохранением изменений) или <ESC> (без сохранения изменений).

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

Для вычисления заработка нужно просто перемножить попарно числа из третьей (столбец C) и четвертой (столбец D) колонок. Результаты вычислений должны быть в пятой колонке (столбец E). С учетом возможностей ЭТ, формулу (т.е. правила) для вычислений можно написать один раз, а потом скопировать. Формулу надо писать там, где должен появиться первый результат (в нашем примере – в ячейке E4, под заголовком "Заработок"). Переводим указатель активной ячейки в клетку E4 и нажимаем клавишу "=" (указание на начало ввода формулы). После этого щелкаем левой кнопкой мыши по ячейке, в которой записан оклад за день (C4), нажимаем на клавиатуре знак операции (умножение – "*") и щелкаем левой кнопкой мыши по ячейке с количеством отработанных дней (D4), после чего нажимаем <ENTER>. В ячейке E4 появляется результат (число 1100), а переместив указатель активной ячейки на E4, в строке ввода увидим формулу =C4*D4

Рис. 2.46. Результат расчёта заработка

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

Если изменить какие-то числа в столбцах C и D, то числа в столбце E будут автоматически пересчитываться.

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

Если в какой-либо ячейке расчетного столбца (столбца "Заработок") перейти в режим редактирования (<F2>), то можно увидеть формулу. При перемещении текстового курсора по формуле будут подсвечиваться ячейки, содержащие данные для формулы (рис. 2.47).

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

Рис. 2.47. Просмотр формулы в режиме редактирования

Рис. 2.48. Таблица для вычислений с параметром

Сумму налога легко сосчитать по правилу "Сумма налога = заработок*ставка_налога". Указав соответствующие адреса ячеек, в ячейке F4 записываем формулу =E4*D1 и копируем ее во все оставшиеся ячейки. При этом получается неожиданный результат (рис. 2.49).

В этом случае использование относительной адресации привело к ошибке – запомнив взаимное расположение ячеек результата и исходных данных (первого заработка в списке и ставки налога) программа ЭТ повторяет это взаимное расположение для остальных строк списка (в чем можно убедиться, войдя в режим редактирования, как показано на рис. 2.49). Чтобы не создавать дополнительный столбец с одним и тем же значением ставки налога, в соответствующей формуле надо использовать абсолютный адрес ячейки, содержащей параметр (в данном случае – значение ставки налога). Для указания абсолютного адреса к букве столбца или номеру строки добавляется префикс $ и формула для расчета суммы налога приобретает вид =E4*$D$1 (для добавления символов $ при редактировании формулы можно использовать клавишу <F4>). Отредактировав формулу в ячейке F4, копируем ее снова в оставшиеся ячейки и получаем правильный результат (рис. 2.50).

Рис. 2.49. Неправильный результат вычислений с параметром

Рис. 2.50. Правильные вычисления с параметром

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

Итак, абсолютный адрес указывает программе ЭТ, что нужно всегда обращаться к одной и той же ячейке (если поставлено два префикса $), строке (если $ поставлен перед номером строки) или столбцу (если $ – перед буквой столбца). Использование абсолютных адресов позволяет работать с условно-постоянными величинами (ставка налога, курс валюты, текущая дата и пр.), причем их значения заносятся в таблицу только один раз, что экономит время и место.

Рис. 2.51. Автосуммирование по столбцу

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

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

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