ChatGPTでGoogleスプレッドシートの表計算作業を超効率化！AI活用術＋覚えておきたい関数18選

ChatGPTなどの生成AIの登場によって、もはやExcelやGoogleスプレッドシートの関数を自分で書かなくても、適切なプロンプトで指示をすることで、ほぼ完璧な関数をAIが書いてくれるようになっている。

この記事では、ChatGPTやClaude3、Google Geminiなどの大規模言語モデルに、スプレッドシートの関数を書いてもらう上でのプロンプトのコツについて解説する。

また、かれこれ10年はGoogleスプレッドシートを愛用している筆者が、非常に頻繁に利用する関数や、覚えておくと便利だと感じている機能を、簡潔に紹介していく。

生成AIにプロンプトを指示して関数式を書く場合でも、自分自身が「Googleスプレッドシートでどんなことができるのか」のイメージを具体的に持っていればいるほど、プロンプトの内容が改善されていくはずなので、基本的な知識を持っておくこともやはり重要だ。

データを整形・集計する基本的な関数から、スプレッドシート上のデータを美しく可視化するためのコツまで、幅広く紹介するので、会社でGoogleスプレッドシートを使うことがある人は、一度は目を通しておいて損はないはずだ。

最近は企業全体でGmailやGoogleドキュメント、Googleスプレッドシートなどを使用しているところも多いだろう。
筆者などは、Gmailとの連携やクラウドでの共同編集の利便性などから、Excelよりも遥かに使用頻度が高くなっている。

共同編集周りの便利な技などは、現段階ではAIに代わりにやってもらうこともできないし、自分が知っておくことで、チームでの仕事の効率を大幅に改善できる場合があるので、特に注目だ。

表計算の作業は、もう全部AIにやらせよう

筆者が、ChatGPTやClaude 3、Geminiなどの大規模言語モデルを使っていて、最も多くの社会人にとって恩恵が大きいのではないかと思う使い道が、ExcelやGoogleスプレッドシートなどの関数を、AIに書かせてしまうことだ。

GoogleスプレッドシートやExcelの関数をググるのはやめよう

AIが登場する以前は、たとえば「このシートのA列のグループごとにB列の合計値を出したいなあ」と思ったら、「グループ別　合計　Excel」などとGoogle検索をしていた。

Google検索結果から、必要な関数の解説ページを見つけ、そのページを自分自身で頑張って理解した上で、実際のExcelシートに当てはめるために書き換える、という作業が必要だった。

しかし、このプロセスが、AIの登場によって一変してしまった。

自分が作業中のGoogleスプレッドシートやExcelの構造を、AIに伝えた上で、「このシートのA列のカテゴリごとにB列を合計して」とプロンプトを書けば、自分が詳細を理解しなくても、そのままコピペするだけで使えてしまう正確な関数を、AIが書き上げてくれるのだ。

「検索→一般的な使い方の理解→自分のシートへの応用法の検討→実行」というステップが、「質問→コピペ→実行」にギューッと省略されてしまったのだ。

ChatGPTを使うとほぼ完璧な関数式が得られる

たとえば、以下のように、ChatGPTにおいて、シートの構造（何行目にデータがあるか、何番目の列か）を解説して、「R列の金額を合計する関数」とプロンプトしただけで、そのままコピペできる関数が生成された。

しかも、この関数式が何をしているのかを、日本語で解説してくれているので、コピペした上で、ちゃんと解説を読めば何が起きているのかを理解することもできる。

これはシンプルな例だが、もっと複雑なIF条件を組み合わせたり、VLOOKUPを使ってシートを跨いだ集計等をしたり、高度で複雑な関数を使う場合でも、的確にプロンプト文を書けば、一瞬で関数式を考えてくれる。

上の例では、ChatGPTの有料プランで最上位のGPT-4モデルを使用している。無料で使えるGPT-3.5でも、似たようなことは可能だが、やはりプロンプト文が長く複雑になってくると、正しい関数式が出てこないことも多いので、どちらかというと有料のGPT-4を使う方がお勧めだ。

生成AI時代に人間に求められる表計算スキルとは

初心者でもChatGPTを使いながらGoogleスプレッドシートやExcelを使えば、中上級者並みの表計算があっという間にできるようになってしまう。

おそらく、数年後には、Googleスプレッドシート上で直接AIに指示をして、勝手に計算をしてくれる時代が来てしまうようにも思える。

人間に残されたスキルの発揮どころは、たとえ関数を知らなくても「多分こんな計算が関数を組み合わせれば実現できるはずだ」と推測して、やりたいことをプロンプトとして言語化する能力、ステップを踏んで最終的な成果物に至るための要件定義の能力だろう。

ChatGPTには、上記の例よりもさらに複雑な関数式の作成や、集計作業を行わせることも可能だが、そうなると人間に求められるプロンプトの記述力も上がっていく。

シート1のB列とC列を〜の条件でフィルタして、シート2にCountifとSumifを組み合わせた集計表を作成し、さらにその結果をシート3でソートする・・・というような段階を踏む作業を行う場合には、まず最終的に必要な結果を思い描き、そのために必要なステップを一つ一つ的確にAIに指示しなければならない。

したがって、生成AIがどれだけ進歩したといっても、やはり基本的な関数の存在や、その使い方は把握しておくに越したことはない。

関数式をAI生成するプロンプトエンジニアリングのコツ

いくら生成AIが進歩したといっても、人間の指示（プロンプト）が不完全だと、AIが考案する関数式はそのままだと全く使えないものになってしまう。

OpenAIのChatGPTやAnthropicのClaude 3、GoogleのGeminiなどの生成AIモデルとのチャットで、GoogleスプレッドシートやExcelについて質問をする場合、AIはあなたが作業中のファイルがどのような構造になっているのかを知らないので、その解説から行う必要がある。

表計算の関数式などをChatGPTに頻繁に聞いている中で、筆者が意識しているプロンプトのポイントは以下のような点だ。

扱いたいデータが何行目／何列目にあるかを伝える
- 例：1行目はタイトル行でデータは2行目から始まっています。
シートを跨いだ参照時は、シート名をそのまま伝える
- 例：「集計」シートで「売上」シートのA列2行目からの金額を合計したいです。
数値以外の形式のデータが入っている列は説明してしまう
- 例：「日付」列には、「2023/4/1 23:59」の形式で日時が入っています。
- 例：「商品」列には「りんご」「いちご」「みかん」があります。

データの範囲を指定しておくことで、生成AIから出力された関数式を、そのまま実際のスプレッドシートにコピペして使うことができる。

データが3行目から始まっているのに、ChatGPTが1行目のデータに計算式を適用していると、一々それを手作業で修正しなければいけないので、先に指示してしまったほうが効率が良い。

また、シートを跨いだ範囲参照時は、「シート2!A2:A10」などといった具合に、シート名を関数式の中に書き込む必要があるので、シート名もすべてChatGPTに伝えておくことで、実際のシート名に合わせた関数式が生成される。

これまた手直しの必要がゼロになるので効率的だ。

また、単なる数値データであれば、合計や平均などの計算をしてと頼むだけで正確な関数式が得られるが、日付データなどだと、様々な形式がありうるために、ChatGPTが考案した関数がそのまま適用できない場合が結構ある（例：日付のみが必要なのに、タイムスタンプが入っており、まずINT関数を適用して時刻情報を消す必要がある、など）。

予めその列に入っているデータの例を一つChatGPTに伝えておけば、そこからどのような形式のデータなのかを類推してChatGPTが考えてくれるので、プロンプトに含めてしまったほうが楽なのだ。

こうしたプロンプトを作成する際、やはり必要になるのは、Googleスプレッドシートの基本的な知識と、AIがどういった点で間違いやすいかを考える力だ。

冒頭で生成AIのGoogleスプレッドシートやExcel作業での活用術を紹介してしまったが、以下では、それでも覚えておくべき必須の関数やスプレッドシートならではの機能を紹介していく。

プロンプトエンジニアリングのコツと、スプレッドシートのよく使う機能を学んでおくことで、あなたの表計算の生産性は大きく向上するだろう。

仕事効率化のため覚えておくべき定番の関数たち

まずは、氏名、住所や郵便番号などの日本人の会社員が頻繁に使うことになるであろう文字データ処理周りのテクニックを、基本的な関数を組み合わせて紹介していく。

余計な記号やスペースを削除し、列を分割・整理する必須関数3種

Excelやスプレッドシートでの作業をしているとき、多くの人が、次のような作業を経験したことがあるのではないだろうか。

苗字と名前が同じセルに入ってしまっているので分けたい
住所のうち、都道府県名だけを抜き出したい
セルによってバラバラな記号やスペースが入っているが、手作業で削除するのはしんどい

こうしたテキスト情報の操作は、氏名や住所、郵便番号などなど、日本人が仕事でExcelやスプレッドシートを使うと、かなり頻繁に出てくる定番の問題だと言えるだろう。

ここでは、こうした和文のテキスト操作に役立つ主要な関数を3つ紹介する。
これらの便利な関数の存在を知っておくと、複雑な作業が必要な場面でも、関数をうまく組み合わせて、目的を達成できることがある。

「汚く手入力された氏名を、苗字／名前に分割し、2列に分ける」という基本タスクを例にとって、ステップバイステップで解説していこう。

SUBSTITUTE：スペースを全て半角に変換

SUBSTITUTEは、置き換えるという意味の英単語だ。
セル中に入力されている文字列に、「りんご」という文字が含まれていたら、それを「もも」と入れ替える、といった操作を自動化できる便利な関数である。

Excelやスプレッドシートにおいて、「全角スペース」は何かと邪魔な存在だ。
後ほど登場する不要なスペースや記号を削除してくれる便利な関数たちも、「全角スペース」には対応していないために、半角スペースは消えても全角スペースが残るなど、ユーザーが期待しているのと違う動作をしてしまうことがよくある。

半角と全角が混じって存在しているような日本語ドキュメントはトラブルの元となるのだ。

そこで、データの整形に着手する前に、ひとまずSUBSTITUTE関数を用いて、全ての全角スペースを、半角スペースに置き換えよう。

=SUBSTITUTE(A2, "　", " ")

TRIM：余計なスペースを一発で全削除

もう一つよくあるトラブルに、見えないスペースの存在によって、期待していたのと違う動作をしてしまうということがある。

「山田太郎」とセルに入力され実は不要なスペースが文頭、文末、文中に複数混入していても、見た目上は普通に見えるので、気付くことができない。

そこで、文の最初、最後に登場するスペースの削除、そして2回以上の繰り返しのスペースを1回に削減してくれる便利なTRIM関数を活用しよう。

=TRIM(B2)

SUBSTITUTE関数の説明で述べた通り、TRIM関数は、全角スペースには対応していない。
従って、全角スペースを含めて不要なスペースを削除したい場合には、まず最初にSUBSTITUTE関数を用いて全角スペースを半角スペースに変換しておく必要がある。

LEFT / RIGHT：名前やIDの分割に役立つ頻出関数

次に、半角スペース1つで区切られた氏名を、苗字と名前に2分割する方法を解説する。
ここで使うLEFT関数、RIGHT関数は、氏名分割だけでなくさまざまな場面で応用が効くので、その存在を知っておくだけでもプラスになるはずだ。

LEFT関数は、セル内の左側から数えて、何番目までの文字を抽出するかを指定して、希望する箇所を切り出すための関数だ。

=LEFT("アイウエオ", 2)

とした場合、左側から2文字の「アイ」を切り出すことができる。

これが基本の使い方だが、「山田太郎」や「小山田太郎」の苗字だけを抜き出すには、苗字の文字数によって2文字目、3番目と数字が変わってしまうので、単純にLEFT関数に数字を入れるだけではうまくいかない。

「半角スペースが何文字目にあるか」を調べ、その数字をLEFT関数に渡せばよいのだ。

そこで用いるのが、FIND関数だ。FIND関数は、ある文字が、何番目に存在するかを返してくれる関数だ。

=FIND("ウ", "アイウエオ")

これは「3」という数字を返してくれる。「ウ」という文字は3番目に存在するためだ。

このFIND関数を、苗字と名前の間にある半角スペースに用いれば、LEFT関数で苗字だけを抜き出せるというわけだ。

ただし、「山田太郎」の場合、半角スペースの位置は「3」番目になるが、欲しいのは「山田」の2文字だけなので、

=FIND(" ", C2) - 1

というように、「1」を引く必要がある。これをLEFT関数と組み合わせ、最終的には以下のようにする。

=LEFT(C2, FIND(" ", C2) - 1)

逆に、RIGHT関数を使って、「山田仁太郎」の右側から「仁太郎」だけを抜き出すには、どうすればよいだろう。

LEFT関数では単純にスペースの位置からマイナス1をすればよかったが、RIGHT関数の場合は、それではうまく名前部分を抜き出せない（スペースの位置3番目ー１で、右から2文字抜き出すと、「仁太郎」が「太郎」になってしまう）。

「山田仁太郎」の場合、「スペース含む文字列全体の長さ（6文字）」ー「半角スペースの位置（3番目）」＝3

と計算すれば、うまく名前の3文字だけを抜き出すことができることがわかる。
これを関数で表現すると以下のようになる。文字列の長さは、LEN関数（LENGTH＝長さという意味）で計算できる。

=LEN("山田 仁太郎") - FIND(" ", "山田 仁太郎")

これをRIGHT関数に渡せば完了だ。

=RIGHT(C2, LEN(C2) - FIND(" ", C2))

種類別に数を数えるCOUNTIF

以下のような表から、国別に売上金額を合計したり、国別に何件の入力があるかを数えたいとする。

そんな時に活躍するのが、条件（IF）に合致する場合に発動する以下の関数だ。

「もしも”国”列が日本だったらカウントする」のがCOUNTIF関数
「もしも”国”列が日本だったら”売上”列をサム（合計）する」のがSUMIF関数

まずは、件数を数えるCOUNTIF関数の例を見てみよう。

A2:A7の範囲に入力されている国の数を数えたいので、その範囲を指定した上で、「アメリカ」という文字と合致した場合に件数をカウントするような条件を指定する。

=COUNTIF(A2:A7, "アメリカ")
=COUNTIF($A$2:$A$7, A12)

上の2式は、同じ結果を返すが、後者の式の方がおすすめだ。

「$」マークを付けると絶対参照となり、この式を他のセルにコピーした時にも、指定した範囲がずれずに済む。
また、「アメリカ」と直接手入力して国名を指定するのではなく、別表として国名を列挙した表を用意し、国名列を参照することで「日本」「中国」「アメリカ」と国名が変わっても自動で集計が可能だ。

種類別に合計値を出すSUMIF

次に、SUMIF関数を解説する。COUNTIF関数と非常に似ているので簡単だ。
SUMIF関数の場合は、件数を数えるのではなく「売上を合計する」ので、合計したい売上列の範囲指定「B2:B7」が加わるだけだ。

先ほどと同じ書き方をすると以下のようになる。後者の式の方がよりおすすめだ。

=SUMIF(A2:A7, "アメリカ", B2:B7)
=SUMIF($A$2:$A$7, A12, $B$2:$B$7)

Wikipediaなどネット上の表を自動で読み込む

あまり知られていない機能であるが、Webページ上に存在している表をスプレッドシートにコピペしたいとき、URLを指定して自動で行う関数が存在する。

IMPORTHTML関数は、URLと、読み込みたい要素（ここでは表を指す”table”）、何番目にある要素か（ページに載っている何番目の表が欲しいか）を指定することで、自動で表を読み込んでくれる。

例えば、ウィキペディアにあるロサンゼルスの解説ページにたくさんある表のうち、中腹あたりにある「人種別の人口構成比」のテーブルを取得したいとする。

Los Angeles – Wikipedia

https://en.wikipedia.org/wiki/Los_Angeles

WikipediaのページのURLをコピペし、10番目の表が欲しいと入力すると、望みの表がスプレッドシートに打ち込まれる。

＝IMPORTHTML("https://en.wikipedia.org/wiki/Los_Angeles", "table", 10)

正確には、上記ページのHTMLを確認して、何番目のtableが欲しいかを数え（ここでは10番目）、数字を入力するという流れになるが、HTMLを読めない人は、大体の位置で8と入れてみたり11と入れてみたり試行錯誤すれば大体辿り着けるだろう。

別シートの一部を範囲を指定して読み込む

外部から表を読み込むIMPORT系の機能として、他のスプレッドシートの表を取り込むIMPORTRANGE関数も役に立つ。

別に作成したスプレッドシートのURLと、そのファイル内のどのシートの、どの範囲を抜き出すかを指定する。

=IMPORTRANGE("https://docs.URLをここに", "シート1!A1:D10"）

こうすれば、たとえば社員の勤怠状況をまとめている表を、別のファイルにある給与計算の表に取り込むことなどが可能になる。

セル内の文字をGoogle翻訳で自動翻訳

Googleのクラウドサービスならではの良さとして、Google翻訳をスプレッドシート内で使用できるのも嬉しいポイントだ。

英文のセルを指定して、GOOGLETRANSLATE関数を用いれば、そのセルの内容を自動的に日本語に翻訳してくれる。

チームでの共有スプレッドシートで使える便利ワザ

会社でGoogle Appsを導入している場合、Googleスプレッドシートの最大の利点は、チームのメンバーでリアルタイムにシートを同時編集・閲覧できる点だろう。

ここからは、そんな共同編集作業に最適なスプレッドシートの便利機能を紹介していく。

コメントを個人に割り当ててGmailを自動送信する

特定のセルを右クリックして、コメントを残す機能がある。

チームのメンバーに修正して欲しい箇所など、コメントが入っていることに確実にメンバーに気づいてもらいたい場合には、Gmailへ通知を送信する機能が便利だ。

コメント欄で「+」記号を打ち込むと、Gmailに登録している連絡先から、コメント通知を送信する相手を選ぶことができる。

入力文字の選択肢を予め決めて、プルダウンで選ばせる

複数人でスプレッドシートを管理する場合、自由記述ではなく、決まった単語だけを入力して欲しい場合がある。

たとえば、勤怠管理シートであれば、それぞれのスタッフにバラバラの書式で手打ちをさせるのではなく、「出勤」「全日休」「午前休」「午後休」などといった定型文を選んでもらった方が、のちのち集計作業を行う上でも楽になる。

こんな時に便利なGoogleスプレッドシートに備わった機能が、「データの入力規則」である。
範囲を指定して、入力させたい単語をリスト形式で定義する。すると、その範囲には予め定義された単語しか入力できないように制限することができる。

上の画像のようにデータの入力規則を設定すると、以下の画像のようにプルダウン形式で選択できるようになる。

チェックボックスを使って共有タスクリストを作る

また、意外な機能かもしれないが、Googleスプレッドシートではセルをチェックボックスとして利用することもできる。

メニューから「挿入」→「チェックボックス」とすると、セルをクリックすることでチェックが可能なボックスが表示される。

これを利用すると、たとえばタスクリストを作成しておいて、チームのメンバーが完了したタスクにチェックを入れていくといいった運用も可能だ。

他メンバーに変更されたくないエリアをロックする

複数人での共同編集は便利ではあるが、自分がせっかく作り上げた複雑な計算式を、他のメンバーにうっかり破壊されてしまうといったトラブルも生じやすい。

Googleスプレッドシートでは、特に重要なデータが含まれる範囲については、ロックをかけて編集できるメンバーを一部に絞り込むことが可能だ。

ロックしたい範囲を指定した上で、「範囲の編集権限」機能でユーザー制限を行うと、特定の範囲だけは閲覧権限のみで編集権限なし、といったかなり器用な権限の割り振りが実行可能だ。

スプレッドシートの見た目を改善する便利技

ここからは、表計算というよりは、表全体を見やすく、美しく整理するためのテクニックを紹介していく。

売上高の分布をカラーヒートマップで直感的に比較できるようにするなど、なんだかんだで役立つことが多いので、覚えておいて損はないはずだ。

数字の高低をヒートマップで直感的に把握

数字の高い・低いによって、色の濃さを変化させるのがヒートマップ／カラースケール機能である。

まずは、色をつけたい範囲を指定した上で、「表示形式」から「条件付き書式」を選択する。

すると、その範囲を何らかの条件で色つけすることが可能なので、ここでは「カラースケール」を選択し、値が大きくなればなるほど、赤色が濃くなるようなカラースキームを設定する。

すると、一目で大きな値を持つ行がどこなのかが分かる。
売上高、成長率、降水量、アクセス数などなど、ありとあらゆる量を一目でわかりやすいように整理したい場合に、頻繁に活躍するであろうとても便利な機能だ。

作業完了した行を塗りつぶす

「条件付き書式」機能を使うと、さらに発展的なことも実行可能だ。

たとえば、問い合わせリストにおいてまだ折り返していない行に「未完了」、折り返し電話が完了した行には「対応済」と記入するとする。
重要なのは未完了の問い合わせだけなので、完了した行は目立たないように黒く塗りつぶしたい。

この場合、条件付き書式で「単一色」を選び、書式ルールをカスタムにして、以下のように設定すれば、「E列に”完了”と入力されていたら、その行を灰色に塗りつぶす」ことができる。

=$E2="完了"

ここで「$」がついているのは、全ての列について色の塗り分けを判定する時に、毎回E列だけをチェックするように指示する絶対参照を意味している。
これによって、E列が完了になった行は、全て灰色に塗りつぶされ、目立たなくすることができるというわけだ。

行の背景色を交互に変える

たくさんのデータが入っている表で、行ごとに色が交互になっている方が見やすい場合がある。

このような場合には、色付けしたい範囲を選択した上で、「表示形式」から「交互の背景色」を選ぶだけで、ワンタッチで交互の背景色が実現できる。

さらに、ヘッダーの有無や、色の組み合わせなどを細かくカスタマイズすることができる。

タイトル行を斜め文字にする

たまに、タイトル行が長めになっている場合など、斜めにした方が外観状美しい場合がある。

あまり目立たない・それほど使い所もない機能ではあるが、Googleスプレッドシートにはそのための機能もある。
フォントの設定をするバーの中に、セル内の文字の傾きを変えることができるオプションが存在している。

Googleサービスの相互連携でもっと便利に

Googleスプレッドシートの強みは、Googleドキュメントなどの他Googleサービスと、スプレッドシートで作成した表やグラフを同期できることも挙げられる。

そんなサービス間の連携についても簡単に紹介する。

Googleドキュメントにグラフを挿入する

スプレッドシートでグラフを作成したとき、そのグラフを画像等としてMicrosoft WordやGoogleドキュメントに取り込みたい場合がある。

せっかくならば、スプレッドシート内のデータが変化した場合に、Googleドキュメントのグラフも最新のものに更新されるような同期機能を活用しよう。

まず、Googleスプレッドシートで一般的なグラフを生成する。

そして、グラフを挿入したいGoogleドキュメントを開いた上で、メニューから「挿入」「グラフ」「スプレッドシートから」と選んで行き、他ファイルであるGoogleスプレッドシートからお目当てのグラフを引っ張ってくる。

すると、スプレッドシート内にあるグラフを直接ドキュメント内に取り込むことができる。

これを用いると、毎月更新が必要なデータダッシュボードのようなものを簡易にスプレッドシートとドキュメントの組み合わせで作ってしまうことなども可能である。

Google Appsのショートカットを覚えたい時は

ExcelやGoogleスプレッドシートについて検索すると、とにかくショートカットキーに関する情報が溢れている。
ほとんどの場合、それらを覚えておくのは不可能で、いつも使う、自分が覚えている範囲のショートカットだけに落ち着いてしまう。

Googleドキュメントやスプレッドシートを使用しているときに、「あのショートカットって何だっけ？」となったら、ググるよりも、メニューから「ヘルプ」「キーボードショートカット」を選び、公式の解説を見た方が早いかもしれない。

このヘルプの存在は一応覚えておこう。