見出し画像

配列問題その１：如何にして0を省略するか

VBA_amateur_writer_hiro

実際に私が毎日どんなVBAを書いているか、ご紹介したいと思います。
ワークシート上のセルを読み込んで、１次元の配列をつくったとします。しかし、それには多数の不要な数値があります。仮に不要な数値は「0」だとしましょう。今回の問題は、０をどうやって省略して必要な数値だけ取り出すか、の問題です。

ワークシート関数で省略すればいい？

VBAを使わずに、ワークシートの関数でこの問題を解決しようとする場合、私はsmallを使っていました。
例えば以下のような考え方です。数値はすべてプラスとします。

  1. セル数値の最大値を求める。

  2. １の最大値より桁数の多い、きりの良い数値を設定する（例えば1,000）。

  3. 行番号と２の数値によりコード化する（セルの数値が50、行番号が12だとしたら、12*1000+50＝12,050）。

  4. ３のコードの最大値を求める。

  5. ４の最大値より桁数の多い、きりの良い数値を設定する（例えば1,000,000）。

  6. もとのセル数値「0」を5の数値に変更する。

  7. 3のコードと6を１列の範囲にまとめる

  8. 7の範囲について、small関数を使い、並べ替えする

  9. mod関数でコードを2の数値を割ったあまりを解とする（ mod(12050,1000)=50 ）。

途中に行番号をかませてコードにすることで、もともと同じ値であっても違う数値として認識しますし、０以外の並び順は変わらないのです。
データ数が少なければ、この方法でも問題ありませんが、データ数が1万、2万となってくると、small関数がうまく動いている感じがしません（限界値はよくわかりません）。
当然ながらデータ数が変わると関数で選択する範囲も変える必要があります（これはoffset関数を使えば解決できますが、常に余分な行を用意しておかないといけないので、行が不足するとエラーになります）。
small関数はちょっと不安なので、vbaで解決できないか、探りました。

関数を使わず配列から0を省略するステップ

モデルとして要素数11の配列をつくります。0が5か所にあり、その他に自然数がはいっています。

配列1　読み込むデータ

さてここからどうするか。私は自然数の部分を１に変換した配列2をつくります。

配列2　自然数を1に変換

次に、配列2を累積値にする配列をつくります。

配列3　配列2の累積値化

ここで11番目の数値の6が自然数がある要素の数だとわかります。

ここで重要なのはあえて配列2でワークシート関数のsumを使わないことです。sumにも限界があります。要素数が6万3000ぐらいになると、sumの値と、配列3による最後の値が違ってくるのです。理由はわかりませんが、私は「エクセルの限界」と読んでいます。

次に配列１×配列3として配列4をつくります。これで、各要素の自然数が何番目にくるかがわかりました。

配列4　配列１×配列3

次に１～6までの配列と、配列4のクロスにより、最終的に１番目にしたい値が配列1で何番目にあるかを計算します。

配列5　元の自然数が何番目にあるかを示す

もともとの位置がわかれば、あとはわかりますね。０を外した部分だけの配列ができました。

求める配列

関数を使わないVBAコード

私がよく使う書き方で書いてみましょう。
配列の名称の付け方は基本的に番号としています。その他のことは最初の投稿　https://note.com/vba_writer_hiro/n/n5e1a3b321824　に書きましたのでご参照ください。いずれは有料にしたいですがnote始めたばかりなので無料で。

前提として別途データを読み取った１次元配列arrがあり、それを代入するかたちのfunction　とします。

function note230312(byval arr as variant)
'定義
dim chk1st() as long,chk2nd() as long,chk3rd() as long,chk4th() as long,chk5th() as long,chk6th() as long,chk7th() as long
dim a as long,dim b as long
dim aa as long,bb as long

'要素数
aa=ubound(arr,1)

'配列1
redim chk1st(1 to aa)
for a=1 to aa
if arr(a)=0 then
chk1st(a)=0
else
chk1st(a)=1
end if
next a

'配列2
redim chk2nd(1 to aa)
for a=1 to aa
if a=1 then
chk2nd(a)=chk1st(a)
else
chk2nd(a)=chk2nd(a-1)+chk1st(a)
end if
next a

bb=chk2nd(aa)

'配列3
redim chk3rd(1 to aa)
for a=1 to aa
chk3rd(a)=chk1st(a)*chk2nd(a)
next a

'配列4
redim chk4th(1 to bb)
for b=1 to bb
redim chk5th(1 to aa)
for a=1 to aa
if b=chk3rd(a) then
chk5th(a)=a
else
chk5th(a)=0
end if
next a
chk4th(b)=application.worksheetfunction.sum(chk5th)
next b

’配列5
redim chk6th(1 to bb)
for b=1 to bb
chk6th(b)=arr(chk4th(b))
next b

note230312=chk6th
end function

いかがでしょうか。配列の数は多いですが、計算内容はいたってシンプル。イメージをなぞっています。途中でsum関数があるではないか、というご指摘もあるかと思いますが、この部分は、配列1～3のあたりを別のfunctionとしておけば、解決できます。

終わりに：モデルをつくって考える

もちろん実際の計算は11個の値ということはありません。それだけならmatch関数＋index関数（またはoffset関数）を使えば解決します。
データ数が増えるとsumも問題ありですが、matchも問題がないわけがありません。エラーが発生すると思いますが、関数自体は正しいのでエラーに気づかないのです。しかし、作業をしていると、おかしな点に気づく。
こういったことを繰り返すと、matchもsumも限界があると受け止めるようになりました。
ですが、すぐにvbaは書けません。やはり、適当な紙にモデルとなる数字を書いて、あーだこーだと変換しながら、考えています。その結果の一つがこのコードです。
誰かのお役にたてれば幸いです。
















この記事が気に入ったらサポートをしてみませんか？