鳥の飛来地をエクセルで集計したい3

みなさんこんにちわ ❘ω・)ﾉ

シリーズでお伝えしています和歌山県鳥類目録を集計する件です。

＜PDFファイルをエクセルにコピペしたら・・＞

＜こうなった＞

これでは集計もへったくれもないので、PDFの表をエクセルワークシートに再現するために、

　番号、科名、種名、生態、備考（飛来地など）

の情報を1行にまとめ無くてはいけません。

そこで、次のような処理をVBAで作ることにしました

１．セルの中身を左から走査して、左端から最初半角スペースまでの文字が数字となるセル（ヘッダーセル)を見つける

２．ヘッダーセルとその次のヘッダーセルのいっこ上のセルまでの情報を連結する

ヘッダーセル(数字＋半角スペースがあるセル)を配列 Arr()に入れたイメージ

３．連結した文字列を新しいシートに順に並べる

（どこからどこまでのセットは目録に記載されている鳥の種類の数だけあるので、それぞれを配列に格納しながら、進めていきます）

前回は上記１．の処理について書きましたので、今回は２.と３．の処理について書こうと思います

ちなみに、処理2, 3はひとまとまりにしてヘッダーセルの数だけループするという処理にしました。

つまり、

　　文字列連結 → 新しいシートへの書き出し 

をヘッダセルごとに行っていきます

でわ２.の処理ですが、上では簡単に書きましたが、さらに3つの処理で完成させます

2-1. 1行にまとめる範囲を特定する

2-2. 特定した範囲内の文字列を順に結合する

2-3. 2-1と2-2の処理を1. で見つけたヘッダーセルの数だけ繰り返して、結果を新しい配列に収める

コードは以下の通り

’処理2-1

1    Dim j As Long: j = 1
2    Dim Arr2() As Variant
3    Dim tempArr As Variant
4     For Each tempArr In Arr 'ヘッダーセルオブジェクトは1. の処理で配列変数 Arr に代入済み

5        ReDim Preserve Arr2(j + 1)
6         Set Arr2(j - 1) = Worksheets("初期データ").Range(Arr(j - 1), Arr(j).Offset(-1, 0))

7         If j = UBound(Arr) Then
8             Exit For
9         End If
10        j = j + 1
11    Next

 

'処理2-2

1   Dim Arr3()
2   Dim k As Long

3    Dim t As Long

4    For k = 1 To UBound(Arr2) - 1
5      Dim tempStr As String: tempStr = ""
6        ReDim Preserve Arr3(k)
7       For t = 1 To Arr2(k - 1).Cells.Count
8            tempStr = tempStr & Arr2(k - 1).Cells(t).Value

9       Next t

'処理2-3

10     Arr3(k) = tempStr

 

'処理３
11     Worksheets("修正データ").Range("A1").Offset(k, 0) = Arr3(k)
13   Next k

 まず、処理2-1部分。

処理のポイントになるのは2、５、6行目です。

2, 5行目：ループを回しながらArr2の配列要素数を増やしていくため、2行目で動的配列を宣言したのちに、5行目で要素数を指定して宣言しなおしています。ただし、宣言により配列の中身がリセットされないように Preserve キーワード付きで宣言します。

６行目：１行にまとめる範囲をRange指定して、配列 Arr2 に格納していきます(Rangeオブジェクトを配列化するため、Setステートメント使用。配列ArrはRangeオブジェクトとして作っていますので、Arr().Offsetといった表現になります。)

次に処理2-2部分。

ポイントは7-9行目です。

7-9行目：Arr2配列各要素のRange範囲内に含まれるセル数をカウンタにしたループを設定

8行目：Arr2配列各要素のRange範囲内に含まれるセルの中身を次々に結合していきます

次に処理2-3部分。

10行目：出来上がった文字列を動的に宣言したArr3に格納(Preserveキーワード付き)

処理３(さりげなく紛れ込んでいますね）

11行目：結合して出来上がった文字列をワークシート"修正データ" にA1セルを起点として書き込んでいきます。

実行結果は以下の通り。

番号に続いて種名、生態などの情報が1種1行にまとまりました｡

ココまで来てやっとスタートラインに来た感じです。

まだまだすべての情報が1行に詰め込まれているので、これを項目ごとに分けて、構造化された表の完成を目指します。

でわまた～￣▽￣)ﾉｼ