Excel Maniacs

関数で出来ますか～？ 第55回 Mode紛い

【問】

B列（B5:B1004）に０～９９までの数値が入っています。
この中で１番多い数値はMODEを使えば出ます。
MODEとCOUNTIF禁止でMODEと同じ値をE９に。

　　　　　　　　　　　　　　　　　　　　　　　　　（からくちさん）

解答用参考ファイル： 20190514をダウンロード

 ***********************************************************************

解答発表は05/18（土）の２０：００です。それまでは直接数式を

書き込むこと・ポイントとなる関数名を書き込むことはご遠慮ください。

***********************************************************************

 

関数で出来ますか～？ 第54回 魔方陣Ⅱ 解答例

からくちさんから出題者解答例が送られてこないので・・・

【解答例】

循環参照です。

まず・・・

「ファイル」→「オプション」→「数式」と辿り、「反復計算を行う」にチェックを入れ、「最大反復回数」を最大にしてください。

そしてD５に・・・

=IF(COUNT($D$5:D5)=4,5,IF(AND(COUNTIF($D$5:$F$7,D5)<2,$D$5+$E$6+$F$7=15,$F$5+$E$6+$D$7=15,D$5+D$6+D$7=15,$D5+$E5+$F5=15),D5,IF(ROW()=5,15-D6-D7,IF(COLUMN()=4,15-E5-F5,INT(RAND()*9)+1))))

・・・と入力してF7までコピペ（187文字）

実は・・・この式はminさんが、以前作ったものを完全にパクったものです。m(__)m

minさんは凄すぎ！！

第58回 魔方陣 解答例

　魔方陣を作るってのは、以前問題が尽きかけた時、できるかどうか分からない問題で
良ければと言うことでくまぷーさんに提案していたものです。
式一種類では全く歯が立たないのですが、ワークエリアを使っての循環参照による繰り返し
計算ならなんとかなりそうだというので出題をお願いしました。

最初作った時は、３×３の場合、B2:C3の４セルの数字を変えて魔方陣ができるまで繰り返計算させていたのですが、その場合約3000回の反復計算が必要でした。
後になって、3セル指定されれば後は自動的に決まってしまうのに気が付き反復計算を大幅にダイエットしたのが33Aです。
この時点では、反復回数をどうこうするというより、文字数のダイエットに集中してました。
しかし、これは何ぼ何でも無駄なサーチをやっているので、その辺りを整理したのが３３Bで
反復回数は劇的に減少しました。
なお、３３B、４４A、４４Bは続行可能で、次々魔方陣を作って行けます。

3×3の魔方陣はいずれにしろ大した反復は必要ないのですが、4×4になるとちょっとやそっとでは収束しません。
で、いろんな工夫をたたきこんで、１万回以下の反復でなんとか最初の魔方陣が見つかるようにしたのが４４Aです。
(33B以降は式のダイエットはほとんど考慮してませんので、冗長な式が結構あります）

５０回程続行してみたのですが、最短は反復５回、長い場合で9000回くらいの反復で次の
魔法陣が見つかります。（平均3000位だと思います）

44Bは4分割した4セルの合計がいずれも34になる特殊形だけを早く見つける工夫をしたものです。
（4×4の魔方陣ではこのタイプが相当数あります）
これは300回くらいの反復で次々みつかります。

なんか、今回は私一人で楽しんじゃったみたいですみませんm(＿＿)m

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　（y sakudaさん談）

★sakudaさんのファイル：Q58_2010_sakuda_san.lzhをダウンロード

★くまぷーのファイル：Q58_2010_kuma_pooh.lzhをダウンロード