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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Нередко я создавал свои собственные функции для подобных преобразований. Это полезно для развития и обучения.

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

Функция BinStr

Функция BinStr Преобразует целое число в строку в двоичном представлении. Синтаксис:

BinStr(Val: Longint; cnt: byte) : shortstring; function binStr(Val: int64; cnt: byte) : shortstring; function binStr(Val: qword; cnt: byte) : shortstring;

Функция binStr возвращает строку, которая содержит двоичное представление целого числа, переданного через параметр Val. Количество символов в строке будет не более, чем указано в параметре cnt. То есть в строке будут только младшие (правые) биты числа. Чтобы получить полное представление больших целых чисел, надо указывать cnt = 32. Иначе старшие разряды могут не попасть в результат.

program binstrfunc; var x, y: integer; begin x:= 100; y:= x * 245; (x, " - ", binStr(x, 4) ); //0100 WriteLn(x, " - ", binStr(x, 8) ); //01100100 WriteLn(y, " - ", binStr(y, 16) ); //0101111110110100 z:= (Int64); WriteLn(z, ":", #10#13, binStr(z, 64)); z:= (Int64); WriteLn(z, ":", #10#13, binStr(z, 64)); ; end.

Двоичное число в виде строки

Зачем вообще может понадобиться выводить двоичное число в виде строки?

Бывает это не так уж и часто. Но всё-таки иногда такая необходимость возникает. Например, если вам надо отследить на экране значения каждого бита в числе.

Я довольно часто упаковываю в число значения логических переменных, где каждый разряд соответствует, например, состоянию какого-то датчика. Это бывает необходимо, когда надо сэкономить место и время при передаче данных по последовательному каналу. Тогда, например, вместо передачи 32 логических переменных я передаю всего 1 переменную типа WORD, в которой каждый разряд соответствует одной логической переменной. А уже в программе я это число дешифрую и таким образом получаю состояния, например, 32-х датчиков.

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

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

Преобразование числа Х из системы счисления с основанием q в систему счисления с основанием р осуществляется по правилу замещения или по правилу деления-умножения на основание системы счисления.

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

Пример .111011,011 (2) = 1 2 4 +0 2 3 +1 2 2 +0 2 1 +l 2 0 +0 2 -1 +l 2 -2 +l 2 -3 = 59, 375.

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

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

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

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

Пример .75,35 (10) =1001011,01011… (2) .

Преобразовывать дробные и целые числа из двоичной системы счисления в шестнадцатеричную и обратно просто. Достаточно разделить двоичное число на группы по 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 й способ).

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

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

Для начала рассмотрим различные варианты решения задачи конвертации чисел в текстовый вид.

Способ 1: форматирование через контекстное меню

Чаще всего пользователи выполняют форматирование числовых выражений в текстовые через контекстное меню.

Способ 2: инструменты на ленте

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

Данные преобразовываются в текстовый вариант.

Способ 3: использование функции

Ещё одним вариантом преобразования числовых данных в тестовые в Экселе является применение специальной функции, которая так и называется – ТЕКСТ . Данный способ подойдёт, в первую очередь, если вы хотите перенести числа как текст в отдельный столбец. Кроме того, он позволит сэкономить время на преобразовании, если объем данных слишком большой. Ведь, согласитесь, что перещелкивать каждую ячейку в диапазоне, насчитывающем сотни или тысячи строк – это не самый лучший выход.

Устанавливаем курсор в первый элемент диапазона, в котором будет выводиться результат преобразования. Щелкаем по значку «Вставить функцию» , который размещен около строки формул.

Запускается окно Мастера функций . В категории «Текстовые» выделяем пункт «ТЕКСТ» . После этого кликаем по кнопке «OK» .

Открывается окно аргументов оператора ТЕКСТ . Данная функция имеет следующий синтаксис:
ТЕКСТ(значение;формат)

Открывшееся окно имеет два поля, которые соответствуют данным аргументам: «Значение» и «Формат» .

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

В поле «Формат» нужно указать вариант отображения результата. Например, если мы введем «0» , то текстовый вариант на выходе будет отображаться без десятичных знаков, даже если в исходнике они были. Если мы внесем «0,0» , то результат будет отображаться с одним десятичным знаком, если «0,00» , то с двумя, и т.д.

После того, как все требуемые параметры введены, щелкаем по кнопке «OK» .

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

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

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

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

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

Конвертация текста в число

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

Способ 1: преобразование с помощью значка об ошибке

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

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

Все данные массива будут преобразованы в указанный вид.

Способ 2: конвертация при помощи окна форматирования

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

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

Способ 3: конвертация посредством инструментов на ленте

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

Значения в диапазоне будут преобразованы из текстовых в числовые.

Способ 4: применение формулы

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

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

Способ 5: применение специальной вставки

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

Способ 6: использование инструмента «Текст столбцами»

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

Способ 7: применение макросов

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

Как видим, существует довольно много вариантов преобразования в Excel цифр, которые записаны в числовом варианте, в текстовый формат и в обратном направлении. Выбор определенного способа зависит от многих факторов. Прежде всего, это поставленная задача. Ведь, например, быстро преобразовать текстовое выражение с иностранными разделителями в числовое можно только использовав инструмент «Текст столбцами» . Второй фактор, который влияет на выбор варианта – это объемы и частота выполняемых преобразований. Например, если вы часто используете подобные преобразования, имеет смысл произвести запись макроса. И третий фактор – индивидуальное удобство пользователя.