エクセル 同じ列の値を抽出し 同じ行の値の引算

質問

EXCEL2003　　

A列の複数同じ値の行の　Ｂ列の値の中から　最大　-　最小　の値をだしたい。
どうしても解からず　手入力してます。　　1000行以上あり困ってます。よろしくお願いします。

　　A列 B列
　　111 　2
　　111 　3
　　111 　5 　　　　3←　5-2
　　222 　3
　　222 　6
　　222 　8
　　222 　9 　　　　6　←　9-3
　　333 　1
　　333 　2
　　333 　3 　　　　2　←3-1

回答No.3

KURUMITO · ベストアンサー

A列およびB列のデータが2行目から下方にあるとしてC2セルには次の式を入力して下方にドラッグコピーします。

=IF(A2=A3,"",MAX(INDEX(B:B,MATCH(A2,A:A,0)):INDEX(B:B,MATCH(A2,A:A,0)+COUNTIF(A:A,A2)-1))&"-"&MIN(INDEX(B:B,MATCH(A2,A:A,0)):INDEX(B:B,MATCH(A2,A:A,0)+COUNTIF(A:A,A2)-1)))

お礼

ありがとうございました。これでかなりの時間短縮になりました。組み合わせ勉強になりました。本当にありがとうございました。

回答No.5

MarcoRossiItaly

No.2 です。こちらでは、質問文中のデータを使って回答済みの数式が問題なく動作することを確認した上で投稿しています。

言われて気付きましたが、B 列のどこかの行に文字列データが入力されていると、文字列から「9^9」という値を引き算しているために、エラーになります。エラー値は「#VALUE!」になっているかと思います。

その場合は数式中の「b:b」という部分を「B$1:B$10」に、「a:a」を「A$1:A$10」といった具合に書き換えてもらえば、エラーが出なくなるはずです。対象のセルの数が減るので、計算も早くなると思います。

C1　=max(index(B$1:B$10-9^9*(A$1:A$10<>A1),))
D1　=min(index(B$1:B$10+9^9*(A$1:A$10<>A1),))

なお「index(B$1:B$10-9^9*(A$1:A$10<>A1),)」の意味は、「「A 列の値が A1 セルの値と異なる行については同じ行の B 列の値から「9^9」を、そうでない行については「0」を引いてできた 10 個の数からなる数列」を INDEX 関数で配列にしている」です。

お礼

ご丁寧にありがとうございます。質問に連絡までしてお答え頂きました事感謝します。

ご指摘とおりA,B１セルにタイトルが入っていました。
B2：B10にするとエラーは消えました。

べき算　無知ゆえ理解できず　これから勉強します。
本当にありがとうございました。

回答No.4

KURUMITO

回答3です。最大値から最小値を引いた値を表示させるのでしたね。その場合にはC2セルに入力する式は次の式になります。

=IF(A2=A3,"",MAX(INDEX(B:B,MATCH(A2,A:A,0)):INDEX(B:B,MATCH(A2,A:A,0)+COUNTIF(A:A,A2)-1))-MIN(INDEX(B:B,MATCH(A2,A:A,0)):INDEX(B:B,MATCH(A2,A:A,0)+COUNTIF(A:A,A2)-1)))

お礼

色々とありがとうございます。　中の組合せ　こんなにこんなに・・と　　やっぱり私には無理でした。
とても勉強になりました　

ほんと感謝します。ありがとうございました。

回答No.2

MarcoRossiItaly

C1　=max(index(b:b-9^9*(a:a<>a1),))
D1　=min(index(b:b+9^9*(a:a<>a1),))
E1　=c1-d1

お礼

ありがとうございました。

演算子^　<>　知りました。ヽ(^o^)丿　色々あるのですね。楽しくなりました。

すっきりした綺麗な関数で嬉しいのですが　でも折角教えて頂いたのですが　すみません・・・数式エラーになります。
　index(b:b+9^9*(a:a<>a1),)　が無知の為　わかりません。これから勉強します。ありがとうございました。

回答No.1

keithin

こんばんは。

ご相談に書かれている通りＡ列がキチンと並べ替え済みなら話は簡単で。
Ｃ２に
=IF(A2=A3,"",MAX(OFFSET(INDEX(B:B,MATCH(A2,A:A,0)),0,0,COUNTIF(A:A,A2),1))-MIN(OFFSET(INDEX(B:B,MATCH(A2,A:A,0)),0,0,COUNTIF(A:A,A2),1)))
リスト下端までコピー貼り付けます。

＃Ｂ列も昇順に並べ替え済みならもっと簡単になりますが、まぁ一般化しておいて良いと思います。

＃参考
Ａ列が並べ替えられていないときは、リストが１０００行程度なら
Ｃ２に
=IF(MATCH(A2,A:A,0)=ROW(A2),MAX(IF($A$1:$A$1000=A2,$B$1:$B$1000))-MIN(IF($A$1:$A$1000=A2,$B$1:$B$1000)),"")
と記入、必ずコントロールキーとシフトキーを押しながらＥｎｔｅｒで入力、リスト下端までコピー貼り付けても出来ます。
（簡単のためＡ列のそれぞれ初出の行に結果を表示します）

お礼

何パターンもありがとうございます。ベストアンサー間違えてしまいました。
こちらの上のものと　No.4がうまくいきました。
本当に感謝します。ありがとうございました。