ケンジー（Ｋｅｎｇｇｙ）とＩＴ

はじめまして、Ｋｅｎｇｇｙこと畑本賢治です。以前「ケンちゃんの世界」というＨＰを公開しておりましたが、無料のＨＰだったためプロバイダーの意向より閉鎖となりました。一時期は、年齢も年齢なのでこのまま埋没してしまおうとも思いましたが、まだやり残したことが多くあるのではないかと再度公開することにしました。どうぞよろしくお願いいたします。前回はＥｘｃｅｌＶＢＡを中心にＣＧＩやＪａｖａＳｃｒｉｐｔでいろいろな動きのあるページを作成していました。その中で人気のあったページは、プログラミングを使わずにＥｘｃｅｌで在庫管理表や売上管理表・勤務表等を作成するページでした。あれからどれ位の時が流れたたのだろう、知らぬ間に五十路を超えていました。若かれし頃のニクックネームの「ケンちゃん」より「ｋｅｎｇｇｙ（ケン爺ーいやケンジー）」に改名です。

2012年7月2日月曜日

Ｋｅｎｇｇｙとエクセル

私とエクセルの出会いは、会社内でワープロからパソコンWINDOWSに移行されたのがキッカケでした。ワープロでロータス１－２－３を使っていたので、エクセルの違和感はありませんでした。
ただ単に表やグラフを作成するだけでは飽き足らず、作業の自動化（＝プログラム）に取り込むようになりました。これが俗にいうＥＵＣ（ＥｎｄＵｓｅｒＣｏｍｐｕｔｉｎｇ）というやつです。エクセルではＶｉｓｕａｌＢａｓｉｃというプログラム言語を使います。昔勉強したＢａｓｉｃを改良した感じ（より人間の書く文章に近い）のプログラム言語です。難を云えば、すべて英語の文章になります。でも難解な単語は殆どありませんので、すぐに慣れると思います。

同僚からも、こをいう作業を自動化してほしいという依頼があり悪戦奮闘することもしばしばありました。そんな中、このプログラムはもう少し工夫すれば汎用性が出でくるのではないかという声が出るようになり、その声に押されながら更なる追求を始めました。
そしてその成果を発表することにしました。広く多くの方々に使って頂くためです。

現在のところ、ベクター（Ｖｅｃｔｏｒ）に十数本登録しています。
すべてフリーソフト（無料）です。ご自由にダウンロードしてください。

（ベクターのアドレス）
http://www.vector.co.jp/vpack/browse/person/an030525.html

2012年7月1日日曜日

クラウド（GoogleSpreadsheet）で株価情報を取得する

今回はクラウドの一翼を担うGoogleSpreadsheetで、株価情報を取得したいと思います。

＊関数ＧｏｏｇｌｅＦｉｎａｎｃｅについて

GoogleSpreadsheetのみで使用可能な関数、GoogleFinanceはシート上に株価情報を取込む時に使用します。

https://support.google.com/docs/bin/answer.py?hl=ja&answer=155178
（GoogleFinanceの日本語ヘルプ）

◎関数公式
=GoogleFinance（銘柄名及び銘柄コード、株価や出来高等）

例1：トヨタ自動車の株価を取込みます。
　　　=GoogleFinance (7203,"price")
・銘柄コード7203：トヨタ自動車
・price：株価

例2：パナソニック（旧松下電器）の出来高を取込みます。
　　　=GoogleFinance (6752,"volume")
・銘柄コード6752：パナソニック
・volume：出来高

例3：ソニーの今日から30日前の終値を取込みます。
　　　=GoogleFinance (6758,"close",Today()-30,Today())
・銘柄コード6758：ソニー
・close：終値
・Today()：今日
・Today()-30：今日から30日前

＊値の種類
・open：始値
・close：終値
・high：高値
・low ：安値
・volume：出来高
・all ：すべて

注）期間を設定する場合は、開始日、終了日の順に入力します。
　　　　=GoogleFinance（銘柄名及び銘柄コード、株価等、開始日、終了日）

例4：NECの2012年5月1日から2012年6月20日までのすべて（始値・終値・安値・高値・出来高）を取込みます。
　　　=GoogleFinance (6701,"all","2012/05/01","2012/06/20")
・銘柄コード6701：NEC（日本電気）
・all：すべての値

（画面をクリックすると拡大します）

そこで、「get_stock_info」という名前のファイル作成しました。

（画面をクリックすると拡大します）

セル番地Ａ１に、「=ＧｏｏｇｌｅＦｉｎａｎｃｅ（Ｈ４、Ｉ４、Ｊ４、Ｋ４）」と入力します。いままで直接入力していたところ、わかり易いように各セル（Ｈ４～Ｋ４）に分けます。

Ｃｏｒｄ欄（Ｈ４）には銘柄コード等を、Ｖａｌｕｅ欄（Ｉ４）には値の種類（ｏｐｅｎ～ａｌｌ）、Date：Start（Ｊ４）欄は開始日、Date：End（Ｋ４）欄には終了日を入力します。

（画面をクリックすると拡大します）

またValue欄は６種類しかないので、リスト表示しました。

為替レートの情報を取得したいときは、米ドルの場合「Currency：usdjpy」という文字をＣｏｒｄ欄（Ｈ４）に入力して下さい。ユーロ等も取得できるように表にしておきました。

最後に、このサンプルをGoogleのテンプレートギャラリーに公開しました。

[テンプレートギャラリー]－[公開テンプレート]から検索「get_stock_info」で表示されます。

この下のアドレスをクリックして下さい。

https://drive.google.com/templates?q=get_stock_info&sort=hottest&view=author

（参考ＨＰ）

・「ＶＢＡを使わずにＥｘｃｅｌで請求書・納品書を作成する」

・「ＶＢＡを使わずにＥｘｃｅｌで在庫管理表を作成する」

・「ＶＢＡを使わずにＥｘｃｅｌで在庫管理表を作成する（２）」

・「ＶＢＡを使わずにＥｘｃｅで売上ｌ管理表を作成する」

2012年6月22日金曜日

クラウドで「ＡＢＣ分析」表を作成する

今回はクラウドの一翼を担うGoogleSpreadsheetで、在庫管理手法の１っ”ＡＢＣ分析”表を取り上げたいと思います。

☆ＡＢＣ分析とは
ABC分析とは、「重点分析」とも呼ばれ在庫管理などで原材料、製品(商品)等の管理に使われる手法である。製造業などで何千・何万とある原材料・製品を管理運用するうえで、管理工数的にも資産運用上もより効率的に管理するために原材料・仕掛り・製品をそれぞれの所要金額の大小でクラス分けし、それぞれに異なった管理手順を適用する。　その際考慮するのは単価ではなく、単価ｘ数量の金額である。言い換えると高額の物でも殆ど動きがないものより、低価格でも大量に動く材料のほうが重要度が高いということである。この金額を大きいほうから並べていくと最初の10~20％の点数で所要金額の80~90％を占める、逆に金額の低いほうは点数こそ多いがその総金額が全体に占める割合は僅かである。

A；重要管理品目、B：中程度管理品目、C：一般管理品目　に仕分けをする為の分類である。

クラスの分割は概ね、A　10％、B　20％、C　70％のような割合で分類される。　点数のすくないAクラスを分析管理することが対金額効果が高い。
（ウィキペディアより）

☆分析表の作成

まず上図のような、商品があるとします。在庫金額は、原価Ｘ在庫数で求められます。

[データ]－[範囲を並べ替え]を使用して、在庫金額の降順（大きい方順）に並べ替えます。

↓

（注）降順は、Ｚ→Ａのラジオボタンを選択してください。

並べ替えると、上記画像の背景色が青色の範囲のようになります。

次に、並び替えたデータを新しいワークシートにコピーします。

在庫金額の一番下の列に合計欄を作り、 "=SUM(B2:B11)" を入力します。

続いて在庫金額の横に、金額構成比・累計構成比・ランクの項目を新設します。

（１）金額構成比　　商品個別の在庫金額　÷　在庫金額の合計　×　１００　

もし商品がc002なら、 "=B7/$B$12*100" となります。

注：　$（ドルマーク）はドラッグする時に、ドルマークの付いた文字や数字を固定します。　　　　　　　　＜　絶対参照といいます　＞

（２）累計構成比　　商品の金額構成比　＋　直上の累計構成比

商品ｃ003の累計構成比を表す数式はＣ３＋Ｄ２となり、商品a002ならＣ４＋Ｄ３です。

（３）ランク　　累計構成比の値＜＝70 → A ： 70＜値＜＝80 → B ：値＜＝81 → C

「累計構成比の値が７０以下ならランクはＡ、７０を越え８０以下ならランクはＢ、　

８１以上ならランクはＣとします。」

注）以上・以下はその数値も含みます。

商品ｃ003のランクを表す数式は =IF(D3<=70,"A",IF(D3<=80,"B","C")) です。

商品a002なら =IF(D4<=70,"A",IF(D4<=80,"B","C")) ということになります。

今回はABCの分岐点を70・80・90で行いましたが±10の範囲で数値はかえられます。

＊このサンプルケースで解ったこと

↓

最後に、このページのサンプルをGoogleのテンプレートギャラリーに公開しました。

[テンプレートギャラリー]－[公開テンプレート]から検索「ABC分析表」で表示されます。

この下のアドレスをクリックして下さい。

https://drive.google.com/templates?q=abc%E5%88%86%E6%9E%90%E8%A1%A8&sort=hottest&view=author

（参考ＨＰ）

・「ＶＢＡを使わずにＥｘｃｅｌで請求書・納品書を作成する」

・「ＶＢＡを使わずにＥｘｃｅｌで在庫管理表を作成する」

・「ＶＢＡを使わずにＥｘｃｅｌで在庫管理表を作成する（２）」

・「ＶＢＡを使わずにＥｘｃｅで売上ｌ管理表を作成する」

2012年6月14日木曜日

クラウドで在庫管理表を作成する（２）

「クラウドで在庫管理表を作成する」の続編として、このページを公開致します。

前回の在庫管理表は、以下ようなものでした。

入出庫表にデータを入力すると、在庫一覧表の現在庫数が自動的に更新される。
例えば、Aという商品が本日10個出庫されたとします。このことを入出庫表にデータ入力するだけで、在庫一覧表の現在庫数が自動的に－１０少ない数字に更新されるというものです。

↓

（今回の改善点）

前回の在庫表では、現在庫数（入庫数　－　出庫数）のみを表示していましたが、それだけでは真の在庫状況は理解できません。

＊在庫状況をより詳細に表示するために以下の項目を入出庫表に追加しました。

（画像をクリックすると大きく鮮明になります）

・引当数とは納入先より注文（受注）が入ったが、まだ出荷日等の関係で出庫されていない(出庫予約）状態にある品物の数量。

・発注数とは仕入先に注文をしたが、まだ納期等の関係で入庫されていない(入庫予約）状態にある品物の数量。

受注によって引当された在庫はいうならばロックオン状態ですので、それを別の受注に使用できません（緊急の場合を除いて・・・）。ですから、従前の現在庫とこの度設定する在庫を区分します。

また、以前の入出庫表では発注状態（発注しているかどうか）を表示する欄がなく、二重発注する可能性がありました。

これら２項目を追加することで、入出庫の前段階も理解できます。

＊在庫一覧表の方にも、新たに項目を設定します。

・有効在庫数＝現在庫数　－　引当数　（実際に使用可能な在庫数）

・有効残数＝（現在庫数+発注数）　－　引当数　（発注の要否に必要）

　　　　　　　　　　　　　　（画像をクリックすると大きく鮮明になります）

上の画像で、表の６行目商品コード：Ｂ００３商品番号：Ｂ－３０００－６６６の欄を見てください。

現在庫数１５となっているので、１５全部使用できるかというそれはできません。引当（受注）が１０あり、実際に使用できる在庫数は５となります。つまりこれが有効在庫数なのです。

商品の発注においてもそうです。現在庫数の増減だけで管理していたものが、新たなファクターである有効残数で行うことにより発注のタイミングが正確なものとなります。

もう一度、表の６行目商品コード：Ｂ００３商品番号：Ｂ－３０００－６６６の欄を見てください。

現在庫数１５となっていて発注点も１０なので、発注する必要ありません。ところが引当数が１０で、有効な数量は５しかありません。このタイミングで発注となります。

＊引当数の設定方法

↓

　　　　　　　　　　　　　　　　　　　（引当数の条件配列）

=DSUM(入出庫表!$C$7:$M$100,入出庫表!$K$7,$P$7:$P$8)

引当数の設定は、前回の在庫管理表作成のページでもお話したように、関数DSUMで行います。

DSUM（全データの範囲、集計するフィールド（ここでは引当数）の番号か文字列、条件配列）

条件配列：商品コード、A001　（上記画像参照）

＊発注数の設定方法

↓

　　　　　　　　　　　　　　　　　　　（発注数の条件配列）

=DSUM(入出庫表!$C$7:$M$100,入出庫表!$L$7,$R$7:$R$8）

DSUM（全データの範囲、集計するフィールド（ここでは発注数）の番号か文字列、条件配列）

条件配列：商品コード、A003　（上記画像参照）

＊入出庫処理

　◎入庫処理

　　商品が発注　→　入荷したときは、発注数の欄をゼロ（又は空欄）にして、入庫数の欄に数字を入力します。例えば発注数１０の商品が入荷した場合は、発注数欄の１０をゼロ（又は空欄）にして、代わりに入庫数の欄に１０を入れます（下記画像参照）。

　◎出庫処理

　　商品が引当　→　出荷したときは、引当数の欄をゼロ（又は空欄）にして、出庫数の欄に数字を入力します。例えば引当数５の商品が出荷した場合は、引当数欄の５をゼロ（又は空欄）にして、代わりに出庫数の欄に５を入れます（下記画像参照）。

　（注）商品が入出庫した後に受注日や発注日が知りたい場合は、備考欄に何月何日受注・発注と記入されではいかがでしょうか。

何故このような複雑なシステムを作成するかというと、その日に発注した商品が即入荷されて、即日すべて出荷されるなら、入荷数と出荷数さえ管理するればこと足ります。ことろが、実際は商品の入荷には納期がかかるものがあります。また受注においても、納入先の事情（計画）というものがあって納入日を指定してきます（どの会社も最小限の在庫数で対応しています）。それでこのようなもの作成しました。

最後に、このページのサンプルをGoogleのテンプレートギャラリーに公開しました（もちろん無料です！！）。

[テンプレートギャラリー]－[公開テンプレート]から検索「zaiko2」で表示されます。

この下のアドレスをクリックして下さい。

https://docs.google.com/templates?q=zaiko2&sort=hottest&view=public

（参考ＨＰ）

・「ＶＢＡを使わずにＥｘｃｅｌで請求書・納品書を作成する」

・「ＶＢＡを使わずにＥｘｃｅｌで在庫管理表を作成する」

・「ＶＢＡを使わずにＥｘｃｅｌで在庫管理表を作成する（２）」

・「ＶＢＡを使わずにＥｘｃｅで売上ｌ管理表を作成する」

2012年6月3日日曜日

kenggyとLinux（リナックス）

私とLinux付き合いは、Windows95→Windows98に移行時に遡ります。

友人が新しいモデルのパソコンを買替えたので、不要となった機種を貰いました。
そのパソコンをどう使うか考えたのですが、Windowsとは違うＯＳ Linuxをインストールすることにしました。
LinuxはWindowsとは違って無償ですし、いろいろな種類もあるので楽しみでした。
・RedHat系・・・・・ redhat,vine,fedora
・Slackware系・・・・ plamo,slackware
・Debian系・・・・ knoppix,ubuntu
・BSD ・・・・ freebsd
と、みんな雑誌の付録のCD-ROMに入っていました。

一番最初、PlamoLinuxをインストールしてみたのですが、残念ながらうまくいきませんでした。その次に、FreeBSDをインストールしてみましたが、これまたPlamoLinuxと同じ結末となりました。なぜこの２っを選んだのかは、図書館で参考本（CD-ROM付き）を無償で借りられたからです。何が悪いのかネットで調べてみたら、Linux において往々にしてあることらしいのでが、機種によってうまくいったりいかなかったり相性があるみたいです。

気を取り直して、今度はVineLinuxをインストールすることにしました。これもまた、図書館で借りました。手引きを読みながら、手順通りに進めていくと今度はバッチリです。

その後、redhatLinuxをインストールすことにしました。たしかあれはVer8.0だったと思います。なかなか使い勝手良いOSでした。

これらは昔の話です。現在なら、KNOPPIX・ubuntu・fedoraなどのCDブート→USBブートが主流となりつつあります。

是非一度Linuxも使ってみて下さい。
ネットから簡単にダウンロードできます。

2012年5月25日金曜日

クラウドで売上管理表を作成する

今回、勤務表と在庫管理表に続き、売上管理表をGoogleSpreadsheetで作成したいと思います。

§　概要設計（まず、どんなものを作るのか？）

（１）月別の分類別の集計表とグラフを作成する

（２）月別の商品別の集計表とグラフを作成する

（３）月別の納入先別の集計表とグラフを作成する

と、この３点に集約します。

§　売上データの作成