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の設定をお忘れなく。