Posts on this page


June 27, 2015

ExcelとAccessのデータ操作の対応

ExcelとAccessは、同じデータ操作をするにも、ソフトの操作方法は大きく異なります。それは、ソフトウェアの設計思想が違うから当然のことですが、ここでは、主にExcelでこんな操作をするなら、Accessで行った方が楽かもしれない、というデータ操作をリストアップしました。

データのマッチング

同じID、名前、カテゴリなどに属するデータを別の表から検索してきて、手元の表に統合する

Excel:
- VLOOKUP、HLOOKUPまたはINDEX(..., MATCH(...))で、ゴリゴリと式を書いて処理
- 複数のテーブルをつなげたり、テーブルAからテーブルBを参照して、そのテーブルBの値でテーブルCを参照する、といった処理は式が複雑になる

Access:
- 結合線(あるいはSQL文でJOIN)により、データをマッチング
- マウスを数回クリックする操作だけで設定できる

条件にあったデータだけ処理

特定の条件を満たすデータだけに計算処理を行い、合計、平均、個数を求める

Excel:
- COUNTIF、SUMIF、AVERAGEIFを使って処理
- 条件が複数になる場合は、COUNTIFS, SUMIFS, AVERAGEIFSといった関数もあるが、式が複雑で見やすいとはいいにくい

Access:
- 抽出条件、Group Byおよび集計用関数で処理
- 相当多数の条件であっても、GUIで確認しながら簡単に入力できる

データを並べ替える

データを何かの基準に沿って並べ替えて表示する

Excel:
- ソート機能を使う
- フィルタ機能を使う

Access:
- データシートビューのフィルタ機能
- または、クエリの並べ替え機能


June 18, 2015

ExcelにMAXIF関数、MINIF関数が無い、が、配列数式を使えば解決できる

COUNTIF関数、SUMIF関数、AVERAGEIF関数などは、エクセルで小規模なデータベースの集計をする際にはとても便利な関数ですが、残念なことにMAXIF関数やMINIF関数はOffice 2010時点では実装されていません。とはいえ、作業セルを使ったような解決法は、スマートともいえないですし、ユーザー定義関数を用いるのは、マクロが許されない環境では難しいでしょう。

そこで、ひとつの解決方法として使えるのが、MAX関数とIF関数を組み合わせた配列数式です。IF関数に配列を渡すことで、条件に見合う値のみからなる配列を取得し、その配列をMAX関数に渡すことでMAXIF関数に相当する処理が実行できます。

詳細については今後記載予定。

June 13, 2015

ExcelのMATCH関数が原因不明のエラーを返す、上手く動かないに確認すること

ちょっとしたデータベースをExcelで処理する場合の定番の関数といえば、VLOOKUP、HLOOKUPや、MATCHとINDEXの組合せとなってくるわけですが、最近ちょっとハマッた事例があったのでメモにしておきます。

1. MATCH関数の第3引数のmatch_typeが省略されていないか

通常、IDを検索するような場合では0を指定すると思うのですが、厄介なのが指定を忘れて引数を省略してしまった場合であっても、関数としては機能してしまう場合がある、ということです。
本来、検索されるはずのIDがMATCHで検索されない場合、期待される結果が返ってこない場合には、match_typeのフラグを確認する必要がありそうです。

2. 数字のみで構成されるIDがMATCHで上手く検索することができないケースではないか

IDには、00098、11022など数字のみで構成されるものも良くあるかと思うのですが、普通にExcel上の検索機能では検索されるにも関わらず、MATCHで検索されない場合があります。どうもセルの書式の自動認識が悪さをしているようです、コピーして値のみ貼り付け、や、セルの書式設定から文字列を指定したところで、問題は解決されません。
解決方法の一つが、TEXT関数を使って強制的にセルの値をフォーマットしてやることです。たとえば、A1に00010と書いてあったとして、B1に=TEXT(A1, "00000")と入力してやれば、強制的に5桁のテキストからなるIDであることをExcelに認識させることができます。
この処理を行い、B列についてMATCH関数を適用すると問題なく検索できるかと思います。その場合も、上の項目で書きましたが第3引数のmatch_type=0の設定をお忘れなく。

January 30, 2015

Excelで文字列やデータのソート、順序の逆転をするには

様々なデータ処理をエクセルで行うときに、データの順序を変えたいときがあります。ここでは、色々なソート方法についてメモします。

1. データを値の順に並べたい
ソート機能を使いましょう。複数条件での並べ替えにも対応した強力なソート機能が初めから付いています。

2. データを指定した順序に並べたい
カスタムソート機能を使いましょう。指定したリスト通りの順番に値を並べ替えることができます。

3. データの順序を逆転したい
作業列を作って1,2,3...と現在の位置を入力してそれを基準に並べ替えましょう。

4. VBAでデータの順序を逆転したい
ソート機能のマクロ記録をして活用する、あるいは、Arrayに格納してから反転させる、それとも、ArrayListオブジェクトを使うと出来そうですね。

5. 文字列を反転させたい
ワークシート関数だけでは、一文字ずつ作業セルに切り出してから反転する方法がありますが、スマートとは言えません。VBAを使うとStrReverse関数があるので簡単に処理できます。
ワークシート関数のように使いたい場合はユーザー定義関数とすればよいでしょう。

January 29, 2015

VBAのコードをコピーしたのですがWindows 8で動きません、というときは

VBAやVBScriptのコードは様々なところにサンプルがあるので、何かしたいと思ったときにコピーするだけで必要な機能を実現できることも多いのですが、これらのプログラミング言語は単体で機能するというよりも、他のアプリケーションやオブジェクトを使用してこそ真価が発揮されるという性質がある故に、64bit版と32bit版では色々と違いがあるようです。

1. VBAではホストアプリケーションにより使えるコマンドと使えないオブジェクトがある
別記事で書きましたが、例えば、ScriptingControlは64bit環境では使えません。

2. WIN APIも異なっており、同じ式では呼び出せません。
例えば、urlmonを呼び出す場合でも、書き方を変えなえればなりません。

32 bit
Private Declare Function URLDownloadToFile Lib "urlmon" _ 
Alias "URLDownloadToFileA" (ByVal pCaller As Long, _ 
ByVal szURL As String, ByVal szFileName As String, _ 
ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long

64 bit
Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" _
    Alias "URLDownloadToFileA" _
    (ByRef pCaller As LongPtr, _
     ByVal szURL As String, _
     ByVal szFileName As String, _
     ByVal dwReserve As Long, _
     ByRef lpfnCB As LongPtr) _
As LongPtr

Web上の情報は時間が古いものでも残っているために、解決法として書かれたコードがどのソフトウエア環境を前提としているのか、を良く確かめないと思わぬエラーの続発に悩まされることにななりそうです。
なお、2015年現在、既にWindows 8への移行は確実に進んでおり、Windows 10の時代になればほぼ64bit環境になるとは思うのですが、OSが大きく変化するときは今後も同じような問題が発生することはあるでしょう。言語仕様が変わらないだけに、安易に同じように書てしまってハマらないようにしたいものです。

Excel VBAで指定したフォルダに株価データを自動でダウンロードして保存する

エクセルのワークシートに入力されたティッカーシンボルを使って、Yahoo financeから株価のデータをダウンロードして所定のフォルダにcsvとして格納するためのコードです。

制限事項:
ダウンロード先のURIは直接アクセスできるファイル名になっていること。例えば、http://hoge.com/file.csvにアクセスしたらそのままファイルのダウンロードが始まる状態であること。
そうでない場合は、IEオブジェクト等ブラウザに読み込ませて最終的なダウンロード先に転送させるようにする必要があります。

yahoo financeはurlがとても分かりやすい構造になっており、パラメータをつなげてWinHTTPでGETするだけで、データを得ることができます。なお、エラー処理等はしていませんので、所定の動作と違うこと(途中でキャンセルボタンを押すなど)をするとマクロがエラーを起こして止まります。 回収したcsvはこちらのコードを使ってブックに読み込めます。


Sub inquire_historical_data()
    Dim tickerSymbols
    Set tickerSymbols = Range(Range("A2"), Range("A1").End(xlDown))
    
    Dim csvUrl, myFile, retVal
    
    Dim Shell, myPath
    Set Shell = CreateObject("Shell.Application")
    Set myPath = Shell.BrowseForFolder(&O0, "Select folder to save files:", &H1 + &H10, "C:\")
    If Not myPath Is Nothing Then dataFolder = myPath.Items.Item.Path
    Set Shell = Nothing
    Set myPath = Nothing
    
    Set http = CreateObject("WinHttp.WinHttpRequest.5.1")
    
    For Each tickersymbol In tickerSymbols
        With tickersymbol
            csvUrl = "http://real-chart.finance.yahoo.com/table.csv?s=" + .Text _
            + "&a=" + .Offset(0, 1).Text + "&b=" + .Offset(0, 2).Text + "&c=" + .Offset(0, 3).Text _
            + "&d=" + .Offset(0, 4).Text + "&e=" + .Offset(0, 5).Text + "&f=" + .Offset(0, 6).Text + "&g=m&ignore=.csv"
        End With
        
        With http
            .Open "GET", csvUrl, False
            .Send
        End With
    
       With CreateObject("ADODB.Stream")
           .Type = 1 'adTypeBinary
           .Open
           .Write http.responseBody
           .saveToFile dataFolder + "\" + tickersymbol.Value + ".csv", 2 '1:adSaveCreateNotExist, 2:adSaveCreateOverWrite
           .Close
       End With
       
    Next
    
    MsgBox "Data download completed. Files are save to: " + dataFolder
End Sub

ExcelのCORREL関数等、配列を引数とする関数では

Excelには様々な便利なワークシート関数があるのですが、correl関数をrow()関数、column()関数と合わせて使用したときに詰まったのでメモします。

以下の式はエラーを返します。
=CORREL(A2:A6, OFFSET(A2, 0, column(), 5,1)

上記の式をB1セルに入力した場合、A2:A6の配列と、B2:B6の配列の相関係数が返ってくることを期待するわけですが、エクセルはエラーを返します。原因が分からず散々調べた上で以下のような記事に行き当たりました。

 Re: Dynamic Correl() formula - works with match(), fails with row() and column()
http://www.excelforum.com/excel-formulas-and-functions/1057831-dynamic-correl-formula-works-with-match-fails-with-row-and-column.html
上の記事の参照先:
http://excelxor.com/2014/08/25/row-vs-rows-for-consecutive-integer-generation/

なるほど、row()関数やcolumn()関数の返り値はなんと、配列だったのですね。全く意識せずに使っていたため、今回のようなエラーに遭遇したわけです。通常、A1セルに
=ROW()
と入力すれば1行目ですので1が返ってくるので、返り値は当然のように整数だと思い込んでいたのですが、実際には、エクセルが内部で配列である返り値{1}を数値である1に自動変換した上で1が表示されていた訳です。

したがって解法としては、上記の相関係数の例ですと、
=CORREL(A2:A6, OFFSET(A2, 0, columns($A:B), 5,1)
となります。
columnsは列数を数値として、rowsは行数を数値として返すので、上記のようにrow、column関数を使った場合のエラーが発生しなくなるのですね。

ちなみに、配列が返った時にどうしてエラーになるのかは参照先の記事に詳しく書かれているのですが、要は

=CORREL(A2:A6, OFFSET(A2, 0, column(), 5,1)
=CORREL(A2:A6, OFFSET(A2, 0, {2}, 5,1))

となって、OFFSET関数の第3引数は数値でなければならないのに{2}と配列になっているためにエラーが起きてしまいます。
興味深いのはsum関数の場合は同様な表記でもエラーなく動いてしまうことです。配列も整数も取れるような関数と配列しか取れない関数では内部的な実装が違うのでしょうか。

結論としては、row(),column()は配列が返り値として欲しい場合に限定し、原則としてrows()、columns()を使ったほうが安全です、ということですね。

January 25, 2015

名前を定義してExcelで疑似関数を作成するには

エクセルのワークシート関数は様々な処理を比較的高速に実行でき、組み合わせ次第で複雑な処理も可能なのですが、一方で、変数が使えずタブによるインデントやスペースを使った整形もできないため、長い式になると見た目が分かりにくくなる、という欠点もあります。

これを解決する手段としては、王道はVBAエディタを使ってマクロを定義してしまうことなのですが、名前を定義して疑似関数を作成するという方法もあります。名前定義の優れている点は、マクロとはちがって通常のワークブックとして保存されるため、エンドユーザーにセキュリティ上の懸念が生じないということです。一方で、関数機能がセルから名前マネージャに追い出されるため、情報が分散するため、式の検証が若干面倒になるという点でしょう(それでもトレース機能は名前定義で参照されているセルについても追いかけてくれる…これは予想外に優秀。)。

ここでは一例として、A1セルに含まれる文字列の中から、最後の半角空白文字を探すし、空白文字以降を切り出す例を挙げます。
=RIGHT(A1,LEN(A1)-FIND("/",SUBSTITUTE(A1," ","/",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

同じことを名前定義を使うと以下のように書くことができます(一例です)。
=RIGHT(A1, FINDFROMLAST)

ここでは、疑似関数FINDFROMLASTを名前として定義します。具体的な方法としては、B1セルを選択した状態で数式リボンの名前を定義をクリックし、名前定義ウィンドウを表示します。 定義する名前をFINDFROMLASTとし、参照先に以下のように入力します。
=LEN(A1)-FIND("/",SUBSTITUTE(A1," ","/",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1

ここで、注意すべき点としては、上記の式は今回のケースではB1セルを選択して、名前を定義する場合です。さらに、式中のすべてのセル参照がA1といった形で相対参照になっていることに注意してください。名前の定義として、相対参照で記述すると定義したセルとは異なるセルで名前を使った場合に、相対参照が維持される点が、この方法のキモです。

さて、定義した疑似関数FINDFROMLASTの使い方ですが、適当なセルに半角空白を含む文字を入力します。そして、その右隣のセルに
=FINDFROMLAST
と入力してみると…最後に見つかった空白の位置が返ってきますね。

なお、同じことをVBAで関数定義したい場合は→Excelで文字列を最後から検索し、最後にヒットする位置を返すには


January 22, 2015

Excelで文字列を最後から検索し、最後にヒットする位置を返すには

テキストを処理しているときに、文字列中である文字が最後に現れる位置を検索したいと思ったのですが、ExcelにはJavascriptでいうところのlastIndexOfにあたる関数が無いようです。そこで、web上を調べると、メジャーな方法としては、以下のようにワークシート関数であるFIND、LEN、SUBSTITUTEを組み合わせた方法が紹介されています。
=FIND(sub_text,SUBSTITUTE(within_text,find_text,sub_text,LEN(within_text)-LEN(SUBSTITUTE(within_text,find_text,""))))
find_text:検索する文字
within_text:検索対象の文字列
sub_text:最後に現れるfind_textを一時的に置き換えるための文字(within_textに含まれない文字で、かつ、find_textと異なる文字)
この処理は中々トリッキーで、SUBSTITUTE関数が、対象文字をすべて置き換える、あるいは、n個目のヒットのみ置き換える、という動作をすることを利用しています。まず、SUBSTITUTEですべてのfind_textを消去し、文字数をLEN関数で確認することで、find_textの個数を確認します。そして、最後にあるfind_textをsub_textに置き換え、最後にFIND関数でsub_textの位置を探しているわけです。
ワークシート関数のみで処理を実現しているという点は素晴らしいかもしれませんが、こうして日本語で書き下してみても、あまりすっきりとした処理方法とは到底言えません。そこで別の方法を考えてみます。
  1. 名前を定義して左に隣接するセルから、特定の文字が最後に現れる位置を返すようにする
  2. 上記のワークシート関数をVBAでユーザー定義関数とする
  3. 初めからVBAの機能を活かしたシンプルなユーザー定義関数を考える
いずれの方法でも、セルに直接数式を書くよりはシンプルになるのですが、
1の方法では任意のセルで実行できず、使い勝手が悪い、
2の方法ではVBAを使うのに結局、処理の中身は分かりにくいままです。
そこで、3の方法として以下のように関数を定義することにしました(セキュリティの問題もあるしマクロは現実的じゃない、場合の選択肢→名前を定義する)。VBAエディタを開き、標準モジュールを追加して以下のように記載します。
Function FINDFROMLAST(find_text As String, within_text As String) As Integer
    within_text = StrReverse(within_text)
    FINDFROMLAST = Len(within_text) - WorksheetFunction.Find(find_text, within_text) + 1
End Function
処理としては非常に単純で、まず、受け取ったwithin_text文字列をStrReverse関数で反転させます。反転した文字列からワークシート関数のFIND関数でfind_textを検索すると、FIND関数の仕様上、最初にヒットした位置(=元の文字列で後ろから数えた位置)が返ります。そこで、LEN関数で元の文字列の長さを求め、先ほどヒットした位置を引くと、元の文字列within_text中で最後にfind_textがヒットする位置を求めることができます。

使い方は、ただ単にセルに=FINDFROMLAST(find_text, within_text)と入力するだけで、FIND関数と同等の使い勝手で最後から文字列を検索することができます。

なお、ユーザー定義関数の弱点としては、マクロ有効ワークブックとして保存する必要があること、built-in関数のようにツールチップを表示できないため、関数の使い方を事前に説明を用意するか、コードを見て理解してもらう必要があるところです。ちなみに、Application.MacroOptionsオブジェクトを使うと関数ダイアログには説明文を追加できますが、残念ながらセル入力時のツールチップを設定することはできないようです。

January 16, 2015

64bitのWindowsでScriptControl objectを作成できない(msscript.ocxが存在しない)

VBAやVBScriptの便利な機能に他の言語を呼び出す機能があります。ScriptControlオブジェクトを作成し、それを使って例えばJavaScriptを実行できます(ました)。ウェブ上にも様々なコードの例が上がっていたり、regsvr32を使ってdllを再登録しなさい、だとか書いてあるわけですが、64bitのWindowsで実行しようとしてハマりました。

調べた結果、今のところの結論は以下の通り。
  • 64bit版のWindowsにはmsscript.ocxは存在しない。
  • ホストアプリケーションが32bitであれば、OSが64 bit版であっても、msscript.ocxが使える。
したがって、開発環境やコードを書き換えずに実行したい場合は以下の対応になります。
  • 32 bitのホストアプリケーションを使う。(OSは32 bitでも64 bitでもよい。
したがって、どうしても同じ手順でスクリプトを使いたいならば、OSとソフトウエアを慎重に選択するあるいは仮想環境を用意するなどの対応が必要になります。

なお、64bit版のホストアプリケーションしかない場合にどうするのか、ということになるんですが、今のところ回避方法は見つからず。