FC2ブログ

ロト6データ更新ブック修正法とお知らせ2

エクセルメモの記事
データを自動入力しよう」シリーズで作成法を紹介し
ワークブックの配布を行っていた

ロト6データ更新_Ver3.xlsm」を使用されている方から
第1128回(2016年12月1日)のデータが入力されない
・・・との指摘を頂きましたので

メンテナンス(修正)方法をお伝えします。


さて、問題の内容から万年カレンダーと
カレンダーを参照する際の数式に異常(問題)がありそうだったので
早速調べてみました


修正すべき箇所は2か所ありました

1つ目は「検索テーブル」シートにある万年カレンダーの12月部分
2つ目は「更新」シートにある月抽選回数テーブルの数式


■万年カレンダー修正箇所
「ロト6データ更新_Ver3.xlsm」の検索テーブルシートを開きます

12月のテーブルに問題がありました
画像で修正箇所を確認してください

万年カレンダー修正箇所と修正後

表の列見出しにある「1~7」の数値は曜日を表します
1が日曜、2が月曜・・・7が土曜


12月の抽選は30日までという決まりがあるので
日曜日が1日なら必然的に
12月第1抽選は2日というように想定しながら作ったもの

直すのは水曜が1日の場合と木曜が1日の場合の
30日と31日の部分(画像右側水色、左側ピンク色)

抽選回数が8回になっていたので「9」という数値に修正します


■月抽選回数&URL作成表
次に「更新」シートに移動します

修正するのは月抽選回数とデータを参照するURLを作成する表です
この表の「12月」の抽選回数を参照するための数式に誤りがありました

修正するのはこの部分→セル番地(R3)

月抽選回数テーブル修正箇所

入れ子にしたIF文で12月を見ようとした時
表の列を参照する座標がセル番地ではなく数値になっていました

入力されている数式は

=IF($L3=1,OFFSET(検索テーブル!$N$1,Q3,$O3),IF($L3=12,OFFSET(検索テーブル!W$1,Q3,3),OFFSET(検索テーブル!$E$1,Q3,$O3)))

※下線を引いた中の赤文字が修正する部分

IF($L3=12,OFFSET(検索テーブル!W$1,Q3,$O3)

数式バーでの修正前後

「3」となっていた箇所を「$O3」に変更します
※列O(オー)3です

数式バーで数式を修正したら、
セル(R13)まで数式をオートフィルで引っ張ります

オートフィルで数式をコピーする

訂正はこれで完了です


問題が解決されているかテストするには
第1128回(2016年12月1日)以降のデータを削除し

最終更新テーブルの値を図のように変更し
ブックを上書き保存して一旦閉じて開きなおします

最終更新を入力しなおす


再度開くと12月1日以降のデータ入力が開始されます。
※1127回は(2016年11月28日)



☆☆あとがき☆☆
今回は、作成時のミスが原因で大変迷惑をおかけしました<(_ _)>


毎抽選終了後にブックを開いていたら
このバグは出なかったでしょうが

様々な使用条件を想定したモノ作りをしなければと
痛感した今日この頃なのでした(>_<)


今回の問題点を改善したブックをダウンロードするには

■ロト6データ自動更新Ver3_xlsm
>>ダウンロード


ちなみにDropboxで従来通りデータを共有できるのは
今月いっぱいで終了だそうです

それ以降はリンクが無効になるため
ブックを使用されたい方はお早めに

3月以降のデータ共有もできるのですが
新たなリンクの作成が必要になる事

これまで提供したデータやブックがどのくらいダウンロードされたか
こちらから知る手段がないため

配布は今月いっぱいで終了する予定です
悪しからず(´・ω・`)


次回お楽しみに☆彡

ロト6データ更新ブック修正法とお知らせ☆彡

史上初の1等・2等該当なしとなった第798回ロト6
当ブログでは数字選択式宝くじの抽選結果をWEBから取り込み
Excelワークブックにデータを蓄積するマクロブックを配布していましたが

9月28日ドリーム館


想定外の出来事に、バグが発生してしまいました(>_<)


ワークブックにデータを取り込む方法や
データ転記の方法については
エクセルメモの「データを自動入力しよう」シリーズで紹介しています

その中でナンバーズの3ゾロ・4ゾロの時に
ストレート以外は該当なしとなる事例から

ミニロトの1等該当なしも想定に入れるべきと書いていましたが
想定していたこととはいえ
ブック作成時にそのことを盛り込んでいなかったことが原因で
プログラムが途中で中断してしまう現象が起きてしまいました

データ収集ワークブックについては
配布を開始してから2年近く経過しているため
ダウンロードされた方がその後どのように使用されているか
こちらからは知ることができないという事情もあり

配布したブックをそのまま使用されている方は
これまでのブックを削除して再ダウンロードしていただければ幸いです

★ロト6データ更新_Ver3.xlsm
>>ダウンロード


配布したブックにワークシートを追加して
独自の研究用に使用されている方もいる可能性があるので

今回はバグの修正法を紹介しておきます


データを自動入力しようで書いていますが
WEBから取り込んだデータは
桁区切りや口数の「口」賞金額の「円」が含まれ、そのままでは文字列です


文字列を数値に変換するのはVBAでもできるのですが
コードをわかりやすくすることとメンテナンスを容易にするために
文字列変換用のテーブルをもう一つ作っています

ロト6データ更新_Ver3-データ取り込み画面


画面の下にある表が数値変換テーブルです


ここで修正するのはC19セルとF19セル

これまでに入力されていたのは
C19セルが
=VALUE(LEFT(C7,LEN(C7)-1))


F19セルが
=VALUE(LEFT(F7,LEN(F7)-1))


第798回のように2等「該当なし」になると
このままの数式では文字列が返されてしまい

プログラムがデータを転記する時
セルのデータを変数に格納する時にデータ型が違うため
プログラムが終了してしまいます


エラーが発生して警告画面が表示されたら
終了ボタンをクリックして、C19・F19セルの数式を修正します


C19セルの数式を修正

C19に入力する数式は
=IF(C7="該当なし",0,VALUE(LEFT(C7,LEN(C7)-1)))


F19の数式を修正

F19に入力する数式は
=IF(F7="該当なし",0,VALUE(LEFT(F7,LEN(F7)-1)))



数式を終了した後、上書き保存してブックを閉じ開きなおせば
第798回以降のデータを取り込み
「賞金額」シートにデータが転記されます。

賞金額シートに第798回以降のデータが入力された



☆☆お知らせ☆☆
ロト6で2等該当なしが起きてしまったので
ミニロトのデータ更新用ブックについても「1等該当なし」を想定し
バージョンアップしました

ミニロト抽選前


★ミニロト賞金額データ自動更新_Ver4.xlsm
>>ダウンロード


今回のバージョンアップでは
新しく「出目表」と「出現回数」シートを追加しました

月齢がわかる出目表を追加


「出目表」は2021年1月21日までの旧暦・月齢が記入されています
Excelのフィルタ機能を使えば月齢による数字の流れをチェックすることができます



ロト7・ナンバーズのデータ更新シートも配布中です。

ロト7抽選前


★ロト7データ自動更新.xlsm
>>ダウンロード



ナンバーズ抽選前

★ナンバーズ3データ更新.xlsm
>>ダウンロード

★ナンバーズ4データ更新.xlsm
>>ダウンロード



ぜひご活用ください


次回お楽しみに☆彡

ロト7データも自動入力♪

ロト7が始まって早1か月・・・

ロト7ポスター


エクセルメモで「データを自動入力しよう」という記事を書きましたが
記事を書いた当時はロト7がなかったため

ロト7バージョンのデータ自動更新ブックを作りました
データ収集にぜひ活用してみてください(^_-)-☆

みずほ銀行宝くじコーナー_ロト7結果ページ


ワークブックを開くだけで
最新のデータをWEBページから取り込み入力まで
Excel君がすべてやってくれます


\ロト7データ自動更新_更新シート


トップ画面の「更新シート」では
最新回のデータを取り込んだ後未入力分があれば
最大1年までさかのぼって「賞金額シート」にデータを入力します。

\ロト7データ自動更新_賞金額シート


「賞金額シート」はこのようなレイアウトになっています
データが蓄積された時に
フィルタ機能を使えば別の角度から傾向を探ることができるように
自動入力される「回号」「日付」のほかに
2020年までの暦データが予め記録されています



ロト7版では「出目表」も毎回自動的に更新されるようにしました。

ロト7データ自動更新_出目表シート


「本数字」「ボーナス数字」のほかに
2020年までの月齢データを加え

将来的に月の暦による数字の流れを分析する時に使えるようにしてあります。


■ロト7データ自動更新.xlsm
>>ダウンロード






■ワークブック使用前の初期設定について

ダウンロードしたワークブックを開くと
VBAを使わない方の場合
「セキュリティの警告‐複数の問題」が表示されます。

「データを自動入力しよう」シリーズでこの対処方法は書いてきましたので
今回はこのような警告画面が表示されなくなる方法を

まずExcelを起動します。
今回はExcel2013の画面で説明します。

Excel2013画面


ブックを開くときに出てくる画面の【オプション】をクリックします。
ショートカットキーは【Ctrl】+【O】

※Excel2007・2010の場合
 画面左上の【Officeボタン】をクリックし
 メニューの一番下にある【Excelのオプション(I)】をクリックします。


Excelのオプション画面

Excelのオプション画面が表示されたら
左側のメニューにある【セキュリティセンター】をクリックし
右下にある【セキュリティセンターの設定(T)】をクリックします。

信頼できる場所画面


左側メニューの【信頼できる場所】をクリックし
【新しい場所の追加(A)】をクリックします。

Microsoft_Officeの信頼できる場所入力画面


入力画面が表示されたら
パスを直接入力するか【参照(B)】をクリックして
エクスプローラーからフォルダをクリックして指定します。


場所を指定した後は【OK】ボタンをクリックして
Excelのオプション画面を閉じます。


ファイルを保存した場所を指定すれば
警告画面は表示されることはないでしょう



★★編集後記★★
ロト7データ自動入力シート
ぜひ使ってみてください


子猫ちゃん♪


今のところ抽選データは5回分ですが
データが集まってきた時に

お宝発見のきっかけになれば幸いです(´・ω・`)


次回お楽しみに☆彡

データを自動入力しよう 番外編

久しぶりのエクセルメモ
新年なので特別番外編


雑誌でいえば新年特大号特別付録のような感じです

ネコの手も借りたいあなたに

宝くじの研究をする時に欠かせないデータ

ロト6は週2回ですし
ナンバーズなどは週5回でうっかりすると
みずほ銀行のHPで確認して入力するデータが溜まりがち

データ入力を自動化して
分析・数字選択の時間に充てたいですね(=^・^=)

そんなネコの手も借りたい方に朗報です


データを自動入力しよう part3で作成したファイルを
さらに便利なものにヴァージョンアップしてみました


ミニロトは過去5回分のデータを読み込み自動入力
ロト6は過去10回分までしか入力できませんでしたが

今回は最新(当月)のデータ、
さらにバックナンバーになる過去12カ月のデータまでを
最終更新時の状況に応じて追加入力できるように修正しました



みずほ宝くじコーナートップページ

みずほ銀行宝くじページで
「当せん番号」「当選口数」「賞金額」「総売上額」をチェックできるのは
当せん金の支払いが関与する上記のデータまで

それ以上さかのぼると当選番号の確認のみになるので
今回のヴァージョンアップで必要と思われるデータをフォローできると考えています


ダウンロードはこちらから

>>ロト6データ更新_Ver3.xlsm


>>ミニロト賞金額データ自動更新_Ver3.xlsm



こちらのファイルには最新抽選データが収録されているので
マクロブックに抵抗のある方はデータの閲覧用(読み取り)で

毎回ファイルを開き結果確認して入力するのが面倒だという方も
ブックを開いたときに過去1年分まで入力可能なので
是非ご活用ください



お宝データが見つかるかもしれませんよ(。・ω・。)


今回特に修正は加えていませんが
ナンバーズのデータを入手したい方はこちらもどうぞ

>>ナンバーズ3データ更新.xlsm


>>ナンバーズ4データ更新.xlsm


こちらのブックもナンバーズの抽選結果を過去1年まで遡って
WEBページから読み込み入力できるものなので
忙しくて更新作業がままならない方にオススメですよ☆彡


★★ブックの作成方法については★★

>>データを自動入力しよう part3


>>データを自動入力しよう Part4


上記の記事を参照ください



■ファイル使用時の注意
ダウンロードしたファイルを開くときに
「セキュリティの警告 - 複数の問題」が表示されます

セキュリティの警告画面

マクロ・データ接続、2つの項目の
「このコンテンツを有効にする(E)」にチェックを入れ【OK】ボタンをクリックするか

セキュリティセンターを開き
ファイル(ブック)を入れたフォルダのパスを入力して
信頼のおける場所として指定すると次回から警告は出なくなります。

ロト6の結果やデータを頻繁に見る方でしたら
デスクトップにファイルを置いておき
信頼のできる場所にデスクトップを指定してもよいでしょう
(パス入力が必要です)

★★編集後記★★
データを自動入力しよう part5を書いてから8か月
当時急いで作成し記事を書いたこともあって

ロト6・ミニロト用のファイルに
読み込みデータの制限があるままになっていたのが心残りでした

記事を書いた後
私自身も作成時のまま使用していたものの
プログラムなどの見直しをしていませんでした


年末年始の短い休みに
久しぶりにブックの構造やプログラムを見直していたら
10分程度の作業で改善出来て驚いてしまいました

やればできるのに
面倒臭かったという理由や
個人的に使用する分には不便を感じないという理由で
先延ばしにしていたんですね


おめざめ


いつかやろうというのは
いつまで経ってもいつかのまま(>_<)


ほとんど放置状態でしたが
やり残しを解決できてスッキリした私なのでしたo(〃^▽^〃)o


今回のデータ入力ブックを使って
お宝データが見つかりますように☆彡



皆様の幸運をお祈りしています(^_-)-☆


次回お楽しみに☆彡

データを自動入力しよう part5

パート4までの流れを簡単に振り返ると
WEBページからデータを取り込み、
同じワークブックの別シートにデータを蓄積する仕組みを作ってきました

猫の手も借りたい方に


Excelを使って分析する場合、研究方法は多種多様ですから
複数のワークブックを使っている事でしょう

膨大な量のデータを持っていても抽選結果が出てから
入力が終わるまで検証に移れなかったり(;一_一)

手入力の場合、一つ一つのテーマをじっくり観察できるというメリットもありますが
VBAでワークブックを巡回、最新データを入力してくれる仕組みがあれば便利ですね♪

今回はパート3で作ったロト6データ入力ブックを使って
取り込んだデータを他のブックに入力するプログラムを作成していきます。


VBA「ファイル操作」の構文は至って簡単!
これにここまで学んだ事を組み合わせる、
データのテーブル構造により作成の時間は違ってきますが
1度作ってしまえば、手入力に使っていた時間を削減できるので
興味を持った方は是非チャレンジしてみてくださいね

それではファイル操作について学んでいきましょう♪


■今回使用するファイル
パート3、サブルーチンの項目で作成したプログラムに新しいコードを書き加えて
巡回入力システムを作ります。


★ロト6データ更新.xlsm
>>ダウンロード

コードを確認するには、【Alt】+【F11】でVBEを起動させ
【プロジェクトウィンドウ】内にある
【標準モジュール】の【賞金額入力】をクリックして開いてください



★データ入力サンプル.xlsx
>>ダウンロード

サンプルファイルにデータを入力することを前提に話を進めます。

コードの細分化


ミニロトのデータ更新プログラムを作成したい方はコチラを

★ミニロト賞金額データ自動更新.xlsm
>>ダウンロード


※サンプルファイルにデータを入力するコードが書かれていますので
 コメントアウトしてご活用ください


★ミニロトデータサンプル.xlsx
>>ダウンロード



■VBAでのファイル操作
ここで手入力の場合にどのような作業が必要なのか確認しましょう

1:ワークブックを開く
2:データを入力するシートを選択(アクティブに)する
3:最終行に入力する
4:ワークブックをを上書き保存する
5:ワークブックを閉じる



1・2・4・5はVBA解説書で短いコードが紹介されています
についてはこれまで学んだ事を応用するだけ

1・2・4・5の作業をする構文です


1)ワークブックを開く
ブックが同じフォルダにある場合
Workbooks.Open ThisWorkbook.Path & "\ファイル名.拡張子"
「"\」「"」の間にファイル名と拡張子を入れます

ロト6の場合「データ入力サンプル.xlsx」
ミニロトの場合は「ミニロトデータサンプル.xlsx」です。

別フォルダにブックがある場合
Workbooks.Open Filename:="ファイルパス\ファイル名.拡張子"

ファイルパスは「C\」から始まるもの
マイドキュメントのアドレスバーから【アドレスのコピー】で
ダブルクォーテーションの間にペーストしてもOKです。



2)シートを選択
Worksheets("シート名").Select

シート名は「サンプル」になります


4)ワークブックを上書き保存
ActiveWorkbook.Save

5)ワークブックを閉じる
ActiveWorkbook.Close



3以外のコードを通しで見るとこのようになります


Private Sub ファイル操作()
'1:ブックを開く(同じフォルダの場合)
Workbooks.Open ThisWorkbook.Path & "\データ入力サンプル.xlsx"

Worksheets("サンプル").Select '2: シート選択

'************************************************************
'ここに「3」が入る
'************************************************************

ActiveWorkbook.Save '4:上書き保存
ActiveWorkbook.Close '5:ブックを閉じる

End Sub


「'」で始まる部分はコメント部分
VBE画面では緑色の文字になっていますね

ファイル操作

Sub () ~ End Subプロシージャでもよいのですが
勝手に動作させたくない事と
後にメインプログラムから呼び出す処理をしたいので

Private Sub としています。

これでプログラムの外枠が出来ました
残りは「3」のデータ入力部分を埋めるだけ


■データを入力する
それでは「データ入力サンプル.xlsx」を開いてみましょう

データ入力サンプルシート

見出しが1行目にあり左から
回→日付→第1数字→・・(中略)・・→ボーナス数字
・・・というテーブル構造になっています

表の右側部分はデータ分析に使用してください
分析はExcel関数にお任せ
研究テーマに合わせExcel関数を入力しておくとよいでしょう

パート3で変数宣言、変数にデータを格納するコードをすでに作ってあるので
先ほど残しておいた「データ入力」部分に入れるコードは
すでに出来上がっているコードをコピペします

コピペする部分

赤枠で囲ってある部分、第1数字以降のセル番地が異なるので
修正します

入力部分をコピペする

修正後出来上がったコードはこのようになります

セル番地を修正してコード完成

ここまでの流れでお気づきになった方もいらっしゃると思いますが
複数のブックを巡回して入力する場合
テーブル構造を同じにしてあると後のコード作成が格段に楽になります
※同じセル番地に同じデータが来る

テーブル構造が同じなら、ここまで作ったコードをさらにコピペして
「ファイル名」「シート名」を書き換えていきます

注意点は、
Sub ()  ~ End SUb までの間に記述できるテキストの限界は31kb
※コメントは除く。
※31kb以上のコードはプロシージャとして認識できないためエラーになります。


コードの長さに注意しながら
フォルダごとに別プロシージャにするなど
後のメンテナンスの事も考慮し「Private Sub」・・・
子プロシージャを複数にしましょう

セル番地が異なるファイルにデータ入力をする場合は
先ほどコピペした部分のCellsプロパティの部分も書き換えましょう

Cells(行,列)です


完成したらメインプログラムのこの部分に
Callステートメントを書き足します

コード呼び出し部分を追加する

書き足すコードは

Call ファイル操作

Callステートメント

ファイルをダウンロードした方は
コメントアウトしてある部分の「’」を消してください
※「データ入力サンプル.xlsx」「ミニロトデータサンプル.xlsx」を同じフォルダに置いてください



■巡回入力のヒント
テーブル構造が同じ場合はコードを短く出来ます
ヒントになれば幸いです(^_-)-☆

例えばデータ更新ブックにシートを追加し
巡回するブックが同じフォルダにある場合は「ファイル名.拡張子」「シート名」
複数のフォルダにブックがある場合は「ファイルパス」「シート名」の一覧表を作り

For ~ Nextステートメントで
一覧表の「ファイル名」「シート名」を順次読み込み
変数に格納してしまう方法もあります

この場合は先ほど作成したコードの
手順1の前に変数宣言

「カウンタ変数(数値)」 → Integer
「ファイル名/パス(テキスト)」 → String
「シート名(テキスト)」 → String


手順1~5の前後を、For ~ Nextステートメントで囲み
開くブックのパス、選択するシートの情報を変数に格納するコードを書き

ワークブックを開く時のコードのパスやファイル名の部分を変数に書き換えます
例)Workbooks.Open ThisWorkbook.Path & [変数名]

選択するシートの部分も同様
例)Worksheets("[変数名]").Select



■サンプルファイルについて
ダウンロードしたファイルを開くときに
「セキュリティの警告 - 複数の問題」が表示されます

セキュリティの警告画面

マクロ・データ接続、2つの項目の
「このコンテンツを有効にする(E)」にチェックを入れ【OK】ボタンをクリックするか

セキュリティセンターを開き
ファイル(ブック)を入れたフォルダのパスを入力して
信頼のおける場所として指定すると次回から警告は出なくなります。

詳細はpart2で紹介したとおりです
>>part2 追記を読む


★★編集後記★★
パート1を書いたのは昨年5月15日
ずいぶん時間が空いてしまい、ほとんど忘れ去られている気もしますが
やり残し感があったのでGW最終日区切りをつけられてほっと一息


今回紹介したデータ入力のコード
データ更新作業に時間を取られていると考えている方は
コード作成に手間はかかりますが

1度作ってしまえば、今後時間に余裕ができると思いますので
チャレンジしてみてはいかがでしょうか
1つのファイルを開いて、入力、保存、閉じるまで1秒位
VBAの速さを体感していただけたら幸いです

二人でお出かけ♪


データを自動入力しようシリーズはこれでおしまい
次回はデータ連携という事で
Accessデータベースとの連携について書ければと考えています

◆追記◆
このシリーズではお伝えしていませんが
VBAを使わなくても「リンクテーブル」という機能を使うと
元データがあるブックとの接続を作ることで
ブックを開いた時に更新することもできます

Excelの使い方は人それぞれで
他の方が作成したブックを見るのも、いい勉強になります

現在コメント欄を閉じているので
質問等は拍手コメントの方にお願いいたします。



次回お楽しみに☆彡

テーマ : エクセル
ジャンル : コンピュータ

プロフィール

ロトマスターきいさん

Author:ロトマスターきいさん
訪問ありがとうございます♪

マニアックだったり
おバカだったりしますが
ジャンルを超えて
「楽しさ」「ワクワク感」が伝わる・・
そんなブログにしていきたいな☆彡


今後ともよろしくお願いします☆彡

最新記事
お気に入り
今日のつぶやき
全記事表示リンク

全ての記事を表示する

カテゴリ
カレンダー
プルダウン 降順 昇順 年別

07月 | 2019年08月 | 09月
- - - - 1 2 3
4 5 6 7 8 9 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28 29 30 31


FC2カウンター
検索フォーム
free spase


かわいがってやってね♪




潜在意識は知っている