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

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

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

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

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

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



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

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

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

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

Sub Conv() With ActiveSheet.UsedRange arr = .Value .NumberFormat = "General" .Value = arr End With End Sub

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

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

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

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

Sub Conv() With ActiveSheet.UsedRange .Replace "," ,"." arr = .Value .NumberFormat = "General" .Value = arr End With End Sub

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

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

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

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

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

Функция 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.

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

Вам понадобится

• Калькулятор, табличный процессор Excel

Инструкция

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

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

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

Преобразовать число в экспоненциальный вид тоже несложно. Это удобный формат для записи очень больших (и очень маленьких) величин, очень часто используется в физике и информатике. Его общий вид: a*10^b, где a - число , по модулю большее единицы, но меньшее десятки, b - степень десятки. Для записи числа в экспоненциальной форме нужно разбить число на два множителя - само число и единицу. Дальше, если число большое, то нужно делить его на 10 (а единицу умножать на 10). Так мы будем делать до тех пор, пока не получим в первом множителе числа, имеющего перед запятой только разряд единиц. Затем записываем число a, букву E и степень, в которую нужно возвести 10, чтобы получить второй множитель (единицу, несколько раз умноженную на 10).

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

Вам понадобится

• приложение Microsoft Excel

Инструкция

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

Второй способ не более сложен, чем предыдущий. Как правило, его применяются в тех случаях, когда необходимо преобразовать дату в таблице в соответствующий ей день недели.
В начале вы должны выбрать в открывшемся листе нужную ячейку, нажать на нее правой клавишей мыши и в появившемся меню найти пункт «Формат ячеек». Далее выберите вкладку «Число» и в списке «Числовые форматы» кликните на строку «Все форматы». Теперь в правой части окошка в поле «Тип» вбейте одно из двух выражений:
дддд (ДДДД) – если хотите, чтобы значение дня недели отображалось в ячейке полностью-
ддд (ДДД) – если хотите, чтобы день недели выдавался в сокращенном виде.
Чтобы сохранить внесенные изменения, следует нажать кнопку ОK, в противном случае – кнопку «Отмена».

Преобразование числовой даты в текст можно выполнить и с помощью функции ТЕКСТ, которая записывается в следующим образом:
=ТЕКСТ(Номер ячейки-"формат").
Так, чтобы переделать дату в день недели, вышеописанная функция примет вид:
=ТЕКСТ(A1-"ДДД").

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

Внимание, только СЕГОДНЯ!

Все интересное

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

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

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

Преобразование строки в дату – одна из самых частых операций, с которыми сталкивается программист при написании определенных программ или скриптов. Каждый язык по-своему реализует данную функцию и имеет собственный инструментарий для обработки…

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

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

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

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

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

Программа Microsoft Office Excel больше подходит для работы с электронными таблицами. Тем не менее, в ней можно оформить текст почти так же, как в текстовом редакторе, в том числе сделать список в одной ячейке. Инструкция 1Запустите приложение…

При работе с таблицами в программе Microsoft Office Excel пользователю может потребоваться пронумеровать строки или столбцы. Если для выполнения поставленной задачи использовать специальные команды, это поможет сэкономить силы и время. Инструкция …

Значения, отображаемые в ячейках электронных таблиц приложения Microsoft Office Excel, часто формируются в результате выполнения записанных в них формул. Результатом вычислений может быть и нулевое значение, показывать которое в ячейке нежелательно.…

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

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

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

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

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

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

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

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

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

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

Похожие статьи