事例：
ある物が格納されている機器が数台あるのだが、そこから毎朝一定数の物を抜き出して別の箇所に移動させないといけない。一定数というのは、１台につき何個じゃなくて全体で何個ということだ。
例えば、
上のような機器と格納数で、全体で３００個抜かないとならない。
というようなシチュエーションだ。
”物”についての具体的な話は問題があるので、今回は”物”ということで。
単純に必要な数量に達するまで順番に抜いていけば良いのだが、どうも制限があるらしい。それは、
”ある機器からは、なるべくなら抜きたくない。とっておきたい”
なら、機器に優先順位を付けておけばだいじょうぶ。
だが、
”物”を抜きたくない機器は、店舗ごとに違う
らしい。
つまり、優先順位を変えられるようにしておく必要があるということだ。
ということで、今回のネタは、
「一定の数値になるまで、いくつかのセルの値を順番に足していく。ただし、この順番は設定で変えられるようにする。」
ということだな。
やり方としては、
①まず優先順位の順番に並べ替える
②次に行ごと累計を必要数になるまで計算する。
という２段階にわけて考えよう。
ステップ１:行ごと累計を必要数になるまで計算する。
まずは簡単そうな②から。とは言っても、１つのセル内に数式を詰め込むと複雑になりそうなので、
１．単純な累計を求める列を追加
２．それに対して一定数に達しているか判断して、抜き取り数を決定。
と２段構えでいくことにしよう。
結果はこうだ。
１．単純な累計を求める列を追加
Ｄ列の単純な累計については説明する必要はないだろう。とは言ったものの、上のように単に足していく方法とSUM関数の範囲を変えていく方法がある。
SUM関数のメリットは最初の行に
=SUM($C$3:C3)
と入力して、後の行はコピーで行ける点。まあ、足し算でいく場合も２行目からはコピーで行けるのでメリットと言えるかどうか・・・
それに対してデメリットは、
行数が増えていくと、SUM関数の足し算の計算量がNx(N-1)/2となる。オーダー表記で書くと
一方、ただの足し算はN-1回なので、
どっちにしろ５行程度なら影響ないな。お好みで。
２．必要数に達しているか判断して、抜き取り数を決定。
抜き取り数を決めているＥ列について説明すると、まずは最初の行、E3の式について。
”格納数が必要数より多かったら、必要数分抜き取る。必要数以下だったら、全部抜き取る”
ということ。
それ以降の行について。
”累計数が必要数より多かったら、必要数に足りない分を抜き取る。ただし、すでに前の行までに必要数に達していたら、何も抜き取らない。
累計数が必要数以下だったら、全部抜き取る”
言葉で説明するとこうなるが、分かりにくいな。
というわけで図にしてみた。
これで多少すっきりしたかな。
参考までに累計を別列で計算するのではなく、式の中で計算する場合の例も挙げておく。
ここでは累計の計算はSUM関数を使っている。なぜならセルの足し算だと、２行目まで違う式を入れないとダメなため。全体を把握しづらい。
どっちにしろ、ちょっと面倒そうな式だな。
やっぱり累計は分けた方がよさそうだ。
問題があるとすれば、実際に機器を操作して”物”を抜き取る作業者に余計な情報を渡すことのになるくらいか。
 |
| ステップ１の結果の表 |
これでＯＫ。
さて、やっとステップ２だ。
ステップ２：優先順位の順番に並べ替える
単純に機器名と格納数が入っているセルを並べる順番を変えれば、対応はできる。そして、店舗ごとにエクセルのファイルの種類を変えれば。
しかしそれだと管理が面倒だし、第一面白くない。
１．機器名を直接指定する場合
さて、どうするか。機器の優先順位を設定する別表が必要だろうな。
こんな感じの。
 |
| 機器名に直接入力する |
ちなみに、ダブったところの色を変えるのは条件付き書式で対応。
この表の優先順位に対応する箇所に機器名を直接入力することにしよう。
更に、それぞれの機器の格納数も隣の機器名に従って可変にする必要があるので、これも別表にしておく。
 |
| 格納数は直接入力 |
ここの格納数には直接、数値を入力する。
なんか、こう、表を分けていく過程はデータベースの正規化にちょっと似てるな。
そしてこれらの表から、おきまりのMATCHとINDEXの組み合わせで問題なく目的は達成できる。
あと、優先順位に何も設定されていないときなど、MATCH関数がN/Aを吐いたときのために、IF文とISNAを追加すると下のような式になる。
＊上の式には配列数式を使っているが、普通の式を下の行にコピーしていっても同じ。
ただ、配列数式の方が、処理内容が解りやすい。と思う。
２．機器名をリストから選択する場合
だが、ちょっと待て。機器名を直接入力というのは、イマイチかな。
AとかBなんて簡単な名前は稀だろう。番号を決めてそれで入力という手もあるが、もうちょっとスマートに行きたいな。
やはり入力規則を使ってリストから選択できるようにした方がいいかも。
問題になりそうなのは、すでに優先順位を設定してある機器名をリストから排除していくやり方だろう。
また、機器リストを自動的に順番に並べるためには機器の大小関係が必要なので、機器番号を導入する。
これをベースにして、すでに優先順位に設定してある機器名を排除していく。
２．R列の式では、SMALL関数を使ってQ列を小さい順に並べ替える。
３．S列の式では、並べ替え後の番号に対応する機器名を表示する。
ちなみに、上のQ～Sの式は１列にまとめることもできる。
しかし、複雑になりすぎるので、分けておいた方が解りやすいだろう。
さて、もう少しだ。
入力規則の範囲指定に使うために、S3～S7の範囲で、エラーが発生していない範囲を、”選択可能機器リスト”という名前として定義する。
（可変範囲はOFFSET関数を、行数のカウントにはCOUNT関数を使用している。）
そしてセルK3～K7に次のように入力規則を設定する。
これで完了だ。
最後に動作テストをしてみよう。
まず、優先順位に何も設定されていない状態。
次に、優先順位１位にAを設定したあと、最も優先順位が低い機器を設定するというシチュエーションを考える。
ちゃんと選択リストにはAを除いた機器が表示されてる。
そして、優先順位最下位をDとしたあとに、優先順位２位の機器を設定する場合。
A,Dを除いたリストが表示される。
よさげな感じだ。
ついでに入力時とエラー時にメッセージを表示するようにしてみた。
テストのために、直に機器名を入力してみた。
しかし、これは致し方ないか。
ちょっと気に入らないが。
というわけで、今回はこれで終了。
あまり纏まっていないが・・・
0 件のコメント:
コメントを投稿