当番は誰？？ MOD関数で得られる余りの値を活用してローテーション管理をする

OpenOffice

下の表では、名前１グループと名前２グループの当番の組み合わせをF3セルとG3セルに表示しています。

送信者 OpenOfficeこの画像のファイルは、SKYDRIVEからcalc109.odsをダウンロードしてください。

それぞれのグループの人数は、COUNTA関数で得られます。
=COUNTA(A2:A30)
COUNTA関数では、空白以外のセルの数を数えるので、A30まで範囲を取っても9行目以降は空白なので計算に入りません。

経過日数は、現時点での日にち=today()から開始日を引いて算出します。
その際、開始日を計算に入れるので1を加えます。
=D2-D1+1

当番が誰か、名前１グループを例にとって説明します。
=INDIRECT(ADDRESS(IF(MOD($E$2;F1)=0;F1;MOD($E$2;F1))+1;1))

MOD関数を使って、経過日数をそれぞれのグループの人数で割った余りを求めます。
=MOD($E$2;F1)
このときの余りが0だった場合は、名前1グループなら7（F1の値)、名前２グループなら5(G1の値)を返すようにします。
IF(MOD($E$2;F1)=0;F1;
それ以外のときは、MOD関数の値を返すようにします。

ここまでに得られた値をセルのアドレスを特定するための行番号として使用します。
ただしこのとき、A列とB列の1行目に、見出しが入っているので、1を加えます。
また、列番号は、名前１グループの場合、一列目の名前を参照するので１、名前２グループの場合、２列目を参照するので２にします。
ADDRESS関数に行と列の番号の値を入れてセルのアドレスを特定します。
=ADDRESS(IF(MOD($E$2;F1)=0;F1;MOD($E$2;F1))+1;1)
ここまでで、F3セルの場合、$A$4がADDRESS関数の返り値になります。

このアドレスをINDIRECT関数に当てはめて、アドレスの値にアクセスします。
=INDIRECT(ADDRESS(IF(MOD($E$2;F1)=0;F1;MOD($E$2;F1))+1;1))
これで名前１グループの場合、中森昭雄が返されます。


ここで使用した主な関数

関数名：ADDRESS関数
書式：=ADDRESS(行番号;列番号;種類;A1;シート名)
何ができる：範囲の中で行番号と列番号が交差するセルのアドレスを文字列として返す
平たく書くと：=ADDRESS(3;1)で$A$3セルが返されます。
●ADDRESS関数で指定する種類と返される値：
種類を省略すると絶対参照・・・$A$3
種類を1に設定すると絶対参照・・・$A$3
種類を2に設定すると相対参照・・・A$3(行を固定）
種類を3に設定すると相対参照・・・$A3(列を固定）
種類を4に設定すると参照なし・・・A3
シート名は、関数を入力するシートと異なるシートへ参照する場合以外、入力不要。

関数名：INDIRECT関数
書式：=INDIRECT(参照範囲)
何ができる：文字列として入力されたセル番号や範囲を計算式で利用できるようにする
平たく書くと：=INDIRECT(A3)でA3セルを参照範囲として計算式の中で利用できるようにします。

関数名：MOD関数
書式：=MOD(処理対象の数値;除数)
何ができる：処理対象の数値を除数で割った余りを返す
平たく書くと：=MOD(28;6)　で28を6で割った余り4を返す

この記事をクリップ！