[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『別シートに貼り付けと同時に特定のセルを統合』（RIN）
[データを別シートに貼り付けたと同時に特定のセルを統合、および集計させる方法]
はじめまして。Excel若葉マークのRinと申します。
環境はExcel2003&WindowsXPです。宜しくお願い致します。
実現したい事を簡単に申し上げますと、
日単位での売り上げを1シートに記載していき、
その日の業務が終わった時点でデータをSheet32へ貼り付けた時に
データが更新されるような仕組みを作りたいと考えています。
＜Sheet1：8/1付け売り上げ表＞
A B C D E
1 業者A みかん 1(個） 100(円) 100(円)
2 業者A りんご 2(個） 200(円) 400(円)
3 業者A なし 3(個） 300(円) 900(円)
4 業者A もも 4(個） 400(円) 1600(円)
5 業者B かき 1(個） 400(円) 400(円)
7 業者B みかん 1(個） 100(円) 100(円)
＜Sheet2：8/2付け売り上げ表＞
A B C D E
1 業者A みかん 5(個） 100(円) 100(円)
2 業者A りんご 1(個） 200(円) 200(円)
3 業者B メロン 1(個） 500(円) 500(円)
・
・
・
■8/1に貼り付けた時の＜Sheet32：月間業者別集計表＞の動き
月初めなのでSheet1のデータをコピー＆ペーストで貼り付けますが、
ここでは業者名（A列）と商品名（B列）の重複チェックを行い、
更に個数（C列）は合計を求め、以下のような表示にしたいと思っています。
A B C D E
1 業者A みかん 2(個） 100(円) 200(円)
2 りんご 2(個） 200(円) 400(円)
3 なし 3(個） 300(円) 900(円)
4 もも 4(個） 400(円) 1600(円)
5 業者B かき 1(個） 400(円) 400(円)
6 びわ 1(個） 300(円) 300(円)
■8/2に貼り付けた時の＜Sheet32：月間業者別集計表＞の動き
上記Sheet32に追記した際も同様に、業者名と商品名での重複チェックを行い、
個数は前日分に加算し以下のような表示にしたいと思うのですが
データを貼り付ける時にSheet32にない商品名があった場合は
そのままデータを追加したいと考えています。
A B C D E
1 業者A みかん 7(個） 100(円) 700(円)
2 りんご 3(個） 200(円) 600(円)
3 なし 3(個） 300(円) 900(円)
4 もも 4(個） 400(円) 1600(円)
5 業者B かき 1(個） 400(円) 400(円)
6 メロン 1(個） 500(円) 500(円)
7 びわ 1(個） 300(円) 300(円)
特記事項としましては
(1)一日のレコード数（＝行の数）はまちまちである。
(2)一日の商品名はダブって記載される事がある。
(3)Sheet32に貼り付けてからオートフィルタを用いて重複レコードを
排除する方法は使用者よりNGと言われてしまいましたので
他によい方法があればお教えください。
(4)VBAも使わない方向です（私の勉強不足により使えませんので・・・）
(3)については相手を納得させる理由があれば相談の余地もあると思いますので
この方法がベスト！というご判断であれば
その設定方法をお教え頂けると助かります。
長文になってしまいましたがよろしくお願い致します。
各日のシートのデータを貼り付けるのではなくて、集計シートから関数で取ってくる 方法はいかがでしょうか？
下記はDSUMでやる方法です。 他のデータベース系の関数で、もっといいやり方があるかもしれないので 色々検討してみてください。
ポイント： 1.各シートの１行目には必ず同じ見出しを付ける
2.集計シートに、検索用のデータを作成する。 例では、集計シートのH列〜L列。 見出しと検索文字を組み合わせて表示したい業者名、商品名別に作成しておく。
3.集計シートの見出しは作っておく。 例では、集計シートのA列〜C列。 ここでは日別にしてますが、日別ではなく合算も可能です。 これでデータに無い商品でも表示できます。
3.集計シートのD,E,F列にDSUM関数で各シートの値を合計して呼び出す。 例ではシート別に呼び出しているので、日付ごとの集計はされていません。 DSUM関数を全シート分足していけば合算も可能だと思います。 ここではややこしくなるので日付別にしました。 日付別にしておいても、ピポットテーブルを使えば集計は簡単です。
シート名[１日]
A B C D E
1 業者 商品 数 単価 金額
2 AAA みかん 1 100 100
3 AAA りんご 2 200 40
4 AAA メロン 3 300 900
5 BBB かき 4 400 1600
6 BBB みかん 1 400 400
7 BBB りんご 1 100 100
シート名[２日]
A B C D E
1 業者 商品 数 単価 金額
2 AAA みかん 5 100 500
3 AAA りんご 2 200 400
4 AAA メロン 3 300 900
5 BBB かき 1 400 400
6 BBB みかん 2 100 200
7 BBB りんご 4 400 1600
シート名[集計]
A B C D E F G H I J K L
1 業者 商品 日付 数 単価 金額 業者 商品 業者 商品
2 AAA みかん 1日 1 100 100 AAA みかん BBB みかん
3 AAA りんご 1日 2 200 400
4 AAA なし 1日 3 300 900 業者 商品 業者 商品
5 AAA もも 1日 4 400 500 AAA りんご BBB りんご
6 AAA かき 1日 0 0 0
7 AAA メロン 1日 0 0 0 業者 商品 業者 商品
8 BBB みかん 1日 1 100 100 AAA なし BBB なし
9 BBB りんご 1日 0 0 0
10 BBB なし 1日 0 0 0 業者 商品 業者 商品
11 BBB もも 1日 0 0 0 AAA もも BBB もも
12 BBB かき 1日 1 400 400
13 BBB メロン 1日 0 0 0 業者 商品 業者 商品
14 AAA みかん 2日 5 100 500 AAA かき BBB かき
15 AAA りんご 2日 2 200 400
16 AAA なし 2日 0 0 0 業者 商品 業者 商品
17 AAA もも 2日 0 0 0 AAA メロン BBB メロン
18 AAA かき 2日 0 0 0
19 AAA メロン 2日 3 300 900
20 BBB みかん 2日 2 100 200
21 BBB りんご 2日 4 400 1600
22 BBB なし 2日 0 0 0
23 BBB もも 2日 0 0 0
24 BBB かき 2日 1 400 400
25 BBB メロン 2日 0 0 0
シート名[集計]の計算式部分
D E F
数 単価 金額
=DSUM('１日'!$A:$E,"数",集計!$H$1:$I$2) =DSUM('１日'!$A:$E,"単価",集計!$H$1:$I$2) =DSUM('１日'!$A:$E,"金額",集計!$H$1:$I$2)
=DSUM('１日'!$A:$E,"数",集計!$H$4:$I$5) =DSUM('１日'!$A:$E,"単価",集計!$H$4:$I$5) =DSUM('１日'!$A:$E,"金額",集計!$H$4:$I$5)
=DSUM('１日'!$A:$E,"数",集計!$H$7:$I$8) =DSUM('１日'!$A:$E,"単価",集計!$H$7:$I$8) =DSUM('１日'!$A:$E,"金額",集計!$H$7:$I$8)
=DSUM('１日'!$A:$E,"数",集計!$H$10:$I$11) =DSUM('１日'!$A:$E,"単価",集計!$H$10:$I$11) =DSUM('１日'!$A:$E,"金額",集計!$H$10:$I$11)
=DSUM('１日'!$A:$E,"数",集計!$H$13:$I$14) =DSUM('１日'!$A:$E,"単価",集計!$H$13:$I$14) =DSUM('１日'!$A:$E,"金額",集計!$H$13:$I$14)
=DSUM('１日'!$A:$E,"数",集計!$H$16:$I$17) =DSUM('１日'!$A:$E,"単価",集計!$H$16:$I$17) =DSUM('１日'!$A:$E,"金額",集計!$H$16:$I$17)
=DSUM('１日'!$A:$E,"数",集計!$K$1:$L$2) =DSUM('１日'!$A:$E,"単価",集計!$K$1:$L$2) =DSUM('１日'!$A:$E,"金額",集計!$K$1:$L$2)
=DSUM('１日'!$A:$E,"数",集計!$K$4:$L$5) =DSUM('１日'!$A:$E,"単価",集計!$K$4:$L$5) =DSUM('１日'!$A:$E,"金額",集計!$K$4:$L$5)
=DSUM('１日'!$A:$E,"数",集計!$K$7:$L$8) =DSUM('１日'!$A:$E,"単価",集計!$K$7:$L$8) =DSUM('１日'!$A:$E,"金額",集計!$K$7:$L$8)
=DSUM('１日'!$A:$E,"数",集計!$K$10:$L$11) =DSUM('１日'!$A:$E,"単価",集計!$K$10:$L$11) =DSUM('１日'!$A:$E,"金額",集計!$K$10:$L$11)
=DSUM('１日'!$A:$E,"数",集計!$K$13:$L$14) =DSUM('１日'!$A:$E,"単価",集計!$K$13:$L$14) =DSUM('１日'!$A:$E,"金額",集計!$K$13:$L$14)
=DSUM('１日'!$A:$E,"数",集計!$K$16:$L$17) =DSUM('１日'!$A:$E,"単価",集計!$K$16:$L$17) =DSUM('１日'!$A:$E,"金額",集計!$K$16:$L$17)
=DSUM('２日'!$A:$E,"数",集計!$H$1:$I$2) =DSUM('２日'!$A:$E,"単価",集計!$H$1:$I$2) =DSUM('２日'!$A:$E,"金額",集計!$H$1:$I$2)
=DSUM('２日'!$A:$E,"数",集計!$H$4:$I$5) =DSUM('２日'!$A:$E,"単価",集計!$H$4:$I$5) =DSUM('２日'!$A:$E,"金額",集計!$H$4:$I$5)
=DSUM('２日'!$A:$E,"数",集計!$H$7:$I$8) =DSUM('２日'!$A:$E,"単価",集計!$H$7:$I$8) =DSUM('２日'!$A:$E,"金額",集計!$H$7:$I$8)
=DSUM('２日'!$A:$E,"数",集計!$H$10:$I$11) =DSUM('２日'!$A:$E,"単価",集計!$H$10:$I$11) =DSUM('２日'!$A:$E,"金額",集計!$H$10:$I$11)
=DSUM('２日'!$A:$E,"数",集計!$H$13:$I$14) =DSUM('２日'!$A:$E,"単価",集計!$H$13:$I$14) =DSUM('２日'!$A:$E,"金額",集計!$H$13:$I$14)
=DSUM('２日'!$A:$E,"数",集計!$H$16:$I$17) =DSUM('２日'!$A:$E,"単価",集計!$H$16:$I$17) =DSUM('２日'!$A:$E,"金額",集計!$H$16:$I$17)
=DSUM('２日'!$A:$E,"数",集計!$K$1:$L$2) =DSUM('２日'!$A:$E,"単価",集計!$K$1:$L$2) =DSUM('２日'!$A:$E,"金額",集計!$K$1:$L$2)
=DSUM('２日'!$A:$E,"数",集計!$K$4:$L$5) =DSUM('２日'!$A:$E,"単価",集計!$K$4:$L$5) =DSUM('２日'!$A:$E,"金額",集計!$K$4:$L$5)
=DSUM('２日'!$A:$E,"数",集計!$K$7:$L$8) =DSUM('２日'!$A:$E,"単価",集計!$K$7:$L$8) =DSUM('２日'!$A:$E,"金額",集計!$K$7:$L$8)
=DSUM('２日'!$A:$E,"数",集計!$K$10:$L$11) =DSUM('２日'!$A:$E,"単価",集計!$K$10:$L$11) =DSUM('２日'!$A:$E,"金額",集計!$K$10:$L$11)
=DSUM('２日'!$A:$E,"数",集計!$K$13:$L$14) =DSUM('２日'!$A:$E,"単価",集計!$K$13:$L$14) =DSUM('２日'!$A:$E,"金額",集計!$K$13:$L$14)
=DSUM('２日'!$A:$E,"数",集計!$K$16:$L$17) =DSUM('２日'!$A:$E,"単価",集計!$K$16:$L$17) =DSUM('２日'!$A:$E,"金額",集計!$K$16:$L$17)
ご参考まで。。。 (yc)
ご参考その２ [[20050201172813]]『ピポッド？』（アリエル） (代奈)
■yc様 RINです。 まずは返信が遅くなってしまった事、お詫びいたします。 質問内容が込み入っていましたのでお返事に時間を要したり しばらくお返事をいただけないのでは・・・と思い ここしばらく書き込みをチェックしておりませんでした。 本当に申し訳ございません。
さて、さきほどご教授頂いたDSUMにて試してみたところ 望みどおりの動きが確認できましたのでこちらの方法を 使わせて頂きたいと思います。
この度yc様には大変細かなご指導を頂きまして誠に恐縮です。 これを機に他の関数についてもこちらで勉強させていただくつもりでおりますので 何かございましたらその際はよろしくお願い申し上げます。
お忙しい中、本当にありがとうございました。
■代奈様 早速ピボットテーブルを参照させていただきました。 私自身、ピボットテーブルをよくわかっていなかったので少し時間はかかってしまいましたが 仕組みを理解し、使いこなせば便利である事がわかりました。
今回は取り急ぎDSUMにて処理しますが使用者と相談してピボットテーブルでの運用も 検討していきたいと思います。
この度はありがとうございました。
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.