その他

コピペで使える便利なスプレッドシート関数まとめ

今回は、WordPressなどとは関係のないテーマなのですが、WEB関連の仕事をしていると日常的にスプレッドシートを扱うことが多く、関数をよく調べます。でも、なかなかの割合で説明が多くてコピペできない記事が多かったので、自分が参考にしたいと思う記事を作ってしまおうというわけでこの記事を作りました。

個人的には関数の細かい説明とかそこまで要らないので、関数のサンプルちょうだいって思います。だから、前置きはこのくらいにしてどんどん関数を紹介していきますよ。随時、更新もしていきます。

セル範囲の指定方法

同タブ
[セル1]:[セル2]
別タブ
'[シート名]'![セル1]:[セル2]

順番の並べ替え

=SORT([セルの範囲],[基準となる列番号],[順番])

[順番]のところは昇順(TRUE)・降順(FALSE)のいずれかを指定します。

VLOOKUP関数

=IFERROR(VLOOKUP([検索値],[探して表示させる範囲],[表示させる列番号],false),"")

VLOOKUP関数は、IFERRORとセットで使用したいですね。

VLOOKUP関数の範囲よりも左の列を表示させる

VLOOKUP関数の弱点は、指定した範囲の一番左の列を基準に右の列を表示するものなので、指定した範囲よりも左の列を表示させることができないという点です。それを解決するのが、次のINDEX関数とMATCH関数の組み合わせです。

=INDEX([表示させる列],MATCH([検索値],[検索値を探す列],0),1)

条件分岐:if関数

条件1つ
=if([条件],[真の場合],[偽の場合])
条件が2つ
=if([条件1],[真1],if([条件2],[真2],[偽2]))
条件が3つ
=if([条件1],[真1],if([条件2],[真2],if([条件2],[真2],[偽2])))

同じ関数を複数行に反映:ARRAYFORMULA関数

これ知らない方、絶対知っておいた方が良いです。今まで複数行に記入していた関数がこれを一番上の行に入れるだけでその下に同じ関数を効かせることができます。

=ARRAYFORMULA([関数を範囲で指定])
連番の自動表示

一般的に一番上に見出しが入り、2行目から連番を入れたいことが多いと思いますので、そういうときはA2セルに下記の関数を入れます。

=ARRAYFORMULA(ROW(A2:A)-1)

目的のデータを抽出(同シート):QUERY関数

同じシート内の別タブからデータを抽出する場合と、別シートからデータを抽出する場合では書き方が異なります。気をつけるべき違いとしては、列の指定が、「同シートの場合はアルファベット」、「別シートの場合は数字」という違いがあります。まずは、同じシート内で抽出する場合の書き方です。

基本形
=QUERY([セルの範囲], " [条件1] [条件2] [条件3] ")

条件を複数指定する場合は、条件と条件の間を半角スペースで区切ります。

表示させたい列を指定(アルファベット)
=QUERY([セルの範囲], " select [列1],[列2] ")

列はアルファベットで指定します。列を複数指定する場合はカンマ区切りにします。

特定の値だけを抽出:where
=QUERY([セルの範囲], " where [列1] = [条件1] ")

列はアルファベットで指定します。これは「列1」の値が「条件1」のデータのみを抽出という意味になります。

whereの値の指定をセルで行いたい場合
=QUERY([セルの範囲], " where [列1] = '"&[セル1]&"' ")

抽出したい値のところに値を直書きしないでセルに表示された値で指定する方法です。

whereの値の指定を日付のセルで行いたい場合

指定するセルが日付のときは少し複雑になります。

=QUERY([セルの範囲], " where [列1] = date '"&TEXT([セル1],"YYYY-MM-DD")&"' ")
orを使う場合
=QUERY([セルの範囲], " where [列1] = [条件1] or [列2] = [条件2] ")

orを使って複数の条件を入れる場合はこのようにします。

andを使う場合
=QUERY([セルの範囲], " where [列1] = [条件1] and [列2] = [条件2] ")

andを使って複数の条件を入れる場合はこのようにします。

順番を指定:order by
=QUERY([セルの範囲], "order by [列1] [順番]")

抽出するデータを並べ替えて表示させたいときは、order by 列1 順番という形になります。「順番」のところには、昇順(ASC)・降順(DESC)のいずれかを指定します。指定する列は、参照元の列です。表示させた先の列を指定することはできません。

昇順(ASC)で空白行が上に来る不具合対応

上記の方法で昇順(ASC)を指定した場合、空白の行が上に来てしまうという不具合があります。その場合、次のようにします。

=SORT(QUERY([セルの範囲],"where 列1 !=''"),[列番号],[順番])

「列1」のところには空白の判断基準となる列のアルファベットを指定、[列番号]のところは並び替えの基準となる列を番号で指定。[順番]のところは昇順(TRUE)・降順(FALSE)のいずれかを指定します。

順番をセルで指定:order by
order by "&[セル1]&" "&[セル2]&"

基準となる列や昇順・降順をセルで参照して指定したい場合はこのような書き方をします。

複数のタブの情報を統合させる

下記のように関数を記入すると複数のタブから抽出したデータを統合させることができます。

=query({ query([セルの範囲1]); query([セルの範囲]2)})

データを抽出(別シート):IMPORTRANGE関数

こちらは、別シートからデータを抽出する場合の書き方です。同じシートの別タブからデータ抽出する場合とは異なりますので、気をつけてください。特に注意する点は、別シートから参照する場合、列の指定がアルファベットではなく列の番号(数字)になります。

基本形
=IMPORTRANGE("[スプレッドシートキー]", "[セルの範囲]")

スプレッドシートキーというのは、下記の部分です。

https://docs.google.com/spreadsheets/d/スプレッドシートキー/edit

ひとつ注意点として、"[セルの範囲]""の中に半角スペースがあるとうまく機能しないので気をつけてください。

スペースの入った悪い例
=IMPORTRANGE("[スプレッドシートキー]", " [セルの範囲] ")

そして、ここから先は、QUERY関数とIMPORTRANGE関数を組み合わせていきます。QUERY関数の基本形[セルの範囲]の部分をIMPORTRANGE関数に置き換えます。

QUERYとIMPORTRANGEの組み合わせ基本形
=QUERY(IMPORTRANGE("[スプレッドシートキー]", "[セルの範囲]"), " [条件1] [条件2] [条件3] ")
表示させたい列を指定(数字)
=QUERY(IMPORTRANGE("[スプレッドシートキー]", "[セルの範囲]"), " select [列1],[列2] ")

列はCol1,Col2,Col3という形で指定します。列を複数指定する場合はカンマ区切りにします。

特定の値だけを抽出:where
=QUERY(IMPORTRANGE("[スプレッドシートキー]", "[セルの範囲]"), " where [列1] = [条件1] ")

列は番号で指定します。これは「列1」の値が「条件1」のデータのみを抽出という意味になります。

whereの値の指定をセルで行いたい場合
=QUERY(IMPORTRANGE("[スプレッドシートキー]", "[セルの範囲]"), " where [列1] = '"&[セル3]&"' ")

抽出したい値のところに値を直書きしないでセルに表示された値で指定する方法です。

orを使う場合:where
=QUERY(IMPORTRANGE("[スプレッドシートキー]", "[セルの範囲]"), " where [列1] = [条件1] or [列2] = [条件2] ")

orを使って複数の条件を入れる場合はこのようにします。

andを使う場合:where
=QUERY(IMPORTRANGE("[スプレッドシートキー]", "[セルの範囲]"), " where [列1] = [条件1] and [列2] = [条件2] ")

andを使って複数の条件を入れる場合はこのようにします。

順番を指定:order by
=QUERY(IMPORTRANGE("[スプレッドシートキー]", "[セルの範囲]"), "order by [列1] [順番]")

抽出するデータを並べ替えて表示させたいときは、order by 列1 順番という形になります。「順番」のところには、昇順(ASC)・降順(DESC)のいずれかを指定します。指定する列は、参照元の列を数字で指定します。表示させた先の列を指定することはできません。

縦横の入れ替え:TRANSPOSE関数

=TRANSPOSE([シート名]'![セル1]:[セル2])

重複する値を削除する:=UNIQUE関数

=UNIQUE([セルの範囲])

まとめ

この記事は、自分用のマニュアルという意味も含めて新しい関数をどんどん追加していこうと思っています。もしよかったらコピペでご利用ください。

最近の記事

  1. CSS

    擬似クラス「:first-child」「:last-child」はどうして効かな…
  2. CSS

    CSS|中央寄せが効かないときは・・・
  3. CSS

    レスポンシブウェブデザインの基本|メディアクリエリの設定
  4. CSS

    要素の配置が簡単に決まるFlexboxの使い方
  5. WordPress

    WordPressの立ち上げからサイト構築までの初期手順
PAGE TOP