Материал: 1599

гична взаимосвязи между ячейками и ссылками исходной формулы. Например (см. рис. 24), при копировании формулы ячейки D4 в D5 будет записано =В3, а в D3 =В1.

Рис. 24. Пример ссылок

Абсолютная ссылка указывает точное расположение ячейки на листе и при копировании или перемещении адрес этой ячейки не изменяется. Для написания абсолютной ссылки перед именем столбца и строки записывается знак доллара $B$2. Например (см. рис. 24), в ячейке С4 записано =$B$2. При копировании этой ячейки в С3 и С5 остаётся =$B$2.

При создании формул возникает необходимость в смешанных ссылках, которые возникают в результате комбинаций абсолютных и относительных ссылок. Например, $B2 означает, что координата столбца абсолютная, а строки – относительная, B$2 – координата столбца относительная, строки

– абсолютная.

Для того чтобы поменять относительные ссылки на абсолютные и наоборот, выберите ячейку с требуемой формулой. В строке формул выделите ссылку, которую необходимо поменять, и нажмите функциональную клавишу F4. При каждом нажатии на клавишу F4 тип ссылки будет переключаться в следующей последовательности: абсолютные и столбец и строка ($B$2), относительный столбец и абсолютная строка (B$2), абсолютный столбец и относительная строка ($B2), относительные и столбец и строка (B2) и всё сначала.

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

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

При ссылке на другие листы той же книги =Лист1!В2.

При ссылке на лист другой книги =[Книга2]Лист3!С6.

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

66

Microsoft Excel предусматривает стиль ссылки R1C1. В этом случае ячейка задаётся номером строки и столбца, то есть R1C1 означает строка (Row) 1 столбец (Column) 1. Установить этот стиль можно с помощью пункта меню Сервис-Параметры. В появившемся диалоговом окне Параметры во вкладке Общие установить переключатель стиля ссылок в группе Параметры в положение R1C1. Отрицательное значение номера столбца указывает на столбец, расположенный левее текущего, отрицательное значение номера строки – на строку выше текущей. Этот стиль предусматривает ссылки абсолютные, относительные и смешанные. Если номер строки и столбца заключён в квадратные скобки R[1]C[1], ссылка является относительной, то есть при текущей ячейке А1 ссылка указывает на ячейку В2, расположенную на одну строку ниже и один столбец правее текущей. Если номера столбцов и строк не заключены в скобки R1C1 – ссылка абсолютная, то есть R1C1 аналогично $A$1. Смешанные ссылки получаются следующим образом:

R[-2]C относительная ссылка на ячейку, расположенную на две строки выше и в том же столбце;

R[-1] относительная ссылка на строку, расположенную выше текущей ячейки;

R абсолютная ссылка на текущую строку.

Заголовки и имена. Для упрощения понимания формул Microsoft Excel предусматривает применение заголовков и имён. Заголовки размещаются вверху столбца и слева от строки. При ссылке можно использовать эти заголовки.

Для создания заголовков в диалоговом окне Параметры пункта меню

Сервис-Параметры во вкладке Вычисления в группе Параметры книги ус-

тановить флажок Допускать название диапазонов. После этого можно использовать заголовки строк и столбцов для указания данных. Для присвоения имени столбцу или строке необходимо выделить столбец (строку) и записать заголовок в Поле имени, или воспользоваться пунктом меню Вставка-Имя. При присваивании имени необходимо соблюдать следующие правила:

Имя должно начинаться с буквы, обратной косой черты ‘\’ или символа подчёркивания ‘_’.

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

Нельзя использовать имена, которые могут трактоваться как ссылки на ячейки.

В качестве имён могут использоваться одиночные буквы R и С.

Заменяйте пробелы в именах диапазонов на подчёркивание. Например, на рис. 24 для того чтобы сослаться на данные в ячейке С4

можно либо использовать уже известную вам формулу =С4, либо – форму-

67

лу =Пример_2 Результаты. Пробел в формуле между «Пример 2» и «Результаты» это, как выше сказано, адресный оператор пересечения диапазонов, который вернёт в ячейку с формулой значение ячейки С4 «6». Или для суммирования данных столбца С (см. рис. 24) можно написать =СУММ(Пример_2) и в ячейку будет возвращена сумма диапазона С3:С5 «18».

Будьте внимательны при присвоении заголовков. Заголовки «При-

мер_2» и «Результаты» не записываются в ячейки, как это может показаться из рис. 24. Вернитесь в предыдущий абзац и ещё раз прочитайте, как присвоить заголовок столбцу или строке. В ячейках записываются операнды, перечисленные на стр. 58.

Ячейке или группе ячеек можно присвоить имя и затем использовать его в формулах, например, =Карданный_вал+Маховик вместо =В2+В3, или =СУММ(Карданный_вал: Маховик) вместо =СУММ(В2:В3) (см. рис. 24). Имена могут использоваться как в текущих листах, так и в других листах. Присвоить имя текущей ячейке можно так же как заголовок строке или столбцу.

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

Формула, содержащая ссылку на ту же ячейку, в которую она введенапростейший тип циклической ссылки. При этом возникнет сообщение о циклической ошибке и, после нажатия кнопки OK, в ячейку будет возвращено значение «0».

Если вы не создавали циклической ссылки, то, как правило, сообщение о циклической ссылке, означает, что вы допустили ошибку. Нажмите кнопку OK и проверьте формулу. Если вы не можете найти ошибку, воспользуйтесь панелью инструментов Циклические ссылки. Включать/выключать панели инструментов вы умеете. Стрелки слежения этой панели инструментов укажут как влияющие, так и зависимые ячейки.

Однако существуют инженерные и научные вычисления, требующие циклические ссылки с определённым числом итераций. Microsoft Excel предусматривает создание таких формул. Для этого необходимо в диалоговом окне Параметры (пункт меню Сервис-Параметры) во вкладке Вычисления установить флажок Итерации. На рис. 25 в ячейке А1 записана формула =СУММ(А2:В4), в ячейке В1 =СТЕПЕНЬ(А1;5/6). По умолчанию расчёт прекратился после 100 вычислений или в тот момент, когда изменения значений между итерациями стало меньше 0,001.

Число итераций можно изменить. В той же вкладке, где вы установили флажок Итерации, установите другие данные в полях Предельное число итераций и Относительная погрешность. При нажатии функциональной клавиши F9 значение пересчитывается и становиться более близким к ко-

68

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

Функции

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

Microsoft Excel насчитывает более 300 встроенных функций. Эти функции можно разбить на следующие группы:

математические;

текстовые;

логические;

просмотра и ссылок;

даты и времени;

финансовые;

статистического анализа;

статистические для создания баз данных.

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

=SIN(2).

Пробелы между круглой скобкой и именем функции или аргументом не допускаются. Если пробел установить, в ячейку будет возвращено ошибочное значение #ИМЯ? Некоторые функции не имеют аргумента, например ПИ, ЛОЖЬ, ИСТИНА. Тем не менее, круглые скобки после имени такие функции должны содержать, например:

=Н12+ПИ().

69

При использовании в функции нескольких аргументов они отделяются друг от друга точкой с запятой, например:

=СТЕПЕНЬ(Е56;4/9).

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

=СУММПРОИЗВЕД(А1:А4;А5:В6;D24:E26).

Функции могут быть вложенные, то есть в качестве аргумента используются другие функции, например:

=ABS(ГРАДУСЫ(ASIN(F890)+ACOS(2)+R456*ПИ()-ATAN(2/3))).

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

Аргументы некоторых функций должны иметь определённый тип. Например, аргументом функции КОРЕНЬ может быть только положительное число, или аргументом функции ATAN число в диапазоне от /2 до + /2.

При вводе функций с клавиатуры, без использования мастера функций, обратите внимание на то, что при ссылке на ячейку должен быть включен латинский шрифт. В противном случае в ячейку будет возвращено ошибочное значение #ИМЯ?

Ввод функций можно производить с использованием диалогового окна Мастер функций – шаг 1 из 2. Вызвать это окно можно с помощью пункта меню Вставка-Функция…

или кнопки панели инструментов Стандартная fx .

Это диалоговое окно содержит два поля с вертикальными линейками прокрутки: в левом поле расположены Категории (группы) функции, в правом

– собственно Функции. После выбора требуемой функции и нажатии кнопки OK, появляется второе окно мастера функций. Это окно содержит по одному полю для каждого аргумента. Число полей увеличивается автоматически после ввода очередного аргумента, если функция имеет несколько аргументов. Справа от каждого поля отображается текущее значение каждого аргумента, а внизу под последним аргументом вычисленное значение функции. Круглые скобки и знак равенства вводятся автоматически.

Ошибочные значения. Я уже дважды в пособии упоминал ошибочное значение #ИМЯ?. Ошибочные значения в ячейке появляются тогда, когда

70