趣味のエクセルマクロ

ナンバーズ4などの数字選択式宝くじデータ分析用の自作マクロおよびナンバーズ4の各種データリストなどをブログにしています。

エクセルメモ

・自分の為のメモなので順不同、重複あるかもしれません。



<<関数 計算式>>


・関数について---マクロを勉強する前に関数をマスターするのはとても大事なことだと言いますね。そして関数は複数組み合わせて、複雑な処理が出来る様になりますね。

関数で出来ない事をマクロで処理する。逆に、関数で出来る事をマクロで作るのは無駄ですね。だから、関数が分からないと遠回りになると思います。



・平均を求める
  A3からj12のセルに入ってる値で80以上の値の平均値を求める。
  =(SUMIF(A3:J12,">=80"))/(COUNTIF(A3:J12,">=80"))


・以上,以下判断
  =IF(E18>=-0.3,IF(E18<=0.3,1,0),0)
   -0.3以上+0.3以下の時---1
       それ以外の時----0とする。


・集計
  =SUBTOTAL(集計方法,範囲)
        集計方法  1、AVERAGE
              2、COUNT
              3、COUNTA 
          以下-----------


・配列数式
  B2*B3---------①
C2*C3---------②→①+②+③=合計求める時 
D2*D3---------③
          {=SUM(B2:D2*B3:D3)}
           { }はCtrl+Shift+Enterキー押下


・文字を数字にする
  =VALUE(A1) (マクロ上ではVAL)


・乱数発生させる
  =RAND()           (マクロ上ではRND)
=RANDBETWEEN(最小値,最大値)


・ セルの結合
=CONCATENATE(原本!E4423,原本!F4423)
  (原本!E4423が---1,原本!F4423--2の時→12になる)


・並び替え(小さい順、大きい順、他 =SMALL(範囲,順位))

・ IF関数 (関数とマクロでの式)
 =IF(a1=1,1,0) b1に左の式の場合a1が1の時b1は1に、でない場合0になる。
 If  cells(1,1)=1 Then cells(1,2)=1 Else cells(1,2)=0 マクロでの式
又はマクロでの式
 If  cells(1,1)=1 Then
  cells(1,2)=1
 Else
  cells(1,2)=0 
 End If


  =IF(AND(a1=10,b1=10),a1+b1,a1-b1)-----c1に入った計算式の場合
If  cells(1,1)=10 and  cells(1,2)=10 Then マクロでの式
 cells(1,3)=cells(1,1)+ cells(1,2)
Else
   cells(1,3)=cells(1,1)- cells(1,2) 
End If


  =IF(OR(A1=10,B1=10),10,0) -----c1に入った計算式の場合
  If  cells(1,1)=10  or  cells(1,2)=10 Then マクロでの式
cells(1,3)=10
 Else
   cells(1,3)=0
End If


 AND  OR も最初のマクロ式の様に一行で表しても良いでしょうね。


=countif(範囲,求める値)-------以下応用例(5の次に4が出たなら1とする)

・文字の分解(4桁の数字に見える文字を1桁数字にする)


 Q4472に数字が文字として入っている場合下の計算、無い場合何も出さない。


=IF(Q4472="","",VALUE(LEFT(Q4472,1)))      1番左
=IF(Q4472="","",VALUE(RIGHT(LEFT(Q4472,2),1)))  左から2番目
=IF(Q4472="","",VALUE(LEFT(RIGHT(Q4472,2),1)))  左から3番目
=IF(Q4472="","",VALUE(RIGHT(Q4472,1)))     1番右
 LEFTとRIGHTを組み合わせる代わりにMIDを使うこともある。


・偶数奇数に分ける(文字の分解した数字をもとに4つとも)


=IF(OR( R4472=1,R4472=3,R4472=5,R4472=7,R4472=9),"奇","偶")
=IF(OR( S4472=1,S4472=3,S4472=5,S4472=7,S4472=9),"奇","偶") 
=IF(OR( T4472=1,T4472=3,T4472=5,T4472=7,T4472=9),"奇","偶")
=IF(OR( U4472=1,U4472=3,U4472=5,U4472=7,U4472=9),"奇","偶")
あるいは =IF(MOD( R4472,2)=0,"偶","奇")


・大小に分ける
(文字の分解した数字をもとに4つとも)
=IF(R4472<5,"■","□")&IF(S4472<5,"■","□")&IF(T4472<5,"■","□")&IF(U4472<5,"■","□")


<<マクロ覚書>>


・セルの指定 (マクロでは入力、出力などでの基本部分)
   cells(行、列).select   A3  cells(3,1).select      D4  cells(4,4).select 
   cells(行+1,列+1).select  は1行下へ1列右に 
      cells(行-1,列-1).select  は1行上へ1列左に 
  cells(行,列)の行、列は変数とする。


               Range("dd16") = "=cz15"   dd16セルにCZ15セルの値を入れる
   Range("aoh28") = "=ストレートパターン!C4"
     シート ストレートパターン のC4のセルの値をaoh28セルに入れる。



         複数範囲 Range(cells(行、列),cells(行、列)).select 
Range("A1:G"+Mid$(Str$(T),2)).select   Tは変数


 アクテブセル(現在選択中のセル)の列、行の取得(整数)
   列=ActiveCell.Column  (列は変数(整数)とする)  
   行=ActiveCell.Row         (行は変数(整数)とする)
   関数で列を取得は  =COLUMN(A1)----1となる。


列表示を上の様に数字で表してセルの列位置を調べ、セルの式を作る。A1はcells(1,1).
(ファイル → オプション → 数式 → 数式の処理 R1C1を選択する)
-------------------------------------------------------------------------------------

ユーザーフォームを開く(ユーザーフォーム1の場合)
UserForm1.Show------別のマクロの中に記入
ユーザーフォームを閉じる
Unload UserForm1-----okボタンにマクロ設定する
(選択後、okボタン押下でユーザーフォームを閉じる)
Private Sub CommandButton1_Click()
If pata_1 = "" And pata_2 = "" Then MsgBox ("選択して下さい"): Exit Sub
Unload UserForm1 '選択後ユーザーホームを閉じる
Exit Sub
End Sub
上のユーザーホームは自分で作成しなければなりませんが。
--------------------------------------------------------------------------------------

Dim   start  as  integer 
start = MsgBox("開始しますか?", vbYesNo)
If start = vbNo Then End  ’はいなら開始、いいえなら終了
実行時間が長いため間違ってマクロを実行させないように選択させている。
変数のstart とメッセージ内容は任意に設定 (はい(Y)、いいえ(N)は自動で表示)
これはユーザーホームの様に作成しなくて良い。(マクロ命令にあるので)


--------------------------------------------------------------------------------------
・変数(変化する値)
   Dim c, l, r As Range’オブジェクト変数
   Dim deme  As Integer 整数型変数
   Dim deme(3)  As Integer   配列変数
   Dim moji  As string 文字列型変数
      Static  (エクセル起動中は値を保持する変数)

 変数の値を消す 
  Set c=Nothing オブジェクト変数
  deme=0   整数型変数
  Erase deme  配列変数
  moji="" 文字列型変数


・定数(変化しない値)
 Const deme As Integer=1 (demeを整数の1と設定する)


・変数、定数ともローカル、モジュールレベル、パブリックが有り適用範囲、使用期間に  
 より選択する。
 プロシジャーだけ、モジュールでのとかありますが分かりにくいですね。
 習うより慣れろの感覚で覚えたいですね。


------------------------------------------------------------------------------------------
Sub saikeisanoff() ’再計算(画面表示関係含む)をしない設定マクロ
With Application
   .Calculation = xlManual
   .MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False  '画面表示関係
Application.ScreenUpdating = False
End Sub


Sub saikeisanon() ’再計算(画面表示関係含む)をする設定マクロ
With Application
   .Calculation = xlAutomatic
   .MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = True '画面表示関係
Application.ScreenUpdating = True
End Sub


再計算をしないにして他のマクロ実行後、再計算をするに戻す。
マクロ実行スピードアップの為のマクロ


-----------------------------------------------------------------------------------------------------
・行、列とも連続してデータの入ったセルの範囲を取得する方法
①変数の指定
 Dim 行列範囲  As  Object 
②行、列範囲の取得
 Set 行列範囲=cells(1,1).CurrentRegion 
③行(最終行)を入れる変数指定
 Dim 行  As  Integer
④最終行を取得する方法
 行=行列範囲.Rows.Count
⑤列の場合は
   列(最終列)を入れる変数指定
 Dim 列  As  Integer
    最終列を取得する方法
 列=行列範囲.Columns.Count
  番地の表示 
         番地=cells(1,1).CurrentRegion .Address(ReferenceStyle:=XlA1)
         msgBox"A1形式;"& 番地



・連続でデータの入った最終行を取得する
 Dim 最終行  As  Integer
 最終行=Range("c1").CurrentRegion.Rows.Count
    列BCDのデータの最終行


----------------------------------------------------------------------------------------------
・プロシジャーとモジュール
  プロシジャー Sub A()やSub B()など
  モジュール  プロシジャーをまとめたもの
         Sub A()とSub B()など



----------------------------------------------------------------------------------------------
・マクロ関数
 自分専用の関数を作る場合はマクロ関数を作ります。
 作った関数をセルに入力して答えを出します。
 


----------------------------------------------------------------------------------------------
・マクロでのエラー処理
 エラーが起きたらマクロを終わらせる。
 Sub  owari()
    ---------
    ---------
    ---------
    On  Error Goto   Owaru
    ---------
    ---------
    ---------
    Owaru:
        Exit Sub


  End Sub