Excelの「リンクされた図」をGoogleスプレッドシートでもやりたい！を実現する スコアカード と 表グラフ

Excelの便利な機能「リンクされた図」。

これと同じようなことを Googleスプレッドシートでもやりたい！って要望が多いので、完全ではないもののmirが普段使っている代替テクについて書いてみます。

途中でGoogleスライドや GASも登場します。

既に活用してるよーって人もいるかと思いますが、参考になる気づきがあるかもしれませんので、是非最後までお読みください。（ついでにスキボタンをポチっちゃってください）

前回は同じく Excelの 改ページプレビューみたいなことをやりたい！を解決する Googelスプレッドシートを 範囲やシートを指定してPDFやCSV,Excel形式でダウンロードするハイパーリンクを生成！という生産性向上テクニックについて書きました。

Excelのリンクされた図（カメラ機能）とは

Excelでこの機能を使ったことがない人や Googleスプレッドシートだけしか使ってない人の為に、まずはExcelの「リンクされた図」という特殊貼り付けの機能、および同等機能と言われる「カメラ機能」、「テキストボックス、図形のセル参照」について軽く触れておきましょう。

Excelの「リンクされた図」機能

Excelの「リンクされた図」は、セル範囲をコピーした後で貼付け時に選択できる「貼り付けタイプ」の一つです。

「リンクされた図」という名前の通り、図としての貼り付けになっていることに加えて、コピーした元のセルと連動する（リンクする）ことで、数式による参照では出来ない色やフォントなどの見た目まで連動させることができます。

つまり

• シートの上にある図なので、セルの構成を気にせず、自由に配置できる

• 拡大、縮小も自由に出来る

といった 図としてのメリットに加えて

• 参照するセルの数式の結果に連動して図の数値や値が変化する

• 通常のセル参照では無理な 塗りつぶしの色や文字の色まで連動される

という リンク（リアルタイム参照）というメリットも兼ね備えた超便利機能です。

ただし負荷が高い機能なんで、多用すると動きが悪くなることがあります。

Exelのカメラ機能とは？

Excelにはこのリンクされた図 と似たような「カメラ」という機能があります。

結構昔からあるんですが、通常はリボンメニューには表示されていないので、使ったことが無い人も多いかもしれません。

「リンクされた図」と「カメラ」は、ほぼ同じように使えますが、見た目が若干違います。

リンクされた図は 枠なしで、参照するセルが特に塗りつぶしされていない場合は透過となりますが、カメラ機能で貼り付けた図は枠線がついて、塗りつぶしなしのセルは白として扱われます。

見た目の違いはありますが、基本的には同じものという理解でOK。好きな方を使うで問題ないかと思います。

面白いのは、参照（キャプチャ）しているセル範囲の上にある図形や画像も表示（連動して参照）するという点。これは凄い。

当然対象のセル範囲の上から図形がはみ出した場合は、このように見切れた感じになります。本当に定点カメラみたいですね。

Excelのテキストボックスは セル参照に出来る

もう1つ「リンクされた図」に近い機能が、テキストボックスのセル参照です。

デスクトップ版Excelでは、テキストボックスを挿入した後、テキストボックスを選択した状態（入力状態ではなく外枠をつかんだ状態）で、数式バーに

=A1

のように セルを参照する式を入れることで、テキストボックスの中身（テキスト）をセルに連動させることができます。（これも昔からあるんですが、知らない人も結構多い）

数式によるセルの参照なんで、フォントの色やサイズといった装飾はリンクされませんが、値が変更されると テキストボックス側もリアルタイムで連動します。

リンクされた図と違って、参照しているセルとは別の背景色や文字サイズ、文字の色を指定したい！って時は便利ですね。

ちなみにテキストボックスに設定できるのは、あくまでもセル参照だけで 関数などを組み合わせることは出来ません。

テキストボックスではなく、図形でも同じようにセル参照設定ができます。便利な機能ですね。

Web版Excelでは「リンクされた図」（カメラ）は使えないけど

この便利な「リンクされた図」や「カメラ」は、残念ながらWeb版 Excelでは使えません。

そもそもWeb版Excelには貼付けオプションに 「図として貼付け」がありません。

つまり、デスクトップ版のExcelのように セル範囲をコピーして 図として貼付けることが出来ないってことです。

「リンクされた図」や「カメラ」を使ってるExcelファイルを アップロードしてWeb版Excelで開いた場合も、このようにエラーとなり表示はされません。残念！

しかし、テキストボックスのセル参照は Web版Excelでも機能します。

Web版Excelで「リンクされた図」みたいなことをやりたい場合は、このテキストボックス（図形）のセル参照を使う方法で代替しましょう！

ただしテキスト没空のセル参照は、新規作成や編集はWeb版では出来ません。

あくまでもデスクトップ版で作成したテキストボックス（図形）のセル参照がWeb版でも機能する、という限定的なものです。

Web版だけでは作れず、有料のデスクトップ版が必須となるのは残念です。

Googleスプレッドシートのリンク貼り付け

ようやく本題のGoogleスプレッドシートの話ですｗ

Web版のExcelで出来ないってことは、同じWebアプリである Googleスプレッドシートも 「リンクされた図」を実装するのは厳しそうですよね。

一応これに近い機能はあるんですが、ほぼ使い物になりません！

Googleスプレッドシートには リンク貼り付けがある！が・・・

Googleスプレッドシートの場合、セル範囲をコピーして

挿入 ＞ 図形描画　で 図形描画のダイアログを立ち上げ、

図形描画に コピーしたセル範囲を貼り付け際に「スプレッドシートにリンク」を選択

これによって リンクされた図 を生成することができます。

もちろん、セルの構成に縛られることなくシート上で自由に移動できますし、拡大や縮小もできます。

ですが、「リンク」（連動）の方はポンコツで、参照しているセルの更新が図に自動で反映されません。

更新は手動になるんですが、これも結構面倒です。

図をダブルクリック（またはメニューから編集を選択）して図形描画を開いた上で、図の「更新」ボタンを手動で選択、図形描画内で最新状態にしてから「保存」とする必要があります。

しかも図形描画内で更新をかけると、シート上でサイズを調整したものはリセットされてしまうというオマケつきｗ

つまり、リンクしても元のセルと 図は自動ではリンクせず、手動更新（しかも イチイチ 図形描画を開いて）で反映させる必要があるというクソ仕様なんです。

図形描画を立ち上げる必要があるんで、複数のリンクされた図をスプレッドシート上で一括更新が出来ません。1つ1つ対応する必要があります。

さらにこれはGASから操作が出来ず、どうやっても自動化が出来ないんです。泣けます。

Googleスライド にリンク貼り付けした「グラフ」だけは GASで更新できる

GASによるリンクされた図の更新ですが、スプレッドシート、ドキュメントにはメソッドが用意されていませんが、なぜかGoogleスライドだけはGASによる リンク更新のメソッドが用意されています。

ただし、リンクされた「表」はダメでリンクされた「グラフ」のみ更新ができるという謎の制限があります。。

たとえばGoogleスプレッドシートの 表と グラフを Googleスライドに リンク付きで貼付けした場合

元データのスプレッドシートの表と グラフの更新を反映させたい場合

Googleスライドのサイドバーに リンクされたオブジェクトの管理画面を表示させ、手動であれば 表もグラフもまとめて 「すべて更新」ボタンで更新することが出来ます。

この「リンクされたオブジェクト」の「すべて更新」ボタンは、スライドファイル内の他のページのリンクされた全てのグラフや表を一発更新できるんで、手動とはいえ便利ではあります。

せめて、Googleスプレッドシートもこんな感じでリンクされた図が一括更新できると良いんですけどね。

Googleスライド スプレッドシートからリンクされたグラフを更新する GAS

このリンクの更新をGASからやる場合、こんなコードになります。

const main = () => {
  SlidesApp
    .getActivePresentation()
    .getSlides()
    .forEach(s => {
      s.getSheetsCharts().forEach(c => c.refresh())
    })
}

メイン部分のコードは、これ以上シンプルなものは無いって感じなんで、こちらに掲載されたコードを使わせていただいております。

通常は 時間トリガーで対応するケースが多いんですが、今回は検証用にGAS実行用のカスタムメニューを表示させるコードを加えておきましょう。

const main = () => {
  SlidesApp
    .getActivePresentation()
    .getSlides()
    .forEach(s => {
      s.getSheetsCharts().forEach(c => c.refresh())
    })
}

//実行用メニューを追加
function onOpen() {
  const ui = SlidesApp.getUi(); 
  const menu = ui.createMenu('GAS実行'); 
  menu.addItem('グラフ更新', 'main');
  menu.addToUi();
}

main のコードの ポイントは SheetsChartクラスの refresh() 。これがリンクされたグラフを更新させるメソッドとなります。

アクティブな（GASのコードをコンテナバインドで記述した）このGoogleスライドの各スライド（ページ）の各グラフ（Charts）に対して １つずつrefresh() していくという、forEachを使った 二重ループです。

実際に実行してみると、こんな感じ。

最初に書いた通り、GASから リンクされた「グラフ」は更新できるんですが、「表」の更新は出来ないんです。。

この困った仕様に対してはかなり前からリクエストが上がってるんですが、一向にGoogle側が対応してくれません。

とりあえず現状をまとめると

このようになります。

Googleスプレッドシートの「リンクされた図」の代替案「スコアカード」

これらを完全ではないものの解決する為の一案が、Googleスプレッドシートのグラフ機能の「スコアカード」と「表グラフ」です。

図形や画像のようにセルの上に配置できて、参照しているセルの変化に合わせて自動更新されるグラフ機能を応用して、「リンクされた図」みたいなことが実現できます。

まずは「スコアカード」（「スコアカードグラフ」）を見ていきましょう。

Googleスプレッドシートのスコアカードグラフ

1つのセルを「リンクされた図」のように自由に セルの上に配置したい場合は、「スコアカード」というグラフが使えます。

スコアカードの基本設定・使い方 ↓

本来は経営ダッシュボードシートなんかを作る時に、集計で 合計や最大値を出力して、予算比や前年比を表示させる使い方をするものですが、

数式を入れた 1セルを参照させる使い方で、計算をさせた結果だけを でっかく表示させたり

数値ではなく 文字が入ったセルを参照させて

こんな感じに 文字であっても 「リンクされた図」風に、セルに連動して表示させることが出来ます。

残念ながら 文字の色やセル背景の色を連動させることは出来ませんが、GASや拡張機能を使わず、スプレッドシートの標準機能で作成できて、自由に配置 ＆ リアルタイム更新が実現できます。

「リンクされた図」というよりも、Excelのテキストボックスのセル参照に近い機能と言えるかもしれません。

スコアカードを Googleスライドにリンク付きで貼付け 日付を自動更新する

また、スコアカードはグラフなので、Googleスライドにリンク付きで貼った場合、GAS + 時間トリガー設定で自動更新させることができます。

たとえば、上のように TODAY関数を入れたせるを スコアカードグラフにしたものをスライドにリンク付きで貼れば

スライドにリンク付きで貼り付けて、先ほど登場した グラフを更新するGASを 時間トリガーで定期実行させることで、常に本日の日付を表示させる TODAY関数をGoogleスライドに入れたような状態に出来ます。

GASは使っていますが シンプルなコード1つをトリガー設定すれば、あとはスプレッドシート側で数式で値を変化させるだけで、スライド内の複数の可変にしたい箇所を全て更新できるのがよいですね。

ドーナツの中心に合計を表示させる複合グラフも可能

話は少しそれますが、スコアカードの応用例としてドーナツ円グラフと組み合わせるテクニックもあります。

こんな感じで先にドーナツグラフを作って

さらに数値の セル範囲を選択してスコアカードグラフを挿入。集計を「合計」として、背景と枠線を透明にしてサイズと配置をいい感じにすれば、

中央に合計値を表示させたドーナツグラフを作ることができます。

スコアカード使えそうですね！

Googleスプレッドシートの「リンクされた図」の代替案「表グラフ」

単体セルではなく複数セル範囲、つまり表を「リンクされた図」のように扱いたい場合は グラフの中の「表グラフ」で代替することが出来ます。

Googleスプレッドシートの表グラフ

「表グラフ」はグラフというよりは、表の画像化みたいな機能です。セル範囲の数値、値をそのまま出力できるのに加えて

リアルタイムで 昇順、降順で並び替えして表示させることも出来ます。

Googleスプレッドシートのグラフは、棒グラフや円グラフなどを大きい順にしたくても並び替えはグラフ機能では出来ないので、表グラフが並び替え出来るのは意外です。

表グラフは絶望的にカスタマイズができない

スプレッドシート内で「リンクした図」のように使ったり、スライドに表の代わりにGASで自動更新できる「表グラフ」をリンク貼り付けといった使い方が出来る 「表グラフ」。

超便利な機能・・・とは言えず、実は絶望的に カスタマイズができないという欠点があります。

カスタマイズできるのは

これだけです。表の背景色や枠線の色、文字の色や文字サイズなど、まったく見た目をいじることが出来ません。

列の幅のバランスすら調整できないんでお手上げです。

範囲サイズはマウスで変えることができますが、文字サイズは変わらず、単にグラフにスクロールバーが表示されるだけです。

もう少し字を大きくしたいって思う時が多いんですが、どうにもなりません。。

また、データの大半が数値である列にマジョリティーの文字列が入ったとき、なぜか少数派のデータ型が表示されないといった 仕様になっています。

これってQUERY関数の仕様に似てますね。

表グラフもデータから列の型を判断して、勝手に少数派を無視するといった仕様みたいです。

文字列だけの表であれば、普通に文字列のセルも表示されます。

カスタマイズの中で「改ページ」という設定がありますが、こちらはグラフ（表）を指定した行数ごとに ページ分けする機能で

面白いとは思いますが、どんな状況で活用できるのかイマイチわかりません。

さらにこの改ページを設定した状態でスライドに貼り付けすると

スライド上ではページめくりが機能せず、1ページ目以外が見れない状態となります。これでは意味がありません。

表グラフが使いづらい時は、スコアカードを組み合わせる方法も

このように表グラフは、見た目が期待したようにならないことが多く結構ストレスが溜まります。

せめて 文字サイズや色がカスタマイズできれば良いんですけどね。

というわけで、少ないセル数であれば スコアカードを 並べるという無理やりな方法もアリです。

これは、さも D2:E5範囲の表をリンクされた図としたように見えますが、実態は図形描画でセル範囲を図形化、タイトル列と枠だけ活かして中身は 4つのスコアカードをいい感じのサイズにして乗せてるだけです。

ちょっと手間はかかりますし、いまいち見た目が綺麗にならないことも多いんですが、どうしても必要な時にこの手を使ったりします。

Googleスプレッドシートの スコアカード、表グラフ を活用しよう！

今回は Excelのリンクされた図（カメラ機能）やセル参照テキストボックスに近いことができる Googleスプレッドシートの スコアカードグラフ、表グラフを紹介しました。

とりあえず、グラフであれば

• スプレッドシート上で自由に配置できて リアルタイム更新される

• Googleスライドにリンクして貼った時でもGASで更新を自動化できる

ってことで、メインとして使うのがスコアカード、状況によって表グラフも検討するのが良さそうです！

Googleスプレッドシートの図形描画はイマイチな機能ですが、「リンクされた図」もイマイチなんですよね。

Googleスプレッドシートの 図形描画のダメっぷりは、過去の noteでも触れています。

リアルタイムでなくてもいいからせめて 5分間隔とかで同期されるようになって欲しいもんです。もしくは GASでコントロールできるとか。

Googleさんは図形回りは改善する気がなさそうなんで、あまり期待は出来なそうですが・・・。いつの日か使いやすくなることを願っています！

次回はまた関数ネタでも書こうかなと。