EXCELで抽出

質問

A列に型番が書いてあり、B列にそれに対するリストが書いてあります。
A001ならばノート、消しゴム、ホチキス、ボールペンで、この内容は増えることも無ければ減ることもありません。
セルE2に「B001」と書いたときに、B002のリストの内容だけが表示されるようにするにはどうすればいいのでしょうか？
また、続けてセルE9に「A001」と書いたときにはA001の内容のリストが表示されるようにしたいです。

回答No.9

imogasi

まだ開いているので、関数を使う方法の１つの、最終版を挙げてみます。
例データ　A,B列　A1:B23
A列は上からA001.B001,C001,D002,E003でF004は、そこで止め、のマークです。
A001 X1
x2
x3
B001 y1
y2
y3
y4
y5
y6
C001 Z1
z2
Z3
Z4
Z5
D002 S1
S2
S3
E003 T1
T2
T3
T4
T5
F004 ＜ーーー最終行の次には、何でもよいから、文字列を入れておいてください。
ーーー
答えの部分は　D1:I9
開始行番号 4 10 15 18 　　　<--第１行目　　＜ーー関数で出します
行数 6 5 3 5 　　　　　<--第２行目　＜ーー関数で出します
B001 C001 D002 E003 <--第３行目　＜ーーこの部分は、ほしい型番の文字列を当初各列に手入力
y1 Z1 S1 T1
y2 z2 S2 T2
y3 Z3 S3 T3
y4 Z4 T4
y5 Z5 T5
y6
関数式
　E1　=MATCH(E3,$A$1:$A$1000)　　その後、右方向に式を複写
　E２　= MATCH(0,INDEX(0/(OFFSET($A1,MATCH(E$3,$A:$A,0),0,1000,1)<>""),),0)　右方向に式を複写
　E4　=IF(ROW()-3>E$2,"",INDEX($B$1:$B$1000,E$1+ROW()-4))　下方向に式を複写
　F4　=IF(ROW()-3>F$2,"",INDEX($B$1:$B$1000,F$1+ROW()-4))　下方向に式を複写
　G4　=IF(ROW()-3>G$2,"",INDEX($B$1:$B$1000,G$1+ROW()-4))　下方向に式を複写　
　H４　=IF(ROW()-3>H$2,"",INDEX($B$1:$B$1000,H$1+ROW()-4))　下方向に式を複写　
E4－＞F4 -＞G4 -＞H4・・の式は式複写で作成できると思う。
第１、第２行が、独立したセルデータになっているのは、E4:H・・・の式が長く複雑にならないように
中間データ的に独立させたもの。
＝＝＝
結構苦労した。特に第３行目に出している、該当行数の算出です。
同じようなものを作るのにVBAなら１０行程度で済むように思う。それというのも、VBAでは、変数が使えることと、繰り返し処理をプログラムできるからです。

回答No.8

imogasi

#2です。関数で考えましたが、途中ですが記してみます。
例データ
A1:B17　A001,B001などが型番データでA列、それ以外はB列にあるデータとします。
A001 X1
x2
x3
B001 y1
y2
y3
y4
y5
y6
C001 Z1
z2
Z3
Z4
Z5
D002 S1
S2
S3
ーーー

G1:H７
G1:H1は指定型番で、当初入力されているとする。
B001 C001
y1 Z1
y2 z2
y3 Z3
y4 Z4
y5 Z5
y6
上記では目視で式の複写を止める行を決めたが、不完全です。
ーー
https://qiita.com/tacoyaky/items/4f1093607bffe1794800
【Excel関数】指定範囲で空白でない最初のセル位置を取得したい
という記事がある（そういう方法がある）ので
B001の場合
= MATCH(0,INDEX(0/(A5:A100<>""),),0)
で、「６」という行数が得られるので、関数を入れている行数（行番号）が、この６を超えると空白を返す関数を組み立ててください。
６は
y1
y2
y3
y4
y5
y6
の６です。
ーーー
回答を締め切られる時期なので、自分でやってみてください。
でも予想通り、式が長くなり、複雑ですっきりしない課題だと思う。
既に関数による回答も出ているので、そっちがすっきりしたものなら、そちらをどうぞ。

回答No.7

kkkkkm

No6の補足です。

E列にデータを入力したときにその右のF列にデータがあるとデータの書き替えはしません。既入力データの上書き防止です。
質問の画像の状態になっている場合(F8までデータがある状態)だと
E8より上にデータを入力しても何も起こりません。
E9以降にデータを入力したときには該当するデータをF列に書き込みます。

回答No.6

kkkkkm

該当シートのシートモジュールに以下で試してみてください。

Private Sub Worksheet_Change(ByVal Target As Range)
Dim FRng As Range, buf As Range
Dim LastRow As Long

If Target.Column <> Columns("E:E").Column Or _
Target.Row < Rows("2:2").Row Or _
Selection.Count <> 1 Then
Exit Sub
End If
If Target.Offset(0, 1).Value <> "" Then
Exit Sub
End If
Application.EnableEvents = False
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
Set FRng = Range(Cells(1, "A"), Cells(LastRow + 1, "A")) _
.Find(What:=Target.Value, LookIn:=xlValues, LookAt:=xlWhole)
If Not FRng Is Nothing Then
Set buf = Range(Cells(FRng.Row + 1, "A"), Cells(LastRow + 1, "A")) _
.Find(What:="*", LookIn:=xlValues, LookAt:=xlWhole)
If Not buf Is Nothing Then
Target.Offset(0, 1).Resize(buf.Row - FRng.Row, 1).Value = Cells(FRng.Row, "A").Offset(0, 1).Resize(buf.Row - FRng.Row, 1).Value
Else
Target.Offset(0, 1).Resize(LastRow - FRng.Row + 1, 1).Value = Cells(FRng.Row, "A").Offset(0, 1).Resize(LastRow - FRng.Row + 1, 1).Value
End If
Else
MsgBox "該当データがありません", vbCritical
End If
Set FRng = Nothing
Set buf = Nothing
Application.EnableEvents = True
End Sub

回答No.5

SI299792

F2: =IF(OR(AND(E2="",INDEX(A:A,MATCH(LOOKUP("ｰ",E$2:E2),A:A,0)+ROW()-MATCH(LOOKUP("ｰ",E$2:E2),E:E,0))>""),AND(E2="",F1="")),"",INDEX(B:B,MATCH(LOOKUP("ｰ",E$2:E2),A:A,0)+ROW()-MATCH(LOOKUP("ｰ",E$2:E2),E:E,0)))&""
下へコピペ。

回答No.4

msMike

[No.3]の続きです。
添付図参照(Excel 2019)
↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓

回答No.3

msMike

質問を一読して、Excel に装備されている類似の機能(データの入力規則)を思い浮かべました。
貴方は「A列に対するB列のリスト」の使用目的を説明されぬままだけど、ひょっとしたら役立つかも知れないので、参考までに[データの入力規則]を簡単に説明しておきます。
丸でMicrosoft社のお得意な“小さな親切、大きなお世話”だなぁと思うなら、どうぞ無視してください。

添付図に示すリストから、３種類のカテゴリの一つをA列に入力したとき、それに対するリストをB列に表示させ(て、その中の一つを選択す)る、という機能です。
各リスト下部の空白部に、後でデータを追加しても構いません。

続いての投稿で、A列B列のリスト選択結果を添付します。

回答No.2

imogasi

回答はエクセル関数で求めておるのか？質問者のVBAはできないなどの、自分の技量を説明して質問するもんだ。
関数では、抜出しタイプの処理は、複雑になり易く、むつかしいことが多い。
本件は、上行と同じ型番の意味で空白にしている。しかしデータベース（アクセスなど学べば判る）のデータの持ち方の禁避に当たるやり方であろう。
こんなことを考える・するとは、初心者だろう。
それで、関数でも、VBAでも難しくなる。
A列がB001などで埋まっておるデータであれば、フィルタで簡単に、ビールーコーヒーが抜き出せるが。
ーー
また１型番だけを抜き出すなら良いが、普通は
B001
C001
D002　例にはない
・・
を並べて出したいとかのニーズが多いかと思う。
そういうことは質問に書いてない。
B列該当は不定数だろうから、どういう質問設定にするかいろいろあり得る。
E列にB００１を入れて、F列に関数を入れて、適当に複写を該当行数を、目測で止めるとかの手もあるが、
普通ではない。
ーー
VBAでは１つだけの場合は、End(xlDown)　　等を使いたくなる。
Sub test01()
x = Range("D2")
rs = Range("A1:A10000").Find(x).Row
MsgBox rs
re = Cells(rs, 1).End(xlDown).Row - 1
MsgBox re
Range("B" & rs & ":B" & re).Copy Range("E2")
End Sub
のようなのも１つのやり方だろうが、型番が２つ３つの場合は、VBAを変える必要がある。

回答No.1

watanabe04

＞セルE2に「B001」と書いたときに、B002のリストの内容
＞続けてセルE9に「A001」と書いたときにはA001の内容

B002であっていますか？
あっているなら+1するのでしょうか？
Aは足さないのでしょうか？

ケアレスミスだと思いますが、
コンピュータ（プログラマー）には意味がわかりません。

補足

B001です。
B002なんて無いですね。
ケアレスミスです。