kaihiV.0

操作データ編

小林誠一郎

平成24年11月

 

1  目次

2  「menu」「menu2」「手集金」

3  「台帳」

4  「台帳」口座振替データ

5  「check」 事前準備作業

6  「menu2」の画面

7  会員の加入、脱退

8  請求データ作成

9  口座振替の

全銀フォーマット

10 ふりカエルの操作手順

11 ふりカエルでデータ作成

12 ファイルの場所の詳細

13 生成データの確認

14 csvファイルの印刷物

15 銀行の詳細について

16 会費台帳へのデータ入力

17  台帳の確認、引き落とし金額合計の確認

18  個別台帳の印刷、手集金分の処理、口座引き去りの通帳を変えた場合(前期と後期で違う場合)

19  台帳の確認印刷、未入金事業所の確認印刷、手集金台帳の入力 

20  「menu2」の画面

21  会費台帳印刷の例

22  会費納入案内用のデータ作成

23  シート「menu」のマクロボタン

24  会費台帳の整理(年度末? 年度始め?)

25  その他の事項

26  非表示にしているシートの説明

27から  22ページの追加的説明(事前案内用データ、差込印刷用データの取り出し)

  

山川町会費台帳(kaihi.xls[689KB]kagin[32KB])  商工会標準版システムの会費口振替データを利用した会費台帳の整理

6月9月12月3月の年4回に分けて納付。会員340名程度。口座振替を利用しているのは80%程度。FDのデータ読込は特定のフォルダからのみ可能。その後、手集金の部分を加えて完成です。平成14年1月【公開日】2002/01/15

 

V2は年2回の納付になったもの(未公開)。菜の花商工会利用。

 

会費台帳V3daityo3.zip)会費台帳V3.xls FDの読込場所を選択指定できるようにしたもの。2000人用。

平成23年12月【公開日】2011/12/26

 

会費台帳V8daityo8.zip)会費台帳V.xls これまでは入金処理だけでしたが、合併後の処理は振替データの作成から案内書データなど、かつ金融機関も増えるので、本所で一括処理できる方法としての機能を加えた。平成24年8月【公開日】2012/08/01

  

9 振替者データ、集金者データ作成のマクロ、一部変更  この部分の説明書追加(27ページ以降) 平成24年8月【公開日】2012/11/20

このファイルは商工会の会費の請求入金を整理する会費台帳です。10枚のシートで構成されています。

(5枚は非表示にしています。このシートの説明は最後のページでしています。)

シート「menu

表紙の画面です。銀行のFDを読み込む作業、全体の表の印刷、表の表示非表示の指定行の入力セルなどがあります。

 

シート「menu2

 会費台帳の印刷方法の指定や、銀行提出用のFD作成のためのCSV作成、その前の銀行口座の入力整理などをします。

 

シート「手集金」 手集金した結果を入力するシート

A

B

C

D

E

F

I

J

K

L

M

N

O

P

Q

 

 

NO

NO2

コード

番号

屋号

支部

備考

年額

前期

日付

前期

日付

後期

日付

後期

日付

年間

合計

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

手集金する事業所を入力します。

A列、B列、C列、D列、E列、F列はシート「台帳」と同じです。G列はメモ欄に、H列は会費年額となります。

I列は前期に手集金した金額を入力します。J列には集金した日付を入力します。 (記入例 7/2

K列は前期に手集金した金額を入力します。L列には集金した日付を入力します。 (記入例 7/25)   

M列は後期に手集金した金額を入力します。N列には集金した日付を入力します。 (記入例 12/5

O列は後期に手集金した金額を入力します。P列には集金した日付を入力します。 (記入例 1/25

Q列は横の金額の合計が表示される式が入っています。

   規約による会費の払い込みは口座振替で年2回になりますが、手集金もあると思います。

   年4回の分割払いまで入力可能です。

口座振替で引落ができなかった場合は手集金しますが、その場合にもこのシートに入力します。

「手集金」の空いている行にコード番号(事業所コード)と屋号を入力し、集金した金額と日付を入力します。

 

シート「台帳」

A

B

C

D

E

F

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

NO

NO2

コード

番号

屋号

支部

機関

口座

番号

年額

前期

手集金

日付

手集金

日付

後期

手集金

日付

手集金

日付

年間

合計

 

備考欄

 

未収

金額

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

A列は全体の通し番号、2000人までです。変更はしません。

B列は旧商工会地区の名称(支所名)です。2文字。

C列は町単位の連続コードで地区会員数の把握に利用します。年度ごとに変更入力します。

E列は事業所名。

F列は支所の中の支部名(地区名)です。

G列は会費の振替で利用する金融機関の略称名です。2文字。

H列は口座番号で銀行に提出されるデータで使用している銀行口座番号です。口座種別(1:普通預金、2:当座預金)+口座番号で全体で8ケタになります。同じ金融機関・支店であれば口座番号が同じになることはないですが、金融機関や支店が異なる場合には、同じ口座番号になる可能性があります。銀行に提出するデータには金融機関名がありますが、このファイルの口座番号にはその区別はありません。

新規に口座振替をする場合は、必ずこの口座番号の重複がないかを調べる必要があります。口座番号が重複した時の対応は手作業でのデータ修正が必要になる場面が出てくることになります(25ページに対処方法を記載)。

I列は会費の年額。

J列は前期に口座振替した金額が表示される式が入っています。口座番号を基に、シート「check」のB列から表示します。

K列は前期の手集金分の金額が表示される式が入っています。コード番号を基に、シート「手集金」のI列から表示します。

L列は前期の手集金分の日付が表示される式が入っています。コード番号を基に、シート「手集金」のJ列から表示します。

M列は前期の手集金分の金額が表示される式が入っています。コード番号を基に、シート「手集金」のK列から表示します。

N列は前期の手集金分の日付が表示される式が入っています。コード番号を基に、シート「手集金」のL列から表示します。

O列は後期に口座振替した金額が表示される式が入っています。口座番号を基に、シート「check」のF列から表示します。

P列は期の手集金分の金額が表示される式が入っています。コード番号を基に、シート「手集金」のM列から表示します。

Q列は期の手集金分の日付が表示される式が入っています。コード番号を基に、シート「手集金」のN列から表示します。

R列は期の手集金分の金額が表示される式が入っています。コード番号を基に、シート「手集金」のO列から表示します。

S列は期の手集金分の日付が表示される式が入っています。コード番号を基に、シート「手集金」のP列から表示します。

T列は横の金額の合計が表示される式が入っています。

U列はメモが入力できます。

V列はH列からS列を減算する式が入っています。年度会費の未収分になります。口座振替の後期分の請求金額にもなります。

2005行から2010行にはメモ欄を設置しています。

今は、旧商工会ごとの小計を算出する式を入力していますが、この式は毎年入力しなおす必要があると思います。

2012行から2024行にはメモ欄を設置しています。

今は、金融機関ごとの小計を算出する式を入力しています。自由に書き換えてください。

 

年度途中からの加入者は予備の欄に修正入力をして行きます。年度途中での脱退者についての特別な処理は必要ありません。

屋号のセルの色を黄色に変えたりして区別すると分かりやすいです。(振替口座入力開始から行ってください。)

 

会員数以上の「台帳」の不要な行は2000行目まで非表示にします。(指定範囲の表示、非表示) シート「menu」に設置。

「手集金」の不要な行は450行目まで非表示にします。(指定範囲の表示、非表示) シート「menu」に設置。

 

通常は非表示にしていますが、W列以降には口座振替に必要なデータを入力する列が」あります。

 

シート「台帳」   口座振替データ

W

X

Y

Z

AA

AB

AC

AD

AE

AF

 

銀行コード

銀行名

支店コード

支店名     

種別コード

種別

口座番号

口座名義

新規コード

連続番号

 

数字

半角カナ

数字

半角カナ

1普通2当座

文字

数字

半角カナ30字

 0

 

 

 

利用金融機関、支店は次のとおり。    金融機関コードを調べるなら    http://zengin.ajtw.net/

















シート「check」       このシートは銀行から返却されたFDから取り込んだデータを格納するシートです。

A

B

C

D

E

F

I

J

K

L

M

N

口座

番号

金額

 

口座

番号

金額

 

口座

番号

金額

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

ABC列  前期口座振替結果を格納

EFG列  後期口座振替結果を格納

IJK列  FD2本目以降のデータを

         一時的に格納

A列、E列、I列は口座番号が入ります。 口座種別(1:普通預金、2:当座預金)+口座番号で全体で8ケタ。

B列、F列、J列は金額が入ります、

C列、G列、K列は処理結果の数値が入ります。 0は出来たもの。不能理由、1預金不足、2口座なし、3以降その他の理由。

MNOには2本目以降のデータ処理にカウントする一時的な計算式を入れるため利用します。.

事前準備作業

基幹システムの各商工会の会員コード(5桁から6ケタ)は合併しても変更はありません。

これまでの会員コードを尊重しながら合併商工会での新会員コードが必要になります。

基幹システムのコード番号にA商工会は1000000を加算、B商工会は2000000を加算、C商工会は3000000を加算等で会員コードを

ダブることのないようにします。これがD列の{コード番号}です。この番号が台帳の整理の基本になります。

シート「台帳」に基本的なデータを入力します。B列からI列。W列からAE列。シート保護をしていますので、シート「menu2」から移動します。

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 








会員の新規加入があった場合

 シート「台帳」に入力します。シート「menu2」の振替口座入力開始からシート「台帳」に入ります。

町や地区や金融機関の指定区分が効きます。指定した部分の一覧表が出ます。      

全地区の一覧名簿が出ます。   

 

入力済みの会員の一番下のあいている行を利用します。年度途中では並べ替えをしないこと。

まずは B列からI列 まで。

町 NO2 コード番号 屋号 支部 機関 口座番号 会費年額

口座振替データは W列からAE列 まで。

銀行コード 銀行名 支店コード 支店名 種別コード 種別 口座番号 口座名義(半角30字以内) 新規コード 連番

 

手集金の場合は シート「手集金」にも必要な事項を入力します。まずは B列からH列 まで。

町 NO2 コード番号 屋号 支部 備考 年額

 

会員の脱退があった場合

会費年額(I列)を変更します(年度内受領分にします)。未収金額(V列)が 0 になるようにするためです。

セルの色を変更にします(分かりやすくする為)。その他の部分は変更する必要はありません。年度更新時に処理します。 

 

 

これらの入力の画面から戻る時は必ず「振替口座入力終了」ボタン終了してください。

シート保護の関連から。

 

請求データ作成

会費請求データは金融機関ごとにCSVファイルにします。口座振替に必要なデータを作成するCSVデータの形式は次のとおりです。

A

B

C

D

 E

F

I

J

K

L

M

N

O

 

 

コ|ド番号

事業所名

年会費額

振替金額

銀行コ|ド

銀行名

支店コ|ド

支店名 

種別コ|ド

種別

口座番号

口座名義

新規コ|ド

連続番号

CSVファイルから銀行に提出するデータは

「ふりカエル」というソフトで変換します。

 

 

 

 

 

 

半角カナ

 

半角カナ

 

 

 

半角カナ

0

 

FDの実際のデータはこの並びではありません。

 

10

11

12

13

14

15

「ふりカエル」のボデイ情報の番号になります。

このデータはシート「台帳」のW列からAF列からのデータで作成されます。

金融機関のみを指定して、このボタンを押せば、金融機関の名前のついたcsvファイルを、このファイルがあるフォルダと同じ場所に作成します。前に同じ名前のファイルがあれば上書きします。(例、 会費鹿銀.csv 会費南銀.csv )

 

このCSVファイルを、請求データ作成前に、しっかりと確認します。間違いがあった場合は、このデータを修正するとともに、元の会費台帳のデータも修正します。

 

請求金額は、前期は年会費額の半分、後期は未収金額になります。前期に一年分を請求する事業所は、このCSVファイルの振替金額を変更をします。

 

このCSVファイルを「ふりカエル」で金融機関提出用のデータを作成し、提出用のFDを作成します。引き落とし予定日の5営業日前までに提出します。

口座振替の全銀フォーマット     FDの内容    1レコード  120バイト

(1)ヘッダーレコード

項目

データ

区分

種別

コード

コード区分

委託者

コード

委託者名

(カナ)

振替日

月日

取引銀行

番号

取引銀行

銀行名

取りまとめ

店番号

取りまとめ

店名(カナ)

預金種目

口座番号

予備

桁数

10

40

15

15

17

内容

91

 

 

 

 

 

 

 

1普通

2当座

 

 

(2)データレコード

項目

データ

区分

引落

銀行番号

引落

銀行名

引落支店番号

引落支店名(カナ)

予備

預金種目

口座番号

預金者名

(カナ)

引落

金額

新規

コード

顧客番号

振替コード

結果

予備

桁数

15

15

30

10

20

内容

 

 

 

 

 

1普通

2当座

 

 

 

 

1資金不足

2取引なし

 

(3)トレーラレコード

項目

データ

区分

合計件数

合計金額

振替済件数

振替済金額

振替不能分件数

振替不能分金額

予備

桁数

12

12

12

65

内容

 

 

依頼時は0、処理結果後 記入

依頼時は0、処理結果後 記入

 

(4)エンドレコード

項目

データ区分

予備

桁数

119

内容

 

 

ふりカエルの操作手順「ふりカエル」を起動する

ファイル ⇒ 環境設定 ⇒ 新規 ⇒ 登録 ⇒

銀行名入力 ⇒OK

読み飛ばしヘッダー行数   CSVファイルの

              項目名等を何行

読み飛ばすか指定

入力ファイル  銀行毎のCSVファイルを指定

出力ファイル  フロッピィを指定(aJIFURI

ヘッダー情報(商工会等の情報)

 種別コード  91(固定)

 コード区分  1(固定) 又は 2(固定)

 会社コード  金融機関から指定されたコード(10桁)

会社名    商工会の名前(カナ)

振替指定日  直近の口座振替をする日(後で変更可)

取引銀行番号 銀行コード(4桁) 

銀行名    銀行名(カナ)

取引支店番号 銀行支店コード(3桁)

取引支店名  支店名(カナ)

預金種目   商工会の預金の種目(1:普通 2:当座)

口座番号   商工会の預金の口座番号

未使用

ボディ情報

 各項目はCSVファイルの何列目かを指定する。(詳細は8ページ目の列数) 新規コードを 0 として振替結果コードにも利用。

 手形交換所番号、引落取引番号、その他分類番号、未使用は空白(使用しない)


ふりカエルで銀行別データ作成

処理 ⇒ 切替 ⇒ 振替 ⇒ OK

 

処理 ⇒ 変換処理 ⇒ 振替日入力 ⇒ 変換開始

 

変換結果をチェックする

 合計金額・変換内容の変換結果をチェックする

 エラーがなくなったら正・副フロッピィを作成し、

他の銀行も同じ処理をする。

 

注意事項

ファイル ⇒ システム設定  

システム設定

*半角カタカナ変換対象文字列

カタカナチェックは、この文字列に存在しないものは

エラーとなります。初期設定では ( ) – などがないので

入力します。

*毎行に改行を文字を入れるにチェックをします。

*変換後桁数は 120 桁。

*データレコード、トレーラーコード  全てにチェックします。

 

10ページのデータ出力先(詳細は次ページ)はHD内に作成。後にFDへコピー作成。FB利用の場合はWEBで送信することになります。

ファイルの場所の詳細






kaihiに使用するファイルは、「ふりカエル」のプログラム以外は、全てCドライブの同じフォルダの中においておくことにします。

本体エクセルファイル、そこから作成するCSVファイル、CSVファイルから「ふりカエル」で作成する振替データファイル。

「ふりカエル」のファイル設定で、入力ファイルと出力ファイルを指定するので、あらかじめ、

そのフォルダの中に CSVファイルと生成した振替データを準備しておく必要があります。

 

「ファイル」 「環境設定」 「新規」

入力ファイルと出力ファイルの参照でフォルダとファイルを指定します。

 

(同じような形で指定します)

 

参照ボタンを押すと、ファイル選択のダイアログボックスが出ます。

 

開かれているフォルダは、「ふりカエル」のプログラムがセットされているフォルダになります。

準備したフォルダを選択します。

 

「入力ファイル」で「CSVファイル」を「出力ファイル」で「生成ファイル」を指定します。

 

ファイル名は すべてJIFURIですから

金融機関毎のフォルダを作成して、その中にファイルを置くことにします。メモ帳で作成したテキストファイルの拡張子を取って、元のファイルを作成します。

 

HDのデータをFDにコピーし、提出します。2枚作成します。

生成データの確認

生成したデータの内容を確認します。変換結果表示のアイコンを押します。

ヘッダー は入金される口座

 

ボデェイは会員の口座

 

トレーラに 件数と金額の

合計が表示されています。

 

フリガナはすべて半角カナです。

データ変換でエラーが

起こるのは

カッコ。半角 ( )  全角( )

スペース。

半角       全角 “ ”。

長音    -   −  

 

HDのデータをFDにコピーし、提出します。

 

CSVファイルの印刷物

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 









銀行の詳細について

金融機関名

鹿児島銀行

南日本銀行

鹿児島

相互信用金庫

鹿児島信用金庫

JAいぶすき

JA南さつま

鹿児島

興業信用金庫

フロッピーディスクの仕様

ファイル名

JIFURI

JIFURI

JIFURI

 

JIFURI

 

JIFURI

 

JIFURI

 

紙ベース

提出日

1営業日前

5営業日前

5営業日前

5営業日前

5営業日前

5営業日前

7営業日記録前

記録方式

全銀手順形式

全銀手順形式

全銀手順形式

全銀手順形式

全銀手順形式

全銀手順形式

 

種別コード

91

91

91

91

91

91

 

コード区分

2

1

1

1

2

2

 

会社コード

 

会社名

ミナミキュウシュウシ

ショウコウカイ

ミナミキュウシュウシ

ショウコウカイ

ミナミキュウシュウシ

ショウコウカイ

ミナミキュウシュウシ

ショウコウカイ

ミナミキュウシュウシ

ショウコウカイ

ミナミキュウシュウシ

ショウコウカイ

南九州市

商工会

振替 指定日

0620  1120

0620  1120

0620  1120

0620  1120

0620  1120

0620  1120

0620  1120

取引銀行番号

0185

0594

1991

1990

9251

9257

2890

取引銀行名

カゴシマ

ミナミニホン

ソウシン

シンキン

イブスキ

ミナミサツマ

コウシン

取引支店番号

250

530

021

050

125

001

019

取引支店名

カワナベ

カワナベ

カワナベ

チラン

エイチユウオウ

ホンショ

マクラザキ

預金種目

1

1

1

1

1

普通預金

口座番号

 

WEB利用

FD

FD

FD

FD

FD

金融機関では処理結果をFDに書き込んで返却してくれます。紙でも結果をくれます。WEBサービス利用の場合は紙はなし。

 

 

会費台帳へのデータ入力

口座振替データをシート「check」に読み込み、金額や不能理由などを確認し修正します。口座振替以外の手集金分はシート「手集金」に金額、日付を入力します。以上のことにより、シート「台帳」が完成します。適宜な時期を選んで印刷します。

前期  セル B4 に 1を入力します。

後期  セル B4 に 2を入力します。

 

 最初に、鹿児島銀行から返却されたFDのデータの読込をします。

 取り込まれたデータはシート「checkA列、B列、C列に入ります。後期にはE列、F列、G列。

C列(G列)は 0 や 1 はそのままで、その他の数字があった場合には 1 に変更します。

整理をした時点で、シート「台帳」の J列(前期)O列(後期)を確認します。

表示された合計金額が引落金額と一致しているはずです。    

 

 

鹿児島銀行以外の2本目以降のデータの読込をします。

取り込まれたデータはシート「check」のI列、J列、Kに入ります、

K列は 0 や 1 はそのままで、その他の数字があった場合には 1 に変更します。

 

複数ある場合は、下の作業がすんでから、またここの作業を繰り返します。

 

 

第2ボタンでの作業がすんだら、このボタンを押します。

前期であれば AC列に転記され、後期であれば EG列に転記されます。

 

台帳の確認

シート「台帳」を見ます。前期の欄(J列)後期の欄(O列)に金額が表示されます。引き落としの出来なかった事業には 0 が表示されます。赤字でマイナス表示される場合は、シート「check」のC列の数字の変更をしていないものです。

引き落とし金額合計の確認

セル J2012、O2012 から金融機関ごとの合計金額が表示されます。違う場合は、その原因を探して修正します(口座番号の違い、1の修正位置違いなど)。旧商工会ごとの小計はセル J2008、O2008 から合計金額が表示されます。各商工会の明細と一致していることを確認します。

地区毎、金融機関毎の集計は 「会費口座振替明細確認」ファイルで行います。会費台帳とは別ファイルです。エクセル2007以降専用。

このファイルは、請求データ用のCSVのから全金融機関分をこの集計用ファイルのシート「check」に転記すれば、 請求分一覧表が作成でき、振替不能のデータを消去していけば、実際の振替結果明細一覧表が

作成できます。エクセル2007からは COUNTIFS  SUMIFS の新関数が使えます。

COUNTIFS(条件範囲 1, 検索条件 1, [条件範囲 2, 検索条件 2])

 条件範囲 1    必須です。対応する条件を評価する最初の範囲です。

検索条件 1    必須です。カウントの対象となるセルを定義する条件を数値、式、セル参照、または文字列で指定します。

条件範囲 2, 検索条件 2, ...    オプションです。追加の範囲とそれに対応する条件です。最大 127 組の範囲/条件のペアを指定できます。

 

重要   各追加範囲の列数と行数は "条件範囲 1" 引数と同じである必要があります。範囲どうしは隣接していなくてもかまいません。

 

SUMIFS(合計範囲, 条件範囲 1,条件 1, [条件範囲 2,条件 2], ...)

 合計範囲    必ず指定します。合計する 1 つまたは複数のセル (数値か、数値を含む名前、範囲、またはセル参照など) を指定します。空白と文字列は無視されます。

条件範囲 1    必ず指定します。対応する条件による評価の対象となる最初の範囲を指定します。

条件 1    必ず指定します。加算の対象となる条件範囲 1 のセルを定義する条件を数値、式、セル参照、または文字列で指定します。

条件範囲 2,条件 2, ...    省略可能です。追加の範囲と対応する条件です。最大 127 組の範囲/条件のペアを指定できます。

個別台帳の印刷

入金状況の確認のため、町の区分と支部の区分と金融機関の区分を指定して、台帳の印刷ができます。

金融機関の指定だけだと3町分が一緒に確認できます。指定はいろいろ出来ます。必要な都度行ってください。

手集金分の処理

シート「手集金」に金額と日付を入力します。

引き落としの出来なかった事業所の集金分は、シート「手集金」の空いている行にコード番号(事業所コード)と屋号を入力し、集金した金額と日付を入力します。

現金集金の入力欄は、基本は年2回の払い込みですが、前期で2回、後期で2回分を設けています。

口座引き去りの通帳を変えた場合(前期と後期で違う場合)

後期の請求前に、口座が変更になった場合、シート「台帳」のAA列からAC列の変更は勿論ですが、H列の口座番号も新しいものに変更します。(1+7桁=8桁の口座番号)。そうすると前期分の会費金額が表示されなくなります。シート「check」のA列の当該事業所の古い口座番号を新しいものに変更する必要があります。これで表示がきちんとされます。

 

台帳の確認印刷

月末毎に、元帳残高と会費台帳の一致を確認します。全体名簿の印刷はいつでも出来るわけですが、保存用として9月末と3月末には必ずします。

旧商工会単位での印刷が良いでしょう。

未入金事業所の確認印刷

引き落としの出来ない事業所の名簿は、金融機関毎に印刷をするのではなく、金融機関の区分の指定をせずに、町の区分だけ指定して、ボタンを押します。すべての金融機関が入った一覧表が出来ますので、こちらを印刷して連絡用に使用します。

振替不能の方には現金納付の案内を電話や文書ですることします。

振替不能者名簿       振替不能者名簿       このデータの作成は、22ページで説明します。

               

未収金額(V列)が0でない会員名簿、随時確認可能。

                                                       

 

 

手集金台帳の入力

                          

                支部別に集金している会員だけを、範囲を絞って入力するためのもの。

また特に利用しなくても、入力可能です。

 

 

会費納入案内用のデータ作成

年2回の請求案内になります。前期と後期の案内は請求金額が違います。前期は、年会費額の半分を請求します。

後期は、未入金額が請求金額となります。口座振替と現金納付の2種類になりますので、それぞれ作成します。

振替者データの作成をして、そのファイルの保存をして、閉じてから、集金者データの作成をしてください。

ファイルが3本以上開いていることは想定していません。

      前期と後期の判断はシート「menu」のセルB4の数値で判断しています。

実際に案内書を出すときには、銀行へ出す請求データと、この案内書が同じ金額であることを確認します。一年分を一括請求した事業所などもありますので、十分注意してください。CSVデータの変更をした事業所をしっかりと確認することです。

口座振替のできなかった事業所のデータを拾い出します。入金処理作業をしていない場合は、何もしないです。

振替不能の方には現金納付の案内を電話や文書ですることします。

これらから作成されるデータは、別に作成する会費請求等の文書への差し込み印刷するデータとして利用します。

 

案内文書には銀行や支店名は漢字表記をしたいと思いますので、その変換用には銀行コードと支店コードを掛け合わせた数値を参照コードとしてVLOOKUP関数を利用します。

シート「menu」にあるマクロボタンの説明

 

 

シート「手集金」のIJKLMNOP列のデータを消去します。           シート「check」の全てのデータを消去します。

シート「台帳」のU列のデータを消去します

   

 シート「手集金」のプレビュー画面からの印刷をします。   シート「台帳」のプレビュー画面からの印刷をします。       

全体の印刷になります。あまり意味はないです。       全体のものです。  

シート「台帳」のデータの並べ替え。

B列からAF列までのすべてのデータをD列(コード番号)を基準にして降順(小さなものから大きなものへ)に

         並べ替えをします。商工会間に新規加入用のスペースを取るためダミーのコードを入力してください。

          A商工会の最大コードより大きく、B商工会の最小コードより小さいもの。以下同様にして確保してください。

          印刷物を見た時、ひとめで新規加入者が最後の方にあるのが、わかりやすいと思います。随時の並べ替えはしないこと。年度始めに一度実施。

シート「手集金」のデータの並べ替え。

B列からP列までのすべてのデータをD列(コード番号)を基準にして降順(小さなものから大きなものへ)に

          並べ替えをします。商工会間に新規加入用のスペースを取るためダミーのコードを入力してください。

           A商工会の最大コードより大きく、B商工会の最小コードより小さいもの。以下同様にして確保してください。

          口座引落の方で、振替不能になった場合で、手集金した場合もここで入力します。この場合は、コードの並びが降順にならなくなってきますので、年度始めでスペースの確保は多目にしておく方が良いと思います。その後、ダミーのコードは

消してしまいます。随時の並べ替えはしないこと。年度始めに一度実施。

 

会費台帳の整理(年度末? 年度始め?)

翌年度のために

年度終了後、残しておくファイルはそのまま保存します。ファイル名を新年度用に変えて新規に保存します。

手集金台帳初期化 口座振替台帳初期化  を行います。

シート「台帳」に表示された金額も消えます。

台帳の整理

並び順は事業所コード順(D列)です。旧商工会単位で確認できるような形で入力します。

脱退者の消去 

シート「menu2」の振替口座入力開始からシート「台帳」に入ります。A列は消去しないでください。

脱退者はセルの色を変更しています。セルの色も元に戻します。この行のセルB列からセルI列までのデータを消去、W列からAF列までのデータを消去します。行や列の削除はしないこと。セルの削除もいけません。挿入もいけません。新規加入者用の予備の場所も準備します。

不要なデータを消去したあとで、いったんシート「menu2」に戻ります。(7ページ参照のこと)。

その後、シート「menu」 ここからデータの並べ替えを行います。

これは一年に一度が基本です。

 

手集金台帳の整理

並び順は事業所コード順(D列)です。旧商工会単位で確認できるような形で入力します。

脱退者はセルの色を変更しています。セルの色も元に戻します。この行のセルB列からセルH列までのデータを消去します。

その後、シート「menu」 ここからデータの並べ替えを行います。これは一年に一度が基本です。

 

このファイルの利用においてはシート保護の解除は想定していません。解除後の利用は自己責任でお願いします。

式参照がおかしくなりますので、行や列の挿入、削除は厳禁です。データの切り取りや貼り付け、データの移動などもいけません。

その他の事項

  シート「台帳」の利用しない行を非表示にします。

画面移動をスムーズにするため、会員の数に応じた表示にするため、不要な行を非表示にします。適宜な数値を設定してください。 

 

   

「台帳」AC列の口座番号との照合です。

 

重複あり!   無いです! どちらでしょう。

 

金融機関や支店が異なれば、会員で口座番号が重複することも考えられます。作業が煩雑になりますが、対策が必要です。

AC列の口座番号は正しいものを入力して変更はしません。あとからの事業所のH列の口座番号を重複しない形に変えることにします。入金処理をするときに、読み込んだデータはシート「check」に取り込まれます。検索で元の口座番号を探して、変更後のH列の口座番号に変更します。これで台帳に表示されます。H列の口座番号のセルの色も変更しておくと良いと思います。コメントもいれましょう。

 

 

会費納入案内用のデータは次のような形で出力されます。ここから必要なデータのみを利用することになります(22ページ)。

 

 

非表示にしているシートの説明

シート「dai」    シート「insatu」   シート「dai2」   シート「csv」   シート「 mati」(利用していません)

 

シート「insatu」はシート「台帳」のA列からV列までの形式を持つデータのない表です。各種の台帳(名簿)印刷の時に利用します。

シート「台帳」を直接加工してエラーが起こった場合にはデータの復旧などが困難になります。

名簿印刷の時には、シート「台帳」の 範囲 A1:V2001 のデータを、シート「dai」にそっくりコピーして、その作業用シートにオートフィルタを設定して、指定項目に従ってデータの作成を行うことにしています。そのセレクトされたデータを更に、シート「insatu」に貼り付け、データのある行だけを印刷すると名簿が完成する次第です。

 

シート「dai2」 シート「csv」 は何もない白紙のシートです。

CSVファイル作成の時には、シート「台帳」の 範囲 A1:AF2001 の全データを、シート「dai2」にそっくりコピーして、その作業用シートにオートフィルタを設定して、指定項目に従ってデータの作成を行うことにしています。そのセレクトされたデータを更に、シート「csv」に貼り付け、振替データ作成用のCSVファイルとして保存することになります。

 

会費納入案内用のデータ作成にも、シート「dai2」 シート「csv」 を利用します。

シート「台帳」の 範囲 A1:AF2001 の全データを、シート「dai2」にそっくりコピーして、その作業用シートにオートフィルタを設定して、指定項目に従ってデータの作成を行うことにしています。そのセレクトされたデータを更に、シート「csv」に貼り付け、そのシートの名前を変えて、名簿ファイルとして別ファイルとして作成します。保存は各自でしてください。

 

おまけ シートでフィルタされたデータをいっきに選択する方法は、{SHIFT}+{CTRL}+{*}と3つのキーを同時に押します。

    それをコピーして別の場所に値のみ貼り付けをして使うという方法を私は多用します。

VBAでは    Selection.CurrentRegion.Select

  Seletion.Copy

 となります。

会費納入案内用のデータ作成  22ページの追加的説明になります。

年2回の請求案内になります。前期と後期の案内は請求金額が違います。前期は、年会費額の半分を請求します。

後期は、未入金額が請求金額となります。口座振替と現金納付の2種類になりますので、それぞれ作成します。

振替者データの作成をして、そのファイルの保存をして、閉じてから、集金者データの作成をしてください。

ファイルが3本以上開いていることは想定していません。

      前期と後期の判断はシート「menu」のセルB4の数値で判断しています。

実際に案内書を出すときには、銀行へ出す請求データと、この案内書が同じ金額であることを確認します。一年分を一括請求する事業所などもありますので、十分注意してください。CSVデータの変更をした事業所をしっかりと確認することです。

 

案内文は3種類  口座振替用、現金納入用、金融機関用

 

1、  シート「menu」のセルB4に前期は「1」、後期は「2」を入力します。

2、  シート「menu2」の「振替者データ」で 口座振替事業所のデータファイルができます。名前をつけて保存します。

 

頴娃、知覧、川辺の全データ。請求金額がOもあります。不要なデータは削除します。

 

NO          NO2    コード番号    屋号   支部   機関   口座番号      会費年額      請求金額       銀行コード    銀行名 支店コード    支店名 種別コード    種別   口座番号      口座名義(半角30字以内)   新規コード    連番

 

これらのデータから 差し込み印刷に必要なデータ を作成します。

事業者名   様  (事業所コード  )           円       金融機関  口座種別  口座番号  口座名義 

                                 漢字表記  漢字表記  数字表記  カナ表記

ファイルの金融機関はカナ表記なので漢字表記に変更します。変更方法はいろいろありますが、編集・置換を使う方法は作業回数が多くなるので、金融機関照合表を準備してVLOOKUP関数を使う方が良いと思います。

金融機関照合表(GINKO.xls)

 

銀行コード * 支店コード

を照合用のコードとします。

同じ数字になることはないと想定していますが、

同じ場合は注意が必要です。

 

2で作成したデータには年額会費を納めた事業所や、脱退者の事業所もあります。

 

新規加入の事業所はコード番号順にはなっていません。台帳の順番になっています。

 

会費年額、請求金額を確認して

不要なデータを削除します。

 

コード番号順に並べ替えをします。

定款会員・特別会員のコード番号にご注意ください。

 

 

 

 

   A列 B列     C列  D列     E列    F列       G列

 

「振替者データ」

S欄(新規コード)の欄に銀行コード*支店コードを入力します。S2の式=K2*M2。以下にオートフィルコピー。

金融機関照合表(GINKO.xls)を開きます。

金融機関照合表からT欄(連番)に銀行名を、その次の列U欄に支店名を入力します。

T2の式=VLOOKUP(S2,GINKO.xls!$E$2:$G$30,2,FALSE)。

U2の式=VLOOKUP(S2,GINKO.xls!$E$2:$G$30,3,FALSE)。

以下にオートフィルコピー。

セルT1に「銀行」、セルU1に{支店}と入力します。上書保存します。

NO          NO2    コード番号    屋号   支部   機関   口座番号      会費年額      請求金額       銀行コード    銀行名 支店コード    支店名 種別コード    種別   口座番号      口座名義(半角30字以内)   新規コード    銀行  支店

 

3、  シート「menu2」の「集金者データ」で 現金納付事業所のデータファイルができます。名前をつけて保存します。

 

頴娃、知覧、川辺の全データ。請求金額がOもあります。

 

3で作成したデータには年額会費を納めた事業所や、脱退者の事業所もあります。新規加入の事業所はコード番号順にはなっていません。

台帳の順番になっています。会費年額、請求金額を確認して不要なデータを削除します。

 

コード番号順に並べ替えをします。定款会員・特別会員のコード番号にご注意ください。

 

NO          NO2    コード番号    屋号   支部   機関   口座番号      会費年額      請求金額       銀行コード    銀行名 支店コード    支店名 種別コード    種別   口座番号      口座名義(半角30字以内)   新規コード    連番

 

これらのデータから 差し込み印刷に必要なデータ を作成します。

事業者名   様  (事業所コード  )           円      

4、  会費台帳に使用しているコード番号は、頴娃はそのまま、知覧が100000を加算、川辺が1000000を加算したものを

利用していますので、口座番号(H列)の欄に正規のコード番号を表示させます。

頴娃はそのまま、                 =D2          以下 知覧のデータ前までオートフィルコピー

知覧が 100000を加算 300行目からとして =D300−100000 以下 川辺のデータ前までオートフィルコピー

川辺が1000000を加算 500行目からとして =D500−1000000以下 最後のデータ前までオートフィルコピー

   上書保存します。

 

5、  差し込み印刷は別の資料をごらんください。

 

定款会員、特別会員のコードは特別扱いをします。

台帳の並びを最初に一般会員、そして定款会員、特別会員としたほうが分かりやすいと思います。

 

この台帳に使うコードを定款会員、特別会員の場合は最後に来るように大きなコードを使います。

元のコードに戻す場合も機械的にはいきませんので、ご注意ください。

 

 

 

 

 

 

 

会費台帳へのデータ入力  16ページの追加的説明になります。

入金処理時の注意点

第1のデータ読み込み。鹿児島銀行のデータ。

FBで返還入手したデータがこちらで想定していたデータの並びと違い、読み込みができなかった。

振替結果をすべて印刷。納付、不能者が印刷物で確認できます。

申請データを読み込むことにした。全てが入金されたことになっているので、印刷物をみながら、不能者のC列の0を1に変更した。

 

2のデータ読み込み。

南銀、JA南さつま、信金、相信   金融機関がきちんとデータを書き込んであるので、問題なく処理できます。

JAいぶすき、興信は頴娃支所のデータ処理であるが、すべて入金になったので、仮に作成しているデータの読み込みで処理。不能があった場合も

鹿銀方式に準じて処理します。

 

地区別、金融機関別の件数・金額集計のためにファイルを作成している。

 会費口座明細確認

JIFUIデータ作成に使用しているCSVファイルをシート「check」に読み込むと、「明細」に集計一覧が出来る。(エクセル2007以降でなくてはけない。新関数使用)

入力したデータ集計が上段に表示されるので、請求時点での数値を、下段に貼り付けをします。

振替結果が判明したら、不能者のデータをクリアします。納入状況が表示されるので、この結果を台帳の集計結果と確認し、一致していることを確認します。

違算がある場合には、一致するまで、その原因を追究し確認することが必要です。

 

 

 

 

振替不能者への再度の納付案内の送付時の注意点

会費台帳から手作業で、不能者データを作成します。新規のシートを準備しておきます。シート「台帳」の保護を解除します。

非表示にしている部分を表示するため、V列AG列を選択して右クリックメニューから再表示。口座データまで表示させます。

 

データ オートフィルタ J列で 0(ゼロ) を選択します。(振替の出来なかった人になります。)

A列からAD列までの選択されたデータを新規のシートにコピーします。(値のみ貼り付けで)。このデータのK列からZ列まで削除します。

この時点で、銀行からの不能者リストと差異がないか照合をします。一致を確認したらJ列に今回の請求金額を入力します。

(方法はいろいろありますが、シート「check」を利用して、VLOOKUP関数で呼び出すのが一番簡単でしょう。)、

 

このデータを差込印刷のデータに利用します。コード番号を利用して住所データを呼び出し、宛名ラベル印刷をします。

 

電話連絡でお願いできる方は電話をすることが望ましい。

 

前期分会費のデータ作成の注意事項について   再確認

 

  JAいぶすき、 興信(紙)    頴娃支所から金融機関へ 

   その他は            知覧支所から金融機関へ

 

csv作成前に

年会費の確認、 一般

       新加入者    初年度会費の確認  加入承認月の確認

  振替口座の確認    新規振替、口座変更、名義変更、振替停止で現金納付へ

 

csv作成

金融機関別にcsvファイル作成  プリントプレビューが出るがここでは印刷しない

全ての金融機関のcsvファイルを作成後に、そのcsvファイルを開いて請求金額の欄の合計を出したものを印刷することとします。

この時点で、一年分振替をする事業所は請求金額の欄を変更します。

 

データ作成

csvファイルを「ふりカエル」で、提出用データをハードデスク内に作成します。

  「ふりカエル」の作業中のエラー

        フリガナぐらいと思います

データコピー

銀行提出用のフロッピィデスクを2枚作成します。

鹿児島銀行はファームバンキングで送ります。

 

 

会費口座明細確認ファイルにCSVファイルを読み込んで、支所別、金融機関別の件数、金額を確認をします。

不能者のデータ消去で結果の確認をします。

 

後期分会費のデータ作成の注意事項について    再確認

 

  JAいぶすき、 興信(紙)    頴娃支所から金融機関へ 

   その他は            知覧支所から金融機関へ

 

csv作成前に

年会費の確認、 一般

       新加入者  初年度会費の確認  加入承認月の確認

未入金欄の確認   赤字のものがないか(マイナスにはならない) 0 以上。

  振替口座の確認    新規振替、口座変更、名義変更、振替停止で現金納付へ

 

csv作成

金融機関別にcsvファイル作成  プリントプレビューが出るがここでは印刷しない

全ての金融機関のcsvファイルを作成後に、そのcsvファイルを開いて請求金額の欄の合計を出したものを印刷することとします。

後期の場合、ここで金額の変更はないと思います。

 

データ作成

csvファイルを「ふりカエル」で、提出用データをハードデスク内に作成します。

  「ふりカエル」の作業中のエラー

        フリガナぐらいと思います

データコピー

銀行提出用のフロッピィデスクを2枚作成します。

鹿児島銀行はファームバンキングで送ります。

 

会費口座明細確認ファイルにCSVファイルを読み込んで、支所別、金融機関別の件数、金額を確認をします。

不能者のデータ消去で結果の確認をします。

 

口座振替不能のかたへの案内

電話で連絡できる会員さんには電話で!

文書で案内をする時は、住所ラベルの印刷、文章は、相手に応じて変更もします。

inserted by FC2 system