[[20120622093955]]　『条件に合う数値の抽出』（pripri）　ページの最後に飛ぶ

『条件に合う数値の抽出』（pripri）

【Excel2003,xp】
下記のような同じ「番号」が複数個ある「数値」データがあり、それぞれの「番号」の
200000以上の最小値を別シートに記載したいと考えています。
できるだけ簡単なやり方で行いたいのですが、ご教授いただけますか?

sheet1
番号　　数値　　　　　項目1　項目2　…
1　　　　120000
1　　　　190000
1　　　　210000　→これ
1　　　　300000
2　　　　150000
2　　　　250000　→これ
2　　　　350000
3　　　　…

sheet2
1　　　　210000
2　　　　250000
3　　　　…

 例題どおり、昇順でならんでいるのなら VLOOKUPで出来ると思うよ。

 ならんでないのなら

	A	B	C	D	E	F
1	番号	数値			番号	数値
2	1	120000			1	120000
3	1	190000			2	150000
4	1	210000			3	3000
5	1	300000				
6	2	150000				
7	2	250000				
8	2	350000				
9	3	3000				
10

 ※例として同シート内

 F2 =SMALL(INDEX((A$2:A$1000=E2)*B$2:B$1000,),COUNTIF(A$2:A$1000,"<>"&E2)+1)

 (GobGob)

 昇順であれば
 =INDEX(B2:B9,MATCH(E2*1000000+200000,INDEX(A2:A9*1000000+B2:B9,))+1)

 =MIN(INDEX(($A$2:$A$9<>E2)*1000000+($B$2:$B$9<200000)*1000000+$B$2:$B$9,))

 該当が無い場合を考慮していません

By

 ＞200000以上の最小値を

 見逃しｗ。

 Byさんと同じ考えだけど

 F2 =IF(SUMPRODUCT((A$2:A$9=E2)*(B$2:B$9>=200000)),MIN(INDEX(((A$2:A$9<>E2)+(B$2:B$9<200000))*10^16+B$2:B$9,)),"")

 (GobGob)

GobGobさま、byさま

たくさんの回答をありがとうございます。

回答をいただいて、やってみたところ、こんな結果でした。
(あまりにたくさん回答をいただいたので、びっくりしています。
返信しようとしたら、次のが来ていて。。このサイト、すごいですね。。。)

=SMALL(INDEX((A$2:A$1000=E2)*B$2:B$1000,),COUNTIF(A$2:A$1000,"<>"&E2)+1)
→こちらは、200000以上の最小値を検索するのが目的だったため、
　"<>"の部分を"<200000"としたところ、数値が0と表示されてしまいました。
　やり方がわるかったでしょうか?

=INDEX(B2:B9,MATCH(E2*1000000+200000,INDEX(A2:A9*1000000+B2:B9,))+1)
→こちらはかなりきれいに検索できたのですが、なぜか71行目から#REFエラーが
　出てしまいました。

=MIN(INDEX(($A$2:$A$9<>E2)*1000000+($B$2:$B$9<200000)*1000000+$B$2:$B$9,))
→こちらは、200000以下の数値しかない場合、7桁以上の表示になって表示されましたが、
　200000以下の数値も拾う価値がある場合は有用ことがわかり、
　その場合、こちらの数字から1000000を引くことで目的の数値を拾うことができました。

=IF(SUMPRODUCT((A$2:A$9=E2)*(B$2:B$9>=200000)),MIN(INDEX(((A$2:A$9<>E2)+(B$2:B$9<200000))*10^16+B$2:B$9,)),"")
→こちらは、200000以下の数値しかない場合、空白表示されるので、アテンションの意味で
　よかったです。

それで、

=SMALL(INDEX((A$2:A$1000=E2)*B$2:B$1000,),COUNTIF(A$2:A$1000,"<>"&E2)+1)

の式を200000以上の最小値を検索するやり方にするためにはどうしたらよいでしょうか?

(pripri)

 =SMALL(INDEX((A$2:A$1000=E2)*B$2:B$1000,),SUMPRODUCT(TEXT(((A$2:A$1000<>E2)+(B$2:B$1000<200000)),"1;;0")*1)+1)

 エラー考慮。

 =IF(SUMPRODUCT((A$2:A$9=E2)*(B$2:B$9>=200000)),SMALL(INDEX((A$2:A$1000=E2)*B$2:B$1000,),SUMPRODUCT(TEXT(((A$2:A$1000<>E2)+(B$2:B$1000<200000)),"1;;0")*1)+1),"")

 (GobGob)

 逆の発想の方がいいかな
 =LARGE(INDEX(($A$2:$A$9=E2)*B2:B9,),SUMPRODUCT((A$2:A$9=E2)*(B$2:B$9>=200000)))

 =IF(SUMPRODUCT((A$2:A$9=E2)*(B$2:B$9>=200000)),LARGE(INDEX(($A$2:$A$9=E2)*B2:B9,),SUMPRODUCT((A$2:A$9=E2)*(B$2:B$9>=200000))),"")

 余計なお世話で
 TEXT(((A$2:A$1000<>E2)+(B$2:B$1000<200000)),"1;;0")*1 は
 SIGN((A$2:A$1000<>E2)+(B$2:B$1000<200000))  でいいかな

By

 >逆の発想の方がいいかな

 あっ！ そうか！

 ソッチがいいっすね。

 (GobGob)

 ちょっと違う発想で。

 =IF(SUMPRODUCT((A$2:A$1000=E2)*(B$2:B$1000>=200000)),MIN(INDEX(TEXT((A$2:A$1000=E2)*B$2:B$1000,"[>=200000];0;1!E+16")*1,)),"")

 (GobGob)

 配列数式の基本にもどって

 =MIN(IF(($A$2:$A$9=E2)*($B$2:$B$9>=200000),$B$2:$B$9,""))
 Ctrl + Shift + Enter

 ただし、該当する値が無い場合 0 になります

またもやたくさんの回答をありがとうございます。

=SMALL(INDEX((A$2:A$1000=E2)*B$2:B$1000,),SUMPRODUCT(SIGN((A$2:A$1000<>E2)+(B$2:B$1000<200000)))+1)
→200000以下の数値の場合、#num!エラーが出ました。

=IF(SUMPRODUCT((A$2:A$9=E2)*(B$2:B$9>=200000)),SMALL(INDEX((A$2:A$1000=E2)*B$2:B$1000,),SUMPRODUCT(TEXT(((A$2:A$1000<>E2)+(B$2:B$1000<200000)),"1;;0")*1)+1),"")
→200000以下の数値の場合、空白になりました。

=LARGE(INDEX(($A$2:$A$9=E2)*B2:B9,),SUMPRODUCT((A$2:A$9=E2)*(B$2:B$9>=200000)))
→200000以下の数値の場合、#num!エラーと、なぜか62行目より、「0」が表示されました。

=IF(SUMPRODUCT((A$2:A$9=E2)*(B$2:B$9>=200000)),LARGE(INDEX(($A$2:$A$9=E2)*B2:B9,),SUMPRODUCT((A$2:A$9=E2)*(B$2:B$9>=200000))),"")
→200000以下の数値の場合、空白になり、一部200000以上の数値があるのに、200000以下の数値を拾うところがありました。

=IF(SUMPRODUCT((A$2:A$1000=E2)*(B$2:B$1000>=200000)),MIN(INDEX(TEXT((A$2:A$1000=E2)*B$2:B$1000,"[>=200000];0;1!E+16")*1,)),"")
→200000以下の数値の場合、空白になりました。

=MIN(IF(($A$2:$A$9=E2)*($B$2:$B$9>=200000),$B$2:$B$9,""))
→確かに該当数値がない場合、0になりました。

ということで、
=MIN(INDEX(($A$2:$A$9<>E2)*1000000+($B$2:$B$9<200000)*1000000+$B$2:$B$9,))
が数式が割合簡単なのでいいのと、検証結果、200000以下の数値も出てくれないと
後の処理がまわらないことが判明したので、上記の数式を採用させていただきます。

もしかして、このまま回答を返さないと、あといくつのやり方がでてくるのだろうと、
驚いています。皆さん、お知恵をありがとうございました!!

(pripri)

 >=IF(SUMPRODUCT((A$2:A$9=E2)*(B$2:B$9>=200000)),LARGE(INDEX(($A$2:$A$9=E2)*B2:B9,),SUMPRODUCT((A$2:A$9=E2)*(B$2:B$9>=200000))),"") 
 >→200000以下の数値の場合、空白になり、一部200000以上の数値があるのに、200000以下の数値を拾うところがありました。

 これくらい自分で訂正できないのかな
 B2:B9→B$2:B$9 に変更すればいいのでは

 >=SMALL(INDEX((A$2:A$1000=E2)*B$2:B$1000,),SUMPRODUCT(SIGN((A$2:A$1000<>E2)+(B$2:B$1000<200000)))+1) 
 >→200000以下の数値の場合、#num!エラーが出ました。
 該当する値がないので、エラー処理していないので当たり前です

 >=LARGE(INDEX(($A$2:$A$9=E2)*B2:B9,),SUMPRODUCT((A$2:A$9=E2)*(B$2:B$9>=200000))) 
 >→200000以下の数値の場合、#num!エラーと、なぜか62行目より、「0」が表示されました。 
 これくらい自分で訂正できないのかな
 B2:B9→B$2:B$9 に変更すればいいのでは
 該当する値がなければ、エラー処理していないので、#num!エラーとなります

 >=MIN(INDEX(($A$2:$A$9<>E2)*1000000+($B$2:$B$9<200000)*1000000+$B$2:$B$9,)) 
 >が数式が割合簡単なのでいいのと、検証結果、200000以下の数値も出てくれないと 
 >後の処理がまわらないことが判明したので、上記の数式を採用させていただきます。

 該当する値が無いとき、どの様に処理したいのかの説明もない中で、このような
 終わり方は、失礼ですね

 私の回答は、これでおしまい

By

コメント返信：

[ 一覧(最新更新順) ]

[[20120622093955]] 『条件に合う数値の抽出』（pripri） ページの最後に飛ぶ

[[20120622093955]]　『条件に合う数値の抽出』（pripri）　ページの最後に飛ぶ