Таблицы Google: возвращает имена сотрудников в списке, разделенном запятыми; отфильтровано по сезонному местоположению и ранжировано по старшинству

У меня есть обширная база данных сотрудников Google Sheet; Имя (ColA), дата доступности (ColB, их имя появляется один раз {ColA} для каждой даты {ColB}, когда они доступны), сезонное местоположение< /strong> (E-H), база знаний о названиях поездок по регионам (I-O) и старшинство (столбец D).

У меня также есть списки поездок, разделенные на сезоны (вкладки «Зимние/весенние поездки») с конкретными днями каждой поездки, расположенными в соответствующей строке. Эти поездки могут быть выполнены только сотрудником, который находится в месте поездки в течение этого сезона и имеет его в своей базе знаний. Пример рабочей тетради.

В идеале я хотел бы, чтобы листы отображали «доступный персонал» в одном столбце (ColL на каждой вкладке «Сезонные поездки») с сотрудниками, у которых есть задача, указанная в их базе знаний, и область, указанная в их сезонном местоположении, ранжированная по старшинству (5, 4, 3, 2, 1). Есть также вкладки «Лето/Осень», которые я добавлю, если будет способ сделать все это возможным.


google-sheets google-sheets-formula google-query-language array-formulas textjoin
person Ken Parker    schedule 08.11.2020    source источник

Ответы (1)


arrow_upward
0
arrow_downward

вставьте в B2 и перетащите вниз:

=ARRAYFORMULA(IFERROR(TEXTJOIN(", ", 1, UNIQUE(QUERY(TO_TEXT('Staff availability'!A:D), 
 "select Col1 
  where Col2 matches '"&TEXTJOIN("|", 1, TO_DATE(VLOOKUP(A2, 
 {'Winter Trips'!A:K; 'Spring Trips'!A:K}, SEQUENCE(1, 10, 2), 0)))&"' 
  order by Col4 desc", 0))), "no one"))

введите здесь описание изображения

Обновить:

=ARRAYFORMULA(IFERROR(TEXTJOIN(", ", 1, UNIQUE(QUERY(
 SUBSTITUTE(QUERY(TRIM(IFERROR(SPLIT(FLATTEN(IF('Staff availability'!E$2:O="",,TRANSPOSE(QUERY(TRANSPOSE({"♦"&'Staff availability'!A$2:A, "♦♠"&TO_TEXT('Staff availability'!B$2:B), "♦"&'Staff availability'!C$2:D}),,9^9))&"♦"&'Staff availability'!E$2:O)), "♦"))), 
 "where Col5 = '"&QUERY(FILTER(INDIRECT("B1:B"&ROW()), INDIRECT("A1:A"&ROW())="-"), "offset "&COUNTA(FILTER(INDIRECT("B1:B"&ROW()), INDIRECT("A1:A"&ROW())="-"))-1)&"' 
     or Col5 contains '"&TRIM(A4)&"'"), "♠", ), 
 "select Col1 
  where Col2 matches '"&TEXTJOIN("|", 1, TO_DATE(VLOOKUP(A4, {'Winter Trips'!A:K; 'Spring Trips'!A:K}, SEQUENCE(1, 10, 2), 0)))&"' 
  order by Col4 desc", 0))), "no one"))

0

person player0    schedule 08.11.2020
comment
не знаю, но теперь я не могу увидеть (и отладить) Шейн, потому что ты переписал его на Staff-0000 xD - person player0; 10.11.2020
comment
Итак, Шейн указан там, потому что он указан в событии Флагстафф. см. Лист 8, например, это сезонное местоположение (E-H) - person player0; 10.11.2020