条件付き書式 別のシートで色付けされたセルデータの転記漏れを防ぐ書式を入れたい

連日、質問させていただきます。

画像を添付は1枚で、Sheet1とSheet2と並べてあります。
先日ここで教えていたたいた条件付き書式の「HLOOKUP・・・」で指定の曜日と同じ曜日に○の付いている人のID番号に色がつくようになりました。
更に次の段階でしたいのが、Sheet1の結果から、Sheet2の方に、色付けされた人のID番号を転記します。
Sheet2では、該当する曜日の人だけの情報を転記するデータになっています。ID番号だけ手入力すれば、その行に自動的にSheet1から転記されるような計算式が入っています。

手入力というのは、必ずしもSheet1の当該行がSheet2で順番に並ぶわけではないからです。
この手入力というのはやむを得ないのです。

ここまではできているのですが、もしSheet1から番号をSheet2に転記忘れをした時に、分かるようにしたいのです。
この場合だと、
Sheet1のB5:B9（あるいはG5:G9でよいか）の中で色付けされたセルと同じ行のA列には
通し番号を入れておきますが、そこのセルが、添付画像のようにもしSheet2に転記されていない場合は、赤くなるような条件付き書式を入れたいと思います。

つまり、Sheet1のB4:B8の範囲で色付けされたセルの中の数字が、Sheet2のA4:A8 になければ、ない番号と同行のＡ列に赤い色がつく、という条件付き書式を入れたいということです。

そんなことができるのでしょうか。

どなたかご教示いただければありがたいです。

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

• 「No.2」のご回答に
  「わかってないなぁ」と思いながら、何度もご回答いただき、ほんとに恐縮です。
  更に恐縮しながら、補足質問させていただきます。
  
  ご回答の作業列というのが参考になりました。
  Sheet1のA3:A7の通し番号に条件付き書式を入れて、
  =AND($B3,HLOOKUP($D$1,$F$2:$K$7,ROW($C3)-1,FALSE)="○")
  とすると、該当するID番号と同列の通し番号に赤い色が付きました。
  次にやりたいのは、「TRUE」の条件の中で、Sheet1の$C5が、
  Sheet2のA3:A7になければ、赤い色がつくようにしたいんです。
  
  違うかもしれませんが、上記条件に加えて、「*COUNTIF(Sheet2!$A$3:$A$7,"<>$C5"),FALSE)
  と入れたのですが、変化がありません。
  
  もう少しのような気がしますが、ご教示いただけないでしょうか。

  
    補足日時：2022/04/26 09:53

No.4 ベストアンサー 回答者： fujillin 回答日時： 2022/04/26 18:16

No3です

ひとまず、お望みのようになったようなので、よかったですね。

＞この手入力というのは、私がするのではなく、入力ならできるという
＞他の職員も触るので、もちろん保護は書けますが
であるなら、なおさらですが、異なるシートを参照しながら入力するような操作法に疑問を感じます。
（他人がやることだからどうでもいいというのなら別ですが・・）

入力操作中にその画面内でチェックや確認ができることが、効率的であり、また、間違えも少なくなるのではないかと想像します。
何らかの「グループ分けをしたい」のなら、リストに対してグループを入力するUIにしておけば、入力漏れや記入間違い、入力のダブり等のご懸念の間違えも避けることが容易になるのではないかと想像します。

前にも書きましたが、操作のための表示と、最終目的の表示は異なるはずなので、途中の表示はその目的にああったもので行い、最終的に使用するものは見やすく適切なレイアウトに編集するのが宜しいかと。
全部を兼用できれば一番良いですが、大抵の場合、その都度で利用者が異なったり、目的が異なったりするので、無理に兼用すると間違えの元になったりすることもあると思います。

一方で、上記は沢山の画面を設けるという意味ではなく、よく考えて不要な表示や操作を省くことで、効率も良くなり操作性も良くなるのではないかという意味です。
その意味はからは、まだまだ省けそうなものがありそうにも感じますが、全体像が不明なのでこの辺で・・

この回答へのお礼

いろいろありがとうございました。
きっと全体像をおみせすることができれば、ベストアンサーがあったかと思いますが、限られた情報の中でしかお示しできないのが残念です。
省けるところもいっぱいあると・・それもそうだろうなと認識しております。

＞（他人がやることだからどうでもいいというのなら別ですが・・）
いいえ、そうでなければこんなに時間は割きません。

エクセルは奥が深く、まだまだ勉強不足ですが、このサイトでいろいろご教示いただき、本当に助かっています。

私なり(？)にたどり着けるまでお付き合いいただき、ありがとうございました。また別の珍問でアップしたときには、助けてくださいね。

macheobaちゃんでした。

お礼日時：2022/04/26 18:49

No.3 回答者： fujillin 回答日時： 2022/04/26 12:06

No2です。

＞「No.2」のご回答に
＞「わかってないなぁ」と思いながら、何度もご回答いただき、ほんとに恐縮です。
笑いました。
質問者様は、色を付けることしか考えていませんよね。
確かに「色を付ける」ような回答はしていませんので。

まぁ、どうしても色をつけたいようなので、「TRUE、かつ、Sheet2に名前（又はID）がない」を条件式にすればよいだけです。
もう少し考えれば、作業列も不要なこともわかるでしょう。

とは言え「手入力」しているのはSheet2なのに、わざわざ別のSheet1を見なければ確認できない仕組みというのもなんだかなと思います。
（Sheet2でそのまま確認できた方が良いはず）

例えば、Sheet2の空き列に対象者のリストが表示されていて、記入欄にその人の名（IDかも）が記入されたら、リストから消えるような仕組みにしておけば、その場で確認ができます。
また、入力が正しく行われれば、リストは消えるので邪魔な（？）表示もなくなります。
（実施には、リストを見ながら入力すればよい）

＞但しダブルで転記するリスクもあると思いますが・・・。
ダブルで記入されたら、リストに色を付けて再表示するような仕組みにでもしておけば。それもチェック可能でしょう。
ダブル記入をチェックするのなら、関係ない人を入力したときのチェックも必要なのではないのかなぁ・・(笑)

この回答へのお礼

fujillin様

苦笑しながらもお付き合いいただき、本当にありがとうございました。

実はできたんです!!

上記表のA3*A7の条件付き書式に

=IF(AND($B3,HLOOKUP($D$1,$F$2:$K$7,ROW($C3)-1,FALSE)="○"),
COUNTIF(Sheet2!$A$3:$A$7,"$C3")<1,FALSE)

とすれば、Sheet1の該当するID番号がSheet２に転記されていなければ、
通し番号が赤く返されました。

fulillinさんがおっしゃるように、作業番号列を外してもできることが確認できました。

叱咤されながら(？)、発奮して教えていただいたことも参考にして、でも最終的には言うことは聞かずに(笑)、やってみて目的が果たせました。

本当に感謝しています。

この手入力というのは、私がするのではなく、入力ならできるという他の職員も触るので、もちろん保護は書けますが、このスタイルにしようと思いついたのです。いろんな職場がありますので・・・。

ベストアンサーボタンを押したいのですが、もう一度fujillinさんに叱咤していただきたく時間を置きます。

お礼日時：2022/04/26 14:17

No.2 回答者： fujillin 回答日時： 2022/04/24 12:58

No1です

残念ながら、文章の意味がまったく伝わっていないようです。
『論理的に決まっているものを、手で入力しなければならない』はずはないということを言いたかったのですが・・・

＞やむを得ない作業です。
とおっしゃるのなら、仕方がないですね。

＞日付は別のシートにも同じ日付けになるように計算式が入っています。
計算式は、簡単に上書きできます。
直接日付を入力する人が出てくる可能性があるのでは？
それを「使い方が悪い」というのは自由ですけれど・・・


以下ご参考までに。（図は簡略化してあります）
添付図でA2:B6にID、氏名があるとして、条件付き書式の判別式と同じものが作業列（仮にG列）にあるとします。
引用したいセル（図ではA10セル）に
　=FILTER(A2:B6,G2:G6,"")
を入力することで、そのまま該当するIDと氏名がスピルされています。
（簡略化のため、Offise365環境を想定しています）

理由は、最初のシートの情報だけで論理的に抽出対象を決めることが可能だからです。

No.1 回答者： fujillin 回答日時： 2022/04/22 11:54

こんにちは

なんだか、ことを敢えて複雑になさっているような感じを受けますが・・

＞手入力というのは、必ずしもSheet1の当該行が
＞Sheet2で順番に並ぶわけではないからです。
＞この手入力というのはやむを得ないのです。
Sheet1の色のついているものを転記したいのではないのですか？
その元となる、「色付け」は自動で判別しているのではないでしょうか？
自動で判別しているものを、「手入力はやむを得ない」とする理由がわかりません。
同じ判別式を用いれば良いだけの話ではないのでしょうか。
一気にやると式が長くなる様なら、作業列をSheet1に設けておけばそれで済む話と思います。

＞転記忘れをした時に、分かるようにしたいのです。
＞そんなことができるのでしょうか。
可能ですけれど、上に記したように本末転倒のような気がします。
簡単にやるなら、上記の作業列に色付けの条件でIDや〇印を表示するようにでもしておけば良いでしょう。
（色付けに該当しなければ空白や×印にしておくなど）
その上で、「Sheet2の欄にそのIDが無ければ色を付ける」という条件付き書式を設定すれば良いことになりますが・・・

全体の利用目的がよくわかりませんけれど、目的を整理した上で要領よく行えば、条件付き書式の設定も不要になりそうな気がしますけれど・・
そもそもですが、Sheet1のE:F列も自動表記のようなので、本当に必要なのかという気がしてきます。

人が目視によって利用するものは、いったいどれなんでしょうか？
その部分に関してはわかりやすく（色を付けたり）することも必要かも知れませんけれど、それ以外に関しては必要ありませんし、最低限にしておいた方がメンテナンスなどを考慮すると却って便利かと思います。

例えば、Sheet1とSheet2に日付の入力欄が別にありますけれど、それって違う日付を入れたらどうなるのでしょうね？
人間が確認したい日付を入力するのでしょうから、入力欄は一つにしておいた方が、常に整合性が保証されることになります。
（連動するようになっていて、かつセルにロックでも掛けられているのなら問題ありませんけれど、そうなっているとは思えませんので・・）

この回答へのお礼

fujillinさん、すぐご回答くださったのに、お礼が遅れてしまい申し訳ございません。他の質問にもいろいろご回答いただき、ありがとうございます。

私なりにこの二日、いろんな計算式を試してみましたが、うまく返してくれません。

ID番号を手入力で他のシートに入力するのは、その時々にグループ分けをしないといけない（連番にはならない）ので、やむを得ない作業です。もちろん日付は別のシートにも同じ日付けになるように計算式が入っています。

Sheet1の結果で該当するID番号が他のシートに転記されたことを確認するためには、とりあえずSheet1の曜日の上に○の数を集計（=人数）して、確認するしかないかなと思っています。
但しダブルで転記するリスクもあると思いますが・・・。

ありがとうございました。

お礼日時：2022/04/24 11:22