dbSheetClient　開発版

Transcription

1 ディービーシート クライアント 開 発 版 Excel-SQL 活 用 マニュアル Ver 年 11 月

2 法 的 な 注 意 事 項 本 書 に 記 載 した 内 容 は 将 来 予 告 なしに 変 更 することがあります 本 ドキュメントの 作 成 にあたっては 細 心 の 注 意 を 払 っていますが 本 書 マニュアルの 記 載 事 項 の 誤 り または 欠 落 により 発 生 した 損 害 については 責 任 を 負 いかねますのでご 了 承 ください 本 書 は 株 式 会 社 ニューコムが 事 前 に 承 諾 している 場 合 を 除 き 製 品 の 使 用 許 諾 契 約 書 に 記 載 の 条 件 に 同 意 をされたライセ ンス 所 有 者 に 対 してのみ 供 給 されるものです 著 作 権 表 示 本 書 に 関 する 著 作 権 は 株 式 会 社 ニューコムへ 独 占 的 に 帰 属 します 株 式 会 社 ニューコムが 事 前 に 承 諾 している 場 合 を 除 き 形 態 および 手 段 を 問 わず 本 書 またはその 一 部 を 無 断 で 複 製 転 載 改 編 することを 禁 止 します 商 標 表 示 本 書 に 記 載 されている dbsheetclient 実 行 版 dbsheetclient 開 発 版 dbsheetclientサーバ 版 は 株 式 会 社 ニ ューコムの 製 品 です 本 書 に 記 載 されているマイクロソフト 製 品 (Windows Office Excel 等 )は 米 国 Microsoft Corporationの 米 国 および その 他 の 国 における 登 録 商 標 または 商 標 です その 他 記 載 の 各 社 の 社 名 製 品 名 およびサービス 名 は 各 社 の 商 標 または 登 録 商 標 です 2

3 はじめに 本 マニュアルは dbsheetclient の 動 作 環 境 や 内 部 処 理 データベースアクセス 制 御 方 法 などの 技 術 情 報 について 記 載 しています よって dbsheetclient で Web アプリケーション 開 発 に 必 要 な Excel の 関 数 と SQL 文 のスキルを 持 っている 人 を 前 提 にしています dbsheetclient で Web アプリケーションを 開 発 するのに 3 つのビジネスロジック(Excel シート 編 集 SQL 文 作 成 dbsheetclient の 定 義 編 集 )を 組 み 合 わせて 行 います その 3 つをどのように 組 み 合 わせたらいいのか? の 理 解 を 手 助 けするために 本 マニュアルを 作 成 しました 第 1 章 第 1 章 Excel とデータベースの 関 係 を 理 解 する Excel とデータベースの 苦 手 を 理 解 するための 説 明 をします 第 2 章 データ 量 に 応 じて Excel シートのフォームを 同 期 する 方 法 照 会 系 リスト 形 式 のフォームをデータ 量 に 応 じたフォームにする 方 法 を 説 明 します 付 録 改 版 履 歴 を 記 載 します 3

4 目 次 はじめに... 3 第 1 章 Excel とデータベースの 関 係 を 理 解 する Excel の 特 徴 複 雑 な 表 は 苦 手 Excel の 文 字 入 力 を 制 限 制 御 する データベースの 特 徴 データベースの 情 報 は 足 し 引 き データベースの 機 能 と Excel の 機 能 のどちらで 実 現 するか? 第 2 章 データ 量 に 応 じて Excel シートのフォームを 同 期 する 方 法 フォーム 内 の 行 数 が 固 定 の 場 合 フォーム 内 の 行 数 が 固 定 の 場 合 (データ 切 替 あり) Excel 内 の 制 御 用 セルの 定 義 定 義 シートの 定 義 行 数 が 可 変 の 場 合 Excel 内 の 制 御 用 セルの 定 義 定 義 シートの 定 義 一 覧 表 示 フォームからカード 形 式 のフォームを 表 示 する Excel 内 のセルの 定 義 定 義 シートの 定 義 付 録 A. 改 版 履 歴

5 第 1 章 Excel とデータベースの 関 係 を 理 解 する dbsheetclient は Excel とデータベースを 活 用 し ビジネス 活 動 を 高 めるポテンシャルを 持 ってい ます そのためには Excel とデータベースの 関 係 をよく 理 解 しておかなければなりません 本 章 では Excel とデータベースの 関 係 を 理 解 し 活 用 するためのポイントをまとめています 1.1 Excel の 特 徴 Excel などの 表 計 算 ソフトは ドキュメント 編 集 ソフトの 1 つといえます その 中 で 表 形 式 に 特 化 し さらに 強 力 な 計 算 機 能 を 内 蔵 していることで 個 人 や 家 庭 から 企 業 などに 至 るまで さまざまな 分 野 で 活 用 され 今 ではなくてはならないソフトになってきました そしてその 特 徴 を 正 しく 理 解 していなくては その 機 能 を 十 分 に 発 揮 させることができません 複 雑 な 表 は 苦 手 Word や html 編 集 は 表 の 中 に 表 の 組 込 みや 列 行 の 幅 が 違 う 表 を 作 成 することが 可 能 ですが Excel はそのようなことができません そこで 行 や 列 を 組 み 合 わせていろいろな 表 を 作 成 します 表 1 表 2 セルの 連 結 を 利 用 して 列 幅 の 違 う 表 をシート 内 に 作 成 します 表 の 固 定 部 分 は 画 面 の 上 部 に 配 置 C し 可 変 部 分 を 下 部 にすることで 視 覚 性 の 良 い 表 や 表 の 上 書 きを 防 止 できます A B Aは 下 の 表 が セルの 連 結 をしています Bは 上 の 表 が セルの 連 結 をしています Cの 上 段 は 表 の 合 計 部 です 下 の 表 は 明 細 行 が 可 変 のため 最 終 行 に 合 計 部 を 表 示 すると 視 覚 性 が 悪 いため 表 の 上 に 合 計 部 を 配 置 しています 5

6 1.1.2 Excel の 文 字 入 力 を 制 限 制 御 する Excel の 入 力 は 基 本 的 に 入 力 や 配 置 は 自 由 です そこで 項 目 のタイトルを 明 記 し 列 あるいは 行 に 同 じ 入 力 制 限 を 設 定 することで 入 力 可 能 な 文 字 を 制 限 し 業 務 で 使 用 可 能 なフォームを 作 成 します また データベースの 情 報 と 同 期 する 必 要 のあるデータを Excel 内 で 取 り 扱 う 場 合 以 下 の 注 意 が 必 要 です 1 運 用 時 はシート 保 護 を 設 定 し 行 列 番 号 を 表 示 しません エンドユーザが 通 常 の Excel シートと 同 じと 思 い 込 み 行 や 列 の 編 集 をするのを 防 止 するためです シート 保 護 を 設 定 済 ならば シートの 編 集 が 丌 可 能 にできますが 逆 に 編 集 できない というク レームになる 場 合 もあります これは 通 常 の Excel シートに 見 えることによる 誤 解 が 生 じたため です つまり 誤 解 を 防 止 するために 編 集 や 表 示 する Excel シートは 行 列 番 号 の 非 表 示 やセル 書 式 設 定 の 保 護 (ロック)とシート 保 護 を 組 み 合 わせて 許 可 したセル 以 外 の 入 力 を 禁 止 します オプション 設 定 を 変 更 します 1 枠 線 を 非 表 示 にする 2 行 列 番 号 を 非 表 示 にする シート 保 護 を 設 定 します セルの 保 護 (ロック)を 解 除 してから シートの 保 護 を 設 定 し ます 2 シート 上 にあるデータの 行 を 削 除 してもデータベースのデータは 削 除 されません エンドユーザは Excel の 操 作 と 思 っていると シート 内 のデータをクリアしたり データのある 行 を 削 除 したりすれば データは 消 えていると 思 います しかし データベースと 連 携 している 場 合 は シートを 変 更 しても データベース 上 のデータを 削 除 しない 限 り 削 除 されませんので 再 度 データを 読 込 んだ 時 消 したのに 削 除 されていない という 問 合 せが 必 ず 来 るようになります シート 上 のデータを 削 除 する 場 合 は シートに 削 除 のマークを 付 けるなどして データベースにど のデータを 削 除 するのかを 知 らせる 必 要 があります 編 集 用 シートで 設 定 する 例 行 のセルに 入 力 書 式 で 入 力 規 則 を 設 定 します 6

7 削 除 列 : 編 集 用 シートを 参 照 する 数 式 を 定 義 lします DBS_STATUS 列 : 削 除 を 選 択 した 場 合 は ステータスを 99 にする 数 式 を 定 義 します 3 入 力 規 則 と 関 数 を 活 用 し 入 力 許 可 する 範 囲 の 値 以 外 は 許 可 しないようにします 日 本 語 入 力 の 制 御 による 制 限 英 数 字 : 無 効 を 推 奨 します この 設 定 により 日 本 語 入 力 に 切 り 替 わることがありません それ 以 外 は 入 力 する 値 の 初 期 モードを 指 定 します セルへの 入 力 制 限 文 字 の 種 類 データの 判 定 ( 直 接 の 値 またはセルを 指 定 ) 数 値 整 数 小 数 点 数 1 次 の 値 の 間 2 次 の 値 の 間 以 外 3 次 の 値 に 等 しい 日 付 日 付 4 次 の 値 に 等 しくない 5 次 の 値 より 大 きい 時 刻 時 刻 6 次 の 値 より 小 さい 文 字 文 字 列 数 7 次 の 値 以 上 8 次 の 値 以 下 リスト リスト リスト 表 示 する 値 をカンマ 区 切 りで 指 定 します ユーザー 設 定 ユーザー 設 定 ( 論 理 型 ) 数 式 を 指 定 します 詳 細 は Excel のヘルプを 参 照 してください 7

8 ユーザー 設 定 の 数 式 例 種 類 数 式 結 果 数 値 =ISNUMBER(A4) 数 値 ならば TRUE 文 字 =ISTEXT(A4) 文 字 ならば TRUE 文 字 以 外 =ISNOTTEXT(A4) 文 字 以 外 ならば TRUE エラーが 含 まれる =ISERROR(A4) エラーならば TRUE 論 理 値 =ISLOGICAL(A4) 論 理 値 ならば TRUE 全 角 =LEN(A4)*2=LENB(A4) 全 角 ならば TRUE 半 角 =LEN(A4)=LENB(A4) 半 角 ならば TRUE 日 付 時 刻 =OR(ISERROR(DATEVALUE(TEXT(A4," yyyy/mm/dd"))),cell("format",a4)<>"d1") =FALSE =OR(ISERROR(TIMEVALUE(TEXT(A4," hh:mm:ss"))),cell("format",a4)<>"d9") =FALSE 日 付 ならば TRUE 時 刻 ならば TRUE 必 須 入 力 =OR(NOT(ISBLANK(A4)),A4<> ) 空 白 以 外 ならば TRUE 4 データの 更 新 や 印 刷 する 場 合 処 理 の 前 に 必 ず 3のチェックを 行 い 許 可 された 場 合 のみ 処 理 を 継 続 するようにします セル:A4 の 入 力 規 則 セルの 数 式 数 式 欄 は セル:A6 を 指 定 し A6は 上 記 の 数 式 を 設 定 します 結 果 がFALSEならば 数 値 以 外 を 入 力 するとエラーになります ただし メモ 帳 などから 文 字 列 をコピー&ペーストしてもエラーにはなりません ボタンを 実 行 時 エラーチェックをするタスク 定 義 の 例 タスクタイプ タスクタイプ 属 性 汎 用 パラメータ1 開 始 判 定 セル 318:メッセージ 表 示 エラー 判 定 数 値 を 入 力 してください A8 406: 印 刷 プレビュー タスクタイプ 318:メッセージ 表 示 の エラー 判 定 はエラーがあれば 指 定 メッセージを 表 示 して 処 理 を 中 断 します 開 始 判 定 セルに A8 を 指 定 し その 値 が 0 の 場 合 は 正 常 で 1 の 場 合 はエラーになります ただし メモ 帳 などから 文 字 列 をコピー&ペーストした 場 合 でも 処 理 の 実 行 時 にエラーになります 8

9 5 重 複 データの 登 録 防 止 は データベースのテーブル 定 義 でプライマリキー(ユニークな 値 持 つ 1 つ または 複 数 のフィールドで 構 成 された 情 報 )を 必 ず 定 義 します ここでは 3 つの 重 複 チェックの 方 法 を 説 明 します 1)Excel シートの 列 にプライマリキーの 値 を 含 め データベースの 更 新 でエラーにする 方 法 データベースの 社 員 マスタの 定 義 内 容 社 員 コードがプライマリキーです Excelシート 定 義 内 容 削 除 社 員 コード 社 員 名 社 員 名 カナ 性 別 生 年 月 日 郵 便 番 号 住 所 1 住 所 2 TEL FAX 更 新 のタスク 定 義 ( タスクタイプ リファレンス マニュアル :SQL( 更 新 ) 使 用 例 を 参 照 )をし 重 複 エラーの 場 合 は 以 下 のエラーメッセージが 表 示 されます 実 行 版 の 標 準 エラーメッセージ 画 面 2)Excel の 関 数 を 使 用 して 重 複 をチェックする 方 法 A B C D E 1 チェック 結 果 2 1 =IF(SUM(A4:A10)=0,0,1) 3 社 員 コード 社 員 名 社 員 名 カナ 重 複 check 4 1 社 員 A シャインA 1 =IF(COUNTIF($A$4:$A$10,$A4)>1,1,"") 5 2 社 員 B シャインB 6 3 社 員 C シャインC 7 4 社 員 D シャインD 8 5 社 員 E シャインE 9 1 社 員 A シャインA 1 10 =IF(COUNTIF($A$4:$A$10,$A10)>1,1,"") データのプライマリキーになる 列 で A4~A10が 重 複 チェックをするデータ 範 囲 です また 重 複 があったセルがわかるように 条 件 付 き 書 式 を 設 定 しています 2 重 複 チェック(E4~E10 列 )の 数 式 です 1の 範 囲 に 自 分 と 同 じ 値 が2 件 以 上 あれば 重 複 エラー(1)にします 3 重 複 があったかをdbSheetClientが 判 断 するためのセルと 数 式 です 2を 集 計 し 0 ならば 正 常 それ 以 外 の 場 合 は 重 複 エラー(1)にします 9

10 タスクタイプ タスクタイプ 属 性 汎 用 パラメータ1 開 始 判 定 セル 318:メッセージ 表 示 エラー 判 定 社 員 コードが 重 複 しています E2 201:SQL 更 新 タスクタイプ 318:メッセージ 表 示 の エラー 判 定 はエラーがあれば 指 定 メッセージを 表 示 して 処 理 を 中 断 します 開 始 判 定 セルに E2 を 指 定 し その 値 が 0 の 場 合 は 正 常 で 1 の 場 合 はエラーになります 3)データベース 側 にデータを 送 信 して 重 複 チェックする 方 法 データベース 側 でデータのチェックをするためには Excel シートのデータを 作 業 用 テーブル に 更 新 する 必 要 があります そのテーブルのデータに 重 複 データが 含 まれているかをチェック する SQL 文 を 発 行 し 結 果 を 取 得 することによって 重 複 の 有 無 を 判 断 します 社 員 マスタ 以 外 に 作 業 用 のテーブルをデータベースに 定 義 しておきます このテーブルはプラ イマリキーになるシーケンス No と 元 のテーブルの 中 から 必 要 な 項 目 のみを 定 義 したテーブ ルになります TEMP_ 社 員 マスタの 定 義 シーケンスNO(プライマリキー) 元 のテーブル( 社 員 マスタ)より チェックに 必 要 な 項 目 のみ 定 義 社 員 マスタ 更 新 用 の シ ー トと 別 に 作 業 用 テ ー ブ ル の 更 新 用 シ ー ト を 作 成 し ま す A B C D E F 1 2 更 新 件 数 =COUNTIF($A:$A,">0") 5 NO 社 員 コード 社 員 名 社 員 名 カナ (データ 件 数 を 求 める 数 式 ) 社 員 A シャインA 社 員 B シャインB 重 複 チェック 結 果 社 員 A シャインA 9 10 ( 元 データを 参 照 する 数 式 ) =IF( 社 員 マスタ!E6="","", 社 員 マスタ!E6) =IF( 社 員 マスタ!D6="","", 社 員 マスタ!D6) =IF( 社 員 マスタ!C6="","", 社 員 マスタ!C6) =IF($A6<>"",ROW()-CELL("row",$A$6)+1,"") (シーケンスNOを 振 る 数 式 ) タスク 定 義 クエリー 定 義 展 開 定 義 を 設 定 します タスク 定 義 クエリー 定 義 展 開 定 義 タスクタイプ タスクタイプ 属 性 汎 用 パラメータ20 SQL 文 開 始 判 定 セル 201:SQL SQL 更 新 TRUNCATE [TEMP_ 社 員 マスタ] 201:SQL 更 新 UPDATE 201:SQL 照 会 シート 後 再 計 算 SELECT CASE WHEN COUNT(x.[ 社 員 コード]) > 0 THEN 1 ELSE 0 END AS [ 結 果 ] FROM (SELECT DISTINCT [ 社 員 コード], COUNT([ 社 員 コード]) AS [ 重 複 数 ] FROM [TEMP_ 社 員 マスタ] GROUP BY [ 社 員 コード]) AS x WHERE x.[ 重 複 数 ] > 1 F8 10

11 1 行 目 の SQL 文 : 作 業 用 のテーブル 内 のデータをすべて 削 除 します 2 行 目 の SQL 文 :dbsheetclient の 更 新 処 理 のための 定 義 3 行 目 の SQL 文 :サブクエリー 側 : 社 員 コード 毎 のデータ 件 数 を 取 得 する 定 義 です メイン 側 クエリー:サブクエリーの 結 果 で 重 複 数 が 1 より 大 きいものが あれば 結 果 として 1 ( 重 複 あり)を 返 す 定 義 です 1.2 データベースの 特 徴 情 報 を 目 的 などに 応 じて 分 類 集 約 し 格 納 するためのものです 格 納 された 情 報 は 共 有 化 できますの で データの 一 元 管 理 が 可 能 になります 一 般 的 にデータベースは 情 報 の 正 規 化 が 行 われ 目 的 ごとのテーブルに 情 報 を 格 納 します また 検 索 を 高 速 にするために プライマリキー(ユニークな 値 を 持 つ 基 準 となるインデックス)を 作 成 します データの 取 得 は 単 一 テーブルから 取 得 する 場 合 と Join やサブクエリーなどを 用 いて 複 数 のテー ブルから 取 得 する 場 合 などがあります データの 取 得 時 に 毎 回 全 件 検 索 すると 処 理 が 遅 くなるために プライマリキーや 他 のインデックスを 定 義 して 絞 り 込 みを 行 うようにします 11

12 1.3 データベースの 情 報 は 足 し 引 き データベースに 格 納 されている 情 報 は 目 的 に 応 じて 作 成 されます 情 報 の 捉 え 方 を 別 の 角 度 から 見 る ようにすると 必 ず 過 丌 足 が 発 生 します 例 えば 1か 月 の 売 上 の 推 移 を 求 める 場 合 売 上 データが 毎 日 必 ずあるならば 売 上 データを1 日 単 位 で 集 計 し1か 月 分 のデータ 集 めて 求 めます ただし 売 上 が 毎 日 発 生 しないことが 考 えられますので 毎 日 のデータを 集 計 することができません このような 場 合 は 日 付 を 補 完 する 情 報 が 必 要 になります よって 集 計 時 に 一 次 的 に1か 月 の 日 付 データ を 生 成 し その 日 付 データに 日 ごとに 集 計 した 売 上 データを 紐 付 けるような SQL 文 を 作 成 します さらに 営 業 日 ベースで 集 計 する 場 合 は 営 業 日 や 休 日 を 管 理 するカレンダーマスターの 情 報 が 必 要 に なります また イベントやキャンペーンの 情 報 を 表 示 する 場 合 は それらを 管 理 するテーブルから 情 報 を 紐 付 ける 必 要 があります このように 情 報 の 特 性 を 理 解 し 1 つのデータで 情 報 丌 足 が 発 生 する 場 合 は 丌 足 分 の 一 時 的 な 生 成 や 他 のデータを 参 照 して 補 うようにします 弁 当 予 約 システムの 年 間 支 払 一 覧 を 表 示 するための SQL 文 dbsheetclient が Excel シートから 情 報 を 受 けるパラメータ 12か 月 の 月 初 日 データを 生 成 します 変 数 テーブルに 年 間 の 年 月 データを 作 成 し そのデータに 業 者 支 払 伝 票 データを 紐 付 けして 表 示 す るための SQL 分 です SELECT 文 は 生 成 した12か 月 情 報 をメインテーブルとして 月 ごとに 集 計 した 注 文 金 額 を 予 定 として 表 示 し 支 払 伝 票 の 金 額 を 実 績 として 表 示 するための 定 義 です 実 行 結 果 は 複 雑 な 表 は 苦 手 の 図 を 参 照 12

13 1.4 データベースの 機 能 と Excel の 機 能 のどちらで 実 現 するか? データベース 側 の 機 能 と Excel の 機 能 で 重 複 しているもの( 例 えば 合 計 を 求 める 関 数 )があります これらを 使 い 分 けるには 社 内 にある 開 発 標 準 化 のためのルールに 則 って 開 発 を 進 めると 思 います 開 発 標 準 化 のためのルールの 例 を 下 記 に 記 載 します 保 守 メンテナンス 変 更 や 追 加 が 容 易 な 方 を 選 択 パフォーマンス スピードの 速 い 方 を 選 択 ユーザー インターフェース 作 業 効 率 良 くなる 方 を 選 択 思 考 の 妨 げになら ない 方 を 選 択 13

14 第 2 章 データ 量 に 応 じて Excel シートのフォームを 同 期 する 方 法 データベースからデータを 取 得 してリスト 方 式 で 展 開 する 場 合 dbsheetclient の 機 能 として 展 開 先 シートのデータをクリアしてから 取 得 データを 指 定 シートに 展 開 します ( 1) このとき Excel シートのフォームを 含 めた 展 開 をしませんので データ 展 開 と 連 動 するようなタスク 定 義 をする 必 要 があります 1. 対 応 するタスク 定 義 のタスクタイプ 201:SQL( 照 会 SQL 更 新 ) 230: 集 計 表 自 動 作 成 203:データ 照 会 画 面 405: 一 括 入 出 力 (CSV 入 力 ) 204: 検 索 付 きデータ 照 会 取 得 したデータの 量 は データを 取 得 するごとに 変 わる 可 能 性 があります そのため データのクリア 後 にデータを 展 開 する 方 法 を 提 供 しています しかしデータの 展 開 ではフォームの 更 新 を 行 われません A B C D E F 1 削 除 商 品 コード 商 品 名 単 位 単 価 分 類 タスク 定 義 201:SQL( 照 会 ) 204: 検 索 付 き データ 照 会 画 面 : A B C D E F 1 削 除 商 品 コード 商 品 名 単 位 単 価 分 類 2 1 鉛 筆 タ ース 240 JM 3 2 万 年 筆 本 5000 JM 4 3 消 しゴム 個 50 JM 5 4 糊 個 100 JM 6 5 定 規 本 100 JM データを 取 得 するタスクのみの 場 合 データの 展 開 が 実 行 する Select Top 5 [ 商 品 コード],[ 商 品 名 ],[ 単 位 ],[ 単 価 ],[ 分 類 ] From [ 商 品 マスタ] SQL 分 (クエリー 定 義 )の 例 先 頭 5 件 を 取 得 SQL 文 は SQL Server 用 です 2.1 フォーム 内 の 行 数 が 固 定 の 場 合 下 記 図 のように 行 数 が 固 定 の 場 合 は Excel のフォームは 固 定 行 として 作 成 します さらに 奇 数 行 と 偶 数 行 で 背 景 色 を 変 えるような 場 合 は Excel の 条 件 付 き 書 式 を 使 用 して 背 景 色 を 変 えます A B C D E F 1 削 除 商 品 コード 商 品 名 単 位 単 価 分 類 タスク 定 義 201:SQL( 照 会 ) 204: 検 索 付 き データ 照 会 画 面 : A B C D E F 1 削 除 商 品 コード 商 品 名 単 位 単 価 分 類 2 1 鉛 筆 タ ース 240 JM 3 2 万 年 筆 本 5000 JM 4 3 消 しゴム 個 50 JM 5 4 糊 個 100 JM 6 5 定 規 本 100 JM 1 条 件 付 き 書 式 は シート 上 のフォーム( 行 ) 全 体 に 設 定 し その 行 範 囲 をコピーします フォームの 行 数 が 固 定 で 行 ごとに 背 景 色 を 変 えたい 場 合 14

15 2.2 フォーム 内 の 行 数 が 固 定 の 場 合 (データ 切 替 あり) フォーム 内 のデータを 切 り 替 えるには <( 前 ) >( 次 ) などのボタンを 定 義 し そのボタンの クリック 時 に 該 当 するデータを 取 得 する SQL 文 を 発 行 するタスク 定 義 を 行 います そのためには 取 得 するデータにシーケンス を 付 加 し そのシーケンス で 絞 り 込 む SQL 文 を 定 義 します さらにその 制 御 をするための 情 報 を Excel シート 内 に 定 義 する 必 要 があります 現 在 の 表 示 A B C D E F 1 削 除 商 品 コード 商 品 名 単 位 単 価 分 類 2 60 商 品 6 箱 商 品 7 個 商 品 8 箱 商 品 9 箱 商 品 10 箱 先 頭 前 ジャンプ 次 最 終 前 頁 分 を 表 示 A B C D E F 1 削 除 商 品 コード 商 品 名 単 位 単 価 分 類 2 10 商 品 1 個 商 品 2 個 商 品 3 個 商 品 4 箱 商 品 5 箱 次 頁 分 を 表 示 A B C D E F 1 削 除 商 品 コード 商 品 名 単 位 単 価 分 類 商 品 11 箱 商 品 12 箱 商 品 13 箱 商 品 14 箱 商 品 15 箱 ジャンプ 先 からの 表 示 A B C D E F 1 削 除 商 品 コード 商 品 名 単 位 単 価 分 類 商 品 20 箱 商 品 21 箱 商 品 22 箱 商 品 23 箱 商 品 24 箱 各 ボタンのクリック 時 にデータを 取 得 する 例 です 上 記 の 動 作 で 定 義 する SQL 文 (クエリー 定 義 )は 1 つで 可 能 です そのためには Excel 内 に 制 御 用 のセルを 定 義 し また それを 活 用 するためのタスク 定 義 を dbsheetclient に 登 録 します Excel 内 の 制 御 用 セルの 定 義 定 数 行 数 5 開 始 1 終 了 25 変 数 前. 開 始 1 次. 開 始 6 現. 開 始 1 現. 終 了 5 (A) (B) (C) (D) (E) (F) (G) 21 ^Now_Start_No^ (H) 現 在 の 開 始 位 置 (シーケンスNo) 22 ^Now_End_No^ (I) 現 在 の 終 了 位 置 (シーケンスNo) dbsパラメータシート Excel シートの 定 義 例 です 下 記 は 定 義 内 容 の 説 明 です 15

16 項 番 名 称 内 容 (A) 行 数 名 前 定 義 名 : 定 数. 行 数 フォームの 明 細 行 数 を 設 定 します (B) 開 始 名 前 定 義 名 : 定 数. 最 小 開 始 位 置 取 得 データのシーケンス 番 号 の 最 小 値 を 設 定 します (C) 終 了 名 前 定 義 名 : 定 数. 最 大 開 始 位 置 取 得 データのシーケンス 番 号 の 最 大 値 です (メニュークリック 時 にデータ 件 数 を 取 得 します ) (D) 前. 開 始 名 前 定 義 名 : 変 数. 前 開 始 位 置 < ボタンをクリック 時 の 開 始 を 数 式 で 求 めます 数 式 にて (D)を 求 めます =IF( 条 件 A, 式 1 式 2) 条 件 A: 変 数. 現 開 始 位 置 (F) - 定 数. 行 数 (A) < 定 数. 最 小 開 始 位 置 (B) 式 1 : 定 数. 最 小 開 始 位 置 (B) 式 2 : 変 数. 現 開 始 位 置 (F) - 定 数. 行 数 (A) 実 際 の 数 式 =IF( 変 数. 現 開 始 位 置 - 定 数. 行 数 < 定 数. 最 小 開 始 位 置, 定 数. 最 小 開 始 位 置, 変 数. 現 開 始 位 置 - 定 数. 行 数 ) (E) 次. 開 始 名 前 定 義 名 : 変 数. 次 開 始 位 置 > ボタンをクリック 時 の 開 始 を 数 式 で 求 めます 数 式 にて (E)を 求 めます =IF( 条 件 B, 式 3 式 4) 条 件 B: 変 数. 現 開 始 位 置 (F)+ 定 数. 行 数 (A) > 定 数. 最 大 開 始 位 置 (C) 式 3 : 定 数. 最 大 開 始 位 置 (C) 式 4 : 変 数. 現 開 始 位 置 (F) + 定 数. 行 数 (A) 実 際 の 数 式 =IF( 変 数. 現 開 始 位 置 + 定 数. 行 数 > 定 数. 最 大 開 始 位 置, 定 数. 最 大 開 始 位 置, 変 数. 現 開 始 位 置 + 定 数. 行 数 ) (F) 現. 開 始 名 前 定 義 名 : 変 数. 現 開 始 位 置 各 ボタンのクリック 時 に 表 示 しているデータ 範 囲 の 開 始 です (G) 現. 終 了 名 前 定 義 名 : 変 数. 現 終 了 位 置 各 ボタンのクリック 時 に 表 示 しているデータ 範 囲 の 終 了 です (H) ^Now_Start_No^ 名 前 定 義 名 :パラ. 現 開 始 位 置 SQL 文 に 渡 す(F)のパラメータです (I) ^Now_End_No^ 名 前 定 義 名 :パラ. 現 終 了 位 置 SQL 文 に 渡 す(G)のパラメータです 16

17 2.2.2 定 義 シートの 定 義 1 メニュークリック 時 に 起 動 するタスクの 定 義 メニュークリック 時 に 起 動 するタスクは 取 得 データの 件 数 を 取 得 するために SQL( 照 会 ) 実 行 の タスクを 定 義 します ここでは 各 定 義 シートでポイントとなる 項 目 についてまとめています 定 義 シート 名 項 目 内 容 メニュー 定 義 TASKNO 取 得 データ 件 数 の 取 得 用 タスク NO を 指 定 します タスク 定 義 タスクタイプ 201:SQL QNO DNO 照 会 下 記 クエリー 定 義 のクエリー 定 義 NO を 指 定 します 下 記 展 開 定 義 の 展 開 定 義 NO を 指 定 します クエリー 定 義 クエリータイプ 照 会 属 性 SQL 文 Query SELECT COUNT(*) FROM [ 商 品 マスタ] 展 開 定 義 定 義 区 分 クエリー 定 義 タイプ タスク 展 開 属 性 セル 範 囲 セル 展 開 範 囲 定 義 名 照 会 定 数. 最 大 開 始 位 置 定 数. 最 大 開 始 位 置 (C) 2 定 数 各 ボタンクリック 時 の 動 作 仕 様 行 数 5 処 理 開 フローを 始 作 成 し 共 通 化 可 能 なタスクを 1 洗 い 出 します 終 了 25 変 数 前. 開 始 1 次. 開 始 現. 開 始 1 現. 終 了 ^Now_Start_No^ 現 在 の 開 始 位 置 (シーケンスNo) 22 ^Now_End_No^ 現 在 の 終 了 位 置 (シーケンスNo) dbsパラメータシート 開 始 開 始 開 始 タスク 定 義 コピー:1 タスク 定 義 コピー:2 タスク 定 義 入 力 画 面 4 タスク 定 義 コピー:3 タスク 定 義 コピー:3 タスク 定 義 コピー:3 タスク 定 義 SQL( 照 会 ) タスク 定 義 SQL( 照 会 ) タスク 定 義 SQL( 照 会 ) 終 了 終 了 終 了 赤 枠 の 処 理 が 共 通 化 可 能 です なお 現. 開 始 及 び 現. 終 了 とパラメータシートとの 間 をコピーし ないで パラメータシート 側 に 数 式 を 記 述 する 方 法 もあります 今 回 は 各 シートの 独 立 性 を 高 めるようにしています また 共 通 化 という 概 念 をわかりやすくする ために 3つの 処 理 を 記 述 しています 17

18 3 各 ボタンクリック 時 に 起 動 するタスクの 定 義 ボタン 用 の 各 種 定 義 定 義 シート 名 項 目 内 容 ボタン 定 義 ボタン 名 表 示 名 TASKNO をクリック 時 に 動 作 するタスクNOを 指 定 します タスク 定 義 タスクタイプ 410:コピー DNO 値 下 記 展 開 定 義 の 展 開 定 義 NOを 指 定 します 展 開 定 義 定 義 区 分 タスク 定 義 タイプ タスク 展 開 属 性 セル 範 囲 先 セル 範 囲 コピー 定 数. 最 小 開 始 位 置 変 数. 現 開 始 位 置 (F) タスク 定 義 タスクタイプ 320:サブタスク 実 行 TASKJUMP1 なし 共 通 処 理 のタスク NO を 指 定 します < ボタン 用 の 各 種 定 義 定 義 シート 名 項 目 内 容 ボタン 定 義 ボタン 名 表 示 名 < TASKNO < をクリック 時 に 動 作 するタスクNOを 指 定 します タスク 定 義 タスクタイプ 410:コピー DNO 値 下 記 展 開 定 義 の 展 開 定 義 NOを 指 定 します 展 開 定 義 定 義 区 分 タスク 定 義 タイプ タスク 展 開 属 性 セル 範 囲 先 セル 範 囲 コピー 変 数. 前 開 始 位 置 変 数. 現 開 始 位 置 (F) タスク 定 義 タスクタイプ 320:サブタスク 実 行 TASKJUMP1 なし 共 通 処 理 のタスク NO を 指 定 します 18

19 > ボタン 用 の 各 種 定 義 定 義 シート 名 項 目 内 容 ボタン 定 義 ボタン 名 表 示 名 > TASKNO > をクリック 時 に 動 作 するタスクNOを 指 定 します タスク 定 義 タスクタイプ 410:コピー DNO 値 下 記 展 開 定 義 の 展 開 定 義 NOを 指 定 します 展 開 定 義 定 義 区 分 タスク 定 義 タイプ タスク 展 開 属 性 セル 範 囲 先 セル 範 囲 コピー 変 数. 次 開 始 位 置 変 数. 現 開 始 位 置 (F) タスク 定 義 タスクタイプ 320:サブタスク 実 行 TASKJUMP1 なし 共 通 処 理 のタスク NO を 指 定 します ボタン 用 の 各 種 定 義 定 義 シート 名 項 目 内 容 ボタン 定 義 ボタン 名 表 示 名 TASKNO をクリック 時 に 動 作 するタスクNOを 指 定 します タスク 定 義 タスクタイプ 401: 直 接 展 開 DNO なし 下 記 展 開 定 義 の 展 開 定 義 NOを 指 定 します 展 開 定 義 定 義 区 分 タスク 定 義 タイプ 直 接 展 開 数 式 OR 値 = 定 数. 最 大 開 始 位 置 (C)- 定 数. 行 数 (A) +1 セル 範 変 数. 現 開 始 位 置 (F) タスク 定 義 タスクタイプ 320:サブタスク 実 行 TASKJUMP1 なし 共 通 処 理 のタスク NO を 指 定 します 19

20 Jump ボタン 用 の 各 種 定 義 定 義 シート 名 項 目 内 容 ボタン 定 義 ボタン 名 表 示 名 Jump TASKNO Jump をクリック 時 に 動 作 するタスクNOを 指 定 しま す タスク 定 義 タスクタイプ 202: 入 力 画 面 画 面 名 称 汎 用 パラメータ3 汎 用 パラメータ11 数 値 移 動 先 NO 入 力 移 動 先 の 行 を 入 力 変 数. 現 開 始 位 置 (F) タスク 定 義 タスクタイプ 320:サブタスク 実 行 TASKJUMP1 なし 共 通 処 理 のタスク NO を 指 定 します 共 通 処 理 用 の 各 種 定 義 定 義 シート 名 項 目 内 容 タスク 定 義 タスクタイプ 410:コピー DNO 値 下 記 展 開 定 義 の 展 開 定 義 NOを 指 定 します 展 開 定 義 定 義 区 分 タスク 定 義 タイプ タスク 展 開 属 性 セル 範 囲 自 動 計 算 方 法 先 セル 範 囲 コピー 変 数. 現 開 始 位 置 (F) シート 前 再 計 現 開 始 位 置 (H) 展 開 定 義 定 義 区 分 タスク 定 義 タイプ タスク 展 開 属 性 セル 範 囲 コピー 変 数. 現 終 了 位 置 (G) 自 動 計 算 方 法 ( 先 頭 で 1 度 行 っているため 丌 要 ) 先 セル 範 現 終 了 位 置 (I) 次 頁 に 続 く 20

21 前 頁 より 続 く 定 義 シート 名 項 目 内 容 タスク 定 義 タスクタイプ 201:SQL QNO DNO 汎 用 パラメータ20 照 会 下 記 クエリー 定 義 のクエリー 定 義 NO を 指 定 します 下 記 展 開 定 義 の 展 開 定 義 NOを 指 定 します 前 再 計 算 クエリー 定 義 クエリータイプ 照 会 属 性 SQL 文 1 2 Query /* 変 数 定 義 */ int = ^Now_Start_No^ = ^Now_End_No^ Select '' As [ 削 除 ],[ 商 品 コード],[ 商 品 名 ],[ 単 位 ],[ 単 価 ],[ 分 類 ] From ( Select Row_Number() Over (Order By [ 商 品 コー ド]) As [NO],[ 商 品 コード],[ 商 品 名 ],[ 単 位 ],[ 単 価 ],[ 分 類 ] From [ 商 品 マスタ]) As tmp Where [NO] 展 開 定 義 定 義 区 分 クエリー 定 義 タイプ タスク 展 開 属 性 セル 範 囲 セル 展 開 範 囲 名 照 会 表 _ 固 表 _ 固 定 1.サブクエリー 側 で 行 番 号 を 付 加 してデータを 取 得 します Select Row_Number() Over (Order By [ 商 品 コード]) As [NO] 2.メインのクエリー 文 で 行 番 号 の 取 得 範 囲 を 絞 り 込 みます 21

22 2.3 行 数 が 可 変 の 場 合 下 記 の 図 は データベースのデータを Excel に 展 開 した 時 に Excel のフォームを 連 動 させた 場 合 のサ ンプルです そのための 制 御 情 報 を Excel シート 内 に 定 義 する 必 要 があります A B C D E F G 削 除 商 品 コード 商 品 名 単 位 単 価 分 類 データを 取 得 した 結 果 に 応 じて Excel のフォームを 連 動 した 例 です Excel 内 の 制 御 用 セルの 定 義 削 除 商 品 コード 商 品 名 単 位 単 価 201:SQL( 照 会 SQL 更 新 ) 204: 検 索 付 きデータ 照 会 画 面 : 分 類 A B C D E F G 削 除 商 品 コード 商 品 名 単 位 単 価 分 類 6 10 商 品 1 個 商 品 2 個 商 品 3 個 商 品 4 箱 商 品 5 箱 商 品 6 箱 商 品 7 個 商 品 8 箱 商 品 9 箱 商 品 10 箱 商 品 11 箱 商 品 12 箱 商 品 13 箱 商 品 14 箱 商 品 15 箱 商 品 16 箱 商 品 17 箱 商 品 18 箱 商 品 19 箱 商 品 20 箱 商 品 21 箱 商 品 22 箱 商 品 23 箱 商 品 24 箱 商 品 25 箱 (A) (B) (C) (D) (E) (F) (G) 入 力 範 囲 開 始 入 力 範 囲 終 了 入 力 範 囲 空 行 数 $B$6 $G$35 $B$6:$G$35 5 クリア 範 囲 終 了 クリア 範 囲 $G$30 $B$6:$G$30 (H) (I) Excel シートの 定 義 例 です 下 記 は 定 義 内 容 の 説 明 です 項 番 名 称 内 容 (A) 書 式 保 存 範 囲 名 前 定 義 名 : 表. 保 存 書 式 書 式 をコピーするためのコピー 元 領 域 です 次 頁 に 続 く 22

23 前 頁 より 続 く 項 番 名 称 内 容 (B) 展 開 範 囲 名 前 定 義 名 : 表. 展 開 範 囲 取 込 みデータの 展 開 範 囲 または 書 式 をコピーするためのコピー 先 領 域 です データ 展 開 後 この 領 域 名 の 範 囲 は 自 動 更 新 されます 展 開 定 義 で 省 略 した 場 合 の 名 前 定 義 名 は dbs_[シート 名 ] で す 展 開 定 義 で 指 定 する 名 前 定 義 名 は EXCEL シートに 未 登 録 で も 可 能 ですが 数 式 などで 使 用 する 場 合 は 事 前 に 登 録 しておく 必 要 があります (C) 展 開 先 先 頭 セル 名 前 定 義 名 : 表. 展 開 先 頭 取 込 みデータの 展 開 先 領 域 の 先 頭 セル( 領 域 の 左 上 )です 展 開 定 義 の 名 前 定 義 名 を 指 定 可 能 です (D) 入 力 範 囲 開 始 名 前 定 義 名 : 表. 入 力 範 囲 開 始 セル 取 込 みデータの 展 開 先 領 域 やクリア 領 域 の 先 頭 セル( 領 域 の 左 上 ) を 求 めます ( クリア 範 囲 開 始 としての 意 味 もあります) =CELL("Address",( 表. 展 開 範 囲 (B))) (CELL 関 数 で 展 開 範 囲 の 左 上 隅 にあるセル 位 置 を 取 得 します ) 実 際 の 数 式 =CELL("Address",( 表. 展 開 範 囲 )) (E) 入 力 範 囲 終 了 名 前 定 義 名 : 表. 入 力 範 囲 終 了 セル 入 力 フォームの 最 終 セルを 求 めます 展 開 データ 量 に 応 じて 最 終 セ ル 位 置 は 変 化 します =ADDRESS( 行 番 号, 列 番 号 ) 行 番 号 : 展 開 開 始 行 の 行 番 号 + 展 開 データの 行 数 + 空 行 数 -1 列 番 号 : 展 開 開 始 列 の 列 番 号 + 展 開 データの 列 数 1 (ADDRESS 関 数 で 展 開 範 囲 の 右 下 にあるセル 位 置 を 取 得 しま す ) 実 際 の 数 式 =ADDRESS(CELL("ROW", 表. 展 開 範 囲 )+ROWS( 表. 展 開 範 囲 )+ 表. 空 行 数 -1,CELL("col", 表. 展 開 範 囲 )+COLUMNS( 表. 展 開 範 囲 )-1) (F) 入 力 範 囲 名 前 定 義 名 : 表. 入 力 範 囲 Excel フォームの 入 力 範 囲 を 求 めます = 入 力 範 囲 開 始 (D)& : & 入 力 範 囲 終 了 (F) 実 際 の 数 式 = 表. 入 力 範 囲 開 始 セル&":"& 表. 入 力 範 囲 終 了 セル 次 頁 に 続 く 23

24 前 頁 より 続 く 項 番 名 称 内 容 (G) 空 行 数 名 前 定 義 名 : 表. 空 行 数 展 開 データ 以 降 にデータ 入 力 用 の 空 行 を 作 成 するための 行 数 を 指 定 します (H) クリア 範 囲 終 了 名 前 定 義 名 : 表.クリア 範 囲 終 了 セル 入 力 フォームの 最 終 セルを 求 めます 入 力 データ 量 に 応 じて 最 終 セ ル 位 置 は 変 化 します =ADDRESS( 行 番 号, 列 番 号 ) 行 番 号 : 展 開 開 始 行 の 行 番 号 + 更 新 ( 入 力 )データの 行 数 -1 列 番 号 : 展 開 開 始 列 の 列 番 号 + 展 開 データの 列 数 1 (ADDRESS 関 数 で 入 力 範 囲 の 右 下 にあるセル 位 置 を 取 得 しま す ) 実 際 の 数 式 =ADDRESS(CELL("ROW", 表. 展 開 範 囲 )+IF( 更 新 行 数 <1,0, 更 新 行 数 ),CELL(" 表. 展 開 範 囲 )+COLUMNS( 表. 展 開 範 囲 )-1) (I) クリア 範 囲 名 前 定 義 名 : 表.クリア 範 囲 Excel フォームのクリア 範 囲 を 求 めます = 入 力 範 囲 開 始 (D)& : &クリア 範 囲 終 了 (H) 実 際 の 数 式 = 表. 入 力 範 囲 開 始 セル&":"& 表.クリア 範 囲 終 了 セル 定 義 シートの 定 義 処 理 フローを 作 成 します 読 込 ボタンの 処 理 開 始 タスク 定 義 クリア: 書 式 タスク 定 義 SQL( 照 会 ) タスク 定 義 コピー: 書 式 終 了 SQL( 照 会 )の 処 理 で 値 のクリアを 行 ってから 取 得 データの 展 開 を 行 いますので その 前 後 処 理 で 書 式 のクリアとコピーを 行 います 24

25 読 込 ボタン 用 の 各 種 定 義 定 義 シート 名 項 目 内 容 ボタン 定 義 ボタン 名 表 示 名 読 込 TASKNO 読 込 をクリック 時 に 動 作 するタスクNOを 指 定 します タスク 定 義 タスクタイプ 411:クリア DNO 書 式 下 記 展 開 定 義 の 展 開 定 義 NOを 指 定 します 展 開 定 義 定 義 区 分 タスク 定 義 タイプ タスク 展 開 属 性 セル 指 定 属 性 セル 範 囲 クリア 書 式 間 表.クリア 範 囲 タスク 定 義 タスクタイプ 201:SQL クエリー 定 義 QNO DNO 汎 用 パラメータ20 クエリータイプ 属 性 SQL 文 照 会 下 記 クエリー 定 義 のクエリー 定 義 NO を 指 定 します 下 記 展 開 定 義 の 展 開 定 義 NOを 指 定 します 前 再 計 算 照 会 Query Select '' As [ 削 除 ][ 商 品 コード],[ 商 品 名 ],[ 単 位 ],[ 単 価 ],[ 分 類 ] From [ 商 品 マスタ] 展 開 定 義 定 義 区 分 タイプ タスク 展 開 属 性 セル 範 囲 セル 展 開 範 囲 名 クエリー 定 義 照 会 表. 展 開 範 囲 表. 展 開 先 表. 展 開 範 囲 タスク 定 義 タスクタイプ 410:コピー DNO 書 式 コメント 入 力 規 則 列 幅 下 記 展 開 定 義 の 展 開 定 義 NOを 指 定 します 展 開 定 義 定 義 区 分 タスク 定 義 タイプ タスク 展 開 属 性 セル 範 囲 属 性 セル 範 囲 自 動 計 算 方 法 先 セル 範 囲 属 性 先 セル 範 囲 コピー 書 式 コメント 入 力 規 則 列 幅 直 表. 書 式 保 存 (A) シート 前 再 計 算 間 表. 入 力 範 囲 (F) 25

26 SQL 文 は 表 示 する 項 目 の 先 頭 から 展 開 する Select 文 にします また 展 開 定 義 は 表 示 する 項 目 の 先 頭 からのセル 位 置 を 指 定 します 26

27 2.4 一 覧 表 示 フォームからカード 形 式 のフォームを 表 示 する 一 覧 表 示 から 明 細 表 示 に 切 り 替 える 場 合 があります このような 場 合 それぞれをシートのフォームを 作 成 し シートを 相 互 に 切 り 替 えて 表 示 するようにします これは タスクタイプの 420:セル 位 置 取 得 を 使 用 してカーソル 行 の 情 報 を 取 得 し その 情 報 を 元 に 明 細 を 表 示 します をク リック 戻 る を クリック 左 図 の 選 択 列 にある をクリックすると その 行 の 位 置 と 情 報 を 取 得 し 明 細 表 示 の 画 面 に 移 動 します 右 図 の 戻 る ボタンをクリックすると 売 上 一 覧 に 戻 った 時 上 記 の 行 にカーソルが 戻 るようにしま す また その 行 の 背 景 色 を 変 更 し どの 行 を 選 択 したかわかるようにします Excel 内 のセルの 定 義 (A) (B) (C ) 検 索 条 件 伝 票 枚 数 売 上 金 額 ( 円 ) 合 計 売 上 日 2007/01/01 ~ 2009/10/ \5,617,635 (D) (E) 選 択 支 店 CD 支 店 名 伝 票 枚 数 売 上 金 額 ( 円 ) 栄 店 4 \100, 横 浜 市 神 奈 川 区 本 店 8 \215, 戸 塚 店 1 \3, 今 井 南 店 3 \18, 若 松 店 21 \1,181, 静 岡 県 掛 川 市 北 門 支 店 27 \438, 静 岡 県 沼 津 市 西 島 町 支 店 12 \330, 静 岡 県 浜 松 市 寺 島 町 支 店 14 \479, 静 岡 県 富 士 市 平 垣 支 店 3 \12, 静 岡 市 葵 区 鷹 匠 支 店 29 \948, 相 模 大 野 店 26 \914, 沢 渡 店 17 \701, 中 尾 店 4 \166, 藤 沢 店 3 \106,260 1,400 1,200 1,000 単 位 千 円 売 上 金 額 栄 横 戸 今 若 静 静 静 静 静 相 沢 中 藤 店 浜 塚 井 松 岡 岡 岡 岡 岡 模 渡 尾 沢 市 店 南 店 県 県 県 県 市 大 店 店 店 神 奈 川 区 本 店 店 掛 沼 浜 富 葵 野 川 津 松 士 区 店 市 市 市 市 鷹 北 西 寺 平 匠 門 島 島 垣 支 支 町 町 支 店 店 支 支 店 店 店 売 上 一 覧 表 示 用 のセル 定 義 です 表 の 行 数 はグラフ 先 に 作 成 するため 25 行 分 作 成 済 にしてあります (グラフ 作 成 時 はテストデータ を 入 れてグラフのフォームを 作 成 します ) 2.3 行 数 が 可 変 の 場 合 のように フォームを 生 成 することも 可 能 です 27

28 (F) (G) (H ) (I) (J) 位 置.シート 名 位 置. 行 No 位 置. 列 No 戻 りセル デフォルト 位 置 情 報 売 上 集 計 $C$15 売 上 集 計 2 12 (K) (L) (M) 位 置. 支 店 CD 分 岐 式 (1: 有 効 2:エラー) 一 覧 の 開 始 セル $B$12 (N) (O) (P) 当 月 月 初 当 月 末 日 取 得 データ 件 数 2007/01/ /10/31 14 メッセージ 定 義 支 店 CDを 選 択 していません (Q) 23 ^ 売 上 日 FROM^ (R) 2007/01/01 売 上 日 FROM 24 ^ 売 上 日 TO^ (S) 2009/10/31 売 上 日 TO 25 ^ 支 店 CD^ (T) 支 店 CD 項 番 名 称 内 容 (A) 売 上 日 ( 開 始 ) 名 前 定 義 名 : 売 上 集 計 2_ 売 上 日 _FR 売 上 データを 日 付 範 囲 で 絞 り 込 んで 取 得 するための 売 上 日 ( 開 始 ) を 入 力 します 初 期 値 は 当 月 月 初 (N)をコピーします (B) 売 上 日 ( 終 了 ) 名 前 定 義 名 : 売 上 集 計 2_ 売 上 日 _TO 売 上 データを 日 付 範 囲 で 絞 り 込 んで 取 得 するための 売 上 日 ( 終 了 ) を 入 力 します 初 期 値 は 当 月 月 末 (O)をコピーします (C) 合 計 名 前 定 義 名 : 売 上 集 計 2_ 合 計 ( 一 覧 表 に 表 示 した 支 店 別 伝 票 枚 数 の 合 計 と 支 店 別 売 上 金 額 の 合 計 を データベースから 取 得 展 開 します (D) 展 開 範 囲 名 前 定 義 名 :VDB_ 売 上 集 計 表 2 取 込 みデータの 展 開 範 囲 または 書 式 をコピーするためのコピー 先 領 域 です データ 展 開 後 この 領 域 名 の 範 囲 は 自 動 更 新 されます (E) 展 開 先 先 頭 セル 名 前 定 義 : 展 開 範 囲 (D)と 同 じにします 取 込 みデータの 展 開 先 領 域 の 先 頭 セル( 領 域 の 左 上 )です 一 覧 表 示 の 処 理 は ここを 基 準 にしています (F) 位 置.シート 名 名 前 定 義 : 売 上 集 計 2_シート タスクタイプ 420:セル 位 置 取 得 で 取 得 したシート 名 を 格 納 する ためのセルです (G) 位 置. 行 No 名 前 定 義 : 売 上 集 計 2_ 行 タスクタイプ 420:セル 位 置 取 得 で 取 得 した 行 No を 格 納 するた めのセルです (H) 位 置. 列 No 名 前 定 義 : 売 上 集 計 2_ 列 タスクタイプ 420:セル 位 置 取 得 取 得 した 列 No を 格 納 するため のセルです 次 頁 に 続 く 28

29 前 頁 より 続 く 項 番 名 称 内 容 (I) 戻 りセル 名 前 定 義 : 売 上 集 計 2_ 戻 りセル 明 細 から 画 面 が 戻 った 時 にカーソルを 停 止 しておくセル 位 置 を 求 め ます これは 画 面 が 元 に 戻 った 時 にカーソルの 場 所 が 丌 明 になる ことを 防 止 するためです =ADDRESS( 位 置. 行 No(F), 位 置. 列 No(G)+ 1) ただし 支 店 CD(L)がない 場 合 は 空 白 にします 実 際 の 数 式 =IF( 売 上 集 計 2_ 支 店 CD 判 定 =1,ADDRESS( 売 上 集 計 2_ 行, 売 上 集 計 2_ 列 +1),"") (J) デフォルト 位 置 情 報 名 前 定 義 : 売 上 集 計 2_ 位 置 _デフォルト 最 初 に 表 示 した 一 覧 表 示 内 のカーソル 位 置 を 先 頭 位 置 にするため シート 名 と 行 No を 設 定 します (K) 位 置. 支 店 CD 名 前 定 義 : 売 上 集 計 2_ 支 店 CD 一 覧 表 でカーソルが 存 在 する 行 内 の 支 店 コードを 取 得 します 売 上 明 細 は 売 上 日 (A B)と 支 店 CD で 絞 り 込 んだデータを 取 得 します そのために 支 店 CD を 求 めます 取 得 方 法 は INDEX 関 数 を 使 用 し 一 覧 表 の 先 頭 行 からの 相 対 行 を 求 め その 行 の 支 店 CD の 値 を 求 めます INDEX( 展 開 範 囲 (D) 表 の 行 No 表 の 列 No) 実 際 の 数 式 =IF(ISERR(INDEX(VDB_ 売 上 集 計 表 2, 売 上 集 計 2_ 行 -CELL("ROW",VDB_ 売 上 集 計 表 2)+1,2)),0,INDEX(VDB_ 売 上 集 計 表 2, 売 上 集 計 2_ 行 -CELL("ROW",VDB_ 売 上 集 計 表 2)+1,2)) (L) 分 岐 式 (1: 有 効 2:エラー) 名 前 定 義 : 売 上 集 計 2_ 支 店 CD 判 定 取 得 した 支 店 CD(K)が 正 しい 値 かをタスク 処 理 で 判 断 させるた めのセルで 判 定 結 果 を 求 めます IF(VALUE( 位 置. 支 店 CD(K))>0,1,2) 実 際 の 数 式 =IF(or(Q6=,Q6<1),2,1) (M) 一 覧 の 開 始 セル 名 前 定 義 : 売 上 集 計 2_ 一 覧 開 始 セル 指 定 データ 展 開 後 に タスクタイプ 426:ハイパータスク 実 行 設 定 で 選 択 列 に を 生 成 するための 開 始 セルを 求 めます CELL( ADDRESS, 展 開 範 囲 (D)) 実 際 の 数 式 =CELL( ADDRESS,VDB_ 売 上 集 計 表 2) 次 頁 に 続 く 29

30 前 頁 より 続 く 項 番 名 称 内 容 (N) 当 月 月 初 名 前 定 義 : 売 上 集 計 2_ 当 月 月 初 メニュー 売 上 集 計 2 のクリック 時 と クリアボタンのクリック 時 に 売 上 日 ( 開 始 )(A) を 設 定 し 直 す(コピー)ための 元 デー タを 求 めます DATE( 日 付 ( 年 ), 日 付 ( 月 ),1) 実 際 の 数 式 =DATE(YEAR(TODAY()),MONTH(TODAY()),1) (O) 当 月 月 初 名 前 定 義 : 売 上 集 計 2_ 当 月 末 日 メニュー 売 上 集 計 2 のクリック 時 と クリアボタンのクリック 時 に 売 上 日 ( 終 了 )(B) を 設 定 し 直 す(コピー)ための 元 デー タを 求 めます DATE( 日 付 ( 年 ), 日 付 ( 翌 月 ),1)-1 実 際 の 数 式 =DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1 (P) 取 得 データ 件 数 名 前 定 義 : 売 上 集 計 2_ 取 得 データ 件 数 一 覧 表 示 されたデータの 件 数 をデータ 展 開 時 に 設 定 します また タスクタイプ 426:ハイパータスク 実 行 設 定 で 選 択 列 に を 生 成 する 時 の 件 数 に 使 用 します (Q) メッセージ 名 前 定 義 : 売 上 集 計 2_MSG01 ボタンのクリック 時 カーソルが 一 覧 表 のデータ 範 囲 外 にあ る 場 合 に 表 示 するメッセージです 支 店 CD を 選 択 していません (R) ^ 売 上 日 FROM^ 名 前 定 義 :パラ. 売 上 日 _FR 売 上 一 覧 データと 売 上 明 細 データを 取 得 した 時 のSQL 文 に 渡 す (A)のパラメータです (S) ^ 売 上 日 TO^ 名 前 定 義 :パラ. 売 上 日 _TO 売 上 一 覧 データと 売 上 明 細 データを 取 得 した 時 のSQL 文 に 渡 す (B)のパラメータです (T) ^ 支 店 CD^ 名 前 定 義 :パラ. 支 店 CD 売 上 明 細 データを 取 得 する 時 のSQL 文 に 渡 す(K)のパラメータ です 30

31 2.4.2 定 義 シートの 定 義 1 メニュークリック 時 に 起 動 するタスクの 定 義 メニュークリック 時 に 起 動 するタスクは 取 得 データの 件 数 を 取 得 するために SQL( 照 会 ) 実 行 の タスクを 定 義 します ここでは 各 定 義 シートでポイントとなる 項 目 についてまとめています 定 義 シート 名 項 目 内 容 メニュー 定 義 TASKNO 一 覧 表 を 初 期 化 するタスク NO を 指 定 します タスク 定 義 タスクタイプ 411:クリア DNO 数 式 と 値 下 記 展 開 定 義 の 展 開 定 義 NO を 指 定 します 展 開 定 義 定 義 区 分 タスク 定 義 タイプ タスク 展 開 属 性 セル 範 囲 シート 保 護 クリア 数 式 と 売 上 集 計 表 2 (E) 有 り 展 開 定 義 定 義 区 分 タスク 定 義 タイプ タスク 展 開 属 性 クリア 数 式 と 値 セル 範 売 上 集 計 2_ 合 計 (C) シート 保 護 有 り タスク 定 義 タスクタイプ 410:コピー DNO 値 下 記 展 開 定 義 の 展 開 定 義 NO を 指 定 します 展 開 定 義 定 義 区 分 タスク 定 義 タイプ タスク 展 開 属 性 コピー 値 セル 範 売 上 集 計 2_ 当 月 月 初 (N) シート 保 護 先 セル 範 囲 先 シート 保 護 有 売 上 集 計 2_ 売 上 日 _FR (A) 有 り 展 開 定 義 定 義 区 分 タスク 定 義 タイプ タスク 展 開 属 性 コピー 値 セル 範 売 上 集 計 2_ 当 月 月 末 (O) シート 保 護 先 セル 範 囲 先 シート 保 護 有 売 上 集 計 2_ 売 上 日 _TO (B) 有 り 31

32 2 各 ボタンクリック 時 の 動 作 仕 様 集 計. 開 始. 開 始 戻 る. 開 始 タスク 定 義 コピー( 値 ) (A) (R) (B) (S) タスク 定 義 セル 位 置 取 得 タスク 定 義 シート 切 替 明 細 一 覧 タスク 定 義 SQL( 照 会 ) 売 上 一 覧 のデータ タスク 定 義 SQL( 照 会 ) 売 上 合 計 の 集 計 データ タスク 定 義 条 件 判 定 分 岐 サブタスク-A タスク 定 義 コピー( 値 ) (K) (T) サブタスク-B タスク 定 義 メッセージ 表 示 タスク 定 義 メインボタン 切 替 (ボタン 切 替 ) 明 細 一 覧 終 了 タスク 定 義 ハイパータス ク 実 行 設 定 (セルにリンク 設 定 ) 終 了 タスク 定 義 SQL( 照 会 ) タスク 定 義 シート 切 替 一 覧 明 細 タスク 定 義 メインボタン 切 替 (ボタン 切 替 ) 一 覧 明 細 終 了 集 計 ボタンは 取 得 データと 展 開 先 が 異 なるため SQL( 照 会 ) を2 回 行 い データ 取 得 後 に 取 得 データ 件 数 (P)も 取 得 するので その 後 ハイパータスク 実 行 設 定 で のリ ンクを 選 択 列 に 設 定 します なお のクリック 時 に ボタンが 動 作 するように ボタン 名 と 同 じ 値 を 表 示 文 字 列 に 設 定 します ボタンは のクリック 時 のカーソルのあるセル 位 置 を 取 得 します その 行 の 支 店 C D が 正 しい 値 のとき サブタスク-A を 実 行 し 誤 っているとき サブタスク-A を 実 行 し ます サブタスク-A は 支 店 CD をパラメータシートにコピーして 売 上 明 細 を 取 得 します そ の 後 売 上 明 細 のシートと 対 応 するメインボタンに 切 り 替 えます 明 細 から 一 覧 に 戻 る 戻 る ボタンは 売 上 一 覧 に 対 応 するシートとメインボタンを 切 り 替 えます シート 切 替 で 元 の 行 にカーソルが 行 くようにジャンプ 先 (I)を 指 定 します 32

33 3 各 ボタンクリック 時 に 起 動 するタスクの 定 義 集 計 ボタン 用 の 各 種 定 義 定 義 シート 名 項 目 内 容 ボタン 定 義 ボタン 名 表 示 名 集 計 TASKNO をクリック 時 に 動 作 するタスクNOを 指 定 します タスク 定 義 タスクタイプ 410:コピー DNO 値 下 記 展 開 定 義 の 展 開 定 義 NOを 指 定 します 展 開 定 義 定 義 区 分 タスク 定 義 タイプ タスク 展 開 属 性 セル 範 囲 シート 保 護 先 セル 範 囲 先 シート 保 護 コピー 売 上 集 計 2_ 売 上 日 _FR(A) 有 売 上 日 _FR(R) 有 り 展 開 定 義 定 義 区 分 タスク 定 義 タスク 定 義 ( 一 覧 用 ) タイプ タスク 展 開 属 性 セル 範 囲 シート 保 護 自 動 計 算 方 法 先 セル 範 囲 先 シート 保 護 タスクタイプ QNO DNO コピー 売 上 集 計 2_ 売 上 日 _TO(B) 有 り 後 再 計 売 上 日 _TO(S) 有 り 201:SQL 照 会 下 記 クエリー 定 義 のクエリー 定 義 NO を 指 定 します 下 記 展 開 定 義 の 展 開 定 義 NOを 指 定 します 次 頁 に 続 く 33

34 前 頁 より 続 く 定 義 シート 名 項 目 内 容 クエリー 定 義 クエリータイプ 照 会 属 性 Query SQL 文 /* 変 数 定 義 */ 売 上 日 売 上 日 請 求 先 CD 売 上 日 FROM 売 上 日 TO 請 求 先 CD datetime, datetime, int = '^ 売 上 日 FROM^' = '^ 売 上 日 TO^' = ^ 代 表 店 CD^ 展 開 定 義 定 義 区 分 クエリー 定 義 SELECT '' AS [ 選 択 ],A.[ 得 意 先 CD] AS [ 支 店 CD],A.[ 得 意 先 名 ] AS [ 支 店 名 ],COUNT(1) AS 伝 票 枚 数,SUM(A.[ 受 注 額 ]) AS 受 注 合 計 FROM 受 注 問 合 せ AS A INNER JOIN 受 注 明 細 AS B ON B.[ 受 注 伝 票 NO] = A.[ 受 注 伝 票 NO] AND B.[ 受 注 行 NO] = 1 WHERE A.[ 請 求 先 CD] 請 求 先 CD AND B.[ 納 品 日 ] 売 上 日 FROM 売 上 日 TO GROUP BY A.[ 請 求 先 CD],A.[ 得 意 先 CD], A.[ 得 意 先 名 ] ORDER BY A.[ 請 求 先 CD] 受 け 取 るパラメータは 変 数 に 代 入 します 売 上 日 による 絞 り 込 みは 明 細 の1 行 目 にある 納 品 日 を 売 上 日 としていた 場 合 INNER JOIN により 受 注 明 細 側 の 紐 付 けを 行 います タイプ タスク 展 開 属 性 セル 範 囲 セル 展 開 範 囲 名 照 会 売 上 集 計 表 売 上 集 計 表 2(E) 次 頁 に 続 く 34

35 前 頁 より 続 く 定 義 シート 名 項 目 内 容 タスク 定 義 ( 合 計 用 ) タスクタイプ QNO DNO 201:SQL 照 会 下 記 クエリー 定 義 のクエリー 定 義 NO を 指 定 します 下 記 展 開 定 義 の 展 開 定 義 NOを 指 定 します クエリー 定 義 クエリータイプ 照 会 属 性 Query SQL 文 /* 変 数 定 義 */ 売 上 日 売 上 日 請 求 先 CD 売 上 日 FROM 売 上 日 TO 請 求 先 CD datetime, datetime, int = '^ 売 上 日 FROM^' = '^ 売 上 日 TO^' = ^ 代 表 店 CD^ SELECT COUNT(1)AS 伝 票 枚 数,SUM(A.[ 受 注 額 ]) AS 受 注 合 計 FROM 受 注 問 合 せ AS A INNER JOIN 受 注 明 細 AS B ON B.[ 受 注 伝 票 NO] = A.[ 受 注 伝 票 NO] AND B.[ 受 注 行 NO] = 1 WHERE A.[ 請 求 先 CD] 請 求 先 CD AND B.[ 納 品 日 ] 売 上 日 FROM 売 上 日 TO GROUP BY A.[ 請 求 先 CD],A.[ 得 意 先 CD], A.[ 得 意 先 名 ] 受 け 取 るパラメータは 変 数 に 代 入 します 売 上 日 による 絞 り 込 みは 明 細 の1 行 目 にある 納 品 日 を 売 上 日 としていた 場 合 INNER JOIN により 受 注 明 細 側 の 紐 付 けを 行 います 展 開 定 義 定 義 区 分 クエリー 定 義 タイプ タスク 展 開 属 性 セル 範 囲 セル 展 開 範 囲 名 シート 保 護 自 動 計 算 方 法 照 会 売 上 集 計 2_ 合 計 売 上 集 計 2_ 合 計 (C) 有 り シート 後 再 計 算 次 頁 に 続 く 35

36 前 頁 より 続 く 定 義 シート 名 項 目 内 容 タスク 定 義 タスクタイプ 426:ハイパータスク 実 行 設 定 シート 保 護 セル 指 定 属 性 リンク 設 定 セル 表 示 文 字 列 同 時 設 定 数 セルにリンク 設 定 有 り 間 売 上 集 計 2_ 一 覧 開 始 セル 指 定 売 上 集 計 2_ 取 得 データ 件 数 (P) ボタン 用 の 各 種 定 義 定 義 シート 名 項 目 内 容 ボタン 定 義 ボタン 名 表 示 名 TASKNO をクリック 時 に 動 作 するタスクNOを 指 定 します タスク 定 義 タスクタイプ 420:セル 位 置 取 得 シート 保 護 取 得 シート 名 取 得 行 番 号 自 動 再 計 算 有 売 上 集 計 売 上 集 計 2_ 行 (G) シート 後 再 計 算 タスク 定 義 タスクタイプ 317: 条 件 判 定 分 岐 開 始 判 定 セル TASKJUMP1 売 上 集 計 2_ 支 店 CD 判 定 (L) セル 位 置 正 常 時 のタスク NO を 指 定 します セル 位 置 誤 り 時 のタスク NO を 指 定 します 36

37 セル 位 置 正 常 時 のタスク の 各 種 定 義 定 義 シート 名 項 目 内 容 タスク 定 義 タスクタイプ 410:コピー DNO 値 下 記 展 開 定 義 の 展 開 定 義 NOを 指 定 します 展 開 定 義 定 義 区 分 タスク 定 義 タスク 定 義 ( 売 上 明 細 用 ) タイプ タスク 展 開 属 性 セル 範 囲 シート 保 護 自 動 計 算 方 法 先 セル 範 囲 タスクタイプ QNO DNO コピー 売 上 集 計 2_ 支 店 CD(K) 有 り シート 後 再 計 支 店 CD(T) 201:SQL 照 会 下 記 クエリー 定 義 のクエリー 定 義 NO を 指 定 します 下 記 展 開 定 義 の 展 開 定 義 NOを 指 定 します クエリー 定 義 クエリータイプ 照 会 属 性 Query SQL 文 /* 変 数 定 義 */ 売 上 日 FROM 売 上 日 TO 支 店 CD int 売 上 日 FROM = '^ 売 上 日 FROM^' 売 上 日 TO = '^ 売 上 日 TO^' 支 店 CD = ^ 支 店 CD^ SELECT B.[ 納 品 日 ] AS [ 売 上 日 ],A.[ 受 注 伝 票 No] AS [ 受 付 No],B.[ 受 注 行 No] AS [ 行 No],[ 商 品 CD],B.[ 商 品 名 ],B.[ 数 量 ],B.[ 金 額 ],A.[ 顧 客 名 ] AS [ 注 文 者 名 ] FROM 受 注 問 合 せ AS A INNER JOIN 受 注 明 細 AS B ON B.[ 受 注 伝 票 NO] = A.[ 受 注 伝 票 NO] AND B.[ 納 品 日 ] 売 上 日 FROM 売 上 日 TO WHERE A.[ 得 意 先 CD] 支 店 CD ORDER BY B.[ 納 品 日 ],B.[ 受 注 伝 票 No] 受 け 取 るパラメータは 変 数 に 代 入 します 売 上 日 による 絞 り 込 みは 明 細 の1 行 目 にある 納 品 日 を 売 上 日 としていた 場 合 INNER JOIN により 受 注 明 細 側 の 紐 付 けを 行 います 次 頁 に 続 く 37

38 前 頁 より 続 く 定 義 シート 名 項 目 内 容 展 開 定 義 定 義 区 分 クエリー 定 義 タイプ タスク 展 開 属 性 照 会 Query セル 範 売 上 明 細 表 2 セル 展 開 範 囲 売 上 明 細 表 2 タスク 定 義 タスクタイプ 403:シート 切 替 シート 名 売 上 明 細 表 2 タスク 定 義 タスクタイプ 310:メインボタン 表 示 メイン BSNO ボタン 切 替 売 上 明 細 2 用 のメインボタンを 定 義 してある BSNO セル 位 置 誤 り 時 のタスク の 各 種 定 義 定 義 シート 名 項 目 内 容 タスク 定 義 タスクタイプ 318:メッセージ 表 示 シート 名 売 上 集 計 2(メッセージの 裏 に 表 示 する Excel シート) 未 設 定 の 場 合 は 現 在 のカレントシートを 表 示 します セル 指 定 属 性 メイン 売 上 集 計 2_MSG01(Q) 売 上 明 細 2 用 のメインボタンを 定 義 してある BSNO 戻 る ボタン 用 の 各 種 定 義 定 義 シート 名 項 目 内 容 タスク 定 義 タスクタイプ 310:メインボタン 表 示 メイン BSNO ボタン 切 替 元 の 売 上 集 計 2 用 のメインボタンを 定 義 してある BSNO タスク 定 義 タスクタイプ 403:シート 切 替 シート 名 売 上 集 計 2 シート 保 護 セル 指 定 方 法 ジャンプ 先 セル 有 り 間 売 上 集 計 2_ 戻 りセル 38

39 付 録 A. 改 版 履 歴 39

40 A. 改 版 履 歴 バージョン 改 版 年 月 日 項 番 変 更 内 容 初 版 公 開 40

41 株 式 会 社 ニューコム (c) Copyright 2009 NEWCOM CORPORATION. 本 社 埼 玉 県 さいたま 市 浦 和 区 常 盤 ジブラルタ 生 命 浦 和 ビル TEL: FAX:

42 株 式 会 社 ニューコム 42