excel関数について質問です。

excel初心者です。

例えば、１から１００までの数字の中で、合計が２００になるパターンを割り出したい時に使える
Excelの関数はありますか。

No.2 回答者： yuji3690 回答日時： 2017/04/17 20:58

入力中に操作ミスで消えたので書く気が失せてしまいましたが…

大雑把になりますが書き直します。

自分の考えた方法では、100桁の2進数で表現し、
各桁の値が1なら、その桁に対応する数字を加えるとし、
合計が200になった場合には、その際に使用した数値を羅列する、
それ以外は空白とする、
フィルタによって空白のものは非表示とする。
といった方法で一応できるとは思いました。

100桁の2進数ということは、
2^100=562949953421312*562949953421312*4≒1267650600228230000000000000000
行必要になるという事ですが…
(2^49=562949953421312です。2^50はエクセルの計算上表示される有効桁数をオーバーしたようです）
まぁ行数は限界までいっても別のシートで考えれば良いのでとりあえず置いておきます。

更に、10進数を2進数に直すのは2進数で9桁(111111111)までしかできないようですので、16進数に直して判断するとか、9桁に区切って判断する必要が有ります。
更に、16進数にしたところで、7FFFFFFFFFまでしかできないようです。
これは2進数に直すと4*9+3=39桁なので、結局区切る必要はありそうです。

これらの問題はありますが、とりあえずやってみた方法としては、
A1=TEXT(DEC2BIN(ROW()),"0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000")
これはそのセルの行番号(A1やB2のアルファベットを除いた数字の部分)を2進数にして、無理やり100桁の整数表示としたものです。
(結果的には上記の通り9桁が限界でしたが)
B1=IF(LEFT(RIGHT($A1,COLUMN()-1),1)="0","",COLUMN()-1)
C1~CW1にも同じ式が入ります。(B~CWは計100列です)
式の意味は、A列のセルに表示されたデータの右から「そのセルの列番号(A列なら1、B列なら2)から1を引いた値」番目の数字が0なら空白、そうでなければ「そのセルの列番号から1を引いた値」を表示する。
というものです。
例えば、A列が(左の0を省略して)101であれば、B列に1、D列に3が表示されます。
CX1=SUM(B1:CW1)
これでその表示された値を合計したものが表示されます。
先ほどの例なら1+3=4ということです。
CY1=IF(CX=200,B1&","&C1&","&D1&","&E1&","&F1&","&G1&","&H1&","&I1&","&J1&","&K1&","&L1&","&M1&","&N1&","&O1&","&P1&","&Q1&","&R1&","&S1&","&T1&","&U1&","&V1&","&W1&","&X1&","&Y1&","&Z1&","&AA1&","&AB1&","&AC1&","&AD1&","&AE1&","&AF1&","&AG1&","&AH1&","&AI1&","&AJ1&","&AK1&","&AL1&","&AM1&","&AN1&","&AO1&","&AP1&","&AQ1&","&AR1&","&AS1&","&AT1&","&AU1&","&AV1&","&AW1&","&AX1&","&AY1&","&AZ1&","&BA1&","&BB1&","&BC1&","&BD1&","&BE1&","&BF1&","&BG1&","&BH1&","&BI1&","&BJ1&","&BK1&","&BL1&","&BM1&","&BN1&","&BO1&","&BP1&","&BQ1&","&BR1&","&BS1&","&BT1&","&BU1&","&BV1&","&BW1&","&BX1&","&BY1&","&BZ1&","&CA1&","&CB1&","&CC1&","&CD1&","&CE1&","&CF1&","&CG1&","&CH1&","&CI1&","&CJ1&","&CK1&","&CL1&","&CM1&","&CN1&","&CO1&","&CP1&","&CQ1&","&CR1&","&CS1&","&CT1&","&CU1&","&CV1&","&CW1)
これでCX列が200の場合のみ、B～CW列に表示されたものを","で区切って表示します。
IFを用いて2番目以降の数字である場合のみ"+"でつなげる。という方法もできますが、
数が多いため面倒になり、この形にしました。
空白でも","は表示されるので、1+99+100=200の場合、1,,,,,(略),,,,99,100といった表示になります。
CX列が200でなければ空白となるので、フィルタで空白を非表示にすれば、完成です。

となる予定だったのですが、最初に書いたような理由で、もう１手間２手間かけなければならないです。
まぁ、なかなか簡単に表示できるわけではないということですね。
他の方が良い案を出してくれる事を期待します。

ちなみにパターン数として
2つ以下の合計では表示できない。
3つの合計なら100+(99+1)~100+(51+49),99+(98+3)~99+(51+50),…,68+(67+65)
=49+48+46+45+43+42+40+39+37+36+34+33+31+30+28+27+25+24+22+21+19+18+16+15+13+12+10+9+7+6+4+3+1+0
=49*34/2
=833パターン
これを全て書き出すだけでも一仕事ですねｗ

No.1 回答者： runix2007 回答日時： 2017/04/17 19:37

ゴールシーク