256行を超える範囲の、縦に結合されたセルを「並べ替え」した場合、並べ替えの結果が正しく無く、また、データの一部が消える

Question

下図のように縦に結合したセルを並べ替えした場合、範囲が256行を超えていると並べ替え結果が正しくなりません。また、データの一部が消えます。

下図の例では、範囲は258行です。

これを昇順に並べ替えると、次のような結果になります。

この状態で「元に戻す」をしても、元に戻らず次の状態になります。

Excel 2003では同じ問題は発生しませんでした。

何かオプションの変更で回避できる問題なのでしょうか？

Answer 1

>結合の解除をしようとしているはずなのに、結合を行う場合のエラーメッセージが出ることがある、との指摘です。

結合セルに複数のデータが入っている状態は、通常の結合セルではないため通常の単独セルとエクセルが判断して、リボンの「セルを結合して中央揃え」アイコンをクリックすると結合する操作をしようとするからです。

このリボン操作で結合を解除したい場合は、アイコンの右の▼をクリックして「セル結合の解除」を選択してください。

>マックちゃん様はどのようにしてこの操作が仕様外であるとお知りになったのですか？

MSのKBにも結合したセルについてのトピックは存在するので、てっきり仕様内の操作かと思っておりました。

結合したセルが問題なく並べ替えられるというトピックはないと思うのですが・・・・

逆に横方向に結合したセルでも、セルが結合しているだけで並べ替えが実行できないという情報があります。

Excel で結合セルを含む範囲を並べ替えるとエラー メッセージが表示されることがある

http://support.microsoft.com/kb/813974/JA

上記のKB情報は、横方向の結合セルの並べ替えでもうまくいかない（仕様外である）ことを示していますが、これが縦方向の結合となると、フィルタでもわかるように、結合セル内の非表示の空白セルも並べ替え対象になる可能性があるので、今回のご質問のような状況が発生したものと考えられます。

ちなみに、縦方向にセルを結合した場合は、ご紹介した結合セルに同じ値を挿入する方法で、おそらく並べ替えもフィルタも全く問題なく対応できると思います。

Answer 2

｢同じ操作」とはもう一度セルを結合させるということでしょうか？

この場合は、単純に結合されているセルの結合解除の操作をしただけと思うのですが・・・

同じ操作とは『リボンの「ホーム」タブの「セルを結合して中央揃え」をクリック』です。

結合されたセルを選択している状態でこの操作を行い、セルの結合を解除することを意図しております。

分かりにくい表現で申し訳ありません。結合の解除をしようとしているはずなのに、

結合を行う場合のエラーメッセージが出ることがある、との指摘です。

ところで、

この現象は、今回の質問と同じく、そもそも結合したセルを並べ替える、あるいはフィルタすることは仕様外の操作のため

仕様外であるならばまさに「仕様外です」がこの質問の回答であるので回答としてマークさせて

いただきたいのですが、マックちゃん様はどのようにしてこの操作が仕様外であるとお知りになったのですか？

MSのKBにも結合したセルについてのトピックは存在するので、てっきり仕様内の操作かと思っておりました。

Answer 3

>ただ、心配な点もあります。「左上端以外に値が入った結合されたセル」というのはExcelで存在することが想定されているのでしょうか？

基本的にセルを結合した場合は、左上端のみのデータが保存され、それ以外のデータは削除されるので、結合という操作だけを考えた場合は、左上端以外のセルのデータが入っていることは想定していないと思います。

>左上端以外に値が入っていない（つまり"普通"の）結合されたセルを選択して同じ操作を行うと、

特にダイアログ無しに結合が解除されます。

｢同じ操作」とはもう一度セルを結合させるということでしょうか？

この場合は、単純に結合されているセルの結合解除の操作をしただけと思うのですが・・・

>・左上端以外に値が入った結合されたセルを含む範囲をソートすると、結果がおかしくなる。

この現象は、今回の質問と同じく、そもそも結合したセルを並べ替える、あるいはフィルタすることは仕様外の操作のためで（結合セル単位の操作にならない）、例えば通常の結合されたセルをオートフィルタで空白を選択すると結合セルが表示されていても実際は空白行（結合セルの下のセルの行）が選択されることと同じような現象だと考えられます。

これらのExcelの仕様外の使い方に対応できるようにする裏ワザとして、数式貼り付けで結合セルのすべてのセルに同じ値を挿入する方法を紹介したわけですので、結合セルに異なる値が入っている場合は、最初の質問と同じように（空白セルと値が入っているパターン）、おかしな並べ替えになるものと考えられます。

Answer 4

例示のように縦に2つのセルが結合されているなら、たとえばＨ１セルが空白セルで、Ｈ2セルに「=H1」という1つ上のセルを参照する数式を入力し、Ｈ１：Ｈ２セルを選択して、右クリック「コピー」、結合セル範囲を選択して、右クリック「形式を選択して貼り付け」で「数式」と「空白セルを無視する」にチェックを入れて「ＯＫ」してください。

 

このようにしておけば、結合セルに表示されている値と同し値が結合対象のセルに入力されていますので、問題なく並べ替えることができます。

なるほど、確かにこの方法ならば回避できますね。マックちゃん様、お見事です。

ただ、心配な点もあります。「左上端以外に値が入った結合されたセル」というのはExcelで存在することが想定されているのでしょうか？と申しますのは、次のような症状が発生してしまうからです。

・そのセルを選択してリボンの「セルを結合して中央揃え」をクリックすると、次のダイアログが表示される。

左上端以外に値が入っていない（つまり"普通"の）結合されたセルを選択して同じ操作を行うと、

特にダイアログ無しに結合が解除されます。

・左上端以外に値が入った結合されたセルを含む範囲をソートすると、結果がおかしくなる。

次の図では、左の状態で「降順に並べ替え」ると右の状態になります。

（B列は左隣のセルの内容を参照しています。）

結合された内容が分解されてしまっています。

・発生条件がはっきりしないのですが、

「結合されている状態と結合されていない状態の中間」とでも呼ぶしかない

変な状態のセルが発生することがある。

A7:A9は次のような状態です。

・A7, A8, A9それぞれのデータが見える。

・A7:A9を選択すると、A7にカーソルが居るように見える。

・A7, A8, A9のどこをクリックしてもA7:A9が選択された状態になる。

・A7:A9を選択した状態でEnterを押せばA10:A12が、Tabを押せばB7が選択される。

・A7の内容は太字ではなく、セルの内容が上下に1ピクセルずれた文字が二重に重なっている。

・A7:A9をどこかにコピーすると、セルが結合されていない状態でコピーされる。

・この状態で保存して開きなおすと、セルは結合されている状態になる。

以上のように、どうにも「形式を選択して貼り付け」で「数式」をチェックすれば結合されている領域で左上端以外のセルにも値を入れることができてしまうこと自体が設計者の想定外で、それをやってしまうと以降そのブックは正常動作が保証できないものになってしまうのではないか、と懸念いたします。

（マイクロソフトの方から「左上端以外に値が入った結合されたセルが存在しても大丈夫です！」と見解を頂ければ一番有難いのですが…）

Answer 5

こちらでも検証してみたところ、Excel2003,2007,2013では問題なく並べ替えられますが、Excel2010だけがご質問のような現象が発生しますね。

数字が無くなって空白になっているセルは、実際は結合セルの下のセルに数字が入っているパターンになっています（結合セルを参照する「=A1」のような数式を書いて下方向にオートフィルすると結合セル内の数字がわかります）。

ご質問にあるように、ある行数を超えると並べ替えが乱れる原因は不明ですが、この現象を避けたいなら以下のような手順で結合セルの中の空白セルにも同じ値を数式を使って挿入する方法で対応することができます。

例示のように縦に2つのセルが結合されているなら、たとえばＨ１セルが空白セルで、Ｈ2セルに「=H1」という1つ上のセルを参照する数式を入力し、Ｈ１：Ｈ２セルを選択して、右クリック「コピー」、結合セル範囲を選択して、右クリック「形式を選択して貼り付け」で「数式」と「空白セルを無視する」にチェックを入れて「ＯＫ」してください。

このようにしておけば、結合セルに表示されている値と同し値が結合対象のセルに入力されていますので、問題なく並べ替えることができます。