Контекстный поиск в excel

Контекстный поиск в excel

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

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

Классический поиск «MS Office».
Условное форматирование (выделение нужных ячеек цветом)
Настройка фильтров по одному или нескольким значениям.
Фрагмент макроса для перебора ячеек в диапазоне и поиска нужного значения.

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

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

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

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

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

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

2) Еще один вариант поиска заключается в выделении всех ячеек содержащих искомое слово каким-нибудь цветом.

Такой поиск реализуется через опцию условное форматирование.

Для форматирования ячеек следует выделить диапазон ячеек, которых нужно найти слово. Далее на вкладке «Главная» нажать кнопку «Условное форматирование».

Настройка форматирования для выделения искомых слов

В меню условного форматирования выбрать «Правила выделения ячеек» … «Текст содержит…».

В открывшемся окне указать искомое слово и цвет заливки ячейки. Нажать «ОК». Все ячейки, содержащие нужное слово будут окрашены.

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

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

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

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

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 ‘если в ячейке содержится слово окрасить ее в красный цвет (поиск)

Надстройка для облегчения ввода значений в ячейку Excel

Автор: nerv
Last Update: 27/03/2012

Вам часто приходится заниматься заполнением электронных таблиц, долго и муторно выбирать варианты из выпадающих списков?

А, может, иметь дело с одними теми же, но не структурированными данными?

Раз так, то данная надстройка призвана облегчить Вам жизнь: сократить время, потраченное на нудную, однообразную работу, а вместе с тем повысить ее качество и эффективность.

Как это работает:

По нажатию Ctrl+Enter рядом с выделенной ячейкой появляется список, который позволяет не только выбирать, но и производить поиск по интересующим Вас данным.

Посмотрим, что он умеет:

  • Не содержит повторов (уникальный). Легко выявить однотипные данные;
  • Отсортирован по возрастанию. Возможность быстро найти то, что нужно;
  • После вызова сразу готов к поиску/выбору из списка. Лишние движения ни к чему;
  • Позволяет искать с использованием специальных подстановочных символов (*.

и т.п.);

  • Осуществлять быстрый поиск по "шаблону". Если ячейка, из которой был вызван список, содержит информацию, поиск будет произведен по ней;
  • Появляется рядом с текущей/активной ячейкой и не "убегает" за пределы экрана;
  • Навигация привычными стандартными клавишами: Up [Вверх], Down [Вниз], Page Up [На страницу Вверх ], Page Down [На страницу вниз];
  • Корректная работа со всеми типами данных: строки, даты, числа;
  • Обработка ошибок формул листа. Никаких пустых строк в списке;
  • Обработка защиты ячеек листа. В защищенные ячейки ввод запрещен;
  • Информация об общем количестве списка и найденных по запросу элементах;
  • Быстрый вызов по нажатию Ctrl+Enter;
  • Быстрое закрытие: клавиша Esc;
  • Быстрый ввод клавишей Enter
  • Читайте также:  Перспектива текста в кореле

    Помимо всего вышеперечисленного, позволяет сэкономить на размере файла за счет формирования списка "на лету", который создается в разы быстрее, если данные упорядочены или частично упорядочены по возрастанию.

    Отличия версии 1.6 от 1.5:

    • новая, более мощная/быстрая процедура сортировки;
    • переход после ввода на следующую ячейку (в зависимости от установок Excel);
    • использования и формирования списка (подробнее во вложении "how to use");
    • поиска с учетом регистра и без него;
    • маски поиска;
    • заголовков.
    Вложение Размер Загрузки Последняя загрузка
    nerv_DropDownList_1.6.zip 28.74 КБ 19 5 лет 41 неделя назад
    • 134897 просмотров

    Комментарии

    Автор этой надстройки — не я.
    Тот, кто её написал, уже давно в комментах на сайте не отвечает, — а я не планирую поддерживать чужие решения.
    Потому, комменты к этой статье я сейчас закрою.

    На вопросы по этой надстройке больше отвечать некому.
    Надстройка не поддерживается, не дорабатывается, — если хотите её использовать, то используйте в таком виде, в каком она сейчас есть.
    Если нужно что-то аналогичное, — оформляйте заказ на сайте, сделаем «с нуля»

    Уважаемый Игорь!
    При работе у меня был сформирован список к примеру с 500 позиций в одном столбце, но уникальных элементов 100, возможно ли скопировать или экспортировать эти 100 уникальных элементов?
    Спасибо за надстройку.

    Люди добрые! Оч полезная надстройка! Но вот у меня одна проблема когда в двух соседних столбцах расположен "автопоиск" выдает ошибку (Unknown error). Допустим автопоиск настроен на ячейку А2 и Второй на В2. записан макрос на автозапуск
    Sub va()

    ‘ va Макрос


    Application.Run "nerv_DropDownList.DropDownListShow"
    End Sub

    и этот макрос запускается командой
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim lReply As Long

    If Target.Cells.Count > 1 Then Exit Sub
    If Not Application.Intersect(Range("A2:A2000"), Target) Is Nothing Then
    Application.Run "va"
    End If
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Application.Intersect(Range("B2:B2000"), Target) Is Nothing Then
    Application.Run "va"

    End Sub
    При переходе на ячейку А2 выходит окно поиска ввожу данные нажимаю enter, происходит переход на след ячейку B2 и выскакивает ошибка Unknown error!
    Помогите советом что сделать !?

    >Подскажите, как сделать так, чтобы можно было формировать запрос из другого файла
    Тот же вопрос: можно ли в качестве "Шаблона Подстановки" использовать другую книгу?
    Благодарю за шикарную надстройку.

    ИНДЕКС() + ПОИСКПОЗ() Вам в помощь.

    Саша, а зачем что-то привязывать.
    Подключите файл как надстройку к Excel, — и он сам будет запускаться каждый раз вместе с Excel

    Подскажите пожалуйста, как его можно привязать к personal.xls или рабочему файлу со списками, чтобы избежать запуска

    Здравствуйте!
    А сколько строк эта надстройка может обработать?
    Можно ли сделать чтобы обрабатывала 200 000

    Скажите, пожалуйста, как выбрать сразу несколько значений. Чтобы в одной ячейке было "Апельсин; Лайм". Это возможно?

    Здравствуйте! Могли бы Вы обновить файл nerv_DropDownList_1.6.zip? Файл скачивается, запускается excel, но ничего не открывается и ошибки не выдает. Спасибо!

    Подскажите, как сделать так, чтобы можно было формировать запрос из другого файла

    Отличная штука, спасибо!

    Есть небольшой глюк при использовании настройки — если на ячейки, куда автоподбором вставляются значения, установить проверку, то проверка не срабатывает когда значение вводится через окно надстройки.

    >> /////и как обеспечить проверку вводимых данных (запрет на ввод данных не из списка).

    >> Это легко можно организовать штатными методами екселя — /Данные/Проверка данных/список, указав предварительно созданный именованный динамический список из тогоже диапазона что и обсуждаемая надстройка.

    Excel 2007 — не получается так задать, макрос обходит запрет

    В документах Microsoft Excel, которые состоят из большого количества полей, часто требуется найти определенные данные, наименование строки, и т.д. Очень неудобно, когда приходится просматривать огромное количество строк, чтобы найти нужное слово или выражение. Сэкономить время и нервы поможет встроенный поиск Microsoft Excel. Давайте разберемся, как он работает, и как им пользоваться.

    Читайте также:  Где хранится pst файл outlook 2010

    Поисковая функция в Excel

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

    Способ 1: простой поиск

    Простой поиск данных в программе Excel позволяет найти все ячейки, в которых содержится введенный в поисковое окно набор символов (буквы, цифры, слова, и т.д.) без учета регистра.

    1. Находясь во вкладке «Главная», кликаем по кнопке «Найти и выделить», которая расположена на ленте в блоке инструментов «Редактирование». В появившемся меню выбираем пункт «Найти…». Вместо этих действий можно просто набрать на клавиатуре сочетание клавиш Ctrl+F.

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

    При нажатии на кнопку «Найти далее» мы перемещаемся к первой же ячейке, где содержатся введенные группы символов. Сама ячейка становится активной.

    Поиск и выдача результатов производится построчно. Сначала обрабатываются все ячейки первой строки. Если данные отвечающие условию найдены не были, программа начинает искать во второй строке, и так далее, пока не отыщет удовлетворительный результат.

    Поисковые символы не обязательно должны быть самостоятельными элементами. Так, если в качестве запроса будет задано выражение «прав», то в выдаче будут представлены все ячейки, которые содержат данный последовательный набор символов даже внутри слова. Например, релевантным запросу в этом случае будет считаться слово «Направо». Если вы зададите в поисковике цифру «1», то в ответ попадут ячейки, которые содержат, например, число «516».

    Для того, чтобы перейти к следующему результату, опять нажмите кнопку «Найти далее».

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

  • В случае, если при запуске поисковой процедуры вы нажмете на кнопку «Найти все», все результаты выдачи будут представлены в виде списка в нижней части поискового окна. В этом списке находятся информация о содержимом ячеек с данными, удовлетворяющими запросу поиска, указан их адрес расположения, а также лист и книга, к которым они относятся. Для того, чтобы перейти к любому из результатов выдачи, достаточно просто кликнуть по нему левой кнопкой мыши. После этого курсор перейдет на ту ячейку Excel, по записи которой пользователь сделал щелчок.
  • Способ 2: поиск по указанному интервалу ячеек

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

      Выделяем область ячеек, в которой хотим произвести поиск.

  • Набираем на клавиатуре комбинацию клавиш Ctrl+F, после чего запуститься знакомое нам уже окно «Найти и заменить». Дальнейшие действия точно такие же, что и при предыдущем способе. Единственное отличие будет состоять в том, что поиск выполняется только в указанном интервале ячеек.
  • Способ 3: Расширенный поиск

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

    К тому же, в выдачу может попасть не только содержимое конкретной ячейки, но и адрес элемента, на который она ссылается. Например, в ячейке E2 содержится формула, которая представляет собой сумму ячеек A4 и C3. Эта сумма равна 10, и именно это число отображается в ячейке E2. Но, если мы зададим в поиске цифру «4», то среди результатов выдачи будет все та же ячейка E2. Как такое могло получиться? Просто в ячейке E2 в качестве формулы содержится адрес на ячейку A4, который как раз включает в себя искомую цифру 4.

    Читайте также:  Ms gamingoverlay windows 10 что это

    Но, как отсечь такие, и другие заведомо неприемлемые результаты выдачи поиска? Именно для этих целей существует расширенный поиск Excel.

      После открытия окна «Найти и заменить» любым вышеописанным способом, жмем на кнопку «Параметры».

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

    По умолчанию, функции «Учитывать регистр» и «Ячейки целиком» отключены, но, если мы поставим галочки около соответствующих пунктов, то в таком случае, при формировании результата будет учитываться введенный регистр, и точное совпадение. Если вы введете слово с маленькой буквы, то в поисковую выдачу, ячейки содержащие написание этого слова с большой буквы, как это было бы по умолчанию, уже не попадут. Кроме того, если включена функция «Ячейки целиком», то в выдачу будут добавляться только элементы, содержащие точное наименование. Например, если вы зададите поисковый запрос «Николаев», то ячейки, содержащие текст «Николаев А. Д.», в выдачу уже добавлены не будут.

    По умолчанию, поиск производится только на активном листе Excel. Но, если параметр «Искать» вы переведете в позицию «В книге», то поиск будет производиться по всем листам открытого файла.

    В параметре «Просматривать» можно изменить направление поиска. По умолчанию, как уже говорилось выше, поиск ведется по порядку построчно. Переставив переключатель в позицию «По столбцам», можно задать порядок формирования результатов выдачи, начиная с первого столбца.

    В графе «Область поиска» определяется, среди каких конкретно элементов производится поиск. По умолчанию, это формулы, то есть те данные, которые при клике по ячейке отображаются в строке формул. Это может быть слово, число или ссылка на ячейку. При этом, программа, выполняя поиск, видит только ссылку, а не результат. Об этом эффекте велась речь выше. Для того, чтобы производить поиск именно по результатам, по тем данным, которые отображаются в ячейке, а не в строке формул, нужно переставить переключатель из позиции «Формулы» в позицию «Значения». Кроме того, существует возможность поиска по примечаниям. В этом случае, переключатель переставляем в позицию «Примечания».

    Ещё более точно поиск можно задать, нажав на кнопку «Формат».

    При этом открывается окно формата ячеек. Тут можно установить формат ячеек, которые будут участвовать в поиске. Можно устанавливать ограничения по числовому формату, по выравниванию, шрифту, границе, заливке и защите, по одному из этих параметров, или комбинируя их вместе.

    Если вы хотите использовать формат какой-то конкретной ячейки, то в нижней части окна нажмите на кнопку «Использовать формат этой ячейки…».

    После этого, появляется инструмент в виде пипетки. С помощью него можно выделить ту ячейку, формат которой вы собираетесь использовать.

    После того, как формат поиска настроен, жмем на кнопку «OK».

    Бывают случаи, когда нужно произвести поиск не по конкретному словосочетанию, а найти ячейки, в которых находятся поисковые слова в любом порядке, даже, если их разделяют другие слова и символы. Тогда данные слова нужно выделить с обеих сторон знаком «*». Теперь в поисковой выдаче будут отображены все ячейки, в которых находятся данные слова в любом порядке.

  • Как только настройки поиска установлены, следует нажать на кнопку «Найти всё» или «Найти далее», чтобы перейти к поисковой выдаче.
  • Как видим, программа Excel представляет собой довольно простой, но вместе с тем очень функциональный набор инструментов поиска. Для того, чтобы произвести простейший писк, достаточно вызвать поисковое окно, ввести в него запрос, и нажать на кнопку. Но, в то же время, существует возможность настройки индивидуального поиска с большим количеством различных параметров и дополнительных настроек.

    Отблагодарите автора, поделитесь статьей в социальных сетях.

    Ссылка на основную публикацию
    Количество дней в текущем месяце excel
    Определим число дней в заданном месяце с помощью функции КОНМЕСЯЦА() . Если ячейка А1 содержит дату, то число дней месяца,...
    Камера мвк 16 схема подключения
    Вы сможете задать интересующий Вас вопрос, заполнив ниже приведенную форму. Мы обязательно ответим Вам и предоставим всю необходимую информацию. Видеокамеры...
    Клавиатура с пластмассовыми штырями преимущества и недостатки
    электронный почта сканер клавиатура Клавиатура является одним из важнейших устройств, определяющим условия комфортабельной работы на РС. Главным элементом в клавиатуре...
    Команда 102 на теле2 что это такое
    Ниже представлены полезные команды Теле2 Казахстан, разбитые по категориям. Они бесплатны, но часть услуг, которыми они управляют могут быть платными....
    Adblock detector