力技_02：FileMakerからExcelワークブックをつくる（その５）

まずは想定する背景を整理しながらダミーデータ作成

　このサンプルでは、「日付」「支店名」「売上金額」の3フィールドのテーブルに乱数を使って1年分のダミーデータを生成しました。1レコード＝レジ会計1回と考えるといいでしょう。

　これをもとに支店ごとの月間売上集計と全体の集計を出していきます。

　以下のようなスクリプトで支店ごと、日付ごとにループを回して集計表を作っていきます。

　文業としては、なるべく全部のデータ処理をFileMakerで行って、帳票レイアウトの部分だけをExcelに任せるいマージです。

#月刊集計（支店別）をつくる

レイアウト切り替え [ 「売上DB」 (売上DB) ]

変数を設定 [ $支店リスト; 値:"銀座¶仙台一番町¶名古屋栄¶心斎橋¶渋谷¶表参道¶福岡天神" ]

変数を設定 [ $開始日; 値://指定された月の１日

Date ( G::G_月設定フィールド ; 1 ; G::G_年設定フィールド ) ]

変数を設定 [ $終了日; 値://開始日の月の末日

$開始日 + 31 - Day ( $開始日 + 31 ) ]

フィールド設定 [ G::G_Excelへ送るデータ; //支店数分のワークシートを含む新規ワークブックを作成listToTAB ( List ( "newBook" ; ValueCount ( $支店リスト ) ) ) ]

#支店でループ

変数を設定 [ $i; 値:1 ]

Loop

変数を設定 [ $支店; 値:GetValue ( $支店リスト ; $i ) ]

フィールド設定 [ G::G_Excelへ送るデータ; 

//ヘッダと全体の書式

List ( G::G_Excelへ送るデータ ;

listToTAB ( List ( "selectSheet" ; $i ) ) ;

listToTAB ( List ( "nameSheet" ; Year ( $開始日 )&Right ( Month ( $開始日 )+100 ; 2 )&GetValue ( $支店リスト ; $i ) ) ) ;

listToTAB  (  "width¶1¶8.71¶10.57¶10.57¶1¶1¶8.71¶10.57¶10.57"  )  ;

//タイトル

listToTAB ( "write¶1¶1¶1¶1¶月間売上集計表（支店別）" ) ; listToTAB ( "merge¶1¶1¶1¶8" ) ;

listToTAB ( "height¶1¶36" ) ;

listToTAB ( "vAlign¶1¶1¶1¶1¶xlCenter" ) ; listToTAB ( "hAlign¶1¶1¶1¶1¶xlCenter" ) ;

//ヘッダ

listToTAB ( "merge¶3¶1¶3¶2" ) ; listToTAB ( "merge¶4¶1¶4¶2" ) ; listToTAB ( "merge¶5¶1¶5¶2" ) ; listToTAB ( "merge¶3¶6¶3¶7" ) ; listToTAB ( "merge¶4¶6¶4¶7" ) ; listToTAB ( "merge¶5¶6¶5¶7" ) ;

listToTAB ( "borders¶3¶1¶5¶3¶xlContinuous" ) ; listToTAB ( "borders¶3¶6¶5¶8¶xlContinuous" ) ; listToTAB ( "hAlign¶3¶1¶5¶7¶xlCenter" ) ;

listToTAB ( "write¶3¶1¶5¶1¶対象店舗¶対象期間¶作成日" ) ;

listToTAB ( List ( "write";3;3;5;3;GetValue ( $支店リスト ; $i );"'"&Year ( $開始日 ) & "年"&Month ( $開始日 ) &"月" ; "'"&Get ( 日付 ) ) ) ;

listToTAB ( "write¶3¶6¶5¶6¶月間客数¶月間売上¶客単価" ) ;

//表

listToTAB  (  "borders¶7¶1¶22¶3¶xlContinuous"  )  ;

listToTAB ( List ( "borders";7;6;Day ( $終了日 )-15+7;8;"xlContinuous" ) ) ; listToTAB ( "write¶7¶1¶7¶8¶月日¶客数¶売上¶¶¶月日¶客数¶売上" ) ; listToTAB ( "hAlign¶7¶1¶7¶8¶xlCenter" ) ;

listToTAB ( "numberFormat¶8¶1¶22¶1¶m\"月\"d\"日\"" ) ; listToTAB ( "numberFormat¶8¶6¶23¶6¶m\"月\"d\"日\"" ) ; listToTAB ( "numberFormat¶8¶3¶22¶3¶#,##0" ) ; listToTAB ( "numberFormat¶3¶8¶23¶8¶#,##0" ) ;

"" ) ]

#日付でループ

変数を設定 [ $date; 値:$開始日 ] 変数を設定 [ $月間客数; 値:0 ] 変数を設定 [ $月間売上; 値:0 ]

Loop

エラー処理 [ オン ]

検索実行 [ 指定された検索条件: レコードの検索; 条件: 売上DB::売上日: 「$date」 AND 売上DB::本支店: 「$支店」 ]  [ 記憶する ]

エラー処理 [ オフ ]

変数を設定 [ $月間客数; 値:$月間客数 + Get ( 対象レコード数 ) ]

変数を設定 [ $月間売上; 値:$月間売上 + 売上DB::集計_売上金額 ]

フィールド設定 [ G::G_Excelへ送るデータ; 

//１日分の客数と売上を書き込む

Let ([

day = Day ( $date ) ;

x = If ( day ≤ 15 ; 1 ; 6 ) ;

y = If ( day ≤ 15 ; day+7 ; day-15+7)

] ;

List ( G::G_Excelへ送るデータ ;

listToTAB ( List ( "write" ;y;x;y;x+2;$date;Get ( 対象レコード数 );0+売上DB::集計_売上金額 ) ) ; "" )

) ]

変数を設定 [ $date; 値:$date+1 ]

Exit Loop If [ $date > $終了日 ]

End Loop

フィールド設定 [ G::G_Excelへ送るデータ; 

//ヘッダ集計値

List ( G::G_Excelへ送るデータ ;

listToTAB ( List ( "write" ;3;8;5;8;$月間客数;$月間売上 ; Round ( $月間売上 / $月間客数 ; 0 ) ) ) ;

"" ) ]

変数を設定 [ $i; 値:$i + 1 ]

Exit Loop If [ $i > ValueCount ( $支店リスト ) ]

End Loop

フィールド設定 [ G::G_Excelへ送るデータ; 

//１枚目のシートに移動

List ( G::G_Excelへ送るデータ ;

listToTAB ( "selectSheet¶1" ) ; "" ) ]

レイアウト切り替え [ 「月間集計を生成する」 (G) ]

　こうして作ったカスタムAppがどのように動作するのか、ここからは、動画デモをご覧ください。