excelで日報を作りたい！別シートに計算結果を出す集計方法について。

質問

関数初心者です。日報をexcelで作りたいと考えているのですが、うまく集計ができず困っています。どなたかお助けください。
A,Bという２つのシートがあるとします。
Aのシートには横列に日付、お客様名、担当、コース代金、税金、税抜合計、合計金額、支払い方法・・・・といった項目がならんでいます。
お客様がきたらどんどん下にかきこんでいく形です。
また支払い方法という項目には、入力規則で現金、売掛、カードと
選べるようにしてあります。コース代金のところはvlookupをつかっていてコース名を選ぶと金額がはいって計算できるようになっています。

シートBには横列に日にち、縦列には、現金売計 カード売計　売掛計 税合計 本日総売上
という項目があります。
シートAで選んだ支払方法の項目ごとにシートBに自動的に合計をいれて、いきたいのです。たとえばカードを選んで合計がでたら一日の終わりにBシートで支払い方法ごとの合計、税合計、本日売上
を計算できる式をいれたいのです。
集計機能でシートＡにそのまま合計をだしていこうかとおもったのですが、ここには３０日分はいるのであまり煩雑になってもいやだし、シートBにSumproductをつかってみたのですが、やりかたがまずいのかうまくいきません。。ここには何をいれるというのをどなたかわかりやすくおしえていただけるとうれしいです。また、日計でこういうやり方があるというのがあればおしえてください。
どうぞよろしくおねがいします

回答No.1

zap35 · ベストアンサー

ピボットテーブルを２つ（支払い方法別、全体）作るのが簡単ですが、一つにしたいのでしょうか。補足要求です
１）カード計、売掛計、現金計はAシートのどの列を合算するのですか？
もしAシートの合計金額列（H列）でよければ、BシートのB1から右方法に日付（整数で1、2、3…）があり、A2に「現金計」があるとして、B2の式は
=SUMPRODUCT((LEFT(A!$H$2:$H$100,2)=LEFT($A2,2))*(DAY(A!$A$2:$A$100)=B$1)*(A!$D$2:$D$100))
を入力して、支払い方式毎の集計範囲にコピーします

２）税合計、本日総売上は支払い方法に関係なく全体を合計すれば良いのですか？
それで良ければ、例えば税合計（AシートのE列を集計）は
=SUMPRODUCT((DAY(A!$A$2:$A$100)=B$1)*A!$E$2:$E$100)
になります。

範囲は変更してください

補足

施術シート
　　A B C D E F G H I J K L M N
1　日付 お客様名 担当 コース コース金額 OP OP価格 OP OP価格 会員 税金 支払方法 税抜合計 合計金額
2　2007/4/1 あいお A子 B70 7,350 デコルテ2,100 デコルテ 450 現金 　　 9,000 　 9,450
3　2007/4/2 かきこ B子 B80 9450 ヘッド 2,100 450 カード 　　　　　9000 　　　　2,000
・
・
・

日計シート
　A 　B C D E F G H・・・・・・
1４月 　1日 　2日 3日 4日 5日 6日 7日・・・・・
2施　術
3現金計
4カード計
5売掛計
6現金税計
7カード税計
8売掛税計
9税合計
10本日総売上
11現金累計
12カード累計
13売掛累計
14税金累計
15総累計

上記の２つのシートがあり、カード計、売掛計、現金計は施術シートの税抜合計と税金をわけて合算していきます
さらに、本日総合計、また日ごとの累計合計もみれるようにしていきたいのです。
なので、日計シートの現金計のところは、日本語でかくと「日付毎にもし施術シートのL欄に現金の文字がはいっていたら、その積をもとめよ」
というかんじだとおもうのですが、これが関数にうまくできません、、、。
累計みるのはピボットテーブルでもいいかなとおもうのですが、それぞれ支払い合計ごとの本日計は
レジしめをするときにみていくので、1枚でみれるといいです。
施術シートはレジ代わりに、それぞれ各担当者が入力して、最後日計シートをみて、レジをしめるという
やり方にしようと考えています。

いろいろ考えたのですが、なかなかうまくいかず、たすけてほしいです！！

回答No.2

zap35

＃０１です。施術シートはK列が税金、L列が支払い方法、M列が合計金額ですね。
日計シートのB3が「１日の現金売の合計金額」、B6が「１日の現金売りの税額合計」でよいですか

B3の式は
=SUMPRODUCT((LEFT(施術!$L$2:$L$100,2)=LEFT($A3,2))*(DAY(施術!$A$2:$A$100)=LEFT(B$1,LEN(B$1)-1)*1)*(施術!$N$2:$N$100))
B3をB4（１日のカード計）、B5（１日の売掛計）にコピーして、B3:B5を右方向にもコピーして下さい

B6の式は
=SUMPRODUCT((LEFT(施術!$L$2:$L$100,2)=LEFT($A6,2))*(DAY(施術!$A$2:$A$100)=LEFT(K$1,LEN(K$1)-1)*1)*(施術!$K$2:$K$100))
になります。同じようにB6をB7、B8にコピーしてB6:B8を右方向にもコピーして下さい

B9の「１日の税合計」は
=SUM(B6:B8)
です。またB10の「１日の売上合計」は
=SUM(B3:B5)
になります。

B11の現金累計は
=SUM($B$3:$B3)
です。B11をB12（カード累計）、B13（売掛累計）にコピーします。更に右方向にコピーします。

B14（税累計）は
　=SUM($B$14:$B14)
B15（売上総合計）
　=SUM($B$15:$B15)
になりますので、これをそれぞれ右方向にコピーします。

お礼

zap35さん！！！で、できましたーーー！
大感謝です。ほんとうに助かりました。ありがとうございます！！！！

数字が見えたときは大感激でした。。

この調子でつくっていきます。
また困ったときは助けていただけるとうれしいです。

どうぞよろしくおねがいします