MFクラウド会計へExcel現金出納帳をインポートする最適解・Excelマクロ

MFクラウド会計に、現金出納帳や経費データをインポートする場合、どうやるのが楽かどうか、まとめてみました。

MFクラウド会計のExcel現金出納帳でインポート

クラウド会計ソフトは、ネットバンクやクレジットカードのデータを連動できるので入力せずにすみ、楽です。
ただ、現金出納帳（現金商売の場合）や自分が立て替えた（個人のカード、ICカード、現金）は、入力せざるを得ません。

もちろん、MFクラウド会計で直接入力する方法もありますが、ここではその量が多い、またはクラウド会計で入力したくないというケースを考えます。
現金出納帳で考えれば、クラウド会計は、Excelの現金出納帳をそのままインポートできることが多いです。

従来型のソフトだと、仕訳形式に変換してCSVファイルとして保存しなければいけません。
（例） 会議費 現金 302 スタバ 打ち合わせ

そのままインポートできるのは楽ですし、Excel→CSVへの変換の手間もないのはうれしいことです。
上書き保存すれば、Excelファイルを開いていてもインポートできる点、二重になるデータ（普通預金への入金や普通預金からの出金は、ネットバンク側でも連動しているので現金としてインポートすると二重になる）も防げます。

MFが準備しているExcel現金出納帳はこのようなもの。

ありがたいはありがたいのですが、「むむむ……」という見た目です。
便利とはいえ、使いたくないなぁと。

日付が月と日に分かれているのも違和感があります。
これは、月を省略できるというメリットがあるとのことなのですが、Excelでデータ処理する以上、こういった形式で入力するのは本来好ましくありません。
手書き時代の名残です。
（〃といれるのもNGです）

8-1、8-2、8-3と入れ、上と同じ日付ならCtrl＋Dで入れるなり、空白を上のセルと同じように置換する方法を使ったほうがいいでしょう。

また、入力しやすくするなら、入力規則（半角英数字を入れるべきところは自動的にそのモードになる）がおすすめです。
このままだと、日本語モードで日付を入れようとすると全角で入ってしまいます。
（設定方法は、後述します）

「科目」は、コード（番号）で入力するようになっていますが、番号をある程度覚えなければいけませんし、入力したほうが速いです。
テンキーで番号を入力すると速いのでしょうが、もはや必要なスキルですので、それよりも正確で速いタイピングスキルを鍛えたほうがいいでしょう。
コードで入力すれば間違いはないんですが。

コードと科目の対応は、Excel現金出納帳にある、この表をメンテナンスしなければいけません。
なお、このコードはMFにはなく、関連していないものです。
従来の会計ソフトだと、コードがないとインポートできないものもあります。

このExcel現金出納帳は、シートが保護されており、変更できない箇所がありますが、パスワード保護はされていません。
リボンの［校閲］→［シート保護の解除］で解除できます。

まあ、いろいろとありますが、このExcel現金出納帳どおりに入れれば、MFの［会計帳簿］→［仕訳帳］→［インポート］→［現金出納帳］で、ファイルを指定すればインポートできるわけです。

このフォーマットルールをどこまで厳格に守らなければいけないのかを試してみました。

・罫線、フォント、色は関係ない
・シート名は関係ない。ただし、一番左に現金出納帳のシートがなければいけない
・ファイル名は関係ない
・もともとは、xlsファイル（古いバージョンでも開けるExcelファイル）だが、xlsx（新しいバージョン）でも問題ない
・下記の画像の項目は必要
・A列は空ける、各項目の場所は同じものである必要（セルB1の「現金出納帳」はいらない。セルB2、C2の普段フォントが白で見えない部分は必要）
・科目のコードはMFでの設定はないので、なんでも入ってればいい

という感じです。

これをもとに自由なフォーマットや見た目にするのもいいでしょう。

とはいえ、このフォーマットでは入力しにくいので、好きな形式で入力し、この形式へ連動させる方法があります。

好きな形式のExcel現金出納帳をMFクラウド会計のExcel現金出納帳へ連動

たとえば、Excelでこのように現金出納帳を入力するとします。
テーブル機能を使い、日付は1つのセル（曜日を表示してもいいでしょう）、補助科目や部門を使わないなら欄をなくしました。
余計な欄がないほうがTabキーでカーソルを移動させるときのロスがありません。

テーブルを選択し［テーブルツール］の［テーブルスタイル］から色を変更することもかんたんです。

［データ］→［データの入力規則］の［日本語入力］タブで、オフにすれば、半角英数字しか入らなくなり、自動的に半角英数モードに切り替わります。
同様に、B列とC列は、日本語入力をオンにしておきましょう。

このデータを、MF形式に連動させます。
MF形式のインポートデータが一番左にあるようにしておきましょう。

（そうでないとインポートできません）

数式は、単純に「＝」で選択するだけです。
日付だけは、「8-1」から月だけ取り出すMONTH関数、日だけ取り出すDAY関数を使いましょう。

 

サンプルはこちらです。
EX-ITサンプル　MF現金出納帳インポート.xlsx

Excel現金出納帳を仕訳形式にしてインポート

現金出納帳形式ではなく、仕訳形式にしてインポートすることもできます。
この場合は、CSVファイルに保存しなければいけません。

ただ、こちらの方法なら、経費、売上、給料そしてクラウド会計ソフトに連動できない・しないほうがいいデータデータにも応用がきき、一括してインポートできます。

このように左に出納帳、右に仕訳形式のデータを準備しましょう。

次のような数式です。

借方勘定科目は、入金か出金かでIFを使って処理してします。

MFの場合、取引NOは、必須ではありませんが、ないと、インポートしたデータの日付がすべて、1行目の日付になってしまうので、適当に「1」とでも入れておきましょう。

経費だとこんな感じです。
現金と違い、入金や残高欄は入りません。
科目は、法人なら「社長借入金」、個人なら「事業主借」を使います。

後述するマクロを使う場合に気を付けたいのは、インポートデータの開始列です。
他のデータの最大公約数的な列にしておきましょう。
マクロで読み取るときに、現金もH列、経費もH列、給料もH列でないと、それぞれで指定しなければいけなくなるので、あわせておいたほうがマクロの管理がしやすいのです。
もし、給料がJ列から始まるなら、すべてJ列にします。

 

今回の事例では、現金、立替経費のシートからシート「import」へ集めて、それをCSVファイルとして保存し、MFで取り込む流れです。

MFのテンプレートと、項目名を合わせておきましょう。
そうしないとインポート時に、こうやって選択しなければいけなくなります。

使っているマクロはこんな感じです。

1 すべてのシート（ただし、集計先のシート「import」は除く）で、

・インポート用データをつくる（入力されたデータの数だけ、インポートデータ用の数式をコピー）
・インポート用データを先月のデータをフィルタリングする
・フィルタリングしたデータをコピーして、シート「インポート」へ（フィルタリングしたもののみで、項目は除くという指定）
を繰り返す

2 新しいブックにシート「import」を貼り付け

3 CSVファイルで保存（このファイルがあるフォルダに「import.csv」という名前で保存

 

フィルタリングして必要な月のみつかうようにしないと、現実的に月ごとにファイルを変えなければいけなくなります。
少なくとも同じ年・年度は同じファイルにしておいたほうが便利です。

Sub import_MF()

 '■シート「import」の2行目以降を削除 

  
    Worksheets("import").Rows("2:" & Worksheets("import").Rows.Count).Delete
 '■シート「import」以外のシートで繰り返し処理 

  
    Dim Ws As Worksheet 

  
    For Each Ws In Worksheets 

  
    
        If Ws.Name <> "import" Then 

  
        
    
        '■各シートのデータ数をカウント 

  
            Dim Max_row As Long 

  
            Max_row = Ws.Range("a" & Ws.Rows.Count).End(xlUp).Row 

  
        
        '■インポートするデータの3行目（数式）をデータ数だけコピー 

  
            Ws.Range("h3", "m3").Copy Ws.Range("h4", "m" & Max_row) 

  
            
        '■「先月」でフィルタリング （「今月」ならCriteria1:=xlFilterThisMonth）

  
            Ws.Range("h2").AutoFilter 

  
            
            Ws.Range("h2").AutoFilter field:=2, Criteria1:=xlFilterLastMonth, Operator:=xlFilterDynamic 

  
            
                
        
               
            
        '■シート「import」の貼り付け位置＝最下行 

  
            Dim Import_Max_row As Long 

  
            
            Import_Max_row = Worksheets("import").Range("a" & Worksheets("import").Rows.Count).End(xlUp).Row 

  
        '■フィルタリングしたデータをコピーし、シート「import」の最下行の1つ下へ貼り付け 
  
    
            Ws.Range("i2").CurrentRegion.SpecialCells(xlCellTypeVisible).Offset(1, 0).Copy 

  
            Worksheets("import").Range("a" & Import_Max_row + 1).PasteSpecial Paste:=xlPasteValues 

  
            
            Columns("b").NumberFormatLocal = "yyyy/m/d" 

  
            
         '■オートフィルターを解除 

  
            Ws.Range("h2").AutoFilter 

  
        
        End If 

  
         
        
    Next 

  
    
    
    
    
 '■新しいブックに、シート「import」を貼り付け 

  
    Worksheets("import").Copy 

  
    
    
 '■import.csvという名称で、ファイルを保存 

  
    Application.DisplayAlerts = False 

  
    
         ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\import.csv", FileFormat:=xlCSV, local:=True 

  
    
        ActiveWorkbook.Close 

  
    
    Application.DisplayAlerts = True 

  
    
    
     
End Sub 

実際には、こういう形でつかうことが多いです。

 

サンプルはこちらです。
EX-ITサンプル　MF現金経費インポートマクロ.xlsm

マクロのメリットは、シートがいくら増えてもヒトの手間は変わらないこと。
ぜひつかってみていただければ。
※シートごとにインポートデータの開始列を合わせること、マクロをそれによって変えることが必要です。
※また、各シートの3行目にはインポート用データをつくる数式を入れなければいけません。
※間違いなくインポートされているか、もれはないか十分確認していただければ。

---

■編集後記

昨日は、メルマガオフ会を開催。
土曜日の昼、子連れOKという新たな試みでした。
ビール飲んで、終わったら娘とともに昼寝……。
夕方は地元の祭りに出かけました。

 

■昨日の1日1新

※詳細は→「1日1新」

マンション パーティルーム
子連れオフ会
地元のお祭り

 

■昨日の娘日記

昨日は、オフ会にて、参加者の方の娘さん（4歳、3歳）と会い、どんな反応を見せるかと思っていましたが、わりとなじんでました。
アンパンマンとかシールとか共通点がありましたし。
うちのaiboとかDVDプレーヤー（しまじろうDVD）も、一緒に遊んでもらってました。