[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『SortedListでの複数キーについて』（T18）
お世話になります。
SortedListで複数のキーを持つ場合についてご教授願います。
例表のI-L列にあるデータのI(id)列、K(pp)列をキーにしてD-G列に昇順に並べ替えして出力したいです。
（一部項目順がデータと異なります）
idとppを結合したのをキーに、nmとtを結合したのを値としてSortedListに格納した後に分離する方法で
コード作成はできましたが、こんな二度手間？ではなくもっとスッキリできないかと思っています。
１万行程度のテストデータでは、RangeのSortメソッド？の方が３倍ほど処理速度が速く
実作業ではSortを使う予定でいます。
SortedListの学習目的でお尋ねしていますが、よろしくお願いします。
（例表）
D E F G I J K L id pp nm t id nm pp t アアア 2 YG49 0.64 ウウウ 3 YG23 0 アアア 9 YG61 0.44 アアア 9 YG61 0.44 イイイ 5 YG55 0.88 ウウウ 1 YG81 0.1 イイイ 11 YG49 0.47 アアア 2 YG49 0.64 ウウウ 1 YG81 0.1 イイイ 11 YG49 0.39 ウウウ 3 YG23 0 ウウウ 4 YG38 0.24 ウウウ 4 YG38 0.24 イイイ 5 YG55 0.88
（コード例）;ソート部分はCall文で呼び出しています・・・
Sub test_SL()
Dim sh As Worksheet
Dim wR1 As Long
Dim itm1()
Dim itm3()
Set sh = ThisWorkbook.Worksheets("test")
wR1 = 10000
itm1() = sh.Range(sh.Cells(2, "I"), sh.Cells(wR1, "L")).Value '格納
Call getSL(wR1, itm1, itm3)
With sh
.Range(.Cells(2, "D"), .Cells(wR1, "G")).Value = itm3() '出力
End With
End With
'------------------------------------------------
Private Sub getSL(データ行 As Long, 対象, 格納)
Dim sl As Object
Dim x
Dim i As Long
Set sl = CreateObject("System.Collections.SortedList")
x = 対象
'// id & pp をキーに nm & t を値として格納；結合子「,」（Ｉ列〜）
For i = LBound(x) To UBound(x)
If sl.Contains(x(i, 1)) = False Then
sl.Add x(i, 1) & "," & Format(x(i, 3), "0000"), x(i, 2) & "," & x(i, 4)
End If
Next i
'// 昇順並び替えられた結果を分割（Split）して格納
Dim buf1, buf2
For i = 0 To sl.Count - 1
buf1 = Split(sl.GetKey(i), ",")
buf2 = Split(sl.getbyindex(i), ",")
ReDim Preserve 格納(1 To sl.Count, 1 To 4)
格納(i + 1, 1) = buf1(0) 'id
格納(i + 1, 2) = buf1(1) 'pp
格納(i + 1, 3) = buf2(0) 'nm
格納(i + 1, 4) = buf2(1) 't
Next i
Set sl = Nothing
End Sub
＜ 使用 Excel：Excel2010、使用 OS：Windows7 ＞
速度を気にしなければ、こんな方法も
Sub test()
Dim a, i As Long, ii As Long, iii As Long, temp As String, n As Long, w
With Sheets("test")
a = .Range("i1", .Cells.SpecialCells(11)).Resize(, 4).Value
With CreateObject("System.Collections.SortedList")
For i = 2 To UBound(a, 1)
If Len(a(i, 1) & a(i, 2) & a(i, 3) & a(i, 4)) Then
temp = GetSortVal(Join(Array(a(i, 1), a(i, 2), a(i, 3), a(i, 4)), Chr(2)))
If Not .Contains(temp) Then
ReDim w(1 To UBound(a, 2), 1 To 1)
Else
w = .Item(temp)
ReDim Preserve w(1 To UBound(w, 1), 1 To UBound(w, 2) + 1)
End If
For ii = 1 To UBound(a, 2)
w(ii, UBound(w, 2)) = a(i, ii)
Next
.Item(temp) = w
End If
Next
n = 1
For i = 0 To .Count - 1
For ii = 1 To UBound(.getbyindex(i), 2)
n = n + 1
For iii = 1 To UBound(a, 2)
a(n, iii) = .getbyindex(i)(iii, ii)
Next
Next
Next
End With
.Range("d1").Resize(UBound(a, 1), UBound(a, 2)).Value = a
End With
End Sub
Function GetSortVal(ByVal txt As String) As String
Static RegX As Object
Dim i As Long, m As Object
If RegX Is Nothing Then Set RegX = CreateObject("VBScript.RegExp")
With RegX
.Global = True
.Pattern = "\d+"
If .test(txt) Then
For i = .Execute(txt).Count - 1 To 0 Step -1
Set m = .Execute(txt)(i)
txt = Application.Replace(txt, m.firstindex + 1, _
m.Length, Format$(m.Value, String(10, "0")))
Next
End If
End With
GetSortVal = txt
End Function
（seiya） 2018/04/12(木) 15:22
15:50 一か所要素の付けたし...
'------------------------------------------------
Private Sub getSL_Kai(データ行 As Long, 対象, 格納)
Dim sl As Object
Dim x
Dim i As Long, j As Long
Set sl = CreateObject("System.Collections.SortedList")
x = 対象
'// id & pp をキーに id, pp, nm, t を配列として格納；Ｉ列〜）
For i = LBound(x) To UBound(x)
If sl.Contains(x(i, 1)) = False Then
sl.Add x(i, 1) & "," & Format(x(i, 3), "0000"), Array(x(i, 1), x(i, 3), x(i, 2), x(i, 4))
End If
Next i
Dim buf
ReDim 格納(sl.Count - 1, 3)
For i = 0 To sl.Count - 1
buf = sl.getbyindex(i)
For j = 0 To 3
格納(i, j) = buf(j)
Next j
Next i
Set sl = Nothing
End Sub
（hatena） 2018/04/12(木) 15:24
早々にありがとうございます。
（例表）のE,F列のnm,ppの順が逆でしたこと、お詫びします。
両コードの処理結果問題なきこと、先ずはご報告します。
コードの理解にはしばらくお時間ください・・・(_ _)
（T18） 2018/04/12(木) 17:35
hatenaさん
「配列を格納すれば、Splitは使わないでできます」
↑は知識不足でした…、 応用させていただきます。
で、テストデータでの結果ですが、私のtest_SLが1.75秒に対し、1.5秒でした。
（3回計測の最大値です、 因みにSortメソッド？では同0.5秒でした）
エクセルの標準機能？って十分速いんですね、勉強になりました。
（厳密にはテストデータやコード、ＰＣ環境を検証する必要があるとは思いますが…）
となると、SortedListをVBAで使うメリットは何なのでしょうか？
と、新たな疑問が・・・
（T18） 2018/04/13(金) 11:14
＞（厳密にはテストデータやコード、ＰＣ環境を検証する必要があるとは思いますが…
それ、すごく大事です。 ソート前のデータの在り様で、どんなソートが適しているかが変わったりします。
＞となると、SortedListをVBAで使うメリットは何なのでしょうか？
エクセルのシートを使う前提だと、無いんじゃないですか？
＞因みにSortメソッド？では同0.5秒でした） ＞エクセルの標準機能？って十分速いんですね
これは昔から言われています。 私は標準機能の方が安心感が持てます。
ところで毎回 "？" を付けていますが、 Sortメソッドなのか、Sortオブジェクト(XL2007以降）なのか意識した方がいいと思います。 以前、質問者さんがSortオブジェクトは速いなんて云ってましたけど・・ ↓ [[20170724204331]] 『配列のソートをわざわざシート上でやる意義』（中途B）
（半平太） 2018/04/13(金) 16:53
>ソート前のデータの在り様で、どんなソートが適しているかが変わったりする
これはソートするアルゴリズムの違いで変わってくる、ということと理解しましたが、どんなのがあるか・・・機会みて調べたいと思います。
私が使用したのはSortメソッドの方ですが、ご紹介サイトではオブジェクトの方が処理が速いようですね。
（後発だから当然といえば当然？、それにしてもこの質問者さんの“求道心”は見習うべきものがあると感じ入りました）
ただ、オブジェクトの方は私には“取っつきにくい”印象があり、今のところ実用上問題ないのでメソッドが使えなくなるまでは使い続けると思います。
>エクセルのシートを使う前提だと、（SortedListを）使うメリットは無い・・・
う〜ん、これもデータの在り様とか欲しいアウトプットと関係してくるように思ってたんですが…
SortedListを検索しても私には難しいことしか書かれていないし、レベルアップして使用してみたいと思えるまでは“置いときます”(笑)
これからもよろしくお願いします。
（T18） 2018/04/16(月) 09:30
＞う〜ん、これもデータの在り様とか欲しいアウトプットと関係してくるように思ってた
いや、そこまで考えたんでしたら、その理解でいいです。引き出しを多くしておくのは有益です。
どっちか単純に決めて掛かりたい人なのかなと思ったので、一本に絞るのに背中を押しただけです。
（半平太） 2018/04/16(月) 09:39
そうですね、ここの教授陣のエッセンスを少しでも吸収したいと思ってはいるのですが、
元々のタンスが小さい上に引出しの数も少なく…（涙）
>どっちか単純に決めて掛かりたい人なのかなと思ったので・・・
基本的にそういうタイプで“お心遣い”嬉しいですが、わからないことに対しウジウジと
優柔不断なところもあり悩ましいところです…（苦笑）
（T18） 2018/04/16(月) 10:48
>う〜ん、これもデータの在り様とか欲しいアウトプットと関係してくるように思ってたんですが… >SortedListを検索しても私には難しいことしか書かれていないし、 >レベルアップして使用してみたいと思えるまでは“置いときます”(笑)
う〜ん。
SortedList クラス（オブジェクト？）は、
キーと値がペアになっているもの集合体として扱うことが前提となっています。
これと同じような考えのオブジェクトは最初から、VBAに用意されてます。
それがCollectionオブジェクトです。
https://www.sejuku.net/blog/30010#i
ただしこれは、機能が少ないので、ない機能（メソッド等)はユーザーが自作する必要があります。
つまり追加した順番で管理は出来ますがキーワードで検索ができません。
これにキーワードでも検索が出来るようにしたのが、連想配列（Dictionaryオブジェクト）
と呼ばれるものです。
つまり、VBA側でループして値を探す処理を書かなくてもよいので、
処理速度が速くなるうえに自作する必要がありません。
その他色々機能もあり便利に使えますね。
配列としても扱えるのかな？（興味が無いので勉強してません。^^;）
そこでさらに、勝手に並べ替えてくれてたらもっといいのに、というときに、
SortedListオブジェクト
というのが出てきます。
https://msdn.microsoft.com/ja-jp/library/system.collections.sortedlist(v=vs.110).aspx
これにもいろいろなプロパティやメソッドが用意されていますね。
しかし、考えてみてください。
データをコレクションに追加するごとに並べ替えが発生しますよね。
何も考えずに勝手に並び替えてくれてたら便利ですが、
無駄と言えば無駄じゃないですかね？
つまり並び替えが主目的な場合、ちょっと不利だと思います。
他のメソッドも使いつつ、並べ替えに労力を使いたくないけど並び替えて欲しいときにこそ、
使う理由が出てくると思います。
エクセルVBAの場合、
基本シートにデータがあり、シートに何かの作業の結果を書き込みますよね？
そうすると基本的にエクセルの機能（並び替えとかコピペとか）を使うことが、
結局、有利なのかなと考察します。
普通のプログラム言語は、自分でシートのような領域を用意しないと何もないので、
そういうオブジェクトを利用することが有利になるのではと思います。
（まっつわん） 2018/04/16(月) 12:22
やはりSortedListが本来もっている機能を使いつつ並べ替えもしたい、というケース以外では
私には必要ないかも、と改めて思いました。
（ご紹介サイトの説明文全て理解できたわけではないですが…^ ^;）
これからもよろしくお願いします。
（T18） 2018/04/17(火) 14:09
「例表のI-L列にあるデータのI(id)列、K(pp)列をキーにして」というのが要望ですが、
ひょっとして「I,J列」がキーになっていないでしょうか？
知らない関数等は調べていますが、根本的にseiyaさんの手法？がわかっていないので
私の勘違いであればお許しください。
キーを変更する時の助けに、コードの考え方を披露いただけると嬉しいです。
（T18） 2018/04/18(水) 14:23
私のコードは複雑なデータに対応したソートをします。
例えば
AB12 AB5 AB100 AB4
を AB4 AB5 AB12 AB100
等、文字・数値が共に複数混在したデータにも対応しています。 普段はSortedListよりも配列に対してソートさせますので、単純なソートにSortedListは使用しませんが...
提示したコードは temp = GetSortVal(Join(Array(a(i, 1), a(i, 2), a(i, 3), a(i, 4)), Chr(2))) 先頭から4列を連結した文字列をキーに昇順で並べ替えします。
K列なら temp = GetSortVal(a(i, 3)) にするだけでよいはずです。
（seiya） 2018/04/18(水) 15:00
なるほど、勘違い失礼しました…
私の例でいえば「GetSortVal(Join(Array(a(i, 1), a(i, 3)), Chr(2)))」でいいわけですね。
seiyaさんが書かれているように、1万行のテストデータでは確かに速度的には差がありましたが、
私には参考になるコード記述が随所にあり、とても勉強になります。
「GetSortVal」のコードは正規表現を使っていると思っていますが、まだまだ理解にはほど遠いです。（涙）
もしよろしければ、解説いただけると・・・って少々甘えすぎですね…
もう少し足掻いてみて、それでもダメな時は別途質問アップさせていただきます。
これからもよろしくお願いします。
（T18） 2018/04/18(水) 22:31
GetSortVal関数は文字列内の数値の桁数を揃えています。 考え方はT18さんと同様ですが、違いは1セル内の文字列にも同様に数値があれば、桁数を揃えます。
例
データが AB2A12 AB11A50
は GetSortVal関数によりそれぞれ、
AB0000000002AB00000000012 AB0000000011AB00000000050 と変換して並べ替えます。
正規表現の部分は文末から数字の塊を検索して10桁に揃えて置き換えています。 少数がある数値を含むデータで昇順・降順入り混じった並べ替えをする場合は都度変更する必要がありますが... （seiya） 2018/04/19(木) 12:11
txtの数式のところがわからず何となく10桁にしてるのかな〜と思っていましたが、
「文末から数字の塊を検索・・・」で腑に落ちました。
随所に参考になるコード記述、勉強になりました。
hatenaさん、半平太さん、まっつわんさん
コード改善案やSortedListに関するご説明、ありがとうございました。
これからの“糧”にしたいと思います。
これからもよろしくお願いします。
（T18） 2018/04/20(金) 09:15
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.