Excelでロッカーの稼働率の計算方法は?

質問

駅にあるような鍵がかけられるロッカーAと、鍵がない棚だけのロッカーBがあります。

ロッカーAは4段5列で最大20個の荷物が置けます。
ロッカーBは幅3mで、棚が6段あり、各段とも余地があれば、詰めてどんどん荷物を置けます。
つまり何個の荷物を置けるかは不定です。

現在、以下の通りExcelに記帳しています。
ロッカーAは荷物で埋まった個数。
ロッカーBは3m×6段の長さから、余地（各段の荷物がない部分の長さの合計）を引いた値。

これらロッカー全体での月間稼働率を求めるには、どういった計算式としたらよいでしょうか?

回答No.6

MarcoRossiItaly

No.4・5 です。何度もすみません。No.4 で、「540 m」と計算していますが、これは 100 % 占有された場合の長さでした。実際には勿論、記録されている使用部分の総延長の和を使用して計算する必要がありますので、ご注意。失礼しました。

お礼

>「540 m」と計算していますが、これは 100 % 占有された場合の長さでした。

意図承知しております。
現場も上司も納得のいく資料が出せるよう頑張ってみます。

度々のご回答有難うございました。

回答No.5

MarcoRossiItaly

＞ただ、お客様からお預かりする荷物が、同時に複数個の場合には向かないように思われますがいかがでしょう？

そうですね。分かりやすいかと思ってそう言ってしまいましたが、あんまり適切な言い方ではなさそうですね。すみません。

荷物の個数とか、契約の件数とか、条件によっては重量とかでも構わないと思いますよ。お手元のデータの実態に合わせればよいでしょう。とにかく趣旨としては、ロッカーよりも、荷物のほうの数量を測定することにより、全体を把握するということですね。

上司の方も「ざっくり」との言い方でおっしゃるのだから、最後は、質問者さんが「これで良し」と感じられた（笑）データを提出されればよろしいのかもしれませんね。お仕事頑張ってください。

回答No.4

MarcoRossiItaly

＞……と思ったきかっけは、あとどれくらい荷物を置けたのか（つまりあとどれくらいお客様を取ることができたのか）を知りたい……

そのようなニーズなのであれば、稼働率を計算するための直接の基準としては、ロッカーの個数や長さではなく、「人数」を採用してはいかがでしょうか。

そうしておけば、例えば A、B 全体で 200 人分の荷物を預かれる店舗で、人数を基準にした稼働率が 80 % だとすれば、200 × (100 - 80)% = 40（人）という具合に、すぐに受入可能数のマージンを求めることができますね。

「数週間から数か月間ロッカーに置いてあります」とありますし、余裕数を把握するのが目的とのことですから、仮に 1 日の間に同じセルで荷主が複数回、入れ替わることがあったとしても、それは 1 回として数えるということになるでしょうか。

「月間延べセル数」というのは、20 個/日× 30 日 = 600 個、みたいなことかな？

No.2 では重み付けの話をしましたが、人数で計算することにしたとしても、A、B 全体で把握するためには、やはり統一の指標を持つ必要がありますね。つまり B での最大の人数を、何かしら想定しないといけませんね。

例えば、過去の実データを幾つか見てみて、単位長さ（1 m）当たりの荷主の人数を計算してみてはいかがでしょう。計算に用いる時間が長いほど精度が上がりそうですから、例えば 1 か月で見たときの総延長が (3 m × 6 段)/日 × 30 日 = 540 m とかだとして、同じ期間の利用者数が 1,500 人だったとすれば、1,500 人 / 540 m = 2.78 人/m と求まりますね。

ただ、預かる荷物の大きさが時季や場所によって大きく変化するといったケースでは、この定数の季節変動・地域変動も考慮し、いくつかの値を持っておくべきなのかもしれません。

このようにして、ある店舗における最大の受入可能数を求めておけば、それに対する実際の利用者数の割合を計算することで、稼働率とできそうですね。

以上の説明は、No.3 さんが「例えばロッカーBが2.5倍の収容力があると計算されたら」とおっしゃっていることの、具体的な方法に相当していると思います。

お礼

重ねてのご回答有難うございます。

>仮に 1 日の間に同じセルで荷主が複数回、入れ替わることがあったとしても、それは 1 回として数えるということになるでしょうか。
→その日のうちに荷主が入れ替わることは殆どありませんが、仮にあったとして、セルの使用中が継続しているとの見方となります。

>「月間延べセル数」というのは、20 個/日× 30 日 = 600 個、みたいなことかな？
→その通りです。ですので、空きがはっきりしており、引き合いに対応しやすいのですが、ロッカーBはそうもいかず・・・。ですが営業で忙しそうな上司はざっくり店舗一覧で数字が欲しいと。かといって異質な稼働率を一緒にした資料とはどんなものだろうか？と思いました。すみません愚痴です。

「人数」基準のご提案、思いもよりませんでした。
ただ、お客様からお預かりする荷物が、同時に複数個の場合には向かないように思われますがいかがでしょう？
そうだとしても、データを蓄積すれば、お客様当りの棚の占有幅の現実的な値が求められそうであるという点は目から鱗でした。

回答No.3

MackyNo1

>ロッカーAに入る荷物と同じサイズのもので、ロッカーBの全棚を満たしたとして、便宜的に換算した収容能力を割り出した上で、加重平均で求めたらいいのでしょうか。

どのような稼働率を求めたいのか（収容能力の計算方法の違い）によって数式が変わりますが、例えば、上記のような設定で求めた稼働率は例示の添付ファイルのようになります。

まずF2セルに開始日（例えば1/1）と入力し、セルの書式設定でユーザー定義にして「m"月"」にして右クリックで下方向にオートフィルコピーして「連続データ月単位」を指定します（オートフィルして右下に表示されるオートフィルのオプションから選択してもOKです）。

次に、Excel2007以降を使用されているとして、1月のロッカーAの稼働率を表示するG2セルに以下の式を入力して下方向にオートフィルコピーします。

=AVERAGEIFS($C$2:$C$500,$B$2:$B$500,G$1,$A$2:$A$500,">="&$F2,$A$2:$A$500,"<"&$F3)/20

同様にロッカーBの稼働率を表示するH2セルはG2セルを右方向にオートフィルして、最後の「20」を「18」に変更します（セルの書式はパーセント）。

=AVERAGEIFS($C$2:$C$500,$B$2:$B$500,H$1,$A$2:$A$500,">="&$F2,$A$2:$A$500,"<"&$F3)/18

総合稼働率(加重平均）のI2セルには、例えばロッカーBが2.5倍の収容力があると計算されたら、以下の式を入力して下方向にオートフィルコピーします。

=(G2+H2*2.5)/(1+2.5)

もちろん上記の総合稼働率の数式にロッカーAとBの稼働率の数式を代入すれば、それらを表示することなく総合稼働率を表示することができます。

＃Excelを含めてOfficeソフトはバージョンによって使用できる機能や操作方法が大きく異なりますので、ご質問の際は必ずバージョンを明記するようにしましょう。

お礼

計算式をお考え下さり有難うございます。

計算式中の「/20」や「/18」の部分も別のワークシートにマスター化して、ロッカーの型式が増えたり変わっても大丈夫な感じにもしてみたいと思います。

システム環境書き忘れのご指摘ありがとうございます。
こちらExcel2007を使用しております。
早速取り入れてみて、店舗の現場の意見とすり合わせてみます。

回答No.2

MarcoRossiItaly

Excel はほとんど関係なく、算数のご質問ですね。

計算を始める前に、いろいろと問題があります。主に、「稼働率」の定義に関連して。

●「時間をどう扱うか」その 1 。例えば、1 h × 10 回と 10 h × 1 回は同じとして
　評価するのか。あるいは、時間は無視し、回数だけで考えるのか。
●「時間をどう扱うか」その 2 。日をまたぐ場合、月をまたぐ場合などはどうするか。
　例えば、按分（比例配分）して分割するとか、時間の長いほうの月に帰属させるとか…。
● A について、「個数」とあることから、整数データと判断。最も多用されている
　定義式は「使用しているロッカー数÷全体のロッカー数」といった感じだが、質問者
　さんとしてはそれで構わないか。
● B について、高さは無視し、水平方向の長さのみで考えても、質問者さんとしては
　それで構わないか。
●その場合、最も多用されている定義式は「使用している長さ÷全体の長さ」といった
　感じだが、質問者さんとしてはそれで構わないか。
●重み付けについて、A に所属しているロッカー同士、B に所属している部分的な
　スペース同士は互いに等価であると考えるとしても、A と B を比べてしまうと、異質。
　両者の重みが等しい（1:1）と決めることにしても、質問者さんとしてはそれで
　構わないか。例えば、利用回数で見ると A と B は 1:3 となっている実態があるので、
　重みは 3:1 に設定してみるとか。利用回数は、個人を区別するのか、延べ人数
　なのか…。A と B では利用料金が異なるので、それに応じた重みを設定するとか。
　それとも A と B をムリヤリ混ぜることはやめて、別々の結果として求めるのか。

質問者さん自身がたくさんのことを決めないと、まだ計算できる段階ではないということです。どんな種類のデータをお持ちなのかも不明ですし、こちらではこれ以上、何とも言えません。計算そのものはただの割合の計算ということで、四則演算だけで済ますことが多いですから、易しいですけどね。

＞……余地から割り出した占有率を用いている……
＞……全棚を満たしたとして、便宜的に換算した収容能力を割り出した上で、加重平均で……

こちらでは、「占有率」の定義が不明。「収容能力」も。考慮する重みが不明なので、「加重平均」も不明。加重平均の形式も、一般的な線形結合のような式でいいのか、もっと複雑な多項式なのか…。

質問者さんにとっては、それらの定義は自明なのだとしたら、気付かないうちに、様々な前提条件を質問者さんが設定してしまっているということです。したがって、こちらでは立式のしようがないということになります。学校のテストなら「これが答え」と言えますが、現実社会の問題に取り組む際には、様々な判断が必要となります。計算能力だけの話ではありません。

まあそういうのも数学力のうちかもしれませんが。とりあえず Excel は単なる計算機であり、そういった判断は勿論できないので、人間が考えるしかありません。

補足

ご回答有難うございます。

この荷物はお客様からお預かりしているもので、お客様の要望に応じて数週間から数か月間ロッカーに置いてあります。
そういったことから「時間をどう扱うか」については、時間単位というよりは日単位で記帳をしております。
また通常、日や月をまたぎますので、按分も行っていません。ある月そこに置いてあった日数をその月の分として見ております。

現在、占有率と稼働率は次の通り計算しております。
　占有率(A)＝使用したセル数÷ロッカーの全セル数
　占有率(B)＝使用した幅÷全体の幅
　稼働率(A)＝一日に使用したセル数の月間合計÷月間延べセル数
　稼働率(B)＝一日に使用した幅の月間合計÷全棚の月間総延長
これがAとBで一緒くたにできそうにないので、全体を把握するとしてどうしたらよいか分かりませんでした。

そもそも全体稼働率を出そうと思ったきかっけは、あとどれくらい荷物を置けたのか（つまりあとどれくらいお客様を取ることができたのか）を知りたいとのことからでした。
ご指摘の通り、ロッカーA、B別々に稼働率を出すことも考えたのですが、A、Bそれぞれ一方のタイプのロッカーだけの店舗や、AB混在した店舗が合わせて数十店あって、店舗間の融通をするために、全店舗の状態を見られる資料が欲しくなったことが、この質問をさせて頂いた主な理由となります。

回答No.1

MackyNo1

>これらロッカー全体での月間稼働率を求めるには、どういった計算式としたらよいでしょうか?

質問内容がいま一つ不明です。

まず確認ですが「月間」ではなく1日の稼働率はどのように計算する（計算したい）のでしょうか。

また、ロッカー全体で評価するとなると、ロッカーAとBの収容能力によって数値が変わると思います。

例えば、ある1日のロッカーAの埋まった個数が10個（占有率50％）で、ロッカーBの各段の荷物がない部分の長さの合計が6ｍ（66％の占有率）の場合、ロッカーBの収容能力が2倍なら単純平均ではなくロッカーBに2倍の重みを付けた加重平均するほうが正確な値になります。

このようなケースで月間平均データを出すなら、ロッカーごとに各日付のデータをすべて合計して、ロッカーごとの収容能力全体で割り算することになります。

このようなご質問では、実際に入力されているデータのレイアウトと入力項目を以下のように具体的に例示されれば、より的確な回答ができると思います。

例：A列に日付、B列にロッカーの種類（AまたはB)、C列に個数または荷物が置かれている部分の長さが入力されていて、月ごと（1年を超えない期間）の集計をしたいなど。

補足

回答有難うございます。
Excelへの日々の記帳方法については「例」として挙げて下さっている通りです。

ロッカーAには物理的に入る荷物の大きさがほぼ同じものとなるのですが、ロッカーBでは幅広の荷物も、そうでないものも、決まったサイズだけのものの置けるため、荷物の個数から見たA・Bの収容能力の比較は出しづらく、ロッカーBでは余地から割り出した占有率を用いている次第です。

ロッカーAに入る荷物と同じサイズのもので、ロッカーBの全棚を満たしたとして、便宜的に換算した収容能力を割り出した上で、加重平均で求めたらいいのでしょうか。