Excel。25日までは翌月分。それ以降は翌々月分として月を求めたい【The month after next】

Excel。25日までは翌月分。それ以降は翌々月分として月を求めたい

＜MONTH+EDATE関数＞

4月25日までなら、翌月の5月に支払。

4月26日以降なら、翌々月の6月に支払。

このように何月に支払いをすればいいのかがわかる一覧表をつくりたい。

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

目視ではちょっと大変ですよね。

A列に取引日が入力されています。

そして支払月ですが、

B2には

=MONTH(EDATE(A2-25,2))

という数式が設定されています。

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

この数式だけで、25日までは翌月、それ以降は翌々月を求めることができます。

この数式のポイントは取引日から-25していることです。

C列に取引日から-25した日付を表示してみましょう。

A5の2025/6/25は2025/5/31となっています。

A6の2025/6/26は2025/6/1となっています。

これで、25日を境目とする条件を満たすことができます。

そして、EDATE関数の2つ目の引数を2とすることで、２か月後の日付をつくることができます。

その2か月後の日付からMONTH関数で月を抽出すれば、完成です。

Excel。日付から月を求めるにはMONTH関数をつかいます。【MONTH】

Excel。日付から月を求めるにはMONTH関数をつかいます。

＜関数辞典：MONTH関数＞

MONTH関数

読み方： マンス　　

分類： 日付時刻 

MONTH(シリアル値)

日付から月を算出します

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。日付から、作業列（条件）をつくらないで、月別集計をしたい。【totalling】

Excel。日付から、作業列（条件）をつくらないで、月別集計をしたい。

＜SUM+IF＋MONTH関数＞

A列には、セミナー開催日が入力されている表があります。

B列には、セミナー別に参加した人数が入力されており、E列に、月別ごとの集計をしたいわけです。

そこで、1つの数式だけで月別集計を行うことはできないのでしょうか。

月別集計を行いたい場合、日付から何月なのかがわからないと集計することができません。

作業列として、MONTH関数をつかって、月を用意する必要があります。

また、期間計算でおこなうとしても、結局のところ、算出するための作業列をつくらないと算出することができません。

実のところ、合計を算出するSUM関数の引数にIF関数を組み込むことで、1つだけの数式で算出することができます。

E2に次の数式を設定しました。

=SUM(IF(MONTH($A$2:$A$10)=D2,$B$2:$B$10,0))

あとは、オートフィルで数式をコピーしました。

このように、1つの数式だけで、月別集計を算出することができました。

使用している関数は、SUM関数とIF関数とMONTH関数という比較的、馴染みのある関数だけで算出することができました。

数式を確認しておきましょう。

IF関数の論理式。

MONTH($A$2:$A$10)は、配列関数ですが、スピル機能が搭載されたExcelだと、配列関数と意識することなく、数式をつくることができます。

A2＝D2だったら、B2。

そうでなければ0（ゼロ）とします。

これを、A10までおこなうわけです。

その値を、SUM関数で、和算するというわけです。

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。四半期別に集計したいので、四半期を判定するのにIFS関数をつかってみる

＜MONTH+IFS関数&SUMIF関数＞

売上データを四半期別に集計する場合、売上日がどの四半期に所属しているのかがわからないと、四半期別に集計することができません。

四半期を判定する考え方自体は、シンプルです。

4-6月だったら、「1」それ以外は…というように、IF関数のネストを繰り返す方法も悪くはありませんが、面倒です。

CHOOSE関数を使う方法もありますが、せっかくIFS関数という新しい関数が追加されたわけですから、使わないのはもったいない。

IF関数でネストを繰り返すよりもシンプルに、つくることができる、IFS関数をつかって、今回は、四半期別集計をおこなっていきます。

事前の準備として、D2:D5。第１四半期と表示されていますが、セルの値自体は「1」～「4」としています。

表示形式のユーザー定義をつかって、「第１四半期」と表示させています。

これは、どの四半期に所属しているのかを「1」～「4」の数値で算出するため、SUMIF関数をつかって集計する時に、効率よく数式を作るために行っています。

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

=IFS(MONTH(A2)>=10,3,MONTH(A2)>=7,2,MONTH(A2)>=4,1,TRUE,4)

数式を説明します。

A2の月をMONTH関数で算出します。

その値が10以上。

つまり、10～12月だったら、第3四半期なので「3」と算出させています。

次の条件は、「7以上なのか」とします。

すでに、10以上は除外されますので、7～9月だったら、第２四半期なので「2」と算出させます。

最後のTRUEは、該当しないものはということなので、1～3月なので、第４四半期に当たりますから「4」と算出させることができます。

IF関数のネストで数式を作成するよりも、コンパクトで、しかも他の関数をつかうよりも、わかりやすい数式です。

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

C2:C11にどの四半期に分類されいるのかがわかりました。

E2にSUMIF関数をつかって、集計します。

E2に設定した数式は、

=SUMIF($C$2:$C$11,D2,$B$2:$B$11)

これで、四半期別に集計することができました。

今回は、IFS関数をつかって、四半期がどの四半期に所属しているのか判別しましたが、IFS関数が搭載されていないExcelのバージョンだと当然、IFS関数をつかうことはできませんので、他の関数をつかった方法も知っておくといいかもしれませんね。

さて、最後に、C列。

算出結果が表示されたままでは、カッコ悪いので、算出結果が見えないようにしておきましょう。

C2:C11を範囲選択して、セルの書式設定ダイアログボックスを表示します。

表示形式のユーザー定義にあわせます。

種類に「;（セミコロン）」を3個「;;;」とすることで、文字を非表示にすることができます。

これで、完成です。

Excel関数辞典 VOL.51。MODE関数～MUNIT関数【dictionary】

Excel関数辞典　VOL.51。MODE関数～MUNIT関数

＜Excel関数＞

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

MODE関数

モード

最頻値を算出します。

MODE(数値1,[数値2],…)

MODE.MULT関数

モード・マルチ

複数の最頻値を算出します。

MODE.MULT(数値1,[数値2],…)

MODE.SNGL関数

モード・シングル

最頻値を算出します。

MODE.SNGL(数値1,[数値2],…)

MONTH関数

マンス

日付から月を算出します。

MONTH(シリアル値)

MROUND関数

エムラウンド

指定した数値の倍数で四捨五入

MROUND(数値,倍数)

MULTINOMIAL関数

マルチノミナル

多項係数（ 数値の和の階乗と数値の階乗の積との比）を算出します。

MULTINOMIAL(数値1,[数値2],…)

MUNIT関数

マトリック ユニット

指定した次元の単位行列を算出します。

MUNIT(次元)

Excel。年度別で4～9月を上半期それ以外は下半期として楽に合計値を算出したい【Aggregated semi-annually】

Excel。年度別で4～9月を上半期それ以外は下半期として楽に合計値を算出したい

＜IF+MONTH関数・SUMIF関数＞

日付関係の計算というのは、簡単に算出出来るイメージはするのですが、実際につくってみると厄介だったりします。

次のデータは、ある商品の販売金額を降順にしたものです。

今回は、4-9月を上半期、10-3月を下半期とします。

A1:C11までのデータから年度別上半期合計をF1に下半期合計をF2に算出しております。

今回は、単年の表なので、2020/10/1以前という条件で合算させてしまえば、上半期合計を算出することができますが、複数年のデータの場合、そう単純な数式では対応できません。

算出するにあたり、基本的な考え方としては、一度上半期か下半期かを判断させる必要があります。その後上半期と下半期ごとに合計値を算出していきます。

上半期と下半期を判断させる方法としては、

D2に次のような数式を作ることが多いかと思います。

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

AND関数をつかった数式ですね。

AND関数をつかうと、期間のように、「ここ～ここまで」という条件をつくることができます。

この方法でも上半期と下半期を判断することができますが、AND関数が苦手な人向けというか、Excelならではの数式というものあります。

D2に次のような数式を作成します。

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

この数式でも、上半期と下半期を先程のAND関数同様に算出してくれます。

確かにAND関数は使っていませんが…

(MONTH(B2)>=4)*(MONTH(B2)<=9)　は何をしているのでしょうか？

Excelのスキルアップの一環だと思って確認していきましょう。

それぞれをバラして説明してきます。

 

H2には、(MONTH(B2)>=4)

I2には、(MONTH(B2)<=9)

という数式を設定しております。

条件を満たせば「TRUE」。

満たさないと「FALSE」を返します。

このTRUEとFALSEだとなんだかわかりませんので、H列I列の結果に「×1」してみます。

ご覧のように、TRUEだと「1」。FALSEだと「0」と算出されましたね。

そう、Excelでは、TRUEを1。FALSEを0としているアレです。

これを「×（乗算）」して「1」ならばTRUEとなり条件は成立する、つまり「上半期」と判断できるわけです。

あとは、上半期と下半期ごとに合計値を算出したいので、SUMIF関数を使えば、それぞれを算出することができます。

F1の数式は、

=SUMIF($D$2:$D$11,"上半期",$C$2:$C$11)

F2の数式は、

=SUMIF($D$2:$D$11,"下半期",$C$2:$C$11)

これで4-9月を上半期、10-3月を下半期として、それぞれの合計値を算出することができました。

ところで、算出はできたのですが、D列につくった判断するための途中計算が見えていて資料としては、カッコ悪いですね。

最後に、D列の途中計算結果を表示形式で非表示にしましょう。

D2:D11を範囲選択して、セルの書式設定ダイアログボックスを表示します。

表示形式のユーザー定義で、「;;;」（セミコロン×3）と設定してOKボタンをクリックします。

これで完成しましたね。

日付関係や期間計算は、なかなか数式一発で算出とはいかないケースが多いようですが、自分にあった方法を見つけられるといいですね。

Excel。会員の誕生月ごとの人数を算出したい。【Birthday Month】

Excel。会員の誕生月ごとの人数を算出したい。

＜SUMPRODUCT関数＆MONTH関数とCOUNTIF関数＞

会員が誕生月を迎えたらDMやメールを送りたいけど、その人数を事前に把握しておきたいなど、日付から月ごとの件数を把握するにはどうしたらいいのでしょうか？

次のような顧客名簿があります。

B列には日付が入力されていて、1月だったら何名という結果をF列に算出したいわけです。
最初に、E列の誕生月を説明しておきます。

1月と表示されていますが、表示形式のユーザー定義をつかって、「月」を表示させているだけです。

セルの値は、1から12の数値が入力されています。

今回は、単純にG/標準のうしろに、"月"を追記しています。

最初に紹介する方法は、手間はかかりますが、わかりやすいかと思う方法です。

【MONTH関数とCOUNTIF関数】

日付の状態では何月なのかがわかりませんので、C列に月だけを算出していきます。

日付を抽出するのは、MONTH関数を使えばOKですね。

C2をクリックして、数式を入力していきます。

=MONTH(B2)
あとは、オートフィルを使って数式をコピーします。

これで、月を抽出することができましたので、F列に月ごとに何件あるのか、COUNTIF関数を使って算出していきます。

F2をクリックして、COUNTIF関数ダイアログボックスを表示します。

範囲には、$C$2:$C$16と設定します。オートフィルを使って数式をコピーしますので、絶対参照を設定しておきます。

検索条件には、E2を設定します。
仮にE2に「1月」と入力していると、合致しないので件数を算出することができません。

そのために、先程、表示形式のユーザー定義を使って「～月」と設定したわけです。

このように、月ごとの件数を算出することができました。
しかしこの方法は、一度月を算出させる必要があるので、わかりやすい反面、少々面倒です。

【SUMPRODUCT関数＆MONTH関数】

一発で算出する方法もあることはあるのですが、ちょっとヤヤコシイ。
G2に次の数式を作成します。

=SUMPRODUCT((MONTH($B$2:$B$16)=E2)*1)

オートフィルを使って数式をコピーしてみると、同じ結果になります。

数式を説明してきます。

SUMPRODUCT関数は、範囲の積を合計した結果を算出する関数なのですが、この説明は後回しとして、引数で使用している、MONTH関数から説明していきます。

I2に引数のところだけで数式をつくって確認してみましょう。

B2:B16のそれぞれのデータが、E3すなわち、2月と合致しているのかを算出しているのがI列です。

合致しているなら、「TRUE」。合致していないなら「FALSE」と算出されるわけです。

ここまでが、MONTH($B$2:$B$16)の処理。

なぜ、MONTH($B$2:$B$16)に「×1」しているのかというと、TRUEやFALSEのままでは合算することができないので、「×1」をすると、TRUEは、1。FALSEは、0と算出されます。

それが、J列。
あとは、SUMPRODUCT関数をつかうことで、この範囲を合算してくれるという仕組みです。

ということで、
=SUMPRODUCT((MONTH($B$2:$B$16)=E2)*1)
という数式でも、算出することができます。

Excel。4月-6月を第1四半期として、四半期別集計を算出したい。

Excel。4月-6月を第1四半期として、四半期別集計を算出したい。

＜INT+MOD+MONTH関数,SUMIF関数＞

ピボットテーブルを使えば、”秒殺”で四半期別集計は算出することができるのですが、表が出来上がっていて、該当のセルに結果を表示したい場合など、ピボットテーブルを使用しないで、算出させたいというケースもあります。

そこで、今回は、4月-6月を第1四半期として、四半期別集計を算出していきます。

次の表があります。

E列の四半期ですが、表示形式をつかって、第○四半期と表示させています。

E2:E5までは、1～４の数値のみがセルに入力されています。

さて、どのようにしたら、4月から6月を第1四半期と判断することができるのでしょうか？

SWITCH関数など4ならば1というような設定をしてもいいのですが、これだと多くの引数が必要になってしまい、わかりやすい反面、効率がわるいです。

そこで、次の手順を踏むことで4月から6月を第1四半期と判断する計算手順があります。

H列には、1月から12月までの数値が入力されています。
H2の1は1月を意味しています。

最初に行うのは、I列のマイナス4を行います。
マイナス3では？と思うかもしれませんが、マイナス3にすると、ズレが生じてしまい、上手くいきません。

I2の数式は、
=H2-4

2番目は、
1年は12カ月なので、12で除算しますが、必要なのは、除算した結果の”余り”なので、MOD関数を使います。
J2の数式は、
=MOD(I2,12)

3番目は、四半期が3カ月分で構成されているので、3で除算します。
K2の数式は、
=J2/3

4番目は、誤差を調整しますので、+1をします。
L2の数式は、
=K2+1

最後は、整数部分だけが必要なので、INT関数で整数部分を取り出します。
M2の数式は、
=INT(L2)

このような手順によって、4月から6月を第1四半期と判断することができます。
これをまとめて数式化しています。

C2をクリックして、次の数式を作ります。
=INT(MOD(MONTH(A2)-4,12)/3+1)
手順とは逆で、INT関数から作成しますので、注意しましょう。

算出出来たら、オートフィルを使って数式をコピーします。

あとは、この結果を使って、集計します。

F2をクリックして、SUMIF関数ダイアログボックスを表示します。

範囲には、$C$2:$C$20
検索条件には、E2
合計範囲には、$B$2:$B$20

F2の数式は、
=SUMIF($C$2:$C$20,E2,$B$2:$B$20)
当然、手入力で数式を作成してもOKです。

それでは、オートフィルを使って数式をコピーします。

これで、4月から6月を第1四半期として集計することができました。

簡単なようですが、これもクセがありますので、パズルのような手順を踏んで数式を作っていくといいですね。

面倒だと感じたら、ピボットテーブルを使って算出してみると、いいかもしれません。

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。20日締めの支払月を算出すると、意外にひっかかってしまう。【Date of payment】

Excel。20日締めの支払月を算出すると、意外にひっかかってしまう。

＜MONTH & EDATE関数＞

Excelは様々な計算を得意としているアプリケーションですが、
日付や時間については、
ちょっと簡単には算出することが難しいことがあります。

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

20日締めだとして、締め月はいつなのか？というのを算出しようとする場合でも、
一つの関数で簡単に算出とはいきません。

当然、MONTH関数で”月”を算出したところ意味がありませんね。
20日より前なのか後なのかでわかれるわけですからね。

それならば、DAY関数を使って、
日にちを算出して、
それを条件としてIF関数を使って算出する方法もありますが、

=IF(DAY(A2)<=20,MONTH(A2),MONTH(A2)+1)

という長い数式になって面倒ですね。

【EDATE関数を使ってみる】

この場合、日付関係の関数で、ほとんど使う機会のない、
「EDATE関数」とMONTH関数を組み合わせることで、
先ほどの数式よりも、簡単に数式を作ることができるのです。

ちなみに、EDATE関数は、指定月後（前）の同日の日にちを算出できる関数です。

D2にEDATE関数ダイアログボックスを表示して確認しておきましょう。

開始日には、A2
月には、1としておきます。
では、OKボタンをクリックします。

D2の数式は、
=EDATE(A2,1)

算出された結果はシリアル値で表示されてしまうので、
表示形式を日付に変更しましょう。

表示が日付になりましたね。2018/9/4と算出されています。

たったこれだけのことなので、
あまり使うことがない関数のEDATE関数ですが、

MONTH関数と一緒に使って数式をつくると、
EDATE関数ってこんなことにも使えるだなぁ～と。

では、C2をクリックして、MONTH関数ダイアログボックスを表示します。

シリアル値には、EDATE関数をネストします。

ポイントは、
EDATE関数の開始日の引数にマイナス20を追加するのを
忘れずに設定しておきましょう。

あとは、OKボタンをクリックします。

C2の数式は、
=MONTH(EDATE(A2-20,1))
となっています。

では、数式をコピーしておきましょう。

これで、締め月はいつなのかを算出することができました。
なぜ、マイナス20したのかも、確認しておきましょう。

D2に購入日から－20した日付を算出してみます。

このように算出されるわけですが、
この方法を使えば、仮に10日締めや25日締めの場合は、
10を減算するか、25を減算すればいいわけなので、
現場に合わせて調整もしやすいと思います。

日付系の関数は、なかなか、
応用する使い方が限られるところがありますが、
異なる関数と組み合せによって、
今回のように数式をコンパクトにすることができることもありますので、
機会があれば、様々な関数を組み合わせてみると、
色々発見があるかもしれませんね。

Excel。6月に会員登録した人数を知りたいけど、楽な方法ないかな？【COUNT】

Excel。6月に会員登録した人数を知りたいけど、楽な方法ないかな？

＜SUMPRODUCT+MONTH関数＞

次のような顧客情報があります。

登録月が6月の人に年間更新の有無を確認したいそうで、
6月に登録した人数を把握したいというのが目的。

普通に考えるならば、D列にMONTH関数を使って、
6と算出されたものの数を数えるというのが、結構基本的な考え方だと思います。

一応やっておきましょう。

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

シリアル値には、C2を入力してOKボタンをクリックして、
オートフィルで数式をコピーしましょう。

あとは、F2にCOUNTIF関数を使って算出させます。

なおF1の6月は、表示形式のユーザー定義で”月”を表示させているだけで、
セルの内容は、6なので、F1を使って算出していきます。

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

範囲には、D2:D2
検索条件には、F1
では、OKボタンをクリックしましょう。

数式は、
=COUNTIF(D2:D12,F1)

このように3名と算出することが出来ました。

この場合でもOKなのですが、次の方法でも算出することが出来ますので、
参考になれば幸いです。

【SUMPRODUCT関数を使ってみる】

D列にMONTH関数で算出した結果を数える方法ではないやり方なのですが、SUMPRODUCT関数を使う方法です。

SUMPRODUCT関数は、掛け算の結果を合計する関数ですね。

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

配列1には、MONTH(C2:C12)を入力して、OKボタンをクリックします。

数式は、このようになっています。
=SUMPRODUCT(MONTH(C2:C12))

ただ、これだけでは当然ダメで、次のように数式をアレンジしていきます。

=SUMPRODUCT((MONTH(C2:C12)=F1)*1)

このように、先ほどと同じように、3と算出することが出来ましたね。

どうして算出することができたのでしょうか？

MONTH(C2:C12)=F1
MONTH(C2)=F1が合致したらTRUE。
合致しなければ、FALSE。と判断が出ます。

では、E列を追加して、確認してみましょう。

E2には、
=MONTH(C2)=$G$1
という数式を作り、オートフィルで数式をコピーしてみると、
G1、つまり、6と合致しているところは、TRUEが算出されて、
合致しないところは、FALSEが算出されます。

TRUEは1。FALSEは0。
というようにExcelでは決まっているのですが、
このままでは計算が出来ないので、×1してあげることにより、
数値に変換することが出来ます。

E2の数式を次のように修正して、
オートフィルで数式をコピーしてみましょう。

=(MONTH(C2)=$G$1)*1

1と0の数値になりましたね。
こうなれば、この範囲の合計値ですから、3になったことがわかりますよね。

今回のように、一度月を算出してその件数を数えてもいいですが、
SUMPRODUCT関数を使うことでも、算出することができますよ。