Пример 7.2. Цель: Осознать, как связаны между собой влияющие и зависимые ячейки при их перемещении по листу Ехсеl.

Постановка задачи: Пусть, например, цена некоторого товара к концу января была 100р. Определить цену этого товара к концу февраля, если она увеличилась на 5%.

Предварительные действия:

1. В ячейку А1 ввести 100р. (Тип: Число, формат: Денежный).

2. В ячейку А2 ввести 5% (Тип: Число, формат: Процентный).

3. В ячейку С1 ввести формулу =A1*(1+A2).

Результат (до фиксации ввода формулы):

.

Результат (после фиксации ввода формулы):

.

Алгоритм (ввода формулы в ячейку С1):

!ЛКМ С1 / !ЛКМ по кнопке <Изменить формулу> / !ЛКМ А1 / Ввод с клавиатуры знака */ Ввод с клавиатуры круглых скобок () / Перемещение курсора ввода внутрь круглых скобок с помощью клавиатурных стрелок / Ввод с клавиатуры 1+ / !ЛКМ А2 / <Enter>.

Комментарии и советы:

1. Команды !ЛКМ С1 / !ЛКМ по кнопке <Изменить формулу> осуществляют выделение ячейки С1, неклавиатурный ввод знака = в строку формул и активизируют режим правки. Напомним, что только в режиме редактирования (правки) можно производить перемещение курсора ввода в строке формул или непосредственно в ячейке с помощью клавиатурных стрелок. Если пользоваться клавиатурными стрелками, находясь в режиме ввода, то вместо перемещения курсора ввода в формуле будут возникать «неправильные» ссылки на ячейки, на которые «указывают» клавиатурные стрелки из позиции последней отсылочной ячейки (на экране эта ячейка обводится бегущей штриховой рамкой).

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

3. Обратим внимание на то, что в результирующей (зависимой) ячейке С1 находится число 105, а по логике организации входных данных было бы желательно, чтобы в этой ячейке отображалось число 105р., однако единица измерения «р» не добавляется к числу автоматически. Это связано с тем, что по умолчанию ячейке С1 назначен формат Общий. Чтобы получить требуемое отображение в виде 105р., нужно ячейке С1 назначить формат Денежный. Для этого после выделения ячейки С1 нужно выполнить команды Формат / Ячейки / Число / Выбор числа десятичных знаков и обозначения (т.е. единицы измерения) / <ОК>. При этом в секции Образец можно предварительно просмотреть результат форматирования, что позволяет при необходимости его исправить. Заметим также, что в рассматриваемом случае букву «р.» к числу 105 в ячейке С1 можно было бы «дописать» с клавиатуры и Ехсеl все равно бы распознал значение 105р. как число в формате Денежный. И это связано все с тем же обстоятельством: единица измерения «р.» зарезервирована за форматом Денежный. В других случаях подобное «дописывание» букв к числам, превратит последние в Текст, и с таким числовым текстом нельзя будет проводить дальнейших вычислений. Окончательный результат:

4. Обратим внимание на ячейку А1. В нее с клавиатуры введено число 100р. Единица измерения р. зарезервирована за денежным форматом. Поэтому после фиксации ввода этой ячейке автоматически присваивается формат Денежный. Отметим, что число 100р. это отображаемое значение ячейки А1; ее хранимое значение – число 100. Именно поэтому ячейке С1 по умолчанию был назначен формат Общий, а не Денежный, поскольку вычисления в ней проводились именно с хранимым значением влияющей ячейки А1.

5. Обратим внимание на ячейку А2. В нее с клавиатуры введено число 5% (символ % набран также с клавиатуры!). Единица измерения % зарезервирована за процентным форматом. Поэтому после фиксации ввода этой ячейке автоматически присваивается формат Процентный. Отметим, что число 5% это и отображаемое, и хранимое значение ячейки А2. Обсудим часто возникающую ошибку при работе с данными в процентном формате. Казалось бы 5% можно ввести в ячейку А2 и следующим образом: сначала ввести число 5, зафиксировать ввод нажатием клавиши <Enter>, снова выделить эту ячейку и назначить ей формат Процентный. Но результат окажется не 5%, а 500%, т.е. неправильным (что, конечно, приведет и к неправильному результату вычислений в ячейке С1). В этом случае наложение на ячейку А2 процентного формата следует воспринимать как действие своеобразного оператора, который число в формате Числовой преобразует в число в формате Процентный по правилу: а  а00% (в обсуждаемом варианте число 5 преобразуется в число 500%). Чтобы в рассматриваемом примере в ячейке А2 получить правильный результат, действуя таким способом, нужно набрать в ней число 0,05, зафиксировать ввод, выделить эту ячейку и назначить ей формат Процентный. Тогда результат будет 5%.

6. Совсем иначе надо понимать действие оператора % в формулах. Пусть, например, в ячейках А7 и А8 находятся, соответственно, числа 5 и 5%, а в ячейки В7 и В8 введены формулы, показанные на следующем рисунке:

.

Каков будет результат? Он окажется следующим:

,

т.е. символ % в зависимой ячейке действует как оператор, уменьшающий число в числовом формате в 100 раз, сохраняя прежний формат. (Сами сформулируйте правило, по которому оператор % действует на числа в процентном формате). Именно такой вариант действия оператора % реализован в ячейке С1 рассматриваемого примера: находящаяся там формула содержит ссылку на ячейку А2; при этом символ % в ней воспринимается как оператор, переводящий число 5% в число 0,05 (и потому, в частности, формат результата вычислений – Общий).

7. Терминологические напоминания:

,

.

8. Посмотрим, к каким изменениям в формулах могут привести перемещения влияющих и зависимых ячеек. Следствие перемещения влияющей ячейки А2 в позицию В3 показано на рисунке:

Как видно, в ячейке С1 произошло изменение формулы: теперь зависимая ячейка С1 ссылается уже не на ячейку А2, а на ячейку В3 (в этом случае говорят, что формула перенастраивается (или настраивается) верно). Результат вычисления по перенастроенной формуле оказывается правильным: 105р.

Следствие перемещения зависимой ячейки С1 в позицию D1 показано на рисунке:

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

Вывод: Перемещение влияющих и зависимых ячеек не оказывает влияния на правильность вычислений, а лишь приводит к изменению их расположения друг относительно друга на листе Ехсеl (конечно, при этом предполагается, что перемещаемые ячейки занимают после перемещения «пустые» позиции, а не позиции, заполненные данными).

Замечание: Аналогичный вывод справедлив и при вставках, удалении и очистке строк, столбцов, диапазонов (при условии, что эти манипуляции не затрагивают влияющие и зависимые ячейки, а лишь приводят к изменению их относительного расположения на листе Ехсеl).

9. Заметим, что, если произвести изменения данных во влияющих ячейках, то это мгновенно сказывается на результатах вычислений в зависимых ячейках (сами формулы при этом не меняются!). Так, если в ячейку А2 исходного примера вместо 5% ввести, например, 7%, то отображаемое значение ячейки С1 станет 107р.

10. Итак, вычисления проведены. Осталось произвести необходимые изменения в структуре данных, чтобы они выглядели привлекательно при печати документа: должным образом их перегруппировать, сформировать таблицы, ввести заголовки строк и столбцов, отформатировать данные. Результат, например, может выглядеть следующим образом:

В предыдущем примере мы рассмотрели, какие изменения могут возникнуть в ссылках при перемещении ячейки, содержащей формулу, и отметили, что при переносе формулы ее синтаксис остается неизменным, но операнды могут изменяться; при этом формула всегда «выдает» правильный результат. При копировании формул^ имеет место аналогичная ситуация, но только относительно синтаксиса: он сохраняется, - а изменение операндов подчинено определенным правилам, в зависимости от которых ссылки подразделяются на абсолютные, относительные и смешанные. Прежде, чем дать точные определения этим понятиям, условимся о следующей терминологии.

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

Если ячейка-образ при произвольной позиции вставки ссылается на ту же ячейку, что и ячейка-оригинал (т.е. новая и исходная влияющие ячейки имеют одинаковые «номера»^ строк и столбцов), то соответствующая ссылка в ячейке-оригинале называется абсолютной ссылкой, имеющей формат типа $А$1 (знак $ стоит как перед “номером” столбца, так и перед “номером” строки и потому часто используется следующее высказывание: “Доллар зацепляет ссылку за столбец и за строку”, - или по-другому: “Формула зацепляется ссылкой за столбец и за строку”). Таким образом, в скопированной формуле не происходит изменения операнда, отвечающего абсолютной ссылке. На Рис.7.1 показана схема копирования формулы с абсолютной ссылкой на влияющую ячейку.

Рис. 7.1. Копирование зависимой ячейки Е19, имеющей абсолютную ссылку на ячейку С20

Если ячейка-образ при произвольной позиции вставки ссылается на новую влияющую ячейку, местоположение которой на листе относительно ячейки-образа точно такое же, как и местоположение исходной влияющей ячейки относительно ячейки-оригинала (как по вертикали, так и по горизонтали), то соответствующая ссылка в ячейке-оригинале называется относительной ссылкой, имеющей формат типа А1. Таким образом, в скопированной формуле происходит изменение операнда, отвечающего относительной ссылке, которое затрагивает как «номер» столбца, так и номер строки. На Рис.7.2 показана схема копирования формулы с относительной ссылкой на влияющую ячейку.

Рис. 7.2 Копирование зависимой ячейки Е4, имеющей относительную ссылку на ячейку С5

Если ячейка-образ при произвольной позиции вставки ссылается на новую влияющую ячейку, местоположение которой на листе относительно ячейки-образа точно такое же, как и местоположение исходной влияющей ячейки относительно ячейки-оригинала только по вертикали, и, кроме того, «номер» столбца новой влияющей ячейки совпадает с «номером» столбца исходной влияющей ячейки, то соответствующая ссылка в ячейке-оригинале называется смешанной ссылкой, имеющей формат типа $А1 (знак $ стоит перед “номером” столбца, и потому часто используется следующее высказывание: “Формула зацепляется ссылкой за столбец”). Таким образом, в скопированной формуле происходит изменение операнда, отвечающего смешанной ссылке, которое затрагивает только номер строки. Отметим также, что в рассматриваемом случае, если ячейка-образ и ячейка-оригинал находятся в одной и той же строке, то они ссылаются на одну и ту же исходную влияющую ячейку. При этом исходная и новая влияющие ячейки всегда находятся в одном и том же столбце. На Рис.7.3 показана схема копирования формулы со смешанной ссылкой формата $A1 на влияющую ячейку.

Рис. 7.3. Копирование зависимой ячейки Е19, имеющей смешанную ссылку формата $А1 на ячейку С20

Если ячейка-образ при произвольной позиции вставки ссылается на новую влияющую ячейку, местоположение которой на листе относительно ячейки-образа точно такое же, как и местоположение исходной влияющей ячейки относительно ячейки-оригинала только по горизонтали и, кроме того, номер строки новой влияющей ячейки совпадает с номером строки исходной влияющей ячейки, то соответствующая ссылка в ячейке-оригинале называется смешанной ссылкой, имеющей формат типа А$1 (знак $ стоит перед номером строки, и потому часто используется следующее высказывание: “Формула зацепляется ссылкой за строку”). Таким образом, в скопированной формуле происходит изменение операнда, отвечающего смешанной ссылке, которое затрагивает только «номер» столбца. Отметим также, что в рассматриваемом случае, если ячейка-образ и ячейка-оригинал находятся в одном и том же столбце, то они ссылаются на одну и ту же исходную влияющую ячейку. При этом исходная и новая влияющие ячейки всегда находятся в одной и той же строке. На Рис.7.4 показана схема копирования формулы со смешанной ссылкой формата A$1 на влияющую ячейку.

Рис. 7.4. Копирование зависимой ячейки Е37, имеющей смешанную

ссылку формата А$1 на ячейку А38