LINEの会話ログをExcelVBAで集計する

この物語は、入社して一年目の新人社員が上司の指南のもと、業務を自動化していく様を記したものです。物語はフィクションですが、自動化の手法は実際にパソコンやスマートフォンで実践できるものですので、物語と自動化作業の両方を楽しめます。(前回のストーリーはこちら)

＊＊＊

・・・なんと太田先輩の連絡先をゲットできた！

しかも、なぜか太田先輩の方からLINEのID交換を申し出てくれたのだ。もしかして、これは脈があるのでは？と思ってしまう。しかも、今日も朝早く来れば「秘密の特訓」をしてくれるとのこと。高鳴る胸を押さえて家を出た。

しかし、失敗もあった。せっかく太田先輩の連絡先を聞いたのに、結局何のメッセージも送っていない。と言うのも、昨夜も今朝も通勤電車の中でも「一通目に何を送ろうか」と、悶々と考えていたのだ。検索エンジンで調べると、気の利いた文例がたくさん見つかったのだが、それを送る勇気がなかった。

そうこうしている内に、会社に着いてしまった。モヤモヤしながら、会社の扉をくぐると、どうやら、太田先輩は今日も先に出社しているようだ。なんと、彼女はスマートフォンを見ながら甘そうなパンをリスのようにかじっていた。その何とカワイイこと！

それで、僕は深呼吸して、背筋を伸ばし、かっこつけて経理部の部屋に入った。

「おはようございます！」と僕が元気に挨拶すると、ニッコリと満面の笑みで太田先輩が答えた。「田中君、おはよう！」そして、急にガバッと立ち上がって言った。

「それでは、早速プログラミングを始めましょう！今日は、LINEの会話ログをExcelVBAで自動処理しよう。」

太田先輩、かっこつけて言った割には、片手に甘いパンの残りを持っているし、多少突っ込みどころがあるのだが、そこは見ないふりをして「待ってました！お願いします。」と答えた。

そして、それを満足そうに聞いた太田先輩が質問してきた。

「田中君、昨日、LINEで連絡先交換したけど、たくさんメッセージ来たでしょ？」

「え？何のことですか？」

僕は昨日から一度も鳴っていないスマートフォンを取り出した。やはり、太田先輩からのメッセージは来ていない。

「あれ？会社のグループに入れたはずなんだけど・・・」

慌てて先輩もスマートフォンを取り出す。

「あれっ、追加できてなかったみたい。ごめんね。実は、昨日、会社のグループラインで懇親会のお店の希望を募ったのよ。」

なんと、昨日、LINE交換したのは、会社のグループラインに入れるのが目的だったとのこと。がっかり。そんな僕の様子に構わず、太田先輩は続ける。

「それで、今日は、このLINEの会話ログを利用して、プログラミングで懇親会のお店の希望を自動で集計しようと思うの。」

僕に落ち込んでいる暇はない。先輩の提案に驚いて尋ねた。「そんなことできるんですか？」

太田先輩は、満面の笑みで頷いた。そして、いつも通りパソコンを開いた。そして、LINEのPC版を起動した。

• 

  LINEで懇親会のお店の希望を募ったところ

「あ、LINEってパソコンでも使えるんですね。」と僕が他尋ねると、太田先輩は、早口で解説を始めた。

「そうなの。しかも、LINEの会話ログをテキストファイルとして保存できるの。画面右上のメニューから『トークを保存』をクリックすると、会話ログをファイルに保存できるよ。そして、この会話ログをプログラムで読み込んで利用するの。」

• 

  LINEの会話ログをプログラミングで？(作画 竹中貴一/created by コミPo!)

その後、太田先輩はExcelを起動した。そして、保存した会話ログをテキストエディタで開いて、テキスト全部をコピーしてExcelに貼り付けた。

• 

  会話ログをExcelに貼り付けたところ

「今、ExcelのSheet1にLINEの会話ログを貼り付けたの。今、新規シートを作ったんだけど、プログラムで集計して、結果はSheet2に出るようにするわね。」

太田先輩は、そう言って、[Alt]+[F11]キーを押してVBAエディタを開くと、プログラムを書きだした。カタカタと小気味よいキーボードの音がする。

「できた！」太田先輩はそう言って、[F5]キーを押してプログラムを実行した。すると、あっという間に、LINEログを元にしたアンケートの集計結果が表示された。

• 

  集計結果が表示された

「やっぱり、焼鳥屋か。」

実行結果が正しく表示された直後は、ニコニコしていた太田先輩だったが、実行結果を確認した後はちょっと嫌そうだった。

「先輩は、本当はどこが良かったんですか？」

「焼き鳥屋も好きだけど、私はパスタ屋が良かったな。」

これは絶好のチャンスだ。僕は勇気を振り絞って言った。

「それなら今度、パスタ屋に一緒に行きませんか？プログラムを教えてもらっているお礼に奢らせてください！」

（続く）

実際のプログラム

さて、太田先輩が作った実際のプログラムを紹介します。今回は、LINEの会話ログを元にして、アンケートを集計します。

実際のサンプルコードをこちらからダウンロードできます。

LINEの会話ログを保存する方法は、本文中にもやり方が乗っていますが、LINEのメニューから「トークを保存」をクリックすると、テキスト形式でログを保存できます。

• 

  LINEの会話ログを保存する方法

続いて、Excelを起動し「空白のブック」を作成します。テキストエディタで保存したLINEの会話ログを開いて、すべてのテキストをSheet1をに貼り付けましょう。そして、画面下にある[+]を押して新規シート(Sheet2)を作成します。このSheet2にプログラムの実行結果を表示します。

これで準備完了です。[Alt]+[F11]キーを押して、VBAエディタを起動しましょう。そして、以下のプログラムを記述します。

Sub CountShopName()
    Dim Names As Variant ' お店の名前
    Dim Values As Variant ' お店ごとの票
    ' お店の名前を指定 --- (*1)
    Names = Split("焼肉屋:焼鳥屋:天ぷら屋:パスタ屋", ":")
    ' 票を0に設定 --- (*2)
    Values = Split("0:0:0:0", ":")
    ' セルの最下行を得る --- (*3)
    LastRaw = Cells(Rows.Count, 1).End(xlUp).Row
    ' 順次セルを繰り返し読む --- (*4)
    For i = 1 To LastRaw
        ' セルの値を得る --- (*5)
        Data = Sheet1.Cells(i, 1)
        ' セルがお店の名前を含んでいればカウント --- (*6)
        For j = 0 To UBound(Names)
            If InStr(Data, Names(j)) > 0 Then
                Values(j) = Values(j) + 1
            End If
        Next
        Debug.Print (Data)
    Next
    ' 結果を表示 --- (*7)
    For i = 0 To UBound(Names)
        Sheet2.Cells(i + 1, 1) = Names(i)
        Sheet2.Cells(i + 1, 2) = Values(i)
    Next
End Sub

プログラムを貼り付けたら、[F5]キーを押すか、エディタ上部にある実行ボタンをクリックしましょう。すると、実行結果が、ExcelのSheet2に表示されます。

プログラムを確認してみましょう。(＊1)の部分では、変数Namesを初期化します。Split関数を使うと、文字列を任意の区切り記号で区切って配列に分割できます。ここではお店の名前をコロン「:」で区切って記述し、Split関数で配列に分割します。つまり、変数Namesには配列形式でお店の名前が代入されます。続いて、(＊2)の部分では、変数Valuesを初期化します。(＊1)と同様の手法で0で初期化します。

続いて(＊3)の部分では、シートの有効な最下行を取得します。ちょっと分かりにくいのですが、このように記述することで最下行が得られます。そして、(＊4)の部分では、シートの一番上から、一番下までを順次読んでいきます。そして、(＊5)の部分でセルの値を取り出して、(＊6)でお店の名前があるかを調べます。InStr関数を使うと文字列検索ができます。お店の名前があれば、変数Valuesの値を加算します。最後に、(＊7)の部分で、Sheet2に結果を表示します。

プログラム改良のポイント

また、自分用のプログラムに書き換える際のポイントを紹介します。と言うのも、懇親会のお店の名前が、いつも「焼肉屋」や「焼鳥屋」であるはずがありません。

プログラムの(＊1)の部分にある「焼肉屋:焼鳥屋:天ぷら屋:パスタ屋」の部分を任意の名前のお店に書き換えるなら異なるアンケートの集計にも利用できます。お店の名前をコロン「:」で区切って並べるだけです。その際、(＊2)の変数Valuesの初期値も同様に書き換える必要があります。

例えば、A、B、Cの三択であれば以下のように書き換えます。

    ' お店の名前を指定 --- (*1)
    Names = Split("A:B:C", ":")
    ' 票を0に設定 --- (*2)
    Values = Split("0:0:0", ":")

まとめ

実は、LINEには投票を募る投票機能があるので、最初からそれを使えば集計の手間が省けます。最初からある機能を使えば、プログラムを作る必要もないのです。ただし、LINEの会話ログを活用したい場面は多いと思いますので、今回のプログラムが参考になりでしょう。

自由型プログラマー。くじらはんどにて、プログラミングの楽しさを伝える活動をしている。代表作に、日本語プログラミング言語「なでしこ」 、テキスト音楽「サクラ」など。2001年オンラインソフト大賞入賞、2004年度未踏ユース スーパークリエータ認定、2010年 OSS貢献者章受賞。技術書も多く執筆している。