セル参照を行番号、列番号指定方式(オフセット方式)にするとメンテナンスしやすい

Excel表からの抽出、Excel表の生成。どちらの手順も、その制御シートでは該当項目の指定に「セル参照」を使います。

例えばExcelシートから、項目1~項目12まで横並びの列(データの先頭は2行目)からデータを抽出する場合を考えてみます。下図の例のように、A2、B2、C2~L2と、「A1形式の相対参照方式」で指定すれば事足りるのですが、もしこのデータ列が100列以上あったらどうでしょうか?

f:id:xoblos:20170722201941p:plain

100列の場合の抽出指定は、A2、B2、C2~CV2と指定しなければならず、設定作業が面倒な感じ。また、列が減ったり増えたり、並び順が変わるなどメンテナンス時の再設定作業も大変そうです。「A1形式」の代わりに、「オフセット方式」で設定してみましょう。まず、A1の代わりに (2, 1) と指定します。これがオフセット方式で、(行番号, 列番号) の形式でセル参照を表現します。(2, 1) は2行目の1列目、つまりA2のセルを参照します。

f:id:xoblos:20170722201954p:plain

(2, 1) と指定したセルの矩形右下の□をポイントすると+に変わるので、そのまま「項目12」の下のセルまで右側にずるっとドラッグしていきます。Excelのオートフィル操作ですね。

f:id:xoblos:20170722202007p:plain

手を離すと、(2, 1) ~ (2, 12) まで簡単に設定できました。(2, 12) はL2と同等です。

f:id:xoblos:20170722202020p:plain

この手法で、100列でも200列以上でも同じ操作で大丈夫です。列が減ったり増えたり、並び順が変わるなどメンテナンス時には、(2, 1) のセルをつまんで、またドラッグして引き直せば、再設定が即座に完了します!

ヘルプページが開かない

Designer からヘルプページを起動。

f:id:xoblos:20170720174353p:plain

業務ファイル(.xob)や、制御シートのセクション/キーワード等の詳細は、上図のようなメニューから参照することができます。

稀な事象ですが‥‥インターネットの設定によっては、たとえインターネットに繋げられる環境であったとしても、次の図のような真っ白なページが表示されて終わってしまうことがあります。

f:id:xoblos:20170720174401p:plain

ヘルプサイトは、一度管理ページへアクセスしてから、本来のページにリダイレクトする仕組みになっています。これを無効にする設定になっていると、参照することができません。

参照できない場合、以下のような設定を試してみてください。

f:id:xoblos:20170720174408p:plain

f:id:xoblos:20170720174419p:plain

f:id:xoblos:20170720174430p:plain

カレンダーを素早く作り、素早く仕組みを組み立てる

Excelでカレンダーを表現する場合、最もシンプルな方法でカレンダー作成することをお勧めします。

とかく、複雑な形式で時系列を表現しがちですが、思い切って簡素化を図ってみましょう。ひな形のカレンダー作成には、Excelのオートフィル操作が威力を発揮します。この例では、年月日を左寄せ、曜日を中央寄せに、前もって書式設定しています。

f:id:xoblos:20170716112252p:plain

1日と曜日を入力したら、2セルを範囲指定して矩形の右下(■)をつまみます。■が+に変わったら、

f:id:xoblos:20170716112305p:plain

下の方にドラッグして31日分を自動作成します。

f:id:xoblos:20170716112318p:plain

f:id:xoblos:20170716112334p:plain

この例は1か月分ですが、どんどん下にドラッグしていけば、12ヵ月分でも3年分でも作成することができます。「セルの書式設定」で、日だけの表示、年月日表示、右寄せなど、お好みの表示形式にしましょう。カレンダーができたら、先頭の方の行に表タイトルを入れたり、項目名見出しの行を入れたりと、体裁を調整します。

後は、xoBlosのExcelインポート機能を使って、縦軸の年月日を目印に、データを挿し込み入力したり、加算集計していく仕組みを作るだけで、効果的なソリューションが開発できてしまいます。以下の参考記事を参照してください。

参考記事

縦軸も横軸も「禁」さえ覚えておけば大丈夫
http://xoblos.hatenablog.jp/entry/2017/07/13/031411

基本入力データから繰り返し数を得る

中間処理を用意。

f:id:xoblos:20170713191307p:plain

前回記事で、繰り返し回数は一意にすることを記述しました。2つ方法を挙げたうち、今回は「基本となる入力データから繰り返しの要素となる項目を一意にして抽出しておく」方法を列挙しようと思います。

お相手は上図――前回、問題となった「基本となる入力データの例③」です。

方法①(Excel 関数)

中間処理用に、生成を 1手順増やします。無論、その後で主要情報を取得するので抽出も 1手順――計 2手順の追加になります。xoBlos は基本、「抽出/生成」の組み合わせです。復習は、こちらの記事でお願いします。

f:id:xoblos:20170714113516p:plain

「事業所コード」と「売上」は元々あったデータ項目。そこから更に、「回転数」という処理用の列を設けます。抽出の際にはこの 3列を纏めて抽出します。

「回転数」の列に設定されている関数の意味は、自身のセルの直上~セル「C1」までの全体を見て、自身と同行の「事業所コード」を探します。そしてもし見つかれば空白、見つからなければ事業所コードをそのままあて込みます。

ここまでくれば簡単ですね。後は「回転数」が空白でないものを抽出すれば、「3回転させる」ことを伝えられる XML ファイルの完成です。

方法②(xob の標準手順)

f:id:xoblos:20170714113532p:plain

xoBlos は、取り扱いデータに様々な法則を持った数値を採番することができます。上図のように『抽出データ番号』という手順を使います。

様々な法則‥‥ということは、それを事前に定義しなければなりません。上図手順に設定されている各内容は、以下の詳細になります。

f:id:xoblos:20170714113524p:plain

「抽出データ.xml」。基本となる入力データの例③のデータを単純抽出したものです。

f:id:xoblos:20170714113540p:plain

「xmlNumber.xml」。法則を示すのに、新たに作成する必要があります。定義ファイルなので制御シートなどと同じところに格納するのが望ましいですね。

f:id:xoblos:20170714113546p:plain

「回転数特定.xml」。法則定義を見て、「事業所コード」毎に採番を行っているものが項目として追加されています。データが必ずある「1」を抽出条件にすれば一意の情報が取れそうですね。

Excel の関数が苦手な方は、この XML を作り、中間で「生成~抽出」を行ってください。

繰り返し処理

繰り返し数の変動対応。

xoBlos は抽出や生成、様々な手順を組むことができますが、いずれの手順も繰り返し行わせることができます。では繰り返しの数はどう決めるのでしょうか。‥‥週次の情報を取り扱うなら「7回」、月次の情報を取り扱うなら「12回」、十二支でも「12回」、十干なら「10回」、徳川将軍家なら「15回」‥‥。回数が徹底しているのであれば、そのまま固定設定するだけで決まります。

では、「処理対象となったデータの件数分回したい」という場合にはどのように設定すれば良いでしょうか。

f:id:xoblos:20170713191257p:plain

回数は一意の情報であることが望ましいです。重複した情報があると、無駄に繰り返し処理が行われてしまいます。この情報は、事前にマスタファイルを用意+抽出しておくか、基本となる入力データから繰り返しの要素となる項目を一意にして抽出しておくかの、いずれかになります。しかしいずれも抽出結果は、上図のような XML ファイルになります。今回は前者の方法で‥‥抽出ファイルは「事業所マスタ.xml」と付けておきましょうか。

f:id:xoblos:20170713191430p:plain

上図、『3.手順の繰り返し実行の設定』節『b.設定』項で、「...」となっている所をクリックすると、下図のような設定ウィンドウが新たに出力されます。

f:id:xoblos:20170713212721p:plain

事前に 3件のデータを抽出した「事業所マスタ.xml」を設定しているので、繰り返し数を固定設定しなくても「3回」繰り返すことを xoBlos は判断することができます。無論、別の機会で実行して対象が 5件であった場合は、何も設定変更することなく、「5回」繰り返されることになります。

繰り返し項目の活用とは?

変動する繰り返し数は、前節で処理対象となったデータの件数によって定まることは明らかになったと思います。ということは、1データの中に複数の情報を持っていても良いことになりますね。その情報達は、参照先のブック/シート/値の特定に、活躍させることができます。

基本となる入力データの例①

f:id:xoblos:20170713191318p:plain

上図は、「Data.xlsx」という不確かな名前のブックに、「東京事業所」「大阪事業所」「名古屋事業所」という確かな名前のシートを持った例になります。

基本となる入力データの例②

f:id:xoblos:20170713191329p:plain

こちらは打って変わって、「東京事業所.xlsx」「大阪事業所.xlsx」「名古屋事業所.xlsx」という確かな名前のブックに、「Sheet1」という不確かな名前のシートを持った例になります。

①②共に、物が違いますが主要情報は持っているようですね。抽出する時は、その確かな情報部分も、抽出しておくことで参照元との紐付けを助けることに繋がります。

f:id:xoblos:20170713191605p:plain

①はシート名が確かな情報なので、抽出キーワード『対象セル』に「%SheetName」といれます。これにより、抽出している時点のシート名を保持することができます。

f:id:xoblos:20170713191554p:plain

②はブック名が確かな情報なので、抽出キーワード『対象セル』に「%BookName」といれます。これにより、抽出している時点のブック名を保持することができます。

f:id:xoblos:20170713191621p:plain

①②共に、上図のように出来上がる XML ファイルの内容は同じです。

基本となる入力データの例③

f:id:xoblos:20170713191307p:plain

さて上図は、ブックにもシートにも主要情報が窺えません。これは取り敢えず抽出するしかなさそうですね。

f:id:xoblos:20170713191614p:plain

生成してみよう。

前節のいずれの例も、「事業所コード」という主要情報を持っていました。冒頭の「事業所マスタ.xml」と紐付ける準備は整っていますね。しかし基本入力ファイルに相当する XML ファイルの形態によっては、都度処理対象とするデータの特定方法が変わります。

f:id:xoblos:20170713191441p:plain

生成キーワード『高度選択条件』は、処理対象を限らせることができます。しかしどういうデータがくるかも見えない中、固定の設定は行えません。‥‥仮に 100パターンの値がくると分かっていても、『高度選択条件』を 100行設定することは現実的でありません。普段、設定値を記述するセル「C13」を覚えておきながら、進めていきましょう。

f:id:xoblos:20170713212315p:plain

xoBlos には、作成した制御シートの内容を一時的に書き換える機能があります。先程のセル「C13」に値をいれるようにしていますね。

そして「$(item:"項目名")」は予約変数であり、繰り返し回数を決める XML ファイルにある項目を保持しています。つまり、「事業所マスタ.xml」には「事業所コード」と「事業所名」があって、繰り返しの都度、それら値を活用することができるのです。

これにより、上図設定から1回目は「東京事業所」として生成処理を行うが対象データは『事業所=東京事業所』のものに限らせる、2回目は「大阪事業所」として生成処理を行うが対象データは『事業所=大阪事業所』のものに限らせる、3回目は‥‥。といった繰り返し処理が実現することになります。

変数を色々なところで使ってしまおう。

「$(item:"項目名")」は変数――。変動するとは言え文字列が入っています。様々な部分に使い回してみましょう。

f:id:xoblos:20170713191630p:plain

上図は、基本データに使用している例です。もし抽出の段階で「東京事業所.xml」、「大阪事業所.xml」、「名古屋事業所.xml」と作成できたら‥‥。この設定だけで、XOB の『制御シートの書き換え設定』も、キーワード『高度選択条件』も必要なくなりますね。

f:id:xoblos:20170713191640p:plain

上図は、出力(生成結果)ファイル名に使用している例です。これは事業所毎にブックの出力を分ける‥‥ブック分割の実現になります!

結局「%BookName」と「%SheetName」は何であったの?

列挙した基本となる入力データはいずれも「事業所コード」がありましたが、万一その情報が無かった場合は「事業所名」で特定させるしか方法はありません。‥‥勿論、「基本となる入力データの例③」から「事業所コード」が無くなれば問題外になります。

xoBlos の開発効率を考えずとも、運用上ブック名やシート名のルール徹底化が如何に重要であるか、気付かせてくれるものですね。

フォルダを作成する(Designer)

フォルダを作成するだけの機能は、xoBlosの標準手順にはありませんが、以下の2種類の手法でフォルダを作成することができます。

ファイルコピーの手順を利用してフォルダを作成する

下図の例では、[WORK] フォルダにダミーファイルを置いておき、[201707] フォルダを作成してから、ダミーファイルをコピーしています。[コピー先のフォルダが存在しなければ作成しますか?]「はい」にしてください。

f:id:xoblos:20170713174207p:plain

作られたダミーファイルは、ファイル削除の手順で削除します。

f:id:xoblos:20170713174221p:plain

mkdirコマンドでフォルダを作成する

コマンドプロンプトmkdirコマンドディレクトリ作成コマンド)を外部アプリとして実行します。

f:id:xoblos:20170713174247p:plain

実行するファイル: %ComSpec%

コマンドライン引数: /c mkdir "作成するフォルダ名"

とします。この例では、作業フォルダを「.」(カレントフォルダ)にしていますので、xobファイルのあるフォルダの下に、「2017年8月」フォルダが作成されます。

%ComSpec% は、[環境変数] タブの一覧からダブルクリックして選択することもできます([実行するファイル]-▼ボタン [編集]-[環境変数] タブ)。

f:id:xoblos:20170713174306p:plain

2階層分類のマスターデータのパターン

xoBlosでは、マスターデータをExcelファイルで持たせる手法を多用します。

2階層でデータを分類することもよくあるかと思います。

  • 勘定科目と補助科目
  • 資金分類科目と資金科目
  • 空間軸(エリアと都道府県などの地区)
  • 時間軸(年と四半期など)
  • 組織軸(事業部と部課など)
  • 製品やサービスの分類
  • 原価配付用の部門階層

ここでは、「資金分類科目と資金科目」を例にして、マスター設計のパターンを並べてみます。目的やメンテナンス性を考えながら、設計パターンを採択しましょう。

f:id:xoblos:20170713073135p:plain

f:id:xoblos:20170713073152p:plain

f:id:xoblos:20170713073202p:plain

f:id:xoblos:20170713073215p:plain

f:id:xoblos:20170713073248p:plain

資金分類が同上(上に同じ)という表現で空欄を使用していたり、セル結合しているパターンでは、Excelから抽出(エクスポート)の制御シートで %UPPER を使用し、データを正規化しましょう。「データが無いときの値」キーワードに、%UPPER または %upper と記せば、同上(上に同じ)データを出力します。

この記事の例では2階層分類を解説しましたが、3階層以上の多階層の場合も、同様の原理でマスター設計ができますのでお試しください。