【Excel】SUMIFSで期間内の条件について

質問

任意の期間内のデータを抽出して合計する場合以下の数式とします。
=SUMIFS(E:E,A:A,">=2019/1/1",A:A,"<=2019/1/27",B:B,B2)
12月まであるのでコピーをしたいのですが、このような場合は手動で月日を入れ替えないとならないですよね？つまりコピペは不可か？ということです。

また、2019年は「うるう年」ではない年ですが、アバウトに2月29日までと入力した場合、正確な結果はでないのでしょうか？
また31日がない月も同様でしょうか？

回答No.8

masnoske

月末は翌月１日-1 で求められますよ。
2020/2/29 = 2020/3/1 - 1

回答No.7

miso_kasu

>次のセル（G4移行）から1ヶ月後になるようにすると指示している…のだと考えますが、
違います。
H3セルの数式ではG4セルを参照していません。
DATE関数はDATE($G$2,$G3,1)の部分だけです。
$G$2は絶対番地で2019という数値が入力されています。
$G3は列記号が絶体番地で行番号は相対番地で1という数値が入力されています。
DATE($G$2,$G3,1)　→　DATE(2019,1,1)　→　2019/1/1

>「>=」（以上）は何についてどう指示しているのでしょうか？
「$A:$A,">="&DATE($G$2,$G3,1)」の「>=」はA列全体の中で日付の2019/1/1以上の日付に対してTRUEを返します。（数式全体の中で比較演算している）
A列の日付で2019/1/1未満はFALSEが返されます。
SUMIFS関数では複数の条件に適う値を集計するので比較演算が理解できないと目的の結果を得られないでしょう。
また、他の関数でも数式内で比較演算することもありますので数式の先頭に書く「=」とは意味合いが異なります。
因みに、数式の先頭に書く「=」は「セル番地=数式」であり、数式バーの左外側にセル番地が表示されていますので、「そのセル番地の数式ですよ」という意味になります。

お礼

比較演算ですね。またひとつ勉強になりました。
わかりやすい説明で理解が深まりました。ありがとうございました。

回答No.6

noname#255669

この質問者はかわいそうな人だったんですね。同じような質問を何度も繰り返して理解力がない、そんな人に「やればわかる」などと失礼な回答をしてしまいました。我慢強い回答者にしか相手ができない人でした。失礼失礼。

お礼

あなたのように最初からすべて理解できるような賢い方からすれば「やればわかる」とすべての質問者に対し簡単に言えるのだと思いますね。羨ましい。

それに他の回答者様に「我慢強い回答者」というのは失礼ですよ。賢い上に親切なのです。誰かさんとは大違い。

質問の意味が理解できない上に回答の代わりに嫌味を言う時間がもったいない。
とスピノザも言うのではないですか(笑)

回答No.5

miso_kasu

>とのことですが表示形式を"0年"、"0月"と設定しましたが、
>0"年"
>0"月"
>に変わってしまいますが…。
表示形式はそれで良いです。

>これもエラーと関係あるのでしょうか？
「#VALUE！」は文字列を数値と間違えて数式に与えたとき起こるエラーです。
G2セルに「2019年」と文字列で入力していませんか？
G3～G14へ「1月、2月、・・・11月、12月」のように文字列で入力されていると思います。
セルの表示形式は数値の場合にどのように表示するかを定義します。
文字列の数字と数値は扱い方が異なりますので間違わないようにしてください。

補足

もう一度調べてみました。文字列にはなってなかったのですが、すでに「2019年」と入力したものを後からユーザー定義に変えたのがいけなかったようで、すべて設定、入力しなおしたところ成功しました。

最後にこの式の関数の読み方について教えてください。

条件1
">="&DATE($G$2,$G3,1)
はDATE関数ですよね？

『DATE関数は、各セルにバラバラに収められた年・月・日の情報をドッキングして、1つのセルにまとめてくれる』とのことですが、この式のでは「日付」列を対象として、G2（年）とG3（月）を一緒に考え、次のセル（G4移行）から1ヶ月後になるようにすると指示している…のだと考えますが、「>=」（以上）は何についてどう指示しているのでしょうか？

回答No.4

miso_kasu

回答No.3の数式に誤りがありましたので訂正します。
=SUMIFS($E:$E,$A:$A,">="&DATE($G$2,$G3,1),$A:$A,"<"&EOMONTH(DATE($G$2,$G3,1),0),$B:$B,H$2)
　　　　　　↓
=SUMIFS($E:$E,$A:$A,">="&DATE($G$2,$G3,1),$A:$A,"<="&EOMONTH(DATE($G$2,$G3,1),0),$B:$B,H$2)

EOMONTH関数の戻り値は月末なのでA列の日付との比較は未満ではなく以下になります。（"<"は未満、"<="は以下）

補足

ご回答ありがとうございます。

さすがmiso_kasuさんですね。作成された表のとおりが目的です。

ですが、H3に訂正された式を入れ、すべてをコピーしたのですが、数値は0、関数の条件１（">="&DATE($G$2,$G3,1)）と２（"<="&EOMONTH(DATE($G$2,$G3,1),0)）が「VALUE！」となってしまいます。

＞G2には2019という数値を入力し、表示形式を"0年"にしました。
＞G3～G14に1～12と月数を数値で入力し、表示形式を"0月"にしました。

とのことですが表示形式を"0年"、"0月"と設定しましたが、
0"年"
0"月"
に変わってしまいますが…。これもエラーと関係あるのでしょうか？

回答No.3

miso_kasu

>任意の期間内のデータを抽出して合計する場合・・・・中略・・・・つまりコピペは不可か？ということです。
文字列で定数となる値はコピペで変化しませんので手作業で部分修正してください。
また、A列の比較は日付の範囲のようですから「">=2019/1/1"」は文字列の比較となっていますので再考が必要になります。
提示の数式ではB列に商品名等が入力されていると推測しますが比較値はB列の特定のセルではないようにしないと集計表として纏められません。

>また、2019年は「うるう年」ではない年ですが、アバウトに2月29日までと入力した場合、正確な結果はでないのでしょうか？
月初めから月末までを例に添付画像のような集計表を作成してみました。
H3セルの数式は下記のようになります。
=SUMIFS($E:$E,$A:$A,">="&DATE($G$2,$G3,1),$A:$A,"<"&EOMONTH(DATE($G$2,$G3,1),0),$B:$B,H$2)
H3セルを右と下へ必要数コピペしました。
G2には2019という数値を入力し、表示形式を"0年"にしました。
G3～G14に1～12と月数を数値で入力し、表示形式を"0月"にしました。
目的に合った数表を作成して、数表に合せて数式を設定すれば省力化できます。

回答No.2

noname#255669

> 12月まであるのでコピーをしたいのですが、このような場合は手動で月日を入れ替えないとならないですよね？つまりコピペは不可か？ということです。

どこからどこへコピペするのか分からないのに不可とも可とも。

> また、2019年は「うるう年」ではない年ですが、アバウトに2月29日までと入力した場合、正確な結果はでないのでしょうか？

やってみればサルでもわかる。スピノザはそう言わなかったか(笑)

回答No.1

f272

例えばF列に1,2,3,...,11,12と月数を入れておけば2月1日以上3月1日未満は
=SUMIFS(E:E,A:A,">="&DATE(2019,F2,1),A:A,"<"&DATE(2019,F2+1,1),B:B,B2)

補足

ご回答ありがとうございます。
F列はすでに他の項目が入ってしまっているし、見栄えのこともあるので、できれば表に関係ない項目を作りたくありません。
EOMONTH関数というのがあって、SUMIFSと組み合わせできるそうで、今調べてますがなかなか難しいです。