excelで日付を16日～15日で1ヶ月とし、その間の有給使用回数をカウントしたい

質問

例えば2008/5/16～2008/6/15を1ヶ月とし、
その間の有給使用回数をカウントしたい。
Pと表示のあるものは0.5とカウントしたい。
それ以外は1とカウントしたい。
結果2008/5/16～2008/6/15の有給使用回数はA9に4.5と出したい。
のですが、できますでしょうか？かなり悩み中です。。。
　　　A
1　2008/4/20
2　2008/4/25
3　2008/5/7
4　2008/5/17
5　2008/5/18
6　2008/6/2
7　2008/6/3P
8　2008/6/10
9　4.5

http://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q1115744076はヒントになりますか。

お願いします。

回答No.3

mshr1962 · ベストアンサー

A9=SUMPRODUCT((TEXT(SUBSTITUTE($A$1:$A$8,"P","")-15,"yymm")="0805")/((RIGHT($A$1:$A$8,1)="P")+1))

解説
SUBSTITUTE($A$1:$A$8,"P","")-15
で日付の文字列から"P"を除いて15日ずらすことで
2008/5/16～2008/6/15を2008/5/1～2008/5/31にしています。
これをTEXT関数で0805という文字に置き換えて比較して
範囲にあれば1(TRUE)、範囲外は0(FALSE)とします。
次に(RIGHT($A$1:$A$8,1)="P")+1)
で最後の文字がPなら2、P以外なら1になるように設定
Pの付いた場合は1/2となり0.5になります。
これをSUMPRODUCT関数で集計しています。

お礼

ありがとうございます！
む、むずかしい式になりますね。
ちょっとやってみます☆

回答No.2

Masa2072

ANo1さんの言うとおり、表の作り方自体を工夫するほうがよいと思います。

一応式としては
=COUNT(OFFSET(A1,MATCH(DATE(2008,5,16),A1:A8,1),0,MATCH(DATE(2008,6,15),A1:A8,1)-MATCH(DATE(2008,5,16),A1:A8,1),1))+COUNTIF(OFFSET(A1,MATCH(DATE(2008,5,16),A1:A8,1),0,MATCH(DATE(2008,6,15),A1:A8,1)-MATCH(DATE(2008,5,16),A1:A8,1),1),"*P")*0.5+COUNTIF(OFFSET(A1,MATCH(DATE(2008,5,16),A1:A8,1),0,MATCH(DATE(2008,6,15),A1:A8,1)-MATCH(DATE(2008,5,16),A1:A8,1),1),"*A")*0.5
といった長くわかりにくいモノを使えばなんとかなるかな？
（もしかしたらもっと分かりやすい式があるかも）

お礼

そうですね。
表自体をもう少し考えたほうがいいですね。
こんな長い式どうやったら思いつくのですか？すごい
ありがとうございました。

回答No.1

kickknock

私は、自分で管理しているのですが・・・・・

まず、この表記はエクセルにあいません。文字列を計算させるようなもので、固定文字列でもありません。

私の場合は、もう既に年間シートを作成しています。
で、有給を使用すれば有給列に（1）半日休暇は（0.5）と入れます。

sumで一ヶ月毎と、連休のある月など、毎月と三ヵ月毎とかで合計を出しています。　残数を入れて、その当時の残数も計算するようにしています。

合計式は、別シートリンクを貼っています。
別シートに
　　　年初有給残数　20　 残
1月度有給消費合計　2.5　17.5
2月度有給消費合計　0.5　17.0

このあたりの式は簡単ですので、説明不要ですよね。
2枚シートを作れば、意外と簡単です。
とか・・・

お礼

もう少し表自体考えたいと思います。
ありがとうございました。
参考にさせてもらいますー