Excel。何か月後の何日はEDATE関数でつくれないので、どうしたらいいの【how many months later】

Excel。何か月後の何日はEDATE関数でつくれないので、どうしたらいいの

＜DATE+YEAR+MONTH関数＞

EDATE関数をつかうことで、日付から何か月後の同日をつくることはできます。

ところが、日付から何か月後の何日という日付をEDATE関数ではつくることができません。

どのようにしたらいいのでしょうか。

EDATE関数で出来ないならば、シンプルに、日付を作ることを考えるといいわけです。

A2に2024/6/3と入力してあります。

C2に3か月後の15日の日付を作る場合は、次のように数式を設定すればいいわけです。

=DATE(YEAR(A2),MONTH(A2)+3,15)

とてもシンプルな関数で対応できます。

DATE関数は、日付を作る関数

YEAR関数は、日付から年を抽出する関数

MONTH関数は、日付から月を抽出する関数

このMONTH関数で抽出された数値に「+3」すれば、3か月後の月を算出できます。

日付には、直接「15」と設定します。

これで、3か月後の15日を作ることができました。

どうしても何か月後ということから、EDATE関数と考えがちですが、単純に当月に「+3」をして、日付には「15」と入力すればいいわけです。

Excelの様々な関数の読み方や引数などを紹介。今回は、YEAR関数～YEN関数です。【dictionary】

Excelの様々な関数の読み方や引数などを紹介。今回は、YEAR関数～YEN関数です。

＜Excel関数辞典：VOL.92＞

今回は、YEAR関数～YEN関数までをご紹介しております。

YEAR関数

読み方： イヤー　　

分類： 日付時刻 

YEAR(シリアル値)

日付から年を算出します　

YEARFRAC関数

読み方： イヤーフラクション　　

分類： 日付時刻 

YEARFRAC(開始日,終了日,[基準])

2つの日付の間の期間を年数で算出します　

YEN関数

読み方： エン　　

分類： 文字列操作 

YEN(数値,[桁数])

数値を四捨五入して、円記号（￥）を付けた文字列に変換する　

Excel。日付から、手早く年度別（4月～3月）で区分けするにはどうしたらいい【year】

Excel。日付から、手早く年度別（4月～3月）で区分けするにはどうしたらいい

＜YEAR+MONTH関数＞

年ごとに、集計するには、年ごとに区分けできればいいので、YEAR関数だけで年を算出すれば、区分けすることができます。

ただ、4月～翌年の3月までの年度となると、YEAR関数だけで簡単に算出することはできません。

では、どのようにしたら、手早く年度で区分けすることができるのでしょうか。

そこで、B2には、次の数式を設定します。

=YEAR(A2)-(MONTH(A2)<4)

オートフィルで数式をコピーしています。

YEAR関数とMONTH関数を組み合わせてつかうことで、手早く算出することができます。

なお、算出結果が、日付で表示されていた場合は、表示形式を標準にして、「シリアル値」に戻してあげる必要です。

今回の年度を算出するにあたり、ポイントになるのは、翌年の1月～3月です。

4月～翌年3月までの年度は、年と比べて、3ヶ月ズレているわけです。

そのため、IF関数をつかって、1月1日～3月31日までは、前年にするような数式をつくることが多いかと思います。

ただ、その数式は、少々長くなり可動性が悪化しやすくなる傾向にあります。

今回は、単純に3ヶ月前にスライドさせるようにした数式を作ってみました。

この数式を説明すると、

YEAR関数は、年を算出しますので、A2の場合は、「2023」と算出されます。

そして、「(MONTH(A2)<4)」。

MONTH関数は、月を算出することができる関数なので、A2は3月なので、「3」と算出されます。

「3<4」算出結果が4より小さいかを判断させています。

この「(MONTH(A2)<4)」は成立していることがわかります。

つまり、この「(MONTH(A2)<4)」は、成立しているか、していないかという判断の数式なわけです。

成立しているということは「TRUE」なわけですね。

Excelでは「TRUE=1」ということですから、「(MONTH(A2)<4)」は「1」と算出されるわけです。

こうすれば、年から「1」を減算しますから、前年の数値を算出することができるというわけです。

A3の4月は、「(MONTH(A2)<4)」が成立しませんので「FALSE」という判断です。

Excelでは「FALSE＝0」ですから、YEAR関数で算出された結果そのものということになります。

TRUE=1。

FALSE=0。

というのを知っていると、Excelのスキルの幅が広がりますので、色々試してみるといいかもしれませんね。

Excel。IF関数もつかわずに、手早く締め日から支払日を算出したい。【Closing date】

Excel。IF関数もつかわずに、手早く締め日から支払日を算出したい。

＜DATE・YEAR・MONTH・DAY関数＞

締め日と支払日。なかなかアレコレ考えて算出する必要があるので面倒だったりします。

締め日と支払日の日にちだけを入力するだけで、支払日を算出できるようにしていきます。

今回は、10日締めの翌20日払いのケースで、紹介していきますが、土日祝日だったら、それをよけるようにするというのは、除いています。

A列には、購入た日が入力されています。

E2には、10日締めなので、「10」と入力しています。

E3には、翌月20日払いなので、「20」と入力しています。

B列の支払日に数式を設定します。

=DATE(YEAR(A2),MONTH(A2)+1+(DAY(A2)>$E$2),$E$3)

締め日が10日より前だから、あとだからといったことを判断させるためのIF関数は使用していません。

20日締めの翌25日払いとかでもE2とE3を変更すれば対応できる数式です。

この数式を説明していきます。

DATE関数は、日付を算出する関数ですね。引数は、年・月・日を設定する必要があります。

引数の年は、YEAR関数を使用して算出します。

引数の月は、MONTH関数を使用することで「月」の数値を算出します。

A2の場合は「7」と算出されます。

そしてこの数式のポイントになるが、「+1+(DAY(A2)>$E$2」。

翌月の払いなので「+1」するのはイメージしやすいと思いますが、7月29日は8月10日に締めて、翌月である「9月」に支払が発生しますので、さらに「+1」する必要があります。

そこで「+(DAY(A2)>$E$2」で判断させています。

「(DAY(A2)>$E$2」のDAY関数は日付を抽出する関数なのでA2の日にちですから「29」と算出されます。

その値がE2である「20」より大きいという条件が成立しているのか、成立していないのかを判断させています。

「29>20」なので、成立しているので「TRUE」という結果になります。

Excelでは、「TRUE」が「1」で「FALSE」が「0」と設定されていますので、さらに「+1」されて、「9」という数値が算出されます。

これにより、月を「9月」とすることができました。

最後の引数の日ですが、20日なので、「E3」を設定します。

TRUEが「1」でFALSEが「0」というのを今まで使用していた数式に組み合わせてみると意外な発見があるかもしれませんね。

Excel。4月1日～3月31日を年度として、手早く年度ごとにわけるにはどうしたらいい【fiscal year】

Excel。4月1日～3月31日を年度として、手早く年度ごとにわけるにはどうしたらいい

＜YEAR+MONTH関数＞

年を算出するには、YEAR関数をつかうことで、簡単に判別することができます。

ただ、4月1日～3月31日を「年度」として、判別するにはYEAR関数だけというわけにはいきません。

次の表を使って説明します。

B6の2023/3/8までは2022年度ですが、B7の2023/4/22は2023年度と算出したいわけです。

年度の算出で一番ネックなのは、どうやったら、1月～3月を前年の「年」と同じにしたらいいのかということですね。

そのため、YEAR関数だけでは、上手く算出することができないわけです。

IF関数で1月～3月だったら、年をマイナス1するというのでもいいのですが、もっとコンパクトな数式で算出することができます。

A2に次の数式を設定します。

=YEAR(B2)-(MONTH(B2)<4)

説明はあとに回すとして、オートフィルで数式をコピーしたら完成です。

なお、算出結果の表示形式が日付型になってしまうので、表示形式を「標準」に戻す必要があります。

さて、この数式を説明していきます。

前半部分の「YEAR(B2)」は、年を算出しています。算出結果は「2022」です。

C列に算出してみました。

後半部分の「(MONTH(B2)<4」ですが、MONTH(B2)で算出される値は「12」ですが、この数式は、「(MONTH(B2)<4」が成立するのか否かということで使っています。

D列に算出しましたが、結果は、TRUEとFALSEと算出されます。

なぜ、TRUEとFALSEを算出させたのかというと、Excelでは、TRUEを「1」。

FALSEを「0」と定義しています。

この「1」と「0」を使いたかったためです。

なお、TRUEとFALSEですが、「×1」すると、数値に置換できるので、確認のため「×1」したのが、E列です。

こうすることで、1月～3月はTRUE。

すなわち「1」を年から減算しますので、2022となり、年度を算出することができます。

これ以外にも、年度を算出する方法は色々ありますので、一例ということでご紹介させていただきました。

Excel。手早く20日締め翌月末払いの日程表をつくりたい【payment】

Excel。手早く20日締め翌月末払いの日程表をつくりたい

＜DATE・EDATE・DAY・YEAE・MONTH関数＞

Excelの日付計算は、頭の中で考えるように、スムーズにいかないところがあります。

たとえば、次のような、20日締め翌月末払いの日程表をつくるとします。

A列に仕入日を入力すると、締め日と支払日が表示されるように数式を設定するわけですが、手早く作りたいわけです。

B列の締め日は、20日締めなので、20日より前ならば、同月20日ですが、21日以降ならば、翌月20日で表示したいわけです。

なお、今回は、土日祝日は考慮しておりません。

さて、20日より前とか後となれば、IF関数をつかって算出させればいいように思います。

B4にIF関数をつかった数式を設定するとすれば、

=IF(DAY(A4)<=20,DATE(YEAR(A4),MONTH(A4),20),DATE(YEAR(A4),MONTH(A4)+1,20))

確かに間違いではないのですが、当然長くなります。

わかりやすい反面、可読性が悪化します。

そこで、B4に次のような数式でも算出することができます。

=DATE(YEAR(A4),MONTH(EDATE(A4-20,1)),20)

数式がコンパクトになったことで、可読性が向上していますね。

それに、IF関数をつかわないで、算出しています。

締め日の着目点は、「20日前なのか後なのか」ということです。

そこで、MONTH関数の引数を確認します。

MONTH(EDATE(A4-20,1))

MONTH関数は、月を算出する関数です。

EDATEは、月ちがいの同日を算出する関数です。

仕入日から20減算した日付をつかい、EDATE関数で算出された月に「+1」した数値を月として、算出しています。

ケースとして、2022/4/21ならば、20日減算すると2022/4/1なので、月は、4+1で5と算出されます。

2022/5/20が締め日となり、2022/4/20ならば、20日減算すると、2022/3/31なので、月は、3+1で4と算出されます。

よって、2022/4/20が締め日と算出することができるというわけです。

このようなことから、IF関数をつかわないで、算出することができるというわけです。

支払日の数式は、IF関数で算出しても、締め日と同じ方法で算出しても、可動性に変わりは、あまりありません。

C4の数式は、

IF関数ならば、

=IF(DAY(A4)<=20,EOMONTH(A4,1),EOMONTH(A4,2))

EDATE関数ならば、

=DATE(YEAR(A4),MONTH(EDATE(A4-20,3)),1)-1

関数の説明をします。

=IF(DAY(A4)<=20,EOMONTH(A4,1),EOMONTH(A4,2))

この数式で使用している、EOMONTH関数は、月末日を算出する関数です。

20日以前ならば、一か月後の月末を算出し、それ以外は、二か月後の月末を算出することができます。

=DATE(YEAR(A4),MONTH(EDATE(A4-20,3)),1)-1

MONTH関数は、先程と同じで、20日減算した日の、同日の3か月後の月を算出しています。

2022/4/21のケースだと、20日減算するので、2022/4/1で、この同日の3か月後ですから、MONTH関数で算出された月は、「7」。

DATE関数で算出されるのが、2022/7/1。

この日から「-1」することで、前月末日。

すなわち、翌月末日を算出することができるので、2022/6/30と算出することができるわけです。

このように、Excelには、様々な日付の関数が用意されいるので、色々組み合わせてみると数式を改善することができるかもしれませんね。

Excel。年度を算出するならEDATE関数。さらに、2019年5月1日以降も平成31年度にしたい【Year】

Excel。年度を算出するならEDATE関数。さらに、2019年5月1日以降も平成31年度にしたい

＜EDATE関数・YEAR関数・TEXT関数と条件付き書式＞

日付から年だけを表示するなら、YEAR関数を使えば簡単に算出することができますが、年度で表示するには、どうしたらいいでしょうか？

B列の暦年は、1月1日～同年12月31日ですので、B2の数式は、
=YEAR(A2)&"年"
とすることで、簡単に算出することができます。

YEAR関数は、日付から「年」を抽出することができる関数です。

なので、2021/1/1は2021年と表示されています。

あと、「&」をつかうことで、文字結合することができます。

では、年度のC列はどのようにしているのでしょうか？
年度の問題点は、4/1～翌年3/31というように、越年していることです。

YEAE関数では算出することはできなのは、想像できますね。

そこで、使用する関数は、『EDATE関数』
C2の数式は、
=YEAR(EDATE(A2,-3))&"年度"
EDATE関数は、あまり使用頻度が多くない関数だと思いますが、年度を算出する時に大活躍してくれます。

EDATE関数は、指定した日付の前後の月を算出することができる関数です。

EDATE(A2,-3)という計算式は、
A2は、2020/4/1なので、その月の数値は4。「-3」するので、2020/1/1を算出することができます。

仮に、EDATE(A2,1)とすると、翌月同日ということなので、2020/5/1と算出することができます。

年度と暦年は、「3ヶ月」ずれているので、EDATE関数をつかって、3か月前にした日付にして、その年を抽出することで、『年度』を算出することができます。

2020年度とC列は算出されていますが、元号で年度を算出するケースも確認しておきましょう。

D2の数式は、
=TEXT(EDATE(A2,-3),"ggge年度")
としてあります。

YEAR関数だと年の数値を算出しますが、これだと、元号に変更できませんので、表示形式を設定することができる、TEXT関数を使うことで、元号での年度にできます。

しかし、この元号の年度は少々問題を含んでいます。

それは、『令和元年度問題』。
2019年5月1日から令和に改元されましたが、年度としては、平成31年度なので、令和元年度で表示するのではありません。

すると、「-3」しているので、2019/8/1は、平成31年度ではなくて令和1年度と表示されてしまいます。

そこで、IF関数を使ってもいいのですが、数式が煩雑になるので、ここは、条件付き書式で対応する方法をご紹介します。

新たにE列を作りました。
E5の数式は、
=EDATE(A5,-3)
としています。結果は、シリアル値で算出されます。

E5:E9を範囲選択して、ホームタブの「条件付き書式」から新しいルールをクリックして、新しいルールダイアログボックスを表示します。

ルールの種類を「指定の値を含むセルだけを書式設定」を選択します。

ルールの内容は、
セルの値で次の値の間を使用します。43586は2019/8/1のシリアル値です。

43830は、2020/3/31のシリアル値です。
要するに、2019/8/1～2020/3/31の間という条件です。

書式ボタンをクリックします。

セルの書式設定ダイアログボックスが表示されますので、表示形式を「ユーザー定義」にして、種類を「平成31年度」としてOKボタンをクリックします。

先程の新しいルールダイアログボックスに戻りますので、OKボタンをクリックします。

2019/8/1は平成31年度と表示することができました。

あとは、条件付き書式でそれ以外の範囲の設定をします。

先程と違うのは、「次の値の間以外」にします。

表示形式の「ユーザー定義」の種類を「ggge”年度”」とします。

令和元年度にするならば、簡単なことなのですが、これで、平成31年度で対応することができます。

年度はケースバイケースで算出する方法が色々ありますので、それぞれにあった算出方法を見つけてみるといいかもしれませんね。

Excel。オートフィルターを使わずに関数で該当データを抽出するには？【Extraction】

Excel。オートフィルターを使わずに関数で該当データを抽出するには？

＜IFERROR+INDEX+SMALL+ROW関数、IF+AND+YEAR+MONTH関数＞

売上データや納品データなど大きなデータがあって、そこから、請求書などのデータを抽出したい時に、オートフィルターなどを使わずに、数式（関数）で直接データを抽出することができれば、オートフィルターなどを使わない分、作業効率が改善されます。

では、どのように数式を作成したらいいのでしょうか？

次のような表（購入リストという名前のシート）と

転記したいシート（店舗別という名前のシート）を用意しています。

転記したいシートのB1には、店舗名。D1には、年。

そして、F1には、月を入力して、そのデータに合致したものを、表から抽出するということをします。

表にオートフィルターを設定して、抽出条件を加味して、そのデータをコピー&ペーストするという作業で問題はないのですが、例えば、店舗が10店舗とかあると、いちいち抽出していると面倒なので、店舗ごとのシートを作れば、年とか月とかを変更するだけで済むようになるわけです。

【合致するものがあるのかを確認する】

転記したいシートのD列を使って、まずは、表と合致するデータがあるのかどうかを算出させます。

D4をクリックして、次の数式を設定します。

=IF(AND(購入リスト!B2=店舗別!$B$1,YEAR(購入リスト!A2)=$D$1,MONTH(購入リスト!A2)=$F$1),ROW(A1),"")

少し長い数式なので、説明をしていきます。

購入リストシートのB2が、抽出したい店舗名と同じで、年月も同じなのかをAND関数を使って確認しています。
IF関数を使って、真の場合は、この後の使う関数のために、昇順の連番がほしいので、ROW(A1)としています。合致しなければ、空白という数式です。

あとは、オートフィルで適度に数式をコピーしておきます。

【合致したデータを詰めて表示したい】

A4に次の数式を設定します。
=IFERROR(INDEX(購入リスト!$A$2:$D$10,SMALL($D$4:$D$12,ROW(A1)),1),"")
D列のデータがないとエラーが表示されるので、IFERROR関数でエラーを表示させないようにしています。

そして、ポイントになる関数。それが、『INDEX関数』です。

INDEX関数は、指定された列と行が交差するデータを表示することができる関数です。

今回は、交差するセル情報を表示させたいので、INDEX関数の引数は、【配列・行番号・列番号】を使います。

配列は、購入リストシートの$A$2:$D$10。

行番号は、先程算出してあるD列を使うのですが、D4に1と算出しているので、最初は1を使うのですが、2件の場合は、D6の3を使うことで、引数の行番号には3を使いたいわけです。

となると、順々に数値を変更させたいのですが、連番とは限りませんので、SMALL関数を使い、さらにROW関数を使うことで、1番目に小さい数値、2番目に小さい数値と引数に設定することができます。

列番号には、1列目のデータなので、1。

先程の行番号は、SMALL関数とROW関数を使うことで、自動的に引数の数値を変えることができたのですが、この列番号だけは、何列目のデータを抽出していいのか、判断が難しいので、それぞれの列で、修正する必要があります。

つまり、
日付のA4の数式は、
=IFERROR(INDEX(購入リスト!$A$2:$D$10,SMALL($D$4:$D$12,ROW(A1)),1),"")

商品名のB4の数式は、
=IFERROR(INDEX(購入リスト!$A$2:$D$10,SMALL($D$4:$D$12,ROW(B1)),3),"")

購入金額のC4の数式は、
=IFERROR(INDEX(購入リスト!$A$2:$D$10,SMALL($D$4:$D$12,ROW(C1)),4),"")

となっています。

それでは、オートフィルで数式をコピーしてみましょう。

該当するデータを詰めて表示することができましたね。

あと作業用で使ったD列の数値が見えているとカッコ悪いですね。
当然、数式を削除するわけにはいきませんね。

そこで、D4以降には、次のような表示形式を設定します。

セルの書式設定ダイアログボックスを表示して、分類の「ユーザー定義」に、;;;（セミコロン×3）と設定して、OKボタンをクリックしましょう。

:::（セミコロン×3）を設定すると、文字を非表示にできます。

このような数式を使うことで、抽出業務も改善できるかもしれませんね。

Excel。IF関数を使わないで、生年月日から干支を算出方法？！【Zodiac】

Excel。IF関数を使わないで、生年月日から干支を算出方法？！

＜MID+MOD+YEAR関数＞

面白いリクエストがありまして、
「スタッフさんの生年月日から『干支』がわかるようにしたいんだけど～」
とのこと。

IF関数を使ってみたら、わからなくなっちゃって…だそうでして。
ということで、ちょっと考えてみましょう。

次のような表があります。

今年2017年の干支は、酉。
というように表示することができればいいわけですね。

このような場合は、
どのようなルール（パターン）になっているかを見つけることが
出来るのかがカギになります。

干支のルールは、
子丑寅…と始まって12種類で元の子に戻るというルールなので、
12で除算してみて、その余りを使うことで、
どの種類なのかを判断することが出来そうですね。

とりあえず、C列に、余りを求める関数のMOD関数を使って、
生年月日を12で除算してみましょう。

C2にMOD関数ダイアログボックスを表示しましょう。

数値には、年数を入れたいので、年数を算出することができる、
YEAR関数を使って算出させますので、YEAR(A2)

除数には、干支の数である12

それでは、OKボタンをクリックして、オートフィルで数式をコピーしましょう。

D列には、ルール（パターン）確認のため、自分で干支を入力してみました。

これによると、0（ゼロ）つまり、余りがない年は、
申年だということがわかりましたね。

0ならば、申。

1ならば、酉というように算出させるには、
IF関数を使うといいように思えますが…

IF関数のネストが多くなりすぎて煩雑になってしまうので、お勧めできません。

そこで、登場するのが、MID関数を使うと便利ですね。

CHOOSE関数でもいいのですが、少し数式が長くなります。

では、B2にMID関数ダイアログボックスを表示しましょう。

このMID関数は、
文字列の指定の位置から指定の文字数分を抽出することが出来る関数ですね。

文字列には、"申酉戌亥子丑寅卯辰巳午未"

子からじゃありませんよ。ここがポイント。

0が申だったので、申からスタートさせます。

開始位置は、先程確認した、数式を使いますので、MOD(YEAR(A2),12)。

ただこれでは、0になってしまいます。
0文字目では、MID関数が機能しませんので、+1をしてあげるといいですね。

文字数は、1文字を抽出するので、1

それでは、OKボタンをクリックして、
オートフィルで数式をコピーして確認してみましょう。

このように、『干支』がちゃんと算出することが出来ましたね。

ちなみに、B2の数式は、
=MID("申酉戌亥子丑寅卯辰巳午未",MOD(YEAR(A2),12)+1,1)
となっていますね。

なお、CHOOSE関数を使ってみると…
=CHOOSE(MOD(YEAR(A2),12)+1,"申","酉","戌","亥","子","丑","寅","卯","辰","巳","午","未")

今回は、干支というお題でしたが、
文字列の抽出という方法を使うことで、
IF関数を使った時の煩雑な数式から少しは
解放されるケースもあるかもしれませんので、
色々考えてみるといいかもしれませんね。

Excel。Year。4月～3月で年度別集計をしたいけど、どうしたらいい？

Excel。4月～3月で年度別集計をしたいけど、どうしたらいい？

＜SUMIF関数・YEAR関数・MONTH関数＞

Excelって「年別」で集計するのは、比較的簡単なんだけど、
「年度別」で集計しようとした場合は、どのようにしたらいいのでしょうか？
ということを聞かれますので、

今回は、4月～3月の年度別集計の方法をご紹介していきます。

まずは、次の表があります。

A列の日付に基づき、F列にB列の金額を年度別で集計をしていきます。

A列の日付だけで集計するには、
大変なので、年度で区分けをするためのデータを作る必要があります。

C3をクリックして、次の数式を作っていきましょう。

=YEAR(A3)-(MONTH(A3)<4)

たぶん、関数ダイアログボックスを表示するよりも、手入力したほうが、
楽だと思いますが、まだ手入力になれていないようでしたら、
関数ダイアログボックスを使って数式を作っていきましょう。

この式の意味を解説していきます。

問題となるは、翌年の1月～3月で、この1月～3月だったら、
前年にする、つまり-1（マイナス1）する必要があります。

まず、思いつくのが、条件分岐の関数である、
IF関数を使う方法ですが、Excelで1～3という範囲でとなると、条件が煩雑になってしまいます。

今回の目的は、-1（マイナス１）したいだけです。

なので、-1（マイナス1）出来ればいいわけです。
つまり1という数字をどうやってもってくるのか？がポイント。

そこで、(MONTH(A3)<4)なのです。

この計算式は、仮にA3の日付が1月だとすると、1<4。
つまりTRUEという判定が出ます。

そう、Excelでは、TRUEは1。FALSEは0。というアイディアを使って、1を算出させます。

それを、YEARから-1（マイナス1）すれば、1月～3月が、前年の数値になるわけです。

だから、数式は、

=YEAR(A3)-(MONTH(A3)<4)

ということになります。

では、オートフィルで数式をコピーしましょう。

ここまで、算出出来れば、
あとは、SUMIF関数を使って集計してあげればOKですね。

なお、E3:E5には、表示形式のユーザー定義で、0”年度”という設定をしてあります。

では、F3をクリックして、SUMIF関数ダイアログボックスを表示しましょう。

範囲は、算出した年度なので、$C$3:$C$18
検索条件は、E2
合計範囲は、金額の、$B$3:$B$18
あとは、OKボタンをクリックしましょう。

数式は、

=SUMIF($C$3:$C$18,E3,$B$3:$B$18)

ですね。
あとは、オートフィルで数式をコピーしましょう。

このように、年度を求めてあげれば、集計は比較的容易になります。

また、SUMIF関数でなくても、ピボットテーブルや、
小計といった集計方法でも集計できますので、
用途に合わせて、算出してみるといいですね。

Excel。quarter。日付から上期下期そして四半期も一気に入力できる方法

Excel。日付から上期下期そして四半期も一気に入力できる方法

＜IF関数+AND関数+YEAR関数とCHOOSE関数＞

少し前に、年表示ではなくて、年度表示をする方法をご紹介したのですが、

知人から、
「年度だけじゃなくて、ついでに、日付を入れたら、上期・下期。
さらには、どの四半期なのかも、表示してくれるようにしたらいいのに。」

と、いっておりましたので、じゃあ、やってみましょうか。
ということで、今回は下記のような表を作ってみたいと思います。

それでは、下記の表をご覧ください。

今回は、4月1日を年度初日として、翌年の3月31日までを一年度としております。

B列に日付を入力すると、
自動的にC列には、上期・下期が、D列には、第何四半期が算出されるようになっております。

では、まずは、C列から考えてみることにしましょう。

C列は、上期か下期かを判断することになりますから、
ここには、IF関数を使ってあげるといいように思えますね。

そして、条件ですが、4月1日～9月30日が上期にあたるわけですね。

けども、【月】が4月～9月だけでも上期か下期かの判断ができますから、
YEAR関数で算出することができます。

それでは、C2にIF関数ダイアログボックスを表示しましょう。

先に、真の場合と偽の場合を設定しておきます。
真の場合には、”上期”。
偽の場合には、”下期”

そして、論理式を作っていきます。
まず、AND関数ダイアログボックスを表示しましょう。

4月以上、9月以下ですので、

MONTH(B2)>=4　とMONTH(B2)<=9

と設定します。あとは、OKボタンをクリックして、オートフィルでコピーしましょう。

なお、数式は、

=IF(AND(MONTH(B2)>=4,MONTH(B2)<=9),"上期","下期")

となっております。これで、上期と下期を判断することが出来ました。

そして、次の四半期。これが面倒なんですよね。
上期と下期のように判断しようとすると、分岐が多くなって煩雑になってしまいます。
そこで、法則を考えてみましょう。

第と四半期の文字は、&を使って文字連結すればいいとすると、
1～4の数字をどうしたらいいのか？ということになります。

また、1～3が第４四半期。4～6が第１四半期ということになります。

こうなってくると、アレコレするよりも、
CHOOSE関数を使って算出するのが効率的なように感じます。

では、D2をクリックして、CHOOSE関数ダイアログボックスを表示しましょう。

インデックスには、MONTH(B2)と入力します。
そして、値1～順々に4⇒1⇒2⇒3という順番に設定していきます。

なお、数式は、

=CHOOSE(MONTH(B2),4,4,4,1,1,1,2,2,2,3,3,3)

数式だけを見るとわかりにくそうですが、やっていることは単純な関数ですよね。

あとは、第と四半期を付けますので、最終的な数式は、

="第" & CHOOSE(MONTH(B2),4,4,4,1,1,1,2,2,2,3,3,3) & "四半期"

あとは、オートフィルでコピーしてみましょう。

このような関数を作ってあげると、上期と下期。そして四半期を作ることもできますよ。

ただ、4月-3月の場合ですので、ほかの場合は、
修正する必要がありますので、お気を付けくださいませ。