Функции категории «Ссылки и массивы»

Автор: Вардомацкая Елена Юрьевна, ст. преподаватель кафедры математики и информационных технологий УО "Витебский государственный технологический университет".

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

Искомое_значение - это значение, которое ПРОСМОТР ищет в первом векторе. Искомое_значение может быть числом, текстом, логическим значением, именем или ссылкой, ссылающимися на значение.

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

Важно! Значения в аргументе просматриваемый_вектор должны быть расположены в порядке возрастания:..., -2, -1, 0, 1, 2,..., A-Z, ЛОЖЬ, ИСТИНА; в противном случае функция ПРОСМОТР может вернуть неверный результат. Тексты в нижнем и верхнем регистре считаются эквивалентными.

Вектор_результатов - это интервал, содержащий только одну строку или один столбец. Он должен быть того же размера, что и просматриваемый_вектор.

· Если ПРОСМОТР не может найти искомое_значение, то подходящим считается наибольшее значение в аргументе просматриваемый_вектор, которое меньше, чем искомое_значение.

· Если искомое_значение меньше, чем наименьшее значение в аргументе просматриваемый_вектор, то функция ПРОСМОТР возвращает значение ошибки #Н/Д.

ВПР(искомое_значение;инфо_таблица;номер_столбца;интервальный_просмотр)

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

Искомое_значение - это значение, которое должно быть найдено в первом столбце массива. Искомое_значение может быть значением, ссылкой или текстовой строкой.

Инфо_таблица - это таблица с информацией, в которой ищутся данные. Можно использовать ссылку на интервал или имя интервала, например, БазаДанных или Список.

· Если интервальный_просмотр имеет значение ИСТИНА, то значения в первом столбце аргумента инфо_таблица должны быть расположены в возрастающем порядке:..., -2, -1, 0, 1, 2,..., A-Z, ЛОЖЬ, ИСТИНА; в противном случае функция ВПР может выдать неправильный результат. Если интервальный_просмотр имеет значение ЛОЖЬ, то инфо_таблица не обязана быть сортированной.

• Если ВПР не может найти искомое_значение и интервальный_просмотр имеет значение ИСТИНА, то используется наибольшее значение, которое меньше, чем искомое_значение.
• Если искомое_значение меньше, чем наименьшее значение в первом столбце аргумента инфо_таблица, то функция ВПР возвращает значение ошибки #Н/Д.
• Если ВПР не может найти искомое_значение и интервальный_просмотр имеет значение ЛОЖЬ, то ВПР возвращает значение ошибки #Н/Д.

Номер_столбца - это номер столбца в массиве инфо_таблица, в котором должно быть найдено соответствующее значение. Если номер_столбца равен 1, то возвращается значение из первого столбца аргумента инфо_таблица; если номер_столбца равен 2, то возвращается значение из второго столбца аргумента инфо_таблица и так далее. Если номер_столбца меньше 1, то функция ВПР возвращает значение ошибки #ЗНАЧ!; если номер_столбца больше, чем количество столбцов в аргументе инфо_таблица, то функция ВПР возвращает значение ошибки #ССЫЛ!.

Пример:

Поиск данных в таблице.

Таблица для выполнения поиска расположена в ячейках D2:F9.

Рабочая таблица A2:B6 расположена т.о., чтобы пользователь мог ввести номер участника в ячейку B2 и в ячейках B4 и B5 получить необходимую информацию из исходной таблицы. Формулы имеют следующий вид:

В ячейке B5 è =ВПР($B$2;$D$2:$F$9;2)

В ячейке B6 è =ВПР($B$2;$D$2:$F$9;3)

Формула в ячейке В5 просматривает первый столбец таблицы D2:F9 в поисках величины введенной в ячейку В2 (это значение 205). Она выбирает соответствующее ему (205-ти) значение из столбца, номер которого задан в формуле (это столбец 2) и возвращает полученное значение (имя Федор) в ячейку В5. Аналогично работает формула в ячейке В6.

Если введенное значение не найдено в таблице, формула возвратит #Н/Д. С помощью функции ЕНД можно изменить формулу т.о., чтобы сообщение было более понятным:

=ЕСЛИ(ЕНД(ВПР($B$2;$D$2:$F$9;2;ЛОЖЬ));"Не найдено";ВПР($B$2;$D$2:$F$9;2;ЛОЖЬ)).

Функция ГПР работает точно так же, как и ВПР, с той лишь разницей, что просматривает значения ячеек первой строки таблицы (выполняет поиск по горизонтали).

Ÿ ВЫБОР(номер_индекса;значение1;значение2;…)

Использует номер_индекса, чтобы выбрать и вернуть значение из списка аргументов-значений. Функция ВЫБОР используется, чтобы выбрать одно значение из списка, в котором может быть до 29 значений. Например, если значения от значение1 до значение7 — это дни недели, то функция ВЫБОР возвращает один из дней при использовании числа от 1 до 7 в качестве аргумента номер_индекса.

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

· Если номер_индекса равен 1, то функция ВЫБОР возвращает значение1; если он равен 2, то функция ВЫБОР возвращает значение2 и так далее.

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

· Если номер_индекса является дробным, то он усекается до меньшего целого.

Значение1, значение2... — это от 1 до 29 аргументов-значений, из которых ВЫБОР, используя номер_индекса, выбирает значение или выполняемое действие. Аргументы могут быть числами, ссылками на ячейки, именами, формулами, функциями или текстами.

· Если номер_индекса является массивом, то каждое значение вычисляется при выполнении функции ВЫБОР.

· Аргументы-значения функции ВЫБОР могут быть как ссылками на интервал, так и отдельными значениями. Например, формула:

СУММ(ВЫБОР(2;A1:A10;B1:B10;C1:C10))

эквивалентна формуле:

СУММ(B1:B10),

которая возвращает значение, вычисленное на основе значений в интервале ячеек B1:B10.

В этом примере сначала вычисляется функция ВЫБОР, которая возвращает ссылку на интервал B1:B10. Затем вычисляется функция СУММ, используя интервал B1:B10, то есть результат функции ВЫБОР, в качестве своего аргумента.

Примеры

ВЫБОР(2;"1-ый";"2-ой";"3-ий";"Последний") равняется "2-ой"

СУММ(A1:ВЫБОР(3;A10;A20;A30)) равняется СУММ(A1:A30)

Если ячейка A10 содержит 4, то:

ВЫБОР(A10;"Гвозди";"Винты";"Гайки";"Болты") равняется "Болты"

Если A10-3 равняется 3, то:

ВЫБОР(A10-3;"1-ый";"2-ой";"3-ий";"Последний") равняется "3-ий"

Если ПрошлыеПродажи - это имя, ссылающееся на значение 10 000, то:

ВЫБОР(2;НовыеПродажи;ПрошлыеПродажи;БюджетПродаж) равняется 10 000

Пример:

Расчет комиссионных менеджера по продажам. Исходные данные для расчета комиссионных приведены в таблице:

Рассмотрим два способа расчета комиссионных:

1. С использованием функции ВПР

Ÿ Для определения величины комиссионных (%) в столбце G5:G14 воспользуемся формулой:

Ÿ В ячейке G5 è =ВПР(F5;$A$4:$B$8;2);

Ÿ Скопируем эту формулу в ячейки G6:G14;

Ÿ Для определения суммы комиссионных воспользуемся формулой:

Ÿ В ячейке H5 è =F5*G5/100;

Ÿ Скопируем эту формулу в ячейки H6:H14;

Ÿ Для определения суммы комиссионных с учетом стажа воспользуемся формулой:

Ÿ В ячейке I5 è =H5+H5*E5/100

Ÿ Скопируем эту формулу в ячейки I6:I14;

2. С использованием функции ЕСЛИ:

В ячейку N5 внесем формулу:

Ÿ =ЕСЛИ(И(M5>=0;M5<=9999,99);M5*0,08;ЕСЛИ(M5<=19999,99;M5*0,105;

Ÿ ЕСЛИ(M5<=39999,99;M5*0,12;M5*0,14)));

Ÿ Скопируем эту формулу в ячейки N6:N14;

Ÿ В ячейку O5 внесем формулу:

§ =N5+N5*L5/100;

Ÿ Скопируем эту формулу в ячейки O6:O14;