システムメンテナンスのお知らせ

こんにちは、エクセル365を使っています。

画像のようにD列に時刻のシリアル値が並んでいて、それをユーザー定義でE列のように表示させています。

このデータは最上段は常に今の時刻になるように1時間おきに下に自動で更新して行きます。

この数値を自動判別し、文字を付加したいと思います。

要は今週の開始時の8時に、【今週】 と言う文字を付加し、 今週 8:00 と表示させたいです。

月曜日の8:00のみに 今週 を付加すると言う事です。

詳しい方、よろしくお願い致します。

「エクセル 時刻のシリアル値を自動判別し文」の質問画像

質問者からの補足コメント

  • うれしい

    失礼しました。

    説明用に書いた少し前のデータで試していました(笑)
    お陰様で出来ました、ありがとうござます。

    No.2の回答に寄せられた補足コメントです。 補足日時:2021/01/25 23:49
gooドクター

A 回答 (2件)

>このデータは最上段は常に今の時刻になるように1時間おきに下に自動で更新して行きます。


とのことですが、更新方法についての説明がありません。
VBAで更新されているとすると下手に関数を使うと行ずれ等を起こす可能性もあります。
本件は一時間毎の更新なのでNOW()関数を使いたいところです。No.1さんのTODAY()関数であれば、更新タイミングの問題はないと思いますが、上記の更新タイミングとNOW()とのタイムラグが心配です。
例えば、他システムと連携して、EXCELに更新時刻と他システムからのデータを転記していくような仕様だとします。
他システムからのデータ渡しが、仮に1分後だとすると、NOW()関数を使うと1分間のタイムラグが発生してしまいます。
そこで、あくまでもEXCELに表示されているもののデータで判断して、「今週 8:00」と表示させるための方法を考えでみました。

ご質問者のニーズは「今週月曜日8時」に印を付けたいということだと理解し、営業日ペースで何らかの履歴を確認したいのだと推測しました。
さて、現在の時刻を基準に「今週の月曜日8時」を考えるとき、現在が日曜日の午前9時なら、翌日の8時が「今週月曜日8時」なわけですが、当然のことながら、まだやってこない月曜日8時は表上に存在せず、表示することはできません。
そこで、No.1さんの、
>まず、週の始まりは一般的には日曜日だけれど、月曜日を週の始めと考えるということでしょうか?
というご指摘のとおり「月を週の始め」と考えるものとします。
こうすると、「今週月曜日8時」の実質的な意味は「現在から遡って、直近の月曜日8時」ということになります。
なお、この表の自動更新は営業日とは無関係に、たとえ休業日であっても更新は続いていることを前提とします。

ここでは「条件付き書式」を使用します。
添付画像をご覧ください。
(1)E列を選択し、条件付き書式で、「ルールの管理」から「新規ルール」ボタンを押し、「数式を使用して、書式設定するセルを決定」を選択する
(2)「次の数式を満たす場合に値を書式設定」の数式欄に以下の数式を記述
=ROW()=VALUE(TEXT(MAX($D:$D),"h"))+IF((WEEKDAY(MAX($D:$D),3)=0)*(VALUE(TEXT(MAX($D:$D),"h"))<8),7,WEEKDAY(MAX($D:$D),3))*24-7+2・・・・・・(a)
(3)書式ボタンを押し、「ユーザー定義」を選択し、「種類(T)」欄に「"今週" h:mm」と記述し、OKボタンを押す
(4)新しい書式ルールのダイアログボックスに戻るのでOKボタンを押す
(5)「条件付き書式設定のルール」のダイアログボックスの「適用先」に「=$E:$E」と表示されていることを確認し、適用ボタン、OKボタンを押す
(6)条件に合致したE列のセルに「今週 8:00」と表示される

※数式の解説
この数式の目的は表中の最新の時刻(最上段のシリアル値)から過去の直近の月曜日8時が何行目にあるかを計算することです。
①見出し行がないものとすると、最新データは1行目にあり、VALUE(TEXT(MAX($D:$D),"h"))+1・・・・・・(b) この行数には本日午前0時のデータがあるはずである
②IF((WEEKDAY(MAX($D:$D),3)=0)*(VALUE(TEXT(MAX($D:$D),"h"))<8),7,WEEKDAY(MAX($D:$D),3))・・・・・・(c) 今日が月曜日かつ8時前か判定し、YESなら「7日後」と判定する。NOならWEEKDAY(シリアル値,3)で月曜日から数えて何日後かを求める
③上記(c)に24を掛けて過去の直近月曜日の午前0時から何時間経過したか算出・・・・・・(d)
④上記(b)と(d)を加えた行は過去の直近月曜日の午前0時のデータのはずである。これから8を引いた行には直近月曜日の午前8時のデータがあるはずである
⑤これらを纏めるとVALUE(TEXT(MAX($D:$D),"h"))+1+IF((WEEKDAY(MAX($D:$D),3)=0)*(VALUE(TEXT(MAX($D:$D),"h"))<8),7,WEEKDAY(MAX($D:$D),3))*24-8となるが、式中に「+1」と「-8」があるため合算整理する
⑥整理し、見出し行(投稿画像から見ると2行)を加えて VALUE(TEXT(MAX($D:$D),"h"))+IF((WEEKDAY(MAX($D:$D),3)=0)*(VALUE(TEXT(MAX($D:$D),"h"))<8),7,WEEKDAY(MAX($D:$D),3))*24-7+2
⑦上記数式で過去の直近月曜日の午前8時の行数が判明したので、その行数と一致する行に「今週」を表示するための数式が(a)である
「エクセル 時刻のシリアル値を自動判別し文」の回答画像2
この回答への補足あり
    • good
    • 0
この回答へのお礼

回答頂き、ありがとうございます。

E列に
=ROW()=VALUE(TEXT(MAX($D:$D),"h"))+IF((WEEKDAY(MAX($D:$D),3)=0)*(VALUE(TEXT(MAX($D:$D),"h"))<8),7,WEEKDAY(MAX($D:$D),3))*24-7+2

の条件付き書式を設定し、ユーザー定義を "今週" h:mm としたのですが、なぜか表示されないみたいです。

時間が無いので、後でもう一度試してみます。

ありがとうございます。

お礼日時:2021/01/25 23:27

こんにちは



ご質問の内容をよく把握できないので、ヒントのみです。

まず、週の始まりは一般的には日曜日だけれど、月曜日を週の始めと考えるということでしょうか?
それによって、「今週」の意味が変わってきますけれど、文脈からはそのように思えるので…

仮に、そうであるものとして、「今日」を基準に、その週の月曜日の日付を計算したければ、
 TODAY()-WEEKDAY(TODAY(),3)
で求めることができます。
D列の日付を、この値と比較することで「今週の月曜日」か否かを判別できます。

>月曜日の8:00のみに 今週 を付加すると言う事です
8:00ジャストだけをピンポイントでチェックして、7:59:59でも8:00:01でも無いということでしょうか?
ジャストで良いのならば、今週月曜の8:00は、上記に8時間を加えて
 TODAY()-WEEKDAY(TODAY(),3)+1/3
となるので、これがD列の値と等しいかをif関数で判別して、【今週】を付加するかどうか決めればよいのではないでしょうか?

※ 意味が違う場合はスルーしてください
    • good
    • 0
この回答へのお礼

回答頂き、ありがとうございます。

今週の月曜のシリアル値が特定出来ました。

ありがとうございます!

お礼日時:2021/01/25 23:17

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

gooドクター

このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング