エクセル関数応用
可変長配列をVSTACKする4つの方法
REDUCE・Thunk・チャンク・再帰分割

=LET(
  input, A2:B5000,
  DROP(
    REDUCE(0, SEQUENCE(ROWS(input)), LAMBDA(stack, i,
      LET(
        str, INDEX(input, i, 1) & "",
        cat, INDEX(input, i, 2) & "",
        items, IFERROR(TEXTSPLIT(str, , ",", TRUE), ""),
        VSTACK(
          stack,
          HSTACK(
            items,
            EXPAND(cat, ROWS(items), 1, cat)
          )
        )
      )
    )),
    1
  )
)

=LET(
  input, A2:B5000,
  make_thunk, LAMBDA(r,
    LET(
      str, INDEX(r, 1) & "",
      cat, INDEX(r, 2) & "",
      items, IFERROR(TEXTSPLIT(str, , ",", TRUE), ""),
      LAMBDA(
        HSTACK(
          items,
          EXPAND(cat, ROWS(items), 1, cat)
        )
      )
    )
  ),
  thunks, BYROW(input, make_thunk),
  DROP(
    REDUCE(0, thunks, LAMBDA(stack, th, VSTACK(stack, th()))),
    1
  )
)

=LET(
  input, A2:B5000,
  n, ROWS(input),
  b_size, 100,
  b_count, ROUNDUP(n / b_size, 0),

  process_row, LAMBDA(r,
    LET(
      str, INDEX(r, 1) & "",
      cat, INDEX(r, 2) & "",
      items, IFERROR(TEXTSPLIT(str, , ",", TRUE), ""),
      HSTACK(
        items,
        EXPAND(cat, ROWS(items), 1, cat)
      )
    )
  ),

  process_block, LAMBDA(b_idx,
    LET(
      start_row, (b_idx - 1) * b_size + 1,
      block_rows, MIN(b_size, n - start_row + 1),
      sub_input, CHOOSEROWS(input, SEQUENCE(block_rows, 1, start_row)),
      DROP(
        REDUCE(0, SEQUENCE(block_rows), LAMBDA(stack, i,
          VSTACK(stack, process_row(CHOOSEROWS(sub_input, i)))
        )),
        1
      )
    )
  ),

  thunks, MAP(SEQUENCE(b_count), LAMBDA(i, LAMBDA(process_block(i)))),

  DROP(
    REDUCE(0, thunks, LAMBDA(stack, th, VSTACK(stack, th()))),
    1
  )
)

=LET(
  input, A2:B5000,

  BYROWEX, LAMBDA(f,
    LET(
      fx, LAMBDA(x, f(LAMBDA(varray, func, x(x)(varray, func)))),
      fx(fx)
    )
  )(
    LAMBDA(BYROWEX,
      LAMBDA(varray, func,
        LET(
          n, ROWS(varray),
          IF(
            n = 1,
            func(varray),
            LET(
              q, QUOTIENT(n, 2),
              VSTACK(
                BYROWEX(TAKE(varray, q), func),
                BYROWEX(DROP(varray, q), func)
              )
            )
          )
        )
      )
    )
  ),

  BYROWEX(
    input,
    LAMBDA(r,
      LET(
        str, INDEX(r, 1) & "",
        cat, INDEX(r, 2) & "",
        items, IFERROR(TEXTSPLIT(str, , ",", TRUE), ""),
        HSTACK(
          items,
          IF(SEQUENCE(ROWS(items)), cat)
        )
      )
    )
  )
)

# --- 設定 ---
show_header = False  # 結果に見出しを出力する場合は True、出力しない場合は False

# A2:B5000 の範囲を取得（1列目:データ, 2列目:カテゴリ）
# ※セル範囲 A2:B5000 自体には見出しを含めないため headers=False
df = xl("A2:B5000", headers=False)
df.columns = ["データ", "カテゴリ"]

# カンマで分割して縦方向に展開
df["データ"] = df["データ"].astype(str).str.split(",")
result = df.explode("データ")[["データ", "カテゴリ"]]

# 設定に応じた出力切り替え
result if show_header else result.values