[[20151002150327]]　『（IF関数を使った）月別合計の出し方』（ぺもと）　ページの最後に飛ぶ

『（IF関数を使った）月別合計の出し方』（ぺもと）

 お世話になります。
 前回は大変失礼致しました。
 まったく新たな質問をさせていただきます。

 Sheet1～Sheet20
      D       E          F

 1  金額　　件数　　　契約日

 2　100　　　1　　　　10月1日  （←実際は2015/10/1となってます）

 3　…　　　…　　　　…

 --------------------------------------------------------

 Sheet21
      B 　　  C  　　 D　　   E

 3     10月　　　　　　　11月

 4  件数　　金額　　件数　　金額

 ---------------------------------------------------------

 上記のように、Sheet1とSheet2があります。

 Sheet1～Sheet20（店舗ごとに20シート作っています）には、金額と件数と日付を入れる欄があり、
 その日その日で契約があった場合に入力されていきます。
 10月～12月の契約情報を、この1枚のシートに記入していきます。

 Sheet21には、月別で店舗ごとの契約数や金額が一目で分かるような表を作っています。（A5:A25には、それぞれ店舗名が記されています。）

 Sheet21に、関数を入れて、10月だったら10月だけの合計を、11月だったら11月だけの合計を表示させたいです。
 IF関数を使うと思うのですが、そこから先が進まず皆様の知恵をお借りしたいと思いました。

 お忙しいとは思いますがどうぞ宜しくお願い致します。

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

 たとえば、Sheet1店舗の10月の件数合計を出したい場合、

 =SUMIF　や　=FIND　などを上手く使ったらできるのかなと思うのですが、自分ではどうにも上手くできません。

 「Sheet1のF列に、"2015/10"という文字列が一部でも一致すれば、Sheet21のB5に、Sheet1のE列（F列で一致した行の値）の合計を出す」

 という感じです。伝わるでしょうか…。
（ぺもと） 2015/10/02(金) 16:40

こんにちは

Sheet21のB3に「2015/10/1」、D3に「2015/11/1」～と入れて書式で「10月」「11月」～と表示させて
いるとして、

Sheet21のセルB5に

=SUMIF(INDIRECT("Sheet"&ROW(A1)&"!F:F"),"<="&EOMONTH(B$3,0),INDIRECT("Sheet"&ROW(A1)&"!E:E"))-SUMIF(INDIRECT("Sheet"&ROW(A1)&"!F:F"),"<="&EOMONTH(B$3,-1),INDIRECT("Sheet"&ROW(A1)&"!E:E"))

Sheet21のセルC5に

=SUMIF(INDIRECT("Sheet"&ROW(B1)&"!F:F"),"<="&EOMONTH(B$3,0),INDIRECT("Sheet"&ROW(B1)&"!D:D"))-SUMIF(INDIRECT("Sheet"&ROW(B1)&"!F:F"),"<="&EOMONTH(B$3,-1),INDIRECT("Sheet"&ROW(B1)&"!D:D"))

として、下方、右方にフィルコピー

データシート名はSheet1～Sheet20で、 Sheet21のA5:A25にシート名順に集計するとしてます。

（ウッシ） 2015/10/02(金) 16:41

 店舗ごとのシート名って、ホント～に「Sheet1～Sheet20」なの？

 実際は店舗名だとして、つまりSheet21のA5:A24に入力されているのはシート名で、
 Sheet21の「10月」等は文字列だとして

 B4 =SUMPRODUCT(N(TEXT(INDIRECT($A4&"!F2:F100"),"m月;;")=B$3),INDIRECT($A4&"!E2:E100"))

 C4 =SUMPRODUCT(N(TEXT(INDIRECT($A4&"!F2:F100"),"m月;;")=B$3),INDIRECT($A4&"!D2:D100"))

 B4とC4を選択して右へコピー、そのまま下へコピー

 こんな感じかな。
（笑） 2015/10/02(金) 16:49

 ウッシさんありがとうございます。

＞Sheet21のB3に「2015/10/1」、D3に「2015/11/1」～と入れて書式で「10月」「11月」～と表示させて
いるとして、

 ウッシさんのおっしゃるとおりで合ってます。

 私のつたない説明で理解していただきありがとうございます。

 先に説明しておけばよかったのですが、実際のシート名はSheet1とかではなく、店舗名（○○店など）がはいっています。

 "Sheet"の部分をその店舗名に変えたらよいでしょうか？

 また、訂正なのですが、Sheet1～Sheet2のデータは
 D6から金額が入力され、E6から件数が入力され、F6から契約日が入力されます。

 被ってしまいましたが、笑さんコメントありがとうございます。
 ご指摘のとおり、Sheet名は上記です。

 その上で、どのように数式が変わりますでしょうか。お手数をかけて申し訳ありません。
（ぺもと） 2015/10/02(金) 17:02

 ややこしいので・・・まとめると・・・

 入力用シート（Sheet21）に数式を入れます。こちらに関して行列の変更はありません。

 店舗シート（元Sheet1～Sheet20、店舗名がそのままシート名になります）の訂正後の行列は下記です。

      D       E          F

 5  金額　　件数　　　契約日

 6　100　　　1　　　　10月1日  （←実際は2015/10/1となってます）

 7　…　　　…　　　　…

（ぺもと） 2015/10/02(金) 17:10

こんにちは

（A5:A25には、それぞれ店舗名が記されています。）
実際のシート名はSheet1とかではなく、店舗名（○○店など）がはいっています。

という事でしたら、

セルB5が

=SUMIF(INDIRECT($A5&"!F:F"),"<="&EOMONTH(B$3,0),INDIRECT($A5&"!E:E"))-SUMIF(INDIRECT($A5&"!F:F"),"<="&EOMONTH(B$3,-1),INDIRECT($A5&"!E:E"))

C5が
=SUMIF(INDIRECT($A5&"!F:F"),"<="&EOMONTH(B$3,0),INDIRECT($A5&"!D:D"))-SUMIF(INDIRECT($A5&"!F:F"),"<="&EOMONTH(B$3,-1),INDIRECT($A5&"!D:D"))

B5：C5を選択して下方、右方にフィルコピーです。

Sheet21の「10月」等は文字列だとしたら（笑）さんのレスの方がずっとスマートです。

（ウッシ） 2015/10/02(金) 17:12

 Sheet21の「10月」等は、数値ではなく「文字列」ということでオッケー？

 だったら範囲を変えるだけでは？
 試してうまくいかなかったのなら、どうなってしまったのかを具体的に説明しましょう。

 それと訂正です。B4とC4じゃなくて「B5とC5」でした。

 B5 =SUMPRODUCT(N(TEXT(INDIRECT("'"&$A5&"'!F6:F100"),"m月;;")=B$3),INDIRECT("'"&$A5&"'!E6:E100"))

 C5 =SUMPRODUCT(N(TEXT(INDIRECT("'"&$A5&"'!F6:F100"),"m月;;")=B$3),INDIRECT("'"&$A5&"'!D6:D100"))

 B5とC5を選択して右へコピー、そのまま下へコピー

 こんな感じかな。
（笑） 2015/10/02(金) 17:19

 うーん、、、
 なんだかうまくいきません・・・。
 時間が無いので取り急ぎです。何がうまくいかないのかまた記します。

 すみません、伝え忘れましたが、10月は文字列ではありません。

「10/1」と入力すると「10月1日」という表記に成り、セルをクリックすると「2015/10/1」となってます。

 セルの書式設定は「日付」になってます。
（ぺもと） 2015/10/02(金) 17:29

 質問してるのは、Sheet21の3行目にある「10月」とか「11月」のことだけど
 それがシリアル値だってこと？
 ･･･ よく見たら、その通りだと書いてありますね。

 だったら、そこを文字列にできない理由も書いてください。

 シリアル値なら　B$3 → TEXT(B$3,"m月") 　に変更すればいいんだけど、
 まあ、どううまくいかなかったのか、詳しい説明を待つことにしましょうか。

 もしかして店舗ごとの契約日は、2015年以外の10月もあって、それは無視するということかな？
 この質問にも答えてください。

 それと書き忘れてましたが、2回目に回答した数式はセル番地以外にも、
 ちょっと変更してるところがあります。
 INDIRECT のところ。
（笑） 2015/10/02(金) 17:45　いろいろ変更18:15

 いつになるかわからないような返事を待ってるのもアレなんで、、、

 ・店舗別シートの契約日には、2015年以外の日付もある
 ・Sheet21の3行目（「10月」とか）はシリアル値
 ・Sheet21の3行目が「2015/10/1」だったら「2015年の10月」のみ集計。2015年以外のデータは無視

 ということなら。

 B5 =SUMPRODUCT(N(TEXT(INDIRECT("'"&$A5&"'!F6:F100"),"yyyymm")=TEXT(B$3,"yyyymm")),INDIRECT("'"&$A5&"'!E6:E100"))

 C5 =SUMPRODUCT(N(TEXT(INDIRECT("'"&$A5&"'!F6:F100"),"yyyymm")=TEXT(B$3,"yyyymm")),INDIRECT("'"&$A5&"'!D6:D100"))

 B5とC5を選択して右と下にコピー
　
　
 または、Sheet21の3行目が必ず「月初の日付」なら

 B5 =SUMIFS(INDIRECT("'"&$A5&"'!E6:E100"),INDIRECT("'"&$A5&"'!F6:F100"),">="&B$3,INDIRECT("'"&$A5&"'!F6:F100"),"<"&EDATE(B$3,1))

 C5 =SUMIFS(INDIRECT("'"&$A5&"'!D6:D100"),INDIRECT("'"&$A5&"'!F6:F100"),">="&B$3,INDIRECT("'"&$A5&"'!F6:F100"),"<"&EDATE(B$3,1))

 B5とC5を選択して右と下にコピー

 SUMIF ではなく SUMIFS
 Sheet21のB3（B3とC3の結合だよね？）には「月初の日付」を入力
 D3（D3とE3の結合）に　=EDATE(B3,1)　これを右にコピーしておくとよい。

 INDIRECT("'"&$A5&"'!F6:F100")
 　　　　　↑ 　　 ↑
 シングル・クォーテーションを外してもエラーにならなければ、付けなくてもかまわない。
 INDIRECT($A5&"!F6:F100")　　INDIRECT のところすべて

 これでもダメなら詳細レポートよろ！
（笑） 2015/10/02(金) 23:44　10/04(日) 20:49 加筆

 笑さん、お返事が遅くなり申し訳ありません。

 今朝試してみました。
 sumproductも、sumifsもどちらもエラーなくできました！
 ちなみにシングル・クォーテーションは無くても大丈夫でした。
 本当にありがとうございます。

 大変お世話になりました。またよろしくお願いいたします。
（ぺもと） 2015/10/05(月) 12:01

コメント返信：

[ 一覧(最新更新順) ]

[[20151002150327]] 『（IF関数を使った）月別合計の出し方』（ぺもと） ページの最後に飛ぶ

[[20151002150327]]　『（IF関数を使った）月別合計の出し方』（ぺもと）　ページの最後に飛ぶ