• ベストアンサー

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

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

質問者が選んだベストアンサー

  • ベストアンサー
  • mu2011
  • ベストアンサー率38% (1910/4994)
回答No.2

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

12ma34
質問者

お礼

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

その他の回答 (3)

  • zap35
  • ベストアンサー率44% (1383/3079)
回答No.4

#01です。  >月半ば入社がわりと発生するので、消化率を出すのに  >反映させることができるのかを、一番知りたいです 完全な回答にはなりそうもありませんがこんな表で考えてみました。多少でもヒントになれば幸いです。       A列   B列 C列 D列 E列       M列  N列   O列    P列 1行目 2007/6  4月 5月 6月 7月 …中略… 3月 入社日 公休累計 消化累計 2行目 公休日数  8 10  9             11    3行目 Aさん    8 10  9                         27      27 4行目 Bさん    8  9  8                        27      25 5行目 Cさん       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"月"」としています。日付形式で入力しないと後で計算に使えないからです 2行目の公休日数は手入力です。また各人の消化日数も当然手入力です。 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関数で切り捨てています。

12ma34
質問者

お礼

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

  • mshr1962
  • ベストアンサー率39% (7417/18945)
回答No.3

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

12ma34
質問者

お礼

ご回答ありがとうございます。 自宅にはエクセルがないという お粗末な環境なので、 明日会社にて できるかどうか挑戦してみます。 一人目ご回答いただいた方宛の補足に書きましたが、 公休日が固定の部署、 変動する部署があり、 公休日が固定してる部署に 教えていただいた方法が あてはまるのかなと思っています。

  • zap35
  • ベストアンサー率44% (1383/3079)
回答No.1

これはひどい。全然わかりません。12ma34さんはご自分の仕事だからこれだけで十分理解できるのでしょうが、回答者は質問文の内容以外の情報はないのですよ ・例えば実取得日数、取得要公休数はどれがその数値ですか? ・また(8) (10) (9)は何ですか? ()で括られていることに意味があるのですか? (普通エクセルで「(8)」と入力すると「-8」を意味します) ・表中の式「23/8+10+9」はどういうルールですか?  23/8も分からなければ、それに10と9を加えている意味が分かりません >Cさんのような途中入社の方の場合、6月 公休9日ー3日=6日を反映して、 > ※5/15以降-公休6日 ・どうしてそうなるのですか? どのような計算式で決まるのですか? >6月そして6月以降も計算できるようにする方法 ・質問の例で結果がどうなれば正しいのですか? それが分からないと検算できないから、むやみやたらに回答せねばなりません。 ・質問の例には6月までしかデータがありませんが7月以降はどうなるのですか? ・備考欄には「4/1入社」と入力されているのですか? 日付形式でないなら加工しないと式にできませんね SUMPRODUCT関数もご利用されるくらいのスキルをお持ちのようですから、回答する目線でもう一度質問を補足していただけませんか。 その内容を拝見して回答できるようなら、再回答いたします

12ma34
質問者

補足

ご指摘ありがとうございます。 ご理解していただけるような補足になるよう努力します。 (あまりエクセルは詳しくありません・・) 何卒よろしくお願いいたします。 ・また(8) (10) (9)は何ですか?  ☆( )内は取得要公休数です。 ( )内がマイナスを意味するとのこと。 今後気をつけます。 ・表中の式「23/8+10+9」はどういうルールですか? ☆Aさんの分子の23は、  8+10+5で、4~6月までの実取得日数、 分母の8+10+9は 4~6月までの取得要公休日数です。 実取得日数÷取得要公休日数で公休消化割合を出します。 4月から表を作成するので、 4月は、4月単月のみですが、 翌月5月は、 4~5月の実取得累計日数/4~5月の取得要累計公休数の率と すべて4月よりの累計として 最終翌年3月までの率を計算します。 >Cさんのような途中入社の方の場合、6月 公休9日ー3日=6日を反映して、 ※5/15以降-公休6日 ・どうしてそうなるのですか? どのような計算式で決まるのですか? ☆さきほど書いた例からいきますと 5月は 公休10日ですが、 5月15日、月半ばで入社した場合、10日ではなく、 取得要公休数は、 その日入社日以降の公休数をカウントします。 公休日はカレンダーで固定で決まっている部署と 変動する部署があります。 固定のところは、その日以降のカレンダー上の日数ををカウントし、 そうでないところは、 入社日以降の月内日数÷31日(28・29・30日)対して (Cさんの例を借りると、17/31×10≒6日)公休日数を計算します。 Cさんの5月の取得要公休数は、例と考えてください。 (私が「6月 公休9日ー3日=6日」と書いたので、 余計ややこしくなってしまったと思います。 5月が正解です。申し訳ございません。) 月半ば入社がわりと発生するので、 消化率を出すのに 反映させることができるのかを、一番知りたいです。 >6月そして6月以降も計算できるようにする方法 ・質問の例で結果がどうなれば正しいのですか? それが分からないと検算できないから、むやみやたらに回答せねばなりません。 ☆知りたいのは、 B,Cさんのように年度始め、 月始めから全員いるわけではない状況で、 入社後の実取得日数を毎月表に入れていけば、 実取得日数/取得要公休数=公休取得率を出せるように、 例には書いていない7月以降もそれをずーとしていって、 最終翌年3月まで 出せるようにしたいです。 それには 特にCさんのように、 月途中入社の人たちの 中途半端な日数を反映できるようにするには どうしたらよいのでしょうか? ・備考欄には「4/1入社」と入力されているのですか? 日付形式でないなら加工しないと式にできませんね ☆最初からいる人、そうじゃない人がいることを示すために 入社日を書きました。 以上です。 補足になっているればよいのですが。 よろしくお願い申し上げます。

関連するQ&A

専門家に質問してみよう