Ｍｙ関数の作成演習問題集

Size: px

Start display at page:

Download "Ｍｙ関数の作成演習問題集"

しょうぶはしかわ
5 years ago
Views:

1 Excel Sum,Average,Max 330 BMI Excel My Excel VBA Visual BASIC Editor AltF11 Visual BASIC Editor My Function Function -1-

2 Function ( As Single, As Single) As Double Function Funciton Funciton As Single As Double Function =* My Function 1, 1, 1, Excel My (F) (N) OK -2-

3 My () Function en_menseki( As Single) As Double en_menseki = * ^ 2 Function daikei_menseki( As Single, As Single, As Single) As Double daikei_menseki = ( + ) * / Msgbox 0~100 Function yuryoka_hantei(tokuten As Single) As String Dim hantei As String Select Case tokuten Case Is < 0, Is > 100 MsgBox ("100 ") hantei = "" Case Is >= 80 hantei = "" Case Is >= 70 hantei = "" Case Is >= 60 hantei = "" Case Else hantei = "" End Select yuryoka_hantei = hantei BMI (kg)(m)

4 Function bmi_taikei_hantei(taiju As Single, sincho As Single) As String ' taiju kg sincho Dim bmi As Single Dim hantei As String bmi = taiju / sincho ^ 2 If bmi < 18.5 Then hantei = "" ElseIf bmi < 25 Then hantei = "" ElseIf bmi < 30 Then hantei = "" Else hantei = "" End If bmi_taikei_hantei = hantei 2 =0 Function nijihouteisiki_kai(a As Single, b As Single, c As Single) As String Dim d As Single Dim ans0, ans1, ans2 As String d = b ^ 2-4 * a * c ' Select Case d Case Is > 0 ans0 = "" ans1 = (-b + Sqr(d)) /(2* a) ans2 = (-b- Sqr(d)) /(2* a) Case Is = 0 ans0 = "" ans1 = (-b + Sqr(d)) /(2* a) ans2 = ans1 Case Else ans0 = "" ans1 = -b/(2* a) &"+i"& Sqr(-d) /(2* a) ans2 = -b/(2* a) &"-i"& Sqr(-d) /(2* a) End Select nijihouteisiki_kai = ans0 & "..." & ans1 & "..." & ans2-4-

5 My 1) 2) Function dollar_en_kanzan(dollar As Long, doru_no_nedan As Integer) As Long dollar_en_kanzan = dollar * doru_no_nedan Function dollar_en_kanzan2(dollar As Long, doru_no_nedan As Integer) As Long dollar_en_kanzan = dollar * doru_no_nedan BMI Function bmi_taikei_hantei_bmi _ (taiju As Single, sincho As Single, BMI As Boolean) As Variant ' taiju kg sincho Dim bmi As Single Dim hantei As String bmi = taiju / sincho ^ 2 If BMI = True Then hantei = bmi Else If bmi < 18.5 Then hantei = "" ElseIf bmi < 25 Then hantei = "" ElseIf bmi < 30 Then hantei = "" Else -5-

6 hantei = "" End If End If bmi_taikei_hantei_bmi = hantei Function en_menseki_taiseki_enshucho(r As Single, sentaku As Integer) As Double Dim dumy As Double 'sentaku = InputBox("1: 2: 3:", "", 1) ' Select Case sentaku Case 1 dumy = * r ^ 2 Case 2 dumy = (4 / 3) * * r ^ 3 Case Else dumy = 2 * * r End Select en_menseki_taiseki_enshucho = dumy 1) 2) 2) 4) 0) hannimy 1).count 2)3) VBA SumAverage WoorksheetFunction.sum()WoorksheetFunction.Average() 4) mod Sumif Sumif MOD -6-

7 =mod3 Sumif sum My Function data_kosu( As Range) As Integer data_kosu =.Count Function data_goukei( As Range) As Long data_goukei = WorksheetFunction.Sum() 'data_goukei =.Sum 'count sum Function data_heikin( As Range) As Single data_heikin = WorksheetFunction.Average() Function data_sumif( As Range, As String, As Range) As Single data_sumif = WorksheetFunction.SumIf(,, ) My 110() 2Average,StDevP 3=10*(C77-AVERAGE($D$77:$D$86))/STDEVP($D$77:$D$86)+50-7-

8 Function hensati( As Range, As Single) As Single Dim heikin As Single Dim sigma As Single heikin = WorksheetFunction.Average() sigma = WorksheetFunction.StDevP() hensati = 10 * ( - heikin) / sigma + 50 Sumif % Function engeru_keisu( As Range, As Variant, As Range) As Single engeru_keisu = WorksheetFunction.SumIf(,, ) / _ WorksheetFunction.Sum() () Ctrl+C,V Module1 My Module1-8-

9 Module1 My My.xls My data_goukei() Test.xls My.xls Test.xls My =My.xls!data_goukei(B13:D13) x Ctrl+C,V Function Ctrl+C Ctrl+V Function Ctrl+C,V -9-

10 My.xls My My Visual BASIC Editor My My My My.bas.bas My.bas.xla *.xla toyamykansutest.xla toyamykansutest.xla AddIns -10-

11 Excel toyamykansutest.xla Visual BASIC Editor VBAProjecttoyamykansutest.xla ProjectExcel -11-

12 T=5/9 F32 (DI) = 0.81T U(0.99T ) T :() U :(%) R RLC kg

B 5 (2) VBA R / B 5 ( ) / 34

B 5 (2) VBA R / B 5 ( ) / 34 B 5 (2) VBAR / B 5 (2014 11 17 ) / 34 VBA VBA (Visual Basic for Applications) Visual Basic VBAVisual Basic Visual BasicC B 5 (2014 11 17 ) 1 / 34 VBA 2 Excel.xlsm 01 Sub test() 02 Dim tmp As Double 03