質問ばかりで、回答出来る能力が身に付かないのですが(^^;)、また教えてください。
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.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.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です。
あるいは「三文字表示」にこだわるなら、「"高特","高等部特殊";"中特","中学部特殊"」の部分を「"高特","高特殊";"中特","中特殊"」に書換してください。
ありがとうございます。「頑なに」というわけではないです。(^^;)
「長い数式を短くできるのか?」という出発点も「数式などをあまり使わない人でも理解できるようにしたい」なので、おっしゃる通りに作業列を入れて、印刷時には印刷されないようにした方が良いのではとも思っています。
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.6
- 回答日時:
皆さんが、主さんの難しい要望に応えるべく解説してくれているので、
単純な私だったらを。
これ、関数の練習なのか?それとも、結果が出せるようになればいいのか?
どちらなんでしょうか?
結果さえできればいいのであれば、元のデータ自体の小、中、高~を
初めから、置き換えておくか、新しく列を追加して、とりあえず、隣にでも欲しい情報を入れたらいいんじゃないの?
A B C D
1 No. 学部 学部 名前
2 1 小 小学部 東京太郎
3 2 中 中学部 埼玉次郎
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.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.1
- 回答日時:
INDEX関数とMATCH関数の結果を納める列を新たに追加すれば、
簡潔で見易い(他人でも理解しやすい)表と結果になると思います。
> 最新のエクセルを使っていない人のために、IF関数で。
そもそも、IF関数は構造が深いと、理解しづらくバグの元です。
そんな場合は、論理式に置き変えたほうが良い場合があります。
例)
結果=条件論理式1*候補1+条件論理式2*候補2+条件論理式3*候補3+…
ご参考まで。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) Excel 値を返す数式についてです 3 2022/11/21 20:08
- Excel(エクセル) エクセルでエラーを無視して一番左側のセルの値を返したい 2 2023/07/27 13:06
- Visual Basic(VBA) ExcelVBAで、index、match関数を使用して、指定範囲に出力したい 3 2022/10/18 21:53
- Excel(エクセル) Excel2019、2021の日付、曜日の表示について 2 2022/11/29 15:01
- Visual Basic(VBA) vbaエクセルマクロ RemoveDuplicatesについて RemoveDuplicatesを使 3 2023/02/28 01:13
- Excel(エクセル) エクセルについて教えてください。 2 2023/06/14 11:11
- Excel(エクセル) エクセルの関数について 5 2023/01/26 15:26
- Visual Basic(VBA) RemoveDuplicatesメソッドについて教えてください。 シート1にシート2から値をもってく 1 2023/02/28 22:43
- Visual Basic(VBA) サブフォルダ(データ)にある複数の.xlsxファイルのSheet3のA2セルの値で01から左側をB2 2 2022/08/14 15:46
- Excel(エクセル) マクロを簡潔にしたい 6 2022/09/16 10:37
このQ&Aを見た人はこんなQ&Aも見ています
-
プロが教えるわが家の防犯対策術!
ホームセキュリティのプロが、家庭の防犯対策を真剣に考える 2組のご夫婦へ実際の防犯対策術をご紹介!どうすれば家と家族を守れるのかを教えます!
-
INDEXとMATCH関数で#N/Aが出るときに非表示にしたい
Excel(エクセル)
-
INDEX、MATCH関数の#N/Aエラー表示を空欄に
Excel(エクセル)
-
Excel MATCH関数で検索範囲内に同じ値の検索値が複数ある場合
Access(アクセス)
-
-
4
【配列数式を使わずに】表から複数のデータを抽出して、別の表に上から順に並べたい。
Excel(エクセル)
-
5
Excelのmatch関数エラー原因が分かりません
Excel(エクセル)
-
6
エクセルで条件に一致したセルの隣のセルを取得したい
その他(Microsoft Office)
-
7
【Excel】 INDEX ,MATCH でいいのか。該当が複数ある場合
Excel(エクセル)
-
8
Excel-参照セルが空白の場合、別のセルを参照するには。
Excel(エクセル)
-
9
エクセルで数式を入れても値が空欄になります
Excel(エクセル)
-
10
差し込み後、元データを変更し 差し込み先に反映させるには?
Access(アクセス)
-
11
エクセルで指定したセルのどれかに○がはいっていたら○を表示したいです。
その他(コンピューター・テクノロジー)
-
12
参照先セルに値が入っていない時に、「0」や「1900/01/00」などが入らないようにしたいのですが
Excel(エクセル)
-
13
【エクセル】関数で「A1が0でないならB1を表示」の式
その他(コンピューター・テクノロジー)
-
14
E列のセルに数値が入れば(空白でなければ)B列の同じ行のセルに色がつく
その他(Microsoft Office)
-
15
【エクセル】IF関数 AまたはBならばCと表示
その他(Microsoft Office)
-
16
別ファイルを開かず、INDIRECT関数を使用せずに、別ファイルのデータを求めたい
Excel(エクセル)
-
17
EXCELで特定のセルに表示された項目をヘッダーやフッターに出力するには
Excel(エクセル)
-
18
Excelでセル参照したとき、書式も一緒に持ってくるには?
Windows Vista・XP
-
19
エクセルの質問。 対象範囲の中で、一番下に位置する値を返す関数。
Excel(エクセル)
-
20
空白セル内の数式を残したままで空白セル扱いとしたいのですが
Excel(エクセル)
関連するカテゴリからQ&Aを探す
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルで表
-
【マクロ】for next構文について
-
職場の人から聞かれており、こ...
-
ユーザー定義関数をアドイン登...
-
PDFの請求明細をエクセルにしたい
-
Excel関数-文字列で自動作成さ...
-
Excelデータをコピペして、ペー...
-
エクセルの関数について教えて...
-
エクセルで不等号記号(≠)が上に...
-
スプレッドシート、Excelでの数...
-
スプレッドシートで使う数式を...
-
Excelで50個のセルに同じ文字を...
-
[オートフィルタ]で抽出された...
-
エクセルの問題です。絶対値の...
-
エクセル関数を教えてください
-
各ページの1番上の表示について
-
Microsoft Officeの中古は信用...
-
pdfの表をexcelにはりつけて計...
-
Excelのif関数で文字が見えなく...
-
Excel 2019 は、SPILL機能があ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルVBA、別ブックへ転記す...
-
エクセルでの作業計算方法について
-
時間によってファイル名が変わ...
-
【関数】適切な文字数の数字を...
-
Excelについて教えてください
-
エクセル初心者です 関数の入れ...
-
【マクロ】ファイル名の変更に...
-
UNIQUE関数が使えないバージョ...
-
エクセルの計算
-
【関数】先頭だけにある、半角...
-
Excelで、決まった行を繰り返し...
-
Excelでセルの値が同じか...
-
LOOKUP関数を使えばいいのでし...
-
Excel
-
はがきについて。
-
エクセルの条件付き書式につい...
-
エクセルのデーターが2か月前の...
-
エクセル②
-
エクセルで「-0.0」と表示さ...
-
Microsoft1Officeの互換ソフト...
おすすめ情報
教えてもらえれば、応用できるかなと思ったので、「他」にしたのですが、実際は「中特殊の中特」と「高特殊の高特」なんです。(;'∀')
「IF関数 複数条件」でググると、「入れ子にして・・・」という解説が数多く見つかり、階層を確認しながら書いたら、思い通りに表示されるようになりましたが、同じINDEXとMATCHの数式が何度も使われていて、さらにいくつもの入れ子というと、書いた私も分からなくなりそうでした。
そこで、ユーザー定義関数だったり、他の関数でもっと簡単のわかりやすいものが有るのかな?と思っての質問でした。
1ページに印刷したい(他の部分もあり)とか、他のシートでも使うので・・・(^^;)