Мои Конспекты
Главная | Обратная связь


Автомобили
Астрономия
Биология
География
Дом и сад
Другие языки
Другое
Информатика
История
Культура
Литература
Логика
Математика
Медицина
Металлургия
Механика
Образование
Охрана труда
Педагогика
Политика
Право
Психология
Религия
Риторика
Социология
Спорт
Строительство
Технология
Туризм
Физика
Философия
Финансы
Химия
Черчение
Экология
Экономика
Электроника

Функции выбора и поиска



ВЫБОР(<номер>;<значение1>;<значение2>; ...)

– возвращает значение из списка значений с заданным <номером> от 1 до 29.

Примеры:

ВЫБОР(2;"Апрель";"Май";"Июнь") ="Май",

ВЫБОР(5;1;4;7;2)=#ЗНАЧ! – ошибка, поскольку искомый номер 5 больше числа элементов в списке (4).

ПОИСКПОЗ(<искомое значение>;

<область поиска>[;<тип поиска>])

– осуществляет поиск позиции (клетки) в <области поиска>, значение данных в которой близко искомому значению. Результатом является не само найденное значение (оно нам известно), а его номер в <области поиска>. Искомое значение и область могут быть любого возможного типа.

Понятие близости определяется значением параметра <тип поиска>. Он может иметь одно из трех значений:

0 – соответствие должно быть точным (полное совпадение).

1 – ищется наибольшее значение, которое меньше или равно (если есть) искомому. Область поиска должна быть упорядочена по возрастанию. Если параметр не указан, он считается равным 1.

-1 – ищется наименьшее значение, которое больше или равно искомому. Область поиска должна быть упорядочена по убыванию.

Примеры.

Положим, анализируемая таблица имеет наполнение, изображенное на рис.1. Тогда следующие функции выдадут результаты:

ПОИСКПОЗ(7;В2:F2;0)=4 клетка Е2 (четвертая в области),

ПОИСКПОЗ(10;В2:F2;0)=#Н/Д не найдено,

ПОИСКПОЗ(12;В3:F3; 1 )=2 клетка С3 (вторая в области),

ПОИСКПОЗ(20;В4:F4;-1)=3 клетка D4 (третья в области)

  А В С D Е F
           
 
 
 

Рис. 1

Найденные клетки выделены полужирным шрифтом.

ГПР(<искомое значение>;<область поиска>;

<номер строки извлечения> [;<тип поиска:»])

– осуществляет поиск <искомого значения> в самой верхней строке <области поиска>. Эту строку будем называть ключевой строкой. Результат извлекается из строки с Заданным <номером> относительно ключевой строки <области поиска>. Номер ключевой строки блока поиска – всегда 1 (этот номер не имеет никакого отношения к фактическим номерам строк в рабочих листах книги). Так что, в несколько схематичной форме функция может быть описана следующим образом

ГПР(<что искать>;<где искать>;<откуда взять>;<как искать>).

Параметр <тип поиска> имеет значение ИСТИНА или ЛОЖЬ. Если ЛОЖЬ, поиск в первой строке будет точным, если ИСТИНА (или параметр не задан) – приблизительным (интервальным). В этом случае найденным считается наибольшее значение, меньшее или равное искомому. Ключевая строка тогда должна быть отсортирована по возрастанию. Если этого не сделать, результаты будут непредсказуемы. Напомним, что логические значения ИСТИНА и ЛОЖЬ, внутри формул могут быть заменены на 1 и 0, соответственно.

Примеры (исходные данные берутся из рис.1).

ГПР(7;В2:Р4;3;ЛОЖЬ)=12

Здесь в верхней строке области B2:F4 ищется значение, в точности равное 7. Если поиск успешен, возвращается значение клетки в строке номер три, отсчитывая от верхней строки области. Иными словами

ГПР(<искомое значение 7>;<область поиска B2:F4>;

<строка, из которой извлекается результат 3>;

<поиск должен быть точным ЛОЖЬ>).

В нашем случае результатом явится значение клетки Е4, содержащей число 12. Совершенно идентичные результаты будут получены формулой ГПР(7;B2:F4;3;0) поскольку 0 заменяет значение ЛОЖЬ.

Функции вида ГПР(7;В2:F4;3;1) и ГПР(7;В2:F4;3) скорее всего вызовут сообщение об ошибке (#Н/Д), поскольку интервальный поиск при неупорядоченной ключевой строке непредсказуем. Хотя в некоторых случаях результат может оказаться верным, рассчитывать на это не стоит.

ГПР(25;ВЗ:Р4;2;ИСТИНА)=45

В верхней строке области B3:F4 ищется значение, близкое к 25. Поскольку точно этого значения в строке B3:F3 нет, поиск завершается при нахождении числа 12 в клетке СЗ, так как следующее значение было (30) бы уже больше искомого (25). Функция возвращает значение клетки в строке номер два, отсчитывая от верхней. В нашем случае это клетка С4. Аналогичный результат будет получен с помощью формулы ГПP(25;B3:F4;2;1), поскольку 1 заменяет значение ИСТИНА.

ВПР(<искомое значение>; <область поиска>;

<номер столбца извлечения> [;<тип поиска>])

– осуществляет поиск <искомого значения> в самом левом ключевом столбце <области поиска>.

Результат извлекается из столбца с заданным <номером> относительно ключевого столбца <области поиска>. Функция является полным вертикальным аналогом функции ГПР().

A B C
  Ключевой столбец Столбец извлечения
  А
  Б
  В
  Г
  Е

Рис. 2

 

Примеры (данные взяты из рис. 2):

ВПР(6;В2:С6;2;0)=ВПР(6;В2:С6;2;1)= В – цифра 6 найдена в ключевом столбце, результат извлечен из второго (относительно ключевого) параллельного столбца С.

ВПР(7;В2:С6;2;1)=В – цифра 7 не найдена, но поскольку поиск интервальный, подходящим считается число 6 и результат (В) берется из столбца С.

ВПР(15;В2:С6;2;1)=Е – число 15 не найдено. Берется ближайшее меньшее 11. Результат поиска – буква Е.

ВПР(1 ;В2:С6;2;0)= #Н/Д,

ВПР(7;В2:С6;2;0)=#Н/Д,

ВПР(15;В2:С6;2;0)=#Н/Д,

ВПР(1;В2:С6;2;1)=#Н/Д.

Точный поиск чисел 1, 7 и 15 оказался неудачным (в ключевой строке нет этих чисел), что и повлекло сообщение об ошибке (#Н/Д – "Нет данных”)

Замечание:

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

Пример.

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

Пусть таблица (рис. 3) содержит данные о рабочих днях сотрудников. Нам нужно создать средство быстрого выделения искомых рабочих дней среди перечня рабочих смен, отработанных сотрудниками учреждения. Искомые даты (до трех) вводятся оператором в область C1:E1.

 

  A B C D E
Искомые даты 6.окт 10.окт 12.окт
         
Ф.И.О. Рабочие смены
Петр 10.окт 11.окт 12.окт 13.окт
Иван 6.окт 9.окт 10.окт 11.окт
Олег 9.окт 11.окт 12.окт 13.окт

Рис. 3

Установим для клетки В4 условное форматирование вида:

Условие 1 ® значение ® равно ® =ГПР(В4;$С$1:$Е$1;1;0)

формат ® шрифт ® полужирный курсив

и распространим его на все клетки основной части таблицы. Результат вы видите на рисунке. Теперь мы легко определим, кто именно работал в указанные дни (эти даты выделены).

Пример.

Рассмотрим (рис.4) некоторый содержательный пример, связанный с использованием функции ГПР().

Пусть по разряду рабочего (клетка В8) требуется в тарифной сетке найти соответствующий ему суточный тариф (D5) оплаты труда для дальнейшего начисления зарплаты по итогам месяца. Затем тариф умножается на число рабочих дней (сейчас не рассматривается). Здесь в клетке С8 должен быть получен тариф Петра по его разряду (т.е. по третьему разряду 42 руб.)

<тариф Петра>=ГПР(<разряд Петра>;

<тарифная сетка>;<строка Тариф>;<поиск точный>)

или в виде функции

С8=ГПР(В8;В$4:Е$5;2;0),

где значение В8 ищется в верхней строке области В4:Е5. Если поиск оказался успешным, результат извлекается из второй строки области поиска и заносится в С8.

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

С8=ГПР(В8;{1 ;2;3;4:30;35;42;50};2;0).

A B C D E
Тарифная сетка
Разряд
Тариф
         
ФИО Разряд Тариф    
Петр    

 

Здесь не требуется иметь таблицу тарифов, но зато при их изменении придется редактировать сами формулы.

Замечание.

Если <объект поиска> целое число, поиск точный, а строка извлечения не слишком велика (до 29 элементов), то для тех же целей можно воспользоваться функцией ВЫБОР(). Так (см. выше), вместо функции С8=ГПР(В8;В$4:Е$5;2;0) можно применить функцию С8=ВЫБОР(В8;В$5;С$5;D$5;Е$5).

Пример. Задача с использованием функции ВПР() (рис. 5).

Положим нужно обслужить фирмы-заказчики, предварительно внесшие некоторые денежные взносы (область А2:В5). Таблица должна быть устроена таким образом, чтобы оператор, вводя название фирмы (например в А8) должен сразу (в В8) видеть эту сумму, с тем, чтобы не допустить отпуск товара свыше нее. В списке фирм заказчик может и отсутствовать. В этом случае в столбцеВзнос должен быть выведен 0. Для розыска значения взноса достаточно формулы вида

В8= ВПР(А8;А$2:В$5;2;0).

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

<взнос фирмы>=если(<фирма> не найдена, то 0р.,иначе <ее взнос>).


  А В С D
Фирма Взнос  
Весна
Сервис
Наташа
Восток
 
Заказчик Взнос Оплата Остаток
Наташа
Запад    

Рис. 5

Или в виде формулы

В8=ЕСЛИ(ЕНД(ВПР(А8;А$2:В$5;2;0));0;ВПР(А8;А$2:В$5;2;0)).

Здесь использована функция ЕНД() распознавания соответствующей ошибки. ТогдаОстаток”

D8=B8-C8.

Замечание.

В некоторых случаях значение четвертого аргумента (тип поиска) функций ВПР() / ГПР() кажется безразличным. В предыдущем примере функция =ГПР(В8;В$4:Е$5;2;0) даст тот же результат, что и =ГПР(В8;В$4:Е$5;2;1), поскольку строка разрядов упорядочена по возрастанию. В случае, если предполагается точный поиск, следует все-таки всегда указывать параметр 0. Это позволит пользователю вовремя заметить допущенную им при заполнении таблицы ошибку. Например, если он для некоторого работника ввел значение несуществующего пятого разряда (рис. 4), Excel выдаст сообщение об ошибке. В том же самом случае при параметретип поиска=1, система предложит считать результатом поиска тариф ближайшего четвертого разряда. Конечно, такую ошибку довольно сложно обнаружить.

 

 


[1] Предикатом (или логическим высказыванием) называется выражение, состоящее из логических элементов, значением которого является «истина» или «ложь».

[2] & (амперсенд) — символ операции склейки (конкатенации) строк.

[3] Прочитайте описание этой функции в Справке.