「StiLL」デザイン情報082 「StiLL-Ⅹ」とは!　-25

便利な「StiLL-Ⅹ」のシステムテンプレート開発カイハツ用(Excel)について

「Excelピボット集計」テンプレート

① 「Excelピボット集計」テンプレートは、ピボットテーブルを使った集計表を作成するシステムのテンプレートです。ピボットテーブルならば、集計表を関数や計算式を使ツカわずに手早く完成させることができ、修正も簡単です。

② テンプレートには必要なシートがセットされていますので、画面やプログラムのサンプルを参考にしながらシステムを作成してください。Excel画面制御を行う場合はシステムテンプレート「StiLLAUTO」を追加します。

③ シート「Pシート1SP」にサンプルプログラムを作成してあります。サンプルプログラムでは他のExcelブックから集計データを取得するようになっていますが、データベースやテキストファイルからデータを取得することもできます。

対応Ver. ：「StiLL-Ⅹ」Ver1.0以降
対応種類：StiLL Dev・Pro・Std

① ピボットテーブルで手早く簡単に集計表を作成できます。

ある場所

メニュー [StiLL] → [システムテンプレート] → [開発用(Excel)]

特徴・目的

Excelの「ピボットテーブル」機能を使って集計表を作成するシステムのテンプレートです。

できる事・便利な点

ピボットテーブルを使うことで関数や計算式を作成する手間が省ハブけますので、手早く簡単に集計表を作成する

ことができます。項目の入れ替カえも簡単ですので、修正にかかる工数も短くなります。

このテンプレートでは必要なシートと集計プログラムのサンプルが作成されていますので、参考にしながら

システムを作成サクセイすることができます。

② システム作成に必要なシートをセット。サンプルを参考にしながらシステムを完成させてください。

このテンプレートにあるシートは下記の通トオりです。

シート名

目　的

集計画面サンプル

集計画面の作成サンプルです。

このサンプルを参考にして集計画面を作成してください。

またサンプルプログラムが動作するようにボタンが設定

されています。

DLDATAサンプル

「DLDATA」シートにデータを出力した状態のサンプルです。

このサンプルを参考にして、集計データをシート[DLDATA]に

出力するプログラムを作成してください。

集計画面1

表示画面として使用します。

Excelの「条件付き書式」機能を使って、罫線を追加したり

セルの色を変化させるように設定されています。

DLDATA

他のExcelブックやデータベース等から取得したデータを

このシートに出力します。

Pシート1

プログラムシートです。ここにプログラムを作成します。

集計プログラムのサンプルが作成済ですので、参考に

しながらプログラムを完成させてください。

集計画面

[集計画面1]シートと同様、表示画面として使用するシート

ですが、条件付き書式は設定されていません。

VERDIALOG

バージョン情報のダイアログを作成するシートです。

使い方カタの詳細については下記バックナンバーをご覧ください。

【バックナンバー075】「StiLL-Ⅹ」とは!　-18

SYSDATAP

システムの基本情報をまとめておくシートです。

使い方カタの詳細については下記バックナンバーをご覧ください。

【バックナンバー065】「StiLL-Ⅹ」とは!　-8

この他、Excel画面制御を行う場合は「StiLLAUTO」テンプレートを追加してください。

「StiLLAUTO」テンプレートの使い方等については下記バックナンバーをご覧ください。

【バックナンバー064】「StiLL-Ⅹ」とは!　-7

③ 集計プログラムの作成ポイント。ピボットテーブルの操作が制御できます。

シート「Pシート1」には、集計プログラムのサンプルが作成されていますので、サンプルプログラムを参考に

しながらプログラムを作成していきます。

サンプルプログラムのポイントは下記の通トオりです。

なお、これらの作成ポイントはあくまでも一例であり、実際に作成するシステムではさらに工夫が必要になる場合が

あります。

サンプルプログラムは実際の集計プログラムを作成する上での参考としてください。

(1) 集計したいデータを取得する

集計したいデータをシート[DLDATA]に出力するように、データ取得のボタンを作成します。

集計サンプルプログラムでは、他のExcelブックのデータを取得することを想定して「レコード複合取得」の

ボタンを作成しています。

データベースのデータを取得して集計したい場合はこのボタンの機能を「DBデータ取得」に、

またテキストファイルのデータを取得したい場合は「テキストデータ取得」機能にしてください。

(2) ピボットテーブルのデータ範囲

ピボットテーブルを作成する際、使用するデータの範囲を範囲名で指定します。

「レコード複合取得」ボタンを設定するとき、「出力範囲名」を設定します(サンプルプログラムでは「DATAP」

という範囲名にしています)。

すると、取得したデータが出力されているセル範囲に名前が定義されます。

「出力範囲名」で指定した名前で定義された範囲をピボットテーブルの「使用するデータの範囲」にします。

このようにすると、データの行数が可変する場合であっても、ボタンを実行すると取得したデータが出力されて

いる範囲に名前が定義されますので、データの漏れを防フセぐことができます。

範囲指定する(左図ズでは1000行目まで)

→1001行目以降のデータが集計されない

「出力範囲名」で指定する

→常にデータ全体が集計される

参考

・

「DBデータ取得」「テキストデータ取得」も、「出力範囲名」を設定することができます。

・

ピボットテーブルの作成方法の詳細については下記「Excelメールサービス」バックナンバーを

ご覧ください。

【Excelメールサービスバックナンバー052】データ--ピボットテーブル作成入門編

http://www.still.co.jp/excel/support/052ems.xls

また「Excelメールサービス」バックナンバーでは、さまざまなピボットテーブルの機能をご紹介

しています。

(3) 集計データがない場合のエラーメッセージ

データを取得した結果、データがない場合はエラーメッセージを表示するようにしています。

シート「Pシート1」に作成されているサンプルプログラムの「④条件分岐実行」ボタンでデータの有無を

判断するようになっています。

サンプルプログラムでは、データを取得した結果、シート[DLDATA]のセルF15に値が入っていればデータが

あると見なし、値がない場合はデータがないと見なしてエラーメッセージを表示、それ以降のボタンを実行

しないという設定になっています。

(4) ピボットテーブルの操作を制御する

ピボットテーブルはユーザーが自由に集計表を操作して、様々な視点からのデータ分析ができるように

なっているのが大きな利点です。

しかし、画面上に表示する集計表を作成する手段としてピボットテーブルを使う場合、表示画面上ウエにある

ピボットテーブルをユーザーが自由に操作できてしまうと、画面表示が崩れてしまうなど不都合が生じる

可能性があります(下図ズ例1・例2参照)。

【例1】

【例2】

そこで、このサンプルプログラムではユーザーによるピボットテーブルの操作を制限しています。

「ピボットテーブル更新」ボタンでは、ダブルクリック・右クリック操作の有効/無効、フィールドリスト・

[ピボットテーブル]ツールバーの表示/非表示を制御できるようになっています。

(5) ピボットテーブルに書式を設定する

ピボットテーブルのデフォルトの書式は各セルに罫線がないなど、作成する表によっては画面上で見づらく

なってしまうことがあります。

【デフォルトの書式の例】

集計サンプルプログラムでは、ピボットテーブルに書式を設定して画面上で見やすくする処理をしています。

①

「名前定義」ボタンを使って、ピボットテーブルが作成されている範囲に対して名前を定義します。

StiLL-Ⅹの「名前定義」機能では、名前を定義する範囲を自動的に判断できるようになっています。

②

「セル書式」ボタンを使って、①で名前を定義した範囲に対して書式を設定します。