「Excelマクロで、会計ソフト推移表データ→Excelの表」はかっこいいけど、おすすめしない理由

  • URLをコピーしました!

Excelマクロでどこまでやるべきか?という質問をよくいただきます。
私の答えは、「マクロを使わなくていいなら使わないほうが、全体のコストは下がります」というものです。

ex-it_04

データを瞬時にExcelの表に

Excelマクロ(プログラム)を書けば、こういった会計ソフトからエクスポートしたデータ(推移表の画面からエクスポート。Excelボタンを使ってはいけません)を

ex-it_01

ボタン1つで、こういった表にすることができます。

ex-it_02

楽ちんですし、見ていてかっこいいです。
■動きます


しかし、効率化は、かっこいいよりも速くできるかどうか、名より実。

ここにマクロを使うのはおすすめしていません。

Excelマクロを覚える・書く・メンテするコスト

Excelマクロを書いてしまえば、一瞬で表がつくれます。
今回の事例は、こういったものを書きました。

Sub suii()

'シートの削除

    Dim w As Worksheet 
    For Each w In Worksheets 
        If w.Name <> "data" And w.Name <> "推移表" Then 
            
            Application.DisplayAlerts = False 
            w.Delete 
            Application.DisplayAlerts = True 
            
        End If 
    
    Next 
   

'シート[data」のコピー 
    Worksheets("data").Copy after:=Worksheets(Worksheets.Count) 
    
'余分な行、列の削除 
    Range("1:104,106:122,154:156,159:160,163:202").Delete 
    
    Range("a:c,m:t").Delete 
    
'[]の項目を削除 
    Dim i 
    For i = Range("a" & Rows.Count).End(xlUp).row To 1 Step -1 
        If InStr(Range("a" & i).Value, "[") <> 0 Then 
        
            Rows(i).Delete 
             
        End If 
        
    Next 
   

'年月の行を挿入 
    Rows("1").Insert 
    
'年月を入力 
    Range("b1").Value = "2017 / 4 / 1" 
    Range("b1").NumberFormatLocal = "yyyy/m" 
    Range("b1").AutoFill Destination:=Range("b1:m1"), Type:=xlFillMonths 
    
    
'合計欄の作成 
    Range("n1").Value = "合計" 
    
    Range("n2").Value = "=sum(a2:m2)" 
    Range("n2").AutoFill Destination:=Range("n2:n34") 
    
'数値の書式を桁区切りに 
    Range("b2:n50").NumberFormatLocal = "#,##0" 
    
'科目にインデント 
    Range("a3, a5:a29, a34:a36, a40:a41, a43").IndentLevel = 1 
    
'列の幅、行の高さ調整 
    Columns("a").AutoFit 
    Rows("1:34").RowHeight = 15 
    
    
'科目列に罫線 
    Range("a1:a44").Borders(xlEdgeRight).LineStyle = xlContinuous

'売上高に罫線、色 
    Range("a2:n2").Borders(xlEdgeTop).LineStyle = xlContinuous 
     Range("a2:n2").Borders(xlEdgeBottom).LineStyle = xlContinuous 
    Range("a2:n2").Interior.Color = 14408946 
    
'利益に罫線、色 
    Range("a4:n4,a31:n31,a34:n34").Borders(xlEdgeTop).LineStyle = xlContinuous 
    Range("a4:n4,a31:n31,a34:n34").Borders(xlEdgeBottom).LineStyle = xlContinuous 
    Range("a4:n4,a31:n31,a34:n34").Interior.Color = 65535 
    
'販管費計に罫線、色 
    Range("a30:n30").Borders(xlEdgeTop).LineStyle = xlContinuous 
    Range("a30:n30").Borders(xlEdgeBottom).LineStyle = xlContinuous 
    Range("a30:n30").Interior.Color = 15853276 
    
End Sub

 

シートの削除(繰り返し使えるように削除しています)

シート「data」のコピー(会計ソフトのデータをそのまま加工するのはおすすめできないので、いったんコピー)

余分な行、列の削除

A列からC列はr必要ありませんし、余分なデータも削除します。ex-it_01

その他、[販売管理費]といった余分な行もあります。
行を指定してプログラムを書くのも大変なので、オートフィルターをかけて、[を含む行を削除しました。

‘[]の項目を削除
Dim i
For i = Range(“a” & Rows.Count).End(xlUp).row To 1 Step -1
If InStr(Range(“a” & i).Value, “[“) <> 0 Then

Rows(i).Delete

End If

Next

年月を入力

データだと、4月度、5月度・・・といった形式なので、2017/4、2017/5といったものを新たに入れなおします。
オートフィルで、12か月分コピーしました。

‘年月の行を挿入
Rows(“1”).Insert

‘年月を入力
Range(“b1”).Value = “2017 / 4 / 1”
Range(“b1”).NumberFormatLocal = “yyyy/m”
Range(“b1”).AutoFill Destination:=Range(“b1:m1”), Type:=xlFillMonths

合計欄の作成
合計欄をつくって、合計する関数(SUM)を入れます。

Range(“n1”).Value = “合計”

Range(“n2”).Value = “=sum(a2:m2)”
Range(“n2”).AutoFill Destination:=Range(“n2:n34”)

数値の書式を桁区切りに

全体を桁区切りにします。

‘数値の書式を桁区切りに
Range(“b2:n50”).NumberFormatLocal = “#,##0”

科目にインデントをつけたほうが見やすいのでつけています。

‘科目にインデント
Range(“a3, a5:a29, a34:a36, a40:a41, a43”).IndentLevel = 1
image

見やすくするために列の幅、行の高さを調整しています。

‘列の幅、行の高さ調整
Columns(“a”).AutoFit
Rows(“1:34”).RowHeight = 15

罫線や色を入れていきます。
image
‘科目列に罫線
Range(“a1:a44”).Borders(xlEdgeRight).LineStyle = xlContinuous

‘売上高に罫線、色
Range(“a2:n2”).Borders(xlEdgeTop).LineStyle = xlContinuous
Range(“a2:n2”).Borders(xlEdgeBottom).LineStyle = xlContinuous
Range(“a2:n2”).Interior.Color = 14408946

‘利益に罫線、色
Range(“a4:n4,a31:n31,a34:n34”).Borders(xlEdgeTop).LineStyle = xlContinuous
Range(“a4:n4,a31:n31,a34:n34”).Borders(xlEdgeBottom).LineStyle = xlContinuous
Range(“a4:n4,a31:n31,a34:n34”).Interior.Color = 65535

‘販管費計に罫線、色
Range(“a30:n30”).Borders(xlEdgeTop).LineStyle = xlContinuous
Range(“a30:n30”).Borders(xlEdgeBottom).LineStyle = xlContinuous
Range(“a30:n30”).Interior.Color = 15853276

 

こんな感じで書けるのですが、私はこういったところでマクロを使っていません。
今回はネタのために書きました。

Excelマクロ、プログラムには、3つのコストがあります。

1つ目は、書くコスト。

書くのもそれなりに大変です。
ちょっとずつ書き、テストしてうまくいったら次へというのを繰り返します。
この記事も、もう2時間が書き始めて2時間です……。

2つ目は、学習コスト。
ググればいいといえばいいのですが、やりたいことをプログラミングするには、それなりに勉強しなければいけません。
覚えるもの、学ぶものが少なければ少ないほど学習コストは下がるわけです。
マクロに限らず、私は、できるかぎり、覚える、使うものを少なくして伝えるようにしています。

この事例でも、次のようなものは、普段使っていません。
・複数の範囲を指定して削除
・行の挿入
・年月の入力、オートフィル
・インデント
・高さの調整
・罫線、色
などは、思い出しつつ調べつつ書きました。
それくらい使っていないのです。

マクロセミナーでお渡ししている資料に、Excelマクロの事例があり、それが26個あります。

その中にも上記のプログラムは入れていません。
使用頻度が低いからです。
image

3つ目はメンテナンスコスト。

イレギュラーがあったり、他で活用するとなるとメンテナンスしなければいけません。

このマクロは大きな問題もあります。
・科目が増えたら、削除する位置が変わる(それを見越してプログラムを書くと、さらに大変)
・何月までのデータ化で、削除する位置が変わる
・他の会社で使うなら、メンテナンスする必要がある

このコストも含めて考えなければいけません。
とはいえ、この作業を手で、毎月、繰り返しやるよりはコストは低いのですが、もっといい方法はあります。

ExcelでできるならExcelで

今回の事例だと、表のフォーマットはExcelでつくっておいて、数値の連動は、VLOOKUP関数を使います。
image

この数式を1つ入れて、あとはコピーすればすむわけです。
科目が増えたときにはメンテナンスは必要になりますが、全体のコストはマクロよりも低いといえるでしょう。

もちろん、プログラミングがめちゃくちゃ得意なら、Excelマクロですべて書いて、あらゆるケースを想定し完璧なプログラムをつくることもできます。
ただ、そうできるまでにも学習コストはかかるわけです。

それなら、ExcelでできることはExcelでやるのをおすすめしています。

手間をかけないExcel資料作成。会計ソフト推移表→決算予測・売上グラフ・経費グラフ・移動年計グラフ・予算実績比較表・目標達成度グラフ | EX-IT

予実管理もVLOOKUPです。

Excelで予実管理・予実比較(予算実績管理・予算実績比較)。ーVLOOKUP関数の活用ー | EX-IT

Excel、マクロの境界線は人により違ってきますが、両方知っていてこそ、全体のコストを下げ、効率化につながります。
実は、昔の私は、冒頭の方法でやっていました。
しかも、今回のようなコードではなく、もっと原始的なものです。

四半期計や、科目の小計(人件費=給料+賞与+法定福利費といったもの)もマクロで書き、動くのを見て喜んでいました。

確かにかっこいいんですけどね……。
今のPCはパワーがあるので一瞬で終わりますが、昔はPCの処理能力も低く、その過程が見れました。
(Excelマクロには、画面の動きを見せないというプログラムもあり、それを使っていたこともありますが、今のPCだとほとんど必要ありません)

VLOOKUP関数でやったほうが、応用もききますし、メンテナンスのしやすさがあります。

Excelで覚えておきたいのは、VLOOKUP関数、グラフ、ピボットテーブルです。
この3つがあれば、マクロの出番を減らせます。
(さらには、ファイル、シートのつくり方も大事ですが)

サンプルはこちらです。
(シート「推移表」にVLOOKUP関数が入っています)

EX-ITサンプル 弥生エクスポート→推移表.xlsm

比べてみていただければ。

会計ソフトの推移表が見やすければ、こういった仕事自体なくなるのですが、まあ、しょうがないでしょうね。

今回の事例だと、A4だと4枚にわたって出ますわよ、奥さん。

ex-it_05


■編集後記

昨日は、Excelマクロプログラミング入門セミナーを開催しました。
セミナーは終わった翌日に復習し、毎回ちょっとずつ変更しています。
今回も、変更を入れてみました。

同セミナーは、10/25にも開催します。

■昨日の1日1新
※詳細は→「1日1新」

MacでPycharm
Pararells Destop14

■昨日の娘日記
「ないない」と片付けするときはするのですが、片付けのときに「ダー」と両手で払うようなしぐさをするときも。
き、気持ちはわかりますが…。

  • URLをコピーしました!