30 функций Excel за 30 дней: ПОИСК (SEARCH)

Вчера в марафоне 30 функций Excel за 30 дней мы распознавали типы ошибок при помощи функции ERROR.TYPE (ТИП.ОШИБКИ) и удостоверились, что она быть может весьма полезна для исправления ошибок в Excel.

18-й денек марафона мы посвятим исследованию функции SEARCH (ПОИСК). Она отыскивает знак (либо знаки) снутри текстовой строчки и докладывает, где он был найден. Мы также разберем, как совладать с теми ситуациями, когда эта функция выдаёт ошибку.

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

Функция 18: SEARCH (ПОИСК)

Функция SEARCH (ПОИСК) отыскивает текстовую строчку снутри иной текстовой строчки, и, если находит, то докладывает её позицию.

Как можно применять функцию SEARCH (ПОИСК)?

Функция SEARCH (ПОИСК) отыскивает текстовую строчку снутри иной текстовой строчки. Она может:

Отыскать строчку текста снутри иной текстовой строчки (без учёта регистра).
Употреблять в поиске знаки подстановки.
Найти стартовую позицию в просматриваемом тексте.

Синтаксис SEARCH (ПОИСК)

Функция SEARCH (ПОИСК) имеет вот таковой синтаксис:

find_text (искомый_текст) – текст, который Вы ищете.
within_text (текст_для_поиска) – текстовая строчка, снутри которой происходит поиск.
start_num (нач_позиция) – если не указан, то поиск начнётся с первого знака.

Ловушки SEARCH (ПОИСК)

Функция SEARCH (ПОИСК) вернет позицию первой совпадающей строчки, не зависимо от регистра. Если Для вас нужен поиск с учётом регистра, Вы сможете применять функцию FIND (НАЙТИ), с которой мы познакомимся дальше в рамках марафона 30 функций Excel за 30 дней.

Пример 1: Находим текст в строке

Используйте функцию SEARCH (ПОИСК), чтоб отыскать какой-нибудь текст снутри текстовой строчки. В этом примере мы будем находить одиночный знак (введённый в ячейке B5) снутри текстовой строчки, находящейся в ячейке B2.

Если текст найден, функция SEARCH (ПОИСК) вернет номер позиции его первого знака в текстовой строке. Если не найден, результатом будет сообщение о ошибке #VALUE! (#ЗНАЧ).

В случае, когда результатом является ошибка, Вы сможете применять функцию IFERROR (ЕСЛИОШИБКА), чтоб заместо выполнения функции SEARCH (ПОИСК) вывести соответственное сообщение. Функция IFERROR (ЕСЛИОШИБКА) возникла в Excel, начиная с версии 2007. В наиболее ранешних версиях этот же итог можно было получить, используя IF (ЕСЛИ) вкупе с ISERROR (ЕОШИБКА).

=IFERROR(SEARCH(B5,B2),»Not Found»)
=ЕСЛИОШИБКА(ПОИСК(B5;B2);»Not Found»)

Пример 2: Используем знаки подстановки с SEARCH (ПОИСК)

Очередной метод проверить итог, возвращаемый SEARCH (ПОИСК), на наличие ошибки – пользоваться функцией ISNUMBER (ЕЧИСЛО). Если строчка найдена, результатом SEARCH (ПОИСК) будет число, а означает функция ISNUMBER (ЕЧИСЛО) возвратит TRUE (ИСТИНА). Если же текст не найден, то SEARCH (ПОИСК) скажет о ошибке, а ISNUMBER (ЕЧИСЛО) вернет FALSE (ЛОЖЬ).

Интересно почитать: Concatenate excel русский

В значении аргумента find_text (искомый_текст) можно применять знаки подстановки. Знак * (звёздочка) подменяет хоть какое количество знаков либо их отсутствие, а ? (вопросительный символ) подменяет хоть какой одиночный знак.

В нашем примере применен знак подстановки *, потому в заглавиях улиц будут найдены фразы CENTRAL, CENTER и CENTRE.

Пример 3: Определяем стартовую позицию для SEARCH (ПОИСК)

Если мы запишем два знака минус (двойное отрицание) перед функцией ISNUMBER (ЕЧИСЛО), то она вернет значения 1/0 заместо TRUE/FALSE (ИСТИНА/ЛОЖЬ). Дальше, функция SUM (СУММ) в ячейке E2 подсчитает суммарное количество записей, где разыскиваемый текст был найден.

В последующем примере в столбце B показаны:

Заглавие городка | Профессия

Наша задачка отыскать профессии, содержащие текстовую строчку, введённую в ячейке E1. Формула в ячейке C2 будет последующая:

Данная формула отыскала строчки, которые содержат слово “bank”, но в одной из их это слово встречается не в заглавии профессии, а в заглавии городка. Это нас не устраивает!

Опосля всякого наименования городка стоит знак | (вертикальная черта), потому мы, используя функцию SEARCH (ПОИСК), можем отыскать позицию этого знака. Его позиция быть может указана, как значение аргумента start_num (нач_позиция) в “главной” функции SEARCH (ПОИСК). В итоге наименования городов будут проигнорированы поиском.

Сейчас испытанная и исправленная формула будет считать лишь те строчки, которые содержат слово “bank” в заглавии профессии:

Функция ечисло в excel

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

Внедрение функции

В файле примера приведены несколько вариантов проверок:

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

2. Если в качестве значения на вход подается число и формат ячейки был установлен Текстовый (до ввода числа в ячейку), то функция возвратит логическое значение ЛОЖЬ. Если формат ячейки с числом поменять на Числовой уже опосля того, как функция возвратила значение ЛОЖЬ, то она не станет возвращать значение ИСТИНА.

Вывод: функция ЕЧИСЛО() не пробует преобразовать значения в числовой формат. Но, ее просто вынудить созодать это. Если в ячейке А1 число сохранено как текст, то формула = ЕЧИСЛО(–A1) возвратит ИСТИНА, а = ЕЧИСЛО(A1) – ЛОЖЬ.

3. Логические значения ЛОЖЬ и ИСТИНА формально в EXCEL числами не являются и это обосновывает тот факт, что формулы =ЕЧИСЛО(ЛОЖЬ) и =ЕЧИСЛО(ИСТИНА) возвратят ЛОЖЬ. Но, значениям ЛОЖЬ и ИСТИНА сопоставлены значения 0 и 1 соответственно, потому формулы =ЕЧИСЛО(–ЛОЖЬ) и =ЕЧИСЛО(–ИСТИНА) возвратят ИСТИНА.

4. Функция ЕЧИСЛО() обычно употребляется в паре с функцией ЕСЛИ() . К примеру, формула =ЕСЛИ(ЕЧИСЛО(B6);"Число";"Не число") возвратит слово Число, если в ячейке В6 находится число (в числовом формате).

Интересно почитать: Документ excel

Описание

Любая из функций Епроверяет обозначенное значение и возвращает зависимо от результата значение ИСТИНА либо ЛОЖЬ. К примеру, функция ЕПУСТО возвращает логическое значение ИСТИНА, если проверяемое значение является ссылкой на пустую ячейку; в неприятном случае ворачивается логическое значение ЛОЖЬ.

Функции Е употребляются для получения сведений о значении перед выполнением с ним вычисления либо другого деяния. К примеру, для выполнения другого деяния при появлении ошибки можно применять функцию ЕОШИБКА в сочетании с функцией ЕСЛИ:

= ЕСЛИ( ЕОШИБКА(A1); "Произошла ошибка."; A1 * 2)

Эта формула инспектирует наличие ошибки в ячейке A1. При появлении ошибки функция ЕСЛИ возвращает сообщение "Произошла ошибка." Если ошибки отсутствуют, функция ЕСЛИ вычисляет произведение A1*2.

Синтаксис

аргумент функции Е описаны ниже.

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

Возвращает значение ИСТИНА, если

Аргумент "значение" ссылается на пустую ячейку

Аргумент "значение" ссылается на хоть какое значение ошибки, не считая #Н/Д

Аргумент "значение" ссылается на хоть какое значение ошибки (#Н/Д, #ЗНАЧ!, #ССЫЛ!, #ДЕЛ/0!, #ЧИСЛО!, #ИМЯ? либо #ПУСТО!)

Аргумент "значение" ссылается на логическое значение

Аргумент "значение" ссылается на значение ошибки #Н/Д (значение труднодоступно)

Аргумент "значение" ссылается на хоть какой элемент, который не является текстом. (Направьте внимание, что функция возвращает значение ИСТИНА, если аргумент ссылается на пустую ячейку.)

Аргумент "значение" ссылается на число

Аргумент "значение" ссылается на ссылку

Аргумент "значение" ссылается на текст

Замечания

Аргументы в функциях Е не преобразуются. Любые числа, заключенные в кавычки, воспринимаются как текст. К примеру, в большинстве остальных функций, требующих числового аргумента, текстовое значение "19" преобразуется в число 19. Но в формуле ЕЧИСЛО( "19") это значение не преобразуется из текста в число, и функция ЕЧИСЛО возвращает значение ЛОЖЬ.

При помощи функций Е комфортно инспектировать результаты вычислений в формулах. Сочитая эти функции с функцией ЕСЛИ, можно отыскивать ошибки в формулах (см. приведенные ниже примеры).

Примеры

Пример 1

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

Инспектирует, является ли значение ИСТИНА логическим

Инспектирует, является ли значение "ИСТИНА" логическим

Инспектирует, является ли значение 4 числом

Инспектирует, является ли значение G8 допустимой ссылкой

Инспектирует, является ли значение XYZ1 допустимой ссылкой

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

Инспектирует, является ли ячейка C2 пустой

Интересно почитать: В excel пропали вкладки

Инспектирует, является ли значение в ячейке A4 (#ССЫЛ!) значением ошибки

Инспектирует, является ли значение в ячейке A4 (#ССЫЛ!) значением ошибки #Н/Д

Инспектирует, является ли значение в ячейке A6 (#Н/Д) значением ошибки #Н/Д

Инспектирует, является ли значение в ячейке A6 (#Н/Д) значением ошибки

Инспектирует, является ли значение в ячейке A5 (330,92) числом

Инспектирует, является ли значение в ячейке A3 ("Регион1") текстом

Get expert help now

Don’t have time to figure this out? Our expert partners at Excelchat can do it for you, 24/7.

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

Примеры использования функции ЕЧИСЛО в Excel

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

Вид таблицы данных:

Для расчетов используем последующую формулу:

Функция ЕСЛИ при помощи функции И делает проверку нескольких критерий (результатов выполнения функции ЕЧИСЛО, проверяющей каждую ячейку на наличие числового значения с оценкой по предмету). Если хотя бы одна из функций возвращает значение ЛОЖЬ, функция И возвратит ЛОЖЬ. В этом случае будет выведена текстовая строчка «Есть несданные», по другому – функция СРЗНАЧ произведет расчет.

Итог выполнения для первого ученика:

Таковым образом мы обусловили пустые ячейки в строчках и столбцах таблицы.

Суммирование ячеек с числами и текстом в Excel

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

Вид таблицы данных:

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

1-ая функция ЕСЛИ инспектирует условие, данное функцией И.
Если оба проверяемых выражения (результаты работы рассматриваемой функции) возвратят значение ИСТИНА (другими словами являются числами), будет выполнена функция СУММ.
Если одна из функций ЕЧИСЛО возвратит значение ЛОЖЬ, будет выполнена проверка доборной функцией ЕСЛИ (проверка первой ячейки на содержание числовых данных).
Если 1-ая ячейка хранит число, будет возвращено его значение, по другому – ворачивается значение, хранящееся в иной ячейке.

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

Описание аргументов функции ЕЧИСЛО в Excel

Функция имеет последующую синтаксическую запись:

Единственным аргументом, неотклонимым для наполнения, является значение – воспринимает данные хоть какого типа (логические, числовые, текстовые, имена, ссылочные) для проверки на соответствие числовым данным.