エクセルの並び替えについて

質問

エクセルの並び替えについて質問させて頂きます。

下記のようにエクセルに文字列を入力しています。

Ａ　　　　　　　　　Ｂ　　　　　　
1　10011 　　ホワイト
2　　　　　　　　ブラック
3　　　　　　　　ブルー
4　10002　　 ブラウン
5　　　　　　レッド
6　　　　　　イエロー
7　10005　　 パープル　
8　　　　　　ネイビー
9　　　　　　サックス

Ａ１に「10011」と入力してありますが、「10011」という品番に対してセルＢ1・Ｂ2・Ｂ3のホワイト・ブラック・ブルーのカラーが存在するという意味です。

ここで並べ替えを行いセルＡ１・Ａ４・Ａ７の数字を若い順（昇順）に並べ替えを行った場合、品番のみ昇順となり、カラーがその品番に対してキレイに並び替えが行われないのです。

最終的に下記のように並び替えを行いたいのですが、どのように並び替えを行えばよろしいでしょうか？よろしくお願い致します。

Ａ　　　　Ｂ　　　　Ｃ　　　…　　　
1　10002　　 ブラウン
2　　　　　　レッド
3　　　　　　イエロー
4　10005　　 パープル　
5　　　　　　ネイビー
6　　　　　　サックス　
7　10011 ホワイト
8　　　　　　ブラック
9　　　　　　ブルー

回答No.8

mitarashi

マクロで、データに手を加えることなく、並び替える事例です。その場で並び替えるのではなく、F1から並び替えた結果を貼り付けます。先日別の質問に回答した物に若干修正を加えました。
本来A列には全て品番を入れ、見苦しければ条件付書式で白色フォントにでもするのが本筋とは思いますが、救済策です。
長いですが、最後のソートのサブルーチン等は自分でも意味が分かっている訳ではありませんので．．．
Sub test()
'並べ替える範囲の先頭セル、結果の出力セル、並べ替えるブロックの行数、同列数
'並べ替えるキーとなる値はブロックの左上隅の値とする
Call blockSort(ActiveSheet.Range("a1"), ActiveSheet.Range("f1"), 3, 2)
End Sub

Private Sub blockSort(startRange As Range, destRange As Range, blockHeight As Long, blockWidth As Long)
Dim targetRange As Range
Dim myDic As Object, myKey As Variant, myKeyS As Variant
Dim i As Long
Dim keyString As String

Set targetRange = startRange.Resize(blockHeight, blockWidth)
Set myDic = CreateObject("Scripting.Dictionary")
Do Until targetRange.Cells(1, 1).Value = ""
keyString = targetRange.Cells(1, 1).Value
If Not myDic.exists(keyString) Then
myDic.Add keyString, targetRange
Else
'ここはキーの重複対策に一応入れた。重複が無ければ不要。
Do While myDic.exists(keyString)
keyString = keyString & "a"
Loop
myDic.Add keyString, targetRange
End If
Set targetRange = targetRange.Offset(blockHeight, 0)
Loop
myKey = myDic.keys
myKeyS = ShellSortStrings(myKey, 1)
'ブロック先頭セルを結合しているとき、先頭セルのみ指定だと、offsetが意図せぬ動作
Set targetRange = destRange.Resize(blockHeight, blockWidth)
For i = 0 To myDic.Count - 1
myDic(myKey(i)).Copy
targetRange.PasteSpecial (xlPasteAll)
Set targetRange = targetRange.Offset(blockHeight, 0)
Next
Set myDic = Nothing
Application.CutCopyMode = False
End Sub

'Microsoftのシェルソートを改造
'direction 1:昇順、-1 降順
Private Function ShellSortStrings(vArray As Variant, direction As Long) As Variant
Dim lLoop1 As Long
Dim lHold As Long
Dim lHValue As Long
Dim lTemp As String
lHValue = LBound(vArray)
Do
lHValue = 3 * lHValue + 1
Loop Until lHValue > UBound(vArray)
Do
lHValue = lHValue / 3
For lLoop1 = lHValue + LBound(vArray) To UBound(vArray)
lTemp = vArray(lLoop1)
lHold = lLoop1
Do While StrComp(vArray(lHold - lHValue), lTemp) = direction
vArray(lHold) = vArray(lHold - lHValue)
lHold = lHold - lHValue
If lHold < lHValue Then Exit Do
Loop
vArray(lHold) = lTemp
Next lLoop1
Loop Until lHValue = LBound(vArray)
ShellSortStrings = vArray
End Function

回答No.7

hotosys

適当な作業列を使う。
たとえばC列だとすると、
C1:=IF(A1="",OFFSET(C1,-1,0,1,1),A1)
として、A列のデータがある行までコピー。
C列をキーに並び替える。

同じ品番内でA列に数値があるものを必ず最初に表示するために、もう1列作業列使った方が安全かもしれない。
たとえばD列だとすると、上記のC列の
C1:=IF(A1="",OFFSET(C1,-1,0,1,1),A1)
に加えて
D1:=IF(A1="",1,0)
として、A列のデータがある行までC:D列をコピー。
C列(優先)D列(2番目)をキーに並び替える。
これで同じ品番内でA列が空白でないものが先頭になる。

回答No.6

imogasi

こんな質問が出るのは、A列に空白セルがあるためだ。
絶対エクセルのソートはセルの値が具体的にその列に無いとそれに基づいてソートしない。だからあれこれ逡巡しないこと。
ーー
A列か作業列にコードを埋める方法を考えること。
（１）A列空白行に直接コードを埋めて、上行と同じ行は白色にする
（２）空白列C列に番号を振る
ーー
（２）は　=IF(A2<>"",A2,C1)
C列第１行は空白にすること
最終行は人間が判断すること。
C列はソートする前に必ず式を消して、値化しておくこと。
ソート時に災いする。
ーーー
（１）は式の複写でやれないので、VBAでも使わないと実現しない。
Sub test01()
d = Range("B65536").End(xlUp).Row
For i = 1 To d
If Cells(i, "A") = "" Then
Cells(i, "A") = m
Cells(i, "A").Font.Color = vbWhite
Else
m = Cells(i, "A")
End If
Next i
End Sub
セルの文字の白色化もやっている。

回答No.5

ts3m-ickw

基本的な考え方は他の回答と同じなのですが、A欄の数が多いと大変です。
そこでこんなやりかたはいかがでしょう。
(1)R1C1形式にしておく。
(2)A欄の空欄を =R[-1]C に置換する。
(3)シート全体を選択しコピーする。
(4)新しいシートに形式を選択して貼り付け→値(V)
(5)並び替え実行。
(6)R1C1形式を元に戻す。

回答No.4

mu2011

NO3です。
以下の点を訂正します。
２）数式バーに=A1を入力、ctrl＋enterキーを同時押下→フォントを白に変更

回答No.3

mu2011

Ａ列の空白部分に先頭番号をコピーして「並び替え」を実施する方法は如何でしょうか。
（１）Ａ列のデータ範囲を選択→編集→ジャンプ→セル選択→「空白」をチェック→ＯＫ
（２）数式バーに=A2を入力、ctrl＋enterキーを同時押下→フォントを白に変更
（３）Ａ列を優先キーで並び替え

回答No.2

wanwan1014

私が思いついたのは、A列の空白セルを無くして全てに100XXを入力する方法です。空白にしておかなければいけませんか？
件数が少なければすぐに空白を埋められると思いますが、件数が多いと大変ですね。

もっといい方法があるといいですね。

回答No.1

keirika

１．Ａ列の空白部分に該当する数値を入力。
２．入力をしたセルの文字色を「白」に変更。
３．並び替えを実行する。

以上です。