Googleスプレッドシート最強ショートカット Ctrl +Y (F4)とカラースケールと 2（マクロの記録）

mir

2024年2月18日 14:31

ショートカットネタの続きです。

前回、mirが推す Googleスプレッドシートの最強ショートカット Ctrl + Y （F4キー）について書きました。

色々書いていたら長くなってしまったので、前回はタイトルにあるもう一つのテーマ「カラースケール」にたどり着く前に終わってしまいましたｗ

今回はようやく、カラースケールと Ctrl + Y（F４キー）って話に入っていきます。そして、GAS（マクロ記録）やシート関数、フリーの便利ツールも登場！

前回の note

条件付き書式と F4キーショートカット

先に少し前回のおさらいをしながら、条件付き書式とF4キーショートカットについて触れておきます。

なお、以降は Ctrl + Y （または Ctrl + Shift + Z）のショートカットは、Googleスプレッドシートでは同じ動作となる F4 キーとして記載していきます。

個人的には 1キーで完結できるF4 キーが最強かなってことで、F4利用を推してますが、3つのうち好きな方法をご利用ください。

前回から F4最強ってやたら書いてるんで、なんか Flower four（花の四人組）推しみたいんですねｗ

古いネタで失礼。

連続でセルの書式設定は F4キーで

F4キーは公式では「やり直し」（海外版だと Redo）という言い方ですが、Googleスプレッドシートでの実際の使い方としては「1つ前に行った処理を選択したセルにも繰り返す」というイメージです。

複雑な処理を繰り返すことは無理ですが、上のようなセルを指定の色にするや 文字色を変える、太字にする など、シンプルな 書式設定を目視で適用していく作業などは、F4キーを使うことで 超効率化することが出来ます。

もちろん、ロジックが決まってるのであれば 条件付き書式で設定（自動化）した方が効率的なんですが、世の中の手作業は意外とロジック化できないものが多いのも実情なんですよね。

また Excelと違って Googleスプレッドシートの条件付き書式で使える書式設定は非常に制限されています。

フォントサイズやゴシック、明朝、メイリオといったフォントの種類、表示形式、セルの罫線は、条件付き書式で使うことができません。

どうしてもこれらを適用させたい場合は、GASを使うか、GASを組むほどでもない、もしくはGASはよくわかりませんって場合は、手作業となってきます。

このような 手作業の負担、時間を軽減するのが F4キーのショートカットであると、覚えておきましょう。

条件付き書式はコピペを繰り返す方法で対応

上のようなセルを この色にする や、文字を太字にするといった、1つの書式設定であれば F4キーで繰り返しが可能です。

しかし、セル色を明るいオレンジ3、文字色を赤、さらに太字にするという複合書式設定は、そのまま F4キーで繰り返すことはできません。

このような複合設定をF4で繰り返そうとすると、最後に設定した書式（上の場合は太字化）のみの繰り返しとなってしまいます。

また、条件付き書式のような サイドバーで諸々設定するような処理は、そもそも F4キーでは繰り返しが出来ません。

このような場合は手動で設定したセルを

コピー → 書式を貼り付け（もしくは条件付き書式を貼り付け）

を最初の1回は手動実行してから、この「書式を貼り付け」をF4キーで繰り返すという方法が有効です。

Q1. 年齢が30以下の人の名前と年齢の両方のセルに条件付き書式で色を付けたい

いきなりですが、ここで軽めの条件付き書式のお題をいってみましょう。

上のように B2:C10セルに「名前、年齢」が入っている表で、年齢が30以下の人の名前（B列）と年齢（C列）の両方のセルを条件付き書式で塗りつぶしたい場合、どのように設定すればよいでしょうか？

今回のケースは、名前セル（B列）は他のセルの数値を基準とするので、カスタム数式を使う必要があります。

これは基本ですから出来ますよね？考えてみましょう！

すぐに回答いきます。
↓↓↓

A1. 年齢が30以下の人の名前と年齢の両方のセルに条件付き書式で色を付ける

正解はこちら。

カスタム数式で

=$C2<=30

このように設定します。（他の書き方もありますが、これが基本形です）

絶対参照の理解は必要になりますが、割と簡単ですね。

条件付き書式については、最近の noteでも触れております。

絶対参照、相対参照の理解 は条件付き書式でカスタム数式を使う上で、必須となります。

理解が甘い方は、Excel系のブログで詳しいところがいっぱいあります。（考え方はExcelと一緒です）しっかり理解しておきましょう。

F4キーは絶対参照 ⇔ 相対参照の切り替えショートカットでもある

今回、「やり直し」（一つ前の動作の繰り返し）をする最強ショートカットキーとして F4を紹介していますが、実はこのキーは別のショートカットキーの顔があります。

それは、セル内の数式においてセル参照の 絶対参照 ⇔ 相対参照 を切り替える機能です。

これも Excelでもお馴染みのショートカットです。

で、単体セルの場合は Excelと同じ感覚で使えるんですが、セル範囲の場合は ExcelとGoogleスプレッドシートではちょっと違います。

Googleスプレッドシートは、例えば片側の A10 だけを選択したとしても、 常に始点A1と終点A10 どちらも絶対参照に切り替わります。

よくある始点だけ絶対参照に切り替えたいといった、片側だけ絶対参照切り替えがF4ショートカットで出来ません。（もし方法がありましたら教えてください）

一方、Excelは選択した側だけ、両方選択したら両方とF4キーによる絶対参照切り替え対象を思い通りに指定できます。

やはり細かいところでよく出来てます！

ついでに言うと、Googleスプレッドシートの条件付き書式のカスタム数式では、F4キーによる絶対参照切り替えが効きません。

これも結構不便だったりします。

F4ショートカットキーのもう一つの顔、絶対参照・相対参照切り替えという余談でした。

Q2. 横のテーブル（表）に条件付き書式をコピーしても使えるようにしたい

本題の条件付き書式の話に戻ります。

先ほどの回答は単体のテーブル（表）であれば問題ないのですが、今回は最終的に 他のテーブル（表）に条件付き書式のコピペを繰り返したいと考えています。

この場合、ちょっと問題があります。

表は 縦2 × 横2 でシートに配置されているんですが、下のBチームは問題なく30歳以下が色付けできるものの、Cチーム、Dチームは gif動画の通り表内の年齢ではなく隣の Aチーム、Bチームで色が付いた行と同じ行が色付けされてしまいます。

これはカスタム数式で

=$C2<=30

と参照する列を 絶対参照で C列に固定してしまっている為、横にある表では条件付き書式をコピーしたとしても、C列の年齢で条件判定されてしまう為です。

では、このように横の表に条件付き書式をコピーしても使えるようにするには、カスタム数式をどのように変更すればよいでしょうか？

なお、表の構成は4つとも同じとします。

お題2つ目チャレンジしてみましょう！

少し難しいですかね。回答です。
↓↓↓

A2. 横のテーブル（表）に条件付き書式をコピーしても使えるようにする

検証しながら回答していきましょう。

これは幾つか方法がありますが、例えば思いつくのが

=OR(B2<=30,C2<=30)

このような OR関数を使って、どちらかが30以下なら TRUEとなるカスタム数式を使う方法。

しかしこれはうまくいきません。

理由は 相対参照になっている為です。

B列の場合は問題ないんですが、C列のセル（たとえば C2セル）の場合

=OR(C2<=30,D2<=30)

このような式で判定されてしまいます。

そして、D列は空欄なので0扱いで30以下と見なされてしまう為、カスタム数式は TRUEを返し、C列が全て塗りつぶしとなってしまいます。

では、どうすればよいか？

そのまま対策をとると、空白ではないを条件に加えればよいので

=OR(B2<=30,AND(C2<=30,C2<>""))

回答その1

という式を使ってもよいです。これも一応回答となります。

ただ、ORとANDが両方出てきて式がわかりづらいですよね。

というわけで、mirとしては別の式が良いかなと考えます。

この場合は MIN関数を使ったカスタム数式がおススメですね。

=MIN(B2:C2)<=30

回答その2

これはMIN関数が文字列や空白セルを無視する（空白を0として扱わない）という性質を利用しています。

また、別解となりますが表の構成が全て同じという前提を利用した

=if(B$1="氏名",C2,B2)<=30

回答その3

というカスタム数式を使う方法もあります。

条件付き書式（カスタム数式）とF4ショートカットを組み合わせる際の注意点

では回答2の条件付き書式を コピペと繰り返しで正しく他の表に適用できるか確認してみましょう。

いい感じですね。F4ショートカットが条件付き書式でも活用できました。

条件付き書式（カスタム数式）を書式コピペ、およびショートカットF4キーで繰り返しする際は、

絶対参照があるとうまく機能しないことがある
カスタム数式は工夫が必要な時もある

という注意点があることを意識しましょう！

カラースケール機能で F4キー（Ctrl + Y）は使えるの？

今回のもう一つのテーマである「カラースケール」機能の話に入っていきましょう。（ようやくｗ）

Googleスプレッドシートのカラースケールとは？

カラースケールは元々 Excelにあった条件付き書式の特殊機能の一つで、色のグラデーションでセルの値の大小を表す機能です。

Excel の条件付き書式の特殊機能 カラーバーや アイコンセットは輸入されませんでしたが、このカラースケールだけは Googleスプレッドシートにも実装されました。

ちなみに Googleスプレッドシートを Excel形式でダウンロードした場合も、このカラースケールの条件付き書式は引き継がれます。

基本的にはデフォルトカラーの中から、範囲内の最大値が濃い目の色、もしくは最小値が濃いめの色で徐々に白（無色）となるグラデーションを使うのがおススメですが、自分で色を設定することも可能です。

また、こちらは 数値セルに対して適用するもので、テキストが入ったセルには影響がありませんし、特定の列の数値をもとに その行全体にカラースケールを適用させるといったことは出来ません。

もう一つの特徴として、Googleスプレッドシートでは条件付き書式は一つしか適用できないというガッカリな縛りがあるんですが、カラースケールだけは特殊で、文字装飾の条件付き書式と組み合わせて使うことができます。

それについては過去の noteで触れています。

順位や数値の大小を視覚的にわかりやすくするのに便利な機能です。

カラースケールを F4キーで繰り返すけどコレじゃない・・・

先ほど書いた通り通常の条件付き書式は、カスタム数式の場合は注意が必要ですが、F4キーで書式貼付け（または条件付き書式のみ貼付け）を繰り返することが出来ます。

しかし、カラースケールの書式コピぺ（F4繰り返し）だと、これが思うようにいきません。

上の画像は F4キーで点数が大きい方をより濃い緑になるようにカラースケールを適用したものを他の教科（列）に書式貼付け F4キー繰返しをしたんですが、列毎のカラースケールにはならず 全体で一つのカラースケールにまとめられてしまいました。

本来やりたいことは個々の範囲（列）ごとの

↑ このようなカラースケールです。

もしや隣接する（隣の）セルにカラースケールの書式を貼り付けるから一塊にされてしまうのかと、間をあけて条件付き書式の貼り付けをしても、このように 飛び飛びの範囲に同じカラースケールが適用されてしまいます。

Googleスプレッドシートは負荷軽減の為なのかわかりませんが、同じシート内の条件つき書式を一つにまとめようとする仕様になってるってことです。

これだと 最強ショートカット F4キーでもお手上げです。

Excelだと個々の範囲のカラースケールを書式貼付け出来る

ちなみに Excelの書式貼付けであれば、選択したセル範囲ごとのカラースケールが適用できます。

やはり、この手の痒い所に手が届く感は Excelが強いですね。

ちなみにこのExcelを Googleドライブにアップロードして スプレッドシートに変換すると

このように 個々のカラースケールが保持された状態となります。

この方法で対応するって回避策もアリですが、ちょっと面倒ですしExcel持ってないと使えない方法なんで、出来ればGoogleスプレッドシートで解決したいところです。

では、個々にカラースケール設定を手動で繰り返す以外にいい方法はないものか？

マクロの記録を使ってみる

このショートカットで簡単に範囲毎にカラースケールを適用する方法ですが、裏技的な方法も探してみたんですが残念ながら見あたりません。

やはりGAS、マクロは全てを解決する・・・・！！　ってことですね。

自分でゼロからコードを書いてもいいんですが、今回は初心者でも割と使いやすい マクロの記録 を使う方法でやってみましょう。

もちろん、まったくGAS触ったことない人にはハードル高いかもですが、この作業が定期的に発生する人は効率化のチャンスです。

プログラミング怖い！って人も、簡単なんで一歩踏み出してみましょう。

このマクロの記録でポイントが2つあります

カラースケールの適用範囲を選択した状態でマクロ記録をスタートする
マクロ記録は「対参照を使用」を選択する

先に一つ目のカラースケールの適用範囲（C2:C5）を選択した状態で

メニューの

拡張機能　＞　マクロ　＞　マクロを記録

で、記録をスタートします。

このようにしないと最初にセルを選択する動きが相対参照で実行時に再現されてしまいますｗ（見当違いのセルに適用されます）

そして画面下に以下のダイアログがが出てきたら、

絶対参照ではなく、相対参照の方を選択します。

絶対参照、相対参照は条件付き書式でも登場しましたが、マクロでも出てきました。やはり表計算ソフトを使う上で、重要な要素ってことです！

以前はマクロの記録にこの設定がなかったので、記録されたコードを書き替える必要がありました。

現在はここの設定を変えるだけで 「選択したセル」にその動作を実行するというコードになります。だいぶ初心者に優しくなったかなと。

あとは、範囲（C2:C5）に条件付き書式のカラースケール（数値が大きいほど濃いグリーン）を適用し、処理が完了したらマクロに名前をつけて保存しましょう。

マクロの名前はなんでもよいです。あと、ショートカットの方は適当に数字の1あたりを入れておくのがよいでしょう。

これらは後で変更可能です。

保存してすぐであれば、左下にこのように 「スクリプトを編集」という表示がでますが、これはしばらくすると消えてしまいます。

その後、マクロ記録で生成されたコードの確認や編集をする場合は、

メニューの

拡張機能＞ Apps Script

でスクリプトエディタを開き行うことができます。

これで「マクロの記録」は完了です。

記録したマクロを実行してみる

記録したマクロはメニューから、もしくは（設定した場合は）ショートカットキーで実行できます。

拡張機能＞マクロ＞ macro01（先ほど保存したマクロ名）

と選択、もしくは先ほど設定したショートカット

Ctrl + Alt + Shift + 1
※1は設定したキー

で実行可能です。※数字キーはテンキーからは使えないことがあります。

適用させたい範囲を選択して記録したマクロを実行してみましょう。

※初回は GASと同じで承認が必要になります。承認だけで終わってしまい実行されないことがありますが、その際はもう1回実行しましょう。

カラースケールが列単位で適用されましたね。

C列（国語）とD列（算数）が別で設定されているのがわかります。

成功です！

ここまでが GAS（プログラミング）の手前ラインなんで、これで十分って方はこのまま使っちゃってください。

ここら先に足を踏み入れることは、ルビコン川を渡ることになります！（そこまで重い話ではないｗ）

より良いマクロを使いたい！もっと理解を深めたい！って方は是非チャレンジを。知ってるひとは内容は簡単なんで読み飛ばしていただければＯＫ。

マクロの記録は、無駄な動きも記録されてしまう

さきほど実行してみた選択範囲にカラースケールを適用するマクロですが、ちょっと気になる点が・・・

スクリプト実行した時に、セルの色がコロコロ変わって、最後にカラーバーが適用されてますよね。これは何か？

よく見ていくと

このように条件付き書式を①、②と差し替えて最後に ③の本来適用したいカラースケールになっています。

これは、Googleスプレッドシートの条件付き書式を選択した時の、基本設定が自動で適用されてしまう仕様をそのまま記録してしまっている為です。

もちろん気にせずこのまま使ってもよいんですが、動作に無駄があるので出来れば最初から③を適用させたいですよね。

というわけで、記録したマクロで生成され記録したマクロの生成されたコードを確認、そして編集していくことになります。

スクリプトエディタの世界へ GOです！

記録したマクロのコードを確認、編集してみる

通常のGASと同じくメニューの

拡張機能　＞　AppScript

からエディタを開くとマクロ記録で生成されたコードが確認できます。

↓ こんなコードが記載されてました

/** @OnlyCurrentDoc */

function macro01() {
  var spreadsheet = SpreadsheetApp.getActive();
  var conditionalFormatRules = spreadsheet.getActiveSheet().getConditionalFormatRules();
  conditionalFormatRules.push(SpreadsheetApp.newConditionalFormatRule()
  .setRanges([spreadsheet.getActiveRange()])
  .whenCellNotEmpty()
  .setBackground('#B7E1CD')
  .build());
  spreadsheet.getActiveSheet().setConditionalFormatRules(conditionalFormatRules);
  conditionalFormatRules = spreadsheet.getActiveSheet().getConditionalFormatRules();
  conditionalFormatRules.splice(conditionalFormatRules.length - 1, 1, SpreadsheetApp.newConditionalFormatRule()
  .setRanges([spreadsheet.getActiveRange()])
  .setGradientMinpoint('#57BB8A')
  .setGradientMaxpoint('#FFFFFF')
  .build());
  spreadsheet.getActiveSheet().setConditionalFormatRules(conditionalFormatRules);
  conditionalFormatRules = spreadsheet.getActiveSheet().getConditionalFormatRules();
  conditionalFormatRules.splice(conditionalFormatRules.length - 1, 1, SpreadsheetApp.newConditionalFormatRule()
  .setRanges([spreadsheet.getActiveRange()])
  .setGradientMinpoint('#FFFFFF')
  .setGradientMaxpoint('#57BB8A')
  .build());
  spreadsheet.getActiveSheet().setConditionalFormatRules(conditionalFormatRules);
};

マクロ記録らしい・・・ごちゃついて読みづらいコードですね。

結構長いコードですが、これは先ほど書いた通り無駄に条件付き書式を設定しては変更してを繰り返しているからです。

コードを分解してみましょう。

var conditionalFormatRules = spreadsheet.getActiveSheet().getConditionalFormatRules();

この conditionalFormatRules が名前からして条件付き書式だってのはわかりますね。

で、コードは

/** @OnlyCurrentDoc */

function macro011() {
  var spreadsheet = SpreadsheetApp.getActive();
 
  //①「空白ではないセル」の条件付き書式
  var conditionalFormatRules = spreadsheet.getActiveSheet().getConditionalFormatRules();
  conditionalFormatRules.push(SpreadsheetApp.newConditionalFormatRule()
  .setRanges([spreadsheet.getActiveRange()])
  .whenCellNotEmpty()
  .setBackground('#B7E1CD')
  .build());
  spreadsheet.getActiveSheet().setConditionalFormatRules(conditionalFormatRules);

  //②数字が小さい方が色の濃いカラースケール
  conditionalFormatRules = spreadsheet.getActiveSheet().getConditionalFormatRules();
  conditionalFormatRules.splice(conditionalFormatRules.length - 1, 1, SpreadsheetApp.newConditionalFormatRule()
  .setRanges([spreadsheet.getActiveRange()])
  .setGradientMinpoint('#57BB8A')
  .setGradientMaxpoint('#FFFFFF')
  .build());
  spreadsheet.getActiveSheet().setConditionalFormatRules(conditionalFormatRules);

  //③数字が大きい方が濃いカラースケール
  conditionalFormatRules = spreadsheet.getActiveSheet().getConditionalFormatRules();
  conditionalFormatRules.splice(conditionalFormatRules.length - 1, 1, SpreadsheetApp.newConditionalFormatRule()
  .setRanges([spreadsheet.getActiveRange()])
  .setGradientMinpoint('#FFFFFF')
  .setGradientMaxpoint('#57BB8A')
  .build());
  spreadsheet.getActiveSheet().setConditionalFormatRules(conditionalFormatRules);

};

このように条件付き書式ごとに 3つに分けることが出来ます。

ちなみにちょっとメソッドの理解が必要ですが ②と③は

conditionalFormatRules.splice(conditionalFormatRules.length - 1, 1, SpreadsheetApp.newConditionalFormatRule()

このように spliceというメソッドが使われています。これは条件付き書式の差し替えをの処理となっています。

条件付き書式を新しく作成するのは ①の書き方

conditionalFormatRules.push(SpreadsheetApp.newConditionalFormatRule()

pushを使ったものです。

そして

SpreadsheetApp.newConditionalFormatRule()　～　build()

の部分が実際の条件付き書式の条件設定となります。

※今回は条件付き書式のGASを学ぶ回という趣旨ではないので、この部分の説明は割愛します。

というわけで、（なんとなーくで良いので） ①の書式設定の部分を ③の書式設定の記述に差し替えれば、あとの②、③は不要ってことだとわかりますね。

つまり

こういうことです。

そうするとコードは

/** @OnlyCurrentDoc */

function macro011() {
  var spreadsheet = SpreadsheetApp.getActive();
 
  //数字が大きい方が濃いカラースケール
  var conditionalFormatRules = spreadsheet.getActiveSheet().getConditionalFormatRules();
  conditionalFormatRules.push(SpreadsheetApp.newConditionalFormatRule()
  .setRanges([spreadsheet.getActiveRange()])
  .setGradientMinpoint('#FFFFFF')
  .setGradientMaxpoint('#57BB8A')
  .build());
  spreadsheet.getActiveSheet().setConditionalFormatRules(conditionalFormatRules);

};

このようになります。だいぶすっきりしましたね。

これを保存し、再度マクロ実行で正しくカラースケールが適用されるか確認してみましょう。

※ Googleスプレッドシートのマクロによるシートへの処理は、ExcelのVBAと違って「元に戻すボタン」（前回紹介の Ctrl + Z）で戻すことが出来ます。

記録しただけの時のマクロ実行した際のコロコロ色が変わる無駄な挙動がなくなり、シンプルにカラースケールが適用されました。

編集したマクロをショートカット実行してみる

それでは今回のテーマであるショートカットでマクロ実行をやってみましょう。

もしマクロの保存時にショートカットキーの設定を飛ばしてしまった場合は、

拡張機能　＞　マクロ　＞　マクロを管理

で再設定（変更)できます。

いかがでしょうか？

列毎に範囲選択 → ショートカット Ctrl + Alt + Shift + 1

を繰り返すことで、列毎のカラースケール適用が出来ました。

もちろん、GASのコードを理解していれば forループなどを使ったコードに改変して、1回実行するだけで範囲内のセルを列毎にカラースケールを適用するって書き換えができます。

でも、GASがあまりわからない人でも、これくらいは割と簡単にできるってことです。

最強ショートカット F4キーが使えない場合（範囲毎のカラースケールなど）の繰り返し作業では、

マクロ記録 + ショートカット実行

が有効であることが理解できましたでしょうか？

ショートカットは作れる PowerToys

最後にGoogleスプレッドシートから少し外れますが、オマケネタです。

先ほどマクロ実行はショートカットを使えると言いましたが、

Ctrl + Alt + Shift + 1

という4点押しで、これ片手で実行するのはかなり厳しいですよね。

でもマクロの記録機能では Ctrl + Alt + Shift は必須なんでキーはもう少し下を選んでも 4点押しは変えようがないし・・・という方に最後に便利なツールを紹介しておきましょう。

Windowsユーザーなら知ってる人は知っている PowerToys というツールです。

Windowsにあったら便利だなというツールを詰め込んだ、彦摩呂の食レポじゃないですが

「便利ツールの宝石箱や～」

と言いたくなるソフトです。

GitHub経由で exeファイルからインストールする方法もありますが、Microsoftのアプリストアからもインストール可能。（不安な人はアプリストアからがおススメです）

インストールの流れや、今回紹介する機能以外にもたくさんの機能があるので、興味がある方は他サイトも参考にして試しに入れてみてください。

KeyBoard Manager のショートカットの再マップを利用する

PowerToysの中にある KeyBoard Manager はキーを入れ替えたり、キーボードショートカットを別のキーボードの組み合わせで実行させるといったことが出来ます。

たとえば

Ctrl + Alt + Shift + 1　を左手で押しやすい　Alt + V に差し替えたい

と言った場合、

キーボードマネージャーの ショートカットの再マップでこのように設定すればOKです。

ターゲットアプリを chrome.exe に指定してるので、Chrome上でのみ このショートカット代替が機能します。

こんな感じで登録されたらOK。

再マップした簡略ショートカットからマクロ実行してみる

それでは実際使えるか試してみましょう。マウス無しキーボードだけの操作で、連続カラースケールを行ってみます。

ScreenToGif のキーストローク表示だと

Alt +1 Alt + Ctrl + Shift + 1

と一気に押したように見えてしまいますが、これは

Alt +1 を押したことで → Alt + Ctrl + Shift + 1 を押したと認識

とイメージしてください。

PowerToysで簡略化したショートカットでマクロ実行ができました。

これはまさに、

「マイクロソフトとGoogleの禁断のマリアージュや～」（再度、彦摩呂）

ですねｗ