いきなり答える備忘録

Google Workspace・Microsoft 365・LibreOfficeなどに関するメモ

(Excel)平日のみ集計する方法

 Excelで、平日(土日祝日以外)に対応する数値だけ足して合計を求める方法についてです。
 平日とする曜日を(月~金以外に)変えて集計する方法についても紹介します。

集計内容

 はじめに集計内容の確認です。
 左側の表が対象データです。中央には祝日リストがあります。
 これらに基づき、対象データのうち平日(土日祝日以外)の日付の売上の合計を求めるものとします。

 なお、日付とともに曜日を表示(ユーザー定義形式の「yyyy/m/d(aaa)」を適用)していますが、これは結果を理解しやすいようにするためで、集計の手順には影響しません。
 また、Excelの機能だけで日付が祝日かどうかを区別することができないので、このように祝日リストを用意しておく必要があります。

NETWORKDAYS関数を使う方法

 まずは作業列を設けて平日かどうかの判定を行います。
 D列を作業列とし(この例では1列挿入しているため、祝日リストがF列に移動していることに注意)、D3セルに次の数式を入力してD13セルまでフィルコピーします。

 D3セル(下方にフィルコピー)

=NETWORKDAYS(B3,B3,F$3:F$5)

 NETWORKDAYS関数は平日の日数を数えるための関数です。期間の開始日、期間の終了日、そして祝日リストの3つの引数を設定すれば、その期間内の平日の日数が取得できます。これを利用して、期間の開始日と期間の終了日に同じ日付を指定すれば、その日付が平日である場合に「1」、土日祝日である場合に「0」が表示されます。
 絶対参照($)を忘れるとフィルコピーに伴い祝日リストの参照先がずれてしまい、祝日の判定を誤るので注意してください。



 あとはSUMIF関数を使い、D列の値が「1」である行の数値の合計を求めれば完了です。
 答えは16900(=3200+3800+4000+3200+2700)となります。

 H3セル

=SUMIF(D3:D13,1,C3:C13)

WORKDAY関数を使う方法

 上記例とは別の関数を使って平日かどうかの判定を行う方法です。
 D列を作業列とし(祝日リストがF列に移動していることに注意)、D3セルに次の数式を入力してD13セルまでフィルコピーします。

 D3セル(下方にフィルコピー)

=WORKDAY(B3-1,1,F$3:F$5)=B3

 WORKDAY関数は平日の日付を求める関数です。基準日、n日後、祝日リストの3つの引数を設定すれば、基準日から数えてn日後(平日だけをカウントする)の平日を求めることができます。
 つまり上記の式は「B3セルの1日前からみた1日後の平日が、B3セルの日付そのものかどうか」という意味になります。B3セルの日付が平日なら結果はTRUE(真)になりますし、平日でなければFALSE(偽)となります。
 この場合も絶対参照($)を忘れないよう注意してください。



 あとはSUMIF関数を使い、D列の値が「TRUE」である行の数値の合計を求めれば完了です。

 H3セル

=SUMIF(D3:D13,TRUE,C3:C13)

 


 なお、WORKDAY関数は意味がわかりにくいのが欠点ですが、引数の範囲を拡張すればスピルします。
 つまりD3セルの1つの式だけで各日付の一括判定が可能です(H3セルの式は上記と同じ)。

 D3セル

=WORKDAY(B3:B13-1,1,F3:F5)=B3:B13

 式のコピーと絶対参照の設定が不要になることがメリットです。
 NETWORKDAYS関数でも同じようになるのでは、と思われるものの実際に第1、第2引数を「B3:B13」にするとなぜかエラーになってしまい、スピルしません。

1つの数式で合計を求める方法

 作業列を使うことなく、1つの式で平日の合計を求める方法です。
 SUM/FILTER関数を使い、平日の判定にはスピルが効くNETWORKDAYS関数を用います。

 G3セル

=SUM(FILTER(C3:C13,WORKDAY(B3:B13-1,1,E3:E5)=B3:B13))

 上記例と同様にWORKDAY関数により各日付が平日かどうか判定しています(平日ならTRUE)。あとはFILTER関数でTRUEに対応する数値(売上)を抽出し、SUM関数で合計を求めています。
 SUMIF関数だと第1引数に関数が使えないので「=SUMIF(WORKDAY(~),TRUE,B3:B13)」とはできないのが注意点です。



 さらにSUM/FILTER関数を用いるメリットとして、複数列の集計が容易であることが挙げられます。
 SUMIF関数だと基本的に複数列の合計が求められませんが、SUM/FILTER関数の場合はFILTER関数の第1引数を複数列に拡張するだけで複数列の合計が求められます。

 G3セル

=SUM(FILTER(C3:D13,WORKDAY(B3:B13-1,1,F3:F5)=B3:B13))

平日とする曜日を自由に指定する方法

 上記の例では月~金曜日を平日、土・日曜日を休日とみなした結果が得られます。ここではそれ以外の曜日を設定する例を紹介します。
 次の例では、水・木曜日を休日、金~火曜日を平日として平日の合計を求めています。

 D3セル(下方にフィルコピー)

=NETWORKDAYS.INTL(B3,B3,"0011000",F$3:F$5)

 NETWORKDAYS.INTL関数はNETWORKDAYSと同様に平日の日数を数える関数ですが、引数が1つ増えています。
 第3引数がポイントで、このように指定することで月~日曜日をそれぞれ平日(0)とみなすか休日(1)とみなすかを指定できます。引数での指定は平日=0ですが式の結果は平日=1となるのが注意点です。
 なおH3セルの数式は「=SUMIF(D3:D13,1,C3:C13)」としています。



 また、WORKDAY.INTL関数を使う方法もあります。
 スピルが効くので、ここではD3セルの式だけで一括判定しています。

 D3セル

=WORKDAY.INTL(B3:B13-1,1,"0011000",F3:F5)=B3:B13

 WORKDAY.INTL関数はWORKDAY関数と同様に平日の日付を求める関数です。やはり第3引数がポイントで、月~土曜日をそれぞれ平日(0)とみなすか休日(1)とみなすかを指定できます。
 H3セルの数式は「=SUMIF(D3:D13,TRUE,C3:C13)」です。



 WORKDAY.INTL関数をFILTER関数内に組み込めば作業列なしで合計が求められます。

 G3セル

=SUM(FILTER(C3:C13,WORKDAY.INTL(B3:B13-1,1,"0011000",E3:E5)=B3:B13))