Преобразование чисел. Excel. Преобразование как бы чисел Преобразование числового значения в текстовый excel

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

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

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

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

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

Но что будет, если данные перевести в формат Числовой? Появиться число 42544,00.

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

Так же быть и с вычитанием данных друг из друга.

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

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

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

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

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

A:A-ЦЕЛОЕ(A:A)

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

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

Это тоже просто, если знаете хитрость. Очень и очень часто, нам удобнее использоваться формат 3,5 часа, чем 3:30. Т.е. перевод числа во временном формате в число, но с нужным значением. Для этого есть прекрасные функции Excel =ЧАС(), =МИНУТЫ().

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

ЧАС(A1)+МИНУТЫ(A1)/60

Как работает: мы находим количество полных часов функцией ЧАС, затем находим количество полных минут и делим их на 60, чтобы получить минут в часе. Готово!

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

ДЕНЬ(D2)*24+ЧАС(D2)+МИНУТЫ(D2)/24

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

P.S. Как всегда прикладываю с примерами выполнения нашей задачки «как перевести время в число в Excel».

Когда я работал в издательстве (сначала верстальщиком, а затем начальником производственного отдела) моим основным рабочим инструментом была программа вёрстки PageMaker от фирмы Adobe (ныне на смену ему пришел InDesign). Когда я освоил все нюансы работы в PageMaker, я стал задумываться о том, что еще можно сделать для сокращения рутинных операций, увеличения доли творчества в работе верстальщика. И я открыл для себя язык Script, поддерживаемый PageMaker. Было это в далеком 1995-м году. Описания языка скриптов нигде не было, но я был настойчив, и нашел вариант – заказал англоязычную книгу у дистрибутора Adobe в России. Через несколько месяцев книга была у меня! Счастью не было предела! Сначала я самостоятельно освоил самые простые скрипты, которые позволяли ускорить работу и внедрить стандарты обработки текста разными верстальщиками. А затем придумал процедуру автоматической верстки текстовых объявлений.

С тех пор прошло много времени и уже давно моим основным инструментом в работе является Excel. Привычка же повышать производительность собственного труда и моих коллег / подчиненных осталась. Так что уже много лет я совершенствуюсь в применении Excel`я. Тем, кто также как и я хочет больше узнать об этой поистине безграничной по своим возможностям программе, могу рекомендовать книгу Холи Д., Холи Р. Excel 2007. Трюки. СПб: Издательство Питер. 2008 г., 368 с.

Сегодня об одном из трюков, изложенных в книге, а также о моих методах борьбы с как бы числами … 🙂

Нередко (особенно при импорте из внешних источников) данные выглядят как числа, но числами не являются. Их нельзя суммировать, и вообще с точки зрения Excel это текст. В Excel визуально отличить текст от чисел довольно просто. Сделайте колонку достаточно широкой. Числа, по умолчанию, форматируются по правому краю ячейки, текст – по левому (для лучшего понимания откройте , лист «Пример1»):

Видно, что числа в колонке «прилипли» к левому краю, то есть Excel их не воспринимает, как числа.

Выделите пустую ячейку, скопируйте её в буфер, выделите диапазон, который вы хотите превратить из как бы чисел в настоящие числа, выберите «Специальная вставка» (на ленте или с помощью правой кнопки мыши), в открывшемся окне выберите «значения» и «сложить», нажмите Ok:

Как бы числа преобразовались в числа:

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

Иногда это не работает – см. в файле Excel лист «Пример2». Обратите внимание на рисунке ниже: в строке формул между числом 88 и курсором есть пробелы.

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

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

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

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

Возьмём А 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.

Литература:

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

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

И поскольку реестр договоров велся в 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. использовать пользовательскую функцию, аналогично стандартной формуле, которая позволит трансформировать сумму прописью в число.

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

Преобразование числа Х из системы счисления с основанием 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) .