- ベストアンサー
Excel VBAでデータを自動処理したい
Excelで大量のデータ処理をしなくてはならないのですが、以下の処理をExcel VBAで自動処理できないでしょうか？ どなたかお知恵をお貸しください。 (1)A、B、C列からなるリストがあります。A,B列にはそれぞれオートフィルタが設定してあり、C列は空白です。A列、B列にそれぞれ条件を設定し、抽出したデータのC列（空白）に特定のデータを入力します。A列、B列2つの条件の組み合わせが100通りくらいあり、現在手動でオートフィルタを設定し、C列にデータを入力しております。例えばA,B列の条件の組み合わせと、それに対応するC列に入力するデータを表にしたテーブルを別に作り、A,B列の条件を自動に設定して、抽出し、C列にデータを自動に入力することを、テーブルの一番上の行から最後の行まで繰り返す、というようなことをVBAでExcelにしてもらいたいのです。自分でちゃんと勉強し、調べて、それでも分からなかったらお聞きするというのが筋だと思うのですが、今この仕事に追われて、時間がありません。（ほとんど毎日午前様です。）この仕事が片付いたら、じっくりVBAを勉強したいと思っております。どうぞよろしくお願いいたします。
- みんなの回答 （8）
- 専門家の回答
質問者が選んだベストアンサー
修正するとすれば 条件の列がＤ列で、「あ」と入力したい列がＧ列になり Ｋ列から始まる条件の表をなら Ｋ1に「条件1」と名前を定義してそのセルに「4」と入力（Ｄ列） Ｌ1に「条件2」と～ 「0」と入力（無視） Ｍ列はタイトルとデータ Ｇ列のデータ範囲に「分類」と名前を定義 ---------------------------この↓から--------------------------------- Sub 新データ処理1() '条件1のデータの上のセルに「条件1」と名前を定義し表の左から対応するデータまでの列数を入力 '条件2のデータの上のセルに「条件2」と名前を定義し表の左から対応するデータまでの列数を入力 '入力するデータの範囲に「分類」と名前を定義 'オートフィルタは表に空白列や行があると別の表と認識するので↑の列数が違ってくる場合があります '分かり図らい場合はA1のセルを選択後、オートフィルタをつけて▼を数えて下さい '条件を1つにしたい場合、「条件2」は0を入力すると無視します Dim i As Integer Dim j As Integer Dim a As Integer Dim b As Integer j = Range("条件1").Column a = Range("条件1").Value b = Range("条件2").Value Range("A1").Select Selection.AutoFilter For i = 2 To Range("条件1").CurrentRegion.Rows.Count Selection.AutoFilter Field:=a, Criteria1:=Cells(i, j), Operator:=xlAnd If b <> 0 Then Selection.AutoFilter Field:=b, Criteria1:=Cells(i, j + 1) Cells(i, j + 2).Select Selection.Copy Range("分類").Select ActiveSheet.Paste Next i Selection.AutoFilter End Sub
その他の回答 (7)
- april21
- ベストアンサー率42% (91/216)
>このプログラムをほかのリスト処理にも使用したいのですが、他のリストでは、条件が1つになり、 >条件の列がＤ列で、「あ」と入力したい列がＧ列になり、組合せの表もＫ列から始まるという風 分類用のシートにすれば修正しなくても結果は得られると思います。 他のリストからこのシートに必要な個所だけコピーして分類が出来たら他の シートに分類の列をコピーして貼り付けます。 Ｄ列をコピーしてＡ列に、Ｂ列をタイトルのみで（データは空）Ｆ列を「=」に します。 （Ｂ列も空＝Ｆ列も空 になりデータの全てが対照になる） Ｇ列をＣ列にコピー（ここは名前を定義してあるので「分類」にＧ列のデータ範囲を指定も可） Ｋ列をＥ列にコピー 結果が出たらリストに貼り付け、分類用のシートはそのまま閉じれば再使用の時 セルをクリアしたりしなくて済みます。（Ｆ列に「=」を入れておけば楽かも？） リストファイルにＶＢＡを入れなくて済むのでファイルサイズも少しは軽い？？？
- april21
- ベストアンサー率42% (91/216)
「我社の製品はこの二つの条件がどのように組み合わさっても、状態維持をクリア」 ↑だと10個、10個で100通りですね？ 「Ａ、Ｂ社2つの条件の組み合わせは100通りすべてあるわけではなく、こちらで必要な組み合わせだけですので」 ↑のどのような組み合わせでもというのに反してますね？ それで最初のご質問をもう一度読んでみたのですが・・・。 オートフィルタのＡ列の▼をクリックすると重複データを除いたデータが表示されますね？ 仮に「goo」というデータを1つ選択すると「goo」を含む行だけ抽出されます。 Ｂ列の▼をクリックすると「goo」を含む行のＢ列の重複しないデータ（excel、ＶＢＡ、kiroro）が表示されたとして Ａ列の▼をクリックした時に表示されるデータ「goo」をＡ列の条件として Ｂ列の▼をクリックした時に表示されるデータ（excel、ＶＢＡ、kiroro）をＢ列の条件として この時の組み合わせを Ｅ列 Ｆ列 Ｇ列 goo、excel、 あ goo、ＶＢＡ、 い goo、kiroro、 う ・ ・ ・ ・ セルに入力させて表を作り その表のＧ列にＣ列に入力するデータを手動であ、い、うと入力して Ａ列がgooで、かつ、Ｂ列がexcelならばＣ列に「あ」と自動で入力したいという事なのでしょうか？
補足
april21さん、早々にご返答いただきありがとうございます。また当方の説明があやふやで、april21さんを悩ませてしまったようで、申し訳ありません。 Ａ列の▼をクリックした時に表示されるデータ「goo」をＡ列の条件として Ｂ列の▼をクリックした時に表示されるデータ（excel、ＶＢＡ、kiroro）をＢ列の条件として この時の組み合わせを Ｅ列 Ｆ列 Ｇ列 goo、excel、 あ goo、ＶＢＡ、 い goo、kiroro、 う ・ ・ ・ ・ セルに入力させて表を作り その表のＧ列にＣ列に入力するデータを手動であ、い、うと入力して Ａ列がgooで、かつ、Ｂ列がexcelならばＣ列に「あ」と自動で入力したいという事なのでしょうか？ ⇒⇒⇒ということです。ただ、Ｅ列 Ｆ列 Ｇ列 goo、excel、 あ goo、ＶＢＡ、 い goo、kiroro、 う という組み合わせの表は、april21さんが最初に考えてくださったプログラムのように、あらかじめ手動入力で用意しておいて、この組合せが満たされているものだけＣ列に"あ"と書き込みをするという事が出来ればいいのです。april21さんが最初に作ってくださったプログラムを実行してみました。大成功でした。これで大満足でございます。私の至らぬ説明で大変ご迷惑をおかけしました。このプログラムをほかのリスト処理にも使用したいのですが、他のリストでは、条件が1つになり、条件の列がＤ列で、「あ」と入力したい列がＧ列になり、組合せの表もＫ列から始まるという風に、場合によって変わってしまいます。その際、april21さんが作ってくださったプログラムを適宜変更すればいろいろなリストで実行することが出来ると思うのですが、修正するのは難しいでしょうか？もしそれほど難しくないのであれば、どの部分を修正すればいいのかご指導いただけますでしょうか？もし難しいのであれば結構です。本当に無理ばかり申し上げてすみませんが、どうぞよろしくお願いいたします。
- april21
- ベストアンサー率42% (91/216)
＞条件が100個というのはE列の条件データが10個、F列の条件データが10個で、100通りほどの組み合わせがあるということなのです。早速会社で試してみますね。お忙しい中本当にありがとうございました。 それでは、各10個のデータから100通りの組み合わせを得るところから始めなくてはならないのでは？ ↓のでは手動で設定してしていた100通りの組み合わせを事前にＥ列とＦ列に入れて処理させる時に 使いまわすだけで良いのかと思ってましたので各10個のデータを入れても10通りしか処理出来ません。 100通りの組み合わせが何なのかも分かりませんし・・・。 何らかの規則性があれば自動で生成する事も出来ると思いますが今の段階では分からないの出きません。 それに100通りに対応するＣ列に入力させるデータも分かりませんし・・・。 Excel VBAでデータを自動処理したい（2）には数値データだけでしたが データって数値だけなのですか？ 条件のつけ方は2個の数値に合致するだけで良かったのですか？
補足
april21さん、私の質問の書き方が悪くて申し訳ありません。補足させていただきます。具体的に申し上げますと、この処理は、我社で製造しているある製品に対して2つの別の会社にテストを依頼しているのです。Ａ社には10通りの様々な条件でテストをしてもらっています。同様にＢ社にも別の10通りの条件でテストをしてもらっています。我社の製品はこの二つの条件がどのように組み合わさっても、状態維持をクリアできなくては販売できません。このテスト結果を表にまとめるための処理なのです。データは条件データもＣ列に入力させるデータも数値ではなく文字データです。そしてＡ、Ｂ社2つの条件の組み合わせは100通りすべてあるわけではなく、こちらで必要な組み合わせだけですので、april21さんが最初に考えてくださったように、事前に用意しておくことが出来ます。こんな状況なのですが、どうでしょうか？貴重なお時間をお取りしてしまって恐縮です。お時間があるとき、又木が向いたときなどにでもご返答くだされば感激ですが、どうぞ負担の感じず無視されても結構です。ここまで考えてくださっただけでも本当に感謝しております。どうもありがとうございました。まずはお礼と補足まで・・・。
- april21
- ベストアンサー率42% (91/216)
＞我社にはＶＢＡの経験者がおりません ■登録方法 [ツール] メニューの [マクロ] をポイントし、[Visual Basic Editor] をクリック。 エディタが開くので「挿入」-「標準モジュール」クリック。 ＶＢＡをコピーして貼り付けます。 エディタを閉じてエクセル画面に戻ります。 [ツール] メニューの [マクロ] をポイントし「マクロ」をクリック。 「データー処理1」と表示されてるはずなので選択して「実行」 （注意：実行する前にオートフィルタはオフにしておいてください） ■実行するには ↓の通りＡＢＣ列の表とＥＦＧ列の表が必要なので ＡＢ列に抽出するデータとＣ列に条件合致時に入力されるデータ領域。 ＥＦ列は条件のデータとＧ列に条件合致時に入力するデータ。 （条件に比較演算子などを用いる場合は左に付けて下さい。3より小さいは<3 という風に。フィルタオプションで設定できるものは設定できるはずです） 2つの条件に合致するデータが何もない場合にコピー先が変わるのを防ぐ為に Ｃ1を利用しているのでデータ領域以外に一行必要です。 （タイトルは何でも構いませんのでタイトル行をつけてください） Ｃ列の領域に名前を定義して下さい。 「挿入」-「名前」-「定義」で名前を分類に、参照範囲はC列のデータ領域選択。 （タイトル行を含むデータの最後までを選択、100個あるなら100まで） ここまで出来たら↑の手順で「データ処理1」を実行してみてください。 確認するにはＣ列をキーにして並べ替えを行ってみると確認しやすいかも？ 毎日、手作業でされてるのはＣ列が出来上がってるでしょうからテストしてみて下さい。 データが文字なのか数字だけなのか両方なのかまったく分からなかったし・・。 条件が100個って・・想像できません。
お礼
April21さん、度々の補足、またＶＢＡの登録、実行の仕方まで丁寧に教えてくださり本当にありがとうございます。条件が100個というのはE列の条件データが10個、F列の条件データが10個で、100通りほどの組み合わせがあるということなのです。早速会社で試してみますね。お忙しい中本当にありがとうございました。
- april21
- ベストアンサー率42% (91/216)
補足 ■「条件1」「条件2」のセルは""にしないようにしてください。 スペース１コでもいいので入れてください。 ■↓の２行要りません。（削除するの忘れました^_^;） If Cells(i, 5) = "" Then Exit For If Cells(i, 6) = "" Then Exit For ■＞3は>3です（半角になってなかったです。 説明するのは作るより難しい(ーー;)まだあるかも・・。 ＡＲＣさんのもあるし・・動かなかったらゴミにだしてやってください。
- april21
- ベストアンサー率42% (91/216)
＞A、B、C列からなるリスト Ａ Ｂ Ｃ・・・・←必ずタイトル行つける事 14 あ 10 い 条件1 条件2 分類・・・（左からＥ、Ｆ、Ｇ列） 10 い 1 Ｃ列に名前を定義して下さい。 「挿入」-「名前」-「定義」で名前を分類、参照範囲はC列のデータ領域選択。 上の例では =Sheet1!$C&1:$C&3 って感じに（3は使う行数によって変わります） Ｃ1セルのタイトルはマクロ処理すると条件に当てはまらなかったデータが入力されるので変わってしまいます。（必要な場合は処理後つけてください。） オートフィルタにはオプションで「等しい」だけでなく「より大きい」とか比較もあるので一応出来るようにしてあります。 3より大きいと条件をつけたい場合は「＞3」に （比較演算子は左に） あで終わるものは「*あ」に あで始まるものは「あ*」に オートフィルタはオフにしておいてください。 ---------------------ここから↓コピー----------------------- Sub データ処理1() Dim i As Integer Range("A1").Select Selection.AutoFilter For i = 2 To Range("E2").End(xlDown).Row If Cells(i, 5) = "" Then Exit For If Cells(i, 6) = "" Then Exit For Selection.AutoFilter Field:=1, Criteria1:=Cells(i, 5), Operator:=xlAnd Selection.AutoFilter Field:=2, Criteria1:=Cells(i, 6), Operator:=xlAnd Cells(i, 7).Select Selection.Copy Range("分類").Select ActiveSheet.Paste Next i Selection.AutoFilter End Sub ---------------------ここの↑まで------------------------------------- とりあえず、確かめましたのでちゃんと動くはずですが・・動かなかったら 説明が抜けてたるのかもしれないのでどうなったかお知らせください。 100通りも条件つけては確かめてないのでバグがあったらm(__)m 文字が一致するだけでいいのであればもっと簡単に出来るので処理は速くできます。 ↑のＶＢＡだとちょっと処理が複雑になるのでデータが沢山ある場合には遅いかも？
お礼
April21さん、ご回答本当にありがとうございます。しかもプログラムの動作まで確認くださったなんて本当に感激です。ゴミにするなんてとんでもない!!私は今回のことでプログラミングに大変関心を持つようになりました。先のＡＲＣ産の回答と同様にApril21さんのプログラムも是非使わさせていただきます。処理の速さを要求するなど私には恐れ多いことです。今手動でやっていることから開放されるだけでも天にも昇る気分です。私を含めた社員一同に希望を下さり本当にありがとうございます。我社にはＶＢＡの経験者がおりませんので、April21さんのプログラムはきちんとしているのにうまくいかないかもしれませんが、みんなでがんばってみます。
- ARC
- ベストアンサー率46% (643/1383)
DGet関数を使っても何とかなりそうな気はしますが、VBAで書くとすれば以下のような感じになります。 使い方は、 DataSearch(検索する範囲, 取得する列番号, [条件1] [,[条件2]]…) です。 例: E列に野菜名, F列に産地, G列に価格が入力されているとすると、 =DataSearch(E:G,3,"Tomato","Aomori") で、青森産トマトの価格を返します。 =DataSearch($E$2:$G$100,1,,,1200) で、1200円の野菜の名前を返します。(複数ある場合は、リストの上にあるものが優先) Public Function DataSearch(DataRange As Range, FieldNum As Long, ParamArray SearchValues() As Variant) As Variant Dim MaxColumn As Long '最大列数 Dim LeftCol As Long '左端のセルの列番号 Dim MaxRow As Long '最大行数 Dim Rng As Range Dim i As Long Dim CurrentRow As Long Dim Hit As Boolean '準備 MaxColumn = UBound(SearchValues) If (DataRange.Columns.Count - 1) < MaxColumn Then MaxColumn = (DataRange.Columns.Count - 1) End If MaxRow = DataRange.Worksheet.UsedRange.Rows.Count 'データ範囲の各行について処理 For Each Rng In DataRange.Rows '準備 LeftCol = Rng.Column CurrentRow = Rng.Row Hit = True '各列について判定処理 For i = 0 To MaxColumn If IsMissing(SearchValues(i)) = False Then If Cells(CurrentRow, LeftCol + i) <> SearchValues(i) Then Hit = False Exit For End If End If Next i '終了判定 If Hit = True Then DataSearch = Cells(CurrentRow, LeftCol + (FieldNum - 1)).Value Exit For End If If Rng.Row >= MaxRow Then Exit For End If Next Rng End Function
お礼
ＡＲＣさん早速のご回答本当にありがとうございます。会社の同僚と一緒にがんばってみます。ＶＢＡを使えば自在にコンピュータを動かせるのですね。今回のことで真剣にＶＢＡを勉強してみようと言う思いが強くなりました。会社の仲間たちはここ最近ゴールの見えない大量のデータ処理に終われて目が死んでいましたが、ＡＲＣさんのおかげで瞳に輝きが戻ってきました。社員一同お礼申し上げます。そして私個人としてもこのようなプログラムをこんな短時間で作ってしまわれるＡＲＣさんにご尊敬申し上げます。ＡＲＣさんのようなプロフェッショナルな技術を得るには相当勉強しなくてはならないでしょうが少しでも近づけるようがんばりたいと思います。また何かありましたらよろしくお願いいたします。
お礼
april21さん、本当に毎回ありがとうございます。実は今日他のリストでも試してみました。そのリストでは条件は１つで、Ｄ列がそれにあたるので、april21さんが書いてくださったコードのRangeAというところをＤに変更し、Selection.AutoFilter Field:=4,、Criteria2:=Cells(i, j),を削除、Criteria1:=Cells(i, j)は、(2,7)、Cells(i, j + 2).Select を(2,8)というように変えてみました。なかなかうまくいかなくて5回くらい試行錯誤してしまいました。でもなんとこれでうまくいきました！！しかし私の修正では不安なので、早速april21さんが書いてくださったように直してみます。実行してみてびっくりしました。今まで手動でやっていたときには30分位かかっていたことが、何と1分です。素晴らしいですね。april21さんのコードがシンプルでとても軽いのでしょうね。社員一同本当に感激しております。どうもありがとうございました。今回のことでapril21さんにはたくさんの貴重なお時間を私どもの為に使っていただき、言葉に出来ないほど感謝しております。また何かありましたらどうぞご教授くださいませ。私もなるべく人に頼らなくてもVBAが使えるようにがんばります。