支店の合計ポイントを拠点数で割った数字を順位付けしたい

Question

いつもご教授いただきましてありがとうございます。
今回もご教授頂けますと幸いです。

元データ  ポイント
東京支店 100
仙台支店 200
九州支店 50

拠点数
東京支店数 東京、神奈川、栃木、茨城、千葉の5拠点
仙台支店 泉、釜石の2拠点
九州支店 熊本、福岡、北九州、別府、長崎の5拠点

各支店のポイントを拠点数で割った数字を出し
その数字に対してランキングをつけたいです。

順位  支店          ポイント
1      仙台支店      100
2      東京支店      20
3      九州支店       10

どういう数式にしたらなら良いでしょうか？

何卒ご教授願います。

tom04 · Accepted Answer

こんばんは！

https://oshiete.goo.ne.jp/qa/11082599.html
↑
のサイトの続きですね。
前回投稿した者しかこの質問内容は判らないですね。

当方の回答がベストとは限りません、
他の方も別の方法で素晴らしい方法を回答してくださるかもあるかと思います。

誰にでも理解できるような質問にした方が良いと思います。
例えば、関連質問のURLを貼り付けるなどして・・・

さて、本題ですが、
元データポイント、支店数はすでに集計済みなのかどうか判らないので
勝手に↓のようにしてみました。
まず、「拠点分け」用の対応表を作成しておきます。（F～H列）
A列に支店名を入力　→　B列に拠点名を表示するようにします。
B2セルに
=IFERROR(INDEX(F$1:H$1,SUMPRODUCT((F$2:H$6=A2)*COLUMN(A1:C1))),"")

という数式を入れフィルハンドルで下へコピー！

そして今回も作業用の列を設けています。

Sheet1の作業列D2セルに
=IFERROR(IF(COUNTIF(B$2:B2,B2)=1,AVERAGEIF(B:B,B2,C:C),""),"")

という数式を入れ、フィルハンドルで下へコピー！

Sheet2のA2セルに
=IF(B2="","",ROW(A1))

B2セルに
=IFERROR(INDEX(Sheet1!B:B,MATCH(LARGE(Sheet1!D:D,ROW(A1)),Sheet1!D:D,0)),"")

C2セルに
=IFERROR(VLOOKUP(B2,Sheet1!B:D,3,0),"")

という数式を入れ、A2～C2セルを範囲指定　→　C2セルのフィルハンドルで下へコピー！

これで画像のような感じになります。

※　同順位は考慮していません。
万一同順位がある場合は作業列をもう1列追加し、
同順位なしのランク付けをすれば対応できると思います。m(_ _)m

nouble1 · Answer

済みません、
不具合と　までは、
行かないものの、

代表式2の　2式を、
やや　修正させてください。

現行、

支店名、
=IFERROR(CHOOSE(ROUND(MOD(LARGE(INDEX(ROUND(MOD(SUMPRODUCT(MMULT(SIGN(COLUMN(A1:F1)),($A$1:$A$6={"東京","神奈川","栃木","茨城","千葉","泉","釜石","熊本","福岡","北九州","別府","長崎"})*$B$1:$B$6)*10^((SIGN(ROUNDDOWN(COLUMN($A$1:$L$1)-6.5,0))+1)*5))*10^(5*(1-ROW($A$1:$A$3))),10^5),0)+ROW($A$1:$A$3)/10,,),ROW(A1)),1)*10,0),"九州","仙台","東京"),"")

集計、
=IFERROR(ROUNDDOWN(LARGE(INDEX(ROUND(MOD(SUMPRODUCT(MMULT(SIGN(COLUMN(A1:F1)),($A$1:$A$6={"東京","神奈川","栃木","茨城","千葉","泉","釜石","熊本","福岡","北九州","別府","長崎"})*$B$1:$B$6)*10^((SIGN(ROUNDDOWN(COLUMN($A$1:$L$1)-6.5,0))+1)*5))*10^(5*(1-ROW($A$1:$A$3))),10^5),0)+ROW($A$1:$A$3)/10,,),ROW(A1)),0),"")

改訂後、

支店名、
=IFERROR(CHOOSE(ROUND(MOD(LARGE(INDEX(ROUND(MOD(SUMPRODUCT(MMULT(SIGN(COLUMN($A$1:$F$1)),($A$1:$A$6={"東京","神奈川","栃木","茨城","千葉","泉","釜石","熊本","福岡","北九州","別府","長崎"})*$B$1:$B$6)*10^((SIGN(ROUNDDOWN(COLUMN($A$1:$L$1)-6.5,0))+1)*5))*10^(5*(1-ROW($A$1:$A$3))),10^5),0)+ROW($A$1:$A$3)/10,,),ROW(A1)),1)*10,0),"九州","仙台","東京"),"")

集計、
=IFERROR(ROUNDDOWN(LARGE(INDEX(ROUND(MOD(SUMPRODUCT(MMULT(SIGN(COLUMN($A$1:$F$1)),($A$1:$A$6={"東京","神奈川","栃木","茨城","千葉","泉","釜石","熊本","福岡","北九州","別府","長崎"})*$B$1:$B$6)*10^((SIGN(ROUNDDOWN(COLUMN($A$1:$L$1)-6.5,0))+1)*5))*10^(5*(1-ROW($A$1:$A$3))),10^5),0)+ROW($A$1:$A$3)/10,,),ROW(A1)),0),"")
　　　　　　　　　　　以上、

違いは、
COLUMN構文　参照域を、
固定しているか　固定していないかです。

お詫びの上　訂正します、
済みません。

後、
映像添付を　忘れてましたので、
添付しておきます。

nouble1 · Answer

今回　ご提示に、
なられた、
質問の　範囲のみ、
と　みれば、

たとえば、
東京支店なら　100/COUNTA({"東京","神奈川","栃木","茨城","千葉"})
と　するだけです。

又、
一般式化するなら、

=B3/COUNTA(CHOOSE(SUMPRODUCT((LEN(A3)<>LEN(SUBSTITUTE(A3,{"東京";"仙台";"九州"},"")))*ROW($A$1:$A$3))+1,"",{"東京","神奈川","栃木","茨木","千葉"},{"泉","釜石"},{"熊本","北九州","別府","長崎"}))
と　すれば良い。

しかし、
tom04様の　回答を、
見ると、
此の　限りでは、
ないようなので、

tom04様、
ご提示の　添付表を、
誠に　勝手ながら、
参考に　させて、
頂きます。

で、
ですね、
出来ました、

2案あります。

代表式1
支店名
=IFERROR(CHOOSE(ROUND(MOD(LARGE(INDEX(ROUND(MOD(SUMPRODUCT(10^(SIGN(ROUNDDOWN(MATCH($A$1:$A$6,{"東京";"神奈川";"栃木";"茨城";"千葉";"泉";"釜石";"熊本";"福岡";"北九州";"別府";"長崎"},0)-6.5,0))*4)*$B$1:$B$6)*10^((ROW($A$1:$A$3)-2)*4),10^4),0)+ROW($A$1:$A$3)/10,,),ROW(A1)),1)*10,0),"東京","仙台","九州"),"")

集計値
=IFERROR(ROUNDDOWN(LARGE(INDEX(ROUND(MOD(SUMPRODUCT(10^(SIGN(ROUNDDOWN(MATCH($A$1:$A$6,{"東京";"神奈川";"栃木";"茨城";"千葉";"泉";"釜石";"熊本";"福岡";"北九州";"別府";"長崎"},0)-6.5,0))*4)*$B$1:$B$6)*10^((ROW($A$1:$A$3)-2)*4),10^4),0)+ROW($A$1:$A$3)/10,,),ROW(A1)),0),"")

代表式2
支店名
=IFERROR(CHOOSE(ROUND(MOD(LARGE(INDEX(ROUND(MOD(SUMPRODUCT(MMULT(SIGN(COLUMN(A1:F1)),($A$1:$A$6={"東京","神奈川","栃木","茨城","千葉","泉","釜石","熊本","福岡","北九州","別府","長崎"})*$B$1:$B$6)*10^((SIGN(ROUNDDOWN(COLUMN($A$1:$L$1)-6.5,0))+1)*5))*10^(5*(1-ROW($A$1:$A$3))),10^5),0)+ROW($A$1:$A$3)/10,,),ROW(A1)),1)*10,0),"九州","仙台","東京"),"")

集計値
=IFERROR(ROUNDDOWN(LARGE(INDEX(ROUND(MOD(SUMPRODUCT(MMULT(SIGN(COLUMN(A1:F1)),($A$1:$A$6={"東京","神奈川","栃木","茨城","千葉","泉","釜石","熊本","福岡","北九州","別府","長崎"})*$B$1:$B$6)*10^((SIGN(ROUNDDOWN(COLUMN($A$1:$L$1)-6.5,0))+1)*5))*10^(5*(1-ROW($A$1:$A$3))),10^5),0)+ROW($A$1:$A$3)/10,,),ROW(A1)),0),"")

ファイル、
https://1drv.ms/x/s!AjviygfJDgV_3FE4d4DpzWPPYXzH

尚、
ファイルは　1度、
ローカルに　別名保存して、
其の保存ファイルを　扱うように、
してくださいね、

別名保存でないと、
意味が　無いですよ。

さすれば、
閲覧も、編集も、
叶うものと　思いますよ。

さて、
今回は　幸いか、
3地方のみなので、

地域毎に　桁を、
別けて　集計し、

結果を　3値に、
別けた後、

地域の　指標値として、
0.1〜0.3を、
各々に　付加して、
大きい順に　取り出し、

小数点以下は　地域取り出しに、
整数部は　集計表示に、
使っています。

では、
構造ですが、

代表式1では、

支店は　12，
5店、2店、5店、
ですので、
各々に　1〜12を、
割り当て、
-6.5を　引き、
小数点以下を　切り捨て、
-5〜5に　変えた上で、

負の数を　-1に、
0を　0に、
正の数を　1に、
変えて、
4を　かけ、
10の　指数として、
10^-4、10^0、10^4、
に、
各々　しています。

其れが　此の式です、
10^(SIGN(ROUNDDOWN(MATCH($A$1:$A$6,{"東京";"神奈川";"栃木";"茨城";"千葉";"泉";"釜石";"熊本";"福岡";"北九州";"別府";"長崎"},0)-6.5,0))*4)

此に　支店毎の、
ポイントを　掛け合わせて、
集計すれば、
SUMPRODUCT(MMULT(SIGN(COLUMN(A1:F1)),($A$1:$A$6={"東京","神奈川","栃木","茨城","千葉","泉","釜石","熊本","福岡","北九州","別府","長崎"})*$B$1:$B$6)

今回は、
3000050.005
と　言う結果が、
得られます。

此を、
ROW($A$1:$A$3)-2)*4
に　より、
10^{-5,0,5}倍すると、
3値が　生成できます。

此処までを　仮に、
集計部と　称して、
おきますが、

此の　各々に、
ROUND(MOD(集計部,10^4),0)
と　処理すると、

300、50、50，
と　なります。

各々に　0.1〜0.3を、
付加して、
300.1、50.2、50.3、
を　得ています。

此を、
LArGE(INDEX(此処までの式,,),ROW(a1))
と　して、
大きい順に　取り出しています。

すると、
300.1
50.2
50.3
との　結果が、
得られます。

例えば、
300.1
ですが、

ROUND(MOD(300.1,1),0)
で　1が、
算出されますので、

CHOOCE(1,"東京","仙台","九州")
と　して、
支店名に、

ROUNDDOWN(300.1,0)
と　すれば、
集計値　300が、
算出できますので、

集計値として、

各々の　列に、
表しています。

次に、
代表式2ですが、

まぁ、
色々と　違う中で、
ポイントは、
集計元データの　割り出し方です。

代表式1では、
MATCH構文を　用いて、

各支店に、
仮に　振り分けた、
1〜12の　どれに、
当たるかを、
判別していますが、

此を　行列式、
詰まり、
MMULT構文で　実現しています。

後は、
小数点域を　使わない事や、
些末には　違いますが、
大差は　無いでしょう。

尚、
著作権放棄は　しません。

ルールを　守って、
頂ければ、
金品等　一切、
求めません。

求めるのは、
何時　何処で、
どんなアカウントの　アイディアだったか、
此等を、
使用、応用、転記する、
全てに　記載する事です。

ご理解くださいね。

お守り　頂けない場合は、
誰であれ、
司法に　問う事に、
なるでしょう。

司法の　前では、
ネットの　秘匿性は、
消え失せる、

と、
お心積もりくださいね。

支店の合計ポイントを拠点数で割った数字を順位付けしたい

こんばんは！

済みません、

今回 ご提示に、

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング

今回　ご提示に、