人に聞けない痔の悩み、これでスッキリ >>

有給取得義務化が決定し、取得予定表を作成するよう指示され事前にスタッフから取得希望日(5日分)を聞いて添付図表の数字(B列からM列)を入力しました。
(日付は4桁表記、同月に2日以上希望がある場合は「,」で区切り、同一セルに入力)

取得日数が何日分入力されているか関数を用いてN列に表示させたいのですが、どのような関数を用いれば良いのかがわかりませんので教えて頂きたく質問致します。(添付の表はベタ打ちしましたが、実際には人数が多いので関数で表示できればと思います)
ひと月につき1日しか入力されていなければCOUNT関数でいけると思うのですが、複数入力されている場合があるのでわからず困っています。

宜しくお願い致します。

「エクセルで複数セルの中の数(日数)を数え」の質問画像

A 回答 (3件)

2巡目以上の 投稿ですか?


お困りですね。


此で 良いのですかね?

式、
=COUNTA($B5:$M5)+SUMPRODUCT(LEN($B5:$M5)-LEN(SUBSTITUTE($B5:$M5,",","")))


COUNT構文で カウントできるのは、
数値でしょうが、

複数日付が 含まれるセルは、
文字列で しょうから、
カウントされないでしょう、

ですので こう言う場合は、
CONTA構文を 使うべきですね。


又、
日付区切り文字は 必ず、
「,」なのですよね、

日付数は 区切り文字+1
ですが、

+1分は セル数で、
カウントされて いますから、

区切り文字数+セル数=追加分日付数
と なるでしょう。


では、
カウント方法ですが

セル数を カウントするのは、
先出でも 説明しましたが、

区切り文字数は、
どう カウントするでしょうか?


今回は、
区切り文字を ないように、
変えてみて、

何文字 減るか、
カウントすれば、

減ったのが 区切り文字だけ、
なのですから、

区切り文字数、
詰まり、
追加分日数が カウントできる
と なるのですよ。


此方で 確認くださいね、

ファイル、
https://1drv.ms/x/s!AjviygfJDgV_3HpMLdcO6lbvstWq

尚、
ファイルは 必ず、
一度 Excelで、
開き、
ローカルに 別名保存してください、

別名保存でないと、
意味が ありませんよ。


そうすれば、
別名保存した ファイルでなら、
閲覧も、編集も、
叶うものと 思いますよ。


如何ですか?
    • good
    • 0
この回答へのお礼

お返事ありがとうございます。
はい、2回目の質問です。結構困っておりまして…

式やサンプルファイル、ありがとうございます。助かります。
式や関数を一つずつ分解して理解し、今後は自分で式が作れるように頑張ります。

ありがとうございました。

お礼日時:2019/05/06 09:28

No.1の回答者さんも指摘しているように、表その物を変えることをお勧めします。



質問者さんが作成した表では、入力する値は4桁である必要はありませんよね。
そんななら、はじめっから1人1つのセルに入れてしまえば良いということになります。
月で分ける必要なんて無い。

・・・本題・・・

自分なら…
 B列からM列までのセルを行単位で結合し、セルを結合する時カンマ(,)で区切る。(冒頭に書いた1つのセルに入力するを実現)
 カンマの数を数え、それに1を足す。(カンマは値を繋ぐ文字なので、この数に1を足せば値の数になる)
…という手順で求めます。

・・・
TEXTJOIN関数を使ってセルを結合します。
 =TEXTJOIN(区切り文字,空のセルを無視,文字列1,[文字列2],…)
のようにして使いますので、
 =TEXTJOIN(",",TRUE,B1:M1)
こんな感じになります。

カンマの数を数えるには、カンマを消した文字列の数と、カンマを消さない文字列の長さを比較すれば良いので
SUBSTITUTE関数でカンマをヌル文字にして消してしまいます。
 =SUBSTITUTE(文字列,検索文字列,置換文字列)
のようにして使いますので、
 =SUBSTITUTE( TEXTJOIN(",",TRUE,B1:M1) , "," , "" )
こんな感じになります。
そして文字列の長さを数えるのはLEN関数。
 =LEN(文字列)
のようにして使いますので、
 =LEN(TEXTJOIN(",",TRUE,B1:M1))
これでカンマで繋がれた文字列の長さ、
 =LEN(SUBSTITUTE( TEXTJOIN(",",TRUE,B1:M1) , "," , "" ))
これでカンマを消して結合した文字列の長さ、
が返ってきます。
あとは引き算して1を足す。
 =LEN(TEXTJOIN(",",TRUE,B1:M1)) - LEN(SUBSTITUTE( TEXTJOIN(",",TRUE,B1:M1) , "," , "" )) +1
これで一応は完成。

ただし、全く入力されていない場合でも「1」と返ってくるので、別途文字列の長さが「0」なら「0」を返す処理を入れます。
 =IF(TEXTJOIN(",",TRUE,B1:M1)="" , 0 , LEN(TEXTJOIN(",",TRUE,B1:M1)) - LEN(SUBSTITUTE( TEXTJOIN(",",TRUE,B1:M1) , "," , "" )) +1)
こーんな面倒くさい数式になります。
 ※ 実際にExcelで動作させた数式ではありません。あくまでも考え方を示すために机上で考えた数式ですので、正しく動作する保証はありません。

・・・
…これ、条件が変わって質問者さん自身でメンテナンスするのほぼ不可能じゃね?
(自分が入力ミスし数式が正しく動かないとして質問者さんがこれを正しく修正することを含む)

そんなわけで、元の表を今からでも作り直すことを強く勧めます。
そのほうが確実に仕事をこなすことができますよ。
    • good
    • 0
この回答へのお礼

お返事ありがとうございます。
後出しになって申し訳ありませんが、表に関しては会社指定になりますので構成を変える事が出来ない状況です・・
TEXTJOIN関数、初めて知りました。勉強になります。
教えて頂いた関数や式を一つ一つ分解して理解し、今後の業務に役立てたいと思います。

ありがとうございました。

お礼日時:2019/05/06 09:18

表に改善の余地があるように思います。


表の改善案を提示しておきます。

◆案1
縦:氏名、横:回目、とする。
月は関係なく、左詰めで1セルに1回の日付を入力していきます。

◆案2
縦:日付+、横:氏名、とする。
「エクセルで複数セルの中の数(日数)を数え」の回答画像1
    • good
    • 0
この回答へのお礼

お返事ありがとうございます。
後出しになって申し訳ありませんが、表に関しては会社指定になりますので構成を変える事が出来ない状況です・・
ご提案頂いた表は、今後の業務の参考にさせて頂きます。

ありがとうございました。

お礼日時:2019/05/06 09:14

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!


人気Q&Aランキング