Как проверить в Excel, находится ли значение в диапазоне?

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

Я имею в виду, конечно, как вы уже видели в заголовке, как проверить в Excel, находится ли значение в диапазоне. Или, если быть более точным, например, проверить, н количество значений находится в диапазоне, который может быть одинаковым для всех или переменным для каждого. Мы увидим оба случая, потому что, хотя формула практически похожа, есть небольшая, но важная деталь, которую мы должны учитывать в первом случае: абсолютные ссылки.

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

переменный диапазон

В этом первом случае мы хотим проверить, находится ли его текущее значение для каждого сотрудника между ранее определенным минимумом и максимумом (который также может быть изменен, если мы сочтем это необходимым). Таким образом, мы начинаем с такого документа:

Как вы увидите, у каждого сотрудника есть минимум и максимум, а также текущее значение и ячейка справа, в которую нам нужно будет ввести формулу, которая проверяет, находится ли текущий между ними. В случае первой строки с данными, 2 (первая строка содержит заголовки), нам нужно проверить, находится ли 149 между 150 и 375 (да, как индивидуальный расчет, это чрезвычайно просто, но помните, что мы говорим о функциях, позволяющих сразу проверить сотни или тысячи записей). Итак, мы хотим, чтобы D2 было больше, чем B2, и меньше, чем B3. И для этого мы собираемся использовать функцию И.

В частности, функция, которую нам нужно будет написать в ячейке E2, будет следующей: «=Y(D2>=B2; D2

Затем используйте функцию автозаполнения Excel, чтобы перенести формулу в остальные ячейки, где она вам нужна (если вы новичок в автозаполнении, просто поместите указатель мыши в правый нижний угол ячейки, которую вы только что отредактировали, и, когда она изменится его внешний вид, щелкните и перетащите, не отпуская, пока не будут выделены все ячейки). Это применит проверку ко всем строкам

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

фиксированный диапазон

Начнем теперь с аналогичного документа, но в котором минимальные и максимальные значения диапазона у всех одинаковые, например

В этом случае нам придется использовать формулу, аналогичную той, что была в предыдущем примере (изменив, конечно, ячейки происхождения текущего значения, минимума и максимума) и, кроме того, мы должны будем написать символ доллара, $, слева от буквы и слева от числа как в ячейке с минимальным значением, так и в ячейке с максимальным значением. То есть формула для этого примера с показанными на картинке ячейками будет такой: “=Y(B2>=$F$2; B2<$F$3)" (опять же, без кавычек).

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

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

Сегодня в диапазоне дат

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

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

Итак, предположим, мы начинаем с такого документа

Что ж, как я уже говорил, мы снова будем использовать функцию И, а также функцию СЕГОДНЯ(), которая всегда будет возвращать точную дату и время. И, как вы, наверное, уже догадались, мы собираемся изменить ссылку на текущее значение, которое будет не значением из таблицы, а информацией, предоставляемой функцией.

Таким образом, для распределения ячеек, показанного на изображении в качестве примера, формула, которую нам нужно будет ввести в D2, будет следующей: «=Y(СЕГОДНЯ()>=B2; СЕГОДНЯ()

Но, если мы откроем тот же документ через несколько дней, например 25 декабря, результат будет другим.

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

Дополнительная информация об Excel: Майкрософт