Найти значение по условиям в диапазоне ячеек

Автор Максим Арцимович, 08.08.2016, 16:30

« назад - далее »

Максим Арцимович

Добрый день! Помогите пожалуйста с формулой.Нужно получить значение ячейки, из желтого диапазона, в которой первой выполняется одно из условий: значение в столбце С  больше А1, значение в столбце D  меньше В1

cheshiki1

#1
почему у вас результат 2 написан? (или это просто так)
проверяйте, формула массива
=ПОИСКПОЗ(1;(C2:C7>A1)*(D2:D7<B1);)
в версии 2007 и выше есть ф-ция СЧЕТЕСЛИМН

Максим Арцимович

результат 2 написан потому что, 2 первым выполнено условие -значение в столбце D  меньше В1. Наверно я задачу не понятно написал.. :(

cheshiki1

=ИНДЕКС($C$2:$D$7;МИН(ЕСЛИ((C2:C7>A1)+(D2:D7<B1);СТРОКА(C1:C6);""));ЕСЛИ(МИН(ЕСЛИ(C2:C7>A1;СТРОКА(C1:C6);""))>МИН(ЕСЛИ(D2:D7<B1;СТРОКА(C1:C6);""));2;1))
формула массива

Максим Арцимович

Спасибо огромное ), теперь все в порядке. Если не сложно, объясните, для чего  используется СТРОКА(C1:C6)

cheshiki1

привязка условий к номеру строки в которой оно выполнилось.
ЕСЛИ((C2:C7>A1)+(D2:D7<B1);СТРОКА(C1:C6);"")=ЕСЛИ(({ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ИСТИНА})+(D2:D7<B1);СТРОКА(C1:C6);"")=ЕСЛИ(({ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ИСТИНА})+({ЛОЖЬ:ЛОЖЬ:ИСТИНА:ИСТИНА:ИСТИНА:ЛОЖЬ});СТРОКА(C1:C6);"")=ЕСЛИ({0:0:1:1:2:1};СТРОКА(C1:C6);"")=ЕСЛИ({0:0:1:1:2:1};{1:2:3:4:5:6};"")={"":"":3:4:5:6}
расписал как пошагово работает часть формулы.

Максим Арцимович

спасибо. А если диапазон поиска, сдвинется вниз( типа C3:D8 ) или сузится (типа C5:D7) как изменять СТРОКА(C1:C6), относительно нового диапазона?, больше ничего менять не нужно в формуле -верно?... или это я не корректно спрашиваю?

cheshiki1

кол-во строк должно равняться к-ву строк в таблице и обязательно с первой строки.
C5:D7=3строки соответственно СТРОКА(C1:C3) (буква здесь не важна), если формула будет растягиваться по ячейкам то диапазон нужно закрепить (читаем про абсолютные и относительные ссылки).

Максим Арцимович

перебирал значения -вот вариант, где не правильно работает формула :(. В диапазоне будут  только числа, положительные

vikttur

формула массива:
=ИНДЕКС(C2:C7;МИН(ЕСЛИ(C2:C7>A1;ЕСЛИ(D2:D7<B1;СТРОКА(C2:C7)-1))))
В Ваших данных нет подходящего числа.

Максим Арцимович

#10
Вы смотрели файл Z_123_1 ? там одно решение в D6 =2, наверно я  с условиями не точен? Нужно получить значение ячейки, из желтого диапазона, в которой первой выполняется  ЛЮБОЕ из условий
значение в столбце С  больше А1
значение в столбце D  меньше В1

vikttur

Предыдущая объединяет условия.
Для варианта "любое из условий" формула массива:
=ИНДЕКС(C2:D7;МИН(ЕСЛИ((C2:C7>A1)+(D2:D7<B1);СТРОКА(C2:C7)-1));(B1>ИНДЕКС(D2:D7;МИН(ЕСЛИ((C2:C7>A1)+(D2:D7<B1);СТРОКА(C2:C7)-1))))+1)
Как видно, определение строки повторяется два раза. Для оптимизации эту часть лучше вынести в отдельную ячейку.
Сравните с предыдущей формулой. формула массива (ФМ):
=МИН(ЕСЛИ((C2:C7>A1)+(D2:D7<B1);СТРОКА(C2:C7)-1))
Обычная:
=ИНДЕКС(C2:D7;ФМ;(B1>ИНДЕКС(D2:D7;ФМ))+1)
ФМ - ссылка на ячейку с ФМ