エクセルとアクセスについてお伺いします。

質問

エクセルとアクセスについてお伺いします。

エクセルのA列に
AAA￥BBB￥001
AAA￥BBB￥002
AAA￥BBB￥003
DDDD￥EEE￥001
DDDD￥EEE￥002
DDDD￥EEE￥003
といったデータが続いているとします。
２つめの￥までのデータが同じものは同一とみなし、一番上のみ残して
あとは消去するというマクロを作りました。

Ｂ列を作業セルその１とし、
=left(A1,FIND("￥",A1,FIND("￥",A1)+1))
の式で、AAA￥BBB￥を取り出し、
Ｃ列を作業セルその２とし、２行目から
=if(B2=B1,2,1)
の式で重複するデータの２つめ以降に「２」が立つようにして、
Ｃ列が２になる行を削除するというものです。

ただしエクセルだと65536件以上のデータには使えませんし、
なにより2000件を超えると計算が非常に遅くなり、
時によっては固まってしまいますから、
アクセスでの作業を考えているのですが、可能でしょうか。

回答No.1

MackyNo1 · ベストアンサー

＞ただしエクセルだと65536件以上のデータには使えませんし、
なにより2000件を超えると計算が非常に遅くなり、
時によっては固まってしまいますから、
アクセスでの作業を考えているのですが、可能でしょうか。

基本的に多くのセルに数式を入力すると、メモリーの消費量がが多くなりフリーズしたり、また計算速度にも影響が出ますので、避けるのが賢明です。

このようなケースなら、エクセルの一般機能を利用して処理されることをお勧めします。

たとえば、A列を選択し「データ」「区切り位置」で「カンマやタブ・・・」にチェックが入っていることを確認して「次へ」をクリックし「その他」にチェックを入れ「￥」を入力し「次へ」で抽出先をたとえばB1セルに指定して「完了」します。

これでB列とC列が等しいものを1種類に限定すれば良いので、B列とC列だけを選択して「データ」「フィルタ」「フィルタオプションの設定」で「重複するレコードは無視する」にチェックを入れてOKします。

このフィルタされたA列のデータを新規シートにコピー貼り付けすれば、ご希望のデータのみ抽出することができます。

お礼

まさに目から鱗でした。
おかげさまで希望のデータが非常に短期間に抽出することができました。
本当にありがとうございました。

回答No.8

layy

http://www.accessclub.jp/bbs2/0042/beginter13819.html
これ以外にも
ファイルパスとファイル名を分けるって
サンプルがあちこちあるので、参考に。

ついでに、
削除したい行を削除せずにマーク付けるだけしておけば
あとで並べ替えできますからそれだけでも効率よく処理が早いかもしれません。

お礼

私がしようとしていることは
「ファイルパスとファイル名を分ける」
ということなのですね。
何しろ素人なので、どういう質問をしたらよいのか、
どういうキーワードで検索をすればよいのかもわかりませんでした。

削除せずにマークを付けるというご意見も大変参考になりました。
ありがとうございました。

回答No.7

layy

実際の検証はしていないので参考レベルです。

ＦＩＮＤを２回行う（文字列を２回操作する）こと
そのものが計算を遅くしているかと思ってます。
だから、アクセスに変えたとしても、早くなるかどうか怪しいのでは？。

ＶＢＡでＡ列文字列を引き渡し文字数分操作、Ｃ列Ｄ列にもセットすることを考える。
案１）
「AAA￥BBB￥001」を（１ケタ目から２個目￥まで１文字ずつ検知していって）
「AAA￥BBB￥」をＣ列へ「001」をＤ列へ分ける
案２）
「AAA￥BBB￥001」を（１１ケタ目から逆に最初の￥まで検知していって）
「AAA￥BBB￥」をＣ列へ「001」をＤ列へ分ける
でどうだろうか、というところです。

コントロールブレイクはマスターしておくべきアルゴリズムですから
この際覚えておきましょう。

お礼

コントロールブレイクですね。
さっそく勉強したいと思います。
またお世話になることがあると思いますが、
宜しくお願いいたします。
ありがとうございました。

回答No.6

imogasi

>65536件以上のデータには使・・
エクセルの公の質問で、バージョンを書いてないのは非常識。
＞アクセスでの作業を考えているのですが
可能でしょう。
＞マクロを作りました。
といって置きながら、その後のやり方は関数式をセルに入れるやり方ではないですか。
ロジックを工夫すれば良いのでは。やり方を帰るのです。質問者のやり方はまず皆が思いつくものでは無いと思う。
ーー
私なら
（１）データをA列でソート
（２）最初行は別として、「前の行文字列」として、２番目の￥までの文字列を保持する（事業に引き渡す、伝える）。
それと今の行の２番目の￥までと比べ同じなら何もしない。
（３）変わったら別シートに今のものを書き出し。
「前の行の文字列」を今の行のもので改める。
（３）終わりの行まで繰り返す。
VBAならInstr関数で文字列内の￥は捉えられる。２つ目の￥はやはり２階適用となろう。
これは昔から行われているコントロール・ブレイクという手法なのだ。
余分なメモリを使わず（溜め込まない）、発想も応用が広い。
エクセルのシートは、一種のデータのメモリへの溜め込みをしているのだが、エクセルなんてない時代から使われている。
ーー
＞あとは消去するというマクロを作りました。
イメージ的にはそうなのだが、別シートに書き出さない、と考える方が良い。発想を変えることでやさしくなるし、行削除は実行時間がかかり、ロジックが難しくなる原因になる。
ーーーーーーーーー
エクセルVBAの初心者らしいが、課題によっては、まだ勉強の余地があるよ。
AccessVBAになるとさらに習得に時間がかかる。

お礼

バージョンも書かない非常識な質問で申し訳ありませんでした。
しかしあなたのような言い方をしなくても、他の方の
「エクセル2007ならできますよ。」というご教示で、
バージョンを書いていなかったことに気づき十分反省しました。
>エクセルＶＢＡの初心者らしいが
とお分かりになっているにもかかわらず、言葉尻を捕らえたり
初心者には理解しにくい回答をされる意地の悪い方だと思いました。
>質問者のやり方はまず皆が思いつくものでは無いと思う。
皆様のようには思いつくことができないから、
この場で質問をさせていただいているのです。

回答No.5

umazanpai

=left(A1,FIND("￥",A1,FIND("￥",A1)+1))
Access
でもLeftとInstrでできそうだけど
ご提示のデータならLeftとLenでも
集計クエリだけであっさりおわりそうです。

お礼

集計クエリも勉強してみようと思います。
あっさり終わりますかね、期待しちゃいます。
ありがとうございました。

回答No.4

yama1718

重複した行を削除した結果の行数が65536件以内に収まるならEXCELでも可能ですよ。
その元のデータはどこから読ませるのですか？
手入力？ＣＳＶ？他のワークシートからのコピペ？
要するにデータの読み取り時や貼り付け時にチェックして、重複していればその行をスキップするマクロを組めばいいでしょう。

新たにOffice2007や2010を購入する費用が無いと言うなら、フリーのOpenOffice v3.2.1のCalcなら扱える行数が1,048,576行に増えているので、これを使うのも手です。
ただし、処理速度はOfficeより遅いようです。

お礼

何をどのように質問したらよいのかもわからなかったもので、
バージョンも元データをどこから読ませるのかも書かずに
大変失礼いたしました。

実は元データは職員のパソコンの証跡でＣＳＶのデータです。
わが社では私有のパソコンや外部記録媒体の使用、
データの持ち出しを禁じているので、証跡を上司がチェックし、
セキュリティ事故を未然に防ぐという目的らしいです。

ＣＳＶそのままだと、単なるデータの羅列ですし、職員番号はＩＤとして
表示されるけど職員氏名は表示されず非常に見にくいものなので、
エクセルで綺麗な形の「管理簿」にしたいらしいのです。

ＣＳＶを出力するのは情報管理部門の者で、難しいことには詳しいようですが、
エクセルやアクセスなどのアプリケーションには疎いらしく、
エクセルの素人に毛が生えた程度の私に何とかしてくれと依頼がきたものです。
職員氏名を表示させるのは別シートに名簿を作らせvlookupで表示させる方法で
対処できたのですが、なにしろＤＶＤを１枚見ただけでも同じファイル名や、
ほぼ同じファイル名が何千件も出てしまい、
わずか５分の証跡で、２５００件あまりのデータとなってしまうので、
なんとか間引きできないかと困っていたものです。

情報足らずの質問に対しても丁寧に回答していただいて、大変感謝しております。
ありがとうございました。

回答No.3

bajutsu

アクセスは表計算ソフトではないので関数では無理です。

VBAを使えば可能ですが、逆にVBAを使えるのなら
アクセスにする必要もなくなっちゃうんじゃないでしょうか。

私なら、こういう処理は、VBScriptを使います。
VBAの親戚なので、VBAが扱えるのなら習得は早いと思います。

ついでに、Office 2007 以降は、65536行以上の行(104万8576行)を扱えたはずです。

お礼

バージョンも書かずに失礼いたしました。
VBScriptも勉強していきたいと思いましたので
またお世話になると思いますが、宜しくお願いいたします。

回答No.2

lv4u

>>アクセスでの作業を考えているのですが、可能でしょうか。

アクセスでは、ＶＢＡが使えますので、そのプログラム言語を使い、さらに、エクセルのデータをアクセスのテーブルに適切に入力すれば、可能だと思います。
ただし、表計算ソフトのエクセルとデータベースのアクセスは処理のデータ処理のやり方に大きな差があります。友人で、「俺はエクセルはつかえるけど、アクセスはがんばっても理解できなかった」という方がいたので、可能かどうかは、ご自身の能力によると思います。

そして、データ数が65536件を超える可能性があるなら、エクセルで処理できませんから、どうしてもアクセスを使うことになりますね。なお、Excel2007からは、最大行数が104万8576行に増えたので、時間がかかってもいいなら、これを使うという手もあるかもしれません。

ただ、処理内容からすれば、PerlとかRubyなどの無償のスクリプト言語で処理するほうが手軽かな？って気がします。でも、新たにPerlやRubyなどの言語を学ぶ必要がありますが。

ちなみに、私も、仕事で少ないデータ件数ならエクセルマクロで処理することもありますが、数万件を超え、百万件以上のデータを相手にする場合もあるので、その場合、Perlを使って処理しています。データをいじっているうちに、やりたいことが複雑になっていくことがあるのですが、そういう場合でも、Perl等を使っていれば、対応が容易です。

お礼

PerlもRubyも初めて聞きましたが、非常に興味を持ちました。
勉強してみようと思いますので、
またお世話になることがあると思いますが、
宜しくお願いいたします。
エクセルの質問に使用バージョンを書かずに失礼しました。
ありがとうございました。