В тази статия ще научим как да подчертаваме числа със символи в Excel.
Сценарий:
Понякога се дава списък с числа и ние трябва да подчертаем числата на базата на някои критерии. Например: трябва да подчертаем числа, по -малки или равни на някаква стойност. Но проблемът възниква, ако данните имат приблизителни резултати. Като се даде пример, ние трябва да подчертаем колко са извършени продажби, които са по -големи или равни на 50000, в данни, където стойностите на продажбите са маркирани със символи като "> 60000". Така че се нуждаем от формула за данните, която подчертава числа и стойности за съвпадение на символи.
Как да решим проблема?
За това очевидно ще използваме условното форматиране и някои Excel функции, изброени по -долу.
- IF функция
- ISNUMBER функция
- НАЛЯВО функция
- MID функция
- LEN функция
Комбинацията от формулата първо проверява дали стойността е число или не. Ако числото отговаря на критериите, връща True или False. Но ако стойността е число с оператор, тогава формулата намира оператора (<) и съответства на останалата стойност (номер) с критериите. По -долу е общата формула, която да съответства на стойност, по -малка или равна на критериите.
Обща формула:
| = IF (ISNUMBER (Cell), Cell <критерии, IF (LEFT (Cell) = "<", (MID (Cell, 2, LEN (Cell)) + 0) <критерии)) |
клетка : препратка към клетка за проверка
критерии : стойност на критерия, с която да съответства
< : оператор да отговаря на критериите
Пример:
Всичко това може да е объркващо за разбиране. Нека разберем тази формула, като я изпълним на пример. Тук имаме данни с прогнозно население за всеки регион.
Тук, както можете да видите, данните имат стойности (числа с оператор), които също трябва да бъдат взети под внимание. Трябва да подчертаем клетки, които имат популация по -малка от 50000. Затова използваме формулата, посочена по -долу с Обяснение в полето Формула за условно форматиране.
Отидете на Начало -> Условно форматиране -> Ново правило
Появява се диалогов прозорец пред избора Използвайте формула, за да определите кои клетки да форматирате -> въведете формулата по -долу под Форматирайте стойности, където тази формула е True:
Използвайте формулата за съвпадаща стойност по -малка от 50000.
| = IF (ISNUMBER (C2), C2 <$ G $ 3, IF (НАЛЯВО (C2) = "<", (MID (C2, 2, LEN (C2) + 0) <$ G $ 3)) |
Обяснение:
- Първата функция IF проверява дали стойността е число или не използва функцията ISNUMBER.
- Ако стойността е число, тогава номерът е директно съчетан с критерии и връща True или False.
- Но ако стойността е числото с оператор, тогава първо извличаме оператора и го съпоставяме с по -малко от ("<") оператор, след което оставащото е числото, което да съвпада с критериите и връща True или False.
- MID (C2, 2, LEN (C2) + 0), тук функцията MID връща оператора за игнориране на стойността (char) и връща стойността в числов формат, използвайки (+0).
Както можете да видите формулата, жълтият фон е маркираните и необходимите данни. Можете да проверите формулата в примерна клетка, за да проверите дали стойността връща True или False. Сега ще добавя още едно правило, за да подчертая популацията, която е по -голяма от 80000. Създайте ново правило при условно форматиране и използвайте формулата по -долу, за да маркирате числата със зелен фон.
Използвайте формулата за съвпадаща стойност по -малка от 50000.
| = IF (ISNUMBER (C2), C2> $ G $ 4, IF (НАЛЯВО (C2) = ">", (MID (C2, 2, LEN (C2) + 0)> $ G $ 4)) |
Както можете да видите, условното форматиране прави цялото подчертаване вместо вас.
Ето всички забележки от наблюденията относно използването на формулата.
Бележки:
- Условното форматиране ви позволява да приложите основата за форматиране върху стойностите на клетките, като цветове, икони и ленти с данни.
- Формулата работи както за текст, така и за числа.
Надявам се, че сте разбрали Как да маркирате числа със символи в Excel. Разгледайте още статии за Excel, подчертаващи клетки с формули тук. Ако ви харесаха нашите блогове, споделете го с приятелите си във Facebook. Можете също така да ни следвате в Twitter и Facebook. Ще се радваме да чуем от вас, уведомете ни как можем да подобрим, допълним или обновим работата си и да я подобрим. Пишете ни на имейл сайта.
Намерете частичния номер на съвпадение от данни в Excel : намерете подниза, съответстваща на стойностите на клетката, като използвате формулата в Excel.
Как да маркирате клетки, които съдържат конкретен текст в Excel : Маркирайте клетки въз основа на формулата, за да намерите конкретната текстова стойност в клетката в Excel.
Условно форматиране въз основа на друга стойност на клетка в Excel : форматирайте клетки в Excel въз основа на състоянието на друга клетка, като използвате някои критерии в Excel.
IF функция и условно форматиране в Excel : Как да използвате условието IF при условно форматиране с формула в Excel.
Изпълнете условно форматиране с формула 2016 : Научете всички функции по подразбиране за условно форматиране в Excel.
Условно форматиране с помощта на VBA в Microsoft Excel : Маркирайте клетките във VBA въз основа на кода в Excel.
Популярни статии:
50 Преки пътища в Excel за повишаване на вашата производителност : По -бързо изпълнете задачата си. Тези 50 преки пътища ще ви накарат да работите още по -бързо в Excel.
Как да използвате функцията VLOOKUP в Excel : Това е една от най -използваните и популярни функции на excel, която се използва за търсене на стойност от различни диапазони и листове.
Как да използвате функцията COUNTIF в Excel : Пребройте стойностите с условия, използвайки тази невероятна функция. Не е необходимо да филтрирате данните си, за да броите конкретни стойности. Функцията Countif е от съществено значение за подготовката на вашето табло.
Как да използвате функцията SUMIF в Excel : Това е друга основна функция на таблото. Това ви помага да обобщите стойностите при конкретни условия.