XLOOKUPをさらに拡張

Google Sheets関数: XLOOKUP_MAP

Google Sheetsでデータの検索を行う際、VLOOKUPやXLOOKUPのような関数は非常に便利です。このXLOOKUP_MAP関数は、これらの関数をさらに拡張して、複数のキーを同時に検索し、その結果を返すことができます。

なぜXLOOKUP_MAPが必要なのか？

XLOOKUPはVLOOKUPを置き換える非常に役立つ関数ですが、一度に複数のキーを検索することはできません。XLOOKUP_MAPは、この制約を克服して複数のキーを検索し、その結果を返す機能を提供します。

対象読者

• Google Sheetsを使って簡単なデータ処理をしたい
• lambda関数、let関数が大体分かる

パラメーター

• key_range：検索するキーの範囲。
• search_range：キーを検索する範囲。
• result_range：結果を返す範囲。

例

=XLOOKUP_MAP(A1:A3,B1:B3,B1:C3)

ソース

	A	B	C
1	A-1	A-2	C-1
2	A-2	A-3	C-2
3	A-3	A-1	C-3

結果

	A	B
1	A-1	C-3
2	A-2	C-1
3	A-3	C-2

実装

=lambda(
  key_range,
  search_range,
  result_range,
  let(
    map_x,lambda(
      range,
      mapper,
      let(
        col_func, lambda(
          col,
          reduce(
            {},
            sequence(rows(col)),
            lambda(
              stack,
              i,
              let(
                cell,chooseRows(col,i),
                curData,mapper(cell),
                if(i=1,curData,IfError(vStack(stack,curData)))
              )
            )
          )
        ),
        reduce(
          {},
          sequence(1,columns(range)),
          lambda(
            stack,
            i,
            let(
              col,chooseCols(range,i),
              curData,col_func(col),
              if(i=1,curData,IfError(hStack(stack,curData)))
            )
          )
        )
      )
    ),
    map_x(
      key_range,
      lambda(x,
        xLookup(
          x,
          search_range,
          result_range
        )
      )
    )
  )
)

上記のコードは、XLOOKUP_MAP関数の実装を示しています。名前付き関数として登録する場合には、key_range、search_range、result_rangeをパラメータとして登録後、以下を関数本体として登録してください。

=let(
    map_x,lambda(
      range,
      mapper,
      let(
        col_func, lambda(
          col,
          reduce(
            {},
            sequence(rows(col)),
            lambda(
              stack,
              i,
              let(
                cell,chooseRows(col,i),
                curData,mapper(cell),
                if(i=1,curData,IfError(vStack(stack,curData)))
              )
            )
          )
        ),
        reduce(
          {},
          sequence(1,columns(range)),
          lambda(
            stack,
            i,
            let(
              col,chooseCols(range,i),
              curData,col_func(col),
              if(i=1,curData,IfError(hStack(stack,curData)))
            )
          )
        )
      )
    ),
    map_x(
      key_range,
      lambda(x,
        xLookup(
          x,
          search_range,
          result_range
        )
      )
    )
  )

解説

まずはx_mapを実装します。x_mapは組み込みのMAP関数と似ていますがMap関数と違い、縦横複数列の結果を返すことができます。これはmapper関数の結果を組み込みのVSTACK関数・HSTACK関数を使って積み上げることによって実現しています。

そのmap_x関数を使用して、各キーに対してXLOOKUPを実行することで、複数のキー(縦横)に対応しています。