Deprecated: The each() function is deprecated. This message will be suppressed on further calls in /home/zhenxiangba/zhenxiangba.com/public_html/phproxy-improved-master/index.php on line 456
109kansu
[go: Go Back, main page]

■1-9　マクロにワークシート関数を組み入れで簡素化できます
 VBAマクロでワークシート関数を使用することにより、プログラムが簡素化されたり、実行が高速になること
があります。著者がワークシート関数を使用した方が便利だと思った処理例から、よく使用したサンプル
(表21参照）を記載しました。 VBAマクロにワークシート関数を組み込んで使用する場合、記述方法などを
参考にしてください。

【表21　ワークシート関数例】
ワークシート関数 内容 補足説明
1 Sum 数値合計 数値の合計を求める場合はこれが早くて簡単
2 Max 最大値を取得 引数として配列あるいはセル範囲を指定した場合、その中の数値のみ
が計算の対象となり、文字列、空白セル、論理値、エラー値は無視
3 Min 最大値を取得
4 Average 平均値の計算 指定したセル参照の文字列、空白セル、論理値は無視。0は計算対象
5
 
 
 Round
 
 
 四捨五入
 
 
 [1]整数の桁数→少数点以下の桁数
[2]桁数に0→最も近い整数へ四捨五入
[3]桁数に負の数字→小数点の左側（整数部）で四捨五入
6
 
 ASC
 
 全角を半角化
 
 StrConv("全角", vbNarrow)関数を使用するより、こちらの方がが5倍
以上高速だった。ただしヴァ・ヴィ・ヴは半角にならない欠点があった
7
 
 Substitute
 
 文字の置換え
 
 Replaceメソッドよりこちらが10倍以上早かった。
また、？・*等のワイルドカ−ド記号の置換えはこれで出来ます
8
 
 VLookup
 
 表データ検索
 
 表にあるデータから特定文字を検索し、選ばれた行から指定した列の
文字を一瞬に取得できる便利な関数です


(1)Sum（指定したセルの合計）の使用例
ワークシート関数「Sum」は,表計算では必ず使用する関数で引数リストに含まれる数値の合計を計算します。
VBAで利用できますがVBAステートメントでは、「Application.WorksheetFunction.関数(引数)」のように記述。

画面62　ワークシート関数「Sum」の使用例

Sub シート関数1()
Dim endr As Integer, myrang As Range, kei As Long
    endr = ActiveCell.SpecialCells(xlLastCell).Row
 
        Set myrang = Range(Cells(2, 4), Cells(endr, 4))
    kei = Application.WorksheetFunction.Sum(myrang)
    MsgBox "D列の合計=" & kei
 
End Sub


【参考191】ワークシート関数記述のとき省略できる文字
 WorksheetFunctionを省略して、Application.Sum(myrang)の記述でも正常に機能して問題ありません。
ただし、マクロ作成時WorksheetFunctionと入力すると、インテリセンス（自動入力補完機能）が働き、
BVAで使用できるワークシート関数が表示されるので通常は記述の方がマクロ化が楽になります。
なお、逆にApplicationを省略して、WorksheetFunction.Sum(myrang)の記述でも問題ありません。



(2)データベースから最大値を取得
ワークシート関数「Max」は引数に含まれる最大の数値を返します。
本例では引数としてC列〜F列のデータを指定した例です（実行例画面63参照）。

画面63　ワークシート関数「Max」の使用例


Sub シート関数2()
Dim endr As Integer, myrang As Range, maxd As Integer
    endr = Range("B1000").End(xlUp).Row
 
    Set myrang = Range(Cells(3, 3), Cells(endr, 6))
        maxd = Application.WorksheetFunction.Max(myrang)
    MsgBox "最大値=" & maxd
End Sub


【参考192】　AVBでセルへ書き込み例
 VBAで全てのワークシート関数を使用できるわけではありません。VABで使用できない関数は、通常の使用
と同じようにセルへ「＝・・・」で記述して使用します。本例はVBAでセルへ記入する方法のサンプルです。

Sub シート関数2a()
Dim endr As Integer
    endr = Range("F1000").End(xlUp).Row
 
    Range("J1").Formula = "=MAX(F1:F" & endr & ")"
    MsgBox "終値の最大値=" & Cells(1, 10)
End Sub



(3)データベースから最小値を取得
ワークシート関数「Min」は引数に含まれる最小の数値を返します。
本例では引数としてF列のデータを指定した例です（実行例画面64参照）。

画面64　ワークシート関数「Min」の使用例


Sub シート関数3()
Dim endr As Integer, myrang As Range, mind As Integer
    endr = Range("B1000").End(xlUp).Row
 
    Set myrang = Range(Cells(3, 6), Cells(endr, 6))
    mind = Application.WorksheetFunction.Min(myrang)
    MsgBox "終値の最小値=" & mind
End Sub



(4)データの移動平均
ワークシート関数「Average」は引数の平均値を計算します。
平均を求めるとき、空白セルは計算に入りませんが、値が0であるセルは計算の対象となる点に注意して
ください（実行例画面65参照）。

画面65　ワークシート関数「Average」の使用例


Sub シート関数4()
Dim endr As Integer
    endr = Range("F1000").End(xlUp).Row
 
Range(Cells(3, 7), Cells(endr, 7)).Formula = "=Average(F3:F7)"
Cells(2, 7).Value = "5日移動平均"
 
End Sub



(5)データの四捨五入

[1]千の位迄表示のマクロ例（別の列へ表示）
ワークシート関数「Round」は四捨五入して指定した桁数にします。
本例では、B列の「123598」が→C列では「124000」のようになります。ワークシート関数の計算式ををC列
に記入したので、更に処理後の結果を元のB列へ戻す処理が必要で少し面倒だが高速化を考えた場合は、
ワークシート関数「Round」の使用が望ましい（実行例画面66参照）。

画面66　B列のデータをC列へ千の位迄の表示例


Sub シート関数5()
Dim endr As Integer, myrang As Range
    endr = Range("B1000").End(xlUp).Row
 
    Set myrang = Range(Cells(3, 3), Cells(endr, 3))
    myrang.Formula = "=Round(B3, -3)"
End Sub



[2]千の位迄表示のマクロ例（同一列へ表示）
マニュアルで操作する場合は前(5)-1のように別セルに計算式を記入して使用していましたが、VBAで操作
する場合は同じセルにワークシート関数の計算式を記入で問題ありません（実行例画面67参照）。
（本例は問題ありませんが、自分を参照する計算式はエラー発生の原因になることが多いので注意）

画面67　B列のデータを千の位迄の表示に変換例


Sub シート関数5a()
Dim endr As Integer, myrang As Range
    endr = Range("B1000").End(xlUp).Row
 
    Set myrang = Range(Cells(3, 2), Cells(endr, 2))
    myrang.Formula = Application.Round(myrang, -3)
End Sub



[3]Round関数の引数(桁数)指定例
以下マクロで引数(桁数)の指定を変えた場合の結果を確認できます。
Sub シート関数5b()
四捨五入して指定した桁数に
    MsgBox Application.Round(123.45678, 2) ' →　123.46
数値をもとも近い整数に切り上げ(切り上げ最小単位に1を指定例）
    MsgBox Application.Ceiling(123.145678, 1)  ' →　124
数値を切捨てゼロに近い値にする(切捨て最小単位に1を指定例）
    MsgBox Application.Floor(123.95678, 1) ' →　123
数値を指定した桁へ切捨て(桁数に3指定で小数点以下3桁例）
    MsgBox Application.RoundDown(123.45678, 3)  ' →　123.456
数値を指定した桁へ切捨て(桁数に3指定で小数点以下3桁例）
    MsgBox Application.RoundUp(123.45678, 3)  ' →　123.457
End Sub



(6) ASC関数で半角文字に統一
検索処理などでは文字の形式が同じでないと思ったようにヒットしない問題が発生します。
本例はワークシート関数「ASC」で全角のカタカナを半角のカタカナに統一した例です（実行例は画面68参照）。

画面68全角のカタカナを半角のカタカナに統一例


Sub シート関数6()
Dim endr As Integer, myrang As Range
    endr = Range("B1000").End(xlUp).Row
    Set myrang = Range(Cells(2, 4), Cells(endr, 4))
    myrang.Formula = "=ASC(B2)"
End Sub



(7)Substitute関数で文字置き換例

[1]カタカナバー「-」→「ｰ」に統一
検索処理などでは文字の形式が同じでないと思ったようにヒットしない問題が発生します。
本例はワークシート関数「Substitute」でカタカナバー「−」→「ー」に統一した例です（実行例は画面69参照）。

画面69カタカナバー「−」→「ー」に統一例


Sub シート関数7()
Dim endr As Integer, myrang As Range
    endr = Range("B1000").End(xlUp).Row
 
    Set myrang = Range(Cells(2, 4), Cells(endr, 4))
    myrang.Formula = "=
Substitute(B2,""−"",""ー"")"
End Sub



[2]ワイルドカード文字「？」を削除例
Webページからデータをダウンロードしたとき、余分な文字として「？」が付いていることがあります。
ワイルドカード文字「?」や「*」の削除は普通の文字のと同じように「置換」操作で単純に削除することは
できません。本例はワークシート関数「Substitute」で「？」を削除した例です。
（実行例は画面70参照）。
画面70　「？」を削除例


Sub シート関数8()
Dim endr As Integer, myrang As Range
    endr = Range("A1000").End(xlUp).Row
 
For i = 1 To endr
 Cells(i, 1) = Application.WorksheetFunction.Substitute(Cells(i, 1), "?", "")
Next
End Sub

ASCコードで文字表示ができないコードは「？」表示になることがあります。
その場合は「Substitute(Cells(i, 1), "?", "")」または、「Substitute(Cells(i, 1), Chr(63), "")」、
[16進では：Chr(&h3f)]で？を削除できないことがあります。削除できない？はChrW(○○)の
Unicode番号指定で削除できる場合ああります。
（Unicode番号取得は1-5節「【参考】Unicodeの文字のコード取得方法」を参照してください）。




(8)Vlookupで表データ検索
(8-1)指定した範囲のデータから、検索値を検索しその値を含む行から、指定した列のデータを返す。
下記の例は株取引でよく参考にする通称「板」言われるデータ表ですは、注文株価に対する注文株数
を見ることができます。下図の例では412円で買い注文を出した場合、注文株数は9200株の注文が
入っており、1000株注文したとすると注文株数は10200株になります。その10200株の消化状況トレース
に、Vlookup関数が便利だった。下記図では412円は11行に表示されているが、11行に固定ではなく
移動するので、412円を追いかけてその注文株数を把握します。Vlookupで検索値412を指定しておけば、
Vlookupのステータスが実行されるたびに注文株数が返っています。




Sub シート関数9()
[1]ExceVBA関数として、変数「dd1」へ代入した例
dd1 = Application.WorksheetFunction.VLookup(Range("C20"), Range("C4:D19"), 2, False)
 
[2]ExceVBA関数として、引数範囲は設定名で変数「dd2」へ代入した例
dd2 = Application.WorksheetFunction.VLookup(Range("C20"), " 板", 2, False)
 
 
[3]マクロで、C25セルへVLOOKUP関数を設定した例
Cells(25, 3).Formula = "=VLOOKUP($C$20,$C$4:$D$19,2,FALSE)"
 
[4]上記と同じですが、引数範囲は設定名で指定した例
Cells(24, 3).Formula = "=VLOOKUP($C$20,板,2,FALSE)"
 
End Sub

(8-2)A列のコード番号からB列の銘柄を抽出例




Sub シート関数9()
cencbo = Cells(10000, 1).End(xlUp).Row
 
On Error Resume Next
meino = Application.WorksheetFunction.VLookup(Range("A1"), _
Range(Cells(3, 1), Cells(cencbo, 2)), 2, False)
On Error GoTo 0
 
MsgBox "検索したコード[" & Cells(1, 1) & "]の銘柄名は「" & meino & "」です。"
End Sub



【戻る】　　　　【Top画面】　　　【HPへ】