Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(指定なし : 指定なし)
この関数で何故やりたいことが実現できるのか教えてください
投稿日時: 22/05/03 07:03:51
投稿者: のらねこ

いつもお世話になっております。
業務で次の関数を使用することが多くありますが、この関数で何故やりたいことが実現できるのかが理解できていません。
この関数の動きというか、何故やりたいことが実現できるのかを教えていただけないでしょうか。
 
@255文字を超える検索
=index(検索範囲,match(true,検索範囲=検索値,0))
ctrl+shift+enter で決定する
https://kimshirodai.at.webry.info/201707/article_1.html
 
A検索値に特定の文字列を含む場合、返す文字列を含む範囲の同じ行の値を返す
=INDEX(返す文字列を含む範囲,MATCH(0,0/FIND(含まれる文字列を含む範囲,検索値),0))&""
https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q11259994876

回答
投稿日時: 22/05/04 08:56:41
投稿者: WinArrow
投稿者のウェブサイトに移動

>=index(検索範囲,match(true,検索範囲=検索値,0))
について
数式を分解すると
「INDEX関数」と「MATCH関数」になります。
  
この中で「MATCH関数」は、
「INDEX関数」の第1引数の「検索範囲」の相対位置を求めるものです。
  
「MATCH関数」は、一般的に
=MATCH(検索値,検索範囲,[照合型])
というように説明しています。
  
この一般的な@「MATCH(検索値,検索範囲,[照合型])」と
A「match(true,検索範囲=検索値,0)」の違いを理解したい
ということでしょうか?
  
@Aどちらも、検索範囲内の相対位置を求めめているのですが、
基本的な違いは、次の通りと思います。
検索範囲は、配列です。・・・この点は理解していますよね?
@の場合は、「検索範囲」というセル範囲を指定しているから、「配列」です。
Aの場合、「検索範囲」は「配列」ですが、「検索範囲=検索値」は配列にはなっていません。
そこで、この部分を配列にするには、「{検索範囲=検索値}」とする必要があります。
  
試しに、次を実行してみてください。
  
セルA1からA4に
a
b
c
b
を入力します。
次に、セルB1=B4に
=A1="b"
=A2="b"
=A3="b"
=A4="b"
  
と数式を入力してみてください。
B1:FALSE
B2:TRUE
B3:FALSE
B4:TRUE
と表示されるはずです。
  
次にセルC1〜C4を選択しておいて
C1に
=A1:A4="b"
と入力し、[Ctrl]+[Shift]+[Enter]で確定します。→配列関数にします。
B1〜B4の結果と同じになると思います。
  
任意のセルに
=MATCH(TRUE,C1:C4,0)と入力します。結果:「2」が返ります。
それを
「C1:C4」を「A1:A4="b"」に置換えで、[Ctrl]+[Shift]+[Enter]で確定します。
 こちらも「2」が返ります。
 
この説明で、おわかりでしょうか?

回答
投稿日時: 22/05/04 09:05:58
投稿者: WinArrow
投稿者のウェブサイトに移動

追加
  
>255文字を超える場合
は、今回の場合、無関係なので、取り合えず、お忘れください。
 
 
それから、
>=index(検索範囲,match(true,検索範囲=検索値,0))
の「検索範囲」について、
この例では、INDEX関数の第1引数とMATCH関数内の「検索範囲」が同じになっていますが、
INDEX関数の第1引数が、列指定の場合は、同一でも構いませんが、
MATCH関数内の「検索範囲」の方は、必ず列範囲にする必要があります。

回答
投稿日時: 22/05/04 20:55:39
投稿者: MMYS

すでに、WinArrowさんの説明の通りですが、補足すると、配列関数の内部処理の途中で、
 
=MATCH(TRUE,{FALSE,TRUE,FALSE,FALSE},0)
 
という処理が行われていると理解しましょう。
また、仕組みから、指定範囲のセルをすべて計算します。ですから、配列数式は、重たい処理となります。配列数式を複数箇所で使用すると、パフォーマンスに影響します。
 
 
なお、個人的な意見ですが、正直、配列数式の使用は極力避けるべきです。なぜなら、配列数式はとても複雑で、上級者しか扱えません。そのシートを作成した人が、部署異動や退職などでいなくなった場合、だれもメンテナンス出来なくなります。
 
大抵の場合、作業列を用いれば、配列数式は必要ありません。今回例でも、作業列で説明してますが、そのまま作業列を使えば、配列数式は必要ありません。
つまり、作業列を使ったシート構成は、Excel関数を使える人なら、誰でもメンテナンスできると言うことです。
 
 

回答
投稿日時: 22/05/04 21:18:52
投稿者: WinArrow
投稿者のウェブサイトに移動

MMYSさんのコメント・・・・優しいですね
 
MMYSさんのコメントの中に、
配列数式のメンテナンスについて、
「配列数式を作った人以外は難しい」
というくだりがあります。
 
そのあたりを、
私のレスの中の一部(↓)を使って説明します。

引用:

次にセルC1〜C4を選択しておいて
C1に
=A1:A4="b"
と入力し、[Ctrl]+[Shift]+[Enter]で確定します。→配列関数にします。
B1〜B4の結果と同じになると思います。

 
セルC1〜C4に同じ数式が入ります。
しかし、数式を変更しようとした場合、
一つのセルを選択しただけでは、変更拒否されます。
(全セルを選択しないと変更できません。)
全セルといっても、それはどこからどこまでなのか?
を事前に知っておく必要があります。
 
一発で選択するショートカットもありますが、
このように特殊な操作がほかにもあります。
通常の数式と同じと侮ると、痛い目にあいます。
 

投稿日時: 22/05/05 15:24:36
投稿者: のらねこ

WinArrowさん、MMYSさん、ご教授ありがとうございました。
恥ずかしながらお二人の解説を読んでも理解に直結しなかったため、まずは解説を理解できるように自分なりに調べてみようと思います。
 
配列に限らず上級者しか理解できない方法を使いのは私も好ましくないと考えておりますが、止むを得ず使用するケースがあり、仕組みから人に説明できるようになりたいと考えています。
 
配列関数の難しさを改めて実感いたしました。
貴重なご意見もありがとうございました。