Примеры найти – Примеры использования функции НАЙТИ в таблицах Excel

Содержание

Примеры использования функции НАЙТИ в таблицах Excel

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

Как использовать функцию НАЙТИ в Excel

Каждая функция с заполненными аргументами в Excel – это формула, с помощью которой выполняются сложные операции и вычисления в Excel. Перед ее введением ставится знак равенства. Именно так вы дадите знать программе, что вводится именно формула, а не текст и не значение. Функция состоит из двух частей: имени и аргумента.

Каждое название функции в Excel описывает ее операцию и назначение. В данном случае это слово «НАЙТИ».

Аргументов может быть несколько. Они могут быть числовыми, символьными или текстовыми и всегда заключаются в круглые скобки. Если аргументов больше одного, между ними ставится знак «;». Для поиска необходимо использовать следующие аргументы.

  1. Искомый текст. Сочетание знаков, которые мы разыскиваем в таблице. Это может быть цифро-буквенное сочетание, только цифры или только буквы, пробелы или знаки. Нужно помнить, что функция учитывает введенный регистр. Результаты поиска слов «Мир» и «мир» будут разными.
  2. Просматриваемый текст. Область поиска может быть указана с помощью мыши. Также ее значения могут быть введены с клавиатуры.
  3. Начальная позиция (опциональный аргумент). По умолчанию началом поиска признается первая ячейка первой строки таблицы. Вы можете вручную задать номер ячейки, которая будет начальной в процессе поиска.

Синтаксис функции выглядит таким образом:

НАЙТИ(«искомый текст»; просматриваемый текст; [начальная позиция])

Результатом применения функции будет номер места в строке, на котором располагается искомое ключевое слово. Если его нет, выдается символ ошибки #ЗНАЧ!



Примеры использования функции НАЙТИ

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

Пример 1. В таблице 4 столбца по 10 строк. В нее внесены:

  • номера по штатному расписанию;
  • ФИО работников;
  • количество отработанных дней:
  • оклад (размер оплаты).

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

Штатное расписание составлено особым образом. Номера рабочих единиц имеют пометку «!». В зависимости от расположения этой пометки можно понять, с какими вредными факторами сталкивается рабочий. Нам нужно отсортировать строки, штатные номера которых имеют пометку «!» на втором месте. Например, 3!7884, 8!6453 или 5!54.

Для этого в ячейку, следующую за последней в первой строчке, нужно ввести функцию НАЙТИ. Она будет выглядеть так.

=НАЙТИ(“!”; A2; 1)

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

Теперь вы сможете выделить и скопировать строки, напротив которых стоит цифра 2 или воспользоваться автофильтром: «ДАННЫЕ»-«Фильтр».

Отчет готов за пару секунд.

Пример 2. В таблице 4 столбца по 10 строк.

В нее сведены артикулы товаров, которые находятся на складе и указаны такие параметры;

  • наименование товара;
  • цвет;
  • цена;
  • артикул.

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

Алгоритм действий аналогичный. В ячейке, следующей за последней в первой строке прописываем функцию.

=НАЙТИ(“de”;D2;1)

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

В артикулах товаров, по которым выдана ошибка #ЗНАЧ!, нет заданных букв. Остается выполнить автофильтр, поиск по которым дал результат 1.

Выборка товаров готова.

Пример 3. В таблице 5 строк. В нее введены математические формулы.

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

Как всегда, функция прописывается в ячейке, следующей за последней в первой строчке. Формула выглядит так.

=НАЙТИ(“+”; A1; 1)

Нажав Enter, вы получите результат функции.

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

Читайте также: Примеры использования функции НАЙТИ в Excel формулах.

Выбраны все необходимые формулы из списка по критерию – «4» указанном в условии для отбора строк автофильтром Excel.

Все выше описанные примеры применяют функцию НАЙТИ без формул. Но на практике чаще всего она используется как вспомогательная функция для формул с обработкой таблиц в режиме базы данных.

exceltable.com

Поиск решений в Excel — пример использования сервиса поиск решений в Excel

Оптимизация значений таблицы Excel, удовлетворяющих определенным критериям, может быть сложным процессом. К счастью, Microsoft предлагает надстройку Решение проблем для численной оптимизации. Хотя данный сервис не может решить всех проблем, он может быть полезным в качестве инструмента что-если. Данный пост посвящен надстройке Решение проблем в Excel.

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

Что такое Поиск решений

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

Где в Excel поиск решений

Надстройка Поиск решений поставляется вместе с Excel, но по умолчанию отключена. Чтобы включить его, перейдите по вкладке Файл в группу Параметры. В появившемся диалоговом окне Параметры, выберите Надстройки -> Управление: Надстройки Excel -> Перейти. В окне Надстройки устанавливаем галочку напротив поля Поиск решения, жмем ОК.

Теперь во вкладке Данные появилась новая группа Анализ с кнопкой Поиск решения.

Пример использования Поиска решения

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

Надстройки Поиск решения. Файл совместим со всеми версиями Excel.

Определение проблемы

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

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

Для начала требуется определить каждый пункт к какой-нибудь группе.

Чтобы указать привязанность пункта к группе, будем помечать их единицей (1), в противном случае нулем (0).

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

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

Наконец, нам необходимо свести сумму групп и работать с разницей между ними.

Наша задача минимизировать разницу между суммами групп.

Теперь мы можем присвоить каждой группе пункты, для этого вручную проставляем единицы в столбцах С и D. Excel отобразит разницу сумм групп в ячейке G11.

Для большей наглядности я добавил условное форматирование для ячеек, имеющих значение >0.

Проблема в том, что количество возможных комбинаций 28, т.е. 256 вероятных ответов на вопрос. Если на каждый из них тратить по 5 секунд, это займет у нас 21,3 минуты, предполагая, что мы сможем выдержать темп и запомнить лучшую комбинацию.

Вот где Поиск решения находит применение.

Поиск оптимального решения в Excel

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

Наши правила

Наше основное требование – это минимизировать разницу между двумя группами. В нашем примере она находится в ячейке G11 – Группа B минус Группа A. Нам нужно, чтобы значение в ячейке G11 было настолько малым насколько это возможно, но больше или равно 0.

Мы также знаем, что пункт может находиться либо в Группе A, либо в Группе B, к тому он не может быть дробным. Таким образом у нас два ограничения для каждого элемента:

Во-первых: Значение элемента в колонке Итог должна равняться единице.

Во-вторых: Значения элементов в группах должны быть целыми.

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

Диалоговое окно Поиска решения

В этом разделе описано окно надстройки Поиск решения и его использования для определения проблемы.

Пустое окно Поиска решения

Заполненное окно Поиска решения

Оптимизировать целевую функцию

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

До

Здесь мы указываем, каких результатов хотим добиться от целевой функции.

Мы хотим, чтобы суммы обоих групп совпадали, т.е. чтобы разница сумм была равна 0. Это может показаться странным, но нам не требуется минимизировать разницу, потому что при этом все элементы будут помещены в Группу A, что приведет к значению ячейки G11 меньше нуля.

Другой способ наложения ограничения – изменить G11 на =ABS(G10-F10). При этом мы сможем установить маркер на Минимум, как результат достижения целевой функции.

Но пока мы остановимся на формуле =G10-F10 и установим маркер в значение равным 0.

Изменяя ячейки переменных

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

$C$2:$D$9.

В соответствии с ограничениями

Ограничения – это правила, которые лимитируют возможные решения проблемы.

Нам необходимо добавить несколько ограничений в наш список:

  1. В колонке Итого каждый элемент должен равняться 1
  2. Элементы групп должны быть целым числом
  3. Сумма значений столбца Итого должна равняться 8

Чтобы наложить ограничения, жмем кнопку Добавить

  1. Для каждой ячейки диапазона E2:E9 устанавливаем ограничение значения равным 1
  2. Для каждой ячейки диапазона C2:D9 устанавливаем ограничение значение целое число.
  3. Необходимо добавить ограничение на сумму обоих групп, ячейка E10 = 8.

Вы можете Изменить или Удалить ограничение, если допустили ошибку, выбрав конкретное ограничение и нажав соответствующие кнопки в диалоговом окне.

Загрузить/сохранить параметры поиска решений

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

Запуск поиска оптимального решения в Excel

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

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

Два параметра, которые необходимо будет менять время от времени:

Точность ограничения: значение от 0 до 1, где, чем больше цифра, тем больше ограничение

Целочисленная оптимальность: показывает насколько далеко от целого числа ограничение имеет право быть.

Запуск модели

Чтобы запустить надстройку нажмите кнопку Найти решение в основном окне.

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

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

Теперь у вас есть 4 варианта на выбор:

— Запустить отчет

— Сохранить сценарий

— Восстановить исходные значения

— Сохранить найденное решение

Запустить отчет

Вы можете создать отчет, выбрав доступные из списка отчетов. Будет создан новый лист Отчет о результатах1.

Обратите внимание, что в зависимости от установленных вами ограничений, будут доступны различные отчеты.

Сохранить сценарий

Если вы нажмете кнопку Сохранить сценарий, Excel откроет следующее диалоговое окно:

Где необходимо ввести название вашего сценария модели и нажать кнопку ОК.

Все сценарии доступны в Диспетчере сценариев, который находится во вкладке Данные в группе Работа с данными –> Анализ что-если -> Диспетчер сценариев.

Вернуться к модели

К тому же, вы можете вернуться к модели и:

— Восстановить исходные значения

— Сохранить найденное решение

Проверка результатов

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

Данная проверка на реалистичность должна начинаться с подтверждения, что все результаты удовлетворяют заданным критериям:

— Являются ли результаты примерно похожими на ваши ожидания?

— Не нарушены ли максимумы и минимумы?

Вам также могут быть интересны следующие статьи

exceltip.ru

Найти ранг матрицы: способы и примеры

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

Теорема 1 о ранге матрицы. Рангом матрицы называется максимальный порядок отличного от нуля минора матрицы.

Понятие минора мы уже разбирали на уроке по определителям, а сейчас обобщим его. Возьмём в матрице сколько-то строк и сколько-то столбцов, причём это “сколько-то” должно быть меньше числа строк и стобцов матрицы, а для строк и столбцов это “сколько-то” должно быть одним и тем же числом. Тогда на пересечении скольки-то строк и скольки-то столбцов окажется матрица меньшего порядка, чем наша исходная матрица. Определитель это матрицы и будет минором k-го порядка, если упомянутое “сколько-то” (число строк и столбцов) обозначим через k.

Определение. Минор (r+1)-го порядка, внутри которого лежит выбранный минор r-го порядка, называется называется окаймляющим для данного минора.

Наиболее часто используются два способа отыскания ранга матрицы. Это способ окаймляющих миноров и способ элементарных преобразований (методом Гаусса).

При способе окаймляющих миноров используется следующая теорема.

Теорема 2 о ранге матрицы. Если из элементов матрицы можно составить минор r-го порядка, не равный нулю, то ранг матрицы равен r.

При способе элементарных преобразований используется следующее свойство:

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

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

Например, дана матрица

.

Возьмём минор

,

окаймляющими будут такие миноры:

.

Алгоритм нахождения ранга матрицы следующий.

1. Находим не равные нулю миноры второго порядка. Если все миноры второго порядка равны нулю, то ранг матрицы будет равен единице (r =1).

2. Если существует хотя бы один минор второго порядка, не равный нулю, то составляем окаймляющие миноры третьего порядка. Если все окаймляющие миноры третьего порядка равны нулю, то ранг матрицы равен двум (r =2).

3. Если хотя бы один из окаймляющих миноров третьего порядка не равен нулю, то составляем окаймляющие его миноры. Если все окаймляющие миноры четвёртого порядка равны нулю, то ранг матрицы равен трём (r =2).

4. Продолжаем так, пока позволяет размер матрицы.

Пример 1. Найти ранг матрицы

.

Решение. Минор второго порядка .

Окаймляем его. Окаймляющих миноров будет четыре:

,

,

,

.

Таким образом, все окаймляющие миноры третьего порядка равны нулю, следовательно, ранг данной матрицы равен двум (r =2).

Проверить решение можно на калькуляторе онлайн Вычисление ранга матрицы.

Пример 2. Найти ранг матрицы

.

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

Проверить решение можно на калькуляторе онлайн Вычисление ранга матрицы.

Пример 3. Найти ранг матрицы

.

Решение. Минор второго порядка этой матрицы , в все миноры третьего порядка этой матрицы равны нулю. Следовательно, ранг данной матрицы равен двум.

Проверить решение можно на калькуляторе онлайн Вычисление ранга матрицы.

Пример 4. Найти ранг матрицы

.

Решение. Ранг данной матрицы равен 3, так как единственный минор третьего порядка этой матрицы равен 3.

Проверить решение можно на калькуляторе онлайн Вычисление ранга матрицы.

Уже на примере 1 видно, что задача определения ранга матрицы способом окаймляющих миноров требует вычисления большого числа определителей. Существует, однако, способ, позволяющий свести объём вычислений к минимуму. Этот способ основан на использовании элементарных преобразований матриц и ещё называется также методом Гаусса.

Под элементарными преобразованиями матрицы понимаются следующие операции:

1) умножение какой-либо строки или какого либо столбца матрицы на число, отличное от нуля;

2) прибавление к элементам какой-либо строки или какого-либо столбца матрицы соответствующих элементов другой строки или столбца, умноженных на одно и то же число;

3) перемена местами двух строк или столбцов матрицы;

4) удаление “нулевых” строк, то есть таких, все элементы которых равны нулю;

5) удаление всех пропорциональных строк, кроме одной.

Теорема. При элементарном преобразовании ранг матрицы не меняется. Другими словами, если мы элементарными преобразованиями от матрицы A перешли к матрице B, то .

Используя эту теорему, отправляясь от любой матрицы A всегда можно прийти к такой матрице B, вычисление ранга которой не представляет затруднений. Для этого следует добиться, чтобы матрица B была трапециевидной.

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

Пример 5. Найти ранг матрицы

.

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

.

Вычитая из четвёртой строки третью, а затем переставив местами вторую и третью строки, получаем матрицу

.

Получили трапециевидную матрицу. Ранг полученной матрицы равен трём (r=3), так как после вычёркивания последней строки, полностью состоящей из нулей, в ней останется три строки.

Желающие могут проверить это решение способом окаймляющих миноров (минор третьего порядка, находящийся в левом верхнем углу, не равен нулю, а все миноры четвёртого порядка равны нулю).

Найти ранг матрицы самостоятельно, а затем посмотреть решение

Поделиться с друзьями

Начало темы “Матрицы”

Продолжение темы “Матрицы”

Другие темы линейной алгебры

function-x.ru

Слово НАЙТИ – Что такое НАЙТИ?

Слово найти английскими буквами(транслитом) – naiti

Слово найти состоит из 5 букв: а и й н т


Значения слова найти. Что такое найти?

Найти девушку

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

ru.wikipedia.org

Найти подобные

Найти подобные – функция поисковой системы, позволяющая по специальному запросу найти при повторном поиске документы подобные (по тем или иным признакам) выделенным документам в результатах первоначального поиска.

glossary.ru

Найти подобные – функция поисковой системы, позволяющая по специальному запросу найти при повторном поиске документы подобные (по тем или иным признакам) выделенным документам в результатах первоначального поиска.

Словарь финансовых терминов

Найти Форрестера

«Найти Форрестера» (англ. Finding Forrester) — фильм Гаса Ван Сэнта 2000 года. Писатель Уильям Форрестер долгие годы прожил в добровольном затворничестве от мира.

ru.wikipedia.org

Найти и уничтожить

Найти и уничтожить, в оригинале Search and destroy или Seek and destroy, — наступательная стратегия, разработанная в середине 1960-х годов в начале Вьетнамской войны главнокомандующим американскими силами Уильямом Уэстморлендом.

ru.wikipedia.org

Найти и обезвредить

«Найти и обезвредить» — советский художественный фильм, поставленный на Свердловской киностудии в 1982 году режиссёром Георгием Кузнецовым. Три друга — Виктор, Дмитрий и Фёдор — едут в тайгу в отпуск.

ru.wikipedia.org

«НАЙТИ И ОБЕЗВРЕДИТЬ», СССР, Свердловская киностудия, 1982, цв., 87 мин. Приключенческий фильм. Приехав из Москвы в отпуск в небольшой сибирский поселок Семиреченск, молодые люди — Виктор, Федор…

Энциклопедия кино. – 2010

Найти и ликвидировать

«Найти и ликвидировать» («Найти и уничтожить») — кинофильм. Экранизация произведения, автор которого — Хауард Кордер. Фильм рассказывает историю Мартина Миркхайма, жизнь которого приходит к полному краху: его брак разваливается на глазах…

ru.wikipedia.org

Хорошего человека найти нелегко

«Хорошего человека найти нелегко» — рассказ написанный Фланнери О’Коннор в 1953 году. Внимание критиков часто фокусируется на неоднозначной финальной сцене.

ru.wikipedia.org

Русский язык

Най/ти́¹ (отыскать).

Морфемно-орфографический словарь. — 2002

Примеры употребления слова найти

Она просит всех женщин найти время для визита к специалистам и пройти обследование.

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

Счастливые обладатели собственных велосипедов могут найти парковки по всему городу.

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

Российским ученым удалось найти хорошую тушу и настоящую кровь мамонта.

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

К тому времени Кривбасс может подать апелляцию на решение и найти себе нового спонсора.

Мы не можем найти денег, хотя я сделал эскиз и все, что можно было сделать с моей стороны.

На самом деле для меня было важно как раз попытаться найти спокойный контроль.

Однако, найти компромисс в сумме отступных с “Шахтером” римляне так и не смогли.


  1. найробийский
  2. найроби
  3. найтись
  4. найти
  5. найтовить
  6. найтов
  7. най

wordhelp.ru

Поиск на листе Excel, примеры использования Find на VBA

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

 

Поиск перебором значений

Довольно простой в реализации способ. Например, найти в колонке “A” ячейку, содержащую “123” можно примерно так:


Sheets("Данные").Select
For y = 1 To Cells.SpecialCells(xlLastCell).Row
    If Cells(y, 1) = "123" Then
        Exit For
    End If
Next y
MsgBox "Нашел в строке: " + CStr(y)

Минусами этого так сказать “классического” способа являются: медленная работа и громоздкость. А плюсом является его гибкость, т.к. таким способом можно реализовать сколь угодно сложные варианты поиска с различными вычислениями и т.п.

Поиск функцией Find

Гораздо быстрее обычного перебора и при этом довольно гибкий. В простейшем случае, чтобы найти в колонке A ячейку, содержащую “123” достаточно такого кода:


Sheets("Данные").Select
Set fcell = Columns("A:A").Find("123")
If Not fcell Is Nothing Then
    MsgBox "Нашел в строке: " + CStr(fcell.Row)
End If

Вкратце опишу что делают строчки данного кода:
1-я строка: Выбираем в книге лист “Данные”;
2-я строка: Осуществляем поиск значения “123” в колонке “A”, результат поиска будет в fcell;
3-я строка: Если удалось найти значение, то fcell будет содержать Range-объект, в противном случае – будет пустой, т.е. Nothing.

Полностью синтаксис оператора поиска выглядит так:

Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

What – Строка с текстом, который ищем или любой другой тип данных Excel

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

LookIn – Тип искомых данных. Может принимать одно из значений: xlFormulas (формулы), xlValues (значения), или xlNotes (примечания).

LookAt – Одно из значений: xlWhole (полное совпадение) или xlPart (частичное совпадение).

SearchOrder – Одно из значений: xlByRows (просматривать по строкам) или xlByColumns (просматривать по столбцам)

SearchDirection – Одно из значений: xlNext (поиск вперед) или xlPrevious (поиск назад)

MatchCase – Одно из значений: True (поиск чувствительный к регистру) или False (поиск без учета регистра)

MatchByte – Применяется при использовании мультибайтных кодировок: True (найденый мультибайтный символ должен соответствовать только мультибайтному символу) или False (найденый мультибайтный символ может соответствовать однобайтному символу)

SearchFormat – Используется вместе с FindFormat. Сначала задается значение FindFormat (например, для поиска ячеек с курсивным шрифтом так: Application.FindFormat.Font.Italic = True), а потом при использовании метода Find указываем параметр SearchFormat = True. Если при поиске не нужно учитывать формат ячеек, то нужно указать SearchFormat = False.

Чтобы продолжить поиск, можно использовать FindNext (искать “далее”) или FindPrevious (искать “назад”).

Примеры поиска функцией Find

Пример 1: Найти в диапозоне “A1:A50” все ячейки с текстом “asd” и поменять их все на “qwe”


With Worksheets(1).Range("A1:A50")
  Set c = .Find("asd", LookIn:=xlValues)
  Do While Not c Is Nothing
    c.Value = "qwe"
    Set c = .FindNext(c)
  Loop
End With

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

Пример 2: Правильный поиск значения с использованием FindNext, не приводящий к зацикливанию.


With Worksheets(1).Range("A1:A50")
  Set c = .Find("asd", lookin:=xlValues)
  If Not c Is Nothing Then
    firstResult = c.Address
    Do
      c.Font.Bold = True
      Set c = .FindNext(c)
    Loop While Not c Is Nothing And c.Address <> firstResult
  End If
End With

В ниже следующем примере используется другой вариант продолжания поиска – с помощью той же функции Find с параметром After. Когда найдена очередная ячейка, следующий поиск будет осуществляться уже после нее. Однако, как и с FindNext, когда будет достигнут конец диапазона, Find продолжит поиск с его начала, поэтому, чтобы не произошло зацикливания, необходимо проверять совпадение с первым результатом поиска.

Пример 3: Продолжение поиска с использованием Find с параметром After.


With Worksheets(1).Range("A1:A50")
  Set c = .Find("asd", lookin:=xlValues)
  If Not c Is Nothing Then
    firstResult = c.Address
    Do
      c.Font.Bold = True
      Set c = .Find("asd", After:=c, lookin:=xlValues)
    Loop While Not c Is Nothing And c.Address <> firstResult
  End If
End With

Следующий пример демонстрирует применение SearchFormat для поиска по формату ячейки. Для указания формата необходимо задать свойство FindFormat.

Пример 4: Найти все ячейки с шрифтом “курсив” и поменять их формат на обычный (не “курсив”)


lLastRow = Cells.SpecialCells(xlLastCell).Row
lLastCol = Cells.SpecialCells(xlLastCell).Column
Application.FindFormat.Font.Italic = True
With Worksheets(1).Range(Cells(1, 1), Cells(lLastRow, lLastCol))
  Set c = .Find("", SearchFormat:=True)
  Do While Not c Is Nothing
    c.Font.Italic = False
    Set c = .Find("", After:=c, SearchFormat:=True)
  Loop
End With

Примечание: В данном примере намеренно не используется FindNext для поиска следующей ячейки, т.к. он не учитывает формат (статья об этом: https://support.microsoft.com/ru-ru/kb/282151)

Коротко опишу алгоритм поиска Примера 4. Первые две строки определяют последнюю строку (lLastRow) на листе и последний столбец (lLastCol). 3-я строка задает формат поиска, в данном случае, будем искать ячейки с шрифтом Italic. 4-я строка определяет область ячеек с которой будет работать программа (с ячейки A1 и до последней строки и последнегостолбца). 5-я строка осуществляет поиск с использованием SearchFormat. 6-я строка – цикл пока результат поиска не будет пустым. 7-я строка – меняем шрифт на обычный (не курсив), 8-я строка продолжаем поиск после найденной ячейки.

Хочу обратить внимание на то, что в этом примере я не стал использовать “защиту от зацикливания”, как в Примерах 2 и 3, т.к. шрифт меняется и после “прохождения” по всем ячейкам, больше не останется ни одной ячейки с курсивом.

Свойство FindFormat можно задавать разными способами, например, так:


With Application.FindFormat.Font 
  .Name = "Arial" 
  .FontStyle = "Regular" 
  .Size = 10 
End With

Следующий пример – применение функции Find для поиска последней ячейки с заполненными данными. Использованные в Примере 4 SpecialCells находит последнюю ячейку даже если она не содержит ничего, но отформатирована или в ней раньше были данные, но были удалены.

Пример 5: Найти последнюю колонку и столбец, заполненные данными


Set c = Worksheets(1).UsedRange.Find("*", SearchDirection:=xlPrevious)
If Not c Is Nothing Then
  lLastRow = c.Row: lLastCol = c.Column 
Else
  lLastRow = 1: lLastCol = 1
End If
MsgBox "lLastRow=" & lLastRow & " lLastCol=" & lLastCol

В этом примере используется UsedRange, который так же как и SpecialCells возвращает все используемые ячейки, в т.ч. и те, что были использованы ранее, а сейчас пустые. Функтция Find ищет ячейку с любым значением с конца диапазона.

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

Пример 6: Выделить красным шрифтом ячейки, в которых текст начинается со слова из 4-х букв, первая и последняя буквы “т”, при этом после этого слова может следовать любой текст.


With Worksheets(1).Cells
  Set c = .Find("т??т*", LookIn:=xlValues, LookAt:=xlWhole)
  If Not c Is Nothing Then
    firstResult = c.Address
    Do
      c.Font.Color = RGB(255, 0, 0)
      Set c = .FindNext(c)
    Loop While Not c Is Nothing And c.Address <> firstResult
  End If
End With

Для поиска функцией Find по маске (шаблону) можно применять символы:
* – для обозначения любого количества любых символов;
? – для обозначения одного любого символа;
~ – для обозначения символов *, ? и ~. (т.е. чтобы искать в тексте вопросительный знак, нужно написать ~?, чтобы искать именно звездочку (*), нужно написать ~* и наконец, чтобы найти в тексте тильду, необходимо написать ~~)

 

www.codernotes.ru