Excel（エクセル）活用コミュのセル内の文字検索について

こんにちはホリャンと申します
皆さんの知恵を拝借させていただきたくトピックを作らさせていただきました。

今回保守会社下請けの勤務表を作成しているのですがその際
1日の現場名のところに代休と入力したら残業時間より-8時間されるような
式を組もうとしたのですが、どのようにググればいいのかわからずこちらに
書かさせて頂きました

写真を見ていただければなんとなくの書式は書いたのですが
4行で1日分
A列に日付と曜日
B列に現場名
C～E列に始業～終業時間
F列に残業時間
という形で作っております

F1には終業時間から始業時間を引いたもの
その下のF2にはB1:B4のセル内に代休という文字が含まれていた場合
F2には-8を表示
B1:B4のセル内に代休という文字が含まれていない場合は
F1から9時間を引いたものを計算

という感じで日本語の式では考えておりますが
それを関数で当てはめた場合-8の表示にしかならなく困っています
実際にF1に入れた式になります
｢=IF(ISERROR(MATCH("代休",B1:B4,0)),-8,HOUR(F1)+MINUTE(F1)/60-9)｣

また何か足りないことがありましたらご指摘願います

コメント(22)

最初
全て
最新の40件

[1] mixiユーザー 12月08日 01:24

>代休という文字が含まれていた場合F2には-8を表示
ということは、真と偽が逆ではありませんか？

[5] mixiユーザー 12月08日 02:29

真と偽が逆でした
｢=IF(ISERROR(MATCH("代休",B1:B4,0)),HOUR(F1)+MINUTE(F1)/60-9,-8)｣
にしたところ「#VALUE」をIF文の真にて返されてしまいました

若干伝え忘れがありました
本来のブックでは代休の時、始業と終業の時刻が空欄でF1に「E1-C1」としていたのでIFの真の値にてエラーとなって返されていたと思われます

F1の式をIF文で時刻が入力されていない場合は0を返すように設定したら
エラーで返されなくなりました。

ですがBの現場名に代休と入れても、現場名を入れても「-9」を返されてしまいます

なにか間違っているのでしょうか？

[6] mixiユーザー 12月08日 02:49

>｢=IF(ISERROR(MATCH("代休",B1:B4,0)),HOUR(F1)+MINUTE(F1)/60-9,-8)｣
にしたところ「#VALUE」をIF文の真にて返されてしまいました

時刻が空欄の状態で=E1-C1の結果は0になっていませんか？
セルが空白である場合、Excelは0として計算するはずなので
0-0=0のはずなんですが

>Bの現場名に代休と入れても、現場名を入れても「-9」を返されてしまいます
B1:B4の範囲の中に「代休」という文字列を入力していますか？

[7] mixiユーザー 12月08日 03:11

あと、
=E1-C1
=ISERROR(MATCH("代休",B1:B4,0))
=HOUR(F1)+MINUTE(F1)/60-9
などのように個別で確認して、正常に算出できていますか？

[8] mixiユーザー 12月11日 20:39

返事遅くなって申し訳ありません

>>Bの現場名に代休と入れても、現場名を入れても「-9」を返されてしまいます
>B1:B4の範囲の中に「代休」という文字列を入力していますか？
入力しています

とりあえず代休の日の-8時間はできましたが
｢=IF(ISERROR(MATCH("代休",B1:B4,0)),-8,HOUR(F1)+MINUTE(F1)/60-9)｣
最初の通り真と偽が逆でないと正しく算出できません
何か間違っているような・・・
よくわからなくなってきました

=E1-C1
OK

=ISERROR(MATCH("代休",B1:B4,0))
代休が入ってても入ってなくても「TRUE」になります

=HOUR(F1)+MINUTE(F1)/60-9
OKです

よくわかりません・・・

[9] mixiユーザー 12月11日 21:16

=MATCH("代休",B1:B4,0)
の値はどうなっていますか？

[10] mixiユーザー 12月12日 00:14

横から失礼します。

MATCH関数の範囲指定　B1:B4　って変じゃないですか？

=MATCH("代休",B1:B8,0)

↑くらいにしないと"代休"が含まれていないのでVALUE#が出てしまうのではないでしょうか？

[11] mixiユーザー 12月12日 00:26

>10
4行で1日分ということなので、その日のB列に「代休」があるかないか
判断するということであれば、B1:B4で問題ないのでは？
普通は「代休」がなければ#N/Aになるはずなので、ISERROR関数で
あるのかないのか判断しているのだと思うのですが

[12] mixiユーザー 12月12日 01:43

検索範囲に「代休が」あってもなくても#N/Aでした

[13] mixiユーザー 12月12日 01:49

=COUNTIF(B1:B4,"代休")
のようにCOUNTIF関数で「代休」の数をCOUNTするとどうでしょう？

[14] mixiユーザー 12月12日 01:59

あとは、「代休」を入力したセルをB2セルとして
=B2="代休"
というようにしてTRUEが返って来るか調べるとか

[15] mixiユーザー 12月12日 02:07

F1セル　　=E1-C1
F2セル　　=IF(ISERROR(MATCH("代休",B1:B4,0)),HOUR(F1)+MINUTE(F1)/60-9,-8)

この式で、B1～B4に代休とあった場合は、F2セルに　-8　と表示されますよ。

無い場合には、　F２セルに　0　と表示されます。

>F1には終業時間から始業時間を引いたもの
>その下のF2にはB1:B4のセル内に代休という文字が含まれていた場合
>F2には-8を表示
>B1:B4のセル内に代休という文字が含まれていない場合は
>F1から9時間を引いたものを計算

今までの皆さんの、計算式で、もう条件は叶ってるのでは？

[16] mixiユーザー 12月12日 02:12

>15　の式でやった時の結果を載せておきます。