Абсолютные и относительные ссылки

Лабораторная работа №2

ВВОД ФОРМУЛ

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

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

Для того чтобы Мicrosoft Ехсе1 распознал выражение как формулу, она должна начинаться со знака равенства.

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

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

Ссылку можно задать вручную или щелкнуть на нужной ячейке (выбрать нужный диапазон) указателем мыши.

Ввод формул

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

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

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

По мере ввода формул программа Excel выделяет каждую ссылку на ячейку другим цветом. Если ячейки, на которые установлены ссылки, отображаются на листе, они выделяются тем же цветом, что и ссылка на ячейку в формуле. Благодаря этому облегчается идентификация ссылок на ячейки, находящихся в формулах.

Закончив вводить формулу, нажмите Enterили наведите указатель мыши на значок с изображением «галочки» слева от строки состояния R и щелкните на нем.

ЗАДАНИЯ ДЛЯ ВЫПОЛНЕНИЯ

В качестве примера в ячейку В2 введем формулу =3+4.

Результат ввода до нажатия клавиши Enter показан на рис.22.

После нажатия клавиши Enter рабочее окно будет иметь вид, который показан на рис.23.

Рис.22. Процесс ввода формулы Рис.23. Результат ввода формулы и

ее выполнение

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

В строке формул все останется без изменений, а вот в ячейке В2 будет отображено число 7 результат сложения чисел 3 и 4. Приведенный пример является иллюстрацией еще одного правила: в строке формул отображается формула, а в соответствующей ячейке результат ее вычисления.

Попробуйте вычислить сумму тех же чисел без знака равенства (рис.24). Результата вы не получите.

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

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

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

Объясним на примере. Чтобы ввести формулу =А1+А2 в ячейке А3, необходимо выполнить следующее:

1. Введите числовые значения 3 и 4 в ячейку А1 и А2 соответственно.

2. Выделите ячейку А3.

3. Введите знак равенства (=).

4. Для ввода ссылки на ячейку А1 наведите указатель мыши на ячейку А1 и щелкните мышью, либо дважды нажмите на клавишу со стрелкой вверх. После выполненных действий выделится ячейка А1. Ссылка на ячейку А1 появится в ячейке А3 и в строке формул.

5. Введите знак плюс (+).

6. Повторите пункт 3 с ячейкой А2. В формуле добавится А2.

7. Закончив ввод формулы, нажмите Enter или значок с изображением «галочки». Точно так же, как и при ручном вводе формул, результат выполнения формулы отобразится в ячейке. Формула появится в строке формул, когда соответствующая ячейка будет активна.

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

Редактирование формул

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

Режим редактирования ячеек можно активизировать несколькими способами:

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

2. Нажмите клавишу F2. Это позволит вам редактировать содержимое непосредственно в ней. Если же опция Правка прямо в ячейке отключена, редактирование будет осуществляться в строке формул.

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

В качестве примера рассмотрим задачу о падении тела в поле тяжести Земли с некоторой высоты без начальной скорости. В этом случае за время t тело пролетает расстояние S, равное S =gt²/2. Величина g это ускорение свободного падения, равное примерно 9,8 м/с². Проанализируем зависимость пройденного расстояния от времени.

Для начала в ячейки таблицы внесем названия величин (для удобства дальнейшей работы). Так, в ячейку А1 введем символ g, в ячейку В1 введем символ t и в ячейку С1 символ S соответственно (рис.25).

После этого во второй строке вводим числа. В ячейку А2 введем значение ускорения свободного падения 9.8. В ячейку В2 введем 1, что подразумевает вычисление пройденного телом пути за одну секунду.

Замечание. Десятичная дробь от целой части числа отделяется запятой.

В ячейку С2 введем формулу =А2*В2^2/2. Необходимо заметить, что для ввода ссылки на ячейку следует выделить эту ячейку, щелкнув на ней левой клавишей мыши, что приведет к появлению адреса этой ячейки в строке формул. Следует избегать набора адреса ячейки на клавиатуре в строке формул, так адрес, набранный русскими буквами, не будет соответствовать адресу ячейки и формула не будет вычислена. По мере ввода этой формулы последовательно будем выделять ячейки, на которые в этой формуле есть ссылки (рис.26 и 27).

Рис.26. При наборе формулы автоматически выделяются ячейки, на которые в формуле есть ссылка. Выделена ячейка А2

Рис.27. Выделены ячейки (А2 и В2), на которые есть ссылки в формуле

После того как формула набрана, можно нажать клавишу Еnter (или щелкнуть на кнопке с зеленой галочкой слева от строки формул R) (рис.27). В ячейке С2 можно видеть правильное значение для пройденного телом за одну секунду расстояния (4.9 метра) (рис.28).

В тех случаях, когда требуется многократно проводить вычисления по одним и тем же формулам, но для разных входных данных, полезность использования ссылок очевидна. Например, если требуется вычислить расстояние, пройденное телом за две секунды, не потребуется вводить в се данные заново. Достаточно поменять значение в ячейке В2 на 2 и нажать Еnter. После этого в ячейке С2 автоматически будет отображен результат (рис.29).

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

Перейдем на Лист2. Таблица запланированных объемов продаж будет состоять из двух столбцов. В столбце А введем названия месяцев, в столбце В числа запланированных объемов продаж. Сначала нужно ввести заголовки столбцов в рабочий лист. Для этого выполните ряд действий.

1. Поместите табличный курсор в ячейку А1 с помощью мыши, щелкнув дважды на ней. В поле Имя появится адрес этой ячейки (рис.30).

2. В ячейку А1 введите слово Месяцы. Для этого наберите текст и нажмите клавишу Enter. В зависимости от установленных параметров Ехсеl переместит табличный курсор в друг ую, соседнюю к ячейке А1, ячейку, или же оставит его в ячейке А1.

3. Переместите табличный курсор в ячейку В1, введите План продаж и нажмите клавишу Enter.

Теперь введем названия месяцев в столбец А.

1. Поместите табличный курсор в ячейку А2 и введите Январь. Названия других месяцев можно ввести вручную, но давайте воспользуемся средством Ехсеl, которое называется Автозаполнение.

2. Убедитесь, что табличный курсор находится в ячейке А2. Обратите внимание на маленький квадратик в правом нижнем углу табличного курсора (маркер заполнения). Если поместить на него указатель мыши, то он примет вид черного крестика. Поместите указатель мыши на маркер заполнения так, чтобы указатель превратился в крестик. Затем нажмите левую кнопку мыши и перетаскивайте указатель вниз, пока не будут выделены все ячейки от А2 до А13.

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

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

После этого в столбце В нужно ввести планируемые объемы продаж за каждый месяц. Предположим, что в январе объемы должны составить 150 тыс. руб. и далее должны возрастать каждый месяц на 3,5%.

1. Для ввода данных поместите табличный курсор в ячейку В2, введите с клавиатуры число 150000 и нажмите клавишу Enter.

2. Чтобы ввести формулу, вычисляющую запланированные объемы продаж в феврале, перейдите в ячейку ВЗ и введите =В2*103,5%. Затем нажмите клавишу Enter, в ячейке должно появиться число 155250. Эта формула умножает содержимое ячейки В2 на 103,5%. Другими словами, объем продаж в феврале будет на 3,5% больше, чем в январе.

3. Подобная формула используется для расчета плановых объемов продаж во все остальные месяцы. Но вместо того, чтобы вводить одну и ту же формулу во все ячейки столбца В, воспользуемся средством автозаполнения. Убедитесь, что табличный курсор находится в ячейке ВЗ, то есть выделена формула, подлежащая копированию. Поместите указатель мыши на маркер заполнения так, чтобы он превратился в крестик. Затем нажмите кнопку мыши и перетаскивайте указатель вниз, пока не будут выделены все ячейки от ВЗ до В13.

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

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

 

Абсолютные и относительные ссылки

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

В формулах используется три типа ссылок на ячейки и диапазоны.

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

· Абсолютные ссылки. Эти ссылки не изменяются при копировании формул.

· Смешанные ссылки. В этих ссылках номер строки (или столбца) является абсолютным, а столбца (строки) относительным.

Отличительной особенностью абсолютных ссылок являются два знака доллара ($): один перед буквой столбца и второй перед номером строки (например, $А$5). Чтобы поставить два знака доллара ($) в адресе ячейки, следует поставить курсор в любом месте адреса ячейки в строке формул и нажать клавишу F4 на клавиатуре один раз.

В Ехсе1 также допускаются смешанные ссылки, в которых только одна часть адреса является абсолютной (например, $А4 или А$4). В этом случае клавишу F4необходимо нажать два или три раза (соответственно А$4 или $А4). Четвертое нажатие F4 возвращает к относительной ссылке.

Например, если в начале формулы вы вводите =А1, то первое нажатие клавиши F4 преобразует ссылку на ячейку в $А$1, второе в А$1, третье — в $А1, а четвертое вернет ей первоначальный вид А1. Нажимайте клавишу F4 до тех пор, пока не появится нужный тип ссылки.

ЗАДАНИЯ ДЛЯ ВЫПОЛНЕНИЯ

Различие между разными типами ссылок проявляется при копировании формул. На рис.33 показана таблица, в ячейке D2 которой находится формула умножения количества наименований товара на его цену. Формула выглядит следующим образом: =В2*С2. Если ее скопировать маркером заполнения на ячейки D3 и D4, то получим таблицу на рис.33. Поскольку в этой формуле используются относительные ссылки, при копировании формулы в ячейки D3 и D4 они соответствующим образом изменятся. В результате в ячейке D3 получим формулу: =B3*C3, а в ячейке D4 соответственно =B4*C4.

Если в ячейке D2 заменим относительные ссылки абсолютными, то получим =$В$2*$С$2.

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

Теперь изменим этот пример и подсчитаем комиссионные. Значение процентной ставки комиссионных хранится в ячейке В7 (рис.34). Перенесем заголовок Всего на одну ячейку вправо, а в D1 впишем =A7. В результате в ячейке D1 получим Комиссионные. В ячейку D2 введем формулу =В2*С2*$В$7.

Количество умножается на цену, а затем результат умножается на процентную ставку комиссионных, значение которой хранится в ячейке В7. Обратите внимание на то, что ссылка на ячейку В7 является абсолютной. Скопировав ячейку D2 в D3, получим =В3*С3*$В$7.

Ссылки на ячейки В2 и С2 изменились, а ссылка на ячейку В7 нет, т.е. мы получили правильный результат.

На рис.35 показана таблица, в которой используются смешанные ссылки. В левом столбце хранит ся значение длины прямоугольника, а в верхней строке находится ширина. Далее вычисляется площадь прямоугольника. Например, в ячейке D5 вычисляется площадь прямоугольника, длина которого 2, а ширина 1,5. В ячейку С3 была введена такая формула: =$В3*С$2

Обратите внимание на то, что в формуле используются смешанные ссылки. В ссылке на ячейку В3 абсолютной является ссылка на столбец ($В), а в ссылке на ячейку С2 используется абсолютная ссылка на строку ($2). Скопировав эту формулу во все ячейки диапазона, мы получим правильный результат вычислений. Например, в ячейке F7 будет содержаться такая формула: $В7*F$2.

Если же в ячейке С3 использовать абсолютные или относительные ссылки, то результат окажется неверным.