Excelで並び替え(昇順・降順)をした時に順位がおかしくなる・関数ずれるなどの現象の対処方法

未分類

今回はExcelで並び替えをしようとしたときにおかしくなる・関数ずれるなどの現象の対処方法です。

例を挙げて説明しますと、ランク関数で順位付けをし、それに対し昇順で並び替えを行ったときに起きた現象です。

現象画像

図のように元の値が変わってしまっており、今回はこれを直す方法になります。

正常な画像

発生原因

今回の現象ですが、関数を選択する際のセル選択にて失敗していると思われます。
恐らく、シートを複数使用しかつ参照している場合に起こると考えています。

並べ替えがうまくいかなかったランク関数の式がこちらです。

発生原因画像①

一見、間違えていないように見えるのですが、参照元は今いるシートなので「Sheet1!」という文言が不要になります。

このほかにも設置件数でも同様にSheet1!があります。F列・D列も同じく入っており、昇順/降順した時に数式がずれてしまっていました。

発生画像②

ランク関数で値を算出し、フィルターを使って並べ替えを行った後の式です。
セルの本来参照しなければいけないところがずれてしまっています。
これが、ランキングの昇順降順がうまくいかない原因です。また関数がずれてしまうのも同時に起こります。

そのため、対処方法は一度数式を見直し、自分のシートを見ているところを調整する必要があります。

そもそも、なぜ自分のシート名が入ってしまうのかというと、同式内で別のシートを参照し、自シートも参照するときに、自シートをクリックする際にシート名が入ります。

対処方法

ポイントは下2つです。

  1. 1行目の式を全てチェック
  2. 式内に自シート内のセルなのに、自シート名が入っている箇所修正

ではここから例を挙げて、詳しく説明していきます。

1行目の式を全てチェック

今回テーブルを使っているのと、他の式はオートフィルを使って修正すればよいので、1行目のデータを確認し修正します。

まずはD列(設置件数)を確認するとCountifでセルを参照する式のところがSheet1!C2となっています。このように「Sheet!1」となっている箇所を全てチェックします。

発生画像②

今回のケースでは、北海道のD~G列のセル計4つを確認します。

式内に自シート内のセルなのに、自シート名が入っている箇所修正

全てチェックし終わったら、Sheet1!を削除します。
チェックし、該当箇所全て行ってください。

ポイントは

セル内の式に自分のシート名称が入っていないことです。

ポイントはシート名称の画像

修正した後に、ランクキングを昇順でソートしてみると、うまくいくはずです。

実際の式もこのように、正しいセルを参照するようになりました。

修正前

修正後

まとめ

いかがでしたでしょうか。
複数のシートを参照する際、自分のシートに戻ってくると自動的に自分のセルが入ってしまいます。
もし、値がおかしいなど思いましたらこの記事を思い出していただけたら幸いです。


コメント