EXCEL IF関数内で共通の条件式をまとめる方法

質問

初めてご質問させていただきます。よろしくお願いいたします。
EXCELで以下のIF関数を使用しています。
U1セルには=NOW()関数で時間が表示されています。
要するに、９時１５分から１１時までの間で、１５分間隔で大小関係を比較するセルを変更して、その結果に応じてA判定、またはB判定、または空欄のいずれかが選択されるIF関数になっています。
お伺いしたいことは以下の２点です。
(1)　各条件式で共通部分が必ず３つあります。最初のA判定とB判定の条件式を見ていただきたいのですが、HOUR($U$1)=9,MINUTE($U$1)>=15,MINUTE($U$1)<30が共通です。この共通している部分を くくり出して一つにまとめる事はできないのでしょうか？
(2)　以下のIF関数をもっと短くエレガントに記述することはできなのでしょうか？

以上２点、ご回答よろしくお願いいたします。

=IF(AND(HOUR($U$1)=9,MINUTE($U$1)>=15,MINUTE($U$1)<30,C3>B3),"A",IF(AND(HOUR($U$1)=9,MINUTE($U$1)>=15,MINUTE($U$1)<30,C4<B4),"B",IF(AND(HOUR($U$1)=9,MINUTE($U$1)>=30,MINUTE($U$1)<45,D3>C3),"A",IF(AND(HOUR($U$1)=9,MINUTE($U$1)>=30,MINUTE($U$1)<45,D4<C4),"B",IF(AND(HOUR($U$1)=9,MINUTE($U$1)>=45,MINUTE($U$1)<=59,E3>D3),"A",IF(AND(HOUR($U$1)=9,MINUTE($U$1)>=45,MINUTE($U$1)<=59,E4<D4),"B",IF(AND(HOUR($U$1)=10,MINUTE($U$1)>=0,MINUTE($U$1)<15,F3>E3),"A",IF(AND(HOUR($U$1)=10,MINUTE($U$1)>=0,MINUTE($U$1)<15,F4<E4),"B",IF(AND(HOUR($U$1)=10,MINUTE($U$1)>=15,MINUTE($U$1)<30,G3>F3),"A",IF(AND(HOUR($U$1)=10,MINUTE($U$1)>=15,MINUTE($U$1)<30,G4<F4),"B",IF(AND(HOUR($U$1)=10,MINUTE($U$1)>=30,MINUTE($U$1)<45,H3>G3),"A",IF(AND(HOUR($U$1)=10,MINUTE($U$1)>=30,MINUTE($U$1)<45,H4<G4),"B",IF(AND(HOUR($U$1)=10,MINUTE($U$1)>=45,MINUTE($U$1)<=59,I3>H3),"A",IF(AND(HOUR($U$1)=10,MINUTE($U$1)>=45,MINUTE($U$1)<=59,I4<H4),"B",""))・・・括弧省略

回答No.5

mshr1962 · ベストアンサー

=IF(AND(INT(MOD($U$1,1)*96)>=37,INT(MOD($U$1,1)*96)<44),IF(INDIRECT(CHAR(INT(MOD($U$1,1)*96)+30)&3)>INDIRECT(CHAR(INT(MOD($U$1,1)*96)+29)&3),"A",IF(INDIRECT(CHAR(INT(MOD($U$1,1)*96)+30)&4)<INDIRECT(CHAR(INT(MOD($U$1,1)*96)+29)&4),"B","")),"")

解説
数式1=MOD($U$1,1)　：NOW()の結果から日付（整数部分）を除いて時間表示
数式2=INT(数式1*96)　：時間（シリアル値）*24*4で15分=1に変換
※数式2の結果
09:15-09:29 => 37、09:30-09:44 => 38、09:45-09:59 => 39
10:00-10:14 => 40、10:15-10:29 => 41、10:30-10:44 => 42、10:45-10:59 => 43

数式3=AND(数式2>=37,数式2<44)　：9:15～10:59は真、左記を外れると偽
数式4=CHAR(数式2+30)　：数値をキャラクタ文字に変換
数式5=CHAR(数式2+29)　：数値をキャラクタ文字に変換
※数式4,5の結果
CHAR(66) => B、CHAR(67) => C、CHAR(68) => D、CHAR(69) => E
CHAR(70) => F、CHAR(71) => G、CHAR(72) => H、CHAR(73) => I

数式6=INDIRECT(数式4&3)　：()内の文字が示すセル範囲を参照
数式7=INDIRECT(数式4&4)　：()内の文字が示すセル範囲を参照

※数式6,7の結果
INDIRECT("B3") => B3、INDIRECT("B4") => B4

お礼

mshr1962 様へ

ご回答ありがとうございます。
やりたいことができました！こんなに短く記述できるのですね！
丁寧に数式の意味も解説してくださって、とてもわかりやすかったです。
この度は、本当にありがとうございました。

回答No.11

kagakusuki

似たような記述が並んでいても、僅かな違いで動作しなくなるのはVBAでも変わりはありませんので、その点に関してVBAが優れている訳ではないため、注意が必要です。

　それから、何かの回答例でたまに見かけますが、具体的な解決方法を示す訳でも、補足要求する訳でも、解決方法がない理由を説明する訳でもないのは、回答しているとは言いかねますので、サイトの目的から外れていると思います。

お礼

kagakusuki 様へ

アドバイスありがとうございます。
この度の複数回のご回答に感謝いたします。
ありがとうございました。

回答No.10

layy

＞「多すぎる引数」「少なすぎる引数」「括弧の位置を間違えて」

似たような記述が並んでいても、こうです。
１ケタ１文字修正を試みるだけでも大変面倒な話、
数式はスラスラ～と書ける程度の長さでいいと思います。

ＶＢＡで、とは回答しましたが、
既存数式の解析がうっとおしいので考えていません。
どうなったらＡ判定？、どうなったらＢ判定？、
機能要件を提示すればＶＢＡでのヒントは出せると思います。

さらに、
ある時間とある時間でもってＡとかＢとか決まるのなら、
別シートに縦軸横軸で対応表を作り、その表から探し当てる。
_____9:00 9:15 9:30 9:45 ・・・・・
9:00 A A A A
9:15 A A A A
9:30 A A A A
9:45 A A A A
・・
修正も楽ですし、数式よりは遙かにスッキリしそうです。

なので、
今回数式が出来たとしても、数式にこだわらず、さらなる改善を期待します。
今後の変更に耐えうる仕組みとしましょう。

お礼

layy 様へ

再びのご回答ありがとうございます。
とても勉強になります。保守のしやすさを考えて作る事が重要なのですね。
この度は、ありがとうございました。

回答No.9

kagakusuki

回答番号ANo.7です。

>最初の数式では「この関数に対して、多すぎる引数が入力されています。」と表示されました。２番目の数式では、「この関数に対して、少なすぎる引数が入力されています。」と表示されました。

　申し訳御座いません、括弧の位置を打ち間違えておりました。
　以下の数式に変更して下さい。

【1秒未満の誤差を気にする必要がある場合】
=IF(AND(MOD($U$1,1)>="9:15"+0,MOD($U$1,1)<"11:00"+0),IF(OFFSET(C3,,INT((HOUR($U$1)-9)*4+(MINUTE($U$1)-15)/15))>OFFSET(B3,,INT((HOUR($U$1)-9)*4+(MINUTE($U$1)-15)/15)),"A",IF(OFFSET(C4,,INT((HOUR($U$1)-9)*4+(MINUTE($U$1)-15)/15))<OFFSET(B4,,INT((HOUR($U$1)-9)*4+(MINUTE($U$1)-15)/15)),"B","")),"")

【1秒未満の誤差を気にする必要が無い場合】
=IF(AND(MOD($U$1,1)>="9:15"+0,MOD($U$1,1)<"11:00"+0),IF(OFFSET(C3,,INT((MOD($U$1,1)-"9:15")*96))>OFFSET(B3,,INT((MOD($U$1,1)-"9:15")*96)),"A",IF(OFFSET(C4,,INT((MOD($U$1,1)-"9:15")*96))<OFFSET(B4,,INT((MOD($U$1,1)-"9:15")*96)),"B","")),"")

お礼

kagakusuki 様へ

再びのご回答ありがとうございます。
やりたいことができました。
この度は本当にありがとうございました。

回答No.8

ki-aaa

U2セルに
=TEXT(U1,"hh:mm")

U3セルに
=IF(U2<"09:15",-1,IF(U2<"09:30",0,IF(U2<"09:45",1,IF(U2<"10:00",2,IF(U2<"10:15",3,-1)))))

それで数式は
=IF(U3=-1,"",IF(OFFSET(C3,,U3)>OFFSET(B3,,U3),"A",IF(OFFSET(C4,,U3)<OFFSET(B4,,U3),"B","")))

お礼

ki-aaa 様へ

ご回答ありがとうございます。
やりたいことができました。
１つの数式がとても短くて理解しやすかったです。
この度は本当にありがとうございました。

回答No.7

kagakusuki

確認したいのですが、例えば9:40の時に、D3>C3とD4<C4が共に成り立っている場合には「A」で宜しいのでしょうか？
　又、D3=C3の場合は「A」ではないと考えれば宜しいのでしょうか？
　又、D3>C3ではなく、且つD4>C4か又はD4=C4の場合には表示無しで宜しいのでしょうか。

　もし、そうであれば、(2)は次の様な数式となります。

=IF(AND(MOD($U$1,1)>="9:15",MOD($U$1,1)<"11:00"),IF(OFFSET(C3,,INT((HOUR($U$1)-9)*4+(MINUTE($U$1)-15)/15))>OFFSET(B3,,INT((HOUR($U$1)-9)*4+(MINUTE($U$1)-15)/15)),"A",IF(OFFSET(C4,,INT((HOUR($U$1)-9)*4+(MINUTE($U$1)-15)/15))<OFFSET(B4,,INT((HOUR($U$1)-9)*4+(MINUTE($U$1)-15)/15)),"B",""),"")

　あえてHOUR関数やMINUTE関数を使用している理由は、シリアル値を用いた判定では、1秒未満の端数が影響して、59秒が1分と扱われたり、1分が59秒と扱われたりする事が、稀にあるため、その対策です。

　もし、1秒未満の誤差を気にする必要が無い場合には、次の様な数式となります。

=IF(AND(MOD($U$1,1)>="9:15",MOD($U$1,1)<"11:00"),IF(OFFSET(C3,,INT((MOD($U$1),1)-"9:15")*96))>OFFSET(B3,,INT((MOD($U$1),1)-"9:15")*96)),"A",IF(OFFSET(C4,,INT((MOD($U$1),1)-"9:15")*96))<OFFSET(B4,,INT((MOD($U$1),1)-"9:15")*96)),"B",""),"")

お礼

kagakusuki 様

ご回答ありがとうございます。
実際に使用しようとしたところ、最初の数式では「この関数に対して、多すぎる引数が入力されています。」と表示されました。２番目の数式では、「この関数に対して、少なすぎる引数が入力されています。」と表示されました。
教えたいただいた数式には私の知らない関数が多数使用されているため、どこをどう直せば良いのか今はまだ分からないのですが、関数を勉強して自分で直してみたいと思います。
この度は、ありがとうございました。

回答No.6

mu2011

NO1様に同感です。数式が長い事は百害あって一利なしです。
条件のなかで５分範囲での２つ目のIF関数が偽だった場合「FALSE」と表示されるが問題ないのだろうか。
一例です。
（１）Y・Z列に時刻表を作成
0:00 1
9:15 2
9:30 2
9:45 2
10:00 2
10:15 2
10:30 2
10:45 2
11:00 1
Z列の１は空白、２は判定の意味です。

（２）数式
=CHOOSE(VLOOKUP(TEXT($U$1,"hh:mm")*1,$Y$1:$Z$9,2),"",IF(OFFSET($A$3,0,MATCH(TEXT($U$1,"hh:mm")*1,$Y$1:$Y$9))>OFFSET($A$3,0,MATCH(TEXT($U$1,"hh:mm")*1,$Y$1:$Y$9)-1),"A",IF(OFFSET($A$3,1,MATCH(TEXT($U$1,"hh:mm")*1,$Y$1:$Y$9))>OFFSET($A$3,1,MATCH(TEXT($U$1,"hh:mm")*1,$Y$1:$Y$9)-1),"B")))

お礼

mu2011 様へ

ご回答ありがとうございます。
教えたくださった数式の$A$3の部分をどう直せば私のやりたいことができるのか只今勉強中です（なにしろ、私の知らない関数が沢山使用されているので）。
それにしても、こんなに短く記述できるのですね！
この度は、本当にありがとうございました。

回答No.4

tom04

こんにちは！
数式内を詳しく拝見していませんが・・・

一つの案として別セルに数式の
HOUR($U$1)=9,MINUTE($U$1)>=15,MINUTE($U$1)<30　部分を表示してそのセル番地を数式内に組み入れればもっと数式は短くなると思います。

↓の画像でU1・U2セルともセルの表示形式は時刻としています。
仮にU1セルに　=NOW()　と入っているとします。
（画像は判りやすくするために、手入力しています）
U2セルに
=FLOOR(U1,"0:15")
または表示形式を変えたくない場合は
=TEXT(FLOOR(U1,"0:15"),"h:mm")*1
とします。

これで数式の　HOUR($U$1)=9,MINUTE($U$1)>=15,MINUTE($U$1)<30　部分は
「$U$2」　と置き換えれば大丈夫だと思います。

尚、B～I列の具体的な配置が判らないので今はこの程度ですが、
配置関係が判ればもっと具体的な回答もあると思いますよ！
この程度ですが、参考にならなかったらごめんなさいね。m(__)m

お礼

tom04 様へ

ご回答ありがとうございます。
画像で説明してくださって、とてもわかりやすかったです。
教えていただいたようにFLOOR関数を使った方がシンプルで記述も短くできますね。
この度はありがとうございました。

回答No.3

wisemac21

別のセルに時間のリスト作成して、そのセルを参照して数式を作成する方法にすれば
例えばA列の10行目以降に
10 時間
11 9:15
12 9:30
13 9:45
14 10:00
15 10:15
16 10:30
17 10:45
18 11:00

HOUR($U$1)=9,MINUTE($U$1)>=15,MINUTE($U$1)<30　が　U1>=A11,U1<A12 で置き換えることが出来ます。

お礼

wisemac21 様へ

ご回答ありがとうございます。
教えていただいたように時間のリストを作ってU1セルと大小を比較した方がシンプルで記述も短くできますね。
この度はありがとうございました。

回答No.2

hallo-2007

＞HOUR($U$1)=9,MINUTE($U$1)>=15,MINUTE($U$1)<30
単純に考えると
=AND(U1<"9:30"*1,U1>="9:15"*1)
ですよね。
＞１５分間隔で大小関係を比較するセルを変更して、その結果に応じてA判定、またはB判定、または空欄＞のいずれかが選択されるIF関数になっています。
15分というのが　15/24/60 の値ですので
U1セルに　9:40　とか時刻が入っているとして
=AND(U1<"9:00"*15/24/60,U1>="9:00"+0/24/60)
でも良いでしょう。
目的がわかりませんが、空いている列に
=AND(U$1<"9:00"+(15/24/60)*ROW(A2),U$1>="9:00"+(15/24/60)*ROW(A1))
と入れて下までコピーしてみてください。
U1の値によってどこかのセルが　TRUE　それ以外が　FALSE　の結果が得られると思います。
或いは
A列に
9:00
9:15
・・・と時刻の列を入れておけば
=AND(U$1<A3,U$1>=A2)と出来て表としてわかりやすいと思うのですが
如何でしょうか。

お礼

hallo-2007 様へ

ご回答ありがとうございます。
教えていただいたように時刻の列を作ってU1セルと大小を比較した方がシンプルで記述も短くできますね。
この度はありがとうございました。

回答No.1

layy

個人的には、
何かの回答例でたまに見かけますが、
これ以上に長ーい数式を提供しどうだと言わんばかりに美学を求めている感じも
よくわかりませんね。
（結果が出たとしても）保守性は劣るしうっとおしいので好きではありません。

規則性があるようですし、
ＶＢＡでオリジナル関数化を考えても良いと思います。
ｘとｙを引数として判定ｚを返す、普通のFUNCTIONの機能です。
こちらのがスッキリするし、保守しやすいと想像できます。

お礼

layy 様へ

ご回答ありがとうございます。
確かにVBAで作った方がいいのかもしれませんね。
これからVBAの勉強を始めてみます。
この度はありがとうございました。