Googleスプレッドシート 知恵と工夫で ランキンググラフをつくる
今回は Googleスプレッドシートの グラフ と 表示形式の ネタです。（関数もちょろっと）
当初はお題形式の関数ネタを書こうかと思っていましたが、先週までが正規表現にGASと重めのネタが続いたのと、最近知恵袋で回答した時に以前グラフ作成で苦労したことを思い出して、 少し軽めのGoogleスプレッドシートのグラフに関するネタでいくことにしました。
でも、結構実用的だったりしますよ～。
先週までは 5回シリーズで「検索と置換」の正規表現を活用した応用ネタやGASの TextFinderクラスについて書いてきました。
Googleスプレッドシートのグラフ機能
今回のタイトル画像に使ったグラフ。このグラフが今回のゴールなんですが、どこに知恵と工夫があるかわかりますか？
単に月ごとのランキング（順位）の推移を表した折れ線グラフあるよね～。
とか
黙示録の営業成績ってｗ 元ネタはアレでしょ？
とか ではなく、注目すべきは
ちゃんとに 1位が 上で 最下位 が下に来ている点です！
（ついでに言うと 1位にだけ 👑マークが入ってるのもポイント）
グラフの縦軸の基本は、上が大きい数字で下が小さい数字。でも、このランキンググラフは 上に小さい数字 1がきていて、下に大きい数字 3,5・・ がきている。
どうやったら出来るの？？って思った方も、本題（解決方法）まで少し引っ張りますんでｗ 慌てずじっくりお読みください。
グラフ作成の変化球というか応用に関するnoteなので、Googleスプレッドシートのグラフ作成の基本的な操作がまったくわかってません。1回もグラフ作ったことありませんって方は、先に他のサイトを参照ください。
グラフ機能は Googleスプレッドシートの弱いところ
ExcelとGoogleスプレッドシートの比較記事は結構ありますが、正直 誤った情報を掲載しているものも多く見かけます。
「基本的な関数はどちらも同じように使えるが、ExcelはGoogleスプレッドシートには無い高度な関数が使えるメリットがある」
こんなことを書いてる記事はダメですね。
そもそも Excelは Microsoft365版と 買い切り版は、まったくの別モノと言ってよいくらい違います。
特にシート関数に関しては 365版の Excelだけが突出していると言えます。
「この関数便利そう！試してみよ！」
— 筒井.xls@エクセル関数擬人化本著者 (@Tsutsui0524) December 31, 2022
っていう時にバージョン足りてない時のもどかしさ…
Excel関数バージョン対応表です pic.twitter.com/FWzrwSdxyv
次々と最新関数が投入されている 365版はともかく、買い切りのExcelとの比較、特に2019以前のExcelとの比較であれば 関数においては Googleスプレッドシートの方が上です！
ちなみに 関数だけだったら 無料のWeb版Excelも割といけてます。
じゃあ逆にExcelと比べて Googleスプレッドシートの弱い点は？
と聞かれたら・・・、
図形描写、パワク、保護、条件付き書式、印刷設定関連、テーブル機能、大量データを扱う時のパフォーマンス・・・
いっぱいあってキリがありませんｗ
表計算の代表といえる Excelに総合力で勝てるとは思ってないです。
グラフ機能 も Googleスプレッドシートの弱い部分の一つなんで
「Excel と同じように こんな感じのグラフをGoogleスプレッドシートで作りたい。」
なんてことを言われると結構苦労します。（どうやっても出来ないことも多いです）
Googleスプレッドシートのグラフ機能は、グラフの種類もそうですが、特にグラフの設定オプション周りが貧弱です。
もちろん基本的なことは出来ますが、Excelと同じ感覚で ちょっと応用的なグラフを求めると あっという間に詰みます。
Excelのグラフは 軸の反転が出来る
ちなみに 普段から Excelを使ってる人には当たり前の感覚かもしれませんが、Excelであれば この手の 1位が一番上にくるような ランキンググラフは
軸のオプション ＞ 軸を反転する
で簡単に出来ます。
これに加えて、横軸との交点や 最小値を調整すればOKですね。
当然ベースの表の段階で、〇位 となっているセルは 中身は数値で 表示形式で "位"を付けるといった、基本的なポイントはおさえる必要があります。
ただ、それ以外はグラフ作成という点では「軸の反転」さえ知っていれば、ポチポチやるだけで簡単に出来るものです。
簡単ですね！
この「軸の反転」という機能がGoogleスプレッドシートにはありません。
じゃあ、
Googleスプレッドシートでは 1位が上にくるランキンググラフは作れないのか？ なんとか出来ないのか？
というのが、今回のテーマです。
まずはランキング表を作ろう
少し関数ネタにも触れておきたいなってことで、グラフ作成の前に まずはグラフのベースとなるランキング表をどのように作ったら良いかを見ていきましょう。
ランキングはルール決めが大事
今回は実は元データとして、上のような 月別営業ポイント表というものが既にありました。
このポイント表のままグラフにしても良いのですが、出来れば 個々の具体的なポイントは見せずに 順位だけを使ってグラフにしたいという要望だったとします。
この場合、直でポイント表から ランキンググラフは生成できないので、一旦画像の下のようなランキング表を用意する必要があります。
月ごとに順位をつけたいので、縦（列ごと）に見て 一番ポイントが高い人が1、その次が2 となります。
4月の場合は、4位となる 同じ75ポイントの人が 2名います。
このように同ポイントが複数名の時はどちらも同じ順位（4位）として、その次の アンを 人数分下げた順位（6位）とするルールでいきましょう。
同じポイントの時のルール設定はしましたが、このランク付け作業をさすがに目視で手動ではやらないですよね？
ランキングの為の RANK関数 とRANK.EQ関数、RANK.AVG関数
ランキングの為の関数として、RANK関数という関数があるのは知ってますか？
「さすがに RANK関数は知ってるよ」という人は多いかもしれません。今回もこの RANK関数を使います。
では、=RANK と入力した時に関数候補に出てくる
RANK.EQ
RANK.AVG
という2つの関数とRANK関数の違いは知ってますでしょうか？
RANK.AVGは 同スコアが複数いたときの挙動が違うのはわかりますね。
AVGと付いてるのでなんとなくわかるかもしれませんが、公式に説明のある通り、
「データセット内に同じ値の複数のエントリがある場合は、エントリの平均順位を返します」
という関数です。
今回決めた同順位の定義だと、これじゃないですね。
一方、RANK関数とRANK.EQ関数は同じ結果を返しています。
公式だと説明がありませんが、実はこの2つはまったく同じ処理をする関数ですｗ
同じ処理をする関数が、ちょっとだけ名前を変えて 2つある理由については、Excel側の歴史の話になるので他のサイトをお読みください。
とりあえず今回の同順位の定義にマッチするのはこちらなので、短い方のRANK関数を使っておきましょう。
RANK関数の引数は、このようになっています。
RANK(値, データ, [降順])
値 - 順位を特定する値です。
データ - 検証するデータセットを含む配列または範囲です。
降順 - [省略可 - デフォルトは 0] - データ内の値の並べ方を降順または昇順で指定します。。
1つずつ式を入れて（コピペで）複数の値のランクを求める場合は、第2引数のデータを絶対参照にしておくのがポイントです！
=RANK(B3,$B$3:$B$8)
RANK関数を使うところまでは理解できましたね。
Q. 一つの式で 各列毎のランクを出力できるか？
それでは、ここでシート関数のお題いってみましょう。
A2:J8の 月別のポイント表から 一つの式で月別のランキング表を作成するにはどんな式を B30に入れればよいでしょうか？
前提条件
・上の画像の下の表のイメージで、外側の項目はコピペで埋めている状態
・式は B30にのみ入れる
・12月のように ポイントが入っていない箇所は ランキング表も空欄とする
↓ A1:J8のデータ（そのままコピペ可能）
黙示録の月別営業ポイント表
4月 5月 6月 7月 8月 9月 10月 11月 12月
パーシバル 80 75 90 82 95 93 78 85
ドニ― 75 74 77 70 80 77 71 78
ナシエンス 75 80 88 80 90 91 93 95
アン 73 70 75 80 75 80 75 83
シン 85 73 81 78 70 75 82 80
イロンシッド 78 90 89 95 90 93 85 98
どうでしょう？出来そうでしょうか？
まずはチャレンジしてみましょう。
↓ ここから回答
A. 一つの式で 各列毎のランクを出力する
=ARRAYFORMULA(BYCOL(B3:J8,LAMBDA(c,IFERROR(RANK(c,c)))))
いきなり回答です。これは GoogleスプレッドシートのLAMBDAヘルパー関数が配列を返せることを知ってる人には簡単ですが、知らないと難しいかもしれません。
ExcelのLAMBDAと違って、GoogleスプレッドシートのLAMBDAヘルパー関数は制限はありますが、配列の入れ子が扱えるのが強みです。
今回の式を少し解説しておきましょう。
1列のデータの ランクを1つの式で 出力したい場合は、Arrayformulaを使って
=ARRAYFORMULA(RANK(B3:B8,B3:B8))
このように書くことが出来ます。
少しわかりにくいかもしれませんが、同じ B3:B8でも
第1引数の方の B3:B8 ・・・ 値 一つひとつ処理される
第2引数の方の B3:B8 ・・・ データ （範囲） こちらは固定
※ 式をコピペしないので 絶対参照をつける必要はない
このように役割が違います。
Arrayformulaで配列処理されるのは 第1引数の 値 の方の B3:B38ですね。
で、これを 列毎に動かしたいのですが、Arrayformulaでは 列毎にという処理は無理なので LAMBDAヘルパー関数の BYCOLを使います。
BYCOLはその名の通り、列毎に処理を繰り返す関数です。
=ARRAYFORMULA(BYCOL(B3:J8,LAMBDA(c,RANK(c,c))))
ARRAYFORMULAは1つあれば カッコ内全体に適用されるので、mirのおススメとしては 一番外側で括る書き方です。
BYCOL に与えた範囲 B3:J8から 1列ずつ
B3:B8 → c として RANK(c,c) で Arrayfromulaが適用された配列計算
C3:C8 → c として RANK(c,c) で Arrayfromulaが適用された配列計算
・
・
・
J3:J8 → c として RANK(c,c) で Arrayfromulaが適用された配列計算
このように処理されているわけです。
最後に 12月の空欄の対処として、RANK関数は #N/A を返してしまうので、IFERRORで空白を返すようにしています。（IFNAでもよい）
GoogleスプレッドシートのIFERRORは、第2引数省略で 空白を返すことが出来ます。
まずはランキング表ができました。
既にランキングの表が作成済みの場合は、このくだりは飛ばしちゃってOK。その場合は、表内の順位が数値になっていることが重要です。
先に反転させてからランキンググラフを作ろう
では、このランキング表からグラフを作る際に1が上にくる、つまり反転させる為にはどうすればよいでしょうか？
グラフの機能では対応できないので、表の時点で加工をする必要があります。
数字部分が小さいほどより大きい世界
グラフ化した時に 1が上にくる 反転術式、それは マイナス化です。
当然ですが、正の数では 1より2、2より3が大きい。つまりグラフでは上になります。
一方 0を境にしたマイナスの世界では、反対に -3より-2、-2より-1の方が大きく グラフでは上になります。
つまり 実データは マイナス化した上で、表示形式でマイナスを隠してグラフ化すれば ランキンググラフができそうですね！
表の実データをマイナスにするのは、先ほどの式にマイナスをつければOK
です。
=ARRAYFORMULA(BYCOL(B3:J8,LAMBDA(c,IFERROR(-RANK(c,c)))))
既にランキングの表になっている場合 検索と置換でマイナス化
最初からランキング表がある場合は、数式で他のセルにマイナスをつけて順位を出力すればよいです。
=ARRAYFORMULA(IF(B3:J8="",,-B3:J8))
注意点としては、元データに空白があった場合は -（マイナス）をつけることで数値化して 0になってしまうので、空白の場合は空白を返すようにIFで処理を分岐させること。
簡単ですね。
もし、どうしても他のセルや別シートに出力することが難しい場合は、既に出来上がってるランキング表の順位（数値）を直接マイナス化してしまいましょう。
ここで使えるのが前回までのシリーズのテーマ「検索と置換」です。
こちらも単純に-を先頭につけると空白セルにもマイナスが入ってしまうので、空白を考慮して
検索 ^(\d+)
置換後 -$1
※正規表現を使用した検索にチェック
このように設定して「すべて置換」。
先頭に文字を追加する方法や$1 キャプチャグループ について知りたい方は、「検索と置換」シリーズを参照ください。
順位がマイナスの表が完成しました。
わざわざマイナスをつけた順位表を、次は見かけ上マイナスを消す処理をします。
Googleスプレッドシート カスタム数値形式
特殊な表示形式を設定したい場合は、適用したいセル範囲を選択して
メニュー ＞ 表示形式 ＞ 数字 ＞ カスタム数値形式
と進みます。
ここで 表示形式を色々設定できるんですが、数値の場合は
正の値の時、負の値の時 で違う表示形式が設定できます。
たとえば、ここで設定したコレ。
#,##0_);[Red](#,##0)
なにこれ？ってなる人も多いんじゃないでしょうか。
これは、; （セミコロン）の前後で、正の数の時と負の数の時の表示形式、それぞれを記載しています。
つまり
正の数の表示形式 ; 負の数の表示形式
こういう意味合いです。そして
正の数の表示形式 #,##0_)
こちらは 3桁区切りで数値を表示し お尻（右端）に、 ) と同じサイズのスペースを入れる という指定になります。
もう一方の
負の数の表示形式 [Red](#,##0)
こちらは マイナスの時は 赤字で 3桁区切りの数値を 半角カッコで括って表示という意味です。
つまり、マイナスの際のカッコ閉じの分を 正の数でもスペースで同じ間隔にすることで、右寄せの際の 数字のお尻がしっかり揃うようになっているわけですね。
こんなのわかんねーよ！！って思うかもしれませんが、一応 この辺りの説明は ヘルプに記載があります。
ちなみに ; による表示形式の分岐は
正の値 ; 負の値 ; ゼロ ; テキスト
と、最大4つまで指定できます。
今回の ランキングは 全てマイナスが 入る想定なので ゼロやテキストの指定は不要で、正の値は特に気にしなくていいですね。
0;0位
こんな感じにしておきましょう。
マイナスが消えて 〇位 という表示になりました。
（余談）カラースケールは 他の条件付き書式と共存する
余談ですが、表を見やすくする為に 条件付き書式を2つ仕込んでいます。
1つは カラースケールで、これは最大値と最小値をセットすれば自動で中間のデータをグラデーション表示してくれる設定です。
もう1つは 1位を目立たせる為に -1以上を条件として 紫字の太字表示としています。
これも実は、Googleスプレッドシートの 条件付き書式で苦しんだことがある人なら、えっ？ってなる部分じゃないでしょうか？
Googleスプレッドシートの 条件付き書式は、通常は上の条件が優先され、条件にマッチしたら 以降の条件は適用されません。
つまり 1番目の条件にマッチするかつ2番目の条件にマッチといったケースでも 2番目の条件は適用されないのです。
故に上のように 土日を塗りつぶし、かつ 本日を 青文字太字を両立させるには、
このように 両方を満たす条件付き書式を用意して、1番目の条件にする必要があります。
しかし、例外としてこのカラースケールは、2番目の（背景色に影響のない）条件付き書式を併用して適用させることが可能なのです。
ただ、ここでも順番が重要で、カラースケールの優先度が下だと 併用した適用はされません。注意！
まぁ条件付き書式も充実していて、罫線やフォント、表示形式まで制御できる Excelからしたら「ふーん」ってレベルの話なんですがｗ
ランキンググラフを作る
ここまでの下準備が出来ていれば、グラフ作成は簡単です。
まずはこのように範囲を選択して 挿入 ＞ グラフ とします。
ここで希望する 折れ線グラフが出ればOK。違うグラフになっちゃった場合はグラフの種類を切り替えましょう。
縦軸の順位は手動で修正が必要です。
一番上を 1位にしたいのですが、全て中身はマイナスの世界なので 最小値ではなく、
最大値に -1 を指定します。
なんか混乱しますねｗ 一番下（最小値）は -7あたりにしておきましょう。
このように、1位が一番上にくる 月ごとのランキング 折れ線グラフが完成しました～。
見た目上は、1位、2位、3位となっていますが、実データは 単純に 0以下の世界で -1、-2、-3 と普通に上が大きく下が小さい数値。生成ＡＩもびっくりな ディープフェイクなグラフですねｗ
オマケ：1位に👑をつけて 目立たせたい
これはオマケ程度に見てください。
なお 今はほぼ問題ないかと思いますが、絵文字 👑 は表示させる環境によっては思った通りに表示されなかったり、文字化けする可能性もあります。
今回のタイトル画像だと1位に👑 がついているのがわかりますか？
1位（数値が1）だったら 数字の前に 👑をつける、これはExcelなら 実は条件付き書式で対応可能です。
残念ながら Googleスプレッドシートでは 条件付き書式では 表示形式の制御はできません。
もちろん関数を使えば簡単です。でも、その場合は データが数値じゃなくなっちゃうのでグラフ化できません。ではどうすればよいか？
カスタム数値形式は 条件による分岐ができる
実は 先ほど登場した カスタム数値形式 は、正・負の 切り替えだけではなく、条件に対して true / false で表示形式の切り替えができます。
これ、日本語のヘルプページには書いてなくて
さきほどのヘルプページの ココ をクリックすると
このようにGoogle本家（英語版）の表示形式のヘルプページに飛びます。
ここに
このように [条件] で条件指定できることが記述されています。
このネタはGoogleスプレッドシート完全攻略さんでも、最近紹介されていました。
この条件式には 計算や関数利用、セル参照の利用などは出来ません。
また テキストの表示形式の条件分岐（ りんご と一致したらアップルを表示など）なども出来ません。シンプルな条件で数値のみに使える分岐となります。
利用できるシーンは、結構限定的ですね。
Excelでは昔からあった 表示形式の条件分岐
でも、これExcelだと結構昔からあった機能なんですよね。実は Googleスプレッドシートでも使えたんですね～。
↑ こちらのサンプルを Googleスプレッドシートで適用させてみましょう。
[blue][>=100]👑0;[red][<=50]💀0;[black]💍0
カスタム数値形式で上のように設定することで、
100以上は 青文字で頭に👑
50以下は 赤文字で頭に 💀
それ以外の数値（50より大きく～100未満） は アタマに💍
このように 表示させることが出来ました。
まぁ、そんなに使うワザではないんですが、知ってると便利な時があるかもしれません。
1位に👑をつけたランキンググラフを作成する
今回のケースは 中身は数値のままが良いし、グラフ上の表示は 設定した表示形式が反映されるんで、まさにこれが活用できるってわけです。
ランキング表に適用してみましょう。
[=-1]👑1位;0位;
このように設定します。
ポイントは2つ
－1に一致した時の 表示は 👑0位 ではなく、👑1位 とする
一致しない時の表示 0位の後ろに ; を入れておく
これを以下のようにしてしまうと
[=-1]👑0位;0位
このようにマイナスが残ってしまいます。難しいですね。
というわけで
[=-1]👑1位;0位;
こちらを設定することで、無事、1位を👑で目立たせグラフの一番上に表示させた、黙示録の営業成績 順位グラフが完成しました～。
Googleスプレッドシートが苦手とするグラフ生成で、なんとかやりたいことが実現できました。
Googleスプレッドシートは 情報が少ないのがネック
今回は Googleスプレッドシートの弱い点である グラフや 表示形式 をいかに知恵と工夫で対処するかって ネタを書きました。
Excelで出来るのにGoogleスプレッドシートでは出来ないことは多いですが、今回の カスタム数値形式の条件指定のように、実は Googleスプレッドシートでも Excelのこの機能使えたんだ ってケースもあります。
こういった情報がネット上に少ない、特にExcelと違って 日本語の情報が圧倒的に少ないのが、Googleスプレッドシートの弱いところなんですよね。
初心者向けの情報は結構増えてきたと思いますが、まだまだ中級者以上が欲しい情報は少ないと感じます。
この noteが 迷える Googleスプレッドシート民のお役に立てば幸いです。
次週は予定していた関数ネタやろうかなと。