Как искать в Excel: поиск слов и ячеек в таблицах
20.11.2020Как выполнить поиск значений в программе «Excel» .
Поиск в Эксель
Далее описаны несколько вариантов поиска и фильтрации данных в таблице «Эксель».
Поиск нужных данных в диапазоне
13205 28.10.2012
Как использовать функцию ВПР (VLOOKUP)
для поиска и выборки нужных значений из списка мы недавно разбирали. Если вы еще с ней не знакомы — загляните сюда, не пожалейте пяти минут, чтобы сэкономить себе потом несколько часов.
Если же вы знакомы с ВПР, то — вдогон — стоит разобраться с похожими функциями: ИНДЕКС (INDEX)
и
ПОИСКПОЗ (MATCH)
, владение которыми весьма облегчит жизнь любому опытному пользователю Excel. Гляньте на следующий пример:
Необходимо определить регион поставки по артикулу товара, набранному в ячейку C16.
Задача решается при помощи двух функций:
=ИНДЕКС(A1:G13;ПОИСКПОЗ(C16;D1:D13;0);2)
Функция ПОИСКПОЗ
ищет в столбце
D1:D13
значение артикула из ячейки
C16
. Последний аргумент функции 0 — означает поиск точного (а не приблизительного) соответствия. Функция выдает порядковый номер найденного значения в диапазоне, т.е. фактически номер строки, где найден требуемыый артикул.
Функция ИНДЕКС
выбирает из диапазона
A1:G13
значение, находящееся на пересечении заданной строки (номер строки с артикулом выдает функция
ПОИСКПОЗ
) и столбца (нам нужен регион, т.е. второй столбец).
Ссылки по теме
- Использование функции ВПР (VLOOKUP) для поиска и подстановки значений.
- Улучшенная версия функции ВПР (VLOOKUP)
- Многоразовый ВПР
Страницы:
Андрей
28.10.2012 23:19:31
а как помкнять задачу? Например у меня есть таблица и надо найти число по 2 параметрам (к примеру по x и y значениям). Ссылка
Елена
28.10.2012 23:19:52
Андрей, ваша задача решается аналогично, только после поиска номера строки (x), ищется точно так же номер столбца (y). =индекс(массив_данных;(поискоз(номер_строки;массив_строк;0));(поискпоз(код_стобца;массив_столбцов;0))). Родитель Ссылка
Hovik Ghambaryan
25.11.2014 13:30:23
здравствуйте у меня тоже проблема по этой тему, дело в том что нужно искать в не большой база данных двух совпадение, Москва в одном ячейке а 002 рядом и ест лист в котором ест тоже в одном Москва а рядом 002,просто там ест много 002 и Москва, но я хочу во первых формула нашла именно ту Москву с которым рядом ест 002 и второй в первом листе рядом Москва и 002 пишется 10 , а втором листе рядом Москва 002 ест 5 надо чтобы формула нашла эти цифры и умножала их очень прошу помогите у меня уже голова болит от этого но ничего не получается Родитель Ссылка
kep
28.10.2012 23:20:30
Оп-па, всё нормально, когда искомое значение найдено, НО, если искомое значение не найдено, тогда функция возвращает «#Н/Д» ВОПРОС: как сделать, чтобы вместо «#Н/Д» значение ячейки равнялось нулю ? Ссылка
Николай Павлов
28.10.2012 23:26:41
Используйте, например, функцию ЕСЛИОШИБКА — она перехватывает любые ошибки и выводит вместо них любое нужное вам значение (0). Родитель Ссылка
Галина
28.10.2012 23:21:40
Огромное спасибо, кто сделал данный сайт с помощью него я решила свою задачу. Ссылка
Николай Павлов
28.10.2012 23:26:55
Не за что! Родитель Ссылка
Леонид
28.10.2012 23:22:36
Очень полезный сайт! Огромный плюс в том, что названия функций даны и на русском, и на английском — хороший выход на сайт с поисковиков. Сочетание ИНДЕКС+ПОИСКПОЗ можно использовать, если нужно не только выбрать из исходной таблицы одно значение, но и целые строчки (например, из таблицы продаж, в которой также подсчитаны промежуточные итоги по месяцам, перенести в новую таблицу только строчки с промежуточными итогами). Решение: в функции ИНДЕКС и ПОИСКПОЗ закрепить диапазоны полностью в кажной, а также закрепить ячейку, по которой ищется совпадение так, чтобы по столбцам не смещалась. Пример:
Ссылка
Parviz
28.10.2012 23:24:09
Здравствуйте, у меня вопрос по данному примеру: =ИНДЕКС(A1:G13;ПОИСКПОЗ(C16;D1:D13;0);2) число 2 это что? Ссылка
Николай Павлов
28.10.2012 23:27:41
это номер столбца в таблице, откуда мы берем значение, т.е. регион Родитель Ссылка
Дмитрий
28.10.2012 23:25:36
Дополнительное условие к задаче: Предположим что у артикула товара 8985 не один а два региона. Возможно ли решение при условии что регионы будут записаны в одну ячейку? Ссылка
Николай Павлов
28.10.2012 23:28:52
Вытащить все вхождения, а не только первое можно с помощью формулы массива — см. здесь Родитель Ссылка
Александр Р
01.01.2013 01:43:20
Подскажите пожалуйста, а если артикул 6576 повторяется два раза в диапазоне D1:D13, но при этом регионы поставки для него разные. Как лучше решить текущую задачу «определить регион поставки по артикулу товара, набранному в ячейку C16»
? Ссылка
Николай Павлов
03.01.2013 00:02:23
Приведенные выше формулы выведут вам первый встретившийся регион. Если вам нужно вывести все регионы для заданного артикула, то придется использовать более хитрые конструкции — см. Многоразовый ВПР Родитель Ссылка
Александр Р
04.01.2013 02:08:28
Благодарю за ответ. Честно говоря, перед мной стояла немножко другая задача. Но как раз с помощью этой темы и информации с вашего форума её удалось решить. Ещё раз спасибо. Родитель Ссылка
Татьяна Данилова
27.11.2019 13:39:27
Шанс получить ответ ничтожный, но вдруг.. мне также требуется вывести сумму всех значений при вводе нескольких условий, при этом СУММЕСЛИМН не подходит, т.к. значения для суммирования не только в одном столбце. Какую формулу Вам удалось составить? Родитель Ссылка
Joricc
09.01.2013 14:48:34
А скажите, пожалуйста, можно ли чтобы эта чудесная формула искала бы значения на разных листах? Я поробовала сделать вот так: =ЕСЛИОШИБКА(ИНДЕКС(Лист2!C700:F900;$C$700:$F$900;ПОИСКПОЗ($A700;$C$700:$C$900;0);1);0), но ничего не получается… Спасибо. Ссылка
Николай Павлов
11.01.2013 18:06:51
Без файла сказать трудно. Но у вас с ходу в формуле выделенное красным — это что? =ЕСЛИОШИБКА(ИНДЕКС(Лист2!C700:F900;$C$700:$F$900;ПОИСКПОЗ($A700;$C$700:$C$900;0);1);0) У функции ИНДЕКС три аргумента, а у вас — четыре. Что-то лишнее Родитель Ссылка
atas
11.04.2013 08:07:19
Конечно можно. У меня была похожая задача и получилось только с INDIRECT. В моем случае название листов находятся в столбце А. (не смог только разобраться с большим количеством кавычек, но работает) =INDEX(INDIRECT(«‘»&$A5&»‘!$A$8:$Z$50»);MATCH($M$1;INDIRECT(«‘»&$A5&»‘!$B$8:$B$50»);0);J$4) Родитель Ссылка
Елена
13.01.2013 10:11:19
добрый день. У меня при изменении значения, по которому нужно искать, найденные значения не меняются автоматически, только если нажать на эту ячейку и Enter, либо сохранив файл. Что можно сделать? Ссылка
Николай Павлов
13.01.2013 11:53:44
Видимо у вас выключен автоматический пересчет формул. Вкладка Формулы — Вычисления — Автоматически
. Родитель Ссылка
Ирина
08.02.2013 08:36:20
Здравствуйте,Николай. Очень Вас прошу помочь в решении: на одной странице диапазон ячеек в 4 столбика, необходимо задать условие, что если в диапазоне ячеек четвертого столбика встретится 0, то необходимо выбрать значение из левого второго столбика и поставить в определенный диапазон ячеек на другом листе,причем уже значение 100( то есть в 1-листе О, то на 2-м листе 100 и сумма всех этих «100»;). Вопрос: какую функцию выбрать. И ворой вопрос: В интернете я открываю свой киви кошелек и вижу сумму, а можно ли сделать через гиперссылку чтобы программа видела остаток по кошельку на данный момент не заходя в интернет? Заранее спасибо. Ирина Ссылка
Николай Павлов
08.02.2013 09:57:17
Ирина, с вопросами не по теме примера лучше на форум. Создайте тему, приложите файл, опишите ситуацию и желаемый результат. Здесь комментарии к примеру. Да и на вопрос ваш, не видя вашего файла, ответить нереально — при всем желании. Родитель Ссылка
Лиза
11.02.2013 08:40:14
При использовании ПОИСКПОЗ столкнулась с проблемой: мне нужно находить ячейку не по точному значению, а брать ближайшее меньшее значение и ближайшее большее значение. Меньшее значение находить без всяких проблем. При поиске большего выдает #Н/Д. Может вы знаете, почему так? Ссылка
Николай Павлов
11.02.2013 10:38:40
При поиске ближайшего наименьшего (последний аргумент функции ПОИСКПОЗ равен 1) таблица, где ищем, должна быть обязательно отсортирована по возрастанию. При поиске ближайшего наибольшего — по убыванию. У вас так? Родитель Ссылка
Лиза
11.02.2013 11:02:24
Теперь так. Спасибо. Родитель Ссылка
Юрий
05.04.2013 19:55:31
Скажите пожалуйста, если вместо артикула товаров (в примере) надо подставлять текст. Попробовал -выдает #Н/Д . Пробовал задавать диапазон ( артикулов) как текст, все равно выдает ошибку.Что нужно сделать чтобы её исправить? Ссылка
Николай Павлов
11.04.2013 08:05:42
Если вы имеете ввиду подставлять вместо обычного диапазона выделенного мышью — имя диапазона текстом, то придется использовать функцию ДВССЫЛ, которая превратит текстовое название диапазона в реальную ссылку на него. Родитель Ссылка
INFINITY
12.05.2013 06:40:07
Спасибо Большое Вам, Николай! Не только за этот пример, а в общем — за весь Сайт!!! Ссылка
Антон Попов
20.05.2013 19:14:12
Николай, спасибо за урок! А не лучше ли сделать то же самое с помощью функции ПРОСМОТР? =ПРОСМОТР(C16;D2:D13;B2:B13) Помоему проще и для понимания и для реализации. Ссылка
Николай Павлов
26.05.2013 09:50:32
Спасибо за уточнение, Антон! ПРОСМОТР — тоже вариант в некоторых случаях. Родитель Ссылка
Артур Манукян
26.05.2013 14:47:05
Добрый день всем! Это мой первый комментарий. В первую очередь хотел бы поблагодарить Николая, за его труд, за этот сайт. Все очень доходчиво, структурировано и очень полезно в повседневной работе. Данный ресурс у меня на первом месте во вкладках по эксель! Ну, а теперь по вопросу если можно, касаемо функции индекс, которая применяется в данном примере. Скажите пожалуйста, а как быть в случае нахождения таблицы в другом соседнем листе. Метод указанный выше работает ровно до 3-го поля функции индекс, где надо указать искомый столбец в виде цифры, откуда мы забираем значение (имя клиента, регион и т.д.) Как корректно выполнить этот этап, чтобы забрать эти значения из соседнего листа? Заранее благодарю за помощь! Ссылка
Николай Павлов
30.05.2013 13:11:27
Указать имя листа и адрес ячейки, откуда брать номер столбца, типа Лист1!A1 — не помогает? Родитель Ссылка
Андрей
31.08.2014 00:07:53
Не всегда помогает. Сегодня весь день убил на реализацию этого метода. Все в толк не возьму — или криво офис на комп встал… или одно из двух…то #ссылку возвращает то #н/д. =ИНДЕКС(Диллеры!$A$4:$B$103;C3;2) на одном листе заработало после милионной попытки на другом листе вообще не пашет. почему на первом заработало — непонятно. Просто в какой то момент выдало нужный результат и все. Хотя ничего не трогал в формуле. Если вставлять ПОИСКПОЗ вообще никак не отрабатывает. Функция по потенциалу понравилась, но как отрабатывает конкретно у меня — нет. ВПР отрабатывает на отлично, но только на одном листе. С другого тоже не хочет хоть разбейся. Очень понравился Ваш ресурс. Подчерпнул. Спасибо Вам. Родитель Ссылка
Андрей
17.09.2014 12:51:13
хм. таки поборол. отличная функция! так упростила мне работу!!! ОГРОМНОЕ ВАМ НИКОЛАЙ СПАСИБО!!! Родитель Ссылка
elena farafontova
30.05.2013 11:44:06
Приветствую! Как выполнить поиск в диапазоне одновременно по двум позициям? Т.е. если на примере в теме Поиска данных в диапазоне известен регион и желаемая цена(приблизительно), а нужно найти и вывести в ячейку количество. ИНДЕКС И ПОИСКПОЗ имеют по одному значению Заранее благодарю) Ссылка
Николай Павлов
30.05.2013 13:07:05
Если искать точно, то можно просто предварительно объединить два столбца в один с помощью функции СЦЕПИТЬ, чтобы получить один столбец для проверки условий. Если нужно искать приблизительно, то простого решения нет. Родитель Ссылка
elena farafontova
02.06.2013 13:54:36
Благодарю Николая за невероятную комбинацию функций ВПР; СМЕЩ; ПОИСКПОЗ; СЧЁТЕСЛИ, которая подарила мне уйму свободного времени. Очень грамотно.8) Ссылка
Василий Ериклинцев
07.08.2013 10:28:25
Николай, добрый день! отличная формула очень часто ее использую однако столкнулся с небольшой проблемой, подставляемые данные вытаскиваю из другого файла, т.е. в формуле у меня стоит ссылка на другой файл. И очень часто когда открываешь файл с формулой ИНДЕКС(ПОИСКПОЗ…) он покрывается ссылками, лечиться только открытием файла на который стоит ссылка в формуле. Не критично конечно но иногда очень не удобно. Это можно как нибудь вылечить? Ссылка
Людмила Гобова
09.08.2013 13:42:49
Здравствуйте, Николай! У меня такая задача. В документе три страницы с информацией (3 класса: А, Б, В), в каждом из которых разное количество учащихся. На 4 странице, в протоколе, мне нужно отобразить информацию отдельно о каждом из учеников, который учится в одном из трех классов. Как через функцию «индекс», по номеру учащегося и классу получить информацию. Т.е. меня интересует второй вариант использования функции «индекс», как производить поиск в нескольких таблицах, как правильно записать формулу. С уважением, Людмила. Ссылка
acheslav
18.09.2013 21:48:36
Николай, большое спасибо за Ваши уроки! Просмотрев этот урок и скачав Ваш пример, нашёл решение своих задач. В частности вместо указания номера столбца вставил ПОИСКПОЗ =ИНДЕКС(A1:G13;ПОИСКПОЗ(C16;D1:D13;0);2) =ИНДЕКС(A1:G13;ПОИСКПОЗ(C16;D1:D13;0);ПОИСКПОЗ(B17;A1:G1;0)) Ещё раз благодарю Вас! С уважением, Вячеслав! Ссылка
Николай Павлов
21.12.2013 09:54:48
Ну да, хорошее решение, чтобы не считать номер колонки вручную Родитель Ссылка
Антон Золотухин
11.08.2014 09:44:33
Здравствуйте, а если шапка таблицы многослойная решение есть? Многослойная шапка — например в строке 2 условия в столбце 2 условия а не по одному. т.е. =ИНДЕКС(Диапазон значений таблицы; ПОИСКПОЗ(значение шапки столбца А;диапазон шапки столбца А;0); ПОИСКПОЗ(значение шапки строки 1;диапазон шапки столбца 1;0); а мне надо еще 2 условия добавить ПОИСКПОЗ(значение шапки столбца B;диапазон шапки столбца B;0); ПОИСКПОЗ(значение шапки строки 2;диапазон шапки столбца 2;0); т.е. значение готовое выберется не по 2-м условиям а по четырем Подскажите пожалуйста как это реализовать в одной формуле. Спасибо! Родитель Ссылка
Николай Павлов
11.08.2014 10:01:17
Антон, как вам ответить не видя вашего файла? Создайте лучше тему на форуме, приложите файлик — поможем. Родитель Ссылка
Антон Золотухин
11.08.2014 10:36:48
Вы так быстро ответили , что я таблицу неуспел нарисовать )) =ИНДЕКС(Диапазон значений таблицы; ПОИСКПОЗ(значение шапки столбца А;диапазон шапки столбца А;0); ПОИСКПОЗ(значение шапки строки 1;диапазон шапки столбца 1;0);
а мне надо еще 2 условия добавить ПОИСКПОЗ(значение шапки столбца B;диапазон шапки столбца B;0); ПОИСКПОЗ(значение шапки строки 2;диапазон шапки столбца 2;0); т.е. значение готовое выберется не по 2-м условиям а по четырем
Родитель Ссылка
Николай Павлов
11.08.2014 16:03:42
Антон, по такой картинке качественно ответить нереально. Я бы склеил условия из шапки попарно с помощью функции СЦЕПИТЬ и получил бы в итоге одно условие, по которому бы и делал обычный поиск. Лучше сделайте тему на форуме и приложите нормальный файл с примером, тогда ответ будет точнее Родитель Ссылка
Sayana
09.12.2013 09:14:03
Здравствуйте! Подскажите пожалуйста, а как мне вывести цену на товар из всплывающего списка, если прайс-лист и его цены у меня находятся на листе1, а вывести надо на лист3? Ссылка
Николай Павлов
21.12.2013 09:54:07
Все будет точно так же, как в примере — только диапазоны будете во время ввода формулы выделять на разных листах. Родитель Ссылка
Наталия Никулина
03.04.2014 10:47:52
Подскажите, пожалуйста, если я осуществляю поиск по двум признакам, которые связаны в таблице при помощи функции Связка и у меня находят данные на одно значение выше, чем искомое, то где ошибка? Ссылка
Николай Павлов
08.05.2014 10:23:14
Проверьте выделение диапазонов в формуле. Где-то на одну ячейку больше, например, шапку зацепили и т.д. Родитель Ссылка
atas
08.05.2014 12:45:27
Красота! Большое спасибо автору. ПОИСКПОЗ ищет первое значение слева, а мне нужно ( в строке есть пустые ячейки ) найти крайнее правое. Вопрос: КАК? Ссылка
atas
08.05.2014 15:03:43
Сам себе и отвечу . =ИНДЕКС(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). А как это записать формулой? Ссылка
Николай Павлов
14.05.2014 15:01:07
Приблизительное соответствие нужно совсем для другого (округления в нужную сторону при поиске числовых, а не текстовых значений).
D1:D13 — это столбец, а не строка Если вы имели ввиду вопрос «как сделать так, чтобы формула находила не первое встретившееся, а последнее значение», то тут проще всего макросом, наверное — писать на VBA функцию аналогичную ВПР.
Родитель Ссылка
magrifa
24.05.2014 11:02:43
Николай, формула ИНДЕКС хорошая, но слева можно найти и с помощью функции ВПР. В вашем примере, формула =ВПР(C16;ВЫБОР({1;2};$D$2:$D$13;$B$2:$B$13);2;0) сделает то-же самое. Может кому пригодится для развития познаний. Ссылка
Леонид Ерофеев
22.08.2014 12:19:25
Николай, во-первых, хочу сказать Вам огромное спасибо за ваш труд и за ту бесценную информацию, которую Вы двигаете в массы!!! Думаю несколько универсализировал формулу в примере (не понимаю как можно прикладывать файлы к сообщению…?): яч. E16 =ИНДЕКС($A$2:$G$13; ПОИСКПОЗ($C$15; $D$2:$D$13; 0); ПОИСКПОЗ(D16; $A$1:$G$1; 0)) Потом просто растягиваем. Но, чтобы это работало нужно предварительно задать списки данных для массива D15:D18 — это тоже делает отчет удобнее. Теперь можно «играться» с разными значениями, просто выбирая их из выпадающего списка. Спасибо! Ссылка
Наталья Антонова
25.08.2014 14:23:08
Подскажите, можно ли с помощью ВПР в массиве данных получить выборку по дате поступлений по контрагентам. т е есть клиенты, которые платят в течение года, когда 2 раза в месяц, когда раз в три месяца. можно ли получить данные, где будет видно, в какие даты приходит оплата от клиента? Ссылка
Hovik Ghambaryan
25.11.2014 13:02:57
здравствуйте у меня тоже проблема по этой тему, дело в том что нужно искать в не большой база данных двух совпадение, Москва в одном ячейке а 002 рядом и ест лист в котором ест тоже в одном Москва а рядом 002,просто там ест много 002 и Москва, но я хочу во первых формула нашла именно ту Москву с которым рядом ест 002 и второй в первом листе рядом Москва и 002 пишется 10 , а втором листе рядом Москва 002 ест 5 надо чтобы формула нашла эти цифры и умножала их очень прошу помогите у меня уже голова болит от этого но ничего не получается Ссылка
Анастасия Литвиненко
16.01.2015 08:14:14
Здравствуйте, у меня следующая проблема: нужно выбрать из таблицы числа с определенным значением и суммировать их в привязке к определенному месяцу, т.е есть несколько строк с одинаковым признаком, но в разные даты и должна получиться одна итоговая сумма по месяцу, но сами числа привязаны к различным датам которые забиты в таблице в кратком формате даты (19.10.2014, 15.11.2014 и т.п). Ссылка
Walkmax
17.01.2015 16:19:30
Николай, здравствуйте В очередной раз встал перед задачей предположительно имеющей простое решение, но … Как реализовать выбор из таблицы оперируя двумя вводными, т.е. например выбрать значение которое соответствует определённому сочетанию значений из двух других столбцов при условии нахождения всех трёх (двух исходных и искомого) в одной строке ИНДЕКС(; ПОИСКПОЗ( позволяет оперировать только одним столбцом или есть варианты? Ссылка
Екатерина Д
11.03.2015 16:43:02
Николай, здравствуйте. Подскажите, возможно ли с помощью данных функций осуществить следующее: имеется файл с 13 листами (12 из них имеют название месяцев и там содержаться соответствующие данные за этот месяц), а 13 итоговый с фильтром, при помощи которого можно задать диапазон месяцев (например, с мая по сентябрь или с января по ноябрь). На каждом листе имеются одинаковые по структуре таблицы (например указание объектов в строках и статей расходов в столбцах). В 13 итоговом листе содержится формула суммирования данных с других листов (идентичных по адресу ячеек) с учетом выбранных условий фильтра. Помогите, пожалуйста, с написанием этой формулы. Ссылка
Вальдемар Пе
12.03.2015 12:56:40
Афтар молодец! Куплю электронную книгу дабы поддержать энтузиазм Ссылка
Руслан Сиразетдинов
27.08.2015 16:19:30
Добрый день, Николай. Функция ПОИСКПОЗ просматривает массив сверху вниз и, соответственно, возвращает первый порядковый номер аргумента:
Пример (скрин) прилагается. Бьюсь несколько дней, но никак не получается, чтобы найти функцию, указывающую последний порядковый номер соответствующего аргумента в массиве. В нашем примере это «5». Подскажите, пожалуйста, функцию для решения этой задачи. Заранее спасибо! Ссылка
Greg M
17.10.2015 02:26:39
Подскажите пож-та как функция ПОИСКПОЗ
может искать данные которые начинаются с определенных символов , но эти символы располагаются в определенном столбце . Т.е. в формулу нужно вбить не сами эти символы с которых функция ищет , а именно ячейку нужно вбить в формулу . Какой синтаксис в этом случае нужно использовать? Ссылка
Vl Sh
11.11.2015 15:20:24
очень важный урок вопрос такой: — есть прайс, ?: нужно выбрать (найти) соответствие цены товара из диапазона допустимых цен товаров чтобы цену товара обозначить соответствующим именем Как это сделать? Ссылка
Ольга
06.02.2016 12:44:07
Добрый день! Подскажите, пожалуйста, есть ли возможность искать не в диапазоне, а в некоторых ячейках? Ситуация следующая: у меня есть лист с данными и сводная, которая подтягивает максимальное значение по данным (не сквозной диапазон, а набор ячеек). Теперь мне необходимо понять какое текстовое значение соотносится с этим максимальным значением (соответственно из набора ячеек). Усложняется задача тем, что такое максимальное значение может встречаться не в одной строке… Соответственно используя данный пример: У меня есть артикул = 15/02/16 — это максимальная дата, которая выбрана из строк 5,7 и 9 одного из столбцов. Далее мне нужно понять какой регион соответствует этой максимальной дате (артикулу), соответственно в этих же строках 5,7 и 9, но другого столбца. Логично предположить, что формула выберет первое значение, которое удовлетворит условию, но если в строках 5 и 7 стоит дата 15/02/16, как прописать, чтобы оба текстовых значения попадали, а строка 9 с датой 08/02 нет… Спасибо! Ссылка
Александра Михайленко
16.03.2016 11:40:15
Подскажите, можно к индекс поиск поз, добавить гиперссылку…чтобы ссылаться на найденное значение? Ссылка
Сергей
16.06.2016 09:31:03
Добрый день. Подскажите как можно более оптимально решить след. задачу: есть таблица, из 2-х столбцов: № договора/сумма оплаты. по одному договору бывает несколько оплат. И есть другая таблица( форма отчета) в которой эти договоры внесены в произвольном порядке. Необходимо из первой таблицы сделать выборку по договорам, и внести оплаты во 2-ю таблицу. С этим справился с помощью «ВПР», но не знаю как быть когда по одному договору несколько сумм в первой таблице. как их сразу просуммировать? Ссылка
Екатерина Скибина
18.06.2016 18:45:58
Николай, спасибо вам огромное! Подскажите, каким образом можно находить данные по 2м критериям, если один из критериев не точный, а приблизительный (например, если критерий дата +- день)? Ссылка
Рома Аалігатор
13.08.2016 21:04:29
Доброго времени суток уважаемый Николай, можна вам по формуле, как сделать так чтоб в списке ексель , привожу пример ((((((ПЕТРОВ ИВАНОВ ИВАНЦОВ ПЕТРОВ))))))))))), если повторяется имя пару рас- разные имена , чтоб через пару пустих ячеек имено повторяющееся имена вставлялись каждое в адельную ячейку,,,ЗАРАНИЕ БЛАГОДАРЮ,(извените за мой руский) Ссылка
Дмитрий Голубев
29.09.2016 14:17:48
Добрый день ! Как реализовать функции ИНДЕКС и ПОИСКПОЗ
в VBA ? Ссылка
Володимр Бухонський
11.10.2016 11:19:30
а если в столбце два одинаковых артикула, а остальные (регион и клиент) разные? Ссылка
Эльмира Хафизова
06.03.2017 17:20:06
При использовании этой формулы в работе с датами, выдает результат 0.1.1900 (
при пустой исходной ячейки) и #Н/Д(в случаях пустых всех заданных диапазонов)
Какую формулу можно дописать, чтобы при отсутствии исходных данных выдавал пусто, вместо самой первой даты в экселе?
P/S На функцию
еслиошибка
реагирует только #Н/Д, а пустую ячейку все равно выдает как
0.1.1900
СПАСИБО:{} Ссылка
Сергей Белый
24.03.2017 18:42:29
Добрый день! Помогите пожалуйста по стоить формулу. — есть таблица с данными: список товаров и столбцы магазинов с оборотами по ним -среднее выводиться с отдельную ячейку С69 задача выводить рядом со средним какой товар = среднему значению и рядом какой магазин =ИНДЕКС(B3:B61;ПОИСКПОЗ(C69;C3:C61;0)) B3:B61= это товары , С69 искомое значение , C3:C61= столбец магазинов где ищет.(НО ИХ 20) Проблема в том, что выводиться только по одной колонке в формуле, а необходимо искать по всем 20. Ссылка
Ярослав Чикал
11.07.2017 19:22:44
Не получается! Бред какой-то получается! Посмотрите, плиз! Может это у меня только так? Дайте вашу почту, файл отправлю. Ссылка
muflic
07.08.2017 12:39:06
Добрый день, пытаюсь решить следующую задачу: Есть сотрудники и даты. Я сверяю из одной таблицы фамилию сотрудника и дату. и хочу забрать значение на пересечении в другую таблицу на переселении фамилии и даты. т.е. нужно сравнить если дата и фамили совпадает то забираем значение в другую на место такого же совпадение. Ссылка
Александр Янченко
25.08.2017 10:01:01
Здравствуйте. А как сделать чтобы у меня выходило значение из двух столбцов? Если поподробнее то регион и например улица Ссылка
Sergey Semyannikov
06.02.2018 16:14:28
Добрый день! Простите, если глупость спрашиваю, но как из столбца цифр выбрать (просуммировать) только те, которые одновременно больше например, 10, но меньше 20. Т.е. как-то так: СУММЕСЛИ(А2:А30; И(«>=10»; «<20») )
Понимаю, что написанное красным неправильно… подскажите как одновременно учесть оба условия. СУММЕСЛИМН тоже лишь перечисляет критерии, выбирая сначала все числа больше 10, а потом ещё все меньше 20, а нужно только числа от 10 до 20. Ссылка
Максим Любимов
07.04.2018 14:56:15
А какую функцию использовать, если надо найти значение не в столбце, а массиве, состоящем из нескольких столбцов? Ссылка
Сергей
18.07.2018 12:59:04
Уважаемые знатоки, требуется Ваша помощь. Задача такая: Есть четыре Списка по выручке (т.е. четыре листа в одном файле «выручка1» «выручка2″…»выручка4») На пятом листе «общая выручка» необходимо все эти данные собрать в один список с привязкой к каждой конкретной дате, при этом каждое наименование выручки относится к своему столбцу (полю) а сумма складывается в один общий столбец. Буду очень благодарен за совет !!!! ) Ссылка
Людмила Гавриловская
26.07.2018 13:50:23
Большое спасибо! Очень помогли! Ссылка
Юрий Владимиров
30.08.2018 11:41:34
Добрый день. Подскажите пожалуйста, если у меня все данные в одной строке, циклично, затем в конце я выбираю значение по формуле «=МИН» и в соседней колонке хочу указать что рядом с той ячейкой в которой нашла по формуле «=МИН» Ссылка
Светлана
17.07.2019 10:58:42
Большое вам человеческое СПАСИБО! Давно пользуюсь индексом и поиском позиции, но просто скопировав из чужого примера, меняя ссылки на ячейки, т.к. вообще не понимала как эти функции работают, а встроенная справка в Excel не дает понятной информации. С помощью вашего примера разобралась с этой функцией: это так легко, просто и безумно полезно!!! Ссылка
Василь Максутов
06.08.2019 10:06:59
Добрый день, скажите пожалуйста а как сделать поиск значения не снизу вверх по массиву, а наоборот начинать поиск сверху? Ссылка
Максим Карась
17.12.2019 23:12:01
Добрый день. Подскажите пожалуйста как решить следующую задачу. Есть столбец с уникальными номерами: 1 2 4 И есть таблица 1 с такими же номерами, а так же значениями: 1 | 01/02/19 | 25 1 | 15/02/19 | 15 2 | 01/02/19 | 25 2 | 12/02/19 | 15 2 | 15/02/19 | 15 3 | 01/02/19 | 25 3 | 15/02/19 | 15 4 | 01/02/19 | 25 4 | 15/02/19 | 15 С помощью какой формулы или метода можно создать таблицу из значений таблицы 1 с соответствующими уникальными номерами из столбца? Ссылка
Сергей Микрюков
02.02.2020 00:25:08
Доброго времени суток! А как быть, если нужно найти максимальное значение?
{=ПОИСКПОЗ(«1″&»1″&»1»;&[А]&[В]&[Г];0)} — ПОИСКПОЗ находит первое значение Ссылка
Ирек Галиев
17.02.2020 00:29:51
Доброго! Подскажите пожалуйста, как с помощью функции ПОИСКПОЗ определить номер строки excel? Ссылка
Василий Горохов
14.03.2020 11:38:11
Николай доброго времени суток!!! Прошу подсказать как найти все уникальные значения и объединить их как текст в одной ячейке по типу изделия.
исходная таблица
и т.д. Ссылка
Александр И
07.09.2020 11:33:26
Николай добрый день! Ситуация: в одном столбце друг за другом чередуются данные «что отправить», «когда» и «кому». Скажите пожалуйста, есть ли в Excel инструмент, который позволяет эти данные выбрать и поставить в аналогичные три колонки? Ссылка
Виталий
22.09.2020 14:50:39
Добрый день. вопрос не получается найти значение через эти формулы. может тогда подскажете решение? Есть строка (не столбец) значений: 2030, 2000, 2050, 2100, 2000. среднее значение их 2036 мне нужно рядом с ними сделать выборку значения максимально приближенного к среднему значению — и этим значением является 2030 ИНДЕКС+ПОИСКПОЗ — находит почему-то только 2000 (при значении»+1″) при замене на «-1» — выдает Н/Д 0 — даже не ставлю, т.к. точного значения в строке нет важно именно делать формулой, так как ряд чисел постоянно меняется спасибо Ссылка
Страницы:
1) Классический поиск (обыкновенный).
Вызвать панель (меню) поиска можно сочетанием горячих клавиш ctrl+F. (Легко запомнить: F- Found).
Окно поиска состоит из поля, в которое вводится искомый фрагмент текста или искомое число, вкладки с дополнительными настройками («Параметры») и кнопки «Найти».
Классический поиск в Excel
В параметрах поиска можно указать, где искать текст, искать ли слово в ячейке целиком или вхождение слова в предложения, учитывать ли регистр или нет.
Условное форматирование для искомых ячеек.
Способ 1
Самый простой способ — выполнить поиск. Для этого можно нажать клавиатурную комбинацию CTRL + F (от англ. Find), откроется окно поиска слов.
Для нажатия клавиатурной комбинации, нажмите клавишу клавиатуры CTRL и, удерживая ее, нажмите клавишу F (на английский язык переходить не нужно).
Вместо клавиатурной комбинации можно использовать кнопку поиска на панели Главная — Найти и выделить — Найти.
По умолчанию открывается маленькое окно, в которое нужно вписать искомое слово и нажать клавишу Найти все или Найти далее.
- Найти все — выполнит поиск всех совпадений с указанной фразой. В окне ниже появится список, в котором будет указана фраза, содержащая искомые символы, а также место в документе, где символы были найдены.
Для удобства работы это окно можно увеличить, потянув за маркер в правом нижнем углу.
Также можно сделать шире столбцы: Книга, Лист, Имя и т.д., потянув за маркеры между названиями столбцов.
В столбце Значение можно видеть полный текст ячейки, в котором есть искомые символы (в нашем примере — excel). Чтобы перейти к этому месту в таблице просто нажмите левой кнопкой мыши на нужную строку, и курсор автоматически переместится в выбранную ячейку таблицы.
- Найти далее — выделит и перейдет к следующей ячейке, в которой найдены искомые символы. Таким образом можно последовательно переходить от одной найденной ячейке к другой.
3) Третий способ поиска слов в таблице «Excel» — это использование фильтров.
Фильтр устанавливается во вкладке «Данные» или сочетанием клавиш ctrl+shift+L.
Настройка фильтра для поиска слов
Кликнув по треугольнику фильтра можно в контекстном меню выбрать пункт «Текстовые фильтры», далее «содержит…» и указать искомое слово.
После нажатия кнопки «Ок» на Экране останутся только ячейки столбца, содержащие искомое слово.
Функция ПОИСКПОЗ в Excel – синтаксис и использование
Функция ПОИСКПОЗ в Excel ищет заданное значение в диапазоне ячеек и возвращает относительное положение этого значения.
Синтаксис формулы ПОИСКПОЗ выглядит следующим образом:
ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])
Искомое_значение (обязательный аргумент) – значение, которое вы хотите найти. Это может быть числовое, текстовое или логическое значение, а также ссылка на ячейку.
Просматриваемый_массив (обязательный аргумент) – диапазон ячеек для поиска.
Тип_сопоставления (необязательный аргумент) – определяет тип соответствия. Это может быть одно из следующих значений: 1, 0, -1.
- 1 или отсутствует (по умолчанию) – находит наибольшее значение в массиве, которое меньше или равно значению поиска. Массив поиска должен быть отсортирован в порядке возрастания, от самого маленького до большого или от A до Z.
- 0 – находит первое значение в массиве, которое в точности равно значению поиска. Сортировка не требуется.
- -1 – находит наименьшее значение в массиве, которое больше или равно значению поиска. Массив поиска должен быть отсортирован в порядке убывания, от самого большого до самого маленького или от Z до A.
Чтобы лучше разобраться в данной функции, давайте напишем простую формулу ПОИСКПОЗ, основанную на следующих данных: фамилии студентов в столбце A и их баллы по экзаменам в столбце B, отсортированные от самых высоких до самых низких. Чтобы узнать, где среди других находится конкретный студент (например, студентка Виноградова), используйте эту простую формулу:
=ПОИСКПОЗ(E1; A2:A8; 0)
При желании вы можете поместить значение поиска в ячейку (E1 в данном примере) и сослаться на эту ячейку в формуле ПОИСКПОЗ Excel:
Функция ПОИСКПОЗ в Excel – Пример использования функции ПОИСКПОЗ в Excel
Как вы видите на изображении выше, фамилии студентов вводятся в произвольном порядке, поэтому мы устанавливаем аргумент тип_сопоставления равным 0 (точное совпадение), поскольку только этот тип соответствия не требует сортировки значений в массиве поиска. Технически формула ПОИСКПОЗ возвращает относительное положение студентки Виноградовой в исследуемом диапазоне. Но поскольку оценки сортируются от самых высоких до самых низких, это также говорит нам о том, что Виноградова пятая по счету среди всех учеников.
Особенности функции ПОИСКПОЗ Excel
Как вы только что видели, использовать ПОИСКПОЗ в Excel легко. Однако, как и у любой функции Excel, у нее есть несколько особенностей, о которых следует знать:
- Функция ПОИСКПОЗ возвращает относительное положение значения поиска в массиве, а не самого значения.
- ПОИСКПОЗ не зависит от регистра, что означает, что она не различает строчные и прописные символы при работе с текстовыми значениями.
- Если массив поиска содержит несколько вхождений искомого значения, возвращается позиция первого значения.
- Если искомое значение не найдено в просматриваемом массиве, возвращается ошибка #Н/Д.
4) Способ поиска номер четыре — это макрос VBA для поиска (перебора значений).
В зависимости от назначения и условий использования макрос может иметь разные конфигурации, но основная часть цикла перебора VBA макроса приведена ниже.
Sub Poisk()
‘ ruexcel.ru макрос проверки значений (поиска)
Dim keyword As String
keyword = «Искомое слово» ‘присвоить переменной искомое слово
On Error Resume Next ‘при ошибке пропустить
For Each cell In Selection ‘для всх ячеек в выделении (выделенном диапазоне)
If cell.Value = «» Then GoTo Line1 ‘если ячейка пустая перейти на «Line1″
If InStr(StrConv(cell.Value, vbLowerCase), keyword) > 0 Then cell.Interior.Color = vbRed ‘если в ячейке содержится слово окрасить ее в красный цвет (поиск)
Line1:
Next cell
End Sub
Различия
Проанализировав результаты, полученные при использовании инструментов ПОИСК и НАЙТИ, можно выделить несколько ключевых отличий:
На заметку! Чтобы найти именно эти символы в тексте необходимо использовать тильду (~) перед аргументом.
Как видите, excel позволяет осуществлять поиск несколькими способами, каждый из которых обладает своими преимуществами. При помощи одного можно осуществить поиск по нескольким значениям, а другой можно вызвать используя горячие клавиши, что, несомненно, ускоряет процесс работы с документом.
Поиск позиции с использованием подстановочных знаков
Если искомое значение точно не известно, то с помощью подстановочных знаков можно задать поиск по шаблону, т.е. искомое_значение может содержать знаки шаблона: звездочку (*) и знак вопроса (?). Звездочка соответствует любой последовательности знаков, знак вопроса соответствует любому одиночному знаку.
Предположим, что имеется перечень товаров и мы не знаем точно как записана товарная позиция относящаяся к яблокам: яблоки или яблоко .
В качестве критерия можно задать «яблок*» и формула =ПОИСКПОЗ(«яблок*»;B53:B62;0) вернет позицию текстового значения, начинающегося со слова яблок (если она есть в списке).
Подстановочные знаки следует использовать только для поиска позиции текстовых значений и Типом сопоставления = 0 (третий аргумент функции).