Willi HeidelbachによるPixabayからの画像
「知っていると得するかもしれないExcelの癖」シリーズ第十八回、テーマは「MATCH関数」になります。
Excel 2016バージョン1903を使用して確認しています。
MATCH関数を使えば指定したセル範囲の中で目的とする値が入っているセルがどこにあるか?を知ることができます。
ただし教えてくれるのは、指定したセル範囲のなかでの位置になるので注意が必要です。
MATCH関数のセル範囲を決める時
列のどこかに入っている値を探す時、MATCH関数の設定で、「まずは列全体を対象にして調べる」というのは良くある話であると存じます。
その理由として「一番最後の行がどこになるか?」が決まっていない段階では、列の先頭を特定なセルに指定しまうと、それはすなわち列の最後を指定しなければならいことになり、となると「適当に最後の行を設定するぐらいなら列全体にしておいた方が良い」という考えが働くためです。
ただし、このように後からセル範囲を決める事にした場合に注意しなければならない事があります。
それは、MATCH関数はあくまでも、「セル範囲に指定された位置から数えた数値」を返しセル番地を返すのでは無い点です。
基本的なことなのですが、ついつい忘れてしまって「列全体ではなく、セル範囲にした方がパフォーマンスが良くなるのでは?」と思って設定を変えてしまうと、いままでの動きと異なり後悔することになります。
そもそもMATCH関数で「列全体の指定からセル範囲に変更してどれだけパフォーマンスに効果があるのか?」が疑問ではありますが、列(縦)方向の検索でいままでと同じ結果に補正するためには下記のように数式を変更すれば大丈夫です。
MATCH(検索文字列,先頭セル:末尾セル,照合の型)+ROW(先頭セル)-1
しかしながら、パフォーマンスを良くするために変更したのに、補正の計算が必要になるのであれば列全体に戻した方が良いかもしれません。
というか、パフォーマンスを気にするのであれば「最初からセル範囲で設計しておくべきであった」のかもしれません。
ただやり直すとなると手戻り感というか何かしらのダメージは受けることになります。
そうなりますと、MATCH関数については「ほぼほぼ書式の設計が固まってから仕様検討に入り、検索範囲に関しては最初から仮置きにしろ最終形で設計する」というのがベターになる認識です。
照合の型が「1」または「-1」で並べ替えしなかったらどうなるのか?
話題が変わるのですが、MATCH関数の照合の型が「0：検索値と等しい値を求める」場合は検索範囲は任意の並びで大丈夫なのですが、「1：検索値以下で最大の値を求める」や「-1：検索値以上での最小の値を求める」時は、「1：昇順」「-1：降順」で検索対象の範囲を並べて置く必要があります。
今回「並べ替えをしないとどのように駄目なのか?」を確認して見ましたのでご一読いただければ幸いです。
「-1：降順」ではないイレギュラーなケース
そもそも「駄目だ」と言われている事をしているので、良いも悪いもないのですが、「1：昇順」ではないイレギュラーなケースよりも結果は良好になります。
なお下記ExcelシートはMicrosoftサポート「OneDrive から Web ページやブログに Excel ブックを埋め込む」を元に設定をしています。
Excelブックを埋め込み方式で表示させるにあたり、下記の点にご注意ください。
- このシートはExcel OnlineでOneDrive「https://onedrive.live.com」にアクセスをして表示します。
- iframeを使用しています。
上記埋め込みExcelシートの検索文字列(赤太枠)はリストボックスになっていて検索文字を選択することができます。
MATCH関数で検索するのは、4文字から7文字と書かれた列(縦)に並んだ10個のセルで、それぞれ後半は空セルになっています。
実際にB列からE列の見出し列に設定されているMTACH関数の数式は下記になります。
=MATCH($F$2,INDIRECT(SUBSTITUTE($A2,"A","B")),-1)
上記数式で、$F$2は検索文字のリストボックスで、A列のセル範囲に入力されている文字列をSUBSTITUTE関数でB列からE列に置換してから、INDIRECT関数でセル範囲に変換しています。
検索文字が「@」の場合の結果が左図になります。
『検索値「@」以上での最小の値「1」「@」を求める』のですが、当然並べ替えられていないイレギュラーなケースですので、結果は正しくありません。
ただし、「A」「1」「-」では正しい値が返りますので、検索文字列を変更して確認してみてください。
「-1：降順」の正しい並びの場合
下記の埋め込みExcelシートのように、正しい値が返りますが、降順での並べ方に注意点があります。
今回のように、検索対象に数字、英字、特殊文字が含まれている場合は「数値とテキスト形式の数値を分けて並べ替えを行う」を指定する必要があります。
「数値に見えるものはすべて数値として並べ替えを行う」による降順の並び方とは異なりますので注意が必要です。
「1：昇順」ではないイレギュラーなケース
「1：昇順」の場合は、なぜなのか?よく分かりませんが、検索する範囲によって返ってくる値が変化をします。
しかもそれが下記埋め込みExcelシートで検束文字「-」の場合には規則性を持っているところが興味深いです。
「-」以外でも「@」や「1」でも規則性は見られますので、一度ご確認いただければ幸いです。
少し縦に長くなっていますがスクロールしていただくと71行まで表示します。
なお上記の結果は、正しい並び順でないため「#N/A」になるのがあるべき姿だと推察致しますが、なぜかセル範囲によって「ある意味正しい値」を返してくれるのが興味深い点です。
「1：昇順」の正しい並びの場合
埋め込みExcelシートは省略いたしますが、「数値とテキスト形式の数値を分けて並べ替えを行う」を指定して並び替えをすれば、セル範囲に関わらず正しい結果が返りますのでご安心ください。
まとめ
一部正しい値が返ってくるケースがあるので、おそらく将来的には照合の型が「1」または「-1」でも、並べ替えが必要なくなることが期待されます。
ただし、現状では照合の型が「1」または「-1」を使用される際は必ず検索対象を「数値とテキスト形式の数値を分けて並べ替えを行う」で並べ替えておかないと結果は保証されませんので注意が必要です。
以上、最後までご一読いただきありがとうございました。
