JP2021047833A - ピボットテーブルを使用する表計算用テンプレート - Google Patents
ピボットテーブルを使用する表計算用テンプレート Download PDFInfo
- Publication number
- JP2021047833A JP2021047833A JP2019197097A JP2019197097A JP2021047833A JP 2021047833 A JP2021047833 A JP 2021047833A JP 2019197097 A JP2019197097 A JP 2019197097A JP 2019197097 A JP2019197097 A JP 2019197097A JP 2021047833 A JP2021047833 A JP 2021047833A
- Authority
- JP
- Japan
- Prior art keywords
- data
- pivot table
- search
- area
- column
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
Images
Landscapes
- Document Processing Apparatus (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
【課題】一般的な表計算ソフトウェアにおいて、ピボットテーブルを使用した環境下で、業務用の集計や分析などの高度化を支援することを目的とする。【解決手段】1又は複数のピボットテーブルを有する表計算用テンプレートであり、ユーザーデータに、データ列を追加しても、ピボットテーブルで集計フィールドなど表内計算を可能にする。1又は複数のマスタデータから関数で検索した情報、もしくは数式による計算結果の情報でデータ列を追加するための検索関数・数式領域と、マクロ・プログラム(VBA)がこの追加列の計算値をデータテーブルに追加してピボットテーブルの更新・再集計を自動実行させるための列・行管理領域とこれに対応するVBAマクロを有する。【選択図】図1
Description
本発明は、ピボットテーブルを使用する表計算用テンプレートに関する。
データを集計又は分析する際に、表計算ソフトウェアの有するピボットテーブル機能が使用される。
なお、特許文献1には、表情報処理装置に関する技術が開示されている。表情報処理装置は、「表を構成する属性を識別する1以上の属性識別子を有するスキーマ情報と、当該表の1以上のレコードとを格納し得る表格納部と、前記1以上の属性識別子のうち、ピボット表またはピボットグラフである分析結果情報を作成するための軸となる属性である軸属性を識別する1以上の軸属性識別子、および前記1以上の属性識別子のうち、属性値を集計する対象の属性である集計属性を識別する1以上の集計属性識別子を有するピボット情報を、予め格納しているピボット情報格納部と、前記表を用いた分析結果情報を作成する指示である情報分析指示を受け付ける受付部と、前記受付部が情報分析指示を受け付けた場合に、前記1以上の軸属性識別子で識別される属性を軸とし、前記1以上の集計属性識別子で識別される属性を集計対象の属性として、前記表の1以上のレコードおよび前記1以上の属性識別子を用いた分析結果情報を作成する情報分析部と、前記情報分析部が作成した分析結果情報を出力する出力部」とを具備する。
一般的な表計算ソフトウェアを用いて集計表を作成する際に、ピボットテーブルという機能が使用されることがある。ピボットテーブル機能は、データテーブルの見出し項目(フィールド)を行(ラベル)、列(ラベル)、値、フィルターのエリアに配置して作表する。値のエリアには、合計などの既設の計算方法に加えて、見出し項目を使ってユーザー独自の数式の列を追加する「集計フィールド」(以下、「表内計算」)という機能があり、これは、業務用途の帳表を作成する際に著しい効果を発揮するものである。ピボットテーブルを用いて業務用途等の高度な分析を行う場合、ピボットテーブルに関する技術的な理解が必要である一方、使用できる機能の選択に制約もあり、大量のデータを扱う業務や高度な分析に関するピボットテーブルの使用が敬遠され、高額なデータベースソフトなどの専用分析ソフトウェアを導入する必要に迫られることとなる。特許文献1に開示された技術は、表示方法に関する技術であって、ピボットテーブルを用いた業務用途や高度な分析を支援するものではない。
表計算ソフトウェアでは、分析用データの高度化のため、「リレーションシップ」という機能が追加され、複数テーブルの情報共有化が可能となったが、前記の「表内計算」が使用できなくなるという大きな制約を生じた。このため、「表内計算」は一部のユーザーの利用にとどまり、業務用途などの高度な活用はされていない。
また、「データテーブル機能」により、新たな列を追加し数式を入力すれば、追加列の全行にわたって入力した数式を複写し自動計算することが可能となり、ユーザーの設定負担が軽減されるようになった。しかし、データの件数が多い業務用途に使用しようとすると、自動計算が随時行われるために計算処理時間が長くなるという欠点が生じている。
本発明は、上記の点に鑑みてなされたものであって、一般的な表計算ソフトウェアにおいて、ピボットテーブルを使用した環境下で、業務用の集計や分析などの高度化を支援することを目的とする。
上記の課題を解決する本発明の一態様は、ピボットテーブルを使用する表計算用テンプレートであって、項目に対し値を関連付けた1又は複数のマスタデータと、基本データと、前記基本データの特定の項目を検索値として、前記検索値と関連付けられた値を前記マスタデータから検索する検索関数と、を含むフレームデータと、前記検索関数を用いた処理の結果得られる値をデータソースのデータとして分析する1又は複数のピボットテーブルと、表計算ソフトウェアに前記処理を実行させる命令を記載したVBAプログラムと、を有する。
上記の、ピボットテーブルを表計算用テンプレートにおいて、前記フレームデータは、前記基本データに関する所定の項目に対する前記値を算出する数式を含み、前記VBAプログラムは、前記表計算ソフトウェアに前記数式による処理を実行させる命令を記載した、ピボットテーブルを使用してもよい。
上記の、ピボットテーブルを使用する表計算用テンプレートにおいて、前記フレームデータと、データ列管理領域と、データ行管理領域とを含む集計用管理領域を有し、前記マスタデータは、項目に対し列番号を関連付けた領域と、前記マスタデータの開始行と最終行とを表示する領域とを有し、前記フレームデータは、前記検索関数及び前記数式を1行に含む数式行を有しており、前記データ列管理領域は、前記命令の実行範囲を特定する開始列及び最終列を示す情報を有しており、前記データ行管理領域は、前記命令の実行範囲を特定する開始行及び最終行を示す情報を有してもよい。
前記フレームデータには、前記検索関数及び前記数式の処理要否及び処理順序が記載され、前記VBAプログラムは、処理要と設定された前記検索関数又は前記数式について前記処理順序に基づいて処理を実行する命令を記載していてもよい。
前記データソースは、前記検索関数及び前記数式を用いた処理の結果得られる値を含み、前記ピボットテーブルは、前記データソースのデータを分析してもよい。
前記ピボットテーブルは、集計フィールド機能が使用可能であってもよい。
本発明の一態様によれば、一般的な表計算ソフトウェアにおいて、ピボットテーブルを使用した環境下で、業務用の集計や分析などの高度化を支援することができる。
以下、図面に基づいて本発明の実施の形態を説明する。発明者は、表計算ソフトウェア(以下、「表計算ソフト」と記載)の1つであるExcel(登録商標:以下同様)を長期にわたり使用しており、ピボットテーブルを用いる業務システムを開発し、目覚ましい成果を上げている。
表計算ソフトの一般的な機能を用いれば、自動計算で一定の成果物を得ることは可能である。その一方で、表計算ソフトを業務に使用する場合、多数のデータを取り扱うことを考慮する必要がある。一般的な機能では、多数のデータに対応可能とはいえず、処理に時間を要してしまうことがある。
一例としては、「データテーブル機能」は、列を追加し数式を入力すれば、全行にわたって数式を複写し自動計算する機能であるが、業務用途ではデータの件数が多くなるため、データの追加の都度、数式の再計算が自動で頻繁に行われるため、データの処理時間が長くなるという欠点が生じる。それに対応するためには、数式の自動計算モードを停止して手動計算モードに切り替える操作が頻繁に必要となるため、マクロなどにより操作手順の自動実行が必要になってくる。
マクロとは、表計算ソフトでユーザーが行う複雑で大量の操作手順を自動実行するためにVisual Basic for Applicasions (以下、「VBA」と記載)というプログラミング言語で作成されたプログラムで広く使われているが、一般的なユーザーには習得が難しく、専門家に開発を依頼する事例も多い。
また、ピボットテーブルを使用する場合、その機能の複雑さから、予め機能に関する深い知識が必要とされる。特に、機能の組み合わせによって制約がある場合があり、調整は容易でない。一例として、Excelにおいて、複数のテーブルを関連付けてリレーションシップを設定し、これらのテーブルを結合してピボットテーブルを作成することが可能になった。しかし、この機能を用いると、当該ピボットテーブルでは、表内計算機能が使えないという制約が生じる。
発明者は、ピボットテーブルの表内計算を長年活用し、自ら開発した業務システムの普及を願い、表計算ソフトやピボットテーブルの知識が十分でないユーザーでも扱えるテンプレートとして、本発明に係る表計算用テンプレートである「MEGUSEED」を発明した。「MEGUSEED」は、発明者の発明した業務システムを汎用化したテンプレートである。ユーザーは、例えば自分の業務に合わせて、「MEGUSEED」に初期設定をされている項目名を変更し、追加列の検索関数、追加数式等のカスタマイズを行い、「ピボット・フレーム」を完成させる。次に、ユーザーは、マスタデータとユーザーデータを全件貼り付け、予め組み込まれているマクロを実行することで、追加列の情報を含む集計用データが完成し、集計フィールドなどの表内計算機能が利用できるピボットテーブルを得る。
更新されたピボットテーブル表は初期設定のままでの項目配置であり、ユーザーの業務システムに応じて項目の追加や調整が可能である。例えば、集計値を新たな計算に用いる集計フィールドを追加することができる。即ち、集計値を新たな計算に用いることが可能である。ユーザーは、ピボットテーブルを調整・加工して「BASEピボット」とし、「BASEピボット」から様々な帳票を作成することができる。ユーザーは、例えばクロス集計やダイス分析を行った資料を作成してもよいし、予算実績比較等の分析を行う新たなピボットテーブルを作成してもよい。
発明者は、「MEGUSEED」を使用して得たピボットテーブルを用いて業務システムを構築するプロセスとして、「PDCM」手法を提案している。「P」はピボットテーブル機能を示し、「D」はデータ(関数等)を示し、「C」は管理会計を示し、「M」はマクロを示している。「PDCM」手法を用いることで、マクロ等の複雑な知識を有さない場合においても、ユーザーのデータ構造に適したピボット・システムを享受することができる。
なお、発明者は、ピボットテーブルの機能を業務用に正しくかつ有効に理解してもらうために、「MEGUSEED」を教育モデルとして使用することを検討している。表計算ソフトの関数やVBAマクロという高機能な使用法に長けていない一般的なユーザーであっても、「MEGUSEED」を使用することで、ピボットテーブルの有する機能を容易かつ高度に利用することができる。
図1は、表計算用テンプレート1の概要図である。表計算用テンプレート1は、作業メニュー・シート10と、集計用データ・シート20と、マスタデータ・シート30と、ピボットテーブル40とを有する。一例として、表計算用テンプレート1は、所定の表計算ソフトのファイル形式で記録されたファイルである。ユーザーが表計算ソフトを起動し、表計算用テンプレート1のファイルを開くと、作業メニュー・シート10と、集計用データ・シート20と、マスタデータ・シート30と、ピボットテーブル40とが異なるシートで表示される。なお、1つの表計算用テンプレート1の有する作業メニュー・シート10と、集計用データ・シート20と、マスタデータ・シート30と、ピボットテーブル40との各々は、1つであっても複数であってもよい。
作業メニュー・シート10は、作業手順を記載した手順書である。例えば作業メニュー・シート10には、作業手順に対応したリンク情報が含まれており、リンク情報を選択すると、作業手順に応じたシートに表示画面が遷移する。
集計用データ・シート20は、ピボットテーブル集計の元データであるデータソース210と、この集計用データを完成させるための「ピボット・フレーム」で構成されている。データソース210は、ユーザーの原始データであるユーザーデータと、列を追加して得られる検索・計算結果とで構成される。また、ピボット・フレームは、この列追加の処理を行うためのフレームデータ220と、マクロ実行のためのデータ列管理領域230及びデータ行管理領域240と、で構成される。フレームデータ220は、基本データと、基本データの特定の項目を検索値として、検索値と関連付けられた値を後述のマスタデータ領域31に含まれる情報から検索する検索関数と、基本データに関する所定の項目に対する値を算出する数式と、を含む。
データソース210の有する検索・計算結果の数値等については、フレームデータ220の検索関数・数式を検索・計算結果の領域の各列・各行のセルにコピーして計算を行うことにより算出される。マクロの実行により、データソース210に追加した列毎に、データ列管理領域230及びデータ行管理領域240の情報に基づいてユーザーデータ全行に計算数値を埋め込む作業が行われる。その結果、データソース210が完成し、ユーザーの求めるピボットテーブル40の集計が可能となる。
なお、本実施形態において、「検索関数」は、表計算ソフト内で予め用意されたものであり、検索キーを用いて、検索対象範囲の所定の列番号の項目を検出する関数である。一例として、検索対象範囲は後述のマスタデータ領域31である。
マスタデータ・シート30は、マスタデータ領域31と、マスタ行管理領域32と、マスタ列管理領域33とから構成される。マスタデータ領域31では、項目に対し値が関連付けられている。マスタ列管理領域33で各項目別に列番号が付与されており、検索関数により列番号が自動的に検索されるようになっている。
ピボットテーブル40は、データソース210のデータを分析するものである。ピボットテーブル40は、データソース210に含まれる数値を集計した集計値を含んでいる。ピボットテーブル40は、表計算ソフトの有する公知の機能を用いて、集計用データ・シート20のデータソース210に基づいて生成される。ピボットテーブル40の生成方法については説明を省略する。ピボットテーブル40は、集計値を用いて算出される表内計算値を含んでいてもよい。また、表計算用テンプレート1は、ピボットテーブル40に基づいて生成される他のピボットテーブル40を含んでいてもよい。
なお、表計算用テンプレート1は、図示しないVBAプログラムを含んでいる。VBAプログラムには、関数又は数式による処理を表計算ソフトに実行させるための命令が記載されている。より具体的には、VBAプログラムは、PC(Personal Computer)等の情報処理装置において実行されるプログラムであって、表計算ソフトをインストールした情報処理装置に対して、検索関数又は数式による処理を実行させる。
なお、VBAプログラムは、Excelなどの表計算ソフトなどの定型業務などで利用される操作手順を自動実行するプログラムであって、いわゆる「マクロ」が含まれる。以下、プログラムがマクロである例を用いて説明する。
図2は、集計用データ・シート20のデータ構造の一例を示す図である。集計用データ・シート20は、データソース210と、フレームデータ220と、データ列管理領域230と、データ行管理領域240と、マクロ実行ボタン250とを含む。本実施形態において、フレームデータ220と、データ列管理領域230と、データ行管理領域240とは、集計用管理領域260として取り扱われる。
データソース210は、ユーザーの原始データであるユーザーデータ領域211と、列を追加して得られる検索・計算結果領域212で構成され、ピボットテーブル40の集計の元データとなる。ユーザーデータ領域211と、検索・計算結果領域212は、各々1又は複数の列により構成される。また、データソース210は、見出し行210aと、1又は複数のデータ行210bと、を含む複数行のデータを有する。
ユーザーデータ領域211は、ユーザーの原始データであるデータを全件有するものである。図2に示すユーザーデータ領域211は、見出し行210aに「注文コード」、「日付」、「顧客コード」、及び「社員コード」などの特性を示す項目と「数量」などの集計用の項目を含んでいる。
検索・計算結果領域212には、各項目に対応して、検索又は計算された値が含まれる。即ち、検索・計算結果領域212には、ユーザーデータ領域211に含まれるデータを用いて各項目に対応して得られた値が含まれる。図2に示す検索・計算結果領域212では、「年度」、「半期」、「顧客名」、「販売エリア」、「担当者」、「商品名」、「商品分類」、「売価単価」、「原価単価」、「売価金額」、及び「原価金額」を示す項目について得られた値が互いに関連付けられて、ユーザーデータ領域211内のデータと併せて1行のデータを形成している。
フレームデータ220は、ユーザーデータ領域211と同様の項目を持つ1又は複数列からなる基本データ領域221と、これらの有する項目に対する値を得るための1又は複数列からなる検索関数・数式領域222と、を持つ領域である。また、フレームデータ220は、シート列番号220aと、計算順220bと、参照シート名220cと、検索列番号220dと、項目行220eと、基本データ・数式行220fと、を含む複数行のデータを有する。
シート列番号220aは、フレームデータ220のデータ列を特定する情報である。計算順220bは、検索関数又は数式の処理要否、及び処理順序を示す情報であって、検索関数又は数式に対して関連付けられている。参照シート名220cは、検索先のマスタデータ・シート30を特定する情報である。検索列番号220dは、検索先のマスタデータ・シート30における列番号を特定する情報である。項目行220eは、ユーザーデータ領域211内のユーザーデータ、又は検索・計算結果領域212内の検索・計算結果の項目名である。基本データ・数式行220fは、基本データ領域221と交わる領域において基本データのサンプルを含み、検索関数・数式領域222と交わる領域において項目に応じた検索関数又は数式を含む。
なお、フレームデータ220には、各行について初期設定の情報が含まれている。ユーザーは、ユーザーデータ領域211のユーザーデータに合わせて、項目行220eに対し見出し行210aに係る項目名を貼り付け、基本データ・数式行220fと基本データ領域221との交わる領域にデータ行210b内のサンプルデータを貼り付ける。これにより、フレームデータ220において、基本データ領域221と追加列の検索関数・数式領域222の調整と検証が可能となる。
検索関数を使用するためには、先ず、検索するマスタデータ・シート30と参照項目の特定が必要である。参照シート名220cと項目行220eを調整することで、検索先のマスタデータ・シート30の列番号が選択される。次に、基本データ・数式行220fと基本データ領域221とが交わる領域内のデータが検索キーとして選択される。検索関数には、基本データにおける所定の項目に対応する値を検索値として、マスタデータ領域31における所定の名前付き範囲を検索範囲とするよう、予め設定されている。これにより、検索結果の戻り値を得ることができる。なお、検索関数以外に、ユーザーが数式を入力し、計算結果を求める列を追加することが可能である。この検索関数・数式領域222に含まれる検索関数・数式をデータソース210の検索・計算結果領域212にコピーすれば、データソース210でも同様の計算が実行されることになる
マクロを実行すると、計算順220bにおいて処理要と設定された検索関数又は数式について、設定された処理順序に基づいて処理が実行される。換言すれば、表計算用テンプレート1に含まれるVBAプログラムは、処理要と設定された関数及び数式について、設定された処理順序に基づいて処理を実行する命令が記載されたものといえる。
図2に示すフレームデータ220には、計算順220b、項目行220e、及び基本データ・数式行220fに情報が格納されている。例えば計算順220b「1」の処理要否・処理順序に対し、項目行220eにおいて「年度」、「半期」、「顧客名」、及び「販売エリア」を示す項目が関連付けられている。これは、「年度」、「半期」、「顧客名」、及び「販売エリア」の各々の項目と関連する検索関数又は数式による処理が、処理要であって、かつ処理順序が「1」番目であることを示している。即ち、マクロが実行されると、これらの検索関数又は数式による処理が1番目に実行される。
なお、図2のフレームデータ220に示すように、同じ処理順序に複数の項目に係る検索関数・数式が関連付けられている場合、これらの関数又は数式による処理順序は列番号の若い順、左側から実行される。また、図2のフレームデータ220において、「商品分類」を示す項目の上段には、何も情報が格納されていない。これは、「商品分類」の項目に関する検索関数又は数式による処理が不要であることを示している。
データ列管理領域230は、フレームデータ220に含まれる処理順序に関する情報を格納する領域である。例えば、データ列管理領域230には、処理順序を示す番号に対し、該処理順序を含むフレームデータ220の列番号が関連付けられて表示される。より具体的には、データ列管理領域230は、マクロによる命令の実行範囲を特定する開始列及び最終列を示す情報を有する。
図2に示すデータ列管理領域230には、処理順序を示す「計算順序」に対し、「検索開始」及び「検索終了」と記載された情報が関連付けられている。「検索開始」とは、検索関数・数式領域222のうち、「計算順序」により特定される処理順序と関連する検索関数・数式の列の開始番号を示す情報であり、「検索終了」とは、列の終了番号を示す情報である。この場合、フレームデータ220の計算順220bに含まれる処理順序を示す値について、空白の場合は処理を実行しない列で、次の右隣の列は順序が繰り上がる。
データ行管理領域240は、集計用データ・シート20の行管理に関する情報である。より具体的には、データ行管理領域240は、マクロによる命令の実行範囲を特定する開始行及び最終行を示す情報を有する。例えば、図2に示すデータ行管理領域240には、「最終行」、「総件数」、「管理行」、「空白」、「データ開始行」、及び「数式行」という項目が設定されている。「最終行」の項目には、「総件数」の項目及び「管理行」の項目に係る行数を合算した値が関連付けられる。「総件数」の項目には、集計用データ・シート20の有する行のうち、情報が記載された行の総数が関連付けられる。「管理行」の項目には、データ列管理領域230及びフレームデータ220に含まれる行であって、情報が記載された行の総数が関連付けられる。
「空白行」の項目には、データ列管理領域230及びフレームデータ220に含まれる行のうち、情報が記載されていない行の総数が関連付けられる。「データ開始行」から「最終行」は、マクロで検索関数・数式をコピーして貼り付ける対象範囲の特定に用いるものである。これらのデータ行管理領域240は自動計算されているので、マクロのプログラムでの設定変更は必要としない。なお、データ行管理領域240に含まれる項目は、図2に示す例に限定されない。
マクロ実行ボタン250は、マクロの実行指示の入力操作を受け付ける領域である。マクロ実行ボタン250が選択クリックされると、表計算ソフトに実行させる命令を記載したマクロによる処理が開始される。本実施形態では、マクロ実行ボタン250が選択されると、検索関数・数式領域222に設定された検索関数又は数式を検索・計算結果領域212にコピーするなど一連の操作手順が自動的に行われる。
図3は、マスタデータ・シート30Aのデータ構造の一例を示す図である。マスタデータ・シート30Aは、マスタデータ・シート30の一例であって、顧客に関するマスタデータを含むものである。
マスタデータ・シート30Aは、マスタデータ領域31と、マスタ行管理領域32と、マスタ列管理領域33とを含む。マスタデータ領域31は、ピボットテーブル40による分析の対象となるデータを格納する領域である。図3に示すマスタデータ領域31には、顧客に関する情報が含まれている。マスタ行管理領域32は、マスタデータ・シート30Aの管理に関する情報であって、例えばマスタデータの開始行と最終行とを表示する領域である。マスタ列管理領域33は、列番号を付してマスタデータの項目の列番号を特定するために使用するものである。付言すれば、マスタ列管理領域33は、項目に対し列番号を関連付けた領域である。
一例として、マスタデータ領域31は、顧客コードと、顧客名と、販売エリアとを項目として含む。顧客コードは、顧客を特定する識別情報である。顧客名は、顧客の氏名又は名称を示す文字列である。販売エリアは、顧客に商品を販売する場合の販売地域を示す情報である。
なお、図3のマスタデータ領域31に示す項目は一例であり、項目は図3に示す例に限られない。例えば、顧客の支社名等の項目が含まれていてもよい。マスタ行管理領域32は、マスタデータ・シート30Aの管理に関する情報であって、検索範囲を特定するために使用するものである。例えばマスタ行管理領域32は、マスタデータ・シート30Aの有する行のうち、情報が記載された行の総数等を含む。
図4は、マスタデータ・シート30Bのデータ構造の一例を示す図である。マスタデータ・シート30Bは、マスタデータ・シート30の一例であって、商品に関するマスタデータを含むものである。
マスタデータ・シート30Bのマスタデータ領域31には、商品に関する情報が含まれる。一例として、マスタデータ領域31は、商品コードと、商品名と、商品分類と、売価単価と、原価単価を項目として含む。
商品コードは、商品を特定する識別情報である。商品名は、商品の名称を示す文字列である。商品分類は、商品の種別を示す情報である。売価単価は、商品1単位あたりの売価を示す値である。原価単価は、商品1単位あたりの原価を示す値である。
なお、図4の商品表示領域に示す項目は一例であり、項目は図4に示す例に限られない。例えば、商品の粗利単価等の項目が含まれていてもよい。マスタ行管理領域32は、マスタデータ・シート30Bの管理に関する情報であって、例えばマスタデータ・シート30Bの有する行のうち、情報が記載された行の総数等を含む。
図5は、表計算用テンプレート1を用いてユーザーが行う操作手順の一例を示すフローチャートである。本図に示す処理開始前に、情報処理装置に表計算ソフトがインストールされ、表計算用テンプレート1が保存されている。
まず、ユーザーは、情報処理装置を操作して表計算ソフトを起動させ、表計算用テンプレート1のファイルを開く(ステップS1)。
次に、ユーザーは、情報処理装置を操作して集計用データ・シート20の初期値を調整する(ステップS2)。より具体的には、ユーザーは、フレームデータ220の基本データ領域221に、自らの項目名並びにテストデータ等の情報を格納する。また、ユーザーデータ領域211にも同様に貼り付けることになるが、この時点では、全件のデータを貼り付ける必要はない。
次に、ユーザーは、情報処理装置を操作してマスタデータ・シート30の初期値を調整する(ステップS3)。より具体的には、ユーザーは、マスタデータ・シート30に設定された項目名に対し、情報を格納する。例えば、図3に示すマスタデータ・シート30Aにおいて、ユーザーはマスタデータ領域31の各項目に対し、顧客コード等の情報を関連付け、マスタ列管理領域33にも行見出し項目を貼付けて、顧客に関するマスタデータ・シート30Aを作成する。
なお、マスタデータ・シート30には予め集計用データ・シート20のデータソース210の有する項目名に対応する項目名が設定されている。しかしながら、集計用データ・シート20に含まれる関数がマスタデータ・シート30内を検索する関数である場合において、マスタデータ・シート30内の所定の位置を検索対象としている場合、集計用データ・シート20とマスタデータ・シート30の項目名が必ずしも対応していなくてもよい。その場合、ユーザーはマスタデータ・シート30の有する項目名を変更してもよい。同様に、ユーザーは集計用データ・シート20のデータソース210の項目名を変更してもよい。
次に、ユーザーは、情報処理装置を操作して集計用データ・シート20のフレームデータ220の初期値を調整する(ステップS4)。より具体的には、ユーザーは、検索関数・数式領域222のうち、マスタ検索を行う列を特定する。ユーザーは、マスタ検索を行う列の項目行220eに対し、マスタデータ・シート30の検索先の項目名を貼り付ける。また、ユーザーは、当該行の検索列番号220dに、マスタデータ・シート30の列番号を入力する。ユーザーは、当該行の検索関数・数式領域222に、検索関数を入力する。
例えばユーザーは、検索関数・数式領域222に以下の検索関数を入力する。
=IF(VLOOKUP($F13,担当M,1,TRUE)=$F13,VLOOKUP($F13,担当M,M$10,TRUE),$F13)
本例において、「$F13」のセルは、図2においてフレームデータ220の基本データ領域221と基本データ・数式行220fとの交わる領域のうち、項目行220eが「社員コード」である領域である。また、「$F13」のセルに含まれる情報は検索値として取り扱われる。即ち、本例の検索関数は、「社員コード」の基本データを検索値としている。
また、本例において、「担当M」とは、検索範囲であって、マスタデータ・シート30のマスタデータ領域31内の所定の領域に付与された名前である。「M$10」は、検索関数・数式領域222のうち、検索関数を記入する行に含まれる検索列番号220dである。検索列番号220dは、「担当M」の名前付き範囲のうち、顧客名が書き込まれた列番号と対応している。本関数によれば、基本データの社員コードと関連するマスタデータ領域31の顧客名を戻り値として得ることができる。
また、ユーザーは、検索関数・数式領域222のうち、計算値を得る列を追加数式列として特定する。ユーザーは、追加数式列の項目行220eに対し項目名を入力する。また、ユーザーは、追加数式列の検索関数・数式領域222に、所望の数式を入力する。なお、数式列を追加する場合には、右側の列に同様な入力作業を行う。
ユーザーは、続いて、計算順220bに、計算順を入力する。計算を行わない列がある場合には、空白として、1からまとまったグループでの入力とする。これで、ピボット・フレームの設定が完了する。
なお、この表計算用テンプレート1には、マスタデータ・シート30は複数用意し、かつ列を追加できる項目も余裕をもって組み込んでいる。使用しないマスタ、列項目は処理順を空白とすれば良い。
次に、ユーザーは、集計用データ・シート20のユーザーデータ領域211に全件のデータを貼り付ける。さらに、1又は複数のマスタデータ・シート30に全件のマスタデータ行を貼り付ける(ステップS5)。これで、ピボットテーブル40で集計する元データの準備が完了する。
次に、ユーザーは、マクロの実行指示を選択し、マクロを実行する(ステップS6)。具体的には、情報処理装置は、集計用データ・シート20の有するマクロ実行ボタン250の選択クリック操作を受け付けると、VBAマクロに係る処理を実行させる。以上により、本フローチャートに記載した処理は終了する。
図6は、マクロにより行われる自動実行処理の一例を示すフローチャートである。本処理は、図5のステップS6において行われる処理を詳細に説明するものである。以下、VBAプログラムにより、自動的に一連の処理が実行される。
まず、情報処理装置は、マクロの開始にあたり、数式の自動計算処理を停止する(ステップS61)。
次に、情報処理装置は、集計用データ・シート20のデータ列管理領域230を参照し、検索開始列番号と検索終了列番号を取得する。情報処理装置は、この列範囲に該当するデータソース210の全データ行をクリアする(ステップS62)。即ち、情報処理装置は、データソース210の検索・計算結果領域212のデータ行210bに含まれるデータを削除する。
次に、情報処理装置は、選択していない計算順序のうち、最も若い計算順序を選択する(ステップS63)。具体的には、情報処理装置は、集計用データ・シート20のフレームデータ220内の計算順220bを参照し、まだ選択していない計算順序を示す番号のうち、最も若い番号を選択する。
なお、計算順220bは、処理要の検索関数又は計算式に対して付与されるため、以下の処理は処理要の検索関数又は計算式について、処理順序に基づいて実行される。換言すれば、本実施形態におけるVBAプログラムは、処理要と設定された検索関数又は数式について、処理順序に基づいて処理を実行する命令が記載されたものといえる。
次に、情報処理装置は、選択した計算順序の検索関数又は数式を1つ選択する(ステップS64)。具体的には、情報処理装置は、ステップS63で選択した計算順序と関連する基本データ・数式行220f内の検索関数又は数式を1つ選択する。選択された検索関数又は数式を含む列を選択列とする。なお、1回目に行われる処理では、ステップS62で選択した計算順序と関連する列のうち、最も左の列の検索関数又は数式が選択される。
次に、情報処理装置は、検索・計算結果領域212の選択列の各行に、検索関数又は数式を入力する(ステップS65)。具体的には、情報処理装置は、集計用データ・シート20のデータ行管理領域240を参照し、データ開始行及び最終行を抽出する。情報処理装置は、検索・計算結果領域212の選択列について、データ開始行から最終行までの範囲を選択範囲とし、ステップS64で選択した検索関数又は数式をコピー及び貼付けする。
次に、情報処理装置は、選択範囲について計算又は検索を行い、値を得る。この選択範囲の計算又は検索結果を、値複写して貼り付ける(ステップS66)。数式を貼り付けただけでは、自動計算がされていない不完全な状態であったが、計算処理又は検索処理後の値に置き換わることになる。
次に、情報処理装置は、まだ選択していない検索関数又は数式があるか否かを判定する(ステップS67)。具体的には、情報処理装置は、ステップS64で選択した計算順序と関連する検索関数又は数式のうち、まだ選択していない検索関数又は数式があるか否かを判定する。情報処理装置は、まだ選択していない検索関数又は数式がないと判定する場合(ステップS67で「NO」の場合)、処理をステップS68に移行する。
情報処理装置は、まだ選択していない検索関数又は数式があると判定する場合(ステップS67で「YES」の場合)、処理をステップS64に移行する。なお、一例として、情報処理装置は、処理をステップS64に移行した場合、まだ選択していない検索関数又は数式のうち、最も左の列に位置する検索関数又は数式を選択する。
次に、情報処理装置は、まだ選択していない計算順序があるか否かを判定する(ステップS68)。具体的には、情報処理装置は、フレームデータ220の計算順220bについて、まだステップS63で選択していない計算順序があるか否かを判定する。情報処理装置は、まだ選択していない計算順序があると判定する場合(ステップS68で「YES」の場合)、処理をステップS63に戻す。即ち、その後実行されるステップS63において、まだ選択していない計算順序が選択される。
情報処理装置は、まだ選択していない計算順序がないと判定する場合(ステップS68で「NO」の場合)、ステップS61で停止した数式の自動計算機能を開始する(ステップS69)。その後、情報処理装置は、本フローチャートの処理を終了する。
以上、本実施形態では、予め集計用データ・シート20に「ピボット・フレーム」で検索関数や数式が設定されているため、簡単な操作でユーザーのマスタデータ・シート30の内容を参照したデータソース210を作成することができる。また、検索関数・数式領域222の計算順序の設定により、処理要否や処理順序を調整できるため、データソース210のデータ量が多い場合においても、VBAマクロの編集調整をしなくてもマクロを実行するだけで、より適切な処理時間にて所望の集計用データ・シート20を得ることができる。
また、本実施形態では、先述したように、集計用データ・シート20に基づいてピボットテーブル40が自動更新される。
図7は、ピボットテーブル40のデータ構造の一例を示す図である。ピボットテーブル40は、項目に対する値を含むテーブルである。図7に示すピボットテーブル40は、「商品分類」、「年度」、「半期」、「年月」、「売上金額」、「数量」、「売価単価」の項目を含んでいる。また、ピボットテーブル40の有する値には、データソース210に含まれる値を集計した集計値が含まれる。
「商品分類」の項目には、集計用データ・シート20の検索・計算結果領域212における「商品分類」の項目に応じた値が格納される。「年度」、及び「半期」の項目には、検索・計算結果領域212における「年度」、及び「半期」の項目に応じた値が格納される。
「売上金額」の項目には、関連する「年度」及び「半期」の値により特定される期間における、「商品分類」の値により特定される分類の商品の売上金額が格納される。同様に、「数量」の項目には、関連する「年度」及び「半期」の値により特定される期間において、「商品分類」の値に分類される商品の売上数量が格納される。
より具体的には、図7に示すピボットテーブル40の作成にあたり、情報処理装置は、データソース210を参照し、「年度」、「半期」及び「商品コード」が共通する行を抽出し、抽出した行の「売価金額」に含まれる値を合計する。また、情報処理装置は、抽出した行の「数量」に含まれる値を合計する。その後情報処理装置は、同時に、抽出した行の「年度」、「半期」、及び「商品コード」に対し、「売上金額」、及び「数量」の項目に各々合計した値を関連付けてピボットテーブル40を生成する。
なお、「売上金額」、及び「数量」に対応する値は、データソース210に含まれる値を集計して得た値であるため、「集計値」として取り扱われる。
また、「売価単価」の項目は、表内計算でユーザー独自の数式により計算した商品の1単位あたりの売価が格納されている列である。本実施形態のピボットテーブル40は、集計フィールド機能が使用可能であるため、ピボットテーブル40の集計フィールド機能を使用して、「売上金額」の値を「数量」で除算する数式を入力することにより、「売価単価」が計算される列が追加される。
先述したように、従来、集計用データ・シート20とマスタデータ・シート30など、異なる複数のテーブルをリレーションシップにより関連付けてピボットテーブル40を作成した場合、「表内計算」の併用が不可のため、集計値を新たな計算に用いることができなかった。本実施形態の表計算用テンプレート1を用いれば、この制約を回避し、集計用データ・シート20及びマスタデータ・シート30の値を用いてピボットテーブル40を作成した場合、集計値を新たな計算に使用して表内計算値を得ることができる。
従って、本実施形態の表計算用テンプレート1を使用することで、表計算ソフトウェアにおいて、ピボットテーブル40を使用した分析を効率的に行うことが可能となる。
なお、表計算用テンプレート1を用いて作成されるピボットテーブル40は、表内計算値を得ることが可能であるため、事後的に集計値を計算に用いる新たな項目を追加することができる。即ち、ユーザーの業務システムに応じて、項目の追加や調整を容易に行うことができる。
また、図5に示すステップS5において、ユーザーによりユーザーデータの貼付けが行われた。本実施形態では、ユーザーデータ部分の取込みは、ユーザーの作業によらず、マクロの実行により行われてもよい。もとより、ユーザーの元データは本テンプレートには存在していないため、他のデータ・ベースや、外部データからの取込みが想定される。
より詳細には、例えば、ユーザーデータのテーブルが未確定テーブルとして記録されているシートがある。データソース210には、確定と未確定の判定項目を持つことで、未確定データをフィルターで絞り込みしてデータ行をクリアする。次にこの未確定テーブルから最新の未確定データをデータソース210のユーザーデータ領域211に貼り付ける。その場合、VBAマクロにこの作業を組み込むことで、ユーザーデータ領域211の入替更新をマクロの実行で自動実行することが可能になる。
以上、本発明に係る各実施形態の説明を行ってきたが、本発明は、上記した実施形態の一例に限定されるものではなく、様々な変形例が含まれる。例えば、上記した実施形態の一例は、本発明を分かり易くするために詳細に説明したものであり、本発明は、ここで説明した全ての構成を備えるものに限定されない。また、ある実施形態の一例の構成の一部を他の一例の構成に置き換えることが可能である。また、ある実施形態の一例の構成に他の一例の構成を加えることも可能である。また、各実施形態の一例の構成の一部について、他の構成の追加・削除・置換をすることもできる。
例えば、本発明テンプレート提供に係る態様として、DVD、外部メモリなどの記録媒体、もしくは、LAN回線や無線LANなどを介してインターネットや他の情報処理装置との接続等などから提供されることが想定される。さらには、このテンプレートを情報処理装置に組み込んだり、表計算ソフトに組み込むことも可能である。また、別の形態としては、データ・ベースソフトと連係して組み込むことも可能である。
また、本発明テンプレートは表計算ソフトを使用することが必要条件であり、一般的にはExcelを想定している。もしくは、Excelと互換性のある表計算ソフトでも使用することが可能である。
また例えば、本発明に係る他の態様として、情報処理装置が、メモリ又は外部記憶装置により機能が実現される記憶部と、CPU(Central Processing Unit)等の演算装置により機能が実現される処理部とを有し、記憶部に表計算用テンプレート1を記憶し、処理部により表計算用テンプレート1に含まれるVBAプログラムの処理が実行されてもよい。本実施形態によれば、このような機能構成である情報処理装置を提供することが可能である。
その場合、情報処理装置は、項目に対し値を関連付けた1又は複数のマスタデータ・シートと、基本データと、基本データの特定の項目を検索値として、該検索値と関連付けられた値をマスタデータ・シートから検索する検索関数と、を含むフレームデータと、検索関数を用いた処理の結果得られる値をデータソースのデータとして分析する1又は複数のピボットテーブルと、表計算ソフトウェアに前記処理を実行させる命令を記載したVBAプログラムとを記憶部に記憶しており、処理部は、検索関数を用いて値を取得するとともに、取得した値を分析するピボットテーブルを更新するものであってもよい。
また、本発明に係る他の態様として、情報処理装置が行うピボットテーブル更新方法であって、項目に対し値を関連付けた1又は複数のマスタデータ・シートと、基本データと、基本データの特定の項目を検索値として、該検索値と関連付けられた値をマスタデータ・シートから検索する検索関数と、を含むフレームデータと、検索関数を用いた処理の結果得られる値をデータソースのデータとして分析する1又は複数のピボットテーブルと、表計算ソフトウェアに前記処理を実行させる命令を記載したVBAプログラムとを記憶部が記憶し、処理部が、検索関数を用いて値を取得する検索手順と、取得した値を分析してピボットテーブルを更新するピボットテーブル更新手順とを実行するものであってもよい。
なお、上記の表計算用テンプレート1の構成は、理解を容易にするために、主な処理内容に応じて分類したものである。構成要素の分類の仕方や名称によって、本発明が制限されることはない。表計算用テンプレート1の構成は、処理内容に応じて、さらに多くの構成要素に分類することもできる。また、1つの構成要素がさらに多くの処理を実行するように分類することもできる。
1:表計算用テンプレート、10:作業メニュー・シート、20:集計用データ・シート、30:マスタデータ・シート、31:マスタデータ領域、32:マスタ行管理領域、33:マスタ列管理領域、40:ピボットテーブル、210:データソース、211:ユーザーデータ領域、212:検索・計算結果領域、220:フレームデータ、221:基本データ領域、222:検索関数・数式領域、230:データ列管理領域、240:データ行管理領域、250:マクロ実行ボタン
Claims (6)
- 項目に対し値を関連付けた1又は複数のマスタデータと、
基本データと、前記基本データの特定の項目を検索値として、前記検索値と関連付けられた値を前記マスタデータから検索する検索関数と、を含むフレームデータと、
前記検索関数を用いた処理の結果得られる値をデータソースのデータとして分析する1又は複数のピボットテーブルと、
表計算ソフトウェアに前記処理を実行させる命令を記載したVBAプログラムと、
を有する、ピボットテーブルを使用する表計算用テンプレート。 - 請求項1に記載の、ピボットテーブルを使用する表計算用テンプレートであって、
前記フレームデータは、前記基本データに関する所定の項目に対する前記値を算出する数式を含み、
前記VBAプログラムは、前記表計算ソフトウェアに前記数式による処理を実行させる命令を記載した、ピボットテーブルを使用する表計算用テンプレート。 - 請求項2に記載の、ピボットテーブルを使用する表計算用テンプレートであって、
前記フレームデータと、データ列管理領域と、データ行管理領域とを含む集計用管理領域を有し、
前記マスタデータは、項目に対し列番号を関連付けた領域と、前記マスタデータの開始行と最終行とを表示する領域とを有し、
前記フレームデータは、前記検索関数及び前記数式を1行に含む数式行を有しており、
前記データ列管理領域は、前記命令の実行範囲を特定する開始列及び最終列を示す情報を有しており、
前記データ行管理領域は、前記命令の実行範囲を特定する開始行及び最終行を示す情報を有している、ピボットテーブルを使用する表計算用テンプレート。 - 請求項3に記載の、ピボットテーブルを使用する表計算用テンプレートであって、
前記フレームデータには、前記検索関数及び前記数式の処理要否及び処理順序が記載され、
前記VBAプログラムは、処理要と設定された前記検索関数又は前記数式について前記処理順序に基づいて処理を実行する命令を記載した、ピボットテーブルを使用する表計算用テンプレート。 - 請求項3又は4に記載の、ピボットテーブルを使用する表計算用テンプレートであって、
前記データソースは、前記検索関数及び前記数式を用いた処理の結果得られる値を含み、
前記ピボットテーブルは、前記データソースのデータを分析する、ピボットテーブルを使用する表計算用テンプレート。 - 請求項1から5のいずれか一項に記載の、ピボットテーブルを使用する表計算用テンプレートであって、
前記ピボットテーブルは、集計フィールド機能が使用可能な、ピボットテーブルを使用する表計算用テンプレート。
Priority Applications (1)
Application Number | Priority Date | Filing Date | Title |
---|---|---|---|
JP2019197097A JP2021047833A (ja) | 2019-10-30 | 2019-10-30 | ピボットテーブルを使用する表計算用テンプレート |
Applications Claiming Priority (1)
Application Number | Priority Date | Filing Date | Title |
---|---|---|---|
JP2019197097A JP2021047833A (ja) | 2019-10-30 | 2019-10-30 | ピボットテーブルを使用する表計算用テンプレート |
Related Parent Applications (1)
Application Number | Title | Priority Date | Filing Date |
---|---|---|---|
JP2019003521 Continuation | 2019-09-18 |
Publications (1)
Publication Number | Publication Date |
---|---|
JP2021047833A true JP2021047833A (ja) | 2021-03-25 |
Family
ID=74878573
Family Applications (1)
Application Number | Title | Priority Date | Filing Date |
---|---|---|---|
JP2019197097A Pending JP2021047833A (ja) | 2019-10-30 | 2019-10-30 | ピボットテーブルを使用する表計算用テンプレート |
Country Status (1)
Country | Link |
---|---|
JP (1) | JP2021047833A (ja) |
Cited By (1)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
US20220284182A1 (en) * | 2021-02-26 | 2022-09-08 | Finicast, Inc. | Apparatus and method for forming pivot tables from pivot frames |
-
2019
- 2019-10-30 JP JP2019197097A patent/JP2021047833A/ja active Pending
Cited By (1)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
US20220284182A1 (en) * | 2021-02-26 | 2022-09-08 | Finicast, Inc. | Apparatus and method for forming pivot tables from pivot frames |
Similar Documents
Publication | Publication Date | Title |
---|---|---|
JP6818050B2 (ja) | ウェブサイト構築システムおよび方法 | |
JP6652490B2 (ja) | 多ディメンション・データー構造に対する実行のためのクエリー構築 | |
CN107111639B (zh) | 构建报表 | |
US20140237341A1 (en) | System and method for creating integrated web form and database applications | |
US8234137B2 (en) | One view integrated project management system | |
CN101297268A (zh) | 用于设计自动业务过程应用程序的集成***、工具和方法 | |
Bakke et al. | A spreadsheet-based user interface for managing plural relationships in structured data | |
CN111522868A (zh) | 数据规则的处理方法、装置、电子设备及存储介质 | |
Alexander et al. | Access 2013 Bible | |
JP2021047833A (ja) | ピボットテーブルを使用する表計算用テンプレート | |
Conrad | Microsoft Access 2013 inside out | |
Seamark | Beginning DAX with Power BI: The SQL Pro’s Guide to Better Business Intelligence | |
DE102019202640A1 (de) | Unterstützungssystem | |
JP6369766B1 (ja) | スケジュール帳票出力方法、及び帳票出力プログラム | |
JP2004185346A (ja) | プロジェクト作業支援方法およびシステム | |
JP5714472B2 (ja) | 製品情報管理装置、方法、及びプログラム | |
JP6870197B2 (ja) | 情報処理装置,コンピュータの制御方法及び制御プログラム | |
Miles et al. | Drupal's Building Blocks: Quickly Building Web Sites with CCK, Views and Panels | |
JP6810347B2 (ja) | アプリケーション作成支援システム | |
MSIS | Being COUNTER-Productive: Developing Strategies and Workflows for Processing and Standardizing E-Resource Usage Data | |
Fath et al. | On the development of user interface design guidelines for electronic commerce | |
Zanzig et al. | Hands-on training in relational database concepts | |
Borg et al. | Användbarhet vid Produktberikning i PIM-System | |
JP6100956B1 (ja) | アプリケーション作成支援システム | |
JP2003316771A (ja) | データ作成支援システム及びデータ作成支援方法 |