営業データ(7)

ほんまにだいぶ日が開いてしまいました。これから、少し更新してまた、繁忙期と秋の試験の近づいた頃に、お休みするような予定になるかと思います。
一時的に、すごい数見に来ていただいてるみたいで、少しは参考になってるのかな？とも思う今日この頃です。

さて、営業データ（６）の続きです。書き出した書き出したあとは、エクセルのマクロを使って、得意先別、請求先別、部門別、担当別にそれぞれソートかけて集計結果を出すようにします。（マクロについては、語り出すと長くなるので、ここでは紹介しないことにしますが）

で、出来上がってほっとしてたら、問題発生です。
どうも、作成したデータと、サーバ側との不一致があって、最終エクセルの集計がおかしいことになってしまっていました。

結局、sub担当割付.fp7だけではなく、得意先累計.fp7と、請求先累計.fp7とで担当者の割り当てられてないデータを、検索する必要がありました。

考えたらsub担当割付.fp7では、サーバに登録してある請求先の担当分しか読み込まないので、得意先についてはまったく検索ができないですよね・・・。

それで、一度読み込んだ得意先累計.fp7で、担当者の割当のないデータを検索して、割当がないデータがあれば、もう一度sub担当割付.fp7で得意先の割当を設定しなおして、もう一度、得意先累計.fp7を読み込むことになります。

念のため請求先累計.fp7でも同様のことをしてますが、これはなくても大丈夫かもです。

これが実際結構な手間で、もう少し簡単にならないものか・・・？。
大体下図のようなフローになります。これを自動化できるところは自動化して、もっと簡素化できないものかと思案中です。

新規の取引先や、担当の変更あった場合に、こまめに更新かけるようにしておけばいいんでしょうけど、時々私のところに書類が回ってこないこともあるので、そのへんの業務の流れからやり直さないとならないかもです。

ファイルメーカー以前の問題です・・・(^_^;)

営業データ(6)

やっと本題の請求先と得意先のデータの取込＞加工＞書出しの部分です。

はじめは、月計売上と累計売上（1月度から12月度）を別けて、請求先と得意先のデータを同じファイルで読み込んで作っていました。

しかし、作りこんでいくうちにややこしい問題がでてきました。たとえばどれをキーにして、請求先、得意先それぞれの月計売上と累計売上を結合するかなどの仕組みがかなり煩雑になってきてしまいました。

しばらくお手上げ状態になっていたのですが、家の風呂に入ってるときにふと思いついたのが、「最終的に請求先と得意先のファイルを作るんやったら、初めから別々のファイルにすればうまくいくかも♪」というものでした。さっそく、月曜日出社と同時に、今までやっていたファイルを全部作り直しました。

しかし、問題はこれだけじゃありませんでした。サーバから取り込むときに、GROUP BYで請求先ごと、得意先ごとの商品の集計（同じ商品数、金額の合計）をしてから取込たかったのですが、以下のようなSQLを書いていました。ファイルの意図からすれば明らかに間違ったSQLなのですが、しばらく気が付ずに使っていました。これじゃ思うように、集計結果を出せないわけです。

★間違って使っていたSQL★

SELECT
"販売テーブル"."販売日付",
"販売テーブル"."販売NO",
"販売テーブル"."行NO",
"販売テーブル"."部門コード",
"販売テーブル"."担当者コード"
"販売テーブル"."商品コード",
"販売テーブル"."得意先コード",
"販売テーブル"."請求先コード",
"販売テーブル"."売上数量",
"販売テーブル"."税抜売上金額",
"販売テーブル"."原価単価"
FROM"販売テーブル"
WHERE
("販売テーブル"."伝票区分"='1')
AND("販売テーブル"."販売日付">='20051221'AND"販売テーブル"."販売日付"<='20061220')
GROUPBY
"販売テーブル"."販売NO"+"販売テーブル"."行NO",
"販売テーブル"."販売日付",
"販売テーブル"."販売NO",
"販売テーブル"."行NO",
"販売テーブル"."部門コード",
"販売テーブル"."担当者コード",
"販売テーブル"."商品コード",
"販売テーブル"."得意先コード",
"販売テーブル"."請求先コード",
"販売テーブル"."売上数量",
"販売テーブル"."税抜売上金額",
"販売テーブル"."原価単価"

間違ってるところ

１）販売NOと行NOの組で、グループ化をしてる。（意図を反映していない）
２）不要な項目まで含めている。（いつものです・・・）
３）集計をかけてない。（これが最悪です）

１）では、伝票ごとの集計になってしますね。これではなんのこっちゃらです。

２）SQLの基本ですが、「SELECT句の計算以外のフィールドをGROUP BYにも記述する」というのがあります。GROUP BYに記述されるとAND条件で扱われるようなので、１項目でも違うと別データとして扱われてしますので、意図のとおり集計されません。ですので、最小限の項目で読み込むのがベストです。その他の項目は、FileMaker側で後からリレーションでもって来るようにします。

３）については、もう馬鹿としか良いようがありません。SUMで集計かけもしないの集計できるわけないです。はい。

☆最終的に出来上がったSQL（得意先月計用）☆
（我ながら、こなれたSQLになりました♪）

SELECT
"販売テーブル"."商品コード"+"販売テーブル"."得意先コード",
"販売テーブル"."得意先コード",
"販売テーブル"."商品コード",
SUM("販売テーブル"."売上数量")"売上数量",
SUM("販売テーブル"."税抜売上金額")"売上金額",
SUM("販売テーブル"."原価単価")"原価合計"
FROM"販売テーブル"
WHERE
("販売テーブル"."伝票区分"='1')
AND("販売テーブル"."販売日付">='20050921'AND"販売テーブル"."販売日付"<='20051020')
GROUP BY
"販売テーブル"."商品コード"+"販売テーブル"."得意先コード",
"販売テーブル"."得意先コード",
"販売テーブル"."商品コード"
ORDER BY
"販売テーブル"."得意先コード"

上記と同様に、他のファイルも作り直し、

請求先累計.fp7（月計、累計込み）
得意先累計.fp7（月計、累計込み）

というファイルを作り、そこにデータを読み込むことで、比較的すっきりリレーション等も整理できました。

結局は最初の設計に問題があったということでした。気がつけばなんのことはないなということばかりでしたが、やってみないとわからないということで、遠回りも無駄じゃなかったんだと思います。

営業データ(5)

あと余談というか、ちょっとしたことなのですが、エクセルで集計をかける際に

担当者コードと担当者名
請求先コードと請求先名
得意先コードと得意先名
部門コードと部門名

など、コードと名前をつなげて一つの言葉にして、それをもとにソートしてから集計をかけます。

なぜそんなことをするかと言えば、下の画像のように何の集計だか分からなくなるからです。どうしても、コードと名前をつなげておく必要があります。

また、１度やってみると分かると思いますが、エクセルで集計をかけた際に、コードが長いととてみとても見づらくなります。そこで、短くできるものは、できるだけ省略しておくほうがベターです。

このコードと名前をくっつける作業をエクセルでやるかどうかですが、FileMakerでやっておいた方が、楽です。エクセルでマクロを組む際に、作業工程が少なくて済みます。

部門コードは、6桁から2桁へ、担当者コードも、6桁から2桁へ短縮しました。
請求先と得意先のコードはどうしても短くできそうもなかったので、しかたなくそのまま使用することにしました。

営業データ(4)

sub担当割付.fp7の運用上の問題は、取引先が増えた、担当が変わったといった際に、サーバのデータと、FileMakerの担当者マスタ側との情報の不一致がないように、なんらかの仕組みを作らないとならないことでした。

１）取引先が単純に増えた場合

過去に１回でも取引があったところは、削除されることはないので、減ることはありえません。
sub担当割付.fp7に、サーバからの読み込み用の「サーバ更新テーブル」と、そのデータと各担当ファイル（main担当、sub担当）を結合する「メインテーブル」を作ってあります。

「サーバ更新テーブル」に、担当者というフィールドをつくり、そこへは、SQLで読み込むのではなく、「メインテーブル」から担当者をルックアップでもってくるようにしました。そうすると、新しく増えた取引先のところだけ、担当者が表示されません。

そして、表示されないところだけ検索して抽出すれば、得意先コード、請求先コードがわかりますので、それを元に必要な項目を入力します。これも、アナログ的でもうひとつしっくりこないのですが、とりあえずこんな運用方法です。

これだと間違えが多くなりそうなので、今、もっと良い方法を考え中です。

２）担当が変わった場合

これは、変更があった際にわたしにちゃんと知らせてね、とお願いするしかないですね。
むっちゃアナログなやり方ですが、現状これくらいしかやれることがありません。
別にサーバ立ち上げて、営業からもアクセスできるようにして、変更してもらう方法もありますが、そこまでは予算でないですし、そこまで大ごとにもしたくないです。

よっぽどめんどくさくなったら、業務の基幹システムに組み込んでもらうか～！

営業データ(3)

SQLとしては、あっさりしたものです。今までで一番簡単だと思います。見直しの必要もないですね。(^^ゞ

SELECT
"得意先マスタ"."得意先コード",
"得意先マスタ"."請求先コード",
"得意先マスタ"."得意先略称",
"得意先マスタ"."担当者コード"
FROM "得意先マスタ"

この得意先マスタには、請求先（おおむね本社）の担当者しか割り当てられていないので、得意先（営業所別、地区別）の担当者情報を組み合わせないとなりません。

営業部長にFileMakerで作った「得意先割当」というファイルを渡して、900件ほどの取引先の営業所、地区ごとの担当を割り振ってもらいました。なるべく、間違えのないように、営業の名前をプルダウンリストにしてなるべく部長の手間を省く工夫をしました。

フィールドとしては、

請求先コード、請求先名、得意先コード、得意先名、担当者コード、担当者名

のみの、簡単なものです。
担当者コードをクリックすると、担当者コードと担当者名を組み合わせたリストを表示するようにして、該当担当者コードを選ぶと自動で、担当者名にも名前が入ります。
こまごましたことですが、ここまでしないとやってくれないのが現実です。

SQLで読み込んだテーブル、請求先担当用のテーブル、得意先担当用のテーブルは、別々してあります。その上で、メインテーブルというテーブルを新たに作って、そこへ必要なデータをルックアップでもってきています。

メインテーブルのフィールド設定は↓の通りです。

ちなみに、各リレーションは↓です。

ここで面白いのは、メインテーブルsub用、メインテーブルmain用、メイン自己リレーションというテーブル３つは、まったく同じテーブルだということです。

リレーション項目が複数ある場合には、仮想テーブル（と呼ぶのでしょうか）・・・、いわゆるMacで言うところのエイリアス、Windowsでのショートカットのようなものをつくり、複数のリレーションを作ることができます。
この仮想テーブルを作らないで、複数の項目でリレーションをかけると、リレーションがAND条件になり正確な数字がルックアップでもってこれなくなります。

それと、自己リレーションかけるときも同様に、仮想テーブルをつかって、自分から自分へリレーションをかけます。なんか妙な風景ですが、慣れれば見やすくなりますね。

営業データ(2)

まずは、商品マスタの作成です。
これは、商品売上管理表、それから後に出てくる在庫表などにも関連するので、共通に使えるようにしました。今まで商品売上管理表に使っていたものと、差し替えて使っています。

SQLもあっさりしています。ただ読み込むだけですからね。

SELECT
"商品マスタ"."商品コード",
"商品マスタ"."商品名1",
"商品マスタ"."商品名2",
"商品マスタ"."税抜上代単価",
"商品マスタ"."商品分類1コード",
"商品マスタ"."商品分類2コード",
"商品マスタ"."商品分類3コード",
"商品マスタ"."名カナ",
"商品マスタ"."JANコード",
"商品マスタ"."原価単価",
"商品マスタ"."原価単価2",
"商品マスタ"."原価単価3",
"商品マスタ"."原価区分"
FROM "商品マスタ"

上記のSQLで、↓の部分は現在のところ不要です。

"商品マスタ"."税抜上代単価",
"商品マスタ"."商品分類1コード",
"商品マスタ"."商品分類2コード",
"商品マスタ"."商品分類3コード",
"商品マスタ"."名カナ",
"商品マスタ"."JANコード",

ただ、今後共通部品として使うことがあるかもしれないので、現在はそのままにしてあります。

でも、"商品マスタ"."名カナ"は使うことないやろな・・・。どう考えても使い道が思いつかない・・・

苦労したのは、勝手についてくるスペースを取り除くためのスクリプトのところでしょうか？数字だけのところは、問題ないのですが、文字列がからむものは、すべてSubstitute関数でスペースを削除することになりました。

データベースを設計する段階で、文字の型とか、文字数とか決めますが、指定した文字数以下だとスペースで埋めるでしょうか？詳しいこと分かりませんが、調べてみたらちょうど設定した文字数分だけスペースが入ってました。
でも、エクセルでデータ取り出すと、そのスペースがきれいになくなってるのが腑に落ちませんが。

営業データ(1)

やっと今やっているデータの処理に追いつきました。これからは、記憶に新しいことなので、すこしはまとまった報告ができると思います。

営業データと言うのは、取引先ごと、部門ごと、担当ごとの売上を締め日を基準にして、集計するものです。そして、エクセルは使えるけどFileMakerを使い慣れない営業員に配布するため、最終的には、エクセルのファイルにして配布します。

営業員の担当が、地区ごと（取引先の営業所＝得意先と呼んでます）の担当と、本社（請求が行くところ＝請求先と呼んでます）の担当とで2通りありますので、2種類のデータを作ります。しかし、SQL Server側のデータには、本社（請求先）の担当しかないのが大きな問題でした。

ソフトウェアベンダに依頼すれば15～20万円くらいの費用発生するような感じでした。担当を複数持たせるということは、得意先マスタの仕様変更だけでなく、標準で付いている実績を出す機能や、他の売上データなどにも影響があるかもしれませんので、そこそこ大事になると予想されます。

そこで考えたのが、FileMaker側で地区ごとの担当のファイルを作って、サーバ側の請求先データと結合させることでした。私の作業そのもものはたいしたことはないのですが、誰がどこの担当というのは営業部員にしか分からないので、営業に依頼して1,000件近い取引先の担当を割り振ってもらうのに、時間がかかりました。
なるべく入力しやすいように、リスト表示して入力の手間を省くよう工夫したつもりですが、なにせ件数が多いのでかなり苦労したようです。

営業は、FileMaker Pro6しか使えない環境でしたので、次に大幅に変更があったときには、再度ファイルを読み込みなおさないとならない手間はありますが、年に１度くらいのことなのでそのへんはなんとかなると思います。

でも、同じ環境で同じバージョンのFileMaker使えると便利ですね・・・
予算降りへんかな～・・・

さて、全体的な作業の流れは、下記のような感じになります。

１）新しい商品が増えてる場合があるので、商品マスタの作成。
２）取引先の増減や、得意先担当の変更がある場合があるので、取引先マスタの作成。
３）請求先別の当月度の売上と、同期の累計の売上読込ファイルの作成。
４）得意先別の当月度の売上と、同期の累計の売上読込ファイルの作成。
５）請求先データと得意先データをエクセルデータへ書き出しのための仕組み作り。
６）エクセルのマクロ機能を使って、データを集計するための仕組み作り。

個々の内容については、後日に詳しく書かせてもらいますが、１）と２）はそれほど時間を取られませんでした。今までやってきたことの応用でほとんどなんとかなりました。
問題は、３）と４）と５）でした。

特に５）に思いのほか時間がかかり、土日も含めて1週間ほど悩みに悩みました。結局私の単なる勘違いによるSQLのミスが原因だったのですが、そこに至るまでが大変でした。

(o_ _)oドテッ!

試作を重ねて、↓の画像のように6種類の試作品を作りました。

それぞれ、リレーションの仕方、集計の仕方が微妙にことなります。
やれやれ・・です・・(^◇^;)

SQLもいつのまにか、↓こんなに書き換えてました。