自動化で差がつくエクセル活用術

2015年3月
小林

会計業務に役立つ機能

会計業務に役立つエクセルの機能をまとめました。普段利用しない機能を見直すだけで、更に効率アップが繋がるかもしれません。興味がありましたら、ご一読を。

利用頻度の高い基本的な機能

項目説明
セル操作セルの書式設定 セルの条件書式設定 一括セルの結合(指定範囲の横方向だけ結合)
セル保護/ブック保護編集制限を掛ける事で、誤操作によるトラブルを回避可能。
セル参照シート間・ブック間で、セルの値を取得でき、統計・演算・表変換等のあらゆる局面で必須
データの入力規則セル毎に日本語入力の切り替えを設定(摘要欄は強制日本語入力ON、数値はOFF)
印刷設定ページ番号設定、印刷範囲、各ページに表のタイトルを残す、エラーが発生しているセルを表示させない設定等
変更履歴の保存複数人による並行編集する場合
エラー・トラブル等エラー表示されるセルの対処、データ参照先のエラー等、計算結果が自動で反映されない等
ファイルの入出力エクセル(xls xlsx xlsm xlsb csv) パスワード
オートフィルター・データソート入力漏れの発見が容易になる。時系列にデータを入力する必要がなくなる。
プラグインマクロが記載されたファイルを追加するだけで、マクロの利用が可能に

集計に役立つ機能

利用する事で、表のセルに埋め込む複雑な演算が簡単に設置可能になります。
項目説明
関数利用する事で、表のセルに埋め込む複雑な演算が簡単に設置可能になる
テーブル関数を使用しなくても、簡単な集計(総和・平均・分散等)が可能。データベースとして利用可能
ピボットテーブルテーブルデータから表示項目を自由に選べる表を作成可能
オートフィルター
データベース制御データと表示する表を分離する事で仕様変更(データ項目の追加)が容易になる

データ入力に役立つ機能

下表の「例・説明」は、「項目」欄の理解を助けるもので、他にも役立つものがあります。
項目例・説明
ショートカットキー 例えば、
ctrl + c / ctrl + v (コピー 貼り付け)
F4 (直前のキー操作を繰り返す)
ctrl + 方向キー (データが存在するセルへ移動)
ctrl + PageUp / ctrl + PageDown (シート間移動)
ctrl + z / ctrl + y (undo redo)

マウス操作の小技例えば、マウス操作のみで連続した値の入力
関数例えば、数値・文字列の変換 / 表から別の表に変換 等
マクロ(ExcelVBA) 例えば、
・入力自動化(セルへの入力値を自動変換 1→「資金移動」 2→「売上返金」)
・表データを変換し、出力
・同じ手順を繰り返す反復入力
・ユーザ編集によって、崩れた表の修正(罫線・セルの参照先等・書式等)の自動化
・複雑になりがちな関数の組み合わせを、可読性の高いものに変更可能

視覚で理解できる資料に役立つ機能

例えば
  • グラフ機能(簡単な統計も可能。散布図→近似直線)
  • セルの背景を簡易グラフ

時短に繋がるエクセルの活用術

文字列を瞬間入力するマクロの活用法

セルに数字を入力すると自動で文字列に変換する手段があります。これを出納帳の摘要欄に適用する事で、入力作業が軽減できます。

出納帳で入力するのは、「年」「月」「日」「科目コード」「適用」「入金」「出金」の黄色の欄のみです。ほぼ全て数字入力なのですが、「摘要」のみが日本語で入力(エクセルの条件書式設定で、自動でIMEをONにする設定は可能)します。出来れば「摘要」も数字入力し、テンキーのみで済ませたいものです。
日本語入力必須の現金出納帳
この「数字入力のみ」をマクロ(ExcelVBA)で、実現できます。
変換テーブル(下表)を用意し、「摘要」に「1」と打ち込めば「資金移動」と自動で変換されます。もちろん他の数値も、テーブルに設定することで変換されます。
変換テーブル
摘要欄コード適用名
1資金移動
2切手代
3クリーニング代
他のメリットとして、「摘要」の記述の違いの排除があります。
「株式会社マシタ」「㈱マシタ」が混在していると、エクエルのオートフィルタや会計ソフトでは別物として処理されるため、検索時に不便です。この不便は一時的なものではありません。データは変換され二次利用されるケースがあるからです。 この不便が解消されるため、数字入力による変換は、継続的な効果があります。

マクロの実装

シート「入力補助」に変換テーブルを設置し、シートにマクロを記述します。
  
Private Const TEKIYOU_R_MIN = 7	'< 摘要欄の行開始番号
Private Const TEKIYOU_COL = 10	'< 摘要欄の列番号
Private Const HOJO_SHNAME = "入力補助"
Private Const CODE_TEKIYOU_TBL = "A3:B29"'< コード番号を適用名に変換するテーブル

Private Sub Worksheet_Change(ByVal Target As range)
    If Target.Row < TEKIYOU_R_MIN Then
        Exit Sub
    End If
    
    If Target.Column = TEKIYOU_COL Then
        Dim strValue As Range
        Dim cell As Range

        Application.EnableEvents = False
        For Each cell In Target
            Call CodeToName(HOJO_SHNAME, CODE_TEKIYOU_TBL, cell)
        Next
        Application.EnableEvents = True
    End If
End Sub

' コード番号から名前に変換
Private Sub CodeToName(sheetName As String, tableName As String, ByVal cell As range)
    Dim range As Range
    Dim foundCell As Range
    
    Set foundCell = Worksheets(sheetName).range(tableName).Find(What:=cell.Value, _
                        LookIn:=xlValues, LookAt:=xlWhole) 
    If foundCell Is Nothing Then
       	Exit Sub
    End If
    
    Dim strTemp As String
    strTemp = foundCell.Offset(0, 1)
    If 0 = Len(strTemp) Then
       	Exit Sub
    End If
    
    cell.Value = strTemp    
End Sub
  
これを更に発展させて、数字を文字列に変換すると同時に、更に科目コードや定額の金額も設定する事もできます。
「現金で、2万円の印刷機の月額のリース代を支払った」
「現金で、2,000円の月額のウォーターサーバー代を支払った」
「摘要」に数字を入力したら「摘要」と共に「科目コード」「出金」も設定するマクロを組み込みことで、実現できます。

出納帳で、自動仕分けするマクロ

借入金を返済すると通帳には引き落とし金額(元金45,000円 利息分 5,000円)が記載されています。しかし、元金と経費として処理できる利息は分けて処理するので、
「金融公庫返済 元金分 45,000」
「金融公庫返済 利息分  5,000」
と、出納帳に記載することもあります。この処理は、毎月定期的に発生し各金額も返済計画表の通りです。従って、機械的に変換できます。
預金出納帳で自動仕訳
預金出納帳で自動仕訳
このエクセル出納帳上の変換は、マクロを利用する事で実現できます。マクロは行の追加と別シートに記載した返済計画表を参照する関数を設定できるからです。他にも、課税と非課税が混在する支払い分ける事もできます。

崩れた表は、マクロで自動修正

データ入力作業で、様々なトラブルが発生します。
  1. 入力作業中にオートコンプリートが機能しなくなる!
    間に空のセルがあるとそこから先はオートコンプリート機能(過去の入力履歴から入力文字列を予測し表示する機能)が働きません。視界から外れた空セルは見落としがちです。
  2. 前に入力したデータが消えた!
    行や列が非表示になっているケースやオートフィルターによる設定のためです。設定者と閲覧者が別だと見落とすかもしれません。
  3. 気付かない間にセルの参照先が間違ったところに!
    相対パスでセルを参照していると、誤った編集で意図しないセルを参照するよう設定してしまうためです。
これらも全てマクロで解消できます。次のような処理を一つのマクロをボタン一つで呼び出すようにするだけです。
  1. 指定領域の空のセルをスペースに変換
  2. 非表示の行や列を再表示し、オートフィルターをフィルターが掛かっていない状態に
  3. 指定領域の相対パスの再設定
崩れた表が自動で修復され、先述のようなトラブルが解消されます。

通帳から預金出納帳を自動作成

預金口座にネットバンキングを利用していると、預金通帳と同じ取引明細をCSVファイルとしてダウンロードできます。 このファイルを読み込む会計ソフトもありますが、「相手科目(科目コード)」は記録されていません。あるのは「取引内容(摘要)」です。そのため会計ソフトで読み込んだ後、設定する必要があります。また、そもそもCSVファイルの読み込みに未対応の会計ソフトもあります。そこでCSVファイルをエクセルの預金出納帳に手作業でコピーし、「取引内容(摘要)」を元に「相手科目」を設定する事になります。 この操作をマクロで自動化できるのです。
預金出納帳で自動仕訳
エクセルは表や文字列の変換が容易で、上図のように「2015/8/28」を「2015」「8」「28」分けられます。更に前述のように「金融公庫返済」も「自動仕分け」も容易にできます。

既知の取引内容とは限らないため、変換できない相手科目も存在します。また必ずしも一意的に決まるものではありません。エクエル預金出納帳で編集する必要があります。しかし、手作業が入ったとしてもCSVファイルからの自動化は、預金通帳から手作業でデータ入力するよりも10倍は早くなります。
ネットバンキングを利用していれば、エクセルの預金出納帳の半自動化が可能です。また、マクロは会計ソフトが読めるCSVファイルを出力可能なので、更に時短に繋がります。

エクセルを開かずエクセル現金出納帳に書き込む方法

データ入力では、誤操作が避けられず、それに伴ってトラブルが発生します。そのトラブルの対処が容易ではないこともあります。多機能なエクセル故の事故です。この避け難い事故は、エクセルを介さないでエクセルファイルにデータ入力する方法で回避できます。

入力専用のウィンドウズアプリケーションを作成し、人の代わりに入力してもらいます。
excelファイルを制御するウィンドウズアプリケーション
また、ウィンドウズアプリケーションは、エクセルファイルの読み込みもできるので、原始資料に近いエクセル資料を別の表に加工も可能です。例えば、出金伝票から振替伝票の自動作成もできます。

スキル向上の具体的な手段

利用頻度の高い入門的な機能

既にexcelを利用できているため、不足している知識を補う事が能率的です。それには、事務所内で教えあう事が適切ですが、加えて、下記のような対策もあります。
無料体験できる入門的なセミナー
無駄にならないよう無料体験を実施しているセミナーをピックアップしました。
アビバ  ハロー!パソコン教室  
eラーニング
 
基本的な機能の理解に役立つ入門的書籍
必要になったら実現方法探すという使い方だけではなく、普段より機能を知る事で更なる利便性を生み出す新たな発想が生まれます。
書籍名著者出版社ISBN-10説明
知りたい操作がすぐわかる Excel2013全機能Bible高橋 慈子
八木 重和
技術評論社47741582673,218円
分かりやすい操作方法の説明。全体的に網羅している。
Excel大全 神ワザ1000+α-三才ブックス48619975421,725円
内容は薄いが、必要な機能の有無の把握が可能。
雑誌
日経PC
日常利用する機能を具体的な視点から説明しています。ビジネス向けの内容です。

関数

全てを詳細に知る必要はなく、関数全体を俯瞰し利用価値のあるものを利用する形で活用します。

下表の関数は、「関数区分」を分かりやすくするための例です。役立つ関数が他にも多数存在します。
会計業務に役立つ関数群
関数区分例えば関数の説明
論理関数IF()条件分岐
NOT()真偽を逆に返す
OR()いずれか真の時、真
情報関数ISERROR()任意のエラーの時、真
検索/行列関数VLOOKUP()特定範囲内の指定文字列に一致する文字列の取得
日付/時刻に関する関数DATE()年月日からシリアル値を返す
MONTH()シリアル値から月を返す
財務関数減価償却費、支払利息の算出
データベース関数-
統計関数COUNT()指定範囲内のデータの数を返す
COUNTIF()指定範囲内の条件に合致するデータの数を返す
SUMIF()指定範囲内の条件に合致するデータの総和を返す
文字列操作関数REPLACE()文字列内の文字列を指定文字列に置換
LEN()文字列長を返す
TRIM()文字列の前後のスペースを削除して返す
更に関数の機能を知る事に加え、作成における実践的な対処方法も必要になります。
  1. 容易に機能を追加・修正できる方法を知る
  2. デバッグ方法を知る
    例えば、
    ・数式タブ→数式の検証の利用する
    ・既存の関数で作成せず、可読性の高いユーザ定義関数を作成し、利用する
  3. 仕様変更を吸収しやすい方法を知る
    例えば、データの持ち方。行追加なので特定セルの位置が変化する場合、特定範囲に名前を付け名前で指定する方法があります。
参考になるセミナー

マクロ(ExcelVBA)

関数と同様に、実践的な対処法の知識も必要です。
  1. 容易に機能を追加・修正できる方法を知る
  2. マクロの外部ファイル化
  3. デバッグ方法を知る
  4. 仕様変更を吸収しやすい方法を知る
マクロを活用できれば、外部のデータベースとの連携の幅が広がるため、会計業務だけではなく事務所内のデータ管理にも役立ちます。
参考になるセミナー(関数と同じ)

定休日のお知らせ

真下 和男の著書

電子申告で便利に

ご契約までの流れ

お問い合わせ

提携企業等

真下公認会計士事務所
真下和男税理士事務所
〒355-0028
埼玉県東松山市箭弓町1-17-4
Tel0493-22-2813
Fax0493-22-2815