Цифры буквами в Excel. Преобразование в ms excel чисел в формат время Как преобразовать значение в число

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

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

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

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

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

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

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

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

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

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

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



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



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

   ТЕКСТ(значение;формат)

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

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

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

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



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



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



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

   

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



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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Как видим, существует довольно много вариантов преобразования в 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 й способ).

Каждый пользователь, которому много приходится работать с документами, хотел бы упростить, автоматизировать свою работу хотя бы в какой-то степени . Специальные средства, реализованные разработчиками в редакторе таблиц Microsoft Excel, позволяют это сделать. В этой статье разберёмся как в Excel преобразовать число в текст. Давайте же начнём. Поехали!

Стоит отметить, что по умолчанию функция перевода числа в текст отсутствует в программе. Для того чтобы такая возможность появилась в Экселе, необходимо скачать и установить специальную надстройку «sumprop». После того как вы скачаете пакет, переместите файл «sumprop» в папку, путь к которой можно посмотреть следующим образом. Откройте меню «Файл» и нажмите «Параметры». В окне параметров Excel перейдите на вкладку «Надстройки». Внизу, в пункте «Управление» укажите «Надстройки Excel» и щёлкните по кнопке «Перейти». В открывшемся окне нажмите «Обзор». Далее, скопируйте путь к папке, в которую нужно будет переместить файл «sumprop». После этого запустите Эксель заново и ещё раз откройте окно надстроек (повторите вышеописанные действия). В разделе «Доступные надстройки» отметьте галочкой пункт «Сумма прописью». Нужная функция появится в программе.

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

Обратите внимание, что после того, как вы применили функцию к какой-либо ячейке, все числа, которые вы будете в неё вводить, сразу будут преобразованы в текст. Используйте «ЧислоПрописью» или «СуммаПрописью» в зависимости от того, какие задачи стоят перед вами сейчас.

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

Бывает что, когда пытаешься провести вычисления с данными, формулы не работают или Excel выдает ошибку. Причины сразу могут быть неясны. Особенно часто проблема встречается после выгрузки из учетных систем, таких как 1С или SAP. Как избежать проблемы, когда формулы не работают? Чаще всего необходимо преобразовать текст в число в Excel? Несколько полезных способов, как бороться с этим несчастьем читайте ниже.

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

С самого начала проверьте формат чисел. Выделите диапазон с данными (на нашем примере столбец B). Нажмите горячую клавишу (меню Формат ячеек если правой кнопкой мыши). На вкладке Число выберите Формат ячеек — Числовой.

Очень удобно пользоваться меню Число на главной панели. Здесь сразу доступны удобные форматы. Слева-направо: денежный, процентный, числовой + кнопки уменьшения/увеличения количества знаков после запятой. Рекомендую.

2. Исправьте ошибки — преобразуйте текст в число специальным инструментом

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

В нашем случае Число сохранено как текст — кликните Преобразовать в число. Вуаля!

Это же доступно по адресу Формулы — Раздел Зависимости формул -

3. Умножьте все числа на 1

Не помог предыдущий вариант? Значит, ошибка довольно редкая, но решается просто. Есть крутая хитрость. В столбце С все значения столбца B перемножьте на 1. Да, буквально так, для ячейки C2 запишите =B2*1 и протяните вниз. Так Excel принудительно преобразовывает имеющиеся значения в число.

4. Удалите лишний символ в ячейках при помощи Найти и Заменить

И пункт 3 не помог? Значит в ячейках закрался какой-то символ, например как в ячейке В7 на первой картинке после первого числ стоит символ » «, это может быть даже не обычный пробел (поэтому скопируйте именно этот символ). Такими вещами очень часто грешит 1С, причем любой конфигурации.

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

В поле Найти: вставляем скопированный символ. В поле Заменить на: ничего не ставим (т.е. заменяем на пусто). Жмем Заменить все.

Должно помочь!

5. Функция

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

6. Перевод времени в число

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

Кстати для того, чтобы в начале и конце ячеки с текстом (например при записи ФИО мы часто ошибаемся и ставим пробел в конце, который не видо). А так же, чтобы убрать двойные пробелы, есть классная функция . Она убирает все лишние пробелы!

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