戻る  □VBAのサロン  □ 使用方法  □ 新着記事  □ 新規に質問する!  □ トピック一覧  □ 検索  □ 過去ログ
[ 最新記事及び返信フォームをトピックトップへ ]
このトピック参照回数 :
土日祝祭日を除く平日のみの出席者率を計算したい

    [130313] 土日祝祭日を除く平日のみの出席者率を計算したい-

    ■親トピック/記事引用/メール受信=OFF■

    □投稿者/ asahishi -(2019/06/04(13:37))
    □U R L/
      いつもお世話になっております。
      
      いつも質問ばかりで今回も質問になってしまうのですが宜しく御願いします・・・
      
      大体以下の様な表を作成しています・・・
      
      
      ・シート名「欠席管理表」
      
           A      B        C           D         E                 F
        +----------------------------------------------+-----------------------+
       1|                                              |                       |
        |                  欠席管理表                  |                       |
       2|                                              |         令和元年5月分 |
        +------+------+----------+----------+----------+-----------------------+
       3| 日付 | 曜日 | 出席者数 | 欠席者数 | 出席者率 |         理由          |
        +------+------+----------+----------+----------+-----------------------+
       4|     1|    水|         1|         0|   100.0% |                       |
        +------+------+----------+----------+----------+-----------------------+
       5|     2|    木|         1|         0|   100.0% |                       |
        +------+------+----------+----------+----------+-----------------------+
       6|     3|    金|         1|         0|   100.0% |                       |
        +------+------+----------+----------+----------+-----------------------+
       7|     4|    土|         1|         0|   100.0% |                       |
        +------+------+----------+----------+----------+-----------------------+
       8|     5|    日|         0|         0|          |                       |
        +------+------+----------+----------+----------+-----------------------+
                                        ・
                                        ・
                                        ・
        +------+------+----------+----------+----------+-----------------------+
      33|    30|    木|         0|         0|          |                       |
        +------+------+----------+----------+----------+-----------------------+
      34|    31|    金|         0|         0|          |                       |
        +======+======+==========+==========+==========+=======================+
      35|    合計     |         7|         0|   100.0% |                       |
        +------+------+----------+----------+----------+-----------------------+
      
       「欠席管理表」シートの F2 には =DATE(入力用!$C$2,入力用!$C$3,1) と入力
      してあり更にセルの書式設定で
      
      [<=43585]ggge"年"m"月分";[>=43831]ggge"年"m"月"d"日";ggg"元年"m"月分"
      
      としてあります。この書式設定はネットで検索して拾って来た物だったと思いま
      すが平成から令和に変わる 2019 年 4 月 から 2019 年 5 月にかけては大丈夫
      の様です。令和 1 年は元年表示もしてくれるみたいです。
      
       A 列の日付に関しては確か以前満月さんという方から教えて頂いた方式を使っ
      て、A4 に
      
      =IF(MONTH(DATE(入力用!$C$2,入力用!$C$3,入力用!$D$3))=MONTH(DATE(入力用!$C$2,入力用!$C$3,入力用!$D$3)+ROW()-4),DATE(入力用!$C$2,入力
      
      用!$C$3,入力用!$D$3)+ROW()-4,"")
      
      と入力し更に 31 日目になる 34 行目迄フィルコピーして入力用のシートの年月
      に応じて自動的に月末までの日付が表示される様にしてあります。
      
       もっと短くなる式もアドバイスして頂いたのですが全部フィルコピーするとなる
      とこの方式が良いのかなぁ等と思っています・・・
      
       またセルの書式設定でユーザー定義の d と指定して日付のみが表示される様に
      してあります。
      
       B 列の曜日に関しては B4 に =A4 と入力し 31 日目になる 34 行目迄フィルコピ
      ーしてセルの書式設定で aaa と指定して曜日のみが表示される様にしてあります。
      
       次に出席者数ですが C4 に
      
      =COUNTIF(OFFSET(入力用!$D$8,0,ROW(A1)*7-7,42,1),"○")+COUNTIF(OFFSET(入力用!$D$8,0,ROW(A1)*7-7,42,1),"早")+COUNTIF(OFFSET(入力用!$D$8,0,ROW(A1)*7-7,42,1),"遅")
      
      と入力して 31 日目の C34 迄フィルコピーしてあります。
      
      「入力用」という名前のシートの D 列・K 列・R 列・・・と 7 列おきに入力され
      ている「○」・「早」・「遅」の文字を 49 行目迄拾って合計する様にしてあります。
      
       もっと簡単な式があったのかも知れませんが自分では難しそうでした・・・
      
       次の欠席者数ですが D4 に
      
      =COUNTIF(OFFSET(入力用!$D$8,0,ROW(A1)*7-7,42,1),"×")
      
      と入力し同じく「入力用」シートから「×」を拾って合計しています。
      
       出席者率の E 列ですが E4 に =IF(C4+D4<>0,C4/(C4+D4),"") と入力して E34 迄
      フィルコピーしてあります。
      
       セルの書式設定でパーセンテージにして小数点以下 1 桁迄表示する様にして
      あります。
      
       また土日祝日の色分けを条件付き書式で指定しています。
      
       まず日曜日がセルの適用先が =$A$4:$F$34 となっていて「数式を使用して、書
      式するセルを決定」の「次の数式を満たす場合に値を書式設定(o):」の所に
      
      =WEEKDAY($A4)=1
      
      と指定してセルの書式設定の塗りつぶしタブの所から薄ピンクで表示される様にし
      てあります。
      
       次に祝日ですが同じくセルの適用先が =$A$4:$F$34 となっていて更に式の方が
      
      =IF($A4<>"",MATCH($A4,OFFSET(作業用!$H$2,0,0,COUNTA(作業用!$H:$H),1),0)>0)
      
      となっています。塗りつぶしの色は薄オレンジになっています。
      
       MATCH での指定は自分ではやった事が無かったと思うのですが元々の表での指定
      がそうなっていたのでそれを流用する形で上記の様な式になりました。
      
       また「作業用」という名前のシートの H 列に「入力用」シートの年月を拾って
      祝日の一覧が計算されて表示される様にしているので上記の様な式になっています。
      
       次に土曜日ですが同じくセルの適用先が =$A$4:$F$34 となっていて同じく式が
      
      =WEEKDAY($A4)=7
      
      となっていて塗りつぶしの色は薄青に指定しています。
      
      
       「入力用」シートですが大体以下の様な感じになっています。
      
      
      ・シート名「入力用」
      
         B     C   D  E  F      G       H         I       J   K  L  M     N
        +--+------+--+--+--+--------+--------+----------+----+--+--+--+--------+
       2|  | 2019 |  |  |  |        |        |          |    |  |  |  |        |
        +--+------+--+--+--+--------+--------+----------+----+--+--+--+--------+
       3|  |     5| 1|  |  |        |        |          |    |  |  |  |        |
        +--+------+--+--+--+--------+--------+----------+----+--+--+--+--------+
       5|  |      | 令和元年5月1日  |   水   | 出席:1名 |    | 令和元年5月2日  |
        +--+------+--+-----+--------+--------+----------+----+--+-----+--------+
       6|  |      |出|時間 |        |        |          |    |出|時間 |        |
        +--+ 氏名 +--+--+--+業務内容|支援内容|効果・課題|備考+--+--+--+業務内容|
       7|  |      |欠|出|退|        |        |          |    |欠|出|退|        |・・・
        +--+------+--+--+--+--------+--------+----------+----+--+--+--+--------+
       8| 1|  A  |○|  |  |        |        |          |    |○|  |  |        |
        +--+------+--+--+--+--------+--------+----------+----+--+--+--+--------+
       9| 2|  B  |  |  |  |        |        |          |    |  |  |  |        |
        +--+------+--+--+--+--------+--------+----------+----+--+--+--+--------+
      10| 3|  C  |  |  |  |        |        |          |    |  |  |  |        |
        +--+------+--+--+--+--------+--------+----------+----+--+--+--+--------+
                         ・
                         ・
                         ・
        +--+------+--+--+--+--------+--------+----------+----+--+--+--+--------+
      48|41|      |  |  |  |        |        |          |    |  |  |  |        |
        +--+------+--+--+--+--------+--------+----------+----+--+--+--+--------+
      49|42|      |  |  |  |        |        |          |    |  |  |  |        |
        +--+------+--+--+--+--------+--------+----------+----+--+--+--+--------+
      
      
      
       ・シート名「作業用」の祝日リストの一部
      
           H    I           J
       +---------------------------------------+
      1|【2019年祝日リスト(計算分)】           |
       +-----------+---------------------------+
      2| 2019/1/14 | 1月第二月曜日(成人の日)|
       +-----------+---------------------------+
      3| 2019/3/21 | 春分の日                  |
       +-----------+---------------------------+
      4|           |                           |
       +-----------+---------------------------+
      5| 2019/7/15 |7月第三月曜日(海の日)   |
       +-----------+---------------------------+
                         ・
                         ・
                         ・
      
      
       「作業用」シートの祝日一覧ですが大体上の様な感じになっています。
      
       例えば H2 には
      
      =DATE(入力用!$C$2,1,14-WEEKDAY(DATE(入力用!$C$2,1,0),3)) 
      
      みたいな式が入っています。
      
       H4 は空欄になっていますが、今年は振替休日を計算した結果対象外だったので
      空欄になっています。
      
       H4 の式ですが、
      
      =IF(WEEKDAY(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-1,0),1)=1,OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-1,0)+1,"")
      
      みたいな感じになっています。ちょっと長ったらしいですが・・・
      
       J4 は
      
      =IF(INDIRECT(ADDRESS(ROW(),COLUMN()-2))="","","振替休日")
      
      となっています。
      
       長くなりましたが、この様な表の場合土日祝祭日を除いた平日の出席者率を求める
      にはどの様な式を入力すれば良いのでしょうか・・・
      
       主に使用されると思われる環境は Excel 2013 以降で OS は Windows 7 以降です。
      
       ネットでも調べてみたのですが、祝祭日の出席者数を求める位が限界でした・・・
      
       祝祭日かつ土日の場合の扱いや祝祭日リストに振替休日でない場合の空欄が入っ
      ているのが難しそうな感じがします・・・
      
       どうか皆さんの智恵をお貸し下さい・・・



      ++++++++++++++++++++
      OS      ⇒OTHER
      Version⇒OTHER
      ++++++++++++++++++++




    [130314] Re[1]: 土日祝祭日を除く平日のみの出席者率を計算したい-

    記事引用/メール受信=OFF■

    □投稿者/ asahishi -(2019/06/04(13:55))
    □U R L/

       情報不足だったかも知れないので補足します・・・

       計算したい出席者率は月毎のものです・・・




    [130315] Re[2]: 土日祝祭日を除く平日のみの出席者率を計算したい-

    記事引用/メール受信=OFF■

    □投稿者/ Mike -(2019/06/04(15:08))
    □U R L/

      シコシkと表の作成、お疲れさまでした。
      素晴らしい!感心しました。
      どうか頑張ってください。




    [130316] Re[3]: 土日祝祭日を除く平日のみの出席者率を計算したい-

    記事引用/メール受信=OFF■

    □投稿者/ 半平太 -(2019/06/04(17:18))
    □U R L/
      >計算したい出席者率は月毎のものです・・・
      
      1.欠席管理表のE35セルに一つ算出すればいいんですか?
      
      >祝祭日リストに振替休日でない場合の空欄が入っ
      >ているのが難しそうな感じがします・・・
      
      2.空欄を入れると折角の祝日リストが台無しになるので、空白の代わりに0にしてください。
      
       ※0は見たくないなら、セルの書式(表示形式)を活用して、
        ユーザー定義で、0が見えない様に設定する。




    [130318] Re[4]: 土日祝祭日を除く平日のみの出席者率を計算したい-

    記事引用/メール受信=OFF■

    □投稿者/ asahishi -(2019/06/04(21:33))
    □U R L/
       半平太さんレス有り難う御座います。
      
       現状では E35 セルは祝祭日土日も含めたひと月分の出席率となっています。
      
       土日は主にイベント事なんかの事が多いので純粋に祝祭日土日を除いた平日の出
      席率を別に表示させたいみたいです。
      
       そもそも祝祭日土日を含めた出席率の方が必要なのかどうか分からなかったりし
      ますが・・・
      
       月毎の祝祭日土日を除いた平日の出席率等は欠席管理票の 37 行目辺りに別に表
      示出来ればと思っています。
      
      
       ・シート名「欠席管理表」
      
           A      B        C           D         E                 F
        +----------------------------------------------+-----------------------+
       1|                                              |                       |
        |                  欠席管理表                  |                       |
       2|                                              |         令和元年5月分 |
        +------+------+----------+----------+----------+-----------------------+
       3| 日付 | 曜日 | 出席者数 | 欠席者数 | 出席者率 |         理由          |
        +------+------+----------+----------+----------+-----------------------+
       4|     1|    水|         1|         0|   100.0% |                       |
        +------+------+----------+----------+----------+-----------------------+
       5|     2|    木|         1|         0|   100.0% |                       |
        +------+------+----------+----------+----------+-----------------------+
       6|     3|    金|         1|         0|   100.0% |                       |
        +------+------+----------+----------+----------+-----------------------+
                                        ・
                                        ・
                                        ・
        +------+------+----------+----------+----------+-----------------------+
      33|    30|    木|         0|         0|          |                       |
        +------+------+----------+----------+----------+-----------------------+
      34|    31|    金|         0|         0|          |                       |
        +======+======+==========+==========+==========+=======================+
      35|    合計     |         7|         0|   100.0% |                       |
        +------+------+----------+----------+----------+-----------------------+
      36|      |      |          |          |          |                       |
        +------+------+----------+----------+----------+-----------------------+
      37| 平日の出欠者|          |          |          |                       |
        +-------------+----------+----------+----------+-----------------------+
      
       最初からこの様に書くべきでした・・・
      
       失礼しました・・・
      
       また祝日一覧の空欄に関してですが一先ず日付が空欄の所は 0 と修正しました。
      
       ただ、0 を非表示にする方法はネットで検索してみたところ 0 の場合条件付き書
      式で白文字にして結果見えなくするという方法が良さげだったのですが、自宅の環
      境では再現出来ませんでした・・・
      
       実際に表を使っている所だと上手くいくのかも知れません・・・
      
       取り敢えず自宅では 0 表示のまま放置する事にします・・・
      
       ただ、振替休日の空欄だけ 0 にしても他にも
      
      |【2019年祝日リスト(計算分)】
      
      の後の
      
      |【2019年祝日リスト(固定分)】		
      		
      の手前に2行程空行があります・・・
      
       これもマズい感じでしょうか・・・
      
       祝日一覧は連続しているセルで式の計算結果も含めて何もデータの入力されてい
      ないセルは存在するとマズいのでしょうか・・・




    [130319] Re[5]: 土日祝祭日を除く平日のみの出席者率を計算したい-

    記事引用/メール受信=OFF■

    □投稿者/ 半平太 -(2019/06/05(00:08))
    □U R L/
      > また祝日一覧の空欄に関してですが一先ず日付が空欄の所は 0 と修正しました
      
      1.こちらの認識では、
        空欄とは、そのセルに数式が入っており、その結果「空白文字列」になっているもの。
      
       その通りであれば、「0」になるように数式を修正して頂いたものとします。
      
       ※もしセルが未入力で、真の空白の場合は、何もする必要はないです。つまリ、無害です(念の為)
      
       ・・それで、数式で0にしたセルを空白に見せる書式は、以下の通りです。(条件付き書式は使いません)
      
       セルの書式(表示形式) の ユーザー定義で、yyyy/m/d;;; と設定する
                                ↑
                             セミコロンを後ろに3個付ける
      
       そして、その休日データ範囲を「祝日」と名前定義するものとします。
      
      2.C4セルの数式は、34行目まで入っているものとします。
      
        つまり、A列が空白(の文字列)でも、C列は「0」と出ているものとします。
      
        ※上記1と同じ書式を適用して、それを空白に見せる様にするかどうかは、そちらの任意です。
      
      3.平日のみの人数を求める数式
      
       C37セル =SUMPRODUCT(N(WORKDAY((0&$A$4:$A$34)+(A$4:A$34="")*7-1,1,祝日)=$A$4:$A$34)*C$4:C$34,C$4:C$34)
      
       右にコピー
      
      > 祝日一覧は連続しているセルで式の計算結果も含めて何もデータの入力されてい
      >ないセルは存在するとマズいのでしょうか・・・
      
      連続している必要はありません。
      
      祝日リストが数種あるような印象を受けるのですが、こちらで、フォローし切れておりません。
      
      上の回答で旨く行かないことがあったら改めて考えます。




    [130322] Re[6]: 土日祝祭日を除く平日のみの出席者率を計算したい-

    記事引用/メール受信=OFF■

    □投稿者/ asahishi -(2019/06/05(11:08))
    □U R L/
       半平太さんレス有り難う御座います。
      
       祝日リストに関してですが例えば「作業用」シートの H4 の式ですが、
      
      =IF(WEEKDAY(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-1,0),1)=1,OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-1,0)+1,"")
      
      となっている所を
      
      =IF(WEEKDAY(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-1,0),1)=1,OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-1,0)+1,"0")
      
      と修正し計算結果として振替休日でない場合は 0 となる様に修正しました。
      
       国民の休日も同様に 0 にしました。
      
       0 を非表示にするには書式設定を使うのですね。早速こちらで使ってみましたが
      うまくいった様です。アドバイス有り難う御座います。
      
       祝日リストは「計算分」と「固定分」の2種類あるのですが、例えば作業用の
      H1 とかには実際には「="【"&入力用!$C$2&"年祝日リスト(計算分)】"」という式が
      入っていてこのコメント行を含めて「祝日」と名前定義してしまうとうまくいかな
      い様でした。なので取り敢えずコメントを J 列にずらして H 列には文字列が入ら
      ない様にしました。
      
       ・シート「作業用」
      
           H    I           J
       +---------------------------------------+
      1|【2019年祝日リスト(計算分)】           |
       +-----------+---------------------------+
      
       H13="【"&入力用!$C$2&"年祝日リスト(固定分)】"としてあり実際の祝日一覧がコ
      メントで分断されていたのもまずかったみたいです・・・
      
       あと半平太さんにアドバイスして頂いた平日のみの人数を求める数式ですが「欠
      席管理表」シートの C4 から C34 迄に 1 とか 0 の数値であれば正常にカウントさ
      れるみたいなのですが、2 以上の数値が入っていると平日の出席者数が何人分か多
      くカウントされるみたいです・・・
      
       「祝日」の名前定義の参照範囲を祝日リスト(固定分)の日付データ部分のみの
      
      =作業用!$H$2:$H$10
      
      とかしても例えばある平日の出席者が 2 名以上の場合は土日祝日を含めた純粋なひ
      と月の出席者合計よりも平日の出席者が多いとかいう感じになってしまいます・・・
      
       何かこちらの入力ミス等があったのでしょうか・・・




    [130323] Re[7]: 土日祝祭日を除く平日のみの出席者率を計算したい-

    記事引用/メール受信=OFF■

    □投稿者/ 半平太 -(2019/06/05(11:34))
    □U R L/
      >とかしても例えばある平日の出席者が 2 名以上の場合は土日祝日を含めた純粋なひ
      >と月の出席者合計よりも平日の出席者が多いとかいう感じになってしまいます・・・
      
      > 何かこちらの入力ミス等があったのでしょうか・・・
      
      こちらのミスです。 m(__)m
      
      >C37セル =SUMPRODUCT(N(WORKDAY((0&$A$4:$A$34)+(A$4:A$34="")*7-1,1,祝日)=$A$4:$A$34)*C$4:C$34,C$4:C$34)
       正   =SUMPRODUCT( (WORKDAY((0&$A$4:$A$34)+(A$4:A$34="")*7-1,1,祝日)=$A$4:$A$34)*C$4:C$34)




    [130324] Re[8]: 土日祝祭日を除く平日のみの出席者率を計算したい-

    記事引用/メール受信=OFF■

    □投稿者/ 半平太 -(2019/06/05(11:38))
    □U R L/
      >=IF(WEEKDAY(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-1,0),1)=1,OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-1,0)+1,"0")
                                                                ↑
                  中身を検討していないですが、こういうところは単に0にしてください。ダブルクーテーションは不要です。




    [130328] Re[9]: 土日祝祭日を除く平日のみの出席者率を計算したい-

    解決!!ありがとうございました! / 記事引用/メール受信=OFF■

    □投稿者/ asahishi -(2019/06/05(13:21))
    □U R L/

       半平太さんレス有り難う御座います。

       修正された方の式で平日の出欠者のカウントが出来る様になったみたいです。

       何度もレス有り難う御座いました。

      |=IF(WEEKDAY(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-1,0),1)=1,OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-1,0)+1,"0")

       上記の様な "0" のダブルコーテーションも外しました。ダブルコーテーションが
      付いたままだと文字列として扱われるんでしょうか・・・

       上の式は確か自セルや自セルの隣のセルの内容を参照する式をネットで探して来
      て使用したものだったと思います。

       半平太さんの知識の凄さにはいつも感動しています。

       本当に有り難う御座いました。



このトピック内容の全ページ数 / [0]

このトピックに書きこむ
Name/
E-Mail/

└>このツリーのレス記事をメールで受信しますか? YES/ NO/
Title/
URL/
Comment/ 通常モード->  図表モード-> (←の場合適当に改行を入れて下さい)
タグが使用できます。例 ⇒ <font color="blue">文字</font>
解決!!ありがとうございました! 解決       保留中です・・・ 保留        迷宮入りorほかあたって見ます・・ 迷宮入       すいませ〜ん。誰か〜! Help! ←※回答者専用
解決したらチェック       保留(コードテスト中など・・)         解決不可orレスが全く付かなくてほかのサイトに行くときなど・・
削除キー/
(半角8文字以内) 


- Child Forum -
Edit:ゆう-G