オフィスソフト

「Excel」で月/年ごとの残高小計を計算--日次残高の応用で複数の関数を利用

「Excel」で1日ごとの最終的な残高を計算する方法を以前の記事で紹介した。今回は月ごとの残高と年ごとの残高を求める。IF()関数に加えて別の関数も使用するが、比較的シンプルな数式だ。

 以前の記事「『Excel』で1日ごとの残高小計を計算--IF()関数と条件付き書式を利用」では、単純な収支計算シートで1日ごとの残高を計算した。このタイプの小計は一般的なもので、単純な数式(これを引いてあれを足す、など)によって実行できる。もっと特異なものを求めているのでなければ、それで問題ない。最初の記事では、単純なIF()関数を使用して、同じ日にあったすべての取引の合計を計算し、1日ごとの残高を算出した。今回の記事で紹介するのは、1カ月ごとの残高と1年ごとの残高を返す数式だ。

 筆者は「Windows 10」の64ビット版システムで「Microsoft 365」を使用しているが、古いバージョンを使っても構わない。作業しやすいように、ダウンロード可能なデモ用の.xlsxファイルと.xlsファイルを用意した。本記事で紹介する関数と数式はブラウザー版でも機能する。また、ここでは「Microsoft Excel」の基本的なスキルを習得済みという前提で説明を進める。

Excelで取引の合計と1日ごとの残高を計算する方法

 まずは図Aの単純な取引シートから見ていこう。E列の最初の数式は以下のとおりだ。

E3: =C3-D3

 最初の取引の貸方から借方を引いて、取引の合計を返す。以下は2つ目の数式で、残りのレコードにコピーされている。

E4: =C5-D5+E4

 これは、前の取引の合計を足して、その時点での合計、すなわち残高を計算する数式だ。

 F列の関数は以下のようになっている。

=IF(B4=B3,"",E3)

 これは、下の日付が現在の取引の日付と一致しない場合に、その時点での取引の合計を返す。この関数は、条件が満たされない場合にのみ合計を返す。つまりFALSEアクションだ。そのため、F列には空のセルがある。それらの空白は、複数の取引があった日であることを表している。この関数の結果はE列のその時点での合計と同じだが、1日ごとの小計を他の日次の合計と分けておくと区別しやすくなる。このシートには、1日ごとの小計を強調表示する条件付き書式も含まれている。

図A

取引の合計と1日ごとの小計を返す単純なシート。
取引の合計と1日ごとの小計を返す単純なシート。

 この時点で、取引(レコード)を日付順(B列)に並べ替えておくことが重要だ。データの性質を考えると、ほとんどの場合、レコードはすでに日付順になっているはずだが、そうでない場合はB列を基準に並べ替える必要がある。そうしないと、取引の合計と1日ごとの小計の数式が正しい結果を返さない。また、オレンジ色のフォントは(ダウンロード可能なデモファイル内の)条件付き書式であり、1日ごとの小計を強調表示する。1日ごとの小計の関数については以上だが、次は別の期間の小計、すなわち1カ月ごとの小計に進もう。

Excelで1カ月ごとの残高を追加する方法

 ここまで見てきたように、1日ごとの残高を計算するために必要なのは、日付を照合してから毎日の残高を返す単純なIF()関数だけだ。では、1カ月ごとの残高も必要な場合を考えてみよう。これには、各取引の日付の月の値を照合する必要がある。幸い、先ほど使用した1日ごとの残高を計算する関数と同じロジックを利用可能だ。

=IF(B4=B3,"",E3)

 この式の条件にMONTH()関数を追加する。

=IF(MONTH(B4)=MONTH(B3),"",E3)

 上記の関数をセルG3に入力し、残りのシートにコピーしよう(テーブルオブジェクトを使用している場合は、関数をコピーする必要はない。テーブルオブジェクトがコピーしてくれる)。図Bはその結果を示している。月の合計は4月と5月の2つだけだ。

図B

MONTH()関数を追加して、1カ月ごとの残高を求める。
MONTH()関数を追加して、1カ月ごとの残高を求める。

「オフィスソフト」で読まれている記事

TechRepublic Japanで人気の記事

編集部オススメ

トレンドまるわかり![PR]

財務・経理
人事・労務
マーケ・営業
購買・調達
生産・製造
データ分析
コミュニケーション
通信・通話
文書・コンテンツ
PC・モバイル
PC
スマートフォン
タブレット
ウェアラブル
オフィスソフト
OCR
RPA
PCソフト
周辺機器
新興技術
ITインフラ
クラウドサービス
OS・ミドルウェア
開発
データベース
運用
セキュリティ

ホワイトペーパーランキング

  1. マンガでわかる「ルール駆動開発」レガシーモダナイズを成功させる開発手法を基礎から理解する
  2. 5分でわかる、レポート作成の心得!成果至上主義のせっかちな上司も納得のレポートとは
  3. APIエコシステムを狙うサイバー攻撃が急増、Webアプリにおける最新のAPIセキュリティ対策とは?
  4. クラウドやコンテナ利用が増える中、世界の企業が利用するAPI経由の安全なアプリ構築手法とは?
  5. ウェビナーによる営業活動が本格化、顧客接点が増加する一方で見えてきたハードルと解決策とは?

Follow TechRepublic Japan

このサイトでは、利用状況の把握や広告配信などのために、Cookieなどを使用してアクセスデータを取得・利用しています。 これ以降ページを遷移した場合、Cookieなどの設定や使用に同意したことになります。
Cookieなどの設定や使用の詳細、オプトアウトについては詳細をご覧ください。
[ 閉じる ]