Excelで、1行の入力に対して複数行の答えが返ってくるやり方について

Question

いつもお世話になっております。

売れた商品のデータを商品管理ソフトにインポートするために、
セット商品をバラしたデータを作りたいです。

1行入力したら、複数行の答えが返ってくるようなものを作りたいのですが、どのようにしたらよいでしょうか。

イメージとしては、
A1セルに　Aset　と入れたら
C1セルに　11-001
C2セルに　11-002
C3セルに　11-003　と自動で入力されるようなものです。

1行につき1行の答えが返ってくる単品商品は、商品マスターを作りVLOOKUP関数で出来たのですが……。
マクロはやったことがないので、可能であれば使わずに出来たらと思います。

よろしくお願いいたします。

Accepted Answer

ECマスターズの田中です。

いただいた画像の中には、
「asetの中には、11-001と11-002と...11-005が含まれている」
という、セット商品（以下、親商品）の商品コードと、
そのセットに含まれる商品（以下、子商品）の商品コードの対応表が見受けられませんが、
そのデータも同じブック内にございますでしょうか。
（「商品マスター」シート、などでしょうか）
また、そのデータは、セット商品コードの順で並び替えられておりますでしょうか。
（同じセット内の子商品が、飛び飛びで登録されてはおりませんでしょうか）

上記を満たしていれば、関数を駆使して
親商品のコードから小商品のコードのリストを取得することができます。
関数では一括は難しいので、随時入力する想定でご案内します。

まずは、下記スプレッドシートをご参照ください。
https://docs.google.com/spreadsheets/d/1iMIgniNzNBEM8mTY9n_p8tXLxP-SuaQr6hm6CDQjUcw/edit?usp=sharing
（閲覧のみの設定になっておりますので、編集したい場合はコピーしてご利用ください）

A・B列に親商品と子商品のリストがあり、
D2〜F3セルに入力した親商品のコード・商品名・数量によって
パターンA・パターンBにそれぞれ、子商品のコードおよび親商品の商品名・数量が
並んでいることが、おわかりいただけるかと思います。

両パターンともに、
リストで最初に見つかった商品から
１つずつ下のセルを見に行くことで、
子商品の洗い出しを行っております。
Aパターンでは次のセットの子商品も取得してしまいますが、
Bパターンのように手を加えることで、取得しないようにすることも可能です。
（「###」という表記は、関数内で書き換え可能です）

それぞれの数式にて、
A:Aを、親子対応表における親商品の列、
B:Bを、同表の子商品の列、
D$2を、親商品番号の入力欄（行番号を絶対参照(※)）に
置き換えていただくことで、
kami-kyotoさんのブックでも、子商品のリストを取得することができます。

※絶対参照とは、式の中でセルがずれないように$を記入して固定する方法です。
　下記ページをご参照ください。
　https://www.becoolusers.com/excel/absolute-references.html

また、いただいた画像から察しますと、
商品名は入力した名前＋行番号から計算したナンバー数、
数量は入力した値の合計で取得できるかと思います。
スプレッドシートでは、ここでも絶対参照を利用して数式を書いております。

まずはこのスプレッドシートをご参照いただき、
１つの親商品の番号をコピペすると結果が出力されるシート作りをお試しの上、
不明な点、想定している内容と異なる点などございましたら
お知らせいただきますようお願いします。

Answer

ECマスターズの田中です。

VLOOKUPを始めとした関数単体では、
１つの入力で複数の結果を返すことができませんので、
マクロの作成、もしくはPowerQueryの利用が必要となります。

PowerQueryはプログラムを組む必要はありませんが、
データベースの知識が必要となります。
下記URLのような処理を行うイメージです。
https://qiita.com/mosugi/items/b252aa9c9188e2ab2bde

関数を利用して手間を省くのであれば、
A列のデータから該当するC列のデータを絞り込み（COUNTIF関数を利用）、
絞り込んだ結果から希望のデータを抽出する（VLOOKUP関数を利用）、
という形になります。

Answer

関数単体では出来ないのですね。
ご紹介いただいたPowerQueryはちょっとハードルが高いので、

＞A列のデータから該当するC列のデータを絞り込み（COUNTIF関数を利用）、
＞絞り込んだ結果から希望のデータを抽出する（VLOOKUP関数を利用）、

これのやり方をもう少し詳しく教えていただいてもいいでしょうか。

よろしくお願いいたします。

Answer

ECマスターズの田中です。

> これのやり方をもう少し詳しく教えていただいてもいいでしょうか。
承知しました。

具体的なご案内の前に、
ご希望の内容を詳しくご確認させていただきますようお願いします。

取得元となる、売れた商品のデータは、
１行につき１セットが並んでいる形でしょうか。
その場合、枝番号（C列に出したいデータ）は、どのような形式で記載されておりますでしょうか。
（１つのセルの中に、11-001/11-002/11-003、001 002 003、001.002.003、など）
あるいは、１行につき１つの枝番号ごとのデータが並んでおり、
その中からセット番号で絞り込みを行いたい、という形でしょうか。

最初に回答差し上げた際は、
A列にセットのIDが並んでいるイメージをしていたのですが、その認識でよろしいでしょうか。
あるいは、A1セルに随時手入力して、１セットずつ結果を見る形でしょうか。
あるいは、元データをすべて一括でバラす形でしょうか。

Answer

＞取得元となる、売れた商品のデータは、
＞１行につき１セットが並んでいる形でしょうか。
はい。画像1の左のデータがそうです。

＞その場合、枝番号（C列に出したいデータ）は、どのような形式で記載されておりますでしょうか。
＞（１つのセルの中に、11-001/11-002/11-003、001 002 003、001.002.003、など）
＞あるいは、１行につき１つの枝番号ごとのデータが並んでおり、
＞その中からセット番号で絞り込みを行いたい、という形でしょうか。
画像3のような形となっております。
これはとりあえずで作ったので、適切な形へと変更可能です。

＞最初に回答差し上げた際は、
＞A列にセットのIDが並んでいるイメージをしていたのですが、その認識でよろしいでしょうか。
＞あるいは、A1セルに随時手入力して、１セットずつ結果を見る形でしょうか。
＞あるいは、元データをすべて一括でバラす形でしょうか。
画像2の青枠部分をコピーして使えたらと思っているので、列は違いますがセットのIDが並んでいるイメージではあると思います。
これを一括でバラせたらと思っています。
関数で無理な場合は、随時手入力でも構いません。


画像1の左に売れた商品のデータを貼付けると、右側に登録用のデータが出来ます。
商品ごとに異なるフローがあるので、それを作業列(I列)で分けておきます。(VLOOKUP関数を使っています)
青枠部分を画像2のように別シートにコピペします。

作業列(A列)で「別途入力」となっているのが、セット商品です。
これを、他の「登録」となっている商品と同じように登録できる形に変換したいというのが目的です。

セット商品は、手入力しようとしていたので画像3のようになっています。
Aセットが3個売れたからAセットを探して3と入れる。
Bセットは4個売れたからBセットを探して4と入れる。
などとやっていたら絶対にミスするな、と思ったので、
ここを手入力以外でやりたいと思っています。

画像2の青枠部分をどこかにコピペしたら、画像3の緑枠がぽんと出てくるのが理想です。
関数では難しい場合は、商品番号と個数を随時入力して１セットずつ結果を見る形でも大丈夫です。

わかりにくい説明となってしまって申し訳ありません。
よろしくお願いいたします。###HR######IMG0d55049c1287061826dcf59ef72a9d86f.jpegIMG0### ###IMG14222209076a8293e42f301fdd3fae7bf.jpegIMG1### ###IMG236a25e8cd229f682a65554f6c43c1957.jpegIMG2###

Answer

ECマスターズの伊藤です。

頂いておりますご質問についてですが、
担当より、明日以降に回答させていただきます。

恐れ入りますが、ご回答まで今しばらくお待ちくださいますよう
お願いいたします。

Answer

ありがとうございます！
ほとんどの部分で希望通りの形の結果を出すことが出来ました！

ただ、数量のところだけわかりませんでした。
スプレッドシートの関数ですと売れた数を足す形かと思うのですが、販売方法が少し違いまして、
asetには
　11-001　が　3個
　11-002　が　3個
　11-003　が　3個　入っていて、
売れた数が　5　の場合
子商品の数量は　各15　になる
というような商品が多いです。


画像の
緑より左は親商品と子商品のリストです。
緑上部1行が入力スペースです。
緑より右が結果です。

セット内容　×　売れた数　＝　入力したい数量
となるようにしたいです。

セット内容　は、それぞれの子商品の番号に紐づいて入力済みです。
売れた数　　は、商品番号を入れるときに一緒に入力します。



何度も申し訳ありません。
よろしくお願いいたします。###HR######IMG08938faa783b144bb518a555fdf741cdf.jpegIMG0###

Answer

ECマスターズの田中です。

> セット内容　×　売れた数　＝　入力したい数量

ということでしたら、AD2セルに下記の数式を入力する形でいかがでしょうか。

=IF(INDEX(A:A,MATCH(I$2,A:A,0)+ROW()-ROW(D$2))=D$2,INDEX(F:F,MATCH(I$2,A:A,0)+ROW()-ROW(D$2))*N$2,"###")

条件分岐の部分は商品コードのセルと同じで、実際の値は
INDEX(F:F,MATCH(I$2,A:A,0)+ROW()-ROW(D$2))*N$2
のように計算します。つまり、

子商品の商品コードに対応する数量 × N2セルに入力した数量

が、AD列に表示されていきます。

Answer

表示されました！

非常に助かりました。
ありがとうございました！