Как в экселе преобразовать. Excel. Преобразование как бы чисел. Отделить дату от времени и наоборот в Excel

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

И поскольку реестр договоров велся в Excel, то возник соблазн воспользоваться какой-нибудь формулой, которая бы перевела мне число, набранное цифрами, в число написанное прописью, причем по-русски. Перебрав все стандартные формулы в Excel, и найдя в категории «текстовые» функцию БАТТЕКСТ с описанием «преобразует число в текст», признаюсь, обрадовался. Разочарование наступило очень быстро, когда число, преобразованное в текст прописью оказалось на непонятном мне языке.

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

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

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

Надстройка позволяет:

2. преобразовать любое число от 0 до 999 999 999 999 в прописной текст;

3. преобразовать все числа в выбранном Вами диапазоне;

4. использовать при написании чисел прописью одну из валют на выбор: рубли, доллары, евро, тенге, гривны ;

5. выбрать строчную либо заглавную букву в каждом первом слове суммы прописью;

6. выбрать любую позицию для вставки суммы прописью, относительно суммы цифрами;

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

Как преобразовать текст, написанный прописью в число цифрами?

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

Надстройка позволяет:

1. Одним кликом мыши вызвать диалоговое окно макроса прямо с панели инструментов Excel;

2. преобразовать любую сумму, написанную прописью в число от 0 до 999 999 999, 99;

3. найти и преобразовать все суммы прописью в выбранном Вами диапазоне;

4. находить и преобразовывать суммы, написанные прописью с использованием любых валют: рублей, гривен, долларов, евро и др.;

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

6. использовать пользовательскую функцию, аналогично стандартной формуле, которая позволит трансформировать сумму прописью в число.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Последний способ подходит в том случае, если значения находятся в одном столбце.

Макрос «Текст – число»

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

Есть набор значений, сохраненных в текстовом формате:

Чтобы вставить макрос, на вкладке «Разработчик» находим редактор Visual Basic. Открывается окно редактора. Для добавления кода нажимаем F7. Вставляем следующий код:

Теперь возвращаемся на страницу с цифрами. Выделяем столбец с данными. Нажимаем кнопку «Макросы». В открывшемся окне – список доступных для данной книги макросов. Выбираем нужный. Жмем «Выполнить».

Цифры переместились вправо.

Следовательно, значения в ячейках «стали» числами.

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

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

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

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

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

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

Возьмём А 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. Таблица соответствия шестнадцатеричных цифр и двоичного кода

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

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

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

Литература:

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