中途半端に規則的な数列

中途半端に規則的な循環数列を簡単に実現する

Excelで座席表なんかを作る場合を想定。

表示用シートに

こんな枠を作っておいて、VLOOKUPなんかで

こういったデータ用シートから値を引っ張ってくる。

各座席の左上のセルに番号を入力したら、必要なデータが残りの３セルに返るようにしてある。

番号を全部手入力するなら、これで十分だが、たとえば

番号をシャッフルして番号入力用セルに自動で入力させたいなあ！

という欲求がしばしば湧き上がってくる。

そうすると、番号入力用セルの位置を一般化する必要がある。

行番号の割り出しは簡単

上の画像の場合だと、行番号の割り出しは簡単だ。

「3,6,9,12,15」というだけなので、単なる「初項3、公差3の等差数列」に過ぎない。たとえば、「ret = (i - 1) * 3 + 3」とでも書けばオッケー。

このぐらいなら、特にコメントを残しておかなくても見たらわかるレベルだろう。

列番号の割り出しは結構めんどくさい

問題は列番号の方だ。

上の画像の場合だと、「2,4,7,9,12,14」を繰り返す必要がある。

頭のいい人だと一般項がパッとわかったりするのだろうけど、凡人の身にはなかなかむつかしいし、仮に頑張って一般化したところで、後で見たときに「これ、何がしたかったんだっけ？」となること必定。

定数と配列を使う

で、次のようなアイディアを考えた。

ひとまずコードをご覧に入れよう。

座席表の枠を作成したシートのモジュールに次のコードを書いた。

リスト１　シートモジュール

Option Explicit

Private Const COLUMN_NUMBERS As String = _
                "2 4 7 9 12 14"    '……(1)'
Private Const MAX_NUMBER As Long = 30

Private Function getColumnNumber( _
             ByVal number As Long) As Long
  Dim ret As Long
  ret = -1
  If number < 1 Or _
     number > MAX_NUMBER Then GoTo Finalizer  '……(2)'
  On Error GoTo Finalizer
  Dim ar() As String    '……(3)'
  ar = Split(COLUMN_NUMBERS)
  Dim columnsCount As Long
  columnsCount = UBound(ar) + 1  '……(4)'
  Dim targetIndex As Long
  targetIndex = (number - 1) Mod columnsCount  '……(5)'
  ret = CLng(ar(targetIndex))  '……(6)'
Finalizer:
  getColumnNumber = ret
End Function

まず、(1)の

Private Const COLUMN_NUMBERS As String = _
                "2 4 7 9 12 14"

で、欲しい数列を文字列としてジカ書きして定数にしておく。

Splitでバラすために半角スペースで区切っている。こうしておくとSplitの第２引数を省略することができるので便利。

まあ、身も蓋もないやり方ｗ

(2)の

If number < 1 Or _
   number > MAX_NUMBER Then GoTo Finalizer

はガード節。

座席表は30席まで対応なので、対象外の数字が渡されたら抜ける。冒頭でretに-1を代入しているので、対象外の数字が渡されたときはあり得ない数字が返るしくみ。

(3)の

Dim ar() As String
ar = Split(COLUMN_NUMBERS)

で、定数にした「"2 4 7 9 12 14"」をバラして配列にし、arにぶち込む。

後は、配列から必要な要素を取り出すだけなのだが、その前に(4)の

columnsCount = UBound(ar) + 1

で配列の要素数を求めておく。

今回の場合要素数は6に決まっているので、COLUMN_NUMBERS同様定数にするという手もあるが、今後COLUMN_NUMBERSの内容が変わったときに併せて書き換える手間が生ずるのでこのようにした。

(5)の

targetIndex = (number - 1) Mod columnsCount

で配列のどの要素を取り出すのかを決定。

次の(6)の式の中に埋め込んでしまうという手もあるが、後でわかりやすいようにあえてこのようにした。

1番目なら配列の添字は「0」、6番目なら配列の添字は「5」、7番目なら配列の添字は「0」、12番目なら配列の添字は「5」、13番目なら配列の添字は「0」、……という風になる。

後は(6)の

ret = CLng(ar(targetIndex))

で取り出した配列の要素をLong型に変換して返す。型変換はしなくてもVBAが勝手にやってくれるんだが、できる限り暗黙の型変換で済ませないように心がけている。

実行

引数numberにいろいろな数値を指定して実行。

バッチリ。

おわりに

こういう身も蓋もない方法も悪くないと思う。