みんなの「教えて(疑問・質問)」にみんなで「答える」Q&Aコミュニティ

こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

締切り済みの質問

月を指定した売上一覧の作成方法

売上等管理表をエクセルで作成しています。
毎回の売上を日付毎・売り先毎に入力できるシート※1は作成したのですが、
別シートで指定した月分だけ売り先毎にまとまった一覧表※2を表示できるように
したいのですが、どのようにすればよいか、判る方教えていただけませんか。

作成したいものの例
※1
5/7 A会社 1,000円
5/8 B会社 2,000円
5/9 A会社 3,000円

6/3 C会社 1,500円

※2
5月売上一覧
A会社 4,000円
B会社 2,000円

投稿日時 - 2019-05-01 16:42:30

QNo.9612515

困ってます

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(4)

ANo.4

>ユーザーが例えばあるセルにその月(”5”月など)を入れると
>自動的にその月の表が表示できるようなボタンを作るのはまた別の話かと思います。
>簡単に作れるものでしょうか?
それは貴方のVBAの慣れの程度と意欲次第と思います。

なお、
どこかのセルに2019/5/1と埋めたときに
その下のセルに翌月1日の日付を関数で求めるのは
容易と思います。
その2つのセルの値をVBAが拾うだけで
指定月の範囲をVBA側に与えることができます。

本物に近いシートレイアウトを説明してくれれば
それにあったVBAをポストすることはできるだろうと思います。

投稿日時 - 2019-05-02 20:59:06

お礼

返信遅くなって恐縮ですが、ご回答誠にありがとうございます。
「このユーザーに質問する」機能を使用して、実際使っているシートを画像添付させていただきますので、よろしければご回答いただければ幸いです。

投稿日時 - 2019-05-05 17:32:25

ANo.3

他の方のコメントにありますように、
一般的には、pivotテーブルを使った集計で対応する課題と思います。
他方、
pivotテーブルでは利用者の事情にフィットしない場合は
VBAを使うことになります。

サンプル画像とコードをポストしますので
よかったら参考に挑戦してみてください。

なお、"#2019/6/1#"としているのは
日付部分に時刻部分が含まれている場合を想定し
6月1日午前0時未満という条件にするためです。


Option Explicit

Sub Sample1()

 Const FDate As String = "#2019/5/1#"
 Const TDate As String = "#2019/6/1#"
 
 Dim cn As Object
 Dim rs As Object
 Dim SQL As String
 Dim shF As Worksheet
 Dim shT As Worksheet
 
 'シートを定義
 Set shF = ThisWorkbook.Sheets("sheet1") '集計元シート
 Set shT = ThisWorkbook.Sheets("sheet2") '集計先シート
 
 'DBを定義、設定
 Set cn = CreateObject("ADODB.Connection")
 Set rs = CreateObject("ADODB.Recordset")
 cn.Provider = "Microsoft.ACE.OLEDB.12.0"
 cn.Properties("Extended Properties") = "Excel 12.0;HDR=YES;IMEX=1"
 cn.Open ThisWorkbook.FullName

 'SQL文を組立
 SQL = ""
 SQL = SQL & "SELECT [会社],SUM([売上])"
 SQL = SQL & "FROM [Sheet1$A1:Z10000]" & vbCrLf '集計元シート名と範囲
 SQL = SQL & "WHERE " & vbCrLf
 SQL = SQL & "( [日付] >= " & FDate & ") and " & vbCrLf
 SQL = SQL & "( [日付] < " & TDate & ") " & vbCrLf
 SQL = SQL & "GROUP BY [会社]" & vbCrLf
 SQL = SQL & "ORDER BY [会社]" & vbCrLf
  
 'SQLを実行
 rs.Open SQL, cn

 '結果セットを格納
 shT.Cells.ClearContents '出力先シートの全領域をクリアー
 shT.Cells(1, 1).CopyFromRecordset rs

  '後処理
 rs.Close
 Set rs = Nothing
 cn.Close
 Set cn = Nothing
 
End Sub

投稿日時 - 2019-05-02 13:59:49

補足

因みに、会社と売上の集計だけでなく、その書式には入力したシートから引っ張ってきた備考欄などの他のセルも反映させるようなイメージです。
その月の分の表を表示させたいときに、ユーザーが例えばあるセルにその月(”5”月など)を入れると自動的にその月の表が表示できるようなボタンを作るのはまた別の話かと思います。簡単に作れるものでしょうか?

投稿日時 - 2019-05-02 19:05:47

お礼

ありがとうございます。
私自身エクセルはピボットテーブル位までしか扱わないので教えていただいたことは挑戦です。が、イメージはそれです。大変参考になります!

投稿日時 - 2019-05-02 18:35:29

ANo.2

計数を会社ごとに合計してしまうが、ピボットテーブルで月でグループ化して
表を作ればしまい、のはなしなのだが。
https://hamachan.info/win7/Excel/pibot.html
「月単位に変更する」

投稿日時 - 2019-05-01 21:21:49

お礼

ありがとうございます。
ピボットテーブルも考えたのですが、普段PCで作らない人が紙で作るのと同じような感覚で見せられるようなシートにしたいと思ってましたので、書式っぽくしたかったのです。

投稿日時 - 2019-05-02 18:29:19

ANo.1

初めに、売上日付列(仮に2行目がA2とします)に記載された日付を年月に変換するために、B2セルより下に以下の式を入れた新たな列を作り、ビボットテーブル゛でB列の年月ごとの集計を行ってみてください。
=DATE(YEAR(A2),MONTH(A2),1)

投稿日時 - 2019-05-01 17:07:13

お礼

ありがとうございます。
ピボットテーブルも考えたのですが、普段PCで作らない人が紙で作るのと同じような感覚で見せられるようなシートにしたいと思ってましたので、書式っぽくしたかったのです。

投稿日時 - 2019-05-02 18:40:13

あなたにオススメの質問