Как преобразовать числа из текстового формата в числовой
Иногда значения в Excel выглядят как числа, но на самом деле это текст. Их нельзя суммировать, умножать, вычитать, хотя выглядят как числа. Дело в том, что это числа в формате текста.
Excel помогает автоматически конвертировать данные в числа при копировании из посторонних источников. Не всегда это получается. В этой статье расскажу как конвертировать такие «текстовые» числа в настоящие.
Как найти числа в текстовом формате
Excel автоматически проверяет значения на принадлежность к числовому формату. Вы могли видеть в эксель таблицах ячейки с маленьким зеленым треугольником в левом верхнем углу. Это сигнал, что с форматом ячейки неполадки. Чтобы узнать детали ошибки надо подвести курсор мыши к желтому ромбику с восклицательным знаком и прочитать уведомление:
Иногда уведомление о текстовом формате ячейки с числами отсутствует. Понять, что ячейка с числом в текстовом формате можно по визуальным признакам:
- Числа выровнены по левому краю
- Если выделена группа ячеек с такими «числами», то в строке состояния не будет подсказок по сумме, среднему чисел в выделенном диапазоне
- Апостроф стоит первым символом в строке формул этой ячейки
На скриншоте ниже пример как Excel автоматически выравнивает по левому краю текстовые значения и по правому краю числовые значения.
Как конвертировать текст в числа в Excel
Конвертация текста в числа через меню ошибки
Если в левом верхнему углу ячеек с числовыми значениями появился зеленый треугольник, то:
Применять этот метод следует на небольшое количество ячеек. Не рекомендую использовать этот метод для конвертации сотен, тысяч строк. Excel может «зависнуть» в процессе конвертации.
Как конвертировать текст в число с помощью смены формата ячейки
Есть еще один способ изменить формат значений текстовой ячейки на числовой. Сделать это можно с помощью смены формата ячейки на числовой. Как это сделать:
- Выделите левой клавишей мыши диапазон ячеек
- На панели инструментов перейдите на вкладку «Главная»
- Перейдите в подгруппу «Число» и в выпадающем списке выберите формат «Числовой»
Примечание. Этот метод помогает не в 100% случаев. Например, если к ячейке был применен текстовый формат, после этого введено число, затем изменен формат ячейки на числовой, то она продолжит отображаться как текст.
Как преобразовать текст в число с помощью специальной вставки
Этот метод требует чуть больше действий, но работает в 99% случаев.
- Выделить ячейки с числами в текстовом формате, и задать формат ячейки «Основной»
- Кликнуть левой клавишей мыши по пустой ячейке на листе
- Скопировать эту ячейку, зажав сочетание клавиш CTRL + C
- Выделить левой клавишей мыши ячейки которые хотите конвертировать в числа
- Кликнуть правой клавишей мыши и выбрать пункт «Специальная вставка» в выпадающем меню
- В диалоговом окне в категории «Вставить» поставить галочку напротив «Значения» . В разделе «Операция» ставим галочку напротив «Сложить» и нажимаем «ОК»
Значения в выделенном диапазоне теперь будут упорядочены по правой стороне и Excel будет воспринимать их как числа.
Как конвертировать текст в числа с помощью функции ЗНАЧЕН()
Для конвертации числовых значений в текстовом формате в числа часто используют функцию ЗНАЧЕН() . Функция предназначена для перевода текста в числа. Напишите функцию в адресной строке и укажите ссылку на ячейку для конвертации.
Как конвертировать текст в числа с помощью математических вычислений
Достаточно умножить значение в текстовом формате на единицу, чтобы получить значение в числовом формате в Excel.
Прибавьте ноль или разделите значение на единицу и текстовое значение будет преобразовано в числовой формат.
Посмотрите видео урок про два способа преобразования чисел в текст в Excel:
Поделитесь в комментариях какой из методов вам кажется самым удобным в работе.
[expert_bq id=»1570″]Чтобы отобразить стрелку трассировки для каждой ячейки, зависимой от активной ячейки, на вкладке Формулы в группе Зависимости формул нажмите кнопку Зависимые ячейки. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] В ячейку, куда мы хотим вставить связь, ставим знак равенства (так же как и для обычной формулы), переходим в исходную книгу, выбираем ячейку, которую хотим связать, щелкаем Enter. Вы можете использовать инструменты копирования и автозаполнения для формул связи так же, как и для обычных формул.Как в Excel Сделать Значение Одной Ячейки Равной Значению Другой. Если найти | 📝Справочник по Excel
Я offsetRelative функцию offsetRelative чтобы использовать Application.Caller для определения ячейки / диапазона, где находится кнопка, и получить номер строки из этого. Затем вместо жесткого кодирования в диапазоне, таком как «AE3», мы можем объединить столбец «AE» с номером строки:
[expert_bq id=»1570″]Эта функция полезна в том случае, если вы начинаете с исходной таблицы или хотите создать ссылки на одни и те же исходные ячейки в нескольких конечных таблицах. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq]
А теперь немного пояснения. Ссылка в Excel это указание адреса на ячейку (диапазон) зашифрованное в формулу. Ссылки в Excel могут быть относительными, абсолютными и смешанными. Относительная ссылка – это значение формулы с изменяемым адресом относительно нового места. То есть к примеру, у нас в ячейке A16 стоит вот такая формула для подсчета суммы в диапазоне A1:A15
Как обрезать текст в ячейке excel
- Укажите ячейку, для которой следует найти зависимые ячейки.
- Чтобы отобразить стрелку трассировки для каждой ячейки, зависимой от активной ячейки, на вкладке Формулы в группе Зависимости формул нажмите кнопку Зависимые ячейки .
Однако не все функции поддерживают применение подстановочных знаков. Некоторые из них (к примеру, функция НАЙТИ) любой символ воспринимают как текст, даже несмотря на то, что он может быть служебным.
С помощью функции НАЙТИ найдем в тексте позицию вхождения вопросительного знака и звездочки:
Как преобразовать текст в число с помощью специальной вставки
- * (звездочка); Обозначает любое произвольное количество символов. Например, поиск по фразе «*ник» найдет слова типа «понедельник», «всадник», «источник» и т.д.
- ? (вопросительный знак); Обозначает один произвольный символ. К примеру, поиск по фразе «ст?л» найдет «стол», «стул» и т.д.
- ~ (тильда) с последующими знаками *, ? или ~. Обозначает конкретный символ *, ? или ~. Например, поиск по фразе «хор*» найдет все фразы начинающиеся на «хор» («хоровод», «хорошо» и т.д.). Поэтому для точного поиска «хор*» нужно использовать символ «~» и искать по фразе «хор~*». Наличие «~» гарантирует, что Excel прочитает следующий символ как текст, а не как подстановочный знак.
— Диапазон условия 1 — Диапазон ячеек, которые проверяются на соответствие определенному условию.
— Условие 1 — Условие, которое определяет какие ячейки надо учитывать при подсчете.
Обратите внимания, что диапазонов условий и соответственно условий может быть несколько.