Одна из наиболее распространенных задач, с которыми сталкиваются пользователи Excel, — преобразование числовых выражений в текстовый формат и наоборот. Этот вопрос часто заставляет тратить много времени на решение, если пользователь не знает четкого алгоритма действий. Давайте посмотрим, как можно по-разному решить обе проблемы.
Конвертация числа в текстовый вид
Все ячейки в Excel имеют определенный формат, который сообщает программе, как обращаться с тем или иным выражением. Например, даже если в них записаны числа, но установлен текстовый формат, приложение будет рассматривать их как обычный текст и не сможет выполнять математические вычисления с этими данными. Чтобы Excel воспринимал числа как числа, они должны быть помещены в элемент листа с общим или числовым форматом.
Для начала рассмотрим различные варианты решения проблемы преобразования чисел в текст.
Способ 1: форматирование через контекстное меню
Чаще всего пользователи выполняют форматирование числовых выражений в текст через контекстное меню.
- Выберите элементы листа, в которых нужно преобразовать данные в текст. Как видите, во вкладке «Главная» на панели инструментов в блоке «Число» в специальном поле отображается информация о том, что эти элементы имеют общий формат, а значит, вписанные в них числа воспринимаются программой как количество.
- Щелкните правой кнопкой мыши по выделенному фрагменту и в открывшемся меню выберите пункт «Форматировать ячейки…».
- В открывшемся окне форматирования перейдите на вкладку «Число», если она была открыта в другом месте. В блоке настроек «Числовые форматы» выберите пункт «Текст». Чтобы сохранить изменения, нажмите кнопку «ОК» внизу окна.
- Как видите, после этих манипуляций в специальном поле отображается информация о том, что ячейки преобразованы в текстовый формат.
- Но если мы попытаемся вычислить автосумму, она отобразится в ячейке ниже. Это означает, что преобразование было неполным. Это одна из замечательных возможностей Excel. Программа не позволяет выполнить преобразование данных наиболее интуитивно понятным способом.
- Чтобы завершить преобразование, нам нужно последовательно дважды щелкнуть левой кнопкой мыши, чтобы поместить курсор на каждый элемент диапазона отдельно, и нажать клавишу Enter. Чтобы упростить задачу, вы можете использовать функциональную клавишу F2 вместо двойного щелчка.
- После проведения этой процедуры со всеми ячейками области содержащиеся в них данные будут восприниматься программой как текстовые выражения и, следовательно, автосумма будет равна нулю. Кроме того, как видите, левый верхний угол ячеек будет окрашен в зеленый цвет. Это также косвенный признак того, что элементы, содержащие числа, были преобразованы в вариант отображения текста. Хотя эта функция не всегда требуется и в некоторых случаях такой знак отсутствует.
Способ 2: инструменты на ленте
Вы также можете преобразовать число в текстовую форму с помощью инструментов на ленте, в частности, используя поле для отображения формата, о котором говорилось выше.
- Выберите элементы, данные которых нужно преобразовать в текст. Находясь на вкладке «Главная», щелкните значок треугольника справа от поля, в котором отображается формат. Он находится в панели инструментов «Число».
- В открывшемся списке вариантов форматирования выберите пункт «Текст».
- Также, как и в предыдущем методе, мы последовательно устанавливаем курсор на каждый элемент диапазона, дважды щелкая левой кнопкой мыши или нажимая клавишу F2, а затем нажимая клавишу Enter.
Данные преобразуются в текстовую версию.
Способ 3: использование функции
Другой вариант преобразования числовых данных в тестовые данные в Excel — использование специальной функции под названием ТЕКСТ. Этот способ подходит, в первую очередь, если вы хотите перенести числа в виде текста в отдельный столбец. Кроме того, это сэкономит время на преобразовании, если объем данных слишком велик. В конце концов, вы должны согласиться с тем, что переворачивание каждой ячейки в диапазоне сотен или тысяч строк — не лучший способ.
- Устанавливаем курсор на первый элемент диапазона, где будет отображаться результат конвертации. Щелкните значок «Вставить функцию», расположенный рядом со строкой формул.
- Откроется окно мастера. В категории «Текст» выберите пункт «ТЕКСТ». Затем нажмите кнопку «ОК».
- Откроется окно аргументов оператора ТЕКСТ. Эта функция имеет следующий синтаксис:
= ТЕКСТ (значение; формат)
В открывшемся окне есть два поля, которые соответствуют заданным аргументам: «Значение» и «Формат».
В поле «Значение» нужно указать преобразованное число или ссылку на ячейку, в которой оно находится. В нашем случае это будет ссылка на первый элемент обрабатываемого диапазона номеров.
В поле «Формат» нужно указать вариант отображения результата. Например, если мы введем «0», вариант текста на выходе будет отображаться без десятичных знаков, даже если они были в источнике. Если ввести «0,0», результат будет отображаться с одним десятичным знаком, если «0,00», то с двумя и т.д.
После ввода всех необходимых параметров нажмите кнопку «ОК».
- Как видите, значение первого элемента указанного диапазона было отображено в ячейке, которую мы выбрали в первом абзаце этого руководства. Чтобы передать другие значения, вам нужно скопировать формулу в соседние элементы листа. Поместите курсор в нижний правый угол элемента, содержащего формулу. Курсор преобразуется в небольшой крестообразный маркер заливки. Удерживая левую кнопку мыши, перетащите пустые ячейки параллельно диапазону, в котором расположены исходные данные.
- Теперь вся строка заполнена необходимыми данными. Но это еще не все. Фактически, все элементы нового диапазона содержат формулы. Выделите эту область и щелкните значок «Копировать», расположенный на вкладке «Главная» в группе инструментов ленты «Буфер обмена».
- Кроме того, если мы хотим сохранить оба диапазона (исходный и преобразованный), не снимайте выделение с области, содержащей формулы. Щелкните по нему правой кнопкой мыши. Список контекстных действий запускается. Выберите в нем место «Специальная вставка». Из вариантов действия в открывшемся списке выберите «Числовые значения и форматы».
Если пользователь хочет заменить данные в исходном формате, то вместо указанного действия выберите его и введите так же, как указано выше.
- В любом случае данные в текстовом формате будут вставлены в выбранный диапазон. Если вы все же выбрали вставку в исходной области, ячейки, содержащие формулы, можно очистить. Для этого выделите их, щелкните правой кнопкой мыши и выберите пункт «Очистить содержимое».
На этом процедура преобразования завершена.
Конвертация текста в число
Теперь давайте узнаем, как вы можете выполнить противоположную задачу, а именно как преобразовать текст в число в Excel.
Способ 1: преобразование с помощью значка об ошибке
Самый быстрый и простой способ преобразовать текстовую версию — использовать специальный значок, сигнализирующий об ошибке. Этот значок выглядит как восклицательный знак, вписанный в ромбовидную пиктограмму. Он появляется, когда вы выбираете ячейки с зеленой отметкой в верхнем левом углу, о чем мы говорили ранее. Этот знак не означает, что данные в ячейке обязательно неверны. Но числа, расположенные в ячейке с текстовой формой, вызывают в программе подозрения, что данные могут быть введены некорректно. Поэтому на всякий случай помечает их, чтобы пользователь обратил внимание. Но, к сожалению, Excel не всегда предоставляет такие знаки, даже когда числа представлены в текстовом виде, поэтому описанный ниже метод подходит не для всех случаев.
- Выделите ячейку, содержащую зеленый индикатор возможной ошибки. Щелкните появившийся значок.
- Откроется список действий. Выбираем в нем значение «Преобразовать в число».
- В выбранном элементе данные будут немедленно преобразованы в числовую форму.
Если необходимо преобразовать много похожих текстовых значений, то в этом случае вы можете ускорить процесс преобразования.
- Выделите весь диапазон, содержащий текстовые данные. Как видите, пиктограмма появилась одна для всей области, а не для каждой ячейки в отдельности. Щелкаем по нему.
- Откроется уже знакомый список. Как и в прошлый раз, выберите «Преобразовать в число».
Все данные в массиве будут преобразованы в указанную форму.
Способ 2: конвертация при помощи окна форматирования
Помимо преобразования данных из числового формата в текст, в Excel есть возможность обратного преобразования через окно форматирования.
- Выберите диапазон, содержащий числа в текстовой версии. Щелкните правой кнопкой мыши. В контекстном меню выберите пункт «Форматировать ячейки…».
- Откроется окно форматирования. Как и в прошлый раз, переходим во вкладку «Номер». В группе Числовые форматы нам нужно выбрать значения, которые преобразуют текст в число. К ним относятся элементы «Общие» и «Числовые». Что бы вы ни выбрали, программа будет рассматривать числа, введенные в ячейку, как числа. Сделаем выбор и нажмем на кнопку. Если вы выбрали значение «Числовое», в правой части окна вы можете настроить представление числа: установить количество десятичных цифр после запятой, установить разделители между цифрами. После завершения настройки нажмите кнопку «ОК».
- Теперь, как и в случае преобразования числа в текст, нам нужно щелкнуть по всем ячейкам, поместив курсор в каждую из них и затем нажав клавишу Enter.
После выполнения этих действий все значения выбранного диапазона преобразуются в нужный нам вид.
Способ 3: конвертация посредством инструментов на ленте
вы можете преобразовать текстовые данные в числовые, используя специальное поле на панели инструментов.
- Выберите диапазон для преобразования. Перейдите на вкладку «Главная» на ленте. Щелкните поле с выбором формата в группе «Число». Выбираем пункт «Числовой» или «Общие».
- Затем нажимайте на каждую ячейку преобразованной области с помощью клавиш F2 и Enter описанным нами способом более одного раза.
Значения в диапазоне будут преобразованы из текста в числовые.
Способ 4: применение формулы
Вы также можете использовать специальные формулы для преобразования текстовых значений в числовые значения. Посмотрим, как это сделать на практике.
- В пустой ячейке, параллельной первому элементу диапазона для преобразования, введите знак равенства (=) и двойной знак минус (-). Далее указываем адрес первого элемента преобразованного диапазона. Таким образом, происходит двойное умножение на значение «-1». Как известно, умножение «минуса» на «минус» дает «плюс». То есть в ячейке назначения мы получаем то же значение, что и было изначально, но уже в числовом виде. Эта процедура называется двоичным двойным отрицанием.
- Нажимаем клавишу Enter, после чего получаем готовое преобразованное значение. Чтобы применить эту формулу ко всем другим ячейкам в диапазоне, мы используем маркер заполнения, который мы ранее применили для функции ТЕКСТ.
- Теперь у нас есть диапазон, полный значений с формулами. Выделите его и нажмите кнопку «Копировать» на вкладке «Главная» или используйте сочетание клавиш Ctrl + C.
- Выберите исходную область и щелкните ее правой кнопкой мыши. В списке включенных контекстов перейдите к пунктам «Специальная вставка» и «Значения и числовые форматы».
- Все данные вводятся в нужную нам форму. Теперь вы можете удалить диапазон транзита, в котором находится формула двоичного двойного отрицания. Для этого выделите эту область, вызовите контекстное меню, щелкнув правой кнопкой мыши и выберите в нем пункт «Удалить содержимое».
Кстати, для преобразования значений этим методом вовсе не обязательно использовать только двойное умножение на «-1». Вы можете использовать любую другую арифметическую операцию, которая не приводит к изменению значений (сложение или вычитание нуля, повышение до первой степени и т.д.)
Способ 5: применение специальной вставки
Принцип работы следующего метода очень похож на предыдущий, с той лишь разницей, что для его использования не нужно создавать дополнительный столбец.
- В любую пустую ячейку листа введите цифру «1». Затем выберите его и щелкните знакомый значок «Копировать» на ленте.
- Выберите область листа, которую нужно преобразовать. Щелкните по нему правой кнопкой мыши. В открывшемся меню дважды щелкните пункт «Специальная вставка».
- В окне «Специальная вставка» установите переключатель в блоке «Операция» в положение «Умножить». Затем нажмите кнопку «ОК».
- После этого действия все значения выбранной области будут преобразованы в числовые значения. Теперь при желании можно убрать цифру «1», которую мы использовали для конвертации.
Способ 6: использование инструмента «Текст столбцами»
Другой вариант преобразования текста в числа — использование инструмента «Столбцы текста». Имеет смысл использовать его, когда вместо запятой в качестве разделителя десятичных знаков используется точка, а в качестве разделителя цифр вместо пробела используется апостроф. Эта опция воспринимается в англоязычном Excel как числовая, но в русскоязычной версии этой программы все значения, содержащие указанные выше символы, воспринимаются как текст. Конечно, вы можете уничтожить данные вручную, но если их много, это займет значительное время, тем более что существует возможность гораздо более быстрого решения проблемы.
- Выделите фрагмент листа, содержимое которого нужно преобразовать. Переходим во вкладку «Данные». На панели инструментов в блоке «Работа с данными» щелкните значок «Текст по столбцам».
- Текстовый мастер запускается. В первом окне убедитесь, что переключатель формата данных находится в положении «С разделителями». По умолчанию он должен находиться в этом месте, но не лишним будет проверить его статус. Затем нажмите кнопку «Далее».
- Во втором окне также оставляем все без изменений и нажимаем на кнопку «Далее».
- Но после открытия третьего окна текстового мастера нужно нажать на кнопку «Подробнее».
- Откроется окно для дальнейших настроек импорта текста. В поле «Разделитель целой и дробной частей» установите точку, а в поле «Разделитель цифр» — апостроф. Затем нажимаем на кнопку «ОК».
- Вернитесь в третье окно текстового мастера и нажмите кнопку «Готово».
- Как видите, после выполнения этих действий числа приняли обычный для русскоязычной версии формат, а это значит, что они одновременно были преобразованы из текстовых данных в числовые.
Способ 7: применение макросов
Если вам часто приходится преобразовывать большие области данных из текстового в числовой формат, то для этой цели имеет смысл написать специальный макрос, который будет использоваться при необходимости. Но для этого в первую очередь необходимо включить макросы и панель разработчика в вашей версии Excel, если это еще не сделано.
- Переходим во вкладку «Разработчик». Щелкните значок ленты «Visual Basic», расположенный в группе «Код».
- Запускается стандартный редактор макросов. Вбиваем или копируем в него следующее выражение:
Sub Text_in_number()
Selection.NumberFormat = «Общие»
Selection.Value = Selection.Value
Конец подзаголовкаЗатем закройте редактор, нажав стандартную кнопку закрытия в правом верхнем углу окна.
- Выделите на листе фрагмент, который нужно преобразовать. Щелкните значок «Макрос», который находится на вкладке «Разработчик» в группе «Код».
- Откроется окно макросов, записанных в вашей версии программы. Найдите макрос с именем «Text_in_number», выберите его и нажмите кнопку «Run».
- Как видите, текстовое выражение сразу преобразуется в числовой формат.
Как видите, в Excel есть несколько вариантов преобразования чисел, которые записываются в числовой форме, в текстовом формате и в обратном направлении. Выбор того или иного метода зависит от многих факторов. Прежде всего, это поставленная задача. В конце концов, например, вы можете быстро преобразовать текстовое выражение с внешними разделителями в одно числовое, используя инструмент «Текст в столбцы». Второй фактор, влияющий на выбор варианта, — это объем и частота выполняемых преобразований. Например, если вы часто используете такие преобразования, имеет смысл записать макрос. И третий фактор — индивидуальная простота использования.
