Excel関数 入居率の計算

今回、Excelを使用し入居率（一月単位）の計算を行ないたいと思っておりますが、計算方法が少し分からない部分が有りましたので質問させて頂きます。

セル表記例
　A　B　C　D　E
1
2

例えば、セルＡ１に当月の日数　※ここでは「30」とします。
Ｂ１に入居人数　※ここでは「10」とします。
Ｃ１に入居者（日）の記入、Ｄ１に退出者（日）を入力するとします。

Ｂ２に延べ人数（少数第２まで）、Ｃ２・Ｄ２には入居率（％）が計算されるようにしたい。

----------------------------------------------

ここで質問ですが、上記の２列目にあるＣ２セルに入力する関数を教えて頂きたいと思います。
例えば、入寮者がいる場合、=1/A1*(A1-C1+1)　と入力すれば計算できますが、いない（0の場合）ですと本来0％（ゼロ値）で表示したいのですが、1.03％　※（1÷30×(30-0+1)）となってしまいますのでIF系の関数で‘Ｃ１が０の場合、入居率は０’といった関数にしたいのです。

因みにＤ２の数式は、=1/A1*D1(表記％)　と入力してあります。


Ｄ２セルに関しましても、もっと良い関数がある様でしたらご教授宜しく御願い致します。

長文、失礼しました。

No.3 ベストアンサー 回答者： Sinogi 回答日時： 2009/07/11 16:27

>「この関数に対して、多すぎる引数が入力されています。

>その関数に必要な引数のヘルプを表示するには［OK］をクリックし、いったんこのメッセージを閉じます。そのあと、数式バーの左端にある符号（＝）をクリックしてください。」
>というメッセージが表示されてしまいます。

せっかくなのでヘルプを読んでください。
関数は引数を　, で区別するので
COUNTIF(I4:M5,O4:S5,">0")では　COUNTIF関数の引数に過剰です。
検索対象領域が離れているので２回計算して合計すればよいでしょう。
つまり　I4:M5　と　O4:S5　それぞれに　COUNTIFを求めてください。

ところで
一連の内容から私なりに質問時の例について整理してみました。
１行目：項目名
A２：当月の日数　以下A列は使用しない。
B２：当月に入退居のなかった居住者人数
B最終行：入退居を考慮した当月の居住者数（当月の居住率）
C２～　入居開始日：当月に入居があった人の居住開始日
C最終行：当月に入居した人の居住月数
D２～　退居日：当月に退居があった人の退居日
C最終行：当月に退居した人の居住月数
質問者様の当初意図とは若干異なりますが大筋問題ないと思います。
ここで１点確認
入居日/退居日は居住日に含むとして考えています。
例：
当月日数（A２）が３０日として
１日入居→３０日居住→３０÷３０＝１か月
30日入居→1日居住→1÷３０＝0.03か月
１日退居→１日居住→1÷３０＝0.03か月
30日退居→３０日居住→３０÷３０＝１か月

でよいでしょうか？
２～４行目に入退居日データがあり、５行目を最終行とすると
B5=B2+C5+D5
C5=ROUND(((COUNTIF(C2:C4,">0")*($A$2+1))-SUM(C2:C4))/$A$2,2)
D5=ROUND(SUM(D2:D4)/$A$2,2)
を考えました。
実データはかなり異なるようなので参考になるでしょうか？

また考え方が違えば補足をお願いします。

この回答への補足

連絡遅れてしまい申し訳ないです。（データが会社にあるため）

COUNTIFを２回で計算したところ解決できました。
=(F4-COUNTIF(I4:M5,">0")-COUNTIF(O4:S5,">0"))+I7+J7+K7+L7+M7+O7+P7+Q7+R7+S7

補足日時：2009/07/13 09:01

この回答へのお礼

質問時の例につきましても、試させてもらいました。

今回は、補足の通り解決出来ましたので、次回に新規データ作成時に活用させて頂こうと思います。

度々のご回答、有難う御座いました。
また別件での質問をするかもしれませんがお目に掛かれば宜しく御願い致します。

お礼日時：2009/07/13 09:06

No.2 回答者： Sinogi 回答日時： 2009/07/08 17:44

補足いただきましたが式の意味は理解できません。

まず、
>B2には=(B1-COUNTA(C1:D1))+C2+D2と入力してありまして、カウントしてしまう分C1・B1が0の場合ですとB2部分の入居率がカウント分少なくいなってしまいます。
には

COUNTA(C1:D1) を　COUNTIF(C1:D1,">0")　とすればご希望になるでしょう。

ところで「意味がわからない」について書きますが
入居率の考え方が何を基準にしているのかが見えません。

私の感覚では　入居率=のべ居住日数÷（全室数×日数）　なのですが提示されている条件を見る限りそうではありません。

入居日は入居前が「空」なので、その入居日を減ずれば良く、
退居日は退居後が「空」なので、退居日～末日を減ずることになりますが、その式が見えません。

何やら独特の判断があるようなので提示されている式にはアドバイスのしようがありません。

この回答への補足

お返事遅れました。更なる回答有難うございます。

提示して頂きました式を以下に当てはめたところ、
「この関数に対して、多すぎる引数が入力されています。
その関数に必要な引数のヘルプを表示するには［OK］をクリックし、いったんこのメッセージを閉じます。そのあと、数式バーの左端にある符号（＝）をクリックしてください。」
というメッセージが表示されてしまいます。

=(F4-COUNTA(I4:M5,O4:S5))+I7+J7+K7+L7+M7+O7+P7+Q7+R7+S7
※質問の式とは異なります。
　　　　　　　↓
=(F4-COUNTIF(I4:M5,O4:S5,">0"))+I7+J7+K7+L7+M7+O7+P7+Q7+R7+S7


＞私の感覚では　入居率=のべ居住日数÷（全室数×日数）・・
考え方としてはそれで結構です。
今回の式の場合、１月が３０日としましたら、一人当たりの入居率は一月分で〔１〕と設定しておりまして、１５日でしたら〔０．５〕です。
（説明不足で大変申し訳なく思っております）

質問の式（例題）の中にあるＣ２・Ｄ２に対する入居率とは、３０日のう何日使用していたかをただ単に率で表しているだけでして、この式のＣ１が10でしたらＣ２は71％となり、Ｂ２は10.71と表示されます。（当月最大の居住者は１１人で、そのうち使用室分の入居率は10.71という事です※入居率という表現自体がおかしいかも知れませんがご了承下さいませ）

＞入居日は入居前が「空」なので・・
Ｃ２がその式でございます。
＞退居日は退居後が「空」なので・・
Ｄ２がその式でございます。Ｃ2Ｄ2共、表示は月maxで1としまして以下％で表示しております。

※この式を何に使うかといいますと、各種料金の使用分に対する計算を行なう為に使います。個人単位のものも有れば、全体量を使用人数・日数分で割り計算する項目がありましてその計算に利用しようと思っております。

またアドバイス頂けると助かります。

補足日時：2009/07/10 13:40

No.1 回答者： Sinogi 回答日時： 2009/07/08 16:27

どうにも式の意味が理解できません。

それはともかく、If関数はヘルプを見ればわかることですが
If(論理式,真の場合,偽の場合)なので
>IF系の関数で‘Ｃ１が０の場合、入居率は０’といった関数にしたいのです
なら=If(C1=0,0,指定の式）
でご希望が実現できます。

>Ｄ２セルに関しましても、もっと良い関数がある様でしたらご教授宜しく御願い致します
C1/D1に入る値が不明（複数の入退去者がいたら?人数？日数？）なのでそこを補足すればご希望の回答を得やすいと思います

この回答への補足

ご指摘有難うございます。質問内容が曖昧な部分があり申し訳ないです。

＞なら=If(C1=0,0,指定の式）
If関数有難うございます。C2表記は解決できました。
が、問題も出て来ましたので追加で質問させていただきます（申し訳ないです）

質問欄にはありませんが、B2には=(B1-COUNTA(C1:D1))+C2+D2と入力してありまして、カウントしてしまう分C1・B1が0の場合ですとB2部分の入居率がカウント分少なくいなってしまいます。
これを解消する他の関数がありましたら宜しくお願いします。
（0の場合（入退去者がいない場合）未入力にすれば問題なく計算できますが、上記表は例であり、実際計算するエクセルシートの欄は数十とありますので０入力で作業時間を省略したい為に質問させて頂きます）

＞C1/D1に入る値が不明（複数の入退去者がいたら?人数？日数？）
質問の表は例ですので、複数は考えないものとしてこの欄には入居日を記入します。（説明不足で大変申し訳ないです）
ですのでＢ列はこの質問ではほぼ無視して頂きまして結構です。
10日に入居すれば、入居日（日数）を記入していただき「10」となります。

補足日時：2009/07/08 16:35