スペース区切りのデータの各値をセルに展開する

さあ、今日はいい天気だなあ。
こんな日は、みんなもバシバシ、パソコンはかどってるだろうなあ。

空を見て、そんなことを思っていると、生徒さんがやってきました。

「先生、こんなミッションが上司から・・・」

「どんなミッションなんですか？ふむふむ、エクセルですね。」

「そうです。あるシステムから出力された数字の羅列があるのですが、
その数字をひとつずつセルに入れてほしいって言われたんです。」

「ははあ、スペースで区切られた数字の集合体ですね。」

見せてもらうと、こんな感じでした。




「確認したいのですが、上司の指示は、こういうことでいいのでしょうか？」

僕は、手入力で数字を入力して、生徒さんに見せました。





「そうなんです。先生。同じ行のC列から右方向にひとつずつ数字を入れていくんです。
でも先生・・・」

「どうしましたか？」

「手入力なら、僕でもできるのですよ。もっと簡単にできませんか？
今回のサンプルは、たった4行ですが、実際は、1000行くらいあるんですよ。」

「それは、手入力では大変ですね。分かりました。困ったときのVBAといいます。
プログラミングで、乗り切りましょう。」


そんなわけで、今回もエクセルVBAを駆使して、解決することになりました。

なお、今回の条件を整理しておくと、
スペースで区切られた数字は、いつも6個
スペースは、半角だけど、1個のときも2個のときもある。
今回のサンプルは4件だが、実際は何件あるか分からない。



プログラミングの際に、まず考えることは、何が処理の核心かということです。
それが分かると、そのために必要な情報は何かということも分かります。
そして、処理の結果をどうしたらいいのかを考えればいいのです。

少しだけ専門的な表現を使うと、入力→処理→出力　という一連の流れを押さえることが重要なのです。

今回の場合、核心の処理は、「スペース区切りのデータを各数値に分解する」ということです。

そのために必要な情報は、スペース区切りのデータ1件分です。
1件のデータをちゃんと分解できれば、後は、その作業の繰り返しに過ぎないからです。

この核心の処理は、ひとつのプロシージャとして記述します。
そのほうが効率的だからです。

なお、引数にデータ1件分を指定するのがミソです。

以下のようにコーディングします。

Private Sub SplitData(ByVal s As String, ByVal r As Integer)
　　'引数sを空白で分割して、各数字をセルに代入
　　'引数rは、セルの行番号

　　Dim Data As String
　　'行頭行末のスペースを削除
　　Data = Trim(s)
　　'スペースの数を一つに統一（置換を使う）
　　Data = Replace(Data, " ", " ")
　　Dim tmp() As String
　　'Split関数を使ってスペース区切りで値を配列に格納
　　tmp = Split(Data, " ")

　　Dim i As Integer
　　'配列の要素数だけ繰り返し
　　For i = 0 To UBound(tmp)
　　　　'配列のi番目の値をセルに代入
　　　　Cells(r, i + 3).Value = tmp(i)
　　Next

End Sub


参考までに、画像を載せておきます。





メインは、Split関数の部分です。この関数は、戻り値を配列として返します。
配列の個数は今回は必ず6個ですが、いくつか分からないとしても何とかなります。
それが、今回の　UBound(tmp)　の部分です。
配列の上限をこれで取得できるのです。
（ちなみに、VB.NET以降は、tmp.Length という書き方をします。）

なお、スペースの数を1つに統一するのに、いわゆる置換の関数を使うと楽です。
また、行頭のスペースを、Trim関数で削除しておくと、配列の最初の要素から数字を取り込むことができます。


あとは、このサブルーチンを使って1行ずつ処理を繰り返します。

以下のようにコーディングしてください。

Public Sub Main()
　　'1行目から最終行までのデータを繰り返し処理

　　Dim i As Integer
　　Dim LastRow As Integer
　　LastRow = Range("A65536").End(xlUp).Row

　　For i = 1 To LastRow
　　　　'1行ごとにサブルーチンを呼び出す
　　　　Call SplitData(Range("A" & i).Value, i)
　　Next
End Sub

画像は以下のとおりです。





最終行を取得する方法を覚えておくと便利ですよ。


実行すると、以下のとおりになります。





生徒さんも、うまくいきました。今日もがんばりましょう。


だい