下記のようにExcelのSheet1にデータが入力されているとします。
(セルは「」で区切ります。)※添付ファイル参照
-----------------------------
A、あ、1、a、2300
A、あ、2、b、2500
A、い、3、c、2300
B、あ、4、d、2900
B、い、5、e、3100
-----------------------------
※左から、列名:種別1、種別2、製品コード、製品名、価格とします。
このリストを使用して、別シートに下記のような仕組みを作成したいです。
----------------------------
種別1選択、種別2選択、製品名選択
■■この行にはリストボックスを作成し、
それぞれをリストから選択できるようにします。(添付ファイル参照)■■
■■その下に、製品コードと価格を表示させたいです。■■
-----------------------------
実現可能でしょうか。。。
すみません、教えてください。
No.7ベストアンサー
- 回答日時:
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
- 回答日時:
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
- 回答日時:
さてでは,シート1に元のデータがあるとして。
シート1は綺麗に並べ替え済みの前提で。補助列とかは無しで。
手順:
シート2の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))
シート2の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))
シート2の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
- 回答日時:
こんばんは!
一例です。
本来であれば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
- 回答日時:
ANo2です 訂正
製品名は
=VLOOKUP(C1,Sheet1!C2:D6,2,FALSE)
価格は
=VLOOKUP(C1,Sheet1!C2:D6,2,FALSE)
の間違いでした
No.2
- 回答日時:
製品コードを一番右の列に変更してください。
製品名は
=VLOOKUP(C1,Sheet1!C2:D6,2,FALSE)
製品コードは
=VLOOKUP(C1,Sheet1!C2:E6,3,FALSE)
で求められます。
リストは、たとえば製品名ならばa~eまでのセルを選択して
名前をつけます(たとえば製品名)
入力規則でリストを作成し元の値を
=製品名
とします。
No.1
- 回答日時:
基本の手順は次のようです。
まずAの配下の種別2のセル3個(B2:B4)を選び,名前ボックス(数式バーの左端,通常A1などと表示が出ている所)の中に _A と記入して名前を付ける。(重要:名前のAは,実際の種別1の記載内容と全く同じにすること。その前にアンダーバーを付ける。以下同文。)
B配下の種別2のB5:B6にも同様に _B と名前を付ける。
Aのあの配下の製品名のセル2個(D2:D3)に _A_あ と名前を付ける
Aのいの配下の製品名のセル1個(D4)に _A_い と名前を付ける
Bのあの配下の製品名のセル1個(D5)に _B_あ と名前を付ける
Bのいの配下の製品名のセル1個(D6)に _B_い と名前を付ける
別シートのA2に入力規則を取り付け
種類 リスト
元の値 A,B
とする
別シートのB2に入力規則を取り付け
種類 リスト
元の値 =INDIRECT("_"&A2)
とする
別シートのC2に入力規則を取り付け
種類 リスト
元の値 =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を使わなくても良い)
まず手作りで,ここまで作って動作を確認します。
今回のご相談のスコープとして,「とりあえずこうすれば出来ます」手順をご紹介しました。
理屈をよく消化できたあと,必要に応じて名前定義の高度化について更に検討を進めてみてください。
○元の表を,細工しやすいように作り変えるアプローチ(割と簡単?)
○元の表を維持したまま,チカラワザの数式で名前定義の範囲を計算させるアプローチ(困難)
○マクロの併用(マクロの知識があれば,まぁまぁ容易)
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・人生のプチ美学を教えてください!!
- ・10秒目をつむったら…
- ・あなたの習慣について教えてください!!
- ・牛、豚、鶏、どれか一つ食べられなくなるとしたら?
- ・【大喜利】【投稿~9/18】 おとぎ話『桃太郎』の知られざるエピソード
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Outlook で宛先が複数の場合の人数
-
マクロ自動コピペ 貼り付ける場...
-
英数字のみ全角から半角に変換
-
Excel テーブル内の空白行の削除
-
会社PCのメールが更新されない
-
Microsoft365で写真をアルバム...
-
Microsoft Officeを2台目のPCに...
-
teams設定教えて下さい。 ①ビデ...
-
Microsoft Formsの「個人情報や...
-
outlookのメールが固まってしま...
-
【Excel VBA】PDFを作成して,...
-
大学のレポート A4で1枚レポー...
-
エクセルでXLOOKUP関数...
-
Outlook 電源OFFの受診の仕方
-
Office 2021 Professional Plus...
-
Officeを開くたびの「再起動メ...
-
会社のOutlookにてメールを予約...
-
何このステータスバー
-
エクセルのシフト表を簡単にGoo...
-
http://oshiete.goo.ne.jp/qa/1...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
英数字のみ全角から半角に変換
-
outlookのメールが固まってしま...
-
会社PCのメールが更新されない
-
マイクロソフト 一時使用コード...
-
大学のレポート A4で1枚レポー...
-
Officeを開くたびの「再起動メ...
-
Microsoft Formsの「個人情報や...
-
one drive使えるpcを買う
-
マクロ自動コピペ 貼り付ける場...
-
PDFのハイパーリンクを自動的に変更し...
-
あらかじめ用意したテンプレー...
-
別シートの年間行事表をカレン...
-
【Excel VBA】PDFを作成して,...
-
エクセルでXLOOKUP関数...
-
office365って抵抗感ないですか?
-
ステータスバーの合計に表示さ...
-
【スプレドシート】IF関数の複...
-
Teamsで課題を提出します。 画...
-
Outlook で宛先が複数の場合の人数
-
Microsoft Officeを2台目のPCに...
おすすめ情報