Excel (VBA)

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

 
(Windows 10 Home : Excel 2016)
ループを使って効率的に作成したいのですが
投稿日時: 19/09/03 20:23:36
投稿者: BILL20001001

@基準表と割合表があります
A割合表のA〜Gの該当する下のセルに次の算式で%を算出しま
  例 区分1のA 100/(100+200+350+150+450)
       ※基準表のA〜Gの該当する数字です
B区分1〜7まで同様に%を求めます
ループを使って効率的なコーディングをご教示下さい
 
基準表    B4セル
A    100
B    200
C    350
D    550
E    150
F    200
G    450
         割合表    D15セル                        
区分1    A    B    C        E    F    G
    8.0%                        
区分2        B    C                G
                            
区分3    A    B    C        D    E    G
                            
区分4    A        C        D    E    
                            
区分5        B            D    E    
                            
区分6        B    C        D    E    
                            
区分7    A    B    C        D    E    
                            

回答
投稿日時: 19/09/03 21:06:12
投稿者: WinArrow
投稿者のウェブサイトに移動

VBAでなければいけないのかしら?
 
数式だけで、解決すると思いますが・・・・
 

回答
投稿日時: 19/09/03 23:16:29
投稿者: WinArrow
投稿者のウェブサイトに移動

基準表の右側の列に卯木のような数式を入れます。
 
表のレイアウトがよくわからないので、
左端がA1と仮定しています。
=B1/SUM($B$1:$B$7)
下へフィルドラッグ
 
なお、説明では、8%と書かれていますが、5%の間違いかな?
 
そして、区分1のA(D9と仮定して)の下のセルへ
=IF(D9<>"",VLOOKUP(D9,$A$1:$C$7,3,FALSE),"")
右へフィルドラッグ
 
こんなので対応できますが・・・・・

回答
投稿日時: 19/09/03 23:43:04
投稿者: simple

割合を計算するときの分母は、各区分で違うのかも知れませんね。
でも、ワークシート関数で書けるとは思います。
VBAコードの練習なんでしょうか。

回答
投稿日時: 19/09/04 07:26:53
投稿者: simple

レイアウトがよく分からないので、下記を前提にしました。
適当に読み替えて下さい。
 
■レイアウト

      A列   B     C     D     E     F     G     H
1行   A     100                                 
2     B     200                                 
3     C     350                                 
4     D     550                                 
5     E     150                                 
6     F     200                                 
7     G     450                                 
8                                               
9     区分1 A     B     C           E     F     G
10                                              
11    区分2       B     C                       G
12                                              
13    区分3 A     B     C     D     E           G
14                                              
15    区分4 A           C     D     E           
16                                              
17    区分5       B           D     E           
18                                              
19    区分6       B     C     D     E           
20                                              
21    区分7 A     B     C     D     E           
22                                              

■コードの一例です。対象となる範囲の行、列などは適宜修正してください。
 
Sub test()
    Dim dic     As Object
    Dim k       As Long
    Dim j       As Long
    Dim total   As Long
    
    '"A"→100 ,"B"→200 といった組みからなるDictionaryを作成
    Set dic = CreateObject("Scripting.Dictionary")
    For k = 1 To 7
        dic(Cells(k, 1).Value) = Cells(k, 2).Value
    Next
    
    For k = 9 To 21 Step 2
        '合計の作成
        total = 0
        For j = 2 To 8
            total = total + dic(Cells(k, j).Value)
        Next
        
        '割合の作成
        For j = 2 To 8
            If Cells(k, j).Value <> "" Then
                Cells(k + 1, j).Value = dic(Cells(k, j).Value) / total
            End If
        Next
        
        '書式設定
        Cells(k + 1, 2).Resize(1, 7).NumberFormatLocal = "0.0%;;"
    Next
End Sub
なお、計算途上でEmpty→Emptyという要素がdicに追加されるが、
合計の計算には影響しません。

投稿日時: 19/09/04 07:33:11
投稿者: BILL20001001

有難うございました。
参考になりました。
自分なりに理解して、挑戦してみます。

回答
投稿日時: 19/09/04 09:14:54
投稿者: WinArrow
投稿者のウェブサイトに移動

>割合を計算するときの分母は、各区分で違うのかも知れませんね。
 
なるほど・・・・しっかり読まなくてはいけませんね(反省)

回答
投稿日時: 19/09/04 10:41:08
投稿者: mattuwan44

>ループを使って効率的なコーディングをご教示下さい
 
実行の行数(または書く行数)が少ない=効率的なコーディング
ならば、ループしないように書ければさらに良いのでは?
単にループの勉強ならそれはそれでいいのですが。。。

回答
投稿日時: 19/09/05 08:34:25
投稿者: WinArrow
投稿者のウェブサイトに移動

>なお、説明では、8%と書かれていますが、5%の間違いかな?
 
ここにこだわっていて、申し訳ないが
 
100/1450 なので、7%ではないでしょうか?

投稿日時: 19/09/12 18:09:53
投稿者: BILL20001001

simple さんの引用:
レイアウトがよく分からないので、下記を前提にしました。
適当に読み替えて下さい。
 
■レイアウト
      A列   B     C     D     E     F     G     H
1行   A     100                                 
2     B     200                                 
3     C     350                                 
4     D     550                                 
5     E     150                                 
6     F     200                                 
7     G     450                                 
8                                               
9     区分1 A     B     C           E     F     G
10                                              
11    区分2       B     C                       G
12                                              
13    区分3 A     B     C     D     E           G
14                                              
15    区分4 A           C     D     E           
16                                              
17    区分5       B           D     E           
18                                              
19    区分6       B     C     D     E           
20                                              
21    区分7 A     B     C     D     E           
22                                              

■コードの一例です。対象となる範囲の行、列などは適宜修正してください。
 
Sub test()
    Dim dic     As Object
    Dim k       As Long
    Dim j       As Long
    Dim total   As Long
    
    '"A"→100 ,"B"→200 といった組みからなるDictionaryを作成
    Set dic = CreateObject("Scripting.Dictionary")
    For k = 1 To 7
        dic(Cells(k, 1).Value) = Cells(k, 2).Value
    Next
    
    For k = 9 To 21 Step 2
        '合計の作成
        total = 0
        For j = 2 To 8
            total = total + dic(Cells(k, j).Value)
        Next
        
        '割合の作成
        For j = 2 To 8
            If Cells(k, j).Value <> "" Then
                Cells(k + 1, j).Value = dic(Cells(k, j).Value) / total
            End If
        Next
        
        '書式設定
        Cells(k + 1, 2).Resize(1, 7).NumberFormatLocal = "0.0%;;"
    Next
End Sub
なお、計算途上でEmpty→Emptyという要素がdicに追加されるが、
合計の計算には影響しません。