「別のファイルから数字をコピーして貼る」「条件に合う行だけを別のシートに写す」「月ごとに集計し直す」——スプレッドシートの手作業のかなりの部分は、写す作業です。

結論から書きます。写す作業の多くは、スプレッドシートの標準の機能だけで自動にできます。 手で写している作業は、次の5つのパターンに分けられます。

いまの手作業

置き換える機能

一度設定すると

① 別のファイルの数字をコピーして貼る

importrange

元のファイルを直せば、こちらも変わる

② 条件に合う行だけを別のシートに写す

filter

条件に合う行が、自動で並ぶ

③ 担当者別などに集計し直す

query

行が増えても、集計表を作り直さなくていい

④ フォームの回答を一覧に写す

フォームとスプレッドシートのリンク

回答が届くたびに、1行ずつ増える

⑤ 集計した数字を資料に貼り直す

スライドへのリンク付き貼り付け

「更新」を押すだけで、資料の数字が変わる

この記事では、それぞれの置き換え方と、関数では届かないところをまとめます(関数は小文字で書いていますが、大文字でも同じように動きます)。

① 別のファイルの数字を写している → importrange

支店ごとのファイル、担当者ごとのファイルから、毎月数字をコピーして集計用のファイルに貼っている場合は、importrange で「つなぐ」形に変えられます。

=importrange("元のファイルのURL","シート1!A1:E100")

1回目は、セルに「#REF!」と出て、その横に「アクセスを許可」のボタンが表示されます。ボタンを押すと、元のファイルのデータが取り込まれます。

importrangeを入れた直後の画面。「アクセスを許可」のボタンが表示されている
「アクセスを許可」を押したあとの画面。別のファイルの一覧がそのまま取り込まれている
  • 元のファイルを直すと、こちらの表示も変わります。貼り直しも、貼り間違いもなくなります
  • 範囲を「シート1!A:E」のように列ごとにしておくと、元のファイルの行が増えても取りこぼしません

② 条件に合う行だけを写している → filter

「未対応の行だけ」「今月の行だけ」を別のシートに写している場合は、filter を使います。元の表を並べ替えたり、行を消したりする必要はありません。

=filter('一覧'!A2:E, '一覧'!D2:D="未対応")
filterで「未対応」の行だけを別のシートに表示した画面
  • 元の表で状態を「対応済」に変えると、その行はこちらから自動で消えます
  • シート名が日本語のときは、「'一覧'!」のようにシート名が「'」で囲まれます(入力すると自動で付きます)
  • 条件は「かつ」で増やせます。例:=filter('一覧'!A2:E, '一覧'!D2:D="未対応", '一覧'!B2:B="山田")

③ 担当者別などに集計し直している → query

集計のたびにフィルタをかけて足している場合は、query に置き換えます。

担当者別の件数と金額を出す(query)

=query('一覧'!A1:E, "select B, count(B), sum(E) where B is not null group by B label count(B) '件数', sum(E) '金額'", 1)
queryで担当者ごとの件数と金額の合計を出した画面
  • 「B列(担当者)ごとに、件数とE列(金額)の合計を出す」という意味です
  • 最後の「label」は、集計した列の見出しを付ける部分です。なくても動きますが、その場合の見出しは「count 担当者」のような表示になります
  • 元の表に行が増えても、集計表は自動で変わります

①と③を組み合わせる

別のファイルのデータを、そのまま集計することもできます。importrange で取り込んだデータを query で集計するときは、列を「B」ではなく「Col2」のように番号で指定します。

=query(importrange("元のファイルのURL","シート1!A:E"), "select Col2, sum(Col5) where Col2 is not null group by Col2 label sum(Col5) '金額'", 1)
別のファイルのデータを取り込んで、担当者ごとの金額を集計した画面

支店ごとのファイルを1つの集計表にまとめたいときに便利です。

画面の例は、記事用に作った架空のデータです。

④ フォームの回答を写している → フォームとスプレッドシートのリンク

Googleフォームの回答を見ながら、一覧表に手で入力している場合は、フォームとスプレッドシートをつなぎます。

  • フォームの編集画面で「回答」タブを開く
  • 「スプレッドシートにリンク」を押す
  • 新しいスプレッドシートを作るか、既存のスプレッドシートを選ぶ

以後は、回答が届くたびに1行ずつ自動で増えます。回答のシートは直接編集せず、②の filter や③の query で別のシートに表示して使うと、元のデータを壊さずに済みます。

⑤ 集計した数字を資料に貼り直している → スライドへのリンク付き貼り付け

毎月の報告資料に、集計表やグラフを貼り直している場合は、リンク付きで貼り付けます。

  • スプレッドシートのグラフや表をコピーする
  • Googleスライドに貼り付け、「スプレッドシートにリンク」を選ぶ
  • 数字が変わったら、スライド側に出る「更新」を押す

貼り直す作業が、ボタン1つになります。

関数では届かないところ

①〜⑤で、写す作業の多くはなくせます。ただ、次のような作業は、関数や標準の機能だけでは届きません。

  • 決まった時間に、自動で動かしたい:毎朝の集計、月末の締め処理など
  • ファイルを作ったり、送ったりしたい:顧客ごとの請求書を作る、メールで送る
  • たくさんのファイルを、まとめて処理したい:数十の支店ファイルを一度に更新する

ここから先は、GAS(Google Apps Script)の出番です。GASは、決まった時間に処理を動かす「トリガー」を設定でき、ファイルの作成やメールの送信もできます。外注した場合の費用は、マクロ・GAS開発を外注すると、いくらかかるのかにまとめています。

自動にする前に、確かめておくこと

  • 元の表の形が揃っているか:途中に合計行がある、1枚のシートに表が2つある、といった形だと、filter や query は正しく動きません。表の形はAIに社内のデータを「食わせる」とは?で書いた5つの形と同じ点を確かめてください
  • 誰が直せるか:関数を組んだ人しか直せない状態だと、その人がいなくなったときに止まります。どのシートがどこから数字を持ってきているか、シートの冒頭に1行メモを残しておくと安心です

当社に頼む場合

①〜⑤の設定は、手順どおりに進めれば社内でもできます。一方で、「どの作業を、どの形に置き換えるか」を決めるところや、GASが必要な部分、作った後の手直しまでを社内で抱えるのが難しい場合は、外に頼む選択肢もあります。

当社のスプレッドシートBPOは、月額198,000円(税込)〜で、作業の代行から、集計の仕組みづくり、作った後の修正・保守までを同じ月額に含めています。月次の集計を手放した会社の例は、月次の集計・報告を手放した3社の事例をご覧ください。

よくある質問

Q. importrange で取り込んだデータは、元のファイルを見られない人にも見えますか?

A. 見えます。importrange で取り込んだ内容は、集計側のファイルを開ける人なら誰でも見られます。元のファイルに見せたくない列がある場合は、取り込む範囲をその列を除いた範囲にしてください。

Q. Excelでも同じことができますか?

A. 考え方は同じですが、使う機能が違います。この記事の手順は、Googleスプレッドシートを前提にしています。

まとめ

  • スプレッドシートの手作業の多くは「写す作業」で、標準の機能で自動にできる
  • 別ファイルからは importrange、条件で抜き出すなら filter、集計し直すなら query、フォームはリンク、資料はリンク付き貼り付け
  • 決まった時間に動かす、ファイルを作る・送る、まとめて処理する、はGASの領域

「この作業は、どのパターンで置き換えられるのか」——いまの作業の流れを教えていただければ、30分の無料相談で、関数で済むか、仕組みづくりが必要かをお伝えします。

スプレッドシートBPOについて相談する

参考にした情報

  • IMPORTRANGE(Google ドキュメント エディタ ヘルプ)
  • FILTER 関数(Google ドキュメント エディタ ヘルプ)
  • QUERY 関数(Google ドキュメント エディタ ヘルプ)
  • フォームの回答を保存する場所を選択する(Google ドキュメント エディタ ヘルプ)
  • Google スライドにグラフや表を追加、更新する(Google ドキュメント エディタ ヘルプ)