Как сделать автоматическое заполнение по множественному условию и сравнению из другой таблицы?

Question

[SaintLoV]

Есть две таблицы. В одной дата сделки, фио спеца и фио РОПа(руковоителя). Вторая таблица справочная, в ней имя спеца, имя РОПа к кому спец относится, дата найма к этому РОПу, дата увольнение от него.
В первой таблице заполняется дата сделки, ФИО спеца. Нужно чтобы автоматически подставлялось ФИО РОПа по проверке имени из первой таблицы во второй, при этом проверять входит ли диапазон даты сделки в период, когда спец был принят и уволен.

примерную таблицу прикрепил
https://docs.google.com/spreadsheets/d/1yliKjQFQXa...

Без проверки по дате, сделать можно через
=ИНДЕКС('Специалисты'!$A$2:$D;ПОИСКПОЗ (B2;'Специалисты'!$A$2:$A;0);2)
Можно было через ВПР, но он почему-то в некоторый случаях выдавал пустой результат в основной моей исходной
таблице

Answer 1

[SaintLoV]

В принципе, вопрос решил через query

=QUERY('Специалисты'!$A$2:$D;"select B where A='"&B2&"' and C <= date '"&ТЕКСТ(A2;"yyyy-mm-dd")&"' and (D >= date '"&ТЕКСТ(A2;"yyyy-mm-dd")&"' or D is null) ")

Единственно вопрос, будет ли она грузить страницу сильно или нет, если вставлять ее в каждую клетку.

Comments

[Ринат]

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

[Ринат]

=ЕСЛИОШИБКА(filter('Специалисты'!B:B;
                    'Специалисты'!D:D=0;
                    'Специалисты'!A:A=B2;
                    'Специалисты'!C:C<=A2);
                    ЕСЛИ(filter('Специалисты'!D:D;'Специалисты'!A:A=B2)<>0;
                           filter('Специалисты'!B:B;
                                  'Специалисты'!A:A=B2;
                                  'Специалисты'!C:C<=A2;
                                  'Специалисты'!D:D>=A2);
                                  filter('Специалисты'!B:B;
                                         'Специалисты'!A:A=B2;
                                         'Специалисты'!C:C<=A2)))

Поправил свою формулу, теперь работает полноценно. В начале формулы добавил что бы в перспективе искал строки на листе специалисты в которых дата увольнения не заполнена. Крч 100% результат

Answer 2

[Григорий Боев]

Делайте ВПР по комбо-условию, что-то вроде:

=arrayformula(if(A:A="";;ВПР(A:A&B:B;{Справочник!A:A&Справочник!B:B\Справочник!C:C};2;0)))

, где A:A&B:B - 2 столбца из текущего листа значения из которых ищем
Справочник!A:A&Справочник!B:B - где ищем
Справочник!C:C - что подставляем в вывод
Если ВПР не ищет, проверяйте на пробелы лишние, или используйте СЖПРОБЕЛЫ(), чаще всего ошибка в этом (если данные вводятся вручную, а не выбором из списка)

Comments

[SaintLoV]

Благодарю за ответ. Насколько я понимаю, в вашей формуле идет точное сравнение. Дата сделки + спец, ищется совпадение в массиве из справочника. А там нужен промежуток по времени.

Answer 3

[Ринат]

В твоей же таблице примерной прописал формулу:

=ЕСЛИ(filter('Специалисты'!D:D;'Специалисты'!A:A=B2)<>0;
    filter('Специалисты'!B:B;
           'Специалисты'!A:A=B2;
           'Специалисты'!C:C<=A2;
           'Специалисты'!D:D>=A2);
    filter('Специалисты'!B:B;
           'Специалисты'!A:A=B2;
           'Специалисты'!C:C<=A2))

Формула работает в том порядке что ты описал. Надеюсь ты знаком с функцией фильтр. Не особо использую функцию впр, но возможно она была бы удобнее в том плане, что не нужно было бы вставлять формулу в каждую ячейку.

Опишу подробнее что делает формула:

ЕСЛИ(filter('Специалисты'!D:D;'Специалисты'!A:A=B2)<>0; //эта часть проверяет заполнена ли дата увольнения в диапазоне 'Специалисты'!D:D сверяя имя в диапазоне 'Специалисты'!A:A и имени нашего спеца в ячейке B2.

Если дата увольнения заполнена, значит сотрудник был уволен и нужно дополнительно сравнить дату сделки с датой увольнения, следующая часть сделает это:

filter('Специалисты'!B:B; //ищем имя ропа в диапазоне ('Специалисты'!B:B) исходя из условий ниже

'Специалисты'!A:A=B2; //находим строку в диапазоне фио ('Специалисты'!A:A) равную имени нашего спеца (B2)

'Специалисты'!C:C<=A2; //находим строку в диапазоне даты приёма ('Специалисты'!C:C) которая меньше даты нашей сделки (A2), соответственно если дата сделки будет меньше даты приёма, фильтр не выведет имя ропа.

'Специалисты'!D:D>=A2); //находим строку в диапазоне даты увольнения ('Специалисты'!d:d) которая больше даты нашей сделки (A2), соответственно если дата сделки будет больше даты увольнения, фильтр не выведет имя ропа.

Последняя часть формулы будет выполняться если дата увольнения по сотруднику не заполнена, раз дата увольнения у сотрудника не заполнена, значит он не уволен, и смысла нет проверять-сверять дату сделки и дату увольнения, поэтому повторяем прошлую часть формулы без проверки на дату увольнения:

filter('Специалисты'!B:B;
'Специалисты'!A:A=B2;
'Специалисты'!C:C<=A2))

По логике зачем нам нужна последняя частя формулы, но там возникает проблема без неё. Если не проверять заполнена дата увольнения или нет, то фильтр не сможет искать и сверять по дате увольнения, ведь в случае незаполненной даты увольнения, дата увольнения будет нулевой и соответственно меньше даты сделки, что логически быть не может.

Comments

[SaintLoV]

Благодарю за ответ!
В принципе все работает.
Единственно, спецов в теории у нас могут перевести от одного РОПа, к другому.
Для этого и стоит дата увольнения.
То есть принят 1 уволен 6 от ропа 1. Дата сделки 5. ищет. Принят 7 к ропу2 и дата сделки 13, не ищет.
Сейчас пройдусь по логике, может изменю.
Пробую еще через query, но тут вопрос, не будет ли грузить таблицу куча функций query.