[[20150725151958]]　『フィルの方法について』（noboru）　ページの最後に飛ぶ

『フィルの方法について』（noboru）

以下データをC6以降にフィルしたいのですが
C5=DSUM(月別集計,"契約金額",$U31:$V32)

単純に下方向にフィルすると
　=DSUM(月別集計,"契約金額",$U32:$V33)
　=DSUM(月別集計,"契約金額",$U33:$V34)
と1づつ数値がアップしますが
やりたいことは
　=DSUM(月別集計,"契約金額",$U33:$V34)
　=DSUM(月別集計,"契約金額",$U35:$V36)
というように2づつアップさせたいのですが
方法はありますか
よろしくお願いします

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

出来そうでできない？
こんなときは、あきらめて、マクロ

 Sub test()
    Dim i As Long, n As Long

    For i = 0 To 100
        n = 31 + i * 2
        Cells(5 + i, "C").Formula = "=DSUM(月別集計,""契約金額"",$U" & n & ":$V" & n + 1 & ")"
    Next

 End Sub

（マナ） 2015/07/25(土) 16:42

 できるけど、その前にちょっと確認。

 SUMIFS

 ではダメなの？
（笑） 2015/07/25(土) 17:34

 (笑)さんご指摘に激しく同意です。

 データーベース関数って、言葉の響きはかっこいいんですが、まず、データベースという割には
 そのリスト領域が固定。提示のケースでは名前定義で"月別集計"というリストを定義して参照していますが
 たとえばそのリストに行が追加されて増えても、名前定義はそのままですから、定義をやりなおさない限り反映しない。
 さらに、条件領域として、タイトル項目行と条件行の２行セットが必要なので、今回のように、フィルで２行アップしなければいけなくなる。

 まず、リスト領域を名前定義ではなく「テーブル」として設定。名前を"月別集計"でもなんでも好きなものにしておく。
 で、条件欄（Ｕ列、Ｖ列）は、タイトル行抜きの条件行だけを必要な行数、記述しておく。

 式は、通常の SUMIFS。ただし、式を入力しながらテーブルの列を選択、あるいは条件欄を選択。
 こうしておけば、１行ずつのフィルで問題ないわけですし、リストに追加があれば自動的に、テーブルに含まれ
 式での計算対象になりますので。

（β） 2015/07/25(土) 19:52

マナさん
ありがとうございます。
マクロで出来ました。完璧です　助かりました。
フィル後１行ずつ修正するのは、時間が掛かりますので

笑さん
βさん
ありがとうございます。
テーブルでとの事ですが、今まで使った事が無くweb上で検索しましたが
やりたいことの凡例が見つかりませんでした。以下に簡単に表の内容を
書きます。
3.4行目が見出し行(2段で利用列と結合で利用列あり)　5行目以降データ行

    A       B      C    ・・・ K   ・・・ AB     AC
3　No     日付              契約金額　　 西暦    月     
4
5
6
7
8

B3:AC600までが"月別集計"範囲

AB列5行目=IF(B5="","",YEAR(B5))下フィル
AC列5行目=IF(B5="","",MONTH(B5))下フィル
別シートのU.V列に

   U    V
5 西暦  月
6 2014  8
7 西暦  月
8 2014  9
以下続きます。

C5=DSUM(月別集計,"契約金額",$U5:$V6)
年月ごとの(B列)の、契約金額(K列)の集計を表示(契約金額と件数)
D5=DCOUNT(月別集計,"契約金額",$U5:$V6)
こんな感じの表です。
上記表に、具体的に実行させる方法を(テーブル活用方法)教えていただけますか？
お手数お掛けしますがよろしくお願いします

（noboru） 2015/07/26(日) 05:26

 是非テーブル活用をおすすめします。そうしておきますと別シートのＵ，Ｖ列は単に
 2014  8
 2014  9
 2014 10
 といったように、タイトルなしで１行ずつの条件にしておくことができます。

 ただし！
 このテーブル機能に限らないのですが、エクセルの様々な機能を最大限活用するためには「リスト」としては
 ・上段にタイトル行。ただし『１行』。２行を行結合した見出しにはしない！（通常のオートフィルターなんかもそうですね）
 ・列結合は行わない。少なくとも「テーブル機能」では、列結合があっても、テーブル設定をすると自動的に結合がはずれてしまいます。

 ですから、現在のそちらのレイアウトでは『残念ながら』無理です。

 ただ、現在のそちらのレイアウト、なぜ、行結合や列結合になっているのでしょうか？
 その必要があるのでしょうか？

 タイトル行の高さを２行分にしたいので、２行結合？？
 ある項目の列幅を広くしたいので、列結合？？？

 タイトルは　３行目だけにして、３行目の行の高さを大きくしておけばそれでいいですよね？
 また、幅を広くしておきたい列は、列幅を大きくしておけばいいですよね？

 もし、そちらで、レイアウトはかえないということであれば無理ですので、設定方法など活用方法を説明しても無駄足になります。
 もし、レイアウトを、↑でコメントしたように、通常の形にできるなら、設定方法など、アドバイスもできるかと思います。

 いかがですか？

（β） 2015/07/26(日) 06:11

βさん
速攻の返事ありがとうございます。
結合セルは、変更いたします
４行目をタイトル行で利用　５行目以下データ行で利用
アドバイスよろしくお願いします
（noboru） 2015/07/26(日) 09:40

 その集計ならいくつかやり方があります。

 1）COUNTIF と SUMIF
　　作業列を使うのなら、件数は COUNTIF、合計金額は SUMIF で。
　　作業列を2列使って「年」と「月」で分けてますが、特に意味がないのなら
　　1列で可能です。

 2）COUNTIFS と SUMIFS
　　作業列を使わなくても、件数は COUNTIFS、合計金額は SUMIFS で可能。
　　その月の「月初日～月末日」までという範囲にする。

 3）ピボットテーブル（日付を「グループ化」する）
　　関数にこだわらないなら、ピボットテーブルでもできます。
　　日付を「年」と「月」でグループ化する。
　　ピボットテーブルの範囲はB列からK列まででオッケー

 1つ1つ具体的に説明すると長文になるので簡単にまとめてみました。
 何か不明な点はありますか？
（笑） 2015/07/26(日) 10:24

 4）SUMPRODUCT
 　 数式なら SUMPRODUCT を使って配列計算というのあります。
　　これも作業列は不要です。

 参考まで。
（笑） 2015/07/26(日) 10:36

笑さん回答ありがとうございます。
申し分けないですがもう少し具体的にお願い出来ませんか
余り応用が利かないもので・・・

＞１）１列でも可能です
　　　↑可能なんですか是非ご教授ください
＞２）COUNTIFS と SUMIFSは今まで利用したことがありません。
その月の「月初日～月末日」までという範囲←このあたりも含め是非お願いします
ピボットテーブルは、思うようなデータが得られないことがあるので今回は辞めておきます
よろしくお願いいたします
（noboru） 2015/07/26(日) 10:41

 それでは、あるシートの A4 に "NO"(これはどういう文字列でも構いませんが） 、どこかの列の 4行目に "日付" 、この日付列に5行目から日付が入っている。
 どこかの列の　4行目に "契約金額"、どこかの列の 4行目に "西暦"、どこかの列の 4行目に "月" というタイトルが設定された連続したリストだとします。
 また、3行目は完全な空白行だという前提です。

 で、今、C5=DSUM(月別集計,"契約金額",$U5:$V6) 等、式が入っているシートですが、リストが存在するシートとは別のシートですよね？
 （同じシートだと、リストの真っただ中になっちゃいますから）
 また、U列、やV列の条件も、リストのあるシートではなく、この DSUM式が入っているシートですね？

 １．現在設定されている名前定義の "月別集計" を削除してください。
 ２．リストの左上、A4　を選択して、挿入タブ->左のほうのテーブル。これでリスト全体が選択され、デザインタブが表示されます。
 ３．リボン右のほうにテーブルスタイルが表示されえています。▼をおすと、さらに、候補がでてきますので、好きなものに変更可能。
 　　（この変更は、いまやらなくても、後日、いつでもできます）
 ４．リボンの一番左にテーブル名という箱があります。デフォルトテーブル名が表示されていますので、ここに　"月別集計" といれて、テーブル名を変更しておきましょう。
 ５．西暦列の5行目を選び。数式入力をします。
 　　=IF( とタイプし、日付列の 5行目をマウスで選び、続けて ="","", とタイプ。続けて　YEAR( とタイプして 日付列 5行目をマウスで選び、 ) とタイプしてエンター。
 　　これで、この列すべての行に計算式がセットされます。式は　=IF([@日付]="","",YEAR([@日付]))　となっているはずです。
 　　操作がややこしければ、最初からこの式を打ち込んでも、もちろんＯＫです。
 ６．同じ要領で、月列にも式をセットします。式は　=IF([@日付]="","",MONTH([@日付]))　になります。

 ７．次に、今DSUM式があるシートに計算式をセットしましょう。
 ８．以下のようになっていると想定しています。

 ９．C5に式をいれます。５．でやったような要領で、リストのセルを選びながら行ってもいいのですが、説明がややこしいので
 　　できあがりの式を直接書きますと　=SUMIFS(月別集計[契約金額],月別集計[西暦],U5,月別集計[月],V5)　こんな式です。
 　　これを下にそのままフィルコピー。

 これで完成です。

 あとはリストを変更、あるいはリストの最後の行に新規にデータを追加すれば、すべて自動反映されます。
 追加した場合は、リスト内の書式や計算式も自動コピーされます。

 お試しください。
（β） 2015/07/26(日) 11:05

 1）書式はあくまで例ですが

 AB5 =TEXT(B5,"yyyy年m月;;")

 これで「2015年7月」のような文字列になります。

 集計は COUNTIF と SUMIF で。
　
　
 2）U6以下は「2014/8/1」のような「月初日のシリアル値」にします（書式はお好みに合わせて変更）

 件数: =COUNTIFS(Sheet1!B:B,">="&U6,Sheet1!B:B,"<="&EOMONTH(U6,0))

 金額: =SUMIFS(Sheet1!K:K,Sheet1!B:B,">="&U6,Sheet1!B:B,"<="&EOMONTH(U6,0))

 こんな感じかな。
（笑） 2015/07/26(日) 11:20

βさん　ありがとうございます。
長文の回答お疲れ様です。
少し理解しながら実行したいとおもいますので
お時間下さい。
返事は、深夜または明日になるかも・・・
よろしくお願いします

笑さん
ありがとうございます。
回答の早さについて行けませんので
確認しながら作業始めます
後ほどお返事させて下さい。
よろしくお願いします
（noboru） 2015/07/26(日) 11:31

 B列に日付が入っていても、K列（契約金額）は空白ってこともあるのかな？
 で、その場合は件数にカウントしない、、とか。

 だとすると

 1）作業列
 AB5 =IF(K5="","",TEXT(B5,"yyyy年m月;;"))
 集計は COUNTIF と SUMIF で。

 2）件数（作業列なし）
 =COUNTIFS(Sheet1!B:B,">="&U6,Sheet1!B:B,"<="&EOMONTH(U6,0),Sheet1!K:K,">0")

 こんな感じかな？
（笑） 2015/07/26(日) 11:53

 ちなみに当初の DSUM をフィルするという質問に対する回答は

 C5 =DSUM(月別集計,"契約金額",OFFSET($U$33:$V$34,ROW(A1)*2-2,0))

 下へコピー

 参考まで
（笑） 2015/07/26(日) 12:05

βさん
最後の部分でつまずいています。
９.の数式ですが
=SUMIFS(月別集計[契約金額],月別集計[西暦],U5,月別集計[月],V5)　Enterで実行
入力した数式は正しくありません。と表示され　関数ボタンをクリックすると
合計対象範囲　月別集計[契約金額]　　←正しくありません
条件範囲1　　月別集計[西暦]　　　　←正しくありません　
条件1　　　　　U5
条件範囲2　　月別集計[月]　　　　　←正しくありません
条件2　　　　　V5
と表示され

以下に修正すると
=SUMIFS(テーブル2[[#見出し],[契約金額]]?,テーブル2[[#見出し],[西暦]]?,U5,テーブル2[[#見出し],[月]]?,V5)　Enterは可能になるのですが答えは正しくなく0となります。

表名もDSUM時の名前を削除し、テーブル名に「月別集計」を入れたのですが？
表名は月別集計の他に　テーブル2、Print_Area、Print_Titlesとあります
リボンの下関数ボタン左をクリックすると表示されます。
テーブルに変換するデータ範囲ですが
＝$A$4：$AC$605　で
□先頭行をテーブルの見出しとして使用するにチェックを入れました。
お気づきの点が、ありますか？
よろしくお願いします

（noboru） 2015/07/26(日) 14:09

βさん
おっ！できた。
数式を=SUMIFS(テーブル5[契約金額],テーブル5[西暦],U5,テーブル5[月],V5)
うまく月別集計に代わったなかっようです。
ちなみに
この仕様で件数も同様に求まりますか？

すみません　ご指導お願いします。
（noboru） 2015/07/26(日) 15:50

 >>=SUMIFS(テーブル5[契約金額],テーブル5[西暦],U5,テーブル5[月],V5)

 これでエラーにならなかったということは出来上がったテーブルの名前が、テーブル5 だということです。
 テーブルは、それがデフォルト名だとすれば、解除して再設定すると、テーブル○　と　連番でかわっていきます。
 そうすると、式の中のテーブル5 が、今度はエラーになります。

 やはり、固有で名前（"月別集計")をつけましょう。
 で、式は　>>=SUMIFS(月別集計[契約金額],月別集計[西暦],U5,月別集計[月],V5) にしておきましょう。

 ところで、テーブルであろうが、普通のエクセルセル領域であろうが関数そのもののルールは同じです。

 テーブル名[項目名] が、タイトル行を除いたデータ行の、その列の領域です。

 ふつうに件数ならどういう式を書きますか？

 =COUNTIFS(検索条件範囲1,検索条件1,検索条件範囲2,検索条件2) ですよね。

 検索条件範囲は　前述の通り、テーブル名[列ラベル] です。
 検索条件は、テーブルの外のセルならそのセル番号ですし、もちろん、固定で与えるなら "hoge" です。
 また、テーブル内の同じ行の別の列のセルなら、[@列ラベル]になります。

（β） 2015/07/26(日) 16:49

 テーブルは使いこなすと非常に便利なツールです。2013 と 2007/2010 では、リボンがちょっと異なります。
 上でアップしたテーブル作成手順は2013ベースでした。2007/2010なら

http://allabout.co.jp/gm/gc/373733/

 こういったページを参考にして簡単なレイアウトのテーブルで練習されることをおすすめします。

（β） 2015/07/26(日) 16:53

 こっちが提示した方法も試してくれてるのかどうかわからないけど、
 作業列を使う方法の補足（作業列の式は前の回答を参照のこと）
　
【別シート】

	B	　C	　D
4		　件数	　金額
5	2014/8/1		
6	2014/9/1		
7	2014/10/1		
8	2014/11/1

 B5セルに月初の日付を入れる（書式は希望のものに変更）

 B6に　=EDATE(B5,1)　を入れ、表示形式をB5と同じものにする。
 B6を必要なだけ下にコピーしておけば、B5の日付を入れるだけでB6以下は自動的に変わる。

 件数と金額
 C5 =COUNTIF(Sheet1!AB:AB,B5)
 D5 =SUMIF(Sheet1!AB:AB,B5,Sheet1!K:K)

 AB列が「2014年8月」という文字列データでも、COUNTIF と SUMIF はそれを月初の日付と見なすので
 これでカウントできる。

 作業列を使ってもいいのなら、これが一番シンプルだと思うけどね。

 ただし、B5には必ず月初の日付を入れること（シリアル値にする場合）
 そこに多少なりとも不安を感じるのなら

 C5 =COUNTIF(Sheet1!AB:AB,B5-DAY(B5)+1)
 D5 =SUMIF(Sheet1!AB:AB,B5-DAY(B5)+1,Sheet1!K:K)

 としておけば、B列が何日の日付であっても、検索値は月初日になる。

 それは COUNTIFS や SUMIFS でも同じこと。
（笑） 2015/07/26(日) 17:23

βさん
長らくありがとうございました。
無事希望することが実現できました。
テーブルの使い方を教えて頂き感謝です
便利ですね！今後も活用していきたいと思います。

笑さん
いろいろアドバイス回答ありがとうございました。
日付は難しいですね！
DSUM のフィル便利です。こんなこと出来るんですね。
また、分からないこと質問しますがよろしくお願いします。
解決！しました。
（noboru） 2015/07/27(月) 13:21

コメント返信：

[ 一覧(最新更新順) ]

[[20150725151958]] 『フィルの方法について』（noboru） ページの最後に飛ぶ

[[20150725151958]]　『フィルの方法について』（noboru）　ページの最後に飛ぶ