MP3からTag情報を抽出する方法

データベースに登録するTag情報をどうやって楽曲から抽出するか。 旧版のExcel Playerでは以下の方法で取得

ShellオブジェクトのNamespaceメソッドでFolderオブジェクトを作成。
FolderオブジェクトのGetdetailsofメソッドでファイルのプロパティを取得

Office TANAKA - 番外編[MP3の「曲の長さ」を調べる]

Rebuild版作成に当たり調べてみたが、他に良さそうな手段が見当たらなかったので、引き続きGetdetailsofメソッドで取得してみる。

Sub Song_Property()
    Dim FSO As Object
    Dim objShell As Variant
    Dim objFolder As Variant
    Dim i As Long
    Dim Song As Variant
    
    Set FSO = CreateObject("Scripting.Filesystemobject")
    Set objShell = CreateObject("Shell.Application")
    Set objFolder = objShell.Namespace("G:\Music\Frost\Milliontown\")
    Song = FSO.getfile("G:\Music\Frost\Milliontown\01 Hyperventilate.mp3").Name
    For i = 0 To 1000
        Cells(i + 1, 1).Value = i
        
        'ファイルのプロパティ名を取得
        Cells(i + 1, 2).Value = objFolder.getdetailsof(objFolder.parsename(objFolder), i)
        
        'ファイルのプロパティ値を取得
        Cells(i + 1, 3).Value = objFolder.getdetailsof(objFolder.parsename(Song), i)
    Next i  
End Sub  
  

試しに1曲情報を取得してみると、僕の環境(Win2007・Excel2013)、音楽ファイル運用方では
・288種類のプロパティ
・上記の内36種類のプロパティに値有り

36種類のプロパティの内、Excel Playerに使えそうなのは以下の通り
ID プロパティ
14 アルバム
15 年
16 ジャンル
20 作成者(※アーティスト名)
21 タイトル(※曲名)
26 トラック番号
27 長さ(※再生時間)
180 パス
195 発行元(※発売レーベル)
220 作曲者

このプロパティをデータベースに設定することにする

Excel Playerの構成

Excle Playerの構成はExcelブック1つとデータベース1つの予定をしている。

Excel
全てのコードを記載
プレイリストの作成

DataBase
楽曲情報の管理

Excel Player使用の流れ

Excelで抽出した楽曲のTag情報をデータベースへ格納。
Excelから検索した楽曲情報をデータベースからExcelに転記。
・転記したExcel上でソート・削除等加工を行う。
・完成したリストを元に楽曲の再生を行う。

データベース使用の理由

現在パソコンに保存されている楽曲は18,100曲。
Excelだけで管理できなくはないが勉強のためにもデータベースを使用する。
使用するデータベースはAccessSQLiteを予定。
特に別のソフトをインストールする必要もなく、ファイルの移動が簡単であることが理由。
Accessとの連携はある程度勉強済みなので、出来そうであればSQLiteを使用することにする。

Excel Player Rebuildスタート~Excelで音楽を再生

10年以上前にExcelで音楽を再生するというマクロを作成したことがある。

つい最近になって、その事をふと思い出してPCを検索してみると

発掘された

しかしいざ使おうとすると現在の環境では使えないのか(当時はWindows XP+Excel2003、現在Windows7+Excel2013) 内部で壊れているのか、動かそうとするとエラー→直す→別のエラー と気持ちが萎えてきたので、勉強がてら1から作り直してみることにした。

こいつのリビルドをメインテーマにして、気長に勉強していこうと思う

空白の消し方

本日のご質問。 vlookup関数でちゃんと入力しているつもりなんだけど答えが正しく表示されません。

Vlookupが上手くいかない

f:id:ExcelLover:20171018215740j:plain
これは結論から言うと、参照するデータのコードの後ろに余白が入力されているため一致しなかったんですね。
別のシステムから出力されたデータをコピー&ペーストするとこういうことがあったりします。

ではどうすればVlookupで正しく答えが算出できるか

・Vlookupの引数にも余白を入力する
・参照するデータから余白を削除する

1つ目の方法でももちろん正しく答えが表示されますが、余白をいくつ入れればいいか毎回悩まないといけないので、2つ目の方法を使うほうが良いでしょう。

空白の消し方

空白の消し方と言ってもどうやればいいでしょうか?
ここでも2つの方法を紹介します。

・「置換」機能で空白を置き換える。
・関数で余分な空白を取り除く。

まずは置換で試してみます。

f:id:ExcelLover:20171018215906j:plain
空白を置き換えたいセルを選択して

Ctrl + H(コントロールキーを押しながらH)で置換機能を呼び出し

検索する文字列に今回の場合は「半角のスペース」を1つ入力。
置き換え後の文字列には何も入力せずそのまま。
「すべて置換」をクリック

これで全ての余白が無くなりました。

f:id:ExcelLover:20171018215959j:plain
しかしマスターデータを見てみると コードの前についていた「0」が全て消えています

場合によっては特に問題がないのかもしれませんが、あまりいい状態ではありません。

関数で空白を消す

次に関数で空白を取り除く方法を試してみます。
この目的で使えそうな関数は
・TRIM
・SUBSTITUTE
・REPLACE
などが考えられます。

今回は 「TRIM」 を関数を使ってみます。

TRIM 関数
各単語間のスペースは 1 つ残し、不要なスペースをすべて削除します。 TRIM 関数は、他のアプリケーションから読み込んだテキストに不要なスペースが含まれているときなどに使用します。

f:id:ExcelLover:20171018220049j:plain
一旦マスターテーブルの横の列で計算します。
引数に空白を削除したいセルを選択します。

この状態では空白が削除されているかわかりにくいし、
マスターテーブルの値が何も変わっていません。

f:id:ExcelLover:20171018220140j:plain
計算結果をコピーしてマスターテーブルに 値で貼り付け をします。

f:id:ExcelLover:20171018220158j:plain
これでvlookupが正しく動くようになりました。

外部システムから出力されたデータを再利用する時は、色々と書式の設定が必要になることが多いです。
色々な編集手段を持っていると効率的に変換ができるようになります。

今週見かけた気になるExcel記事

今週見かけた気になるExcel記事をリストアップ

・管理人のOfficeは2013のため影響はないけど、こんなのに遭遇してコード吹き飛んだら泣くな。 www.atmarkit.co.jp

・以前に百マス計算を作ってあげたらしばらく興味持ってやってくれてたんだよな。 www.atmarkit.co.jp

・今一番おもしろいExcel VBA関連のネタを提供してくれるt-homさん関連 thom.hateblo.jp

thom.hateblo.jp

thom.hateblo.jp

・実務ではよくあるCSV取込について

www.atmarkit.co.jp

・こちらも実務で使いみちがありそう

forest.watch.impress.co.jp

・IF関数とそれと組み合わせると便利な関数について

excel-master.net

・アンケートに活用すると集計が楽になりそう

excel-hack.com

・全然存在すら知らなかった関数。

ExcelのAGGREGATE関数の使い方|リストやデータベースの集計値を返す|エクセルハック

HELPの消し方

勝手にHELPが表示される

f:id:ExcelLover:20170902150414j:plain

ExcelのHELP、関数の引数を調べるのにとても便利でよく使います。
が、このHELPウィンドウ、呼び出してもいないのに(※正確には呼び出したつもりもない)勝手に表示されてイラッとくることも多々あります。
なんで出てくるかというと HELPを呼び出すショートカットキー「F1」をクリック しているからです。

HELPの消し方

F1で表示されたHELPを「×」をクリックして消すのは、恐らく今右手はキーボードの上にあると思われるので、マウスに手を伸ばさないといけないことになり手間やストレスが余計にかかっていまします。
ショートカットキーで消すのが早く・ストレスレスで消せます。

・ Alt + F4
・ Ctrl + F1(未確認ですがExcel2003まではAlt + F4ではなくこちら)
・ Alt + Space → c (Altを押しながらスペースを押して、メニュー表示されたら C )
f:id:ExcelLover:20170902150434j:plain

個人的にはF4が遠いので Alt + Space → c を使っています。
この2つのショートカットキーはHELP以外の、Excel以外のウィンドウも消せますので何かと重宝します。

※世の中にはHELPのショートカットキー自体を潰す方法もあるようですね

Excel HELP 無効 - Google 検索

月末日付の求め方・EOMONTH 関数

月末日付の求め方

請求書の締切日などを記載するために月末の日付を算出しないといけないことは多々あると思われます。
今までは次のような数式を使って求めていました。

= Date(2017,8+1,1)-1

f:id:ExcelLover:20170902141015j:plain

これは求めたい月末の翌日1日を作成してから1日マイナスして月末を求める方法です。

しかしExcelにはそれ専用の便利な関数が用意されていました。
それがEOMONTH 関数です。

EOMONTH 関数の使い方

EOMONTH(開始日、月)

開始日・・・必ず指定します。 起算日を表す日付を指定します。 日付は、DATE 関数を使って入力するか、他の数式または他の関数の結果を指定します。 たとえば、2008 年 5 月 23 日を入力する場合は、DATE(2008,5,23) を使用します。 日付を文字列として入力した場合、エラーが発生することがあります。

月・・・必ず指定します。 開始日から起算した月数を指定します。 “月” に正の数を指定すると起算日より後の日付を返し、負の数を指定すると起算日より前の日付を返します。
メモ 月に整数以外の値を指定すると、小数点以下は切り捨てられます。

たとえばEOMONTH(“2017/8/30",1)と入力すると2017年8月の1ヶ月後の月末、つまり2017年9月30日が表示されます。
「月」に0を入力すると「開始日」の月の月末、マイナスの数値を入力すると「月」の数字だけ遡った月の月末が表示されます。

EOMONTH(“2017/8/30",0)→2017/8/31
EOMONTH("2017/8/30",-1)→2017/7/31 f:id:ExcelLover:20170902141214j:plain