Применение Excel в экономике

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

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

Аргумент искомое_значение и элементы в диапазоне могут быть также текс­товыми строками. Например, если ячейки A1:D1 содержат текстовые значе­ния, как показано на рис.11, следующая формула возвратит значение 2: =ПОИСКПОЗ("Двадцать";А1:01;0)

При использовании функции ПОИСКПОЗ для определения позиции тексто­вых строк аргумент тип_сопоставления обычно задается равным 0 (полное совпадение). В этом случае в аргументе искомое_значение можно использо­вать символы шаблона * и ?.

Рис. 11. Функцию ПОИСКПОЗ можно использовать для определения позиции текстовой строки

5.4. Функция ПРОСМОТР

Функция ПРОСМОТР имеет две синтаксические формы. В обе­их формах она аналогична функциям ВПР и ГПР и подчиняется тем же пра­вилам.

5.4.1. Первая форма

Первая (векторная) форма функции ПРОСМОТР:

=ПРОСМОТР(искомое_значение;просмагриваемый_вектор;вектор_результатов)

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

Подобно функциям ГПР и ВПР, функция ПРОСМОТР ищет в просматрива­емом векторе наибольшее сравниваемое значение, не превышающее искомое значение. Затем она выбирает значение из соответствующей позиции в векторе_результатов. Хотя обычно просматриваемый_вектор и вектор_ резуль­татов размещаются в листе параллельно, это совсем не обязательно. Они могут быть расположены в разных областях листа, кроме того, один диапазон может быть горизонтальным, а другой вертикальным. Единственное ограниче­ние состоит в том, что они должны иметь одинаковое число элементов. Например, рассмотрим формулу в листе на рис.12. =ПРОСМОТР(3;ВЗ:В7;ЕЗ:Е7)

Эта формула сравнивает Искомое_значение 3 со значениями в просматриваемом_векторе ВЗ:В7. Третья ячейка этого диапазона (В5) содержит наиболь­шую величину, которая не превосходит искомое значение. Таким образом, функция возвращает число 300, а именно содержимое третьей ячейки диапа­зона результатов (Е5).

Рис.12.  Функцию ПРОСМОТР можно использовать для извлечения информации из заданного диапазона

Теперь рассмотрим формулу в листе на рис.13 с непараллельными диапа­зонами. =ПРОСМОТР(3;А1:А5;06:Н6)

Эта формула возвращает значение 300. Просматриваемый_вектор, А1:А5, и вектор результатов, D6:H6, имеют по пять элементов. Искомое_значение 3 совпадает с числом в третьей ячейке просматриваемого_вектора, и, таким образом, формула возвращает содержимое третьей ячейки диапазона резуль­татов: 300.

Рис. 13.  Функция ПРОСМОТР позволяет извлекать информацию из непараллельных, диапазонов ячеек

5.4.2. Вторая форма

Вторая синтаксическая форма функции ПРОСМОТР предназначена для ра­боты с массивами: =ПРОСМОТР(искомое_значение; массив)

Аргумент искомое_значение — это значение, которое функция ПРОСМОТР ищет в массиве. Функция ПРОСМОТР всегда возвращает значение, получен­ное из последней строки или столбца массива.

Эту форму функции ПРОСМОТР можно использовать как с горизонтальны­ми, так и с вертикальными таблицами. ПРОСМОТР использует размерности таблицы для определения расположения сравниваемых значений. Если табли­ца вытянута в высоту или квадратная, функция рассматривает эту таблицу как вертикальную и считает, что сравниваемые значения находятся в крайнем левом столбце. Если таблица вытянута в ширину, функция рассматривает ее как горизонтальную и считает, что сравниваемые значения находятся в первой строке таблицы.

Поскольку поведение функций ГПР и ВПР более предсказуемо и управляемо, предпочтительнее использовать именно их, а не функцию ПРОСМОТР.

5.5. Функция ИНДЕКС

Функция ИНДЕКС тоже имеет две формы. Одна предназначена для работы с массивами и возвращает одно или несколько значений, а другая возвращает ссылку на ячейку или диапазон ячеек рабочего листа.

5.5.1. Первая форма

Первая синтаксическая форма функции ИНДЕКС работает только с массивом аргументов и возвращает значения, а не ссылки на ячейки. Эта форма функ­ции имеет следующий синтаксис: =ИНДЕКС(массив;номер_строки;номер_столбца)

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

=ИНДЕКС( {ДО; 20; 30:40; 50; 60}; 1; 2)

5.5.2. Вторая форма

Вторая синтаксическая форма функции ИНДЕКС возвращает адрес ячейки. Она полезна в тех случаях, когда нужно выполнить операции с

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

Функция ИНДЕКС имеет два достоинства: во-первых, в качестве просматри­ваемого диапазона может использоваться область листа, состоящая из не­скольких несмежных диапазонов; во-вторых, функция может возвратить диа­пазон (несколько ячеек). Ссылочная форма этой функции имеет следующий синтаксис: