えくせるちゅんちゅん

ことりがエクセルをちゅんちゅんするブログ

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

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

f:id:Kotori-ChunChun:20190415230529p:plain


きっかけ

先日、職場にて「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やDocuWorksXPSに印刷して確認
    1. 行の高さが大きいものは文末切れが起きやすいため、フィルタして優先的にチェックすると良い
    2. 必ず目視で一通りチェックすること

ソースコード

実行前テーブル

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でデータの状態を見ながら4種類のマクロを使い分けることで、行の高さを調整します。

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

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


おまけ

実は↑のような複雑なコードを書かなくても、簡単な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からどうぞ♪

週1回の更新を目指して、頑張ってますので応援よろしくおねがいします!

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