【スキマ学習】ガチ初心者と一緒に、Excelでnoteのタグ解析ができるツールを作ってみよう《7》

長らくお待たせいたしました。ツールの開発に戻りたいと思います。

前回までで、エクセルのシートから別のシートにそっくりそのままデータをコピーすることができました。

ここからは、サイトから持ってきた生のデータを加工する段階に入りたいと思います。宜しくお願い致します。

・会社にいてもちょっとしたスキマ時間に実践できるよう、一回の分量をなるべく少なくしてお送りします。
・特別な準備は要りません。オフィスのPCにだいたい入ってるエクセルとGoogleクロームだけを使って作ります。記事も無料です。

前回の記事はこちら

今回の内容はこんな感じです↓

えっと、今どこだっけ？

《4》の記事で、ツールのおおまかな全体像をデザインしました。

最終的に私たちが分析したいデータはこんな感じのものでした。

・タイトル
・書き出し
・記事のURL
・スキ数
・ハッシュタグ
・画像
・著者名
・投稿日時

次に、このデータを得るための簡単なフローを想定しました。

①noteからデータをコピーしてくる。

②Excelに貼り付ける。

③並び替えて表にする。

このうち、前回までは③の中身に注目して、以下のような仕組みを考えました。《5》と《6》の内容は一番最後の行をコード化するというものです。

Sub 並び替えて表にする()

  必要な情報のみを抽出する
  どこまでが1記事なのか把握する
  データを横一列に並べ替える
  表に書き写す ←《5》《6》ではここをやりました！

End Sub

「うげ、まだこんなところなの」と、そう気を落とさないでください。

確かにこれまでの六回では全体の一部にしか触れてきませんでしたが、とっても重要な基礎を学んでいたのですから当然のことです。

ここから一段ずつ積み上げていきましょう。

今回はひとつ上の行、「データを横一列に並べ替える」の部分を扱います。

なにをつくるのか確認する

noteのWebサイトからコピーしてきたデータを、エクセルに貼り付けた場合、たしかこんな感じの構造になっていたと思います。

複数行ある項目はちょっと面倒くさそうですね。

今回は一行分のデータとみなして、「コピー元」のシートに次のようなデータを用意しておきましょう。

これを、欲しいデータの順番に並べ替えて、「貼り付け先」のシートに下のような形で貼り付けたいわけです。

今回不要な列を整理するとこんな感じになるでしょうか。

念のためテキストでも用意しておきます。

筆者名
投稿日時
タイトル
書き出し
もっと見る
タグ
スキ
コメントする

これを、

タイトル 書き出し もっと見る スキ数 タグ 著者名 投稿日時

こうしたいと。

そうなると、行う操作は大きく二つありますね。

①データを並べ替える

②縦向きのデータを横向きにする

今回はこれを両方やってみたいと思います。

順番を並べ替える

結論からお話しすると、これにはいろんな方法が存在します。

わかりやすい簡単な方法も確かにあるのですが、
私は応用の幅が広いやり方をご紹介しようと思います。

便宜上、データの各項目に番号を振っておきますね。

①～⑧の項目があり、8番目の「コメントする」は要らないので削除することにしましょう。

上の図のように並べ替えて貼り付けるために、前回までのコピペのコードを少しアレンジしてみたいと思います。

今回データをやりとりするのは「A1:A8」という名前のセル範囲なので、まずはその部分を書き直しておきましょう。

Sub copyData()

 Dim ws1 As Worksheet        
 Set ws1 = Sheets("コピー元")
 Dim rg1 As Range
 Set rg1 = ws1.Range("A1:A8") '←セル範囲を書き換え'

 Dim data As Variant
 data = rg1.Value
 
 Dim ws2 As Worksheet
 Set ws2 = Sheets("貼り付け先")
 Dim rg2 As Range
 Set rg2 = ws2.Range("A1:A8") '←セル範囲を書き換え'
 
 rg2.Value = data
 
End Sub

このまま実行していただくと、以前と同様、「コピー元」のシートから「貼り付け先」に8行のデータがコピペされると思います。

貼り付けるデータを並べ替えるということは、最後の行で「貼り付け先」のセル「A1:A8」に代入されている変数dataの順番を入れ替えるということです。

配列

さて、この変数dataが何者なのかを知らないと、並べ替えるもへったくれもありませんね。えっと確か、Variant型の変数だったと思います。

ちょっとローカルウィンドウを使って調べてみましょう。

すぐ下の適当な行にブレークポイントを設置して、

ローカルウィンドウを開いて実行します。 ※もう開いている人はそのまま実行

実行が停止して、ローカルウィンドウに変数が羅列されました。

変数dataの中身を見てみると、ちゃんと「筆者名」というデータが格納されているのが分かります。

型の列には、何やら「Variant(数字 to 数字)」と呪文のようなものが書いてあります。変数dataの正体はここから読み取ることができそうです。

セル範囲の値をまるっと取り込んできた変数dataは、「配列」という形のデータ構造をしています。

セル範囲から変数dataに取り込まれた値は、それぞれ行番号と列番号を与えられて配列の中に登録されています。

「変数dataの4行目1列目は”書き出し”」と説明できますね。

配列は、このように番号でデータを管理することができるデータの集合体です。

いろんなデータに対して、ちまちま変数を宣言して格納しておくのは面倒なので、配列を使って管理すると便利です。

配列では、データのインデックス番号を指定することで、中身のデータを呼び出すことができます。

例えば「書き出し」のデータを呼び出したい場合は、「data(4,1)」と座標のように指定することができます。ちょっと試してみましょう。

Debug.Printを使って、イミディエイトウィンドウに「書き出し」のデータを呼び出してみます。

先ほど設置したブレークポイントより上に、

Debug.Print data(4,1)

このコードを挿入します。デバッグのときの要領です。

イミディエイトウィンドウを開いて、コードを実行してください。
※すでに開いている人はそのまま実行。

上の画像でいうところの左側、イミディエイトウィンドウに「書き出し」と表示されていれば成功です。

配列は大きなデータを扱う際に大変便利です。構造が少し複雑ですが、是非活用していただきたいと思います。

変数dataという配列の中に、八つのデータが番号付きで保管されている状態はちょうどこんなかんじです。

それぞれ緑枠の中にある赤文字のコードで中身を呼び出すことができます。

エクセルのシートを取り込んでいるので、配列の軸は行と列の二つしかありませんが、コードの中では3軸以上の立体的な配列を作成することも可能です。

今回のようにデータを呼び出すための番号が2軸の配列のことを「2次元配列」と呼びます。

配列の値を呼び出す

書き方：

2次元配列 x行目y列目のデータ = 配列の変数名(x, y)

配列をつくる

さて、本題に戻りましょう。

冒頭のコード最終行では、セル範囲rg2の値に配列dataを代入していました。

ということは、この配列の順番を変えてしまえばよいわけです。

既存の配列dataをこねくり回して加工しても良いのですが、
新しく作ってしまった方が手っ取り早いので、やってみましょう。

Sub copyData()

 Dim ws1 As Worksheet        
 Set ws1 = Sheets("コピー元")
 Dim rg1 As Range
 Set rg1 = ws1.Range("A1:A8")

 Dim data As Variant
 data = rg1.Value
 
 Dim ws2 As Worksheet
 Set ws2 = Sheets("貼り付け先")
 Dim rg2 As Range
 Set rg2 = ws2.Range("A1:A8") 
 
 rg2.Value = data '←ここで代入する、新しい配列をつくる'
 
End Sub

ここからは便宜上、順番を入れ替える前の配列を「data1」、入れ替え後の配列を「data2」と呼ぶことにしましょう。

コード内の「data」をすべて「data1」に書き換えておいてください。

さっきまでdataと呼ばれていたこの配列data1は、シート内の複数のセルの値をまとめて代入することで作成されていました。

data1 = rg1.Value '「コピー元」のセル「A1:A8」の値を代入' 

これはExcelが自動で配列にしてくれているので楽ですが、何もないところから新しく配列を作る方法はもう少し複雑です。

新しく2次元配列を作るには、

・名前

・行列の呼び出し番号

のふたつを設定する必要があります。

今回作る配列data2は、配列data1の八つのデータから、「コメントする」を抜いた七つを格納する2次元配列です。

したがって、

名前： data2

呼び出し番号：行（1～7）、列（1～1）

ということになります。コードにするとこんな感じです。

   Dim data2() As Variant        'data2という変数をVariant型の配列として宣言'
   ReDim data2(1 To 8, 1 To 1)   'data2の1軸目は1～8、2軸目は1～1の番号で管理すると再宣言'

配列も変数の一種なので、必ずDimで宣言するのを忘れないでください。

変数名のうしろに()を付けると、配列を宣言することになります。

Redimは、re-（再び）dimするという見た目の通り、変数を宣言し直すという意味です。ここで配列の大きさを決めてあげています。

2次元配列を作る

書き方：

Dim 配列の変数名() As Variant
Redim 変数名(x to x, y to y)  'xとyは要素の数によって変える'

配列に値を代入する

実際にコードの中に書いてみましょう。

Sub copyData()

 Dim ws1 As Worksheet        
 Set ws1 = Sheets("コピー元")
 Dim rg1 As Range
 Set rg1 = ws1.Range("A1:A8")

 Dim data1 As Variant
 data1 = rg1.Value
 
 Dim data2() As Variant       '新しい配列を作る'
 ReDim data2(1 To 7, 1 To 1) 

 
 Dim ws2 As Worksheet
 Set ws2 = Sheets("貼り付け先")
 Dim rg2 As Range
 Set rg2 = ws2.Range("A1:A8") 
 
 rg2.Value = data2            'data2に書き替え'
 
End Sub

このdata2の中に、data1の値をひとつひとつ代入していきます。

配列に値を代入する際は、格納しておきたい座標を呼び出した状態で、代入したい値とイコールで結びます。

配列に値を代入する

書き方：

配列の変数名(x, y) = 値

今回は配列data1の中身をそのまま代入していくので、次のように書くことができますね。

data2(x, y) = data1(x, y) 'data2の値にdata1の値を代入する'

並べ替えｊの対応関係は先ほどの図の通りです。

※式は左右逆になっているので気を付けてください。

これをコードにしたものがこちらです。

Sub copyData()

 Dim ws1 As Worksheet        
 Set ws1 = Sheets("コピー元")
 Dim rg1 As Range
 Set rg1 = ws1.Range("A1:A8")

 Dim data1 As Variant
 data1 = rg1.Value
 
 Dim data2() As Variant
 ReDim data2(1 To 7, 1 To 1) 


 data2(1, 1) = data1(3, 1) 'data2の値にdata1の値を代入'
 data2(2, 1) = data1(4, 1)  
 data2(3, 1) = data1(5, 1)
 data2(4, 1) = data1(7, 1)
 data2(5, 1) = data1(6, 1)
 data2(6, 1) = data1(1, 1)
 data2(7, 1) = data1(2, 1)
'data2(8, 1)は使わないのでスルー'
 
 
 Dim ws2 As Worksheet
 Set ws2 = Sheets("貼り付け先")
 Dim rg2 As Range
 Set rg2 = ws2.Range("A1:A8") 
 
 rg2.Value = data2
 
End Sub

では、ブレークポイントが残っている人は外して、実行してみましょう。

順番が並べ替えられて貼り付けできていれば成功です。

縦横を入れ替える

さて、ここまで来ればゴールは目前です。

①データを並べ替える

②縦向きのデータを横向きにする

ここまででもうお気づきかと思いますが、rg2の範囲と配列data2の形を少しいじってあげるだけで、②は簡単に実行できます。

横向きに並べ替えるということで、rg2の範囲は下の画像の通り「A1:G1」の七つのセルです。

Set rg2 = ws2.Range("A1:G1")

この範囲にすっぽりおさまる形の配列data2を定義します。行数1、列数7の配列ですから、こんな感じです。

   Dim data2() As Variant
   ReDim data2(1 To 1, 1 To 7)

data2の行と列が逆転するので、呼び出す際の番号も逆になります。

   data2(1, 1) = data1(3, 1)
   data2(1, 2) = data1(4, 1)
   data2(1, 3) = data1(5, 1)
   data2(1, 4) = data1(7, 1)
   data2(1, 5) = data1(6, 1)
   data2(1, 6) = data1(1, 1)
   data2(1, 7) = data1(2, 1)

この通りにコードを書き直したものがこちらです。

Sub copyData()

 Dim ws1 As Worksheet        
 Set ws1 = Sheets("コピー元")
 Dim rg1 As Range
 Set rg1 = ws1.Range("A1:A8")

 Dim data1 As Variant
 data1 = rg1.Value
 
 Dim data2() As Variant
 ReDim data2(1 To 1, 1 To 7) '横向きに再宣言'


 data2(1, 1) = data1(3, 1) 'data2の番号を縦横逆転させる'
 data2(1, 2) = data1(4, 1)
 data2(1, 3) = data1(5, 1)
 data2(1, 4) = data1(7, 1)
 data2(1, 5) = data1(6, 1)
 data2(1, 6) = data1(1, 1)
 data2(1, 7) = data1(2, 1)
 
 
 Dim ws2 As Worksheet
 Set ws2 = Sheets("貼り付け先")
 Dim rg2 As Range
 Set rg2 = ws2.Range("A1:G1") '貼り付け範囲も横向きにする'
 
 rg2.Value = data2
 
End Sub

実行してみましょう。

横一列にデータが入っていれば成功です。

まとめ

どうでしょうか。なんだか一気にプログラムっぽくなってきましたね！

人間の手ではちょっとめんどくさいことが、プログラムによって一瞬で完了してしまいます。これは本当にいつ見ても爽快です。

念のため、完成コードと和訳を載せておきます。

Sub copyData()

 Dim ws1 As Worksheet        
 Set ws1 = Sheets("コピー元")
 Dim rg1 As Range
 Set rg1 = ws1.Range("A1:A8")

 Dim data1 As Variant
 data1 = rg1.Value
 
 Dim data2() As Variant
 ReDim data2(1 To 1, 1 To 7)

 data2(1, 1) = data1(3, 1)
 data2(1, 2) = data1(4, 1)
 data2(1, 3) = data1(5, 1)
 data2(1, 4) = data1(7, 1)
 data2(1, 5) = data1(6, 1)
 data2(1, 6) = data1(1, 1)
 data2(1, 7) = data1(2, 1)
 
 Dim ws2 As Worksheet
 Set ws2 = Sheets("貼り付け先")
 Dim rg2 As Range
 Set rg2 = ws2.Range("A1:G1")
 
 rg2.Value = data2
 
End Sub

「copyDataという名前の手続きは以下の通りである。」

  「ws1という変数をワークシートの形で宣言」
  「ws1は”コピー元”という名前のシート」
  「rg1という変数をセル範囲の形で宣言」
  「rg1はws1の”A1:H1”という名前の範囲」

  「data1という変数を不定形で宣言」
  「data1はrg1の値すべてを取り込んだ配列」

  「data2()という変数を不定形で宣言」
  「data2の一次元目は1から1、2次元目は1から7で管理する」

  「data2の(1, 1)の値に、data1の(3, 1)の値を代入」
  「data2の(1, 2)の値に、data1の(4, 1)の値を代入」
  「data2の(1, 3)の値に、data1の(5, 1)の値を代入」
  「data2の(1, 4)の値に、data1の(7, 1)の値を代入」
  「data2の(1, 5)の値に、data1の(6, 1)の値を代入」
  「data2の(1, 6)の値に、data1の(1, 1)の値を代入」
  「data2の(1, 7)の値に、data1の(2, 1)の値を代入」

  「ws2という変数をワークシートの形で宣言」
  「ws2は”貼り付け先”という名前のシート」
  「rg2という変数をセル範囲の形で宣言」
  「rg2はws2の”A1:H1”という名前の範囲」

  「rg2の値に配列data2の値を代入」

「以上」

次回は更に、これを複数の記事に対して繰り返し行う処理に挑戦してみようと思います。

いよいよ人間の手よりもプログラムの方が素早い領域に入ってきましたね。楽しいのはここからです。一歩一歩、確実に進んでいきましょう。

今回もお疲れ様でした。

ご読了ありがとうございました。
こんなやまびこですが、人生の時間をほんのちょっとだけ分けてあげてもいいよという方は、フォローを頂けると大変喜びます。

【だいたい平日18時頃に更新中】

Twitter：
https://twitter.com/echoyamabiko
@echoyamabiko

note：
https://note.com/echo_yamabiko

はてなブログ：
https://echo-yamabiko.hatenablog.com/

※内容は基本的に同じなので、一番身近なアカウントでのフォローをお勧め致します。

【匿名での質問や感想はこちらが便利です】

やまびこへの質問箱：
https://peing.net/ja/echo_yamabiko?event=0