スプレッドシートで別シートに自動反映する方法｜同じファイル・別ファイル・条件抽出の関数の選び方

この記事は、スプレッドシートの別シート・別ファイルへ値を自動で出す関数の選び方を扱います。表を分けてIDでつなぐ設計はスプレッドシートのデータベース化で、GASでできる範囲はGASでできることで扱っています。

「入力は元のシートに1回だけ、ほかのシートへは勝手に出てほしい」という相談は、コピーして貼る作業が毎週残っている会社から出てきます。貼り忘れと貼り間違いが起きるうえに、元を直しても貼ったほうは古いままになるためです。この記事では、反映する先が同じファイルか別ファイルか、全部か一部かで関数を選び、反映されなかったときの原因までを順に説明します。

反映先が同じファイルか、別ファイルか、一部の行かで関数を選ぶ

別シートへの自動反映は、同じファイルなら「=シート名!セル」、別ファイルならIMPORTRANGE、条件に合う行だけならQUERYかFILTERの3つから、反映元の場所と出したい範囲で選びます。下の図は、その3つの選び分けを示しています。

迷ったら、まず左の「どこから」を決めます。条件で絞るときは、IMPORTRANGEで取り込んだ範囲をQUERYに渡すこともできます。

3つの書き方を、そのまま貼れる形で並べます。シート名とセル範囲は、自分のファイルに合わせて書き換えてください。

使う場面	入力する式	補足
同じファイルの別シートから	`=受注台帳!B2`	元のセルを変えると、反映先も変わります。同じファイルの別シートを範囲ごと反映するには、反映先の先頭セルに「=受注台帳!A2:D」と入力します。結果は下のセルまで自動で広がります
別のファイルから	`=IMPORTRANGE("URL","受注台帳!A1:D")`	新しい参照元を初めて読み込むとき、「アクセスを許可」を押します。参照元のオーナーでない場合は、オーナーの許可が出るまで値は入りません
条件に合う行だけ	`=QUERY(受注台帳!A1:F,"select A,B,E where F = '未請求'",1)`	未請求だけ、今月分だけ、のように抜き出して別シートに並べます。FILTER関数でも同じことができます

シート名にスペースや記号が入っているときは、シート名を半角のシングルクォートで囲みます。たとえば「売上データ」のシートなら ='売上データ'!B2 と書きます。FILTERで同じ抽出をするなら、=FILTER(受注台帳!A2:E, 受注台帳!F2:F="未請求") のように、出したい範囲と条件の列を渡します。

自動反映されないときの原因は5つに絞れる

数式を入れたのに値が出ない、途中から反映が止まった、というときは、次の5つを上から順に見ます。

反映先のセルに値を直接打ち込んだ：範囲で出した数式は、下のセルまで結果を広げます。広がる先に既存の値が入っていると、結果がそのデータを上書きしてしまうため展開されない、という #REF! エラーが出ます。邪魔なセルを消せば直ります
IMPORTRANGEの許可を押していない：新しい参照元を初めて読み込むと、セルに #REF! と「アクセスを許可」のボタンが出ます。押すまで値は入りません。参照元のオーナーでない場合は、アクセス権をリクエストし、オーナーが許可するまで待ちます
IMPORTRANGEで参照しているシートの名前を変えた：同じファイル内の参照はシート名を変えても追従します。IMPORTRANGEの文字列の中のシート名は追従しないので、手で直します
範囲を A1:D100 のように固定している：101行目以降は反映されません。行が増える表は A:D や A2:D のように、終わりを決めない書き方にします
反映元の列を入れ替えた、削除した：列の位置で指定しているQUERYの "select A,B" は、列を入れ替えると別の列を拾います。列を動かしたら、数式の列記号も見直します

とくに1番目は、他の人が良かれと思って反映先の表を直して起きます。反映先のシートは見るための表と決め、見出しの色を変えて「ここは直さない」と分かるようにしておくと、直接入力を減らせます。確実に防ぎたいときは、シートの保護で反映先を編集できる人を絞ります。

基準値の表を別シートに置くと、変えたときに全件へ反映される

別シートへの自動反映が最も効くのは、計算に使う基準値の置き場所です。不動産会社の物件査定シートの相談では、利回りなどの基準値が月ごとに変わることがあり、「基準を更新したら、すでに査定した物件も最新の条件で計算し直せますか」という質問が出ていました。基準値を査定の行に直接書かず、別シートの基準表に1か所だけ置き、各行は数式で参照すると、基準表を直した時点ですべての行が計算し直されます。

基準は1か所、使う側は参照だけにします。下の点線は、値を貼り付けて固定した場合に起きることです。

反対に、基準値を貼り付けて固定する作りにすると、基準を変えたあとに計算した物件と、変える前に計算した物件で、同じ条件の数字が食い違います。参照式にすれば、基準表を直した時点で全件が最新の基準で計算し直されます。基準を更新するたびに全件を計算し直し、どの時点の基準で出した数字かを残す、という整理が現場では必要になります。あとは、基準を変えた日を基準表の隣に1行書いておけば、数字の食い違いを説明できます。

建築年月から築年数を自動で出す

同じシートの中でも、入力を減らす形で使えます。たとえば同じ物件査定シートでは、築年数を手で換算させる代わりに、建築年月を日付で入れると築年数が出る形を試しています。式は =DATEDIF(B2,TODAY(),"Y") のようになります。TODAY関数は開いた日の日付で計算するので、築年数は年が変わると更新されます。査定した日の築年数を残したい行は、日付を別の列に入れておきます。なお、このシートは試用の段階で、削減できた時間の実績はまだ出ていません。

コピー貼りが残っている作業を洗い出したい方へ

3分の診断で、いまの業務のどこに手作業の転記が残っているかを確認できます。自動反映にする順番を決める材料にしてください。

3分の事業基盤診断を受ける

効果は、転記にかけていた時間と食い違いの件数で測る

自動反映に切り替える前に、2週間分の数字を取っておきます。見るのは次の2つです。

転記にかけていた時間：元のシートから別のシートへ写す作業を、週に何分やっているかを記録します。切り替えたあとに同じ数え方で2週間測り、比べます
数字の食い違いの件数：「こちらのシートと数字が違う」と指摘された回数を記録します。減らなければ、反映先に値が直接入っている行が残っています

反映元を1枚にそろえるところから始めたい場合は、営業のシートを1枚にまとめた例を毎週月曜に2時間かけて営業数字を手集計している会社へで、集計した数字を1画面に出す作り方をスプレッドシートでダッシュボードを作る方法で紹介しています。

関数で足りないのは「書き込み」が要るとき

関数は元を見て表示するだけなので、受け取った時点の値を別シートに残したいときや、決まった時刻に転記したいときは関数では足りません。その場合はGASで、別シートへ値を書き込む処理を作ります。

履歴として残したい：フォームの回答が届いた時点の金額を別シートに記録し、あとから元の単価が変わっても過去分は動かさない、といった使い方です
決まった時刻に動かしたい：毎朝8時に前日分を集計して、報告用のシートへ転記する、といった使い方です
別のサービスへ出したい：シートの内容をChatへ通知する、PDFにして保存する、といった使い方です

GASで任せられる範囲と、任せないほうがいい範囲はGASでできることにまとめています。現場の報告をフォームで受けて1枚のシートに集める設計はGoogleフォームで現場の報告を1枚に集める設計が近い内容です。シートが重くなってきた場合は、関数を足す前にスプレッドシートが重いときの設計の直し方を先に見てください。

SalesDockは、業務の流れを先に整理してからシートや仕組みを組み立てる支援をしています。この記事のような反映の仕組みも、「誰がどこに入力し、どこで使うか」を決めたうえで組みます。

一次情報・公式情報

製品の仕様は変更されます。最新の情報は公表元でご確認ください。

スプレッドシートの別シート自動反映のよくある質問

スプレッドシートで別シートのデータを自動で反映するには？

同じファイル内なら、反映先のセルに「=シート名!セル」と入力します。元のセルを変えると、反映先も自動で変わります。別のファイルから取り込む場合は IMPORTRANGE 関数を使い、条件に合う行だけを出したい場合は QUERY 関数か FILTER 関数を使います。

別のスプレッドシートのデータを自動で反映できますか？

できます。IMPORTRANGE 関数に、参照元のURLと「シート名!範囲」を指定します。新しい参照元を初めて読み込むときは、セルに出る「アクセスを許可」を押す必要があります。参照元のオーナーでない場合は、オーナーの許可が出るまで値は入りません。

別シートに反映した値を、あとから手で直してもいいですか？

おすすめしません。数式の入ったセルを手で上書きすると、その行だけ反映が止まります。直すのは元のシートに限り、反映先のシートは見るための表として扱うと、食い違いが起きません。

関数ではなくGAS（Google Apps Script）が要るのはどんなときですか？

別シートへ「書き込む」処理が要るときです。たとえば、フォームの回答が届いた時点の値を別シートに記録して、あとから元が変わっても動かないようにしたい場合や、毎朝決まった時刻に集計を転記したい場合です。関数は元を見て表示するだけなので、元が変わると表示も変わります。