Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Pro : Excel 2016)
同じ日付のデータを積み重ねで表示させる。
投稿日時: 18/07/14 11:30:10
投稿者: wingoone17

お世話になります。
宜しくお願い致します。
 
現在の問題点は、同じ日付で検索すると、重量@の部分が合計されてしまいます。
番号と、数量で検索するのですが、うまく抽出できません。
 
以下のデータがあります。
番号  日付   支店   数量  重量@  重量A   重量B   重量C
001  7/1    A     3 10   20 30
002 7/1 A      2 40   50
003 7/2 B      2 100   200
004 7/2 B     3 30 40 50
005 7/3 A     1 10
006 7/3 B     2 20 30
  〜
099  7/31    A      4 100 200 300 400
100 7/31 B      1 30
 
上記のデータを下のように抽出、表示されたいと思います。
 
全支店
日付  7/1   7/2   7/3  7/31
数量   5 5 3 3
重量  10    100   10   100
重量  20 200 20 200
重量 30 30 30 300
重量 40 40 400
重量 50 50 30
 
全支店、A支店 B支店と集計したいと思います。
 
ご教授お願いします。
 
   

投稿日時: 18/07/14 11:35:38
投稿者: wingoone17

すみません。表示がうまくできていませんが、
データは、番号〜 重量Cまで横にあります。
 
抽出させたい表示は、日付、 同じ日付の数量、数量分の重量を
縦に表示させたいと思います。
 
よろしくお願いします。

回答
投稿日時: 18/07/14 11:55:45
投稿者: simple

集計するのに余り適当なフォーマットではないように思います。
あとあと苦労がかかるように思います。
例えば、以下のようなデータの持ち方にしてはどうですか?
 

番号    日付    支店    重量
1       7月1日  A        10
1       7月1日  A        20
1       7月1日  A        30
2       7月1日  A        40
2       7月1日  A        50
3       7月2日  B       100
3       7月2日  B       200
4       7月2日  B        30
4       7月2日  B        40
4       7月2日  B        50
5       7月3日  A        10
6       7月3日  B        20
6       7月3日  B        30
99      7月31日 A       100
99      7月31日 A       200
99      7月31日 A       300
99      7月31日 A       400
100     7月31日 B        30

そうすればピボットテーブルを利用して、
以下の表が簡単に作れます。
        7月1日          7月2日          7月3日          7月31日         合計    
支店    数量    重量    数量    重量    数量    重量    数量    重量    数量    重量
A       5       150                     1       10      4       1,000   10      1,160
B                       5       420     2       50      1          30    8        500
総計    5       150     5       420     3       60      5       1,030   18      1,660

投稿日時: 18/07/14 12:17:54
投稿者: wingoone17

simple様 ご回答ありがとうございます。
 
データ抽出する時に、入力の重量を個別に表示させ、入力ミスを確認するために、
そのようなデータにしております。
 
合計重量だけでは、入力ミスを確認する事が出来ないと考えております。
 
よろしくお願いします。

回答
投稿日時: 18/07/14 12:41:32
投稿者: simple

>入力の重量を個別に表示させ、入力ミスを確認するために、
>そのようなデータにしております。
>合計重量だけでは、入力ミスを確認する事が出来ないと考えております。

私は、合計重量を入力してくださいとは申し上げていません。
 
入力の確認はひとつの元データですればよいわけで、
それをまた一件単位の表に作り替えているわけで、
そこにミスが入り込む余地があると考えます。
最初から集計を意識した表にしたほうが全体として効率がよいはずです。
 
・一件ごとの入力を確実にすることと、
・合計を算出することは
切り離してはどうか、という提案です。
# まあ、抵抗はあるんでしょう。今までの実績がありますから。
# 心情は理解できます。
他の方の回答をお待ち下さい。それでは。

投稿日時: 18/07/14 13:14:06
投稿者: wingoone17

simpleさん、ありがとうございます。
 
検討致します。

回答
投稿日時: 18/07/15 12:52:08
投稿者: 半平太

1.データは、一か月分しか無いんですか?
 
>全支店、A支店 B支店と集計したいと思います。
 
2.サンプルは全支店分しかないですけど、
それら(例では3種類の集計)はどう表示されればいいんですか?
 
1種類ずつ? それとも一気に全種類?

投稿日時: 18/07/16 10:59:25
投稿者: wingoone17

半平太さん、ありがとうございます。
 
>1.データは、一か月分しか無いんですか?
・データは約1年分のデータになります。
 
>2.サンプルは全支店分しかないですけど、
>それら(例では3種類の集計)はどう表示されればいいんですか?
>1種類ずつ? それとも一気に全種類?
 
・月毎に、A支店、B支店、全支店合計と表示し、
・番号001、7/1に数量3回で、重量@に10、重量Aに20、重量Bに30
 番号002、7/1に数量2回で、重量@に40、重量Aに50
 とありますので、7/1 数量5とし、5個分の重量を縦に表示させたいと考えております。
 
A支店
日付 7/1 7/2〜7/31   
数量 5
重量 10
重量 20
重量 30
重量 40
重量 50
  
 Me.ComboBox1.Valueは、支店名です。
 
 i = .Cells(.Rows.Count, "b").End(xIUp).Row
     tmp = .Range("a1:i" & i).Value
     For i = 2 To UBound(tmp)
         For n = 3 To 9
            a = tmp(i, 2)
              If Month(a) <> Month(b) Then Exit For
                   If tmp(i, n) = Me.ComboBox1.Value Then
                        myN = myN + tmp(i, 5)
                        tmpA(5, 3) = myN
                   End if
このコードでは、7/1に重量@、重量Aが合計されてしまいます。
どのように、ifを入れたらよいでしょうか?
よろしくお願いします。

回答
投稿日時: 18/07/16 11:46:51
投稿者: 半平太

ちょっと確認です。
 
> Me.ComboBox1.Valueは、支店名です。
 
と言うことは、「指定した支店名」だけの処理ですよね?
 
しかし、この表現だと全部一気に処理する様に読めますが、どうなっているんでしょうか?
     ↓
>・月毎に、A支店、B支店、全支店合計と表示し、
 
 
重量のデータは、MAXでK列までと考えていいですか?
 
>If Month(a) <> Month(b) Then Exit For
            ↑
このbは日付データなんでしょうが、どこにあるんですか?
その日付の月だけが対象になるんですね?
 
日付は昇順に並んでいるんですか?
それとも、月またがりに入り繰りがありますか?
 
>どのように、ifを入れたらよいでしょうか?
 
うーん、私の構想とは違うのでちょっと頭が回らないです。
 
私は、「各々の支店」と「全支店」を集計する為にクラスを設計しようと思っています。
上の疑問にお答えいただかないと、具体案を提示できません。
 
クラス名は「日付列」
 
プロパティとして、以下を持つ
 店名   A支店とか、全支店とか
 日付  目的の月の各日
 数量
 重量() 存在する重量の数だけ配列を自動拡張する
 
メソッドとして、以下を持つ
「データ追加」
「結果表示」

投稿日時: 18/07/16 15:57:27
投稿者: wingoone17

半平太さん、ありがとうございます。
 
>と言うことは、「指定した支店名」だけの処理ですよね?
  
はい。その通りです。
 
>重量のデータは、MAXでK列までと考えていいですか?
  
ct列まであります。
 
>このbは日付データなんでしょうが、どこにあるんですか?
データシートと、表示シートを分けているためです。
 
>その日付の月だけが対象になるんですね?
 
はい。月締めで、日付毎の表示になります。
  
>日付は昇順に並んでいるんですか?
>それとも、月またがりに入り繰りがありますか?
 
はい。 1234〜31まで横に並んでいます。
 
  
>クラス名は「日付列」
  
>プロパティとして、以下を持つ
 店名   A支店とか、全支店とか
 日付  目的の月の各日
 数量
 重量() 存在する重量の数だけ配列を自動拡張する
  
>メソッドとして、以下を持つ
「データ追加」
「結果表示」
 
以上の件は、すみません。よく分かりませんので、ご教授お願い致します。

回答
投稿日時: 18/07/16 16:31:47
投稿者: 半平太

> >重量のデータは、MAXでK列までと考えていいですか?
> ct列まであります。
  
 はれ? そんなにあるんですか。
 まぁ、それくらいないと仕事にならないですね。
   
>>このbは日付データなんでしょうが、どこにあるんですか?
>データシートと、表示シートを分けているためです
 
どこに存在しているのか、お聞きしているんですけど。
 
 Me.ComboBox2.Valueとか、「表示」シートのA1セルとかを想定していますが・・
 
 表示シートは、処理に先立って、前回処理結果をクリアしちゃっていいですか?
 
>>日付は昇順に並んでいるんですか?
>>それとも、月またがりに入り繰りがありますか?
> はい。 1234〜31まで横に並んでいます。
 
横と言うのが分からないです。
当初の質問では縦に並んでいたハズですが? データシートの方ですよ。
 
もう一度お尋ねします。
データシートの日付は、昇順に並んでいるんですか?(それとも入り乱れているんですか?)
 
データシート上に休日はないですね?
従って、表示シートもその日付はなくていいですね?

回答
投稿日時: 18/07/16 18:05:34
投稿者: 半平太

ちょっと、不安なので再確認します。
 

tmp = .Range("a1:i" & i).Value 
        ↑
tmpはデータ範囲なのだと思いますが、列番はa列:i列になっていますよね?
 
さっきの話と整合性が取れてないですが、サンプルなのでi列にしただけ、と理解すればいいですね?
  ↓
> ct列まであります。

投稿日時: 18/07/17 18:37:00
投稿者: wingoone17

半平太さん、ありがとうございます。
 
>tmpはデータ範囲なのだと思いますが、列番はa列:i列になっていますよね?
  
>さっきの話と整合性が取れてないですが、サンプルなのでi列にしただけ、と理解すればいいですね?
 
はい。その通りです。
ct列まであります。
 
お世話になり、ありがとうございます。

回答
投稿日時: 18/07/17 19:02:38
投稿者: 半平太

その前のレスで、他にもお聞きしているんですが・・

投稿日時: 18/07/18 14:14:09
投稿者: wingoone17

  半平太 さん 、すみません。
 
 >表示シートは、処理に先立って、前回処理結果をクリアしちゃっていいですか?
はい。大丈夫です。
  
>もう一度お尋ねします。
>データシートの日付は、昇順に並んでいるんですか?(それとも入り乱れているんですか?)
  
すみません。データシートに昇順に並んでいます。
  
>データシート上に休日はないですね?
 
ありません。
 
>従って、表示シートもその日付はなくていいですね?
 
今、表示シートには、日付を入れてそれを、以下のようにしています。
i = .Cells(.Rows.Count, "b").End(xIUp).Row
 tmp2 = .Range("a1:ai" & i).Value
 
 
お世話になります。

回答
投稿日時: 18/07/18 15:00:40
投稿者: 半平太

>>従って、表示シートもその日付はなくていいですね?
 
>今、表示シートには、日付を入れてそれを、以下のようにしています。
>i = .Cells(.Rows.Count, "b").End(xIUp).Row
> tmp2 = .Range("a1:ai" & i).Value
 
ここ、よく分かりません。
表示シートの日付は、1行目だけじゃないのですか?
 
次の返信でも理解できない内容が残る場合、私は降ります。

投稿日時: 18/07/18 17:11:43
投稿者: wingoone17

半平太 さんありがとうございます。
 
説明がうまく出来なくてすみません。
 
>i = .Cells(.Rows.Count, "b").End(xIUp).Row
> tmp2 = .Range("a1:ai" & i).Value
 
>ここ、よく分かりません。
>表示シートの日付は、1行目だけじゃないのですか?
 
 tmp2 = .Range("a1:ai" & i).Valueとしているのは、
tmp2(1,1〜31)に日付を入れ、tmp2(2,1〜31)に数量
tmp2(3,1〜31)に重量を入れます。
tmp2(4,1〜31)以降も重量を入れます。
.Range("a1:ai" & i)としたのは、今後、重量が増加した時に対応するためです。
 
これで、大丈夫でしょうか?
 
 

回答
投稿日時: 18/07/18 17:54:19
投稿者: 半平太

>>>このbは日付データなんでしょうが、どこにあるんですか?
>>データシートと、表示シートを分けているためです

>どこに存在しているのか、お聞きしているんですけど。
 ↑
これにお答えいただいていましたか?
 
1年分のデータがある中で、何月分を処理するのかをどこで(何で)指定しているんですか?

投稿日時: 18/07/19 19:23:48
投稿者: wingoone17

半平太さん、ありがとうございます。
 
何度もお世話になります。
 
>どこに存在しているのか、お聞きしているんですけど。
 ↑
>これにお答えいただいていましたか?
 
ユーザーフォーム上に、bは、テキストボックスで日付を指定して集計しています。
  
>1年分のデータがある中で、何月分を処理するのかをどこで(何で)指定しているんですか?
 
ユーザーフォーム上のコマンドボタンで、月単位で集計するようにしております。
 
以上です。よろしくお願い致します。
 

回答
投稿日時: 18/07/19 20:10:47
投稿者: 半平太

意外にシンプルな機能だったので、クラスは作らず、ユーザー定義型で済ませる事にします。
 
1.対象月のデータに絞り込むのにフィルタ(詳細設定)を使いますので、
 「データ」シートの一行目はIT列までタイトル項目名で全て埋めてください。
 
<例>

 
 行  __A__  __B__  __C__  __D__  ___E___  ___F___  ___G___    __IT___
  1  番号   日付   支店   数量   重量01   重量02   重量03 ・・・ 重量94

2.「全店」の集計時は、支店名に "全支店" と指定してください。
 
3.集計結果は「集計」シートに表示されます。
 
Private Type tp重量
    Ary() As Double
End Type

Sub 集計()
    Const Detail As String = "データ" '実際のシート名に変える
    Const Digest As String = "集計"   '実際のシート名に変える
    Const POS日付 As Long = 2
    Const POS支店 As Long = 3

    Dim 重量s() As tp重量
    Dim RW As Long, InCL As Long, OutCL As Long, Idx As Long
    Dim TempIn
    Dim numOut()
    Dim lastMonEnd As Long  '前月末日 シリアル値
    Dim Emon As Long        '当月末  数値 28〜31
    Dim POSNew As Long
    Dim Branch As String
    Dim MaxRow As Long
    Dim b As Date
    
    b = CDate(Me.TextBox1.Text)
    lastMonEnd = b - Day(b) '前月末日のシリアル値を求める
    
    Emon = Day(DateAdd("m", 1, lastMonEnd + 1) - 1)
    
    Branch = Me.ComboBox1.Value        '全店集計は、"全支店" とする
    
    With Sheets(Digest)
        .UsedRange.Cells.Clear
        
        .Range("A1").Value = "条件"
        .Range("A2").FormulaR1C1 = "=データ!RC[1]-DAY(データ!RC[1])=" & lastMonEnd
        
        Sheets(Detail).Columns("A:CT").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=.Range("A1:A2"), CopyToRange:=.Range("A4"), Unique:=False
        
        TempIn = .Range("A5:CT" & .Cells(.Rows.Count, "B").End(xlUp).Row).Value
        
        .UsedRange.Cells.Clear
        
        With .Range("B2:AF2")
            .FormulaLocal = "=IF(" & Emon & "<COLUMN()-1,""""," & lastMonEnd & "+COLUMN()-1)"
            .Value = .Value
            .NumberFormat = "yyyy/m/d;@"
        End With
        
        ReDim numOut(1 To 1, 1 To Emon)     '数量格納用
    End With
    
    ReDim 重量s(1 To Emon)
    
    For RW = 1 To UBound(TempIn)
        If Branch = "全支店" Or Branch = TempIn(RW, POS支店) Then  '該当支店
            
            For InCL = 5 To 98   'IT列まで
                If TempIn(RW, InCL) = "" Then
                    Exit For
                Else
                    Idx = Day(TempIn(RW, POS日付))             '収納番を決定
                    
                    numOut(1, Idx) = numOut(1, Idx) + 1        '数量をカウントアップ
                    POSNew = numOut(1, Idx)
                    
                    ReDim Preserve 重量s(Idx).Ary(1 To POSNew)
                    重量s(Idx).Ary(POSNew) = TempIn(RW, InCL)
                End If
            Next InCL
        End If
    Next RW
    
    Rem 打ち出し
    Application.ScreenUpdating = False
    
    With Sheets(Digest)
        .Range("B3").Resize(, Emon).Value = numOut
        
        For Idx = 1 To Emon
            If 0 < numOut(1, Idx) Then
                OutCL = Idx + 1
                .Cells(4, OutCL).Resize(numOut(1, Idx)).Value = _
                Application.Transpose(重量s(Idx).Ary)
                MaxRow = Application.Max(MaxRow, numOut(1, Idx))
            End If
        Next Idx
        
        ' 左タイトル用
        .Range("A1:A3").Value = Application.Transpose(Array(Branch, "日付", "数量"))
        .Range("A4").Resize(MaxRow).Value = "重量"
    End With
    
    Application.ScreenUpdating = True
End Sub

投稿日時: 18/07/21 18:04:59
投稿者: wingoone17

半平太 さん お疲れ様です。
 
今回、いろいろとご教授して頂き誠にありがとうございました。
思い通りの事が出来ました。
 
VBA初心者のなので、目から鱗です。
 
今後も、頑張って勉強したいと思います。
本当にありがとう御座いました。