@SaintLoV

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

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

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

Без проверки по дате, сделать можно через
=ИНДЕКС('Специалисты'!$A$2:$D;ПОИСКПОЗ (B2;'Специалисты'!$A$2:$A;0);2)
Можно было через ВПР, но он почему-то в некоторый случаях выдавал пустой результат в основной моей исходной
таблице
  • Вопрос задан
  • 257 просмотров
Пригласить эксперта
Ответы на вопрос 3
@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) ")

Единственно вопрос, будет ли она грузить страницу сильно или нет, если вставлять ее в каждую клетку.
Ответ написан
ProgrammerForever
@ProgrammerForever Куратор тега Google Sheets
Учитель, автоэлектрик, программист, музыкант
Делайте ВПР по комбо-условию, что-то вроде:
=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 - что подставляем в вывод
Если ВПР не ищет, проверяйте на пробелы лишние, или используйте СЖПРОБЕЛЫ(), чаще всего ошибка в этом (если данные вводятся вручную, а не выбором из списка)
Ответ написан
@P1lK111p
Псевдо-аналитик
В твоей же таблице примерной прописал формулу:
=ЕСЛИ(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))

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

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы
28 февр. 2024, в 19:21
300 руб./за проект
28 февр. 2024, в 19:10
2000 руб./за проект
28 февр. 2024, в 19:09
35000 руб./за проект