[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『関数式のマクロ化』（T1608）
お世話になります。
関数式のマクロ化を進めていて、下記の配列数式？で頓挫しています。
「AI2={=MIN(LARGE(IF($AK$2:$AK$6000=AK2,$AY$2:$AY$6000,),{1,2,3}))}」
できるだけ一連の作業と統一性をもたせたいため勝手いいますが、
以下コードの「v24」（．．．）をご教授願えないでしょうか？
Set r24 = Range("AI2:AI" & maxR) 'maxR;最終行
v24 = r24.Value
i = 0
With WorksheetFunction
For Each t In r24
i = i + 1
z24 = t.EntireRow.Range("AK1")
．．．
v24(i, 1) = （．．．）
．．．
Next
End With
r24.Value = v24
よろしくお願いいたします。
＜ 使用 Excel：Excel2010、使用 OS：Windows7 ＞
数式はよくわかりませんけど、もしAI2に対する式が正しいとすればループ処理は不要で
Range("AI2").FormulaArray = "=MIN(LARGE(IF($AK$2:$AK$6000=AK2,$AY$2:$AY$6000,),{1,2,3}))"
Range("AI2").AutoFill Destination:=Range("AI2:AI" & maxR), Type:=xlFillDefault
この２行だけでいいのでは？
（β） 2016/08/30(火) 16:54
説明不足でした、実は式はAI列以下にも入力してあります。（AI3=...=AK3...）
であれば、提示式をループで埋めればいいかとも思ったのですが、
目的が「高速化」にありまして、処理時間的にはどうなんでしょうか？
（トンチンカンな質問であれば、ご容赦ください．．）
（T1608） 2016/08/30(火) 17:07
本題じゃなくてすみません。
この数式って、ちょっと変に感じるんですけど。。
↓
＞AI2=MIN(LARGE(IF($AK$2:$AK$6000=AK2,$AY$2:$AY$6000,),{1,2,3}))
「１，２，３」番目に大きい中で、一番小さいものだったら、単に3番目に大きいものなんじゃないですか？
これとどこが違うんですか？ ↓ =LARGE(IF($AK$2:$AK$6000=AK2,$AY$2:$AY$6000,),3)
（半平太） 2016/08/30(火) 17:26
まったくその通りで、式がまちがっていますね。
（おそらく同種の式の転用でまちがったのだと思います）
式を下記に訂正させていただきます。
{=LARGE(IF($AK$2:$AK$6000=AK2,$AY$2:$AY$6000,),3)}
失礼しました。
（T1608） 2016/08/30(火) 17:58
数式がよくわからないのですけど、もし、AI2 にセットする数式を AK列等、他のセルにフィルコピーできるものなら フィルコピーする領域を変更すればいいんですが？
ちなみに、AK2 には、どんな数式が入るのですか？ それと、実際にセットすべき領域は、どこですか？
（セルには数式ではなく、計算結果を値でセットしたいということでしたか？）
（β） 2016/08/30(火) 18:02
よろしくお願いいたします。
（T1608） 2016/08/30(火) 18:33
数式セットか計算結果の値セットかは別にして
あらためて、どんなレイアウトで、その、どの部分にどんな計算結果をセットしたいか、 明確に説明してもらえませんか。
（β） 2016/08/30(火) 18:37
そもそもが
（T1608） 2016/08/30(火) 17:07 のコメントは、私が提示した２行のコードで実行して、要件に合わないという判断をされたのですか？ それとも、なにもせず、ただ、コードを見て、これはだめだと判断されたのですか？
（β） 2016/08/30(火) 18:42
提示いただいたコードでの結果に問題なきことは確認済みです。
今回の結果を得るのを、マクロ処理で．．というのが希望になります。
実データの抜粋を以下に示します。
AI AK AY 97 07030808 86 97 07030808 82 82 07030707 73 82 07030707 45 82 07030707 5 82 07030707 64 80 03020802 78
稀にですが、AK列が空欄の時があるので、その時はAI該当列も空欄に
したいです。 今検討中のイメージは以下なんですが．．．
If IsEmpty(z24) Then v24(i, 1) = "" Else v24(i, 1) = ???
よろしくお願いいたします。
（T1608） 2016/08/31(水) 09:15
横から失礼。 空白セルの対処ならIF文を入れればいいのでは？
Range("AI2").FormulaArray = "=IF(AK2="""","""",LARGE(IF($AK$2:$AK$6000=AK2,$AY$2:$AY$6000,),3))"
（bi） 2016/08/31(水) 09:27
If IsEmpty(z24) Then
v24(i, 1) = ""
Else
vv = r24.Value
For j = 1 To maxR
If Cells(j + 1, "AK").Value = z24 Then
vv(j, 1) = Cells(j + 1, "AY").Value
End If
Next
v24(i, 1) = .Large(vv, 3)
End If
まぁループ処理なので当然なのかも知れませんが、他の方法が思いつきません。
関数式より処理が早いマクロにしたいのですが．．ご助言願えないでしょうか？
（T1608） 2016/08/31(水) 16:07
補足）
現在あるシートの処理速度の改善に取り組んでいます。
シートにはデータ列以外に作業列が20列ほどあり、各々に関数式が
セットされています。
これまでに本サイトへの投稿や他の投稿を参考にして、14列の関数式を
マクロ化して効果をあげていますが、今回の配列数式？で頓挫しています。
（今回うまくいけば、他への展開を考えています）
データ処理作業の効率上、常に「手動計算」に設定し、データ入力後に
「再計算」ボタンを押すようにしています。
したがって、マクロでの計算式セットでは処理時間の短縮にはあまり
貢献しないのではないかと考えています。
（β様の提示コードでの手動実測比較でもほとんど変わりませんでした）
よろしくお願いいたします。
>関数式より処理が早いマクロにしたいのですが．．ご助言願えないでしょうか？
いろいろ手はありますよ。 なので、具体的に何をしたいか、その要件を教えてもらえませんか？ 数式を見て判断しろ ということかもしれませんが、βは 数式が超苦手で、そこで何をやっているのか 誤解する可能性大ですので。
さらに、なんとなく 同じコード内での３番目の値を表示するように思うのですが、もし ３番目がなければ (極端にいえば１番目しかなければ）どうするのかな？ とか、 10,10,10,7,7,5 とあった場合、１番目は 10 ですかね。３番目は？ ５って６番目だという人もいるので。
（β） 2016/08/31(水) 17:07
その修正を兼ねて改めて要件を記述しますが、お許し願えますでしょうか？
（要件）
・AK列、AY列は入力データです →AK列；識別ID、 AY列；固有データ（数値）
・AI列（作業列）に以下を出力
1)AK列の同じ識別ID内のAY列の「大きい順から3番目」の数値を出力する
2)下記の場合には「""」を出力
イ．AK列が8桁の識別ID以外の時
ロ．AY列が数値以外の時
ハ．今回の要件の想定外の場合
3)「大きい順から3番目」の定義
イ．同値を含む（10,10,10,7,7,5 とあった場合、10が該当）
ロ．3番目がない場合は2番目が該当、2番目なければ1番目が該当
以上です。
（T1608） 2016/08/31(水) 22:55
書いて、エラーなく動くという確認しかしていません。 不具合がある、あるいは、処理時間が長い ということであれば指摘願います。
なお、【AK列が8桁の識別ID以外の時】ということですが、アップされたサンプルでは７桁ですし、 別途、識別IDが、どこかにテーブルとして存在するのかどうかもわかりませんでしたので、空白以外は処理対象にしています。
★8:10 コード案、見直しておかしなところがあったので、いったん削除します。
★あらためて再掲します。
Sub Sample()
Dim al As Object
Dim dicA As Object
Dim dicD As Object
Dim c As Range
Dim v As Variant
Dim i As Long
Dim mx As Long
Dim id As Variant
Dim num As Variant
Dim w As Variant
Set dicA = CreateObject("Scripting.Dictionary")
Set dicD = CreateObject("Scripting.Dictionary")
mx = Range("AY" & Rows.Count).End(xlUp).Row
ReDim v(2 To mx, 1 To 1)
For i = 2 To mx
id = Range("AK" & i).Value
num = Range("AY" & i).Value
If id <> "" Then
If IsNumeric(num) Then
If Not dicD.exists(id) Then Set dicD(id) = CreateObject("System.Collections.ArrayList")
dicD(id).Add num
End If
End If
Next
For i = 2 To mx
id = Range("AK" & i).Value
If dicA.exists(id) Then
v(i, 1) = dicA(id)
Else
If dicD.exists(id) Then
dicD(id).Sort
dicD(id).Reverse
w = dicD(id).toarray
num = w(WorksheetFunction.Min(UBound(w), 2))
v(i, 1) = num
dicA(id) = num
End If
End If
Next
Range("AI2:AI" & mx).Value = v
End Sub
（β） 2016/09/01(木) 08:07
「Dic..」ですか．．これが処理時間短縮の“キモ”なのでしょうか？
あるいは「Ｓort」？．．
（時間短縮の“考え方”をいただけると嬉しいのですが）
「Dic..」は頭には浮かびましたが実現する能力がなく、手も足も出ません。
今までマクロ化したものでも未だ20秒〜30秒要しているのがあり応用したいのですが、
とてもとても．．
少しづつでも前進せねば．．とは思いますが、壁にぶつかった時はまた手（頭？）を
お借りしたいと思います。
これからも、よろしくお願いいたします。
（T1608） 2016/09/01(木) 11:22
コードではDictionaryも使っていますが、脇役の扱いで、今回の主役はArrayListです。
Dictionary、SortedList、ArrayList 等々、VBAからも利用が可能なんですが それらの実装が優れているんでしょうね、難しいことを考えずにコードを書くだけで （たとえ、そのコードが下手なものであっても）比較的優良な処理効率が得られます。
これらの【入れもの】には、特性というか、得意分野があって
・Dictionary は、登録したキーの順番にはかかわらず、キーを与えて直接参照、書き込みができます。 ・SortedListは、ほぼ Dictionaryと同じ機能ですが、加えてキーを書きこんだ時に、自動的に中身を【昇順】にしてくれます。
これら２つの難点というか、同じ値をキーとして複数のレコードを格納することができません。 （工夫すればできるんですが、ややこしくなります）
・ArrayList は、キーとデータ という関係ではなく データしか登録できません。ただし 同値のものを複数格納することができます。 格納したものを昇順に並び替えることができますし、昇順で並び替えた後、降順にすることもできます。
で、今回のコードは 識別ID ごとに ArrayList を持たせ（DicD(識別ID)がその識別IDに紐付いたArrayListになっています） 固有データ（数値）を、その 識別IDに紐付いたArrayList に格納していきます。
格納が終われば、そのArrayList内を降順（大きなものからの順）に並び替え、３番目に位置する値（１つ、ないしは２つしかない場合はそれ） を取得しています。同じ識別IDが何回も登場しますので、そのたびにこの処理を行うのは、もったいないので、別途、dicA に 識別コードをキーに、 答えの数値をデータにして登録しておき、その登録があれば、dicAから直接抽出します。
で、この結果をセル毎に書きださず、配列におさめておいて、最後に一挙に AI列に書きこんでいます。
まぁ、あまりあせらず、一歩ずつ吸収していってください。
（β） 2016/09/01(木) 12:04
このように解説いただけると、各々の本質的な機能（特性？）が
わかった気になるので困ったもんです。（苦笑）
「わかった＝コードを書ける」までには遠い道程ですが、少しづつ．．
ご提示のコード理解はこれからですが、躓いた場合は新たにＵＰさせて
いただきたいと思います。
これからも、よろしくお願いいたします。
（T1608） 2016/09/01(木) 14:13
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.