Абсолютные и относительные ссылки. Стандартные формулы и функции. Логические функции

⇐ ПредыдущаяСтр 39 из 46Следующая ⇒

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

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

При обращении к ячейке в формуле обычно указывается ее номер. Такая ссылка называется относительной.

Например, Al, C4.

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

Например, в ячейке В4 находится формула =В1+В2. При копировании этой формулы в ячейку С4 она примет вид =С1+С2. В ячейке А4 находится формула =А1+А2. При копировании этой формулы в ячейку А5 она примет вид =А2+А3.

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

Например, $А$1, $В$1 и т.п.

Кроме этого, можно использовать смешанные ссылки, например, А$1 или $А1. Часть ссылки, не содержащая знак «$», будет обновляться при копировании, а другая часть, со знаком «$», останется без изменения.

Формулы могут ссылаться на ячейки, находящиеся в других рабочих листах. Формат такой ссылки: «< имя листа>! < адрес ячейки> ». Чтобы при редактировании формулы использовать ячейку, находящуюся на другом листе, надо перед выбором ячейки щелкнуть на ярлычке нужного листа.

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

Можно работать с диапазоном ячеек на нескольких рабочих листах. В этом случае ссылка на данный диапазон осуществляется следующим образом: Лист1: ЛистЗ! А1: С12

В трехмерных ссылках при перемещении и копировании листов происходят изменения.

Например, используется формула =СУММ(Лист2: Лист6! А2: А5), суммирующая содержимое ячеек с А2 по А5 с листа 2 по лист 6 включительно: — если между листом 2 и листом 6 Книги вставить новые

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

— если между листом 2 и листом 6 книги удалить листы, содержимое ячеек удаленных листов будет исключено из суммы;

— если переместить листы, находящиеся между листом 2 и листом 6 книги, и разместить их таким образом, что они будут расположены перед листом 2 или после листа 6, содержимое ячеек перемещенных листов будет исключено из суммы;

— если переместить лист 2 или лист 6 в новое место книги, в сумму войдет содержимое ячеек листов, находящихся между листом 2 и листом 6 включительно;

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

Значение ошибок в формуллх

Excel выводит в ячейку значение ошибок, когда формула для этой ячейки не может быть правильно вычислена. Если формула содержит ссылку на ячейку, которая содержит значение ошибки, то эта формула также будет выводить значение ошибки (за исключением тех случаев, когда используются специальные функции рабочих листов ЕОШ, ЕОШИБКА или ЕНД, которые проверяют наличие ошибок).

Код ошибки	Возможные причины
#Дел/0!	В формуле делается попытка деления на ноль
#Н/Д	Нет допустимого значения. Обычно такое значение непосредственно вводится в те ячейки рабочего листа, которые впоследствии будут содержать данные, отсутствующие в настоящий момент. Формулы, ссылающиеся на такие ячейки, также возвращают #Н/Д в место вычисленного значения
#ИМЯ?	Excel не может распознать имя, использованное в формуле
#ПУСТО!	Было задано пересечение двух областей, которые не имеют общих ячеек
#ЧИСЛО!	Возникли проблемы с числом
#ССЫЛКА!	Формула неправильно ссылается на ячейку
#ЗНАЧ!	Аргумент или операнд имеет недопустимый тип

Использование функций

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

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

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

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

Логические функции

Логические функции являются неотъемлемыми компонентами многих формул.

Они используются всякий раз, когда необходимо выполнить те или иные действия в зависимости от выполнения каких-либо условий. В Excel имеются следующие логические функции: ЕСЛИ, И, ИЛИ, ИСТИНА, ЛОЖЬ, НЕ.

Функция ЕСЛИ (арг_лог, aprl, арг2) возвращает значение aprl, если арг_лог при вычислении приобретает

значение ИСТИНА, и арг2, если арг_лог приобретает значение ЛОЖЬ.

Если арг_лог имеет значение ИСТИНА и значение aprl опущено, то возвращается значение ИСТИНА. Если арг_- лог имеет значение ЛОЖЬ и значение арг2 опущено, то возвращается значение ЛОЖЬ.

При конструировании более сложных проверок в качестве значений аргументов aprl и арг2 могут быть вложены до семи функций ЕСЛИ.

Пример.

Введите в ячейку ЕЗ формулу = ЕСЛИ(Е1= 5; " Правиль-ный ответ"; " Ошибка" )

Введите в ячейку Е1 число 4. В ячейке ЕЗ появилось слово " Ошибка"

Введите в ячейку Е1 число 5. В ячейке ЕЗ появилась
надпись " Правильный ответ". Такая конструкция позволя
ет создавать достаточно сложные тестовые программы,
например, такие, в которых следует выбрать номер
правильного варианта из многих_____________________

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

Пример.

Важно, что значение ИСТИНА не тождественно числу 1, поэтому когда в ячейке A3 находится число 1, результатом работы формулы =ЕСЛИ(АЗ =истина; ИСТИНА; ЛОЖЬ), будет логическое значение ИСТИНА, т.е. истинно то, что в ячейке A3 находится единица.

Однако, при арифметических действиях логическое значение ИСТИНА ведет себя как число 1. Например, если в ячейке А1 находится значение ИСТИНА, а в ячейке В1 — какое-нибудь отрицательное число, например -10, 5, то результатом формулы =А1+В1 будет число —9, 5 а результатом произведения =А1*В1 будет число -10, 5. Аналогично логическое значение ЛОЖЬ не тождественно числу 0, но в арифметических действиях ведет себя как число 0.

Выполните действия, описанные в примере, самостоятельно.

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

⇐ Предыдущая 34 35 36 37 383940 41 42 43 Следующая ⇒