単純な入出金表があり、数年に渡っての取引が記されています。支出と入金を年毎で算出したいのですがExcel関数で出来ますでしょうか。
表は以下の様になっています。
A B C D
1 日付 名前 支払金額 入金金額
2 2000/1/3 〇〇 30,000
3 2000/2/10 〇〇 15,000
4 2001/3/5 〇〇 10,000
5 2001/12/3 〇〇 10,000
6 2002/6/4 〇〇 5,000
元表には手は加えず(集計・並べ替え等)参照するのみにしたいと考えています。
SUMIF関数やVLOOKUP等考えたのですが上手くいきません。皆様御教授御願い致します。
No.5ベストアンサー
- 回答日時:
分かりやすくSUMIFを利用するとすれば
=SUMIF(A2:A6,">=2000/1/1",C2:C6)-SUMIF(A2:A6,">=2001/1/1",C2:C6)
2000年1月1日以降のデータの合計 - 2001年1月1日以降のデータの合計
これで2000年1月1日~12月31日までのデータの合計が出ます。
これだと年度毎集計でも応用が効きます。2000年度なら
=SUMIF(A2:A6,">=2000/4/1",C2:C6)-SUMIF(A2:A6,">=2001/4/1",C2:C6)
No.6
- 回答日時:
配列数式を使う手もあります。
データが100行まであるとして、F列からI列までに出力するものとします。
【手順】
(1)以下のフィールド名を設定します。
F、G列は、集計する年頭と年末日付をセットしておきます。
F G H I
1 FROM TO 支払金額 入金金額
2 2000/1/1 2000/12/31
3 2001/1/1 2001/12/31
4 2002/1/1 2002/12/31
(2)支払い金額および入金金額のしたにそれぞれ以下の式を入力します。
入力終了時、Crtrl+Shiftを押しながらEnterキーを押します。
(式全体が{=・・・ }となります)
H2=SUM(IF((F2<=$A$2:$A$100)*(G2>=$A$2:$A$100),$C$2:$C$100,0))
I2=SUM(IF((F2<=$A$2:$A$100)*(G2>=$A$2:$A$100),$D$2:$D$100,0))
(3)H2,I2を下の行にコピーします。
ご回答下さいまして有難うございます。
悩みは無事解決いたしました。御教授下さいました方法も次の機会に生かしたいと思います。ありがとうございました。
No.4
- 回答日時:
No.2 mshr1962さんのSUMPRODUCT関数がお勧めです。
それで、半期とか任意の期間を指定して集計するなら、
=SUMPRODUCT((A2:A6>=DATEVALUE("2000/1/1"))*(A2:A6<=DATEVALUE("2000/6/30"))*(C2:C6))
ですね。
また、別解としてオートフィルターとSUBTOTAL関数の組み合わせでもできます。
1. オートフィルターで例えば2000/1/1から2000/12/31のデータを表示
2. =SUBTOTAL(9,C2:C65536)
ご参考までに。
ご回答下さいまして有難うございます。
SUMPRODUCT関数良いな、と思ったのですが私の使用環境ではもうすこし考えないとダメなようです。
オートフィルターでSUBTOTALも便利ですが今の使用環境に適してはいないのです…すみません。
御教授下さいまして有難うございました。
No.3
- 回答日時:
Excelは最近余り使っていないので自信無いですが、、。
E列に年度を書き出すところまではNO1さんと同じです。
あと、
「データ」-->「集計」で、
「グループの基準」-----年度
「集計の方法」-------合計
「集計するフィールド」---収入、支出
そして
「現在の集計表と置き換える」のチェックをはずして
完了、でどうでしょうか。
元に戻す時は「データ」-->「集計」で、
「すべて削除」ボタンをクリックしますと元の表に。
なお「集計行をデータの下に挿入する」のチェックを
はずしますと、集計はデータの上に表示されます。
私の感覚では見苦しい感じがしますけれども、、、。
ご回答有難うございます。
やはりJasmine002 さんがおっしゃっているとうりビジュアル的に少し気がかりです。会社全体で使うものなので次の機会に生かしたいと思います。御教授下さいまして有難うございました。
No.2
- 回答日時:
SUMIFだと
=SUM(IF((YEAR(A2:A6)=2000)*1,C2:C6))
でShft+Ctrl+Enrerで配列数式にするか
=SUMPRODUCT((YEAR(A2:A6)=2000)*(C2:C6))
入金はC2:C6をD2:D6に変えるだけ...
ご回答下さいまして有難うございます。
SUMIFでの方法がうまく出来なかったのでSUMPRODUCTを試したところ出来ました。ただ、表の中に空白や文字列があると答えが返ってこないので色々工夫したいと思います。
御教授くださいまして有難うございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- 高校 日商簿記3級の勉強中なのですが 精算表が完成せず困っています。 こちらの問題の回答を教えていただきた 2 2023/03/02 09:07
- 所得税 2022総決算 2 2022/12/28 17:15
- 投資・株式の税金 一般口座で同一銘柄の総平均法のことで 1 2023/02/27 22:08
- Excel(エクセル) Excel シート複数 金額日計表と日付 簡単にシートコピーしたら前日の残高と日付を変更させたい 1 2022/07/15 22:10
- 猫 シニア猫の手術保険について 1 2022/09/06 21:10
- 美容費・被服費 支出トータル45% 1 2022/12/28 16:46
- その他(税金) 事業復活支援金の事前確認ってどこまでチェックされるのでしょうか? 1 2022/04/22 11:55
- 確定申告 個人の確定申告。「医療費のお知らせ」記載内容と医療費控除の明細書【内訳書】に記入する内容の関係 4 2023/03/04 18:41
- その他(Microsoft Office) エクセルの関数について 4 2022/07/30 16:42
- その他(学校・勉強) おそらく一般的な数学の質問です、、 ・一括で1000万預けて『30年寝かす』と350万増えて1350 7 2023/08/09 19:08
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【マクロ】エクセルにかいてあ...
-
エクセルの行の抽出について質...
-
Excel初心者です。 詳しい方、...
-
スプレッドシート クエリ関数 1...
-
別シートからの文字を変更
-
Excel初心者です。 詳しい方、...
-
Excel 2019 のピボットテーブル...
-
MOS365 Excel Expert / Excel R...
-
エクセルの数式で教えてください。
-
数学 Tan(θ)-1/Cos(θ)について...
-
4つのパターンを表示するEXACT...
-
各ページの1番上の表示について
-
INDIRECTを使わず excelで複数...
-
関数を教えて下さい。
-
Excelのグラフ軸について
-
Excel:一部のフォントでセルの...
-
エクセルで指定した日付、店舗...
-
Excel ウインドウ枠の固定をす...
-
EXACT関数とIF関数の組み合わせ...
-
写真のコピー
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
ファイル内にある数字の出現回...
-
Excel関数の先頭に「@」が入っ...
-
エクセルの気味悪い不思議
-
Excel VBAで、実行時にsheet上...
-
表示されている人数だけを数え...
-
他人が作ったマクロの理解
-
Excelの関数について質問です。
-
Excel 集計表
-
エクセル 日時の計算式について
-
Excelの関数に関して質問です。...
-
エクセル:セル内の文字列の下...
-
絞り込み検索
-
エクセルの関数で
-
エクセルの書式設定について教...
-
余分なEXCELファイルに印刷され...
-
VBA 同一シート内での転記の仕方
-
長期休みの関数はありますか
-
Excelの空のセル
-
エクセルで入力してある文を別...
-
Excelのマクロで、セルを結合し...
おすすめ情報