Excel (VBA)

Excel VBAに関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(Windows 10全般 : Excel 2016)
スピンボタンでセルをシフトさせて演算
投稿日時: 20/02/05 00:39:03
投稿者: 茶釜

    A B C D E F G
1   あ,い,う,え,お,か,き
2   10,11,12,13,14,15,16
3   20,21,22,23,24,25,26
4   30,31,32,33,34,35,36
 
上記の配列において、1行目のあ,い,う・・・にはそれぞれ列方向の値の和が入るものとします。
(例えば、"あ"には60(10+20+30)が返されます)
2〜4行目にはスピンボタンを配置し、それをクリックすることで当該行のセルの値を左右にシフトさせ、
仮に2行目のボタンを1回押して1セル分だけ左にシフトさせた場合は、61(11+20+30)が返ってくるよう
にしたいのですが、上手くいきません。何方かご教示のほど、よろしくお願いいたします。

回答
投稿日時: 20/02/05 09:53:52
投稿者: simple

こんにちは。
>例えば、"あ"には60(10+20+30)が返されます
とのことですが、求める数値はひとつですよね。
"あ"のほかのセルにも合計値が必要なんですか?
  
質問の話は、VBAを使わなくても、できると思います。
たぶんそのほうが簡単です。
1. スピンボタンは、形状からして、たぶんActiveXコントロールのものだと思いますが、
  「LinkedCell」プロパティを設定できます。
2.これを横方向のオフセットと考え、
  A列の基準セルをもとに、Offset関数を使って、
  シフトを反映した値を求めることができます。
3.同様のことを他の行でも行って、その3つの数値を足せばよいでしょう。

回答
投稿日時: 20/02/05 12:30:03
投稿者: radames1000

「上手くいきません」の内容がわかりません。
 
スピンボタンの配置や値のシフトが出来ていて、
値の和が求められないケースの場合
 
 和を求める数式が「=A2+A3+A4」であれば
 「=SUM(A2:A4)」に変更する。
 
などでしょうか。

回答
投稿日時: 20/02/05 13:01:46
投稿者: hatch315
メールを送信

VBAにすると以下のような感じになると思います。
参考にしてみてください。
 
Private Sub SpinButton1_SpinDown()
 
        Dim I As Long
        Dim ColumnName1 As String
        Dim ColumnName2 As String
        Dim ColumnLast As Long
        
       '合計行は1行目とする。
       '計算対象行はA2から4行目までを対象とする。
       '2行目のボタンを1回押した場合
           
       '列削除判定
        If ActiveSheet.Cells(2, 1) = "" Then
        
          '画面チラツキ防止 再計算Off
           Application.ScreenUpdating = False
           Application.Calculation = xlCalculationManual
          
          '列削除
           ActiveSheet.Cells(2, 1).Delete Shift:=xlToLeft
            
          '最終列取得
           ColumnLast = 0
           For I = 2 To 4
            
               If ColumnLast < ActiveSheet.Cells(I, Columns.Count).End(xlToLeft).Column Then
                  ColumnLast = ActiveSheet.Cells(I, Columns.Count).End(xlToLeft).Column
               End If
           Next I
          
          '列番号変換
           ColumnName1 = ActiveSheet.Cells(2, ColumnLast + 1).Address(True, False)
           ColumnName1 = Left(ColumnName1, InStr(ColumnName1, "$") - 1)
              
          '列番号変換
           ColumnName2 = ActiveSheet.Cells(2, Columns.Count).Address(True, False)
           ColumnName2 = Left(ColumnName2, InStr(ColumnName2, "$") - 1)
              
          '計算式削除
           ActiveSheet.Columns(ColumnName1 & ":" & ColumnName2) = ""
             
          '画面チラツキ防止解除 イベントOn 再計算On
           Application.Calculation = xlCalculationAutomatic
           Application.EnableEvents = True
           Application.ScreenUpdating = True
        End If
End Sub
 
Private Sub SpinButton1_SpinUp()
 
        Dim I As Long
        Dim ColumnName As String
        Dim ColumnLast As Long
        
       '合計行は1行目とする。
       '計算対象行はA2から4行目までを対象とする。
       '2行目のボタンを1回押した場合
               
       '列挿入判定
        If ActiveSheet.Cells(2, Columns.Count) = "" Then
        
          '画面チラツキ防止 再計算Off
           Application.ScreenUpdating = False
           Application.Calculation = xlCalculationManual
            
          '最終列取得
           ColumnLast = ActiveSheet.Cells(2, Columns.Count).End(xlToLeft).Column
    
          '書式コピー
           ActiveSheet.Cells(1, ColumnLast).Copy
           ActiveSheet.Cells(1, ColumnLast + 1).PasteSpecial (xlPasteFormats)
            
          '書式コピー
           ActiveSheet.Cells(2, ColumnLast).Copy
           ActiveSheet.Cells(2, ColumnLast + 1).PasteSpecial (xlPasteFormats)
           Application.CutCopyMode = False
            
          '列挿入
           ActiveSheet.Cells(2, 1).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
         
          '列番号変換
           ColumnName = ActiveSheet.Cells(2, ColumnLast + 1).Address(True, False)
           ColumnName = Left(ColumnName, InStr(ColumnName, "$") - 1)
               
          'どちらかを選択する ↓
           
              '計算式挿入(最終列のみ)
               ActiveSheet.Cells(1, ColumnLast + 1) = "=SUM(" & ColumnName & "2:" & ColumnName & "4)"
             
              '計算式挿入(全列対象)
               For I = 1 To ColumnLast + 1
            
                  '列番号変換
                   ColumnName = ActiveSheet.Cells(2, I).Address(True, False)
                   ColumnName = Left(ColumnName, InStr(ColumnName, "$") - 1)
             
                   ActiveSheet.Cells(1, I) = "=SUM(" & ColumnName & "2:" & ColumnName & "4)"
               Next I
          'どちらかを選択する ↑
            
          '画面チラツキ防止解除 再計算On
           Application.Calculation = xlCalculationAutomatic
           Application.ScreenUpdating = True
        End If
End Sub

回答
投稿日時: 20/02/05 21:36:25
投稿者: simple

私のコメントが少しおかしかったですね。
数値は、一行目の各セルに表示するわけですね。
 
それぞれのスピンボタンのLinkedCellを、それぞれAA2,AA3,AA4セルにセットしたとすると、
A1セル("あ")に、下記の式を設定しておけば、お望みの計算ができるのでは?
=OFFSET(A2,0,$AA$2)+OFFSET(A3,0,$AA$3)+OFFSET(A4,0,$AA$4)
後は、これを右にコピーペイストするだけですね。
 
上記のような計算式で対応してもできますし、
スピンボタンのイベントプロシージャの中で、同様の計算をすることも可能でしょう。
考え方はまったく同じだと思います。
スピンボタンのValueプロパティの値が、上記のAA2セル等の値に相当します。
今度は、RangeオブジェクトのOffsetプロパティを使います。
(各セルへの書き込みをプロシージャのなかでやるよりも、
  セルに計算式を埋め込んで置いた方が簡単じゃないかと愚考します。)
 
あと気になるのは、スピンボタンの最小値は0として、最大値はどうするんですか?
データが何列まであるか知りませんが、
例えば、50列まであるとしたら、
合計計算は30列までにして、シフトの最大値は、20にするとか、そんなことなんでしょうか。

回答
投稿日時: 20/02/06 10:49:37
投稿者: mattuwan44

>2〜4行目にはスピンボタンを配置し、それをクリックすることで当該行のセルの値を左右にシフトさせ、
 >仮に2行目のボタンを1回押して1セル分だけ左にシフトさせた場合は、61(11+20+30)が返ってくるよう
 >にしたい

 
A列はそれで操作できるかもですが、
C列の計算を変えたいときは、どうやってC列の計算の操作だとエクセル君に
認識させるおつもりでしょうか?

回答
投稿日時: 20/02/06 10:52:55
投稿者: mattuwan44

あれ?
列は分けずに全部ずらすのかなぁ。。。。
 
なんか行き当たりばったりで書きかけたけど、
質問文を読み直したけど、
どうしたいかよくわかんなかったです。

回答
投稿日時: 20/02/06 13:16:12
投稿者: simple

回答をしたものの、計算の趣旨がよくわからないですね。
スピンボタンは各行で一つでしょうから
(各セルにあるというのは非現実的。いやそれも排除できない?)、
同じ変位を行毎に、各列に適用したいと考えました。
 
で、これを使って何をしようとしているのか。
 
スピンボタンを操作するということは、
何か最適なものを探すために、試行錯誤しているということなんでしょうか。
もっと端的に、作業の目的を示してもらえば、
こちらの誤解(もしあれば)の軌道修正に役立ちますし、
別のアプローチが提案されるかもしれません。
 
直接の関係者でもないから知る必要もないですが、簡単な趣旨説明があれば、
回答者閲覧者の参考にもなるかもしれません。

回答
投稿日時: 20/02/08 17:22:12
投稿者: takesi

A2からG2の値右に左にグルグルと
 
H2セル値を1にしておく
ActiveXスピンボタンをH2に配置
(どこでもいいがH2セルの作業値を隠すのに)
スピンボタンのプロパティのLinkedCellをH2にする
 
 
Sheet1に
(デザインモードでスピンボタンダブルクリックしても表示される)
Private Sub SpinButton1_Change()
Dim Tmp(6) As Integer
Dim Swap As Long
  'セルを配列に読み込み
  For i = 0 To 6
    Tmp(i) = Cells(2, 1 + i).Value
  Next i
   
  If Cells(2, 8).Value > 1 Then
  '右回転
    Swap = Tmp(6)
    For i = 5 To 0 Step -1
        Tmp(i + 1) = Tmp(i)
    Next
    Tmp(0) = Swap
  ElseIf Cells(2, 8).Value < 1 Then
  '左回転
    Swap = Tmp(0)
    For i = 0 To 5
        Tmp(i) = Tmp(i + 1)
    Next
    Tmp(6) = Swap
  End If
  '配列をセルに書込み
  For i = 0 To 6
        Cells(2, 1 + i).Value = Tmp(i)
    Next i
  'セルの初期値に戻す
  Cells(2, 8).Value = 1
   
End Sub

回答
投稿日時: 20/02/08 22:24:49
投稿者: WinArrow
投稿者のウェブサイトに移動

スピンボタンが必要な理由がよくわかりませんが、
 
合計したいセルをダブルクリックさせて、
SUM関数をするとか、合計を表示するとか
といった対応の方が楽なのではないでしょうか?

トピックに返信