スプレッドシートの関数一覧 エクセルとの違いと独自の関数を検索

エクセルで覚えた関数がスプレッドシートでも動作するかどうかは、大半が関数名で判断できます。この記事の早見表では、同じ名前の関数がもう一方にあるか、なければ何で代わりにするかを1行ずつ確認できます。

スプレッドシートの関数一覧を検索、エクセルとの違いも早見表で

スプレッドシートの関数は、早見表に関数名か用途の語を入れると、エクセルとの違いと一緒に1行で確認できます。 早見表は、Googleスプレッドシートの関数と、同じ働きをするエクセルの関数を横に並べた一覧です。

SUMIFやVLOOKUPなど、よく利用する関数は同名同用法

SUMIFやVLOOKUPなどの関数は、エクセルとスプレッドシートで名前も使い方も同じです。 早見表の列は、エクセル側の関数名・スプレッドシート側の関数名・用途の3つです。また、早見表の上の検索ボックスに「VLOOKUP」のような関数名か、「合計」「翻訳」のような用途の語を入れると、条件に一致する行だけが表示されます。

関数の早見表

用途の列は、GoogleとMicrosoftの公式ヘルプにある説明を短く書き直したものです。名前が同じでも引数が異なる関数には、その違いも書き添えました。たとえばCONCATは、エクセルでは複数の範囲や文字列を結合し、スプレッドシートでは2つの値だけを連結する、&演算子と同じ働きの関数です。

Googleの「関数リスト」の表には、515個の関数が並んでいます。ただし5関数は、個別の説明ページがあるのに「関数リスト」には載っていません。

該当するのは、XMATCH・BAHTTEXT・BINOM.DIST.RANGE・PERCENTIF・COUNTUNIQUEIFSの5つです。早見表には、この5つも入れました。

早見表の「—」はスプレッドシートかExcelだけの関数

早見表の「—」は、その行の関数がGoogleスプレッドシートかExcelのどちらか片方にしかないことを表します。 つまり、スプレッドシートの列が「—」ならエクセルだけの関数で、エクセルの列が「—」ならスプレッドシートだけの関数です。

同じ名前の関数がなくても、別の関数で同じことができる場合があります。エクセルのTEXTSPLITは、スプレッドシートのSPLITで書き換えることができます。代わりの関数がある行には、用途の列に「〜で代替する」か「〜が近い」と書きました。

スプレッドシートの基本関数はエクセルと同名で使える

エクセルにもある基本関数は、Googleスプレッドシートでも同じ名前のまま入力できます。 このあとは、事務でよく使う用途ごとに、そのまま入力できる数式の例を紹介します。

SUMIFやCOUNTIFで条件に合う数を集計

SUMIFは条件に合う行の数値だけを合計し、COUNTIFは条件に合う行の個数を求める関数です。 =SUMIF(B2:B20,"東京",C2:C20)と書くと、B列が「東京」の行についてだけC列を合計します。条件を複数にするならSUMIFSで、=SUMIFS(C2:C20,B2:B20,"東京",D2:D20,"成約")のように合計範囲を先頭に置きます。

SUMIFの構文は「範囲, 条件, 合計範囲」の順で、合計範囲を最後に置きます。SUMIFSは「合計範囲, 条件範囲1, 条件1」の順で、合計範囲を先頭に置きます。SUMIFの式をSUMIFSに書き換えるときは、合計範囲の位置を入れ替えてください。

また、件数を求めるならCOUNTIFとCOUNTIFSが基本です。=COUNTIF(B2:B20,"東京")は、B列に「東京」と入っている行の数を求めます。なお、条件には">=100"のような比較も指定できます。

VLOOKUPやXLOOKUPで別シートを参照して値を探す

VLOOKUPは範囲の1列目で値を検索し、XLOOKUPは探す範囲と結果の範囲を別々に指定して値を探す関数です。 VLOOKUPは、検索キーが見つかった行にある指定した列の値を取り出します。探す表が別のシートにあっても構いません。

=VLOOKUP(A2,商品!A2:D100,3,FALSE)なら、「商品」シートのA列でA2の値を検索し、その行の3列目の値を表示する式です。最後のFALSEで、完全に一致する値だけを探すよう指定しています。エラー値が出たときは、エラーの種類を見分けてから対処を判断します。

XLOOKUPでは、探す範囲と結果の範囲をそれぞれ書きます。=XLOOKUP(A2,商品!A2:A100,商品!C2:C100,"なし")の4つ目の引数は、見つからないときに表示する値です。

MATCHとXMATCHは、範囲の中で値が何番目にあるかを求める関数です。XMATCHはGoogleの関数リストの表に掲載されていないため、エクセルだけの関数と誤解されやすいですが、スプレッドシートにもあります。デフォルトの探し方は、2つの関数で異なります。

• MATCH: 検索の種類を省略すると1になり、昇順に並べ替えた範囲から検索キー以下で最大の値を探す。完全一致は=MATCH(A2,B2:B50,0)のように0を指定する
• XMATCH: 一致モードを省略すると0になり、完全一致で探す。?と*のワイルドカードも使用できる

文字列の結合と日付の計算はTEXTJOINとDATEDIF

文字列の結合や日付の計算に使う関数も、エクセルと同じ名前で入力できます。 複数のセルを1つに結合するなら、区切り文字を指定するTEXTJOINが両方にあります。=TEXTJOIN("、",TRUE,A2:A10)は、A2からA10までを読点を区切りにして連結する式で、2つ目の引数がTRUEなので空のセルは無視されます。

CONCATは2つの値しか結合できないため、3つ以上を結合するにはTEXTJOINか&演算子が必要です。

DATEDIFは、2つの日付の間の日数・月数・年数を求める日付の関数です。=DATEDIF(B2,TODAY(),"Y")と書くと、生年月日から今日までの満年齢を求めます。

IMPORTRANGEやQUERYなどスプレッドシート独自の関数

スプレッドシートにしかない関数は、主に次のグループに分類されます。 エクセルから乗り換えた場合は、他のファイルからデータを取り込む関数と、1つの式で表全体を処理する関数を先に覚えておくと、作業の幅が拡大します。

• 他のファイルやWebから読み込む: IMPORTRANGE・IMPORTDATA・IMPORTXML
• データをまとめて処理する: QUERY・ARRAYFORMULA
• Googleのサービスを使う: GOOGLETRANSLATE・GOOGLEFINANCE・AI

IMPORTRANGEで別ファイルの範囲を参照して読み込む

IMPORTRANGEは、別のスプレッドシートにあるセル範囲を、開いているシートへ読み込む関数です。 1つ目の引数に読み込み元のスプレッドシートのURLを、2つ目の引数に範囲の文字列を書きます。=IMPORTRANGE("読み込み元のURL","売上!A1:D50")のように、どちらも二重引用符で囲みます。範囲は「シート名!範囲」の書式で指定し、シート名を省くと最初のシートが対象です。

初めて読み込むときは、セルに#REF!が出てアクセスを許可のボタンが表示されます。ご自身がオーナーのファイルなら、このボタンで許可すると読み込みが始まります。オーナーが別の人のファイルは、オーナーにアクセス権をリクエストし、許可が下りるまで読み込みは始まりません。

QUERYで集計し、ARRAYFORMULAで列全体に適用

QUERYは表の抽出と集計を1つの式で行う関数で、ARRAYFORMULAは1つの式を範囲全体に適用する関数です。 QUERYは、Google Visualization APIのクエリ言語でデータに問い合わせます。たとえば、=QUERY(A1:D100,"select A, sum(D) group by A",1)と入力するとA列の値ごとにD列を合計した表が表示されます。

クエリの文字列は二重引用符で囲みます。最後の1は、範囲の先頭1行を列名として読み込む指定です。クエリには、select・where・group by・pivot・order byを使うことができます。

配列数式とは、1つの式で複数のセルに結果を出す書き方のことです。ARRAYFORMULAは、配列数式の結果を複数の行や列に表示し、配列を受け取らない関数にも配列を指定できます。

=ARRAYFORMULA(B2:B100*C2:C100)と書けば、2行目から100行目までの「単価×数量」が一度に出ます。行ごとに式をコピーする必要はありません。

式を編集している途中でCtrl+Shift+Enterを押すと、先頭にARRAYFORMULA(が自動で入ります。また、多くの配列数式はARRAYFORMULAを書かなくても、隣のセルにも結果が表示されます。

GOOGLETRANSLATE関数でスプレッドシートの文章を翻訳

GOOGLETRANSLATEは、セルの文章をある言語から別の言語へ翻訳する関数です。 つまり、=GOOGLETRANSLATE(A2,"ja","en")と入力するとA2の日本語が英語に翻訳されて表示されます。言語は2文字のコードで指定し、英語ならen・日本語ならjaと書きます。

ソース言語のデフォルトはauto、ターゲット言語のデフォルトはシステムの言語なので、=GOOGLETRANSLATE(A2)と書くこともできます。ソース言語だけを省くことはできず、省くならターゲット言語も一緒に省きます。

GOOGLEFINANCEも、Googleのサービスと連携した関数です。Google Financeにある現在や過去の証券情報を、セルに取り込みます。

スプレッドシートにないExcelの関数と代わりの書き方

エクセルにしかない関数を含む数式は、そのまま貼り付けても計算されないので、別の関数への書き換えかデータの用意し直しが必要です。 代わりの関数があるものは書き換え、ないものは関数で出していた値をデータとして用意します。

PHONETICなどの文字列関数はスプレッドシートに未対応

PHONETICは文字列からふりがなを取り出すエクセルの関数で、スプレッドシートに同じ働きの関数はありません。 名簿を読み順に並べたいときは、ふりがなの列をご自身で用意します。半角の文字を全角に変換するDBCSとJISも、エクセルだけの関数です。

WEBSERVICEはWebサービスのデータを取得して表示する関数で、FILTERXMLはXPathでXMLから特定のデータを抽出する関数です。どちらもExcel for the webでは使用できません。

スプレッドシートでWeb上のデータを読み込むなら、CSVやTSVはIMPORTDATAで、XMLやHTMLはIMPORTXMLで取り込みます。CUBEで始まる7つの関数も、スプレッドシートにはありません。

TEXTSPLITはSPLIT、SORTBYはSORTで書き換える

TEXTSPLITはSPLITに、SORTBYはSORTに書き換えることで、スプレッドシートでも同じ結果を出すことができます。 ただし、書き換えるときは引数の順番とデフォルトの動きの違いに注意が必要です。

=TEXTSPLIT(A2,",")は、スプレッドシートでは=SPLIT(A2,",")と書けば同じ結果になります。ただしSPLITは、デフォルトでは区切り文字を1文字ずつ見て分ける仕様です。"the"を指定するとt・h・eのそれぞれで分割されるので、区切り文字全体で分けたいときは3つ目の引数をFALSEにします。

SORTBYは、対応する範囲の値を基準に並べ替える関数です。SORTでも、並べ替える範囲の外にある列（同じ行数の1列）を並べ替えの基準に指定することができます。

SORTBYの-1は降順ですが、SORTではFALSEが降順です。=SORTBY(A2:C20,D2:D20,-1)は、=SORT(A2:C20,D2:D20,FALSE)と書き換えます。

CSVを取り込むときは0落ちや文字化けに気をつけます。ショートカットキーはWindowsとMacで異なるので、使う環境に合わせて覚えます。