『差の出し方』（T1508） お世話になります。 A列：グループ名 C,H,M...列：数値 E,J,O...列：C,H,M...列の順位（グループ別の） G,L,Q...列：E,J,O...列の1位には2位との差を、2位には3位との差を．． 　　　　　　と以下順に表示する G,L,Q...列に関数式を入力したいのですが、同順位があったり「飛び」順位が あるため、うまくいきません。 考え方をご教示願えないでしょうか？ 尚、順位は最大で18位まで、全体の行数は10000行程度までです。 ＜ 使用 Excel：Excel2010、使用 OS：Windows7 ＞ ---- 頭を捻らずに理解ができるよう、 そして問題が正確に伝わるよう、 具体的なサンプルを提示してもらえますか？ （γ） 2015/08/29(土) 19:36 ---- うまく説明できずにすみません。 A B C D E F G H I J K L 店1 - 43.9 - 4 - - 20 - 8 - - 店1 - 54.5 - 3 - 10.6 135 - 1 - 85 店1 - 23 - 12 - - 50 - 3 - - 店1 - 72.3 - 2 - 17.8 135 - 1 - 85 ("-" は省略です） 例えばですが、1位が2個あれば2位はないので、1位のところは1位と3位の差を表示したいです。 作業列を設ければ何とかなりそうに思うのですが、列数も結構あり今から 手を加えるのも面倒そうなので．．． （T1508） 2015/08/29(土) 23:22 ---- 首を突っ込んだ手前、何もしないのもどうかと思いコメントしておきます。 A列 B C D 1 グループ 得点 ランク 得点差 2 g1 50 7 #N/A 3 g1 55 6 5 4 g1 60 4 5 5 g1 60 4 5 6 g1 80 2 20 7 g1 80 2 20 8 g1 100 1 20 9 g2 50 7 #N/A 10 g2 58 6 8 11 g2 70 4 12 12 g2 70 4 12 13 g2 80 3 10 14 g2 90 2 10 15 g2 100 1 10 -------------- ランクをどういう式で作成しているのか教えて欲しいですが、 （少なくとも500グループはある計算になるが、手作業なんだろうか。） 以下は、マクロと計算式の折衷案です。 ワークシート関数の達人からの回答を楽しみにしています。 (1)もとの順番をどこかの列に持って置いて（復元のため）、 順序を一時的に変更することを前提にします。 (2)グループ別、得点の昇順（ランクの降順）にソートします。 (3)マクロでグループの最初の行、最後の行を繰り返しで求め、 以下の計算式をD列に書き込みます。 D2: =B2-INDEX($B$2:$B$8,MATCH(C2+1,$C$2:$C$8,-1)) D3: =B3-INDEX($B$2:$B$8,MATCH(C3+1,$C$2:$C$8,-1)) 以下略 (4)エラー値のみジャンプで検出して、クリアー (5)式を値に変換します。（値のみ貼り付け） (6)元の順序に戻します。 実際のコードはどうぞ、そちらで工夫して下さい。 面倒?の丸投げに協力している暇も余りないので。 （γ） 2015/08/30(日) 10:09 ---- γさんありがとうございます。 ランクは「COUNTIFS」で計算させてます。 ただ、提供されるデータに不都合があり、それらは対象外の意味で ＩＦ文で「99」にしています。 ご提示の方法は理解できたつもりです。 書かれている手順をマクロ化できればいいわけですが しかしこれはさすがにハードル高く、今の私にはムリ（涙） （いずれは挑戦したいと思ってますが．．） 差を求めたい項目が7項目ほどあるのですが、一つマクロできたら それを7個つなげるイメージですかね、マクロは．． INDEX,MATCHでいろいろやってたんですが、関数ではムリっぽいですかね。 もう少しあがいてみることにします．．． （T1508） 2015/08/30(日) 11:59 ---- ああ、簡単な数式でできましたね。 (1)(2)のソートするところまでは同じ。 (3)のところは、 D2: =IF(A2<>A1,"",IF(B2<>B1,B2-B1,D1)) として下にコピーペイストするだけでした。 あとは、値にして、ソートを元に戻します。 （γ） 2015/08/30(日) 13:37 ---- ありがとうございます。 ソートを前提にしたいろいろな方法を学べました。 他の事例で応用できそうです。 今回はA,B列を共通にC,H,M列...と7項目のデータがあり、 順位はそれぞれ昇順あり降順ありとなっています。 ソート前提だと作業時間が大変そうです。（関数式だけでは） ※説明不足ご容赦ください、ご回答をヒントに何とかなると 　勝手に思っていました どうも関数式では“一筋縄”ではいきそうにないような．．（涙） マクロを齧り始めましたが、どうなることやら．．． （T1508） 2015/08/30(日) 22:29 ---- 7項目だけとも言えますね。大したことないです。 同一作業を繰り返し行う可能性があるなら、 マクロ記録をとって、それをマクロにしておけばよいでしょう。 必要なら勉強されることだと思います。頑張ってください。 （γ） 2015/08/30(日) 22:49 ---- G1 =IF(OR(E1="",E1>=LARGE(E:E,COUNTIF(E:E,99)+1)),"",C1-INDEX(C:C,MATCH(COUNTIF(E:E,"<="&E1)+1,E:E,0))) 下にコピー。 G列をコピーして計算する列へコピー。 ってこと？ 　 （GobGob） 2015/08/31(月) 15:52 ---- あっ。 グループか。。。 G1 =IF(E1=99,"",IFERROR(C1-SUMIFS(C:C,$A:$A,$A1,E:E,COUNTIFS($A:$A,$A1,E:E,"<="&E1)+1)/COUNTIFS($A:$A,$A1,E:E,COUNTIFS($A:$A,$A1,E:E,"<="&E1)+1),"")) （GobGob） 2015/08/31(月) 16:03 ---- GobGobさん、ありがとうございます。 いやぁ〜、こんなにアッサリとできるもんなんですねぇ．． 私はSUMPRODUCTをひねくってましたが、どうにも．．． COUNTIFSでこのように“割って引く”ことに思いが到りませんでした。 スッキリして素晴らしい式だとただただ感服しています。 今日は単一グループ内でのマクロ（ソートなし）はできたんですが、 これを全グループ範囲に適用する方法がわからず．．疲労困憊です（涙） 実務はご提案の式を使わせていただいて、マクロはまだまだこれからの 課題とします。 これからもよろしくお願いします。 （T1508） 2015/08/31(月) 17:30 ---- 「99」の存在がねぇ。。。 COUNTIFSの結果が「99」のとき、なんか 不具合ありそうなので、 ランクの列は「99」やめて「-」とかにしたほうが いいかもね。（検証してまへん） あと、参考に 配列数式なら G1 =IFERROR(C1-LOOKUP(1,0/(($A$1:$A$99=$A1)*(E$1:E$99=SUMPRODUCT(($A$1:$A$99=$A1)*(E$1:E$99<=E1))+1)),C$1:C$99),"") G1 =IFERROR(C1-LOOKUP(1,0/(($A$1:$A$99=$A1)*(E$1:E$99=COUNTIFS($A$1:$A$99,$A1,E$1:E$99,"<="&E1)+1)),C$1:C$99),"") とか。 ※これも「99」検証してまへん。 　 （GobGob） 2015/08/31(月) 18:33 ---- よく見たら最大18位なんすね。 なら99気にしなくてエエねw （GobGob） 2015/08/31(月) 18:47 ---- よくよく考えたらランキングされてるんやから G1 =IF(E1=99,"",IFERROR(C1-SUMIFS(C:C,$A:$A,$A1,E:E,E1+COUNTIFS($A:$A,$A1,E:E,E1))/COUNTIFS($A:$A,$A1,E:E,E1+COUNTIFS($A:$A,$A1,E:E,E1)),"")) で、エエかも。 （GobGob） 2015/08/31(月) 23:24 ---- なるほど、言われてみればという感じですね。 参考にさせていただきました。 ところで、 私が提示したのは、ソートさえすれば、 全グループに対してまったく同一の計算式で、 しかも、前の１行との関係だけ見れば算出できるというものでした。 しかるに、 > 今日は単一グループ内でのマクロ（ソートなし）はできたんですが、 > これを全グループ範囲に適用する方法がわからず．．疲労困憊です と全く別の方向に進まれたのは意外でしたし、 疲労困憊と言われましてもという感じです。 （γ） 2015/09/01(火) 06:38 ---- GobGobさん、βさん、再度ありがとうございます。 ご提示の式、さらにスッキリしていいですね．．勉強になります。 >．．．と全く別の方向に進まれたのは意外でした．．． これは失礼しました。　 マクロのオートフィルタで手こずりいつのまにか脱線していました。 （当たり前ですが自分以外のせいにしているわけではありません） 時間かかろうと必ずやり遂げるつもりでいますが、“白旗”を挙げた時は 手を差伸べていただきたく．．．（苦笑） これからもよろしくお願いします。 （T1508） 2015/09/01(火) 15:16