初めてご質問させていただきます。よろしくお願いいたします。
EXCELで以下のIF関数を使用しています。
U1セルには=NOW()関数で時間が表示されています。
要するに、9時15分から11時までの間で、15分間隔で大小関係を比較するセルを変更して、その結果に応じてA判定、またはB判定、または空欄のいずれかが選択されるIF関数になっています。
お伺いしたいことは以下の2点です。
(1) 各条件式で共通部分が必ず3つあります。最初のA判定とB判定の条件式を見ていただきたいのですが、HOUR($U$1)=9,MINUTE($U$1)>=15,MINUTE($U$1)<30が共通です。この共通している部分を くくり出して一つにまとめる事はできないのでしょうか?
(2) 以下のIF関数をもっと短くエレガントに記述することはできなのでしょうか?
以上2点、ご回答よろしくお願いいたします。
=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ベストアンサー
- 回答日時:
=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
- 回答日時:
似たような記述が並んでいても、僅かな違いで動作しなくなるのはVBAでも変わりはありませんので、その点に関してVBAが優れている訳ではないため、注意が必要です。
それから、何かの回答例でたまに見かけますが、具体的な解決方法を示す訳でも、補足要求する訳でも、解決方法がない理由を説明する訳でもないのは、回答しているとは言いかねますので、サイトの目的から外れていると思います。
kagakusuki 様へ
アドバイスありがとうございます。
この度の複数回のご回答に感謝いたします。
ありがとうございました。
No.10
- 回答日時:
>「多すぎる引数」「少なすぎる引数」「括弧の位置を間違えて」
似たような記述が並んでいても、こうです。
1ケタ1文字修正を試みるだけでも大変面倒な話、
数式はスラスラ~と書ける程度の長さでいいと思います。
VBAで、とは回答しましたが、
既存数式の解析がうっとおしいので考えていません。
どうなったらA判定?、どうなったらB判定?、
機能要件を提示すればVBAでのヒントは出せると思います。
さらに、
ある時間とある時間でもってAとかBとか決まるのなら、
別シートに縦軸横軸で対応表を作り、その表から探し当てる。
_____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
- 回答日時:
回答番号ANo.7です。
>最初の数式では「この関数に対して、多すぎる引数が入力されています。」と表示されました。2番目の数式では、「この関数に対して、少なすぎる引数が入力されています。」と表示されました。
申し訳御座いません、括弧の位置を打ち間違えておりました。
以下の数式に変更して下さい。
【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
- 回答日時:
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 様へ
ご回答ありがとうございます。
やりたいことができました。
1つの数式がとても短くて理解しやすかったです。
この度は本当にありがとうございました。
No.7
- 回答日時:
確認したいのですが、例えば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 様
ご回答ありがとうございます。
実際に使用しようとしたところ、最初の数式では「この関数に対して、多すぎる引数が入力されています。」と表示されました。2番目の数式では、「この関数に対して、少なすぎる引数が入力されています。」と表示されました。
教えたいただいた数式には私の知らない関数が多数使用されているため、どこをどう直せば良いのか今はまだ分からないのですが、関数を勉強して自分で直してみたいと思います。
この度は、ありがとうございました。
No.6
- 回答日時:
NO1様に同感です。
数式が長い事は百害あって一利なしです。条件のなかで5分範囲での2つ目のIF関数が偽だった場合「FALSE」と表示されるが問題ないのだろうか。
一例です。
(1)Y・Z列に時刻表を作成
0:001
9:152
9:302
9:452
10:002
10:152
10:302
10:452
11:001
Z列の1は空白、2は判定の意味です。
(2)数式
=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
- 回答日時:
こんにちは!
数式内を詳しく拝見していませんが・・・
一つの案として別セルに数式の
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
- 回答日時:
別のセルに時間のリスト作成して、そのセルを参照して数式を作成する方法にすれば
例えばA列の10行目以降に
10時間
119:15
129:30
139:45
1410:00
1510:15
1610:30
1710:45
1811:00
HOUR($U$1)=9,MINUTE($U$1)>=15,MINUTE($U$1)<30 が U1>=A11,U1<A12 で置き換えることが出来ます。
wisemac21 様へ
ご回答ありがとうございます。
教えていただいたように時間のリストを作ってU1セルと大小を比較した方がシンプルで記述も短くできますね。
この度はありがとうございました。
No.2
- 回答日時:
>HOUR($U$1)=9,MINUTE($U$1)>=15,MINUTE($U$1)<30
単純に考えると
=AND(U1<"9:30"*1,U1>="9:15"*1)
ですよね。
>15分間隔で大小関係を比較するセルを変更して、その結果に応じて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セルと大小を比較した方がシンプルで記述も短くできますね。
この度はありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) 出退勤管理の遅刻・早退時間について 3 2023/08/10 15:33
- 英語 英語得意な方、お願いします。 5 2022/08/20 14:08
- Visual Basic(VBA) VBAの繰り返し処理について教えてください。 3 2022/08/02 13:21
- Excel(エクセル) ExcelのIF関数について 4 2023/05/24 12:54
- 英語 可算名詞が、どのような場合に複数形を取らないのかについて 5 2023/01/10 10:18
- Excel(エクセル) WORKDAY関数 4 2023/06/08 13:23
- Excel(エクセル) エクセルの数式で教えてください。 3 2022/12/22 17:29
- Visual Basic(VBA) ファイル全てを .xlsm に変更したところ、プログラムが途中で落ちてしまっています 17 2022/12/07 12:03
- Excel(エクセル) R列の1111/11/11以外、且つQ列の×の条件で該当行のAからAE列までオレンジに塗りつぶす 2 2022/07/02 10:18
- Excel(エクセル) アウトラインの小計のやり方 1 2023/03/20 11:51
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
VLOOKUP関数について
-
英数字のみ全角から半角に変換
-
Microsoft Formsで「応答」から...
-
1つのPCに「Excel 2010」「Exc...
-
マイクロソフト オフィスについて
-
【スプレッドシート】指定の日...
-
Office 2021 Professional Plus...
-
【Microsoft Forms】回答を削除...
-
会社PCのメールが更新されない
-
vb.net オブジェクト指向につい...
-
outlookのメールが固まってしま...
-
Office2021を別のPCにインスト...
-
Microsoft Formsの「個人情報や...
-
【スプレッドシート】白色のセ...
-
エクセルマクロ(超初心者)
-
Excel VBA 日程表からスケジュ...
-
エクセルでXLOOKUP関数...
-
マイクロソフト 一時使用コード...
-
teams設定教えて下さい。 ①ビデ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
マイクロソフト 一時使用コード...
-
英数字のみ全角から半角に変換
-
Office2021を別のPCにインスト...
-
Microsoft Formsの「個人情報や...
-
officeビジネス型のワードやエ...
-
会社PCのメールが更新されない
-
【スプレッドシート】指定の日...
-
Microsoft Officeを2台目のPCに...
-
何このステータスバー
-
2つのシートの一致する行のセ...
-
会社のTeamsのことで相談です。...
-
エクセルにリンクされるのをし...
-
Windows 11で、IME言語バー(IM...
-
office2010とoffice365の共存で...
-
Microsoftのパソコンです。 エ...
-
エクセルでXLOOKUP関数...
-
Excel関数について質問ですm(__)m
-
VBA
-
自分の専門分野の仕事。初見で...
おすすめ情報