[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『追加された行を並べ替える』（派遣社員A）
よろしくおねがいします
見積書
sheet1
A B C D E 1 品名 サイズ 単価 個数 金額 2 ネジA 1x1 10 10 100 3 ネジSUS 304 2x2 20 10 200 4 ネジB 3x3 30 10 300 5 ネジ三価W 4x4 40 10 400 6 ネジ黒染 5x5 50 10 500 7 ネジSUS 305 6x6 60 10 600
顧客からの返信
sheet2
A B C D E 1 品名 サイズ 単価 個数 金額 2 ネジSUS 305 2x2 20 10 200 3 ネジA 1x1 10 10 100 4 ネジSUS 304 2x2 20 10 200 5 ネジ三価B 4x4 40 10 400 6 ネジB 3x3 30 10 300 7 ネジ三価W 4x4 40 10 400 8 ネジ黒染 5x5 50 10 500 9 ネジSUS 305 6x6 60 10 600 10 ネジSUS 304 2x2 20 10 200
sheet1の見積書に沿って並び替えたい
sheet3
A B C D E 1 品名 サイズ 単価 個数 金額 2 ネジA 1x1 10 10 100 3 ネジSUS 304 2x2 20 10 200 4 ネジSUS 305 2x2 20 10 200 5 ネジB 3x3 30 10 300 6 ネジ三価W 4x4 40 10 400 7 ネジ三価B 4x4 40 10 400 8 ネジ黒染 5x5 50 10 500 9 ネジSUS 304 2x2 20 10 200 10 ネジSUS 305 6x6 60 10 600
見積書（sheet1）に顧客が行を追加して返信（sheet2）をしてくるので、もとの見積書のデータに追加された行を含み並び替える（sheet3）ということをやりたい。
顧客が追加してくる製品は
ネジSUS 304、ネジSUS 305、ネジ三価W、ネジ三価B、です
追加してくる製品はこれ以外ありません
長年変化なく、取引が続く限り変わりません
見積書にネジ三価Bがある場合、顧客はネジ三価Wを追加してきます
見積書にネジ三価Wがあれば、ネジ三価Bを追加してきます
見積書にネジSUS 304がある場合、顧客はネジSUS 305を追加してきます
見積書にネジSUS 305があれば、ネジSUS 304を追加してきます
ネジSUS 304の下にネジSUS 305を、ネジSUS 305の上にネジSUS 304を、
三価Wの下に三価Bを、三価Bの上に三価Wをそれぞれ並べ替えたい
よろしくお願いします
＜ 使用 Excel：Excel2019、使用 OS：Windows10 ＞
（γ） 2022/08/03(水) 09:16
A B C D E F G 1 品名 サイズ 単価 個数 金額 組 番号 2 ネジA 1x1 10 10 100 10 1 3 ネジSUS304 2x2 20 10 200 10 2 4 ネジB 3x3 30 10 300 11 3 5 ネジ三価W 4x4 40 10 400 11 4 6 ネジ黒染 5x5 50 10 500 12 5 7 ネジSUS305 6x6 60 10 600 12 6
このように入力しておいて
<Sheet2>
A B C D E F G 1 品名 サイズ 単価 個数 金額 組 番号 2 ネジSUS305 2x2 20 10 200 10 3 ネジA 1x1 10 10 100 10 1 4 ネジSUS304 2x2 20 10 200 10 2 5 ネジ三価B 4x4 40 10 400 11 6 ネジB 3x3 30 10 300 11 3 7 ネジ三価W 4x4 40 10 400 11 4 8 ネジ黒染 5x5 50 10 500 12 5 9 ネジSUS305 6x6 60 10 600 12 6 10 ネジSUS304 2x2 20 10 200 12
このように返してもらえば、
G列で昇順に並び替え
F列で昇順で並び替え
とすれば、
<Sheet3>
A B C D E F G 1 品名 サイズ 単価 個数 金額 組 番号 2 ネジA 1x1 10 10 100 10 1 3 ネジSUS304 2x2 20 10 200 10 2 4 ネジSUS305 2x2 20 10 200 10 5 ネジB 3x3 30 10 300 11 3 6 ネジ三価W 4x4 40 10 400 11 4 7 ネジ三価B 4x4 40 10 400 11 8 ネジ黒染 5x5 50 10 500 12 5 9 ネジSUS305 6x6 60 10 600 12 6 10 ネジSUS304 2x2 20 10 200 12 7
このように並び替えが可能ですかね？
（まっつわん） 2022/08/03(水) 11:47
まっつわんさん
組と番号ですね
ありがとうございます
挑戦してみます
（派遣社員A） 2022/08/03(水) 19:28
このようなパターンはどうやれば並び替えできますか？
パーツ一覧なので製品名は長かったり、短かったりします。
５文字〜２０文字程度、もっと長いものもあります。
客先システムを通すので客先担当と変更の交渉は難しいです。
並び替えの法則が決まっているのは
末尾がφ３Wの下にφ５B、φ５Bの上にφ３W、G3の下にG4、G4の上にG3
sheet1に見積、sheet2にシステムを通した後の見積（上のφ３WとかG3などが追加されている）です。
sheet2をsheet1のように並び替えたいです
その時、末尾がφ３Wの下にφ５B、φ５Bの上にφ３W、G4の下にG5、G5の上にG4になるように
並び替えたいです
sheet1 見積
A B C 1 品番 単価 数量 2 dhsjfasu-nu-φ３W 10 1 3 sdjfhasuh-saffd-dd-ko 10 1 4 ns-jie-48m-88aa 10 1 5 546hdbsjah-φ５B 10 1 6 8er9aygihjsab-G5 10 1 7 m-sd-fe7j 10 1 8 as-88ijfie-w 10 1 9 mw-skos-e987ab-G4 10 1 10 isufjwoow-nie-ry 10 1
sheet2 客先システムを通した後の見積
A B C
1 品番 単価 数量
2 mw-skos-e987ab-G5 10 1
3 ns-jie-48m-88aa 10 1
4 mw-skos-e987ab-G4 10 1
5 8er9aygihjsab-G5 10 1
6 isufjwoow-nie-ry 10 1
7 dhsjfasu-nu-φ３W 10 1
8 8er9aygihjsab-G4 10 1
9 sdjfhasuh-saffd-dd-ko 10 1
10 546hdbsjah-φ５B 10 1
11 m-sd-fe7j 10 1
12 as-88ijfie-w 10 1
13 dhsjfasu-nu-φ5B 10 1
sheet3 このように並び替えたい
sheet1の並び順に
末尾がφ３Wの下にφ５B、φ５Bの上にφ３W、G4の下にG5、G5の上にG4の条件を足す
A B C
1 品番 単価 数量
2 dhsjfasu-nu-φ３W 10 1
3 dhsjfasu-nu-φ５B 10 1
4 sdjfhasuh-saffd-dd-ko 10 1
5 ns-jie-48m-88aa 10 1
6 546hdbsjah-φ５B 10 1
7 8er9aygihjsab-G4 10 1
8 8er9aygihjsab-G5 10 1
9 m-sd-fe7j 10 1
10 as-88ijfie-w 10 1
11 mw-skos-e987ab-G4 10 1
12 mw-skos-e987ab-G5 10 1
13 isufjwoow-nie-ry 10 1
（派遣社員A） 2022/08/13(土) 17:09
最初の質問に少し戻りますが、 | 顧客が追加してくる製品は | ネジSUS 304、ネジSUS 305、ネジ三価W、ネジ三価B、です | 追加してくる製品はこれ以外ありません | 長年変化なく、取引が続く限り変わりません ということですから、 ・出てくる可能性のあるものも含めた品番はすべて予め分かっているので、 ・それらの順番を指定しておく だけでソートができるはずです。 つまり、 ネジA ネジSUS 304 ネジSUS 305 ネジB ネジ三価W ネジ三価B ネジ黒染 ネジSUS 304 ネジSUS 305 を「ユーザー定義リスト」に予め登録しておいて、それを利用するだけです、ということです。 （登録処理は、ワークシートのセル範囲を簡単に取り込めますから手間もかかりません。）
■ 今回の追加質問も、予め出てくる可能性があるものを含めた品番の一覧があるはずなので、 それを「ユーザー定義リスト」に登録して利用すればよいと思いますよ。
アドホックに手で並び替えるようなことをしなければならないなら、事務管理に支障が出ます。 順序が求められるなら、それを実現するための仕掛けがきっと備わっているはずです。 （たぶんあなたがそれを知らされていないだけじゃないですか？周囲に確認してみてください。）
そうした固定のものではないんです、今ある品番に続けてコードが勝手に追加されるんです、 ということであれば、 品番を二つの部分にわけるしかありません (1)固定の品番部分 と (2)追加の部分 の二つです。
ソートに当たっては、 ・最優先されるキーに (1)の部分を入れた列を指定して、順序は「ユーザー定義リスト」から指定 ・次に優先されるキーに (2)の部分を入れた列を指定して、順序は「昇順」 をセットして実行すればよいわけです。
もっとも、キーを二つに分ける話は、品番体系をある意味で変更するということですから、 取扱者が一存で変更できるものでもないですし、可能性は低いです。あくまで理屈の上での話です。
（γ） 2022/08/14(日) 08:10
（派遣社員A） 2022/08/14(日) 17:21
＞そうした固定のものではないんです、今ある品番に続けてコードが勝手に追加されるんです、
確認したところこのパターンのようです
品番を二つに分けることは不可とのことでした
マクロでうまく処理することはできないでしょうか
（派遣社員A） 2022/08/22(月) 15:37
Sub test1()
Dim i As Long, j As Long, k As Long
Dim s As String, s1 As String, s2 As String
Dim ss() As String
Dim table2 As Range
Set table2 = Worksheets("Sheet2").Range("A1").CurrentRegion
With table2.Columns(table2.Columns.Count) '作業列（使用済み）
If .Cells(1).Value = "x" Then
.Cells.Offset(1).ClearContents
Else
.Cells(1).Offset(, 1).Value = "x"
Set table2 = table2.CurrentRegion
End If
End With
Worksheets("Sheet3").UsedRange.Offset(1).ClearContents
j = 2
ss = Split("dummy,φ3W,φ5B,G4,G5,dummy", ",") '半角で ペアは，区切りで並べて配置
' On Error GoTo errHand
For i = 2 To Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
s = StrConv(Worksheets("Sheet1").Cells(i, "A").Value, vbNarrow)
For k = 1 To UBound(ss) - 1
If s Like "*" & ss(k) Then Exit For
Next k
If k < UBound(ss) Then 'ペア対象の時
s1 = ss(k)
s2 = IIf(k Mod 2 = 0, ss(k - 1), ss(k))
findd2 table2, s, s1, s2, j '1番目の値
s1 = ss(k)
s2 = IIf(k Mod 2 = 0, ss(k), ss(k + 1))
findd2 table2, s, s1, s2, j '２番目の値
Else 'ペア以外
s1 = ""
s2 = ""
findd2 table2, s, s1, s2, j 'そのものだけ
End If
Next
On Error Resume Next 'Sheet1に無い品番の転記
table2.Columns(table2.Columns.Count).SpecialCells(xlCellTypeBlanks).EntireRow.Copy _
Worksheets("Sheet3").Cells(j + 1, 1)
On Error GoTo 0
table2.Columns(table2.Columns.Count).ClearContents
Worksheets("Sheet3").Activate
' Exit Sub
'errHand:
' MsgBox s & "がありません"
End Sub
Sub findd2(table As Range, s As String, s1 As String, s2 As String, j As Long)
Dim r As Range
Dim ss As String
Dim rAD As String
Dim col As Long
ss = Replace(s, s1, s2)
col = table.Columns.Count
Set r = table.Columns(1).Find(ss, , xlValues, xlWhole, xlByColumns, xlNext, , False)
If Not r Is Nothing Then
rAD = r.Address
Do Until r(1, col).Value = "" '作業列で未使用チェック
Set r = table.Columns(1).FindNext(r)
If r.Address = rAD Then
Set r = Nothing
Exit Do
End If
Loop
End If
If Not r Is Nothing Then
r(1, col).Value = j '使用済み
r.Resize(, 3).Copy Worksheets("Sheet3").Cells(j, 1)
j = j + 1
Else
' Err.Raise 513
End If
End Sub
（kazuo） 2022/08/23(火) 16:15
回答拝見しました。 当時用意しておいたコメントを下記します。
相手に提示したデータ（以下、「提示データ」）の品番の順序は、 別に社内共通の順序というわけではなく、 いわば"俺様並び"ということですか？
戻ってきたデータ（以下、「戻りデータ」）を、 ともかく「提示データ」の順番に準拠して並び替えたいのだ、 ということなんですか？
それなら、以下の手順でできます。
(1)「提示データ」について、提示順による「提示順連番」(1,2,3・・・）を作業列に作成
(2)「提示データ」について、「品番」の昇順でソート
(3)「戻りデータ」について、作業列に追加し、
「品番」をキーにして、VLOOKUP(第4引数をTrue）を使って、
提示データから「提示順連番」を取得（作業列に追加）
(4)「戻りデータ」について
第一優先キー:「提示順連番」（昇順）
第二優先キー:「品番」 （昇順）
を指定してソートすれば、出来上がります。
(5)なお、「提示データ」を「提示順連番」でソートし、元に戻しておきます。
必要ならマクロを書けば自動化できます。 データの数にもよりますが、手作業で適宜並び替えたほうがほうが早いかも知れませんね。
【素朴な疑問】 それほど順番を気にされるなら、提示データの順序は、どのように決めたのですか？ 社内統一の順序を定め、それで提示コードも、戻りコードも並び替えるのが自然だと思います。
（gamma） 2022/08/23(火) 21:07
既に回答コメントが提示されていますが、上記の方針をコードにしてみました。
【参考コード】 Sheet1 が 「提示データ」 Sheet2 が 「戻りデータ」 であるとして、 Sheet2を直接書き換えます。 （したがって、必要であれば、Sheet2のコピーをどこかに持っておいたほうが安全かもしれない）
Sub main()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim rng As Range
Dim lastCol&
Dim num&
Dim rng2 As Range
Dim lastCol2&
Dim num2&
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
'(1)「提示データ」について、提示順による「提示順連番」(1,2,3・・・）を作業列に作成
Set rng = ws1.[A1].CurrentRegion
Set rng = rng.Resize(, rng.Columns.Count + 1)
lastCol = rng.Columns.Count
num = rng.Rows.Count - 1
rng.Cells(2, lastCol) = 1
rng.Cells(2, lastCol).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
Step:=1, Stop:=num, Trend:=False
'(2)「提示データ」について、「品番」の昇順でソート
rng.Sort key1:=rng.Cells(1, 1), order1:=xlAscending, Header:=xlYes
'(3)「戻りデータ」について、作業列に追加し、
' 「品番」をキーにして、VLOOKUP(第4引数をTrue）を使って、
' 提示データから「提示順連番」を取得（作業列に追加）
Set rng2 = ws2.[A1].CurrentRegion
Set rng2 = rng2.Resize(, rng2.Columns.Count + 1)
lastCol2 = rng2.Columns.Count
num2 = rng2.Rows.Count - 1
With rng2.Cells(2, lastCol).Resize(num2, 1)
.Formula = "=VLOOKUP(A2,Sheet1!" & rng.Address & ",4,TRUE)"
.Value = .Value
End With
'(4)「戻りデータ」について
' 第一優先キー:「提示順連番」（昇順）
' 第二優先キー:「品番」 （昇順）
' を指定してソート
rng2.Sort key1:=rng2.Cells(1, lastCol2), order1:=xlAscending, _
key2:=rng2.Cells(1, 1), order2:=xlAscending, _
Header:=xlYes
'(5)なお、「提示データ」を「提示順連番」でソートし、元に戻しておきます。
rng.Sort key1:=rng.Cells(1, lastCol), order1:=xlAscending, Header:=xlYes
rng.Columns(lastCol).ClearContents 'Sheet1の作業列を消去
rng2.Columns(lastCol2).ClearContents 'Sheet2の作業列を消去
End Sub
（gamma） 2022/08/23(火) 21:08
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.