C１の検索値からC行を取り出す

質問

お世話になります。
下記データの２つの抽出方法を教えて下さい。

１行目には20項目くらいあります（商品コード・品名・価格・保持拠点等）
２行目以降9999までデータが入っています。
商品コード　品名　価格　渋谷　新宿　池袋　秋葉原　上野　東京　品川　・・・・
08149　　りんご　30　　2　　　1　　9　　　1　　　99　0　　　3　
093081　 ばなな　80　　9　　　0　　2　　　1　　　99　3　　　0　
・・・・

(1)【上記データで別シートに商品コードを入力しその列のデータを取り出す】
例：別シートA２に093081と入力→B1から横列に項目反映
　　　　　　　　　　　　　　　　　B2から横列にデータ反映
(2)【上記データで別シートに商品コードを入力しその列の０以外のデータを取り出す】
　※０のデータは項目も含めて取り出さない。
例：別シートA2に08149と入力→B1から横列に項目反映。但し東京は反映させない。　　　　　　　　　　　　　　　　　　B2から横列にデータ反映。但し東京は反映させない。

INDEX関数・MATCH関数使ってみましたが、自分には出来ませんでした＞＜
早めにご教授頂ければ助かりますが、ご回答頂けるまでお待ちします。
宜しくお願い致します。

回答No.6

MackyNo1

参考までに。

2つ目の質問に対する回答では配列数式を使用していますのでCtrl+Shift+Enterで確定する必要がありました。
また、Excel2007以降でないと使用できないIFERROR関数も使用しています。

Excel2003以前のバージョンでも使用でき、かつCtrl+Shift+Enterで確定する必要のない数式にしたいなら、以下のような数式を入力してください。

B1セル（項目名の表示）

=INDEX(Sheet1!1:1,SMALL(INDEX((INDEX(Sheet1!$B:$T,MATCH($A$2,Sheet1!$A:$A,0),)=0)*10000+COLUMN($B:$T),),COLUMN(A:A)))&""

B2セル

=IF(B1="","",INDEX(INDEX(Sheet1!$A:$T,MATCH($A$2,Sheet1!$A:$A,0),),SMALL(INDEX((INDEX(Sheet1!$B:$T,MATCH($A$2,Sheet1!$A:$A,0),)=0)*10000+COLUMN($B:$T),),COLUMN(A:A))))

回答No.5

tom04

こんにちは！

＞例：別シートA２に093081と入力→B1から横列に項目反映
＞B2から横列にデータ反映

とありますので、項目名と各データを表示するという解釈です。
（毎回1データずつの表示とします）

↓の画像で上側が元データのSheet1で下側のSheet2に表示させるとします。

Sheet2のB1セルに
=IFERROR(INDEX(Sheet1!$A1:$Z1,,SMALL(IF(OFFSET(Sheet1!$A1:$Z1,MATCH($A2,Sheet1!$A:$A,0)-1,,1)<>0,COLUMN($A1:$Z1)),COLUMN(B1))),"")
これは配列数式になりますので、Ctrl+Shift+Enterで確定！
この画面からコピー＆ペーストする場合は
上記数式をドラッグ＆コピー　→　Sheet2のB1セルを選択　→　数式バー内に貼り付け　→　そのまま（編集可能なまま）
Ctrl+Shiftキーを押しながらEnterキーで確定！
数式の前後に{ }マークが入り配列数式になります。

次にSheet2のB2セル（配列数式ではありません）に
=IFERROR(INDEX(Sheet1!$A:$Z,MATCH($A2,Sheet1!$A:$A,0),MATCH(B$1,Sheet1!$1:$1,0)),"")
という数式を入れ、B1・B2セルを範囲指定　→　B2セルのフィルハンドルで右へずぃ～～～！っとコピー！
これで画像のような感じになります。m(_ _)m

回答No.4

MackyNo1

質問内容を勘違いしていたかもしれないのでNo1の回答の補足です。

基本的に元データのシートにコード番号は１種類しかなく、そのコードの行を別シートのB2セルに抽出したいということでしょうか？

その場合は、１の質問なら以下のような関数が簡単です。

=VLOOKUP($A$2,Sheet1!$A:$T,COLUMN(B:B),0)

２の質問は、
項目名を表示するB1セルに以下の式を入力し、Ctrl+Shift+Enterで確定して右方向にオートフィルします。

=IFERROR(INDEX(Sheet1!1:1,SMALL(IF(VLOOKUP($A$2,Sheet1!$A:$T,COLUMN($B:$T),0)<>0,COLUMN($B:$T)),COLUMN(A:A))),"")

同様にその値を表示するB2セルには以下の式を入力してCtrl+Shift+Enterで確定して右方向にオートフィルします。

=IFERROR(INDEX(VLOOKUP($A$2,Sheet1!$A:$T,COLUMN($A:$T),0),SMALL(IF(VLOOKUP($A$2,Sheet1!$A:$T,COLUMN($B:$T),0)<>0,COLUMN($B:$T)),COLUMN(A:A))),"")

#Excelのバージョンが記載されていなかったので、ひとまずExcel2007以降のバージョンの例で説明しましたが、Officeソフトはバージョンによって使用できる機能や操作方法が大きく異なりますので、質問の際には必ずバージョンを明記するようにしましょう。

お礼

回答ありがとうございます。
まず、タイトルと内容で質問内容が違って申し訳ありません。お聞きしたいのは質問内容の方なので、タイトルの方は無視して頂くようお願い致します。
また内容はNO.５様がそのものずばり貼り付けて頂きました。
コード番号は１～９９９９まであると仮定します。

エクセルのバージョンは２００７なので言われた数式等は問題ありません。
言葉足らずでスミマセンでした。

回答No.3

mt2008

(1)-2のセル式は以下の方が良いですね。

2.Sheet2のB2セルに以下の式を入れ、右方向にコピー
　=DGET(Sheet1!$A:$J,B1,$A$1:$A$2)
↓
　=IFERROR(DGET(Sheet1!$A:$J,B1,$A$1:$A$2),"")

回答No.2

mt2008

こんな感じでどうでしょう
＃データ用シートをSheet1、別シートをSheet2とします。

(1)【上記データで別シートに商品コードを入力しその列のデータを取り出す】
#その行のデータ……ですよね？
1.Sheet2の1行目に「商品コード、品名、価格、…」とデータ用シートと同じ項目名を入力しておく
2.Sheet2のB2セルに以下の式を入れ、右方向にコピー
　=DGET(Sheet1!$A:$J,B1,$A$1:$A$2)

(2)【上記データで別シートに商品コードを入力しその列の０以外のデータを取り出す】
上記の(1)をやったうえで、Sheet2のワークシートモジュールに以下のコードを入れる。

Private Sub Worksheet_Change(ByVal Target As Range)
　　If Target.Address <> "$A$2" Then Exit Sub
　　Cells.EntireColumn.Hidden = False
　　For i = 2 To 20　'項目が20列まであると仮定
　　　　If Cells(2, i) = 0 Then
　　　　　　Cells(2, i).EntireColumn.Hidden = True
　　　　End If
　　Next i
End Sub

回答No.1

MackyNo1

質問タイトルと本文の関係がよくわからないのですが、ひとまず現時点の回答をしてみます。

＞(1)【上記データで別シートに商品コードを入力しその列のデータを取り出す】
例：別シートA２に093081と入力→B1から横列に項目反映
　　　　　　　　　　　　　　　　　B2から横列にデータ反映

同じコードの番号を複数抽出したいということでよろしいのですね。
その場合は、元データがSheet1にあり、例示のレイアウトなら、B1セルに以下の式を入力して、Ctrl+Shift+Enterの操作で配列数式にして、右方向に項目数分だけ、および下方向に適当数オートフィルしてください。

=IF(COUNTIF(Sheet1!$A$2:$A$10000,$A$2)<ROW(1:1),"",INDEX(Sheet1!B:B,SMALL(IF(Sheet1!$A$2:$A$10000=$A$2,ROW($2:$10000)),ROW(1:1))))

ただし、表示するデータ数が多くなると再計算に時間がかかりシートの動きが重くなりますので必要以上の行に数式をコピーしないようにしてください。

(2)【上記データで別シートに商品コードを入力しその列の０以外のデータを取り出す】
　※０のデータは項目も含めて取り出さない

この条件だと２つ目のデータに同じ列以外の列に0がある場合に、その列を削除すると列の表示が乱れるので一覧として抽出できないと思うのですが、具体的にどのような表示にしたいのでしょうか？