PC関連

エクセルで管理するクレジットカードの使用記録

投稿日:2018-04-08 更新日:

仕事で実際にあった例をもとにした「クレジットカード使用記録簿」です。

規模は小さめの会社でした。

変更前の使用簿

引き継いだクレジットカードの管理簿は、エクセルに入力していましたが、紙の記録のようなものでした。

エクセル・クレジットカード使用簿

従来の方法

  • クレジットカード使用日で抽出ができない。
  • 引落日の合計金額がわからない。
  • 記録簿に入力後、仕訳入力をするという方法だった。二度手間だし、入力ミスも多くあった。
  • 記録簿と仕訳データが一致しているかの確認が大変すぎた。
  • クレジットカードの明細書と照合しておらずに計上漏れがあった。

理由があってこのような管理表にした可能性もあるかな、と思ったため、しばらく従来通りの方法で管理しました。どうやら何も理由がないよう。非常に不便だったため変更しました。

管理が楽になるクレジット使用簿の作成方法

クレジットカード使用簿の例

エクセル・テーブル設定

日付は「月」と「日」を分けず、日付で入力します。上の表では「使用日」と「引落日」の書式設定を日付にします。この例では、ユーザ定義で「mm/dd」で設定しています。

クレジットカード使用簿に入力したデータから集計した結果の例

エクセル・テーブル設定

この例では7月4日に引落される金額がわかります。

  • 内訳:使用した月、日付で表示することも可能。
  • 総計:7月4日の引き落とし金額

データ入力は、仕訳データをコピー&ペーストしています。会計システムからCSVデータを取り出せば簡単。会計システムへCSVデータをインポートしている場合は、インポートデータを利用します。

テーブルに変換する

テーブルに変換しておくと、管理が楽になります。例えば、集計をピボットテーブルで行う場合、データを追加した後にデータの範囲を変更する必要がありません。

(1)クレジットカード使用簿のデータ内の任意のセルを選択します。

(2)リボン [ 挿入 ] > [ テーブル ] > [ テーブル ] を選択します。

→「テーブルの作成」が表示され、データ範囲が点線で囲まれてます。

エクセル・テーブル設定

(3)「OK」をクリックします。

エクセル・テーブル設定

テーブル変換の設定は以上で終わりです。

テーブルに変換された状態

テーブルに変換されるとカラフルな表示になります。

エクセル・テーブル設定

テーブルに変換されている場合は、テーブルの最終行の右端が緑色になっています。

エクセル・テーブル設定

テーブルスタイルの変更

テーブルのスタイルは好みで変更できます。

(1)テーブル内の任意のセルを選択します。

→ リボン [ テーブルのデザイン ] が表示されます。

(2)「テーブルのスタイル」より変更したいスタイルを選択します。

エクセル・テーブル設定

テーブル変換前の表示にしたい場合は、「なし」にします。

エクセルスタイル

テーブル名の変更

テーブル名を変更することができます。

自動的につけられたテーブル名

エクセル・テーブル設定

ここでは、「shiyou」に変更しました。

エクセル・テーブル設定

ピボットテーブルの設定

ピボットテーブルの設定をします。

(1)テーブルのデータ内の任意のセルを選択します。

(2)リボン [ 挿入 ] > [ テーブル ] > [ ピボットテーブル ] を選択します。

エクセル・テーブル設定

または、リボン [ テーブルデザイン ] > [ ツール] > [ ピボットテーブルで集計 ] を選択します。

エクセル・テーブル設定

→ ピボットテーブルの作成が表示されます。

(3)テーブルまたは範囲を選択のテーブル範囲が合っているか確認します。

ここでの例はテーブル名を「shiyou」にしているので「shiyou」になっています。

(4)ピボットテーブルレポートを配置する場所を選択し、「OK」をクリックします。

ピボットテーブルの設定

(5)「ピボットテーブルのフィールド」の設定をします。

  • フィルター:引落日
  • 行:使用日
  • 値:金額(合計)

エクセルピボットテーブル

(6)金額の表示形式を「通貨」または「会計」に設定します。

(7)使用日の表示を「月」にします。

(7)-1.使用日の任意のデータを選択し、リボン [ ピボットテーブル分析 ] > [ グループの選択 ] を選択します。

エクセル・テーブル設定

→ 「グループ化」が表示されます。

(7)-2.グループ化で「月」を選択します。

エクセル・ピボットテーブル

完成・金額のチェック

金額のチェック

  • 会計システム等の元帳残高と引落日が空欄の合計が一致しているか。
  • 引落日の合計がクレジットカードの請求金額と一致しているか。
  • 計上金額が合っているか。

エクセル・ピボットテーブル

引落日で抽出できます。

エクセル・ピボットテーブル

行ラベルの名前を項目名に変更する

(1)データ範囲をクリックします。例えば「行ラベル」のセルA3。

(2)リボン [ デザイン ]>[ レポートのレイアウト ]>[ アウトライン形式で表示する ] を選択します。

エクセル・ピボットテーブル・行ラベルの変更

行ラベルが「使用日」に変更されました。

エクセル・ピボットテーブル・行ラベルの変更

 

グループ化できない場合

グループ化ができない場合は、データの書式設定を確認します。

ここでの例では、使用日のデータがすべて「日付」になっているか確認します。

表示形式の分類が「日付」になっているか。

エクセル・テーブル設定

ここでの設定はユーザー定義で「mm/dd」の日付設定をしています。

使用日と引落日が逆の例

フィルターに「使用日」、行に「引落日」の例です。

エクセル・ピボットテーブル

使用日をグループ化したい場合の注意点

ピボットテーブルの設定で最初にこのパターンにした場合、使用日をグループ化できません。はじめに使用日を行に設定後にグループ化します。そのごフィルターに使用日を設定すると月別に表示できます。

 

-PC関連
-

Copyright© バイクOBA3 , 2019 All Rights Reserved.