Примеры
Пример 1
Скопируйте эталон данных из последующей таблицы и вставьте их в ячейку A1 новейшего листа Excel. Чтоб показать результаты формул, выделите их и нажмите кнопку F2, а потом — кнопку ВВОД. По мере необходимости измените ширину столбцов, чтоб созидать все данные.
Инспектирует, является ли значение ИСТИНА логическим
Инспектирует, является ли значение «ИСТИНА» логическим
Инспектирует, является ли значение 4 числом
Инспектирует, является ли значение G8 допустимой ссылкой
Инспектирует, является ли значение XYZ1 допустимой ссылкой
Скопируйте эталон данных из приведенной ниже таблицы и вставьте его в ячейку A1 новейшего листа Excel. Чтоб показать результаты формул, выделите их и нажмите кнопку F2, а потом — кнопку ВВОД. По мере необходимости измените ширину столбцов, чтоб созидать все данные.
Инспектирует, является ли ячейка C2 пустой
Инспектирует, является ли значение в ячейке A4 (#ССЫЛ!) значением ошибки
Инспектирует, является ли значение в ячейке A4 (#ССЫЛ!) значением ошибки #Н/Д
Инспектирует, является ли значение в ячейке A6 (#Н/Д) значением ошибки #Н/Д
Инспектирует, является ли значение в ячейке A6 (#Н/Д) значением ошибки
Инспектирует, является ли значение в ячейке A5 (330,92) числом
Инспектирует, является ли значение в ячейке A3 («Регион1») текстом
Пример 2: Используем символы подстановки с SEARCH (ПОИСК)
Еще один способ проверить результат, возвращаемый SEARCH (ПОИСК), на наличие ошибки – воспользоваться функцией ISNUMBER (ЕЧИСЛО). Если строка найдена, результатом SEARCH (ПОИСК) будет число, а значит функция ISNUMBER (ЕЧИСЛО) вернет TRUE (ИСТИНА). Если же текст не найден, то SEARCH (ПОИСК) сообщит об ошибке, а ISNUMBER (ЕЧИСЛО) возвратит FALSE (ЛОЖЬ).
В значении аргумента find_text (искомый_текст) можно использовать символы подстановки. Символ * (звёздочка) заменяет любое количество символов или их отсутствие, а ? (вопросительный знак) заменяет любой одиночный символ.
В нашем примере использован символ подстановки *, поэтому в названиях улиц будут найдены фразы CENTRAL, CENTER и CENTRE.
Пример 1: Находим текст в строке
Используйте функцию SEARCH (ПОИСК), чтобы найти какой-либо текст внутри текстовой строки. В этом примере мы будем искать одиночный символ (введённый в ячейке B5) внутри текстовой строки, находящейся в ячейке B2.
Если текст найден, функция SEARCH (ПОИСК) возвратит номер позиции его первого символа в текстовой строке. Если не найден, результатом будет сообщение об ошибке #VALUE! (#ЗНАЧ).
В случае, когда результатом является ошибка, Вы можете использовать функцию IFERROR (ЕСЛИОШИБКА), чтобы вместо выполнения функции SEARCH (ПОИСК) вывести соответствующее сообщение. Функция IFERROR (ЕСЛИОШИБКА) появилась в Excel, начиная с версии 2007. В более ранних версиях тот же результат можно было получить, используя IF (ЕСЛИ) вместе с ISERROR (ЕОШИБКА).
Примеры
Пример 1
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
Проверяет, является ли значение ИСТИНА логическим
Проверяет, является ли значение «ИСТИНА» логическим
Проверяет, является ли значение 4 числом
Проверяет, является ли значение G8 допустимой ссылкой
Проверяет, является ли значение XYZ1 допустимой ссылкой
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
Проверяет, является ли ячейка C2 пустой
Проверяет, является ли значение в ячейке A4 (#ССЫЛ!) значением ошибки
Проверяет, является ли значение в ячейке A4 (#ССЫЛ!) значением ошибки #Н/Д
Проверяет, является ли значение в ячейке A6 (#Н/Д) значением ошибки #Н/Д
Проверяет, является ли значение в ячейке A6 (#Н/Д) значением ошибки
Проверяет, является ли значение в ячейке A5 (330,92) числом
Проверяет, является ли значение в ячейке A3 («Регион1») текстом
Проверка в Excel, если содержит текст
Что делать, если нужно проверить, содержит ли ячейка строку текста. Используем функцию: =ЕТЕКСТ(ссылка) . Функция вернет ИСТИНА, когда ссылка указывает на ячейку с текстом, ЛОЖЬ – в противном случае. Вот несколько примеров работы:
Получили такие результаты:
| Тип проверяемого значения | Результат |
|---|---|
| Число | ЛОЖЬ |
| Дата | ЛОЖЬ |
| Текст | ИСТИНА |
| Число в текстовом формате | ИСТИНА |
| Пустая ячейка | ЛОЖЬ |
ИСТИНА
Формула
Зависит от типа вычисленного значения
Ссылка
Зависит от типа значения в ячейке, на которую ссылаемся
То есть, функция считает текстом не только строку, а и число в текстовом формате ячейки, непечатаемый символ.
Обратный результат даст функция =ЕНЕТЕКСТ(ссылка) , которая проверяет, чтобы в ячейке было нетекстовое значение:
Таблица с итогами:
| Тип проверяемого значения | Результат |
|---|---|
| Число | ИСТИНА |
| Дата | ИСТИНА |
| Текст | ЛОЖЬ |
| Число в текстовом формате | ЛОЖЬ |
| Пустая ячейка | ИСТИНА |
| Непечатаемый символ | ЛОЖЬ |
| Формула | Зависит от типа вычисленного значения |
| Ссылка | Зависит от типа значения в ячейке, на которую ссылаемся |
Как будет выглядеть формула в Excel: если ячейка содержит текст, то значение, в противном случае – пустая строка.
Вот так: =ЕСЛИ(ЕТЕКСТ(A1);»Это текст»;»») . Т.е. аргументы:
- Условие: ЕТЕКСТ(А1)
- Значение, если это текст: строка «Это текст»
- Значение, если это не текст: пустая строка (обозначается «»)
Как Excel воспринимает данные?
Ячейка в Excel может содержать следующие типы данных: дата, время, число, текст, формула
ТЕКСТ И ФОРМУЛЫ Любой произвольный набор символов, если он не представляет собой число или дату, будет воспринят как текст: « привет », « 12 штук ». Если данных в ячейке начинаются со знака =, —, + — Excel попытается вычислить выражение, следующее за знаком, т.к. посчитает это формулой.
ЧИСЛА, ДАТА, ВРЕМЯ
- ЧИСЛА Для представления чисел в Excel используется 15 цифр. Если введено число длиной более 15 цифр (как до, так и после десятичной запятой), Excel сохранит его с точностью до 15 цифр, заменив оставшиеся разряды нулями. Очень большие числа автоматически представляются в экспоненциальном формате: 1,2Е+19 = 1,2*1019 или 1,2Е-19 = 1,2*10-19 . Наибольшее положительное число 9,9Е+307 ; наименьшее положительное число 1 Е-307 (числа, большие 9,9Е+307 становятся текстом, меньшие 1Е-307 – нулем) Для определения целой и дробной частей чисел используется разделитель: в российском языковом стандарте это запятая; в английском – точка
- Дата Дата для Excel это тоже число, только представленное иным форматом. В Excel отсчет даты и времени начинается с 01.01.1900 . Для компьютеров под управлением Macintosh в Excel отсчет начинается с 02.01.1904 . Для корректной совместимости с такими компьютерами в Excel предусмотрена возможность включения исчисления дат 1904: Файл→ Параметры→ Дополнительно→ Использовать систему дат 1904 (File→ Options→ Advanced→ Use 1904 date system) . Если в ячейку ввести число 43587 и присвоить этой ячейке формат Даты — «ДД.ММ.ГГГГ» (правая кнопка мыши по ячейке→ Формат ячеек→ вкладка Число→ Дата (Format Cells→ вкладка Number→ Date) ), то в ячейке будет отображено 02.05.2019 . Т.е. 43587 в переводе на дату равно 02.05.2019 . 43587 — это количество дней. 1 — это одни целые сутки.
- Время Т.к. целые числа для Excel это даты, то они уже не могут быть временем. Временем является дробная часть чисел. 0,5 = 12:00 , а 0,124 — 2:58:34 . Следовательно, если записать в ячейку 43587,124 , то в перевод на дату/время это будет — 02.05.2019 2:58:34 .
Если количество часов больше 23, введенное время преобразуется в формат «дата время». Например, 24:12:15 = 01.01.1900 0:12:15
Хранение данных подобным образом позволяет производить различные математические операции с датой и временем (складывать, вычитать, умножать и т.п.)
Важно знать, что при изменении данных Excel автоматически пытается определить тип вводимых данных и преобразовать их в максимально приближенный формат (на свое усмотрение). Порой это может сыграть очень плохую шутку
Например, если попытаться в русской локализации записать в ячейку текст вида 2.1 , то Excel непременно преобразует его в дату — 02.янв . Причина тому тот факт, что разделителем целой и дробной части является запятая, а разделителем дней, месяцев и лет в дате – точка. Чтобы Excel этого не делал необходимо перед внесением данных установить формат ячейки — Текстовый (Text) . Или ставить перед такими числами апостроф — ‘ 2.1
Есть и другой вариант: когда вы открываете файл, в котором уже есть подобные «числа»( 2.1 , 5.10 и т.п.). Они уже в момент открытия в большинстве случаев будут преобразованы в даты и менять формат ячеек уже поздно. Но можно попробовать схитрить и установить на время открытия таких файлов разделитель целой и дробной части — точка. Сделать это можно через Параметры:
- Excel 2003: меню Сервис —Параметры. -вкладка Международные
- Excel 2007: Кнопка Офис —Параметры (Excel Options) —Дополнительно (Advanced)
- Excel 2010 и выше: Файл (File) —Параметры (Options) —Дополнительно (Advanced)
Снять галку с «Использовать системные разделители (Use system separators) » и установить в полях ниже свои. Как правило помогает установка в качестве разделителя целой и дробной части (decimal separator) точки вместо запятой.
Статья помогла? Поделись ссылкой с друзьями!
Описание аргументов функции ЕЧИСЛО в Excel
Функция имеет следующую синтаксическую запись:
=ЕЧИСЛО(значение)
Единственным аргументом, обязательным для заполнения, является значение – принимает данные любого типа (логические, числовые, текстовые, имена, ссылочные) для проверки на соответствие числовым данным.
Примечания:
- В некоторых случаях функция ЕЧИСЛО возвращает значение ИСТИНА для чисел, хранящихся в ячейках, отформатированных как текст. Обычно, прямое преобразование не выполняется (например: =ЕЧИСЛО(“2”) вернет значение ЛОЖЬ). Следует быть осторожным с выполнением вычислений над такими данными, всегда устанавливать соответствующий формат данных для ячеек.
- Функция не выполняет промежуточных преобразований логических данных. Например, результатом выполнения =ЕЧИСЛО(ИСТИНА) будет логическое ЛОЖЬ. В Excel предусмотрена возможность прямого преобразования данных с использованием знака двойного отрицания «—». Например, результат выполнения функции =ЕЧИСЛО(—ИСТИНА) – логическое ИСТИНА.
- Рассматриваемую функцию обычно используют в качестве аргумента с проверкой условия для функции ЕСЛИ.
- Если в качестве аргумента функции передана ссылка на ячейку, содержащую число, возможны два варианта возвращаемых значений:
Поиск значения в строке Excel
Макрос поиска ячейки с выпадающим списком
Отсутствие возвращаемого значения
Функции не обязаны возвращать значение. Чтобы сообщить компилятору, что функция не возвращает значение, используется тип возвращаемого значения . Давайте посмотрим на функцию из предыдущего урока:
Эта функция имеет тип возвращаемого значения , что указывает на то, что она не возвращает значение вызывающей стороне. Поскольку она не возвращает значение, инструкция не требуется (попытка вернуть конкретное значение из функции с типом возврата приведет к ошибке компиляции).
Вот еще один пример функции, ничего не возвращающей, и пример программы, которая ее вызывает:
При первом вызове функции функция печатает «Hi», а затем ничего не возвращает вызывающей функции. Управление возвращается в , и программа продолжает выполнение.
Второй вызов функции даже не будет компилироваться. Функция имеет возвращаемый тип , то есть не возвращает значения. Однако эта инструкция пытается отправить возвращаемое из значение в для печати. не знает, что с этим делать (какое значение он будет выводить?). Следовательно, компилятор пометит это как ошибку. Вам нужно закомментировать эту строку кода, чтобы код компилировался.
Тип возврата (что означает, что ничего не возвращается) используется, когда нам нужна функция, которая ничего не возвращает вызывающей стороне (потому что в этом нет необходимости). В приведенном выше примере у функции есть полезное поведение (она печатает «Hi»), но ей не нужно ничего возвращать вызывающей стороне (в данном случае ). Следовательно, функции присваивается возвращаемый тип .
Описание аргументов функции ЕЧИСЛО в Excel
Функция имеет следующую синтаксическую запись:
Единственным аргументом, обязательным для заполнения, является значение – принимает данные любого типа (логические, числовые, текстовые, имена, ссылочные) для проверки на соответствие числовым данным.
- В некоторых случаях функция ЕЧИСЛО возвращает значение ИСТИНА для чисел, хранящихся в ячейках, отформатированных как текст. Обычно, прямое преобразование не выполняется (например: =ЕЧИСЛО(“2”) вернет значение ЛОЖЬ). Следует быть осторожным с выполнением вычислений над такими данными, всегда устанавливать соответствующий формат данных для ячеек.
- Функция не выполняет промежуточных преобразований логических данных. Например, результатом выполнения =ЕЧИСЛО(ИСТИНА) будет логическое ЛОЖЬ. В Excel предусмотрена возможность прямого преобразования данных с использованием знака двойного отрицания «—». Например, результат выполнения функции =ЕЧИСЛО(—ИСТИНА) – логическое ИСТИНА.
- Рассматриваемую функцию обычно используют в качестве аргумента с проверкой условия для функции ЕСЛИ.
- Если в качестве аргумента функции передана ссылка на ячейку, содержащую число, возможны два варианта возвращаемых значений:
- ИСТИНА, если формат ячейки установлен по умолчанию или является числовым;
- ЛОЖЬ, если ячейка имеет текстовый формат.
Подсчет значений не равно и СЧЁТЕСЛИ
СчЁтесли не равно Excel
=СЧЕТЕСЛИ («диапазон»,»критерий х»)
Если вам нужно подсчитать Количество ячеек, которые содержат значения, не равные определенному значению, можно использовать функцию СЧЕТЕСЛИ. В общем виде формула диапазон ячеек, и X представляет значение (критерий). Суммируется все значения которые не попали под критерий.
В приведенном примере в активной ячейке содержится такая формула:
Как работает формула:
СЧЕТЕСЛИ подсчитывает количество ячеек в диапазоне, которые удовлетворяют критериям.
В этом примере мы используем «» (логический оператор «не равно») и количество ячеек в диапазоне C5:C10, что не равно «100». СЧЕТЕСЛИ возвращает в результате суму значений ячеек не соответствующие критерию.
СЧЕТЕСЛИ является не чувствительным к регистру.
Если вы хотите использовать значение из другой ячейки как часть критерия, использование амперсанда ( & ), чтобы объединить так:
Если значение в ячейке А1 является «100», то критерии будут «100» после объединения, и СЧЕТЕСЛИ подсчет ячеек не будет равна 100.
Пример 3: Определяем стартовую позицию для SEARCH (ПОИСК)
Если мы запишем два знака минус (двойное отрицание) перед функцией ISNUMBER (ЕЧИСЛО), то она вернет значения 1/ заместо TRUE/FALSE (ИСТИНА/ЛОЖЬ). Дальше, функция SUM (СУММ) в ячейке E2 подсчитает суммарное количество записей, где разыскиваемый текст был найден.
В последующем примере в столбце B показаны:
Заглавие городка | Профессия
Наша задачка отыскать профессии, содержащие текстовую строчку, введённую в ячейке E1. Формула в ячейке C2 будет последующая:
Данная формула отыскала строчки, которые содержат слово “bank”, но в одной из их это слово встречается не в заглавии профессии, а в заглавии городка. Это нас не устраивает!
Опосля всякого наименования городка стоит знак | (вертикальная черта), потому мы, используя функцию SEARCH (ПОИСК), можем отыскать позицию этого знака. Его позиция быть может указана, как значение аргумента start_num (нач_позиция) в “главной” функции SEARCH (ПОИСК). В итоге наименования городов будут проигнорированы поиском.
Сейчас испытанная и исправленная формула будет считать лишь те строчки, которые содержат слово “bank” в заглавии профессии:
Выбор уникальных и повторяющихся значений в Excel
Как в Excel определить содержит ли ячейка число
Возможность проверить, содержит ли ячейка или все ячейки рабочего листа число, может быть полезна при выполнении вычислений. С этим так легко ошибиться, и даже небольшие ошибки могут привести к огромным результатам. Однако решение есть всегда. В этом учебном материале по Excel мы рассмотрим примеры того как узнать содержит ли ячейка число.
Описание
Чтобы проверить, содержит ли ячейка (или любая текстовая строка) число, вы можете использовать функцию НАЙТИ вместе с функцией СЧЁТ. В основной форме формулы (см. выше) A1 представляет ячейку, которую вы тестируете. Проверяемые числа (числа от 0 до 9) предоставляются в виде массива. В этом примере формула в C5 имеет вид:
Пояснение
Функция НАЙТИ возвращает положение текста в ячейке и ошибку #ЗНАЧЕН!, если нет. В этом примере ячейка B5 содержит число «4» в 5 позиции. Итак, если бы мы просто использовали эту формулу:
В результате она вернет число 5. Однако, поскольку мы даем функции НАЙТИ массив элементов для проверки, она вернет массив результатов, который выглядит следующим образом: Другими словами, НАЙТИ проверяет содержимое B5 для каждого числа и возвращает результат каждой проверки как элемент массива. После того, как НАЙТИ вернет массив, СЧЁТ считает элементы в массиве. СЧЁТ считает только числовые значения, поэтому любой элемент #ЗНАЧЕН! в массиве обрабатывается как ноль. Если в массиве есть числа СЧЁТ вернет число больше нуля, если нет то вернет ноль. Последний шаг в формуле — сравнение результата функций НАЙТИ и СЧЁТ с нулем. Если были найдены какие-либо числа, формула вернет ИСТИНА. В противном случае формула вернет ЛОЖЬ. Если вы хотите сделать что-то большее, чем просто проверить, содержит ли ячейка текст, вы можете заключить формулу в оператор ЕСЛИ следующим образом:
Вместо того, чтобы возвращать ИСТИНА или ЛОЖЬ, приведенная выше формула возвращает «Да», если B5 содержит какие-либо числа, и «Нет», если не содержит.
Содержит ли ячейка числовое значение?
Если вам нужно только проверить, содержит ли ячейка числовое значение, вы можете использовать функцию ЕЧИСЛО следующим образом:
Дополнительные параметры поиска слов и фраз
Небольшой тест
Вопрос 1
Проверьте следующие программы и укажите, что они выводят, или будут ли они не компилироваться.
1а)
Ответ
Эта программа печатает число 16.
1b)
Ответ
Эта программа не компилируется. Вложенные функции не допускаются.
1c)
Ответ
Эта программа компилируется, но ничего не выводит. Значения, возвращаемые функциями, ни для чего не используются (и, таким образом, отбрасываются).
1d)
Ответ
Эта программа печатает буквы A и B в отдельных строках.
1e)
Ответ
Эта программа не компилируется. Функция возвращает , которое пытается отправить в . Это приведет к ошибке компиляции.
1f)
Ответ
Эта программа напечатает 5 дважды (в отдельных строках). Оба раза, когда вызывается функция , возвращается значение 5. Когда выполняется инструкция , функция немедленно завершается, поэтому инструкция никогда не выполняется.
1g)
Ответ
Эта программа не будет компилироваться, потому что функция имеет недопустимое имя. Мы говорили о правилах именования в уроке «1.7 – Ключевые слова и именование идентификаторов».
1h) Чуть сложнее.
Ответ
Эта программа будет скомпилирована, но функция не будет вызвана, потому что при вызове функции отсутствуют круглые скобки. То, что на самом деле будет выведено, зависит от компилятора.
Вопрос 2
Что означает «DRY» и почему это полезно?
Ответ
DRY означает «Don’t Repeat Yourself» (не повторяйся). Это практика, которая включает в себя написание кода таким образом, чтобы минимизировать избыточность. Это делает ваши программы более краткими, менее подверженными ошибкам и более удобными для поддержки.
Использование функции
В файле примера приведены несколько вариантов проверок:
1. Если в качестве значения на вход подается число и формат ячейки НЕ был установлен Текстовый, то функция вернет логическое значение ИСТИНА. Если формат ячейки с числом изменить на Текстовый уже после того, как функция вернула значение ИСТИНА, то она не станет возвращать значение ЛОЖЬ.
2. Если в качестве значения на вход подается число и формат ячейки был установлен Текстовый (до ввода числа в ячейку), то функция вернет логическое значение ЛОЖЬ. Если формат ячейки с числом изменить на Числовой уже после того, как функция вернула значение ЛОЖЬ, то она не станет возвращать значение ИСТИНА.
Вывод: функция ЕЧИСЛО() не пытается конвертировать значения в числовой формат. Однако, ее легко заставить делать это. Если в ячейке А1 число сохранено как текст, то формула = ЕЧИСЛО(—A1) вернет ИСТИНА, а = ЕЧИСЛО(A1) — ЛОЖЬ.
3. Логические значения ЛОЖЬ и ИСТИНА формально в EXCEL числами не являются и это доказывает тот факт, что формулы =ЕЧИСЛО(ЛОЖЬ) и =ЕЧИСЛО(ИСТИНА) вернут ЛОЖЬ. Однако, значениям ЛОЖЬ и ИСТИНА сопоставлены значения 0 и 1 соответственно, поэтому формулы =ЕЧИСЛО(—ЛОЖЬ) и =ЕЧИСЛО(—ИСТИНА) вернут ИСТИНА.
4. Функция ЕЧИСЛО() обычно используется в паре с функцией ЕСЛИ() . Например, формула =ЕСЛИ(ЕЧИСЛО(B6);»Число»;»Не число») вернет слово Число, если в ячейке В6 находится число (в числовом формате).
Как извлечь число из конца текстовой строки.
Если у вас есть столбец буквенно-цифровых значений, в котором число всегда идет после текста, вы можете использовать одну из следующих формул для извлечения из них чисел.
Важная заметка! В следующих формулах извлечение выполняется с помощью функций ВПРАВО и ВЛЕВО, которые относятся к категории текстовых функций. Эти функции всегда возвращают текст. В нашем случае результатом будет числовая подстрока, которая, с точки зрения Excel, также является текстом, а не числом. Если результатом должно быть число (которое можно использовать в дальнейших вычислениях), оберните соответствующую формулу в функцию ЗНАЧЕНИЕ или выполните с ней простейшую математическую операцию (например, двойное отрицание).
Чтобы извлечь число из строки «текстовое число», первое, что вам нужно знать, — это с чего начать операцию. Затем мы определяем положение первой цифры, используя это общее выражение:
О логике расчетов поговорим чуть позже. А пока просто замените слово «ячейка» ссылкой на позицию, содержащую исходный текст (в нашем случае, A2), и запишите полученное выражение в любой пустой ячейке той же строки, например, в B2:
Хотя формула содержит константу массива, это обычное выражение, которое вводится обычным способом: нажатием клавиши Enter.
После того, как вы определили положение первой цифры, вы можете использовать функцию ВПРАВО, чтобы получить номер. Чтобы узнать, сколько символов нужно извлечь, вычтите позицию первой цифры из общей длины строки и добавьте единицу к результату, потому что первая цифра также должна быть включена:
Где A2 — исходная ячейка, а B2 — позиция первой цифры.
На следующем снимке экрана показаны результаты:
Чтобы исключить вспомогательный столбец, содержащий позицию первой цифры, вы можете встроить формулу MIN непосредственно в функцию RIGHT следующим образом:
Чтобы формула возвращала число вместо числовой строки, заключите его в функцию VALUE:
Или просто двойное отрицание с двумя знаками минус»:
Другой способ извлечь число из конца строки — использовать такое выражение:
Используя исходный текст в ячейке A2, напишите следующую формулу в ячейке B2 или любой другой пустой ячейке в той же строке, а затем скопируйте ее в столбец:
Примечание. Эти формулы предназначены для случая, когда числа находятся только в конце текстовой строки. Если какие-то числа стоят даже в середине или в начале, ничего не получится.
Этих недостатков лишена третья формула, которая извлекает только последнее число в тексте, игнорируя все предыдущие:
На скриншоте ниже вы можете увидеть результат его работы.
Как видите, числа в начале или в середине текста игнорируются
Также обратите внимание, что результат, как и в предыдущих формулах, представляет собой число, записанное в виде текста. О том, как превратить его в обычный номер, мы уже рассказывали выше в этой статье
Примечание. Если вы используете Excel 2019 или более раннюю версию, вам нужно использовать формулу массива, нажимая Ctrl + Shift + Enter при вводе. Если у вас Office365, введите как обычно с помощью Enter.
5 вариантов использования функции ИНДЕКС (INDEX)
Пример 1: Находим текст в строке
Используйте функцию SEARCH (ПОИСК), чтоб отыскать какой-нибудь текст снутри текстовой строчки. В этом примере мы будем находить одиночный знак (введённый в ячейке B5) снутри текстовой строчки, находящейся в ячейке B2.
Если текст найден, функция SEARCH (ПОИСК) вернет номер позиции его первого знака в текстовой строке. Если не найден, результатом будет сообщение о ошибке #VALUE! (#ЗНАЧ).
В случае, когда результатом является ошибка, Вы сможете применять функцию IFERROR (ЕСЛИОШИБКА), чтоб заместо выполнения функции SEARCH (ПОИСК) вывести соответственное сообщение. Функция IFERROR (ЕСЛИОШИБКА) возникла в Excel, начиная с версии 2007. В наиболее ранешних версиях этот же итог можно было получить, используя IF (ЕСЛИ) вкупе с ISERROR (ЕОШИБКА).
=IFERROR(SEARCH(B5,B2),»Not Found») =ЕСЛИОШИБКА(ПОИСК(B5;B2);»Not Found»)
Функция ЕСЛИ СОДЕРЖИТ
Наверное, многие задавались вопросом, как найти функцию в EXCEL«СОДЕРЖИТ» , чтобы применить какое-либо условие, в зависимости от того, есть ли в текстовой строке кусок слова , или отрицание, или часть наименования контрагента, особенно при нестандартном заполнении реестров вручную.
Такой функционал возможно получить с помощью сочетания двух обычных стандартных функций – ЕСЛИ и СЧЁТЕСЛИ .
Рассмотрим пример автоматизации учета операционных показателей на основании реестров учета продаж и возвратов (выгрузки из сторонних программ автоматизации и т.п.)
У нас есть множество строк с документами Реализации и Возвратов .
Все документы имеют свое наименование за счет уникального номера .
Нам необходимо сделать признак « Только реализация » напротив документов продажи, для того, чтобы в дальнейшем включить этот признак в сводную таблицу и исключить возвраты для оценки эффективности деятельности отдела продаж.
Выражение должно быть универсальным , для того, чтобы обрабатывать новые добавляемые данные .
Для того, чтобы это сделать, необходимо:
-
- Начинаем с ввода функции
ЕСЛИ
-
- (вводим
«=»
-
- , набираем наименование
ЕСЛИ
-
- , выбираем его из выпадающего списка, нажимаем
fx
-
- в строке формул).
В открывшемся окне аргументов, в поле Лог_выражение вводим СЧЁТЕСЛИ() , выделяем его и нажимаем 2 раза fx.
Далее в открывшемся окне аргументов функции СЧЁТЕСЛИ в поле «Критерий» вводим кусок искомого наименования *реализ* , добавляя в начале и в конце символ * .
Такая запись даст возможность не думать о том, с какой стороны написано слово реализация (до или после номера документа), а также даст возможность включить в расчет сокращенные слова «реализ.» и «реализац.»
- Аргумент «Диапазон» — это соответствующая ячейка с наименованием документа.
- Далее нажимаем ОК , выделяем в строке формул ЕСЛИ и нажимаем fx и продолжаем заполнение функции ЕСЛИ.
- В Значение_если_истина вводим « Реализация », а в Значение_если_ложь – можно ввести прочерк « — »
- Далее протягиваем формулу до конца таблицы и подключаем сводную.
Теперь мы можем работать и сводить данные только по документам реализации исключая возвраты . При дополнении таблицы новыми данными, остается только протягивать строку с нашим выражением и обновлять сводную таблицу.
Если материал Вам понравился или даже пригодился, Вы можете поблагодарить автора, переведя определенную сумму по кнопке ниже:
(для перевода по карте нажмите на VISA и далее «перевести»)
Рассмотрим использование функции ЕСЛИ в Excel в том случае, если в ячейке находится текст.
Будьте особо внимательны в том случае, если для вас важен регистр, в котором записаны ваши текстовые значения. Функция ЕСЛИ не проверяет регистр – это делают функции, которые вы в ней используете. Поясним на примере.
Повторное использование функций
Теперь мы можем проиллюстрировать хороший случай повторного использования функции. Рассмотрим следующую программу:
Пока эта программа работает, но она немного избыточна. Фактически, эта программа нарушает один из основных принципов хорошего программирования: «Не повторяйся» (или англоязычная аббревиатура DRY, т.е. «Don’t Repeat Yourself»).
Почему повторяющийся код плох? Если бы мы хотели изменить текст «Enter an integer:» на что-то другое, нам пришлось бы обновить его в двух местах. А что, если бы мы захотели инициализировать 10 переменных вместо 2? Это было бы большое количество избыточного кода (что сделало бы наши программы длиннее и сложнее для понимания) и много места для вкрадывания опечаток.
Давайте обновим эту программу, чтобы использовать нашу функцию , которую мы разработали выше:
Эта программа создает следующий вывод:
В этой программе мы дважды вызываем : один раз для инициализации переменной и один раз для инициализации переменной . Это избавляет нас от дублирования кода для ввода пользовательских данных и снижает вероятность ошибки. Как только мы узнаем, что работает для одной переменной, она будет работать для любого их количества, сколько нам будет нужно.
В этом суть модульного программирования: возможность написать функцию, протестировать ее, убедиться, что она работает, а затем знать, что мы можем повторно использовать ее столько раз, сколько захотим, и она будет продолжать работать (пока мы не изменим эту функцию – после чего нам придется ее повторно протестировать).
Лучшая практика
Следуйте рекомендациям DRY: «не повторяйся». Если вам нужно сделать что-то более одного раза, подумайте, как изменить свой код, чтобы удалить как можно больше избыточности. Переменные можно использовать для хранения результатов вычислений, которые необходимо использовать более одного раза (чтобы нам не нужно было повторять вычисления). Функции можно использовать для определения последовательности инструкций, которые мы хотим выполнять более одного раза. А циклы (которые мы рассмотрим в следующей главе) можно использовать для выполнения инструкции более одного раза.
10) ПОИСКПОЗ
Функция ПОИСКПОЗ помогает найти указанный элемент в массиве ячеек и определяет его положение.
Синтаксис
Формула функции:
=ПОИСКПОЗ(искомое_значение;просматриваемый_массив;тип_сопоставления).
«Искомое значение» и «просматриваемый массив» – обязательные аргументы, «тип сопоставления» – необязательный.
Рассмотрим подробнее аргумент «тип сопоставления». Он указывает, каким образом сопоставляется найденное значение с искомым. Существует 3 типа сопоставления:
1 – значение меньше или равно искомому (при указании данного типа нужно учитывать, что просматриваемый массив должен быть упорядочен по возрастанию);
0 – точное совпадение;
-1 – наименьшее значение, которое больше или равно искомому.
Примеры
Рассмотрим следующий пример. Здесь я попыталась узнать, какой из запросов в приведенной таблице имеет количество переходов, которое равно или меньше 900.
Формула функции здесь: =ПОИСКПОЗ(900;B2:B6;1). 900 – искомое значение, B2:B6 – просматриваемый массив, 1 – тип сопоставления (меньше или равно искомому). Результат – «3», то есть третья позиция в указанном диапазоне.
Как сделать проверку данных в Excel
Выбор похожих значений
16.11.2011, 00:22
Выбор последних значенийДрузья, день добрый. Есть вот такой файлик, хотел узнать есть ли возможность не прибегая в VBA.
выбор значений из столбцаЗдравствуйте, помогите с формулой или может есть уже такая тема?! (тогда направте туда.
Выбор значений таблицы по условиюВсем день добрый. Нужна помощь, в 1 столбце даны наименования, следующие 4 столбца — значения.
Выбор только числовых значенийЗдравствуйте, Уважаемые Форумчане. Помогите решить проблему. Нужно что бы из столбца А, таблицы.
16.11.2011, 01:57
2
Вложения
| выбор.xls (19.5 Кб, 196 просмотров) |
16.11.2011, 11:26
3
16.11.2011, 11:31
4
Вложения
| похожие коды.zip (38.5 Кб, 109 просмотров) |
16.11.2011, 12:48
5
Формулы массива вводятся нажатием Ctrl+Shift+Enter.
PS Maxbank, скажите, зачем выкладывать тысячи строк?! Почему не обойтись десятком? Даже десяток много. Хватило бы и ДВУХ строк.
Вложения
| Maxbank.rar (45.7 Кб, 108 просмотров) |
16.11.2011, 13:03
6
Спасибо. Извините, не подумал удалить лишние строки.
Добавлено через 5 минут Или я что-то не понял или все-таки нет нужного результата. В листе1 есть коды, например, 100-06319, 100-06319Р. Их надо вытянуть и вставить в Лист2 напротив кода 100-06319P, например через запятую или каждый совпадающий код в отдельном столбце.
18.11.2011, 02:12
7
Вложения
| maxbank.xls (25.0 Кб, 204 просмотров) |
21.11.2011, 19:19
8
25.01.2012, 21:49
9
25.01.2012, 21:59
10
25.01.2012, 22:09
11
Вложения
| maxbank_1.xls (13.5 Кб, 40 просмотров) |
25.01.2012, 22:24
12
25.01.2012, 23:03
13
25.01.2012, 23:12
14
25.01.2012, 23:26
15
25.01.2012, 23:29
16
Не знаю. Я вижу только тот файл, который Вы приложили. В нём будет работать. Давайте файл с ошибкой — я поправлю.
25.01.2012, 23:42
17
В файле есть код 203-05058 и 203-05058P. Формула ПОИСК(B798;B10:B1108) (где В798=203-05058) показывает, что совпадений 1. Хотя в тестовом файле maxbank_1.xls все работает правильно. Подсчитывает и результат дает 10.
Добавлено через 59 секунд Можно выложить файл со всеми строками?
Добавлено через 11 минут Отправил на мыло.
25.01.2012, 23:49
18
Нет. Только проблемные. Вес файла не более 100 Кб в xls.
ЗЫ Видимо это я косякнул. Правильная формула должна быть такая:
26.01.2012, 00:21
19
не работает в файле, котром надо.
Добавлено через 3 минуты СУММПРОИЗВ(ЕЧИСЛО(ПОИСК(B798&»*»;B10:B1108))*E10:E1104) И так тоже.
26.01.2012, 00:26
20