[[20031008100728]]　『日付ごとの売上集計』（masabou5）　ページの最後に飛ぶ

『日付ごとの売上集計』（masabou5）

シート１　　　　　　　　　　　　　　　　　　シート２

   日　付　　　売上金額　           日　付　　　品名　　　売上金額
  03/9/1　　　　　　　　　　　　　  03/9/1       a         100
  03/9/3                            03/9/1       b          50
  03/9/5                            03/9/3       c         250
  03/9/10                           03/9/5       a         150
                                    03/9/5       d         100
                                    03/9/10      d         200

上の表のように、シート２には日付毎、品名毎の売上金額の明細があります。

そこで、売上のあった日付だけをを取り出して、その日の売上金額をシート１に合計で出したいのですが、いい方法がありましたら教えて下さい。

 VBAになりまっけどよろしおまっか？
 Sheet1,2のＡ1から右へそれぞれ項目を書き込んでおくんなはれ。
 標準モジュールに下のコードをコピぺして、シート２にデータを書き込んで下さい。
 なおシート１は「日付」「売上金額」だけにしとって下さい。

 準備がでけたらマクロを実行してみておくんなはれ。
 これ、関数じゃ骨折れますさかいな、試してみて良かったらどうぞ。
 　　　（おいぼれ　弥太郎）
 '------------------
 Option Explicit
 '--------------------
 Sub Uriage()
    Dim ws1 As Object, ws2 As Object
    Dim RowNo As Long, i As Long, totl As Long, n As Long
    Dim hiduke As String
    Dim get_totl As Long

    Application.ScreenUpdating = False
    Set ws1 = Worksheets("sheet1")
    Set ws2 = Worksheets("sheet2")
    ws1.Range("a2:b3000").ClearContents

    RowNo = ws2.Range("a1").End(xlDown).Row
    n = 1
    For i = 2 To RowNo
        n = i + 1
        hiduke = ws2.Cells(i, 1)
        totl = totl + ws2.Cells(i, 3)

        Do While hiduke = ws2.Cells(n, 1)
            totl = totl + ws2.Cells(n, 3)
            n = n + 1
        Loop

        ws1.Cells(get_row, 2) = totl
        ws1.Cells(get_row, 1) = hiduke
        get_totl = get_totl + totl
        totl = 0
        i = n - 1
    Next
    ws1.Cells(get_row, 2) = get_totl
    ws1.Cells(get_row, 1) = "合計"
    Application.ScreenUpdating = True

 End Sub
 '------------------- 
 Function get_row() As Integer
    Dim i As Integer
    i = 1
    Do While Worksheets("sheet1").Cells(i, 1) <> ""
        i = i + 1
    Loop
    get_row = i
 End Function

　あ、衝突。。ピボットテーブルをSheet1に作成・・・じゃだめなのでしょうか？(kuro)

 kuroさん、こんちわ。
 そのビボットテーブルとやらも、披露して差し上げたらどないでんねん？
 私も、後学のため知りとうおますわ、えぇ。尤も理解でけしまへんのやろけど...。
 　　（おいぼれ　弥太郎）

 弥太郎さん　ども♪です。
 ピボットテーブルとは、、、なんていえばいいのやら（汗；　
 自動集計してくれる機能とでも言えばいいのかしら。でわ、作成手順をば。

 Sheet2のデータ上（どのセルでもいい）をアクティブセルにする＞
 メニュー＞データ＞ピボットテーブルとピボットテーブルグラフレポート＞
 「次へ」をクリック＞「次へ」をクリック＞既存のワークシート＞
 Sheet1の表を挿入したい場所（左上端セル）をクリック＞「完了」をクリック

 現れたピボットテーブルのツールバーにある項目名のうち、
 「日付」を「ここに行のフィールドをドラッグします」とあるスペースにドラッグ＆ドロップ、
 「売上金額」を「ここにデータアイテムをドラッグします」とあるスペースにドラッグ＆ドロップします。

 上部に余計と思われる項目名表示が出てしまいますが、これは削除できませんので見せたくなければ
 行ごと「表示しない」指定にすることで対応できます。(kuro)

 ひょへ～っ！！　kuroさん、ピポットテーブルとか言うん初めて試してみたんやけど
 大した働きしまんねんなぁ。いやいや、感心する事しきりですわ、えぇ。

 ところで、Sheet2にデータを追加していった場合は関数みたいに即座に反応してくれ
 しまへんやろ？
 再度集計したい時には現在のテーブルを一旦削除せなあきまへんのん？
 それやったら、テーブルの消し方を教えて～な。アシスタントの無能冴子嬢が度々
 現れて、いちゃもんつけてきよりまんねん。わし、あの娘キライ！
 　　　　ピポットテーブル師匠殿　（おいぼれ弥太郎）

 データの追加については、あらかじめ下方の空欄まで範囲指定してテーブルを作っておけば、
 ピボットテーブル上で右クリックメニューにある「データの更新」で追加更新できます。
 この場合「空欄」も表示されてしまうので「日付」項目右側の▼で現れる一覧の「空欄」
 （およびその他いらない日付）のチェックをはずせば表示されなくなります。
 もし日付順序が変ってしまったなら、日付欄のどこかをアクティブセルにして昇順ソートをかければ直ります。

 テーブルの消し方ですが、、テーブル全部のセルを選択して「削除」するしかないのかな・・？（汗；(kuro)

 弥太郎さんの有り難いVBAにイチャモンを付ける（笑）無理矢理関数式です。

 ＜Sheet1＞
 日付　の前に１列挿入して
 	A	   B	  C	   D
 2行目		03/9/1	
 3行目		03/9/3	
 4行目		03/9/5	
 5行目		03/9/10

 A2　=IF(MAX(Sheet2!A$2:A$1000)<MAX(A$1:A1)+1,"",MAX(A$1:A1)+1)
 B2　=IF($A2="","",VLOOKUP($A2,Sheet2!$A$2:$E$1000,5,0))
 C2　=IF($A2="","",SUMIF(Sheet2!$B$2:$D$1000,B2,Sheet2!$D$2:$D$1000))

　で　下にコピー

 ＜Sheet2＞
 日付　の前に１列挿入して
 	A	   B	  C	   D	E
 2行目		03/9/1	  a	  100
 3行目		03/9/1	  b	  50
 4行目		03/9/3	  c	  250
 5行目		03/9/5	  a	  150
 6行目		03/9/5	  d	  100
 7行目		03/9/10	  d	  200

 A2　=IF(OR(E2>=TODAY()+1,E2=""),"",MAX(A$1:A1)+1)
 E2　=IF(OR(B2="",D2="",B2=B3),"",IF(OR(B2<=B3,B3=""),B2,""))

 で　下にコピー　です。

 しかりとは試していませんので駄目でしたら済みません。

 ピボットテーブルが簡単で機能を使いこなしていますね。
 実はオートフィルタ、ピボットテーブル　の使い方を私は今一理解して無いのです。
 どうも未だ、あの　▼　に馴染めないのですね。今度質問して詳しい方に教えて頂きます。 　　　（jun53）

 ウエッ？！　kuroさん、削除でけまへんのんかいなあっ！
 えらいこっちゃ、このファイルにゃマクロがたっぷり詰まっとるし、シートにも大事な
 データでビッシリ埋まってまんのに...。kuroさんの甘い言葉に乗ってもてニッチもサ
 ッチもいかんようになってもたがな...。どないしょう（大汗;;
 っちゅうのはウソ。
 こんな事も有ろうと思うて、新しことする時はいつも新しBookで試していますさかい
 ご心配なく。(^^)
 ご教授、おおきに。

 junさん、こんちわ
 >今度質問して詳しい方に教えて頂きます
 ひょっとして、これ、この私の事？
 かなわんなぁ、早い事手ぇまわしてからに...。
 　　I like ityamon table（おいぼれ　弥太郎）

 ＜Sheet2＞の式　若干修正しました。　シンプル　伊豆　ベスト
 A2　=IF(E2="","",MAX(A$1:A1)+1)
 E2　=IF(OR(B2="",D2="",B2=B3),"",IF(OR(B2<B3,B3=""),B2,"")) 
 yatarou inoti no　　（jun53）

 なかなか面白いことなっているようなので、ぼくも入れて下さい。

ピボットテーブルの削除は、ぼくは、「編集⇒シートの削除」で、当該シートを削除しています。ピボットテーブルはほんとに便利で、ピボットテーブルがあるからエクセルを使う、という人もいます。masabou5 さんにも是非おぼえて頂きたいと思います。

ピボットテーブルの他にも、「データ⇒集計」機能も使えます。こちらは、操作はすごく単純で、masabou5 さんご紹介のシート２のような簡単な表ですと、何も指定しなくても、ＯＫだけクリックすれば、集計されるのではないかと思います。日付順に並べておく必要がありますが、これはすでに並んでいるようですし…。（もしうまく行かないようでしたら、「集計の設定」で、一番上の「グループの基準」を「日付」にして下さい。）

表が示されると、左欄外に比較的小さな文字で、１、２，３と示されている中から、２をクリックすると、小計欄のみ表示されます。再び全データを表示したい時は、３をクリックします。

小計表示を止めて、完全にもとに戻りたい時は、「データ⇒集計」で、「全て削除」ボタンをクリックします。

合計のほかにも、日ごとの平均なども出せますし、日付の他にも、品名別合計や平均も出せます。（通りすがりの者）

masabou5です。大勢の皆さんから回答をいただき、ありがとうございました。

ただ、ＶＢＡもピボットテーブルを理解していませんので、ｊｕｎ５３さんの関数で作ってみます。何せ式を見てもすぐ理解できませんので、これをもとに自分なりに理解しようと思います。

大勢の皆さん、ありがとうございました。完成しましたら改めてご連絡いたします。（masabou5）

 masabou5さんは旨く完成すると良いですね。

 ピボットテーブルの多様な便利機能は漠然と乍ら知ってましたが、新たに　これはどうなんだろう　という疑問も出てきました。
 もう少し使いこなして整理して、改めて質問する時があるかもしれませんが、
 その時は宜しくお願い致します。 　　　（jun53）

ｊｕｎ５３さん、いろいろやってみたのですが、ｊｕｎ５３さんの式の意味が理解できませんでした。

申し訳ありませんが、細かいことは別にして、式の解説をお願いできませんでしょうか？

ほんとに理解できないのです。（masabou5）

 この式を作っては見たけど本当にうまい事計算してるのか心配なのです。皆さんどうなんでしょう？

 ＜Sheet2＞から説明した方がいいですね　修正した方です。
 E2　=IF(OR(B2="",D2="",B2=B3),"",IF(OR(B2<B3,B3=""),B2,""))
 １つ目のIFは
  (B2=""→日付が "") 又は(D2=""→金額が "") 又は(B2=B3 →日付B2とB3が同じ)なら ""
 本来なら B2=B3 だけで目的は達せられますが表の見栄え上 B2="",D2="" も加えました。
 ２つ目のIFは１番ポイントのところで
 (B2<B3→日付が一行下より小さければ) 又は(B3=""→ 一行下の日付が ""なら) B2→その行の日付を
 (B3="",B2)は最終入力が終わった時点で次の日付が "" ならその日の日付の意味です。

 A2　=IF(E2="","",MAX(A$1:A1)+1)
 (E2="",""→２行目の日付が "" なら "")
 (MAX(A$1:A1)+1) → ここもポイントで、その行の日付が入っていたら１行目から数えて１番大きい数字に１つ足しなさいという意味。
 A$1 は１行目から数えて、の意味ですから１行目に数字が入ってはいけません。
 ２行目から始めるのは１行目が「ゼロ」で無ければいけないからです。
 ３行目から始めたい時は A$2:A2 となります。

 ＜Sheet1＞の式
 A2　=IF(MAX(Sheet2!A$2:A$1000)<MAX(A$1:A1)+1,"",MAX(A$1:A1)+1)
 MAX(Sheet2!A$2:A$1000)<MAX(A$1:A1)+1 → Sheet2 A列の最大数字を見付けてここの最大数字と比較します。
 Sheet2 A列の最大数字より大きい数字は表示されないはずです。

 B2　=IF($A2="","",VLOOKUP($A2,Sheet2!$A$2:$E$1000,5,0))
 A列で抽出した数字を検索値にして Sheet2 から条件にあった値を求めています（日付になります）

 C2　=IF($A2="","",SUMIF(Sheet2!$B$2:$D$1000,B2,Sheet2!$D$2:$D$1000))
 B列で抽出した日付を条件にして(B2) Sheet2 の範囲から条件にあった日付全部の金額の合計を求めます。

 比較的容易な表の作成方法は
 とりあえず私の提示した通りの「行」「列」に　「式」「日付」「記号」「金額」等を埋め込んで下さい。行も１０行程度でいいでしょう。
 それが済んで無事に日付、金額等が表示されるのを確認してから、
 表の形成（列挿入や行の式を必要範囲までコピー等）をした方が良いかもしれません。
 ただし、列挿入は式修正が伴う事もありますが理解して頂ければ出来ると思います。
 範囲外の（A列の左に列挿入は）式が勝手に（自動で）修正されてるはずです。

 説明、長いだけでへたでごめんなさい。一度ここをコピーしてからゆっくり確認して下さい。　　（jun53）

jun53様、いろいろとご丁寧な説明していただき、ありがとうございました。

まだ、解析が出来ていませんので、もう少し時間を下さい。とりあえず御礼まで。（masabou5）

コメント返信：

[ 一覧(最新更新順) ]

[[20031008100728]] 『日付ごとの売上集計』（masabou5） ページの最後に飛ぶ

[[20031008100728]]　『日付ごとの売上集計』（masabou5）　ページの最後に飛ぶ