[[20170601135451]] 『平日・日曜・祝日別にカウントしたい』(oaki) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『平日・日曜・祝日別にカウントしたい』(oaki)

ご教授ください。

下記のようにA列に日付、B列に曜日、C列に行事名を入れています。
C列の行事名は行事がある場合だけ入力し、ない場合は空白です。

    A    B    C  
  1 日付  曜日 行事名
  2 4/30  日  イベントA
  3 5/1  月
  4 5/2  火
  5 5/3  水  イベントB
  6 5/4  木
  7 5/5  金  イベントC
  8 5/6  土
  9 5/7  日
 10 5/8  月  イベントD

この表を元に
平日イベント実施回数
日曜イベント実施回数
祝日イベント実施回数
をそれぞれ求めたいです。

別シートに祝日の一覧表は作成してます。(上記の表では5/3,4.5が祝日になります。)
どうぞ宜しくお願い致します。

< 使用 Excel:Excel2010、使用 OS:Windows7 >


 祝日はどのように判定するのですか?
 提示されているデータには祝日の情報はないようですが。

 また、祝日が土曜、日曜だった場合はどうするのですか?
(カリーニン) 2017/06/01(木) 14:02

 >提示されているデータには祝日の情報はないようですが。

 ↓ ですよね???
 別シートに祝日の一覧表は作成してます。

 >平日イベント実施回数 
 >日曜イベント実施回数 
 >祝日イベント実施回数 

 ・行事はあっても1日に1回ですか?
 ・土曜日は行事がないんですか?(それとも土曜日は平日なんですか?)

 とりあえず確認だけ。
(笑) 2017/06/01(木) 15:24

   A    B
 1 日付   名称 
 2 2017/1/1 元日
 3 2017/1/2 振替
 4 2017/1/9 成人の日
 5  ・    ・
 6  ・    ・ 

上記のような「祝日リスト」という別シートをつくっています。

実際の表では条件付き書式で数式を使用して書式設定しています。
祝日を =COUNTIF(祝日リスト!$A$1:$B$112,$A2)=1
日曜日を =WEEKDAY($A2,1)=1
とし、それぞれに該当するセルを緑と赤に色分けしています。

土曜日は通常は平日、祝日だったら祝日に、
日曜が祝日だった場合は、祝日としたいです。
行事は1日1回です。

説明不足ですいません。。。。
(oaki) 2017/06/01(木) 15:32


 >別シートに祝日の一覧表は作成してます。

 見落としてました。失礼しました。
(カリーニン) 2017/06/01(木) 15:33

 集計が、
 F2セル:日曜
 F3セル:祝日
 F4セル:平日
 で、日付別一覧表は最大300行目までだとして、

 F2 =SUMPRODUCT((WEEKDAY(A2:A300)=1)*(COUNTIF(祝日リスト!A2:A150,A2:A300)=0)*(C2:C300<>""))

 F3 =SUMPRODUCT(SIGN(COUNTIF(祝日リスト!A2:A150,A2:A300))*(C2:C300<>""))

 F4 =COUNTA(C2:C300)-SUM(F2:F3)

 こんな感じでいけますか?

 >祝日を =COUNTIF(祝日リスト!$A$1:$B$112,$A2)=1 

 祝日の日付だけ見ればいいので、
 祝日リスト!$A$1:$B$112 → 祝日リスト!$A$1:$A$112 でも同じです。
                                             ~~~~~~
 参考まで。
(笑) 2017/06/01(木) 16:13

 作業列を使ってもいいのなら、

 D2 =IF(C2="","",IF(COUNTIF(祝日リスト!A:A,A2),"祝",IF(WEEKDAY(A2)=1,"日","平")))

 下へコピー

 D列の「祝」「日」「平」の個数をそれぞれCOUNTIFで数えると、それが実施回数になります。

 参考まで。
(笑) 2017/06/01(木) 16:41

 B列の曜日が文字列、つまり B2に =TEXT(A2,"aaa;;") という数式が入っているのなら
 もしくはこの式に変更してもいいのなら、

 F2(日曜)=SUMPRODUCT((COUNTIF(祝日リスト!A2:A150,A2:A300)=0)*(B2:B300="日")*(C2:C300<>""))

 作業列の数式は
 D2 =IF(C2="","",IF(COUNTIF(祝日リスト!A:A,A2),"祝",IF(B2="日",B2,"平")))

 でもいいです。
(笑) 2017/06/01(木) 17:28

 できました!!
 ありがとうございます!

 未熟なので私的には作業列を使う方が関数の内容がすんなり理解できました。
 作業列を使わないやりかたも、自分で作るのは難しいかもしれませんが、今理解しようと試みてます。

 >>祝日を =COUNTIF(祝日リスト!$A$1:$B$112,$A2)=1 

 >祝日の日付だけ見ればいいので、
 >祝日リスト!$A$1:$B$112 → 祝日リスト!$A$1:$A$112 でも同じです。
                                             ~~~~~~
 ほんとにご指摘の通りです!
 早速修正しました。

 ちなみに後学の為に・・・
 祝日と日曜の行事の回数を合算する場合、作業列を使わない簡単な関数てありますでしょうか?

(oaki) 2017/06/02(金) 11:29


 >祝日と日曜の行事の回数を合算する場合、作業列を使わない簡単な関数

 簡単なと言われるとアレですけど、日曜日の回数に使ったSUMPRODUCTの条件を変更すればできます。

 日曜は「日曜日」かつ「祝日ではない」というAND条件。
 これを「日曜日」または「祝日」というOR条件にする。

 =SUMPRODUCT(SIGN(COUNTIF(祝日リスト!A2:A150,A2:A300)+(WEEKDAY(A2:A300)=1))*(C2:C300<>""))

 または、B列(曜日)の数式が =TEXT(A2,"aaa;;") なら
 =SUMPRODUCT(SIGN(COUNTIF(祝日リスト!A2:A150,A2:A300)+(B2:B300="日"))*(C2:C300<>""))

 こんな感じ。
 なお日曜日の式と比べると (WEEKDAY(A2:A300)=1) が COUNTIF のうしろにきてますが、
 これに関して深い意味はありません。
(笑) 2017/06/02(金) 14:36

コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.