[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『表からの条件付抽出』(daddy)
お世話になります。
下記ルーチン作業をVBAで処理したく、ご教授ください。
作業)
1.G列にある名前(重複あり、順不同)毎にその同じ名前内でD列の最大値を
抽出してC列に書き出す
※非該当は「-」とする
2.A列の名前(重複なし、順不同)にG列の名前があれば、抽出したC列と
E,F列を結合してB列に書き出す
※A列に該当するのがない場合は「-」とする
3.書き出したB列は、最終的に「数値」に変換する
表)
A B C D E F G マ 490116 - 55 08 20 イ イ 580212 49 49 01 16 マ . . - 52 02 18 イ . . - 10 11 20 マ . . 58 58 02 12 イ . . . . . . .
・D列:数値、E,F列:文字数字、A,G列:全文字種(全・半角混じり)
・D〜G列は通常1万行前後
よろしくお願いします。
< 使用 Excel:Excel2010、使用 OS:Windows7 >
関数でも可能だがVBAのほうがいいのだろうか? (ねむねむ) 2014/11/14(金) 14:11
掲示のシートでその他の作業をVBAで行っており、その流れが一つ。
二つ目は自身の学習です。(さっぱり記述できません..汗)
(daddy) 2014/11/14(金) 15:00
こんなのでどうかな。
'------ Sub TEST() Dim i As Long Dim tbl As Variant, MyDt As Variant Dim dic As Object Set dic = CreateObject("Scripting.Dictionary") tbl = Range("D1", Range("G" & Rows.Count).End(xlUp)) For i = 2 To UBound(tbl, 1) If Not dic.Exists(tbl(i, 4)) Then dic(tbl(i, 4)) = Array(Empty, Empty, Empty) End If If dic(tbl(i, 4))(1) < tbl(i, 1) Then dic(tbl(i, 4)) = Array(i, tbl(i, 1), Format(tbl(i, 1), "00") & Format(tbl(i, 2), "00") & Format(tbl(i, 3), "00")) End If Next
For Each MyDt In dic.Items Range("C" & MyDt(0)).Value = MyDt(1) Next
For i = 2 To Range("A" & Rows.Count).End(xlUp).Row If dic.Exists(Range("A" & i).Value) Then Range("B" & i).Value = dic(Range("A" & i).Value)(2) End If Next End Sub '------ (HANA) 2014/11/17(月) 16:29
ざくっと確認させていただいた上で教えてください。
・D列の最大値を求めている部分を解説お願いできますか?
・C列の非該当に「-」を表示するにはどこを変えればよろしいか?
今までは知識はなくとも記述を見て“それとなく”やっていることが
イメージできる場合もありましたが、今回全くできません(涙)
(おそらく関数式が出てこないからだと思いますが...)
急ぎませんのでよろしくお願いします。
(daddy) 2014/11/17(月) 18:29
>・D列の最大値を求めている部分を解説お願いできますか? If dic(tbl(i, 4))(1) < tbl(i, 1) Then の所で、今登録されている最大値とセルの値を比べて セルの値が大きい時、そのデータに上書きします。
Dictionaryは分かりますか?
>・C列の非該当に「-」を表示するにはどこを変えればよろしいか? すみません、忘れてました。
C列は For Each MyDt In dic.Items Range("C" & MyDt(0)).Value = MyDt(1) Next の所で、対象セルにのみ値を入力しています。 なので、このループの前にすべてのセルを「-」にしておいてください。 Range("C2:C" & UBound(tbl, 1)).Value = "-" '←追加2014/11/18 For Each MyDt In dic.Items Range("C" & MyDt(0)).Value = MyDt(1) Next (HANA) 2014/11/18(火) 11:01
>Dictionary<は以前の投稿で教えていただきました。
(まだまだ咀嚼できてませんが..汗)
「-」の追加も問題ありません。
今回とよく似た処理を関数式で行っている動作の遅いシートがあり、
時間できたら移植して比較してみようと思います。
(あまり効果期待できないですかね?)
ありがとうございました。
(daddy) 2014/11/18(火) 17:28
>>Dictionary<は以前の投稿で教えていただきました。 >(まだまだ咀嚼できてませんが..汗) どの辺までわかってますか?
「2014/11/17(月) 16:29のコードで、Dictionaryがどのように使われているか?」 ではなく、もっと基本的に「Dictionaryでどんな事が出来るか?」って事ですが。 (HANA) 2014/11/18(火) 19:24
「データキーと“組み”となる項目を格納」ということで「辞書」のように
扱える..という認識はもっているのですが、一般的?な配列との違いは
よくわかっていません(というか、配列そのものの知識もアヤシイです)
今回の場合、
・「キー」はG列の名前、「項目」はD列でよろしいのでしょうか?
・「dic.Exists(dic(tbl(i,4))」の時点でもう先に進めません。
「Exists」でいったい「dic」の中の何を確認してるんでしょうか?
(そもそもこの解釈がまちがってる??)
???だらけで“わからないことがわからない”状態でしょうか(涙)
低レベルで申し訳ないことです。
(daddy) 2014/11/19(水) 11:47
>「データキーと“組み”となる項目を格納」ということで「辞書」のように >扱える..という認識はもっているのですが これを持っているかどうかを聞きたかったので、完璧なお返事をいただきました。
>一般的?な配列との違いはよくわかっていません
Dicitonaryが、データ&キー が格納できるので キーに相当する列、データに相当する列 の 二つの列を持つ配列を考えてみたいと思います。 <1> <2> [ 1] [ 2] [ 3] こんな感じの配列(まだ空の状態)です。
これにデータを入れる時は、 1行目の1列目の位置に「AAA」を入れなさい。 1行目の2列目の位置に「123」を入れなさい。 の様に指示します。 すると、配列は <1> <2> [ 1] AAA 123 [ 2] [ 3] になります。
続いて、2行目1列目の位置に「BBB」 2行目2列目の位置に「456」 3行目1列目の位置に「CCC」 3行目2列目の位置に「789」も入れると <1> <2> [ 1] AAA 123 [ 2] BBB 456 [ 3] CCC 789 になります。
今度はデータを取り出すのは 1行目1列目の位置に入っている値は? の様にして取り出します。
このデータから「BBB に対応する 2列目の値が何か知りたい」と思ったら ループ処理で探すのが一般的だと思います。
1行目1列目の位置に入っている値はBBBか?・・・・・・FALSE 2行目1列目の位置に入っている値はBBBか?・・・・・・TURE これで、BBBが2行目に有るとわかるので 2行目2列目に有る値が、知りたい値です。 2行目2列目の位置に入っている値は? の様にして取り出します。
コードにしてみると、こんな感じです。 Sub SAMPLE1() Dim Tbl例 As Variant, i As Long ReDim Tbl例(1 To 5, 1 To 2) '★データを入れる Tbl例(1, 1) = "AAA": Tbl例(1, 2) = 123 Tbl例(2, 1) = "BBB": Tbl例(2, 2) = 456 Tbl例(3, 1) = "CCC": Tbl例(3, 2) = 789 '★「BBB」に対応するデータは? For i = 1 To 3 If Tbl例(i, 1) = "BBB" Then MsgBox Tbl例(i, 2) End If Next '★「AAA」って入力がありますか? For i = 1 To 3 If Tbl例(i, 1) = "AAA" Then MsgBox "あります。" End If Next End Sub
ついでに、「AAA」の入力があるか?(ある時にのみ「あります」メッセージ)も書いてみました。
この例ではループも3回で2回目に見つかるのですぐに「知りたい値」がわかりますが 1万データの内の1万回目にあるかもしれません。
こんな時に、Dictionaryが有用です。
Dictonaryは、daddyさんの認識通り「辞書」のように扱えるものです。 私のイメージ・・・今回やろうと思っているイメージ・・・では「索引」です。
カタログの後ろとかに ついてたりしますよね。 アルファベット順とか、五十音順とかにワードが並んでいて そのワードがどのページにあるのか書いてある一覧です。
「BBB」を探すと「456」と書かれている。
「索引」だと、BBBを探さないといけないので ループ処理と同じ様ですが すごい記憶力の良い人がその「索引」を丸暗記していて daddyさんが「BBBは何ページ?」と尋ねると「456ページ」と答えてくれる。 をイメージすると、本当に便利だと思いませんか?
最初に「AAAは123」「BBBは456」「CCCは789」を覚えて。と言っておくと 後で「AAAってなんだっけ?」と聞いた時に「123」と答えてくれる。
ちなみに「AAAって登録してるっけ?」と聞いた時に「はい・いいえ」でも答えてくれます。
Dictionaryを登録する書き方はいくつかありますが 私が良く使うのは Dic(登録したいKey) = 対応するItem って書き方です。
実際にコードにしてみると、こんな感じです。 Sub SAMPLE2() Dim dic As Object Set dic = CreateObject("Scripting.Dictionary") '★KyeとItemを登録する dic("AAA") = 123 dic("BBB") = 456 dic("CCC") = 789 '★「BBB」に対応するItemは? MsgBox dic("BBB") '★「AAA」って登録してたっけ? MsgBox dic.Exists("AAA") End Sub
イメージがわかってもらえたら 今回の処理コードを書いてみたいと思いますが、どうですか?
なお、最初に載せたコードは、一旦忘れてもらえたらと思います。 (HANA) 2014/11/19(水) 22:52
>If Not dic.Exists(tbl(i, 4)) Then
dic(tbl(i, 4)) = Array(Empty, Empty, Empty)
→なぜ「Not」が必要なんでしょうか?
そもそも「Exists」は「True/False」を返すのでは?
→G列が空っぽの時はD,E,Fも空っぽにすると?
>If dic(tbl(i, 4))(1) < tbl(i, 1) Then
...(割愛)
→ここからD列の値を比較してより大きい値を“上書きしていく”ですよね。
しかし、この2行でG列の“同名の中での”最大値を求めてるんですか?
G列の名前に関係なく単に比較しているように思えるのですが..
その“カラクリ?”がサッパリわかりません..(涙)
>For Each MyDt In dic.Items
→ここから先も“お手上げ”で..(涙)
お時間が許す範囲で..よろしくお願いします。
(daddy) 2014/11/20(木) 17:15
えっと、それって最初のコードですよね? >なお、最初に載せたコードは、一旦忘れてもらえたらと思います。 と書いてます様に、それについての説明は後回しにします。
>索引<=“パッと見開き全体がわかる”イメージですかね。 いや >>すごい記憶力の良い人がその「索引」を丸暗記していて 尋ねたらすぐに答えてくれる って方です。
すごい記憶力の良い人がDictonary その人の性質が、一つのKeyと一つのItem の二つの項目を関連づけて いくつでも覚えられる。 まるで「索引」の様に。
索引は、同じワードが複数回でてこないですよね? DictonaryのKeyも、同じワードを重複して登録できません。
索引を引くと、目的のワードがどこにあるのかすぐに調べられますね? DictonaryもKyeを問合せると、すぐにItemを教えてくれます。
基本的には同じ流れですが、メモ用の配列を一つ追加しようと思います。 Dictonaryには、そのメモ用紙の何行目にデータを入れたか を登録し 実際に欲しいデータは、そのメモ用紙に記録する事にします。
'------ Sub TEST2() Dim i As Long, MyR As Long Dim tbl As Variant, MyDt As Variant Dim メモ用配列 As Variant Dim dic As Object
Set dic = CreateObject("Scripting.Dictionary") tbl = Range("D1", Range("G" & Rows.Count).End(xlUp)) ReDim メモ用配列(1 To UBound(tbl, 1), 1 To 3)
For i = 2 To UBound(tbl, 1) '登録が無かったら、DictionaryにKeyと何番目の登録か番号を登録 'dic.Exists(tbl(i, 4)) が TRUE だったら 登録有り。 FALSE だったら登録無し。 'Not FALSE は、FALSEの否定なので TRUE になり IF関数の中の処理に分岐する。 If Not dic.Exists(tbl(i, 4)) Then dic(tbl(i, 4)) = dic.Count + 1 End If
'変数:MyR に、Keyが何番目の登録かを代入(何番目の登録か=メモ用配列の何行目にデータを入れたかの数値です) MyR = dic(tbl(i, 4))
'メモ用配列の、MyR行2列目の値がD列の値より小さければ・・・データ更新 '(初めてのデータであれば、必ず更新されます。) If メモ用配列(MyR, 2) < tbl(i, 1) Then '1列目に シート上の何行目に出てきたか メモ用配列(MyR, 1) = i '2列目に その時の値 メモ用配列(MyR, 2) = tbl(i, 1) '3列目に B列に表示させるための D&E&F をくっつけた文字 メモ用配列(MyR, 3) = Format(tbl(i, 1), "00") & Format(tbl(i, 2), "00") & Format(tbl(i, 3), "00") End If Next
Range("C2:C" & UBound(tbl, 1)).Value = "-" For i = 1 To dic.Count 'メモ用配列の1列目には シート上の何行目のデータか? ' 2列目には その値 'を入れたので、順番に書き出す。 Range("C" & メモ用配列(i, 1)).Value = メモ用配列(i, 2) Next
For i = 2 To Range("A" & Rows.Count).End(xlUp).Row 'A列のデータが登録されていたら・・・・メモ用配列の3列目に「D&E&F をくっつけた文字」が入っているので If dic.Exists(Range("A" & i).Value) Then 'メモ用配列の何行目に有るか Dictonaryで調べて 変数:MyR に代入 MyR = dic(Range("A" & i).Value) 'B列に、その値を書き出す。 Range("B" & i).Value = メモ用配列(MyR, 3) End If Next End Sub '------
Dictonaryには イ 1 マ 2 が登録されます。
メモ用配列は、最終的には <1> <2> <3> [ 1] 6 58 580212 [ 2] 3 49 490116 [ 3] [ 4] [ 5] が出来上がっています。
i=2のループ >55 8 20 イ を処理して <1> <2> <3> [ 1] 2 55 550820 ・・・・「イ」のデータ [ 2] [ 3] [ 4] [ 5]
i=3のループ >49 1 16 マ を処理して <1> <2> <3> [ 1] 2 55 550820 ・・・・「イ」のデータ [ 2] 3 49 490116 ・・・・「マ」のデータ [ 3] [ 4] [ 5]
i=4のループ >52 2 18 イ を処理して・・・・・52 と 55 は、55の方が大きいので変更なし。
i=5のループ >10 11 20 マ を処理して・・・・・49 と 10 は、49の方が大きいので変更なし。
i=6のループ >58 2 12 イ を処理して <1> <2> <3> [ 1] 6 58 580212 ・・・・「イ」のデータ [ 2] 3 49 490116 ・・・・「マ」のデータ [ 3] [ 4] [ 5]
すると、6行目に「58」、3行目に「49」を書き出せば良いとわかります。 (HANA) 2014/11/20(木) 18:20
またまたピンボケの質問で申訳ありません。
レスポンス悪くなりますが、よろしくお願いします。
(daddy) 2014/11/21(金) 17:03
>Dictonaryには > イ 1 > マ 2 >が登録されます。 の所はわかりますか?
・Keyが「イ」 対応するItemが「1」 ・Keyが「マ」 対応するItemが「2」
たとえばここに、さらに ・Keyが「イ」 対応するItemが「3」 を登録しようとしても、Key「イ」はすでに登録されているで 新たに登録はできません。
「イ」に対応するItemを「1」→「3」に変更する事は出来ますが。
>G列の名前は重複してますが..??? そこは重複してて良いんですよ? G列の名前を出てくる順に、Dictionaryにすべて登録している訳ではないので。
コードの↓の所 '登録が無かったら、DictionaryにKeyと何番目の登録か番号を登録 ~~~~~~~~~~~~~~~~と言う条件で登録しています。
Dictonaryの登録の方は書いていないので、そこが引っかかってるのかな。。。?
i=2のループで、「イ」は登録されていないので Dictonaryに登録します。key「イ」Item「1」 i=3のループで、「マ」は登録されていないので Dictonaryに登録します。key「マ」Item「2」 i=4のループで、「イ」は登録されているので、Dictonaryのデータは変更無し。 i=5のループで、「マ」は登録されているので、Dictonaryのデータは変更無し。 i=6のループで、「イ」は登録されているので、Dictonaryのデータは変更無し。
ステップインで実行しながら、どの部分が実行されているのか確認してもらえたら 2,3回目のループで dic(tbl(i, 4)) = dic.Count + 1 が実行されるが、4回目以降は実行されていないのが 分かってもらえると思うのですが。 また、ローカルウィンドウを表示させて メモ用配列の中身の移り変わりとか 変数 MyR の移り変わりなども確認してもらえると良いと思います。
それで >Dictonaryには > イ 1 > マ 2 >が登録されます。 と言う事になるのですが。 (HANA) 2014/11/21(金) 17:35
この「メモ用の配列」を使わずにしたのが「Array」でしょうか?
「dic(..)(1),dic(..)(2)」は2種類の「dic」があるということ?
まだまだ混乱状態で困ったもんです..(涙)
今の自分には、>dicは索引機能があり重複データを除くのに便利である<
ということが“おぼろげに”イメージするのが精一杯のようです..
(さすがにもうこれ以上お世話になるわけにも..スルーいただいても構いません)
(daddy) 2014/11/25(火) 17:56
「索引機能がある」と言ってしまうと ちょっと変わってきてしまうと思います。 今回は確かに「索引」の様に使用しましたが。
たとえば、Dictionaryに ・リンゴー果物 ・トマトー野菜 ・イチゴー果物 と覚えさせて、後から「トマトって何だっけ?」を知りたい時に すぐに答えを返してくれるのが 便利な点です。
もしも配列に入っていたら ループ処理でトマトを探して その対応する値・・・と言う手順で答えを取得しないといけません。
せっかくなので、Dictionaryを使わないパターンのコードを書いてみます。 データ量を増やして(重複しない値10件に、元データ30件 等) ステップインで実行しながら ループ処理にどのくらい差があるか 実感してもらえたらと思います。
'------ Sub TEST3() Dim i As Long, MyR As Long Dim ii As Long, MyCnt As Long '★変数二つ追加 Dim tbl As Variant, MyDt As Variant Dim メモ用配列 As Variant
tbl = Range("D1", Range("G" & Rows.Count).End(xlUp)) ReDim メモ用配列(1 To UBound(tbl, 1), 0 To 3) '~~~ 0〜3の4列の配列に変更
For i = 2 To UBound(tbl, 1) MyR = 0 '★メモ用配列の何行目に入っているか調べる For ii = 1 To MyCnt If tbl(i, 4) = メモ用配列(ii, 0) Then MyR = ii Exit For End If Next
'★MyRが「0」のままだったら新しいデータなので0列目に入れる If MyR = 0 Then MyCnt = MyCnt + 1 メモ用配列(MyCnt, 0) = tbl(i, 4) MyR = MyCnt End If
'メモ用配列の、MyR行2列目の値がD列の値より小さければ・・・データ更新 '(初めてのデータであれば、必ず更新されます。) If メモ用配列(MyR, 2) < tbl(i, 1) Then '1列目に シート上の何行目に出てきたか メモ用配列(MyR, 1) = i '2列目に その時の値 メモ用配列(MyR, 2) = tbl(i, 1) '3列目に B列に表示させるための D&E&F をくっつけた文字 メモ用配列(MyR, 3) = Format(tbl(i, 1), "00") & Format(tbl(i, 2), "00") & Format(tbl(i, 3), "00") End If Next
Range("C2:C" & UBound(tbl, 1)).Value = "-" For i = 1 To MyCnt 'メモ用配列の1列目には シート上の何行目のデータか? ' 2列目には その値 'を入れたので、順番に書き出す。 Range("C" & メモ用配列(i, 1)).Value = メモ用配列(i, 2) Next
For i = 2 To Range("A" & Rows.Count).End(xlUp).Row MyR = 0 '★メモ用配列の何行目に入っているか調べる For ii = 1 To MyCnt If Range("A" & i).Value = メモ用配列(ii, 0) Then MyR = ii Exit For End If Next
'★MyRが「0」より大だったらその値の行にデータがあるので 'A列のデータが登録されていたら・・・・メモ用配列の3列目に「D&E&F をくっつけた文字」が入っているので If MyR > 0 Then 'B列に、その値を書き出す。 Range("B" & i).Value = メモ用配列(MyR, 3) End If Next End Sub '------
★のコメントを付けてあるところが、大きく追加・変更した部分です。
Dictionaryを使った場合 '登録が無かったら、DictionaryにKeyと何番目の登録か番号を登録 If Not dic.Exists(tbl(i, 4)) Then dic(tbl(i, 4)) = dic.Count + 1 End If
'変数:MyR に、Keyが何番目の登録かを代入(何番目の登録か=メモ用配列の何行目にデータを入れたかの数値です) MyR = dic(tbl(i, 4)) この4行で 登録がなかったら登録・何行目に有るか調べる をしていますが
使わない場合は MyR = 0 '★メモ用配列の何行目に入っているか調べる For ii = 1 To MyCnt If tbl(i, 4) = メモ用配列(ii, 0) Then MyR = ii Exit For End If Next
'★MyRが「0」のままだったら新しいデータなので0列目に入れる If MyR = 0 Then MyCnt = MyCnt + 1 メモ用配列(MyCnt, 0) = tbl(i, 4) MyR = MyCnt End If この12行で、同様の処理をしています。 コード自体は12行ですが、ループ処理が入っていますので 実際に実行される行数は 12行より多くなります。 (HANA) 2014/11/25(火) 22:50
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.