エクセルで文字列検索後隣のセルの数値と比較後カウン

質問

OKWave初めての書き込みです。

初心者な質問で申し訳ございませんが、
お分かりになる方、どうかご教授ください。

◆質問内容

まずはじめに、エクセル内のデータはこのようになっております。

▼シート１
A【年月日】　B【時間】　C【数値】　D【数値】
2011.09.17　 17:00　　 10.50　　　　12.21
2011.09.17　 18:00　　 15.51　　　　9.08
2011.09.17　 19:00　　 5.51　 　　　99.22
　・
　・
　・
2012.06.11　 9:00　　 　99.05　 　　89.288
2012.06.11　 10:00　　 29.05　 　 　80.20
2012.06.11　 11:00　　 20.05　 　 　11.20

▼シート２
A【7時】　B【8時】　C【9時】　・・・　V【4時】　W【5時】　X【6時】
xxx　　　　xxx　　　 xxx　　　　　　　xxx　　　 xxx　　　　xxx

シート２の各xxxには、下記の条件に当てはまるカウント数のみを表示したいです。

★シート２のA1にある「2010.01.01」～シート２のA2にある「2012.03.02」の指定期間で
★シート１の指定時間（対象：シート１のB列）にあてはまる時間で
★その行のC列よりD列のほうが数値が多いカウント数

説明べたで申し訳ないですが、★の複数条件に当てはまるカウントした数値の計算方法を
ご教授いただけましたらすごく幸いでございます。

本当に困っていますので、どうかよろしくお願いします。。

回答No.5

keithin

どんだけデータが入るか考えたくなくて、しばしば「1048576」までの式を書く人をみかけますが、通常はそういう計算の仕方はしません。
ゼロになるのは、計算式に与えた範囲の与え方が不適切なため、エクセルが止まってるからかもしれません。または単純にデータが間違っているからかもしれません。たとえば記録２のＡ列やＣ列など。
あるいは、補足の情報提供の数式が途切れているところで、何かミスってるのかも？しれません。
いずれにしても回答した方法では、あなたが補足質問で書かれたような手抜きの使い方をすると実用に耐えなくなりますので、使わないでください。

データ行がどこまで増えるか事前検討したくないのでしたら、あきらめてＨ列に個行の評価結果をYESかNOで示す数式を並べ、その結果をＳＵＭなどの簡単な関数を使って答えを求めて下さい。

作成例：
「現在」シートの
Ｄ１に開始の日付が年/月/日で記入してある
Ｈ１に終了の日付が年/月/日で記入してある
Ｃ１１からＺ１１までに７、８、…６と記入してある

記録２の
Ｈ２に
=IF(AND(現在!$D$1<=A2,A2<=現在!$H$1,D2<G2),HOUR(C2),"")
と記入し、リストの下端までコピー貼り付ける

「現在」シートに戻り
Ｄ１１に
=COUNTIF(記録2!$H:$H,C11)
と記入して右にコピー。

＃参考
この方式で個別行の評価結果を並べてみることで、Ａ列やＣ列に不適切データが紛れてないか確認することもできます。

回答No.4

keithin

ANo3は，間違って他の方のご質問への回答を書き込んで投稿してしまいました。失礼しました。
改めまして。

シート１の
Ａ列に日付が年/月/日で記入してある
Ｂ列に時刻が時:00で記入してある
Ｃ列，Ｄ列に数値が記入してある

シート２の
Ａ１に開始の日付が年/月/日で記入してある
Ａ２に終了の日付が年/月/日で記入してある
Ａ３から右に 7　8　9　…　と記入し，以降　Ｘ３に　6　まで記入してある

Ａ４に
=SUMPRODUCT(($A$1<=Sheet1!$A$2:$A$100)*(Sheet1!$A$2:$A$100<=$A$2)*(HOUR(Sheet1!$B$2:$B$100)=A3)*(Sheet1!$C$2:$C$100<Sheet1!$D$2:$D$100))
と記入，右にコピー。

言わずもがなですが，実際の表の配置に合わせて，寄せられた回答の数式を正しく直してから計算してください。

お礼

keithinさん回答ありがとうございます。
No3の件、了解いたしました。

こちらの回答が一番望むものに近いのですが、
実際の表の配置にあわせて数式を正しく直しましたが、
「０」と表示されてカウントがされませんでした。。

念のため実際の表の配置にあわせて正しくなおした数式を
こちらに書き込みますので、どこがおかしいか、
またどのような下人が考えられるかご指摘いただけないでしょうか？

----------

記録2の
Ａ列に日付が年/月/日で記入してある
C列に時刻が時:00で記入してある
D列，G列に数値が記入してある

現在のシートの
D１に開始の日付が年/月/日で記入してある
H１に終了の日付が年/月/日で記入してある
C１１から右に 7　8　9　…　と記入し，以降　Z１１に　6　まで記入してある

=SUMPRODUCT(($D$1<=記録2!$A$2:$A$1048576)*(記録2!$A$2:$A$1048576<=$H$1)*(HOUR(記録2!$C$2:$C$1048576)=C11)*(記録2!$D$2:$D$1048576<記録2!

ながながとホントに申し訳ないですが、よろしくお願いいたします。
ちなみに、バージョンはMicrosoft Office Excel 2007を使用しております。

回答No.3

keithin

番号１，２，３がＡ，Ｂ，Ｃ列にあるとして。

番号４のＤ１セルに
=IF(COUNTIF(A1:C1,"XX5F*"),HLOOKUP("XX5F*",A1:C1,1,FALSE),"")

Excel2007以降を使っているなら
=IFERROR(HLOOKUP("XX5F*",A1:C1,1,FALSE),"")

と入れて下向けにコピー。

＃ご利用のエクセルのバージョンによって，使える関数とか方法とか変わります。
ご相談投稿では，ご利用のソフト名は元より普段あなたが使ってるソフトのバージョンまで，キチンと明記することを覚えてください。

回答No.2

KURUMITO

初めに日付の入力なのですが2011.09.17のように入力しているのでしたら日付がシリアル値になっていませんので数値ではなく単なる文字列のようになっているということでしょうか。それとも2011/09/17のように入力してからセルの表示形式で　yyyy.mm.dd のようにして表示させているということでしょうか。ここでは後者であると解釈してお答えします。
シート１では１行目が項目名で２行目から下方にデータが有るとします。作業列を作って対応することが分かり易く計算にも負担がかかりません。
シート１のE２セルには次の式を入力して下方にドラッグコピーします。

=IF(A2="","",IF(D2>C2,HOUR(B2)*100000+A2,""))

答えの表はシート２に作成することにしてA１セルには2010/01/01のように入力し、A２セルには2012/03/02のように入力します。
A３セルから右横にはい時間ごとの時間表示をさせることにしてA３セルには7:00と入力します。B３セルには8:00と入力します。
A３セルとB３セルを選択して右方法にドラッグコピーします。
これでAC３セルまでには7:00から6:00までは表示されますね。
A３セルからAC３セルまでを選択してセルの表示形式で　h"時"　のように入力すれば７時、８時のように表示することができます。
これらの準備をしたうえでA４セルには次の式を入力してAC４セルまで右横方向にドラッグコピーします。

=COUNTIF(Sheet1!$E:$E,">="&HOUR(A3)*100000+$A1)-COUNTIF(Sheet1!$E:$E,">"&HOUR(A3)*100000+IF(HOUR(A3)>=7,$A2,$A2+1))

これで４行目には指定された期間におけるその時間帯における件数が表示されます。

お礼

KURUMITOさん、回答ありがとうございます。
シート１のデータ量が膨大なため、
新しくセルを作成するというのを避けたいんです。。＞＜

回答No.1

aokii

意味不明ですが、まずは、★の各条件毎に当てはまるセルにマーク（１）を付けて、ＡＮＤ関数で選択し、カウントしてはいかがでしょう。

指定期間 =IF(AND(A2-"2010/1/1">=0,A2-"2012/3/2"<=0),1,0)
指定時間 =IF(AND(B2-"10:11">=0,B2-"12:23"<=0),1,0)
C列よりD列のほうが数値が多い =IF(C2<D2,1,0)

お礼

aokiiさん早速の回答ありがとうございます。
★の条件をすべてまぜた計算式が知りたいんです＞＜

シート２のA1～A2の指定期間の、シート１のB列の指定時間の、シート１のC列よりD列が大きい行数という意味なんです。

わかりずらくて申し訳ないです。。＞＜