関数だけでランキング表を作る意味があるのか？？
さあ、、、わかりません(笑)
過去に私、毎日販売数ランキングの速報を出さねばならない時期がありまして、、、各所から報告をもらってから集計するのですが、みんな報告期限ギリギリ、下手すりゃ期限超えて報告もある、、、、早く出さねばと入力して、ソートして整えて、、、、みたいなタイムアタックしてると、何これ？競技なの？! なんて馬鹿らしくなってくるわけです(笑)
当時、会社PCがMacで、まだその頃のExcelforMacのVBAは機能も限られている上、とても、とても、とても！不安定（笑）で使い物にならなかったんです。なので、関数だけで自動計算できんもんかと、、、考えて作ったんですね。
そんなことを「関数だけで可変リスト」の記事を書いてたら、思い出したので、こっちも書いていきます！
それでは作成手順で説明していきます。
まず、下のような集計表があります。
横方向に集計してランキングを振る、よく見る表です。集計関数はみなさんにお任せしますが、ここでは下のように作成しています。
黄色で表したC列エリア以降の列がデータ入力エリア。
赤で表したB列は合計欄で、SUM関数+OFFSET関数を使って、データ入力エリアの項目行（１行目）に項目があるだけ横方向に合計し、青で表したA列のランキング欄は、RANK関数を使って、B列全体を範囲とした、合計値の降順順位（最大値が１位）を出しています。縦のデータが増えた時はオートフィルが必要ですが、横は１行目に項目を入力すれば、簡単に集計に組み込める仕組みです。OFFSET関数に不慣れだと、使い方がわかりにくいかもしれませんね。そちらは【Excel】関数だけで可変するリストを作ってみる(前編）で詳しく説明しています。知りたい方は読んでみてください。
また、このA列のRANK関数は新しいバージョンのRANK.EQ関数があります。RANK関数と全く同じ設定と動きです。ここでは私が古い人間なのでRANK関数にしてますが、古いバージョンのExcelを使わないとわかっている場合は、なるべく新しい関数を使ったほうがよいです。古いものは将来使えなくなる可能性がありますし、セキュリティ強化にもつながりますしね。
それでは！！本題に入ります。
並び替え用に、もう一枚シートを使います。上の集計するシートは「集計」とし、順位を表示するシートを「表示」という名前にして準備します。もちろんデータの横に作業列をつくっても良いです。
私の経験上、データは削除、追加しやすく、報告にも使えるように設計しておいた方が、あとあと応用も効くことが多いので分けてます。
では、追加する「表示シート」をどう使うかといいますと、
このように、並び替え用に「出現回数【A列】」、「データ位置【C列】」の列を2列使って、「表示順位【B列】」と「表示地域【D列】」で結果表示させる仕組みです。集計結果のランキングが変われば表示も連動して変わります。この並びは説明用ですが、報告や共有に使う場合は表示順位と表示地域は並べたほうが使いやすいと思います。
そして、動作例ですが、次のように、
集計結果が同順位になった時、ちゃんとRANK関数のとおり、同じ数字で表示し並び替えます。
それでは仕組みを順番に説明していきます。
まず、【A列】出現回数です。
関数式はこちら
=COUNTIF(集計!A:A,ROW()-1)このようになっています。
このCOUNTIF関数は
=COUNTIF（カウントする範囲，カウントするもの）
と使います。
この例ですと、「集計」シート【A列】のランキング欄で、RANK関数を使って出した順位の出現回数を数えています。
対象データが下段に増えてもオートフィルだけで対応できるように、集計!A:Aと集計シートA列の全体を指定し、カウントする順位は直接入力せずに、ROW関数で行番号を使っています
カウントする順位は、COUNT関数で累計データ数を数えて＋１の方法で求めてもよいですが、行番号なら１ずつ勝手に増えていきますので同じことですね。
ちょっと考えないといけないのは、項目が入っていることです。ここでは、１行項目を入れているので、項目行数分、マイナス（−１）してROW()-1となっています。
「いやいや、そもそも出現回数を数えるのはなんで？！」
ですよね？
これは、重複した順位を把握して、処理を変えるきっかけにするために使います。このあとで効いてきます。
次に、【B列】表示順位についてです。
まず、順位は１位スタートですから、１行目は「１」で確定です。最初のデータなので重複も考える必要ありません。もし、１が出現しない状況があるとするなら、データ自体が入力されていない状態、つまり使ってない状態です。業務で使うようなExcelファイルに使ってないときの処理まで考えなくてもよいと思いますが、、、、いやいや、しっかり使ってないときの処理もしたいとか、縁起がいいから順位は7番スタートだよ！とか、３番は永久欠番だからとか、、、諸事情がある方、、、IFERROR関数などを使って、各自で工夫をお願いいたします（笑）
そして、２行目以降です。
先程、【A列】で出現回数をカウントしました。でも実は、「何回重複したか？」はどうでもよくて、順位が出現しなかったこと、つまり「０」かどうか？が重要です。
順位が「出現しなかった」ということは、その順位は重複して現れなかったわけですから、上に向かって「最初に出現する順位と同じ」ということになります。これは、複数重複になっても同じ条件です。
この上に向かって最初に出現する順位を関数で表現すると、、、図のようになるわけです。０かどうか？ですぐ上を見れば、すぐ下が重複したとしても、すぐ上が上を参照しているので、「最初に出現する順位」になるわけです。
見にくいといけないので関数式は下のようになります。
=IF(A3=0,B2,ROW()-1)
この「現在の行数が順位なる」にこんがらがってしまいがち（私だけか（笑））なので、補足説明させて頂きますと、
集計シートのデータ位置と表示シートの関数の位置は、リンクして変化しますが、行数の位置については、全く意味合いは違います。図で説明しますと、
このようになっており、集計シートはデータ位置は変わらず順位が入れ替わり、表示は表示位置（順位）は変わらずデータが入れ替わるように、起点が逆になっています。これをしっかり理解しておかないと、あれれ？どっちだ？？となります（笑）
それでは、ここが今回の山場です。
【C列】データ位置について説明します。
この前の【B列】の表示順位のはじめ、「１行目は最初のデータなので重複を考えない」としてましたね。そのとおりで、重複するかどうかは２つ目のデータから考えればいいので、ここでは考えません。なので、図のようにMATCH関数で１位を集計シートの【A列】ランキングへ探しに行きます。
関数式は
=MATCH(B2,集計!A:A,0)です。MATCH関数の使い方は、
=MATCH（調べる値，調べる範囲，一致条件）
です。一致条件は以下[1],完全一致[0],以上[-1] の3種類ですが、私の業務範囲では、完全一致[0]以外使ったことないです。ここでも完全一致しか使いません。いつも０です（笑）
このMATCH関数についての詳細は【Excel】関数だけで可変するリストを作ってみる(後編）にあります。この記事では応用説明しますが、詳細までは書きませんので、わからない方は見てみてください。ただ、ものすごい長い記事なので迷子にならないように気をつけて。。。あと、そちらでも以下、以上は説明してません（笑）
さて、１行目は普通にMATCH関数を使って、１位を集計シートから探しました。MATCH関数は「指定範囲から一致するデータを探して場所を調べる」便利な関数ですが、データの端から探して、一番最初に見つかったところで探すのをやめてしまいます。
探す対象が指定範囲の中に１つなら、このままの関数式を下方向にオートフィルすれば解決します。今回ので言うと、ランキングに重複がなければこれでいいわけですが、残念ながらそういうわけにはいきません。
そこで重複対策します。
そのため【A 列】と【C列】のデータ位置をわざわざ作っているのですから！！
それではまず、MATCH関数の使い方を確認しながら考えましょう。
MATCHは「指定範囲の最初のデータを探して位置を教えてくれる」優秀なやつです。
でも、指示は１通りしか受け付けません。
指示の仕方を変えればいいんです。
MATCHは、いつも１番目を探してくるので、次は範囲をズラして１番目を探してもらいましょう！
ということです。
それをMATCHにわかるように関数に置き換えると、、、
となります（笑）
もう暗号ですね。関数式は
=MATCH(B3,OFFSET(集計!$A$1,SUMIF($B$2:B2,B3,$C$2:C2),0,COUNTA(集計!B:B)-SUMIF($B$2;B2,B3,$C$2:C2),1),0)となります。
では、バラバラに見ていきます。
一番外側のMATCH関数を要約して置き換えると、
MATCH（B3，可変する探す範囲，0）
となっています。長いのは可変する範囲指定だけということがわかります。
それでは、可変する範囲を要約して見てみます。
OFFSET（集計!A1，縦にズラす距離，0，集計のデータ数-縦にズラす距離，1)
縦にズラす距離？
これは先程のMATCHの動作確認の図の最後、「見つけた場所の次から探して」を表したものです。
関数から抜き出すと、
SUMIF($B$2:B2,B3,$C$2:C2)これです。読み解くと、
「現在のセルの直前（１つ上）までの範囲で、同じ表示順位のものは、表示位置を合計する」
ということになります。MATCH関数は選択範囲の中で何番目にあるかを調べるので、検索させる開始位置がズレれば、その分調べる位置のカウントスタートもズレるわけです。なので重複を数えるときは、前までの調べた結果を「累計」しないとデータのはじめから数えた位置にならないということです。
また、範囲指定の高さ[行数]が集計データ数からズラした距離を引き算してます。これは、同じものを探し終われば、対象データもその分減るわけなので、それを表しています。このへんは文章だけではわからないですよね。前の記事で作った図を、参考に下に載せておきます。伝わればよいのですが、、、
最後に、【D列】表示地域です。
ここまで来ると、あとは【C列】の数字と考え方を使って、表示するデータをINDEX関数で拾ってくるだけです。
ほぼ【C列】と一緒の考え方ですので詳しくは説明しませんが、少し違うのは、SUMIF関数で参照する累計範囲が、【C列】は直前（１つ上）まででしたが、ここでは選択セル位置も含めての累計になってます。【C列】は表示順位が同じものを調べるために、直前の調査結果までが必要でしたが、【D列】は表示名を参照するための位置なので、該当セルまでの表示結果まで含めないとたどり着かないという違いです。
これで完成です！短く書こうと思っても長くなってしまいます。これぐらいならサッといけるかと思いました。。。
最後のほうが集中力切れてますので、また書き直しながら整えていきます。
Excel関数の編集は長くなってくると見にくくなり、インデントを使うにも操作が面倒で触りたくありません（笑）。なので、なにか作ろうと思われたら、このサンプルのように一度、分けておくと作りやすいです。そのあとに式を合体すればいいわけですし、合体させているときに不要な一手に気づくこともあります。今回のサンプルで言えば、表示シート【A列】出現回数は【B列】表示順位と合体させるのは簡単ですね。表示順位は同じ行のB列からA列の値が0かそうでないかを判断しています。ということは参照しているセルの関数をそのまま式に入れてしまえば列が不要になります。
どういうことか、実際にみてみると、A列が
=COUNTIF(集計!A:A,ROW()-1)これでしたね。そしてB列が
=IF(A3=0,B2,ROW()-1)これです。このB列のA3の内容というのが、上のA列の式の値のことなので、A3のところに上の式がそのまま入れられるということです。もしこの関数式を運用する場合はご参考にしてみてください。
話は変わりますが、この方法は、Macの表計算アプリ「Numbers」でも使えます。
実はこの記事、資料はすべて「Numbers」と「Keynote」で作ったものです。「Numbers」は、表１つ１つを切り離して作成できて、レイアウトもパワポみたいにグリグリ移動できます。Excelと違ってレイアウトに制約がないので、直感的に使えてプレゼン向きです。ただ、、、できることは少ない上、癖が強いので、Excelの代わり、、、ではなく、別物です。
次こそはさらっと読める記事にします。
追記 2022年5月6日
サンプルEXCELファイルを私のページに公開
