[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『差の出し方』（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
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
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
いやぁ～、こんなにアッサリとできるもんなんですねぇ．．
私は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
ご提示の式、さらにスッキリしていいですね．．勉強になります。
>．．．と全く別の方向に進まれたのは意外でした．．． これは失礼しました。 マクロのオートフィルタで手こずりいつのまにか脱線していました。 （当たり前ですが自分以外のせいにしているわけではありません） 時間かかろうと必ずやり遂げるつもりでいますが、“白旗”を挙げた時は 手を差伸べていただきたく．．．（苦笑）
これからもよろしくお願いします。
（T1508） 2015/09/01(火) 15:16
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.