С какого знака начинается формула в excel: Обзор формул — Служба поддержки Майкрософт

Ввод формул | Ваш персональный компьютер

Содержимое ячейки воспринимается программой Excel как формула, если оно начинается со знака «=». Формула может содержать числовые константы, функции Excel и ссылки на ячейки. Ввод формулы заканчивается нажатием клавиши <Enter> или щелчком на кнопке Ввод в строке формул. В ячейке выводится результат вычисления, а при активизации ячейки в строке формул отображается введенная формула.

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

Правило использования формул в программе Excel состоит в том, что если вычисляемое значение зависит от других ячеек таблицы, то всегда следует использовать формулу со ссылками на эти ячейки. Ссылка задается указанием адреса ячейки. На рисунке 1 показан пример вычисления в ячейке С2 по формуле: = A2*B2

Рис. 1

Ссылку на ячейку можно задать двумя способами:

1. ввести адрес ячейки с клавиатуры;

2. по ходу ввода формулы щелкать на нужной ячейке.

Второй способ является более быстрым и удобным.

Так для ввода указанной формулы, следует последовательно выполнить следующие действия:

1. активизировать ячейку С2;

2. ввести с клавиатуры знак “=”;

3. щелкнуть в ячейке А2;

4. ввести с клавиатуры знак “_*“;

5. щелкнуть в ячейке В2;

6. нажать <Enter>.

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

Применение операторов в формулах

Операторами обозначаются операции, которые следует выполнить над операндами формулы. В Microsoft Excel включено четыре вида операторов: арифметические, текстовые, а также операторы сравнения и адресные операторы. пока рассмотрим только арифметические операции.

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

По умолчанию, Microsoft Excel вычисляет формулу слева направо, начиная вычисление со знака равенства (=). Используя синтаксис написания формулы можно управлять процессом вычисления. Например, следующая формула возвращает число 11, так как умножение имеет больший приоритет над сложением и, поэтому, выполняется в первую очередь: сначала происходит умножение 2 на 3 (результат умножения равен 6), а затем полученное значение складывается с 5.

=5+2*3

Если для изменения синтаксиса воспользоваться скобками, то сначала произойдет сложение 5 и 2, а затем умножение полученного результата на 3. Следующая формула вернет число 21.

=(5+2)*3

Редактирование формул

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

Изменение ссылки в формуле:

• выделить в строке формул адрес ячейки двойным щелчком;
• отщелкнуть в таблице ячейку, на которую должна быть ссылка.

Изменение типа адресации:

• выделить адрес ячейки двойным щелчком;
• нажать клавишу <F2>.

Для подтверждения внесенных изменений использовать клавишу <Enter>или кнопку Ввод в строке формул; для отмены изменений – клавишу <Esc> или кнопкуОтмена в строке формул.

Задача №7. Электронные таблицы. Абсолютная и относительная адресация. Графики и диаграммы.

Автор материалов — Лада Борисовна Есакова.

Microsoft Excel (в дальнейшем просто — Excel) — это программа выполнения расчетов и управления так называемыми электронными таблицами.

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

Важной особенностью использования электронной таблицы является автоматический пересчет результатов при изменении значений ячеек. ). Формула начинается знаком =.

В Excel предусмотрены стандартные функции, которые могут быть использованы в формулах. Это математические, логические, текстовые, финансовые и другие функции. Однако, на экзамене Вам могут встретиться только самые простые функции: СЧЕТ (количество непустых ячеек), СУММ (сумма), СРЗНАЧ (среднее значение), МИН (минимальное значение), МАКС (максимальное значение).

Диапазон ячеек обозначается следующим образом: A1:D4 (все ячейки прямоугольника от A1 до D4.

Адреса ячеек бывают относительными, абсолютными и смешанными.

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

Относительная адресация:

Если в ячейке B2 мы напишем формулу =D1+3, то таблица воспримет это как «взять значение ячейки на две правее и на одну выше текущей, и прибавить к нему 3».

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

Абсолютная адресация:

Если нам не нужно, чтобы адрес пересчитывался при копировании формулы, мы можем его «закрепить» в формуле — поставить знак $ перед буквой и индексом ячейки: =$D$1+3. Такой адрес называется абсолютным. Такая формула не будет изменяться при копировании:

Смешанная адресация:

Если же мы хотим, чтобы при копировании формулы автоматически пересчитывался, к примеру, только индекс ячейки, а буква оставалась неизменной, мы можем «закрепить» в формуле только букву (или наоборот):  =$D1+3. Такой адрес называется смешанным. При копировании формулы будет меняться только индекс в адресе ячейки:

Электронные таблицы. Копирование формул.

Пример 1.

В ячейке C2 записана формула =$E$3+D2. Какой вид приобретет формула, после того как ячейку C2 скопируют в ячейку B1?

1) =$E$3+C1   2) =$D$3+D2      3) =$E$3+E3      4) =$F$4+D2

Решение:

Место расположения формулы меняется с C2 на B1, т.е. формула сдвигается на одну ячейку влево и на одну ячейку вверх (буква «уменьшается» на единицу и индекс уменьшается на единицу). Значит, так же изменятся все относительные адреса, а абсолютные (закрепленные знаком $) останутся неизменными:

=$E$3+С1.

Ответ: 1

Пример 2.

В ячейке В11 электронной таблицы записана формула. Эту формулу скопировали в ячейку А10. В результате значение в ячейке А10 вычисляется по формуле х—Зу, где х — значение в ячейке С22, а у — значение в ячейке D22. Укажите, какая формула могла быть написана в ячейке В11.

1) =C22-3*D22          2) =D$22-3*$D23      3) =C$22-3*D$22      4) =$C22-3*$D22

Решение:

Проанализируем поочередно каждую формулу:

Место расположения формулы меняется с B11 на A10, т. е. буква «уменьшается» на 1 и индекс уменьшается на 1.

Тогда при копировании формулы изменятся следующим образом:

1)      =B21-3*C21

2)      =C$22-3*$D22

3)      =B$22-3*C$22

4)      =$C21-3*$D21

Условию задачи соответствует формула 2).

Ответ: 2

Электронные таблицы. Определение значения формулы.

Пример 3.

Дан фрагмент электронной таблицы:

В ячейку D1 введена формула =$А$1*В1+С2, а затем скопирована в ячейку D2. Какое значение в результате появится в ячейке D2?

1) 10   2) 14    3) 16    4) 24

Решение:

Место расположения формулы меняется с D1 на D2, т.е. буква не меняется, а индекс увеличивается на 1.

Значит, формула примет вид: =$А$1*В2+С3. Подставим в формулу числовые значения ячеек:1*5+9=14. Правильный ответ указан под номером 2.

Ответ: 2

Пример 4.

В электронной таблице значение формулы =СРЗНАЧ(A6:C6) равно (-2). Чему равно значение формулы =СУММ(A6:D6), если значение ячейки D6 равно 5?

1) 1                    2) -1                     3) -3                     4) 7

Решение:

По определению среднего значения:

СРЗНАЧ(A6:C6) = СУММ(A6:С6)/3 = -2

Значит, СУММ(A6:С6) = -6

СУММ(A6:D6) = СУММ(A6:С6)+D6 = -6+5 = -1

Ответ: 2

Электронные таблицы и диаграммы.

Пример 5.

Дан фрагмент электронной таблицы в режиме отображения формул.

После выполнения вычислений построили диаграмму по значениям диапазона  A1:D1. Укажите полученную диаграмму:

Решение:

Вычислим по формулам значения ячеек A1:D1.

B1 = 3-2 =1

A1 = 2-1 =1

C1 = 1+2 =3

D1 = 1*3 =3

Этим данным соответствует диаграмма 3.

Ответ:3

Благодарим за то, что пользуйтесь нашими материалами.
Информация на странице «Задача №7. Электронные таблицы. Абсолютная и относительная адресация. Графики и диаграммы.» подготовлена нашими авторами специально, чтобы помочь вам в освоении предмета и подготовке к ЕГЭ и ОГЭ.
Чтобы успешно сдать нужные и поступить в ВУЗ или техникум нужно использовать все инструменты: учеба, контрольные, олимпиады, онлайн-лекции, видеоуроки, сборники заданий.
Также вы можете воспользоваться другими материалами из разделов нашего сайта.

Публикация обновлена:
08.03.2023

Как извлечь подстроки в Excel с помощью функций LEFT, RIGHT, MID

Перейти к содержимому Как извлечь подстроки в Excel с помощью функций LEFT, RIGHT, MID

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

Такие задачи даются легко!

Но когда у вас есть сотни или даже тысячи строк данных, ну…

…все становится совсем утомительно! 😱

К счастью для нас, Microsoft Excel предоставляет функции ВЛЕВО, ВПРАВО и СРЕДНЯЯ.

В этом уроке по Excel вы научитесь превращать это…

… в это!

Вы также узнаете, как полностью раскрыть свой потенциал, используя LEN и НАЙТИ!

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

Содержание

Приступим!

Открытая рабочая тетрадь.

Вы найдете контактные имена и контактные номера нескольких человек.

Это может быть список потенциальных клиентов компании. Или список контактов учителей для учеников.

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

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

Чтобы получить это, вы используете функцию ВЛЕВО.

Извлечение текстовой строки с помощью функции ВЛЕВО

Функция ВЛЕВО извлекает заданное количество символов, начиная с левой стороны строки.

1. Введите «=LEFT(» в ячейку C2, где вы хотите ввести первый код города .

2. Первый аргумент функции LEFT — text , это исходная текстовая строка.

Обычно, это ссылка на ячейку, и в данном случае это B2 .

Итак, введите:

=LEFT(B2

И поставьте запятую для разделения следующего аргумента.

3. Следующий ввод num_chars . Это количество символов слева от строки, которая будет извлечена. 0003

Если вы посчитаете символы кода города, включая круглые скобки, вы получите 5.

Итак, введите 5 и закройте формулу правой скобкой следующим образом:

=ЛЕВО(B2,5)

4 , Нажмите Enter.

5. Давайте заполним остальные строки, дважды щелкнув или перетащив маркер заполнения вниз.

Результат:

Отличная работа!

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

Извлечение текстовой строки с помощью функции ПРАВИЛЬНО

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

Имеет те же аргументы, text и num_chars , что и функция LEFT.

1. Вы хотите, чтобы первый номер Extension появился в ячейке D2, поэтому введите «= ПРАВИЛЬНО («».

Совет для профессионалов!

Excel автоматически предлагает формулы по мере ввода.

Например, вы можете просто ввести «=ri», и появится предложение для «=RIGHT(».

Нажмите клавишу Tab , чтобы ввести предложенную формулу.

2. Затем введите первый аргумент, который еще ячейка B2.

= ПРАВИЛЬНО (B2,

3. Затем подсчитайте количество символов справа, чтобы получить расширение.

Вы получите 3, поэтому ваша формула должна быть:

= ПРАВИЛЬНО (B2,3)

4. Нажмите Введите и снова заполните остальные строки, используя маркер заполнения.0003

Результат:

Хорошо!

А если нужен текст из средней части? Например, в данном случае это номер стационарного телефона .

Для этого у нас есть функция MID.

Извлечение текстовой строки с помощью функции MID

Функция Excel MID извлекает заданное количество символов, начиная с указанной позиции в исходной строке.

1. В ячейке E2 должен появиться первый номер стационарного телефона . Итак, введите «=MID(».

Вы можете скрыть столбец D.

2. Функция MID имеет тот же первый вход, что и LEFT и RIGHT, который является исходным текстом .

Итак, формула принимает следующий вид:

=MID(B2,

3. Второй вход функции MID: start_num .

Это начальная позиция, из которой будет извлечена подстрока.

3

Давайте посчитаем каждый символ в контактном номере :

Введите это в формулу для start_num :

=MID(B2,7,

3. Третьим входом функции MID является num_chars . Это количество символов из start_num , которое будет включено в выходную подстроку.

Из числа символов, показанного выше, вы знаете, что последняя цифра номера стационарного телефона находится на позиции 14.

Это означает, что весь номер стационарного телефона состоит из 8 символов. 0003

=MID(B2,7,8)

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

Результат:

Отлично сделано!

Теперь вы знаете, как использовать функции ВЛЕВО, ВПРАВО и СРЕДНИЙ!

Эти функции часто комбинируются с другими функциями Excel для создания более сложных формул.

Давайте рассмотрим несколько примеров ниже.

Извлечь подстроку до или после определенного символа

Теперь взгляните на Имена контактов .

Мы можем использовать LEFT для извлечения Заголовка , используемого для каждого человека.

Однако, поскольку «мистер»/«мисс» и «миссис». имеют разную длину строки, мы должны вводить num_chars динамически.

Если вы заметили, символ точки «.» является общим для всех заголовков.

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

Теперь нужно просто найти этот разделитель в заданном тексте! 🔎

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

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

1. Первый Заголовок должен появиться в ячейке F2. Итак, введите «=НАЙТИ(«.

2. Первый ввод в НАЙТИ: find_text . Это символ или разделитель, который вы ищете.

Поскольку мы ищем символ точки, введите:

=НАЙТИ(“.”,

3. Следующий ввод внутри_текста и это исходная строка.

В данном случае это ячейка A2. Таким образом, приведенная выше формула принимает вид:

=НАЙТИ(“., A2

Третий ввод — start_num . Это начальная позиция, в которой Excel будет искать исходную строку.

Этот ввод является необязательным и по умолчанию НАЙТИ сканирует строку с самого начала слева

Каспер Лангманн , специалист Microsoft Office

4. Нажмите Введите .

5. Теперь используйте приведенную выше функцию НАЙТИ в качестве второго входа в функции ВЛЕВО.

Ваша формула примет следующий вид:

=ЛЕВОЕ(A2,НАЙТИ(“.”, A2))

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

Результат:

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

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

Каспер Лангманн , специалист по Microsoft Office

Пример 3: Объединение функций НАЙТИ

Затем вы хотите извлечь Имя .

Это не так просто, как в приведенных выше примерах. Чтобы сделать это, вы должны сначала понять концепции ниже.

Приступим!

Вы знаете, что Имя находится между первым и вторым пробелами « », которые находятся на позициях 4 и 10 соответственно.

Чтобы получить Имя , вы можете использовать MID для извлечения символов, начиная с позиции 5 (символ сразу после первого пробела) до позиции 9 (символ, предшествующий второму пробелу).

Но как именно набрать формулу?

1. Получить начальную позицию для функции MID достаточно просто.

Вы используете НАЙТИ так же, как в предыдущем примере. Но вместо символа точки используйте символ пробела ” “.

Также добавьте 1 к результату. Формула начальной позиции принимает вид:

=НАЙТИ(» «,A2)+1

Попробуйте это в ячейке G2.

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

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

=НАЙТИ(» «,A2)+1

Затем вы можете использовать приведенную выше формулу в качестве начальной позиции второй функции НАЙТИ чтобы найти второе пространство, как показано ниже:

=НАЙТИ(» «,A2, НАЙТИ(» «,A2)+1)

Попробуйте в ячейке G2.

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

Ваша формула MID в основном выглядит следующим образом:

= MID(A2, позиция ПОСЛЕ 1-го пробела, позиция 2-го пробела – позиция 1-го пробела – 1 )

Таким образом, окончательная формула в ячейке G2 будет:

=СРЕДН(A2,НАЙТИ(» «,A2)+1,НАЙТИ(» «,A2,НАЙТИ(» «,A2)+1)-НАЙТИ(» «,A2)-1)

Вы вычитаете 1 в конце, чтобы удалить лишний пробел из результата.

4. Заполните строки ниже и наблюдайте, как происходит волшебство Excel!

Невероятно, не так ли? 🤩

Ниже еще один интересный пример!

Пример 3: Использование функции Excel LEN

Наконец, если вы хотите извлечь Фамилию , вы используете функцию ПРАВИЛЬНО.

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

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

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

Как же получить длину всей текстовой строки?

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

1. В ячейке h3 введите приведенную ниже формулу:

=ДЛСТР(A2)

2. Теперь вы можете объединить это с формулой для второго пробела, чтобы получить длину Фамилии :

=ДЛСТР(A2) – НАЙТИ(» «,A2, НАЙТИ(» «, A2)+1)

3. Наконец, завершите функцию ПРАВИЛЬНО, используя приведенную выше формулу в качестве количества символов, которые необходимо извлечь: НАЙТИ(» «,A2)+1))

4. Нажмите Введите и заполните строки ниже.

Результат:

Поздравляем! 👏

Вы заполнили рабочую тетрадь!

Вот и все – Что теперь?

Теперь вы хорошо подготовлены для работы с текстовыми строками с помощью Microsoft Excel!

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

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

На самом деле есть еще более сложные комбинации приведенных выше примеров. Вы можете комбинировать другие функции в Excel, такие как функции ПОДСТАВИТЬ и ПОВТОР.

Если вы хотите узнать больше, нажмите здесь, чтобы подписаться на мой бесплатный 30-минутный курс по электронной почте.

Другие ресурсы

На самом деле существует несколько способов работы с текстовыми строками в Microsoft Excel.

Вы можете использовать другие встроенные функции, такие как «Быстрая заливка» и «Текст в столбцы». Вы можете узнать больше, посмотрев наше видео на YouTube о том, как разделить ячейки в Excel — 4 метода.

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

Надеюсь, это руководство по Excel помогло вам!

Берегись! 👋

Kasper Langmann2023-02-23T15:19:28+00:00 Ссылка для загрузки страницы

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

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

Синтаксис

Синтаксис функции ПОИСК в Excel следующий:

  =ПОИСК(найти_текст,внутри_текста,[начальный_номер]) 
 

• Find_text — это подстрока или символ, который вы хотите найти.
• Within_text — это текстовая строка или ссылка на ячейку, в которой вы будете искать свои символы.
• Start_num — (необязательно) номер позиции символа, с которого вы хотите начать поиск. Если третий аргумент опущен, ПОИСК начинает поиск с первого символа строки.

Полезно знать

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

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

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

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

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

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

В этом примере нет необходимости указывать третий аргумент (start_num), так как мы ожидаем только один символ @ в каждом адресе электронной почты.

  =ПОИСК("@",A2)  

Эта функция может быть невероятно полезна несколькими способами.

Проверка наличия текстовой строки

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

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

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

Итак, мы можем просто сделать нашу формулу ПОИСК аргументом формулы IЧИСЛО.

  =ISNUMBER(SEARCH("xyz.net",A4))  

Приведенная выше формула определяет, содержит ли текстовая строка подстроку «xyz.net». Если эта подстрока не содержится, формула возвращает значение ЛОЖЬ.

В качестве альтернативы мы можем поставить два знака минус перед функцией ISNUMBER, что приведет к тому, что формула будет возвращать 1 или 0 для ИСТИНА или ЛОЖЬ соответственно. Затем становится просто добавить количество адресов с доменом «xyz.net» с помощью функции SUM.

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

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

С помощью этого принципа мы можем сказать Excel, что делать, если ISNUMBER возвращает ответ TRUE, и что делать, если нет.

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

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

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

  =ЕСЛИ(ЧИСЛО(ПОИСК(2,A2,5)),"Да","Нет")  

Обратите внимание, что в приведенном выше примере мы использовали все три аргумента функции ПОИСК. Это позволило нам искать значение «2», начиная с 5-го значения.

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

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

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

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

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

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

Значение этих аргументов следующее:

• Старый_текст — текстовая строка, содержащая заменяемую подстроку.
• Start_num — номер начальной позиции заменяемого текста.
• Num_chars — длина заменяемого текста.
• New_text — это текст, который заменит символы, которые вы хотите удалить.

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

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

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

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

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

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

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

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

Последний аргумент приведенной выше функции ЕСЛИОШИБКА возвращает исходный текст в столбце A, если нет совпадения находится по аргументу ПОИСК.

Извлечение подстроки из большей текстовой строки

Функция ПОИСК может быть объединена с функциями ВЛЕВО, ВПРАВО и СРЕДНЯЯ, чтобы извлекать символы из текстовой строки, находя символ или начало текстовой строки, а затем используя эту информацию для извлечь нужную подстроку.

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

Мы можем извлечь имена пользователей из адресов электронной почты, используя функцию ПОИСК в качестве аргумента функции ЛЕВЫЙ.

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

  =LEFT(текст, [число_символов])  

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

  =LEFT(A2,SEARCH("@",A2)-1)  

Найдя номер позиции символа @, мы можем определить, что имя пользователя заканчивается предыдущим символом, то есть нам нужно вычесть 1 из результат формулы ПОИСК. Это становится num_chars аргумент формулы LEFT.

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

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

Вариант 1

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

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

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

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

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

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

После того, как ПОИСК определит номер позиции символа @, и мы вычтем этот номер позиции из длины текстовой строки (любезно предоставлено LEN), у нас останется количество символов, составляющих доменное имя. Затем это становится num_chars аргумент функции ПРАВО.

Вариант 2

Наш второй вариант еще проще. Мы можем использовать функцию MID для захвата символов, которые начинаются в середине текстовой строки и доходят до конца. Синтаксис функции MID:

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

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

  =СРЕДН(A2,ПОИСК("@",A2)+1,100) 
 

Мы использовали ПОИСК, чтобы определить положение символа «@». Добавление 1 к этому номеру позиции сообщает нам начальную позицию (или start_num ) функции MID.

Извлечение символов из середины текстовой строки

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

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

Шаг 1

Первый шаг прост. Нам нужно найти номер позиции первого символа пробела. Мы можем использовать стандартную формулу ПОИСК без дополнительного аргумента.

  =ПОИСК(" ",A2) 
 

Результат равен 7. Это означает, что первый пробел находится на седьмой позиции текстовой строки. Таким образом, добавляя 1, мы знаем, что отчество начинается с символа номер 8. 

  =ПОИСК(" ",A2)+1 
 

Эта информация позже будет использоваться в качестве аргумента start_num функции MID.

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

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

Шаг 2

Мы уже знаем, что SEARCH(“ ”,A2)+1 сообщает Excel, где начинается отчество, поэтому давайте воспользуемся этим же местоположением, чтобы начать поиск следующего символа пробела.

Эта формула вернет номер позиции второго пробела в исходной текстовой строке.

Шаг 3

Осталось только использовать функцию MID для просмотра текста в ячейке A2 (аргумент text ), начиная с восьмого символа ( start_num аргумент), извлечь три символа ( num_chars аргумент).

  ПОИСК(" ",A2,ПОИСК(" ",A2)+1)-ПОИСК(" ",A2)) 
 

Последний элемент SEARCH(“ ”,A2) гарантирует, что число 11 будет вычтено из номера позиции первого символа пробела.

Теперь мы объединим все эти элементы в качестве аргументов функции MID.

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

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

Использование подстановочных знаков для поиска

Подстановочные знаки полезны, когда точная подстрока неизвестна или допускается частичное совпадение.

Функция ПОИСК поддерживает использование следующих подстановочных знаков: 

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

  =ПОИСК("цент*",A2) 
 

Функция ПОИСК возвращает номер позиции первого символа в подстроке. Как указывалось в самом начале, не имеет значения, является ли текст прописным или строчным.

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

  =ПОИСК("цент??",A2) 
 

При таком поиске совпадениями считаются только строки, содержащие буквы «цент», за которыми следуют ровно два символа и пробел. Текстовые строки, не соответствующие этому критерию, возвращают ошибку #ЗНАЧ! ошибка.

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

  =ПОИСК("~?",A2) 
 

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

SEARCH и SEARCHB 

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

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

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

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

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