Абсолютные
и
относительные
адресации
Содержание
Введение
1. Интерфейс электронной таблицы
2. Обзор возможностей OpenOffice.org Calc
3. Абсолютная адресация
4. Относительная адресация
5. Абсолютная и относительная адресация как основа для расчета формул
6. Встроенные функции и «Мастер функций»
Заключение
Литература
ВВЕДЕНИЕ
Во многих коммерческих и финансовых приложениях основными информационными единицами, данные из которых постоянно используются,
обновляются, пополняются и обрабатываются, служат документы табличного типа.
Вот почему одной из первых была разработана информационная технология для манипулирования данными, представленными в табличной форме или, как часто говорят, в форме динамических таблиц.
Комплекс средств, состоящий из программных модулей и реализующий некоторую совокупность операций над табличными данными, принято называть табличным процессором (электронной таблицей – ЭТ). Последний стал непременным элементом автоматизации учрежденческой и управленческой деятельности.
Разработкой и распространением табличных процессоров (программ ЭТ) занимаются как коммерческие компании, так и группы разработчиков свободного программного обеспечения (точнее, проектов FOSS – Free and Open Source Software).
Один из самых известных проектов FOSS – интегрированный офисный пакет OpenOffice.org - также имеет в своем составе табличный процессор OpenOffice.org Calc (в дальнейшем, для краткости, будем использовать обозначение OOo Calc). Этот табличный процессор с успехом может использоваться для решения экономических и инженерно-технических задач.
В данном пособии рассматриваются некоторые типовые задачи и даются методические указания, полезные для практической работы с электронными таблицами OOo Calc. Для успешного выполнения рассматриваемых задач нужно обладать начальными знаниями о технологии работы с графическим пользовательским интерфейсом.
1. ИНТЕРФЕЙС ЭЛЕКТРОННОЙ ТАБЛИЦЫ
В интерфейсе программ ЭТ различают понятия “окно программы” и “рабочее поле программы”. Окно программы ЭТ устроено стандартным для графического интерфейса образом и содержит строку заголовка окна, строку главного меню, панели инструментов (как минимум, панели “стандартная” и “форматирование”), а также строку состояния (рис. 1). Нестандартным элементом окна, свойственным только программам ЭТ, является строка ввода, в которой отображается и редактируется помещенная в таблицу информация.
Слева от строки ввода расположены три важные кнопки: кнопка "=",
обеспечивающая переход в режим ввода формул, кнопка автосуммирования "" и кнопка вызова Мастера функций "f(x)".
Рис. 1. Элементы интерфейса электронной таблицы.
Рабочее поле программы представляет из себя совокупность листов, каждый из которых состоит из ячеек. Каждая ячейка имеет адрес, определяемый строкой и столбцом, на пересечении которых находится ячейка. Столбцы, как правило, обозначаются буквами латинского алфавита (1 или 2 буквы), строки – числами. Сетку, которая “расчерчивает” лист на ячейки, при желании можно сделать невидимой.
Всегда одна из ячеек является активной. Активная ячейка – это та ячейка, в которую в данный момент осуществляется ввод или содержание которой редактируется. Активная ячейка всегда выделена (обычно “жирной” рамкой). Перемещение указателя активной ячейки осуществляется клавишами-стрелками на клавиатуре или щелчком левой кнопкой мыши в любом месте листа. Ввод информации в позицию активной ячейки осуществляется либо полностью с помощью клавиатуры, либо (в случае построения формул с функциями) с помощью действий “вставка функции” ("Вставка/Функция...") из главного меню или панели инструментов.
По умолчанию в документе электронной таблицы листов немного (3 или 4).
Однако щелчок правой кнопкой мыши по ярлычку листа вызывает контекстное меню, с помощью которого листы можно добавлять, переименовывать, копировать, перемещать и удалять.
Слева от ярлычков листов имеются кнопки перемещения по листам. Пока листов мало, они неактивны.
В каждой ячейке ЭТ могут быть записаны числа, текст или формулы. Особым случаем числа является календарная дата. Числа автоматически выравниваются по правому краю ячеек, а текст – по левому. Содержание активной ячейки отображается в строке ввода, а для редактирования содержания активной ячейки надо нажать клавишу <F2> и после этого редактировать содержание строки ввода как текст.
Редактирование завершается нажатием на <ENTER>. Для удаления содержания ячейки (или блока ячеек) достаточно нажать клавишу <DEL>.
Каждая ячейка ЭТ имеет адрес, который записывается как комбинация буквы
столбца и номера строки (напр. A8, C67, X999). Для указания блока (диапазона) ячеек указываются адреса левой верхней и правой нижней ячеек через двоеточие (например, A3:C18, B1:B46, C3:C12).
Операции редактирования (копирования, перемещения, удаления и вставки) можно делать с ячейками или блоками ячеек (копирование, перемещение или удаление), со строками, столбцами, группами строк и столбцов (удаление или вставка). Перед операцией редактирования нужно выделить объект для выполнения операции. Выделение групп ячеек делается "протаскиванием" мыши с нажатой левой кнопкой от левого верхнего угла нужного блока до нижнего правого угла, а выделение строк и столбцов – щелчком левой кнопкой мыши по номеру строки (букве столбца).
Для выделения группы строк или столбцов тоже надо "протащить" указатель мыши. Каждый лист ЭТ OOo Calc может содержать до 245 столбцов и до 65000 строк (примерно), а в файле ЭТ может храниться до 256 таких листов. В каждую ячейку может быть записан текст или формула длиной до 256 символов.
Обратим внимание на нижний правый угол указателя активной ячейки. Этот угол выделен черным квадратиком. Если "зацепить" этот квадратик левой кнопкой мыши и протащить на нескольтко ячеек вбок или по вертикали, будет вызвана функция "Автозаполнение", которая во многих случаях упрощает создание последовательности данных или копирование формул.
2. ОБЗОР ВОЗМОЖНОСТЕЙ OpenOffice.org Calc
Как и остальные упомянутые программы (за исключением Excel), OpenOffice.org Calc определяет особое приложение языка разметки XML, которое и используется для хранения рабочих книг (почему-то workbook переводится как «рабочая книга», хотя вообще-то это обычная «тетрадь») с подшитыми в них листами электронных таблиц. Как и остальные компоненты OpenOffice.org, OpenOffice.org Calc упаковывает XML-файл с содержимым (а также ряд вспомогательных файлов) в PKZIP-архив, который и является единицей хранения документа.
Кроме «родного» формата, OpenOffice.org Calc «понимает» распространенный формат, используемый Excel разных версий, экспортирует данные в DIF (Data Interchange Format), форматы ранних версий StarCalc, потомком которых она является, SYLK, импортирует — также из форматов dBase и Lotus 1-2-3. Книгу (workbook) можно с очень приличным качеством экспортировать в гипертекст (html 3.2).
Текущая версия (1.0) OpenOffice.org Calc позволяет работать с отдельными таблицами (листами) размером до 255 столбцов (пронумерованных буквами и двухбуквенными сочетаниями, от A до IV) на 32000 строк (пронумерованных числами), чего вполне достаточно для большинства офисных применений и уж, во всяком случае, для любых разумных учебных задач. OpenOffice.org Calc допускает абсолютную и относительную адресацию ячеек и их диапазонов.
В OpenOffice.org Calc поддерживается типизация данных с возможностью их интерпретации как чисел, денежных сумм, дат, времени, логических значений и, наконец, просто текста. Возможны и определяемые пользователем типы. Для некоторых типов определены различные форматы представления, задающие способ их отображения или печати. В случае, если ячейка содержит формулу, ее результат также может быть типизован.
Библиотека функций OpenOffice.org Calc достаточно компактна — их около трех с половиной сотен. Она разбита на ряд категорий: управление БД, работа с датами и временем, финансы, статистика и т.п. Имеются средства расширения этого набора.
OpenOffice.org Calc реализует такие средства, как:
автозаполнение однородных рядов данных;
именование ячеек и их групп;
сортировка и фильтрация;
построение графиков и диаграмм.
Мощный механизм стилей оформления, свойственный всем компонентам пакета OO.o, доступен и в OpenOffice.org Calc. Стили оформления могут определяться для отдельных ячеек, их совокупностей, листов и рабочих книг в целом, а также для включаемых элементов, таких как текст или иллюстрации (в том числе, графики и диаграммы).
3. Абсолютная адресация
Табличные процессоры поддерживают и абсолютную адресацию. Абсолютные ссылки отличаются от относительных наличием знака доллара перед именем столбца и номером строки: $A$5. Адреса диапазонов тоже могут содержать абсолютные и смешанные ссылки: например, $A$5:$C$7, A2:$B$4, C2:$F$3 и т.п.
Еще бывают смешанные адреса: C$3, $F4. В смешанных адресах перед абсолютной частью также стоит знак доллара, т.е. знак $. (Например, в адресе C$3 адрес столбец C относительный, адрес строки 3 – абсолютный). Кроме того, абсолютной частью адреса можно сделать и название листа: $Лист1.$C$3:F$5.
Отличие абсолютных адресов от относительных в том, как их обрабатывает табличный процессор при копировании ячеек с формулами.
Рассмотрим пример с подсчетом суммы чисел в диапазоне. Введем формулу подсчета суммы и преобразуем ссылки в абсолютные адреса. Упростим ввод формулы. Для этого:
· Выделим ячейку В14 и нажмем кнопку в строке формул. Табличный процессор цветной рамкой выделит диапазон для подсчета. В формуле диапазон будет закрашен черным цветом.
· Преобразуем адрес диапазона в абсолютный. Для этого щелчком левой кнопки мыши установим курсор перед символом В и введем знак $ (в английской раскладке клавиатуры). Таким же образом введем знак $ в другие позиции.
· В результате формула содержит абсолютный адрес диапазона.
Рис.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. Для упрощения выбора функции сгруппированы по категориям.
Рис. 15. Диалог выбора функции.
При выборе конкретной функции "Мастер функций" помогает правильно построить формулу в интерактивном режиме.
Любая функция может быть аргументом другой функции. В "Мастере функций" при указании аргументов можно использовать кнопки fx слева от поля ввода для вставки функции в качестве аргумента (рис. 7). Количество вложенных функций ограничено емкостью ячейки ЭТ - вся конструкция не должна превышать длину в 256 символов.
Рис. 16. Определение аргументов функции.
Кнопки с зеленой стрелочкой справа от полей ввода (кнопки "Уменьшить") позволяют сворачивать диалог определения функции и указывать адреса ячеек, содержащих аргументы функции с помощью щелчка мышью (или выделения диапазона мышью, если аргументом может быть диапазон ячеек).
При определении аргументов функции можно сразу же увидеть результат ее работы. Если в результате получается ошибка, это значит, что аргументы не определены или определены неправильно.
ЗАКЛЮЧЕНИЕ
Невозможно описать все возможности электронных таблиц OpenOffice.org Calc и всю важность абсолютной и относительной адресации при работе с ними. Здесь приведены только некоторые базовые сведения и приемы работы. За рамками рассмотрения остались такие вопросы как операции со строками и столбцами, форматирование ячеек, операции с листами и многое другое.
Однако каждый пользователь может сам освоить нужные возможности
с помощью встроенной справочной системы, оригинальной документации, книг и информационных ресурсов Internet.
Один из самых известных проектов FOSS – интегрированный офисный пакет OpenOffice.org - также имеет в своем составе табличный процессор OpenOffice.org Calc. Этот табличный процессор с успехом может использоваться для решения экономических и инженерно-технических задач.
В данной работе были рассмотрены некоторые типовые задачи и даны методические указания, полезные для практической работы с электронными таблицами OOo Calc. Для успешного выполнения этих задач нужно обладать начальными знаниями о технологии работы с графическим пользовательским интерфейсом.
Литература
1. О.Ф.Литвин. Табличный Процессор Supercalc 4. Москва. "Финансы и статистика", 2008.
1. Майнхард Шмидт. QuattroPro for Windows ...для пользователя. Киев, BHV, Москва, "Бином". 2009.
2. А.Гарнаев. Internet в экономике и финансах. СПб, БХВ-Петербург, 2009
3. С.М.Лавренов. Сборник примеров и задач. Москва, "Финансы и статистика", 2007