7
Help us understand the problem. What are the problem?

More than 1 year has passed since last update.

意外と知らないスプレッドシートによる範囲結合と小ネタ5選

はじめに

意外と知られていないスプレッドシートのセル範囲の結合を使った例を5つ紹介します。
スマホを持つ猫

スプレッドシートの関数では、{範囲,範囲}横方向{範囲;範囲}縦方向にセルの範囲を結合することができます。公式ドキュメントは以下です。

Google スプレッドシートで配列を使用する
https://support.google.com/docs/answer/6208276?hl=ja

縦方向に結合する

スクリーンショット 2021-01-06 14.14.09.png
={A2:A4;B2:B4}

2列の値を1列にすることができました。

横方向に結合する

スクリーンショット 2021-01-06 14.12.18.png
={A2:A4,B5;B7}

1列の値を2列にすることができました。今回はこの配列結合を組み合わせた応用例を紹介します。

1.VLOOKUPの検索値より左の値を返す

スクリーンショット 2021-01-06 10.38.20.png
=VLOOKUP(E2,{C2:C4,B2:B4},2,false)

通常VLOOKUPは範囲の検索値より、右方向の値しか取り出せませんが、配列の結合を用いることで関数の中で入れ替えを行えます。{品名,値段}の配列を作ることで、品名を元に値段を取り出せます。

スクリーンショット 2021-01-06 10.38.38.png
=FILTER(B2:B4,C2:C4=E2)
なお、このケースはフィルタ関数でも代用可能です。フィルタの場合、検索値に一致する要素が複数ある場合、結果が配列で取り出せます。

2.複数ある表からVLOOKUPで値を返す

スクリーンショット 2021-01-06 11.08.59.png
=VLOOKUP(I2,{C2:C4,B2:B4;G2:G4,F2:F4},2,false)

{
表Aの品名,表Aの値段;
表Bの品名,表Bの値段
}

セミコロンで繋ぐことで、縦方向に結合して1つの表として、VLOOKUPの範囲にすることができます。これは月毎に表が別のシートにある場合などのケースで活用できます。

3.今日の日付のセルへジャンプする

スクリーンショット 2021-01-06 11.36.10.png
=HYPERLINK("#gid=944597197&range="&"A"&VLOOKUP(TODAY(),{A2:A,ARRAYFORMULA(ROW(A2:A))},2,false), "今日の日付のセルへ")

配列結合を使った応用例です。
HYPERLINKが入ったセルをクリックするだけで、今日の日付が入ったA列にジャンプする関数です。

gid=にシートid、range=にセル番地を入れます。

今日の日付のセル番地はARRAYFORMULA関数と行関数ROWを組み合わせて配列を作り、{日付,行番号}の配列を作っています。

4.値が含まれている最終行を取得する

スクリーンショット 2021-01-06 14.50.59.png

=MAX(ARRAYFORMULA(FILTER(ROW(A2:A),A2:A<>"")))

FILTER関数で値が空白ではない、行番号を返し、MAXでその行の最終行を取得しています。列も同様の方法で取得可能です。空白を無視した最大行はROWS関数だけで取得できます。

5.進捗ゲージぽいものを表示する方法

スクリーンショット 2021-01-06 12.16.13.png

=SPARKLINE({A2,1},{"charttype","bar";"max",1;"min",0;"color1","#5468ff";"color2","#bbb6b8"})

配列結合を応用し、color2にグラフの最大値を結合することで、背景色ぽく使用できます。進捗が100%になったら色を変えるなど、応用も可能です。

さいごに

今回は配列の結合を使った応用例を紹介しました。まだまだ、色々な活用方法があるかもしれません。この記事がお役に立てれば幸いです。

Register as a new user and use Qiita more conveniently

  1. You can follow users and tags
  2. you can stock useful information
  3. You can make editorial suggestions for articles
What you can do with signing up
7
Help us understand the problem. What are the problem?