Excelの隠し関数DATEDIF

この記事では、excel に関する関連知識を紹介し、主に DATEDIF 関数を中心とした隠し関数の関連事項を紹介します。一緒に見ていきましょう。皆様のお役に立てれば幸いです。

関連する学習の推奨事項: excel チュートリアル

Excel には隠し関数と呼ばれる関数の種類があります。関数リストとは何ですか? Excel では? これらは見つかりませんし、Microsoft のヘルプ ファイルにも関連する手順はありませんが、強力なだけでなく、仕事でも広く使用されています。

DATEDIF は Excel に存在する魔法の隠し関数ですが、Excel のヘルプ ファイルには見つかりません。

今日は、この魔法のような隠された関数を見ていきます。

1. DATEDIF 関数の詳細説明

DATEDIF の基本構文は次のとおりです:

DATEDIF (開始日、終了日、間隔の種類)

このうち、start_date と end_date パラメータは 2 つの日付であり、前者が後者より大きくてはなりません。

unit には次の 6 つのパラメータがあり、以下の表に示すように、さまざまな差を計算するために使用されます。

DATEDIF のパラメータ

日常的なスペルでは、関数名の真ん中の D を見逃して DATEIF に変更してしまう人がいます。これは間違いであり、入力が間違っていると、 Excel システムではプロンプトが表示されません。

この機能語の覚え方は簡単です。DATEDIF は Date Different の略称であり、異なる日付として翻訳されます。

2. 2 つの日付間の年、月、日の間隔を計算します

下図に示すように、これは DATEDIF の従来の使用法であり、これら 6 つのパラメータの実際の意味は次のとおりです。図と組み合わせて、 のデータを説明します。

DATEDIF の一般的な使用法

まず、セル D16 と D24 にそれぞれ次の数式を入力し、それぞれセル D21 と D29 にコピーします。さまざまなパラメータの違い:

=DATEDIF(E16,F16,C16)

=DATEDIF(E24,F24,C24)

D24 セル、パラメータ "Y" を探しています2017年と2020年だけで年の差は3あるはずですが、2017/7/28から2020/2/8までは、先に2019/7/28まで2年が経過しており、必要な2020/2にはまだ達していません。 / 8. 1年経過すると2020/7/28となり終了日を超えるため、結果は2を返し、3を返すことはできません。

セル D25、パラメータ「M」、2017/7/28 から 30 か月後には 2020/1/28 に達し、さらに 1 か月後には 2020/2/28 に達し、終了日は 2020/2/8 であるため、結果は 31 ではなく 30 のみになります。 「全体の年数」と「全体の月数」の「全体」という言葉の意味をよく理解する必要があります。

セル D26、パラメータ「D」は、日数の差を計算するために 2 つの日付を直接減算することと同等です。

セル D27、パラメータ「MD」、この計算では月と年は無視されます。これは、start_date を end_date より前の最も近い日付にプルすることと同じです。つまり、2017/7/28 を 2020/2/8 より前の 28 日、つまり 2020/1/28 に最も近い日付に近づけ、2020/1/28 と 2020 の間の距離を計算します。 /2/8 日差は11日です。

セル D28、パラメーター "YM" は、日と年を無視して月全体の数を計算します。これは、2017/7/28 を 2020/2/8 よりも前の最も近い 7 月 28 日にすることと同じになります。 2019/7/28 と、2020/2/8 との「全体」の月数の差、つまり 6 か月を計算します。

セル D29、パラメーター "YD" は、年の計算における日数の違いを無視します。これは、start_date を同じ月の最も近い日付にプルし、end_date の前日にすることと同等です。つまり、2017/7/28 を 2019/7/28 に近づけて、2019/7/28 と 2020/2/8 の日数の差である 195 日を計算します。

「MD」「YD」パラメータを使用して日差を計算する場合、閏年の存在により理想値と1日の差が生じる場合がありますが、通常はこのような状況にはなりません。私たちの日常の使用に影響を与えます。

3. 年、月、日全体の違い

下図に示すように、2017/7/28 ～ 2020/7/27 と 2017/7/28 ～ 2020 /7 と /28 の比較がリストされていますが、end_date は 1 日だけ異なりますが、結果は大きく異なります。計算原理は同じですが、「全体」という言葉の意味を理解する必要があります。

年、月、日全体の違い

これほど多くのパラメーターをどうやって覚える必要があるのでしょうか?まず、この関数の役割を知り、各パラメータの計算原理を理解する必要があります。仕事で頻繁に日付を計算する必要がある場合は、印刷して机の横に貼ってすぐに参照できます。

4. ケース:勤続年数の計算

今日が2019/7/28で、各従業員の勤務開始日が下図のC列の通りだとすると、それぞれの人の勤続年数は何ですか？勤続年数はm年nヶ月で表すことができます。

優先順位の計算

は段階的に実行できます。まず「全体の年」の数を計算し、次に「全体の月」の数を計算します。月数を計算するときは、12 か月が 1 年となるため、月数の最大値が 11 を超えないことに注意する必要があります。つまり、月数を計算するには年の存在を無視する必要があります。 。では、どのパラメータを計算に使用すればよいのでしょうか?

前セクションで説明した DATEDIF のパラメーター比較表からわかるように、パラメーター "Y" は整数の年数を計算するために使用され、パラメーター "YM" は整数の月数を計算するために使用されます。年に関係なく。したがって、セル D51 の関数式は次のように記述できます。

(左右にドラッグして完全な式を表示します)

=DATEDIF(C51,”2019/7/28″,”Y ”)&” 年 “&DATEDIF(C51,”2019/7/28″,”YM”)&” 月”

セル範囲 D54:D56 を見てみましょう。計算結果は次のように異なります。たった1日の違い。したがって、DATEDIF を使用するときは、「全体」という概念を常に念頭に置く必要があります。

さらに、DATEDIF の Y、M、および D パラメーターは大文字と小文字の両方です。

5. 事例：年次有給休暇日数の計算

「年次有給休暇に関する規程」では、勤続年数が1年以上通算10年に満たない場合には、年次有給休暇を取得することが定められています。休暇は 5 日、従業員の経験が 20 年未満の場合、年次休暇は 10 日、従業員の経験が 20 年を超える場合、年次休暇は 15 日です。

同様に、今日が2019/7/28だとすると、各従業員の年次有給休暇は何日になるでしょうか？

実は、この質問は前のセクションの場合よりも単純で、各従業員の勤続年数を知る必要があるだけです。

次の図に示すように、セル D66 に次の数式を入力して、各従業員の勤続年数を計算します。

=DATEDIF(C66,DATE(2019,7,28) ,” Y”)

年次休暇日数の計算

ショートカット入力方法を使用して数式で日付を表現すると、上記セクションの「DATEDIF(C51,"2019/7/28″,"Y")」のように二重引用符を追加する必要があります。二重引用符の使い方をマスターできない場合は、規律ある方法で DATE 関数を使用してください。エラーがないことを確認するためです。

D 列の年数に基づいて、法定年次休暇の日数を計算できます。セル E66 に次の数式を入力します。

=LOOKUP(D66,{0, 1,10,20},{ 0,5,10,15})

関連学習の推奨事項: Excel チュートリアル

以上がExcelの隠し関数DATEDIFの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。