• ベストアンサー
  • 困ってます

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

エクセルで特定のセルにある関数式を複数の場所で利用できる? ある別シートのセルに作った条件式を本シートの複数の場所から式自体を参照して利用することは エクセルで可能でしょうか? 具体的には、シートAのH2セルにH5で入力された番号から自動で分類番号を入れる関数式 =IF(H5="","",IF(COUNTIF(H5,"*42*"),1,IF(COUNTIF(H5,"*LA*"),2,IF(COUNTIF(H5,"*67*"),3・・・ をページ毎に入力してあります。 コピー&ペーストするだけで複製は可能なのですが、分類番号が増えると1箇所で修正を行い それを全ての場所にコピーする必要が出てきます。 そこで、シートBに関数式を入力しておき、その式をH3で読み出してH5の状態から最終的に H3へ分類番号を表示する・・・ こんなことはできるのでしょうか? ちなみに、可能となれば関数式中のH5では2ページ以降に適応できなくなるので (INDIRECT(ADDRESS(ROW()+3,COLUMN())) に置き換えて自分より3つ下のセルという形で参照させる予定です。 よろしくお願いします。

共感・応援の気持ちを伝えよう!

  • 回答数3
  • 閲覧数1516
  • ありがとう数4

質問者が選んだベストアンサー

  • ベストアンサー
  • 回答No.3
  • keithin
  • ベストアンサー率66% (5278/7939)

「条件の追加」についても想定はしましたが,ご質問に掲示された >=IF(H5="","",IF(COUNTIF(H5,"*42*"),1,IF(COUNTIF(H5,"*LA*"),2,IF(COUNTIF(H5,"*67*"),3・・・ この式は「=IF(IF(IF(…」の重ね合わせで書かれていたので,あと幾つも条件を追加しないうちにカッコの重ね合わせ限度に達します。結果してそういった使い方では上手くやる以前に数式がそもそも書けないので,そういうお話では無いだろうと判断していました。 方法1) 今の式を =IF(H5="","", IF(COUNTIF(H5,"*42*"),1) + IF(COUNTIF(H5,"*LA*"),2) + IF(COUNTIF(H5,"*67*"),3) ) のように直します。 2個目以降のIFの組み方が「いままでとは違う」ので,よく注意して見逃さないように気を付けてください。 置換を使い,たとえば IF(COUNTIF(H5,"*67*"),3) を IF(COUNTIF(H5,"*67*"),3)+IF(COUNTIF(H5,"*43*"),4) に全て置換するといった方法で,幾らでも数式を追記していくことが出来ます。 方法2) 非常にテクニカルですが,名前の定義の中に数式を直接記述して計算させる方法も利用できます。 あるシートで「H2セルを選択する」(必須) 挿入メニューの名前の定義で次の通り登録する 名前 H2の式 参照範囲 =IF(!H5=TEXT(NOW(),""),"",IF(COUNTIF(!H5,"*42*"),1,IF(COUNTIF(!H5,"*LA*"),2,IF(COUNTIF(!H5,"*67*"),3)))) 「H5」じゃなく「!H5」なので,間違えないように作成します 各シートのH2セルに =H2の式 と記入する。 #説明不足のため,他のセルに式を転用したいその「他のセル」が,他のシートのH2セルなのか同じシートの別番地セル(たとえばH12に数式を入れてH15を計算したい)というお話なのか,不明です。 上述のサンプルは最大限の自由度で,任意のセルに入れて3つ下のセルを自動的に計算するように作成してあります。 が,その引き替えに数式を修正するときは,またH2セルを選んで名前の定義で数式を追加するなどのコツが必要になります。 シート1,2,3を移るだけでH2番地であることは変わらないなら,もっと簡単に扱える風にも出来ました。 方法3) 数式の中に「*42*」やらを直接記入する方法は放棄して,対応表を別に用意,そしてもうちょっと難易度の高い数式で計算させます。 #ただし。 エクセルマニアなら喜んで難しい数式を考えてくれますし,それを「コピーするだけ」なら多分誰にも出来ますが,ぶっちゃけ言えば(IF(IF…の式でいっぱいいっぱいの)素人さんが読み解くにはちょっと難しい結果になります。ワタシ的にはこのアプローチはあまりお奨めでは無いと思います。 ワイルドカードの逆引きになるので,通常のVLOOKUP等では出来ません。

共感・感謝の気持ちを伝えよう!

質問者からの補足

多数のご指摘・案ありがとうございました。 最初にご指摘いただいたIFの重ね限界は2007だからこそ使えているのは承知で、以前のバージョンでは既に破綻している 式であることを自覚しています。そこで、ステップアップも兼ねて質問させていただきました。 方法1ではIFを重ねず加算として連ねていくことで、重ね限界を超えて機能することに目から鱗でした。 これは、条件に一致した物だけ指定した数字が現れそれ以外が0になることで加算結果が目的の数字になるという解釈でよろしいのでしょうか? 方法2は試してみましたが、うまく機能させることができませんでした。 手順としては、先の回答に補足したH5の部分を"自分から3つ下"という指定方法に書き換えH2に記載した状態では正常に機能している式を シート2のA1に名前の定義にて"機器選定"という名前にして参照範囲に貼り付け、シート1のH2から"=機器選定"を指定しました。 しかし、数式は機能せず参照ができていないのか?と思いシート2のA1に直接数字を入れてみたところ参照できていました。 説明不足部分については、シート1に40行毎のページとして連続で作成されています。代表として1ページ目の H2がH5を参照で説明していましたが、H42がH45を参照、H82がH85を参照・・・と変わっていきます。 機器が増えてそれ以降で分岐数が増えて行く分には、既に作成されている部分に影響がないので 気にしなければいいと言われればそれまでですが、後から見てもわかりやすくしたいため質問させていただきました。 方法3については是非拝見したいと思います。 といっても、コピペするつもりは一切ありません。性格上、公式は暗記せず公式の意味・変数が何故そこにあるのかを理解したいので 見て考えられる限り考え、理解できたときは利用させていただきたいと思います。理解できなかったときは自分はまだその域ではないので わかる様になるまで封印いたします。 また、やはりVLOOKUP等が利用できないのはワイルドカードの逆引きになるのが原因なんですね(^-^; 関係ない話ですが、FORTRAN・CASLでプログラミングをかじった事があるためか、他人のプログラムをそのまま転用するのは 後から自分が苦しむことを経験しているからかもしれません・・・

関連するQ&A

  • エクセル関数について(IF関数)

    エクセルの関数について教えて下さい。 セルに1と入力した場合Aと出てきて、2と入力した場合B、3と入力した場合Cとなるような計算式を入れたいのですが、どのような式を入れればよろしいのでしょうか? 別のシートから呼び出す(?)というような方法でもいいのですが、どんな式を入れればよいのか、全く分かりません。 自分で調べてみた結果、IF関数を用いるということは解りました。 『IF(A1="1","A",IF(A1="2","B",IF(A1="3","C")))』 上記のような式を入れてみて、実際に実践してみたところ、結果としては入力した数字が表示されるだけで、計算式は消えてしまいました。 どうしたらいいのか解りません。 素人故に恥ずかしい質問ですが、お知恵を貸して下さい。 よろしくお願いします。

  • エクセル関数のセル参照方法

    エクセル関数のセル参照で、あるセルに入力した数値を、参照するセルの行番号として使いたいのですがどうしたらよいのでしょうか? VBAではそのような方法があったと思うのですが、関数で処理する方法はあるのでしょうか? たとえば、 =SUM(C5:C*) のような関数で「*」を他のセルに入力して参照させることは可能でしょうか? よろしくお願いいたします。

  • EXCELで特定のセルを参照させる関数について

    EXCELで、自分の設定した行列の特定のセルを参照させる関数や方法ってありますか?例えば、行を月火水木・・・、列をいろはにほへと・・・として、式の中に「火」と「へ」を入力すれば火行へ列を参照してくれるようなイメージです。

その他の回答 (2)

  • 回答No.2
  • mu2011
  • ベストアンサー率38% (1910/4994)

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

共感・感謝の気持ちを伝えよう!

質問者からの補足

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

  • 回答No.1
  • keithin
  • ベストアンサー率66% (5278/7939)

Ctrl+Hで置換を出し,ご利用のエクセルのバージョンに寄りますがオプション>を開いて検索場所をブック全体にして ~*42~* を *44* に全て置換してしまうのが,多分一番手っ取り早い方法です。 さもなくば Sheet0のA1,A2,A3にそれぞれ42,LA,67を記入しておき, =IF(H5="","",IF(COUNTIF(H5,"*"&Sheet0!A1&"*"),1,… のように全部の式が一箇所を参照する事で,そこを直せば全部に効いてくるように仕込みます。

共感・感謝の気持ちを伝えよう!

質問者からの補足

説明不足で大変申し訳ありませんでした。 既に入力されている部分を修正することより、基本的には項目が増えていくことによる修正が多くあります。 具体的には、現在10までの分類番号分岐がありますが「*42*」で#42Rを検出して「1」としていた物が #42Gという物が増え、かつ内容が異なるため「*42*」を「*42R」に修正して、末尾に IF(COUNTIF(H5,"*42G"),11,・・・ を追加修正するといった具合です。 基本的にはそれ以前の分岐に影響は出ないため、そのままにしておいても問題はないのですが この文書を他の人が引き継いだときに、セルによって統一されていないのは内容の理解に時間が かかったり、見たときに美しくないと思ったため一箇所で管理できたら・・・と思った次第です。

関連するQ&A

  • エクセルcountif である数字を含むセルを数える

    エクセルの基本的な質問で失礼します。 現在、以下のようなエクセルを作っています。 ちょっと分かりにくくて恐縮ですが… 元データであるシート「2」のA列にカンマ区切りで入力されている複数の数字を、 シート「1」で1つの数字ごとに横列に並べて、該当しているものには○を出すとしたいのです。 ただ、シート「1」のA1で 『=IF(COUNTIF('2'!A1,1)=1,"○","")』とすると、 セルが「1」のみの時しか○が表示されず、「1を含む場合」は○が出ません。 『=IF(COUNTIF('2'!A1,"*1*")=1,"○","")』ともしたのですが、 そうすると「1」のみの時にも○が表示されなくなってしまいました。 この場合、どういう式にするのが適切でしょうか? 恐れ入りますが、ご教示のほどよろしくお願い申し上げます。 ------------------------------------- シート「1」 ------------------------------------- A1「シート2のA1で1を含んでいる場合には○」  B1「シート2のA1で2を含んでいる場合には○」   C1「シート2のA1で3を含んでいる場合には○」 以下同様… ------------------------------------- シート「2」 ------------------------------------- A1「1,2,3,4」 A2「3,5」 A3「2」

  • エクセルの関数について教えてください

    エクセルでvlookupを使い得た結果がAだとします。 次にif関数でvlookup関数の結果のセルがAならばと言う式を作りました。 が、if関数が認識してくれません。 同じif関数の式でキーボードからAを入力したら結果が出ます。 vlookup関数のセルにはAでなく式しか入ってないからでしょうか? vlookup関数の結果をif関数で認識する方法をお願いします。

  • エクセルで3D集計の計算式を入力した複数のセルに

    エクセルで3D集計の計算式を入力した複数のセルに マクロ等で一度に絶対参照をかけたいのですが、 なにか方法はあるでしょうか?? =SUM(シート1!D9,シート2!D9,シート3!D9,シート4!D9) といったかたちの式が各々入っている複数のセルを一気に =SUM(シート1!$D$9,シート2!$D$9,シート3!$D$9,シート4!$D$9) といったかたちにしたいのです。 ちなみに、ベクターからダウンロードした、 『絶対参照に変換』というフリーソフトを使ってみたのですが、 上手くいきませんでした…。 よろしく御回答お願い致します。

  • Excel関数を教えてください。

    Excel関数を教えてください。 =IF(AA6="123市",VLOOKUP(AA6,参照シート!$Z$2:$AA$8,2,FALSE),VLOOKUP(AA6,参照シート!$T$2:$U$36,2,FALSE)) ↑このような関数を立てました。 市町村番号(自社のもの)を検索したいと思っています。 『もし、123市なら参照シートの表(2)Z2-AA8の番号を、それ以外の市町村なら参照シートの表(1)T2-U36の番号を返せ』と。 “それ以外の市町村”の表示はうまくいきました。 問題は、123市は大きな市で、その中でまた001~006まで支部として分かれています。 住所の「123市◎◎4-5-6」とあるように、◎◎で支部分けされています。 住所はAB6に入力されています。 これを支部番号まで表示できるような関数が作れませんか?

  • Excel関数について

    Excel関数を教えてください。 =IF(AA6="123市",VLOOKUP(AA6,参照シート!$Z$2:$AA$8,2,FALSE),VLOOKUP(AA6,参照シート!$T$2:$U$36,2,FALSE)) ↑このような関数を立てました。 市町村番号(自社のもの)を検索したいと思っています。 『もし、123市なら参照シートの表(2)Z2-AA8の番号を、それ以外の市町村なら参照シートの表(1)T2-U36の番号を返せ』と。 “それ以外の市町村”の表示はうまくいきました。 問題は、123市は大きな市で、その中でまた001~006まで支部として分かれています。 住所の「123市◎◎4-5-6」とあるように、◎◎で支部分けされています。 住所はAB6に入力されています。 これを支部番号まで表示できるような関数が作れませんか?

  • 【エクセル関数】参照するセル

    エクセル関数で教えていただきたいです。 セル参照で、参照するセルを、ある法則道理に参照していきたいのです。 例えば、シートA,シートB、とあったときに、 シートBのC3に → シートAのC3を参照、 シートBのC6に → シートAのC4を参照、 シートBのC9に → シートAのC5を参照、 シートBのC12に → シートAのC6を参照・・・・ ・・・・・・・・・・・ という風に、参照元の列は1行づつ増やしていきたいのですが、 それをシートBでは、3行ごとに表示したいのです。 自分でいろいろ試してみましたが、うまくできません。 こういったことは、関数でできるのでしょうか? もし、あれば、教えていただけると助かります。 よろしくお願いします。

  • 特定の関数の設定されたセルを見つけるには

    こんにちわ!よろしくお願いいたします。 シート内で、特定の関数が使われているセルをすべて見つける方法 ってありますでしょうか。 シート内は表ですが、色々は関数が使われています。その中で 例えば「COUNTIF」が使われているセルはどことどこ?といった具合です。 いかがでしょうか。

  • エクセルの関数で (時間の場合?)

    エクセルの関数で セルG8が、  6時間までならば「0.25」  6~8時間までならば「0.75」  8時間以上は「1.25」 となるよう、下記の式をIFで作ったのですが =IF(G8>=8,"1.25",IF(G8>=6,"0.75","0.25")) G8のセル値が、表示形式の分類が時刻だと うまく行きません・・・どうしたら良いのか どなたか御教授お願い致します。

  • エクセル 2個セル間の数字があれば、参照する関数を探しています。

    エクセル 2個セル間の数字があれば、参照する関数を探しています。 ■参照部分(参照シート)  A  B  C  1  24  不合格  25 50  再テスト  51 100  合格 ■リスト A  B  C 高橋 79  合格 佐藤 12  不合格  山田 40  再テスト 木下 58  合格 2シートに分かれています。 リストシートのBセルに数字を入れると、自動的にCに合格・不合格が 入るような関数にするにはどうしたらよいのでしょうか。 IFとVLOOKUPの組み合わせでしょうか。 よろしくお願いします。

  • エクセルで複数の条件のセル数をそれぞれ合計したい

    エクセルで複数条件のセルにそれぞれ分類を分け(判定)、分類ごとにセル数の合計を出したいのですが、この場合のセル合計を簡単に計算する条件式はないでしょうか?(分類項目はIF、AND関数を使って結果表示しています) 例)C列に分類AとBが0の時"*"、Aが1・Bが0の時"2"、ABとも1の時"1"、ABとも空欄の時空欄" "と判定し、"*"・"2"・"1"それぞれのセル数を合計したいのですが・・・。   列 A B C      行 1  0 0 *    2  1 0 2    3  1 1 1    4     ・        ・ どなたかご教示よろしくお願いします。