Блог

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

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

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

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

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