【EXCEL2003】範囲に含まれた項目を全部抜き出す関数

質問

質問してばかりで恐縮ですが、また行き詰ってしまいました。
皆さんのお知恵をお貸しください。

添付の画像をご覧いただくと分かりやすいと思いますが、範囲指定するのは『売価』です。
指定した範囲に含まれた売価の行項目を抜き出したいのです。
（表１）から100円以上200円未満の売価の商品を抜き出して（表２）を作りたいのです。

ここで質問した続きになります。
http://oshiete1.goo.ne.jp/qa5628538.html

よろしくお願いいたします。

回答No.5

tom04 · ベストアンサー

No．1です！
たびたびお邪魔します。

前回の数式はSheet1のデータが100行目まで対応できる数式でした。
補足を読ませていただくとデータが150個くらいあるということなので
101行目以降のデータが正確ではないと思います。

ということで・・・
前回の表はそのままで200行目まで対応できるような数式にしてみました。
Sheet1の作業列E2セルの数式を
=IF(OR(A2="",COUNTBLANK(Sheet2!$F$1:$F$2)>0),"",IF(AND(B2>=Sheet2!$F$1,B2<Sheet2!$F$2),RANK(B2,$B$2:$B$200,1)*1000+ROW(A1),""))
としてみてください。これをフィルハンドルの（＋）マークでダブルクリックか、オートフィルでずぃ～～～！っと下へコピー！

そして、Sheet2のB2セルに
=IF(COUNT(Sheet1!$E$2:$E$200)<ROW(A1),"",INDEX(Sheet1!A$2:A$200,MOD(SMALL(Sheet1!$E$2:$E$200,ROW(A1)),1000)))
という数式を入れ、列方向と行方向にオートフィルでコピーしてみてください。
たぶん大丈夫だと思います。
どうも何度も失礼しました。m(__)m

お礼

何度もお返事いただき恐縮してます（_　_。）

完璧です！
一桁増やせばいいだけのことだったんですね。
Excelとは奥が深いものです。

また行き詰ったときにお世話になるかもしれませんが、よろしくお願いいたします。
ありがとうございました。

回答No.4

mimeu

もしＶＢＡでやりたいのなら、こういうのも簡単かと。

Sub 条件つき転記()
Const 最低値 = 100
Const 最大値 = 200
Dim 行1 As Long, 行2 As Long, 行末1 As Long, 列 As Long, 列末 As Long
' 表１の位置は("A2")分かっていると仮定します
行末1 = Range("A2").End(xlDown).Row
列末 = Range("A2").End(xlToRight).Column
' 新しい表を２行開けた下に作る
行2 = 行末1 + 3
Cells(行2, 1) = "表２"
行2 = 行2 + 1
Range("A2:D2").Copy Cells(行2, 1)
行2 = 行2 + 1
' 新しい表に条件にあう行だけを転記
For 行1 = 3 To 行末1
If Cells(行1, 2) >= 最低値 And Cells(行1, 2) <= 最大値 Then
Range(Cells(行1, 1), Cells(行1, 4)).Copy Cells(行2, 1)
行2 = 行2 + 1
End If
Next
End Sub

Sub 黄色いマーク()
Const 列 = 7 ' データのある列
Const 行頭 = 20 ' データのある行
Const 上限 = 59
Const 下限 = 50
Dim 行3 As Long, 行末3 As Long
行末3 = Cells(行頭, 列).End(xlDown).Row
Debug.Print 行末3
For 行3 = 行頭 To 行末3
If Cells(行3, 列) >= 下限 And Cells(行3, 列) <= 上限 Then
Cells(行3, 列).Interior.ColorIndex = 6
End If
Next
End Sub

色つけなどはＥｘｃｅｌのマクロ自動記録を使えば調べられますよ。

お礼

ご回答ありがとうございます。

今回は、ＶＢＡを使ったものを希望していなかったので関数式を採用しますが、
その後上記のＶＢＡを研究してみました。

実際やってみてかなり使えそうな感じがしました。
これをきっかけに今後はこっちの研究も進めていきます。

回答No.3

noname#204879

[フィルタオプションの設定]による求め方
（此れが最も簡単！）

１．セル E3 に式 =AND(B3>=100,B3<200) を入力
２．次の条件で［フィルタオプションの設定］を実行
　　　抽出先　　　　　指定した範囲
　　　リスト範囲　　　$A$2:$D$10
　　　検索条件範囲　　$E$11:$E$12
　　　抽出範囲　　　　$A$13:$D$13

ただそれだけ。ネ、簡単でしょ。
ただし「売価を昇順で」でなく、「表1」の順序のまま。

回答No.2

tom04

No．1です！
たびたびごめんなさい。

前回の作業列の数式では降順に表示されてしまいますので、
↓のように訂正してください。

作業列E2セルの数式は
=IF(OR(A2="",COUNTBLANK(Sheet2!$F$1:$F$2)>0),"",IF(AND(B2>=Sheet2!$F$1,B2<Sheet2!$F$2),RANK(B2,$B$2:$B$100,1)*100+ROW(A1),""))

でオートフィルで下へコピーです。
Sheet2の数式はそのままでOKのはずです。
これでSheet2には売価の昇順で表示できると思います。

どうも何度も失礼しました。m(__)m

回答No.1

tom04

こんばんは！
一例です。
↓の画像で説明させていただきます。
左側が元データのSheet1で右側のSheet2のF1・F2セルに
○以上・△未満　の数値を入力すればSheet2に抽出できるようにしています。
Sheet1に作業用の列を使っています。

Sheet1の作業列E2セルに
=IF(OR(A2="",COUNTBLANK(Sheet2!$F$1:$F$2)>0),"",IF(AND(B2>=Sheet2!$F$1,B2<Sheet2!$F$2),RANK(B2,$B$2:$B$100)*100+ROW(A1),""))
という数式を入れオートフィルでずぃ～～～！っと下へコピーします。

そして、Sheet2のA2セルに
=IF(COUNT(Sheet1!$E$2:$E$100)<ROW(A1),"",INDEX(Sheet1!A$2:A$100,MOD(SMALL(Sheet1!$E$2:$E$100,ROW(A1)),100)))
という数式を入れオートフィルで列方向と行方向にコピーすると
画像のような感じになります。

尚、数式はSheet1の100行目まで対応できるようにしていますが、
データ量によって範囲指定の領域はアレンジしてみてください。

以上、参考になれば幸いですが、
他に良い方法があれば読み流してくださいね。m(__)m

補足

出来た！と喜んだのですが、よく見ると範囲外の商品が混じっていました(T-T )
作業列は正しく範囲内のものが選択されているようです。

実際作るデータは商品名が150個くらいあります。
売価列の数値がアトランダムに並んでいると、範囲外のものを含んでしまうようです。
ほとんど出来上がっているのに、なんだか悔しいです・・・