[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『売上高からデータを絞込み、データ出力がしたいです。』（マイマイ）
お世話になります。
いつも拝見し勉強させていただいてます。
今回私のスキルではどうにも難しく、初めて質問させてもらいます。皆さまの知恵をお貸しください。
やりたいことは売上高からデータを絞込み、データ出力がしたいです。
Sheet"売上高"と"データ出力"が同じブック内にあります。
Sheet"売上高"は元データで、A1〜V1まで下記の項目があります。
抜けている項目は不要なので省いてあります。
A 日付
B 購入者氏名
D モール
E 商品科目
F 商品名
G 個数
H 単価
I 売上
K 原価
L 送料
Q 限界利益
R 限界利益率
S 判定
V No. ※商品ごとにNo.がふってあり、商品科目と商品名で識別しています。
VBAのUserForm1にこのようなフォームを作りました。
┌───────────────────────────┐ ｜ 集計期間 ｜ ｜Textbox"日付1" 〜 Textbox"日付2" ｜ ｜ ｜ ｜ Frame1"対象店舗"−−−−−−−−−−−−− ｜ ｜ ｜ ｜ □Checkbox店舗1 □店舗2 □店舗3 □店舗4 □店舗5 ｜ ｜ Commandbutton1〜2[すべて選択][すべて解除] ｜ ｜ ｜ ｜ −−−−−−−−−−−−−−−−−−−−− ｜ ｜ ｜ ｜ Textbox"店舗手数料" ｜ ｜ ｜ ｜ Commandbutton3[集計] ｜ └───────────────────────────┘ 集計期間と対象店舗(モール)と店舗手数料を入力し、[集計]を押すと上記A〜Vの項目が "出力データ"に左詰めで出力されるということがしたいです。
UserForm1
・"日付1"と"日付2"を入力
・絞り込みたい対象店舗にチェックを入れる(複数選択可能にしたい)
・"店舗手数料"を入力する
Sheet売上高
・元データを集計期間とチェックを入れた店舗で絞込み
・Vの列【No.】を昇順 Rの列【限界利益率】を降順 の優先順位でソート
Sheet出力データ
・出力データ 1ページ目
○年○月○日〜△年△月△日
対象店舗名 手数料
売上高 客単価
限界利益 限界利益率
−−−商品毎の小合計一覧−−−−−
※1 対象店舗…UserForm1でチェックを入れた店舗名
※2 手数料…UserForm1で入力した数値
※3 売上高…指定期間のI列【売上】の総計
※4 客単価…売上高÷購入者氏名カウント数
※5 限界利益…指定期間のR列【限界利益】の総計−※2手数料
※6 限界利益率… ※5限界利益÷※3売上高
・出力データ 2ページ目以降
○年○月○日 山田太郎 店舗1 調味料 商品A 2 1,000 2,000 900 400 700 35％ × 101
・
・
・
商品小合計 調味料 商品A 30 30,000 13,500 4,000 12,500 41％ ○ 101
やりたいことはハッキリしているのですが、私には難しくてどうすればいいのか分かりません。
一気に処理しようとしすぎでしょうか。
＜ 使用 Excel：Excel2016、使用 OS：Windows10 ＞
本業が今立て込んでるので、レスポンス悪くなりますが、それでよろしければお手伝いさせてください。 待ちきれなくなったら、他の回答者お待ちください。
１）実際のデータがないとこちらも判断できないので、 「売上高シート」のデータと、 「ユーザーフォームの入力例」と 「出力データシート」の結果を具体的に出してください。 ２）「出力データシート」に、1ページ目、2ページ目以降とありますが、なんのことですか？ ３）「出力データシート」の表に、行列番号が振ってないですが、日付開始〜日付終了はA,B列で「〜」は書式でよろしいですか？ ４）対象店舗名は複数あった場合、同一セルでカンマ区切りで出力でよろしいのですか？ ５）※3〜6は計算式でできると思うので、回答は※1,2に絞ります
と、ここまで書いてて思いましたが、フィルタオプションでよくないですか？
（稲葉） 2019/02/27(水) 08:26
あ、あと店舗名はモールのことでいいんですかね？ アパレルかな？ （稲葉） 2019/02/27(水) 08:29
1）すみません。すごく長いので分かりづらいのですが、"･"がセル区切りで左詰めと思ってください。
【売上高シート入力例】
A B C D E F G H I J K L M N O P Q R S T U V 1日付･購入者･顧客コード･モール･商品科目･商品名･個数･単価･売上･単原価･送料･値引･クーポン･限界利益･総売上･総限界利益･限界利益率･判定･JANコード･商品管理番号･No. 2 2019/2/25･山田太郎･43521山田太郎･楽天･バスソルト･ハーブ小粒600g･5･1000･5000･300･1500･1000･0･0･2500･5000･2500･50％･△･4571461332826･be-herbspa600･172
【ユーザーフォーム入力例】
┌───────────────────────────┐ ｜ 集計期間 ｜ ｜"2019/2/1" 〜 "2019/2/28" ｜ ｜ ｜ ｜ −−−"対象店舗"−−−−−−−−−−−−− ｜ ｜ ｜ ｜ ■楽天 □Yahoo! □amazon □自社サイト □TEL/FAX ｜ ｜ [すべて選択][すべて解除] ｜ ｜ ｜ ｜ −−−−−−−−−−−−−−−−−−−−− ｜ ｜ ｜ ｜ "店舗手数料100,000" ｜ ｜ ｜ ｜ [集計] ｜ └───────────────────────────┘
【出力データ結果例】
A B C D E F G H I J K L M N 1 2019/2/1･〜･2019/2/28 2 モール名･楽天･手数料･100,000 3 売上高･1,300,000･客単価･3,000 4 限界利益･520,000･限界利益率･40％ 5日付･購入者･モール･商品科目･商品名･個数･単価･売上･原価･送料･総限界利益･限界利益率･判定･No, 6 2019/2/5･山田花子･楽天･塩･岩塩ピンクあら塩1?s･2･1,000･2,000･400･510･1090･55％･○･101 ・ ・ ・ 10 小計･ ･ ･ ･岩塩ピンクあら塩1kg･5･1,000･5,000･1,000･1,590･2,410･48％ 11 2019/2/25･山田太郎･楽天･バスソルト･ハーブ小粒600g･5･1,000･5,000･1,500･1,000･2,500･50％･△･172 ・ ・ ・ 2）本当は1ページ目は商品ごとの小計のみをリストにして表書きのような感じで、2ページ目以降にその詳細という形にしたいと思っていたのですが、あまりにも大変なので、2ページ目以降云々は忘れてください。
3）書式でもA1(日付1) B1(〜) C1(日付2)でもどちらでも大丈夫です。
4）カンマ区切りで大丈夫です。
私だけが使うならフィルタオプションでいいのですが、エクセルに詳しくない人も使うので可能ならVBA化してユーザーフォームに入力するだけボタンを押すだけで集計結果が出るようにしたいなと思いました。
人の手をお借りして、顔厚忸怩たる思いですが、何卒お力をお貸しください。
（マイマイ） 2019/02/27(水) 10:22
全く例になってないと思いますが、推測で・・・ 売上高シートのQ列がたぶん利益か何かでしょうか？
コメント長くなりますので、コメントを2つに分けます。
先に流れを説明して、コード等々を二つに分けます。
UserFormを直接書き換えるコードを使います。
１）エクセルのオプション＞セキュリティセンター＞セキュリティセンターの設定＞マクロの設定＞開発者〜〜＞VBプロジェクト〜〜
にチェックを入れてください。
２）Userform1というフォームがある場合、書き換えてしまうので名前を変更しておいてください。
３）標準モジュールに「一回だけ実行」のコードを入れて一回だけ実行し、消してください。
４）１）の設定を元に戻してください。
５）３）で作成されたUserform1に■以下のコードを入れてください。
６）売上高シートの項目名と、出力データの項目名を一致させてください。
いただいたデータでは、
購入者 ＞ 購入者氏名 いずれか
総限界利益 と 限界利益 どっちのデータが必要なのか
No, ＞ No. いずれか
７）Userform1を実行してください。
８）以下テストデータと結果を提示します。
売上高シート
|[A] |[B] |[C] |[D] |[E] |[F] |[G] |[H] |[I] |[J] |[K] |[L] |[M] |[N] |[O] |[P] |[Q] |[R] |[S] |[T] |[U] |[V]
[1] |日付 |購入者|顧客コード|モール|商品科目|商品名|個数|単価|売上|単原価|送料|値引|クーポン|限界利益|総売上|総限界利益|利益？|限界利益率|判定|JANコード|商品管理番号|No.
[2] |2019/1/1|佐藤 | |埼玉 | | | | |4000| | 400| 500| | | | | | | | | |
[3] |2019/1/1|田中 | |埼玉 | | | | |5000| |1000| 500| | | | | | | | | |
[4] |2019/1/1|田中 | |千葉 | | | | |4000| | 800| 500| | | | | | | | | |
[5] |2019/1/1|佐藤 | |千葉 | | | | |5000| |1000| 500| | | | | | | | | |
[6] |2019/1/1|佐藤 | |千葉 | | | | |5000| |1000| 500| | | | | | | | | |
[7] |2019/2/1|鈴木 | |東京 | | | | |3000| | 300| 500| | | | | | | | | |
[8] |2019/2/1|佐藤 | |東京 | | | | |3000| | 300| 500| | | | | | | | | |
[9] |2019/2/1|鈴木 | |千葉 | | | | |5000| | 500| 500| | | | | | | | | |
[10]|2019/2/1|佐藤 | |東京 | | | | |3000| | 600| 500| | | | | | | | | |
[11]|2019/2/1|佐藤 | |埼玉 | | | | |4000| | 400| 500| | | | | | | | | |
[12]|2019/2/1|佐藤 | |東京 | | | | |4000| | 400| 500| | | | | | | | | |
[13]|2019/3/1|佐藤 | |千葉 | | | | |5000| |1000| 500| | | | | | | | | |
[14]|2019/3/1|鈴木 | |千葉 | | | | |2000| | 400| 500| | | | | | | | | |
[15]|2019/3/1|鈴木 | |東京 | | | | |5000| | 500| 500| | | | | | | | | |
[16]|2019/3/1|鈴木 | |千葉 | | | | |2000| | 200| 500| | | | | | | | | |
[17]|2019/3/1|佐藤 | |千葉 | | | | |3000| | 300| 500| | | | | | | | | |
[18]|2019/3/1|田中 | |埼玉 | | | | |4000| | 400| 500| | | | | | | | | |
[19]|2019/3/1|鈴木 | |東京 | | | | |1000| | 200| 500| | | | | | | | | |
[20]|2019/3/1|田中 | |埼玉 | | | | |3000| | 300| 500| | | | | | | | | |
[21]|2019/3/1|田中 | |千葉 | | | | |1000| | 100| 500| | | | | | | | | |
条件 2019/1/1〜2019/2/1 東京,千葉 手数料1000
出力データシート
|[A] |[B] |[C] |[D] |[E] |[F] |[G] |[H] |[I] |[J] |[K] |[L] |[M] |[N]
[1] |2019/1/1|〜 |2019/2/1 | | | | | | | | | | |
[2] |モール名|千葉東京|手数料 | 1000| | | | | | | | | |
[3] |売上高 | |客単価 | | | | | | | | | | |
[4] |限界利益| |限界利益率| | | | | | | | | | |
[5] |日付 |購入者 |モール |商品科目|商品名|個数|単価|売上|単原価|送料|総限界利益|限界利益率|判定|No.
[6] |2019/1/1|田中 |千葉 | | | | |4000| | 800| | | |
[7] |2019/1/1|佐藤 |千葉 | | | | |5000| |1000| | | |
[8] |2019/1/1|佐藤 |千葉 | | | | |5000| |1000| | | |
[9] |2019/2/1|鈴木 |東京 | | | | |3000| | 300| | | |
[10]|2019/2/1|佐藤 |東京 | | | | |3000| | 300| | | |
[11]|2019/2/1|鈴木 |千葉 | | | | |5000| | 500| | | |
[12]|2019/2/1|佐藤 |東京 | | | | |3000| | 600| | | |
[13]|2019/2/1|佐藤 |東京 | | | | |4000| | 400| | | |
12:17 表が古いまま載せてしまったので修正 次の投稿でコード出します。
（稲葉） 2019/02/27(水) 12:09
'標準モジュールのコード
Sub 一回だけ実行()
'オプション＞セキュリティセンター＞セキュリティセンターの設定＞マクロの設定＞開発者向け＞VBプロジェクト〜〜〜にチェックを入れる
'終わったら、必ずチェックを外す
Dim vbp As Object
With ThisWorkbook.VBProject
On Error Resume Next
.VBComponents.Remove .VBComponents("UserForm1")
Sheets.Add after:=Sheets(1)
Sheets(2).Name = "検索"
On Error GoTo 0
Set vbp = .VBComponents.Add(3)
With vbp
.Name = "UserForm1"
.Activate
.Designer.Controls.Clear 'コントロールの全消去
.Properties("width").Value = 230
.Properties("Height").Value = 250
.Properties("Caption").Value = "集計フォーム"
'ラベル（期間）
With vbp.Designer.Controls.Add("Forms.Label.1")
.Name = "Label1"
.Caption = "集計期間"
.Left = 10
.Top = 5
.Height = 10
.Width = 50
End With
'テキストボックス（日付1）
With vbp.Designer.Controls.Add("Forms.TextBox.1")
.Name = "TextBox1"
.Left = 10
.Top = 20
.Height = 20
.Width = 100
End With
'テキストボックス（日付2）
With vbp.Designer.Controls.Add("Forms.TextBox.1")
.Name = "TextBox2"
.Left = 120
.Top = 20
.Height = 20
.Width = 100
End With
'ラベル（モール名）
With vbp.Designer.Controls.Add("Forms.Label.1")
.Name = "Label2"
.Caption = "モール名"
.Left = 10
.Top = 50
.Height = 10
.Width = 50
End With
'リストボックス（店舗名複数選択)
With vbp.Designer.Controls.Add("Forms.ListBox.1")
.Name = "ListBox1"
.Top = 50
.Left = 10
.Width = 100
.Height = 100
.MultiSelect = fmMultiSelectMulti
End With
'ボタン 全選択
With vbp.Designer.Controls.Add("Forms.CommandButton.1")
.Name = "CommandButton1"
.Caption = "全選択"
.Left = 120
.Top = 60
.Height = 20
.Width = 100
End With
'ボタン 全解除
With vbp.Designer.Controls.Add("Forms.CommandButton.1")
.Name = "CommandButton2"
.Caption = "全解除"
.Left = 120
.Top = 90
.Height = 20
.Width = 100
End With
'ラベル（手数料）
With vbp.Designer.Controls.Add("Forms.Label.1")
.Name = "Label3"
.Caption = "店舗手数料"
.TextAlign = fmTextAlignRight
.Left = 10
.Top = 170
.Height = 10
.Width = 100
End With
'テキストボックス（店舗手数料）
With vbp.Designer.Controls.Add("Forms.TextBox.1")
.Name = "TextBox3"
.Left = 120
.Top = 170
.Height = 20
.Width = 100
End With
'ボタン 集計
With vbp.Designer.Controls.Add("Forms.CommandButton.1")
.Name = "CommandButton3"
.Caption = "集計"
.Left = 120
.Top = 200
.Height = 20
.Width = 100
End With
End With
End With
End Sub
UserForm1のコード
'■
Option Explicit
Private Sub CommandButton1_Click()
'全選択クリック
Dim i As Long
For i = 0 To ListBox1.ListCount - 1
ListBox1.Selected(i) = True
Next i
End Sub
Private Sub CommandButton2_Click()
'全解除クリック
Dim i As Long
For i = 0 To ListBox1.ListCount - 1
ListBox1.Selected(i) = False
Next i
End Sub
Private Sub CommandButton3_Click()
'集計クリック
'入力項目のチェック
Dim i As Long
Dim lst As String
Dim msg As String
If Not IsDate(TextBox1.Value) Then msg = msg & vbCrLf & "テキストボックス１に日付が入力されていません。"
If Not IsDate(TextBox2.Value) Then msg = msg & vbCrLf & "テキストボックス２に日付が入力されていません。"
If Not IsNumeric(TextBox3.Value) Then msg = msg & vbCrLf & "テキストボックス３に数値が入力されていません。"
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = True Then lst = lst & Chr(2) & ListBox1.List(i)
Next i
If lst = "" Then msg = msg & vbCrLf & "リストボックスが選択されていません。"
If msg <> "" Then
MsgBox "未入力または誤った値が入力されています。" & msg
Else
lst = Mid$(lst, 2)
With Sheets("検索")
.Cells.ClearContents
.[A1:B1] = [{"日付";"日付"}]
.[A2] = ">=" & TextBox1.Value
.[B2] = "<=" & TextBox2.Value
.Range("D1").Resize(UBound(Split(lst, Chr(2))) + 1).Value = Application.Transpose(Split(lst, Chr(2)))
.Range("C2").Formula = "=COUNTIF($D:$D,売上高!D2)"
End With
Sheets("売上高").Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheets("検索").Range("A1:C2"), CopyToRange:=Sheets("出力データ").Range("A5:N5"), Unique:=False
With Sheets("出力データ")
.Activate
.[A1] = TextBox1.Value
.[B1] = "〜"
.[C1] = TextBox2.Value
.[B2] = lst
.[D2] = TextBox3.Value
End With
Unload Me
End If
End Sub
Private Sub UserForm_Initialize()
'リストボックスにモール名追加
Dim dic As Object
Dim w As Variant
Dim i As Long
Set dic = CreateObject("Scripting.Dictionary")
With Sheets("売上高")
w = .Range("D2", .Cells(Rows.Count, "D").End(xlUp)).Value
For i = 1 To UBound(w, 1)
dic(w(i, 1)) = ""
Next i
End With
ListBox1.List = dic.keys
Set dic = Nothing
End Sub
出力データシートのB3、B4、D3、D4は数式で埋められると思うので、省きました。 求める結果が異なっている場合、教えてください。 売上高シートのデータが1行だけだと検証できません。 （稲葉） 2019/02/27(水) 12:11
丁寧に書いていただいた手順通りにやったら、出力データに反映されました。
すごいです。感動しました。
モジュールの使い方も、組んでいただいたコードも素晴らしいです。
あとはこのデータを元にソートと小合計まで出来れば完璧です。
こちらは自力で調べながらやってみようと思います。
拙い説明だったのに、本当にありがとうございました。
（マイマイ） 2019/02/28(木) 16:57
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.