[[20041005124846]]　『週ごとの集計』（頭わるー）　ページの最後に飛ぶ

『週ごとの集計』（頭わるー）

また、お世話になります。教えてください。

下のような表「経費入力」がシート1にあります。

「経費入力」

入力日科目名　　備考　　　　　　支払先　　　　　金　額

 10/1　　　手数料	郵便局振込手数料	　郵便局	　　　　　\130 
 10/2　　　消耗品　　ガムテープ　　　　ローソン　　　　　 \150

この表には１ヶ月（約２０００行）のデータが入力されます。

この表を元に、週ごとの集計データを毎週月曜日に提出しなければいけなくなりました。

シート2に、下の表のような１週間（月曜～土曜）分での、各科目別合計が
表示されるようにしたいのです。

 10/1～10/2
消耗品　　　手数料　　交通費　　・・・・（科目３０種類）
　\520 　　　 \130 　　\8250
　
 10/4～10/9
消耗品　　　手数料　　交通費　　・・・・
  \0　　　 　\520　　　\5800

今は、
　1.「経費入力」の入力日オートフィルタで、10/1以上　　10/2以下

  　　２.「経費入力」の科目名オートフィルタで科目を３０回セレクト

　　　　3.E1欄のSUBTOTALの値をシート2の各欄へ貼り付け

　　　　　しています。

これを関数で、自動表示したいのですが、どのようにしたら良いのでしょうか

教えてください。

 ピボットテーブルを使うのはどうでしょうか？
ちょっと表の体裁は異なりますが・・・
下記のように第何週かをWEEKNUM関数で求めておけば、週単位での集計が簡単になるとおもいます。
B2=WEEKNUM(A2)
月日		週	項目	金額
10月1日		40	手数料	100
10月1日		40	消耗費	200
10月1日		40	手数料	300
10月1日		40	交通費	400
10月1日		40	消耗費	500
10月6日		41	手数料	600
10月7日		41	消耗費	700
10月8日		41	手数料	800
10月9日		41	交通費	900
10月10日	         42	手数料	1000
10月11日	         42	交通費	1100
　↓はピボットテーブルでの結果
合計 / 金額	項目			
週	交通費	手数料	消耗費	総計
40	400	400	700	1500
41	900	1400	700	3000
42	1100	1000		2100
総計	2400	2800	1400	6600
　
または、表の体裁が決まっていて、それにあわすのなら、SUMPRODUCT関数で
=SUMPRODUCT((Sheet1!B2:B12=40)*(Sheet1!C2:C12="消耗費"),Sheet1!D2:D12)
のような感じにすれば良さそうですが、重くなるかもしれません。
　　(Hatch)

(Hatch)さん、ありがとうございます。

なるほど、WEEKNUMという関数を追加すれば、大いに集計しやすくなりますね。

表の体裁が決まっていますので、

　　=SUMPRODUCT((Sheet1!B2:B12=40)*(Sheet1!C2:C12="消耗費"),Sheet1!D2:D12)で

会社で試して見ます。

気になるのは、重くなるかもですが・・・。

報告は、昼休みになります。あしからず。

（最初の集計データ修正しました。）

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　（頭わるー）

WEEKNUMで週を設定するところで、コケました。関数のヘルプもありません。

後で調べたら、標準インストールではインストールされない関数だったのですね。

「ツール」「アドイン」「分析ツール」でスタートさせましたが、今度は会社の

ＯＦＦＩＣＥのＣＤが、どこにあるかわかりませーん。

報告は、夜になるかも知れません。　　　　　　　　　　　　　　（頭わるー）

インストールして、出来ました。

でも、やっぱり全体的に処理が重いって感じです。

間違いのない表にしたかったのですが、軽さも重要だって事に気が付きました。

ピボットテーブルで、各合計を貼り付けで対応したいと思います。

いろいろと、ありがとうございました。

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　（頭わるー）

 やっぱり重くてダメでしたか(^_^;)・・・代案です。
Sheet1のB列を=WEEKNUM(A2)&C2　として、
Sheet2を
	A		B　　　　　C
1	10/1～10/2	40	
2	消耗費	　　　　手数料	交通費
3	700	　　　　400	400
のようにしてB1に週の40をいれます。
A3には=SUMIF(Sheet1!$B$2:$B$12,Sheet2!$B$1&Sheet2!A2,Sheet1!$D$2:$D$12)
として、右へコピーするというのはいかがですか？　
　
 追加です。
式が長くなりますが、Sheet2のA1セルが10/1～10/2のように入力されているのなら、
=SUMIF(Sheet1!$B$2:$B$12,WEEKNUM(DATEVALUE(LEFT($A$1,FIND("～",$A$1)-1)))&A2,Sheet1!$D$2:$D$12)
とすると、B1セルに「40」とかの週を入れなくてもすみます。
　　(Hatch)

 あっ・・・年の処理がうまくいかない可能性が・・・年始の頃が危なそう・・・
ということで、再度追加しておきます。
=SUMIF(Sheet1!$B$2:$B$12,WEEKNUM(DATEVALUE("2004/"&LEFT($A$1,FIND("～",$A$1)-1)))&A2,Sheet1!$D$2:$D$12)
としておいた方がよさそうですね・・・
　(Hatch)

お手数をおかけしました。上の「週指定」「2004入り日付指定」の２種類の方法で

共に出来ました。(Hatch)さん、ありがとうございました。

色々試してみて感じた事なのですが、範囲指定で１ヶ月（約２０００行）のデータに

余裕を持って対応する為に、初めから広い範囲を設定している（約３０００行）に

せざるを得ない事（集計範囲が広い）が、重さの原因なのでしょうか？

だとしたら、「科目別にシートを分けてくれ」との本社からの別の要望もあるので、

科目別にシートを分けて、データコピーし、少ないデータ量で同様の処理をした方が、

軽い処理が出来るのでしょうか？　　

　　　　　　　　　　　　　　　　　　　　　　　　　　　　（頭わるー）

 傍観してましたが、Hatchさんがお忙しそうなので、横からちょこっとお邪魔します。
私ならこうするかなという例です。
元データからフィルターを使って、指定週分のデータを抽出、別シートにコピーし、
Hatchさんも書かれている、SUMIF関数で集計します。
その際、指定週分の抽出は済んでますから、検索するのは、科目名のみで済みます。
例としては、
=SUMIF(Sheet1!$B$2:$B$700,A$2,Sheet1!$E$2:$E$700) って感じで右にコピーする。
                          ↑　科目を表示している行
フィルターで抽出したデータをコピーする場合に、不要列を非表示にし、ジャンプ・可視セルで
コピーすれば、データ量も削減できます。　　(sin)

(sin)さん、アドバイスありがとうございます。

うまく出来るかわかりませんが、マクロの記録とか使ってシートコピーをして

是非ためしてみます。

報告には、かなり時間がかかるかも知れません。

また、よろしくお願いします。

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　（頭わるー）

 ついでと言っては何ですが、私が当初考えていた方法（補助用のシートを作成しない方法）も出しときます。
考え方は、元データ内の指定範囲（行範囲）を探し出し、その範囲内で科目に対し、
SUMIF関数で集計します。
※元データ（テスト作成につきSheet2になっています）の入力は、日付順で昇順になっていることが前提です（当然途中に空白行はなし）。
また、集計するシートに範囲の開始行と終了行を表示させる作業セルを設けます。
どこでも良いですが、ここでは、B1とC1としています。邪魔なら文字色を白にして下さい。
  B1セルに　↓　の式（入力日が歯抜けでも対応させる為メッチャ長いです）
=IF(ISNUMBER(MATCH(DATE(YEAR(Sheet2!$A$2),MONTH(LEFT($A$1,FIND("～",$A$1)-1)),DAY(LEFT($A$1,FIND("～",$A$1)-1))),Sheet2!$A$1:$A$20,FALSE)),MATCH(DATE(YEAR(Sheet2!$A$2),MONTH(LEFT($A$1,FIND("～",$A$1)-1)),DAY(LEFT($A$1,FIND("～",$A$1)-1))),Sheet2!$A$1:$A$20,FALSE),IF(MATCH(DATE(YEAR(Sheet2!$A$2),MONTH(LEFT($A$1,FIND("～",$A$1)-1)),DAY(LEFT($A$1,FIND("～",$A$1)-1))),Sheet2!$A$1:$A$20,TRUE)+1>MATCH(DATE(YEAR(Sheet2!$A$2),MONTH(RIGHT($A$1,LEN($A$1)-FIND("～",$A$1))),DAY(RIGHT($A$1,LEN($A$1)-FIND("～",$A$1)))),Sheet2!$A$1:$A$20,TRUE)," ",MATCH(DATE(YEAR(Sheet2!A2),MONTH(LEFT($A$1,FIND("～",$A$1)-1)),DAY(LEFT($A$1,FIND("～",$A$1)-1))),Sheet2!$A$1:$A$20,TRUE)+1))
  C1セルに
=IF($B$1=" ","",LOOKUP(DATE(YEAR(Sheet2!$A$2),MONTH(RIGHT($A$1,LEN($A$1)-FIND("～",$A$1))),DAY(RIGHT($A$1,LEN($A$1)-FIND("～",$A$1)))),Sheet2!$A$1:$A$20,ROW(Sheet2!$A$1:$A$20)))
  A3セルに　↓　の式、B列以降にコピー
=IF($B$1=" ","対象なし",SUMIF(INDIRECT("Sheet2!B"&$B$1&":B"&$C$1),A$2,INDIRECT("Sheet2!E"&$B$1&":E"&$C$1)))
抽出する範囲内の入力日が、どんな時でも全て入力されるならば随分短い式（特にB1の式）で済みます。
WEEKNUM関数は、存在すら知りませんでしたので、こんな事になってます。
(sin)

(sin)さん、いろいろとありがとうございます。

実を言うと、いろいろなマクロを入れ過ぎていた事が、原因のようでした。

ファイルサイズをチェックしてみたら、ビックリ！7.93MBもありました。

もう一度、作り直しをしてみようと思っています。

WEEKNUMで求めた欄にオートフィルタを掛け、新しく作る予定のシート

「第１週」～「第５週」へデータをコピーして、SUMIF関数で求めてみようと

思います。

(Hatch)さんには、新しい関数（WEEKNUM）を教えて頂き、

(sin)さんには、作る上での考え方を教えて頂き、大変勉強になりました。

くじけた時には、またいろいろと教えて下さい。
　　　　　　　　　　　　　　　　　　　　　　　　　　　　（頭わるー）

コメント返信：

[ 一覧(最新更新順) ]

[[20041005124846]] 『週ごとの集計』（頭わるー） ページの最後に飛ぶ

[[20041005124846]]　『週ごとの集計』（頭わるー）　ページの最後に飛ぶ