Перейти к основному содержанию

Как выделить ближайшее к заданному числу в списке значение в Excel?

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

документ выделить ближайшее значение 1
документ выделить ближайшее значение 2

Выделите ближайшие или ближайшие n значений к заданному числу с помощью условного форматирования


стрелка синий правый пузырь Выделите ближайшие или ближайшие n значений к заданному числу с помощью условного форматирования

Чтобы выделить ближайшее значение на основе данного числа, сделайте следующее:

1. Выберите список номеров, который вы хотите выделить, и нажмите Главная > Условное форматирование > Новое правило, см. снимок экрана:

документ выделить ближайшее значение 3

2. В Новое правило форматирования диалоговом окне выполните следующие операции:

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

(2.) В Формат значений, где эта формула истинна текстовое поле, введите эту формулу: =ABS(A2-$D$2)=MIN(ABS($A$2:$A$15-$D$2)) (A2 это первая ячейка в вашем списке данных, D2 это число, которое вы будете сравнивать, A2: A15 - это список номеров, из которого вы хотите выделить ближайшее значение.)

документ выделить ближайшее значение 4

3. Затем нажмите Формат кнопка, чтобы перейти Формат ячеек диалоговое окно под Заполнять на вкладке выберите один понравившийся цвет, см. снимок экрана:

документ выделить ближайшее значение 5

4, Затем нажмите OK > OK чтобы закрыть диалоги, ближайшее значение к определенному числу было выделено сразу, см. снимок экрана:

документ выделить ближайшее значение 6

Советы: Если вы хотите выделить 3 ближайших значения к заданным значениям, вы можете применить эту формулу в Условное форматирование, =ISNUMBER(MATCH(ABS($D$2-A2),SMALL(ABS($D$2-$A$2:$A$15),ROW($1:$3)),0)), см. снимок экрана:

документ выделить ближайшее значение 7

Внимание: В приведенной выше формуле: A2 это первая ячейка в вашем списке данных, D2 это число, которое вы будете сравнивать, A2: A15 список номеров, из которого вы хотите выделить ближайшее значение, $ 1: $ 3 указывает, что будут выделены три ближайших значения. Вы можете изменить их по своему усмотрению.

Лучшие инструменты для офисной работы

🤖 Kutools AI Помощник: Революционный анализ данных на основе: Интеллектуальное исполнение   |  Генерировать код  |  Создание пользовательских формул  |  Анализ данных и создание диаграмм  |  Вызов функций Kutools...
Популярные опции: Найдите, выделите или определите дубликаты   |  Удалить пустые строки   |  Объедините столбцы или ячейки без потери данных   |   Раунд без формулы ...
Супер поиск: Множественный критерий VLookup    VLookup с несколькими значениями  |   VLookup по нескольким листам   |   Нечеткий поиск ....
Расширенный раскрывающийся список: Быстрое создание раскрывающегося списка   |  Зависимый раскрывающийся список   |  Выпадающий список с множественным выбором ....
Менеджер столбцов: Добавить определенное количество столбцов  |  Переместить столбцы  |  Переключить статус видимости скрытых столбцов  |  Сравнить диапазоны и столбцы ...
Рекомендуемые функции: Сетка Фокус   |  Просмотр дизайна   |   Большой Формулный Бар    Менеджер книг и листов   |  Библиотека ресурсов (Авто текст)   |  Выбор даты   |  Комбинировать листы   |  Шифровать/дешифровать ячейки    Отправлять электронные письма по списку   |  Суперфильтр   |   Специальный фильтр (фильтровать жирным шрифтом/курсивом/зачеркиванием...) ...
15 лучших наборов инструментов12 Текст Инструменты (Добавить текст, Удалить символы, ...)   |   50+ График Тип (Диаграмма Ганта, ...)   |   40+ Практических Формулы (Рассчитать возраст по дню рождения, ...)   |   19 Вносимые Инструменты (Вставить QR-код, Вставить изображение из пути, ...)   |   12 Конверсия Инструменты (Числа в слова, Конверсия валюты, ...)   |   7 Слияние и разделение Инструменты (Расширенные ряды комбинирования, Разделить клетки, ...)   |   ... и более

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

Описание


Вкладка Office: интерфейс с вкладками в Office и упрощение работы

  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
  • Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
  • Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
Comments (8)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
=ABS(A2-$D$2)=MIN(ABS($A$2:$A$15-$D$2)) данная формула не работает. Пробовал exel 2010 и 2013. Если использовать вторую формулу, ругается на ошибку.
This comment was minimized by the moderator on the site
Hello friend,

Sorry to hear that. But I did try the two formulas and they both work perfectly. Please see the screenshots.
Here is the tip. Please make sure you choose the right range, in my case here, it is A2:A15. Then click Home > Conditional Formatting to set up the rules. Please have a try. If the problem still remains, please provide me with more details so I can help you. Have a nice day.

Sincerely,
Mandy
This comment was minimized by the moderator on the site
Hi, das hilft mir bereits sehr weiter, danke!Gibt es aber auch eine Möglichkeit, die Formel aus Schritt 2 ohne "$"-Zeichen zu verwenden? Das würde das Kopieren der bedingten Formatierung erleichtern...
This comment was minimized by the moderator on the site
What if data is over column and not in a row?
This comment was minimized by the moderator on the site
This is useful, but how would I do the following please. Text is in Col A, the number range is in Col B: when the average has been found using the above method and is thus highlighted, I need the corresponding cell in Col A to also highlight.

Thank you
This comment was minimized by the moderator on the site
Were you able to figure this out? I would also like to know about expanding the format to the remaining row.
This comment was minimized by the moderator on the site
Hi! I am very thankfull to u for this as this solved my problem for a presentation to a client. However I am looking for something more for my future presentations. like when u say highlight 3 closest(n) values i want to highlight them as: 1) the most closest with Green. 2) the least closest in red and the 3) value which is between most and least closest in yellow. Can u please help me to achieve this. As per the example my d2 value is live updating every minute so i want to know where exactly my d2 value lies on scale of a range(eg. Number list). This will add a brand finish to my presentations. Point to note i am using excel 2007 as many of my clients are still using the same. Just to avoid any compatibility issues i avoid latest versions. Thanks in advance and many regards. :)
This comment was minimized by the moderator on the site
An excellent resource for highlighting the closest value. Since the logic is same , won't it work in Google Sheets? Anyway it is not working in Google Sheets. Would be obliged if you could please clarify!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations