IF関数が長くて、実行できない。

質問

関数初心者です。Excel2003を使用しております。

～目的～
IF関数が長くなり、実行することが出来ない。
VLOOKUP関数の複数範囲の中から検索できる数式を作成したい。

～状況～
・VLOOKUP関数で複数の範囲を指定している。
・各範囲は_コード表という名前の定義付けをしており、_コード表1～12まである。
・_コード表12までの複数範囲の中から検索できる数式を作成したい。

=IF(ISERROR(VLOOKUP(B5&"_"&C5&"_"&D5,_コード表１,5,0)),IF(ISERROR(VLOOKUP(B5&"_"&C5&"_"&D5,_コード表2,5,0)),IF(ISERROR(VLOOKUP(B5&"_"&C5&"_"&D5,_コード表3,5,0)),"",VLOOKUP(B5&"_"&C5&"_"&D5,_コード表3,5,0)),VLOOKUP(B5&"_"&C5&"_"&D5,_コード表2,5,0)),VLOOKUP(B5&"_"&C5&"_"&D5,_コード表１,5,0))

上記の数式で検索が出来るのですが、
_コード表12までになると、関数が長すぎて実行できません。
何か良い方法はありませんでしょうか。
ご回答をお待ちしております。

回答No.4

mu2011 · ベストアンサー

>IF関数が長くなり、実行することが出来ない。
　⇒IF関数のネスト数は８個までです。
　　IF関数ネストで数式を連ねと長くなり難解、変更・追加等の保守性が悪い、このような数式は絶対やめた方がよい。（ご例示の数式でさえ非常に判りずらい）

>VLOOKUP関数の複数範囲の中から検索できる数式
　⇒私見ですが、数式はシンプルなほど最良と思います。
　　ご例示の数式から、コード表は同一構成の様だし、コードは重複していない事からコード表の集約を検討されては如何でしょうか。

お礼

ご回答ありがとうございました。

IF関数のネスト数は8個までだったのですね。
教えて頂きありがとうございます。

仰る通り、私の数式はわかりづらく、保守性が悪いと感じます。
今回の件は、コード表を一つにまとめ、シンプルな数式を立てることにしたいと思います。

数式のアドバイス大変参考になりました。
これからもシンプルを心掛けていきたいと思います。
ありがとうございました。

どなたをベストアンサーにするか非常に迷いましたが、
一番解決するのに役立ちましたので、選ばせて頂きます。
ご回答頂きました皆様、誠にありがとうございました。
助かりました。

回答No.3

x0000x

マクロで実現するのであれば、以下で実現可能です。

手順
(1)Alt+F11を押下。
　→VBEが表示されマクロ入力が可能。
(2)「挿入」-「標準モジュール」をクリック
　→標準モジュールに「Module1」が追加されます。
(3)マクロを記述（以下を貼り付けする）

Public Function EXVlookup(ByVal value As Variant) As Variant
On Error GoTo EXVlookup_err

'初期化
EXVlookup = Empty
'複数のVLOOKUPで検索を行う
If EXVlookup = Empty Then
EXVlookup = Application.WorksheetFunction.VLookup(value, Range("_コード表1"), 5, False)
End If
If EXVlookup = Empty Then
EXVlookup = Application.WorksheetFunction.VLookup(value, Range("_コード表2"), 5, False)
End If
If EXVlookup = Empty Then
EXVlookup = Application.WorksheetFunction.VLookup(value, Range("_コード表3"), 5, False)
End If
If EXVlookup = Empty Then
EXVlookup = Application.WorksheetFunction.VLookup(value, Range("_コード表4"), 5, False)
End If
If EXVlookup = Empty Then
EXVlookup = Application.WorksheetFunction.VLookup(value, Range("_コード表5"), 5, False)
End If
If EXVlookup = Empty Then
EXVlookup = Application.WorksheetFunction.VLookup(value, Range("_コード表6"), 5, False)
End If
If EXVlookup = Empty Then
EXVlookup = Application.WorksheetFunction.VLookup(value, Range("_コード表7"), 5, False)
End If
If EXVlookup = Empty Then
EXVlookup = Application.WorksheetFunction.VLookup(value, Range("_コード表8"), 5, False)
End If
If EXVlookup = Empty Then
EXVlookup = Application.WorksheetFunction.VLookup(value, Range("_コード表9"), 5, False)
End If
If EXVlookup = Empty Then
EXVlookup = Application.WorksheetFunction.VLookup(value, Range("_コード表10"), 5, False)
End If
If EXVlookup = Empty Then
EXVlookup = Application.WorksheetFunction.VLookup(value, Range("_コード表11"), 5, False)
End If
If EXVlookup = Empty Then
EXVlookup = Application.WorksheetFunction.VLookup(value, Range("_コード表12"), 5, False)
End If

Exit Function

EXVlookup_err:
Resume Next

End Function

-------------[　ここまで貼り付け ]-----------------
(4)Excelシートのセルに以下の数式を入力します。
　"=EXVlookup(B5&"_"&C5&"_"&D5)"

　※上記の様に標準モジュールでPUBLICのFUNCTIONがセルの計算式に　
　　関数として記述可能です。

数式を入力する、B5セル、C5セル、D5セルに値を入力することで
「EXVlookup」マクロが呼び出されます。

マクロ処理はVlookup関数を実行し、検索できない場合,
複数の”_コード表"を検索します。

たとえば、
上記マクロが貼り付け済みである時、
(1)A1セルの数式に"=EXVlookup(B5&"_"&C5&"_"&D5)"を入力し、
(2)B5セル、C5セル、D5セルに検索文字列を入力すると各コード表を検索し、
検索結果をA1セルに格納します。

お試しを！

お礼

ご回答ありがとうございます。
マクロの発想はありませんでした。
ただマクロの入力をしたことがなく、今回は時間が迫っている為
これからの参考にさせて頂きます。
大変勉強になりました。ありがとうございます。

回答No.2

hallo-2007

ひとつのセルに記述できる関数の文字数は256文字
コード表が１２あればひとつのコード表に対して20文字以内
現在の関数を改良しても無理だと思います。
残念ながら
コード表をひとつにまとめるしか方法がないのでは！！

お礼

ご回答ありがとうございます。
コード表をまとめる方向で進めたいと思います。
ありがとうございました。

回答No.1

keithin

取ってくる値が数値だった場合
=IF(ISERROR(VLOOKUP(B5&"_"&C5&"_"&D5,_コード表１,5,0)),0,VLOOKUP(B5&"_"&C5&"_"&D5,_コード表１,5,0))
+IF(ISERROR(VLOOKUP(B5&"_"&C5&"_"&D5,_コード表2,5,0)),0,VLOOKUP(B5&"_"&C5&"_"&D5,_コード表2,5,0))
+IF(ISERROR(VLOOKUP(B5&"_"&C5&"_"&D5,_コード表3,5,0)),0,VLOOKUP(B5&"_"&C5&"_"&D5,_コード表3,5,0))

取ってくる値が文字列だった場合
=IF(ISERROR(VLOOKUP(B5&"_"&C5&"_"&D5,_コード表１,5,0)),"",VLOOKUP(B5&"_"&C5&"_"&D5,_コード表１,5,0))
&IF(ISERROR(VLOOKUP(B5&"_"&C5&"_"&D5,_コード表2,5,0)),"",VLOOKUP(B5&"_"&C5&"_"&D5,_コード表2,5,0))
&IF(ISERROR(VLOOKUP(B5&"_"&C5&"_"&D5,_コード表3,5,0)),"",VLOOKUP(B5&"_"&C5&"_"&D5,_コード表3,5,0))

のように数珠繋ぎにしていくと，沢山計算できます。

お礼

ご回答ありがとうございます。
教えて頂いた方法で多く入力することが出来ました。
大変勉強になります。
本当にありがとうございました。