便利な関数小技6選！ Google スプレッドシートの意外と知られていないテクニック ｜ SiTest (サイテスト) ブログ

Google スプレッドシートを使っていると、複雑な集計をしたり、表示方法を工夫して見やすくしたくなることがあります。
色々試行錯誤してやってみたら、実は関数一つで解決してしまうようなケースが良くあります。
やりたいことを実現できる関数がないか探してみる習慣を付けることは大切です。
そして、なるべく手作業を減らして、自動化できるようにすることで、ミスを減らして効率化できます。

今回は、意外と知られていない Google スプレッドシートの関数を使ったテクニックを6つご紹介します。
各テクニックのサンプルはこちらで公開しています。

数式（関数）の入ったセルをコピペすることはよくあると思います。
しかし、上手くコピペできていなかったり、数式が消えていたりなんてことも起こってしまいます。
そんな時、 ISFORMULA 関数 を使えば、どこに数式があるかを自動で判別してくれます。

構文

ISFORMULA(セル)

ISFORMULA 関数と条件付き書式を組み合わせて使用することで、数式が含まれるセルに書式を設定することができます。

このサンプルでは、数式が含まれるセルの背景色が緑色になるように条件付き書式を設定しました。

条件付き書式の範囲はシート全体にし、カスタム数式で「=ISFORMULA(A1)」と入力しています。

どのセルに数式が含まれるか一目瞭然で分かり易いですね！

数値を合計する際に、小計と総合計をそれぞれ出す場合があります。
ところが総合計を計算するのに、小計が邪魔になる場合があります。
SUM 関数で一つずつ指定して足してもいいですが、シートを編集した場合に面倒なことになったりします。
SUBTOTAL 関数で、小計を除いて合計を計算しましょう！

構文

SUBTOTAL(関数コード, 範囲1, [範囲2, ...])

SUBTOTAL 関数は、合計以外にも、関数コードの数値次第で色んな計算を行ってくれます。
今回は、合計を計算する関数コードである9を使います。
SUBTOTAL 関数を使用すると、他のSUBTOTALの結果を除外した計算結果を出力してくれます。

=SUBTOTAL(9,C4:C5)

C6セルにはこの数式。

=SUBTOTAL(9,C7:C8)

C9セルにはこの数式。

=SUBTOTAL(9,C4:C9)

C10セルにはこの数式。

C10セルでは、C4：C9が範囲になっており、小計を含んでいますが、小計を除いた合計値が算出されていることがわかります。

スプレッドシートに未入力の項目があったりすると、エラー状態になって見にくくなってしまうことがあります。
IFERROR 関数を使用することで、エラーの場合は空や別の文字列を表示することができます。
構文

IFERROR(値, [エラー値])

=IFERROR(A4, "エラーです")

B4セルにはこの数式が入っており、エラーの場合は「エラーです」と表示されます。

=IFERROR(A5, "エラーです")

B5セルにはこの数式が入っていますが、エラーではないのでそのまま数値が表示されます。

=IFERROR(A6)

B6セルにはこの数式が入っています。
ISERROR 関数の第2引数を省略した場合は、エラーの場合に空文字になります。

数値が一定の増加や減少をする場合、TREND 関数でその予測をすることができます。

構文

TREND(既知データ_y, [既知データ_x], [新規データ_x], [b])

=TREND(B4:B9, A4:A9, A10)

B10セルにこの数式が入っています。
過去の傾向から、最小二乗法で計算した値を返してくれます。

日付から曜日や月を表示する場合に、「表示形式」の機能を使わずに、TEXT 関数を使用して実現できます。
構文

TEXT(数値, 表示形式)

=TEXT(A4, "ddd")

C4セルは、表示形式を「ddd」にしています。
「ddd」は、曜日を省略形で表示します。

=TEXT(A4, "dddd")

C5セルは、表示形式が「dddd」です。
「dddd」は、曜日を省略せずに出力します。

=TEXT(A4, "mmm")

C6セルは、表示形式を「mmm」にしています。
「mmm」は、月を表示します。

TEXT 関数は、その他にも色んな表示形式に対応しています。
色んな場面で利用できる関数です。

こちらは複数関数使用していますが、それらを駆使することで、ありそうでなかった「単語を指定回数繰り返して各セルに入力する」という機能を実現しています。

説明していきます。

=TRANSPOSE(SPLIT(CONCATENATE(ARRAYFORMULA(REPT(A4:A5 & ",",B4:B5))),","))

D3に入っているのがこの数式です。
5つの関数を使用していますので順に説明します。

① REPT 関数

REPT 関数は、指定した回数だけ、文字列を繰り返して連結する関数です。
REPT 関数で、指定した回数だけ文字を繰り返していますが、その際に「,（カンマ）」を連結しておくのがポイントです。

② ARRAYFORMULA 関数

ARRAYFORMULA 関数は、配列数式を扱う関数です。
「ピザ」の繰り返し文字列と「キリン」の繰り返し文字列を、配列数式として扱うために使用します。

③ CONCATENATE 関数

CONCATENATE 関数は、文字列を連結する関数です。
②の配列数式にした文字列を連結します。
ここまでの数式の結果は「ピザ,ピザ,ピザ,ピザ,ピザ,キリン,キリン,キリン,キリン,」のようになるはずです。

④ SPLIT 関数

SPLIT 関数は、指定した文字で、文字列を分割する関数です。
③の文字列を「,（カンマ）」で分割します。

⑤ TRANSPOSE 関数

TRANSPOSE 関数は、配列数式の行と列を入れ替えます。
SPLIT 関数の結果は横方向に展開されるので、 TRANSPOSE 関数で、縦に並ぶようにしています。

他にも Google スプレッドシート関連の記事を書いております。
こちらの記事を読んで頂いた方は、オススメですのでぜひ以下の記事も御覧ください！

■Googleスプレッドシート初心者脱出！配列数式でスマートに計算しよう
■営業日だけを見たい！ Google スプレッドシートで色々便利な日程表を作ってみた
■簡単！5分でできる！ Google 機械学習をスプレッドシートで使う方法を解説
■Google スプレッドシートで使えるアドオンを作ってみよう
■Googleスプレッドシート初心者脱出！SUMPRODUCT関数を使ってみよう