日別データから欠損月を追加して年月集計解答

公開日：2013年5月以前　最終更新日：2025-10-11

日別データから欠損月を追加して年月集計解答

エクセル練習問題解答ページです。

エクセル練習問題

A:Cの日々のデータから1年分（4月～3月）の年月集計表を作成します。
データが存在しない年月（欠損月）は0で出力してください。
E1に入れる数式を作成してください。
※年度はA列の最小日の年

日付	借方金額	貸方金額
2025/04/01	1078	862
2025/04/29	850	203
2025/05/24	-207
2025/05/24	827	968
2025/06/06	1791	460
2025/06/14		651
2025/07/08	883	421
2025/07/10	1367	567
2025/09/05	173	835
2025/09/12	1623
2025/10/11	1432	839

サンプルデータでは、2025年8月が抜けています。
したがって、2025年8月と2026年11月～2026年3月のデータを作成する必要があります。

シンキングタイム

シンキングタイム開始

シンキングタイム終了

当サイトのコンテンツ

自分に合った、学習方法を見つけましょう。

エクセル入門

エクセル作業において必須となるショートカットキーから基本の操作、機能、そして覚えておくべき関数とその使い方までを、入門・初級・初心者向けに解説しています。エクセルには多くの関数がありますが、どの業種でも必要とされる関数に絞って紹介しています。

エクセル基本操作

エクセルの基本操作について解説。知ってそうで知らない操作や高度な操作まで。日付、時刻に関するあれこれ数値の書式のあれこれ１．千単位、百万単位２．不要な0を表示せずに、小数点位置を揃える３．ユーザー定義書式で条件付き書式を使う４．Excel2003で、条件によりフォント色を５段階で表示する方法。

エクセル関数応用

エクセルの関数の応用技の解説。関数サンプルと必須の基本技術から応用・高等テクニックまでを紹介しています。１年後の日付、○か月後の日付複数条件の合計・件数・サンプルデータ・複数条件の合計・複数条件の件数・スピルと新関数入力規則のリストを、追加・削除に自動対応で作成一覧リストを使用して、「入力規則」の「リスト」を作…

ここで、じっくり勉強して下さい。

エクセル練習問題解答

では解答です。

E1の数式

=LET(
tbl,A:.C,
dm,EXPAND(EDATE(DATE(YEAR(MIN(TAKE(tbl,,1))),4,1),SEQUENCE(12,,0)),12,3,0),
ar,VSTACK(tbl,dm),
df,INDEX(ar,,2)-INDEX(ar,,3),
ym,LET(d,TAKE(ar,,1),d-DAY(d)+1),
hd,{"年月","借方金額","貸方金額","借方-貸方"},
VSTACK(hd,GROUPBY(ym,HSTACK(DROP(ar,,1),df),SUM,1,0))
)

数式の処理概要

全月リストの生成: 元データに存在する日付に関わらず、会計年度（4月始まり）のすべての月（12か月分）を生成し、その月を表現するダミーデータ（dm）を作成します。
データ結合: 元データ（tbl）と、上記で作成した全月リストのダミーデータ（dm）を結合（ar）します。
差額の計算: 借方と貸方の差額（df）を計算します。
年月列の作成: 結合されたデータ（ar）から日付を取り出し、その月の1日（ym）に変換します。
集計: 作成した年月（ym）をキーとして、借方、貸方、差額を集計（GROUPBY）します。
結果の整形: 見出し（hd）と集計結果を縦に結合（VSTACK）して最終結果を出力します。

各変数の説明

tbl：借方、貸方の3列を持つ元のデータ範囲

入力データ範囲です。
Trim Refs（TRIMRANGE関数）を使って指定しています。

TRIMRANGE関数は、範囲または配列の外側の端から空の行や空の列を除外します。列全体や行全体に対する数式では、不必要な（データのない）セルも含めて計算してしまうため、非常に効率が悪く余分な処理時間がかかってしまいます。TRIMRANGE関数を挟んで必要な範囲・配列だけにすることで、計算効率・パフォーマンスが大幅…

dm：元データに欠けている月を補うための、12か月分の日付と借方0、貸方0からなるデータ。

ar：元データ（tbl）とダミー月データ（dm）を結合した全データ。

df：差額列借方（2列目）から貸方（3列目）を引いた差額（借方-貸方）。

ym：年月キー集計のキーとするために、arの日付をその月の1日に変換したもの。

毎月1日のデータを作製しています。
ここは、TEXT関数で文字列として「yyyy年mm月」で統一しても構いません。
その後の使用方法次第で使い分けてください。

hd：見出し行最終的な集計表の列ヘッダー（"年月"など）。

{"年月","借方金額","貸方金額","借方-貸方"}
配列定数です。

この数式は一例です。
順に読み解いてみてください。

X（旧Twitter）の色々な人からの回答も参考にしてください。

参考ページ

LET関数（数式で変数を使う）

LET関数は、関数内で計算結果やセル範囲に名前を定義できます。これにより、数式の中間計算に名前を定義したり、後ろの引数で定義した名前を式に使う事が出来ます。これはプログラミングにおける変数と同じ機能になります。

SEQUENCE関数（連続数値）

SEQUENCE関数は、連続した数値の配列を作成します。1,2,3,…など、連続した数値の一覧を生成することができます。SEQUENCE関数はスピルで登場した新しい関数です。SEQUENCE関数の書式 =SEQUENCE(行,[列],[開始],[目盛り]) 行必須です。

VSTACK関数（配列を縦方向に順に追加・結合）

配列を縦方向（垂直方向）に順番に追加し、1つの大きな配列を作成して返します。VSTACK関数は各配列引数を行単位で上から順に（つまり下へ下へ）追加して新しい配列を作成します。横方向（水平方向）に結合する場合はHSTACK関数を使用します。

HSTACK関数（配列を横方向に順に追加・結合）

配列を横方向（水平方向）に順番に追加し、1つの大きな配列を作成して返します。HSTACK関数は各配列引数を列単位で左から順に（つまり右へ右へ）追加して新しい配列を作成します。縦方向（垂直方向）に結合する場合はVSTACK関数を使用します。

EXPAND関数（配列を指定された行と列に拡張する）

EXPAND関数は、配列（またはセル範囲）を指定された行と列のサイズに拡張した配列を返します。配列の拡張される部分は埋め込む値を指定できます。EXPAND関数の構文 =Expand(配列,行,[列],[pad_with]) 引数「行」または「列」の値が、引数「配列」の行数または列数より小さい場合は#VALUE!エラ…

GROUPBY関数（縦軸でグループ化して集計）

GROUPBY関数は、行（縦）でグループ化し指定された関数によって値を集計します。行（縦）の軸に沿ったグループ化と、関連する値の集計がサポートされます。複数の行グループレベルに対応しています。総計・小計、並べ替え、フィルター処理もサポートされています。

イータ縮小ラムダ（eta reduced lambda）

LAMBDAヘルパー関数のLAMBDA関数の記述部分を縮小記述するものです。明示的にLAMBDA関数を記述する場合に比べてイータ縮小ラムダは記述が短く扱いやすくなっています。もちろん明示的にLAMBDA関数を記述しても構いませんが、イータ縮小ラムダの記述が可能な場合は極力使うようにすることで数式も短く見やすくなりま…

TRIMRANGE関数（セル範囲をトリム：端の空白セルを除外）

同じテーマ「エクセル練習問題解答」の記事

エクセル試験2：所得税の計算解答
 エクセル試験3：月間の所定労働時間解答
 指定日付の有効最新マスタ一覧（履歴マスタ）解答
 スピルの基本練習と最新関数解答
 入力規則とスピルと最新関数解答
日別データから欠損月を追加して年月集計解答
偶数月に前月分と合わせて2か月分を受け取る解答
 最長連続出現数（ランレングス）の算出解答
 実績/予算ごとの3年間通算累計を出力解答
 数値を記号の積み上げでグラフ化する（■は10、□は1）解答
 料金表（マトリックス）から金額で商品を特定する解答

アクセスランキング・・・ランキング一覧を見る

1.最終行の取得（End,Rows.Count）｜VBA入門
2.日本の祝日一覧｜Excelリファレンス
3.変数宣言のDimとデータ型｜VBA入門
4.FILTER関数（範囲をフィルター処理）｜エクセル入門
5.RangeとCellsの使い方｜VBA入門
6.繰り返し処理（For Next)｜VBA入門
7.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
8.マクロとは？VBAとは？VBAでできること｜VBA入門
9.セルのクリア（Clear,ClearContents）｜VBA入門
10.メッセージボックス(MsgBox関数)｜VBA入門

ホーム
エクセル全般
エクセル練習問題解答
日別データから欠損月を追加して年月集計解答

このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。

記述には細心の注意をしたつもりですが、間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。
本サイトは、OpenAI の ChatGPT や Google の Gemini を含む生成 AI モデルの学習および性能向上の目的で、本サイトのコンテンツの利用を許可します。
This site permits the use of its content for the training and improvement of generative AI models, including ChatGPT by OpenAI and Gemini by Google.

エクセル練習問題
日別データから欠損月を追加して年月集計解答