Google таблицы - перекрестное соединение / декартово соединение из двух отдельных столбцов

#google-sheets #google-sheets-formula #cartesian-product #cross-join

#google-таблицы #google-sheets-формула #Декартово произведение #перекрестное соединение

Вопрос:

Надеюсь, что кто-нибудь сможет помочь мне с декартовым произведением в Google Таблицах. У меня есть данные в двух отдельных столбцах, и я хочу создать все возможные комбинации этих двух столбцов на отдельной вкладке. Первый столбец — идентификатор (текст), а второй — формат даты. Вывод должен состоять из двух отдельных столбцов. Формула должна быть динамической, т. Е. Список должен обновляться при добавлении новых идентификаторов или дат во входные списки.

Я искал решения в Интернете, но не нашел решения, которое работает. Я хорошо разбираюсь в Excel, но не в Google Sheet 🙂

Вот пример листа: https://docs.google.com/spreadsheets/d/150uIg3XH1hxZa8vSxDhcVZVOcSEOp175OPYL4Rc-wWI/edit?usp=sharing

Ответ №1:

Используйте это:

 =ARRAYFORMULA(
  SPLIT(
    FLATTEN(
        FILTER('input 1'!A2:A, 'input 1'!A2:A <> "")
      amp; ","
      amp; TRANSPOSE(FILTER('input 2'!A2:A, 'input 2'!A2:A <> ""))
    ),
    ","
  )
)

в ячейке A3 , но убедитесь, что вы переформатировали столбец B в date (скопируйте формат из столбца E):

1. Спасибо, это именно то, что мне было нужно. В настоящее время существует 10 идентификаторов («ввод»! A2: A11) и 12 месяцев («ввод 2»! A2: A13). Знаете ли вы, возможно ли сделать формулу «динамической», чтобы, если я добавлю новый идентификатор, формула автоматически определяла и изменялась на «ввод»! A2: A12″? Спасибо!

2. @WilliamPontoppidan немного исправил формулу с FILTER поддержкой открытых диапазонов, чтобы сделать ее динамической.

Ответ №2:

Я думаю, что решение soMarios лучше в вашем случае, но вот другое решение, просто чтобы показать другой подход. При некоторой работе этот подход может быть обобщен для любого количества столбцов.

 =ARRAYFORMULA(
  {
    VLOOKUP(
      INT(SEQUENCE(COUNTA('input 1'!A2:A) * COUNTA('input 2'!A2:A), 1,) / COUNTA('input 2'!A2:A))   1,
      {SEQUENCE(COUNTA('input 1'!A2:A)), FILTER('input 1'!A2:A, 'input 1'!A2:A <> "")},
      2,
    ),
    VLOOKUP(
      MOD(SEQUENCE(COUNTA('input 1'!A2:A) * COUNTA('input 2'!A2:A), 1,), COUNTA('input 2'!A2:A))   1,
      {SEQUENCE(COUNTA('input 2'!A2:A)), FILTER('input 2'!A2:A, 'input 2'!A2:A <> "")},
      2,
    )
  }
)

Ответ №3:

попробуй:

 =INDEX(SPLIT(FLATTEN(
 OFFSET('input 1'!A2,,,COUNTA('input 1'!A2:A))amp;"♠"amp;TRANSPOSE(
 OFFSET('input 2'!A2,,,COUNTA('input 2'!A2:A)))), "♠"))

и форматируйте столбец B как дату