勤務シフトの時間別人数を関数を使っての出し方を教えて下さい

質問

お世話になります。
エクセルで─線以下のように、時間別の人数を関数を使って、簡単に表示したいのですが・・・宜しくお願い致します。

名前　　　　1/30　　　　　　　　　　1/31　 　　　 　　
Ａさん｜　1000-1800　　　　　　　　　休　 　　　　　　　　
Ｂさん｜　　　有　　　　　　　　　900-1830　　
Ｃさん｜　900-1800　　　　　　　1000-1830　　　
Ｄさん｜　1200-2000　　　　　　1030-1900
Ｅさん｜　1130-1930　　　　　　1200-2000
───────────────────────
900-1000　　　　１　　　　　　　　　　１
1000-1030　　　２　　　　　　　　　　２
1030-1100　　　２　　　　　　　　　　３
1100-1130　　　２　　　　　　　　　　３　　　　　
1200-1300　　　４　　　　　　　　　　４
1900-2000　　　２　　　　　　　　　　１

─線より上の時間は、ひとつのセルに1000-1800と入力してるのではなく、実際は、ひとつのセルで改行して２行で入力しています。
｜1000｜
｜1800｜　のような感じです。
(休)などは、COUNTIFを使用して表示は出来たのですが、時間を入力すると出来ませんでした。
宜しくお願い致します。

回答No.3

n_na_tto

無理です。
こんなに使えない式に。
＿＿＿＿＿＿A＿＿＿＿B＿＿＿＿C_
_1＿＿＿_名前＿＿＿_1/30＿＿1/31_
_2＿＿_Ａさん＿1000-1800＿＿＿＿休_
_3＿＿_Ｂさん＿＿＿＿有＿＿900-1830_
_4＿＿_Ｃさん＿_900-1800＿1000-1830_
_5＿＿_Ｄさん＿1200-2000＿1030-1900_
_6＿＿_Ｅさん＿1130-1930＿1200-2000_

11＿_900-1000＿＿＿＿＿1＿＿＿＿＿1_
12＿1000-1030＿＿＿＿＿2＿＿＿＿＿2_
13＿1030-1100＿＿＿＿＿2＿＿＿＿＿3_
14＿1100-1130＿＿＿＿＿2＿＿＿＿＿3_
15＿1200-1300＿＿＿＿＿4＿＿＿＿＿4_
16＿1900-2000＿＿＿＿＿2＿＿＿＿＿1_

B11
=SUMPRODUCT((((LEFT(SUBSTITUTE(SUBSTITUTE(B$2:B$6,"有",0&CHAR(10)&0),"休",0&CHAR(10)&0),FIND(CHAR(10),SUBSTITUTE(SUBSTITUTE(B$2:B$6,"有",0&CHAR(10)&0),"休",0&CHAR(10)&0))-1)-LEFT($A11,FIND(CHAR(10),$A11)-1)>=0)*(LEFT(SUBSTITUTE(SUBSTITUTE(B$2:B$6,"有",0&CHAR(10)&0),"休",0&CHAR(10)&0),FIND(CHAR(10),SUBSTITUTE(SUBSTITUTE(B$2:B$6,"有",0&CHAR(10)&0),"休",0&CHAR(10)&0))-1)-MID($A11,FIND(CHAR(10),$A11)+1,4)<0)+(LEFT($A11,FIND(CHAR(10),$A11)-1)-LEFT(SUBSTITUTE(SUBSTITUTE(B$2:B$6,"有",0&CHAR(10)&0),"休",0&CHAR(10)&0),FIND(CHAR(10),SUBSTITUTE(SUBSTITUTE(B$2:B$6,"有",0&CHAR(10)&0),"休",0&CHAR(10)&0))-1)>=0)*(LEFT($A11,FIND(CHAR(10),$A11)-1)-MID(SUBSTITUTE(SUBSTITUTE(B$2:B$6,"有",0&CHAR(10)&0),"休",0&CHAR(10)&0),FIND(CHAR(10),SUBSTITUTE(SUBSTITUTE(B$2:B$6,"有",0&CHAR(10)&0),"休",0&CHAR(10)&0))+1,4)<0))>0)*1)
下方向・↓右方向・→

回答No.2

nattocurry

> ─線より上の時間は、ひとつのセルに1000-1800と入力してるのではなく、実際は、ひとつのセルで改行して２行で入力しています。
> ｜1000｜
> ｜1800｜　のような感じです。

これだと、文字列扱いになってしまい、そのまま計算には使えません。
改行文字で区切って、文字列を数値に直して、とかってやればいいのかもしれませんが、私にはちょっとよく解りません。

フォーマットを変えることはできないのですが？

もし添付図のようなフォーマットに変更できるのであれば、添付図の場合、C5に、
=SUMPRODUCT((C$2:C$4<=$A5)*(D$2:D$4>=$B5))
と入力して、C5を他のセルにコピーすればよいです。

フォーマットを変えられないのであれば、私には解りません。

回答No.1

singlecat

(休)同様に、=COUNTIF(範囲,"1000"&char(10)&"1800")でいけると思います。