オフィスソフト

「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. IT部門責任者が理解すべき「コンテナとKubernetes」の基礎を網羅
  2. Zoomなどオンライン商談による「ちょっとだけ打ち合わせ」の威力とは?新しい売り方の教科書が登場!
  3. データ活用のためのハイブリッドクラウド基盤構築-データプラットフォームに求められる12の要件
  4. 脱パスワード 不便と不安を取り除くSSO-メリットと導入方法、ADやM365との連携を解説
  5. 事例:9割の業務の段取りを効率化、個人スキル依存から脱却したDFE社のタスク管理改善術

Follow TechRepublic Japan

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