Excel формулу преобразовать в число

Как перевести время в число в Excel

Еще раз привет. Как обычно начну статью с того, что скажу — часто встречаюсь с каким-нибудь незнанием со стороны пользователей и сейчас расскажу об этом. Банально, но действительно так и приходят идеи о статье. Сегодня хочется написать отдельный текст про работу со временем в Excel. Как можно быстро обработать временные данные? Как перевести время в число в Excel? Как не запутаться в этом — читаем в тексте!

Время и число в Excel. =СЕГОДНЯ()

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

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

Любое значение дата_время в Excel — это число преобразованное в нужный формат числа при помощи опции Формат ячеек!

Приведем пример. Вам нужно задать сегодняшнюю дату? Пожалуйста! Записываем функцию =СЕГОДНЯ() — без аргумента в скобках. Она возвращает сегодняшнее число. Как правило, сразу в этой ячейке будет дата в правильном формате.

Но что будет, если данные перевести в формат Числовой? Появиться число 42544,00.

Тоже самое будет и со временем в формате Время (см. на первой картинке), значение будет равно, например 21:00. Если сравнить значения чисел, то они будут идентичны.

Соответственно, как складывать и вычитать такие данные? Да как обычные числа! Если вы хотите, чтобы у вас в ячейке отображалось дата_время, просто сложите числа и задайте нужный формат. Как на примере:

Так же быть и с вычитанием данных друг из друга.

Теперь можно перейти к хитростям обработки дата_время.

Отделить дату от времени и наоборот в Excel

Предположим, что у вас уже есть числа в формате дата_время, например вы выгрузили их из учетной системы или вам их прислали по почте.

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

Воспользуйтесь формулой для определения времени из дата_время

Формула находит целое значение числа, что будет для нас датой и вычитает из общего значения — разница будет для нас временем, не забудьте перевести в полученные данные в формат времени.

Как перевести время в число в Excel? Функции ДЕНЬ, ЧАС, МИНУТЫ

Это тоже просто, если знаете хитрость. Очень и очень часто, нам удобнее использоваться формат 3,5 часа, чем 3:30. Т.е. перевод числа во временном формате в число, но с нужным значением. Для этого есть прекрасные функции Excel =ЧАС(), =МИНУТЫ().

Чтобы перевести время 3:30 в просто три с половиной часа, нам нужно воспользоваться формулой.

Как работает: мы находим количество полных часов функцией ЧАС, затем находим количество полных минут и делим их на 60, чтобы получить минут в часе. Готово!

Если необходимо подсчитать количество дней во времени (например 33:30:00) нужно воспользоваться функцией ДЕНЬ.

Важно! Не забудьте перевести ячейку в числовой формат, иначе после выполнения формулы, или в ячейке будет выдаваться 0:00:00 Это будет удобно при работе с большим объемом данных, вычислениями со временем или группировкой данных в сводной таблице

Макрос VBA.

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

Нажмите сочетание клавиш Alt+F11 или откройте вкладку Разработчик (Developer) и нажмите кнопку Visual Basic. В появившемся окне редактора добавьте новый модуль через меню Insert — Module и скопируйте туда следующее небольшое выражение:

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

Что делает этот макрос?

Вы можете выделить несколько областей данных для конвертации (можно использовать мышку при нажатой клавише CTRL). При этом, если в ваших числах в качестве разделителя десятичных разрядов используется запятая, то она будет автоматически заменена на точку. Ведь в Windows чаще всего именно точка отделяет целую и дробную части числа. А при экспорте данных из других программ запятая в этой роли встречается почему-то очень часто.

Чтобы использовать этот код, выделяем область на рабочем листе, которую нужно преобразовать. Жмем на значок «Макросы», который расположен на вкладке «Разработчик» в группе «Код». Или нам поможет комбинация клавиш ALT+F8.

Открывается окно имеющихся макросов. Находим «Текст_в_число», указываем на его и жмем на кнопку «Выполнить».

Как преобразовать текст в число в Excel?

Бывает что, когда пытаешься произвести вычисления с данными, формулы не работают или Excel выдает ошибку. Причины сразу могут быть неясны. Особенно часто проблема встречается после выгрузки из учетных систем, таких как 1С или SAP. Как избежать проблемы, когда формулы не работают? Чаще всего необходимо преобразовать текст в число в Excel. Несколько полезных способов, как бороться с этим несчастьем читайте ниже.

1. Преобразовать текст в число. Сделайте формат ячеек — число

С самого начала проверьте формат чисел. Выделите диапазон с данными (на нашем примере столбец B). Нажмите горячую клавишу ctrl + 1 (меню Формат ячеек если правой кнопкой мыши). На вкладке Число выберите Формат ячеек — Числовой.

Очень удобно пользоваться меню Число на главной панели. Здесь сразу доступны удобные форматы. Слева-направо: денежный, процентный, числовой + кнопки уменьшения/увеличения количества знаков после запятой. Рекомендую.

2. Исправьте ошибки — преобразуйте текст в число специальным инструментом

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

В нашем случае Число сохранено как текст — кликните Преобразовать в число. Вуаля!

Это же доступно по адресу Формулы — Раздел Зависимости формул — Проверка формул

3. Умножьте все числа на 1

Не помог предыдущий вариант? Значит, ошибка довольно редкая, но решается просто. Есть крутая хитрость. В столбце С все значения столбца B перемножьте на 1. Да, буквально так, для ячейки C2 запишите =B2*1 и протяните вниз. Так Excel принудительно преобразовывает имеющиеся значения в число.

4. Удалите лишний символ в ячейках при помощи Найти и Заменить

И пункт 3 не помог? Значит в ячейках закрался какой-то символ, например как в ячейке В7 на первой картинке после первого числ стоит символ » «, это может быть даже обычный пробел (поэтому скопируйте именно этот символ). Такими вещами очень часто грешит 1С, причем любой конфигурации.

Пользуемся инструментом Найти и заменить, подробное описание здесь . Если быстро нажимаете ctrl + H , появиться окно.

В поле Найти: вставляем скопированный символ. В поле Заменить на: ничего не ставим (т.е. заменяем на пусто). Жмем Заменить все.

5. Функция ЗНАЧЕН

И, конечно, есть универсальный способ применить функцию =ЗНАЧЕН(). Она лечит все ошибки с числами, кроме лишних символов (не касается пробелов), и преобразовывает текст в число. Очень удобно использовать ее для проверок в расчетах, как подстраховку от ошибок.

6. Перевод времени в число

На самом деле время и дата это уже число, но в нужном формате. Но в их обработке может быть сразу несколько хитростей. Читаем в этой статье.

P.S.

Кстати, для того чтобы убрать лишние пробелы в начале и конце ячейки с текстом (например, при записи ФИО мы часто ошибаемся и ставим пробел в конце, который не видно), а так же, чтобы убрать двойные пробелы, есть классная функция =СЖПРОБЕЛЫ() . Она убирает все лишние пробелы!

Цифры прописью в Excel

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

Интересно. Как сделать подобное в Word читайте здесь.

Число прописью в Экселе. Установка

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

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

Делаем в следующем порядке:

1. Скачиваем и распаковываем файл кликнув здесь.

2. Закиньте файл NUMSTR.XLA в папку Library, которая находится C:ProgramFiles (x86)MicrosoftOfficeOffice14Library (возможно расположение немного отличается, зависит от версий ПО).

3. Запустите Excel и на вкладке «Разработчик» кликните по кнопке «Надстройки» ( если ее нет, читайте ниже ).

Внимание. Если нет вкладки «Разработчик», пройдите Файл → Параметры → Настройка ленты и установите галку напротив пункта «Разработчик»

4. В окне «Надстройки» отметьте пункт NumStrAdd-in и нажмите ОК.

Усё можно пользоваться.

Число прописью в Excel. Как пользоваться

Расширение установлено, теперь несколько слов как использовать:

5. Для примера напишите в произвольной ячейке число цифрами.

6. Кликните по кнопке «Вставить Функцию».

7. В открывшемся окне «Мастер функций» в списке «Категория» выберите «Определенные пользователем».

8. После этого выберите необходимую функцию:

NumStr – отобразит цифры буквами в Excel;

RubStr – преобразует число прописью в рублях;

USDStr – конвертирует число прописью в долларах;

EURStr – выведет сумму прописью в евро.

9. В следующем окне «Аргументы функции» укажите в поле ячейку с данными (кликнут курсором) или непосредственно число цифрами, завершив нажатием кнопки ОК и растянувшейся улыбкой.

Интересно. Как закрепить строки в Excel читайте тут.

Преобразование текста в число в Excel

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

Преобразование текста в число с функцией ЗНАЧЕНИЕ

Функция VALUE — это часто используемая функция для преобразования текста в число, общий синтаксис:

=VALUE (text)

text: текст или ячейка, содержащая текст, который нужно преобразовать в число.

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

=VALUE(A2)

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

Вот простая формула, которая также может помочь вам преобразовать текст в числа, примените формулу ниже:

=A2+0

Преобразование текста в число с помощью функций Right Left или MID

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

=LEFT (text, )
=RIGHT(text, )
=MID(text, start_num,num_chars)

• text: текстовая строка, из которой вы хотите извлечь символы справа, слева или посередине.
• num_chars: количество символов для извлечения справа, слева или посередине.
• start_num: расположение первого символа, который вы хотите извлечь.

Пожалуйста, примените приведенные ниже формулы в ячейки в зависимости от ваших потребностей:

=RIGHT(A2,4)+0
=LEFT(A5,3)+0
=MID(A7,5,4)+0

Внимание: В конце формулы +0 принудительно преобразовывает возвращаемый числовой текст в действительное число

Используемые относительные функции:

• VALUE:
• Функция VALUE помогает преобразовать текстовую строку, представляющую число (например, число, формат даты или времени) в число.

• RIGHT:
• Функция RIGHT извлекает определенное количество символов из правой части текстовой строки.

• LEFT:
• Функция LEFT извлекает заданное количество символов из левой части предоставленной строки.

• MID:
• Чтобы вернуть определенные символы из середины текстовой строки.

Другие статьи:

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

• Извлечь несколько строк из ячейки
• Если у вас есть список текстовых строк, разделенных разрывами строк (это происходит при нажатии клавиш Alt + Enter при вводе текста), и теперь вы хотите извлечь эти строки текста в несколько ячеек, как показано ниже. Как бы вы могли решить это с помощью формулы в Excel?

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

Преобразовать в число

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

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

1. Выберите этот символ. В раскрывающемся списке выберите «Преобразовать в число».

Это обновит все выбранные вами текстовые числа до общего числового формата данных.

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

Извлечение числа из текста

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

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

=ЗНАЧЕН(ПРАВСИМВ(A2;3))

Если не использовать функцию ПРАВСИМВ внутри функции ЗНАЧЕН, то результат будет в символьном формате, что мешает выполнять любые вычислительные операции с получившимися цифрами.

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

Как получить N-е слово из текста.

Этот пример демонстрирует оригинальное использование сложной формулы ПСТР в Excel, которое включает 5 различных составных частей:

• ДЛСТР — чтобы получить общую длину.
• ПОВТОР — повторение определенного знака заданное количество раз.
• ПОДСТАВИТЬ — заменить один символ другим.
• ПСТР — извлечь подстроку.
• СЖПРОБЕЛЫ — удалить лишние интервалы между словами.

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

Где:

• Строка — это исходный текст, из которого вы хотите извлечь желаемое слово.
• N – порядковый номер слова, которое нужно получить.

Например, чтобы вытащить второе слово из A2, используйте это выражение:

Или вы можете ввести порядковый номер слова, которое нужно извлечь (N) в какую-либо ячейку, и указать эту ячейку в формуле, как показано на скриншоте ниже:

Как работает эта формула?

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

ПОДСТАВИТЬ и ПОВТОР заменяют каждый пробел в тексте несколькими. Количество этих дополнительных вставок равно общей длине исходной строки: ПОДСТАВИТЬ($A$2;» «;ПОВТОР(» «;ДЛСТР($A$2)))

Вы можете представить себе промежуточный результат как «астероиды» слов, дрейфующих в пространстве, например: слово1-пробелы-слово2-пробелы-слово3-… Эта длинная строка передается в текстовый аргумент ПСТР.

• Затем вы определяете начальную позицию для извлечения (первый аргумент), используя следующее уравнение: (N-1) * ДЛСТР(A1) +1. Это вычисление возвращает либо позицию первого знака первого слова, либо, чаще, позицию в N-й группе пробелов.
• Количество букв и цифр для извлечения (второй аргумент) — самая простая часть — вы просто берете общую первоначальную длину: ДЛСТР(A2).
• Наконец, СЖПРОБЕЛЫ избавляется от начальных и конечных интервалов в извлечённом тексте.

Приведенная выше формула отлично работает в большинстве ситуаций. Однако, если между словами окажется 2 или более пробелов подряд, это даст неверные результаты (1). Чтобы исправить это, вложите еще одну функцию СЖПРОБЕЛЫ в ПОДСТАВИТЬ, чтобы удалить лишние пропуски между словами, оставив только один, например:

Следующий рисунок демонстрирует улучшенный вариант (2) в действии:

Если ваш исходный текст содержит несколько пробелов между словами, а также очень большие или очень короткие слова, дополнительно вставьте СЖПРОБЕЛЫ в каждое ДЛСТР, чтобы вы были застрахованы от ошибки:

Я согласен с тем, что это выглядит немного громоздко, но зато безупречно обрабатывает все возможные варианты.

Экономия 5 минут в час за счет более продуктивной работы дает за год экономию в 4 рабочие недели

Обратное преобразование даты в число

Когда вы связываете несколько систем друг с другом возможны всякие…нюансы. Вроде таблицы с числами вида

Само собой при выгрузке не имелся ввиду июнь 2034 года или 4 августа 2016 года. А конкретные числа 6.34 или 4.08, которые Excel по ошибке попытался преобразовать в даты. Тот же самый механизм, что и если вы введете в любой ячейке любого листа, например, текстом «12-15» и нажмете на Enter. Вы думаете, что вводите числовой диапазон, а Excel думает, что это незавершенная дата вида 12-15-2016 и «помогает» вам дописать его.

Исправить при вводе это автоматическое преобразование можно добавив в начало формулы одинарный апостроф — ‘

В этом случае неважно что вы введете в значение ячейки (в том числе формулу) — Excel будет считать, что дальше текст

Само преобразование может пойти по трем путям (в т.ч. в зависимости от региональных разделителей): 1. Если второе число меньше или равно 12. Excel будет пытаться определить в первом числе день, во втором числе месяц 2. Если второе число больше 12, но меньше или равно 29. Excel будет считать первое число месяцем, второе — годом. Год будет считаться от 1900 3. Если второе число больше или равно 30. Excel будет считать первое число месяцем, второе — годом. Год будет считаться от 2000

Итак, причины и механизмы возникновения ошибки ясны. Как поправить? Простая махинация вроде —A2 не поможет ведь после «дописки» значения до даты прошло преобразование и то же число 6.34 это теперь 12 571.

Исправление немного сложнее. Во-первых посмотрим по какому пути пошло преобразование. Если год от этой даты совпадает с текущим годом — значит мы имеем дело с первой ситуацией, в ином случае — ситуация 2 или 3.

Запишем формулу с вложенным ЕСЛИ():

Для Ситуации 1 мы должны склеить дни (целое число) и номер месяца (дробная часть числа):

Для Ситуации 2 и 3 мы должны взять номер месяца (целое число) и две последние цифры года (дробная часть числа).

Осталось только подставить эти значения в наш ЕСЛИ() в соответствующие группы формулы:

И вуаля. Можем преобразовать хоть 10 000 строк за раз.

PS К сожалению, в ситуации 1 мы не можем точно знать какое число было преобразовано в дату — с нулем, или без него как первый знак после запятой. То есть 4,08 и 4,8 в данном случае будут преобразованы одинаково.

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

Использование текста в столбце

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

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

1. Выберите «Данные» в меню, а затем выберите «Текст в столбцы» в разделе «Инструменты данных» на ленте.

1. В окне мастера оставьте выбранным разделитель по умолчанию и выберите Далее.

1. На следующей странице мастера оставьте выбранную вкладку по умолчанию и снова выберите Далее.

1. Наконец, на последней странице мастера убедитесь, что в разделе Формат данных столбца выбрано Общее. В поле «Место назначения» вы можете выбрать новый столбец, в который нужно перенести числовые данные, или просто оставить текущий выбранный столбец без изменений. Выберите Готово.

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

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

Быстрая конвертация при помощи Специальной вставки.

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

• Скопируйте любую пустую ячейку (выделите ее и нажмите Ctrl + C).
• Выберите диапазон с текстовыми значениями, которые вы хотите превратить в даты.
• Щелкните правой кнопкой мыши выделенную область, выберите «Специальная вставка» и выберите «Сложить» в диалоговом окне:

Нажмите кнопку ОК, чтобы завершить работу и закрыть диалоговое окно.

Что вы только что сделали, это попросили Эксель добавить ноль (пустую ячейку) к вашим текстовым датам. Чтобы выполнить это, программа конвертирует текст в число, и, поскольку добавление нуля не приводит к изменению значения, вы получаете именно то, что хотели — порядковый номер дня. 

Как и обычно, далее вы устанавливаете для этих ячеек нужный вид с помощью диалогового окна «Формат ячеек».

Более сложные ситуации

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

Например, я нашел сообщение на форуме, где кто-то хотел извлечь числа из строки, например, «45t * 65 /», чтобы в итоге он получил «4565». Другой автор дал следующую формулу в качестве одного из способов сделать это:

=SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(—MID(A1,ROW($1:$25),1))* ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)

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

,

Преобразование времени

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

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

Для удобства также приводим ссылку на оригинал (на английском языке).

Предположим, вам нужно узнать, сколько часов находится в 5,5 дней или сколько дней в 3,45 лет или сколько месяцев проходит между датой начала и датой окончания.

Существует несколько способов преобразования единиц времени.

Для преобразования из одной единицы времени в другую используйте функцию Convert .

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

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

Для преобразования десятичного формата в часы: минуты: секунды используйте оператор деления (/) вместе с функцией текст .

Примечание: Вы также можете применить формат времени без использования функции текст для задания формата. Чтобы просмотреть число как раз, выделите ячейку, а затем на вкладке Главная в группе число щелкните стрелку рядом с полем числовой формат . Затем выберите пункт другие числовые форматы, в списке Категория выберите пункт другой , а затем в поле тип выберите настраиваемый формат. Помните: Если используется формат, примененный к функции текст и числовой формат, функция текст имеет приоритет.

Excel SEARCH функция

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

Синтаксис функции ПОИСК в Excel:

=SEARCH (find_text, within_text, )

 Аргументы:

• find_text: Необходимые. Символ или текст, который вы хотите найти.
• within_text: Необходимые. Текстовая строка, в которой нужно искать.
• start_num: Необязательный. С какой позиции вы хотите начать поиск в текстовой строке. Если этот аргумент опущен, поиск начинается с первого символа данной текстовой строки.

Заметки:

• 1. Функция ПОИСК вернет позицию первого появления find_text внутри текстовой строки, если start_num опущен
• 2. Функция ПОИСК не чувствительна к регистру, если вам нужно выполнить поиск с учетом регистра, вы должны использовать функцию НАЙТИ.
• 3. Если значение find_text не найден, функция ПОИСК вернет # ЗНАЧ! значение ошибки.
• 4. Если start_num не больше 0 или больше длины внутри_текст, Значение! также возвращается значение ошибки.
• 5. Функция ПОИСК позволяет использовать подстановочные знаки в find_text Аргумент:
  • Вопросительный знак (?): соответствует одному символу;
  • Звездочка (*): соответствует любой серии символов;
  • Чтобы найти настоящий вопросительный знак (?) Или звездочку (*), введите перед ними тильду (~), например: ~* или ~?.

 Примеры:

Пример 1: Поиск символа или текста в текстовой строке (с начала)

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

=SEARCH(B2, A2)

Примечание. В приведенной выше формуле B2 — это ячейка, содержащая символ или текст, позицию которого вы хотите найти, A2 — это текстовая строка, в которой вы хотите выполнить поиск.

Затем вы получите позицию искомого текста от начала текстовой строки, см. Снимок экрана:

Пример 2: поиск символа или текста в текстовой строке (с указанным началом)

Иногда вам может потребоваться получить позицию определенного символа в текстовой строке с определенного начала, а не с начала первого символа. В этом случае вам просто нужно добавить третий аргумент start_num.

Введите или скопируйте следующую формулу в пустую ячейку:

=SEARCH(B2, A2,C2)

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

Снимок экрана:

Затем были возвращены позиции символа или текста в текстовой строке, с которой вы хотите начать поиск, см. Снимок экрана:

Пример 3: Найдите и извлеките текст между скобками

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

Вставьте следующую формулу в пустую ячейку:

=MID(A2,SEARCH(«(«,A2)+1, SEARCH(«)»,A2)-SEARCH(«(«,A2)-1)

И все тексты между скобками были извлечены сразу, см. Снимок экрана:

 Дополнительные функции:

• Функция Excel ВПРАВО
• Функция RIGHT используется для возврата текста справа от текстовой строки.

• Функция ЧИСЛО в Excel
• Функция NUMBERVALUE помогает вернуть действительное число из числа, хранящегося в виде текста.

• Функция ЗАМЕНИТЬ в Excel
• Функция ЗАМЕНИТЬ может помочь вам найти и заменить символы на основе заданного местоположения в текстовой строке новым текстом.

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

Еще одна текстовая функция – условная. Она проверяет строку на тип данных. Если в строке расположен текст – он выводится на экран. Если же это число, функция возвращает пустую строку. Синтаксис функции такой: =Т(Значение).

Функции преобразования форматов – очень важные и позволяют сделать результаты трудов комфортными и совершенными. Тренируйтесь в их использовании, чтобы показывать высший пилотаж в работе с текстом!

Это последний пост, рассказывающий о текстовых функциях Excel. А в следующей статье мы попробуем применить их на практике. А дальше — будем изучать работу с датами и временем. Заходите почитать, будет полезно!

Функции преобразования типов

Наименования функций преобразования типов, типы возвращаемых данных, диапазоны допустимых значений для аргумента:

Функция	Тип данных	Диапазон значений аргумента
CBool	Boolean	Любое допустимое строковое или числовое выражение.
CByte	Byte	От 0 до 255.
CCur	Currency	От -922 337 203 685 477,5808 до 922 337 203 685 477,5807.
CDate	Date	Любое допустимое выражение даты.
CDbl	Double	От -1,79769313486231E308 до -4,94065645841247E-324 для отрицательных значений; от 4,94065645841247E-324 до 1,79769313486232E308 для положительных значений.
CDec	Decimal	79 228 162 514 264 337 593 543 950 335 для чисел без десятичных знаков. Для чисел с 28 десятичными знаками диапазон составляет 7,9228162514264337593543950335. Наименьшим возможным числом, отличным от нуля, является число 0,0000000000000000000000000001.
CInt	Integer	От -32 768 до 32 767, дробная часть округляется.
CLng	Long	От -2 147 483 648 до 2 147 483 647, дробная часть округляется.
CSng	Single	От -3,402823E38 до -1,401298E-45 для отрицательных значений; от 1,401298E-45 до 3,402823E38 для положительных значений.
CStr	String	Результат, возвращаемый функцией CStr, зависит от аргумента Выражение.
CVar	Variant	Диапазон совпадает с типом Double  для числовых значений и с типом  String  для нечисловых значений.

Замена формулы на ее результат

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

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

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

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

Замена формул с помощью вычисляемых значений

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

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

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

Выбор диапазона, содержащего формулу массива

Щелкните ячейку в формуле массива.

На вкладке Главная в группе Редактирование нажмите кнопку Найти и выделить, а затем выберите команду Перейти.

Нажмите кнопку Дополнительный.

Нажмите кнопку Текущий массив.

Нажмите кнопку ” копировать “.

Нажмите кнопку вставить .

Щелкните стрелку рядом с пунктом Параметры вставки и выберите пункт только значения.

В следующем примере показана формула в ячейке D2, которая умножает ячейки a2, B2 и скидку, полученную из C2, для расчета суммы счета для продажи. Чтобы скопировать фактическое значение вместо формулы из ячейки на другой лист или в другую книгу, можно преобразовать формулу в ее ячейку в ее значение, выполнив указанные ниже действия.

Нажмите клавишу F2, чтобы изменить значение в ячейке.

Нажмите клавишу F9 и нажмите клавишу ВВОД.

После преобразования ячейки из формулы в значение оно будет отображено как 1932,322 в строке формул

Обратите внимание, что 1932,322 является фактическим вычисленным значением, а 1932,32 — значением, которое отображается в ячейке в денежном формате

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

Замена части формулы значением, полученным при ее вычислении

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

При замене части формулы на ее значение ее часть не может быть восстановлена.

Щелкните ячейку, содержащую формулу.

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

Чтобы вычислить выделенный фрагмент, нажмите клавишу F9.

Чтобы заменить выделенный фрагмент формулы на вычисленное значение, нажмите клавишу ВВОД.

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