私はいろいろ検索してきましたが、なぜ私の数式が機能しないのかを見つけるのに苦労しています。
数式を投稿する前に、何をしようとしているのかを説明する必要がありますが、それは少し複雑です (少なくとも、これが私が考えられる唯一の方法です)。
**背景**
部門の予算（カテゴリ別に分類）に関するスプレッドシートを作成する必要がありますが、現在、財務システムから収入/支出をダウンロードすると、日付順に並べられたデータ行（生データ）が出力されるだけです。
目標1- エクスポートされた生データを自動的にカテゴリに分類し、各カテゴリで費やされた金額をテーブルに明確に表示します。達成
目標2- エクスポートされた生データをカテゴリ別に分類し、ユーザーの選択に基づいて特定の月のみを表示します。立ち往生
生データのエクスポート例:
表: データ2015
Date | Description of Expense | Amount | Codes
Jan-15 | Workshop for employee | 100000 | WRKS
Jan-15 | Workshop for employee | 100000 | WRKS
Feb-15 | Sponsors for employee | 200000 | SPON
Feb-15 | Sponsors for employee | 100000 | SPON
Feb-15 | Workshop for employee | 300000 | WRKS
Mar-15 | Sponsors for employee | 100000 | SPON
上記の表を、財務システムからエクスポートしたときに取得した RAW データ (「Data2015」という名前) の例として使用するとします (Excel 内で表に変換しました)。
目標 1 を達成するために行ったことは、カテゴリの説明を含む新しい表 (「Categories」という名前) を作成することでした。
表: カテゴリー
Categories | Codes
Workshop | WRKS
Sponsors | SPON
次に、以下のように別のテーブル (「Budget」という名前) を作成しました。
表: 予算
Categories | Codes | Amount
Workshop | WRKS | 500000
Sponsors | SPON | 400000
予算表の金額列に使用した式は次のとおりです。
=SUMIF(Data2015[Codes],[@Codes],Data2015[Amount])
したがって、この式は目標 1 を達成するのに役立ちます。
目標 2 を達成するために、次のことを試しました。
ユーザーが確認したい月を取得するためのテーブルを作成しました:
表: 条件
Month | X | Background
Jan-15 | x | 1/01/2015
Feb-15 | x | 1/02/2015
Mar-15 | x | 1/03/2015
特定の月の経費を確認したい場合は、ユーザーは X 列に「x」を入力します。
私は、背景列で次の数式を使用しました。
=IF([@X]="x",TEXT([@Month], "d/mm/yyyy"),"")
次に、前と同じように月をさらにカテゴリにフィルターします。
上記のカテゴリ テーブルに似ていますが、ユーザーが見たいものに基づいて特定の月を対象としています。
なんとか機能させることができましたが、テーブル全体ではなく 1 行のみでした。私
の数式を確認して、間違っている点や、目標 2 を達成するためのロジックが間違っている点をお知らせください。あなたの洞察は非常に役立ちます。非常に混乱した状況で申し訳ありません。もっと簡単に説明できる方法が思いつきません。
次の数式を使用して、Data2015 シートを模倣する別のスプレッドシートを作成しました。
=IF(ISNUMBER(SEARCH(Month_View!$D$5, $A5)),Data2015[@Account], IF(ISNUMBER(SEARCH(Month_View!$D$6,$A5)),Data2015[@Account],""))
Month_View!は表: 条件が位置し、$D$5は1月15日、$D$6は2月15日などとなります。
この数式は、A5（日付がある列）が表: データ2015の日付列（同じ行にあるため @ が付いています）。
ここまでは、セル A5 に「Jan-15」または「Feb-15」と入力すれば、すべて問題なく動作します。ユーザーの好みに基づいてこのセルにデータを入力するには、次の数式を使用しました (必要な結果は得られません)。
=IF(Condition[Background]=TEXT(Data2015[@Date],"d/mm/yyyy",Data2015[@Date],"")
私にとって、この式は次のことを行うことを意味します
。データ2015表は、日付/値（「d/mm/yyyy」の形式）の1つと一致します。状態「背景」という列（ユーザーが表示したい月に「x」を入れた場合にのみ表示されます）に、このセルに次の値を入力します。データ2015の日付列です。
セルに「Jan-15」または「1/01/2015」が入力されると、テーブルの残りの部分にも入力され、目標 1 を達成するために使用した数式を使用して、カテゴリ別にさらにフィルター処理できます。
しかし、セルには「Jan-15」や「1/01/2015」は入力されず、代わりに空白または#VALUEが表示されます。
これは、状態@ 付きのテーブルも必要ですが、これは不要です。
混乱していますが... 私が何を達成しようとしているのか理解していただければ、どんな洞察や議論でも役に立ちます。おそらく、この段階では私の脳が過負荷になっているだけでしょう。
答え1
Excel に組み込まれているテーブル機能を使用します。数式は必要ありません。範囲を選択して、Ctrl + T を押して開始します。
これにより、一番上の行のフィルターがオンになります。ユーザーはフィルターを使用して、年や月で日付をフィルターできます。
合計機能については、合計行を追加し ([表ツール] / [デザイン] リボンを使用)、その行で各列に必要な集計 (合計、カウントなど) を選択できます。これは、フィルタリングに従って自動的に調整されます。
答え2
はい、解決しました！
私が使用した式は次のとおりです。
=SUM(SUMIF(Data2015BG1[Category],[@Codes],Data2015GB1[Amount]))
*追記: 参照内容が私の質問で示した例と異なる場合は申し訳ありません。
Data2015BG1 シートには、トリガー列に「x」が含まれているかどうかを検出し、選択した月に基づいて必要なデータを表示する数式があります。使用した数式は次のとおりです。
=IF(ISNUMBER(MATCH(TEXT(Data2015[@Period],"d/mm/yyyy"),Condition2015A[Background],0))=TRUE,Data2015[@Period],"")
それが意味を成すといいのですが、基本的に私がやったことは誰でも簡単に使えるようにすることだったので、視覚的に何を達成できたかを正確に示すために写真を投稿したかったのですが、まだ画像を投稿するのに十分な評判がありません。次回にしましょう。
フィルターは機能するでしょうが、私は Excel の経験がほとんどない人のためにこれを作成しているので、フィルターを要求することさえ少しやりすぎです (... わかっています、わかっています...)