Ечисло поиск excel

Е (функции Е)

Описание

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

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

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

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

Синтаксис

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

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

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

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

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

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

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

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

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

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

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

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

Замечания

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

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

Примеры

Пример 1

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

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

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

Проверяет, является ли значение 4 числом

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

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

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

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

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

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

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

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

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

Проверяет, является ли значение в ячейке A3 (“Регион1”) текстом

Источник: support.office.com

Ечисло поиск excel

Спасибо, формула работает, но не понял про “формат ячейки” – куда нужно вставить “Найдено”;;Не найдено” ? Сейчас формула выдаёт 0 или 1 в зависимости от “нашла-не нашла”. Если вставлять нужно в условное форматирование, то какое правило там нужно выбрать?

Спасибо, формула работает, но не понял про “формат ячейки” – куда нужно вставить “Найдено”;;Не найдено” ? Сейчас формула выдаёт 0 или 1 в зависимости от “нашла-не нашла”. Если вставлять нужно в условное форматирование, то какое правило там нужно выбрать? Гордей

Спасибо, формула работает, но не понял про “формат ячейки” – куда нужно вставить “Найдено”;;Не найдено” ? Сейчас формула выдаёт 0 или 1 в зависимости от “нашла-не нашла”. Если вставлять нужно в условное форматирование, то какое правило там нужно выбрать? Автор – Гордей
Дата добавления – 29.03.2018 в 13:52

Che79 Дата: Четверг, 29.03.2018, 13:57 | Сообщение № 5

Делай нормально и будет нормально!

Ответить

and_evg Дата: Четверг, 29.03.2018, 14:00 | Сообщение № 6
Гордей Дата: Четверг, 29.03.2018, 14:13 | Сообщение № 7

Спасибо, но нужно именно в одной ячейке, а в Вашей версии надо протягивать на каждую искомую.

Спасибо, но нужно именно в одной ячейке, а в Вашей версии надо протягивать на каждую искомую. Гордей

Спасибо, но нужно именно в одной ячейке, а в Вашей версии надо протягивать на каждую искомую. Автор – Гордей
Дата добавления – 29.03.2018 в 14:13

and_evg Дата: Четверг, 29.03.2018, 14:17 | Сообщение № 8
Гордей Дата: Четверг, 29.03.2018, 14:23 | Сообщение № 9

Да, это работает, спасибо! А нахождение совпадений не менее чем в пяти ячейках из просматриваемого диапазона? Чуть выше сформулировал точнее.

Да, это работает, спасибо! А нахождение совпадений не менее чем в пяти ячейках из просматриваемого диапазона? Чуть выше сформулировал точнее. Гордей

Да, это работает, спасибо! А нахождение совпадений не менее чем в пяти ячейках из просматриваемого диапазона? Чуть выше сформулировал точнее. Автор – Гордей
Дата добавления – 29.03.2018 в 14:23

Che79 Дата: Четверг, 29.03.2018, 14:25 | Сообщение № 10

формат ячеек такой же, как и был.

[p.s.] ФЯ в сообщении #2 поправил, изначально, второпях забыл одни кавычки

формат ячеек такой же, как и был.

[p.s.] ФЯ в сообщении #2 поправил, изначально, второпях забыл одни кавычки Che79

Делай нормально и будет нормально!

формат ячеек такой же, как и был.

[p.s.] ФЯ в сообщении #2 поправил, изначально, второпях забыл одни кавычки Автор – Che79
Дата добавления – 29.03.2018 в 14:25

Гордей Дата: Четверг, 29.03.2018, 14:33 | Сообщение № 11

Да, работает. Ещё одно спасибо! Единственный минус такого решения в том, что ячейка выводит “Не найдено” так, что это не поддаётся условному форматированию (хочу окрасить в красный, если вылезает “Не найдено”, но условное форматирование “не видит” слово “Не найдено” в такой ячейке)

Да, работает. Ещё одно спасибо! Единственный минус такого решения в том, что ячейка выводит “Не найдено” так, что это не поддаётся условному форматированию (хочу окрасить в красный, если вылезает “Не найдено”, но условное форматирование “не видит” слово “Не найдено” в такой ячейке) Гордей

Да, работает. Ещё одно спасибо! Единственный минус такого решения в том, что ячейка выводит “Не найдено” так, что это не поддаётся условному форматированию (хочу окрасить в красный, если вылезает “Не найдено”, но условное форматирование “не видит” слово “Не найдено” в такой ячейке) Автор – Гордей
Дата добавления – 29.03.2018 в 14:33

Источник: www.excelworld.ru

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  1. В некоторых случаях функция ЕЧИСЛО возвращает значение ИСТИНА для чисел, хранящихся в ячейках, отформатированных как текст. Обычно, прямое преобразование не выполняется (например: =ЕЧИСЛО(“2”) вернет значение ЛОЖЬ). Следует быть осторожным с выполнением вычислений над такими данными, всегда устанавливать соответствующий формат данных для ячеек.
  2. Функция не выполняет промежуточных преобразований логических данных. Например, результатом выполнения =ЕЧИСЛО(ИСТИНА) будет логическое ЛОЖЬ. В Excel предусмотрена возможность прямого преобразования данных с использованием знака двойного отрицания «–». Например, результат выполнения функции =ЕЧИСЛО(–ИСТИНА) – логическое ИСТИНА.
  3. Рассматриваемую функцию обычно используют в качестве аргумента с проверкой условия для функции ЕСЛИ.
  4. Если в качестве аргумента функции передана ссылка на ячейку, содержащую число, возможны два варианта возвращаемых значений:
  • ИСТИНА, если формат ячейки установлен по умолчанию или является числовым;
  • ЛОЖЬ, если ячейка имеет текстовый формат.

Источник: exceltable.com

Функция 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 по умолчанию начнет поиск с самого начала текстовой строки.

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

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

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

Источник: excelhack.ru

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

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

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

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

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

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

Как можно использовать функцию SEARCH (ПОИСК)?

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

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

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

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

SEARCH(find_text,within_text,[start_num])
ПОИСК(искомый_текст;текст_для_поиска;[нач_позиция])

  • 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 (ЛОЖЬ).

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

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

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

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

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

Название города | Профессия

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

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

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

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

Источник: office-guru.ru

Функции Excel ЕСЛИ (IF) и ЕСЛИМН (IFS) для нескольких условий

Логическая функция ЕСЛИ в Экселе – одна из самых востребованных. Она возвращает результат (значение или другую формулу) в зависимости от условия.

Функция ЕСЛИ в Excel

Функция имеет следующий синтаксис.

ЕСЛИ(лог_выражение; значение_если_истина; [значение_если_ложь])

лог_выражение – это проверяемое условие. Например, A2 30) не выполняется и возвращается альтернативное значение, указанное в третьем поле. В этом вся суть функции ЕСЛИ. Протягивая расчет вниз, получаем результат по каждому товару.

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

Пока все логично, но смущают минусы. Разве бывают отрицательные остатки? Нет, конечно. Запасы не могут быть ниже нуля. Чтобы прогноз был корректным, нужно отрицательные значения заменить нулями. Здесь отлично поможет формула ЕСЛИ. Она будет проверять полученное по прогнозу значение и если оно окажется меньше нуля, то принудительно выдаст ответ 0, в противном случае — результат расчета, т.е. некоторое положительное число. В общем, та же логика, только вместо значений используем формулу в качестве условия.

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

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

Формула ЕСЛИ в Excel – примеры нескольких условий

Довольно часто количество возможных условий не 2 (проверяемое и альтернативное), а 3, 4 и более. В этом случае также можно использовать функцию ЕСЛИ, но теперь ее придется вкладывать друг в друга, указывая все условия по очереди. Рассмотрим следующий пример.

Нескольким менеджерам по продажам нужно начислить премию в зависимости от выполнения плана продаж. Система мотивации следующая. Если план выполнен менее, чем на 90%, то премия не полагается, если от 90% до 95% — премия 10%, от 95% до 100% — премия 20% и если план перевыполнен, то 30%. Как видно здесь 4 варианта. Чтобы их указать в одной формуле потребуется следующая логическая структура. Если выполняется первое условие, то наступает первый вариант, в противном случае, если выполняется второе условие, то наступает второй вариант, в противном случае если… и т.д. Количество условий может быть довольно большим. В конце формулы указывается последний альтернативный вариант, для которого не выполняется ни одно из перечисленных ранее условий (как третье поле в обычной формуле ЕСЛИ). В итоге формула имеет следующий вид.

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

Теперь вы знаете, как пользоваться функцией ЕСЛИ в Excel, а также ее более современным вариантом для множества условий ЕСЛИМН.


Источник: statanaliz.info

Добавить комментарий

Adblock
detector