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

Question

dingding

730

719もっと見る

110pt

インターネットウェブ制作

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

「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回まで

登録：2010/08/20 09:32:18
終了：2010/08/27 09:35:03

※ 有料アンケート・ポイント付き質問機能は2023年2月28日に終了しました。

がら 2010/08/20 11:25:18

１番シートのＡ１に入っているのは
①111ですか？
②それとも９桁の
111222333
③それとも３桁数字（空白）３桁数字（空白）３桁数字の
111 222 333ですか？

②もしくは③で111を
2番シートのＡ１に求めるなら
=LEFT(Sheet1!A1,3)
で行けるのではないかと思います
dingding 2010/08/20 12:19:42

やりたいのは、
A1,A2,A3にある値を入れた時のB1の値を関数で取得することです。

関数名がわかりませんが、イメージとしては
(Sheet2!A1=Sheet1!A4,Sheet2!A2=Sheet1!B1,Sheet2!A3=Sheet1!C1)の条件の時のSheet2!B1の値を取得する関数を知りたいのです
ootatmt 2010/08/20 12:22:34

マクロを使わないと無理じゃないかな。
nepia11 2010/08/20 14:22:10

2番シートの「いろいろ計算した」この計算式を、
それぞれx,y,zのセルに記述すればいいんじゃないですか。
ootatmt 2010/08/20 14:35:49

> 2番シートの「いろいろ計算した」この計算式を、
> それぞれx,y,zのセルに記述すればいいんじゃないですか。

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

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

マクロを使ったら簡単なんですけど、関数だけでは無理だと思います。
dingding 2010/08/20 15:24:57

ありがとうございます。

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

マクロを使うなら、

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

これを必要回数分繰り返すだけです。
dingding 2010/08/20 16:11:04

具体的な書き方がいまいちわかりませんでした。
参考になるサイトがあれば教えてください。

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

これでいいような気がしますが、期待したようになりません。
ootatmt 2010/08/20 16:17:12

Functionプロシージャの中では、
Worksheets("Sheet2").Range("A1").Value = aaa
のように、セルの値を操作することができません。

Subプロシージャを使ってください。
SALINGER 2010/08/20 17:17:50

ちょっと面白いものを作ってみたので試してみてください。
>>
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とか数式にあるとエラーになります。
また、単純な物は試しましたが、複雑な関数を処理できるかは知りません。
ootatmt 2010/08/20 18:59:13

SALINGERさん

その方法もアリかと思いましたけど、数式が B1 だけに入っているわけではないので使えなさそうですよ。
SALINGER 2010/08/20 20:01:32

そうだね。もともと配列一つあっても使えないので。
はなからユーザー定義関数ではなくて、実行形式のマクロにすれば簡単ですが。
SALINGER 2010/08/20 20:17:25

＞B1 だけに入っているわけではないので使えなさそうですよ。
これはセル文字列を取得して再帰を使えばなんとかなるかもしれないな・・・
でもユーザー定義関数でそこまで普通やらないか。
hathi 2010/08/20 22:53:45

質問があります。下記の言われている意味がわかりません。
>>>>>>>>> 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は、回答の中に出てくることはない？
　
どのようなことをされようとしているのか、できたら、解説をお願いします。
dingding 2010/08/21 08:37:20

みなさま、ありがとうございます！

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

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

一つ一つの計算式は四則演算とIF文くらいしかないので、計算式にしようとすればできるのではないかと思えてきましたが、エクセルで気軽に計算していてその結果を簡単に計算式にできないものなのかなと考えていました。
ootatmt 2010/08/21 13:30:20

> エクセルシート上で、複数のセルにまたがって計算結果を参照する複雑なことをしても、結果がおかしくなったことは私の経験ではありません。

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

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

質問をよく読めばわかりますよ。
hathi 2010/08/21 15:18:35

失礼しました。もう一度読んで見ました。
　
①　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に正しく反映するかを見ましたが、
　　特に問題は起きませんでした。
うぃんど 2010/08/21 17:40:36

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

windofjuly さん　
　ありがとうございました。
　ようやく　元のdingding の意図を理解することができました。
　『「1番目シート」の同行のA,B,C列の数字を引数にして、
　　関数（処理）を「２番シート」に記載したもので使って、
　　「1番目シート」の同行のD列に答えを出す』
　
　なるほど、私はすっかり誤解していました。
　済みませんでした。
　コメント、ご回答をありがとうございました。

　＝～～～～～～～～～～～～～～～～～～＝
　はてなの質問に対する既回答で、理解できないけれど理解したいことがあったとき、
　［この質問・回答へのコメント］の機能を使って質問したりするのは、
　　不適切な行為になってしまうのですか。
　　はてなの質問を新規に立てると、なんのことかわかりにくくなると思ったので、
　　［この質問・回答へのコメント］の機能を使わせていただきました。
　　（元の質問に対する回答のつもりは全然ないので、回答機能は使いません）
　　［この質問・回答へのコメント］の機能を使ってこのように質問することが
　　　不適切な行為になるのでしたら、申し訳ありませんでした。
SALINGER 2010/08/23 19:33:45

VBAを使わずワークシートだけを使う方法を紹介しておきます。
http://d.hatena.ne.jp/SALINGER/20100823
dingding 2010/08/24 09:36:59

すみません、反応が遅れていました。ちょっと試す時間が取れなくて試せていませんが、試してみたいと思います。ありがとうございました。

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("A2"), Range("B2"), Range("C2"), Worksheets("Sheet2").Range(
SALINGERの日記 2010-08-23 19:32:44

前回はユーザー定義関数を作ろうとして失敗したわけですが、 VBAを使わずにワークシートだけで結果を求める方法があることに気づきました。その方法とは、ずばり循環参照です。 Excelで

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

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

リクエスト送信済

回答リクエストを送信したユーザーはいません

stnet · Answer 1 · 2010-08-20T09:59:21+09:00

=Sheet1!A1

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

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

がら · Answer 2 · 2010-08-20T10:06:11+09:00

１番シートの前の３つとはＡ1，Ａ2，Ａ3のことですよね？

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

１番シート（Sheet1)のC1に「=Sheet2!B1」

でＯＫでは？

Penyan · Answer 3 · 2010-08-20T11:26:47+09:00

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

xの数式を

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

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

ycyc · Answer 4 · 2010-08-21T01:13:09+09:00

計算式が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

ask001 · Answer 5 · 2010-08-21T10:56:46+09:00

(はてなにより削除しました)

noriklb · Answer 6 · 2010-08-21T21:48:34+09:00

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

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

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

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

sheet1のＸのセルＤ1に、＝の付いた式を入れます。

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

たとえば、

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

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

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

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

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

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

回答（6件）

stnet804342010/08/20 09:59:21

がら458182010/08/20 10:06:11

Penyan4472010/08/20 11:26:47

ycyc3762010/08/21 01:13:09

ask0014902010/08/21 10:56:46

noriklb7992010/08/21 21:48:34

コメント（21件)

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

トラックバック