Excelでｘとyの値を求めたい

質問

下記の表を作り、C列に値を入れたら、D、Ｅ列にｘ、ｙの値が出るようにしたいと思います。

　　　　A　　　　　　　　　　　B　　　　　　　　　　C　　　　　　　　　　D　　　　　　　　　　E
1　ｘ*0.2+3.2　　　　　　　　　　　　　　　　　3.7　　　　　　　＝（C1-3.2）/0.2
2　ｘ*0.4+2.9　　　　　y*0.2+3.0　　　　　　7.5

ｘだけの場合は数式でなんとかなりました。
問題は、ｘとｙの場合です。
2行目は、A2+B2がC列の値になっています。
C2に7.5を入力したら、D2とE2にｘとｙの答えを出したいのですが、
別の表にあるｘの値「+5.0、+2.5、0、-5.0、-10」、ｙの値「+3.0、+1.5、0、-7.5、-15」から
答えを選びたいのです。（ここではD2は2.5、E2が1.5が正解になります）

よい方法がありましたらアドバイスをお願いします。

この投稿のマルチメディアは削除されているためご覧いただけません。

回答No.8

MackyNo1 · ベストアンサー

>X（G1:G5）（4、2,0、0、-5、-10）
Y（J1:N1）（5、2.5、0、-7.5、-15）
C1は9.4
計算式の値が違うものがいくつかありまして、全部同じならいいのですが（；；）

計算式の変更は、たとえばA1セルに「D1*0.4+2.9」のように入力しておき、「置換」の操作で元の数式部分をA1セルの文字列に変換するとよいと思います。

また提示した数式は、基本的に計算式部分を変更すれば、該当する値を表示することができます。
ただし、すでに述べているように、XとYの値の中に正解が含まれないときや、２つ以上の正解が含まれる場合はエラー値となります。

すなわち、例示のデータではX、Yの値が０、０の場合と５、－５の両方のケースで成立しますので、エラーが表示されます。

ちなみに、条件に合致する組み合わせが２つある場合でも、さらに複雑な数式にすれば１つのセルで表示させることができると思いますが、極めて複雑な数式になるため実用的ではありません。

お礼

ありがとうございます！
大変勉強になりました。
分かりやすいご説明ありがとうございました！

回答No.7

MackyNo1

５×５の組み合わせ全部に対応する式は以下のようになります。

ただし、Xの値がG列のG1：G5セルに、Yの値が１行目のJ1セルからN1セルに入力されている場合、

A1：　=D1*0.4+2.9（特に入力する必要はない）
B1：　=E1*0.2+3（特に入力する必要はない）

C1：　数値を入力
D1；　=INDEX(G1:G5,SUMPRODUCT(((G1:G5*0.4+2.9+J1:N1*0.2+3)=C1)*ROW(G1:G5)))
E1：　=INDEX(J1:N1,SUMPRODUCT(((G1:G5*0.4+2.9+J1:N1*0.2+3)=C1)*COLUMN(A:E)))

該当データがないとE１に#VALUEエラーが返ります。

補足

ありがとうございます！
こちらの方法でも試してみています。
この場合、A1とB1の数字が変わっても可能でしょうか。
たとえば、
A1：＝Ｘ*0.4+2.9
B1：＝Ｙ*0.4+6.5

X（G1:G5）（4、2,0、0、-5、-10）
Y（J1:N1）（5、2.5、0、-7.5、-15）
C1は9.4
計算式の値が違うものがいくつかありまして、全部同じならいいのですが（；；）

回答No.6

KURUMITO

解答Ｎｏ2です。ｘとｙが0のときにエラーが表示されるとのことですが、Ｄ2セルへの入力式を次のようにすればよいでしょう。
=IF(ISERROR(MATCH(10^10,I1:I5)),0,INDIRECT("G"&MATCH(10^10,I1:I5)))

補足

ありがとうございます！
うまくいきました☆

ちなみに、Xの値が5個、Yの値が7とばらつきがある場合や式の数値が違う場合は可能でしょうか？

ちがう数値でやってみた場合エラーが返されてしまいました。
A1：Ｘ*0.4+2.8
B1：Ｙ*0.4+6.5
G1：G5（4、2、0、-2.5、-5）
H1：H7（10、7.5、5、2.5、0、-10、-20）

回答No.5

MackyNo1

>ｘ値とｙ値が同じでない行にある場合には別の値となって正解が得られませんね。

質問者に確認ですが、別表のXとYの値は、それぞれ対応した組み合わせではないのでしょうか？

私の回答は、それぞれの組み合わせの中から正解を選択するという条件で数式を作成しています。

補足

すみません、対応した組み合わせではありません。
Xの値のどれかとYの値のどれかを組み合わせると正解が出るようにしたいです。

回答No.4

KURUMITO

横から失礼します。配列数式を使っての計算式が提案されていますが、たまたま2.5と1.5が同じ行にあることから正解が得られていますが、ｘ値とｙ値が同じでない行にある場合には別の値となって正解が得られませんね。

回答No.3

MackyNo1

>それで、数式を3.3にかえると7.5を入力したら2.5と1.5が出ました！

>しかし他の式にも応用しようとすると出なかったりします（；；）

数式を変更すると配列数式が解除されますが、修正後Ctrl+Shift+Enterの操作を行っているでしょうか？

また、「出なかったりする」結果は、別の結果が表示されるということでしょうか？

その場合は、前提でも触れましたが（エラー処理せず数式を簡略化している）、一覧に該当データが含まれていないということはありませんか？

回答No.2

KURUMITO

別の表がＸの値としてＧ1からＧ5セルに入力されており、Ｙの値がＨ1セルからＨ5セルに入力されているとします。
作業列としてＩ1セルには次の式を入力してＩ5セルまでオートフィルドラッグします。
=IF(COUNTIF(H$1:H$5,(C$2-3-2.9-0.4*G1)/0.2)=0,"",(C$2-3-2.9-0.4*G1)/0.2)
その後にＤ2セルには次の式を入力します。
=INDIRECT("G"&MATCH(10^10,I1:I5))
Ｅ2セルには次の式を入力します。
=MAX(I1:I5)
これでＤ2セルにはＸの値が、Ｅ2セルにはＹの値が表示されるでしょう。
正解はＸが2.5でＹが3.0ではないでしょうか。

お礼

なんとか形になりました！
ただ、正解がX、Y共に0の場合#N/A値が返されてしまいました。
エラーが表示されないようにするしかないですかね。。
もう少し粘ってみます。
分かりやすいご説明ありがとう御座いました。

補足

ありがとうございます！
参考になりそうです！
ちなみに別の式（A、B列）で正解が0の場合も表示されますでしょうか？

回答No.1

MackyNo1

答えを自動的に求めたいならソルバーを利用することになりますが、今回のように一覧表の中から答えに合致するデータを選ぶというような場合は以下のような配列数式を使うことになります。

まず前提として別の表がG1：H5セル（ｘの値がG1:G5セル、ｙの値がH1:H5セル）にあるとして、D2セルに以下の式を入力してCtrl+Shift+Enterで確定し、右方向に１つオートフィルします。

=INDEX(G1:G5,MAX((($G$1:$G$5*0.4+2.9+$H$1:$H$5*0.2+3)=$C2)*ROW(G1:G5)))

ただし、上記の数式は必ず該当データがあるという条件の数式でエラー処理をしていません（データがない場合は、数式入力行のデータが表示されます）。

ちなみに、例示のデータでは正解は５と３の組み合わせでないでしょうか？

お礼

大変申し訳ありません、式が間違っていました。
B2の式はｙ*0.2+3.3でした。
それで、数式を3.3にかえると7.5を入力したら2.5と1.5が出ました！

しかし他の式にも応用しようとすると出なかったりします（；；）
もう少しやってみます（＞＜）

補足

ありがとうございます！
正解はすでに出ていて7.5が先にわかっています。
C列に7.5と入力したら、A2の数式とB2の数式をもとに（なので、A、B列は文字列です）
逆算してｘの値の2.5とｙの値の1.5の組み合わせが、D、E列に出るようにしたいのです。
説明が分かりにくくてもうしわけありません。。