HPのデータをexcelで集計したい。

大学駅伝のHPで、例えば出雲駅伝メンバー　http://www.izumo-ekiden.jp/runner/team/01.html
のHPで各選手のデータが表示していますが、これをexcelに貼り付けて平均値とかを計算したいのですがどうしたらよいのでしょうか？
現在はHPを範囲指定で選択しコピーでexcelに形式を選択でHTMLで貼り付けています。
どうもこのやり方だと単に文字になってるようです。
その後、書式変更で　分、秒、○○　に変更できるのでしょうか？
場合によっては箱根駅伝などは　時間、分、秒となりますのでその辺も合わせてお願いします。

No.11 ベストアンサー 回答者： GooUserラック 回答日時： 2016/10/10 17:01

No.9 のお礼について

登録の仕方はご指摘のURLの通りです。コピペするのが間違いないと思います。

この回答へのお礼

有難う御座いました。

goouserラックさんに回答いただいた内容でトライしようと思います。
次回の11月の全日本大学駅伝には活用できるよう頑張ります。

お礼日時：2016/10/10 17:44

No.12 回答者： WindFaller 回答日時： 2016/10/11 19:42

こんにちは。

>ただ、意味不明だけどこの関数をここに入れてくださいという風にバカチョン方式で出来るものであれば可能かもしれませんが、今のところそれさえも判りません。

やっと完成には近いものの、未完成な部分が残っているかもしれません。

ダウンロードしたら、アンチウィルスやアンチマルウェアのプログラムを掛けてよいでしょう。

私の場合は、単にマクロのセキュリティがいくつかありますから、それを外してくださればよいです。

・ダウンロードした後に、右クリックして、プロパティからブロックを外す。
「このファイルは他のコンピュータから取得したものです。……」
　ブロックの解除にチェック　->適用　->OK
　
・黄色のマクロの「セキュリティの警告」のバーから「コンテンツの有効化」の、ボタンをクリックしてもらう。
・「ユーザーマクロで行えます」をクリックします。
上記2つは外さないと動かないはずです。

2007でも、それ以降でも使えますが、それ以下のバージョンでは、かなり面倒になってしまいます。制作は、Excel2013 と、Office のCustomUI Editor （フリー)と、NPP(NotePad++)にXMLのアドインをして作りました。NPPが、こんなに便利なものとは知りませんでした。

なお、通例、セキュリティのために、マクロを全部消去するマクロというものをつけることがありますが、今回は含まれていませんが、バックアップで、マクロ抜きのファイルになります。元のブックには、データは一切入っていません。

なんとなくですが、このマクロが使えるのは、短い間だけのような気がします。マクロの内容は、月次ですが、メニューのリボン・カスタマイズは、現在勉強中ですが、今回もまた学ぶことが多かったです。こうした機会がないと、途中で投げ出していただろうと思います。

11月の全日本大学駅伝のデータを見ましたが、出雲駅伝からでは、かなりの加工が必要と見られますが、それでも、今回、Table 取得という方法を本格的に使いましたので、別のサイトでも応用できるような気がします。

コード自体は、#6とそう大きく変わるものではありませんが、今の内容も、あまり褒められたものではないように思います。

10/11 19:06:12 から、3日
http://bit.ly/2dTW96O

パスワードがあります。この今開いているスレッドのURLの7桁の数字です。

以下の画像は、ユーザータブの内容です。そこにボタンをまとめましたので、それらをクリックすればよいです。もしかしたら、バグが残っているかもしれませんし、PCそのものに、セキュリティがかかっていたら、取れなくなる可能性が高いです。中身は、InternetExplorerを使っていますが、ほとんど、見える状態で使ってますから、オブジェクトを残し忘れることはほとんどないはずです。

No.10 回答者： WindFaller 回答日時： 2016/10/10 14:48

マクロを出そうと思っていた者です。

http://www.izumo-ekiden.jp/runner/order/01.html

team -> order で、中身も変わりました。

やっと、今、一通り全部のデータ収拾ができたと思ったら、本日(10/10)のお昼頃、HTMLの内容を全面的に変えてきたので、今まで作ったものは、ボツになってしまいました。（よくあることですが)

しばらく開けてくだされば、完成までするつもりです。ここが開いていれば、数日の間は、ダウンロードできるように、ブックを公開します。

便利といえば便利かもしれませんね。手作業ではとても大変な作業ですから。

スキルを越えているというよりも、もともと知らないものでしょうから、見よう見まねでトライするかしないかの違いだけです。それで痛むものは何もないでしょう。なお、こういうものは、本質的には「ブラックボックス」ですから、あまり説明してもしょうがないです。

時々、その概念は？という人がいます。いえ、それはオブジェクトだよっていうと、オブジェクトは概念ではないか、なんていう話になります。

ただ、来年のことを少し考えてしまいました。
短時間のうちに変えてくると、来年は、まったく違うものになりそうな気がします。

突然にページの内容が変わったので、慌ててしまいました。

なお、マクロが不要だった、何も言わなくても、使わなければすみますが、質問は、ご質問者さんのためにあって、そうでないこともあります。私にとっては、今回は、よい勉強をさせていただく機会です。今回の場合、Table データとして取得していたら、一部の変更で済んだかもしれません。

この回答へのお礼

有り難う御座います。

只今、No4のgoouserラックに回答いただいた　関数の標準モジュールの登録を勉強中で、何とかなりそうな気がしてきた今現在です。

windfallerさんの回答にある　関数は、何をどういう風に行おうとしてる数式かさっぱりわかりません。

ただ、意味不明だけどこの関数をここに入れてくださいという風にバカチョン方式で
出来るものであれば可能かもしれませんが、今のところそれさえも判りません。

質問者さんもしくは閲覧者さんの参考になれるのであれば、windfallerさんの答えが完結するまで開けておきます。

お礼日時：2016/10/10 16:44

No.9 回答者： GooUserラック 回答日時： 2016/10/10 12:38

No.4 のお礼について

こちらではどこがスキルを超えているのが判りません。
よろしければ教えてください。
① 標準モジュールへ登録
・VBAを使っているようなので問題は無いと思います。
② 組み込んだ関数の使い方
・通常の関数と同じで数値バーの左の「Fx」から関数の分類の「ﾕｰｻﾞｰ定義」を選べば「OO_OO_OO_to_Time」が表示されています。あとは他の関数と同じです。
③ 書式設定は「mm:ss.00」などにして下さい。
・「分:秒.1/100秒」ですが判りますよね
④ その他
------------------------------------------------------------------
なお「OO_OO_OO_to_Time」な関数名は長いなど嫌ならば、使っている関数名などと被っていなければ、すべて入れ替えれば適当に直せます。
・本当は「OO.OO.OO to Time」したかったのですが「.」「 」(空白)が使えなかったので区別しやすいようにこうしました。

この回答へのお礼

ご親切に有難う御座います。
私はexcelに標準装備している機能のほんの一部を使ってるユーザーです。
セルに関数（ifなど）を入れて計算する、セルの書式を変更して文字、数値、日時に変更するなどの初級レベルです。

お言葉に甘えてわからないところ
① 標準モジュールへ登録　
　　No4で回答いただいた”標準モジュールへ登録”は
　　http://www.excel.studio-kazu.jp/lib/e1iw/e1iw.html
　　のように
　　function　ooから始まり　end　functionで終わる　No4での13行の関数を
　　登録するということでしょうか？

No4回答の　”☆使い方”以下は理解できます。

以上宜しくお願いいたします。

お礼日時：2016/10/10 16:29

No.8 回答者： mike_g 回答日時： 2016/10/10 12:23

[No.3お礼]へのコメント、

》 数式中の*1はどういう意味があるんでしょうか？
=("0:"&SUBSTITUTE(E8,".",":",1))*1
の末尾の「*1」のことですね？
=TIMEVALUE("0:"&SUBSTITUTE(E8,".",":",1))
と等価にするために四則演算させたのです。そういう次第なので、
「*1」は次の何れでも構いません。と憶えておくだけにして(なぜ？を貴方に説明すると長くなりそうだから)
￣￣加算⇒「+0」
￣￣減算⇒「-0」
￣￣乗算⇒「*1」
￣￣除算⇒「/1」

この回答へのお礼

わかりました

お礼日時：2016/10/10 15:43

No.7 回答者： WindFaller 回答日時： 2016/10/09 14:01

追伸

平均値とか……

'数式（平均)を加える
Sub AddFormulas()
Dim sh As Worksheet
For Each sh In Worksheets
　　With sh
　　 With .Range("E25").End(xlUp)
　　　　 .Offset(1).FormulaLocal = "=AVERAGE(R8C5:R[-1]C)"
　　　　 .Offset(1, 1).FormulaLocal = "=AVERAGE(R8C6:R[-1]C)"
　　　　 .Offset(1, -1).Value = "平均"
　　 End With
　　End With
Next
End Sub

'数式を削除する
Sub RemoveFomulas()
Dim sh As Worksheet
For Each sh In Worksheets
　　With sh
　　On Error Resume Next
　　　　With .Range("E25").End(xlUp)
　　　　 .CurrentRegion.SpecialCells(xlCellTypeFormulas).ClearContents
　　　　End With
　　　　.Cells.Find("平均").ClearContents
　　　　On Error GoTo 0
　　End With
Next
End Sub

この回答へのお礼

重ねて回答ありがとうございます。

お礼日時：2016/10/10 12:10

No.6 回答者： WindFaller 回答日時： 2016/10/09 10:41

こんにちは。

今、全部のページを試してみましたら、エラーが出ますので修正してください。
エラーの理由は、「北海道学連選抜」と「アイビーリーグ」で、わざわざ、学校のチームでないものの内部の名称を変えているのです。そんなのを見る人いないのにね。

コードは「標準モジュール」に貼り付けます。
実行する時は、必ず、新しいブックで行ってください。
そうしないと、上書きされてしまいます。
一度、取得したら、標準モジュールを解放すれば、xlsx ファイルとしても残しておけます。もちろん、xlsx で保存しようとすれば、マクロはなくなります。

-----------------
コードを検索(Ctrl + F )などをして、変えてください。

-------------
シングルクォート(')を削除
'　'シートを用意する
'　If Worksheets.Count < 21 Then
'　　 dif = 21 - Worksheets.Count
'　　 Worksheets.Add after:=Worksheets(Worksheets.Count), Count:=dif

　'シートを用意する
　If Worksheets.Count < 21 Then
　　 dif = 21 - Worksheets.Count
　　 Worksheets.Add after:=Worksheets(Worksheets.Count), Count:=dif
----------------

全リスト21チームを取得用に、21 に変える

>　For n = 1 To 1 ' 本来は21

　For n = 1 To 21
---------------
加筆
>Set oTeam = .document.getElementsByClassName("team")

　Set oTeam = .document.getElementsByClassName("team")　元からあったコード
　If oTeam.Length = 0 Then
　Set oTeam = .document.getElementsByClassName("team2")
　End If
　If oTeam.Length = 0 Then
　Set oTeam = .document.getElementsByClassName("team3")
　End If

以上　6行を加える

この回答へのお礼

早速の回答ありがとうございます。

すいませんが、私のスキルを超えてますので理解できません。

お礼日時：2016/10/10 12:09

No.5 回答者： WindFaller 回答日時： 2016/10/09 02:32

質問の意味が違っていたらすみません。

くだんの画面を見ていると、OZに見えてきます。

>箱根駅伝などは　時間、分、秒となりますのでその辺も合わせて

以下は、「選手紹介」の分だけです。他のフォーマットはまた別のコードが必要です。なお、正規表現を使えば、ふりがなの部分を中に入れられますが、それも面倒なので、省略しました。

ちょっとまだ、試験段階なので、21枚をすべて通してはいません。2/3枚程度です。

なお、#1さん紹介の「ExcelVBAでＩＥを思いのままに操作できるプログラミング術」という本の題名は知っていますが、読んだことはありません。私は、Firebug からはじめましたが、今は、IEに強力なツールがついていますので、それだけで制作が可能です。

また、Webサイトは、Table 構造になっていますから、Webクエリで取れるはずです。


'//
Public Const BASEURL As String = "http://www.izumo-ekiden.jp/runner/team/"
Sub GetRaceData()
　Dim objIE As Object 'IEオブジェクトを準備
　Dim i, j, t, n As Long, num As String, dif As Long
　Dim buf
　Dim oCoach, oTeam, oRunner
　Dim sh As Worksheet
'　'シートを用意する
'　If Worksheets.Count < 21 Then
'　　 dif = 21 - Worksheets.Count
'　　 Worksheets.Add after:=Worksheets(Worksheets.Count), Count:=dif
'　End If
　Set objIE = CreateObject("InternetExplorer.Application")
　objIE.Visible = True 'IEを表示
　
　For n = 1 To 1 ' 本来は21
　With objIE
　　num = Format(n, "00")
　　Set sh = Worksheets(n)
　　sh.Select
　　.Navigate2 BASEURL & num & ".html" 'IEでURLを開く
　　
　　Do While .Busy Or .readyState <> 4: DoEvents: Loop '読み込み待ち
　　Set oCoach = .document.getElementsByClassName("coach")
　　Set oRunner = .document.getElementsByClassName("runner")
　　
　　If oRunner.Length = 0 Then MsgBox "失敗": GoTo endLine
　　buf = oRunner(0).innerText
　　On Error Resume Next
　　sh.Name = buf
　　On Error GoTo 0
　　buf = ""
　　
　　If oCoach.Length = 0 Then MsgBox "失敗": GoTo endLine
　　t = 2: j = 1
　　For i = 0 To oCoach(0).Cells.Length - 1
　　 Cells(t, j).Value = oCoach(0).Cells(i).innerText
　　 If i Mod 2 = 1 Then
　　　 j = 1: t = t + 1
　　　Else
　　　 j = j + 1
　　　End If
　　Next
　　
　　Set oTeam = .document.getElementsByClassName("team")
　　
　　t = 6: j = 2
　　If oTeam.Length = 0 Then MsgBox "失敗": GoTo endLine
　　 For i = 0 To oTeam(0).Cells.Length - 1
　　　 If oTeam(0).Cells(i).innerText Like "選手名" Then
　　　 j = 1
　　　 t = t + 1
　　　 ElseIf i > 1 Then
　　　 j = j + 1
　　　 End If
　　　 buf = oTeam(0).Cells(i).innerText
　　　 If buf = "10000m" Then
　　　　　t = t + 1: j = j - 1
　　　 ElseIf buf Like "*#.##.#*" Then
　　　　 buf = Replace(buf, ".", ":", 1, 1)
　　　　 sh.Cells(t, j).NumberFormatLocal = "mm:ss.00"
　　　 End If
　　　 sh.Cells(t, j).Value = buf
　　 Next
　　End With
　　buf = ""
　　sh.UsedRange.Columns.AutoFit
　 Next n
endLine:
　 objIE.Quit
　　Set objIE = Nothing
End Sub

p.s.こういうものには、年齢の差は出ないような気がします。
しかし、私個人の需要と供給のバランスは、うまくいかないものですね。自分ができるようになったら、周りは必要としなくなっていました。

この回答へのお礼

早速の回答ありがとうございます。

すいませんが、私のスキルを超えてますので理解できません。

お礼日時：2016/10/10 12:09

No.4 回答者： GooUserラック 回答日時： 2016/10/08 21:59

「mm.ss.00」の文字列をシリアル値に変換する関数を作成しました。

標準モジュールに登録すると「ユーザー定義」関数として使えます。
--------------------------------------------------------------------------
Function OO_OO_OO_to_Time(分秒文字列 As String) As Date
分秒文字列 = Trim(分秒文字列)
If Len(分秒文字列) <> 8 Then Exit Function
If Not IsNumeric(Left(分秒文字列, 2)) Then Exit Function
If Mid(分秒文字列, 3, 1) <> "." Then Exit Function
If Not IsNumeric(Mid(分秒文字列, 4, 2)) Then Exit Function
If Mid(分秒文字列, 6, 1) <> "." Then Exit Function
If Not IsNumeric(Right(分秒文字列, 2)) Then Exit Function
OO_OO_OO_to_Time = Left(分秒文字列, 2)
OO_OO_OO_to_Time = OO_OO_OO_to_Time * 60 + Mid(分秒文字列, 4, 2)
OO_OO_OO_to_Time = OO_OO_OO_to_Time * 100 + Right(分秒文字列, 2)
OO_OO_OO_to_Time = OO_OO_OO_to_Time / 8640000
End Function
--------------------------------------------------------------------------
☆ 使い方
・A1 セルに「30.12.26」が入力されているとしたら「=OO_OO_OO_to_Time(A1)」の様にしてください。
・直接入力するならば「=OO_OO_OO_to_Time("30.12.26")」の様にしてください。
※ 書式設定は「mm:ss.00」などにして下さい。
※ 時間を含むものはフォーマットが不明なので対応していません。

この回答へのお礼

早速の回答ありがとうございます。

すいませんが、私のスキルを超えてますので理解できません。

お礼日時：2016/10/10 12:09

No.3 回答者： mike_g 回答日時： 2016/10/08 21:54

何はなくとも先ずはデータをワークシートに取り込むことですね。

添付図は、[データ]⇒[外部データの取り込み <Webクエリ>]でご案内のページの[選手]直下のデータを取り込んだだけの状態を示しています。

例えばセル E8 のデータ「28.46.81」を時間データに変換するには、
式 =("0:"&SUBSTITUTE(E8,".",":",1))*1
を適用して、「m:ss.00」なる書式を施してあげればＯＫです。

後はご自分でどうぞ。

この回答へのお礼

早速の回答ありがとうございます。

回答2のtom04さんと同様に
最後のSUMなどの関数計算ができません。

すいません　数式中の*1はどういう意味があるんでしょうか？

お礼日時：2016/10/10 12:06