マクロ
書式・関数
■マクロ作成適格者
  • 0.社会常識にたけた人
  • 1.真面目な人
  • 2.几帳面、緻密な人
  • 3.辛抱強い人
  • 4.理屈っぽい人
  • 5.仕事を簡素化したい人
  • 6.その仕事に精通している人
  • 6.コツコツ型の人
  • 7.集中力のある人
  • 9.歩きながら考えられる人
 青色申告原本.xlsm 

(左クリックでGoogle Driveが開きます)

マクロはどこにある?

 どんどん変わっていくエクセルの見た目、マクロを弄りたくても一体どこにあるのだろうと迷ってしまうこの頃です。
 最初の設定では隠れているリボンを表示するには、ファイル→オプション→リボンのユーザー設定→右の枠内にある□開発という文字の左側をクリックしチェックを入れます→OKで戻れば、ファイル ホ^ム 挿入 と続くタブの中に『開発』という文字が現れます。
 この開発タブを押すと、一段下の一番左側に『Visual Basic』、その右に『マクロ』が見えます。これでマクロを編集したり記録したりすることが出来るようになります。ホンに面倒な設定ですね。エクセル開発チームはマクロなんて一般人には必要ないだろうと思っているのですかね。



エクセルの初期設定

 購入したてのエクセルシートに施してあるのが初期設定、私のものを調べてみると、フォントは [ MS Pゴシック ] 、大きさは [ 11 ] になっている。ブックには3枚のシートが初期設定。
 これらを自分の初期設定に変更するには、ファイル→オプション→基本設定→この中にある [ 使用するフォント ] [ フォントサイズ ] [ ブックシート数 ] を変更します。これで、新しいシートや新しいブックを追加すると、自分の設定した内容が初期設定となって反映されています。



マクロでの書式設定

 シート上での書式設定は、[ ホーム ] の [ 書式 ]→表示形式→ユーザー設定で設定しますが、セルに日付が入っていれば m ならば月を表示します。その後に 月 を表示させたい時には m"月" とユーザー設定するわけです。
 マクロを書く時は、書式全体をまず " " でくくりこの中にシート上と同じ設定項目を書きます。m はそのままでいいのですが、"月" を書く時既に両側に "" がありますので、もう一組 "" を書かないと "月" を設定したことにはならなくなります。
 そこで、シート上の書式設定、m"月" はマクロでは、"m""月""" と書くことになるわけです。



ABCという列表示

 初期設定ではセルの最上部にある列を表示する文字はABCDE・・・となっています。マクロではセルを書く時には A1 とか $A$1 という書き方をします。$が入れば絶対位置を表しますが、最近ではA1という書き方で問題ありません。
 また別の書き方では、CELL(1,1)という書き方があります。この( , )内の左の数字が行を、右の数字が列を表します。変数を使ってセルを表示したい場合には絶対必要なテクニックです。
 例えばB4というセルは、CELL(4,2)という書き方になります。またCELL(K,2) Kという変数は4だと指定すれば同じセルになります。
 列をABCではなく行の表示と同じように12345・・・・と表示させたい時は、ファイル→オプション→数式→数式の処理で□R1C1参照形式を使用するにチェックを入れると、数字の列表示になります。



For 〜 Next 文のおさらい

 cell
 Defaultの ABC・・表示と、R1C1参照形式を並べてみました。マクロの中でも一番マクロらしいマクロのひとつであるこの For-Next 文を充分理解すると、マクロにはまっていく自分を見つけることが出来るでしょう。

 Range("A1") = Cells(1,1)
 Range("A9") = Cells(9,1) です。

 分かり易い例題を。
セル B2 から B6 まで、文字 春 を書くマクロはこうなります。

For Suuji = 2 to 6
 Cells( Suuji , 2 ) = "春"
Next Suuji

 変数 Suuji が 2 から 6 まで繰り返すというコマンドです。Suiiji という変数が 2 から 6 まで 1 ずつ加算されながら繰り返されます。
1回目は Cells( 2 , 2 ) = "春"
2回目は Cells( 3 , 2 ) = "春"
3回目は Cells( 4 , 2 ) = "春"
4回目は Cells( 5 , 2 ) = "春"
5回目は Cells( 6 , 2 ) = "春"
という風に自動的に処理されます。
 Suuji が 6 の処理が終わったらこの For-Next 文は終了します。。

 変数名 Suuji は何でも良く、昔は子供の名前 fumika, yuuki, natsumi 等を遊びで使ったものでした。
このマクロが単独マクロなら、もちろん最初と最後に Sub XXX() , End Sub が必要になります。

 この3行マクロを完璧に理解できれば、もう鬼に金棒、マクロがおもしろくておもしろくて仕方がなくなります。

 Do While 〜 Loop

 Do 〜 Loop While

 Do Until 〜 Loop

 Do 〜 Loop Until

 上記のように、繰り返しを実行するには For-Next 文以外にも英語として意味が分かりやすいコマンドがあります。
 どちらを使うかは好き嫌いの問題でしょう。私は、昔から For-Next 文を使い続けています。
 デバッグの最中に無限繰り返しになって困ることが時々あります。1行 i = i + 1 が必要な For-Next 文以外の奴がちょっと面倒に感じるのです。
 ここでは説明をしませんが、Web上にはたくさんの解説ページが存在します。よーく研究してください。



空白セルを知る

 もし Range("C4") が空白かどうかを知るには、If Range("C4") = "" と書いて下さい。
 または、If Cells( 4, 3 ) = "" です。



目下、書きかけ中!

 ダウンロード可能な上記「青色申告原本.xlsm」は、あくまでも見本品です。申告には自分用にカスタマイズする必要があります。仮に記入してあるデータを実験用として弄ることで、分かる人は分かるかもしれません。
 実際に使い始めるためにはあらためてデータを消去して、本物データを入力し始めることによって、来年申告のための準備が出来るでしょう。このページが完成するまでは、細かい使い方を説明することは出来ません。ご了承下さい。




 ver. EXCEL2010

 ただ今このページを作成途中!
 マクロ [ AllSouKanMoto ] の説明は出来ました。目下、そのフローチャートを作っています。と言いながら、もう1年近く先に進んでいません。

 個人事業主のための青色申告なるものをエクセルで作ってみた。

 この個人的制作途中のマクロをそのまま使って万が一にも税務申告に使うことをしないでください。そんな愚かな人はいないと信じています。想定もしておりません。このマクロは、あくまでも自分で申告しようとしている人が自らの手で確定申告用マクロを組むために、私なりのヒントを公開しているページであることを、ご理解ください。ある程度の知識を持った人に贈るエクセル・マクロ技術情報です。

 エクセルを使って仕事をする人の原則は、自分の仕事に精通していることが、まず求められる。仕事を熟知していないでエクセルを使うこと、さらにエクセル・マクロを使うことはあり得ない。そういう意味では、この青色申告マクロは、経理の「ケ」の字も知らない人には理解が難しいだろう。経理を多少なりとも知っていて、エクセルも使ったことのある人向きだということを最初に断っておかなければなりません。

 ウェッブ上にはエクセルによる青色申告ファイルがたくさん存在します。いろいろなファイルをダウンロードして、それなりにいじってみましたが、なんかしっくりするものに出会えなかったものですから、こうやって自分なりの分かり易いエクセル・マクロ・青色申告に挑戦した次第です。

 個人事業主でも税理士を雇った方が一番賢明であることは確かです。ただ事業主となって1、2年は売上も上がらず利益が出ないのも普通です。税理士を雇うお金があるなら、別のものに使うことの方がより賢明かもしれません。事業が軌道に乗るまでの短い?間、自分で経理事務をし経理知識を身につけることは決して無駄にはなりません。それどころか、経理の「ケ」の字を知らないで経営なんて出来るわけがないことを学ぶきっかけを作ってくれること請け合いです。それでは、来年のためにこの青色申告・エクセル・ファイルの内容を説明しながら、このマクロについても説明できることを願いつつ始めることにしましょう。



青色申告 エクセル・ファイル

 この欄の左側に置いてある『青色申告原本.xlms』をダウンロードしてください。シート『仕訳帳』に毎日取引内容を記帳することによって、随時シート『総勘定元帳』を作成することが出来ます。期末にはこれが決算内容になります。
 『仕訳帳』の日付け入力は『日』列に入力します。『月』欄の表示は自動で行われます。同じ月の場合は表示しない様になっています。借方も貸方も勘定科目は右の表に登録してある番号を入力します。
 借方科目が現金以外の時は貸方科目は現金がデフォルトになっています。現金以外の科目の時にはその科目番号を入力してください。複式簿記ですが、借方と貸方は1対1で記入することが規則です。それゆえ、貸方科目金額は借方科目金額を参照して同額になるようになっています。
 『仕訳帳』に記入を追加する場合は、一番下の空白行に記入し、『日付順に並べ替え』ボタンをクリックすることにより、自動的に日付順に並べ替えられますので便利です。とりあえずは見本のデータが入っていますので、遊んでみてください。
 『仕訳帳』にある勘定科目番号表に登録する科目は自由に自分の事業に合わせて登録しなおしてください。正式にこの仕訳帳を使い始まるときは、クリアボタンを押して、データをクリアしてからお始めください。
 ほかのシートは全部自動というわけにはなっていません。エクセルが分かる人なら、最後の貸借対照表まで行きつけることが出来るかもしれません。『仕訳帳』と『総勘定元帳』は基本中の基本ですから、これらを真面目に記帳・操作することによって青色申告への道は開けることと思います。



マクロ [ AllSouKanMoto ]

 開発→Bisual Basic とクリックし、標準モジュール [Module1] コードを表示してください。二つ目にあるマクロがこの表題マクロになります。
 1行目 ' Application.ScreenUpdating = False
頭に『'』アポストロフィーがあります。これはこの行を作動させない、あくまでもコメント行として扱うという約束事です。この『'』が無ければ、このマクロが作動中にその動作を見せないで仕事をするというコマンドです。マクロを実行し始まったシートのそのままで、裏で仕事をして最後まで実行します。今回は敢えて仕事をしているところを見せるために、このコマンドを実行しない様にしています。マクロが動作していることを確認できて面白いことは確かです。動作を見る必要がなければ、この『'』だけ取ってあげれば、動きのないマクロ実行をすることになります。

 このマクロ全体は『仕訳帳』からデータを取り込み『総勘定元帳』を作成するマクロになります。仕訳帳に登録してある勘定科目番号順にその元帳を作成しようとするものです。現在の1番から35番までの科目について作業します。何も記入のないスペースを見つけると、マクロは終了します。とりあえず該当科目は登録しないが以下にも科目が続く場合は、『Non』を入れておくことにより、作業を継続します。

 2行目 シート全体をクリアします Cells はシート全体のこと、ClearContents は文字だけを消去します。罫線や書式はこのコマンドでは消えません。文字も書式も初期設定状態に戻したいときは、Clear コマンドを使います。
 3行目 xlLineStyleNone シート全体の罫線を消します。
 4行目 xlGeneral シート全体の書式を [ 標準 ] にします。
 5行目 "m""/""" B列(『月』列)4行目から5千行目までに『月/』を書式設定。書式では "/" と設定しますが、それをマクロの中で書く時は、さらに""""とダブってあげることがコツです。何故5千行目までなのかは特に理由はありません。現実的に5千行にデータが入っていたら、エクセル・マクロは相当苦労するでしょう。
 6行目 C列(『日』列)4行目から5千行目までに『日』を書式設定。
 7行目 "#,##0 ;[赤]-#,##0 ;0 " F列(借方)とG列(貸方)金額欄の4行目から5千行目までにカンマ付き数字表示書式を設定。
 8行目 NEN という変数に仕訳帳に入力されたデータの1行目の『日』の日付を格納します。総勘定元帳、各勘定科目名の右端に表示する年度のために取得します。
 9行目 SKM という変数は『仕訳帳』から『総勘定元帳』に取り込んだデータを書く行を表します。まず見出し行を書くためにその変数を 2 (行目)から始めるという意味になります。

 10行目  For 〜 Next 文が始まります。For ASK = 5 To 39 仕訳帳の勘定科目登録順に科目名を読み込んでいきますので、その勘定科目が登録されている仕訳帳の5行目から39行目までを繰り返し対象とします。
 11行目から13行目 勘定科目名が Non の場合、読み飛ばして以下の作業をしません。GoTo NonTobashi 次の作業に移るために繰り返し点 Next の直前にある NonTobashi: 地点に飛びます。
 14行目から16行目 勘定科目名セルが空白の場合、これで登録された科目はなくなったと判断してこのマクロを終了します。Exit For
 If で始まるコマンドは必ず End If で閉めます。
 17行目 変数 KMK に仕訳帳の科目登録された勘定科目名を取得します。10行目、For ASK = 5 から始まりますから、Cell(ASK,13)〜最初は仕訳帳の Cell(5,13) 「現金」を取得するということになります。
 18行目 同じように変数 KKK には Cells(ASK,14) = Cells(5,14) 「借方」を取得します。

 19行目から25行目 見出しの1行目を書きます。「総勘定元帳」「勘定科目名」(センター揃え、中太罫線囲み)、「年度」(書式設定)をやります。
 26行目 SKM = SKM + 1 見出しの2行目のために、書く行を1行加算します。
 27行目から32行目 見出しの2行目を書きます。「月」「日」「相手科目」「摘要」「借方」「貸方」
 33行目 この見出しの2行目に「田」罫線を引きます。
 34行目 SKM = SKM + 1 データの書き始め行のために1行加算します。
 35行目 OKH = SKM データの書き始め行を変数 OKH に覚えます。

 現在 For ASK = 5 To 39 という繰り返しを実行中です。仕訳帳に登録された勘定科目の順番に総勘定元帳を作るマクロです。ここまでで見出しまでの作業と仕訳帳のデータを読み込む前の準備が出来ました。
 これから、仕訳帳のデータを1行目から最後の行まで見に行き、勘定科目が同じ(例えば最初の科目なら「現金」)ならその行を総勘定元帳に取り込むという作業をします。その際、仕訳帳は複式簿記で1行に勘定科目が2科目記載されていますので、このマクロではまず左側の借方を全部済ましてから、次に右側の貸方全部を見に行き作業するという過程を踏んでいます。このあたりはマクロを組む人の個性が表れます。借方を読んで取り込む場合と貸方を読んで取り込む場合は、参照セルに違いが生じます。反対科目を相手科目として取り込むからです。このあたりでは多少の経理知識が必要かもしれません。

 36行目 For SWG = 4 To 10000 このマクロは、シート『総勘定元帳』にあるボタン『総勘定元帳全科目作成』に登録されています。勿論このボタンをクリックでマクロを実行します。
 シート『仕訳帳』の4行目から1行ずつ見に行って、同じ科目があればその値を『総勘定元帳』というこのシートに取得するわけです。To 10000 という数字は1万行まで見に行きなさいということですが、実際にはその途中でスペース行を見つけてこの For-Next 作業を終了することになります。
 まずはこの繰り返し作業で「借方」科目にある処理をします。37行目、38行目で空白行を見つけたら、この繰り返し作業を抜け出せと言っています。39行目から44行目で、もしも空白行でなければ「月」「日」「相手科目」「摘要」「借方金額」「貸方金額」の値を取得しなさい、と言っています。
 45行目 SKM = SKM + 1 総勘定元帳シートの書く行をまた 1 行追加しています。
 46行目 End If と47行目 Next SWG は見ての通り。

 47行目 For SWH = 4 To 10000 今度は仕訳帳の右側部分・借方科目の値を取得します。48行目から58行目までは前述借方科目とほぼ同じ処理をします。取得する値が借方・貸方ではちょっと位置が違うだけです。この2つの繰り返し取得が終われば、仕訳帳から総勘定元帳へひとつの勘定科目だけを抜き出したデータ・エリアが出現することになります。

 59行目 If OKH < SKM Then 、35行目 OKH = SKM でひとつの総勘定元帳データを書き出す行を取得していました。そして、仕訳帳にその勘定科目のデータがあれば SKM は OKH より大きくなっているはずです。なので、データがあれば60行目から63行目でその取得したデータ群を「日」「相手科目」「摘要」順に並べ替える作業を要請しています。並べ替える範囲は、OKH その勘定科目のデータ最初の行から SKM - 1 空白行を見つけた行から -1 した行になります。
 64行目 Range(Cells(OKH, 2), Cells(SKM - 1, 2)).FormulaR1C1 = "=RC[1]" B列(2列目)『月』列にこの数式を入れます。入れる範囲は、OKH データの開始行から SKM - 1 データの最終行になります。
 65行目から67行目 一つの科目にデータがあるならば、『月』列に "=IF(ISBLANK(RC[1]),"""",IF(MONTH(R[-1]C[1])=MONTH(RC[1]),
"""",RC[1]))" という数式を入れて、月が違う時のみその月を表示するようにします。
 66行目は借方合計金額を、67行目は貸方合計金額を求める式を入れています。・・・Sum(・・・がそうですね。
 68行目から72行目 勘定科目が借方科目か貸方科目かを取得してあった KKK を判断して、1行下の残高金額を借方に表示するか貸方に表示するかを決めて、どちらかに残高計算式を入れています。
 73行目大きな End If
 74行目 『合計』という文字を入れます。
 75行目 『残高』という文字を入れます。
 76行目 xlContinuous ひとつの科目データ領域に普通罫線を引きます。
 77行目 SKM = SKM + 4 書き出す行を4行追加して、次の科目エリア書き出しに備えます。
 78行目 NonTobashi: 飛び地。前述11行、12行目で仕訳帳の科目登録に『Non』があった場合は、何も処理せずにここまで飛ばして来ます。
 79行目 Next ASK 大きな繰り返しがここで繰り返し地点を迎えます。仕訳帳の登録勘定科目を5行目から39行目まで繰り返し読んで作業するという For-Next 文です。
 80行目 PageSetup.PrintArea で印刷範囲を設定しています。すべての科目が終了してからなので、印刷範囲の設定行は想像がつくと思います。
 81行目と82行目 マクロを終了する前にページが一番上を表示しカーソルが一番左上A1にあるように指示しています。
 これでこのマクロは終了します。

 もう一度言います。
 このマクロは、『仕訳帳』シートにあるデータを同じシートにある勘定科目登録にに従って、勘定科目ごとに『総勘定元帳』シートに総勘定元帳を作成するものです。仕訳帳の複式記帳を分解して、相手科目として組み立て直します。