質問ばかりで、回答出来る能力が身に付かないのですが(^^;)、また教えてください。
A B C
1 No. 学部 名前
2 1 小 東京太郎
3 2 中 埼玉次郎
4 3 小 神奈川三郎
5 4 高 千葉四郎
6 5 他 大阪五郎
というような「リスト」シートが有ります。
別のシートから、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.1
- 回答日時:
INDEX関数とMATCH関数の結果を納める列を新たに追加すれば、
簡潔で見易い(他人でも理解しやすい)表と結果になると思います。
> 最新のエクセルを使っていない人のために、IF関数で。
そもそも、IF関数は構造が深いと、理解しづらくバグの元です。
そんな場合は、論理式に置き変えたほうが良い場合があります。
例)
結果=条件論理式1*候補1+条件論理式2*候補2+条件論理式3*候補3+…
ご参考まで。
No.2ベストアンサー
- 回答日時:
自分なら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が使えると思い付きませんでした。
※範囲指定の中では、左端になるので使えましたが。
「配列を参照に」も配列自体、私の勉強にもなりましたし、実際参照にすることで、他の箇所でも使うことが出来たので、有効な手段でした。
ありがとうございました。
No.3
- 回答日時:
もっと短くできるのかもしれませんが。
私がすぐに思いつく方法だと、
=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.5
- 回答日時:
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.6
- 回答日時:
皆さんが、主さんの難しい要望に応えるべく解説してくれているので、
単純な私だったらを。
これ、関数の練習なのか?それとも、結果が出せるようになればいいのか?
どちらなんでしょうか?
結果さえできればいいのであれば、元のデータ自体の小、中、高~を
初めから、置き換えておくか、新しく列を追加して、とりあえず、隣にでも欲しい情報を入れたらいいんじゃないの?
A B C D
1 No. 学部 学部 名前
2 1 小 小学部 東京太郎
3 2 中 中学部 埼玉次郎
No.8
- 回答日時:
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系関数を使わないので、もっとシンプルに
できます。でも条件付き書式は使いたくないのでしょうね。
No.9
- 回答日時:
いろいろなアイディアがでているようですが、頑なに作業列を使わないという判断で進むのであれば、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です。
あるいは「三文字表示」にこだわるなら、「"高特","高等部特殊";"中特","中学部特殊"」の部分を「"高特","高特殊";"中特","中特殊"」に書換してください。
ありがとうございます。「頑なに」というわけではないです。(^^;)
「長い数式を短くできるのか?」という出発点も「数式などをあまり使わない人でも理解できるようにしたい」なので、おっしゃる通りに作業列を入れて、印刷時には印刷されないようにした方が良いのではとも思っています。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
このQ&Aを見た人はこんなQ&Aも見ています
-
あるあるbotに投稿したけど採用されなかったあるある募集
あるあるbotに投稿したけど採用されなかったあるあるをこちらに投稿してください
-
フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
あなたが普段思っている「これまだ誰も言ってなかったけど共感されるだろうな」というあるあるを教えてください
-
映画のエンドロール観る派?観ない派?
映画が終わった後、すぐに席を立って帰る方もちらほら見かけます。皆さんはエンドロールの最後まで観ていきますか?
-
海外旅行から帰ってきたら、まず何を食べる?
帰国して1番食べたくなるもの、食べたくなるだろうなと思うもの、皆さんはありますか?
-
天使と悪魔選手権
悪魔がこんなささやきをしていたら、天使のあなたはなんと言って止めますか?
-
【配列数式を使わずに】表から複数のデータを抽出して、別の表に上から順に並べたい。
Excel(エクセル)
-
INDEX、MATCH関数の#N/Aエラー表示を空欄に
Excel(エクセル)
-
【Excel】 INDEX ,MATCH でいいのか。該当が複数ある場合
Excel(エクセル)
-
-
4
Excel MATCH関数で検索範囲内に同じ値の検索値が複数ある場合
Access(アクセス)
-
5
INDEXとMATCH関数で#N/Aが出るときに非表示にしたい
Excel(エクセル)
-
6
リンク先のファイルを開かなくても、値が読み込めるようにできますか?(SUMIFSを使ってます)
Excel(エクセル)
-
7
別ファイルを開かず、INDIRECT関数を使用せずに、別ファイルのデータを求めたい
Excel(エクセル)
-
8
【エクセル】関数で「A1が0でないならB1を表示」の式
その他(コンピューター・テクノロジー)
-
9
E列のセルに数値が入れば(空白でなければ)B列の同じ行のセルに色がつく
その他(Microsoft Office)
-
10
IF関数で、時間を条件にしたい場合の式について
Access(アクセス)
-
11
エクセルで条件に一致したセルの隣のセルを取得したい
その他(Microsoft Office)
-
12
エクセルで指定したセルのどれかに○がはいっていたら○を表示したいです。
その他(コンピューター・テクノロジー)
-
13
ある範囲のセルから任意の値を検索して、その隣のセルの値を取得するという関数はありますか?
Excel(エクセル)
-
14
エクセルで60進法計算の仕方を教えてください
数学
-
15
Excelのmatch関数エラー原因が分かりません
Excel(エクセル)
-
16
SUMIF関数で、「ブランク以外を合計」を指定したい
その他(Microsoft Office)
-
17
スプレッドシートでindexとIMPORTRANGEとmatchの組み合わせ
Excel(エクセル)
-
18
INDEX、MATCH関数での空欄を「0」ではなくそのまま空欄する式について
Excel(エクセル)
-
19
Excelで数式の入っているセルを空白と認識させたい
Excel(エクセル)
-
20
INDIRECT関数の代替方法は?
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・人生のプチ美学を教えてください!!
- ・10秒目をつむったら…
- ・あなたの習慣について教えてください!!
- ・牛、豚、鶏、どれか一つ食べられなくなるとしたら?
- ・【大喜利】【投稿~9/18】 おとぎ話『桃太郎』の知られざるエピソード
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルの質問です。 F列からL...
-
作成した数式を値として表示し...
-
エクセルの文字が途中から消える
-
条件付き書式設定で罫線を引き...
-
Excel関数について教えてくださ...
-
Excelの警告について
-
Excel関数について教えてくださ...
-
ワークシートに出現したこの画...
-
エクセルのセル内に分数などの...
-
タイムスタンプとテキストから...
-
EXCELの散布図で日付が1900年に...
-
エクセルでファイルの最終更新...
-
シートの情報を別のシートへま...
-
マクロの処理が遅くなった
-
エクセルの数式バーのフォント...
-
エクセルの「条件付き書式」を...
-
エクセル日付 文字列の関数がエ...
-
Excelでの文字色
-
Excelの数字の前に入っている空...
-
Excelについて教えてください。...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excelの警告について
-
Excelで数値を時間数に変換する...
-
エクセルの数式バーのフォント...
-
エクセルで数字の組み合わせを...
-
エクセルを使用して、円周率を...
-
Excelで特定の文字列が含まれて...
-
Excel 対象のセルに入力が無い...
-
任意の値が存在する行に名前を...
-
エクセルでファイルの最終更新...
-
index関数の説明をお願いします。
-
条件付き書式でやりたいのですが
-
重複しない値を取り出したい
-
【ExcelVBA】UTF-8(BOM無)でC...
-
【マクロ】マクロが割当てされ...
-
エクセル IF計算式?でしょうか?
-
エクセルで曜日を入れたい
-
表中の指定した条件の文字列を...
-
【Excel】版が同じ事を示す番号...
-
EXCELの散布図で日付が1900年に...
-
Excelについて。Excelに縦1列に...
おすすめ情報
教えてもらえれば、応用できるかなと思ったので、「他」にしたのですが、実際は「中特殊の中特」と「高特殊の高特」なんです。(;'∀')
「IF関数 複数条件」でググると、「入れ子にして・・・」という解説が数多く見つかり、階層を確認しながら書いたら、思い通りに表示されるようになりましたが、同じINDEXとMATCHの数式が何度も使われていて、さらにいくつもの入れ子というと、書いた私も分からなくなりそうでした。
そこで、ユーザー定義関数だったり、他の関数でもっと簡単のわかりやすいものが有るのかな?と思っての質問でした。
1ページに印刷したい(他の部分もあり)とか、他のシートでも使うので・・・(^^;)