Newcomposers.ru

IT Мир
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Формула найти в excel

Функция НАЙТИ в Excel

Добрый день друзья!

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

Но сразу могу сказать, что в чистом виде функция НАЙТИ выглядит очень неприглядно и в принципе практически не используется. Самая распространенная возможность как используется функция НАЙТИ в Excel только в комбинированном виде, в соединение с такими функциями как ЛЕВСИМВ, ПРАВСИМВ и ПСТР. Такие комбинации позволят эффективно использовать все возможности функции НАЙТИ и выполнять более сложные задачи в сравнении с простым использованием.

Ну, а теперь давайте рассмотрим синтаксис, который имеет функция НАЙТИ в Excel:

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

  • текст, который ищем – это, как видно с аргумента, текст который нам нужно найти;
  • текст, который просматривается – это тот текст, ячейка или ссылка где функция будет искать;
  • [начальная позиция] – это аргумент, который показывает, с какой позиции должен начинаться поиск в просматриваемом тексте, если этот аргумент не указывать, то поиск, по умолчанию, начнется со значения 1.

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

Например, рассмотрим 2 примера для сравнения:

=НАЙТИ(«ВОДА»; «ВОДА вода»), нам вернет значение 1, а вот формула

=НАЙТИ(«вода»; «ВОДА вода»), вернёт позицию 6.

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

В случае, когда текст, который вы хотите найти не будет обнаружен, формула вернёт ошибку #ЗНАЧ!. Итак, собственно, давайте на примерах рассмотрим вариативность, как применяется функция НАЙТИ в Excel в простых и сложных примерах:

Простые примеры:

Рассмотрим применение только одной функции НАЙТИ, в нескольких вариациях и исполнениях. Возьмем, к примеру, текстовое значение «Нагаев Артём» и применим функцию:

=НАЙТИ(«А»;A2), в этой формуле мы ищем позицию первой буквы «А» в ячейке А2, и получаем результат «8»;

=НАЙТИ(«а»;A2), в этой формуле мы ищем позицию первой буквы «а» в ячейке А2, и получаем результат «2»;

=НАЙТИ(«а»;A2;3), в этой формуле мы ищем позицию первой буквы «а» в ячейке А2, но поиск начинаем с третьего знака и получаем результат «4». Комбинированные примеры:

Первым примером будет использование комбинации с помощью функции ПСТР. Например, мы имеем название продукции с номенклатурным номером «Светодиод №456217-485» и нам надо отделить зерна от плевы, то есть название детали от номера и для этого нужна формула:

=ПСТР(A4;1;НАЙТИ(« №»;A4;1) -1), в формуле мы находим позицию « №» и всё что до нее вытягивается соответствующей формулой, и мы получаем результат «Светодиод». Аргумент «-1» будет учитывать тот факт, что позицию, которую занимает текст, который мы ищем, также является входящим в диапазон подсчёта и к возвращению не подлежит. Вторым примером я использую комбинации с функциями ЛЕВСИМВ, ПРАВСИМВ. Например, в ячейке А5 у нас введена имя и фамилия «Нагаев Артём», в этом случае используя формулы:

=ЛЕВСИМВ(A5;НАЙТИ(СИМВОЛ(32);A5) -1), мы сможем извлечь фамилию,

=ПРАВСИМВ(A5;ДЛСТР(A5) -НАЙТИ(СИМВОЛ(32);A5)), извлечём имя. В случае, когда между двумя значениями имени и фамилии будут содержаться лишние пробелы, два и более, то для функционирования вышеуказанных формул необходимо использовать функцию СЖПРОБЕЛЫ.

На этом я думаю, что еще одну текстовую функции, вы смогли по достоинству оценить. Научились, или хотя бы разобрались, как и где используется функция НАЙТИ в Excel. Изучили ее полезность и варианты применения. Большее количество функций представлено для ознакомления в «Справочнике функций».

Если возникли вопросы, пишите комментарии, ставьте лайки, если вам понравилось.

До встречи в новых статьях!

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

Microsoft Excel

трюки • приёмы • решения

Как в таблице Excel найти все ячейки с формулами

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

Использование окна Выделение группы ячеек

Этот метод выявления ячеек с формулами легкий, но не динамический. Другими словами, он хорош для единичной проверки.

  1. Выберите одну ячейку в листе.
  2. Выберите Главная ► Редактирование ► Найти и выделить ► Выделение группы ячеек для открытия диалогового окна Выделение группы ячеек.
  3. В окне Выделение группы ячеек установите переключатель в положение формулы и убедитесь, что все флажки ниже установлены.
  4. Нажмите кнопку ОК. Excel выберет все ячейки с формулами.
  5. Нажмите кнопку Цвет заливки в группе Шрифт вкладки Главная. Выберите любой цвет, который еще не используется.
  6. Используйте элемент управления Масштаб и задайте для своего листа небольшой масштаб (например, 25%).
  7. Внимательно проверьте лист и посмотрите, какие ячейки остались невыделенными. Возможно, это формула, которая была перезаписана значением.
Читать еще:  Математические формулы в excel

Если вы не делали никаких изменений, то можете нажать кнопку Отменить (или нажать Ctrl+Z) для отмены цветовой заливки, которую применили в шаге 6.

Использование условного форматирования

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

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

  1. Выберите Формулы ► Определенные имена ► Присвоить имя для открытия диалогового окна Создание имени.
  2. В окне Создание имени введите следующую строку в поле Имя: CellHasFormula .
  3. Введите такую формулу в поле Диапазон: =ПОЛУЧИТЬ.ЯЧЕЙКУ(48;ДВССЫЛ(«rc»;ЛОЖЬ)) .
  4. Нажмите кнопку ОК, чтобы закрыть диалоговое окно Создание имени.
  5. Выделите все ячейки, к которым хотите применить условное форматирование. Как правило, они составляют диапазон от А1 до правого нижнего угла используемой области листа.
  6. Выберите Главная ► Стили ► Условное форматирование ► Создать правило для открытия диалогового окна Создание правила форматирования.
  7. В верхней части окна выберите пункт Использовать формулу для определения форматируемых ячеек.
  8. Введите следующую формулу в поле диалогового окна (рис. 196.1): =CellHasFormula .
  9. Нажмите кнопку Формат для открытия диалогового окна Формат ячеек и выберите тип форматирования для ячеек, содержащих формулу.
  10. Нажмите кнопку , чтобы закрыть окно Формат ячеек, и снова нажмите , чтобы закрыть окно Создание правила форматирования.

Рис. 196.1. Окно для установки условного форматирования для выделения ячеек с формулами

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

Формула, которую вы вводили в шаге 3, — макрос XLM. Следовательно, вам необходимо сохранить книгу с расширением с поддержкой макросов (используя расширение XLSM). Если вы сохраните книгу в виде XLSX-файла, Excel удалит имя CellHasFormula.

Функция FIND (НАЙТИ) в Excel. Как использовать?

Функция НАЙТИ (FIND) в Excel используется для поиска текстового значения внутри строчки с текстом и указать порядковый номер буквы с которого начинается искомое слово в найденной строке.

Что возвращает функция

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

Синтаксис

=FIND(find_text, within_text, [start_num]) – английская версия

=НАЙТИ(искомый_текст;просматриваемый_текст;[нач_позиция]) – русская версия

Аргументы функции

  • find_text (искомый_текст) – текст или строка которую вы хотите найти в рамках другой строки;
  • within_text (просматриваемый_текст) – текст, внутри которого вы хотите найти аргумент find_text (искомый_текст);
  • [start_num] ([нач_позиция]) – число, отображающее позицию, с которой вы хотите начать поиск. Если аргумент не указать, то поиск начнется сначала.

Дополнительная информация

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

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

Пример 1. Ищем слово в текстовой строке (с начала строки)

На примере выше мы ищем слово “Доброе” в словосочетании “Доброе Утро”. По результатам поиска, функция выдает число “1”, которое обозначает, что слово “Доброе” начинается с первой по очереди буквы в, заданной в качестве области поиска, текстовой строке.

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

Пример 2. Ищем слово в текстовой строке (с заданным порядковым номером старта поиска)

Третий аргумент функции НАЙТИ указывает позицию, с которой функция начинает поиск искомого значения. На примере выше функция возвращает число “1” когда мы начинаем поиск слова “Доброе” в словосочетании “Доброе утро” с начала текстовой строки. Но если мы зададим аргумент функции start_num (нач_позиция) со значением “2”, то функция выдаст ошибку, так как начиная поиск со второй буквы текстовой строки, она не может ничего найти.

Если вы не укажете номер позиции, с которой функции следует начинать поиск искомого аргумента, то Excel по умолчанию начнет поиск с самого начала текстовой строки.

Читать еще:  Как скопировать гиперссылку в excel

Пример 3. Поиск текстового значения внутри текстовой строки с дублированным искомым значением

На примере выше мы ищем слово “Доброе” в словосочетании “Доброе Доброе утро”. Когда мы начинаем поиск слова “Доброе” с начала текстовой строки, то функция выдает число “1”, так как первое слово “Доброе” начинается с первой буквы в словосочетании “Доброе Доброе утро”.

Но, если мы укажем в качестве аргумента start_num (нач_позиция) число “2” и попросим функцию начать поиск со второй буквы в заданной текстовой строке, то функция выдаст число “6”, так как Excel находит искомое слово “Доброе” начиная со второй буквы словосочетания “Доброе Доброе утро” только на 6 позиции.

НАЙТИ, НАЙТИБ (функции НАЙТИ, НАЙТИБ)

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

В этой статье описаны синтаксис формулы и использование функций НАЙТИ и НАЙТИБ в Microsoft Excel.

Описание

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

Эти функции могут быть доступны не на всех языках.

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

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

Функция НАЙТИБ при подсчете рассматривает каждый двухбайтовый знак как два знака, если включена поддержка языка с БДЦС и такой язык установлен по умолчанию. В противном случае функция НАЙТИБ рассматривает каждый знак как один знак.

К языкам, поддерживающим БДЦС, относятся японский, китайский (упрощенное письмо), китайский (традиционное письмо) и корейский.

Синтаксис

Аргументы функций НАЙТИ и НАЙТИБ описаны ниже.

Искомый_текст — обязательный аргумент. Текст, который необходимо найти.

Просматриваемый_текст — обязательный аргумент. Текст, в котором нужно найти искомый текст.

Начальная_позиция — необязательный аргумент. Знак, с которого нужно начать поиск. Первый знак в тексте «просматриваемый_текст» имеет номер 1. Если номер опущен, он полагается равным 1.

Замечания

Функции НАЙТИ и НАЙТИБ работают с учетом регистра и не позволяют использовать подстановочные знаки. Если необходимо выполнить поиск без учета регистра или использовать подстановочные знаки, воспользуйтесь функцией ПОИСК или ПОИСКБ.

Если в качестве аргумента «искомый_текст» задана пустая строка («»), функция НАЙТИ выводит значение, равное первому знаку в строке поиска (знак с номером, соответствующим аргументу «нач_позиция» или 1).

Искомый_текст не может содержать подстановочные знаки.

Если искомый_текст не отображается в просматриваемый_текст, функция найти и НАЙТИБ возвращают #VALUE! значение ошибки #ЧИСЛО!.

Если нач_позиция не больше нуля, найти и НАЙТИБ возвращают #VALUE! значение ошибки #ЧИСЛО!.

Если «нач_позиция» больше, чем длина просматриваемый_текст, найти и НАЙТИБ возвращают #VALUE! значение ошибки #ЧИСЛО!.

Аргумент «нач_позиция» можно использовать, чтобы пропустить нужное количество знаков. Предположим, например, что для поиска строки «МДС0093.МесячныеПродажи» используется функция НАЙТИ. Чтобы найти номер первого вхождения «М» в описательную часть текстовой строки, задайте значение аргумента «нач_позиция» равным 8, чтобы поиск в той части текста, которая является серийным номером, не производился. Функция НАЙТИ начинает со знака 8, находит искомый_текст в следующем знаке и возвращает число 9. Функция НАЙТИ всегда возвращает номер знака, считая от левого края текста «просматриваемый_текст», а не от значения аргумента «нач_позиция».

Примеры

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Как искать в Excel: поиск слов и ячеек в таблицах

Программа Excel ориентирована на ускоренные расчеты. Зачастую документы здесь состоят из большого ко.

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

Поиск слов

Документы часто имеют много страниц, тогда встает вопрос о том, как в Еxcel найти слово. Сделать это иногда становится проблематично. Для упрощения этой задачи существует специальная функция поиска. Чтобы ею воспользоваться, необходимо выполнить следующий алгоритм действий:

Читать еще:  Конвектор видео в mpeg4

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

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

Существует также способ нестрогого поиска, который подходит для ситуаций, когда искомое слово помнится частично. Он предусматривает использование символов-заменителей (джокерные символы). В Excel их всего два:

  • «?» – подразумевает любой отдельно взятый символ;
  • «*» – обозначает любое количество символов.

Примечательно, при поиске вопросительного знака или знака умножения дополнительно впереди ставится тильда («

»). При поиске тильды, соответственно – две тильды.

Алгоритм неточного поиска слова:

  • запустить программу;
  • активировать страницу щелчком мыши;
  • зажать комбинацию клавиш «Ctrl + F»;
  • в строке «Найти» появившегося окна ввести искомое слово, используя вместо букв, вызывающих сомнения, джокерные символы;
  • проверить параметр «Ячейка целиком» (он не должен быть отмеченным);
  • нажать «Найти все».

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

Поиск нескольких слов

Не зная, как найти слово в таблице в Еxcel, следует также воспользоваться функцией раздела «Редактирование» – «Найти и выделить». Далее нужно отталкиваться от искомой фразы:

  • если фраза точная, введите ее и нажмите клавишу «Найти все»;
  • если фраза разбита другими ключами, нужно при написании ее в строке поиска дополнительно проставить между всеми словами «*».

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

Поиск ячеек

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

Для поиска ячеек с формулами выполняются следующие действия.

  1. В открытом документе выделить ячейку или диапазон ячеек (в первом случае поиск идет по всему листу, во втором – в выделенных ячейках).
  2. Во вкладке «Главная» выбрать функцию «Найти и выделить».
  3. Обозначить команду «Перейти».
  4. Выделить клавишу «Выделить».
  5. Выбрать «Формулы».
  6. Обратить внимание на список пунктов под «Формулами» (возможно, понадобится снятие флажков с некоторых параметров).
  7. Нажать клавишу «Ок».

Для поиска объединенных ячеек потребуется выполнение следующих манипуляций.

  1. Перейти во вкладку «Главная».
  2. Выбрать функцию «Найти и выделить».
  3. Нажать на команду «Найти».
  4. Перейти в «Параметры» и выбрать «Формат».
  5. Здесь выделить функцию «Выравнивание», поставить отметку «Объединить ячейки».
  6. Нажать на «Ок».
  7. Нажать на кнопку «Найти все» и проанализировать список ячеек, которые объединены на соответствующем листе.

При нажимании кнопкой мыши на элемент в списке происходит выделение объединенной ячейки на листе. Дополнительно доступна функция «Отменить объединение ячеек».

Выполнение представленных выше действий приводит к нахождению всех объединенных ячеек на листе и при необходимости отмене данного свойства. Для поиска скрытых ячеек проводятся следующие действия.

  1. Выбрать лист, требующий анализа на присутствие скрытых ячеек и их нахождения.
  2. Нажать клавиши «F5_гт_ Special».
  3. Нажать сочетание клавиш «CTRL + G_гт_ Special».

Можно воспользоваться еще одним способом для поиска скрытых ячеек:

  1. Открыть функцию «Редактирование» во вкладке «Главная».
  2. Нажать на «Найти».
  3. Выбрать команду «Перейти к разделу». Выделить «Специальные».
  4. Попав в группу «Выбор», поставить галочку на «Только видимые ячейки».
  5. Нажать кнопку «Ок».

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

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

Чтобы найти ячейки, для которых применено условное форматирование:

  • нажать на ячейку, не предусматривающую условное форматирование;
  • выбрать функцию «Редактирование» во вкладке «Главная»;
  • нажать на кнопку «Найти и выделить»;
  • выделить категорию «Условное форматирование».

Чтобы найти ячейки, для которых применено одинаковое условное форматирование:

  • выбрать ячейку, предусматривающую условное форматирование, требующую поиска;
  • выбрать группу «Редактирование» во вкладке «Главная»;
  • нажать на кнопку «Найти и выделить»;
  • выбрать категорию «Выделить группу ячеек»;
  • установить свойство «Условные форматы»;
  • напоследок нужно зайти в группу «Проверка данных» и установить аналогичный пункт.

Поиск через фильтр

Чтобы узнать, как в Еxcel найти слово при использовании фильтра, потребуется изучить следующий алгоритм действий:

  • выделить заполненную ячейку;
  • во вкладке «Главная» выбрать функцию «Сортировка»;
  • нажать на кнопку «Фильтр»;
  • открыть выпадающее меню;
  • ввести искомый запрос;
  • нажать кнопку «Ок».

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

Ссылка на основную публикацию
Adblock
detector