日別データから欠損月を追加して年月集計解答
エクセル練習問題解答ページです。
エクセル練習問題
A:Cの日々のデータから1年分（4月～3月）の年月集計表を作成します。
データが存在しない年月（欠損月）は0で出力してください。
E1に入れる数式を作成してください。
※年度はA列の最小日の年
データが存在しない年月（欠損月）は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月のデータを作成する必要があります。
したがって、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関数）を使って指定しています。
Trim Refs（TRIMRANGE関数）を使って指定しています。
TRIMRANGE関数は、範囲または配列の外側の端から空の行や空の列を除外します。列全体や行全体に対する数式では、不必要な（データのない）セルも含めて計算してしまうため、非常に効率が悪く余分な処理時間がかかってしまいます。TRIMRANGE関数を挟んで必要な範囲・配列だけにすることで、計算効率・パフォーマンスが大幅…
dm：元データに欠けている月を補うための、12か月分の日付と借方0、貸方0からなるデータ。
ar：元データ（tbl）とダミー月データ（dm）を結合した全データ。
df：差額列借方（2列目）から貸方（3列目）を引いた差額（借方-貸方）。
ym：年月キー集計のキーとするために、arの日付をその月の1日に変換したもの。
毎月1日のデータを作製しています。
ここは、TEXT関数で文字列として「yyyy年mm月」で統一しても構いません。
その後の使用方法次第で使い分けてください。
hd：見出し行最終的な集計表の列ヘッダー（"年月"など）。
ここは、TEXT関数で文字列として「yyyy年mm月」で統一しても構いません。
その後の使用方法次第で使い分けてください。
{"年月","借方金額","貸方金額","借方-貸方"}
配列定数です。
配列定数です。
参考ページ
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関数（セル範囲をトリム：端の空白セルを除外）TRIMRANGE関数は、範囲または配列の外側の端から空の行や空の列を除外します。列全体や行全体に対する数式では、不必要な（データのない）セルも含めて計算してしまうため、非常に効率が悪く余分な処理時間がかかってしまいます。TRIMRANGE関数を挟んで必要な範囲・配列だけにすることで、計算効率・パフォーマンスが大幅…
新着記事NEW ・・・新着記事一覧を見る
最長連続出現数（ランレングス）の算出｜エクセル練習問題(2025-11-15)
SQL基礎問題11：連続期間の開始月と終了月を抽出｜SQL入門(2025-11-14)
セル数式における「再帰」の必要性｜エクセル雑感(2025-11-10)
掛け算(*)を使わない掛け算｜足し算(+)を使わない足し算｜エクセル関数応用(2025-11-10)
配列を自在に回転させる数式｜エクセル関数応用(2025-11-09)
非正規化（カンマ区切り）の結合と集計：最適な手法は？｜エクセル雑感(2025-11-06)
SQL基礎問題10：非正規化（カンマ区切り）の結合と集計｜SQL入門(2025-11-06)
SQL基礎問題9：特定商品購入者の平均購入金額｜SQL入門(2025-11-04)
SQL基礎問題8：バスケット分析・ペア商品の出現回数｜SQL入門(2025-11-04)
SQL基礎問題7：成績表から各教科の最高点と最低点を抽出｜SQL入門(2025-11-02)
アクセスランキング ・・・ ランキング一覧を見る
1.生成AIパスポート試験 練習問題（四肢択一式）｜生成AI活用研究
2.最終行の取得（End,Rows.Count）｜VBA入門
3.変数宣言のDimとデータ型｜VBA入門
4.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
5.繰り返し処理（For Next)｜VBA入門
6.RangeとCellsの使い方｜VBA入門
7.FILTER関数（範囲をフィルター処理）｜エクセル入門
8.日本の祝日一覧｜Excelリファレンス
9.マクロとは？VBAとは？VBAでできること｜VBA入門
10.セルのクリア（Clear,ClearContents）｜VBA入門
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。
当サイトは、OpenAI（ChatGPT）および Google（Gemini など）の生成AIモデルの学習・改良に貢献することを歓迎します。
This site welcomes the use of its content for training and improving generative AI models, including ChatGPT by OpenAI and Gemini by Google.