はじめに
最近、周りでGoogleスプレッドシートを使っている姿をよく見るようになってきましたが、意外と知られていない小技や機能、使い方がありましたので汎用性や重要度の高そうなモノをピックアップして紹介します。
集計や分析、調査、作業エビデンスの作成などの作業効率やモチベーションが大きく変わるので、参考にして頂けると幸いです。
また、エンジニアでも集計・分析などを手作業で行なっていたり、Markdownを使用した情報共有ツールにまとめて記載した結果 計算ミスや記載ミスが発生したり、追加作業する際に導出方法が前回と異なっていたり...と様々な問題を目にしますので、そういう方にもGoogleスプレッドシートを活用して頂ければと思います。
なお、サンプルとして紹介している画像はMacで操作したモノになりますので、Windowsのユーザーは適宜読み替えてご利用ください。
【貼り付け・取り込み】
・クリップボードの画像が貼り付けられる
スプレッドシートのメニューの挿入 > 画像から画像ファイルをアップロードして画像を貼り付ける姿をよく見みますが、画面キャプチャや図形オブジェクトをコピーした(クリップボードに格納された)画像は、画像の挿入操作をせずに、そのままスプレッドシートにCtrl+V(Macは⌘+V)で貼り付けることができます。
(1/4) ⌘+Ctrl+4で範囲選択の画面キャプチャモードにする
(2/4) 画面キャプチャの範囲を選択する
(3/4) スプレッドシートの貼り付けるセルを選択する
(4/4) ⌘+V で貼り付ける
意外と知らない人が多い上に、作業のエビデンスや資料作成などの大量の画像を扱う際には作業効率やモチベーションに大きく影響するので、是非覚えておいて欲しい小技です。
・表の形式を保持したまま貼り付けられる
Webサイトや表計算アプリなどの表をコピーした(クリップボードに格納された)オブジェクトも、表や形式を保持したままスプレッドシートにCtrl+V(Macは⌘+V)で貼り付けることができます。
(1/3) 表を選択して ⌘+C でコピーする
(2/3) スプレッドシートの貼り付けるセルを選択する
(3/3) ⌘+V で貼り付ける
また、セルに分割された状態でテキストだけ貼り付けたい場合はスプレッドシートにCtrl+Shift+V(Macは⌘+option+shift+V)で貼り付けることができます。
(1/3) 表を選択して ⌘+C でコピーする
(2/3) スプレッドシートの貼り付けるセルを選択する
(3/3) ⌘+option+shift+V で貼り付ける
表形式で公開されている情報の集計・計算を行う場合や、表の特定列のみ抜き出したい場合などに便利です。
・テキストを表として貼り付けられる
コマンドの実行結果やテキストエディタの文字列は、タブ区切りになっているとスプレッドシートにCtrl+Shift+V(Macは⌘+option+shift+V)でテキスト列の変換なしに貼り付けることができます。
$ echo -e "項目1\t項目2\t項目3\n1-1\t1-2\t1-3\n2-1\t2-2\t2-3"
項目1 項目2 項目3
1-1 1-2 1-3
2-1 2-2 2-3
$
(1/4) コンソールでコマンドを実行する(タブ区切り)
(2/4) 出力結果を選択して ⌘+C でコピーする
(3/4) スプレッドシートの貼り付けるセルを選択する
(4/4) ⌘+option+shift+V で貼り付ける
カンマやスペースが区切り文字の場合もテキスト列の変換で同様のことができますが、スプレッドシートのセルの選択状態が外れがちで変換するのも一手間かかるので、初めからコマンド結果をタブ区切りで出力するようにした方が楽に貼り付けられます。
また、sshなどでサーバーに入って作業する場合に調査結果をファイルに落としてローカルまで持ってくるのは面倒なので、サーバーに入ったまま調査結果をタブ区切りで出力させてスプレッドシートに貼り付ける方がゴミファイルも生成されず、楽に貼り付けられます。
調査結果をまとめた表をMarkdownで一生懸命作成した結果、可読性やメンテナンス性の低い表になったり、最悪スクロールできずに見えない部分が出来てしまったりと悲しい自体が発生してしまう場合があるので、そいう場合は是非スプレッドシートを活用してください。
また、どうしても長い表をMarkdownで表を記載したい場合は元データをスプレッドシートで作成し、テキストエディタやWebサイト等でMarkdown形式に変換して表を作成する事もできますので、活用してみてください。
-
VSCodeのmarkdown extendedでMarkdown形式の表を作成
-
Tables GeneratorでMarkdown形式の表を作成
-
esaではスプレッドシートを貼り付けると自動でMarkdown形式の表を作成してくれる
・セルを画像として貼り付けられる
スプレッドシートの表をコピーするとスプレッドシート内の画像やGoogleスライド内に貼り付けることができます。
(1/5) 表を選択して ⌘+C でコピーする
(2/5) 挿入 > 図形描画 を選択する
(3/5) ⌘+V で貼り付ける
(4/5) 貼り付けボタンを押す
(5/5) 保存して終了ボタンを押す
また、元のスプレッドシートのデータに変更が入った場合は参照先のデータも変更してくれるので、スプレッドシートとスライドを利用している場合は非常に便利です。
スプレッドシートではまだできませんが、エクセルだとオブジェクトをコピーすると画像データとして保存されるので、そのままスプレッドシートに貼り付けることができます。
(1/3) オブジェクトを選択して ⌘+C でコピーする
(2/3) スプレッドシートの貼り付けるセルを選択する
(3/3) ⌘+V で貼り付ける
また、画像データになっているのでスライドにもそのまま貼り付けられます。
同様にSlackなどアプリにも画像として貼り付けられます。
修正が頻繁に発生したり、ちょっとした作図を行う場合などにエクセルは重宝します。
ただ、Macのエクセルはオブジェクトの範囲選択ができないので、1シートに1つの画像としたいオブジェクトを配備し、オブジェクトの全選択機能で選択してからコピーするなどの工夫が必要になります。
・大量データのファイルを取り込む
大量のデータをコピー＆ペーストでスプレッドシートに貼り付けると、貼り付け終わるまでブラウザが固まったり、貼り付けに失敗したりします。
そういう場合はスプレッドシートのインポート機能を使用するとデータの取り込みができるようになります。
まずは大量のデータをファイルに落とし、スプレッドシートのメニューのファイル > インポート > アップロードを選択し、ファイルをドラッグ＆ドロップします。
スプレッドシートには格納できるデータに上限が設けられており、2022年12月17日現在では1,000万セル(または18,278列)が上限1となっています。
大量データをスプレッドシートで扱う場合は上限を超えないように気をつけてください。
なお今まで段階的に上限を引き上げているので、将来的にはさらに上限が引き上げられるかもしれません。
- 2019年:200万セルから500万セルに引き上げ
- 2022年:500万セルから1,000万セルに引き上げ
(ブラウザが耐えられるか心配ですが...)
【ショートカット】
スプレッドシートには様々なショートカットが用意されていますが、その中でも便利なショートカットや使い方について紹介しています。
また、スプレッドシートのショートカット全般については[公式]Google ヘルプ - ショートカットをご覧ください。
- 紹介するショートカット一覧
| Windows | Mac | |
|---|---|---|
| 値のみ貼り付け | ・Ctrl+Shift+V | ・⌘+option+shift+V (⌘+shift+V) |
| 端のセルに移動 | ・Ctrl+↑↓←→(十字キー) | ・⌘+↑↓←→(十字キー) |
| 端のセルまで連続して選択 | ・Ctrl+Shift+↑↓←→(十字キー) | ・⌘+shift+↑↓←→(十字キー) |
| 時刻を挿入 | ・Ctrl+Shift+: | ・⌘+shift+; |
| 複数セル選択 | ・Ctrl+(セル択) | ・⌘+(セル選択) |
・値のみ貼り付け
表の形式を保持したまま貼り付けられるでも記載しましたが、スプレッドシートやWebサイトなどでコピーしたオブジェクトはスタイルを持っているので、普通に貼り付けを行うとスタイルと値が一緒に貼り付けられてしまいます。
値だけ貼り付けたい場合はスプレッドシートのセル上でCtrl+Shift+V(Macの場合は⌘+option+shift+V)を押すとスタイルが付いていない値だけ貼り付けする事ができます。
Macの場合は⌘+shift+Vで値だけ貼り付けできるという記載がありますが、Macのショートカット設定次第では⌘+option+shift+V)が割り当てられていたりするので、うまく貼り付けできない場合はそれぞれのショートカットを試してみてください。
・端のセルに移動(連続して選択)
スプレッドシートで大量のデータを扱っている場合に一生懸命スクロールをして移動している姿を見かけますが、そういう場合はCtrl+↑↓←→(十字キー)(Macの場合は⌘+↑↓←→(十字キー))で端のセルに一気に移動する事ができます。
(1/6) セルを選択する
(2/6) ⌘+↓で連続しているセルの一番下に移動
(3/6) ⌘+↑で連続しているセルの一番上に移動
(4/6) ⌘+→で連続しているセルの一番右に移動
(5/6) ⌘+↓で空白の直前まで移動
(6/6) ⌘+↓で値が入っているセルに移動
また、大量データのセルを選択する際にスクロースしながらマウスで選択するのは大変なので、そういう場合はCtrl+Shift+↑↓←→(十字キー)(Macの場合は⌘+shift+↑↓←→(十字キー))で一気に選択する事ができます。
(1/3) セルを選択する
(2/3) ⌘+ shift +→で連続しているセルの一番右に移動しながら選択する
(3/3) ⌘+ shift +↓で連続しているセルの一番下に移動しながら選択する
一つのシートで作業エビデンスやメモを作成する際に左側の列に目印をつけておくとCtrl+↑↓←→(十字キー)(Macの場合は⌘+↑↓←→(十字キー))で一気に飛べるので便利です。
例えばA列は大項目、B列は中項目の目印用の列として用意しておき、エビデンスやメモはC列以降に記載していくようにすると、大項目を移動する際にはA列で移動、中項目を移動する際はB列で移動する事ができます。
(1/4) 中項目のB列を選択する
(2/4) ⌘+↓で次の中項目に移動
(3/4) 大項目のA列を選択する
(4/4) ⌘+↓で次の大項目に移動
・時刻を挿入
作業エビデンスを作成していて画面キャプチャや調査ログをスプレッドシートに貼り付る際に、作業時刻も一緒に記載したいけど手打ちするのが面倒で端折ってしまう姿を見たりします。
そういう場合Ctrl+Shift+:(Macの場合は⌘+shift+;)で時刻を入力できるので、エビデンスを作成する際などに活用してもらえればと思います。
(1/3) セルを選択する
(2/3) ⌘+shift+; で時刻を入力する
(3/3) ⌘+V で画面キャプチャを貼り付ける
・複数セル選択
罫線を引くときやセル操作する際に飛び飛びのセルを選択したくなる場合がありますが、そいう場合はCtrl(Macの場合は⌘)を押しながらセルを選択すると複数セルを選択する事ができます。
(1/2) ⌘を押しながらセルを選択する
(2/2) 下の罫線を選択する
複数列・行選択なども行えるので、書式変更や表示形式変更、またこの後紹介する簡易集計機能などに便利に使用する事ができます。
【小技機能】
・簡易集計機能
スプレッドシートで部分的にセルを選択して合計値や平均値などが欲しい場合、セルに関数を記述して計算を行なっている姿をよく見ます。
実は、対象セルを選択するだけで右下に計算結果が表示されるので、計算用のセルがない場合やちょっと確認をしたい場合などでは非常に便利です。
(1/2) 計算したいセルを選択する
(2/2) 右下の計算結果を確認する
・セルへのリンクが作れる
対象のシートを表示した状態でブラウザのURLをコピーすると、対象シートへのリンクを取得する事ができますが、実はシート内の特定セルへのリンクも取得・作成する事ができます。
また、特定セルへのリンクに遷移すると、特定セルが一番左上に来て選択状態で表示されるので、シートの記述が長くなったり紹介や説明をするために、特定セルを選択した状態でリンクを作成したい場合は非常に便利です。
- リンクを取得する
特定セルを右クリックし、セルでの他の操作項目を表示 > このセルへのリンクを取得でクリップボードに特定セルへ遷移するURLが格納されます。
(1/3) 遷移先のセルを選択して右クリック
(2/3) セルでの他の操作項目を表示
(3/3) このセルへのリンクを取得
- リンクを作成する
スプレッドシートのURLの末尾に&range=(対象セル)を追加すると対象セルへのリンクとなります。例えばURLが下記の場合にC4のセルへのリンクを作成しようとした場合は末尾に&range=C4を追加します。
https://https://docs.google.com/spreadsheets/xxxx/edit#gid=0
↓&range=C4を追加
https://https://docs.google.com/spreadsheets/xxxx/edit#gid=0&range=C4
・セル毎に編集履歴が見られる
スプレッドシートの特定のセルの変更履歴を確認する際にメニューのファイル > 変更履歴から地道に特定セルの変更履歴を探している姿を見かけますが、実は特定セルを右クリックし編集履歴を表示を選択すると編集履歴(変更履歴)が表示されます。
また、編集履歴画面の<をクリックすると過去の編集履歴を辿れるようになっています。
2019年06月から追加された機能なのですが、意外と知らない方が多いようなので是非活用して頂ければと思います。
(大量のファイルの変更履歴から特定セルの変更を探すのは非常に時間のかかる作業なので、もしそういう方を見かけた場合は本機能を教えてあげてください)
それ以外にも様々な変更履歴を見る方法がありますので、興味のある方は[公式]Google ヘルプ - ファイルの変更内容を確認するを参照してください。
【便利機能】
・ピポットテーブル
ログやトランザクションデータなどの蓄積されたデータの集計・分析を行う際は、ピポットテーブルの使用を検討してみてください。
例えば日時、url、respons_timeが記載されているログがスプレッドシートにある場合に、ログのセルを選択してメニューの挿入>ピポットテーブルを選択してピポットテーブルを作成すると、日時単位やURL単位などでrespons_timeを集計する事ができるので、どんなURLがどの程度存在するのか、どのURLのrespons_timeが高いのか、などの分析をする事ができます。
(1/4) ⌘+shift+→↓でセルを選択する
(2/4) 挿入>ピポットテーブル>新しいシートを選択する
(3/4) 行:日時、列:url、値:respons_timeを選択する
(4/4) 日時セルを右クリックし、集計単位を時単位に設定する
上記のようにピポットテーブルでは元データを二次元的に変更できたり、色々な軸で集計・分析する事ができるので非常に便利です。
他にも様々な機能がありますので、興味のある方は是非調べてみてください。
・条件付き書式
セルがある条件を満たしている場合にセルの背景色、文字色、罫線などの書式を自動的に設定できる条件付き書式という機能があります。
管理台帳の作成などに使われる機会が多いですが、ステータスが完了になっている行の背景色を自動的に灰色に変更する事ができます。
(1/4) ⌘+shift+→↓でセルを選択する
(2/4) 表示形式>条件付き書式を選択する
(3/4) 書式ルールでカスタム数式を選択する
(4/4) 値または数式に「=$B1="完了”」を設定し、塗り潰しで灰色を設定する
また、修正前後の差分や正常ログと異常ログの差分を視覚的に表示する際にも活用できます。
(1/4) ⌘+shift+→↓でセルを選択する
(2/4) 表示形式>条件付き書式を選択する
(3/4) 書式ルールでカスタム数式を選択する
(4/4) 値または数式に「=A2<>D2」を設定し、塗り潰しで赤色を設定する
・SPARKLINE関数(簡易グラフ)
通常グラフを作成する際はメニューの挿入 > グラフなどでグラフオブジェクトを作成すると思いますが、実はSPARKLINE関数を使用してセル内に簡易グラフを表示させることができます。
各セルの値を視覚的に表示したい、動的に値が変わるので変わったタイミングでグラフも変えたい、スライドなどに表として貼り付けたい、といった場合に便利です。
また、詳細を知りたい場合は[公式]Google ヘルプ - SPARKLINEを参照してください。
・タイムライン(ガントチャート)
突発プロジェクトやプロジェクト管理ツールがない状態でスケジュール管理する場合に、自前でガントチャートを作成しているケースがありますが、そういう場合はタイムラインという機能を使用すると簡単にガントチャートを作成する事ができます。
スプレッドシートのセルにグループ、タイトル、詳細、開始日、終了日、色を用意しておき、対象セルを選択した状態でメニューの挿入>タイムラインを選択し、それぞれの項目を割り当てると自動でガントチャートを作成してくれます。
(1/3) ⌘+shift+→↓でセルを選択する
(2/3) 挿入>タイムラインを選択する
(3/3) 開始日、終了日、タイトル、色、詳細、グループを設定する
他にも週末の設定や表示単位などを変更できたりするので、詳細を知りたい場合は[公式]Google ヘルプ - タイムライン ビューを参照してください。
タイムラインは2022年11月から追加された機能で、個人のGoogleアカウント、G Suite Basic、Businessでは使用できないので注意してください。
また、今後も機能のアップデートを予定しているようなので、
おわりに
上記で紹介した以外にスプレッドシートの名前付き関数2やマクロ3、GoogleSheetsAPI4やGAS(Google Apps Script)など高度で便利な機能が沢山あるので、機会があれば紹介していきたいと思います。
また、スプレッドシートのアップデート情報は、毎年行なっているGoogle Cloud NextやGoogle Workspaceアップデートブログなどでも紹介されているので興味のある方は是非見てみてください。
-
https://support.google.com/drive/answer/37603?hl=ja#:~:text=%E2%80%8BGoogle%20%E3%82%B9%E3%83%97%E3%83%AC%E3%83%83%E3%83%89%E3%82%B7%E3%83%BC%E3%83%88%E3%81%A7,%E5%88%97%EF%BC%88%E5%88%97%20ZZZ%EF%BC%89%E3%81%BE%E3%81%A7%E3%80%82 ↩
-
https://workspaceupdates-ja.googleblog.com/2022/09/blog-post.html?m=0 ↩