はじめに
意外と知られていないスプレッドシートのセル範囲の結合を使った例を5つ紹介します。
スプレッドシートの関数では、{範囲,範囲}で横方向、{範囲;範囲}で縦方向にセルの範囲を結合することができます。公式ドキュメントは以下です。
Google スプレッドシートで配列を使用する
https://support.google.com/docs/answer/6208276?hl=ja
縦方向に結合する
={A2:A4;B2:B4}
2列の値を1列にすることができました。
横方向に結合する
={A2:A4,B5;B7}
1列の値を2列にすることができました。今回はこの配列結合を組み合わせた応用例を紹介します。
1.VLOOKUPの検索値より左の値を返す
=VLOOKUP(E2,{C2:C4,B2:B4},2,false)
通常VLOOKUPは範囲の検索値より、右方向の値しか取り出せませんが、配列の結合を用いることで関数の中で入れ替えを行えます。{品名,値段}の配列を作ることで、品名を元に値段を取り出せます。
=FILTER(B2:B4,C2:C4=E2)
なお、このケースはフィルタ関数でも代用可能です。フィルタの場合、検索値に一致する要素が複数ある場合、結果が配列で取り出せます。
2.複数ある表からVLOOKUPで値を返す
=VLOOKUP(I2,{C2:C4,B2:B4;G2:G4,F2:F4},2,false)
{
表Aの品名,表Aの値段;
表Bの品名,表Bの値段
}
セミコロンで繋ぐことで、縦方向に結合して1つの表として、VLOOKUPの範囲にすることができます。これは月毎に表が別のシートにある場合などのケースで活用できます。
3.今日の日付のセルへジャンプする
=HYPERLINK("#gid=944597197&range="&"A"&VLOOKUP(TODAY(),{A2:A,ARRAYFORMULA(ROW(A2:A))},2,false), "今日の日付のセルへ")
配列結合を使った応用例です。
HYPERLINKが入ったセルをクリックするだけで、今日の日付が入ったA列にジャンプする関数です。
gid=にシートid、range=にセル番地を入れます。
今日の日付のセル番地はARRAYFORMULA関数と行関数ROWを組み合わせて配列を作り、{日付,行番号}の配列を作っています。
4.値が含まれている最終行を取得する
=MAX(ARRAYFORMULA(FILTER(ROW(A2:A),A2:A<>"")))
FILTER関数で値が空白ではない、行番号を返し、MAXでその行の最終行を取得しています。列も同様の方法で取得可能です。空白を無視した最大行はROWS関数だけで取得できます。
5.進捗ゲージぽいものを表示する方法
=SPARKLINE({A2,1},{"charttype","bar";"max",1;"min",0;"color1","#5468ff";"color2","#bbb6b8"})
配列結合を応用し、color2にグラフの最大値を結合することで、背景色ぽく使用できます。進捗が100%になったら色を変えるなど、応用も可能です。
さいごに
今回は配列の結合を使った応用例を紹介しました。まだまだ、色々な活用方法があるかもしれません。この記事がお役に立てれば幸いです。