エクセルで 指定する期間内の数値を合計する関数はありますか?
自分に合う家計簿をエクセルで自作してるのですが、カード払いの集計でつまづいています。
Aカード10/15~11/14・Bカード10/11~11/10(締め日)までの期間に使用した
カードの合計を別シートのセルに表示したいのです。
たとえばですが、11月の家計簿シートで
11/ 1 Bカード 1000円
11/ 9 Aカード 1000円
11/10 Aカード 1000円
11/16 Aカード 1000円
を使用したとして、
Aカード 2000円(12月支払い額のセルに金額表示)
Aカード 1000円(1月支払い額のセルに金額表示)
Bカード 1000円(12月支払い額のセルに金額表示)
と金額だけを表示させたいと思います。
可能でしょうか?
または似たような表示方法があれば伝授お願いします。
結局は、11月に使用した金額を何月にいくら支払うのかを把握できるようにしたいということです。
説明が下手で申し訳ないのですが アドバイスいただけたら助かります。
よろしくお願いします。
No.6ベストアンサー
- 回答日時:
こんばんは!
色々方法はあるかと思いますが・・・
一案です。
↓の画像のような配置にしてみました。
A列は通常の日付でシリアル値・E列は各月の1日のシリアル値にして表示形式をユーザー定義から
yyyy/m としています。
そして、各カードの各月の始まりから締日をI~K列のような表にしています。
F2セルに
=SUMPRODUCT(($A$2:$A$1000>=DATE(YEAR($E2),MONTH($E2)-1,VLOOKUP(F$1,$I$2:$K$3,2,0)))*($A$2:$A$1000<=DATE(YEAR($E2),MONTH($E2),VLOOKUP(F$1,$I$2:$K$3,3,0)))*($B$2:$B$1000=F$1)*($C$2:$C$1000))
という数式を入れ、列方向と行方向にオートフィルでコピーすると
画像のような感じになります。
尚、元データの1000行目まで対応できる数式にしています。
尚、「0」が目障りであれば当方使用のExcel2003の場合は
メニュー → ツール → オプション → 表示タブで「ゼロ値」のチェックを外せば
「0」は表示されなくなります。
あくまで一つの案ですので
他に良い方法があれば読み流してくださいね。m(__)m
No.7
- 回答日時:
エクセルのバージョンを書いてない質問だが、エクセルを取り巻く情況のことを良く知らないようだ。
2007からSUMIFSと言う関数が増えている。シートの操作も大幅に変わっている。だから書かないといけない。
ーー
質問をあれこれ書いているが、結局
カード会社名、開始日、終了日の3条件の加算ではないのか。
カード会社名から開始日、締切日も割り出せないこともないかもしれないが、複雑に砂利過ぎるので
操作者が毎月入れる。
2007ではSUIFSで出来ると思う。
2003までは3条件(2条件も)をかける集計は、SUMPRODUCT関数を使うのが定石。
このコーナーにも沢山の質問があった。
Googleででも、SUMIFS、SUMPRODUCT関数で照会して、勉強してください。
エクセルの関数の普通の疑問は、Googleで勉強材料は揃います。
No.5
- 回答日時:
今仮に、家計簿シートの名称が
家計簿シート
で、そのA列に日付のデータ、
B列にカード等の使用目的、
C列に金額
が既に入力されていて、
Sheet2に月々の各カード使用料の集計結果を表示させるものとします。
まず、Sheet2の
A1セルに カードの種類
B1セルに 〆日
D1セルに 2010年2月
と入力して下さい。
(D1セルに入力する値は、年と月のみの形式であれば、いつの年月であっても構いません)
次に、Sheet2のD2セルに入力した日付データを一旦削除してから、次の数式を入力して下さい。
=IF(ISNUMBER($C1),DATE(YEAR(C$1),MONTH(C$1)+1,1),"")
次に、Sheet2のD1セルをコピーして、Sheet2のE1~N1の範囲に貼り付けて下さい。
次に、Sheet2のC2セルに次の数式を入力して下さい。
=IF(OR($A2="",$B2="",ISNUMBER(C$1)=FALSE),"",SUMPRODUCT((OFFSET(家計簿シート!$A$1,,,MATCH(9^9,家計簿シート!$A:$A))>=IF($B2="末日",C$1,DATE(YEAR(C$1),MONTH(C$1)-1,$B2)+1))*(OFFSET(家計簿シート!$A$1,,,MATCH(9^9,家計簿シート!$A:$A))<=IF($B2="末日",DATE(YEAR(C$1),MONTH(C$1)+1,1)-1,C$1+B$2-1))*(OFFSET(家計簿シート!$B$1,,,MATCH(9^9,家計簿シート!$A:$A))=$A2)*OFFSET(家計簿シート!$C$1,,,MATCH(9^9,家計簿シート!$A:$A))))
次に、Sheet2のC2セルをコピーして、Sheet2のD2~N2の範囲に貼り付けて下さい。
次に、Sheet2のC2~N2の範囲をコピーして、同じ列の3行目以下に、リストで管理するカードの枚数を、リストの行数が上回る様になるまで、複数行に渡って貼り付けて下さい。
ここまででフォーマットは完成です。
後は、Sheet2のA2セルから下方に向かって、カードの名称を入力して行って下さい。
次に、各カードの〆日を、Sheet2のB2セルから下方に向かって入力して行って下さい。
この時、例えば毎月14日が〆日の場合は、
14
とのみ入力して下さい。
又、〆日が月末のものに関しては、
末日
と入力して下さい。
最後に、Sheet2のC1セルに、集計を始める最初の月を、
2010年1月
という具合に、年と月を合わせた形式で入力して下さい。
すると、家計簿シートに入力されているデータを基に、月々の各カード使用料の集計結果が表示されます。(無論、家計簿シートに後から追加したデータも、自動的に集計されます)
No.4
- 回答日時:
またまたミスです(今日は他にもケアレスミスが多く、自分でも不思議ですが、もしかすると厄日なのかもしれません)。
質問をよく見たら、カードごとに日付の〆日が違うのですね。
私の提示した数式は前月の11日から今月の10日までのBカードのパターンの数式を提示していましたので、Aカードの列は15から開始し14日で終わるように適宜数字を変更してください。
No.3
- 回答日時:
No2の回答ですが、実際の添付画像と数式範囲が違っていました。
画像のデータは12行目まであるのに、提示した数式は10行目までしか集計できない式になっていましたので、セル範囲を指定する10の数字を適宜100などの実際のデータよりも少し大きめの数字に設定してください。
No.2
- 回答日時:
日付が先月11日から今月10日までの範囲の日付のセルをカードごとに集計したいなら以下のような関数を利用します(添付画像のF2セル)。
=SUMPRODUCT(($A$2:$A$10<=DATE(YEAR($E2),MONTH($E2)-1,10))*($A$2:$A$10>=DATE(YEAR($E2),MONTH($E2)-2,11))*($B$2:$B$10=F$1)*$C$2:$C$10)
ただし、金額の欄は「円」を付けずに数字で入力し(円の表示が必要なら表示形式で「0"円"」などと設定)、集計する項目の「年月」を表示するセルは「2010/11」のように入力し、セルの書式設定でユーザ定義にして「yyyy年m月」などと設定しておき下方向にオートフィルドラッグします(連続データが月単位で入力できないときは右クリックでオートフィルをしてください)。
No.1
- 回答日時:
一例です。
仮にご例示の11月シートでデータ範囲がA1:C4、A列セルの表示形式は日付、C列セルの表示形式は数値(0円)とします。
Aカードの12月支払分は、以下のような数式になります。
■エクセル2003以下
=SUMPURODUCT(($A$1:$A$4="10/15"*1)*($A$1:$A$4="11/14"*1)*($B$1:$B$4="Aカード")*($C$1:$C$4))
■エクセル2007以上
=SUMIFS(C:C,A:A,">="&"10/15"*1,A:A,"<="&"11/14"*1,B:B,"Aカード")
因みに数式に日付等を固定にすると数式が長くなり汎用的ではないので別セルに設定した方が良い。
別シートにカード別決済表を1月から12月まで作った方が良いかもしれません。
=SUMPRODUCT(($A$1:$A$4>=$E$1)*($A$1:$A$4<=$E$2)*($B$1:$B$4=$E$3)*($C$1:$C$4))=SUMIFS(C:C,A:A,">="&$E$1,A:A,"<="&$E$2,B:B,$E$3)
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- 電子マネー・電子決済 電子版Suicaについて教えてください。iphoneです。普段パスコードを利用しておらず、使う時だけ 1 2022/10/02 22:24
- クレジットカード カードのポイントについての質問です。 年会費無料で1000円1Pのカードを、年会費11000円で10 3 2023/06/11 23:26
- カードローン・キャッシング 楽天カードですが、 支払額60万円と利用可能額20万円と表示されており、その合計が80万円で、利用可 2 2023/02/28 06:08
- クレジットカード 楽天カードのポイント付与は利用ごとか月額合計に対して計算をするのかどっちでしょうか? 4 2022/11/30 15:48
- メルカリ メルカリの支払い方法について 2000円の商品を購入しようと思っていますがポイントを1000円使い残 3 2023/07/12 13:24
- Excel(エクセル) 図書カードの分配 7 2023/05/09 15:57
- クレジットカード クレジットカードの利用可能額について 2 2022/04/08 23:13
- クレジットカード あとからリボ。 4 2022/04/25 18:15
- 格安スマホ・SIMフリースマホ 今 ahamoを使っていて 4898円以下になる携帯会社があれば乗り換えたいです 20GBと24時間 9 2022/09/27 07:43
- Excel(エクセル) 10円の誤差が分からない 11 2022/11/13 07:25
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【関数】スペースがいくつ入っ...
-
西暦や和暦の表示をyyyymmdd表...
-
Excelのセルを飛ばして入力する
-
Excelのオートフィル
-
別シートからの文字を変更
-
Excel 2019 のピボットテーブル...
-
エクセルの行の抽出について質...
-
【マクロ】エクセルにかいてあ...
-
スプレッドシート クエリ関数 1...
-
エクセルでセルに「氏名を入力...
-
MOS365 Excel Expert / Excel R...
-
excelの不要な行の削除ができな...
-
EXACT関数とIF関数の組み合わせ...
-
スプレッドシートの関数VLOOKUP...
-
Excelで全角を半角にしたいので...
-
Excel初心者です。 詳しい方、...
-
エクセルの数式で教えてください。
-
4つのパターンを表示するEXACT...
-
スマートな関数を教えて下さい。
-
【Excel】セル内の時間帯が特定...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
ファイル内にある数字の出現回...
-
Excel関数の先頭に「@」が入っ...
-
エクセルの気味悪い不思議
-
Excel VBAで、実行時にsheet上...
-
表示されている人数だけを数え...
-
他人が作ったマクロの理解
-
Excelの関数について質問です。
-
Excel 集計表
-
エクセル 日時の計算式について
-
Excelの関数に関して質問です。...
-
エクセル:セル内の文字列の下...
-
絞り込み検索
-
エクセルの関数で
-
エクセルの書式設定について教...
-
余分なEXCELファイルに印刷され...
-
VBA 同一シート内での転記の仕方
-
長期休みの関数はありますか
-
Excelの空のセル
-
エクセルで入力してある文を別...
-
Excelのマクロで、セルを結合し...
おすすめ情報