id:jack_sonic
ポイントあり1000ptベストアンサーあり
コンピュータ

Excel 2000 VBAでデータベースからテーブルを読み込んだシート（1行目にカラムの見出し付き,列数行数不定）において、

[1.]VBAだけによる完全制御でオートフィルタを使い、複数の条件をつけてレコードの抽出を行い、抽出されたレコードの件数を取得したいです。
（これを何度も条件を変えて繰り返します。オプションとして作業用シートに一時的に抽出データを出せるオプションがあるといいです）
（また、できればですが、特定列への値の条件の付け方について、対象列の列番指定を決め打ちするのではなく、WorksheetFunction.Match()関数で列名から列番を検出することが可能ならばそうしたいです）
[2.]また、データベースの表を読み込んだ、「列数,行数不定」のシートの内容をテキストファイルに出力したり、逆に読み込んだりしたいです。

プログラミングの知識やデータベース/SQL中心にVBAの経験はあるのですが、SQLで直接叩く他にエラーチェック用に一度レコード一覧をワークシートに落として使う選択肢を持たないといけないので、他の方のプログラミングも見せて頂きたいです。よろしくお願いいたします。1000ptで特に参考になった回答の方には更にポイント送信をさせて頂きます。

回答の条件
  • 1人5回まで
  • 登録：
  • 終了：2011/09/22 07:35:02
※ 有料アンケート・ポイント付き質問機能は2023年2月28日に終了しました。

ベストアンサー

id:Mook No.2

回答回数1314ベストアンサー獲得回数393

ポイント333pt

一度却下になった案ですが、アイデアということでの提言です。


SQL はお使いになれるようなので、やはりDBから読込んだデータの保存先をCSV で

はなく MDB へ書き込んではどうでしょうか。

MDB は Access がないと使えないと思われる方も多いですが、ADO やDAO を使用すれば、

実は Office すらなくても使用することができます。

http://www.f3.dion.ne.jp/~element/msaccess/AcTipsGnrHowToUseJetWithoutMSAccess.html

http://www.nobotan.net/cgi-bin/sfs6_diary/sfs6_diary.cgi?action=cat&cat=5


下の方に、EXCEL で条件を記述し SQL で抽出する例が出ていますが、MDB と ADO

を使用するメリットは、コメントにも書きましたが SQL でデータの操作ができる

点です。


もし CSV への書き出しにこだわるようでしたら、コマンドベースのツールになりますが、

MicroSoftが無償提供しているLogParser をお勧めします。

これも便利なツールで、コマンドを使用して SQL で CSV からデータ抽出を行うことができます。

http://technet.microsoft.com/ja-jp/scriptcenter/dd919274

http://technet.microsoft.com/ja-jp/scriptcenter/ff191270.aspx

今回は使わないかもしれませんが、出力としてテキストだけでなくグラフ出力が

出来たりと、優れものです。


EXCEL の機能を使って解析もできますが、VBA の Shell でコマンドを呼び出すと短いコードで

処理をすることができますので、これも検討されてはどうでしょうか。


いずれにせよ DB からのデータ抽出ですから、自分で一からツールを作るのではなく、うまく

SQLへのラッピングをする工夫をした方が、汎用性があるように思います。

id:jack_sonic

ありがとうございます。

JETなどのある程度のライブラリが入ってれば

ExcelVBAからもmdb使えるんですね。

2011/09/21 19:35:32

その他の回答（2件）

id:taknt No.1

回答回数13539ベストアンサー獲得回数1198

ポイント334pt

オートフィルタで表示された件数のカウントです。

Sub 表示件数カウント()
    '表示されている行数をカウント。見出しを除く場合は －１する。
    d = 0
    For Each c In AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible)
        
        d = d + 1
    Next c
End Sub
id:jack_sonic

ありがとうございます。

オートフィルタは条件３つだけということは間違いだったとのことですが、

ANDとORを組み合わせてパラメータには変数を使う条件でカウントしたりするのにはあまり向いていないでしょうか？

普通にプログラムして行ループでレコードのフィールド値を条件にかけて

カウントすることも考えています。

（同時にデータエラーの検査を含めたり）

また、表を展開したシートのテキスト入出力ですが、

通常のInput Outputか、あるいはFileSystemObjectを使い、

カテゴリ名や日付情報などで識別し、いちいちダイアログなど出さないIOも、

普通にプログラムすれば済む問題かもしれませんね。

2011/09/15 19:45:10
id:Mook No.2

回答回数1314ベストアンサー獲得回数393ここでベストアンサー

ポイント333pt

一度却下になった案ですが、アイデアということでの提言です。


SQL はお使いになれるようなので、やはりDBから読込んだデータの保存先をCSV で

はなく MDB へ書き込んではどうでしょうか。

MDB は Access がないと使えないと思われる方も多いですが、ADO やDAO を使用すれば、

実は Office すらなくても使用することができます。

http://www.f3.dion.ne.jp/~element/msaccess/AcTipsGnrHowToUseJetWithoutMSAccess.html

http://www.nobotan.net/cgi-bin/sfs6_diary/sfs6_diary.cgi?action=cat&cat=5


下の方に、EXCEL で条件を記述し SQL で抽出する例が出ていますが、MDB と ADO

を使用するメリットは、コメントにも書きましたが SQL でデータの操作ができる

点です。


もし CSV への書き出しにこだわるようでしたら、コマンドベースのツールになりますが、

MicroSoftが無償提供しているLogParser をお勧めします。

これも便利なツールで、コマンドを使用して SQL で CSV からデータ抽出を行うことができます。

http://technet.microsoft.com/ja-jp/scriptcenter/dd919274

http://technet.microsoft.com/ja-jp/scriptcenter/ff191270.aspx

今回は使わないかもしれませんが、出力としてテキストだけでなくグラフ出力が

出来たりと、優れものです。


EXCEL の機能を使って解析もできますが、VBA の Shell でコマンドを呼び出すと短いコードで

処理をすることができますので、これも検討されてはどうでしょうか。


いずれにせよ DB からのデータ抽出ですから、自分で一からツールを作るのではなく、うまく

SQLへのラッピングをする工夫をした方が、汎用性があるように思います。

id:jack_sonic

ありがとうございます。

JETなどのある程度のライブラリが入ってれば

ExcelVBAからもmdb使えるんですね。

2011/09/21 19:35:32
id:nikodesu No.3

回答回数1025ベストアンサー獲得回数39

ポイント333pt

Excel使うより、これを使ったほうがずっと使いやすいとおもうのですが？

楽しいので一度試してみればいかがでしょう？

http://www.filemaker.co.jp/

id:jack_sonic

ありがとうございます。

別件での個人趣味的にはいいのですが、

今回のは既存の環境に依存していて

動作環境を変えることは不可能なので

そこだけはご理解下さい。

2011/09/21 19:39:55

コメント（20件)

  • id:jack_sonic
    じゃっくそにっく 2011/09/15 07:44:42
    ベストアンサー賞を出すことを保証します。
    プログラミングには相応の代価が払われるべきと思っていますので、
    私にとって特に実用可能なコードであれば1000pt以上追加送信をさせて頂きます。

  • id:taknt
    きゃづみぃ 2011/09/15 09:36:29
    オートフィルタなんて 条件３つしかつけられないし、単に 非表示にするだけだから
    あまり意味ないよな。

    あと
    ＞[2.]また、データベースの表を読み込んだ、「列数,行数不定」のシートの内容をテキストファイルに出力したり、逆に読み込んだりしたいです。

    これが よくわからんが、データベースの表を読み込むって どうやって？
    対象とするＤＢは？
    ODBCを使えばいいの？
    シートに読み込んだら、それを csvで保存すれば
    ＞テキストファイルに出力したり
    は ＯＫ？
  • id:Mook
    Mook 2011/09/15 09:54:10
    対象データベースは何でしょうか。
    また、取得先のテーブルは単一テーブルですか？
  • id:jack_sonic
    じゃっくそにっく 2011/09/15 12:56:44
    説明不足ですみません。
    ODBCでのOracleデータベースへの接続と、単一テーブルではなく
    左外部結合と条件を使ったクエリ結果表をシートに展開するところは
    実装できています。
    しかし表を可視覚化しつつさらに条件でカウントしたりエラー検出があるのでオートフィルタを
    考えましたが、
    オートフィルタが三つしか条件を指定できないというのは驚きました。
    自分で展開したシートを走査しながら条件をマッチさせてカウントさせたほうがいいですね。
    全部SQLでカウントしてしまいたいところなんですが、シートで見れるようにしなければならず‥
    テキストデータなのでテキストファイルでいいかと思っています。
  • id:taknt
    きゃづみぃ 2011/09/15 13:03:47
    ＞オートフィルタが三つしか条件を指定できないというのは驚きました。

    あ、すみません。

    私の勘違いでした。失礼しました。
  • id:jack_sonic
    じゃっくそにっく 2011/09/15 21:08:16
    コメントで質問を頂いて考えたら
    自分でプログラミングできそうなのですが、
    汎用性・柔軟性の高い優れたコードは今も歓迎です。
    （せっかく1000pt質問を立てたのもありますので）
  • id:Mook
    Mook 2011/09/15 23:45:40
    データの状況もわからないのですが、何より今回の要望の主眼が何なのかが不明です。
    オートフィルタでできない何かの付加的な機能を求めているのか、単に操作の簡略化を求めているのでしょうか。

    また、既にデータベースからデータを取得した後の処理を目的としているのでしょうか？
    それとも条件を指定してからデータベースからデータを取得することを目的としているのでしょうか。

    条件の指定は、数値なのか文字なのでしょうか。数値なら範囲、文字なら正規表現の対応になるかと思いますが、
    カラムはすべてシート上にあって、それに対するフィルタということでしょうか。

    ［２］はテキストファイルといっても、読込みたいという要望があるなら、CSV や タブ区切りなど、
    フォーマットの指定は必要だと思います。

    たとえば、下記のようなテーブルがあって
    ProductName
    Price
    ReleaseDate
    Memo
    Excel 上も A1~D１ に同名のタイトルがあって、A2:D2 に
    　　　　[A]　　　　　[B] 　　　　[C] 　　　　　[D]
    [１]　ProductName 　Price 　　ReleaseDate 　　Memo
    [２] 　%Phone% 　　>30000 　　>2010/1/1 　　　 %白%

    のようにして実行したら、該当する条件のデータ件数とデータを３行目以降に出す
    といったイメージなのでしょうか。
  • id:jack_sonic
    じゃっくそにっく 2011/09/16 03:04:01
    最終的に、色々な条件に合うレコードのカウンティング、
    および視認を含めたエラーチェックが主旨なのですが、
    重要な点は、SQLでいっぺんに抽出＆カウントをするのではなく、
    まずゆるい条件のSQLである程度の量のレコード群の中間結果をExcelシート上に視認できる形で出し、
    そのシートに対して条件カウントやテキスト入出力といった処理をかけるという点です。
    データはテキストのみで、外部結合で結合したテーブルがそのままカラム名と
    一緒に中間結果シートに出ている感じです。

  • id:jack_sonic
    じゃっくそにっく 2011/09/16 03:15:27
    データベースに対する操作やクエリはこちらで全てできるので、
    DBの種類やSQL等の意識は全く必要ないです、
    というか他の処理もでやればできるといえばできるので
    そう言ってしまうとおしまいなのですが（＾＾；
    （スマートかどうかは別として）
    変な質問ですみません。
  • id:Mook
    Mook 2011/09/16 08:02:12
    うーん、となると今回の質問が何を求めているのかますます不明です。
    完全にEXCEL 上だけの処理を求めているのであれば、どのようなI/Fや
    処理を想定しているのでしょうか。

    最初のSQLでの抽出に漏れがないとして、あとはオートフィルタを適用
    するのだと思いますが標準のフィルタ機能では使いづらいということで
    しょうか。

    >重要な点は、SQLでいっぺんに抽出＆カウントをするのではなく、
    >まずゆるい条件のSQLである程度の量のレコード群の中間結果をExcelシート上に視認できる形で出し、
    この部分は既に実装されているので不要ということだと思いますが、

    >そのシートに対して条件カウントやテキスト入出力といった処理をかけるという点です。
    >データはテキストのみで、外部結合で結合したテーブルがそのままカラム名と
    >一緒に中間結果シートに出ている感じです。
    であれば、テキストの入出力だけが要望なのでしょうか。
    テキストへの入出力は EXCELに対するもので、DBに対する修正は想定していないと
    考えて良いのでしょうか。
    この程度なら標準機能の「ファイルに名前を付けて保存」で CSV を指定するレベルの
    マクロでできると思いますが、そのテキストファイルの使用目的はなんでしょうか。
    （一度CSVで保存してそれをnotepadで開いて、可否を判断できませんか？）

    いずれにせよ明確な要件定義ができなければ、希望するものは回答できそうもありません。
    ソフトを外注に出すつもりで（そこまで細かくできないかもしれませんが）、要求仕様を
    定義できませんか。
  • id:jack_sonic
    じゃっくそにっく 2011/09/16 20:55:35
    >Mookさん
    わかりにくい質問につきあってくださりありがとうございます。

    データベースに対しては、読み込みだけです。書き込みは一切行いません。
    リードオンリーです。

    オートフィルタですと、複雑な条件でのカウントや、
    フィールドデータのエラーチェックが
    難しそうなので、できればループで処理したいなと考えています。

    簡単な例でいえば、テーブルt1とテーブルt2をあるコードXで左外部結合したとして、
    t1.aとt2.bの値が一致しなければデータ入力ミスだということだが、
    そういった入力ミスパターンを検出しながら色んな区分条件によるカウントを行います。

    ここで、カウントを行うに際し、
    毎回データベースからシートに結合テーブルを読み込むのは時間と負荷がかかるので、
    一度抽出したら、
    "何年何月分_カテゴリ名_帳票種類名.csv"というような、規則的な名前で自動保存し、
    次回からは同じ条件のときは自動的にファイルから取り出すか、
    DBから読み直すかYesNoが選べたりするようにするつもりです。

    また、件数カウントを反映させる表に、別の手動で作成されたテキストファイルから、
    簡単なフォーマット：識別子と値　程度のものを同時に読み込むことも考えています。

    またまたわかりにくいですがお許しください。
    毎日あれこれ考えながら組んでいます。
  • id:Mook
    Mook 2011/09/16 22:58:16
    回答までたどり着けるかどうかわかりませんが、気長に要求仕様をまとめるよう
    コメントしたいと思います。

    まず、こちらが状況をイメージできない最大の原因は、データやワークフローが
    見えていないことに起因しています。

    じゃっくそにっく さんには当たりまえでも、回答者にとっては
    ・データ量はどのくらいなのか（EXCEL からの検索時間はどのくらいなのか）？
    ・データは一度登録されたら更新されないものなのか？
    ・指定月のデータは、先月以前は変更が発生しないものなのか？
    ・条件は年月、カテゴリ、帳票種類だけで、この単位で集計を行うのか？
    などがまったく初めての情報になります。

    上記を読む限りはデータはテキストである必要はなく、EXCEL の標準ファイルとして
    保存するので良いのではないでしょうか。

    >次回からは同じ条件のときは自動的にファイルから取り出すか、
    という機能を持つためには、検索した履歴と保存ファイルを管理する必要がありますね。
    ですから解析用のブックは、これらの管理に特化したものにして解析対象は別ブックに
    してしまっても良いと思います。

    そのうえで、まだ要望がわからないのは
    >オートフィルタですと、複雑な条件でのカウントや、
    >フィールドデータのエラーチェックが
    >難しそうなので、できればループで処理したいなと考えています。
    という部分ですね。
    失礼ながら、じゃっくそにっく さんも明確なイメージがないため、要望がまとまらない
    のではないかという気がします。

    「複雑な条件」というのは具体例が示せませんか？
    そのあたりが仕様作成の取っ掛りになるような気がします。
  • id:jack_sonic
    じゃっくそにっく 2011/09/17 19:09:49
    ＞Mookさん
    お世話になります。より柔軟性があり、
    不整合データの監視と、帳票のための正確なカウンティングが行える
    ものを目指しており、よりよい設計があれば作りながら取り入れようと思っています。

    仮に頼むような感じで言うとなりますと、
    SQLでできるようなことを、DBに対してではなく、
    クエリで呼び出し展開させたExcelシートに対して行える、
    柔軟・汎用的なコードがほしいです。

    例えばコード系のカラムに対しては、コード値が存在することや、現在有効であることのチェックを、WorksheetFunction.VLOOKUPでマスタシートを参照して調べたり、
    それをやりながら、さらに
    kubun='変数' AND (substr(YMD,1,4)>=変数1 AND substr(YMD,1,4) <= 変数2) AND (syubetu='1' OR syubetu='2') ...
    といった色々な条件をかませて、どこにカウントすればよいかの判断をし、
    ときにレコードを抽出して、新しいブックの新しいシートに出力して、
    マクロや外部リンクを含まない状態で他の人が見やすい状態で、
    データ状態の報告する構想です。
    カウンティングは、例えば地域別かつ区分１別かつ区分２別かつ区分３別ごとに
    分けて件数をカウントした帳票出力のような目的に使います。
  • id:Mook
    Mook 2011/09/17 20:47:41
    ようやく詳細な説明に至りましたね。ですがこれまでの説明にない マスタシートや様々な
    項目名が出てきました。

    マクロを書くことを考えれば、わかると思いますがこれらの項目が、ファイルやシート場に
    どのようにレイアウトされているかわからなければ、実際のコードを書くことができません。

    ある程度複雑になるのであれば、EXCEL にこだわらず MDB ファイルにいったん条件に該当
    するレコードを抽出登録しておけば、「SQL のような」ではなくまさに SQL で検索できます。
  • id:jack_sonic
    じゃっくそにっく 2011/09/17 21:03:54
    環境の問題があるので、Excel2000と中間結果までのODBC,SQLが使えるのみです。
    具体的な項目の内容の詳細に至るような、
    完全なる外注をしたいわけではなく、参考になる
    汎用モジュール・汎用ファンクションの例を求めております。

  • id:jack_sonic
    じゃっくそにっく 2011/09/17 21:13:29
    引数の数が2個なのか3個なのか等といった細かいことは全く気にしませんし、
    参考にはしてもそのまま使うことはありえなくて、
    使うとしても自分でかなりの拡張をしますので、堅く考えず気楽にご回答ください。
  • id:jack_sonic
    じゃっくそにっく 2011/09/17 21:27:37
    単機能の部品だけでもあれば改造・拡張・組み合わせをできるので
    （というかそういうことが最も得意とするところなので）
    極めて部品的で構いません。
    適当な部品的な（完全な部品である必要なし）ものがたくさんあると自分で
    いろいろいじれるので助かります。
    （原型をとどめるかどうかは別として、アイデアになるので）
  • id:Mook
    Mook 2011/09/17 22:19:18
    あまり具体論になりそうもないので、アイデアレベルのコメントです。

    データベースクエリはご存知でしょうか。
    http://www.eurus.dti.ne.jp/~yoneyama/Excel/filter3.htm
    のように、条件をセル上に記述してデータベース（EXCEL Book も可）から該当レコードを
    抽出し指定先（シート、セル位置）へ書き出す機能です。

    下の方にVBAでの応用もありますから、このあたりのやり方を参考にしてはどうでしょうか。
  • id:jack_sonic
    じゃっくそにっく 2011/09/19 19:26:42
    >Mookさん
    これは面白い仕組みですね。前のコメントの中の表で、
    なぜ条件と一緒になった表を書かれていたのか
    わかりました。ありがとうございます。
    こちらだけでなくたまに回答欄のほうにも書いてくだされば
    ポイント配分ができますのでご遠慮なくどうぞ。
    単機能部品でも小出しでも構いません。
  • id:Mook
    Mook 2011/09/21 02:10:10
    ではお言葉に甘えて、まったく趣旨から外れるかもしれませんが
    アイデアレベルの回答です。

    回答のリンク先にADO の具体例が記載されていますか、不明な点等あれば
    別途回答いたします。

この質問への反応（ブックマークコメント）

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

回答リクエストを送信したユーザーはいません
ユーザー登録をして使いはじめる!無料
すでに登録されている方はこちらからログイン
この質問に含まれるキーワード
知りたいことを検索してみよう
▲ページトップへ