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

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

Примеры использования ссылок:

СРЗНАЧ (С2:С6) — вычисление среднего значения содержимого ячеек с С2 по С6;

СУММ (D4;Лист2!D6:E6) — вычисление суммы содержимого ячейки D4 и диапазона ячеек D6:E6 на листе Лист 2;

СУММ (В1;[Книга1]Лист1!$D$4) — вычисление суммы содержимого ячейки В1 и ячейки D4 на листе Лист 1 в книге Книга 1.

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

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

В следующем примере ячейка В6 содержит формулу =А5; искомое значение находится на одну ячейку выше и левее ячейки В6. Такая ссылка называется относительной.

Рисунок 4.. Относительная ссылка

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

Кроме относительных и абсолютных, ссылки бывают и смешанными, когда изменяется только одна часть адреса ячейки, например:

$А10 — изменяется только номер строки, а столбец всегда постоянный;

А$10 — изменяется только буква столбца, номер строки — постоянный.

Для изменения типа ссылки можно воспользоваться клавишей F4, предварительно в формуле выделив адрес нужной ячейки.

Ввод формулы

1. Выберите ячейку, в которую необходимо ввести формулу.

2. Введите = (знак равенства). Чтобы начать набор формулы с функции, нажмите кнопку Изменить формулу или Вставка функции. При этом автоматически вставляется знак равенства.

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

4. Нажмите клавишу ENTER.

Функции в Excel

Функции задаются с помощью формул, которые выполняют вычисления по заданным величинам, называемым аргументами, и в указанном порядке, называемом синтаксисом. Список аргументов может состоять из чисел, текста, логических величин (ИСТИНА или ЛОЖЬ), массивов, значений ошибок (#Н/ Д) или ссылок. Необходимо следить за соответствием типов аргументов. Кроме того, аргументы могут быть как константами, так и формулами. Эти формулы, в свою очередь, могут содержать другие функции.

Рисунок 5. Элементы функции

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

Рисунок 6.Составная функция

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

1. Нажмите кнопку Изменить формулув строке формул.

2. Нажмите кнопку со стрелкой, расположенную справа от поля Функции.

3. Выберите функцию, вставляемую в формулу. Если функция отсутствует в списке, выберите пункт Другие функции, после чего запустится мастер функций.

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

5. Нажмите кнопку ОК.

Категории функций Microsoft Excel

В Microsoft Excel используется более 100 функций, объединенных по категориям:

• Функции работы с базами данных можно использовать, если необходимо убедиться в том, что значения списка соответствуют условию. С их помощью, например, можно определить количество записей в таблице о продажах или извлечь те записи, в которых значение поля «Сумма» больше 1000, но меньше 2500.

• Функции работы с датой и временем позволяют анализировать и работать со значениями даты и времени в формулах. Например, если требуется использовать в формуле текущую дату, воспользуйтесь функцией СЕГОДНЯ, возвращающей текущую дату по системным часам.

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

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

• Информационные функции предназначены для определения типа данных, хранимых в ячейке. Они проверяют выполнение какого-то условия и возвращают в зависимости от результата значение ИСТИНА или ЛОЖЬ. Так, если ячейка содержит четное значение, функция НЕЧЁТН возвращает значение ИСТИНА. Если в диапазоне функций имеется пустая ячейка, можно воспользоваться функцией СЧИТАТЬ ПУСТОТЫ.

• Логические функции предназначены для проверки выполнения условия или дня проверки нескольких условий. Так, функция ЕСЛИ позволяет определить, выполняется ли указанное условие, и возвращает одно значение, если условие истинно, а другое — если оно ложно.

• Функции ссылки и автоподстановки осуществляют поиск в списках или таблицах. Например, для поиска значения в таблице используйте функцию ВПР, а для поиска положения значения в списке — функцию ПОИСК ПОЗ.

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

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

• Функции обработки текста позволяют производить действия над строками текста, например, изменить регистр или определить длину строки. Можно также объединить несколько строк в одну. Например, с помощью функций СЕГОДНЯ и ТЕКСТ можно создать сообщение, содержащее текущую дату, и привести его к виду «дд-ммм-гг»:= «Балансовый отчет от» &ТЕКСТ(СЕГОДНЯ), «дц-мм-гг»)

Ошибки в формулах

При появлении сообщения Ошибка в формуле:

• Проверьте, одинаково ли количество открывающих и закрывающих скобок.

• Проверьте правильность использования оператора диапазона при ссылке на группу ячеек.

• Проверьте, все ли необходимые аргументы введены для функций.

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

• Проверьте, в каждой ли внешней ссылке указано имя книги и полный путь к ней.

• Не изменяйте формат чисел, введенных в формулы. Например, даже если в формулу необходимо ввести 1000 р., то введите число 1000.

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

Ошибка #ДЕЛ/0! Ошибка появляется, когда в формуле делается попытка деления на ноль.

Ошибка #Н/Д. Значение ошибки #Н/Д является сокращением термина "Неопределенные Данные". Это значение помогает предотвратить использование ссылки на пустую ячейку. Ошибка может возникнуть, если не заданы один или несколько аргументов стандартной или пользовательской функции, а также задан недопустимый аргумент.

Ошибка #ИМЯ? Ошибка #ИМЯ? появляется, когда Excel не может распознать имя, используемое в формуле. Возможная причина:

• Используемое имя было удалено или не было определено.

• Имеется ошибка в написании имени.

• Имеется ошибка в написании имени функции.

• В формулу введен текст, не заключенный в двойные кавычки.

• В ссылке на диапазон ячеек пропущен знак двоеточия (:).

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

Ошибка #ССЫЛКА! Ошибка #ССЫЛКА! появляется, когда используется недопустимая ссылка на ячейку.

Ошибка #ЗНАЧ! Ошибка #ЗНАЧ! появляется, когда используется недопустимый тип аргумента или операнда. Например, вместо числового или логического значения введен текст и Microsoft Excel не может преобразовать его к нужному типу данных.

Упражнение для самостоятельной работы