Excel как преобразовать текст в число
Как уже говорилось ранее, значения ячеек в текстовом формате отображаются точно так же, как вводятся. Excel воспринимает эти значения как строки, то есть как текстовые значения, независимо от того, содержатся ли в ячейках текст или числа. Другими словами значения 1 и «1» Excel понимают по-разному. Получается, что число можно отобразить и как число, используя числовой формат, и как текст, используя формат текстовый. Разница между этими числами заключается в том, что числа в числовом формате участвуют в расчетах, а числа в текстовом формате — нет! Это может приводить к ошибкам в вычислениях. Как отличить эти числа друг от друга?
Как отличить настоящее число от числа, отформатированного как текст?
Разработчики Excel об этом позаботились. Числа в ячейках с числовым форматом выравниваются по правому краю ячеек, а с текстовым форматом — по левому. Кроме того, Excel осуществляет фоновый контроль ошибок, конечно при условии того, что он настроен в параметрах Excel, в разделе «Формулы».
Когда Excel встречает в ячейках листа числа, отформатированные как текст — помечает их индикатором ошибок. Индикатор ошибок представляет собой цветной треугольник (цвет настраивается, по умолчанию он зеленый) в левом верхнем углу ячейки. Индикатор ошибок появляется в ячейках только при нарушении одного из правил контроля ошибок.
Несмотря на все старания разработчиков оградить пользователей от ошибок, связанных с использованием текстового формата для чисел, такие ошибки все же возникают. Приведу несколько наглядных примеров.
1) Если в ячейку с текстовым форматом, внести числовое значение, а затем формат ячейки изменить на числовой, то число остается отформатированным как текст и не будет участвовать в расчетах;
2) Если в настройках Excel отключен контроль ошибок, то индикатор ошибок не отображается, при этом числа отформатированные как текст, становятся незаметными, что может привести к ошибкам в вычислениях;
3) Если контроль ошибок включен, а правило контроля «Числа, отформатированные как текст или с предшествующим апострофом» выключено, то индикатор ошибок не отображается и числа, отформатированные как текст, становятся незаметными, что также может привести к ошибкам в расчетах;
4) Если пользователь проигнорировал появление индикатора ошибок, не придал ему должного значения или не заметил индикатор в большом объеме данных таблицы.
Внимательный читатель может спросить «А как же выравнивание числовых значений по правому краю, а текстовых — по левому? Видно же различие!» Видно, если не установлен автоподбор ширины столбцов, если ширина столбцов заметно больше длины значений, находящихся в ячейках, и выравнивание по разным краям ячейки визуально заметно, если не установлено выравнивание по центру. Кроме всех перечисленных «если» числа с текстовым форматом могут попадать в таблицы при переносе этих таблиц в Excel, например, из текстового редактора Word.
В некоторых случаях в ячейках с числами могут стоять какие-либо непечатаемые символы, что также превращает числа в текст.
Как преобразовать текст (строку) в число?
Для того, чтобы преобразовать числа, отформатированные как текст, в настоящие числа, необходимо выделить ячейку (переместить в нее рамку табличного маркера), после этого кликнуть левой кнопкой мыши на значке информационного сообщения индикатора ошибок и выбрать пункт «Преобразовать в число».
Можно также в выбранной ячейке нажать клавишу F2 на клавиатуре и затем нажать клавишу Enter. Помогает, но не всегда! Можно изменить формат ячеек на числовой формат и заново ввести значения вручную. Для исправления нескольких ячеек тоже вариант. В некоторых случаях можно воспользоваться умножением на единицу или поиском/заменой непечатаемых символов.
Для преобразования же текста в число в больших таблицах или для поиска подобного рода ошибок в большом объеме данных эффективнее всего воспользоваться надстройкой.
Надстройка для поиска и преобразования чисел, отформатированных как текст в настоящие числа
Надстройка представляет собой xla-файл, в котором сохранен программный код, написанный на встроенном в приложения Office языке программирования. Надстройка устанавливается через меню приложения Excel (установка надстроек) и по завершении установки в Excel появляется новая панель инструментов либо новая кнопка на вкладке «Надстройки».
Нажатием этой кнопки запускается программа и отображает диалоговое окно, в котором пользователь устанавливает необходимые параметры работы программы. В диалоговом окне пользователь на свое усмотрение может выбрать диапазон для проведения преобразования текста в число. Здесь «используемый диапазон» — это диапазон от первой заполненной до последней заполненной ячейки на активном листе. Диапазон для поиска и замены можно выбирать из четырех возможных.
видео по преобразованию строк в числа
надстройка (макрос) для преобразования текста, похожего на число, в число
Далее макрос производит проверку данных в указанном диапазоне и преобразует текст, похожий на числа, в настоящие (реальные) числа. Количество таких преобразований подсчитывается и сообщается пользователю по окончании действия макроса. В надстройке предусмотрена возможность удаления непечатаемых символов из ячеек выбранного диапазона. Кроме этого предусмотрена возможность изменения формата в тех ячейках, где найден текст, похожий на число для того, чтобы не нарушать общий стиль отображаемых данных. Использование этой надстройки — быстрый, надежный и безошибочный способ трансформирования текста в число.
Надстройка позволяет удалять апострофы перед числами, находить числа, отформатированные как текст и преобразовывать их в реальные числа, а также удалять начальные, конечные пробелы и другие непечатаемые символы (Tab, Alt+Enter) в выбранном диапазоне значений.
