今回は、WordPressなどとは関係のないテーマなのですが、WEB関連の仕事をしていると日常的にスプレッドシートを扱うことが多く、関数をよく調べます。でも、なかなかの割合で説明が多くてコピペできない記事が多かったので、自分が参考にしたいと思う記事を作ってしまおうというわけでこの記事を作りました。
個人的には関数の細かい説明とかそこまで要らないので、関数のサンプルちょうだいって思います。だから、前置きはこのくらいにしてどんどん関数を紹介していきますよ。随時、更新もしていきます。
コンテンツ
セル範囲の指定方法
同タブ
[セル１]:[セル２]
別タブ
'[シート名]'![セル１]:[セル２]
順番の並べ替え
=SORT([セルの範囲],[基準となる列番号],[順番])
[順番]のところは昇順(TRUE)・降順(FALSE)のいずれかを指定します。
VLOOKUP関数
=IFERROR(VLOOKUP([検索値],[探して表示させる範囲],[表示させる列番号],false),"")
VLOOKUP関数は、IFERRORとセットで使用したいですね。
VLOOKUP関数の範囲よりも左の列を表示させる
VLOOKUP関数の弱点は、指定した範囲の一番左の列を基準に右の列を表示するものなので、指定した範囲よりも左の列を表示させることができないという点です。それを解決するのが、次のINDEX関数とMATCH関数の組み合わせです。
=INDEX([表示させる列],MATCH([検索値],[検索値を探す列],0),1)
条件分岐：if関数
条件１つ
=if([条件],[真の場合],[偽の場合])
条件が２つ
=if([条件１],[真１],if([条件２],[真２],[偽２]))
条件が３つ
=if([条件１],[真１],if([条件２],[真２],if([条件２],[真２],[偽２])))
同じ関数を複数行に反映：ARRAYFORMULA関数
これ知らない方、絶対知っておいた方が良いです。今まで複数行に記入していた関数がこれを一番上の行に入れるだけでその下に同じ関数を効かせることができます。
=ARRAYFORMULA([関数を範囲で指定])
連番の自動表示
一般的に一番上に見出しが入り、２行目から連番を入れたいことが多いと思いますので、そういうときはA2セルに下記の関数を入れます。
=ARRAYFORMULA(ROW(A2:A)-1)
目的のデータを抽出（同シート）：QUERY関数
同じシート内の別タブからデータを抽出する場合と、別シートからデータを抽出する場合では書き方が異なります。気をつけるべき違いとしては、列の指定が、「同シートの場合はアルファベット」、「別シートの場合は数字」という違いがあります。まずは、同じシート内で抽出する場合の書き方です。
基本形
=QUERY([セルの範囲], " [条件１] [条件２] [条件３] ")
条件を複数指定する場合は、条件と条件の間を半角スペースで区切ります。
表示させたい列を指定（アルファベット）
=QUERY([セルの範囲], " select [列１],[列２] ")
列はアルファベットで指定します。列を複数指定する場合はカンマ区切りにします。
特定の値だけを抽出：where
=QUERY([セルの範囲], " where [列１] = [条件１] ")
列はアルファベットで指定します。これは「列１」の値が「条件１」のデータのみを抽出という意味になります。
whereの値の指定をセルで行いたい場合
=QUERY([セルの範囲], " where [列１] = '"&[セル1]&"' ")
抽出したい値のところに値を直書きしないでセルに表示された値で指定する方法です。
whereの値の指定を日付のセルで行いたい場合
指定するセルが日付のときは少し複雑になります。
=QUERY([セルの範囲], " where [列１] = date '"&TEXT([セル1],"YYYY-MM-DD")&"' ")
orを使う場合
=QUERY([セルの範囲], " where [列１] = [条件１] or [列２] = [条件２] ")
orを使って複数の条件を入れる場合はこのようにします。
andを使う場合
=QUERY([セルの範囲], " where [列１] = [条件１] and [列２] = [条件２] ")
andを使って複数の条件を入れる場合はこのようにします。
順番を指定：order by
=QUERY([セルの範囲], "order by [列１] [順番]")
抽出するデータを並べ替えて表示させたいときは、order by 列１ 順番という形になります。「順番」のところには、昇順(ASC)・降順(DESC)のいずれかを指定します。指定する列は、参照元の列です。表示させた先の列を指定することはできません。
昇順（ASC）で空白行が上に来る不具合対応
上記の方法で昇順（ASC）を指定した場合、空白の行が上に来てしまうという不具合があります。その場合、次のようにします。
=SORT(QUERY([セルの範囲],"where 列1 !=''"),[列番号],[順番])
「列1」のところには空白の判断基準となる列のアルファベットを指定、[列番号]のところは並び替えの基準となる列を番号で指定。[順番]のところは昇順(TRUE)・降順(FALSE)のいずれかを指定します。
順番をセルで指定：order by
order by "&[セル１]&" "&[セル２]&"
基準となる列や昇順・降順をセルで参照して指定したい場合はこのような書き方をします。
複数のタブの情報を統合させる
下記のように関数を記入すると複数のタブから抽出したデータを統合させることができます。
=query({ query([セルの範囲１]); query([セルの範囲]２)})
データを抽出（別シート）：IMPORTRANGE関数
こちらは、別シートからデータを抽出する場合の書き方です。同じシートの別タブからデータ抽出する場合とは異なりますので、気をつけてください。特に注意する点は、別シートから参照する場合、列の指定がアルファベットではなく列の番号（数字）になります。
基本形
=IMPORTRANGE("[スプレッドシートキー]", "[セルの範囲]")
スプレッドシートキーというのは、下記の部分です。
https://docs.google.com/spreadsheets/d/スプレッドシートキー/edit
ひとつ注意点として、"[セルの範囲]"の"の中に半角スペースがあるとうまく機能しないので気をつけてください。
スペースの入った悪い例
=IMPORTRANGE("[スプレッドシートキー]", " [セルの範囲] ")
そして、ここから先は、QUERY関数とIMPORTRANGE関数を組み合わせていきます。QUERY関数の基本形[セルの範囲]の部分をIMPORTRANGE関数に置き換えます。
QUERYとIMPORTRANGEの組み合わせ基本形
=QUERY(IMPORTRANGE("[スプレッドシートキー]", "[セルの範囲]"), " [条件１] [条件２] [条件３] ")
表示させたい列を指定（数字）
=QUERY(IMPORTRANGE("[スプレッドシートキー]", "[セルの範囲]"), " select [列１],[列２] ")
列はCol1,Col2,Col3という形で指定します。列を複数指定する場合はカンマ区切りにします。
特定の値だけを抽出：where
=QUERY(IMPORTRANGE("[スプレッドシートキー]", "[セルの範囲]"), " where [列１] = [条件１] ")
列は番号で指定します。これは「列１」の値が「条件１」のデータのみを抽出という意味になります。
whereの値の指定をセルで行いたい場合
=QUERY(IMPORTRANGE("[スプレッドシートキー]", "[セルの範囲]"), " where [列１] = '"&[セル３]&"' ")
抽出したい値のところに値を直書きしないでセルに表示された値で指定する方法です。
orを使う場合：where
=QUERY(IMPORTRANGE("[スプレッドシートキー]", "[セルの範囲]"), " where [列１] = [条件１] or [列２] = [条件２] ")
orを使って複数の条件を入れる場合はこのようにします。
andを使う場合：where
=QUERY(IMPORTRANGE("[スプレッドシートキー]", "[セルの範囲]"), " where [列１] = [条件１] and [列２] = [条件２] ")
andを使って複数の条件を入れる場合はこのようにします。
順番を指定：order by
=QUERY(IMPORTRANGE("[スプレッドシートキー]", "[セルの範囲]"), "order by [列１] [順番]")
抽出するデータを並べ替えて表示させたいときは、order by 列１ 順番という形になります。「順番」のところには、昇順(ASC)・降順(DESC)のいずれかを指定します。指定する列は、参照元の列を数字で指定します。表示させた先の列を指定することはできません。
縦横の入れ替え：TRANSPOSE関数
=TRANSPOSE([シート名]'![セル１]:[セル２])
重複する値を削除する：=UNIQUE関数
=UNIQUE([セルの範囲])
まとめ
この記事は、自分用のマニュアルという意味も含めて新しい関数をどんどん追加していこうと思っています。もしよかったらコピペでご利用ください。
