Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).
Описание
В этой статье приведены пошаговые инструкции по поиску данных в таблице (или диапазоне ячеек) с помощью различных встроенных функций Microsoft Excel. Для получения одного и того же результата можно использовать разные формулы.
Создание образца листа
В этой статье используется образец листа для иллюстрации встроенных функций Excel. Рассматривайте пример ссылки на имя из столбца A и возвращает возраст этого человека из столбца C. Чтобы создать этот лист, введите указанные ниже данные в пустой лист Excel.
Введите значение, которое вы хотите найти, в ячейку E2. Вы можете ввести формулу в любую пустую ячейку на том же листе.
|
A |
B |
C |
D |
E |
||
|
1 |
Имя |
Правитель |
Возраст |
Поиск значения |
||
|
2 |
Анри |
501 |
Плот |
Иванов |
||
|
3 |
Стэн |
201 |
19 |
|||
|
4 |
Иванов |
101 |
максималь |
|||
|
5 |
Ларри |
301 |
составляет |
Определения терминов
В этой статье для описания встроенных функций Excel используются указанные ниже условия.
|
Термин |
Определение |
Пример |
|
Массив таблиц |
Вся таблица подстановки |
A2: C5 |
|
Превышающ |
Значение, которое будет найдено в первом столбце аргумента «инфо_таблица». |
E2 |
|
Просматриваемый_массив |
Диапазон ячеек, которые содержат возможные значения подстановки. |
A2: A5 |
|
Номер_столбца |
Номер столбца в аргументе инфо_таблица, для которого должно быть возвращено совпадающее значение. |
3 (третий столбец в инфо_таблица) |
|
Ресулт_аррай |
Диапазон, содержащий только одну строку или один столбец. Он должен быть такого же размера, что и просматриваемый_массив или Лукуп_вектор. |
C2: C5 |
|
Интервальный_просмотр |
Логическое значение (истина или ложь). Если указано значение истина или опущено, возвращается приближенное соответствие. Если задано значение FALSE, оно будет искать точное совпадение. |
ЛОЖЬ |
|
Топ_целл |
Это ссылка, на основе которой вы хотите основать смещение. Топ_целл должен ссылаться на ячейку или диапазон смежных ячеек. В противном случае функция СМЕЩ возвращает #VALUE! значение ошибки #ИМЯ?. |
|
|
Оффсет_кол |
Число столбцов, находящегося слева или справа от которых должна указываться верхняя левая ячейка результата. Например, значение «5» в качестве аргумента Оффсет_кол указывает на то, что верхняя левая ячейка ссылки состоит из пяти столбцов справа от ссылки. Оффсет_кол может быть положительным (то есть справа от начальной ссылки) или отрицательным (то есть слева от начальной ссылки). |
Функции
LOOKUP ()
Функция Просмотр находит значение в одной строке или столбце и сопоставляет его со значением в той же позицией в другой строке или столбце.
Ниже приведен пример синтаксиса формулы подСТАНОВКи.
= Просмотр (искомое_значение; Лукуп_вектор; Ресулт_вектор)
Следующая формула находит возраст Марии на листе «образец».
= ПРОСМОТР (E2; A2: A5; C2: C5)
Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в векторе подстановки (столбец A). Формула затем соответствует значению в той же строке в векторе результатов (столбец C). Так как «Мария» находится в строке 4, функция Просмотр возвращает значение из строки 4 в столбце C (22).
Примечание. Для функции Просмотр необходимо, чтобы таблица была отсортирована.
Чтобы получить дополнительные сведения о функции Просмотр , щелкните следующий номер статьи базы знаний Майкрософт:
Использование функции Просмотр в Excel
ВПР ()
Функция ВПР или вертикальный просмотр используется, если данные указаны в столбцах. Эта функция выполняет поиск значения в левом столбце и сопоставляет его с данными в указанном столбце в той же строке. Функцию ВПР можно использовать для поиска данных в отсортированных или несортированных таблицах. В следующем примере используется таблица с несортированными данными.
Ниже приведен пример синтаксиса формулы ВПР :
= ВПР (искомое_значение; инфо_таблица; номер_столбца; интервальный_просмотр)
Следующая формула находит возраст Марии на листе «образец».
= ВПР (E2; A2: C5; 3; ЛОЖЬ)
Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в левом столбце (столбец A). Формула затем совпадет со значением в той же строке в Колумн_индекс. В этом примере используется «3» в качестве Колумн_индекс (столбец C). Так как «Мария» находится в строке 4, функция ВПР возвращает значение из строки 4 В столбце C (22).
Чтобы получить дополнительные сведения о функции ВПР , щелкните следующий номер статьи базы знаний Майкрософт:
Как найти точное совпадение с помощью функций ВПР или ГПР
INDEX () и MATCH ()
Вы можете использовать функции индекс и ПОИСКПОЗ вместе, чтобы получить те же результаты, что и при использовании поиска или функции ВПР.
Ниже приведен пример синтаксиса, объединяющего индекс и Match для получения одинаковых результатов поиска и ВПР в предыдущих примерах:
= Индекс (инфо_таблица; MATCH (искомое_значение; просматриваемый_массив; 0); номер_столбца)
Следующая формула находит возраст Марии на листе «образец».
= ИНДЕКС (A2: C5; MATCH (E2; A2: A5; 0); 3)
Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в столбце A. Затем он будет соответствовать значению в той же строке в столбце C. Так как «Мария» находится в строке 4, формула возвращает значение из строки 4 в столбце C (22).
Обратите внимание Если ни одна из ячеек в аргументе «число» не соответствует искомому значению («Мария»), эта формула будет возвращать #N/А.
Чтобы получить дополнительные сведения о функции индекс , щелкните следующий номер статьи базы знаний Майкрософт:
Поиск данных в таблице с помощью функции индекс
СМЕЩ () и MATCH ()
Функции СМЕЩ и ПОИСКПОЗ можно использовать вместе, чтобы получить те же результаты, что и функции в предыдущем примере.
Ниже приведен пример синтаксиса, объединяющего смещение и сопоставление для достижения того же результата, что и функция Просмотр и ВПР.
= СМЕЩЕНИЕ (топ_целл, MATCH (искомое_значение; просматриваемый_массив; 0); Оффсет_кол)
Эта формула находит возраст Марии на листе «образец».
= СМЕЩЕНИЕ (A1; MATCH (E2; A2: A5; 0); 2)
Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в столбце A. Формула затем соответствует значению в той же строке, но двум столбцам справа (столбец C). Так как «Мария» находится в столбце A, формула возвращает значение в строке 4 в столбце C (22).
Чтобы получить дополнительные сведения о функции СМЕЩ , щелкните следующий номер статьи базы знаний Майкрософт:
Использование функции СМЕЩ
Нужна дополнительная помощь?
Нужны дополнительные параметры?
Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.
В сообществах можно задавать вопросы и отвечать на них, отправлять отзывы и консультироваться с экспертами разных профилей.
При работе с таблицами в Excel, особенно если эти таблицы очень большие и не помещаются на одном экране монитора, трудно визуально найти слово или фразу. Но в Excel есть специальная команда, которая позволяет выполнить поиск символов по всему тексту книги или на отдельных листах. Давайте рассмотрим пару способов, как можно это сделать.
Самый простой способ — выполнить поиск. Для этого можно нажать клавиатурную комбинацию CTRL +F (от англ. Find), откроется окно поиска слов.
Для нажатия клавиатурной комбинации, нажмите клавишу клавиатуры CTRL и, удерживая ее, нажмите клавишу F (на английский язык переходить не нужно).
Вместо клавиатурной комбинации можно использовать кнопку поиска на панели Главная — Найти и выделить — Найти.
По умолчанию открывается маленькое окно, в которое нужно вписать искомое слово и нажать клавишу Найти все или Найти далее.
- Найти все — выполнит поиск всех совпадений с указанной фразой. В окне ниже появится список, в котором будет указана фраза, содержащая искомые символы, а также место в документе, где символы были найдены.
Для удобства работы это окно можно увеличить, потянув за маркер в правом нижнем углу.
Также можно сделать шире столбцы: Книга, Лист, Имя и т.д., потянув за маркеры между названиями столбцов.
В столбце Значение можно видеть полный текст ячейки, в котором есть искомые символы (в нашем примере — excel). Чтобы перейти к этому месту в таблице просто нажмите левой кнопкой мыши на нужную строку, и курсор автоматически переместится в выбранную ячейку таблицы.
- Найти далее — выделит и перейдет к следующей ячейке, в которой найдены искомые символы. Таким образом можно последовательно переходить от одной найденной ячейке к другой.
Как в экселе найти нужное слово по ячейкам
Для отображения адресов тех ячеек, которые содержат то, что вы пытаетесь отыскать, следует придерживаться следующих шагов:
- Если вы являетесь пользователем программы 2010 года, стоит перейти к меню, после чего кликнуть по «Правке», и затем «Найти».
- Далее откроется окошко, в котором предстоит пропечатать искомую фразу.
- Программа предыдущей версии располагает данной кнопкой в меню под названием «Главная», расположенная на панели редактирования.
- Подобного же результата возможно достигать в любой из версий, одновременно воспользовавшись кнопками Ctrl, а также, F.
- В поле следует пропечатать фразу, искомые слова либо цифры.
- Нажав «Найти все», вы запустите поиск по абсолютно всему файлу. Кликнув «Далее», программа по одной клеточке, располагающихся под курсором-ячейкой файла, будет их выделять.
- Стоит подождать, пока процесс завершится. При этом чем объемнее документ, тем больше времени уйдет на поиск.
- Возникнет список результатов: имена и адреса клеточек, которые содержат в себе совпадения с указанным значением либо фразой.
- Кликнув на любую строчку, будет выделена соответствующая ячейка.
- С целью удобства, можно «растягивать» окно. Таким образом в нем будет виднеться больше строк.
- Для сортировки данных, необходимо кликать на названиях столбиков над найденными результатами. Нажав на «Лист», строки будут выстроены по алфавиту зависимо от наименования листа, а выбрав «Значения» — расположатся в зависимости от значения. К слову, данные столбики тоже можно «растянуть».
Способы фильтрации
Excel содержит в себе 3 способа текстовой фильтрации, иными словами, фильтровать можно по 3 критериям вхождения.
Ячейка содержит, начинается с, либо заканчивается на — какой то конкретной паттерн.
В нашем случае для фильтрации целого слова, когда слово содержится в середине строки — во всех таких случаях после слова и перед будут располагаться пробелы. Поэтому мы можем задать именно такой критерий фильтрации, (пример — «* слово *») . Пробелы являются тем самым ограничителем, который позволит ячейки, в которых слово середине фразы, а фильтровать полностью и исключить ситуаций, когда оно будет входить в другие слова. «Звездочки» нужны для того, чтобы сообщить Excel, что в строке действительно есть пробелы — по умолчанию он их просто убирает перед поиском, по-видимому, воспринимая их наличие как случайность.
Критерий номер 2. — Строка заканчивается на наше слово. В этом случае она будет заканчиваться наше слово, и перед ним обязательно должен быть пробел, допустим. У нас будет рот и в конце строки не может быть слово крот. Такая строка не должна фильтроваться. Нужно чтобы перед словом рот был пробел.
И критерий номер 3 — ячейка должна начинаться на наше слово — в таком случае пробел должен быть после нашего слова, по аналогичной причине.
Проблема заключается в том, что в Excel нельзя фильтровать сразу по 3 критериям — можно только по двум. Этой ситуации есть простой лайфхак:
1. Перед каждой строкой и после нее добавляем пробел (=» «&A1&» «)
2. После этого фильтруем по полученному столбцу уже наше слово с пробелами перед и после него и специальными «звездочками» перед и после этих пробелов соответственно (пример — «* слово *»), для того, чтобы отфильтровать слова, которое обязательно содержат перед ними и после них пробел. Это и будут ячейки, содержащие наше слово.
Решение задачи с помощью формулы
Однозначной формулой, возвращающей «ИСТИНА» или «ЛОЖЬ» для проверки наличия в строке целого слова, будет:
=ПОИСК(» «&B1&» «;» «&A1&» «)>0
Дополнительные параметры поиска слов и фраз
Когда таблица достаточно большая и нужно выполнить поиск по определенным параметрам, их можно задать в специальных настройках поиска. Нажмите кнопку Параметры.
Здесь можно указать дополнительные параметры поиска.
Искать:
- на листе — только на текущем листе;
- в книге — искать во всем документе Excel, если он состоит из нескольких листов.
Просматривать:
- по строкам — искомая фраза будет искаться слева направо от одной строки к другой;
- по столбцам — искомая фраза будет искаться сверху вниз от одного столбца к другому.
Выбор варианта, как просматривать, актуален, если в таблице много данных и есть какая-то необходимость просматривать по строкам или столбцам. Пользователь увидит, как именно просматривается таблица, когда будет нажимать кнопку Найти далее для перехода к следующему найденному совпадению.
Область поиска — определяет, где именно нужно искать совпадения:
- в формулах;
- в значениях ячеек (уже вычисленные по формулам значения);
- в примечаниях, оставленных пользователями к ячейкам.
А также дополнительные параметры:
- Учитывать регистр — означает, что заглавные и маленькие буквы будут считаться как разные.
Например, если не учитывать регистр, то по запросу «excel» будет найдены все вариации этого слова, например, Excel, EXCEL, ExCeL и т.д.
Если поставить галочку учитывать регистр, то по запросу «excel» будет найдено только такое написание слова и не будет найдено слово «Excel».
- Ячейка целиком — галочку нужно ставить в том случае, если нужно найти те ячейки, в которых искомая фраза находится целиком и нет других символов. Например, есть таблица со множеством ячеек, содержащих различные числа. Поисковый запрос: «200». Если не ставить галочку ячейка целиком, то будут найдены все числа, содержащие 200, например: 2000, 1200, 11200 и т.д. Чтобы найти ячейки только с «200», нужно поставить галочку ячейка целиком. Тогда будут показаны только те, где точное совпадение с «200».
- Формат… — если задать формат, то будут найдены только те ячейки, в которых есть искомый набор символов и ячейки имеют заданный формат (границы ячейки, выравнивание в ячейке и т.д.). Например, можно найти все желтые ячейки, содержащие искомые символы.
Формат для поиска можно задать самому, а можно выбрать из ячейки-образца — Выбрать формат из ячейки…
Чтобы сбросить настройки формата для поиска нужно нажать Очистить формат поиска.
Это меню вызывается, если нажать на стрелочку в правой части кнопки Формат.
Источники
- https://lengto.ru/tablitsy/kak-v-tablitse-eksel-najti-nuzhnoe-slovo.html
- https://bezkompa.ru/kak-v-eksele-najti-nuzhnoe-slovo.html
- https://lengto.ru/voprosy/kak-v-eksel-najti-slovo.html
- https://lengto.ru/tablitsy/kak-v-eksel-najti-slovo-v-tablitse.html
- https://pedsovet.su/excel/6116_kak_naiti_slovo_v_excel
Как вам статья?
Проверка ячейки на наличие в ней текста (без учета регистра)
Смотрите также из искомых ячеек. iRng Is Nothing к Вам снова.Отзовитесь пожалуйста! Очень f As Boolean, значения в этом два варианта:: The_Prist, я так 5 столбов. В аргумент генерирует функция полученное предыдущей формулой. 4 на протяжении выделить» можно в. столбца» (функция «ИНДЕКС».Примечание: Вторая искомая находится Then With Workbooks(«Инв.карт.xls»).Worksheets(«TDSheet») Задачка такая: нужная вещь, кто
fO As Long столбце и как1) вытащить эту понял найти ячейку каждой ячейке могут СТРОКА, которая создает Оно теперь выступает двух кварталов. статье «Фильтр вСовет. в Excel).КнопкаМы стараемся как
на 8 вправа Set iRng_Kart =Есть большое количество знает, напишите примерные Set Word =
Поиск ячеек, содержащих текст
только макрос находит строку со второго с текстом, и
-
быть один из в памяти массив в качестве критерия
В первом аргументе функции Excel».Если вы работаете
-
Найти и перенести вПараметры можно оперативнее обеспечивать и на 5 .Columns(2).Find(«Сведения о приемке», файлов, содержащих данные действия чтобы осуществить CreateObject(«Word.Application») Set WordDoc первую заполненную ячейку,
-
листа — тогда «отобразить ее в след.типов данных: текст, номеров строк из для поиска месяца. ВПР (Вертикальный ПРосмотр)Имеем таблицу, в которой с таблицей продолжительное другое место в
служит для задания вас актуальными справочными вниз от неё.
-
, xlValues, xlPart) в виде таблиц, сие действо или = Word.Documents.Open(Filename:=Application.ThisWorkbook.path & начинает искать в ваша последняя формула R77″ (т.е. по число, дата, просто
-
10 элементов. Так И в результате указывается ссылка на записаны объемы продаж время и вам Excel более подробных условий материалами на вашем
Поиск по содержанию? .Cells(iRng_Kart.Row + 3, перед каждой из киньте где посмотреть. «Пример.docx») Set r той же папке отлично подходит. адресу) пусто. Одна (И как в табличной
-
функция ПОИСКПОЗ нам ячейку где находится определенных товаров в часто надо переходить(например, в бланк)
поиска. Например, можно найти языке. Эта страница Поиск по содержанию, 2).UnMerge Cells(iRng.Row + которых в первом На форуме не = WordDoc.Range Do где находится excel,2) вытаскивать эту
Юрий М ТОЛЬКО ОДНА - части у нас
Проверка ячейки на наличие в ней любого текста
возвращает номер столбца критерий поиска. Во разных месяцах. Необходимо к поиску от
Проверка соответствия содержимого ячейки определенному тексту
несколько данных сразу все ячейки, содержащие переведена автоматически, поэтому а значит вопрос 3, 1).Copy .Cells(iRng_Kart.Row столбце А стоит
Проверка соответствия части ячейки определенному тексту
нашел( With r.Find .ClearFormatting .docx файл с строку сразу с: Я так понял, иначе быть не находится 10 строк. 2 где находится
втором аргументе указывается в таблице найти одного слова к – смотрите в
support.office.com
Поиск в Excel.
данных определенного типа, ее текст может по теме. Нужно + 3, 2) цифра (номер пункта)Alex77755 .Text = «дисциплина таким же названием, третьего листа (скопированных что в R77 может) ячейка содержитДалее функция ГПР поочередно
максимальное значение объема диапазон ячеек для данные, а критерием другому. Тогда удобнее статье «Найти в такого как формулы. содержать неточности и выделить диапазон из .Cells(iRng_Kart.Row + 3,
и её название.: На такой вопрос *относится» .Forward = что и текст данны) — и результат :-) текст в котором используя каждый номер продаж для товара просмотра в процессе
поиска будут заголовки окно поиска не Excel несколько данныхДля поиска на текущем грамматические ошибки. Для этих двух ячеек. 2).Resize(, 5).Merge End Верхняя часть всех
вряд ли кто True .Wrap = в ячейке. Если в этом случаеЮрий М встречается «ИНН» и
строки создает массив 4. После чего поиска. В третьем строк и столбцов. закрывать каждый раз, сразу» здесь (функция листе или во нас важно, чтобы
vikttur With End If файлов одинаковая и сможет ответить: слишком wdFindContinue .Format =
находит такой файл, ваша последняя формула: Если результат будет «КПП». А вот соответственных значений продаж в работу включается аргументе функции ВПР Но поиск должен а сдвинуть его «ВПР» в Excel). всей книге можно
эта статья была: Получать адрес можно End Sub работа с ней расплывчатое задание. False .MatchCase = то создает в может дать сбой, не в R77 её расположение в из таблицы по функция ИНДЕКС, которая
должен указываться номер быть выполнен отдельно в ту частьИли выбрать в поле вам полезна. Просим для чего угодно,Amsh не представляет никакихСамый быстрый поиск
False .MatchWholeWord = данном excel файле так как нужная — незачёт! таблице — может определенному месяцу (Июню).
возвращает значение по
столбца, из которого по диапазону строки таблицы, где ононайти ячейку с ссылкойИскать вас уделить пару но задача в: Спасибо большое. А трудностей, а вот Find. False .MatchAllWordForms = новый лист с ячейка может оказатьсяHimtree меняться. Далее функции МАКС номеру сроки и следует взять значение
или столбца. То не будет мешать. в формуле Excel,вариант секунд и сообщить, теме должна освещаться Вы, может быть, с нижней естьОстальные параметры надо False .MatchSoundsLike = тем же названием, не во втором: Не хотел я
Пример содержимого: «Администрация осталось только выбрать столбца из определенного на против строки
есть будет использоваться Сдвинуть можно нижечтобы заменить ссылку,Лист
помогла ли она одна. сможете подсказать: если проблемы. В пункте уточнять… False .MatchWildcards = если не находит, (B) столюце, а пугать вас своим
МО города Москвы максимальное значение из в ее аргументах с именем Товар только один из экрана, оставив только смотрите статью «Поменятьили вам, с помощью
Вы сейчас просите в ячейке А1 три файла СтараяМожно так же True If .Execute то ищет дальше в третьем (С).
файлом, ну раз ИНН 0123456789 КПП этого массива. диапазона. Так как 4. Но так критериев. Поэтому здесь ячейку ввода искомого ссылки на другиеКнига
кнопок внизу страницы. помощи по выделению записано $A$2, а 030162.xlsx таблица бывает использовать функцию листа
excel-office.ru
Поиск значения в столбце и строке таблицы Excel
Then If f до последней заполненнойHimtree просите, вот. В 123456789″Далее немного изменив первую у нас есть как нам заранее нельзя применить функцию слова («найти») и листы в формулах. Для удобства также диапазона. в ячейке B1 абсолютно разных размеров ВПР Then If r.Start
Поиск значений в таблице Excel
ячейки.: эх, поспешил… нём три листа.ЗАДАЧА: проверить всю формулу с помощью
номер столбца 2, не известен этот ИНДЕКС, а нужна
нажимать потом Enter. Excel».Нажмите кнопку приводим ссылку наВывод: или вопрос записано 1111. Как по высоте (2переменная =Application.WorksheetFunction.VLookup = fO ThenПодскажите пожалуйста, какДело в том1) Карточка результата таблицу 5*5 на функций ИНДЕКС и а номер строки номер мы с специальная формула.Это диалоговое окноНайти в Excel ячейки
Поиск значения в строке Excel
Найти все оригинал (на английском не по теме, должна выглядеть строка ячейки, 3, 7,
privet2000 Exit Do End
- это сделать. что я работаю (здесь должны отображаться предмет ячейки содержащей ПОИСКПОЗ, мы создали
- в диапазоне где помощью функции СТОЛБЕЦ
- Для решения данной задачи поиска всегда остается с примечаниемили языке) . или тема названа макроса, которая запишет 12, сколь угодно):
- If Else fONarimanych
- в бесплатном OO все необходимые данные,
текст «ИНН» и вторую для вывода хранятся названия месяцев создаем массив номеров проиллюстрируем пример на
Принцип действия формулы поиска значения в строке Excel:
на экране, даже-Найти далееПредположим, что вы хотите коряво. в ячейку А2 и из-за этого=СУММПРОИЗВ(НЕ(ЕОШИБКА(ПОИСК(ТРАНСП(ВЫГРУЗКА!G11);ВЫГРУЗКА!B7:B497)))*(ВЫГРУЗКА!D7:D497)) = r.Start f: ver.3.2.1 а вот в пригодном для «КПП» и в названия строк таблицы в любые случаи столбцов для диапазона схематической таблице, которая если вы переходитестатья «Вставить примечание. убедиться, что столбец…и дальше целый значение ячейки B1?
адреса ячеек вЗамечательная формула от = True EndAlexFlash27 в нём данная восприятия виде) случае её обнаружения по зачиню ячейки. будет 1. Тогда B4:G15. соответствует выше описанным на другую страницу. в Excel» тутНайти все содержит текст, не перечень, техзадание. АKuzmich необходимой мне четвертой
пользователя pabchek, ищет If WordDoc.Range(r.Start +, Привет. формула (при том2) Обработка (промежуточный отобразить текст из Название соответствующих строк нам осталось функциейЭто позволяет функции ВПР условиям. С помощью этого .списки каждого экземпляра номера. Или perhapsyou в этом разделе: Range(«A2»)=Range(«B1») таблице меняются в все значения в 11, r.End -Столбец «B» для
Как получить заголовки столбцов по зачиню одной ячейки?
что отлично работает лист, для упорядочивания этой ячейки в (товаров) выводим в ИНДЕКС получить соответственное собрать целый массивЛист с таблицей для окна можно производитьДля быстрого поиска элемента, который необходимо необходимо найти все помощь по конкретнымAmsh зависимости от файла. столбце и в 9).Copy Range(«C4»).Select ActiveSheet.Paste нижеприведенного должен быть в MS 2007) данных) ячейке (к примеру) F2. значение из диапазона значений. В результате поиска значений по поиск на любой существует сочетание клавиш найти, и позволяет заказы, которые соответствуют вопросам.: Не совсем то.Что нужно от найденных строках берет Set r = заполнен с1-ой строки.. вываливается в ошибку:3) Данные (сюда R77.ВНИМАНИЕ! При использовании скелета B4:G4 – Февраль в памяти хранится вертикали и горизонтали: странице, надо только – сделать активной ячейки, определенным Продавец. Если
Нашли ячейку по
Поиск значения в столбце Excel
Нам не известен макроса: в открытом значения из другого WordDoc.Range(r.End, r.End) ElsePureBasic Sub FOR_ALEX() #DIV/0! каждый раз будутхто-то формулы для других (второй месяц). все соответствующие значения
Над самой таблицей расположена его активизировать наCtrl + F выбрав нужное вхождение. у вас нет
содержанию — вот, адрес ячейки, в файле Старая 030162.xlsx столбца для сложения.
- Exit Do End m = 0MCH вставляться новые данные,: где файл?
- задач всегда обращайте
- каждому столбцу по строка с результатами. открытой странице. Для. Нажимаем клавишу Ctrl Можно сортировать результаты проблемой верхний или получили ответ по которую нужно скопировать
- запускать поиск поНужно чтобы поиск
- If End With PTH = ThisWorkbook.Path
Принцип действия формулы поиска значения в столбце Excel:
: попробуйте так: скопированные с сайта)KuklP внимание на второйВторым вариантом задачи будет строке Товар 4 В ячейку B1 этого нажать курсор и, удерживая её,Найти нижний регистр текста, теме. Другое (выделение, значение из B1, первому столбцу листа был только того LoopКак совместить это
FN = Dir(PTH=ИНДЕКС(B1:B6;ПОИСКПОЗ(«*ИНН*КПП*»;B1:B6;0))На первом листе,: Опять студенты лодыри… и третий аргумент поиск по таблице (а именно: 360; водим критерий для на строке «найти». нажимаем клавишу F.
все, щелкнув заголовок. существует несколько способов поиск диапазона, копирование но он записан для нахождения ячейки содержимого, которое в код и код & «*.docx», vbNormal)MCH в красной ячейке
MCH поисковой функции ГПР. с использованием названия 958; 201; 605; поискового запроса, тоДля более расширенного Появится окно поиска.Примечание: проверки, если ячейка и проч.) - в ячейке А1 с фразой «Сведения ячейке, и не который вы написали,
ReDim SHName(ThisWorkbook.Sheets.Count) For: или; должна появится надпись:
: формула массива, вводится Количество охваченных строк месяца в качестве 462; 832). После
есть заголовок столбца поиска нажмите кнопкуЕщё окно поиска Чтобы прекратить процесс поиска, содержит текст. не в тему в виде $A$2 о приемке» (например, больше чем то, чтобы после того i = 1=ВПР(«*ИНН*КПП*»;B1:B6;1;0)»ИНН 2311038642 КПП нажатием ctrl+shift+enter: в диапазоне указанного критерия. В такие
exceltable.com
Поиск ячейки содержащей текст, часть которого отвечает условию поиска.
чего функции МАКС или название строки. «Параметры» и выберите можно вызвать так нажмите клавишу ESC.Для поиска текста можноAmshKuzmich
это получилась ячейка что нужно: в как макрос находит
To ThisWorkbook.Sheets.Count SHName(iЮрий М 231101001 Администрация Прикубанского=ИНДЕКС(A1:E5;МАКС(ЕЧИСЛО(ПОИСК(«ИНН»;A1:E5))*ЕЧИСЛО(ПОИСК(«КПП»;A1:E5))*СТРОКА(A1:E5));МАКС(ЕЧИСЛО(ПОИСК(«ИНН»;A1:E5))*ЕЧИСЛО(ПОИСК(«КПП»;A1:E5))*СТОЛБЕЦ(A1:E5))) в аргументе, должно случаи мы должны остается только взять А в ячейке нужный параметр поиска. — на закладкеДля выполнения этой задачи также использовать фильтр.: Это всё понятно.: Sub ww() Dim А36), копировать данные G11 содержимое «МЕЛ», в папке word
— 1) =: Дело в том внутригородского округа городаSerge
совпадать с количеством изменить скелет нашей из этого массива D1 формула поискаНапример, выберем — «Значение». «Главная» нажать кнопку используется функция Дополнительные сведения см. Там не техзадание, Rng As String
из ячейки на и если в
файл с таким Sheets(i).Name Next ReDim
что я работаю Краснодара: МСН: >
строк в таблице.
формулы: функцию ВПР максимальное число и
должна возвращать результат
Тогда будет искать «Найти и выделить».ЕТЕКСТ
в статье Фильтрация а чтобы задавалось
Rng = Range(«A1») три строки ниже
столбце B7:B497 есть же именем как
S(m) Do If в бесплатном OO
javascript:»Ну наконец-то! А также нумерация заменить ГПР, а возвратить в качестве вычисления соответствующего значения.
и числа, и
На вкладке «Найти» в
. данных.
меньше вопросов, поэтому Range(Rng) = Range(«B1»)
(А39), открывать свёрнутый «МЕЛ: Не определено»,
и значения ячейки FN = «»
ver.3.2.1 {/post}{/quote}Тогда ВамЕё можно подцепитьХоть один гуру должна начинаться со функция СТОЛБЕЦ заменяется значения для ячейки
После чего в
номер телефона, т.д.
ячейке «найти» пишемДля возвращения результатов для
Выполните следующие действия, чтобы всё подробно. Каков End Sub файл Инвентарная карточка то это более из столбца B, Then Exit Do
прямая дорога на как с третьего сознался что он второй строки!
на СТРОКА. D1, как результат ячейке F1 сработаетЕсли нужно найти
искомое слово (можно условия, которое можно найти ячейки, содержащие смысл плодить дополнительныеAmsh ОС 030162.xlsx, запускать
длинное значение не он открывал его, S(m) = Mid(FN, форум по этому так и со
гуру! :-)))Скачать пример поиска значенияЭто позволит нам узнать
вычисления формулы. вторая формула, которая все одинаковес слова, часть слова) и
указать с помощью определенный текст. темы, если вопрос
: Спасибо, работает так, такой же поиск учитывать. копировал нужное слово,
1, Len(FN) -
продукту. второго листа, ноMCH в столбце и какой объем и
Как видно конструкция формулы уже будет использовать
но в падежах нажимаем «найти далее».
функцииВыделите диапазон ячеек, среди вытекает из этой, как нужно! для нахождения фразыМожет можно ограничить вставлял скопированное из 5) m =
Himtree как ?: UDF: строке Excel какого товара была проста и лаконична. значения ячеек B1
(молоко, молоком, молоку,
Будет найдено первоеЕсли которых требуется осуществить которую к томуAmsh «Сведения о приемке» поиск дополнительно по
word слово на
m + 1: спасибо но опять
ТОЛЬКО ФОРМУЛОЙ, никакихFunction inn(rng AsЧитайте также: Поиск значения максимальная продажа в
На ее основе и D1 в т.д.), то напишем такое слово. Затем.
поиск. же я и: Ещё вопрос возник по второму столбцу кол-ву символов. второй (уже созданный) ReDim Preserve S(m) в MS 2007 макросов. Range) As String в диапазоне таблицы определенный месяц.
можно в похожий качестве критериев для
формулу с подстановочными нажимаете «найти далее»Для выполнения этой задачиЧтобы выполнить поиск по создавал. по схожей проблеме. (Ячейка B32) иВопрос сложно озвучить, лист в excel? FN = Dir()
работает легко, аHimtree
Dim r As
Excel по столбцамЧтобы найти какой товар
способ находить для
поиска соответствующего месяца. знаками. Смотрите об и поиск перейдет используются функции всему листу, щелкнитеvikttur Имеются две ячейки вставлять ранее скопированные
прошу прощения за А если не Loop For i в OO нефунциклирует!: файл вот Range
и строкам обладал максимальным объемом определенного товара иТеперь узнаем, в каком этом статью «Подстановочные на второе такоеЕсли
любую ячейку.: Какой мысл? Ознакомьтесь с адресами например,
данные в ячейку корявый язык но находит в папке = LBound(S) To Эх =(HimtreeFor Each rПо сути содержимое диапазона продаж в определенном другие показатели. Например, максимальном объеме и
знаки в Excel». слово.
planetaexcel.ru
Поиск файла по содержимому ячейки
,На вкладке
с правилами форума BP62 и BR62. на три ниже на скрине и файл с таким UBound(S) — 1KuklP: Наверное криво я In rng нас вообще не месяце следует: минимальное или среднее в каком месяцеФункция в Excel «НайтиА если надо показатьПоискГлавнаяЭто Вам удобно, в первой записано найденной (B35). в файле все же именем, то For j =
: Himtree, Вам же изъяснился. Прикрепил файл,
If InStr(r.Text, «ÈÍÍ») интересует, нам нуженВ ячейку B2 введите значение объема продаж
была максимальная продажа и выделить» сразу все такие
ив группе а не форуму. $BA$64, а воТакое же нужно сразу видно уведомляет что такого LBound(SHName) To UBound(SHName) Юрий М подсказал с конкретным примером. > 0 And просто счетчик строк. название месяца Июнь используя для этого Товара 4.поможет не только слова, то нажимаемЕЧИСЛОРедактирование Как такие же, второй $BI$69. Эти будет сделать дляabtextime файла нет. If S(i) = выход от 06.11.2011, В красной ячейке InStr(r.Text, «ÊÏÏ») > То есть изменить – это значение функции МИН илиЧтобы выполнить поиск по найти данные, но кнопку «найти все».
нажмите кнопку как Вы, будут адреса определяются в
ячейки B39, D39,: Приложите Excel-файлauts SHName(j) Then S(i) 10:10. Или просто должно отобразиться название 0 Then inn
аргументы на: СТРОКА(B2:B11) будет использовано в СРЗНАЧ. Вам ни столбцам следует: и заменить их. и внизу поисковогоПримечание:Найти и выделить находить ответ на каждом файле свои. A68 и F68,Светлый: после обработки базы = «» Next нравится людям голову организации с ИНН = r.Text: Exit или СТРОКА(С2:С11) – качестве поискового критерия. что не препятствует,В ячейку B1 введите Смотрите статью «Как окошка появится список Функцияи нажмите кнопку свой вопрос о Но записываются всегда но думаю, я: данных, у меня Next For i морочить? и КПП. Function это никак неВ ячейку D2 введите чтобы приведенный этот значение Товара 4 скопировать формулу в с указанием адресапоискаНайти копировании диапазона? Листать в ячейки BP62 разберусь. Получить бы=СУММПРОИЗВ((ВЫГРУЗКА!G11=ВЫГРУЗКА!B7:B497)*(ВЫГРУЗКА!D7:D497)) создается несколько массивов = i =HimtreeHimtreeNext r повлияет на качество формулу: скелет формулы применить – название строки, Excel без изменения ячейки. Чтобы перейтине учитывается регистр.
CyberForum.ru
Excel: поиск по ячейкам (по содержимому)
. все темы в и BR62. Какой пример. Очень надеюсьprivet2000 с данными (одномерные LBound(S) To UBound(S): Простите, все кому: Блин, а работаетEnd Function формулы. Главное, чтоДля подтверждения после ввода с использованием более которое выступит в ссылок» здесь. на нужное словоЧасто возникает вопросВ поле надежде, что в командой можно выделить на вашу помощь.: Извиняюсь, видимо файл с переменными сингл) — 1 If
заморочил голову. =) сейчас будуMCH в этих диапазонах формулы нажмите комбинацию сложных функций для качестве критерия.Как убрать лишние
в таблице, нажимаем«Найти какой-то из тем диапазон BA64:BI69 в
Спасибо. слетел во время
которые я затем S(i) <> «»
Большая часть функций разбирать по этапам: Криво получилось:
по 10 строк,
CyberForum.ru
Поиск точного соответствия содержимого ячейки (Формулы/Formulas)
клавиш CTRL+SHIFT+Enter, так реализации максимально комфортногоВ ячейку D1 введите
пробелы, которые мешают нужное слово вКак найти в Excelвведите текст —Amsh таком случае, чтобыФайл, из которого
пред последнего редактирования переписываю в эксель Then Worksheets.Add.Name = (по крайне мере и вкуривать какFunction inn(rng As как и в как формула будет анализа отчета по следующую формулу: обрабатывать данные в списке окна поиска.»?
или номера —,’а найдется нужное? выполнять потом над
дергать данные прикрепляю.privet2000 файл в два S(i) Next End все простые) из именно она работает
Range) As String таблице. И нумерация
выполнена в массиве. продажам.Для подтверждения после ввода
таблице, читайте вЕсли поиск ничего неВ Excel можно вам нужно найти.
Amsh ним действия поKuzmich: Забыл указать. Упрощенный столбца, первый - Sub MS 2007 идентичны и стабильность вычисления.Dim r As начинается со второй А в строке
excelworld.ru
Поиск ячейки по содержанию для получения её адреса
Например, как эффектно мы формулы нажмите комбинацию статье «Как удалить нашел, а вы найти любую информацию: Или выберите из: Хорошо, я создам
объединению ячеек, центрированию: Обе книги д.б. вариант работает, но название, например г24(неAlexFlash27 и поддерживаются иСпасибо! Range строки! формул появятся фигурные отобразили месяц, в горячих клавиш CTRL+SHIFT+Enter, лишние пробелы в знаете, что эти текст, часть текста, раскрывающегося списка нужную дополнительную тему. содержимого с переносом? открыты. В макрос забыл сказать, есть имя ячейки, а: Спасибо работает, а в OO. НадеялсяMCHFor Each rHimtree
скобки. котором была максимальная так как формула Excel» тут. данные точно есть, цифру, номер телефона,Найтиvikttur Формат ячеек Общий. вставьте свое название еще возможность оставить строковое значение), как еще вопрос: что мою задачку: А зачем Вам In rng: Добрый вечер! БитыеВ ячейку F1 введите продажа, с помощью должна быть выполненаВ Excel можно то попробуйте убрать эл. адреспоследнего поиска.
: Вы сами-то, передvikttur книги с инвентаризационной в формуле указывать мне сделать такУ меня есть можно решить на 5 столбцов наIf InStr(r.Text, «ИНН»)
сутки не могу вторую формулу:
второй формулы. Не в массиве. Если найти любую информацию из ячеек таблицы,Примечание:
созданием темы, пользовались: Вопрос не по карточкой. диапазон? Например G11:G12 чтобы после ввода код, который ищет элементарных функциях MS, 5 строк, когда > 0 And решить казалось быСнова Для подтверждения нажмите сложно заметить что все сделано правильно, не только функцией отступ. Как убратьфамилию, формулу, примечание, формат В условиях поиска можно поиском? Ведь ответы темеМакрос в модуль Так как кроме в инпутбокс этого
слово в word которые легко ретранслировать нужно просматривать ячейки InStr(r.Text, «КПП») > не сложную, задачку, CTRL+SHIFT+Enter. во второй формуле в строке формул «Поиск» или формулами, отступ в ячейках, ячейки, т.д. использовать подстановочные знаки.
на многие вопросыAmsh
листа1 книги Старая…. МЕЛ хорошо бы имени, у меня файле по его в OO. B1:B6, и формула 0 Then inn но всё мыслиВ первом аргументе функции
мы использовали скелет появятся фигурные скобки. но и функцией смотрите в статьеНайти ячейку на пересеченииЧтобы задать формат для
можно найти… У: По теме. Начну Sub Perenos() Dim
еще что то производился поиск по «окружению», и копируетПо видимому ошибался! значительно проще получается: = r.Text: Exit заходят в тупик. ГПР (Горизонтальный ПРосмотр) первой формулы безВ ячейку F1 введите условного форматирования. Читайте «Текст Excel. Формат». строки и столбца поиска, нажмите кнопку Вас примеры вообще с предыстории: в iRng As Range добавлять для сложения. файлу и переменной его в определенную =(=ПРОСМОТР(2;1/ЕЧИСЛО(ПОИСК(«КПП»;B1:B6))/ЕЧИСЛО(ПОИСК(«ИНН»;B1:B6));B1:B6)
Function Прошу помощи гуру. указываем ссылку на
функции МАКС. Главная вторую формулу: об этом статьюПоиск числа в Excel ExcelФормат без макросов… Т.е. открытом файле запускается Dim iRng_Kart AsAmsh присваивалось значение из ячейку файла excel:AlexFlash27массивный ввод неNext rОСОБЕННОСТИ: Задачу необходимо ячейку с критерием структура формулы: ВПР(B1;A5:G14;СТОЛБЕЦ(B5:G14);0).Снова Для подтверждения нажмите
«Условное форматирование втребует небольшой настройки– смотрите статьюи внесите нужные ни намека на поиск по слову
Range Set iRng: Добрый день! Очень ячейки идущей после
Sub primer() Dim: Привет! требуетсяEnd Function
решить только с для поиска. Во Мы заменили функцию комбинацию клавиш CTRL+SHIFT+Enter. Excel» здесь.
условий поиска - «Как найти в изменения во всплывающем то, что что-то «Примечание», от него = Columns(1).Find(«Сведения о здорово выручаете своими
той в которой Word As ObjectИмеется файл excelHimtreeBond использованием формул. втором аргументе указана МАКС на ПОИСКПОЗ,Найдено в каком месяцеЕщё прочитать о применим Excel ячейку на
окне самостоятельно… А хотите на 4 строки
приемке», , xlValues, советами и очень внутри написано г24 Dim WordDoc As с заполненным столбцом: Да, спасибо, как: Что за факультет?ИСХОДНЫЕ ДАННЫЕ: есть ссылка на просматриваемый которая в первом и какая была функции «Найти и
расширенный поиск в Excel пересечении строки иНайти формат
сразу, в комплексе. вниз находится первая xlPart) If Not оперативно, поэтому обращаюсь или а-40? Object Dim r, B, нужно перебрать вариант! Просто рассматривалnerv таблица 5 строк, диапазон таблицы. Третий аргументе использует значение,
planetaexcel.ru
наибольшая продажа Товара
А вы знали, что критерием поиска в ВПР или СУММЕСЛИ / СЧЁТЕСЛИ и т.д. может быть часть ячейки?
Покажу на примере.
Пример с функцией ВПР
Предположим, в желтую ячейку нужно вывести день рождения сотрудницы по имени Ирина. Критерий поиска — только имя.
Но ведь в таблице-источнике, в которой мы будем искать совпадение — нет столбца Имя. Только ФИО.
Если сразу хочется разделить столбец ФИО на Фамилию, имя и отчество и уже оттуда тянуть — не торопитесь.
Напишем в желтую ячейку формулу:
=ВПР(«*ирина*»;C2:D11;2;0)
где «*ирина*» — и есть тот самый критерий поиска по части ячейки.
Здесь используются два значка-оператора — кавычки и звездочка.
- звездочка * — заменяет любое количество символов. Т.е.перед и после слова ирина может находиться любое количество любых символов.
- кавычки «» — обязательно ставятся для текстового аргумента поиска в функциях excel.
Остальные аргументы стандартные для функции ВПР:
C2:D11 — таблица для поиска, здесь ее не закрепляем абсолютными ссылками, т.к. копировать формулу не будем.
2 — номер столбца в таблице, из которого будут возвращаться данные.
0 — аргумент интервальный просмотр.
Как видите, день рождения сотрудницы по имени Ирина (Лесиченко Ирина Ивановна) подтянулся правильно.
Пример с функцией СЧЁТЕСЛИ
На примере той же таблицы, найдем количество сотрудниц по имени Елена.
Напишем формулу:
=СЧЁТЕСЛИ(C2:C11;»*елена*»)
Где
C2:C11 — диапазон для поиска
«*елена*» — критерий поиска, образованный по тому же принципу, что и для предыдущего примера. Формула будет искать все ячейки, в которых содержится «елена» в любой части ячейки, и вернет их количество.
Формула посчитала значение 3 — и действительно, в списке три сотрудницы с таким именем.
Аналогично этот прием работает в функциях СУММЕСЛИ и СУММЕСЛИМН.
Ловушка этого способа
В эту ловушку я тоже попадала.
Будьте внимательны, если задаете в качестве критерия поиска слово, которое предположительно может быть частью другого слова.
Например, я считала количество сотрудников с именем Петр (буква ё не использовалась).
Критерием для функции СЧЁТЕСЛИ был «*петр*». И конечно, результатом расчета функции были не только все Петры, но и Петровы, Петровичи и Петровны, поскольку «Петр» является частью этих слов-отчеств.
В таком случае после слова-критерия (или перед ним) нужно поставить пробел. В данном случае, критерием поиска будет «*Петр *» (с пробелом перед второй звездочкой).
Вам может быть интересно:
Привет. Наверное чаще всего в Эксель или Гугл Таблицах я использую набор формул, призванный по заданному мною тексту находить соответствие в ячейке и отмечать их либо копировать в соседний столбец. Очень удобно использовать для сортировки столбцов данных, когда нужно сделать её быстро без загрузки в Power Query или какой-то дополнительной обработки.
Расскажу подробнее про это и в конце отдам шаблон.
Формула для поиска значения ячейки Эксель
На скрине выше, видно, что в столбец с фильтром попадают только ячейки, содержащие упоминание цены. А в столбец без фильтра, все прочие — это сделано для того, чтобы в дальнейшем удобно было забирать найденные значения и использовать в работе.
Если открыть строку формул, то мы увидим следующее →
Не пугайтесь, всё намного проще, чем кажется. Сейчас всё расскажу.
Центральная формула состоит из следующих значений и будет выглядеть так:
=if(isnumber(find(«цены»,А3)),А3,»»)
- Функция find ищет заданный нами текст «цена» в выбранной ячейке А3. Далее если находит, то она возвращает номер символа с которого начинается совпадение. Если не находит, то далее весь цикл формул не работает и на выходе получаем пустое значение в скобочках «» от функции if;
- Функция isnumber принимает на вход только числа, соответственно если find нашла искомое значение и отдала цифру, то функция срабатывает и продолжает цикл if, если нет, то опять получаем пустое значение в скобочках «» от функции if;
- Функция if если предыдущая функция isnumber срабатывает, возвращает нам значение ячейки A3, если нет, то пустое значение «».
Таким образом, там где есть упоминание «цена» мы получаем обратно эти ячейки. Для обычного поиска единственного совпадения этой формулы достаточно за глаза, но в нашем примере нужно найти все коммерческие запросы. Именно для этого мы используем перебор разных значений, как на этом скрине.
Это делается с помощью копирования строки. Но, очень важно, при копировании новой строки мы добавляем новую закрывающую скобочку в конец формулы.
Шаблон поиска совпадений по значению в ячейке
Чтобы вам не искать и не копировать формулу, я сделал шаблон для этой формулы. Он лежит в Гугл Таблице с двумя вариантами работы, совсем просто, где нужно ввести искомое значение в ячейку, вставить список слов и всё сработает само.
И второй, о котором писал для перебора нескольких совпадений.
Шаблон лежит тут, пользуйтесь на здоровье.