[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『仕損費の集計』（toto）
お世話になります
仕損費の集計表です
A列には1月から12月までの月、B列は日、C列は品番、Ｄ列は金額が入っています。
この表の品番の合計金額を2種類に分けて出したいのです。
1つ目は月度で抽出し8月の合計金額をSeet2の決めたセルに￥3200
2つ目もSeeT2の決めたセルに8月度の品番別の合計金額を。2種類あるので品番ごとに。
同じように9月の分10月の分と入れていきたいのです。
Seet1
A B C D Sheet2 合計 品番別 金額
月 日 品番 金額 8月 ￥3200 a-1234 ￥200
c-2487 ￥3000
8 1 a-1234 100
8 3 a-1234 100 9月 \460 e-5789 \400
8 5 c-2487 1000 f-2369 \60
8 6 c-2487 1000
8 7 c-2487 1000
9 2 e-5789 200
9 3 e-5789 200
9 5 f-2369 30
9 6 f-2369 30
よろしくお願いいたします
＜ 使用 Excel：Excel2010、使用 OS：Windows7 ＞
Sheet1の月は、数値ですよね？ Sheet2の8月は、数値だけ入ったセルの教示形式で8月にしているのでしょうか？ それともまんま、8月？ （BJ） 2018/08/09(木) 22:38
ピボットテーブルでいいのでは？ 「Seet2の決めたセル」というのが、どういうことか分かりませんけど。
一応、確認 ・品番は本当に2種類だけ？ それは月ごとに決まっていて、Sheet2に品番は入力済みなんですか？ ・例示では、8月と9月の品番は別のものですが、 同じ品番が別の月に出てくることはないんですか？
以上です （笑） 2018/08/09(木) 23:10
よろしくお願いします。
（toto） 2018/08/10(金) 10:00
私もピボットテーブル推奨ですね。 日付は、月と日を別に入れるのではなく日付データにしてしまった方が良くないですか？ （コナミ） 2018/08/10(金) 10:12
Dim c As Range, dic As Object, k As Variant, i As Long, tot As Long, sht1 As Worksheet, sht2 As Worksheet
Set sht1 = Sheets("Sheet1")
Set sht2 = Sheets("Sheet2")
sht2.Cells.ClearContents
sht2.Range("A1:D1").Value = Array("月", "合計", "品番別", "金額")
For i = WorksheetFunction.Min(sht1.Range("A:A")) To WorksheetFunction.Max(sht1.Range("A:A"))
Set dic = CreateObject("Scripting.Dictionary")
tot = 0
For Each c In sht1.Range("A:A").SpecialCells(2)
If i = c.Value Then
dic(c.Offset(, 2).Value) = WorksheetFunction.SumIfs(sht1.Range("D:D"), sht1.Range("A:A"), i, sht1.Range("C:C"), c.Offset(, 2).Value)
End If
Next c
sht2.Range("C" & Rows.Count).End(xlUp).Offset(1, -2).Resize(, 2).Value = Array(i, WorksheetFunction.SumIf(sht1.Range("A:A"), i, sht1.Range("D:D")))
For Each k In dic
sht2.Range("C" & Rows.Count).End(xlUp).Offset(1).Resize(, 2).Value = Array(k, dic(k))
Next k
Next i
End Sub
（mm） 2018/08/10(金) 10:18
Sheet1
|[A]|[B]|[C] |[D]
[1] |月 |日 |品番 |金額
[2] | | | |
[3] | 8| 1|a-1234| 100
[4] | 8| 3|a-1234| 100
[5] | 8| 5|c-2487|1000
[6] | 8| 6|c-2487|1000
[7] | 8| 7|c-2487|1000
[8] | 9| 2|e-5789| 200
[9] | 9| 3|e-5789| 200
[10]| 9| 5|f-2369| 30
[11]| 9| 6|f-2369| 30
Sheet2
|[A]|[B] |[C] |[D]
[1]| |合計 |品番別|金額
[2]|8月|=SUMPRODUCT((Sheet1!$A$3:$A$11=--SUBSTITUTE(A2,"月",""))*(Sheet1!$D$3:$D$11))|a-1234|=SUMPRODUCT((Sheet1!$C$3:$C$11=C2)*(Sheet1!$D$3:$D$11))
[3]| | |c-2487|=SUMPRODUCT((Sheet1!$C$3:$C$11=C3)*(Sheet1!$D$3:$D$11))
↑
文字の8月
（BJ） 2018/08/10(金) 10:56
ああ、8月も入れるんだった。 （因みにSUMIFSを使ったことが無いので・・・。）
Sheet2!D2 =SUMPRODUCT((Sheet1!$A$3:$A$11=--SUBSTITUTE(A2,"月",""))*(Sheet1!$C$3:$C$11=C2)*(Sheet1!$D$3:$D$11))
Sheet2!D3 =SUMPRODUCT((Sheet1!$A$3:$A$11=--SUBSTITUTE(A2,"月",""))*(Sheet1!$C$3:$C$11=C3)*(Sheet1!$D$3:$D$11)) （BJ） 2018/08/10(金) 11:20
ピポットテーブルも勉強します。
BJ様の式がやりたかったことに合致したのでそちらでやってみました。
見事できました。 ありがとうございました。
（toto） 2018/08/10(金) 13:33
？？？
Sheet2に品番は入力していないという話だったはずですけど 入力してあるんですか？
品番が入力してあるのなら
A B C D 1 月 合計 品番 金額 2 8 3,200 a-1234 200 3 8 c-2487 3,000 4 9 460 e-5789 400 5 9 f-2369 60
A列には「8」とか「9」とだけ入力し（表示形式～ユーザー定義 0"月"） A3とかA5を空白にせず、すべて埋める。
B2 =IF(OR(A2="",A2=A1),"",SUMIF(Sheet1!A:A,A2,Sheet1!D:D))
D2 =IF(C2="","",SUMIFS(Sheet1!D:D,Sheet1!A:A,A2,Sheet1!C:C,C2))
下コピー
参考まで （笑） 2018/08/10(金) 14:23
入力してあるんですか？ ですけど、あらかじめの入力はしていなくて 品番を入力するとその月のその品番の合計が出るようにしたいです。
(BJ)様の式で例表にあてはめたらうまくできたのですが、本物のワークシートにあてこんだらできませんでした。何がいけないのでしょうか？
変化点はSeet名が6桁の数字であること。360302です。
金額を読み込むセルに=IF(F2=0,"",VLOOKUP(F2,V$2:AI$32,3,0))という関数が入っています。でも隣の列に数値のみコピーしてよんでも＃N/Aになります。
どこがまちがっているのでしょうか？
（toto） 2018/08/10(金) 17:19
=SUMPRODUCT((Sheet1!$A$3:$A$11=--SUBSTITUTE(A2,"月",""))*(Sheet1!$D$3:$D$11)) ↓ =SUMPRODUCT((360302!$A$3:$A$11=--SUBSTITUTE(A2,"月",""))*(360302!$D$3:$D$11<>""),360302!$D$3:$D$11)
=SUMPRODUCT((Sheet1!$A$3:$A$11=--SUBSTITUTE(A2,"月",""))*(Sheet1!$C$3:$C$11=C2)*(Sheet1!$D$3:$D$11)) ↓ =SUMPRODUCT((360302!$A$3:$A$11=--SUBSTITUTE(A2,"月",""))*(360302!$C$3:$C$11=C2)*(360302!$D$3:$D$11<>""),360302!$D$3:$D$11)
実際シート名が数字だと、こんな風に変えられちゃうけど。 '360302'!$A$3:$A$11
（BJ） 2018/08/10(金) 21:11
＞金額を読み込むセルに=IF(F2=0,"",VLOOKUP(F2,V$2:AI$32,3,0))という関数が入っています。 ＞でも隣の列に数値のみコピーしてよんでも＃N/Aになります。 ＞どこがまちがっているのでしょうか？
↑ は当初の質問とどう関係するんですかね？
よく分かりませんけど、最初に言った通り、ピボットテーブルでやった方がいいですよ。 Sheet2には、品番も含めて何一つ入力する必要はありません。 マウス操作だけでできます。
それとも ＞予想として1カ月100品番くらいで設定したいと思います。
入力する品番はこのうちのごく一部なんですか？
数式でやるんだったら、SUMIFとSUMIFS を使いましょう。
参考まで （笑） 2018/08/10(金) 22:45
すいません！最初の式でできていました。本シートのA列が文字列になっていたためエラーになっていました。
何度も教えていただきありがとうございました。
（笑）様アドバイスありがとうございます。
（toto） 2018/08/11(土) 16:38
>すいません！最初の式でできていました。本シートのA列が文字列になっていたためエラーになっていました。
？？？ 金額が ="" こんな感じになるところがあるのでしょう。 だったら、エラーになるよ。 SUMPRODUCT は、=""に対応して無いから。 （BJ） 2018/08/11(土) 19:48
＞SUMPRODUCT は、=""に対応して無いから。
数値に文字列を掛けたらエラーになるというだけの話です。 （よみびとしらず） 2018/08/12(日) 09:54
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.