IF関数やCOUNTIF関数など詳しい方

質問

エクセルについての質問です

ある機械の稼動グラフを作りたいと思っています
そのためのデータをまとめたいのですが

シート１のＡ列に１分おきの時刻表があり
シート２のＡ列に発生時刻（時間帯はバラバラです）、Ｂ列にＯＮとＯＦＦがはいっています

こんな感じです

シート1　　　　　　シート２
　　A　　　B　　　　　　A　　　B
1　9:00　　0　　　　1　8:20　　ON
2　9:01　　　　　　　2　8:43　　OFF
3　9:02　　　　　　　3　9:03　　ON
4　9:03　　　　　　　　　（略）
　　（略）

このときシート１のＢ２から下に、ＯＮになれば５、ＯＦＦになれば０
何もないときは上のセルと同じ値（ＯＦＦのときは０が続いていく感じです）を入れたいのですが
なかなかうまくいきません（日付などはあわしています）

誰か詳しい方ご教授お願いします

回答No.1

kybo · ベストアンサー

B1に以下のような数式を入れて、その数式を下へコピーしてみてください。

=IF(VLOOKUP(A1,Sheet2!$A$1:$B$4,2,TRUE)="OFF",0,5)

お礼

VLOOKUPのTRUEはよくわかってませんでした。

バッチリできました。

すごく勉強になりました。ありがとうございました。

回答No.4

imogasi

>なかなかうまくいきません
検索はVLOOKUPやMATH関数を使うが、どういう関数でやったか書くべきだ。
VLOOKUP関数でやったが、など。
Sheet2に該当無しはどうしましたか？
＞（日付などはあわしています
とはどういうこと？
日付部分は例データに無いが、実際データではあるのか？。
時刻は日付時刻シリアル値で入っていることに間違いないか。文字列ではないでしょうね。
単純なケースでは
Sheet2　A,B列
8:20 ON 
8:43 OFF
9:01 ON
9:03 ON
9:04 OFF
・・・
Sheet1で　A,B列
9:00 #N/A
9:01 ON
9:02 #N/A
9:03 ON
9:04 OFF
B1の式は　=VLOOKUP(A1,Sheet2!$A$1:$B$10,2,FALSE)
下方向に式を複写
結果上記B列
＞何もないときは上のセルと
該当なしのばあい、と言うべき。
ISNAやISERRORで検知する。
その場合Sheet1上で直前の行のB列を取るらしいから、最初行のデータでも、直前行を考えるのは出来ないから
B２は=IF(ISERROR(VLOOKUP(A2,Sheet2!$A$1:$B$10,2,FALSE)),B1,VLOOKUP(A2,Sheet2!$A$1:$B$10,2,FALSE))
と入れて下方向に式を複写する。
B1は特別に　=IF(ISERROR(VLOOKUP(A1,Sheet2!$A$1:$B$10,2,FALSE)),"OFF","ON")
にする。
Sheet1 A列　B列　B列（改良）
9:00 #N/A OFF
9:01 ON ON
9:02 #N/A ON
9:03 ON ON
9:04 OFF OFF
このONなら５、OFFなら０は
Sheet2でC列に
=IF(B1="ON",5,0とト入れて、下方向に式複写。
Sheet2　
A列　　　　B列　　　　C列
8:20 ON 5
8:43 OFF 0
9:01 ON 5
9:03 ON 5
9:04 OFF 0
C列データを取ってくるほうが、Sheet2のB列の式が長くならなくて済むように思う。
時刻シリアル値に付いては
既出回答でご指摘の件は、Googleででも「エクセル　時刻　値　誤差」で照会すれば下記ほか沢山記事がある点です。
http://www.excel.studio-kazu.jp/mwiki/index.php/%E6%99%82%E5%88%BB%E8%A8%88%E7%AE%97%E8%AA%A4%E5%B7%AE%E3%81%AE%E8%A7%A3%E6%B6%88%E6%96%B9%E6%B3%95
本件の場合、気にしないで済むかどうか判らない。

お礼

つっこみありがとうございます。

自分はＣＯＵＮＴＩＦ関数をつかってました。

いろいろな考え方がありますね。

参考になりました。　ありがとうございます。

回答No.3

noname#204879

》 ＯＮになれば５、ＯＦＦになれば０何もないときは上のセルと同じ

上の意味は、ＯＦＦからＯＮに変化すれば５、ＯＮからＯＦＦに変化すれば０、変化ないときは上のセルと同じ、ということですか？

補足

はい、そうです。

説明が下手で申し訳ないです。

回答No.2

KURUMITO

時刻はシリアル値で見ますと小数点以下の桁数が多くお示しのようなケースではシート１とシート２の表示時刻が同じでもシリアル値では違っている場合があり必ずしも一致しているとは言えません。そのために単なる式ではエラーとなり正しい答えが得られません。そこで式の上で多少の工夫が必要となります。ここでは表示の時間にプラス０．１秒をして関数を使っています。
例えばB２セルには次の式を入力して下方にオートフィルドラッグします。

=IF(ISERROR(MATCH(A2+"0:00:00.1",Sheet2!A:A,1)),B1,IF(INDEX(Sheet2!B:B,MATCH(A2+"0:00:00.1",Sheet2!A:A,1))="ON",5,0))

INDEX関数の代わりのVLOOKUP関数を使ってもよいでしょう。

お礼

試してみましたがうまくいきませんでした。
上から下までずっと０がでます。

参考にしながらいろいろやってみます。
ありがとうございました。