Преобразование текста в число в ячейке Excel. Преобразование числа в текст и обратно в Microsoft Excel Как перевести числовой формат ячейки в текстовый

Преобразовывать дробные и целые числа из двоичной системы счисления в шестнадцатеричную и обратно просто. Достаточно разделить двоичное число на группы по 4 бита (битом будем называть двоичный разряд), причём 4 бита начинают формировать непосредственно от точки, которая разделяет целую и дробную части числа. Следовательно, для целой части группы формируются от точки справа налево, а для дробной части слева направо. Каждую группу по 4 бита, которую ещё называюттетрадой , можно преобразовать в один шестнадцатеричный разряд со значением от 0 доF(см. Табл.1.3).

Таблица 1.3. Соответствие двоичных тетрад и шестнадцатеричных цифр.

Двоичная тетрада	Шестнадцатеричная цифра	Десятичное значение

Если для тетрады не хватает битов, то слева дописываются незначащие нули для целой части, и справа – для дробной части (см. рис. 1.3).

Рис.1.3. Преобразование дробного двоичного числа в шестнадцатеричное.

Преобразование дробные и целые числа из шестнадцатеричной системы в двоичную проделать тоже несложно – каждая цифра шестнадцатеричного числа заменяется соответствующей двоичной четырёхбитной тетрадой из таблицы 1.3 (см. рис. 1.4).

Рис.1.4. Преобразование дробного шестнадцатеричного числа A18.2E 16 в двоичное.

Целые числа

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

1 й способ. Последовательное вычитание из числа степеней двойки. Самая большая степень двойки, меньшая, чем число, вычитается из этого числа. Та же операция проделывается с полученной разностью до тех пор, пока разность не станет равной нулю (Рис. 1.5). Когда число разложено по степеням двойки, то двоичное значение получается так: единички ставятся в тех позициях, которые соответствуют полученным степеням двойки, а нули – во всех остальных позициях.

Рис.1.5. Преобразование десятичного числа 197 в двоичное (1 й способ).

2 й способ. Последовательное деление числа на два. Частное записывается непосредственно под исходным числом, а остаток записывается рядом с частным. Процесс повторяется до тех пор, пока не останется 0. Двоичное число можно прочитать из колонки остатковснизу вверх (Рис. 1.6).

Рис. 1.6. Преобразование десятичного числа 197 в двоичное (2 й способ).

Преобразование целых десятичных чисел в шестнадцатеричные можно совершать последовательным делением числа на 16. Если результат деления больше 16, то деление производится еще раз. Частное, полученное последним, будет старшим разрядом шестнадцатеричного числа. Далее записываются остатки от деления в обратном порядке их получения (Рис. 1.7). Числа, больше 9, но меньше 16, получаемые при делении, заменяются соответствующими шестнадцатеричными цифрами (табл. 1.1 или 1.3).

Рис. 1.7. Преобразование десятичного числа 1970 в шестнадцатеричное.

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

1 й способ. Суммирование степеней двойки, у которых биты двоичного числа равны 1:

Рис. 1.8. Преобразование двоичного числа в десятичное (1 й способ).

2 й способ. Двоичное число записывается вертикально по одному биту в строке, начиная с крайнего правого. Крайний левый бит находится внизу. Десятичное число собирается снизу вверх. Нижняя строка это 1. Число из нижнего ряда переходит вверх, умножаясь на 2, и суммируется с битом текущего ряда. Результат получается в самом верхнем ряду:

Рис. 1.9. Преобразование двоичного числа в десятичное (2 й способ).

Шестнадцатеричная система счисления – это система счисления по основанию 16. Это означает, что в системе используются 16 символов: цифры от 0 до 9 и буквы A, B, C, D, E, F. Преобразовать десятичное число в шестнадцатеричное труднее, чем шестнадцатеричное в десятичное. Сначала уясните процесс преобразования, а потом приступайте к нему – это позволит избежать ошибок.

Преобразование небольших чисел

Десятичное	0	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15
Шестнадцатеричное	0	1	2	3	4	5	6	7	8	9	A	B	C	D	E	F

Шаги

Интуитивный метод

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

   • Если вы вообще ничего не знаете о шестнадцатеричной системе, начните с изучения основных понятий .

2. Возведите 16 в степень от 1 до 5 и запишите результаты. Разряд каждой цифры шестнадцатеричного числа является результатом возведения в степень числа 16, так же как разряд каждой цифры десятичного числа является результатом возведения в степень числа 10. Следующий список результатов возведения 16 в различные степени пригодится в процессе преобразования:

   • 16 5 = 1048576
   • 16 4 = 65536
   • 16 3 = 4096
   • 16 2 = 256
   • 16 1 = 16
   • Если конвертируемое десятичное число больше 1048576, возведите 16 в большую степень, а результат добавьте в список.

3. В списке найдите наибольшее число, которое меньше данного десятичного числа. Запишите данное десятичное число, которое нужно преобразовать в шестнадцатеричное. Посмотрите на список, приведенный выше, и найдите наибольший результат (возведения 16 в степень), который меньше данного десятичного числа.

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

4. Разделите десятичное число на выбранный результат возведения 16 в степень. Работайте с целочисленным результатом деления – не обращайте внимания на цифры после десятичной запятой.

   • В нашем примере: 495 ÷ 256 = 1,93..., поэтому работайте с числом 1 (это целое частное от деления).
   • Полученный результат – это первая цифра шестнадцатеричного числа. В этом случае вы разделили данное десятичное число на 256, поэтому 1 находится в разряде 256-и.

5. Найдите первый остаток. То есть остаток от деления данного десятичного числа на выбранное число (делитель). Остаток вычисляется так же, как при делении в столбик.

   • Умножьте полученное частное на делитель. В нашем примере: 1 х 256 = 256 (то есть 1 в шестнадцатеричном числе представляет 256 по основанию 10).
   • Результат умножения вычтите из данного десятичного числа: 495 - 256 = 239 .

6. Разделите остаток на следующий (по списку) результат возведения 16 в степень. Посмотрите на список с результатами возведения 16 в разные степени. Найдите результат, который находится под результатом, который вы выбрали для предыдущего деления. Разделите остаток на выбранное число, чтобы найти следующую цифру шестнадцатеричного числа (если остаток меньше выбранного числа, следующая цифра равна 0).

   • 239 ÷ 16 = 14 . Не обращайте внимания на цифры после десятичной запятой.
   • Это вторая цифра шестнадцатеричного числа, которая находится в разряде 16-и. Любое число от 0 до 15 может быть представлено одной шестнадцатеричной цифрой. Полученные цифры будут преобразованы и расставлены в конце этого метода.

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

   • 14 x 16 = 224.
   • 239 - 224 = 15, то есть остаток равен 15 .

8. Если остаток равен числу от 0 до 15, он может быть выражен одной шестнадцатеричной цифрой. Эта цифра будет последней цифрой.

   • Последней цифрой шестнадцатеричного числа является число 15, которое находится в разряде единиц.

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

   • Цифры от 0 до 9 не меняются.
   • 10 = A; 11 = В; 12 = C; 13 = D; 14 = E; 15 = F
   • В нашем примере вы получили цифры (1)(14)(15). То есть шестнадцатеричное число запишется так: 1EF .

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

    • 1EF → (1)(14)(15)
    • Работайте с цифрами справа налево. 15 находится в разряде единиц: 16 0 = 1, поэтому 15 х 1 = 15.
    • Следующая цифра находится в разряде 16-и: 16 1 = 16, поэтому 14 x 16 = 224.
    • Следующая цифра находится в разряде 256-и: 16 2 = 256, поэтому 1 x 256 = 256.
    • Сложите найденные результаты: 256 + 224 + 15 = 495, то есть получилось исходное десятичное число.

    Быстрый метод (использование остатков)

    1. Разделите десятичное число на 16. Работайте с целочисленным результатом деления, то есть не обращайте внимания на цифры после десятичной запятой.

       • Например, преобразуйте десятичное число 317547 в шестнадцатеричное. Разделите: 317547 ÷ 16 = 19846 (это первое частное). Не обращайте внимания на цифры после десятичной запятой.

    2. Остаток запишите в шестнадцатеричной системе счисления. Остаток от деления данного числа на 16 будет находиться в разряде ниже 16-и. Таким образом, остаток должен находиться в разряде единиц, то есть в последнем разряде шестнадцатеричного числа.

       • Чтобы найти остаток, умножьте результат деления на делитель (16), а затем результат умножения вычтите из делимого (десятичное число). В нашем примере: 317547 - (19846 х 16) = 11.
       • Преобразуйте число по основанию 16. Для этого воспользуйтесь таблицей, которая приведена в начале этой статьи. Таким образом, 11 = B .

    3. Разделите первое частное. Вы преобразовали остаток в цифру шестнадцатеричной системы. Теперь нужно преобразовать первое частное. Для этого разделите его на 16. Получится вторая (справа) цифра шестнадцатеричного числа. Процесс аналогичен вышеописанному: первое частное делится на 256 (16 х 16 = 256), поэтому остаток будет находиться в разряде ниже 256-и. Вы уже нашли цифру для разряда единиц, поэтому остаток запишется в разряд 16-и.

       • В нашем примере: 19846/16 = 1240.
       • Остаток = 19846 - (1240 х 16) = 6 . Эта вторая (справа) цифра шестнадцатеричного числа.

    4. Повторяйте описанный процесс до тех пор, пока остаток не будет меньше 16. Не забывайте конвертировать остатки, которые равны числам от 10 до 15, в цифры шестнадцатеричной системы счисления. Записывайте каждый остаток. Последний остаток (меньше 16) будет первой цифрой шестнадцатеричного числа. В нашем примере:

       • Предыдущий остаток разделите на 16: 1240/16 = 77 с остатком 8 .
       • 77/16 = 4 с остатком 13; 13 = D .
       • 4 <16, поэтому 4 – это первая цифра шестнадцатеричного числа.

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

Преобразование целых чисел

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

Рассмотрим для примера перевод числа из десятичной системы счисления в .

Возьмём А 10 = 124 и поделим его на основание двоичной системы, то есть число 2. Деление будем производить уголком:

В результате первого деления получим разряд единиц (самый младший разряд). В результате второго деления получим разряд двоек. Деление продолжаем, пока результат деления больше двух. В конце операции преобразования мы получили двоичное число 1111100 2 .

Теперь то же самое число переведём в восьмеричную систему счисления. Для этого число 124 10 разделим на число 8:

Как мы видим, остаток от первого деления равен 4. То есть младший разряд восьмеричного числа содержит цифру 4. Остаток от второго деления равен 7. то есть второй разряд восьмеричного числа – это цифра 7. Старший разряд получился равным 1. То есть в результате многократного деления мы получили восьмеричное число 174 8 .

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

1×8 2 +7×8 1 +4×8 0 =64 10 +56 10 +4 10 =124

;А можно ли осуществить перевод из восьмеричной системы счисления в двоичную делением? Можно! Но деление нужно произвести по правилам восьмеричной арифметики. Правила работы в восьмеричной системе счисления мы рассмотрим в следующей главе. Тем не менее, для полноты материала, рассмотрим пример перевода в двоичную форму полученного ранее восьмеричного числа 174 8 . Разделим его на основание новой системы счисления 2.

Как мы убедились выполнять деление в восьмеричной системе очень неудобно, ведь подсознательно мы делим в десятичной системе счисления. Давайте обратим внимание на то, что число 8 является степенью числа 2. То есть можно считать восьмеричную систему счисления просто более короткой записью двоичного числа. Это означает, что для представления восьмеричной цифры можно использовать три двоичных бита (8=2 3). Давайте составим таблицу соответствия. Она приведена в таблице 1.

Таблица 1. Таблица соответствия восьмеричных цифр и двоичного кода

Используя эту таблицу можно просто заменить каждую восьмеричную цифру тремя двоичными битами. Три двоичных бита обычно называют триадой или трибитом. Теперь давайте переведём восьмеричное число 1748 в двоичную форму при помощи таблицы 7:

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

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

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

В качестве примера использования таблицы 2 переведем шестнадцатеричное число 7С 16 в двоичную форму представления:

Таблица 2. Таблица соответствия шестнадцатеричных цифр и двоичного кода

Двоичный код	Восьмеричная цифра	Десятичный эквивалент
0000	0	0
0001	1	1
0010	2	2
0011	3	3
0100	4	4
0101	5	5
0110	6	6
0111	7	7
1000	8	8
1001	9	9
1010	a	a
1011	b	b
1100	c	c
1101	d	d
1110	e	e
1111	f	f

Пример преобразования двоичного числа в восьмеричную и шестнадцатеричную форму приведён на рисунке 1.

Рисунок 1. Пример преобразования двоичного числа в восьмеричную и шестнадцатеричную форму.

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

Литература:

Вместе со статьей "Преобразование чисел из одной системы счисления в другую" читают:

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

1. Выделите столбец

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

2. Нажмите эту кнопку

Кнопка "столбцы" обычно применяется для разделения столбцов, но ее также можно использовать для преобразования столбца текста в числа. На вкладке Данные нажмите кнопку Текст по столбцам .

3. Нажмите кнопку "Готово"

Остальные шаги мастера нужны для разделения текста на столбцы. Так как нам нужно только преобразовать текст, нажмите кнопку Готово , и Excel преобразует ячейки.

4. Задайте формат

Нажмите клавиши CTRL+1 (или +1 на Mac). Выберите нужный формат.

Примечание: Если вы по-прежнему видите формулы, которые не выводят числовые результаты, возможно, включен параметр Показать формулы . Откройте вкладку Формулы и отключите параметр Показать формулы .

Другие способы преобразования

Использование формулы

С помощью функции ЗНАЧЕН можно возвращать числовое значение текста.

1. Вставьте новый столбец

Вставьте столбец рядом с ячейками, содержащими текст. В этом примере столбец E содержит числа, которые хранятся в виде текста. Столбец F является новым столбцом.

2. Примените функцию ЗНАЧЕН

В одной из ячеек нового столбца введите =ЗНАЧЕН() и укажите в скобках ссылку на ячейку, содержащую числа, которые хранятся в виде текста. В данном примере это ячейка E23 .

3. Наведите указатель мыши

Теперь нужно заполнить формулу вниз. Вот как это сделать: Наведите указатель на правый нижний угол ячейки, чтобы он принял вид знака плюс (+).

4. Щелкните и перетащите вниз

Щелкните и перетащите вниз, чтобы добавить формулу в другие ячейки. После этого вы можете использовать новый столбец или скопировать и вставить новые значения в исходный столбец. Вот как это сделать: Выделите ячейки с новой формулой. Нажмите клавиши CTRL+C. Щелкните первую ячейку в исходном столбце. На вкладке Главная щелкните стрелку рядом с кнопкой Вставить и выберите пункт Специальная вставка > Значения .

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

Число в текстовом формате: причины и проблемы

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

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

Еще одна проблема при хранении цифр как строк возникает при попытке их сортировки. Возьмем для примера ряд чисел: 10, 211, 15, 1021, 26, 100, 128. Их сортировка не представляет трудности, и программа легко с ней справляется. Но, если эти числа записаны в строковом формате, сортировка будет кардинально отличаться от привычной и необходимой. Эта разница показана в таблице:

Что же делать в этом случае и как преобразовать текст в число в Excel?

Исправление ошибки

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

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

Рядом с выделенным диапазоном полей появится значок - кнопка с восклицательным знаком, указывающий на наличие ошибки. При нажатии на кнопку появляется меню, первым пунктом которого показано, какая именно проблема в выделенном участке листа («Число сохранено как текст»). Нажмите пункт «Преобразовать в число». Зеленые индикаторы ошибки сразу исчезнут, и над числами можно будет проводить необходимые действия.

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

Еще один способ, помогающий преобразовать текст в число в Excel, - использование спецвставки. В одну из свободных ячеек внесите число 1, предварительно установив формат как общий: на вкладке «Главная» программы, панель инструментов «Число» показывает формат выделенного поля.

Скопируйте единицу. Для этого сделайте на ней правый клик и выберите команду «Копировать» из меню. Второй способ - кнопка на панели инструментов «Буфер обмена». И третий - горячие клавиши Ctrl+C.

Выделите диапазон чисел в текстовом формате, который собираетесь приводить к числовому. На той же панели «Буфер обмена» нажмите «Вставить» / «Специальная вставка». Откроется окно для ввода параметров. В нем отметьте точкой элемент «умножить» блока «Операция» и нажмите «ОК» - текст Excel преобразуется в число.

Ту же операцию можно произвести с помощью контекстного меню. Выделив нужный диапазон, кликните правой кнопкой мыши, выберите «Специальная вставка» / «Специальная вставка». Откроется то же окно.

Использование формул и макросов

Еще один способ преобразовать текст в число в Excel - формула. Вставьте рядом с проблемной ячейкой или графой пустой столбец. Введите в верхнее поле нового столбца формулу. Для этого напишите «=». Программа воспринимает этот знак как намерение добавления вычисления и предлагает выбрать функцию. Нажмите значок функции fx под панелью инструментов.

В открывшемся окне в строке поиска введите текст «значен» и найдите функцию «ЗНАЧЕН». В параметрах функции укажите нужную ячейку и нажмите «ОК». В соседней с искомой ячейкой вы получите число вместо текста. Чтобы скопировать формулу на все поля столбца, кликните мышкой в правом нижнем углу ячейки, содержащей ее, получив курсор в виде изображения крестика, зажмите его и проведите вниз по всей длине. Формула появится по всей графе.

Тем, кто знаком с программированием, поможет преобразовать текст в число Excel VBA. Этот встроенный язык предлагает команды трансформации типов данных. Для целых чисел подойдет операция CInt, для дробных - CDbl. Ниже показан пример использования VBA для нашего случая:

Sub TestModule()

Cells(1, 12) = CInt(Cells(1, 11))

Обратная задача

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

Еще один способ - использование формул. Он значительно более громоздкий, так как подразумевает создание пользовательских справочников - именованных диапазонов в диспетчере имен. Затем эти имена используются в формуле на основе функции «ПОДСТАВИТЬ».

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