Check-moscow.ru

Финансы и учет
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как выполнить преобразование между формулой и текстом / значением

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

Как упоминалось выше, значения ячеек в текстовом формате отображаются точно так, как они были введены. Excel рассматривает эти значения как строки, т.е. как текстовые значения, независимо от того, содержат ли ячейки текст или числа. Другими словами, Excel по-разному интерпретирует значения 1 и «1». Оказывается, число можно отобразить как в виде числа, используя числовое форматирование, так и в виде текста, используя текстовое форматирование. Разница между этими числами заключается в том, что числа в числовом формате участвуют в вычислениях, а числа в текстовом формате — нет. Это может привести к ошибкам в расчетах. Чем эти числа отличаются друг от друга?

Excel works!

menu

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

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

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

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

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

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

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

Время дата1

Что происходит, когда данные преобразуются в числовой формат? Число отображается как 42544.00.

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

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

перевести время в число в Excel 1

Это то же самое, что и вычитание данных друг из друга.

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

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

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

Читайте так же:
Как выровнять повторяющиеся или совпадающие значения в двух столбцах в Excel?

Сначала проверьте, является ли номер . Если да, выполните следующие действия. Если нет, следуйте инструкциям в статье.

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

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

Время дата5

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

Хитрость тоже проста. Очень-очень часто нам удобнее использовать формат 3,5 часа, чем формат 3:30. Другими словами, вам нужно преобразовать число в формате времени в число, но с правильным значением. Для этого есть отличные функции Excel =Час(), =Минуты().

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

Как это работает: мы находим количество полных часов с помощью функции HOUR, затем находим количество полных минут и делим на 60, чтобы получить минуты часа.

Так же, как верно подсказали мне в комментариях, можно умножить 3:30 (в ячейке A1) на 24 — получится числовое значение времени.

Если вы хотите посчитать количество дней во времени (например, 33:30:00), используйте функцию DAY.

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

Сгруппировать различные типы данных

Используйте тот же тип данных при работе с элементом Formula. Тип данных для каждого параметра обозначается значком на странице завершения формулы:

  • btn_com_id.png— уникальный идентификатор;
  • btn_com_integer.png— числовой;
  • btn_com_decimal.png— дробный;
  • btn_com_string.png— текстовый;
  • btn_com_lookup9.png— справочное значение;
  • btn_com_date_time.png— значение даты и времени.

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

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

Чтобы преобразовать элемент #ReadOrder.First полученной коллекции ScheduledDueDate#, заключите его в круглые скобки и добавьте метод .ToString(). В этом случае бизнес-процесс, в который включен элемент генерации заголовка активности, будет работать корректно.

Как в Excel заменить точку, на запятую?

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

№ п/пВалютная параПересчетПродажаПокупкаСпрэд
1EUR/USD11.13471.13500.0003
2GBP/USD11.54381.54420.0004
3USD/CHF10.95430.95470.0004
4USD/JPY1001.19481.1945-0.0003

Теперь перейдите на рабочий лист и щелкните правой кнопкой мыши на ячейке A1. В появившемся всплывающем меню выберите опцию «Вставить пользовательский». В диалоговом окне выберите «Текст Unicode» и нажмите OK.

Читайте так же:
Как заблокировать позицию листа, чтобы она всегда была видна в книге Excel?

Текст в кодировке Unicode.Изменение шрифта по умолчанию.

Столбец C — единственный распознаваемый столбец Excel. Значения в этом столбце выравниваются по правому краю. Остальные столбцы выровнены по левому краю. Во всех ячейках стоит «Общий», и только в ячейках D3, D5, E3, E5 указан формат «Дата». Данные копируются с помощью специальной вставки, при этом все форматирование из исходной таблицы удаляется. Одной из причин этого является отсутствие запятой, только точка. Расчеты с таким типом данных невозможны, так как они не подготовлены.

Примечание. Если вы копируете данные из других источников без специальной вставки, формат копируется вместе с данными. Ячейки в этом случае могут быть отформатированы как «Общий» (по умолчанию). Числа и текст в этом случае невозможно различить визуально.

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

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

Способ 1 замена точки на запятую в Excel через Блокнот

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

  1. Скопируйте данные из исходной таблички на этой странице. Откройте программу Блокнот Windows («Пуск»-«Все программы»-«Стандартные»-«Блокнот») и вставьте в него скопированные данные для подготовки. Вставка в Блокнот.
  2. Выберите в меню «Правка» опцию «Заменить» (или комбинацию горячих клавиш CTRL+H). В появившимся диалоговом окне, введите в поле: «Что» точку (.), а в поле «Чем» запятую (,). И нажмите кнопку «Заменить все».

Блокнот заменил все полные остановки запятыми. Теперь данные готовы к копированию и вставке на лист.

Готово.

Этот способ очень простой, но эффективный.

Способ 2 временно меняем настройки Excel

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

Для этого откройте «Файл»-«Настройки»-«Дополнения». На вкладке «Изменить настройки» временно снимите флажок «Использовать системные разделители». В поле «Разделитель целых и дробных частей» уберите запятую и введите точку.

Разделитель целой и дробной части.

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

Внимание! Чтобы этот метод работал, необходимо внести все изменения перед импортом данных.

Способ 3 временно меняем системные настройки Windows

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

Открываем «Пуск»-«Панель управления»-«Языки и региональные стандарты». Нажимаем на кнопку «Дополнительно». В появившимся окне изменяем в первом поле «Разделитель целой и дробной части» — вводим нужное нам значение. Дальше ОК и ОК.

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

Настройка формата в Windows.

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

Способ 4 используем функцию найти и заменить в Excel.

Этот метод аналогичен первому. Только здесь мы используем ту же функцию Блокнота, но в самом Excel.

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

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

  1. Предварительно выделите столбцы где будут находится дробные числа с точкой в качестве разделителя. В данном случаи это 3 столбца D:F.
  2. Установите для выделенного диапазона текстовый формат ячеек, чтобы заранее избежать автоматического преобразования в формат даты некоторых чисел. Для этого выберите текстовый формат из выпадающего списка на закладке «Главная» в разделе «Число». Или нажмите CTRL+1 , в появившимся окне «Формат ячеек» выберите закладку «Число», а в разделе «Числовые форматы» укажите «Текстовый». Задать текстовый формат ячеек.
  3. Скопируйте таблицу и щелкните правой кнопкой мышки по ячейке A1. Из контекстного меню выберите опцию «Специальная вставка». Выберите «Текст в кодировке Unicode» и нажмите ОК. Обратите внимание, как теперь отображаются значения в ячейках: D3, D5, E3, E5, в отличии от самого первого копирования таблицы. Вставка таблицы.
  4. Нажмите на инструмент «Главная»-«Найти и выделить»-«Заменить» (или нажмите комбинацию CTRL+H). Найти и заменить в Excel.
  5. В появившемся окне введите в поле «Найти» — точку, а в во второе поле введите запятую. И нажмите «Заменить все».
  6. Снова выделите 3 столбца D:F и измените формат ячеек на «Числовой» CTRL+SHIFT+1. Не забудьте увеличить разрядность до 4-х, как описано в пункте №2.

Все полные остановки были заменены запятыми. И текст был автоматически преобразован в числа.

Можно использовать формулу с функциями в отдельном столбце вместо 4-го и 5-го:

Например, выберите диапазон ячеек G2:I5, введите формулу и нажмите CTRL+Enter. Затем переместите значения в ячейках из области G2:I5 в область D2:F5.

Эта формула находит точку в тексте с помощью функции Найти. Затем другая функция меняет его на запятую. А функция SIGN преобразует результат в число.

В записи числа имеются посторонние символы

Способы решения проблемы

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

Читайте так же:
Как добавить указатель мыши к определенной форме в Excel?

Другой тип пробела, который может помешать сохранению ячейки в правильном формате, — это так называемый неразрывный пробел (с кодом 160). Описанный выше метод недостаточен для решения этой проблемы. Чтобы решить эту проблему, скопируйте этот символ прямо из ячейки, а затем вставьте его в поле «Найти» или введите в это поле Alt + 0160 (но это число не работает на ноутбуках, так как для этой задачи требуется цифровая клавиатура).

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

Почему не суммируется (число сохраняет как текст) в Excel

6

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

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

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

  1. Перед дефисом, который находится в середине строки. Только в трех случаях разрешается ставить дефис в начале строки: когда используется прямая речь, когда дефис отмечает элемент списка и когда дефис заменяет дефис. Чтобы дефис не переносился на новую строку, ему должен предшествовать пробел, отличный от дефиса.
  2. Между числом и единицей. Эта проблема возникает довольно часто. В результате человек может перенести число в таблицу Excel через пробел, а единица окажется в другой ячейке. Как правило, в таких случаях необходимо использовать пробел за пределами начала, чтобы избежать их разделения. Однако при переносе в Excel могут возникнуть проблемы.
  3. Перед знаком процента. В этом случае ячейка может не переводиться в процентный формат. Конечно, вы не везде найдете знак процента, отделенный от числа пробелом, но некоторые люди считают это правильным. Поэтому при переносе данных в Excel они могут быть отображены в текстовом формате. То же самое справедливо и для обычной комнаты.
  4. Цифры и знаки абзаца. Такая ситуация также часто возникает, когда необходимо перенести разделы руководств или части документов в электронные таблицы.
  5. Многозначные числа. Наиболее распространенная причина переноса неразрывных пробелов в таблицу Excel. Согласно правилам, в многозначных числах обязательно должны быть пробелы, чтобы пользователям было легче их читать. Однако если число слишком велико, его можно автоматически перенести по частям на следующую строку. Для решения этой проблемы используется неразрывный пробел. И если такое число скопировать в ячейку, оно автоматически отобразится в текстовом формате.
Читайте так же:
Как выделить ячейки с одинаковыми / повторяющимися значениями в Excel?

После переноса данных из документа Word в Excel обычно появляются пустые поля.

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

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

Кроме того, в Excel может потребоваться поставить неразрывный пробел. Для этого нажмите комбинацию клавиш ALT и 0160. В Windows для ввода неразрывного пробела практически из любой программы можно использовать стандартную комбинацию горячих клавиш CTRL + SHIFT + ПРОБЕЛ.

Есть два основных способа решения этой проблемы — использовать функцию «Найти/Заменить» или формулу.

Операция Найти/Заменить

Чтобы удалить пробелы, можно воспользоваться функцией «Найти/Заменить». Введите пробел в первое поле и оставьте нижнее поле пустым.

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

Формула

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

Что же это за формулы?

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

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

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

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

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector