Выделить ближайшее значение ячейки в таблице
У меня есть таблица данных о зарплате SalaryDataTbl и справа от нее селектор, где вы можете выбрать сотрудника в ячейке K2. L2 ищет их дисциплину, а M2 ищет их зарплату.
Есть ли способ выделить ближайшую зарплату для своей дисциплины в SalaryDataTbl, используя функцию в ячейке или проверку данных? Или для этого потребуется решение VBA?
В примере на изображении для зарплаты 3750 и дисциплины программирование следует выделить ячейку G2.
РЕДАКТИРОВАТЬ Новые данные образца, демонстрирующие ошибку, когда зарплата составляет 65000 и используется формула =AND(ABS(D2-$M$2)=MIN(ABS(DROP(D:I, 1)-$M$2)), $A2=$L$2)
Ответы 1
Выделение ячеек на основе значений других ячеек будет использовать условное форматирование. Для этого ответа вам понадобится Excel 2021 или Excel для Office 365. Выберите диапазон D2: I18, нажмите «Условное форматирование» > «Новое правило» на вкладке «Главная» и измените параметр на «Использовать формулу для определения форматируемых ячеек».
Формула, которую вам нужно использовать, будет
=AND(ABS(D2-$M$2)=MIN(ABS(DROP($D:$I, 1)-$M$2)), $A2=$L$2)
Затем вы можете использовать настройки форматирования, чтобы выбрать любой формат, который вам нужен, чтобы «выделить» соответствующие ячейки, например. меняем заливку на желтую.
Ограничивая падение и диапазон до D2: I500, он не блокируется и отлично работает.
Можно было бы работать намного быстрее, если бы вы поместили часть MIN(ABS(DROP(D:I,1)-$M$2)) в отдельную ячейку и указали ее в формуле — это та часть, которая требует больше всего для вычисления, а в условной формуле она пересчитывает это для каждой ячейки, которую проверяет, тогда как если вы поместите его в отдельную ячейку, он будет вычислять его только один раз для всего листа.
Если у меня одинаковая зарплата для нескольких уровней, она будет выделять оба. Если бы я перенес уровень сотрудника в N2, был бы способ включить его уровень в формулу?
Да, часть $A2=$L$2 делает это для дисциплины, вы можете добавить аналогичное условие для уровня.
Прежде чем я применил добавление уровня, я заметил ошибку в приведенной выше формуле. При определенных значениях заработной платы он не выделяет ячейки. Я создал образец данных, демонстрирующий проблему, и добавил его в конец вопроса. Когда я использую зарплату 65000, она не выделяет никаких ячеек. В этом примере я ожидаю, что он выделит H4 и G5. Если он включает уровень сотрудника, а в этом примере это старший, то он должен выделить H4. Вы знаете, что здесь может происходить?
Извиняюсь, я понял, что D: I должен быть абсолютной ссылкой, редактируя ответ.
Опять же, это не будет проблемой, если у вас есть отдельная ячейка (в моем случае я использовал ячейку O2), содержащая формулу =MIN(ABS(DROP(D:I, 1)-M2)). Затем условное форматирование может быть применено ко всему D:I, и правило форматирования просто =AND($A1=$L$2, ABS(D1-$M$2)=$O$2) - оно также вычисляется намного быстрее.
Я применил вышеуказанное к той же ячейке и изменил условное форматирование на то же самое, но, к сожалению, он по-прежнему не выделяет ни одну ячейку с образцами данных.
Нашел проблему. У меня есть больше строк данных, охватывающих все дисциплины, так что больше, чем в примере в вопросе выше. Если я удалю все эти строки, он выделит правильную ячейку.
Я скорректировал образцы данных в вопросе выше. При использовании с последними формулами проявляет ошибку.
У меня есть Office 365, и когда я добавляю новое правило, устанавливаю заливку зеленого цвета и применяю к =$D:$I, к сожалению, это ничего не делает. Я что-то не так настроил? При первой настройке он также на мгновение заблокировал мою машину во время вычислений.