Абсолютные и относительные адресации (стр. 2 из 3)

· В результате формула содержит абсолютный адрес диапазона.

Рис.2.

· Скопируем эту формулу в соседнюю ячейку В7. Как видно из рисунка адрес диапазона не изменился, и сумма будет считаться по Стенду № 1.

Рис.3.

Часто абсолютная адресация указывается для ячеек, хранящих какое-то постоянное число, используемое в расчетах. Например, стоимость 1 КВт/час для оплаты электроэнергии.

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

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

А во втором – перед буквами ($B2:$B13); область исходных данных будет перемещаться только между строками, оставаясь в одних и тех же столбцах.

4. Относительная адресация

Адреса ячеек или диапазонов, как вы уже видели, используются в формулах. Ссылки в формулах на ячейки выглядят как A5, D12 и т.п. а на диапазон ячеек – например, как C3:F9. Это – так называемые относительные адреса ячеек и диапазонов.

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

Рис.4

Рис.5.

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

Рис.6.

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

5. АБСОЛЮТНАЯ И ОТНОСИТЕЛЬНАЯ АДРЕСАЦИЯ КАК ОСНОВА ДЛЯ РАСЧЕТА ФОРМУЛ

Начнем рассмотрение вычислений в ЭТ с простой задачи. Пусть имеется список из 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>), то можно увидеть формулу и выделенные цветом ячейки, содержащие данные для формулы (рис. 3).

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

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

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

Рис. 10. Неправильная организация вычислений с параметром.

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

Рис. 11. Правильная организация вычислений с параметром.

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

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

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

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

Затем введем цены в условных единицах.

В ячейку С4 введем формулу :=И4*$C$2 (набором символа $ с клавиатуры или нажатием клавиши [F4], которая используется для пересчета цены из условным единиц в рубли.

Рис.12.

При вводе нового курса, цены пересчитывается автоматически.

Рис.13.

Мы научились форматировать документ OOo Calc. При это м использовали такие средства, как изменение ширины столбцов, объединение ячеек, управление выравниванием текста, создание рамок ячеек. Мы выяснили, что в готовом документе заданные и выявленные ячейки отображаются одинаково.

4. ВСТРОЕННЫЕ ФУНКЦИИ И "МАСТЕР ФУНКЦИЙ"

Программа электронной таблицы OOo Calc позволяет использовать несколько сотен встроенных функций различных категорий.

Каждая функция имеет скобки, в которых записываются аргументы функции (например SIN(A8), PRODUCT(число1;число2;…), PI() ). В электронной таблице в качестве аргумента может быть указано число, адрес ячейки, диапазон адресов ячеек, другая функция или не указано ничего (как в функциях PI() или TRUE() ).

Функции участвуют в формулах для вычислений. Для построения формул с функциями в ЭТ OOo Calc имеется система автоматизированного построения формул ("Мастер функций"). Вызывается эта система через команды меню "Вставка/Функция..." или нажатием на кнопку f(x) на панели инструментов программы ЭТ. Вариант окна для выбора функций показан на рис. 12. Для упрощения выбора функции сгруппированы по категориям.