Поиск значения в столбце и строке таблицы Excel

Как выполнить поиск значений в программе «Excel» .

Поиск в Эксель
Далее описаны несколько вариантов поиска и фильтрации данных в таблице «Эксель».

1. Классический поиск «MS Office».

2. Условное форматирование (выделение нужных ячеек цветом)

3. Настройка фильтров по одному или нескольким значениям.

4. Фрагмент макроса для перебора ячеек в диапазоне и поиска нужного значения.

Поиск нужных данных в диапазоне

15124 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 — даже не ставлю, т.к. точного значения в строке нет важно именно делать формулой, так как ряд чисел постоянно меняется спасибо Ссылка

Anna Matushkina

01.10.2020 11:26:14

Не могу понять, какую формулу лучше применить, если есть даты начала и окончания периодов проекта, мне надо выбрать период по функции СЕГОДНЯ() и на другой лист поставить название этого проекта… Можно ли как-то в этом случае использовать ВПР или тут совсем другой способ? Ссылка

Страницы:

1) Классический поиск (обыкновенный).

Вызвать панель (меню) поиска можно сочетанием горячих клавиш ctrl+F. (Легко запомнить: F- Found).

Окно поиска состоит из поля, в которое вводится искомый фрагмент текста или искомое число, вкладки с дополнительными настройками («Параметры») и кнопки «Найти».

Классический поиск в Excel

В параметрах поиска можно указать, где искать текст, искать ли слово в ячейке целиком или вхождение слова в предложения, учитывать ли регистр или нет.

Условное форматирование для искомых ячеек.

Дополнительные параметры поиска слов и фраз

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

Здесь можно указать дополнительные параметры поиска.

Искать:

• на листе — только на текущем листе;
• в книге — искать во всем документе Excel, если он состоит из нескольких листов.

Просматривать:

• по строкам — искомая фраза будет искаться слева направо от одной строки к другой;
• по столбцам — искомая фраза будет искаться сверху вниз от одного столбца к другому.

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

Область поиска — определяет, где именно нужно искать совпадения:

• в формулах;
• в значениях ячеек (уже вычисленные по формулам значения);
• в примечаниях, оставленных пользователями к ячейкам.

А также дополнительные параметры:

• Учитывать регистр — означает, что заглавные и маленькие буквы будут считаться как разные.

Например, если не учитывать регистр, то по запросу «excel» будет найдены все вариации этого слова, например, Excel, EXCEL, ExCeL и т.д.

Если поставить галочку учитывать регистр, то по запросу «excel» будет найдено только такое написание слова и не будет найдено слово «Excel».

• Ячейка целиком — галочку нужно ставить в том случае, если нужно найти те ячейки, в которых искомая фраза находится целиком и нет других символов. Например, есть таблица со множеством ячеек, содержащих различные числа. Поисковый запрос: «200». Если не ставить галочку ячейка целиком, то будут найдены все числа, содержащие 200, например: 2000, 1200, 11200 и т.д. Чтобы найти ячейки только с «200», нужно поставить галочку ячейка целиком. Тогда будут показаны только те, где точное совпадение с «200».
• Формат… — если задать формат, то будут найдены только те ячейки, в которых есть искомый набор символов и ячейки имеют заданный формат (границы ячейки, выравнивание в ячейке и т.д.). Например, можно найти все желтые ячейки, содержащие искомые символы.

Формат для поиска можно задать самому, а можно выбрать из ячейки-образца — Выбрать формат из ячейки…

Чтобы сбросить настройки формата для поиска нужно нажать Очистить формат поиска.

Это меню вызывается, если нажать на стрелочку в правой части кнопки Формат.

3) Третий способ поиска слов в таблице «Excel» — это использование фильтров.

Фильтр устанавливается во вкладке «Данные» или сочетанием клавиш ctrl+shift+L.

Настройка фильтра для поиска слов

Кликнув по треугольнику фильтра можно в контекстном меню выбрать пункт «Текстовые фильтры», далее «содержит…» и указать искомое слово.

После нажатия кнопки «Ок» на Экране останутся только ячейки столбца, содержащие искомое слово.

Поиск позиции в массивах с текстовыми значениями

Произведем поиск позиции в НЕ сортированном списке текстовых значений (диапазон B7:B13 )

Столбец Позиция приведен для наглядности и не влияет на вычисления.

Формула для поиска позиции значения Груши: =ПОИСКПОЗ(«груши»;B7:B13;0)

Формула находит первое значение сверху и выводит его позицию в диапазоне, второе значение Груши учтено не будет.

Чтобы найти номер строки, а не позиции в искомом диапазоне, можно записать следующую формулу: =ПОИСКПОЗ(«груши»;B7:B13;0)+СТРОКА($B$6)

Если искомое значение не обнаружено в списке, то будет возвращено значение ошибки #Н/Д. Например, формула =ПОИСКПОЗ(«грейпфрут»;B7:B13;0) вернет ошибку, т.к. значения «грейпфрут» в диапазоне ячеек B7:B13 нет.

В файле примера можно найти применение функции при поиске в горизонтальном массиве.

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