Материал: 1480

Текстовый оператор & (амперсант) используется для объединения последовательностей символов в единую последовательность. Например, =”Стеклянная”&”стена” результат Стеклянная стена. Или: если в ячейку А3 записать =А1&A2, в ячейку А1 Стеклянная, в ячейку А2 – стена, то в ячейке А3 будет возвращено текстовое значение Стекляннаястена. Для того чтобы вставить пробел между словами, формулу необходимо изменить: =А1&” “&A2. Эта формула содержит два текстовых оператора и

текстовую константу – пробел.

С помощью этого оператора можно объединить и числовые значения, и текстовые и числовые значения.

Адресные операторы (табл. 1). Эти операторы объединяют диапазоны ячеек для осуществления вычислений.

Ссылки. Ссылка является идентификатором ячейки (D15) или группы ячеек (G56:R98). Ссылка указывает, где расположены данные, используемые в формуле. Создавая формулу со ссылками на ячейки, вы связываете формулу с ячейками. Теперь формула зависит от содержимого ячеек, на которые указывают ссылки. Значение формулы при этом изменяется при изменении содержимого этих ячеек.

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

ми.

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

66

Относительные, абсолютные и смешанные ссылки. Относительная ссылка указывает местоположение ячейки, относительно той, в которой расположена формула. Например (рис. 24), ячейка D4 содержит формулу =В2, то есть искомое значение находится на две строки выше и на два столбца левее ячейки D4.

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

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

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

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

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

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

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

67

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

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

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

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

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 предусматривает применение заголовков и имён. Заголовки размещаются вверху столбца и слева от строки. При ссылке можно использовать эти заголовки.

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

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

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

68

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

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

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

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

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

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

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

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

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

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

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

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

Однако существуют инженерные и научные вычисления, требующие циклические ссылки с определённым числом итераций. Microsoft Excel

69

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

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

Функции

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

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

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

текстовые;

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

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

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

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

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

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

70