[[20110428135936]]　『優先順位に沿った並び替え』（にっこ）　ページの最後に飛ぶ

『優先順位に沿った並び替え』（にっこ）

 過去似たようなものがあったかも知れませんが見つけられませんでしたので、宜しければご教授願います。
 xp,2003

　★

 品目　　　　　規格　　　
  あ　　1　　　(空欄)　1
  い　　2　　　　S　　 2
  う　　3　　　　M　　 3
  え　　4　　　　L　　 4

 ……と、もう少し続きます。

 品目　　規格
  あ　　　L
  え
  あ　　　M
  い　　　S
  う
  え　　　M

  このようにランダムに並んでいるものを品目→規格の優先順で★の番号通りに下記のように並べ替えたいのですがどうしたら良いでしょうか？

 品目　　規格
  あ　　　M
  あ　　　L
  い　　　S
  う
  え
  え　　　M

  休みに入りますので返事が遅くなると思いますm(_ _)m

 作業列を使った方法です。

 ＜Sheet1＞
 	A	B	C	D
 1	品目		規格	
 2	あ	1		1
 3	い	2	S	2
 4	う	3	M	3
 5	え	4	L	4

 ＜Sheet2＞
 	A	B	C	D
 1	品目	規格		
 2	あ	L		
 3	え			
 4	あ	M		
 5	い	S		
 6	う			
 7	え	M

 Sheet2のC2セルに =VLOOKUP(A2,Sheet1!A$2:B$5,2,FALSE)
 Sheet2のD2セルに =IF(B2="",1,VLOOKUP(B2,Sheet1!C$3:D$5,2,FALSE))
 下にフィルコピー

 A列からD列を選択してデータ → 並べ替え
 「範囲の先頭行」で「タイトル行」を選んで
 最優先されるキー　(列 C)
 ２番目に優先されるキー　(列 D)
 ＯＫボタンクリック

 でどうでしょうか？

 (se_9)

 申し訳ありません。書いていなかったのですが、この作業を全て自動で行いたいのです。
 一度使うだけならどうとでもなるのですが、不特定多数の者が何度も使うものなので「データ→並び替え」のような
 もう一段操作を必要とするものでは合理的ではありません。se_9様、本当に申し訳ありません。

 それにこの操作でも巧くいかないようです。

 品目と規格の番号をCONCATENATEで繋げてみたりもしたのですが、数字が文字列になってしまい計算式が認識してくれませんでした。

 ★は暫定的に取り決めたものなので必ずしもこの形に添わなくても構いません。
 ただ、「あ＿」を1、「あS」を2、としたとしても後々品目で突然「を」が出てきたりもしますので、それもなかなか現実的ではないように思います。

 わがままを言って本当に申し訳ありませんが、何卒よろしくお願いします。

（にっこ）

 同じ組み合わせ、例えば「あL」が２つ以上ある得るのでしょうか。　　(NB)

 NB様

 最初の時点では勿論重複がありますが、並び替えの時点ではCOUNTIFを使って既に重複を除いています。
 ただ「あL」や「あM」といったように品名が同じものはありますが、規格で分けたいのでこの場合は重複とは言えません。

 （にっこ）

 どの表をどのように並べ替えるのでしょう？
 2列のみのデータすか？
 並べ替えるリストに数式は使用せれていますか？
 (seiya)

 seiya様

 最終的に

 品目　規格　数量　金額

 というように4列の表にします。
 しかし数量と金額はあとからSUMIFなどで合計を出しますのでここでは記入しませんでした。

 並び替えを行なう前に品目・規格をCONCATENATEで一つにし、品目と規格が同じものは省いています。
 ですので一番最初に例に出した2つ目の表には数式が入っている訳です。

 ちょっと考えたのですが、品目の番号を100や1000などの桁にするか後からこれらをかけて桁を変え、
 規格の番号と足して「101」などの数字にし、これをSMALLなどで並び替える……
 ……でもこれだと計算工程が多くなってしまう……ような……？

 1回や2回の工程で出来れば1番良いのですが……

 (にっこ)

 VBAでの処理になります。

 Sheet1のA1から基本リストがあり、Sheet2のA1から並べ替えるリストがあると仮定しています。

 Sub test()
Dim a(), i As Long, ii As Long, n As Long
a = Sheets("sheet1").Range("a1").CurrentRegion.Value
With CreateObject("scripting.dictionary")
    .comparemode = vbTextCompare
    For i = 2 To UBound(a, 1)
        For ii = 2 To UBound(a, 1)
            .Item(a(i, 1) & ";;" & a(ii, 3)) = n
            n = n + 1
    Next ii, i
    a = Sheets("sheet2").Range("a1").CurrentRegion.Value
    ReDim Preserve a(1 To UBound(a, 1), 1 To UBound(a, 2) + 1)
    For i = 2 To UBound(a, 1)
        a(i, Ubound(a,2)) = .Item(a(i, 1) & ";;" & a(i, 2))
    Next
    VSortM a, 2, UBound(a, 1), UBound(a, 2), 1
End With
Sheets("sheet2").Range("a1").CurrentRegion.Value = a
End Sub

 Sub VSortM(ary, LB, UB, ref, myOrd)
Dim i As Long, ii As Long, iii As Long, M, temp
i = UB: ii = LB
M = ary(Int((LB + UB) / 2), ref)
Do While ii <= i
    If myOrd = 1 Then
        Do While ary(ii, ref) < M: ii = ii + 1: Loop
        Do While ary(i, ref) > M: i = i - 1: Loop
    Else
        Do While ary(ii, ref) > M: ii = ii + 1: Loop
        Do While ary(i, ref) < M: i = i - 1: Loop
    End If
    If ii <= i Then
        For iii = LBound(ary, 2) To UBound(ary, 2)
            temp = ary(ii, iii): ary(ii, iii) = ary(i, iii): ary(i, iii) = temp
        Next
        i = i - 1: ii = ii + 1
    End If
Loop
If LB < i Then VSortM ary, LB, i, ref, myOrd
If ii < UB Then VSortM ary, ii, UB, ref, myOrd
End Sub
(seiya)
コード修正 13:41

 ＞品目の番号を100や1000などの桁にするか後からこれらをかけて桁を変え、
 ＞規格の番号と足して「101」などの数字にし、これをSMALLなどで並び替える……
 を数式にすると。。。

 Sheet1	[A]	[B]
[1]	品目	規格
[2]	あ	
[3]	い	S
[4]	う	M
[5]	え	L
			↓C2=IF(A2="","",MATCH(A2,Sheet1!A:A,0)*100+IF(B2="",1,MATCH(B2,Sheet1!B:B,0)))			
 Sheet2	[A]	[B]	[C]	[D]	[E]	[F]
[1]	品目	規格	順番		品目	規格
[2]	あ	L	205		あ	M
[3]	え		501		あ	L
[4]	あ	M	204		い	S
[5]	い	S	303		う	
[6]	う		401		え	
[7]	え	M	504		え	M
[8]					↑	
					｜	
					E2=IF($A2="","",INDEX(A:A,MATCH(SMALL($C:$C,ROW(A1)),$C:$C,0))&"")

 Sheet2のC列を無くした式も出来ますが、有った方が効率が良いと思います。
 E2式をF2にフィルドラッグと
 C2:F2を余裕を見た必要行、下にフィルドラッグしてみて下さい。

 (HANA)

 品目、規格がSheet1の　A1:B5
 データが　　Sheet2の　A2:B100として
 Sheet2
 E2:=IF(COUNTA($A$2:$A$100)=0,"",INDEX(Sheet1!$A$2:$A$5,MIN(IF(COUNTIF($A$2:$A$100,Sheet1!$A$2:$A$5)>0,ROW($A$1:$A$4),""))))
 「Ctrl]+[shift]+[Enter]　で確定
 E3:=IF(E2="","",IF(COUNTIF($E$2:E2,E2)<SIGN(SUMPRODUCT(($A$2:$A$100=E2)*($B$2:$B$100="")))+SIGN(SUMPRODUCT(($A$2:$A$100=E2)*($B$2:$B$100="S")))+SIGN(SUMPRODUCT(($A$2:$A$100=E2)*($B$2:$B$100="M")))+SIGN(SUMPRODUCT(($A$2:$A$100=E2)*($B$2:$B$100="L"))),E2,IF(E2=Sheet1!$A$5,"",INDEX(Sheet1!$A$2:$A$5,MIN(IF((COUNTIF($A$2:$A$100,Sheet1!$A$2:$A$5)>0)*(COUNTIF($E$2:E2,Sheet1!$A$2:$A$5)=0),ROW($A$1:$A$4),""))))))
 「Ctrl]+[shift]+[Enter]　で確定
 下へコピー
 F2:=IF(E2="","",IF(E1<>E2,IF(SUMPRODUCT(($A$2:$A$100=E2)*($B$2:$B$100="")),"",IF(SUMPRODUCT(($A$2:$A$100=E2)*($B$2:$B$100="S")),"S",IF(SUMPRODUCT(($A$2:$A$100=E2)*($B$2:$B$100="M")),"M","L"))),IF(F1="",IF(SUMPRODUCT(($A$2:$A$100=E2)*($B$2:$B$100="S")),"S",IF(SUMPRODUCT(($A$2:$A$100=E2)*($B$2:$B$100="M")),"M","L")),IF(F1="S",IIF(SUMPRODUCT(($A$2:$A$100=E2)*($B$2:$B$100="M")),"M","L"),IF(F1="M","L","")))))
  下へコピー
 (NB)

 何とか形になってきました！
 自分でもう少し調整してみます。

 回答を寄せて下さいましたse_9様、NB様、seiya様、HANA様　本当に有難うございました!!

 (にっこ）

コメント返信：

[ 一覧(最新更新順) ]

[[20110428135936]] 『優先順位に沿った並び替え』（にっこ） ページの最後に飛ぶ

[[20110428135936]]　『優先順位に沿った並び替え』（にっこ）　ページの最後に飛ぶ