Excelの「クエリ」を活用してWebアクセスログを読み込む　【Excel活用発展編】

クエリでどのようなことができるの？

Excelのクエリは外部にある各種データに問い合わせを行い、必要なデータをExcelにインポート (取り込む) 機能です。Excelに取り込んだデータは元のデータが更新された場合も、手動または自動で最新のデータに更新できます。いったん取り込んだデータはExcelのデータになりますので、Excelの機能を使って自由に加工・計算・集計できます。

取り込むデータとしては、同じワークシートのデータ、同じPC上のデータ、サーバーにあるデータ、さらにはWeb上のデータなどさまざまです。Excelに取り込むことができるデータは標準で次の種類がサポートされています。

• Access、SQLサーバー、dBASE、FoxPro、Oracle、Paradoxなどのデータベース
• Excelのワークシート
• テキストファイル

クエリを使うには？

クエリはクエリウィザードを使うことで簡単に作成できますので、実際に例をあげて説明することにします。例えば、レンタルサーバーなどでインターネット上に開設したWebサイトへのアクセス数を集計することもできます。今ではアクセス解析ソフトが便利になってきたので、わざわざこのような活用は多くないかもしれませんが、一例としてWebサーバーのアクセスログをクエリで取り込み、Excel上でアクセス数を集計するという例を考えてみます。

前提として、Excelを使うユーザーがそのアクセスログに対してインターネット経由で読み取り以上のアクセス権を持っていること、アクセスログはホームページ開設から現在までの累計アクセス数をページ別に記録したものと、最新日のアクセス数をページ別に記録したものの２つのログがあるとします。さらにアクセスログは区切り記号で項目が区切られた形式のテキストファイルであることを前提とします。下はここで実際に使用するダミーアクセスログです。日付はダミーです。

●累計アクセスログ (lime.log)
1<>xxx.net/index.html<>2169<>News<>xxx.xxx.xxx.xxx<>2016/7/30<>
2<>xxx.net/index1.html<>690<>What’s<>xxx.xxx.xxx.xxx<>2016/7/30<>
3<>xxx.net/index2.html<>656<>Activity<>xxx.xxx.xxx.xxx<>2016/7/30<>
4<>xxx.net/index3.html<>720<>Schedule<>xxx.xxx.xxx.xxx<>2016/7/30<>
5<>xxx.net/index9.html<>298<>Rule<>xxx.xxx.xxx.xxx<>2016/7/30<>
6<>xxx.net/index13.html<>376<>Column<>xxx.xxx.xxx.xxx<>2016/7/30<>
7<>xxx.net/index6.html<>269<>Contact<>xxx.xxx.xxx.xxx<>2016/7/30<>
8<>xxx.net/index7.html<>288<>Link<>xxx.xxx.xxx.xxx<>2016/7/30<>
9<>xxx.net/index4.html<>387<>Disaste<>xxx.xxx.xxx.xxx<>2016/8/9<>

●日計アクセスログ (day.log)
1<>xxx.net/index.html<>2<>News<>xxx.xxx.xxx.xxx<>2017/11/12<>
2<>xxx.net/index1.html<>0<>What’s<>xxx.xxx.xxx.xxx<>2017/11/12<>
4<>xxx.net/index3.html<>0<>Schedule<>xxx.xxx.xxx.xxx<>2017/11/12<>
9<>xxx.net/index4.html<>1<>Disaste<>xxx.xxx.xxx.xxx<>2017/11/12<>
3<>xxx.net/index2.html<>1<>Activity<>xxx.xxx.xxx.xxx<>2017/11/12<>
5<>xxx.net/index9.html<>0<>Rule<>xxx.xxx.xxx.xxx<>2017/11/12<>
6<>xxx.net/index13.html<>1<>Column<>xxx.xxx.xxx.xxx<>2017/11/12<>
7<>xxx.net/index6.html<>0<>Contact<>xxx.xxx.xxx.xxx<>2017/11/12<>
8<>xxx.net/index7.html<>0<>Link<>xxx.xxx.xxx.xxx<>2017/11/12<>

このログは次のような形式になっています。
ページ番号<>URL<>アクセス数<>ページタイトル<>最後のアクセス元IPアドレス<>日付<>

Webサイトのアクセスログを取り込む

累計アクセスログを読み込む

それではクエリを使ってWeb上のアクセスログを読み込んでみましょう。Excelのメニューの [データ] タブで[新しいクエリ] をクリック、[その他のデータソースから] → [Webから] をクリックします。

メニューの [データ] タブ→ [新しいクエリ] → [その他のデータソースから] → [Webから] をクリック

次に、 [Webから] ダイアログの [URL] 欄に累計アクセスログファイルのURLを入力し、[OK] ボタンをクリックします。

ログファイルのURLを入力する

ログファイルの内容が表示されますので、[編集] ボタンをクリックします。[クエリエディター] が表示されます。④メニューの [列の分割] → [区切り記号による分割] をクリックします。

[クエリエディター] のメニューから [列の分割] → [区切り記号による分割] をクリックする

[区切り記号による列の分割] ダイアログが表示されますので、[–カスタム–] 欄の下の欄に区切り記号 (このログの場合は<>) を入力し、[OK] ボタンをクリックします。

[–カスタム–] 欄の下に区切り記号を入力し、[OK] ボタンをクリックする

項目ごとに列に分割されたログが表示されます。

項目ごとに列に分割されたログが表示される

この列の中で集計に必要な列は [Column1.1] のページ番号、[Column1.3] のアクセス数と [Column1.4] のページタイトルだけですので、他の不要な列を削除します。[Column1.2]、[Column1.5]、[Column1.6]、[Column1.7]、の列を [Ctrl] キーを押しながらクリックして選択し、メニューの [列の削除] をクリックします。

不要な列を削除し、ページ番号、累計アクセス数、ページ名の３つの列だけが残って表示される

もし [Column1.1] のページ番号が昇順になっていない場合は、メニューの [並べ替え] で昇順をクリックして昇順に並べ替えします。ここまでできたら、メニューの [閉じて読み込む] をクリックします。Excelに新しいシートが作られ、そこにデータが読み込まれます。

必要なデータがExcelの新しいワークシートに読み込まれた

日計アクセスログを読み込む

続いて日計ログも同様の操作で取り込みます。ただし、日計ログでは最終アクセス日のデータも必要ですので、最終アクセス日の列は削除しないで、ページ番号、アクセス数、ページタイトル、最終アクセス日の列をExcelに取り込みます。ページ番号の昇順での並べ替えも忘れないようにします。

左から、ページ番号、日時アクセス数、ページタイトル、最終アクセス日の項目となっている

次にExcel画面右にある２つのクエリを両方選択して右クリックし、[グループへ移動] → [グループの作成] をクリックします。[グループの作成] ダイアログが表示されますので、適当なグループ名を入力し、[OK] ボタンをクリックします。

グループ名を入力し[OK] ボタンをクリックする

クエリを作成した後に修正したい場合は、Sheet2のクエリ結果のどこかを選択した状態で [クエリ] タブから [編集] をクリックします。[クエリエディタ―] が表示されますので、ここで修正ができます。後の作業が楽になるので、ここで行と列の入れ替えを行っておきましょう。メニューの [変換] タブで [入れ替え] をクリックします。行と列が入れ替わって表示されます。

行と列が入れ替わった

[ホーム] タブに戻り、[閉じて読み込む] をクリックします。Sheet2に編集した結果が表示されます。同様にSheet3（日計ログのシート）でも行と列を入れ替えます。こうしておくと後で集計するときにやりやすくなります。

クエリで編集した結果 (画面は累計アクセスログの例)

クエリで編集した結果 (画面は日計アクセスログの例)

5行目の日付セルはこのままだとわかりにくいので、書式設定で [日付] の表示形式にしておくとよいでしょう。

なお、Excelを起動したときに [セキュリティの警告] が表示されることがあります。これはインターネット上のデータを読み込んでいるために警告されるものです。[コンテンツの有効化] ボタンをクリックしてください。

集計表を作る

ログの取り込みができたら、このデータを使ってExcel上で集計しましょう。Sheet1に下のような集計表をデザインします。この時ページタイトルは、Sheet2のページタイトル行をコピーします。

Sheet1に集計表を作る

それでは、次に日計のシートを作成します。下の [Sheet2] タブを右クリックして [挿入] をクリックします。[挿入] ダイアログで [ワークシート] をクリックし [OK] ボタンをクリックします。

[ワークシート] をクリックし [OK] ボタンをクリックする。もちろんシートのタブの右にある「＋」マークでワークシートを追加してもよい

[Sheet4] が新たに作成されます。ここでわかりやすいようにシート名を変更しておきましょう。
[Sheet4] タブを右クリックして [名前の変更] をクリックします。ここでは「日計」としました。同様に [Sheet1] を「累計」に、[Sheet2] を「累計クエリ」に、[Sheet3] を「日計クエリ」に変更しました。次に日計シートの集計表をデザインします。累計シートをコピーして修正すると簡単です。

日計のシートで集計表を作る

取り込んだデータを集計表に入れる

それではデータの入力のための準備をします。まずは「累計」シートの最初のデータ欄 (画面では３行目)のB3からJ3 に最後にクエリを実行した日の前々日の累計アクセス数を手動で入力します。

前々日の累計アクセス数は前日の累計アクセス数から前日の日計アクセス数を引いた値になります。前々日の累計アクセス数がわからない場合は前日の累計アクセス数を入れてもいいでしょう。その場合は最初の日計アクセス数がすべて0になります。前々日の累計アクセス数を3行目に入れるのは、後述する自動集計を実行したときに計算に使うためです。A3のセルには前々日 (ここでは「2017/11/12」) を入力します。

その他、集計表を見やすくするために以下の設定を行っています。
◯B列からK列を全選択し、右クリック→ [セルの書式設定] → [表示形式] タブ→ [数値] で [桁区切り (,) を使用する] にチェックを付けます。これで数字に３桁ごとにカンマが入り見やすくなります。
◯A列を選択し、右クリック→ [セルの書式設定] → [表示形式] タブ→ [日付] を選択します。
◯K3の合計のセルにはB3からJ3までの合計を、SUM関数を使って入力します。

準備のできた集計表

次は一番重要なデータの自動入力の設定です。このためにはExcelのマクロ (VBA) を使います。マクロとはMicrosoft Office用に用意されたプログラミング言語の「VBA」を用いた一連の自動処理のことで、Excelのあらゆる操作を自動化することができます。マクロの使い方についてはここだけでは到底説明しきれないので、その詳細については別の機会で行いたいと思います。したがってここではひとまず後述するプログラミングのコードをコピー＆ペーストしてください。

まず、Excelの[開発]タブから[Visual Basic]をクリックするとマクロを作成したりする「Visual Basic Editor」が開きますので、上部のメニューにある[挿入]から[標準モジュール]をクリックしてください。

Excelの[開発]タブから[Visual Basic]をクリックすると上記のような「Visual Basic Editor」の画面が現れる

[挿入]から[標準モジュール]をクリックしたら上記のような入力画面が出てくるので、ここに後述するVBAのコードをコピー&ペーストする

貼り付けるVBAのコードは以下になります。

Sub syuukei()
　Dim N1 As Integer
　DIm N2 As Integer
　Dim D1 As Date
　Dim D2 As Date
　Dim D3 As Date
　Dim i As Integer
　Dim x As Long
　N1 = Cells(Rows.Count, “A”).End(xlUp).Row
　N2 = N1 + 1
　D1 = Sheets(“日計クエリ”).Range(“A5”).Value2
　D3 = Sheets(“累計”).Range(“A” & N1).Value2
　D2 = D1 – 1
　If D3 < D2 Then
　　　For i = 1 To 9
　　　　x = Sheets(“累計クエリ”).Cells(3, i) – Sheets(“日計クエリ”).Cells(3, i)
　　　　Sheets(“累計”).Cells(N2, i + 1) = x
　　　　Sheets(“日計”).Cells(N1, i + 1) = x – Sheets(“累計”).Cells(N1, i + 1)
　　Next i
　　　　Sheets(“累計”).Range(“A” & N2).Value2 = D2
　　　　Sheets(“日計”).Range(“A” & N1).Value2 = D2
　　　　Sheets(“累計”).Range(“K” & N2).Value = “=SUM(B” & N2 & “: J” & N2 & “)”
　　　　Sheets(“日計”).Range(“K” & N1).Value = “=SUM(B” & N1 & “: J” & N1 & “)”
　End If
End Sub

コードの解説はここでは割愛させていただきますが、このコードでは、「累計クエリ」シートと「日計クエリ」にあるデータを参照することで、「累計」シートにも「日計」シートにも最新の日付の行が追加され、その行に対して、前者では累計アクセス数が、後者ではその日ごとのアクセス数が反映される仕組みになっています。このマクロを４日間実行した結果は次の通りです。

累計の集計表

日計の集計表

この例では、１日１回クエリとマクロを実行することにはなりますが、データが自動的に追加されていくので、アクセスログを手作業でExcelに入力するなどという手間は必要ありません。

クエリを手動で実行するのは面倒だという場合は、クエリの自動更新という機能があります。クエリのセルを選択した状態で、[データ] タブの [すべて更新] の右下にある▼印をクリックし、[接続のプロパティ] をクリックします。[接続のプロパティ] ダイアログで、[ コントロールの更新] から [定期的に更新する] または [ファイルを開くときにデータを更新する] のどちらかまたは両方にチェックを付けます。[定期的に更新する] にチェックを付けた場合は、更新間隔を分単位で指定します。なお、この自動更新の設定はクエリごとに行う必要があります。

[接続のプロパティ] ダイアログで、自動更新の設定を行う

この自動更新はExcelが１日１回は起動していないと役に立ちません。そこで、Windowsの起動時に自動的にExcelを起動させる手もあります。それにはスタートアップにクエリの含まれるデータファイルを登録しておけばよいでしょう。また現状だとマクロも1日1回実行する必要がありますが、これも同様にExcelを起動した時に自動実行する設定が可能です。これらの説明については、また機会があれば解説してきたいと思います。

ライタープロフィール パソコン工房NEXMAG［ネクスマグ］編集部