エクセルで、以下を実現する方法は?


「1番シート」に以下のようにデータが入っているとします。
111 222 333 x
113 234 213 y
321 421 314 z

「2番シート」はA1,A2,A3にそれぞれ値を入れると、B1にいろいろと計算した結果の数字が入るようになっています。

ここで、1番シートのまえの3つの数字を、2番シートのA1,A2,A3にいれてB1に出てきた結果を、x,y,zのそれぞれに入るようにしたいと思います。

どうすれば実現できるのでしょうか?

回答の条件
  • 1人2回まで
  • 13歳以上
  • 登録:2010/08/20 09:32:18
  • 終了:2010/08/27 09:35:03

回答(6件)

id:stnet No.1

stnet回答回数804ベストアンサー獲得回数342010/08/20 09:59:21

ポイント13pt

=Sheet1!A1

のような感じで入力すれば、他シートの値を参照できますよ

http://www.excel-jiten.net/formula/ref_other_sheets.html

id:dingding

意味がちがいます。

2番シートに

111 222 333をいれたときのB1の値をxに、

113 234 213をいれたときのB1の値をyに、

321 421 314をいれたときのB1の値をzに、

入れたいんです。

2010/08/20 11:04:54
id:gara_cp No.2

がら回答回数458ベストアンサー獲得回数182010/08/20 10:06:11

ポイント25pt

1番シートの前の3つとはA1,A2,A3のことですよね?

2番シート(Sheet2)のA1に「=Sheet1!A1」(「」はつけないでね)

1番シート(Sheet1)のC1に「=Sheet2!B1」

でOKでは?

id:Penyan No.3

Penyan回答回数44ベストアンサー獲得回数72010/08/20 11:26:47

ポイント24pt

A1,A2,A3の順番でよければ、

xの数式を

=IF(AND(A1=Sheet2!$A$1,B1=Sheet2!$A$2,C1=Sheet2!$A$3),Sheet2!$B$1,"")

にしてy,zにコピーしてください。

id:dingding

なんかうまくいかないですね。

2010/08/20 11:55:03
id:ycyc No.4

ycyc回答回数37ベストアンサー獲得回数62010/08/21 01:13:09

ポイント24pt

計算式が1箇所にしかないので、複数の値を順番に入れて計算結果を適宜設定するようにすればいいと思うので、dingdingさんのマクロが正しく動かないとすれば、Functionを呼び出しているところがまずいのではないでしょうか?

Private Sub CommandButton1_Click()

Dim row As Integer

For row = 1 To 1000

If (sheet1.Cells(row, 1) = "") Then

' 適当なところまで繰り返す

Exit For

End If

Worksheets("Sheet1").Cells(row, 4).Value = _

XXX(Worksheets("Sheet1").Cells(row, 1).Value, _

Worksheets("Sheet1").Cells(row, 2).Value, _

Worksheets("Sheet1").Cells(row, 3).Value)

Next

End Sub

Function XXX(aaa, bbb, ccc)

Worksheets("Sheet2").Range("A1").Value = aaa

Worksheets("Sheet2").Range("A2").Value = bbb

Worksheets("Sheet2").Range("A3").Value = ccc

XXX = Worksheets("Sheet2").Range("B1").Value

End Function

id:dingding

ありがとうございます。試してみたいと思います

2010/08/21 08:39:24
id:ask001 No.5

ask001回答回数49ベストアンサー獲得回数02010/08/21 10:56:46

(はてなにより削除しました)
id:noriklb No.6

noriklb回答回数79ベストアンサー獲得回数92010/08/21 21:48:34

ポイント24pt

この問題について、かなり考えてしまいました。

if関数を使う、and関数を使う・・・・

やっぱりマクロか・・・・

でも、発想の転換をしました。

sheet1のXのセルD1に、=の付いた式を入れます。

その式は、sheet2の式をコピーします。

たとえば、

sheet2 の B1に =A1+A2-A3 という式が入っていたとします。



それをコピーし、sheet1のD1に貼り付けます。

その式を =A1+B1-C1 に書き直します。



これが、Xの値となります。

オートフィルで、Y、Z の値を出します。






 

id:dingding

ありがとうございます。それをしようと思って、参照をすべてせずに1行の式にしたんですが、文字数が5000文字位あることに途中で気づきました。それで断念してしまいました。

今回のような使い方はよくできればなと思って放置していたやり方なので、できるようになれればなと思って投稿していました。

なんとなく簡単にできるだろうと思ってたら、思いのほか難しいことがわかりそれはそれでとても有益でした。ありがとうございます。

2010/08/22 09:34:14
  • id:gara_cp
    1番シートのA1に入っているのは
    ①111ですか?
    ②それとも9桁の
    111222333
    ③それとも3桁数字(空白)3桁数字(空白)3桁数字の
    111 222 333ですか?

    ②もしくは③で111を
    2番シートのA1に求めるなら
    =LEFT(Sheet1!A1,3)
    で行けるのではないかと思います
  • id:dingding
    やりたいのは、
    A1,A2,A3にある値を入れた時のB1の値を関数で取得することです。

    関数名がわかりませんが、イメージとしては
    (Sheet2!A1=Sheet1!A4,Sheet2!A2=Sheet1!B1,Sheet2!A3=Sheet1!C1)の条件の時のSheet2!B1の値を取得する関数を知りたいのです
  • id:ootatmt
    マクロを使わないと無理じゃないかな。
  • id:nepia11
    2番シートの「いろいろ計算した」この計算式を、
    それぞれx,y,zのセルに記述すればいいんじゃないですか。
  • id:ootatmt
    > 2番シートの「いろいろ計算した」この計算式を、
    > それぞれx,y,zのセルに記述すればいいんじゃないですか。

    単純に =A1+A2+A3 のような式だったら、それでいいと思うんですけど、
    きっと計算式が複雑で複数のセルに渡って書かれているんでしょう。
    違うかな?

    結構、こんな要求はありますよね。

    マクロを使ったら簡単なんですけど、関数だけでは無理だと思います。
  • id:dingding
    ありがとうございます。

    >きっと計算式が複雑で複数のセルに渡って書かれているんでしょう。
    まさしくそうです。
    たくさんのセルにわかって長々と計算しています。
    マクロを使えばいけるんですね。調べてみます。
  • id:ootatmt
    マクロを使うなら、

    1番シートから数値を取ってくる
    2番シートの A1, A2, A3 に代入
    2番シートの結果 B1 の値を取ってきて
    1番シートの x に入力

    これを必要回数分繰り返すだけです。
  • id:dingding
    具体的な書き方がいまいちわかりませんでした。
    参考になるサイトがあれば教えてください。

    Function XXX(aaa, bbb)
    Worksheets("Sheet2").Range("A1").Value = aaa
    Worksheets("Sheet2").Range("A2").Value = bbb
    XXX= Worksheets("Sheet2").Range("B1").Value
    End Function

    これでいいような気がしますが、期待したようになりません。
  • id:ootatmt
    Functionプロシージャの中では、
    Worksheets("Sheet2").Range("A1").Value = aaa
    のように、セルの値を操作することができません。

    Subプロシージャを使ってください。
  • id:SALINGER
    ちょっと面白いものを作ってみたので試してみてください。
    >>
    Function XXX(r1, r2, r3) As String
    Dim str As String
    str = Worksheets("Sheet2").Range("B1").Formula
    str = Replace(str, "A1", r1)
    str = Replace(str, "B1", r2)
    str = Replace(str, "C1", r3)
    XXX = Evaluate(Mid(str, 2))
    End Function
    <<
    これは数式を取り出して、値を置換して計算した結果を返します。
    これは実は不完全でして、A11とか数式にあるとエラーになります。
    また、単純な物は試しましたが、複雑な関数を処理できるかは知りません。
  • id:ootatmt
    SALINGERさん

    その方法もアリかと思いましたけど、数式が B1 だけに入っているわけではないので使えなさそうですよ。
  • id:SALINGER
    そうだね。もともと配列一つあっても使えないので。
    はなからユーザー定義関数ではなくて、実行形式のマクロにすれば簡単ですが。
  • id:SALINGER
    >B1 だけに入っているわけではないので使えなさそうですよ。
    これはセル文字列を取得して再帰を使えばなんとかなるかもしれないな・・・
    でもユーザー定義関数でそこまで普通やらないか。
  • id:hathi
    質問があります。下記の言われている意味がわかりません。
    >>>>>>>>> 2番シートの「いろいろ計算した」この計算式を、
    >>>>>>>>> それぞれx,y,zのセルに記述すればいいんじゃないですか。
    >>>>>>単純に =A1+A2+A3 のような式だったら、それでいいと思うんですけど、
    >>>>>>きっと計算式が複雑で複数のセルに渡って書かれているんでしょう。
    >>>>>>違うかな?
    >>>>>>マクロを使ったら簡単なんですけど、関数だけでは無理だと思います。
    >>>まさしくそうです。
    >>>たくさんのセルにわかって長々と計算しています。

    エクセルシート上で、複数のセルにまたがって計算結果を参照する複雑なことをしても、結果がおかしくなったことは私の経験ではありません。
    ユーザ定義関数の計算結果を参照するようなことをさせる場合には、制限があるそうですが、普段そうしたユーザ定期関数の結果を入れ子入れ子で何段ものセルで計算させたことがないので、経験上おかしなことになったこともありません。
      http://support.microsoft.com/kb/170787/ja
     
    『(Sheet2!A1=Sheet1!A4,Sheet2!A2=Sheet1!B1,Sheet2!A3=Sheet1!C1)の条件の時の Sheet2!B1の値を取得する』のであれば、Sheet1!A4、Sheet1!B1、Sheet1!C1、Sheet2!A1、Sheet2!A2、Sheet2!A3、Sheet2!B1にくるまでの計算にユーザ定義関数が使われていても、(循環参照)になっていなければ、ifでSheet2!B1のセルに表示されている値を取得することはできるように思うのですが、違うのでしょうか?
     
    マクロを使わなければならないのは、例えば、どのような計算式が各セルに使われているような場合なのでしょうか。
      
     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     
    (今回の質問者dingding さんの意図(何をしたいのか)が、私にはよくわからないのです)
    『(Sheet2!A1=Sheet1!A4,Sheet2!A2=Sheet1!B1,Sheet2!A3=Sheet1!C1)の条件の時の Sheet2!B1の値を取得する』
     1番シートのまえの3つの数字を、2番シートのA1,A2,A3にいれてB1に出てきた結果を、x,y,zのそれぞれに入る
     Sheet2!B2=f(Sheet2!A1,Sheet2!A2,Sheet2!A3) で、x,y,zにSheet2!B2の値を入れるというのは何でしょうか?
     Sheet1には最初 次のように画面表示されていて、
      A B C D E
    1 "111" " 222" "333" "x"
    2 "113" " 234" "213" "y"
    3 "321" " 421" "314" "z"
    4
     
     Sheet2のA1,A2,A3に入力すると、Sheet2のB2に結果が表示されてくる
      (そこまでは既に完成済み)
     で………
     Sheet2のB2の結果(value)を、Sheet1のD1、D2、D3のx、y、zの文字と置換するのでしょうか?
      同じ値をD1、D2、D3に入れるなら、D2、D3は"=D1"の値参照で良いようですし………
     
     
    『(Sheet2!A1=Sheet1!A4,Sheet2!A2=Sheet1!B1,Sheet2!A3=Sheet1!C1)の条件の時
     このSheet1!A4はブランクだとすると、 (Sheet2!A1をブランクにした時以外)
      3つの条件がand条件だと、条件式はほとんどの場合意味がない? 
       
      また Sheet1!A1、Sheet1!A2:Sheet1!C3は、回答の中に出てくることはない?
     
    どのようなことをされようとしているのか、できたら、解説をお願いします。
  • id:dingding
    みなさま、ありがとうございます!

    サンプルではシンプルなのですが、実際のものでは、
    A1に数字をいれたら、計算途中としてA10、A11,A12に計算結果がでて、その結果をつかって、B10,B11,B12に結果がでて…というような感じのを10回くらい繰り返しています。

    すべての参照を手動でつなげて1つの計算式にできないか試してみたところ、
    5000文字くらいの計算式になってしまいました。

    一つ一つの計算式は四則演算とIF文くらいしかないので、計算式にしようとすればできるのではないかと思えてきましたが、エクセルで気軽に計算していてその結果を簡単に計算式にできないものなのかなと考えていました。
  • id:ootatmt
    > エクセルシート上で、複数のセルにまたがって計算結果を参照する複雑なことをしても、結果がおかしくなったことは私の経験ではありません。

    私もおかしくなったことはありませんし、質問者さんもおかしくなっていないと思います。

    > どのようなことをされようとしているのか、できたら、解説をお願いします。

    質問をよく読めばわかりますよ。
  • id:hathi
    失礼しました。もう一度読んで見ました。
     
    ① Sheet2(A1,A2,A3)に入力した値が、
       Sheet1(A1,B1,C1)に対応して3つとも一致しているとき ⇒ D1に
       Sheet1(A2,B2,C2)に対応して3つとも一致しているとき ⇒ D2に
       Sheet1(A3,B3,C3)に対応して3つとも一致しているとき ⇒ D3に
      Sheet2のB1の値を入れる。
     
    ② Sheet2のB1の値は複雑な条件式や計算によって表示される結果であるが、
      その結果はおかしくなることはない。(いつも正しい結果を出している)
     
    こういう理解で良いでしょうか?

    この理解で良い場合に、3 回答者:Penyanさんの 2010-08-20 11:26:47
      xの数式を (Sheet1 のD1のセルに)
       =IF(AND(A1=Sheet2!$A$1,B1=Sheet2!$A$2,C1=Sheet2!$A$3),Sheet2!$B$1,"")
       にしてy,zにコピーしてください。(Sheet1 のD2,D3のセルに)
     で、うまくできそうに私には思えるのですが、
      『なんかうまくいかないですね。』
      『きっと(B1に至る)計算式が複雑で複数のセルに渡って書かれているんでしょう。
       結構、こんな要求はありますよね。
       マクロを使ったら簡単なんですけど、関数だけでは無理だと思います。 』

    ここが現在もわかりません。
     
     B1に至る計算式が、どのようにセルを参照し続ける複雑なものでも、
     最終的にB1に正しい答えが出ているのであれば、
     (If文で B1を参照する方法)がうまくいかないことがあると言うのがわからないのです。
     どのような条件があると、うまく動かなくことがあるのかを知りたいのです。
        よろしくお願いいたします。
     
     試しに、
      D1に =IF(AND(A1=Sheet2!$A$1,B1=Sheet2!$A$2,C1=Sheet2!$A$3),Sheet2!$B$1,"")として
      D2,D3にコピーし、
      B1に[20段のセル参照で各セル6セルを(Ifと四則演算、INT、RAND、POWERの関数で)
      やる式]を作りました。
      Sheet2!(A1,A2,A3)に手入力して、Sheet1のD1,D2,D3に正しく反映するかを見ましたが、
      特に問題は起きませんでした。

  • id:windofjuly
    うぃんど 2010/08/21 17:40:36
    横から失礼します
     
    【1】趣旨
    「2番シート」を1つの関数とみなして使い回しを図ろうというのが趣旨だと思われますので、手順は下記でしょう
    (0)「1番シート」のA-D列に下記のように入っているとします
    A B C D
    111 222 333 x
    113 234 213 y
    321 421 314 z
    (1)1番シート(A1,B1,C1)を2番シート(A1,A2,A3)に入れて2番シート(B1)に出てきた結果を1番シート(D1)に書く
    (2)1番シート(A2,B2,C2)を2番シート(A1,A2,A3)に入れて2番シート(B1)に出てきた結果を1番シート(D2)に書く
    (3)1番シート(A3,B3,C3)を2番シート(A1,A2,A3)に入れて2番シート(B1)に出てきた結果を1番シート(D3)に書く
    以下、多分続くと思われる
     
    【2】シート上だけではできない
    VBAで上記(1)、(2)、(3)を順次繰り返す処理が必要
     
    【3】計算式をVBA側で書くと良いと思われる
    >すべての参照を手動でつなげて1つの計算式にできないか試してみたところ、
    >5000文字くらいの計算式になってしまいました。
    2番シートの仕様が詳細に判れば簡略化する事も可能な場合がありますが、
    はてな程度でのやりとりならば【2】に記述しましたように繰り返し処理させる方法を教えてもらうのが早道ですね
    VBAのコードも数行ですみますから理解も早いはずです
    回答4の方式(functionに分けなくてもいいと思うのだけど、それは本題ではないので放置)でおそらくできるはずなので、
    これ以上コメント欄を延ばすのは不毛でしょう
     
    【その他】経験豊かな回答者は皆、コメント欄
    間接回答拒否の影響によって回答できない模様
    回答1、2、3を見て「ヒントだけでも」との思いでコメントしているのでしょう
    間接回答拒否を否定はしませんが、既存回答者の多くは多かれ少なかれ敵(主に不適切回答者)を作っていますので、
    他のユーザーの回答拒否に頼るのではなく過去の履歴を参照してから回答を開くということを質問者が行って、自ら防衛してもらいたいですね
  • id:hathi
    windofjuly さん 
     ありがとうございました。
     ようやく 元のdingding の意図を理解することができました。
     『「1番目シート」の同行のA,B,C列の数字を引数にして、
      関数(処理)を「2番シート」に記載したもので使って、
      「1番目シート」の同行のD列に答えを出す』
     
     なるほど、私はすっかり誤解していました。
     済みませんでした。
     コメント、ご回答をありがとうございました。

     =~~~~~~~~~~~~~~~~~~=
     はてなの質問に対する既回答で、理解できないけれど理解したいことがあったとき、
     [この質問・回答へのコメント]の機能を使って質問したりするのは、
      不適切な行為になってしまうのですか。
      はてなの質問を新規に立てると、なんのことかわかりにくくなると思ったので、
      [この質問・回答へのコメント]の機能を使わせていただきました。
      (元の質問に対する回答のつもりは全然ないので、回答機能は使いません)
      [この質問・回答へのコメント]の機能を使ってこのように質問することが
       不適切な行為になるのでしたら、申し訳ありませんでした。
  • id:SALINGER
    VBAを使わずワークシートだけを使う方法を紹介しておきます。
    http://d.hatena.ne.jp/SALINGER/20100823
  • id:dingding
    すみません、反応が遅れていました。ちょっと試す時間が取れなくて試せていませんが、試してみたいと思います。ありがとうございました。

この質問への反応(ブックマークコメント)

トラックバック

  • Twitter Trackbacks 2010-08-20 09:56:47
  • SALINGERの日記 2010-08-21 00:01:43
    http://q.hatena.ne.jp/1282264337 この質問のコメントで書いた再帰を使った方法を考えてみる。 Sub test() Debug.Print myEval(Range(&quot;A2&quot;), Range(&quot;B2&quot;), Range(&quot;C2&quot;), Worksheets(&quot;Sheet2&quot;).Range(
  • SALINGERの日記 2010-08-23 19:32:44
    前回はユーザー定義関数を作ろうとして失敗したわけですが、 VBAを使わずにワークシートだけで結果を求める方法があることに気づきました。 その方法とは、ずばり循環参照です。 Excelで
「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません