Теоретические сведения о функциях ссылок и массивов

«Поиск позиции» - функция ПОИСКПОЗ.

Функция ПОИСКПОЗ выполняет поиск указанного элемента в диапазоне ячеек и возвращает его относительную позицию.

Функцией ПОИСКПОЗ следует пользоваться, когда требуется найти позицию элемента в диапазоне, а не сам элемент. Например, функцию ПОИСКПОЗ можно использовать для передачи значения аргумента «номер_строки» функции ИНДЕКС.

Синтаксис функции:

ПОИСКПОЗ(искомое_значение, просматриваемый_массив, [тип_сопоставления])

Аргумент «искомое_значение» - обязательный. Это значение, которое сопоставляется со значениями в аргументе «просматриваемый_массив», может быть значением (числом, текстом или логическим значением) или ссылкой на ячейку, содержащую такое значение.

Аргумент «просматриваемый_массив» - обязательный. Это диапазон ячеек, в которых производится поиск.

Тип_сопоставления - необязательный аргумент. Число, равное -1, 0 или 1. Этот аргумент указывает, каким образом нужно искать значение в просматриваемом массиве, если:

· 1 - функция ПОИСКПОЗ находит ближайшее значение, которое меньше или равно значению искомого аргумента. Просматриваемый массив должен быть упорядочен по возрастанию.

· 0 - функция ПОИСКПОЗ находит первое значение, совпадающее с искомым. Просматриваемый массив может быть не упорядочен.

· -1 - функция ПОИСКПОЗ находит ближайшее значение, которое больше или равно значению искомого аргумента. Просматриваемый массив должен быть упорядочен по убыванию

Примечание. Функция ПОИСКПОЗ возвращает не само значение, а его позицию в аргументе «просматриваемый_массив».

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

Если функция ПОИСКПОЗ не находит соответствующего значения, возвращается значение ошибки #Н/Д.

По умолчанию аргумент «тип сопоставления» равен 1.

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

диаметр	[20; 30)	[30; 40)	[40; 50)	[50; 80)	[80; 120)	[120; 150)	[150; 250)	[250; ¥)
фаска		1,2	1,6		2,5

Решение. Сначала рассмотрим эту таблицу. Пусть значение делительного диаметра равно 80, тогда значение фаски равно 2.5, так как левая граница отрезка совпала с заданным числом. Пусть теперь делительный диаметр равен 144, тогда размер фаски должен быть равен 3, так как значение 144 находится между числами 120 и 150. Исходя из этого, для определения функции ПОИСКПОЗ табличные значения диаметров должны быть упорядочены по возрастанию, а третий аргумент этой функции нужно взять равным 1, чтобы номер позиции определялся по ближайшему меньшему числу: для числа 144 – ближайшее меньшее 120. Перейдем к выполнению решения в Excel.

Переименуйте новый лист, например лист 4, в Справочники, создайте два диапазона по рисунку:

Далее для столбцов таблицы «Делительный диаметр» и «Фаска» создадим два именованных диапазона Диаметр и Фаска следующим образом:

1. Выделите диапазон A2:A9.

2. Щелкните поле «Имя» у левого края строки формулы

3. Введите имя Диаметр, которое нужно использовать для ссылки на этот выбор

4. Нажмите клавишу Enter.

5. Выделить диапазон B2:B9.

6. Щелкните поле «Имя» у левого края строки формулы

7. Введите имя Фаска, которое нужно использовать для ссылки на этот выбор.

8. Нажмите клавишу Enter.

9. Вернитесь к листу 1 (с примером 1). Оформите расчет по рисунку:

В результате в диапазоне Диаметр будет определен номер строки, которая содержит ближайшее число, меньшее заданного делительного диаметра d=138, т.е. 120. Число 120 стоит на шестом месте в диапазоне Диаметр, поэтому результатом функции будет число 6.

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

Синтаксис функции:

ИНДЕКС(массив;номер_строки;номер_столбца)

1. Аргумент «массив» - диапазон ячеек или массив констант. Если массив содержит только одну строку или один столбец, аргумент «номер_строки» (или, соответственно, «номер_столбца») не является обязательным.

2. Номер_строки - номер строки в массиве, из которой требуется возвратить значение. Если аргумент «номер_строки» опущен, аргумент «номер_столбца» является обязательным.

3. Номер_столбца - номер столбца в массиве, из которого требуется возвратить значение. Если аргумент «номер_столбца» опущен, аргумент «номер_строки» является обязательным.

Замечания

1. Если используются оба аргумента — и «номер_строки», и «номер_столбца», — то функция ИНДЕКС возвращает значение, находящееся в ячейке на пересечении указанных строки и столбца.

2. Значения аргументов «номер_строки» и «номер_столбца» должны указывать на ячейку внутри заданного массива; в противном случае функция ИНДЕКС возвращает значение ошибки #ССЫЛ!.

Пример 4. Закончим предыдущую задачу о выборе фаски по делительному диаметру. Определение фаски

В результате расчета получили число 3,т. е. значение, которое стоит на 6 месте в диапазоне Фаска. Измените фон ячейки с полученным результатом, чтобы выделить ответ.

Задание 1. На листе Справочники создайте два именованных диапазона Режим и Коэффициент:

На листе 1 создайте диапазон по рисунку:

1. Выполните определение номера позиции в диапазоне Коэффициент по заданному значению, например, 0,7 с помощью функции ПОИСПОЗ:

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

Задание 2. Дана таблица определения припуска по габаритному диаметру (ниже). Выполните определение припуска по известному значению габаритного диаметра и рассчитайте диаметр заготовки по формуле: Dz=Dgab+Prip

Диаметр габаритный	припуск
(0, 18]	3,2
(18, 30]	3,6
(30, 50]	4,6
(50, 80]	5,2
(80, 120]	5,8
(120, 180]	6,8
(180, 250]	7,4
(250, 320]	8,2
(320, 400]	8,8
(400, 500]	9,8
(500, 630]	10,6
(630, 800]	12,4
(800, 1000]	14,0
(1000, 1250]	15,8
(1250, 1600]	18,2
(1600, 2000]	21,0
(2000, 2500]	24,4
(2500, 3150]	28,8
(3150,)	36,0

Рекомендации к решению. В Excel заполните диапазон исходных данных на листе Справочники и ячейки с результатом на листе 1 по рисунку. Дайте имена диапазонам Диаметр и Припуск. Выделите ячейку с результатом расчета

Обратите внимание, что на рисунке расположение значений диаметров – от максимального к минимальному (почему?). Учтите этот факт в использовании функции ПОИСКПОЗ.