Excel（エクセル）活用コミュの関数での表し方

有給管理表を作成しております。

?勤続年数が6ヶ月、出勤日数が48日以上、72日以下の場合は有給日数1日
?勤続年数が6ヶ月、出勤日数が73日以上、120日以下の場合は有給日数3日
?勤続年数が6ヶ月、出勤日数が121日以上、168日以下の場合は有給日数5日
?勤続年数が6ヶ月、出勤日数が169日以上、216日以下の場合は有給日数7日
?勤続年数が6ヶ月、出勤日数が217日以上の場合は有給日数10日

?勤続年数が18ヶ月、出勤日数が48日以上、72日以下の場合は有給日数2日
?勤続年数が18ヶ月、出勤日数が73日以上、120日以下の場合は有給日数4日
?勤続年数が18ヶ月、出勤日数が121日以上、168日以下の場合は有給日数6日
?勤続年数が18ヶ月、出勤日数が169日以上、216日以下の場合は有給日数8日
?勤続年数が18ヶ月、出勤日数が217日以上の場合は有給日数12日

?勤続年数が30ヶ月、出勤日数が48日以上、72日以下の場合は有給日数2日
?勤続年数が30ヶ月、出勤日数が73日以上、120日以下の場合は有給日数4日
?勤続年数が30ヶ月、出勤日数が121日以上、168日以下の場合は有給日数6日
?勤続年数が30ヶ月、出勤日数が169日以上、216日以下の場合は有給日数9日
?勤続年数が30ヶ月、出勤日数が217日以上の場合は有給日数12日

…というように、有給の規定日数がありますが、入社日と出勤日を入力すると有給日数が出るようにしたいのです。

上記のものは78ヶ月まで表にしてありますので、イメージ的にVLOOKUP関数みたいなもので参照できればいいのですが、これ以上自分では分からなくて(ーー;)

是非、皆様のお力を貸してくださいますよう、宜しくお願い申し上げます。

コメント(18)

最初
全て
最新の40件

[1] mixiユーザー 06月24日 15:00

最初からいっぺんに15種類全部をやらず、１つをやってみませんか？

それが出来れば５つ出来るし、全部も見えてくると思いますよ。
　

[2] mixiユーザー 06月24日 15:09

fenwickさん

イメージをお伝えするために15個書きましたが、数字が違うだけで全部同じ事を表しています。

表し方さえ分かれば、後は全部数式のコピーで大丈夫なので

[4] mixiユーザー 06月24日 15:56

ta- さん。

VLOOKUP関数みたいなもので参照できればいいということなのでVLOOKUP関数で参照する方法は以下の通りで如何でしょう？

別表に勤続月数と出勤日数をあわせたカラムと有給日数のカラムを用意し、
勤続月数と出勤日数をあわせた条件をキーにVLOOKUPで有給日数を取得する。

条件を複合するのは、下記トピで八神かかしさんが説明されてますので参考にして下さい。
http://mixi.jp/view_bbs.pl?id=43605246&comm_id=2106

また、余計なお世話かもしれませんが、同じような内容のトピを作成されてますよね。
http://mixi.jp/view_bbs.pl?id=43527243&comm_id=2106
まずコチラを完結するのが筋ではありませんか？

[5] mixiユーザー 06月24日 20:24

この質問は、シンプルだけど、そう簡単には行かないようです。
(コメントした人は、判って…)

まず、以下のことを考えなければなりません。
・表を使うのか、使わないのか？
　(表を使わない場合、かなり面倒で、正確性に欠け、非効率的となります。)
・表を使うなら、どういう表を作成すれば良いのか？
・表から検索する関数は、何を使うのか？
・検索値は、完全一致なのか、近似値(最大値)なのか？
　(完全一致であれば、簡単ですが…)
・「勤続年数」を計算する関数は？
・「勤続年数」を計算する基準日はいつか？(たぶん2009/4/1？)

表の作成は、
1)２つの条件(勤続年数,出勤日数)を羅列した表
2)「勤続年数」を縦軸、「出勤日数」を横軸としたマトリックス表
の2つが考えられます。

1)であれば、SUMPRODUCT関数で出来ると思いますが、こちらはオススメしません。
2)であれば、VLOOKUP関数(または、OFFSET関数、または、INDEX関数)とMATCH関数
で出来ると思います。

http://www.eurus.dti.ne.jp/~yoneyama/Excel/kansu/vlookup.htm#vlookup-match
ヒントは、検索の型を省略です。

また、

>勤続年数が6ヶ月、出勤日数が48日以上、72日以下の場合は有給日数1日

は、

勤続年数が6ヶ月以上(18ヶ月未満)、出勤日数が48日以上(72日未満)の場合は有給日数1日

と考え、マトリックス表を作成すると良いでしょう。

[6] mixiユーザー 06月24日 21:00

もう一つ、
勤続年数が6ヶ月未満、または、出勤日数が48日未満の場合は、
有給日数が常に、0日なので、

IF関数(とOR関数)で処理するも良し、
マトリックス表に盛り込むも良し。

ちなみに、マトリックス表に盛り込む場合は、

勤続年数が0ヶ月以上(6ヶ月未満)、出勤日数が0日以上(48日未満)の場合は、有給日数0日

[7] mixiユーザー 06月24日 21:04

間違えました。

勤続年数が0ヶ月以上(6ヶ月未満)の場合は、全て有給日数0日
出勤日数が0日以上(48日未満)の場合は、全て有給日数0日

ですね。

[8] mixiユーザー 06月24日 21:05

適当に作ってみました。
こんなんでどうでしょう？

ただ、私自信がこんな感じかな、という風につくったので
上にもあるとおり表形式がいいのか、変えたほうがいいのかはお任せします。

[9] mixiユーザー 06月24日 21:07

補足

A列が勤続月数、１行目が出勤日数です。
A14に勤続月数、B14に出勤日数を入れます。

勤続月数は適当に計算してください。
基準日がわからなかったので。

[10] mixiユーザー 06月24日 21:18

＞しのさん

いい感じです。
ただ、G2は、7の間違いですよね？

[11] mixiユーザー 06月24日 21:24

検索キー無しだと、

=VLOOKUP(A14,$A$3:$G$10,MATCH(B14,$B$1:$G$1)+1)

となります。

[12] mixiユーザー 06月24日 22:26

>10 たつや.comさん
>＞しのさん
>ただ、G2は、7の間違いですよね？

はい、７です。なんで８になってるんだろう？ｗ

>11 たつや.comさん
MATCH関数ってありましたね。ほとんど使わないので
頭の中から抜けてました。勉強になります。

[13] mixiユーザー 06月25日 11:30

みなさん、こちらから質問しておいて返事が遅くなり申し訳ございませんm(__)m
こんなにたくさんのお返事ありがとうございます

＞ねこ丸様
ありがとうございます。是非参考にさせていただきます。
トピックの作成に関しては、マナーがよく分かっていなくて申し訳ございません

前回のトピックが全然回答が無かったのと、自分の中では新たに次のステップに進んでいると解釈しため、このような形を取ってしまいました。
本当にごめんなさい。

＞たつや.com様
ありがとうございます

《Ans.》
・表は使用します
・ A1に《=TODAY()》で本日の日付を表示
　 A4から入力できる表を作成

　 A列：氏名
　 B列：入社日
C列：入社月数《=DATEDIF(B4,$A$1,"m")》
D列：出勤日数
E列：有給日数《ここの関数が知りたいです》
F列：前年有給繰越日数
G列：今年度取得日数
H列：今年度残日数《=E4+F4-G4》※次年度になったら、H列のデータをF列に貼り付ける
　 I列：次年度繰越日数《=IF(H4<0,0,IF(H4<E4,H4,E4))》

・Sheet2に表を作り、E列に関数に使用したいです。
・検索値は、近似値になります。
・「勤続年数」を計算する関数は C列の入社月数《=DATEDIF(B4,$A$1,"m")》になります。
・「勤続年数」を計算する基準日ですか？入社日…ですかね？はっきり分からずごめんなさい(-_-;)

教えていただいたやり方で早速やってみますね！
ご丁寧にありがとうございました

＞しの様
ありがとうございます

ねこ丸様やたつや.com様に教えていただいた方法と一緒に、これから早速やってみます

この問題が解決できたら、
◎使用日数は繰り越し分より消化されていく
◎欠勤日数が出勤すべき日数の2割以上の場合は無し
…という条件も付け足したいんですが、こちらは厳しいですかね？？

もしお分かりになれば、教えてください。

またUPさせていただきますね！

[14] mixiユーザー 06月25日 18:20

>◎使用日数は繰り越し分より消化されていく

これは、意識しなくても良いのでは？
それより、次年度の繰越日数の上限や繰越の期限(次年度まで？)を意識すべきかと…

>◎欠勤日数が出勤すべき日数の2割以上の場合は無し

例えば、8のしのさんの表の場合、

=IF(欠勤日数セル>=OFFSET($A$1,0,MATCH(B14,$B$1:$G$1))*0.2,0,VLOOKUP(A14,$A$3:$G$10,MATCH(B14,$A$1:$G$1)))

[15] mixiユーザー 06月26日 12:05

たつや.com様
繰越日数の上限ですが、繰り越せるのは｢前年度に付与された有給日数だけ｣との事です。
たとえば、2005年に10日、全く使わないで一年半後に11日、有給が付与されたとします。（この時点で有給は21日）

次の二年半の時点で12日付与された時の繰越分は、11日だけになります。

説明がへたですいません

分かりますか？？

[16] mixiユーザー 06月26日 19:28

分かりますよ。

13>I列：次年度繰越日数《=IF(H4<0,0,IF(H4<E4,H4,E4))》

この式で合っていますね。(ちゃんと反映されていました。)
でも、H列はマイナスになるとは思えないので、
IF(H4<0,0,
の部分は不要と思います。
=IF(H4<E4,H4,E4)

ちなみに、
=MIN(H4,E4)
でも良いですね。(シンプルになります。)

[17] mixiユーザー 06月27日 08:23

> たつや.com様

ありがとうございました

みなさんも本当にありがとうございましたm(_ _)m

みなさん、色んな事知っててすごいですね。
うらやましいです