「StiLL」デザイン情報306 開発 -- レイアウト変更に強い仕組みの考え方

【テーマ】
過去に作成した画面レイアウトの変更を行うと、画面のセルと直結している処理も見直しとなります。
今回、この変更による影響を少なくする構築法についてご紹介します。

【方法】
画面シートの入出力項目のセル参照式を作業シートに1行配置して、FORMULATEXT関数で参照式からセル番地を取り出します。このセル番地を結合して（複数ｾﾙ値ｾｯﾄ「BtSetMultiCell」）ボタンで使用することでレイアウト変更の影響を少なくします。

【参考】
FORMULATEXT関数は他のシートの行/列の挿入や削除で再計算されます。
BtSetMultiCell は、セルの値をそのまま転記するので参照式のように空欄が 0 となることはありません。

■ 今回の内容

画面レイアウトが変更された場合の影響を少なくする処理の構築法についてご紹介いたします。

　　　　当初の画面レイアウトが変更されたイメージを示します。

　最初のレイアウトイメージ

変更後のレイアウトイメージ

・ポイント

　１．画面シートの入出力項目の参照式を作業シートに並べて設定。　・・・参照式は行挿入等のレイアウト変更時に自動追従

　２．FORMULATEXT関数で参照式からセル番地を取り出す。

　３．取り出したセル番地を TEXTJOIN、または S_Connect関数で結合させる。

　４．結合させたセル番地を（複数ｾﾙ値ｾｯﾄ「BtSetMultiCell」）ボタンの入出力で使用する。

■ 参照式の設定とセル番地の取出しのイメージ

　本例の説明では、StiLLのシステムテンプレートのワークシート「DLDATA」を使用しております。

（StiLLタブ-システムテンプレート- 開発用シートタブのExcelﾃﾞｰﾀ取得）

　【最初のレイアウト】に対応した参照式を 11行目に並べて入力した状態を示します。（セルE12はセル番地を取り出す式）

メーカー名

単価

原価

備考

↓

　上図の数式で実際に表示される内容を示します。（冒頭の最初のレイアウトイメージを参照）

　セルE12 数式の説明

　１．FORMULATEXT関数で11行目 E11:K11 の範囲の参照式を取り出します。

　２．MID関数で上記１の参照式から、=画面! の４文字分を除外してセル番地のみにします。

　　　　・5文字以降の4文字分を取り出すことで、結合セルを含む画面範囲（ZZ99迄）に対応しています。

　　　　なお、ここでの数式は Microsoft 365（サブスクリプション版）、Excel 2021以降のスピル機能を利用しています。

　　　　上記以外の Excelをご使用の場合は、E12 から K12 の各セルにセル番地を取り出す数式を入力してください。

　　　　例）E12=MID(FORMULATEXT(E11),5,4)

　　　　 F12=MID(FORMULATEXT(F11),5,4)

　　　　　　　・・・・・・・・・・

　【変更後のレイアウト】に対応した参照式を示します。

　セルE12 数式の補足

　FORMULATEXT関数の列範囲を M11 まで広げています。

　・最初のレイアウト時に、あらかじめ予備分として列範囲を仮にP列まで広げておくことでこの式の変更も不要とすることが出来ます。

　　なお参照式が空欄だとエラーになるので =MID(FORMULATEXT(E11:.P11),5,4) のように "." を入れてスピル参照としておきます。

　　Microsoft 365、Excel 2021 以外の場合は個々のセルで、=IFERROR(MID(FORMULATEXT(E11),5,4),"") とします。

　セルE12の式で得られたセル番地を、TEXTJOIN関数、または、S_Connect関数（StiLLで使用可）で結合させます。

　本例ではこの数式を「DLDATA」のセルC12 に入力します。

　　・TEXTJOIN関数の式：C12="画面!"&TEXTJOIN(",",TRUE,E12:P12)　・・・　P列に合わせています

　　・S_Connect関数の式：C12="画面!"&S_Connect(E12:P12,",")

　この結合結果を複数セル値セット「BtSetMultiCell」で使用します。

■ ボタンの作成と設定

今回使用するボタンを示します。（リボン - StiLL - ボタンテンプレート）

今回のボタンの設定例を以下に示します。

１．検索時（表示）：データベース等から取得した各項目内容を画面のセルに表示するときのボタン設定

　　　複数セル値セット「BtSetMultiCell」の設定例

２．更新時（入力）：画面で入力されたセルの内容をデータベース等の入力位置にセットするボタン設定

　　　複数セル値セット「BtSetMultiCell」の設定例

　　　　　データ入出力イメージ（更新・検索共用）

　※今回の方法では、入出力処理を同一画面で行えるので、レイアウト変更時も１つの画面の変更で済みます。

■ ボタンの実行

1．（検索）ボタン・・・「ボタン連続実行（BtPush）」の設定

　　　　データを条件抽出するボタンの次に、上段の「１．検索時（表示）」ボタンをリストします。

　　　　・以下のバックナンバーも参照してください。

【Excelメールサービスバックナンバー304】「データを取込みフォームに表示する」

2．（更新）ボタン・・・「ボタン連続実行（BtPush）」の設定

　　　　上段の「２．更新時（入力）」ボタンを最初にリストし、次にデータを更新するボタンをリストします。

　　　　・以下のバックナンバーも参照してください。

【Excelメールサービスバックナンバー302】「入力フォームで入力したデータをCSVファイルに出力する」

・必要に応じてメッセージボタンを追加します。

■ ご参考までに

1. FORMULATEXT関数は他のシートの行/列の挿入や削除で再計算されます。（準揮発性関数）

　　そこで、以下の数式をセル値セット「BtSetValue」ボタンに設定してセル番地を値化します。

==IFERROR(MID(FORMULATEXT(E11),5,4),"")

　　「BtSetValue」ボタンの設定例

　　このボタンを「STILLAUTO」の（STILLOPEN）ボタンにリストすることで初期起動時にセル番地が値化され再計算を防げます。

2. BtSetMultiCell は、セルの値をそのまま転記するので参照式のように空欄が 0 となることはありません。

　　従ってデータ型に応じて IF文等で参照式を加工する必要もなく、一律に単純な式の設定で済みます。

3. 複数セル値セット「BtSetMultiCell」の設定範囲について

　　上段、(今回のボタンの設定例)で固定範囲で設定しているデータ取得範囲（DLDATA!E15:M15）を変数で指定出来ます。

　　例えばセルC11 に次の式を入力します。

　　　C11="DLDATA!E15:"&ADDRESS(15,COUNTA(E12:P12)+4,4,1)

　　　これは12行目に展開したE列からP列までの予備を含む範囲でセル番地の個数をカウントして有効範囲を求める式です。

　　　この結果は上段の変更後の DLDATA!E15:M15 と一致し、今後P列まで項目が追加された場合も自動追従します。

　　　検索時（表示）ボタンの設定例　　　・・・　更新時（入力）ボタンも同様に設定

　　　この設定で、予備を含めたP列まで項目が追加されてもこのボタンを変更する必要はありません。

（各ボタンの設定、ODBCの詳細はStiLLヘルプをご確認ください）