下記のようにExcelのSheet1にデータが入力されているとします。

質問

下記のようにExcelのSheet1にデータが入力されているとします。
(セルは「」で区切ります。)※添付ファイル参照
-----------------------------
A、あ、１、a、2300
A、あ、２、b、2500
A、い、３、c、2300
B、あ、４、d、2900
B、い、５、e、3100
-----------------------------
※左から、列名：種別1、種別2、製品コード、製品名、価格とします。

このリストを使用して、別シートに下記のような仕組みを作成したいです。
----------------------------
種別1選択、種別2選択、製品名選択

■■この行にはリストボックスを作成し、
　　　　それぞれをリストから選択できるようにします。(添付ファイル参照)■■

■■その下に、製品コードと価格を表示させたいです。■■
-----------------------------

実現可能でしょうか。。。

すみません、教えてください。

回答No.7

tom04 · ベストアンサー

No.4・6です！
何度もごめんなさい！

Sheet2のA9・B9セルの数式を書いていませんでした！

A9セルは
=IF(COUNTBLANK(A2:C2),"",INDEX(Sheet1!C2:C5000,MATCH(A2&B2&C2,Sheet1!L2:L5000,0)))

B9セルは
=IF(A9="","",VLOOKUP(A9,Sheet1!C2:E5000,3,0))

としてみてください。

どうも何度もごめんなさいね。m(__)m

回答No.6

tom04

No.4です！
返信が遅れてごめんなさい！

データ量がかなり多いということなので、もう一度考えてみました。

↓の画像のようにSheet1に作業用の列を複数使用することになりますが・・・

作業列F2セルに
=IF(COUNTIF(A$2:A2,A2)=1,ROW(A1),"")
という数式を入れ、隣のG2セルまでコピーします。
H2セルには
=IF(AND(A2=Sheet2!$A$2,B2=Sheet2!$B$2),ROW(A1),"")

I2セルは
=IF(COUNT(F$2:F$5000)<ROW(A1),"",INDEX(A$2:A5000,SMALL(F$2:F$5000,ROW(A1))))
として隣のJ2セルまでコピー
K2セルは
=IF(COUNT($H$2:$H$5000)<ROW(A1),"",INDEX($D$2:$D$5000,SMALL($H$2:$H$5000,ROW(A1)))&"")
最後のL2セルには
=A2&B2&D2

として、F2～L2セルを範囲指定し、L2セルのフィルハンドルでダブルクリック、又はオートフィルで下へずぃ～～～！っとコピーします。

次にSheet2にI・J・K列をリスト表示させたいので
各列「名前定義」しておきます。
当方使用のExcel2003の場合は　メニュー　→　挿入　→　名前　→　「作成」から範囲指定し、「上端行」で名前定義できますし、
2行目以降を範囲指定した後に直接名前ボックスに入力しても構いません。
今回は仮に　I2以降を範囲指定　→　「リスト1」と名前定義
同様にJ2以降範囲指定　→　「リスト2」と名前定義　K2以降・・・　→　「リスト3」と
名前定義したとします。

Sheet2のA2セルをアクティブ　→　データ　→　入力規則　→　リスト　→　元の値の欄に
=リスト1　としてOK

同様にB2セル　→　「リスト2」・・・とC2セルまで入力規則のリスト設定を行います。
これでA2のリストで表示したものでヒットするものがB2のリスト表示の候補になるはずです。
同様に、A2・B2で選択したものにヒットするものがC2セルのC2セルにリスト候補になり、
C2セルを選択すればその結果がSheet2のA9・B9セルに表示されると思います。

尚、当然のことながら「商品コード」に重複は無いしています。
そして、数式は5000行目まで対応できるようにしていますが、データ量によって範囲指定の領域はアレンジしてみてください。

この程度の方法しか思い浮かびませんが
他に良い方法があれば無視してくださいね。
どうも長々と失礼しました。m(__)m

回答No.5

keithin

さてでは，シート１に元のデータがあるとして。
シート１は綺麗に並べ替え済みの前提で。補助列とかは無しで。

手順：
シート２のA2に　入力規則　で　リスト　で　A,B

そのA1でどれか選んでおいてから
名前の定義を開始，次を登録
　名前　rngB
　参照範囲　=IF(Sheet2!$A$2="","",OFFSET(Sheet1!$A:$A,MATCH(Sheet2!$A$2,Sheet1!$A:$A,NOW()*0)-1,1,COUNTIF(Sheet1!$A:$A,Sheet2!$A$2),1))

シート２のB2に　入力規則　で　りすと　で =rngB

そのB2で何か選んでおいてから
名前の定義を開始，次を登録
　名前　rngD
　参照範囲　=IF(Sheet2!$B$2="","",OFFSET(rngB,MATCH(Sheet2!$B$2,rngB,NOW()*0)-1,2,COUNTIF(rngB,Sheet2!$B$2),1))

シート２のC2に　入力規則　で　りすと　で =rngD

A7に
=IF(ISERROR(MATCH(B2,rngB,0)*MATCH(C2,rngD,0)),"",OFFSET(rngD,MATCH(C2,rngD,0)-1,-1,1,1))
B7に
=IF(ISERROR(MATCH(B2,rngB,0)*MATCH(C2,rngD,0)),"",OFFSET(rngD,MATCH(C2,rngD,0)-1,1,1,1))

以上です。

回答No.4

tom04

こんばんは！
一例です。
本来であればSheet2のA2セルのリストで選択したデータに該当するものが種別2のB2セルのリスト候補に
そしてB2セルのリストで選択したものがC2セルのリスト候補に選択されるのが一番良い方法なのですが、
今回の場合、A・B別の種別1でも種別2には同じデータがあるわけですよね？

その場合はC2セルのリスト候補を順に絞っていく！というのは少し難しくなりますが・・・
↓の画像のSheet1のようにリストに表示させるために少し表をアレンジしています。
そして、H1～I1セルを範囲指定し、「種別」と名前定義します。
同様にH2～H3セルを「A」と名前定義、I2～I3セルを範囲指定　→　「B」と名前定義
K2～K4セルを「あ」と名前定義、L2～L3セルを「い」と名前定義しています。

そしてF列に作業用の列を設け、F2セルに
=A2&B2&D2
という数式を入れ、オートフィルで下へずぃ～～～！っとコピーします。

Sheet2のA2セルには入力規則のリストから数式欄に
=種別　としてOK
B2セルにも同様に入力規則のリストから数式欄に
=INDIRECT(A2)　としてOK
C2セルには
=INDIRECT(B2)　としてOK
本来であればA2で絞ったものがB2のリストに、B2で絞ったものがC2のリスト候補になるはずですが
今回の場合は重複している項目がありますので、元データにないものもリストで表示されると思います。

最後にA9セルは
=IF(COUNTBLANK(A2:C2),"",INDEX(Sheet1!C2:C1000,MATCH(A2&B2&C2,Sheet1!F2:F1000,0)))
とし、B9セルに
=IF(A9="","",VLOOKUP(A9,Sheet1!C2:E1000,3,0))
という数式を入れると画像のような感じになります。
尚、数式はSheet1の1000行目まで対応できるようにしています。

以上、長々と書きましたが
参考になれば幸いです。m(__)m

補足

ありがとうございます。

教えて頂いた方法で作成しようとしたのですが、
実際に作成する正式データは、行数が3000行以上、種別の種類が30種類程あり、
別行作成などは非効率に感じます。。。

なにか良い方法はないでしょうか。。。。
よろしくお願いいたします。

回答No.3

kmetu

ANo2です　訂正

製品名は
=VLOOKUP(C1,Sheet1!C2:D6,2,FALSE)

価格は
=VLOOKUP(C1,Sheet1!C2:D6,2,FALSE)
の間違いでした

回答No.2

kmetu

製品コードを一番右の列に変更してください。
製品名は
=VLOOKUP(C1,Sheet1!C2:D6,2,FALSE)
製品コードは
=VLOOKUP(C1,Sheet1!C2:E6,3,FALSE)
で求められます。

リストは、たとえば製品名ならばa～eまでのセルを選択して
名前をつけます(たとえば製品名)
入力規則でリストを作成し元の値を
=製品名
とします。

回答No.1

keithin

基本の手順は次のようです。

まずＡの配下の種別２のセル３個（B2:B4）を選び，名前ボックス（数式バーの左端，通常A1などと表示が出ている所）の中に　_A　と記入して名前を付ける。（重要：名前のAは，実際の種別１の記載内容と全く同じにすること。その前にアンダーバーを付ける。以下同文。）
Ｂ配下の種別２のB5:B6にも同様に　_B　と名前を付ける。

Ａのあの配下の製品名のセル２個（D2:D3)に　_A_あ　と名前を付ける
Ａのいの配下の製品名のセル１個（D4)に　_A_い　と名前を付ける
Ｂのあの配下の製品名のセル１個（D5)に　_B_あ　と名前を付ける
Ｂのいの配下の製品名のセル１個（D6)に　_B_い　と名前を付ける

別シートのＡ２に入力規則を取り付け
　種類　リスト
　元の値　A,B
とする

別シートのＢ２に入力規則を取り付け
　種類　リスト
　元の値　=INDIRECT("_"&A2)
とする

別シートのＣ２に入力規則を取り付け
　種類　リスト
　元の値　=INDIRECT("_"&A2&"_"&B2)
とする

製品コードは
　=INDEX(OFFSET(INDIRECT("_"&A2&"_"&B2),0,-1),MATCH(C2,INDIRECT("_"&A2&"_"&B2),0))
価格は
　=VLOOKUP(C2,OFFSET(INDIRECT("_"&A2&"_"&B2),0,0,ROWS(INDIRECT("_"&A2&"_"&B2)),2),2,FALSE)
などのようにする。（勿論，無理してVLOOKUPを使わなくても良い）

まず手作りで，ここまで作って動作を確認します。

今回のご相談のスコープとして，「とりあえずこうすれば出来ます」手順をご紹介しました。
理屈をよく消化できたあと，必要に応じて名前定義の高度化について更に検討を進めてみてください。
○元の表を，細工しやすいように作り変えるアプローチ（割と簡単？）
○元の表を維持したまま，チカラワザの数式で名前定義の範囲を計算させるアプローチ（困難）
○マクロの併用（マクロの知識があれば，まぁまぁ容易）