[[20170228123157]]　『sumifの条件に関数』（ぽんとりん）　ページの最後に飛ぶ

『sumifの条件に関数』（ぽんとりん）

「予定」と「月別数量」というセルがあります。
・予定シート
Ａ列：=month()関数でＢ列から月を出しています
Ｂ列：日にち（セルの書式設定のユーザ設定定義でd"日"としています）
Ｅ列：その日の数量
・数量シート
Ａ列：月
Ｂ列：=SUMIF(予定!$A:$A,月別数量!$A4,予定!E:E)
となっています。

予定シートのＡ列にＢ列の月を表示していましたがその必要がなくなりました。数量シートのＢ列に月ごとの数量の合計を出すためのみに予定シートのＡ列が存在しています。予定シートのＡ列を削除し、Ｂ列の日にち情報を使い、直接数量の合計が出るようにできないものでしょうか。
sumif関数の条件には簡単な数式は使えるようですがmonthのような関数は使えないようです。難しいようであれば予定シートのＡ列を非表示にするしかないかと考えています。

＜使用 Excel：Excel2013、使用 OS：Windows7 ＞

 >sumif関数の条件には簡単な数式は使えるようですがmonthのような関数は使えないようです。
 これは単に使い方を間違えているためだろう。

 ところで日にちデータには年のデータも含まれている。
 その年のデータはどこから持ってくればいいのか？
（ねむねむ） 2017/02/28(火) 13:08

 年を考慮しないでいいのであれば

 =SUMPRODUCT((MONTH(予定シート!B:B)=A1)*予定シート!E:E)
 でも。
 (予定シートのBれつが日付でE列が数量の場合）
（ねむねむ） 2017/02/28(火) 13:16

ねむねむ様
ありがとうございます。

日にちデータはC1セルに西暦を入力して取得しています。
B3：=DATE(C1,4,1)
B4:=B3+1
B5:=B4+1
このようにしています。

SUMPRODUCT初めて見る関数です。
=SUMPRODUCT((MONTH(予定シート!B:B)=A1)*予定シート!E:E)
で確認してみます。

（ぽんとりん） 2017/02/28(火) 13:35

 >=SUMIF(予定!$A:$A,月別数量!$A4,予定!E:E)

 月別数量シートのA列で月を指定しているみたいですけど、そこはどのようなデータなんでしょうか？
（コナミ） 2017/02/28(火) 13:40

 これまでのやり方が、A2セルに =MONTH(B2) と入れてたってことなら
 B列が空白セルだったら「1」になりますよね？

 つまりB列が空白なのにE列に数値が入っていると、それは「1月」として加算されてしまうけど、
 そんな可能性はないんですか？

 特定年の月別に合計すればいいのなら、
 数量シートのA4セルに 2017/3/1 とか 2017/4/1 のように「月初の日付」を入力しておいて、

 =SUMIFS(予定!E:E,予定!B:B,">="&A4,予定!B:B,"<"&EDATE(A4,1))

 でも。

 ピボットテーブルでもできますけどね。
（笑） 2017/02/28(火) 14:06

 私は月別数量のA列は普通に日付データで、表示が「m」になっているとふんでいるのですが(^_^;)
（コナミ） 2017/02/28(火) 14:21

 特定年限定でなくても、
 数量シートのA4セルに「3月」とか「4月」と入力しておいて、

 =SUMPRODUCT(N(TEXT(予定!$B$2:$B$500,"m月;;")=A4),予定!$E$2:$E$500)

 とすると、B列が空白でE列に数値が入っていても「1月」には加算されません。

 参考まで。

 ＞私は月別数量のA列は普通に日付データで、表示が「m」になっているとふんでいるのですが(^_^;)

 作業列を使えば提示のSUMIFでできてるってことは「1～12」の数値でしょう。
（笑） 2017/02/28(火) 14:31

ねむねむ様
＃value！
となってしまいます。

コナミ様
ありがとうございます。
A列には数字で1～12が入っています。

笑様
ありがとうございます。
B列が空白でE列に数値が入ることはありません。
大丈夫です！
=SUMIFS(予定!E:E,予定!B:B,">="&A4,予定!B:B,"<"&EDATE(A4,1))
試してみます。

（ぽんとりん） 2017/02/28(火) 14:35

 月別数量シートと数量シートは一緒ってことかー。
（コナミ） 2017/02/28(火) 14:41

 もしかして予定シートの１行目が見出しなどでE列に文字があるということは無いか？
 その場合は
 =SUMPRODUCT((MONTH(予定シート!B$2:B$1000)=A1)*予定シート!E$2:E$1000)
 のように文字の入っていない範囲を指定してみてくれ。
（ねむねむ） 2017/02/28(火) 14:42

ねむねむ様
ありがとうございます。
おっしゃる通りです。範囲指定でできました。
SUMPRODUCTの使い方も少し分かりました！

笑様
ありがとうございます。
SUMIFSおよびEDATEの使い方分かりました！

=SUMPRODUCT(N(TEXT(予定!$B$2:$B$500,"m月;;")=A4),予定!$E$2:$E$500)
については？？？のままです。

ご回答いただいた皆様本当にありがとうございます。
これで先に進むことができます！

（ぽんとりん） 2017/02/28(火) 19:08

 ＞=SUMPRODUCT(N(TEXT(予定!$B$2:$B$500,"m月;;")=A4),予定!$E$2:$E$500) 
 ＞については？？？のままです。

 と言われても、こっちが？？？です。
 できなかったということ？

 A4セルには「4月」と入力するんですよ。
「4」とだけ入力して表示形式で「4月」にしてもダメですよ。

 A5から下を「5月」「6月」･･･にしたいんだったら、A4をオートフィルするだけ。

 今回のケースでは関係ないようですけど、
 予定シートのB列が空白の場合にE列に数値が入っていても集計に影響しない、ということの他に、
 B列とE列に見出し以外にも文字列（数式の「""」を含む）があっても#VALUE!エラーにはならない、
 といったテクニックが惜しげもなく盛り込まれていますｗ

 ■ついでに書いておくと、SUMIFS関数の場合
 予定シートのC1セルに西暦年を入れてるんだったら、

 A4 =DATE(予定!C1,4,1)
 A5 =EDATE(A4,1)　　A5をA15まで下にコピー

 これで年度が変わっても、数量シートは何も変更する必要がありませんね。
 A4からA15に12か月分を一覧表示する場合ですけど。

 参考まで。
（笑） 2017/03/01(水) 17:08

 慎重を期して

 =SUMPRODUCT(N(TEXT(予定!$B$3:$B$500,"m月;;;")=A4),予定!$E$3:$E$500)
                                         ~~~
 の方がいいですね。
　
「;;」→「;;;」　2つを3つに。

 参考まで。
（笑） 2017/03/01(水) 18:24

コメント返信：

[ 一覧(最新更新順) ]

[[20170228123157]] 『sumifの条件に関数』（ぽんとりん） ページの最後に飛ぶ

[[20170228123157]]　『sumifの条件に関数』（ぽんとりん）　ページの最後に飛ぶ