列と列を入れ替える

前の記事は列の「並び替え」でしたが
並び替えよりも
入れ替えをしたいなっと思うことの方が
個人的には多いです。

1列丸ごと入れ替えます。
ですので列の途中まで同士を入れ替えたい場合は
他の方法でお願いします。

セルの値を配列に格納する方法を知ってからは
かなりこの方法を重宝しています。

今回も
列全体をVariant型の配列に入れるのに使います。

まず、
1つ目の列の値ををVariant型の配列に入れます。
それから2つ目の列の値を1つ目の列にいれます。
最後にVariant型の配列に入れた1つ目の列の値を
2つ目の列に入れます。

この方法ではフォーマットは移されません。

コードはこちら

Sub ExchangeC(C1 As Integer, C2 As Integer)
'列の入れ替え
'C1=入れ替える列の番号1
'C2=入れ替える列の番号2

    '1つ目の列の値を変数に入れる
    Dim vData As Variant
    vData = Columns(C1)
   
    '2つ目の列の値を1つ目の列に入れる
    Columns(C1) = Columns(C2).Value
   
    '1つ目の列の値を2つ目の列に入れる
    Columns(C2) = vData
   
End Sub

Sub macro110227a()
'ExchangeCの使用例

    '2行目と3行目を入れ替える
    Call ExchangeC(2, 3)
   
End Sub

フォーマットも含む
列の入れ替えをしたい場合の方法も考えます。

「切り取り」と「切り取ったセルの挿入」を使います。

まず、
列番号の大きいほうの列を切り取ります。
それを列番号の小さいほうの列の
次の列に挿入します。

次に、列番号の小さいほうの列を切り取ります。
それを列番号の大きいほうの列の
次の列に挿入します。
これで入れ替えができます。

コードはこちら

Sub ExchangeC2(C1 As Integer, C2 As Integer)
'列の入れ替え
'C1=入れ替える列の番号1
'C2=入れ替える列の番号2

    'C1<C2にする
    If C1 > C2 Then
        Dim i As Integer
        i = C2
        C2 = C1
        C1 = i
    End If
   
    'C2を切り取り、(C1+1)列に挿入
    Columns(C2).Cut
    Columns(C1 + 1).Insert Shift:=xlToRight
   
    'C1を切り取り、(C2+1)列に挿入
    Columns(C1).Cut
    Columns(C2 + 1).Insert Shift:=xlToRight
   
End Sub

Sub macro110227b()
'ExchangeC2の使用例

    '2行目と4行目を入れ替える
    Call ExchangeC2(2, 4)
   
End Sub

macro110227b実行前のシート：

4列目切り取り挿入後のシート：

2列目切り取り挿入して
入れ替え終了後のシート：

上の画像のようにフォーマットも移されます。

列の「並び替え」

列の「並び替え」
ってどんな場合に使うのかわからないけれども
とりあえず
1っ回くらいやっておこう。

以前の記事「並び替えをする 」で使った
「並び替え」のテンプレートの引数を変えるだけです。

Orientation:=xlTopToBottom

の箇所を

Orientation:=xlSortRows

にすると列の「並び替え」ができます。

「Orientation:=xlTopToBottom」についてですが
マクロ自動記録で記録したコードは
このようになっていたので
そのように以前の記事で使ったのですが、

Orientation:=xlSortColumns

でも同じように
行の「並び替え」を指定できるようです。

xlSortRowsなんていうと
RowsのSortで
行の並び替えをしそうですが、
Rows方向(=Orientation)の並び替え
= 水平方向の並び替え
= 列の並び替えです。

xlSortColumnsも同様に
Columns方向の並び替え
= 垂直方向の並び替え
= 行の並び替えです。

行のタイトル順に
列を「並び替える」シチュエーションしか
思い浮かばないので
それをしてみます。

コードはこちら

Sub macro110226a()
'列の並び替え
'１行目を基準に昇順

    Range("A1:G11").Sort _
        Key1:=Range("A1"), Order1:=xlDescending, _
        Header:=xlNo, OrderCustom:=1, _
        MatchCase:=False, Orientation:=xlSortRows, _
        SortMethod:=xlPinYin
       
End Sub

実行前のシート：

実行後のシート：

手動での列の「並び替え」は、
「並び替え」のダイアログの左下の[オプション]ボタンを押せば
並び替えの方向を選択できます。

ランダムに並び替える

行の並び替えや
配列のソートについての記事が続いていますが
その過程で
並び替えの効果を見やすくするために
行や配列をランダムで並び替えた方がいいかなと
思うこともありました。

ある名簿で
ランダムで並び替えて順番を決める。

一定量の英文法の問題があり
それを毎回ランダムな順番で出題したい。

いままでは
順番に並べることばかり考えていましたが
ランダムで並び替えたい場面はたくさんありますね。

ランダムといえば
やっぱり乱数を使うしかない！

ということで
乱数を使ってランダムに並び替えたいと思います。

基本的な流れは
あるランダムに並び替えたい範囲があるとします。
その範囲に1列追加して
その列に乱数を入れます。

乱数を入れた列を加えた並び替えたい範囲を
乱数の列を基準に昇順か降順の並び替えをする。

乱数を入れた列を削除する。

これでランダム？に並び替えられます。
あくまで個人的にランダムと思われる程度で
厳密にはわかりません。

具体的に実行してみます。

今、下のシートの画像のように
A列に「あいうえおかきくけこ」と入力してあります。
これをランダムに並び替えます。

B列に乱数を入れて
この列を基準に並び替えます。
それからB列を削除します。

コードはこちら

Sub macro110219a()
'ランダムに並び替える

    Dim i As Integer
   
    Randomize
    '乱数を入れる
    For i = 1 To 10
        Cells(i, 2) = Rnd()
    Next i
   
    '乱数で昇順並び替え
    Range("A1:B10").Sort _
        Key1:=Range("B1"), Order1:=xlAscending, _
        Header:=xlNo, _
        OrderCustom:=1, _
        MatchCase:=False, _
        Orientation:=xlTopToBottom, _
        SortMethod:=xlPinYin
       
    '乱数を削除
    Range("B1:B10").Clear
   
End Sub

実行課程の様子です。

B列に乱数を入れる

B列を昇順に並び替え

B列を削除

配列の場合はどうやろうかな？

数式を値に変換する2

以前の記事「 数式を値に変換する 」で
コピペを使って
数式を値に変換する方法を紹介しました。

今回はコピペを使わずに
数式を値に変換してみます。

セルA1に、1
セルB1に、2
セルC1に、=A1+B1
と入力してあるとします。

この状態で以下のプロパティは
次のようになります。

Range("C1").Value = 3
Range("C1") = 3
Range("C1").Formula = "=A1+B1"

Range("C1")　が値で、
Range("C1").Formula　が数式に当たります。

つまり
Range("C1")の数式を値に変換するには
Range("C1")をRange("C1")に入れればよいので

Range("C1") = Range("C1")

になります。

これだけ見ると何がしたいのか
さっぱりわからないコードです。

複数のセルを含む範囲
例えばRange("C1:C3")の数式を値に変換するには

Range("C1:C3") = Range("C1:C3").Value

のように右辺にValueをつける必要があります。

1つのセルだけのときもValueをつけると覚えたほうが
いいようです。

2次元配列をソートする

以前の記事「 配列をソートする 」では
1次元の配列をソートする方法でしたが
今回は2次元配列をソートします。

上記の記事で使ったBubbleSort2を改造して
2次元配列をソートできるようにしました。

BubbleSortのほうが
大きい配列をソートするのを
速くできるようなので
BubbleSortのほうにしました。

コードはi番目の要素と
i+1番目の要素を入れ替えるところを変更しただけです。

2次元配列をソートするためには
i番目の「行」と
i+1番目の「行」を入れ替えなくてはいけません。

行の要素を１つ１つ入れ替えるために
変数ｊを使ったFor文を使います。

コードはこちら

Function BubbleSort3(TempArray As Variant, Col As Integer, Order As Integer)
'Option Base 1で実行
'Col= ソートの基準の列
'Order = 0 昇順
'Order = 1 降順

    Dim Temp() As Variant
    ReDim Temp(1, UBound(TempArray, 2))
   
    Dim i As Integer, j As Integer
    Dim NoExchanges As Integer

    ' ループの終わりでNoExchanges = Trueで終了
    Do
        NoExchanges = True

        For i = 1 To UBound(TempArray, 1) - 1
            If Order = 0 Then
                '昇順
                If TempArray(i, Col) > TempArray(i + 1, Col) Then
                    NoExchanges = False
                    For j = 1 To UBound(TempArray, 2)
                        Temp(1, j) = TempArray(i, j)
                    Next j
                    For j = 1 To UBound(TempArray, 2)
                        TempArray(i, j) = TempArray(i + 1, j)
                    Next j
                    For j = 1 To UBound(TempArray, 2)
                        TempArray(i + 1, j) = Temp(1, j)
                    Next j
                End If
            ElseIf Order = 1 Then
                '降順
                If TempArray(i, Col) < TempArray(i + 1, Col) Then
                    NoExchanges = False
                    For j = 1 To UBound(TempArray, 2)
                        Temp(1, j) = TempArray(i, j)
                    Next j
                    For j = 1 To UBound(TempArray, 2)
                        TempArray(i, j) = TempArray(i + 1, j)
                    Next j
                    For j = 1 To UBound(TempArray, 2)
                        TempArray(i + 1, j) = Temp(1, j)
                    Next j
                End If
            End If
        Next i
    Loop While Not (NoExchanges)

End Function

これを使って
下の画像のシートをソートしてみます。

範囲A2:G11を配列に格納して、
A列を基準に降順にソートします。
ソートした配列をA20を基準に出力します。

ここではわかりやすく
シートのデータを配列の内容に使っています。
シートのデータ（セル）を並び替えるだけなら
「並び替え」をVBAで実行するのがいいと思います。

Sub macro110212a()
'BubbleSort3使用例
   
    'ソートする配列
    Dim vData As Variant
    vData = Range("A2:G11")
   
    '配列vDataを1列目を基準に降順にソートする
    Call BubbleSort3(vData, 1, 0)
   
    'ソート後の配列を出力
    Range("A20").Resize(UBound(vData), UBound(vData, 2)) = vData
   
End Sub

実行結果：

配列は行単位で扱うことができないようです。

例えば
上のコードで使ったVariant型の配列でいうと
vData(3)
で3行目を扱うことはできませんでした。

vDataをウォッチウィンドウで見てみると
このようになっています。

これを見ると
vData(3)で3行目を扱えそうですが
できませんでした。

ちなみに
vData(3)をウォッチウィンドウで見てみると
値は「インデックスが有効範囲にありません」となります。

シートの並び替えをする

以前の記事「 配列をソートする 」の続きで
2次元配列をソートする方法を考えていたのですが、
その前にシートの「並び替え」についてVBAで実行する方法についてのまとめ。

「並び替え」を手動で実行するには
並び替えたい範囲を選択してから
[データ]-[並び替え]を選択します。

そうすると下の画像のようなダイアログが出るので
各条件を指定して実行します。

Excel2000はキーが3つまでしか指定できませんが
Excel2007はもっとキーが増えているようです。

ここではExcel2000の場合です。

下の画像は、
ある時点の価格.comのノートパソコンランキングの
TOP10の情報をシートに入れたものです。
これを使って「並び替え」をします。

今は順位（A列昇順）で並んでいます。

第一優先を最安価格(C列)、昇順
第二優先を液晶(D列)、降順
で「並び替え」ます。

これは液晶、降順で並び替えてから、
最安価格、昇順で並び替えたものと同じです。
もし最安価格が同じなら
液晶サイズが大きいほうが上になります。

並び替えるとこのようになります。

同じことをVBAで実行するコードは次のようになります。

    Range("A2:G11").Sort _
        Key1:=Range("C2"), Order1:=xlAscending, _
        Key2:=Range("D2"), Order2:=xlDescending, _
        Header:=xlNo, _
        OrderCustom:=1, _
        MatchCase:=False, _
        Orientation:=xlTopToBottom, _
        SortMethod:=xlPinYin

Range、Key、Orderのところだけ変更すれば
大体の「並び替え」はできそうです。

各引数の詳細はVBAヘルプにあります。

最後にキーを3つ使った「並び替え」のVBAコードの
テンプレートを作っておきます。

    'ヘッダーを含まない範囲の並び替え
    Range("").Sort _
        Key1:=Range(""), Order1:=xlAscending / xlDescending, _
        Key2:=Range(""), Order2:=xlAscending / xlDescending, _
        Key3:=Range(""), Order3:=xlAscending / xlDescending, _
        Header:=xlNo, _
        OrderCustom:=1, _
        MatchCase:=False, _
        Orientation:=xlTopToBottom, _
        SortMethod:=xlPinYin

最初のRangeに
ヘッダーを含まない並び替える範囲を入れます。
３つKeyのRangeには
それぞれ列の並び替える範囲内の一番上のセルを入れます。
あとは３つKeyのOrderを
xlAscending か xlDescendingのどちらかにすればOKです。
Key2、Key3は不要ならその行を削除してください。

変更する箇所はこの画像を参考にしてください。