ExcelとGoogleスプレッドシートの自動化｜関数・Power Query・VBA・GASの使い分け

毎月の集計や、同じ形式の資料作成、複数ファイルからの転記。ExcelやGoogleスプレッドシートで繰り返している作業は、関数や標準機能を組み合わせることで、手作業を減らせる場合があります。

自動化を始める際は、まず何を計算・取得・更新したいのかを整理しましょう。いきなりVBAやGoogle Apps Scriptでコードを書くのではなく、関数やPower Query、マクロの記録で対応できるかを確認すると、仕組みを小さく保ちやすくなります。

この記事では、ExcelとGoogleスプレッドシートの選び方、目的別の自動化機能、具体的な集計例を紹介します。作成後に結果を確認し、ほかの担当者へ引き継ぐためのポイントも整理します。

１．ExcelとGoogleスプレッドシートは、利用環境で選ぶ

Excelは個人作業専用、Googleスプレッドシートは共同作業専用というわけではありません。Excelも、対応するバージョンやOneDrive・SharePoint Onlineなどの保存先を使うことで、共同編集ができます。

選ぶ際は、現在のファイル形式、組織で認められている保存先、必要な機能、運用担当者を確認します。慣れている環境を生かせるかも、導入後の負担に関わります。

ローカルに保存すれば自動的に安全になるわけでも、クラウドなら必ず危険になるわけでもありません。データの内容と組織のルールに合わせ、保存場所、アクセス権限、バックアップを確認してください。

Excelのデスクトップ版とWeb版を区別する

Excel for the webでは、VBAマクロの作成・実行・編集はできません。マクロを含むブックを開けることと、そのマクロを動かせることは別です。

また、Office ScriptsはVBAとは異なる自動化の仕組みです。既存のVBAをそのまま使える機能ではないため、利用条件や対応する操作を確認して選びましょう。

２．自動化する前に、元データの形をそろえる

自動化がうまく動かない原因は、コードだけとは限りません。日付が文字列になっている、同じ項目に複数の表記がある、見出しが途中で変わるといった状態では、集計や取り込みの結果がずれることがあります。

まず、何を１行として記録するのかを決め、列ごとの意味と入力方法を統一してください。見た目を整えるための表と、集計するためのデータを分けると管理しやすくなります。

• １行の意味を決める：１件の注文、１回の作業、１人の回答などにそろえる。
• 見出しを統一する：「金額」「売上金額」など、同じ意味の列名を混在させない。
• 値の形式をそろえる：日付は日付、金額は数値として扱える状態にする。
• 分類名をそろえる：入力規則などで表記ゆれを減らす。
• 明細と集計結果を分ける：合計行を明細に混ぜ、二重に集計しない。

元データを直接書き換える処理を試す場合は、複製したファイルで確認しましょう。削除や上書きを含む自動化では、通常の「元に戻す」だけに頼らず、復元できる状態を用意します。

３．目的から、関数・標準機能・スクリプトを選ぶ

自動化の方法は、一つの順番ですべて学ぶ必要はありません。条件別の集計なら関数、複数ファイルの取り込みならPower Queryなど、作業の内容から候補を絞ります。

関数は、セルの値を基に計算結果を表示するためのものです。一方、ファイルの作成やメール送信など、外部への操作を伴う処理には、別の機能やスクリプトが必要になります。

４．具体例：SUMIFSで条件別の金額を集計する

まずは、ExcelとGoogleスプレッドシートの両方で使えるSUMIFS関数を試してみましょう。次の架空のデータを、見出しを含めてA1からD5へ入力します。

「分類が教材」かつ「状態が完了」の金額を合計するには、F2など、元データと重ならないセルへ次の式を入力します。C列の金額は、「3000円」という文字列ではなく数値として入力してください。

=SUMIFS(C2:C5,B2:B5,”教材”,D2:D5,”完了”)

この式では、C2:C5が合計する範囲です。B列が「教材」、D列が「完了」という両方の条件を満たす行だけが対象になるため、期待する結果は3000＋1500＝4500です。

行が増えたときの範囲も確認する

この例の範囲は５行目までです。６行目以降にデータを追加しても、式の対象範囲を変更しなければ、その行は集計されません。

継続利用する場合は、Excelのテーブルなどを活用する方法や、運用に合った範囲指定を検討します。列全体を参照する方法もありますが、データ量や数式の数による処理負荷も確認してください。

結果が違うときに見るポイント

• 「教材」の前後に余分な空白が入っていないか。
• 「完了」と「対応完了」など、分類名が混在していないか。
• 金額が文字列として保存されていないか。
• 合計範囲と条件範囲の行数・位置がそろっているか。
• 追加した行が、集計範囲の外にないか。

最初は手計算できる少量のデータで確かめ、正しい結果になることを確認してから対象を増やしましょう。エラーが出ないことだけでなく、集計対象が意図どおりかを見ることが重要です。

５．複数ファイルの統合ではPower Queryも検討する

各部署のファイルを毎月まとめる作業では、VBAを書く前にPower Queryで対応できるかを確認してみましょう。同じ列構成などを持つ複数のファイルを取り込み、一つの表へ結合する用途に使えます。

取り込みや変換の手順を設定すると、次回以降はデータを更新して結果を確認できます。ただし、対応するデータソースや操作はExcelの環境によって異なるため、利用中の版で確認してください。

1. 対象ファイルの列名やデータ形式をそろえる。
2. 取り込み対象のフォルダーやファイルを指定する。
3. 不要なファイルを除外し、結合する。
4. 日付・数値などのデータ型と、必要な列を確認する。
5. 結果を読み込み、件数や合計を元ファイルと照合する。
6. ファイルを追加・更新した場合の再読み込みを試す。

同じフォルダーに過去の複製や集計済みファイルがあると、意図しないデータまで取り込む可能性があります。結合できたことだけで安心せず、対象ファイルと取り込み件数を確認しましょう。

また、処理速度はファイル数、容量、変換内容、実行環境によって変わります。「数十ファイルでも必ず数秒」とは断定せず、自分のデータで計測してください。

６．マクロの記録とVBA・GASの使いどころ

マクロの記録は、書式設定などの操作を記録し、繰り返し実行するための機能です。コードを最初から書かずに始められますが、記録した操作が異なるデータにも適用できるかは確認が必要です。

例えば、決まったセルに色を付ける処理と、選択した位置を基準に色を付ける処理では、必要な参照方法が異なります。固定した範囲を記録しただけでは、増えた行が処理対象に入らないこともあります。

ExcelとGoogleスプレッドシートでは、記録される仕組みが異なる

ExcelのVBAマクロの記録では、操作がVBAコードとして保存されます。Googleスプレッドシートでマクロを記録すると、操作を再現するApps Scriptの関数が作成されます。

どちらも、必要に応じてコードを確認・修正できます。ただし、記録機能があらゆる操作を再現できるわけではなく、条件分岐や外部サービスとの連携には追加の実装が必要になる場合があります。

GASによる定期実行や通知では、実行者と上限を確認する

Google Apps Scriptでは、トリガーを使って時刻やイベントに応じた処理を実行できます。インストール型トリガーは、作成したアカウントの権限で動作するため、担当者変更時の管理も必要です。

また、実行時間やサービスの利用回数には上限があります。フォーム送信後の通知などを作る場合も、遅延や失敗を把握できるようにし、「送信した瞬間に必ず通知される」とは考えないようにしましょう。

７．運用前に確認したいこと

自動化では、間違った処理も繰り返されます。通常のデータだけでなく、空欄、重複、列の追加、ファイル名の変更などを試し、意図しない更新が起きないか確認してください。

手順書には、コードの説明だけでなく、どのファイルをどこへ置くか、どの操作で実行するか、正常終了を何で判断するかを残します。作成者が不在でも、確認と停止ができる状態を目指しましょう。

効果を測る際は、処理時間だけでなく、準備・確認・修正・保守に必要な時間も含めます。自動化後に確認作業が増えている場合は、処理範囲や入力方法を見直してください。

よくある質問

プログラミングができなくても自動化できますか？

関数やピボットテーブル、Power Query、マクロの記録から取り組めます。まずは一つの集計や書式設定を選び、元の作業と同じ結果になることを確認してみましょう。

VBAとGASはどちらを先に学ぶべきですか？

現在使っている環境と、実現したい操作で選びます。デスクトップ版Excelの既存業務を扱うならVBA、GoogleスプレッドシートやGoogle Workspaceの連携を扱うならGASが候補になりますが、標準機能で済むかも先に確認してください。

突然動かなくなったら、何を確認すればよいですか？

エラーの内容と、直前に変わった点を確認します。シート名・列構成・保存場所だけでなく、アクセス権限、認証、利用上限、サービス側の障害も確認対象です。原因が分からないまま繰り返し実行せず、元データを保全して調べましょう。

まとめ｜小さな集計から、確認できる自動化を始めよう

ExcelとGoogleスプレッドシートの自動化では、利用環境に合う機能を選び、元データの形をそろえることが重要です。関数、Power Query、マクロ、スクリプトを、作業の目的に合わせて使い分けましょう。

最初は、手計算できる条件別集計などから始めてください。正しい結果を確認し、データが増えた場合やエラー時の動作も確かめながら、自動化する範囲を広げていきましょう。

参考資料

• Microsoft：Excelブックの共同編集、Excel for the webでのVBAマクロの扱い：共同編集の条件とWeb版の制約。
• Microsoft：Power Queryで複数ファイルを含むフォルダーからデータをインポートする：ファイルの結合と更新。
• Microsoft Learn：Office ScriptsとVBAマクロの違い：自動化の仕組みと対応環境の比較。
• Google：SUMIFS関数：複数条件による合計の構文。
• Google：スプレッドシートのマクロ：操作の記録とApps Scriptの関係。
• Google：インストール型トリガー、Apps Scriptの割り当てと制限：実行権限と利用上限。