- 締切済み
【Excel】1000パターンの組み合わせ
初投稿です。いたらない所もあると思いますがよろしくお願いします。 教えて欲しいのはEcxelで、以下のような事が出来るかどうかです。 ただ当方マクロ等には詳しくはないので、出来ればエクセル関数のみでやりたいと思ってます。 逆にわかりにくくなるかもしれませんが箇条書きで書いてみます ・10個の異なる数値(3桁以上) ・それらが3列分ある ・1列ずつ1個ずつ数値を取り出す ・その合計値が、ある一定の数値より上か下かを判断する 1列から1個ずつ取り出すのを全パターンやって調べたいのです。 合計1000パターンですかね? --------------------------- 3個3列で例題的に表示しますと 199 199 456 239 239 547 279 279 638 199+199+456→合計値A 199+239+547→合計値B 199+199+638→合計値C … 中略 … 279+279+547→合計値Y 279+279+638→合計値Z 合計値が1000を越えるのは○○個 --------------------------- やりたい事はこんな感じになります。 合計値を表示させたいわけではなく、 合計値が1000を越えるものが何個あるかが知りたいのです。 で、○○個あるので○○/1000で 確率は何% みたいにやりたいわけです。 全パターンを別シートで合計させてーって考えてたのですがあまりに多くて 何かいい方法はないかと調べてたのですが見つからなくてここに質問させてもらいました。 わかりにくいかも知れませんがよろしくお願いします。
- みんなの回答 (6)
- 専門家の回答
みんなの回答
- 某HN クロメート(Chromate)(@CoalTar)
- ベストアンサー率40% (705/1742)
#5です。関数案をもう一つ思いついてしまった。 =10^-3*SUM(1* (HLOOKUP(A1,A1:A10,MOD(INT((ROW(A1:A1000)-1)/10^2),10)+1,0) +HLOOKUP(B1,B1:B10,MOD(INT((ROW(A1:A1000)-1)/10^1),10)+1,0) +HLOOKUP(C1,C1:C10,MOD(INT((ROW(A1:A1000)-1)/10^0),10)+1,0) >1000))
- 某HN クロメート(Chromate)(@CoalTar)
- ベストアンサー率40% (705/1742)
関数で =SUM(((SMALL(A1:A10+TRANSPOSE(B1:B10),ROW(A1:A100))+TRANSPOSE(C1:C10))>1000)*1)/10^3 [Ctrl]+[Shft] +[Enter] で確定、配列数式です({}で囲まれる) 10行1列+1行10列 → 10行10列 変換して 100行1列+1行10列 → 100行10列
- kagakusuki
- ベストアンサー率51% (2610/5101)
今仮に、Sheet1のA1:C10の範囲に数字が入力されているものとします。 まず、Sheet2のA1セルに次の数式を入力して下さい。 =IF(ROW()>10^3,"",INDEX(Sheet1!A:A,INT(MOD(ROW()*10^(1-COLUMNS($A:A)),10))+1)) 次に、Sheet2のA1セルをコピーして、Sheet2のB1セルとC1セルに貼り付けて下さい。 次に、Sheet2のD1セルに次の数式を入力して下さい。 =IF(COUNT($A1:$C1)=3,SUM($A1:$C1),"") 次に、Sheet2のA1:D1の範囲をコピーして、Sheet2のA2:D1000の範囲に貼り付けて下さい。 そして、合計値が1000を越えるものが何個あるかを表示させるセルに、次の数式を入力して下さい。 =COUNTIF(Sheet2!$D:$D,">1000") これで、合計値が1000を越えるものが何個あるかを求める事が出来ます。 尚、作業列を4列も使用したのは、Sheet1のA1:C10の範囲にある、どの数字を使った場合なのかを判り易くするためですが、どの数字の組み合わせなのか判らなくても構わない場合には、Sheet2のA列~C列に入力した数式は使わずに、 D1セルに入力する数式を、次の様にして下さい。 =IF(ROW()>10^3,"",INDEX(Sheet1!$A:$A,INT(MOD(ROW()/10^0,10))+1)+INDEX(Sheet1!$B:$B,INT(MOD(ROW()/10^1,10))+1)+INDEX(Sheet1!$A:$A,INT(MOD(ROW()/10^2,10))+1))
- imogasi
- ベストアンサー率27% (4737/17069)
VBAも経験ない人が、関数でこんなことやろうとするのは、無茶だ。 組み合わせ問題はエクセルは得意じゃない。 質問者は何処までやってみたのか。丸投げだし。 ーー エクセルはセルに、実際のデータが作られてて無いと和の判別なども難しいと思う。 エクセル関数は ・対象を変えて繰り返し ・変数に値を一時保存 の考えが取れないから、 式の作成がが不可能か、できても極くややこしい式になる。 別に関数そのものに繰り返し的の要素が入るのは、配列数式だが、同行の別列データ同士が多くこれで回答する人が出るかな。い。 ーー 繰り返すが、セルに組み合わせの実際データをつくらないと難しいのではと思う。 まず3個でやってみて、10個なりに類推拡張するのが常道だろう。 ーー 例データ F,G,H列 1-3行 17 1 12 19 3 16 11 6 15 とする ーー C1セルに =INDEX($H$1:$H$3,MOD((ROW()-1),3)+1) 下方向に式複写する。3X3X3=27行まで。 ーー B1セルに =INDEX($G$1:$G$3,MOD(INT((ROW()-1)/3),3)+1) 下方向に式を複写する。27行まで。 ーー A1セルに =INDEX($F$1:$F$3,MOD(INT((ROW()-1)/9),3)+1) 下方向に式を複写する。27行まで。 結果 17 1 12 17 1 16 17 1 15 17 3 12 17 3 16 17 3 15 17 6 12 17 6 16 17 6 15 19 1 12 19 1 16 19 1 15 19 3 12 19 3 16 19 3 15 19 6 12 19 6 16 19 6 15 11 1 12 11 1 16 11 1 15 11 3 12 11 3 16 11 3 15 11 6 12 11 6 16 11 6 15 ーー この3列の各行のデータをD列にSUM関数の=SUM(A1:C1)で合計する。 下方向に式を複写する。 和が一定数以上(この際は35とする)の件数は =COUNTIF(D1:D27,">35") で結果 9 === 質問者の本番?に向けて A-J列+アルファの列を使う。上記のF-Hにあたる列(組み合わせデータ)は右のほうの列の持てt来る。 元データのセル範囲を拡げる.。例 $H$1:$H$3ー>($H$1:$H$10 数が3行の場合の回答になっているので、10行にするため、式の中のINT,MOD関数の3になっている個所を10に改めて式をつくり式複写する。 もちろん式を複写する行数は10x10x10=1000行になる。
- merlionXX
- ベストアンサー率48% (1930/4007)
ANo1 merlionXXです。 多分、Njord3333さんの表の範囲はA1~C10ではありませんよね? それにあわせて間単にVBAのコードを修正できるようにしてみました。 myV = Range("A1:C10") の部分だけで修正できます。 仮にB2~D11が正しい範囲なら myV = Range("B2:D11") としてください。 Sub test02() Dim myV Dim i As Long, j As Long, n As Long, r As Long myV = Range("A1:C10") For i = 1 To UBound(myV, 1) For j = 1 To UBound(myV, 1) For n = 1 To UBound(myV, 1) If Application.Sum(myV(i, 1), Cells(j, 2), Cells(n, 3)) > 1000 Then r = r + 1 End If Next n Next j Next i MsgBox r & "個が1,000を超えていました。", vbInformation, "(´^∇^)σ " End Sub
- merlionXX
- ベストアンサー率48% (1930/4007)
VBA(マクロ)なら一発でOKなんですが。 仮に表の範囲がA1~C10の30個のセルだとしたら、 簡単ですので以下の手順をおためしください。 範囲が違うならコードを書き換える必要がありますが、それよりは表をA1~C10にあわせたほうがかんたんですよね? 1.AltキーとF11キー同時に押し(以下Alt+F11キーと記述)て Visual Basic Editor を呼び出します。 2.Visual Basic Editor のメニューから「挿入」、「標準モジュール」で出てきたコードウィンド(右側の白い広い部分)に以下のコード(Sub~End Sub)をコピペします。 '********これより下********** Sub test01() Dim i As Long, j As Long, n As Long, c As Long For i = 1 To 10 For j = 1 To 10 For n = 1 To 10 If Application.Sum(Cells(i, "A"), Cells(j, "B"), Cells(n, "C")) > 1000 Then r = r + 1 End If Next n Next j Next i MsgBox r & "個が1,000を超えていました。", vbInformation, "(´^∇^)σ " End Sub '********これより上********** 3.Alt+F11キーでワークシートへもどります. 4.Alt+F8キーで出てきたマクロ名(test01)を選択して実行します。