エクセルで特定のセルにある関数式を複数の場所で利用できる？

質問

エクセルで特定のセルにある関数式を複数の場所で利用できる？

ある別シートのセルに作った条件式を本シートの複数の場所から式自体を参照して利用することは
エクセルで可能でしょうか？

具体的には、シートＡのＨ２セルにＨ５で入力された番号から自動で分類番号を入れる関数式
=IF(H5="","",IF(COUNTIF(H5,"*42*"),1,IF(COUNTIF(H5,"*LA*"),2,IF(COUNTIF(H5,"*67*"),3・・・
をページ毎に入力してあります。
コピー＆ペーストするだけで複製は可能なのですが、分類番号が増えると１箇所で修正を行い
それを全ての場所にコピーする必要が出てきます。

そこで、シートＢに関数式を入力しておき、その式をＨ３で読み出してＨ５の状態から最終的に
Ｈ３へ分類番号を表示する・・・
こんなことはできるのでしょうか？

ちなみに、可能となれば関数式中のＨ５では２ページ以降に適応できなくなるので
(INDIRECT(ADDRESS(ROW()+3,COLUMN()))
に置き換えて自分より３つ下のセルという形で参照させる予定です。

よろしくお願いします。

回答No.3

keithin · ベストアンサー

「条件の追加」についても想定はしましたが，ご質問に掲示された
＞=IF(H5="","",IF(COUNTIF(H5,"*42*"),1,IF(COUNTIF(H5,"*LA*"),2,IF(COUNTIF(H5,"*67*"),3・・・

この式は「=IF(IF(IF(…」の重ね合わせで書かれていたので，あと幾つも条件を追加しないうちにカッコの重ね合わせ限度に達します。結果してそういった使い方では上手くやる以前に数式がそもそも書けないので，そういうお話では無いだろうと判断していました。

方法１）
今の式を
=IF(H5="","",　IF(COUNTIF(H5,"*42*"),1) + IF(COUNTIF(H5,"*LA*"),2) + IF(COUNTIF(H5,"*67*"),3)　)
のように直します。
２個目以降のIFの組み方が「いままでとは違う」ので，よく注意して見逃さないように気を付けてください。

置換を使い，たとえば
IF(COUNTIF(H5,"*67*"),3)
を
IF(COUNTIF(H5,"*67*"),3)+IF(COUNTIF(H5,"*43*"),4)
に全て置換するといった方法で，幾らでも数式を追記していくことが出来ます。

方法２）
非常にテクニカルですが，名前の定義の中に数式を直接記述して計算させる方法も利用できます。

あるシートで「Ｈ２セルを選択する」(必須)
挿入メニューの名前の定義で次の通り登録する
名前　H2の式
参照範囲
=IF(!H5=TEXT(NOW(),""),"",IF(COUNTIF(!H5,"*42*"),1,IF(COUNTIF(!H5,"*LA*"),2,IF(COUNTIF(!H5,"*67*"),3))))
「Ｈ５」じゃなく「！Ｈ５」なので，間違えないように作成します

各シートのＨ２セルに
=H2の式
と記入する。

＃説明不足のため，他のセルに式を転用したいその「他のセル」が，他のシートのH2セルなのか同じシートの別番地セル（たとえばH12に数式を入れてH15を計算したい）というお話なのか，不明です。

上述のサンプルは最大限の自由度で，任意のセルに入れて３つ下のセルを自動的に計算するように作成してあります。
が，その引き替えに数式を修正するときは，またH2セルを選んで名前の定義で数式を追加するなどのコツが必要になります。
シート１，２，３を移るだけでＨ２番地であることは変わらないなら，もっと簡単に扱える風にも出来ました。

方法３）
数式の中に「*42*」やらを直接記入する方法は放棄して，対応表を別に用意，そしてもうちょっと難易度の高い数式で計算させます。

＃ただし。
エクセルマニアなら喜んで難しい数式を考えてくれますし，それを「コピーするだけ」なら多分誰にも出来ますが，ぶっちゃけ言えば(IF(IF…の式でいっぱいいっぱいの)素人さんが読み解くにはちょっと難しい結果になります。ワタシ的にはこのアプローチはあまりお奨めでは無いと思います。
ワイルドカードの逆引きになるので，通常のVLOOKUP等では出来ません。

補足

多数のご指摘・案ありがとうございました。

最初にご指摘いただいたIFの重ね限界は2007だからこそ使えているのは承知で、以前のバージョンでは既に破綻している
式であることを自覚しています。そこで、ステップアップも兼ねて質問させていただきました。

方法１ではIFを重ねず加算として連ねていくことで、重ね限界を超えて機能することに目から鱗でした。
これは、条件に一致した物だけ指定した数字が現れそれ以外が０になることで加算結果が目的の数字になるという解釈でよろしいのでしょうか？

方法２は試してみましたが、うまく機能させることができませんでした。
手順としては、先の回答に補足したH5の部分を"自分から３つ下"という指定方法に書き換えH2に記載した状態では正常に機能している式を
シート２のA1に名前の定義にて"機器選定"という名前にして参照範囲に貼り付け、シート1のH2から"=機器選定"を指定しました。
しかし、数式は機能せず参照ができていないのか？と思いシート２のA1に直接数字を入れてみたところ参照できていました。
説明不足部分については、シート１に４０行毎のページとして連続で作成されています。代表として１ページ目の
H2がH5を参照で説明していましたが、H42がH45を参照、H82がH85を参照・・・と変わっていきます。
機器が増えてそれ以降で分岐数が増えて行く分には、既に作成されている部分に影響がないので
気にしなければいいと言われればそれまでですが、後から見てもわかりやすくしたいため質問させていただきました。

方法３については是非拝見したいと思います。
といっても、コピペするつもりは一切ありません。性格上、公式は暗記せず公式の意味・変数が何故そこにあるのかを理解したいので
見て考えられる限り考え、理解できたときは利用させていただきたいと思います。理解できなかったときは自分はまだその域ではないので
わかる様になるまで封印いたします。
また、やはりVLOOKUP等が利用できないのはワイルドカードの逆引きになるのが原因なんですね(^-^;
関係ない話ですが、FORTRAN・CASLでプログラミングをかじった事があるためか、他人のプログラムをそのまま転用するのは
後から自分が苦しむことを経験しているからかもしれません・・・

回答No.2

mu2011

一般的に入力される番号から確定できる条件が固定（文字列の開始位置）ならば、
この文字列と分類番号を配列にして別シートに定義すればVLOOKUP関数の利用で
随時数式を訂正する必要はありませんのでご検討下さい。

補足

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

VLOOKUP・HLOOKUPについても調べてみましたが私が関数として組み立ててみた限りでは
思った様な結果を出すことができませんでした。
具体的な目的は・・・
１ページ毎に（以下、１ページ目を参考に記載します）セルH2にて関数を使いセルH5に入力された
#42RやLA・#27R・#51R・#51GSといった器具番号から特定の文字列を検索し、該当する分類番号
を表示する。この数字を使用して別シートに作成した列方向に器具毎の台帳項目を羅列した表から
オフセットして項目を抜き出す様に細工しています。

ワイルドカードが検索する値側ではなく検索される側にあるのが難しくしている元かとは思いますが・・・

回答No.1

keithin

Ctrl+Hで置換を出し，ご利用のエクセルのバージョンに寄りますがオプション>を開いて検索場所をブック全体にして
~*42~*
を
*44*
に全て置換してしまうのが，多分一番手っ取り早い方法です。

さもなくば
Sheet0のA1，A2，A3にそれぞれ42，LA，67を記入しておき，
=IF(H5="","",IF(COUNTIF(H5,"*"&Sheet0!A1&"*"),1,…
のように全部の式が一箇所を参照する事で，そこを直せば全部に効いてくるように仕込みます。

補足

説明不足で大変申し訳ありませんでした。

既に入力されている部分を修正することより、基本的には項目が増えていくことによる修正が多くあります。
具体的には、現在10までの分類番号分岐がありますが「*42*」で#42Rを検出して「１」としていた物が
#42Gという物が増え、かつ内容が異なるため「*42*」を「*42R」に修正して、末尾に
IF(COUNTIF(H5,"*42G"),11,・・・
を追加修正するといった具合です。

基本的にはそれ以前の分岐に影響は出ないため、そのままにしておいても問題はないのですが
この文書を他の人が引き継いだときに、セルによって統一されていないのは内容の理解に時間が
かかったり、見たときに美しくないと思ったため一箇所で管理できたら・・・と思った次第です。