Пошаговая инструкция функции просмотр в Excel скачать примеры

Функция ПРОСМОТР применяется, когда необходимо из одного столбца/строки найти соответствующее значение в той же позиции (то есть, напротив значения) из другого столбца/строки.

Практическая работа функции ПРОСМОТР в Excel

Как работает функция ПРОСМОТР? Синтаксис функции выглядит следующим образом:

Первый аргумент «Искомое_значение» может быть любого типа в зависимости от данных таблицы и нужного результата. Аргумент «Вектор_результатов» является необязательным, он должен иметь размер, одинаковый с аргументом «Просматриваемый_вектор». Второй вариант синтаксиса используется не так часто и предназначен для совместимости з другими программами. Лучше всего вместо массива использовать функцию ВПР. На первом примере рассмотрим принцип работы ПРОСМОТР и все нюансы выполнения задачи. У нас есть таблица с данными о граничном значении частоты звука и его характеристике. Для начала выполним простейшее задание – нам нужно найти характеристику для частоты 100 Гц. Указываем число «100», затем выбираем диапазон А3:А6, в котором находится искомое значение. Последним этапом указываем В3:В6, из которого возвратим характеристику:

В результате получили характеристику «Низкие частоты», поскольку именно это находится на одной позиции с числом «100» и совпадение произошло с одним из значений. Теперь проверим как работает формула, когда ищет значение, которое не имеет совпадений из данных. Например, укажем искомое число 2000 среди диапазона:

Результат мы все же получили. Формула сработала следующим образом – она нашла характеристику напротив ближайшего к числу «2000» значению в сторону уменьшения. То есть, «2000» попадает в диапазон «1000-4000», но «1000» меньше за «4000», поэтому возвратилось значение, соответствующее цифре «1000». Следующим шагом выберем число, больше за максимальное в столбце «Граница частоты, Гц», например, «5000»:

В ячейке В12 возвратилось «Средние частоты». Логика выполнения очень похожа на предыдущий пример – формула устремилась к меньшему ближайшему числу и выдала соответствующую характеристику. И последний пример – укажем число, которое будем искать, меньшее за минимальное, например, «10»:

Теперь у нас появилась ошибка, поскольку среди данных нет числа, меньшего за «10», формуле не на что опираться, и она возвратила ошибку.



Как использовать функцию ПРОСМОТР для разных задач

В следующем примере у нас будет таблица с продажами фирм в разные периоды. И будут также недели, в которые не было продаж, например, фирма Roche не имела продаж на 6 и 7 неделях:

Наша задача состоит в том, чтобы извлечь те данные, которые были последними по каждой фирме. В случае, если бы таблица была полностью заполнена, информация по свежим продажам всегда была бы в столбце «7 нед». В этом примере работа фирм отличается. Из таблицы можем сделать вывод, что последние продажи фирмы Bayer были на 6 неделе, но фирм много, и для решения этой задачи воспользуемся функцией ПРОСМОТР. Искомое значение должно быть числом, которое будет больше любого имеющегося в таблице. Необязательно вычитывать, какое же число точно будет больше любого из таблицы, просто сразу укажем величину «9999». Затем для аргумента «Просматриваемый вектор» укажем диапазон «В19:Н19»:

Мы получили желаемое значение – информацию о самых свежих продажах, на 5 неделе были продажи объемом 76 условных единиц. Принцип работы формулы следующий: ПРОСМОТР просматривает содержимое каждой ячейки в диапазоне В19:Н19, сравнивая их с искомым значением. Если бы число из таблицы совпало с искомым, функция возвратила бы это число. Поскольку значение каждой следующей ячейки не совпадало идентично, ПРОСМОТР переходила от одной к следующей ячейке и возвратила значение последней проверяемой ячейки. Скопируем формулу до конца столбца и проверим, выполнили ли мы задание:

Как видим, по каждой фирме возвратились данные по последним продажам. Пусть вместо числовых значений у нас будут буквенные:

Принцип работы похожий, только теперь вместо очень большого числа нам нужно указать очень много букв – текст «яяяя». Дело в том, что «большой» текст – это код, который состоит из букв в следующем возрастании:

Заменяем искомый текст, оставляем тот же диапазон и смотрим результат:

ПРОСМОТР так же отлично сработал на поиск последней заполненной ячейке по строке. Копируем формулу до конца столбца, чтобы выполнить задачу:

В следующем примере сделаем таблицу с выпадающим списком. У нас есть информация о фирмах. Нам нужно создать таблицу, в который мы выберем из списка нужную нам фирму и все данные по ней автоматически подтянутся:

Первым этапом будет создание выпадающего списка из десяти фирм. Для начала убедитесь, что вы находитесь на нужной вам ячейке (клик по ячейке К32»). Переходим на вкладку «Данные», в группе «Работа с данными» выбираем «Проверка вводимых значений». Появится окно, в котором нужно среди элементов «Типа данных» выбрать список, а в «Источнике» указать диапазон. В нашем случае диапазоном будет список десяти фирм К19:К28:

Сейчас возле ячейки К32 должен появиться указатель выпадающего списка, благодаря которому можно выбрать любую из десяти фирм:

Теперь нам нужно организовать автоматическое подтягивание всех данных по выбранной фирме. В ячейке L32 пишем формулу ПРОСМОТР. В качестве искомого значения у нас будет ячейка К32, поскольку она будет меняться, и в зависимости от этих изменений будет меняться и совпадения «Фирма N = Название». Вторым аргументом будет диапазон К19:L28, в котором формула будет просматривать совпадения:

Сейчас видим результат Bayer, поскольку первая таблица содержит информацию, что Фирма 5 – это фирма с названием Bayer. Дальше так же мы строим формулы для прибыли, кода, и страны-партнера:

В формулах, где мы возвратим данные по прибыли, коду и стране-партнера второй аргумент разделится на два – просматриваемый вектор и вектор результатов. Первый будет неизменный – диапазон К19:К28, а второй – будет меняться в зависимости от столбца, из которого нужно возвратить данные. Как видим, по фирме 5 все получилось правильно – все элементы совпали. Теперь выберем любую другую фирму из списка, например, 2:

Сравниваем взглядом все совпадения. Наша таблица исправно выполняет работу. Так же мы можем немного расширить информацию во второй таблице. Пусть нам нужно сверять данные по двум фирмам одновременно. Для этого нам нужно, чтобы сразу две фирмы можно было выбирать и читать их характеристики. Сначала выполняем копирование ячейки К32 на один пункт вниз. Возле ячейки К33 так же должен быть указатель выпадающего списка. Затем копируем так же ячейки L32, M32, N32, O32. Однако, перед копированием нужно указать абсолютные ссылки для диапазонов, иначе формула съедет на одну позицию вниз и у нас будут ошибки:

Теперь совершаем копирование и у нас есть два ряда с выпадающими списками фирм и информацией о них. Выберем фирму 4 и фирму 7:

Скачать пошаговые примеры использования функции ПРОСМОТР в Excel

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