image

В этой статье мы узнаем, как выделять числа с помощью символов в Excel.

Сценарий:

Иногда дается список чисел, и нам нужно выделить номера данных на основе некоторых критериев. Например: нам нужно выделить числа, меньшие или равные некоторому значению. Но проблема возникает, если данные имеют приблизительные результаты. В данном примере нам нужно выделить, сколько продаж сделано, которые больше или равны 50000, в данных, где значения продаж отмечены символами типа «> 60000». Итак, нам нужна формула для данных, которая выделяет числа и значения соответствия символов.

Как решить проблему?

Для этого мы будем использовать link: / conditional-formatting-conditional-formatting-with-formula-2016 [Условное форматирование], очевидно, и некоторые функции Excel, перечисленные ниже.

  1. link: / tips-if-condition-in-excel [IF function]

  2. link: / logic-formulas-the-isnumber-function-in-excel [ISNUMBER function]

  3. link: / working-with-formulas-left-function-in-microsoft-excel [LEFT function]

  4. link: / excel-text-formulas-the-mid-function-in-excel [Функция MID]

  5. link: / excel-text-formulas-len-function-in-excel [LEN function]

Комбинация формулы сначала проверяет, является ли значение числом или нет. Если число соответствует критериям, возвращается True или False. Но если значение является числом с оператором, то формула находит оператор (<) и сопоставляет оставшееся значение (число) с критериями. Ниже приводится общая формула для сопоставления значения, меньшего или равного критерию.

Общая формула:

=IF(

ISNUMBER

(Cell) ,Cell < criteria ,

IF(

LEFT

(Cell) = «<«

,(MID

(Cell , 2 ,

LEN

(Cell) ) + 0) < criteria ) )

ячейка: ссылка на ячейку для проверки критериев: значение критерия для соответствия с оператором <: * для соответствия критериям

Пример:

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

image

Перейдите на главную страницу → Условное форматирование → Новое правило. Перед вами появится диалоговое окно. Выберите «Использовать формулу, чтобы определить, какие ячейки нужно форматировать» → введите приведенную ниже формулу в поле «Значения формата», где эта формула имеет значение True: менее 50000.

=IF(

ISNUMBER

(C2) ,C2 < $G$3 ,

IF(

LEFT

(C2) = «<«

,(MID

(C2 , 2 ,

LEN

(C2 ) + 0) < $G$3 ) )

Пояснение:

  1. Первая функция ЕСЛИ проверяет, является ли значение числом или нет, с помощью функции ЕЧИСЛО.

  2. Если значение является числом, то число напрямую сопоставляется с критериями и возвращает True или False.

  3. Но если значением является число с оператором, то сначала мы извлекаем оператор и сопоставляем его с оператором «меньше чем» («<«), а затем оставшееся число — это число, которое соответствует критериям и возвращает True или False.

  4. link: / excel-text-formulas-the-mid-function-in-excel [MID]

(C2, 2, link: / excel-text-formulas-len-function-in-excel [LEN]

(C2) + 0), здесь функция MID возвращает значение, игнорируя оператор (char)

и возвращает значение в числовом формате с использованием (+0).

image

Как вы можете видеть в формуле, желтый фон — это выделенные и необходимые данные. Вы можете проверить формулу в ячейке образца, чтобы убедиться, что значение возвращает True или False. Теперь я добавлю еще одно правило, чтобы выделить население, превышающее 80000. Создайте новое правило в условном форматировании и используйте приведенную ниже формулу, чтобы выделить числа на зеленом фоне.

Используйте формулу * для сопоставления значения менее 50000.

=IF(

ISNUMBER

(C2) ,C2 > $G$4 ,

IF(

LEFT

(C2) = «>»

,(MID

(C2 , 2 ,

LEN

(C2 ) + 0) > $G$4 ) )

image

Как видите, условное форматирование делает все за вас.

Вот все заметки по использованию формулы.

Примечания:

  1. Условное форматирование позволяет применять основу форматирования к значениям ячеек, таким как цвета, значки и гистограммы.

  2. Формула работает как для текста, так и для чисел.

Надеюсь, вы поняли, как выделять числа символами в Excel.

Дополнительные статьи о выделении ячеек формулами в Excel можно найти здесь. Если вам понравились наши блоги, поделитесь ими с друзьями на Facebook. А также вы можете подписаться на нас в Twitter и Facebook. Мы будем рады услышать от вас, дайте нам знать, как мы можем улучшить, дополнить или усовершенствовать нашу работу и сделать ее лучше для вас. Напишите нам на [email protected].

Статьи по теме:

link: / lookup-formulas-find-the-partial-match-number-from-data-in-excel [Найти частичное совпадение-число из данных в Excel]: найти подстроку, совпадающую со значениями ячеек, используя формулу в Excel.

link: / conditional-formatting-highlight-cells-that-contain-specific-text [Как выделить ячейки, содержащие определенный текст в Excel]: выделить ячейки на основе формулы, чтобы найти конкретное текстовое значение в ячейке в Excel .

link: / conditional-formatting-conditional-formatting-based-on-another-cell-value [Условное форматирование на основе другого значения ячейки в Excel]: форматирование ячеек в Excel на основе состояния другой ячейки с использованием некоторых критериев в Excel .

link: / tips-conditional-formatting-with-if-statement [Функция ЕСЛИ и условное форматирование в Excel]: Как использовать условие ЕСЛИ в условном форматировании с формулой в Excel. link: / conditional-formatting-conditional-formatting-with-formula-2016 [Выполнить условное форматирование с формулой 2016]: изучите все функции условного форматирования по умолчанию в Excel.

link: / formating-in-vba-conditonal-formatting-using-microsoft-excel-vba [Условное форматирование с использованием VBA в Microsoft Excel]: выделение ячеек в VBA на основе кода в Excel.

Популярные статьи:

link: / keyboard-formula-shortcuts-50-excel-shortcuts-to-Повышение-продуктивность [50 ярлыков Excel для повышения производительности]: быстрее справляйтесь с задачей. Эти 50 ярлыков заставят вас работать в Excel еще быстрее.

link: / formulas-and-functions-Introduction-of-vlookup-function [Как использовать функцию ВПР в Excel]: Это одна из наиболее часто используемых и популярных функций Excel, которая используется для поиска значений из разных диапазонов и листы.

link: / tips-countif-in-microsoft-excel [Как использовать функцию СЧЁТЕСЛИ в Excel]: Подсчет значений с условиями с помощью этой удивительной функции. Вам не нужно фильтровать данные для подсчета определенных значений. Функция Countif важна для подготовки вашей приборной панели.

link: / excel-formula-and-function-excel-sumif-function [Как использовать функцию СУММЕСЛИ в Excel]: Это еще одна важная функция информационной панели. Это поможет вам суммировать значения для конкретных условий.