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

В диапазоне 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 – Окончательный вариант решения задачи

Задание критериев для извлечения данных из массива значений

Теперь усложним задачу. Найдем название дистрибьютора, склад которого расположен на минимальном расстоянии от нашей фирмы.

Для решения данной задачи мы должны сначала найти минимальное расстояние, затем порядковый номер найденного минимального значения в диапазоне, а по порядковому номеру – название дистрибьютора, склад которого расположен на минимальном расстоянии.

В данной задаче аргументом функции ПОИСКПОЗ Искомое_значение будет результат работы функции МИН.

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

Рисунок 28 – Редактирование части формулы

Чтобы вставить в аргумент Искомое_значение вместо 200 функцию МИН вызовите ее в списке функций, как показано на рисунке 29.

Рисунок 29 – Выбор вложенной функции МИН

В качестве аргументов функции МИН задайте диапазон D2:D23.

Рисунок 30 – Аргументы функции МИН

Как видно из рисунка минимальное расстояние равно 60 км, а название компании ОАО Литисия. Нажмите кнопку ОК.

Итоговая функция имеет вид

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

Еще усложним задачу. Найдем минимальное расстояние до склада компании, расположенной не в Ростовской области.

Теперь мы должны вложить в функцию МИН функцию ЕСЛИ, благодаря которой при вычислении минимального расстояния можно проигнорировать компании из заданного региона (Ростовской области).

В строке формул щелкните по имени функции, в которую нужно внести изменения (в нашем примере МИН) и щелкните по кнопке (Вставка функции)

Рисунок 31 – Выбор вложенной функции для редактирования

Чтобы вставить в аргумент Число1 функцию ЕСЛИ вызовите ее в списке функций, как показано на рисунке 32.

Рисунок 32 – Выбор вложенной функции ЕСЛИ

В качестве аргументов функции ЕСЛИ задайте следующее:

Лог_выражение – C2:C23<>"Ростовская область". Знак <> означает «не равно», кавычки вводить обязательно. Если ввести текстовый критерий отбора (в нашем примере – Ростовская область) без кавычек, Excel будет интерпретировать его как имя ячейки.

Значение_если_истина – D2:D23.

Не забывайте о необходимости ввода данной строки как формулы массива для области C2:C23. Это означает, что завершать ввод данной функции нужно при помощи одновременного нажатия трех клавиш {Ctrl}+{Shift}+{Enter}.

Рисунок 33 – Аргументы функции ЕСЛИ

Как видно из рисунка минимальное расстояние компании, расположенной за пределами Ростовской области у ЗАО ЛТД

Итоговая функция имеет вид

{=ИНДЕКС(A2:D23;ПОИСКПОЗ(МИН(ЕСЛИ(C2:C23<>"Ростовская область";D2:D23)); D2:D23;0);2)}

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

Обычно диаграмма строится на основе данных, содержащихся в ячейках рабочего листа. Диаграмма может быть построена на отдельном специальном листе Excel – листе диаграмм, или непосредственно на рабочем листе, содержащем исходные данные (такая диаграмма называется встроенной).

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