Как в excel найти формулу: Вычисление вложенной формулы по шагам

Содержание

Как в таблице Excel найти все ячейки с формулами — Трюки и приемы в Microsoft Excel

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

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

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

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

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

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

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

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

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

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

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

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

Поиск и исправление ошибок в формулах MS Excel

Главная » Табличный редактор Excel » Поиск и исправление ошибок в формулах MS Excel

 

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

К счастью, в наших руках несколько отличных инструментов для поиска “хитрых” ошибок в формулах MS Excel.

Влияющие и зависимые ячейки в MS Excel

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

Именно с этой точки зрения все ячейки в MS Excel разделяются на влияющие и зависимые. Различить и запомнить их просто:

  • Влияющие ячейки, это ячейки на которые ссылается формула (т.е. если формула это А+Б, то данные в ячейках А и Б – это данные влияющие на результат вычисления формулы).
  • Зависимые – содержат формулу влияющую на содержимое ячейки (т.е. если формула В+Г берет данные по В из ячейки содержащей не число, а результат вычисления А+Б, то ячейка с формулой В+Г, будет по отношению к ней зависимой, т.к. от правильности работы А+Б зависит результат вычисления в В+Г).

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

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

Дополнительно я создал ещё одну простую формулу: она умножает наш “Итог” на некую постоянную поправку, которую я задал прямо в формуле вручную: Итог х 0,6.

Давайте перейдем на вкладку “Формулы” и в группе “Зависимости формул” посмотрим на два крайне полезных в работе инструмента: “Влияющие ячейки” и “Зависимые ячейки”.

Определяем влияющие ячейки в Excel. Влияющие они естественно на вычисления происходящие в данной ячейке

Выделяю результирующую ячейку “Итог” и нажимаю кнопку “Влияющие ячейки”. Оп, и на листе MS Excel появляются синие стрелки ведущие от трех используемых в вычислениях ячеек к итоговой формуле. Согласитесь, нагляднее представить себе понятие “влияющая ячейка” невозможно.

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

Теперь нажимаю (не убирая курсор с ячейки “итоги”) кнопку “Зависимые ячейки” и на экране появляется ещё одна стрелка. Она ведет к ячейке “результат с поправкой”, то есть той, результат вычислений в которой зависит от текущей.

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

Ошибка возникшая из-за замены цифры на букву. Excel подсветил “ошибочное” вычисление красной стрелкой

Отключить графику можно в любой момент нажав на кнопку “Убрать стрелки”.

Чтобы убрать стрелки с листа MS Excel воспользуйтесь соответствующей кнопкой

Исправление ошибок возникающих в MS Excel

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

Ищем ошибку в формуле Excel

Снова выделим наш пресловутый “итог” и щелкнем мышью по кнопке “Вычислить формулу”. Открывшееся окно содержит в себе поле в котором записана “проблемная” формула. Пока ничего не обычного, верно? Нажимаем на кнопку “Вычислить” внизу и обращаем внимание как формула в окне начнет меняться – вместо идентификаторов ячеек в ней появятся цифры из соответствующих ячеек.

А вот и ошибка – как видите, программа ясно дает понять, что проблема возникает ещё до умножения, то есть на этапе сложения показателей

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

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

Теги: excel, Основы работы

 

Также вас может заинтересовать:

    Как использовать функцию ПОИСК Excel

    Если вы хотите найти символ или группу символов в Excel, функция ПОИСК Excel может быть первым вариантом, к которому вы инстинктивно тянетесь, и не без оснований.

    НАЙТИ ищет одну текстовую строку в другой текстовой строке и возвращает номер позиции первого символа в искомой строке.

    Ниже приведены несколько указаний, о которых следует помнить, и несколько советов по работе с функцией ПОИСК в Excel.

    Синтаксис

    Синтаксис функции НАЙТИ следующий:

      =НАЙТИ(найти_текст,внутри_текста,[начальный_номер])
    • Find_text — это подстрока или символ, который вы хотите найти.
    • Within_text — это ссылка на ячейку или текстовая строка, в которой вы будете искать свои символы.
    • Start_num (необязательный аргумент) — это номер позиции символа, с которого должен начинаться ваш поиск. Если этот аргумент опущен, НАЙТИ начинает поиск с первого символа строки.

    Примечания

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

    Загрузите бесплатный файл практики!

    Используйте этот бесплатный файл Excel, чтобы попрактиковаться вместе с учебным пособием.

    Введите адрес электронной почты

    Базовое приложение

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

    В ячейках A2 и A3 подстроки «Слово» и «слово» встречаются в одной текстовой строке. Поскольку ПОИСК чувствителен к регистру, find_text Аргумент «слово» приводит к тому, что формула пропускает подстроку «Слово» и размещает «слово» на 37-й позиции. При изменении find_text на «Word» Excel возвращает значение 11.

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

      =НАЙТИ(B2,A2)

    Формула в ячейке C3:

      =НАЙТИ(B3,A3)

    Обратите внимание, что при использовании ссылок на ячейки мы не используем двойные кавычки, поскольку мы не пытаемся найти буквальное значение «B2» или «B3».

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

    Сочетание НАЙТИ с другими функциями

    НАЙТИ можно комбинировать с другими текстовыми функциями Excel, такими как ДЛСТР, ОБРЕЗАТЬ, ПОДСТАВИТЬ, ЗАМЕНИТЬ, ВЛЕВО, ВПРАВО и СРЕДНИЙ, чтобы извлекать, разделять или заменять определенные элементы из текстовых строк. Исходный текст и/или желаемый результат могут иметь фиксированную или переменную длину. Те, которые имеют переменную длину, обычно требуют некоторого творческого мышления, поэтому полезно знать тип значения, возвращаемого для каждой функции, чтобы определить комбинацию, которая даст желаемый результат.

    Краткое описание этих функций показано ниже.

    Функция

    Синтаксис

    Возвращаемое значение

    ЛЕН

    ДЛСТР(текст)

    Номер символов

    ТРИМ

    ОТДЕЛКА(текст)

    Текст без начальных и конечных пробелов

    ЗАМЕНА

    ПОДСТАВИТЬ(текст, старый_текст, новый_текст, [номер_экземпляра])

    Текст , подставив заданные значения

    ЗАМЕНИТЬ

    ЗАМЕНИТЬ (старый_текст, начальный_номер, число_символов, новый_текст)

    Текст, путем замены одной текстовой строки другой

    ЛЕВЫЙ

    ЛЕВЫЙ(текст, [число_знаков])

    Текст — крайний левый символ(ы)

    СПРАВА

    ПРАВО (текст, [число_знаков])

    Текст — крайний правый символ(ы)

    СРЕДНЯЯ

    MID(текст, start_num,num_chars)

    Номер позиции указанного текста (поиск с учетом регистра)

    Заменить подстроку, если она присутствует

    ПОИСК часто встраивается в функцию ЗАМЕНИТЬ, чтобы найти подстроку внутри другой и затем заменить ее чем-то другим. Синтаксис функции ЗАМЕНИТЬ:

    .
  =ЗАМЕНИТЬ(старый_текст, начальный_номер, число_знаков,новый_текст)
    • Старый_текст — это полная текстовая строка, содержащая заменяемую подстроку.
    • Start_num — номер начальной позиции заменяемого текста.
    • Num_chars — длина заменяемого текста.
    • New_text — это текст, который заменит удаляемые символы.

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

      =ЗАМЕНИТЬ(A2,НАЙТИ("song",A2),4,"poem")

    Поскольку точное расположение подстроки «песня» в текстовой строке неизвестно, номер позиции возвращается в результате Формула НАЙТИ использовалась в качестве аргумента start_num функции ЗАМЕНИТЬ. С помощью этой формулы ячейки, не содержащие подстроку, возвращали ошибку #ЗНАЧ! ошибка. Этого можно избежать, добавив функцию ЕСЛИОШИБКА для возврата альтернативного результата.

    Синтаксис функции ЕСЛИОШИБКА:

      =ЕСЛИОШИБКА(значение, значение_если_ошибка)

    Используя комбинацию формул ЗАМЕНИТЬ/НАЙТИ в качестве аргумента значение функции ЕСЛИОШИБКА, Excel оценивает, приводит ли попытка найти подстроку к ошибке.

      =ЕСЛИОШИБКА(ЗАМЕНИТЬ(A2,НАЙТИ("песня",A2),4,"стихотворение"),A2)

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

    Извлечение части текста из текстовой строки большего размера

    Функция НАЙТИ часто сочетается с функциями ВЛЕВО, ПРАВО и СРЕДНЯЯ, чтобы извлечь часть текста, используя номер его позиции в текстовой строке большего размера.

    Извлечь крайние левые символы

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

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

      =ЛЕВЫЙ(текст, [число_символов])

    Поскольку функция НАЙТИ возвращает номер позиции символа или текстовой строки, результат функции НАЙТИ можно использовать в качестве аргумента число_знаков функции ЛЕВЫЙ. Обычно мы вычитаем из этого результата 1, чтобы получить «конечную» позицию строки, которую мы хотим извлечь.

      =ЛЕВСИМВ(A2,НАЙТИ("@",A2)-1)

    Результат этой формулы НАЙТИ становится аргументом num_chars формулы ЛЕВЫЙ.

    Извлечь крайние правые символы

    Чтобы извлечь доменное имя из адресов электронной почты, мы можем использовать вместе функции ПРАВО и ДЛСТР. Или, если проявить немного творчества, функцию MID. См. оба варианта ниже.

    Вариант 1

    Функция ДЛСТР подсчитывает количество символов в текстовой строке. Синтаксис:

      =ДЛСТР(текст)

    Функция ПРАВИЛЬНО начинает отсчет с крайнего правого символа и возвращает отсчет с крайнего правого символа. Синтаксис следующий:

      =ПРАВО(текст, [число_знаков])

    Следовательно, мы можем использовать предыдущую комбинацию формул ДЛСТР/НАЙТИ в качестве аргумента число_знаков формулы ПРАВИЛЬНО.

      =ВПРАВО(A2,ДЛСТР(A2)-НАЙТИ("@",A2))

    В приведенной выше формуле используется ПОИСК для получения номера позиции символа «@». Это вычитается из длины исходной строки, которая затем используется в качестве аргумента num_chars функции RIGHT.

    Вариант 2

    Второй вариант еще проще. Функция MID может захватывать символы с середины текстовой строки до конца. Синтаксис:

      =MID(text, start_num, num_chars)

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

      =MID(A2,FIND("@",A2)+1,100)

    Добавление 1 к номеру позиции символа «@» сообщает нам начальную позицию (или start_num ) функции MID.

    Определение наличия подстроки

    Функция ПОИСК может быть вложена в функцию ЕСЧИСЛО, чтобы проверить, присутствует ли определенная подстрока или нет. Это работает следующим образом: если НАЙТИ возвращает номер позиции, то формула ЕСЧИСЛО возвращает значение ИСТИНА. И если НАЙТИ возвращает #ЗНАЧ! ошибка, ISNUMBER вернет FALSE.

    Синтаксис функции ISNUMBER:

      =ЧИСЛО(значение)

    Таким образом, мы можем использовать формулу НАЙТИ в качестве аргумента формулы ЧИСЛО.

      =ЧИСЛО(НАЙТИ("xyz.net",A2))

    Приведенная выше формула определяет, содержит ли каждая текстовая строка подстроку «xyz.net». Если это не так, формула возвращает значение FALSE. Обратите внимание, что в приведенном выше примере формула, относящаяся к ячейкам A4 и A11, вернула ИСТИНА, а формула, относящаяся к ячейке A10, вернула ЛОЖЬ, поскольку НАЙТИ чувствителен к регистру. Когда регистр не имеет значения, лучше воспользоваться функцией ПОИСК.

    Для простого подсчета количества экземпляров результата TRUE поставьте два знака минус перед функцией ISNUMBER. Это приведет к тому, что формула вернет 1 или 0 для ИСТИНА или ЛОЖЬ соответственно. Эти результаты можно использовать с функцией СУММ для добавления количества адресов с доменом «xyz.net».

      =--ISNUMBER(НАЙТИ("xyz.net",A11))

    Мы можем сделать еще один шаг вперед, возвращая настраиваемые ответы с помощью функции ЕСЛИ.

    Функция ЕСЛИ оценивает логический оператор и возвращает один ответ, если оператор имеет значение ИСТИНА, и другой ответ, если оператор оценивается как ЛОЖЬ. С помощью этого принципа мы можем сказать Excel, что он должен делать, если ISNUMBER возвращает ответ TRUE, и что он должен делать в противном случае.

    Синтаксис функции ЕСЛИ:

      =ЕСЛИ(логическая_проверка, [значение_если_истина], [значение_если_ложь])

    Итак, мы можем сделать нашу комбинацию формул ЕЧИСЛО/НАЙТИ первым аргументом формулы ЕСЛИ.

      =ЕСЛИ(ЧИСЛО(НАЙТИ(2,A2,5)),"Да","Нет")

    В приведенном выше примере мы использовали все три аргумента функции НАЙТИ. Это позволило нам найти значение «2», начиная с 5-го значения.

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

    Начиная с 5-го символа, если найдено число «2», функция НАЙТИ возвращает номер позиции, в результате чего функция ЕЧИСЛО возвращает ИСТИНА. Функция ЕСЛИ вложена в это, так что, если ячейка действительно содержит желаемый текст, то «Да» возвращается для ИСТИННЫХ результатов и «Нет» для ЛОЖНЫХ результатов.

    Примечание : Поиск числового значения работает с двойными кавычками (или без них).

    FIND и FINDB

    Если вам интересно узнать о функции FINDB, обратите внимание, что единственная разница между FIND и FINDB заключается в том, что FINDB подсчитывает 2 байта на символ, когда установлен язык с двухбайтовым набором символов (DBCS). в качестве языка по умолчанию. Языки, поддерживающие DBCS, включают корейский, японский, китайский (упрощенный) и китайский (традиционный).

    В противном случае FINDB ведет себя так же, как FIND, считая 1 байт для каждого символа. По этой причине в этом ресурсе мы обсудили только ПОИСК.

    Другие способы поиска в Excel

    Функция ПОИСК в Excel помогает искать что-либо в определенной текстовой строке. Но если вы хотите найти элемент в таблице или наборе данных Excel, функции ВПР или ГПР могут больше подойти для этой задачи.

    Если у вас есть Excel 365, функция XLOOKUP еще более гибкая. Он может делать все, что могут предыдущие функции, и даже больше.

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

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

    Повысьте уровень своих навыков работы с Excel

    Станьте сертифицированным ниндзя Excel с небольшими курсами GoSkills

    Начать бесплатную пробную версию

    Найти функцию в Excel — формула, пример, как использовать?

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

    Например, формула =НАЙТИ(«w»,»подсолнух») возвращает 7. Это означает, что символ «w» находится на седьмой позиции текстовой строки «подсолнух».

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

    Функция НАЙТИ относится к категории текстовых функций Excel.

    Содержание
    • Что такое функция НАЙТИ в Excel?
      • Синтаксис функции НАЙТИ в Excel
      • Как использовать функцию НАЙТИ в Excel?
        • Пример № 1. Поиск одного символа в текстовой строке
        • Пример № 2. Подсчет количества вхождений одного символа в диапазоне
        • Пример № 3. Подсчет текстовых строк, заканчивающихся определенными символами
        • Пример № 4. Извлечение определенных символов из каждой ячейки диапазона
      • Актуальность и использование функции НАЙТИ в Excel
      • Ключевые моменты, связанные с функцией НАЙТИ в Excel
      • Часто задаваемые вопросы
      • Функция НАЙТИ в Excel Видео
      • Рекомендуемые статьи

    Синтаксис функции НАЙТИ в Excel

    Синтаксис функции НАЙТИ в Excel показан на следующем рисунке:

    Функция НАЙТИ в Excel принимает следующие аргументы:

    • Find_text: Это символ (или подстрока), положение которого необходимо найти. Он может быть указан как ссылка на ячейку, содержащую подстроку, или как прямая подстрока, заключенная в двойные кавычки.
    • Within_text: Это текстовая строка, в которой необходимо найти символ (или подстроку). Его можно передать либо непосредственно в функцию НАЙТИ, либо в виде ссылки на ячейку, содержащую текстовую строку. Заключите текстовую строку в двойные кавычки, если она передается непосредственно в функцию.
    • Start_num: Это символ, с которого должен начинаться поиск. Он предоставляется в виде числового значения для функции НАЙТИ. Например, если этот аргумент равен 3, поиск начинается с третьего символа аргумента «внутри_текста».

    Аргументы «find_text» и «within_text» обязательны, а «start_num» необязательны. Если аргумент «start_num» опущен, поиск начинается с первого символа аргумента «within_text».

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

    Давайте рассмотрим несколько примеров, чтобы понять работу функции НАЙТИ в Excel.

    Вы можете скачать этот шаблон Excel функции НАЙТИ здесь — Шаблон Excel функции НАЙТИ

    Пример № 1. Поиск одного символа в текстовой строке

    На следующем изображении показаны текстовая строка и подстрока в ячейках A3 и B3 соответственно. В текстовой строке (леопард) мы хотим найти подстроку (а) с помощью функции НАЙТИ в Excel. Предоставьте аргументы «find_text» и «within_text» как:

    1. Ссылки на ячейки
    2. Прямые строки

    Считайте аргумент «start_num» равным 1 в обоих случаях.

    а. Шаги по использованию функции НАЙТИ со ссылками на ячейки Ссылка на ячейку Ссылка на ячейку в Excel ссылается на другие ячейки в ячейку, чтобы использовать ее значения или свойства. Например, если у нас есть данные в ячейке A2 и мы хотим использовать их в ячейке A1, используйте =A2 в ячейке A1, и это скопирует значение A2 в A1. Подробнее перечислены ниже:

    Шаг 1: Введите ссылки на ячейки подстроки и текстовой строки (в указанной последовательности) для функции НАЙТИ. Итак, введите следующую формулу в ячейку C3.

    «=НАЙТИ(B3,A3)»

    Обратите внимание, что аргумент «start_num» опущен, так как он равен 1.

    Шаг 2: Нажмите клавишу «Enter». Появится вывод, как показано на следующем рисунке. Следовательно, буква «а» является пятой буквой слова «леопард».

    б. Шаги по использованию функции НАЙТИ с прямыми строками перечислены ниже:

    Шаг 1: Введите подстроку и текстовую строку (в указанной последовательности) непосредственно в функцию НАЙТИ. Итак, введите следующую формулу в ячейку C4.

    «=НАЙТИ(«a», «Leopard»)»

    Поскольку аргумент «start_num» равен 1, он был опущен.

    Шаг 2: Нажмите клавишу «Ввод». Выход в ячейке C4 равен 5. Это показано на следующем рисунке. Следовательно, независимо от того, введены ли первые два аргумента как ссылки на ячейки или прямые строки, вывод функции НАЙТИ будет одинаковым.

    Пример №2. Подсчитайте, сколько раз один символ встречается в диапазоне

    На следующем изображении показаны некоторые случайные текстовые строки в диапазоне A3:A6. Мы хотим найти, сколько раз символ (или подстрока) «i» появляется в этом диапазоне. Используйте функции Excel НАЙТИ, ЕЧИСЛО и СУММПРОИЗВ.

    Шаги для определения количества символов с помощью указанных функций перечислены ниже:

    Шаг 1: Введите следующую формулу в ячейку B3.

    «=СУММПРОИЗВ(- -(ЧИСЛО(НАЙТИ(«i»,A3:A6))))»

    Шаг 2: Нажмите клавишу «Ввод». Выход в ячейке B3 равен 3. Это показано на следующем рисунке. Следовательно, символ «i» появляется трижды в диапазоне A3:A6.

    Объяснение: В формуле, введенной на шаге 1, функция НАЙТИ обрабатывается первой, так как это самая внутренняя функция. Далее, функция ЕСЧИСЛОЧИСЛОЧИСЛО в Excel — это информационная функция, которая проверяет, является ли указанное значение ячейки числовым или нечисловым. Подробнее и СУММПРОИЗВСУММПРОИЗВ В ExcelФункция СУММПРОИЗВ умножает числа двух или более массивов и суммирует полученные продукты. обрабатывается больше функций. Вся формула работает следующим образом:

    • Функция НАЙТИ ищет символ «i» в каждой ячейке диапазона A3:A6. Этот символ находится в ячейках A3, A4 и A6. В ячейке A5 его нет. Если в ячейке найден символ «i», функция НАЙТИ возвращает его позицию. Однако, если этот символ не найден в ячейке, функция НАЙТИ возвращает «#ЗНАЧ!» ошибка. Итак, функция НАЙТИ возвращает массив из четырех значений {3;5;#ЗНАЧ!;5 }.
    • Массив, возвращаемый функцией НАЙТИ, становится аргументом функции ЕЧИСЛО. Если выходные данные функции НАЙТИ являются числовыми, ISNUMBER возвращает «истина». Однако, если выход функции НАЙТИ является ошибкой, функция ЕСЧИСЛО возвращает «ложь». Таким образом, ISNUMBER возвращает массив логических значений {ИСТИНА; ИСТИНА; ЛОЖЬ; ИСТИНА}.
    • Унарный оператор или двойной отрицательный символ (- -) преобразует каждый «истинный» и «ложный» результат функции ЕЧИСЛО в 1 и 0 соответственно. Итак, унарный оператор возвращает вертикальный массив из четырех чисел {1;1;0;1}.
    • Массив ({1;1;0;1}), возвращаемый унарным оператором, становится аргументом функции СУММПРОИЗВ. Поскольку это один массив чисел, СУММПРОИЗВ суммирует его. Итак, сумма, возвращаемая функцией СУММПРОИЗВ, равна 3.

    Следовательно, окончательный вывод данной формулы (введенной на шаге 1) равен 3.

    Обратите внимание, что три ячейки диапазона A3:A6 содержат один экземпляр символа «i». Однако если бы этот символ встречался несколько раз в ячейках A3, A4 или A6, функция НАЙТИ вернула бы его первый экземпляр. Таким образом, окончательный вывод по-прежнему будет равен 3.

    Примечание 1: Функция ЕСЧИСЛО проверяет, является ли значение (или ячейка, содержащая значение) числовым или нет. Функция СУММПРОИЗВ умножает числа двух или более массивов и суммирует полученные произведения. В случае одного массива функция СУММПРОИЗВ складывает числа и возвращает их сумму.

    Чтобы узнать о синтаксисе функций ЕСЧИСЛО и СУММПРОИЗВ, щелкните гиперссылки, приведенные в предыдущем объяснении.

    Примечание 2: Вместо функции НАЙТИ можно было бы использовать СЧЁТЕСЛИФункция СЧЁТЕСЛИ в Excel подсчитывает количество ячеек в диапазоне на основе предварительно заданных критериев. Он используется для подсчета ячеек, содержащих даты, числа или текст. Например, СЧЁТЕСЛИ(A1:A10,»Козырь») подсчитает количество ячеек в диапазоне A1:A10, содержащих текст «Козырь».
    читать дальше функция. Формула для подсчета символа «i» в диапазоне A3:A6 будет выглядеть так: СЧЁТЕСЛИ(A3:A6, «*i*»). Эта формула также вернула бы результат 3,9.0004

    Как и функция НАЙТИ, функция СЧЁТЕСЛИ также вернула бы 3 в случае многократного появления символа «i» в ячейках A3, A4 или A6. Однако функция СЧЁТЕСЛИ не чувствительна к регистру, в отличие от функции НАЙТИ.

    Пример №3. Подсчет текстовых строк, оканчивающихся на определенные символы

    На следующем рисунке показан список имен в столбце A. Нам нужно найти количество имен, оканчивающихся на «анш» или «анка» в диапазоне A3: А10. Используйте функции Excel НАЙТИ, ЕЧИСЛО и СУММПРОИЗВ.

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

    Шаг 1: Введите следующую формулу в ячейку B3.

    «=СУММПРОИЗВ(- -((ЧИСЛО(НАЙТИ(«ansh»,A3:A10))+ЧИСЛО(НАЙТИ(«anka»,A3:A10)))>0))

    Шаг 2: Нажмите клавишу «Ввод». Выход в ячейке B3 равен 4. Это показано на следующем рисунке.

    Таким образом, всего 4 имени заканчиваются на «анш» или «анка». Два имени оканчиваются на первую подстроку (Приянш и Дивянш), а два имени оканчиваются на вторую подстроку (Приянка и Дивянка).

    Объяснение: Формула, введенная на шаге 1, поясняется следующим образом:

    • Функция НАЙТИ обрабатывает каждое имя диапазона A3:A10. Если в ячейке присутствуют «анш» или «анка», функция НАЙТИ возвращает позицию первой буквы этих подстрок. Однако, если эти подстроки отсутствуют в ячейке, функция НАЙТИ возвращает «#ЗНАЧ!» ошибка. Итак, функция НАЙТИ возвращает два массива {#ЗНАЧ!;#ЗНАЧ!;5;5;#ЗНАЧ!;#ЗНАЧ!;#ЗНАЧ!; #ЗНАЧ!} и {5;5;#ЗНАЧ!;#ЗНАЧ!;#ЗНАЧ!;#ЗНАЧ!;#ЗНАЧ!;#ЗНАЧ!}.
    • ISNUMBER обрабатывает выходные данные, возвращаемые функцией НАЙТИ. Если выходные данные функции НАЙТИ являются числовыми, ISNUMBER возвращает «истина», в противном случае возвращает «ложь». Итак, функция ЕЧИСЛО возвращает два массива {ЛОЖЬ; ЛОЖЬ; ИСТИНА; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ} и {ИСТИНА; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ}.
    • Два массива, возвращаемые функцией ISNUMBER, складываются из-за помещенного между ними оператора «плюс». Массив, возвращаемый после сложения и применения выражения «больше нуля», равен {ИСТИНА; ИСТИНА; ИСТИНА; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ}.
    • Унарный оператор (- -) преобразует каждое значение «истина» и «ложь» предыдущего массива в 1 и 0 соответственно. Итак, унарный оператор возвращает итоговый массив из 8 значений {1;1;1;1;0;0;0;0}.
    • Функция СУММПРОИЗВ суммирует один массив, возвращаемый унарным оператором. Следовательно, эта функция возвращает результат 4.

    Примечание 1: Выражение «больше нуля» проверяет, больше ли каждый результат двух массивов (возвращаемых функцией ЕЧИСЛО) нуля или нет. Это выражение полезно, если в качестве вывода требуются логические значения (истина и ложь).

    Это выражение возвращает значение «истина», если любой из двух выходных параметров (из двух массивов, возвращаемых функцией ЕЧИСЛО) является числом. Он возвращает «false», если оба выхода являются ошибками.

    Примечание 2: Чтобы узнать больше о функциях ЕСЧИСЛО и СУММПРОИЗВ, обратитесь к гиперссылкам (в пояснении) или «примечанию 1» к предыдущему примеру (пример № 2).

    На следующем изображении показаны некоторые неполные операторы, содержащие символ решетки (#) в столбце C. Из каждого оператора мы хотим извлечь этот символ, слово, следующее за ним, и завершающий пробел в отдельном столбце. Используйте функции MID и FIND Excel.

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

    Шаг 1: Введите следующую формулу в ячейку D3.

     «=СРЕДН(C3,НАЙТИ(«#»,C3),НАЙТИ(» «,(СРЕД(C3,НАЙТИ(«#»,C3),ДЛСТР(C3)))))»

    Шаг 2 : Нажмите клавишу «Ввод». Вывод в ячейке D3: «#Свадьба». Он включает пробел в конце. Результат показан на следующем изображении.

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

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

    Объяснение: В данной формуле (введенной на шаге 1) обрабатывается каждая функция справа , за которой следует последующая функция слева. Функция LENФункция LENФункция Len возвращает длину заданной строки. Он вычисляет количество символов в заданной строке в качестве входных данных. Это текстовая функция в Excel, а также встроенная функция, доступ к которой можно получить, набрав =LEN( и введя строку в качестве входных данных. Читать далее, будучи самой внутренней (или самой правой), обрабатывается в первую очередь. Вся формула работает следующим образом. :

    • Функция ДЛСТР подсчитывает общее количество символов в ячейке C3. Таким образом, формула «=LEN(C3)» возвращает 27. Это число включает 23 буквы, 3 пробела и 1 символ решетки.
    • Затем функция НАЙТИ ищет позицию символа решетки в ячейке C3. Таким образом, формула «= НАЙТИ («#», C3)» возвращает 10. Это означает, что символ решетки находится на десятой позиции оператора, заданного в ячейке C3.
    • Выходные данные функций НАЙТИ и ДЛСТР становятся аргументами функции MIDФункция MIDФункция mid в Excel — это текстовая функция, которая находит строки и возвращает их из любой средней части электронной таблицы. прочитайте больше. Итак, функция MID обрабатывает формулу «=MID(C3,10,27)» и возвращает «#Свадьба в Джайпуре».
    • Выход функции MID становится аргументом функции FIND. Функция НАЙТИ ищет символ пробела в строке «#Свадьба в Джайпуре». Таким образом, формула «=НАЙТИ(« »,«#Свадьба в Джайпуре») возвращает 9. Это означает, что первый экземпляр пробела находится на девятой позиции заданной текстовой строки (#Свадьба в Джайпуре).
    • Формула «=НАЙТИ(«#»,C3)» снова возвращает 10.
    • Выходы двух функций НАЙТИ (в двух предыдущих указателях) становятся аргументами функции СРЕДН. Формула «=MID(C3,10,9)» возвращает строку, начинающуюся с десятого символа и состоящую всего из девяти символов. Таким образом, эта формула возвращает «#Wedding», включая один пробел в конце слова.

    Аналогично, выходные данные в ячейках D4 и D5 были возвращены Excel.

    Примечание 1: Функция ДЛСТР подсчитывает все символы в ячейке. Сюда входят буквы, цифры, пробелы и специальные символы. Функция MID помогает извлечь определенное количество символов из середины строки. Позиция начала извлечения может быть указана в функции MID.

    Чтобы узнать о синтаксисе функций LEN и MID, щелкните гиперссылки, приведенные в предыдущем объяснении.

    Примечание 2: В формуле «=MID(C3,10,27)» аргумент «начальное_число» равен 10, а аргумент «число_символов» равен 27. Если сумма этих двух аргументов превышает общую длину строки, функция MID возвращает символы, начиная с «start_num» и до конца всей строки.

    Следовательно, поскольку 37 («start_num» равно 10, а «num_chars» равно 27) превышает 27 (длина всей строки), функция MID возвращает символ, начиная с десятого места (#) до конца строки ( Джайпур). Итак, функция MID возвращает «#Свадьба в Джайпуре».

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

    Функция НАЙТИ в Excel полезна в следующих ситуациях:

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

    Важные моменты, связанные с функцией НАЙТИ Excel, перечислены ниже:

    • Функция НАЙТИ ищет первое вхождение «найти_текст» в аргументе «внутри_текста».
    • Аргументы «найти_текст» и «внутри_текста» можно указать как ссылки на ячейки или прямые текстовые строки. Чтобы предоставить эти аргументы в виде прямых текстовых строк, они должны быть заключены в двойные кавычки.
    • Если аргумент «найти_текст» содержит более одного символа, функция НАЙТИ возвращает позицию первого символа в аргументе «внутри_текста».
    • Если аргумент «find_text» представляет собой пустую строку («»), функция НАЙТИ возвращает 1.
    • Функция НАЙТИ чувствительна к регистру и не поддерживает использование подстановочных знаков.
    • Функция НАЙТИ возвращает значение «#ЗНАЧ!» ошибка в следующих случаях:
      • «Найти_текст» не найден в аргументе «внутри_текста».0021

    Часто задаваемые вопросы

    1. Определите функцию НАЙТИ в Excel.

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

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

    Примечание: Синтаксис функции НАЙТИ в Excel см. в разделе «Синтаксис функции НАЙТИ в Excel» этой статьи.

    2. Является ли функция ПОИСК в Excel чувствительной к регистру? Объясните с помощью примера.

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

    Например, формула =НАЙТИ(«м»,»улыбка») ищет букву «м» в нижнем регистре в текстовой строке «улыбка». Он возвращает 2, что означает, что «m» — вторая буква данной текстовой строки.

    Однако формула =НАЙТИ(«М»,»улыбка») ищет заглавную букву «М» в текстовой строке «улыбка». Он возвращает «#ЗНАЧ!» ошибка, так как «M» не может быть найден в данной текстовой строке. Если бы текстовая строка была представлена ​​как «SMILE», функция НАЙТИ снова вернула бы 2.

    Примечание: Для функции без учета регистра используйте ПОИСК вместо функции НАЙТИ в Excel.

    3. Как можно использовать функцию НАЙТИ с функцией ЛЕВЫЙ в Excel?

    Функция ВЛЕВО помогает извлечь указанное количество символов из крайнего левого края текстовой строки. Функции НАЙТИ и ЛЕВЫЙ можно использовать вместе для извлечения подстроки из левой части ячейки. Формула для него выглядит следующим образом:

    «=ЛЕВЫЙ(текстовая строка,НАЙТИ(символ,текстовая строка)-1)»

    «Текстовая строка» — это ссылка на ячейку, содержащую всю текстовую строку. Подстрока, предшествующая «символу», извлекается. «-1» гарантирует, что «символ» не будет включен в вывод.

    Таким образом, если подстроку «роза» необходимо извлечь из ячейки A1, содержащей «цветок розы», используется формула «=ЛЕВЫЙ(A1,НАЙТИ(» «,A1)-1)». «-1» в формуле помогает исключить конечный пробел извлекаемой подстроки. Если бы строки «роза» и «цветок» были разделены дефисом, мы бы использовали дефис («-») в качестве «символа».

    Примечание: Данная формула должна быть введена в Excel без начальных и конечных двойных кавычек.

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

     

    Рекомендуемые статьи

    Это руководство по функции НАЙТИ в Excel. Здесь мы обсудим, как использовать формулу НАЙТИ в Excel с пошаговыми примерами. Вы также можете посмотреть на эти полезные функции в Excel:

    • Excel Найти и выбратьExcel Найти и выбратьНайти и выбрать в Excel — это функция, доступная на вкладке «Главная» в Excel, которая помогает пользователю быстро найти определенный текст или значение в данном данные. Горячая клавиша для мгновенного использования этой функции — Ctrl+F.Подробнее
    • VBA FIND FunctionVBA FIND FunctionVBA Find дает точное совпадение аргумента. Он принимает три аргумента, один из которых — что найти, где найти и где посмотреть. Если какой-либо параметр отсутствует, он принимает существующее значение в качестве этого параметра. Подробнее
    • Функция строки в Excel Функция строки в Excel Функция строки в Excel — это функция рабочего листа, которая отображает номер индекса текущей строки выбранной или целевой ячейки.

Читайте также: