Excel 関数のコーディング事例をご紹介しています。
およそ3年半前に「数独向けの条件付き書式とは何ですか?」という記事を公開しているのですが、条件付き書式」をメインにしていために、「二択分岐」という観点からは「自分で盤面をコピーして用意された盤面に値貼り付けする」というとてもクオリティが低い内容になっていました。
かねがね「いつか作り直さないといけない…」と思ってはいたのですが、気付けば既に3年半が経とうとしていました…
これ以上先延ばしするのも申し訳なく誠に対応が遅くなり恐縮ではありますが、今回新たに「二択分岐」を主眼とした数独の書式を作成いたしましたので、その内容をご紹介したく存じます。
なお記事タイトルでは「マクロ有効ブックではないExcel」としていますが、記事の中に「盤面の値を確定するためのマクロ」をご説明する箇所がございます。
「一般的なExcelブックでマクロを実行することができるのか?」お思いになるかもしれませんが、実は一般的にExcelブックはマクロをブックに保存する事ができないだけで、実行する上では何も問題はありません。
※この辺りの詳細につきましては「Excel XLSX形式ファイルでVBAコーディングを実行する」という記事で説明していますのでご参照いただければ幸いです。
では「何をするためのマクロなのか?」と言いますとマクロを使わないと実装できない「盤面のセルに設定してある数式を値に置き換える」ためのものです。
ただし「盤面のセルに数字を入力する」ために「マウスの左クリックでセルを選択する」場合に、該当セルが「セル入力状態」にならないように気を付けて操作をすればマクロを使わなくても事足ります。
つまり必ずしもマクロは使用する必要はありませんので、あらかじめお含み置きいただければ幸いです。
なお「シートの保護」が前提となりますが盤面のセルに対して「数式を非表示にする」設定をする事でマクロを使用しないで済むバージョンも併せて公開いたします。ただしこちらは入力する以外のセルが「セル入力状態」にならないように気を付けて操作する必要がありますので、どちらか使い勝手の良い方をご利用ください。
※動作は32bit版Excel 2016の バージョン2206(ビルド 15330.20230)を使用して検証しています。
※ブック内の各シートには「シートの保護」を設定していますがパスワードは設定していません。
数独の「二択分岐」の書式とは?
そもそも『「二択分岐」とは何か?』と思われると思うのですが、数独を解いていった時に「自分の使っている解き方」ではどうしても行き詰まってしまう難しい数独問題に遭遇する事がある事と存じます。
その際にブロック(3×3のマス目)・縦1列・横1行の9つのマス目に対して狙い目の数字の入るマス目が2箇所に絞られるような時、いづれか1つに数字を仮置きして解き進めるという「奥の手」を「二択分岐」と当サイトでは表現しています。
※なおこれは一般的な表現ではありませんのでお含み置きいただけれは幸いです。
ところで数独の解き方の「あるべき姿」としては、「二択分岐」などは使わずに解ける事である事と存じます。
この「二択分岐」が「正しいか?、間違っているか?」は「50%の確率」なのですが、この「二択分岐」する回数を減して行かないと問題を解くまでの時間を短くすることはできません。
回数を減らすためには「二択分岐」する直前の盤面を残して置いて、問題を解いた後にその盤面を振り返り「どうすれば良かったのか?」をあれこれ試行錯誤して見る事が肝要であると存じます。
それによって『今まで気づいてなかった新しい解き方を見つける事ができれば「二択分岐」の回数を減らせるのではないか?』と思い、今回の書式を作りました。
話を本題に戻しますが、この「二択分岐」を書式化するには「1つの盤面を2つの盤面にコピーする機能」をマクロ有効ブックではない通常ブックのExcelで実装する必要があります。
合わせてもう一つの課題として『「二択分岐」は何階層まで実装できれば良いか?』と言う事があるのですが、これは「自分の使っている解き方」の発展度合いによりケースバイケースになる事と存じます。
そうなると理論的には無制限に「二択分岐」ができる事が望ましい事になります。(もっとも先ほど記述したようにできれば「二択分岐」などは使わずに解ける事が理想ではありますが…)
以上2つの課題を解決できるような仕様を考える事にしました。
初めに考えた「二択分岐」の仕様
以上を踏まえて作成する書式の仕様としては下図のような盤面構成で初回書式と継続書式という2つのシートを作る事にしました。
- 初回書式シート
- 初期盤面
数独の問題を初期盤面に手入力します。入力した内容は盤面01に反映します。 - 盤面01
盤面01で行き詰まった時は「二択分岐」のために盤面11と盤面12に盤面01の内容を「反映」します。 - 盤面11
盤面11で行き詰まった時は盤面21と盤面22に盤面11の内容を「反映」します。 - 盤面12
盤面12で行き詰まった時は盤面31と盤面32に盤面12の内容を「反映」します。
- 初期盤面
これにより2回「二択分岐」できますが、もしも2回では終わらなかった時は、その盤面を継続書式シートの盤面01に引き継ぐ事ができます。
- 継続書式シート
- 初期盤面が無い代わりに、初回書式の盤面21、22、31、32のいづれかの盤面を盤面01にコピーできるようにします。
- 後は初回書式と同様になります。
- 継続書式シートをシートコピーする事で論理的には分岐先を無制限に増やして行く事ができます。
例えば初期書式の盤面22を継続書式の盤面01に引き継いで継続書式の盤面11と盤面12に「二択分岐」する事が可能です。
もしも初期書式の盤面31も同様に2回で終わらなかった時は、継続書式シートをシートコピーする事で初期書式の盤面31をコピーした継続書式の盤面01に引き継ぐ事ができます。
これにより継続書式シートをシートコピーすれば論理的には解き終わるまで「二択分岐」を続ける事が可能です。
ただしこの仕様だと…
前章の仕様で「二択分岐」の盤面遷移を考えると実際に使用されない盤面の数が増えてしまう事が想定されます。
例えば一番未使用盤面数が多くなるバターンの一例としては下図のようになります。
※灰色に色付けした盤面が未使用になります。
未使用な盤面ができる事はさておき、1つのシートで分岐が2回までしかできないというのも残念なところです。
ちなみにこの場合は盤面12の分岐を盤面21と22にする事ができれば、初回書式シートだけで解けるようになるはずです。
そこで仕様を変更する事にしました。
ただし「だからと言って」1つのシートに収める盤面数を今以上に増やすのは得策ではありません。
と言うのは詳しくは後段でご説明いたしますが、盤面1つを増やすと約1,550のセルに数式がセットされ、更に延べで約1,640セル(重複あり)に条件付き書式が設定され、セル書式のフォントや表示形式、またデータの入力規則やフォームコントロールなどの設定もあるのでxlsxのファイルサイズがどんどん大きくなってしまいます。
このような理由から盤面数は変えずに対応する事にします。
変更した「二択分岐」の仕様
初期の仕様では「二択分岐」という事から、「盤面11は盤面21と22、盤面12は盤面31と32」というように分岐を2箇所に固定していたのですが、これを改め「盤面12から盤面21と22にも行ける」ようにして、更に「盤面21と22はそれぞれ盤面31と32にも行ける」ようにしました。
言葉では解りづらいので図にすると下図のようになります。
- 「盤面12から盤面21と22にも行ける」のですが、「盤面11から盤面21と22に行く」フラッグが立っている時は盤面12の内容は反映されません。
- 同様に「盤面12から盤面31と32に行く」フラッグが立っている時は盤面21or22の内容は反映されません。
- 継続書式シートには初回書式の盤面21、22、31、32に加えて盤面11、12からもコピーできるようにします。
これであれば前章のパターンのような3回の分岐は右図のように初回書式だけに収納する事ができるので、使わなかった継続書式シートは削除してしまう事が可能です。
仕様を実装した通常のExcelブックについて
今回作成した書式はExcelの通常ブックでマクロ有効ブックではありません。この制約のもと、なるべく盤面入力の負荷を軽減するためにワークシート関数の他に「セルに値を格納してくれるフォームコントロール」を使って実装しています。
なお新しい書式のブックの名前を「sudoku.xlsx」にしていて、この名前は以降の文面に登場しますのでお含み置きいただければ幸いです。
まずはsudoku.xlsxブックに含まれる下記3つのシートの外観からご説明いたします。
- 初回書式シート
- 継続書式シート
- 盤面確定マクロ ※シート名になります。
- 「数式を非表示にする」バージョンには含まれていません。
それぞれのワークシートについて
それぞれのシートの画面キャプチャは下図になります。
順を追ってご説明いたしますが、1点先にお願いした事があります。
※この辺りの説明は「Excelで条件付き書式を使用する際の注意点」でしていますのでご参照ください。
初回書式シートの外観説明
初回書式には、01から32までの7つの盤面の他に次の2つの盤面が存在します。
シート上で本システムが使用するエリアについて
- 手入力
- 数独の問題を一番最初に手入力するための盤面で、候補欄の「データの入力規則」を除いて盤面01～32のような設定は何もしていません。
- コピーエリア
- 他のsudoku.xlsxブックから盤面をコピーするためのエリア(盤面)
- 盤面確定マクロでもこのエリアを使用していますのでご注意ください。
- 01～32
- 手入力の盤面から始まり盤面01以降は一つ手前の盤面の内容を引き継ぐ事ができます。
- 各マス目の候補欄は1～9まで左から順番に9マス設定
- 設定されている数字以外の入力はエラー(「データの入力規則」により)になります。
- その他に条件付き書式、フォームコントロールなど設定をしています。
初回書式・継続書式のシート上で背景色が灰色のセル(フォームコントロールを除く)は本システムで使用しています。
※シートの保護により更新はロックしています。
- 例えば50～94行目まで広範囲が背景色が灰色のセルになっています。
この箇所では盤面01～32上の1つのマス目に入力された数字を数式を使って連続した9列のセルに反映しています。
※左図ではCH66セル(灰色セル)にCH18を参照する数式が設定されている事が解りますが、CH66～CP66までの連続する9つのセルにもCH18を参照する数式が設定されています。
→そのためにCH66～CP66のセルは空白になっています。
では「この箇所は何に使っているのか?「と言いますと「マス目に数字が入力された時に、該当マス目の候補欄の背景色を灰色に設定する」ための条件付き書式で使用しています。
これ以外にも背景色が灰色の箇所があるのですが、その他の箇所の説明につきましては、後段でご説明するものを除いて割愛をいたしますので、お含み置きください。
継続書式シートの外観説明
継続書式シートには初回書式シートと同じ01～32までの7つの盤面があり、それ以外には参照するシート名及び盤面No(番号)を指定する項目があります。
- シート・盤面No指定
- シート名欄は手入力、盤面No欄はプルダウンメニューから選択します
- 盤面確定マクロで使用
- 初回書式ではコピーエリアとしても使用しましたが、継続書式では盤面確定マクロでのみ使用するシステムエリア(背景灰色)になります。
- 01～32
- 初回書式と同じになります。
シート・盤面No指定の入力方法
参照するシート名・盤面No(番号)の入力方法はつぎのようになります。
- シート名
- シート名は手入力になります。なおデフォルトで「初回書式」と入力してあります。
継続書式シートをシートコピーして追加した場合は、追加したシートでシート名を変更する必要があります。
- シート名は手入力になります。なおデフォルトで「初回書式」と入力してあります。
- 盤面No
- 項目を入力状態にするとプルダウンメニューの▼マークが表示されるので▼マークをクリックして対象の盤面番号を選択してください。
- 選択すると上右図のように盤面01に選択したシート名・盤面の内容が表示されます。
盤面確定マクロシートの外観説明
- A列(A1～A49セル)
- マクロコーディングのテキストデータです。
- 注釈箇所は文字の色を緑色にしています。
- C-D列(C1～D72セル)
- 盤面確定マクロの動かし方とブック保存・終了時の操作マニュアルを記載しています。
盤面確定マクロの中身の詳しい説明は割愛致しますが、実装している内容は下記になります。
- マクロを実行すると「入力値の確定」ウインドウが開くので盤面番号(01～32)を入力
- 入力された盤面番号の盤面をコピーして「コピーエリアor盤面確定マクロ使用エリア」に「値」貼り付け
- 「コピーエリアor盤面確定マクロ使用エリア」の上・中・下の3連ブロック(3×3のマス目)をコピーして、入力された盤面番号の該当ブロックに「数式」貼り付け
ちなみに「コピーエリアor盤面確定マクロ使用エリア」を経由させる理由はつぎの2点になります。
- 盤面の数字入力マスがセルを結合したセル範囲になっているため
- 盤面の数字入力マスには数式が設定されているため
なおこの盤面確定マクロをMicrosoft Visual Basic for Applicationsにコード貼り付けする詳しい説明は盤面確定マクロシートのC-D列(C1～D72セル)の操作マニュアルをご参照ください。
「二択分岐」の操作方法について
先の章で使用した左図の流れを例題にして、sudoku.xlsxで「二択分岐」する際の具体的な操作方法をご説明致します。
盤面01を盤面11と12に反映する
- 盤面01の右下にある「反映」というラベルが付いたチェックボックス(左図緑上矢印)をクリックします。
- このチェックボックスはExcelが提供するフォームコントロールで、結果はこのフォームコントロールの真下のFC24セルに格納されています。
- 盤面11と12に盤面01の内容が反映されます。
- 内容を反映させる数式は後段でご説明いたします。
盤面12を盤面21と22に反映する
- 盤面12の右下には2つの「反映」チェックボックスがありますが、左側の21・22の矢印マークの付いた方(左図紫下矢印)をクリックします。
- すると盤面21と22に盤面12の内容が反映されます。
- 左図の例では盤面の内容がすべて同じになっていますのでお含み置きください。
盤面21を盤面31と32に反映する
- 盤面21の右下に「反映」チェックボックスがあるので(左図青下矢印)クリックします。
- すると盤面31と32に盤面21の内容が反映されます。
盤面のマス目に設定してある数式について
盤面01～32の1つの盤面のマス目・候補欄のすべてのセル(810個)に数式がセットされています。
- 横一行には([候補欄(9)+マス目(1)]×横9マス(9))=90セル
- 1つの盤面全体では横一行(90)×縦9列(9)=810セル
- 例えば盤面01の左上の候補欄のCH3セルを選択すると数式「=_0」がセットされています。
実は数式「=_0」は盤面01のすべてのマス目・候補欄のセル(左図橙色で囲った3つのエリア)にセットされています。 - 同様にFV6セル(左図赤枠左矢印)を入力状態にすると手入力の盤面上の同じ位置のセル(左図黄緑左矢印)を参照している事が解ります。
実は数式「=_0」の「_0」はタブメニュ「数式」→「名前の管理」で定義された名前です。
※名前の定義はシートごとに設定する必要があり数は多いですが、セットする名前は同じにしています。
※なお名前と盤面番号の関係は下記のようになります。
| 位置 | 名前 | 盤面 番号 | 名前 | 盤面 番号 | 名前 | 盤面 番号 |
|---|---|---|---|---|---|---|
| 最初 | _0 | 初回01 | _N | 継続01 | － | － |
| 上段 | _1 | 11 | _2 | 12 | _3 | 13 |
| 下段 | _4 | 21 | _5 | 22 | _6 | 32 |
※名前に設定している数式内容のご説明は割愛いたしますが、やっている事はチェックボックスの値に基づき「どのシートのどの盤面を参照するか?」をセルの相対参照で指定しているだけです。
※名前の文字数を短くしているのには訳がありまして、数式がセットされているセルを入力状態にすると数式の長さの分だけ入力エリアが右に伸びて行きます。そうなると幅が極端に短い候補欄ではこのエリアが邪魔になるので、極力短い名前にしています。
※「数式を非表示にする」バージョンでは「シートの保護」を解除する事で盤面に名前の数式が表示されます。
セル入力状態から抜けるためには
数式が設定されているセルでセル入力状態になるとそのセルに数字を入力しても数式に追加入力されてしまう事になって困ります。
ちなみに盤面確定マクロで数式を数字に変更しておけば、このような煩わしい状況に陥らなくて済むのですが、本記事の冒頭でもお知らせしたように「気を付けて操作をすれば」マクロを使わなくても大丈夫です。
そうは言っても意図せず「セル入力状態」になってしまう事もあるので抜け出すための操作方法をお伝えいたします。
盤面の書式設定
候補欄及びマス目のセルに対して下記の書式を設定しています。
候補欄
- フォント及びフォントサイズ
- 「Segoe UI Black」が小さいサイズでも見やすく感じたので採用しています。
- フォントサイズは小さくて恐縮ですが弊社使用モニターでギリギリ表示できるサイズにしています。
- 表示形式のユーザ定義につきましてはこの後の章でご説明いたします。
※もしもご使用のパソコン環境で候補欄の数字が表示されない時は、フォントサイズを更に小さくしてご確認いただければ幸いです。
マス目
- フォント及びフォントサイズ
- 「Meiryo UI」のサイズ17に設定しています。
- フォントとサイズはセルに表示されるものであれば、盤面数が多くて大変ではありますが変更可能です。
セル表示形式にユーザ定義を使用している理由
ユーザ定義に設定しているの「#」です。「#」は一桁の数字て0を表示しません。
では「なぜそのような設定にしているのか?」と言うと「Excelでは空白セルに対してセル参照をすると空白ではなく0(ゼロ)が返されるためです。
先の章で数式を使って手前の盤面の同じ場所を参照しているご説明をいたしましたが、このユーザ定義をしていないと盤面上に空白のマス目はすべて0で埋め尽くされてしまう事になります。
従いまして、まだ数字を入力していないマス目を空白に見せるためにこの設定をしています。
※ちなみに盤面確定マクロでは値貼り付けをする際に0をスベースに置き換えていますので0は表示されません。
盤面の条件付き書式について
条件付き書式で盤面の背景色を動的に変更するポイントは前回と変わっていませんが、設定している数式は処理速度を意識して見直しを図っています。
条件付き書式には下記の6のパターンが設定されています。(下図右の薄青背景色の丸数字と下記リスト番号は対応しています。)
- マス目に数字を入力したら、該当マス目の候補欄の背景色を薄灰色にする。
- 「数字」欄に入力した数字(1～9)と同じ「盤面に入力されている数字」の背景色を濃い薄緑色にする。
- マス目・候補欄ともに処理対象にしています。
- 盤面で指定した横一行 or 縦一列の背景色を薄緑色にする。
- 横一行・縦一列それぞれが正しく1～9の数字で埋まった時に行頭・列頭の数字を太字にして背景色を薄水色にする。
- 一ブロック(3×3のマス目)が正しく1～9の数字で埋まった時はブロックの中の左上マス目の文字を太字のイタリック体にして文字の色を青にします。
- 盤面上の9つのマス目に同じ数字が正しく入力されたらスクロールバー下の該当数字を太字にして背景色を薄水色にする。
※「正しく」入力されているか?は条件付き書式に設定する数式の中で判断していますが、数式内容のご説明は割愛いたします。
※条件付き書式は先頭に設定されているものが優先度が一番高く、下に行く程低くなって行きます。
※1つのセルに複数の条件付き書式が設定されている場合は優先度が高い方が反映されます。
以下優先度順ではなく上記リスト番号順に従ってご説明いたします。
条件付き書式のリスト「1及び2」の動き方
- 左図青矢印が指している候補欄の背景色薄灰色は、マス目に数字が入力されたので配色しています。
→上記リスト番号「1」 - 盤面01～32の左下(左図黄色矢印)に「数字」という項目があります。
- 例えば1を入力すると、盤面上の候補欄及びマス目に「1」と入力されているセルの背景色が濃薄緑色に変わります。
- 同様に2～9まで同じ動きになります。
→上記リスト番号「2」
スクロールバーの使い方
数独を解く時、1～9の数字を「数字」項目に入れて盤面上の数字の位置を確認しながら、候補欄にマス目に入る可能性のある数字を入力する操作をします。
この時に「数字」項目をマウスでクリックし入力状態にしてからキーボードで数字を入力するというのは面倒くさい話になりす。
そこで普通のExcelブックで使用できるフォームコントロールの1つのスクロールバーの使い方をご紹介いたします。
※スクロールバーにするとマウスの操作だけで数字を変える事ができますが、指定の数字に飛ぶことはできませんので、お含み置きいただければ幸いです。
- 盤面01～32の「数字」項目の右隣り(左図黄色矢印の範囲)にスクロールバーを設定しています。
- 右矢印をクリックすると0～9の間で1目盛づつカウントアップし、左矢印をクリックするとカウントダウンします。
- 「数字」項目が空白で右矢印を1回クリックします。
- 「数字」項目に1がセットされます。
- 左矢印を1回クリックすると「数字」項目に0がセットされます。
- 0は盤面の書式のユーザ定義では表示されないのですが、ここでは標準書式にして敢えて0を見せています。
- 見えなくしても「入力状態にすると0が入っている」というのが紛らわしいからです。
- スクロールバーの中間地点をクッリクすると8目盛カウントアップorカウントダウンします。
- 主に1から9、9から1に移動したい時に使用します。
条件付き書式リスト「3」の動き方
盤面の「指定した横一行or指定した縦一列」に背景色を設定するのですが、普通のExcelブックで使用できるフォームコントロールの1つのスピンボタンを使用してクリックする事で設定できるようにしています。
- 1つの盤面に行頭及び列頭にぞれぞれ9箇所にスピンボタンを配置しています。
- スピンボタンには▲▼ボタンがありますが、左図では盤面01の5「列」目の▲ボタンをクリックした状態になります。
- 上記状態で更に4「行」目の▲ボタンをクリックした状態になります。
※スピンボタン・スクロールバーともに値を指定したセルに反映させますが、設定している上限値よりも大きな数字が指定したセルにセットされていると、それより小さな数字はセットできない仕様のようです。
そのため上記の列では5列を選択しているので続けて6～9列のいづれかは選択できますが、1～4列はクリックしても背景色は移動しません。1～4列に戻るためには一旦▼ボタンをクリックして指定したセルにセットされた値を下限値に戻す必要があります。
下限値はいづれの場合も0(ゼロ)にしているので、▼ボタンは同じ盤面であればそれぞれどこのボタンを押しても(行は行、列は列でのくくりにはなりますが)下限値に戻ります。
スピンボタンの値をセットするセルについて
実は盤面01～32には7つの盤面があるのですが、値をセットするセルは2箇所に集約しています。
- 盤面01・21・22は盤面01の真ん中下の左図で黄色で丸したセルに行・列それぞれの値をセットしています。
- 盤面11・12・31・32は手入力盤面の真ん中下のセルになります。
そのためシートを縮小して見ると、上図の例では盤面01の5列目の▲ボタンをクリックしたにもかかわらず、それぞれ集約した盤面の同じ列番号に背景色が付いている事が解ります。
条件付き書式のリスト「4・5・6」の動き方
盤面の横一行、縦一列すべてが埋まった時、または1ブロック(3×3のマス目)がすべて埋まった時に下図の位置の背景色または文字の書式を設定しています。
- 横一行、縦一列が数字1～9で埋まった時は行頭、列頭の数字を太字にした上でセルの背景色を薄青色に設定していますので、色が変われば一行、一列が埋まった事が解ります。
左図では5行目、4列目が埋まった状態です。
→リスト番号「4」 - 1つのブロックがすべて数字1～9で埋まった時はブロックの左上のマス目の数字を青色の太字イタリック体に設定しています。色が変われば1ブロックが正しく埋まっている事が解ります。
→リスト番号「5」 - 一つの数字が9個のブロックに正しく入力された時はスクロールバー下の該当数字の文字を太字にした上で結合したセルの背景色を薄青色に設定しています。
→リスト番号「6」 - 左図では9個のプロックに1つづつ数字の7が入力されていますが、7列目には数字7が無く8列目に数字7が2箇所存在した状態なので、書式は元のままです。
- 「横一行、縦一列ともに該当数字が1つだけ存在する」というロジックで判断しています。
画面を切り替えるためのリンク設定について
盤面01～32の左下に盤面番号にリンク設定をした箇所がありますが、この内容についてご説明いたします。
- 左図では盤面12のリンクを青矢印が示していますが、これをクリックして見ます。
- 左図の盤面01ではリンクは「TOP [01] [11] [12]」の4箇所ですが、盤面によって遷移先を変えています。
- 画面全体が選択状態になりますが、画面左上に盤面12の左上が重なる形でシートが遷移します。
- 画面全体が選択状態はどかのセルをクリックする事で解消されます。
「なぜこのようなリンクを設定しているか?」というと候補欄のセル幅が極小のために盤面を切り替えるのも面倒くさいところがあります。
そこで「画面全体が選択状態になる」欠点はありますが、意図した盤面に素早く切り替えたい時にご使用いただければ幸いです。
なおこのリンク設定の数式の中で$CN$80というセルを参照しているのですが、この内容を簡単にご説明いたします。
- $CN$80というセルは初回書式・継続書式ともに設定しています。
この数式の結果は左図赤線の内容なのですが、数式により得られる結果は「[ブック名]’シート名’」(シート名にシングルコーテーションを付けた形)になります。 - また$CN$80の数式では左図黄色枠でくくったCN78というセルを参照しています。
この数式はCELL(“filename”,A1)関数を元に「[ブック名]シート名」(シート名にシングルコーテーションが付かない)を抽出するためのものです。
※この辺りの詳しい説明は「Excelでシートコピーした時リンク設定をコピー先にするための数式」をご参照いただければ幸いです。
シートの保護について
「シートの保護」を設定する一番の目的は「ロックしているセルを不本意に更新する事から防ぐ」事であると存じますが、今回はそれ以外でもシートの保護をしなければならない理由がありますのでご説明いたします。
今回の数独の盤面では数式を設定してあるセルに対して数字を入力できるようにしていますが、そのために候補欄・マス目ともにすべてのセルの「ロックを解除」する必要があります。
- ただし「Excelのオプション」の中の「数式」タブに「エラーチェック ルール」が存在するのですが、デフォルトでは「数式を含むロックされていないセル」が有効になっているはずです。(左図で黄色線を付けたチェックボックス)
- この場合、上記項目ヘルプ(左図黄色吹き出し)に書かれているように該当箇所にエラーインジケータが表示される事になります。
- ワークシートをコピーした時に、シートの保護がされていないと数式をセットしたセルすべてにエラーインジケータが表示されてしまいます。
- 左図はシートの保護を解除して初回書式シートをコピーした例です。
今回のように1つのシートの中に多くの数式がセットされていると、エラーインジケータが表示し終わるまでにもかなり時間がかかります。
これを避けるためにも「シートの保護」が必要になります。
※初回書式・継続書式については事前に「このエラーを無視する設定」をしているので「シートの保護」を解除してもエラーインジケータは表示されません。
※なおこれを回避するために「数式を含むロックされていないセル」を無効化すると、すべてのExcelブックに対しても適用されるためにお勧めできません。
従って無効化はせずに「シートの保護」もしくはエラーインジケータが表示されているすべてのセルを選んで「このエラーを無視する設定」をする事をおすすめ致します。
「エラーを無視する設定」の詳細につきましては「セル左上隅のエラー表示(エラーインジケーター)を有効に使う」をご参照いただければ幸いです。
sudoku.xlsxのダウンロード
zip圧縮するとファイルサイズが477KBととなり、xlsxのままより175KB小さくなるのでzip圧縮ファイルでファイルをアップする事にしました。
| 解凍時ファイル名 | 解凍時サイズ | ZIPファイル無料ダウンロード |
|---|---|---|
| sudoku.xlsx | 652KB |
なお本品(sudoku.xlsx)はフリーウェアとしてご自由にご利用いただけますが、(改変した場合を含めて)再配布する事はできません。
※ダウンロードした本品をご自身が使用する上で複製したり改変したりする事はできます。
※解法結果を共有する時は、本品をそのまま受け渡しするのではなく「画面キャプチャ・画面ショット」を取っていただけますようお願い申し上げます。
※ブック内の各シートには「シートの保護」を設定していますがパスワードは設定していません。
盤面確定マクロを使わない「数式を表示しない」バージョン
盤面確定マクロはセル入力状態になった時にセルに設定されている数式が表示されてしまう事を回避するのが目的でした。
実は「シートの保護」が前提になるのですが、セルの書式設定の保護タブには「ロック」とともにセルに設定されている数式を「表示しない」チェックボックスがあります。これを有効化すると「シートの保護」がされている状態ではセルに設定されている数式が何も表示されず空白セルのように見えます。
この設定をする事でも、数字を入力しようとしているセルに対しては盤面確定マクロを使用するのと同じような効果を得る事ができます。
ただしご注意いただきたのは、「前の盤面から数式で数字が表示されているセルまたは後から数字を入力したセルに対して、不本意にセル入力状態にして、そのまま別のセルにフォーカスを移動してしまうと、盤面に設定されている数式(数字)が削除されてしまう」事です。
※これが「数式を非表示にする」設定にした場合の最大のデメリットです。
従いまして数字を入力する以外のセルが不本意に「セル入力状態」になってしまった時は「セル入力状態から抜けるためには」の章でご説明した方法で入力状態を解消する必要がありますのでご注意ください。
なおある程度操作に慣れてくると、こちらの方が「気を付けて操作すれば済む」ので使い勝手は良いかもしれません…
先ほどと同様にzip圧縮ファイルでファイルをアップしています。
※ブックの名前は「sudoku.S.xlsx」にしています。
| 解凍時ファイル名 | 解凍時 サイズ | ZIPファイル無料ダウンロード |
|---|---|---|
| sudoku.S.xlsx | 549KB |
なお本品(sudoku.S.xlsx)はフリーウェアとしてご自由にご利用いただけますが、(改変した場合を含めて)再配布する事はできません。
※ダウンロードした本品をご自身が使用する上で複製したり改変したりする事はできます。
※解法結果を共有する時は、本品をそのまま受け渡しするのではなく「画面キャプチャ・画面ショット」を取っていただけますようお願い申し上げます。
※ブック内の各シートには「シートの保護」を設定していますがパスワードは設定していません。
※盤面確定マクロシートは含まれていません。
最後に
マクロ有効ブックではないExcelの数独の書式をご紹介いたしました。
「マクロ有効ブックではないExcel」と言いながらマクロの力を借りないと対処できない事があり、「盤面確定マクロ」のコーディング事例も併せてご紹介しています。
では「なぜマクロ有効ブックにしないのか?」につきましては、現状マクロ有効ブックのExcelを公開したとしてもセキュリティの観点から簡単にはご使用いただけないものと推察致します。
また「マクロ有効ブック」にするのであれば、今回の書式に含まれる「使い難い部分」を無くした形にするべきで、その意味からも「マクロ有効ブックではないExcel」にこだわった書式にしております。
ダウンロードしていただいた方がもう少し安心して「マクロ有効ブック」を使用できるようになった際には、再度新しい書式に挑戦したいと思います。
以上最後までご一読いただき誠にありがとうございました。
