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

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

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

Задний план

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

Разъяснение

В этом примере значения в столбце A «сохраняются как текст». Это значит, что если вы попытаетесь SUM столбец A, вы получите нулевой итог.

Функция VALUE попробует «преобразовать» число, сохраненное в виде текста, к настоящему числу. В обычных вариантах это просто сработает, и вы получите числовой итог. Если это не сработает, вы получите ошибку #VALUE.

Заместо этого добавьте ноль

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

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

Удаление остальных персонажей

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

Формулы в C8 и C9 демонстрируют, как применять функции LEFT и RIGHT для удаления нечисловых знаков из текстового значения перед его преобразованием в число. Вы также сможете применять функцию MID в наиболее сложных ситуациях. Если для вас необходимо удалить излишние пробелы либо остальные непечатаемые знаки, см. Функции ОБРЕЗАТЬ и ОЧИСТИТЬ.

В конце концов, функция ЗАМЕНА дозволит для вас удалять знаки с функцией типа «поиск и подмена».

Преобразование времени в десятичное число в Excel (часы, минутки, секунды)

По дефлоту дата и время сохраняются в Excel как числа / десятичные дроби. Но вы нередко видите это в различных форматах из-за метода форматирования ячеек.

К примеру, в приведенном ниже примере значение в столбцах A и B идиентично, но они показываются по-разному из-за формата.

И одна из вещей, которые почти всем необходимо в Excel, — это преобразовать время в десятичное (к примеру, часы, минутки либо секунды).

К примеру, заместо даты и времени 01-01-2020 6:30 AM вы сможете получить:

  • Количество часов как 6
  • Количество минут как 30

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

Как преобразовать время в десятичные числа в Excel

В этом руководстве я покажу для вас несколько примеров преобразования времени в десятичное число при помощи формул (т. е. Преобразования времени в часы, минутки и секунды).

Есть несколько методов преобразовать время в десятичное в Excel :

  • Внедрение арифметических операций — самый обычный метод преобразовать время в десятичные числа. К примеру:
    • Чтоб преобразовать время в часы, помножьте значение времени на 24.
    • Чтоб преобразовать время в минутки, помножьте значение времени на 24 * 60.
    • Чтоб преобразовать время в секунды, помножьте значение времени на 24 * 60 * 60.

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

    Преобразование времени в часы в Excel

    В этом разделе я расскажу о 3-х разных методах преобразования времени в часы в Excel.

    Внедрение обычного умножения для получения значения часа

    Представим, у вас есть время в ячейке как 18:00 (в ячейке A2), и вы желаете преобразовать его в часы.

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

    Это работает, поэтому что Excel хранит дату и время в виде чисел. Полный денек (24 часа) сохраняется как 1 в Excel. Это значит, что любой час сохраняется как 1/24.

    Таковым образом, хотя вы видите значение 18:00 в бэкэнде, оно сохраняется как значение 0,75 (что показывает на то, что 18 часов из 24 уже прошли). Итак, если вы умножите его на 24, вы получите количество прошедших часов.

    Если у вас есть такое значение, как 18:32, внедрение приведенной выше формулы даст для вас итог как 18,53 (где часть минут / секунд отображается в десятичном формате, а полные / полные часы — в виде целых чисел). В этом примере числовое значение 32 минут в Excel будет 0,53 часа.

    Примечание. Если вы получили значение часа с десятичными знаками (к примеру, 18,53), удостоверьтесь, что вы отформатировали его так, чтоб также показывались десятичные знаки. Если вы этого не сделали, вы сможете узреть итог как 19, так как Excel указывает для вас округлое целочисленное значение. Но это не изменит значение в ячейке, которое как и раньше будет 18,53.

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

    INT дает для вас лишь целую часть, и для вас не надо волноваться о излишних минутках.

    Внедрение функций Excel (HOUR, MINUTE, и SECOND)

    Если для вас не нравится ручной способ умножения, очередной вправду обычный метод преобразовать время в часы — применять интегрированные функции, связанные с течением времени (HOUR, MINUTE и SECOND).

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

    Ниже приведена формула для преобразования времени в часы.

    Функция HOUR просто возвращает количество часов, прошедших за данное время, как и функции MINUTE и SECOND.

    Но так как нам необходимо окончательное значение в часах, нам необходимо поделить значение минут на 60 (чтоб преобразовать его в часы), а 2-ое значение на 60 * 60 (опять, чтоб преобразовать его в часы).

    Внедрение функции преобразования (CONVERT)

    Очередной весьма обычный (может быть, самый обычный из 3-х способов) — применять функцию CONVERT.

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

    Ниже приведена формула, которая конвертирует время в часы:

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

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

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

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

    Давайте также стремительно взглянем на эти примеры.

    Преобразование времени в минутки в Excel

    В этом разделе я расскажу о 3-х разных методах преобразования времени в минутки в Excel.

    Внедрение обычного умножения для получения минутного значения

    В сутках 1440 минут (24 * 60).

    Итак, чтоб преобразовать время в минутки, вы сможете просто помножить значение времени на 1440.

    Представим, у вас есть время в ячейке как 18:00 (в ячейке A2), и вы желаете преобразовать его в минутки.

    Ниже приведена формула для этого:

    Это даст для вас полное количество минут, прошедших в этот денек за обозначенное время.

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

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

    Внедрение функций Excel (HOUR, MINUTE, и SECOND)

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

    Ниже приведена формула, которая конвертирует время в минутки при помощи этих функций:

    Так как цель тут — получить все части в минутках (часы, минутки и секунды), мы умножаем 60 на часы, чтоб получить в их значение минут, и делим секунды на 60, чтоб получить значение минут.

    Внедрение функции преобразования

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

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

    2-ой аргумент докладывает функции, что значение в ячейке A2 находится в формате денька, а 3-ий аргумент («mn») показывает ей преобразовать его в минутки.

    Преобразование времени в секунды в Excel

    В этом разделе я расскажу о 3-х разных методах преобразования времени в секунды в Excel.

    Внедрение обычного умножения для получения значения секунд

    В сутках 86400 секунд (24 * 60 * 60).

    Итак, чтоб преобразовать время в секунды, вы сможете просто помножить значение времени на 86400.

    Представим, у вас есть время в ячейке как 18:32:30 (в ячейке A2), и вы желаете преобразовать его в секунды.

    Ниже приведена формула для этого:

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

    Внедрение функций Excel (HOUR, MINUTE и SECOND)

    Ниже приведена формула, которая конвертирует время в минутки при помощи этих функций:

    Так как цель тут — получить все части (часы, минутки и секунды) в секундах, мы умножаем 60 * 60 на часы, чтоб получить в их значение секунд, и умножаем минутки на 60, чтоб получить значение секунд.

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

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

    Но что, если у вас есть дата, также время?

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

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

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

    Поделить дату и время при помощи формулы INT

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

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

    Приведенная выше формула удаляет целую часть и дает для вас десятичную часть даты.

    Сейчас вы сможете применять хоть какой из 3-х способов, обрисованных выше (используя арифметическое умножение, используя формулу HOUR/MINUTE/SECOND либо формулу CONVERT), чтоб преобразовать время в десятичное.

    Разделите дату и время при помощи формулы MOD

    Иной метод создать это — применять функцию MOD.

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

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

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

    Интересно почитать:  Как в excel округлить результат формулы
Ссылка на основную публикацию
Adblock
detector