Элементарные приемы работы с формулами и функциями

Основные определения

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

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

Функция –это стандартная встроенная в Ехсеl формула, которая возвращает результат выполнения определенных действий над своими элементами, выступающими в качестве ее аргументов. Функции используются для упрощения сложных вычислений. Аргументами функции могут являться константы, ссылки на ячейки или диапазоны, имена, текстовые строки и другие (вложенные) функции. Просмотреть список встроенных функций (весьма значительный!) можно, выполнив! ЛКМ по кнопке < Вставка функции>.

Пример 7.1. Цель: Ввести в ячейку А1 формулу = 5+2*3 (звездочка (*) – оператор умножения чисел в Ехсеl) и напомнить понятие приоритета арифметических операций.

Результат:

Действия:

1. Выделение ячейки А1.

2. Ввод с клавиатуры знака = *.

3. Ввод с клавиатуры остальной части формулы.

4. Фиксация ввода клавишей < Enter>.

Обсуждение: По умолчанию Excel вычисляет формулу слева направо, начиная со знака равенства (=). Синтаксис написания формулы управляет процессом ее вычисления. Введенная в ячейку А1 формула

при выделении ячейки отображается в строке формул, а результат вычисления, т.е. число 11 отображается в самой ячейке. Эта формула возвращает число 11, так как умножение имеет (как это принято в арифметике) более высокий приоритет над сложением и поэтому выполняется в первую очередь: сначала происходит умножение 2 на 3 (результат умножения равен 6), а затем полученное значение складывается с 5. Если для изменения синтаксиса воспользоваться скобками, например, так =(5+2)*3, то сначала произойдет сложение 5 и 2, а затем умножение полученного результата на 3. Последняя формула возвращает число 21. Ниже представлено отображение ячейки А1 в этом случае, соответственно, до и после фиксации ввода:

, .

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

Совет: Одну и ту же формулу можно ввести сразу в несколько ячеек. Для этого необходимо выделить ячейки, ввести формулу, а затем нажать клавиши < Ctrl> + < Enter>.

Применение операторов и порядок выполнения действий
в формулах

Если формула включает несколько операторов, то действия выполняются в порядке, представленном в следующей таблице.

Таблица 7.1

Операторы Ехсеl и их приоритет

Верхним строкам Табл.7.1 отвечает более высокий приоритет операторов, а расположенным ниже – соответственно более низкий. Операторы, расположенные в одной строке таблицы, имеют одинаковый приоритет. Если формула содержит операторы с одинаковым приоритетом, например, формула содержит операторы умножения и деления, то Excel обрабатывает операторы слева направо. Как уже говорилось выше, чтобы изменить порядок обработки операторов, нужно расставить круглые скобки в соответствующих позициях формулы. В ряде случаев расстановка круглых скобок непринципиальна, например, две формулы =P1: Y1^(1/2) и =(P1: Y1)^(1/2) (с которыми мы встретимся ниже) приведут к одному и тому же результату, поскольку оператор диапазона (двоеточие) имеет более высокий приоритет, чем оператор возведения в степень. Однако формула = P1: Y1^1/2 возвратит иной результат в сравнении с написанными выше: в формулах = P1: Y1^(1/2) и =(P1: Y1)^(1/2) из каждого элемента диапазона P1: Y1 извлекается квадратный корень, а в формуле = P1: Y1^1/2 каждый элемент этого диапазона возводится в степень 1 (т.е. не изменяется), а затем делится пополам. Указанное различие в возвращаемых значениях сравниваемых формул существенно связано со свойством приоритетности операторов::, ^, /.

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

Обратим внимание на различие операторов Унарный_минус и Вычитание. Например, в формулах = -А1, = -А1-В1 первый минус это Унарный_минус, а в формулах = -А1-В1, = А1-В1 второй минус обозначает Вычитание. С использованием унарного минуса в формулах Ехсеl могут быть связаны неожидаемые результаты (и даже неожиданные ошибки). Так, если пользоваться правилами арифметики, то мы ожидаем, что формула = -(-1)^2 возвратит значение, равное -1, а на самом деле в Ехсеl это не так: результат будет равен 1 (еще раз внимательно посмотрите на Таблицу 7.1 приоритетов операторов!). Поэтому рекомендация: либо вообще не пользоваться оператором Унарный_минус, либо применять его с осторожностью.

В Excel включено (в порядке уменьшения их приоритета) четыре вида операторов: адресные, арифметические, текстовый и операторы сравнения.

Адресные операторы действуют на диапазоны ячеек, которые в дальнейшем обрабатываются иными операторами Ехсеl ( см. Табл.7.2 ).

Арифметические операторы (операции) используются для выполнения вычислений над числами (см. Табл.7.3). Результатом выполнения арифметических операций всегда являются числа.

Операторы сравнения (см. Табл. 7.4) используются для обозначения операций сравнения двух операндов (обычно чисел, но это необязательно). Результатом выполнения операции сравнения является логическое значение ИСТИНА или ЛОЖЬ.

Таблица 7.2

Адресные операторы Ехсеl

Таблица 7.3

Арифметические и текстовые операторы Ехсеl

Таблица 7.4

Операторы сравнения Ехсеl

Вычисление значений в формулах, ссылающихся на ячейки

Формула является основным средством анализа данных. С помощью формул можно складывать, умножать, сравнивать, объединять данные и выполнять прочие операции над данными (при условии, что смысл операции соответствует типу данных, например, складывать можно, как правило, данные типа Число, но не типа Текст). Формулы могут ссылаться на ячейки текущего листа (используя тип ссылки =А1, что означает: в данную ячейку введена формула, которая ссылается на ячейку того же листа, где расположена данная ячейка), других листов той же книги (используя тип ссылки =Лист1! A1, что означает: данная ячейка содержит формулу, ссылающуюся на ячейку с именем А1, которая располагается на листе с именем Лист1; при этом имена отсылочных листа и ячейки разделены знаком!) или других книг (в этом случае используется тип ссылки =[ Книга1]Лист1! $A$1, что означает: данная ячейка содержит формулу, ссылающуюся на ячейку с именем А1, которая располагается на листе с именем Лист1 книги с именем Книга1; при этом имена отсылочных книги, листа и ячейки разделены соответствующими знаками (см. синтаксис формулы)*).

Если необходимо, чтобы ячейка, в которую вводятся данные, ссылалась на значение некоторой другой ячейки, нужно в процессе ввода формулы в данную ячейку выполнить! ЛКМ по этой другой ячейке или набрать ее имя (в «латинице»!, если это имя типа имени по умолчанию, т.е. типа А1, $A1, Лист1! A1 и т.п.) на клавиатуре (говорят, что данная ячейка ссылается на другую ячейку). В этом случае ячейка, содержащая формулу, называется зависимой ячейкой, т.к. ее значение зависит от значения другой ячейки, а последняя называется влияющей, т.к. ее значение влияет на значение зависимой ячейки. Формула, находящаяся в зависимой ячейке, как правило, возвращает иное значение по сравнению с исходным, если изменяется значение влияющей ячейки.

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

Для ввода в формулу встроенной в Ехсеl функции или для ее изменения удобно пользоваться Мастером функций или, по-другому, панелью формул/функций. После вставки функции в строку формул там отображается ее имя, аргументы, синтаксис, а также возвращаемое функцией значение. При работе с Мастером функций следует руководствоваться следующими общими правилами:

1. Для ввода формулы, содержащей функцию, нужно выделить ячейку, в которую будет вводиться формула.

2. Чтобы начать ввод формулы с функции, необходимо нажать кнопку < Изменить формулу> в строке формул.

3. Далее, либо нажать кнопку < Вставка функции> на панели инструментов Стандартная, либо нажать кнопку < Стрелка вниз>, расположенную справа от поля имен (которое при вводе формулы заменяется на поле Функции) в левой части строки формул.

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

5. После выбора функции вызывается ее диалоговое окно, в поля которого нужно ввести ее аргументы.

6. По завершении ввода функции нажать кнопку < ОК> в диалоговом окне для дальнейшего набора формулы или сразу нажать клавишу < Enter> для фиксации ввода формулы.

7. Панель формул можно использовать для изменения функции в формуле. Чтобы отобразить панель формул, нужно выделить ячейку, содержащую формулу с функцией, и нажать кнопку < Изменить формулу>. Сразу отобразится диалоговое окно с первой функцией формулы, содержащее ее поля со всеми аргументами. Изменение ЛЮБОЙ ДРУГОЙ функции, входящей в формулу, производится путем ПОЗИЦИОНИРОВАНИЯ КУРСОРА ВВОДА в строке формул НА ЛЮБОЙ ЧАСТИ ЕЕ ИМЕНИ с последующим исполнением! ЛКМ по кнопке < Изменить формулу>.

8. Для очистки ячейки от формулы нужно выделить ячейку, ее содержащую, и нажать клавишу < Delete>.

Пример 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

Пример 7.3. Цель: Научиться применять в формулах относительные ссылки.

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

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

Решение:

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

В ячейке D1 содержится уже знакомая нам формула (записанная в относительных ссылках!), а в ячейки В2, D2, F2, H2 введен знак %. Причем последнее действие выполнено следующим образом: знак % введен с клавиатуры в ячейку В2, скопирован, затем ячейки D2, F2, H2 выделены (диапазон несвязный – удерживаем клавишу < Ctrl>!) и после этого знак % вставлен во все эти ячейки в одно действие. Затем во все эти ячейки введены числовые подправки. В результате таблица принимает вид:

Формула в ячейке D1 ссылается на ячейки В1 и В2. Это, соответственно, ячейка-оригинал и исходные зависимые ячейки. В ячейке F1 должна находиться формула, по структуре аналогичная той, которая находится в ячейке D1, но ссылаться она должна на ячейки D1 и D2. Расположение последних относительно ячейки F1 такое же, как и у ячеек В1 и В2 относительно ячейки D1. Поэтому ячейка F1 может рассматриваться в качестве ячейки-образа, а ячейки D1 и D2 - в качестве новых влияющих ячеек в смысле определения относительных ссылок. Поэтому формулу в ячейке F1 можно получить копированием формулы из ячейки D1. Но все то же самое справедливо и по отношению к ячейкам Н1 и F1, F2, J1 и Н1, Н2. Поэтому формулу из ячейки D1 можно сразу скопировать в ячейки F1, Н1 и J1 за один шаг. Для этого нужно выполнить следующие действия:

1. Выделить ячейку D1 (с формулой).

2. Произвести ее копирование (< Ctrl> + < Insert>).

3. Выделить ячейки F1, Н1 и J1 (удерживая клавишу < Ctrl>).

4. Вставить за один шаг во все эти ячейки скопированную формулу (< Shift> + < Insert>).

Замечание: Еще проще это можно сделать так: сразу выделить все ячейки D1, F1, Н1 и J1, активизировать ячейку D1 (c помощью клавиши < Tab> или < Shift> + < Tab>), ввести формулу в строке формул и нажать клавиши < Ctrl > + < Enter>.

В результате в нашей таблице возникнут следующие формулы:

Эти формулы автоматически просчитаются и окончательный результат принимает следующую числовую форму:

Совет: Очень часто копированные формулы в связном диапазоне удобнее получать, используя не буфер обмена, а маркер заполнения, протаскивая его в нужном направлении при помощи ЛКМ или ПКМ. Для этого нужно выделить ячейку, содержащую формулу, и перетащить маркер заполнения по горизонтали или по вертикали в соседний (примыкающий к ячейке с формулой) диапазон.

Попробуем реализовать данный совет в нашем случае. Для этого вернемся назад к таблице, которая содержит все заголовки, процентные приросты цены товара по месяцам, цену товара к концу января и формулу в ячейке D1. Именно ячейку D1 (предварительно выделенную!) и надо протащить вправо вдоль первой строки за маркер заполнения, удерживая, например, ЛКМ. Учтем, что при этом все заголовки, находящиеся в ячейках E1, G1, I1 будут уничтожены. Поэтому (чтобы не вводить эти заголовки заново) предварительно вырежем (или скопируем) эти ячейки и вставим их “на время” куда-нибудь в свободное место листа и только после этого выполним процедуру копирования с помощью маркера заполнения. Схематично описанные действия представлены на рисунке:

Промежуточный результат после протаскивания маркера заполнения по «горизонтальному» диапазону Е1: J1 следующий:

Как видим, в ячейках F1, Н1 и J1 появились правильные формулы, а в ячейках E1, G1, I1 возникли «решетки» - ошибки отображения информации (значение такой ошибки имеет вид ), указывающие на то, что ширина этих ячеек недостаточна для адекватного отображения содержащихся в них данных. Неотображенные данные можно увидеть в строке формул при выделении ячеек с ошибкой отображения . Неотображенные данные также можно увидеть и в самих ячейках, если увеличить их ширину (напомним, что наиболее просто это делается исполнением !! ЛКМ по разделителю имен столбцов: данного и смежного с ним справа, - или перетаскиванием вправо этого разделителя; см. рисунки, приведенные ниже, на которых схематически показано, какие действия следует предпринять, чтобы увеличить ширину ячейки).

После «раздвижки» столбцов E, G, I мы увидим, что в ячейках E1, G1, I1 также находятся формулы, полученные “протяжкой” маркера заполнения (правда увидеть их можно только в строке формул, а не в том виде, в котором они показаны ниже на рисунке: причина этого обсуждается далее по тексту):

Интересно отметить, что формула в ячейке Е1 ссылается на ячейки С1 и С2, в которых «лежат» текстовые значения, и понятно, что при возвращении результата по этой формуле должна возникнуть уже вычислительная ошибка (ведь текстовые строки, как правило, нельзя складывать с числами и перемножать между собой!), проявлением чего в Ехсеl служит отображение значения этой ошибки #ЗНАЧ!. Оноуказывает на то, что хотя бы в одной из влияющих ячеек находится недопустимый тип аргумента (для функции) или операнда (для формулы). В свою очередь формулы в ячейках G1 и I1 сами ссылаются на ошибочные значения и потому также возвращают значение ошибки #ЗНАЧ!. Таблица на этом шаге будет иметь следующий вид:

Последний шаг, который нужно сделать: вернуть в ячейки E1, G1, I1 недостающие заголовки с помощью процедуры D& D_ЛКМ, что схематично показано ниже:

Таблица примет свой окончательный вид: такой же, как и полученный ранее. При этом значения ошибок #ЗНАЧ! будут замещены названиями столбцов таблицы из ячеек E4, G4, I4.

Пример 7.4. Цели: 1) Научиться рационально организовывать на рабочем листе данные, включающие формулы. 2) Выяснить, какие следствия возникают при копировании и перемещении блоков с формулами, содержащими только относительные и смешанные ссылки.

Постановка задачи: Та же, что и в предыдущем примере, только речь идет не о пяти, а о двенадцати месяцах, к примеру 1996 г., т.е. считаются известными входная цена (цена товара к концу января 100р.) и процентные приросты в каждый из двенадцати месяцев, причем за первые пять месяцев они остаются прежними; в июне процентный прирост составляет 13% с увеличением на 2% в каждый последующий месяц (пример носит учебный характер: числовые значения не соответствуют реальным данным).

Обсуждение: Ясно, что никакого труда не составляет решить эту задачу по изложенной выше схеме. Проблема в другом: как рационально сформировать необходимую таблицу и как рационально ее разместить? Что касается второй части этой проблемы, то здесь существует два аспекта. Во-первых, если построить “строчную” таблицу, аналогичную построенной в предыдущем примере, но не для пяти, а для двенадцати месяцев, то вся эта таблица в “нормальном” режиме просмотра данных не уместится на экран компьютера (кстати, чтобы изменить масштаб просмотра имеет смысл пользоваться командами Вид / Масштаб). Во-вторых, если таблица сильно «вытянута» по горизонтали, то могут возникнуть проблемы с печатью: в режиме общепринятой вертикальной ориентации страницы (кстати, ориентация страницы при печати настраивается командами Файл / Параметры страницы / Страница / Ориентация и, кроме того, на вкладке Страница имеются другие настройки, необходимые для печати документа) и при “нормальных атрибутах” форматирования часть таблицы распечатается на одной странице, а другая часть – на другой. Одним из вариантов решения обсуждаемой проблемы является “вертикальное” расположение таблицы на листе Ехсеl, например, такое, которое показано на последнем рисунке (правда, не для двенадцати месяцев, а только для четырех, но это в рассматриваемом контексте не принципиально).

Для того, чтобы “получить” такую “вертикальную” таблицу нужно выполнить следующие действия:

1. Выделить исходную “горизонтальную ” таблицу.

2. Скопировать ее (< Ctrl> + < Insert>).

3. Выделить одну свободную ячейку как метку вставки левого верхнего угла “вертикальной” таблицы, которая будет вставлена на следующем шаге.

4. Активизировать команды Правка / Специальная вставка / Вставить Все ( или при необходимости: Формулы, или Значения, или Форматы, т.п. ) / “Повесить” флажок Транспонировать / < ОК> (отметим, что нужно следить за тем, чтобы вставляемая таблица не “накрыла” непустые ячейки, т.к. их содержимое может быть уничтожено; впрочем это не критично, поскольку перед “уничтожением” Ехсеl задаст вопрос, сделано ли это намеренно или случайно).

5. При необходимости – выделить и удалить старую “горизонтальную” таблицу.

Отметим, что в новой «вертикальной» таблице происходит перенастройка формул, т.к. ссылки в них относительные. К примеру, в ячейке А6 «вертикальной» таблицы (см. последний рисунок) «лежит» формула =A4*(1+B4). При этом перенастроенные формулы «работают» правильно, поскольку описанные действия сохраняют относительное расположение всех влияющих и зависимых ячеек (что как раз и позволяет безболезненно выполнить действие 5 из последнего списка действий). Отметим, что перенастроенные формулы настроились бы неверно, если бы была скопирована лишь часть исходной таблицы, в которой имелись бы ссылки на ячейки, содержащиеся в ее нескопированной части. Например, в рассматриваемом случае это произошло бы, если бы «горизонтальная» таблица была скопирована без своего первого столбца (более общо: без части входных данных).

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

q содержат формулы только с относительными ссылками,

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

Подчеркнем, что перемещать блоки, в которых содержатся только относительные ссылки, можно совершенно произвольно – как целиком, так и частями; при этом ссылки всегда настраиваются верно.

Итак, таблица расположена «вертикально», но все же ее структурированность оставляет желать лучшего: текстовые и числовые строки «перемежают» друг друга, а это часто мешает дальнейшей их обработке.

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

.

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

Наши дальнейшие действия состоят из трех шагов:

1. Формирование заголовков строк таблицы в столбцах А и С.

2. Ввод исходных данных в ячейку В1 и во все ячейки столбца D3: D13.

3. Ввод в ячейку В3 главной формулы и воспроизведение всех остальных формул столбца В4: В13 еекопированием ( в этом и состоит смысл термина главная формула ).

Шаг 1.

Заголовки строк столбцов А и С состоят из названий месяцев, дополненных словом Цена в столбце А и символом % в столбце С. Названия месяцев содержатся во встроенном списке процедуры автозаполнения, и поэтому заманчиво было бы им воспользоваться. Но как «сцепить» отдельное слово с этим списком? Для этого можно использовать встроенную функцию СЦЕПИТЬ (текст1; текст2;...).

Предварительные настройки интерфейса при работе с функциями: < Сервис> / < Параметры> / < Надстройки> / Активизировать флажок Пакет анализа.

Справка

Функция СЦЕПИТЬ объединяет несколько текстовых строк в одну.

Синтаксис: =СЦЕПИТЬ (Текст1; Текст2;...), где Текст1, Текст2,... - аргументы функции. Их может быть от 1 до 30, и они являются элементами текста, объединяемыми функцией СЦЕПИТЬ в один элемент текста. Элементами текста могут быть текстовые строки, числа или ссылки, каждая из которых может ссылаться на свою отдельную ячейку.

Замечание: Вместо функции СЦЕПИТЬ для объединения текстовых данных можно использовать оператор &. Последний называется амперсантом, а сама процедура сцепления – конкатенацией.

Посмотрим, как можно применить функцию СЦЕПИТЬ для конкатенации слова Цена и символа % со списком автозаполнения. Для этого в свободной части листа Ехсеl сделаем следующие предварительные «заготовки» и выполним действия, понятные из следующих рисунков:

, .

В ячейку G4 введем функцию СЦЕПИТЬ, аргументами которой являются:

¨ Значение ячейки F2.

¨ Пустой текст: два раза набранные двойные кавычки, с пробелом между ними (Пояснение: Нам нужно, чтобы в ячейке G4 возникло текстовое значение Цена янв; пустой текст с пробелом требуется для отделения текста Цена от текста янв пробелом).

¨ Значение ячейки F4.

Дальнейшие действия соответствуют следующему алгоритму:! ЛКМ по кнопке < Изменить функцию> (Эту кнопку называют также Мастером функций) / Ехсеl предъявляет диалоговое окно: Мастер функций – шаг 1 из 2 /

Рис.7.5. Диалоговое окно Мастер функций: шаг 1 из 2

Рис. 7.6. Диалоговое окно функции Сцепить

В списке Категория выбираем Текстовые / В списке Функция выбираем Сцепить / < ОК> / Ехсеl предъявляет диалоговое окно: Сцепить/! ЛКМ по полю Текст1 /! ЛКМ F2 (по ячейке на рабочем листе) /! ЛКМ < F4> (по клавише на клавиатуре; повторяем удары по клавише < F4> до тех пор, пока в поле Текст1 не возникнет отображение в виде F$2) /! ЛКМ по полю Текст2 / С клавиатуры набираем два раза двойные кавычки и вставляем пробел между ними /! ЛКМ по полю Текст3 /! ЛКМ F4 (по ячейке на рабочем листе) /! ЛКМ < F4> (по клавише на клавиатуре; повторяем удары по клавише < F4> до тех пор, пока в поле Текст3 не возникнет отображение в виде $F4) / < ОК>. Промежуточный результат показан ниже:

После этого ячейку G4 перетаскиваем маркером заполнения на одну ячейку вправо. Имеем следующий промежуточный результат:

Далее, выделяем блок G4: Н4 и копируем его с помощью маркера заполнения в вертикальном направлении вниз вплоть до ячеек G15: Н15 (в этом случае копирование можно выполнить в стандартном варианте: «протяжкой» маркера, - либо нестандартно: !! ЛКМ по маркеру). Получаем окончательный результат этапа работы с функцией СЦЕПИТЬ:

.

В последней таблице в столбцах G и H мы видим отображаемые значения соответствующих ячеек. Их «формульное наполнение» представлено ниже:

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

¨ Относительно клавиши < F4>

В процессе ввода абсолютных или смешанных ссылок (как в нашем примере) приходится вставлять в соответствующие позиции (перед именем строки, перед именем столбца или перед тем и другим) символ $. Это проще всего делать нажатием клавиши < F4>. Последовательные нажатия приводят к изменению позиции вставки этого символа так, что он перемещается циклически, например,

Подчеркнем, что символ $ можно вводить и с клавиатуры (но это значительно менее удобно по сравнению с применением клавиши < F4>).

¨ Относительно окна функции (в нашем примере это окно Сцепить)

При заполнении полей окна функции приходится ссылаться на диапазоны рабочего листа. В нашем примере это сводилось к исполнению ударов ЛКМ по ячейкам листа. Часто окно функции загораживает необходимые отсылочные ячейки. Это легко исправить, поскольку окно «плавает» над листом и его можно перетаскивать мышью, применяя процедуру ПиО_ЛКМ и освобождая необходимое пространство рабочего листа. Кроме этого, имеется возможность, свернуть окно до узкой полосы с помощью кнопок, которые находятся с правой стороны каждого поля заполнения окна функции. Эти кнопки присутствуют и в свернутых окнах. Исполнение удара ЛКМ по такой кнопке приводит к развертыванию окна функции, т.е. к возврату в первоначальное положение.

¨ Относительно ввода ссылок в окне функции

Ссылки в полях окна функции можно вводить, исполняя удары ЛКМ по отдельным ячейкам или протаскивая ЛКМ по диапазонам листа в комбинации с клавиатурным набором (т.е. также, как при обычной работе). Но можно их вводить только с клавиатуры как в полях окна, так и в строке формул (или непосредственно в ячейке). Начинающим пользователям рекомендуется первый способ (возникает меньше ошибок!). Второй способ обычно используют опытные пользователи, но риск сделать при этом дополнительную отсылочную ошибку возрастает.

¨ Относительно оператора конкатенации &

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

Пусть, к примеру, имеет место следующая исходная ситуация

,

при которой в ячейку В36 предварительно введен текст северный, а в ячейку С36 – текст ветер. В ячейке D36 требуется получить текстовую строку Сильный северный ветер. Тогда в эту ячейку нужно с клавиатуры ввести формулу:

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

На ячейки В36 и С36 можно сослаться в процессе ввода или редактирования формулы, исполняя по ним! ЛКМ, или набрать их имена непосредственно с клавиатуры (в «латинице»)*. Окончательная фраза имеет вид

¨ Относительно главной формулы

Ввод главной формулы (в нашем примере имеется одна главная формула, в других случаях их может быть более одной) это наиболее ответственный этап работы с пользовательской таблицей, построенной на основе «формульного» автозаполнения. В рассматриваемом примере (на Шаге 1)) главная формула находится в ячейке G4 и имеет вид =СЦЕПИТЬ(F$2; " "; $F4). Обратите внимание и проанализируйте самостоятельно, почему знак $ в первой ссылке стоит перед числом, а во второй – перед буквой. Подсказка здесь такая: при копировании вправо эта формула должна «зацепляться» за столбец с названиями месяцев (столбец F), а при копировании вниз она должна «зацепляться» за строку с текстом Цена и знаком % ( строка 2).

Подчеркнем еще одно обстоятельство, связанное с главной формулой. Главную формулу мы сначала копировали с помощью маркера заполнения по горизонтали, а затем полученный автозаполнением «горизонтальный» блок с (двумя) формулами копировался по вертикали (при этом также использовался маркер заполнения). Однако эти действия можно выполнить и в обратном порядке: сначала главную формулу можно скопировать по вертикали, а затем полученный вертикальный блок (предварительно выделенный!) – скопировать вправо по горизонтали. Результат от этого не изменится (проверьте!).

¨ Относительно перенастройки формул при перемещении и копировании блоков с формулами

Как вытекает из вышеизложенного, перемещение зависимых ячеек приводит к перенастройке формул, но возвращаемые значения при этом остаются прежними. Проверим это положение. Выделим блок, содержащий формулы (это блок G4: Н15), и передвинем его в какое-либо другое место:

Посмотрим на результат:

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

Представляет интерес также и целостная структура взаимосвязей влияющих и зависимых ячеек. Она приведена на следующем рисунке (где стрелки идут от влияющих ячеек к зависимым ячейкам):

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

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

Посмотрим на результат:

.

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

Представляет интерес структура взаимосвязей ячеек-образов и новых влияющих ячеек скопированной таблицы в целом. Она приведена на следующем рисунке:

.

Из последней схемы отчетливо видно, что формулы скопированного блока «зацепляются» за ту же строку и тот же столбец, что и формулы исходного блока (ведь ссылки – смешанные обоих форматов $A1 и A$1!), но произошел сдвиг влияющих ячеек, соответствующий сдвигу блока в целом (на две ячейки вправо по горизонтали и на две ячейки вниз по вертикали). В результате формулы скопированного блока настроились неверно.

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

Шаг 2.

Итак, выполнен Шаг 1 поставленной задачи. Проблема копирования или перемещения блоков с формулами обсуждалась на шаге 1 не случайно: ведь из полученных в диапазоне G4: Н15 данных на следующем шаге нужно сформировать заголовки строк новой таблицы. Копированием столбцов G4: G15 и Н4: Н15 этого достичь нельзя, но казалось бы это можно сделать их перемещением и вставкой в требуемые позиции листа. Однако и в таком варианте действий присутствует свой “подводный камень”: конечно, “разнести” столбцы G4: G15 и Н4: Н15 можно (используя, например, процедуру D& D_ ЛКМ), отображаемые данные при этом сохранятся. Но ведь эти столбцы содержат формулы, которые ссылаются на отдельные ячейки F2, G2 и ячейки диапазона F4: F15 рабочего листа. Последние носят вспомогательный характер, и после того, как новая таблица будет сформирована, их надо будет каким-либо образом “уничтожить”. Нужно осознавать, что, как правило, работа в Ехсеl должна быть завершена печатью документа, а отображение ячеек F2, G2, F4: F15 будет диссонировать со всем остальным содержанием напечатанного документа. Просто очистить эти ячейки (Правка / Очистить) или удалить их (Правка / Удалить) нельзя: это мгновенно приведет к неправильному отображению информации в зависимых от них ячейках. Самым простейшим вариантом решения такой задачи – задачи уничтожения “лишних” данных - является следующий: не печатать ту часть рабочего листа, в которой содержатся вспомогательные данные. Еще лучше под них отвести специальный лист, но в этом случае возникает неудобство, связанное с тем, что в процессе работы нужно переключаться с одного листа на другой. Правда часто бывает, что этого не избежать: структурированность (как правило, разнородных) данных, разнесенных по различным рабочим листам или даже книгам, перекрывает упомянутое неудобство.

Однако существуют три простых способа уничтожения “лишних” данных:

§ Скрыть строки/столбцы, в которых они находятся.

§ Назначить соответствующим ячейкам скрытый формат.

§ Использовать команды Правка / Специальная вставка / Копировать: Значения / < ОК> или процедуру D& D_ПКМ с выбором из контекстного меню команды Копировать только значения.

Алгоритм первого способа:! ЛКМ по имени строки (или столбца) / < Формат> / Строка (или Столбец) / Скрыть.

Замечание: Вместо! ЛКМ по имени строки можно исполнить! ЛКМ по любой выделенной ячейке строки.

Иногда требуется отобразить скрытые строку или столбец. Алгоритм отображения для строки (для столбца аналогичен) таков:

Курсор на разделитель имен строк, между которыми “лежит” скрытая строка так, чтобы он принял форму / !! ЛКМ. ( или: Выделить любые две подряд идущие ячейки в строках, между которыми «лежит» скрытая строка / < Формат> / < Строка> /< Отобразить>).

Отметим, что при скрытии строк/столбцов данные, в них содержащиеся, не уничтожаются; просто соответствующие строки или столбцы Ехсеl не отображаются на листе и при печати. Например, если имена столбцов на листе идут в следующем порядке А, D, E, …, то это означает, что столбцы В, С являются скрытыми, но при желании их можно «раскрыть» с помощью описанного выше алгоритма.

Чтобы реализовать второй способ предварительно требуется создать скрытый формат. Это делается весьма просто: < Ctrl> + < 1> / Число / Все форматы (или Пользовательские форматы) / В поле Тип три раза с клавиатуры ввести точку с запятой (;;;) / < ОК>.

Алгоритм второго способа: Выделить ячейки или блоки, которые нужно скрыть / < Ctrl> + < 1> / Число / Все форматы (или Пользовательские форматы) / В списке Тип выбрать созданный скрытый формат (;;;)/ ОК.

Отметим, что при назначении ячейке скрытого формата данные в ней не уничтожаются, лишь не отображаются: для того, чтобы их «увидеть» (правда, в строке формул), нужно исполнить по ней! ЛКМ.

Результатом применения третьего способа является «физическое» уничтожение (т.е. удаление) формул в соответствующих ячейках; при этом формулы замещаются своими собственными значениями. В нашем случае формулы, полученные на основе функции СЦЕПИТЬ, выполнили свое предназначение: создали необходимые ряды текстовых данных, - и могут быть удалены при условии, что возвращаемые ими данные сохранятся в тех же самых ячейках, где «лежали» формулы. Именно такая ситуация реализуется в третьем способе. В частности, это означает, что блоки с текстовыми константами – заголовками строк будущей таблицы – можно будет (после уничтожения формул, но с сохранением возвра