Excelを利用した時刻の比較について質問です

質問

excel初心者です。
仕事で必要なものなのですが、技術が足りず、とても困っています。
ご回答いただけたら幸いです。

添付画像のA列とB列のような二つの、形式の違う、時間を表す数列があり、
Aの数列とBの数列を上の行から比較し、異なっているセルに色を付けて表示したいのです。
空白のセルは無視します。
画像の例でいえば「A2、B2、A7、A8、B9、B10」のセルの色を変えて表示することになります。

皆様、なにとぞ宜しくお願い致します。

回答No.8

bunjii · ベストアンサー

回答No.7の改良版です。
作業テーブルを使わずにダイレクトの条件付き書式に設定する数式を下記のとおり提言します。
適用先A列
数式を大幅に簡略化しました。
=IF(OR(A1="",A1=INDEX(B$1:B$11,SMALL(INDEX((B$1:B$11<>"")*ROW(A$1:A$11),0),COUNTBLANK(B$1:B$11)+COUNTA(A$1:A1)))),0,1)

適用先B列
=IF(OR(B1="",B1=INDEX(A$1:A$11,SMALL(INDEX((A$1:A$11<>"")*ROW(B$1:B$11),0),COUNTBLANK(B$1:B$11)+COUNTA(B$1:B1)))),0,1)

回答No.7

bunjii

>画像の例でいえば「A2、B2、A7、A8、B9、B10」のセルの色を変えて表示することになります。
>つまり「A1とB2」「A2とB2」「A3とB3」「A4とB6」「A6とB8」「A7とB9」「A8とB10」「A9とB11」をそれぞれ比較することになります。
条件が不合理な点を1ヶ所指摘させてください。
「A1とB2」、「A2とB2」ではA1とA2からB2を比較対象にしているので「A1とB1」、「A2とB2」の誤りではないかと思います。
条件付き書式で直接数式を与えるには数式自体が長くなり無理があるかと思われます。
作業用の使えば可能のようです。

貼付画像のD列とE列が作業用のテーブルになります。
D1=IF(A1="","",IF(A1=INDEX(B$1:B$11,IF(A1="","",IFERROR(SMALL(INDEX((B$1:B$11<>"")*ROW(D$1:D$11),0),COUNTBLANK(B$1:B$11)+COUNT($A$1:$A1)),""))),"",IF(A1="","",IFERROR(SMALL(INDEX((B$1:B$11<>"")*ROW(D$1:D$11),0),COUNTBLANK(B$1:B$11)+COUNT($A$1:$A1)),""))))
E1=IF(COUNTIF(D$1:D$11,ROW()),1,"")
D1とE1を下へ必要数コピーします。
条件付書式では次の数式を使います。
適用先$A:$A
=D1<>""
適用先$B:$B
=E1<>""

お礼

おかげさまで、無事に解決することができました！
ありがとうございました！

また質問することもあるかと思いますが、またぜひよろしくお願いいたします！

回答No.6

ryo_ky

回答5です．

質問とは逆の事をやっていました (^^;
この場合は，先の数式でセルの色は塗りつぶしなしにして
更にルールを追加します．

=A1<>0
この数式に合うものに対して色を付けます．

ルールの順番が重要になりますので，
=match・・・が上
=A1<>0が下にして下さいね．

回答No.5

ryo_ky

A列とB列の同じ行番号の比較ではないという事は，A列とB列で比較して同じ数値があれば，セルの色を塗るという事ですね．

であるなら，これも条件付き書式で可能です．
A列を選択した状態で条件付き書式の新しいルールを作成します．

ルールの内容は「数式を使用して書式設定するセルを決定」で
=MATCH(A1,$B:$B,0)
書式のセルの塗りつぶしで色を選択します．

これでA列の値に対してB列の値が完全一致している場合において塗りつぶしが適用されます．

同様にB列を選択してルールを作成，以下の数式を入れます．
=MATCH(B1,$A:$A,0)
あとは任意の色を設定すれば，ルールに合ったセルのみが色が付きます．

※Excel2010で確認済み

回答No.4

bunjii

>Aの数列とBの数列を上の行から比較し、異なっているセルに色を付けて表示したいのです。
>空白のセルは無視します。
条件式は以下のようになります。
=AND(A1<>B1,A1<>"",B1<>"")
適用先を$A:$AとすればA列のみ目的の色付けすれば良いでしょう。
更に適用先を$B:$Bとして同じ条件式を指定します。
条件式が同じでも適用先を$A:$Bのように一纏めにすると目的通りにならないようです。

>画像の例でいえば「A2、B2、A7、A8、B9、B10」のセルの色を変えて表示することになります。
上記で提示している条件ではA2、B2、A6、B6、A8、B8、A9、B9に色が付きます。
隠された条件があるのでしょうか？

貼付画像はExcel 2013で検証した結果です。
A列とB列の背景色を同じ色にもできます。

補足

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

教えていただいた方法だと、同じ行のA列とB列を比較するもののようです。
今回、させていただいた質問では、同じ行にあるとは限らず、上から順番に比較していきたいのです。
つまり「A1とB2」「A2とB2」「A3とB3」「A4とB6」「A6とB8」「A7とB9」「A8とB10」「A9とB11」を
それぞれ比較することになります。

回答No.3

Cupper-2

条件付き書式で設定すると言うことですよね。

とりあえずＡ１セルからＢ１０セルを選択して、条件付き書式の数式に
　　＝Ａ１＜＞Ｂ１
これで良いと思うんですけど。
（１行目のセルが選択された状態で条件付き書式を設定したなら１０行目まで数字の部分は適切に自動で書き換わるはずです）
あとは条件がそろったときに修飾する色を選択するだけです。

Ｂ列では見えない秒の桁が違っても無視するのであれば、
　　＝ＴＥＸＴ（Ａ１，”ｈ：ｍ”）＜＞ＴＥＸＴ（Ｂ１，”ｈ：ｍ”）
などとして、秒の桁を除いた形で比較してみてください。

　※現在Ｅｘｃｅｌを起動できる環境がありませんので、上記の条件式の検証はしていません。

回答No.2

MackyNo1

例示のデータなら、A列を選択して、条件付き書式をクリックし、「新しいルール」「数式を使用して・・・」を選択し、以下の式を入力します。
=AND(COUNTIF($B:$B,A1)=0,A1<>"")

同様にB列を選択して、条件付き書式で以下の式を入力します。
=AND(COUNTIF($A:$A,B1)=0,B1<>"")

回答No.1

noname#204879

》 空白のセルは無視
なのに、なぜ
A7、B9、B10「のセルの色を変えて表示することにな」るの？

補足

ご返答ありがとうございます。
B9とB10のセルは空白ではなく、「__:__」という記号が入力されているからです。