指定した年月のデータを合計

質問

年月日(Ａ列)　　 製品（B列）　　　金額（C列）

2012/2/1　　 　　　A　　　　　　　　10

2013/5/25　　　　 B　　　　　　 　 20

2012/3/2 　　　　　C 　　　　　　　 30

2012/3/16 　　　　　C 　　　　　　　 40

（セルE1）に2012とか2013/5とか手動で入力すると（セルＥ２）に合計額が出るようにしたいです

2012→80
2013→20
2013/5→20
2012/3→70

セルＥ１に入れるべき関数をお教えくださいませ。

回答No.8

MarcoRossiItaly · ベストアンサー

同一セルに「2012」と「2013/5」の 2 パターンで入力したいとのことなのですが、1 つのセルに年・月という属性の異なるデータを入れたり入れなかったり、というのでは、難しくなってしまいます。不可能ということはないはずですが。

それよりも、お勧めなのはやはり、年と月のセルを分けることです。年月の指定の仕方がシンプルで、分かりやすいシステムになります。

★月のセルを未入力にすれば、自動的に 1 年間での合計が算出されるような数式にしておきます。

E1　2012　　など
G1　3　　　 など、あるいは未入力
E5　=sumifs(c:c,a:a,">"&date(e1,1+max(,g1-1),0),a:a,"<"&date(e1+(g1=0),g1+1,1))
　　　　　　…… Excel2007 以後
　　あるいは
　　=sumif(a:a,">"&date(e1,1+max(,g1-1),0),c:c)-sumif(a:a,">"&date(e1+(g1=0),g1+1,0),c:c)
　　……全バージョン

A・C 列の「表外の」行に関係ない数値を入力すると、数式が誤った結果を算出する可能性があります。その場合は、「a:a」→「a2:a1000」、「c:c」→「c2:c1000」といった具合に、行番号で合計する範囲を指定する形にセル参照を書き換えます。

E5 の 2 式をよく見比べると、不等号の向きと、2 つ目の DATE 関数の第 3 引数が微妙に異なっていますので、ご注意。

年月のセルの書式は、上式の場合、「文字列」も含め、何に設定しても構いません。

なお「年のみを入力した場合の合計」と「年月を入力した場合の合計」を、別のセルで求めることにしても構わないという場合は、No.4 さんのように、更に易しい数式で計算することができます。

お礼

図つきで、とても分かりやすかったです。
重宝しそうです。
ありがとうございました。

回答No.9

keithin

＞その辺の適切な回答もしていただけるかなと甘えていました・・

文字列の書式にしておくことで、２０１２でも２０１３／５でも両方その通りに記入できます、とお話ししてありますが？

＞えくせる２０１０

たとえばＥ２には
=SUMIFS(C:C,A:A,">="&(EDATE(E1&"/1",0)),A:A,"<"&EDATE(E1&"/1",IF(E1-2100<0,12,1)))
などのように入れておきます。

お礼

関数の中の2100の意味だけ教えていただけませんか？

補足

書き方が悪かったです。
最初の質問で甘えていました。
だけども、そのように回答していただいたので大変ありがたいということでした。

いろんなやり方があることにびっくりしています。
皆様には感謝です。

回答No.7

KURUMITO

次のような関数をE2セルに入力することでE1セルが文字列の入力であるか標準のままの入力であるかにかかわらず合計額が表示されます。

=IF(YEAR(E1)<1920,SUMPRODUCT((YEAR(A1:A10)=E1*1)*(C1:C10)),SUMPRODUCT((YEAR(A1:A10)=YEAR(E1))*(MONTH(A1:A10)=MONTH(E1))*(C1:C10)))

上式ではYEAR(E1)<1920となっていますがこれは例えば2012と入力された場合にはシリアル値で2012であり、1905/7/4と日付の表示ではなります。したがって2027年以前のデータまでは対応できます。また、A列に1920年以前の日付が有る場合には対応できません。

回答No.6

kagakusuki

もし、E1セルの書式設定が文字列となっている場合には、次の様な関数となります。

=IF(E1="","",IF(ISNUMBER((E1&"年"&"1月")+0),SUMIF($A:$A,"<"&DATE(E1+1,1,1),$C:$C)-SUMIF($A:$A,"<"&(E1&"/1/1")+0,$C:$C),IF(ISNUMBER((E1&"/28")+0),SUMIF($A:$A,"<"&DATE(YEAR(E1+0),MONTH(E1+0)+1,1),$C:$C)-SUMIF($A:$A,"<"&(E1&"/1")+0,$C:$C),IF(ISNUMBER(FIND("/",E1)+(E1&" 0:0:1")),SUMIF($A:$A,"="&E1+0,$C:$C),"検索値が正しくありません"))))

回答No.5

kagakusuki

「2012とか2013/5とか手動で入力する」セルがE1セルで、合計額を表示するセルがE2セルなのですね？
　その場合、E2セルに入力する関数を次の様なものにされると宜しいかと思います。

=IF(E1="","",IF(ISNUMBER(MATCH(CELL("format",E1),{"D1","D2","G"},0)/DAY(E1)/($E1=ABS(INT($E1)))),CHOOSE(MATCH(CELL("format",E1),{"D1","D2","G"},0),SUMIF($A:$A,E1,$C:$C),SUMIF($A:$A,"<"&DATE(YEAR(E1),MONTH(E1)+1,1),$C:$C)-SUMIF($A:$A,"<"&(TEXT(E1,"yyyy/m")+0),$C:$C),SUMIF($A:$A,"<"&DATE(E1+1,1,1),$C:$C)-SUMIF($A:$A,"<"&(E1&"/1/1")+0,$C:$C)),"検索値が正しくありません"))

　尚、上記の関数は、E1セルの書式が、年のみを表示する様になっているのか、年と月のみを表示する様になっているのか、或いは、年月日を表示する様になっているのかによって、どの様な期間を集計対象にするのかを決めていますので、E1セルの書式設定を文字列にしてしまうと、上記の関数は正常に動作出来なくなります。
　それから、2013/5の様に、年/月の形式でセルに入力しますと、May-13と表示されますが、そのままでも、2013/5の月の合計額を表示する事が出来ます。
　因みに、セルの書式設定を[ユーザー定義]のyyyy/mにしますと、2013/5の形式で表示されます。

回答No.4

noname#204879

添付図参照
年だけのときは、セル E2 に４桁の西暦年を入力します。
年月の場合は、yyyy/m という書式を設定したセル E4 に 2012/3 のように入力（この場合は、2013/3/1 のように、当該年月の１日付けの年月日が入力されることに注）します。
G2: =YEAR(A2)=E$2
F2: =DSUM(A1:C5,3,G1:G2)
G4: =DATE(YEAR(A2),MONTH(A2),1)=E$4
F4: =DSUM(A1:C5,3,G3:G4)

回答No.3

K Kazz（@JazzCorp）

ムリヤリやるとこうなる、、、
Ｆ列：年（A列データ）
Ｆ2：
=IF($A2="","",YEAR($A2))
Ｇ列：年月(月初日：A列データ）
Ｇ２：
=IF($A2="","",YEAR($A2)&"/"&MONTH($A2)&"/"&1)
Ｆ２、Ｇ２、まとめて下にコピー
Ｅ１：集計のキー、年月または年（書式：YYYY/m）
年指定の場合は、文字列として入力（頭に '）
Ｅ２：
=SUMIF($F2:$F99,$E$1,$C2:$C99)+SUMIF($G2:$G99,$E$1,$C2:$C99)

回答No.2

keithin

＞（セルE1）に2012とか2013/5とか手動で入力する

ふつーのエクセルで試しにそれをやってみると、すっかり意図と違う結果になって慌てる羽目になります。
まず手を動かして、実際にＥ１セルに２０１２とか2013/5とか、また２０１３とか記入してください。

あなたが思ってたように使うため、Ｅ１セルの書式設定の表示形式を必ず「文字列」の設定にしておきます。

さて本番。
エクセル２００７以降ではもっと綺麗な関数が使えますが、あなたが普段使うエクセルのバージョンも書いてないので、全てのエクセルで使えるもっと重たい数式にします。

たとえば
=IF(E1="","",SUMPRODUCT((TEXT(A1:A1000,"yyyy"&IF(E1-2100<0,"","/m"))=E1)*1,C1:C1000))

補足

すいません。
えくせる2010です。
たしかに、2012/5だと大丈夫ですが、2012だと文字列にしておかないとだめですよね。

その辺の適切な回答もしていただけるかなと甘えていました・・

回答No.1

MackyNo1

2012と年だけを入力した場合は以下の式になります。
=SUMPRODUCT((YEAR($A$2:$A$10)=E2)*$C$2:$C$10)

2013/5と年月を入力した場合は以下の式になります。
=SUMPRODUCT((YEAR($A$2:$A$10)=YEAR(E2))*(MONTH($A$2:$A$10)=MONTH(E2))*$C$2:$C$10)

後者の場合は、セルの書式設定で表示形式を「ユーザー定義」にして「yyyy/m」と入力して、そのセルに「2013/5」のように入力してください。