Извлечение данных из массива значений

В диапазоне A1:D23 рабочего листа Excel, показанном на рисунке 19, приведены сведения о дистрибьюторах нашей фирмы, регионах (в которых они расположены) и расстоянии до складов, на которые наша фирма доставляет товары.

Решим задачу по определению расстояния до склада компании, название которой задается в отдельной ячейки, например, до склада компании ЗАО Титан.

Нам необходимо решить задачу поиска значения элемента в заданном массиве, пользуясь функциями из категории Ссылки и массивы. Прежде чем привести окончательный вариант решения задачи рассмотрим возможности работы функции данной категории. Начнем с функции ИНДЕКС, которая имеет две формы. Мы изучим только одну:

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

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

Рисунок 19 – Исходный массив значений

Введите в ячейку Е1 название компании, в нашем примере ЗАО Титан. Затем установите курсор в ячейку Е2, нажмите кнопку Вставка функции и в появившемся окне Мастер функций в поле со списком Категории выберите Ссылки и массивы. В поле Выберите функцию выделите функцию ИДЕКС и нажмите ОК.

На экране появится диалоговое окно Мастер функций - шаг 1а из 2, в котором необходимо выбрать первый тип функции.

Рисунок 20– Выбор списка аргументов функции ИНДЕКС

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

Рисунок 21 – Аргументы функции ИНДЕКС

Массив в нашем примере будет диапазон ячеек A2:D23.

Мы можем задавать номер строки явно – 20, поскольку в учебных целях был взят массив с небольшим количеством строк. Итак, введите в поле Номер_строки число 20.

Километраж, показывающий расстояние до складов фирм, содержится в четвертом столбце. Поэтому в поле Номер_столбца вводим 4.

Задавая в качестве аргументов функции ИНДЕКС явно номер_строки, в которой расположена информация о компании ЗАО Титан (20 строка) и номер_столбца (4 столбец) мы получаем искомое расстояние 160 км.

В данном примере мы могли бы найти расстояние не прибегая к возможностям функций Excel, но в практической деятельности экономиста-менеджера зачастую приходится сталкиваться со значительными объемами данных, которые трудно «обозреть одним взглядом» и найти нужную информацию – в этом случае приходится затрачивать значительное время.

Для облегчения поиска позиции элемента в списке существует функция ПОИСКПОЗ. При помощи этой функции мы найдем позицию в массиве данных искомого значения – ЗАО Титан.

Введите в ячейку Е3 функцию ПОИСКПОЗ, которая находится в категории Ссылки и массивы окна Мастер функций.

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

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

Искомое_значение – значение, используемое при поиске значения в таблице. В нашем примере Искомое_значение ЗАО Титан.

Просматриваемый_массив – непрерывный интервал ячеек, возможно, содержащих искомые значения. В нашем примере просматриваемый_массив диапазон В2:В23.

Тип_сопоставления – число -1, 0 или 1. Тип_сопоставления указывает, как Microsoft Excel сопоставляет искомое_значение со значениями в аргументе просматриваемый_массив

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

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

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

· Если тип_сопоставления опущен, то предполагается, что он равен 1.

В нашем примере список неупорядочен, поэтому в поле тип_сопоставления введите 0

Рисунок 22 – Аргументы функции ПОИСКПОЗ

Как видно из рисунка 22 функция ПОИСКПОЗ нашла позицию элемента ЗАО Титан в массиве данных (20 позиция).

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

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

Сначала в окне Мастер функций в поле со списком Категории выберите Ссылки и массивы. В поле Выберите функцию выделите функцию ИДЕКС и нажмите ОК.

На экране появится диалоговое окно Мастер функций - шаг 1а из 2, в котором необходимо выбрать первый тип функции.

Рисунок 23 – Выбор списка аргументов функции ИНДЕКС

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

Рисунок 24 – Выбор вложенной функции ПОИСКПОЗ

В поле Массив введите диапазон ячеек A2:D23, в котором содержатся исходные данные.

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

Итак, Вы щелкнули по кнопке со стрелкой вниз и в появившемся списке выполнили щелчок по функции ПОИСКПОЗ. На экране появилось диалоговое окно ПОИСКПОЗ. Введите аргументы функции: Искомое_значение – ЗАО Титан; просматриваемый_массив – В2:В23; тип_сопоставления – 0. Не нажимайте кнопку ОК после ввода аргументов функции ПОИСКПОЗ.

Рисунок 25 – Ввод аргументов во вложенную функцию

Обратите внимание на строку формул. В ней содержится функция ИНДЕКС, в которую вложена функция ПОИСКПОЗ. Обратите внимание на то, что в функции ИНДЕКС введены не все аргументы – не указан номер столбца.

Для того, чтобы вернуться в функцию ИНДЕКС просто щелкните по ее имени в строке формул

Рисунок 26 – Окончательный вариант решения задачи

Введите аргумент Номер_столбца – 4 и только сейчас нажмите кнопку ОК.

Как вы можете видеть из рисунка ответ – это вложенные функции, которые позволяет найти расстояние до склада компании ЗАО Титан выглядит следующим образом:

=ИНДЕКС(A2:D23;ПОИСКПОЗ("ЗАО Титан";B2:B23;0);4)

Закрепим полученные знания на обратном примере. Например, нам необходимо найти название компании, расположенной на расстоянии 200 км. Функция выглядит следующим образом

=ИНДЕКС(A2:D23;ПОИСКПОЗ(200;D2:D23;0);2)

Диалоговое окно показано на рисунке 27.

Рисунок 27 – Окончательный вариант решения задачи