道具屋がExcellツール集の作成にあたり、「ヘルプ」のみで知り得た情報を元に作成した、独自のノウハウ集です。 「ヘルプ」の例題は我々工学系になじみの薄い、金利がどうの収益がどうのと、事務系が多いようでピント来ません。 道具屋は力学が専門ですので、例題はそちらの系統と成ります。ご同業の方には、なじみ易いと自負しております。 皆様方の少しでもお役に立つ様なら幸いです。
下図は軸組の壁倍率表です。範囲名を、
②表は”倍率表”、
①見出しに”軸組種類”と名付けています。
④ユーザーに軸組種をリスト選択させ、 ③右隣セルで検索関数を用いて壁倍率を得ています。
範囲に名前を付けると、以降セル範囲に対する操作や数式内の参照を範囲名で行えます。
たとえば、名前ボックスから範囲選択ジャンプ、ワークシート関数やマクロ内でセル範囲指定等。
ワークシート内の数式は、その参照先を移動してもExcelが自動的に調整してくれますが、マクロ内までは面倒は見てくれません。
そこで、マクロ内では移動されて困る様な参照先は範囲名で記述する事でその問題は解決できます。
注意 範囲名は絶対参照で記憶されます。範囲名を含む数式を他ブック等に複写すると外部参照が設定されます。
名前を付けるにはコマンド「挿入」「名前」「定義」で「名前の定義」ダイアログを表示
テキストボックスに範囲名称を記入し、参照範囲のレンジセレクターで範囲を指定、「追加」「ＯＫ」とする。
名前に関するHint!
シートに範囲名が定義されているか知りたい
時はそのシートを「ズーム」で39%以下にすると
範囲と名前が表示されます。
以前のズーム率に戻す時は「元に戻す」が便利です。
範囲名と似た機能として他にラベルがあります。
Microsoftの説明では「Excelで自然言語数式を使用する」として
「自然言語数式とは、参照名として行または列ラベルを使って、テーブルのセル範囲を参照できる
メソッドです。」(文書番号279412)と言っています。
ラベルを設定すると、表の縦横検索を表自体の見出名で行えます。
当然ながら表の見出しが存在する区間内に参照先も在るときのみ指定できます。
たとえば列見出しが"C3:H3で行見出し"B4:B9で参照先が"J11"のときラベルては参照できません。
少なくとも行、列の一方に含まれる必要があります。
その際含まれていない方の指定は参照元と同じと解釈されます。
このように制約が多い割りに機能が単一で実用上、あまりメリットは有りません。
ラベルはそのシート内のみ有効で、前出のとおり使用範囲が限られています。
名前が定義される訳では無いので、名前ボックスラベルは有りません。
コマンド「ツール」「オプション」でダイアログの「計算方法」タブ内の 「数式でラベルを使用する」にチェックを入れると使えるように成ります。
たとえば列見出しが"C3:H3、行見出し"B4:B9の時。
コマンド「挿入」「名前」「ラベル」で「ラベル範囲」ダイアログを表示
オプションボタン「列ラベル」ON
「ラベル範囲追加」に範囲"C3:H3"選択「追加」
オプションボタン「行ラベル」ON
「ラベル範囲追加」に範囲"B4:B9"選択「追加」「ＯＫ」とする。
下図は検索関数の使用例で、多様な方法（非実用的な物を含む）で表を引いています。一つの問題に対し複数の解決法を知っておくことは重要なことです。此処ではどんな状況下でどの関数 (個々の詳細はヘルプを参照下さい。) を使うかが主題です。
この表はこの場でダウンロード出来ます。又ユーザー定義関数マクロを含んでいます。
ご自分のExcellでお確かめ下さい。
ダウンロード expTB.xls(44kB)
表には規定値として「セルの書式設定」で背景色を「パターン」「色なし」としておく。
表のデータ範囲C4:H9を選択してコマンド「書式」「条件付き書式」でダイアログを表示させ
下図の様に条件と書式を設定して「ＯＫ」とする。見出部で、
行は範囲B4:B9を選択(列は範囲C3:H3を選択)、条件を「セルの値が」「次の値に等しい」「=$C$11」(列は「=$F$11」)とし書式を設定して「ＯＫ」とする。
下記に例題を示します。
aX+bY+cZ=dの未知数XYZを求めるには、３個の式の係数abcと右辺定数dを抜き出して記入します。
係数は要素3X3の正方行列、右辺定数要素3のベクトルです。これをソルバーと行列関数で解きます
複数の代入セルに計算機が適当な数値を次々代入し、目的セル（代入セルを参照する数式が入力されている。）
が、最大、最小或いは、特定の目標値に成るまで続けます。コンピュータならではの手当たりしだいの力技です。
ここでのポイントは目的セルが１つという事です。本来の目的セル=右辺合計の目標値70としてもよいのですが
両者の比が1とすれば目標値は常に固定できます。
係数の逆行列に定数ベクトルを右乗して求めます。ただし行列要素の個数に制限があります。
この表はこの場でダウンロード出来ます。又ユーザー定義関数マクロ
を含んでいます。 ご自分のExcellでお確かめ下さい。
ダウンロード expTB.xls(44kB)
ソルバーを使うには、予めコマンド「ツール」「アドイン」「ソルバーアドイン」にチェックを入れてインストールして下さい。
準備が出来たら、コマンド「ソルバー」で「パラメータの設定」ダイアログを表示。
「EXtips06 多元連立一次方程式を解く」の例題でソルバーを使って解くは下図の様に設定します。
一度使用すると自動でブックに保存するようです。幾つも設定を変えて使用するなら、シートの範囲に保存できます。 上図ダイアログのオプションでモデルの読み込みモデルの保存が可能です。
図面の情報を活用するには、その図面自体がデータの再利用を加味した作図である必要があります。 たとえば１本の線にしてもそれが仕上げなのか躯体なのか、人ではなく計算機がわかるように 整理して描くことです。つまり後で区別が付くようなルールに則った独自の属性を線に与えます。 本来の属性であるレイヤー、線種、線色、線幅などはこのために有ります。さてこの様に整理された ＣＡＤデータから読み取れる情報としては基本的な座標や数量関係、仕様関係、さらにはメーカーや事務所情報 まで活用できます。ただしこのままではＣＡＤデータで終わってしまいます。もしこのデータがＥｘｃｅｌ内で 整理した状態で存在するなら、その利用形態はずいぶん広がるはずです。立派なデータベースにもなりえます。
ここで紹介するのは「JWW_cad」と「JW-ExList.Pro」との組み合わせです。JWは言わずと知れたフリーCADで、JW-ExList.Proは「道具屋」 のJW-ExcelList相互データ変換フリーソフトです。JWW_cadには座標ファイルと言うTXTファイル出力コマンドが有り これをJW-ExList.Proが分類整理しリスト展開します。JW-ExList.Pro自体がExcelのアプリケーションですから 取り込んだデータをユーザーは手軽に扱え、しかもExcelの機能をフルに活用すれば分類摘出加工等思いのままです。 又、双方向の変換ですからExcelの計算結果をＣＡＤデータに変換して出力とすることもできます。