☰

Excelデータクエリ

tag-icon • tag-icon microsoft-excel worksheet-function date
Excelデータクエリ

予算監視スプレッドシートを作成しようとしています。カテゴリ別、月別のエントリの合計を取得したいと考えています。データは次の行にあり、支出をダウンロードするたびに毎月増加します。サンプル データは次のとおりです。

Date       Category    Amount
03-15-21   Auto        500.00
02-12-21   House       375.00

最終的には、列に月、行にカテゴリを表示して、予算を監視できるようにします。

カテゴリ別、月別にデータを抽出するにはどうすればよいでしょうか?

sumifs、sumproduct、index match を調べましたが、解決策が見つかりません。ご指導いただければ幸いです。

答え1

ピボット テーブル! テーブル全体を選択し、[挿入] > [ピボット テーブル] をクリックします。ピボット テーブル フィールド サイド バーが表示されるまで、指示に従います。次に、フィールドを適切なボックスにドラッグします。好みに合わせていろいろ試してみてください。まずは、

フィルターは空のままにします。日付を「列」セクションにドラッグします。カテゴリを「行」セクションにドラッグします。金額を「値」セクションにドラッグします。

Excel が日付を正しく認識した場合、ピボットは月ごとに自動的にグループ化されます。そうでない場合は、ヘッダーが Month の新しい列 B を挿入し、=EOMONTH(A2,0)B2 に数式を追加することもできます。ピボット テーブルでは、Date ではなく Month を使用します。最初にピボットを作成した後に列を追加する場合は、ピボット テーブルを更新する必要があります。EOMONTH で引き続きエラーが発生する場合は、日付形式が地域の日付形式設定と一致していることを確認してください。

ヒント： ピボット テーブルとグラフは、Excel のまさに宝石のような機能です。最初は理解しにくいかもしれませんが、一度理解してしまえば、もう後戻りすることはないでしょう。YouTube で「ピボット テーブルの使い方」の短いビデオを視聴するといいでしょう。

答え2

ピボットテーブルを直接使用できます:

ここに画像の説明を入力してください

答え3

これにより問題は解決します:

ここに画像の説明を入力してください

使い方：

  • ヘルパーデータのY34の式:

    =TEXT(V34,"mmmm")

  • 一意の月名を表す Z34 の配列 (CSE) 式:

    {=IFERROR(INDEX($Y$34:$Y$49,MATCH(0,COUNTIF($Z$33:Z33,$Y$34:$Y$49),0)),"")}

  • AA33 の一意のカテゴリ名の配列 (CSE) 式:

    {=IFERROR(INDEX($W$34:$W$49,MATCH(0,COUNTIF($Z$33:Z33,$W$34:$W$49),0)),"")}

注意

  • 配列数式を終了するにはCtrl+Shift+Enter。

  • 後でわかりやすくするために、列 Y のヘルパー データを非表示にすることができます。

  • セル AA34 の数式:

    =SUMPRODUCT((TEXT($V$34:$V$49,"MMMM")=$Z34)*($W$34:$W$49=AA$33)*($X$34:$X$49))

範囲全体に数式を入力します。

必要に応じて数式内のセル参照を調整します。

関連情報