【エクセル】並べ替えがうまく出来ない！？エラーや数式ズレを防ぐ６つの対策！

Excel(エクセル)で表を『並べ替え』機能を使って並べ替えすると、分析や集計を簡単に行うことが出来ます。

しかし、『並べ替え』を使うとエラーが表示されたり、数式が崩れたり、表の並びがぐちゃぐちゃになったりと、使用には気を付けなければいけないことが多くあります。

今回の記事では、『並べ替え』機能に関する6つの困りごとについての対策を各見出しで解説します。

『並べ替え』機能の使い方をおさらいしたい方は、下のリンク記事を参考にしてください。

６つの困りごと(問題と対策 )

『並べ替え』機能で生じる困りごとは主に以下の６つがあります。

NO.１の「並べ替え機能が使えない」はそもそも利用が出来ないという問題ですが、残りのNO.２～６は、並べ替え後に生じる問題です。

これらの問題を解決するための以下の対策を以降の見出しで一つずつ解説します。上の６つの問題の番号と、下の対策の番号は一致していますので、どの見出しを読むかの参考にしてください。

【対策１】セルの結合は禁止

並べ替え機能が使えない場合は、表内に「セルの結合」が含まれている可能性が高いです。

「セルの結合」が含まれた表を『並べ替え』しようとすると、どうなるか確認してみましょう。まず、表内の任意のセルを選択してから、「データ」タブの「並べ替え」をクリックします。

セルが結合されてない「商品名」列を選択して並べ替え方法を選択します。そうすると・・・

下のように「この操作を行うには、すべての結合セルを同じサイズにする必要があります」という警告・注意マークが表示されます。

このように、表内のセルの結合がある場合は、エラーが表示されて並べ替えが出来ません。

そのため、表は以下のように、セルの結合を解除し、解除後に発生した空白セルもデータ入力すれば、このような問題は発生しません。

この「セルの結合」は非表示で隠れているセルが結合されていてもエラーとなります。

下の図のように、画面上に見えていないセルの結合があるので、エラーが表示されます。

【対策２】連番を振る

表を並べ替えた後に、表の並びを元に戻せなくなることがあります。

その原因は、複数の列に対して並べ替えのルールを設定したりすることで、元の表の元の並び順が分からなくなるためです。

並び順を元に戻すことが出来るようにするために、表には「連番」を付けるようにしましょう。

そうすることで、並べ替えた後も「連番」列を昇順で並べ替えすることで元に戻すことが可能です。

【対策３】関数の引数の設定

並べ替え後の数式崩れを防ぐ対策を紹介します。

並べ替えすると、関数の参照先が変わった影響で値が崩れる場合と、崩れない場合があります。

先に結論を言ってしまうと、数式が上のセルを参照していないか、絶対参照が使われていないかなどを考慮してから並べ替えが必要ということです。

どのような場合に値が崩れるか、３つのパターンを紹介します。

数式や値が崩れることを確実に防ぎたい場合は、表を値に貼り付け直してから並べ替えしてください。

絶対参照と相対参照という言葉が分からない方は以下の記事を参考にしてください。

パターン①　上のセルを足し算(相対参照)

下の表は、F9セルに「＝F8＋F5」の足し算が挿入されて、ブドウの販売高の合計が計算されています。

この表を『販売高』列で降順で並べ替えすると、関数が「=F2+#REF!」に変わり、エラーが発生してしまいました。数式が相対参照のため、並べ替えた後も同じ位置のセルを参照したことが原因で数式が崩れました。

パターン➁　上のセルを足し算(絶対参照)

パターン①では数式が崩れてしまいましたが、足し算を絶対参照(＄で参照先を固定)にしたらどうなるでしょうか。

この表を『販売高』列で降順で並べ替えしても、関数は「＝$F$8＋$F$5」のまま固定されているため、リンゴの販売高のセル(F8)を参照してしまい、値が「287,350円」から「199,200円」に変わってしまいました。

パターン③　同じ行のセル同士を掛け算

下の表では、同じ行の２つのセルを掛け算していますが、絶対参照が含まれます。

この表を販売単価の小さい順に並べ替えしてみます。すると・・・

G7のセルの数式を確認すると、「＝E3*$F$7」となっていて、数式の参照先が崩れたことが分かります。

【対策４】空白列に注意

表の中に空白列が存在している場合は、表の並びが崩れる可能性があります。

下の図のように、表の中のD列に表を左右に分断する空白列があります。黄色の行は表の崩れを説明するために色塗りしています。

この状態で、表の左側のセルを選択してから「並べ替え」を選択します。

そうすると、下の図のように、空白列のせいで、表の左側だけが「並べ替えの対象セル」で選択されてしまっていることが分かります。この状態で、商品名を昇順で並べ替えしてみると・・・

表の左側だけが並べ替えされてしまいました。表の右側は並べ替え前と同じ並びです。

このように空白列が非表示の状態でもエクセルが自動で表を左と右に分断してしまいます。

この表の分断を防ぐためには空白列を挿入しないか、もしくは下の図のように、空白列も含んだ状態で表全体を選択してから「並べ替え」を使用すれば、この表の崩れは発生しません。

【対策５】先頭行を見出しに設定

下の図のように、見出し行も並べ替えされてしまうことがあります。この原因と対策について紹介します。

「並べ替え」を使うと、「並べ替え」画面が表示されます。この右上に「先頭行をデータの見出しとして使用する」というチェックボックスがあります。基本的にデフォルトで✓が入っていますが、稀に✓が外れている場合があります。その際は、✓を入れてから並べ替えしましょう。

【対策６】最下行のSUM関数に注意

表の最下行にSUM関数が挿入されている表が多く存在します。

下の図のように、最下行にSUM関数が挿入された表を並べ替えすると、SUM関数のセルも並べ替えされてしまいます。

この原因は、最下行も表の一部と見なされてしまうためです。このような並べ替えが発生しないよう以下のどれか１つの対策を実行してください。

SUM関数は表に隣接しないセルに作成

SUM関数を表に隣接していないセルに挿入すれば、並べ替えを使用しても表の一部の範囲と判断されません。

表の範囲を選択してから『並べ替え』

手動でSUM関数を除いたセル範囲を選択してから『並べ替え』を選択するとSUM関数は表の一部の範囲と判断されません。