Как в excel присвоить тексту числовое значение

Автор: | 05.05.2022

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

Пример присвоения имени в Excel

Когда мы управляем магазином, мы хотим знать все о ценах на его товары. Сколько мы покупаем, сколько продаем. То есть нам нужен прайс-лист с оптовой закупкой и розничными ценами, в котором определяется наша прибыль. Чтобы упростить пример, мы установим одинаковую разметку 10% для всех заголовков.

Как видите, при изменении разметки в этом случае нужно изменить все формулы. Конечно, для этого вы можете ввести значение маржи в отдельную ячейку и сделать на него абсолютную ссылку в формулах. Но гораздо удобнее использовать название, если у каждой товарной группы своя разметка. В конце концов, такие ссылки в аргументах формулы легко читать.

Дадим разметке имя, а пока установим ее значение на 10%, потом изменим. Чтобы назвать значение, сделайте следующее:

 

  1. Выберите инструмент: Формулы — Определенные имена — Присвоить имя».
  2. Введите имя «Markup1», а в поле «Диапазон» только значение 10%, как показано на изображении. При необходимости выберите объем названия (для конкретного листа или для всей книги). И жмем ОК.
  3. Теперь нам нужно применить наше имя «Markup1» в формулах.

При создании имен следует помнить о нескольких простых правилах:

  1. Имя должно начинаться с буквы или подчеркивания. Первый символ имени не может быть числом или специальным символом.
  2. Начиная со второго символа, вы можете использовать: буквы, цифры, точки и символы подчеркивания. Другие символы запрещены (даже пробел).
  3. Пробелы использовать нельзя.

Теперь мы можем без проблем изменить разметку, и формулы автоматически пересчитают результат расчета. Например, увеличим нашу наценку на 2%:

 

  1. Выберите инструмент «Формулы» — «Диспетчер имен».
  2. В диалоговом окне выберите «Markup1» и нажмите кнопку «Изменить». В поле «Интервал» введите новое значение 12% (или 0,12). И жмем ОК. После щелчка все формулы с именем «Markup1» автоматически пересчитаются и дадут новые результаты.

Примечание. Если формулы не пересчитываются, у вас есть возможность ручного пересчета формул, включенная в настройках Excel («Формулы» — «Параметры расчета» — «Вручную»), в этом случае мы получим новые цены после нажатия клавиши F9.

Преимущества имен в Excel

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

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

Как присвоить значение ячейке из списка по номеру позиции

Бывают ситуации, когда необходимо присвоить ячейке значение, которое зависит от результата, выраженного в числовом выражении. Для таких ситуаций подойдет функция «ВЫБОР» или в англоязычной версии «ВЫБРАТЬ». Эта функция присваивает результат ячейке по указанному индексу.

Рассмотрим пример использования функции «ВЫБОР».

Например, необходимо автоматизировать присвоение статуса школьникам в зависимости от полученной оценки: 2 — плохо, 3 — С и так далее.

Это можно сделать с помощью функции «Выбрать».

Создадим список состояний от плохого студента до отличника.

Выбираем ячейку, в которую будет записана оценка.

Выбираем ячейку, в которую будет записан результат (статус студента).

В ячейку результата записываем функцию «Выбор».

Как прописать функцию «Выбор».

В поле номера индекса укажите ячейку с оценкой.

В полях «Значение 1,2 . и т.д.» Укажите статус учащихся в порядке возрастания.

=ВЫБОР(B7;C6;D6;E6;F6;G6)

Теперь при вводе числа 2 в ячейку с оценкой статус ученика (результат) будет равен Неудачникам.

ТЕКСТ (функция ТЕКСТ)

С помощью функции ТЕКСТ можно изменить представление числа, применив форматирование с кодами формата. Это полезно, когда вы хотите отображать числа в удобочитаемой форме или комбинировать их с текстом или символами.

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

Аргументы функции ТЕКСТ описаны ниже.

Числовое значение, которое нужно преобразовать в текст.

Текстовая строка, определяющая формат, применяемый к указанному значению.

Общие сведения

Более простая функция ТЕКСТ означает следующее:

= ТЕКСТ (значение для форматирования; «применяемый код формата»)

Ниже приведены популярные примеры, которые вы можете скопировать прямо в Excel, чтобы поэкспериментировать самостоятельно. Обратите внимание, что коды формата заключены в кавычки.

Денежный формат с разделителем групп цифр и двумя цифрами дробной части, например: 1 234,57 ₽. Примечание. Excel округляет значение до двух десятичных знаков.

Сегодняшняя дата в формате ДД / ММ / ГГ, например: 14.03.12

Сегодня день недели, например: понедельник

Текущее время, например: 13:29

Формат процентного соотношения, например: 28,5 %

Дробный формат, например: 4 1/3

Дробный формат, например: 1/3. Обратите внимание, что TRIM используется для удаления начального пробела перед дробной частью.

Экспоненциальная запись, например: 1.22E + 07

Скачивание образцов

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

Другие доступные коды форматов

вы можете просмотреть другие доступные коды формата в диалоговом окне «Формат ячеек.

Нажмите CTRL + 1 (на Mac — +1), чтобы открыть диалоговое окно Формат ячеек.

На вкладке Число выберите желаемый формат.

Выбрать (все форматы).

Требуемый код формата будет показан в поле Тип. В этом случае выберите все содержимое поля Тип, кроме точки с запятой (;) и символа @. В следующем примере выбирается и копируется только код ДД.ММ.ГГГГ.

Нажмите CTRL + C, чтобы скопировать код формата, затем нажмите кнопку «Отмена», чтобы закрыть диалоговое окно «Формат ячеек.

Теперь все, что вам нужно сделать, это нажать CTRL + V, чтобы вставить код формата в функцию ТЕКСТ. Пример: = ТЕКСТ (B2; «ДД.ММ.ГГГГ»). Убедитесь, что вы заключили скопированный код формата в кавычки («код формата»), иначе Excel отобразит сообщение об ошибке.

 

Ячейки> Число> Другое, чтобы получить строки формата.» />

Коды форматов по категориям

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

  • Выбор числового формата
  • Ведущие нули
  • Сепаратор группы грома.
  • Числовые, валютные и финансовые форматы
  • У тебя
  • Значения времени
  • Интерес
  • Фракции
  • Экспоненциальная запись
  • Дополнительные форматы

Почему Excel удаляет ведущие нули?

Excel обрабатывает последовательность чисел, введенных в ячейку, как число, а не как числовой код, например SKU или SKU. Чтобы сохранить нули в начале числовых последовательностей, примените текстовый формат к соответствующему диапазону ячеек перед вводом или вводом значений. Выберите столбец или диапазон, в котором вы хотите разместить значения, нажмите CTRL + 1, чтобы открыть диалоговое окно «Формат ячеек», и на вкладке «Число» выберите «Текст». Теперь Excel не удаляет ведущие нули.

Если вы уже ввели данные и Excel удалил ведущие нули, вы можете добавить их снова, используя функцию ТЕКСТ. Обратитесь к верхней ячейке со значениями и используйте формат = ТЕКСТ (значение; «00000»), где количество нулей представляет количество символов, которое вы хотите. Затем скопируйте функцию и примените ее к остальной части диапазона.

Если по какой-то причине вам нужно преобразовать текстовые значения обратно в числа, вы можете умножить их на 1 (например: = D4 * 1) или использовать унарный двойной оператор (-), например: = -D4.

В Excel группы цифр разделяются пробелом, если код формата содержит пробел, окруженный числовыми символами (#) или нулями. Например, если код формата «# ###», число 12200000 отображается как 12200000.

Пробел после заполнителя цифры указывает деление числа на 1000. Например, если вы используете код формата «# ###, 0», число 12200000 отображается в Excel как 12200.0.

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

Разделитель тысяч может использоваться в числовом, денежном и финансовом форматах.

Ниже приведены примеры стандартных числовых форматов (только разделитель тысяч и десятичные разряды), валюты и финансовых показателей. В формате валюты вы можете добавить желаемый символ валюты, и значения будут выровнены с ним. В финансовом формате символ рубля расположен в ячейке справа от значения (если вы выберете символ доллара США, эти символы будут выровнены по левому краю ячеек, а значения — по правому краю). Обратите внимание на разницу между кодами валюты и финансового формата: в финансовых форматах звездочка (*) используется для отделения символа валюты от значения (*).

Чтобы получить код формата для определенной валюты, сначала нажмите CTRL + 1 (на Mac, +1) и выберите нужный формат, затем выберите символ в раскрывающемся списке «Обозначение.

Затем в разделе «Числовые форматы» слева выберите элемент (все форматы) и скопируйте код формата вместе с символом валюты.

Примечание. Функция ТЕКСТ не поддерживает форматирование цвета. Если вы скопируете код формата, в котором используется цвет, в диалоговое окно «Формат ячеек», например «# ## $ 0.00; [Красный] # ## $ 0.00», функция ТЕКСТ примет его, но цвет не будет отображаться.

вы можете изменить способ отображения дат, используя комбинацию «D» (для дня), «M» (для месяца) и «Y» (для года).

В функции ТЕКСТ коды формата не используются ни в коем случае, поэтому разрешены символы «M» и «m», «D» и «d», «Г» и «г».

Если вы делитесь файлами и отчетами Excel с людьми из разных стран, они, скорее всего, вам понадобятся на разных языках. Минда Трейси, MVP по Excel, предлагает отличное решение этой проблемы в своей статье «Просмотр дат в Excel на разных языках» (на английском языке). В нем также есть образец книги, который вы можете скачать.

Режим отображения времени можно изменить, используя комбинации символов «H» (для часов), «M» (для минут) и «S» (для секунд). Кроме того, «AM / PM» может использоваться для представления времени в 12-часовом формате».

Если символы «AM / PM» не указаны, время будет отображаться в 24-часовом формате.

Функция ТЕКСТ использует коды формата без учета регистра, тогда используются символы «H» и «H», «M» и «M», «C» и «S», «AM / PM» и «am / pm» разрешается».

Форматы процентов (%) могут использоваться для отображения десятичных значений.

Десятичные числа могут отображаться как дроби с использованием кодов формата, таких как «?/?».

Экспоненциальная запись — это способ отображения значения в виде десятичного числа от 1 до 10, умноженного на 10 до некоторой степени. Этот формат часто используется для лаконичного отображения больших чисел.

В Excel есть четыре дополнительных формата:

«Почтовый индекс» («00000»);

«Индекс + 4» («00000-0000»);

Типичный сценарий

Функция ТЕКСТ редко используется отдельно, но чаще в сочетании с чем-то еще. Предположим, мы хотим объединить текст и числовое значение, например, чтобы получить строку «Отчет, напечатанный 14 марта 2012 года» или «Еженедельный доход: 66 348,72 доллара США». Вы можете ввести такие строки вручную, но суть в том, что Excel может сделать это за вас. К сожалению, когда вы комбинируете форматированный текст и числа, такие как даты, время, денежные суммы и т.д., excel удаляет форматирование, поскольку неизвестно, в какой форме их отображать. Здесь может пригодиться функция ТЕКСТ, потому что с ее помощью вы можете принудительно форматировать числа, указав желаемый код формата, например «ДД.ММ.ГГГГ» для дат.

В следующем примере показано, что происходит, когда вы пытаетесь объединить текст и число без использования функции ТЕКСТ. Мы используем амперсанд (&) для объединения текстовой строки, пробела («») и значения: = A2 & «» & B2.

Вы можете видеть, что значение даты, взятое из ячейки B2, не отформатировано. В следующем примере показано, как применить желаемое форматирование с помощью функции ТЕКСТ.

Вот обновленная формула:

ячейка C2: = A2 & «» & ТЕКСТ (B2; «дд.мм.гггг») — формат даты.

Добавить комментарий

Ваш адрес email не будет опубликован.