基準列が空欄セルも含めて並び替えを実施する方法

質問

添付ファイルの様な左列のIDを基準にそれに紐付く氏名、住所、商品名の列があります。　表が出力される時は並びがIDを基準に降順（番号の新しいものが上に）になっていますが、これを毎日昇順（番号が古い物を上に）する作業があります。　表を見て頂ければおわかりの通り、IDは1行しか表示されない為、通常のエクセルの並び替えメニューで実行するとIDが表示ある行とない行でバラバラになってしまう為、現状は行切り取り→下へ移動の方法で並び替えを実施していますが、この作業が手作業な為、非常に時間が掛かってしまいます。　マクロかVBAでID列を基準にそれにぶら下がっているID空欄行を全て並び替える（降順　→　昇順）方法がお分かりになればご指導願います。

※サンプルは５明細ですが実際の明細は５０前後の明細になります。

回答No.5

kagakusuki

回答No.3、4です。

　一応、VBAのマクロの一例も回答させて頂きます。

【昇順で並べ替えを行うマクロ】
※罫線の書換えあり。D列は同じIDのデータのもの同士の範囲内において昇順で並べ替え。

Sub ID昇順()

Dim I As Long '繰り返し処理用の変数
Dim LR As Long '最終行
'最終行を求める際に、「End(xlUp).Row」を用いますと、関数等で空欄になっているセルの行番号や、現在は空欄でも過去においてデータが入っていた事のあるセルの行番号等の、本当の最終行よりも下の行の行番号を求めてしまう場合があるため、次の方法を用います。
LR = Application.Evaluate("MAX(IF(COUNT(D:D),MATCH(9E+307,D:D),0),IF(COUNTIF(D:D,""*?""),MATCH(""*?"",D:D,-1),0))")
If LR <= Range("E1").Row Then Exit Sub
Range("A1:D" & LR).Borders.LineStyle = xlContinuous
For I = 2 To LR
If Range("A" & I) = "" And Range("D" & I) <> "" Then
Range("A" & I & ":C" & I).Value = Range("A" & I - 1 & ":C" & I - 1).Value
End If
Next I
Columns("A:D").Sort _
Key1:=Range("A1"), _
Order1:=xlAscending, _
Key2:=Range("D1"), _
Order2:=xlAscending, _
Header:=xlYes
For I = LR To 3 Step -1
If Range("A" & I) <> "" And Range("A" & I) = Range("A" & I - 1) Then
Range("A" & I & ":C" & I).Value = ""
Range("A" & I & ":C" & I).Borders(xlEdgeTop).LineStyle = xlNone
End If
Next I

End Sub

【降順で並べ替えを行うマクロ】
※罫線の書換えあり。D列は同じIDのデータのもの同士の範囲内において昇順で並べ替え。

Sub ID降順()

Dim I As Long '繰り返し処理用の変数
Dim LR As Long '最終行
'最終行を求める際に、「End(xlUp).Row」を用いますと、関数等で空欄になっているセルの行番号や、現在は空欄でも過去においてデータが入っていた事のあるセルの行番号等の、本当の最終行よりも下の行の行番号を求めてしまう場合があるため、次の方法を用います。
LR = Application.Evaluate("MAX(IF(COUNT(D:D),MATCH(9E+307,D:D),0),IF(COUNTIF(D:D,""*?""),MATCH(""*?"",D:D,-1),0))")
If LR <= Range("E1").Row Then Exit Sub
Range("A1:D" & LR).Borders.LineStyle = xlContinuous
For I = 2 To LR
If Range("A" & I) = "" And Range("D" & I) <> "" Then
Range("A" & I & ":C" & I).Value = Range("A" & I - 1 & ":C" & I - 1).Value
End If
Next I
Columns("A:D").Sort _
Key1:=Range("A1"), _
Order1:=xlDescending, _
Key2:=Range("D1"), _
Order2:=xlAscending, _
Header:=xlYes
For I = LR To 3 Step -1
If Range("A" & I) <> "" And Range("A" & I) = Range("A" & I - 1) Then
Range("A" & I & ":C" & I).Value = ""
Range("A" & I & ":C" & I).Borders(xlEdgeTop).LineStyle = xlNone
End If
Next I

End Sub

お礼

ご回答ありがとうございます。　上記マクロを実行してみたところうまくいきました。　また、関数の方も一度トライしてみます。
どうもありがとうございました。

回答No.4

kagakusuki

回答No.3の続きです。

　次に以下の操作を行って、降順シートのD2セルに条件付き書式を設定して下さい。
【ExcelのバージョンがExcel2007以降の場合】
降順シートのD2セルを選択
　　↓
Excelウィンドウの左上の辺りにある[ホーム]タブをクリック
　　↓
現れた「スタイル」グループの中にある[条件付き書式]ボタンをクリック
　　↓
現れた選択肢の中にある[ルールの管理]をクリック
　　↓
現れた「条件付き書式ルールの管理」ダイアログボックスの中にある[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの[指定の値を含むセルだけを書式設定]をクリック
　　↓
「次のセルのみを書式設定」と記されている囲いの中の左端の欄をクリック
　　↓
現れた選択肢の中にある[セルの値]をクリック
　　↓
「次のセルのみを書式設定」と記されている囲いの中の左端から2番目の欄をクリック
　　↓
現れた選択肢の中にある[次の値に等しくない]をクリック
　　↓
「次のセルのみを書式設定」と記されている囲いの中の右端の欄に次の様に入力

=""

　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[罫線]タブをクリック
　　↓
現れた[クリア]ボタンをクリック
　　↓
「プリセット」欄の中にある[外枠]ボタンをクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの中にある[適用]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの中にある[OK]ボタンをクリック

【ExcelのバージョンがExcel2003以前の場合】
降順シートのD2セルを選択
　　↓
「メニュー」バーの[書式]ボタンをクリック
　　↓
現れた選択肢の中にある[条件付き書式]をクリック
　　↓
現れた「条件付き書式の設定」ダイアログボックスの「条件1(1)」の左端の欄をクリック
　　↓
現れた選択肢の中にある「セルの値が」をクリック
　　↓
「条件付き書式の設定」ダイアログボックスの「条件1(1)」の左から2番目の欄をクリック
　　↓
現れた選択肢の中にある[次の値に等しくない]をクリック
　　↓
「条件付き書式の設定」ダイアログボックスの「条件1(1)」の右端の欄に次の様に入力

=""

　　↓
「条件付き書式の設定」ダイアログボックスの「条件1(1)」の[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[罫線]タブをクリック
　　↓
現れた[クリア]ボタンをクリック
　　↓
「プリセット」欄の中にある[外枠]ボタンをクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式の設定」ダイアログボックスの[OK]ボタンをクリック

　次に、降順シートのB2セルをコピーして、降順シートのC2セルに貼り付けて下さい。
　次に、以下の操作を行って、降順シートのA2～D2の範囲の罫線を消して下さい。

降順シートのA2～D2のセル範囲をまとめて範囲選択
　　↓
選択範囲を示す黒い太枠の内側にカーソルを合わせてマウスを右クリック
　　↓
現れた選択肢の中にある[セルの書式設定]をクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[罫線]タブをクリック
　　↓
現れた「プリセット」欄の中にある[なし]ボタンをクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック

　次に、降順シートのA2～D2の範囲をコピーして同じ列の3行目以下に貼り付けて下さい。
　次に、降順シートのA1～D1の範囲に格子状に罫線を設定して下さい。

　次に、昇順シートのA2セルに次の関数を入力して下さい。

=IF(ROWS($2:2)>作業!$A$4,"",IF(IF(ROWS($2:2)=1,FALSE,INT(LARGE(作業!$E:$E,ROWS($2:2))/作業!$A$3)=INT(LARGE(作業!$E:$E,ROWS($2:2)-1)/作業!$A$3)),"",INDEX(入力!$A:$A,MATCH(9E+99,作業!$B$1:INDEX(作業!$B:$B,MATCH(LARGE(作業!$E:$E,ROWS($2:2)),作業!$E:$E,0))))))

　次に、昇順シートのB2セルに次の関数を入力して下さい。

=IF($A2="","",VLOOKUP($A2,入力!$A:$D,COLUMNS($A:B),FALSE))

　次に、昇順シートのD2セルに次の関数を入力して下さい。

=IF(ROWS($2:2)>作業!$A$4,"",INDEX(入力!$D:$D,MATCH(LARGE(作業!$E:$E,ROWS($2:2)),作業!$E:$E,0)))

　次に降順シートのA2セルとB2セルに対して条件付き書式を設定した時と同様の操作を、昇順シートのA2セルとB2セルに対して行って、昇順シートのA2セルとB2セルにも同様の条件付き書式を設定して下さい。
　次に降順シートのD2セルに対して条件付き書式を設定した時と同様の操作を昇順シートのD2セルに対して行って、昇順シートのD2セルにも同様の条件付き書式を設定して下さい。
　次に、昇順シートのB2セルをコピーして、昇順シートのC2セルに貼り付けて下さい。
　次に、以下の操作を行って、昇順シートのA2～D2の範囲の罫線を消して下さい。

昇順シートのA2～D2のセル範囲をまとめて範囲選択
　　↓
選択範囲を示す黒い太枠の内側にカーソルを合わせてマウスを右クリック
　　↓
現れた選択肢の中にある[セルの書式設定]をクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[罫線]タブをクリック
　　↓
現れた「プリセット」欄の中にある[なし]ボタンをクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック

　次に、昇順シートのA2～D2の範囲をコピーして同じ列の3行目以下に貼り付けて下さい。
　次に、昇順シートのA1～D1の範囲に格子状に罫線を設定して下さい。

　これで、例え入力シートのデータが順不同に並んでいたとしても、入力シートのデータを追加、或いは削除、変更する毎に、IDを降順に並べ替えた表が降順シートに、IDを昇順に並べ替えた表が昇順シートに、それぞれ自動的に表示される様になります。

回答No.3

kagakusuki

確認したいのですが、それはどうしてもVBAのマクロで行わなければならない事なのでしょうか？
　例えば、元データの表に順不同でデータを入力して行くと、別の2枚のシート上に、それぞれIDを昇順で並べ替えた表と、IDを降順で並べ替えた表が自動的に作成されるという事を、（マクロではなく）ワークシート関数を使って行うのでは駄目なのでしょうか？
　最新の昇順の表と降順の表が常に存在しているのであれば、毎度毎度並べ替えを行う手間が無いため楽だと思うのですが如何でしょうか？
　ワークシート関数を使った場合、通常ではセルの並び替えを行いますと、セルの位置関係がずれてしまい、正しい結果が得られなくなる場合がありますが、それはあくまでも普通のやり方で参照先を指定するワークシート関数を使った場合の話であり、工夫次第で、セルの並べ替えに殆ど影響されない様なワークシート関数を作る事も出来ます。

　以下の方法は、元データの表が存在しているシート上において、「ID」、「氏名」、「住所」、「商品名」といった項目名が入力されているセルA1～D1の位置が変わらなければ、その下の、データが入力されているセルの位置を入れ替えたり、セルの削除・挿入等を行っても、動作に影響を受けないワークシート関数を使用しています。

　今仮に、元の表が存在しているシートのシート名が「入力」であり、「作業」というシート名のシートのA列～E列を作業列として使用して、「降順」というシート名のシートにIDを降順に並べ替えた表を表示させ、「昇順」というシート名のシートにIDを昇順に並べ替えた表を表示させるものとします。
　又、同じIDを持つデータの中でD列の商品名が異なるものに関しては、商品名を昇順に並べるものとします。

　まず、作業シートのA1セルに次の関数を入力して下さい。

=COUNT(入力!$A:$A)

　次に、作業シートのA2セルに次の関数を入力して下さい。

=COUNT(入力!$D:$D)

　次に、作業シートのA3セルに次の関数を入力して下さい。

=IF(COUNT($C:$C),10^ROUNDUP(LOG10(COUNT($C:$C)+1),0),"")

　次に、作業シートのA3セルに次の関数を入力して下さい。

=COUNT($D:$D)

　次に、作業シートのB2セルに次の関数を入力して下さい。

=IF(INDEX(入力!$A:$A,ROW())="","",COUNTIF(入力!$A:$A,">"&INDEX(入力!$A:$A,ROW()))+(INDEX(入力!$A:$A,ROW())<CHAR(1))*$A$1)

　次に、作業シートのC2セルに次の関数を入力して下さい。

=IF(INDEX(入力!$D:$D,ROW())="","",COUNTIF(入力!$D:$D,">"&INDEX(入力!$D:$D,ROW()))+(INDEX(入力!$D:$D,ROW())<CHAR(1))*$A$2)

　次に、作業シートのD2セルに次の関数を入力して下さい。

=IF(AND(ISNUMBER($C2),COUNT($B$1:$B2)),RANK(LOOKUP(9E+99,$B$1:$B2),$B:$B)*$A$3+$C2,"")

　次に、作業シートのE2セルに次の関数を入力して下さい。

=IF(AND(ISNUMBER($C2),COUNT($B$1:$B2)),RANK(LOOKUP(9E+99,$B$1:$B2),$B:$B,1)*$A$3+$C2,"")

　次に、作業シートのB2～E2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。

　次に、降順シートのA2セルに次の関数を入力して下さい。

=IF(ROWS($2:2)>作業!$A$4,"",IF(IF(ROWS($2:2)=1,FALSE,INT(LARGE(作業!$D:$D,ROWS($2:2))/作業!$A$3)=INT(LARGE(作業!$D:$D,ROWS($2:2)-1)/作業!$A$3)),"",INDEX(入力!$A:$A,MATCH(9E+99,作業!$B$1:INDEX(作業!$B:$B,MATCH(LARGE(作業!$D:$D,ROWS($2:2)),作業!$D:$D,0))))))

　次に、降順シートのB2セルに次の関数を入力して下さい。

=IF($A2="","",VLOOKUP($A2,入力!$A:$D,COLUMNS($A:B),FALSE))

　次に、降順シートのD2セルに次の関数を入力して下さい。

=IF(ROWS($2:2)>作業!$A$4,"",INDEX(入力!$D:$D,MATCH(LARGE(作業!$D:$D,ROWS($2:2)),作業!$D:$D,0)))

　次に以下の操作を行って、降順シートのA2セルとB2セルに条件付き書式を設定して下さい。
【ExcelのバージョンがExcel2007以降の場合】
降順シートのA2～B2のセル範囲をまとめて範囲選択
　　↓
Excelウィンドウの左上の辺りにある[ホーム]タブをクリック
　　↓
現れた「スタイル」グループの中にある[条件付き書式]ボタンをクリック
　　↓
現れた選択肢の中にある[ルールの管理]をクリック
　　↓
現れた「条件付き書式ルールの管理」ダイアログボックスの中にある[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの[数式を使用して、書式設定するセルを決定]をクリック
　　↓
「次の数式を満たす場合に値を書式設定」と記されている欄に次の数式を入力

=$D2<>""

　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[罫線]タブをクリック
　　↓
現れた[クリア]ボタンをクリック
　　↓
「罫線」欄の中の左辺と右辺をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの[数式を使用して、書式設定するセルを決定]をクリック
　　↓
「次の数式を満たす場合に値を書式設定」と記されている欄に次の数式を入力

=OR($A3<>"",AND($D2<>"",$D3=""))

　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[罫線]タブをクリック
　　↓
現れた[クリア]ボタンをクリック
　　↓
「罫線」欄の中の左辺と右辺、及び下辺をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの中にある[適用]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの中にある[OK]ボタンをクリック

【ExcelのバージョンがExcel2003以前の場合】
降順シートのA2～B2のセル範囲をまとめて範囲選択
　　↓
「メニュー」バーの[書式]ボタンをクリック
　　↓
現れた選択肢の中にある[条件付き書式]をクリック
　　↓
現れた「条件付き書式の設定」ダイアログボックスの「条件1(1)」の左端の欄をクリック
　　↓
現れた選択肢の中にある「数式が」をクリック
　　↓
「条件付き書式の設定」ダイアログボックスの「条件1(1)」の左から2番目の欄に次の数式を入力

=$D2<>""

　　↓
「条件付き書式の設定」ダイアログボックスの「条件1(1)」の[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[罫線]タブをクリック
　　↓
現れた[クリア]ボタンをクリック
　　↓
「罫線」欄の中の左辺と右辺をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式の設定」ダイアログボックスの[追加]ボタンをクリック
　　↓
現れた「条件付き書式の設定」ダイアログボックスの「条件2(2)」の左端の欄をクリック
　　↓
現れた選択肢の中にある「数式が」をクリック
　　↓
「条件付き書式の設定」ダイアログボックスの「条件2(2)」の左から2番目の欄に次の数式を入力

=OR($A3<>"",AND($D2<>"",$D3=""))

　　↓
「条件付き書式の設定」ダイアログボックスの「条件2(2)」の[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[罫線]タブをクリック
　　↓
現れた[クリア]ボタンをクリック
　　↓
「罫線」欄の中の左辺と右辺、及び下辺をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式の設定」ダイアログボックスの[OK]ボタンをクリック

　そろそろ、このサイトの回答欄に入力可能な文字数制限を超えそうですので、残りは又後で回答させて頂きます。

回答No.2

chonami

まずは、空欄を埋めてから作業するのが一番でしょう。

出力された表でA1セルを選択→Ctrl+G→セル選択→空白セル→OK

で表内の空白セルが選択されます。

その状態で＝を入力し↑（上矢印キー）を押し、Ctrl+Enterを押します。

すると、空白セルが埋まりますので、A～C列を選択しコピー＆値貼り付けします。
それから並べ替えをされてはどうでしょうか？

必要でしたら、空白セルを生めて値貼り付けまでをマクロ記録などにしておいて個人用マクロブックなどに保存しておけば毎回ここまでの作業を自動でやることができます。

お礼

ご回答ありがとうございました。

回答No.1

A88No8

こんにちは
　なかなか工数を無償提供してくださる方は少ないと思うのでご自分で何とかする場合の助言をしますね。

　普通のプログラミングなら次のような考え方でVBAのリファレンスマニュアルを見ながら作ります（(2)と(4)は同じループ内で処理してしまいます。また質問者さんの提示された体裁は罫線が無くても見やすい仕様なので罫線の処理は省略します(^^;）。

　VBAの特徴でもあるOOPSは使用しない（VBA処理系を熟知していないと理解が困難なため）。

SUB OriginalSort()
REM ●前処理
　REM ワークシートを選択
　REM (1)商品名が空欄になる最終行を求める処理を書く
　REM (2)ID列で空欄を次の異なるIDになるまで同じIDで埋める処理（最終行で終わり）を書く
　REM (2)氏名列で空欄に次の異なる氏名が来るまで同じ氏名で埋める処理（最終行で終わり）を書く
　REM (2)住所列で空欄に次の異なる住所が来るまで同じ住所で埋める処理（最終行で終わり）を書く

REM ●ソート処理
　REM (3)2行目から前処理で求めた最終行までをIDで降順、商品名で昇順ソートする処理を書く

REM ●後処理
　REM (4)ID列でデータ最初の行を残し次の異なるIDになるまで空欄を作る処理（最終行で終わり）を書く
　REM (4)氏名列でで0他最初の行を残し次の異なる氏名になるまで空欄を作る処理（最終行で終わり）を書く
　REM (4)住所列で最初の行を残し次の異なる住所になるまで空欄を作る処理（最終行で終わり）を書く
END

　σ(^_^;の表計算ソフトは、KING OFFICE（VBAの無いExcel2003相当）のためVBAマクロは試せないないのですが、この仕事をマクロの使えないKING OFFICEの表計算ソフトでσ(^_^;が手作業するなら…

(1)準備
　データを入力するID列から住所列までの使用するセル範囲を条件付き書式で「上の行と同じなら非表示（例えば文字の色を白にする）」に設定します。
(2)毎日の作業
　ID～氏名～住所の列は、入力時にデータで埋めます（入力作業時CTRL+Dで上行のセルを簡単にコピーできる）。
　ID～氏名～住所の列は、同じデータの入った行は(1)の処理のため、最初の行しか表示されません（というかシートと同じ色なので普通は見えない）。
(3)ソート処理
　2行目から最終行まで選択してIDで降順、商品で昇順の指定をしてソートする。
　ソート一発で終わりですm(__)m

お礼

ご回答ありがとうございました。