Office TANAKA - [オートフィルタのバグ]

「○○すると変な結果になります。これってExcelのバグですか？」という質問をよく受けます。シロウトが安易に"バグ"という言葉を使うと、上級者から嫌われますので注意してくださいね。いずれにしても「変な結果になります。これってExcelのバグですか？」的な質問の場合、過去に数え切れないほどの質問を受けてきた私の経験では、その90%以上が「(Excelなどの)仕様」または「勘違い」です。たとえば先日「Excel上に同じ名前のブックを複数開けないのはバグですか？」と質問されました。もうね、どこから説明すればいいのやら。それはExcelの仕様です。こうして安易に「バグですか？」と考える人は、決してExcelの上級者になれません。もうひとつの「勘違い」も多いです。いや、むしろこっちの方が多数派かと。ちなみに、この手の質問をされたときは「私が再現できるデータ形式と手順を示してください。話はそれからです。」と、いつもお答えしています。だって、私のところでは変な結果になっていないのですから、お答えのしようがありません。お医者さんに電話して「そういえば昔、なんかちょっと調子悪かったんですよ～原因は何ですか？」って聞いているようなものです。そうして、私の環境でも再現できるような手順を示してくれる方は、ほとんどいません。要するに自分が、どんなデータに対して、どんな操作をして、結果がどうなったのかを明確に覚えていないんですね。それではどんな名医だって、病名や対処法をお教えすることはできません。

そんな中、つい先日のschooで「オートフィルタで変な結果になるんです」という質問を受けました。いつもどおり「再現できる手順を教えてください」とお答えしたところ、その方は後日、私が再現できる手順を投稿してくださいました。やってみました。バグでした。これは明かなバグでした。確認したら、Excel 2003でも同じ現象が起きます。相当に古い時期からのバグです。なぜ今まで話題にならなかったのか不思議です。今回は、その現象と対応策を詳しく解説します。

前提1：フィルハンドルの操作

Excelでは、選択したセルまたはセル範囲の右下に、小さい■が表示されます。これを"フィルハンドル"と呼びます。

フィルハンドルには、さまざまな便利機能が搭載されているのですが、その中で最もよく使われるのは「セルのコピー」でしょう。フィルハンドルをドラッグすると、ドラッグした範囲にセルをコピーできます。

コピー元セルに入力されている値が、文字列や単純な数値だったらコピーされますし、日付や「A-101」のように連続データを作成できる形式の場合は、連続データの作成になります。

コピー元のセルに計算式が入力されていた場合、コピー先の計算式では参照しているセルのアドレスが自動的に調整されます。

さて、このフィルハンドルには、もうひとつ便利な仕組みが備わっています。それは、フィルハンドルのダブルクリックです。フィルハンドルのダブルクリックは、フィルハンドルをドラッグしたときと同様に、セルのコピーや連続データの作成が行われます。このとき、どこまでコピーするかはExcelが判断します。一般的には、コピー元セルに隣接している、左または右の列に、何らかのデータが入力されている範囲にコピーされます。

また、コピー先に何らかのデータが既に入力されている場合は、その直前までコピーされます。既存データを上書きするとこはありません。

フィルハンドルの"ドラッグ"と、フィルハンドルの"ダブルクリック"は、Excelの内部で同じ操作が行われています。これは、両者をマクロ記録すると分かります。

Sub Macro1()
'セルB2のフィルハンドルをセルB6までドラッグしたとき
    Selection.AutoFill Destination:=Range("B2:B6"), Type:=xlFillDefault
    Range("B2:B6").Select
End Sub

Sub Macro2()
'セルB2のフィルハンドをダブルクリックしたとき
    Selection.AutoFill Destination:=Range("B2:B6")
    Range("B2:B6").Select
End Sub

どちらも同じ AutoFillメソッドが行われています。ドラッグしたときに記録される「Type:=xlFillDefault」は、何のデータをコピー(または作成)するかを、Excelが自動判断したという意味です。フィルハンドルは、マウスの右ボタンでドラッグすると、いろいろな操作が選択できますから、ドラッグのときにはこの引数が記録されたのでしょう。今回の件では関係がないです。

このように、一般的なExcel入門書では「フィルハンドルのドラッグとダブルクリックは同じ操作ですよ」と解説されます。唯一の違いである「(ダブルクリックでは)コピー先をExcelが自動判定する」という点は、ちょっとしたプラスアルファや、Excelの小さい配慮程度にしか触れられません。こんなこともできるんですよ～知ってると便利ですよ～みたいな。

前提2：オートフィルタの挙動

この件を誤解している人がメチャクチャ多いです。おそらく、日本国民の90%以上の人が勘違いしているでしょう。それは、オートフィルタで絞り込んだセル範囲に対して何かの操作を行うと、表示されているセルだけが処理の対象になるということです。可視セル選択なんてしなくてもいいんです。証拠をお見せしましょう。

上図のリストで、A列を"田中"で絞り込みます。

表示されているセル範囲A2:B8を選択して、セルの背景色を黄色くします。なお、この選択しているセル範囲には、非表示の3行目・5行目・7行目も含まれています。

でも、オートフィルタの絞り込みを解除すると、次のような結果になります。

非表示のセルには背景色が設定されていません。つまりExcelが、見えているセル(表示されているセル)だけを処理の対象にしてくれたんです。もちろん、言うまでもありませんが、太字や罫線などの書式設定、セルの削除や行の削除なども同様に、表示されているセルだけが対象になります。では次です。

セルD1には数値の100が入力されています。A列を"田中"で絞り込み、セルD1をコピーして、セル範囲B2:B8に貼り付けます。

これ、非表示のセルにもコピーされちゃうって思ってた人、きっといますよね。あなたも、そう思ってませんでしたか？ちゃんとExcelがうまくやってくれるんですよ。最後に、もっと怖い操作をしてみましょう。なお、いまコピーしたB列の100はクリアしておきます。

絞り込んだ結果のセルB2に数値の100を入力しました。このセルB2のフィルハンドルをセルB8までドラッグしてみます。

さすがに、この操作では非表示のセルにもコピーされちゃうと思ってませんか？結果は次のとおりです。

もちろん計算式のコピーでも同様です。

B列には数値を入力しました。A列を"田中"で絞り込み、表示されているセルC2に「=B2*2」という計算式を入力します。このセルC2のフィルハンドルをセルC8までドラッグしてみましょう。

ね、うまくいくでしょ。まだ疑っている人は、上記のようにサンプルのデータを入力して、実際に試してみてください。必ず同じようになります。

このように、オートフィルタで絞り込んだ結果に対して何かの操作を行うと、Excelが表示されているセルだけを処理の対象としてくれます。これは、手動操作でもマクロでも同じです。ちなみに細かい話をすれば、これはExcel 2002からの仕様です。しかし、ほとんどの方が「なんか～ときどき～非表示のセルに影響が及ぶってゆーか～なんか変な結果になることが、あったような気がするみたいな～よく分かんないけど～」とモヤモヤしています。そう、モヤモヤしているだけなんです。実際にはよく分かってないんです。たぶん、あなたもそうなのでは？実は、オートフィルタで絞り込んだ結果に対して何かをしたとき、非表示のセルにも影響が及んでしまう操作が2つだけあります。モヤモヤしている皆さんは、その2つの操作と、それ以外の上手くいく操作の区別がつかないだけです。区別がつかない理由のひとつは、多くのExcelユーザーは「自分が何の操作をしたか正確に把握していない」からでしょう。Excelの正式な機能名称を意識せず「データを引っぱってくる」とか「データを持ってくる」みたいに考えているから、自分が何の操作をしたか覚えていないんです。それでは、Excelを効率よく使うことなど無理です。なお「非表示のセルにも影響が及んでしまう2つの操作」に関しては、説明が長くなるので割愛します。私のVBAスタンダードセミナーでは、毎回この件をじっくり解説していますので、興味のある方はご参加ください。

オートフィルタのバグ

お待たせしました。では、いよいよオートフィルタのバグをご紹介します。まずは、上記で解説した2つの前提

フィルハンドルのドラッグとダブルクリックは同じ動作である
オートフィルタで絞り込んだ結果に対しては表示されているセルだけが操作の対象になる

を、しっかり認識してください。では、いきます。まずは、正常な動作から。

上図のようなリストで、A列を"田中"で絞り込みます。

続いて、セルB2に数値の100を入力します。そして、セルB2のフィルハンドルをセルB8までドラッグします。

結果は次のとおりです。

これはいいですね。では次です。今度はA列を"鈴木"で絞り込みます。

表示されているセルB3に数値の200を入力し、セルB3のフィルハンドルをセルB9までドラッグします。

これは正常な動作です。次に変な結果になる操作をご紹介します。A列を"田中"で絞り込んで100を入力するところまでは一緒です。そして、同じようにA列を"鈴木"で絞り込みます。

セルB3に数値の200を入力し、今度はセルB3のフィルハンドルをダブルクリックします。

この時点でおかしいですよね。セルB3が下までコピーされるはずですけど、コピーされません。さらに、セルB3に入力した200が100に変わってしまいました。オートフィルタの絞り込みを解除すると、次のようになっています。

なんじゃこりゃ？ですよね。明らかにおかしい結果です。しかし、ただ「結果がおかしい」で終わらせてはいけません。こうした変な結果や、予期しない結果に遭遇したら、何はともあれ「発生要因」を明確にしなければなりません。

・フィルハンドルのダブルクリックが問題か？

上記の操作で異なったのは、"鈴木"で絞り込んだあとで入力した200のセルで、フィルハンドルをドラッグしたか、それともダブルクリックしたのかです。怪しいのは"ダブルクリック"なのですが、今回の操作では、セルのコピーを2回行っています。"田中"で絞り込んで100をコピーしたのと、"鈴木"で絞り込んで200をコピーしたので、それぞれ、ドラッグ操作とダブルクリック操作を変えて試してみました。結果は次のとおりです。

	1回目	2回目
ドラッグ→ドラッグ	○	○
ドラッグ→ダブルクリック	○	×
ダブルクリック→ドラッグ	○	○
ダブルクリック→ダブルクリック	○	×

単純に、オートフィルタで絞り込んだ結果に対して、フィルハンドルをダブルクリックしたことが問題であるなら、1回目の"田中"で絞り込んで100をコピーするときだって、変な結果になるはずです。1回目のコピーではダブルクリックで正常にコピーされるということは、ダブルクリック操作が怪しいとしても、ほかにも別の要因がありそうです。

・2回目とはどんな状態か？

この現象を投稿してくれた質問者さんは「1回目は上手くいくけど、2回目は変なことになる」みたいに言っていました。しかし、操作を行った回数によって誤動作するというのは、ないとは言いませんが、ちょっと考えにくいです。上手くいった1回目と、変な結果になった2回目では、根本的に何が異なっていたのでしょう。それは

1回目にコピーするときは、B列に何もデータが入力されていない
2回目にコピーするときは、B列に何らかのデータが入力されている

という違いです。これが要因かどうか、次のように確認してみました。

セルB8に、あらかじめ100を入力しておきます。この状態で、A列を"鈴木"で絞り込み、200をダブルクリックでコピーしてみます。

この時点で、ちょっと「むむ？」ですね。絞り込みを解除して、何が起きたのか確認してみます。

分かったことがひとつと、新たな疑問がひとつあります。まず分かったのは、フィルハンドルのダブルクリックによって、コピーは行われているということです。ただし、そのコピー先がセルB7で止まっています。A列を"鈴木"で絞り込んでいたのですから、セルB9までコピーされなければおかしいです。上記の「前提1」で書きましたが、フィルハンドルのドラッグとダブルクリックの違いは「コピー先をExcelが判定するかどうか」です。また「前提2」で確認したとおり、オートフィルタで絞り込んだ結果に対して何かの操作をすると、Excelは表示されているセルだけを処理の対象にしれくれます。であるなら、コピー先として、ExcelはセルB9までを自動判定しなければおかしいです。実際には、セルB7で止まっています。これも「前提1」に書きましたが、オートフィルタを抜きにして、普通のワークシート上でフィルハンドルをダブルクリックしたとき、コピー先に何らかの既存データが入力されていると、Excelはその直前までコピーを行います。今回の現象もそれに似ています。ダブルクリックでは、オートフィルタによって非表示になったセルも含めて、既存データが存在するかどうかをExcelが自動判定していると。そういう結論になります。まぁ、ここだけを考えると、それも仕様として"有り"かなって思いますけど、いや、ちょっと待ってください。問題は、今回の確認で新たに生じた疑問です。最初の実験では、セルに入力した200が勝手に100となってしまいました。今回は200のままです。これはいったい、何が起きているのでしょう。

・1つ上のセル？

変な動作の"法則性"を見つけるため、いろいろなパターンで何度も実験したところ、さらに不思議な現象に出会いました。下図の状態からスタートしてみます。

これは、1回目の「"田中"で絞り込み→100をコピー」した後の状態です。これまでやってきたのと同じように、A列を"鈴木"で絞り込んでから200を入力し、フィルハンドルをダブルクリックします。結果は下図のとおりです。

これは冒頭にお見せした動作です。不思議なのは「セルB3に入力した200が、フィルハンドルをダブルクリックすると100になってしまう」ことです。さて、問題は次です。今度は下図の状態から始めてみます。

セルB4を空欄にしてみました。この状態で、A列を"鈴木"で絞り込んで200を入力し、フィルハンドルをダブルクリックしました。結果は次のとおり。

今度は、200が100に変化することもなく、一応コピーはされています。コピーがセルB5で止まっているのは、その下のセルB6に既存データが入力されていたからでしょう。セルB3の値が「100に変化」するのと「200のまま」コピーされる違いは、どうやら次のように考えられそうです。

今回の件が「間違いなくバグ」だと確信したのは、ここです。フィルハンドルのダブルクリックで、コピーされる先の自動判定がおかしいってのは"有り"かもしれません。フィルハンドルのドラッグとダブルクリックは、本来同じ動作なんだけど、オートフィルタで絞り込まれた結果に関しては違うと。そのときは、非表示のセルも自動判定に含めるんですと。だから、それは仕様ですとMicrosoftが言い張るのでしたら、百歩譲ってのみましょう。しかし、状況によっては、1つ上のセルがコピーされちゃうってのはいただけません。これは弁明の余地がないです。1つ上のセルがコピーされるといえば Ctrl + D(フィル-下方向へコピー) と似ていますが、フィルハンドルのダブルクリックと Ctrl + D は根本的に違います。Ctrl + D では連続データを作成できません。まったく違う機能です。

回避策

バグは、発見するだけでは不十分です。どのような状況で発生するのかを見極めて、じゃぁどーするのかという回避策を検討しなければ意味がありません。今回の件は、どう回避したらいいのでしょう。まず確定していることは、

オートフィルタで絞り込んだ結果に対して、何かのセルをコピーするとき、コピー元セルのフィルハンドルをダブルクリックしてはいけない

ということです。フィルハンドルのドラッグなら問題ありません。なので「これからはドラッグしてください」というのが回避策のひとつです。

とはいえ、フィルハンドルをダブルクリックしてきた方には、それなりの理由があるはずです。たとえば、コピーする行数が多いケースです。数行から数十行程度でしたら、ドラッグしても手間ではありません。しかし、コピーする行数が数百行とか千行を超えるとか。そういうときもあるでしょう。一画面に収まらないような範囲をマウスでドラッグするのが苦手の人は多いです。つい、勢いが余って行きすぎた…なんて経験は私にもあります。あるいは、ノートパソコンのマウスパッドって、ドラッグ操作がやりにくいです。やりにくくないですか？やりにくいですよね。私だけかな？まぁ、いずれにしましても、ドラッグ以外の方法をご紹介します。

まずここで重要なことは、今回オートフィルタで絞り込んだ結果に対して行うことは「連続データの作成」ではなく「セルのコピー」だということです。フィルハンドルを操作するので、どうしても「連続データの作成」をイメージしがちですが、それは無理です。オートフィルタで絞り込んでいる状態では、絞り込んでいるリストの中や、そのリストとは関係ない別のセル範囲であっても、フィルハンドルの操作で「連続データの作成」は行えません。この仕様をご存じない方が多いので、ちょっとやってみます。

オートフィルタで絞り込んでいるリストとは別のセル範囲でも、連続データは作成できません。

ちなみに、テーブルの場合は話が別です。テーブルをオートフィルタで絞り込んでいる状態でも、テーブル以外のセル範囲では連続データが作成できます。

しかし、テーブルであっても、テーブル内では連続データの作成ができません。

今回やることは「セルのコピー」です。であるなら、コピー元のセルと、コピー先のセル範囲を、何とかして選択できればいいです。ここで使うのは、Ctrl + C と Ctrl + V ではありません。Ctrl + D です。

ということなので、コピー元セルを含むコピー先セル全体を選択する、マウスのドラッグではない方法をご紹介します。なお、実際には一画面に収まらないような範囲を対象にすることが普通ですが、ここでは解説の都合上、少ない行数で解説します。

方法1：Shiftキーの活用

まぁ、Excelの基本っちゃ基本なんですが。まず、コピー元のセルをひとつ選択します。そのまま、必要であれば画面をスクロールするなどして、選択したいセル範囲の一番下のセルを表示します。このとき、矢印キーを押すとアクティブセル(現在選択しているコピー元セル)が移動してしまうので、PageDownキーを押すか、スクロールバーをドラッグするなどしてください。

選択したいセル範囲の一番下セルを確認したら、そのセルをShiftキーを押しながらクリックします。Shift＋クリックというのは、現在のアクティブセルから、Shift＋クリックしたセルまでの範囲を選択する操作です。

Ctrl + Dを押せば完了です。

方法2：下から上へ選択する

私は、この方法をよく使います。てゆーか、こういうことをしたいと考えたとき、無意識に指が動きます。今回のようなケースで、コピー先の選択は何が難しいのかというと「コピー元セルから下方向に向かってEndモードを使った選択(Ctrl + Shift + ↓)ができない」からです。コピー元セルの下にはデータが入力されていないのですから、Ctrl + ↓ではワークシートの一番下まで移動してしまいます。なので、発想を変えます。もし、アクティブセルが、選択したいセル範囲の一番下のセルにあったら、そこからCtrl + Shift + ↑で一気に選択可能です。だから、まずは一番下のセルを選択します。今回のケースは「A列を"鈴木"で絞り込んだ状態」です。であるなら、A列には空欄セルが存在しないはずです。ということは、A列のセルからCtrl + ↓で一気に目指す最下行までジャンプできます。

Ctrl + Dを使って、一番上のセルをコピーするとき、一般的には一番上のコピー元セルにアクティブセルがありますけど、別にアクティブセルの位置はどこでもかまいません。上図のように、アクティブセルが一番上のセルになくても、選択範囲内の一番上セルをコピー元として、選択範囲すべてにコピーされます。

余談ですが、こうしてコピーしたあとで、コピー元セル(ここではセルB3)に対して何かしたいことがあります。上図の操作でコピーして、その後セルB3にアクティブセルを移動するには、Tabキーを押してからのShift + BackSpaceです。これも指が覚えています。ぜひ、お試しください。

方法3：マニアックなキー操作

今回のように、空欄セルがあってEndモードを使えないようなとき、それでも何とか対象のセル範囲を選択するキー操作があります。ちょっとマニアックな方法です。私はあまり使いませんが、知っていると何かのときに役立つかも知れませんのでご紹介しますね。

まず、データがすべて入力されているセル範囲を選択状態にしたいので、アクティブセルをセルA3に移動します。そこから、Ctrl + Shift + ↓を押して、データの最下行までを選択します。「この選択範囲を1列右に移動させる」というイメージです。

選択範囲を右方向へ広げるために、Shiftキーを押しながら右矢印キー(→)を押します。今回の解説では「すべてデータが入力されているA列」と「実際に選択したいB列」が隣なので、右矢印キーを1回だけ押しましたが、もし両者が離れていたら、そのぶんだけ右矢印キーを押してください。要するに、選択されているセル範囲で「すべてデータが入力されている列」が左端列、「実際に選択したい列」が右端列となるようにします。

ポイントはここです。あまり知られていませんが、セル範囲を選択した状態でTabキーを押すと、選択状態をキープしたままで、アクティブセルだけを移動できます。このキー操作は、意外と良く使います。選択した範囲だけを並べ替えたいときなどに重宝しますので、ぜひ覚えておきましょう。ちなみに「すべてデータが入力されている列」と「実際に選択したい列」が離れているときは、そのぶんだけTabキーを押せばいいのですが、もし1秒でも急いでいるときは Ctrl + .(ピリオド)というワザも使えます。Ctrl + .(ピリオド)は、選択しているセル範囲の四隅(左上→右上→右下→左下)にアクティブセルを移動するキー操作です。こんなときくらいしか使い道はありません。

最後に、Shiftキーを押しながら右矢印キー(→)を押して、選択範囲を右方向へ狭めます。

セル範囲を選択している状態で、Shiftキーを押しながら矢印キーを押すと、その選択範囲がどのような状況になるかは、理屈を理解するよりも身体で覚えた方がいいでしょうね。

まとめ

私は、フィルハンドルのダブルクリック操作を、ほとんどやらないので、このバグを知りませんでした。なぜダブルクリックしないのかといえば、コピー先のセル範囲を簡単に選択状態にするキー操作を指が覚えているからです。本当を言えば、そうしたときに便利な仕組みを自作アドインで組み込んでいたりもします。とはいえ、確認しただけでも、Excel 2003時代から続くバグです。今までに数万人のExcelユーザーと直接会ってきましたので、耳に入ってもよさそうなものです。そうした質問がなかった理由のひとつは、そもそもほとんどのExcelユーザーが「オートフィルタの仕様」を正確に理解していないからではないかと。何かと「データを引っぱってくる」「セルを持ってくる」みたく、自分が何の操作をしたかを理解していないので「なんか～ときどき～変なことになるんすよ～」って感じてる人が多いです。だから、今回の件に遭遇したとしても「ああ、また変な結果になった」程度の記憶しか残らなかったのではないかな～って気がしています。ちなみに、上記の解決策には書きませんでしたが、表をテーブルに変換しておけば簡単です。いつも、テーブルを"使わない理由がない"と言っていますが、その"使わない理由がない"という"理由"を、強く感じた件でもありました。