デートの4分の1を取得するにはどうすればよいですか?
Excel には、日付から四半期を直接取得する関数はありません。しかし、これに対する公式を作成することはできます。
はい、数式を使用して日付から四半期を計算できます。使用できる方法は複数あります。ご存知のとおり、1 年は合計 4 四半期ありますが、実際には、日付からさまざまな方法で四半期を取得できます。
1 つ目は、通常のカレンダー ベースで 1 年を想定し、2 つ目は課税カレンダー ベースで 1 年を想定します。そして今日の投稿では、両方のカレンダーを使用して四半期番号を取得する方法を紹介し、そのための複数の方法も共有します。
それでは、始めましょう。
1. ROUNDUP 関数と MONTH 関数を使用して四半期を取得します
ROUNDUP と MONTH を組み合わせて使用するのが、日付の四半期を見つける最良の方法です。四半期を数値として返します (1、2、3、4 など)。
これが式です。
=ROUNDUP(MONTH(A1)/3,0)
ここでは、日付として 2018 年 5 月 26 日を使用し、数式は結果として 2 を返します。四半期の数値の前に Q を追加する場合は、次の式を使用できます。
="Q-"&ROUNDUP(MONTH(A1)/3,0)
この式はどのように機能するのでしょうか?
この式は 3 つの部分で機能します。
最初の部分では、MONTH 関数を使用し、月番号を返す日付を参照しました。
その後、 2 番目の部分では、今月の数値を 3 で割って、小数点以下の桁を含む数値を求めます。
最後に、 3 番目の部分では、ROUNDUP 関数を使用して四捨五入し (この数値を小数点以下の桁に)、実際の四半期が得られます。
本当のことは次のとおりです。ご存知のとおり、4 つの四半期と 12 か月があります。右？そして、すべての月の数値を 3 で割ると (はい、3 だけです)、次のような結果が得られます。
これらを四捨五入すると、常にその月の四半期の数字が得られます。
2. 会計カレンダーを使用して四半期を取得する
上の例では、日付から四半期を取得する簡単な数式を使用しましたが、場合によっては会計カレンダーを使用する必要があります。
また、会計カレンダー四半期の計算には別の数式を使用する必要があり、この数式では SELECT 関数と MONTH 関数を組み合わせる必要があります。
式は次のとおりです。
=CHOOSE(MONTH(A1),4,4,4,1,1,1,2,2,2,3,3,3)
(ここでは、4月1日に始まり3月31日に終わるインドの会計年度を使用しています)
この月は 5 月で、年は 4 月から始まり、5 月は第 1 四半期に該当する年の 2 番目の月であるため、結果には 1 が返されます。
この式はどのように機能するのでしょうか?
ここでは 2 つの異なる関数を組み合わせており、2 つの部分で動作します。
最初の部分では、MONTH 関数を使用して日付から月番号を取得しました。ここでは 5 が返されます。
その後、 2 番目の部分では、MONTH 関数によって返された月番号に基づいて数値を取得する関数を選択しました。
CHOOSE について詳しくは、こちらをご覧ください。
ご覧のとおり、CHOOSE 関数では 12 個の異なる数値 (4、4、4、1、1、1、2、2、2、3、3、3) を指定しているため、MONTH が数値を返すと、CHOOSE は数値を返します。そのリストの。
取引内容は次のとおりです。
最初の 3 桁は 4 です。つまり、1、2、または 3 (1 月から 3 月) が返された場合は 4 が返され、次の 3 か月間は 1 が返されます (これは、年の税金が 4 月から始まるためです)。
3. VBA コードを使用して日付を四半期に変換します
ここでは、VBA コードを使用して日付を四半期番号に単純に変換します。コードは次のとおりです。
Sub convert2Quarter() Dim Rng As Range For Each Rng In Selection If IsDate(Rng.Value) = True Then Rng.Value = DatePart("q", Rng.Value) Rng.NumberFormat = “General” End If Next Rng End Subこのコードを使用するには、VB エディターに追加するだけです。
次に、日付を四半期数値に変換する必要があるセルを選択し、最後にこのコードを実行します。
このコードがどのように機能するかは次のとおりです…
このコードを実行すると、まずセルに日付があるかどうかがチェックされ、日付の場合は四半期数値に変換されます。
ここでは 2 つの異なる VBA 関数を使用しました。
- ISDATE: セルに日付があるかどうかを確認します。
- DATEPART: 日付を四半期に変換します。
これを確認してください: VBA 初心者向けの役立つマクロ コード トップ 100 | ExcelのVBAとは何ですか
4. 日付から四半期の開始日を取得する
四半期番号は忘れて、四半期の開始日を使用しましょう。
これは、日付が 2018 年 5 月 26 日の場合、四半期番号 2 の代わりに、四半期の最初の月の開始日である 2018 年 4 月 1 日を設定できることを意味します。使用する必要がある式は次のとおりです。
=DATE(YEAR(A1),FLOOR.MATH(MONTH (A1)-1,3)+1,1)
この数式を入力すると、単純に四半期の開始日が返されます。
この式はどのように機能するのでしょうか?
この式は、学期の開始日を与える 4 つの異なる部分で構成されています。
最初の部分では、日付の月番号を返す MONTH 関数を使用しました。
それでは、MONTH が月番号として 5 (その後、その 1 を引いたもの) を返すと、 2 番目の部分で FLOOR はそれを 3 に四捨五入し、次に 1 を加算して、同じ四半期の最初の月 (4 月) である 4 を取得できるのでしょうか。
その後、 3 番目の部分では、YEAR 関数を使用して実際の日付から年を取得しました。
そして4 番目の部分では、DATE 関数を単純に組み合わせて、最初の 2 つの部分の値を組み合わせた日付を作成しました。
5.日付から四半期終了日を取得する
OK、四半期の開始日や数値ではなく終了日が必要です。そのために使用できる式は次のとおりです。
=DATE(YEAR(A2),((INT((MONTH(A2)-1)/3)+1)*3)+1,1)-1
2018 年 5 月 26 日の場合は、四半期の最終日である 2018 年 6 月 30 日に戻ります。
この式はどのように機能するのでしょうか?
この式には 4 つの異なる部分があります。
最初の部分では、INT 関数と MONTH 関数を組み合わせました。 MONTH は日付の月番号を返し、それを 3 で割ります。その後、INT は最も近い整数に丸めます。
最後に、3 を掛けて四半期の終了月である 6 (6 月) を取得し、それに 1 を加えます。
簡単に言えば、式のこの部分は、実際の日付の月を次の四半期の最初の月に変換するだけです。
2 番目の部分では、YEAR 関数を使用して実際の日付から年を取得しました。
その後、 3 番目の部分では、DATE 関数で 1 を使用して日付を完成させます。
ごめん！もう一つ。
4 番目の式では、式全体から 1 を引くので、次の四半期の最初の日の代わりに四半期の最後の日を取得できます。
Excelファイルを取得する
結論
関数を組み合わせるだけで、日付から四半期を計算する簡単な数式を作成できます。この記事では、これについて 4 つの数式と VBA コードを検討しました。
率直に言って、#1 は四半期の日付を見つけるのに最もよく使用され、簡単な方法ですが、必要に応じて残りの日付を使用することもできます。この記事がお役に立てば幸いですが、ここでこれを教えてください。
日付から四半期番号を計算する別の方法をご存知ですか?
コメントセクションで私と共有してください。ぜひご意見をお待ちしております。このヒントをお友達と共有することも忘れないでください。きっと喜んでくれると思います。