エクセルで複数列に並んでいるものを１列で表すには？

質問

エクセルで例えば
ＮＯ、店、商品・・・
１「Ａスーパ」「リンゴ」「バナナ」「ミカン」
２「Ｂコンビニ」「ナシ」「モモ」
３「Ｃ百貨店」「クリ」「ブドウ」
のように各セルに文字列が入っているものを
下記のように、「店」と「商品」がそれぞれ対応するように
一列に並べるにはどのようにしたらよいでしょうか？
１「Ａスーパ」「リンゴ」
２「Ａスーパ」「バナナ」
・・・
８「Ｃ百貨店」「ブドウ」

条件としては、
一つの行に店は１つだけ
ただし、商品の数はばらばら
です。

回答No.1

maron--5 · ベストアンサー

◆Sheet2の式
A1=IF(ROW(A1)>COUNTA(Sheet1!$B$1:$F$3),"",INDEX(Sheet1!A:F,SMALL(INDEX(SUBSTITUTE((Sheet1!$B$1:$F$3<>"")*1,0,10^3)*ROW($B$1:$F$3),),ROW(A1)),1))
★下にコピー

B1=IF(ROW(A1)>COUNTA(Sheet1!$B$1:$F$3),"",INDEX(Sheet1!B:F,SMALL(INDEX(SUBSTITUTE((Sheet1!$B$1:$F$3<>"")*1,0,10^3)*ROW($B$1:$F$3),),ROW(A1)),COUNTIF($A$1:A1,A1)))
★下にコピー

お礼

お礼が遅くなり申し訳ございませんでした。
ありがとうございます。
関数しか使えないので関数でいろいろ頑張ってみたのですが
できませんでした。
大変助かりました。

回答No.3

imogasi

#2です。作業列を使う方法を挙げておきます。
これでも複雑ですが、作業列を使わない方法に比べて、どうでしょうか。
今の行番号を元に、持って来る元データ範囲（下記ではＡ１：Ｅ６）の中で、何行数、何列数かを計算している方法です。
例データ（Ａ列は店、Ｂ－Ｅ列は品物、品物数限度４個の例）
Ａ列　　Ｂ列　　Ｃ列　　Ｄ列　　Ｅ列
a c x
d e f g g
h i j
k l
m n o p q
r s t
ーーー
Ｆ列　Ｆ１は０、Ｆ２に式　=COUNTA($B$1:E1)+1　と入れて下方向に
式を複写
結果
0
3
7
9
10
14
Ｇ列に　=MATCH(ROW(),$F$1:$F$100,1)　と入れて式を下方向に複写
結果
1
1
2
2
2
2
3
3
4
5
5
5
5
6
6
ーー
Ｉ列I１に　=INDEX($A$1:$E$100,MATCH(ROW(),$F$1:$F$100,1),1)
Ｊ列Ｊ１に　=INDEX($A$1:$E$100,MATCH(ROW(),$F$1:$F$100,1),COUNTIF($G$1:G1,G1)+1)
それぞれ下方向に式を品物数行だけ複写
結果
Ｉ列　　Ｊ列
a c
a x
d e
d f
d g
d g
h i
h j
k l
m n
m o
m p
m q
r s
r t

回答No.2

imogasi

質問者には無縁かもしれないが、こういう手の問題（表の組み換え）はＶＢＡでやると考えやすい。単純な規則性があるから。
（式でやると既出ご回答のように初心者には理解も難しい式になる。）
参考に。
ーー
例データ
質問の通り
コード
標準モジュールに
Sub test01()
d = Worksheets("Sheet1").Range("A65536").End(xlUp).Row
k = 1
For i = 1 To d
For j = 2 To Worksheets("Sheet1").Cells(i, "IV").End(xlToLeft).Column
Worksheets("Sheet2").Cells(k, 1) = Worksheets("Sheet1").Cells(i, 1)
Worksheets("Sheet2").Cells(k, 2) = Worksheets("Sheet1").Cells(i, j)
k = k + 1
Next j
Next i
End Sub
ーー
結果
Ｓｈｅｅｔ２
Ａ列　　Ｂ列
Ａスーパ リンゴ
Ａスーパ バナナ
Ａスーパ ミカン
Ｂコンビニ ナシ
Ｂコンビニ モモ
Ｃ百貨店 クリ
Ｃ百貨店 ブドウ