話題 19  カスタム関数を Let関数でつくる

題2で いくつかのカスタム関数を掲載しましたが, 力ずくのカスタム関数となっています(-_-)。 これらをLet関数で書くとスマートに, Scriptを書くように, わかりやすいですね。Let関数というのは まさにこの使い方にうってつけの関数ですね。いままでLet関数の使い方が分からなかったのですが, 遅ればせながら最近 やっと学習しました(+_+)。以下, 話題2のカスタム関数を書き直して再掲載します。ただし 4のカスタム関数はLet関数で書き直す意味がないので, Let関数を使っていません。


1. Intermediate2 (被検文字; 検索文字A ; 検索文字B ; 順序数)

Let (

[

text = 被検文字 ;

word1 = 検索文字A ;

word2 = 検索文字B ;

n = 順序数 ;


position1 = Position ( text ; word1 ; 1 ; n  ) ;

position2 = Position ( text ; word2 ; 1 ; n )

];


Trim ( Middle ( text ; position1 + Length ( word1 ); position2  - position1 - Length ( word1 )))

)


2. Middle_Position(被検文字; 検索文字 ; 順序数)

Let (

[

text = 検索文字 & 被検文字 & 検索文字 ;

word = 検索文字 ;

n = 順序数 ;


position1 = Position ( text ; word ; 1 ; n  ) ;

position2 = Position ( text ; word ; 1 ; n + 1 )

];


Middle ( text ; position1 + Length ( word ); position2 - position1 - Length ( word ))

)


3. Substitute_Day1(A; B)

Let (

[

mw1 = Trim ( MiddleWords ( B ; 1 ; 1 ) );

mw2 = Trim ( MiddleWords ( B ; 2 ; 1 ) );

mw3 = Trim ( MiddleWords ( B ; 3 ; 1 ) );

mw4 = Trim ( MiddleWords ( B ; 4 ; 1 ) );

mw5 = Trim ( MiddleWords ( B ; 5 ; 1 ) );

mw6 = Trim ( MiddleWords ( B ; 6 ; 1 ) );

mw7 = Trim ( MiddleWords ( B ; 7 ; 1 ) );

mw8 = Trim ( MiddleWords ( B ; 8 ; 1 ) );

mw9 = Trim ( MiddleWords ( B ; 9 ; 1 ) );

mw10 = Trim ( MiddleWords ( B ; 10 ; 1 ) );

mw11 = Trim ( MiddleWords ( B ; 11 ; 1 ) );

mw12 = Trim ( MiddleWords ( B ; 12 ; 1 ) );

mw13 = Trim ( MiddleWords ( B ; 13 ; 1 ) );

mw14 = Trim ( MiddleWords ( B ; 14 ; 1 ) );

mw15 = Trim ( MiddleWords ( B ; 15 ; 1 ) );

mw16 = Trim ( MiddleWords ( B ; 16 ; 1 ) );

mw17 = Trim ( MiddleWords ( B ; 17 ; 1 ) );

mw18 = Trim ( MiddleWords ( B ; 18 ; 1 ) );

mw19 = Trim ( MiddleWords ( B ; 19 ; 1 ) );

mw20 = Trim ( MiddleWords ( B ; 20 ; 1 ) );

mw21 = Trim ( MiddleWords ( B ; 21 ; 1 ) );

mw22 = Trim ( MiddleWords ( B ; 22 ; 1 ) );

mw23 = Trim ( MiddleWords ( B ; 23 ; 1 ) );

mw24 = Trim ( MiddleWords ( B ; 24 ; 1 ) );

mw25 = Trim ( MiddleWords ( B ; 25 ; 1 ) );

mw26 = Trim ( MiddleWords ( B ; 26 ; 1 ) );

mw27 = Trim ( MiddleWords ( B ; 27 ; 1 ) );

mw28 = Trim ( MiddleWords ( B ; 28 ; 1 ) );

mw29 = Trim ( MiddleWords ( B ; 29 ; 1 ) );

mw30 = Trim ( MiddleWords ( B ; 30 ; 1 ) );

mw31 = Trim ( MiddleWords ( B ; 31 ; 1 ) )

];


Substitute ( A; [ mw1 ; "1"]; [ mw2 ; "1"]; [ mw3 ; "1"]; [ mw4 ; "1"];[ mw5 ; "1"];[ mw6 ; "1"];[ mw7 ; "1"];[ mw8 ; "1"];[ mw9 ; "1"]; [ mw10 ; "1"];[ mw11 ; "1"]; [ mw12 ; "1"]; [ mw13 ; "1"]; [ mw14 ; "1"];[ mw15 ; "1"];[ mw16 ; "1"];[ mw17 ; "1"];[ mw18 ; "1"];[ mw19 ; "1"];[ mw20 ; "1"];[ mw21 ; "1"]; [ mw22 ; "1"]; [ mw23 ; "1"]; [ mw24 ; "1"];[ mw25 ; "1"];[ mw26 ; "1"];[ mw27 ; "1"];[ mw28 ; "1"];[ mw29 ; "1"];[ mw30 ; "1"];[ mw31 ; "1"])

)


4. Substitute_DayEmp(A)

Substitute ( A ;  ["01"; ""]; ["02"; ""];["03"; ""]; ["04"; ""]; ["05"; ""];["06"; ""];["07"; ""]; ["08"; ""]; ["09"; ""]; ["10"; ""]; ["11"; ""]; ["12"; ""]; ["13"; ""]; ["14"; ""]; ["15"; ""]; ["16"; ""]; ["17"; ""]; ["18"; ""]; ["19"; ""];["20"; ""]; ["21"; ""]; ["22"; ""]; ["23"; ""]; ["24"; ""]; ["25"; ""]; ["26"; ""]; ["27"; ""]; ["28"; ""]; ["29"; ""]; ["30"; ""]; ["31"; ""] )


5.Sum_day31(A)

Let (

[

mw1 = GetAsNumber ( MiddleWords ( Substitute ( A ; "," ; "a " ) ; 1; 1 ) );

mw2 = GetAsNumber ( MiddleWords ( Substitute ( A ; "," ; "a " ) ; 2; 1 ) );

mw3 = GetAsNumber ( MiddleWords ( Substitute ( A ; "," ; "a " ) ; 3; 1 ) );

mw4 = GetAsNumber ( MiddleWords ( Substitute ( A ; "," ; "a " ) ; 4; 1 ) );

mw5 = GetAsNumber ( MiddleWords ( Substitute ( A ; "," ; "a " ) ; 5; 1 ) );

mw6 = GetAsNumber ( MiddleWords ( Substitute ( A ; "," ; "a " ) ; 6; 1 ) );

mw7 = GetAsNumber ( MiddleWords ( Substitute ( A ; "," ; "a " ) ; 7; 1 ) );

mw8 = GetAsNumber ( MiddleWords ( Substitute ( A ; "," ; "a " ) ; 8; 1 ) );

mw9 = GetAsNumber ( MiddleWords ( Substitute ( A ; "," ; "a " ) ; 9; 1 ) );

mw10 = GetAsNumber ( MiddleWords ( Substitute ( A ; "," ; "a " ) ; 10; 1 ) );

mw11 = GetAsNumber ( MiddleWords ( Substitute ( A ; "," ; "a " ) ; 11; 1 ) );

mw12 = GetAsNumber ( MiddleWords ( Substitute ( A ; "," ; "a " ) ; 12; 1 ) );

mw13 = GetAsNumber ( MiddleWords ( Substitute ( A ; "," ; "a " ) ; 13; 1 ) );

mw14 = GetAsNumber ( MiddleWords ( Substitute ( A ; "," ; "a " ) ; 14; 1 ) );

mw15 = GetAsNumber ( MiddleWords ( Substitute ( A ; "," ; "a " ) ; 15; 1 ) );

mw16 = GetAsNumber ( MiddleWords ( Substitute ( A ; "," ; "a " ) ; 16; 1 ) );

mw17 = GetAsNumber ( MiddleWords ( Substitute ( A ; "," ; "a " ) ; 17; 1 ) );

mw18 = GetAsNumber ( MiddleWords ( Substitute ( A ; "," ; "a " ) ; 18; 1 ) );

mw19 = GetAsNumber ( MiddleWords ( Substitute ( A ; "," ; "a " ) ; 19; 1 ) );

mw20 = GetAsNumber ( MiddleWords ( Substitute ( A ; "," ; "a " ) ; 20; 1 ) );

mw21 = GetAsNumber ( MiddleWords ( Substitute ( A ; "," ; "a " ) ; 21; 1 ) );

mw22 = GetAsNumber ( MiddleWords ( Substitute ( A ; "," ; "a " ) ; 22; 1 ) );

mw23 = GetAsNumber ( MiddleWords ( Substitute ( A ; "," ; "a " ) ; 23; 1 ) );

mw24 = GetAsNumber ( MiddleWords ( Substitute ( A ; "," ; "a " ) ; 24; 1 ) );

mw25 = GetAsNumber ( MiddleWords ( Substitute ( A ; "," ; "a " ) ; 25; 1 ) );

mw26 = GetAsNumber ( MiddleWords ( Substitute ( A ; "," ; "a " ) ; 26; 1 ) );

mw27 = GetAsNumber ( MiddleWords ( Substitute ( A ; "," ; "a " ) ; 27; 1 ) );

mw28 = GetAsNumber ( MiddleWords ( Substitute ( A ; "," ; "a " ) ; 28; 1 ) );

mw29 = GetAsNumber ( MiddleWords ( Substitute ( A ; "," ; "a " ) ; 29; 1 ) );

mw30 = GetAsNumber ( MiddleWords ( Substitute ( A ; "," ; "a " ) ; 30; 1 ) );

mw31 = GetAsNumber ( MiddleWords ( Substitute ( A ; "," ; "a " ) ; 31; 1 ) )

];


mw1+mw2 +mw3 +mw4 +mw5 +mw6 +mw7 +mw8 +mw9 +mw10 +mw11+mw12 +mw13 +mw14 +mw15 +mw16 +mw7 +mw18 +mw19 +mw20 +mw21+mw22 +mw23 +mw24 +mw25 +mw26 +mw27 +mw8 +mw29 +mw30 +mw31

)


Sample

上記5題のカスタム関数を収録したSample Fileを掲載しました。Let関数を使ったカスタム関数は名前の先頭に”X”をつけて従来のカスタム関数と区別していますが, 機能は同じなので, “実行ボタン”で 同じ結果が得られますので 区別はつきません。Scriptをごらんください。なお, カスタム関数を変更するには “FM advanced”で操作する必要があります。