EXCEL VBA でフォルダ内のブックを開きデータを読み込む

FileSystemObject と VBA の関係

 Power Query が使えないと不便である.先日 EXCEL 2010 の素の環境でフォルダ内のブックをすべて開き,データを読み込む必要があったのだが,Power Query が使えなかったため,VBA でブックを開いて読み込まなければならなかった.備忘録としての記事である.

 この記事はPower Query でフォルダから複数ファイルを一括インポートすると対応している.やっていることは同じだが,.xls 形式だとクエリの検証に時間がかかるため,VBA で読み込んだほうが動作は早いかもしれない.

 フォルダー内のファイル一覧を取得するには FileSystemObject を使う場合と Dir() 関数を使う方法とがある.ここでは FileSystemObject を使うことにする.

 データは Range オブジェクトに格納されているため,Range オブジェクトを取得するのが当面の目標となる.

“EXCEL VBA でフォルダ内のブックを開きデータを読み込む” の続きを読む

Power Query でフォルダから複数ファイルを一括インポートする

ソースファイル内部の候補がポップアップする

 Power Query で,あるフォルダ内の同一構造のファイルを一括してインポートする機会は多い.M 言語は未開拓であるが,その一端に触れてみた.

 EXCEL ブックであれ csv ファイルであれ,構造化されたデータという観点から見れば,ファイル形式などどうでも良い話である.この抽象化が理解できれば,Power Query への理解が一定程度進むのではないかと思う.

 この記事はEXCEL VBA でフォルダ内のブックを開きデータを読み込むと対応する.

“Power Query でフォルダから複数ファイルを一括インポートする” の続きを読む

色の知覚(1)太陽光

太陽光の分光強度分布 (National Renewable Energy Laboratory)

 色彩に関してはこれまで先人の膨大な研究の積み重ねがある.その一端を紹介し,色の物理的性質から生理的反応への橋渡しについて考察する.

 今回は太陽光について調べた.データベースは主に National Renewable Energy Laboratory から取った.日本国内にも太陽光についてのデータベースは気象庁新エネルギー・産業技術総合開発機構がデータを公開している.

“色の知覚(1)太陽光” の続きを読む

1920年から2015年までの都道府県別の5歳階級別人口推移

1920年から2015年までの都道府県別の5歳階級別人口推移

 e-Stat を渉猟していると面白いファイルを見つけた.国勢調査は 1920 年から開始されており, 2020 年 3 月現在では最新の調査結果は 2015 年のものである.20 回分の人口データが一つのファイルにまとめられており,グラフ化するには格好のデータである.

 年齢(5歳階級),男女別-都道府県(大正9年~平成27年)というファイルである.リンク先のページにはファイルが 3 つあるが,最後のものが最も粒度が細かいので,これをグラフ化する.

年齢(5歳階級),男女別人口-都道府県(大正9年~平成27年)
年齢(5歳階級),男女別人口-都道府県(大正9年~平成27年)

“1920年から2015年までの都道府県別の5歳階級別人口推移” の続きを読む

厚労省「地域ごとのまん延の状況に関する指標等」の PDF から Power BI Desktop でデータを抽出し EXCEL のグラフに表現する

各都道府県の新型コロナウイルス確定患者数の推移

 新型コロナウイルスのパンデミック宣言以降,Twitter でフォローしているアカウントに自然と相互協調の動きがみられる.

 このツイートから始まった一連のやりとりで,厚労省の発表した PDF からテーブルを抽出するくだりに注目した.

 今回はここを画像つきで実施してみた.

“厚労省「地域ごとのまん延の状況に関する指標等」の PDF から Power BI Desktop でデータを抽出し EXCEL のグラフに表現する” の続きを読む

国勢調査から5歳階級の人口推移を調べる

日本人口の年齢階級推移(国勢調査より筆者作成)

 人口統計は最も重要な基幹統計の一つである.総務省の e-Stat は確かに有用であるが,かゆいところに手が届かない.例えば「市区町村ごと,年齢5歳階級ごとの人口構成の国勢調査ごとの推移を知りたい」という要求には全く無力である.

 主として技術的な理由によるものと,統計調査の粒度の細かさによる.技術的な理由としては,データベースの画面表示セル数の上限を容易に超えてしまうデータ量になってしまうことである.しかし,根本的な理由は調査の粒度の細かさである.

 2005 年以前と 2010 年以降とでは調査の精度が違う.今後は高精度なデータファイルが e-Stat に掲載されていくものと思われるが,2005 年以前に関しては都道府県より細かい粒度は存在しない.そこを求めると手作業になってしまい,現実的ではない.国立社会保障・人口問題研究所ならデータを持っているかもしれない.

 2020 年は国勢調査の年にあたる.総務省にはできるだけ細かい粒度でのデータ掲載を望むものである.

“国勢調査から5歳階級の人口推移を調べる” の続きを読む

PowerPivotで100万件超えのデータを取り出す

「Power Pivot」タブ「データモデル」から「管理」

 EXCEL のワークシートの仕様上,100 万件を超えるデータは扱えない.これは大規模なデータを扱う際の制約である.180万件のデータをPower Queryで処理してEXCELがオーバーフローした話 でも述べたが,この制約を乗り越えてデータをインポートするにはデータモデルに読み込むほかはない.

 SQL Server で PowerQuery が使えればこういった制約を回避できるのだが,ないものは仕方がない.今回は PowerPivot を用いてデータモデルに蓄積したデータを取り出す方法を見つけたので備忘録として記す.

“PowerPivotで100万件超えのデータを取り出す” の続きを読む

SPSS から Ubuntu 上の SQL Server に接続する

 最近 Ubuntu 環境の構築SQL Server のインストールWindows からのリモート接続を行った.

 以前 SPSS から SQL Server に接続したのを思い出し,Windows 環境の SPSS から Ubuntu 上の SQL Server にリモート接続できないか試してみたら,あっさり接続できたので書き記す.

“SPSS から Ubuntu 上の SQL Server に接続する” の続きを読む

SPSSからSQL Serverに接続する

 SQL Server に蓄積したデータを SPSS で解析する際には一旦 EXCEL のワークシートに出力していたのだが,この一手間が面倒になってきた.SPSS のメニューを眺めていると「データベースを開く」というメニューがある.これを使えないか?と試行錯誤した結果を備忘録として記す.

“SPSSからSQL Serverに接続する” の続きを読む