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

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

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

No.1 ベストアンサー 回答者： maron--5 回答日時： 2008/10/02 17:33

◆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)))
★下にコピー

この回答へのお礼

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

お礼日時：2008/10/07 12:34

No.3 回答者： imogasi 回答日時： 2008/10/02 22:23

#2です。

作業列を使う方法を挙げておきます。
これでも複雑ですが、作業列を使わない方法に比べて、どうでしょうか。
今の行番号を元に、持って来る元データ範囲（下記ではＡ１：Ｅ６）の中で、何行数、何列数かを計算している方法です。
例データ（Ａ列は店、Ｂ－Ｅ列は品物、品物数限度４個の例）
Ａ列　　Ｂ列　　Ｃ列　　Ｄ列　　Ｅ列
acx
defgg
hij
kl
mnopq
rst
ーーー
Ｆ列　Ｆ１は０、Ｆ２に式　=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)
それぞれ下方向に式を品物数行だけ複写
結果
Ｉ列　　Ｊ列
ac
ax
de
df
dg
dg
hi
hj
kl
mn
mo
mp
mq
rs
rt

No.2 回答者： imogasi 回答日時： 2008/10/02 20:37

質問者には無縁かもしれないが、こういう手の問題（表の組み換え）はＶＢＡでやると考えやすい。

単純な規則性があるから。
（式でやると既出ご回答のように初心者には理解も難しい式になる。）
参考に。
ーー
例データ
質問の通り
コード
標準モジュールに
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
ーー
結果
Ｓｈｅｅｔ２
Ａ列　　Ｂ列
Ａスーパリンゴ
Ａスーパバナナ
Ａスーパミカン
Ｂコンビニナシ
Ｂコンビニモモ
Ｃ百貨店クリ
Ｃ百貨店ブドウ