Ссылки на ячейку или на группу ячеек

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

«По умолчанию» в Microsoft Excel используются ссылки типа «A1», в которых столбцы обозначаются буквами от A до IV (256 столбцов максимально), а строки числами — от 1 до 65536. Альтернативный способ, когда названия столбцов указываются арабскими цифрами и в адресе сначала указывается номер строки, а потом столбца – тип «R 1 C 1» (изменить тип ссылок можно в меню Сервис, Параметры, Общие, Стиль ссылок R 1 C 1). Чтобы указать ссылку на ячейку, введите адрес (координаты) ячейки, то есть букву заголовка столбца, а затем номер строки. Например, D50 является ссылкой на ячейку, расположенную в пересечении столбца D с 50- й строкой. Чтобы сослаться на диапазон ячеек, введите ссылку на верхний левый угол диапазона, поставьте двоеточие (:), а затем — ссылку на правый нижний угол диапазона.

Ссылки можно делать не только на ячейки текущего листа, но и на ячейки других листов и файлов (книг). При этом в ссылке должно быть название листа, на ячейки которого делается ссылка, отделенное от адреса ячеек восклицательным знаком, например, Лист1!A10. А при ссылке на другую книгу добавляется название книги в квадратных скобках [Книга2]Лист5!$A$5.

Таблица 1Варианты возможных ссылок

Чтобы сослаться на	Введите
Ячейку столбца A и 10-й строки	A10
Диапазон ячеек столбца A с 10-й строки по 20-ю	A10:A20
Диапазон ячеек в 15-й строке со столбца B по столбец E	B15:E15
Все ячейки в 5-й строке	5:5
Все ячейки между 5-й и 10-й строками включительно	5:10
Все ячейки в столбце H	H:H
Все ячейки между столбцами H и J включительно	H:J

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

При перемещении формулы ссылки, расположенные внутри формулы, не изменяются. При копировании формулы абсолютные ссылки не изменяются, а относительные ссылки изменяются.

При создании формулы ссылки обычно изменяются относительно расположения ячейки, содержащей формулу. В следующем примере ячейка С5 содержит формулу =A5*В5. Ссылка на ячейку А5 (как и В5) называется относительной (т.е. адрес относительно названия столбца и номера строки).

	A	B	С
5	100	2	=А5*В5
6	200	5
7

При копировании формулы, содержащей относительные ссылки в другие ячейки ссылки, будут меняться относительно адреса новой ячейки. В следующем примере формула из ячейки С5 копируется в ячейку С6. В ячейке С6 будет содержаться формула =A6*В6, которая при копировании на 1 строку ниже изменила номер строки на 1. А при копировании формулы из ячейки С5 вячейку D6, будет содержаться формула = B6* C6, в которой изменился адрес ячеек относительно перемещения на 1 строку ниже и 1 столбец вправо.

	A	B	С	D
5	100	2	=А5*В5
6	200	5	=A6*В6	=B6*C6
7

Если необходимо, чтобы ссылки не изменялись при копировании формулы в другую ячейку, воспользуйтесь абсолютными ссылками. Например, если необходимо умножить все значения столбца А на содержимое ячейки B5 (=A5*В5, =A6*В5, =A7*В5 и т.д.), то чтобы не вводит формулу в каждую ячейку, а копировать ее, необходима абсолютная ссылка. Для создания абсолютной ссылки на ячейку В5, поставьте знак доллара ($) перед той частью адреса, которая не должна изменяться (В$5). Чтобы создать абсолютную ссылку на ячейку В5, поместите знак доллара так:

=A5*$В$5

При копировании такой формулы, как по строкам, так и по столбцам, ссылка на ячейку не будет меняться В5. И, например, после копирования ее в ячейку D 45 будет формула =В45*$В$5.

Можно фиксировать и диапазоны ячеек: $В$2:$В$20.

Для удобства, при создании абсолютной ссылки в режиме ввода и редактирования формул можно использовать клавишу F4. Поставив курсор у адреса ячейки и нажав 1 раз F4, будет зафиксирована вся ячейка ($C$1 – абсолютная ссылка), 2 раза – только номер строки (C$1 – абсолютная ссылка по строке), 3 раза – только название столбца ($C1 – абсолютная ссылка по столбцу) и 4 раза – снимается фиксация (C1 – относительная ссылка).

 

Табличные вычисления

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

Текст формулы, которая вводится в ячейку таблицы, должен начинаться со знака равенства (=), чтобы программа Excel могла отличить формулу от текста. Знак равенства вводится с клавиатуры или нажатием на кнопку  = - Редактирование формулы  в строке формул. После знака равенства в ячейку записывается математическое выражение, содержащее аргументы, арифметические операции и функции.

В качества аргументов в формуле обычно используются числа и адреса ячеек. Для обозначения арифметических операций могут использоваться следующие символы: + (сложение); - (вычитание); Ч (умножение); / (деление); ^ (возведение в степень).

Предположим, что в ячейке А1 таблицы находится число 100, а в ячейке В1 - число 20. Чтобы разделить первое число на второе и результат поместить в ячейку С1, в ячейку С1 следует ввести формулу =А1/В1 и нажать на клавиатуре Enter или в строке формул кнопку   P  . Адреса ячеек А1 и В1 вводятся латинским буквами.

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

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

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

 

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

Чтобы начать редактировать содержимое ячейки, нужно сначала выделить эту ячейку. Далее включить режим редактирования, нажав клавишу F2 или выполнив двойной щелчок мышью. Для редактирования формул можно нажать кнопку = в строке формул. В режиме редактирования активизируется строка формул, в которой виден текст формулы, а не результат ее вычисления.

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

Где был выполнен щелчок, появится мерцающий текстовый курсор. С этой позиции можно вводить новые символы с клавиатуры. Щелчок по кнопке Р (Esc)слева от формулы отменяет все произведенные изменения, так что содержимое ячейки остается без изменения. Щелчок по кнопке ь (Enter)слева от формулы подтверждает изменение, и в ячейке появляется новое значение.

 

Мастер функций

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

Для быстрого поиска необходимой функции они разделены по категориям: 10 недавно использовавшихся, Полный алфавитный перечень, Финансовые, Дата и время, Математические, Статистические, Ссылки и массивы, Работа с базой данных, Текстовые, Логические и Проверка свойств. Встроенный Мастер функций помогает правильно применять функции. Он позволяет построить и вычислить большинство функций.

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

Например:

=СУММ(А5:А9) – сумма ячеекА5, А6, А7, А8, А9;

=СРЗНАЧ(G4:G6) – среднее значение ячеекG4, G5, G6.

Функции могут быть вложенными одна в другой, например:

=ОКРУГЛ(СРЗНАЧ(H4:H8);2).

Функция расчета среднего значения является вложенной в функцию округления. При расчете сначала выполняется вложенная функция. Можно сделать в формуле не более 7 вложенностей.

 

Рисунок 3 Строка формул и диалоговое окно функции Просмотр

 

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

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

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

После нажатия кнопки ОК появится диалоговое окно выбранной функции (например, функция Просмотр,

Необходимо в полях обязательных и необязательных параметров этого окна ввести аргументы функции (числовые значения или ссылки на ячейки). Чтобы указать аргументы, можно щелкнуть кнопку , находящуюся справа от поля, и выделить мышью ячейки, содержащие аргументы функции; для выхода из этого режима следует щелкнуть кнопку Х, которая находится справа от поля или клавишу Esc. Для завершения ввода формулы нажмите ОК.

Названия Обязательных параметров выделены жирным шрифтом, эти поля обязательно подлежат заполнению. Если не введены обязательные параметры при нажатии кнопки ОК будет выдано сообщение об ошибке. Необязательные параметры заполняются по необходимости.

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

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

Редактирование функций

Щелкните мышью по ячейке, где находится функция. Затем щелчком по пиктограмме Мастер функций откройте соответствующее диалоговое окно.

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

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

Где был выполнен щелчок, появится мерцающий текстовый курсор. С этой позиции можно вводить новые символы с клавиатуры. Щелчок по кнопке Отмена Р слева от формулы отменяет все произведенные изменения, так что содержимое ячейки остается без изменения. Щелчок по кнопке   ь   (Enter)слева от формулы подтверждает изменение, и в ячейке появляется новое значение.

Вычисление суммы

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

В наборной строке появится знак равенства и слово SUM, за которым в скобках обычно следует адрес некоторой области, которую программа предлагает после анализа близлежащих ячеек в качестве области суммирования. Если Excel "угадал" правильно, нажмите Enter.

В противном случае выделите группу ячеек, которые нужно сложить. Можно выделить несколько несмежных групп ячеек, используя клавишу Ctrl. Эту клавишу нужно удерживать в нажатом состоянии до тех пор, пока все группы не будут выделены.

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

 

Массивы формул

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

Массивы формул удобно использовать для введения однотипных формул и обработки данных в виде таблиц. Например, для вычисления модуля от чисел, размещенных в ячейках A1, B1, C1, D1, E1, вместо ввода формул в каждую ячейку можно ввести одну формулу–массив для всех ячеек. Microsoft Excel добавляет вокруг массива формул фигурные скобки { }, по которым его можно отличить.

Для создания массива формул необходимо:

- выделить ячейки, в которых должен находиться массив формул;

- ввести формулу обычным способом, указав в качестве аргументов группу ячеек-аргументов;

- в последнем окне вместо кнопки ОК нажать комбинацию клавиш Ctrl+Shift+Enter.

Для редактирования массива формул необходимо:

- выделить ячейки, в которых находится массив;

- щелкнуть мышью внутри строки редактирования и отредактировать формулу;

- нажать комбинацию клавиш Ctrl+Shift+Enter.

 

Рисунок 4 Массив формул

 

Другой пример формулы массива для одной ячейки: ={СУММ(ЕСЛИ(А1:Е1=-12; А2:Е2;0))},

Эта формула просматривает все ячейки массива А1:Е1 и если они равны –12 суммирует соответствующие ячейки строки 2.