★★★★クロス集計表や帳票をリスト形式に変換するのに、ワークシート関数とVBAプログラムとどちらがいいのか? ~ 答えのひとつとしては、「3枚以下の表ならワークシート関数」が良い気がしますし、同じ表が3枚以上あるならセルに関数式を埋めることすら面倒くさいので、「VBAプログラム」を検討してもOKだと思います。
  
★ サンプルファイルのダウンロード

こちら ← からダウンロードできます。
  
  

★ はじめに

「クロス集計表や帳票をリスト形式の表に変換したい」という要望があることが少なくないと思います。(データクレンジング?と言ったりもするのかしら?)

理由は、
(特にクロス集計表を変換したい場合は)

・リスト形式の表に変換すると、モトのクロス集計表の集計とは別に、

・数倍~十数倍、場合によっては数十倍の、

・「異なる切り口での」クロス集計表が「さらに作成できる」から。

です。
  

で、その場合、方法は大別して2つあると思います。

(a)ワークシート関数を使う方法
(b)VBAプログラムを使う方法

※補足
(a)と(b)のほかには、「パワークエリ」を使う方法もあると思います。
が、「かなりイレギュラーなレイアウト」には対応しづらいのではないか?とも思うので、今回は説明していません。というか、僕も簡単な例しか使ったことないので、よくわかっていません。すみません。
が、今回ココで紹介しているサンプル程度のレイアウトの表なら、もしかしたら「パワークエリ」が一番早く済むかもしれません。
なので、「パワークエリ」も絶対に使えるほうがいいには決まっています。
ただ、もちろん、変換したい表の数が、1~5枚くらいだけなら・・・ということになりますので、例えば毎回50枚以上処理しないといけない、等々の状況なら、VBAのほうが早いと思います。
VBAも意外と少ないコード量で済むこともありますので。
なお、本当にシンプルなクロス集計表(列名や行名が1段づつしかないとか)なら、ピボットテーブルでもクロス集計表からリスト形式の表に変換できます。
シンプルなものは是非ピボットも試していただきたいと思います。

  

(a)と(b)、どちらも有効な方法だと思いますが、それぞれに良さがあると思います。

基本、
(a)の「ワークシート関数を使う方法」では、
処理するシートの数が少なければ少ないほど、有効だと思います。

それに対して、
(b)のVBAプログラムを使う方法は
処理するシートの数が多ければ多いほど、有効・・・、だと思います。

例えば、処理したいシートが50シートある場合を考えてみます。

その場合、
「それをリスト表に1枚ずつ変換し、さらには全部を縦に結合(マージ)したい」というような場合、シート1枚1枚にワークシート関数を含んだ数式を、全部で50枚、いちいちセルに埋め込んでいく・・・」
というのはかなり大変な作業で、数式の設定ミスも発生しやすいです。

それを50枚縦に結合するという話になったら、さらにミスが発生しかねません。

さらには、「手作業だと」50枚結合したものが正しく結合されているかのチェックも必要になってしまいます。(1行分だけ抜けてた・・・とかのミスがあると非常に見つけづらいです。)

VBAなら、全自動になります。
また、チェックするのも、これもVBAで行えるので、ラクに行えると思います。
  

逆に、処理したいシートが1枚しかないのに、わざわざVBAにする必要もない、というケースも多々あります。

ただ、VBAプログラムが理解できるのなら、サンプルをいったん組んでおけば、「意外にも」、「あとは少し部分修正するだけ・部分的に組み替えるだけ」なことも少なくないので、「意外にも困難」ではなく、「処理したいシートが1枚」のときでも、もちろんVBAプログラムは有効です。

が、サンプルプログラムが無いときや、VBAプログラミンング自体がまだ未経験の場合は、やはりワークシート関数が生きてきます。

以上のように、「どちらかひとつだけで全部やろうとする」と、ロス・ムリ・ムダ・ムラ、を生んでしまうので、本当は両方できたほうがいいです。

本記事では、そのような場合に、「VBAプログラミングでの処理のひな型」として使って頂けるサンプルをご紹介するとともに、「ワークシート関数」での処理のサンプルもさらっとご紹介だけしたいと思います。(僕は逆にワークシート関数が苦手です。VBAのループも苦手だけど・・・)

  
  

★ 変形前のクロス集計表のレイアウト

サンプルのExcelファイルの中には大きくは4つシートがありますが、最初の3つは以下のようなパターンの変換になります。

最後の1つのシートは以下のような変換パターンになります。

3つのシートの方の、VBAプログラムで変換したものを、少しソースのレイアウトを変えたものです。
それによってプログラㇺがどのくらい変化するかを見てみてください。
意外と変化しないことが分かると思います。(もちろんそんなケースばかりでは無いとは思いますが・・・。)
  
  

★ 「関数で01MOD関数などでの方法」シートについて
【欲しいデータベースを作る】クロス集計表をリスト形式の表に変換する方法
に記された方法をそのまま試しています。
(ちょっとセル位置や、変換先の表示形式などが異なりますが)

  

★ 「関数で02_INDEX関数を使う方法」シートについて

「Excel 多量データ整形テクニック」の「階層並列表」の項(P108あたり)のやり方でやっています。

  

★ 「VBAで01テスト」シートについて

以降のようなコードで処理しています。

このサンプルだけを見ると、これだけのコード量でいいなら、ワークシート関数でやるのとどっちがいいのだろう?と考えたくなります。

でも、「ワークシート関数を使うほうがVBAよりも便利なレイアウト」のケースも絶対にあると思うので、VBAの妄信は危険だとも思います。

  

★ 「VBAで02test」シートについて

以降のようなコードで処理しています。
「GetMrgCelAddr_Fst02()」関数と、「LTrimWrd」関数は前項のコードとまったく同じです。
前項のコードとの行数だけでも比べてみてください。
思ったよりも増えてないと感じるのではないかと思います。

  

★ 「test002_DellComment()」の、コメントつきのコード

以下、①と②も3回繰り返します。
(つまり、りんご1行分につき、果物とりんごを3回繰り返します。)

※結局、下図のように、「1行単位で」、矢印などを書いて動きを視覚化してみると、ループのプログラムが作りやすいかもしれません。

  
  

  
★ 「test004_DellComment()」の、コメントつきのコード

こちらも、以下、①と②も3回繰り返します。
(つまり、りんご1行分につき、果物とりんごを3回繰り返します。)

※こちらも、下図のように、「1行単位で」、矢印などを書いて動きを視覚化してみると、ループのプログラムが作りやすいかもしれません。