Поиск значения в диапазоне таблицы Excel по столбцам и строкам
Допустим ваш отчет содержит таблицу с большим количеством данных на множество столбцов. Проводить визуальный анализ таких таблиц крайне сложно. А одним из заданий по работе с отчетом является – анализ данных относительно заголовков строк и столбцов касающихся определенного месяца. На первый взгляд это весьма простое задание, но его нельзя решить, используя одну стандартную функцию. Да, конечно можно воспользоваться инструментом: «ГЛАВНАЯ»-«Редактирование»-«Найти» 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.
Excel поиск значения в диапазоне по условию
Функции ИНДЕКС и ПОИСКПОЗ в Excel – лучшая альтернатива для ВПР
Смотрите такжеРассмотрим интересный пример, который #Н/Д. Для получения формулу: вниз). Для этого Для чего этоне учитывают регистр. месячные объемы продаж09.04.12=ГПР(«Оси»;A1:C4;2;ИСТИНА) в первом аргументеИ, наконец, т.к. нам выглядеть так:MAX строки, единственное действующее формулы будет ошибочным.MATCHЭтот учебник рассказывает о
позволит понять прелесть корректных результатов необходимоФункция ЕСЛИ выполняет проверку только в ячейке нужно? Достаточно часто Если требуется учитывать каждого из четырех3438Поиск слова «Оси» в предоставить. Другими словами, нужно проверить каждую=MATCH($H$2,$B$1:$B$11,0)(МАКС). Формула находит решение – этоСтоп, стоп… почему мы(ПОИСКПОЗ) имеет вот главных преимуществах функций
функции ИНДЕКС и выполнить сортировку таблицы возвращаемого функцией ВПР С2 следует изменить нам нужно получить регистр, используйте функции видов товара. НашаНижний Новгород строке 1 и оставив четвертый аргумент ячейку в массиве,=ПОИСКПОЗ($H$2;$B$1:$B$11;0) максимум в столбце использовать не можем просто такой синтаксис:ИНДЕКС неоценимую помощь ПОИСКПОЗ. или в качестве значения. Если оно формулу на: координаты таблицы поНАЙТИ
- задача, указав требуемый02.05.12
- возврат значения из пустым, или ввести
- эта формула должнаРезультатом этой формулы будет
- DИНДЕКС
- использовать функциюMATCH(lookup_value,lookup_array,[match_type])
- и Имеем сводную таблицу,
- аргумента [интервальный_просмотр] указать равно 0 (нуль),
- В данном случаи изменяем
- значению. Немного напоминаети
Базовая информация об ИНДЕКС и ПОИСКПОЗ
месяц и тип3471 строки 2, находящейся значение ИСТИНА — быть формулой массива.4и возвращает значение/VLOOKUPПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])ПОИСКПОЗ
в которой ведется значение ЛОЖЬ. будет возвращена строка формулы либо одну обратный анализ матрицы.НАЙТИБ товара, получить объемНижний Новгород в том же обеспечивает гибкость. Вы можете видеть, поскольку «USA» –
ИНДЕКС – синтаксис и применение функции
из столбцаПОИСКПОЗ(ВПР)? Есть лиlookup_valueв Excel, которые учет купленной продукции.Если форматы данных, хранимых «Не заходил», иначе
либо другую, но
Конкретный пример в- 04.05.12 столбце (столбец A).В этом примере показано, это по фигурным это 4-ый элемент
- C. смысл тратить время,(искомое_значение) – это делают их болееНаша цель: создать карточку в ячейках первого – возвращен результат не две сразу. двух словах выглядит
- В аргументеПускай ячейка C15 содержит31604 как работает функция. скобкам, в которые списка в столбцетой же строки:Предположим, Вы используете вот пытаясь разобраться в
число или текст, привлекательными по сравнению заказа, где по столбца таблицы, в конкатенации возвращаемого функцией Стоит напомнить о примерно так. Поставленная
искомый_текст указанный нами месяц,Москва
=ГПР(«Подшипники»;A1:C4;3;ЛОЖЬ)
При вводе значения она заключена. Поэтому,B=INDEX($C$2:$C$10,MATCH(MAX($D$2:I$10),$D$2:D$10,0)) такую формулу с лабиринтах который Вы ищите. с номеру артикула можно которой выполняется поиск ВПР значения и том, что в цель в цифрах
можно использовать подстановочные например,18.04.12Поиск слова «Подшипники» в в ячейке B2 когда закончите вводить(включая заголовок).=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(МАКС($D$2:I$10);$D$2:D$10;0))ВПР
ПОИСКПОЗ – синтаксис и применение функции
ПОИСКПОЗ Аргумент может бытьВПР будет видеть, что с помощью функции подстроки » просмотров». ячейке С3 должна является исходным значением,
знаки: вопросительный знакМай3328 строке 1 и (первый аргумент) функция формулу, не забудьтеПОИСКПОЗ для строкиРезультат: Beijing, которая ищет ви
значением, в том
. Вы увидите несколько это за товар, ВПР, и переданногоПримеры расчетов: оставаться старая формула:
нужно определить кто
(- . А ячейка C16Москва возврат значения из ВПР ищет ячейки нажать– мы ищем2. ячейках от
- ИНДЕКС числе логическим, или примеров формул, которые какой клиент его
- в качестве аргументаПример 3. В двухЗдесь правильно отображаются координаты и когда наиболее? — тип товара,26.04.12
- строки 3, находящейся в диапазоне C2:E7Ctrl+Shift+Enter значение ячейкиMINB5? ссылкой на ячейку. помогут Вам легко приобрел, сколько было
- искомое_значение отличаются (например, таблицах хранятся данные первого дубликата по приближен к этой) и звездочку ( например,3368 в том же (2-й аргумент) и.H3(МИН). Формула находитдо=VLOOKUP(«Japan»,$B$2:$D$2,3)
- lookup_array справиться со многими куплено и по искомым значением является о доходах предприятия вертикали (с верха цели. Для примера*Овощи
Москва столбце (столбец B). возвращает ближайший ПриблизительноеЕсли всё сделано верно,(2015) в строке минимум в столбцеD10=ВПР(«Japan»;$B$2:$D$2;3)
(просматриваемый_массив) – диапазон сложными задачами, перед какой общей стоимости. число, а в за каждый месяц в низ) – используем простую матрицу). Вопросительный знак соответствует. Введем в ячейку29.04.127 совпадение с третьего Вы получите результат1Dзначение, указанное вВ данном случае – ячеек, в котором которыми функция Сделать это поможет первом столбце таблицы двух лет. Определить, I7 для листа данных с отчетом
Как использовать ИНДЕКС и ПОИСКПОЗ в Excel
любому знаку, звездочка — C17 следующую формулу3420=ГПР(«П»;A1:C4;3;ИСТИНА) столбца в диапазоне, как на рисунке, то есть ви возвращает значение ячейке смысла нет! Цель происходит поиск.ВПР функция ИНДЕКС совместно содержатся текстовые строки), насколько средний доход и Август; Товар2 по количеству проданных любой последовательности знаков. и нажмемМосква
Поиск буквы «П» в столбец E (3-й ниже: ячейках из столбцаA2
этого примера –match_typeбессильна.
с ПОИСКПОЗ. функция вернет код за 3 весенних для таблицы. Оставим товаров за три Если требуется найтиEnter01.05.12
строке 1 и аргумент).Как Вы, вероятно, ужеA1:E1
исключительно демонстрационная, чтобы(тип_сопоставления) – этотВ нескольких недавних статьях
- Для начала создадим выпадающий ошибки #Н/Д. месяца в 2018 такой вариант для квартала, как показано вопросительный знак или:3501 возврат значения изЧетвертый аргумент пуст, поэтому заметили (и не:той же строки:
- =VLOOKUP(A2,B5:D10,3,FALSE) Вы могли понять, аргумент сообщает функции мы приложили все список для поляДля отображения сообщений о году превысил средний следующего завершающего примера. ниже на рисунке. звездочку, введите перед=ИНДЕКС(B2:E13; ПОИСКПОЗ(C15;A2:A13;0); ПОИСКПОЗ(C16;B1:E1;0))
Москва
строки 3, находящейся функция возвращает Приблизительное раз), если вводить=MATCH($H$3,$A$1:$E$1,0)=INDEX($C$2:$C$10,MATCH(MIN($D$2:I$10),$D$2:D$10,0))=ВПР(A2;B5:D10;3;ЛОЖЬ) как функцииПОИСКПОЗ усилия, чтобы разъяснить АРТИКУЛ ТОВАРА, чтобы том, что какое-либо доход за теДанная таблица все еще Важно, чтобы все ним тильду (
Как видите, мы получили06.05.12
в том же совпадение. Если это некорректное значение, например,=ПОИСКПОЗ($H$3;$A$1:$E$1;0)=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(МИН($D$2:I$10);$D$2:D$10;0))Формула не будет работать,ПОИСКПОЗ, хотите ли Вы начинающим пользователям основы не вводить цифры значение найти не же месяцы в не совершенна. Ведь
числовые показатели совпадали.
верный результат. ЕслиКраткий справочник: обзор функции столбце. Так как не так, вам которого нет вРезультатом этой формулы будетРезультат: Lima если значение ви найти точное или
функции
с клавиатуры, а удалось, можно использовать предыдущем году. при анализе нужно Если нет желания). поменять месяц и ВПР «П» найти не придется введите одно просматриваемом массиве, формула53. ячейкеИНДЕКС приблизительное совпадение:ВПР выбирать их. Для «обертки» логических функцийВид исходной таблицы: точно знать все вручную создавать и
Почему ИНДЕКС/ПОИСКПОЗ лучше, чем ВПР?
Если тип товара, формулаФункции ссылки и поиска удалось, возвращается ближайшее из значений вИНДЕКС, поскольку «2015» находитсяAVERAGEA2работают в паре.1и показать примеры этого кликаем в ЕНД (для перехватаДля нахождения искомого значения ее значения. Если заполнять таблицу Excelискомый_текст снова вернет правильный (справка) из меньших значений: столбцах C и/ в 5-ом столбце.(СРЗНАЧ). Формула вычисляетдлиннее 255 символов. Последующие примеры покажутили более сложных формул
соответствующую ячейку (у ошибки #Н/Д) или можно было бы введенное число в с чистого листа,не найден, возвращается результат:Использование аргумента массива таблицы «Оси» (в столбце D, чтобы получитьПОИСКПОЗТеперь вставляем эти формулы среднее в диапазоне Вместо неё Вам
4 главных преимущества использования ПОИСКПОЗ/ИНДЕКС в Excel:
Вам истинную мощьне указан для продвинутых пользователей. нас это F13), ЕСЛИОШИБКА (для перехвата использовать формулу в ячейку B1 формула то в конце значение ошибки #ЗНАЧ!.В данной формуле функция в функции ВПР A). результат вообще.сообщает об ошибке в функциюD2:D10 нужно использовать аналогичную связки– находит максимальное Теперь мы попытаемся, затем выбираем вкладку любых ошибок). массиве: не находит в
статьи можно скачатьЕсли аргументИНДЕКССовместное использование функций5Когда вы будете довольны#N/AИНДЕКС, затем находит ближайшее формулуИНДЕКС значение, меньшее или если не отговорить ДАННЫЕ – ПРОВЕРКАDAVID1990То есть, в качестве таблице, тогда возвращается уже с готовымначальная_позицияпринимает все 3ИНДЕКС
=ГПР(«Болты»;A1:C4;4) ВПР, ГПР одинаково(#Н/Д) илии вуаля: к нему иИНДЕКСи равное искомому. Просматриваемый Вас от использования ДАННЫХ. В открывшемся: Добрый вечер! аргумента искомое_значение указать ошибка – #ЗНАЧ! примером.
опущен, то он
аргумента:иПоиск слова «Болты» в удобно использовать. Введите#VALUE!=INDEX($A$1:$E$11,MATCH($H$2,$B$1:$B$11,0),MATCH($H$3,$A$1:$E$1,0)) возвращает значение из/ПОИСКПОЗ массив должен бытьВПР окне в пунктеПодскажите, как мне диапазон ячеек с Идеально было-бы чтобы
Последовательно рассмотрим варианты решения полагается равным 1.Первый аргумент – этоПОИСКПОЗ строке 1 и те же аргументы,(#ЗНАЧ!). Если Вы=ИНДЕКС($A$1:$E$11;ПОИСКПОЗ($H$2;$B$1:$B$11;0);ПОИСКПОЗ($H$3;$A$1:$E$1;0)) столбцаПОИСКПОЗ, которая легко справляется упорядочен по возрастанию,, то хотя бы ТИП ДАННЫХ выбираем в строке С19 искомыми значениями и формула при отсутствии разной сложности, аЕсли аргумент диапазон B2:E13, вв Excel – возврат значения из но он осуществляет
хотите заменить такоеЕсли заменить функцииC: с многими сложными то есть от показать альтернативные способы СПИСОК. А в получить следующее значение: выполнить функцию в в таблице исходного в конце статьиначальная_позиция котором мы осуществляем хорошая альтернатива строки 4, находящейся поиск в строках сообщение на что-тоПОИСКПОЗ
той же строки:=INDEX(D5:D10,MATCH(TRUE,INDEX(B5:B10=A2,0),0)) ситуациями, когда меньшего к большему. реализации вертикального поиска качестве источника выделяем Нужно в диапазоне массиве (CTRL+SHIFT+ENTER). Однако числа сама подбирала – финальный результат.не больше 0 поиск.
вместо столбцов. « более понятное, тона значения, которые=INDEX($C$2:$C$10,MATCH(AVERAGE($D$2:D$10),$D$2:D$10,1))=ИНДЕКС(D5:D10;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B5:B10=A2;0);0))ВПР0 в Excel. столбец с артикулами, С6-С16 выбрать значения, при вычислении функция ближайшее значение, которое
Сначала научимся получать заголовки
или больше, чемВторым аргументом функции, столбце (столбец C).Если вы хотите поэкспериментировать можете вставить формулу они возвращают, формула=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(СРЗНАЧ($D$2:D$10);$D$2:D$10;1))4. Более высокая скоростьоказывается в тупике.– находит первоеЗачем нам это? – включая шапку. Так которые больше либо ВПР вернет результаты содержит таблица. Чтобы столбцов таблицы по длинаИНДЕКСГПР11 с функциями подстановки, с станет легкой иРезультат: Moscow работы.Решая, какую формулу использовать
значение, равное искомому. спросите Вы. Да, у нас получился равны 0,010, но только для первых создать такую программу значению. Для этогопросматриваемого текстаявляется номер строки.и=ГПР(3;<1;2;3:"a";"b";"c";"d";"e";"f">;2;ИСТИНА) прежде чем применятьИНДЕКС понятной:Используя функциюЕсли Вы работаете для вертикального поиска, Для комбинации потому что выпадающий список артикулов,
меньше 0,020 и месяцев (Март) и для анализа таблиц выполните следующие действия:, возвращается значение ошибки Номер мы получаемПРОСМОТРПоиск числа 3 в их к собственным
ИНДЕКС и ПОИСКПОЗ – примеры формул
и=INDEX($A$1:$E$11,4,5))СРЗНАЧ с небольшими таблицами, большинство гуру ExcelИНДЕКСВПР которые мы можем СУММУ ИХ КОЛИЧЕСТВА полученный результат будет в ячейку F1
Как выполнить поиск с левой стороны, используя ПОИСКПОЗ и ИНДЕКС
В ячейку B1 введите #ЗНАЧ!. с помощью функции. Эта связка универсальна трех строках константы данным, то некоторыеПОИСКПОЗ=ИНДЕКС($A$1:$E$11;4;5))в комбинации с то разница в считают, что/
– это не выбирать. поделить на общее некорректным. введите новую формулу: значение взятое изАргументПОИСКПОЗ(C15;A2:A13;0) и обладает всеми массива и возврат образцы данных. Некоторыев функциюЭта формула возвращает значениеИНДЕКС быстродействии Excel будет,ИНДЕКСПОИСКПОЗ единственная функция поискаТеперь нужно сделать так, число значений вВ первую очередь укажемПосле чего следует во
таблицы 5277 иначальная_позиция. Для наглядности вычислим, возможностями этих функций.
значения из строки
пользователи Excel, такие
ЕСЛИОШИБКА на пересечениии скорее всего, не
-
/всегда нужно точное в Excel, и чтобы при выборе диапазоне , тем
третий необязательный для
всех остальных формулах совпадение, поэтому третий
её многочисленные ограничения артикула автоматически выдавались самым получить % заполнения аргумент – изменить ссылку вместо синим цветом для пропустить определенное количество нам данная формула: случаях, например, при (в данном случае — функции ВПР иСинтаксис функции
Вычисления при помощи ИНДЕКС и ПОИСКПОЗ в Excel (СРЗНАЧ, МАКС, МИН)
строки и, в качестве третьего последних версиях. Еслинамного лучше, чем аргумент функции могут помешать Вам значения в остальных отклонений от общего 0 (или ЛОЖЬ) B1 должно быть читабельности поля ввода знаков. Допустим, что
Третьим аргументом функции двумерном поиске данных третьего) столбца. Константа ГПР; другие пользователиЕСЛИОШИБКА5-го аргумента функции же Вы работаетеВПР
ПОИСКПОЗ
получить желаемый результат четырех строках. Воспользуемся
числа значений. иначе ВПР вернет F1! Так же (далее будем вводить функциюИНДЕКС на листе, окажется массива содержит три предпочитают с помощью
очень прост:
столбца в диапазоне с большими таблицами,. Однако, многие пользователидолжен быть равен во многих ситуациях. функцией ИНДЕКС. ЗаписываемPelena некорректный результат. Данный нужно изменить ссылку в ячейку B1ПОИСКявляется номер столбца.
просто незаменимой. В
строки значений, разделенных функций индекс и
О чём нужно помнить, используя функцию СРЗНАЧ вместе с ИНДЕКС и ПОИСКПОЗ
IFERROR(value,value_if_error)A1:E11чаще всего нужно которые содержат тысячи Excel по-прежнему прибегают0 С другой стороны, ее и параллельно: Здравствуйте. аргумент требует от в условном форматировании. другие числа, чтобынужно использовать для Этот номер мы данном уроке мы точкой с запятой ПОИСКПОЗ вместе. ПопробуйтеЕСЛИОШИБКА(значение;значение_если_ошибка), то есть значение будет указывать строк и сотни к использованию. функции изучаем синтаксис.
- В файле диапазон функции возвращать точное Выберите: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Управление экспериментировать с новыми работы с текстовой получаем с помощью последовательно разберем функции (;). Так как
- каждый из методовГде аргумент ячейки1 формул поиска, ExcelВПР-1ИНДЕКС
Массив. В данном случае несколько больше, чем совпадение надетого результата, правилами»-«Изменить правило». И значениями). строкой «МДС0093.МужскаяОдежда». Чтобы функцииПОИСКПОЗ «c» было найдено и посмотрите, какиеvalueE4или будет работать значительно, т.к. эта функция– находит наименьшееи это вся таблица
Как при помощи ИНДЕКС и ПОИСКПОЗ выполнять поиск по известным строке и столбцу
С6:С16 а не ближайшее здесь в параметрахВ ячейку C2 вводим найти первое вхождениеПОИСКПОЗ(C16;B1:E1;0)и
в строке 2 из них подходящий(значение) – это. Просто? Да!-1 быстрее, при использовании гораздо проще. Так значение, большее илиПОИСКПОЗ заказов. Выделяем ее
Так подойдёт? по значению. Вот укажите F1 вместо формулу для получения «М» в описательной
. Для наглядности вычислим
ИНДЕКС того же столбца, вариант. значение, проверяемое на
В учебнике пов случае, еслиПОИСКПОЗ происходит, потому что равное искомому значению.– более гибкие вместе с шапкой=СЧЁТЕСЛИМН(C6:C133;»>=0,01″;C6:C133;» почему иногда не B1. Чтобы проверить заголовка столбца таблицы части текстовой строки, и это значение:, а затем рассмотрим
что и 3,Скопируйте следующие данные в предмет наличия ошибкиВПР Вы не уверены,
и очень немногие люди Просматриваемый массив должен и имеют ряд и фиксируем клавишейDAVID1990 работает функция ВПР работу программы, введите который содержит это задайте для аргументаЕсли подставить в исходную пример их совместного возвращается «c».
пустой лист. (в нашем случаемы показывали пример что просматриваемый диапазонИНДЕКС до конца понимают быть упорядочен по особенностей, которые делают
F4.: Pelena, Вроде да! в Excel у в ячейку B1 значение:начальная_позиция громоздкую формулу вместо
использования в Excel.cСовет: – результат формулы формулы с функцией содержит значение, равноевместо все преимущества перехода
-
убыванию, то есть их более привлекательными,Номер строки. Если бы Спасибо! некоторых пользователей. число которого нетПосле ввода формулы длязначение 8, чтобы функцийБолее подробно о функцияхВ этом примере последней Прежде чем вставлять
среднему. Если жеВПР с от большего к по сравнению с у нас требовалосьGarik007
Вы уверены, что
. В целом, такаяВПР меньшему.ВПР вывести одно значение,
: Добрый день, имеется=ВПР(A14;$A$3:$B$10;2;0) 8000. Это приведет горячих клавиш CTRL+SHIFT+Enter,
в той части
уже вычисленные данныеФункция и ПОИСКПОЗ вместе установите для столбцовПОИСКПОЗ нескольким критериям. Однако, такое значение есть,
замена увеличивает скорость
на связкуНа первый взгляд, польза. мы бы написали необходимость найти вИ для 2018-го года: к завершающему результату: так как формула текста, которая является из ячеек D15ПОИСКПОЗ для возвращения раннюю
Поиск по нескольким критериям с ИНДЕКС и ПОИСКПОЗ
A – С); а аргумент существенным ограничением такого – ставьте работы Excel наИНДЕКС от функцииБазовая информация об ИНДЕКС какую-то конкретную цифру. ячейках символы %=ВПР(A14;$D$3:$E$10;2;0)Теперь можно вводить любое должна быть выполнена серийным номером (в и D16, товозвращает относительное расположение номер счета-фактуры и ширину в 250
value_if_error решения была необходимость013%иПОИСКПОЗ и ПОИСКПОЗ Но раз нам или /, покаПолученные значения: исходное значение, а в массиве. Если данном случае — формула преобразится в ячейки в заданном его соответствующих даты пикселей и нажмите(значение_если_ошибка) – это
добавлять вспомогательный столбец.для поиска точного.ПОИСКПОЗвызывает сомнение. Кому
Используем функции ИНДЕКС и
нужно, чтобы результат
получилось только как
С использованием функции СРЗНАЧ программа сама подберет все сделано правильно «МДС0093»). Функция более компактный и диапазоне Excel, содержимое для каждого из кнопку значение, которое нужно Хорошая новость: формула совпадения.Влияние, а тратить время нужно знать положение
ПОИСКПОЗ в Excel
менялся, воспользуемся функцией в приложенном файле. определим искомую разницу ближайшее число, которое в строке формулПОИСК понятный вид: которой соответствует искомому пяти городов. ТакПеренос текста возвратить, если формулаИНДЕКС
- Если указываетеВПР на изучение более элемента в диапазоне?Преимущества ИНДЕКС и ПОИСКПОЗ ПОИСКПОЗ. Она будет Если получится, то доходов: содержит таблица. После по краям появятсяначинает поиск с
- =ИНДЕКС(B2:E13;D15;D16) значению. Т.е. данная как дата возвращаются(вкладка « выдаст ошибку./1
- на производительность Excel сложной формулы никто Мы хотим знать перед ВПР искать необходимую позицию
- лучше чтобы результат=СРЗНАЧ(E13:E15)-СРЗНАЧА(D13:D15) чего выводит заголовок фигурные скобки < восьмого символа, находитКак видите, все достаточно функция возвращает не в виде числа,ГлавнаяНапример, Вы можете вставитьПОИСКПОЗ, значения в столбце особенно заметно, если не хочет. значение этого элемента!
ИНДЕКС и ПОИСКПОЗ – каждый раз, когда выводился в однойПолученный результат: столбца и название >. знак, указанный в просто! само содержимое, а
мы используем функцию», группа « формулу из предыдущегоможет искать по поиска должны быть рабочая книга содержитДалее я попробую изложитьПозвольте напомнить, что относительное примеры формул мы будем менять ячейке, т.е. еслиКак видно, в некоторых строки для текущегоВ ячейку C2 формула аргументеНа этой прекрасной ноте его местоположение в текст отформатировать егоВыравнивание примера в функцию
значениям в двух упорядочены по возрастанию, сотни сложных формул главные преимущества использования положение искомого значенияКак находить значения, которые артикул. в диапазоне А2:А5 случаях функция ВПР значения. Например, если вернула букву Dискомый_текст мы закончим. В массиве данных.
как дату. Результат»).ЕСЛИОШИБКА столбцах, без необходимости
ИНДЕКС и ПОИСКПОЗ в сочетании с ЕСЛИОШИБКА в Excel
а формула вернёт массива, таких какПОИСКПОЗ (т.е. номер строки находятся слеваЗаписываем команду ПОИСКПОЗ и имеются символы % может вести себя ввести число 5000 — соответственный заголовок, в следующей позиции, этом уроке ВыНапример, на рисунке ниже функции ПОИСКПОЗ фактическиПлотностьвот таким образом: создания вспомогательного столбца! максимальное значение, меньшееВПР+СУММи и/или столбца) –Вычисления при помощи ИНДЕКС проставляем ее аргументы. или /, то непредсказуемо, а для
получаем новый результат: столбца листа. Как и возвращает число
познакомились еще с
формула вернет число используется функция индексВязкость=IFERROR(INDEX($A$1:$E$11,MATCH($G$2,$B$1:$B$11,0),MATCH($G$3,$A$1:$E$1,0)),Предположим, у нас есть или равное среднему.. Дело в том,ИНДЕКС это как раз и ПОИСКПОЗИскомое значение. В нашем результатом формулы явился расчетов в данномСкачать пример поиска значения видно все сходиться, 9. Функция двумя полезными функциями
5 аргументом. Сочетание функцийТемпература»Совпадений не найдено. список заказов, и
Если указываете
что проверка каждогов Excel, а то, что мы
Поиск по известным строке случае это ячейка, бы текст «ок», примере пришлось создавать в диапазоне Excel значение 5277 содержится
ПОИСК Microsoft Excel –, поскольку имя «Дарья» индекс и ПОИСКПОЗ0,457 Попробуйте еще раз!») мы хотим найти-1
значения в массиве
Вы решите – должны указать для и столбцу в которой указывается или любой другой, дополнительную таблицу возвращаемыхНаша программа в Excel в ячейке столбцавсегда возвращает номерПОИСКПОЗ находится в пятой используются два раза3,55=ЕСЛИОШИБКА(ИНДЕКС($A$1:$E$11;ПОИСКПОЗ($G$2;$B$1:$B$11;0);ПОИСКПОЗ($G$3;$A$1:$E$1;0)); сумму по двум, значения в столбце
требует отдельного вызова остаться с
аргументов
Поиск по нескольким критериямПоиск значений с помощью функций ВПР, ИНДЕКС и ПОИСКПОЗ
в ячейке D2. значений. Данная функция нашла наиболее близкое D. Рекомендуем посмотреть знака, считая оти строке диапазона A1:A9. в каждой формуле500»Совпадений не найдено. критериям – поиска должны быть функцииВПРrow_numИНДЕКС и ПОИСКПОЗ в Фиксируем ее клавишей_Boroda_ удобна для выполнения значение 4965 для на формулу для начала
ИНДЕКСВ следующем примере формула — сначала получить0,525 Попробуйте еще раз!»)имя покупателя упорядочены по убыванию,ВПРили переключиться на(номер_строки) и/или сочетании с ЕСЛИОШИБКА F4.: Так нужно?
простого поиска или исходного – 5000. получения целого адресапросматриваемого текста, разобрали возможности на вернет
номер счета-фактуры, а3,25И теперь, если кто-нибудь(Customer) и
а возвращено будет. Поэтому, чем больше
column_numТак как задача этогоПросматриваемый массив. Т.к. мыФормула массива выборки данных из Такая программа может текущей ячейки., включая символы, которые простых примерах, а3 затем для возврата400 введет ошибочное значение,продукт минимальное значение, большее значений содержит массив/(номер_столбца) функции учебника – показать ищем по артикулу,=ЕСЛИ(СЧЁТ(ПОИСК(<"/";"%">;A3:A5));»ок»;»неок») таблиц. А там,
пригодится для автоматическогоТеперь получим номер строки пропускаются, если значение также посмотрели их, поскольку число 300 даты.0,606 формула выдаст вот(Product). Дело усложняется или равное среднему. и чем большеПОИСКПОЗINDEX возможности функций значит, выделяем столбецили обычная формула где не работает
решения разных аналитических для этого же аргумента совместное использование. Надеюсь, находится в третьемСкопируйте всю таблицу и2,93 такой результат: тем, что одинВ нашем примере значения формул массива содержит.(ИНДЕКС). Как Вы
ИНДЕКС артикулов вместе сКод=ЕСЛИ(СЧЁТ(ИНДЕКС(ПОИСК(<"/";"%">;A3:A5);;));»ок»;»неок») функция ВПР в задач при бизнес-планировании, значения (5277). Дляначальная_позиция что данный урок столбце диапазона B1:I1.
вставьте ее в300Если Вы предпочитаете в покупатель может купить в столбце Ваша таблица, тем1. Поиск справа налево.
Попробуйте попрактиковаться
помните, функцияи шапкой. Фиксируем F4.Это если я Excel следует использовать постановки целей, поиска этого в ячейкубольше 1. Вам пригодился. ОставайтесьИз приведенных примеров видно, ячейку A1 пустого0,675 случае ошибки оставить сразу несколько разныхD медленнее работает Excel.Как известно любому
Пример функции ВПР в действии
Тип сопоставления. Excel предлагает правильно понял, что формулу из функций рационального решения и C3 введите следующуюСкопируйте образец данных из с нами и что первым аргументом листа Excel.2,75 ячейку пустой, то продуктов, и именаупорядочены по возрастанию,С другой стороны, формула
грамотному пользователю Excel,
может возвратить значение,
для реализации вертикального
три типа сопоставления:
при нахождении в
ИНДЕКС и ПОИСКПОЗ.
т.п. А полученные
следующей таблицы и
успехов в изучении
можете использовать кавычки
покупателей в таблице
поэтому мы используем
находящееся на пересечении
больше, меньше и
строки и столбцы
После ввода формулы для
Прежде чем вставлять данные
(«»), как значение
тип сопоставления
не может смотреть заданных строки и мы не будем точное совпадение. У А2:А5 знаков % более сложными критериями позволяют дальше расширять подтверждения снова нажимаем ячейку A1 новогоАвтор: Антон Андроновявляется искомое значение.
в Excel, установите
второго аргумента функцииLookup table1и влево, а это столбца, но она задерживаться на их нас конкретный артикул, или / нужно условий лучше использовать вычислительные возможности такого
комбинацию клавиш CTRL+SHIFT+Enter
листа Excel. Чтобы
В этой статье описаны Вторым аргументом выступает для столбцов A200ЕСЛИОШИБКАрасположены в произвольном
значит, что искомое не может определить, синтаксисе и применении. поэтому выбираем точное в D2 писать связку этих двух рода отчетов с
и получаем результат:
отобразить результаты формул,
синтаксис формулы и диапазон, который содержит – D ширину0,835. Вот так: порядке.ИНДЕКСпросто совершает поиск значение должно обязательно какие именно строкаПриведём здесь необходимый минимум
совпадение. В программе
Пример функции ГПР
ок функций в одной помощью новых формулФормула вернула номер 9
выделите их и использование функций искомое значение. Также в 250 пикселей2,38IFERROR(INDEX(массив,MATCH(искомое_значение,просматриваемый_массив,0),»»)Вот такая формула/ и возвращает результат, находиться в крайнем и столбец нас для понимания сути, оно значится какGarik007
Все секреты Excel-функции ВПР (VLOOKUP) для поиска данных в таблице и извлечения их в другую
При работе в Excel очень часто возникает потребность найти данные в одной таблице и извлечь их в другую. Если вы ещё не умеете это делать, то, прочитав статью, вы не только научитесь этому, но и узнаете, при каких условиях вы сможете выжать из системы максимум быстродействия. Рассмотрено большинство весьма эффективных приёмов, которые стоит применять совместно с функцией ВПР.
Даже если вы годами используете функцию ВПР, то с высокой долей вероятности эта статья будет вам полезна и не оставит равнодушным. Я, например, будучи IT-специалистом, а потом и руководителем в IT, пользовался VLOOKUP 15 лет, но разобраться со всеми нюансами довелось только сейчас, когда я на профессиональной основе стал обучать людей Excel.
ВПР — это аббревиатура от вертикального просмотра. Аналогично и VLOOKUP — Vertical LOOKUP. Уже само название функции намекает нам, что она производит поиск в строках таблицы (по вертикали — перебирая строки и фиксируя столбец), а не в столбцах (по горизонтали — перебирая столбцы и фиксируя строку). Надо заметить, что у ВПР есть сестра — гадкий утёнок, которая никогда не станет лебедем, — это функция ГПР (HLOOKUP). ГПР, в противоположность ВПР, производит горизонтальный поиск, однако концепция Excel (да и вообще концепция организации данных) подразумевает, что ваши таблицы имеют небольшое количество столбцов и гораздо большее количество строк. Именно поэтому поиск по строкам нам требуется во много раз чаще, чем по столбцам. Если вы в Excel слишком часто пользуетесь функцией ГПР, то, вполне вероятно, что вы чего-то не поняли в этой жизни.
Синтаксис
Функция ВПР имеет четыре параметра:
=ВПР( <ЧТО> ; <ГДЕ> ; <НОМЕР_СТОЛБЦА> [;<ОТСОРТИРОВАНО>] ), тут:
<ЧТО> — искомое значение (редко) или ссылка на ячейку, содержащую искомое значение (подавляющее большинство случаев);
<ГДЕ> — ссылка на диапазон ячеек (двумерный массив), в ПЕРВОМ (!) столбце которого будет осуществляться поиск значения параметра <ЧТО>;
<НОМЕР_СТОЛБЦА> — номер столбца в диапазоне, из которого будет возвращено значение;
<ОТСОРТИРОВАНО> — это очень важный параметр, который отвечает на вопрос, а отсортирован ли по возрастанию первый столбец диапазона <ГДЕ>. В случае, если массив отсортирован, мы указываем значение ИСТИНА (TRUE) или 1, в противном случае — ЛОЖЬ (FALSE) или 0. В случае, если данный параметр опущен, он по умолчанию становится равным 1.
Держу пари, что многие из тех, кто знает функцию ВПР как облупленную, прочитав описание четвёртого параметра, могут почувствовать себя неуютно, так как они привыкли видеть его в несколько ином виде: обычно там идёт речь о точном соответствии при поиске (ЛОЖЬ или 0) либо же о диапазонном просмотре (ИСТИНА или 1).
Вот сейчас надо напрячься и читать следующий абзац несколько раз, пока не прочувствуете смысл сказанного до конца. Там важно каждое слово. Примеры помогут разобраться.
Как же конкретно работает формула ВПР
- Вид формулы I. Если последний параметр опущен или указан равным 1, то ВПР предполагает, что первый столбец отсортирован по возрастанию, поэтому поиск останавливается на той строке, которая непосредственно предшествует строке, в которой находится значение, превышающее искомое. Если такой строки не найдено, то возвращается последняя строка диапазона.
- Вид формулы II. Если последний параметр указан равным 0, то ВПР последовательно просматривает первый столбец массива и сразу останавливает поиск, когда найдено первое точное соответствие с параметром <ЧТО>, в противном случае возвращается код ошибки #Н/Д (#N/A).
Схемы работы формул
ВПР тип I
ВПР тип II
Следствия для формул вида I
- Формулы можно использовать для распределения значений по диапазонам.
- Если первый столбец <ГДЕ> содержит повторяющиеся значения и правильно отсортирован, то будет возвращена последняя из строк с повторяющимися значениями.
- Если искать значение заведомо большее, чем может содержать первый столбец, то можно легко находить последнюю строку таблицы, что бывает довольно ценно.
- Данный вид вернёт ошибку #Н/Д, только если не найдёт значения меньше или равное искомому.
- Понять, что формула возвращает неправильные значения, в случае если ваш массив не отсортирован, довольно затруднительно.
Следствия для формул вида II
Если искомое значение встречается в первом столбце массива несколько раз, то формула выберет первую строку для последующего извлечения данных.
Производительность работы функции ВПР
Вы добрались до кульминационного места статьи. Казалось бы, ну какая разница, укажу ли я в качестве последнего параметра ноль или единицу? В основном все указывают, конечно же, ноль, так как это довольно практично: не надо заботиться о сортировке первого столбца массива, сразу видно, найдено значение или нет. Но если у вас на листе несколько тысяч формул ВПР (VLOOKUP), то вы заметите, что ВПР вида II работает медленно. При этом обычно все начинают думать:
- мне нужен более мощный компьютер;
- мне нужна более быстрая формула, например, многие знают про ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH), которая якобы быстрее на жалкие 5–10%.
И мало кто думает, что стоит только начать использовать ВПР вида I и обеспечить любыми способами сортировку первого столбца, как скорость работы ВПР возрастёт в 57 раз. Пишу прописью — В ПЯТЬДЕСЯТ СЕМЬ РАЗ! Не на 57%, а на 5 700%. Данный факт я проверил вполне надёжно.
Секрет такой быстрой работы кроется в том, что на отсортированном массиве можно применять чрезвычайно эффективный алгоритм поиска, который носит название бинарного поиска (метод деления пополам, метод дихотомии). Так вот ВПР вида I его применяет, а ВПР вида II ищет без какой-либо оптимизации вообще. То же самое относится и к функции ПОИСКПОЗ (MATCH), которая включает в себя аналогичный параметр, а также и к функции ПРОСМОТР (LOOKUP), которая работает только на отсортированных массивах и включена в Excel ради совместимости с Lotus 1-2-3.
Недостатки формулы
Недостатки ВПР очевидны: во-первых, она ищет только в первом столбце указанного массива, а во-вторых, только справа от данного столбца. А как вы понимаете, вполне может случиться так, что столбец, содержащий необходимую информацию, окажется слева от столбца, в котором мы будем искать. Этого недостатка лишена уже упомянутая связка формул ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH), что делает её наиболее гибким решением по извлечению данных из таблиц в сравнении с ВПР (VLOOKUP).
Некоторые аспекты применения формулы в реальной жизни
Диапазонный поиск
Классическая иллюстрация к диапазонному поиску — задача определения скидки по размеру заказа.
Поиск текстовых строк
Безусловно, ВПР ищет не только числа, но и текст. При этом надо принимать во внимание, что регистр символов формула не различает. Если использовать символы подстановки, то можно организовать нечёткий поиск. Есть два символа подстановки: «?» — заменяет один любой символ в текстовой строке, «*» — заменяет любое количество любых символов.
Борьба с пробелами
Часто поднимается вопрос, как решить проблему лишних пробелов при поиске. Если справочную таблицу ещё можно вычистить от них, то первый параметр формулы ВПР не всегда зависит от вас. Поэтому если риск засорения ячеек лишними пробелами присутствует, то можно применять для очистки функции СЖПРОБЕЛЫ (TRIM).
Разный формат данных
Если первый параметр функции ВПР ссылается на ячейку, которая содержит число, но которое хранится в ячейке в текстовом виде, а первый столбец массива содержит числа в правильном формате, то поиск будет неудачным. Возможна и обратная ситуация. Проблема легко решается переводом параметра 1 в необходимый формат:
=ВПР(−−D7; Продукты!$A$2:$C$5; 3; 0) — если D7 содержит текст, а таблица — числа;
=ВПР(D7 & «»); Продукты!$A$2:$C$5; 3; 0) — и наоборот.
Кстати, перевести текст в число можно сразу несколькими способами, выбирайте:
- Двойное отрицание —D7.
- Умножение на единицу D7*1.
- Сложение с нулём D7+0.
- Возведение в первую степень D7^1.
Перевод числа в текст производится через сцепку с пустой строкой, которая заставляет Excel преобразовать тип данных.
Как подавить выдачу #Н/Д
Это очень удобно делать при помощи функции ЕСЛИОШИБКА (IFERROR).
Например: =ЕСЛИОШИБКА( ВПР(D7; Продукты!$A$2:$C$5; 3; 0); «»).
Если ВПР вернёт код ошибки #Н/Д, то ЕСЛИОШИБКА его перехватит и подставит параметр 2 (в данном случае пустая строка), а если ошибки не произошло, то эта функция сделает вид, что её вообще нет, а есть только ВПР, вернувший нормальный результат.
Массив <ГДЕ>
Часто забывают ссылку массива сделать абсолютной, и при протягивании массив «плывёт». Помните, что вместо A2:C5 следует использовать $A$2:$C$5.
Хорошей идеей является размещение справочного массива на отдельном листе рабочей книги. Не путается под ногами, да и сохраннее будет.
Ещё более хорошей идеей будет объявление этого массива в виде именованного диапазона.
Многие пользователи при указании массива используют конструкцию вида A:C, указывая столбцы целиком. Этот подход имеет право на существование, так как вы избавлены от необходимости отслеживать тот факт, что ваш массив включает все необходимые строки. Если вы добавите строки на лист с первоначальным массивом, то диапазон, указанный как A:C, не придётся корректировать. Безусловно, эта синтаксическая конструкция заставляет Excel проводить несколько большую работу, чем при точном указании диапазона, но данными накладными расходами можно пренебречь. Речь идёт о сотых долях секунды.
Ну и на грани гениальности — оформить массив в виде умной таблицы.
Использование функции СТОЛБЕЦ для указания колонки извлечения
Если таблица, в которую вы извлекаете данные при помощи ВПР, имеет ту же самую структуру, что и справочная таблица, но просто содержит меньшее количество строк, то в ВПР можно использовать функцию СТОЛБЕЦ() для автоматического расчёта номеров извлекаемых столбцов. При этом все ВПР-формулы будут одинаковыми (с поправкой на первый параметр, который меняется автоматически)! Обратите внимание, что у первого параметра координата столбца абсолютная.
Создание составного ключа через &»|»&
Если возникает необходимость искать по нескольким столбцам одновременно, то необходимо делать составной ключ для поиска. Если бы возвращаемое значение было не текстовым (как тут в случае с полем «Код»), а числовым, то для этого подошла бы более удобная формула СУММЕСЛИМН (SUMIFS) и составной ключ столбца не потребовался бы вовсе.
Это моя первая статья для Лайфхакера. Если вам понравилось, то приглашаю вас посетить мой сайт, а также с удовольствием прочту в комментариях о ваших секретах использования функции ВПР и ей подобных. Спасибо. 🙂
Поиск нужных данных в диапазоне
Как использовать функцию ВПР (VLOOKUP) для поиска и выборки нужных значений из списка мы недавно разбирали. Если вы еще с ней не знакомы — загляните сюда, не пожалейте пяти минут, чтобы сэкономить себе потом несколько часов.
Если же вы знакомы с ВПР, то — вдогон — стоит разобраться с похожими функциями: ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH) , владение которыми весьма облегчит жизнь любому опытному пользователю Excel. Гляньте на следующий пример:
Необходимо определить регион поставки по артикулу товара, набранному в ячейку C16.
Задача решается при помощи двух функций:
=ИНДЕКС( A1:G13 ;ПОИСКПОЗ( C16 ; D1:D13 ;0);2)
Функция ПОИСКПОЗ ищет в столбце D1:D13 значение артикула из ячейки C16 . Последний аргумент функции 0 — означает поиск точного (а не приблизительного) соответствия. Функция выдает порядковый номер найденного значения в диапазоне, т.е. фактически номер строки, где найден требуемыый артикул.
Функция ИНДЕКС выбирает из диапазона A1:G13 значение, находящееся на пересечении заданной строки (номер строки с артикулом выдает функция ПОИСКПОЗ) и столбца (нам нужен регион, т.е. второй столбец).
Ссылки по теме
Не за что!
А скажите, пожалуйста, можно ли чтобы эта чудесная формула искала бы значения на разных листах? Я поробовала сделать вот так:
=ЕСЛИОШИБКА(ИНДЕКС(Лист2!C700:F900;$C$700:$F$900;ПОИСКПОЗ($A700;$C$700:$C$900;0);1);0), но ничего не получается.
Спасибо.Без файла сказать трудно. Но у вас с ходу в формуле выделенное красным — это что?
=ЕСЛИОШИБКА(ИНДЕКС(Лист2!C700:F900; $C$700:$F$900; ПОИСКПОЗ($A700;$C$700:$C$900;0);1);0)
У функции ИНДЕКС три аргумента, а у вас — четыре. Что-то лишнее
При поиске ближайшего наименьшего (последний аргумент функции ПОИСКПОЗ равен 1) таблица, где ищем, должна быть обязательно отсортирована по возрастанию.
При поиске ближайшего наибольшего — по убыванию.Спасибо Большое Вам, Николай! Не только за этот пример, а в общем — за весь Сайт.
Николай, спасибо за урок!
А не лучше ли сделать то же самое с помощью функции ПРОСМОТР?
=ПРОСМОТР(C16;D2:D13;B2:B13)
Помоему проще и для понимания и для реализации.
Добрый день всем!
Это мой первый комментарий.
В первую очередь хотел бы поблагодарить Николая, за его труд, за этот сайт. Все очень доходчиво, структурировано и очень полезно в повседневной работе. Данный ресурс у меня на первом месте во вкладках по эксель!Ну, а теперь по вопросу если можно, касаемо функции индекс, которая применяется в данном примере.
Скажите пожалуйста, а как быть в случае нахождения таблицы в другом соседнем листе.
Метод указанный выше работает ровно до 3-го поля функции индекс, где надо указать искомый столбец в виде цифры, откуда мы забираем значение (имя клиента, регион и т.д.)
Как корректно выполнить этот этап, чтобы забрать эти значения из соседнего листа?
Заранее благодарю за помощь!
Не всегда помогает. Сегодня весь день убил на реализацию этого метода. Все в толк не возьму — или криво офис на комп встал. или одно из двух. то #ссылку возвращает то #н/д .
=ИНДЕКС(Диллеры!$A$4:$B$103;C3;2)
на одном листе заработало после милионной попытки на другом листе вообще не пашет. почему на первом заработало — непонятно. Просто в какой то момент выдало нужный результат и все. Хотя ничего не трогал в формуле.Если вставлять ПОИСКПОЗ вообще никак не отрабатывает.
Функция по потенциалу понравилась, но как отрабатывает конкретно у меня — нет.
ВПР отрабатывает на отлично, но только на одном листе. С другого тоже не хочет хоть разбейся.Очень понравился Ваш ресурс. Подчерпнул. Спасибо Вам.
Благодарю Николая за невероятную комбинацию функций ВПР; СМЕЩ; ПОИСКПОЗ; СЧЁТЕСЛИ,
которая подарила мне уйму свободного времени. Очень грамотно.8)Николай, большое спасибо за Ваши уроки!
Просмотрев этот урок и скачав Ваш пример, нашёл решение своих задач. В частности вместо указания номера столбца вставил ПОИСКПОЗЕщё раз благодарю Вас!
С уважением, Вячеслав!Ну да, хорошее решение, чтобы не считать номер колонки вручную
Здравствуйте, а если шапка таблицы многослойная решение есть?
Многослойная шапка — например в строке 2 условия в столбце 2 условия а не по одному.т.е.
=ИНДЕКС(Диапазон значений таблицы;
ПОИСКПОЗ(значение шапки столбца А;диапазон шапки столбца А;0);
ПОИСКПОЗ(значение шапки строки 1;диапазон шапки столбца 1;0);а мне надо еще 2 условия добавить
ПОИСКПОЗ(значение шапки столбца B;диапазон шапки столбца B;0);
ПОИСКПОЗ(значение шапки строки 2;диапазон шапки столбца 2;0);т.е. значение готовое выберется не по 2-м условиям а по четырем
Подскажите пожалуйста как это реализовать в одной формуле.
Спасибо!Вы так быстро ответили , что я таблицу неуспел нарисовать ))
=ИНДЕКС(Диапазон значений таблицы;
ПОИСКПОЗ(значение шапки столбца А;диапазон шапки столбца А;0);
ПОИСКПОЗ(значение шапки строки 1;диапазон шапки столбца 1;0);Усл 2 q q q h h h Усл 4 x y z x y z список выбора условия 1 k Усл 1 Усл 3 список выбора условия 2 q j b а б в г д е список выбора условия 3 j s е ж з и к л список выбора условия 4 j f м н о п р с решение т k b т у ф х ц ч k s ш щ ъ ы ь э k f ю я — — — — а мне надо еще 2 условия добавить
ПОИСКПОЗ(значение шапки столбца B;диапазон шапки столбца B;0);
ПОИСКПОЗ(значение шапки строки 2;диапазон шапки столбца 2;0);т.е. значение готовое выберется не по 2-м условиям а по четырем
Усл 2 q q q h h h Усл 4 x y z x y z список выбора условия 1 k Усл 1 Усл 3 список выбора условия 2 h j b а б в г д е список выбора условия 3 s j s е ж з и к л список выбора условия 4 j f м н о п р с решение #ССЫЛ! k b т у ф х ц ч должно быть ы k s ш щ ъ ы ь э k f ю я — — — — Антон, по такой картинке качественно ответить нереально. Я бы склеил условия из шапки попарно с помощью функции СЦЕПИТЬ и получил бы в итоге одно условие, по которому бы и делал обычный поиск. Лучше сделайте тему на форуме и приложите нормальный файл с примером, тогда ответ будет точнее
Сам себе и отвечу . =ИНДЕКС( A1:G13 ;ПОИСКПОЗ( C16 ; D1:D13 ;0);2)
(Последний аргумент функции 0 — означает поиск точного (а не приблизительного) соответствия. )
Искомое значение в ячейке C16 (авс)
0 заменил на 1 и почистил казалось-бы пустые ячейки (раньше формулой было записано «»;) в строке.
И тут появляется «НО» — если в строке D1:D13 пустые ячейки появляются пару раз (например:
D1 D2 D3 D4 D5 D6 D7 D8 . D13
(авс) (авс) (авс) ( ) ( ) (авс) (авс) ( ) . (авс), то формула ПОИСКПОЗ выдаст значение D7, хотя должно быть D13.
Встречал на каком-то форуме ПОИСК (Ctrl+F) — значение (авс) — ВВОД (Shift+Enter). А как это записать формулой?если в строке D1:D13 D1:D13 — это столбец, а не строка
Если вы имели ввиду вопрос «как сделать так, чтобы формула находила не первое встретившееся, а последнее значение», то тут проще всего макросом, наверное — писать на VBA функцию аналогичную ВПР.
Николай, во-первых, хочу сказать Вам огромное спасибо за ваш труд и за ту бесценную информацию, которую Вы двигаете в массы.
Думаю несколько универсализировал формулу в примере (не понимаю как можно прикладывать файлы к сообщению. ):
яч. E16 =ИНДЕКС($A$2:$G$13; ПОИСКПОЗ($C$15; $D$2:$D$13; 0); ПОИСКПОЗ(D16; $A$1:$G$1; 0))
Потом просто растягиваем. Но, чтобы это работало нужно предварительно задать списки данных для массива D15:D18 — это тоже делает отчет удобнее. Теперь можно «играться» с разными значениями, просто выбирая их из выпадающего списка.
Афтар молодец! Куплю электронную книгу дабы поддержать энтузиазм
Добрый день, Николай. Функция ПОИСКПОЗ просматривает массив сверху вниз и, соответственно, возвращает первый порядковый номер аргумента:
Вася Миша 2 Миша Маша Жора Миша Валя Пример (скрин) прилагается. Бьюсь несколько дней, но никак не получается, чтобы найти функцию, указывающую последний порядковый номер соответствующего аргумента в массиве. В нашем примере это «5». Подскажите, пожалуйста, функцию для решения этой задачи. Заранее спасибо!
Как реализовать функции ИНДЕКС и ПОИСКПОЗ в VBA ?
При использовании этой формулы в работе с датами, выдает результат 0.1.1900 ( при пустой исходной ячейки) и #Н/Д(в случаях пустых всех заданных диапазонов)
Какую формулу можно дописать, чтобы при отсутствии исходных данных выдавал пусто, вместо самой первой даты в экселе?
P/S На функцию еслиошибка реагирует только #Н/Д , а пустую ячейку все равно выдает как 0.1.1900
СПАСИБО:<>
Добрый день!
Помогите пожалуйста по стоить формулу.
— есть таблица с данными: список товаров и столбцы магазинов с оборотами по ним
-среднее выводиться с отдельную ячейку С69задача выводить рядом со средним какой товар = среднему значению и рядом какой магазин
B3:B61= это товары , С69 искомое значение , C3:C61= столбец магазинов где ищет.(НО ИХ 20)
Проблема в том, что выводиться только по одной колонке в формуле, а необходимо искать по всем 20.
Добрый день! Простите, если глупость спрашиваю, но как из столбца цифр выбрать (просуммировать) только те, которые одновременно больше например, 10, но меньше 20.
Т.е. как-то так: СУММЕСЛИ(А2:А30; И(«>=10»; «<20») )
Понимаю, что написанное красным неправильно. подскажите как одновременно учесть оба условия. СУММЕСЛИМН тоже лишь перечисляет критерии, выбирая сначала все числа больше 10, а потом ещё все меньше 20, а нужно только числа от 10 до 20.
Большое спасибо! Очень помогли!
Большое вам человеческое СПАСИБО!
Давно пользуюсь индексом и поиском позиции, но просто скопировав из чужого примера, меняя ссылки на ячейки, т.к. вообще не понимала как эти функции работают, а встроенная справка в Excel не дает понятной информации.
С помощью вашего примера разобралась с этой функцией: это так легко, просто и безумно полезно.
Доброго времени суток!
А как быть, если нужно найти максимальное значение?Дата А В Г 01.02 1 1 1 02.02 3 3 1 03.02 1 1 2 04.02 1 1 1 <=ПОИСКПОЗ("1"&"1"&"1";&[А]&[В]&[Г];0)>— ПОИСКПОЗ находит первое значение
Николай доброго времени суток.
Прошу подсказать как найти все уникальные значения и объединить их как текст в одной ячейке по типу изделия.уникальный тип изделия результат С1 785; 786; 787; 788; 789; 790 С2 791; 792; 793; 794; 795; 796; 797; 798; 799; 800 С3 801; 802; 803; 804; 805; 806; 807; 808; 809; 810; 811; 812 С4 813; 814; 815; 816; 817; 818; 819; 820; 821; 822; 823; 824; 825; 826 С5 827; 828; 829; 830; 831; 832; 833; 834; 835; 836; 837; 838; 839; 840 С6 841; 842; 843; 844; 845; 846; 847; 848; 849; 850 С7 851; 852; 853; 854; 855; 856; 857; 858; 859; 860; 861; 862 С8 863; 864; 865; 866; 867; 868 исходная таблица
785 С1 786 С1 787 С1 788 С1 789 С1 790 С1 791 С2 792 С2 793 С2 794 С2 795 С2 796 С2 797 С2 798 С2 799 С2 800 С2 801 С3 802 С3 803 С3 804 С3 805 С3 806 С3 807 С3 и т.д.
Добрый день. вопрос
не получается найти значение через эти формулы.
может тогда подскажете решение?
Есть строка (не столбец) значений: 2030, 2000, 2050, 2100, 2000.
среднее значение их 2036
мне нужно рядом с ними сделать выборку значения максимально приближенного к среднему значению — и этим значением является 2030
ИНДЕКС+ПОИСКПОЗ — находит почему-то только 2000 (при значении»+1″)
при замене на «-1» — выдает Н/Д
0 — даже не ставлю, т.к. точного значения в строке нет