5.4. Вложенные функции ЕСЛИ

Иногда бывает очень трудно решить логическую задачу только с помощью операторов сравнения и функций И, ИЛИ и НЕ. В этих случаях можно использовать вложенные функции ЕСЛИ. Например, в следующей формуле используются три функции ЕСЛИ: =ЕСЛИ(А1=100;"Всегда";ЕСЛИ(И(А1>=80;А1<100);"Обычно";

ЕСЛИ(И(А1>=60;А1<80);"Иногда";"Увы!")))

Если значение в ячейке А1 является целым числом, формула читается следу­ющим образом: «Если значение в ячейке А1 равно 100, возвратить строку Всегда. В противном случае, если значение в ячейке А1 находится между 80 и 100 (точнее, от 80 до 99 включительно), возвратить строку Обычно. В про­тивном случае, если значение в ячейке А1 находится между 60 и 80 (от 60 до 79 включительно), возвратить строку Иногда. И наконец, если ни одно из этих условий не выполняется, возвратить строку Увы!».

Всего допускается до семи уровней вложения функций ЕСЛИ, но при этом, конечно, должно соблюдаться ограничение по максимальной длине значения в ячейке (255 символов).

9.5. Функции ИСТИНА и ЛОЖЬ

Функции ИСТИНА и ЛОЖЬ предоставляют альтернатив­ный способ записи логических значений ИСТИНА и ЛОЖЬ. Эти функции не имеют аргументов и выглядят следующим образом: =ИСТИНА(), =ЛОЖЬ(). Например, предположим, что ячейка В5 содержит логическое выражение, тогда следующая формула возвратит строку Внимание!, если логическое вы­ражение в ячейке В5 имеет значение ЛОЖЬ: =ЕСЩВ5=ЛОЖЬ(); "Внимание!"; "ОК"). Иначе формула возвратит строку ОК.

10. Функции просмотра и ссылок

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

10.1. Функция АДРЕС

Функция АДРЕС предоставляет удобный способ создания ссыл­ки из чисел. Эта функция имеет следующий синтаксис:

=АДРЕС(номер_строки; номер_столбца; тип_ссылки; а 1; имя_листа). Аргументы номер_строки и номер_столбца определяют строку и столбец формируемого адреса. Аргумент тип_ссылки задает тип возвращаемой ссылки: 1 - абсолютная ссылка, 2 — смешанная (абсолютная строка и относительный столбец), 3 — смешанная (относительная строка и абсолютный столбец), 4 — относительная. Аргумент а1 — это логическое значение. Если а1 имеет значение ИСТИНА, функция АДРЕС возвращает ссылку в стиле Al, a если этот аргумент имеет значение ЛОЖЬ, то функция возвращает ссылку в стиле R1C1. Имя_листа — это текст, задающий имя листа в создаваемой ссылке. Если этот текст состоит из нескольких слов, то при построении ссылки Excel заключает имя листа в одинарные кавычки. Например, следую­щая формула возвращает текст ссылки 'Лист Excel !$AS 1:

=АДРЕС(1;1;1;ИСТИНА;'Лист Excel’).

10.2. Функция ВЫБОР

Функция ВЫБОР возвращает значение элемента из списка аргу­ментов. Эта функция имеет следующий синтаксис:

=ВЫБОР(номер_индекса,'значение 1;значение 2; .;значение 29)

Аргумент номер_индекса — это номер элемента в просматриваемом списке, а значение 1, значение 2 и т. д. — элементы списка. Номер_индекса должен

быть положительным и не может превышать количество элементов в списке.

Если номер_индекса меньше 1 или больше количества значений в списке, Excel возвращает ошибочное значение #ЗНАЧ!. Функция ВЫБОР возвращает значение элемента списка, который занимает позицию, заданную аргументом номер_индекса. Например, следующая функ­ция возвращает значение 1, поскольку 1 является вторым элементом в списке, а значение номер_индекса не рассматривается как часть списка:

=ВЫБОР(2;6;1;8;9;3).

Аргументы функции ВЫБОР могут быть ссылками на ячейки. Если вы использовали ссылку на ячейку для аргумента номер_индекса, Excel выбирает элемент из списка в соответствии со значением, хранящимся в этой ячейке. Предположим, что ячейка А1 содержит формулу =ВЫБОР(А10;0,15;0,22;0,21;0,21;0,26)

Если в ячейке А10 находится число 5, функция ВЫБОР возвратит значе­ние 0,26, а если ячейка А10 содержит значение 1, результатом функции бу­дет 0,15.

Подобным образом, если ячейка С1 содержит значение 0,15, в ячейке С2 записано число 0,22, а в каждой из ячеек СЗ, С4 и С5 — 0,21, то следующая формула возвратит значение 0,15 в том случае, если ячейка А10 содержит значение 1, а если в ней находится значение 3, 4 или 5, формула возвра­тит 0,21: =ВЫБОР(А10;С1;С2;СЗ;С4;С5)

В качестве аргумента нельзя задавать диапазон. Если вы попытаетесь заме­нить функцию из предыдущего примера представленной ниже функцией, то в результате получите ошибочное значение #ЗНАЧ!. =ВЫБОР(А10;С1:С5). Элементы в списке могут быть текстовыми строками. Например, следующая функция выбирает третий элемент из списка и возвращает строку Третий.

=ВЫБОР(3; "Первый";"Второй";"Третий")

5.3. Функция ПОИСКПОЗ

Функция ПОИСКПОЗ тесно связана с функцией ВЫБОР. Од­нако если функция ВЫБОР возвращает значение элемента списка, кото­рый занимает позицию, задаваемую аргументом номер_индекса, то функция ПОИСКПОЗ возвращает позицию элемента в списке, который в наибольшей степени соответствует искомому значению. Эта функция имеет следующий ' синтаксис:

=ПОИСКПОЗ(искомое_значение;просматриваемый_массив;тип_сопоставления) Аргумент искомое_значение — это исходное искомое значение или строка, а просматприваемый_массив — диапазон, который содержит значения, сравни­ваемые с искомым значением.

Пусть в ячейку Е1 рабочего листа, показанного на рис.9, введена формула =ПОИСКПОЗ(10;А1:01;0). Тогда результат окажется равен 1, поскольку первая ячейка просматриваемого массива содержит значение, совпадающее с искомым.

Аргумент тип_сопоставления определяет принцип поиска и может принимать значение 1, 0 или -1. Если тип_сопоставления равен 1 или опущен, функция ПОИСКПОЗ ищет в диапазоне наибольшее значение, которое меньше или равно искомому значению. При этом просматриваемый_массив должен быть отсортирован по возрастанию. Например, рассмотрим формулу в рабочем листе, представленном на рис. 9: =ПОИСКПОЗ(19;А1:D1;1). Эта формула возвратит значение 1, поскольку наибольшее значение в диапа­зоне A1:D1, не превосходящее искомое_значение, — это число 10, то есть первый элемент в этом диапазоне. Если функция не находит в заданном диапазоне элементов, которые меньше или равны искомому значению, то она возвращает ошибочное значение #Н/Д.

Рис.9 Функция ПОИСКПОЗ определяет позицию значения в списке

Взгляните на рис.10, и вы увидите, что может произойти, если просматриваемый_массив не отсортирован по возрастанию. Следующая формула возвращает 1 вместо ожидаемого значения 4: =ПОИСКПОЗ(20;А1:01;1)

Рис.10. Для правильной работы функции ПОИСКПОЗ необходимо, чтобы просматриваемый массив был отсортирован по возрастанию

Если тип_сопоставления равен 0, функция ПОИСКПОЗ ищет в диапазоне первое значение, которое точно совпадает с искомым значением. В этом слу­чае просматриваемый_массив может быть и не отсортированным. Если, в диапазоне нет элемента, полностью совпадающего с искомым значением, функ­ция возвращает #Н/Д.