Профессиональные приемы работы в Microsoft Excel

Обмен опытом => Microsoft Excel => Тема начата: Максим Арцимович от 08.08.2016, 16:30

Название: Найти значение по условиям в диапазоне ячеек
Отправлено: Максим Арцимович от 08.08.2016, 16:30
Добрый день! Помогите пожалуйста с формулой.Нужно получить значение ячейки, из желтого диапазона, в которой первой выполняется одно из условий: значение в столбце С  больше А1, значение в столбце D  меньше В1
Название: Re: Найти значение по условиям в диапазоне ячеек
Отправлено: cheshiki1 от 08.08.2016, 16:51
почему у вас результат 2 написан? (или это просто так)
проверяйте, формула массива
=ПОИСКПОЗ(1;(C2:C7>A1)*(D2:D7<B1);)
в версии 2007 и выше есть ф-ция СЧЕТЕСЛИМН
Название: Re: Найти значение по условиям в диапазоне ячеек
Отправлено: Максим Арцимович от 08.08.2016, 17:01
результат 2 написан потому что, 2 первым выполнено условие -значение в столбце D  меньше В1. Наверно я задачу не понятно написал.. :(
Название: Re: Найти значение по условиям в диапазоне ячеек
Отправлено: cheshiki1 от 08.08.2016, 17:31
=ИНДЕКС($C$2:$D$7;МИН(ЕСЛИ((C2:C7>A1)+(D2:D7<B1);СТРОКА(C1:C6);""));ЕСЛИ(МИН(ЕСЛИ(C2:C7>A1;СТРОКА(C1:C6);""))>МИН(ЕСЛИ(D2:D7<B1;СТРОКА(C1:C6);""));2;1))
формула массива
Название: Re: Найти значение по условиям в диапазоне ячеек
Отправлено: Максим Арцимович от 08.08.2016, 17:41
Спасибо огромное ), теперь все в порядке. Если не сложно, объясните, для чего  используется СТРОКА(C1:C6)
Название: Re: Найти значение по условиям в диапазоне ячеек
Отправлено: cheshiki1 от 08.08.2016, 17:58
привязка условий к номеру строки в которой оно выполнилось.
ЕСЛИ((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}
расписал как пошагово работает часть формулы.
Название: Re: Найти значение по условиям в диапазоне ячеек
Отправлено: Максим Арцимович от 08.08.2016, 18:19
спасибо. А если диапазон поиска, сдвинется вниз( типа C3:D8 ) или сузится (типа C5:D7) как изменять СТРОКА(C1:C6), относительно нового диапазона?, больше ничего менять не нужно в формуле -верно?... или это я не корректно спрашиваю?
Название: Re: Найти значение по условиям в диапазоне ячеек
Отправлено: cheshiki1 от 08.08.2016, 18:43
кол-во строк должно равняться к-ву строк в таблице и обязательно с первой строки.
C5:D7=3строки соответственно СТРОКА(C1:C3) (буква здесь не важна), если формула будет растягиваться по ячейкам то диапазон нужно закрепить (читаем про абсолютные и относительные ссылки).
Название: Re: Найти значение по условиям в диапазоне ячеек
Отправлено: Максим Арцимович от 08.08.2016, 18:55
перебирал значения -вот вариант, где не правильно работает формула :(. В диапазоне будут  только числа, положительные
Название: Re: Найти значение по условиям в диапазоне ячеек
Отправлено: vikttur от 08.08.2016, 20:28
формула массива:
=ИНДЕКС(C2:C7;МИН(ЕСЛИ(C2:C7>A1;ЕСЛИ(D2:D7<B1;СТРОКА(C2:C7)-1))))
В Ваших данных нет подходящего числа.
Название: Re: Найти значение по условиям в диапазоне ячеек
Отправлено: Максим Арцимович от 08.08.2016, 20:57
Вы смотрели файл Z_123_1 ? там одно решение в D6 =2, наверно я  с условиями не точен? Нужно получить значение ячейки, из желтого диапазона, в которой первой выполняется  ЛЮБОЕ из условий
значение в столбце С  больше А1
значение в столбце D  меньше В1
Название: Re: Найти значение по условиям в диапазоне ячеек
Отправлено: vikttur от 09.08.2016, 00:25
Предыдущая объединяет условия.
Для варианта "любое из условий" формула массива:
=ИНДЕКС(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)
ФМ - ссылка на ячейку с ФМ