スピった結果の検索に注意
Excelに"スピル"という仕組みが実装され、スピルありきの画期的ワークシート関数が続々と追加されています。めっちゃ便利なスピルなのですが、スピった結果を検索するときには注意が必要です。
ここでは、分かりやすいように、2枚のシート上でやってみます。もちろん、同一シート内でも同じことになります。まず、Sheet1に上図のようなリストを作りました。テーブルにしています。テーブルの名前は「Data」です。さて、Sheet2で、次のようにFILTER関数を使ってみました。
セルA1を変更すると、該当する名前のデータだけが表示されます。では、このSheet2で"神奈川県"を検索してみます。見てすぐに分かりますが、セルC4にあります。
[次へ]ボタンをクリックすると、次の結果になります。
まぁ、ほとんどの方が「はぁ？なんで？」ってなりますよね。ちょっとExcelを知っている方なら[セル内容が完全に同一であるものを検索する]が怪しいのでは？って思うかも。"神奈川県"の後ろにスペースでも混在していて"神奈川県 "みたくなっているのではって。
残念ですが、犯人はそいつじゃありません。冤罪です。結論から申し上げましょう。見つからなかった原因は、[検索]ダイアログボックス左下にある[検索対象]です。
[検索対象]には、上図のような選択肢があります。古いExcelでは「メモ」が表示されないかもしれませんが、気にしないでください。下二つの「メモ」と「コメント」は、今回関係ないので触れません。問題は「数式」と「値」です。両者の違いを正確に理解して、検索時に使い分けている人は少ないのでは？詳しく解説しましょう。
まず、検索対象のセル内に「値(数字や文字や日付など)が直接入力されているとき」は、どちらでも同じ結果になります。
問題は、セル内に「数式が入力されているとき」です。今度は、セルに「=10+20」という数式を入力してみました。もちろん、計算結果の「30」がセルに表示されています。このとき、[検索対象]が「数式」だと「30」は見つかりません。
セルに数式が入力されているとき、[検索対象]の「数式」は、セルに入力されている数式の中を検索します。計算結果の「30」を検索するには、[検索対象]を「値」にしなければなりません。
[検索対象]の「数式」は、数式の中を検索しますので、たとえば次のように「SUM関数が使われているセル」を探すこともできます。
できますけど「数式の中を検索する」なんて、そんなことします？実務では一般的に、数式の計算結果を探すことの方が多いと思います。だったら、[検索対象]は「値」にしてください。ちなみに、この「値」では、もうひとつ便利な仕様があります。
下図のA列には日付を入力してあります。さらに、表示形式を「yyyy/m/d(aaa)」として、1文字の曜日が表示されるようにしました。この、表示形式によって表示されている曜日を検索してみましょう。これ[検索対象]が「数式」では見つかりません。
このときも、「値」だと検索できます。
VBAをやっている人でしたら、このへんでピンと来るかも知れませんね。「数式」はセルのFormulaプロパティで、「値」はValueプロパティが検索対象になっているんです。そう考えれば「値」という表記にも納得できます。
さて、冒頭にご紹介した、FILTER関数の結果から、なぜ"神奈川県"が見つからなかったのか、もうお分かりですね。
セルC4に表示されている"神奈川県"は、セルB2に入力した数式の「計算結果」です。したがって、[検索対象]を「値」にしなければ見つからないんです。スピルが実装されるまで、複数行×複数列を返すワークシート関数なんて、ほとんどありませんでしたし、その結果内を検索するようなこともレアケースでした。だから、それほど困らなかったんですが、これからは違います。スピルの結果にオートフィルターを設定したり、スピった結果内を検索するような使い方も増えてくるでしょう。そんなときは、ここで解説した[検索対象]に注意してください。
さらに注意すべきこと
検索の[検索対象]では、一般的に「値」を使うことが多いでしょう。しかし残念なことに、Excelを起動して最初に表示される[検索と置換]ダイアログボックスでは「数式」が選択されています。「数式」が初期値なんです。だからもし、本稿のように計算結果を検索するときは、毎回「値」に変更しなければなりません。とはいえ、心配することはありません。[検索と置換]ダイアログボックスには、今回のテーマである[検索対象]だけでなく、ほかにも多くの設定項目あります。これらの設定項目は「前回の状況が表示」されます。たとえば、[検索対象]を「数式」から「値」に変更すると、その後[次へ]ボタンをクリックしようが、[閉じる]ボタンをクリックしようとも、次に[検索と置換]ダイアログボックスを開いたときには「値」が選択された状態になっています。ちなみに、[検索する文字列]ボックス右にある▼をクリックすると、過去に入力した文字列がリストに表示されます。
このリストは、Excelを起動し直すまで、ずっと記憶されます。余談ですが、[検索と置換]ダイアログボックスは、ダイアログボックスを表示している状態でも、普通にExcelを操作できます。このようなダイアログボックスを「モードレスダイアログボックス」と呼びます。ちなみに、[セルの書式設定]ダイアログボックスみたく、ダイアログボックスを閉じないとExcelを操作できないタイプが「モーダルダイアログボックス」です。[検索と置換]ダイアログボックスは、Excelでも珍しい「モードレスダイアログボックス」であり、ものすごく特殊な印象を受けます。たぶんですけど、UserForm的に言うなら、[閉じる]ボタンなどでダイアログボックスを閉じても、UnLoadされるのではなく、Hideされているだけなのではないかと。各種設定項目などが"記憶されて次回に反映される"というよりも、常にメモリ上に存在しているのではないかなって。そう考えると、いろいろと辻褄が合ってきます。
さてさて、[検索と置換]ダイアログボックスでは、設定項目などが「前回の状態」で表示されます。これ、言い換えると「前回の状況を引き継いでいる」みたいなものです。ここで注意していただきたいのは、この「前回の状況」には、マクロで行った検索も含まれるということです。たとえば、もしあなたが計算結果を検索しようとして、手動操作で[検索対象]を「値」に変更したとします。すると、次回[検索と置換]ダイアログボックスを開いたときにも「値」になっています。しかし、何かのマクロを実行して、そのマクロの中で検索が行われていて、その処理では[検索対象]を「数式」で検索したとすると、次に手動操作で[検索と置換]ダイアログボックスを開いたとき、[検索対象]は「数式」に戻っている、ということです。ちなみに、マクロで[検索対象]を指定するには、LookInという引数を使います。
LookIn:=xlFormulas → 「数式」で検索 LookIn:=xlValues → 「値」で検索
以上、ややこしくなったので、箇条書きでまとめます。
- Excelを起動した直後の[検索対象]は「数式」が指定されている
- 手動操作で「値」に変更すれば、次回以降は「値」のままになる
- ほかの設定項目なども、前回の状態が再現される
- マクロで行う検索も、この"前回の状態"に含まれる
- 不意に設定項目が変化したら、マクロのコードもチェックしよう
みたいな感じです。十分ご注意くださいな。