IF関数の複数条件とINDEX関数とMATCH関数を使った長い関数を短くできる？

質問ばかりで、回答出来る能力が身に付かないのですが(^^;)、また教えてください。

　　A　　B　　C
1　No.　学部　名前
2　１　　小　東京太郎
3　２　　中　埼玉次郎
4　３　　小　神奈川三郎
5　４　　高　千葉四郎
6　５　　他　大阪五郎
というような「リスト」シートが有ります。
別のシートから、Index関数とMacth関数を使って、参照します。
B2セルに「4」と入力し、C2セルに4の千葉四郎さんの学部を参照したい場合、
C2セルに「=INDEX(リスト!B$2:B$6,MATCH($B2,リスト!$A$2:$A$6,0))」
とすると、当然「高」が代入されます。

このままなら、問題無いのですが、
「小は小学部」「中は中学部」「高は高等部」「他はそのまま他」と表示させたいのです。
そこで、C2セルに
=IF(INDEX(リスト!B$2:B$6,MATCH($B2,リスト!$A$2:$A$6,0))="高","高等部",IF(COUNTIF(INDEX(リスト!B$2:B$6,MATCH($B2,リスト!$A$2:$A$6,0)),"*他*")=1,INDEX(リスト!B$2:B$6,MATCH($B2,リスト!$A$2:$A$6,0)),INDEX(リスト!B$2:B$6,MATCH($B2,リスト!$A$2:$A$6,0))&"学部"))
としました。
ちゃんと、代入されています。
※ IFS関数も有るのが分かりましたが、最新のエクセルを使っていない人のために、IF関数で。

「ユーザー定義関数を作れば、出来るのかな？」とも考えたのですが、まだまだの知識で作ることが出来ませんでした。
この長い関数をどうにかすることは出来るのでしょうか？

質問者からの補足コメント

• 

  教えてもらえれば、応用できるかなと思ったので、「他」にしたのですが、実際は「中特殊の中特」と「高特殊の高特」なんです。(;'∀')

  No.3の回答に寄せられた補足コメントです。 補足日時：2020/12/01 23:25

• 

  「IF関数 複数条件」でググると、「入れ子にして･･･」という解説が数多く見つかり、階層を確認しながら書いたら、思い通りに表示されるようになりましたが、同じINDEXとMATCHの数式が何度も使われていて、さらにいくつもの入れ子というと、書いた私も分からなくなりそうでした。
  　そこで、ユーザー定義関数だったり、他の関数でもっと簡単のわかりやすいものが有るのかな？と思っての質問でした。
  
  1ページに印刷したい(他の部分もあり)とか、他のシートでも使うので･･･(^^;)

  No.6の回答に寄せられた補足コメントです。 補足日時：2020/12/02 00:03

No.2 ベストアンサー 回答者： 銀鱗 回答日時： 2020/11/30 15:28

自分ならINDEX関数とMATCH関数の組み合わせではなくVLOOKUP関数を使う。

その後でINDEX関数で置き換えるかな。

C2に
　=VLOOKUP($B2,リスト!$A$2:$C$6,2,TRUE)
で ”高” が表示されるなら、
D2セルに
　=INDEX({"小学部","中学部","後頭部","他"},MATCH(C2,{"小","中","高","他"},0))
と作れば、D2セルに ”後頭部” と表示される。

C2セルを参照している箇所に VLOOKUP関数を使ったB2セルの関数を入れるだけ。

　=INDEX({"小学部","中学部","後頭部","他"},MATCH(VLOOKUP($B2,リスト!$A$2:$C$6,2,TRUE),{"小","中","高","他"},0))

こんな感じになる。

当然ながら、エラー対策はしていません。
あと、メンテナンス性を上げるなら変換リストを別に設けたほうが良い。
そうすれば、INDEX関数やMATCH関数内で使った配列を参照に置き換えるだけで済む。
※ 配列：{ } で囲んだところ。

　　B　　C
5　小　商学部
6　中　中学部
7　高　後頭部
8　他　他

とすると、
　=INDEX($A$5:$A$8,MATCH(VLOOKUP($B2,リスト!$A$2:$C$6,2,TRUE),$B$5:$B$8,0))

とスッキリする。


・・・余談・・・

IFS関数は複数の条件で一つの結果を返す物なので、ここでは使いようがないよ。
また、他の回答者さんも仰るようにIF関数内でカスケードにしないほうが良い。

この回答へのお礼

例として挙げたものは、左端ですが、実際のシートでは端ではないのでVLOOKUPが使えると思い付きませんでした。
※範囲指定の中では、左端になるので使えましたが。
「配列を参照に」も配列自体、私の勉強にもなりましたし、実際参照にすることで、他の箇所でも使うことが出来たので、有効な手段でした。
ありがとうございました。

お礼日時：2020/12/08 10:17

No.9 回答者： goomania 回答日時： 2020/12/07 16:22

いろいろなアイディアがでているようですが、頑なに作業列を使わないという判断で進むのであれば、No.2さんのご指摘のようにVLOOKUP関数を使用する方法を支持します。

まずはリストのB列の名称変更(「高→高等部」など)を検討すべきです。

それ以外の方法としては、メンテナンス、数式文字数等を考慮すると、

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(VLOOKUP($B4,リスト!$A:$B,2,0),"高","高等部"),"中","中学部"),"小","小学部"),"特","特殊")

という数式はどうでしょうか。

この数式は、「高特」を「高等部特殊」、「中特」を「中学部特殊」まで含めた表示変更に対応できます。
ただし、エラーチェックはしていませんので、単独の「高」「中」「小」および「高特」「中特」以外で、文字中に「高」「中」「小」「高」「特」を含む文字列も勝手に変換されますし、含まないものはそのまま表示されます。

同じくエラーチェックなしで、若干長めになりますが、IFERROR関数を使用し、VLOOKUP関数の結果から、さらにVLOOKUP関数で{}内に書込された「対応配列」から変換後の表示を検索する以下の数式のほうが、判りやすいのかも知れません。

=IFERROR(VLOOKUP(VLOOKUP($B2,リスト!$A:$B,2,0),{"高","高等部";"中","中学部";"小","小学部";"高特","高等部特殊";"中特","中学部特殊"},2,0),VLOOKUP($B2,リスト!$A:$B,2,0))

この数式は「高」「中」「小」「高特」「中特」のみを特定して変換するため、これ以外で文字中に「高」「中」「小」「高」「特」を含む文字列が勝手に変換されることはありません。
また、「高特」「中特」はそのまま表示させるのであれば、{}内のその部分を削除すればOKです。
あるいは「三文字表示」にこだわるなら、「"高特","高等部特殊";"中特","中学部特殊"」の部分を「"高特","高特殊";"中特","中特殊"」に書換してください。

この回答へのお礼

ありがとうございます。「頑なに」というわけではないです。(^^;)
「長い数式を短くできるのか？」という出発点も「数式などをあまり使わない人でも理解できるようにしたい」なので、おっしゃる通りに作業列を入れて、印刷時には印刷されないようにした方が良いのではとも思っています。

お礼日時：2020/12/08 10:09

No.8 回答者： enunokokoro 回答日時： 2020/12/02 11:47

No.3の回答者です。

質問文を単純に読んでいました。
質問にある関数の組み合わせを正しく理解していませんでした。

質問にある関数の組み合わせだと、「高」の場合は「高等部」にして、
それ以外は参照した値に「学部」を付けたものですね。
ただし「他」が入った文字列の場合(ワイルドカード使った「*他*」)は、そのまま表示するということをしたいようですね。
ワイルドカードのものがなければ、
　=IF(INDEX(リスト!B$2:B$16,MATCH($B2,リスト!$A$2:$A$16,0))="高","高等部",INDEX(リスト!B$2:B$16,MATCH($B2,リスト!$A$2:$A$16,0))&"学部")
だけで済むものですね。
これに、その他としてのワイルドカードのある文字列も対象にしたい
ということなら、以下のようなもので対応します。

　=IFNA(IFERROR(CHOOSE(FIND(INDEX(リスト!B$2:B$16,MATCH($B2,リスト!$A$2:$A$16,0)),"小中高中特高特"),"小学","中学","高等","中特学",,"高特学")&"部",INDEX(リスト!B$2:B$16,MATCH($B2,リスト!$A$2:$A$16,0))),"")

質問の関数での組み合わせより、ちょっと短くなっているはずです。
条件付き書式を使えば、IF系関数を使わないので、もっとシンプルに
できます。でも条件付き書式は使いたくないのでしょうね。

この回答へのお礼

ありがとうございます。条件付き書式は、コピペすると、範囲が変わっていくのがちょっと嫌なんですよねぇ(;'∀')

お礼日時：2020/12/05 08:42

No.7 回答者： ぐーるぐる 回答日時： 2020/12/02 00:08

印刷時に邪魔なら列を非表示にしたら、問題ないですよ？

持つべき情報は正確に入れておいた方が、ややこしい関数を使うより、容易だと思われます。自分でわからないものを難しく書いたところで、メンテナンスで崩れたら終了ですよ？シンプルに作ることをお勧めします。

この回答へのお礼

そうですね、ファイルで他の人に渡す前提なので、なおさら見てわかるようにしておいた方が親切ですね。非表示って、自分でも見落としてしまいそうなので、やるならグループ化かなと思いました。

お礼日時：2020/12/05 08:46

No.6 回答者： ぐーるぐる 回答日時： 2020/12/01 10:20

皆さんが、主さんの難しい要望に応えるべく解説してくれているので、

単純な私だったらを。
これ、関数の練習なのか？それとも、結果が出せるようになればいいのか？
どちらなんでしょうか？

結果さえできればいいのであれば、元のデータ自体の小、中、高～を
初めから、置き換えておくか、新しく列を追加して、とりあえず、隣にでも欲しい情報を入れたらいいんじゃないの？

A B C 　　　D
1 No. 学部 学部 　　　名前
2 1 小 小学部 東京太郎
3 2 中 中学部 埼玉次郎

No.5 回答者： enunokokoro 回答日時： 2020/12/01 09:15

No.3の回答者です。

VLOOKUP関数を使い別に用意した表を使えば、No.2の回答者さん
が書いているように短くてスッキリします。
"小学部","中学部","高等部","他"を関数の引数として入れないの
ですから、短くなりますよね。

No.3の回答の関数は、別表を用意しないでも利用できるものです。
別解として、以下のようなものでも結果は同じです。
　=IFNA(MID("小学部中学部高等部他",FIND(INDEX(リスト!B$2:B$6,MATCH($B2,リスト!$A$2:$A$6,0)),"小中高他")*3-2,3),"")
エラー処理をしたものなら、ちょっとだけ短くなります。
今回のような、"小学部","中学部","高等部"が同じ文字数ならば、
"他"を最後に持ってくることで残りの文字数として対応できます。

No.4 回答者： mike_g 回答日時： 2020/11/30 16:48

添附圖參照(Excel 2019)

この回答へのお礼

教えていただいた中で、一番短い数式かなと思います。ありがとうございました。

お礼日時：2020/12/08 10:11

No.3 回答者： enunokokoro 回答日時： 2020/11/30 15:35

もっと短くできるのかもしれませんが。

私がすぐに思いつく方法だと、
　=CHOOSE(FIND(INDEX(リスト!B$2:B$6,MATCH($B2,リスト!$A$2:$A$6,0)),"小中高他"),"小学部","中学部","高等部","他")
での対応ですね。
FIND関数で一文字としてリストから抽出した結果から位置をだし、
CHOOSE関数で、位置に対応した値を出しています。

一応エラー対応として、添付画像では
　=IFNA(CHOOSE(FIND(INDEX(リスト!B$2:B$6,MATCH($B2,リスト!$A$2:$A$6,0)),"小中高他"),"小学部","中学部","高等部","他"),"")
で処理しています。

No.1 回答者： angkor_h 回答日時： 2020/11/30 15:13

INDEX関数とMATCH関数の結果を納める列を新たに追加すれば、

簡潔で見易い(他人でも理解しやすい)表と結果になると思います。

> 最新のエクセルを使っていない人のために、IF関数で。
そもそも、IF関数は構造が深いと、理解しづらくバグの元です。
そんな場合は、論理式に置き変えたほうが良い場合があります。
例)　
結果=条件論理式1*候補1＋条件論理式2*候補2＋条件論理式3*候補3+…
ご参考まで。

この回答へのお礼

はい、しかも長い数式だと、なおさら間違いの元になるだろうというのが、今回の質問の動機です。ありがとうございました。

お礼日時：2020/12/05 08:50