Excel関数、計算結果の自動出力

質問

ややこしいので説明が長くなります。

数値を入れると計算結果がでるようなExcel関数があります。
条件が多いので多くのセルに分かれています。

例えば
A1=A2+A3+B2+B3+B4+B5・・・

という関数があり、B列の数値がA2とA3の数値によって計算されます。

A2には…2～100（2の倍数）
A3には…5～500（5の倍数）
の数値が入ります。

これらのA2、A3の全組み合わせ5000パターンの計算結果を
表にまとめなくてはいけません。

（パターン1） A2：2　＆　A3：5
（パターン2） A2：2　＆　A3：10
（パターン3） A2：2　＆　A3：15

・・・

（パターン98） A2：2　＆　A3：490
（パターン99） A2：2　＆　A3：495
（パターン100） A2：2　＆　A3：500

（パターン101） A2：4　＆　A3：5
（パターン102） A2：4　＆　A3：10
（パターン103） A2：4　＆　A3：15

・・・
・・・
・・・

（パターン4999） A2：100　＆　A3：495
（パターン5000） A2：100　＆　A3：500

という具合でA2、A3の全組み合わせ5000パターンの計算を
一覧で出力することはできるのでしょうか。

現在はカット＆ペーストで計算式全てを横一行にずらーっと並ぶように整理して、
フィルハンドルで全組み合わせの数値を入力し、
5000行かけて計算した結果を別シートにコピペしているのですが、
整理しても100列を超える計算式が何種類もあるため、その作業ですら大変なんです。

良い方法があったらご教授ください。

回答No.6

marumets

No.5です。

計算式の件ですが、
A1=A2+A3+B2+B3+B4+B5・・・というのが、例えば
=A2+A3+B2+B3+・・・+B120+C2+C3+・・・+C50とかであれば、
=SUM(A2:A3,B2:B120,C2:C50)に変えれば、式の修正が楽です。
絶対参照にするには
＝SUM($A$2:$A$3,$B$2:$B$120,$C$2:$C$50)で終わりですし、

最期の修正でも
=SUM(B2:C2,$B$2:$B$120,$C$2:$C$50)で終わりです。

回答No.5

marumets

欲しいのは、A1の値（計算結果）とA2及びA3の値の一覧表でしょうか？

そうであるなら、計算結果を算出する現在のシート（Sheet1とします）をすべてSheet2にコピーします。
別のシート（Sheet3)に一覧表を作ります。

Sheet3の1行目のA列からC列に、"計算結果"、"ファクター１"、"ファクター２"とでも項目名を入れます。

Sheet2のA1セルの式を絶対参照に修正します。
（=A2+A3+B2+B3+B4+B5・・→=$A$2+$A$3+$B$2+$B$4+$B$5+・・）
修正後、A1を切り取り、Sheet3のA2セルに貼り付けます。

Sheet3に移り、A2セルを修正します。
（=Sheet1!$A$2+Sheet1!$A$3+Sheet1!$B$2・・→B2+C2++Sheet1!$B$2・・）
Sheet3のB2には=INT(((ROW(A2)-2)/100)+1)*2、
Sheet3のC2には=INT(ROW(A2)-1)-INT((ROW(A2)-2)/100)*100)*5
と入力し、これらを下方向に5000行分コピーしていきます。

回答No.4

tom04

No.3です！
前回のC列は掛け算をしていました。
足し算の結果を表示しなければならなかったのですよね？

前回のコードで表示されたC2セルに
=A2+B2
という数式を入れ、フィルハンドルの部分でダブルクリック！

または前回コード内の

＞.Offset(, 2) = i * j
の行を
＞.Offset(, 2) = i + j
に訂正してください。

失礼しました。m(_ _)m

回答No.3

tom04

こんばんは！
A2×A3のすべてを表示させたい訳ですね？

関数ではなくVBAでの一例です。
↓の画像のようにSheetの1行目に項目を入れておきます（ストッパーの意味で）

画面左下の操作したいSheet見出し上で右クリック　→　コードの表示　→　VBE画面が出ますので
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt+F8キー　→　マクロ　→　マクロ実行です）

Sub test() 'この行から
Dim i, j As Long
For i = 2 To 100 Step 2
For j = 5 To 500 Step 5
With Cells(Rows.Count, 1).End(xlUp).Offset(1)
.Value = i
.Offset(, 1) = j
.Offset(, 2) = i * j
End With
Next j
Next i
End Sub 'この行まで

※　ご希望の方法でなかったらごめんなさいね。m(_ _)m

回答No.2

MarcoRossiItaly

No.1です。ごめんなさい。自分で立てた過程で混乱し、大間違いをしてしまいました…。最後のC3セルに入力すべき式は、正しくは次のとおりです。

○
=SUM($B3,C$2,$B3+1,$B3-1,C$2*2,C$2/2)

×
=sum($B3,C$2,sheet1!a2+1,sheet1!a2-1,sheet1!a3*2,sheet1!a3/2)

回答No.1

MarcoRossiItaly

「=A2+A3+B2+B3+B4+B5」という式ですが、関数を使って「=sum(a2:a3,b2:b5)」と書いたほうがスッキリするし、意味も分かりやすいかと思います。ただし、今回はB列の値がA列に応じて変化するらしいので、ご質問の作業のためには、ここまで簡潔な式は書けません。

例えば、B2、B3、B4、B5セルにそれぞれ「=a2+1」「=a2-1」「=a3*2」「=a3/2」と入力されているものとします。

また、以下、別シートに抽出表を作るものとして話を進めます。話の分かりやすさのためなので、同じシートのどこかにその表を作っても構いません。抽出元のシート名を「Sheet1」とします。

抽出先のシートで、次の表を用意します。なお、2の倍数や5の倍数の数列は、3つくらい手入力し、あとはドラッグでもすれば簡単に得られます（オートフィル）。

　　　 A列　　B列　　C列　　D列　　E列　　F列　　G列
1行　　　　　　　　 　A3
2行　　　　　　　　　　5　　 10　　 15　　 20　　 25
3行　　 A2　　 2
4行　　　　　　 4
5行　　　　　　 6
6行　　　　　　 8
7行　　　　　　10

次に、C3セルに次の式を入力します。

=sum($B3,C$2,sheet1!a2+1,sheet1!a2-1,sheet1!a3*2,sheet1!a3/2)

C3セルをコピーし、C3:G7の範囲に貼り付けます。