Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
Help us understand the problem. What is going on with this article?

意外と知らないスプレッドシートによる範囲結合と小ネタ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%になったら色を変えるなど、応用も可能です。

さいごに

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

ids-kinoue
ソフトウェアテストの業界に住む猫。
ids
イマジカデジタルスケープの「QAサポートサービス」では、高品質なデバッグ・検証と、それに関わるサービスを実現し、お客様の「ものづくり」をより確かなものにします。
https://dc-qas.dsp.co.jp/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away