[Google Document] Google Spreadsheet でセルの値を上位、下位で色分けする。( Excel のトップ10による色分けの代用)

Excelでは、選択したセル内でトップ(ボトム)10個を指定した色で色分けしハイライトする機能がありますが、残念ながらGoogle Spreadsheetにはその機能がないようです。そこで、10個をハイライトするわけではありませんが、四分位数を活用して、類似のハイライトを作成する方法について紹介します。

使用するデータ

前回紹介した、環境省の運営する「はなこさん」で公開している花粉の飛散量データを使います。今回は、測定局毎の日別の花粉飛散量の平均データ(4月1日〜4月8日)を用いることにします。

測定局毎の日別の花粉飛散量の平均データ(4月1日〜4月8日)

この期間で花粉が特に多かった(少なかった)測定局のセル(J列:Grand Total)を判断するため、このデータを以下のように、値の下位、上位をそれぞれ、緑、赤で網掛けしてハイライトしたいと思います。

小さい値の場合に緑色網掛けにする設定を、1つのセルに対し実施

今回はJ列をハイライトしたいので、まずJ列の先頭セルである「J3」セルをクリック(選択)した状態で、メニューから[Format] – [Conditional Formatting]を選択し、条件付き書式の設定を実施します。

実際に入力する式は以下になります。

= J3 < QUARTILE($J$3:$J$20, 1)

大きな値の場合に赤色網掛けにする設定を、1つのセルに対し実施

続けて、値が大きい場合の設定を設定します。画面右側に出ている、Conditional format rulesの下部に「+ Add another rule」がありますので、これをクリックします。すると、右からスワイプするような動きを見せますが、何も変わっていないように見えます。実は、このときすでにルールが複製されているため、現在表示されている内容を直接編集することで、2つ目のルールを設定することができます。

分かりづらいですが、スワイプされれば、ルールが複製(コピー)されています。

スワイプ後の画面の内容を、以下のように編集します。

実際に入力する式は以下になります。今回、対象のJ3セルがこの条件に該当しないため、特に網掛けに変化はありません。

= J3 > QUARTILE($J$3:$J$20, 3)

設定した書式を対象セル全体に貼り付け

それでは、J3セルに設定した条件付き書式を、すべてのセル(J3〜J20)に貼り付けていきます。J3セルをコピーし、[Edit]-[Paste special]-[Paste format only]を選択し、貼り付けていきます。

貼付け後、J3とは別のセル(例えばJ7)をクリックし、[Format]-[Conditional Formatting]を選択することで、ルールが設定されていることを確認できます。

各セルに2つずつルールが設定されている状態

最終的に、以下のように色分けされます。全18のデータの内、値の小さいデータとして5個が、値の大きいデータとして5個がハイライトされ、Excelで上位(下位)5件を色分けしたような形になります。

なお、今回は色分けのしきい値として、四分位数(QUARTILE)を用いましたが、「特定の固定値」や、「平均(AVERAGE)」などを利用することも可能です。

まとめ

  • Google Spreadsheet には、Top10 色分けという、そのものズバリの機能は存在しない
  • 四分位数(QUARTILE)と、 Conditional Format を活用することで同様の表現が可能

関連