Excel INDEX関数の使い方

質問

添付図はA列B列にデータが入っており　E1：E5のようなリスト(部署一覧)を用意しておき　C列に部署名をとりだそうというものです
C2に　=INDEX(部署一覧,MATCH(1,FIND(部署一覧,B2),0)) という数式を入力してCtrl+Shift+Enterで配列数式にすれば部署名を取り出せるとありました

1．その通りにやってなるほど部署名はとりだせたのですが　MATCH関数の引数に”1”とあるのがその意味が分かりません　どなたか解説をお願いできないでしょうか

2．また部署名の前に別の文字列　例えば”関東支社”のような文字列がある場合　部署名だけを取り出すことはできないものでしょうか

よろしくお願いします

回答No.1

KURUMITO · ベストアンサー

例えば部署一覧がシート2のA1セルからA10セルに有るとしてお示しと同じC2セルへの式は次の式になります。

{=INDEX(Sheet2!A$1:A$10,MATCH(1,FIND(Sheet2!A$1:A$10,B2),0))}

ここで配列数式なっていますのでFIND関数の中身について考えてみます。
E1セルに次の式を入力して下方にドラッグコピーしてみます。
=FIND(Sheet2!A1,B$2)　ではエラーの表示
=FIND(Sheet2!A２,B$2)　では１
=FIND(Sheet2!A３,B$2)　ではエラーの表示
=FIND(Sheet2!A1,B$2)　ではエラーの表示
以上のようになって1に該当するSheet2!A2のデータが求める値となります。
つまりMATCH関数ではFIND関数で並んだデータの中から1の相当する行を検索していることになります。
ところでFIND(Sheet2!A2,B$2)からも分かるようにシート2にあるデータがB2セルの中でどの位置にあるかを見ていることになります。先頭にあるから1となります。先頭に無い場合には1ではなく3や4などの値となります。
先頭にある文字がシート2にある文字と一致する場合にのみ1となります。関東支社と4文字が前にあるのでしたらMATCH関数では引数を1ではなく5とすればよいでしょう。

お礼

ありがとうございました
大変参考になりました

回答No.4

tom04

こんにちは！
横からお邪魔します。

要するに「部署一覧」と名前定義した範囲の文字列があれば、その文字列を表示する！という解釈での一例です。
C2セルに
=IF(OR(ISNUMBER(FIND(部署一覧,B2))),INDEX(部署一覧,SUMPRODUCT((ISNUMBER(FIND(部署一覧,B2)))*ROW($A$1:$A$4))),"該当なし")
（これも配列数式になりますので、Shift＋Ctrl＋Enterで確定です）

※　数式内の　$A$1:$A$4　部分は
名前定義しているデータの行数に合わせます。
今回は4行のデータですので、$A$1:$A$4　としています。
A列でなくても構いませんが、必ず1行目からの指定でないと
INDEX関数の何行目を表示するのか？という関数ですので、表示される行がずれてしまいます。

これで、質問の（1）・（2）の両方に対応できると思います。m(_ _)m

お礼

ありがとうございました
参考になりました

回答No.3

noname#204879

》 E1：E5のようなリスト(部署一覧)を用意しておき
の「E1：E5」は間違いで「E2：E5」とあったはずです。誤記しないやうにネ。
部署一覧にあるデータは何れもＢ列の該当セルの左端から始まっているので、FIND(部署一覧,Bn) が返す数値は 1 ですよね。

添付図を参照ください。
範囲 F1:L1 には左から文字列で B2、B3、…、B8 を入力しています。
範囲 F2:F5 を選択して、[数式バー]内で式 =FIND(部署一覧,INDIRECT(F1)) を入力して、CtrlキーとShiftキーを抑えたまま Enterキーを「エイヤッ！」と叩き付けた後、当該範囲を右にズズーッとドラッグ＆ペーストした結果が添付図です。

見て分かるように、1 が部署一覧の何行目にあるかを知るために「MATCH関数の引数に”1”とある」のです。

今回は、取り敢えず、此処まで。

お礼

ありがとうございました
参考になりました

回答No.2

KURUMITO

回答No1です。
部署名の前に別の文字列が有る場合に対処法ですが例えばE列からN列までを作業列として、その結果を受けてC列に部署名を表示させるとします。
E2セルには次の式を入力して式を確定する段階でCtrlキーとShiftキーを押しながらEnterキーを押します。その式を右横方向にN2セルまでドラッグコピーしたのちに下方にもドラッグコピーします。

=IF($B2="","",IF(ISERROR(INDEX(Sheet2!$A$1:$A$20,MATCH(COLUMN(A1),FIND(Sheet2!$A$1:$A$20,$B2),0))),"",INDEX(Sheet2!$A$1:$A$20,MATCH(COLUMN(A1),FIND(Sheet2!$A$1:$A$20,$B2),0))))

その後にC2セルには次の式を入力して下方にドラッグコピーします。

=IF(COUNTIF(E2:N2,"*?"),INDEX(E2:N2,MATCH("*?",E2:N2,0)),"")

なお、作業列が目障りでしたらE列からN列を選択して右クリックをし「非表示」を選択すればよいでしょう。

お礼

ありがとうございました
こちらはちょっと歯が立たない