Довольно часто в Excel требуется определить, в какой из нескольких числовых интервалов попадает некоторое числовое значение. Это бывает нужно как в численных методах, так и в финансовых или естественнонаучных расчётах. Давайте решим эту задачу корректно, начав с простейшего примера. В ячейках A1:A4 набраны 4 числа, показывающие границы интервалов, например, 1; 2; 3; 4, разумеется, числа могут быть любыми другими, но они должны быть упорядочены по возрастанию, иначе потом не будет работать поиск. Сортировка в Excel делается элементарно (меню Данные, пункт Сортировка), так что выполнения этого условия нетрудно добиться.

В ячейке B1 набрано число, которое мы будем проверять на попадание в интервалы, например, значение 1,5 (полтора). Элементарно определить "карман", в который попадает числовое значение, можно с помощью стандартной функции ПОИСКПОЗ:

=ПОИСКПОЗ(B1;A1:A4;1)

Третий параметр функции здесь равен единице, это означает, что ПОИСКПОЗ находит наибольшее значение, которое меньше либо равно, чем искомое значение из ячейки B1, то есть, мы берём левые границы интервалов. В случае, если нужны правые границы, следует здесь и далее упорядочить данные по убыванию и везде ставить в ПОИСКПОЗ третий параметр, равный -1.

У кода минимум два недостатка - если величина из B1 меньше границы самого левого интервала, выведется системное сообщение #Н/Д ("не найдено"), кроме того, если искомая величина больше последней из границ интервала, Excel решит, что величина всё равно попадает в последний интервал, что неприемлемо.

Избавляемся от первого недостатка (теперь Excel пишет "Неверная точка" для слишком маленьких чисел):

=ЕСЛИ(ЕНД(ПОИСКПОЗ(B1;A1:A4;1))=ИСТИНА;"Неверная точка";ПОИСКПОЗ(B1;A1:A4;1))

Избавляемся от второго недостатка, предусмотрев случай, когда ПОИСКПОЗ дал последний интервал, но при этом значение в ячейке B1 больше, чем значение в последней ячейке интервала. В этом случае продублируется сообщение "Неверная точка":

=ЕСЛИ(ЕНД(ПОИСКПОЗ(B1;A1:A4;1))=ИСТИНА;"Неверная точка";
ЕСЛИ(И(ПОИСКПОЗ(B1;A1:A4;1)=ЧСТРОК(A1:A4);B1>A4);
"Неверная точка";ПОИСКПОЗ(B1;A1:A4;1)))

Формула здесь и далее показана с разрывом строки, вставить её в это не помешает.

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

=ЕСЛИ(ЕНД(ПОИСКПОЗ(B1;A1:A4;1))=ИСТИНА;
"Неверная точка";
ЕСЛИ(И(ПОИСКПОЗ(B1;A1:A4;1)=ЧСТРОК(A1:A4);
B1>ИНДЕКС(A1:A4;ЧСТРОК(A1:A4)));
"Неверная точка";
ПОИСКПОЗ(B1;A1:A4;1)))

Это работает вполне приемлемо.

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

Для того, чтобы определить количество числовых значений из диапазона A1:A10, находящихся, например, в интервале от 2,5 до 6,15 включительно, можно использовать любую из семи представленных ниже формул:

=СУММПРОИЗВ((A1:A10>=2,5)*(A1:A10<=6,15))
=СЧЁТЕСЛИ(A1:A10;"<=6,15")-СЧЁТЕСЛИ(A1:A10;"<2,5")
=СЧЁТЕСЛИ(A1:A10;">=2,5")-СЧЁТЕСЛИ(A1:A10;">6,15")
=СУММ((A1:A10>=2,5)*(A1:A10<=6,15))
=СЧЁТ(ЕСЛИ((A1:A10>=2,5)*(A1:A10<=6,15);A1:A10))
=СУММ(ЕСЛИ(A1:A10>=2,5;ЕСЛИ(A1:A10<=6,15;1;0);0))
=СЧЁТ(ЕСЛИ(A1:A10>=2,5;ЕСЛИ(A1:A10<=6,15;1;"");""))

Последние четыре формулы являются формулами массива, их ввод необходимо завершать комбинацией клавиш Ctrl+Shift+Enter.

 Скачать пример в Excel XP/2003 (14 Кб)

гостевая; E-mail