Excelで行の高さを維持したまま並び替えできるようにする方法

今回はExcelで行の高さを維持したまま並び替えできるようにする方法を紹介します。

---

きっかけ

先日、職場にて「Excelでオートフィルタ等を使ってソート（並び替え）すると元の行の高さが維持されない」という相談を受けました。

「そうだっけ・・・？そうだったかも・・・？ちょっと調べてみるから時間ちょうだい。」といかにも頼りない回答をして、早速試してみたのですが、ちゃんと行の高さが維持されます。

おかしいな、と思いつつも現代っ子の私はすぐにググります。

そしたら出るわ出るわ、同じような状況の人たち。

教えて！goo　Excel2010の並べ替えで行の高さを維持したい

Yahoo知恵袋 エクセルの並び替えのときに、行の高さを保持

行の高さや列の幅は一度手動で変えてしまうと文字数などに合わせた自動調整ができなくなります。

一度、全セルを選択しどこかの行番号の境目をダブルクリックして高さを自動調整してください。

はい、そうでした、そうでした。解決策は私の中にありました。

ついこの前、関連記事を書いたばかりでした。

www.excel-chunchun.com

でも、このときは並び替えする時については触れてませんでした。

さて、誰もがご存知の通り、Excelでは 画面上では見えているのに印刷すると文末が途切れる という問題が起こります。

折り返して全体表示のときに行の高さの自動調整に頼ると、最後の行が印刷されない なんてこともよく見かけます。

だからこそ手動調整するわけですが、データ件数が多くなるとあまり現実的ではありません。

少なければ並び替えするたびに手動調整すれば良いかもしれませんが、私はやりたくないので先の記事の通り作業列を作って、行の高さの最低値を設定すると思います。

ここまでは前回の話。

今回、相談者から送られてきたデータはなんと 20万行！！！

万ですよ万

• 内部改行されてて
• 印刷が必要で
• 20万行もあって
• ソートもしたい

ちょっとイレギュラーかもしれませんね。

でも私の職場では珍しくない話でして、年に2回は見かけます。

読者の方々でも、100行程度のデータなら見かけることもあるのではないでしょうか。

無い！ って言われたらそれ迄ですが、少なくとも私はあるので世の中にはそんな人もあるでしょう。

実際に困っている人がこの記事にたどり着けるか、この内容が理解できるかは、知る由もありませんが・・・

話が逸れました。

20万行ものデータに、行の高さの最低値を設定するテクニック を割り当てるのは骨が折れると思います。

それに質問者は 印刷時に全部表示されることを確認済み と言っていますが、私は信用していません。

というか、「確認作業をする前に聞いてくれたら自動チェックで人件費が削減出来たのではないか」、「なんて無駄な時間を浪費したんだ」と考えてしまいますが。そんなこと言ってもメリットは無いので黙ってコードをカキカキしましょう。

必要なプログラム

今回の目的を達成するのに必要なプログラムは次の4種類です。

どんなイレギュラーが見つかるかわからないため、作業列をふんだんに使って進捗を可視化できる仕様にしました。

また、基本的には既存の行の高さを活用しますが、間違っている可能性も考えて簡易的なチェックも行います。

• ソート前のインデックスを書き出すコード
  • すでにキー等がある場合は必要ないかもしれませんが、今回のご依頼のデータには連番がありませんでした。
  • ソートして問題が見つかったら戻せるように、元のインデックスを控えておくと安全です。
• 現在の行の高さを書き出すコード
  • 文字が切れないように手動で調整済みとのことなので、行の高さを控えておく必要があります。
  • また異常値を発見するのにも一躍買います。（オートフィルタ・重複削除）
• 各行の行数を書き出すコード
  • ここで言う行数とは、LF（アスキーコード10）の事ではなく、見かけ上の改行数です。
  • 見かけ上の行数を求めるなんて不可能ですから、行の高さ(RowHeight）を元に行数を推測します。
  • ついでに、内部改行数を元に、自動調整用のダミー文字列を関数で生成します。
    • 関数は"=RC[-1]&REPT(CHAR(10)&""*"",RC[-1]-1)"
    • 「3(改行)＊(改行)＊(改行)」のようなデータを書き出します。
    • この文字を入れることで、行の高さの自動調整の動きをコントロールすることによって、文末の文字が途切れる現象を防ぎます。（詳しくは前記事へ）
• 膨大な行に「行の高さの自動調整」を適用するコード
  • 不用意に「行の高さの自動調整」を実行するとエクセルがクラッシュします。
  • かといって一行づつ処理すると時間がかかります。
  • 適度な行数に絞って、段階的に実行することで実現できます。

作業の流れ

1. 作業列1：インデックスを書き出し
2. 作業列2：現在の行の高さを書き出し
3. 行の高さの自動調整を実行
4. 作業列3：現在の行の高さを書き出し
5. 作業列4：作業列2と3の差を書き出し
   1. ここで差が大きすぎる物については手動設定漏れと断定できる
   2. 必要に応じて手動調整を行い、2からやり直す
6. 作業列5：作業列3を元に各行の内部行数を書き出し
7. 作業列6：作業列5を元に自動調整用のダミー文字列を書き出し
8. 実際にPDFやDocuWorksやXPSに印刷して確認
   1. 行の高さが大きいものは文末切れが起きやすいため、フィルタして優先的にチェックすると良い
   2. 必ず目視で一通りチェックすること

ソースコード

▶クリックで展開

Option Explicit

'前提：オートフィルタが設定されていること

Const COL1 = "連番"
Const COL2 = "手動行高"
Const COL31 = "自動行高"
Const COL32 = "行高差"
Const COL41 = "調整行数"
Const COL42 = "調整文字"

'オートフィルタの設定範囲から任意の項目名の列のデータ部のRangeを取得
'存在しない列の場合は右端の空の列に追加する。
Function AddColDataRange(WS As Worksheet, ColumnName As String) As Range
    Dim tbl As Range
    Dim rng As Range
    Dim Ret As Range
    
    'ColumnName列を検索し、データ部のみを返却対象とする
    On Error Resume Next
    Set tbl = WS.AutoFilter.Range
    On Error GoTo 0
    If tbl Is Nothing Then
        Err.Raise 9999, "AddColDataRange", "オートフィルタ未設定のシートでの実行は認めない"
    End If
    
    '列を検索して存在したらデータ部を返す
    For Each rng In tbl.Resize(1)
        If rng.Value = ColumnName Then
            Set Ret = rng.Offset(1).Resize(tbl.Rows.Count - 1)
            Exit For
        End If
    Next
    
    '存在しない時は列を追加してからデータ部を返す
    If Ret Is Nothing Then
        '列を追加するために空の列を検索
        '※空欄列が有り得る為、逆順探索する。
        Dim i As Long
        For i = tbl.Columns.Count To 1 Step -1
            If WorksheetFunction.CountA(tbl.Offset(, i).Resize(, 1)) = 0 Then Exit For
        Next
        '列名の記入／オートフィルタの更新／戻り値の設定
        tbl(1, i + 1).Value = ColumnName
        Set Ret = tbl.Offset(1).Offset(, i).Resize(tbl.Rows.Count - 1, 1)
        tbl.AutoFilter
        tbl.Resize(, tbl.Columns.Count + 1).AutoFilter
    End If
    
    Set AddColDataRange = Ret
End Function

Sub ①連番を出力()
    With AddColDataRange(ActiveWindow.ActiveSheet, COL1)
        .Item(1).Value = 1
        .Item(1).AutoFill Destination:=.Cells, Type:=xlLinearTrend
        '.Select
    End With
End Sub

Sub ②手動行高を出力()
    Call ②行の高さを出力(COL2)
End Sub

Sub ②自動行高を出力()
    Call ②行の高さを出力(COL31)
    Call ②行高差を出力
End Sub

Sub ②行の高さを出力(列名 As String)

    Dim rng As Range: Set rng = AddColDataRange(ActiveWindow.ActiveSheet, 列名)
    Dim data As Variant: data = rng.Value
    Dim i As Long
    
    For i = 1 To UBound(data, 1)
        data(i, 1) = rng(i).EntireRow.RowHeight
    Next
    
    rng.Value = data
    
End Sub

'手動と自動の差を算出する列を追加
Sub ②行高差を出力()

    Dim rng As Range: Set rng = AddColDataRange(ActiveWindow.ActiveSheet, COL32)
    
    rng.ClearContents
    rng.EntireColumn.NumberFormatLocal = "G/標準"
    rng.EntireColumn.WrapText = False
    rng.FormulaR1C1 = "=RC[-1]-RC[-2]"
    
End Sub

Sub ③行の高さの自動調整を安全に行う()
    Const GROUP_ROWS = 200
    Const SELECT_MODE = True
    
    Dim i As Long, j As Long, k As Long
    Dim WS As Worksheet: Set WS = ActiveWindow.ActiveSheet
    Dim rng As Range: Set rng = WS.AutoFilter.Range.Offset(1)
    
    For i = 0 To rng.Rows.Count / GROUP_ROWS
        With rng.Resize(GROUP_ROWS, 1).Offset(i * GROUP_ROWS).EntireRow
            'WS1.Rows ((3 + i * 100 + 1) & ":" & (3 + (i + 1) * 100))
            If SELECT_MODE Then .Select
            .AutoFit
        End With
        DoEvents
    Next
    
    If SELECT_MODE Then rng(2, 1).Select
    
End Sub

'自動設定された行の高さから、各行数における行高を理解し適切な文字数の数字を設定する
'自動調節でキリの良い行の高さに整えられていること。
'※全部のフォントが同一であることが前提
Sub ④行の高さに応じて行数を出力()
    
    Dim rng As Range: Set rng = AddColDataRange(ActiveWindow.ActiveSheet, COL41)
    Dim data As Variant: data = rng.Value
    Dim i As Long, j As Long
    
    '一旦値を消してから書式設定をしないと重い
    rng.ClearContents
    rng.EntireColumn.NumberFormatLocal = "@"
    
    '行の高さを取得　※100倍して整数で保持
    Dim rhArr() As Long
    ReDim rhArr(1 To UBound(data, 1))
    For i = 1 To UBound(data, 1)
        rhArr(i) = Int(rng(i, 1).RowHeight * 100)
    Next

    'データ部の先頭100行の行の高さ読み込んで最大公約数を求める
    '100行もあれば内部改行1の時の高さが特定出来るはず
    Dim dic As Dictionary: Set dic = New Dictionary
    For i = 1 To WorksheetFunction.Min(100, UBound(data, 1))
        If Not dic.Exists(rhArr(i)) Then
            dic.Add rhArr(i), 0
        End If
    Next
    Dim rh As Long
    rh = GCD(dic.Keys)
    
    '行数を出力
    For i = 1 To UBound(data, 1)
        data(i, 1) = rhArr(i) / rh
    Next
    rng.Value = data
    
    '調節文字
    Set rng = AddColDataRange(ActiveWindow.ActiveSheet, COL42)
    rng.ClearContents
    rng.EntireColumn.NumberFormatLocal = "G/標準"
    rng.EntireColumn.WrapText = True
    rng.FormulaR1C1 = "=RC[-1]&REPT(CHAR(10)&""*"",RC[-1]-1)"
    
End Sub

'最大公約数を算出（ユークリッドの互除法）
Function GCD_Single(x As Long, y As Long) As Long
    
    If x Mod y = 0 Then
        GCD_Single = y
        Exit Function
    End If
    GCD_Single = GCD_Single(y, x Mod y)

End Function

'最大公約数を算出（可変長パラメータ）
'WSFでもできる。
'?WorksheetFunction.Gcd(array(12,48),array(8,64))
'4
Function GCD(ParamArray Arr() As Variant) As Long
    '※ParamArrayは0~
    If UBound(Arr, 1) = -1 Then
        GCD = 0 'ERROR
        Exit Function
    End If
    
    Dim i As Long
    Dim Ret As Long
    
    If IsArray(Arr(0)) Then
        Ret = Arr(0)(0)
        For i = 1 To UBound(Arr(0), 1)
            Ret = GCD_Single(Ret, CLng(Arr(0)(i)))
        Next
    Else
        Ret = Arr(0)
    End If
    
    For i = 1 To UBound(Arr, 1)
        Ret = GCD_Single(Ret, CLng(Arr(i)))
    Next
    
    GCD = Ret
End Function

実行前テーブル

No	備考
1	あああ
2	いいいい いいい
3	ううう
4	ええええ ええええ ええええ ええ
5	おおおお

実行後テーブル

No	備考	連番	手動行高	自動行高	調整行数	調整文字
1	あああ	1	40.5	13.5	1	1
2	いいいい いいい	2	40.5	27	2	2 ＊
3	ううう	3	13.5	13.5	1	1
4	ええええ ええええ ええええ ええ	4	27	54	4	4 ＊ ＊ ＊
5	おおおお	5	54	13.5	1	1

解説

前述の通り、Excelでデータの状態を見ながら４種類のマクロを使い分けることで、行の高さを調整します。

【調整行数】の部分の数値を変えることで【調整文字】が変わります。

しかし数式で作った文字列には、行の高さの自動調整が働かないので、改めて自動調整を呼び出すことで適切な高さに変化します。

おまけ

実は↑のような複雑なコードを書かなくても、簡単なVBAでやる方法があります。

以下の関数を、シート上に=GetRowHeight(A1)という感じで記述して縦にフィルするだけで、各行の高さが計算して表示されます。

'指定Rngの行の高さを返す
Function GetRowHeight(rng As Range) As Double
    GetRowHeight = rng.RowHeight
End Function

そして、=B1/13.5という感じで行数を算出し（※13.5はフォントによって異なる）

"=RC[-1]&REPT(CHAR(10)&""*"",RC[-1]-1)"を使って行の高さ分の文字列を出力

データ全体を選択して、行の調整部分をダブルクリック

そのかわり、この方法は重いです。一度に実行するなら1万行程度が限界です。

Excelのソートの注意事項

その前にExcelのソートには次のような特徴（注意事項）がありますのでここに書き残しておきます。(深く考えずに箇条書きしたものなので雑です)

• 安定ソートである。
  • 安定ソートとは同一値が存在する場合に元の並び順が保証されているソートの事です。
  • もし不安定ソートだったらデータを元に戻せなくなったりして色々と不便だったことでしょう。
• 何も指定しない場合「ふりがな」を使ってソートする。
  • 同じ「単語」でもふりがな情報が付いた「単語(タンゴ)」と付いていない「単語」では別々のデータとして処理されます。
  • これを知らないと意図した通りに並ばなくて大変なことになります。
  • ユーザー設定のオプションで切り替える事ができます。
• 「ふりがな」が無い場合、漢字や記号はExcel独自の評価基準によりソートされる。
  • 日本人にとって連続的な情報でも対応してなかったりするので注意が必要です。
  • 有名なのは漢数字「一二三四五六七八九十」をソートすると「一九五三四七十二八六」になります。
  • 「ユーザー定義リスト」を使うことで一応は設定できますが、環境が変われば再登録が必要なのでそのたびにこのトラブルに見舞われます。
• 数字が文字列として記録されている場合、（多くの）ユーザーの希望した通りにはソートされない。
  • 例えば「file1.txt」と「file2.txt」と「file10.txt」をソートした場合、1、10、2の順になります。
  • 文字列の場合、先頭の文字から順番に1:1で照合しているから起こる現象です。
• 空の行があるとそこから下はソートされない。
  • 範囲を明示的に指定する必要があります。
  • 特にヘッダ行だけを選択してオートフィルタを設置したり、データの末尾に追記したことがわかるように一行空けて記入するような誤ったExcelの使い方をすると、後日ソートしたら下の方に未ソートのものが残っていたなんて事故が起こります。
• 範囲に結合されたセルがあるとソートできない。
  • いまさら言うまでもないですが、セルを結合してしまうとソートができません。
• データや着色はソート対象となるが、行の高さは維持されない。
  • ソート機能そのものには「行の高さ」を維持する機能は付いていません。
  • ただしセルが折り返して全体表示になっており、さらに移動先のセルの「行の高さが自動調整」になっていると、値に応じて自動調整されます。

別解

実は今回は検討しませんでしたが、今回とは全く逆の考え方で、全ての行を手動設定にする方法もあります。

その方法ではソート自体をVBAで行う必要があります。

ユーザーが無視してオートフィルタのソートを使った途端に破壊されます。

またVBAを使うと「元に戻す」が使えません。

さらに事前の手動調整にミスがあったとしても検知することができません。

（100%ではないもののバイト数から検証することはできそうな気もしますが・・・）

こちらの記事を書くかどうかは、今後の需要次第という感じですね。

おわりに（オチ？）

悲しきかな。この相談をしてきた方は本年度を持って退職されました。もう二度と会うことはないでしょう。次の職場で活かされることを祈ります。

以上

---

何か御座いましたらコメント欄、またはTwitterからどうぞ♪

それではまた来週♪　ちゅんちゅん(・8・)