この記事では、表の列の値をキーに別の表データを同じに並び替える方法についてご説明します。
独自の規則の並び方をしているデータをキーにソートする
単純に数値や文字(列)、アルファベットなどをソートする場合は、Excelの並び替え機能で並び替えることができます。
ですが、独自の規則に基づいて並んでいるデータの並びを、別の表のデータも同じ通りに並び替えたい場合にExcelの並び替え機能だけでは正しく並び替えることができません。
例えば、以下はとある学級の生徒の点数データですが、独自の規則性に基づいて並んでおり、左側の表の生徒一覧と右側の表の生徒一覧とでは生徒名の並びが異なります。
今回のマクロでは、左側の表にある生徒名の並びの通りと同じに、右側の表を並び替えることを実現します。
並び替えたイメージは下の通りです。
【動画】表の列の値をキーに別の表データを同じに並び替える実際の動き
本題に入る前に、まずは次の動画をご覧ください。
左の表と右の表では、生徒名の並びが異なっています。
マクロを実行すると、左の表にある生徒名の並びに合わせて右の表が並び替わります。
また、右側の表の生徒が左側の表に存在しない場合は表の末に並べて表示しています。
マクロ作成の流れ
取得した生徒名は右の表が何行目にあるのか特定するのに使います。
Excelファイルの例
今回は先ほどお見せしたExcelファイルを使います。
左側の表には生徒の1月から6月の点数データが、右側の表には生徒の7月から12月の点数データが入力されています。
左側の表と右側の表は生徒の並びが異なります。
左側の表と右側の表は生徒の並びを合わせたいため、左側の生徒の並びに合わせて右側の表の並びを並び替えます。
生徒名の並びは独自の規則に基づいて並んでいるので、Excelの並び替え機能では正しく並び替えができないのでマクロで正しく並び替えます。
コードの例
Excelのマクロのコード(例)
Option Explicit
Sub sortData()
Dim getVal() As Variant '各生徒の名前と点数を格納する配列
Dim rCnt As Long 'セルの行のカウント用カウンタ
Dim lCnt As Integer 'セルの列のカウント用カウンタ
Dim fCnt As Integer '検索用カウンタ
Dim fltValCnt As Integer 'Filter関数で取得した複数の値用のカウンタ
Dim getValRCnt As Integer '配列の一次元の要素数カウント用カウンタ
Dim lFndStrClmPos As Integer '表(1月～6月)の生徒名の列位置
Dim lBgnRowPos As Integer '表(1月～6月)の開始行
Dim lEndRowPos As Integer '表(1月～6月)の終端行
Dim rBgnRowPos As Integer '表(7月～12月)の開始行
Dim rEndRowPos As Integer '表(7月～12月)の終端行
Dim rBgnClmPos As Integer '表(7月～12月)の開始列
Dim rEndClmPos As Integer '表(7月～12月)の終端列
Dim fndData As Range '検索したデータ
Dim fVal As Variant 'フィルターで取得した値
Dim nfDataFlg As Boolean '右側の表の生徒が左側の表に存在しないかを判定するフラグ
'→True ：存在しない
' False：存在する
Dim rowPos() As String '左側の生徒と合致する、右側の表の生徒行位置用配列
'配列の一次元の要素数カウント用カウンタを初期化する
getValRCnt = 0
'フラグを初期化する
nfDataFlg = False
'配列の要素数を変更する
ReDim rowPos(getValRCnt)
'************************************************************************
'表(1月～6月)の各値を変数に設定
'************************************************************************
'表(1月～6月)の生徒名の列位置
lFndStrClmPos = 2
'表(1月～6月)の各データの開始行
lBgnRowPos = 3
'表(1月～6月)の各データの終端行
lEndRowPos = 22
'************************************************************************
'表(7月～12月)の各値を変数に設定
'************************************************************************
'表(7月～12月)の各データの開始行
rBgnRowPos = 3
'表(7月～12月)の各データの終端行
rEndRowPos = 22
'表(7月～12月)の各データの開始列
rBgnClmPos = 11
'表(7月～12月)の各データの終端列
rEndClmPos = 17
'配列の要素数を変更する
'→引数 1つ目：表(7月～12月)のデータ部の行数
' 2つ目：表(7月～12月)のデータ部の列数
ReDim getVal(lEndRowPos - lBgnRowPos, _
rEndClmPos - rBgnClmPos)
'表(1月～6月)の生徒数分ループを行う
For rCnt = 0 To lEndRowPos - lBgnRowPos
ReDim Preserve rowPos(rCnt)
'表(1月～6月)の生徒名を、表(7月～12月)に対して検索する
Set fndData = Range(Cells(rBgnRowPos, rBgnClmPos), Cells(rEndRowPos, rBgnClmPos)).Find(Cells(rCnt + lBgnRowPos, lFndStrClmPos).Value, LookAt:=xlWhole)
If Not fndData Is Nothing Then
'検索した生徒名が存在する場合
'表(7月～12月)の列数分ループを行う
For lCnt = 0 To rEndClmPos - rBgnClmPos
'表(1月～6月)の生徒名に合致した表(7月～12月)の生徒の各データ(生徒名と点数)を配列に格納する
getVal(getValRCnt, lCnt) = Cells(fndData.Row, rBgnClmPos + lCnt)
Next lCnt
'左の表の生徒が右側にも存在する場合に、その右側の表に存在する生徒の行位置を取得する
rowPos(getValRCnt) = LTrim(Str(fndData.Row))
'配列の一次元の要素数カウント用カウンタの値を増やす
'※存在する生徒のデータだけ配列に入れて無駄な空白行を増やしたくないので、
' 生徒名が存在しない場合はカウンタの値を増やさない
getValRCnt = getValRCnt + 1
End If
DoEvents
Next rCnt
'ここからは、右側の表の生徒が左側の表に存在するか確認し、
'存在しなければ、配列「getVal」に格納する
'配列rowPosの要素数分ループする
For fCnt = 0 To UBound(rowPos)
'フィルター関数を使い、右の表の生徒が左の表にも存在する行位置を配列rowPosから抜き出す
fVal = Filter(rowPos, fCnt + lBgnRowPos)
If (UBound(fVal) <> -1) Then
'右の表の生徒が左の表にも存在する場合
'フィルター関数は部分一致で取得されてしまい、関係ない行位置まで複数抜き出してしまうので、
'その複数の値の中から合致する値を取り出すために、取得した複数の値の個数分ループする
'※例えば、フィルター関数を使って配列に対し値「3」を検索した場合、「3」と「13」が取得されてしまう(フィルター関数は部分一致なので)
' あくまで値「3」を検索したいのであって、「13」はいらない
For fltValCnt = 0 To UBound(fVal)
If fVal(fltValCnt) = fCnt + lBgnRowPos Then
'フィルター関数で取得した配列の値が、右の表の生徒が左の表にも存在する行の位置と合致する場合
'右側の表の生徒が左側の表に存在しないかを判定するフラグをFalseに設定する(Falseは存在するの意)
nfDataFlg = False
'ループを抜ける
Exit For
Else
'フィルター関数で取得した配列の値が、右の表の生徒が左の表にも存在する行の位置と合致しない場合
'右側の表の生徒が左側の表に存在しないかを判定するフラグをTrueに設定する(Trueは存在しないの意)
nfDataFlg = True
End If
Next fltValCnt
Else
'右の表の生徒が左の表にも存在しない場合
'右側の表の生徒が左側の表に存在しないかを判定するフラグをTrueに設定する(Trueは存在しないの意)
nfDataFlg = True
End If
If nfDataFlg Then
'右の表の生徒が左の表にも存在しない場合
'表(7月～12月)の列数分ループを行う
For lCnt = 0 To rEndClmPos - rBgnClmPos
'表(7月～12月)の生徒の各データ(生徒名と点数)を配列に格納する
getVal(getValRCnt, lCnt) = Cells(fCnt + lBgnRowPos, rBgnClmPos + lCnt)
Next lCnt
getValRCnt = getValRCnt + 1
End If
DoEvents
Next fCnt
'配列のデータを表(7月～12月)に出力する
Range(Cells(rBgnRowPos, rBgnClmPos), Cells(rEndRowPos, rEndClmPos)).Value = getVal
End Sub
コードの解説
注目すべきコード①
最初に見て頂きたいのは39行目から61行目です。
'************************************************************************
'表(1月～6月)の各値を変数に設定
'************************************************************************
'表(1月～6月)の生徒名の列位置
lFndStrClmPos = 2
'表(1月～6月)の各データの開始行
lBgnRowPos = 3
'表(1月～6月)の各データの終端行
lEndRowPos = 22
'************************************************************************
'表(7月～12月)の各値を変数に設定
'************************************************************************
'表(7月～12月)の各データの開始行
rBgnRowPos = 3
'表(7月～12月)の各データの終端行
rEndRowPos = 22
'表(7月～12月)の各データの開始列
rBgnClmPos = 11
'表(7月～12月)の各データの終端列
rEndClmPos = 17
以上のコードは、左と右の表が持つ各位置情報をマクロが参照するのに必要な値を設定しているコードです。
例えば、左側の生徒が右側の表には何行目の何列目に存在するのかを特定したり、生徒の各データが何列までを対象にするのか、などの情報を以上のコードで設定しておきます。
以上のコードだけでは単に数値しか見えないので以下の画像で図示しておきます。
ちなみに今回のマクロでは、列の指定は(アルファベットではなく)全て数値で設定しています。
例えば数値が2ならB列、数値が3ならC、数値が17ならQ列になります。
B列やC列なら2列目と3列目なのはすぐに分かりますが、Q列は17列目だとパッと出てこないかもしれません。
そこで、Q列のようなセルが、何列目にあるのか特定するにはExcel関数のColumn関数を使うとすぐに特定できます。
使い方は次の記事で詳しく説明しているのでそちらを参考にされるとよいです。
【Excel関数】セルが左から何番目の列にあるのか特定する方法
注目すべきコード②
次に見て頂きたいのは67行目と68行目です。
'配列の要素数を変更する
'→引数 1つ目：表(7月～12月)のデータ部の行数
' 2つ目：表(7月～12月)のデータ部の列数
ReDim getVal(lEndRowPos - lBgnRowPos, _
rEndClmPos - rBgnClmPos)
以上のコードは、並び替える生徒データを格納する配列getValの要素数を変更しているコードです。
この配列に並び替えた生徒データを格納後、右側の表に出力します。
ちなみに配列のデータを、174行目で出力しています。
'配列のデータを表(7月～12月)に出力する
Range(Cells(rBgnRowPos, rBgnClmPos), Cells(rEndRowPos, rEndClmPos)).Value = getVal
注目すべきコード③
次に見て頂きたいのは71行目です。
'表(1月～6月)の生徒数分ループを行う
For rCnt = 0 To lEndRowPos - lBgnRowPos
以上のfor文は、左の表の生徒数分処理を繰り返すためのループになります。
表には20人分の生徒データが存在するので、20回ループします。
注目すべきコード④
次に見て頂きたいのは76行目です。
'表(1月～6月)の生徒名を、表(7月～12月)に対して検索する
Set fndData = Range(Cells(rBgnRowPos, rBgnClmPos), Cells(rEndRowPos, rBgnClmPos)).Find(Cells(rCnt + lBgnRowPos, lFndStrClmPos).Value, LookAt:=xlWhole)
以上のコードは、左側の生徒が右側の表の何行目に存在するのかを検索して特定する処理です。
左側の表にある生徒名をFind関数の引数に設定して実行することで、左側の表の何行目に特定することができます。
何行目かを取得するには、find関数を実行した後に変数fndDataからのrowプロパティから取得することができます。
以上は、右側の表の8行目に生徒名が存在してることを示しています。
注目すべきコード⑤
次に見て頂きたいのは78行目から98行目です。
If Not fndData Is Nothing Then
'検索した生徒名が存在する場合
'表(7月～12月)の列数分ループを行う
For lCnt = 0 To rEndClmPos - rBgnClmPos
'表(1月～6月)の生徒名に合致した表(7月～12月)の生徒の各データ(生徒名と点数)を配列に格納する
getVal(getValRCnt, lCnt) = Cells(fndData.Row, rBgnClmPos + lCnt)
Next lCnt
'左の表の生徒が右側にも存在する場合に、その右側の表に存在する生徒の行位置を取得する
rowPos(getValRCnt) = LTrim(Str(fndData.Row))
'配列の一次元の要素数カウント用カウンタの値を増やす
'※存在する生徒のデータだけ配列に入れて無駄な空白行を増やしたくないので、
' 生徒名が存在しない場合はカウンタの値を増やさない
getValRCnt = getValRCnt + 1
End If
以上は、生徒データ(生徒名と点数データ)を配列getValに格納しているコードになります。
86行目で行っている一人の生徒データが持つ生徒データを配列に格納する処理を、列の数分繰り返し行っています。(83行目のFor文)
ちなみに、左の表の生徒が右の表に存在するかどうかを63行目のif文で判定しています。
もし左の表の生徒が右の表に存在しなければ配列には何も格納しません。
また、91行目で、行位置が格納されたfndData.Rowの値を配列「rowPos」に格納しています。
この配列に値を格納する目的は、右側の表の生徒が左側の表に存在するかを確認するのに使用するためです。(108行目以降の処理で使用します)
左側の表に存在する生徒以外の生徒名を拾うのには、別途どの行に存在しなかったかを把握する必要があります。
右側の表の生徒が左側の表に存在するかを知っておくことで、逆にどの行の生徒が左の表に存在しないかを把握することができます。
なので91行目で、行位置が格納されたfndData.Rowの値を配列「rowPos」に格納する処理を行います。
注目すべきコード⑥
次に見て頂きたいのは108行目です。
'表(1月～6月)の生徒数分ループを行う
For rCnt = 0 To lEndRowPos - lBgnRowPos
以上のfor文は、右の表の生徒数分処理を繰り返すためのループになります。
このループは、右側の表の生徒が左側の表に存在するかを確認するためのループです。
右側の表のデータ件数が20件あるので、ループの回数は20回で指定しています。
注目すべきコード⑦
次に見て頂きたいのは111行目から151行目です。
'フィルター関数を使い、右の表の生徒が左の表にも存在する行位置を配列rowPosから抜き出す
fVal = Filter(rowPos, fCnt + lBgnRowPos)
If (UBound(fVal) <> -1) Then
'右の表の生徒が左の表にも存在する場合
'フィルター関数は部分一致で取得されてしまい、関係ない行位置まで複数抜き出してしまうので、
'その複数の値の中から合致する値を取り出すために、取得した複数の値の個数分ループする
'※例えば、フィルター関数を使って配列に対し値「3」を検索した場合、「3」と「13」が取得されてしまう(フィルター関数は部分一致なので)
' あくまで値「3」を検索したいのであって、「13」はいらない
For fltValCnt = 0 To UBound(fVal)
If fVal(fltValCnt) = fCnt + lBgnRowPos Then
'フィルター関数で取得した配列の値が、右の表の生徒が左の表にも存在する行の位置と合致する場合
'右側の表の生徒が左側の表に存在しないかを判定するフラグをFalseに設定する(Falseは存在するの意)
nfDataFlg = False
'ループを抜ける
Exit For
Else
'フィルター関数で取得した配列の値が、右の表の生徒が左の表にも存在する行の位置と合致しない場合
'右側の表の生徒が左側の表に存在しないかを判定するフラグをTrueに設定する(Trueは存在しないの意)
nfDataFlg = True
End If
Next fltValCnt
Else
'右の表の生徒が左の表にも存在しない場合
'右側の表の生徒が左側の表に存在しないかを判定するフラグをTrueに設定する(Trueは存在しないの意)
nfDataFlg = True
End If
まずは111目のfilter関数で、配列rowPosに検索したい文字列が含まれているかを検索しています。
配列rowPosは、左の表の生徒が右側にも存在する場合にその生徒が存在する行位置が格納されており、その配列に格納された行位置を一つ一つ確認します。
もし右の表の生徒が左の表にも存在するかを113行目のIf文で判定します。
存在する場合は121行目のfor文以降の処理を、存在しなければ144行目のElse以降の処理を行います。
存在する場合は、さらにフィルター関数で取得した配列(fVal)の値を確認します。
フィルター関数は部分一致(※完全一致はできない)のため、例えば値「13」で検索した場合は値「13」をフィルター関数は配列で返します。(この「13」という値は、右の表の生徒が左の表にも存在している場合の右の表にある行位置)
このように1つだけ値を返してくれれば値の特定ができて楽なのですが、値「3」を検索した場合はそうもいかず「3」と「13」の2つの値が返ってきてしまいます。
つまり3行目なのか13行目なのかが特定できないというわけです。
そこで、あくまで値「3」を検索したいのであって「13」はいらないので、フィルター関数で取得した配列fValの値の数分(「3」と「13」)をループさせて値「3」なのか値「13」なのかを特定させます。
そのループが121行目のfor文になります。
123行目でフィルター関数で取得した配列fValに対して一つ一つ値を確認し、「3」なのか「13」なのかを判定します。
判定した結果合致した場合は、右の表の生徒が左の表にも存在するということなので、フラグnfDataFlgにFalseを設定します。
合致しない場合は、右の表の生徒が左の表にも存在しないということなので、フラグnfDataFlgにTrueを設定します。
Falseは右側の表の生徒が左側の表に存在しないことを、Trueは右側の表の生徒が左側の表に存在することを表します。
存在しない場合は、各生徒の名前と点数を格納する配列getValにその生徒データを格納します。(詳しくは後述する「注目すべきコード⑧」でご説明します)
注目すべきコード⑧
次に見て頂きたいのは153行目から167行目です。
If nfDataFlg Then
'右の表の生徒が左の表にも存在しない場合
'表(7月～12月)の列数分ループを行う
For lCnt = 0 To rEndClmPos - rBgnClmPos
'表(7月～12月)の生徒の各データ(生徒名と点数)を配列に格納する
getVal(getValRCnt, lCnt) = Cells(fCnt + lBgnRowPos, rBgnClmPos + lCnt)
Next lCnt
getValRCnt = getValRCnt + 1
End If
以上のコードは、nfDataFlgの値を判定し、Trueの場合は右側の生徒データを配列getValに格納する処理です。
nfDataFlgは、右側の表の生徒が左側の表に存在しないかを判定するフラグで、Trueの場合は右の表の生徒が左の表にも存在しない場合に該当するので、161行目で右側の生徒データを配列getValに格納しています。
Falseの場合は右側の表の生徒が左側の表に存在しているので、すでにその生徒データは配列getValに格納されているので何も処理は行いません。
注目すべきコード⑨
次に見て頂きたいのは174行目です。
'配列のデータを表(7月～12月)に出力する
Range(Cells(rBgnRowPos, rBgnClmPos), Cells(rEndRowPos, rEndClmPos)).Value = getVal
以上のコードは、配列getValの値を指定した範囲のセルに貼り付ける処理です。
貼り付け先のセルの範囲と配列のサイズは合わせておかないと配列の値全てがセルに貼り付かないので、漏れがないよう正しくコードを記述しましょう。
動作確認
【パターン①】左側の表の生徒全てが右側の表に存在する場合で、かつ並びが異なる場合
マクロ実行前
以下の表は、左側の表の生徒全てが右側の表に存在する場合で、かつ並びが異なっています。
マクロ実行後
マクロを実行した後は、右側の表の並びが左側の表の並びと同じになりました。
【パターン②】右側の表の生徒の何人かが左側の表に存在しない場合(または、左側の表の生徒の何人かが右側の表に存在しない場合)
マクロ実行前
以下の表は、右側の表の生徒の何人かが左側の表に存在していません。(または、左側の表の生徒の何人かが右側の表に存在していません)
マクロ実行後
マクロを実行した後は、右側の表の生徒が左側の表に存在する生徒同士の並びを同じにして、右側の表の生徒が左側の表に存在しない場合は右側の表の末に並べて表示しています。
なお、表の列の値をキーに別の表データを同じに並び替えることを前提としているので、左側の生徒の並びはそのままです。
並び替えるのはあくまで右側の表だけです。
【パターン③】右側の表の生徒全てが左側の表に存在する場合で、かつ並びが同じ場合
マクロ実行前
以下の表は、右側の表の生徒全てが左側の表に存在する場合で、かつ並びが同じです。
マクロ実行後
マクロを実行した後は、マクロを実行する前と同じ状態のままです。
最後に
本記事では、表の列の値をキーに別の表データを同じに並び替える方法についてご説明しました。
もしある表の列の通りに、別の表の並び替えをしたい時にはExcelの並び替え機能では実現できません。
なので、表の列の値をキーに別の表データを並び替えたい時は本記事を参考にしてみてくださいね。
プログラミングのスキルを習得するなら
プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。
プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。
