エクセル：計算式の組み合わせがわかりません。

質問

エクセル2003で、公休消化率を作成しています。
毎月末締めで、決まった（取得要公休数）があり、
実取得日数／取得要公休数で、割合をだしています。
　　　　　4月　　　5月　　　6月
　　　　（８）　　（１０）　　（９）　　合計　公休消化率　備考
Ａさん　　８　　　１０　　　５　　　23　 23/8+10+9　4/1入社　　　
Ｂさん　　　　　 １０　　　９　　　19　　19/10+9　　5/1日入社
Ｃさん　　　　　 　５　　　５　　　10　　　10/6+9 　5/15日入社
　　　　　　　　　　　　　　　　　　　 ※5/15以降-公休６日
１日より在籍しているＡさんとＢさんに関しては、
セルに数字が入力されてあれば、取得要公休数を計算するのに、
=SUMPRODUCT(NOT(ISBLANK(E5:P5))*$E$4:$P$4)　を使って
上の行に取得要公休数を入力しておいて
反映させることができるのがわかったのですが、
Ｃさんのような途中入社の方の場合、
６月　公休９日ー３日＝６日を反映して、
全体の率も実取得数を入れるだけで、
６月そして６月以降も計算できるようにする方法は
ありますか？
お知恵をおかりしたくよろしくお願い申し上げます。

回答No.2

mu2011 · ベストアンサー

安直ですが、途中入社用の調整日数列を追加して取得要公休数から調整日数（途中入社は３、正規は０）を減算する方法では駄目でしょうか。

お礼

早速のご回答ありがとうございます。
家のパソコンにはエクセルが入っていないので、
明日会社にて挑戦してみます。

回答No.4

zap35

＃０１です。
　＞月半ば入社がわりと発生するので、消化率を出すのに
　＞反映させることができるのかを、一番知りたいです
完全な回答にはなりそうもありませんがこんな表で考えてみました。多少でもヒントになれば幸いです。

　　　　　　Ａ列　　　B列　C列　D列　E列　　　　　　　M列　　N列　　　O列　　　　P列
１行目　2007/6　　4月　5月　6月　7月　…中略…　3月　入社日　公休累計　消化累計
２行目　公休日数　　8　10　　9　　　　　　　　　　　　　11　　　
３行目　Ａさん　　　　8　10　　9　　　　　　　　　　　　　　　　　　　　　　　　　27　　　　　　27
４行目　Ｂさん　　　　8　　9　　8　　　　　　　　　　　　　　　　　　　　　　　　27　　　　　　25
５行目　Ｃさん　　　　　　　5　　9　　　　　　　　　　　　　　　　2007/5/15　　　14　　　　　　14

「月締め」とのことなのでA1セルには「処理月」を入力します。当月が2007年6月なら「2007/06」と入力してセルの書式は「YYYY/M」にしておきます。（実際には2007/6/1の日付が入力されたことになります）

B1:M1も日付形式で「2007/4」「2007/5」…「2008/3」と入力してセルの書式を「M"月"」としています。日付形式で入力しないと後で計算に使えないからです

２行目の公休日数は手入力です。また各人の消化日数も当然手入力です。

N列には入社日を日付形式で入力します。日割り計算をするため、「YYYY/M/D]できちんと入力する必要があります

P列には「処理月」までの消化累計を計算します。P3セルに入力する式は以下です。
　=SUMPRODUCT(($B$1:$M$1<=$A$1)*(B3:M3))

最後にO列は「処理月」までの各人の公休日数の累計です。その式は長いですが以下です。この式で使用しているDATEDIF関数は「ツール」→「アドイン」→「分析ツール」にチェックを入れないと使えませんので、先にその操作を行っておいてください

　=IF(N3="",SUMPRODUCT(($B$1:$M$1<=$A$1)*($B$2:$M$2)),SUMPRODUCT(($B$1:$M$1<=$A$1)*($B$1:$M$1>N3)*($B$2:$M$2))+INT(INDEX($B$2:$M$2,MATCH(N3,$B$1:$M$1,1))*DATEDIF(N3,DATE(YEAR(N3),MONTH(N3)+1,0),"d")/DATEDIF(DATE(YEAR(N3),MONTH(N3),1),DATE(YEAR(N3),MONTH(N3)+1,1),"d")))

少し解説します。N列が空白なら単純に処理月までの累計です。それが
　SUMPRODUCT(($B$1:$M$1<=$A$1)*($B$2:$M$2))
の部分です。入社日が入力された人に対しては
　入社月より後の月の累計　＋　入社月の公休日数×日割り日数×入社月日数
で計算しています。

入社月より後の月の累計は
　SUMPRODUCT(($B$1:$M$1<=$A$1)*($B$1:$M$1>N3)*($B$2:$M$2))
入社月の公休日数
　INDEX($B$2:$M$2,MATCH(N3,$B$1:$M$1,1))
日割り日数
　DATEDIF(N3,DATE(YEAR(N3),MONTH(N3)+1,0),"d")
入社月日数
　DATE(YEAR(N3),MONTH(N3)+1,1),"d"))
です。日割りした結果はINT関数で切り捨てています。

お礼

処理月のところと、
日割りのところが未知の分野なので、
すぐには理解できそうにありませんが、
じっくり取り組み活用して、
新しい知識を習得したいと思います。
ありがとうございました。

回答No.3

mshr1962

公休日リスト
2007/4/1
・
2007/5/1
2007/5/3
2007/5/4
2007/5/5
・
2008/3/24
のように公休日をリスト化して
=SUMPRODUCT((公休日リスト>=入社日)*(公休日リスト<=計算する月末日))
で個人別の取得要公休数をだせば良いのでは

お礼

ご回答ありがとうございます。

自宅にはエクセルがないという
お粗末な環境なので、
明日会社にて
できるかどうか挑戦してみます。

一人目ご回答いただいた方宛の補足に書きましたが、
公休日が固定の部署、
変動する部署があり、
公休日が固定してる部署に
教えていただいた方法が
あてはまるのかなと思っています。

回答No.1

zap35

これはひどい。全然わかりません。12ma34さんはご自分の仕事だからこれだけで十分理解できるのでしょうが、回答者は質問文の内容以外の情報はないのですよ

・例えば実取得日数、取得要公休数はどれがその数値ですか？

・また（８）　（１０）　（９）は何ですか？　（）で括られていることに意味があるのですか？　（普通エクセルで「(8)」と入力すると「-8」を意味します）

・表中の式「23/8+10+9」はどういうルールですか？
　23/8も分からなければ、それに10と9を加えている意味が分かりません

＞Ｃさんのような途中入社の方の場合、６月　公休９日ー３日＝６日を反映して、
＞ ※5/15以降-公休６日
・どうしてそうなるのですか？　どのような計算式で決まるのですか？

＞６月そして６月以降も計算できるようにする方法
・質問の例で結果がどうなれば正しいのですか？　それが分からないと検算できないから、むやみやたらに回答せねばなりません。

・質問の例には６月までしかデータがありませんが７月以降はどうなるのですか？

・備考欄には「4/1入社」と入力されているのですか？　日付形式でないなら加工しないと式にできませんね

SUMPRODUCT関数もご利用されるくらいのスキルをお持ちのようですから、回答する目線でもう一度質問を補足していただけませんか。
その内容を拝見して回答できるようなら、再回答いたします

補足

ご指摘ありがとうございます。
ご理解していただけるような補足になるよう努力します。
（あまりエクセルは詳しくありません・・）
何卒よろしくお願いいたします。

・また（８）　（１０）　（９）は何ですか？　
☆(　)内は取得要公休数です。
（　）内がマイナスを意味するとのこと。
今後気をつけます。

・表中の式「23/8+10+9」はどういうルールですか？
☆Ａさんの分子の２３は、　
８＋１０＋５で、４～６月までの実取得日数、
分母の８＋１０＋９は　４～６月までの取得要公休日数です。
実取得日数÷取得要公休日数で公休消化割合を出します。
４月から表を作成するので、
４月は、４月単月のみですが、
翌月５月は、
４～５月の実取得累計日数／４～５月の取得要累計公休数の率と
すべて４月よりの累計として
最終翌年３月までの率を計算します。

＞Ｃさんのような途中入社の方の場合、６月　公休９日ー３日＝６日を反映して、 ※5/15以降-公休６日
・どうしてそうなるのですか？　どのような計算式で決まるのですか？
☆さきほど書いた例からいきますと
５月は　公休１０日ですが、
５月１５日、月半ばで入社した場合、１０日ではなく、
取得要公休数は、
その日入社日以降の公休数をカウントします。
公休日はカレンダーで固定で決まっている部署と
変動する部署があります。
固定のところは、その日以降のカレンダー上の日数ををカウントし、
そうでないところは、
入社日以降の月内日数÷３１日（２８・２９・３０日）対して
（Ｃさんの例を借りると、１７/３１×１０≒６日）公休日数を計算します。
Ｃさんの５月の取得要公休数は、例と考えてください。
（私が「６月　公休９日ー３日＝６日」と書いたので、
余計ややこしくなってしまったと思います。
５月が正解です。申し訳ございません。）
月半ば入社がわりと発生するので、
消化率を出すのに
反映させることができるのかを、一番知りたいです。

＞６月そして６月以降も計算できるようにする方法
・質問の例で結果がどうなれば正しいのですか？　それが分からないと検算できないから、むやみやたらに回答せねばなりません。
☆知りたいのは、
Ｂ，Ｃさんのように年度始め、
月始めから全員いるわけではない状況で、
入社後の実取得日数を毎月表に入れていけば、
実取得日数／取得要公休数＝公休取得率を出せるように、
例には書いていない７月以降もそれをずーとしていって、
最終翌年３月まで
出せるようにしたいです。
それには
特にＣさんのように、
月途中入社の人たちの
中途半端な日数を反映できるようにするには
どうしたらよいのでしょうか？

・備考欄には「4/1入社」と入力されているのですか？　日付形式でないなら加工しないと式にできませんね
☆最初からいる人、そうじゃない人がいることを示すために
入社日を書きました。

以上です。
補足になっているればよいのですが。
よろしくお願い申し上げます。