Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Поиск значений из таблицы, которые соответствуют условиям заданным в ячейках
 
#1
09.11.2017 11:56:21
Добрый день! Прошу помощи с созданием формулы поиска

Имеется Лист-1 (на нем все данные)
Лист-2 то куда эти данные должны добавляться

В примере:
ячейки выделенные фиолетовым - данные на основании которых нужно искать значение на Лист1
ячейки выделенные оранжевым - то где эти данные нужно получить
ячейки выделенные зеленым -пример того что должно получится

Самое простое что пришло в голову это на Лист1 добавить столбец (сцепить B1+F1) и на Лист2 через ИНДЕКС/ПОИСКПОЗ получать нужные значения, но хотелось бы обойтись без добавления этого столбца.
К сожалению с формулами массива не очень дружу...
 
 
 
#2
09.11.2017 12:03:35
Вот, через функцию просмотр....
Изменено: a.i.mershik - 09.11.2017 12:17:09
Не бойтесь совершенства. Вам его не достичь.
 
 
 
#3
09.11.2017 12:22:47
Формула массива для "R7". Завершайте ввод формулы не клавишей Enter (как Вы это обычно делаете), а сочетанием клавиш Ctrl+Shift+Enter.
=ГПР(R$5;Путевки[#Все];ПОИСКПОЗ(ИСТИНА;ЕСЛИ(Путевки[Дата]=$N$4;Путевки[Автомобиль]=$H7);0)+1;0)
Изменено: Karataev - 09.11.2017 14:22:11
 
 
 
#4
09.11.2017 12:41:52
Формула массива (Ctrl+Shift+Enter):
=СУММ(ЕСЛИ((Путевки[[Дата]:[Дата]]=$N$4)*(Путевки[[Автомобиль]:[Автомобиль]]=$H9)*(Путевки[[#Заголовки];[О ДО]:[Т П]]=R$5);Путевки[[О ДО]:[Т П]]))
Или просто формула:
=СУММПРОИЗВ((Путевки[[Дата]:[Дата]]=$N$4)*(Путевки[[Автомобиль]:[Автомобиль]]=$H7)*(Путевки[[#Заголовки];[О ДО]:[Т П]]=AN$5)*(Путевки[[О ДО]:[Т П]]))
Такие штуки, как Путевки[[Дата]:[Дата]] - это для того, чтобы при копировании названия столбцов в smart-таблице "Путевки" не менялись.
Только при изменении даты надо менять ссылку с $N$4 на $Y$4 и т.д. Попробую доработать формулу.
Изменено: Wanschh - 09.11.2017 12:42:16
 
 
 
#5
09.11.2017 13:09:31
a.i.mershik, Wanschh, Karataev, спасибо за ответы!
Все предложенные варианты работают так как нужно.

Для себя использую вариант от a.i.mershik, единственное убрал $ перед $N$4 чтобы можно было скопировать формулу в ячейки справа и не нужно было каждый раз менять адрес ячейки с датой.
 
 
 
#6
24.11.2017 10:25:44
И снова прошу помощи у Вас.
Возможно ли изменить формулу для тех случаев когда в один день авто выезжало дважды(трижды, более 3х раз не бывает), в примере  2.11.17 авто М002ЕМ 32RUS, соответственно на Лист2 нужно вывести "Т ДО" - самого первого выезда за этот день(первое найденое сверху, т.к. таблица всегда отсортирована) "Т П" - самого последнего выезда за этот день, и "1+2" - сумма значений всех выездов в этот день

В примере:
фиолетовым - данные на основании которых нужно искать значение на Лист1
оранжевым - то где эти данные нужно получить
зеленым - то что получается при использовании предложенных ранее вариантов (все идеально если все авто выезжают 1 раз в день)
бирюзовым - то что нужно получить

Соответственно новый пример прилагаю)
 
 
 
Страницы: 1
Наверх