http://q.hatena.ne.jp/1239344679

特に「関数で」という制限がなかったのでマクロで作成してみてはどうでしょうか。

関数は、今回のように開始位置を変更したいというときにオフセットの調整が煩わしい

ですが、マクロでしたらセルにデータとして展開できますので、作成後に行の挿入等で

位置調整が可能です。

Sub makeList()
    Dim dstWB As Workbook
    Dim dstWS As Worksheet

    Set dstWB = Workbooks.Add()
    Set dstWS = dstWB.Worksheets(1)
    
    Dim lastRow As Long
    lastRow = Range("A" & Rows.Count).End(xlUp).Row

    Dim i As Long, j As Long, k As Long
    Dim data As String
    For i = 1 To lastRow
        For j = 0 To 4
            If Cells(i, 2 + j).Value <> "" Then
                data = Cells(i, "A").Value & "-" & Cells(i, 2 + j).Value
            Else
                data = Cells(i, "A").Value
            End If
            For k = 0 To 4
                dstWS.Cells((i - 1) * 25 + j * 5 + k + 1, "A").Value = data
                dstWS.Cells((i - 1) * 25 + j * 5 + k + 1, "B").Value = Cells(i, 7 + j).Value
            Next
        Next
    Next
End Sub

データのあるシートのタブを右クリックし、「コードの表示」で表示されるウィンドウに上記コードを貼り、

EXCEL に戻った後、ALT+F8で、Sheet1!makeList のように表示されているものを実行するば新規にデータが

作成されます。

ブックにマクロを残したくない場合、上記の表示の手順で貼り付けたものを削除すれば元の状態に戻ります。

今回はそれを懸念して、新規ファイルで作成するようにしましたが（作成されたファイルはマクロを含まない）

同じブックの2番目のシートにデータを作成したい場合、コード中の

    Set dstWB = Workbooks.Add()
    Set dstWS = dstWB.Worksheets(1)

の代わりに

    Set dstWS = ThisWorkbook.Worksheets(2)

とすればそうなります。

関数での解法をお望みでしたら、読み飛ばしてください（ポイントは不要です）。

http://excelvba.pc-users.net/