Числа, хранящиеся как текст, могут приводить к непредвиденным результатам. Выделите ячейки и нажмите кнопку , чтобы выбрать вариант преобразования. Если эта кнопка недоступна, выполните указанные ниже действия.
Выделите столбец с такими ячейками. Если вы не хотите преобразовывать весь столбец, можно выбрать одну или несколько ячеек. Ячейки должны находиться в одном и том же столбце, иначе этот процесс не будет работать. (Если такие ячейки есть в нескольких столбцах, см. раздел "Другие способы преобразования" ниже.)
Кнопка "столбцы" обычно применяется для разделения столбцов, но ее также можно использовать для преобразования столбца текста в числа. На вкладке Данные нажмите кнопку Текст по столбцам .
Остальные шаги мастера нужны для разделения текста на столбцы. Так как нам нужно только преобразовать текст, нажмите кнопку Готово , и Excel преобразует ячейки.
Нажмите клавиши CTRL+1 (или +1 на Mac). Выберите нужный формат.
Примечание: Если вы по-прежнему видите формулы, которые не выводят числовые результаты, возможно, включен параметр Показать формулы . Откройте вкладку Формулы и отключите параметр Показать формулы .
Использование формулы
С помощью функции ЗНАЧЕН можно возвращать числовое значение текста.
Вставьте столбец рядом с ячейками, содержащими текст. В этом примере столбец E содержит числа, которые хранятся в виде текста. Столбец F является новым столбцом.
В одной из ячеек нового столбца введите =ЗНАЧЕН() и укажите в скобках ссылку на ячейку, содержащую числа, которые хранятся в виде текста. В данном примере это ячейка E23 .
Теперь нужно заполнить формулу вниз. Вот как это сделать: Наведите указатель на правый нижний угол ячейки, чтобы он принял вид знака плюс (+).
Щелкните и перетащите вниз, чтобы добавить формулу в другие ячейки. После этого вы можете использовать новый столбец или скопировать и вставить новые значения в исходный столбец. Вот как это сделать: Выделите ячейки с новой формулой. Нажмите клавиши CTRL+C. Щелкните первую ячейку в исходном столбце. На вкладке Главная щелкните стрелку рядом с кнопкой Вставить и выберите пункт Специальная вставка > Значения .
Одной из частых задач, с которыми сталкиваются пользователи программы Эксель, является преобразования числовых выражений в текстовый формат и обратно. Этот вопрос часто заставляет потратить на решение много времени, если юзер не знает четкого алгоритма действий. Давайте разберемся, как можно решить обе задачи различными способами.
Все ячейки в Экселе имеют определенный формат, который задает программе, как ей рассматривать то или иное выражение. Например, даже если в них будут записаны цифры, но формат выставлен текстовый, то приложение будет рассматривать их, как простой текст, и не сможет проводить с такими данными математические вычисления. Для того, чтобы Excel воспринимал цифры именно как число, они должны быть вписаны в элемент листа с общим или числовым форматом.
Для начала рассмотрим различные варианты решения задачи конвертации чисел в текстовый вид.
Чаще всего пользователи выполняют форматирование числовых выражений в текстовые через контекстное меню.
Преобразовать число в текстовый вид можно также воспользовавшись инструментами на ленте, в частности, использовав поле для показа формата, о котором шел разговор выше.
Данные преобразовываются в текстовый вариант.
Ещё одним вариантом преобразования числовых данных в тестовые в Экселе является применение специальной функции, которая так и называется – ТЕКСТ . Данный способ подойдёт, в первую очередь, если вы хотите перенести числа как текст в отдельный столбец. Кроме того, он позволит сэкономить время на преобразовании, если объем данных слишком большой. Ведь, согласитесь, что перещелкивать каждую ячейку в диапазоне, насчитывающем сотни или тысячи строк – это не самый лучший выход.
ТЕКСТ(значение;формат)
Открывшееся окно имеет два поля, которые соответствуют данным аргументам: «Значение» и «Формат» .
В поле «Значение» нужно указать преобразовываемое число или ссылку на ячейку, в которой оно находится. В нашем случае это будет ссылка на первый элемент обрабатываемого числового диапазона.
В поле «Формат» нужно указать вариант отображения результата. Например, если мы введем «0» , то текстовый вариант на выходе будет отображаться без десятичных знаков, даже если в исходнике они были. Если мы внесем «0,0» , то результат будет отображаться с одним десятичным знаком, если «0,00» , то с двумя, и т.д.
После того, как все требуемые параметры введены, щелкаем по кнопке «OK» .
Если же пользователь желает заменить данные исходного формата, то вместо указанного действия нужно выделить именно его и произвести вставку тем же способом, который указан выше.
Теперь давайте разберемся, какими способами можно выполнить обратную задачу, а именно как преобразовать текст в число в Excel.
Проще и быстрее всего выполнить конвертацию текстового варианта с помощью специального значка, который сообщает об ошибке. Этот значок имеет вид восклицательного знака, вписанного в пиктограмму в виде ромба. Он появляется при выделении ячеек, которые имеют пометку в левом верхнем углу зеленым цветом, обсуждаемую нами ранее. Эта пометка ещё не свидетельствует о том, что данные находящиеся в ячейке обязательно ошибочные. Но цифры, расположенные в ячейке имеющей текстовый вид, вызывают подозрения у программы в том, что данные могут быть внесены некорректно. Поэтому на всякий случай она их помечает, чтобы пользователь обратил внимание. Но, к сожалению, такие пометки Эксель выдает не всегда даже тогда, когда цифры представлены в текстовом виде, поэтому ниже описанный способ подходит не для всех случаев.
Если подобных текстовых значений, которые следует преобразовать, не одно, а множество, то в этом случае можно ускорить процедуру преобразования.
Все данные массива будут преобразованы в указанный вид.
Как и для преобразования данных из числового вида в текст, в Экселе существует возможность обратного конвертирования через окно форматирования.
После выполнения этих действий все значения выбранного диапазона преобразуются в нужный нам вид.
Перевести текстовые данные в числовые можно, воспользовавшись специальным полем на ленте инструментов.
Значения в диапазоне будут преобразованы из текстовых в числовые.
Также для преобразования текстовых значений в числовые можно использовать специальные формулы. Рассмотрим, как это сделать на практике.
Кстати, для преобразования значений данным методом совсем не обязательно использовать исключительно двойное умножение на «-1» . Можно применять любое другое арифметическое действие, которое не ведет к изменению значений (сложение или вычитание нуля, выполнение возведения в первую степень и т.д.)
Следующий способ по принципу действия очень похож на предыдущий с той лишь разницей, что для его использования не нужно создавать дополнительный столбец.
Ещё одним вариантом, при котором можно преобразовать текст в числовой вид, является применение инструмента «Текст столбцами» . Его есть смысл использовать тогда, когда вместо запятой в качестве разделителя десятичных знаков используется точка, а в качестве разделителя разрядов вместо пробела – апостроф. Этот вариант воспринимается в англоязычном Экселе, как числовой, но в русскоязычной версии этой программы все значения, которые содержат указанные выше знаки, воспринимаются как текст. Конечно, можно перебить данные вручную, но если их много, это займет значительное количество времени, тем более что существует возможность гораздо более быстрого решения проблемы.
Если вам часто приходится преобразовывать большие области данных из текстового формата в числовой, то имеется смысл в этих целях записать специальный макрос, который будет использоваться при необходимости. Но для того, чтобы это выполнить, прежде всего, нужно в своей версии Экселя включить макросы и панель разработчика, если это до сих пор не сделано.
Как видим, существует довольно много вариантов преобразования в 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 й способ).
При работе с 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 преобразовать текст в число, применив его на практике несколько раз, вы приспособитесь делать это быстро и эффективно, получая нужный результат.
При импорте файлов или копировании данных с числовыми значениями часто возникает проблема: число преобразуется в текст. В результате формулы не работают, вычисления становятся невозможными. Как это быстро исправить? Сначала посмотрим, как исправить ошибку без макросов.
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
Таким образом, возникающую при импорте или копировании числовых данных ошибку легко устранить. Преобразовать текст в число можно разными способами (с помощью макросов и без них). Сделать это просто и быстро. А впоследствии с числовыми аргументами производятся все необходимые операции.
