えくせるちゅんちゅん

ことりがエクセルをちゅんちゅんするブログ

MENU
トップ > Excel > 1から100の偶数の和を求めるワンライナー作成の軌跡

1から100の偶数の和を求めるワンライナー作成の軌跡

Excel Excel関数 VBA

先日Twitterで話題になった「1から100の偶数の和を求めるワンライナー」に関して、VBA作成までに思考したことを整理しておきます。

f:id:Kotori-ChunChun:20190817060426p:plain

きっかけ

元ネタはこちらです。未読の方は先にご覧ください。

qiita.com


出題

まにゃpy@Python解説の猫 (@uuyr112)

いきなり問題！

ここまでのツイートを手がかりに

Pythonのfor文を使って1から100までの間の偶数の和を計算するソースコードを書いてみて下さい。

発端

1から100までの偶数の和もPythonならたったの1行で書ける

result =sum(list([i for i in range(1,101) if i %2 == 0]))

（sumは組み込み関数なのでimportなしで使えます。）

とてもシンプルですね。Pythonなら複雑なコードもシンプルに。

そう、Pythonならね


私の作ったワンライナー

様々なアプローチが考えられますが、プログラムを考えるに当たって自身に次の条件を課しました。（というか出題するからには、こういう課題なんだろうなと解釈）


条件

  • 1～100の要素すべてに対して偶数判定を行うこと
  • マルチステートメント（コロンで繋ぐ）を使わないこと
  • VBAの文法のみで完結させること
  • （できれば）要素が1～99や2～100への変更に対応できること


NG

  • 数学的に式を最適化したもの
  • 下準備する要素を半分の1～50に減らして、合計値を倍にしたもの
  • どんなプログラミング言語でも同様に書ける、単なる繰り返しと分岐と加算するだけのもの
  • 繰り返しの増分を+2にして、最初から偶数しか出現しないもの


私が作ったVBAは以下のとおり。

?Evaluate(Join(Filter([TRANSPOSE(ROW(1:100))/2], ".", False), "+")) * 2

最終的には「1～100の配列を生成する」部分が、VBAの基本文法で再現できなくてExcel VBA固有の書き方に頼ってしまう結果となりました。


解説

連番の配列を作成する

連番の配列とは、要するに次のようなプログラムである。

Dim arr(1 To 100)
For i = LBound(arr) To UBound(arr)
    arr(i) = i
Next

残念ながら、VBAには連番を作成してくれるような、便利な関数は存在しなかった。

そりゃあ、マルチステートメントを使えば一行でも書けるが、これは何か違う気がする。

Dim arr(1 To 100): For i = LBound(arr) To UBound(arr): arr(i) = i: Next

だが、後述する方法により、連番の配列を作ることに成功した。


話は変わるが、Excelの数式において連番の配列を作るテクニックとして次のような数式が存在する。

{=ROW(1:100)}

通常のROW関数は、指定したセル範囲の先頭の行番号を返すだけの関数だが、配列数式とすることで指定したセル範囲それぞれの行番号の配列を返すようになるのである。

=ROW(A1)
1
=ROW(1:100)
1
{=ROW(1:100)}
{1,2,3,...,100}

詳しい仕組みについては、いつもの高度な数式以下略を参照。

www.excel-chunchun.com

この式をVBAに組み込む事ができれば、連番の配列が作れるはずである。


VBAに話を戻して、Excel VBA限定だが数式を計算させるための、Evaluateという便利な関数がある。

Application.Evaluate メソッド (Excel)

Evaluate関数を使えば、VBAで作成した文字列の数式を計算させることができる。

?Evaluate("1+2+3+4+5")
15

もちろん、数式にはExcel関数を混ぜ込むこともできる。

?Evaluate("SUMPRODUCT(ROW(1:100))")
5050

計算結果を配列として返すこともできる。（Evaluateの評価は配列＞非配列）

arr = Evaluate("ROW(1:100)")

Evaluate関数の代わりに、角カッコを用いた特殊な記法で書くことができる。

arr = [ROW(1:100)]

ちなみに、これらはVBAの標準的な記法で書いた以下の例と全く同じ結果になる。

arr = Range("A1:A100").Value

これで「連番の配列」が出来たかのように見える。

ところが、このままではちょっと問題がある。

単一列多数行のセル範囲を変数へ読み込んだ時に一次元配列になってほしいのだが、Excel VBAでRange.ValueをVariant変数に代入すると以下のような二次元配列となる。

式 値 型
arr Variant/Variant(1 to 100,1 to 1)
arr(1) Variant(1 to 1)
arr(1,1) 1 Variant/Double
arr(2) Variant(1 to 1)
arr(2,1) 1 Variant/Double
arr(3) Variant(1 to 1)
arr(3,1) 1 Variant/Double
・・・
arr(100) Variant(1 to 1)
arr(100,1) 1 Variant/Double

実は、これが後述のFilter関数に支障が出るため、無視できない問題となった。

Evaluateの記法による両者の違いは以下の通り。

Evaluate 角カッコ
例 "ROW(1:100)" ROW(1:100)
記法 文字列 数式そのまま
メリット 汎用性がある 構文が短い
開始値をnとして変数化した例 "ROW("&n&":"&(99+n)&")" ×

あとEvaluateには

  • 式はA1形式
  • 式の長さは255文字まで
  • 戻り値は数式次第。参照(Range)の時もあれば値のときもある。
  • シートオブジェクト未指定時は、ActiveSheetが省略されているものとして動く。

といった注意があり、特に255文字制限がボトルネックとなることが多い。


前述した問題：単一列多数行のセル範囲を一次元配列として変数に格納したい場合は、Transpose関数を使うのが一番簡単な手法である。※TRANSPOSE関数にも厄介な制限があるが、今回は弊害とならないので無視する。

Transpose関数は、WorksheetFunction.Transpose()としても、Evaluateの中に組み込んでも特に大差は無い。

arr = WorksheetFunction.Transpose([ROW(1:100)])
arr = [TRANSPOSE(ROW(1:100))]

これで扱いやすい一次元配列になった。

式 値 型
arr Variant/Variant(1 to 100)
arr(1) 1 Variant/Double
arr(2) 1 Variant/Double
arr(3) 1 Variant/Double
・・・
arr(100) 1 Variant/Double


今回はROW関数を使って連番の配列を作成したが、COLUMN関数を使うことでTRANSPOSEを使わずに済ませることもできる。

arr = [COLUMN(A:CV)]

これにより、一発で一次元配列が出来ることを確認した。

Variant/Variant(1 to 100)

しかし、100→CV、CV→100と頭の中で一発で置き換えられるような人はいないと思う。

従って、ロスは大きくなるが、可読性を担保するためにROWで求めた結果をTransposeしたほうが良いと私は考える。


もし、COLUMN()でA:CVではなく、1:100を指定するとどうなるのかも書いておく。

arr = [COLUMN(1:100)]

結果は失敗。右端の列（16384 = XFD）という結果になった。

Variant/Variant(1 to 16384)

原因はやはり1:100の部分で[COLUMN(1:100)]を直訳すると、「1行目から100行目までを選択して、最初の列から最後の列までの列番号を返せ」である。

1:100は行指定なので、実は[COLUMN(A1:XFD100)]という意味だったりするのだ。


配列から奇数を取り除いて偶数だけにする

偶数と奇数を判定する方法で最も一般的なのは、2で割ったときの余りを見る方法だろう。

For i = 1 To 100
    Debug.Print IIf(i Mod 2 = 0, "偶数です", "奇数です")
Next

偶数だけを合計したいなら、奇数を0に置き換えてしまえば良い話なのでこうなる。

For i = 1 To 100
    Debug.Print IIf(i Mod 2 = 0, i, 0)
Next

ただしIIFは低速である。i Mod 2の結果が奇数の時1、偶数の時0なので、VBAの仕様を逆手に取って偶数の時のTrue = -1、False = 0を乗算することで高速化できる。

For i = 1 To 100
    Debug.Print i * Abs((i Mod 2) = 0)
Next


もう一つの偶数奇数判定の方法が、２進数として見た時に1ビット目が0かどうかを見る方法だ。

奇数だけを生かすならi * (i And 1)で済むのだけれど、偶数を生かすとなるとi * Abs((i And 1) = 0)になるのが微妙。もう少しなんとかなりそうな気がするのだが・・・。

For i = 1 To 100
    Debug.Print i * Abs((i And 1) = 0)
Next
10進数 2進数 i And 1
1 0000 0001 1
2 0000 0010 0
3 0000 0011 1
4 0000 0100 0
5 0000 0101 1
・・・
96 0110 0000 0
97 0110 0001 1
98 0110 0010 0
99 0110 0011 1
100 0110 0100 0

基本的に割り算の余りよりもビット演算のほうが高速なので、たぶんこれが一番計算が早いと思われる。


Excel関数においては、奇数偶数判定の関数が用意されている。

もちろんMOD関数で余りを求める方法もある。

数式 概要
MOD(数値,除数) 数値を除数で割ったときの余りを返す
ISODD(数値) 数値が奇数の時True、違う時False
ISEVEN(数値) 数値が偶数の時True、違う時False


ワンライナー作成における最大の課題は、上記のような計算を如何にして配列に対して行うかである。

だが、VBAには上記に合致するような都合の良い関数は「無い」


そこで、私は文字列配列から絞り込みを行う事のできる「Filter関数」に注目した。

https://twitter.com/KotorinChunChun/status/1161626339901726720?s=20

Filter関数

構文

Filter(sourcearray, match, [ include, [ compare ]])

引数

sourcearray 必須です。 検索する文字列の 1 次元配列。

match 必須。 検索する文字列。

include 省略可能。 返すサブ文字列に match を含めるか、除外するかを示す Boolean 値。 include が True の場合、Filter は、match が含まれる配列のサブセットをサブ文字列として返します。 include が False の場合、Filter は、match が含まれない配列のサブセットをサブ文字列として返します。

compare 省略可能。 使用する文字列比較の種類を示す数値。 値については、「設定」セクションを参照してください。

本来、Filter関数は文字列処理用の関数であるが、実はsourcearrayは数値配列でも文字列として処理出来ることを経験則から知っていた。

配列の要素に偶数のみに存在する何らかの法則を持たせることで、奇数を取り除くことが出来るのではないかと考えたわけである。


まず、最初に考えたのが、配列全体の値を5倍して末尾が0なら偶数。5なら奇数と判断する方法だ。

Sub q3_3()
    Dim a, b
    a = [TRANSPOSE(ROW(1:100))*5]
'    a = Array(5, 10, 15, 20, 95, 100, 105, 110, 485, 490, 495, 500)
    b = Filter(a, "0")
    '配列を合計
    '結果を/5
End Sub

結果は見事に失敗した。

数値 5倍後 残す 結果
1 5 × ×
2 10 ○ ○
3 15 × ×
4 20 ○ ○
19 95 × ×
20 100 ○ ○
21 105 × ○←異常値
22 110 ○ ○
97 485 × ×
98 490 ○ ○
99 495 × ×
100 500 ○ ○

構築時に気がついたが、Filter関数は「部分一致検索」と「完全一致検索」は出来るが、「後方一致検索」が出来ないのである。

従って、途中で「0」の出現する105などを誤って抽出してしまう問題が発生したため廃案となった。


諦めるな！！！まだ可能性はあるはずだ！！！　でもその前にお風呂休憩…


そして、値を半分にして小数点があるものが奇数だ！と「ひらめく」ことに成功。（こういう当たり前なことでも、自分で気がつくのは中々難しい）

Sub q3_4()
    Dim a, b
'    a = [TRANSPOSE(ROW(1:100))/2]
    a = Array(0.5, 1, 1.5, 2, 9.5, 10, 10.5, 11, 48.5, 49, 49.5, 50)
    b = Filter(a, ".", False)
    '配列を合計
    '結果を2倍
End Sub

このアイディアは正解だった。

数値 0.5倍後 残す 結果 出力値
1 0.5 × × -
2 1 ○ ○ 1
3 1.5 × × -
4 2 ○ ○ 2
19 9.5 × × -
20 10 ○ ○ 10
21 10.5 × × -
22 11 ○ ○ 11
97 48.5 × × -
98 49 ○ ○ 49
99 49.5 × × -
100 50 ○ ○ 50

あとは合計してから二倍にするだけである。


配列の合計を求める

実はVBAには合計を求める関数がない。


ただし、Excel VBAにおいてはWorksheetFunction.Sum()が使えるので、簡単に合計を求める事が可能だったりする。

?WorksheetFunction.Sum([TRANSPOSE(ROW(1:100))])
 5050


ちなみに、SUMIFが使えれば奇数偶数判定と同時にやることも出来そうな気がしたが、「無理だった」とだけ書き残しておく。

SUMIFやSUMIFSは第一引数が「配列参照」限定なので、VBAからは扱いにくいのが欠点だ。

なんで配列（参照・値・定数なんでも可）にしなかったんだろう？


さて、再び問題だ。

Filter関数を通したことで、配列の要素がDoubleからStringに変わってしまった。

SUM関数はワークシート上とVBAは同じように文字列を無視する仕様のため、合計することはできない。

Sub q4_1()
    Dim arr
    arr = Array("5", "10", "20", "15", 1)
    Debug.Print WorksheetFunction.Sum(arr)
End Sub

1


なお、Evaluate("数式")は式を文字列で表現しなければならないので、VBAの配列変数を指定するのは不可能。

Sub q4_4()
    Dim arr
    arr = Array("2", "4", "6", "8", "10")
    Debug.Print Evaluate("SUM(" & arr & ")")
    '型が一致しません。
End Sub


じゃあどうすんねん！という話だが、実は既に解決策を既に例示している。

文字列の数式を作れば良いのである

?Evaluate("1+2+3+4+5")

配列から足し算の数式への変形は、Join関数を使えば簡単にできる。

Sub q4_2()
    Dim arr
    arr = Array("2", "4", "6", "8", "10")
    Debug.Print Join(arr, "+")
End Sub
2+4+6+8+10

ここまでのプログラムを繋ぐとこうなる

?Join(Filter([TRANSPOSE(ROW(1:100))/2], ".", False), "+")

1+2+3+4+5+6+7+8+9+10+11+12+13+14+15+16+17+18+19+20+21+22+23+24+25+26+27+28+29+30+31+32+33+34+35+36+37+38+39+40+41+42+43+44+45+46+47+48+49+50
'計140 文字

ただ、この方法には欠点があって、Evaluateの限界が255文字と非常に短いため、要素数が少し増えると対応できなくなる。

今回は1～100ということで無事収まったが、もしオーバーした場合には使えないので、使用するのは止めたほうが良いような気がする。


他の例

配列から計算

要素ごとに奇数は0、偶数は1を乗算してからSUMPRODUCTで合計

H氏の式

=SUMPRODUCT(ROW(A1:A100)*ISEVEN(ROW(A1:A100)))

これをEvaluateすれば良いので、

?[SUMPRODUCT(ROW(A1:A100)*ISEVEN(ROW(A1:A100)))]


行列計算

2～101を2で割ったときの余りの列と1～100の行をMMULTで計算

=MMULT(MOD(COLUMN(B1:CW1),2),ROW(A1:A100))

これをEvaluateすれば良いのですが、戻り値が配列となるため(1)を付けて、

?Evaluate("MMULT(MOD(COLUMN(B1:CW1),2),ROW(A1:A100))")(1)

この時、角カッコを使った記法は使えない。

?[MMULT(MOD(COLUMN(B1:CW1),2),ROW(A1:A100))](1)
コンパイル エラー:
引数の数が一致していません。または不正なプロパティを指定しています。

丸括弧で覆ってもダメ。

?([MMULT(MOD(COLUMN(B1:CW1),2),ROW(A1:A100))])(1)
実行時エラー '13':
型が一致しません。

なぜだか分からないが、そういう仕様みたい。

一旦変数に入れさえすれば問題なく出来るんですけどね。


最適化済の式

'∴S=Σ2n＝2×｛0.5n(n+1)｝=n(n+1)
?50 * (50 + 1)


For文による正攻法

Dim i, j: For i = 2 To 100 Step 2: j = j + i: Next:?i

Dim i, j: For i = 1 To 100: j = IIf(i Mod 2 = 0, j + i, j): Next:?i


Dim i, j: For i = 1 To 100: j = j + -(i * ((i And 1) = 0)): Next:?i

Dim r, v: For Each v In [ROW(1:100)]: r = r + -(v * ((v And 1) = 0)): Next:?r


s=0:for i=1 to 100:s=s+iif(i mod 2,0,i):next:?s

s=0:for i=1 to 100:s=s-(i mod 2=0)*i:next:?s


参考資料

発端の記事

qiita.com


連番の作成や1～100の配列作成において、こちらの記事をよく参考にしています。

qiita.com


まとめ

本当は他にも色々なアイディアを考えては捨て、考えては試しをしているのだが、ちゃんと記憶に残っているものだけを抜粋して書いたのでこのような結果になった。

ワンライナーは素早くイミディエイトで実行したい時には便利だが、今回のように構築に膨大な時間を掛けるのは本末転倒である。

プログラム中で無理にワンライナーを実装しても、デバッグがしづらくなるだけなので、私はあまりしないようにしている。

VBAで出来る限界への挑戦というのは大変勉強になるので、今後も挑戦していけると良いな。

以上

そうそう、ワンライナーといえば、イミディエイトウィンドウのありとあらゆる使い方を以前執筆しているので、暇な人は読んでみてね！

www.excel-chunchun.com

何か御座いましたらコメント欄、またはTwitterからどうぞ♪

それではまた来週♪　ちゅんちゅん(・8・)

プライバシーポリシー