Допустим ваш отчет содержит таблицу с большим количеством данных на множество столбцов. Проводить визуальный анализ таких таблиц крайне сложно. А одним из заданий по работе с отчетом является – анализ данных относительно заголовков строк и столбцов касающихся определенного месяца. На первый взгляд это весьма простое задание, но его нельзя решить, используя одну стандартную функцию. Да, конечно можно воспользоваться инструментом: «ГЛАВНАЯ»-«Редактирование»-«Найти» CTRL+F, чтобы вызвать окно поиска значений на листе Excel. Или же создать для таблицы правило условного форматирования. Но тогда нельзя будет выполнить дальнейших вычислений с полученными результатами. Поэтому необходимо создать и правильно применить соответствующую формулу.
Поиск значения в массиве Excel
Схема решения задания выглядит примерно таким образом:
- в ячейку B1 мы будем вводить интересующие нас данные;
- в ячейке B2 будет отображается заголовок столбца, который содержит значение ячейки B1
- в ячейке B3 будет отображается название строки, которая содержит значение ячейки B1.
Фактически необходимо выполнить поиск координат в Excel. Для чего это нужно? Достаточно часто нам нужно получить координаты таблицы по значению. Немного напоминает обратный анализ матрицы. Конкретный пример в двух словах выглядит примерно так. Поставленная цель в цифрах является исходным значением, нужно определить кто и когда наиболее приближен к этой цели. Для примера используем простую матрицу данных с отчетом по количеству проданных товаров за три квартала, как показано ниже на рисунке. Важно, чтобы все числовые показатели совпадали. Если нет желания вручную создавать и заполнять таблицу Excel с чистого листа, то в конце статьи можно скачать уже с готовым примером.
Последовательно рассмотрим варианты решения разной сложности, а в конце статьи – финальный результат.
Поиск значения в столбце Excel
Сначала научимся получать заголовки столбцов таблицы по значению. Для этого выполните следующие действия:
- В ячейку B1 введите значение взятое из таблицы 5277 и выделите ее фон синим цветом для читабельности поля ввода (далее будем вводить в ячейку B1 другие числа, чтобы экспериментировать с новыми значениями).
- В ячейку C2 вводим формулу для получения заголовка столбца таблицы который содержит это значение:
- После ввода формулы для подтверждения нажимаем комбинацию горячих клавиш CTRL+SHIFT+Enter, так как формула должна быть выполнена в массиве. Если все сделано правильно в строке формул по краям появятся фигурные скобки < >.
В ячейку C2 формула вернула букву D — соответственный заголовок столбца листа. Как видно все сходиться, значение 5277 содержится в ячейке столбца D. Рекомендуем посмотреть на формулу для получения целого адреса текущей ячейки.
Поиск значения в строке Excel
Теперь получим номер строки для этого же значения (5277). Для этого в ячейку C3 введите следующую формулу:
После ввода формулы для подтверждения снова нажимаем комбинацию клавиш CTRL+SHIFT+Enter и получаем результат:
Формула вернула номер 9 – нашла заголовок строки листа по соответствующему значению таблицы. В результате мы имеем полный адрес значения D9.
Как получить заголовок столбца и название строки таблицы
Теперь научимся получать по значению координаты не целого листа, а текущей таблицы. Одним словом, нам нужно найти по значению 5277 вместо D9 получить заголовки:
- для столбца таблицы – Март;
- для строки – Товар4.
Чтобы решить данную задачу будем использовать формулу с уже полученными значениями в ячейках C2 и C3. Для этого делаем так:
- Для заголовка столбца. В ячейку D2 введите формулу: На этот раз после ввода формулы для подтверждения жмем как по традиции просто Enter:
- Для строки вводим похожую, но все же немного другую формулу:
В результате получены внутренние координаты таблицы по значению – Март; Товар 4:
На первый взгляд все работает хорошо, но что, если таблица будет содержат 2 одинаковых значения? Тогда могут возникнуть проблемы с ошибками! Рекомендуем также посмотреть альтернативное решение для поиска столбцов и строк по значению.
Поиск одинаковых значений в диапазоне Excel
Чтобы проконтролировать наличие дубликатов среди значений таблицы создадим формулу, которая сможет информировать нас о наличии дубликатов и подсчитывать их количество. Для этого в ячейку E2 вводим формулу:
Более того для диапазона табличной части создадим правило условного форматирования:
- Выделите диапазон B6:J12 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Правила выделения ячеек»-«Равно».
- В левом поле введите значение $B$1, а из правого выпадающего списка выберите опцию «Светло-красная заливка и темно-красный цвет» и нажмите ОК.
- В ячейку B1 введите значение 3478 и полюбуйтесь на результат.
Как видно при наличии дубликатов формула для заголовков берет заголовок с первого дубликата по горизонтали (с лева на право). А формула для получения названия (номера) строки берет номер с первого дубликата по вертикали (сверху вниз). Для исправления данного решения есть 2 пути:
- Получить координаты первого дубликата по горизонтали (с лева на право). Для этого только в ячейке С3 следует изменить формулу на: В результате получаем правильные координаты как для листа, так и для таблицы:
- Получить координаты первого дубликата по вертикали (сверху вниз). Для этого только в ячейке С2 следует изменить формулу на:
В данном случаи изменяем формулы либо одну либо другую, но не две сразу. Стоит напомнить о том, что в ячейке С3 должна оставаться старая формула:
Здесь правильно отображаются координаты первого дубликата по вертикали (с верха в низ) – I7 для листа и Август; Товар2 для таблицы. Оставим такой вариант для следующего завершающего примера.
Поиск ближайшего значения в диапазоне Excel
Данная таблица все еще не совершенна. Ведь при анализе нужно точно знать все ее значения. Если введенное число в ячейку B1 формула не находит в таблице, тогда возвращается ошибка – #ЗНАЧ! Идеально было-бы чтобы формула при отсутствии в таблице исходного числа сама подбирала ближайшее значение, которое содержит таблица. Чтобы создать такую программу для анализа таблиц в ячейку F1 введите новую формулу:
После чего следует во всех остальных формулах изменить ссылку вместо B1 должно быть F1! Так же нужно изменить ссылку в условном форматировании. Выберите: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Управление правилами»-«Изменить правило». И здесь в параметрах укажите F1 вместо B1 . Чтобы проверить работу программы, введите в ячейку B1 число которого нет в таблице, например: 8000. Это приведет к завершающему результату:
Теперь можно вводить любое исходное значение, а программа сама подберет ближайшее число, которое содержит таблица. После чего выводит заголовок столбца и название строки для текущего значения. Например, если ввести число 5000 получаем новый результат:
Наша программа в Excel нашла наиболее близкое значение 4965 для исходного – 5000. Такая программа может пригодится для автоматического решения разных аналитических задач при бизнес-планировании, постановки целей, поиска рационального решения и т.п. А полученные строки и столбцы позволяют дальше расширять вычислительные возможности такого рода отчетов с помощью новых формул Excel.
Во второй части нашего учебника по функции ВПР (VLOOKUP) в Excel мы разберём несколько примеров, которые помогут Вам направить всю мощь ВПР
на решение наиболее амбициозных задач Excel. Примеры подразумевают, что Вы уже имеете базовые знания о том, как работает эта функция. Если нет, возможно, Вам будет интересно начать с первой части этого учебника, в которой объясняются синтаксис и основное применение ВПР. Что ж, давайте приступим.
Поиск в Excel по нескольким критериям
Функция ВПР в Excel – это действительно мощный инструмент для выполнения поиска определённого значения в базе данных. Однако, есть существенное ограничение – её синтаксис позволяет искать только одно значение. Как же быть, если требуется выполнить поиск по нескольким условиям? Решение Вы найдёте далее.
Пример 1: Поиск по 2-м разным критериям
Предположим, у нас есть список заказов и мы хотим найти Количество товара (Qty.), основываясь на двух критериях – Имя клиента (Customer) и Название продукта (Product). Дело усложняется тем, что каждый из покупателей заказывал несколько видов товаров, как это видно из таблицы ниже:
Обычная функция ВПР не будет работать по такому сценарию, поскольку она возвратит первое найденное значение, соответствующее заданному искомому значению. Например, если Вы хотите узнать количество товара Sweets, заказанное покупателем Jeremy Hill, запишите вот такую формулу:
Есть простой обходной путь – создать дополнительный столбец, в котором объединить все нужные критерии. В нашем примере это столбцы Имя клиента (Customer) и Название продукта (Product). Не забывайте, что объединенный столбец должен быть всегда крайним левым в диапазоне поиска, поскольку именно левый столбец функция ВПР просматривает при поиске значения.
Итак, Вы добавляете вспомогательный столбец в таблицу и копируете по всем его ячейкам формулу вида: =B2&C2. Если хочется, чтобы строка была более читаемой, можно разделить объединенные значения пробелом: =B2&» «&C2. После этого можно использовать следующую формулу:
=VLOOKUP("Jeremy Hill Sweets",$A$7:$D$18,4,FALSE) =ВПР("Jeremy Hill Sweets";$A$7:$D$18;4;ЛОЖЬ)
Пример 2: ВПР по двум критериям с просматриваемой таблицей на другом листе
Если Вам необходимо обновить основную таблицу (Main table), добавив данные из второй таблицы (Lookup table), которая находится на другом листе или в другой рабочей книге Excel, то Вы можете собрать искомое значение непосредственно в формуле, которую вставляете в основную таблицу.
Как и в предыдущем примере, Вам понадобится в таблице поиска (Lookup table) вспомогательный столбец с объединенными значениями. Этот столбец должен быть крайним левым в заданном для поиска диапазоне.
Итак, формула с ВПР может быть такой:
Чтобы сделать формулу более читаемой, Вы можете задать имя для просматриваемого диапазона, и тогда формула станет выглядеть гораздо проще:
Чтобы формула работала, значения в крайнем левом столбце просматриваемой таблицы должны быть объединены точно так же, как и в критерии поиска. На рисунке выше мы объединили значения и поставили между ними пробел, точно так же необходимо сделать в первом аргументе функции (B2&» «&C2).
Соглашусь, добавление вспомогательного столбца – не самое изящное и не всегда приемлемое решение. Вы можете сделать то же самое без вспомогательного столбца, но в таком случае потребуется гораздо более сложная формула с комбинацией функций INDEX (ИНДЕКС) и MATCH (ПОИСКПОЗ).
Извлекаем 2-е, 3-е и т.д. значения, используя ВПР
Вы уже знаете, что ВПР может возвратить только одно совпадающее значение, точнее – первое найденное. Но как быть, если в просматриваемом массиве это значение повторяется несколько раз, и Вы хотите извлечь 2-е или 3-е из них? А что если все значения? Задачка кажется замысловатой, но решение существует!
Предположим, в одном столбце таблицы записаны имена клиентов (Customer Name), а в другом – товары (Product), которые они купили. Попробуем найти 2-й, 3-й и 4-й товары, купленные заданным клиентом.
Простейший способ – добавить вспомогательный столбец перед столбцом Customer Name и заполнить его именами клиентов с номером повторения каждого имени, например, John Doe1, John Doe2 и т.д. Фокус с нумерацией сделаем при помощи функции COUNTIF (СЧЁТЕСЛИ), учитывая, что имена клиентов находятся в столбце B:
После этого Вы можете использовать обычную функцию ВПР, чтобы найти нужный заказ. Например:
- Находим 2-й товар, заказанный покупателем Dan Brown:
=VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE) =ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ)
Находим 3-й товар, заказанный покупателем Dan Brown:
=VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE) =ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)
На самом деле, Вы можете ввести ссылку на ячейку в качестве искомого значения вместо текста, как представлено на следующем рисунке:
Если Вы ищите только 2-е повторение, то можете сделать это без вспомогательного столбца, создав более сложную формулу:
- $F$2 – ячейка, содержащая имя покупателя (она неизменна, обратите внимание – ссылка абсолютная);
- $B$ – столбец Customer Name;
- Table4 – Ваша таблица (на этом месте также может быть обычный диапазон);
- $C16 – конечная ячейка Вашей таблицы или диапазона.
Эта формула находит только второе совпадающее значение. Если же Вам необходимо извлечь остальные повторения, воспользуйтесь предыдущим решением.
Если Вам нужен список всех совпадений – функция ВПР тут не помощник, поскольку она возвращает только одно значение за раз – и точка. Но в Excel есть функция INDEX (ИНДЕКС), которая с легкостью справится с этой задачей. Как будет выглядеть такая формула, Вы узнаете в следующем примере.
Извлекаем все повторения искомого значения
Как упоминалось выше, ВПР не может извлечь все повторяющиеся значения из просматриваемого диапазона. Чтобы сделать это, Вам потребуется чуть более сложная формула, составленная из нескольких функций Excel, таких как INDEX (ИНДЕКС), SMALL (НАИМЕНЬШИЙ) и ROW (СТРОКА)
Например, формула, представленная ниже, находит все повторения значения из ячейки F2 в диапазоне B2:B16 и возвращает результат из тех же строк в столбце C.
Если Вам интересно понять, как она работает, давайте немного погрузимся в детали формулы:
Часть 1:
Результатом функции IF (ЕСЛИ) окажется вот такой горизонтальный массив:
Часть 2:
Часть 3:
Часть 4:
Часть 5:
Двумерный поиск по известным строке и столбцу
Выполнение двумерного поиска в Excel подразумевает поиск значения по известному номеру строки и столбца. Другими словами, Вы извлекаете значение ячейки на пересечении конкретной строки и столбца.
Итак, давайте обратимся к нашей таблице и запишем формулу с функцией ВПР, которая найдет информацию о стоимости проданных в марте лимонов.
Существует несколько способов выполнить двумерный поиск. Познакомьтесь с возможными вариантами и выберите наиболее подходящий.
Функции ВПР и ПОИСКПОЗ
Вы можете использовать связку из функций ВПР (VLOOKUP) и ПОИСКПОЗ (MATCH), чтобы найти значение на пересечении полей Название продукта (строка) и Месяц (столбец) рассматриваемого массива:
- Ищем символы «Mar» – аргумент lookup_value (искомое_значение);
- Ищем в ячейках от A1 до I1 – аргумент lookup_array (просматриваемый_массив);
- Возвращаем точное совпадение – аргумент match_type (тип_сопоставления).
Использовав 0 в третьем аргументе, Вы говорите функции ПОИСКПОЗ искать первое значение, в точности совпадающее с искомым значением. Это равносильно значению FALSE (ЛОЖЬ) для четвёртого аргумента ВПР.
Вот так Вы можете создать формулу для поиска по двум критериям в Excel, что также известно, как двумерный поиск или поиск в двух направлениях.
Функция СУММПРОИЗВ
Функция СУММПРОИЗВ (SUMPRODUCT) возвращает сумму произведений выбранных массивов:
В следующей статье я буду объяснять эти функции во всех деталях, так что сейчас можете просто скопировать эту формулу:
Если Вы не в восторге от всех этих сложных формул Excel, Вам может понравиться вот такой наглядный и запоминающийся способ:
- Выделите таблицу, откройте вкладку Formulas (Формулы) и нажмите Create from Selection (Создать из выделенного).
- Отметьте галочками Top row (в строке выше) и Left column (в столбце слева). Microsoft Excel назначит имена диапазонам из значений в верхней строке и левом столбце Вашей таблицы. Теперь Вы можете осуществлять поиск, используя эти имена, напрямую, без создания формул.
=Lemons Mar … или наоборот: =Mar Lemons
Помните, что имена строки и столбца нужно разделить пробелом, который в данном случае работает как оператор пересечения.
При вводе имени, Microsoft Excel будет показывать подсказку со списком подходящих имен, так же, как при вводе формулы.
- Нажмите Enter и проверьте результат
В целом, какой бы из представленных выше методов Вы ни выбрали, результат двумерного поиска будет одним и тем же:
Используем несколько ВПР в одной формуле
Бывает так, что основная таблица и таблица поиска не имеют ни одного общего столбца, и это мешает использовать обычную функцию ВПР. Однако, существует ещё одна таблица, которая не содержит интересующую нас информацию, но имеет общий столбец с основной таблицей и таблицей поиска.
Давайте разберем следующий пример. У нас есть основная таблица (Main table) со столбцом SKU (new), куда необходимо добавить столбец с соответствующими ценами из другой таблицы. Кроме этого, у нас есть 2 таблицы поиска. Первая (Lookup table 1) содержит обновленные номера SKU (new) и названия товаров, а вторая (Lookup table 2) – названия товаров и старые номера SKU (old).
Чтобы добавить цены из второй таблицы поиска в основную таблицу, необходимо выполнить действие, известное как двойной ВПР или вложенный ВПР.
- Запишите функцию ВПР, которая находит имя товара в таблице Lookup table 1, используя SKU, как искомое значение:
Здесь New_SKU – именованный диапазон $A:$B в таблице Lookup table 1, а 2 – это столбец B, который содержит названия товаров (смотрите на рисунке выше)
Запишите формулу для вставки цен из таблицы Lookup table 2 на основе известных названий товаров. Для этого вставьте созданную ранее формулу в качестве искомого значения для новой функции ВПР:
Здесь Price – именованный диапазон $A:$C в таблице Lookup table 2, а 3 – это столбец C, содержащий цены.
На рисунке ниже виден результат, возвращаемый созданной нами формулой:
Динамическая подстановка данных из разных таблиц при помощи ВПР и ДВССЫЛ
В начале разъясним, что мы подразумеваем под выражением «Динамическая подстановка данных из разных таблиц», чтобы убедиться правильно ли мы понимает друг друга.
Бывают ситуации, когда есть несколько листов с данными одного формата, и необходимо извлечь нужную информацию с определенного листа в зависимости от значения, которое введено в заданную ячейку. Думаю, проще это объяснить на примере.
Представьте, что имеются отчеты по продажам для нескольких регионов с одинаковыми товарами и в одинаковом формате. Требуется найти показатели продаж для определенного региона:
Если у Вас всего два таких отчета, то можно использовать до безобразия простую формулу с функциями ВПР и ЕСЛИ (IF), чтобы выбрать нужный отчет для поиска:
- $D$2 – это ячейка, содержащая название товара. Обратите внимание, здесь мы используем абсолютные ссылки, чтобы избежать изменения искомого значения при копировании формулы в другие ячейки.
- $D3 – это ячейка с названием региона. Используем абсолютную ссылку для столбца и относительную ссылку для строки, поскольку планируем копировать формулу в другие ячейки того же столбца.
- FL_Sales и CA_Sales – названия таблиц (или именованных диапазонов), в которых содержаться соответствующие отчеты о продажах. Вы, конечно же, можете использовать обычные названия листов и ссылки на диапазоны ячеек, например ‘FL Sheet’!$A$3:$B$10, но именованные диапазоны гораздо удобнее.
Однако, когда таких таблиц много, функция ЕСЛИ – это не лучшее решение. Вместо нее можно использовать функцию ДВССЫЛ (INDIRECT), чтобы возвратить нужный диапазон поиска.
Как Вы, вероятно, знаете, функция ДВССЫЛ используется для того, чтобы вернуть ссылку, заданную текстовой строкой, а это как раз то, что нам сейчас нужно. Итак, смело заменяем в представленной выше формуле выражение с функцией ЕСЛИ на ссылку с функцией ДВССЫЛ. Вот такая комбинация ВПР и ДВССЫЛ отлично работает в паре:
- $D$2 – это ячейка с названием товара, она неизменна благодаря абсолютной ссылке.
- $D3 – это ячейка, содержащая первую часть названия региона. В нашем примере это FL.
- _Sales – общая часть названия всех именованных диапазонов или таблиц. Соединенная со значением в ячейке D3, она образует полное имя требуемого диапазона. Ниже приведены некоторые подробности для тех, кто не имеет опыта работы с функцией ДВССЫЛ.
Как работают ДВССЫЛ и ВПР
Во-первых, позвольте напомнить синтаксис функции ДВССЫЛ (INDIRECT):
- A1, если аргумент равен TRUE (ИСТИНА) или не указан;
- R1C1, если FALSE (ЛОЖЬ).
В нашем случае ссылка имеет стиль A1, поэтому можно не указывать второй аргумент и сосредоточиться на первом.
Итак, давайте вернемся к нашим отчетам по продажам. Если Вы помните, то каждый отчёт – это отдельная таблица, расположенная на отдельном листе. Чтобы формула работала верно, Вы должны дать названия своим таблицам (или диапазонам), причем все названия должны иметь общую часть. Например, так: CA_Sales, FL_Sales, TX_Sales и так далее. Как видите, во всех именах присутствует «_Sales».
Функция ДВССЫЛ соединяет значение в столбце D и текстовую строку «_Sales», тем самым сообщая ВПР в какой таблице искать. Если в ячейке D3 находится значение «FL», формула выполнит поиск в таблице FL_Sales, если «CA» – в таблице CA_Sales и так далее.
Результат работы функций ВПР и ДВССЫЛ будет следующий:
Если данные расположены в разных книгах Excel, то необходимо добавить имя книги перед именованным диапазоном, например:
Урок подготовлен для Вас командой сайта office-guru.ru Источник: https://www.ablebits.com/office-addins-blog/2014/07/29/vlookup-formula-examples/ Перевел: Андрей Антонов Правила перепечатки Еще больше уроков по Microsoft Excel
Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).
Предположим, вам нужно найти расширение телефона сотрудника с помощью его номера, а также правильно оценить коэффициент Комиссии для суммы продажи. Вы ищете данные, чтобы быстро и эффективно находить определенные данные в списке и автоматически проверять, правильно ли используются данные. После того как вы просмотрит данные, вы можете выполнить вычисления и отобразить результаты, указав возвращаемые значения. Есть несколько способов поиска значений в списке данных и отображения результатов.
В этой статье
Поиск значений в списке по вертикали по точному совпадению
Для выполнения этой задачи можно использовать функцию ВПР или сочетание функций индекс и ПОИСКПОЗ.
Примеры использования функции ВПР
Дополнительные сведения можно найти в разделе функция ВПР.
Примеры ИНДЕКСов и СОВПАДЕНИй
=ИНДЕКС(нужно вернуть значение из C2:C10, которое будет соответствовать ПОИСКПОЗ(первое значение "Капуста" в массиве B2:B10))
Формула ищет первое значение в ячейке C2: C10, соответствующее капусты (в B7), и возвращает значение в C7 ( 100) — первое значение, соответствующее капусты.
Дополнительные сведения можно найти в разделе Функция индекс и функция ПОИСКПОЗ.
Поиск значений в списке по вертикали по приблизительному совпадению
Для этого используйте функцию ВПР.
Важно: Убедитесь, что значения в первой строке отсортированы в возрастающем порядке.
В приведенном выше примере функция ВПР ищет имя учащегося, у которого есть 6 тардиес в диапазоне A2: B7. В таблице нет записи для 6 тардиес, поэтому функция ВПР ищет следующее самое высокое соответствие ниже 6 и находит значение 5, связанное с первым именем Дэйв, и, следовательно, возвращает Дэйв.
Дополнительные сведения можно найти в разделе функция ВПР.
Поиск значений по вертикали в списке неизвестного размера с точным соответствием
Для выполнения этой задачи используйте функции СМЕЩ и ПОИСКПОЗ.
Примечание: Этот подход используется, если данные находятся в диапазоне внешних данных, который вы обновляете каждый день. Вы знаете, что в столбце B есть Цена, но вы не знаете, сколько строк данных возвращает сервер, а первый столбец не отсортирован по алфавиту.
C1 — это верхняя левая ячейка диапазона (также называемая начальной ячейкой).
Match ("апельсины"; C2: C7; 0) ищет оранжевый цвет в диапазоне C2: C7. Не следует включать начальную ячейку в диапазон.
1 — количество столбцов справа от начальной ячейки, для которых должно быть возвращено возвращаемое значение. В нашем примере возвращаемое значение находится в столбце D, Sales.
Поиск значений в списке по горизонтали по точному совпадению
Для выполнения этой задачи используется функция ГПР. Ниже приведен пример.
Функция ГПР выполняет поиск по столбцу Sales и возвращает значение из строки 5 в указанном диапазоне.
Дополнительные сведения можно найти в разделе функции ГПР.
Поиск значений в списке по горизонтали с использованием приблизительного совпадения
Для выполнения этой задачи используется функция ГПР.
Важно: Убедитесь, что значения в первой строке отсортированы в возрастающем порядке.
В приведенном выше примере функция ГПР ищет значение 11000 в строке 3 в указанном диапазоне. Он не находит 11000 и, следовательно, ищет следующее наибольшее значение, которое меньше 1100 и возвращает число 10543.
Дополнительные сведения можно найти в разделе функции ГПР.
Создание формулы подстановки с помощью мастера подстановок (толькоExcel 2007 )
Примечание: Надстройка "Мастер подстановок" прекращена в Excel 2010. Эти функциональные возможности заменены мастером функций и доступными функциями поиска и работы со ссылками (ссылками).
В Excel 2007 мастер подстановок создает формулу подстановки на основе данных листа, имеющих заголовки строк и столбцов. Мастер подстановок помогает находить другие значения в строке, когда вы знаете значение в одном столбце, и наоборот. Мастер подстановок использует индекс и СОВПАДЕНИе в создаваемых формулах.
Щелкните ячейку в диапазоне.
На вкладке формулы в группе решения нажмите кнопку Подстановка .
Если команда подстановка недоступна, необходимо загрузить мастер подстановок надстройка программу.
Загрузка программы-надстройки "Мастер подстановок"
Нажмите кнопку Microsoft Office 
, щелкните Параметры Excelи выберите категорию надстройки.
В поле Управление выберите элемент Надстройки Excel и нажмите кнопку Перейти.
В диалоговом окне надстройки установите флажок Мастер подстановоки нажмите кнопку ОК.
